T-SQL関連メモ

→データベース関連

→SQL Server関連

→SQL一般

→SQL関連メモ(Oracle)

関連Web
selectしながらロック
selectの条件に正規表現
日付型を文字列に変換
現在日付
文字列の中に改行コードがあるかの条件
文字列の中に数字以外の文字があるかを判定
ストアドプロシージャでファイル出力する
先頭のｘｘ件だけ取得したいとき
外部結合とLEFT JOINは微妙に動作が違う！？
DB情報取得系
情報検索操作系
DB定義操作系
システムストアドプロシージャ
あるDBのバックアップからの別のDBへのリストア
データベースファイルを圧縮する
肥大したLogファイルを縮小する
コマンドラインからのSQL Serverへの接続
データファイル、ログファイルからデータベースを新規作成
デタッチ→アタッチしたDBのユーザをログインと結びつける
ログインの作成
ユーザーを作成
ロールにユーザーを追加

selectしながらロック†

Oracleで言う for update はSQL Serverには無い。そのかわりにwith (updlock,tbllock)などを使う。
with (updlock) の使用について
- http://www.atmarkit.co.jp/bbs/phpBB//viewtopic.php?topic=27757&forum=26&7

↑

selectの条件に正規表現 †

http://homepage1.nifty.com/kojama/works/rdbms/mssql/sql.html#oterm01

全角半角区別などはコレーション設定の影響があるので注意

select col from hoge
--where col like '%[A-Z][0-9]%' --英大文字に数字が連結している
--where col like '%[^A-Z][a-z]%' --1文字目が英大文字でなく、2文字目以降が英小文字
--where col like '%[ぁ-ん]%' --ひらがなを含む
--where col like '%[ァ-ン]%' --全角カタカナを含む
--where col like '%[亜-龠]%' --漢字を含む

↑

日付型を文字列に変換†

http://msdn.microsoft.com/ja-jp/library/ms187928.aspx

CONVERT(VARCHAR, <日付型フィールド>, 111) -> YYYY/MM/DD
CONVERT(VARCHAR, <日付型フィールド>, 112) -> YYYYMMDD

↑

現在日付†

CURRENT_TIMESTAMPを使う
あるいは Select GETDATE()

↑

文字列の中に改行コードがあるかの条件†

列名 like '%'+char(10)+'%'

↑

文字列の中に数字以外の文字があるかを判定†

SQL Serverにはtranslate()がないので↓のようにする

len(
replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
replace(kekka,'0', '') , '1', ''), '2', ''),'3',''), '4',''),'5',''),'6',''),'7',''),'8',''),'9',''),'.','')
) > 0

もっといい方法ないのか？

↑

ストアドプロシージャでファイル出力する†

http://msdn2.microsoft.com/ja-jp/library/ms175046.aspxより
次の例では、@var 変数の内容を、現在のサーバーディレクトリにある var_out.txt というファイルに書き込みます。
```
DECLARE @cmd sysname, @var sysname
SET @var = 'Hello world'
SET @cmd = 'echo ' + @var + ' > var_out.txt'
EXEC master..xp_cmdshell @cmd
```
oracleのような組み込みコマンドはないらしい。よってシェルを使うらしい。

↑

先頭のｘｘ件だけ取得したいとき†

以下のようにする
```
SELECT TOP 100 * FROM <table>
```

↑

外部結合とLEFT JOINは微妙に動作が違う！？†

外部結合

select * from
(
	select a.ptsid, isnull(cnt,0) cont 
	from
		pt_basic a,
		(select ptsid, Count(*) cnt from exam_basic 
			WHERE EXDATE>='20060101' and EXDATE<='20060630' group by ptsid
		) b
	where a.ptsid *= b.ptsid  
) a
where cont=0

LEFT JOIN

select * from
(
	select a.ptsid, isnull(cnt,0) cont 
	from
		pt_basic a LEFT JOIN
		(select ptsid, Count(*) cnt from exam_basic 
			WHERE EXDATE>='20060101' and EXDATE<='20060630' group by ptsid
		) b	
	ON a.ptsid = b.ptsid  
) a
where cont=0

この場合、cont=0という条件で絞った結果が欲しいのだが、外部結合だと全行が返ってくる。バグ？
（SQL Server2000の場合）

ヘルプの「*=」の項目に以下のような記述があるため、できるだけ LEFT JOINを使った方が良さそう

Microsoft潤・SQL Server潤・の以前のバージョンでは、左外部結合と右外部結合の条件は
WHERE 句で *= 演算子および=* 演算子を使用して指定していました。この構文を使用すると、
何とおりにも解釈できるあいまいなクエリに
なることがあります。FROM 句で SQL-92 に準拠した外部結合を指定すれば、解釈があいまいに
なることはありません。
SQL-92 構文の方が厳密なので、WHERE 句で従来の Transact-SQL 外部結合構文を使用する方法に
ついてはこのリリースでは説明していません。SQL Server の今後のバージョンではこの構文は
サポートしなくなる予定です。
Transact-SQL 外部結合を使用しているステートメントは SQL-92 構文を使用するように
変更する必要があります。

SQL-92 規格では、FROM 句または WHERE 句のどちらでも内部結合を指定できます。WHERE 句で
内部結合を指定すると、Transact-SQL 外部結合構文で生じたようなあいまいさは生じません。

↑

DB情報取得系†

現在日付取得
- getdate() もしくは current_timestampを使う

サーバ情報取得
```
select * from sysservers
```

データベース名取得
```
select name from sysdatabases
```

ユーザテーブル名取得

select * from sysobjects where type='U' order by name

テーブルの列情報取得

select  * from syscolumns where id = object_id('table_name')

ある名前の列を持つテーブルを列挙する

select a.name tname, b.name cname from 
syscolumns a, sysobjects b
where a.name='col_name' and a.id=b.id

テーブルに従属するオブジェクト類を得る
```
select * from sysobjects where parent_obj=object_id('table_name')
```
- プライマリキー xtype='PK'
- 外部参照 xtype='F'

制約ビュー sysconstraints

以下のようなビューになっている

SELECT constid = CONVERT(int, id), 
       id = CONVERT(int, parent_obj), 
       colid = CONVERT(smallint, info), 
       spare1 = CONVERT(tinyint, 0), 
       status = CONVERT(int, 
                 CASE xtype WHEN 'PK' THEN 1 
                            WHEN 'UQ' THEN 2 
                            WHEN 'F' THEN 3 
                            WHEN 'C' THEN 4 
                            WHEN 'D' THEN 5 
                            ELSE 0 
                 END + 
                 CASE WHEN info != 0 THEN (16)    ELSE (32) 
                 END + 
                 CASE WHEN (status & 16) != 0 THEN (512)  ELSE 0 
                 END + 
                 CASE WHEN (status & 32) != 0 THEN (1024)  ELSE 0 
                 END + (2048) + 
                 CASE WHEN (status & 256) != 0 THEN (16384) ELSE 0 
                 END + 
                 CASE WHEN (status & 512) != 0 THEN (32767) ELSE 0 
                 END + 
                 CASE WHEN (status & 4) != 0 THEN (131072) ELSE 0 
                 END + 
                 CASE WHEN (status & 1) != 0 THEN (1048576) ELSE 0 
                 END + 
                 CASE WHEN (status & 1024) != 0 THEN (2097152) ELSE 0 END), 
       actions = CONVERT(int, 4096), 
       error = CONVERT(int, 0)
FROM   sysobjects
WHERE  xtype IN ('C', 'F', 'PK', 'UQ', 'D') AND (status & 64) = 0

↑

情報検索操作系†

日付型のフォーマットを指定する

convert(varchar,date,112) --YYYYMMDDの形式

データ型の変換
- CONVERT()関数を使う。 CONVERT(float, <exp>)みたいな感じ

２つの日付の差分を取る

datediff()関数を使う

select manid,datediff(year,birth,getdate()) age from <table_name>

NULLだったらゼロを返す

select *,((f1+f2+f3+f4+f5)*ryo) fuk
from(
	select ptsid,exdate,a.ykcd,b.ryo,
	isnull(ksufuk,0) f1,
	isnull(asafuk,0) f2,
	isnull(hirufuk,0) f3,
	isnull(yorufuk,0) f4,
	isnull(ssnfuk,0) f5,
	yoho,yoryo 
	from ykzi_info a, tmykzi b
	) c
) d

みたいにする

文字列結合

select (JNM_SEI + '　' + JNM_MEI) as simei from hoge_tbl;

↑

DB定義操作系†

ある列を後からプライマリキーに指定する
```
ALTER TABLE <table_name> ADD constraint <pk_name> PRIMARY KEY (<col_name>)
```
最後の列名の指定には()が必ず必要な模様なので注意

指定した制約を無効化する

ALTER TABLE <table_name> NOCHECK CONSTRAINT <const_name>

VIEWがあったときだけドロップする

if exists (select * from dbo.sysobjects where id = object_id(N'<ビューの名前>')
 and OBJECTPROPERTY(id, N'IsView') = 1) 
     drop view <ビューの名前>

列の属性変更
```
ALTER TABLE <tablename> ALTER column <col_name> char(15) Not null
```
みたいな感じ。ただし制約があると失敗するのであらかじめ制約をなんとかしないといけない

テーブルの列追加
```
alter table <table_name> ADD <column_name> INTEGER DEFAULT 0  not null
```
みたいな感じ。
- 列の追加位置はSQL Serverでは指定できないようで、tableを作り直すとかしないとダメらしい（本当か？）
- ちなみにMySQLであれば before <col_name>, after <colname> という指定ができる

テーブルの列削除
```
alter table <table_name> drop column <column_name>
```
ただしこれだと制約をあらかじめ削除しておかないと失敗する

テーブルの列名変更

exec sp_rename '<table_name>.<col_name>', '<new_col_name>';

index作成
```
create unique NONCLUSTERED index Index名 on テーブル名(列1,列2)
```
- クラスターIndexはIndexの為のテーブルを作成せずに実際のテーブルを指定された列でソートして管理する。
- そのため各テーブルにクラスターIndexは１つだけ

index削除
```
drop index テーブル名.Index名
```

↑

システムストアドプロシージャ†

コネクションとそのステータスの一覧を得る
```
sp_who
```

特定のテーブルのサイズを得る　sp_spaceused <table_name>

やや高度な応用技

CREATE TABLE #temp (
TABLE_NAME sysname,
rows sysname,
reserved sysname,
data varchar(32),
index_size varchar(254),
unused varchar(100)
)

INSERT INTO #temp EXEC sp_MSforeachtable @command1 = "sp_spaceused '?'"
SELECT * FROM #temp order by table_name

テーブル一覧取得
```
sp_tables
```

テーブルの列情報取得
```
sp_columns <table_name>
```

テーブルのプライマリキー情報取得
```
sp_pkeys <table_name>
```

クエリアナライザによるストアドプロシージャのデバッグテクニック
多くの管理操作および情報操作は、システムストアドプロシージャを使用して実行できる

↑

あるDBのバックアップからの別のDBへのリストア†

RESTORE DATABASE <TO_DB>
FROM DISK='FROM_DBバックアップファイル' WITH REPLACE,
MOVE 'FROM_DBデータ論理名' TO 'TO_DBデータ物理ファイルフルパス.mdf',
MOVE 'FROM_DBログ論理名' TO 'TO_DBログの物理ファイルフルパス.ldf'

みたいな感じ。リストア先のDBはREPLACEをつけておけばあってもなくてもOK

  -- 読み込みのDISKのファイル名
  SELECT  @bkfname = 'D:\backup7\MySampleTest.DAT'
  --データベースの名前
  SELECT  @dbname2  = 'MySampleCopy'

  RESTORE  DATABASE  @dbname2   --復元したDBの名前
        FROM  DISK = @bkfname   --保存先ファイル名
        WITH
             REPLACE             --同じ名前のDBがあれば、既存を削除
           , MOVE 'MySampleTest_Data' TO 'd:\test\MySampleCopy.mdf'
           , MOVE 'MySampleTest_Log'  TO 'd:\test\MySampleCopy.ldf'

論理ファイル名はRESTORE FILELISTONLYで返されるレコードセットで取得できる
```
RESTORE FILELISTONLY FROM DISK='<backup_file_name>'
```

↑

データベースファイルを圧縮する†

DBCC SHRINKDATABASE
   ( database_name [ , target_percent ]
       [ , { NOTRUNCATE | TRUNCATEONLY } ]
   )

↑

肥大したLogファイルを縮小する†

BACKUP LOG [DatabaseName] WITH NO_LOG
DBCC SHRINKDATABASE ([DatabaseName], TRUNCATEONLY)
DBCC SHRINKFILE ([DatabaseName]_log, 2, TRUNCATEONLY)

↑

コマンドラインからのSQL Serverへの接続†

sqlcmd　-S　サーバー名　-U　ログインユーザー名　-P パスワード

：QUITで終了
※－ｉでファイル指定
※サーバー名はコンピュータ名\SQLEXRESSなどだがコンピューター名の代わりにlocalhostでもOK

↑

データファイル、ログファイルからデータベースを新規作成†

create database データベース名
on primary
(
filename = 'データファイルのフルパス'
)
log on
(
filename = 'ログファイルのフルパス'
)
for attach

データファイルが複数ある時は

on primary
(
filename = 'データファイルのフルパス'
)
, 
(
filename = 'データファイルのフルパス'
)

となり、必ずひとつはprimaryを指定する。

↑

デタッチ→アタッチしたDBのユーザをログインと結びつける†

参考：http://support.microsoft.com/kb/274188/
アタッチしたDBが hogeDB ユーザ名が hoge とする
結びついてないユーザの確認
```
Use hogeDB
go
sp_change_users_login 'report'
```
ユーザhogeが表示されるはず
ユーザとログインの結びつけ
- まずmanagement Studio でセキュリティの下に hoge ログインを作成する
- 以下のコマンドを実行する
```
Use hogeDB
go
sp_change_users_login 'update_one', 'hoge', 'hoge'
```

↑

ログインの作成†

create login
db_user with password = 'db_user',
default_database = デフォルトのデータベース,
check_policy = OFF

※デフォルトのデータベースを指定しない時はmasterがデフォルトのデータベースとなる。
※パスワードを簡単にしたりするにはcheck_policy = OFFが必要。

↑

ユーザーを作成†

以下ではユーザーhoge_userをログインユーザーdb_userで作成
```
create user hoge_user for login db_user with default_schema = dbo 
```

↑

ロールにユーザーを追加†

以下ではデータベースロールdb_ownerにユーザー
```
exec sp_addrolemember 'db_owner', 'hoge_user'
```

関連Web†

selectしながらロック†

selectの条件に正規表現 †

日付型を文字列に変換†

現在日付†

文字列の中に改行コードがあるかの条件†

文字列の中に数字以外の文字があるかを判定†

ストアドプロシージャでファイル出力する†

先頭のｘｘ件だけ取得したいとき†

外部結合とLEFT JOINは微妙に動作が違う！？†

DB情報取得系†

情報検索操作系†

DB定義操作系†

システムストアドプロシージャ†

あるDBのバックアップからの別のDBへのリストア†

データベースファイルを圧縮する†

肥大したLogファイルを縮小する†

コマンドラインからのSQL Serverへの接続†

データファイル、ログファイルからデータベースを新規作成†

デタッチ→アタッチしたDBのユーザをログインと結びつける†

ログインの作成†

ユーザーを作成†

ロールにユーザーを追加†

最新の50件

T-SQL関連メモ

関連Web†

selectしながらロック†

selectの条件に正規表現†

日付型を文字列に変換†

現在日付†

文字列の中に改行コードがあるかの条件†

文字列の中に数字以外の文字があるかを判定†

ストアドプロシージャでファイル出力する†

先頭のｘｘ件だけ取得したいとき†

外部結合とLEFT JOINは微妙に動作が違う！？†

DB情報取得系†

情報検索操作系†

DB定義操作系†

システムストアドプロシージャ†

あるDBのバックアップからの別のDBへのリストア†

データベースファイルを圧縮する†

肥大したLogファイルを縮小する†

コマンドラインからのSQL Serverへの接続†

データファイル、ログファイルからデータベースを新規作成†

デタッチ→アタッチしたDBのユーザをログインと結びつける†

ログインの作成†

ユーザーを作成†

ロールにユーザーを追加†

最新の50件

selectの条件に正規表現 †