一、简答题

1.简述你们公司使用的MySQL版本，并说明具体小版本及GA时间？

5.7.20   2017.9.13

2.请介绍你熟悉的数据库关系系统的种类和代表产品名称？

DBMS  数据库管理系统
RDBMS   （关系型数据库:）     Oracle , MySQL , PG ,  MSSQL
NoSQL   （非关系型数据库:）   MongoDB , Redis , ES
NEWSQL  （分布式）            TiDB , Spanner , AlisQL , OB , PolarDB

3.请简述MySQL二进制安装重点步骤？

1.要下载的MySQL版本一定要选择正确，不然会出现不兼容的情况
2.卸载和下载相关的依赖包
3.创建的管理MySQL软件和数据的用户是不可登录的虚拟用户
4.需要授权属主属组为创建的管理MySQL的用户，这一点很容易忘记
5.初始化数据库和数据库的配置文件和数据库具体所在的路径一定要匹配上，不然会出错
6.启动数据库的方式有很多，但尽量做到什么方式启动的也用什么方式关闭

4.怎么确认数据库启动成功了？

1.直接登录MySQL查看
2.netstat -lntup | grep 3306 查看数据库端口是否存在
3.ps -ef | grep mysql        查看数据库进程是否存在
4.查看mysql.sock文件是否按配置文件指定的路径出现（不建议此方法）
一般建议使用以上任意两种方法，即可确保MySQL 是否启动

5.简述你了解的MySQL分支版本情况？

6.请简述mysqld的程序结构（1条SQL语句的执行过程）

MySQL的程序结构分为三层：连接层，SQL层，存储引擎层
一条SQL语句执行过程也就是SQL层的执行过程：
1.验证语法
2.验证语义
3.验证权限
4.解析器进行语句解析，生成多种执行该条语句的执行方案
5.优化器进行筛选，根据多方面（cpu的消耗，磁盘io等）找到一个消耗最小的执行方案
6.执行器按照优化器给出的最佳的执行方案进行执行该条SQL语句
7）提供query cache(缓存，默认没有开)，一般也不开，用redis代替
8）记录操作日志（binlog）(只记录修改的记录，默认没开)

7.请简述你了解的MySQL的启动方式

（二进制安装）
1.自带启动方式：/application/mysql/support-files/mysql.server 'start|restart|stop'
2./etc/init.d启动 把/mysql/support-files/mysql.server移动到/etc/init.d下，然后即可通过该方式启动
[root@mysql52 /etc/init.d]# service mysqld ‘start|stop|restart’
3.systemctl管理启动 在/etc/systemd/system/添加一个配置文件，即可通过systemctl 管理

8.简述MySQL配置文件默认读取顺序

mysqld --help --verbose | grep "my.cnf"  通过这条命令，我们可以看到，MySQL配置文件的默认读取顺序是：
/etc/my.cnf  /etc/mysql/my.cnf  /usr/local/mysql/etc/my.cnf   ~/.my.cnf
优先级为后边的大于前边的，因为按照正常的读取，后边的会前边的都覆盖掉

9.mysqld_safe --default-files=/opt/my.cnf &是什么作用？

是启动MySQL时，强行指定读取的配置文件时/opt/my.cnf，即不读取其他任何的配置文件。

10.忘记管理员root的密码处理过程，请对参数详细说明

1.关闭数据库             systemctl stop mysqld
2.检查是否关闭           systemctl status mysqld
3.使用特殊参数启动数据库进入救援模式   mysqld_safe --skip-grant-tables --skip-networking &
--skip-grant-tables  是跳过连接层的账户密码验证环节
--skip-networking    是将连接层的远程TCP/IP连接方式关闭，即不允许远程连接
&符                  是将其放在后台运行，敲几下回车即可正常操作
现在可以直接免用户免密码登录
然后即可在MySQL内部通过sql语句重置密码                   mysql> alter user root@'localhost' identified by '1';
但是会报错，不允许该方式修改密码，没关系，执行这条语句： mysql> flush privileges;
再执行，重置密码即可                                     mysql> alter user root@'localhost' identified by '1';
密码重置之后，立即关闭数据库，然后正常启动数据库，验证登录即可。

11.请列举SQL语句的常用种类

DDL
DQL
DCL
DML

12.请说明聚集索引和辅助索引的区别

辅助索引可以有多个，配合聚集索引使用                 聚集索引只能有一个，非空且唯一，一般是主键列
辅助索引存放的单列的信息，自动排序生成B树            聚集索引存放的数据行全部信息
辅助索引是基于列产生的                               聚集索引是一般是根据主键列（ID）自动生成的，如果没有主键列，会选择唯一值多的列作为聚集索引

13.请简述以下语句执行计划可能存在的问题

14. 请简述，影响索引树高度的因素？

1.数据量过大                          解决方法:可以分库分表
2.索引列值长度                        解决方法:前缀索引
3.数据类型（char、varchar）           一般情况下，能用varchar就不用char
4.enum的使用                          建议使用enum

15.请说明数据库启动失败的处理思路？

一般情况下，数据库启动失败原因可分为三类;
1. /etc/my.cnf 路径与实际不一致
2. /tmp/mysql.sock   
3. mysql软件权限和路径权限不是mysql
还可以查看错误日志快速定位启动失败的原因，错误日志位置：mysql/主机名.err

16. MySQL索引的种类都有哪些？

B树索引        又分为:B-tree    B+tree    B*tree
R树索引
Hash索引
Full test
GIS

17. 你了解的MySQL存储引擎种类有哪些？

MyISAM存储引擎和InnoDB存储引擎
MyISAM把列的定义信息，数据行，和索引分为三个文件进行存储，分别是.frm ， .MYD , .MYI
InnoDB把列的定义信息放在.frm文件,把数据行和索引信息放在.ibd文件

18.InnoDB存储引擎核心特性
InnoDB把列的定义信息放在.frm文件,把数据行和索引信息放在.ibd文件

二、操作题

1.创建管理员用户：oldboy能通过10.0.0.0/24网段任意地址登录管理MySQL
create user ceshi@'10.0.0.0/255.255.254.0' identified by '123456' with grant option;

2.创建应用用户：wordpress能通过172.16.1.0/24网段任意地址登录操作wordpress库下的所有表

grant ALL on wordpress.* to wordpress@'10.0.0.0/255.255.254.0' identified by '123456';

3.请写出/etc/my.cnf的基础配置信息

mysql
[mysqld]                            服务端
user=mysql                          管理用户名
basedir=/application/mysql          程序路径
datadir=/data/mysql/data            数据路径
socket=/tmp/mysql.sock              sock文件路径
server_id=6                         主从复制时，代表自己的身份
port=3306                           端口号
log_error=/data/mysql/data/mysql.log         错误日志路径
log_bin=/data/mysql/data/mysql-bin           二进制文件位置+文件名前缀
[mysql]                             客户端
socket=/tmp/mysql.sock              连接时指定的sock文件

4.请写出使用oldboy用户远程登录MySQL的具体语句

mysql -uoldboy -p -h10.0.0.51

5.查看当前数据库的字符集

show create database 库名;
默认字符集为latin1

6. 创建GBK字符集的数据库oldboy，并查看已建库完整语句

create database oldboy character set gbk collate gbk_chinese_ci;

7. 请分别介绍 NOT NULL default auto_increament 的作用

not null                     非空，即为必填项
default                      设置默认值
auto_increament              自增

8. 创建用户oldboy，使之可以管理数据库oldboy

grant all on oldboy.* to user oldboy@'localhost' identified by '123456';

9. 收回oldboy用户的drop权限

revoke drop on *.* from 'oldboy'@'localhost';

10. 查看创建的用户oldboy拥有哪些权限

show grants for oldboy@'localhost';

11. 查看建表结构及表结构的SQL语句

show create table 表名;
desc 库.表;

12. 插入一条数据“1,oldboy”

insert into oldboy(id,name) values ('1','oldboy');

13.再批量插入2行数据“2,老男孩”，“3,oldboyedu”

insert into course(cno,cname,tno) values ('1005','alibaba','105'),('1006','tianmao','106');


删除alibaba 数据行（对应行号1005）
delete from course where cno=1005;

14.查询名字为oldboy的记录

select * from course where cname='oldboy';

15. 查看数据库中所有引擎的类型

show engines;

16.查看数据库关于日志的参数配置

show binary logs                 查看二进制日志情况
show binlog events in            查看二进制日志事件 
show relaylog events in          查看relay日志事件
show variables  like 'log%';     模糊查找

17.查看handler_read_key当前的状态信息

show status like 'handler_read_key';

18. 列出删除表中数据的方式

delete from 库.表 where 删除条件；

19.test表中，有id、name、shouji列。把id列设置为主键，在Name字段上创建普通索引

alter table test add primary key(id);
alter table test add unique index idx_id(id);
alter table test add index idx_na(name);

20. 在手机字段上对前8个字符创建普通索引

alter table test add index idx_sh(shouji(8));

21.查看创建的索引及索引类型等信息

show index from 表名;


show index from 表名\G


desc 表名;

22.删除Name，shouji列的索引

drop idenx 索引名 from table 表名;


alter table 表名 dorp index 索引名;

23.对Name列的前6个字符以及手机列的前8个字符组建联合索引

alter table 表名 add index idx_na_sh(Name(6),shouji(8));

24. 将shouji列索引替换为唯一键索

alter table 表名 drop index 索引名;
alter table 表名 index unique 索引名;

25.如何查看world数据库下city表中population列的重复值情况

select count(population) from world.city;
select count(distinct(population)) from world.city;

26. 请列出explain命令中type中多种类型

27.Select查询语句加强练习

统计世界上每个国家的总人口数.
select name,sum(population) from country group by name;

统计中国各个省的总人口数量
 select district,sum(population) from world.city where countrycode='CHN' group by district;

统计世界上每个国家的城市数量
select country.name,count(city.id)
from country join city
on country.code=city.countrycode
group by country.name;

统计中国每个省的总人口数，将总人口数小于100w进行从高到低排序显示
select district,sum(population) 
from city where countrycode='CHN' 
group by district having sum(population)<1000000 
order by sum(population) desc;

28.生成整个数据库下的所有表的单独备份语句

SELECT CONCAT("mysqldump -uroot -p123  ",table_schema," ",table_name," 
>/tmp/",table_schema,"_",table_name,".sql")  
FROM information_schema.tables 
WHERE table_schema NOT IN('sys','performance','information_schema')
INTO OUTFILE '/tmp/bak.sh';

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,sum(score.score),count(cno)
from student join score
on student.sno=score.sno
group by student.sno;

3. 查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分

select course.cno,MAX(score.score) as '最高分',MIN(score.score) as '最低分' 
from
course join score 
on course.cno=score.cno group by course.cname,course.cno;

4. 统计各位老师,所教课程的及格率

select teacher.tname,count(case when ifnull(score.score,0)>=60 then 1 end)/count(*)*100  as '及格率%'
from teacher
join course
on course.tno=teacher.tno
join score
on score.cno=course.cno
group by teacher.tno;

select teacher.tname,course.cname,
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

4. 统计各位老师,所教课程的及格率

select teacher.tname,count(case when ifnull(sc.score,0)>=60 then 1 end)/count(*)*100  as '及格率%'
from teacher
join course
on course.tno=teacher.tno
join sc
on sc.cno=course.cno
group by teacher.tno;

select teacher.tname,count(sc.score>=60 ),count(student.sno),count(sc.score>60 or null)/count(student.sno)
from teacher
join course 
on course.tno=teacher.tno
join sc
on course.cno=sc.cno
join student
on student.sno=sc.sno
group by teacher.tno;

select teacher.tname,sum(sc.score>=60),count(student.sno),count(sc.score>60 or null)/count(student.sno)
from teacher
join course 
on course.tno=teacher.tno
join sc
on course.cno=sc.cno
join student
on student.sno=sc.sno
group by teacher.tno;

5. 查询每门课程被选修的学生数

select course.cname,count(student.sname)
from course join score
on course.cno=score.cno
join student on score.sno=student.sno
group by course.cname;

6. 查询出只选修了一门课程的全部学生的学号和姓名

select student.sno,student.sname
from student join score
on student.sno=score.sno
join course on score.cno=course.cno
group by student.sno
having count(course.cname)=1;

7. 查询选修课程门数超过1门的学生信息

select student.sno,student.sname
from student join score
on student.sno=score.sno
join course on score.cno=course.cno
group by student.sno
having count(course.cname)>1;

8. 统计每门课程:优秀(85分以上),良好(70-85),一般(60-70),不及格(小于60)的学生列表

优秀
select course.cname,AVG(score.score),group_concat(student.sname)
from course join score
on course.cno=score.cno
join student on score.sno=student.sno
group by course.cname
having AVG(score.score)>85;

良好
select course.cname,AVG(score.score),group_concat(student.sname)
from course join score
on course.cno=score.cno
join student on score.sno=student.sno
group by course.cname
having AVG(score.score)>70 and AVG(score.score)<85;

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;