【数据库复习】SQL
SQL：Structured Query Language（结构化查询语言）；
数据模型：对于数据的描述方式；比如关系数据模型是用二维表来描述，层次数据模型用树来描述数据。
数据模型由三部分组成：
(1)数据结构：定义数据的结构；
(2)数据操作：CRUD；
(3)数据约束：比如键约束、完整性约束等；
数据模型介绍
1.关系数据模型
将数据描述成二维表的形式，例如：
关系模型的特点：
(1)建模方便，操作简单（SQL）；
(2)高效性(访问快速)、易用性；
(3)描述数据单一，即用表来表述数据；
2.半结构化数据模型
将数据描述成类似于XML的形式，例如：
3.对象关系模型
关系的属性不仅仅是基本数据类型，并且每个关系都有相关的方法；
4.层次模型
基于树结构的模型，在物理层次进行操作，很不方便；
5.网状模型
基于图结构的模型，也是在物理层次进行操作，很不方便；
关系模型术语
关系模型约定：属性具有原子性，即每个属性都是基本的数据类型；
关系：二维表；
属性：每列为一个属性；
元组：每一行为一个元组，即记录；
分量（component）：元组的每个属性值就是一个分量；
表模式：由表名、表的属性、属性的数据类型组成；
数据库模式：多个表的模式；
域：属性的数据类型；
实例：表就是关系的实例，当然，如果随着时间的改变，表发生改变，但是他仍然是关系的实例；
超键：能够确定一个元组的属性集；
候选键：最小的超键；
主键：设计者选定的候选键；
主属性：包含于候选码的一个属性；比如如果候选码为AB,则主属性可以是A，B，只要包含于候选码的单个属性即可；
就上图来说，
关系：account；
属性：account_number、balance；
元组：（A-101，500），....；
第一个元组的第一个分量：A-101；
表模式：account（account_number，balance）；
数据库模式未知；
account_number的域：string，balance的域为int；
实例为现在account的这张表；
超键可以为（account_number，balance）、（account_number）；
候选键为（account_number）；
主键根据设计者自行选定；
问：比较以下两个关系，是否为同一个关系？
SQL初步
SQL是专门用于关系模式的查询语言，他是高度抽象化的；
SQL的版本有最早的 ANSI SQL-->SQL92-->SQL99-->SQL2003
现在的各大商业数据库都是有各自版本的SQL，比如SQL Server对应T-SQL，而Oracle对应PL/SQL；
SQL中存在DDL和DML；
SQL默认是基于包的数据库，即允许一个表中存在重复的元组；
SQL不区分大小写，包括关键字，表名，属性名等都是，但是字符串是大小写敏感的；比如：FROM 和 from是一样的，表明Table和tablE是一样的，属性名Attr和attR是一样的；
SQL中的比较运算符：需要注意的是两个运算符，=和<>分别表示相等和不等，不能使用！=来表示不等；
字符串连接运算符：+，即'xiaz'+'dong'；
字符串是用单引号括起来的；
DDL语句
(1)CREATE TABLE创建表模式；
(2)ALTER TABLE 修改表模式，比如：
ALTER TABLE t1 ADD age int；
ALTER TABLE t1 DROP age；
(3)DROP TABLE 删除表模式，比如：
DROP TABLE t1；
小知识点：如果想要为某个属性定义默认值，可以在属性声明后面加上 DEFAULT 值；
比如：age int DEFAULT 0；
基本数据类型
(1) INT；
(2)DECIMAL，指定小数点位数，DECIMAL(n,d) 表示一共n位有效数字，d个小数位；
(3)VARCHAR；变长的字符串，比如VARCHAR(5)表示最多长度为5的字符串；
(4)CHAR：固定长度的字符串，比如 CHAR(5)表示长度为5的字符串；
(5)DATE，DATE'2010-02-03'
(6)TIME：时间，由时、分、秒、微秒组成，TIME '14:00:20' 表示 14时00分20秒；
(7)TIMESTAMP：由DATE和TIME组成；
(5)BOOLEAN：TRUE、FALSE、UNKNOWN；
(6)位串BIT；B'0010'
问：CHAR(3)和VARCHAR(3)有什么区别？
答：如下图所示: 虽然这两种数据类型的存储方式不同，但是如果char(3)类型的'a'和varchar(3)类型的'a'是相等的；
注意：SQL中string是用单引号括起来的；
问：数据库和表的关系？
答：数据库中含有多个表，不能单纯的创建表；
问：PRIMARY KEY和UNIQUE有什么区别？
答：PRIMARY KEY 和UNIQUE都表示属性能够区别所有元组，但是PRIMARY KEY 不能存在NULL值，而UNIQUE允许NULL值；
问：类似PRIMARY KEY这种约束声明在哪？
答：可以声明在两个地方，
CREATE TABLE person(
id INT PRIMARY KEY,
name VARCHAR(20),
ismale boolean DEFAULT TRUE,
birth DATE,
salary DECIMAL(6,2)
);
或者
CREATE TABLE person(
id INT,
name VARCHAR(20),
ismale boolean DEFAULT TRUE,
birth DATE,
salary DECIMAL(6,2)，
PRIMARY KEY(id)
);
示例：
SQL查询
原始Person表有如下数据：
规范：
SELECT attr1,attr2....attrn
FROM table1
WHERE predicate；
执行顺序：先FROM，再where后SELECT；
AS 可用来重命名属性或关系，比如：
|
SELECT id AS 学号,name AS 姓名,age AS 年龄,'ECNU' AS 学校 FROM Person |
运行结果如下所示：
从上图中可以看出，列被重命名，而且还多加了一个“学校”的属性，因此在SELECT 中可以用AS重命名，而且还可以添加新的列；
字符串的操作
1.字符串的连接：+
2.字符串的额比较：<，>，<>，=，<=，>=；
3.字符串的模式匹配：%和 _ 分别表示任意多个字符和单个任意字符；比如'xiazdong'匹配'xia%'，'xia_ _ _ _ _'；
S LIKE P：S匹配模式P时返回true；
S NOT LIKE P： S不匹配模式P时返回true；
字符串中两个连续的单引号表示一个单引号，比如'a''a'表示字符串a'a；
我们可以通过ESCAPE '字符'定义转义字符，比如ESCAPE '\'就表名定义\为转义字符，即\%表示真实的%，而不是模式匹配的%；
比如：name LIKE 'xia\%\_%' ESCAPE '\'表示\作为转义字符，并且名字是以xia%_开头的字符串；
举例：
如果Person表存在以下数据：
可以通过如下语句进行查找
SELECT *
FROM Person
WHERE name LIKE 'xia!%!_%' ESCAPE '!'
就可以查找到此记录；
SQL日期操作
SQL日期分为：
(1)DATE：由年月日组成，标准格式为'yyyy-MM-dd'；
(2)TIME：由小时、分钟、秒组成，标准格式为'HH:mm:ss'；
(3)TIMESTAMP：由DATE和TIME组合而成，标准的格式为'yyyy-MM-dd HH:mm:ss'；
日期实例：
DATE '2012-04-25' 表示 2012年4月25日；
TIME '14:30:00' 表示14点30分00秒；
TIMESTAMP '2012-04-25 14:30:00' 表示2012年4月25日 14点30分00秒；
日期操作是可以通过一般的比较运算符进行比较的；
实例：
Person表结构如下所示：
CREATE TABLE `person` (
'id' int(20),
'name' varchar(20),
'age' varchar(20),
'birth' date,
'meeting' time,
'graduate' timestamp
PRIMARY KEY (`id`)
);
插入语句为：
INSERT INTO Person VALUES (10,'xiazdong-10',30,DATE'1991-12-10' , TIME'15:00:00',TIMESTAMP'2012-02-02 14:20:00' );
NULL操作
1.NULL值和任意值算术运算都是空值；
2. NULL值和任意值比较运算都是UNKNOWN值；
比如：
a=null,b=5,则a+b为null；
a=null,b=5,则a>5为UNKNOWN；
判断是否为NULL的语句为a IS NULL 或 a IS NOT NULL；
问：SELECT * FROM Person WHERE age>0 OR age<=0是否会返回全部的元组呢？
答：不会。因为如果age的值为null，则age>0为UNKNOWN，age<=0为UNKNOWN，则不返回；
排序操作
ORDER BY 属性 [asc\desc]；
ORDER BY子句一定要放在所有语句的后面，即ORDER BY放在GROUP BY \HAVING等的后面；
举例：
对于Person表，如下语句：
SELECT * FROM PERSON
ORDER BY name,id DESC；
表明先以name值降序排列，当name值相等时然后以id值降序排列；
ORDER BY后面的属性可以是表达式，比如：
ORDER BY id+age,age+score；表示先以id+age的总数升序排序，如果id+age相等的话，则再以age+score升序排序；
不同表的属性重名问题
比如：
在Person中Name和Course中Name属性不是一个含义，因此如果以下语句：
SELECT P.Name as stuName , C.Name as CourseName , C.teacherName
FROM Person as P,Course as C
WHERE P.name=C.stuName
为了区分不同表的name属性，我们需要在属性前面加上表的名称，并且还在SELECT 子句中重命名属性；
元组变量：像以上语句的Person as P ，其中P就是元组变量；
并、交、差运算
UNION || UNION ALL：对于集合操作的并；
INTERSECT|| INTERSECT ALL：集合的交；
EXCEPT || EXCEPT ALL：集合的差；
注意：
(1)比如UNION 后的关系需要去重复，因为是集合操作；而UNION ALL后的关系不需要去重复；
(2)需要保证集合操作的关系属性名要相等，不能出现比如：SELECT NAME FROM PERSON union SELECT AGE FROM PERSON；的情况；
举例：
（SELECT name FROM person）
UNION
（SELECT stuname AS name FROM course）；
连接操作
1.交叉连接 CROSS JOIN
最基础的连接类似于直接用逗号分割，但是有一个区别：
A CROSS JOIN B ：A不能等于B，即A和B不能是一张表；
A，B：没有以上限制；
2.自然连接 NATURAL JOIN
A NATURAL JOIN B：将A和B表中属性名相同的两列进行比较，如果值相等，则合并；
比如：
SELECT * FROM Person as A NATURAL JOIN Person as B；
3.θ连接 JOIN ON
A JOIN B ON predicate；
比如：
select * from person as B JOIN person as A ON A.id=B.id
4.外连接 OUTER JOIN
A [FULL | LEFT | RIGHT ] OUTER JOIN B；
A NATURAL LEFT OUTER JOIN B：在自然连接的基础上，保留A中不能匹配的元组，并将B中独有的属性处设为NULL；
举例：
子查询
子查询需要从里向外分析
1.子查询返回一个值
比如 select id from person where id =1 返回的只有一个值 1；
SELECT * FROM Person WHERE id = ( select id from Person WHERE id=1) ;
2.子查询返回一个关系
SELECT * FROM Person WHERE id IN (SELECT id FROM person) ；
2.1子查询位于WHERE中
SELECT * FROM PERSON WHERE ID > ALL (SELECT id FROM person)；
2.2子查询位于FROM中
SELECT * FROM Person，（SELECT id FROM Person） AS P2；
消除重复操作
DISTINCT；
此符号可以放在SELECT后，也可以放在聚集操作之中；
比如：
SELECT DISTINCT person
FROM Person；
分组操作
GROUP BY 和 HAVING；
HAVING子句是对聚集操作符进行限制；
WHERE子句是对一般的属性进行限制；
比如
SELECT age，avg(score)
FROM Person
GROUP BY age
HAVING avg(score)>90
聚集操作
聚集操作忽略null值，比如age有1,2，null，则avg(age)=1.5；
SUM(a)：对a求和；
SUM(DISTINCT a)：在对a求和之前先去除重复；
AVG(a)
AVG(DISTINCT a)：在对a求平均之前先去除重复；
MIN(a)
MIN(DISTINCT a)：在对a求最小值之前先去除重复；
MAX(a)
MAX(DISTINCT a)：在对a求最大值之前先去除重复；
COUNT(a)：计算a属性的个数（不包括null的元组）；
COUNT(DISTINCE a)：在对a计数之前先去除重复；
COUNT(*)：元组的个数（包括null元组）；
补充：Top用法
Top是用来规定返回的元组数目；比如一个数据库表中包含1万个元组，目的是要返回前5个，则可以使用Top；
在SQL Server中语法如下：
SELECT TOP 5 column1,column2.... //规定返回前5个元组，并且列出column1...属性；
FROM Table
比如：
SELECT TOP 5 age
FROM Person
ORDER BY age ； 表示列出年纪最小的5个人；
在MySQL中，语法如下：
SELECT *
FROM Table
LIMIT 5;
比如：
SELECT age
FROM Person
LIMIT 5
ORDER BY age； //列出年纪最小的5个人；
其他符号
EXISTS R：如果关系R非空时返回true；
NOT EXISTS R：如果R空，则返回true；
t IN R：如果元组t属于R中的某个元组，则返回true；
t NOT IN R：如果元组t不属于R中的任何一个值，则返回true；
c > ALL R：c值要大于所有R（只有一个列）的记录；
c < ALL R：c值要小于所有R的记录；
c<>ALL R：c不等于R中全部的记录；
c > ANY R：c大于R中一个记录即可；
c = ANY R：c等于R中任意一个记录即可；
c<>ANY R 和 c<>ALL R 的区别
c<>ANY R 表示R中存在一个元组，不等于c，就是说只要R中存在一个元组不等于c，就返回true；
c<>ALL R表示元组c不在R中；
SQL约束
主动元素：一个表达式或语句，编写后存储在数据库中，当某个特定的时间就会被执行；
比如约束、触发器等都是主动元素，因为比如约束的语句存储在数据库中，当插入或更新数据时被调用进行检查；
为约束命名
我们可以形如：
CONSTRAINT [name] [约束]
比如：
CREATE TABLE Person(
id int CONSTRAINT c1 PRIMARY KEY,
name varchar(30)
);
键约束
PRIMARY KEY；
声明方式有两种：
（1）
CREATE TABLE Person(
id int PRIMARY KEY,
name varchar(30)
);
（2）
CREATE TABLE Person(
id int ,
name varchar(30),
PRIMARY KEY (id)
);
外键约束
一个关系中的某个属性为外键，则此属性中的值必须在另一个关系中的主键或唯一性属性中出现；
(1)
CREATE TABLE Course(
id int REFERENCES Person (id), //引用Person表中的主键：id
name varchar(30)
);
(2)
CREATE TABLE Course(
id int ,
name varchar(30),
FOREIGN KEY (id) REFERENCES Person (id)
);
更新时违反外键约束时的动作设置
默认为拒绝此违反约束的操作；
就拿上面的例子讲，如果Course表中有一个元组的id属性为1，对应Person表的id=1，如果Person表的id从1更新为2，则级联修改，Course表的id也随着修改；
如果Course表中有一个元组的id属性为1，对应Person表的id=1，如果Person表的id=1的元组被删除，则Course表的对应元组置空；
我们可以在声明时设置：
ON DELETE CASCADE：当Person的id删除时，Course对应的元组也会被删除；
ON DELETE SET NULL：当Person的id删除时，Course对应的元组会被设为null；
ON UPDATE SET NULL：当Person的id被更新，则Course对应的元组被设为null；
ON UPDATE CASCADE：当Person的id被更新，则Course对应的元组被更新；
(1)
CREATE TABLE Course(
id int REFERENCES Person (id)
ON DELETE SET NULL
ON UPDATE CASCADE, //引用Person表中的主键：id
name varchar(30)
);
推迟约束检查
如果要执行一个事务，而事务执行到一半时可能会违反某个预先设定的约束，但是最后事务提交时，是不违反约束的，（因为默认是执行一条语句检查一次）则可以使用延迟检验；
这个功能MySQL没有提供延迟约束检查的机制；
每个约束都会带有一个设置，可以是deferrable或not deferrable，分别表示可延迟和不可延迟：
(1)可延迟的意思是每次都在事务提交时才检查约束；
(2)不可延迟的意思是每条语句检查约束；
NOT DEFERRABLE 表示不可延迟，如果在声明时设置为不可延迟，则约束都是不可延迟的；
DEFERRABLE INITIALLY IMMEDIATE 表示不可延迟，但是和上面的NOT DEFERRABLE不同，可以对约束进行更改，改为可延迟；
DEFERRABLE INITIALLY DEFERRED 表示可延迟约束；
问：NOT DEFERRABLE和DEFERRABLE INITIALLY IMMEDIATE 的区别
答：两者语义相同，但是区别在于一个可以对约束更改，一个不可以；
NOT DEFERRABLE只要一设定，就不能更改；
DEFERRABLE INITIALLY IMMEDIATE 可以对约束进行更改，改为可延迟，因此比较灵活，比如：
SET CONSTRAINT aa DEFERRED;//将immediate改为deferred；
SET CONSTRAINT aa IMMEDIATE;//将deferred改为immediate；
举例：
（1）
CREATE TABLE Course(
id int REFERENCES Person (id)
DEFERRABLE INITIALLY DEFERRED, //延迟约束检查;
name varchar(30)
);
（2）
SET CONSTRAINT [name] DEFERRED；
也可以将一个约束设置为延迟的；
非空约束
CREATE TABLE Course(
id int REFERENCES Person (id) NOT NULL, // id属性不为空
name varchar(30)
);
CHECK约束
CHECK约束是形如：
CHECK (age>30) 表示age属性药大于30；
CHECK约束括号中的语句可以是任何WHERE子句中的语句；
（1）基于元组的CHECK约束
当CHECK约束涉及此关系中的多个属性时，则使用基于元组的CHECK约束；
当元组被插入或更新时检查；
（2）基于属性的CHECK约束
此CHECK约束只属于一个属性，当属性被插入或更新时检查；
注意：
(1)CHECK对于旧的数据是不检查的，比如在设置CHECK约束前已经插入了违反CHECK约束的数据，则CHECK约束是检查不出的；
(2)基于元组的约束检查次数比基于属性的约束更多；
CREATE TABLE Person(
id int CHECK ( id > 0), //基于属性的约束
gender varchar(1) CHECK(gender in ('F','M')),
name varchar(30),
PRIMARY KEY (id),
);
CREATE TABLE Person(
id int ,
gender varchar(1),
name varchar(30),
PRIMARY KEY (id),
CHECK ( id > 0 AND CHECK(gender in ('F','M'))) //基于元组的约束
);
修改约束
添加约束
ALTER TABLE [表名] ADD CONSTRAINT [约束名] [约束]；
ALTER TABLE person ADD CONSTRAINT c1 PRIMARY KEY (id);
删除约束
ALTER TABLE person DROP CONSTRAINT c1;
断言
CREATE ASSERTION ass1CHECK (判断语句)；
DROP ASSERTION ass1；
断言类似于CHECK约束，但是又一个不同点：
CHECK约束在删除时不做检查，而断言在任何时候都做检查；
比如：
CREATE ASSERTION a1 CHECK(10<=(SELECT sum(id) FROM Person)); //id的总和大于等于10；
ALTER TABLE Person ADD CONSTRAINT c1 CHECK(10<=(SELECT sum(id) FROM Person)) ;
如果现在有以下数据：
如果删除了id=10的数据，则CHECK约束是不会检查约束的，因为CHECK约束只会检查更新和插入；
而断言则会检查到不满足断言；
触发器
触发器简单地说就是：当遇到某个事件时，触发器被触发，并执行一系列动作；
触发器被称为事件-条件-动作规则（ECA规则），因为是由于某个事件（比如插入、删除、更新事件）的发生触发了触发器，而如果此事件使得条件（WHEN）为真，则执行动作（SQL），如果条件为假，则不执行动作；
MySQL也不支持标准的触发器语法，而是有自己的触发器语法！
我们用例子来讲解触发器的语法：
CREATE TRIGGER t1 //t1触发器
AFTER UPDATE OF id ON Person //在Person表的id被更新之后被触发，AFTER可以被改为BEFORE，UPDATE可以被改为INSERT DELETE，但是插入和删除不能使用“OF 属性”，只能是AFTER INSERT ON Person,AFTER UPDATE On Person,AFTER DELETE ON Person;
REFERENCING
OLD ROW AS old //旧元组，比如如果是更新，则old表示更新前的每行；
NEW ROW AS new //新元组，比如如果是更新，则new表示更新后的每行；
//可以加上
//OLD TABLE AS oldtable
//NEW TABLE AS newtable 如果是FOR EACH STATEMENT，则必须是OLD TABLE 和 NEW TABLE
FOR EACH ROW //对于每行,执行一次触发器，可以是FOR EACH STATEMENT，比如如果是更新语句触发器，则对于每个更新语句，执行一次触发器；
WHEN (id>10) //条件，可以是where子句中的任何表达式，可以不加，不加则直接执行动作
BEGIN
UPDATE Person //动作
SET name= old.name
WHERE name=new.name;
END
注意点：
(1)
REFERENCING
OLD ROW AS old //旧元组
NEW ROW AS new //新元组
FOR EACH ROW //对于每行
是固定的，如果是FOR EACH STATEMENT，则不能使用：
REFERENCING
OLD ROW AS old
NEW ROW AS new
，但是可以使用：
REFERENCING
OLD TABLE AS old
NEW TABLE AS new
FOR EACH STATEMENT
，表示对于每个新、旧的表；
当然也可以同时引用这些，如：
REFERENCING
OLD TABLE AS oldtable
NEW TABLE AS newtable
OLD ROW AS oldrow //旧元组
NEW ROW AS newrow
FOR EACH ROW
(2)只有UPDATE能够针对某个属性的更新定义触发器，INSERT/DELETE只能针对某个元组定义触发器；
示例：
(1)
BEFORE INSERT ON Person
REFERENCING
OLD ROW AS oldrow
NEW ROW AS newrow
FOR EACH ROW
表示在插入语句执行之前触发，newrow表示将要插入的元组，而oldrow没有意义；
(2)
AFTER DELETE ON Person
REFERENCING
OLD ROW AS oldrow
NEW ROW AS newrow
FOR EACH ROW
表示在删除语句执行之后触发，oldrow表示要删除的元组，而newrow没意义；
MySQL触发器语法
没有WHEN , REFERENCING，而old和new默认已经给定；
CREATE TRIGGER t1
BEFORE UPDATE ON Person
FOR EACH ROW
BEGIN
IF new.id>10 then
SET NEW.name='zzzz';
END IF;
END
视图
视图并不是存储在数据库中的数据，而是由一个SQL语句构成；
CREATE VIEW viewname AS （SQL语句）；
CREATE VIEW viewname(属性重命名) AS (SQL语句)；
DROP VIEW viewname;
可更新视图：如果视图只涉及单个表，且满足其他一些必要条件时，才能对视图进行更新、插入、删除；
对视图的插入、删除、更新实际上是对表的操作。
举例：
CREATE VIEW v1 AS(
SELECT id
FROM Person
WHERE id>5
);
针对视图的替换触发器
有一种专门针对视图的触发器，如果定义了此触发器，则对视图的插入、删除、更新语句将会被触发器定义的语句所替换；
语法：将BEFORE\AFTER换为INSTEAD OF；
比如：
INSTEAD OF UPDATE ON Viewname
.......
BEGIN
...... //触发器的更新语句将不会执行，而会执行此处的语句；
END
物化视图
和视图不同，物化视图是真实地存储在数据库中的；
如果一个视图被频繁的访问到，则需要使用物化视图预先计算，减少重复计算量；
CREATE MATERIALIZED VIEW name AS (
SELECT name,avg(score)
FROM course,person
WHERE course.id=person.id
GROUP BY name
);
原本如果每次要查询每个人的平均分，则需要计算上面的SQL语句，如果定义了物化视图，则只需要简单的查询即可；
物化视图的维护
(1)对于每个更新语句都会增量式地更改物化视图；
比如Person(id,name,age);
物化视图SELECT id FROM Person;
当插入Person一个元组时，则不需要重新构造物化视图，只需要把新插入的id插入到物化视图中；
(2)每天某个时间点重构物化视图；
索引
针对某种情况加快查找速度；一般都是用B树或B+树实现；
CREATE INDEX indexNameONTable（attr1,attr2）； //对attr1和attr2做索引
DROP INDEX indexName；
比如：
CREATE INDEX i1 ON Person(id,name);
SQL DML
INSERT INTO [Table][(属性)] VALUES(值)：插入单个元组；
INSERT INTO Table [SQL语句]：批量插入；
DELETE FROM Table WHERE [...]；
UPDATE Table SET [更新语句] WHERE [谓词]；
举例：
INSERT INTO Person VALUES(1,'xiazdong');
INSERT INTO Person(name,id) VALUES('xiazdong',1);
INSERT INTO Person
SELECT DISTINCT id FROM Person；
UPDATE Person SET age=20,name='xiazdong' WHERE id=1; //将id=1的人姓名改为xiazdong，年龄改为20；
SQL事务
