首页 > 其他分享 >高级数据查询语句-多表联查

高级数据查询语句-多表联查

时间：2024-07-22 19:25:56浏览次数：17  
标签：语句 classid 多表 -- student join 联查 class select

一、多表联查

        多表联查可以通过连接运算实现，即将多张表通过主外键关系关联在一起进行查询。下图提供了多表联查 时用到的数据库表之间的关系。 

1.内联查询 

        只有完全满足条件(主外键关系)的数据才能出现的结果

 1.1 非等值联查  

        语法：非等值查询：SELECT * FROM 表1，表2

        注意：非等值联查：笛卡尔积 逻辑上有错误，表中标红的一行数据是有错误的，班级编号为1，但是显示数据在二班

-- 非等值联查   -- 笛卡尔积 逻辑上有错误
select * from  student,class;

 

1.2  等值联查

        语法：等值查询：SELECT * FROM 表1，表2 WHERE 表1.字段1 = 表2.字段2...

-- 等值联查
-- 查询出学生和班级信息 student  class
select * from  student,class where student.classid=class.classid;

 

1.3 五张表全部联查出来

-- 5张表全部联查出来
select * from student,class,sc,course,teacher
where student.classid=class.classid and student.Sid=sc.Sid and sc.Cid=course.Cid and course.Tid=teacher.Tid;

 

1.4 面试题！！！ 

-- 查询出学生学过张三老师课程的学生信息(面试！！！)
select * from student;
select * from teacher;
select student. * from student,sc,course,teacher 
where  student.Sid=sc.Sid and sc.Cid=course.Cid and course.Tid=teacher.Tid and teacher.Tname='张三';

 

-- 查询每个学生的平均成绩 学生姓名 班级名称 平均成绩
select student.Sname ,class.classname ,avg(score) from student,sc,class
where student.Sid=sc.Sid 
and student.classid=class.classid
group by student.Sid;

 

1.5 inner join  on 

select * from student 
inner join class on student.classid=class.classid
inner join sc on student.Sid=sc.Sid
where ssex='男'

 

1.6 等值联查与inner join on 的区别

等值联查：

  • 先拿出所有的结果再筛选
  • 适合表的个数多，但是每个表的数据量不大，吃内存但是 IO小

inner join on：

  • 通过第一张表的结果集进行on后面的结果匹配，符合条件的放到结果集里面
  • 适合表少，但是每张表的数据大，内存占用小，IO高
-- 笛卡尔积
-- 先拿出所有的结果再筛选
-- 适合表的个数多，但是每个表的数据量不大，吃内存但是 IO小
select * from student,class
where student.classid=class.classid andssex='男';     -- 结果一样


-- 通过第一张表的结果集进行on后面的结果匹配，符合条件的放到结果集里面
-- 适合表少，但是每张表的数据大，内存占用小，IO高
select * from student 
inner join class on student.classid=class.classid
inner join sc on student.Sid=sc.Sid
where ssex='男'

 

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

 

-- 5表联查
select * from student
inner join class on student.classid=class.classid
inner join sc on student.Sid=sc.Sid
inner join course on student.classid=course.Cid
inner join teacher on course.Tid=teacher.Tid

 

-- 没门课程的平均成绩 课程名称 代课老师姓名 平均成绩
select course.Cname,teacher.Tname,avg(sc.score)from sc
inner join course on sc.Cid=course.Cid
inner join teacher on course.Tid=teacher.Tid
group by course.Cid

 

2.外联查询 

  • 找到主查表
  • 以小表驱动大表

 

2.1  left join on 左外联  主查表在join的左边

-- 获取所有学生的数据和对象的班级信息
-- left join on 左外联  主查表在join的左边
select * from student
left join  class on student.classid=class.classid

2.2  right join on 右外联  主查表在join的右边

-- right join on 右外联  主查表在join的右边
select * from class
right join student on student.classid=class.classid

         查询出所有的学生都学过多少门课程 显示学生姓名  课程数 

-- 查询出所有的学生都学过多少门课程 显示学生姓名  课程数
select sname ,COUNT(Cid) from student 
left join sc on student.Sid=sc.Cid
group by student.Sid

select sname ,COUNT(DISTINCT Cid) from student 
left join sc on student.Sid=sc.Cid
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

select * from student
right join class on student.classid=class.classid
where student.Sid is null

2.3 union

注意：

  •  union 两个结果集的并集
  •  union 去除重复与distinct 一样
  •  不同类型的字段是可以合并的
  •  不同列数量的结果集不允许合并
  •   起别名给第一个结果集才有用

        查询库中的有名字的人 

-- 查询库中的有名字的人
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.子查询 

         子查询，又叫内部查询

3.1  where 型子查询

        查询id最大的一个学生(使用排序+分页实现)

select * from student order by sid desc limit 1

 

        查询id最大的一个学生(子查询)

  •  所有的查询必须用小括号括起来
  •  效率极低 
select * from student 
where sid=(select max(sid) from student)

 

        查询每个班下id最大的学生(使用where子查询实现)

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人的班级名称和人数(不使用子查询)

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

 

        查询大于5人的班级名称和人数(使用from型子查询) 

-- 查询大于5人的班级名称和人数(使用from型子查询)
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 子查询 

子句有结果，父句执行 ，子句没结果 ，父句不执行 

-- exists 子查询 子句有结果，父句执行 ，子句没结果 ，父句不执行
select * from teacher
where exists (select * from student where classid=10)

 

3.4 any(推荐新版本的any) \ some 子查询

        题：查询出一班成绩比二班最低成绩高的学生

-- any(推荐新版本的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)

-- some
select  DISTINCT student.* from sc
left join student on sc.Sid=student.Sid
where student.classid=1 and score>some(
	select score from sc
left join student on sc.Sid=student.Sid
where student.classid=2)

 

3.5 all 子查询 

         题：查询出一班成绩比二班最高成绩高的学生

-- all 
-- 题：查询出一班成绩比二班最高成绩高的学生
select  DISTINCT student.* from sc
left join student on sc.Sid=student.Sid
where student.classid=1 and score>all(
	select score from sc
left join student on sc.Sid=student.Sid
where student.classid=2)

 

4.结果集的控制语句

4.1 IF(expr1,expr2,expr3) 

IF(expr1,expr2,expr3)
expr1 条件
expr2 条件成立 显示数据
expr3 条件不成立 显示数据

select * from teacher
-- 1 女
-- 0 男
select tid,tname ,if(tsex=1,'女','男')tsex,tbirthday,taddress from teacher

 

4.2  IFNULL(expr1,expr2)

IFNULL(expr1,expr2)
expr1 字段
expr2 当字段为null写的默认值

IFNULL(expr1,expr2)
-- expr1 字段
-- expr2 当字段为null写的默认值
select sid,sname,IFNULL(birthday,'这个学生没有生日,可怜宝宝！')bir,ssex from student

 

4.3 case when then end (必须同时出现) 

-- 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 tsex,tbirthday from teacher

-- 查询学生的成绩，
-- 大于80分的用B显示，
-- 大于70分的用C显示，
-- 大于60分的用D显示，
-- 小于60分的显示不及格 

select score,
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

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

 

(面试题！！！)行专列  列转行
        统计各个分数段的人数 

分数段    人数
100-90     5
90-70      10
70-60      2
不及格     3 

select '100-90' 分数段 ,count(*) 人数 from sc where score <=100 and score >=90
union 
select '90-70' ,count(*) from sc where score <=90 and score >=70
union 
select '70-60' ,count(*) from sc where score <=70 and score >=60
union 
select '不及格' ,count(*) from sc where score <60

 

 

 分数段   100-90    90-70   70-60   不及格
人数        3        10      2        3             5

select '人数' 分数段,   
       count(case when score <=100 and score >=90 then  score end) '100-90' , 
       count(case when score <90 and score >70 then  score end) '90-70' ,
			 count(case when score <70 and score >60 then  score end) '70-60' ,
			 count(case when score <60 then  score end) '不及格' 
from sc

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

         

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

         

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 bioabioa

标签：语句,classid,多表,--,student,join,联查,class,select
From： https://blog.csdn.net/m0_68041576/article/details/140613943

相关文章

  • 最详细的Verilog阻塞,非阻塞赋值语句介绍--数码管控制段选信号代码
    目录前言一、结构语句1、initial语句2、always语句二、赋值语句1.阻塞赋值2.非阻塞赋值3.总结三、条件语句1if_else语句2.case语句前言本文笔者将为大家详细的介绍Verilog的三种语句介绍,包括结构语句,赋值语句和条件语句一、结构语句1、initial语句initi......
  • Scanner,switch,分支语句,循环语句
    Scanner1.概述:​Scanner是java定义好的类2.用法:​a.导包importjava.util.Scanner​b.创建对象Scanner对象名=newScanner(System.in);​c.调用方法对象名.next() //录入字符串对象名.nextInt() //录入整数publicclassDemo02{publicstat......
  • 基本的DQL语句-单表查询
    一、DQL语言        DQL(DataQueryLanguage数据查询语言)。用途是查询数据库数据，如SELECT语句。是SQL语句中最核心、最重要的语句，也是使用频率最高的语句。其中，可以根据表的结构和关系分为单表查询和多表联查。注意：所有的查询都会得到一张虚拟的表二.单表查询......
  • idea内置数据库DataGrip + 多表操作sql语句 -- 逻辑外键建表 + 案例联系
    逻辑外键建表+案例联系--=====================================多表设计================================---------------------一对多职位与员工--员工子表createtabletb_emp(idintunsignedprimarykeyauto_incrementcomment'id',user......
  • Java基础语法01-运算符&流程控制语句If
    Java基础语法1.运算符1.1算术运算符（理解）1.1.1运算符和表达式运算符：对常量或者变量进行操作的符号表达式：用运算符把常量或者变量连接起来符合java语法的式子就可以称为表达式。​不同运算符连接的表达式体现的是不同类型的表达式。举例说明：inta=10;intb=2......
  • javascript条件判断语句。
    if语句条件满足就执行，不满足就不执行if(条件){语句}ifelse语句条件满足，执行语句1，条件不满足，执行语句2if(条件){语句1}else{语句2}ifelseifelseif… if(条件1){ 语句1 }else{ 语句2 }if(条件2){ 语句2 }el......
  • MP+XML手写sql语句分页查询
    原则：让IPage接收从数据库查处的记录@AutowaireprivateUserMapperuserMapper;publicPageDTO<UserVO>pageUser(UserPageQueryquery){IPage<UserVO>page=newPage<>(query.getPageNo(),2);page=userMapper.PageAndXml(query,page);List<U......
  • C基础：控制语句
    1.分支语句1.1if语句1.1.1基本结构if(表达式){语句块1;}else{语句块2;}执行顺序：如果条件成立，则执行语句块1，否则条件不成立的话执行语句块2。1.1.2分层结构if(表达式1){语句块1;}elseif(表达式2){语句块2;}else{语句块3;}执行顺序：如果满......
  • idea内置数据库DataGrip + 多表查询sql语句 -- 内连接与外连接
    多表查询内连接与外连接--——————————多表查询--用的是多表设计中一对多的表与数据--单表select*fromtb_dept;select*fromtb_emp;--多表select*fromtb_emp,tb_deptwheretb_emp.dept_id=tb_dept.id;--==============内链接没有联系的查......
  • 循环执行语句：do while语句、for语句、while语句
    什么是循环执行语句循环执行语句在百度之中的意思是一组被重复执行的语句称之为循环体,能否继续重复,决定循环的终止条件。循环语句是由循环体及循环的终止条件两部分组成的。要使用循环语句时，必须要确定循环体及条件（布尔表达式）两个重要因素。简单来说，当你在打游戏的时......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99