常见MySQL面试题解析一

首先申明一下要用的表

员工表
EMPLOYEE_ID：员工ID
FIRST_NAME：姓
LAST_NAME：名
EMAIL：email
phone_int：电话
HIRE_DATE：雇佣时间
JOB_ID：工作id
SALARY：月薪e
COMMISSION_PCT：奖金点
MANAGER_ID：该员工经理ID
DEPARTMENT_ID：该员工所属部门id

部门表
DEPARTMENT_ID：部门ID
DEPARTMENT_NAME：部门名称
MANAGER_ID：部门管理员ID
LOCATION_ID：部门地址ID

地址表：
LOCATION_ID：地址ID
STREET_ADDRESS：地址信息
POSTAL_CODE：邮政编码
CITY：城市
STATE_PROVINCE：省份
COIUNTRY_ID：所属国家ID

国家表：
COUNTRY_ID：国家id
COUNTRY_NAME：国家名字
REGION_ID：所属地域ID

地域表：
REGION_ID：地域ID
REGION_NAME：地域名称

员工工作历史表：
EMPLOYEE_ID：员工ID
START_DATE：开始工作时间
END_DATE：工作结束时间
JOB_ID：工作ID
DEPARTMENT_ID：所属部门ID

工资等级表：
GRADE_LEVEL：等级；
LOWEST_SAL：该等级下限；
HIGHEST_SAL：该等级上限；

------------------------------------------------------------分割线------------------------------------------------------------------------

一、查询员工的全名，email和电话

select 

CONCAT(first_name,last_name) full_name,

employee_id,email,phone_int 

from employees

string拼接函数concat的使用

二、查询所有员工的全名，月薪和年薪（月薪*12）

select 

employee_id,CONCAT(first_name,last_name) full_name,

salary, salary * 12 year_income 

from employees

考点：涉及到列的运算

三、查询所有员工的全名，月薪和年终奖(年薪*commission_pct)

select 

CONCAT(first_name,last_name) full_name,salary monthIncome, 

salary*12*ifnull(commission_pct,0) yearReward  

from employees

这里有坑，因为commission_pct完全可能为空
此处考察对于null的处理，ifnull(exp,defaultValue),若exp为null则采用默认值

四、哪些部门有员工,员工的职位是什么

SELECT 

count(e.email),e.job_id

from employees e right join departments d 

on e.department_id = d.department_id

五、查询1999年之后(含1999年)入职的员工信息

select * from employees where `YEAR`(HIRE_DATE) = 1999;

考察MySQL内置函数的用法

六、查询公司的老板信息

SELECT * 

from employees

where manager_id is null 

此处考察对于列值为null的判断,老板由谁管？

七、查询所有员工信息，按照部门升序和年薪降序排序；

select 

CONCAT(first_name,last_name) full_name,

salary*12 yearIncome , DEPARTMENT_ID 

from employees

order by DEPARTMENT_ID asc , yearIncome desc;

八、计算员工姓名和全薪；

select CONCAT(first_name,last_name) full_name,  

(salary * 12 + salary * 12 * IFNULL(commission_pct,0)) fullSalary 

from employees

这里同样是个大坑，具体考察对于null的处理,null和谁发生运算结果都是null

九、查询员工所属的部门id，如果没有部门，打印“未分配部门”

	select e.employee_id, ifnull(e.department_id,"未分配部门")

	from employees 

对于null值的优雅处理

十、查询出每一个部门的平均工资

	select d.department_id, avg(e.salary) 
	
	from employees e join departments d on e.department_id = d.department_id

	group by e.department_id

分组的考察

十一、查询平均工资高于8000的部门和其平均工资

	select d.department_id, d.department.name
	
	from employees e join departments d on e.department_id = d.department_id

	group by e.department_id having avg(e.salary) > 8000

group by + having 的考察，手写的时候容易忘记，having必须在有group的前提下才能使用

十二、查询换过工作员工换工作的次数

	select e.employee_id, CONCAT(first_name,last_name) full_name,count(e.employee_id)
	
	from employees e join job_history jh on jh.employee_id = e.employee_id

	group by e.employee_id

十三、查询在1995，1996，1997，1998年各进公司多少人

	select Year(hire_date),count(*)

	from employees

	where Year(hire_date) >= '1995' and Year(hire_date) <= '1998'

	group by Year(hire_date)

对于in的考察

十四、输出员工名称，员工id，员工所属部门

	select e.employee_id, CONCAT(first_name,last_name) full_name, d.department_name
	
	from employees e join departments d on e.department_id = d.department_id

十五、输出员工信息，包括employee_id, first_name, department_id,
department_name location_id city

select employee_id,first_name,last_name,

d.DEPARTMENT_NAME,d.department_id,city

from employees e INNER JOIN departments d 

on e.DEPARTMENT_ID = d.DEPARTMENT_ID

INNER JOIN locations lo on lo.location_id = d.location_id

十六、在 EMPLOYEES 表中薪水的最低级别和最高级别分别是什么？

select jg.grade_level from job_grades jg join 

(
		select salary from employees where

		salary = (select MIN(salary) from employees ) or salary = (select MAX(salary) from employees)

)t

	 on t.salary BETWEEN jg.lowest_sal and jg.highest_sal 

十七、查询所有有奖金的员工的姓名，部门，地址，城市

select 

	e.employee_id,CONCAT(first_name,last_name) full_name,

	d.department_name, 

	lo.street_address,lo.city,

	c.country_name,

	r.region_name			
	
	from 

	employees e join departments d on e.department_id = d.department_id

	join locations lo on lo.location_id = d.location_id

	join countries c on c.country_id = lo.country_id

	join regions r on r.region_id = c.region_id

	where e.commission_pct is not null

十七、查询last_name为’Ki’ 的员工的经理信息

	select m.employee_id,CONCAT(m.first_name,m.last_name) full_name

	from employees e join employees m on e.manager_id = m.employee_id

	where e.last_name like concat('Ki','%')

十八、查询公司工资最低的员工信息

	select employee_id,CONCAT(first_name,last_name) full_name, min(salary)

	from employees 

十九、查询1999年进入公司的员工的最高工资的员工

	
	select e.employee_id,CONCAT(first_name,last_name) full_name, max(e.salary)

	from employees e where YEAR(e.hire_date) = '1999'

二十、查询曾经做过ST_CLERK的员工信息

	select 

	e.employee_id, CONCAT(first_name,last_name) full_name
	
	from employees e join job_history jh on e.employee_id = jh.employee_id

	where jh.job_id = 'ST_CLERK' and e.job_id != 'ST_CLERK'

胜负统计问题：

	select 

	date,
	
	(select count(*) from game where result = '胜' and tab1.date = date),
	
	(select count(*) from game where result = '负' and tab1.date = date)

	from game tab1

	group by date