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

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

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

* SQL基礎編はこちら

https://qiita.com/takekota/items/8c6a1a346d382529a9ba

* DB理論はこちら

https://qiita.com/takekota/items/47e57625e15d4f570b95

# 応用編（データ制御言語）
# 認証と権限
テーブル操作するためには、３つのセキュリティチェックを通過する必要があるのだ

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

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

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

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

## ユーザーの作成
```sql
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`に戻すことで解決
>```sql
>-- 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';
>```
>```sh
># 設定反映のためmysqlの再起動
>sudo service mysql restart
>```
>* [Node.jsでMySQL 8.0へ接続しようとする時に発生するエラー](https://qiita.com/monga3/items/6583c07a9b275b469608)
>* [Mysql パスワードをPolicyに合わせるとめんどくさい件について](https://qiita.com/keisukeYamagishi/items/d897e5c52fe9fd8d9273)

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

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

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

### 権限の種類
`GRANT` ：許可
`DENY` ：拒否（MySQLにはない）
`REVOKE` ：取り消し

### 権限の設定
```sql
-- 基本構文
権限の種類 権限 ON オブジェクト名 TO データベースユーザー名またはロール
```
```sql
-- テーブルの選択権限の付与
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';
```

### ロールの設定
```sql
-- 新しい権限グループを作成
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）
```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）
```sql
-- ローカル変数の定義
DECLARE var1 INT;
DECLARE var2 VARCHAR(255);

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

-- ローカル変数を使用したクエリの実行
SELECT * FROM table_name WHERE column1 = var1 AND column2 = var2;
```
ローカル変数は、トランザクション内でのみ有効であり、トランザクションの外では使用できない。
セッション変数は、セッションが終了するまで値を保持し、データ型を明示する必要がない。
セッション変数の名前は頭に `@` をつける。
```sql
-- セッション変数の設定
SET @var1 = 10;
SET @var2 = 'Example';

-- セッション変数を使用したクエリの実行
SELECT * FROM table_name WHERE column1 = @var1 AND column2 = @var2;
```
[コミット | 公式ドキュメント](https://dev.mysql.com/doc/refman/8.0/ja/commit.html)
[トランザクション | 公式ドキュメント](https://dev.mysql.com/doc/refman/8.0/en/innodb-autocommit-commit-rollback.html)
[トランザクション 参考サイト](https://dream-database-sql-seminar.com/mysql-intro-contents/mi_ch07/mi_0701/)
[セッション変数 | 公式ドキュメント](https://dev.mysql.com/doc/refman/8.0/ja/set-variable.html)

# 変数（SQL server）
SQL server にセッション変数はなく、ローカル変数の頭に `@` をつける。
```sql
DECLARE @x = int
SET @x = 78

DECLARE @y int = 373
```

## メッセージ出力
```sql
-- SQL server
PRINT '出力したい文字列'

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

DECLARE @z int = 987
PRINT 'z=' + CONVERT(varchar, @z) -- 文字列型に変換
```
```sql
-- MySQL
SELECT '出力したい文字列' AS '';

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

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

# 変数の利用
```sql
-- 最上位の値を格納
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
;
```
## プリペアドステートメント
```sql
-- テーブル名を動的に変数として扱う
SET @table = 'departments';
SET @sql = CONCAT('SELECT * FROM ', @table);
-- プリペアドステートメントの準備と実行と解放
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
```
[参考記事](https://gene.hatenablog.com/entry/20100608/1275994173)

# 流れ制御
## 条件分岐
```sql
-- 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
```
```sql
-- 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` を利用することが多い

[参考記事](https://qiita.com/souhei-etou/items/1a346721f8689eefd0c6)

## ループ処理
```sql
-- SQL server
DECLARE @x int
SELECT @x = 1
WHILE @x <= 10
BEGIN
 PRINT @x
 SELECT @x = @x + 1
END
```
```sql
-- 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 ステートメント | 公式ドキュメント](https://dev.mysql.com/doc/refman/8.0/ja/while.html)
[LOOP ステートメント | 公式ドキュメント](https://dev.mysql.com/doc/refman/8.0/ja/loop.html)

# UPSERT
MERGE(SQL server), UPSERT(Oracle), ON DUPLICATE KEY UPDATE(MySQL)
データが存在するならUPDATE、存在しないならINSERT
```sql
-- SQL server
MERGE INTO マージ先のテーブル
    USING マージ元のテーブルまたはクエリ
    ON マージの条件
    WHEN MATCHED THEN
        UPDATE SET 更新
    WHEN NOT MATCHED THEN
        INSERT VALUES ( 追加 )
```
```sql
-- 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
-- SQL server
CREATE TABLE #一時テーブル名 (
    列名1 データ型
    ,列名2 データ型
    , …)
```
```sql
-- MySQL
CREATE TEMPORARY TABLE tmp_t1 (
    id INT,
    name varchar(256)
    );
```
WITH句はクエリ内で使える一時的な仮想テーブルを作る。（[基本編へ](https://qiita.com/takekota/items/8c6a1a346d382529a9ba#with%E5%8F%A5%E4%B8%80%E6%99%82%E3%83%86%E3%83%BC%E3%83%96%E3%83%AB%E4%B8%80%E6%99%82%E3%83%93%E3%83%A5%E3%83%BC)）
一方`CREATE TEMPORARY TABLE`ステートメントはDB内にセッション終了時にDROPされるテーブルを作る

# テーブル変数
MySQL にはない
```sql
-- SQL server
CREATE TABLE @テーブル変数名 (
    列名1 データ型
    ,列名2 データ型
    , …)
```

# 再帰クエリ
応用情報でよくあるやつ
```sql
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](https://stackoverflow.com/questions/24525634/mysql-alternate-solution-of-sql-servers-hierarchyid-datatype)
```sql
-- 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
-- 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
```
```sql
-- 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;
```
[SQL Server 公式ドキュメント](https://learn.microsoft.com/ja-jp/sql/relational-databases/stored-procedures/stored-procedures-database-engine?view=sql-server-ver16)
[MySQL 公式ドキュメント](https://dev.mysql.com/doc/refman/8.0/ja/create-procedure.html)
[参考記事](https://qiita.com/chihiro/items/5d438549b5869900abdd)

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

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

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

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

## ストアドファンクション
MySQLでは、データを返す再利用可能な関数の形式が procedure と別にされている。
```sql
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
-- 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
```
```sql
-- 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 ;
```

## 確認（定義の表示）
```sql
-- MySQL
SHOW CREATE {PROCEDURE | FUNCTION} sp_name;
```

## 削除
```sql
-- SQL server
DROP PROCEDURE ストアドプロシージャ名
```
```sql
-- MySQL
DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name;
```

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

# try catch
```sql
-- SQL server
BEGIN TRY
    -- 例外の発生を調べるコード
END TRY
BEGIN CATCH
    -- 例外が発生したときに処理するコード
END CATCH
```
```sql
-- 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 | 公式ドキュメント](https://dev.mysql.com/doc/refman/8.4/en/declare-handler.html)

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

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

# インデックス
## 概要
インデックスは、データベース内での検索を効率化するためのデータ構造（多くの場合、B+ツリー）

**普通の検索の場合**
```sql
select * from users where first_name = '太郎';
```
何番目にあるか、何件あるか、何もわからん
 → 先頭から末尾まで探索するしかない（全件検索）
 → 全データが１億２千万列あったら時間かかるよ

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

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

## 削除
```sql
-- SQL server
DROP INDEX テーブル名.インデックス名
DROP INDEX インデックス名 ON テーブル名
```
```sql
-- MySQL
ALTER TABLE テーブル名 DROP INDEX インデックス名;
```

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

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


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

## トリガー
「表」で「事象」が起き｛る前 | た後｝に「被動作」をする。
```sql
CREATE TRIGGER トリガー名 {BEFORE | AFTER} 事象
    ON 表名 [REFERENCING {遷移表 | 遷移変数リスト}]
    被トリガー動作
```
トリガー事象：INSERT, UPDATE, DELETE ステートメント