MySQL高级部分全集_只有当表类型为myisam、innodb或bdb时,才可以向有null、blob或text列中添加索

Mysql数据模型 MySQL数据优化 MySQL安全性

一、函数

一.字符函数

select LOWER('SQL Course')//转小写
select UPPER('SQL Course')//转大写
select CONCAT('Hello', 'World')//拼接
select SUBSTR('HelloWorld',1,5)//截取
select LENGTH('HelloWorld')//长度
select INSTR('HelloWorld', 'W')//字符出现索引值
select TRIM('H' FROM 'HelloWorld')//字符截取后半段
select REPLACE('abcd','b','m')//字符替换
select LPAD('salary',10,'*')****salary
select RPAD ('salary', 10,'*')salary****
1
2
3
4
5
6
7
8
9
10

二、数字函数

#round 四舍五入
	SELECT ROUND(-1.55);  
	SELECT ROUND(1.567,2); 
#ceil 向上取整,返回>=该参数的最小整数
	SELECT CEIL(-1.02);
#floor 向下取整，返回<=该参数的最大整数
	SELECT FLOOR(-9.99);
#TRUNCATE: 截断
	TRUNC(45.926, 2) 45.92
#MOD: 求余
	MOD(1600, 300) 100
1
2
3
4
5
6
7
8
9
10
11

三、日期

//获取系统当前时间
select now()
//将日期格式的字符转换成指定格式的日期
select STR_TO_DATE('9-13-1999','%m-%d-%Y')
//将日期转换成字符
select DATE_FORMAT(‘2018/6/6’,‘%Y年%m月%d日’)
#curdate 返回当前系统日期，不包含时间
 SELECT CURDATE()
 #curtime 返回当前时间，不包含日期
 SELECT CURTIME();
1
2
3
4
5
6
7
8
9
10

四、逻辑控制函数

#if函数： if else 的效果
SELECT IF(10<5,'大','小');
#ifNull函数
SELECT IFNULL(NULL, "RUNOOB");RUNOOB
#流程控制表达式： case-when-then
select case price when price<100 then '便宜'
when price>=100 and price <1000 then '优惠'
when price > 1000 then '昂贵'
else '其他'  end as '价格' form foodtable; 
#VERSION：获取当前数据库版本
	SELECT VERSION();
#DATABASE: 获取当前数据库的数据库名
	SELECT DATABASE();
#USER:获取当前数据库用户名
	SELECT USER();
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

五、分组函数

#简单 的使用
SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT SUM(salary) 和,AVG(salary) 平均,MAX(salary) 最高,MIN(salary) 最低,COUNT(salary) 个数 FROM employees;
#参数支持哪些类型
SELECT SUM(last_name) ,AVG(last_name) FROM employees;
SELECT SUM(hiredate) ,AVG(hiredate) FROM employees;
SELECT MAX(last_name),MIN(last_name) FROM employees;
SELECT MAX(hiredate),MIN(hiredate) FROM employees;
SELECT COUNT(commission_pct) FROM employees;
SELECT COUNT(last_name) FROM employees;
#是否忽略null
SELECT SUM(commission_pct) ,AVG(commission_pct),SUM(commission_pct)/35,SUM(commission_pct)/107 FROM employees;
SELECT MAX(commission_pct) ,MIN(commission_pct) FROM employees;
SELECT COUNT(commission_pct) FROM employees;
#和distinct搭配
SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;
SELECT COUNT(DISTINCT salary),COUNT(salary) FROM employees;
#count函数的详细介绍
#效率：MYISAM存储引擎下  ，COUNT(*)的效率高INNODB存储引擎下，COUNT(*)和COUNT(1)的效率差不多，比COUNT(字段)要高一些
SELECT COUNT(salary) FROM employees;
SELECT COUNT(*) FROM employees;
SELECT COUNT(1) FROM employees;
#和分组函数一同查询的字段有限制
SELECT AVG(salary),employee_id  FROM employees;
#去重复distinct关键字的使用
SELECT distinct name salary FROM employees;
#先进行条件筛选 where 后分组 group by
SELECT classid FROM student WHERE sex="男"  GROUP BY classid
#在查询student之前需要考虑 到底是先分组还是先筛选条件先筛选 where 后分组 group by想分组 group by 后筛选 having
HAVING AVG(chinese)=79

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

二、多表查询

嵌套子查询
使用IN和NOT IN 完成子查询
使用EXISTS 和 NOT EXISTS 完成子查询
使用SOME ANY ALL进行子查询
聚合技术
使用COMPUTE和COMPUTE BY进行汇总查询
排序函数
ROW_NUMBER函数
公式表表达式
类似于C#中的方法，让复杂的SQL语句操作更简单
1
2
3
4
5
6
7
8
9
10

一、多表查询

#笛卡尔集
select * from dept, emp;
#等值连接
SELECT beauty.id, NAME ,boyname FROM beauty , boys
WHERE beauty.`boyfriend id‘=boys . id;
#非等值连接
SELECT salary,grade_level
FROM employees e,job_grades g
WHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal`;
#自连接：自身与自身连接查询，也是属于等值查询，只是涉及一张表
SELECT .employee_id,e.last_name,m.employee_id,m.last_name
FROM employees e,employees m
WHERE e.`manager_id`=m.`employee_id`;

1
2
3
4
5
6
7
8
9
10
11
12
13
14

二、join on 连接查询

/*
select 查询列表
	from 表1 别名 【连接类型】 join 表2 别名 
	on 连接条件	
	【where 筛选条件】
	【group by 分组】
	【having 筛选条件】
	【order by 排序列表】

*/
#内连接
SELECT employee_id, city, department_name
FROM employees e 
Inner JOIN departments d
ON d.department_id = e.department_id 
Inner JOIN locations l
ON d.location_id = l.location_id;
#左外连接查询：在内连接查询的基础上，加上左边表有而右边表没有的记录
select * from dept d left join emp e on e.dept_id=d.id;
#右外连接查询：在内连接查询的基础上，加上右边表有而左边表没有的记录
select * from dept d right join emp e on e.dept_id=d.id;
#全外连接查询：在内连接查询的基础上，加上左边表有而右边表没有的记录 和 右边表有而左边表没有的记录，通过union关键字来模拟全外连接查询
select * from dept left join emp on emp.dept_id = dept.id
union
select * from dept right join emp on emp.dept_id = dept.id;
#交叉连接
select * from TestA a cross join TestB b 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

三、子查询

#单行单列子查询
--查询公司之中工资最低的雇员的完整信息
SELECT * FROM emp e WHERE e.sal=(SELECT MIN(sal)  FROM emp);
-- 查询出基本工资比ALLEN低的全部雇员信息
SELECT * FROM emp e WHERE e.sal<( SELECT sal  FROM emp  WHERE ename='ALLEN');
#单行多列子查询。
--查找出与ALLEN从事同一工作，并且基本工资高于雇员编号为7521的全部雇员信息,
SELECT *FROM emp e WHERE e.job=(SELECT job FROM emp  WHERE ename='ALLEN') AND e.sal>( SELECT sal FROM emp  WHERE empno=7521);
 --查询与雇员7566从事同一工作且领导相同的全部雇员信息
SELECT *FROM emp e WHERE (e.job,e.mgr) =
(SELECT job,mgr FROM emp WHERE empno=7566);

#多行单列子查询     主要使用三种操作符：IN、ANY（> ,>=最小值  <、<=最大值  =任意值）、ALL(> ,>=最大值 <、<=最小值  <>、!=任意值)SOME(> ,>=最小值 <、<=最大值  =任意值)

#SOME找set1中有哪些元素，刚好等于集合set2中的元素呢 
select * from set1 where number = some(select * from set2)
--查询出与每个部门中最低工资相同的全部雇员信息
SELECT *FROM emp e WHERE e.sal IN
(SELECT MIN(sal)FROM empGROUP BY deptno);
--查询出不与每个部门中最低工资相同的全部雇员信息
SELECT *FROM emp e
WHERE e.sal 
NOT IN(SELECT MIN(sal)FROM empGROUP BY deptno);
--查询出每个部门经理的工资
SELECT  * 
FROM emp WHERE sal = ANY ( SELECT MIN (sal)
FROM emp WHERE job='MANAGER'GROUP BY deptno);
--查询出每个部门不等于经理的工资 !=(或<>)ALL不等于子查询中所有值
SELECT  * FROM emp WHERE sal <> ALL (
SELECT MIN (sal)FROM emp
WHERE job='MANAGER'GROUP BY deptno);
#子查询作为临时表使用
SELECT FoodName,Price,SalesVo,AddDate,f2.FoodName
FROM FoodName f1
inner join(SELECT  * FROM FoodTypeTable) f2
on f1.FoodTypeID=f2.FoodtypeID
#子查询作为列使用
SELECT FoodName,Price,SalesVo,AddDate,
(SELECT FoodTypeName from FoodTypeTable f2
where f1.FoodTypeID=f2.FoodTypeID),FoodTypeName
from FoodTable
# IN 和NOT IN 通常在WHERE 子句中使用，在IN 和NOT IN后接的子查询中，可以有多个值出现,NOT IN 的用法与IN 一样，唯一的区别就是意义相反
#查询菜品编号为1和3的菜品信息
SELECT FoodName,Price,SalesVo,AddDate from FoodTable where FoodID in(1,3)
#使用in子查询 查询包含范围的数据
select FoodName, Price, SalesVolume, AddDate 
from FoodTable f1where FoodTypeID 
in(select FoodTypeID from FoodTypeTable where FoodTypeID = 4)
#使用Exists子查询 验证是否包含成功会返回数据，不成功则什么数据都不会返回
#在语句中会判断EXISTS 和 NOT EXISTS 后接的子句是否存在和是否不存在
select * from FoodTypeTable  f1 Where Exists
( SELECT * from FoodTable f2 
where f1.FoodTypeID = f2.FoodTypeID and Price>50)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53

四、聚合技术

#GROUP BY子句有个缺点，就是返回的结果集中只有合计数据，而没有原始的详细记录。
#COMPUTE BY 子句使您得以用同一 SELECT 语句既查看明细行，又查看汇总行，可以计算子组的汇总值，也可以计算整个结果集的汇总值。
Select 列名1，列名2……列名n from 表名 order by列名1 compute by] 函数名（列名2） 
 select type,price from titles  order by type compute avg(price) 

1
2
3
4
5

五、排序

#row_number()数据库自带一个排序函数  统将自动生成一个排序列。 注意：单价相同时，排名是不同的，而且排名是连续的
#语法：row_number()  over（子句）
--系统将自动生成一个排序列。注意：单价相同时，排名是不同的，而且排名是连续的
select row_number() over(order by Price desc) id ,* from FoodTable 
#分组排序:使用partition by分组子句
--注意分组子句可不止跟Row_number()排序函数使用。
--实现步骤1：使用排序函数Row_number实现按照价格从高到低排序
select row_number() over(order by Price desc) as id,* from FoodTable
--实现步骤2：使用分组子句将普通排序改成分组排序
select row_number() over(partition by FoodTypeID  order by Price desc) as id, * from FoodTable
#rank()数据库自带一个排序函数
--语法：rank() ： over（子句）
--rank函数在over子句中排序字段值相同的情况下，排名是相同的，而且排名是不连续的。
SELECT rank()over(ORDER BY Price)AS id,* FROM FoodTable
1
2
3
4
5
6
7
8
9
10
11
12
13
14

六、公式表表达式

#With...As...语句创建
#问题：查询学员与其相应的成绩
-- WITH 创建方法名 AS 后则是方法体
--公式表表达式
--在一个批中建立一个临时表
WITH StuInfo_Marks 
AS(SELECT S1.StuID,S1.StuName,S2.Subject,  S2.Score 
   FROM StuInfo S1,StuMarks S2
   WHERE S1.StuID=S2.StuID)
#调用   
SELECT * FROM StuInfo_Marks
1
2
3
4
5
6
7
8
9
10
11

三、索引视图

一、索引

1.索引作用
--快速读取数据；
--保证数据记录的唯一性；
--实现表与表之间的参照完整性；
--在使用GROUP BY、ORDER BY子句进行数据检索时，利用索引可减少排序和分组的间。
--只有当表类型为MyISAM、InnoDB时，才可以向有NULL、BLOB或TEXT列中添加索引。
--一个表最多可有16个索引。最大索引长度是256个字节。
--对于CHAR和VARCHAR列，可以索引列的前缀。这样索引的速度更快并且比索引整个列需要较少的磁盘空间。
--MySQL能在多个列上创建索引。
2.索引分类
--1．普通索引（INDEX）这是最基本的索引类型，它没有唯一性之类的限制。创建普通索引的关键字是INDEX。
--2．唯一性索引（UNIQUE）这种索引和前面的普通索引基本相同，但有一个区别：索引列的所有值都只能出现一次，即必须是唯一的。创建唯一性索引的关键字是UNIQUE。
--3．主键（PRIMARY KEY）主键是一种唯一性索引，它必须指定为“PRIMARY KEY”。主键一般在创建表的时候指定，也可以通过修改表的方式加入主键。但是每个表只能有一个主键。
--4．全文索引（FULLTEXT）MySQL支持全文检索和全文索引。在MySQL中，全文索引的索引类型为FULLTEXT。全文索引只能在VARCHAR或TEXT类型的列上创建，并且只能在MyISAM表中创建。
--5．哈希索引（HASH）当表类型为MEMORY或HEAP时，除了BTREE索引，MySQL还支持哈希索引（HASH）。使用哈希索引，不需要建立树结构，但是所有的值都保存在一个列表中，这个列表指向相关页和行。
3.索引的创建时机
#适合创建索引的列
--主键自动建立唯一索引；
--频繁作为查询条件的字段应该创建索引 
--查询中与其它表关联的字段，外键关系建立索引
--单键/组合索引的选择问题， 组合索引性价比更高 
--查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度 
--查询中统计或者分组字段
--当数据表中的某一列被频繁的用于数据搜索时，或者该列用于对数据进行排序时可以创建成索引。
#不适合创建索引的列
--表记录太少；
-- 经常增删改的表或者字段 
--Where 条件里用不到的字段不创建索引
--过滤性不好的不适合建索引 
--如果列中仅有几个不同的值，或者表中仅包含几行值，则不推荐为其创建索引。为小型表创建索引可能不太划算，因为对数据量较小的表来说，在MySql中使用索引搜索数据所花的时间比直接进行表扫描所花的时间更长。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

外键添加
CONSTRAINT `外键名称` FOREIGN KEY (`主表名`) REFERENCES `外表名称`（ID）
索引创建语法
--CREATE [ UNIQUE ] [ FULLTEXT | SPATIAL]索引类型
--INDEX|KEY   index_name索引名字
--ON table_name (index_col_name…)索引位置
1.普通索引
--所表一起创建:
CREATE TABLE customer (
id INT(10) UNSIGNED AUTO_ INCREMENT ,
customer_no VARCHAR(200),
customer_name VARCHAR(200),
PRIMARY KEY(id)，
KEY (customer_ name));
--单独建单值索引: 
CREATE INDEX idx_ customer_ name ON customer(customer_ name);

2.唯一索引
--概念:索引列的值必须唯一，但允许有空值
--随表一起创建:
CREATE TABLE customer (
id INT(10) UNSIGNED AUTO_INCREMENT ,
customer_no VARCHAR(200),
customer_name VARCHAR(200),
PRIMARY KEY(id),
KEY (customer_name),
UNIQUE (customer_no));
--单独建唯-索引:
CREATE UNIQUE INDEX idx__customer_no ON customer(customer_no);

3.主键索引
CREATE TABLE customer (
id INT(10) UNSIGNED AUTO_INCREMENT ,
customer_no VARCHAI(200),
customer_nameVARCHAR(200),
PRIMARY KEY(id) );
--单独建主键索引:
ALTER TABLE customer add PRIMARY KEY customer(customer_ _no);
--删除建主键索引:
ALTER TABLE customer drop PRIMARY KEY ;
--修改建主键索引:
--必须先删除掉(drop)原索引，再新建(add)索引
4.复合索引
--概念:即一个索引包含多个列
--随表一起建索引:
CREATE TABLE customer (
id INT(10) UNSIGNED AUTO_INCREMENT ,
customer_no VARCHAR(200),
customer_name VARCHAR(200),
PRIMARY KEY(id),
KEY (customer_name), 
UNIQUE (customer_name),
KEY (customer_no,custome _name));
--单独建索引: .
CREATE INDEX idx_no_name ON customer(customer_no,customer_name);

5.基本语法
创建  CREATE [UNIQUE ] INDEX [indexName] ON table_name(column))
删除 DROP INDEX [indexName] ON mytable;
查看 SHOW INDEX FROM table_name\G
使用Alter命令 ALTER TABLE tbl_name ADD PRIMARY KEY(column_lit) 该语句添加一一个主键，这意味着索引值必须是唯一的，且不能为NULL.
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list)
ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出现多次。
ALTER TABLE tbl_name ADD FULL TEXT index_name (column_list):该语句指定了索引为FULLTEXT ,用于全文索引。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65

二、性能优化

1.使用索引查询
--1.如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)注意：要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引
-- 2.对于多列索引，不是使用的第一部分，则不会使用索引
-- 3.like查询是以%开头
-- 4.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
-- 5.条件左边使用函数，如 left(name,2) == 'AA';
2.sql优化操作
-- 1、在表中建立索引，优先考虑where、group by使用到的字段。
-- 2、尽量避免使用select *，返回无用的字段会降低查询效率。如下：	
-- 	SELECT * FROM t 
-- 优化方式：使用具体的字段代替*，只返回使用到的字段。
-- 3、尽量避免使用in 和not in，会导致数据库引擎放弃索引进行全表扫描。如下：
 	SELECT * FROM t WHERE id IN (2,3)
 	SELECT * FROM t1 WHERE username IN (SELECT username FROM t2)
--  优化方式：如果是连续数值，可以用between代替。如下：
 	SELECT * FROM t WHERE id BETWEEN 2 AND 3
-- 如果是子查询，可以用exists代替。如下：
 	SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t2 WHERE t1.username = t2.username)
--4、尽量避免使用or，会导致数据库引擎放弃索引进行全表扫描。如下：
	SELECT * FROM t WHERE id = 1 OR id = 3
--优化方式：可以用union代替or。如下：
	SELECT * FROM t WHERE id = 1
	UNION
	SELECT * FROM t WHERE id = 3
--（PS：如果or两边的字段是同一个，如例子中这样。貌似两种方式效率差不多，即使union扫描的是索引，or扫描的是全表）
--5、尽量避免在字段开头模糊查询，会导致数据库引擎放弃索引进行全表扫描。如下：
	SELECT * FROM t WHERE username LIKE '%li%'
   --优化方式：尽量在字段后面使用模糊查询。如下：
	SELECT * FROM t WHERE username LIKE 'li%'
--6、尽量避免进行null值的判断，会导致数据库引擎放弃索引进行全表扫描。如下：
	SELECT * FROM t WHERE score IS NULL
   --优化方式：可以给字段添加默认值0，对0值进行判断。如下：
	SELECT * FROM t WHERE score = 0
.--7、尽量避免在where条件中等号的左侧进行表达式、函数操作，会导致数据库引擎放弃索引进行全表扫描。如下：
	SELECT * FROM t2 WHERE score/10 = 9
	SELECT * FROM t2 WHERE SUBSTR(username,1,2) = 'li'
--优化方式：可以将表达式、函数操作移动到等号右侧。如下：
	SELECT * FROM t2 WHERE score = 10*9
	SELECT * FROM t2 WHERE username LIKE 'li%'

3.Sql优化的规则
--在MySQL中可以使用连接（JOIN）查询来替代子查询
--1.不要有超过5个以上的表连接（JOIN）
--2.连接查询不需要建立临时表，其速度比子查询要快，如果查询中使用到索引的话，性能会更好
--3.连接之所以更有效率，是因为MySQL不需要在内存中创建临时表来完成查询工作。
--4.考虑使用临时表或表变量存放中间结果。
--5.少用子查询
--6.视图嵌套不要过深,一般视图嵌套不要超过2个为宜
--7.使用EXPLAIN或者DESCRIBE检查select 语句进行性能分析
--8.避免索引失效
--9.子查询尽量使用join查询
解释:
-- 连接的表越多，其编译的时间和连接的开销也越大，性能越不好控制。
-- 最好是把连接拆开成较小的几个部分逐个顺序执行。
-- 优先执行那些能够大量减少结果的连接。
-- 拆分的好处不仅仅是减少SQL Server优化的时间，更使得SQL语句能够以你可以预测的方式和顺序执行。
-- 如果一定需要连接很多表才能得到数据，那么很可能意味着设计上的缺陷。
-- 连接是outer join，非常不好。因为outer join意味着必须对左表或右表查询所有行。
-- 如果表很大而没有相应的where语句，那么outer join很容易导致table scan或index scan。
-- 要尽量使用inner join避免scan整个表。
 4.Sql查询缓慢的原因
--    1.数据量过大
--    2.表设计不合理
-- 　　3.sql语句写得不好
-- 　　4.没有合理使用索引
-5.针对SQL语句的优化　　
-- 1.查询语句中不要使用 *
-- 2.尽量减少子查询，使用关联查询（left join,right join,inner  join）替代
-- 3.减少使用IN或者NOT IN ,使用exists，not exists或者关联查询语句替代
-- 4.or 的查询尽量用 union或者union all 代替　(在确认没有重复数据或者不用剔除重复数据时，union all会更好)
-- 5.合理的增加冗余的字段（减少表的联接查询）
--6.增加中间表进行优化（这个主要是在统计报表的场景，后台开定时任务将数据先统计好，尽量不要在查询的时候去统计）
--7.建表的时候能使用数字类型的字段就使用数字类型（type,status...），数字类型的字段作为条件查询比字符串的快
--8.那些可以过滤掉最大数量记录的条件必须写在WHERE子句的最末尾
--9.合理使用like模糊查询
1、 select * from student where name like '%姜小鱼%' --会造成全表扫描
2、 select * from student where name like '姜小鱼%' --不会造成全表扫描　　
--10.where子句使用 ！= 或 <> 操作符优化在where子句中使用 ！= 或 <>操作符，索引将被放弃使用，会进行全表查询
1 SELECT id FROM A WHERE ID != 5             --会造成全表扫描
2 SELECT id FROM A WHERE ID>5 OR ID<5        --不会造成全局表描
--12.where子句中使用 IS NULL 或 IS NOT NULL 的优化 在where子句中使用 IS NULL 或 IS NOT NULL 判断，索引将被放弃使用，会进行全表查询。
1、 SELECT id FROM A WHERE num IS NULL --会造全表扫描
2、 SELECT id FROM A WHERE num=0 --优化成num上设置默认值0，确保表中num没有null
--13.where子句使用or的优化 很多时候使用union all 或 nuin(必要的时候)的方式替换“or”会得到更好的效果。where子句中使用了or,索引将被放弃使用。
1. SELECT id FROM A WHERE num =10 or num = 20   　　--索引失效
2. SELECT id FROM A WHERE num = 10 union all SELECT id FROM A WHERE num=20    --优化后
--14.where子句使用IN 或 NOT IN的优化in和not in 也要慎用，否则也会导致全表扫描。
　方案一：between替换in
1. SELECT id FROM A WHERE num in(select num from b ) --会造成全表扫描
2 .SELECT num FROM A WHERE num exists(select 1 from B where B.num = A.num)    --不会造成全表扫描　
 方案二：exist替换in
1. SELECT id FROM A WHERE num in(select num from b ) --会造成全表扫描
2. SELECT num FROM A WHERE num exists(select 1 from B where B.num = A.num)    --不会造成全表扫描
　方案三：left join替换in
1. SELECT id FROM A WHERE num in(select num from B)   --会造成全表扫描2 、
2 .SELECT id FROM A LEFT JOIN B ON A.num = B.num    --不会造成全表扫描　
--15.where子句中对字段进行表达式操作的优化
--不要在where子句中的“=”左边进行函数、算数运算或其他表达式运算，否则系统将可能无法正确使用索引。
1 SELECT id FROM A WHERE num/2 = 100   --会造成索引失效
2 SELECT id FROM A WHERE num = 100*2    --优化后
3 SELECT id FROM A WHERE substring(name,1,3) = 'abc' --会造成索引失效
4 SELECT id FROM A WHERE LIKE 'abc%'  --优化后
5 SELECT id FROM A WHERE datediff(day,createdate,'2016-11-30')=0　　--会造成索引失效
6 SELECT id FROM A WHERE createdate>='2016-11-30' and createdate<'2016-12-1'　　--不会造成索引失效  　　
7.SELECT id FROM A WHERE year(addate) <2016　　　　--会造成索引失效
8.SELECT id FROM A where addate<'2016-01-01'　　--不会造成索引失效
--16使用“临时表”暂存中间结果
　　　--采用临时表暂存中间结果好处：
　　　　（1）避免程序中多次扫描主表，减少程序执行“共享锁”阻塞“更新锁”，减少了阻塞，提高了并发性能。
　　　　（2）尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。
　　　　（3）避免频繁创建和删除临时表，以减少系统资源的浪费。
　　　　（4）尽量避免向客户端返回大数据量，若数据量过大，应考虑相应需求是否合理。
　　　　
--17.limit分页优化 当偏移量特别时，limit效率会非常低
1 SELECT id FROM A LIMIT 1000,10   --超级快
2 SELECT id FROM A LIMIT 90000,10 --特别慢
方法一：
select id from A order by id limit 90000,10;-- 很快，0.04秒就OK。因为用了id主键做索引当然快
方法二：
select id,title from A where id>=(select id from collect order by id limit 90000,1) limit 10;
方法三：
select id from A order by id  between 10000000 and 10000010;
--18.批量插入优化
1 .INSERT into person(name,age) values('A',14)
2 .INSERT into person(name,age) values('B',14)
3 .INSERT into person(name,age) values('C',14)　
可优化为：
INSERT into person(name,age) values('A',14),('B',14),('C',14)
--21.尽量不要使用 BY RAND()命令
--BY RAND()是随机显示结果，这个函数可能会为表中每一个独立的行执行BY RAND()命令，这个会消耗处理器的处理能力。
 SELECT * FROM A order by rand() limit 10;
 --优化之后
 SELECT * FROM A WHERE id >= ((SELECT MAX(id) FROM A)-(SELECT MIN(id) FROM A)) * RAND() + (SELECT MIN(id) FROM A) LIMIT 10 
--22. 排序的索引问题　
--Mysql查询只是用一个索引，因此如果where子句中已经使用了索引的话，
--那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求情况下
--不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
--23.尽量用 union all 替换 union
--union和union all的差异主要是前者需要将两个（或者多个）结果集合并后再进行唯一性过滤操作，
--这就会涉及到排序，增加大量的cpu运算，加大资源消耗及延迟。
--所以当我们可以确认不可能出现重复结果集或者不在乎重复结果集的时候，尽量使用union all而不是union
--24.避免类型转换
--这里所说的“类型转换”是指where子句中出现column字段的类型和传入的参数类型不一致的时候发生的类型转换。
--人为的上通过转换函数进行转换，直接导致mysql无法使用索引。如果非要转型，应该在传入参数上进行转换。
--例如utime 是datetime类型，传入的参数是“2016-07-23”，在比较大小时通常是 date（utime）>"2016-07-23",可以优化为utime>"2016-07-23 00：00：00"
--25.exist 代替 in　
 SELECT * from A WHERE id in (SELECT id from B)
 SELECT * from A WHERE id EXISTS(SELECT 1 from A.id= B.id)
 --26.索引的分类:
 -- 2.2.1、单列索引：一个索引只包含单个列，但一个表中可以有多个单列索引。这里不要搞混淆了。
-- 2.2.2、普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。
-- 2.2.3、唯一索引：索引列中的值必须是唯一的，但是允许为空值，
-- 2.2.4、主键索引：是一种特殊的唯一索引，不允许有空值。（主键约束，就是一个主键索引）
-- 2.3、组合索引：在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。例如，这里由id、name和age3个字段构成的索引，索引行中就按id/name/age的顺序存放，索引可以索引下面字段组合(id，name，age)、(id，name)或者(id)。如果要查询的字段不构成索引最左面的前缀，那么就不会是用索引，比如，age或者（name，age）组合就不会使用索引查询
-- 2.4、全文索引：全文索引，只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引，介绍了要求，说说什么是全文索引，就是在一堆文字中，通过其中的某个关键字等，就能找到该字段所属的记录行，比如有"你是个大煞笔，二货 ..." 通过大煞笔，可能就可以找到该条记录。这里说的是可能，因为全文索引的使用涉及了很多细节，我们只需要知道这个大概意思。
-- 2.5、空间索引：空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有四种，GEOMETRY、POINT、LINESTRING、POLYGON。在创建空间索引时，使用SPATIAL关键字。要求，引擎为MyISAM，创建空间索引的列，必须将其声明为NOT NULL。可能跟游戏开发有关。
-- 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157

四、视图

#视图的优势
第一，增强数据安全性
第二，提高灵活性，操作变简单
第三，提高数据的逻辑独立
#视图的使用
select 
   视图定义列 
from 
   视图名
  --删除视图的语法
DROP VIEW if EXISTS  view_FoodTable;
--  创建视图，查询菜品名称、菜品类型名称、销量 
--创建名为view_FoodTable的视图 在创建视图view_FoodTable之前，判断视图是否已经存在，
存在则将其先删除 
CREATE VIEW view_FoodTable
AS
    SELECT FoodName,FoodTypeName,SalesVolume
    FROM FoodTable f1
    inner join FoodTypeTable f2
    ON f1.FoodTypeID = f2.FoodTypeID;
   
#视图的修改  这里的所有参数都与创建视图的参数一样。
  create or replace 
	view 
	视图名
	as
	 select 语句;
#视图的查看
--查看视图是指查看数据库中已经存在的视图的定义。查看视图必须要有show view的权限。
查看视图的几种方法：
1)describe语句，语法格式：describe 视图名称;  或者desc视图名称;
2)show table status语句,语法格式: show table status like '视图名'
3)show create view语句，语法格式：show create view '视图名'
4)查询information_schem数据库下的views表
语法格式：select * from information_schema.views where table_name ='视图名'
5)SELECT * FROM view_FoodTable 
-- 写出创建满足下述要求的视图的 SQL语句。查询学生的学号、姓名、课程号、课程名、成绩
--创建名为view stu course的视图
CREATE VIEW view_stu_course
AS
Select sno, sname, cno, cname, score 
From StuMarks s1
inner join StuInfo s2 on s1. sno=s2 . sno
inner join course C on s1. cno=C. cno
--利用刚才建立的视图，完成如下查询：查询考试成绩大于等于 90 分的学生的姓名、课程名和成绩
SELECT sname,cname,score FROM view_stu_course where score >= 90
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

四、变量事物

#变量的分类
一、系统变量
#注意：全局变量需要添加global关键字，会话变量需要添加session关键字，如果不写，默认会话级别
1）全局变量
#变量由系统定义，不是用户定义，属于服务器层面
--作用域：针对于所有会话（连接）有效，但不能跨重启

①查看所有全局变量
	SHOW GLOBAL VARIABLES;
②查看满足条件的部分系统变量
	SHOW GLOBAL VARIABLES LIKE '%char%';
③查看指定的系统变量的值
	SELECT @@global.autocommit;
④为某个系统变量赋值
	SET @@global.autocommit=0;
	SET GLOBAL autocommit=0;
2）会话变量
--作用域：作用域：针对于当前会话（连接）有效
①查看所有会话变量
	SHOW SESSION VARIABLES;
②查看满足条件的部分会话变量
	SHOW SESSION VARIABLES LIKE '%char%';
③查看指定的会话变量的值
	SELECT @@autocommit;
	SELECT @@session.tx_isolation;
④为某个会话变量赋值
	SET @@session.tx_isolation='read-uncommitted';
	SET SESSION tx_isolation='read-committed';
二、自定义变量
1）用户变量
--说明：变量由用户自定义，而不是系统提供的
--作用域：针对于当前会话（连接）有效，作用域同于会话变量
使用步骤：
1、声明
2、赋值
3、使用（查看、比较、运算等）
赋值操作符：=或:=
①声明并初始化
	SET @变量名=值;
	SET @变量名:=值;
	SELECT @变量名:=值;
②赋值（更新变量的值）
     方式一：
	SET @变量名=值;
	SET @变量名:=值;
	SELECT @变量名:=值;
    方式二：
	SELECT 字段 INTO @变量名	FROM 表;
③使用（查看变量的值）
	SELECT @变量名;
2）局部变量
--作用域：仅仅在定义它的begin end块中有效
--应用在 begin end中的第一句话
①声明
DECLARE 变量名 类型;
DECLARE 变量名 类型 【DEFAULT 值】;
②赋值（更新变量的值）
方式一：
	SET 局部变量名=值;
	SET 局部变量名:=值;
	SELECT 局部变量名:=值;
方式二：
	SELECT 字段 INTO 局部变量名  FROM 表;
③使用（查看变量的值）
	SELECT 局部变量名
3）案例
  --案例：声明两个变量，求和并打印
用户变量
SET @m=1;
SET @n=1;
SET @sum=@m+@n;
SELECT @sum;

局部变量
DECLARE m INT DEFAULT 1;
DECLARE n INT DEFAULT 1;
DECLARE SUM INT;
SET SUM=m+n;
SELECT SUM;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79

事务

#事务的概念
事务(TRANSACTION)是作为单个逻辑工作单元执行的一系列操作
这些操作作为一个整体一起向系统提交，要么都执行、要么都不执行 
事务是一个不可分割的工作逻辑单元
#事务引擎支持
1、概念：在mysql中的数据用各种不同的技术存储在文件（或内存）中。
2、通过show engines；来查看mysql支持的存储引擎。
3、 在mysql中用的最多的存储引擎有：innodb，myisam ,memory 等。其中innodb支持事务，而myisam、memory等不支持事务
#事务的特性
--事务必须具备以下四个属性，简称ACID 属性：
原子性（Atomicity）：事务是一个完整的操作。事务的各步操作是不可分的（原子的）；要么都执行，要么都不执行
一致性（Consistency）：当事务完成时，数据必须处于一致状态
隔离性（Isolation）：对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务
永久性（Durability）：事务完成后，它对数据库的修改被永久保持，事务日志能够保持事务的永久性
#事务的使用
1、（推荐）begin ，rollback,commit .当然有的人用begin /begin work .
	推荐用START TRANSACTION 是SQL-99标准启动一个事务。    
	start transaction；
	update from usertable set money=money-100 where name='a'; 
	update from usertable set money=money+100 where name='b';
	commit；
	--解释：这样start transaction 手动开启事务，commit 手动关闭事务。
2、默认的时候@@autocommit=1 自动提交是开启的，
	所以你可以理解为每条语句一输入到mysql就commit 了。
	当你 set @@autocommit=0 时候，你可以这样：
   update from usertable set money=money-100 where name='a';
	update from usertable set money=money+100 where name='b';  commit；
	--默认都不提交，只有手动键入commit 时候才上述都提交。
#事务的隔离级别
对于同时运行的多个事务, 当这些事务访问数据库中相同的数据时, 如果没
有采取必要的隔离机制, 就会导致各种并发问题: 
--脏读: 对于两个事务 T1, T2, T1 读取了已经被 T2 更新但还没有被提交的字段. 之后, 若 T2 回滚, T1读取的内容就是临时且无效的.
--不可重复读: 对于两个事务T1, T2, T1 读取了一个字段, 然后 T2 更新了该字段. 之后, T1再次读取同一个字段, 值就不同了.  
--幻读: 对于两个事务T1, T2, T1 从一个表中读取了一个字段, 然后 T2 在该表中插 入了一些新的行. 之后, 如果 T1 再次读取同一个表, 就会多出几行. 
--数据库事务的隔离性: 数据库系统必须具有隔离并发运行各个事务的能力, 使它们不会相互影响, 避免各种并发问题. 
-- 一个事务与其他事务隔离的程度称为隔离级别. 数据库规定了多种事务隔离级别, 不同隔离级别对应不同的干扰程度, 隔离级别越高, 数据一致性就越好, 但并发性越弱.


Oracle 支持的 2 种事务隔离级别：
	READ COMMITED, SERIALIZABLE。 
	Oracle 默认的事务隔离级别为: READ COMMITED 
    Mysql 支持 4 种事务隔离级别. 
	Mysql 默认的事务隔离级别为: REPEATABLE READ

每启动一个 mysql 程序, 就会获得一个单独的数据库连接. 
	每个数据库连接都有一个全局变量 @@tx_isolation, 表示当前的
事务隔离级别. 
  查看当前的隔离级别: SELECT @@tx_isolation;
设置当前 mySQL 连接的隔离级别: 
  set transaction isolation level read committed;  
设置数据库系统的全局的隔离级别:
  set global transaction isolation level read committed;
		
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53

五、存储过程函数

一、存储过程

#存储过程
--存储过程（procedure）存储在数据库当中的可以执行特定工作(查询和更新)的一组SQL代码的程序段。
使用好处：
	1、简化应用开发人员的很多工作
	2、减少数据在数据库和应用服务器之间的传输
	3、提高了数据处理的效率
#存储过程的组成	
--存储过程可以包含数据操纵语句、变量、逻辑 控制语句等
存储过程—————》可以包含——————》单个 SELECT、DELETE、UPDATE 语句、语句块、语句块与逻辑控制语句
存储过程的优点： 
存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。 
当对数据库进行复杂操作时（如对多个表进行Update、Insert、Query、Delete时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。 
存储过程可以重复使用，可减少数据库开发人员的工作量。 
安全性高，可设定只有某此用户才具有对指定存储过程的使用权。
1
2
3
4
5
6
7
8
9
10
11
12
13
14

二、自定义存储过程

#定义存储过程的语法
 基本的创建语法：
CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
	存储过程体（一组合法的SQL语句）
END
1、参数列表包含三部分
	参数模式  参数名  参数类型
	举例：
	in stuname varchar(20)
	参数模式：
	in：该参数可以作为输入，也就是该参数需要调用方传入值
	out：该参数可以作为输出，也就是该参数可以作为返回值
	inout：该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值

2、如果存储过程体仅仅只有一句话，begin end可以省略
存储过程体中的每条sql语句的结尾要求必须加分号。
存储过程的结尾可以使用 delimiter 重新设置
语法：
delimiter 结束标记
案例：
delimiter $

#存储过程例子
基本的创建、调用语法：
delimiter //
DROP PROCEDURE IF EXISTS test //
CREATE PROCEDURE test --存储过程名 
(IN inparms INT, OUT outparams varchar(32)) --输入参数                      
BEGIN/* 语句块头 */                                                           
      select name from tables where id=inparms  into outparams ;
     DECLARE var CHAR(10);  /* 变量声明 */                                    
     IF inparms = 1 THEN/* IF条件开始*/                                         
         SET var = 'hello';  /* 赋值 */                                      
     ELSE
        SET var = 'world';
     END IF;   /* IF结束 */                                                  
     INSERT INTO t1 VALUES (var);  /* SQL语句 */                              
     SELECT name FROM t1 LIMIT 1 INTO outparams;
END //
delimiter ;
 
call test(1, @out);

#流程控制结构语句
F语句
IF search_condition THEN statement_list    
    [ELSEIF search_condition THEN statement_list] ...   
    [ELSE statement_list]
END IF 
IF实现了一个基本的条件构造。如果search_condition求值为真，相应的SQL语句列表被执行。如果没有search_condition匹配，在ELSE子句里的语句列表被执行。statement_list可以包括一个或多个语句。 

--流程控制结构语句
举例： 
DELIMITER //
CREATE PROCEDURE p1(IN parameter1 INT)
      BEGIN
      DECLARE variable1 INT;
      SET variable1 = parameter1 + 1;
      IF variable1 = 0 THEN
          INSERT INTO t VALUES (17);
      END IF;
      IF parameter1 = 0 THEN
          UPDATE t SET s1 = s1 + 1;
      ELSE
          UPDATE t SET s1 = s1 + 2;
      END IF;
END; //
DELIMITER ;


#流程控制结构语句
循环语句
WHILE … END WHILE
LOOP … END LOOP
REPEAT … END REPEAT
在循环中还穿插一些循环控制语句，如LEAVE（类似java语言的break）、ITERATE（类似java语言的continue）等。
LEAVE语句
LEAVE label 这个语句被用来退出任何被标注的流程控制构造。它和BEGIN ... END或循环一起被使用。
ITERATE语句
ITERATE label ITERATE只可以出现在LOOP, REPEAT, 和WHILE语句内。ITERATE意思为：再次循环。 

循环语句
WHILE … END WHILE 举例：

DELIMITER //CREATE PROCEDURE p4 ()
BEGIN
     DECLARE v INT;
     SET v = 0;
     WHILE v < 5 DO
           INSERT INTO t VALUES (v);
           SET v = v + 1;
      END WHILE;
END //

循环语句
LOOP … END LOOP 举例：
CREATE PROCEDURE p5 ()
BEGIN
     DECLARE v INT;
     SET v = 0;
     loop_label: LOOP
         INSERT INTO t VALUES (v);
         SET v = v + 1;
         IF v >= 5 THEN
             LEAVE loop_label;--break;
         END IF;
    END LOOP;
END; //
[begin_label:] LOOP   
      statement_list
END LOOP [end_label]
--LOOP允许某特定语句或语句群的重复执行，实现一个简单的循环构造。在循环内的语句一直重复直到循环被退出，退出通常伴随着一个LEAVE 语句

循环语句--dowhile
REPEAT … UNTIL () END REPEAT 举例：

CREATE PROCEDURE p6 ()
BEGIN
     DECLARE v INT;
     SET v = 0;
     REPEAT
          INSERT INTO t VALUES (v);
          SET v = v + 1;
     UNTIL v >= 5 END REPEAT;
END; //
--功能与WHILE差不多，差别是在执行一次后检查，而WHILE是在开始时检查，累死DO…WHILE功能。

补充：迭代（ITERATE）语句--continue
CREATE PROCEDURE p7 ()
BEGIN
     DECLARE v INT;
     SET v = 0;
     loop_label: LOOP
         IF v = 3 THEN
             SET v = v + 1;
             ITERATE loop_label;
         END IF;
         INSERT INTO t VALUES (v);
         SET v = v + 1;
         IF v >= 5 THEN
             LEAVE loop_label;
         END IF;
     END LOOP;
END; //

#修改存储过程
修改存储过程只能修改其特性，不可修改其内容：
ALTER {PROCEDURE | FUNCTION}  sp_name [characteristic ...]
characteristic ：特性参数

CONTAINS SQL 表示子程序包含 SQL 语句，但不包含读或写数据的语句。
NO SQL 表示子程序中不包含 SQL 语句。
READS SQL DATA 表示子程序中包含读数据的语句。
MODIFIES SQL DATA 表示子程序中包含写数据的语句。
SQL SECURITY { DEFINER |INVOKER } 指明谁有权限来执行。
DEFINER 表示只有定义者自己才能够执行。
INVOKER 表示调用者可以执行。
COMMENT 'string' 表示注释信息。

#删除存储过程
--说明：一次只能删除一个存储过程或者函数，并且要求有该过程或函数的alter routine 权限
删除存储过程：
drop procedure [if exists] 存储过程名

#查看存储过程
1.查看存储过程或函数的状态：
show {procedure|function} status like 存储过程或函数名
2.查看存储过程或函数的定义：
show create {procedure|function} 存储过程或函数名
3.通过查看information_schema.routines了解存储过程和函数的
信息（了解）
select * from rountines where rounine_name =存储过程名|函
数名

#游标/光标：
声明游标
DECLARE cursor_name CURSOR FOR select_statement
这个语句声明一个光标。也可以在子程序中定义多个光标，但是一个块中的每一个光标必须有唯一的名字。

打开游标
OPEN cursor_name
这个语句打开先前声明的光标。

游标FETCH ：读取游标数据到变量
FETCH cursor_name INTO var_name [, var_name] ...
这个语句用指定的打开光标读取下一行（如果有下一行的话），并且前进光标指针
关闭游标CLOSE
CLOSE cursor_name
这个语句关闭先前打开的光标。 
如果未被明确地关闭，光标在它被声明的复合语句的末尾被关闭。

设置游标结束：
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

游标的特性：
READ ONLY 只读，只能取值而不能赋值；
NOT SCROOLABLE 不可回滚，只能顺序读取；
ASENSITIVE 敏感，不能在已经打开游标的表上执行update事务；

举例：
CREATE PROCEDURE curdemo()
BEGIN
     DECLARE done INT DEFAULT 0;
     DECLARE d INT;
     DECLARE a CHAR(16);
     DECLARE b,c INT;
     DECLARE cur2 CURSOR FOR SELECT employee_id FROM employees;
     DECLARE cur1 CURSOR FOR SELECT cname,credit FROM course;
     DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
     OPEN cur1;
     OPEN cur2;
     SET d = 0;
     REPEAT
          FETCH cur1 INTO a, b;
          FETCH cur2 INTO c;
          IF NOT done THEN
           SET d= d+1;   
         END IF;
     UNTIL done END REPEAT;
    CLOSE cur1;
    CLOSE cur2;
    SELECT d;
END //

2）案例
CREATE PROCEDURE pro_ test12()
begin
declare e_ id int( ) ;--创建变量
declare e_ name varchar(50) ;
declare e_ age int() ;
declare e_ salary int(11) ;

declare emp_result cursor for select*from emp;--创建游标并且指定表重新语句
declare has_data int default 1;--创建循环变量默认值为1
DECLARE EXIT HANDLER FOR NOT FOUND set has_data=0;--判断游标是否走到尾部，为尾部则has_data=0
open emp_ result;--打开游标
repeat--repeat循环开始
fetch emp_ result into e_id,e_name,e_age,e_salary;--插入行数据
select concat('id=',e_id,'age=',e_ age) ;--输出
until has_data = 0 --当has_data为0跳出循环
end repeat;--循环结束
close emp_result;--关闭游标

END//
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246

 声明变量：
DECLARE var_name[,...] type [DEFAULT value]
这个语句被用来声明局部变量。要给变量提供一个默认值，需要包含一个DEFAULT子句。值可以被指定为一个表达式，不需要为一个常数。如果没有DEFAULT子句，初始值为NULL。 
 局部变量的作用范围在它被声明的BEGIN ... END块内。它可以被用在嵌套的块中，除了那些用相同名字声明变量的块。

  变量赋值，SET语句：
SET var_name = expr [, var_name = expr] ...
也可以用语句代替SET来为用户变量分配一个值。在这种情况下，分配符必须为:=而不能用=，因为在非SET语句中=被视为一个比较 操作符，如下所示：mysql> SET @t1=0, @t2=0, @t3=0;mysql> SELECT @t1:=0,@t2:=0,@t3:=0;对于使用select语句为变量赋值的情况，若返回结果为空，即没有记录，此时变量的值为上一次变量赋值时的值，如果没有对变量赋过值，则为NULL。

  变量赋值，SELECT ... INTO语句
SELECT col_name[,...] INTO var_name[,...] table_expr这个SELECT语法把选定的列直接存储到变量。因此，只有单一的行可以被取回。
SELECT id,data INTO x,y FROM test.t1 LIMIT 1;
1
2
3
4
5
6
7
8
9
10
11
12

三、存储函数

#创建存储函数
定义存储函数的语法
    基本的创建语法：
CREATE function 存储函数名(参数列表) returns type
BEGIN

	存储函数体(有效的sql语句)
	return param;
END
说明
   1、参数列表包含两部分
	参数名  参数类型（没有 OUT,INOUT参数模式，所有参数都是IN）
	举例：
	stuname varchar(20)
	说明
	returns type :
		返回参数类型；如returns varchar(255)
		
2、 函数体必须包含一个return value 语句,且只能返回一个值；如果返回类型与returns type类型不一致，返回值将会被强制转换为恰当的类型。
案例：将输入的a都翻倍并返回
DELIMITER //
create function numDoubled(a int) returns INT 
return a*2;
//
DELIMITER ;

#删除存储函数
说明：一次只能删除一个存储过程或者函数，并且要求有该
过程或函数的alter routine 权限
删除存储函数：
drop function [if exists] 存储函数名

#查看存储函数
1.查看存储过程或函数的状态：
show {procedure|function} status like 存储过程或函数名
2.查看存储过程或函数的定义：
show create {procedure|function} 存储过程或函数名
3.通过查看information_schema.routines了解存储过程和函数的
信息（了解）
select * from rountines where rounine_name =存储过程名|函
数名

#存储函数与存储过程的差异
与存储过程相比：
	存储过程可以没有返回值，也可以有任意个输出参数，必须单独调用。
	执行的本质都一样。只是函数有如只能返回一个变量的限制。而存储过程可以返回多个。
	而函数是可以嵌入在sql中使用的，可以在select中调用，而存储过程不行。

	函数限制比较多，比如不能用临时表，只能用表变量。还有一些函数都不可用等等。而存储过程的限制相对就比较少。
 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51

六、 触发器

一、问题引入

最优的解决方案就是采用触发器：
它是一种特殊的存储过程 
也具备事务的功能 
它能在多表之间执行特殊的业务规则 
1
2
3
4

二、创建触发器

#触发器的定义
触发器（Trigger）是用户定义在关系表上的一类由事件驱动(insert、update、delete)的特殊过程。利用触发器能够有效地保证数据完整性，也便于执行一些自动的数据操作

#使用SQL创建触发器
CREATE TRIGGER  触发器名  BEFORE|AFTER  触发事件    ON  表名  FOR EACH ROW 触发器语句
触发器只能建立在基本表上，不能建立在临时表或视图上。
触发事件包括：INSERT、UPDATE、DELETE
FOR EACH ROW行级触发器，MYSQL目前还没有支持语句级的触发器。
一个表上不能有两个相同时间和事件的触发程序。
MYSQL中，触发器执行的顺序是BEFORE触发器、表操作(INSERT、UPDATE 和 DELETE)、AFTER触发器。

#Insert触发器的原理
INSERT触发器的工作原理：
1）从表插入数据
2）触发INSERT触发器向new中插入被新增的副本

---利用触发器实现订单与库存表产品编号号上的级联更新。
create trigger prod_insert after insert on order_prod for each row
begin
  declare new_prod_no char(10);
  set new_prod_no=new.prod_no;
    if new_prod_no is not null then
     update  stock_prod set prod_num =prod_num-1  where prod_no=new_prod_no;
  End if;
END;

#使用SQL创建触发器
使用OLD(旧值)和NEW(新值)关键字，能够访问受触发程序影响的行中的字段值（OLD和NEW不区分大小写）。
在INSERT触发程序中，仅能使用NEW.col_name，没有旧值。
在DELETE触发程序中，仅能使用OLD.col_name，没有新值。
在UPDATE触发程序中(先删掉再修改)，可以使用OLD.col_name来引用更新前的旧值，也能使用NEW.col_name来引用更新后的行中的新值。
OLD列是只读的，可以引用它，但不能更改它。
NEW列，如果具有SELECT权限，可引用它。在BEFORE触发程序中，如果具有UPDATE权限，可使用“SET NEW.col_name = value”更改它的值。意味着可以使用触发程序来更改将要插入到新行中的值，或用于更新行的值。


#DELETE触发器
DELETE触发器的工作原理：
从表删除数据
触发delete触发器向OLD中插入被删除的副本
--原理：先将要删除的数据保存到临时表，之后执行触发器对应的SQL语句，如果触发器执行失败，则自动恢复临时表中的数据，确保数据的完整性和一致性。
--因为触发器也是一种特殊的事务

#触发器的案例
利用触发器实现学生与成绩表学生编号上的级联更新。
create trigger stud_delete after delete on stuinfo for each row
begin
  declare oldsno char(10);
  set oldsno=old.sno;
delete from stumarks where sno=oldsno;
END;

#UPDATE触发器
UPDATE触发器的工作原理：
1）先从表中删除要更新的行
2）把删除的行的副本插入old中
3）再向原表中插入新行
4）把新行的副本插入new中
5）检查old和new中的数据，确定是否需要回滚或执行其他操作
--利用触发器实现学生与成绩表学生编号上的级联更新。
create trigger stud_update after update on stuinfo for each row
begin
  declare newsno char(10);
  declare oldsno char(10);
  set newsno=new.sno;
  set oldsno=old.sno;
  if newsno<>oldsno then
     update stumarks set sno=newsno where sno=oldsno;
  End if;
END;

--触发器是在对表进行插入、更新或删除操作时自动执行的存储过程
触发器不能带参数，不能直接调用
触发器不能返回任何结果 不要在触发器中使用 select
触发器一般都需要使用临时表：old和new，它们存放了被删除或插入的记录行副本 
AFTER触发器类型
          INSERT触发器
          UPDATE触发器
          DELETE触发器
触发时间 分为 after 和 before
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79