SQL常见问题总结

总结一下常见问题。

一、概念性问题

1。事务：事务是一系列数据库操作，是数据库应用的基本逻辑单位。
事务的特性：ACID
原子性：事务要么全部被执行，要么就全部不被执行。
一致性：事务的执行使得数据库从一种正确状态转换成另一种正确状态
持久性：事务正确提交后，其结果将永久保存在数据库中，即使在事务提交后有了其他故障，事务的处理结果也会得到保存。
隔离性：在事务正确提交之前，不允许把该事务对数据的任何改变提供给任何其他事务
事务的回滚：ROLLBACK 指撤销指定的SQL语句和过程。事务运行的过程中发生了某种故障，事务不能继续执行，系统将事务中对数据库的所有已完成的操作全部撤销，滚回到事务开始时的状态。
事务的保留点：SAVEPOINT 指事务处理中设置的临时占位符，可以对它发布回退
事务的提交:COMMIT 指将未存储的SQL语句结果写入数据库表
2。视图：视图是从数据库的基本表中选取出来的数据组成的逻辑窗口，它是一个虚表，它包含的不是数据而是根据需要检索数据的查询。视图定义了一种封装SELECT语句的层次，可用来简化数据处理，重新格式化或保护基础数据。在数据库中，存放的只是视图的定义而已，不存放视图包含的数据，这些视图仍存放在原来的基本表结构中。
视图的实质就是查询语句的封装代替一大串sql语句。
3。索引：索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。
索引的几种类型（SQL SERVER）：

唯一索引：唯一索引不允许两行具有相同的索引值
主键索引：为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的，并且不能为空
聚集索引(Clustered)：表中各行的物理顺序与键值的逻辑（索引）顺序相同，每个表只能有一个
非聚集索引(Non-clustered)：非聚集索引指定表的逻辑顺序。数据存储在一个位置，索引存储在另一个位置，索引中包含指向数据存储位置的指针。可以有多个，小于249个

优点：加快搜索速度
缺点：降低表的更新速度，因为数据库不仅要更新数据，还要保存索引文件
4。存储过程：带有逻辑的SQL语句。
特点：1）执行效率非常快！存储过程是在数据库的服务器端执行的
2）可以降低网络通信量，提高通信速率
3）移植性很差！不同数据库的存储过程是不能移植
存储过程的写法：

DELIMITER $                         -- 声明存储过程的结束符CREATE PROCEDURE pro_test()         --存储过程名称(参数列表)BEGIN                               -- 开始    -- 可以写多个sql语句;             -- sql语句+流程控制    SELECT * FROM employee;END $                                -- 结束 结束符-- 执行存储过程CALL pro_test();                       -- CALL 存储过程名称(参数);

5。约束：数据库约束是为了保证数据的完整性(正确性)而实现的一套机制
约束类型：
1）主键约束——实体完整性（非空、唯一）
2）外键约束——参照完整性（涉及两个表）

CREATE TABLE employee(    id INT PRIMARY KEY,    empName VARCHAR(20),    deptId INT,-- 把部门名称改为部门ID    -- 声明一个外键约束    CONSTRAINT emlyee_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id)    --           外键名称                  外键               参考表(参考字段))

注意：
(1)被约束的表称为副表，约束别人的表称为主表，外键设置在副表上的！！！
(2)主表的参考字段通用为主键！
(3)添加数据： 先添加主表，再添加副表
(4)修改数据： 先修改副表，再修改主表
(5)删除数据： 先删除副表，再删除主表

3）检查约束——用户自定义完整性（限制表中某一列或某些列中可接受的数据值或数据格式，它用于限制列的取值范围）
4）唯一约束 UNIQUE
5）非空约束 NOT NULL
6)默认值约束DEFAULT
6。主键和外键：主键是数据库中的一个或多个字段，是表中记录的唯一标识符，主键不能为空，一个表中只能有一个主键，主键列可以是一列或多列，主键可唯一的标识一行；外键表示的是两个关系之间的联系，如果两个表中的公共关键字是一个表的主键，那么这个公共关键字就是另一个表的外键。或者说在一个表中存在的另一个表的主键称此表的外键
7。数据库存储结构：数据库-》表-》数据

SQL语句的分类：
DDL： 数据定义语言
create / drop / alter
DML：数据操作语句
insert / delete /update / truncate
DQL： 数据查询语言：
select / show
8。内连接和外连接：
内连接是保证两个表中所有的行都要满足连接条件

SELECT empName,deptName    FROM employee    INNER JOIN dept    ON employee.deptId=dept.id;

外连接只限制其中一个表的行，而不限制另一个表的行。分左连接、右连接、全连接三种
如果是左连接，左边表的数据一定会显示，右边表的数据如果不匹配就会变成NULL

SELECT d.deptName,e.empName    FROM dept d    LEFT OUTER JOIN employee e    ON d.id=e.deptId;

9。关于范式：
函数依赖：
如果一个表中某一个字段Y的值是由另外一个字段或一组字段X的值来确定的，就称为Y函数依赖于X。

第一范式就是原子性，字段不可再分割； 第二范式就是完全依赖，没有部分依赖； 第三范式就是没有传递依赖。
第一范式：
定义：如果关系模式R的每个关系r的属性都是不可分的数据项，那么就称R是第一范式的模式。
1NF是关系模式应具备的最起码的条件，如果数据库设计不能满足第一范式，就不称为关系型数据库。关系数据库设计研究的关系规范化是在1NF之上进行的。
第二范式
定义：如果关系模式R是1NF，且每个非主属性完全函数依赖于候选键，那么就称R是第二范式。
每个非主属性是由整个主键函数决定的，而不能由主键的一部分来决定。
第三范式
定义：如果关系模式R是2NF，且关系模式R（U，F）中的所有非主属性对任何候选关键字都不存在传递依赖，则称关系R是属于第三范式
也就是说：首先要满足第二范式，其次要使得非主属性之间不存在函数依赖。

10、触发器：sql server触发器详解
触发器是一种特殊类型的存储过程
触发器的执行由事件触发，对一个表进行操作（ insert，delete， update）时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等

SqlServer包括三种常规类型的触发器：DML触发器、DDL触发器和登录触发器。

1.DML(数据操作语言,Data Manipulation Language)触发器

DML触发器是一些附加在特定表或视图上的操作代码，当数据库服务器中发生数据操作语言事件时执行这些操作。
1、 after触发器（之后触发）
只能在表上定义触发器
a、 insert触发器
b、 update触发器
c、 delete触发器
这些类型的触发器对于监视发生在数据库表格里的数据变化十分好用。
2、 instead of 触发器 （之前触发）
种类和after触发器一样，如果在 Emplyees 表上有一个 INSTEAD OF UPDATE 触发器和在这个表上执行一条 UPDATE 语句，结果是这条 UPDATE 语句并不会改变 Employee 表中的任何一行。语句只是为了触发这个触发器。 既可以在表上定义instead of触发器，也可以在视图上定义。

当遇到下列情形时，应考虑使用DML触发器：
通过数据库中的相关表实现级联更改
防止恶意或者错误的insert、update和delete操作，并强制执行check约束定义的限制更为复杂的其他限制。
评估数据修改前后表的状态，并根据该差异才去措施。
2.DDL(数据定义语言,Data Definition Language)触发器

  DDL触发器是当服务器或者数据库中发生数据定义语言(主要是以create,drop,alter开头的语句)事件时被激活使用，使用DDL触发器可以防止对数据架构进行的某些更改或记录数据中的更改或事件操作。

3.登录触发器

  登录触发器将为响应 LOGIN 事件而激发存储过程。与 SQL Server 实例建立用户会话时将引发此事件。登录触发器将在登录的身份验证阶段完成之后且用户会话实际建立之前激发。因此，来自触发器内部且通常将到达用户的所有消息（例如错误消息和来自 PRINT 语句的消息）会传送到 SQL Server 错误日志。如果身份验证失败，将不激发登录触发器。