1. 增加表信息
mysql> insert into student(name,age) values ("小芳",18),("李華",18),("小李子",18),("小燕子",18),("紫薇",18);
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from student;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 1 | 小芳 | 18 |
| 2 | 李華 | 18 |
| 3 | 小李子 | 18 |
| 4 | 小燕子 | 18 |
| 5 | 紫薇 | 18 |
+----+-----------+------+
5 rows in set (0.00 sec)
mysql> insert into class(name) values ("云計算1810"),("云計算1901"),("云計算1902") ;
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from class;
+----+---------------+
| id | name |
+----+---------------+
| 1 | 云計算1810 |
| 2 | 云計算1901 |
| 3 | 云計算1902 |
+----+---------------+
3 rows in set (0.00 sec)
2. 添加列class_id 列屬性和class表id 屬性int相同
mysql> alter table student add class_id int;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from student;
+----+-----------+------+----------+
| id | name | age | class_id |
+----+-----------+------+----------+
| 1 | 小芳 | 18 | NULL |
| 2 | 李華 | 18 | NULL |
| 3 | 小李子 | 18 | NULL |
| 4 | 小燕子 | 18 | NULL |
| 5 | 紫薇 | 18 | NULL |
+----+-----------+------+----------+
5 rows in set (0.00 sec)
3. 更新字段
mysql> update student set class_id=1 where id>=2 and id<=3;
Query OK, 2 rows affected (0.10 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> select * from student;
+----+-----------+------+----------+
| id | name | age | class_id |
+----+-----------+------+----------+
| 1 | 小芳 | 18 | NULL |
| 2 | 李華 | 18 | 1 |
| 3 | 小李子 | 18 | 1 |
| 4 | 小燕子 | 18 | NULL |
| 5 | 紫薇 | 18 | NULL |
+----+-----------+------+----------+
5 rows in set (0.00 sec)
mysql> update student set class_id=null;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 5 Changed: 2 Warnings: 0
mysql> select * from student;
+----+-----------+------+----------+
| id | name | age | class_id |
+----+-----------+------+----------+
| 1 | 小芳 | 18 | NULL |
| 2 | 李華 | 18 | NULL |
| 3 | 小李子 | 18 | NULL |
| 4 | 小燕子 | 18 | NULL |
| 5 | 紫薇 | 18 | NULL |
+----+-----------+------+----------+
5 rows in set (0.00 sec)
4. 增加外鍵的約束屬性
alter table 從表 add constraint 外鍵名稱 foreign key 從表（從表列名）reference 主表（主表列名）坟比；
mysql> alter table student add constraint FK_class_id foreign key student(class_id) references class(id);
Query OK, 5 rows affected (0.10 sec)
Records: 5 Duplicates: 0 Warnings: 0
5. 查詢從表和主表的對應信息
mysql> select * from student a , class b where a.class_id=b.id;
+----+-----------+------+----------+----+---------------+
| id | name | age | class_id | id | name |
+----+-----------+------+----------+----+---------------+
| 1 | 小芳 | 18 | 2 | 2 | 云計算1901 |
| 2 | 李華 | 18 | 1 | 1 | 云計算1810 |
| 3 | 小李子 | 18 | 1 | 1 | 云計算1810 |
| 4 | 小燕子 | 18 | 2 | 2 | 云計算1901 |
| 5 | 紫薇 | 18 | 3 | 3 | 云計算1902 |
+----+-----------+------+----------+----+---------------+
5 rows in set (0.00 sec)
