mysql面试题及其解题思路

1.针对如下表actor结构创建索引：
CREATE TABLE IF NOT EXISTS actor (
actor_id smallint(5) NOT NULL PRIMARY KEY,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update timestamp NOT NULL DEFAULT (datetime(‘now’,’localtime’)))
对first_name创建唯一索引uniq_idx_firstname，对last_name创建普通索引idx_lastname
答案：
CREATE UNIQUE INDEX uniq_idx_firstname ON actor(first_name);
CREATE INDEX idx_lastname ON actor(last_name);
2.按照dept_no进行汇总，属于同一个部门的emp_no按照逗号进行连接，结果给出dept_no以及连接出的结果employees
CREATE TABLE dept_emp (
emp_no int(11) NOT NULL,
dept_no char(4) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,dept_no));
输出格式:
dept_no employees
d001 10001,10002
d002 10006
d003 10005
答案：

select dept_no,group_concat
(emp_no)  as employees from dept_emp group by dept_no;
group_concat函数，结合group by一起使用

3.分页查询employees表，每5行一页，返回第2页的数据
答案：

select * from employees limit 5,5;
注意：第一条开始 的索引为 0；

4.查找排除当前最大、最小salary之后的员工的平均工资avg_salary
默认当前时间是：’9999-01-01’

SELECT AVG(salary) AS avg_salary FROM salaries 
WHERE to_date = '9999-01-01' 
AND salary NOT IN (SELECT MAX(salary) FROM salaries WHERE to_date = '9999-01-01')
AND salary NOT IN (SELECT MIN(salary) FROM salaries WHERE to_date = '9999-01-01')
知识：not in

5.将所有获取奖金的员工当前的薪水增加10%。
create table emp_bonus(
emp_no int not null,
recevied datetime not null,
btype smallint not null);
CREATE TABLE salaries (
emp_no int(11) NOT NULL,
salary int(11) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL, PRIMARY KEY (emp_no,from_date));

update salaries set salary=salary*1.1 where emp_no in(select emp_no from emp_bonus);
知识：in