MySQL整理

DQL（数据查询语言）: 查询语句，凡是select语句都是DQL。
DML（数据操作语言）：insert delete update，对表当中的数据进行增删改。
DDL（数据定义语言）：create drop alter，对表结构的增删改。
TCL（事务控制语言）：commit提交事务，rollback回滚事务。(TCL中的T是Transaction)
DCL（数据控制语言）: grant授权、revoke撤销权限等。

2、sql语句

2.1、常用sql语句

CRUD操作：Create（增） Retrieve（检索） Update（修改） Delete（删除）

select * from 表名;   

select * from t_staff limit 10;   //取前10条数据

select 字段名1,字段名2,字段名3,.... from 表名;

select  字段,字段... from  表名  where  条件;    //执行顺序：先from，然后where，最后select

select ename from emp where ename like '%O%';   //模糊查询like，%代表任意多个字符

select ename from emp where ename like '_A%';    //模糊查询like，_代表任意1个字符

select ename , sal from emp order by sal asc;  // 升序

select ename , sal from emp order by sal desc;  // 降序

select ename,sal from emp order by sal desc , ename asc;   //按照sal降序排列，当sal相同则按照ename升序排列（注意：越靠前的字段越能起到主导作用。只有当前面的字段无法完成排序的时候，才会启用后面的字段）

分组函数：count：计数
                  sum：求和
                  avg：平均值
                  max：最大值
                  min：最小值
SELECT * FROM s_grade WHERE grade>AVG(grade);   //注意：这样写会报错。Invalid use of group function。这是因为SQL语句当中有一个语法规则，分组函数不可直接使用在where子句当中，因为group by是在where执行之后才会执行的。

count(*)：不是统计某个字段中数据的个数，而是统计总记录条数（和某个字段无关）。      count(comm)：表示统计comm字段中不为NULL的数据总数量。

IFNULL：空处理函数，把null的字段设定为指定值。例：SELECT sno,IFNULL(grade,0) FROM s_grade;

group by 和 having：group by是按照某个字段或者某些字段进行分组。  having是对分组之后的数据进行再次过滤。注意：分组函数一般都会和group by联合使用，而group by一定是和having一起用的

select distinct job from emp;   // distinct关键字去除重复记录。注意：distinct只能出现在所有字段的最前面，“select ename,distinct job from emp;”是错的

//创建一个表
CREATE TABLE DEPT
       (DEPTNO INT(2) NOT NULL ,
	    DNAME VARCHAR(14) ,
	    LOC VARCHAR(13),
	    PRIMARY KEY (DEPTNO)
	   );

//设置表的字符编码（如果想要表支持中文就要设置成utf-8的字符编码）
ALTER TABLE T_STUDENT CONVERT TO CHARACTER SET utf8;

//给表中新增一列
ALTER TABLE t_student ADD COLUMN score INT;

//插入一条数据
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES (10, 'ACCOUNTING', 'NEW YORK'); 

//插入多条数据（用逗号隔开）
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES (50, '张三', 'test1'),(60, 'test2', 'test2'),(70, 'test2', 'test2');

//将查询结果当做表创建出来
CREATE TABLE T_EMP AS SELECT ENAME,SAL FROM EMP ORDER BY SAL DESC LIMIT 0, 5;

//将查询结果插入到一张表中
INSERT INTO T_EMP SELECT ENAME,SAL FROM EMP ORDER BY SAL DESC LIMIT 5, 2;

//修改表中指定的数据
UPDATE T_EMP SET SAL = 2000 WHERE ENAME = 'SCOTT';

//修改表中所有数据
UPDATE T_EMP SET ENAME='test',SAL=0;

//删除表中所有数据
DELETE FROM T_EMP;

//删除表中指定的数据
DELETE FROM T_EMP WHERE ENAME='KING';

//删除整个表
DROP TABLE T_EMP;

 
//创建一个用户  create user '用户名'@'数据库ip' identified by '密码'
create user 'zhangsan'@'localhost' identified by '123';

//删除用户 drop user '用户名'@'IP地址';
drop user 'zhangsan'@'localhost';

//修改用户名 rename user '用户名'@'IP地址' to '新用户名'@'IP地址';
rename user 'zhangsan'@'localhost' to '李四'@'localhost';

//修改密码 set password for '用户名'@'IP地址'=Password('新密码');
set password for 'zhangsan'@'localhost'=Password('456');


//对用户授权
//查看权限
show grants for '用户'@'IP地址'

//授权 zhangsan用户仅对db1.t1文件有查询、插入和更新的操作
grant select ,insert,update on db1.t1 to "zhangsan"@'localhost';

//表示有所有的权限，除了grant这个命令，这个命令是root才有的。zhangsan用户对db1下的t1文件有任意操作
grant all privileges  on db1.t1 to "zhangsan"@'localhost';

//zhangsan用户对db1数据库中的文件执行任何操作
grant all privileges  on db1.* to "zhangsan"@'localhost';

//zhangsan用户对所有数据库中文件有任何操作
grant all privileges  on *.*  to "zhangsan"@'localhost';
 

//取消权限
//取消zhangsan用户对db1的t1文件的任意操作
revoke all on db1.t1 from 'zhangsan'@"localhost";  

//取消来自远程服务器的lisi用户对数据库db1的所有表的所有权限

revoke all on db1.* from 'lisi'@"127.0.0.1";  

//取消来自远程服务器的lisi用户所有数据库的所有的表的权限
revoke all privileges on *.* from 'lisi'@'127.0.0.1';

2.2、连接查询

在实际开发中，大部分的情况下都不是从单表中查询数据，一般都是多张表联合查询取出最终的结果。

连接查询可分为：内连接、外连接、全连接

内连接：等值连接、非等值连接、自连接
外连接：左外连接（左连接）、右外连接（右连接）
全连接

笛卡尔积现象：当两张表进行连接查询的时候，没有任何条件进行限制，最终的查询结果条数是两张表记录条数的乘积。怎么避免笛卡尔积现象？当然是加条件进行过滤。

2.2.1、内连接

等值连接（条件是等量关系）

SELECT * FROM EMP e INNER JOIN DEPT d WHERE e.deptno=d.deptno;
SELECT * FROM EMP e JOIN DEPT d WHERE e.deptno=d.deptno;
SELECT * FROM EMP e,DEPT d WHERE e.deptno=d.deptno;

非等值连接（连接条件中的关系是非等量关系）

SELECT e.ENAME,e.SAL,s.GRADE FROM EMP e INNER JOIN SALGRADE s WHERE e.SAL BETWEEN s.LOSAL AND s.HISAL;

自连接（一张表看做两张表。自己连接自己）

SELECT a.ENAME,b.ENAME  FROM EMP a INNER JOIN EMP b ON a.MGR = b.EMPNO;

2.2.2、外连接

内连接和外连接的区别

内连接：
假设A和B表进行连接，使用内连接的话，凡是A表和B表能够匹配上的记录查询出来，这就是内连接。
AB两张表没有主副之分，两张表是平等的。

外连接：
       假设A和B表进行连接，使用外连接的话，AB两张表中有一张表是主表，一张表是副表，主要查询主表中
       的数据，捎带着查询副表，当副表中的数据没有和主表中的数据匹配上，副表自动模拟出NULL与之匹配。

外连接的分类

左外连接（左连接）：表示左边的这张表是主表。
右外连接（右连接）：表示右边的这张表是主表。

左外连接

SELECT a.ENAME, b.ENAME FROM EMP a LEFT OUTER JOIN EMP b ON a.MGR = b.EMPNO;  //a为主表 b为副表

右外连接

SELECT a.ENAME, b.ENAME FROM EMP a RIGHT OUTER JOIN EMP b ON a.MGR = b.EMPNO;  //b为主表 a为副表

2.2.3、子查询

select语句当中嵌套select语句，被嵌套的select语句是子查询。

子查询可以出现的位置：select后面、from后面、where后面。

where子句中使用子查询

SELECT * FROM EMP WHERE SAL > (SELECT AVG(SAL) FROM EMP);

from后面嵌套子查询

SELECT t.*,s.GRADE FROM (SELECT DEPTNO,AVG(SAL) AS avgsal FROM EMP GROUP BY DEPTNO) t JOIN SALGRADE s ON t.AVGSAL BETWEEN s.LOSAL AND s.HISAL;

select后面嵌套子查询

SELECT e.ENAME,(SELECT d.DNAME FROM DEPT d WHERE e.DEPTNO = d.DEPTNO) AS DNAME FROM EMP e;

2.2.4、union

union的作用是可以将查询结果集相加

//将同一个表的结果拼合在一起
select ename,job from emp where job = 'MANAGER'
union
select ename,job from emp where job = 'SALESMAN';

//相当于：select ename,job from emp where job = 'MANAGER' or job = 'SALESMAN';
//或者：select ename,job from emp where job in('MANAGER','SALESMAN');




//将两张不同表的结果拼合在一起
SELECT ENAME FROM EMP
UNION
SELECT DNAME FROM DEPT;

2.3、limit

limit是mysql特有的，其他数据库中没有，不通用。（Oracle中有一个相同的机制，叫做rownum）。limit的作用是取结果集中的部分数据。注：limit是sql语句最后执行的一个环节

//语法机制：select xxx from 表 limit startIndex, length
//startIndex表示起始位置，从0开始，0表示第一条数据。length表示取几个

//取前5条记录
SELECT ENAME,SAL FROM EMP ORDER BY SAL DESC LIMIT 0, 5;
SELECT ENAME,SAL FROM EMP ORDER BY SAL DESC LIMIT 5;

2.4、约束(Constraint)

在创建表的时候，可以给表的字段添加相应的约束，添加约束的目的是为了保证表中数据的合法性、有效性、完整性。
常见的约束有：

非空约束(not null)：约束的字段不能为NULL
唯一约束(unique)：约束的字段不能重复
主键约束(primary key)：约束的字段既不能为NULL，也不能重复（简称PK）
外键约束(foreign key)：一个表的外键是参照另一个表的主键建立的（简称FK）
检查约束(check)：mysql的常用engine是InnoDB，在该引擎下使用check约束是无效的。但是可以通过使用触发器来做到检查约束的效果。（MySQL 8.0.16版本以后是支持check约束的）

2.4.1、非空约束（not null)

CREATE TABLE DEPT(
    DEPTNO INT(2) NOT NULL ,
	DNAME VARCHAR(14) ,
	LOC VARCHAR(13),
	PRIMARY KEY (DEPTNO)
	);

注意：not null约束只有列级约束。没有表级约束

2.4.2、唯一性约束（unique）

唯一约束修饰的字段具有唯一性，不能重复。但可以为NULL。

//给某一列添加unique
CREATE TABLE T_USER(
             id INT,
             username VARCHAR(255) UNIQUE  // 列级约束
	);

//给两个列或者多个列添加unique
CREATE TABLE T_USER(
	     id INT, 
	     usercode VARCHAR(255),
             username VARCHAR(255),
	     UNIQUE(usercode,username) // 多个字段联合起来添加1个约束unique 【表级约束】
	);

2.4.3、主键约束

主键字段中的数据不能为NULL，也不能重复。

主键的分类：
根据主键字段的字段数量来划分：

单一主键（推荐的，常用的。）
复合主键(多个字段联合起来添加一个主键约束)（复合主键不建议使用，因为复合主键违背三范式）

根据主键性质来划分：

自然主键：主键值最好就是一个和业务没有任何关系的自然数。（这种方式是推荐的）
业务主键：主键值和系统的业务挂钩，例如：拿着银行卡的卡号做主键，拿着身份证号码作为主键。（不推荐用）最好不要拿着和业务挂钩的字段作为主键。因为以后的业务一旦发生改变的时候，主键值可能也需要随着发生变化，但有的时候没有办法变化，因为变化可能会导致主键值重复。

注：一张表的主键约束只能有1个

//单一主键
CREATE TABLE T_USER(
		id INT PRIMARY KEY,  // 列级约束
		username VARCHAR(255),
		email VARCHAR(255)
		);

CREATE TABLE T_USER(
		id INT,
		username VARCHAR(255),
		PRIMARY KEY(id)    //表级约束
		);


//复合主键
CREATE TABLE T_USER(
		id INT,
		username VARCHAR(255),
		pwd VARCHAR(255),
		PRIMARY KEY(id,username)  //复合主键
		);

mysql提供主键值自增：AUTO_INCREMENT（id字段自动维护一个自增的数字，从1开始，以1递增）

CREATE TABLE T_USER(
		d INT PRIMARY KEY AUTO_INCREMENT, // id字段自动维护一个自增的数字，从1开始，以1递增
		username VARCHAR(255)
		);

2.4.4、外键约束

CREATE TABLE T_CLASS(
		cno INT,
		cname VARCHAR(255),
		PRIMARY KEY(cno)
		);

CREATE TABLE T_STUDENT(
		sno INT,
		sname VARCHAR(255),
		classno INT,
		PRIMARY KEY(sno),
		FOREIGN KEY(classno) REFERENCES T_CLASS(cno)  //参照表T_CLASS中cno字段建立的外键
		);

T_CLASS为父表，T_STUDENT为子表，子表中的字段参照父表建立外键。

顺序要求：

删除数据的时候，先删除子表，再删除父表。
添加数据的时候，先添加父表，在添加子表。
创建表的时候，先创建父表，再创建子表。
删除表的时候，先删除子表，在删除父表。

注意：外键可以为NULL，外键字段引用其他表的某个字段的时候，被引用的字段不一定必须是主键，但至少具有unique约束。

3、存储引擎

存储引擎：mysql中的数据用各种不同的技术存储在文件或者内存中。

SHOW ENGINES;  //查看当前数据库的所有存储引擎

InnoDB支持事务，而MEMORY、MyISAM等是不支持事务的。

4、MySQL事务

4.1、事务的ACID（acid)属性

原子性（Atomicity）：事务是最小的工作单元，不可再分。
一致性（Consistency）：事务必须保证多条DML语句同时成功或者同时失败。
隔离性（Isolation）：事务A与事务B之间具有隔离。
持久性（Durability）：持久性说的是最终数据必须持久化到硬盘文件中，事务才算成功的结束。

隐式事务：事务没有明显的开启和结束的标记。

显示事务：事务有明显的开启和结束标记，前提是必须先设置自动提交为禁用。

SHOW VARIABLES LIKE 'autocommit';   //查看自动提交状态（on开启，off关闭）

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

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

事务的使用：

SET autocommit=0;
START TRANSACTION;  //开启事务
UPDATE T_STUDENT SET phone='123456' WHERE sname='张三';
UPDATE T_STUDENT SET phone='123456' WHERE sname='李四';
COMMIT;   //提交事务

ROLLBACK;  //事务回滚

4.2、事务并发问题

对于同时运行的多个事务，如果不采用隔离机制就会导致各种并发问题。

脏读：对于两个事务T1,T2。T1读取了已经被T2更新但还没有被提交的字段之后，若T2回滚，T1读取的内容就是临时且无效的。
不可重复读：对于两个事务T1,T2。T1读取了一个字段，然后T2更新了该字段之后，T1再次读取同一个字段，值就不同了。
幻读：对于两个事务T1,T2。T1从一个表中读取了一个字段，然后T2在该表中插入了一些新的行，之后如果T1再次读取同一个表就会多出几行。

数据库事务的隔离性：数据库系统必须具有隔离并发运行各个事务的能力，使它们不会相互影响，避免各种并发问题。

事务的隔离级别
	脏读	不可重复读	幻读
read uncommitted	√	√	√
read committed	×	√	√
repeatable read	×	×	√
serializable	×	×	×

select @@tx_isolation;  //查看事务的隔离级别

set session transaction isolation level read committed;  //设置事务的隔离级别

select @@global.tx_isolation;  //查看事务的全局隔离级别

set global transaction isolation level read uncommitted;  //设置事务的全局隔离级别

4.2.1、read uncommitted

Read Uncommitted，顾名思义，就是读未提交，也就是说事务所作的修改在未提交前，其他并发事务是可以读到的。

参考：https://blog.csdn.net/oyw5201314ck/article/details/79621347

4.2.2、read committed

Read Committed，顾名思义，就是读已提交，一个事务只能看到其他并发的已提交事务所作的修改。很显然，该级别可以解决Read Uncommitted中出现的“脏读“问题。除了Mysql，很多数据库都以Read Committed作为默认的事务隔离级别。

参考：https://blog.csdn.net/oyw5201314ck/article/details/79621954

4.2.3、repeatable read

Repeatable Read，顾名思义，可重复读，也即在一个事务范围内相同的查询会返回相同的数据。

参考：https://blog.csdn.net/oyw5201314ck/article/details/79622649

4.2.4、serializable

Serializable，顾名思义，可串行化的，也即并发事务串行执行。很显然，该级别可以避免前面讲到的所有问题：“脏读”、“不可重复读”和“幻读”。代价是处理事务的吞吐量低，严重浪费数据库的性能，因此要慎用此事务隔离级别。

参考：https://blog.csdn.net/oyw5201314ck/article/details/79623253

4.2.5、savepoint

savepoint的作用是设置保存点，通常会和rollback连用，让事务恢复到上一个保存点的状态。

//savepoint 设置保存点
SET autocommit=0;
START TRANSACTION; //开启事务
DELETE FROM t_student WHERE id=4;
SAVEPOINT a;
DELETE FROM t_student WHERE id=3;
ROLLBACK TO a;
COMMIT;

5、视图

MySQL从5.0.1版本开始提供视图功能。一种虚拟存在的表，行和列的数据是在使用视图时动态生成的，只保存了sql逻辑，不保存查询结果。

//创建一个视图
CREATE VIEW v_t_student AS SELECT * FROM t_student WHERE score>0;

//修改一个视图整个显示的内容
CREATE OR REPLACE VIEW v_t_student AS SELECT AVG(score) FROM t_student  WHERE score>0;

//修改一个视图整个显示的内容
ALTER VIEW v_t_student AS SELECT AVG(score) FROM t_student  WHERE score>0;

//删除视图 drop view 视图1,视图2,视图3......;
DROP VIEW v_t_student;

//查看视图（视图的表结构）
DESC v_t_student;

//查看视图（视图的逻辑sql语句）
SHOW CREATE VIEW v_t_student;

//修改视图某个字段的值（注意：修改视图某个字段的值会把这个视图源自于的那个表的相关字段的值也一并修改无论当前账户是否有修改某个表的权限，只要该账户有修改表的衍生视图的权限，那么该账户就可以通过修改视图把表也修改了）
UPDATE v_t_student SET score=100 WHERE sname='张三';

注：修改视图某个字段的值会把这个视图源自于的那个表的相关字段的值也一并修改，无论当前账户是否有修改某个表的权限，只要该账户有修改表的衍生视图的权限，那么该账户就可以通过修改视图把表也修改了。

具备以下特点的视图不允许更新：

包含以下关键字的sql语句：分组函数、distinct、group by、having、union或者union all
常量视图

//创建一个常量视图
CREATE OR REPLACE VIEW v_param AS SELECT 'AAA' lname;

SELECT * FROM v_param;

select中包含子查询
join连接的多个表（可以update，但不可以insert）
where子句中的子查询引用了from子句中的表

CREATE OR REPLACE VIEW v_sss AS SELECT sno,sname,cno FROM t_student WHERE cno IN(SELECT cno FROM t_class);  //where子句中的子查询引用了from子句

6、变量

6.1、系统变量

系统变量是由系统提供的，不是用户定义的，属于服务器层面。全局级别：GLOBAL。会话级别：SESSION。默认是SESSION。

//查看所有系统变量
SHOW GLOBAL VARIABLES;
SHOW SESSION VARIABLES;

//查看满足条件的部分系统变量
SHOW GLOBAL VARIABLES LIKE '%char%';
SHOW SESSION VARIABLES LIKE '%char%';

//查看某个指定系统变量的值
SELECT @@GLOBAL.autocommit;
SELECT @@SESSION.autocommit;

//为某个系统变量赋值
SET @@GLOBAL.autocommit=0;
SET @@SESSION.autocommit=0;

6.2、自定义变量

自定义变量是用户自己定义的，分为用户变量和局部变量。

	作用域	定义和使用的位置	语法
用户变量	当前会话	会话中的任何地方	必须加@符号，不用限定类型
局部变量	begin end	只能在begin end中，且必须为第一句话	一般不用加@符号，需要限定类型

//自定义用户变量
SET @x=1;
SET @y=2;
SET @z=@x+@y;
SELECT @z;

//自定义局部变量
DELIMITER $
CREATE PROCEDURE p0()
BEGIN   
DECLARE a INT;   
DECLARE b INT; 
DECLARE c INT;
SET a = 5;  
SET b = 15;
SET c=a+b;
END $

7、存储过程

存储过程为一组预先编译好的sql语句的集合，可以理解为批处理语句。

优点：

提高代码的重用性
简化操作
减少编译次数并减少了和数据库服务器连接的次数，提高了效率

//创建一个空参存储过程
DELIMITER $   //定义介素符$
CREATE PROCEDURE p0()
BEGIN
INSERT INTO t_student(sno,sname,score) VALUES('20160009','陈八',20);
END $

CALL p0;  //执行存储过程p0


//创建一个带输入参数的存储过程
DROP PROCEDURE p1;
DELIMITER $
CREATE PROCEDURE p1(IN s_name NVARCHAR(30))   //这里要注意，如果参数为中文，就要改成nvarchar类型
BEGIN
SELECT * FROM t_student WHERE sname = s_name;
END $

CALL p1('张三');  //执行存储过程p1


//创建登录存储过程
DELIMITER $
CREATE PROCEDURE p_login(IN u_name VARCHAR(10),IN p_wd VARCHAR(10))
BEGIN
DECLARE result INT DEFAULT 0;
SELECT COUNT(*) INTO result FROM t_login WHERE uname=u_name AND pwd=p_wd;
SELECT IF (result>0,'成功','失败');
END $

CALL p_login('AAA','123');


//创建带输入和输出参数的存储过程
DELIMITER $
CREATE PROCEDURE p2(IN s_no INT,OUT s_name NVARCHAR(20),OUT s_core INT)
BEGIN
SELECT sname,score INTO s_name,s_core FROM t_student WHERE sno=s_no;
END $

SET @s_name='ww',@s_core=0;
CALL p2(20160001,@s_name,@s_core);
SELECT @s_name,@s_core;


//创建带输入输出参数的存储过程
DELIMITER $
CREATE PROCEDURE p3(INOUT a INT,INOUT b INT,INOUT c INT)
BEGIN
SET a=a+1;
SET b=b+1;
SET c=a+b;
END $

SET @a=1,@b=2,@c=3;
CALL p3(@a,@b,@c);
SELECT @a,@b,@c;

SHOW CREATE PROCEDURE p3;  //查看存储过程

DROP PROCEDURE p2;  //删除存储过程

8、函数

//创建一个无参有返回值的函数
DELIMITER $
CREATE FUNCTION f0() RETURNS INT
BEGIN
DECLARE a INT DEFAULT 0;
SELECT COUNT(*) INTO a FROM t_student;
RETURN a;
END $

SELECT f0();  //调用函数


//创建一个有参数有返回值的函数
DROP FUNCTION f1;
DELIMITER $
CREATE FUNCTION f1(s_name NVARCHAR(20)) RETURNS INT
BEGIN
SET @s_core=0;
SELECT score INTO @s_core FROM t_student WHERE sname=s_name;
RETURN @s_core;
END $

SELECT f1('张三');


SHOW CREATE FUNCTION f0;  //查看函数

DROP FUNCTION f1;  //删除函数

9、结构控制

9.1、分支结构

DELIMITER $
CREATE PROCEDURE p4(IN score INT)
BEGIN 
CASE 
    WHEN score>=90 AND score<=100 THEN SELECT 'A';
    WHEN score>=80 THEN SELECT 'B';
    WHEN score>=60 THEN SELECT 'C';
ELSE SELECT 'D';
END CASE;
END $

CALL p4(95);

9.2、循环结构

//批量插入t_student表，如果次数大于20则停止插入（使用while实现循环）
DELIMITER $
CREATE PROCEDURE p5(IN insertCount INT)
BEGIN 
DECLARE i INT DEFAULT 1;
DECLARE sno_start INT DEFAULT 20160009;
a:WHILE i<=insertCount 
DO
INSERT INTO t_student(sno,sname,score) VALUES (sno_start+i,CONCAT('学生',i),0);
IF (i>=20) THEN LEAVE a;  //当i>=20时，跳出循环
END IF;
SET i=i+1;
END WHILE a;
END $

CALL p5(100);

SELECT * FROM t_student;


//批量插入t_student表，如果次数大于20则停止插入（使用loop实现循环）
DELIMITER $
CREATE PROCEDURE p6(IN insertCount INT)
BEGIN 
DECLARE i INT DEFAULT 1;
DECLARE sno_start INT DEFAULT 20160030;
a:LOOP 
INSERT INTO t_student(sno,sname,score) VALUES (sno_start+i,CONCAT('学生',i),0);
IF (i>=20) THEN LEAVE a;  //当i>=20时，跳出循环
END IF;
SET i=i+1;
END LOOP a;
END $

CALL p6(100);
SELECT * FROM t_student;


//批量插入t_student表，如果次数大于20则停止插入（使用repeat实现循环）
DELIMITER $
CREATE PROCEDURE p7(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE sno_start INT DEFAULT 20160050;
a:REPEAT
INSERT INTO t_student(sno,sname,score) VALUES (sno_start+i,CONCAT('学生',i),0);
IF (i>=20) THEN LEAVE a;
END IF;
SET i=i+1;
UNTIL i>insertCount     //注意：这里until结束循环条件后面不能加';'
END REPEAT a;
END $

CALL p7(100);
SELECT * FROM t_student;

10、MySQL索引

10.1、概念

数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。我们平常所说的索引，如果没有特别指明，都是指B树(多路搜索树，并不一定是二叉的)结构组织的索引。其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种类型的索引之外，还有哈稀索引(hash index)等。

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。索引的目的在于提高查询效率，可以类比字典。

索引优点：通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。

索引缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。因此索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

索引示例：通过建立索引，每次查询一个数最多只要访问三次内存就可以查到数。

索引通常会采用B树实现，因为B树会自动根据两边的情况自动调节，使两端无限趋近于平衡状态。可以使性能最稳定。但B树也有弊端就是当插入/修改操作过多时，B树会不断调整平衡，消耗性能，因此索引不是越多越好。

10.2、数据库索引使用B树和B+树的区别

这是一棵B树，索引使用b树的优点在于可以大大的提高查找效率，无论查找哪个，都最多只需访问三次内存就可以搞定，B树的每一个节点都包含key和value，因此经常访问的元素可能离根节点更近，因此访问也更迅速。

这是一颗B+树，构造和B树很相似，只是B+树的叶子节点是由链表串起来的。在B+树中只有叶子节点中存放value，其他的分支节点都存放了key。和B树相比，如果只是查找单个数据的话，B树的效率似乎更加高一些，但如果是范围查找的话，B+树的效率要远远高于B树。因为如果是B+树查找索引在17到35之间的数据的话，B+树先遍历一遍B+树查找到10，然后就一个单链表的遍历，从10遍历到37，最终查出10和21，只要遍历一次B+树即可。如果是B树查找17到35之间的数据的话，就要第一次遍历B树找到10，再遍历一次B树找到21，等于说要遍历两次B树。

10.3、索引建立

alter table t_staff add primary key t_staff(no);  //给t_staff的no字段建立主键索引

create unique index name_index on t_staff(name);   //创建唯一性索引
alter table t_staff add unique index name_index(name);

drop index name_index on t_staff;   //删除索引

create index name_no_index on t_staff(no,name);   //创建复合索引name,no
alter table t_staff add index no_name_index(no,name);

show index from t_staff   //查看表的索引

哪些情况需要创建索引：

主键自动建立唯一索引
频繁作为查询条件的字段应该创建索引(where 后面的语句)
查询中与其它表关联的字段，外键关系建立索引
查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
查询中统计或者分组字段

哪些情况不要创建索引：

表记录太少
经常增删改的表（因为索引虽然可以提高查询效率，但却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件
where条件里用不到的字段不创建索引注意：如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果

10.4、索引优化

10.4.1、explain

使用explain关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的，分析你的查询语句或是表结构的性能瓶颈。

SELECT识别符。这是SELECT的查询序列号，表示查询中执行select子句或操作表的顺序

select_type

SIMPLE：简单SELECT(不使用UNION或子查询)
PRIMARY：最外面的SELECT
UNION：UNION中的第二个或后面的SELECT语句
DEPENDENT UNION：UNION中的第二个或后面的SELECT语句,取决于外面的查询
UNION RESULT：UNION 的结果
SUBQUERY：子查询中的第一个SELECT
DEPENDENT SUBQUERY：子查询中的第一个SELECT,取决于外面的查询
DERIVED：导出表的SELECT(FROM子句的子查询)

table

输出的行所引用的表

partitions

官方定义为The matching partitions（匹配的分区），该字段应该是看table所在的分区吧。值为NULL表示表未被分区。

type

表示查询所使用的访问类型，type的值主要有八种，该值表示查询的sql语句好坏，从最好到最差依次为：system>const>eq_ref>ref>range>index>ALL。
type = system：表只有一行记录（等于系统表）
type = const：表示通过索引一次就找到了,const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。
type = ref：非唯一性索引扫描，返回匹配某个单独值的所有行。
type = eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
type = range：只检索给定范围的行（where后面限定查询范围）使用一个索引来选择行。
type = index：遍历全表，不过index是通过索引读取全表。如：select id from xxx(id为索引)。
type = all：遍历全表，不同于index，all是从硬盘中读取全表。

key_len：索引的长度，key_len表示实际使用到的索引的字节数。

ref

显示索引的哪一列被用到了。
ref = const
ref = 表.列名

rows：rows列显示MySQL认为它执行查询时必须检查的行数。

Extra：包含不适合在其他列中显示但十分重要的额外信息。

Extra = Using filesort：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。
Extra = Using temporary：使了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。
Extra = Using where：表明使用了where过滤。
Extra = Using join buffer：使用了连接缓存
Extra = no matching row in const table：没有匹配到数据

10.4.2、索引使用注意事项

不要在索引列上做任何操作（计算、函数、类型转换），因为这样是会使索引失效。
尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少使用select *。
mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描。
is not null 也无法使用索引,但是is null是可以使用索引的。
like以通配符开头('%abc...')mysql索引失效会变成全表扫描的操作。
字符串不加单引号索引失效。
少用or,用它来连接时会索引失效。

11、Show Profile

Show Profile是mysql提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优的测量。

//看看当前的mysql版本是否支持
Show variables like 'profiling';

//开启Show Profile
set profiling=1;

//查看结果
show profiles;

//诊断SQL
show profile cpu,block io for query 2;

注意以下几种状态！！！

converting HEAP to MyISAM 查询结果太大，内存都不够用了往磁盘上搬了。
Creating tmp table 创建临时表。
Copying to tmp table on disk 把内存中临时表复制到磁盘，危险！！！
locked。

12、Mysql锁机制

定义：锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

Mysql中的锁按操作类型可分为：

读锁：读锁又叫“共享锁”，针对同一份数据，多个读操作可以同时进行而不会互相影响。
写锁：写锁又叫“排它锁”，当前写操作没有完成前，它会阻断其他写锁和读锁。

Mysql中的锁按锁粒度可分为：

表锁：
行锁：

在给数据库中的表加锁要用到数据引擎，Mysql中常见的数据引擎为“Myisam”和“ Innodb”。有关“Myisam”和“ Innodb”的详细区别具体可以参考这篇文章：https://blog.csdn.net/qq_35642036/article/details/82820178

简单来说，如果表中有大量的查询操作，就首选Myisam。如果表中有比较多的数据更新操作，那么首选Innodb。

12.1、表锁（读锁&写锁）

//读锁：使用Myisam这个数据引擎建表
create table mylock(test_id int,test_context varchar(50))engine Myisam;

//添加读锁
lock table mylock read;

//给表加写锁
lock table mylock write;

//释放锁
unlock tables;

读锁&写锁
锁类型	自己可读	自己可写	自己可读其他表	自己可写其他表	他人可读	他人可写	他人可读其他表	他人可写其他表
读锁	是	否	否	否	是	否	是	是
写锁	是	是	否	否	否	否	是	是

12.2、行锁（偏写）

12.2.1、建立行锁

行锁偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。

//使用行锁需在建表时使用innodb引擎
create table t_rowLock(id int,txt varchar(50)engine=innodb;

//为了使行锁生效，需关闭自动提交
set autocommit=0;

//InnoDB行锁是通过给索引上的索引项加锁来实现的
alter table t_rowLock add index id_index(id);
alter table t_rowLock add index txt_index(txt);

nnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。 InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

12.2.2、间歇锁

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，

InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（GAP Lock）。

间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害。

12.2.3、行锁分析

通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况。

//行锁分析
show status like 'innodb_row_lock%';

12.2.4、Mysql锁的优化建议

尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁。
尽可能较少检索条件，避免间隙锁。
尽量控制事务大小，减少锁定资源量和时间长度。
锁住某行后，尽量不要去调别的行或表，赶紧处理被锁住的行然后释放掉锁。
涉及相同表的事务，对于调用表的顺序尽量保持一致。
在业务环境允许的情况下,尽可能低级别事务隔离。

13、主从复制

MySQL主从复制就是将两台服务器（一台为主服务器，一台为从服务器）上的MySQL相关联起来。它们享有对同一个数据库的操作。相当于负载均衡，如果一台服务器宕机了也不会影响该数据库的正常使用。该数据库可以在另一个服务器上正常运行。

有关主从复制（两个linux服务器）的步骤可参考这篇文章：https://blog.csdn.net/qq_41772936/article/details/80380950

MySQL复制过程分成三步：

master将改变记录到二进制日志（binary log）。这些记录过程叫做二进制日志事件，binary log events。
slave将master的binary log events拷贝到它的中继日志（relay log）。
slave重做中继日志中的事件，将改变应用到自己的数据库中。 MySQL复制是异步的且串行化的。

14、参考资料

金斗潼关

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
2
评论
MySQL整理

1、概念1.1、sql、DB、DBMS分别是什么，他们之间的关系？DB：DataBase（数据库，数据库实际上在硬盘上以文件的形式存在）DBMS：DataBase Management System（数据库管理系统，常见的有：MySQL Oracle DB2 Sybase SqlServer...）SQL：结构化查询语言，是一门标准通用的语言。标准的sql适合于所有的数据库产品。 SQL属于高级语言。只要能看懂英语单词的，写出来的sql语句，可以读懂什么意思。 ...
复制链接

扫一扫