记一次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)begindeclare chars_str varchar(100) default 'abcdefghigklmnopqrstuvwxyzABCDEFGHIGKLMNOPQRSTUVWXYZ';declare return_str varchar(255) default '';declare i int default 0;while i < n doset 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 INTbeginreturn 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))begindeclare i int default 0; #set autocommit =0 把autocommit设置成0set autocommit = 0; repeatset 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_numend repeat;commit;end $$/** 创建部门存储过程**/create procedure insert_dept(in start int(10),in max_num int(10))begindeclare i int default 0; #set autocommit =0 把autocommit设置成0set autocommit = 0; repeatset i = i + 1;insert into dept(dname, loc) values (rand_string(8),rand_string(20));until i = max_numend repeat;commit;end $$/** 创建员工存储过程 **/create procedure insert_emp(in start int(10),in max_num int(10))begindeclare i int default 0; #set autocommit =0 把autocommit设置成0set autocommit = 0; repeatset 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_numend 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 | -05-20 | 5403.00 | 400.00 |10 || 99993 | aCKReV | SALESMAN | 1 | -05-20 | 6364.00 | 400.00 |9 || 99978 | DHywNA | SALESMAN | 1 | -05-20 | 5324.00 | 400.00 |8 || 99995 | aqCOmT | SALESMAN | 1 | -05-20 | 13049.00 | 400.00 |7 || 99970 | pvgxSW | SALESMAN | 1 | -05-20 | 1418.00 | 400.00 |6 || 99988 | nggcTk | SALESMAN | 1 | -05-20 | 8301.00 | 400.00 |5 || 100000 | UIEZgV | SALESMAN | 1 | -05-20 | 242.00 | 400.00 |4 || 99991 | hOvNCy | SALESMAN | 1 | -05-20 | 14879.00 | 400.00 |3 || 99998 | ubVyIS | SALESMAN | 1 | -05-20 | 6920.00 | 400.00 |2 || 99996 | moGEVN | SALESMAN | 1 | -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: 0mysql> 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 |+--------+----------+-------+--------+----------+
