SQL全部まとめたい | 応用編

SQLの概要

分類	SQL	役割
データ操作言語	SELECT INSERT UPDATE DELETE	データ検索 データ追加 データ更新 データ削除
データ定義言語	CREATE ALTER DROP	オブジェクト作成 オブジェクト変更 オブジェクト削除
データ制御言語	GRANT REVOKE COMMIT ROLLBACK	権限を与える 権限を取り消す トランザクションをコミット トランザクションを取り消す

基礎編では、「データ操作言語」と「データ定義言語」を
応用編では、「データ制御言語」をまとめました
SQL以外のDBに関する理論とかはまた別にしました

• SQL基礎編はこちら

• DB理論はこちら

応用編（データ制御言語）

認証と権限

テーブル操作するためには、３つのセキュリティチェックを通過する必要があるのだ

1. ネットワークセキュリティ
   そもそもDBサーバーが非公開だとなにも始まらない
   サーバーにアクセスできるネットワークを制限
   不要なポートを閉じ、ファイアウォールを設定
   SSL/TLSを使用してMySQLの通信を暗号化し、中間者攻撃からデータを保護

2. ログイン認証（サーバー接続）
   DBサーバーに接続したいなら、ユーザー名とパスワードを入力してください
   「ユーザーはログインできませんでした」

3. データベースへの接続
   そのユーザーはそのデータベースへのアクセス権もってんのか？
   「データベースにアクセスできません」

4. アクセス制御（オブジェクト操作権限）
   ユーザーアカウントおよびロールに必要最低限の権限を付与する（GRANTおよびREVOKE文）
   「SELECT 権限が　拒否されました」

ユーザーの作成

CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'password';

パスワードポリシーと認証モード

強力なパスワードポリシーを設定し、パスワードの長さ、複雑さ、期限を制限する

以下「mysql8.0 と node.js の連携エラー」（WEBアプリを作成していた時のメモ）

MySQL8.0.4以降 のログイン認証方式はcaching_sha2_passwordがデフォルトだが、Node.jsの「mysql」は未対応なので、認証方式をmysql_native_passwordに戻すことで解決

-- ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
ALTER USER 'root' IDENTIFIED WITH mysql_native_password BY '';

-- passwotd policy change
SHOW VARIABLES LIKE 'validate_password%';
-- ポリシーを変更
set global validate_password.policy=LOW;
-- 文字列の長さを変更
set global validate_password.length=6;

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

-- mysql2にしたいので戻しました。
SELECT Host,User,plugin,ssl_cipher,authentication_string FROM mysql.user;
ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'password';

# 設定反映のためmysqlの再起動
sudo service mysql restart

• Node.jsでMySQL 8.0へ接続しようとする時に発生するエラー
• Mysql パスワードをPolicyに合わせるとめんどくさい件について

オブジェクト権限とロール

オブジェクト権限とは、CRUD（選択・挿入・更新・削除）権限の総称。
権限が付与されているユーザーは、その操作を実行できる。

ビューやストアドプロシージャを利用したセキュリティ強化も重要

ロールとは、オブジェクト権限をパッケージ化したもの。
（MySQLでは、権限を論理的にグループ化したものが使える）

権限の種類

GRANT ：許可
DENY ：拒否（MySQLにはない）
REVOKE ：取り消し

権限の設定

-- 基本構文
権限の種類 権限 ON オブジェクト名 TO データベースユーザー名またはロール

-- テーブルの選択権限の付与
GRANT SELECT ON database_name.table_name TO 'new_user'@'localhost';
-- テーブルの更新権限の付与
GRANT INSERT, UPDATE, DELETE ON database_name.table_name TO 'new_user'@'localhost';
-- テーブルの全権限の付与
GRANT ALL PRIVILEGES ON database_name.table_name TO 'new_user'@'localhost';
-- データベース全体の権限の付与
GRANT ALL PRIVILEGES ON database_name.* TO 'new_user'@'localhost';

-- テーブルの権限の削除
REVOKE SELECT ON database_name.table_name FROM 'new_user'@'localhost';
-- データベース全体の権限の削除
REVOKE ALL PRIVILEGES ON database_name.* FROM 'new_user'@'localhost';

ロールの設定

-- 新しい権限グループを作成
CREATE ROLE my_role [WITHオプション];

-- 作成したロールに権限を付与
GRANT SELECT, INSERT, UPDATE ON database_name.table_name TO my_role;

-- ユーザーにロールを割り当てる
GRANT my_role TO 'user1'@'localhost';
GRANT my_role TO 'user2'@'localhost';

トランザクション

トランザクション処理とは複数の処理を大きな1つの処理として扱うこと
大きな処理の中で何らかのトラブルが発生した場合には、処理自体を取り消すことが可能

初めにサンプルを掲示（こんな感じのSQL）

-- トランザクションの開始
START TRANSACTION;

-- クエリの実行
SELECT * FROM departments;
INSERT INTO departments(name) VALUES('サステナビリティ部');
UPDATE departments SET name = 'イノベーション部' WHERE id = 12;
DELETE FROM departments WHERE id = 13;
SELECT * FROM departments;

-- トランザクションのコミットまたはロールバック
COMMIT;
-- ROLLBACK;

START TRANSACTION; をすると COMMIT; または ROLLBACK; をするまで、自動コミットが無効になる。

変数（MySQL）

-- ローカル変数の定義
DECLARE var1 INT;
DECLARE var2 VARCHAR(255);

-- ローカル変数に値をセット
SET var1 = 10;
SET var2 = 'Example';

-- ローカル変数を使用したクエリの実行
SELECT * FROM table_name WHERE column1 = var1 AND column2 = var2;

ローカル変数は、トランザクション内でのみ有効であり、トランザクションの外では使用できない。
セッション変数は、セッションが終了するまで値を保持し、データ型を明示する必要がない。
セッション変数の名前は頭に @ をつける。

-- セッション変数の設定
SET @var1 = 10;
SET @var2 = 'Example';

-- セッション変数を使用したクエリの実行
SELECT * FROM table_name WHERE column1 = @var1 AND column2 = @var2;

コミット | 公式ドキュメント
トランザクション | 公式ドキュメント
トランザクション 参考サイト
セッション変数 | 公式ドキュメント

変数（SQL server）

SQL server にセッション変数はなく、ローカル変数の頭に @ をつける。

DECLARE @x = int
SET @x = 78

DECLARE @y int = 373

メッセージ出力

-- SQL server
PRINT '出力したい文字列'

DECLARE @message VARCHAR(255) = 'Hello, world!'
PRINT @messge

DECLARE @z int = 987
PRINT 'z=' + CONVERT(varchar, @z) -- 文字列型に変換

-- MySQL
SELECT '出力したい文字列' AS '';

SET @message = 'Hello, world!';
SELECT @message;

SET @z = 987;
SELECT CONCAT('z=', @z);

変数の利用

-- 最上位の値を格納
SELECT
    @param1 = id,
    @param2 = name,
FROM users
LIMIT 1
;
-- id=3の値を格納
(@param3, @param4) = select name, salary  from users where id = 3;
-- 動的SQL
SET @myname = '松岡';
SELECT id FROM users WHERE name = @myname;
SET @your_id = 3;
SELECT @your_name = name
    FROM users
    WHERE id = @your_id
;

プリペアドステートメント

-- テーブル名を動的に変数として扱う
SET @table = 'departments';
SET @sql = CONCAT('SELECT * FROM ', @table);
-- プリペアドステートメントの準備と実行と解放
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

参考記事

流れ制御

条件分岐

-- SQL server
-- IF関数
SELECT IIF( salary >= 500000, '50万円以上', '50万円未満' ), salary FROM users;
-- IF文
DECLARE @x int
SET @x = DATEPART( hour, GETDATE() ) -- 現在時間を取得

IF @x < 12
    BEGIN
        PRINT 'おはよう'
    END
ELSE 
    BEGIN
        PRINT 'こんにちは'
    END

-- MySQL
-- IF関数
SELECT IF(score >= 60, '合格', '不合格') AS result
FROM exam_results;
-- IF文
IF EXISTS (SELECT * FROM exam_results WHERE score >= 60)
THEN
    SELECT '合格です';
ELSE
    SELECT '不合格です';
END IF;

EXISTS や NOT EXISTS を利用することが多い

参考記事

ループ処理

-- SQL server
DECLARE @x int
SELECT @x = 1
WHILE @x <= 10
BEGIN
 PRINT @x
 SELECT @x = @x + 1
END

-- MySQL
-- LOOP ステートメント
DELIMITER //
CREATE PROCEDURE simple_loop(IN max_count INT)
BEGIN
    DECLARE counter INT DEFAULT 1;
    
    -- ループ開始
    simple_loop_label: LOOP
        IF counter > max_count THEN
            LEAVE simple_loop_label; -- ループを抜ける
        END IF;
        
        -- ここにループ内で実行したい処理を記述
        SELECT counter;
        
        SET counter = counter + 1; -- カウンターを増加
    END LOOP simple_loop_label; -- ループ終了
END//
DELIMITER ;
-- プロシージャの実行
CALL simple_loop(5);

-- WHILE ステートメント
DELIMITER //
CREATE PROCEDURE simple_while_loop(IN max_count INT)
BEGIN
    DECLARE counter INT DEFAULT 1;

    -- ループ開始
    simple_while_loop_label: WHILE counter <= max_count DO
        -- ここにループ内で実行したい処理を記述
        SELECT counter;
        
        SET counter = counter + 1; -- カウンターを増加
    END WHILE simple_while_loop_label; -- ループ終了
END//
DELIMITER ;
-- プロシージャの実行
CALL simple_while_loop(5);

MySQLには LOOP ステートメントや、WHILE ステートメントがある。
ストアドプロシージャやストアド関数内で繰り返し処理を実装できる。
WHILE ステートメント | 公式ドキュメント
LOOP ステートメント | 公式ドキュメント

UPSERT

MERGE(SQL server), UPSERT(Oracle), ON DUPLICATE KEY UPDATE(MySQL)
データが存在するならUPDATE、存在しないならINSERT

-- SQL server
MERGE INTO マージ先のテーブル
    USING マージ元のテーブルまたはクエリ
    ON マージの条件
    WHEN MATCHED THEN
        UPDATE SET 更新
    WHEN NOT MATCHED THEN
        INSERT VALUES ( 追加 )

-- MySQL
INSERT INTO table1(col1, col2) 
    SELECT col1, col2 FROM table2
    ON DUPLICATE KEY UPDATE 
        col1=table2.col1,
        col2=table2.col2;
INSERT INTO users (name, department_id, salary) VALUES('木村', 1, 10000)
    ON DUPLICATE KEY UPDATE salary=salary+10000;

一時テーブル

利用しているセッション内だけで有効なテーブルを作成し、セッションが閉じたときに自動的にテーブルが削除される構文

-- SQL server
CREATE TABLE #一時テーブル名 (
    列名1 データ型
    ,列名2 データ型
    , …)

-- MySQL
CREATE TEMPORARY TABLE tmp_t1 (
    id INT,
    name varchar(256)
    );

WITH句はクエリ内で使える一時的な仮想テーブルを作る。（基本編へ）
一方CREATE TEMPORARY TABLEステートメントはDB内にセッション終了時にDROPされるテーブルを作る

テーブル変数

MySQL にはない

-- SQL server
CREATE TABLE @テーブル変数名 (
    列名1 データ型
    ,列名2 データ型
    , …)

再帰クエリ

応用情報でよくあるやつ

WITH cte1 (社員番号, 社員名, 上司社員番号, 階層) AS (
    -- 上司
    SELECT 社員番号, 社員名, 上司社員番号, 0
        FROM 社員
        WHERE 社員番号 = 1001
    UNION ALL
    -- 部下（再帰）
    SELECT e.社員番号, e.社員名, e.上司社員番号, cte1.階層 + 1
        FROM 社員 AS e
        INNER JOIN cte1
        ON e.上司社員番号 = cte1.社員番号
)
SELECT * FROM cte1

Hierarchy データ型

MySQL にはない
MySQL: Alternate solution of SQL Server's HierarchyId datatype

-- SQL server
WITH cte1 (path, 社員番号, 社員名, 上司社員番号, 階層) AS (
    -- 上司
    SELECT
        HierarchyID::GetRoot() AS root,
        社員番号, 社員名, 上司社員番号, 0
    FROM 社員
        WHERE 社員番号 = 1001
    UNION ALL
    -- 部下（再帰）
    SELECT
        CAST( cte1.path.ToString() + CAST(e.社員番号 AS varchar(30)) + '/' AS HierarchyID ),
        e.社員番号, e.社員名, e.上司社員番号, cte1.階層+ 1
    FROM 社員 AS e
        INNER JOIN cte1
        ON e.上司社員番号 = cte1.社員番号
)
-- pass は16進数なので文字列に変換して表示
SELECT path.ToString(), * FROM cte1

ストアドプロシージャ

概要

データベース操作を一つのオブジェクトとして、DBサーバーに保存したもの。

作成と実行

-- SQL server
CREATE PROCEDURE ストアドプロシージャ名
    @param1 int,
    @param2 int
    AS 任意の Transact-SQL ステートメント

EXECUTE ストアドプロシージャ名

-- sample
CREATE PROCEDURE proc1
    @param1 int
    AS SELECT * FROM emp WHERE deptno = @param1
EXEC proc1 20

-- MySQL
DELIMITER //

CREATE PROCEDURE procedure_name(parameters)
BEGIN
    -- プロシージャの中身
END //

DELIMITER ;

CALL procedure_name(parameters);

-- sample
delimiter //
CREATE PROCEDURE citycount (IN country CHAR(3), OUT cities INT)
    BEGIN
        SELECT COUNT(*) INTO cities FROM world.city
        WHERE CountryCode = country;
    END//
delimiter ;
CALL citycount('JPN', @cities);
SELECT @cities;

公式ドキュメント
参考記事

IN ：入力パラメータ
OUT ：出力パラメータ
INTO ：変数に取得した値を格納する

引数はデフォルトで IN 、初期値 NULL

引数は、プロシージャー内で値が変更される可能性がある。
プロシージャーから戻ったとき、 IN の場合その変更は反映されず、 OUT の場合は反映される。

テーブル名を変数として使いたい場合プリペアドステートメントを使う

ストアドファンクション

MySQLでは、データを返す再利用可能な関数の形式が procedure と別にされている。

DELIMITER //
CREATE FUNCTION calculate_sum(a INT, b INT)
RETURNS INT
BEGIN
    DECLARE result INT;
    SET result = a + b;
    RETURN result;
END //
DELIMITER ;
SELECT calculate_sum(5, 3);

RETURN ステートメント

強制終了して、値を返す

-- SQL server
CREATE PROCEDURE proc6
    @param1 int = NULL
    AS
        IF @param1 IS NULL
            BEGIN
                PRINT 'パラメーター未入力！'
                RETURN (99)
            END

DECLARE @ret int
EXEC @ret = proc6
SELECT @ret

-- MySQL
-- stored procedure にはないが、stored function では使える
DELIMITER //
CREATE FUNCTION calculate_sum(num1 INT, num2 INT) RETURNS INT
BEGIN
    DECLARE sum_result INT;
    -- 引数がNULLの場合は0を返す
    IF num1 IS NULL OR num2 IS NULL THEN
        RETURN 0;
    END IF;
    -- num1とnum2の合計を計算して返す
    SET sum_result = num1 + num2;
    RETURN sum_result;
END //
DELIMITER ;

確認（定義の表示）

-- MySQL
SHOW CREATE {PROCEDURE | FUNCTION} sp_name;

削除

-- SQL server
DROP PROCEDURE ストアドプロシージャ名

-- MySQL
DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name;

照合順序

｛大文字小文字、ひらがなカタカナ、全角半角｝を区別するかどうかのやつ
照合順序はテーブルやカラム、データベースレベルで設定されるため、適切な照合順序を選択することで、文字の区別を調整することができる。

try catch

-- SQL server
BEGIN TRY
    -- 例外の発生を調べるコード
END TRY
BEGIN CATCH
    -- 例外が発生したときに処理するコード
END CATCH

-- MySQL
-- ０での除算エラー
DECLARE division_error CONDITION FOR SQLSTATE '22012';
DECLARE CONTINUE HANDLER FOR division_error
BEGIN
    SELECT 'Division by zero error occurred';
END;
-- 一般的なSQLエラー
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
    SELECT 'An error occurred';
END;

handlers | 公式ドキュメント

エラーメッセージ・再スロー・ユーザー定義エラー（割愛）

1. エラーメッセージ:
   MySQLでは、エラーが発生した場合、エラーコードとエラーメッセージが生成されます。共にログに記録され、クライアントにも返されます。
2. 再スロー:
   再スローは、ストアドプロシージャやトリガー内でエラーが発生した場合に、そのエラーを再度スローして呼び出し元に伝えることです。再スローを使用すると、エラーをより詳細に処理したり、階層的なエラーハンドリングを実装したりすることができます。
3. ユーザー定義エラー:
   ユーザー定義エラーは、開発者が自分のアプリケーション内で定義したカスタムエラーコードやメッセージです。これにより、特定の状況や条件に応じて、アプリケーション独自のエラーメッセージを返すことができます。

MySQLでは、これらの概念を組み合わせて、エラーハンドリングを効果的に行うことができます。

インデックス

概要

インデックスとは、検索の精度を向上させるための機能（本の索引みたいなもの）

普通の検索の場合

select * from users where first_name = '太郎';

何番目にあるか、何件あるか、何もわからん
→ 先頭から末尾まで探索するしかない（全件検索）
→ 全データが１億２千万列あったら時間かかるよ

indexを貼った列の検索時、内部的にツリー構造で探索する
→ テーブルの規模が大きく、少量の検索をする場合は、インデックスを作ったほうが良い
→ useless index はパフォーマンスを低下させる＆余分なディスクとメモリを使う

作成

-- SQL server
CREATE [{CLUSTERED | NONCLUSTERED}] INDEX インデックス名 ON テーブル名(列名)

-- MySQL
CREATE [{CLUSTERED | NONCLUSTERED}] INDEX インデックス名 ON テーブル名(列名)
ALTER TABLE テーブル名 ADD INDEX インデックス名(列名[,列名２...]);
show index from テーブル名;

削除

-- SQL server
DROP INDEX テーブル名.インデックス名
DROP INDEX インデックス名 ON テーブル名

-- MySQL
ALTER TABLE テーブル名 DROP INDEX インデックス名;

クラスタインデックスとセカンダリインデックス

• クラスタインデックス
  主キーのこと
  １テーブルに１つしか定義できない
  ポインターではなく、実際のデータを格納する（本でいうページ番号）

• セカンダリインデックス
  デフォルトのインデックス（本の索引みたいなもの）

その他キーワード

カーソル

テーブルから一度に１行ずつ、検索と操作を行えるようにする仕組み
DECLARE：宣言
OPEN：開く
FETCH：取り出し
CLOSE：閉じる

トリガー

「表」で「事象」が起き｛る前 | た後｝に「被動作」をする。

CREATE TRIGGER トリガー名 {BEFORE | AFTER} 事象
    ON 表名 [REFERENCING {遷移表 | 遷移変数リスト}]
    被トリガー動作

トリガー事象：INSERT, UPDATE, DELETE ステートメント