MySQL函数，分组，子查询，正则，索引，执行计划，引擎

学习主题：MySQL
学习目标：
1 掌握 聚合函数和分组
2 掌握子查询
3 掌握正则
4 掌握Mysql索引
5 掌握用户管理
6 掌握分页 查询过程 执行计划
7 了解mysql的引擎

1.MySQL中的聚合函数
(1)MySQL5.7是否支持SQL99标准？
支持

2.MySQL中的数据分组
(1)MySQL中有哪些常用的聚合函数(组函数)？
1、avg()函数
2、sum()函数
3、min()函数
4、max()函数
5、count()函数

(2)每个聚合函数的作用是什么？
1、avg()函数：求平均值
2、sum()函数：求和
3、min()函数：求最小值
4、max()函数：求最大值
5、count()函数：返回一个表中的行数

(3)在MySQL中通过什么子句来创建数据分组？
Group by

(4)如何约束数据分组？
having

(5)计算每个部门的平均薪水
select avg(e.salary) from employees e group by e.department_id

(6)显示那些最高薪水大于 5000 的部门的部门号和最高薪水。
select e.department_id,max(e.salary) from employees e group by e.department_id HAVING max(e.salary) > 5000

3.MySQL中的子查询
(1)MySQL的子查询可以放到SQL语句中的什么位置？
可以将子查询放在许多的 SQL 子句中，包括：
• WHERE 子句
• HAVING 子句
• FROM 子句

(2)使用子查询的原则是什么？
• 子查询放在圆括号中。
• 将子查询放在比较条件的右边。
• 在单行子查询中用单行运算符，在多行子查询中用多行运算符。

(3)在MySQL中如何处理多行子查询？
IN:等于列表中的任何成员
ANY:比较子查询返回的每个值
ALL:比较子查询返回的全部值

4.MySQL中的正则表达式
(1)MySQL中正则表达式的特点是什么？
• MySQL 中允许使用正则表达式定义字符串的搜索条件，性能要高于 like。
• MySQL 中的正则表达式可以对整数类型或者字符类型检索。
• 使用 REGEXP 关键字表示正则匹配。
• 默认忽略大小写，如果要区分大小写，使用 BINARY 关键字
(2)MySQL中使用正则表达式的语法结构是什么？

(3)MySQL中支持哪些正则表达式的模板？其对应的含义是什么？

(4)查询雇员表中名字是以k开头的雇员名字与薪水。
select last_name,salary from employees where last_name REGEXP ‘¹’

(5)查询雇员表中名字是以n结尾的雇员名字与薪水。
select last_name,salary from employees where last_name REGEXP binary 'n$

5.MySQL中创建普通索引
(1)MySQL中的索引类型有哪些？
• 普通索引
• 唯一索引
• 主键索引
• 组合索引
• 全文索引

(2)在MySQL中创建普通索引的方式有些？
在创建索引时，可以指定索引长度。length 为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度，如果是 BLOB 和 TEXT 类型，必须指定 length

(3)如何查询一个表中的索引？
SHOW INDEX FROM table_name

(4)如何删除一个索引？
DROP INDEX index_name ON table

6.MySQL中创建唯一索引
(1)什么是唯一索引？
唯一索引与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。

(2)在MySQL中如何创建唯一索引？
CREATE UNIQUE INDEX indexName ON table(column(length))

7.MySQL中创建主键索引
(1)什么是主键索引？
主键索引是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引。

(2)在MySQL中如何创建主键索引？
修改表时添加主键索引
ALTER TABLE 表名 ADD PRIMARY KEY(列名)
创建表时创建主键索引
CREATE TABLE table (COLUMN TYPE , PRIMARY KEY(column))

8.MySQL中创建组合索引
(1)什么是组合索引？
组合索引是指使用多个字段创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用(最左前缀原则)。

(2)什么是最左前缀原则？
就是最左优先。

(3)MySQL中如何创建组合索引？
修改时添加组合索引
ALTER TABLE table_name ADD INDEX index_name (column(length),column(length))
创建表时创建组合索引
CREATE TABLE table (
COLUMN TYPE , INDEX index_name (column(length),column(length)))

9.MySQL中创建全文索引
(1)什么是全文索引？
全文索引(FULLTEXT INDEX)主要用来查找文本中的关键字，而不是直接与索引中的值相比较。FULLTEXT 索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的 where语句的参数匹配。FULLTEXT 索引配合 match against 操作使用，而不是一般的 where 语句加 like。全文索引可以从 CHAR、VARCHAR 或 TEXT 列中作为 CREATE TABLE 语句的一部分被创建，或是随后使用 ALTER TABLE 添加。不过切记对于大容量的数据表，生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。

(2)如何创建全文索引？
修改添加全文索引
ALTER TABLE table_name ADD FULLTEXT index_content(content)
创建表时创建全文索引
CREATE TABLE table (
COLUMN TYPE , FULLTEXT index_name (column))

(3)如何删除全文索引？
DROP INDEX index_name ON table
ALTER TABLE table_name DROP INDEX index_name

10.MySQL中使用全文索引
(1)在MySQL中如何使用全文索引？
全 文 索 引 的 使 用 与 其 他 索 引 不 同 。 在 查 询 语 句 中 需 要 使 用 match(column)against(‘content’) 来检索数据。
SELECT 投影列 FROM 表名 WHERE MATCH(全文索引列名) AGAINST(‘搜索内容’)

(2)什么是全文解析器？
全文索引中基本单位是”词”。分词，全文索引是以词为基础的，MySQL 默认的分词是所有非字母和数字的特殊符号都是分词符。在检索数据时我们给定的检索条件也是词。MySQL 中默认的全文解析器不支持中文分词。如果数据含有中文需要更换全文解析器NGRAM。

(3)在创建全文索引时如何指定解析器？
ALTER TABLE table_name ADD FULLTEXT index_content(content) WITH PARSER NGRAM

11.MySQL中的用户管理
(1)MySQL中有几种用户类型？
root 用户，超级管理员，和由 root 用户创建的普通用户

(2)在MySQL中如何创建用户？
CREATE USER username IDENTIFIED BY ‘password’;

(3)在MySQL中如何查看用户信息？
SELECT USER,NOST FROM USER(该表位于 mysql 库中)

(4)在MySQL中如何为用户分配权限？
GRANT 权限 ON 数据库.表 TO 用户名@登录主机 IDENTIFIED BY “密码”
GRANT ALL PRIVILEGES ON . TO ‘username’@‘localhost’ IDENTIFIED BY ‘password’

(5)MySQL中有哪些权限？
ALTER: 修改表和索引。
CREATE: 创建数据库和表。
DELETE: 删除表中已有的记录。
DROP: 删除数据库和表。
INDEX: 创建或删除索引。
INSERT: 向表中插入新行。
SELECT: 检索表中的记录。
UPDATE: 修改现存表记录。
FILE: 读或写服务器上的文件。
PROCESS: 查看服务器中执行的线程信息或杀死线程。
RELOAD: 重载授权表或清空日志、主机缓存或表缓存。
SHUTDOWN: 关闭服务器。
ALL: 所有权限，ALL PRIVILEGES 同义词。
USAGE: 特殊的 “无权限” 权限

(6)在MySQL中如何刷新权限？
FLUSH PRIVILEGES

(7)在MySQL中如何删除用户？
DROP USER username@localhost

12.MySQL中使用Navicat管理用户
(1)使用Navicat创建u_bjsxt用户并分配权限。
(2)使用Navicat删除u_bjsxt用户。

13.MySQL中的分页查询
(1)MySQL中的分页原则是什么？
• 在 MySQL 数据库中使用 LIMIT 子句进行分页查询。
• MySQL 分页中开始位置为 0。
• 分页子句在查询语句的最后侧。

(2)MySQL中分页语法是什么？
SELECT 投影列 FROM 表名 WHERE 条件 ORDER BY LIMIT 开始位置，查询数量
SELECT 投影列 FROM 表名 WHERE 条件 ORDER BY LIMIT 查询数量 OFFSET
开始位置。

14.MySQL执行查询过程介绍
(1)MySQL执行计划的作用是什么？
在 MySQL 中可以通过 explain 关键字模拟优化器执行 SQL 语句，从而知道 MySQL 是如何处理 SQL 语句的。

(2)MySQL执行查询处理过程是什么？
• 客户端向 MySQL 服务器发送一条查询请求
• 服务器首先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果。否则进
入下一阶段
• 服务器进行 SQL 解析、预处理、再由优化器生成对应的执行计划
• MySQL 根据执行计划，调用存储引擎的 API 来执行查询
• 将结果返回给客户端，同时缓存查询结果

15.MySQL的执行计划
(1)执行计划中包含哪些列？
1、ID
2、select_type
3、table
4、type
5、possible_keys
6、Key
7、key_len
8、ref
9、rows
10、Fitered
11、extra

(2)在执行计划中每个列的作用是什么？
1、ID：id 值相同时表示从上向下执行，id 值相同被视为一组，如果是子查询，id 值会递增，id 值越高，优先级越高
2、select_type：
simple:表示查询中不包含子查询或者 union
primary:当查询中包含任何复杂的子部分，最外层的查询被标记成 primary
derived:在 from 的列表中包含的子查询被标记成 derived
subquery:在 select 或 where 列表中包含了子查询，则子查询被标记成 subquery
union:两个 select 查询时前一个标记为 PRIMARY，后一个标记为 UNION。union 出现
在 from 从句子查询中，外层 select 标记为 PIRMARY，union 中第一个查询为 DERIVED，
第二个子查询标记为 UNION
unionresult：从 union 表获取结果的 select 被标记成 union result 。
3、table：显示这一行的数据是关于哪张表的。
4、type：显示连接使用了何种类型。从最好到最差的连接类型为 system、const、
eq_reg、ref、range、index 和 ALL。
system：表中只有一行数据。属于 const 的特例。如果物理表中就一行数据为 ALL
const ：查询结果最多有一个匹配行。因为只有一行，所以可以被视为常量。const 查询速度非常快，因为只读一次。一般情况下把主键或唯一索引作为唯一条件的查询都是 const
eq_ref：查询时查询外键表全部数据。且只能查询主键列或关联列。且外键表中外键列中数据不能有重复数据，且这些数据都必须在主键表中有对应数据（主键表中数据可以有没有用到的）
ref：相比 eq_ref，不对外键列有强制要求，里面的数据可以重复，只要出现重复的数据取值就是 ref。也可能是索引查询。
range：把这个列当作条件只检索其中一个范围。常见 where 从句中出现 between、<、in 等。主要应用在具有索引的列中
index：这个连接类型对前面的表中的每一个记录联合进行完全扫描（比 ALL 更好，因为索引一般小于表数据）。
ALL：这个连接类型对于前面的每一个记录联合进行完全扫描，这一般比较糟糕，应该尽量避免
5、possible_keys：查询条件字段涉及到的索引，可能没有使用。
6、Key：实际使用的索引。如果为 NULL，则没有使用索引。
7、key_len：表示索引中使用的字节数，查询中使用的索引的长度（最大可能长度），并非实际使用长度，理论上长度越短越好。key_len 是根据表定义计算而得的，不是通过表内检索出的。
8、ref：显示索引的哪一列被使用了，如果可能的话，是一个常量 const。
9、rows：根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。
10、Fitered：显示了通过条件过滤出的行数的百分比估计值。
11、extra：MYSQL 如何解析查询的额外信息。

16.MySQL的存储引擎介绍
(1)如何查看MySQL的存储引擎？
SHOW ENGINES

(2)MySQL5.7中提供了哪些存储引擎？
1、ISAM(Indexed Sequential Access Method)
2、MyISAM
3、InnoDB

(3)如何修改数据库级别的存储引擎？
修改 MySQL 的 my.ini 配置文件
C:\ProgramData\MySQL\MySQL Server 5.7default-storage-engine=数据库引擎名称
重启 MySQL

(4)如何修改标级别的存储引擎？
ALTER TBALE tableName engine=InnoDB
查询表的存储引擎
show create table table_name;

---

1. t ↩︎