○SQL (Structured Query Language)

データベース操作用言語、リレーショナルデータベースの操作に使用します。

はじめに
文字の表記
コメントの書き方
データベース操作
テーブル操作
トランザクション
データの削除
データの挿入
データの更新
データの出力
テーブルの結合
計算および関数の使い方
条件式
並べ替え
フィールドをグループ化して処理
SELECT文の結果を結合
ビュー(クエリー)
トリガー
複数の指定された値が存在しているかどうか
どれか一つが一致するレコードを返す
単一行との比較
CASE演算子





■はじめに

Microsoft SQLServerなどで使用できます

= は比較のみ、代入機能はありません

■文字の表記========================================================================================

▼データーベース名やテーブル名、フィールド名で a-a や a a などは [a-a] [a a] または "a-a" "a a" と表記する。
▼  "  のみを入力したい場合は["]を表記する。
▼  [  のみを入力したい場合は"["とする。
▼シングルクオーテーション  '  の入力には  ''  と入れる

■コメントの書き方========================================================================================

▼一行コメント

--コメント行

▼複数行コメント

/*
複数行のコメント
*/

■データベース操作========================================================================================

▼データーベース(testDB)を選択します

USE testDB;

▼新しいデータベースを作成します

CREATE DATABASE testDB;

▼データーベースを削除します

DROP DATABASE testDB;

■テーブル操作========================================================================================

▼テーブルの作成

CREATE TABLE テーブル名 (フィールド名1 フィールド1の型,フィールド名2 フィールド2の型);

CREATE TABLE table1 (field1 char(10),field2 int);

▼NULL文字を禁止したフィールドの作成

CREATE TABLE table1(field1 char(10) NOT NULL , field2 int);

▼テーブルの削除

DROP TABLE テーブル名

▼フィールドの変更

ALTER TABLE テーブル名 (ADD DROP....)

▽フィールドtestを追加します

ALTER TABLE table1 ADD test char(10);

▼インデックスの作成

CREATE INDEX インデックス名 ON テーブル名(フィールド名);

CREATE INDEX indexName1 ON table1(field1);

▽ユニークなインデックス

CREATE UNIQUE INDEX インデックス名 ON テーブル名(フィールド名);

▼インデックスの削除

DROP INDEX テーブル名.インデックス名;

DROP INDEX table1.indexName1;

■トランザクション========================================================================================

レコードがロックされます。
トランザクションの開始を行い、対象レコードに対して更新・削除を行うと、対象レコード付近にロックがかかり、
他からの対象レコードに対しての参照・更新・削除がトランザクションの完了までロックされます。


▼トランザクションの開始

BEGIN TRANSACTION;

▼トランザクションの完了

COMMIT;

▼トランザクションのキャンセル

ROLLBACK;



■レコードに参照・削除・更新禁止のロックをかける
(レコード単位のロックのため挿入はロックできません)


BEGIN TRANSACTION;
SELECT * FROM table1 WITH(XLOCK)    ←table1の対象レコードに排他ロックをかけて読み出す。
            ↑
            この間、対象となるレコードは参照・削除・更新がロックされている。
            ↓
COMMIT

これにより対象レコードに対するDELETE文、UPDATE文がロックされます。
ただし、SELECT文の場合は上記のようにロックをかけても、

SELECT * FROM table1

のように排他ロックを取得せずに読み出された場合にはロックがかかりません

SELECT * FROM table1 WITH(XLOCK)

のように排他ロックを取得するように呼び出された場合において読み出しロックが掛かります。




■トランザクションの分離レベルの設定
最初に設定するとそのセッション中有効です

▼ダーティーリード

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

他者がトランザクションでロックをしていても、その更新中のレコードを見ることができます。
データの整合性が保障されません

*****************************************************

SELECT * FROM table1;他者がロック中でも見えます

*****************************************************

▼書き込む側からのレコードのロック(デフォルト)

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

ロック中のレコードはコミットされるまで他者からの読み出し、更新がロックされます(新しいレコードはロックされてないので追加はできます)
SQLServerのデフォルト設定です

*****************************************************

BEGIN TRANSACTION;

UPDATE  table1 SET field1 ='sss' WHERE field1='bbb';該当レコードのみをロック

COMMIT;

*****************************************************

▼読み出す側からのレコードのロック

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

レコード単位のロックのため、SELECTで選択されたレコード以外には変更や追加ができます
SELECT文で読み込んだレコードのみを更新できないようにロックする

*****************************************************

BEGIN TRANSACTION;

SELECT * FROM table1 WHERE field1='aaa';ココで選択されたfield1='aaa'のレコードのみトランザクション中に他から更新されないようにロックされる

COMMIT;

*****************************************************

▼直列化

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

トランザクション中は、他者からの追加、更新をロックする(見ることはできる)

*****************************************************

BEGIN TRANSACTION;

SELECT * FROM table1 WHERE field1='a'; table1にロックがかかる
この間、table1に対して他者からの追加・更新はロックされる

COMMIT;

*****************************************************

■データの削除========================================================================================

DELETE FROM テーブル名 WHERE 条件式

▼field1='2です'のみのレコードを削除

DELETE FROM table1 WHERE field1='2です';

▼全てのレコードを削除

DELETE FROM table1;

■データの挿入========================================================================================

INSERT INTO テーブル名 (フィールド名1,フィールド名2....) VALUES (値1,値2....);

INSERT INTO table1 (field1,field2) VALUES ('1です',1);

▼SELECTでの出力をINSERTする

テーブルtable1のフィールドa,b
から
テーブルtable2のフィールドc,d
に複製する

INSERT INTO table2(c,d) SELECT a,b FROM table1;


▼SELECTの出力の全ての項目を既存のテーブルに挿入する

INSERT INTO table2 SELECT * FROM table1


▼SELECTの出力を新しいテーブルを作成して書き出す

SELECT * INTO 作成するテーブル名 FROM テーブル名;

--新しいテーブルtestを作成してSELECTの結果を書き出す
SELECT * INTO test FROM table1;

--作成したテーブルの表示
SELECT * FROM test;

--作成されたテーブルの削除
DROP TABLE test;

■データの更新========================================================================================

UPDATE テーブル名 SET フィールド名1=値1,フィールド名2=値2 WHERE 条件式

▼field2=3 のレコードの field1を'test'に変更します

UPDATE table1 SET field1='test' WHERE field2=3;

▼全てのレコードのfield1を'test'に変更

UPDATE table1 SET field1='test';

■データの出力========================================================================================

SELECT フィールド名 FROM テーブル名 WHERE 条件式;

▼ワイルドカードで全てのフィールドを出力する

SELECT * FROM table1;

▼列に任意の名前をつけて表示する

SELECT  field1 AS 'HELLO' FROM table1;

HELLO       
----------- 
0
1
2
3

▼重複行があった場合は一つの行にまとめられます

SELECT DISTINCT * FROM table1;

▼指定のフィールドのみ出力する

SELECT field1,field2 FROM table1;

▼複数のテーブルのフィールドを出力する

全ての組み合わせパターンが出力される
SELECT * FROM table1,table2;

▼先頭から指定行だけを返す

4行のみ返す
SELECT TOP 4 * FROM table1;

▼フィールドにNULLが含まれる行だけ返す

SELECT * FROM table1 WHERE field1 is NULL;

▼サブクエリー(副問い合わせ)

SELECTの条件式の中のSELECTをサブクエリーと言います

SELECT * FROM table1 WHERE field2=(SELECT field2 FROM table1 WHERE field2=2);

▼(実行例)

*****************************************************
SELECT * FROM table1;

field1               field2      
-------------------- ----------- 
1です                  1
2です                  2
3です                  3
4です                  4

*****************************************************
SELECT * FROM table2;

field3               field4      
-------------------- ----------- 
--4--                4
--3--                3
--2--                2
--1--                1

*****************************************************
SELECT * FROM table1,table2;

field1               field2      field3               field4      
-------------------- ----------- -------------------- ----------- 
1です                  1           --4--                4
2です                  2           --4--                4
3です                  3           --4--                4
4です                  4           --4--                4
1です                  1           --3--                3
2です                  2           --3--                3
3です                  3           --3--                3
4です                  4           --3--                3
1です                  1           --2--                2
2です                  2           --2--                2
3です                  3           --2--                2
4です                  4           --2--                2
1です                  1           --1--                1
2です                  2           --1--                1
3です                  3           --1--                1
4です                  4           --1--                1

*****************************************************

■テーブルの結合========================================================================================

▼結合元のテーブル table1
*****************************************************
SELECT * FROM table1;

field1               field2      
-------------------- ----------- 
0です                  0
1です                  1
2です                  2
3です                  3

*****************************************************

▼結合元のテーブル table2
*****************************************************
SELECT * FROM table2;

field3               field4      
-------------------- ----------- 
--4--                4
--3--                3
--2--                2
--1--                1

*****************************************************

▼テーブルを結合しました
*****************************************************
SELECT * FROM table1,table2 WHERE field2=field4;

field1               field2      field3               field4      
-------------------- ----------- -------------------- ----------- 
1です                  1           --1--                1
2です                  2           --2--                2
3です                  3           --3--                3

*****************************************************

▼外部結合 =*    * の付いている側のテーブルのデータを全て残します
*****************************************************
SELECT * FROM table1,table2 WHERE field2=*field4;

field1               field2      field3               field4      
-------------------- ----------- -------------------- ----------- 
NULL                 NULL        --4--                4
3です                  3           --3--                3
2です                  2           --2--                2
1です                  1           --1--                1

*****************************************************

▼外部結合 *=    * の付いている側のテーブルのデータを全て残します
*****************************************************
SELECT * FROM table1,table2 WHERE field2*=field4;

field1               field2      field3               field4      
-------------------- ----------- -------------------- ----------- 
0です                  0           NULL                 NULL
1です                  1           --1--                1
2です                  2           --2--                2
3です                  3           --3--                3

*****************************************************

▼テーブルを結合しました2
*****************************************************
SELECT * FROM table1 INNER JOIN table2 ON field2=field4;

field1               field2      field3               field4      
-------------------- ----------- -------------------- ----------- 
1です                  1           --1--                1
2です                  2           --2--                2
3です                  3           --3--                3

*****************************************************

▼外部結合 LEFT 側のテーブルのデータを全て残します
*****************************************************
SELECT * FROM table1 LEFT JOIN table2 ON field2=field4;

field1               field2      field3               field4      
-------------------- ----------- -------------------- ----------- 
0です                  0           NULL                 NULL
1です                  1           --1--                1
2です                  2           --2--                2
3です                  3           --3--                3

*****************************************************

▼外部結合 RIGHT 側のテーブルのデータを全て残します
*****************************************************
SELECT * FROM table1 RIGHT JOIN table2 ON field2=field4;

field1               field2      field3               field4      
-------------------- ----------- -------------------- ----------- 
NULL                 NULL        --4--                4
3です                  3           --3--                3
2です                  2           --2--                2
1です                  1           --1--                1

*****************************************************

■計算および関数の使い方========================================================================================

▼計算

SELECT 1+2;
SELECT field2+1 FROM table1;
SELECT (SELECT field2 FROM table1 WHERE field1='2です')*(SELECT field2 FROM table1 WHERE field1='4です');

▼レコード数のカウント

SELECT COUNT(*) FROM table1;

▼最大値を出力

SELECT MAX(field2) FROM table1;

▼最小値を出力

SELECT MIN(field2) FROM table1;

▼合計を出力

SELECT SUM(field2) FROM table1;

▼平均を出力

SELECT AVG(field2) FROM table1;

▼特定のレコードの足し算は条件式で指定して計算する

SELECT SUM(field2) FROM table1 WHERE field1='1です' OR field1='4です';

▼最小値のレコードのみ削除

DELETE FROM table1 WHERE field2 = (SELECT MIN(field2) FROM table1);

▼文字列の連結
SELECT 'aa'+'bb';

▼両側のスペースを削除
SELECT RTRIM(LTRIM('   aa    '))+'bb';

▼数字を文字に変換
SELECT 'aa'+STR(10);

▼文字を数字に変換
SELECT 10 + CONVERT(int , '10');

▼NULLであるかどうか
SELECT 'NULLです' WHERE (NULL) IS NULL;

▼SELECT文の結果に行が含まれるかどうか
SELECT '行が存在します' WHERE EXISTS (SELECT * FROM table1);

▼文字の長さを出力 結果は4です
SELECT LEN('test');

■条件式========================================================================================

▼関係演算子

▽数字の比較

フィールド名 >= 値
フィールド名 = フィールド名

▽文字列が同一かどうか比較

フィールド名 = '比較文字列'

▼論理演算子

(条件式) AND (条件式)
(条件式) OR (条件式)

▽符号の反転

NOT (条件式)

▼数字を文字列に変換して比較

LTRIM(STR(field2))='1'

▼フィールドのある範囲に含まれているか判定

▽2から5の間の値の抽出

SELECT * FROM テーブル名 WHERE フィールド名 BETWEEN 2 AND 5;

▽2から5と7から9の間の値の抽出

SELECT * FROM テーブル名 WHERE (フィールド名 BETWEEN 2 AND 5) OR (フィールド名 BETWEEN 7 AND 9);

▽2から5以外の値の抽出

SELECT * FROM テーブル名 WHERE フィールド名 NOT BETWEEN 2 AND 5

▼文字列のパターン判定

指定された文字列が指定されたパターンと一致するかどうか判定
戻り値はboolです。

SELECT * FROM テーブル名 WHERE フィールド名 LIKE 'パターン文字列';

▽ワイルドカード文字-------------------------------

% : 0個またはそれ以上の文字で構成される任意の文字列
_ : 任意の1文字

---------------------------------------------------

▽フィールドが3文字のレコードのみ抽出

SELECT * FROM テーブル名 WHERE フィールド名 LIKE '___';

▽フィールドが3文字以外のレコードのみ抽出

SELECT * FROM テーブル名 WHERE NOT フィールド名 LIKE '___';

▽フィールドにAが含まれるレコードのみ抽出

SELECT * FROM table1 WHERE  field1 LIKE '%A%';

▽ b または c または d のどれかの一文字を判定するには
'[bcd]'と書きます

bcdどれかの一文字が含まれるフィールドのみ抽出
SELECT * FROM table1 WHERE field1 LIKE '%[bcd]%';

■並べ替え========================================================================================

SELECT * FROM テーブル名 ORDER BY 並べ替えるキーになるフィールド;
SELECT * FROM テーブル名  WHERE (条件式)  ORDER BY 並べ替えるキーになるフィールド;

▼昇順

ORDER BY キーになるフィールド名 ASC

▼降順

ORDER BY キーになるフィールド名 DESC

▼複数フィールドの並び替え

ORDER BY フィールド1 ASC,フィールド2 DESC,フィールド3 ASC
フィールド3→フィールド2→フィールド1の順番に並び替えられる

■フィールドをグループ化して処理========================================================================================

SELECT * FROM テーブル名 GROUP BY グループ化するフィールド名 HAVING 出力させる条件式

*****************************************************
SELECT * FROM table1;

field1      group1               
----------- -------------------- 
1           A                   
2           A                   
3           A                   
4           A                   
5           A                   
1           B                   
2           B                   
1           C                   
2           C                   
3           C                   
4           C            
       
*****************************************************

上のテーブルをgroup1ごとに分けて合計を出します。

*****************************************************
SELECT group1,SUM( field1) FROM table1 GROUP BY group1;

group1                           
-------------------- ----------- 
A                    15
B                    3
C                    10

*****************************************************

■SELECT文の結果を結合========================================================================================

*****************************************************
SELECT * FROM table1;

field1               field2               
-------------------- -------------------- 
a                    b                   
c                    d                   
e                    f                   
g                    h                   
a                    b                   
*****************************************************

上の field1 と field2 の SELECT文を結合します
このとき重複行があった場合は一つにまとめられます。

*****************************************************
SELECT field1 FROM table1 UNION  SELECT field2 FROM table1;

field1               
-------------------- 
a                   
b                   
c                   
d                   
e                   
f                   
g                   
h       

*****************************************************

UNION ALL を指定すると全ての行が返されます

*****************************************************
SELECT field1 FROM table1 UNION ALL SELECT field2 FROM table1;

field1               
-------------------- 
a                   
c                   
e                   
g                   
a                   
b                   
d                   
f                   
h                   
b              

*****************************************************



▼複数のテーブルの結合

*****************************************************
SELECT * FROM a

a_field1        a_field2
-------------------------------
a               あ
aa              ああ
*****************************************************

*****************************************************
SELECT * FROM b

b_field1        b_field2
-------------------------------
bbb             びびび
bbbb            びびびび
*****************************************************

上記テーブルを結合します

*****************************************************
SELECT * FROM a UNION ALL SELECT * FROM b

a_field1        a_field2
-------------------------------
a               あ
aa              ああ
bbb             びびび
bbbb            びびびび
*****************************************************

結合されたテーブルから条件で抽出もできます

*****************************************************
SELECT * FROM (SELECT * FROM a UNION ALL SELECT * FROM b) WHERE a_field1='bbb'

a_field1        a_field2
-------------------------------
bbb             びびび
*****************************************************





■ビュー(クエリー)========================================================================================

▼ビューの作成
CREATE VIEW ビュー名 AS ビューを生成するためのSELECT文

CREATE VIEW testView AS SELECT field1 FROM table1

▼ビューの削除
DROP VIEW ビュー名

DROP VIEW testView

■トリガー========================================================================================

トランザクションの途中でもトリガーは働きます

DELETE INSERT UPDATE
が行われたときに実行される命令を規定します。
複数のレコードが影響を受けたとしても、実行されるのは一度だけです。

▼トリガーの作成

CREATE TRIGGER トリガーの名前 ON トリガーを仕掛けるテーブル名 FOR イベント名 AS 実行される命令;

CREATE TRIGGER tgrName1 ON table1 FOR DELETE AS SELECT 'DELETEが処理されました';

▼トリガーの削除

DROP TRIGGER トリガーの名前;

DROP TRIGGER tgrName1;

■複数の指定された値が存在しているかどうか========================================================================================

SELECT * FROM テーブル名 WHERE フィールド名 IN(値1,値2....);
SELECT * FROM テーブル名 WHERE フィールド名 IN(SELECT文);

*****************************************************
SELECT * FROM table1;

field1               field2               field3      
-------------------- -------------------- ----------- 
aa                   bb                   10
aaa                  bbb                  100
aaaa                 bbbb                 1000
aaaaa                bbbbb                10000

*****************************************************
SELECT * FROM table1 WHERE field1 IN('aaa','aaaa');

field1               field2               field3      
-------------------- -------------------- ----------- 
aaa                  bbb                  100
aaaa                 bbbb                 1000

*****************************************************
SELECT * FROM table1 WHERE field1 IN(SELECT field1 FROM table1 WHERE field3>100);

field1               field2               field3      
-------------------- -------------------- ----------- 
aaaa                 bbbb                 1000
aaaaa                bbbbb                10000

*****************************************************

■どれか一つが一致するレコードを返す========================================================================================

SELECT * FROM テーブル名 WHERE フィールド名 = ANY(SELECT フィールド名2 FROM テーブル名2);

*****************************************************
SELECT * FROM table1;

field1               field2               field3      
-------------------- -------------------- ----------- 
aa                   bb                   10
aaa                  bbb                  100
aaaa                 bbbb                 1000
aaaaa                bbbbb                10000

*****************************************************
SELECT * FROM table2;

field1               
-------------------- 
aaa                 
aaaa          
      
*****************************************************

二つのレコードのfield1のどれか一つが一致するレコードを返します

*****************************************************
SELECT * FROM table1 WHERE field1=ANY(SELECT field1 FROM table2);

field1               field2               field3      
-------------------- -------------------- ----------- 
aaa                  bbb                  100
aaaa                 bbbb                 1000

*****************************************************

■単一行との比較========================================================================================

SELECT * FROM テーブル名 WHERE フィールド名 = ALL(単一行を返すSELECT文);

ALL(単一行を返すSELECT文)が単一行以外(複数の行)を返した時点で判断式はFALSEになります。

*****************************************************
SELECT * FROM table1;

field1               field2               field3      
-------------------- -------------------- ----------- 
aa                   bb                   10
aaa                  bbb                  100
aaaa                 bbbb                 1000
aaaaa                bbbbb                10000

*****************************************************

複数行を返すSELECT文の場合
*****************************************************
SELECT * FROM table1 WHERE field1 = ALL(SELECT field1 FROM table1 WHERE field1 = 'aaa' or field1='aaaa');

field1               field2               field3      
-------------------- -------------------- ----------- 
*****************************************************

単一行を返すSELECT文の場合
*****************************************************
SELECT * FROM table1 WHERE field1 = ALL(SELECT field1 FROM table1 WHERE field1 = 'aaa');

field1               field2               field3      
-------------------- -------------------- ----------- 
aaa                  bbb                  100
*****************************************************

■CASE演算子========================================================================================

a           
----------- 
0
1
2

▼上のテーブルの値を変換し出力する
aが1ならば'1です'が出力され、それ以外なら'1以外です'と出力する。

SELECT (CASE a WHEN 1 THEN '1です' ELSE '1以外です' END) FROM test;

---------- 
1以外です
1です
1以外です

▼条件式により値を変換し出力する

SELECT (CASE WHEN a>1 THEN  '1より大きい' ELSE str(a)  END) FROM test;

----------- 
         0
         1
1より大きい


■おぼえ========================================================================================



*****************************************************
SELECT * FROM data1

str                  no                   str2      
-------------------- -------------------- ----------- 
文字列1             数字                 文字列2
*****************************************************

上記のようなテーブルにデータがあり先頭がら1000行のみ取り出し、
strの中に"で"が含まれる行を検索し、その結果をstrで並び替える場合、

@SELECT TOP 1000 * FROM data1 WHERE str LIKE '%で%' ORDER BY str

ASELECT * FROM data1 WHERE str IN (SELECT TOP 1000 str FROM data1 WHERE str LIKE '%で%') ORDER BY str

@は結果が1000以上の場合でも正しく表示するが、Aは正しくない
結果が1000以下の場合には@とAは同一の結果を出す
そのため多量の検索結果がある場合は、@は非常に時間がかかる。

























▲トップページ > SQL 関連