SQL必知必会小知识（一）

创建数据库的时候

utf8_general_ci，代表对大小写不敏感，如果设置为utf8_bin，代表对大小写敏感

AUTO_INCREMENT代表主键自动增长

UNIQUE INDEX（唯一索引），NORMAL INDEX（普通索引）。唯一索引和普通索引的区别在于它对字段进行了唯一性的约束

DEFAULT，表明了字段的默认值。如果在插入数据的时候，这个字段没有取值，就设置为 默认值。比如我们将身高 height 字段的取值默认设置为 0.00，即DEFAULT 0.00

 

设计数据表的原则：三少一多

用更少的表、更少的字段、更少的联合主键字段

可复用则是通过主键、外键的使用来增强数据表之间的复用率

 

通配符

SELECT name FROM heros WHERE name LIKE '%太%'

SELECT name FROM heros WHERE name LIKE '_%太%'

 

HAVING的使用

如何使用HAVING过滤分组，它与WHERE的区别是什么？

WHERE 是用于数据行，而 HAVING 则作用于分组。

对于分组的筛选，我们一定要用 HAVING，而不是 WHERE。另外你需要知道的是，HAVING 支持所有 WHERE 的 操作，因此所有需要 WHERE 子句实现的功能，你都可以使用 HAVING 对分组进行筛选。

 

创建视图

CREATE VIEW view_name AS 

SELECT column1, column2 

FROM table WHERE condition

view_name 为视图名称，column1、column2 代表列名， condition 代表查询过滤条件。

 

嵌套视图

当我们创建好一张视图之后，还可以在它的基础上继续创建视图，比如我们想在虚拟表 player_above_avg_height 的基础上，找到比这个表中的球员平均身高高的球员，作为新 的视图 player_above_above_avg_height，那么可以写成：

 

CREATE VIEW player_above_above_avg_height AS 

SELECT player_id, height 

FROM player

WHERE height > (SELECT AVG(height) from player_above_avg_height)

 

修改视图：ALTER VIEW

ALTER VIEW view_name AS 

SELECT column1, column2 

FROM table 

WHERE condition

 

删除视图：DROP VIEW

DROP VIEW view_name

 

视图小知识

 

创建一个存储过程

CREATE PROCEDURE 存储过程名称([参数列表])

BEGIN

    需要执行的语句

END

和视图一样，我们可以删除已经创建的存储过程，使用的是 DROP PROCEDURE。如果要 更新存储过程，我们需要使用 ALTER PROCEDURE

 

存储过程3种参数类型

 

事务的特性：ACID

A，原子性（Atomicity）。原子的概念就是不可分割，你可以把它理解为组成物质的基本单位，也是我们进行数据处理操作的基本单位。

C，一致性（Consistency）。一致性指的就是数据库在进行事务操作后，会由原来 的一致状态，变成另一种一致的状态。也就是说当事务提交后，或者当事务发生回滚后，数据库的完整性约束不能被破坏。

I，隔离性（Isolation）。它指的是每个事务都是彼此独立的，不会受到其他事务的执行影响。也就是说一个事务在提交之前，对其他事务都是不可见的。

D，持久性（Durability）。事务提交之后对数据的修改是持久性的，即使在系统出故障的情况下，比如系统崩溃或者存储介质发生故障，数据的修改依然是有效的。因为当事务完成，数据库的日志就会被更新，这时可以通过日志，让系统恢复到 最后一次成功的更新状态。

 

事务的常用控制语句

START TRANSACTION 或者 BEGIN，作用是显式开启一个事务。

COMMIT：提交事务。当提交事务后，对数据库的修改是永久性的。

ROLLBACK 或者 ROLLBACK TO [SAVEPOINT]，意为回滚事务。意思是撤销正在进行 的所有没有提交的修改，或者将事务回滚到某个保存点。

SAVEPOINT：在事务中创建保存点，方便后续针对保存点进行回滚。一个事务中可以存 在多个保存点。

RELEASE SAVEPOINT：删除某个保存点。

SET TRANSACTION，设置事务的隔离级别

 

事务两种方式：隐式事务和显式事务

隐式自动提交，显式不自动提交。

Oracle 默认不自动提交，需要手写 COMMIT 命令，而 MySQL 默认自动提交

 

autocommit

set autocommit =0;  //关闭自动提交

set autocommit =1;  //开启自动提交

 

事务并发处理可能存在的异常

脏读：读到了其他事务还没有提交的数据

不可重复读：对某数据进行读取，发现两次读取的结果不同，也就是说没有读到相同的 内容。这是因为有其他事务对这个数据同时进行了修改或删除。

幻读：事务 A 根据条件查询得到了 N 条数据，但此时事务 B 更改或者增加了 M 条符合 事务 A 查询条件的数据，这样当事务 A 再次进行查询的时候发现会有 N+M 条数据，产 生了幻读。

 

解决异常情况的4种隔离级别

读未提交，也就是允许读到未提交的数据，这种情况下查询是不会使用锁的，可能会产生脏 读、不可重复读、幻读等情况。

 

读已提交就是只能读到已经提交的内容，可以避免脏读的产生，属于 RDBMS 中常见的默认隔离级别（比如说 Oracle 和 SQL Server），但如果想要避免不可重复读或者幻读，就需要我们在 SQL 查询的时候编写带加锁的 SQL 语句

 

可重复读，保证一个事务在相同查询条件下两次查询得到的数据结果是一致的，可以避免不 可重复读和脏读，但无法避免幻读。MySQL 默认的隔离级别就是可重复读。

 

可串行化，将事务进行串行化，也就是在一个队列中按照顺序执行，可串行化是最高级别的 隔离等级，可以解决事务读取中所有可能出现的异常情况，但是它牺牲了系统的并发性。

 

游标的常用步骤

第一步，定义游标

DECLARE cursor_name CURSOR FOR select_statement

select_statement 代表的是 SELECT 语句

第二步，打开游标。

OPEN cursor_name

当我们定义好游标之后，如果想要使用游标，必须先打开游标。打开游标的时候 SELECT 语句的查询结果集就会送到游标工作区。

第三步，从游标中取得数据

FETCH cursor_name INTO var_name ...

这句的作用是使用 cursor_name 这个游标来读取当前行，并且将数据保存到 var_name 这 个变量中，游标指针指到下一行。如果游标读取的数据行有多个列名，则在 INTO 关键字 后面赋值给多个变量名即可。

第四步，关闭游标

CLOSE cursor_name

最后一步，释放游标。

有 DECLARE 就需要有 DEALLOCATE，DEALLOCATE 的作用是释放游标