03.MySQL常用知识

1，基础查询

//1.group by+ 聚合函数+ HAVING条件过滤
SELECT SEX, AVG(AGE) FROM `user` GROUP BY SEX HAVING SEX='女'

2，DCL语句

1，创建用户，当前主机访问
CREATE user 'serlyf'@'localhost' IDENTIFIED BY '123456'
2，创建用户，任意主机访问
CREATE user 'serlyf2'@'%' IDENTIFIED BY '123456'
3，修改用户密码（待测试）
ALTER user 'serlyf2'@'%' IDENTIFIED WITH mysql_native_password BY '222222'
4，删除用户
DROP user 'serlyf2'@'%'

3，用户权限

1，查询权限
SHOW GRANTS FOR 'serlyf'@'localhost'
2，授予权限
GRANT ALL ON icmsauto_sc.* TO 'serlyf'@'localhost'
3，撤销权限
GRANT ALL ON icmsauto_sc.* TO 'serlyf'@'localhost'

8，子查询

1，标量子查询( 结果集只有一行一列 )
select * from user where age> (select age from user where name = 'zhangsan')
2，列子查询 ( 结果集只有一列多行 ，常用操作符：IN ， NOT IN，ANY，SOME，ALL)
select * from emp where dept_id in (select * from dept where name='zhangsan' or name='lisi')
3，行子查询 ( 结果集有一行多列,操作符 = <>，IN,NOT IN )
select * from emp where (salary,managerId) =(select salary,managerId from emp where name='zhangsan')
4，表子查询 ( 结果集一般为多行多列 ,操作符in)
select * from emp where (job,salary) in (select job,salary from emp where name='zhangsan' or name = 'lisi')

CREATE INDEX idx_bpm_instance ON bpm_instance(instance_name,type_id)
show INDEX FROM bpm_instance
DROP INDEX idx_bpm_instance ON bpm_instance

11.SQL性能分析

A、查询SQL执行频率查询：
show [session|gloabl] status Like 'Com____';//可以查看当前数据库的增删改查频率

B、慢查询日志：
查询慢查询日志是否开启：
show variable like 'slow_querey_log';

默认慢查询日志是未开启的，需要在MySQL的配置文件（/etc/my.cnf)增加如下配置：
show_query_log = 1
long_query_time = 2

慢查询日志存放位置：
/var/lib/mysql/localhost-slow.log

C、profile详情
show profile 能够在SQL优化时帮助我们了解时间都去哪儿了，通过have_profiling参数，能够看到当前MySQL是否支持，profile操作：
select @@have_profiling;查询是否支持
通过select @@profiling 查看是否开启，默认情况下是关闭的，通过set指令开启[session|gloabal]：
SET profiling = 1 //开启profile功能

通过执行如下指令查询当前数据库的所有sql耗时情况：
show profiles

查询指定query_id的sql语句的各个阶段的耗时情况：
show profile for query query_id;
查看指定query_id的sql语句CPU执行情况：
show profile cpu for query query_id;


D、Explain执行计划

12、索引的使用原则

1，最左前缀法则
如果使用了多列（联合索引）要遵循最左前缀法则，最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引的列，如果跳过某一列，索引将部分失效（后面的字段将失效）
2，范围查询
联合索引，出现范围查询（>、<）范围右侧的列索引失效  

3，索引列运算，索引失效
不要在索引列上使用运算操作，索引将失效

4，字符串类型时，不加引号，索引将失效

5，模糊查询时，如果仅仅是尾部模糊匹配，索引将不会失效，如果是头部模糊索引，索引失效

6，or连接，用or分割的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引不会被用到

7，SQL提示，就是在sql语句中加入一些人为的提示来达到优化操作的目的
select * from user [use|ingor|force] index(ind_user_name) where name='zhangsan'

8, 覆盖索引
尽量使用覆盖索引（查询使用覆盖索引，并且需要返回的列，在该索引中已经全部能找到）减少select *

9，前缀索引
当建立索引的字段是字符串时，字符串的长度太长，建立的索引可能会长，
create index idx_xxxx on table_name(column(n))

15，视图

A,创建视图
create [OR replace] view 视图名称[(列名列表)] AS select语句 [WITH[CASCADED|LOCAL]CHECK OPTION]

B,查询视图sql语句
show create view 视图名称

C,查看视图数据
select  * from 视图名称


D,修改视图
create [OR replace] view 视图名称[(列名列表)] AS select语句 [WITH[CASCADED|LOCAL]CHECK OPTION]
Alter view 视图名称[(列名列表)] AS select语句 [WITH[CASCADED|LOCAL]CHECK OPTION]

E,删除视图
DROP VIEW[IF EXISTS] 视图名称[,视图名称]

17，存储过程

A,创建存储过程
CREATE PROCEDURE 存储过程名称([参数列表])
BEGIN
	--SQL语句
END

B,存储过程调用
CALL 存储过程名称([参数列表])

C,查看存储过程
SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_SCHEMA='icms_ynnx_2'
SHOW CREATE PROCEDURE 存储过程名称

E,删除存储过程
DROP PROCEDURE[IF EXISTS] 存储过程名称；

注意：在命令的执行过程中，执行创建存储过程的sql语句时，需要通过关键字delimiter 指定sql 语句的结束符

21，存储过程-case

CREATE PROCEDURE p2(IN month int,OUT result VARCHAR(10))
BEGIN
	CASE
		WHEN month>=1 AND month<=3 THEN
			SET result := '第一季度';
		WHEN month>=4 AND month<=5 THEN
			SET result := '第二季度';
		WHEN month>=6 AND month<=9 THEN
			SET result := '第三季度';
		WHEN month>=10 AND month<=12 THEN
			SET result := '第四季度';
		ELSE
			SET result := '非法参数';
	END CASE;
END
	CALL p2(3,@result)
	SELECT CONCAT('你输入的月份是',@result);

22，存储过程-whlie

CREATE PROCEDURE p3(IN n int,OUT total int)
BEGIN
	SET total := 0;
	WHILE n >0 DO
		SET total :=total + n;
		SET n := n - 1;
	END WHILE;
END;

CALL p3(100,@total)
SELECT @total

23，存储过程-repeat

CREATE PROCEDURE p4(IN n int,OUT total int)
BEGIN
	SET total := 0;
	REPEAT 
		SET total :=total + n;
		SET n := n - 1;
		UNTIL n<=0
	END REPEAT;
END;

CALL p4(100,@total)
SELECT @total

24，存储过程-loop

CREATE PROCEDURE p5(IN n int,OUT total int)
BEGIN
	SET total := 0;
	sum:LOOP 
		IF n <=0 THEN
			LEAVE sum;
		END IF;
		SET total :=total + n;
		SET n := n - 1;
	END LOOP sum;
END;

CALL p5(100,@total)
SELECT @total



CREATE PROCEDURE p6(IN n int,OUT total int)
BEGIN
	SET total := 0;
	sum:LOOP 
		IF n <=0 THEN
			LEAVE sum;
		END IF;
		IF n%2=1 THEN
			SET n := n - 1;
			ITERATE sum;
		END IF;
		SET total :=total + n;
		SET n := n - 1;
	END LOOP sum;
END;

CALL p6(100,@total)
SELECT @total

25，存储过程-游标

CREATE PROCEDURE p7(IN uAge int)
BEGIN
	DECLARE uname VARCHAR(100);
	DECLARE upro VARCHAR(100);
	DECLARE u_cursor CURSOR for select `name` , profession from tb_user where age>=uAge;
	
	DROP table if EXISTS tb_user_pro;
	CREATE table if not EXISTS tb_user_pro(
		id int PRIMARY key auto_increment,
		`name` VARCHAR(100),
		profession VARCHAR(100)
	);
	
	OPEN u_cursor;
	WHILE true DO
		FETCH u_cursor into uname,upro;
		INSERT into tb_user_pro values(null,uname,upro);
	END WHILE;
	CLOSE u_cursor;
END
	

26，存储过程-handler

CREATE PROCEDURE p7(IN uAge int)
BEGIN
	DECLARE uname VARCHAR(100);
	DECLARE upro VARCHAR(100);
	DECLARE u_cursor CURSOR for select `name` , profession from tb_user where age>=uAge;
	DECLARE exit HANDLER FOR SQLSTATE '02000' CLOSE u_cursor;
	
	DROP table if EXISTS tb_user_pro;
	CREATE table if not EXISTS tb_user_pro(
		id int PRIMARY key auto_increment,
		`name` VARCHAR(100),
		profession VARCHAR(100)
	);
	
	OPEN u_cursor;
	WHILE true DO
		FETCH u_cursor into uname,upro;
		INSERT into tb_user_pro values(null,uname,upro);
	END WHILE;
	CLOSE u_cursor;
END
	

27，存储过程-存储函数

CREATE FUNCTION fun1(n int)
RETURNS INT DETERMINISTIC
BEGIN
	DECLARE total int DEFAULT 0;
	WHILE n >0 DO
		SET total :=total + n;
		SET n := n - 1;
	END WHILE;
	RETURN total;
END;

SELECT fun1(100)

28，触发器

CREATE TRIGGER tb_user_insert_triger
	AFTER INSERT on tb_user for EACH ROW
BEGIN
	INSERT INTO user_log(id,operation,content) VALUES(null,'insert','插入数据')
END;

29，Mysql日志

1，错误日志
默认开启，记录MySQL启动和停止过程中，以及服务器运行过程中的任何严重错误的相关信息，默认存放路径是：/var/log/mysqld.log，查看日志位置
show variables likes '%log_error%'

2,二进制日志
记录了所有的DDL和DML语句，但不包括select和show语句
show variables likes '%log_bin%'
查看二进制日志的格式：
show variables likes '%binlog_format%'
查看二进制文件
mysqlbinlog -v logfilename
删除二进制日志

3，查询日志
记录了客户端的所有操作语句，默认未开启，如果需要开启查询日志：
show variables like '%general%'
开启的话，修改/etc/mysql.cnf文件

4，慢查询日志
slow_query_log=1
long_query_time=2

30.主从复制配置

//主库配置
1，主从配置/etc/my.cnf
server-id=1
read-only=0
2，重启数据库
systemctl restart mysqld
3，登录mysql，创建远程连接的账号，并赋予主从复制权限；
CREATE USER 'serlyf'@'%' IDENTIFIED WITH mysql_native_password BY '123456'；
GRANT REPLICATION SLAVE ON *.* TO 'serlyf'@'%'；
4，查看二进制日志坐标
show master status;

//从库配置
1，主从配置/etc/my.cnf
server-id=2
read-only=1
2，重启数据库
systemctl restart mysqld
3，登录从库，执行如下sql，设置主库配置
CHANGE REPLICATION SOURCE TO SOURCE_HOST='192.168.2.201',SOURCE_USER='serlyf',SOURCE_PASSWORD='123456',SOURCE_LOG_FILE='binlog.000005',SOURCE_LOG_POS=656;
4,开启同步
start replica;
5，查看从库状态
show replica status\G;

测试