牛客网MySQL练习

最新推荐文章于 2022-07-05 21:33:02 发布

cjm_success

最新推荐文章于 2022-07-05 21:33:02 发布

阅读量1.8k

点赞数 1

文章标签： mysql sql sqlite

原文链接：https://www.nowcoder.com/ta/sql

版权

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

注意点:

由于测试数据中，salaries.emp_no不唯一（因为号码为emp_no的员工会有多次涨薪的可能，所以在salaries中对应的记录不止一条），employees.emp_no唯一，即salaries的数据会多于employees，因此需先找到employees.emp_no在salaries表中对应的记录salaries.emp_no，则有限制条件e.emp_no = s.emp_no
根据题意注意到salaries.from_date 和employees.hire_date的值应该要相等, ( 入职时的工资 ), 因此有限制条件 e.hire_date = s.from_date
根据题意要按照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_no
 and 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_no
 and 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_no
	and a.emp_no = c.emp_no
	and 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>5000
    and (
        (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.name
    from 
        category, film, film_category 
    where 
        category.category_id=film_category.category_id 
        and film_category.film_id=film.film_id 
    group by 
        category.category_id
    having 
        count(film.film_id)>=5
) as t1
代码思路:

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

# 代码1:
select 
	t1.name, 
	count(film.film_id)
from
	(
    	select 
			category.category_id, category.name
		from 
			category, film, film_category 
		where 
			category.category_id=film_category.category_id 
			and film_category.film_id=film.film_id 
		group by 
			category.category_id
		having 
			count(film.film_id)>=5
    ) as t1,
	film, 
	film_category
where
	film.description like '%robot%'
	and film_category.category_id=t1.category_id
	and 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_id
	and fc.category_id = cc.category_id
	and 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
BEGIN
   INSERT INTO audit(EMP_no, NAME) VALUES (new.ID, new.NAME);
END $$
delimiter ;

# 在牛客网中使用的代码
CREATE TRIGGER audit_log
AFTER INSERT 
ON employees_test FOR EACH ROW
BEGIN
   INSERT 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`
  
- 改为如下代码: 

  - ```mysql
    delete 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的两种用法:
replace into

此语句的作用是向表插入数据时使用, 如果插入的数据会导致UNIQUE 索引或PRIMARY KEY发生冲突/重复，则先删除旧数据再插入最新的数据
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')
AS
	tbs;

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
FROM
	employees;

# sqlite
SELECT
	last_name || "'" || first_name
AS 
	name
FROM
	employees

题目 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 by
	substring(first_name, -2, 2);

# 代码2: 牛客网 & MySQL都可过
select
    first_name
from
    employees
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)
FROM
	dept_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
from
	employees
inner join
	dept_emp
on
	dept_emp.emp_no=employees.emp_no
left join
	emp_bonus
on
	employees.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