SQL server高级子查询

-- 高级查询

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

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

-- 联表查询 A,B
-- 左连接 left join
-- 右连接 right join
-- 全连接 full join
-- 内连接 inner join
-- select * from A inner join B
-- on A.sid=B.stu_id

------------------------------------------

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 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,那么可以存在多行

-- 查看年龄比“李斯文”大的学员
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 * 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;