秒懂SQL

一、表信息

major专业表:mno mname

stu学生表:sno sname age sex mno

cou课程表:cno cname ctime ccredit

​ sc成绩表:sno cno grade

此处mno sno cno 都是主键

学生表中mno是外键

eg. mno mname

1“计算机科学与技术”

2“软件工程”

二、创建表

create database 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 stu create table sc( sno int, cno int, grade decimal(5,2) primary key (sno,cno) foreign key(sno) references stu(sno) ) alter table sc add constraint fk_sc foreign key(cno) references cou(cno)

三、增删改

use stuinfo alter table stu add qq varchar(20) --删除字段 alter table stu drop column qq select * from stu create table t( tt int ) select * from t --删除表 drop table t select * from major insert into major(mno,mname) values(1,'计算机科学与技术') insert into major(mno,mname) values(2,'软件工程') --0表示女 1表示男-- --插入学生表信息-- select * from stu insert into stu values(1,'小k',20,0,null) insert into stu values(2,'小X',21,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='大X' where sno=2

四、单表查询

use stuinfo --查询全部学生的信息-- select * from stu --查询全部学生的学号sno和姓名sname-- select sno,sname from stu --查询全部学生的姓名和出生年份-- select sname,2020-age as birthdate from stu --查询有选课程的学生学号 distinct去重--- select distinct sno from sc --查询姓名是‘小十’的学生全部信息 like或=-- select * from stu where sname='小十' select * from stu where sname like '小十' --查询课号是‘20201’且学生成绩高于80的学生学号--如果like后面的字符串不含有通配符（%，用于模糊查询，那么=等价于like） select sno from sc where cno='20201' and grade>80 select sno from sc where cno like '20201' and grade>80 --查询年龄在18-19之间的学生姓名-- select sname,age from stu where age>=18 and age <= 19 select sname,age from stu where age=18 or age = 19 select sname,age from stu where age between 18 and 19 --查询专业号是01，02，04的学生信息--in 就是 or select * from stu where mno=1 or mno=2 or mno=4 select * from stu where mno!=3 select * from stu where mno in(1,2,4) --查询专业号不是01，02，04的学生信息-- 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

五、聚集函数、order by 与group by

1.order by 默认是升序（从低到高），order by …desc是降序。

2.count(*) count(sno) sum avg max min

这些聚集函数不能出现在where子句中 ，可用 group by …having +条件

--聚集函数、order by 和group by-- use stuinfo --查询学生成绩 要求按照学生成绩由低到高（升序）-- select * from sc order by grade --查询学生成绩 要求按照学生成绩由高到低（降序）-- select * from sc order by grade desc --查询学生总人数-- select count(*) from stu --查询选了课程的学生人数-- select * 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' --求各个课程号以及相应的选修人数--select 与gro up by 都要有cno select cno,COUNT(sno) as num from sc group by cno --查询平均成绩大于等于90的学生学号和平均成绩（where子句不能用聚集函数作为条件表达式）-- select sno,AVG(grade) from sc where AVG(grade)>=90--错误-- select sno,AVG(grade) from sc group by sno having AVG(grade)>=90--正确--

六、多表查询

1.连接查询

2.嵌套查询

3.带有exists查询

4.集合查询

use stuinfo --连接查询 --①等值连接-- --查询每个学生的信息和选修课程的信息-- select stu.*,sc.* from stu,sc where stu.sno=sc.sno --查询选修了‘20201’学生的姓名sname-- select sname from stu,sc where stu.sno=sc.sno and sc.cno='20201' --多表连接 --查询每个学生的信息和选修课程的信息和学时-- select stu.*,sc.*,ctime from stu,sc,cou where stu.sno=sc.sno and cou.cno = sc.cno --②左外连接-- --查询每个学生的信息和选修课程的信息，没选课的学生也要显示-- select stu.*,sc.* from stu left outer join sc on stu.sno=sc.sno --查询每个专业的学生人数，假设每个专业都有人-- select * from major insert into major values(5,'aa') delete from major where mno=5 select mno,COUNT(sno) from stu group by mno having mno between 1 and 4 --查询每个专业的学生人数，假设有的专业没有人-- insert into major values(5,'aa')--先插入一个没有人的专业-- select major.mno,COUNT(sno) from major left outer join stu on major.mno=stu.mno group by major.mno --嵌套查询-- --①不相关嵌套查询 子查询不依赖父查询 --查询选修20201学生的姓名--in返回多个结果 =返回一个结果（建议统一用in） 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 = '20201') --错误 select * from sc select sname from stu where sno =(select sno from sc where cno = '20202') --②相关嵌套查询 （将连接放在子查询里面） --查询选20201的学生的姓名sname-- 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,AVG(grade) as '平均分'from sc group by sno --查询每个学生超过他平均分的课程号-- select sno,cno from sc x where grade>(select AVG(grade) from sc y group by sno having x.sno=y.sno) --派生表方法--不用create新建表而是使用派生表-- select sno,cno from sc,(select sno,AVG(grade)from sc group by sno)as avg_sc(avg_sno,avg_grade) where sno=avg_sno and grade >avg_grade --where sc.sno=avg_sc.avg_sno and sc.grade >avg_sc.avg_grade --带有exists（涉及2个表也需要连接） --查询选修‘20201’学生的姓名sname--返回ture or false 每次取一个sno连接 select sname from stu where exists( select * from sc where cno = '20201' and stu.sno=sc.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 select sno from stu where age=18 except select sno from stu where mno!=1 --查询选修‘2021’课程或‘20203’的学生学号 select distinct sno from sc where cno in ('20201','20203') select distinct sno from sc where cno='20201' or cno = '20203' select sno from sc where cno='20201' union select sno from sc where cno='20203' --查询选修‘2021’课程且‘20203’的学生学号 select sno from sc where cno='20201' and cno = '20203'--错误-- select sno from sc where cno='20201' intersect 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 create view v_major as select * from major --创建视图 select *from v_major delete from major where mno=5 --查询学生的信息（sno,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 平均分 from sc group by sno select * from v_stu3 where 平均分<89 --查询每个同学较自己平均分高的课程cno ---1.每个同学的平均分视图 ---2.开始查询 select sc.sno,cno from sc,v_stu3 where sc.sno = v_stu3.sno and sc.grade>v_stu3.平均分

八、存储过程

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

数据库中查看存储过程：数据库–stuinfo–可编程性–存储过程

--返回学号2020005学生的成绩情况 存储名p1-- select * from sc where sno='2020005' create proc p1 as begin select * from sc where sno='2020005' end --begin end 就是一对括号的意思 --调用p1 exec p1 --查询某学生指定课程号的成绩和学分 alter 存储名p1-- --因为已经有p1 所以不能再create创建按 可以alter修改-- alter proc p1 @sno varchar(50),@cno int as begin select sc.*,cou.ccredit from sc,cou where sno=@sno and sc.cno=@cno and cou.cno=sc.cno end exec p1 '2020004','20203' --删除p1-- drop proc p1

九、触发器

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

create trigger t1 on stu after instead of 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 --查询 select COUNT(*)from stu --插入 insert into stu(sno,sname) values('20200017','KK') insert into stu(sno,sname) values('20200018','YK')--触发器提示错误 因为超过17 ---触发器改为插入之前------ 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(50) declare @sname varchar(30) declare @age smallint declare @sex bit declare @mno int --赋值 select @sno=sno from inserted select @sname=sname from inserted select @age=age from inserted select @sex=sex from inserted select @mno=mno from inserted insert into stu(sno,sname,age,sex,mno) values(@sno,@sname,@age,@sex,@mno) end end delete from stu where sno='20200017' insert into stu(sno,sname) values('20200017','KK') --在插入之前触发器是不会进行插入 所以查询总数还是16-- select COUNT(*)from stu --总结：after不用在触发器里写插入语句，instead of 需要在触发器里写插入-- --学生表人数不能少于16（删除操作的话）------ 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 ---删除 成功-- delete from stu where sno='20200017' --再删除 失败 因为再删除就是15了 不满足触发器要求-- delete from stu where sno='20200016' --当新增学生成绩55-59 改成60分--- create trigger t3 on sc instead of insert as begin --insert插入之前先声明 declare @sno varchar(50) declare @cno int 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(@sno,@cno,@grade) end insert sc values('20200016','20202',57) select * from sc

十、函数

自定义函数：函数和之前讲过的存储过程很像，不同之处就是函数多了一个return。

create function--(---) returns -- as begin return end --1.计算某门课程的平均分-- create function fun1(@cno int) returns int as begin declare @avgscore int select @avgscore=AVG(grade) from sc where cno=@cno return @avgscore end --调用函数 select dbo.fun1('20201') --2.输入专业 返回该专业的学生学号和姓名 create function fun2(@mno int) returns @snosname table( sno varchar(50), sname varchar(30) ) as begin --declare @sno varchar(50) --declare @sname varchar(30) --sno sname mno 在stu表 --select @sno=sno,@sname=sname 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 @snocnograde table( sno varchar(50), cno int, grade decimal(5,2) ) as begin insert into @snocnograde 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 dbo.fun3(1)

十一、索引

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

目的：加快查询速度（目录），但是占用一定的存储空间，需要定期更新和维护。

以下情况 不适合创建索引：1.频繁更新的字段和经常增删改的表。2.表记录太少。3.某些数据包含大量重复值。例如性别字段 。

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

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

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

十二、游标

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

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

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

语法： 游标名 cursor for select …

步骤：

声明游标

打开游标

取数据（循环）

关闭游标 或者 deallocate

与视图相比：

1.本质不同：一个是作为指针操作（游标），一个是作为数据库对象（视图）

2.占用资源：多（游标）和少

3.工作方式：一个行处理（游标）。视图是一个整个表的查询结果，不能更新。

--声明游标 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 --1.对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 int declare @sno varchar(50) declare @grade decimal(5,2) 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 --2.查询所有学生的专业名和姓名-- select * from stu left outer join major on stu.mno=major.mno select * from stu ,major where stu.mno=major.mno --这样专业为null就缺失了 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(20) open my_cursor fetch next from my_cursor into @sname,@mname while @@FETCH_STATUS=0 begin select @sname as 'sname',@mname as 'mmname' fetch next from my_cursor into @sname,@mname end close my_cursor

​