クライアントからの接続先インスタンスの解説
MySQLは、一つのサーバーに一つのインスタンスしかインストールできない。
ログインはインスタンスに対して行なうので、一つのインスタンスに対してのみログインをすれば良い。
データベースとスキーマは同一で、一つのインスタンスに複数のデータベース(スキーマ)を作成できる。
MySQLのユーザーはインスタンス単位で管理されており、ログインもデータベースへのアクセスも同一のユーザーで可能となっている。
また、ユーザーとロールはほとんど同じで、違いはロールではログインできない点である。
クライアントツール
コマンドラインツール
mysql
GUIツール
HeidiSQL
インスタンスへの接続・終了方法
コマンドラインツール
インスタンスへ接続する
mysql -u ユーザー名 -p
終了方法
quit
データベースへの接続や切替
データベースへ接続する
mysql -u ユーザー名 -p [データベース名]
データベースを切替える
use データベース名;
現在の確認
現在接続しているデータベースを確認する
SELECT database();
現在のスキーマを確認する
SELECT database();
※データベースとスキーマは同じ。
現在のユーザーを確認する
select user(), current_user();
一覧の表示
データベース一覧の表示
show databases;
スキーマ一覧の表示
show databases;
※データベースとスキーマは同じ。
テーブル一覧の表示
show tables;
show table status;
オブジェクト一覧の表示
SHOW FULL TABLES IN データベース名
ストアドプロシージャの一覧表示。
SHOW PROCEDURE STATUS;
関数の一覧表示。
SHOW FUNCTION STATUS;
VIEWの一覧表示。
SHOW FULL TABLES IN データベース名 WHERE TABLE_TYPE LIKE 'VIEW';
ユーザー一覧の表示
SELECT Host,User FROM mysql.user;
ロール一覧の表示
SELECT user,host FROM mysql.user WHERE user = 'ro_role';
※ユーザーとロールは属性が違うだけでほぼ同じ。
個別情報の表示
テーブルのレイアウトを表示する
desc テーブル名
SHOW FULL COLUMNS FROM テーブル名;
ユーザーのロールを表示する
SHOW GRANTS FOR ユーザー名@ホスト名;
ファイル入出力
SELECT結果をテキストファイルへエクスポートする
エクスポート先は
MySQLの設定ファイルの my.ini の secure-file-priv に設定されているパスにしか出力できない。
SELECT * FROM テーブル名 INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/ファイル名 ' ;
出力できるパスは「SELECT @@global.secure_file_priv;」で確認する。
(SHOW VARIABLES LIKE “secure_file_priv”; でも確認できる)
Windowsの場合、初期値で「C:/ProgramData/MySQL/MySQL Server 8.0/Uploads」の配下でなければ出力できない。
Windowsの場合、このパス名はフォルダーの区切り文字が「/」になっている。
「\」を区切り文字に指定したパス名では、SQLエラー(1290)が出てしまう。
INTO OUTFILE で指定するパス名の「\」を「/」に変更すると上手く主力できる。
my.ini の secure-file-priv に指定しているパス名を空文字列にすると、どこにでも出力できるようになる。(my.ini変更後にMySQLの再起動が必要)
テキストファイルをインポートする
CSVファイルをインポートするには、以下の命令で指定のテーブルにCSVの内容を取り込む事ができる。
LOAD DATA INFILE 'ファイル名' INTO TABLE テーブル名 FIELDS TERMINATED BY ',';
‘ファイル名’ はフルパス指定が必要であり、エクスポート同様に secure-file-priv に設定されているパスのファイルしか参照できない。
取り込むテキストファイルは文字エンコーディングがMySQLサーバー側と一致している必要がある。
また、UTF-8(utf8mb4等)の場合、テキストファイルはBOM無しUTF-8でなければならない。SQLエラー(1366)が出たら、文字エンコーディングとBOMの有無を確認すると良い。
文字エンコーディングは以下の指令で確認できる。
show variables like 'character%'; -- クライアント側の確認
show global variables like 'character%'; -- サーバー側の確認
例:
LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/test.csv' INTO TABLE tb1 FIELDS TERMINATED BY ',';
作成する
データベースを作成する
CREATE DATABASE データベース名;
SHOW PROCESSLIST;
スキーマを作成する
CREATE DATABASE データベース名;
※MySQLではデータベースとスキーマは同じ。
ユーザーを作成する
通常のユーザー作成
CREATE USER ユーザー名@ホスト名 IDENTIFIED BY 'パスワード';
ローカルインスタンスでユーザー作成する場合は、ホスト名に localhost を指定する。
ユーザーにパスワードを設定する。
SET PASSWORD FOR ユーザー名@ホスト名 = 'パスワード' ;
ログイン中のユーザーにパスワードを設定する。
SET PASSWORD = 'パスワード' ;
ユーザーに直接権限を与える
GRANT 権限名 ON データベース名.* TO ユーザー名@ホスト名 ;
主要なアクセス権限を与えるなら ALL PRIVILEGES 権限 を与える。
ロールを作成する
CREATE ROLE ロール名@ホスト名 ;
GRANT 権限名 ON 対象オブジェクト TO ロール名@ホスト名 ;
ロールをユーザーに割り当てる
GRANT ロール名@ホスト名 TO ユーザー名@ホスト名 ;
ロールを有効化する。
SET DEFAULT ROLE ALL TO ロール名@ホスト名;
SET GLOBAL activate_all_roles_on_login = on;
以下のコマンドで確認する。
SHOW VARIABLES LIKE 'activate_all_roles_on_login';
activate_all_roles_on_login の値が ON になっていれば良い。
テーブルを作成する
最小 CREATE TABLE
CREATE TABLE テーブル名(
列名1 データ型 [オプション],
列名2 データ型 [オプション] ,
.... ,
列名N データ型 [オプション]
);
プライマリーキー１つ設定
CREATE TABLE テーブル名(
列名1 データ型 NOT NULL PRIMARY KEY ,
列名2 データ型 [オプション]
.... ,
列名N データ型 [オプション]
);
プライマリーキー２つ設定
CREATE TABLE テーブル名(
列名1 データ型 [オプション],
列名2 データ型 [オプション] ,
.... ,
列名N データ型 [オプション] ,
PRIMARY KEY (列名1 , 列名2)
);
外部キー設定
CREATE TABLE テーブル名(
列名1 データ型 [オプション],
列名2 データ型 [オプション] ,
.... ,
列名N データ型 [オプション] ,
FOREIGN KEY [インデックス名] (列名1, ....) REFERENCES 対象テーブル名(列名, ....)
);
文字列データ型
CHAR(文字数)
VARCHAR(文字数) 最大16,383文字まで。
TEXT
文字エンコーディングについて
デフォルトの文字エンコーディングはUTF-8である。
UTF-16だけを使用するNCHARやNVARCHARは存在しない。
文字エンコーディングは、以下のようにデータベース単位かカラム単位で指定する。
CREATE DATABASE データベース名 CHARACTER SET 文字エンコーディング;
CREATE TABLE テーブル名 (
カラム名1 データ型1 CHARACTER SET 文字エンコーディング,
カラム名2 データ型2 CHARACTER SET 文字エンコーディング,
...
);
ALTER TABLE テーブル名 MODIFY カラム名 データ型 CHARACTER SET 文字エンコーディング;
数値データ型
真数値
TINYINT 1バイト
SMALLINT 2バイト
MEDIUMINT 3バイト
INTEGER (または INT) 4バイト
BIGINT 8バイト
DECIMAL、NUMERIC (総桁数, 少数点以下桁数) 総桁数の最大値は65。
概数値
FLOAT 4バイト
DOUBLE 8バイト
日時データ型
DATE 日付のみ
TIME 時刻のみ
YEAR 年のみ
DATETIME 日付時刻、範囲は ‘1000-01-01 00:00:00’ から ‘9999-12-31 23:59:59’
TIMESTAMP 日付時刻、UTC方式なので’1970-01-01 00:00:01’から ‘2038-01-19 03:14:07’
DATETIMEとTIMESTAMPは時刻情報を秒の小数点以下6桁まで保有する。両方ともタイムゾーンの変換に対応する。
バイナリデータ型
BINARY(バイト数) 右側が0x00 (ゼロバイト)値で埋められます。
VARBINARY(バイト数)
BLOB
ビューを作成する
CREATE VIEW ビュー名 AS SELECT 列名1, 列名2, … FROM 対象テーブル名;
インデックスを作成する
CREATE INDEX インデックス名 ON テーブル名 (列名, …)
ストアド・プロシージャを作成する
デリミタの設定に特徴が有る。
通常はSQLのデリミタはセミコロン(;)だが、procedure や function の中のセミコロンで create procedure の命令が終わってしまうと、二行以上のロジックが作れなくなる。
そこで create procedure の命令の間だけ、デリミタを二つのスラッシュ(//)に変更する。
終わったらセミコロン(;)に戻す。
delimiter //
CREATE PROCEDURE プロシージャ名(引数名1 データ型 , 引数名2 データ型 , ... )
BEGIN
SQL文1;
SQL文2;
SQL文N;
END;
//
delimiter ;
例:
DROP PROCEDURE if EXISTS sample;
delimiter //
CREATE PROCEDURE sample()
BEGIN
SELECT * FROM m_user;
END;
//
delimiter ;
呼び出すときは、
CALL sample();
ストアド・ファンクションを作成する
デリミタの設定に関しては procedure と同じ。
function の場合、セキュリティ上`DETERMINISTIC`、`NO SQL`、または `READS SQL DATA` のいずれかの属性を追加する必要がある。
`DETERMINISTIC`は、関数の実行結果が同じ入力に対して常に同じ結果を返すことを示す。
`NO SQL`は、関数がデータベース内のデータを変更しないことを示す。
`READS SQL DATA`は、関数がデータベースからデータを読み取るだけであることを示す。
これらの指定により、関数がデータベース内の状態に影響を及ぼすかどうかを明確に示し、バイナリログの安全性を確保する。
これらの指定をしない場合は、log_bin_trust_function_creators の値を 1 に設定して、バイナリログへの記録を停止する。邪道なので、推奨されない。
delimiter //
CREATE FUNCTION 関数名 (引数名1 データ型, 引数名2 データ型, .... )
RETURNS 返り値データ型
[ DETERMINISTIC , NO SQL , READS SQL DATA ]
BEGIN
DECLARE ローカル変数名 データ型名;
SQL文1 ;
RETURN ローカル変数名 ;
END;
//
delimiter ;
例:
SET GLOBAL log_bin_trust_function_creators = 1;
DROP FUNCTION if EXISTS dispuser;
delimiter //
CREATE FUNCTION dispuser(uid bigint)
RETURNS VARCHAR(30)
READS SQL DATA
BEGIN
DECLARE uname varchar(30);
SELECT username INTO uname FROM m_user WHERE id = uid;
RETURN uname;
END;
//
delimiter ;
リテラル・代入・演算子
リテラル
文字列は単一引用符か二重引用符で囲む。
文字列中で単一引用符を使用する場合は、単一引用符を二つ続けて書く。
例: ’text’ , ‘I”m fine.’
変数
DECLARE [変数名] [データ型] DEFAULT [初期値];
例:
BEGIN
DECLARE xname VARCHAR(5) DEFAULT 'bob';
DECLARE newname VARCHAR(5);
DECLARE xid INT;
代入
SET [変数名] = [値];
演算子
加算 A + B
減算 A – B
乗算 A * B
除算 A / B
剰余 A % B
文字列連結 CONCAT( 文字列A , 文字列B )
「AがBより小さい」 A < B
「AはB以下」 A <= B
「AがBより大きい」 A > B
「AはB以上」 A >= B
「AとBは等しい」 A = B
「AとBは等しい(NULL対応)」 A <=> B
「AとBは等しくない」 A != B または A <> B
論理演算
AND , OR , NOT
(括弧)の使用は任意でよい。
条件式やループなど
条件式
if else 相当
IF 条件式 THEN 処理
[ELSEIF 条件式 THEN 処理] ...
[ELSE 処理]
END IF
switch相当
CASE [評価変数]
WHEN 評価値1 THEN 処理
[WHEN 評価値N THEN 処理] ...
[ELSE 処理]
END CASE
ループ1
[開始ラベル:] LOOP
処理
END LOOP [終了ラベル];
ループ2
[開始ラベル:] WHILE 条件式 DO
処理
END WHILE [終了ラベル];
ループ3
[開始ラベル:] REPEAT
処理
UNTIL 条件式
END REPEAT [終了ラベル]
ループ制御
break相当
LOOPから途中で離脱するなら、LEAVEを使用する。C言語の break; に相当する。
LEAVE ラベル名
continue相当
LOOPの先頭からやり直すなら、ITERATEを使用する。C言語の continue; に相当する。
ITERATE ラベル名
ストアドの例外処理の書き方
例外処理の定義方法
DECLARE ハンドラータイプ HANDLER FOR 例外条件 [条件の引数] 例外処理 ;
○ハンドラータイプ
CONTINUE : 例外処理実行後、元の処理を継続する。
EXIT : 例外処理実行後、プログラムを終了する。
UNDO : 未サポート
○例外条件
SQLSTATE [値] SQL状態値 : 固定定義されたSQL-STATEを捉える。
SQLWARNING : 01 で始まるSQL-STATEを捉える。
NOT FOUND : 02 で始まるSQL-STATEを捉える。
SQLEXCEPTION : 01, 02 以外で始まるSQL-STATEを捉える。
条件名 : DECLARE 条件名 CONDITION で定義した例外を捉える。
mySQL固有のエラーコード : 特定のMySQL固有エラーコードを捉える。
○例外処理
例外を捉えたときに、実行する処理を記述する。
SQL一つでも良く、BEGINとENDで囲んで複数の処理を書くこともできる。
DECLARE 条件名 CONDITION FOR 条件値 ;
○条件名
SQLSTATE [値] SQL状態値 : 固定定義されたSQL-STATEを捉える。
mySQL固有のエラーコード : 特定のMySQL固有エラーコードを捉える。
例:
-- INSERT INTO sample_table if EXISTS sample_procedure;
delimiter //
CREATE PROCEDURE sample_procedure()
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SELECT 'An error occurred';
END;
START TRANSACTION;
INSERT INTO sample_table VALUES (1, 'sample');
COMMIT;
END;
//
delimiter ;
-- DROP FUNCTION if EXISTS dispuser;
delimiter //
CREATE PROCEDURE p1()
BEGIN
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02'
SELECT 'SQLSTATE handler was activated' AS msg;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
SELECT 'SQLEXCEPTION handler was activated' AS msg;
DROP TABLE test.t;
END;//
delimiter ;
意図的に例外を発生する方法
プログラム中で、意図的に例外を発生させる場合は、SIGNAL 命令を使用する。
SIGNAL [SQLSTATE 'オプション'] SET MESSAGE_TEXT = '例外メッセージ';
SQLSTATE ‘オプション’: 例外オプション。この部分にはカスタムのエラーコード（SQLSTATE）を指定する。通常、標準のSQLエラーコード（例: ‘45000’）を使用するが、カスタムコードを使用することもできる。
MESSAGE_TEXT = ‘例外メッセージ’: 必須。エラーメッセージを指定する。発生するエラーの内容を説明するメッセージを指定する。
例:
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '例外が発生しました。';
プロシージャでの使用例:
CREATE PROCEDURE reigai(sw int)
BEGIN
-- 例外処理(通常は例外処理を前に書く)
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SELECT 'SW=9 SQLEXCEPTION !';
END;
-- ここに通常のプロシージャの処理を記述します
SELECT 'standard.';
if sw = 1 THEN
SELECT 'SW=1';
ELSEIF sw = 9 THEN
-- 例外を発生させる。
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Custom Exception';
ELSE
SELECT 'OTHER';
END if;
END;
引数に 9 を指定すると、SIGNAL命令により例外を発生し、DECLARE EXIT HANDLER FOR SQLEXCEPTION で定義した例外処理を実行する。
カーソルの書き方
DECLARE カーソル変数 CURSOR FOR select文 ;
OPEN カーソル変数 ;
FETCH カーソル変数 INTO レコード変数 ;
CLOSE カーソル変数 ;
— カーソルループの書き方
DECLARE カーソル変数 CURSOR FOR select文 ;
DECLARE done INT DEFAULT FALSE; -- 終了判定用
OPEN カーソル変数;
readloop : LOOP
FETCH カーソル変数 INTO レコード変数 ;
IF done = TRUE THEN
LEAVE readloop;
END IF;
-- レコードを使用する処理。
END LOOP;
CLOSE カーソル変数;
SQLの方言
コメントの書き方
-- 行コメント
/* コメント開始
複数行コメント
コメント終わり */
SELECT文の方言
結合の省略記法
SELECT 選択列
FROM テーブルA,テーブルB
WHERE 結合条件
内部結合
select * from user_master u, authorization a
where u.USER_ID = a.USER_ID
※省略記法は非推奨です。JOIN句を用いた標準記法を使用すべきです。
INSERT文の方言
列名の省略が可能。
INSERT INTO テーブル名 VALUES(値1 , 値2 , … ) ;
INSERT INTO テーブル名 SELECT * FROM 参照テーブル名;
UPDATE文の方言
特に無し。
DELETE文の方言
特に無し。
オブジェクトの存在確認の方法
mysqlコマンドでは、以下の方法がある。
SHOW TABLES LIKE 'テーブル名';
ストアドプロシージャの中でテーブルの存在確認をするなら、以下のようにする。
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'データベース名'
AND TABLE_NAME = 'オブジェクト名';
プロシージャやファンクションの場合は以下の方法で確認する。
SHOW CREATE PROCEDURE プロシージャ名 ;
SHOW CREATE FUNCTION ファンクション名 ;
SELECT ROUTINE_NAME, ROUTINE_TYPE
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = 'データベース名'
AND ROUTINE_NAME = 'オブジェクト名';
トランザクションの使い方
BEGIN命令でトランザクションを開始し、COMMITまたはROLLBACKで終了する。
トランザクションの開始
BEGIN
または、
START TRANSACTION
コミット
COMMIT
ロールバック
ROLLBACK
