SQL 高级子查询，联表

前面了解过了高级函数，这次来了解一下高级子查询和联表。

下面看一下前面了解过的高级查询

-- 聚合函数
-- sum求和,count计数,avg平均值,max最大值,min最小值

-- 分组函数 (group by)
-- where写在 group by 之前
-- order by 写在 group by 后面
-- select 后面只能出现分组的依据和聚合函数

接下来了解一下子查询

create table stuInfo --学生表
(
    stuNo      varchar(6)  not null primary key,
    stuName    varchar(10) not null,
    stuSex     varchar(2)  not null,
    stuAge     int         not null,
    stuSeat    int         not null identity (1, 1),
    strAddress varchar(255) default ('地址不详')
);

create table stuMarks --成绩表
(
    ExamNo      varchar(7) not null primary key,
    stuNo       varchar(6) not null references stuInfo (stuNo),
    writtenExam int        null,
    LabExam     int        null
);


insert into stuInfo(stuNo, stuName, stuSex, stuAge, strAddress)
select 's25301', '张秋丽', '男', 18, '北京海淀'
union
select 's25303', '李斯文', '女', 22, '河阳洛阳'
union
select 's25302', '李文才', '男', 85, '地址不详'
union
select 's25304', '欧阳俊雄', '男', 28, '新疆'
union
select 's25318', '梅超风', '女', 23, '地址不详';

insert into stuMarks(ExamNo, stuNo, writtenExam, LabExam)
select 's271811', 's25303', 93, 59
union
select 's271813', 's25302', 63, 91
union
select 's271816', 's25301', 90, 83
union
select 's271817', 's25318', 63, 53;

这里我们是先建了两个表

先了解一下子查询

1.子查询是根据某一个数据或者多个数据来进行查询的

一个数据的情况下可以用“=”来作为索引查询

多个数据的情况下需要使用“in”来作为索引查询

--01.先了解单个数据作为索引查询的

-- 查看年龄比“李斯文”大的学员

select * from stuinfo
where stuAge>
(select stuAge from stuInfo where stuName='李斯文');

输出结果

 这里分为两个查询，一个根据名字为李斯文的查询出一个年龄，另一个根据查询出的年龄根其他年龄作比较。子查询也可用与删除和修改，根查询一样用法。

--查询年龄与最大年龄一致的

select * from stuInfo where stuAge=(
    select max(stuAge) from stuInfo
);

输出结果

这里也可以排序取第一个的方法得到答案，虽然两个方法结果一样，但是排序的局限性明显小一点，而用子查询，它的局限性就大一点 ，也方便查询。

--03.查看年龄最小的

select * from stuInfo where stuAge=(
    select min(stuAge) from stuInfo
);

输出结果

 --04.查询成绩最高的学生

分两步，先把成绩最高的学生id查询出来，再根据id查询出那个学生

select * from stuInfo where stuNo=(
    select top 1 stuNo from stuMarks order by writtenExam desc
);

输出结果

--查询笔试成绩在70分以上的学生信息

1.先查询出来70分以上的学生的学号

select stuNO from stuMarks where writtenExam>70;

2.将学号放进去

select * from stuInfo where stuNo in (
    select stuNO from stuMarks where writtenExam>70
);

输出结果

条件放多少都没关系，只要能够运存并且不报错就行。 

接下来了解联表

--联表跟子查询其实本质上都是一样的，都是表与表之间建立联系

不同点就是：子查询是针对性的查询

                      联表是把数据整合起来查询

1.联表查询

--01.联表查询某个学生的成绩

查询李斯文的成绩

select * from stuInfo a inner join stuMarks b
on a.stuNo = b.stuNo
where a.stuname='李斯文';

输出结果

 

--02.查询笔试成绩大于全班笔试平均成绩的学生记录

select * from stuInfo a inner join stuMarks b
on a.stuNo = b.stuNo
where writtenExam>(
    select avg(writtenExam) from stuMarks
);

 输出结果

 这里如果用聚合函数的话，就不能使用where了，要用having

虽然联表查询跟子查询的本质差不多，但是我个人觉得联表查询更加简单，也更加好用。

子查询就比较考验理解能力了，而且很多时候没有链表查询好用。