牛客网sql刷题解析-完结

⽜客⽹sql刷题解析-完结
解题步骤：
题⽬：查询最晚⼊职员⼯的所有信息
　⽬标：查询员⼯的所有信息
筛选条件：最晚⼊职
答案：
1SELECT
2*--查询所有信息就⽤*
3FROM
4 employees
5WHERE
6 hire_date = (--这⾥是⼀个⼦查询，因为要和hire_date匹配，所以只能是⼀个值，注意max函数使⽤规则
7SELECT
8MAX(hire_date)
9FROM
10 employees
11 )
解题步骤：
题⽬：查找⼊职员⼯时间排名倒数第三的员⼯所有信息
　⽬标：查询员⼯的所有信息
筛选条件：⼊职时间到第三
答案：
1SELECT
2*--所有信息，⽤*省事
3FROM
4 employees
5where hire_date = ( SELECT DISTINCT--⼦查询，注意hire_date是⼀个值，⼦查询的返回值⼀定要是⼀个
6 hire_date
7FROM
8 employees
9ORDER BY--这⾥有⼀个⼩技巧，倒序排，从第三条取，去⼀条
10 hire_date DESC
11 limit 2,1--分页语法要仔细看看，limit m,n=> 从m+1开始取，取n条
12 )
解题步骤：
题⽬：查找各个部门当前(to_date='9999-01-01')领导当前薪⽔详情以及其对应部门编号dept_no
　⽬标：查询领导薪⽔详情，对应部门编号
筛选条件：部门表当前时间，隐藏的条件（薪⽔表当前时间）
答案：
1SELECT
2 s.*,
3 d.dept_no
4FROM
5 salaries s--左联到部门表，薪⽔表⼈全，所以做主表⽐较好，不会出现关联出空的情况
6LEFT JOIN dept_manager d ON s.emp_no = d.emp_no
7WHERE
8 s.TO_DATE ='9999-01-01'--筛选条件
9AND d.TO_DATE ='9999-01-01'
解题步骤：
题⽬：查找所有已经分配部门的员⼯的last_name和first_name
　⽬标：查询员⼯的 last_name,first_name,题⽬隐藏要显⽰dept_no
筛选条件：已分配部门的员⼯
答案：
1SELECT
2 st_name,
3 e.first_name,
4 d.dept_no
5FROM
6 employees e--通过左联，确认员⼯已分配部门
7LEFT JOIN dept_emp d ON d.emp_no = e.emp_no
8WHERE
9 d.dept_no !=''--防⽌关联为空
解题步骤：
题⽬：查找所有员⼯的last_name和first_name以及对应部门编号dept_no，也包括展⽰没有分配具体部门的员⼯　⽬标：查询员⼯的 last_name,first_name,题⽬隐藏要显⽰dept_no，没有分配具体部门的员⼯
筛选条件：已分配部门的员⼯
答案：
1SELECT
2 st_name,
3 ep.first_name,
4 dp.dept_no
5FROM
6 employees ep --⼈员信息表为主表，左联，⼀位部门可能为空，所以关联后就会包含未分配部分的⼈
7LEFT JOIN dept_emp dp ON ep.emp_no = dp.emp_no
解题步骤：
题⽬：查找所有员⼯⼊职时候的薪⽔情况，给出emp_no以及salary，并按照emp_no进⾏逆序
　⽬标：查询薪⽔情况，显⽰emp_no以及salary（select 要显⽰的字段）
筛选条件：员⼯⼊职时间，并按照emp_no进⾏逆序
答案：
1SELECT --显⽰字段
2 e.emp_no,
3 s.salary
4FROM
5 employees e
6LEFT JOIN salaries s ON e.emp_no = s.emp_no --确定是同⼀个⼈
7AND e.hire_date = s.from_date --确定是⼊职时间
8ORDER BY
9 e.emp_no DESC --倒序
解题步骤：
题⽬：查找薪⽔涨幅超过15次的员⼯号emp_no以及其对应的涨幅次数t
　⽬标：查找员⼯号，涨幅次数（select 要显⽰的字段）
筛选条件：涨幅超过15次
答案：
1SELECT
2 emp_no,
3SUM(1) --统计次数;
4FROM
5 salaries
6GROUP BY
7 emp_no --对每⼀个员⼯进⾏分组，然后统计其涨幅次数
8HAVING
9COUNT(1) >15--进⾏次数过滤
解题步骤：
题⽬：找出所有员⼯当前(to_date='9999-01-01')具体的薪⽔salary情况，对于相同的薪⽔只显⽰⼀次,并按照逆序显⽰　⽬标：查找薪⽔（select 要显⽰的字段）
筛选条件：当前时间（to_date）,相同的仅显⽰⼀次，逆序显⽰
答案：
SELECT DISTINCT--去重
salary
FROM
salaries
WHERE
TO_DATE ='9999-01-01'
ORDER BY
salary DESC--倒序
解题步骤：
题⽬：获取所有部门当前manager的当前薪⽔情况，给出dept_no, emp_no以及salary，当前表⽰to_date='9999-01-01'　⽬标：查找dept_no, emp_no以及salary（select 要显⽰的字段）
筛选条件：当前表⽰to_date='9999-01-01'
答案：
1SELECT
2 d.dept_no,
3 s.emp_no,
4 s.salary
5FROM --注意左关联条件
6 dept_manager d
7LEFT JOIN salaries s ON d.emp_no = s.emp_no
8AND d.TO_DATE = s.TO_DATE
9WHERE
10 d.TO_DATE ='9999-01-01'
解题步骤：
题⽬：获取所有⾮manager的员⼯emp_no
　⽬标：查找emp_no（select 要显⽰的字段）
筛选条件：⾮manager的员⼯
答案：
1SELECT
2 em.emp_no
3FROM
4 employees em
5WHERE
6NOT EXISTS ( --此处⽤了⼀个exists表达式，是管理者的员⼯，然后将这些排除就时⾮管理员的员⼯，此处也可以⽤not in,但是效率会降低⼀些 7SELECT
81
9FROM
10 dept_manager dm
11WHERE
12 em.emp_no = dm.emp_no
13 )
题外闲谈：exists,in;语法上区别，效率上区别；没有觉得效率⾼低，看实际场景
解题步骤：
题⽬：获取所有员⼯当前的manager，如果当前的manager是⾃⼰的话结果不显⽰，当前表⽰to_date='9999-01-01'　⽬标：查找员⼯，当前的manager（select 要显⽰的字段）
筛选条件：当前表⽰to_date='9999-01-01'，如果当前的manager是⾃⼰的话结果不显⽰
答案：
1SELECT
2 de.emp_no,
3 dm.emp_no AS manager_no
4FROM
5 dept_emp de
6LEFT JOIN dept_manager dm ON de.dept_no = dm.dept_no
7WHERE
8 de.emp_no != dm.emp_no
9AND de.TO_DATE ='9999-01-01'
10AND dm.TO_DATE ='9999-01-01'
解题步骤：
题⽬：获取所有部门中当前员⼯薪⽔最⾼的相关信息，给出dept_no, emp_no以及其对应的salary
　⽬标：查找部门，员⼯，薪⽔（select 要显⽰的字段）
筛选条件：当前表⽰to_date='9999-01-01'，
答案：
1SELECT
2 de.dept_no,
3 de.emp_no,
4MAX(sa.salary) --注意函数使⽤时机，什么时候需要group by，什么时候不需要
5FROM
6 dept_emp de
7LEFT JOIN salaries sa ON de.emp_no = sa.emp_no
8WHERE
9 de.TO_DATE ='9999-01-01' --题⽬中默认为当前时间
10AND sa.TO_DATE ='9999-01-01'
11GROUP BY
12 de.dept_no --每个部门中最⾼的薪⽔的⼈，所以需要按照部门分组
解题步骤：
题⽬：从titles表获取按照title进⾏分组，每组个数⼤于等于2，给出title以及对应的数⽬t。

　⽬标：查找title以及对应的数⽬t（select 要显⽰的字段）
筛选条件：按照title进⾏分组，每组个数⼤于等于2
1SELECT
2 title,
3COUNT(1) AS t
4FROM
5 titles
6GROUP BY
7 title --分组
8HAVING --配合分组使⽤，作⽤和where差不多
9COUNT(1) >=2
解题步骤：
题⽬：从titles表获取按照title进⾏分组，每组个数⼤于等于2，给出title以及对应的数⽬t。

注意对于重复的title进⾏忽略。

　⽬标：查找title以及对应的数⽬t（select 要显⽰的字段）
筛选条件：按照title进⾏分组，每组个数⼤于等于2，注意对于重复的title进⾏忽略。

1SELECT
2 title,
3COUNT(DISTINCT(emp_no)) AS t --本题关键是对于重复的title进⾏忽略。

也就是计数的时候要去重，注意函数和distinct的使⽤⽅法
4FROM
5 titles
6GROUP BY
7 title
8HAVING
9COUNT(1) >=2
关于count(distinct)的延伸
sql-按条件统计⾮重复值，count(distinct case when)使⽤
背景
项⽬中，遇到⼀个统计需求，从某张表中按照条件分别统计。

刚开始想到⽤union all的写法，太臃肿，后来使⽤count(distinct case when)解决此问题count
数据统计中，count出现最频繁
最简单的⽤法
select count(*) from table where ....
select count(distinct xx) from table where ...
但最简单的⽤法也有其深刻的地⽅，⽐如这⾥其实有3种写法，count(1)、count(*)、count(字段)，它们有什么区别呢？
count(1) 和count(*)
count(1)和count(*)差别不⼤，使⽤count(*)时，sql会帮你⾃动优化，指定到最快的字段。

所以推荐使⽤count(*)
count(*) 和count(字段)
count(*)会统计所有⾏数，count(字段)不会统计null值
count(case when)
条件统计，即对某个table分条件统计，⽐如表test_db，有⼀个字段user_id(可能重复), gender(man、women)，需要统计man和women的⼈数
可以使⽤where分别统计
select count(distinct user_id) as man_cnt from test_db where gender ='man'
select count(distinct user_id) as women_cnt from test_db where gender ='women'
也可以使⽤按条件统计
select count(distinct case gender ='man'then user_id end) as man_cnt --⾄于case when，本⼈没⽤过，不过也很少有这个使⽤场景吧
, count(distinct case gender ='women'then user_id end) as woman_cnt
from test_db
解题步骤：
题⽬：查找employees表所有emp_no为奇数，且last_name不为Mary的员⼯信息，并按照hire_date逆序排列
　⽬标：查找所有员⼯信息（select 要显⽰的字段）
筛选条件：ast_name不为Mary，emp_no为奇数
1SELECT
2*
3FROM
4 employees
5where emp_no%2!=0 --如果emp_no为索引列，会导致索引失效
6AND last_name !='Mary'
7order by hire_date desc
拓展：；
两个⽰例
例⼦⼀
表结构
DROP TABLE IF EXISTS `account`;
CREATE TABLE IF NOT EXISTS `account` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`account` int(10) unsigned NOT NULL,
`password` char(32) NOT NULL,
`ip` char(15) NOT NULL,
`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `time` (`time`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
⽐如要统计2012年08⽉15⽇注册的会员数：
SELECT count(id) FROM account WHERE DATEDIFF("2012-08-15",time)=0
例⼦⼆
表结构
DROP TABLE IF EXISTS `active`;
CREATE TABLE IF NOT EXISTS `user` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`userid` int(10) unsigned NOT NULL,
`lastactive` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `lastactive` (`lastactive`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
统计最近3分钟的活跃⽤户
SELECT count(id) FROM user WHERE unix_timstamp()-lastactive <180
以上两个例⼦中，虽然都建有索引，但是SQL执⾏中却不⾛索引，⽽采⽤全表扫描。

原因揭密
SQL语句where中如果有functionName(colname)或者某些运算，则MYSQL⽆法使⽤基于colName的索引。

使⽤索引需要直接查询某个字段。

索引失效的原因是索引是针对原值建的⼆叉树，将列值计算后，原来的⼆叉树就⽤不上了；
为了解决索引列上计算引起的索引失效问题，将计算放到索引列外的表达式上。

解决办法
例⼦⼀：SELECT count(id) FROM account WHERE time between "2012-08-1500:00:00" and "2012-08-1523:59:59"
例⼦⼆：SELECT count(id) FROM user WHERE lastactive > unix_timstamp() -180
相关内容
1、如果对时间字段进⾏查找，可以将时间设置为int unsigned类型，存取UNIX时间戳。

因为整型⽐较速度快
2、当我们执⾏查询的时候，MySQL只能使⽤⼀个索引。

3、MySQL只有对以下操作符才使⽤索引：<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE。

可以在LIKE操作中使⽤索引的情形是指另⼀个操作数不是以通配符（%或者 _ ）开头的情形。

例如， “SELECT peopleid
不得不说
创建索引、优化查询以便达到更好的查询优化效果。

但实际上，MySQL有时并不按我们设计的那样执⾏查询。

MySQL是根据统计信息来⽣成执⾏计划的，这就涉及索引及索引的刷选率，表数据量，还有⼀些额外的因素。

Each table index is queried, and the best index is used unless the optimizer believes that it is more efficient to use a table scan. At one time, a scan was used based on whether the best index spanned more than 30%of the table, but a fixed pe 简⽽⾔之，当MYSQL认为符合条件的记录在30%以上，它就不会再使⽤索引，因为mysql认为⾛索引的代价⽐不⽤索引代价⼤，所以优化器选择了⾃⼰认为代价最⼩的⽅式。

事实也的确如此
实例检测
表结构
DROP TABLE IF EXISTS `active`;
CREATE TABLE IF NOT EXISTS `active` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`userid` int(10) unsigned NOT NULL,
`lastactive` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `lastactive` (`lastactive`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
插⼊数据
insert into active values
(null,10000, unix_timestamp("2012-08-2015:10:02")),
(null,10001, unix_timestamp("2012-08-2015:10:02")),
(null,10002, unix_timestamp("2012-08-2015:10:03")),
(null,10003, unix_timestamp("2012-08-2015:10:03")),
(null,10004, unix_timestamp("2012-08-2015:10:03")),
(null,10005, unix_timestamp("2012-08-2015:10:04")),
(null,10006, unix_timestamp("2012-08-2015:10:04")),
(null,10007, unix_timestamp("2012-08-2015:10:05")),
(null,10008, unix_timestamp("2012-08-2015:10:06"))
explain select*from active where lastactive > unix_timestamp()-3;
上⾯这句索引起作⽤。

但是我在测试中，因为插⼊的⽇期与我测试的当前⽇期相差不少时间。

所以我改写为以下内容：
explain select*from active where lastactive > unix_timestamp("2012-08-2015:10:06") -3;
但是数据显⽰，TYPE为ALL，key为NULL。

也就是说索引不起作⽤。

我在改写以下语句测试：
explain select*from active where lastactive > unix_timestamp("2012-08-2015:10:06");
上⾯这个语句，索引⼜起作⽤了。

⼀个疑惑
正好⼿头上有⼀个12016条记录的数据，证实⼀下“当MYSQL认为符合条件的记录在30%以上，它就不会再使⽤索引”的结论。

经过测试，在总记录12016条记录的表中，查询⼩于1854条记录时⾛索引，⼤于该记录时不⾛索引。

符合条件的记录在15. mysql⾃⼰判断是否使⽤索引，如果你⾃⼰确信使⽤索引可以提⾼效率，你也可以强⾏实⽤索引force index(index_name)
解题步骤：
题⽬：统计出当前各个title类型对应的员⼯当前（to_date='9999-01-01'）薪⽔对应的平均⼯资。

结果给出title以及平均⼯资avg。

　⽬标：查找结果给出title以及平均⼯资avg。

（select 要显⽰的字段）
筛选条件：默认为当前时间
1SELECT
2 t.title,
3AVG(s.salary) AS avg
4FROM
5 salaries s
6LEFT JOIN titles t ON s.emp_no = t.emp_no
7WHERE
8 s.TO_DATE ='9999-01-01'
9AND t.TO_DATE ='9999-01-01'
10GROUP BY
11 t.title
题⽬：获取当前（to_date='9999-01-01'）薪⽔第⼆多的员⼯的emp_no以及其对应的薪⽔salary
　⽬标：查找emp_no以及其对应的薪⽔salary（select 要显⽰的字段）
筛选条件：薪⽔第⼆多
1SELECT
2 emp_no,
3 salary
4FROM
5 salaries
6WHERE
7 TO_DATE ='9999-01-01'
8ORDER BY
9 salary DESC --本题精髓，第⼆多，可以理解成倒序排第⼆的⼈
10 limit
111,1 --从第⼆条开始，去⼀条；也就是取得第⼆条
题⽬：查找员⼯编号emp_no为10001其⾃⼊职以来的薪⽔salary涨幅值growth
　⽬标：查找涨幅值growth（select 要显⽰的字段）
筛选条件：⾃⼊职以来，号emp_no为10001
1SELECT
2MAX(salary) -MIN(salary) AS growth --函数的使⽤，计算，以及起别名 as的语法
3FROM
4 salaries
5WHERE
6 emp_no ='10001';
终于到了听起来⽜逼点的题⽬啦，其实也没啥~~~，还是查询
题⽬：针对actor表创建视图actor_name_view，只包含first_name以及last_name两列，并对这两列重新命名，first_name为first_name_v，last_name修改为last_name_v：
　⽬标：创建这个视图
筛选条件：
1CREATE VIEW actor_name_view AS
2SELECT
3 first_name AS first_name_v,
4 last_name AS last_name_v
5FROM
6 actor
　题⽬：针对salaries表emp_no字段创建索引idx_emp_no，查询emp_no为10005, 使⽤强制索引。

　⽬标：使⽤强制索引
筛选条件：
1SELECT
2*
3FROM
4 salaries indexed
5by idx_emp_no --使⽤强制索引
6WHERE
7 emp_no =10005
拓展：
　题⽬：修改表结构，⽤sql（这样⽐⼿写显得⽜逼）
　⽬标：alter⽤法
筛选条件：
1ALTER TABLE actor ADD'create_date'datetime NOT NULL DEFAULT'0000-00-00 00:00:00'
　题⽬：构造⼀个触发器audit_log，在向employees_test表中插⼊⼀条数据的时候，触发插⼊相关的数据到audit中。

（听着就很⾼级，虽然实际中从未⽤过）　⽬标：
筛选条件：
1CREATE TRIGGER audit_log AFTER
2INSERT ON employees_test
3BEGIN
4INSERT INTO audit VALUES (
5 new.id,
6
7 );
8
9END;
　题⽬：使⽤含有关键字exists查找未分配具体部门的员⼯的所有信息。

　⽬标：查找员⼯的所有信息。

筛选条件：未分配具体部门
1SELECT
2*
3FROM
4 employees e
5WHERE
6NOT EXISTS ( --就是exists的使⽤，上⾯有详细讲解过的，注意返回值是逻辑真假
7SELECT
81
9FROM
10 dept_emp de
11WHERE
12 e.emp_no = de.emp_no
13 )。