2019-06-17 MySQL 测试题(一)
1. 简述你们公司使用的 MySQL 版本, 并说明具体小版本及 GA 时间?
- oracle MySQL 5.6.38
- oracle MySQL 5.7.20
GA 时间: 2017-9-13
2. 请介绍你熟悉的数据库的种类和代表产品名称?
- ODBMS:oracle MySQL mssql PostgreSQL
- NoSQL:Memcached MongoDB Redis
3. 请简述 MySQL 二进制安装重点步骤?
下载解压
创建 MySQL 用户
创建数据文件夹并设置权限
设置环境变量并 source:echo 'export PATH=$PATH:/usr/local/mysql/bin'>>/etc/profile;source /etc/profile
初始化数据库: mysqld --initialize-insecure --user=MySQL --datadir=/data/MySQL --basedir=/usr/bin/MySQL
生成配置文件:/etc/my.cnf
启动服务
4. 怎么确认数据库启动成功了?
进程: ps -ef|grep mysqld
端口: netstat -lnutp|grep 3306;ss -lnutp|grep 3306
直接 MySQL 登陆测试
5. 简述你了解的 MySQL 分支版本情况?
oracle MySQL: 最新版本 8.0 稳定版本 5.7
percona MySQL: 最新版本 8.0 稳定版本 5.7
mariadb: 最新版本 10.4 稳定版本 10.3
6. 请简述 mysqld 的程序结构(1 条 SQL 语句的执行过程)
连接层:
提供连接协议, 验证, 专用连接线程
SQL 层
语法, 语义, 权限, 解析, 优化, 执行, 查询缓存, 日志记录
存储引擎层
相当于 Linux 文件系统, 例如: InnoDB 提供了事务, CSR, 热备, MVCC, 行级锁等
7. 请简述你了解的 MySQL 的启动方式
- sys-v:MySQL.server--->mysqld_safe---->mysqld
- systemd: mysqld --defaults-file
- mysqld_safe
- mysqld
8. 简述 MySQL 配置文件默认读取顺序
- [[email protected] ~]# 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 配置文件并在后台启动 MySQL
10. 忘记管理员 root 的密码处理过程, 请对参数详细说明
[[email protected] ~]# mysqld_safe --skip-grant-tables --skip-networking
--skip-grant-tables: 关闭连接层的验证功能
--skip-networking: 关闭 TCP/IP 协议
11. 请列举 SQL 语句的常用种类
DDL DCL DML DQL
12. 请说明聚集索引和辅助索引的区别
辅助索引, 叶子节点只存储, 有序的某个列的所有值
聚集索引, 存储的是整行数据
辅助索引一般是配合聚集索引使用, 通过辅助所用找到主键值, 然后通过聚集索引找到数据行, 减少了回表查询带来的硬盘 IO
13. 请简述以下语句执行计划可能存在的问题
- explain select * from city where countrycode='CHN' order by population;
- ![](https://s1.51cto.com/images/blog/201906/17/ddacb4adefc3f67f6b766453b41a925b.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=)
- image.PNG
结果: type--all;Extra--filesort;
1. countrycode 没有走索引, 有可能是没建立索引, 或者是索引失效
2. 出现了 filesort 文件排序, orderby 条件也没走索引
建议:
1. 如果没有索引导致, 建立联合索引(countrycode,population)
2. 如果是索引失效, 删除索引重建
简述出现以下结果的可能原因
![](https://s1.51cto.com/images/blog/201906/17/3b002b14ce2948738950877c85e2bc8e.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=)
应该是 telnum 列是字符串类型, 不加引号导致出现了隐式转换, 需要进一步判断数据类型, 建议加上单引号.
请简述, 影响索引树高度的因素?
数据量级: 分表分库分布式
索引键值太长: 前缀索引
数据类型: char varchar 选择, enum, 选择合理的数据类型
14. 请简述, 影响索引树高度的因素?
数据量级: 分表分库分布式
索引键值太长: 前缀索引
数据类型: char varchar 选择, enum, 选择合理的数据类型
15. 请说明数据库启动失败的处理思路?
先看服务有没有启动;
再看配置文件中 sock 文件设置, ls 查看有无文件
再然后看日志;
如果没有日志, 使用 mysqld 直接测试启动
16MySQL 索引的种类都有哪些?
B 树 HASH R 树
17 你了解的 MySQL 存储引擎种类有哪些?
InnoDB,MyIAM,CSV,TokuDB, Percona-XtraDB
18.InnoDB 存储引擎核心特性
事务, CSR,MVCC, 行级锁, 热备, 外键
二, 操作题
1. 创建管理员用户: oldboy 能通过 10.0.0.0/24 网段任意地址登录管理 MySQL
- create user [email protected]'10.0.0.%/255.255.255.0' identified by '123';
- SELECT DISTINCT user,host FROM MySQL.user;
- user | host |
- +--------+------------------------+
- | oldboy | 10.0.0.%/255.255.255.0
- ...
2. 创建应用用户: WordPress 能通过 172.16.1.0/24 网段任意地址登录操作 WordPress 库下的所有表
- create user [email protected]'172.16.1.%/255.255.255.0' identified by '123';
- grant all on WordPress.* to [email protected]'172.16.1.%/255.255.255.0';
- grant all select update insert delete WordPress.* to [email protected]'172.16.1.%/255.255.255.0';
3. 请写出 / etc/my.cnf 的基础配置信息
- [mysqld]
- user=MySQL
- basedir=/usr/local/MySQL
- datadir=/data/MySQL
- socket=/tmp/MySQL.sock
- server_id=1
- port=3306
- [MySQL]
- socket=/tmp/MySQL.sock
4. 请写出使用 oldboy 用户远程登录 MySQL 的具体语句
MySQL -uoldboy -p -h 10.0.0.30 -P3306(端口可省略)
5. 查看当前数据库的字符集
show 语句
show charset;
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 o[email protected]'10.0.0.%' identified by '123';
9. 收回 oldboy 用户的 drop 权限
revoke drop on oldboy.* from [email protected]'10.0.0.%';
10. 查看创建的用户 oldboy 拥有哪些权限
show grants for [email protected]'10.0.0.%';
11. 查看表结构及建表的 SQL 语句
- desc stu;
- show create table stu;
12. 插入一条数据 "1,oldboy"
insert into stu values(1,'oldboy');
13. 再批量插入 2 行数据 "2, 老男孩","3,oldboyedu"
- insert into stu values(2,'老男孩');
- insert into stu values(3,'oldboyedu');
14. 查询名字为 oldboy 的记录
select * from t1 where name='oldboy';
查看数据库中所有引擎的类型
show engines;
16. 查看数据库关于日志的参数配置
show variables like '%log%';
17. 查看 handler_read_key 当前的状态信息
show status like 'handler_read_key';
18.delete 和 truncate 区别
delete : 逻辑逐条删除数据行
trucate: 物理删除表段中的所有数据页
19.test 表中, 有 id,name,shouji 列. 把 id 列设置为主键, 在 Name 字段上创建普通索引
- create table test (
- id int not null primary key comment '学号',
- name varchar(64) not null comment '姓名',
- telnum char(11) not null comment '手机号'
- )engine InnoDB Charset Utf8 comment '学生表';
- alter table test add index idx_name(name);
20. 在手机字段上对前 8 个字符创建普通索引
alter table add index idx_tel(telnum(8));
21. 查看创建的索引及索引类型等信息
- desc test;
- show index from test
22. 删除 Name,shouji 列的索引
- alter table test drop index idx_name ;
- alter table test drop index idx_tel;
23. 对 Name 列的前 6 个字符以及手机列的前 8 个字符组建联合索引
alter table test add index idx_n_t(name(6),telnum(8));
24. 将 shouji 列索引替换为唯一键索
alter table test add unique index idx_tel(telnum);
25. 如何查看 world 数据库下 city 表中 population 列的重复值情况
- select name,population,count(id) from world.city group by population having count(id)>1
- order by count(id) desc ;
26. 请列出 explain 命令中 type 中多种类型
ALL,INDEX,RANGE,REF,EQ_REF,SYSTEM(const),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(name)
- from city
- 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;
28. 生成整个数据库下的所有表的单独备份语句
- SELECT CONCAT("mysqldump -uroot -p123",table_schema,"",table_name,">/bak/",table_schema,"_",table_name,".sql")
- FROM information_schema.TABLES
29.SQL 综合练习
1). 查询平均成绩大于 60 分的同学的学号和平均成绩;
select student_id,avg(num) from score group by student_id having avg(num)> 60
2). 查询所有同学的学号, 姓名, 选课数, 总成绩;
- select score.student_id,sum(score.num),count(score.student_id),student.sname
- from score left join student
- on score.student_id = student.sid
- group by score.student_id
3). 查询各科成绩最高和最低的分: 以如下形式显示: 课程 ID, 最高分, 最低分
select course_id, max(num) as max_num, min(num) as min_num from score group by course_id;
4). 统计各位老师, 所教课程的及格率(case)
count(case when isfull(socre,0)>=60 then 1 end)/count(*)
group by 学科
5). 查询每门课程被选修的学生数
select course_id, count(student_id) from score group by course_id;
6). 查询出只选修了一门课程的全部学生的学号和姓名
- select student.sid, student.sname, count(course_id)
- from score left join student
- on score.student_id = student.sid
- group by course_id having count(course_id) = 1
7). 查询选修课程门数超过 1 门的学生信息
select * from score group by student_id having count(student_id)> 1
8). 统计每门课程: 优秀 (85 分以上), 良好(70-85), 一般(60-70), 不及格(小于 60) 的学生列表(case)
SELECT t1.c#,t2.cname,
SUM(CASE WHEN t1.score> 85 AND t1.score <= 100 THEN 1 ELSE 0 END) AS 优秀人数,
SUM(CASE WHEN t1.score> 70 AND t1.score <= 85 THEN 1 ELSE 0 END) AS 良好人数,
SUM(CASE WHEN t1.score> 60 AND t1.score <= 70 THEN 1 ELSE 0 END) AS 一般人数,
SUM(CASE WHEN t1.score <= 60 THEN 1 ELSE 0 END) AS 刚及格人数
- FROM sc t1
- INNER JOIN course t2
- ON t1.c# = t2.c#
- GROUP BY t1.c#,t2.cname
9). 查询平均成绩大于 85 的所有学生的学号, 姓名和平均成绩
- select student_id,sname, avg(if(isnull(score,0) ,>85))
- from score left join student
- on score.student_id = student.sid
- group by student_id;
- SELECT student_id, sname,AVG(score.score) AS avg_score
- from score left join student
- ON score.student_id = student.sid
- GROUP BY student_id
- HAVING AVG(score.score)> 85
2019-06-17 MySQL 测试题(一)
来源: http://www.bubuko.com/infodetail-3095360.html