MySql

一、基本的SELECT语句

1、SQL 的分类：

DDL（数据定义语言）：这些语句定义了不通的表，数据库，视图，索引等数据库对象，还可以用来创建删除，
修改数据库和数据表的结构，
主要的语句关键字包括：create,drop，alter（修改一个表）等；
DML（数据库操作语言）：用于添加删除更新和查询数据库的记录，并检查数据的完整性，
主要的语句关键字包括：insert,delete,update,select等；
DCL（数据控制语言）：用于定义数据库，表，字段，用户的访问权限和安全级别，
主要的语句关键字包括：grant（赋予权限）,revoke（回收相关的权限）,
commit,rollback,savepoint（保存点）等；

2、sql语言的规格与规范：

2.1 SQL大小写规范

数据库名、表名、表别名、字段名、字段别名等都小写
SQL 关键字、函数名、绑定变量等都大写

2.2 SQL注释

单行注释：#注释文字(MySQL特有的方式)
单行注释：-- 注释文字(--后面必须包含一个空格。)
多行注释：/* 注释文字 */

2.3 命名规则：

2.4去除重复行：

select DISTINCT t.CALL_ID from csp_call t; #去除重复行，使用DISTINCT 关键字

2.5 空值参与运算

所有运算符或列值遇到null值，运算的结果都为null；
在 MySQL 里面， 空值不等于空字符串。一个空字符串的长度是 0，
而一个空值的长度是空。而且，在 MySQL 里面，空值是占用空间的。

2.6 显示表结构

使用DESCRIBE 或 DESC 命令，表示表结构。

2.7 ifnull

select id ,IFNULL(name,0) from mytb1;

二、运算符

三、排序和分页

1、排序

asc 升序，desc 降序；
ORDER BY 子句在SELECT语句的结尾。

select id,name FROM mytb1 ORDER BY id desc ;

可以使用不在SELECT列表中的列排序。在对多列进行排序的时候，首先排序的第一列必须有相同的列值，
才会对第二列进行排序。如果第一列数据中所有值都是唯一的，将不再对第二列进行排序。

select CALL_ID,CALL_BGTIME from csp_call ORDER BY CALL_ID,CALL_BGTIME desc;

2、分页

LIMIT [位置偏移量,] 行数

--前10条记录：
SELECT * FROM 表名 LIMIT 0,10;
或者
SELECT * FROM 表名 LIMIT 10;
--第11至20条记录：
SELECT * FROM 表名 LIMIT 10,10;
--第21至30条记录：
 SELECT * FROM 表名 LIMIT 20,10;

LIMIT 子句必须放在整个SELECT语句的最后！

四、多表查询

1、UNION的使用

合并查询结果 利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。
合并时，两个表对应的列数和数据类型必须相同，并且相互对应。
各个SELECT语句之间使用UNION或UNIONALL关键字分隔。
UNION 操作符返回两个查询的结果集的并集，去除重复记录。
UNION ALL操作符返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重。
执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据不存在重复数据，
或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效率。**

五、函数

1、单行函数

count(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略为NULL的值。  
count(1)包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略为NULL的值。
count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空
（这里的空不是指空字符串或者0，而是表示null）的计数，即某个字段值为NULL时，不统计。

2.聚合函数

聚合函数作用于一组数据，并对一组数据返回一个值。
**AVG()-->**可以对数值型数据使用AVG 和 SUM 函数。
**SUM()-->**可以对数值型数据使用AVG 和 SUM 函数。
**MAX()-->**可以对任意数据类型的数据使用 MIN 和 MAX 函数。
**MIN()-->**可以对任意数据类型的数据使用 MIN 和 MAX 函数。
**COUNT()-->**COUNT(*)返回表中记录总数，适用于任意数据类型。count(字段)返回不为null的总行数

SELECT CALLED_NO from csp_call GROUP BY CALLED_NO WITH ROLLUP;

HAVING子句：
1. 行已经被分组。
2.  使用了聚合函数。
3.  满足HAVING 子句中条件的分组将被显示。
4.  HAVING 不能单独使用，必须要跟 GROUP BY 一起使用。

SELECT CALLED_NO ,count(CALLED_NO) from csp_call GROUP BY CALLED_NO HAVING count(CALLED_NO)>100;

3、WHERE和HAVING的对比

WHERE 可以直接使用表中的字段作为筛选条件，但不能使用分组中的计算函数作为筛选条件；
HAVING 必须要与 GROUP BY 配合使用，可以把分组计算的函数和分组字段作为筛选条件。

如果需要通过连接从关联表中获取需要的数据，WHERE 是先筛选后连接，而 HAVING 是先连接后筛选。

4、SELECT的执行过程

SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT...

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT

六、创建和管理表

6.1、创建数据库：

create database 数据库名；

创建数据库并指定字符集：

create database 数据库名 CHARACTER SET  字符集；

判断数据库是否已经存在，不存在则创建数据库（推荐）：

create database 数据库名 if not EXISTS 数据库名;

6.2、 使用数据库

查看所有的数据库：

show databases;

查看当前使用的数据库：

select database();

查看指定库下所有的表:

show tables from 数据库;

查看数据库的创建信息

show create database 数据库名;
SHOW CREATE DATABASE 数据库名\G;

使用/切换数据库：

use 数据库名；

6.3 修改数据库

更改数据库的字符集：

ALTER DATABASE 数据库名 CHARACTER SET 字符集;  #比如：gbk、utf8等

删除指定的数据库 

drop database 数据库名;
DROP DATABASE IF EXISTS 数据库名;

6.4、创建表

create table testTable(
 id INT(5) AUTO_INCREMENT,
 name VARCHAR(10),
 PRIMARY KEY(id)  #主键
);

CREATE table mytb2 as (select * from mytb1);

6.5 修改表

ALTER table testTable add age VARCHAR(12);

6.6 修改一个列

6.7 重命名一个列

七、约束

外键影响数据库的插入速度。

八、视图

视图(VIEW) ：一个或者多个数据表里的数据的逻辑显示，视图并不存储数据。
视图是一种虚拟表，本身是不具有数据的，占用很少的内存空间；

视图的创建和删除只影响视图本身，不影响对应的基表。但是当对视图中的数据进行增加、
删除和修改操作时，数据表中的数据会相应地发生变化，反之亦然。

在数据库中，视图不会保存数据，数据真正保存在数据表中。
当对视图中的数据进行增加、删除和修改操作时，数据表中的数据会相应地发生变化；反之亦然。

视图，是向用户提供基表数据的另一种表现形式。
通常情况下，小型项目的数据库可以不使用视图，但是在大型项目中，
以及数据表比较复杂的情况下，视图的价值就凸显出来了，它可以帮助我们把经常查询的结果集放到虚拟表中，
提升使用效率。理解和使用起来都非常方便。

create VIEW mytb1vieu as select id from mytb1;

在创建视图时，没有在视图名后面指定字段列表，则视图中字段列表默认和SELECT语句中的字段列表一致。
如果SELECT语句中给字段取了别名，那么视图中的字段名和别名相同。

九、存储过程与函数

9.1、存储过程：

存储过程：预先存储在 MySQL 服务器上，需要执行的时候，客户端只需要向服务器端发出调用存储过程的命令，
服务器端就可以把预先存储好的这一系列 SQL 语句全部执行。
**预先编译的mysql语句的封装。**

好处：

1、简化操作，提高了sql语句的重用性，减少了开发程序员的压力
2、减少操作过程中的失误，提高效率
3、减少网络传输量（客户端不需要把所有的 SQL 语句通过网络发给服务器） 
4、减少了 SQL 语句暴露在网上的风险，也提高了数据查询的安全性


存储过程的参数类型可以是IN、OUT和INOUT。

根据这点分类如下：

1、没有参数（无参数无返回） 
2、仅仅带 IN 类型（有参数无返回） 
3、仅仅带 OUT 类型（无参数有返回） 
4、既带 IN 又带 OUT（有参数有返回） 
5、带 INOUT（有参数有返回）
注意：IN、OUT、INOUT 都可以在一个存储过程中带多个。

IN：当前参数为输入参数，也就是表示入参；存储过程只是读取这个参数的值。
如果没有定义参数种类，默认就是 IN，表示输入参数。
OUT：当前参数为输出参数，也就是表示出参；执行完成之后，
调用这个存储过程的客户端或者应用程序就可以读取这个参数返回的值了。
INOUT：当前参数既可以为输入参数，也可以为输出参数。

#创建存储过程：
DELIMITER $
CREATE PROCEDURE select_data_call()
BEGIN
	SELECT * from csp_call;
END $
DELIMITER ;
#存储过程的调用：
call select_data_call();

DELIMITER $
CREATE PROCEDURE select_avg_age_out(out avetime int(8))
BEGIN
select avg(TOTAL_TIME) into avetime FROM csp_call;
END $
DELIMITER ;

call select_avg_age_out(@avetime);
select @avetime;

DELIMITER $
create PROCEDURE select_in(in callNo VARCHAR(20))
BEGIN
select CALL_NO from csp_call where CALL_NO = callNo and call_no is not NULL;
end $
DELIMITER ;

call select_in('10000');
##@赋值符号@+
set @callNo = '10000';
call select_in(@callno);

DELIMITER $
create PROCEDURE select_in_out(in callNo VARCHAR(10),out avgtime int(8))
BEGIN
select avg(TOTAL_TIME) into avgtime from csp_call where call_no = callNo;
end $
DELIMITER ;
set @callNo ='10000';
call select_in_out(@callNo,@avgtime);
call select_in_out('10000',@avgtime);
select @avgtime;

delimiter $
create PROCEDURE select_inout(INOUT custname VARCHAR(10))
BEGIN
select cust_name into custname  from csp_cust where cust_name=custname; 
end $
delimiter ;
set @custname='李丹';
call select_inout(@custname);
select @custname;

9.2、存储函数

1、参数列表：指定参数为IN、OUT或INOUT只对PROCEDURE是合法的，FUNCTION中总是默认为IN参数。
2、RETURNS type 语句表示函数返回数据的类型；RETURNS子句只能对FUNCTION做指定，
对函数而言这是强制的。它用来指定函数的返回类型，而且函数体必须包含一个RETURN value语句。
3、characteristic 创建函数时指定的对函数的约束。取值与创建存储过程时相同，这里不再赘述。
4、函数体也可以用BEGIN…END来表示SQL代码的开始和结束。如果函数体只有一条语句，
也可以省略BEGIN…END。

delimiter $
create FUNCTION select_begintime()
RETURNS datetime
DETERMINISTIC 
BEGIN
 return (select CALL_BGTIME from csp_call where call_id = '12345678910');
END $
delimiter ;

select select_begintime();

delimiter $
create FUNCTION select_bycallid2(callId VARCHAR(32))
RETURNS datetime
BEGIN
return (select CALL_BGTIME from csp_call where call_id = callId );
end $
delimiter ;
 set @callId='12345678910';
select select_bycallid2(@callId);

9.3、存储过程的查看，修改和删除：

##使用SHOW CREATE语句查看存储过程和函数的创建信息
show create PROCEDURE select_avg_age;

##使用SHOW STATUS语句查看存储过程和函数的状态信息
show PROCEDURE STATUS LIKE 'select_avg_age';

##从information_schema.Routines表中查看存储过程和函数的信息
select * from information_schema.Routines WHERE ROUTINE_NAME='select_avg_age' and ROUTINE_TYPE = 'PROCEDURE';

修改存储过程或函数，不影响存储过程或函数功能，只是修改相关特性。使用ALTER语句实现。

ALTER PROCEDURE select_avg_age SQL SECURITY INVOKER COMMENT '平均年龄';

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

DROP PROCEDURE select_avg_age;##删除存储过程

9.4、关于存储过程使用的争议

**1、存储过程可以一次编译多次使用。**存储过程只在创建时进行编译，之后的使用都不需要重新编译，
这就提升了 SQL 的执行效率。
**2、可以减少开发工作量。**将代码封装成模块，实际上是编程的核心思想之一，
这样可以把复杂的问题拆解成不同的模块，然后模块之间可以重复使用，在减少开发工作量的同时，
还能保证代码的结构清晰。
**3、存储过程的安全性强。**我们在设定存储过程的时候可以设置对用户的使用权限，
这样就和视图一样具有较强的安全性。
**4、可以减少网络传输量。**因为代码封装到存储过程中，每次使用只需要调用存储过程即可，
这样就减少了网络传输量。
**5、良好的封装性。**在进行相对复杂的数据库操作时，原本需要使用一条一条的 SQL 语句，
可能要连接多次数据库才能完成的操作，现在变成了一次存储过程，只需要连接一次即可。


**1、可移植性差**。存储过程不能跨数据库移植，比如在 MySQL、Oracle 和 SQL Server 里编写的存储过程，
在换成其他数据库时都需要重新编写。
**2、调试困难**。只有少数 DBMS 支持存储过程的调试。对于复杂的存储过程来说，开发和维护都不容易。
虽然也有一些第三方工具可以对存储过程进行调试，但要收费。
**3、存储过程的版本管理很困难。**比如数据表索引发生变化了，可能会导致存储过程失效。
我们在开发软件的时候往往需要进行版本管理，但是存储过程本身没有版本控制，版本迭代更新的时候很麻烦。
**4、它不适合高并发的场景。**高并发的场景需要减少数据库的压力，有时数据库会采用分库分表的方式，
而且对可扩展性要求很高，在这种情况下，存储过程会变得难以维护，增加数据库的压力，显然就不适用了。

十、变量、流程控制与游标

10.1、变量

在 MySQL 数据库中，变量分为系统变量以及用户自定义变量。

10.1.1、系统变量

系统变量分为全局系统变量（需要添加global 关键字）以及会话系统变量（需要添加 session 关键字），
有时也把全局系统变量简称为全局变量，有时也把会话系统变量称为local变量。
如果不写，默认会话级别。静态变量（在 MySQL 服务实例运行期间它们的值不能使用 set 动态修改）
属于特殊的全局系统变量。
每一个MySQL客户机成功连接MySQL服务器后，都会产生与之对应的会话。会话期间，
MySQL服务实例会在MySQL服务器内存中生成与该会话对应的会话系统变量，
这些会话系统变量的初始值是全局系统变量值的复制。

全局系统变量

全局系统变量针对于所有会话（连接）有效，但不能跨重启

#查看所有全局变量
SHOW GLOBAL VARIABLES;
SHOW GLOBAL VARIABLES LIKE 'max_connections';
#查看所有会话变量
SHOW SESSION VARIABLES;
#或
SHOW VARIABLES;

查看指定系统变量

作为 MySQL 编码规范，MySQL 中的系统变量以两个“@”开头，其中“@@global”仅用于标记全局系	统变量，
“@@session”仅用于标记会话系统变量。“@@”首先标记会话系统变量，如果会话系统变量不存在，
则标记全局系统变量。

select @@global.max_connections;##查看最大连接数

##查看当前会话的 MySQL 连接 ID
select @@session.pseudo_thread_id;
select @@pseudo_thread_id;

**修改系统变量的值**
方式1：修改MySQL配置文件，继而修改MySQL系统变量的值（该方法需要重启MySQL服务）
方式2：在MySQL服务运行期间，使用“set”命令重新设置系统变量的值

set @@global.max_connections=150;
set GLOBAL max_connections=160;

SET @@session.pseudo_thread_id=9;

10.1.2、用户变量

用户变量是用户自己定义的，作为 MySQL 编码规范，MySQL 中的用户变量以一个“@”开头。
根据作用范围不同，又分为会话用户变量和局部变量。
会话用户变量：作用域和会话变量一样，只对当前连接会话有效。
局部变量：只在 BEGIN 和 END 语句块中有效。局部变量只能在存储过程和函数中使用。

set @a=1;
set @c=2;
set @sum=@a+@c
select (@sum);

SELECT (@a-@b) where (SELECT @a :=count(*) from csp_call UNION select @b:=count(*) from csp_call);
select count(*) from csp_call into @a;
select @a;

10.1.2 局部变量

定义：可以使用DECLARE语句定义一个局部变量
作用域：仅仅在定义它的 BEGIN ... END 中
有效位置：只能放在 BEGIN ... END 中，而且只能放在第一句

DELIMITER $
CREATE PROCEDURE testbian()
BEGIN
#声明
declare m int DEFAULT 1;
--使用
select count(*) into m from csp_call;
--查看
select m;
END $
DELIMITER ;

call testbian();

10.2 定义条件与处理程序

-- DECLARE 错误名称 CONDITION FOR 错误码（或错误条件）
DECLARE Field_Not_Be_NULL CONDITION for 1048;
DECLARE Field_Not_Be_NULL CONDITION FOR SQLSTATE '23000';

定义条件就是给MySQL中的错误码命名，这有助于存储的程序代码更清晰。
它将一个错误名字和指定的错误条件关联起来。
这个名字可以随后被用在定义处理程序的DECLARE HANDLER语句中。

-- DECLARE 处理方式 HANDLER FOR 错误类型 处理语句

处理方式：处理方式有3个取值：CONTINUE、EXIT、UNDO。
CONTINUE：表示遇到错误不处理，继续执行。
EXIT：表示遇到错误马上退出。
UNDO：表示遇到错误后撤回之前的操作。MySQL中暂时不支持这样的操作。

错误类型（即条件）可以有如下取值：
SQLSTATE '字符串错误码'：表示长度为5的sqlstate_value类型的错误代码；
MySQL_error_code：匹配数值类型错误代码；
错误名称：表示DECLARE ... CONDITION定义的错误条件名称。
SQLWARNING：匹配所有以01开头的SQLSTATE错误代码；
NOT FOUND：匹配所有以02开头的SQLSTATE错误代码；
SQLEXCEPTION：匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误代码；

处理语句：如果出现上述条件之一，则采用对应的处理方式，并执行指定的处理语句。
语句可以是像“SET 变量 = 值”这样的简单语句，也可以是使用BEGIN ... END编写的复合语句。

delimiter //
create PROCEDURE testBC()
BEGIN
DECLARE CONTINUE HANDLER FOR 1048 SET @info = 'NO_SUCH_TABLE';
SET @x = 1;
 UPDATE mytb1 set age=null where id = 1;
SET @x = 2; 
	UPDATE mytb1 set age=null where id = 2; 
SET @x = 3;
end //
delimiter ;

call testBC();
select @x;

#方法1：捕获sqlstate_value
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info = 'NO_SUCH_TABLE';
#方法2：捕获mysql_error_value
DECLARE CONTINUE HANDLER FOR 1146 SET @info = 'NO_SUCH_TABLE';
#方法3：先定义条件，再调用
DECLARE no_such_table CONDITION FOR 1146;
DECLARE CONTINUE HANDLER FOR NO_SUCH_TABLE SET @info = 'NO_SUCH_TABLE';
#方法4：使用SQLWARNING
DECLARE EXIT HANDLER FOR SQLWARNING SET @info = 'ERROR';
#方法5：使用NOT FOUND
DECLARE EXIT HANDLER FOR NOT FOUND SET @info = 'NO_SUCH_TABLE';
#方法6：使用SQLEXCEPTION
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info = 'ERROR';

10.3、 流程控制

解决复杂问题不可能通过一个 SQL 语句完成，我们需要执行多个 SQL 操作。
流程控制语句的作用就是控制存储过程中 SQL 语句的执行顺序，是我们完成复杂操作必不可少的一部分。
只要是执行的程序，流程就分为三大类：

顺序结构：程序从上往下依次执行
分支结构：程序按条件进行选择执行，从两条或多条路径中选择一条执行
循环结构：程序满足一定条件下，重复执行一组语句针

对于MySQL 的流程控制语句主要有 3 类。
注意：只能用于存储程序。

条件判断语句：IF 语句和 CASE 语句
循环语句：LOOP、WHILE 和 REPEAT 语句
跳转语句：ITERATE 和 LEAVE 语句

10.3.1、IF

delimiter //
create PROCEDURE test03(IN ids INT)
BEGIN
DECLARE salary01 int DEFAULT 0;
select salary into salary01 from mytb1 where id = ids;
if 
	salary01>100
	then 
	UPDATE mytb1 set salary = salary+100 where id = ids;
END IF;
end //
delimiter ;
-- select salary from mytb1 where id = 2;
call test03(2);

10.3.2、CASE

delimiter //
create procedure test04(IN ids INT)
BEGIN
declare salary01 int default 0;
select salary into salary01 from mytb1 where id = ids;
CASE salary01
	when 100 then UPDATE mytb1 set salary = salary+100 where id = ids;
	when 400 then UPDATE mytb1 set salary = salary-100 where id = ids;
	when 300 then UPDATE mytb1 set salary = salary-200 where id = ids;
	end case;
end //
delimiter ;

delimiter //
create PROCEDURE test07(IN ids int)
BEGIN
declare salary01 int default 0;
declare value123 VARCHAR(20) default "";
select salary into salary01 from mytb1 where id = ids;
CASE 
	WHEN salary01<100 THEN
		select'salary01<100';
	when salary01<200 THEN
		select  'salary01<200';
	when salary01<300 THEN
		select 'salary01<300';
	else 
		select 'salary01>300';
END CASE;

end //
delimiter ;

10.3.3、LOOP

delimiter //
create PROCEDURE testr2w(IN ids int,out num int)
BEGIN
DECLARE salary01 INT DEFAULT 0;
DECLARE loop_count INT DEFAULT 0;
select salary into salary01 from mytb1 where id = ids;
label_loop:LOOP
 if  salary01>900 THEN LEAVE label_loop;
 END IF;
 	UPDATE mytb1 set salary = salary+100 where id = ids;
 	select salary into salary01 from mytb1 where id = ids;
 	SET loop_count = loop_count + 1;
end LOOP label_loop;
 SET num = loop_count;
end //
delimiter ;

call testr2w(2,@num);

10.3.4、while

delimiter //
create PROCEDURE testnn(out num int)
BEGIN
declare while_count int DEFAULT 0;
DECLARE avg_sal DOUBLE ;
select avg(salary) into avg_sal from mytb1;
WHILE avg_sal<700 do 
	update mytb1 set salary = 1.1*salary;
	set while_count = while_count+1;
	select avg(salary) into avg_sal from mytb1;
end while;
	set num =while_count;
end //
delimiter ;
call testnn(@num);

10.3.5、REPEAT

delimiter //
create PROCEDURE testtn(out num int)
BEGIN
declare while_count int DEFAULT 0;
DECLARE avg_sal int ;
select avg(salary) into avg_sal from mytb1;
REPEAT
	update mytb1 set salary = 1.1*salary;
	set while_count = while_count+1;
	select avg(salary) into avg_sal from mytb1;
UNTIL avg_sal >= 13000
end REPEAT;
	set num =while_count;
end //
delimiter ;
call testtn(@num);

10.3.6、跳转语句之LEAVE语句

**LEAVE语句**：可以用在循环语句内，或者以 BEGIN 和 END 包裹起来的程序体内，
表示跳出循环或者跳出程序体的操作。如果你有面向过程的编程语言的使用经验，
你可以把 LEAVE 理解为 break。

LEAVE 标记名 -- 基本格式

delimiter //
create PROCEDURE testjj(OUT num INT)
BEGIN
DECLARE avg_sal double;
DECLARE count int DEFAULT 0;
select avg(salary) into avg_sal from mytb1;
leave_while:while TRUE DO 
	IF avg_sal>1800 then LEAVE leave_while;
	end if;
	update mytb1 SET salary=salary*1.5;
	set count = count+1;
	select avg(salary) into avg_sal from mytb1;
end while;
set num = count;
end //
delimiter ;

10.3.7 跳转语句之ITERATE语句

**ITERATE语句**：只能用在循环语句（LOOP、REPEAT和WHILE语句）内，表示重新开始循环，
将执行顺序转到语句段开头处。如果你有面向过程的编程语言的使用经验，
你可以把 ITERATE 理解为 continue，意思为“再次循环”。

ITERATE label -- 基本格式

delimiter //
CREATE PROCEDURE test_iterate101()
BEGIN
DECLARE a int DEFAULT 0;
my_loop: LOOP
	set a = a+1;
	if a<10 
		then ITERATE my_loop;
	elseif a>15
		then leave my_loop;
	end if;
	select "小蛋蛋";
END LOOP;

end //
delimiter ;

10.4、游标

游标，提供了一种灵活的操作方式，让我们能够对结果集中的每一条记录进行定位，
并对指向的记录中的数据进行操作的数据结构。游标让 SQL 这种面向集合的语言有了面向过程开发的能力。

10.4.1、声明游标

在MySQL中，使用DECLARE关键字来声明游标，其语法的基本形式如下：

DECLARE cursor_name CURSOR FOR select_statement; 

如果是用 Oracle 或者 PostgreSQL，需要写成：

DECLARE cursor_name CURSOR IS select_statement;

要使用 SELECT 语句来获取数据结果集，而此时还没有开始遍历数据，
这里 select_statement 代表的是SELECT 语句，返回一个用于创建游标的结果集。

DECLARE cur_emp CURSOR FOR SELECT employee_id,salary FROM employees;

10.4.2、打开游标

OPEN cursor_name

10.4.3、使用游标（从游标中取得数据）

FETCH cursor_name INTO var_name [, var_name] ...

10.4.4、第四步，关闭游标

有 OPEN 就会有 CLOSE，也就是打开和关闭游标。当我们使用完游标后需要关闭掉该游标。
因为游标会占用系统资源，如果不及时关闭，游标会一直保持到存储过程结束，影响系统运行的效率。

CLOSE cursor_name

create PROCEDURE test_cursor(IN sum_salary INT,OUT total INT)
BEGIN
DECLARE sum_sal INT DEFAULT 0;
DECLARE cos_sal int DEFAULT 0;
DECLARE num INT DEFAULT 0;
-- 定义游标
DECLARE salary_num CURSOR for select salary from mytb1 ORDER BY salary;
-- 打开游标
OPEN salary_num;
WHILE sum_sal<sum_salary DO
-- 使用游标
	FETCH salary_num into cos_sal;
	set sum_sal = sum_sal +cos_sal;
	set num = num+1;
end while;
SET total = num;
-- 关闭游标
close salary_num;
end //
delimiter ;

call test_cursor(5000,@total);
SELECT @total;

游标是 MySQL 的一个重要的功能，为逐条读取结果集中的数据，提供了完美的解决方案。
跟在应用层面实现相同的功能相比，游标可以在存储程序中使用，效率高，程序也更加简洁。
但同时也会带来一些性能问题，比如在使用游标的过程中，会对数据行进行加锁，这样在业务并发量大的时候，
不仅会影响业务之间的效率，还会消耗系统资源，造成内存不足，这是因为游标是在内存中进行的处理。
建议：养成用完之后就关闭的习惯，这样才能提高系统的整体效率。



使用SET GLOBAL语句设置的变量值只会临时生效。数据库重启后，
服务器又会从MySQL配置文件中读取变量的默认值。 MySQL 8.0版本新增了SET PERSIST命令。
例如，设置服务器的最大连接数为1000：

SET PERSIST global max_connections = 1000;

十一、触发器

MySQL的触发器和存储过程一样，都是嵌入到MySQL服务器的一段程序。
触发器是由事件来触发某个操作，这些事件包括INSERT、UPDATE、DELETE事件。
所谓事件就是指用户的动作或者触发某项行为。如果定义了触发程序，当数据库执行这些语句时候，
就相当于事件发生了，就会自动激发触发器执行相应的操作。
当对数据表中的数据执行插入、更新和删除操作，需要自动执行一些数据库逻辑时，可以使用触发器来实现。

CREATE TRIGGER 触发器名称 
	{BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 表名 FOR EACH ROW 触发器执行的语句块;

表名：表示触发器监控的对象。
BEFORE|AFTER：表示触发的时间。
BEFORE 表示在事件之前触发；AFTER 表示在事件之后触发。	
INSERT|UPDATE|DELETE：表示触发的事件。
INSERT 表示插入记录时触发；
UPDATE 表示更新记录时触发；
DELETE 表示删除记录时触发。

CREATE TABLE test_trigger (id INT PRIMARY KEY AUTO_INCREMENT,t_note VARCHAR(30));

CREATE TABLE test_trigger_log (id INT PRIMARY KEY AUTO_INCREMENT,t_log VARCHAR(30));

DELIMITER //
CREATE TRIGGER before_insert BEFORE INSERT ON test_trigger FOR EACH ROW 
BEGIN 
INSERT INTO test_trigger_log (t_log) VALUES('before_insert');
END //
DELIMITER ;

INSERT INTO test_trigger (t_note) VALUES ('测试 BEFORE INSERT 触发器');
SELECT * FROM test_trigger_log;

11.1. 查看、删除触发器

SHOW TRIGGERS;
SHOW CREATE TRIGGER 触发器名;
SELECT * FROM information_schema.TRIGGERS;

11.2.删除触发器

DROP TRIGGER  IF EXISTS 触发器名称;

十二、linux 下安装mysql

查看是否安装过MySQL

 rpm -qa | grep -i mysql  # -i 忽略大小写

检查mysql service：

systemctl status mysqld.service

MySQL的卸载

systemctl stop mysqld.service
rpm -qa | grep -i mysql 
# 或 
yum list installed | grep mysql

卸载上述命令查询出的已安装程序

yum remove mysql-xxx mysql-xxx mysql-xxx mysqk-xxxx

务必卸载干净，反复执行rpm -qa | grep -i mysql确认是否有卸载残留

find / -name mysql

rm -rf xxx

rm -rf /etc/my.cnf

由于mysql安装过程中，会通过mysql用户在/tmp目录下新建tmp_db文件，所以请给/tmp较大的权限。
执行 ：

chmod -R 777 /tmp

安装前，检查依赖

 rpm -qa|grep libaio;
 rpm -qa|grep net-tools;

在mysql的安装文件目录下执行：（必须按照顺序执行）

rpm -ivh mysql-community-common-8.0.25-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-plugins-8.0.25-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-8.0.25-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-8.0.25-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-8.0.25-1.el7.x86_64.rpm

安装过程中可能的报错信息：

一个命令：yum remove mysql-libs 解决，清除之前安装过的依赖即可

yum remove mysql-libs 

查看mysql版本：

mysql --version
#或
mysqladmin --version

执行如下命令，查看是否安装成功。需要增加 -i 不用去区分大小写，否则搜索不到。

rpm -qa|grep -i mysql

为了保证数据库目录与文件的所有者为 mysql 登录用户，如果你是以 root 身份运行 mysql 服务，
需要执行下面的命令初始化：

mysqld --initialize --user=mysql

--initialize 选项默认以“安全”模式来初始化，
则会为 root 用户生成一个密码并将该密码标记为过期，登录后你需要设置一个新的密码。
生成的临时密码会往日志中记录一份。

查看密码：

cat /var/log/mysqld.log

root@localhost: 后面就是初始化的密码
 启动MySQL，查看状态

启动：systemctl start mysqld.service
关闭：systemctl stop mysqld.service
重启：systemctl restart mysqld.service
查看状态：systemctl status mysqld.service

查看进程：

ps -ef | grep -i mysql

 查看MySQL服务是否自启动

systemctl list-unit-files|grep mysqld.service

systemctl enable mysqld.service
systemctl disable mysqld.service

登陆后修改密码：

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password'; 

防火墙：

systemctl start firewalld.service
systemctl status firewalld.service
systemctl stop firewalld.service#设置开机启用防火墙
systemctl enable firewalld.service#设置开机禁用防火墙
systemctl disable firewalld.service

Linux下修改配置

Host列指定了允许用户登录所使用的IP，比如user=root Host=192.168.1.1。
这里的意思就是说root用户只能通过192.168.1.1的客户端去访问。
 user=root Host=localhost，表示只能通过本机客户端去访问。而%是个通配符，
 如果Host=192.168.1.%，那么就表示只要是IP地址前缀为“192.168.1.”的客户端都可以连接。
 如果Host=%，表示所有IP都有连接权限。
 注意：在生产环境下不能为了省事将host设置为%，这样做会存在安全问题，
 具体的设置可以根据生产环境的IP进行设置。

 use mysql;
 select Host,User from user;
 update user set host = '%' where user ='root';

Host设置了“%”后便可以允许远程访问。

flush privileges; #Host修改完成后记得执行flush privileges使配置立即生效：

mysql的字符集

 查看默认使用的字符集
show VARIABLES like 'character%';
-- 或者
show variables like '%char%';
-- 修改字符集
-- vim /etc/my.cnf
-- 在MySQL5.7或之前的版本中，在文件最后加上中文字符集配置
-- character_set_server=utf8 但是原库、原表的设定不会发生变化，参数修改只对新建的数据库生效。
-- 重新启动MySQL服务
-- systemctl restart mysqld 

--  修改已创建数据库的字符集
alter database dbtest1 character set 'utf8';
-- 修改已创建数据表的字符集
alter table t_emp convert to character set 'utf8';
#查看GBK字符集的比较规则
SHOW COLLATION LIKE 'gbk%';
#查看UTF-8字符集的比较规则
SHOW COLLATION LIKE 'utf8%';
SHOW VARIABLES LIKE '%lower_case_table_names%'
#查看服务器的字符集和比较规则
SHOW VARIABLES LIKE '%_server';
#查看数据库的字符集和比较规则
SHOW VARIABLES LIKE '%_database';
#查看具体数据库的字符集
SHOW CREATE DATABASE dbtest1;
#修改具体数据库的字符集
ALTER DATABASE dbtest1 DEFAULT CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
#查看表的字符集
show create table employees;
#查看表的比较规则
show table status from atguigudb like 'employees';
#修改表的字符集和比较规则
ALTER TABLE emp1 DEFAULT CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

SQL大小写规范

SHOW VARIABLES LIKE '%lower_case_table_names%';

默认为0，大小写敏感。
设置1，大小写不敏感。创建的表，数据库都是以小写形式存放在磁盘上，对于sql语句都是转换为小写
	  对表和数据库进行查找。
设置2，创建的表和数据库依据语句上格式存放，凡是查找都是转换为小写进行。



MySQL在Linux下数据库名、表名、列名、别名大小写规则是这样的：
1、数据库名、表名、表的别名、变量名是严格区分大小写的；
2、关键字、函数名称在 SQL 中不区分大小写；
3、列名（或字段名）与列的别名（或字段别名）在所有的情况下均是忽略大小写的；

Linux下大小写规则设置

1、停止MySQL服务
2、删除数据目录，即删除 /var/lib/mysql 目录
3、在MySQL配置文件（ /etc/my.cnf ）中添加 lower_case_table_names=1
4、启动MySQL服务

SQL编写建议

1. 关键字和函数名称全部大写；
2. 数据库名、表名、表别名、字段名、字段别名等全部小写；
3. SQL 语句必须以分号结尾。

十三、MySql的数据目录

find / -name mysql

show variables like 'datadir';

MySQL数据库文件的存放路径：/var/lib/mysql/
相关命令目录：/usr/bin（mysqladmin、mysqlbinlog、mysqldump等命令）和/usr/sbin。
配置文件目录：/usr/share/mysql-8.0（命令及配置文件），/etc/mysql（如my.cnf）

数据库和文件系统的关系

mysql：
MySQL 系统自带的核心数据库，它存储了MySQL的用户账户和权限信息，一些存储过程、事件的定义信息，
一些运行过程中产生的日志信息，一些帮助信息以及时区信息等。
information_schema：
MySQL 系统自带的数据库，这个数据库保存着MySQL服务器维护的所有其他数据库的信息，
比如有哪些表、哪些视图、哪些触发器、哪些列、哪些索引。这些信息并不是真实的用户数据，
而是一些描述性信息，有时候也称之为元数据。在系统数据库information_schema中提供了一些
以innodb_sys开头的表，用于表示内部系统表。
performance_schema：
MySQL 系统自带的数据库，这个数据库里主要保存MySQL服务器运行过程中的一些状态信息，可以用来监控 
MySQL 服务的各类性能指标。包括统计最近执行了哪些语句，在执行过程的每个阶段都花费了多长时间，
内存的使用情况等信息。
sys：
MySQL 系统自带的数据库，这个数据库主要是通过视图的形式把information_schema和
performance_schema结合起来，帮助系统管理员和开发人员监控 MySQL 的技术性能。

表在文件系统中的表示

InnoDB存储引擎模式

为了保存表结构，InnoDB在数据目录下对应的数据库子目录下创建了一个专门用于描述表结构的文件，
文件名是这样：

表名.frm

系统表空间（system tablespace）：
默认情况下，InnoDB会在数据目录下创建一个名为ibdata1、大小为12M的文件，这个文件就是对应的系统表空间在
文件系统上的表示。怎么才12M？注意这个文件是自扩展文件，当不够用的时候它会自己增加文件大小。

当然，如果你想让系统表空间对应文件系统上多个实际文件，或者仅仅觉得原来的ibdata1这个文件名难听，
那可以在MySQL启动时配置对应的文件路径以及它们的大小，比如我们这样修改一下my.cnf 配置文件：

[server]innodb_data_file_path=data1:512M;data2:512M:autoextend

独立表空间(file-per-table tablespace)：
在MySQL5.6.6以及之后的版本中，InnoDB并不会默认的把各个表的数据存储到系统表空间中，而是为每一个表建
立一个独立表空间，也就是说我们创建了多少个表，就有多少个独立表空间。使用独立表空间来存储表数据的话，
会在该表所属数据库对应的子目录下创建一个表示该独立表空间的文件，文件名和表名相同，只不过添加了一个
.ibd的扩展名而已，所以完整的文件名称长这样：

表名.ibd

比如：我们使用了独立表空间去存储dbest1数据库下的test表的话，那么在该表所在数据库对应的dbest1目录下会
为test表创建这两个文件：

test.frm
test.ibd
-- 其中test.ibd文件就用来存储test表中的数据和索引。

系统表空间与独立表空间的设置

我们可以自己指定使用系统表空间还是独立表空间来存储数据，
这个功能由启动参数innodb_file_per_table控制，比如说我们想刻意将表数据都存储到系统表空间时，
可以在启动MySQL服务器的时候这样配置：

[server]innodb_file_per_table=0 # 0：代表使用系统表空间； 1：代表使用独立表空间

show variables like 'innodb_file_per_table';

MyISAM存储引擎模式

在存储表结构方面，MyISAM和InnoDB一样，也是在数据目录下对应的数据库子目录下创建了一个
专门用于描述表结构的文件：

表名.frm

在MyISAM中的索引全部都是二级索引，该存储引擎的数据和索引是分开存放的。所以在文件系统中也是使用不同的
文件来存储数据文件和索引文件，同时表数据都存放在对应的数据库子目录下。假如test表使用MyISAM存储引擎的
话，那么在它所在数据库对应的atguigu目录下会为test表创建这三个文件：

test.frm   存储表结构
test.MYD   存储数据 (MYData)
test.MYI   存储索引 (MYIndex)

十四、用户与权限管理

创建用户
CREATE USER语句的基本语法形式如下：
CREATE USER 用户名 [IDENTIFIED BY '密码'][,用户名 [IDENTIFIED BY '密码']];
用户名参数表示新建用户的账户，由用户（User）和主机名（Host）构成；
“[ ]”表示可选，也就是说，可以指定用户登录时需要密码验证，也可以不指定密码验证，这样用户可以直接登录。
不过，不指定密码的方式不安全，不推荐使用。
如果指定密码值，这里需要使用IDENTIFIED BY指定明文密码值。CREATE USER语句可以同时创建多个用户。

CREATE USER zhang3 IDENTIFIED BY '123123';  # 默认host是 %
CREATE USER 'kangshifu'@'localhost' IDENTIFIED BY '123456';

修改用户

UPDATE mysql.user SET USER='li4' WHERE USER='wang5';
FLUSH PRIVILEGES; 

删除用户

设置当前用户密码

十四、索引的数据结构

索引（Index）是帮助MySQL高效获取数据的数据结构。
索引，提高数据检索的效率，降低数据库的IO成本，这也是创建索引最主要的原因。
通过创建唯一索引，可以保证数据库表中每一行数据的唯一性。
在实现数据的参考完整性方面，可以加速表和表之间的连接。
换句话说，对于有依赖关系的子表和父表联合查询时，可以提高查询速度。 
在使用分组和排序子句进行数据查询时，可以显著减少查询中分组和排序的时间，降低了CPU的消耗。


创建索引和维护索引要耗费时间，并且随着数据量的增加，所耗费的时间也会增加。 
虽然索引大大提高了查询速度，同时却会降低更新表的速度。当对表中的数据进行增加、
删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。
索引需要占磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，
存储在磁盘上，如果有大量的索引，索引文件就可能比数据文件更快达到最大文件尺寸。

14.1、聚簇索引：

	使用记录主键值的大侠进行记录和页的排序：
	1.页内的记录是按照主键的大小顺序排成的一个单项列表，
	2.各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表。
	3.存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键
	  大小顺序排成一个双向链表。
优点：
  1.数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，
    因此从聚簇索引中获取数据比非聚簇索引更快；
  2.聚簇索引对于主键的排序查找和范围查找速度非常快
  3.按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，
  	数据库不用从多个数据块中提取数据，所以节省了大量的io操作。
 缺点：
  1.、插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响
      性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键，
  2、更新主键的代价很高，因为将会导致被更新的行移动。
  	 因此，对于InnoDB表，我们一般定义主键为不可更新
  3、二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据

14.2、 二级索引（辅助索引、非聚簇索引）

概念：回表 我们根据这个以c2列大小排序的B+树只能确定我们要查找记录的主键值，所以如果我们想根据c2列的值
查找到完整的用户记录的话，仍然需要到聚簇索引中再查一遍，这个过程称为回表。也就是根据c2列的值查询一条完
整的用户记录需要使用到2棵B+树！

14.3 联合索引

我们也可以同时以多个列的大小作为排序规则，也就是同时为多个列建立索引，
比方说我们想让B+树按照c2和c3列的大小进行排序，这个包含两层含义：
先把各个记录和页按照c2列进行排序。在记录的c2列相同的情况下，采用c3列进行排序注意一点，
以c2和c3列的大小为排序规则建立的B+树称为联合索引，本质上也是一个二级索引。
它的意思与分别为c2和c3列分别建立索引的表述是不同的，
不同点如下：建立联合索引只会建立如上图一样的1棵B+树。为c2和c3列分别建立索引会分别以c2和c3列的大小为
排序规则建立2棵B+树。

十五、InnoDB数据存储结构：