自学内容网 自学内容网
自学内容网 > 正文

数据库多表联查

🕗 发布于 2024-07-24 04:51 数据库  sql  
一、内联查询
内联查询只有完全满足条件的数据才能出现的结果
1.1 非等值联查
笛卡尔积，查到的结果具有不一致性
示例：
select * from student,class
1.2 等值查询
-- 查询出学生表和班级信息
   select * from student,class where student.classid=class.classid

-- 5张表全部联查
   select * from student,class,course,sc,teacher where                          student.classid=class.classid and student.sid=sc.sid
   and sc.cid=course.cid and course.tid=teacher.tid

-- 查询学过张三老师课程的学生信息
   select student.* from student,class,course,sc,teacher where student.sid=sc.sid
   and sc.cid=course.cid and course.tid=teacher.tid 
   and teacher.tname='张三'

-- 多表最终跟单表一致
-- 查询每个学生的 学生信息，班级名称，平均成绩
   select student.Sname,class.classid,avg(score) from student,class,sc 
   where student.sid=sc.sid and student.classid=class.classid 
   group by student.sid

-- 通过第一张表的结果进行on条件匹配
-- 表少 每张表的数据大，内存占用小， IO高
   select * from student,class where student.classid=class.classid ssex='男'
1.3 inner join on
表的个数多，每个表的数据量不大,吃内存  IO小，笛卡尔积

-- 通过第一张表的结果进行on条件匹配
   select * from student inner join class on student.classid=class.classid
where ssex='男'

   select * from student inner join class on student.classid=class.classid
inner join sc on student.sid=sc.sid -- 五表联查
select * from class inner join student on class.classid=student.classid
inner join sc on sc.sid=

-- 每门课程的平均成绩  课程名称  代课老师姓名 平均成绩
   select cname,tname,avg(score) from sc 
   inner join course on sc.cid=course.cid
   inner join teacher on course.tid=teacher.tid
   group by course.cid
二、外联查询
找到主查表
2.1 left join on 左外联
主表在join左边

-- 所有学生的数据和对应的班级信息
-- 主表是student
   select * from student 
   left join class on student.classid=class.classid

-- 查询出所有的学生学过多少门课程 学生姓名 课程数
   select sname,count(cid) from student left join sc on student.sid=sc.sid
group by student.sid

-- 没有班级 的学生
   select * from student left join class on student.classid=class.classid
where class.classid is null

-- 没有学生的班级
   select * from class left join student on student.classid=class.classid
where student.sid is null
2.2 right join on 右外联
主表在join右边

-- 没有学生的班级
   select * from student 
   right join class on student.classid = class.classid
   where student.sid is null
2.3 union
两个结果集的并集
（1） 去除重复 distinct
（2）不同类型的字段是可以合并
（3）起别名给第一个结果集才有用
（4）不同列数量的结果集不能合并

-- 库中的所有人的名字
   select sname,ssex,classid from student 
   union
   select tname,tsex,temail from teacher

-- 获取没有学生的班级和没有班级的学生的数据
   select * from student left join class on student.classid=class.classid
   where class.classid is null
   union 
   select * from student right join class on student.classid=class.classid
   where student.sid is null

-- 获取没有班级的学生和、班级和学生都有、
-- 全连接
   select * from student left join class on student.classid=class.classid
   union 
   select * from student right join class on student.classid=class.classid

-- 不去重的并集
   select * from student left join class on student.classid=class.classid
   union all
   select * from student right join class on student.classid=class.classid
三、子查询
子查询，又称内部查询
3.1 where 子句
--查询id最大的一个学生
  select * from student order by sid desc limit 1

-- 查询id最大的一个学生（子查询）
-- 效率差
   select max(sid) from student
   
   select * from student where sid=(select max(sid) from student)  -- 魔数

-- 查询每个班id最大的学生（子查询）
   select * from student 
   left join class on student.classid=class.classid
   where sid in(select max(sid) from student group by classid)

-- 查询学过张三老师课程的学生
   select * from student where sid in(
   select sid from sc where cid=
    (select cid from course where tid=(
   select tid from teacher where tname='张三')
    )
   )

-- 查询没学过张三老师课程的学生
   select * from student where sid not in(
   select sid from sc where cid=
    (select cid from course where tid=(
   select tid from teacher where tname='张三')
    )
   )
3.2 from 子查询
查询结果将作为一张表使用

-- 查询大于5人的班级名称和人数（不使用子查询）
   select classname,count(*) from class left join student on       class.classid=student.classid
   group by class.classid having count(*)>5

-- 查询大于5人的班级名称和人数（使用子查询）
   select classname,人数 from class left join (
   select classid,count(*) 人数
   from student group by classid)t1
   on class.classid = t1.classid
   where 人数>5
3.3 exists 子查询
子句有结果，父句执行，子句没结果，父句不执行

select * from teacher where exists(select * from student where classid=10)
3.4 any /some,all
子查询
  -- 查询一班成绩比二班最低分高的学生
  SELECT DISTINCT student.* FROM sc  
  LEFT JOIN student ON sc.sid=student.sid 
  WHERE student.classid=1 AND score > ANY(
  SELECT score FROM sc
  LEFT JOIN student  ON sc.sid=student.`Sid`
  WHERE student.classid=2
 )

-- all
 SELECT DISTINCT student.* FROM sc
 LEFT JOIN student ON student.sid=sc.sid 
 WHERE student.classid=1 AND score >ALL(
 SELECT score FROM sc
 LEFT JOIN student ON sc.sid=student.`Sid`
 WHERE student.`classid`=2
 )
3.5 IF(expr1,expr2,expr3)
（1）expr1 条件
（2）expr2 条件成立，显示数据
（3）expr 条件不成立，显示数据

 -- 1女
 -- 0男
 SELECT *FROM teacher ;

 SELECT tid,tname,IF(tsex=1,'女','男')tsex,tbirthday,taddress FROM teacher;
3.6 IFNULL(expr1,expr2)
 -- expr1 字段
 -- expr2 当字段为null时，默认值

select sid,sname,ifnull(birthday,'这个学生没有生日'),ssex from student
3.7 case when then end
同时出现

select tid,tname,
case tsex
when 0 then '男'
when 1 then '女'
else '保密'
end tsex,tbirthday from teacher 

select tid,tname,
case 
when tsex>1 then '男'
when tsex=1 then '女'
when tsex<1 then '未知'
end,tbirthday from teacher 

select score,sid,
case 
when score>=90 then 'A'
when score>=80 then 'B'
when score>=70 then 'C'
when score>=60 then 'D'
when score<60 then '不及格'
end from sc

原文地址：https://blog.csdn.net/m0_65347933/article/details/140644030

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

相关文章

自学内容网
Copyright © 2015-2024