MySQL 5.6 Developer試験 公式「試験内容 チェックリスト」
[https://education.oracle.com/ja/mysql-56-developer/pexam_1Z0-882:embed:cite]
MySQL 5.6 リファレンスマニュアル
[https://dev.mysql.com/doc/refman/5.6/ja/:embed:cite]
MySQL ServerとのインターフェースであるMySQL クライアントプログラムを使用して、対話的な方法およびバッチでアクセスする
[https://dev.mysql.com/doc/refman/5.6/ja/tutorial.html:embed:cite]
dockerでうごかすまで
docker container run --rm -dit \
--name mysql \
-e MYSQL_ROOT_PASSWORD=root \
mysql:5.6
docker container exec -it mysql bash
root@c9b2970c51d2:/# mysql -proot
mysql -proot
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.48 MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
- ヘルプ
mysql> \! mysql --help
\! mysql --help
mysql Ver 14.14 Distrib 5.6.48, for Linux (x86_64) using EditLine wrapper
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Usage: mysql [OPTIONS] [database]
サーバーへの接続とサーバーからの切断
root@c9b2970c51d2:/# mysql -h localhost -u root -p
Enter password: (入力)
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.48 MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
-
【所感】rootで入れるんですね
- postgresqlは入れない
- 【補】
-h localhost
はUnixドメインソケットに解決されることに注意する - 前述のコンテナの立ち上げ方で
mysql
だけ打つとAccess denied
root@c9b2970c51d2:/# mysql
mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
- 環境変数なしで同期で立ち上げてログを見てみる
docker run --rm mysql:5.6
2020-05-21 15:30:51+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 5.6.48-1debian9 started.
2020-05-21 15:30:51+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
2020-05-21 15:30:51+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 5.6.48-1debian9 started.
2020-05-21 15:30:51+00:00 [ERROR] [Entrypoint]: Database is uninitialized and password option is not specified
You need to specify one of MYSQL_ROOT_PASSWORD, MYSQL_ALLOW_EMPTY_PASSWORD and MYSQL_RANDOM_ROOT_PASSWORD
You need to specify one of MYSQL_ROOT_PASSWORD, MYSQL_ALLOW_EMPTY_PASSWORD and MYSQL_RANDOM_ROOT_PASSWORD
MYSQL_ALLOW_EMPTY_PASSWORD=true
を設定して立ち上げてみる
docker run --rm -dit \
--name=mysql \
-e MYSQL_ALLOW_EMPTY_PASSWORD=true \
mysql:5.6
docker container exec -it mysql bash
mysql
だけで入れる
root@f973197c71b6:/# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.48 MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
-
\q
,QUIT
, UnixならCtrl + D
で出る- 【補】
EXIT
可
- 【補】
クエリーの入力
mysql> SELECT VERSION(), CURRENT_DATE;
SELECT VERSION(), CURRENT_DATE;
+-----------+--------------+
| VERSION() | CURRENT_DATE |
+-----------+--------------+
| 5.6.48 | 2020-05-21 |
+-----------+--------------+
1 row in set (0.00 sec)
- 【補】
CURRENT_DATE
にカッコが付かないのは標準SQL準拠 - 計算機にもなる
mysql> SELECT SIN(PI() / 4), (4 + 1) * 5;
SELECT SIN(PI() / 4), (4 + 1) * 5;
+--------------------+-------------+
| SIN(PI() / 4) | (4 + 1) * 5 |
+--------------------+-------------+
| 0.7071067811865475 | 25 |
+--------------------+-------------+
1 row in set (0.00 sec)
- 1行に複数クエリ
mysql> SELECT VERSION(); SELECT NOW();
SELECT VERSION(); SELECT NOW();
+-----------+
| VERSION() |
+-----------+
| 5.6.48 |
+-----------+
1 row in set (0.00 sec)
+---------------------+
| NOW() |
+---------------------+
| 2020-05-21 15:51:13 |
+---------------------+
1 row in set (0.00 sec)
- 逆に1クエリを複数行にまたがって書くこともできる
mysql> SELECT USER()
SELECT USER()
-> ;
;
+----------------+
| USER() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
- やめたくなったら
\c
clear (\c) Clear the current input statement.
mysql> SELECT
SELECT
-> USER()
USER()
-> ,
,
-> \c
\c
mysql>
- コメント
mysql> SELECT /* hoge */ 1;
SELECT /* hoge */ 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
データベースの作成と使用
mysql> SHOW DATABASES;
SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
- 【補】
information_schema
は標準SQL
mysql> USE information_schema
USE information_schema
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> SHOW TABLES;
SHOW TABLES;
+---------------------------------------+
| Tables_in_information_schema |
+---------------------------------------+
| CHARACTER_SETS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS |
| COLUMN_PRIVILEGES |
| ENGINES |
...
データベースの作成と選択
mysql> CREATE DATABASE managerie;
CREATE DATABASE managerie;
Query OK, 1 row affected (0.00 sec)
mysql> SHOW DATABASES;
SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| managerie |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)
mysql> USE managerie;
USE managerie;
Database changed
-
ログイン時にDB指定もできる
- オプション無しの引数
-proot
のように書かないといけない理由
root@f973197c71b6:/# mysql managerie
mysql> SELECT database();
SELECT database();
+------------+
| database() |
+------------+
| managerie |
+------------+
1 row in set (0.00 sec)
mysql> SHOW TABLES;
SHOW TABLES;
Empty set (0.00 sec)
mysql> CREATE TABLE tbl (col int);
CREATE TABLE tbl (col int);
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO tbl (col) VALUES (1);
INSERT INTO tbl (col) VALUES (1);
Query OK, 1 row affected (0.00 sec)
mysql> TABLE tbl;
TABLE tbl;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TABLE tbl' at line 1
mysql> SELECT * FROM tbl;
SELECT * FROM tbl;
+------+
| col |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
mysql>
- 【補】TABLE文はない
-
【補】
generate_series
もない- パフォーマンスチューニングの学習とか面倒そう…
テーブルへのデータのロード
-
LOAD DATA
ステートメントを使う- 【補】PostgreSQLの
COPY FROM
,\copy from
にあたる
- 【補】PostgreSQLの
mysql> \h LOAD DATA
Name: 'LOAD DATA'
Description:
Syntax:
LOAD DATA
[LOW_PRIORITY | CONCURRENT] [LOCAL]
INFILE 'file_name'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[PARTITION (partition_name [, partition_name] ...)]
[CHARACTER SET charset_name]
[{FIELDS | COLUMNS}
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char']
]
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']
]
[IGNORE number {LINES | ROWS}]
[(col_name_or_user_var
[, col_name_or_user_var] ...)]
[SET col_name={expr | DEFAULT},
[, col_name={expr | DEFAULT}] ...]
The LOAD DATA statement reads rows from a text file into a table at a
very high speed. LOAD DATA is the complement of SELECT ... INTO
OUTFILE. (See
https://dev.mysql.com/doc/refman/5.6/en/select-into.html.) To write
data from a table to a file, use SELECT ... INTO OUTFILE. To read the
file back into a table, use LOAD DATA. The syntax of the FIELDS and
LINES clauses is the same for both statements.
You can also load data files by using the mysqlimport utility; see
https://dev.mysql.com/doc/refman/5.6/en/mysqlimport.html. mysqlimport
operates by sending a LOAD DATA statement to the server.
For more information about the efficiency of INSERT versus LOAD DATA
and speeding up LOAD DATA, see
https://dev.mysql.com/doc/refman/5.6/en/insert-optimization.html.
URL: https://dev.mysql.com/doc/refman/5.6/en/load-data.html
- 【補】
COPY TO
にあたるものはSELECT ... INTO OUTFILE
-
【補】
LOCAL
: クライアント側のファイルをロードすることを意味する- つけないとサーバ側
- やってみる
- 準備
mysql> DROP TABLE tbl;
DROP TABLE tbl;
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE tbl (id int, name varchar(20));
CREATE TABLE tbl (id int, name varchar(20));
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT * FROM tbl;
SELECT * FROM tbl;
Empty set (0.00 sec)
-
投入データ作る
- タブ区切り
- NULLは
\N
mysql> \! echo '1\thoge' > data.txt
mysql> \! echo '2\tpiyo' >> data.txt
mysql> \! echo '3\t\\N' >> data.txt
mysql> \! cat /data.txt
1 hoge
2 piyo
3 \N
LOAD DATA
ステートメントを実行するもエラー
mysql> LOAD DATA INFILE 'data.txt' INTO TABLE tbl;
LOAD DATA INFILE 'data.txt' INTO TABLE tbl;
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
-
--secure-file-priv
オプションがついてるから駄目らしい- スコープ: グローバル
- 動的: no
- my.cnfで設定を行う
- どこ?
mysql --help | grep my.cnf
order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf
- 本docker環境では
/etc/mysql/my.cnf
にあった
/docker:mysql:/etc/mysql $ ls
conf.d my.cnf my.cnf.fallback mysql.cnf mysql.conf.d
/etc/mysql/my.cnf
# * IMPORTANT: Additional settings that can override those from this file!
# The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/
- どこかの
[mysqld]
設定に追記
[mysqld]
secure-file-priv = ""
- 書き換えるだけでは駄目
mysql> LOAD DATA INFILE 'data.txt' INTO TABLE tbl;
LOAD DATA INFILE 'data.txt' INTO TABLE tbl;
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
- SIGHUPしてみる
kill -s SIGHUP 1
- 駄目
mysql> LOAD DATA INFILE 'data.txt' INTO TABLE tbl;
LOAD DATA INFILE 'data.txt' INTO TABLE tbl;
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
- restartが必要
docker container restart mysql
- あらためて
mysql> LOAD DATA INFILE 'data.txt' INTO TABLE tbl;
LOAD DATA INFILE 'data.txt' INTO TABLE tbl;
ERROR 13 (HY000): Can't get stat of '/var/lib/mysql/managerie/data.txt' (Errcode: 2 - No such file or directory)
--secure-file-priv
のエラーは解消した-
「そんなファイルねえぞ」と言われる
- カレントディレクトリ =
/
にdata.txtを作った LOAD DATA INFILE
で相対パスを指定すると、データベースディレクトリ/var/lib/mysql/managerie/
からの相対パスになる
- カレントディレクトリ =
- 絶対パスで指定し直す
mysql> LOAD DATA INFILE '/data.txt' INTO TABLE tbl;
LOAD DATA INFILE '/data.txt' INTO TABLE tbl;
Query OK, 3 rows affected (0.01 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
- OK
mysql> SELECT * FROM tbl;
SELECT * FROM tbl;
+------+------+
| id | name |
+------+------+
| 1 | hoge |
| 2 | piyo |
| 3 | NULL |
+------+------+
3 rows in set (0.00 sec)
ならべかえ
- 【補】
ORDER BY
にNULLS {FIRST|LAST}
とかはない
日付の計算
- 2つのtimepointの差
mysql> SELECT CURDATE(),
SELECT CURDATE(),
-> TIMESTAMPDIFF(YEAR, '2020-04-04', CURDATE()),
TIMESTAMPDIFF(YEAR, '2020-04-04', CURDATE()),
-> TIMESTAMPDIFF(MONTH, '2020-04-04', CURDATE()),
TIMESTAMPDIFF(MONTH, '2020-04-04', CURDATE()),
-> TIMESTAMPDIFF(DAY, '2020-04-04', CURDATE());
TIMESTAMPDIFF(DAY, '2020-04-04', CURDATE());
+------------+----------------------------------------------+-----------------------------------------------+---------------------------------------------+
| CURDATE() | TIMESTAMPDIFF(YEAR, '2020-04-04', CURDATE()) | TIMESTAMPDIFF(MONTH, '2020-04-04', CURDATE()) | TIMESTAMPDIFF(DAY, '2020-04-04', CURDATE()) |
+------------+----------------------------------------------+-----------------------------------------------+---------------------------------------------+
| 2020-05-21 | 0 | 1 | 47 |
+------------+----------------------------------------------+-----------------------------------------------+---------------------------------------------+
1 row in set (0.00 sec)
- timepoint + interval
mysql> SELECT CURDATE(), DATE_ADD(CURDATE(), INTERVAL 1 MONTH);
SELECT CURDATE(), DATE_ADD(CURDATE(), INTERVAL 1 MONTH);
+------------+---------------------------------------+
| CURDATE() | DATE_ADD(CURDATE(), INTERVAL 1 MONTH) |
+------------+---------------------------------------+
| 2020-05-21 | 2020-06-21 |
+------------+---------------------------------------+
1 row in set (0.00 sec)
- 抽出
mysql> SELECT YEAR(CURDATE()), MONTH(CURDATE()), DAY(CURDATE());
SELECT YEAR(CURDATE()), MONTH(CURDATE()), DAY(CURDATE());
+-----------------+------------------+----------------+
| YEAR(CURDATE()) | MONTH(CURDATE()) | DAY(CURDATE()) |
+-----------------+------------------+----------------+
| 2020 | 5 | 21 |
+-----------------+------------------+----------------+
1 row in set (0.00 sec)
データベースとテーブルに関する情報の取得
mysql> SELECT VERSION();
SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.6.48 |
+-----------+
1 row in set (0.00 sec)
mysql> DESCRIBE tbl;
DESCRIBE tbl;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
- cf. CREATE TABLE文の取得
mysql> SHOW CREATE TABLE tbl;
SHOW CREATE TABLE tbl;
+-------+----------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------+
| tbl | CREATE TABLE `tbl` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
-
index見る
- まだ貼ってない
mysql> SHOW INDEX FROM tbl;
SHOW INDEX FROM tbl;
Empty set (0.00 sec)
バッチモードでのMySQLの使用
- mysqlコマンドにstdinでSQLファイルを流し込める
cat sample.sql
SELECT 1;
mysql < sample.sql
- 出力は簡潔
1
1
- cf. インタラクティブモード
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
- バッチモードでもインタラクティブモードと同じ表示形式(table format)を用いるには、
-t, --table
オプションをつける
-t, —table Output in table format.
mysql -t < sample.sql
+---+
| 1 |
+---+
| 1 |
+---+
- 実行したコマンドをstdoutに出力するには
-vvv
mysql -t -vvv < sample.sql
--------------
SELECT 1
--------------
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
Bye
SQLモードと、MySQLの動作に対するSQLモードの影響について説明する
[https://dev.mysql.com/doc/refman/5.6/ja/sql-mode.html:embed:cite]
MySQL Server は異なる SQL モードで動作でき、
sql_mode システム変数の値に応じて異なるクライアントにこれらの異なるモードを適用できます。 DBA はサイトサーバーの動作要件に一致するグローバル SQL モードを設定でき、
各アプリケーションはアプリケーションのセッション SQL モードをアプリケーション独自の要件に設定できます。
- デフォルト値について
MySQL 5.6.6 以降でのデフォルトの SQL モードは
NO_ENGINE_SUBSTITUTION
で、 MySQL 5.6.5 以前では、これは空白です (モードの設定なし)。
- 確認
mysql> SELECT VERSION();
SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.6.48 |
+-----------+
1 row in set (0.00 sec)
mysql> SELECT @@GLOBAL.sql_mode, @@SESSION.sql_mode;
SELECT @@GLOBAL.sql_mode, @@SESSION.sql_mode;
+------------------------+------------------------+
| @@GLOBAL.sql_mode | @@SESSION.sql_mode |
+------------------------+------------------------+
| NO_ENGINE_SUBSTITUTION | NO_ENGINE_SUBSTITUTION |
+------------------------+------------------------+
1 row in set (0.00 sec)
-
注意
- パーティショニングしたテーブルの作成・データ投入後にSQLモードを変えないほうが良い
- レプリケーション時はマスターとスレーブで揃える
-
【補】8.0のドキュメントでも「slave」と言っている
- PostgreSQLは10を境にsecondary, standbyなどと呼ぶようになった
-
どんなのがあるの
NO_ZERO_DATE
0000-00-00
を拒否するやつONLY_FULL_GROUP_BY
- SQL AntipatternsのAmbiguous Groupsなクエリを拒否するやつ
- 他、識別子のクォートや文字列concat演算子なども変わる
-
他RDBMSに寄り添う組み合わせのプリセットが用意されていたりする
- ANSI
- MSSQL
- ORACLE
- POSTGRESQL
- …
セッションのスコープ(有効範囲)を識別する
[https://dev.mysql.com/doc/refman/5.6/ja/server-system-variables.html:embed:cite]
- オプションファイル(my.cnf)でサーバー起動時に設定できる
- サーバーの実行中にSETステートメントで動的に変更できる
mysql> SELECT @@GLOBAL.sort_buffer_size;
SELECT @@GLOBAL.sort_buffer_size;
+---------------------------+
| @@GLOBAL.sort_buffer_size |
+---------------------------+
| 262144 |
+---------------------------+
1 row in set (0.00 sec)
- 見るからにパフォーマンスチューニングに使いそうなサーバーシステム変数
- globalで2倍にしてみる
mysql> SELECT 262144 * 2;
SELECT 262144 * 2;
+------------+
| 262144 * 2 |
+------------+
| 524288 |
+------------+
1 row in set (0.00 sec)
mysql> SET GLOBAL sort_buffer_size=524288;
SET GLOBAL sort_buffer_size=524288;
Query OK, 0 rows affected (0.00 sec)
- 確認
mysql> SELECT @@GLOBAL.sort_buffer_size, @@SESSION.sort_buffer_size;
SELECT @@GLOBAL.sort_buffer_size, @@SESSION.sort_buffer_size;
+---------------------------+----------------------------+
| @@GLOBAL.sort_buffer_size | @@SESSION.sort_buffer_size |
+---------------------------+----------------------------+
| 524288 | 262144 |
+---------------------------+----------------------------+
1 row in set (0.00 sec)
- セッションの値は変わらない
- グローバルの値は変わった
- ログアウトして再度ログイン
mysql> SELECT @@GLOBAL.sort_buffer_size, @@SESSION.sort_buffer_size;
SELECT @@GLOBAL.sort_buffer_size, @@SESSION.sort_buffer_size;
+---------------------------+----------------------------+
| @@GLOBAL.sort_buffer_size | @@SESSION.sort_buffer_size |
+---------------------------+----------------------------+
| 524288 | 524288 |
+---------------------------+----------------------------+
1 row in set (0.00 sec)
- 新しいセッションではグローバルの値が読まれる
- 【所感】SIGHUPやreloadによるサーバ無停止オプションファイル再読込がない代わりに、PostgreSQLでいうところの
ALTER SYSTEM SET
がある感じ - グローバルスコープのみのサーバーシステム変数を
@@SESSION
で読もうとするとエラー
mysql> SELECT @@SESSION.secure_file_priv;
SELECT @@SESSION.secure_file_priv;
ERROR 1238 (HY000): Variable 'secure_file_priv' is a GLOBAL variable
- 動的でないものを書き換えようとするとエラー
mysql> SET GLOBAL secure_file_priv = 'true';
SET GLOBAL secure_file_priv = 'true';
ERROR 1238 (HY000): Variable 'secure_file_priv' is a read only variable