魏长东

weichangdong

东邪

最好的mysql面试题

表结构约定:

emp雇员表(empno员工号/ename员工姓名/job工作/mgr上级编号/hiredate受雇日期/sal薪金/comm佣金/deptno部门编号)

dept部门表(deptno部门编号/dname部门名称/loc地点)

emp 雇员表(
    empno 员工号
    ename员工姓名
    job 工作
    mgr 上级编号
    hiredate 受雇日期
    sal 金币
    comm佣金
    deptno 部门编号
)

dept 部门表(
    deptno部门编号
    dname部门名称
    loc地点
)

create table emp(empno int(2),ename varchar(10),
job varchar(12),mgr int(2),hiredate date,sal int(5),comm int(5),deptno int(5));
create table dept(deptno int(5),dname varchar(12),loc varchar(12));

insert into emp values(7369, 'Smith', 'Clerk',7902, '1980-12-17',800,0,20
) ;
insert into emp values(7499, 'Allen', 'Salesman',7698,'1981-2-20',1600,300,30) ;
insert into emp values(7844, 'Turner', 'Salesman',7499, '1981-9-8',1500,0
,30) ;
insert into emp values(7698, 'Tom', 'Manager',0, '1981-9-8',6100,600,40)
;
insert into emp values(7876, 'Adams', 'Clerk',7900, '1987-5-23',1100,0,20
) ;
insert into emp values(7900, 'James', 'Clerk',7698, '1981-12-3',2400,0,30
) ;
insert into emp values(7902, 'Ford', 'Analyst',7698, '1981-12-3',3000,null,20) ;
insert into emp values(7901, 'Kik', 'Clerk',7900, '1981-12-3',1900,0,30)
;
insert into emp values(7564, 'Scott', 'Salesman',7319, '1982-9-6',1550,0,
30);
insert into emp values(7324, 'Petter', 'Salesman',7561, '1980-3-6',1650,0
,30);


insert into dept values(10, 'Accounting', 'New York') ;
insert into dept values(20, 'Research', 'Dallas') ;
insert into dept values(30, 'Sales', 'Chicago') ;
insert into dept values(40, 'Operations', 'Boston') ;
insert into dept values(50, 'Admin', 'Washing') ;
mysql> select * from emp;
+-------+--------+----------+------+------------+------+------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+----------+------+------------+------+------+
| 7369 | Smith | Clerk | 7902 | 1980-12-17 | 800 | 0 | 20 |
| 7499 | Allen | Salesman | 7698 | 1981-02-20 | 1600 | 300 | 30 |
| 7844 | Turner | Salesman | 7499 | 1981-09-08 | 1500 | 0 | 30 |
| 7698 | Tom | Manager | 0 | 1981-09-08 | 6100 | 600 | 40 |
| 7876 | Adams | Clerk | 7900 | 1987-05-23 | 1100 | 0 | 20 |
| 7900 | James | Clerk | 7698 | 1981-12-03 | 2400 | 0 | 30 |
| 7902 | Ford | Analyst | 7698 | 1981-12-03 | 3000 | NULL | 20 |
| 7901 | Kik | Clerk | 7900 | 1981-12-03 | 1900 | 0 | 30 |
| 7564 | Scott | Salesman | 7319 | 1982-09-06 | 1550 | 0 | 30 |
| 7324 | Petter | Salesman | 7561 | 1980-03-06 | 1650 | 0 | 30 |
+-------+--------+----------+------+------------+------+------+
10 rows in set (0.00 sec)
mysql> select * from dept;
+--------+------------+----------+
| deptno | dname | loc |
+--------+------------+----------+
| 10 | Accounting | New York |
| 20 | Research | Dallas |
| 30 | Sales | Chicago |
| 40 | Operations | Boston |
| 50 | Admin | Washing |
+--------+------------+----------+
5 rows in set (0.00 sec)
1 、列出至少有一个员工的所有部门。 ( 两个表联合查询,及 group by...having 的用法 )

mysql> select * from dept where deptno in(select deptno from emp group by deptno
 having count(*)>1);
+--------+----------+---------+
| deptno | dname    | loc     |
+--------+----------+---------+
|     20 | Research | Dallas  |
|     30 | Sales    | Chicago |
+--------+----------+---------+
2 rows in set (0.00 sec)

2 、列出所有员工的姓名及其直接上级的姓名。 ( 多次对自己查询 , 为表的取个别名,
内部查询可以像对象一样引用外部的对象的字段,这里引用与编程中的作用域相似,即与 {} 类比 )

mysql> select ename,(select ename from emp where empno=a.mgr) from emp a;
+--------+-------------------------------------------+
| ename  | (select ename from emp where empno=a.mgr) |
+--------+-------------------------------------------+
| Smith  | Ford                                      |
| Allen  | Tom                                       |
| Turner | Allen                                     |
| Tom    | NULL                                      |
| Adams  | James                                     |
| James  | Tom                                       |
| Ford   | Tom                                       |
| Kik    | James                                     |
| Scott  | NULL                                      |
| Petter | NULL                                      |
+--------+-------------------------------------------+
10 rows in set (0.00 sec)

3 、列出受雇日期早于其直接上级的所有员工。 ( 同上 , 日期可直接拿来比较 )

mysql> select ename from emp a where hiredate<(select hiredate from emp where empno=a.mgr);
+-------+
| ename |
+-------+
| Smith |
| Allen |
+-------+
2 rows in set (0.00 sec)

4 、列出所有“ CLERK ”(办事员)的姓名及其部门名称

mysql> select (select dname from dept where deptno=a.deptno) as dname ,ename from 
emp a where job='CLERK';
+----------+-------+
| dname    | ename |
+----------+-------+
| Research | Smith |
| Research | Adams |
| Sales    | James |
| Sales    | Kik   |
+----------+-------+
4 rows in set (0.00 sec) 

5 、列出最低薪金大于 1500 的各种工作

mysql> select job,min(sal) msal from emp group by job having min(sal)>1500;
+---------+------+
| job     | msal |
+---------+------+
| Analyst | 3000 |
| Manager | 6100 |
+---------+------+
2 rows in set (0.00 sec)

6 、列出薪金高于公司平均薪金的所有员工。 ( 反复查自己 )

mysql> select ename from emp where sal>(select avg(sal) from emp);
+-------+
| ename |
+-------+
| Tom   |
| James |
| Ford  |
+-------+
3 rows in set (0.00 sec)

7 、列出与“ SCOTT ”从事相同工作的所有员工。 ( 排除自己 )

mysql> select * from emp where job=(select job from emp where ename='SCOTT');
+-------+--------+----------+------+------------+------+------+--------+
| empno | ename  | job      | mgr  | hiredate   | sal  | comm | deptno |
+-------+--------+----------+------+------------+------+------+--------+
|  7499 | Allen  | Salesman | 7698 | 1981-02-20 | 1600 |  300 |     30 |
|  7844 | Turner | Salesman | 7499 | 1981-09-08 | 1500 |    0 |     30 |
|  7564 | Scott  | Salesman | 7319 | 1982-09-06 | 1550 |    0 |     30 |
|  7324 | Petter | Salesman | 7561 | 1980-03-06 | 1650 |    0 |     30 |
+-------+--------+----------+------+------------+------+------+--------+
4 rows in set (0.00 sec)

8 、列出薪金等于部门 30 中员工的薪金的所有员工的姓名和薪金。 (any 的用法,且排挤 )

mysql> select * from emp where sal in (select sal from emp where deptno=30);
+-------+--------+----------+------+------------+------+------+--------+
| empno | ename  | job      | mgr  | hiredate   | sal  | comm | deptno |
+-------+--------+----------+------+------------+------+------+--------+
|  7499 | Allen  | Salesman | 7698 | 1981-02-20 | 1600 |  300 |     30 |
|  7844 | Turner | Salesman | 7499 | 1981-09-08 | 1500 |    0 |     30 |
|  7900 | James  | Clerk    | 7698 | 1981-12-03 | 2400 |    0 |     30 |
|  7901 | Kik    | Clerk    | 7900 | 1981-12-03 | 1900 |    0 |     30 |
|  7564 | Scott  | Salesman | 7319 | 1982-09-06 | 1550 |    0 |     30 |
|  7324 | Petter | Salesman | 7561 | 1980-03-06 | 1650 |    0 |     30 |
+-------+--------+----------+------+------------+------+------+--------+
6 rows in set (0.00 sec)

9 、列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金。 (max 的用法 )

mysql> select sal,ename from emp where sal>(select max(sal) from emp where deptn
o=30);
+------+-------+
| sal  | ename |
+------+-------+
| 6100 | Tom   |
| 3000 | Ford  |
+------+-------+
2 rows in set (0.00 sec)

10 、列出在每个 ( 每个是关键字 , 对此 group by) 部门工作的员工数量、平均工资和平均服务期限。
 ( 经典的 group by 用法 )

mysql> select deptno,count(*) as '员工数量',avg(sal) as '平均工资',avg(HIREDATE)
 as '平均服务期限' from emp a group by deptno;
+--------+----------+-----------+-----------------+
| deptno | 员工数量 | 平均工资  | 平均服务期限    |
+--------+----------+-----------+-----------------+
|     20 |        3 | 1633.3333 | 19827647.666667 |
|     30 |        6 | 1766.6667 |        19810791 |
|     40 |        1 | 6100.0000 |        19810908 |
+--------+----------+-----------+-----------------+
3 rows in set (0.00 sec)

11 、列出所有员工的姓名、部门名称和工资 .( 经典的两个表的连接查询,用具体的名称替换一个表中
的主键的 id ( 解决很多人在实际运用中会遇到的不能绑定多列的问题 ) ,也可用 where 来查询 , 
与题 5 比较 )

mysql> select ename,sal,(select dname from dept a where a.deptno=b.deptno)as dname from emp b;
+--------+------+------------+
| ename  | sal  | dname      |
+--------+------+------------+
| Smith  |  800 | Research   |
| Allen  | 1600 | Sales      |
| Turner | 1500 | Sales      |
| Tom    | 6100 | Operations |
| Adams  | 1100 | Research   |
| James  | 2400 | Sales      |
| Ford   | 3000 | Research   |
| Kik    | 1900 | Sales      |
| Scott  | 1550 | Sales      |
| Petter | 1650 | Sales      |
+--------+------+------------+
10 rows in set (0.00 sec)

12 、列出从事同一种工作但属于不同部门的员工的一种组合

mysql> select a.ename, b.ename, a.job, b.job, a.deptno, b.deptno from emp a,emp b where 
a.job=b.job and a.deptno<>b.deptno;
+-------+-------+-------+-------+--------+--------+
| ename | ename | job   | job   | deptno | deptno |
+-------+-------+-------+-------+--------+--------+
| James | Smith | Clerk | Clerk |     30 |     20 |
| Kik   | Smith | Clerk | Clerk |     30 |     20 |
| James | Adams | Clerk | Clerk |     30 |     20 |
| Kik   | Adams | Clerk | Clerk |     30 |     20 |
| Smith | James | Clerk | Clerk |     20 |     30 |
| Adams | James | Clerk | Clerk |     20 |     30 |
| Smith | Kik   | Clerk | Clerk |     20 |     30 |
| Adams | Kik   | Clerk | Clerk |     20 |     30 |
+-------+-------+-------+-------+--------+--------+
8 rows in set (0.00 sec)

13 、列出所有部门的详细信息和部门人数。 ( 因为是 * ,将显示 dept 和后面临时表 b 的全部字段 
( 注意 : 不只是 dept 的字段 , 注意 * 号 ))

mysql> select * from dept a left join (select deptno,count(*) from emp group by
deptno) b on a.deptno=b.deptno;
+--------+------------+----------+--------+----------+
| deptno | dname      | loc      | deptno | count(*) |
+--------+------------+----------+--------+----------+
|     10 | Accounting | New York |   NULL |     NULL |
|     20 | Research   | Dallas   |     20 |        3 |
|     30 | Sales      | Chicago  |     30 |        6 |
|     40 | Operations | Boston   |     40 |        1 |
|     50 | Admin      | Washing  |   NULL |     NULL |
+--------+------------+----------+--------+----------+
5 rows in set (0.00 sec)

14 、列出各种 ( 与每个同义 ( 参看题 13)) 工作的最低工资

mysql> select job,min(sal) from emp group by job;
+----------+----------+
| job      | min(sal) |
+----------+----------+
| Analyst  |     3000 |
| Clerk    |      800 |
| Manager  |     6100 |
| Salesman |     1500 |
+----------+----------+
4 rows in set (0.00 sec)

15 、列出各个部门的 MANAGER (经理 , 经理唯一,不用 group by )的最低薪金

mysql> select min(sal) from emp where job='MANAGER';
+----------+
| min(sal) |
+----------+
|     6100 |
+----------+
1 row in set (0.00 sec)