一眨眼,一个学期的一半过去了,在此梳理下SQL知识。主要是SQL,数据库理论很少,只写写感觉在编写SQL语句时需要非常注意的理论知识
1、一些名词
1.1 域
一组具有相同数据类型的值的集合
1.2 关系
关系理解为一张二维表即可,不能表中套表
若关系中某一属性组的值能唯一地标识一个元组,而其子集不能,则称该属性组为候选码;候选码的诸属性为主属性;若一个关系中有多个候选码,则选定其中一个为主码
1.3 参照关系
设F是基本关系R的一个或一组属性,但不是R的码,K是基本关系S的主码。如果F和K相对应,则称F是R的外码,并称基本关系R为参照关系,基本关系S为被参照关系
1.4 空值null
使用is null或is not null判断某个属性值是否为空值,使用=null,相当于判断属性值是否为’null’字符串
2、关系的完整性
对关系的某种约束,即属性值需满足的条件
2.1 实体完整性
主属性不能取null
2.2 参照完整性
外码要么为null,要么来自被参照关系的属性值,保证了多个表间的约束
2.3 用户定义完整性
在创建表时定义,例如年龄只能[0,100]
3、模式
一个模式下通常包括了多个表、视图和索引等数据库对象。模式授权于用户,用户被授权某个模式,相当于被授权使用数据库中该模式下数据的权利
create schema "S-T" authorization wang --为用户 wang 授权架构S-T
drop table "S-T".tab1; -- 先删该模式下的表等数据对象
drop schema "S-T"; -- 再删除该模式
4、数据定义
4.1 SQL数据类型
数据类型 | 含义 |
---|---|
char(n) | 长度为n的定长字符串 |
varchar(n) | 最大长度为n的变长字符串 |
clob | 字符串大对象 |
blob | 二进制大对象 |
int | 长整数(4字节) |
smallint | 短整数(2字节) |
bigint | 大整数(8字节) |
numeric(p,d)、decimal(p,d)、dec(p,d) | 定点数,p位数字组成,小数点后有d位数字 |
float(n) | 可选精度的浮点数,至少为n个数字 |
boolean | 布尔逻辑 |
date | 日期,YYYY-MM-DD |
time | 时间,HH:MM:SS |
4.2 create创建表
使用create语句,同时定义约束,如设定主码、自定义约束、参照关系等
也可使用 constraint 完整性约束命名子句在创建表时定义完整性约束,对约束进行命名
create table Student(
Sno varchar(20) primary key, --主码
Sname varchar(20) unique, --值互不相同
Ssex varchar(2),
Sage smallint,
Sdept varchar(20)
); --建立学生表
create table Course(
Cno varchar(4) primary key, --主码
Cname varchar(40) not null, --不能取空值
Cpno varchar(4), --表示先行课程
Ccredit smallint,
foreign key(Cpno) references Course(Cno) --自身与自身参照,Cpno是外码(确保先行课程是存在的)
); --建立课程表
create table SC(
Sno varchar(20),
Cno varchar(4),
Grade smallint,
primary key(Sno,Cno), --主属性包含两个属性
foreign key(Sno) references Student(Sno), --Sno是外码,被参照表为Student表,数据类型需相同
foreign key(Cno) references Course(Cno) --Cno是外码,被参照表为Course表
/*确保选课的学号真实存在,所选的课真实存在*/
); --建立选课表
也可使用 constraint 子句在创建表时定义完整性约束,对约束进行命名,便于后续对约束的修改和删除
create table Student
(
Sno numeric(6)
constraint C1 check(Sno between 90000 and 99999),
Sname varchar(10)
constraint C2 not null,
Sage numeric(3)
constraint C3 check(Sage<30),
Ssex varchar(2)
constraint C4 check(Ssex in('男','女'))
); --约束命名为C1、C2、C3、C4
create table Teacher
(
Eno numeric(4) primary key,
Ename varchar(10),
Job varchar(8),
Salary numeric(7,2),
Deduct numeric(7,2),
Deptno numeric(2)
constraint teacher_refer foreign key(Deptno)
references Dept(Deptno), --为参照关系约束命名
constraint sum_salary check(Salary+Deduct>=3000) --工资必须大于3000
);
4.3 表的修改和删除
修改表结构使用alter子句,可修改表的列属性、添加约束
alter table Student add S_entrance date; --Student表添加新列
alter table Student alter column Sage int; --Student表改属性数据类型
alter table Course add unique(Cname); --Course表改Cname属性值互不相同
表删除分为cascade级联和restrict限制删除,但T-SQL都不支持。级联删除会删除该表参照关系涉及的所有表,限制删除则不会
4.4 索引
建立索引的目的是加速查询速度,索引的类型有两种,unique(一个索引值对应唯一的数据记录)和cluster(聚簇索引)
/* create 类型 index 索引名 on 表名(属性名) */
create unique index Stuson on Student(Sno); --建立唯一对应的索引
create unique index SCno on SC(Sno asc,Cno desc); --按照Sno升序,Sno相同时按Cno降序
alter index Scno on SC rename to Scsno; --修改索引名称
drop index Stuson on Student; --删除索引
5、数据查询
SQL语句中最复杂的部分
5.1 基本查询
5.1.1 基本格式
select [all|distinct] <目标表达式>
from <表名>
where <条件表达式>
group by 列名 [having <条件表达式>]
order by 列名 [asc|desc];
使用select语句
select * from Student; --*代表查询所有属性
select Sno,Sname from Student; --指定查询的属性
查询结果仍是一个表,可为该表的列起名字
select Sname 姓名,2021-Sage 出生年,'专业',upper(Sdept)
from Student; --专业名转大写
-- 列名 (as) 别名
5.1.2 去重
结果可使用distinct去重
select Sno as 学号 from SC; --默认为all
select distinct Sno as 学号 from SC; --该列去重
5.1.3 where
使用where子句进一步筛选元组
select Sname,Sage,Sdept
from Student
where Sdept='CS';
--查询CS专业的学生姓名和年龄
--字符串需要单引号,内容不区分大小写,'CS'和'cs'效果相同
5.1.4 搭配比较运算符
比较运算符有=、>、<、>=、<=、!=、<>、!>、!<,同时可使用not搭配上述运算符,相当于取反
--查询20岁及以下
select Sname,Sage
from Student
where Sage not > 20;
select Sname,Sage
from Student
where Sage !> 20; --等价
5.1.5 搭配逻辑运算符
SQL语言中逻辑运算符为 and、or、not,相当于C语言中的 &&、||、!
使用 between ··· and ··· 可查询属性在指定范围内的元组,包括两端,可使用not取反
select Sname,Sage
from Student
where Sage between 19 and 21; --19~21岁之间,包括19和21
-- Sage>=19 and Sage<=21
select Sname,Sage
from Student
where Sage not between 19 and 21; --不在19~21岁之间
-- Sage<19 and Sage>21
5.1.6 搭配in
使用 in 可查询属性在指定集合内的元组,可使用not取反
select Sname,Sdept
from Student
where Sdept in('CS','IS'); --专业是CS、IS
-- Sdept='CS' or Sdept='IS'
select Sname,Sdept
from Student
where Sdept not in('CS','IS'); --专业不是CS、IS
-- Sdept!='CS' and Sdept!='IS'
5.1.7 like模糊查询
like+匹配串,可使用not取反,实现模糊查询,即字符串匹配查询,使用通配符 %或_ 代替字符
select Sname,Sdept
from Student
where Sname like '王%'; --王姓学生
select Sname,Sdept
from Student
where Sname not like '王%'; --非王姓学生
select Sname,Sdept
from Student
where Sname like '欧阳_'; --欧阳姓三字学生
select Sname,Sdept
from Student
where Sname like '欧阳__'; --欧阳姓四字学生
% 和 _ 作为通配符,那么当字符串中需要真正表示百分号和下划线时,需要使用关键字escape定义转码字符
select Sname,Sdept
from Student
where Sname like '王/%' escape'/'; --转义%为普通的百分号
5.1.8 order by排序
order by用于对查询结果进行排序,需指定排序的属性名,asc升序、desc降序,默认为升序asc
select Sname,Sage,Sno
from Student
where Sdept in('cs','is')
order by Sage; --按年龄升序排序
select Sname,Sage,Sno
from Student
where Sdept in('cs','is')
order by Sage,Sno desc; --年龄相同时按学号(字符串)降序排序
5.1.9 聚集函数
聚集函数用于数据的统计功能,只可用于select语句和group by的having语句
- count(*):元组的个数
- count(<列名>):一列中值的个数‘
- sum(<列名>):一列值得总和(该列必须为数值型)
- avg(<列名>):一列值的平均值(该列必须为数值型)
- max(<列名>):一列值的最大值
- min(<列名>):一列值得最小值
这些函数在使用时,可选择统计该列distinct去重后或all所有,默认为all
select count(*) as 学生总数 from Student; --学生总数
select count(Sdept) from Student; --有专业的学生总数
select count(distinct Sno) from SC; --选修了课得学生总数
select avg(Sage) as 平均年龄 from Student; --平均年龄
select max(Sage) as 最大年龄 from Student; --最大年龄
select min(Sage) as 最小年龄 from Student; --最小年龄
这些函数在使用时,可选择统计该列distinct去重后或all所有,默认为all
使用count(<列名>)查询数量时,会依据元组的该属性是否存在,因此会忽略空值,而使用count(*)查询时不会忽略空值
5.1.10 group by分组
group by语句可将查询结果按某一列或多列分组,同时可使用having语句搭配聚集函数对每组的数据进一步筛选。having语句只能使用在group by语句后,用于进一步筛选分组后的数据
select Sdept,count(*) as 人数
from Student
group by Sdept; --各个专业的人数
select Sdept,count(*) as 人数
from Student
group by Sdept having count(*)>1
order by 人数; --查询专业人数大于1的专业,按升序排序
5.2 连接查询
5.2.1 自身连接与等值连接
关系型数据库中,两个表的笛卡尔积包括了两表连接的所有形成的元组
[表名1] <列名1> <比较运算符> [表名2] <列名2>
比较运算符使用 = 时为等值连接,使用其他运算符如 >、<、>=、<= 时非等值连接
SQL语句中,当涉及到多个表的列名时,需要指定该列在哪个表中(表名.列名)
--等值连接
select Student.Sno,Student.Sname,SC.Cno,Course.Cname
from Student,SC,Course
where Student.Sno=SC.Sno and SC.Cno=Course.Cno; --查询选课学生所选课的信息
--非等值连接,多表连接
select Student.Sno,Student.Sname,Student.Sage,SC.Cno,Course.Cname
from Student,SC,Course
where Student.Sno=SC.Sno and SC.Cno=Course.Cno and Student.Sage>20; --查询20岁以上选课学生所选课的信息
自身连接即一个表与其自己进行连接,如查询某一个课程先修课的先修课
使用自身连接相当于把一个表视为两份,需要为每份在from语句后起别名
select First_table.Cno,First_table.Cpno,second_table.Cpno as 先修的先修
from Course as First_table,Course as second_table
where First_table.Cpno=second_table.Cno; --每份表起别名
5.2.3 外连接
[表1] left outer join [表2] on(连接条件)
左外连接保留左侧表(表1)的悬浮元组
--两个表左外连接
select Student.Sno,Student.Sname,SC.Cno
from Student left join SC on(Student.Sno=SC.Sno);
--三个表左外连接
select Student.Sno,Student.Sname,SC.Cno,Course.Cname
from Student left join SC on(Student.Sno=SC.Sno)
left join Course on(SC.Cno=Course.Cno);
[表1] right outer join [表2] on(连接条件)
左外连接保留右侧表(表2)的悬浮元组
select Course.Cno,Course.Cname,SC.Sno
from SC right join Course on(SC.Cno=Course.Cno); --两个表右外连接
select Course.Cno,Course.Cname,SC.Sno,Student.Sname
from SC right join Course on(SC.Cno=Course.Cno)
left join Student on(SC.Sno=Student.Sno); --先右外连接后左外连接
[表1] full outer join [表2] on(连接条件)
全外连接保留表1和表2的所有悬浮元组,缺失的属性值赋值null
select Student.Sno,Student.Sname,SC.Cno,Course.Cname
from Student left join SC on(Student.Sno=SC.Sno)
full join Course on(SC.Cno=Course.Cno); --左外连接,全外连接
select Student.Sno,Student.Sname,SC.Cno,Course.Cname
from Student left join SC on(Student.Sno=SC.Sno)
left join Course on(SC.Cno=Course.Cno); --左外连接,左外连接
select Student.Sno,Student.Sname,SC.Cno,Course.Cname
from Student left join SC on(Student.Sno=SC.Sno)
right join Course on(SC.Cno=Course.Cno); --左外连接,右外连接
5.3 嵌套查询
5.3.1 带in谓词的子查询
in谓词后接集合,表示查询某属性值在某个集合中的元组,可加not
select Sname,Sdept
from Student
where Sno in
(select Sno
from Student
where Sdept='cs'); --查询cs系的学生,不相关子查询
select Sname,Sdept
from Student
where Sdept='cs'; --与上述等价
如上为不相关子查询,子查询的条件不依赖于父查询,称为不相关子查询。从下向上分析。如下为相关子查询,从上向下分析分析
select Sname,Cno
from Student,SC as x
where Student.Sno=x.Sno and x.Grade>=(
select avg(Grade)
from SC as y
where y.Sno=x.Sno); --查询选修课成绩高于平均成绩的学生信息,相关子查询
5.3.2 any和all谓词
对查询结果进行筛选,any指查询结果中的某个值,all指查询结果中的所有值。可用其他形式代替
= | <>或!= | < | <= | > | >= | |
---|---|---|---|---|---|---|
any | in | – | <max | <=max | >min | >=min |
all | – | not in | <min | <=min | >max | >=max |
select max(Sage) as 最大年龄
from Student
where Sdept='cs';
select Sname,Sage
from Student
where Sdept='cs' and Sage<any
(select Sage
from Student
where Sdept='cs'); --查询cs系中比任意一个学生年龄小的学生
5.3.3 exists谓词
exists代表存在量词∃,带有exists的子查询不返回数据,只产生逻辑真值true或false,可使用not exists
select Sname
from Student
where exists
(select *
from SC
where Sno=Student.Sno and Cno='1'); --查询选修了1号课的学生
针对外层查询的每一个元组,判断内层查询结果,true则输出,false则不输出
一些带exists或not exists谓词的子查询不能被其他形式的子查询等价替代,但所有带in谓词、比较运算符、any和all谓词的子查询都能用带exists谓词的子查询等价替代
select Sname,Sdept
from Student
where Sdept in
(select Sdept
from Student
where Sname='王伯成'); --与王伯成同一个系的学生
select Sname,Sdept
from Student as s1
where exists
(select *
from Student as s2
where s1.Sdept=s2.Sdept and s2.Sname='王伯成'); --与上述等价
select Sname
from Student
where not exists
(select *
from Course
where not exists
(select *
from SC
where Sno=Student.Sno and Cno=Course.Cno)
); --查询选修了所有课的学生
SQL中没有全称量词,(∀x)P = ┐(∃x(┐)P)
选修了所有课的学生,即没有一门课是该学生不选修的。对每一个Studnet表中的元组,判断他是否没有选修Course表中存在的所有课
select distinct Sno
from SC as scx
where not exists
(select *
from SC as scy
where scy.Sno='2019102' and not exists
(select *
from SC as scz
where scx.Sno=scz.Sno and scz.Cno=scy.Cno)
); --查询至少选修了2019102学号学生选修的所有课的学生
p:学生2019102选修了课程y
q:学生x选修了课程y
(∀y)p → q = ┐(∃y(p∧┐q))
至少选修了2019102学号学生选修的所有课的学生,即不存在这样一门课,2019102学生选了而该学生没有选
5.4 集合查询
5.4.1 交集
- union:并集
--并集,cs系的学生及(或)年龄不大于20岁的学生
select Sname,Sage,Sdept
from Student
where Sdept='cs'
union
select Sname,Sage,Sdept
from Student
where Sage<=20; --自动去除重复元组
select Sname,Sage,Sdept
from Student
where Sdept='cs'
union all
select Sname,Sage,Sdept
from Student
where Sage<=20; --保留所有元组使用union all
5.4.2 并集
- intersect:交集
select Sname,Sage,Sdept
from Student
where Sdept='cs'
intersect
select Sname,Sage,Sdept
from Student
where Sage<=20; --cs系且年龄不大于20的学生
5.4.3 差集
- except:差集
select Sname,Sage,Sdept
from Student
where Sdept='cs'
except
select Sname,Sage,Sdept
from Student
where Sage>20; --查询cs系学生与年龄大于20岁的学生的差集
--先查询出cs系学生,再将其中年龄大于20的剔除
5.5 基于派生表的查询
派生表使用在 from 语句后,临时使用,查询结束后删除,不会像基本表那样占用内存
派生表必须指定别名,没有使用聚集函数时,列名可省略
select Student.Sname,Course.Cname,SC.Grade,Avg_sc.avg_grade
from Student,SC,Course,
(select Sno,avg(Grade)
from SC
group by Sno
) as Avg_sc(avg_sno,avg_grade) --临时表
where SC.Sno=Avg_sc.avg_sno and SC.Grade>=Avg_sc.avg_grade
and Student.Sno=SC.Sno and SC.Cno=Course.Cno; --查询学生分数大于其平均分的学科
6、数据更新
即表中元组的增、删、改,均可使用子查询作为语句中条件
6.1 插入数据
向表中插入一个或多个元组,可指定插入属性的顺序,插入数据不能违反关系的完整性
insert into Student values('20191102041','王伯成','男','20','计算机科学与技术'); --按表中属性本身的顺序输入
insert into Student(Sname,Sno,Ssex,Sage)
values('张三','2001','男','21'); --指定输入属性的顺序,未指定的置为NULL(若不违反用户定义的完整性)
/*insert into Student(Sname,Sno,Ssex,Sage)
values('李五','2001','男','20'); */ --主码对应元组唯一,两个2001
insert into Student values
('2000','王五','男',22,'信息安全'),
('2002','马六','男',20,'软件工程'),
('2003','陈一','女',21,'网络工程'); --可一次插入多条
6.2 修改数据
修改数据指修改某个已存在元组的某些属性值,可使用子查询作为条件
update Student
set Sname='王伯成',Sdept='计算机科学与技术'
where Sno='20191102041' and Ssex='男';
update SC
set Grade=null
where Sno in
(select Sno
from Student
where Sdept='cs'); --cs系成绩置空
6.3 删除数据
删除数据也可使用子查询作为条件
delete
from Student
where Sno='20191102041';
delete
from SC
where Sno in
(select Sno
from Student
where Sdept is null); --删除无专业学生的选课记录
7、视图
视图是一个虚表,只存放视图的定义,不存放具体的数据
若一个视图是从一个基本表中导出的,并且只是去掉了基本表的某些行和某些列,但保留了主码,则为行列子集视图
7.1 定义视图
使用create语句定义,如果在结尾添加了with check option,则在修改该视图时数据库管理系统会自动加上建立视图时的where条件,即保证该视图中仅有cs系学生
/*create view <视图名> [<列名>,<列名>...]
as <子查询>
[with check option]*/
create view cs_student
as
select Sno,Sname,Sage
from Student
where Sdept='cs'; --建立行列子集视图,保存cs系的学生信息
create view cs_student
as
select Sno,Sname,Sage,Sdept
from Student
where Sdept='cs'
with check option; --建立视图
insert into cs_student
values('1998','王大狗',21,'cs'); --可成功执行
insert into cs_student
values('199889','王小狗',21,'is'); --无法执行,不是cs系
7.2 查询视图
查询视图和查询基本表相同,因为视图存储的是视图的定义语句,在查询视图时会执行视图消解:把视图定义语句和视图查询语句融合为对表的查询语句
select Sname,Sage
from cs_student
where Sage<=20;
select Sname,Sage
from Student
where Sdept='cs' and Sage<=20; --与上述等价
当建立视图使用聚集函数时,查询视图在视图消解转换时会发生错误
/*select Sno,avg(Grade)
from SC
group by Sno; --定义S_G视图时的子查询 */
select *
from S_G
where Gavg>=90; --查询选修课平均分大于90的学生
--试图消解后:
select Sno,avg(Grade)
from SC
where avg(Grade)>=90
gropu by Sno;
7.3 更新视图
对视图的更新最终将转换为对基本表的更新,对视图更新后,其依赖的基本表也将做出变化
对视图进行更新时,更新的列需要唯一的对应其基本表的某一列,即若视图的某列是聚集函数或运算表达式得出的,则无法更新
update cs_student
set Sname='王大宝'
where Sno='20191102041';
update Student
set Sname='王大宝'
where Sno='20191102041'; --与上述等价
update avg_sdept
set avg_age=21
where Sdept='cs'; --无法修改,基本表没有avg_age这一列,这是聚集函数分组得到的列
8、数据库安全性
8.1 授权:授权与收回
权限包括select、update、insert、delete、references、all privileges
grant select
on Student
to wbc; --赋予wbc用户查询Student表的权限
--on后面不需显示指定数据类型,wbc用户必须存在
-- T-SQL不支持同时授予用户多个对象的权限,且不需要privileges
grant all privileges
on Student,Course
to wbc;
grant select
on SC
to public; --查询权限授予所有用户
grant update(Sno),select
on Student
to root_wbc; --授予该用户修改学号属性和查询的权限
授予权限语句后添加 with grant option ,该用户可将权限转授
grant insert
on Student
to wbc
with grant option; --该用户可将插入权限授予给别的用户
收回授权时,可使用cascade
revoke select
on Student
from wbc; --可执行
/*revoke insert
on Student
from wbc; --无法成功执行,因为该权限可转授,需显式指定cascade,将转授的一并收回*/
revoke insert
on Student
from wbc cascade; --可执行
8.2 数据库角色
与直接授予用户权限一样,角色的权限是用来给用户的
grant select,insert
on SC
to a_student; --授予该角色权限
grant a_student
to wbc; --角色的权限是用来给用户的
revoke insert
on SC
from a_student; --回收角色的某个权限
revoke all
on SC
from a_student; --回收角色的所有权限
--如果授予角色权限时使用了with grant option,则需cascade
9、数据库完整性与存储过程
触发器和存储过程的标准SQL语法和T-SQL语法差距很大
9.1 断言
声明断言来指定更具一般性的约束,可以涉及多个表或聚集操作的比较复杂的完整性约束
/*create assertion <断言名> <check 语句>*/
create assertion asse_sc_db_num
check
(60 <=
(select count(*)
from Course,SC
where SC.Cno=Course.Cno and Course.Cname='数据库')
); --更新表时,检查数据库班级人数是否小于等于60
9.2 触发器
触发器类似于窗体事件,在发送信号后执行一个函数,SQL中即在对表的增、删、改操作时激活相对应的触发器
/*create trigger <触发器名>
{before|after} <触发事件> on <表名> --指明所在表,激活的时刻
referencing new|old row as <变量> --指出引用的变量
for each{row|statement} --触发器类型,行级和语句级
[when<触发条件>] <触发动作>*/
--修改成绩大于百分10时在其他表中记录
create trigger SC_T
after update of Grade on SC
referencing
oldrow as oldTuple --旧行
newrow as newTuple --新行
for each row
when(newTuple.Grade>=1.1*oldTuple.Grade)
insert into SC_U(Sno,Cno,oldGrade,newGrade)
values(oldTuple.Sno,oldTuple.Cno,oldTuple.Grade,newTuple.Grade);
--为教师表Teacher教授的工资不能低于4000元
create trigger insert_or_update_salary
before insert or update on Teacher
referencing newrow as newTuple
for each row
begin
if(newTuple.Job='教授' and newTuple.Salary<4000)
then newTuple.Salary:=4000; --赋值使用 :=
end if;
end;
删除触发器如下
drop trigger insert_or_update_salary on Teacher; --需指定表
9.2 存储过程
类似于函数,传入参数,执行相应SQL语句
create procedure transfer(inAccount int,outAccount int,amount int) --参数
as
declare totalDepositOut int,
totalDepositIn int,
inAccountnum int; --定义自变量
begin
select total_salary into totalDepositOut from Account where Account_id=outAccount;
if totalDepositOut is null then
rollback;
return;
end if;
if totalDepositOut<amount then
rollback;
return;
end if;
select total_salary into totalDepositIn from Account where Account_id=inAccount;
if totalDepositIn is null then
rollback;
return;
end if;
update Account set total_salary=total_salary-amount where Account_id=outAccount;
update Account set total_salary=total_salary+amount where Account_id=inAccount;
end;
call procedure transfer(1,2,100); --执行
alter procedure transfer_account rename to transfer_salary; --更改存储过程名
drop procedure transfer_salary; --删除存储过程
学期的一半过去了,SQL语法也基本学完了,越来越感觉标准SQL和T-SQL是两个毫不相干的语言(ˉ▽ˉ;)…