MySQL 文法 まとめ(更新中)

最終更新: 2016/02/25

環境

  • Server version: 5.6.27 Source distribution


データ型の説明

数値型 numeric value type

  • INT -2147483648 ~ 2147483647 (case of no sign 0 ~ 4294967295)
  • TINYINT -128 ~ 127 (case of no sign 0 ~ 255)
  • SMALLINT -32768 ~ 32767 (case of no sign 0 ~ 65535)
  • MEDIUMINT -8388608 ~ 8388607 (case of no sign 0 ~ 16777215)
  • BIGINT -9223372036854775808 ~ 9223372036854775807 (case of no sign 0 ~ 18446744073709551615)
  • FLOAT -3.402823466E+38 ~ 1.175494351E-38
  • DOUBLE -2.2250738585072014E-308 ~ 1.7976931348623157E+308
  • DECIMAL After the decimal point 30 digit ~ maximum 65 digit
  • BINARY(binary_number) fixed length binary string 0 ~ 255 character
  • VARBINARY(binary) variable length binary string 0から65535 byte


BLOB型 blob type

data type of binary data. but do not designate data size, be set maximum length.

  • TINYBLOB maximum length 255 (28 - 1) byte
  • BLOB maximum length 65,535 (216 - 1) byte
  • MEDIUMBLOB maximum length 16,777,215 (224 - 1) byte
  • LONGBLOB maximum length 4,294,967,295、or 4GB (232 - 1) byte


文字列型 string type

  • CHAR fixed length string limited 255 character
  • VARCHAR variable length 1 ~ 65532 byte Shift-JIS limited 255 character
  • TEXT limited 65535 character
  • LONGTEXT limited 4294967295 character

In case of fixed length CHAR all figure length be set when it is stored away to DB, and space is deleted when it is taken.


日付型 date type

  • DATETIME 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
  • DATE 1000-01-01 ~ 9999-12-31
  • YEAR 1901 ~ 2155
  • TIME -838:59:59 ~ 838:59:59


イーナム型 ENUM type

  • ENUM type can set the constant of string
  • case of store the data, you decide the fixed number of string
  • can list value limited 65,535

    ENUM('value1', 'value2',...)


SET type

  • SET type can set the constant of string
  • case of store the data, you can set constant the above 0
  • can list value limited 64

    SET('value1','value2',...)



基本文法の説明 syntax

CREATE

// データベース作成
mysql> CREATE DATABASE database_name;

// テーブル作成
mysql> CREATE TABLE table_name (column_name DATE);
mysql> CREATE TABLE `test` (
    `id` INT AUTO_INCREMENT,
    `date` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `name` VARCHAR(32),
    `approved` TINYINT(1) DEFAULT 0,
    PRIMARY KEY (`id`)
);


RENAME

mysql> RENAME TABLE `before_change_table_name` TO `after_change_table_name`;


SELECT

// scoreの降順、dateの昇順でorder by(ソート)を行う
mysql> SELECT * FROM TABLE order by score DESC, date ASC

// エイリアスを使って別名で表示する
mysql> SELECT column_name AS alias_name FROM table_name;

// aカラムをbananaとしてselectする
mysql> select a as banana, b,c from table_name;
    +------------+------+------+
    | banana     | b    | c    |
    +------------+------+------+
    | 2015-08-15 | NULL | NULL |
    | 2015-08-15 | aaa  |  123 |
    +------------+------+------+
    2 rows in set (0.00 sec)

mysql> select c*100 as 値段, a, b from table_name;
    +---------+------------+------+
    | 値段    | a          | b    |
    +---------+------------+------+
    |    NULL | 2015-08-15 | NULL |
    |   12300 | 2015-08-15 | aaa  |
    | 1100000 | 2015-08-15 | aaa  |
    | 1350000 | 2015-08-15 | abc  |
    +---------+------------+------+
    4 rows in set (0.00 sec)


ALTER TABLE

// カラムとデータ型の追加 add column and data_type
mysql> ALTER TABLE table_name ADD column_name TINYINT;

// データ型の修正 modify data_type
mysql> ALTER TABLE table_name MODIFY column_name INT UNIQUE;

// カラム名とデータ型の変更 change column_name and data_type
mysql> ALTER TABLE table_name CHANGE column_name new_column_name data_type;
mysql> ALTER TABLE test_table CHANGE COLUMN column_name new_column_name TINYINT(1) DEFAULT 0 NOT NULL;

// カラムとデータ型を変更する際、カラム順序も入れ替える when column and data_type change, order change too
mysql> ALTER TABLE table_name ADD column_name data_type FIRST;
mysql> ALTER TABLE table_name ADD column_name_1 data_type AFTER column_name_2;
mysql> ALTER TABLE table_name MODIFY column_name_1 data_type AFTER column_name_2;

// カラムを削除する drop column
mysql> ALTER TABLE table_name DROP column_name;


プライマリキーとユニークキーを設定する Set primary key and unique key

mysql> CREATE TABLE table_name (`id` INT PRIMARY KEY AUTO_INCREMENT NOT NULL, column_name_2 data_type));
mysql> ALTER TABLE table_name MODIFY column_name INT UNIQUE;

一旦プライマリキーを設定したら、カラムを変更する際にプライマリキーの再設定は不要 once you set primary key, when alter column you need not set primary key.


インデックスを設定する Set INDEX

プライマリキーを設定する際、インデックスを自動で設定される when set primary key, set index automatically.

mysql> CREATE INDEX index_name ON table_name(column_name);

mysql> CREATE INDEX ind ON a(c);
mysql> SHOW INDEX FROM a\G
*************************** 1. row ***************************
    Table: a
    Non_unique: 1
    Key_name: ind
    Seq_in_index: 1
    Column_name: c
    Collation: A
    Cardinality: 1
    Sub_part: NULL
    Packed: NULL
    Null: YES
    Index_type: BTREE
    Comment:
Index_comment:
1 row in set (0.00 sec)

mysql> DROP INDEX index_name ON table_name;

mysql> DROP INDEX ind ON a;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW INDEX FROM a;
Empty set (0.00 sec)


INSERT

mysql> INSERT INTO table_name (`id`, `created_at`, `created_by`, `updated_at`, `updated_by`) VALUES ('', NOW(), 'system', NOW(), 'system');
mysql> INSERT INTO table_name (`last_name`, `first_name`) values ('たろう', 'おかもと');


GROUP BY

hrt0kmt.hatenablog.com


テーブルとレコードのコピー Copy table && record

// all table record & structure copy (but may fail some structure)
mysql> CREATE TABLE table_name_1 SELECT * FROM table_name_2;

// copy only column structure not record
mysql> CREATE TABLE table_name_1 LIKE table_name_2;


ステータスを表示する see the status

// サーバーステータスの確認 confirm server status
mysql> show status;

// サーバー変数の確認 confirm server variable
mysql> show variables;

Warningを表示する

mysql> SHOW WARNINGS;

mysql> INSERT INTO test_table (id, date, name, approved) values ('', NOW(), 'okamoto', '');
Query OK, 1 row affected, 2 warnings (0.01 sec)

// この場合は、数値型に文字列型として挿入しているため、warningとなっている
mysql> show warnings;
+---------+------+------------------------------------------------------------+
| Level   | Code | Message                                                    |
+---------+------+------------------------------------------------------------+
| Warning | 1366 | Incorrect integer value: '' for column 'id' at row 1       |
| Warning | 1366 | Incorrect integer value: '' for column 'approved' at row 1 |
+---------+------+------------------------------------------------------------+
2 rows in set (0.00 sec)


プロセス系

プロセスの確認 Which mysql server build or not

% ps aux | grep mysql
    188:_mysql           142   0.0  0.5  3094556  20376   ??  S    Thu09AM   0:24.64 /opt/local/lib/mysql56/bin/mysqld --user=_mysql
    204:root              86   0.0  0.0  2469228    180   ??  Ss   Thu09AM   0:00.04 /opt/local/bin/daemondo --label=mysql56-server --start-cmd /opt/local/lib/mysql56/bin/mysqld --user=_mysql ; --pid=exec
% mysqladmin ping -u root -p
    Enter password:
    mysqld is alive


ユーザーの確認 confirm user

mysql> SELECT host,user FROM mysql.user;
mysql> SELECT * FROM mysql.user \G


権限系

GRANT(権限)の確認 confirm grant

mysql> show grants;
mysql> show grants for 'root'@'localhost'\G


権限の設定 set grant

// rootユーザーのみ権限を付与する valid grant only root user
mysql> UPDATE mysql.user set Grant_priv='Y' where user='root’;

// 設定を反映させる reflection setting
mysql> FLUSH PRIVILEGES;


全ての権限を持つローカルからのみ接続可能なユーザーの作成

mysql> GRANT ALL ON データベース名.* TO ユーザー名@localhost IDENTIFIED BY 'パスワード';
mysql> GRANT ALL ON database_name.* TO root@localhost IDENTIFIED BY 'root';


ユーザーのパスワードを変更する change password

mysql> SET PASSWORD FOR root@"localhost"=PASSWORD('after_change_password');


バックアップ

バックアップを作成する make backup

% mysqldump -u user_name -p database_name > output_file_name
% mysqldump [options] db_name [tables]
% mysqldump [options] --databases db_name1 [db_name2 db_name3...]

// 全てのデータベースをdump.
% mysqldump [options] --all-databases
% mysqldump -u root -x --all-databases > dump.sql

// ホストを指定して外部キーを解除しつつデータベースも指定して `gzip` して圧縮する.
% mysqldump -h host_name -u user_name -p --routines --add-drop-database --disable-keys --databases database_name_1 database_name_2 database_name_3... | gzip > ./dump.gz

--add-drop-database : add DROP DATABASE statement to before CREATE DATABASE statement

--default-character-set=charset_name : set charset_name as default character set. if do not set character, mysqldump use utf8.


復元 restoration

% mysql -u root -p < ~/dump.dump

// ホストを指定してSQL実行.
% mysql -h host_name -u user_name -p < ./dump.sql

// 外部キー制約のエラーなど無視してSQL実行.
% mysql -uroot -pokmt1007 -f database_name < ~/Downloads/dump.sql


ファイル解凍

.gz ファイルの解凍.

% gunzip xxx.sql.gz


リモートサーバーからローカルへダンプする dump file bring from remote server to local

% scp user_name@server_name:/home/xxx/backups/test.sql.gz /Users/hrt0kmt/Desktop


MySQLWorkBench

データベースのsync画面ショートカット: shift + cmd + y

  • PK PRIMARY KEY
  • NN not null check
  • UQ unique unique key
  • BIN binary
  • UN unsigned
  • ZF zero fill
  • AI auto increment
  • uniqueindex do not permit duplication

外部キー制約 foreign key constraint

on update Noaction : how act if parent id changed

  • cascade : delete myself and conditional data
  • set null


MySQLサーバー操作系

MySQLの再起動 reload mysql

% service mysqld reload

hrt0kmt.hatenablog.com


その他

NULLが入っているレコードがある状態でNOT NULLを設定した場合の挙動

mysql> select * from test_table;
+----+---------------------+---------+----------+--------+
| id | date                | name    | approved | sales  |
+----+---------------------+---------+----------+--------+
|  1 | 2015-10-04 11:43:25 | okamoto |        0 |   NULL |
|  2 | 2015-10-04 12:02:14 | tanaka  |        1 | 120000 |
+----+---------------------+---------+----------+--------+
2 rows in set (0.00 sec)

mysql> alter table test_table change sales sales INT not null;
Query OK, 2 rows affected, 1 warning (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 1

// INTにした場合、既存のNULLが0に変わる ※ varcharの場合は''
mysql> select * from test_table;
+----+---------------------+---------+----------+--------+
| id | date                | name    | approved | sales  |
+----+---------------------+---------+----------+--------+
|  1 | 2015-10-04 11:43:25 | okamoto |        0 |      0 |
|  2 | 2015-10-04 12:02:14 | tanaka  |        1 | 120000 |
+----+---------------------+---------+----------+--------+
2 rows in set (0.00 sec)


ランダムデータの生成

mysql> desc test_table;
+----------+--------------+------+-----+-------------------+----------------+
| Field    | Type         | Null | Key | Default           | Extra          |
+----------+--------------+------+-----+-------------------+----------------+
| id       | int(11)      | NO   | PRI | NULL              | auto_increment |
| date     | timestamp    | NO   |     | CURRENT_TIMESTAMP |                |
| name     | varchar(32)  | YES  |     | NULL              |                |
| approved | tinyint(1)   | YES  |     | 0                 |                |
| sales    | int(11)      | NO   |     | NULL              |                |
| memo     | varchar(100) | NO   |     | NULL              |                |
+----------+--------------+------+-----+-------------------+----------------+
6 rows in set (0.00 sec)

// 空のデータを生成
mysql> insert into test_table () values ();
Query OK, 1 row affected, 2 warnings (0.00 sec)

// 2乗に増やしていく
mysql> insert into test_table (id) select 0 from test_table;
Query OK, 6 rows affected, 2 warnings (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 2

// さらに2乗して増やしていく
mysql> insert into test_table (id) select 0 from test_table;
Query OK, 12 rows affected, 2 warnings (0.00 sec)
Records: 12  Duplicates: 0  Warnings: 2

// データを投入する
mysql> update test_table set
    -> date = now(),
    -> name = CONCAT('name', id),
    -> approved = CEIL(RAND() * 2),
    -> sales = CEIL(RAND() * 10000),
    -> memo = SUBSTRING(MD5(RAND()), 1, 30);

mysql> select * from test_table;
+----+---------------------+--------+----------+-------+--------------------------------+
| id | date                | name   | approved | sales | memo                           |
+----+---------------------+--------+----------+-------+--------------------------------+
|  1 | 2015-10-04 12:27:17 | name1  |        2 |   309 | 121078958b35af7ed71689c2b3c958 |
|  2 | 2015-10-04 12:27:17 | name2  |        1 |  4067 | c61b64291d29715f350264b2ba6d3c |
|  3 | 2015-10-04 12:27:17 | name3  |        1 |  7015 | 8ddca355c206106f36134dab2c7fd8 |
|  4 | 2015-10-04 12:27:17 | name4  |        2 |  1371 | 36467c0caf5384af53bb46d30eab83 |
|  5 | 2015-10-04 12:27:17 | name5  |        1 |   246 | a0298f1c128c7e943442229311f3b3 |
|  6 | 2015-10-04 12:27:17 | name6  |        1 |  2635 | f55889f71f3a7a2214e6a379249ef1 |
|  7 | 2015-10-04 12:27:17 | name7  |        2 |  4119 | e9fdcf7f43b3127b59bd372d1fafe7 |
|  8 | 2015-10-04 12:27:17 | name8  |        2 |  8325 | 28d809a8d7e5bc745c0f94616e9d9b |
|  9 | 2015-10-04 12:27:17 | name9  |        2 |  4773 | 5550f8bf3498948606a6d508f8037c |
| 10 | 2015-10-04 12:27:17 | name10 |        2 |  7002 | 75f162635b9813d55e8269cab63f2c |
| 11 | 2015-10-04 12:27:17 | name11 |        2 |  1293 | 332dd215b439a42a199982e63b8476 |
| 12 | 2015-10-04 12:27:17 | name12 |        2 |  4327 | bb0a1a54330df9f8b066ec2c660805 |
| 14 | 2015-10-04 12:27:17 | name14 |        1 |  1213 | e126cf81db72608306d6045ba19ca3 |
| 15 | 2015-10-04 12:27:17 | name15 |        2 |   181 | 7bec38d4e5c69a94bbda63b4270944 |
| 16 | 2015-10-04 12:27:17 | name16 |        2 |  1669 | 72ef577597618eb08219ce6c36718a |
| 17 | 2015-10-04 12:27:17 | name17 |        2 |  8582 | 739c4f46bf6151290bb651de352f94 |
| 18 | 2015-10-04 12:27:17 | name18 |        1 |  1470 | 2f82517f4afc9c47d5cd55dc7c52e1 |
| 19 | 2015-10-04 12:27:17 | name19 |        2 |  5862 | 06a6500d5b158284f3d791e2c6e6ae |
| 20 | 2015-10-04 12:27:17 | name20 |        1 |  9255 | 657f614f3f5f11417111a542fe3af7 |
| 21 | 2015-10-04 12:27:17 | name21 |        1 |  2070 | a4982f2ac07e00761698aa78679055 |
| 22 | 2015-10-04 12:27:17 | name22 |        2 |  5720 | 5cc95e3bdae4a871b1a29f4ab17599 |
| 23 | 2015-10-04 12:27:17 | name23 |        2 |  1658 | 9ec77a55c2159e8436bf14fbc1f125 |
| 24 | 2015-10-04 12:27:17 | name24 |        1 |  8168 | c18b660b031ade1c229f88f13d2b05 |
| 25 | 2015-10-04 12:27:17 | name25 |        1 |  8683 | 52b890a35b4f71155ef620170d61ec |
+----+---------------------+--------+----------+-------+--------------------------------+
24 rows in set (0.00 sec)


参考にさせていただいたサイト

MySQLで簡単にランダムなテストデータを作成する方法 - Qiita