多表查询本质（图解）
展开（图解）
联表查询，7种join对比：
1.1 inner/right/left join对比
-- 联表查询
-- join 对比
-- 表中数据不够了加了一些
insert into `result`(`studentno`,`subjectno`,`examdate`,`studentresult`)
values
(1001,1,'2013-11-11 16:00:00',85),
(1001,2,'2013-11-12 16:00:00',70),
(1001,3,'2013-11-11 09:00:00',68),
(1001,4,'2013-11-13 16:00:00',98),
(1001,5,'2013-11-14 16:00:00',58),
(1002,1,'2013-11-11 16:00:00',85),
(1002,2,'2013-11-12 16:00:00',70),
(1002,3,'2013-11-11 09:00:00',68),
(1002,4,'2013-11-13 16:00:00',98),
(1002,5,'2013-11-14 16:00:00',58),
(1003,1,'2013-11-11 16:00:00',85),
(1003,2,'2013-11-12 16:00:00',70),
(1003,3,'2013-11-11 09:00:00',68),
(1003,4,'2013-11-13 16:00:00',98),
(1003,5,'2013-11-14 16:00:00',58);
-- 查询参加了考试的同学（学号，姓名，科目编号，分数）
SELECT * FROM result
SELECT * FROM student
-- 分析
-- 1.分析查询字段来自那些表（连接查询）
-- 确定使用哪种连接查询？ 7种
-- 确定交叉，学生表studentno=成绩表studentno
-- inner join
SELECT s.studentno,studentname,subjectno,studentresult
FROM student AS s
INNER JOIN result AS r
WHERE s.studentno=r.studentno
-- right join
SELECT s.studentno,studentname,subjectno,studentresult
FROM student AS s
RIGHT JOIN result r -- AS可写可不写
ON s.studentno=r.studentno
-- left join
SELECT s.studentno,studentname,subjectno,studentresult
FROM student AS s
LEFT JOIN result r -- AS可写可不写
ON s.studentno=r.studentno
操作
描述
inner join
如果表中至少有一个匹配，就会返回值，当两张表有同样的列时，需在语句种确定，返回那张表的值
right join
会从左表中返回所有的值，即使右表中没有匹配
left join
会从右表中返回所有的值，即使左表中没有匹配
1.2多表查询练习
-- 查询缺考的同学
-- UPDATE result SET studentresult=NULL
-- WHERE studentno=1003 and subjectno=5
SELECT s.studentno,studentname,subjectno,studentresult
FROM student AS s
LEFT JOIN result r -- AS可写可不写
ON s.studentno=r.studentno
WHERE studentresult is NOT NULL
-- 注意
-- join（连接的表） on （判断条件）连接查询
-- where 等值查询
-- 思考题（查询参加考试的同学信息：学号，学生姓名，科目名，分数），三表查询
SELECT s.studentno,studentname,subjectname,studentresult
FROM student AS s
RIGHT JOIN result r -- AS可写可不写
ON s.studentno=r.studentno
-- where studentresult is not NULL
INNER JOIN `subject` sub
ON r.subjectno=sub.subjectno
-- 总结，当有多张表查询是，应先查询两张表然后再慢慢增加
1.3自连接
本质：自己的表和自己的表连接，核心：一张表拆成两张一样的表即可
eg：
父类
categoryid
categoryname
2
信息技术
3
软件开发
5
美术设计
子类
pid
categoryid
categoryname
3
4
数据库
2
8
办公信息
3
6
web开发
5
7
美术设计
操作：查询父类对应子类的关系
父类
子类
信息技术
办公信息
软件开发
数据库
软件开发
web开发
美术设计
ps
-- ================自连接================
-- 创建新表，插入数据
CREATE TABLE category(
categoryid INT(10) UNSIGNED not NULL auto_increment COMMENT '主题id',
pid INT(10) NOT NULL COMMENT '父id',
categoryname VARCHAR(50) NOT NULL COMMENT '主题名字',
PRIMARY KEY(categoryid)
)ENGINE=INNODB DEFAULT charset=utf8;
INSERT into category (categoryid,pid,categoryname)
VALUES('2','1','信息技术'),
('3','1','软件开发'),
('4','3','数据库'),
('5','1','美术设计'),
('6','3','web开发'),
('7','5','ps技术'),
('8','2','办公信息');
-- 自己 的表和自己的表连接，核心：一张表拆成两张一样的表即可
-- 查询父子信息
SELECT a.categoryname AS '父栏目',b.categoryname as '子栏目'
from category AS a, category AS b
WHERE a.categoryid=b.pid
SELECT * FROM category
-- 练习：查询学员所属的年纪（学号，学生姓名，年纪名称）
SELECT studentno,studentname,gradename
from student s
INNER JOIN grade g
ON s.gradeid=g.gradeid
-- 练习：查询科目所属的年级(科目名称，年级名称)
SELECT subjectname,gradename
FROM `subject` s
INNER JOIN grade g
ON s.gradeid=g.gradeid
-- 思考题（查询参加数据库结构-1考试的同学信息：学号，学生姓名，科目名，分数），
SELECT s.studentno,studentname,subjectname,studentresult
FROM student s
INNER JOIN result r
ON s.studentno=r.studentno
INNER JOIN `subject` sub
ON sub.subjectno=r.subjectno
WHERE subjectname='高等数学-1'
SELECT * FROM grade
SELECT * FROM `subject`
SELECT * FROM result
SELECT * FROM student
1.4分页（limit）和排序（order by）
-- ===========分页（limit）和排序(order by)========
-- 升序ASC，降序DESC
-- 排序
-- 查询结果根据成绩降序排
SELECT s.studentno,studentname,subjectname,studentresult
FROM student s
INNER JOIN result r
ON s.studentno=r.studentno
INNER JOIN `subject` sub
ON sub.subjectno=r.subjectno
WHERE subjectname='高等数学-1'
ORDER BY studentresult DESC
-- 分页，缓解数据库压力，给人体验更好，瀑布流
-- 分页，每页只显示3条数据
-- 语法：limit 起始值，页面大小
SELECT s.studentno,studentname,subjectname,studentresult
FROM student s
INNER JOIN result r
ON s.studentno=r.studentno
INNER JOIN `subject` sub
ON sub.subjectno=r.subjectno
WHERE subjectname='高等数学-1'
ORDER BY studentresult DESC
LIMIT 3,3
-- 分页总结：【pagesize：页面大小，起始值=（n-1）*pagesize】
-- 【n:当前页】，【页面总数/页面大小=总页数】
总结题
-- 思考题：查询高等数学-1，课程成绩排在前三的学生，并且分数大于80的学生信息（学号，姓名，课程名称，分数）
SELECT s.studentno,studentname,subjectname,studentresult
FROM student s
INNER JOIN result r
ON s.studentno=r.studentno
INNER JOIN `subject` sub
ON r.subjectno=sub.subjectno
WHERE subjectname='高等数学-1' AND studentresult>=80
ORDER BY studentresult DESC
LIMIT 0,3