mysql根据姓氏笔画查询信息

文章目录

• 一、mysql DQL使用方法

• 1.1 单表查询

• 1.1.1 as的使用
• 1.1.2 where的用法
• 1.1.3 distinct 去除查询结果中，特定字段中重复行的记录
• 1.1.4 函数的使用
• 1.1.5 分组统计 group by 的使用
• 1.1.6 having的使用
• 1.1.7 order by 排序用法
• 1.1.8 limit的使用
• 1.1.9 in的用法
• 1.1.10 总结

• 1.2 多表查询

• 1.2.0 super 理解外连接专用图
• 1.2.1 纵向合并 union
• 1.2.2 横向交叉连接 cross join
• 1.2.3 内连接 inner join on
• 1.2.4 左外连接 left join on
• 1.2.6 右外链接 right join on
• 1.2.6 完全外连接
• 1.2.7 外连接后一表独有的内容
• 1.2.8 子查询
• 1.2.9 自连接
• 1.2.10 三表查询

• 二、视图|函数|存储过程|触发器

• 2.1视图的使用

• 2.1.1 使用场景：
• 2.1.2 缺点：
• 2.1.3 语法：
• 2.1.4 注意：
• 2.1.5 示例：

• 2.2 函数

• 2.2.1 分类：
• 2.2.2 自定义函数介绍
• 2.2.3 自定义函数使用示例

• 2.2.3.1 创建无参函数
• 2.2.3.2 创建有参函数

• 2.2.4 函数的基本操作

• 2.2.4.1 调用函数

• 2.3 触发器

• 2.3.1定义：
• 2.3.2 触发器创建的四个要素
• 2.3.3 创建触发器语法
• 2.3.4 创建触发器示例
• 2.3.5 触发器的基操

• 2.3.5.1 查看触发器器
• 2.3.5.2 删除触发器

• 2.4 存储过程

• 2.4.1 概念
• 2.4.2 存储过程优势
• 2.4.3 存储过程与自定义函数的区别
• 2.4.4 存储过程的创建

• 2.4.4.1语法：
• 2.4.4.2 示例

• 2.4.5 存储过程的基操

• 2.4.5.1查看已有的存储过程
• 2.4.5.2 删除存储过程
• 2.4.5.3调用存储过程
• 2.4.5.4 查看存储过程的定义

• 三、mysql帐户与授权

• 3.1 mysql 用户

• 3.1.1存放用户信息的表
• 3.1.2 用户名的构成方式
• 3.1.3 用户管理

• 3.1.3.1 创建用户
• 3.1.3.2 用户重命名
• 3.1.3.3 删除用户
• 3.1.3.4 修改用户密码

• 3.1.4 忘记sql密码怎么办

• 3.2 MySQL 权限管理

• 3.2.1 权限类别

• 3.2.1.1 管理类
• 3.2.1.2 程序类 FUNCTION、PROCEDURE、TRIGGER
• 3.2.1.3 库和表级别：DATABASE、TABLE

• 3.2.3 授权

• 3.2.3.1 授权语法

• 3.2.4 回收授权
• 3.2.5 查看用户获得的授权
• 3.2.6 注意

• 四、存储引擎

• 4.1 MyISAM

• 4.1.1 MyISAM引擎特点
• 4.1.2 MyISAM存储引擎适用场景
• 4.1.3 MyISAM引擎文件

• 4.2 InnoDB

• 4.2.1 InnoDB引擎特点
• 4.2.3 InnoDB数据库文件

• 4.3 其他存储引擎
• 4.4 管理存储引擎

• 4.4.1查看mysql支持的存储引擎
• 4.4.2 查看当前默认的存储引擎
• 4.4.3 设置默认的存储引擎
• 4.4.4查看库中所有表使用的存储引擎
• 4.4.5查看库中指定表的存储引擎
• 4.4.6 设置表的存储引擎：

• 五、服务器选项与变量

• 5.1 查看选项变量的帮助文档
• 5.2 变量的分类以及选项的区别关系
• 5.3 服务器选项

• 5.3.1获取mysqld的可用选项列表：
• 5.3.2 设置服务器选项方法：

• 5.4 服务器变量

• 5.4.1查询变量

• 5.4.1.1查询系统变量
• 5.4.1.2 查询状态变量

• 5.4.2 修改系统变量的值：

• 5.5 特殊的服务器变量 sql_mode

• 六、mysql缓存与性能优化

• 6.1缓存

• 6.1.1 查询缓存（ Query Cache ）原理
• 6.1.2 查询缓存优缺点
• 6.1.3 哪些查询可能不会被缓存
• 6.1.4 查询缓存相关的服务器变量
• 6.1.5 query_cache_type参数变量 的详细介绍
• 6.1.6 SELECT语句的缓存控制

• 6.2 通过缓存进行性能优化

• 6.2.1 查询缓存相关的状态变量：
• 6.2.2 命中率和内存使用率估算

• 6.3 缓存优化的流程
• 6.4 示例

• 6.4.1 开启缓存并且设置缓存空间大小
• 6.4.2 验证

一、mysql DQL使用方法

1.1 单表查询

1.1.1 as的使用

• as 为列取别名

MariaDB [hellodb]>  select NAME  as 姓名,age ,stuid from students ;
+---------------+-----+-------+
| 姓名          | age | stuid |
+---------------+-----+-------+
| Shi Zhongyu   |  22 |     1 |
| Shi Potian    |  22 |     2 |
| Xie Yanke     |  53 |     3 |
| Ding Dian     |  32 |     4 |

• as 为列取别名,通常多表使用，单表没有效果

MariaDB [hellodb]> select stuid , NAME as 姓名 from students  as 学生表;
+-------+---------------+
| stuid | 姓名          |
+-------+---------------+
|     1 | Shi Zhongyu   |
|     2 | Shi Potian    |
|     3 | Xie Yanke     |
|     4 | Ding Dian     |
|     5 | Yu Yutong     |

• 省略as

MariaDB [hellodb]>  select NAME  姓名 ,age  年龄 ,stuid from students ;
+---------------+--------+-------+
| 姓名          | 年龄   | stuid |
+---------------+--------+-------+
| Shi Zhongyu   |     22 |     1 |
| Shi Potian    |     22 |     2 |
| Xie Yanke     |     53 |     3 |
| Ding Dian     |     32 |     4 |
| Yu Yutong     |     26 |     5 |

1.1.2 where的用法

WHERE子句：指明过滤条件以实现“选择”的功能：

过滤条件：布尔型表达式
算术操作符：+, -, *, /, %
比较操作符：=,<=>（相等或都为空）, <>, !=(非标准SQL), >, >=, <, <= BETWEEN min_num AND max_num
IN (element1, element2, …)
IS NULL
IS NOT NULL

• 查询编号小于3的学生

MariaDB [hellodb]> select stuid 编号 , NAME as 姓名 from students as s   where s.stuid <3;
+--------+-------------+
| 编号   | 姓名        |
+--------+-------------+
|      1 | Shi Zhongyu |
|      2 | Shi Potian  |
+--------+-------------+
2 rows in set (0.00 sec)

• 配合and使用

MariaDB [hellodb]> select stuid 编号 , NAME as 姓名 from students as s   where s.stuid <4 and s.stuid >2;
+--------+-----------+
| 编号   | 姓名      |
+--------+-----------+
|      3 | Xie Yanke |
+--------+-----------+
1 row in set (0.00 sec)

• between 与 and一起使用 结果会包括2和4

MariaDB [hellodb]> select stuid 编号 , NAME as 姓名 from students as s   where s.stuid   between 2 and 4;
+--------+------------+
| 编号   | 姓名       |
+--------+------------+
|      2 | Shi Potian |
|      3 | Xie Yanke  |
|      4 | Ding Dian  |
+--------+------------+
3 rows in set (0.00 sec)

• 单独用and，达到between …and… 的效果

MariaDB [hellodb]> select stuid 编号 , NAME as 姓名 from students as s   where s.stuid <=4 and s.stuid >=2;
+--------+------------+
| 编号   | 姓名       |
+--------+------------+
|      2 | Shi Potian |
|      3 | Xie Yanke  |
|      4 | Ding Dian  |
+--------+------------+
3 rows in set (0.01 sec)

1.1.3 distinct 去除查询结果中，特定字段中重复行的记录

select age from students 之前25列，之后15列

MariaDB [hellodb]> select  distinct age from students ;
+-----+
| age |
+-----+
|  22 |
|  53 |
|  32 |
|  26 |
|  46 |
|  19 |
|  17 |
|  20 |
|  23 |
|  33 |
|  21 |
|  25 |
|  18 |
|  27 |
| 100 |
+-----+
15 rows in set (0.00 sec)

1.1.4 函数的使用

统计students表的行数

MariaDB [hellodb]> select count(*) as 记录数 from students ;
+-----------+
| 记录数     |
+-----------+
|        25 |
+-----------+
1 row in set (0.00 sec)

1.1.5 分组统计 group by 的使用

• group by 单独使用 不同性别的平均年龄

MariaDB [hellodb]> select gender 性别,avg(age) as 平均年龄 from students group by gender;
+--------+--------------+
| 性别    | 平均年龄      |
+--------+--------------+
| F      |      19.0000 |
| M      |      33.0000 |
+--------+--------------+
2 rows in set (0.00 sec)

• 当和where 一起使用时
  where在前分组在后,否则出错

MariaDB [hellodb]> select classid , avg(age) from students group by classid;
+---------+----------+
| classid | avg(age) |
+---------+----------+
|    NULL |  63.5000 |
|       1 |  20.5000 |
|       2 |  36.0000 |
|       3 |  20.2500 |
|       4 |  24.7500 |
|       5 |  46.0000 |
|       6 |  20.7500 |
|       7 |  19.6667 |
+---------+----------+
8 rows in set (0.00 sec)


MariaDB [hellodb]> select classid , avg(age) from students where classid >3 group by classid;
+---------+----------+
| classid | avg(age) |
+---------+----------+
|       4 |  24.7500 |
|       5 |  46.0000 |
|       6 |  20.7500 |
|       7 |  19.6667 |
+---------+----------+
4 rows in set (0.00 sec)

1.1.6 having的使用

• having的用法是对分组后的结果进行过滤；

MariaDB [hellodb]> select classid , avg(age) from students  group by classid having classid >3;
+---------+----------+
| classid | avg(age) |
+---------+----------+
|       4 |  24.7500 |
|       5 |  46.0000 |
|       6 |  20.7500 |
|       7 |  19.6667 |
+---------+----------+
4 rows in set (0.00 sec)

• 混合使用: 查询班级号>3,每个班的平均年龄>30的班级

MariaDB [hellodb]> select classid , avg(age) from students where classid >3 group by classid having avg(age)>30;
+---------+----------+
| classid | avg(age) |
+---------+----------+
|       5 |  46.0000 |
+---------+----------+
1 row in set (0.00 sec)

• 多重过滤
  每个班的不同性别的平均年龄,先班级分组再性别分组；

MariaDB [hellodb]> select classid , gender,avg(age) from students  group by classid ,gender;
+---------+--------+----------+
| classid | gender | avg(age) |
+---------+--------+----------+
|    NULL | M      |  63.5000 |
|       1 | F      |  19.5000 |
|       1 | M      |  21.5000 |
|       2 | M      |  36.0000 |
|       3 | F      |  18.3333 |
|       3 | M      |  26.0000 |
|       4 | M      |  24.7500 |
|       5 | M      |  46.0000 |
|       6 | F      |  20.0000 |
|       6 | M      |  23.0000 |
|       7 | F      |  18.0000 |
|       7 | M      |  23.0000 |
+---------+--------+----------+
12 rows in set (0.00 sec)

1.1.7 order by 排序用法

• 默认升序，
  此时null 在最前

MariaDB [hellodb]> select  name  ,classid  from   students  order by classid  ;
+---------------+---------+
| name          | classid |
+---------------+---------+
| Sun Dasheng   |    NULL |
| Xu Xian       |    NULL |
| Shi Potian    |       1 |
| Xiao Qiao     |       1 |
| Xu Zhu        |       1 |
| Wen Qingqing  |       1 |
| Tian Boguang  |       2 |
| Shi Zhongyu   |       2 |
| Xie Yanke     |       2 |
| Yue Lingshan  |       3 |
| Xi Ren        |       3 |
| Lu Wushuang   |       3 |
| Yu Yutong     |       3 |
| Ma Chao       |       4 |
....

若使用 “ -""排序字段 ” 则降序

select  name  ,classid  from   students  order by -classid  ;

• desc 降序
  此时null 在最后
  效果：

MariaDB [hellodb]> select  name  ,classid  from   students  order by classid  desc;
+---------------+---------+
| name          | classid |
+---------------+---------+
| Hua Rong      |       7 |
| Diao Chan     |       7 |

    ...
    
| Xi Ren        |       3 |
| Yu Yutong     |       3 |
| Lu Wushuang   |       3 |
| Shi Zhongyu   |       2 |
| Tian Boguang  |       2 |
| Xie Yanke     |       2 |
| Xu Zhu        |       1 |
| Wen Qingqing  |       1 |
| Xiao Qiao     |       1 |
| Shi Potian    |       1 |
| Xu Xian       |    NULL |
| Sun Dasheng   |    NULL |
+---------------+---------+

若使用 “ -""排序字段 ” 则升序

select  name  ,classid  from   students  order by -classid  desc;

• asc 升序

MariaDB [hellodb]> select  name  ,classid  from   students  order by classid  asc;
+---------------+---------+
| name          | classid |
+---------------+---------+
| Sun Dasheng   |    NULL |
| Xu Xian       |    NULL |
| Shi Potian    |       1 |
| Xiao Qiao     |       1 |
| Xu Zhu        |       1 |
| Wen Qingqing  |       1 |
| Tian Boguang  |       2 |
| Shi Zhongyu   |       2 |
| Xie Yanke     |       2 |
| Yue Lingshan  |       3 |
| Xi Ren        |       3 |
| Lu Wushuang   |       3 |
| Yu Yutong     |       3 |
| Ma Chao       |       4 |
....

• 默认的null值优先级最高（可以理解为绝对值最小）， 升序排第一，降序排最后，
  使用 is null + 排序字段的方式 ，可以把null的行无论desc 还是asc 都放在最后。

MariaDB [hellodb]> select  name  ,classid  from   students  order by  classid  is null, classid desc  ;
+---------------+---------+
| name          | classid |
+---------------+---------+
| Hua Rong      |       7 |
| Diao Chan     |       7 |
| Lin Daiyu     |       7 |
| Ren Yingying  |       6 |
| Huang Yueying |       6 |
		
		...
		
| Shi Zhongyu   |       2 |
| Tian Boguang  |       2 |
| Xie Yanke     |       2 |
| Xu Zhu        |       1 |
| Wen Qingqing  |       1 |
| Xiao Qiao     |       1 |
| Shi Potian    |       1 |
| Xu Xian       |    NULL |
| Sun Dasheng   |    NULL |
+---------------+---------+
25 rows in set (0.00 sec)

• 总结 ：
  升降序的相同用法

• 升序
  第1种

select  distinct  classid  from   students  order by classid   ;

第2种

select  distinct  classid  from   students  order by classid   asc  ;

1、2 结果是

+---------+
| classid |
+---------+
|    NULL |
|       1 |
|       2 |
|       3 |
|       4 |
|       5 |
|       6 |
|       7 |
+---------+
8 rows in set (0.00 sec)

• 降序
  第一种

select  distinct  classid  from   students  order by  classid   desc  ;

第二种

select  distinct  classid  from   students  order by classid is null ,-classid ;

1、2 结果都是是

+---------+
| classid |
+---------+
|       7 |
|       6 |
|       5 |
|       4 |
|       3 |
|       2 |
|       1 |
|    NULL |
+---------+
8 rows in set (0.01 sec)

1.1.8 limit的使用

• 直接显示前n行

MariaDB [hellodb]> select  *  from teachers ;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | Ma yun        |  26 | NULL   |
|   6 | Zhang tao     |  10 | NULL   |
+-----+---------------+-----+--------+
6 rows in set (0.00 sec)

MariaDB [hellodb]> select  *  from teachers limit 3 ;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
+-----+---------------+-----+--------+
3 rows in set (0.00 sec)

• 跳过前n行，显示第n+1到n+k行

MariaDB [hellodb]> select  *  from teachers limit 2,3 ;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | Ma yun        |  26 | NULL   |
+-----+---------------+-----+--------+
3 rows in set (0.00 sec)

1.1.9 in的用法

用于查询多个条件时，简化or的写法

MariaDB [hellodb]> select  *  from teachers  where tid =1 or tid =3 or tid =4 ;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
+-----+---------------+-----+--------+
3 rows in set (0.00 sec)

MariaDB [hellodb]> select  *  from teachers  where tid  in(1,3,4) ;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
+-----+---------------+-----+--------+
3 rows in set (0.00 sec)

1.1.10 总结

写查询语句时，先写where再分组group by ，再 对查询的结果过滤having，然后再排序desc，最后限制limit

查看部分内容；

分组时，select 与where之间的的内容一定是分组字段，或者聚合函数

1.2 多表查询

1.2.0 super 理解外连接专用图

如图所示，红色S（S1+S2）带代表学生表，蓝色教师表 T（T1+T2），空白部分的内容为null。因为学生表teacherid 与教师表tid的相同,所以S1与T1连接起来，而S2的teacherid 没有对应的T.tid，所以并没有连接起来，s2对应的T3表内容为空， 表示有些学生没有任课老师；T2.tid没有对的S.teacherid,s3为空，表示有些老师不带学生课。

1.2.1 纵向合并 union

场景：把A表的内容变成B表的一部分 ;

union用法：要求前后select 字段 数量一样，次序类一样型；A union B ，A表和B 表 重复的内容会删除，A union all B ，会把A 表和B表重复的内容不会删除，distinct 可以达到相同效果

MariaDB [hellodb]> select  s.stuid, s.name,s.age,s.gender from students s union  select * from teachers ;
+-------+---------------+-----+--------+
| stuid | name          | age | gender |
+-------+---------------+-----+--------+
|     1 | Shi Zhongyu   |  22 | M      |
|     2 | Shi Potian    |  22 | M      |
|     3 | Xie Yanke     |  53 | M      |
|     4 | Ding Dian     |  32 | M      |
|     5 | Yu Yutong     |  26 | M      |
|     6 | Shi Qing      |  46 | M      |
|     7 | Xi Ren        |  19 | F      |
|     8 | Lin Daiyu     |  17 | F      |
			...
|     1 | Song Jiang    |  45 | M      |
|     2 | Zhang Sanfeng |  94 | M      |
|     3 | Miejue Shitai |  77 | F      |
|     4 | Lin Chaoying  |  93 | F      |
|     5 | Ma yun        |  26 | NULL   |
|     6 | Zhang tao     |  10 | NULL   |
+-------+---------------+-----+--------+
31 rows in set (0.00 sec)

MariaDB [hellodb]>

1.2.2 横向交叉连接 cross join

结果内容是两张表内容的笛卡尔乘积,此时的查询结果只有部分内容才有意义

MariaDB [hellodb]> select  * from students cross join teachers;
+-------+---------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
| StuID | Name          | Age | Gender | ClassID | TeacherID | TID | Name          | Age | Gender |
+-------+---------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |   1 | Song Jiang    |  45 | M      |
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |   2 | Zhang Sanfeng |  94 | M      |

    ...

1.2.3 内连接 inner join on

内连接之等值连接：让表之间的字段以“等值”建立连接关系；

A表和B表相同的内容取连接，即2表取交集，T1+T2

MariaDB [hellodb]> select  * from students inner join teachers on teacherid=tid ;
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
| StuID | Name        | Age | Gender | ClassID | TeacherID | TID | Name          | Age | Gender |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
|     5 | Yu Yutong   |  26 | M      |       3 |         1 |   1 | Song Jiang    |  45 | M      |
|     1 | Shi Zhongyu |  22 | M      |       2 |         3 |   3 | Miejue Shitai |  77 | F      |
|     4 | Ding Dian   |  32 | M      |       4 |         4 |   4 | Lin Chaoying  |  93 | F      |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
3 rows in set (0.00 sec)

选择部分字段，有意义的，去掉重复的内容

MariaDB [hellodb]> select s.stuid 学生编号 , s.age  学生年龄, t.tid 任课老师id, t.name 老师, t.age 老师的年龄  from  students  s inner join teachers  t on s.teacherid=t.tid;
+--------------+--------------+----------------+---------------+-----------------+
| 学生编号     | 学生年龄     | 任课老师id     | 老师          | 老师的年龄      |
+--------------+--------------+----------------+---------------+-----------------+
|            5 |           26 |              1 | Song Jiang    |              45 |
|            1 |           22 |              3 | Miejue Shitai |              77 |
|            4 |           32 |              4 | Lin Chaoying  |              93 |
+--------------+--------------+----------------+---------------+-----------------+
3 rows in set (0.00 sec)

内连接旧式写法

MariaDB [hellodb]> select s.stuid ,s.age,s.teacherid ,t.name,t.age from students s, teachers  t where s.teacherid=t.tid;
+-------+-----+-----------+---------------+-----+
| stuid | age | teacherid | name          | age |
+-------+-----+-----------+---------------+-----+
|     5 |  26 |         1 | Song Jiang    |  45 |
|     1 |  22 |         3 | Miejue Shitai |  77 |
|     4 |  32 |         4 | Lin Chaoying  |  93 |
+-------+-----+-----------+---------------+-----+
3 rows in set (0.00 sec)

1.2.4 左外连接 left join on

图：s1+s2+T1+T3

A left outer join B on 条件 A是表的左边， A表全保留， B表若没有对应的，右边为空

MariaDB [hellodb]> select s.stuid,s.name,s.age,t.tid,t.name,t.age from students  s  left  outer join teachers t on s.teacherid =t.tid;
+-------+---------------+-----+------+---------------+------+
| stuid | name          | age | tid  | name          | age  |
+-------+---------------+-----+------+---------------+------+
|     1 | Shi Zhongyu   |  22 |    3 | Miejue Shitai |   77 |
|     2 | Shi Potian    |  22 | NULL | NULL          | NULL |

1.2.6 右外链接 right join on

图：T1+T2+S1+S3

MariaDB [hellodb]> select s.stuid,s.name,s.age,s.teacherid ,t.tid,t.name,t.age from students  s right outer join teachers t on s.teacherid=t
+-------+-------------+------+-----------+-----+---------------+-----+
| stuid | name        | age  | teacherid | tid | name          | age |
+-------+-------------+------+-----------+-----+---------------+-----+
|     1 | Shi Zhongyu |   22 |         3 |   3 | Miejue Shitai |  77 |
|     4 | Ding Dian   |   32 |         4 |   4 | Lin Chaoying  |  93 |
|     5 | Yu Yutong   |   26 |         1 |   1 | Song Jiang    |  45 |
|  NULL | NULL        | NULL |      NULL |   2 | Zhang Sanfeng |  94 |
|  NULL | NULL        | NULL |      NULL |   5 | Ma yun        |  26 |
|  NULL | NULL        | NULL |      NULL |   6 | Zhang tao     |  10 |
+-------+-------------+------+-----------+-----+---------------+-----+
6 rows in set (0.01 sec)

1.2.6 完全外连接

连个表连接起来后，AB两张表都显示没有的对应空

图：整个图：s1+s2+s3+t1+t2+t3

sql语句：连个表左右连接后去重

MariaDB [hellodb]> select s.stuid,s.name,s.age,t.tid,t.name,t.age from students  s  left  outer join teachers t on s.teacherid =t.tid  union  select s.stuid,s.name,s.age,t.tid,t.name,t.age from students  s  right  outer join teachers t on s.teacherid =t.tid;

1.2.7 外连接后一表独有的内容

图：S2+T3, 学生表独有的内容，哪些学生没有任课老师（包括虽然有老师id，但是老师表找不到这个人）

MariaDB [hellodb]> select s.stuid,s.name,s.age  from students  s  left  outer join teachers t on s.teacherid =t.tid  where t.tid is null;

1.2.8 子查询

用法：把子查询的结果作为一个新表 ，并且为新表取别名t，然后就可以作为另一个表的判断条件所使用， 子查询结果的字段不能重复；

示例：图S2+T3+S3+T2的表示

select * from (select s.stuid,s.name s_name,s.age s_age ,s.teacherid ,t.tid,t.name t_name ,t.age t_age  from students  s  left  outer join teachers t on s.teacherid =t.tid ) as  n where  n.teacherid is null or n.tid is null  ;

1.2.9 自连接

用法：一张表取别名构造两张表，外连接查询

场景：一张表查询员工的领导是哪个？

MariaDB [hellodb]> select * from emp ;
+------+----------+----------+
| id   | name     | leaderid |
+------+----------+----------+
|    1 | mage     |     NULL |
|    2 | zhangsir |        1 |
|    3 | wang     |        2 |
|    4 | zhang    |        3 |
+------+----------+----------+
4 rows in set (0.00 sec)


MariaDB [hellodb]> select y.name as 员工,l.name as 他的领导 from emp as y  left  outer join emp as l on y.id=l.id;
+----------+--------------+
| 员工     | 他的领导     |
+----------+--------------+
| mage     | mage         |
| zhangsir | zhangsir     |
| wang     | wang         |
| zhang    | zhang        |
+----------+--------------+
4 rows in set (0.00 sec)

1.2.10 三表查询

示例

MariaDB [hellodb]> select s.name,c.course ,sc.score from students as s inner join scores as sc on s.stuid=sc.stuid inner join courses as c oourseid=c.courseid;
+-------------+----------------+-------+
| name        | course         | score |
+-------------+----------------+-------+
| Shi Zhongyu | Kuihua Baodian |    77 |
| Shi Zhongyu | Weituo Zhang   |    93 |
| Shi Potian  | Kuihua Baodian |    47 |
| Shi Potian  | Daiyu Zanghua  |    97 |
| Xie Yanke   | Kuihua Baodian |    88 |
| Xie Yanke   | Weituo Zhang   |    75 |

二、视图|函数|存储过程|触发器

2.1视图的使用

2.1.1 使用场景：

简化查询语句，避免用户直接接触到表的数据

2.1.2 缺点：

视图是虚拟表，由满足一定条件的查询语句结果所得，但是当视图创建完成后，任然可以对视图插入和修改操作，包括不满足创建视图定义条件的数据都可以插入，因为实际上是对原表操作，但是新的操作之后的结果通过视图却看不见（不满足创建视图定义的查询条件）。

2.1.3 语法：

查询视图： create view view_name as 查询语句;

查看视图定义：show create view view_name ;
删除视图： drop view view_name;

查看视图状态： show table status like ‘表名或视图名’ \G；

2.1.4 注意：

创建视图后，该视图存在于当前数据库中，类似一个表。show tables 就可以查看到；

通过查看表的状态， show table stutus like ‘table_name’ \G，判断这个表是视图还是真实存在的表，如果是视图记录为空，comment是view，反之不为空，comment为空。

2.1.5 示例：

MariaDB [hellodb]> create view vs as select s.name,c.course ,sc.score from students as s inner join scores as sc on s.stuid=sc.stuid inner join courses as c on sc.courseid=c.courseid;
Query OK, 0 rows affected (0.00 sec)


MariaDB [hellodb]> select * from vs;
+-------------+----------------+-------+
| name        | course         | score |
+-------------+----------------+-------+
| Shi Zhongyu | Kuihua Baodian |    77 |

...


MariaDB [hellodb]> show create  view vs \G;

MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |

...

| teachers          |
| toc               |
| vs                |
+-------------------+
11 rows in set (0.00 sec)

MariaDB [hellodb]> show   table status like 'vs' \G;
*************************** 1. row ***************************
           Name: vs
         Engine: NULL
        Version: NULL
     Row_format: NULL
 
			...
			
 Create_options: NULL
        Comment: VIEW
1 row in set (0.01 sec)

2.2 函数

2.2.1 分类：

系统函数和自定义函数 (user-defined function UDF)

系统函数:https://dev.mysql.com/doc/refman/5.7/en/func-op-summaryref.html

2.2.2 自定义函数介绍

• 位置：保存在mysql.proc表中
• 创建：

• 基本语法：

CREATE [AGGREGATE] FUNCTION function_name(parameter_name,type,[parameter_name type,...])

runtime_body

RETURNS {STRING|INTEGER|REAL}

• 定义局部变量语法
  DECLARE 变量1[,变量2,… ]变量类型 [DEFAULT 默认值]
• 说明：局部变量的作用范围是在BEGIN…END程序中,而且定义局部变量语句必须在
  BEGIN…END的第一行定义

• 说明：

• 参数可以有多个,也可以没有参数；
• 必须有且只有一个返回值；
• 因为写函数代码需要换行符，但是函数默认的分号在sql语句中是命令提交符，发生了冲突需要修改，函数写之前改一下，写完了改一下恢复原来的环境；
• 换行符的修改：DELIMITER + // 或分号

2.2.3 自定义函数使用示例

2.2.3.1 创建无参函数

MariaDB [hellodb]> create function simplefun() returns varchar(20) return "hello ,zhangtao";
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> select simplefun();
+-----------------+
| simplefun()     |
+-----------------+
| hello ,zhangtao |
+-----------------+
1 row in set (0.00 sec)

2.2.3.2 创建有参函数

作用：在学生表中查询到stuid为uid的学生记录并且删除，返回删除后的学生表的记录数

MariaDB [hellodb]> delimiter //
MariaDB [hellodb]> create function deletbyid(uid smallint unsigned)returns varchar(20)
    -> begin
    -> delete from students where stuid=uid;
    -> return (select count(stuid) from students );
    -> END //
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> delimiter  ;

MariaDB [hellodb]> select deletbyid(25);
+---------------+
| deletbyid(25) |
+---------------+
| 24            |
+---------------+
1 row in set (0.01 sec)

2.2.4 函数的基本操作

2.2.4.1 调用函数

select function_name( arguments);

注意函数的使用一定要加括号

2.2.4.2 查看函数列表

MariaDB [hellodb]> show function status\G;

2.2.4.3 查看函数定义

MariaDB [hellodb]> show create function simplefun;

2.2.4.4 删除函数

MariaDB [hellodb]> drop function simplefun;
Query OK, 0 rows affected (0.00 sec)

2.3 触发器

2.3.1定义：

触发器（trigger）是MySQL提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（insert，delete， update）时就会激活它执行

简单的来说，当为一张表创建了触发器之后， 当某一条sql语句执行时，会触发更多的sql语句执行

2.3.2 触发器创建的四个要素

（1）监视地点（table）
（2）监视事件（insert/update/delete）
（3）触发时间（after/before）
（4）触发事件（insert/update/delete）

2.3.3 创建触发器语法

CREATE

[DEFINER = { user | CURRENT_USER }]

TRIGGER trigger_name

trigger_time trigger_event

ON tbl_name FOR EACH ROW

trigger_body

说明：
trigger_name：触发器的名称

trigger_time：{ BEFORE | AFTER }，表示在事件之前或之后触发
trigger_event:：{ INSERT |UPDATE | DELETE }，触发的具体事件
tbl_name：该触发器作用在表名

2.3.4 创建触发器示例

1. 先创建2张表student_info 信息表、student_count 学生数量表 并且为学生初始值0

CREATE TABLE student_info (
    stu_id INT(11) NOT NULL AUTO_INCREMENT,
    stu_name VARCHAR(255) DEFAULT NULL,
    PRIMARY KEY (stu_id)
);
CREATE TABLE student_count (
    student_count
    INT(11) DEFAULT 0
);

INSERT INTO student_count VALUES(0);

1. 创建触发器，在向学生表INSERT数据时，学生数增加，DELETE学生时，学生数减少

CREATE TRIGGER trigger_student_count_insert
AFTER INSERT
ON student_info FOR EACH ROW
UPDATE student_count SET student_count=student_count+1;
CREATE TRIGGER trigger_student_count_delete
AFTER DELETE
ON student_info FOR EACH ROW
UPDATE student_count SET student_count=student_count-1;

2.3.5 触发器的基操

2.3.5.1 查看触发器器

• 查看所有的触发器

MariaDB [hellodb]> show triggers;

• 查看指定的触发器

MariaDB [hellodb]> use information_schema

MariaDB [information_schema]> select * from triggers where trigger_name='trigger_student_count_insert';

2.3.5.2 删除触发器

drop trigger trigger_name;

2.4 存储过程

2.4.1 概念

在一些语言中，如pascal，有一个概念叫“过程”procedure，和“函数”function，在php中，没有过程，只有函数。

在MySQL中：

我们把若干条sql封装起来，起个名字 —— 过程

把此过程存储在数据库中 —— 存储过程

2.4.2 存储过程优势

• 存储过程把经常使用的SQL语句或业务逻辑封装起来,预编译保存在数据库中,当需要时从数据库中直接调用,省去了编译的过程
• 提高了运行速度，同时降低网络数据传输量

2.4.3 存储过程与自定义函数的区别

• 存储过程实现的过程要复杂一些,而函数的针对性较强
• 存储过程可以有多个返回值,而自定义函数只有一个返回值
• 存储过程一般可独立执行,而函数往往是作为其他SQL语句的一部分来使用
• 过程：封装了若干条语句，调用时，这些封装体执行
  函数：是一个有返回值的“过程”
  总结：过程是一个没有返回值的函数

2.4.4 存储过程的创建

2.4.4.1语法：

create procedure procedureName(  proc_parameter  )
begin
  //--sql 语句
end$

proc_parameter 格式: [IN|OUT|INOUT] parameter_name type；

如：IN uid smallint unsigned

过程可以有多个参数

其中IN表示输入参数，OUT表示输出参数，INOUT表示既可以输入也可以输出；
param_name表示参数名称；type表示参数的类型

2.4.4.2 示例

• 创建无参存储过程

MariaDB [hellodb]> delimiter //
MariaDB [hellodb]> CREATE PROCEDURE showTime()
    -> BEGIN
    ->  
    -> SELECT now();
    -> END//
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> delimiter ;
MariaDB [hellodb]> CALL showTime;
+---------------------+
| now()               |
+---------------------+
| 2020-01-22 16:12:12 |
+---------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

• 创建有参的存储过程

MariaDB [hellodb]> delimiter //
MariaDB [hellodb]> CREATE PROCEDURE selectById(IN uid SMALLINT UNSIGNED)
    -> BEGIN
    -> SELECT * FROM students WHERE stuid = uid;
    -> END//
Query OK, 0 rows affected (0.01 sec)

MariaDB [hellodb]> delimiter ;
MariaDB [hellodb]> call selectById(2);
+-------+------------+-----+--------+---------+-----------+
| StuID | Name       | Age | Gender | ClassID | TeacherID |
+-------+------------+-----+--------+---------+-----------+
|     2 | Shi Potian |  22 | M      |       1 |         7 |
+-------+------------+-----+--------+---------+-----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

2.4.5 存储过程的基操

2.4.5.1查看已有的存储过程

MariaDB [hellodb]> show procedure status ;

2.4.5.2 删除存储过程

drop procedure procedureName;

MariaDB [hellodb]> drop procedure selectById;
Query OK, 0 rows affected (0.01 sec)

2.4.5.3调用存储过程

CALL sp_name ([ proc_parameter [,proc_parameter …]])

CALL sp_name
说明:当无参时,可以省略"()",当有参数时,不可省略"()”

2.4.5.4 查看存储过程的定义

MariaDB [hellodb]> show create  procedure sp_name ;

三、mysql帐户与授权

3.1 mysql 用户

3.1.1存放用户信息的表

数据库是mysql,user表存放了用户的用户名和密码，以及权限相关的信息 db, host, user
columns_priv, tables_priv, procs_priv, proxies_priv

MariaDB [(none)]> select   host ,user,password from mysql.user ;
+-----------+------+-------------------------------------------+
| host      | user | password                                  |
+-----------+------+-------------------------------------------+
| localhost | root | *2F0BC06E23851C1FD8508F795C4960885BF33886 |
| 192.168.% | test | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+-----------+------+-------------------------------------------+
2 rows in set (0.00 sec)

3.1.2 用户名的构成方式

• 用户账号： ‘USERNAME’@‘HOST’
• @‘HOST 的意思’: 主机名 、IP地址或Network
• 通配符： % 或 _ 示例：172.16.%.%

3.1.3 用户管理

3.1.3.1 创建用户

CREATE USER ‘USERNAME’@‘HOST’ [IDENTIFIED BY ‘password’]；

示例：

MariaDB [hellodb]> create user 'test'@'192.168.%' identified by '123' ;
Query OK, 0 rows affected (0.01 sec)

此时test的权限是默认权限，看不到其他数据库，只能看到information_schema;只能用来连接

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

3.1.3.2 用户重命名

RENAME USER ‘old_user_name’@‘old_host’ TO ‘new_user_name’@‘new_host’;

例：

MariaDB [(none)]> rename user 'test'@'192.168.%' to 'tt'@'192.168.%' ;
Query OK, 0 rows affected (0.00 sec)

3.1.3.3 删除用户

DROP USER ‘USERNAME’@'HOST‘

例：

MariaDB [(none)]> drop user 'tt'@'192.168.%';
Query OK, 0 rows affected (0.00 sec)

3.1.3.4 修改用户密码

• 第一种方式：set password 命令修改
  SET PASSWORD FOR ‘user’@‘host’ = PASSWORD(‘password’);
  例：

MariaDB [(none)]> set password for 'test'@'192.168.%'=password('12345');
Query OK, 0 rows affected (0.00 sec)

• 第二种方式： 直接修mysql.user表；
  UPDATE mysql.user SET password=PASSWORD(‘password’) where 子句；
  然后这种方式要，设置不会立即生效，需冲洗权限；flush privileges
  例：

MariaDB [(none)]> UPDATE mysql.user SET password=PASSWORD('777') where user='test'; 
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [(none)]> flush privileges ;
Query OK, 0 rows affected (0.00 sec)

3.1.4 忘记sql密码怎么办

• 修改配置文件
  设置 一关闭远程连接、二设置跳过权限检查；update修改mysql.user表，一二步设置再打开，然后重启

[mysqld]
skip-grant-tables
skip-networking

• 不修改配置文件的话，可以通过mysqld 设置服务器选项操作

1. 启动mysqld进程时，为其使用如下选项：–skip-grant-tables --skip-networking
2. 使用UPDATE命令修改管理员密码
3. 关闭mysqld进程，移除上述两个选项，重启mysqld

3.2 MySQL 权限管理

3.2.1 权限类别

管理类
程序类
数据库级别
表级别
字段级别

3.2.1.1 管理类

CREATE TEMPORARY TABLES
CREATE USER
FILE
SUPER
SHOW DATABASES
RELOAD
SHUTDOWN
REPLICATION SLAVE
REPLICATION CLIENT
LOCK TABLES
PROCESS

3.2.1.2 程序类 FUNCTION、PROCEDURE、TRIGGER

CREATE
ALTER
DROP
EXCUTE

3.2.1.3 库和表级别：DATABASE、TABLE

ALTER
CREATE
CREATE VIEW
DROP
INDEX
SHOW VIEW
GRANT OPTION：能将自己获得的权限转赠给其他用户

create user ‘test’@'192.168.% ’ identified by ‘123’ ;

3.2.3 授权

3.2.3.1 授权语法

参考：https://dev.mysql.com/doc/refman/5.7/en/grant.html

GRANT priv_type [(column_list)],... ON [object_type] priv_level TO 'user'@'host' 
[IDENTIFIED BY 'password'] [WITH GRANT OPTION];

语法说明

• priv_type: ALL [PRIVILEGES]
• object_type:TABLE | FUNCTION | PROCEDURE
• priv_level: (所有库) | . | db_name. | db_name.tbl_name| tbl_name(当前库
  的表) | db_name.routine_name(指定库的函数,存储过程,触发器)
• with_option: GRANT OPTION
  | MAX_QUERIES_PER_HOUR count
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count

示例：

grant select(col1),  insert(col1,col2)  on mydb.mytbl to 'someuser'@'somehost';

MariaDB [(none)]> grant all privileges on  hellodn.* to 'test'@'192.168.%';
Query OK, 0 rows affected (0.00 sec)

3.2.4 回收授权

语法

REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ... ON 
[object_type] priv_level
 
FROM user [, user] ...

例

MariaDB [(none)]> revoke delete on hellodb.*  from 'test'@'192.168.%';
Query OK, 0 rows affected (0.00 sec)

3.2.5 查看用户获得的授权

• 看指定用户
  SHOW GRANTS FOR ‘user’@‘host’;

MariaDB [(none)]> show grants for 'test'@'192.168.%';
+-------------------------------------------------------------------------------------------------------------+
| Grants for test@192.168.%                                                                                   |
+-------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'192.168.%' IDENTIFIED BY PASSWORD '*E8D868B7DA46FC9F996DC761C1AE01754A4447D5' |
| GRANT ALL PRIVILEGES ON `hellodn`.* TO 'test'@'192.168.%'                                                   |
+-------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

• 看当前用户
  SHOW GRANTS FOR CURRENT_USER();

3.2.6 注意

• MariaDB服务进程启动时会读取mysql库中所有授权表至内存
• GRANT或REVOKE等执行权限操作会保存于系统表中，MariaDB的服务进程通常会自动重读授权表，使之生效
• 对于不能够或不能及时重读授权表的命令，可手动让MariaDB的服务进程
  重读授权表：mysql> FLUSH PRIVILEGES;

四、存储引擎

4.1 MyISAM

4.1.1 MyISAM引擎特点

• 不支持事务
• 表级锁定
• 读写相互阻塞，写入不能读，读时不能写
• 只缓存索引
• 不支持外键约束
• 不支持聚簇索引
• 读取数据较快，占用资源较少
• 不支持MVCC（多版本并发控制机制）高并发
• 崩溃恢复性较差
• MySQL5.5.5前默认的数据库引擎

4.1.2 MyISAM存储引擎适用场景

只读（或者写较少）、表较小（可以接受长时间进行修复操作）

4.1.3 MyISAM引擎文件

• tbl_name.frm 表格式定义
• tbl_name.MYD 数据文件
• tbl_name.MYI 索引文件

4.2 InnoDB

4.2.1 InnoDB引擎特点

• 行级锁
• 支持事务，适合处理大量短期事务
• 读写阻塞与事务隔离级别相关
• 可缓存数据和索引
• 支持聚簇索引
• 崩溃恢复性更好
• 支持MVCC高并发
• 从MySQL5.5后支持全文索引
• 从MySQL5.5.5开始为默认的数据库引擎

4.2.3 InnoDB数据库文件

所有InnoDB表的数据和索引放置于同一个表空间中

• 表空间文件：datadir定义的目录下
• 数据文件：ibddata1, ibddata2, …
  每个表单独使用一个表空间存储表的数据和索引
  启用：innodb_file_per_table=ON
  mariadb 10.2 版本默认打开这一选项

注意：

两类文件放在数据库独立目录中
数据文件(存储数据和索引)：tb_name.ibd 、表格式定义：tb_name.frm

4.3 其他存储引擎

• Performance_Schema：Performance_Schema数据库使用
• BLACKHOLE ：黑洞存储引擎接受但不存储数据，检索总是返回一个空集。该功能可用于分布式数据库设计，数据自动复制，但不是本地存储
• CSV：CSV存储引擎使用逗号分隔值格式将数据存储在文本文件中。可以使用CSV引擎以CSV格式导入和导出其他软件和应用程序之间的数据交换
• example：“stub”引擎，它什么都不做。可以使用此引擎创建表，但不能将数
  据存储在其中或从中检索。目的是作为例子来说明如何开始编写新的存储引擎

4.4 管理存储引擎

4.4.1查看mysql支持的存储引擎

show engines;

4.4.2 查看当前默认的存储引擎

show variables like '%storage_engine%';

4.4.3 设置默认的存储引擎

vim /etc/my.conf

[mysqld]
default_storage_engine= InnoDB

4.4.4查看库中所有表使用的存储引擎

mysql> show table status from  hellodb;

4.4.5查看库中指定表的存储引擎

show table status like ' tb_name ';
show create table tb_name;

4.4.6 设置表的存储引擎：

CREATE TABLE tb_name(... ) ENGINE=InnoDB;
ALTER TABLE tb_name ENGINE=InnoDB;

五、服务器选项与变量

5.1 查看选项变量的帮助文档

• https://dev.mysql.com/doc/refman/5.7/en/server-option-variable-reference.html
• https://mariadb.com/kb/en/library/full-list-of-mariadb-options-system-and-status-variables/
• 其中有些参数支持运行时修改，会立即生效；有些参数不支持，且
  只能通过修改配置文件，并重启服务器程序生效；有些参数作用域是全局
  的，且不可改变；有些可以为每个用户提供单独（会话）的设置

5.2 变量的分类以及选项的区别关系

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ppFuAZyo-1579791061729)(A:/image_typoral_used/102033148.png)]

• 根据变量修改的方式：

• 动态变量：可以在MySQL运行时调整其指，并立即生效；set global sort_buffer_size=value
• 静态变量：需要在配置文件中修改，重启服务后生效；/etc/my.cnf，这种特殊类型的系统变量就是服务器选项，可以在mysqld --print-defaults 查看；例如datadir

MariaDB [(none)]> select @@datadir ;
+-----------------+
| @@datadir       |
+-----------------+
| /var/lib/mysql/ |
+-----------------+
1 row in set (0.00 sec)

但并不是所有的服务器选项都是变量

root@z1:~# /usr/sbin/mysqld   --print-defaults ;
/usr/sbin/mysqld would have been started with the following arguments:
--user=mysql --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock --port=3306 --basedir=/usr --datadir=/var/lib/mysql 

	...

• 根据变量的生效范围：
  全局变量：服务级别的设定，对整个服务生效,所有回话，当时已经连接不生效，重新联系才生效；set global sort_buffer_size=value
  会话变量：仅对当前会话生效，其他会话和新会话不受影响；会话结束值即销毁；set session sort_buffer_size=value

5.3 服务器选项

5.3.1获取mysqld的可用选项列表：

mysqld --help --verbose 
mysqld --print-defaults  获取默认设置

5.3.2 设置服务器选项方法：

• 在命令行中设置
  shell> ./mysqld_safe --skip-name-resolve=1
• 在配置文件my.cnf中设置
  skip_name_resolve=1

5.4 服务器变量

5.4.1查询变量

5.4.1.1查询系统变量

• 查看所有系统变量

show variables \G;

• 查看所有当前系统全局变量

show  global  variables \G;

• 查看所有当前系统会话变量

show   session variables  \G;

• 查看特定系统变量

• 使用select命令查看 SELECT @@VARIABLES;

MariaDB [(none)]> select @@datadir;
+-----------------+
| @@datadir       |
+-----------------+
| /var/lib/mysql/ |
+-----------------+
1 row in set (0.00 sec)

• 使用show 命令查看 show variables like ’ 变量名’；

MariaDB [(none)]> show       variables like  'datadir' ;
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.00 sec)

5.4.1.2 查询状态变量

状态变量（只读）：用于保存mysqld运行中的统计数据的变量，不可更改

看全局状态变量

show  global  status;

看会话状态变量

show session status;

查看特定的状态变量

MariaDB [(none)]> show  status like 'Connections' ;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections   | 35    |
+---------------+-------+
1 row in set (0.00 sec)

5.4.2 修改系统变量的值：

许多系统变量都是动态的，可以在运行时通过使用SET 语句来更改 。有关列表，请参见 第5.1.8.2节“动态系统变量”。要使用更改系统变量 SET，请使用名称来引用它，还可以在其前面加上修饰符。在运行时，必须使用下划线而不是破折号来写系统变量名称。以下示例简要说明了此语法：

• 查看帮助

mysql> help SET

• 修改全局变量：仅对修改后新创建的会话有效；对已经建立的会话无效
  mysql> SET GLOBAL system_var_name=value;
  mysql> SET @@global.system_var_name=value;

SET GLOBAL max_connections = 1000;
SET @@GLOBAL.max_connections = 1000;

• 修改会话变量：
  mysql> SET [SESSION] system_var_name=value;
  mysql> SET @@[session.]system_var_name=value;

SET SESSION sql_mode = 'TRADITIONAL';
SET @@SESSION.sql_mode = 'TRADITIONAL';
SET @@sql_mode = 'TRADITIONAL';

5.5 特殊的服务器变量 sql_mode

Name	Cmd-Line	Option File	System Var	Var Scope	Dynamic
sql_mode	Yes	Yes	Yes	Both	Yes

• 作用：设置SQL_MODE对可以完成一些约束检查的工作,可分别进行全局的设置或当前会 话的设置，参看：https://mariadb.com/kb/en/library/sql-mode/
• 常见MODE:

• NO_AUTO_CREATE_USER
  禁止GRANT创建密码为空的用户
• NO_ZERO_DATE
  在严格模式，不允许使用‘0000-00-00’的时间

• ONLY_FULL_GROUP_BY
  对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么 将认为这个SQL是不合法的
• NO_BACKSLASH_ESCAPES
  反斜杠“\”作为普通字符而非转义字符
• PIPES_AS_CONCAT
  将"||"视为连接操作符而非“或运算符”

六、mysql缓存与性能优化

6.1缓存

6.1.1 查询缓存（ Query Cache ）原理

缓存SELECT操作或预处理查询的结果集和SQL语句，当有新的SELECT语句或预 处理查询语句请求，先去查询缓存，判断是否存在可用的记录集，判断标准：与缓存的SQL语句，是否完全一样，区分大小写

6.1.2 查询缓存优缺点

• 不需要对SQL语句做任何解析和执行，当然语法解析必须通过在先，直接从 Query Cache中获得查询结果，提高查询性能
• 查询缓存的判断规则，不够智能，也即提高了查询缓存的使用门槛，降低效率
• 查询缓存的使用，会增加检查和清理Query Cache中记录集的开销

6.1.3 哪些查询可能不会被缓存

• 查询语句中加了SQL_NO_CACHE参数
• 查询语句中含有获得值的函数，包含自定义函数，如：NOW() 、CURDATE()、GET_LOCK()、RAND()、CONVERT_TZ()等
• 对系统数据库的查询：mysql、information_schema 查询语句中使用 SESSION级别变量或存储过程中的局部变量
• 查询语句中使用了LOCK IN SHARE MODE、FOR UPDATE的语句，查询语 句中类似SELECT …INTO 导出数据的语句
• 对临时表的查询操作；存在警告信息的查询语句；不涉及任何表或视图的查 询语句；某用户只有列级别权限的查询语句
• 事务隔离级别为Serializable时，所有查询语句都不能缓存

6.1.4 查询缓存相关的服务器变量

• query_cache_min_res_unit：查询缓存中内存块的最小分配单位，默认4k，较 小值会减少浪费，但会导致更频繁的内存分配操作，较大值会带来浪费，会导 致碎片过多，内存不足
• query_cache_limit：单个查询结果能缓存的最大值，默认为1M，对于查询结 果过大而无法缓存的语句，建议使用SQL_NO_CACHE
• query_cache_size：查询缓存总共可用的内存空间；单位字节，必须是1024 的整数倍，最小值40KB，低于此值有警报
• query_cache_wlock_invalidate：如果某表被其它的会话锁定，是否仍然可以 从查询缓存中返回结果，默认值为OFF，表示可以在表被其它会话锁定的场景 中继续从缓存返回数据；ON则表示不允许
• query_cache_type：是否开启缓存功能，取值为ON, OFF, DEMAND， 看6.1.5

6.1.5 query_cache_type参数变量 的详细介绍

• query_cache_type的值为OFF或0时，查询缓存功能关闭
• query_cache_type的值为ON或1时，查询缓存功能打开，SELECT的结果符合 缓存条件即会缓存，否则，不予缓存，显式指定SQL_NO_CACHE，不予缓存， 此为默认值
• query_cache_type的值为DEMAND或2时，查询缓存功能按需进行，显式指 定SQL_CACHE的SELECT语句才会缓存；其它均不予缓存
• 参看：https://mariadb.com/kb/en/library/server-system-variables/#query_cache_type https://dev.mysql.com/doc/refman/5.7/en/query-cache-configuration.html

6.1.6 SELECT语句的缓存控制

• SQL_CACHE：显式指定存储查询结果于缓存之中
• SQL_NO_CACHE：显式查询结果不予缓存

6.2 通过缓存进行性能优化

6.2.1 查询缓存相关的状态变量：

MariaDB [(none)]> SHOW GLOBAL STATUS LIKE 'Qcache%'; 
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 16759656 |
| Qcache_hits             | 0        |
| Qcache_inserts          | 0        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 30       |
| Qcache_queries_in_cache | 0        |
| Qcache_total_blocks     | 1        |
+-------------------------+----------+
8 rows in set (0.00 sec)

说明：

• Qcache_free_blocks：处于空闲状态 Query Cache中内存 Block 数
• Qcache_total_blocks：Query Cache 中总Block ，当Qcache_free_blocks 相对此值较大时，可能用内存碎片，执行FLUSH QUERY CACHE清理碎片
• Qcache_free_memory：处于空闲状态的 Query Cache 内存总量
• Qcache_hits：Query Cache 命中次数
• Qcache_inserts：向 Query Cache 中插入新的 Query Cache 的次数，即没 有命中的次数
• Qcache_lowmem_prunes：记录因为内存不足而被移除出查询缓存的查询数
• Qcache_not_cached：没有被 Cache 的 SQL 数，包括无法被 Cache 的 SQL
  以及由于 query_cache_type 设置的不会被 Cache 的 SQL语句
• Qcache_queries_in_cache：在 Query Cache 中的 SQL 数量

6.2.2 命中率和内存使用率估算

• 查询缓存中内存块的最小分配单位query_cache_min_res_unit ：
  (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache
• 查询缓存命中率 ：Qcache_hits / ( Qcache_hits + Qcache_inserts ) * 100%
• 查询缓存内存使用率：(query_cache_size – qcache_free_memory) /
  query_cache_size * 100%

6.3 缓存优化的流程

6.4 示例

6.4.1 开启缓存并且设置缓存空间大小

假若刚开开始没有开启缓存，则也没有Qcache 相关的内容

MariaDB [(none)]> show variables like 'quer%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| query_alloc_block_size       | 16384   |
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 0       |
| query_cache_strip_comments   | OFF     |
| query_cache_type             | OFF     |
| query_cache_wlock_invalidate | OFF     |
| query_prealloc_size          | 24576   |
+------------------------------+---------+
8 rows in set (0.00 sec)

MariaDB [(none)]> show status like 'Qcache%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_free_blocks      | 0     |
| Qcache_free_memory      | 0     |
| Qcache_hits             | 0     |
| Qcache_inserts          | 0     |
| Qcache_lowmem_prunes    | 0     |
| Qcache_not_cached       | 0     |
| Qcache_queries_in_cache | 0     |
| Qcache_total_blocks     | 0     |
+-------------------------+-------+

设置 my.cnf;

[mysqld]
query_cache_type=1
query_cache_limit	= 1M
query_cache_size=10M

然后重启服务 systemctl restart mariadb

再次查询

MariaDB [(none)]> show variables like 'quer%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| query_alloc_block_size       | 16384    |
| query_cache_limit            | 1048576  |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 10485760 |
| query_cache_strip_comments   | OFF      |
| query_cache_type             | ON       |
| query_cache_wlock_invalidate | OFF      |
| query_prealloc_size          | 24576    |
+------------------------------+----------+
8 rows in set (0.00 sec)


MariaDB [(none)]> show status like 'Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 10468264 |
| Qcache_hits             | 0        |
| Qcache_inserts          | 0        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 28       |
| Qcache_queries_in_cache | 0        |
| Qcache_total_blocks     | 1        |
+-------------------------+----------+
8 rows in set (0.00 sec)

6.4.2 验证

第一次查询，是没有利用到缓存的，所以Qache_hits命中数是0；未命中数Qcache_inserts是1;

MariaDB [hellodb]> select * from teachers ;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | Ma yun        |  26 | NULL   |
|   6 | Zhang tao     |  10 | NULL   |
+-----+---------------+-----+--------+
6 rows in set (0.00 sec)

MariaDB [hellodb]> show status like 'Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 10466728 |
| Qcache_hits             | 0        |
| Qcache_inserts          | 1        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 30       |
| Qcache_queries_in_cache | 1        |
| Qcache_total_blocks     | 4        |
+-------------------------+----------+
8 rows in set (0.00 sec)

把第一次查询的命令 select * from teachers 原封不动执行3次，再查;发现Qcache hit 3，表示利用缓存查询了3次，而insert是0，所以命中率是75%；

MariaDB [hellodb]> show status like 'Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 10466728 |
| Qcache_hits             | 3        |
| Qcache_inserts          | 1        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 30       |
| Qcache_queries_in_cache | 1        |
| Qcache_total_blocks     | 4        |
+-------------------------+----------+
8 rows in set (0.00 sec)

再把 Select * from teachers 执行一次；此次因为没有利用到缓存，，得到的结果是 hits 3、inserts 2;所以命中率是5/6；

MariaDB [hellodb]> show status like 'Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 10465704 |
| Qcache_hits             | 3        |
| Qcache_inserts          | 2        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 30       |
| Qcache_queries_in_cache | 2        |
| Qcache_total_blocks     | 6        |
+-------------------------+----------+
8 rows in set (0.01 sec)

注意：缓存的使用，不仅大小写要求一致、也不能多或者少一个空格