数据库
一、数据库系统
组成：硬件、软件、数据库、用户。
二、数据模型分类
组成：数据结构/数据操作/数据完整性约束。
1、概念数据模型：E-R模型/扩充的E-R模型/谓词模型等。面向用户。
2、逻辑模型：层次模型/网状模型/关系模型/面向对象模型。面向用户和系统，主要用于DBMS实现。
3、物理模型：不仅与DBMS有关，还与OS和硬件有关。
a.：E-R模型
例：
b.层次模型：有且只有一个节点没双亲节点(称根节点)，根以外节点有且只有一个双亲节点。
例：
c.关系模型：R(U,D,DOM,I,∑) ，R是关系名，U是全部属性，D是属性取值值域Domain，DOM是属性列到域的映射(U->D)，I是一组完整性约束条件，∑是属性集间的一组数据依赖。
d.服务器端：web服务器/应用服务器/数据库服务器等。
三、关系数据库
1、传统集合运算
并RUS，差R-S，交R∩S，广义笛卡尔RXS。
2、专门关系运算
例：表1: S(S1,S2,S3,S0)，表名(属性)，表2: C(C1,C2,C3,S0)
1)选择运算
指在给定的关系中选择出满足条件的元组组成一个新的关系。
σs1>=1990Λs2="刘"(S)
2)投影运算
对一个关系内属性的指定。提供了一种从垂直方向构造一个新关系的手段。
πs3,s1(S) 查找S列表中s1,s3属性
3)连接
把两个关系连接成一个新的关系。
RXS,再满足R.R1=S.S1的。
4)自然连接
要求两个关系中进行比较的分量必须是相同的属性组，并且在结果中把重复的属性列去掉。
先计算RXS，再选R.S0=S.S0的，再去掉其中一个S0列。
5)除运算
适用的场合：包含短句“对所有的”的查询。
R÷S，需要满足R⊆S
3、查询优化
关系代数表达式的等价变换规则：
1)连接和笛卡尔积的交换律 E1XE2=E2XE1，连接一样。
2)连接和笛卡尔积的结合律 (E1XE2)XE3=E1X(E2XE3)，连接也一样。
3)投影的串接
4)选择的串接
5)选择与投影的交换
6)选择对笛卡尔积的交换律
7)选择与并运算交换
8)选择与差运算交换
9)投影对笛卡尔积的交换率
10)投影对并的交换律
1)尽可能早地执行选择策略；
2)合并笛卡尔积和其后的选择操作，使之成为一个连接运算；
3) 合并连续的选择和投影操作，以免分开运算造成多次扫描文件，节省操作时间；
4)找出表达式里的公共子表达式，预先计算出结果保存，以免重复计算；
5)适当的对关系文件做预处理，根据实际需要对文件分类排序或建立临时索引等；
6)同时进行投影和选择运算。
四、关系数据库标准语言SQL
实现对数据的定义、操作和控制约束等功能。
1、SQL体系结构
1）SQL用户，终端用户或应用程序等。
2）基本表，简称基表，在SQL中一个关系对应一个表。
3）视图，从一个或几个基表导出的表，虽然它也是关系形式，但它本身实际不存储在数据库中，只存放对视图的定义信息（没有对应的数据）。视图是一个虚表或虚关系，基表是实关系。
4）存储文件，每个基表对应一个存储文件。一个基表还可以带一个或多个索引，存储文件和索引一起构成了关系数据库的内模式。
2、SQL的定义功能
1）基本表的定义
CREATE TABLE <表名>
(<列名> <数据类型> [列级完整性约束条件]
[,<列名> <数据类型> [列级完整性约束条件] ...]
[,<表级完整性约束条件>]);
例：
CREATE TABLE 学生
(学号 CHAR(8) NOT NULL UNIQUE, -------等同于primary key，都是主键
姓名 CHAR(8) primiary key,
学院 CHAR(15) default '计算机'; -------取一个默认值；
primary key (学号,姓名), -------多个关键字只能用primary key(<属性1>,<属性2>...)
foreign key(学号) references 学习(学号), -------外健，如果只有一个属性，可以直接用reference <表名> <属性>
foreign key(姓名) references 课程(姓名)); -------第一个姓名是外部关键字，第二个姓名是被参照的属性。
#SQL类型：number(相当于int)，char/varchar(char添加空格到指定长度，varchar自动删除后继空格，允许长度变化)；
#oracle类型：Text(clob/blob),date,timestamp;
2）基本表的修改
ALTER TABLE <表名>
[ADD <新列名> <数据类型> [完整性约束]]
| [DROP <完整性约束名>]
COLUMN <列名> |
| [ALTER COLUMN <列名> <数据类型>]
例：
alter table 学生 add 年龄 smallint; 增加
alter table 学生 drop column 年龄; 删除
alter table 课程 alter column 课程名 char(20); 修改课程名的长度为20。
3）基本表的删除
DROP TABLE <表名> [RESTRICT|CASCADE];
#restrict表示如果有视图或约束条件设计要删除的表时，就禁止DBMS执行该命令；cascade选项则将该表与其涉及的对象一起删除。
例：
drop table 临时表 cascade
4）索引的建立与删除
根据关系(表)中某些字段的值建立一个树型的结构的文件，目前很多DBMS直接使用主键的概念建立主索引，一个表只能有一个主索引，可有多个其他索引。
建立索引：
CREATE [UNIQUE][CLUSTER] INDEX <索引名> on <表名> (<列名> [ASC|DESC][,<列名>[<ASC|DESC>]]...);
unique 表明此索引的每个索引值只对应唯一数据记录；
cluster表示要建立的索引是聚簇索引，聚簇索引指索引项的顺序与表中记录的物理顺序一致的索引组织（每个表只能有一个）；
例：
create unique index SC_IDX_SNO_CNO on 学习(学号 asc,课程号 desc);
删除索引：
DROP INDEX <索引名>; //不影响primary key和unique约束条件，这两种只能用alter table...drop删；
索引技巧：
a.对小表来说使用索引性能不会有任何提高；
b.索引列中有较多的不同的数据和空值时，索引会使性能有极大的提高；
c.当查询要返回的数据很少时，索引可以优化查询，如果返回的数据很多，索引会加大系统开销；
d.索引可以提高数据的返回速度，但它使数据的更新操作变慢；若进行大量的更新操作，先删除索引，之后再恢复索引；
e.索引会占用数据库的空间；
f.不要对经常需要更新或修改的字段创建索引；
g.不要将索引与表存储在同一个驱动器上，分开存储会减少访问的冲突从而使结果返回地更快。
3、数据查询
SELECT [ALL | DISTINCT] <属性列表> #类似于关系代数里的投影运算；distinct去除重复的行；可以对列进行算数运算得到表中不存在的信息。
FROM <表名或视图名> [,<表名或视图名>]...
[WHERE <条件表达式>] #类似于关系代数里的选择运算；
[GROUP BY <列名>] #对查询结果进行分组
[HAVING <条件表达式>] #对分组的结果进行筛选
[ORDER BY <列名> [ASC|DESC]]; #对查询结果进行排序
例：year(now(o)) 取当前时间
column as <列名>或column <列名>
1）单表查询
select 学号,2009-出生年份 as 年龄 from 学生 #列出学号和年龄并用年龄作为别名。
where中子句常用查询条件=,<,>...between and,not between and,in,not in,like,not like,is null,is not null,and,or
where 姓名 like '_王%';找第二个字为王的姓名；如果名字里有_或%，用escape'\'对通配符进行转义；
例like 'DB\_BESIGN' escape'\' 转义为普通的_(DB_BESIGN)。
使用集函数：
count ([distinct|all]*) #统计元组个数
sum/avg/max/min([distinct|all]<列名>)求列中。。。
2）连接查询
若一个查询同时涉及两个以上的表，称为连接查询，也称复杂查询。
a.等值与非等值连接查询：比较运算符：=,>,<,>=,<=,!=
例：where 学生.学号=学习.学号
b.自身连接
例：from 课程 as first,课程 as second
where first.先修课称号=second.课程号
c.外连接查询
左外连接：规定所有记录从连接语句左侧表中返回，即使右侧表中并没有匹配。
例：from 学生 left outer join 学习 on 学生.学号=学习.学号
from 学生,学习 where 学习.学号(+)=学生.学号
右外连接：
例： from 学习 right outer join 课程 on 学习.课程号=课程.课程号
from 学习,课程 where 课程.课程号=(+)学习.课程号
d.复合条件连接查询：
例：where ... and ... and...
join 学习 on 学生.学号=学习.学号 and ... and ...
join ... on ... join ... on ...
e.集合运算连接查询：union/intersect/except对应于并/交/差，这三个都是自动去除重复，union all/intersect all/except all保留重复。
3）嵌套查询
一个(select...from...where)语句称为一个查询块，将一个查询块嵌套在另一个查询块的where或having中的查询称为嵌套查询。
a. 带in的子查询： select ... from ... where 学号 in (select ...)
b. 带比较运算符的子查询：
... where 课程号=（select ...）
c.带any/all谓词的子查询(需同时用比较运算符)
... where 2009-出生年份<any (select ...)
d.带exists(代表存在)谓词的子查询：不返回数据，只产生逻辑true/false
用in的可转换为exists，反之不行。
例：exists/not exists (select * ...)
4、数据更新
1）插入数据
插入单个元组：
INSERT
INTO <表名> [(<属性列1>[,<属性列2>...])]
VALUES (<常量1>[,<常量2>]...);
例：
insert
into 学生
values ('090010','夏雨','男',...);
插入子查询结果：
INSERT
INTO <表名> [(<属性列1>[,<属性列2>...)]]
子查询;
例:
insert
into dept_age(sdept,avg_age)
select ...
2）删除数据
DELETE FROM <表名>
[ WHERE <条件> ];
例：
delete from 学生
where 学号='0901';
3）修改数据
UPDATE <表名>
SET <列名>=<表达式1>[,<列名>=<表达式2>]...
[WHERE <条件> ]; //修改指定表中满足where子句条件的元组。
例：
update 学生
set 成绩=成绩+1
where 课程号='18001';
5、视图
1）建立视图
CREATE VIEW <视图名>([<列名1>[,<列名2>]...)]
AS <子查询>
[WITH CHECK OPTION];
例：
create view BT_S(学号,姓名,年龄)
as select ...
from 学生;
2）删除视图
DROP VIEW <视图名> //关联的也失效，用户应该使用DROP VIEW语句将它们一一删除。
3）查询视图
select *
from 视图
where ...
4）更新视图
insert/delete/update 。在把对视图的更新操作转换为对基本表的等价操作后，还是允许对这些视图进行更新的。
a.若视图的属性来自属性表达式或常数，不允许insert/update，允许delete。
b.若视图的属性来自库函数，不允许对此视图更新。
c.若视图定义中有GROUP BY/DISTINCT，不允许对此视图更新。
d.若视图定义中有嵌套查询，并且嵌套查询的from子句涉及到导出该视图的基本表，不允许对此视图更新。
e.若视图有两个以上的基本表导出，不允许对此视图更新。
f.如果在一个不允许更新的视图上再定义一个视图，二次视图是不允许更新的。
6、数据控制
1）授权
GRANT <权限>[,<权限>]...
[ON <对象类型> <对象名>]
[TO <用户1>[,<用户2>]]...
[WITH CRANT OPTION]; //指定了with grant option，则获得某种权限的用户还可以把这种权限再授予别的用户。
例：grant select on table 学生 to User1;
2）收回权限
REVOKE <权限>[,<权限>]...
[ON <对象类型> <对象名>]
[FROM <用户1>[,<用户2>]]...
例：revoke update(学号) on table 学生 from User4;
五、数据库保护
备份和恢复/并发控制/完整性控制/安全性控制。
1）事务：用户定义的一个数据库操作序列，这些操作要么全做，要么全不做。在关系数据库中一个事务可以是一条SQL语句/一组SQL语句或整个程序。
定义事务：
BEGIN TRANSACTION ;开始事务
COMMIT ;提交事务，重新改写数据库
ROLLBACK ;撤销事务，发生错误撤销
例：
begin transaction
insert
into S(S#,sname,sage,sdept)
values('1002'...)
commit/rollback
事务特征：原子性/一致性/隔离性/持久性
2）数据库恢复：基本原理是‘冗余’，即数据的重复存储。数据库的恢复是吧数据库从错误状态恢复到某一已知的正确状态的功能，DBMS必须具有此项功能。
恢复技术：一、如何建立冗余数据；二、如何利用这些冗余数据实现数据库恢复。
通过数据转储建立冗余；通过日志文件建立冗余；事务恢复。
事务故障一定在事务提交前发生，这时应撤销(UNDO)该事务对数据库的一切更新，是由系统自动完成的，对用户透明。
3）并发控制：封锁技术，封锁是实现并发控制的一种机制，封锁就是事务T在对某个数据对象操作之前，先对其加锁。
并发操作引发的问题：丢失修改(写-写冲突引起的)；不可重复读(同一个查询操作执行两次或多次的结果应该是一致的，否则产生不可重复读现象)；读“脏”数据(写-读冲突引起的，T2在T1改写后读，但是T1又撤回了，此时T2读到的就是“脏”数据。)。
a.排它锁(X锁)：写锁
b.共享锁(S锁)：读锁，同一数据对象允许多个事务并发地读，但对写操作是排它的。
c.封锁协议：约定规则，例如何时申请X锁或S锁、持续时间、何时释放等，这些规则称为封锁协议。
一级封锁协议：读数据前不加锁，修改前加锁。防止丢失修改，并保证事务T是可恢复的。不可保证可重复读和不读“脏”数据。
二级封锁协议：读前加锁，防止可丢失修改，进一步防止读“脏”数据。
三级封锁协议：加锁，释放锁。防止丢失修改，解决不可重复读和不读“脏”数据。
六、数据库应用
1、数据库操作：
1）查询数据库：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.02 sec)
- information_schema：主要存储了系统中的一些数据库对象信息，比如用户表信息、列信息、权限信息、字符集信息和分区信息等；
- mysql：MySQL 的核心数据库，类似于 SQL Server 中的 master 表，主要负责存储数据库用户、用户访问权限等 MySQL 自己需要使用的控制和管理信息。常用的比如在 mysql 数据库的 user 表中修改 root 用户密码。
- performance_schema：主要用于收集数据库服务器性能参数。
- sys：MySQL 5.7 安装完成后会多一个 sys 数据库。sys 数据库主要提供了一些视图，数据都来自于 performation_schema，主要是让开发者和使用者更方便地查看性能问题。
2）创建/查找/删除数据库
create database test_demo01; 创建数据库；
SHOW DATABASES LIKE '%test%'; 查找中间是test的数据库。
use test_demo01; 进入数据库test_demo01
select database(); 查看当前使用数据库；
show variables like 'port'; 查看数据库使用端口；
show tables; 查看数据库的表信息;
drop database test_demo01; 删除数据库test_demo01；
