データベース - MySQL - テーブル作成 about training HOT 3 CLOSED

mysql> create database kadai14DB default character set utf8;
Query OK, 1 row affected (0.00 sec)

mysql> use kadai14DB;
Database changed

mysql> create table kadai1  (id int not null, title varchar(32), note text, created_at datetime not null ) engine=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> describe kadai1;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id         | int(11)     | NO   | PRI | NULL    |       |
| title      | varchar(32) | YES  |     | NULL    |       |
| note       | text        | YES  |     | NULL    |       |
| created_at | datetime    | NO   |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> drop table kadai1;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
Empty set (0.00 sec)

mysql> create table kadai1  (id int not null, title varchar(32), note text, created_at datetime not null, primary key(id) ) engine=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> describe kadai1;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id         | int(11)     | NO   | PRI | NULL    |       |
| title      | varchar(32) | YES  |     | NULL    |       |
| note       | text        | YES  |     | NULL    |       |
| created_at | datetime    | NO   |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table kadai1 add editor varchar(16) after title;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> describe kadai1;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id         | int(11)     | NO   | PRI | NULL    |       |
| title      | varchar(32) | YES  |     | NULL    |       |
| editor     | varchar(16) | YES  |     | NULL    |       |
| note       | text        | YES  |     | NULL    |       |
| created_at | datetime    | NO   |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> alter table kadai1 modify title varchar(32) not null;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> describe kadai1;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id         | int(11)     | NO   | PRI | NULL    |       |
| title      | varchar(32) | NO   |     | NULL    |       |
| editor     | varchar(16) | YES  |     | NULL    |       |
| note       | text        | YES  |     | NULL    |       |
| created_at | datetime    | NO   |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> alter table kadai1 drop note; 
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> describe kadai1;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id         | int(11)     | NO   | PRI | NULL    |       |
| title      | varchar(32) | NO   |     | NULL    |       |
| editor     | varchar(16) | YES  |     | NULL    |       |
| created_at | datetime    | NO   |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> create table kadai4 (id int not null, detail text, foreign key(id) references kadai1(id)) engine=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> describe kadai4;
+--------+---------+------+-----+---------+-------+
| Field  | Type    | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| id     | int(11) | NO   | MUL | NULL    |       |
| detail | text    | YES  |     | NULL    |       |
+--------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> drop table kadai4;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+---------------------+
| Tables_in_kadai14DB |
+---------------------+
| kadai1              |
+---------------------+
1 row in set (0.00 sec)

mysql> create table kadai4 (id int not null, detail text, foreign key(id) references kadai1(id)) engine=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> describe kadai4;
+--------+---------+------+-----+---------+-------+
| Field  | Type    | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| id     | int(11) | NO   | MUL | NULL    |       |
| detail | text    | YES  |     | NULL    |       |
+--------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)