MySQL联合查询

CREATE TABLE student(
	uid INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
	name VARCHAR(20) NOT NULL,
	age TINYINT UNSIGNED NOT NULL,
	sex enum("man", "woman") NOT NULL
);

CREATE TABLE course(
	cid INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
	name VARCHAR(20) NOT NULL,
	credit TINYINT UNSIGNED NOT NULL
);

CREATE TABLE exam(
	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,'man'),('gaoyang',20,'woman'),('chenwei',22,'man'),('linfeng',21,'woman'),('liuxiang',19,'man');
insert into course(cname, credit) values('C++基础课程', 5),('C++高级课程', 10),('C++项目开发', 8),('C++算法课程', 12);
insert into exam(uid,cid,time,score) values(1,1,20210409,99),(1,2,20210410,80),(2,2,20210410,90),(2,3,20210412,99),(3,1,20210409,56),(3,2,20210410,93),(3,3,20210412,89),(3,4,20210411,100),(4,4,20210411,99),(5,2,20210410,59),(5,3,20210412,94),(5,4,20210411,95);

select uid, name, age, sex from student where uid=1;

select time, score from exam where uid=1 and cid=2;

select stu.uid, stu.name, stu.age, stu.sex, ex.time, ex.score from student as stu 
inner join exam as ex 
on stu.uid=ex.uid 
where ex.uid=1 and ex.cid=2;

select stu.uid, stu.name, stu.age, stu.sex, ex.time, ex.score, co.cname from exam as ex -- 第一个放用于连接的中间表，即和另外两张表有公共部分的表
inner join student as stu on ex.uid=stu.uid -- 连接考试表和学生表
inner join course as co on ex.cid=co.cid    -- 连接考试表和课程表
where ex.uid=1 and ex.cid=2;                -- 过滤条件

select course.cid, course.cname, count(*) cnt
from course
join exam on exam.cid=course.cid  -- 连接
where exam.score > 90.0           -- 条件过滤
group by exam.cid                 -- 分组
order by cnt desc;                -- 排序

select co.cname, co.credit, stu.name, ex.score
from exam ex
inner join course co on ex.cid=co.cid
inner join student stu on ex.uid=stu.uid
where ex.cid=2
order by ex.score desc
limit 1;

select avg(score) from exam where cid=2;

select co.cname, co.credit, avg(score) from course co
inner join exam ex
on co.cid=ex.cid
group by ex.cid;

select a.id, a.email, a.password from
t_user inner join (select id from t_user limit 1500000, 10) b
on a.id=b.id

// left join把左表的所有数据显示出来，若右表不存在，则显示为NULL
select * from student left join exam on student.uid=exam.uid;

// right join把右表的所有数据显示出来，若左表不存在，则显示为NULL
select * from student right join exam on student.uid=exam.uid;

-- select distinct uid from exam 会产生一张中间表供外面的SQL查询
-- not in对索引的命中并不高，一般情况下都是整表扫描
select * from student where uid not in (select distinct uid from exam);

-- 使用外连接，只显示课程号为null的学生信息
select a.* from student a left join exam b on a.uid=b.uid where b.cid is null;

-- 查看参加了3号课程的人，用where过滤后，再决定大小表关系，大表整表扫描
select a.* from student a inner join exam b on a.uid=b.uid where b.cid=3;

-- 看起来是left join，其实变成了inner join，查询过程和inner join一样
select a.* from student a left join exam b on a.uid=b.uid where b.cid=3;

-- 查看参加了考试同学的信息，未参加3号课程考试的则用NULL填充
select a.*, b.* from student a left join exam b on a.uid=b.uid and b.cid=3;

-- 查看没有参加3号课程考试的同学
select a.*, b.* from student a left join exam b on a.uid=b.uid and b.cid=3 where b.cid is null;

select * from Customers left join Orders on Customers.Id = Orders.CustomerId;

select * from Customers join Orders on Customers.Id = Orders.CustomerId;

select * from Customers right join Orders on Customers.Id = Orders.CustomerId;