数据库学习笔记

2021-12-26

建表

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);

单表查询
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

 多表查询
--等值查询
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