SQL(SQLServer) - hamaのページ

SQLServerへの接続方法

コマンドプロンプトからsaアカウントで接続

>sqlcmd -S localhost -Usa
パスワード:

selectの出力結果をテキストファイルに保存

>sqlcmd -Q "use [000db];select * from [aaa_tbl]"

　　　 -S localhost -Usa -P ******* > test.txt

基本的なSQL

追加(INSERT)

INSERT INTO xxx_tbl(xxx,yyy,zzz) VALUES('XXX','YYY','ZZZ')

削除(DELETE)

DELETE xxx_tbl WHERE XXX=’XXX’
※WHERE以降を省略した場合、テーブル内のすべての行を削除するため注意

変更(UPDATE)

UPDATE xxx_tbl SET xx='xx',yy='yy' WHERE zz='zz'
※WHERE以降を省略した場合、テーブル内のすべての行の値を変更するため注意

参照(SELECT)

基本パターン

同一テーブルをJOIN

SELECT A.[アカウント名],A.[ユーザID],B.[氏名] 
FROM [aaa_tbl] AS A
INNER JOIN [aaa_tbl]B ON A.[ユーザID]=B.[アカウント名]
WHERE A.[ユーザID]='111111'
※[aaa_tbl]＝Aに短縮、同一テーブルをJOINする場合は別名にする必要があり、[aaa_tbl]Bとした。

総数

SELECT COUNT(*) FROM table1

再帰

SELECT * FROM [aaa_tbl] 
WHERE [kkk]=(SELECT DISTINCT([lll]) FROM [aaa_tbl]
 WHERE [アカウント名]='111111')

複数のユーザを対象としたselectの再帰

SELECT * FROM [bbb_tbl]
WHERE [アドレス] = ANY (SELECT [アドレス] FROM [aaa_tbl])

論理積条件

SELECT [アドレス] FROM [ccc_tbl]
WHERE ([ユーザID_A] IS NULL)
 AND ([ユーザID_B] IS NULL)
 AND ([ユーザID_C] IS NULL)

差分発生個所の確認（1カラム）

SELECT    '1側' AS 差分存在箇所, Main.ユーザID AS ユーザID
FROM      table1 AS Main LEFT JOIN
          table2 AS Sub ON (Main.ユーザID = Sub.ユーザID)
WHERE     Sub.ユーザID IS NULL
UNION ALL
SELECT    '2側' AS 差分存在箇所, Main.ユーザID AS ユーザID
FROM      table2 AS Main LEFT JOIN
          table1 AS Sub ON (Main.ユーザID = Sub.ユーザID)
WHERE     Sub.ユーザID IS NULL

差分発生個所の確認（行全体）

SELECT    '1側' AS 差分存在箇所, AA.*
FROM      (SELECT  *  FROM table1
           EXCEPT
           SELECT  *  FROM table2) AS AA
UNION ALL
SELECT    '2側' AS 差分存在箇所, AA.*
FROM      (SELECT  *  FROM table2
           EXCEPT
           SELECT  *  FROM table1) AS AA

SELECTの表示結果の変更

caseによる場合分け

SELECT name,TYPE, 
CASE WHEN member='ito taro' THEN 'ito' 
WHEN member='sato goro' THEN 'sato'
ELSE 'xxx' 
END
FROM dbo.member_tbl

表示結果の結合

SELECT name,TYPE,("name"+'('+TYPE+')') AS dummy,
FROM user_tbl

文字列の置換(name列の中のAをBに置き換えて表示）

SELECT name,REPLACE(name,A,B) FROM table1

桁を揃える

SELECT name,RIGHT('0000000' + RTRIM(ユーザID), 7) AS ユーザID
FROM table1

@の右側のドメイン名を変更する

SELECT name,
 LEFT(a.メールアドレス, CHARINDEX('@', a.メールアドレス) - 1) + '@sample.com' 
 AS 変更後のアドレス FROM table1

左から１番目の文字を抽出する

SELECT name, SUBSTRING(ユーザID, 0, 2) FROM table1

ローマ字姓名の頭文字を大文字に変換する

SELECT 
 UPPER(LEFT(ローマ字姓,1))+LOWER(RIGHT(ローマ字姓,LEN(ローマ字姓)-1)) AS ローマ字姓,
 UPPER(LEFT(ローマ字名,1))+LOWER(RIGHT(ローマ字名,LEN(ローマ字名)-1)) AS ローマ字名
FROM talble1

テーブル操作

テーブルの作成

CREATE TABLE table1 (col1 varchar(8000),col2 varchar(8000))

テーブルのコピー

SELECT * INTO todb.dbo.user_tbl FROM fromdb.dbo.user_tbl

テーブルの削除

DROP TABLE table1

テーブルのデータを空にする

TRUNCATE TABLE table1