数据库笔记整理（主要为练习代码）

参考视频
后天开始看理论，被期末赶得哇哇大哭

建表

之后使用的数据库信息以及其中的数据信息如下

--数据库信息
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),
)
alter table sc add constraint fk_sc foreign key(cno) references cou(cno)

其中的数据

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('2020001','小一',18,0,1);
insert into stu values('2020002','小二',18,1,1);
insert into stu values('2020003','小四',18,1,1);
insert into stu values('2020004','小五',18,1,1);
insert into stu values('2020005','小六',18,0,2);
insert into stu values('2020006','小七',18,1,2);
insert into stu values('2020007','小八',18,0,2);
insert into stu values('2020008','小九',18,1,2);
insert into stu values('2020009','小十',19,0,3);
insert into stu values('20200010','小十',20,0,3);
insert into stu values('20200011','小快',19,0,3);
insert into stu values('20200012','小冬',21,0,3);
insert into stu values('20200013','小宇',19,0,null);
insert into stu values('20200014','小点',19,0,4);
insert into stu values('20200015','彭杰',21,0,4);
insert into stu values('20200016','彭小杰',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('2020006','20201',null);
insert into sc(sno,cno,grade) values('2020005','20201',null);
insert into sc(sno,cno,grade) values('2020001','20201',90);
insert into sc(sno,cno,grade) values('2020002','20201',90);
insert into sc(sno,cno,grade) values('2020003','20201',90);
insert into sc(sno,cno,grade) values('2020004','20201',58);
insert into sc(sno,cno,grade) values('2020004','20202',98);
insert into sc(sno,cno,grade) values('2020004','20203',90);
insert into sc(sno,cno,grade) values('2020005','20203',90);

简单增删改

alter table stu add qq varchar(20)
alter table stu drop column qq

create table t(
    tt int
)
drop table t

insert into major(mno,mname) values(1,'软件工程')

单表查询

select * from stu

select sno,sname from stu

select sname,2020-age as birth from stu

select distinct sno from sc

select * from stu where sname='小十'
select * from stu where sname like '小十'

select * from sc where cno='20201' and grade>80

select * from stu where age>=18 and age<=19
select * from stu where age between 18 and 19

select * from stu where mno 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

order by 聚集函数 group by

select * from sc order by grade
select * from sc order by grade desc

select count(*) from stu 

select distinct sno from sc
select count(distinct sno) from sc

select * from sc
select count(grade) from sc

select avg(grade) from sc where cno='20201'

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

--查询平均成绩>90的学生学号和平均成绩
select sno,avg(grade) from sc group by sno having avg(grade)>=90

--count sum avg max min 
--不可以出现在where语句后面，要使用group by和having

where子句不能用聚集函数作为条件表达式

多表查询

--等值查询
select stu.*,sc.* from stu,sc where stu.sno=sc.sno
select * from stu,sc where stu.sno=sc.sno

select * from stu,sc where stu.sno=sc.sno and sc.cno='20201'

--多表查询
select * from stu,sc,cou where stu.sno=sc.sno and sc.cno=cou.cno

--左外连接
--保留左表内数据，进行两表连接
--查询所有学生的信息和选课信息，但是没有选秀的学生也要显示出来
select * from stu left outer join sc
on stu.sno=sc.sno
--查询每个专业的人数，假设每个专业都有人
select mno,count(sno) from stu group by mno having mno between 1 and 4
--查询每个专业的人数，但是有的专业可能没有人
--需要保留major表，左外连接
insert into major values(5,'test')
select major.mno,count(sno) from major left outer join stu
on major.mno=stu.mno
group by major.mno

--嵌套查询
--①不相关的嵌套查询（子查询不依赖父查询）
--查询选修'20201'的学生姓名sname
select sname from stu,sc where cno='20201' and stu.sno=sc.sno
select sname from stu where sno 
in (select sno from sc where cno='20201')

--选修'20202'的学生的姓名
--建议用in而不是=
--in	返回多个结果
--=		返回一个结果
select sname from stu where sno = (
select sno from sc where cno='20202')

--②相关嵌套查询（将连接放在子查询里面）
--查询选修'20201'学生的姓名sname
select sname from stu,sc where cno='20201' and stu.sno=sc.sno
select sname from stu where '20201' 
in (select cno from sc where stu.sno=sc.sno)
--查询选修了c语言课程的学生学号
select sno from cou,sc where cou.cno=sc.cno  and cou.cname='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

--带有exists
--查询选修'20201'学生的姓名
select sname from stu,sc where stu.sno=sc.sno and 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的学生学号
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
select sno from stu where age=18 except select sno from stu where mno!=1

--查询选修'20201'号课程或'20203'的学生学号
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'

视图

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

视图更加方便用户的查询

--查询学生的信息(sno,sname,age)
create view v_stu1
as
select sno,sname,age from stu
select * from v_stu1
--查询学生的信息(sno,sname,mname)
drop view v_stu2
create view v_stu2
as
select sno,sname,stu.mno from stu left outer join major
on 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

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

存储过程

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

可通过表的可编程性->存储过程->系统存储过程

--返回学校2020005学生的成绩情况，存储名p1
create proc p1
as
begin
	select * from sc where sno='2020005'
end
exec p1

--查询某学生指定课程号和学分 alter 存储名p1
alter proc p1 @sno varchar(13),@cno varchar(13)
as
begin 
	select sc.*,cou.ccredit from sc,cou 
	where sno=@sno and sc.cno=cou.cno
end
exec p1 '2020004','20203'

--删除p1
drop proc p1

触发器

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

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

--学生人数不能大于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('8','kk')
delete from stu where sno='7'

--在插入之前执行触发器需要在触发器内进行数据插入
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(13)
		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
select count(*) from stu

--学生人数不能小于16
create trigger t2 on stu
after delete
as
begin
	if (select count(*) from stu)<16
	begin
		print 'error'
		rollback
	end
	else
		print 'right'
end
delete from stu where sno='2020001'

--当新增学生成绩55-59改成60
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 <=59
	begin
		set @grade=60
	end
	insert into sc values(@cno,@sno,@grade)
end

insert sc values('20202','20200016',57)
select * from sc

函数

--计算某门课程的平均分
--input cno
--output average
create function fun1(@cno varchar(13))
returns int 
as
begin
	declare @average int
	select @average=avg(grade) from sc where cno=@cno
	return @average
end
select dbo.fun1('20201')

--输入专业
--返回学生学号和姓名（这个专业）output table
create function fun2(@mno int)
returns @snoSname table(
	sno varchar(13),
	sname varchar(30)
)
as
begin
	--declare @sno varchar(13)
	--declare @sname varchar(30)
	--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)
--输入专业号
--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)

索引

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

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

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

不创建

1. 频繁更新字段或者经常增删改的表不适合创建索引
2. 表记录太少，不需要创建索引
3. 如果某些数据包含大量重复数据，因此建立索引就没有太大的效果，例如性别字段，只有男女，不适合建立索引
4. stu sex 0 1

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

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

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

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

游标

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

具体描述（光标）

优点：保存查询结果，方便以后使用。游标的结果集时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

--对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 sc


--查询所以学生的专业名和姓名
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

与视图比较

1. 本质不同：一个是作为指针操作，一个是作为数据库对象
2. 占用资源：多和少
3. 工作方式：一个行处理，一个整个表（查询结果
4. 数据库操作不同