Day19-联表查询

Day19

联表查询

JOIN对比

• INNER JOIN

-- 查询参加了考试的同学（学号，姓名，科目编号，分数）
/*
思路：
1.分析需求，分析需要查询的字段来自于哪些字段，（连接查询）
2.确定用七种联表查询中的哪一种
3.确定交叉点，两个表之间哪些字段是相同的
4.判断的条件如：student表中学号和result表中的学号相同
*/

SELECT s.studentno AS 学号,`studentname` AS 姓名,`subjectno` AS 科目编号,`studentresult` AS 学生成绩
FROM `student` AS s
INNER JOIN `result` AS r
WHERE s.studentno = r.studentno

• RIGHT JOIN

-- RIGHT JOIN
SELECT s.studentno AS 学号,`studentname` AS 姓名,`subjectno` AS 科目编号,`studentresult` AS 学生成绩
FROM `student` AS s
RIGHT JOIN `result` AS r
ON s.studentno = r.studentno-- 确定交叉点

• LEFT JOIN

-- LEFT JOIN
SELECT s.studentno AS 学号,`studentname` AS 姓名,`subjectno` AS 科目编号,`studentresult` AS 学生成绩
FROM `student` AS s
LEFT JOIN `result` AS r
ON s.studentno = r.studentno-- 确定交叉点

总结：

操作	描述
inner join	如果表中至少有一个匹配，就返回行
left join	会从左边中返回所有的值，即使右表中没有匹配
right join	会从右边中返回所有的值，即使左表中没有匹配

-- JOIN(连接的表) ON (判断的条件)连接查询
-- WHERE 等值查询

-- 查询缺考的同学
SELECT s.studentno AS 学号,`studentname` AS 姓名,`subjectno` AS 科目编号,`studentresult` AS 学生成绩
FROM `student` AS s
LEFT JOIN `result` AS r
ON s.studentno = r.studentno-- 确定交叉点
WHERE `studentresult` IS NULL

-- 查询学员的年级（学号，学生姓名，年级名称）
SELECT `studentno` AS 学生姓名, `studentname` AS 学生姓名,`gradename` AS 年级名称
FROM `student` AS s
INNER JOIN `grade` AS g
ON s.`gradeid` = g.`gradeid`

• 三表查询

-- 思考题（查询了参加考试的同学信息：学号，学生姓名，科目名，分数）
SELECT s.studentno AS 学号,`studentname` AS 姓名,`subjectname`AS 科目名,`studentresult`AS 学生成绩
FROM `student` AS s
RIGHT JOIN `result` AS r
ON s.studentno = r.studentno-- 连接条件
-- 开始连接subject表
INNER JOIN `subject` AS sb
ON r.subjectno = sb.subjectno

-- 查询了参加 数据库-1 考试的同学信息：学号，学生姓名，科目名，分数
SELECT s.studentno AS 学号,`studentname` AS 姓名,`subjectname`AS 科目名,`studentresult`AS 学生成绩
FROM `student` AS s
RIGHT JOIN `result` AS r
ON s.studentno = r.studentno-- 连接条件
-- 开始连接subject表
INNER JOIN `subject` AS sb
ON r.subjectno = sb.subjectno
WHERE `subjectname` = '高等数学-1'

-- 我要查询哪些数据 select ...
-- 从哪几个表中查 from 表 XXX join 连接的表 on 交叉条件
-- 假设存在一种多张表查询，慢慢来，先查询两张表然后再慢慢增加

自连接（了解）

自连接：自己的表和自己的表连接

核心：一张表拆分为两张一样的表

父类：

category_id	category_name
2	信息技术
3	软件开发
5	美术设计

子类：

pid	category_id	category_name
3	4	数据库
2	8	办公信息
3	6	web开发
5	7	ps技术

父类对应的子类关系：

父类	子类
信息技术	办公信息
软件开发	数据库
软件开发	web开发
美术设计	ps技术

-- 查询父子信息：把一张一张表拆分为两张一样的表
SELECT a.`category_name` AS 父类, b.`category_name` AS 子类
FROM `category` AS a, `category` AS b
WHERE a.`category_id` = b.`pid`

分页和排序

分页 LIMIT

• 缓解数据库压力，给人体验更好。
• 或者不分页，应用瀑布流（一般用于图片，抖音也用了，可以不停的往下刷）

-- 分页，每页显示两条数据
-- LIMIT 起始值（从第几条数据开始显示），页面的大小（每页显示多少条数据）
SELECT s.studentno AS 学号,`studentname` AS 姓名,`subjectname`AS 科目名,`studentresult`AS 学生成绩
FROM `student` AS s
RIGHT JOIN `result` AS r
ON s.studentno = r.studentno
-- 开始连接subject表
INNER JOIN `subject` AS sb
ON r.subjectno = sb.subjectno
-- 降序排列
ORDER BY `studentresult` ASC
LIMIT 0,2
-- 第一页 limit 0,2    (1-1)*2
-- 第二页 limit 2,2    (2-1)*2
-- 第三页 limit 4,2   (3-1)*2
-- 第N页 limit (n-1)*2,2    (n-1)*pageSize,pageSize
-- pageSize,页面大小
-- (n-1)*pageSize，起始值
-- n，当前页
-- 总页数 = (数据总数%页面大小==0)? (数据总数/页面大小) : (数据总数/页面大小 + 1)

排序 ORDER BY

-- 排序： 升序ASC和降序DESC
-- ORDER BY +排序的字段+ 升序ASC或者降序DESC
-- 查询的结果根据 成绩降序 排序
SELECT s.studentno AS 学号,`studentname` AS 姓名,`subjectname`AS 科目名,`studentresult`AS 学生成绩
FROM `student` AS s
RIGHT JOIN `result` AS r
ON s.studentno = r.studentno
-- 开始连接subject表
INNER JOIN `subject` AS sb
ON r.subjectno = sb.subjectno
-- 降序排列
ORDER BY `studentresult` DESC

SELECT s.studentno AS 学号,`studentname` AS 姓名,`subjectname`AS 科目名,`studentresult`AS 学生成绩
FROM `student` AS s
RIGHT JOIN `result` AS r
ON s.studentno = r.studentno
-- 开始连接subject表
INNER JOIN `subject` AS sb
ON r.subjectno = sb.subjectno
-- 升序排列
ORDER BY `studentresult` ASC

子查询

where（这个值是计算出来的）

本质：在where语句中嵌套一个子查询语句

-- 方式1：使用连接查询
SELECT `studentno` AS 学号,r.`subjectno` AS 科目编号,`studentresult` AS 学生成绩
FROM `result` r
INNER JOIN `subject` sb
ON r.`subjectno`=sb.`subjectno`
WHERE `subjectname` = '高等数学-3'

-- 方式二  子查询(由里及外)
SELECT `studentno` AS 学号,`subjectno` AS 科目编号,`studentresult` AS 学生成绩
FROM `result` 
WHERE `subjectno` =(
     SELECT `subjectno` FROM `subject`
     WHERE `subjectname` = '高等数学-3'
)