1. MySQL中的聚合函数
(1) MySQL5.7是否支持SQL99标准？
Sql95 后标准 主流数据库都支持
支持
2. MySQL中的数据分组
(1) MySQL中有哪些常用的聚合函数(组函数)？
Avg(int):求平均数 ,
Min((int,string,date)):求最小值
Max((int,string,date)):求最大值
Sum(int):求和
Count():返回一个表中的行数
Count(*)
Count(expr)
Count(distinct expr)
(2) 每个聚合函数的作用是什么？
Avg(int):求平均数 ,
Min((int,string,date)):求最小值
Max((int,string,date)):求最大值
Sum(int):求和
Count():返回一个表中的行数
Count(*)
Count(expr)
Count(distinct expr)
(3) 在MySQL中通过什么子句来创建数据分组？
Group by 分组函数
(4) 如何约束数据分组？
Having:约束分组结果
(5) 计算每个部门的平均薪水
Select avg(salary) from employees group by department_id;
(6) 显示那些最高薪水大于 5000 的部门的部门号和最高薪水。
Select department_id,max(salary) from employees group by department_id having max(salary)>5000;
3. MySQL中的子查询
(1) MySQL的子查询可以放到SQL语句中的什么位置？
可以将子查询放在许多的sql字句中,包括:
Where字句
Having字句
From字句
(2) 使用子查询的原则是什么？
子查询放在圆括号中
将子查询放在比较条件的右边
在单行子查询中用单行运算符,在多行子查询中用多行运算符
谁的薪水比 Oldlu 高
Select name from employees where salary >(select salary from employees where name=’Oldlu’)
查询 Oldlu 的同事，但是不包含他自己
Select name from employees where department_id=(select department_id from employees where name=’Oldlu’) and name <> ‘Oldlu’;
(3) 在MySQL中如何处理多行子查询？
用多行运算符处理
查找各部门收入为部门最低的那些雇员。显示他们的名字，薪水以及部门 ID
select name,salary,department from employees where salary in( Select min(salary) from employees group by department_id) ;
4. MySQL中的正则表达式
(1) MySQL中正则表达式的特点是什么？
1. mysql中允许使用正则表达式定义字符串的搜索条件.(性能高于like)
2. Mysql中的正则表达式可以对整数类型或者字符类型检索;
3. 使用regexp关键词表示正则匹配
4. 默认忽略大小写,如果要区分大小写,使用binary关键字
正则表达式的模板及其含义
模板
什么模式匹配
^
字符串的开始
$
字符串的结尾
.
任何单个字符
[...]
在方括号内的任何字符列表
[^...]
非列在方括号内的任何字符
P1|p2|p3
交替匹配任何模式p1,p2,p3
*
零个或多个前面的元素
+
前面的元素的一个或多个实例
{n}
前面的元素的n个实例
{m,n}
m到n个实例前面的元素
(2) MySQL中使用正则表达式的语法结构是什么？
1. ”^”在正则表达式中表示开始
查询以 x 开头的数据(忽略大小写)
Select 列名 from 表名 where 列名 regexp’^x’
查询雇员表中名字是以 k 开头的雇员名字与薪水
Select name,salary from employees where name regexp binary’^k’;
2. ”$”表示结尾
查询以x 结尾的数据(忽略大小写)
Select 列名 from 表名 where 列名 regexp ’x$’
查询雇员表中名字是以 n 结尾的雇员名字与薪水
Select name,salary from employees where name regexp binary ’n$’;
3. ”.”符号
英文的点,它匹配任何一个字符,包括回车,换行等.
Select 列名 from 表名 where 列名 regexp ’x.’
查询雇员表中名字含有 o 的雇员的姓名与薪水
Select from employees where name regexp ’o.’
4. ”*”
“*”:星号匹配0个或多个字符,在它之前必须有内容
5. ”+”
“+”:加号匹配一个或多个字符,在他之前也必须有内容
Select 列名 from 表名 where 列名 regexp ’x+’;匹配大于一个的任意字符
6. ”?”
“?”:问号匹配0次或1次
Select 列名 from 表名 where 列名 regexp ’x?’;匹配0个或多个字符
7. ”|”
“|”:表示或者含义
Select 列名 from 表名 where 列名 regexp ‘abc|bcd’;匹配包含abc或bcd
查询雇员表中名字含义ke或者lu的雇员的名字和薪水
Select last_name,salary from employees where last_name regexp ’le|lu’
8. ”[a-z]”
“[a-z]”:字符范围
“^[....]”:以什么字符开头
“[^....]”:匹配不包含在[]的字符
Select 列名 from 表名where 列名 regexp ’[a-z]’;匹配内容包括a-z范围的数据
查询雇员表中名字包含x,y,z字符的雇员的名字和薪水
Select last_name,salary from employees where last_name regexp’[x-z]’
查询雇员名字是t,f开头的雇员名字和薪水
Select last_name,salary from employees where last_name regexp’^[t|f]’;
查询雇员的名字和薪水,不包含oldlu
Select last_name,salary from employees where last_name regexp’[^oldlu]’;
9. ”{n}”
“{n}”:固定次数
匹配以s连续出现两次的所有数据,匹配任意出现两次的数据以下可以写成 s.{2};
Select * from student where name regexp’s{2}’
查询雇员名字含有连续两个 e 的雇员的姓名与薪水
Select last_name,salary from employees where last_name regexp ’e{2}’;
10. “{n,m}”
“{n,m}”范围次数
匹配以s开头且重复2到5次的所有数据
Select * from employees where name regexp’^s{2,5}’
查询雇员名字中包含一个或者两个o的雇员名字和薪水
Select name,salary from employees where name regexp ’o.{1,2}’;
(3) MySQL中支持哪些正则表达式的模板？其对应的含义是什么？
(4) 查询雇员表中名字是以k开头的雇员名字与薪水。
Select last_name,salary from employees where last_name regexp binary’^k’
(5) 查询雇员表中名字是以n结尾的雇员名字与薪水。
Select last_name,salary from employees where last_name regexp ’n$’;
5. MySQL中创建普通索引
(1) MySQL中的索引类型有哪些？
1. 普通索引
2. 唯一索引
3. 主键索引
4. 组合索引
5. 全文索引
1. 普通索引:最基本的索引,它没有任何限制
在创建索引时,可以指定索引长度.length为可选参数,表示索引的长度,只有字符串类型的字段才能指定索引长度,如果是BLOB(二进制大对象)和TEXT类型,必须指定length
创建索引时注意:
如果指定单列索引长度,length必须小于这个字段所允许的最大字符个数据个数
查询索引:show index from 表名
2. 唯一索引
唯一索引与普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值.
3. 主键索引
主键索引是一种特殊的唯一索引,一个表只能有一个主键,不允许有空值,一般是在建表的时候同时创建主键索引
4. 组合索引
组合索引是指使用多个字段创建的索引,只有在查询条件中使用了创建索引时的第一个字段,索引才会被使用(最左前缀原则)
5. 全文索引
全文索引(fulltext index)主要用来查找文本中的关键字,而不是直接与索引中的值相比较.fulltext索引跟其他索引大不相同,它更像是一个搜索引擎,而不是简单where语句的参数匹配.fulltext索引匹配match against操作使用,
而不是一般的where语句加like
全文索引可以从char,varchar或text列中作为create table语句的一部分被创建,或是随后使用alter table 添加,不过切记对于大容量的数据表,生成全文索引是一个非常消耗时间非常消耗银盘空间的做法
(2) 在MySQL中创建普通索引的方式有些？
直接创建索引
Create index from 表名 on 索引名(列名(长度))
通过修改表来创建索引
alter table 表名add index 索引名(列名(长度));
创建表时创建索引
create table 表名(id INTEGER,name VARCHAR(10),index 索引名(列名))
(3) 如何查询一个表中的索引？
Show index from 表名
(4) 如何删除一个索引？
Drop index 索引名 on 表名五队
6. MySQL中创建唯一索引
(1) 什么是唯一索引？
唯一索引与普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值.
(2) 在MySQL中如何创建唯一索引？
Create unique index from 表名 on 索引名(列名(长度))
Alter table 表名 add unique index 索引名(列名(长度))
7. MySQL中创建主键索引
(1) 什么是主键索引？
主键索引是一种特殊的唯一索引,一个表只能有一个主键,不允许有空值,一般是在建表的时候同时创建主键索引
(2) 在MySQL中如何创建主键索引？
8. MySQL中创建组合索引
(1) 什么是组合索引？
6. 组合索引
组合索引是指使用多个字段创建的索引,只有在查询条件中使用了创建索引时的第一个字段,索引才会被使用(最左前缀原则)
(2) 什么是最左前缀原则？
按照创建组合索引时,的创建顺序查询时才会经过索引
例子:alter table employees add index 索引名(last_name,salary)
Select from employees where last_name=? and salary=?(经过索引)
Select from employees where last_name=?(经过索引)
Select from employees where salary=?(不经过索引)
(3) MySQL中如何创建组合索引？
Alter table 表名add index 索引名(列名1,列名2...)
9. MySQL中创建全文索引
(1) 什么是全文索引？
全文索引(fulltext index)主要用来查找文本中的关键字,而不是直接与索引中的值相比较.fulltext索引跟其他索引大不相同,它更像是一个搜索引擎,而不是简单where语句的参数匹配.fulltext索引匹配match against操作使用,
而不是一般的where语句加like
全文索引可以从char,varchar或text列中作为create table语句的一部分被创建,或是随后使用alter table 添加,不过切记对于大容量的数据表,生成全文索引是一个非常消耗时间非常消耗银盘空间的做法
(2) 如何创建全文索引？
Alter table add fulltext 索引名(列名(长度))
(3) 如何删除全文索引？
DROP Iindex 索引名 on 表名
10. MySQL中使用全文索引
(1) 在MySQL中如何使用全文索引？
什么是全文解析器？全文索引的使用和其他索引不同.在查询语句中需要使用match(column)against(‘content’)来检索数据
Select 投影列 from 表名where match(全文索引名)against(‘搜索内容’)
(2)
全文索引中基本单位是”词”,分词,全文索引是以词为基础的,mysql默认的分词是所有非字母和数字的特殊符号都是分词符,在检索数据时我们给定的检索条件也是词
Mysql中默认的全文解析器不支持中文分词,如果数据含有中文需要更换全文解析器ngram
(3) 在创建全文索引时如何指定解析器？
在创建全文索引时可以指定ngram解析器
Create index fulltext 索引名(列名(长度)) with parser ngram
Alter table 表名 add fulltext 索引名(列名(长度)) 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’
登陆主机：
% 匹配所有主机
localhost localhost 不会被解析成 IP 地址，直接通过 UNIXsocket 连接
127.0.0.1 会通过 TCP/IP 协议连接，并且只能在本机访问；
::1 ::1 就是兼容支持 ipv6 的，表示同 ipv4 的 127.0.0.1
(5) MySQL中有哪些权限？
Alter:修改表和索引
Create:创建数据库和表
Delete:删除表中已有的记录
Drop:删除数据库和表
Index:创建或删除索引
Select:检索表中的数据
Update:修改现存表记录
File:读或写服务器上的文件
Process;查看服务器中执行的信息或杀死线程
Reload:重载授权表或清空日志,主机缓存或者表缓存
Shutdown:关闭服务器
All:所有权限
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中分页语法是什么？
1.limit字句
Select 投影列 from 表名 where 条件 order by limit开始位置,查询数量
查询雇员表中所有数据按 id 排序，实现分页查询，每次返回两条结果。
Select * from employees order by employee_id limit 0,2;
2. limit offset子句
1. select 投影列 from 表名 where 条件 order by limit 开始位置,查询数量
查询雇员表中所有数据按 id 排序，实现分页查询，每次返回两条结果。
Select * from employees order by employee_id limit 2 offset 0;
14. MySQL执行查询过程介绍
(1) MySQL执行计划的作用是什么？
在mysql中可以通过explain关键词模拟优化器执行sql语句,从而知道mysql是如何处理sql语句的
(2) MySQL执行查询处理过程是什么？
客户端向mysql服务器发送一条查询请求
服务器首先检查查询缓存.如果命中缓存,则立即返回存贮在缓存中的结果,否则进入下一阶段
服务器进行sql解析,预处理,再由优化器生成相应的执行计划
Mysql根据执行计划,调用存储引擎的api来执行查询
将结果返回给客户端,同时缓存查询结果
15. MySQL的执行计划
(1) 执行计划中包含哪些列？
1. Id 代表执行顺序 id越高优先级越高
2. Select_type
Simple:表示查询不包含子查询或者union(把两个子查询查询的结果集相同的列合并输出)
Primary:当查询中包含任何复杂的字部分,最外层的查询被标记为primary
Derived:在from后的子查询被标记derived
Subquery:在select和where 列表后包含了子查询,被标记为subquery
Union(把两个子查询查询的结果集相同的列合并输出);两个select查询时前一个标记为primary,后一个标记为union.union出现在from从句子查询中,外层select标记为primary,union中第一个查询为derived,第二个子查询标记为union
3. Table:这一行的数据来自那张表
4. type:重要的列,显示连接使用了何种类型(类似于oracle的cost代价值)
从好到坏的连接类型为,system,const,eq_reg,ref,range,index,all
5. possible_keys:查询条件字段涉及到的索引,可能没有使用
6. Key:实际使用的索引.为null表示没有使用索引
7. Key_len;表示索引中使用的字节数,查询中使用的索引的长度(最大可能长度),并非实际使用长度,理论上长度越短越好
8. ref:显示索引的那一列被使用了,如果可能的话,是一个常量const
9. Rows:估算出找到所需的记录和所需要读取的行数
10. Fitered:显示了通过条件过滤的行数的百分比估计值
11. Extra:MYSQL 如何解析查询的额外信息。
Distinct:MySQL 发现第 1 个匹配行后,停止为当前的行组合搜索更多的行。
Not exists:MySQL 能够对查询进行 LEFT JOIN 优化,发现 1 个匹配 LEFT JOIN 标准的行
后,不再为前面的的行组合在该表内检查更多的行。
range checked for each record (index map: #):MySQL 没有发现好的可以使用的索引,但发
现如果来自前面的表的列值已知,可能部分索引可以使用。
Using filesort:MySQL 需要额外的一次传递,以找出如何按排序顺序检索行。
Using index:从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的
列信息。
Using temporary:为了解决查询,MySQL 需要创建一个临时表来容纳结果。
Using where:WHERE 子句用于限制哪一个行匹配下一个表或发送到客户。
Using sort_union(...), Using union(...), Using intersect(...): 这 些 函 数 说 明 如 何 为
index_merge 联接类型合并索引扫描。
Using index for group-by:类似于访问表的 Using index 方式,Using index for group-by 表示
MySQL 发现了一个索引,可以用来查 询 GROUP BY 或 DISTINCT 查询的所有列,而不要
额外搜索硬盘访问实际的表
(2) 在执行计划中每个列的作用是什么？
16. MySQL的存储引擎介绍
(1) 如何查看MySQL的存储引擎？
Show engines;
(2) .7中提供了哪些存储引擎？
Isam ,myisam,innodb
1. myisam是isam的替代品 查询速度快,不支持事务和外键
2. Innodb查询速度相对较慢,支持事务和外键
innodb 与 myisam 区别
1. InnoDB 支持事务，MyISAM 不支持，对于 InnoDB 每一条 SQL 语言都默认封装成事
务，自动提交，这样会影响速度，所以最好把多条 SQL 语言放在 begin 和 commit 之间，组
成一个事务；
2. InnoDB 支持外键，而 MyISAM 不支持。对一个包含外键的 InnoDB 表转为 MYISAM
会失败；
3. InnoDB 是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引
效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，
主键不应该过大，因为主键太大，其他索引也都会很大。而 MyISAM 是非聚集索引，数据
文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
4. InnoDB 不保存表的具体行数，执行 select count(*) from table 时需要全表扫描。而
MyISAM 用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度
很快；
5. Innodb 不支持全文索引，而 MyISAM 支持全文索引，查询效率上 MyISAM 要高；(在
MySQL5.7 版本中已经支持全文索引)
(3) 如何修改数据库级别的存储引擎？
修改MySQL的my.ini配置文件
C:\programData\mysql\mysql server5.7
Default-stroage-engine=数据库引擎名称
重启mysql
(4) 如何修改标级别的存储引擎？
Alter table tableName engine=InnoDb
查询表的存储引擎
Show create table table_name
