○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
*****************************************************
▼テーブルを結合しました２
*****************************************************
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 フィールド名２ FROM テーブル名２);
*****************************************************
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ならば'１です'が出力され、それ以外なら'１以外です'と出力する。
SELECT (CASE a WHEN 1 THEN '１です' ELSE '１以外です' END) FROM test;
----------
１以外です
１です
１以外です
▼条件式により値を変換し出力する
SELECT (CASE WHEN a>1 THEN '1より大きい' ELSE str(a) END) FROM test;
-----------
0
1
1より大きい
■おぼえ ==================
*****************************************************
SELECT * FROM data1
str no str2
-------------------- -------------------- -----------
文字列１ 数字 文字列２
*****************************************************
上記のようなテーブルにデータがあり先頭がら1000行のみ取り出し、
strの中に"で"が含まれる行を検索し、その結果をstrで並び替える場合、
①SELECT TOP 1000 * FROM data1 WHERE str LIKE '%で%' ORDER BY str
②SELECT * FROM data1 WHERE str IN (SELECT TOP 1000 str FROM data1 WHERE str LIKE '%で%') ORDER BY str
①は結果が1000以上の場合でも正しく表示するが、②は正しくない
結果が1000以下の場合には①と②は同一の結果を出す
そのため多量の検索結果がある場合は、①は非常に時間がかかる。
▲トップページ
>
SQL 関連