记一次mysql面试题
基础数据准备
/**创建测试库**/
create database test;
/**创建部门表**/
CREATE TABLE dept( /*部门表*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*编号*/
dname VARCHAR(20) NOT NULL DEFAULT "", /*名称*/
loc VARCHAR(13) NOT NULL DEFAULT "" /*地点*/
) ENGINE=MyISAM DEFAULT CHARSET=utf8
/** Innodb索引**/;
CREATE TABLE `test`.`dept` (
`deptno` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`dname` VARCHAR(45) NOT NULL,
`loc` VARCHAR(45) NOT NULL,
PRIMARY KEY (`deptno`)) ENGINE=Innodb DEFAULT CHARSET=utf8;
/** MyISAM 索引**/
CREATE TABLE `test`.`dept` (
`deptno` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`dname` VARCHAR(45) NOT NULL,
`loc` VARCHAR(45) NOT NULL,
PRIMARY KEY (`deptno`)) ENGINE=MyISAM DEFAULT CHARSET=utf8;
/**创建员工表**/
CREATE TABLE emp
(empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*编号*/
ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/
job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/
mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*上级编号*/
hiredate DATE NOT NULL,/*入职时间*/
sal DECIMAL(7,2) NOT NULL,/*薪水*/
comm DECIMAL(7,2) NOT NULL,/*红利*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/
)ENGINE=MyISAM DEFAULT CHARSET=utf8 ;
/** MyISAM 索引**/
CREATE TABLE `test`.`emp` (
`empno` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
`ename` VARCHAR(20) NOT NULL,
`job` VARCHAR(9) NOT NULL,
`mgr` MEDIUMINT NOT NULL,
`hiredate` DATE NOT NULL,
`sal` DECIMAL(7,2) NOT NULL,
`comm` DECIMAL(7,2) NOT NULL,
`deptno` MEDIUMINT NOT NULL,
PRIMARY KEY (`empno`)) ENGINE=MyISAM DEFAULT CHARSET=utf8;
/** Innodb索引**/
CREATE TABLE `test`.`emp` (
`empno` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
`ename` VARCHAR(20) NOT NULL,
`job` VARCHAR(9) NOT NULL,
`mgr` MEDIUMINT NOT NULL,
`hiredate` DATE NOT NULL,
`sal` DECIMAL(7,2) NOT NULL,
`comm` DECIMAL(7,2) NOT NULL,
`deptno` MEDIUMINT NOT NULL,
PRIMARY KEY (`empno`)) ENGINE=Innodb DEFAULT CHARSET=utf8;
/**创建随机生成字符串函数**/
delimiter $$
create function rand_string(n INT)
returns varchar(255)
begin
declare chars_str varchar(100) default 'abcdefghigklmnopqrstuvwxyzABCDEFGHIGKLMNOPQRSTUVWXYZ';
declare return_str varchar(255) default '';
declare i int default 0;
while i < n do
set return_str =concat(return_str,substring(chars_str,floor(1+rand()*52),1));
set i = i + 1;
end while;
return return_str;
end $$
/** 删除函数**/
drop function rand_string$$
/**创建随机生成数字函数**/
create function rand_num(n INT) returns INT
begin
return floor(1+rand()*n);
end $$
/**创建随机生成数字函数**/
create function rand_num() returns INT begin return floor(1+rand()*10); end$$
/** 创建一个存储过程**/
create procedure insert_emp(in start int(10),in max_num int(10))
begin
declare i int default 0;
#set autocommit =0 把autocommit设置成0
set autocommit = 0;
repeat
set i = i + 1;
insert into emp values ((start+i) ,rand_string(6),'SALESMAN',0001,curdate(),rand_num(20000),400,rand_num(10));
until i = max_num
end repeat;
commit;
end $$
/** 创建部门存储过程**/
create procedure insert_dept(in start int(10),in max_num int(10))
begin
declare i int default 0;
#set autocommit =0 把autocommit设置成0
set autocommit = 0;
repeat
set i = i + 1;
insert into dept(dname, loc) values (rand_string(8),rand_string(20));
until i = max_num
end repeat;
commit;
end $$
/** 创建员工存储过程 **/
create procedure insert_emp(in start int(10),in max_num int(10))
begin
declare i int default 0;
#set autocommit =0 把autocommit设置成0
set autocommit = 0;
repeat
set i = i + 1;
insert into emp(ename, job,mgr, hiredate,sal,comm,deptno) values (rand_string(6),'SALESMAN',0001,curdate(),rand_num(20000),400,rand_num(10));
until i = max_num
end repeat;
commit;
end $$
/**删除存储过程**/
drop procedure insert_emp$$
/**创建索引**/
create index deptnoIndex on emp(deptno);
alter table emp add primary key(empno);
ALTER TABLE `test`.`emp` CHANGE COLUMN `empno` `empno` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT ;
/**插入数据**/
call insert_emp(1, 100000);
查询出各部门最高工资最高的员工信息(员工id,员工name,员工salary
答案
mysql> select s.deptno, s.empno, s.ename, s.sal from (select * from emp order by sal desc)as s group by deptno limit 10;
+--------+-------+--------+----------+
| deptno | empno | ename | sal |
+--------+-------+--------+----------+
| 1 | 72239 | siLfuH | 19997.00 |
| 2 | 61024 | whwMtG | 19999.00 |
| 3 | 6081 | tGXVIF | 19998.00 |
| 4 | 20886 | uCFrVC | 19998.00 |
| 5 | 45454 | CDknLR | 20000.00 |
| 6 | 4803 | EsckQz | 19997.00 |
| 7 | 62417 | WmwuLw | 19994.00 |
| 8 | 49995 | GXGEVM | 19992.00 |
| 9 | 85071 | yTbxlM | 19995.00 |
| 10 | 254 | fphQKb | 19996.00 |
+--------+-------+--------+----------+
mysql> explain select * from (select * from emp order by sal desc)as s group by deptno limit 10;
+----+-------------+------------+------+---------------+------+---------+------+--------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+--------+---------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 100000 | Using temporary; Using filesort |
| 2 | DERIVED | emp | ALL | NULL | NULL | NULL | NULL | 100166 | Using filesort |
+----+-------------+------------+------+---------------+------+---------+------+--------+---------------------------------+
2 rows in set (0.17 sec)
由MyISAM作为engine创建的表
## deptno上的索引没有起到作用
mysql> explain select max(sal), deptno from emp group by deptno desc limit 100;
+----+-------------+-------+------+---------------+------+---------+------+--------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+---------------------------------+
| 1 | SIMPLE | emp | ALL | NULL | NULL | NULL | NULL | 100000 | Using temporary; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+--------+---------------------------------+
1 row in set (0.01 sec)
由InnoDB作为engine创建的表
- 注意点:max函数同group by一起使用时,虽然sal是最大的,但是与之匹配的用户信息却是不对的(实际上取得是group by分组后的第一条记录的基本信息)
mysql> select max(sal), deptno, empno, ename from emp group by deptno desc limit 20;
+----------+--------+--------+--------+
| max(sal) | deptno | empno | ename |
+----------+--------+--------+--------+
| 19996.00 | 10 | 99999 | HOXxsy |
| 19995.00 | 9 | 99993 | aCKReV |
| 19992.00 | 8 | 99978 | DHywNA |
| 19994.00 | 7 | 99995 | aqCOmT |
| 19997.00 | 6 | 99970 | pvgxSW |
| 20000.00 | 5 | 99988 | nggcTk |
| 19998.00 | 4 | 100000 | UIEZgV |
| 19998.00 | 3 | 99991 | hOvNCy |
| 19999.00 | 2 | 99998 | ubVyIS |
| 19997.00 | 1 | 99996 | moGEVN |
+----------+--------+--------+--------+
10 rows in set (0.09 sec)
mysql> select * from emp group by deptno desc limit 20;
+--------+--------+----------+-----+------------+----------+--------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+--------+--------+----------+-----+------------+----------+--------+--------+
| 99999 | HOXxsy | SALESMAN | 1 | 2019-05-20 | 5403.00 | 400.00 | 10 |
| 99993 | aCKReV | SALESMAN | 1 | 2019-05-20 | 6364.00 | 400.00 | 9 |
| 99978 | DHywNA | SALESMAN | 1 | 2019-05-20 | 5324.00 | 400.00 | 8 |
| 99995 | aqCOmT | SALESMAN | 1 | 2019-05-20 | 13049.00 | 400.00 | 7 |
| 99970 | pvgxSW | SALESMAN | 1 | 2019-05-20 | 1418.00 | 400.00 | 6 |
| 99988 | nggcTk | SALESMAN | 1 | 2019-05-20 | 8301.00 | 400.00 | 5 |
| 100000 | UIEZgV | SALESMAN | 1 | 2019-05-20 | 242.00 | 400.00 | 4 |
| 99991 | hOvNCy | SALESMAN | 1 | 2019-05-20 | 14879.00 | 400.00 | 3 |
| 99998 | ubVyIS | SALESMAN | 1 | 2019-05-20 | 6920.00 | 400.00 | 2 |
| 99996 | moGEVN | SALESMAN | 1 | 2019-05-20 | 1235.00 | 400.00 | 1 |
+--------+--------+----------+-----+------------+----------+--------+--------+
10 rows in set (0.13 sec)
改进:利用join和索引。
mysql> explain select max(sal), deptno from emp group by deptno desc limit 100;
+----+-------------+-------+-------+---------------+-------------+---------+------+--------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-------------+---------+------+--------+-------+
| 1 | SIMPLE | emp | index | NULL | deptnoIndex | 3 | NULL | 100157 | |
+----+-------------+-------+-------+---------------+-------------+---------+------+--------+-------+
1 row in set (0.00 sec)
mysql> explain select a.deptno, a.empno, a.ename, a.sal from emp a join (select max(sal) sal, deptno from emp group by deptno)b on a.sal= b.sal and a.deptno = b.deptno order by a.deptno limit 10;
+----+-------------+------------+-------+---------------+-------------+---------+------+--------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+---------------+-------------+---------+------+--------+---------------------------------+
| 1 | PRIMARY | a | ALL | deptnoIndex | NULL | NULL | NULL | 100166 | Using temporary; Using filesort |
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 10 | Using where; Using join buffer |
| 2 | DERIVED | emp | index | NULL | deptnoIndex | 3 | NULL | 100166 | |
+----+-------------+------------+-------+---------------+-------------+---------+------+--------+---------------------------------+
3 rows in set (0.08 sec)
## 因为是选出最大工资,所以对工资添加索引。
mysql> create index salIndex on emp(sal);
Query OK, 0 rows affected (0.28 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select a.deptno, a.empno, a.ename, a.sal from emp a join (select max(sal) sal, deptno from emp group by deptno)b on a.sal= b.sal and a.deptno = b.deptno order by a.deptno limit 10;
+----+-------------+------------+-------+----------------------+-------------+---------+-------+--------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+----------------------+-------------+---------+-------+--------+---------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 10 | Using temporary; Using filesort |
| 1 | PRIMARY | a | ref | deptnoIndex,salIndex | salIndex | 4 | b.sal | 500 | Using where |
| 2 | DERIVED | emp | index | NULL | deptnoIndex | 3 | NULL | 100166 | |
+----+-------------+------------+-------+----------------------+-------------+---------+-------+--------+---------------------------------+
3 rows in set (0.07 sec)
mysql> select a.deptno, a.empno, a.ename, a.sal from emp a join (select max(sal) sal, deptno from emp group by deptno)b on a.sal= b.sal and a.deptno = b.deptno order by a.deptno limit 10;
+--------+-------+--------+----------+
| deptno | empno | ename | sal |
+--------+-------+--------+----------+
| 1 | 72239 | siLfuH | 19997.00 |
| 2 | 61024 | whwMtG | 19999.00 |
| 3 | 6081 | tGXVIF | 19998.00 |
| 4 | 78399 | AAYtWD | 19998.00 |
| 4 | 20886 | uCFrVC | 19998.00 |
| 5 | 45454 | CDknLR | 20000.00 |
| 6 | 4803 | EsckQz | 19997.00 |
| 7 | 62417 | WmwuLw | 19994.00 |
| 8 | 49995 | GXGEVM | 19992.00 |
| 9 | 85071 | yTbxlM | 19995.00 |
+--------+-------+--------+----------+
10 rows in set (0.07 sec)
查询出各部门最高工资的员工信息(部门id,部门name,员工name,员工salary)
由InnoDB作为engine创建的表
## 方式一
mysql> explain select d.deptno, d.dname, e.empno, e.ename, e.sal from emp e join dept d where e.deptno = d.deptno and e.sal >=(select max(sal) from emp e2 where e.deptno = e2.deptno);
+----+--------------------+-------+------+---------------+-------------+---------+---------------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+------+---------------+-------------+---------+---------------+-------+-------------+
| 1 | PRIMARY | d | ALL | PRIMARY | NULL | NULL | NULL | 10 | |
| 1 | PRIMARY | e | ref | deptnoIndex | deptnoIndex | 3 | test.d.deptno | 25041 | Using where |
| 2 | DEPENDENT SUBQUERY | e2 | ref | deptnoIndex | deptnoIndex | 3 | test.e.deptno | 25041 | |
+----+--------------------+-------+------+---------------+-------------+---------+---------------+-------+-------------+
3 rows in set (0.00 sec)
## 方式二:利用索引改进
mysql> explain select a.deptno, c.dname, a.empno, a.ename, a.sal from emp a left join dept c on a.deptno = c.deptno join (select max(sal) sal, deptno from emp group by deptno)b on a.sal= b.sal and a.deptno = b.deptno order by a.deptno ;
+----+-------------+------------+--------+----------------------+-------------+---------+---------------+--------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+----------------------+-------------+---------+---------------+--------+---------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 10 | Using temporary; Using filesort |
| 1 | PRIMARY | a | ref | deptnoIndex,salIndex | salIndex | 4 | b.sal | 500 | Using where |
| 1 | PRIMARY | c | eq_ref | PRIMARY | PRIMARY | 4 | test.a.deptno | 1 | |
| 2 | DERIVED | emp | index | NULL | deptnoIndex | 3 | NULL | 100166 | |
+----+-------------+------------+--------+----------------------+-------------+---------+---------------+--------+---------------------------------+
4 rows in set (0.07 sec)
select a.deptno, c.dname, a.empno, a.ename, a.sal from emp a left join dept c on a.deptno = c.deptno join (select max(sal) sal, deptno from emp group by deptno)b on a.sal= b.sal and a.deptno = b.deptno order by a.deptno ;
+--------+----------+-------+--------+----------+
| deptno | dname | empno | ename | sal |
+--------+----------+-------+--------+----------+
| 1 | PqmlvwSZ | 72239 | siLfuH | 19997.00 |
| 2 | hLiGGACh | 61024 | whwMtG | 19999.00 |
| 3 | aVzKcfuH | 6081 | tGXVIF | 19998.00 |
| 4 | OdaQcOPF | 78399 | AAYtWD | 19998.00 |
| 4 | OdaQcOPF | 20886 | uCFrVC | 19998.00 |
| 5 | iFCXeFMV | 45454 | CDknLR | 20000.00 |
| 6 | aVzLiGTs | 4803 | EsckQz | 19997.00 |
| 7 | RfcUpTwE | 62417 | WmwuLw | 19994.00 |
| 8 | ZMLvUmzO | 49995 | GXGEVM | 19992.00 |
| 9 | OVoHvgtA | 85071 | yTbxlM | 19995.00 |
| 10 | rvgsvbUs | 254 | fphQKb | 19996.00 |
| 10 | rvgsvbUs | 473 | KXHucb | 19996.00 |
+--------+----------+-------+--------+----------+