SQL Server 高级子查询

目录

一、子查询的用法：

1.查询tb_stu中比你输入的那个人的名字大的年龄的学生信息select*from tb_stu where sage>(select sage from tb_stu where sname='名字'）

2.比班级平均分高的学生信息select *from tb_grade where grade>(

二、 聚合函数的使用

-- 将一个sql语句的结果作为条件来判断：子查询-- 子查询的语句,查询的列只允许一个-- 子查询的语句,如果使用=,>,<,<=,>=结果必须只有一行-- 如果使用in,那么可以存在多行表：

--联表

---

一、子查询的用法：
1.查询tb_stu中比你输入的那个人的名字大的年龄的学生信息
select*from tb_stu where sage>(
select sage from tb_stu where sname='名字'
）
2.比班级平均分高的学生信息
select *from tb_grade where grade>(

select avg(grade) from tb_grade

)

使用 in 关键字 使可以返回多组数据 如果查询不在这里面的数据在 in 前面加上 not 即可

二、 聚合函数的使用

-- 学生表的男性的总人数
select count(*) as '男性人数' from stuInfo
where stuSex='男';
-- 男女的人数
select stuSex,count(*) from stuInfo group by stuSex;
-- 表关联操作
select * from stuInfo a inner join stuMarks b
on a.stuNo=b.stuNo;

-- 将一个sql语句的结果作为条件来判断：子查询
-- 子查询的语句,查询的列只允许一个
-- 子查询的语句,如果使用=,>,<,<=,>=结果必须只有一行
-- 如果使用in,那么可以存在多行
表：

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
);
-- 外键约束
-- 外键的值必须是主键已经有的
-- 删除主键的时候,外键对应的数据会全部删除
-- stuInfo(主表),stuMarks(从表)
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;
select * from stuinfo;
select * from stuMarks;
-- 查看年龄比“李斯文”大的学员
select * from stuinfo
where stuAge>
      (select stuAge from stuInfo where stuName='李斯文');
-- 查询出来李斯文的年龄
select stuAge from stuInfo where stuName='李斯文';
-- 查看性别和“李斯文”一致的学员
select * from stuInfo where stuSex = (
    select stuSex from stuInfo where stuName='李斯文'
);
-- 查询“李斯文”的性别
select * from stuInfo where stuName='李斯文';
-- 删除性别和“李斯文”一致的学员
delete from stuInfo where stuSex = (
    select stuSex from stuInfo where stuName='李斯文');
-- 查询年龄最大的学生信息
-- 排序取第一
select top 1 * from stuinfo order by stuAge desc;
-- 查询最大的年龄
select max(stuAge) from stuInfo;
-- 查询年龄和最大年龄一致的
select * from stuInfo where stuAge=(select max(stuAge) from stuInfo);
--查询年龄最小的学生信息
select * from stuInfo where stuAge=( select min(stuAge) from stuInfo);
--查询笔试成绩成绩最高的学生
-- 先查询笔试成绩最高的人是谁?
select top 1 stuNo from stuMarks order by writtenExam desc;
-- 查询学生表,学生表的学号要和成绩最高的那个学号相同
select * from stuInfo where stuNo=(
    select top 1 stuNo from stuMarks order by writtenExam desc
);
三、联表
select top 1
             a.stuNo,
             b.stuNo,
             stuName,
             writtenExam
from stuInfo a inner join stuMarks b
on a.stuNo = b.stuNo order by writtenExam desc;
-- 查询笔试成绩大于全班笔试平均成绩的学生记录
-- 聚合函数不能用在where里面
select * from stuInfo a inner join stuMarks b
on a.stuNo = b.stuNo
where writtenExam>(
    select avg(writtenExam) from stuMarks
);
-- 查询全班的平均成绩
select avg(writtenExam) from stuMarks;
-- 查询笔试成绩在70分以上的学生信息(禁止联表)
-- 1.先查询出来70分以上的学生的学号
select stuNO from stuMarks where writtenExam>70;
-- 2.将学号带过去
select * from stuInfo where stuNo in (
    select stuNO from stuMarks where writtenExam>70
);
-- 查看那些人没有考试
-- 子查询
select * from stuInfo where stuNo not in(
    select stuNo from stuMarks
);

--联表

select * from stuInfo a left join stuMarks b
on a.stuNo = b.stuNo where ExamNo is null;
select * from stuInfo;
select * from stuMarks;