mysql 语句牛客网练习21-40_中

21. 统计各个部门对应员工涨幅的次数总和，给出部门编码dept_no、部门名称dept_name以及次数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`));

• 答案 题目讨论

这道题在牛客争议较大，
仅分享思路
1. 查询每个员工薪水的涨幅次数
2. 与dept_emp连接，生成一个包含dept_no,emp_no,counts字段的临时表
3. 将第二步生成的临时表再和departments进行连接，以dept_no分株，计算对于部门的所有员工的counts的总和，生成题目所需的字段。

22. 对所有员工的当前(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`));

• 答案

分析：
1. 首先是针对一张表的salary进行排序
SELECT emp_no,salary.COUNT(salary) AS rank
	FROM salaries 
	WHERE to_date = '9999-01-01'
	ORDER BY salary DESC;
	
2. 这样只是统计了salary出现的次数，并没有按照次序进行排序，要进行次序的排序，必须count，大于等于该条salary的数据条数，因为有重复数据，所以需要DISTINCT，此处使用表的重复使用功能
SELECT s1.emp_no,s1.salary,COUNT(SISTINCT s2.salary) AS rank
	FROM salaries s1,salaries s2
	WHERE s1.to_date = '9999-01-01'
	AND s2.to_date = '9999-01-01'
	AND s1.salary <= s2.salary
	ORDER BY s1.salary DESC;
	
3.还需要按照emp_no分组、升序排列
SELECT s1.emp_no,s1.salary,COUNT(DISTINCT s2.salary) AS rank
	FROM salaries s1,salaries 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;

23. 获取所有非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`));

• 答案

自己的：（还是不习惯用连接）
SELECT de.dept_no,de.emp_no,s.salary
    FROM dept_emp de,salaries s
    WHERE de.emp_no = s.emp_no
    AND s.to_date = '9999-01-01'
    AND de.emp_no NOT IN(
        SELECT emp_no 
        FROM dept_manager
    )

大佬的：
SELECT de.dept_no,de.emp_no,s.salary
    FROM dept_emp de
    INNER JOIN salaries s
    ON de.emp_no = s.emp_no
    AND s.to_date = '9999-01-01'
    WHERE de.emp_no NOT IN(
        SELECT emp_no
        FROM dept_manager
    )

24. 获取员工其当前的薪水比其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`));

• 答案

思路：划分为两张表，1.员工的工号和薪资的表；2.manager的工号和薪资的表。然后两张表比较，输入员工比起manager工资高者即可
SELECT emp_sa.emp_no,man_sa.emp_no AS manager_no,emp_sa.salary AS emp_salary,man_sa.salary AS manager_salary
    FROM
        (SELECT emp.emp_no,s.salary,emp.dept_no
        FROM dept_emp emp
        INNER JOIN salaries s
        ON emp.emp_no = s.emp_no
        AND s.to_date = '9999-01-01'
        )AS emp_sa,
        (SELECT man.emp_no,s.salary,man.dept_no
         FROM dept_manager man
         INNER JOIN salaries s
         ON man.emp_no = s.emp_no
         AND s.to_date = '9999-01-01'
        )AS man_sa
    WHERE emp_sa.dept_no = man_sa.dept_no 
    AND emp_sa.salary > man_sa.salary

25. 汇总各个部门当前员工的title类型的分配数目，结果给出部门编号dept_no、dept_name、其当前员工所有的title以及该类型title对应的数目count

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

• 答案：

SELECT d.dept_no,d.dept_name,t.title,COUNT(t.title) AS count
    FROM titles t
    INNER JOIN dept_emp emp
    ON t.emp_no = emp.emp_no
    AND emp.to_date = '9999-01-01'
    AND t.to_date = '9999-01-01'
    INNER JOIN departments d
    ON emp.dept_no = d.dept_no
    GROUP BY d.dept_no,title
    
解析：
1. 牛客网的sql，只要题目中出现了 当前  之类的字，就得需要加 to_date = '9999-01-01'的条件了
2. 看题目要求，汇总各个部门当前员工。各个部门！我只知道 GROUP BY title,忘了还需要针对 dept_no，分下组了。

26. 查找描述信息中包括robot的电影对应的分类名称以及电影数目，而且还需要该分类对应电影数量>=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);

• 答案

做哭我了，题意看蒙我了
大佬的思路：	1. 找到对应的电影数量>=5的所有分类，建立一个虚表
			2. 设置限定条件
            3. 联合3个实表和一个虚表进行查询
            
SELECT c.name,COUNT(c.name)
	FROM film f,category c,film_category fc,
	(SELECT category_id 
	 FROM film_category
	 GROUP BY category_id
	 HAVING COUNT(film_id) >= 5
	) AS cc
	WHERE f.description LIKE '%robot%'
	AND f.film_id = fc.film_id
	AND fc.category_id = c.category_id
	AND c.category_id = cc.category_id

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

• 不写建表sql了，跟26题的建表sql一模一样

• 答案

这道题还阔以，挺简单的
SELECT f.film_id,f.title
    FROM film f
    LEFT JOIN film_category fc
    ON f.film_id = fc.film_id
    WHERE fc.category_id IS NULL

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

• 建表sql依旧跟26的一模一样
• 答案

子查询：
SELECT f.title,f.description
    FROM film f,film_category fc
    WHERE f.film_id = fc.film_id
    AND fc.category_id = 
    (SELECT category_id
     FROM category
     WHERE name = 'Action'
    )
    
连接(非子查询）
SELECT f.title,f.description
    FROM film f
    INNER JOIN film_category fc
    ON f.film_id = fc.film_id
    INNER JOIN category c
    ON fc.category_id = c.category_id
    WHERE c.name = 'Action'

29. 将employees表的所有员工的last_name和first_name拼接起来作为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`));

• 答案

看了一眼这题目，（终于遇见一个不用动脑子的题了）
看了第二眼，（咦，有点小问题）
看了第三眼，（管它呢，先写出来再说）

SELECT(last_name+" "+first_name) AS Name
    FROM employees
    
不好意思，打扰了。。。
别人家的：
SELECT last_name||" "||first_name AS Name
	FROM employees
牛客网上通过测试了，但是不支持MySql，是什么SQLLite的语法

我本地使用Navicat测试，
SELECT
    CONCAT( CONCAT( last_name, " " ), first_name ) AS NAME 
FROM
    employees
只有这个达到了预期的结果（但是这个在牛客网上是通过不了的）

CONCAT()、CONCAT_WS()

• mysql中CONCAT()函数用于将多个字符连接成一个字符串，

  • CONCAT(str1,str2,…)：返回结果为连接参数产生的字符串

  

  • 若有任何一个参数为NULL，则返回NULL
    

• mysql中CONCAT_WS(separator,str1,str2,…)：跟CONCAT()差不多，但是它的第一个参数是分隔符，分隔符的位置是需要放在要连接的两个字符中间。

  • 分隔符可以是一个字符 或者 一个字符串

  • 字符

-   字符串

- 分隔符为 NULL，则返回NULL

-   （makedowm的图片排版怎么这么飘逸呢？）

-   附赠：[菜鸟编程MySQL函数解析](https://www.runoob.com/mysql/mysql-functions.html)

30.创建一个actor表，包含如下列信息

列表	类型	是否为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	最后更新时间，默认是系统的当前时间

• 答案

CREATE TABLE IF NOT EXISTS actor (
    actor_id SMALLINT(5) NOT NULL,
    first_name VARCHAR(45) NOT NULL,
    last_name VARCHAR(45) NOT NULL,
    last_update TIMESTAMP NOT NULL DEFAULT (datetime('now','localtime')),
    PRIMARY KEY(actor_id),
)

datetime()似乎是一个函数，在navicat中去掉datetime()函数可顺利建表成功

• 我炸了

31. 对于表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')))

插入数据：

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');
    
因为是全字段插入，所以不需要在values之前不需要声明字段

32. 对于表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');

• navicat测试效果

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	姓氏

• 答案

仅针对MySQL：
CREATE TABLE actor_name
    SELECT first_name,last_name FROM actor

34. 对first_name创建唯一索引uniq_idx_firstname，对last_name创建普通索引idx_lastname

针对如下表actor结构创建索引：  
CREATE TABLE IF NOT EXISTS actor (  
actor_id smallint(5) NOT NULL PRIMARY KEY,  
first_name varchar(45) NOT NULL,  
last_name varchar(45) NOT NULL,  
last_update timestamp NOT NULL DEFAULT (datetime('now','localtime')))  
对first_name创建唯一索引uniq_idx_firstname，对last_name创建普通索引idx_lastname

• 答案

仅针对MySql：
ALTER TABLE actor ADD UNIQUE uniq_idx_firstname(first_name);
ALTER TABLE actor ADD INDEX idx_lastname(last_name);
下面这种既适合MySQL，也适合牛客网的
CREATE UNIQEUE INDEX uniq_idx_firstname ON actor(first_name);
CREATE INDEX idx_lastname ON actor(last_name);

35. 针对actor表创建视图actor_name_view，

只包含first_name以及last_name两列，并对这两列重新命名，first_name为first_name_v，last_name修改为last_name_v：

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

• 答案

第一种：
CREATE VIEW actor_name_view AS
	SELECT first_name AS first_name_v,last_name AS last_name_v
	FROM actor;
	
第二种：
CREATE VIEW actor_name_view (first_name_v,last_name_v) AS
	SELECT first_name,last_name FROM actor;

36. 针对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
SELECT * FROM salaries FROM INDEX idx_emp_no WHERE emp_no = 10005; 

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

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 actor ADD create_date datetime NOT NULL DEFAULT '0000-00-00 00:00:00';

38. 构造一个触发器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
);

• 答案

CREATE TRIGGER audit_log AFTER INSERT ON employees_test
	BEGIN
	INSERT INTO audit VALUES(NEW.ID,NEW.NAME);
	END
	
解析：
	CREATE TRIGGER:创建触发器
	AFTER | BEFORE:说明是在事件之前还是之后执行触发器
	INSERT UPDATE DELETE:针对哪种操作的
	ON:指明表名
	BEGIN 和 END 之间写触发的动作
	NEW关键字表示更新后的表的字段，OLD表示更新后的表的字段。

触发器(Trigger)：

• 数据库的回调函数，他会在指定数据库事件发生时自动执行或调用。

• 要点

  • 可以指定在特定的数据库发生DELETE、INSERT 或 UPDATE时触发，或在一个或多个指定表的指定表的列发生更新时触发。
  • BEFORE 或 AFTER关键字决定何时执行触发器动作，决定是在关联行的插入、修改或删除之前或者之后执行触发器动作。
  • 当触发器相关联的表被删除时，自动删除触发器。

39. 删除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);

insert into titles_test values ('1', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('2', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('3', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('4', '10004', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('5', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('6', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('7', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01');

• 答案

解析：反向思考，只保留最小的id
1. 以emp_no分组，筛选出min(id)
2. 从表中删除不是此id的即可

DELETE FROM titles_test WHERE id NOT IN
	(SELECT MIN(id) FROM titles_test GROUP BY emp_no);

40. 将所有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);

insert into titles_test values ('1', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('2', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('3', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('4', '10004', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('5', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('6', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('7', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01');

• 答案

UPDATE titles_test 
    SET to_date = NULL,
    from_date = '2001-01-01'
    WHERE to_date = '9999-01-01';

文章首发地址: mysql 语句牛客网练习21-40_中