14. 牛客网MySQL练习

MySQL常用命令集合 (帮你写练习~)

# MySQL查询语句的 执行 & 书写 顺序(7)  SELECT 
(8)  DISTINCT <select_list>
(1)  FROM <left_table>
(3)  <join_type> JOIN <right_table>
(2)  ON <join_condition>
(4)  WHERE <where_condition>
(5)  GROUP BY <group_by_list>
(6)  HAVING <having_condition>
(9)  ORDER BY <order_by_condition>
(10) LIMIT <limit_number>

14.1 零一到十五

题目01

查找最晚入职员工的所有信息，为了减轻入门难度，目前所有的数据里员工入职的日期都不是同一天

(sqlite里面的注释为–, mysql为comment)

CREATE TABLE `employees` (`emp_no` int(11) NOT NULL, `birth_date` date NOT NULL, `first_name` varchar(14) NOT NULL, `last_name` varchar(16) NOT NULL, `gender` char(1) NOT NULL, `hire_date` date NOT NULL, PRIMARY KEY (`emp_no`));

# 答案:
select * from employees order by hire_date desc limit 1;

order by 默认从小到大排列, 找最晚入职的员工需要找到 ‘hire_date’ 最大的, 所以需要倒序排列取第一个

order by hire_date desc 排序, 用 limit 1取第一个值

---

题目02

根据上一题的table; 查找入职员工时间排名倒数第三的员工所有信息

# 代码:
select * from employees order by hire_date limit 2, 1;

---

题目03

查找各个部门当前 (dept_manager.to_date=‘9999-01-01’) 领导当前 (salaries.to_date=‘9999-01-01’) 薪水详情以及其对应部门编号dept_no

(注: 请以salaries表为主表进行查询，输出结果以salaries.emp_no升序排序，并且请注意输出结果里面dept_no列是最后一列)

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`));---------------------------------------------------------------------------------------------CREATE TABLE `dept_manager` (`dept_no` char(4) NOT NULL, `emp_no` int(11) NOT NULL,`to_date` date NOT NULL, PRIMARY KEY (`emp_no`,`dept_no`));

# 代码
select s.*, d.dept_no 
from salaries as s,dept_manager as d
where s.to_date="9999-01-01" and d.to_date="9999-01-01" and d.emp_no = s.emp_no
order by s.emp_no;

多个where子句间用and连接

---

题目04

查找所有已经分配部门的员工的 last_name 和 first_name 以及 dept_no (请注意输出描述里各个列的前后顺序)

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`));------------------------------------------------------------------------------------------------CREATE TABLE `employees` (`emp_no` int(11) NOT NULL, `birth_date` date NOT NULL, `first_name` varchar(14) NOT NULL,`last_name` varchar(16) NOT NULL, `gender` char(1) NOT NULL, `hire_date` date NOT NULL,PRIMARY KEY (`emp_no`));

# 代码
select employees.last_name, employees.first_name, dept_emp.dept_no from employees, dept_emp 
where dept_emp.emp_no = employees.emp_no;

多表联合查询

---

题目05

根据题04的表, 查找所有员工的 last_name 和 first_name 以及对应部门编号 dept_no，也包括暂时没有分配具体部门的员工
(请注意输出描述里各个列的前后顺序)

# 代码
select employees.last_name, employees.first_name, dept_emp.dept_no from employees 
left join dept_emp on dept_emp.emp_no = employees.emp_no;

左连接查询

---

题目06

查找所有员工入职时候的薪水情况，给出 emp_no 以及 salary， 并按照emp_no进行逆序
(请注意，一个员工可能有多次涨薪的情况)

CREATE TABLE `employees` (`emp_no` int(11) NOT NULL, `birth_date` date NOT NULL,`first_name` varchar(14) NOT NULL, `last_name` varchar(16) NOT NULL,`gender` char(1) NOT NULL, `hire_date` date NOT NULL, PRIMARY KEY (`emp_no`));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`));

# 代码1: 用inner join
SELECT e.emp_no, s.salary FROM employees AS e INNER JOIN salaries AS s
ON e.emp_no = s.emp_no AND e.hire_date = s.from_date ORDER BY e.emp_no DESC# 代码2: 用多表查询
SELECT e.emp_no, s.salary FROM employees AS e, salaries AS s
WHERE e.emp_no = s.emp_no AND e.hire_date = s.from_date ORDER BY e.emp_no DESC

注意点:

1. 由于测试数据中，salaries.emp_no不唯一（因为号码为emp_no的员工会有多次涨薪的可能，所以在salaries中对应的记录不止一条），employees.emp_no唯一，即salaries的数据会多于employees，因此需先找到employees.emp_no在salaries表中对应的记录salaries.emp_no，则有限制条件e.emp_no = s.emp_no
2. 根据题意注意到salaries.from_date 和employees.hire_date的值应该要相等, ( 入职时的工资 ), 因此有限制条件 e.hire_date = s.from_date
3. 根据题意要按照emp_no值逆序排列，因此最后要加上 ORDER BY e.emp_no DESC

---

题目07

查找薪水变动超过15次的员工号emp_no以及其对应的变动次数t

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`));

# 代码
select emp_no, count(emp_no) from salaries group by emp_no having count(emp_no)>15; 

知识点: 聚合函数, 聚合分组, 以及 聚合筛选

---

题目08

根据题目07的表, 找出所有员工当前 (to_date=‘9999-01-01’) 具体的薪水 salary 情况，对于相同的薪水只显示一次, 并按照逆序显示

# 代码1: 用distinct关键字
select distinct salary from salaries where to_date='9999-01-01' order by salary desc;# 代码2: 用聚合分组
select salary from salaries where to_date='9999-01-01' group by salary order by salary desc

去重语句 DISTINCT >> 不显示字段重复值

---

题目09

获取所有部门当前 (dept_manager.to_date=‘9999-01-01’) manager 的当前 (salaries.to_date=‘9999-01-01’) 薪水情况，
给出 dept_no, emp_no 以及 salary (请注意，同一个人可能有多条薪水情况记录)

CREATE TABLE `dept_manager` (`dept_no` char(4) NOT NULL, `emp_no` int(11) NOT NULL,`from_date` date NOT NULL, `to_date` date NOT NULL, PRIMARY KEY (`emp_no`,`dept_no`));-------------------------------------------------------------------------------------------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`));

# 代码
select d.dept_no, d.emp_no, s.salary from dept_manager as d, salaries as s
where d.emp_no=s.emp_no and d.to_date='9999-01-01' and s.to_date='9999-01-01'

用 d.emp_no=s.emp_no 来避免对同一个manager的多次选择

---

题目10

获取所有非manager的员工emp_no

CREATE TABLE `dept_manager` (`dept_no` char(4) NOT NULL, `emp_no` int(11) NOT NULL,`from_date` date NOT NULL, `to_date` date NOT NULL,PRIMARY KEY (`emp_no`,`dept_no`));--------------------------------------------------------------------CREATE TABLE `employees` (`emp_no` int(11) NOT NULL, `birth_date` date NOT NULL, `first_name` varchar(14) NOT NULL, `last_name` varchar(16) NOT NULL, `gender` char(1) NOT NULL, `hire_date` date NOT NULL, PRIMARY KEY (`emp_no`));

# 代码1: 用not in与子查询
select emp_no from employees where emp_no not in (select emp_no from dept_manager);

注意: 子查询加括号

# 代码2: 用left join
select emp_no from 
(select * from employees left join dept_manager on dept_manager.emp_no = employees.emp_no)
where dept_no is null;

思路: 将两张表合并所有数据, 从新表中找倒dept_no为null的数据

---

题目11

获取所有员工当前的 (dept_manager.to_date=‘9999-01-01’) manager，如果员工是 manager 的话不显示(也就是如果当前的 manager是自己的话结果不显示) 输出结果第一列给出当前员工的 emp_no, 第二列给出其 manager 对应的 emp_no

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`));--------------------------------------------------------------------------------CREATE TABLE `dept_manager` (`dept_no` char(4) NOT NULL, `emp_no` int(11) NOT NULL, `from_date` date NOT NULL, `to_date` date NOT NULL,PRIMARY KEY (`emp_no`,`dept_no`));

# 代码
select dept_emp.emp_no, dept_manager.emp_no as manager 
from dept_emp inner join dept_manager on dept_emp.dept_no=dept_manager.dept_no
where dept_manager.to_date='9999-01-01' and dept_emp.emp_no!=dept_manager.emp_no;

分析:

• 员工与自己的manager所在的部门是一样的 >> dept_emp.dept_no=dept_manager.dept_no
• 员工如果是manager则不显示manager >> dept_emp.emp_no!=dept_manager.emp_no

on和where的区别：

• on后面的是连接条件，代表两个表建立关系所遵循的规则
• where后面的可以看作是筛选条件，是对最终结果集进行过滤所遵循的规则

---

题目12

获取所有部门中当前 (dept_emp.to_date = ‘9999-01-01’) 员工当前 (salaries.to_date=‘9999-01-01’) 薪水最高的相关信息，
给出 dept_no, emp_no 以及其对应的 salary

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`));--------------------------------------------------------------------------------------------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`));

# 代码
select dept_emp.dept_no, dept_emp.emp_no, salaries.salary 
from salaries, dept_emp where dept_emp.emp_no= salaries.emp_no and dept_emp.to_date = '9999-01-01' and salaries.to_date='9999-01-01' group by dept_emp.dept_no having max(salaries.salary)

having与where的区别:

• having是在分组后对数据进行过滤
• where是在分组前对数据进行过滤
• having后面可以使用聚合函数
• where后面不可以使用聚合

---

题目13

从 titles 表获取按照 title 进行分组，每组个数大于等于 2，给出 title 以及对应的数目 t。

CREATE TABLE IF NOT EXISTS "titles" (`emp_no` int(11) NOT NULL, `title` varchar(50) NOT NULL,`from_date` date NOT NULL, `to_date` date DEFAULT NULL);

# 代码
select title, count(title) as t from titles group by title having t >= 2;

聚合函数

---

题目14

从 题目13的 titles 表中获取, 按照 title 进行分组，每组个数大于等于2，给出 title 以及对应的数目t。
注意! 对于重复的 emp_no 进行忽略 (即 emp_no 重复的 title 不计算，title 对应的数目t不增加)。

# 代码
select title, count(distinct emp_no) from titles group by title;

如何忽略emp_no重复的title?

• 用COUNT(DISTINCT emp_no)可以统计同一title值且不包含重复emp_no值的记录条数

---

题目15

查找 employees 表所有 emp_no 为奇数，且 last_name 不为 Mary (注意大小写) 的员工信息，并按照 hire_date 逆序排列
(题目不能使用mod函数)

CREATE TABLE `employees` ( `emp_no` int(11) NOT NULL, `birth_date` date NOT NULL, `first_name` varchar(14) NOT NULL, `last_name` varchar(16) NOT NULL,`gender` char(1) NOT NULL, `hire_date` date NOT NULL, PRIMARY KEY (`emp_no`));

# 代码
select * from employees where emp_no%2=1 and last_name<>'Mary' order by hire_date desc;

---

14.2 十六到三十

题目16

统计出当前 (titles.to_date=‘9999-01-01’) 各个 title 类型对应的员工当前 (salaries.to_date=‘9999-01-01’) 薪水对应的平均工资。
结果给出 title 以及平均工资 avg

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`));-------------------------------------------------------------------------------------------CREATE TABLE IF NOT EXISTS "titles" (`emp_no` int(11) NOT NULL, `title` varchar(50) NOT NULL,`from_date` date NOT NULL, `to_date` date DEFAULT NULL);

# 代码
select titles.title, avg(salaries.salary) from titles, salaries 
where titles.emp_no=salaries.emp_no and titles.to_date='9999-01-01' and salaries.to_date='9999-01-01'
group by titles.title;

---

题目17

查看题目16的salaries表, 获取当前（to_date=‘9999-01-01’）薪水第二多的员工的 emp_no 以及其对应的薪水 salary

# 代码
select emp_no, salary from salaries order by salary desc limit 1, 1;

---

题目18

查找当前薪水 (to_date=‘9999-01-01’) 排名第二多的员工编号 emp_no、薪水 salary、last_name 以及 first_name
(尽量不使用order by)

CREATE TABLE `employees` (`emp_no` int(11) NOT NULL, `birth_date` date NOT NULL,`first_name` varchar(14) NOT NULL, `last_name` varchar(16) NOT NULL,`gender` char(1) NOT NULL, `hire_date` date NOT NULL, PRIMARY KEY (`emp_no`));-------------------------------------------------------------------------------------------------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`));

# 代码1: 使用order by
select e.emp_no, s.salary, e.last_name, e.first_name from employees e, salaries s
where s.to_date='9999-01-01' and s.emp_no=e.emp_no order by s.salary desc limit 1, 1;

# 代码2: 不使用order by
select e.emp_no, max(s.salary), e.last_name, e.first_name from employees e, salaries s
where s.to_date='9999-01-01' and s.emp_no=e.emp_no 
and s.salary not in (select max(salary) from salaries); 

思路:

• 不能使用order by找第二大的salary
• 找到salary的最大值, 用max()聚合函数, 然后将最大值排除掉
• 再用max()找到的就是salary第二大的

---

题目19

查找所有员工的 last_name 和 first_name 以及对应的 dept_name，也包括暂时没有分配部门的员工

CREATE TABLE `departments` (`dept_no` char(4) NOT NULL, `dept_name` varchar(40) NOT NULL,PRIMARY KEY (`dept_no`));----------------------------------------------------------------------------------------------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`));----------------------------------------------------------------------------------------------CREATE TABLE `employees` (`emp_no` int(11) NOT NULL, `birth_date` date NOT NULL, `first_name` varchar(14) NOT NULL, `last_name` varchar(16) NOT NULL,`gender` char(1) NOT NULL, `hire_date` date NOT NULL, PRIMARY KEY (`emp_no`));

# 代码
select e.last_name, e.first_name, d.dept_name 
from employees e left join dept_emp de on e.emp_no=de.emp_no 
left join departments d on d.dept_no=de.dept_no;

这是一个多对多的表关联查询, 需要用到两次…join…on…

---

题目20

查找员工编号emp_no为10001其自入职以来的薪水salary涨幅(总共涨了多少)growth(可能有多次涨薪，没有降薪)

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`));

# 代码
select max(salary) - min(salary) from salaaries where emp_no=10001;

在select后面可以使用算术运算符

---

题目21

查找所有员工自入职以来的薪水涨幅情况，给出员工编号 emp_no 以及其对应的薪水涨幅 growth，并按照 growth 进行升序
注: 可能有 employees 表和 salaries 表里存在记录的员工，有对应的员工编号和涨薪记录，但是已经离职了
离职的员工 salaries 表的最新的 to_date!=‘9999-01-01’，这样的数据不显示在查找结果里面

CREATE TABLE `employees` (`emp_no` int(11) NOT NULL, `birth_date` date NOT NULL, `first_name` varchar(14) NOT NULL, `last_name` varchar(16) NOT NULL, `gender` char(1) NOT NULL, `hire_date` date NOT NULL, PRIMARY KEY (`emp_no`));------------------------------------------------------------------------------------------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`));

# 代码1: 用inner join语句
select e.emp_no, (s.salary - sa.salary) as growth
from employees as e inner join salaries as s
on e.emp_no = s.emp_no and s.to_date = '9999-01-01'
inner join salaries as sa
on e.emp_no = sa.emp_no and e.hire_date = sa.from_date
order by growth asc

代码1思路:

• 员工的工资有涨有跌, 不能简单的用 max(salary)-min(salary) 来计算入职时的工资与现在的工资差

  • 例: 工资浮动为:1200,1000,1300, 那么入职到现在的工资浮动应为100, 而不是300

• 需要找到入职时的工资 (employees.hire_date=salaries.from_date), 与现在的工资 (salaries.to_date=‘9999-01-01’)

• 用inner join提取员工现在的工资列表

  select e.emp_no, s.salary from employees as e inner join salaries as s
  on e.emp_no = s.emp_no and s.to_date = '9999-01-01'
  

• 用inner join提取员工入职时的工资列表

  select e.emp_no, sa.salary from employees as e inner join salaries as sa
  on e.emp_no = s.emp_no and e.hire_date = sa.from_date
  

• 结合两段代码, 提取工资差

# 代码2: 用子查询方法
select s1.emp_no, (salary - old_salary) as growth from 
(select emp_no ,salary from salaries where to_date='9999-01-01') as s1 
inner join 
(select e.emp_no , salary as old_salary from employees as e left join salaries as s on e.emp_no = s.emp_no and e.hire_date = s.from_date) as s2 
on s1.emp_no = s2.emp_no order by growth

代码2思路:

• 整体思路与代码1相同

• 做一个s1表, 找到salaries表中所有员工现在的工资

  select emp_no ,salary from salaries where to_date='9999-01-01'
  

• 做一个s2表, 找到所有员工入职时的工资

  select e.emp_no , salary as old_salary from employees as e 
  left join salaries as s on e.emp_no = s.emp_no and e.hire_date = s.from_date
  

• 从两张表中计算出工资差

---

题目22

统计各个部门的工资记录数，给出部门编码 dept_no、部门名称 dept_name 以及部门在 salaries 表里面有多少条记录 sum

CREATE TABLE `departments` (`dept_no` char(4) NOT NULL, `dept_name` varchar(40) NOT NULL,PRIMARY KEY (`dept_no`)); ---------------------------------------------------------------------------------------------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`));---------------------------------------------------------------------------------------------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`));

思路:

• departments 表与 salaries 表没有直接联系, 有第三方表 dept_emp 做两表的关联 (有dept_no和emp_no字段)

• 主表是departments, 需要做一张副表, 含有 dept_no 和 salaries信息

  select d.dept_no, count(s.salary) as sum from dept_emp d, salaries s 
  where d.emp_no=s.emp_no group by d.dept_no;
  

• 用departments 去 left join 自创的副表

# 代码: 多表联合查询 & 子查询
select de.dept_no, de.dept_name, s1.sum from departments as de left join
(select d.dept_no, count(s.salary) as sum from dept_emp d, salaries s where d.emp_no=s.emp_no group by d.dept_no) as s1 on de.dept_no=s1.dept_no;

---

题目23

对所有员工的当前 (to_date=‘9999-01-01’) 薪水按照 salary 进行降序排名 (名次: 1~N)，相同的 salary 排名并列且按照 emp_no 升序排列

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`));

难点:

• 如何显示排名的名次?
  • 思路: 找到比该salary数据大的salary数量, 例: 大于等于salary=1000的salary数量为1, 那1000的salary就是排名为1

# 代码:
select s1.emp_no, s1.salary, count(distinct s2.salary) as rank
from salaries as s1, salaries as s2 
where s1.to_date='9999-01-01' and s2.to_date='9999-01-01' and s1.salary<=s2.salary
group by s1.emp_no
order by s1.salary desc, s1.emp_no asc

---

题目24

获取所有非 manager 员工当前的薪水情况，给出 dept_no、emp_no 以及 salary ，当前表示 to_date=‘9999-01-01’

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`));-------------------------------------------------------------------------------------------CREATE TABLE `dept_manager` (`dept_no` char(4) NOT NULL, `emp_no` int(11) NOT NULL, `from_date` date NOT NULL, `to_date` date NOT NULL, PRIMARY KEY (`emp_no`,`dept_no`));-------------------------------------------------------------------------------------------CREATE TABLE `employees` (`emp_no` int(11) NOT NULL, `birth_date` date NOT NULL,`first_name` varchar(14) NOT NULL, `last_name` varchar(16) NOT NULL,`gender` char(1) NOT NULL, `hire_date` date NOT NULL,PRIMARY KEY (`emp_no`));-------------------------------------------------------------------------------------------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`));

思路:

• emp_no 和 salary 的数据可以直接从 salaries 表中获得, 难点在于区分那个 emp_no 是manager 以及是属于哪个dept_no
• 判断emp_no是否是manager:
  • 如果emp_no出现在了dept_manager表中, 那么他是manager
  • 所以我们需要的数据应该是: emp_no not in (select emp_no from dept_manager)
• 判断emp_no属于哪个dept_no
  • 在dept_emp表中有emp_no与dept_no的关系
  • 可以通过: select dept_no, emp_no from dept_emp where to_date=‘9999-01-01’ 来找到emp_no对应的dept_no

# 代码
select de.dept_no, de.emp_no, s.salary from dept_emp de, salaries s 
where de.to_date='9999-01-01' and s.to_date='9999-01-01' and s.emp_no=de.emp_no
and de.emp_no not in (select emp_no from dept_manager);

---

题目25

获取员工其当前的薪水比其 manager 当前薪水还高的相关信息，当前表示 to_date=‘9999-01-01’
结果第一列给出员工的 emp_no，第二列给出其 manager 的 manager_no，第三列给出该员工当前的薪水 emp_salary , 第四列给该员工对应的 manager 当前的薪水 manager_salary

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`));-------------------------------------------------------------------------------------------CREATE TABLE `dept_manager` (`dept_no` char(4) NOT NULL, `emp_no` int(11) NOT NULL, `from_date` date NOT NULL, `to_date` date NOT NULL, PRIMARY KEY (`emp_no`,`dept_no`));-------------------------------------------------------------------------------------------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`));

思路:

• 建立2张表

  • 表1显示普通员工工资: (题目24的答案)
  • 表2显示manager工资: (将题目24答案中的 not in, 改为 in)

• 基于dept_no字段信息将两表中的信息进行比较

  select t1.emp_no, t2.emp_no as manager_no, t1.salary as emp_salary, t2.salary as manager_salary 
  from 表1 as t1, 表2 as t2 
  where t1.salary>t2.salary and t1.dep_no=t2.dep_no
  

# 代码1
select t1.emp_no, t2.emp_no as manager_no, t1.salary as emp_salary, t2.salary as manager_salary 
from 
(select de.dept_no, de.emp_no, s.salary from dept_emp de, salaries s where de.to_date='9999-01-01' and s.to_date='9999-01-01' and s.emp_no=de.emp_noand de.emp_no not in (select emp_no from dept_manager))
as t1, 
(select de.dept_no, de.emp_no, s.salary from dept_emp de, salaries s where de.to_date='9999-01-01' and s.to_date='9999-01-01' and s.emp_no=de.emp_noand de.emp_no in (select emp_no from dept_manager)) 
as t2 
where t1.salary>t2.salary and t1.dept_no=t2.dept_no

转换思路:

• 无需另建表格

# 代码2
select d.emp_no, dm.emp_no as manager_no, s.salary as emp_salary, ss.salary as manager_salary 
from dept_emp d, dept_manager dm, salaries s, salaries ss
where s.emp_no = d.emp_no and ss.emp_no = dm.emp_no and s.to_date='9999-01-01' and ss.to_date='9999-01-01' and s.salary>ss.salary and d.dept_no = dm.dept_no 

---

题目26

汇总各个部门当前员工的 title 类型的分配数目，即结果给出部门编号 dept_no、dept_name、其部门下所有的当前(dept_emp.to_date = ‘9999-01-01’)员工的当前(titles.to_date = ‘9999-01-01’) title以及该类型 title 对应的数目 count

(注：因为员工可能有离职，所有dept_emp里面to_date不为’9999-01-01’就已经离职了，不计入统计，而且员工可能有晋升，所以如果titles.to_date 不为 ‘9999-01-01’，那么这个可能是员工之前的职位信息，也不计入统计)

CREATE TABLE `departments` (`dept_no` char(4) NOT NULL, `dept_name` varchar(40) NOT NULL,PRIMARY KEY (`dept_no`));-------------------------------------------------------------------------------------------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`));-------------------------------------------------------------------------------------------CREATE TABLE IF NOT EXISTS `titles` (`emp_no` int(11) NOT NULL, `title` varchar(50) NOT NULL,`from_date` date NOT NULL, `to_date` date DEFAULT NULL);

思路:

• dept_no 与 dept_name 可在departments表中获取,
  • 条件: departments.dept_no = dept_emp.dept_no and dept_emp.to_date = ‘9999-01-01’
• title 与count(title)数据可在 titles 表中获取
  • 条件titles.to_date = ‘9999-01-01’ and titles.emp_no=dept_emp.wmp_no

# 代码2
select b.dept_no, b.dept_name, c.title, count(title)
from dept_emp as a, departments as b, titles as c
where a.dept_no = b.dept_noand a.emp_no = c.emp_noand a.to_date = '9999-01-01'and c.to_date = '9999-01-01'
group by a.dept_no, c.title;

group by 后可以跟随多个分组条件

---

题目 27

给出每个员工每年薪水涨幅超过5000的员工编号 emp_no、薪水变更开始日期 from_date 以及薪水涨幅值 salary_growth，并按照salary_growth 逆序排列。
提示：在 sqlite 中获取 datetime 时间对应的年份函数为 strftime(’%Y’, to_date)
(数据保证每个员工的每条薪水记录 to_date-from_date=1年，而且同一员工的下一条薪水记录 from_data=上一条薪水记录的to_data)

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`));

思路:

• 注意: 是每年涨幅超过5000的员工, 不是总涨幅超过5000的员工
  如何在数据上理解每年的涨幅 >>> 两个from_date或两个to_date相差为1

# 代码
select s1.emp_no,s1.from_date,(s1.salary-s2.salary) as salary_growth
from salaries as s1,salaries as s2
where s1.emp_no = s2.emp_no and salary_growth>5000and ((strftime('%Y',s1.from_date)-strftime('%Y',s2.from_date)=1) or (strftime('%Y',s1.to_date)-strftime('%Y',s2.to_date)=1))
order by salary_growth desc

问题!! mysql使用strftime()函数报错

---

题目 28

查找描述信息 (film.description) 中包含 robot 的电影对应的分类名称 (category.name) 以及电影数目 (count(film.film_id))，而且还需要该分类包含电影总数量 (count(film_category.category_id))>=5 部

CREATE TABLE IF NOT EXISTS film (`film_id` smallint(5) NOT NULL DEFAULT '0', `title` varchar(255) NOT NULL, `description` text, PRIMARY KEY (film_id));----------------------------------------------------------------------------------------CREATE TABLE category (`category_id` tinyint(3) NOT NULL, `name` varchar(25) NOT NULL,`last_update` timestamp, PRIMARY KEY ( category_id ));---------------------------------------------------------------------------------------CREATE TABLE film_category (`film_id`  smallint(5) NOT NULL, `category_id` tinyint(3) NOT NULL, `last_update` timestamp);

思路:

• 理解题目:

  • 需要输出两个字段信息: 电影类别名称 & 该电影类别对应电影的描述中含有’robot’的电影数量
  • 其中, category.name 对应的电影的description中必须包含 “robot”
  • 并且, 该电影类别对应的电影总数量大于等于5部

• 观察表格:

  • film表, category表, film_category表
  • 多对多关系, 一部电影可以属于多种电影类别, 一个电影类别下也可以有多部电影

• 提取数据

  • 需要从category提取name字段信息, 以及film中提取到相应的电影数量count(film_id)

    • 涉及到3张表, 需要满足
      category.category_id=film_category.category_id and film_category.film_id=film.film_id

  • select语句中带有聚合函数, 因此必须使用聚合分组:

    • group by category.name

  • 需要满足条件1: description中包含 'roboot’字符串

    • like模糊查询: film.description like ‘%robot%’

  • 需要满足条件2: 电影类别下的电影数量大于等于5

    • 创建一个新表, 内有满足该条件的数据

    (select category.category_id, category.namefrom category, film, film_category where category.category_id=film_category.category_id and film_category.film_id=film.film_id group by category.category_idhaving count(film.film_id)>=5
    ) as t1
    

• 代码思路:

  • 从t1表中获取类别名称, 再从film中找到该类别对应的电影, 并且描述中含有’robot’的电影数量

# 代码1:
select t1.name, count(film.film_id)
from(select category.category_id, category.namefrom category, film, film_category where category.category_id=film_category.category_id and film_category.film_id=film.film_id group by category.category_idhaving count(film.film_id)>=5) as t1,film, film_category
wherefilm.description like '%robot%'and film_category.category_id=t1.category_idand film_category.film_id=film.film_id
group by t1.name

其他方式

# 代码2:
select c.name,count(*) as name 
from film_category fc,category c,film f,(select category_id from film_category group by film_category.category_id having count(*)>=5) as cc
where f.description like'%robot%'and fc.category_id=c.category_idand fc.category_id = cc.category_idand fc.film_id=f.film_id group by fc.category_id;

---

题目 29

使用 join 查询方式找出没有分类的电影 id 以及名称

CREATE TABLE IF NOT EXISTS film (`film_id` smallint(5) NOT NULL DEFAULT '0', `title` varchar(255) NOT NULL, `description` text, PRIMARY KEY (film_id));----------------------------------------------------------------------------------------CREATE TABLE category (`category_id` tinyint(3) NOT NULL, `name` varchar(25) NOT NULL,`last_update` timestamp, PRIMARY KEY ( category_id ));---------------------------------------------------------------------------------------CREATE TABLE film_category (`film_id`  smallint(5) NOT NULL, `category_id` tinyint(3) NOT NULL, `last_update` timestamp);

思路:

• 注意! 必须用join查询方式 (inner join, left join, right join)
• 需要的字段信息: film_id & title 都可以从film表中提取, 但是需要满足条件
• 用left join: film为左表, film_category为右表, 保留左表所有数据,将右表加入左表, 查看那些右表字段为null的数据

# 代码
select film.film_id, film.title from film left join film_category 
on film.film_id=film_category.film_id 
where film_category.category_id is null;

---

题目 30

用子查询的方式找出属于 Action 分类的所有电影对应的 title , description

CREATE TABLE IF NOT EXISTS film (`film_id` smallint(5) NOT NULL DEFAULT '0', `title` varchar(255) NOT NULL, `description` text, PRIMARY KEY (film_id));----------------------------------------------------------------------------------------CREATE TABLE category (`category_id` tinyint(3) NOT NULL, `name` varchar(25) NOT NULL,`last_update` timestamp, PRIMARY KEY ( category_id ));---------------------------------------------------------------------------------------CREATE TABLE film_category (`film_id`  smallint(5) NOT NULL, `category_id` tinyint(3) NOT NULL, `last_update` timestamp);

思路:

• 需要拿到 title & description 字段, 都可以从 film 中取得, 只需要满足该电影对应的category是Action
• 回顾子查询的定义:
  • 当一个select语句中包含另一个select 查询语句，则称之为有子查询的语句
  • 可以出现在select后, from之后, 或 where后
• 子查询, 找到Action类型对应的category_id
  • select category_id from category where name=‘Action’

select film.title, film.description from film, film_category 
where film.film_id=film_category.film_id and film_category.category_id in 
(select category_id from category where name='Action')

---

14.3 三一到四五

题目 31

获取select * from employees对应的执行计划

explain select * from employees

使用 EXPLAIN 关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的: 看看这些SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描等等。这可以帮你分析你的查询语句或是表结构的性能瓶颈

---

题目 32

将employees表的所有员工的last_name和first_name拼接起来作为Name，中间以一个空格区分
(注：该数据库系统是sqllite,字符串拼接为 || 符号，不支持concat函数)

CREATE TABLE `employees` (`emp_no` int(11) NOT NULL, `birth_date` date NOT NULL, `first_name` varchar(14) NOT NULL, `last_name` varchar(16) NOT NULL,`gender` char(1) NOT NULL,`hire_date` date NOT NULL,PRIMARY KEY (`emp_no`));

mysql中的concat()函数支持字符串的拼接

# mysql代码:
select concat(last_name, ' ', first_name) from employees

SQLite字符串的连接需要用||

# sqlite代码
select last_name||" "||first_name as Name from employees;

---

题目 33

创建一个actor表，包含如下列信息
(注: sqlite 获取系统默认时间是 datetime(‘now’,‘localtime’) )

列表	类型	是否为NULL	含义
actor_id	smallint(5)	not null	主键id
first_name	varchar(45)	not null	名字
last_name	varchar(45)	not null	姓氏
last_update	timestamp	not null	最后更新时间，默认是系统的当前时间

# mysql代码
create table actor (actor_id smallint(5) auto_increment not null, first_name varchar(45) not null, last_name varchar(45) not null,last_update timestamp default CURRENT_TIMESTAMP not null, PRIMARY KEY (actor_id)) CHARSET=utf8;

# sqlite代码
create table 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')));

在sqlite下设置 CHARSET=utf8 会报错

---

题目 34

对在题目33中创建的actor表批量插入如下数据 (不能有2条insert语句哦!)

actor_id	first_name	last_name	last_update
1	PENELOPE	GUINESS	2006-02-15 12:34:33
2	NICK	WAHLBERG	2006-02-15 12:34:33

insert into actor values (1, 'PENELOPE', 'GUINESS', '2006-02-15 12:34:33'), (2, 'NICK', 'WAHLBERG', '2006-02-15 12:34:33');

---

题目 35

对在题目33中创建的actor表, 批量插入如下数据, 如果数据已经存在，请忽略 (不支持使用replace操作)

actor_id	first_name	last_name	last_update
‘3’	‘ED’	‘CHASE’	‘2006-02-15 12:34:33’

# mysql代码
INSERT IGNORE INTO actor VALUES(3, 'ED', 'CHASE', '2006-02-15 12:34:33');

# sqlite代码
INSERT or IGNORE INTO actor VALUES(3, 'ED', 'CHASE', '2006-02-15 12:34:33');

MySQL中的特殊插入模式:

• replace into: 如果插入的数据会导致UNIQUE 索引或PRIMARY KEY发生冲突/重复，则先删除旧数据再插入最新的数据
• insert ignore into: 如果插入的数据会导致UNIQUE索引或PRIMARY KEY发生冲突/重复，则忽略此次操作/不插入数据

---

题目 36

对在题目33中创建的actor表, 有如下数据:

actor_id	first_name	last_name	last_update
1	PENELOPE	GUINESS	2006-02-15 12:34:33
2	NICK	WAHLBERG	2006-02-15 12:34:33

请你创建一个actor_name表，并且将actor表中的所有first_name以及last_name导入该表.

actor_name表结构如下：

列表	类型	是否为NULL	含义
first_name	varchar(45)	not null	名字
last_name	varchar(45)	not null	姓氏

# 创建表
create table actor_name (first_name varchar(45) not null, last_name varchar(45) not null) CHARSET=utf8;

# 插入数据
insert into actor_name values ('PENELOPE', 'GUINESS'), ('NICK', 'WAHLBERG')# 高级写法
insert into actor_name select first_name,last_name from actor;

---

题目 37

针对题目33中创建的actor表结构创建索引：
(注: 在 SQLite 中, 除了重命名表和在已有的表中添加列, ALTER TABLE 命令不支持其他操作)

对 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);

---

题目 38

针对题目33中创建的actor表创建视图 actor_name_view，只包含 first_name 以及 last_name 两列，
并对这两列重新命名，first_name 修改为 first_name_v，last_name 修改为 last_name_v：

什么是视图:

• 视图是存储的查询语句, 当调用的时候产生结果集, 视图充当的是虚拟表的角色。

• 其实视图可以理解为一个表或多个表中导出来的表，作用和真实表一样，包含一系列带有行和列的数据

创建视图的语法

• CREATE VIEW 视图名 AS 完整的SELECT语句;

# 代码
CREATE VIEW actor_name_view AS 
select first_name as first_name_v, last_name as last_name_v from actor;

---

题目 39

针对 salaries 表 emp_no 字段创建索引 idx_emp_no，查询 emp_no 为10005 , 使用强制索引。

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`));---------------------------------------------------------------------------------------------create index idx_emp_no on salaries(emp_no);

mysql强制使用索引:force index(索引名或者主键PRI)

# MySQL代码: 该代码在MySQL中可以正常运行, 但是无法通过牛客网的监测
select * from salaries force index(idx_emp_no) where emp_no=10005;

# sqlite代码: 该代码无法在Mysql运行会报错, 但是可以通过牛客网的检查
SELECT * FROM salaries INDEXED BY idx_emp_no WHERE emp_no = 10005;

---

题目 40

存在 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')));

现在在 last_update 后面新增加一列名字为 create_date , 类型为 datetime , NOT NULL ，默认值为 ‘0000-00-00 00:00:00’

添加字段的基本语法:

• alter table 表名 add 字段名 数据类型 after 字段名;

# 代码
alter table actor add create_date datetime NOT NULL default '0000-00-00 00:00:00';

注意!

• 该代码可以通过牛客网审核, 但是在mySQL运行时会报错

• 原因: MySQL的配置文件中有如下配置:

  • ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, 
    ERROR_FOR_DIVISION_BY_ZERO, and NO_ENGINE_SUBSTITUTIN
    

  • 其中NO_ZERO_IN_DATE, NO_ZERO_DATE两个选项禁止了0000这样的日期和时间

• 解决方法: 使用 SET [SESSION|GLOBAL] sql_mode='modes’语法重新设置sql_mode值

  • set global sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
    

  • SESSION(默认选项): 表示当前回话中生效; GLOBAL**(需重启MySql)**: 表示全局生效

  • 重启MySQL

---

题目 41

构造一个触发器 audit_log，在向 employees_test 表中插入一条数据的时候，触发插入相关的数据到 audit 中。

CREATE TABLE employees_test(ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL,AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL);--------------------------------------------------------------------------------------------CREATE TABLE audit(EMP_no INT NOT NULL, NAME TEXT NOT NULL);

触发器是与 MySQL 数据表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。

基本语法:

delimiter $$
CREATE <触发器名> < BEFORE 或 AFTER >
<INSERT 或 UPDATE 或 DELETE >
ON <表名> FOR EACH Row
begin
<触发器主体>;
end $$
delimiter ;

# 在mysql中应该使用的代码:
delimiter $$
CREATE TRIGGER audit_log
AFTER INSERT 
ON employees_test FOR EACH ROW
BEGININSERT INTO audit(EMP_no, NAME) VALUES (new.ID, new.NAME);
END $$
delimiter ;

# 在牛客网中使用的代码
CREATE TRIGGER audit_log
AFTER INSERT 
ON employees_test FOR EACH ROW
BEGININSERT INTO audit(EMP_no, NAME) VALUES (new.ID, new.NAME);
END ;

---

题目 42

在 titles_test 表中 删除 emp_no 重复的记录，只保留最小的id对应的记录。

CREATE TABLE IF NOT EXISTS titles_test (id int(11) not null primary key, emp_no int(11) NOT NULL,title varchar(50) NOT NULL, from_date date NOT NULL, to_date date DEFAULT NULL);

思路:

• 题目目的:

• 在titles_test中有emp_no重复的记录, 但是 id 不同; 将重复的emp_no记录删除, 只保留 id 最小的那个

• 删除重复的emp_no, 只保留最小id的那个emp_no

  • 删除数据的语法: delete from titles_test where 条件语句;
  • 难点在于条件语句怎么写

• 条件1: 找到哪些emp_no是重复的

  • 通过count(emp_no)可以发现哪些emp_no是重复的

    select emp_no from titles_test group by emp_no having count(emp_no)>1
    

• 条件2: 保留 id 最小的

  • 可以使用min(id)的方式, 从那些有重复的emp_no集合中找到

    select min(id) from titles_test where emp_no in (select emp_no from titles_test group by emp_no having count(emp_no)>1) group by emp_no
    

• 将根据 条件1, 条件2 选取出要的结果

  delete from titles_test where emp_no in 条件1 and id not in 条件2
  

上述思路原理上可行, 但是子选择语句过多, 太过繁琐, 转换思路:

• 实际上只需要用min(id) 再 group by emp_no 即可取出所有不能被删除的数据

• group by emp_no 使每个emp_no都有, min(id) 使每个emp_no对应的最小id也有了

• SELECT MIN(id), emp_no FROM titles_test GROUP BY emp_no
  

• 再用deleate语句将所有不在上述数据中的数据删除即可

注意! MySQL 中有对 delete where in 语句的子查询限制

• # 例:
  delete from titles_test where emp_no in 
  (select emp_no from titles_test group by emp_no having count(emp_no)>1);
  

  上述代码会报错
`ERROR 1093 (HY000): You can't specify target table 'titles_test' for update in FROM clause`- 改为如下代码: - ```mysqldelete from titles_test where emp_no in (select n.emp_no from(select emp_no from titles_test group by emp_no having count(emp_no)>1)as n);

# 代码
delete from titles_test
where id not in 
(select n.id from (SELECT MIN(id) as id, emp_no FROM titles_test GROUP BY emp_no) 
as n);

---

题目 43

将所有 to_date 为 9999-01-01 的全部更新为 NULL, 且 from_date 更新为 2001-01-01

CREATE TABLE IF NOT EXISTS titles_test (id int(11) not null primary key, emp_no int(11) NOT NULL,title varchar(50) NOT NULL, from_date date NOT NULL, to_date date DEFAULT NULL);

更新表数据的基本语法

update 表名 set 字段1=值1,字段2=值2,... where 条件;

# 代码:
update titles_test 
SET to_date=NULL,from_date='2001-01-01'
where to_date='9999-01-01'

---

题目 44

将 id=5 以及 emp_no=10001 的行数据替换成 id=5 以及 emp_no=10005 ,其他数据保持不变，使用 replace 实现。

CREATE TABLE IF NOT EXISTS titles_test (id int(11) not null primary key, emp_no int(11) NOT NULL,title varchar(50) NOT NULL, from_date date NOT NULL, to_date date DEFAULT NULL);

MySQL中replace的两种用法:

1. replace into

   • 此语句的作用是向表插入数据时使用, 如果插入的数据会导致UNIQUE 索引或PRIMARY KEY发生冲突/重复，则先删除旧数据再插入最新的数据

2. replace(object, search, replace)

   • 此语句用于在object字段中找到search数据, 并替换成replace数据

   # 例：把表table中的name字段中的aa替换为bb
   update table set name=replace(name,'aa','bb')
   

# 代码:
update titles_test set emp_no=replace(emp_no, '10001', '10005') where id=5;

---

题目 45

将titles_test表名修改为titles_2017

CREATE TABLE IF NOT EXISTS titles_test (id int(11) not null primary key, emp_no int(11) NOT NULL,title varchar(50) NOT NULL, from_date date NOT NULL, to_date date DEFAULT NULL);

表重命名的基本语法

alter table 表名 rename to 新表名;

# 代码1: 在牛客网过不了审查, 但是MySQL可过
alter table titles_test rename titles_2017;

# 代码2: 牛客网可过, 并且MySQL也不报错
ALTER TABLE titles_test RENAME TO titles_2017;

---

14.4 四六到六十

题目 46

在audit表上创建外键约束，其emp_no对应employees_test表的主键id
(audit已经创建，需要先drop)
(注：创建表的时候，字段的顺序不要改变)

CREATE TABLE employees_test(ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL);-----------------------------------------------------------------------------------------CREATE TABLE audit(EMP_no INT NOT NULL, create_date datetime NOT NULL);

删除表的基本语法:

• drop table 表名;
  

创建外键约束的基本语法

• # 创建外键约束的语法
  CONSTRAINT 约束名 FOREIGN KEY 外键名 (外键字段名) REFERENCES 外表名 (要关联的外表字段名)# 可选参数1: 删除关联关系ON DELETE {RESTRICT 或 CASCADE 或 SET NULL 或 NO ACTION}# 可选参数2: 更新关联关系ON UPDATE {RESTRICT 或 CASCADE 或 SET NULL 或 NO ACTION}
  

# 删除原audit表
drop table audit;# 创建新的audit表 方法1: 并创建外键约束 (该方法牛客网无法通过, 但是MySQL正常)
CREATE TABLE audit(EMP_no INT NOT NULL, create_date datetime NOT NULL,FOREIGN KEY f_k (EMP_no) REFERENCES employees_test (ID));# 创建新的audit表 方法2: 该方法在MySQL与牛客网都正常
CREATE TABLE audit(EMP_no INT NOT NULL,create_date datetime NOT NULL,FOREIGN KEY(EMP_no) REFERENCES employees_test(ID));

---

题目 47

存在如下的视图:

create view emp_v as select * from employees where emp_no >10005;

如何获取 emp_v 和 employees 的相同数据？

CREATE TABLE `employees` (`emp_no` int(11) NOT NULL, `birth_date` date NOT NULL, `first_name` varchar(14) NOT NULL, `last_name` varchar(16) NOT NULL, `gender` char(1) NOT NULL, `hire_date` date NOT NULL, PRIMARY KEY (`emp_no`));

(你能不用 select * from employees where emp_no >10005完成吗，挑战一下自己对视图的理解吧)

什么是视图:

• 视图是存储的查询语句, 当调用的时候产生结果集, 视图充当的是虚拟表的角色
• 其实视图可以理解为一个表或多个表中导出来的表，作用和真实表一样，包含一系列带有行和列的数据
• 视图中，用户可以使用SELECT语句查询数据，也可以使用INSERT，UPDATE，DELETE修改记录

创建视图的语法:

{CREATE 或 REPLACE} VIEW 视图名 AS 完整的SELECT语句;
# REPLACE: 如果添加原来有同名视图的情况下会覆盖掉原有视图

不太了解题目的意义, 视图本身数据就是来自于目标, 因此视图中的所有数据都与原表相同

# 答案代码1: 牛客网可过, MySQL可过
select * from emp_v

# 答案代码2: 牛客网可过, 但是MySQL报错
select * from employees intersect select * from emp_v;

intersect为产生交集; 但是MySQL不支持INTERSECT操作符; 有inner join

# 答案代码2: 牛客网不可过, MySQL可过
select * from employees inner join emp_v where emp_v.emp_no=employees.emp_no;

---

题目 48

请你写出更新语句，将所有获取奖金的员工当前的 (salaries.to_date=‘9999-01-01’) 薪水增加 10%。
( emp_bonus 里面的 emp_no 都是当前获奖的所有员工 )

create table emp_bonus(emp_no int 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 表名 set 字段1=值1, 字段2=值2, ... where 条件;
    

• 获取所有当前获奖的员工

  • select emp_no from emp_bonus
    

# 代码
update salaries 
set salary=salary*1.1 
where emp_no in (select emp_no from emp_bonus)and to_date='9999-01-01';

---

题目 49

针对库中的所有表生成 select count(*) 对应的SQL语句，如数据库里有以下表，

employees; departments; dept_emp; dept_manage; salaries; titles; emp_bonus

(注:在 SQLite 中用 “||” 符号连接字符串，无法使用concat()函数)

MySQL:

• 获取数据库内的所有表名

  • select table_name from information_schema.tables where table_schema='数据库名'
    

• 合并字符串:

  • concat('字符串1', '字符串2', ...)
    

# MySQL: 牛客网不通过
SELECT concat('select count(*) from', ' ', TABLE_NAME, ';') 
AS cnts
FROM (select table_name from information_schema.tables where table_schema='test001')
AStbs;

sqlite:

• 在 SQLite 系统表 sqlite_master 中可以获得所有表的索引，其中字段 name 是所有表的名字，而且对于自己创建的表而言，字段 type 永远是 ‘table’
• 在 SQLite 中用 “||” 符号连接字符串

# sqlite: 牛客网可通过
SELECT "select count(*) from " || name || ";" 
AS cnts
FROM sqlite_master 
WHERE type = 'table'

---

题目 50

将 employees 表中的所有员工的 last_name 和 first_name 通过 ’ 连接起来。(不支持concat，请用 || 实现)

CREATE TABLE `employees` (`emp_no` int(11) NOT NULL, `birth_date` date NOT NULL, `first_name` varchar(14) NOT NULL,`last_name` varchar(16) NOT NULL,`gender` char(1) NOT NULL,`hire_date` date NOT NULL,PRIMARY KEY (`emp_no`));

# MySQL
SELECT concat(last_name, "'", first_name) 
AS name
FROMemployees;

# sqlite
SELECTlast_name || "'" || first_name
AS name
FROMemployees

---

题目 51

查找字符串 ‘10,A,B’ 中逗号 ‘,’ 出现的次数

思路:

• 获取 ‘10, A, B’ 的长度
• 然后将逗号替换为空
• 获取 ‘10AB’ 的长度
• 两个长度相减, 即是逗号的数量

注意:

• 空格也是一个字符

select length('10,A,B') - length(REPLACE('10,A,B',',',''))

---

题目 52

获取 Employees 中的 first_name ，按照 first_name 最后两个字母 进行 升序排列

CREATE TABLE `employees` (`emp_no` int(11) NOT NULL,`birth_date` date NOT NULL,`first_name` varchar(14) NOT NULL,`last_name` varchar(16) NOT NULL,`gender` char(1) NOT NULL,`hire_date` date NOT NULL,PRIMARY KEY (`emp_no`));

mysql中的SUBSTRING()函数可以对字符串进行截取

# 代码1: MySQL可过, 牛客网不通过
select first_name 
from employees
order bysubstring(first_name, -2, 2);

# 代码2: 牛客网 & MySQL都可过
selectfirst_name
fromemployees
order by substr(first_name, length(first_name)-1, 2);

---

题目 53

按照 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 & emp_no 两个字段的信息, 其中 emp_no 有多个的话, 不同 emp_no 之间用逗号连接
• 需要使用GROUP_CONCAT() 函数 (将分组中的字符串与各种选项进行连接)

SELECT dept_no,group_concat(emp_no)
FROMdept_emp
group by dept_no;

---

题目 54

查找排除最大、最小 salary 之后的当前 (to_date = ‘9999-01-01’ ) 所有员工的平均工资 avg_salary

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`));

思路:

• 去除 最大&最小 的工资, 用not in

select 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')

---

题目 55

分页查询 employees 表，每5行一页，返回第2页的数据

CREATE TABLE `employees` (`emp_no` int(11) NOT NULL,`birth_date` date NOT NULL,`first_name` varchar(14) NOT NULL,`last_name` varchar(16) NOT NULL,`gender` char(1) NOT NULL,`hire_date` date NOT NULL,PRIMARY KEY (`emp_no`));

理解题意:

• 没5行一页, 返回第二行的数据; 其实就是输出第6~10条数据, 可以用limit实现
• imit后有两个参数，第一个参数为从第几个数据开始，第二个参数为取多少个数据
• 注意, 第一个参数为0

# 代码
select*
from employees
limit 5, 5

---

题目 56

获取所有员工的 emp_no、部门编号 dept_no 以及对应的 bonus 类型 btype 和 received，
没有分配奖金的员工不显示对应的bonus类型btype和received

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`));------------------------------------------------------------------------------------------CREATE TABLE `emp_bonus`(emp_no int(11) NOT NULL, received datetime NOT NULL, btype smallint(5) NOT NULL);------------------------------------------------------------------------------------------CREATE TABLE `employees` (`emp_no` int(11) NOT NULL, `birth_date` date NOT NULL, `first_name` varchar(14) NOT NULL, `last_name` varchar(16) NOT NULL, `gender` char(1) NOT NULL, `hire_date` date NOT NULL, PRIMARY KEY (`emp_no`));

思路:

• 需要获取emp_no, dept_no, btype, 和 received 四个字段的数据, btype 和 received若为空则显示null
• 用左连接或右连接进行多表联合查询

# 代码
select employees.emp_no, dept_emp.dept_no,emp_bonus.btype,emp_bonus.received
fromemployees
inner joindept_emp
ondept_emp.emp_no=employees.emp_no
left joinemp_bonus
onemployees.emp_no=emp_bonus.emp_no

需要注意多个join使用时的语句顺序

• 一个join语句下立马跟上相应的on语句
• 然后再写下一个join 语句

---

题目 57

使用含有关键字 exists 查找未分配具体部门的员工的所有信息。

CREATE TABLE `employees` (`emp_no` int(11) NOT NULL, `birth_date` date NOT NULL,`first_name` varchar(14) NOT NULL, `last_name` varchar(16) NOT NULL,`gender` char(1) NOT NULL, `hire_date` date NOT NULL, PRIMARY KEY (`emp_no`));---------------------------------------------------------------------------------------------------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`));

思路:

• MySQL中exists的用法:
  • EXISTS用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值True或False
    EXISTS 指定一个子查询，检测 行 的存在。

# 代码
SELECT * FROM employees WHERE NOT EXISTS 
(SELECT emp_no FROM dept_emp WHERE emp_no = employees.emp_no)

---

题目 58

存在如下的视图：

create view emp_v as select * from employees where emp_no >10005;-----------------------------------------------------------------------------------------CREATE TABLE `employees` (`emp_no` int(11) NOT NULL, `birth_date` date NOT NULL, `first_name` varchar(14) NOT NULL, `last_name` varchar(16) NOT NULL, `gender` char(1) NOT NULL, `hire_date` date NOT NULL,PRIMARY KEY (`emp_no`));

获取employees中的行数据，且这些行也存在于emp_v中。注意不能使用intersect关键字。
(你能不用select * from employees where emp_no >10005 这条语句完成吗，挑战一下自己对视图的理解)

与 题目 47 一模一样

# 代码
select * from emp_v

---

题目 59

获取有奖金的员工相关信息。

给出emp_no、first_name、last_name、奖金类型 btype、对应的当前薪水情况 salary 以及奖金金额 bonus。
bonus 类型 btype 为 1 其奖金为薪水 salary 的 10%，btyp e为 2 其奖金为薪水的 20%，其他类型均为薪水的 30%。

当前薪水表示 to_date=‘9999-01-01’

CREATE TABLE `employees` (`emp_no` int(11) NOT NULL, `birth_date` date NOT NULL,`first_name` varchar(14) NOT NULL,`last_name` varchar(16) NOT NULL,`gender` char(1) NOT NULL,`hire_date` date NOT NULL,PRIMARY KEY (`emp_no`));-----------------------------------------------------------------------------------------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`));-----------------------------------------------------------------------------------------               create table `emp_bonus` (`emp_no` int not null,`received` 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`));

思路:

• 需要的数据:
  • emp_no, first_name, last_name字段可以从employees表中获得
  • btype字段数据从emp_bonus表中获得
  • salary数据需要从sallaries中获取,
  • bonus需要根据salary, 和btype计算得出
• 获取有奖金的员工的emp_no, first_name, last_name, btype,
  • 用 employees inner join emp_bonus on emp_no=emp_no
• 获取员工工资和奖金的数额
  • 用left join

SELECT e.emp_no, e.first_name, e.last_name, eb.btype, s.salary, (s.salary*eb.btype/10.0) AS bonus
FROM employees AS e 
INNER JOIN emp_bonus AS eb
ON e.emp_no=eb.emp_no
left JOIN salaries AS s 
ON eb.emp_no=s.emp_no AND s.to_date='9999-01-01';

---

题目 60

按照 salary 的累计和求出 running_total，running_total 为前 N 个当前 ( to_date = ‘9999-01-01’) 员工的 salary 累计和

结果需要输出 emp_no, salary, 和 running_total 三个字段的信息

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`));

输出案例:

emp_no	salary	running_total
10001	88958	88958
10002	72527	161485
10003	43311	204796

思路:

• 输出emp_no, 和 salary字段信息并不难, 难点在于如何将前N个salary字段的信息加总

• 仅仅输出 emp_no, 和 salary 字段信息

  • select emp_no, salary from salaries where to_date = '9999-01-01'# 输出:
    +--------+--------+
    | emp_no | salary |
    +--------+--------+
    |  10001 |  10765 |
    |  10002 |   7978 |
    +--------+--------+
    

• 观察输出的数据 :
  emp_no 都是按升序输出的, 那么我们如果要得到一个累加值的话, 只需要将小于等于当前emp_no的salary加总即可

• 用select语句中嵌套select语句实现

# 代码:
select t1.emp_no, t1.salary,(select sum(t2.salary) from salaries t2 where t2.emp_no<=t1.emp_no and t2.to_date='9999-01-01') as sum
from salaries t1
where t1.to_date = '9999-01-01'

---

题目 61

对于 employees 表中，输出 first_name 排名 (按 first_name 升序排序) 为奇数的 first_name

CREATE TABLE `employees` (`emp_no` int(11) NOT NULL,`birth_date` date NOT NULL,`first_name` varchar(14) NOT NULL,`last_name` varchar(16) NOT NULL,`gender` char(1) NOT NULL,`hire_date` date NOT NULL,PRIMARY KEY (`emp_no`));

思路:

• 仅仅输出first_name, 然后按升序排列很简单

  • select first_name from employees order by first_name
    

• 难点在于如何仅仅输出排列数为奇数的数据

• 先研究如何得到排列数:

  • 首先需要了解, 字符串是可以使用比较运算符的, 那么我们可以利用上一题的思路拿到排序的数字

  • select t1.first_name, (select count(t2.first_name) from employees t2 where t2.first_name<=t1.first_name) as num
    from employees t1
    order by t1.first_name# 输出:
    +------------+------+
    | first_name | num  |
    +------------+------+
    | Anneke     |    1 |
    | Bezalel    |    2 |
    | Chirstian  |    3 |
    | Duangkaew  |    4 |
    +------------+------+
    

• 由于select语句是最后才被执行的, 因此无法拿取排列顺序进行奇偶数判断, 所以需要将排列顺序在where语句前拿到

  • 因此需要把子查询语句写在where语句中 (题目中不需要输出排名)

# 代码1: 
select t1.first_name
from employees t1
where(select count(t2.first_name) from employees t2 where t2.first_name<=t1.first_name order by t2.first_name)%2=1# 注意, 由于题目没有要求排序, 所以如果最后加上order by的话,会不通过
# 实际上, 在子选择语句中由于以及使用了比较运算符, 通过得到比某值小或等于的值有多少个可以得知该值的奇偶性, 因此子选择语句中的order by也可以省略

# 代码2:
SELECT e1.first_name 
FROM (SELECT e2.first_name, (SELECT COUNT(*) FROM employees AS e3 WHERE e3.first_name <= e2.first_name) AS rowid FROM employees AS e2) AS e1
WHERE e1.rowid % 2 = 1
order by e1.first_name