mysql经典面试题

数据库面试题

一、简答题

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

5.7.20     2017.9.13

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

DBMS 数据库管理系统
RD （关系型数据库） Oracle,mysql,PG,MSSQL
NoSQL (非关系型数据库) MonnoDB，Redis,ES
NEWSQL (分布式)      TiDB，Spanner，AlisQL，OB，PolarDB。

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

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

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

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

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

目前业界的MySQL主流分支版本有Oracle官方版本的MySQL、Percona Server、MariaDB。接下来看一下各个分支的特点。
在OLTP只读模式下，MySQL 5.7比MySQL 5.6快近3倍的速度，MySQL 5.6比MySQL 5.5快近1.5倍的速度，而且5.7有将近100万的QPS（每秒的查询量）
Percona Server是MySQL重要的分支之一，它基于InnoDB存储引擎的基础上，提升了性能和易管理性，最后形成了增强版的XtraDB引擎，可以用来更好地发挥服务器硬件上的性能。所以Percona Server也可以称为增强的MySQL与开源的插件（plugin）的结合。由于官方版本的MySQL在一些特性的使用上有一定的局限性，需要收费。所以Percona Server就有了一定的市场占有比例，也比较受大家的欢迎。像一些常用的工具包xtrabackup、percona-toolkit等，在生产环境中是DBA的必备武器。还有像XtraDB-Cluster这种支持多点写入的强同步高可用集群架构，真正实现实时同步的过程，解决了MySQL主从复制之间经常出现并让人头疼的延迟问题。而且Percona还收购了TokuDB公司，TokuDB存储引擎非常优秀，淘宝网、阿里云上大量在使用这款存储引擎。它支持数据压缩，支持hot scheme modification，它的高扩展性和优秀的查询插入性能都是我们喜欢它的地方。

Mariadb是由MySQL创始人Monty创建的，是一款高度兼容的MySQL产品，主要由开源社区维护，采用GPL授权许可。Oracle把MySQL收购之后，为避免MySQL在开源粒度上的下降，MariaDB由此而生。它不仅仅是MySQL的一个替代品，还创新与提高了MySQL原有的技术。既包含了Percona的XtraDB存储引擎，还包含TokuDB存储引擎、Spider水平分片存储引擎等多种存储引擎，并且还有一些复制功能上的新特性，比如基于表的并行复制、Multi-source Replication多源复制、Galera Cluster集群。还有比较有意思的一点就是MariaDB有一套Java的管理系统，可以通过投票机制来决定哪些特性和参数是我们需要的。

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下，然后即可通过该方式启动
3、service mysqld ‘start|stop|restart’
4、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、使用特殊的参数启动数据库进入救援模式 mysql_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树     聚簇索引存放数据行的全部信息

辅助索引是基于列生产的                   
聚簇索引一般是根据主键列自动生产的，如果没有主键列，会选择唯一值多的列作为聚簇索引。

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

阐述以下语句可能存在的问题，并提出合理解决方案

explain select * from city where countrycode='CHN' order by population;

image.png

简述出现以下结果的可能原因

image.png

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.创建管理员用户：wangyongqiang能通过10.0.0.0/24网段任意地址登录管理MySQL

create user ceshi@'10.0.0.0/255.255.254.0' indentified 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' indentified 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.请写出使用ceshi用户远程登录MySQL10.0.0.51的具体语句

mysql -uceshi -p -h10.0.0.51

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

show create database 库名；
默认字符集为Latin1

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

create database ceshi character set gbk 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' indentified 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 index 索引名from table 表名；
alter table 表名 drop 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中多种类型

type：这是重要的列，显示如何执行sql语句去表里查找记录的。从最好到最差的类型为:

(1)const  (2)eq_reg （3）ref  （4）range  (5）index  （6）all

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 -p 123456","table_schema,"",table_name,")
>/tmp/",table_schema,"_""table_name,"".sql"

from information_schema.tables
WHERE table_schema NOT IN('sys','performace','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,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

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;

posted @ 2019-12-27 21:29 老王教你学Linux 阅读(322) 评论(0) 编辑收藏举报

刷新页面返回顶部

老王教你学Linux

吃水不忘挖井人，走路不忘领路者！！！ 文档配视频效果更佳哦：https://edu.51cto.com/lecturer/14390454.html

mysql经典面试题

数据库面试题

一、简答题

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

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

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

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

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

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

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

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

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

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

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

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

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

阐述以下语句可能存在的问题，并提出合理解决方案

简述出现以下结果的可能原因

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

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

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

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

18.InnoDB存储引擎核心特性

二、操作题

1.创建管理员用户：wangyongqiang能通过10.0.0.0/24网段任意地址登录管理MySQL

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

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

4.请写出使用ceshi用户远程登录MySQL10.0.0.51的具体语句

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

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

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

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

9. 收回oldboy用户的drop权限

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

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

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

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

14.查询名字为oldboy的记录

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

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

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

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

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

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

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

22.删除Name，shouji列的索引

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

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

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

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

27.Select查询语句加强练习

统计世界上每个国家的总人口数.

统计中国各个省的总人口数量

统计世界上每个国家的城市数量

统计中国每个省的总人口数，将总人口数小于100w进行从高到低排序显示

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

29. SQL综合练习

1. 查询平均成绩大于60分的同学的学号和平均成绩；

2. 查询所有同学的学号、姓名、选课数、总成绩；

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

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

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

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

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

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

9. 查询平均成绩大于85的所有学生的学号、姓名和平均成绩

公告

吃水不忘挖井人，走路不忘领路者！！！文档配视频效果更佳哦：https://edu.51cto.com/lecturer/14390454.html