数据库知识小结

临近大四准备找工作之际，遂拿出数据库复习一番，有一些网上的博客参考，以及自己的见解。仅供参考，如有错误，望指出。

外键：

过分强调使用外键会使导致性能降低，导入导出操作更慢。
外键能保证数据一致性，完整性，可靠性。
外键约束删除问题：在删除有外键的数据库表经常会出现错误，提示有外键约束
方法一：查看报错，先倒着删除包含该外键的表信息，再删除该表
方法二：暴力点，取消外键约束–删除–恢复外键约束。
禁用外键约束
SET FOREIGN_KEY_CHECKS=0;
–进行删除
启动外键约束
SET FOREIGN_KEY_CHECKS=1;
查看当前FOREIGN_KEY_CHECKS的值
SELECT @@FOREIGN_KEY_CHECKS;

视图：

视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询。表是真正的存在与数据库中（也就是硬件介质上）的数据组合
例子：
普通查询语句：
select cust_name, cust_contact from customers, orders, orderitems
where customers.cust_id=orders.cust_id and
orders.order_num=orderitems.order_num and
orderitems.prod_id=‘TNT2’;

使用视图：
先建视图：
create view productcustomers as
select cust_name, cust_contact, prod_id from customers, orders, orderitems
where customers.cust_id=orders.cust_id and orderitems.order_num=orders.order_num;
使用视图进行查询：
select cust_name, cust_contact from productcustomers where prod_id=‘TNT2’;

尽量将视图用于检索，而不是更新，因为更新一个视图将更新其基表。
视图的好处：
1，重用sql语句；
2，简化复杂的sql操作。在编写查询后，可以方便的重用它而不必知道它的基本查询细节；
3，使用表的组成部分而不是整个表；
4，保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限；
5，更改数据格式和表示，视图可返回与底层表的表示和格式不同的数据；

索引：

索引优缺点：
优点：
可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

缺点：
当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。
创建索引和维护索引需要空间成本，每一条索引都要占据数据库的物理存储空间，数据量越大，占用空间也越大
创建索引和维护索引需要时间成本，这个成本随着数据量的增加而加大

索引操作：增加索引
ALTER TABLE emp
ADD INDEX name_index (NAME);
#删除索引
ALTER TABLE emp
DROP INDEX name_index;
#索引的存在：就是为了索引列作为条件时，提升查询效率
SELECT * FROM emp WHERE NAME = ‘关磊涛’;

为什么索引会增加速度，DB在执行一条Sql语句的时候，默认的方式是根据搜索条件进行全表扫描，遇到匹配条件的就加入搜索结果集合。如果我们对某一字段增加索引，查询时就会先去索引列表中一次定位到特定值的行数，大大减少遍历匹配的行数，所以能明显增加查询的速度。

存储过程、函数、触发器：

存储过程和函数的区别：
本质上没区别，执行本质都是一样的，只有在一些小地方有区别。
比如:
函数只能返回一个变量，存储过程可以返回多个。
函数可以嵌套多sql中使用，存储过程不行。
一般来说，存储过程实现的功能要复杂一点，而函数的实现的功能针对性比较强。

行级触发器与语句级触发器的区别：
行级触发器对DML语句影响的每一行都执行一次（DML是指insert、update、delete）
语句级触发器只对每个DML语句执行一次。
例如：
一个insert语句在一个表上插入500行，语句级触发器只会执行一次，而行级触发器会执行500次。

在触发器中禁止使用rollback、 commit、savepoint、create 、drop、alter 即在触发器中不能有DDL语句和DCL语句（DDL语句是指create、alter、drop DCL指grant授权、rollback、commit等）
触发器的类型：

存储过程和触发器可以互相调用

游标：

游标是系统开始的一个数据缓冲区 ，存放SQL语句执行的结果集。可以根据需求滚动或浏览其中的数据。

事务：

mysql中，并非所有的引擎都支持事务管理，比如常见的引擎myisam和innodb，前者就不支持事务，而后者支持。事务处理是一种机制，用来管理必须成批执行的mysql操作，以保证数据库不包含不完整的操作结果。利用事务处理，可以保证一组操作不会中途停止，它们或者作为整体执行，或者完全不执行。如果没有错误发生，整组语句提交给数据库表。如果发生错误，则进行回退，已恢复数据库到某个一直且安全的状态。
事务的特性ACID
1）原子性（Atomicity）原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
2）一致性（Consistency）一个事务中，事务前后数据的完整性必须保持一致。
3）隔离性（Isolation）多个事务，事务的隔离性是指多个用户并发访问数据库时， 一个用户的 事务不能被其它用户的事务所干扰，多个并发事务之间数据要相互隔离。
不考虑隔离性：
1.读脏数据
一个事务读取了另一个事务未提交的数据
假设Ａ向Ｂ转帐100元，对应sql语句如下所示
　　　　　　1.update account set money=money+100 where name=‘B’;
　　　　　　2.update account set money=money-100 where name=‘A’;
　　　 当第1条sql执行完，第2条还没执行(A未提交时)，如果此时Ｂ查询自己的帐户，就会发现自己多了100元钱。如果A等B走后再回滚，B就会损失100元。　
2.不可重复读
在一个事务中读取某一行数据，多次读取结果不一样
3.虚读
一个事务内读取到了别的事务插入的数据，导致前后读取不一致。
如丙存款100元未提交，这时银行做报表统计account表中所有用户的总额为500元，然后丙提交了，这时银行再统计发现帐户为600元了，造成虚读同样会使银行不知所措，到底以哪个为准
4）持久性（Durability）持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。

一般的mysql语句都是直接对数据库表执行和编写的，这就是所谓的隐含提交，也就是提交操作是自动进行的。但是，在事务处理中，必须使用commit语句进行明确的提交：

start transaction;
delete from orderitems where order_num = 20010;
delete from orders where order_num = 20010;
commit;

事务中的语句全部成功时，commit才会成功，如果上边第一条delete起作用，但第二条失败，则commit不会成功。当commit或rollback语句执行后，事务会自动关闭。

数据库完整性约束

1．域（列）完整性
是指表中的列必须满足某种特定的数据类型约束，其中约束又包括取值范围、精度等规定。
2．实体（行）完整性（有唯一主键）
实体完整性是对关系中的记录唯一性，也就是主键的约束。准确地说，实体完整性是指关系中的主键值不能为Null且不能有相同值。定义表中的所有行能唯一的标识,一般用主键,唯一索引 unique关键字,及identity属性比如说我们的身份证号码,可以唯一标识一个人.
3．参照完整性（外键是别的表的主键或者NULL）
参照完整性是对关系数据库中建立关联关系的数据表间数据参照引用的约束，也就是对外键的约束。准确地说，参照完整性是指关系中的外键必须是另一个关系的主键有效值，或者是NULL。参照完整性维护表间数据的有效性,完整性,通常通过建立外键联系另一表的主键实现,还可以用触发器来维护参考完整性
4 . 用户定义的完整性
不同的关系数据库系统根据其应用环境的不同，往往还需要一些特殊的约束条件。用户定义的完整性即是针对某个特定关系数据库的约束条件，它反映某一具体应用必须满足的语义要求。

三大范式：

第一范式：数据库表中的字段都是单一属性，不可分。即原子性。并且通俗解释：一个字段只存储一项信息.
eg:
不满足第一范式

学号	姓名	班级
0001	小红	高三1班

改成

0001 小红 高三 1班

学号	姓名	年级	班级
0001	小红	高三	1班

第二范式：消除部分依赖，即唯一性。（满足第一范式）
1、每一行数据具有唯一性，一般有主键就能做到
2、消除数据的部分依赖。

eg:
不满足第二范式：

拆表：

分析：第一张表中“快递单号”和“商品编号”构成主键，但’商品名称”和“单价”只和“商品编号”有关，这样就产生了“部分依赖” 于是拆成表二就消除了部分依赖。

第三范式：消除传递依赖，即独立性。（满足第二范式）

传递依赖：如果某一属性依赖于其他非主键属性，而其他非主键属性又依赖于主键，那么这个属性就是间接依赖于主键，这被称作传递依赖于主属性。
eg：
不符合第三范式：

Sno	Sname	Sex	Sdept	Sloc
1	小黑	男	软件	20栋
2	小白	女	经管	4栋

改成：

Sno	Sname	Sex	Sdept
1	小黑	男	软件
2	小白	女	经管

Deptno	Sdept	Sloc
1	软件	20栋
2	经管	4栋

鲍依斯-科得范式（BCNF）：在第三范式的基础上，不存在关键字段决定关键字段的情况。
eg:
反例：

StoreHouseID（仓库ID）	GoodsID（商品ID）	ManagerID（管理员ID）	GoodsNum（商品数量）
001	20130104	1	200

主键是
(仓库ID, 商品ID) →(管理员ID, 数量) 或
(管理员ID, 商品ID) → (仓库ID, 数量)，
(仓库ID, 商品ID)和(管理员ID,商品ID)都是StorehouseManage的候选关键字，表中的唯一非关键字段为数量
存在关键字段决定关键字段情况。
(仓库ID) → (管理员ID)
(管理员ID) → (仓库ID)
正解：
仓库管理表

StoreHouseID（仓库ID）	GoodsID（商品ID）	GoodsNum（商品数量）
001	20130104	200

仓库表

StoreHouseID（仓库ID）	ManagerID（管理员ID）
001	1

表连接：

表Websites:
±—±-------------±--------------------------±------±--------+
| id | name | url | alexa | country |
±—±-------------±--------------------------±------±--------+
| 1 | Google | https://www.google.cm/ | 1 | USA |
| 2 | 淘宝 | https://www.taobao.com/ | 13 | CN |
| 3 | 菜鸟教程 | http://www.runoob.com/ | 4689 | CN |
| 4 | 微博 | http://weibo.com/ | 20 | CN |
| 5 | Facebook | https://www.facebook.com/ | 3 | USA |
| 7 | stackoverflow | http://stackoverflow.com/ | 0 | IND |

表access_log:
±----±--------±------±-----------+
| aid | site_id | count | date |
±----±--------±------±-----------+
| 1 | 1 | 45 | 2016-05-10 |
| 2 | 3 | 100 | 2016-05-13 |
| 3 | 1 | 230 | 2016-05-14 |
| 4 | 2 | 10 | 2016-05-14 |
| 5 | 5 | 205 | 2016-05-14 |
| 6 | 4 | 13 | 2016-05-15 |
| 7 | 3 | 220 | 2016-05-15 |
| 8 | 5 | 545 | 2016-05-16 |
| 9 | 3 | 201 | 2016-05-17 |
±----±--------±------±-----------+
“Websites” 表中的 “id” 列指向 “access_log” 表中的字段 “site_id”。上面这两个表是通过 “site_id” 列联系起来的。
内连接：inner join on
SELECT Websites.id, Websites.name, access_log.count, access_log.date
FROM Websites INNER JOIN access_log
ON Websites.id=access_log.site_id;
结果:

外连接： 全外连接 full join 左外连接 left join 右外连接 right join
全外连接：
mysql不能用，oracle能用。 查询结果左右的数据，没有对应匹配数据填null。
左外连接：
左表数据都有，右表显示与左表匹配的数据，没有则显示null
右外连接：
右表数据都有，左表显示与右表匹配的数据，没有则显示null

自然连接：natural join
要求两表中进行比较的属性组是名称相同的属性组，在结果去去重（即：留下名字相同属性组的其中一组）。