記事の内容
概要
CREATE文を使用すると、データベース・テーブル・ビュー・インデックスなどを作成できます。
データベース
CREATE
記述
CREATE DATABASE データベース名;
【DB一覧】
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| sample |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
6 rows in set (0.01 sec)
【DB作成】
mysql> CREATE DATABASE test2;
Query OK, 1 row affected (0.01 sec)
【DB一覧】
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| sample |
| mysql |
| performance_schema |
| sys |
| test |
| test2 |
+--------------------+
7 rows in set (0.01 sec)テーブル
CREATE(テーブル)
記述
CREATE TABLE テーブル名 (
カラム名 データタイプ 制約,
…..
PRIMARY KEY (1つ以上指定)
);
【テーブル一覧】
mysql> SHOW TABLES;
+-----------------------+
| Tables_in_testDB |
+-----------------------+
| cache |
| cache_locks |
| failed_jobs |
| job_batches |
| jobs |
| migrations |
| password_reset_tokens |
| sessions |
| users |
+-----------------------+
9 rows in set (0.01 sec)
【informationsテーブル作成】
mysql> CREATE TABLE informations (
-> id INT NOT NULL AUTO_INCREMENT,
-> name text NOT NULL,
-> created_at DATETIME DEFAULT current_timestamp,
-> updated_at DATETIME DEFAULT current_timestamp on update current_timestamp,
-> PRIMARY KEY (id)
-> );
Query OK, 0 rows affected (0.02 sec)
【テーブル一覧】
mysql> SHOW TABLES;
+-----------------------+
| Tables_in_testDB |
+-----------------------+
| cache |
| cache_locks |
| failed_jobs |
| informations |
| job_batches |
| jobs |
| migrations |
| password_reset_tokens |
| sessions |
| users |
+-----------------------+
10 rows in set (0.01 sec)
【作成テーブルの情報表示】
mysql> SHOW COLUMNS FROM informations;
+------------+----------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------+------+-----+-------------------+-----------------------------------------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | text | NO | | NULL | |
| created_at | datetime | YES | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| updated_at | datetime | YES | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+------------+----------+------+-----+-------------------+-----------------------------------------------+
4 rows in set (0.00 sec)
DEFAULT値
INSERT時に指定がなかった場合にデフォルトで入る値や文字列になります。
※指定がなければNULLが入る
【例】
create table test (
id INT NOT NULL AUTO_INCREMENT,
name text DEFAULT "abc" NOT NULL,
PRIMARY KEY (id)
);NULL / NOT NULL制約
属性としてNULLの許可有無を指定します。
【例】
create table test (
id INT NOT NULL AUTO_INCREMENT,
name text NULL,
PRIMARY KEY (id)
);CHECK制約
条件式を設定して有効な値なのかチェックして作成・更新を実施します。
【例】
create table test (
id INT NOT NULL AUTO_INCREMENT,
age INT CHECK(age > 20 AND age <= 40) NOT NULL,
PRIMARY KEY (id)
);UNIQUE制約
列の値での重複を認めない設定になります。
【例】
create table test (
id INT NOT NULL UNIQUE,
name varchar(20) NOT NULL,
PRIMARY KEY (id)
);ビュー
VIEWは、データの参照やクエリの簡略化しアクセス制御の強化などの目的で使用されます。
CREATE(ビュー)
記述
CREATE VIEW ビュー名 AS SELECT カラム名 FROM テーブル名;
【テーブル一覧】
mysql> SHOW TABLES;
+------------------------+
| Tables_in_test_db |
+------------------------+
| departments |
| employees |
+------------------------+
2 rows in set (0.01 sec)
【departmentsテーブル情報の表示】
mysql> SHOW COLUMNS FROM departments;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | varchar(50) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
【departmentsテーブルをすべて表示】
mysql> SELECT * FROM departments;
+----+-------------+
| id | name |
+----+-------------+
| 1 | department1 |
| 2 | department2 |
| 3 | department3 |
+----+-------------+
3 rows in set (0.00 sec)
【employeesテーブル情報の表示】
mysql> SHOW COLUMNS FROM employees;
+---------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| first_name | varchar(50) | YES | | NULL | |
| last_name | varchar(50) | YES | | NULL | |
| department_id | int | YES | | NULL | |
+---------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
【employeesテーブルをすべて表示】
mysql> SELECT * FROM employees;
+----+-------------+------------+---------------+
| id | first_name | last_name | department_id |
+----+-------------+------------+---------------+
| 1 | first1 | last1 | 1 |
| 2 | first2 | last2 | 2 |
| 3 | first3 | last3 | 3 |
| 4 | first4 | last4 | 1 |
+----+-------------+------------+---------------+
4 rows in set (0.00 sec)
【view(employee_department_view)作成】
mysql> CREATE VIEW employee_department_view AS
-> SELECT emp.first_name, emp.last_name, dep.name
-> FROM Employees AS emp
-> INNER JOIN Departments AS dep ON emp.department_id = dep.id;
Query OK, 0 rows affected (0.01 sec)
【view表示】
mysql> SELECT * FROM employee_department_view;
+-------------+------------+-------------+
| first_name | last_name | name |
+-------------+------------+-------------+
| first1 | last1 | department1 |
| first2 | last2 | department2 |
| first3 | last3 | department3 |
| first4 | last4 | department1 |
+-------------+------------+-------------+
4 rows in set (0.00 sec)インデックス
INDEXは、特定の列の値とその列が存在する行の物理的な位置を関連付けることで、
データの検索・ソートおよびフィルタリングのパフォーマンスを向上させます。
CREATE(インデックス)
記述
CREATE INDEX インデックス名 ON テーブル名(カラム名);
【指定したテーブルのインデックス確認】
mysql> SHOW INDEX FROM users;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| users | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.01 sec)
【インデックス作成】
mysql> CREATE INDEX user_name ON users(name);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
【指定したテーブルのインデックス確認】
mysql> SHOW INDEX FROM users;
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| users | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
| users | 1 | user_name | 1 | name | A | 0 | NULL | NULL | YES | BTREE | | | YES | NULL |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.00 sec)