MySQL连接查询
在多表联合查询时,为了减少查询的次数,使用连接查询可以一次查询多个相关联表的数据。
MySQL连接查询:分为内连接查询
和外连接查询
。
其中外连接查询又分成 left连接查询
和 right连接查询
。
下午为两张数据库表,表1和表2。
- 查询两张表相交的数据,使用内连接查询,如红色区域的相交数据,可使用内连接进行查询。
- 查询两张表不相交的数据使用外连接,如表1中与表2不相交的区域即蓝色区域使用
left连接查询
。 - 表2中与表1不相交的区域,即绿色区域,使用
right连接查询
建立数据表
使用背景:学生成绩管理系统,包含学生表(student)、课程表(course)、考试表(exam)等三种表,表的关系如下所示
首先创建三种表的结构
CREATE TABLE student (
uid INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
age TINYINT UNSIGNED NOT NULL,
sex ENUM('M','W') NOT NULL);
CREATE TABLE course (
cid INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
cname VARCHAR(50) NOT NULL,
credit TINYINT UNSIGNED NOT NULL);
CREATE TABLE exame (
uid INT UNSIGNED NOT NULL,
cid INT UNSIGNED NOT NULL,
time DATE NOT NULL,
score FLOAT NOT NULL,
PRIMARY KEY(uid,cid));
向数据表中添加数据
insert into student(name,age,sex) values
('zhangsan', 18, 'M'),
('gaoyang', 20, 'w'),
('chenwei', 22, 'M'),
('linfeng', 21, 'W'),
('liuxiang', 19, 'W');
insert into course(cname,credit) values
('c++基础课程', 5),
('c++高级课程', 10),
('c++项目开发', 8),
('c++算法课程', 12);
insert into exame(uid,cid,time,score) values
(1,1,'2021-04-09', 99.0),
(1,2,'2021-04-10', 80.0),
(2,2,'2021-04-10', 90.0),
(2,3,'2021-04-12', 85.0),
(3,1,'2021-04-09', 56.0),
(3,2,'2021-04-10', 93.0),
(3,3,'2021-04-12', 89.0),
(3,4,'2021-04-11', 100.0),
(4,4,'2021-04-11', 99.0),
(5,2,'2021-04-10', 59.0),
(5,3,'2021-04-12', 94.0),
(5,4,'2021-04-11', 95.0);
SQL内连接(inner join)
两个表的内连接查询
select a.uid,a.name,a.age,a.sex from student a where a.uid=1;
select c.score from exame c where c.uid=1 and c.cid=2;
// 从 student 小表中取出所有的a.uid 然后拿着这些 uid 在exame大表中搜索
select a.uid,a.name,a.age,a.sex from student a
inner join exame c on a.uid=c.uid
where c.uid=1 and c.cid=2;
三个表的内连接查询
select a.uid,a.name,a.age,a.sex,b.cid,b.cname,b.credit,c.score
from exame c
inner join student a on c.uid=a.uid
inner join course b on c.cid=b.cid
where c.uid=1 and c.cid=2;
- 区分大表和小表,按照选择的数据量来区分,而不是整表的数据量。小表永远是整表扫描,然后去大表搜索,一般在大表中创建索引。例:从exam大表中取出所有的c.uid和c.cid,然后拿着这些uid和cid去student和course小表中整表扫描
- 可结合分组、排序操作,进行更精确的搜索
- 先执行on连接产生临时表,再执行where在临时表中筛选符合条件的数据
- 对于inner join内连接,过滤条件写在where的后面和on连接条件里面,效果是一样的。
结合limit分页优化SQL语句
select a.id,a.email,a.passward from t_user a inner join(select id from t_user limit 150000,10) b on a.id=b.id;
在mysql中创建一个临时表作为小表,提升SQL效率
SQL外连接(outer join)
SQL左连接查询(left join)
把left这边的表所有数据显示出来,在右表中不存在相应数据,则显示NULL
SELECT a.属性名列表,b.属性名列表 FROM table_name1 a LEFT JOIN table_name2 b on a.id=b.id;
select a.*,b.* from student a left outer join exame b on a.uid=b.uid;
SQL右连接查询(right join)
把right这边的表所有数据显示出来,在左表中不存在相应数据,则显示NULL
select a.*,b.* from student a right outer join exam b on a.uid=b.uid;
实际应用场景:查询在当前连接中为空的属性
select a.* from student a right join exam b on a.uid=b.uid where b.cid is null;
// not in 对于索引的命中并不高,推荐使用外连接
select * from student where uid not in (select distinct uid from exam);
对于outer join外连接,在判断null和限制条件的情况下,限制条件写在on里面,where条件过滤只判断null的情况
select a.* from student a left join exam b on a.uid=b.uid and b.cid=3 where b.cid is null;