MySQL数据库之多表查询

多表查询实例

use studentmanager 
-- 交叉连接
-- CROSS JOIN 得到笛卡尔积
SELECT * FROM student
SELECT * FROM score
SELECT * FROM student CROSS JOIN score
-- JOIN 得到笛卡尔积
SELECT * FROM student JOIN score
-- ,得到笛卡尔积
SELECT * FROM student,score
-- 对笛卡尔积中的数据进行过滤
SELECT * FROM student,score WHERE student.stuid = score.stuid -- 等值连接
-- 查询姓名为jim考试信息
-- 交叉连接
SELECT stu.stuid,`name`,subid,score 
FROM student stu,score sco 
WHERE stu.stuid = sco.stuid AND `name`='jim'

-- 内连接
-- 使用on 设置连接条件，根据连接条件决定将两个表或多个表的数据进行关联，省去了无用连接。
-- 获取姓名为Jim的学生的所有成绩
SELECT student.stuid,`name`,sex,subid,score
FROM student
INNER JOIN score
ON student.stuid = score.stuid
WHERE `name` = 'jim'
-- 内连接
-- 查询所有考试科目的平均成绩并降序排序 若成绩相同则按照科目id升序排序 （subname,avg）
SELECT score.subid,subname,AVG(score) AS avg
FROM score
INNER JOIN `subject`
ON score.subid = `subject`.subid
GROUP BY score.subid,subname
ORDER BY avg DESC,subid
-- 查询所有学生的考试成绩(学生id,姓名,班级id,班级名称,科目名称、考试成绩)
SELECT stu.stuid,`name`,c.cid,cname,sub.subname,score
FROM student stu,classinfo c,score sco,`subject` sub
WHERE stu.stuid = sco.stuid AND stu.cid = c.cid AND sub.subid = sco.subid
-- 查询所有学生的考试成绩(学生编号、姓名、考试科目、考试成绩)
SELECT stu.stuid,`name`,cla.cid,cname,subname,score
FROM student stu
INNER JOIN classinfo cla
ON stu.cid = cla.cid
INNER JOIN score sco
ON sco.stuid = stu.stuid
INNER JOIN `subject` sub
ON sco.subid = sub.subid
-- 外连接
-- 查询所有学生的考试成绩(左外连接)
SELECT stu.stuid,`name`,subid,score
FROM student stu
LEFT JOIN score sco
ON sco.stuid = stu.stuid

SELECT student.stuid,`name`,IFNULL(subid,'未修'),IFNULL(score,'缺考')
FROM student
LEFT JOIN score 
ON score.stuid = student.stuid
-- 查询所有的科目对应的考试信息
SELECT subname,`subject`.subid,score
FROM score
RIGHT JOIN `subject`
ON `subject`.subid = score.subid
-- 查询所有参加考试的学生信息
SELECT `name`,subid,score
FROM student
RIGHT JOIN score
ON student.stuid = score.stuid
-- 使用内连接
-- 查询考试成绩及格的学生信息

-- 查询所有学生信息(stuid,name,cid,cname)

-- 查询所有考试科目对应的成绩(subid,subname,socre)

-- 查询所有学生的平均成绩(学生编号、姓名、考试成绩)
SELECT student.stuid,`name`,AVG(score) score
FROM student 
LEFT JOIN score
ON score.stuid = student.stuid
GROUP BY student.stuid
-- 未参加考试的学生信息
SELECT student.stuid,`name`,score
FROM student
LEFT JOIN score
ON student.stuid = score.stuid
WHERE score IS NULL
-- 查询所有参加考试的学生信息(学生编号、学生姓名、学生班级编号、班级名称)
-- 查询所有参加考试的学生成绩(学生编号、学生姓名、班级名称、科目id，科目名称、考试成绩)
SELECT * 
FROM
(SELECT stu.stuid,`name`,cla.cid,cname,sub.subid,subname,score
FROM student stu
INNER JOIN classinfo cla
ON stu.cid = cla.cid
INNER JOIN score sco
ON sco.stuid = stu.stuid
INNER JOIN `subject` sub
ON sco.subid = sub.subid) newtable
ORDER BY score DESC,stu.stuid
-- 1.按照学生考试成绩降序排序
-- 2.若成绩相同按照学生编号升序排序

-- 查询所有参加考试学生的平均成绩(学生编号、姓名、平均成绩)并按照平均成绩降序排序

-- 查询所有参加考试学生的平均成绩前3名(学生编号、姓名、平均成绩)，成绩降序排序
SELECT score.stuid,name,AVG(score) score
FROM score
INNER JOIN student
ON student.stuid = score.stuid
GROUP BY score.stuid
HAVING score >= 60
ORDER BY score DESC
LIMIT 3
-- 查询所有学生的考试成绩(学生编号、学生姓名、性别、考试成绩)
-- 查询所有科目的平均成绩,如没有平均成绩则为0,平均成绩降序排序
-- 1.分别使用左 右连接实现
-- 2.要求查询出 科目编号、科目名称、平均成绩
SELECT `subject`.subid,subname,IFNULL(AVG(score),'0') avgs
FROM `subject`
LEFT JOIN score
ON `subject`.subid=score.subid
GROUP BY score.subid
ORDER BY avgs DESC

USE employeemanager
-- 3.查询研发部人员信息，显示姓名，职位，电话及部门名称
--  内连接 外连接
-- 查询所有员工信息及工资等级，结果保留员工ID、姓名、职位、工资、工资等级，
-- 并按员工ID升序排列。
SELECT eid,ename,job,salary,
				CASE gid
				WHEN 1 THEN '初级'
				ELSE '其他等级'
				END
FROM employee
INNER JOIN salarygrade
ON employee.salary >= salarygrade.lowsalary
AND employee.salary < salarygrade.highsalary
ORDER BY eid DESC
-- 查询所有员工信息、部门名称以及工资等级，结果保留员工ID、姓名、职位、部门名、工资、工资等级，并按员工ID升序排列。
SELECT eid,ename,job,dname,salary,gid
FROM employee e
INNER JOIN dept d
ON e.did = d.did
INNER JOIN salarygrade s
ON e.salary >= s.lowsalary
AND e.salary < s.highsalary
ORDER BY eid
-- 合并结果集 UNION ON 类型不同可以合并，列数不同不可以合并
SELECT * FROM t1 -- 去重复
UNION
SELECT * FROM t2

SELECT * FROM t1 -- 不去重复
UNION ALL
SELECT * FROM t2