MySQL连接查询

MySQL连接查询

在多表联合查询时，为了减少查询的次数，使用连接查询可以一次查询多个相关联表的数据。
MySQL连接查询：分为内连接查询和外连接查询。
其中外连接查询又分成 left连接查询 和 right连接查询。
下午为两张数据库表，表1和表2。

1. 查询两张表相交的数据，使用内连接查询，如红色区域的相交数据，可使用内连接进行查询。
2. 查询两张表不相交的数据使用外连接，如表1中与表2不相交的区域即蓝色区域使用left连接查询。
3. 表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;

1. 区分大表和小表，按照选择的数据量来区分，而不是整表的数据量。小表永远是整表扫描，然后去大表搜索，一般在大表中创建索引。例：从exam大表中取出所有的c.uid和c.cid，然后拿着这些uid和cid去student和course小表中整表扫描
2. 可结合分组、排序操作，进行更精确的搜索
3. 先执行on连接产生临时表，再执行where在临时表中筛选符合条件的数据
4. 对于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;