SQL小知识（二）

设计数据库表的原则：

三少一多

数据库表越少越好，数据库表的字段个数越少越好，联合组建的字段个数越少越好

使用主键和外键越多越好

select中的执行顺序

from > where >group by >having >select >distinct >order by >limit

首先先通过 CROSS JOIN 求笛卡尔积，相当于得到虚拟表 vt（virtual table）1-1；
通过 ON 进行筛选，在虚拟表 vt1-1 的基础上进行筛选，得到虚拟表 vt1-2；
添加外部行。如果我们使用的是左连接、右链接或者全连接，就会涉及到外部行，也就是在虚拟表 vt1-2 的基础上增加外部行，得到虚拟表 vt1-3。
当然如果我们操作的是两张以上的表，还会重复上面的步骤，直到所有表都被处理完为止。这个过程得到是我们的原始数据。
当我们拿到了查询数据表的原始数据，也就是最终的虚拟表 vt1，就可以在此基础上再进行 WHERE 阶段。在这个阶段中，会根据 vt1 表的结果进行筛选过滤，得到虚拟表 vt2。
然后进入第三步和第四步，也就是 GROUP 和 HAVING 阶段。在这个阶段中，实际上是在虚拟表 vt2 的基础上进行分组和分组过滤，得到中间的虚拟表 vt3 和 vt4。
当我们完成了条件筛选部分之后，就可以筛选表中提取的字段，也就是进入到 SELECT 和 DISTINCT 阶段。
首先在 SELECT 阶段会提取想要的字段，然后在 DISTINCT 阶段过滤掉重复的行，分别得到中间的虚拟表 vt5-1 和 vt5-2。
当我们提取了想要的字段数据之后，就可以按照指定的字段进行排序，也就是 ORDER BY 阶段，得到虚拟表 vt6。
最后在 vt6 的基础上，取出指定行的记录，也就是 LIMIT 阶段，得到最终的结果，对应的是虚拟表 vt7。
当然我们在写 SELECT 语句的时候，不一定存在所有的关键字，相应的阶段就会省略。
同时因为 SQL 是一门类似英语的结构化查询语言，所以我们在写 SELECT 语句的时候，还要注意相应的关键字顺序，所谓底层运行的原理，就是我们刚才讲到的执行顺序。

视图

用户                        虚拟表              数据表

视图作为一张虚拟表，帮我们封装了很多底层与数据表的接口。他相当于是一张表或者多张表的数据结果集。视图可以帮我们简化复杂的sql查询，比如在编写视图后，我们可以直接重用它，而不需要考虑视图中包含的基础查询的细节。同样，我们也可以根据需要更改数据格式，返回与底层数据表格式不一样的数据。可以把经常查询的结果集放到虚拟表中提升使用效率。

创建视图：实际上就是在sql查询语句的基础上封装了视图，形成了一张虚拟表，可以直接作为表使用

CREATE VIEW view_name as  select column1,column2 
from table 
where condition

嵌套视图

create view view_name 
select id,height
from player
where condition >
(select AVG(height) from player_above_avg_height)

修改视图

ALTER VIEW view_name as 
select column1,column2
from table 
where condition

删除视图

DROP VIEW view_name

需要说明的是sqLite不支持试图修改，要修改智能先删除，再创建

视图的常见用法

利用视图进行数据的格式化

使用视图与计算字段

好处：

安全，可以针对不同的用户开房不同的数据权限，只给某个级别以上的人开房，其他人的视图则不展示对应的字段。虚拟表一般本身不存储数据，而且一般不会通过视图对底层数据进行修改。

简单清晰：视图是对sql的查询的封装，他可以将原本复杂的sql查询简化，编写好后我可以直接重用而不必知道具体的细节

储存过程：

和视图一样都是对sql代码进行封装，可以反复利用。同样清晰，安全，还可以减少网络传输量，不过视图是虚拟表，不对底层数据进行操作，而存储过程是程序化的sql,可以直接操作底层数据表。能够实现更为复杂的数据处理，存储过程可以说是有sql和流程语句构成的语句集合，他和我们之前学到的函数一样。可以接受输入参数，也可以返回输出参数给调用者，返回计算结果

#定义一个存储过程
CREATE PROCEDURE 存储参数（[参数列表]）
BEGAIN 
    需要执行的语句
END



#删除存储过程
drop procedure



#修改存储过程
alter procedure


DELIMITER ; 定义结束符 可以用 // 什么的都行



#3种参数类型
IN   不返回    向存储过程传入参数，存储过程中修改该参数的值，不能被返回
OUT    返回    把存储过程计算的结果放到该参数中，调用者可以得到返回值
INOUT  返回     把IN和OUT返回，既用于存储过程的传入参数，同时又可以把计算结果放到参数中，调用            
                者可以得到返回值



CREATE PROCEDURE `get_hero_scores`(
       OUT max_max_hp FLOAT,
       OUT min_max_mp FLOAT,
       OUT avg_max_attack FLOAT,  
       s VARCHAR(255)
       )
BEGIN
       SELECT MAX(hp_max), MIN(mp_max), AVG(attack_max) FROM heros WHERE role_main = s INTO max_max_hp, min_max_mp, avg_max_attack;
END

#调用

CALL get_hero_scores(@max_max_hp, @min_max_mp, @avg_max_attack, '战士');
SELECT @max_max_hp, @min_max_mp, @avg_max_attack;

流程控制语句：

BEGAIN ... END ;

DECLARE ; 声明变量 使用的位置位于BEGAIN ... END 语句中间，而且需要在其他语句使用之前进行变量的声明

SET ; 赋值语句，用于对变量进行赋值

SELECT .. INTO;把数据表中查出的结果放在变量中，也就是为变量赋值

流程控制语句
IF ... THEN ...ENDIF  

CASE when ... then ... else .. .end


LOOP 循环语句

LEAVE 跳出循环

ITERATE 进入下一循环


REPEAT ... UNTIL ... END REPEAT 循环语句，首先会执行一次循环 

WHILE ...DO...END WHILE 会先进行条件判断，如果满足条件就进行循环，不满足就退出循环

存储过程可以一次编译多次使用，存储过程只在创建时进行编译，之后的使用都不需要进行重新编译，提升了SQL的执行效率。但是可移植性差，存储过程不能跨数据库移植，换成其他数据库时要重新编写。其次调试困难，之友少数的额数据库支持存储过程的调试，对于复杂的存储过程拉斯说，开发和维护都不容易。版本管理困难，如索引发生变化，可能会导致存储过程失效。不适合高并发

事务：可以通过show  engines来查看当前Mysql的存储引擎都有哪些，以及是否支持事务

事务的常用控制语句：

START TRANSACTION  或者 BEGAIN  :显式的开启一个事物。

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

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

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

RELEASE SAVEPOINT :删除某个保存点

SET TRANSACTION 设置事务的隔离级别

事务有两种方式，分别为显式和隐式事务。隐式事务实际上是自动提交，ORACLE 默认不提供自动提交，需要手写COMMIT  命令。

手写COMMIT命令
set autocommit = 0; 关闭

set autocommit = 1; 开启

@@completion_type参数的作用：

completion = 0;默认情况，说明在执行commit的时候会提交事务，执行下一个事务的时候需要用START TRANSACTION或者ＢＥＧＡＩＮ　　来开启

completion = 1; 提交事务后，相当于执行了commit and chain 也就是开启了一个链式事务。即我们提交事务后会开启一个相同隔离级别的事务。

completion= 2，这种情况下 commit  = commit and release  当我们提交后，会自动与服务器断开连接。

四种隔离级别：

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

读已提交：只能读到已提交的内容可以避免脏读

可重复读：保证一个事物在相同的查询条件下得到的数据结果是一致的

可串行化：将事务串行化，在一个队列中按照顺序执行，但是牺牲了系统的并发性。

游标：

提供了一种灵活的操作方式，可以让我们从数据结果集中每次提取一条数据记录进行操作。游标让sql有了面向过程的编码方式。

游标是一种临时的数据库对象，可以指向存储在数据库表中的数据行指针。这里充当了指针的作用，我们可以通过操作游标来对数据行进行操作。

游标的使用：

1.定义游标。(select_statement代表查询语句)

MYSQL

DECLARE cursor_name SURSOR FOR select_statement

ORCAL

DECLARE sursor_name CURSOR IS select_statement

2.打开游标

OPEN cursor_name; 打开游标的时候，select 语句的查询结果集就会送到游标的工作区

3.从游标中取得数据

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

4,关闭游标

CLOSE cursor_name 关闭游标之后，我们就不能再检索查询结果集中的数据行，如果需要检索只能再次打开游标。

5.释放游标。

DEALLOCATE cursor_namec

不关闭游标就会一直存在于内存中，直到进程关闭。

当游标溢出后继续执行会报错。需要定义一个continue事件来指定这个事件发生时修改变量done的值，以此来判断游标是否溢出。

limit：

如果可以确定结果集只有一条，那么加上limit 1的时候，当找到一条结果的时候就不会继续扫描了，这样会加快查询速度。如果数据表已经对字段建立了唯一索引，不会对全表进行扫描的话，就不需要加上limit 1了。

通配符

_  匹配任意一个字符 ,%匹配大于等于0个任意字符。

单独的like '%'无法查出NULL值，比如 select * from heros where role_assist like '%'

ORDER BY加索引

在Mysql支持两种排序方式，分别是FileSort 和Index排序。在Index排序中，索引可以保证数据的有序性，不需要再进行排序，效率更高。而FileSort 排序占用CPU较多，如果待排结果较大，会产生临时文件I/O到磁盘排序的情况，效率较低。

在sql中，可以在where 子句和order  by中使用索引，目的是在where 子句中避免全表扫描，在order by子句中避免使用fileSort排序。

尽量使用Index完成order by 排序。如果where 和order by后面是相同的列就是用单索引列，如果不同就使用联合索引。

无法使用index时，需要对FileSort方式进行调优。

Order by 是对分的组排序，还是对分组中的记录排序？

order by 就是对记录进行排序，如果你在order by 前面用到了group by 实际上这是一种分组的聚合方式。已经把一组的数据合成了一条记录，在进行排序的时候，相当于对分的组进行了排序。

小表驱动大表应该使用exists，反之用IN.

 CREATE TABLE test(name varchar(255), PRIMARY KEY (name)) ENGINE=InnoDB;
 BEGIN;
 INSERT INTO test SELECT '关羽';
 BEGIN;
 INSERT INTO test SELECT '张飞';
 INSERT INTO test SELECT '张飞';
 ROLLBACK;
 SELECT * FROM test;

在Mysql中BEGAIN 用于开启事务，如果没有连续BEGAIN,当开启了第一个事务，还没有进行COMMIT提交时，会直接进行第二个事务的BEGAIN,这时数据库会隐式的COMMIT第一个事务，然后进入到第二个事务。