mysql> use sample;
Database changed
mysql> create table Bugs (
    -> status ENUM('NEW', 'IN PROGRESS', 'FIXED')
    -> );
Query OK, 0 rows affected (0.13 sec)

mysql> INSERT INTO Bugs VALUES('NEW');
Query OK, 1 row affected (0.08 sec)

mysql> select * from Bugs;
+--------+
| status |
+--------+
| NEW    |
+--------+
1 row in set (0.01 sec)

mysql> SELECT column_type FROM information_schema.columns WHERE table_schema = 'sample' AND table_name = 'Bugs' AND column_name = 'status';
+-----------------------------------+
| COLUMN_TYPE                       |
+-----------------------------------+
| enum('NEW','IN PROGRESS','FIXED') |
+-----------------------------------+
1 row in set (0.09 sec)

-- 決まった値を管理する BugStatus テーブルを作成する
mysql> CREATE TABLE BugStatus (
    -> status VARCHAR(20) PRIMARY KEY
    -> );
Query OK, 0 rows affected (0.65 sec)

mysql> INSERT INTO BugStatus (status) VALUES ('NEW'), ('IN PROGRESS'), ('FIXED');
Query OK, 3 rows affected (0.18 sec)
Records: 3  Duplicates: 0  Warnings: 0


-- Bug テーブルを別途作成し、status は BugStatus の値を参照するようにする。また `ON UPDATE CASCADE` で BugStatus の更新を Bug テーブルに反映するようにする。
mysql> CREATE TABLE Bugs (
    -> status VARCHAR(20),
    -> FOREIGN KEY (status) REFERENCES BugStatus(status) ON UPDATE CASCADE
    -> );
Query OK, 0 rows affected (0.71 sec)

-- BugStatus に格納されているデータは Bug テーブルにも格納可能
mysql> INSERT INTO Bugs (status) VALUES ('NEW'), ('IN PROGRESS'), ('FIXED');
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

-- BugStatus に格納されていないデータは Bug テーブルにも格納できない
mysql> INSERT INTO Bugs (status) VALUES ('OLD');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`sample`.`Bugs`, CONSTRAINT `Bugs_ibfk_1` FOREIGN KEY (`status`) REFERENCES `BugStatus` (`status`) ON UPDATE CASCADE)

-- BugStatus が UPDATE されると Bug もそれに追従
mysql> select * from BugStatus;
+-------------+
| status      |
+-------------+
| FIXED       |
| IN PROGRESS |
| NEW         |
+-------------+
3 rows in set (0.00 sec)

mysql> INSERT INTO Bugs (status) VALUES ('FIXED');
Query OK, 1 row affected (0.07 sec)

mysql> UPDATE BugStatus SET status = 'DONE' WHERE status = 'FIXED';
Query OK, 1 row affected (0.09 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from BugStatus;
+-------------+
| status      |
+-------------+
| DONE        |
| IN PROGRESS |
| NEW         |
+-------------+
3 rows in set (0.01 sec)

mysql> CREATE TABLE Bugs(
    -> bug_id SERIAL PRIMARY KEY,
    -> data_reported DATE NOT NULL,
    -> summary VARCHAR(80) NOT NULL,
    -> status VARCHAR(10) NOT NULL,
    -> hours NUMERIC(9,2),
    -> INDEX (bug_id), -- 1.
    -> INDEX (summary), -- 2.
    -> INDEX (hours),
    -> INDEX (bug_id, data_reported, status)  -- 3.
    -> );

mysql> CREATE TABLE Accounts(account_id SERIAL PRIMARY KEY, first_name VARCHAR(10) NOT NULL, last_name VARCHAR(10) NOT NULL);
mysql> INSERT INTO Accounts (first_name, last_name) VALUES ('John', 'Smith');
Query OK, 1 row affected (0.13 sec)

mysql> INSERT INTO Accounts (first_name, last_name) VALUES ('Tom', 'Cat');
Query OK, 1 row affected (0.07 sec)

mysql> ALTER TABLE Accounts ADD COLUMN middle_initial CHAR(2);
Query OK, 0 rows affected (0.52 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM Accounts;
+------------+------------+-----------+----------------+
| account_id | first_name | last_name | middle_initial |
+------------+------------+-----------+----------------+
|          1 | John       | Smith     | NULL           |
|          2 | Tom        | Cat       | NULL           |
+------------+------------+-----------+----------------+
2 rows in set (0.77 sec)

mysql> SELECT first_name || ' ' || middle_initial || ' ' || last_name As full_name FROM Accounts;
+-----------+
| full_name |
+-----------+
|      NULL |
|      NULL |
+-----------+
2 rows in set, 8 warnings (0.19 sec)

-- IS NULL
mysql> SELECT * FROM Accounts WHERE middle_initial IS NULL;
+------------+------------+-----------+----------------+
| account_id | first_name | last_name | middle_initial |
+------------+------------+-----------+----------------+
|          1 | John       | Smith     | NULL           |
|          2 | Tom        | Cat       | NULL           |
+------------+------------+-----------+----------------+
2 rows in set (0.01 sec)

-- 以下の2つのクエリは等価
mysql> SELECT * FROM Accounts WHERE middle_initial IS NULL OR middle_initial <> 'J.';
+------------+------------+-----------+----------------+
| account_id | first_name | last_name | middle_initial |
+------------+------------+-----------+----------------+
|          1 | John       | Smith     | NULL           |
|          2 | Tom        | Cat       | NULL           |
+------------+------------+-----------+----------------+
2 rows in set (0.07 sec)

mysql> SELECT * FROM Accounts WHERE NOT (middle_initial <=> 'J.'); -- MySQL では IS NOT DISTINCT FROM と等価な演算子 <=> を提供している
+------------+------------+-----------+----------------+
| account_id | first_name | last_name | middle_initial |
+------------+------------+-----------+----------------+
|          1 | John       | Smith     | NULL           |
|          2 | Tom        | Cat       | NULL           |
+------------+------------+-----------+----------------+
2 rows in set (0.41 sec)

-- ' ' || middle_initial || ' ' の値が NULL の場合、' ' が文字連結される
mysql> SELECT first_name || COALESCE(' ' || middle_initial || ' ', ' ') || last_name As full_name FROM Accounts;
+--------------+
| full_name    |
+--------------+
| John Smith   |
| Tom Cat      |
| Hoge J. Fuga |
| Piyo R. Foo  |
+--------------+
4 rows in set (0.06 sec)

mysql> CREATE TABLE Bugs(bug_id SERIAL PRIMARY KEY, summary VARCHAR(100) NOT NULL, description VARCHAR(100) NOT NULL);
Query OK, 0 rows affected (0.69 sec)

-- フルテキストインデックス作成
mysql> ALTER TABLE Bugs ADD FULLTEXT INDEX bugfts (summary, description);
Query OK, 0 rows affected, 1 warning (0.68 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> INSERT INTO Bugs (summary, description) VALUES ('crash', 'hoge');
Query OK, 1 row affected (0.51 sec)

mysql> INSERT INTO Bugs (summary, description) VALUES ('crashed', 'fuga');
Query OK, 1 row affected (0.01 sec)

-- MATCH 関数で検索を行う
mysql> SELECT * FROM Bugs WHERE MATCH(summary, description) AGAINST ('crash');
+--------+---------+-------------+
| bug_id | summary | description |
+--------+---------+-------------+
|      1 | crash   | hoge        |
+--------+---------+-------------+
1 row in set (0.20 sec)

mysql> CREATE TABLE Issues (
    -> issue_id SERIAL PRIMARY KEY
    -> );
Query OK, 0 rows affected (0.19 sec)

mysql> INSERT INTO Issues (issue_id) VALUES (1234);
Query OK, 1 row affected (0.17 sec)

mysql> CREATE TABLE IssueAttributes (
    -> issue_id BIGINT UNSIGNED NOT NULL,
    -> attr_name VARCHAR(100) NOT NULL,
    -> attr_value VARCHAR(100),
    -> PRIMARY KEY (issue_id, attr_name),
    -> FOREIGN KEY (issue_id) REFERENCES Issues(issue_id)
    -> );
Query OK, 0 rows affected (0.13 sec)

mysql> INSERT INTO IssueAttributes(issue_id, attr_name, attr_value)
    -> VALUES
    -> (1234, 'product', '1'),
    -> (1234, 'date_reported', '2009-06-01');
Query OK, 2 rows affected (0.17 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE Accounts ( account_id SERIAL PRIMARY KEY, account_name VARCHAR(20) );

mysql> CREATE TABLE Products ( product_id SERIAL PRIMARY KEY, product_name VARCHAR(20) );

mysql> CREATE TABLE Issues ( issue_id SERIAL PRIMARY KEY, reported_by BIGINT UNSIGNED NOT NULL, product_id BIGINT UNSIGNED, priority VARCHAR(20), version_resolved VARCHAR(20), status VARCHAR(20), FOREIGN KEY (reported_by) REFERENCES Accounts(account_id), FOREIGN KEY (product_id) REFERENCES Products(product_id) );

mysql> CREATE TABLE Bugs ( issue_id BIGINT UNSIGNED PRIMARY KEY, severity VARCHAR(20), version_affected VARCHAR(20), FOREIGN KEY (issue_id) REFERENCES Issues(issue_id) );

mysql> CREATE TABLE FeatureRequests ( issue_id BIGINT UNSIGNED PRIMARY KEY, sponsor VARCHAR(50), FOREIGN KEY (issue_id) REFERENCES Issues(issue_id) );

mysql> INSERT INTO Accounts (account_name) VALUES ('Taro');

mysql> INSERT INTO Products (product_name) VALUES ('Product1');

mysql> INSERT INTO Accounts (account_name) VALUES ('Jiro');

mysql> INSERT INTO Issues (reported_by, product_id, priority, version_resolved, status) VALUES (1, 1, 'High', 'version1', 'IN PROGRESS');

mysql> INSERT INTO Bugs (issue_id, severity, version_affected) VALUES (LAST_INSERT_ID(), 'Warn', 'version1');

mysql> INSERT INTO Issues (reported_by, product_id, priority, version_resolved, status) VALUES (2, 1, 'Low', 'version2', 'DONE');

-- LAST_INSERT_ID()　の値はセッションに格納されているので、以下のクエリよりも前に他から Issues に対して INSERT が実行されても LAST_INSERT_ID() の値は変更されない
mysql> INSERT INTO FeatureRequests (issue_id, sponsor) VALUES (LAST_INSERT_ID(), 'sponsor1');

mysql> SELECT i.*, b.*, f.* FROM Issues As i LEFT OUTER JOIN Bugs AS b USING(issue_id) LEFT OUTER JOIN FeatureRequests AS f USING(issue_id);

+----------+-------------+------------+----------+------------------+-------------+----------+----------+------------------+----------+----------+
| issue_id | reported_by | product_id | priority | version_resolved | status      | issue_id | severity | version_affected | issue_id | sponsor  |
+----------+-------------+------------+----------+------------------+-------------+----------+----------+------------------+----------+----------+
|        1 |           1 |          1 | High     | version1         | IN PROGRESS |        1 | Warn     | version1         |     NULL | NULL     |
|        2 |           2 |          1 | Low      | version2         | DONE        |     NULL | NULL     | NULL             |        2 | sponsor1 |
+----------+-------------+------------+----------+------------------+-------------+----------+----------+------------------+----------+----------+
2 rows in set (0.05 sec)