SQL语句整理

参考视频：单表查询_哔哩哔哩_bilibilihttps://www.bilibili.com/video/BV1ct411G78W/?p=5&vd_source=8338c6f8572bfcc9b30838be9bc268f7

自己整理的代码（0积分可直接下载）：MYSQL常用语句，包括简单增删改、单表查询、多表查询、函数等，适合快速入门和复习资源-CSDN文库https://download.csdn.net/download/qq_41511474/87654163

 建表：

--删除数据库
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) --最大有效数字5位，精度小数点两位
)
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)
)

数据输入

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 major
select * from stu
delete from major where mno=1 --错误，major中的mno是stu的外键。
--应该先把别的表中的外键置为null
--修改表中数据
update stu set mno=null where mno=1
delete from major where mno=1

单表查询

select * from stu

--在stu表中查询特定列
select sno,sname from stu 

--查询显示列birth
select sname,2020-age as birth from stu

--distinct不重复数据
select distinct sno from sc

--where条件语句，like后面的字符串没有通配符%时，=可以等价为like
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

//mno=1 or mno=2 or mno=4 in来代替or
select * from stu where mno in (1,2,4)

--%表示占0-n位字节
select * from stu where sname like '彭%'

--_是占据一个字节，忽略不计
select * from stu where sname like '_小%'

select * from stu where sname like '%小%'

--表示空用is
select * from sc where grade is null

聚集函数

--order by 排序
select * from sc order by grade      --从小到大
select * from sc order by grade desc --从大到小

--count(*)一个表统计有多少行
--count(sno)一个表sno值的个数（不为空）
select * from stu
select COUNT(*) from stu

select * from sc
select COUNT(distinct sno) from sc --distinct不重复
select COUNT(grade) from sc

--avg平均值，max最大值，min最小值
select AVG(grade) from sc where cno='20201'

--select xx from table group by xx根据XX进行区分
select * from sc
select cno, COUNT(sno) as num from sc group by cno

--where后面不能出现聚集函数：conut、sum、avg、man、min
--order by、group by后面不能用where
--group by ··· having 是对表数据的一个分组筛选
--order by用来对数据进行排序，放在where、group by ··· having整理数据子句的后面；
select * from stu
select sno,sname from stu where age>=18
select * from stu where age=18 order by sno 

--查询平均成绩大于大于90的学生学号和平均成绩
--查询学生学号和平均成绩，一个学生可能有多个成绩需要group by sno
--错误 select sno,avg(grade) from sc group by sno where avg(grade) >= 90 
select sno,avg(grade) from sc group by sno having avg(grade) >= 90 

多表查询（重点难点内容）

--1、等值查询，取出两个表中有相同字段
--部分学生没有选修，如果需要显示全部字段使用左外连接
select * from stu
select * from sc
select stu.*,sc.* from stu,sc where stu.sno=sc.sno
select * 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 * from stu
select * from sc
select * from cou
select * from stu,sc,cou where stu.sno=sc.sno and sc.cno=cou.cno
select stu.*,sc.*,ctime from stu,sc,cou where stu.sno=sc.sno and sc.cno=cou.cno


--2、左外连接 left outer join ...  on
--保留左表内数据，进行两表连接
--查询所有学生的信息和选课信息，但是没有选秀的学生也要显示出来
select stu.*,sc.* from stu,sc where stu.sno=sc.sno --显示相同片段
select stu.*,sc.* from stu left outer join sc on stu.sno=sc.sno --全部显示

--查询每个专业的人数，假设每个专业都有人
select * from major
select * from stu
select major.* from major,stu where major.mno=stu.mno
select mno,COUNT(sno) as num from stu group by mno

--查询每个专业的人数，但是有的专业可能没有人
--需要保留major表，左外连接
insert into major values(5,'aa')
select major.*,stu.* from major,stu where major.mno=stu.mno order by major.mno --对比，没有人的专业不显示
select major.*,stu.* from major left outer join stu on major.mno = stu.mno
select major.mno,COUNT(sno) from major left outer join stu on major.mno = stu.mno group by major.mno


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

--建议用in而不是=。in 返回多个结果，= 返回一个结果
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 * from sc
select * from cou
select sc.sno from sc,cou where sc.cno=cou.cno and cname='C语言'
select sno from sc where cno in (select cno from cou where cname='C语言')
select sno from sc where 'C语言' in (select cname from cou where sc.cno=cou.cno)

--查询每个学生超过他平均分的课程号 第二种方法用派生表实现
select * from sc
select sno,cno from sc x where grade>
(select AVG(grade) from sc y group by sno having x.sno=y.sno)
--派生表
select * from sc
select sno,AVG(grade) from sc group by sno
select sno,cno 
from sc,(select sno,AVG(grade) from sc group by sno) as arg_sc(agv_sno,agv_grade)
where sc.sno=arg_sc.agv_sno and grade>agv_grade

--带有exists()每次取一个sno连接，返回true/false
--查询选修'20201'学生的姓名
select * from stu
select * from sc
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
)

--3、集合查询：交集intersect(and) 除去except 并集union(or in)
--查询年龄是18且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 * from sc
select distinct sno from sc where cno in('20201','20203') --行为单位所以需要加distinct
select sno from sc where cno='20201' union select sno from sc where cno='20203'--集合为单位

select distinct 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_stu
as select sno,sname,age from stu
--查看视图
select * from v_stu
--删除视图
drop view v_stu

--查询学生的信息(sno,sname,mname)
select * from stu
select * from major
select sno,sname,mname from stu,major--错误，笛卡儿积的结果

create view v_stu1
as select sno,sname,mname from stu,major where stu.mno=major.mno --相同字段，没有考虑学生没有专业或者专业没有学生的情况
select * from v_stu1

create view v_stu2
as select stu.sno,stu.sname,major.mname 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
select * from v_stu3 where avg_grade<89

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

存储过程

--返回学校2020005学生的成绩情况，存储名p1
--创建存储过程（类比函数）
create proc p1
as 
begin
  select * from sc where sno='2020005'
end
--调用存储过程
exec p1
--删除存储过程
drop proc p1

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

触发器

--触发器结构
--create trigger ti on stu
--after instead of
--update insert delete
--as
--begin
--...
--end

--学生人数不能大于17
select * from stu

--创建触发器
create trigger ti on stu after insert --插入之后触发
as 
begin
  if(select COUNT(*) from stu)>17
  begin
    print 'error'
    rollback tran --回滚
  end
  else
  begin
    print 'right'
  end
end

--修改触发器
alter trigger ti 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'
    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

--测试：插入数据
insert into stu(sno,sname) values('20200017','kk')
insert into stu(sno,sname) values('20200018','kb')
delete from stu where sno='20200017'

--删除触发器
drop trigger ti

----学生人数不能小于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

select * from stu
delete from stu where sno='20200017'

--当新增学生成绩55-59改成60
select * from sc
create trigger t3 on sc instead of insert
as
begin
  select * from inserted
  select * from deleted
  declare @cno varchar(13)
  declare @sno varchar(13)
  declare @grade decimal(5,2)
  select @cno=cno from inserted
  select @sno=sno 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 into sc values('20202','20200011',57)
insert into sc values('20202','20200017',53)

函数

--计算某门课程的平均分
--input cno
--output avgscore

create function fun1(@cno varchar(13))
returns int
as
begin
  declare @avgscore int
  select @avgscore=AVG(grade) from sc where cno=@cno
  return @avgscore
end

select AVG(grade) from sc where cno='20201'
select dbo.fun1('20201')

--输入专业
--返回这个专业学生学号和姓名output table
select sno,sname from stu where mno=3 --输出专业为3的学生学号姓名

create function fun1(@mno int)
returns @snoSname table(
  sno varchar(13),
  sname varchar(13)
)
as
begin
  --declare @sno varchar(13)
  --declare @sname varchar(30)
  --select @sno=sno,@sname=sname from stu where mno=@mno错误，只返回一条信息
  --insert into @snoSname values(@sno,@sname)
  insert into @snoSname select sno,sname from stu where mno=@mno
  return 
end

select * from dbo.fun1(2)

--输入专业号
--return 这个专业所有学生的每个课程对应成绩的一个表
select * from stu
select * from sc
select cno,sc.sno,grade from sc,stu where sc.sno=stu.sno and mno=1
alter function fun2(@mno int)
returns @snoSname table(
  sno varchar(13),
  cno varchar(13),
  grade decimal(5,2)
)
as
begin
  declare @sno varchar(13)
  declare @cno varchar(13)
  declare @grade int
  insert into @snoSname select sc.sno,cno,grade from sc,stu where sc.sno=stu.sno and mno=@mno
  return
end

select * from fun2(1)

索引

定义：对数据库一列或者多列进行排序的结构（B+树）
作用：加快查询，类比目录
不建议创建索引的情况：
1.频繁更新的字段，需要频繁进行B+树修改操作
2.表记录太少，索引占用一定内存
3.数据量大量重复，会变成全表查询

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

游标：用户缓冲区
声明游标
打开游标
取数据
关闭游标 or deallocate

游标和视图的不同：游标是指针操作，视图是数据库对象

--声明游标
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 --0数据提取成功，-1没取到数据/数据取完
begin
  select @mname as 'mname'
  fetch next from my_cursor into @mname
end
--关闭游标
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 stu
select * from major
select stu.sname,major.mname from stu left outer join major on stu.mno=major.mno

declare my_cursor cursor for select stu.sname,major.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
deallocate my_cursor