一、简答题
1.简述你们公司使用的MySQL版本，并说明具体小版本及GA时间？
mysql 5.6.38,GA时间是2016年9月13
mysql 5.7.20,GA时间是2016年9月13
2.请介绍你熟悉的数据库关系系统的种类和代表产品名称？
RDBMS(关系型数据库)：mysql、Oracle、PG
NoSQL(非关系型数据库)：MongoDB、Redis、ES
NewSQL(分布式数据库):TiDB，Spanner ，PolarDB
3.请简述MySQL二进制安装重点步骤？
- 下载并上传软件至/server/tools
- 解压软件
- 用户的创建处理原始环境
- 设置环境变量
- 创建数据路径并授权
- 初始化数据（创建系统数据）
- 配置文件的准备
- 启动数据库
4.怎么确认数据库启动成功了？
（1）检查端口：netstat -lntup|grep mysqld
（2）检查服务：ps -ef|grep mysqld
（3）登录数据库
5.简述你了解的MySQL分支版本情况？
5.6 ： 5.6.34 5.6.36 5.6.38（2017913） 5.6.40
5.7 ： 5.7.18 ，5.7.20（2017913） ，5.7.24 ，（上课版本：5726）
8.0 ： 8014， 8015 ，8016
6.请简述mysqld的程序结构（1条SQL语句的执行过程）
1.经过连接层：
1.1提供连接协议：socket和TCPIP
1.2验证用户名密码合法性，并核对授权表
1.3生成一个专用链接线程
2.SQL层：
2.1验证SQL语法和SQL_MODE
2.2验证语义
2.3验证权限
2.4解析器进行语句解析，生成执行计划（解析树）
2.5优化器（各种算法，基于执行代价），根据算法，找到代价最低的执行计划。
2.6执行器按照优化器选择执行计划，执行SQL语句，得出获取数据的方法。
2.7提供query cache(默认不开)，一般不开，会用redis
2.8记录操作日志（binlog），默认没开
3.存储引擎层：
根据SQL层提供的取数据的方法，拿到数据，返回给SQL，结构化成表，再又连接层线程返回给用户。
7.请简述你了解的MySQL的启动方式
辅助脚本方式（普通的启动关闭）：
sys-v : /etc/init.d/mysqld
/etc/init.d/mysqld start
----> /application/mysql/bin/mysqld_safe
----> /application/mysql/bin/mysqld
systemd : /etc/systemd/system/mysqld.service
systemctl start mysqld
---->/application/mysql/bin/mysqld
维护性的启动方式：
/application/mysql/bin/mysqld_safe --skip-grant-tables --skip-networking &
/application/mysql/bin/mysqld &
8.简述MySQL配置文件默认读取顺序
[root@db01 ~]$ mysqld --help --verbose|grep my.cnf
/etc/my.cnf 优先于/etc/mysql/my.cnf 优先于/usr/local/mysql/etc/my.cnf 优先于 ~/.my.cnf 优先于 命令行执行
注：如果同时有多个配置文件，优先级低的，会覆盖掉优先级高的，最终以命令行输入为准
9.mysqld_safe --default-files=/opt/my.cnf &是什么作用？
指定配置文件启动mysql，而不使用/etc/my.cnf
10.忘记管理员root的密码处理过程，请对参数详细说明
- 使用如下命令，启动数据库到维护模式：
mysqld_safe --skip-grant-tables --skip-networking &
--skip-grant-tables 跳过授权表，不验证密码
--skip-networking 不允许远程连接- 登录mysql后，使用如下命令修改密码：
alter user root@'localhost' identified by '密码';- flush privileges; ====>刷新数据库
- 关闭数据库，正常启动验证
11.请列举SQL语句的常用种类
DDL：数据定义语句
DCL：数据控制语句
DML：数据操作语句
DQL：数据查询语句
12.请说明聚集索引和辅助索引的区别
（1）聚集索引只能有一个，非空唯一，一般是主键
（2）辅助索引可以有多个，是配合聚集索引使用的
（3）聚集索引叶子节点，就是磁盘的数据行存储的数据页
（4）MySQL是根据聚集索引，组织存储数据，数据存储时就是按照聚集索引的顺序进行存储数据
（5）辅助索引只会提取索引键值，进行自动排序生成B树结构
13.请简述以下语句执行计划可能存在的问题
阐述以下语句可能存在的问题，并提出合理解决方案
explain select * from city where countrycode='CHN' order by population;
会出现using filesort;原因是：条件where countrycode='CHN'执行时，相当于进行一次排序，而执行order by population时又根据人口数量进行排序，多次由不同列排序会出现using filesort
用countrycode列和population列建立联合索引
简述出现以下结果的可能原因
SQL语句的字符串类型是需要加引号的，如果不加引号，会将字符串类型当做数字类型，隐式转换导致索引失效,所以就不走索引
14. 请简述，影响索引树高度的因素？
1.数据行的多少，优化方案是：分表存储
2.索引列的字符长度，优化方案是：前缀索引
3.char varchar，优化方案是：建表时合理设计
4.enum，优化方案是：能用则用
15.请说明数据库启动失败的处理思路？
查看日志：
在哪？
/data/mysql/data/主机名.err
[ERROR] 上下文
可能情况：
/etc/my.cnf 路径不对等
/tmp/mysql.sock文件修改过 或 删除过
数据目录权限不是mysql
参数改错了
16. MySQL索引的种类都有哪些？
按算法分类：B树（B-tree、B+tree、B*tree）、R树、Hash索引、full text、GIS
按功能分类：辅助索引（单列索引、联合多列索引、唯一索引）、聚集索引
17. 你了解的MySQL存储引擎种类有哪些？
InnoDB
MyISAM
MEMORY
ARCHIVE
CSV
18.InnoDB存储引擎核心特性
1、事务（Transaction）
2、MVCC（Multi-Version Concurrency Control多版本并发控制）
3、行级锁(Row-level Lock)
4、ACSR（Auto Crash Safey Recovery）自动的故障安全恢复
5、支持热备份(Hot Backup)
6、复制Replication: Group Commit , GTID (Global Transaction ID) ,多线程(Multi-Threads-SQL )
二、操作题
1.创建管理员用户：oldboy能通过10.0.0.0/24网段任意地址登录管理MySQL
create user oldboy@'10.0.0.0/24' identified by '123456';
2.创建应用用户：wordpress能通过172.16.1.0/24网段任意地址登录操作wordpress库下的所有表
grant all on wordpress.* to wordpress@'172.16.1.0/24' identified by '123456';
3.请写出/etc/my.cnf的基础配置信息
[mysqld] <===服务端
user=mysql <===管理用户
basedir=/application/mysql <===默认安装路径
datadir=/data/mysql/data <===默认数据存放路径
socket=/tmp/mysql.sock <===socket文件路径
server_id=6 <===多实例时用到次参数
port=3306 <===端口号
[mysql] <===客户端
socket=/tmp/mysql.sock <===客户端的socket文件
4.请写出使用oldboy用户远程登录MySQL的具体语句
mysql -uoldboy -p -h10.0.0.15 -P3306
5.查看当前数据库的字符集
show create database oldboy;
6. 创建GBK字符集的数据库oldboy，并查看已建库完整语句
创建：
create database oldboy charset=GBK;
查看：
show create database oldboy;
7. 请分别介绍 NOT NULL default auto_increament 的作用
NOT NULL：非空
default：默认
auto_increament：自增
8. 创建用户oldboy，使之可以管理数据库oldboy
grant all on oldboy.* to oldboy@'10.0.0.%' identified by '123456';
9. 收回oldboy用户的drop权限
revoke drop on oldboy.* from 'oldboy'@'10.0.0.%';
10. 查看创建的用户oldboy拥有哪些权限
show grants for oldboy@'localhost';
11. 查看建表结构及表结构的SQL语句
show create table 表名;
desc 表名;
12. 插入一条数据“1,oldboy”
insert into oldguo values (1,'oldboy');
13.再批量插入2行数据“2,老男孩”，“3,oldboyedu”
insert into oldguo values (2,'老男孩'),(3,'oldboyedu');
14.查询名字为oldboy的记录
select * from 表名 where name='oldboy';
15. 查看数据库中所有引擎的类型
show engines;
16.查看数据库关于日志的参数配置
show binary logs xxxx;
17.查看handler_read_key当前的状态信息
show status like 'handler_read_key';
18. 列出删除表中数据的方式
删除列
alter table oldguo drop sid;
删除某一行：
delete from oldguo where id=1;
清空一个表的全部数据：
delete from oldguo;
truncate table oldguo;
19.test表中，有id、name、shouji列。把id列设置为主键，在Name字段上创建普通索引
alter table test modify id int primary key;
alter table test add index idx_na(name);
20. 在手机字段上对前8个字符创建普通索引
alter table test add index idx_sh(shouji(8));
21.查看创建的索引及索引类型等信息
show index from test;
22.删除Name，shouji列的索引
alter table test drop index idx_na;
alter table test drop index idx_sh;
23.对Name列的前6个字符以及手机列的前8个字符组建联合索引
alter table test add index idx_na_sh(name(6),shouji(8));
24. 将shouji列索引替换为唯一键索
alter table test drop index idx_sh;
alter table test add unique index idx_sh;
25.如何查看world数据库下city表中population列的重复值情况
select population,count(population) from city group by population;
26. 请列出explain命令中type中多种类型
all： 全表扫描，不走索引
index： 全索引扫描
range： 索引范围扫描
ref： 辅助索引等值查询
eq_ref： 在多表连接查询时，on的条件列是唯一索引或主键
const,system： 主键或唯一键等值查询
null： 查不到数据
27.Select查询语句加强练习
统计世界上每个国家的总人口数.
select CountryCode,sum(Population) from city group by CountryCode;
统计中国各个省的总人口数量
select district,sum(Population) from city where CountryCode='CHN' group by district;
统计世界上每个国家的城市数量
select countrycode,count(district) from city group by countrycode;
统计中国每个省的总人口数，将总人口数小于100w进行从高到低排序显示
select district,sum(Population) from city where CountryCode='CHN' group by districthaving sum(Population)<1000000order by sum(Population) desc;
28.生成整个数据库下的所有表的单独备份语句
select concat("mysqldump -uroot -p123456 ",table_schema," ",table_name," >/tmp/",table_schema,"_",table_name,".sql")
from information_schema.tables;
29. SQL综合练习
1. 查询平均成绩大于60分的同学的学号和平均成绩；
select student.sno,student.sname,avg(score.score)
from student
join score
on student.sno=score.sno
group by student.sno
having avg(score.score)>60;
2. 查询所有同学的学号、姓名、选课数、总成绩；
select student.sno,student.sname,count(course.cno),sum(score.score)
from student
join score
on student.sno=score.sno
join course
on score.cno=course.cno
group by student.sno;
3. 查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
select course.cno,course.cname,max(score.score),min(score.score)
from score
join course
on score.cno=course.cno
group by course.cno,course.cname;
4. 统计各位老师,所教课程的及格率
select teacher.tname,course.cname,
concat(100*sum(case when score.score>=60
then 1 end)/count(*),"%") as 及格率
from teacher
join course
on teacher.tno=course.tno
join score
on course.cno=score.cno
group by teacher.tno,course.cno;
5. 查询每门课程被选修的学生数
select course.cname,count(student.sno)
from student
join score
on student.sno=score.sno
join course
on score.cno=course.cno
group by course.cno;
6. 查询出只选修了一门课程的全部学生的学号和姓名
select student.sno,student.sname,group_concat(course.cname),count(course.cno)
from student
join score
on student.sno=score.sno
join course
on score.cno=course.cno
group by student.sno
having count(course.cno)=1;
7. 查询选修课程门数超过1门的学生信息
select student.sno,student.sname,student.sage,student.ssex,group_concat(course.cname),count(course.cno)
from student
join score
on student.sno=score.sno
join course
on score.cno=course.cno
group by student.sno
having count(course.cno)>1;
8. 统计每门课程:优秀(85分以上),良好(70-85),一般(60-70),不及格(小于60)的学生列表
select student.sno,student.sname,course.cname,score.score,
case
when school.score.score<60
then '不及格'
when score.score>=60 and score.score<70
then '一般'
when score.score>=70 and score.score<85
then '良好'
when score.score>=85
then '优秀'
END as '级别'
from student
join score
on student.sno=score.sno
join course
on score.cno=course.cno;
9. 查询平均成绩大于85的所有学生的学号、姓名和平均成绩
select student.sno,student.sname,avg(score.score)
from student
join score
on student.sno=score.sno
group by student.sno
having avg(score.score)>85;
