SQLServer练习

create table Student
(
Sno char(6) primary key,
Sname nvarchar(20) not null,
Ssex nchar(1) not null default '男' check(Ssex='男' or Ssex='女'),
Sbirthday smalldatetime,
Sdept nvarchar(20),
Memo text
)
select * from Student
insert into Student values('060101','钟文辉','男','1997-05-01','计算机系','优秀毕业生'),
('060102','吴细文','女','1997-03-24','计算机系','爱好：音乐'),
('060103','吴朝西','男','1998-07-01','机电系',null),
('070101','王冲瑞','男','1998-05-04','机电系','爱好：音乐'),
('070102','林滔滔','女','1997-04-03','机电系','爱好：体育'),
('070103','李修雨','女','1996-03-03','机电系',null),
('070301','李奇','男','1998-09-17','信息管理系',null);

create table Course(
Cno char(3) primary key,
Cname nvarchar(20) not null,
PreCno char(3),
Credit tinyint,
Semester tinyint
)
insert into Course values
('C01','高等数学',null,4,1),
('C02','程序设计',null,4,2),
('C03','数据结构','C02',3,3),
('C04','数据库原理','C03',3,4),
('C05','音乐欣赏',null,1,4),
('C06','大学物理','C01',4,2),
('C07','计算机网络','C02',2,4)
select * from Course
create table SC(
Sno char(6),
Cno nchar(20),
Grade smallint check(Grade>=0 and Grade<=100),
primary key(Sno,Cno)
)
select * from SC
insert into SC values
('060101','C01',91),
('060101','C03',88),
('060101','C04',95),
('060101','C05',null),
('060102','C02',81),
('060102','C03',76),
('060102','C04',92),
('070101','C01',50),
('070101','C03',86),
('070101','C04',90),
('070101','C05',null),
('070103','C04',52),
('070103','C06',47),
('070301','C03',87),
('070301','C04',93)
--查询全体学生的学号与姓名
select Sno 学号,Sname 姓名 from student
--查询全部课程的课程号，课程名和开课学期
select Cno 课程号,Cname 课程名,Semester 开课学期 from Course
--查询全体学生的全部学生
select * from student
select Sno 学号,Sname 姓名,Ssex 性别,Sbirthday 出生时间,Sdept 系部,Memo 备注 from Student
--查询全体学生的姓名和年龄year(getdate())-year(Sbirthday)
select Sname 姓名,year(getdate())-year(Sbirthday) 年龄 from Student
select Sname 姓名,year(getdate())-year(Sbirthday) 年龄,'今年是','2022' 年份 from Student
--查询计算机系的学生的姓名和所在系部
select Sname 姓名,Sdept 系部 from Student where Sdept= '计算机系'
--查询成绩小于60的学生的学号，课程号和成绩
select * from Course
select Sno 学号,Cno 课程号,Grade 成绩 from SC where Grade<60
--查询成绩大于等于90的学号吗，课程号和成绩
select Sno 学号,Cno 课程号,Grade 成绩 from SC where Grade>=90
--查询成绩在60到80之间的学号吗，课程号和成绩
select Sno 学号,Cno 课程号,Grade 成绩 from SC where Grade between 60 and 80
--查询1997年出生的学生学号，姓名，出生日期和所在系部
select Sno 学号, Sname 姓名,Sbirthday 出生日期,Sdept 系部 from Student
where Sbirthday between '1997-01-01' and '1997-12-31'
select Sno 学号, Sname 姓名,Sbirthday 出生日期,Sdept 系部 from Student
where year(Sbirthday)=1997
--查询成绩在60到80之间的学号吗，课程号和成绩
select Sno 学号,Cno 课程号,Grade 成绩 from SC where not Grade between 60 and 80
--查询学分在2至3之间的课程的课程名，学分和开课日期
select Cname 课程名, Credit 学分, Semester 开课日期 from Course
where Credit between 2 and 3
select Cname 课程名, Credit 学分, Semester 开课日期 from Course
where not Credit between 2 and 3
--查询计算机系和机电系的学生的学号，姓名和所在系
select Sno 学号, Sname 姓名, Sdept 系部 from Student
where Sdept in ('计算机系','机电系')
--查询成绩为57、88、91的学生的学号，对应的课程号和成绩
select Sno 学号,Cno 课程号,Grade 成绩 from SC where Grade in (57,88,91)
--查询不是计算机系和机电系的学生的学号，姓名和所在系
select Sno 学号, Sname 姓名, Sdept 系部 from Student
where not Sdept in ('计算机系','机电系')
--查询姓“李”的学生的学号，姓名和所在系部
select Sno 学号, Sname 姓名, Sdept 系部 from Student
where Sname like '李%'
--查询姓名第二个字是“冲”的学生的学号，姓名和所在系部
select Sno 学号, Sname 姓名, Sdept 系部 from Student
where Sname like '_冲%'
--查询学号倒数第二位是0的学生的学号，姓名和所在系部
select Sno 学号, Sname 姓名, Sdept 系部 from Student
where Sno like '%0_'
--查询学号最后一位是2,3的学生的学号，姓名和所在系部
select Sno 学号, Sname 姓名, Sdept 系部 from Student
where Sno like '%[2,3]'
--查询学号第二位是6,7的学生的学号，姓名和所在系部
select Sno 学号, Sname 姓名, Sdept 系部 from Student
where Sno like '_[6,7]%'
--查询学号倒数第二位不是1,2的学生的学号，姓名和所在系部
select Sno 学号, Sname 姓名, Sdept 系部 from Student
where Sno like '%[^1,2]_'
--查询学号最后一位不是2或3的学生的学号，姓名和所在系部
select Sno 学号, Sname 姓名, Sdept 系部 from Student
where Sno like '%[^2,3]'
--查询还没有考试的学生的学号，相应的课程号和成绩
select * from SC where Grade is null
--查询有备注的学生的学号，姓名和备注
select Sno,Sname,Memo from Student where Memo is not null
--查询机电系有备注的学生的学号，姓名，所在系和备注
select Sno 学号,Sname 姓名, Sdept 系部,Memo 备注 from Student
where Sdept='机电系' and Memo is not null
--查询计算机和机电系的学生的学号，姓名，所在系和出生日期
select Sno 学号,Sname 姓名, Sdept 系部,Sbirthday 出生日期 from Student
where Sdept in('机电系','计算机系')
--查询计算机1997年出生的学生的学号，姓名，所在系和出生日期
select Sno 学号,Sname 姓名, Sdept 系部,Sbirthday 出生日期 from Student
where year(Sbirthday)=1997 and Sdept='计算机系'
--查询机电系1997年出生的学生的学号，姓名，所在系和出生日期
select Sno 学号,Sname 姓名, Sdept 系部,Sbirthday 出生日期 from Student
where year(Sbirthday)=1997 and Sdept='机电系'
--查询计算机系和机电系1997年出生的学生的学号，姓名，所在系和出生日期
select Sno 学号,Sname 姓名, Sdept 系部,Sbirthday 出生日期 from Student
where year(Sbirthday)=1997 and Sdept in('机电系','计算机系')
select Sno 学号,Sname 姓名, Sdept 系部,Sbirthday 出生日期 from Student
where year(Sbirthday)=1997 and (Sdept ='计算机系'OR Sdept = '机电系');
--查询学生信息表里面的所在系
--distinct处理相同的信息
select distinct Sdept from Student
--查询有考试挂科的学生的学号
select distinct Sno 学号 from SC where not Grade>=60
--查询有考试挂科的学生的学号和相应的课程号
select distinct Sno 学号,Cno 课程号 from SC where not Grade>=60
--order by 列名 asc/desc
--将C01号课程的成绩按升序排列
select Grade from SC where Cno='C01' order by Grade
--将C01号课程的成绩按降序排列
select Grade from SC where Cno='C01' order by Grade desc

--使用聚合函数查询
--1、count(*)统计学生个数
select COUNT(Sname) 学生个数 from Student
--2、统计课程的总个数
select * from Course
select count(Cno) 个数 from Course
--3、统计学生信息表中含有几个系
select COUNT(distinct(Sdept)) 个数 from Student
--4、统计学号060101学生的总分
select sum(Grade) 学生总分 from sc where Sno='060101'
--5、统计C01课程的平均分，最高分和最低分
select avg(grade) 平均分,max(grade) 最高分,min(grade) 最低分 from SC where Cno='C01'
--对数据分组group by <分组依据> [,...n] [having <组提取条件>]
--语法
/*select 目标列明1，目标列明2，...,聚合函数1,聚合函数2... from 表名 group by 分组依据列名
目标列只能来源于分组依据列。
having 用于对分组自身进行限制*/
--1、统计每门课程的选课人数，列出课程号和选课人数
select Cno,count(Sno) 选课人数 from SC where Cno='C01' group by Cno
--2、统计每名学生的选课人数，列出学号和选课门数
select Sno 学号,count(Cno) 选课门数 from SC group by Sno
--3、统计每个系的男生人数和女生人数，列出系别，性别和人数
select Sdept 系别,Ssex 性别,count(Ssex) 人数 from Student group by Sdept,Ssex
--4、统计每个系的男生人数
select sdept,count(*) 男生人数 from Student
where Ssex='男' group by Sdept,Ssex having Ssex='男'
--5、统计每个系的男生人数，列出男生人数等于2的所在系别
select sdept,count(*) 男生人数 from Student
where Ssex='男' group by Sdept,Ssex having count(*)=2
--6、统计选修课程门数超过3门的学生学号和课程门数
select Sno 学号,count(Cno) 课程门数 from SC
group by Sno having count(Cno)>3
--7、统计超过3人选修的课程的课程号和选课人数
select Cno 课程号,count(Sno) 选课人数 from SC
group by Cno having count(Sno)>3
--查询全体学生的学号，姓名和所在系
select Sno,Sname,Sdept from Student
where Sdept='计算机系'
select * from Student where year(Sbirthday)=1997
select Sno,Sname,Sdept,year(Sbirthday) 年龄 from Student
where Sno like '_[6,7]%'
select Sno,Sname,Sdept,Memo from Student
where Memo is not null
select Sno,Sname,Sdept,Memo from Student
where Sdept in('计算机系','机电系')
select * from Student
where year(Sbirthday)=1997 and Sdept in('计算机系','机电系')
select Sno,Sname,Sdept from Student
where Sdept in('计算机系','机电系')
order by Sdept asc
select Sno,Sname,Sdept from Student
where Sdept in('计算机系','机电系')
order by Sdept desc
select sdept 系名,Ssex 性别, count(Sno) 人数 from student
group by sdept,Ssex
select sdept 系名,Ssex 性别, count(Sno) 人数 from student
where Ssex='男' group by sdept,Ssex
select * from sc
select Cno,count(Cno) 选课人数 from SC
group by Cno
having count(cno)>3
--查询计算机系学生的基本信息和选课信息
select * from Student inner join SC on Student.Sno=SC.Sno
where Sdept='计算机系'
--查询全体学生的基本信息和选课信息，不包括重复的属性列
select * from Student join SC on Student.Sno=SC.Sno
--查询选课的学生的学号，姓名，所在系，选修的课程号和成绩
select st.sno,st.sname,st.sdept,sc.Cno,sc.Grade from student st join sc on st.Sno=sc.Sno
--查询选课的学生的学号，姓名，所在系，年龄，选修的课程号和成绩
select st.sno,st.sname,st.sdept,2022-year(st.Sbirthday),sc.Cno,sc.Grade from student st join sc on st.Sno=sc.Sno
--自连接
--1、查询与王冲瑞同一个系的学生的姓名和所在系
select s2.Sname,s2.Sdept from Student s1 join Student s2 on s1.Sdept=s2.Sdept
where s1.Sname='王冲瑞'
select Sname,Sdept from Student
where Sdept=(select sdept from Student where Sname='王冲瑞')
--2、查询与程序设计课程同一学期开课的课程名和开课学期
select c2.Cname,c1.Semester from Course c1 join Course c2 on c1.Semester=c2.Semester
where c1.Cname='程序设计'
--左外连接
--left [outer] join
--右外连接
--right[outer] join
--1、查询计算机系学生的选课情况（姓名，学号，所在系）
select s1.Sname,s1.Sno,s1.Sdept from Student s1 left join SC s2 on s1.Sno=s2.Sno
where s1.Sdept='计算机系'
--2、查询没有选的课程的课程名
select c.Cname from Course c left join SC on c.Cno=sc.Cno
where sc.Cno is null
--3、统计计算机系学生的选课人数，包括未选课的学生
select s1.Sno,COUNT(*) from Student s1 left join SC s2 on s1.Sno= s2.Sno
where s1.Sdept='计算机系' group by s1.Sno
--4、统计机电系学生选课门数少于3门的学生的选课门数，包含未选课的学生，按照选课门数的降序排序
select s1.Sno,COUNT(s1.sno) 选课门数 from Student s1 left join SC s2 on s1.Sno= s2.Sno
where s1.Sdept='机电系' group by s1.Sno having COUNT(s1.sno)<3 order by COUNT(*) desc

--top 的使用
--top n percent
select top n (percent) (with ties) 目标列名1，目标列名2.... from 表名

select 目标列名1,目标列名2,
case 测试表达式
when 值1 then 结果1
when 值2 then 结果2
when 值3 then 结果3
else 结果n
end 列名,.... from 来源表名
select 目标列名1,目标列名2,
case
when 测试表达式=值1 then 结果1
when 测试表达式=值2 then 结果2
when 测试表达式=值3 then 结果3
else 结果n
end 列名,.... from 来源表名

where 行选择条件
order by 排序依据列 asc/desc
group by 分组依据列
having 组内选择条件
--1、查询C04号课程的前三名学生的学号和成绩
select top 3 Sno,Grade from SC where Cno='C03' order by Grade desc
--2、查询学分最多的四门课程的名称，学分和开课学期
select top 4 c.Cname,c.Credit,c.Semester from SC join Course c on SC.Cno=c.Cno
select Sno,Sname,Ssex,Sbirthday,Sdept,
case Sdept
when '计算机系' then 'CS'
when '机电系' then 'ID'
when '信息管理系' then 'IM'
end '系代码',Memo
from Student
--2、查询“C04”号课程的考试情况，列出学号和成绩，同时对成绩进行如下处理：
--如果成绩大于等于90，则在查询结果中显示“优”；
--如果成绩在80到89分之间，则在查询结果中显示“良”；
--如果成绩在70到79分之间，则在查询结果中显示“中”；
--如果成绩在60到69分之间，则在查询结果中显示“及格”；
--如果成绩小于60分，则在查询结果中显示“不及格”。
select Sno,Grade,
case
when Grade>=90 then '优'
when Grade between 80 and 89 then '良'
when Grade between 70 and 79 then '中'
when Grade between 60 and 69 then '及格'
when Grade<60 then '不及格'
end 等级 from SC where Cno='C04'
--3、统计“计算机系”每个学生的选课门数，包括没有选课的学生。
--列出学号、选课门数和选课情况，其中对选课情况的处理为：
--如果选课门数超过4门，则选课情况为“多”；
--如果选课门数在2~4范围内，则选课情况为“一般”；
--如果选课门数少于2门，则选课情况为“少”；
--如果学生没有选课，则选课情况为“未选”。
--并将查询结果按选课门数降序排序。
select st.Sno 学号,count(Cno) 选课门数,
case
when count(Cno)>4 then '多'
when count(Cno) between 2 and 4 then '一般'
when count(Cno) between 0 and 2 then '少'
when count(Cno)=0 then '未选'
end 选课情况 from Student st left join SC on st.Sno=SC.Sno
where st.Sdept='计算机系'
group by st.Sno order by count(Cno) desc

select * from Course
select * from sc
select * from Student

--1、查询全体学生的姓名、学号和所在系
select Sno,Sname,Sdept from Student
--2、查询全体学生的详细记录
select * from Student
--3、查询全体学生的姓名及其出生年份
select Sname,YEAR(Sbirthday) 出生年份 from Student
--4、查询全体学生的姓名、出生年份和所在院系
select Sname,YEAR(Sbirthday),Sdept from Student
--5、查询选修了课程的学生学号
select * from SC where not Grade is null
--6、查询计算机科学系全体学生的名单
select Sname from Student where Sdept='计算机系'
--7、查询所有年龄在20岁以下的学生姓名及其年龄
select Sname, 2022-YEAR(Sbirthday) 年龄 from Student where 2022-YEAR(Sbirthday)>20
--8、查询考试成绩不及格的学生的学号
select Sno from SC where Grade<60
--9、查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄
select Sname,Sdept,2022-YEAR(Sbirthday) 年龄 from Student where 2022-YEAR(Sbirthday) between 20 and 23
--10、查询年龄不在20~23岁之间的学生的姓名、系别和年龄