SQL高级查询语句

关联表查询

数据库中的各个表中存放者不同的数据，往往需要用多个表中的数据组合查询出所需要的信息，即从多个数据表中查询数据。等值多表查询将按照等值的条件查询多个数据表中关联的数据。要求关联的多个数据表的某些字段具有相同的属性，即相同的数据类型,宽度和取值范围.使用where或join(这里使用join,语句简洁，逻辑清晰，性能更优)

双表关联

• natural join

  1.NATURAL JOIN子句基于两个表之间有相同名字
  的所有列

  2.它从两个表中选择在所有的匹配列中有相等值的行

  3.如果有相同名字的列的数据类型不同，返回一个错
  误

select  * from 
studinfo natural join classinfo

• join tablename using(等值字段)

  用USING 子句创建连接

  1.如果一些列有相同的名字，但数据类型不匹配，NATURAL JOIN子句能够用USING 子句修改以指定将被用于一个等值连接的列

  2.当有多个列匹配时，用USING 子句匹配唯一的列

  3.在引用列不要使用表名或者别名

  4.NATURAL JOIN 和USING子句是相互排斥的

select * from studinfo join classinf
using (classid)

• join tablename on(table1.等值字段=table2.等值字段)

  用ON 子句创建连接

  1.对于自然连接的连接条件，基本上是带有相同名字的所有列的等值连接

  2.为了指定任意条件，或者指定要连接的列，可以使
  用ON 子句

  3.连接条件从另一个搜索条件中被分开

select * from studinfo join classinfo
on (studinfo.classid=classinfo.classid)

以上三句查询结果是等效的

picone.PNG

多表关联

• natural join table2 natural join table3...

select * from studinfo 
natural join classinfo 
natural join studscoreinfo 

• join table2 using (等值字段1) join table3 using (等值字段2)...

select * from studinfo 
natural join classinfo classid
natural join studscoreinfo studno

• join table2 on (table1.等值字段1=table2.等值字段1) join table3 on (table1.等值字段2=table3.等值字段2)...

select *
from studinfo s join classinfo c
on(s.classid=c.classid) join
studscoreinfo sc
on(s.studno=sc.studno) 

以上三句查询结果是等效的

pictwo.PNG

表连接

作为测试我向Classinfo下面插入了3条记录，但班级下是没有学生的，也就是说
studinfo，不会和我天加的记录匹配的

insert into classinfo(classid,classname,classdesc) values('201611521','大数据与智能工程','good') 
insert into classinfo(classid,classname,classdesc) values('201611521','大数据与智能工程','good') 
insert into classinfo(classid,classname,classdesc) values('201611522','软件工程','good')

picthree.PNG

• 内连接 inner join (连接两个或多个表仅返回匹配的行)

select * from studinfo
inner join classinfo using(classid)

picfour.PNG

确实是这样,仅返回两张表都匹配的classid的记录，共616条记录

• 左连接 left join (左表所有行出现，右表仅返回匹配的行)

select * from studinfo
left join classinfo using(classid)

结果和上面相同，共616条记录，我添加的3条记录，因为和studinfo(左表)不匹配
从而并不返回

• 右连接 right join (右表所有行出现，左表仅返回匹配的行)

select * from studinfo
right join classinfo using(classid)

picfive.PNG

最后返回了619条记录，classinfo是右表它的所有记录都显示

子查询

在SQL语言中，当一个查询语句嵌套在另一个查询的查询条件之中称为嵌套查询，又称为子查询。嵌套查询是指在一个外层查询中包含另外一个内层查询，其中外层查询称为主查询，内层查询称为子查询，通常情况下嵌套查询中的子查询先挑选中部分数据，以作为外层查询的数据来源或搜素条件，子查询都是写在圆括号中，允许使用表大式的地方都可以嵌套子查询

• 子查询这里就不各种类型展开讨论了，子查询功能强大，查询语句可简单，可复杂主要就是SQL简单查询的组合嵌套查询，因此也十分灵活多变

• 举例

1.查询重修15门以上的学生信息

select * from studinfo
where studno in
(
select studno
from studscoreinfo 
where studscore<60
having count(*)>15
//子查询查询出重修15门以上的学生学号
)
//跟据获得的学号查找出这些学生的基本信息

2.查询同班同姓名的学生成绩信息

分析1：先找出同班同名的学生

select classid||studname 
from studinfo
gruop by classid||studname
having count(*) >1 //最内层子查询

picsix.PNG

发现班级ID为990716的班上有两个名为陈曦的同学

分析2：要获取成绩信息，就要知道学生学号

select studno 
from studinfo
wherer classid||studname in (最内层子查询)//次内层子查询

分析3:根据学号查询成绩

select studscore 
from studscoreinfo
where studno in (次外层子查询)

组合：

select * from studscoreinfo
where studno in
(
select studno from studinfo
where classid||studname in
(
select classid||studname
from studinfo
group by classid||studname
having count(*)>1
)
)

3.查询同名同性别的学生信息

select * from studinfo
where (studname,studsex) in
(
select studname,studsex
from studinfo
group by studname,studsex
having count(*)>1
)

MERGE语句

提供有条件地更新和插入数据到数据库表中的能力
如果行存在，执行UPDATE；如果是一个新行,执
行INSERT：

• 作用

用一张表的数据更新另一张表的数据，例如：一个游戏每天有人注册，修改密码
可以设置一个定时任务，每天凌晨4点从线上的数据表，更新到线下备份的数据表
记录不存在就插入，记录存在就看密码是否修改，如果修改了密码，就更新这条记录

v2-c43f1a2451e41a9087ae2f383f21f908_hd.gif

• 语法

MERGE INTO 待修改的表 c
USING 数据源表 e
ON (c.等值字段 = e.等值字段)
WHEN MATCHED THEN  //当匹配更新
UPDATE SET
...
WHEN NOT MATCHED THEN //不必配插入
INSERT VALUES...;

练习

1． 在学生信息表(StudInfo)和学生成绩信息表(StudScoreInfo)分别使用内联接，左联接，右联接，全联接查询学生的学号、姓名、性别、课程编号、成绩。
下面是内联接的实例

Select S.StudNo,S.StudName,S.StudSex,SI.
CourseID,SI.StudScore
From StudInfo S inner join
StudScoreInfo SI On S.StudNo=SI.StudNo
--使用 WHERE 条件与上语句等价
Select S.StudNo,S.StudName,S.StudSex,SI.
CourseID,SI.StudScore
From StudInfo S,StudScoreInfo SI
Where S.StudNo=SI.StudNo

1.1
select s.studno,studname,studsex,si.courseid,si.studscore
from studinfo s inner join
studscoreinfo si on s.studno=si.studno

1.2
select s.studno,studname,studsex,courseid,studscore
from studinfo s left join
studscoreinfo si on s.studno=si.studno 

1.3
select s.studno,studname,studsex,courseid,studscore
from studinfo s right join
studscoreinfo si on s.studno = si.studscore

2． 使用 IN 子查询，查询学生平均成绩大于 75 小于 80 的学生基本信息（包括 StudInfo 中的所有字段）

select * from studinfo
where studno in
(select studno
from studscoreinfo
group by studno
having avg(studscore) between 75 and 80)

3． 写出统计各课程平均分、总分、最高分、最低分、参考人数的 SQL 语句，查询结果包括课程编号(CourseID)、课程名称(CourseName)、课程总分(SumScore)、课程平均分(AvgScore)、课程最高分(MaxScore)、课程最低分(MinScore)、参考人数(CourseCount)字段。

select ci.courseid,ci.coursename,sum(studscore) 课程总分, avg(studscore) 课程平均分, max(studscore) 课程最高分
,min(studscore) 课程最低分,count(*) 参考人数
from studscoreinfo si,courseinfo ci
where ci.courseid = si.courseid
group by ci.courseid,ci.coursename

4． 在学生成绩信息表（StudScoreInfo）、学生信息表（StudInfo）、班级信息表（ClassInfo）中，查询学生成绩重修（成绩<60）门数大于 10 门的学生基本信息（查询结果包括学号、姓名、性别、班级名称字段）

select * from studinfo
where studno in
(
select studno
from studscoreinfo
where studscore<60
group by studno
having count(*)>10
)

5． 在学生成绩信息表（StudScoreInfo）、课程信息表（CourseInfo）中，统计各学生所获得学分（成绩大于等于 60 为获得该门课程学分）。

select studno,studname,sum(coursecredit) as 学分
from studinfo join studscoreinfo using(studno)
join courseinfo using(courseid)
where studscore>60
group by studno,studname

6． 在学生成绩信息表（StudScoreInfo）中查询学号为 20010505001 课程成绩最高的 5 门课程的 SQL 语句。

select * from studinfo 
where studno in 
(
select studno from 
(
select studno,avg(studscore) 
from studscoreinfo
group by studno 
order by avg(studscore)
)
where rownum <=5)

7． 在学生信息表（StudInfo）中找出相同姓名相同性别的学生信息。

select studname,studsex,count(*)
from studinfo
group by studname,studsex
having count(*)>1

8． 在学生成绩信息表（StudScoreInfo）中查询学号为 20010505 开头名列前 5 名的学生信息（提示：使用子查询）。

select * from 
(
select * from studscoreinfo
where studno like '20010505%'
)where rownum < 6

9． 在学生成绩信息表(StudScoreInfo)中统计课程门数 10 以上的各学生去掉最高分和最低分课程后的平均分。包括学号（StudNo）、总分(SumScore)、最高分（MaxScore）、最低分
(MinScore)、课程门数(CourseCount)、平均分（AvgScore）字段。

select studno,count(*) 课程门数,max(studscore)最高分,min(studscore) 最低分,
case when count(*)>10 then
    (sum(studscore)-max(studscore)-min(studscore))/(count(*)-2)
else avg(studscore) end 平均分
from studscoreinfo
group by studno

10． 在学生信息表(StudInfo)中，选出 StudNo（学号）,StudName（姓名）,StudSex（性
别）,ClassID（班级编号）以中文名字作为别名，将表结构和数据同时存入新表名 为
ChineseStudInfo 的表中。

create table ChineseStudinfo(学号,姓名,性别,班级编号) 
as
select studno,studname,studsex,classid from studinfo

11．使用班级信息表（ClassInfo）、学生信息表(StudInfo)、课程信息表（CourseInfo）、学生
成绩信息表(StudScoreInfo)四表，建立连接查询，要求得到学号、姓名、性别、出生日
期、班级编号、班级名称、课程编号、课程名称、成绩字段。（注：要求使用 Where 关
联表、Inner Join 联接、Using 子句三种方法实现）

A.where 子句

select si.studno 学号，studname,studsex,studbirthday,si.classid,
c.classname,sc.courseid,ci.coursename,studscore
from classinfo  c ,studinfo si ,studscoreinfo sc , courseinfo ci
where c.classid=si.classid and si.studno = sc.studno and ci.courseid = sc.courseid 

B.inner join   子句

select si.studno 学号，studname,studsex,studbirthday,c.classid,
c.classname,si.courseid,ci.coursename,studscore
from 
((studinfo s inner join classinfo c on s.classid = c.classid)
inner join studscoreinfo si on si.studno = s.studno)
inner join courseinfo ci on ci.courseid = si.courseid


c.Using 子句 
select si.studno 学号，studname,studsex,studbirthday,c.classid,
c.classname,si.courseid,ci.coursename,studscore
from 
((studinfo s join classinfo c using(classid))
join studscoreinfo si using(studno))
join courseinfo ci using(courseid)

12． 查询课程平均分在 80 以上，参考人数在 30 人以上的课程信息。

select * from courseinfo
where courseid in(
select courseid
from studscoreinfo
group by courseid
having count(*) >10 and avg(studscore)>80
)

13． 写出在学生成绩信息表(StudScoreInfo)和学生信息表(StudInfo)中统计学生平均分
在 60-70 和 90-100 之间的学生成绩记录。包括学生学号、学生姓名、总分、平均分、课
程门数、课程最高分、课程最低分字段。

select si.studno,studname,sum(stuscore),avg(studscore),count(*),max(studscore),min(studscore)
from studscoreinfo ss,studinfo si
where ss.studno = si.studno
group by si.studno
having avg(studscore) between 60 and 70 or avg(studscore) between 90 and 100

14． 写出在学生成绩信息表(StudScoreInfo)和学生信息表(StudInfo)中查询学生性别为’
女’并且平均分大于 80 的学生基本信息。（用子查询 IN 或关联表两种方法实现）

select * from 
studinfo
where studsex='女' and studno in(
select studno from studscoreinfo
group by studno
having avg(studscore)>80)

15． 在学生成绩信息表（StudScoreInfo）、课程信息表（CourseInfo）中，统计各学生所
获得必修课程学分（课程类别为 A、B、C 的即为必修课，成绩大于等于 60 为获得该门
课程学分）。

select studno,studname,sum(coursecredit) as 必修学分
from studinfo join studscoreinfo using(studno)
join courseinfo using(courseid)
where studscore>60 and (coursetype='A' or coursetype='B' or coursetype='C')
group by studno,studname

16． 查询班级为“电信01”且课程名称为“英语(2)”的学生成绩大于该班该门课程平
均分的成绩记录。

select * from 
(select *
from studscoreinfo
where studno in
(
select studno from studinfo
where classid in 
(select classid from classinfo where classname='电信01')
)
and  courseid in (select courseid from courseinfo where coursename='英语(2)')) A
where 
studscore >
(
select avg(studscore) 平均分 from 
(select *
from studscoreinfo
where studno in
(
select studno from studinfo
where classid in 
(select classid from classinfo where classname='电信01')
)
and  courseid in (select courseid from courseinfo where coursename='英语(2)')) 
)

17． 查询同年同月同日出生且同班的学生成绩信息

select * from studscoreinfo
where studno in 
(
select studno from studinfo
where studbirthday in
(
select studbirthday
from studinfo
group by studbirthday,classid
having count(*)>1
)
)

18． 创建 StudInfoBack 空表，字段名以中文汉字命名，包括（学号、姓名、性别、出生
日期）字段，其数据类型与 StudInfo 表中的字段对应相同。
A. 使用 Insert Into table subquery 添加学生信息表 StudInfo 中 1986 年以后出生的男学生到 StudInfoBack 表中。
B. 在 StudInfoBack 表中，将姓赵的改成姓李
C. 使用 Merge 合并 StudInfo 数据到 StudInfoBack 中。

create table studinfoback
(
学号 varchar2(15) primary key,
姓名 varchar2(20),
性别 char(2) check(性别 in ('男','女')),
出生日期 date,
班级ID varchar2(10)
)

A
insert into studinfoback
select * from studinfo
where studbirthday > to_date('1986-01-01','yyyy-mm-dd') 
and studsex = '男'

commit

B
update studinfo 
set 姓名='李'||substr(姓名,2)
where studname = 
(
select 姓名 from studinfoback
where 姓名 like '赵%'
)

c
merge into table studinfoback a
using (select * from studinfo)
from studinfo s
when matched then
    update set studinfoback = studinfo
when not matched then
    insert(s.studno,s.studname,s.studsex,s.studbirthday,s.classid) value (a.学号,a.姓名,a.性别,a,出生日期，a.班级ID)