主に、AccessSQLの覚え
一部
Excel VBA（ADO）
標準SQL
目次
----
AccessSQLと、標準SQLの違い
SQLの構成要素
選択
結合
集合
グループ化
サブクエリ（副問合せ）
レコード操作
データ定義
*********************************************
.AccessSQLと、標準SQLの違い
*********************************************
文字列の連結
&、+
||（ダブルパイプ）、CONCAT('a','b')
NULLの置換
Nz(a,0)
COALESCE(a,0) 可変長の引数を持ち、NULLでない最初の引数を返す
条件式
IIf(条件式,'a','b')
CASE WHEN 条件式 THEN 'a' ELSE 'b' END
CASE F7
WHEN 1 THEN 'a',
WHEN 2 THEN 'b'
END
日付リテラル
#2020/01/01#
'20200101'
ワイルドカード
AccessSQL（ANSI-89（DAO)）
*
?
[!a]
#
標準SQL、ANSI-92（Microsoft SQL Server、ADO）
% 0文字以上の文字列
_ 1文字
[^a]
*********************************************
.SQLの構成要素
*********************************************
文（SELECT文）
句（SELECT句、FROM句）
述語（論理演算の結果を返すもの、IN述語、EXISTS述語、比較演算子）
式（値を返すもの、CASE式、演算式、集合関数）
*********************************************
.選択
*********************************************
SELECT
------
SELECT * FROM T1
SELECT COUNT(*) FROM T1
SELECT F1,F2 FROM T1
SELECT DISTINCT F1,F2 FROM T1
SELECT F1 FROM T1 WHERE F1 = 'a' 選択（特定の行を取り出す）
WHERE NOT (F1 = 'a')
NOT演算子は複合条件には使えない 使えない例：NOT(a AND b)
WHERE F1 IS Null （= NULL は誤り）
WHERE F1 IS NOT Null
WHERE F1 <> 1 （NULLの行は含まれない）
WHERE F1 LIKE "*a*" 0文字以上の文字列
WHERE F1 LIKE "a?" 1文字
WHERE F1 LIKE [abc] いずれか1文字
WHERE F1 LIKE 1#0 数字1文字
WHERE F1 = #2020/01/01#"
WHERE F1 BETWEEN #1998/01/01# AND #2000/12/31#
WHERE F1 = 'a' AND F2 > #1998/01/01#
WHERE (F1 = 'a') OR (F1 = 'b')"
WHERE F1 IN ('a', 'b') （aかb）
WHERE F1 NOT IN ('a', 'b')
SELECT DISTINCT F1 FROM T1 重複の削除
SELECT DISTINCT F1,F2 FROM T1 F1もF2も同じ場合に省略
SELECT * FROM T1 ORDER BY F1, F2 （昇順のASCは省略可）
SELECT * FROM T1 ORDER BY F2 DESC （降順）
SELECT * FROM T1 WHERE F1=1 ORDER BY F2
SELECT * FROM T1 ORDER BY 2 ASC （2列目で並べ替え）
AS
テーブルの別名、フィールドの別名
AS は省略可
SELECT F1 FROM T1 AS A
SELECT F1 FROM T1 A
×SELECT T1.F1 FROM T1 AS A 別名を使った場合、元のテーブル名は使えない
連番 ※F1はユニークな値、F1の並び順
SELECT Count(T1.F1) AS 連番, T1.F1
FROM T1 AS T1, T1 AS T2
WHERE (((T1.F1)>=T2.F1);
連番 ※F1はユニークな値、F2の並び順
SELECT Count(T1.F1) AS 連番, T1.F1
FROM T1 AS T1, T1 AS T2
WHERE (((T1.F2 & T1.F1)>=T2.F2 & T2.F1);
F2毎に連番を振る（通し番号） ※F1はユニークな値、F1の並び順
SELECT Count(T1.F1) AS 連番, T1.F1, T1.F2
FROM T1 AS T1, T1 AS T2
WHERE (((T1.F1)>=T2.F1) AND ((T1.F2)=T2.F2))
GROUP BY T1.F1, T1.F2;
連番
連番:DCount("F1","T1",F1 <=" & [F1])
※こちらの方法の方がAccessでは早い
（標準SQL）
ウィンドウ関数（分析関数）
AVG(F1)
SELECT AVG(F1) OVER (PARTITION BY F2) AS A1 FROM T1
MAX(F1)
MIN(F1)
SUM(F1)
COUNT(F1)
ROW_NUMBER() 1からの行番号を取得、単純なナンバリングに使う
LAG(F1,[n]) n行前のF1（n省略時=1）
SELECT LAG(F1) OVER (PARTITION BY F2 ORDER BY F3) AS A1 FROM T1
前回との差や、前回比などの計算に使う
1行目は前がないのでNULL
LEAD(F1,[n]) n行後
RANK()
順位（同順は、その後を飛び番にする）
SELECT RANK() OVER (PARTITION BY F2 ORDER BY F3 DESC) AS A1 FROM T1
F3の大きい順位
DENSE_RANK() 順位（同順は、その後を飛び番にしない）
NTILE(n)
n等分、n個に分割、3つに分割した場合は1,2,3が返る
SELECT NTILE(3) OVER (ORDER BY F3 DESC) AS A1 FROM T1
移動平均
SELECT AVG(F1) OVER (ORDER BY F2 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS A1 FROM T1
ROWS:行単位、RANGE:値
指定モード
CURRENT ROW 現在の行
n PRECEDING n行前
n FOLLOWING n行後
UNBOUNDED PRECEDING 先頭の行
UNBOUNDED FOLLOWING 末尾の行
*********************************************
.結合
*********************************************
CROSS JOIN
----------
デカルト積、直積、交差結合
すべての行の総当たりの組み合わせを出力
ON句もUSING句も指定しない
SELECT A.F1 FROM T1 A CROSS JOIN T2 B
SELECT A.F1 FROM T1 A,T2 B（CROSS JOIN を省略した記述）※結合型を省略して単にJOINのみはINNER JOIN
INNER JOIN
----------
内部結合、内結合（うちけつごう）
一致するレコードが存在する行のみ出力される
T1の結合列がNULLの場合、結合の対象とならない
SELECT A.F1, B.F2 FROM T1 A INNER JOIN T2 B ON A.F1 = B.F1
SELECT A.F1, B.F2 FROM T1 A INNER JOIN T2 B USING(F1)
等価結合、等結合（内部結合の一部が等価結合、等結合）
ON句、USING句で結合。結合列の重複が保持される
自然結合 同じ列名、データ型で結合（結合列の重複が取り除かれる）
ON句やUSING句は指定できない
OUTER JOIN
----------
外部結合（左結合、右結合、完全結合、ひだりそとけつごう）
SELECT A.F1, B.F2 FROM T1 A LEFT OUTER JOIN T2 B ON A.F1 = B.F1
SELECT A.F1, B.F2 FROM T1 A RIGHT OUTER JOIN T2 B ON A.F1 = B.F1
SELECT A.F1, B.F2 FROM T1 A FULL OUTER JOIN T2 B ON A.F1 = B.F1
OUTER は省略可
結合先のテーブルにレコードが存在しない場合はNull
結合元の結合列がNULLの場合、NULLで埋まる
ON句は結合条件（T1=T2）と、結合前抽出（主にT2、条件に合致しない行もNULLで残る）
WHERE句は結合後条件（T1、実行結果の表の左側の列）
完全外部結合
FULL OUTER JOIN は、AccessSQLにはない
（例）完全外部結合～外部結合の和を用いて作り出す
SELECT A.*, B.* FROM T1 A RIGHT OUTER JOIN T2 B ON A.F1 = B.F1
UNION
SELECT C.*, D.* FROM T1 C RIGHT OUTER JOIN T2 D ON C.F1 = D.F1
3つ以上のテーブルの結合
-----------------------
SELECT A.F1,B.F2,C.F3
FROM (T1 LEFT JOIN T2 ON T1.F1 = T2.F1) LEFT JOIN T3 ON T1.F2 = T3.F2
再帰結合（自己結合）
--------------------
同じテーブル同士の結合
SELECT F1 FROM T1 A,T2 B WHERE A.F2 = B.F3
SELECT F1 FROM T1 A INNER JOIN T1 B ON A.F2 = B.F3
*********************************************
.集合
*********************************************
和両立
次数が同じ（属性の数が同じ）
対応する属性のドメインが等しい（タイプ）
和集合、差集合、共通集合の演算に必要
UNION
-----
縦につなげる（和集合、マージ）
SELECT F1 FROM T1 UNION SELECT F1 FROM T2 （ALLを付けないと重複が除かれる）
SELECT F1 FROM T1 UNION ALL SELECT F1 FROM T2 （ALLを付けると重複を含む）
SELECT F1 FROM T1 UNION ALL SELECT F1 FROM T2 ORDER BY F1 （全体を並べ替え）
ORDER BY で指定できる列名は最初のSELECTで指定されている列に限る
INTERSECT（積、共通部分）
-------------------------
AccessSQLなし
SELECT F1 FROM T1 INTERSECT ALL SELECT F1 FROM T2 （ALLを付けないと重複が除かれる）
EXCEPT（DIFFERENCE、差、差分）
------------------------
AccessSQLなし
SELECT F1 FROM T1 EXCEPT ALL SELECT F1 FROM T2（ALLを付けないと重複が除かれる）
*********************************************
.グループ化
*********************************************
GROUP BY
--------
SELECT F1,SUM(F2) FROM T1 GROUP BY F1
SELECT F1,SUM(F2) FROM T1 WHERE F1 > 100 GROUP BY F1
HAVING
グループ化した結果に対する検索条件
SELECT F1,SUM(F2) FROM T1 WHERE F1 > 100 GROUP BY F1 HAVING SUM(F2) > 500
SELECT F1 FROM T1 GROUP BY F1 HAVING COUNT(*) > 3 （件数が3件以上のグループ）
集計関数
AVG(F1)
MAX(F1)
MIN(F1)
SUM(F1)
COUNT(*)
COUNT(DISTINCT F1) AccessSQL不可
WHERE句の中で集計関数は使えない
全ての行がNULLの場合、SUM(F1)はNULL（0ではない）
*********************************************
.サブクエリ（副問合せ）
*********************************************
SELECT文の中に、別のSELECT文を含んでいる問合せのこと
非相関サブクエリ
----------------
スカラーサブクエリ（1行1列のテーブル（単一の値）を返すサブクエリ）
SELECT * FROM T1 WHERE F1 = (SELECT MAX(F1) FROM T1)
SELECT * FROM T1 WHERE F1 < (SELECT AVG(F1) FROM T1)
複数行単一列のテーブルを返すサブクエリ
SELECT * FROM T1 WHERE F1 IN (SELECT DISTINCT(F1) FROM T2)
SELECT * FROM T1 WHERE F1 NOT IN (SELECT DISTINCT(F1) FROM T2)
SELECT * FROM T1 WHERE F1 <> ALL (SELECT DISTINCT(F1) FROM T2)
SELECT * FROM T1 WHERE F1 = SOME (SELECT DISTINCT(F1) FROM T2)
SELECT * FROM T1 WHERE F1 > ANY (SELECT F1 FROM T2)
相関サブクエリ（相関副問合せ）
------------------------------
外側のテーブルを参照する
EXISTSを使った存在チェックで使うことが多い
副問合せの列名は一般的に*を使う
SELECT * FROM T1 WHERE 2 = (SELECT COUNT(*) FROM T2 WHERE T1.F1 = T2.F1)
SELECT * FROM T1 WHERE EXISTS (SELECT * FROM T2 WHERE T1.F1 = T2.F1)
UPDATE T1 SET T1.F2 = (SELECT AVG(T2.F2) FROM T2 WHERE T1.F1 = T2.F1)
WITH句
-------
一時的に利用できるテーブル
副問合せを読みやすくするために使う
WITH T1(F1) AS (SELECT文）
SELECT F1 FROM T1
再帰問合せで使う一時表
WITH RECURSIVE AS (SELECT文)
WITH句を2つ以上
WITH T1(F1) AS (SELECT文),
T2(F2) AS (SELECT文)
*********************************************
.レコード操作
*********************************************
UPDATE
------
レコード更新
UPDATE T1 SET F1=1
UPDATE T1 SET F1=1,F2=NULL
UPDATE T1 SET F1=1 WHERE F2 > 10
UPDATE T1 LEFT JOIN T2 ON T1.F1 = T2.F2 SET T1.F2 = T2.F2
「更新可能なクエリであることが必要です」のエラー
集計クエリーと結合すると更新不可のクエリーとなるので、定義域集計関数を使う
UPDATE T1 SET F1 = DCount('F2','T1','F3 = ' F3 ) F3毎の件数をF1に代入
UPDATE T1 SET F1 = DCount("*","T1","F3 = " & Nz([F3],0) & " AND F2 <=" & [F2]) F3毎の連番をF1に代入
DELETE
------
レコード削除
DELETE [FROM] T1 WHERE F1=1;
（例）VBA にて全削除
Dim strSQL As String
strSQL = "DELETE T1.* FROM T1;"
DoCmd.RunSQL strSQL
（例）VBAにて1件ずつ全削除
Dim con As Object
Dim rs As Object
Dim strSql As String
Set con = Application.CurrentProject.Connection
Set rs = CreateObject("ADODB.Recordset")
strSQL = "SELECT * FROM T1"
rs.Open strSQL, con, 0, 3
Do Until rs.EOF
rs.Delete
rs.MoveNext
Loop
rs.Close
con.Close
Set rs = Nothing
Set con = Nothing
INSERT
------
レコード追加
INSERT INTO T1 (F1,F2) VALUES(100,'abc')
INSERT INTO T1 (F1,F2) SELECT F1,F2 FROM T2
INSERT INTO T1 SELECT * FROM T1
*********************************************
.データ定義
*********************************************
ALTER TABLE
-----------
既存のテーブルの定義の変更
ALTER TABLE T1 ADD COLUMN F1 TEXT(3) フィールド追加、フィールドサイズ3
ALTER TABLE T1 ALTER COLUMN F1 TEXT(3) フィールドサイズ変更
ALTER TABLE T1 DROP COLUMN F1 フィールドを削除
ALTER TABLE T1 ADD PRIMARY KEY(F1)
CREATE TABLE
------------
テーブルの作成
空のテーブルが作られる
CREATE TABLE T1(F1 TEXT(5) PRIMARY KEY,F2 NOT NULL)
CREATE TABLE T1(F1 TEXT(50), F2 LONGTEXT, F3 SHORT, F4 LONG, F5 DOUBLE, F6 DATETIME, F7 YESNO, F8 CURRENCY)
既存のテーブルを元に新規テーブルを作成
SELECT F1,F2 INTO T1 FROM T2 'データも追加される
テーブルの削除
DROP TABLE T1 'テーブルを閉じた状態で実行
CREATE
------
CREATE TABLE
CREATE VIEW
CREATE INDEX
（標準SQL）
CREATE ROLE
CREATE TRIGGER
CREATE ASSERTION
CREATE DOMAIN
CREATE TABLE(F1 CHAR(3) PRIMARY KEY, F2 CHAR(3))
CREATE TABLE(F1 CHAR(3), F2 CHAR(3), PRIMARY KEY(F1))
CREATE TABLE(F1 CHAR(3) DEFAULT "a") デフォルト値
CREATE TABLE T2 LIKE T1（T1と同じ列構成のT2を定義する）
主なデータ型（RDBMSごとに異なる）
CHAR(n) 半角固定長（n<=255）、nバイト、余った部分は半角空白で埋められる
NCHAR(n) 全角固定長（n<=127）、n*2バイト、余った部分は全角空白で埋められる
VARCHAR(n) 半角可変長（最大n文字、実際の文字数＋4バイトの付加情報）
NCHAR VARYING(n) 全角可変長（最大n文字、実際の文字数*2＋4バイトの付加情報）
SMALLINT ±32,768、2バイト
INTEGER ±21憶（約）、4バイト
DECIMAL(m,n) 有効桁数m桁で、そのうちn桁が小数部、m/2+1の小数部を切り捨てたバイト数
DATE 2023-01-01、4バイト
TIME 23:59:59、3バイト
TIMSESTAMP 2023-01-01 23:59:59.999999、10バイト
整合性制約（制約）
CREATE TABLE T1(F1 CHAR(3), UNIQUE(F1))
F1 CHAR(3) NOT NULL（非ナル制約）
F1 CHAR(3) UNIQUE（一意性制約、NULLの重複は可）
F1 CHAR(3) PRIMARY KEY（主キー制約、主キーは1つのTに1つ、NULLは不可）
F1 CHAR(3),F2 INT, PRIMARY KEY(F1,F2)（複合キー）
F1 INT DEFAULT 1
検査制約
F1 INT CHECK(F1<10)（Falseの場合は更新や挿入時にエラー）
参照制約（外部キー制約）
CREATE TABLE T1(F1 CHAR(3) REFERENCES T2(F1))
参照元に外部キーを指定
参照元T1、参照先T2（主キーまたは一意のフィールド）
制約
参照元の外部キーに同じ値がある場合、参照先の行を削除できない
参照先に存在しない値を、参照元に追加・更新できない
CREATE TABLE T1(F1 CHAR(3),,FOREIGN KEY(F1) REFERENCES T2(F1))
CREATE TABLE T1(F1 CHAR(3),,FOREIGN KEY(F1) REFERENCES T2(F1) ON DELETE SET NULL)
参照制約の参照動作（オプション）
ON DELETE|UPDATE
NO ACTION デフォルト（何もしない、猶予でコミット時に制約検査し、参照制約違反があれば更新拒否）
RESTRICT 更新削除不可
CASCADE 連鎖更新削除
SET NULL
SET DEFAULT
制約名の付与（CONSTRAINT）
CREATE TABLE T1(F1 CHAR(3) CONSTRAINT C1 PRIMARY KEY)
CREATE TABLE T1(F1 CHAR(3),CONSTRAINT C1 PRIMARY KEY(F1))
CREATE VIEW
CREATE VIEW V1 AS SELECT F1 FROM T1
CREATE VIEW V1(A,B) AS SELECT F1,F2 FORM T1
更新可能なVIEW
基底表が特定できる
基底表の行が特定できる
下記を使っていない
集約関数（AVGなど）
GROUP BY
DISTINCT（重複を除く）
直積でない
基底表が更新可能
WITH CHECK OPTION
VIEWで指定した条件のデータしか追加できない
CREATE VIEW V1 AS SELECT F1 WHERE F2=1 WITH CHECK OPTION
F2=1しか追加できない
CREATE INDEX index1 ON T1(F1) 既存のテーブルにインデックスを作成、index1はインデックス名
DROP INDEX index1 ON T1 インデックスを削除
CREATE ROLE
ロールは権限をまとめたもの
CREATE ROLE R1
GRANT SELECT V1 TO R1
GRANT R1 TO USER1
CREATE TRIGGER
テーブルに対する操作（挿入、更新、削除）を契機に処理を実行
CREATE TRIGGER TR1 BEFORE UPDATE ON T1
REFERENCING OLD AS OLD1 NEW AS NEW1 FOR EACH ROW
SET NEW1 = COALESCE(NEW1.F1, 'A')
CREATE TRIGGER TR1 AFTER UPDATE OF F1 ON T1
REFERENCING NEW [ROW](|TABLE) AS NEW1
FOR EACH ROW | STATEMENT
WHEN(R1.F1=1)
BEGIN ATOMIC
SELECT文、CALL
END
トリガー動作時期
AFTER、BEFORE、INSTEAD OF
トリガー事象
INSERT、DELETE、UPDATE
遷移表または遷移表リスト
OLD [ROW]、NEW [ROW]、OLD TABLE、NEW TABLE
被トリガー動作
FOR EACH ROW（1行ずつ操作）、FOR EACH STATEMENT（表に対して1回操作）
WHEN
表明（ASSERTION）
CREATE ASSERTION A1 CHECK(NOT EXISTS(SELECT * FROM T1))
定義域（DOMAIN）
ドメインはデータ型として使える／削除、変更はDROP、ALTER
CREATE DOMAIN D1 INT CHECK(IN(1，2，3)
DROP
----
DROP TABLE T1
DROP VIEW V1
DORP ROLE R1
GRANT
-----
権限を付与する
GRANT ALL PRIVILEGES ON T1 TO ID1
GRANT SELECT, UPDATE(F1) ON T1 TO PUBLIC
GRANT ROLE1 TO ID1
権限
ALL PRIVILEGES、SELECT、INSERT、DELETE、UPDATE
ユーザーID
PUBLIC,ユーザー、ロール
WITH GRANT OPTION
与えられた権限をほかのユーザーに与えられる
REVOKE
------
権限を取り消す
REVOKE ALL PRIVILEGES ON T1 FROM ID1
ビューを使用するときの権限
所有者；SELECTは可、INSERT、DELETE、UPDATEは原表に従う
所有者以外：ビューに対する権限の有無だけで判断
ビューを作成するときの権限
SELECT権限が必要
GRANT OPTIONありなら、作成したビューのSELECT権限を他に付与できる
埋込型
------
（標準SQL）
カーソルの宣言
EXEC SQL DECLARE C1 CURSOR FOR
SELECT文
END-EXEC
カーソルを開く
EXEC SQL OPEN C1
END- EXEC
カーソルを閉じる
EXEC SQL CLOSE C1
END- EXEC
1行取り出す
EXEC SQL FETCH C1 INTO :A,:B
END- EXEC
位置設定によるUPDATE文、DELETE文
EXEC SQL UPDATE文
WHERE CURRENT C1
END- EXEC
コミット
EXEC SQL COMMIT END-EXEC
ロールバック
EXEC SQL ROLLBACK END-EXEC
SQLの使い方（AccessSQL）
2023-12-02
