12天学完 SQL_Server笔记

最新推荐文章于 2024-09-15 06:31:07 发布

呱呱鸿

最新推荐文章于 2024-09-15 06:31:07 发布

阅读量144

点赞数

分类专栏： SQL 文章标签：百度 sql 数据库 sqlserver Powered by 金山文档

本文链接：https://blog.csdn.net/xiaohong_k/article/details/128757104

版权

SQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

学习视频：参考视频

主键：唯一且非空，即某条记录的唯一识别码

外键：就是在别的表出现过的主键，即其他表的主键可以是其他表中的一条记录或者可以为空

Day1：建表

create database stuinfo			--创建一个stuinfo的数据库
use stuinfo
create table major(
	mno int,
	mname varchar(20),
	primary key(mno)
)
select * from major

create table stu(
	sno int,
	sname varchar(30),
	age smallint,
	sex bit,
	mno int,
	primary key(sno),								--主键
	foreign key(mno) references major(mno)			--外键
)
select * from stu;

create table cou(
	cno int,
	cname varchar(30),
	ctime smallint,
	ccredit decimal(4,2)				--四位数，两个小数点
	primary key(cno)
)
select * from cou;

create table sc(
	sno int,
	cno int,
	grade decimal(5,2)
	primary key(sno,cno)
	foreign key(sno) references stu(sno),
)
--对sc表加一个外键
alter table sc add constraint fk_sc foreign key(cno) references cou(cno)

Day2：增删改

use stuinfo
alter table stu add qq varchar(20)
alter table stu drop column qq
select * from stu

create table t(
	tt int
)
drop table t		--删除表
select * from t		--无效

--插入数据
select * from major
select * from stu
insert into major(mno,mname) values(1,'计算机科学与技术')
insert into major(mno,mname) values(2,'软件工程')
--0表示女，1表示男
insert into stu values(1,'kk',20,0,null)
insert into stu values(2,'dd',20,1,1)
--delete
delete from stu where sno=1
select * from stu
--删除一个专业 1
delete from major where mno=1
--别的表有外键的先置成null  ，update 指更新
update stu set mno=null where mno=1
update stu set sname='康康' where sno=2

Day3：补充（*）增添一些数据（巩固，复习）

drop database stuinfo
create database stuinfo
use stuinfo
create table major(
	mno int primary key,
	mname varchar(30)
)
create table stu(
	sno varchar(13) primary key,
	sname varchar(30) not null,
	age int,
	sex bit,
	mno int,
	foreign key(mno) references major(mno)
)
create table cou(
	cno varchar(13) primary key,
	cname varchar(30) not null,
	ctime int,
	ccredit decimal(5,2)
)
create table sc(
	cno varchar(13),
	sno varchar(13),
	grade decimal(5,2),
	primary key(cno,sno),
	foreign key(cno) references cou(cno),
	foreign key(sno) references stu(sno),
)

select * from major
insert into major(mno,mname) values(1,'计算机科学与技术');
insert into major(mno,mname) values(2,'软件工程');
insert into major(mno,mname) values(3,'网络工程');
insert into major(mno,mname) values(4,'智能科学与技术');


select * from stu
insert into stu values('202001','小一',18,0,1);
insert into stu values('202002','小二',18,1,1);
insert into stu values('202003','小四',18,1,1);
insert into stu values('202004','小五',18,1,1);
insert into stu values('202005','小六',18,0,2);
insert into stu values('202006','小七',18,1,2);
insert into stu values('202007','小八',18,0,2);
insert into stu values('202008','小九',18,1,2);
insert into stu values('202009','小十',19,0,3);
insert into stu values('2020010','小十',20,0,3);
insert into stu values('2020011','小快',19,0,3);
insert into stu values('2020012','小冬',21,0,3);
insert into stu values('2020013','小宇',19,0,null);
insert into stu values('2020014','小点',19,0,4);
insert into stu values('2020015','彭杰',21,0,4);

select * from cou
insert into cou values('20201','C语言',32,5);
insert into cou values('20202','C#',32,3);
insert into cou values('20203','数据结构',16,5);
insert into cou values('20204','大学英语1',32,3.5);
insert into cou values('20205','大学英语2',32,3.5);
insert into cou values('20206','大学英语3',32,3.5);
insert into cou values('20207','大学英语4',32,3.5);

select * from sc
insert into sc(sno,cno,grade) values('202006','20201',null);
insert into sc(sno,cno,grade) values('202005','20201',null);
insert into sc(sno,cno,grade) values('202001','20201',90);
insert into sc(sno,cno,grade) values('202002','20201',90);
insert into sc(sno,cno,grade) values('202003','20201',90);
insert into sc(sno,cno,grade) values('202004','20201',90);
insert into sc(sno,cno,grade) values('202004','20202',90);
insert into sc(sno,cno,grade)values('202004','20203',90);
insert into sc(sno,cno,grade) values('202005','20203',90);

Day4：单表查询

%：可以表示为很多很多字符甚至可以没有也可以有

--查询全部学生的全部信息
select * from stu

--查询全部学生的学号sno和姓名sname
select sno,sname from stu

--查询全部学生的姓名和出生年份
select sname,2022-age as brith_date  from stu

--查询有选修课程的学生学号distinct去重
select  distinct sno from sc

--查询姓名是‘小十’的学生全部信息
select * from stu where sname='小十'

--查询课号十‘20201’且学生成绩高于80的学生学号like 或=、
select sno from sc where cno like '20201' and grade>=80


--如果like 后面的字符串不含有通配符（%,_用于模糊查询）那么=等价于like

--查询年龄在18-19之间的学生姓名，年龄2中写法
select sname,age from stu where age>=18 and age<=19				--方法1
select sname,age from stu where age between 18 and 19			--方法2

--查询专业号 01，02，04的学生信息 in
select * from stu where mno=1 or mno=2 or mno=4					--方法1
select * from stu where mno in(1,2,4)							--方法2

--查询专业号不是01，02，04的学生信息 not in
select * from stu where mno not in(1,2,4)

--查询所有姓“彭”的学生信息（模糊查询）
select * from stu where sname like '彭%'

--查询姓名中第二字为‘点’的学生姓名
select * from stu where sname like '_点%'

--查询名字有‘小’的学生信息
select * from stu where sname like '%小%'

--查询有选课记录，没有考试的选课信息
select * from sc where grade is null

Day5：orderby-聚集函数-groupby

count(*) 一个表统计有多少行

count(sno) 一个表sno值的个数(不为空)

聚集函数不能用在where语句中，只能用在select和group by 语句

--查询学生成绩,要求按照成绩由低到高(升序)
select * from sc order by grade

--查询学生成绩,要求按照成绩由高到低(降序)
select * from sc order by grade desc

--默认升序 order by		desc
--count(*)一个表统计有多少行
--count(sno)一个表sno值的个数(不为空)
--sum
--avg
--max
--min

--查询学生总人数
select * from stu
select COUNT(*) from stu

--查询选修了课程的学生人数
select * from sc
select COUNT(sno) from sc
	也可以--select COUNT(distinct sno) from sc

--查询选修‘20201’课程的学生平均成绩
select AVG(grade) from sc where cno='20201'

--查询选修20201课程的学生最高成绩
select MAX(grade) from sc where cno='20201'

--查询选修20201课程的学生最小成绩
select MIN(grade) from sc where cno='20201'

--求各个课程号以及相应的选修人数
select cno,COUNT(sno) as people from sc group by cno

--查询平均成绩大于等于90的学生学号和平均成绩(错误案例   正确案例)
--注意,where子句不能用聚集函数作为条件表达式
--group by...having
select sno,AVG(grade) from sc group by sno having AVG(grade)>=90

Day6：多表查询

union 并集

intersect 相交

except 除外

distinct 去除重复的

--连接查询
--1 等值查询
--查询每个学生的信息和选修课程的信息
--Stu sc
select stu.*,sc.* from stu,sc where stu.sno=sc.sno

--查询选修‘20201’学生的姓名sname
select sname from sc,stu where stu.sno=sc.sno and sc.cno='20201'


--多表连接
--查询每个学生的信息和选修课程的信息和学时
select stu.*,sc.*,ctime from stu,sc,cou where stu.sno=sc.sno and sc.cno=cou.cno

--2  左外连接
--查询所有的学生信息和选修课信息，但是没有选修的学生也要显示出来
select stu.*,sc.* from stu left outer join sc on stu.sno=sc.sno
--查询每个专业的学生人数，假设每个专业都有人
delete from major where mno=5
insert into major values(5,'aa')
select * from major
select mno,COUNT(sno) from stu group by mno having mno between 1 and 4


--嵌套查询
--1  不相关嵌套查询（子查询不依赖父查询
--查询选修‘20201’学生的姓名sname
select sname from stu,sc where stu.sno=sc.sno and sc.cno='20201'
select sname from stu where sno in (select sno from sc where cno='20202')
--查询选修‘20202’学生的姓名sname
--in（返回多个结果）, =（返回一个结果）
--建议:	in

--2   相关嵌套查询（将连接放在子查询里面）
--查询选修‘20201’学生的姓名sname 
select sname from stu,sc where stu.sno=sc.sno and sc.cno='20201'
select sname from stu where '20201' in(select cno from sc where stu.sno=sc.sno)
--查询选择‘C语言’课程的学生学号
select sno from sc where 'C语言' in(select cname from cou where sc.cno=cou.cno)
--查询每个学生超过他平均分的课程号 第二种方法用派生表实现
select sno,cno
from sc x
where
grade>(select AVG(grade) from sc y group by sno having x.sno=y.sno)

select sno,cno
from sc,(select sno,AVG(grade) from sc group by sno) as avg_sc(avg_sno,avg_grade)
where sc.sno=avg_sc.avg_sno and grade>avg_grade
--带有exista（涉及2个表也需要连接）

--查询选修‘20201’学生的姓名sname
select sname from stu,sc where stu.sno=sc.sno and sc.cno='20201'

select sname from stu where exists(
	select * from sc where cno='20201' and stu.sno=sc.sno
)
--返回true or false ,每次取一个sno连接

--集合查询 union intersect except

--查询年龄是18且mno=1的学生学号			intersect
select sno from stu where age=18 and mno=1
select sno from stu where age=18 intersect select sno from stu where mno=1
--查询年龄是18且mno=1的学生学号			except
select sno from stu where age=18 except select sno from stu where mno!=1
--查询选修‘20201’号课程或‘20203’的学生学号	union  
select distinct sno from sc where cno in('20201','20203')
select sno from sc where cno='20201' union select sno from sc where cno='20203'
select * from sc
select distinct sno from sc where cno='20201' intersect select distinct sno from sc where cno ='20203'

Day7：视图

视图是从一个或几个基本表(或视图)导出的表。不同的是,它是一虚表,数据库中只存放视图的定义,而不存放视图对应的数据,这些数据仍然存放在原本的基本表中。所以一旦基本表发生变化,从视图中查询的数据也就随之改变。

作用之一:视图更加方便用户的查询

--查询学生的（sno, sname,age)
create view v_stul
as
select sno,sname,age from stu
select * from v_stul

create view v_major
as select * from major
select * from major

--查询学生的信息(sn, sname, mname)
create view v_stu2
as
select sno,sname,mname from stu,major where stu.mno=major.mno
select * from v_stu2

--查询学生的信息(sno,avg( grade))
create view v_stu3
as
select sno,AVG(grade) as avg_grade from sc group by sno
select * from v_stu3 where avg_grade<85

--查询每个同学较自己平均分高的课程cno
--1,每个同学的平均分视图
--2.开始查询
select sc.sno,cno from sc,v_stu3 where sc.sno=v_stu3.sno and sc.grade>v_stu3.avg_grade

Day8：存储过程

存储过程是事先经过编译并保存在数据库中的一段sql语句集合，使用时调用即可。

proc 存储过程

drop proc p1 删除存储过程

begin

end
				存储过程结构

--返回学号 202005学生的成绩情况 存储名 p1
create proc p1
as
begin
	select * from sc  where sno='202005'
end
exec p1
--可编程性中查看建立
--查询某学生指定课程号的成绩和学分 alter 存储名p1
create proc p1 @sno varchar(13),@cno varchar(13)
as
begin
	select sc.*,cou.ccredit from sc,cou where sno=@sno and sc.cno=@cno and sc.cno=cou.cno 
end
exec p1 '202004','20203'

select * from cou where cno='20203'

--删除 p1
drop proc p1

Day9：触发器

触发器的定义：监视某种情况，并触发某种操作，当对一个表格进行增删改就有可能自动激活执行它

trigger 触发器

error 错误

结构

create trigger t1 on stu
after instead of
update insert delete
as
begin
......
end

--结构
create trigger t1 on stu
after instead of
update insert delete
as
begin
......
end

drop trigger t1
--学生人数不能大于17
create trigger t1 on stu after insert
as
begin
	if (select COUNT(*) from stu)>17
	begin
		print 'error'
		rollback tran			--不执行
	end
	else
	begin
		print 'right'
	end
end
insert into stu(sno,sname) values('2020017','kk')
insert into stu(sno,sname) values('2020019','kk')
delete from stu where sno='2020017'

--插入之前
alter trigger t1 on stu instead of insert
as
begin
	select * from inserted
	select * from deleted
	if (select COUNT(*) from stu)>16
	begin
		print 'error'
		rollback tran			--不执行
	end
	else
	begin
		print 'right'
		--insert
		declare @sno varchar(13)
		declare @sname varchar(30)
		declare @age int
		select @sno=sno from inserted
		select @sname=sname from inserted
		select @age=age from inserted
		insert into stu(sno,sname,age) values(@sno,@sname,@age)
	end
end

insert into stu(sno,sname) values('2020017','kk')
delete from stu where sno='2020015'
select COUNT(*) from stu

----------------------------------------------------
create trigger t2 on stu after delete
as
begin
	if (select COUNT(*) from stu)<16
	begin
		print 'error'
		rollback tran			--不执行
	end
	else
	begin
		print 'right'
	end
end

---after /insteead of
---当新增学生成绩55-59 改为60分
drop trigger t3
create trigger t3 on sc instead of insert
as
begin
	declare @sno varchar(13)
	declare @cno varchar(13)
	declare @grade decimal(5,2)
	select @sno=sno from inserted
	select @cno=cno from inserted
	select @grade=grade from inserted
	if @grade>=55 and @grade<=60 
	begin
		set @grade=60
	end
	insert into sc values(@cno,@sno,@grade)
end

insert into sc values('20205','202001','40')
select * from sc

Day10：函数

自定义函数

--计算某课程的平均分
--input cno
--output avgscore
create function fun1(@cno varchar(13))
returns int
as
begin
	declare @avgscore int
	
	select @avgscore=AVG(grade) from sc where cno=@cno
	
	return @avgscore
end
select dbo.fun1('20202')
--2.输入专业
--返回学生学号和姓名（这个专业）output table
alter function fun2(@mno int)
returns @snoSname table(
	sno varchar(13),
	sname varchar(30)
)
as
begin
	--declare @sno varchar(13)
	--declare @sname varchar(30)
	--sno sname mno  --stu
	--select @sno=sno,@sname=sname from stu where mno=@mno
	insert into @snosname(sno,sname) select sno,sname from stu where mno=@mno
	
	return
end
select * from dbo.fun2(1)
--3.输入专业号
--return 这个专业所有学生的每个课程对应成绩的一个表
create function fun3(@mno int)
returns @mSc table(
	sno varchar(13),
	cno varchar(13),
	grade decimal(5,2)
)
as
begin
	insert into @mSc select stu.sno,cno,grade from major,stu,sc where major.mno=stu.mno and stu.sno=sc.sno
	and stu.mno=@mno
	return
end
select * from fun3(1)

Day11：索引

定义：索引时对数据库表中的一列或者多列值进行排序的一种结构

目的：加快查询速度（目录）

但是占用一定的存储空间，更新和维护

不创建

1频繁更新字段或者经常增删改的表不适合创建索引

2表记录太少，不需要创建索引

3如果某些数据包含大量重复数据，因此建立索引就没有太大的效果，例如性别字段，只有男女，不适合建立索引

4stu sex 0 1

其实SQL server创建表时建立聚集索引

聚集索引定义：数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能有一个聚集索引

非聚集索引定义：数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序可能不同，一个表中可能有一个或多个非聚集索引

--sc表按学号升序和课程号降序建唯一索引
create unique index scno on sc(sno asc,cno desc)
--删除索引scno
drop index scno on sc

Day12:游标

游标定义：用来操作查询的结果集，是一个用户数据缓冲区

具体描述（光标）

优点：保存查询结果，方便以后使用。游标的结果集时select执行结果，需要的时候，只需一次，不用重复的查询

缺点：数据缓冲区，如果游标数据量大则会造成内存不足，所以，在数据量小时才使用游标

步骤

●声明游标

●打开游标

●取数据(循环)

●关闭游标或者deallocate

--语法
--游标名 cursor for select ...

-- 声明游标
declare my_cursor cursor for select mname from major
declare @mname varchar(30)
-- 打开游标
open my_cursor
-- 取数据(循环)
fetch next from my_cursor into @mname
while @@FETCH_STATUS=0
begin
	select @mname as 'mname'
	fetch next from my_cursor into @mname
end
-- 关闭游标或者deallocate
deallocate my_cursor

--语法
--游标名 cursor for select...

--声明游标
declare my_cursor cursor for select mname from major
declare @mname varchar(30)
--打开游标
open my_cursor
--取数据（循环）
fetch next from my_cursor into @mname
while @@FETCH_STATUS=0
begin
	select @mname as 'mname'
	fetch next from my_cursor into @mname
end
--关闭游标或者deallocate
deallocate my_cursor


select * from sc
--对sc表添加一个等级列，若学生成绩80以上等级A,70-79分为B，其余为C，null仍为null
select * from sc

declare my_cursor cursor for select cno,sno,grade from sc
declare @cno varchar(13)
declare @sno varchar(13)
declare @grade decimal
open my_cursor
fetch next from my_cursor into @cno,@sno,@grade
while @@FETCH_STATUS=0
begin
	if @grade>=80
		update sc set sc_rank='A' where cno=@cno and sno=@sno
	else if @grade>=70
		update sc set sc_rank='B' where cno=@cno and sno=@sno
	else if @grade>=0
		update sc set sc_rank='C' where cno=@cno and sno=@sno
	fetch next from my_cursor into @cno,@sno,@grade
end
deallocate my_cursor

--查询所有学生的专业名和姓名
select * from stu
select * from stu left outer join major
on stu.mno=major.mno

declare my_cursor cursor for select sname,mname from stu left outer join major on stu.mno=major.mno
declare @sname varchar(30)
declare @mname varchar(30)
open my_cursor
fetch next from my_cursor into @sname,@mname
while @@FETCH_STATUS=0
begin
	select @sname as 'sname' ,@mname as 'mname'
	fetch next from my_cursor into @sname,@mname
end
close my_cursor