MySQL增删改查和添加外键

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)