1、数据库的特点：永久存储、有组织、可共享。
第二章 关系数据库 第三章 关系数据库标准语言SQL 1、索引（1）概述
在数据之外，数据库系统还维护着满足特定查找算法的数据结构，包括B+树或者Hash表。
**由于存储引擎表示的是数据在磁盘上面的不同的组织形式，所以索引底层采用哪种数据结构是跟数据库的存储引擎相关的。**如果是MyIsam或者是InnoDB存储引擎，那么对应的底层的数据结构为B+树，如果是Memory存储引擎，那么对应的底层的数据结构为Hash表。
**采用B+树的最根本的原因是由于二叉树的树太高，树太高则直接影响到磁盘IO的次数，影响数据查询的效率，采用B+树的数据结构，可以在某个数据节点里面尽可能多的存储数据，使树的高度尽量的变低，提高效率。**日常开发过程中，遇到的比较多的可能就是聚簇索引和联合索引，里面又涉及到了覆盖索引，最左匹配，回表，索引下推等各方面的知识点，在编写SQL语句的时候，我们就可以利用这些点来进行优化，提高数据的查询效率。
**一般来说索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储在磁盘上。**索引是数据库中用来提高性能的最常用的工具。
（2）底层数据结构
- 从存储结构上来划分：B-Tree，B+Tree，Hash索引
- 从应用层次来分：普通索引，唯一索引，复合索引
- 从数据的物理顺序与键值的逻辑（索引）顺序关系：聚集索引，非聚集索引
--学生表 create table Student (Sno char(9) primary key, Sname char(20) unique, Ssex char(2), Sage smallint, Sdept char(20) ); --课程表 create table course (Cno char(4) primary key, Cname char(40) not null, Cpno char(4), Ccredit smallint, foreign key (Cpno) references Course(Cno) ) --选课表 create table SC (Sno char(9), Cno char(4), Grade smallint, primary key (Sno,Cno), foreign key (Sno) references Student(Sno), foreign key (Cno) references Course(Cno) )3、建立索引
--学生表按学号升序建立唯一索引 create unique index StuSno on Student(Sno); --课程表按课程号降序建立唯一索引 create unique index Coucno on Course(Cno desc); --选课表学号升序，课程号降序唯一索引 create unique index SCno on SC(Sno asc,Cno desc)4、数据查询 （1）单表查询 4.1.1 where子句
--列别名
select Sname,2022-Sage BirthDay
from student
select Sname,2022-Sage as BirthDay
from student
--distinct去除重复的行
select distinct Sno
from SC
--where子句
--①比较大小
select Sname,Sage
from student
where Sage<20
--②确定范围 between ... and ...
select Sname,Sage
from student
where Sage between 18 and 20
--③确定集合 in
select Sname
from student
where Sdept in ('CS','MA')
--④字符匹配 like
select *
from student
where Sno like '00_'
--⑤涉及空值的查询 is NULL/is not NULL
select *
from sc
where Grade is NULL
--⑥多重条件查询 and / or ;and的优先级高于or
select *
from student
where Sdept='CS' and Ssex='女'
-
字符匹配 like 关键字
- %（百分号）:代表任意长度（长度可为0）字符串。例如：'a%b'代表a开头b结尾的字符串；
- -（下横线）：代表任意单个字符；
- []：代表指定范围的单个字符。例如：[a]代表a，[a-f]代表a-f范围内的某一字符；（测试失败）
- [^]：代表不在指定范围内的单个字符。[^a]代表不是a，[^a-f]代表不在a-f范围内的某一字符。（测试失败）
-
如果要匹配字符串本身含有上述关键字，要使用escape短语进行关键字转义：
Select * from student where Sno like '00%_' escape '%' --这里的%不是占位通配符,需要匹配00%开头的任意4个字符
Select * from SC where Cno='1' order by Grade desc,Sno asc --asc不写也可，默认升序4.1.3 聚集函数
- count(*)：统计元组个数；
- count(distinct 列名)：统计某列不同值的个数；
- sum(列名)：某列的总和；
- avg(列名)：某列的均值；
- max(列名)：某列的最大值；
- min(列名)：某列的最小值。
select count(distinct Sno) from SC select avg(all Grade) from SC where Cno='1'
- 注：
- 默认列名前是all，可指定distinct排除重复值；
- 当聚集函数遇到空值时，除count(*)外，都跳过空值处理非空值；
- where不能使用聚集函数，聚集函数只能用于select子句和group by子句的having子句。
- 分组后聚集函数作用于每一个组。
select Sno,avg(Grade) as avgGrade from SC group by Sno having avgGrade>=60 --having avg(Grade)>=60也一样（2）连接查询
--等值连接和非等值连接 select student.Sno,Sname from student,SC where student.Sno=SC.Sno and Cno='2' and grade>=90 --自身连接 select x.Cno,x.Cname,y.Cno as Cpno,y.Cname as CpnoName from course x,course y where x.Cpno=y.Cno4.2.1 外连接
-
左外连接：列出左边关系中所有元组；
select student.Sno,Sname,Cno,Grade from student left outer join sc on student.sno=sc.sno
-
右外连接：列出右边关系中所有元组。
select student.Sno,Sname,Cno,Grade from student right outer join sc on student.sno=sc.sno
注：
- 子查询不能使用order by子句，order by子句只能对最终查询结果排序；
①子查询的查询条件不依赖于父查询，称为不相关子查询；
select * from student where Sdept in ( select Sdept from student where Sname='刘晨' )
- 有些嵌套查询可以使用连接查询替代，有些不能替代，在实际应用中，优先选择连接查询；
②如果子查询的查询条件依赖于父查询，这类子查询称为相关子查询；
--找出每个学生超过自己平均成绩的课程记录 select Sno,Cno,Grade from SC x where Grade> (select avg(Grade) from SC y where y.Sno=x.Sno)
③当用户能确切的知道内层查询返回的是单个值时，可以用>、<、=、>=、<=、!=、<>等比较运算符。
4.3.1 any和all谓词④带有any (some)或all谓词的子查询：子查询返回单值时可以用比较运算符，但返回多值时要用any(有的系统用some)或all谓词修饰符；
例如：
- <=any：小于等于子查询结果中的某个值;
- >all：大于子查询结果中的所有值；
- !=any：不等于子查询结果中的某个值；
- <>all：不等于子查询结果中的任何一个值。
--查询非计算机系中比计算机系中任意一个学生年龄大的学生 select Sname,Sage from student x where x.Sdept!='CS' and x.Sage>any (select distinct Sage from student y where y.Sdept='CS')
注：通常使用聚集函数实现子查询比使用any或all效率高。
4.3.2 exists谓词⑤带有exists谓词的子查询：不返回任何数据，只产生逻辑真值"true"或逻辑假值"false";
--查询所有选修了1号课程的学生姓名 select Sname from student where exists (select * from sc where Sno=student.Sno and sc.Cno='1')
- 由exists引出的子查询，其目标列表达式通常都用*，因为带exists的子查询只返回真值或假值，给出列名无实际意义。
⑥not exists：若内层查询结果为空，则外层的where子句返回真值，否则返回假值。
--查询选修了所有课程的学生名 select Sname from student where not exists (select * from course where not exists (select * from sc where Sno=student.Sno and Cno=course.Cno ) ) --查询至少选修了‘001’选修的全部课程的学生号码 select Sno from student where not exists (select * from sc x where x.Sno='001' and not exists (select* from sc y where y.Sno=student.Sno and y.Cno=x.Cno) )（4）集合查询
注：参加集合操作的各查询结果的列数必须相同，对应项的数据类型也必须相同。
4.4.1 并操作 unionselect * from student where Sdept='CS' union --使用 union 会自动去掉重复元组，如果保存重复元祖使用 union all select * from student where Sage<=194.4.2 交操作 intersect 4.4.3 差操作 except （5）基于派生表的子查询
子查询不仅可以出现在where子句中，还可以出现在from子句中，这时子查询生成临时派生表成为主查询的查询对象。
--找出每个学生超过它自己选修课程平均成绩的课程号 select sc.Sno,Cno from SC,(select Sno,avg(Grade)avgGrade from SC group by Sno) x where sc.Sno=x.Sno and sc.grade> x.avgGrade
注：必须为派生表指定别名。
（6）select语句一般格式select from where group by having order by5、数据更新 （1）插入数据 5.1.1 插入元祖
- into子句中没有出现的属性列，新元组在这些列上将取空值；
- 如果into子句中没有指明任何属性列名，则新插入的元组必须在每个属性列上均有值；
insert
into student
values ('006','张三','女',21,'MA')
insert
into student(Sno,Sname,Ssex,Sdept)
values ('007','张三','男','CS') -- Sage为NULL
insert
into student
values ('008','李四','男',NULL,NULL) --不给出列名时，不赋值的属性要明确给出NULL
5.1.2 插入子查询结果
语法：select 子句目标列必须与into子句匹配；
insert into 表名(columns) 子查询
insert into dept_age select Sdept,avg(Sage) from student group by Sdept（2）修改数据
语法：如果省略where子句，表示要修改所有记录；
update 表名 set 列名=表达式...... where 条件
update student set Sage=24 where Sname='李四'（3）删除数据
语法：如果省略where子句表示删除全部元组，但是表的定义仍在字典中。
delete from 表名 where 条件
delete from dept_age where Sdept='CS'6、空值的处理
- 判断一个值是否为空值使用 is NULL或 is not NULL；
- 空值与另一个值（包括另一个空值）的算术运算的结果为空值；
- 空值与另一个值（包括另一个空值）的比较运算的结果为unknown；
- 在查询语句中，只有使where和having子句中的选择条件为true的元组才被选出作为输出结果。
数据库中只存放视图的定义，不存放视图对应的数据，这些数据仍存放在原来的基本表中，所以一旦基本表中的数据发生变化，从视图中查询的数据也就随之改变了。
（1）定义视图语法：
create view 视图名（列名,.....） as 子查询 with check option
- 子查询可以是任意的select语句，是否可以含有order by和distinct短语，取决于具体系统的实现；
- with check option 表示对视图进行update 、insert和delete操作时要保证更新、插入或删除的行满足视图定义中的谓词条件（即子查询中的条件表达式）；
- 组成视图的列名或者全部省略或者全部指定，没有第三种选择；
- 下面三种情况必须明确指定组成视图的所有列名：
- 某个目标列不是单纯的属性名，而是聚集函数或列表达式；
- 多表连接时选出了几个同名列作为视图字段；
- 需要在视图中为某个列启用新的更合适的名字。
--信息系所有学生视图 create view IS_student as select * from student where Sdept='IS' with check option --选修了1号课程的所有信息系学生视图 create view IS_c1(Sno,Sname,Grade) as select student.Sno,Sname,Grade from student,sc where student.Sdept='IS'and sc.Cno='1'and sc.Sno=student.Sno with check option --分组视图 create view AVGScore(Sno,Sname,avgGrade) as select student.Sno,Sname,avg(Grade) from student,sc where student.Sno=sc.Sno group by student.Sno
- create view语句只是把视图的定义存入数据库字典，并不执行select语句，在对视图进行查询时，才按视图的定义从基本表中将数据查出；
语法：
drop view 视图名 [cascade]
- cascade级联删除语句将删除由该视图导出的其他视图；
- 基本表删除后由该基本表导出的视图都无法使用了，但是视图的定义还存在，需要显示删除视图；
-
视图消解：从数据字典中取出视图的定义，把定义中的子查询和用户的查询结合起来，转换成等价的对基本表的查询，然后再执行修正了的查询。
-
目前多数关系数据库系统对行列子集视图的查询均能进行正确转换，但是非行列子集视图不一定（例如某视图的列为聚集函数结果）。
- 由于视图是不实际存储数据的虚表，因此对视图的更新最终要转换为对基本表的更新；
- 不是所有的视图都能够更新，一般行列子集视图是可更新的；
数据库的安全性是指保护数据库防止不合法使用造成的数据泄露、更改或破坏。
1、用户身份鉴别 2、存取控制 （1）自主存取控制：用户对不同的数据库对象有不同的存取权限，不同用户对同一对象也有不同的权限，而且用户还可将其拥有的权限转授给其他用户。
- 数据库角色：被命名的一组与数据库操作相关的权限，角色是权限的集合。
每一个数据库对象被标以一定的密级，每一个用户也被授予某一个级别的许可证。
- 主体：系统中的活动实体，既包括数据库管理系统所管理的实际用户，也包括代表用户的各进程；
- 客体：系统中的被动实体，是受主体操纵的，包括文件、基本表、索引、视图等；
- 主体的敏感度标记称为许可证级别；
- 客体的敏感度标记称为密级。
规则：
- 仅当主体的许可证级别大于或等于客体的密级时，该主体才能读取相应的客体；
- 仅当主体的许可证级别小于或等于客体的密级时，该主体才能写相应的客体。
通过视图机制把要保密的数据对无权存取的用户隐藏起来。
4、审计把用户对数据库的所有操作自动记录下来放入审计日志中。
5、数据加密 第五章 数据库完整性数据库的完整性是指数据的正确性和相容性。
- 正确性：数据是符合现实世界语义、反映当前实际状况的；
- 相容性：数据库同一对象在不同关系表中的数据是符合逻辑的；
create assertion 断言名 check子句
--限制每门课选修学生不超过60 create assertion maxCount check (60>= all (select count(*) from sc group by Cno))
注：MySql不支持断言。
（2）删除断言drop assertion 断言名5、触发器
触发器是用户定义在关系表上的一类由事件驱动的特殊过程。
（1）定义触发器--网上找的MySQL语法
CREATE TRIGGER 触发器名
BEFORE|AFTER 触发事件 ON 表名
FOR EACH ROW
BEGIN
执行语句列表
END
（2）激活触发器
同一个表上的多个触发器激活时遵循如下的执行顺序：
- 执行该表上的before触发器；
- 激活触发器的SQL语句；
- 执行该表上的after触发器。
drop trigger 触发器名 on 表名
