测试基础 - MySQL多表查询篇(2)

MySQL 多表查询

主键：唯一标识记录的字段或组合，不与业务逻辑发生关系，只作为唯一标识

外键：在主表与从表中，从表中与主表主键相连的字段就是外键，作用为 与主键构成主外键关系，使主从表相连。

规则：删除主表时，必须先删除从表

 

1. 创建主表主键

create table 表名(字段名 字符类型 primary key)charset utf8;

 

2. 创建从表外键

create table 表名(字段名 字符类型, froeign key 外键字段名 references 主表表名(主表主键字段名))charset utf8;

 

3. 删除外键

alter table 表名 drop foveign key 从表外键名称;

 

4. 查看从表外键名称/查看从表所有信息

show create table 从表名称; CONSTRAINT后面的就是外键名称，如“student_idfk_1”

 

5. 删除主键

alter table 主表表名 drop primary key;

 

6. 添加主键

alter table 表名 add primary key (字段名);

 

7. 添加外键

alter table 表名 add foreign key (字段名) references 主表表名(主表主键字段名);

 

8. 关联表查询（左外连接）

select * from 主表 left join 从表 on 主表.主键 = 从表.外键;

PS：on 等同于 where，显示左表全部信息，右表只显示可对应上的信息，对应不上的字段显示NULL

 

9. 关联表查询（右外连接）

select * from 主表 reght join 从表 on 主表.主键 = 从表.外键;

PS：显示右表全部信息，左表显示可以对应的信息，其余不显示

 

10. 关联表查询（内连接）

select * from 主表 inner join 从表 where 主表.主键 = 从表.外键;

 

11. 常用关联查询（查询对应信息）

这种查询方式，先找到相同数据，如有其它条件用 and 连接
select * from 表1,表2 where 表1.字段名 = 表2.字段名;

嵌套查询的方式，两个表中的某个字段值相同，可用 = 或 in 连接
select * from 主表 where 主表.主键 = (select 从表中外键 from 从表 where 条件);

select * from 主表 where 主表.主键 in (select 从表中外键 from 从表 where 条件);

三表查询：表1字段名 与 表2字段名 有相同数据， 表2字段名 与 表3字段名 有相同数据
select * from 表1 where 表1.字段名 = (select 表2字段名 from 表2 where 条件 in (select 表3字段名 from 表3 where 条件));

 

12. 主键自增

create table 表名(字段名 字段类型 primary key auto_increment)charset utf8;

PS：auto_increment 的用法依赖于主键，只能用于主键

例：主键设置自增后，新增数据时该字段可不用填写： insert into 表名(字段名1,字段名2,......,字段n) values (null,'张三',......,'一班')