选项
Oracle：甲骨文公司收费
Microsoft SQL Server：微软公司收费
MySQL 与 PostgreSQL的对比
1、开源许可
MySQL是GPL许可，使用者对代码所做的修改也必须开源
PostgreSQL是BSD许可，允许使用者对代码所做的修改进行保密
2、开发背景
MySQL的背后是一个成熟的商业公司Sun公司
PostgreSQL的背后是一个庞大的志愿开发组
3、并发模式
MySQL使用的是线程模式，线程模式对资源消耗比较少，支持更多连接
PostgreSQL使用的是进程模式，进程模式对多CPU利用率比较高
关系模型的完整性约束：实体完整性、参照完整性、用户定义的完整性
完整性：数据正确性（对象自身符合现实）和相容性（对象之间符合逻辑）
实体完整性：主键唯一且不能为空
参照完整性：外键要么为空，要么是另一张表存在的主键值
用户定义的完整性：例如非主键的非空性和唯一性
集合运算：并 U，交 n ，差 -，笛卡尔积 X
笛卡尔积：A表的行 和 B表的行，两两组合 成目标表
关系运算：选择（行过滤）、投影（列过滤）、连接（等值连接、自然连接、外连接）、除
等值连接：在A表和B表的笛卡尔积中，过滤出 A表指定属性 = B表指定属性 的行，组成目标表
自然连接：在A表和B表的笛卡尔积中，过滤出 A表和B表所有的同名属性都相等 的行，组成T表，T表去除重复属性，得到目标表
悬浮组：等值连接中，AB两表中被舍弃的行
外连接：AB表的等值连接T + AB表的悬浮组D，组成外链接。D加入T时，没有的属性，值取null
左外连接：外连接中，只保留A表悬浮组
右外连接：外连接中，只保留B表悬浮组
除：从A表中提取A表有而B表没有的列，去重作为T表；T表中的一些行与B表的笛卡尔积为A的子集，这些行组成目标表
SQL（Structured Query Language）：结构化查询语言
数据字典：保存关于表、视图、索引、用户的设置
SQL操作对象：模式（表的命名空间）、表、视图、索引、断言、触发器
create schema wang authorization wang -- 创建模式，指定授权用户
drop schema wang cascade -- 级联删除模式，另有 restrict，只允许删除空模式
create table wang.Student -- 创建表，指定模式
(Sno char(9) primary key, -- 主键
Sname char(20) unique, -- 唯一性约束
Spno char(4) not null, -- 非空约束
Sgrade int check(Sgrade>0), -- 条件约束，可以是where字句能用到的所有条件
foreign key (Spno) references Course(Cno)) -- 外键
alter table student -- 修改表
add column Sname char(20) unique -- 添加新列
add unique(Sname), -- 添加完整性约束
alter column Ssex int -- 修改列数据类型
drop column Ssex cascade -- 级联删除列，另有restrict，只允许删除未被其他对象（表，视图）引用的列
drop table student cascade -- 级联删除表，另有restrict，只允许删除未被其他对象（表，视图）引用的表
数据类型：定长字符串char(n)，不定长字符串varchar(n)，2字节整数smallint，4字节整数int，8字节整数bigint，布尔值boolean，日期date，时间time，时间戳timestamp，精度为n的浮点数float(n)
搜索路径
创建表时，若不指定模式，则默认采用搜索路径里设置的模式
查看：show search_path
设置 set search_path to 模式1,模式2
索引
加快查询、分组、排序、连接速度，但需要额外占用存储空间，增删改表时需要额外时间更新索引
create index Sno on SC(Sno ASC, Cno DESC) -- 给SC表创建索引Sno，索引属性为Sno和Cno。ASC为升序，DESC为降序
create unique index Sno on SC(Sno) -- 唯一索引，性能更高，插入值需要唯一，但允许为空
alter index Sno rename to Cno -- 重命名索引
drop index Sno -- 删除索引
索引设计原则
1、一个频繁更新的表，索引数 和 索引列数 尽可能少
2、行数少表、重复值多的列 不要建索引
3、在unique列上建立unique索引
4、一个频繁排序和分组的表，对应列建立索引
数据查询
/** 列查询 **/
select distinct Sno,lower(Sname),2018-Sage birth -- 选择列，可以进行表达式运算，可以取别名，distinct表示去重
from Student -- 来源表
where Sno=1 -- 查询条件
order by Sage,(case when Sage=null then 0 else 100 end) desc -- 结果排序，这里实现了三元表达式运算，desc表示降序
/** 统计查询 **/
select count(distinct Sno) --distinct表示去重
from Student
order by ( case when store_id is null then 0 when store_id=25 then 1 else 2 end) -- 自定义排序
/** 组查询 **/
select Cno,Cname, avg(Sno) -- 这里得到一个组列表，Cno是一个组共有的，Cname不是一个组公有的，会得到一个次序在前的值，avg是对一个组进行统计
from Student
group by Cno
having count(*) > 3 -- 组过滤
查询条件
比较：=、>、< 等
范围：between and，not between and
集合：in，not in
字符匹配：like，not like
空值：is null，is not null
多重条件：and，or，not
聚集函数
count(*)、count(distinct 列) 、count(列)，max(列)、min(列)、sum(列)、avg(列)、group_concat(列)
连接查询
/** 等值连接 **/
select student.*,sc.* -- 取所有列
from student,sc
where student.Sno = sc.Sno -- 等值连接
/** 非等值连接 **/
select student.*,sc.*
from student,(select ... from ...) as sc -- sc为派生表
where student.Sno > sc.Sno -- 在笛卡尔积中过滤出符合条件的行，组成目标表
/** 自然连接 **/
select student.Sno,Sname,Ssex -- 指定具体列，去除重复。两表中不重复的列，可以不指明表名
from student,sc
where student.Sno = sc.Sno
/** 自连接 **/
select first.Sno,second.Spno -- 指定具体列，去除重复。两表中不重复的列，可以不指明表名
from student first,student second -- 给表取别名，用于区分
where first.Sno = second.Spno
/** 外连接 **/
select student.Sno,Sname,Ssex
from student outer join sc on(student.Sno = sc.Sno)
/** 左外连接 **/
select student.Sno,Sname,Ssex
from student left outer join sc on(student.Sno = sc.Sno)
/** 右外连接 **/
select student.Sno,Sname,Ssex
from student right outer join sc on(student.Sno = sc.Sno)
/** 多表连接 **/
select student.Sno,Sname,Ssex
from student,sc,course
嵌套查询
/** 不相关嵌套查询 **/
-- 先处理子查询，再处理父查询
select Sname
from student
where Sdept in -- 子查询的结果为单列集合，那么谓词为 in、>any、>all、=any、!=all等，some与any同义
(select Sdept from ...); -- 内层查询(子查询)
/** 相关嵌套查询 **/
-- 1、忽略子查询，先得到一个结果T；2、遍历T，计算出子查询，过滤出符合条件的行
select Sname
from student x
where Sno >= -- 子查询的结果为单列单行，那么谓词为>、<、=等
(select avg(grade) from student y where y.sno = x.sno); -- 子查询用到父查询中的表
select Sname
from student x
where exists -- 子查询的结果多列集合，那么谓词为exists、not exists
(select * from student y where y.sno = x.sno)
select student.*,sc.*
from student,(select * from student) sc --子查询在from子句，作为一个表
where student.Sno = sc.Sno
SELECT * from
(SELECT *, MAX(age) AS max_age FROM student GROUP BY gender) student -- 子查询结果表的别名
ORDER BY max_age -- 组函数不能被order by引用，可以改成这样的嵌套查询
select age+(SELECT MAX(score) AS maxScore FROM score) from student -- 子查询在select子句
集合查询
select * from student
union -- 并集，另外交集intersect，差集except
select * from student;
数据更新
insert -- 插入行
into student(Sno,Sname) -- 未指定值的列，默认为null
values('2000','hogen'), ('300','alina');
insert
into student(Sno,Sname)
子查询; -- 以子查询为输入
update student -- 更新行
set Sage=Sage+2
where Sno='3333' -- 可以是子查询
delete -- 删除行
from student
where Sno='33333' -- 可以是子查询
视图
一个或几个表导出的虚表，数据库只保存视图的定义，不保存视图对应的数据
视图消解：查询视图时，结合用户查询和视图定义，最终形成对表的查询
视图的作用：简化SQL语句，隐匿原表数据；不能提升查询性能
/** 单表视图 **/
create view student_view(Sno,Sname,Sbirth) -- 保留主键的视图称为行列子集视图
as
select Sno,Sname,2018-Sage -- Sbirth为派生属性（虚拟属性，表达式属性），表只保存最简属性，视图提供派生属性
where Sage > 30
with check option -- 视图消解时，遇到不符合 where 字句的操作，拒绝执行
/** 多表视图 **/
create view student_view(Sno,Sname)
多表查询
drop view student_view cascade -- 级联删除
权限
/** 授权 **/
grant update(Sno),select -- 操作另有insert、delete、references、all privileges，可以指定列也可以不指定
on table student
to user1 -- 另有public，表示所有用户
with grant option; -- 允许转授权（传播权限）
/** 回收权限 **/
revoke select
on table student
from user1;
/** 创建用户，指定系统角色 **/
create user hogen with dba; -- 没有指定系统角色的话，默认为connect
/** 自定义角色 **/
create role role1; -- 创建角色
grant select
on table student -- 另有 view
to role1; -- 给角色授权
grant role1
to role2,hogen -- 把角色的权限授予用户或其他角色
with admin option; -- 允许转授权给其他角色
revoke select
on table student
from role1; -- 回收角色权限
系统角色
dba：超级管理员，拥有包括创建用户、创建模式的所有权限
resource：有创建表、操作表的权限
connect：只有操作表的权限
断言
create assertion ass -- 创建断言
check (60>=( -- 拒绝插入第61条，即拒绝将导致断言为否的操作
select count(*) from Student)
);
drop assertion ass; -- 删除断言
触发器
create trigger trigger1 -- 触发器的命名空间也是模式
after update on table1 -- 触发器只能定义在表上。after 另有 before，update 另有delete、insert
referencing
oldrow as oldStudent -- 行数据别名
newrow as newStudent
for each row -- 行级触发器，另有语句级触发器 for each statement，执行一条语句只触发一次
when(newStudent.Sage > oldStudent.Sage) -- 触发条件
begin
insert into ... ; -- 触发动作
end
drop trigger trigger1 on table1; -- 删除触发器
关系规范化
码：由一个或多个属性组成
超码：能唯一确定一行的码
候选码：最小范围的超码，即只保留超码中的必要属性
主码：从多个候选码中选一个为主码
主属性：任一候选码包含的属性
非主属性：非码属性，即主属性以外的属性
外码：另一个关系的候选码
完全函数依赖：非主属性 完全函数依赖于 候选码，即确定的候选码 能确定 所有非主属性
部分函数依赖：非主属性 部分函数依赖于 超码，即部分确定的候选码 就能确定 所有非主属性
第一范式 1NF：一个属性不可拆分，多个属性之间不能重复
第二范式 2NF：任一非主属性完全函数依赖于任一候选码
第三范式 3NF：任一非主属性只函数依赖于候选码
第三范式修正BCNF：任一主属性 完全函数依赖且只函数依赖于 其他候选码
E-R图（Entity Relationship Diagram，实体-联系图）
矩形框：实体
椭圆框：属性
菱形框：联系，与实体连接，标明（1:1，1:m，n:m）
事务
ACID特性
原子性（Atomicity）：事务不可拆分
一致性（Consistency）：事务执行前后，数据库保持一致性（正确性）状态
隔离性（Isolation）：事务与事务之间互相隔离
持续性（Durability）：永久性，事务一旦提交，永久改变
begin transaction; -- 开始定义事务
sql语句
commit; -- 提交事务
rollback; -- 某种情况下，显式撤销事务
封锁
排他锁（X锁）：写锁，独占锁，独享锁，互斥锁
共享锁（S锁）：读锁
一级封锁协议：事务在修改数据之前加X锁，事务结束时释放锁
二级封锁协议：在一级封锁协议的基础上，事务在读取数据之前加S锁，读取结束时释放锁
三级封锁协议：在一级封锁协议的基础上，事务在读取数据之前加S锁，事务结束时释放锁
活锁：在没有事务排队机制的系统上，一个事务请求加锁，可能长时间被插队，导致这个事务没法完成
死锁：两个事务互相依赖于被对方加锁的数据，导致两个事务都无法完成
预防死锁
一次封锁法：一次性把所需数据全部加锁，用完释放；缺点：降低了系统并发能力
顺序封锁法：预先规定一个加锁顺序，所有事务都遵循该顺序；缺点：顺序难以维护，不能处理动态选择的封锁对象
死锁诊断
超时判定：事务等待超过时限，判定事务遇到死锁；缺点：容易误判
等待图分析：数据库管理系统能获取到事务等待图，判断有没有循环等待
死锁解除
数据库管理系统撤销一个事务，释放其持有的锁
事务加锁规则
可串行性：给定一个初始状态；1、多个事务依次启动，并行执行；2、同样的几个事务依次串行执行；如果12两种方式的结果无差异，则称这几个事务可串行化（具有可串行性）
两段锁协议（2PL）：第一阶段（扩展阶段），读写数据前加锁，不解锁；第二阶段（收缩阶段），只解锁，不加锁
所有事务都遵循两段锁协议 是 这些事务可串行化 的充分条件
显式封锁：本对象被加锁
隐式封锁：本对象的父对象被加锁
封锁冲突：一个对象只能有一个显式锁 或者 一个隐式锁，因此给对象加锁时系统需要检查它是否已经有显式锁、隐式锁，还要检查子对象有没有显式锁
意向锁：给一个对象加锁时，同时给他的所有父对象加意向锁，使得其他事物给父对象加锁时，不用向下检查
意向共享锁：IS锁
意向排他锁：IX锁
数据库优化
1、建立合适的索引
2、sql 语句优化
3、创建冗余字段，用来存储（查询、排序、分组用到的）需要大量计算得来的数值，用触发器更新该字段
4、去除多余字段，多余关联，杜绝一个接口服务多个场景导致的字段多余
5、利用elastic search 实现模糊搜索
SQL案例
1、每组取 最小计算值 所在的那条记录
难点：计算值可以被order by，但是group by时 所取的行，不是order by的第一行
select o.*, abs(score - 50) as minscore from student o, # abs(score - 50) 即为本例针对的计算值
(SELECT name, min(abs(score - 50)) as minscore FROM student group by name) t # 找到name和最小计算值的对应关系，作为临时表
where o.name=t.name and abs(score - 50)=t.minscore # 等值连接
2、不存在才插入
INSERT INTO student(name) SELECT '张三' FROM DUAL -- DUAL为mysql的一个辅助表
WHERE NOT EXISTS(SELECT id FROM student WHERE name='张三') -- 此语句用于防止重复插入
3、更新 或 插入
REPLACE INTO student(id, num, name) VALUES(3, 5, '张山') -- 主建或 唯一键 存在则更新，不存在则插入
