mysql分页关联查询执行过程_掌握聚合函数和分组、子查询、正则、索引、用户管理、分页、查询过程 、执行计划mysql的引擎...

学习主题：MySQL

学习目标：1 掌握 聚合函数和分组

2 掌握子查询

3 掌握正则

MySQL中的聚合函数

MySQL5.7是否支持SQL99标准？

支持

MySQL中的数据分组

MySQL中有哪些常用的聚合函数(组函数)？

Avg()求平均值(整数)

Sum()分组求和(整数)

Min()求分组最小值

Max()求分组最大值

Count()求行数

每个聚合函数的作用是什么？

Avg()求平均值 参数：(整数)

Sum()分组求和 参数：(整数)

Min()求分组最小值 参数：(字符、数字、日期)

Max()求分组最大值 参数：(字符、数字、日期)

Count()求行数

Count(*)

Count(expr)

Count(distinct expr)

在MySQL中通过什么子句来创建数据分组？

Group by

如何约束数据分组？

Having()

计算每个部门的平均薪水

Select avg(e.salary) from employeese group by e.department_id;

显示那些最高薪水大于 5000 的部门的部门号和最高薪水。

select e.department_id,max(e.salary) from employees e group by e.department_id HAVING MAX(e.salary)>500;

MySQL中的子查询

MySQL的子查询可以放到SQL语句中的什么位置？

Where 子句

Having子句

From 子句

使用子查询的原则是什么？

子查询放在圆括号中。

将子查询放在比较条件的右边。

在单行子查询中用单行运算符，在多行子查询中用多行运算符。

在MySQL中如何处理多行子查询？

In 等于列表中的任何成员

any 比较子查询返回的每个值

all比较子查询返回的全部值

MySQL中的正则表达式

MySQL中正则表达式的特点是什么？

使用正则表达式定义字符串的搜索条件，性能高于like

可以对整数类型或者字符类型检索

使用regexp关键字表示正则匹配

默认忽略大小写，如果区分大小写，使用binary关键字

MySQL中使用正则表达式的语法结构是什么？

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

^在正则表达式中表示开始

$在正则表达式中表示结尾

. 英文的点，它匹配任何一个字符，包括回车、换行等

“*”：星号匹配 0 个或多个字符，在它之前必须有内容。

"+"：加号匹配 1 个或多个字符，在它之前也必须有内容。

“?”：问号匹配 0 次或 1 次。

“|”：表示或者含

“[a-z]”：字符范围

“^[....]”：以什么字符开头的

“[^....]”：匹配不包含在[]的字符

“{n}”：固定次数。

“{n,m}”：范围次数。

查询雇员表中名字是以k开头的雇员名字与薪水。

Select last_name,salary from employees where last_name REGEXP binary '^K';

查询雇员表中名字是以n结尾的雇员名字与薪水。

Select last_name,salary from employees where last_name REGEXP binary 'n$';

MySQL中创建普通索引

MySQL中的索引类型有哪些？

• 普通索引

• 唯一索引

• 主键索引

• 组合索引

• 全文索引

在MySQL中创建普通索引的方式有些？

直接创建索引

CREATE INDEX index_name ON table(column(length));

修改表添加索引

ALTER TABLE table_name ADDINDEX index_name(column(length));

创建表时指定索引列

CREATE TABLE`table`( COLUMNTYPE, PRIMARYKEY(`id`), INDEX index_name(column(length)) );

如何查询一个表中的索引？

Show index from table_name;

如何删除一个索引？

DROP INDEX index_name ON table;

学习主题：MySQL

学习目标：1 掌握Mysql索引

2 掌握用户管理

3 掌握分页 查询过程 执行计划

4 了解mysql的引擎

MySQL中创建唯一索引

什么是唯一索引？

唯一索引与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值

在MySQL中如何创建唯一索引？

CREATE UNIQUE INDEX indexName ON table(column(length));

MySQL中创建主键索引

什么是主键索引？

主键索引是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建 表的时候同时创建主键索引。

在MySQL中如何创建主键索引？

修改表添加主键索引

ALTER TABLE 表名 ADD PRIMARYKEY(列名);

创建表时指定主键索引

CREATE TABLE`table`( COLUMNTYPE, PRIMARYKEY(column) );

MySQL中创建组合索引

什么是组合索引？

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

什么是最左前缀原则？

就是最左优先

如：我们使用表中的 name，address，salary 创建组合索引，那么想要组合索引生效， 我们只能使用如下组合： name/address/salary name/address name/ 如果使用 addrees/salary 或者是 salary 则索引不会生效。

MySQL中如何创建组合索引？

修改添加组合索引

ALTER TABLE table_name ADD INDEX index_name(column(length),column(length));

创建表时创建组合索引

CREATE TABLE`table`(COLUMNTYPE,INDEX index_name(column(length),column(length)) );

MySQL中创建全文索引

什么是全文索引？

全文索引(FULLTEXTINDEX)主要用来查找文本中的关键字，而不是直接与索引中的值 相比较。 FULLTEXT 索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的 where 语句的参数匹配。FULLTEXT 索引配合 match against 操作使用，而不是一般的 where 语句 加 like。

全文索引可以从 CHAR、VARCHAR 或 TEXT 列中作为 CREATETABLE 语句的一部分 被创建，或是随后使用 ALTERTABLE 添加。不过切记对于大容量的数据表，生成全文索 引是一个非常消耗时间非常消耗硬盘空间的做法。

如何创建全文索引？

修改添加全文索引

ALTER TABLE table_name ADD FULLTEXT index_content(content);

创建表时创建全文索引

CREATE TABLE`table`( COLUMNTYPE, FULLTEXTindex_name (column) );

如何删除全文索引？

DROP INDE Xindex_name ON table ;

ALTER TABLE table_name DROP INDEX index_name;

MySQL中使用全文索引

在MySQL中如何使用全文索引？

全 文 索 引 的 使 用 与 其 他 索 引 不 同 。 在 查 询 语 句 中 需 要 使 用 match(column) against(‘content’) 来检索数据

什么是全文解析器？

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

在创建全文索引时如何指定解析器？

在创建全文索引时可以指定 ngram 解析器

ALTER TABLE table_name ADD FULLTEXT index_content(content) WITH PARSER NGRAM;

MySQL中的用户管理

MySQL中有几种用户类型？

root 用户

超级管理员

由 root 用户创建的普通用户

在MySQL中如何创建用户？

CREATE USER username IDENTIFIED BY 'password';

在MySQL中如何查看用户信息？

SELECT USER,NOST FROM USER(该表位于 mysql 库中);

在MySQL中如何为用户分配权限？

新用户创建完后是无法登陆的，需要分配权限。 GRANT 权限 ON 数据库.表 TO 用户名@登录主机 IDENTIFIEDBY"密码" GRANTALLPRIVILEGESON*.*TO'username'@'localhost'IDENTIFIEDBY'password' 登陆主机： % 匹配所有主机 localhost localhost 不会被解析成 IP 地址，直接通过 UNIXsocket 连接 127.0.0.1 会通过 TCP/IP 协议连接，并且只能在本机访问； ::1 ::1 就是兼容支持 ipv6 的，表示同 ipv4 的 127.0.0.1

MySQL中有哪些权限？

ALTER: 修改表和索引。

CREATE: 创建数据库和表。

DELETE: 删除表中已有的记录。

DROP: 删除数据库和表。

INDEX: 创建或删除索引。

INSERT: 向表中插入新行。

SELECT: 检索表中的记录。

UPDATE: 修改现存表记录。

FILE: 读或写服务器上的文件。

PROCESS: 查看服务器中执行的线程信息或杀死线程。

RELOAD: 重载授权表或清空日志、主机缓存或表缓存。

SHUTDOWN: 关闭服务器。

ALL: 所有权限，ALLPRIVILEGES 同义词。

USAGE: 特殊的 "无权限" 权限

在MySQL中如何刷新权限？

每当调整权限后，通常需要执行以下语句刷新权限 FLUSHPRIVILEGES

在MySQL中如何删除用户？

DROPUSERusername@localhost

MySQL中使用Navicat管理用户

使用Navicat创建u_bjsxt用户并分配权限。

创建用户

分配权限

使用Navicat删除u_bjsxt用户。

MySQL中的分页查询

MySQL中的分页原则是什么？

• 在 MySQL 数据库中使用 LIMIT 子句进行分页查询。

• MySQL 分页中开始位置为 0。

• 分页子句在查询语句的最后侧

MySQL中分页语法是什么？

LIMIT 子句：SELECT 投影列 FROM 表名 WHERE 条件 ORDERBYLIMIT 开始位置，查询数量。

LIMITOFFSET 子句：SELECT 投影列 FROM 表名 WHERE 条件 ORDER BY LIMIT 查询数量 OFFSET 开始位置。

MySQL执行查询过程介绍

MySQL执行计划的作用是什么？

在 MySQL 中可以通过 explain 关键字模拟优化器执行 SQL 语句，从而知道 MySQL 是 如何处理 SQL 语句的。

MySQL执行查询处理过程是什么？

• 客户端向 MySQL 服务器发送一条查询请求

• 服务器首先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果。否则进 入下一阶段

• 服务器进行 SQL 解析、预处理、再由优化器生成对应的执行计划

• MySQL 根据执行计划，调用存储引擎的 API 来执行查询

• 将结果返回给客户端，同时缓存查询结果

MySQL的执行计划

执行计划中包含哪些列？

ID

select_type

table

type

possible_keys

Key

key_len

ref

rows

0Fitered

extra

在执行计划中每个列的作用是什么？

ID:查询执行顺序

Select_type: simple union primary derived subquery union

Table:显示这一行的数据是关于哪张表的

Type：显示连接使用了何种类型。从最好到最差的连接类型为 system、const、 eq_reg、ref、range、index 和 ALL。

possible_keys：查询条件字段涉及到的索引，可能没有使用。

Key：实际使用的索引。如果为 NULL，则没有使用索引。

key_len：表示索引中使用的字节数，查询中使用的索引的长度(最大可能长度)，并非实际使用 长度。

Ref：显示索引的哪一列被使用了，如果可能的话，是一个常量 const。

Rows：根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数

Fitered：显示了通过条件过滤出的行数的百分比估计值

Extra：MYSQL 如何解析查询的额外信息。

MySQL的存储引擎介绍

如何查看MySQL的存储引擎？

SHOW ENGINES;

MySQL5.7中提供了哪些存储引擎？

ISAM(IndexedSequentialAccessMethod)

MyISAM

InnoDB

如何修改数据库级别的存储引擎？

修改 MySQL 的 my.ini 配置文件 C:\ProgramData\MySQL\MySQLServer5.7 default-storage-engine=数据库引擎名称 重启 MySQL

如何修改标级别的存储引擎？

ALTERTBALE tableNameengine=InnoDB 查询表的存储引擎 showcreatetabletable_name;