数据库——多表查询和子查询

1.联合查询

union 合并去重复 两个的集合并集；并且会排重

如果是一个字段，重复直接去掉，如果是多个字段，都相同才会去重。

代码实现:

select name,sex from studentinfo

UNION #联合查询,会自动去重复

select teacherName,sex from teacher

union all：联合但是不会去重

代码实现:

select name,sex from studentinfo

UNION all#联合查询,不会自动去重复

select teacherName,sex from teacher

union all 效率比union 高。

2.子查询

子查询是一个嵌套在 SELECT、INSERT、UPDATE 或 DELETE 语句中的查询。数据库引擎将子查询作为虚表执行查询操作。

#子查询: 在sql中再嵌套另外一个sql。分为：简单子查询，相关子查询

简单子查询实现:

select * from studentinfo

where classId =

(

select classId from classInfo where className = 'AAA01'

)

#in 用于返回值有多个

select * from studentinfo

where classId in   

(

select classId from classInfo where className = 'AAA01'

)

相关子查询实现: 

.相关子查询：不能独立运行的，需要依赖外部查询的输入，查询结果返回到外部

三表关联

SELECT

s.name,c.courseName,e.score

from studentInfo s

join examInfo e

on s.studentId = e.studentId

join courseInfo c

on e.courseId = c.courseId

子查询的类型 

查询语句中的任何条件，值，范围，都可以使用子查询表示。

将子查询的结果集 再作为一个临时表来用。

1.子查询结果为一个值。（可以作为字段使用）

  查询与张三峰在同一个班级的学生信息

#查询与张三在同一个班级的学生信息

#1.需要知道张三峰在哪个班级

select classId from studentInfo 

where name = '张三峰'

#2.用班级做条件，查询其他学生

select * from studentinfo

where classId = (

select classId from studentInfo 

 where name = '张三峰'

)

2.子查询结果为多行一列(可以当作多个值来用(1,2,3,4))

查询参加了某门课程（html）考试的学生信息

 

#查询参加了某门课程（html）考试的学生信息

select * from studentinfo

where studentId in

(

SELECT

studentId

from examinfo

where courseId = (

select courseId from courseInfo where courseName = 'html'

)

)

比较运算符中使用子查询 

如果子查询的返回值不止一个，而是一个集合时，则不能直接使用比较运算符，可以在比较运算符和子查询之间插入ANY、SOME或ALL。其中等值关系可以用IN操作符（in关键字用于where子句中用来判断查询的表达式是否在多个值的列表中。返回满足in列表中的满足条件的记录）。

all子查询 

all可以与=、>、>=、<、<=、<>结合是来使⽤，分别表示等于、⼤于、⼤于等于、 ⼩于、⼩于等于、不等于其中的所有数据。当所有数据都满足才是true，会返回满足所有条件的数据。只要大于其中的所有值才会被显示。

any/some子查询

any 可以与=、>、>=、<、<=、<>结合起来使⽤，分别表示等于、⼤于、⼤于等于、⼩于、⼩于等于、不等于其中的任何⼀个数据。也就是说只要有任意一个满足就是true。并且显示满足条件的数据。高于最小值。

使用exists和not exists子查询

where  exist （⼦查询）如果该⼦查询有结果数据（⽆论什么数据，只要⼤于等于1⾏），则就为true，否则就为false

如果内层select返回true则外层select可以返回值，否则就返回空。

自关联

#自关联

#西游取经团(公司人事结构:职员，领导)

create table xyj

(

id int primary key, #人员编号

name varchar(20), #人员姓名

pid int           #上级领导编号

)

select * from xyj;

insert into xyj

(id,name,pid)

VALUES

(1,'如来',null),

(2,'菩萨',1),

(3,'唐僧',2),

(4,'孙悟空',3),

(5,'猪八戒',3),

(6,'沙和尚',3)

#请查询，所有员工信息，以及员工的领导

# 如来 NULL

# 菩萨 如来

# 唐僧 菩萨

# 孙悟空 唐增

#查询每个员工的领导

SELECT

a.name,b.name

from xyj a  #员工

left join xyj b  #领导表

on a.pid = b.id

#子查询

select

name,(select name from xyj where id = a.pid)

from xyj a

#查询每个领导下的小兵

SELECT

a.name,b.name

from xyj a #领导表

join xyj b #员工表

on a.id = b.pid