sql数据库笔记整理
建表
之后使用的数据库信息以及其中的数据信息如下
--数据库信息
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)
索引
定义:索引时对数据库表中的一列或者多列值进行排序的一种结构
目的:加快查询速度(目录)
但是占用一定的存储空间,更新和维护
不创建
- 频繁更新字段或者经常增删改的表不适合创建索引
- 表记录太少,不需要创建索引
- 如果某些数据包含大量重复数据,因此建立索引就没有太大的效果,例如性别字段,只有男女,不适合建立索引
- 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
与视图比较
- 本质不同:一个是作为指针操作,一个是作为数据库对象
- 占用资源:多和少
- 工作方式:一个行处理,一个整个表(查询结果
- 数据库操作不同
本文转自 https://blog.csdn.net/qq_43717736/article/details/107722502,如有侵权,请联系删除。