创建表and插入数据
课程表
create database stuinfo
use stuinfo
create table major(
mno varchar(20),
mname varchar(20),
primary key(mno)
)
drop table 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,'人工智能')
insert into major (mno,mname) values(5,'计算机科学与技术')
select * from major
学生表
create table stu(
sno varchar(30),
sname varchar(30) not null,
age smallint,
sex bit,
mno varchar(20),
primary key(sno),
foreign key(mno) references major(mno)
)
drop table stu
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);
课程表
create table cou(
cno varchar(30),
cname varchar(30) not null,
ctime smallint,
ccredit decimal(4,2)
primary key(cno)
)
drop table 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 cou
成绩表
create table sc(
sno varchar(30),
cno varchar(30),
grade decimal(5,2)
primary key (sno,cno)
foreign key(sno) references stu(sno)
)
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 * from sc
–对sc表加一个外键
alter table sc add constraint fk_sc foreign key(cno) references cou(cno)
–添加qq字段
alter table stu add qq varchar(20)
select * from stu
–删除qq字段
alter table stu drop column qq
–创建表 删除表
create table t(
t int
)
select * from t
drop table t
select * from major
–删除一个专业(外键关联)
delete from major where mno=1
update stu set mno=null where mno=1
delete from major where mno=1
更新一条数据
update stu set sname='小小' where sno='2020002'
select * from stu
升序 降序 查询
select * from sc order by grade
select * from sc order by grade desc
聚集函数 count
select COUNT(sno) from sc
查询学生人数 去掉重复的sno学号 distinct
select COUNT(distinct sno) from sc
查询20201课程的学生平均成绩
select AVG(grade) from sc where cno='20201'
查询课程号20201 最高成绩的分数 最小值 最大值 min max
select min(grade) from sc where cno='20201'
select max(grade) from sc where cno='20201'
求各个课程号以及相应的选修人数 group by
select cno,COUNT(distinct sno) as num from sc group by cno
查询平均成绩大于等于90的学生号和平均成绩
select sno ,AVG(grade) from sc group by sno having AVG(grade)>=90
查询选修了‘20201’学生的姓名sname
select sname from stu,sc where stu.sno=sc.sno and cno='20201'
多表查询
链接查询 (等值链接)
select stu.*,sc.* from stu,sc where stu.sno=sc.sno
查询选修了‘20201’学生的姓名sname
select sname from stu,sc where stu.sno=sc.sno and cno='20201'
–多表链接
select stu.*,sc.*,ctime from stu,sc,cou where stu.sno=sc.sno and sc.cno=cou.cno
左外链接
(当需要保存一个表的信息时就需要用到左外链接)left outer join 条件是用的 on
select stu.*,sc.* 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
–查询每个专业的人数,但有的专业可能没有人
select * from major
select major.mno,COUNT(sno) as num from major left outer join stu on major.mno=stu.mno group by major.mno
嵌套查询
不相关嵌套查询(子查询不依赖父查询)
select sname from stu where sno in (select sno from sc where cno='20201')
select sname from stu where sno = (select sno from sc where cno='20202')
相关嵌套查询
select sname from stu where '20201' in(select cno from sc where stu.sno=sc.sno)
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
select sname from stu where sno in (select sno from sc where cno='20201')
select sname from stu where exists(
select *from sc where cno='20201' and stu.sno=sc.sno
)
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
select * from sc
select distinct sno from sc where cno='20201' intersect select distinct sno from sc where cno='20203'
查看表的主键
select "COLUMN_NAME" FORM "ALL_CONS_COLUMS" WHERE "TABLE_NAME"=‘TABLE_NAME’ AND ("CONSTRAINT_NAME" LIKE ‘%_PKEY’ OR "CONSTRAINT_NAME" LIKE ‘PK_%’);
视图
视图是从一个或几个基本表(或视图)到出的表。不同的是,它是一个虚表,数据库中只存放视图的定义,而不存放视图对应的数据,这些数据仍然存放在原来的基本表中。所以一旦基本表改变,从视图中查询出的数据也随之改变。
作用之一: 视图更加方便用户的查询。
视图的基本操作
- 创建视图
在这里插入代码片
create view v_stul as select sno,sname,age from stu
- 查询视图
select * v_stul
- 删除视图
drop view v_stul
- 删除原表的数据试图视图,从视图中查询出的数据也随之改变。
create view v_major as select * from mojor
select * from v_major
delete from major whele mno=5
再次查询,视图查询内容以改变
create view v_stul as select sno,sname,age from stu
- 查询学生的信息(sno,sname,mname)
创建视图
create view v_stu2
as
select sno,sname,mname from stu ,major where stu.mno=major.mno
select * from v_stu
- 查询学生的信息(sno,avg(grade))
select sno,AVG(grade) from sc group by sno
create view v_stu3
as
select sno,AVG(grade) as avg_grade from sc group by sno
select * from v_stu3
平均分小于70
select * from v_stu3 whele avg_grade<70
- 查询每个同学较自己平均分高的课程cno
每个同学的平均分视图:v_stu3
select sc.sno,cno from sc,v_stu3 whele sc.sno=v_stu3.sno and sc.grade>v_stu3.avg_grade
存储过程
存储过程是事先经过编译并保存在数据库中的一段spl语句集合,使用时调用即可。
- 返回学号2020005学生的成绩情况 储存名为p1
select * from sc where sno='2020005'
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 whele sno=@sno and sc.cno=@cno and sc.cno=cou.cno
end
exec p1 '2020004','20203'
- 删除存储过程 p1
drop proc p1
触发器
定义: 监视某种情况,并出发某种操作,当对一个表格进行增删改就能自动激活执行它
create trigger t1 on stu
after insert of
insert delete update
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
pring 'right'
end
end
- 触发器的使用
insert into stu(sno,sname) values('20200018','kk')
打印 ‘error’
并返回取消执行
- 触发器(先判断后执行)
alter trigger t1 on stu instead of insert
as
begin
select * from inserted
select * from deleted
if(select COUNT(*) from stu)>17
begin
print 'error'
rollback tran
end
else
begin
print 'right'
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
- 触发器
create trigger t1 on stu after delete
as
begin
if(select COUNT(*) from stu)<16
begin
print 'error'
rollback tran
end
else
begin
pring 'right'
end
end
删除学生触发触发器
delete from stu where sno='20200016'
当新增学生成绩为55-59 改为60分
alter trigger t1 on stu instead of insert
as
begin
declare @sno varchar(13)
declare @cno varchar(13)
declare @arade decimal(5,2)
select @sno=sno from inserted
select @cno=cno from inserted
select @grade=grade from inserted
if @grade>=55 and @argde<50
begin
set @grade = 60
end
insert into sc values(@sno,@sno,@grade)
end
函数
自定义函数
函数和存储过程很像,不同之处就是函数多了一个return
- 例1:计算某门课程的平均分
create function fun1(@con varchar(13))
returns int
as
begin
declare @avgscore int
select @avgsore=avg(grade)from sc where con=@ano
returns @avgscore
end
调用函数fun1()
select dbo.fun1('20202');
- 例2:输入专业号,返回学生号和姓名
alter function fun2(@mno int)
returns @snoSname table(
sno varchar(13)
sname varchar(30)
)
as
begin
insert into @anoSname(ano,sname) select sno,sname from stu where mno=@mno
end
select * from dbo.fun2(1)
- 例3:输入专业号,返回这个专业所有学生的每个课程对应成绩的一个表
create function fun3(@mno int)
returns @snoSname 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)
索引
定义:索引是对数据库表中的一列或者多列值进行排序的一种结构
目的:加快查询的速度(目录)select
但是占用一定的存储空间,
不建议创建索引:
- 频繁更新的字段或者经常增删改的表
- 表数据太少,不需要创建索引
- 如果某些数据包含大量重复数据,因此建立索引就没有太大的效果,例如性别字段,只有男(0)女(1)
QSL Sever默认主键为聚集索引
聚集索引:数据行的物理顺序与列值(一般是主键的那一列)的逻辑顺序相同,一个表中只能拥有一个聚集索引
create unique index scno on sc(sno asc,cno desc)
drop index scno on sc
游标
定义:
用来操作查询的一个结果集,是一个用户数据缓冲区
具体描述(光标)
fetch
优点:
保存查询结果,以便以后使用。游标结果集是select执行结果,需要的时候,只需一次,不用重复查询。
缺点:
数据缓冲区,如果游标数据量大则会造成内存不足。
所以,在数据量小时才使用游标
语法:
declare 游标名 cursor for select ...
步骤
- 声明游标
declare my_cursor cursor for select *from major
- 打开游标
open my_sursor
- 取数据(循环)
declare @mname varchar(30)
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
与视图比较
- 本质不同:一个是作为指针操作,一个是作为数据库对象
- 占用资源:多和少
- 工作方式:一个行处理,一个整个表(查询结果
- 数据库操作不同
视频教程见 B站 @DJ同学
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)