SQL Server2019学习笔记

胆怯与勇敢

已于 2022-06-23 14:08:08 修改

阅读量1.7k

点赞数 6

文章标签：算法 sql

于 2021-11-27 21:56:58 首次发布

本文链接：https://blog.csdn.net/qq_52652401/article/details/121584342

版权

第一部分

–无条件查询

select distinct SNo 
from SC

–条件查询

--比较大小
select SNo,Score 
from SC 
where CNo='C1'

--多重条件查询
select SNo,CNo,Score 
from SC 
where (CNo='C1' or CNo='C2') and Score>=85

--确定范围
select TNo,TN,Prof 
from T
where Sal not between 1000 and 1500

--确定集合
select SNo,CNo,Score 
from SC
where CNo not in ('C1','C2')

--部分匹配查询
select TNo,TN 
from T 
where TN like '张%'

select TNo,TN 
from T 
where TN like '_力%'

--空值查询
select SNo,CNo 
from SC 
where Score is null

–常用库函数及统计汇总查询

--SUM和AVG函数
select sum(Score) as TotalScore,avg(Score) as AvgScore
from SC 
where SNo='S1'

--MAX和MIN函数
select max(Score) as MaxScore,min(Score) as MinScore,
max(Score)-min(Score) as diff 
from SC 
where SNo='S1'

--COUNT函数
select count(distinct Dept) as DeptNum 
from S

–分组查询

--查询选修两门以上课程的学生的学号和选课门数
select SNo,count(*) as C_Num 
from SC
group by SNo 
having(count(*)>=2)

–查询结果的排序

--查询结果按学号升序排列，学号相同再按成绩降序排列
select SNo,Cno,Score
from SC
where CNo in ('C2','C3','C4','C5')
order by SNo,Score desc

第二部分

1、内连接查询
–查询"刘伟"老师所讲授的课程，列出教师号、教师名和课程号

	--方法1
select T.TNO,TN,CNo 
from T,TC
where (T.TNO=TC.tNo) and TN='刘伟'

	--方法2
select T.TNO,TN,CNo 
from T 
inner join TC
on T.TNO=TC.tNo 
where TN='刘伟'

	--方法3
select R1.TNO,R2.TN,R1.CNo 
from (select TNo,CNo from TC) as R1
inner join
(select TNo,TN from T where TN='刘伟') as R2
on R1.tNo=R2.TNO

–查询每门课程的课程号、课程名和选课人数

select C.CNo,CN,count(SC.SNo) as 选课人数
from C,SC 
where SC.cNo=C.CNo
group by C.CNo,CN

2、外连接查询
–查询所有学生的学号、姓名、选课名称和成绩(没有选课同学显示为空)

select S.SNo,SN,CN,Score 
from S
left outer join SC 
on S.SNo=SC.sNo
left outer join C 
on C.CNo=SC.cNo

3、交叉连接

select * 
from S 
cross join C

4、自连接查询
–查询所有比"刘伟"工资高的教师姓名、工资和刘伟的工资

	--方法1
select X.TN,X.Sal AS Sal_a,Y.Sal AS Sal_b
from T as X,T as Y
where X.Sal>Y.Sal and Y.TN='刘伟'

	--方法2
select X.TN,X.Sal,Y.Sal
from T as X inner join T as Y
on X.Sal>Y.Sal and Y.TN='刘伟'

	--方法3
select R1.TN,R1.Sal,R2.Sal
from
(select TN,Sal from T) as R1
inner join
(select Sal from T where TN='刘伟') as R2
on R1.Sal>R2.Sal

–检索所有学生姓名、年龄和选课名称

	--方法1
select SN,Age,CN 
from S,C,SC
where S.SNo=SC.sNo and SC.cNo=C.CNo

	--方法2
select R3.SN,R3.Age,R4.CN
from 
(select SNo,SN,Age from S) as R3
inner join
(select R2.SNo,R1.CN
from
(select CNo,CN from C) as R1
inner join
(select SNo,CNo from SC) as R2
on R1.CNo=R2.cNo) as R4
on R3.SNo=R4.sNo

第三部分

–局部变量

declare @id char(8)
select @id='10010001'
print @id

declare @sno varchar(10),@sn varchar(10)
select @sno=SNo,@sn=SN 
from S 
where SNo='S1'
print @sno
print @sn

–赋值运算符

declare @MyCounter int
set @MyCounter=1
print @Mycounter

–批处理

create table dbo.T3(a int)
insert into dbo.T3 values(1)
insert into dbo.T3 values(1,1)
insert into dbo.T3 values(3)
go
select * from dbo.T3

–IF…ELSE语句

use School
go
if (select avg(Score) from SC where SNo='S1')>=60
	print 'Pass!'
else
	print 'Fail!'
go

–IF[NOT]EXISTS语句

use School
go
declare @message varchar(255) /*定义变量nessage*/
if exists(select * from S where SNo='S1')
	set @message='存在学号为S1的学生'
else
	set @message='不存在学号为S1的学生'
print @message
go

–CASE语句

	--普通版
use School
go
select SNo,Sex=
	case Sex
		when '男' then 'M'
		when '女' then 'F'
	end
from S
go

	--升级版
use School
go
select SNo,CNo,Score=
	case
		when Score is null then '未考'
		when Score<60 then '不及格'
		when Score>=60 and Score<70 then '及格'
		when Score>=70 and Score<90 then '良好'
		when Score>=90 then '优秀'
	end
from SC
go

–WHILE…CONTINUE…BREAK语句

--计算1-100之间所有能被3整除的数的总和及个数
declare @s smallint,@i smallint,@nums smallint
set @s=0
set @i=1
set @nums=0
while (@i<=100)
	begin
		if(@i%3=0)
			begin
				set @s=@s+@i
				set @nums=@nums+1
			end
		set @i=@i+1
	end
print @s
print @nums

–WAITFOR语句

waitfor delay '00:00:10'
select * from S

waitfor time '17:03:00'
select * from S

–GOTO语句

declare @s_1 smallint,@i_1 smallint
set @i_1=1
set @s_1=0
beg:
if (@i_1<=10)
	begin
		set @s_1=@s_1+@i_1
		set @i_1=@i_1+1
		goto beg /*使程序跳转到标号为beg的地方执行*/
	end
print @s_1

第四部分

–创建数据表

create table A_1
(SNo varchar(6),
SN nvarchar(10),
Sex nchar(1) default '男',
Age int,
Dept nvarchar(20))

–NULL/NOT NULL约束

create table A_2
(SNo varchar(6) constraint S_cons not null,
SN nvarchar(10),
Sex nchar(1),
Age int,
Dept nvarchar(20))

–UNIQUE约束(唯一约束)

--列约束
create table A_3
(SNo varchar(6),
SN nvarchar(10) constraint SN_UNIQ unique,
Sex nchar(1),
Age int,
Dept nvarchar(20))
	
--表约束
create table A_4
(SNo varchar(6),
SN nvarchar(10),
Sex nchar(1),
Age int,
Dept nvarchar(20),
constraint S_UNIQ unique(SN,Sex))

–PRIMARY KEY约束(主键约束)

--列约束
create table A_5
(SNo varchar(6) constraint S_PRIM primary key,
SN nvarchar(10),
Sex nchar(1),
Age int,
Dept nvarchar(20))
	
--表约束
create table A_6
(SNo varchar(6),
SN nvarchar(10),
Sex nchar(1),
Age int,
Dept nvarchar(20),
constraint SC_Prim primary key(SNo,SN))

–FOREIGN KEY约束(外键约束)

--列约束
create table A_7
(SNo varchar(2) constraint S_fore foreign key references S(SNo),
SN nvarchar(10),
Sex nchar(1),
Age int,
Dept nvarchar(20))

--表约束
create table A_8
(SNo varchar(2) constraint S_fore foreign key references S(SNo),
SN nvarchar(6) constraint C_fore foreign key references S(SN),
Sex nchar(1),
Age int,
Dept nvarchar(20),
constraint S_C_Prim primary key (SNo,SN))

–CHECK约束

--列约束
create table A_9
(SNo varchar(6),
SN nvarchar(10),
Sex nchar(1),
Age int constraint age_Chk check(Age>=0),
Dept nvarchar(20))

--表约束
create table A_10
(SNo varchar(6),
SN nvarchar(10),
Sex nchar(1),
Age int,
Dept nvarchar(20),
constraint S_CHk check(Age>18 and age<30))

–修改数据表

--ADD方式
	--增加新列
alter table A_10
add Score int
	
	--增加完整性约束
alter table A_10
add constraint Score_Chk check(Score between 0 and 100)

--ALTER方式
	--修改列
alter table A_10
alter column SN nvarchar(4)
	
	--修改NOT NULL约束
alter table A_10
alter column SNo varchar(6) not null 

--DROP方式
	--删除完整性约束
alter table A_10
drop constraint Score_Chk

	--删除数据表
drop table A_10

第五部分

–1.查询各位学生的学号、班级和姓名

select SNo,Class,SN 
from S

–2.查询课程的全部信息

select * 
from C

–3.查询数据库中有哪些专业班级

select distinct Class 
from S

–4.查询学时数大于60课程信息

select * 
from C 
where CT>60

–5.查询在1986年出生的学生的学号、姓名和出生日期

select SNo,SN,birthday 
from S 
where datename(year,birthday)=1986

–6.查询三次作业的成绩都在80分以上的学号、课程号

select SNo,CNo 
from SC 
where Score_1>=80 and Score_2>80 and Score_3>80

–7.查询姓张的学生的学号、姓名和专业班级

select SNo,SN,Class 
from S 
where SN like '张%'

–8.查询05级的男生信息

select * 
from S 
where right(Class,2)=05 and Sex='男'

–9.查询没有作业成绩的学号和课程号

select SNo,CNo 
from SC
where Score_1 is null or Score_2 is null or Score_3 is null

–10.查询学号为1538的学生的作业1总分

select sum(Score_1) as 总分 
from SC 
where SNo=1538

–11.查询选修了K001课程的学生人数

select count(SNo) as 选课人数 
from SC 
where CNo='K001'

–12.查询数据库中共有多少个班级

select count(distinct Class) as 班级数量 
from S

–13.查询选修三门以上（含三门）课程的学生的学号和作业1平均分、作业2平均分和作业3平均分

select SNo,avg(Score_1) 作业1平均分,avg(Score_2) 作业2平均分,avg(Score_3) 作业3平均分
from SC 
group by SNo 
having count(CNo)>=3

–14.查询于兰兰的选课信息，列出学号、姓名、课程名（使用两种连接查询的方式）

	--方法1
select S.SNo,SN,CN 
from S,C,SC
where S.SNo=SC.SNo and SC.CNo=C.CNo and SN='于兰兰'
	--方法2
select R4.SNo,R4.SN,R3.CN
from
(select CNo,CN from C) as R3
inner join
(select R1.SNo,R1.SN,R2.CNo
from
(select SNo,SN from S where SN='于兰兰') as R1
inner join
(select CNo,SNo from SC) as R2
on R1.SNo=R2.SNo) as R4
on R3.CNo=R4.CNo

第六部分

–1.查询与“张志国”同一班级的学生信息(使用连接查询和子查询方式)

	--连接查询(子连接查询)
select X.SNo,X.SN,X.Sex,X.Class,X.Birthday,X.Phone
from S as X,S as Y
where Y.SN='张志国' and X.Class=Y.Class and X.SN!='张志国'
	
	--子查询
select * from S where Class = (
	select Class from S where SN='张志国') and SN!='张志国'

–2.查询比“计算机应用基础”学时多的课程信息(使用连接查询和子查询方式)

	--连接查询
select X.CNo,X.CN,X.Credit,X.CT,X.Teach
from C as X,C as Y
where Y.CN='计算机应用基础' and X.CT>Y.CT
	
	--子查询
select * from C where CT>(
	select CT from C where CN='计算机应用基础')

–3.查询选修课程号为K002的学生的学号、姓名(使用连接查询、普通子查询、相关子查询、使用exists关键字的相关子查询)

	--连接查询(内连接查询)
select R1.SNo,R1.SN
from
(select SNo,SN from S) as R1
inner join
(select CNo,SNo from SC
where CNo='K002') as R2
on R1.SNo=R2.SNo
	
	--普通子查询
select SNo,SN from S where SNo in(
	select SNo from SC where CNo='K002')
	
select SNo,SN from S where SNo=any(
	select SNo from SC where CNo='K002')
	
	--相关子查询
select SNo,SN from S where SNo in(
	select SNo from SC where CNo='K002' and SNo=S.SNo)
	
	--使用exists关键字的相关子查询
select SNo,SN from S where exists(
	select * from SC where CNo='K002' and SNo=S.SNo)

–4.查询没有选修K001和M001课程的学号、课程号和三次成绩(使用子查询)

	--子查询(普通子查询)
select SNo,CNo,Score_1,Score_2,Score_3
from SC 
where CNo not in ('K001','M001')

–5.在学生表中添加一条学生记录，其中，学号为1593，姓名为张乐，性别为男，专业班级为电子05

insert into S(SNo,SN,Sex,Class)
	values(1593,'张乐','男','电子05')

–6.将所有课程的学分数变为原来的两倍

update C
set Credit=Credit*2

–7.删除张乐的信息

delete 
from S 
where SN='张乐'

第七部分

–1.创建一个电子05的学生视图(包括学号、姓名、性别、专业班级、出生日期)

go
create view S_E(SNo,SN,Sex,Class,Birthday)
as select SNo,SN,Sex,Class,Birthday 
	from S 
	where Class='电子05'
go

–2.创建一个生物05的学生作业情况视图(包括学号、姓名、课程名、作业1成绩、作业2成绩、作业3成绩)

go
create view SC_B(SNo,SN,CN,Score_1,Score_2,Score_3)
as
select R3.SNo,SN,CN,Score_1,Score_2,Score_3
from (select SNo,SN from S where Class='生物05') as R3
inner join
(select SNo,CN,Score_1,Score_2,Score_3
from 
(select CNo,CN from C) as R1
inner join 
(select * from SC) as R2
on R1.CNo=R2.CNo) as R4
on R3.SNo=R4.SNo
go

–3.创建一个学生作业平均成绩视图(包括学号、作业1平均成绩、作业2平均成绩、作业3平均成绩)

go
create view SC_Score(SNo,AVGScore_1,AVGScore_2,AVGScore_3)
as
select SNo,avg(Score_1),avg(Score_2),avg(Score_3)
from SC 
group by SNo
go

–4.修改第2题中生物05的学生作业情况视图，将作业2成绩和作业3成绩去掉

	--修改视图的列
alter view SC_B
as select SNo,SN,CN,Score_1 from SC_B

–5.向电子05的学生视图中添加一条记录，其中学号为1596，姓名为赵亦，性别为男，专业班级为电子05，出生日期为1986-6-8(除了电子05的学生视图发生变化之外，看看学生表中发生了什么变化？)

insert into S_E
	values(1596,'赵亦','男','电子05','1986-6-8')

–6.将电子05的学生视图中赵亦的性别改为“女”(除了电子05的学生视图发生变化之外，看看学生表中发生了什么变化？)

update S_E
set Sex='女'
where SN='赵亦'

–7.删除电子05的学生视图中赵亦的记录

delete S_E
where SN='赵亦'

–8.删除电子05的学生视图(给出SQL语句即可)

drop view S_E

第八部分

–(1)检索所有学生的选课信息，包括学号、姓名、课程号、课程名和成绩。

select R3.SNo,SN,CNo,CN,Score
from (select SNo,SN from S) as R3
inner join
(select SNo,R1.CNo,CN,Score
from 
(select SNo,CNo,Score from SC) as R1
inner join
(select CNo,CN from C) as R2
on R1.CNo=R2.CNo) as R4
on R3.SNo=R4.SNo

–(2)查询选修了“编译原理”的学生的学号和姓名。

select R3.SNo,SN
from (select SNo,SN from S) as R3
inner join
(select SNo
from 
(select SNo,CNo from SC) as R1
inner join
(select CNo from C where CN='编译原理') as R2
on R1.CNo=R2.CNo) as R4
on R3.SNo=R4.SNo

–(3)查询与“李思”在同一个专业的学生学号、姓名。

	--自链接
select Y.SNo,Y.SN
from S as X,S as Y
where X.Dept=Y.Dept and X.SN='李思' and Y.SN!='李思'
	--子查询
select SNo,SN from S where Dept=(
	select Dept from S where SN='李思') and SN!='李思'

–(4)查询其他系中比“信息”系的所有教师工资都高的教师的信息。

select * from T where Sal>all(
	select Sal from T where Dept='信息') and Dept!='信息'

–(5)查询比“周武”年纪大的男学生信息。

select * from S where Sex='男' and Age>(
	select Age from S where SN='周武') and SN!='周武'

–(6)查询每个学生选修课程的课程号和学号，要求该选修课程的成绩超过他选修课程的平均成绩。

select CNo,R1.SNo from
(select CNo,SNo,Score from SC) as R1
inner join
(select SNo,avg(Score) as avg_Score 
	from SC group by SNo) as R2
on R1.SNo=R2.SNo
where Score>avg_Score

–(7)检索最高分与最低分之差大于5分的学生的学号、最高分和最低分。

select R1.SNo,max_Score,min_Score 
from
(select SNo,max(Score) as max_Score,min(Score) as min_Score
	from SC group by SNo) as R1
where (max_Score-min_Score)>5

–(8)检索选修2门以上课程的学生的学号和总成绩（不统计不及格的课程），并要求按总成绩的降序排列出来。

select R1.SNo,sum(Score) as sum_score
from
(select SNo,Score from SC
	where Score>60) as R1
inner join 
(select SNo from SC
	group by SNo
	having count(CNo)>2) as R2
on R1.SNo=R2.SNo
group by R1.SNo
order by sum_score desc

–(9)查询选修了全部课程的学生学号和姓名。

select SNo,SN from S
where (not exists (select * from C 
	where not exists(select * from SC 
		where SNo=S.SNo and CNo=C.CNo)))

–(10)向SC表插入一条记录(’S6’，’C4’，79)，然后查询至少选修了学生“S6”选修的全部课程的学生的学号和姓名。

insert into SC(SNo,CNo,Score) values('S6','C4',79)
select SNo,SN from S
where (not exists (select * from SC as R1
	where SNo='S6' and not exists(select * from SC as R2
		where R2.SNo=S.SNo and R2.CNo=R1.CNo))) and SNo!='S6'

–(11)查询“李力”老师所讲授的课程，要求列出教师号、教师姓名和课程号。

select T.TNo,TN,CNo
from T,TC
where T.TNO=TC.TNo and TN='李力'

–(12)查询所有学生的学号、姓名、选课名称及成绩（没有选课的同学的选课信息显示为空）

select R1.SNo,SN,CN,Score
from (select SNo,SN from S) as R1
left outer join SC
on R1.SNo=SC.SNo
left outer join
(select CNo,CN from C) as R2
on R2.CNo=SC.CNo

–(13)查询选修课程号为C5的学生的学号，姓名(使用连接查询、普通子查询、相关子查询、使用exists关键字的相关子查询)

	--连接查询
select R1.SNo,R1.SN
from
(select SNo,SN from S) as R1
inner join
(select CNo,SNo from SC
where CNo='C5') as R2
on R1.SNo=R2.SNo
	
	--普通子查询
select SNo,SN from S where SNo in(
	select SNo from SC where CNo='C5')

select SNo,SN from S where SNo=any(
	select SNo from SC where CNo='C5')
	
	--相关子查询
select SNo,SN from S where SNo in(
	select SNo from SC where CNo='C5' and SNo=S.SNo)
	
	--使用exists关键字的相关子查询
select SNo,SN from S where exists(
	select * from SC where CNo='C5' and SNo=S.SNo)