一、简答题
1.简述你们公司使用的MySQL版本，并说明具体小版本及GA时间？
5.6.38
5.7.20
2017/09/13
2.请介绍你熟悉的数据库关系系统的种类和代表产品名称？
关系型数据库：MySQL、Oracle、SQL Server、PG
非关系型数据库：Redis、MongoDB、Memcache、ES
NewSQL：PolarDB、TiDB、Spanner、AliSQL
3.请简述MySQL二进制安装重点步骤？
下载，上传，解压
创建工作目录
创建管理用户并给目录授权
设置环境变量
卸载MariaDB
安装libiao-level依赖
初始化数据库
编辑配置文件
编辑配置systemd开机管理服务文件
4.怎么确认数据库启动成功了？
ps -ef|grep mysqld
netstat -lntup|grep 3306
ss -lnp|grep 3306
lsof -i:3306
5.简述你了解的MySQL分支版本情况？
Oracle的MySQL
RedHat：MariaDB
Percona: PerconaDB
6.请简述mysqld的程序结构（1条SQL语句的执行过程）
#连接层：
提供连接协议：TCP/IP、Socket
验证用户名，密码，匹配授权表信息
开启连接线程
#SQL层：
接收上层的SQL语句
验证语法，语义，权限
解析器：解析SQL语句，生成执行计划
优化器：根据算法选择最优的执行计划
执行器：按照最优的执行计划，执行SQL语句，得到获取数据的方法
提供查询缓存，默认是不开启的，一般会用Redis代替
记录操作日志binlog，默认不开启
#存储引擎层
根据SQL层获取数据的方法，到磁盘中拿到数据，返回SQL层，结构化成表结构，由连接线程返回给用户
7.请简述你了解的MySQL的启动方式
mysql.server
systemctl
mysql_safe
mysqld
8.简述MySQL配置文件默认读取顺序
mysql --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
10.忘记管理员root的密码处理过程，请对参数详细说明
1.关闭mysql
2.维护性启动mysql
mysql_safe --skip-grant-tables --skip-networking &
3.登录修改密码
alter user root@'localhost' identified by '密码'；
4.关闭数据库，正常启动验证
--skip-grant-tables 跳过授权表验证
--skip-networking 跳过TCP/IP连接
11.请列举SQL语句的常用种类
DDL 数据库定义语言
DML 数据库操作语言
DCL 数据库控制语言
DQL 数据库查询语言
12.请说明聚集索引和辅助索引的区别
聚集索引只能有一个，非空，唯一，一般是主键
辅助索引可以有多个，细分有联合索引，唯一索引，
一般取唯一值多的列
聚集索引的叶子存的是真实的数据页，将ID值取出形成枝节点和根节点
辅助索引叶子节点存的只是列的键值
13.请简述以下语句执行计划可能存在的问题
阐述以下语句可能存在的问题，并提出合理解决方案
explain select * from city where countrycode='CHN' order by population;
image.png
使用的filesort文件排序
给where条件后的列和order by后的列建立联合索引
简述出现以下结果的可能原因
image.png
telnum列出现隐式转换导致索引失效
telnum列是char类型，如果输入int类型，内部会进行字符串的转换，导致不走索引
14.请简述，影响索引树高度的因素？
数据量大---------->分库分表
索引列的列值过长---------->前缀索引
数据类型：变长长度的字符串用了char类型----->使用varchar
15.请说明数据库启动失败的处理思路？
查看错误日志
检查配置文件参数
检查socket文件
16.MySQL索引的种类都有哪些？
聚集索引
辅助索引：联合索引，唯一索引
17.你了解的MySQL存储引擎种类有哪些？
InnoDB
MyISAM
Memory
ARCHIVE
18.InnoDB存储引擎核心特性
1.支持事务
2.行级锁
3.支持热备份
4.MVCC多版本并发控制
5.ACSR自动的故障恢复
6.Replication: Group Commit , GTID (Global Transaction ID) ,多线程(Multi-Threads-SQL )
二、操作题
1.创建管理员用户：oldboy能通过10.0.0.0/24网段任意地址登录管理MySQL
grant all on *.* to oldboy@'10.0.0.%' identified by "密码"；
2.创建应用用户：wordpress能通过172.16.1.0/24网段任意地址登录操作wordpress库下的所有表
grant select,update,insert,delete on wordpress.* to wordpress@'172.16.1.%' identified by '密码'；
3.请写出/etc/my.cnf的基础配置信息
[mysqld]
user=mysql
basedir=/application/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
port=3306
log_error=/data/msyql/db01.err
log_bin=/data/mysql/mysql-bin
[mysql]
socket=/tmp/mysql.sock
4.请写出使用oldboy用户远程登录MySQL的具体语句
mysql -uoldboy -p -h10.0.0.51 -P 3306
5.查看当前数据库的字符集
show create database 数据库名;
6.创建GBK字符集的数据库oldboy，并查看已建库完整语句
create database oldboy charset='GBK';
7.请分别介绍 NOT NULL default auto_increament 的作用
not null 不允许为空
default 默认值
auto_increment 自增
8.创建用户oldboy，使之可以管理数据库oldboy
grant all on oldboy.* to oldboy@'localhost' identified by '密码'；
9.收回oldboy用户的drop权限
revoke drop on oldboy.* from oldboy@'localhost';
10.查看创建的用户oldboy拥有哪些权限
show grants for oldboy@'localhost';
11.查看建表结构及表结构的SQL语句
desc 表名；
show create table 表名；
12.插入一条数据“1,oldboy”
insert into oldboy values(1,'oldboy');
13.再批量插入2行数据“2,老男孩”，“3,oldboyedu”
insert into oldboy values(2,'老男孩')，(3,'oldboyedu');
14.查询名字为oldboy的记录
seletc * from oldboy where name='oldboy';
15.查看数据库中所有引擎的类型
show engines;
16.查看数据库关于日志的参数配置
show variables like '%log%';
17.查看handler_read_key当前的状态信息
show status like 'handler_red_key';
18. 列出删除表中数据的方式
delete from table; 逻辑删除
truncate table；物理删除
19.test表中，有id、name、shouji列。把id列设置为主键，在Name字段上创建普通索引
create table test(
id int(4) primary key not null auto_increment,
name varchar(32) not null;
shouji char(11) not null
)engine=innodb charset=utf8mb4;
alter table test add index idx_name(name);
20.在手机字段上对前8个字符创建普通索引
alter table test add index idx_sj(shouji(8));
21.查看创建的索引及索引类型等信息
desc test;
show index from test;
22.删除name，shouji列的索引
alter table test drop index id_name;
alter table test drop index id_sj;
23.对Name列的前6个字符以及手机列的前8个字符组建联合索引
alter table test add index idx_nm_sj(name(6),shouji(8));
24.将shouji列索引替换为唯一键索
alter table test add unique index idx_sj(shouji);
25.如何查看world数据库下city表中population列的重复值情况
select population,count(id)
from world.city
group by population;
26.请列出explain命令中type中多种类型
ALL
index
range
ref
eq_ref
const,system
null
27.Select查询语句加强练习
统计世界上每个国家的总人口数.
select country.name,sum(city.population)
from city
join country
on city.countrycode=country.code
group by 'city.country.code';
统计中国各个省的总人口数量
SELECT District,SUM(Population)
FROM city
WHERE CountryCode='CHN'
GROUP BY District;
统计世界上每个国家的城市数量
SELECT country.`Name`,COUNT(id)
FROM city
JOIN country
ON city.`CountryCode`=country.`Code`
GROUP BY CountryCode;
统计中国每个省的总人口数，将总人口数小于100w进行从高到低排序显示
SELECT District,SUM(Population)
FROM city
WHERE CountryCode='CHN'
GROUP BY District
HAVING SUM(Population)<1000000
ORDER BY SUM(Population) DESC;
生成整个数据库下的所有表的单独备份语句
SELECT CONCAT("mysqldump -uroot -p123 ",table_schema," ",table_name," >/tmp/",table_schema,"_",table_name,".sql")
FROM information_schema.`TABLES`
WHERE table_schema NOT IN('information_schema','performance_schema','sys','mysql')
INTO OUTFILE '/server/scripts/bak.sh';
29.SQL综合练习
1. 查询平均成绩大于60分的同学的学号和平均成绩；
SELECT score.`sno`,AVG(score)
FROM score
WHERE score>60
GROUP BY score.`sno`;
2. 查询所有同学的学号、姓名、选课数、总成绩；
SELECT student.`sno`,student.`sname`,COUNT(score.`cno`),SUM(score.`score`)
FROM student
JOIN score
ON student.`sno`=score.`sno`
GROUP BY student.`sno`;
3.查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
SELECT score.`cno` ,MAX(score.`score`),MIN(score.`score`)
FROM score
GROUP BY score.`cno`;
4. 统计各位老师,所教课程的及格率
SELECT teacher.`tname`,course.`cname`,COUNT(CASE WHEN score.`score`>=60 THEN 1 END)/COUNT(*) AS 及格率
FROM score
JOIN course
ON score.`cno`=course.`cno`
JOIN teacher
ON teacher.`tno`=course.`tno`
GROUP BY score.`cno`;
5. 查询每门课程被选修的学生数
SELECT course.`cname`,COUNT(score.`sno`)
FROM course
JOIN score
ON course.`cno`=score.`cno`
GROUP BY score.`cno`;
6.查询出只选修了一门课程的全部学生的学号和姓名
SELECT score.`sno`,student.`sname`
FROM student
JOIN score
ON student.`sno`=score.`sno`
GROUP BY score.`sno`
HAVING COUNT(score.`cno`)=1;
7. 查询选修课程门数超过1门的学生信息
SELECT student.`sage`,student.`sname`,student.`sname`,student.`ssex`
FROM student
JOIN score
ON student.`sno`=score.`sno`
GROUP BY score.`sno`
HAVING COUNT(score.`cno`)>1;
8. 统计每门课程:优秀(85分以上),良好(70-85),一般(60-70),不及格(小于60)的学生列表
SELECT GROUP_CONCAT(CASE WHEN score.`score`>=85 THEN student.`sname` END) AS '优秀',
GROUP_CONCAT(CASE WHEN score.`score` BETWEEN 70 AND 85 THEN student.`sname` END) AS '良好',
GROUP_CONCAT(CASE WHEN score.`score` BETWEEN 60 AND 70 THEN student.`sname` END) AS '一般',
GROUP_CONCAT(CASE WHEN score.`score` <60 THEN student.`sname` END) AS '不及格'
FROM score
JOIN student
ON score.`sno`=student.`sno`;
9. 查询平均成绩大于85的所有学生的学号、姓名和平均成绩
SELECT student.`sno`,student.`sname`,AVG(score.`score`) AS a
FROM student
JOIN score
ON student.`sno`=score.`sno`
GROUP BY student.`sno`
HAVING a>85;
