<article class="_2rhmJa">
一、简答题
1.简述你们公司使用的MySQL版本，并说明具体小版本及GA时间？
5.6.38
5.7.20
2017-9-13
2.请介绍你熟悉的数据库的种类和代表产品名称？
NoSQL:Redis Mongodb memecache ES
RDBMS:Oracle MySQL MSSQL PG
newSQL:RDBtip
3.请简述MySQL二进制安装重点步骤？
1.下载,上传,解压
2\. 创建用户
3\. 创建相关目录并授权
4\. 设置环境变量
5\. 初始化数据
6\. 配置文件
7\. 配置启动脚本
4.怎么确认数据库启动成功了？
ps -ef |grep mysqld
netstat -lnp|grep 3306
ss -lnp|grep 3306
mysql 登陆测试
5.简述你了解的MySQL分支版本情况？
1.Oracle---->MySQL
2.MariaDB
3.Percona
6.请简述mysqld的程序结构（1条SQL语句的执行过程）
连接层:
提供连接协议,验证,专用连接线程
SQL层
语法,语义,权限,解析,优化,执行,查询缓存,日志记录
存储引擎层
相当于Linux文件系统,例如:InnoDB提供了事务,CSR,热备,MVCC,行级锁等
7.请简述你了解的MySQL的启动方式
sys-v:mysql.server--->mysqld_safe---->mysqld
systemd: mysqld --defaults-file
systemctl start mysqld
/application/mysql/support-file/mysql.service start
mysqld_safe
mysqld
8.简述MySQL配置文件默认读取顺序
[root@db02 ~]# mysqld --help --verbose|grep "my.cnf"
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
my.cnf, $MYSQL_TCP_PORT, /etc/services, built-in default
9.mysqld_safe --default-files=/opt/my.cnf & 是什么作用？
自定义配置文件,后台启动mysql.
10.忘记管理员root的密码处理过程，请对参数详细说明
--skip-grant-tables 关闭连接层的验证功能
--skip-netwoking 关闭TCPIP协议
11.请列举SQL语句的常用种类
DDL 数据定义语言
DML 数据操作语言
DCL 数据控制语言
DQL 数据查询语言
12.请说明聚集索引和辅助索引的区别
辅助索引,叶子节点只存储,有序的某个列的所有值
聚集索引,存储的是整行数据
辅助索引一般是配合聚集索引使用,通过辅助索引找到主键值,然后通过聚集索引找到数据行,减少了回表查询带来的随机IO。
13.请简述以下语句执行计划可能存在的问题
阐述以下语句可能存在的问题，并提出合理解决方案
explain select * from city where countrycode='CHN' order by population;
image
1. countrycode没有走索引,有可能是没建立索引,或者是索引失效。
2. 出现了filesort文件排序, orderby条件也没走索引。
建议:
1. 如果没有索引导致,建立联合索引(countrycode,population)。
2. 如果是索引失效,删除索引重建。
简述出现以下结果的可能原因
image
怀疑是 telnum列是字符串类型,可能出现了隐式转换,需要进一步判断数据类型.
14. 请简述，影响索引树高度的因素？
1.数据量级:分表分库分布式
2.索引键值太长:前缀索引
3.数据类型:char varchar选择,enum,选择合理的数据类型
15.请说明数据库启动失败的处理思路？
有日志,看日志,没日志,使用mysqld直接测试启动，看控制台打印的错误日志。
16. MySQL索引的种类都有哪些？
1.B树
2.HASH索引
3.R树
4.full text
5.GIS
17. 你了解的MySQL存储引擎种类有哪些？
InnoDB，MyIAM，CSV，MEMORY，MERGE，EXAMPLE，ARCHIVE，BLACKHOLE，FEDERATED。
18.InnoDB存储引擎核心特性
支持事务,CSR,MVCC,行级锁,热备,外键等。
二、操作题
1.创建管理员用户：zhangwb能通过10.0.0.0/24网段任意地址登录管理MySQL。
grant all on *.* to zhangwb@'10.0.0.%' identified by '123456';
2.创建应用用户：wordpress能通过172.16.1.0/24网段任意地址登录操作wordpress库下的所有表。
grant select,update,delete,insert on wordpress.* to wordpress@'172.16.1.%' identified by '123456';
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.请写出使用zhangwb用户远程登录MySQL的具体语句
mysql -uzhangwb -p -h 10.0.0.51 -P3306
5.查看当前数据库的字符集
show charset;
6. 创建GBK字符集的数据库zhangwb，并查看已建库完整语句
create database zhangwb charset gbk engine innodb;
show create database zhangwb;
7. 请分别介绍 NOT NULL default auto_increament 的作用
Not NULL :非空
default: 默认值
auto_increament:自动增长
8. 创建用户zhangwb，使之可以管理数据库zhangwb
grant all on zhangwb.* to zhangwb@'10.0.0.%' identified by '123456';
9. 收回zhangwb用户的drop权限
revoke drop on zhangwb.* from zhangwb@'10.0.0.%';
or
revoke drop on *.* from zhangwb@'10.0.0.%';
10. 查看创建的用户zhangwb拥有哪些权限
show grants for zhangwb@'10.0.0.%';
11. 查看表结构及建表的SQL语句
desc stu;
show create table stu;
12. 插入一条数据“1,zhangwb”
insert into t1 values(1,'zhangwb');
13.再批量插入2行数据“2,zhangwb1”，“3,zhangwb2”
insert into t1 values(2,'zhangwb1'),(3,'zhangwb2');
14.查询名字为zhangwb的记录
select * from t1 where name='zhangwb';
15. 查看数据库中所有引擎的类型
show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| CSV | YES | CSV storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
16.查看数据库关于日志的参数配置
show variables like '%log%';
+--------------------------------------------+-----------------------------------------------+
| Variable_name | Value |
+--------------------------------------------+-----------------------------------------------+
| back_log | 80 |
| binlog_cache_size | 32768 |
| binlog_checksum | CRC32 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_error_action | ABORT_SERVER |
| binlog_format | ROW |
| binlog_group_commit_sync_delay | 0 |
| binlog_group_commit_sync_no_delay_count | 0 |
| binlog_gtid_simple_recovery | ON |
| binlog_max_flush_queue_time | 0 |
| binlog_order_commits | ON |
| binlog_row_image | FULL |
| binlog_rows_query_log_events | OFF |
| binlog_stmt_cache_size | 32768 |
| binlog_transaction_dependency_history_size | 25000 |
| binlog_transaction_dependency_tracking | COMMIT_ORDER |
| expire_logs_days | 0 |
| general_log | OFF |
| general_log_file | /application/mysql/data/db02.log |
| innodb_api_enable_binlog | OFF |
| innodb_flush_log_at_timeout | 1 |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_buffer_size | 16777216 |
| innodb_log_checksums | ON |
| innodb_log_compressed_pages | ON |
| innodb_log_file_size | 50331648 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_log_write_ahead_size | 8192 |
| innodb_max_undo_log_size | 1073741824 |
| innodb_online_alter_log_max_size | 134217728 |
| innodb_undo_log_truncate | OFF |
| innodb_undo_logs | 128 |
| log_bin | OFF |
| log_bin_basename | |
| log_bin_index | |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| log_builtin_as_identified_by_password | OFF |
| log_error | /application/mysql/data/zhangweibin_mysql.err |
| log_error_verbosity | 3 |
| log_output | FILE |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| log_statements_unsafe_for_binlog | ON |
| log_syslog | OFF |
| log_syslog_facility | daemon |
| log_syslog_include_pid | ON |
| log_syslog_tag | |
| log_throttle_queries_not_using_indexes | 0 |
| log_timestamps | UTC |
| log_warnings | 2 |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 1073741824 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| max_relay_log_size | 0 |
| relay_log | |
| relay_log_basename | /application/mysql/data/db02-relay-bin |
| relay_log_index | /application/mysql/data/db02-relay-bin.index |
| relay_log_info_file | relay-log.info |
| relay_log_info_repository | FILE |
| relay_log_purge | ON |
| relay_log_recovery | OFF |
| relay_log_space_limit | 0 |
| slow_query_log | OFF |
| slow_query_log_file | /application/mysql/data/db02-slow.log |
| sql_log_bin | ON |
| sql_log_off | OFF |
| sync_binlog | 1 |
| sync_relay_log | 10000 |
| sync_relay_log_info | 10000 |
+--------------------------------------------+-----------------------------------------------+
17.查看handler_read_key当前的状态信息
show status like 'handler_read_key';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| Handler_read_key | 0 |
+------------------+-------+
18. delete和truncate区别
delete :逻辑逐条删除数据行
trucate:物理删除表段中的所有数据页
19.test表中，有id、name、shouji列。把id列设置为主键，在Name字段上创建普通索引
create table test (
id int not null primary key unique 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);
or
alter table test add unique key idx_tel(telnum);
25.如何查看world数据库下city表中population列的重复值情况
select 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 code,sum(population) from country group by code;
统计中国各个省的总人口数量
select district,sum(population) from city where countrycode='CHN' group by district;
统计世界上每个国家的城市数量
select t.name,t.code,count(c.id) from city as c join country t on c.countrycode=t.code group by t.code;
统计中国每个省的总人口数，将总人口数小于100w进行从高到低排序显示
select district,sum(population) as total 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 s.s_no,avg(c.s_core) from student as s join score as c on s.s_no=c.s_no group by s.s_no having avg(c.s_core)>60;
2. 查询所有同学的学号、姓名、选课数、总成绩；
select s.s_no,s.s_name,c.c_name,sum(o.s_core) from student s join score o on s.s_no=o.s_no join course c on o.c_no=c.c_no group by s.s_no,c.c_no;
3. 查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
select c.c_no,c.c_name,max(o.s_core) max,min(o.s_core) min from score o join course c on o.c_no=c.c_no group by c.c_no;
4. 统计各位老师,所教课程的及格率(case)
count(case when isfull(socre,0)>=60 then 1 end)/count(*)
group by 学科
方法一、
mysql> select t.t_name,c.c_name,sum(s.s_core)/count(c.c_no) from teacher t join course c on t.t_no=c.t_no join score s on c.c_no=s.c_no where s.s_core >=60 group by t.t_no,c.c_no;
+--------+--------+-----------------------------+
| t_name | c_name | sum(s.s_core)/count(c.c_no) |
+--------+--------+-----------------------------+
| oldboy | linux | 80.66666666666667 |
| hesw | python | 75.5 |
| oldguo | mysql | 89 |
+--------+--------+-----------------------------+
方法二、
mysql> SELECT t.t_name, c.c_name, CASE WHEN 1 = 1 THEN sum(r.s_core) / count(c.c_no) END AS '及格率' FROM teacher t JOIN course c ON t.t_no = c.t_no JOIN score r ON c.c_no = r.c_no where r.s_core>=60 GROUP BY t.t_no, c.c_no;
+--------+--------+-------------------+
| t_name | c_name | 及格率 |
+--------+--------+-------------------+
| oldboy | linux | 80.66666666666667 |
| hesw | python | 70 |
| oldguo | mysql | 76.75 |
+--------+--------+-------------------+
方法三、
select teacher.t_name,course.c_name,count(case when ifnull(score.s_core,0)>=60 then 1 end)/count(*)*100 as '及格率' from teacher join course on course.t_no=teacher.t_no join score on score.c_no=course.c_no group by teacher.t_no;
+--------+--------+-----------+
| t_name | c_name | 及格率 |
+--------+--------+-----------+
| oldboy | linux | 100.0000 |
| hesw | python | 66.6667 |
| oldguo | mysql | 75.0000 |
+--------+--------+-----------+
方法四、
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 c.c_name,count(t.s_no) from student t join score e on t.s_no=e.s_no join course c on e.c_no=c.c_no group by c.c_no;
+--------+---------------+
| c_name | count(t.s_no) |
+--------+---------------+
| linux | 6 |
| python | 3 |
| mysql | 8 |
+--------+---------------+
6. 查询出只选修了一门课程的全部学生的学号和姓名
select t.s_no,t.s_name,count(c.c_no) from student t join score e on t.s_no=e.s_no join course c on e.c_no=c.c_no group by t.s_no having count(c.c_no)=1;
+------+---------+---------------+
| s_no | s_name | count(c.c_no) |
+------+---------+---------------+
| 5 | zh4 | 1 |
| 8 | oldboy | 1 |
| 9 | oldgirl | 1 |
| 10 | oldp | 1 |
+------+---------+---------------+
7. 查询选修课程门数超过1门的学生信息
select t.s_no,t.s_name,count(c.c_no) from student t join score e on t.s_no=e.s_no join course c on e.c_no=c.c_no group by t.s_no having count(c.c_no)>1;
+------+--------+---------------+
| s_no | s_name | count(c.c_no) |
+------+--------+---------------+
| 1 | zhang3 | 2 |
| 2 | zhang4 | 2 |
| 3 | li4 | 2 |
| 4 | wang5 | 3 |
| 6 | zhao4 | 2 |
| 7 | ma6 | 2 |
+------+--------+---------------+
8. 统计每门课程:优秀(85分以上),良好(70-85),一般(60-70),不及格(小于60)的学生列表(case)
,group_concat(case when isfull(score ,0) >=85 then sname end) as '优秀'
,group_concat(case when isfull(score ,0) between 70 and 85 then sname end ) as '良好'
方法一、
select d.s_name as '一般' from teacher t join course c on t.t_no=c.t_no join score s on c.c_no=s.c_no join student d on s.s_no=d.s_no where s.s_core >=60 and s.s_core <70 group by t.t_no,c.c_no,d.s_no;
+--------+
| 一般 |
+--------+
| ma6 |
| wang5 |
+--------+
select d.s_name as '良好' from teacher t join course c on t.t_no=c.t_no join score s on c.c_no=s.c_no join student d on s.s_no=d.s_no where s.s_core >=70 and s.s_core <85 group by t.t_no,c.c_no,d.s_no;
+---------+
| 良好 |
+---------+
| zhang3 |
| wang5 |
| oldboy |
| zhao4 |
| ma6 |
| oldgirl |
+---------+
select d.s_name as '优秀' from teacher t join course c on t.t_no=c.t_no join score s on c.c_no=s.c_no join student d on s.s_no=d.s_no where s.s_core >=85 group by t.t_no,c.c_no,d.s_no;
+--------+
| 优秀 |
+--------+
| li4 |
| zhao4 |
| zhang4 |
| zhang4 |
| wang5 |
| oldp |
+--------+
select d.s_name as '不及格' from teacher t join course c on t.t_no=c.t_no join score s on c.c_no=s.c_no join student d on s.s_no=d.s_no where s.s_core <60 group by t.t_no,c.c_no,d.s_no;
+-----------+
| 不及格 |
+-----------+
| zhang3 |
| li4 |
| zh4 |
+-----------+
方法二、
mysql> SELECT CASE WHEN s.s_core >= 85 THEN d.s_name END AS '优秀', CASE WHEN s.s_core >= 70 AND s.s_core < 85 THEN d.s_name END AS '良好', CASE WHEN s.s_core >= 60 AND s.s_core < 70 THEN d.s_name END AS '一般', CASE WHEN s.s_core < 60 THEN d.s_name END AS '不及格', s.s_core FROM score s JOIN course c ON s.c_no = c.c_no JOIN student d ON s.s_no = d.s_no;
+--------+---------+--------+-----------+--------+
| 优秀 | 良好 | 一般 | 不及格 | s_core |
+--------+---------+--------+-----------+--------+
| NULL | zhang3 | NULL | NULL | 80 |
| NULL | NULL | NULL | zhang3 | 59 |
| zhang4 | NULL | NULL | NULL | 90 |
| zhang4 | NULL | NULL | NULL | 100 |
| li4 | NULL | NULL | NULL | 99 |
| NULL | NULL | NULL | li4 | 40 |
| NULL | wang5 | NULL | NULL | 79 |
| NULL | NULL | wang5 | NULL | 61 |
| wang5 | NULL | NULL | NULL | 99 |
| NULL | NULL | NULL | zh4 | 40 |
| zhao4 | NULL | NULL | NULL | 89 |
| NULL | zhao4 | NULL | NULL | 77 |
| NULL | NULL | ma6 | NULL | 67 |
| NULL | ma6 | NULL | NULL | 82 |
| NULL | oldboy | NULL | NULL | 70 |
| NULL | oldgirl | NULL | NULL | 80 |
| oldp | NULL | NULL | NULL | 96 |
+--------+---------+--------+-----------+--------+
方法三、
select student.s_no,student.s_name,course.c_name,score.s_core, case when school.score.s_core<60 then '不及格' when score.s_core>=60 and score.s_core<70 then '一般' when score.s_core>=70 and score.s_core<85 then '良好' when score.s_core>=85 then '优秀' END as '级别' from student join score on student.s_no=score.s_no join course on score.c_no=course.c_no;
+------+---------+--------+--------+-----------+
| s_no | s_name | c_name | s_core | 级别 |
+------+---------+--------+--------+-----------+
| 1 | zhang3 | linux | 80 | 良好 |
| 1 | zhang3 | python | 59 | 不及格 |
| 2 | zhang4 | python | 90 | 优秀 |
| 2 | zhang4 | mysql | 100 | 优秀 |
| 3 | li4 | linux | 99 | 优秀 |
| 3 | li4 | mysql | 40 | 不及格 |
| 4 | wang5 | linux | 79 | 良好 |
| 4 | wang5 | python | 61 | 一般 |
| 4 | wang5 | mysql | 99 | 优秀 |
| 5 | zh4 | mysql | 40 | 不及格 |
| 6 | zhao4 | linux | 89 | 优秀 |
| 6 | zhao4 | mysql | 77 | 良好 |
| 7 | ma6 | linux | 67 | 一般 |
| 7 | ma6 | mysql | 82 | 良好 |
| 8 | oldboy | linux | 70 | 良好 |
| 9 | oldgirl | mysql | 80 | 良好 |
| 10 | oldp | mysql | 96 | 优秀 |
+------+---------+--------+--------+-----------+
方法四、
select course.c_name, group_concat(case when ifnull(score.s_core,0)>=85 then student.s_name end) as '优秀', group_concat(case when ifnull(score.s_core,0) between 70 and 85 then student.s_name end ) as '良好', group_concat(case when ifnull(score.s_core,0) between 60 and 70 then student.s_name end) as '一般', group_concat(case when ifnull(score.s_core,0)< 60 then student.s_name end) as '不及格' from student join score on score.s_no=student.s_no join course on course.c_no=score.c_no group by course.c_no;
+--------+-------------------+---------------------+------------+-----------+
| c_name | 优秀 | 良好 | 一般 | 不及格 |
+--------+-------------------+---------------------+------------+-----------+
| linux | zhao4,li4 | wang5,zhang3,oldboy | ma6,oldboy | NULL |
| python | zhang4 | NULL | wang5 | zhang3 |
| mysql | wang5,zhang4,oldp | zhao4,ma6,oldgirl | NULL | zh4,li4 |
+--------+-------------------+---------------------+------------+-----------+
9. 查询平均成绩大于85的所有学生的学号、姓名和平均成绩
select t.s_no,t.s_name,avg(e.s_core) from student t join score e on t.s_no=e.s_no group by t.s_no having avg(e.s_core)>85;
</article>
1人点赞
作者：张维斌
链接：https://www.jianshu.com/p/acf9d032faca
来源：简书
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。
