![604b87b7ba55ac04501113375a44eaf1.png](https://i-blog.csdnimg.cn/blog_migrate/10b9a7e9380ab23da1c2c2765f70e35a.jpeg)
牛客网SQL实战网址:https://www.nowcoder.com/ta/sql
持续更新——记录自己在牛客网SQL的做题过程
更新进度:61题,2019-4-3,更完了
1.查找最晚入职员工的所有信息
![a17a5c3317bc19d61758b5ff658547cc.png](https://i-blog.csdnimg.cn/blog_migrate/3450ce9f4ba95389345e1a5e8a02657f.png)
![87a1c0af28583736269d03745db1f012.png](https://i-blog.csdnimg.cn/blog_migrate/918125e5a7e07f1446885cc8b7c44183.jpeg)
应该考虑的问题:
①最晚入职的当天未必就一个人,也许有多人,使用排序并限制得只能取得指定数量的结果
SELECT *
FROM employees
WHERE hire_date = (
SELECT MAX(hire_date)
FROM employees);
注:日期最大的就是最晚的,日期较早就是较小。
2.查找入职员工时间排名倒数第三的员工所有信息
![d975b578bbe2e022134026d4ee80fd49.png](https://i-blog.csdnimg.cn/blog_migrate/8094428c149a12fcabe829ee357e88bd.png)
![b3944f99bb2e2903cbdd7899e9a9877d.png](https://i-blog.csdnimg.cn/blog_migrate/f3b665d180dca142bc0842e5e409c82e.png)
应该考虑的问题:
①取的是日期倒数第三的人,不是倒数第三的人
SELECT emp_no, birth_date, first_name, last_name, gender, hire_date
FROM employees
WHERE hire_date=(
SELECT DISTINCT hire_date
FROM employees
ORDER BY hire_date DESC
LIMIT 2,1);
注:
①牛客网网友EricZeng的严谨写法,可以学习。
②LIMIT是从0开始计数。
3.查找各个部门当前(to_date='9999-01-01')领导当前薪水详情以及其对应部门编号dept_no
![22d8d0ea6b0bca9dd588cd4fed30f966.png](https://i-blog.csdnimg.cn/blog_migrate/2e24a65de091c701ba36ec7ed12d1fb4.jpeg)
![ba0d550a3d8b04796b289f2b3b031337.png](https://i-blog.csdnimg.cn/blog_migrate/b90a8d3da5d9f615d0a45ce7425f1bd3.png)
应该考虑的问题:
①薪水表是按年发放的,所以要过滤掉以前的薪水,只保留现在还在的当前领导的薪水
SELECT s.*,d.dept_no FROM salaries AS s,dept_manager AS d
WHERE s.emp_no = d.emp_no
AND s.to_date='9999-01-01'
AND d.to_date='9999-01-01'
注:
①用AND可以加条件
②表的顺序和你FROM的顺序还有WHERE的顺序需要一致
4.查找所有已经分配部门的员工的last_name和first_name
![d2402a864ff173052264fa5c63e30a0e.png](https://i-blog.csdnimg.cn/blog_migrate/3a30f71f1cf8c0ef27a4840140398954.jpeg)
![7625b06321d2dc381bdd11192c42a7d1.png](https://i-blog.csdnimg.cn/blog_migrate/bc36e473ae4ca367ee25952b35a6b612.png)
应该考虑的问题:
①因为是查找已分配部门的员工,所以dept_no不应该为空,应该使用内连接。
SELECT emp.last_name,emp.first_name,dept.dept_no
FROM dept_emp dept,employees emp
WHERE emp.emp_no = dept.emp_no
注:表连接。
5.查找所有员工的last_name和first_name以及对应部门编号dept_no
![ca9f2ee83b0a5b40265a6ad3b978a56d.png](https://i-blog.csdnimg.cn/blog_migrate/c5a7132801fc473ce9f1cb3015d7dbdc.jpeg)
![3f15d303170b76cbd7ea70d3d87824bd.png](https://i-blog.csdnimg.cn/blog_migrate/32dcc46bdb23ae06e27fc87913c6069f.png)
应该考虑的问题:
①没有具体分配的员工也要展示,也就是要保证employees表的完整
②内连接已经不能满足需求,需要用左连接或者又连接
SELECT e.last_name,e.first_name,d.dept_no
FROM employees e LEFT JOIN dept_emp d
ON e.emp_no=d.emp_no;
注:
①内连接(INNER JOIN)两边表任何一边缺失都不显示。
②左连接(LEFT JOIN),右边表可以无对应数据。
③右连接(RIGHT JOIN),左边表可以无对应数据。
6. 查找所有员工入职时候的薪水情况
![1066c8e6c84f948211631906367bf213.png](https://i-blog.csdnimg.cn/blog_migrate/bdf31d18a3a776c6c5cb71ed6cc2b4cd.jpeg)
![e68d752b4897b8733db17270538df279.png](https://i-blog.csdnimg.cn/blog_migrate/314dce598b1962586b3fa08af21b68f6.png)
应该考虑的问题:
①因为员工会有多次涨薪,所以salaries.emp_no 不唯一,这时我们就应该确定具体确定这个薪水的时间,也就是这个入职时间hire_date
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
7.查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t
![720b92827c073d21c04123dae29b0259.png](https://i-blog.csdnimg.cn/blog_migrate/3a59cf86bd8ec7d6f2d15293ff77222b.png)
![dfdca4f704149ec1b82fab8327aa0e8a.png](https://i-blog.csdnimg.cn/blog_migrate/9ed6b3024c4947dac22a1c1b4b35989b.png)
应该考虑的问题:
①需要利用分组函数GROUP BY对emp_no进行分组。
②需要利用分组限定条件限定t值
SELECT emp_no,COUNT(emp_no) as t FROM salaries
GROUP BY emp_no HAVING t>15
注:①好像是先选择列和计算,然后再进行分组和再一次计算,也就是按照语句的顺序进行。
8.找出所有员工当前具体的薪水salary情况
![283fb6015efc1f4f193cc0d921092315.png](https://i-blog.csdnimg.cn/blog_migrate/c8473c429cc309e9c07b82cc42af6854.png)
![a2dc0f726d9b5bb37dbe6563c01b463c.png](https://i-blog.csdnimg.cn/blog_migrate/9a47053ee2e9d54ed48ee5c4b9afa00c.png)
SELECT DISTINCT salary FROM salaries
WHERE to_date='9999-01-01'
ORDER BY salary DESC
或
SELECT DISTINCT salary FROM salaries
WHERE to_date='9999-01-01'
GROUP BY salary
ORDER BY salary DESC;
注:
①大表一般用distinct效率不高,大数据量的时候都禁止用distinct,建议用group by解决重复问题。
②在不同记录数较小时,count group by性能普遍高于count distinct,尤其对于text类型表现的更明显。而对于不同记录数较大的场景,count group by性能反而低于直接count distinct(牛客网网友—啊啥水果的总结)
9.获取所有部门当前manager的当前薪水情况,给出dept_no, emp_no以及salary
![f2da36419e7c0611616103317036ac49.png](https://i-blog.csdnimg.cn/blog_migrate/c2c9de06f83e9f6d7eb5d43589b48e52.png)
![a850bf315e540bf136dae8f5690ed3e9.png](https://i-blog.csdnimg.cn/blog_migrate/c6110723981b2884856d15859f02713a.png)
应该考虑的问题:
①同一emp_no会在salaries表中对应多条涨薪记录
SELECT d.dept_no,d.emp_no,s.salary
FROM dept_manager d,salaries s
WHERE d.emp_no = s.emp_no
AND d.to_date = '9999-01-01'
AND s.to_date = '9999-01-01'
10.获取所有非manager的员工emp_no
![913f4815e4cf2854a122705ab2991ea5.png](https://i-blog.csdnimg.cn/blog_migrate/debc96e975bbe073c640eeb975da5aac.png)
![0edd77871009fb21dcc86008441f557b.png](https://i-blog.csdnimg.cn/blog_migrate/c341f49307886dff6c52f7aab661e453.png)
SELECT e.emp_no
FROM employees e
WHERE emp_no NOT IN (SELECT emp_no FROM dept_manager)
注:
①子查询在这里用到了。
②牛客网的wasrehpic说,用LEFT JOIN,然后筛选NULL也行,wasrehpic写的解析很棒,给了我很多帮助。
11.获取所有员工当前的manager
![7f265e2f08162de168b5c32aabc142e7.png](https://i-blog.csdnimg.cn/blog_migrate/4d39930feb5d82cb07ff28a941228ad6.png)
![8f7ae6fc5765bb73e91e60ba53b9d02c.png](https://i-blog.csdnimg.cn/blog_migrate/37a4472e257fb2ddba0985e7c82639b9.png)
需要考虑的问题:
①两个表应该以dept_no进行联结而不是emp_no。
②需要注意如果当前的manager是自己的话结果不显示。
SELECT e.emp_no,m.emp_no AS manager_no
FROM dept_emp e,dept_manager m
WHERE e.dept_no = m.dept_no
AND e.to_date='9999-01-01'
AND m.to_date='9999-01-01'
AND e.emp_no != m.emp_no
注:①这里用到了重命名AS
12.获取所有部门中当前员工薪水最高的相关信息
![6feb7e16deb58958ec4c1689e04b02e2.png](https://i-blog.csdnimg.cn/blog_migrate/f2f9f42cb94b6df4bcbb9f0bd9e81036.png)
![0ae2698454d44d91502111593e8a548d.png](https://i-blog.csdnimg.cn/blog_migrate/368b9448be942895865aae4d1dac0bbc.png)
需要考虑的问题:
①需要用到分组函数GROUPBY,并以部门进行分组。
②需要用到MAX函数取出最大的薪水值
③所以也就是一个分组加最大值函数
SELECT d.dept_no,d.emp_no,MAX(s.salary)
FROM dept_emp d,salaries s
WHERE d.emp_no = s.emp_no
AND d.to_date='9999-01-01'
AND s.to_date='9999-01-01'
GROUP BY d.dept_no
注:考虑的时候应该先考虑分组然后再考虑每个取值的函数,所以想着最好是倒着去想,想完了再正着去写。
13. 从titles表获取按照title进行分组
![9dd20cecdff774d8a87ffd6935cb9fe7.png](https://i-blog.csdnimg.cn/blog_migrate/8650d68b76a19369666260ed54e61f6a.png)
![ef557722dfff1401fa6d741e37ca2020.png](https://i-blog.csdnimg.cn/blog_migrate/07b022c31b697e957edacf172464c813.png)
需要考虑的问题:WHERE后边不可跟COUNT()函数
SELECT title,COUNT(title) AS t
FROM titles
GROUP BY title
HAVING t>=2
14.从titles表获取按照title进行分组,注意对于重复的emp_no进行忽略。
![af0da497ee506f1c05aea0eeac5ad33a.png](https://i-blog.csdnimg.cn/blog_migrate/575eb31aebfc30a6510e75625c8d0ecf.png)
![0042f70e56bb5665fed1433b5b18022e.png](https://i-blog.csdnimg.cn/blog_migrate/9fa2c2362cb16e130275137d6738b300.png)
需要考虑的问题:一个title内可能存在三个emp_no,其中两个有可能是一样的,所以就需要去重统计。
SELECT title,COUNT(DISTINCT emp_no) AS t
FROM titles
GROUP BY title
HAVING t>1
15.查找employees表
![874a236835fc39cc3975e98fa9d60a82.png](https://i-blog.csdnimg.cn/blog_migrate/eebd1a5370949bc0567bce2f9a3e177b.png)
![6dcb1740ef150fb74968342bdff70932.png](https://i-blog.csdnimg.cn/blog_migrate/0f8ee46896c8a4712eefd5a5be914359.png)
SELECT *
FROM employees
WHERE emp_no % 2 = 1
AND last_name <> 'Mary'
ORDER BY hire_date DESC;
16.统计出当前各个title类型对应的员工当前薪水对应的平均工资
![b8370ace4314a35d021e4bba1c3ad159.png](https://i-blog.csdnimg.cn/blog_migrate/347caf5c94966b719267268ab74c9d32.png)
![55b90003acf64b8498c6fc43c61e188a.png](https://i-blog.csdnimg.cn/blog_migrate/34500b7eb580901843f528ba99570882.png)
SELECT t.title,AVG(s.salary) AS avg
FROM titles t,salaries s
WHERE t.emp_no = s.emp_no
AND t.to_date='9999-01-01'
AND s.to_date='9999-01-01'
GROUP BY t.title;
17.获取当前薪水第二多的员工的emp_no以及其对应的薪水salary
![9e00ec4af5b99476f24dd0ddbb70efe7.png](https://i-blog.csdnimg.cn/blog_migrate/3c0d7ee01954e763aa3e0955f7d566f3.png)
![595fb3f4420966f767cdbe620af0861c.png](https://i-blog.csdnimg.cn/blog_migrate/ccb530d94fa70ea14a0ebb2de96fe9b1.png)
SELECT emp_no,salary
FROM salaries
WHERE to_date='9999-01-01'
ORDER BY salary DESC
LIMIT 1,1;
注:在做题讨论时候,有的人说薪水第一的有可能哟多个,所以要去重后选择第二位,但是还有一个问题,如果有两个人并列薪水第一,那么你选择的第二其实是薪水第三多的人,所以其实得看一下数据集再确定,很明显,牛客网给定的数据集只有一个第一,所以怎么写都行。
18.查找当前薪水排名第二多的员工编号emp_no、薪水salary、last_name以及first_name,不准使用order by
![2028a1d719a67afa30b947e7eca9f20f.png](https://i-blog.csdnimg.cn/blog_migrate/33754f753ce0b3a494c504f8f1d8725c.png)
![0d5b55046290e1e4d22b4cd1c291ba9f.png](https://i-blog.csdnimg.cn/blog_migrate/13eec47487e988cb3c63bf12744e1c14.png)
#wasrehpic的答案
SELECT e.emp_no, MAX(s.salary) AS salary, e.last_name, e.first_name
FROM employees AS e INNER JOIN salaries AS s
ON e.emp_no = s.emp_no
WHERE s.to_date = '9999-01-01'
AND s.salary NOT IN (SELECT MAX(salary) FROM salaries WHERE to_date = '9999-01-01')
19.查找所有员工的last_name和first_name以及对应的dept_name
![0d818f55d86cfbbc2512b90090e497e2.png](https://i-blog.csdnimg.cn/blog_migrate/3060f24de061ddb8a09ac509b125b722.png)
![1ebb9dec25545bdd3929daa486f6b971.png](https://i-blog.csdnimg.cn/blog_migrate/8e04ba22a8538757e1d09487a0a186c0.png)
SELECT emp.last_name, emp.first_name, dep.dept_name
FROM employees AS emp LEFT JOIN dept_emp AS dept ON emp.emp_no = dept.emp_no
LEFT JOIN departments AS dep ON dept.dept_no = dep.dept_no
注:多表连接(超过两个表)
20.查找员工编号emp_no为10001其自入职以来的薪水salary涨幅值growth
![1a1c8baa8158b06056cb314e67311722.png](https://i-blog.csdnimg.cn/blog_migrate/879bc9ded35d01536018b60819361fe8.png)
![ee8e52dc62a2b62a9a390d48eef5e586.png](https://i-blog.csdnimg.cn/blog_migrate/4450a1c0f7e6f0df84d7f73afa0cdab3.png)
SELECT MAX(salary)-MIN(salary) AS growth
FROM salaries
WHERE emp_no="10001"
21.查找所有员工自入职以来的薪水涨幅情况
![795cd5247e9522e36df8134e743c4993.png](https://i-blog.csdnimg.cn/blog_migrate/4908c0db1358ce35cb71365fc30cbad1.png)
![05c96e06e88373497673d01ef1b2dd98.png](https://i-blog.csdnimg.cn/blog_migrate/b073c6488535c01e94b85ba325b0517d.png)
思考过程:
①创建第一个表,存储现在的薪水和员工号
②创建第二个表,存储入职的薪水和员工号
③将两个表连接
④计算涨薪
#wasrehpic答案,自己没写出来
SELECT sCurrent.emp_no, (sCurrent.salary-sStart.salary) AS growth
FROM (SELECT s.emp_no,s.salary
FROM employees e LEFT JOIN salaries s
ON e.emp_no = s.emp_no
WHERE s.to_date = '9999-01-01') AS sCurrent
INNER JOIN(SELECT s.emp_no,s.salary
FROM employees e LEFT JOIN salaries s
ON e.emp_no = s.emp_no
WHERE s.from_date = e.hire_date) AS sStart
ON sCurrent.emp_no = sStart.emp_no
ORDER BY growth
22.统计各个部门对应员工涨幅的次数总和
![75cb4f3a2e2966736f8825d2e90ac62f.png](https://i-blog.csdnimg.cn/blog_migrate/bc4a03e7f58d0d647be5ac6fe6217973.png)
![f954a21b992708530c24e256a901911a.png](https://i-blog.csdnimg.cn/blog_migrate/34fa4016b6f66597c4e7a2a5fa411b0c.png)
SELECT dep.dept_no,dep.dept_name,COUNT(dep.dept_no)AS sum
FROM departments dep INNER JOIN dept_emp dept ON dep.dept_no=dept.dept_no
INNER JOIN salaries s ON s.emp_no=dept.emp_no
GROUP BY dep.dept_no
23.对所有员工的薪水按照salary进行按照1-N的排名
![2e875fa4a882e22dc4d1458219a0456f.png](https://i-blog.csdnimg.cn/blog_migrate/7c57c34c28feac8aa5a9afb5ed888f18.png)
![de92e088b1f6b9c1c0cbff152cda6dce.png](https://i-blog.csdnimg.cn/blog_migrate/3bf63e6768678d00cb58ac03e819db57.png)
没写出来
答案如下
![a4f72c541da66697799c80793428e79b.png](https://i-blog.csdnimg.cn/blog_migrate/744bc070594a3c641348dbb605e75d5c.jpeg)
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员工当前的薪水情况
![380c8e3a42dfd9712091df1b3af993db.png](https://i-blog.csdnimg.cn/blog_migrate/01c1d0e97bfd84177ef0703e783b9c31.jpeg)
![d1f7ae1dec90cee0501549c125781a04.png](https://i-blog.csdnimg.cn/blog_migrate/a48c4e8e996a5c320f711574a46e060e.png)
考虑思路:
①将dept_emp和salaries连接,salaeies中就有所有的员工名单,所以不用employees。
②过滤掉经理
SELECT de.dept_no, s.emp_no, s.salary
FROM dept_emp AS de INNER JOIN salaries AS s
ON s.emp_no = de.emp_no
AND s.to_date = '9999-01-01'
WHERE de.emp_no NOT IN (SELECT emp_no
FROM dept_manager
WHERE to_date = '9999-01-01')
25.获取员工其当前的薪水比其manager当前薪水还高的相关信息
![d40400559ee4ddd9322006abed5fa1c6.png](https://i-blog.csdnimg.cn/blog_migrate/1ed1b0d8d3ac4a4285c8d13734dfb992.jpeg)
![c0938bc9ef8a09241053504fd350d3e0.png](https://i-blog.csdnimg.cn/blog_migrate/54f0cd60a8c089eab36eb8117dd59228.png)
考虑过程:
①提取de.emp_no,de.dept_no,s.salary获得一个员工薪水表
②提取dm.emp_no,dm.dept_no,s.salary获得一个经理薪水表
③合并INNER JOIN获得一个目标薪水表
④选出员工薪水大于经理的列
SELECT emp_s.emp_no,mar_s.emp_no AS manager_no,emp_s.salary AS emp_salary,mar_s.salary AS manager_salary
FROM (SELECT de.emp_no,de.dept_no,s.salary
FROM dept_emp de,salaries s
WHERE de.emp_no=s.emp_no
AND de.to_date='9999-01-01'
AND s.to_date='9999-01-01') AS emp_s
INNER JOIN (SELECT dm.emp_no,dm.dept_no,s.salary
FROM dept_manager dm,salaries s
WHERE dm.emp_no=s.emp_no
AND dm.to_date='9999-01-01'
AND s.to_date='9999-01-01') AS mar_s
ON emp_s.dept_no=mar_s.dept_no
WHERE emp_s.salary > mar_s.salary
26.汇总各个部门的当前员工title类型的分配数目
![f6a82cb3e27cfd64827654b557f89622.png](https://i-blog.csdnimg.cn/blog_migrate/154eeb7a2ef0c9cfb4cd684adbaab491.png)
![d458c67c399fb8203ae7eb82ce1903c6.png](https://i-blog.csdnimg.cn/blog_migrate/e6e82edf6de35e06787303ea8a97d8ef.png)
解题思路:
①这是一个三表连接加上分组的问题
②先将titles和dept_emp连接,然后将departments 填进去
③讲dept_no和title分组统计
SELECT dept.dept_no, dp.dept_name, t.title, COUNT(t.title) AS count
FROM titles AS t INNER JOIN dept_emp AS dept
ON t.emp_no = dept.emp_no AND dept.to_date = '9999-01-01' AND t.to_date = '9999-01-01'
INNER JOIN departments AS dp
ON dept.dept_no = dp.dept_no
GROUP BY dept.dept_no, t.title
27.给出每个员工每年薪水涨幅超过5000的员工
![560e86e80116eee3527d67c271360868.png](https://i-blog.csdnimg.cn/blog_migrate/4f143efd57e176673cdbe9116a8d18b1.png)
![ff1e0622174fed489e8fd461e9a46be8.png](https://i-blog.csdnimg.cn/blog_migrate/be733dcd366d3862fa313c07ccdf0a09.png)
参考高赞答案,自己编写的发生了未知错误。
![6b716b5520da5906e76c826790dbf30d.png](https://i-blog.csdnimg.cn/blog_migrate/9340005f38fbaef676935d1b74efa91c.jpeg)
28.查找描述信息中包括robot的电影对应的分类名称以及电影数目,而且还需要该分类对应电影数量>=5部
![5c579c503b45a6f92b2a70b66bc0015f.png](https://i-blog.csdnimg.cn/blog_migrate/082582970a5977dc647baaa32dd8bb27.png)
![00a70f5da76dc546957af23ae1e8a9e2.png](https://i-blog.csdnimg.cn/blog_migrate/4ca9ddc88a35d2da03af22ff18f8ed59.png)
谁能让我看看数据长啥样啊~~
![6c4cf5eb98aa4d4405e338005887a3da.png](https://i-blog.csdnimg.cn/blog_migrate/2d9884f984aeb22c9824b5d7c664e6ee.jpeg)
29.使用join查询方式找出没有分类的电影id以及名称
![49414e213cc679aa4162b7ebe520a95b.png](https://i-blog.csdnimg.cn/blog_migrate/2964a99ec147b269782acdb1e34b16cb.png)
![7323743754b6358f01cc2088061c1be3.png](https://i-blog.csdnimg.cn/blog_migrate/1df7b3d3066ea2b0e4d7348ef92c6754.png)
解题思路:两种方法
①第一种,连接表之后用NOT IN
②第二种,识别分类id是NULL
SELECT f.film_id,f.title
FROM film f LEFT JOIN film_category fc
ON f.film_id = fc.film_id
WHERE f.film_id NOT IN (SELECT film_id FROM film_category);
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
30.使用子查询的方式找出属于Action分类的所有电影对应的title,description
![4b6e0809d7b548d74c04796dc25a00b3.png](https://i-blog.csdnimg.cn/blog_migrate/22c3a8adf33b5bc69f6fe5e8e9cf12bb.png)
![230fd63fa0b27628b9de6066d361c727.png](https://i-blog.csdnimg.cn/blog_migrate/7814cd85acea2e36ac4bb42355287f83.png)
SELECT f.title, f.description
FROM film f INNER JOIN film_category fc
ON f.film_id = fc.film_id
WHERE fc.category_id IN (SELECT category_id FROM category
WHERE name="Action")
31.获取select * from employees对应的执行计划
![ae14b62a53524278628f0f426ca647fd.png](https://i-blog.csdnimg.cn/blog_migrate/58081f407816542349a6263aac3f30d0.png)
EXPLAIN SELECT * FROM employees
可以使用 "EXPLAIN" 关键字或 "EXPLAIN QUERY PLAN" 短语,用于描述表的细节。
SQLite Explain
32.将employees表的所有员工的last_name和first_name拼接起来作为Name
![d52b2bb9c81ddfb8651926f42e0d57a9.png](https://i-blog.csdnimg.cn/blog_migrate/98902b6446dc6786f713850133c37b36.png)
SELECT last_name||" "||first_name AS Name
FROM employees
注:有些版本还可以用CONCAT
33.创建一个actor表,包含如下信息
![6e3d4870c86e88d66c475dd9f15facd3.png](https://i-blog.csdnimg.cn/blog_migrate/e935644c5390831b09362e9843720d0d.png)
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')))
34.批量插入数据
![c5a5c5eea2a512534c3e3b375c3bde87.png](https://i-blog.csdnimg.cn/blog_migrate/e7a0efef1fb79db93573577cdd54bd82.jpeg)
INSERT INTO actor VALUES(1,'PENELOPE','GUINESS','2006-02-15 12:34:33'),
(2, 'NICK', 'WAHLBERG', '2006-02-15 12:34:33')
35.批量插入数据,不使用replace操作
![62e0870c250e788a7537ed21f0562c3e.png](https://i-blog.csdnimg.cn/blog_migrate/5e0267fdba437491cb42888bea307135.png)
INSERT OR IGNORE INTO actor
VALUES(3,'ED','CHASE','2006-02-15 12:34:33');
36.创建一个actor_name表
![90c8a1168cd0b078746e3f2b8a85e807.png](https://i-blog.csdnimg.cn/blog_migrate/bdc2b053dd36f5311983c4306d3f4bc3.png)
CREATE TABLE actor_name
(
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL
);
INSERT INTO actor_name (first_name,last_name)
SELECT first_name, last_name FROM actor;
37.对first_name创建唯一索引uniq_idx_firstname,对last_name创建普通索引idx_lastname
![62d216edf2483da50cb28fcda3989be4.png](https://i-blog.csdnimg.cn/blog_migrate/1db14a3ef9749a18d5ceb262e86f72d6.png)
CREATE UNIQUE INDEX uniq_idx_firstname ON actor(first_name);
CREATE INDEX idx_lastname ON actor(last_name);
注:
①加UNIQUE是创建唯一索引。
②索引好像得一条一条的加,也可能是牛客网这个编辑器的问题。
38.针对actor表创建视图actor_name_view
![1f55b56554a4c3fb0173de2b07f311c5.png](https://i-blog.csdnimg.cn/blog_migrate/48564bb43405094666002e886e0db67d.png)
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, 使用强制索引
![9a28f4e21f2b2bd5ee9d7e8ad390ae80.png](https://i-blog.csdnimg.cn/blog_migrate/dbb3946360a69844e5b0c0ce8abcc2e6.png)
SELECT * FROM salaries
INDEXED BY idx_emp_no
WHERE emp_no='10005';
注:
①sqlLite 使用 indexed by 进行强制索引 SQLite Indexed By
②mysql 使用 force index 进行强制索引
40.在lastupdate后面增加一列名字为create_date
![7dda899899dd4bb64f6b377841abb545.png](https://i-blog.csdnimg.cn/blog_migrate/e349e3708ec491aa7fd0d03b24876bea.png)
ALTER TABLE actor
ADD COLUMN create_date datetime NOT NULL DEFAULT '0000-00-00 00:00:00';
注:MySQL中有AFTER参数可以指定放在哪一列后边
41.构造一个触发器audit_log
![b299c6c853c7b436f4972825e5198d55.png](https://i-blog.csdnimg.cn/blog_migrate/5983aa78818e1a693f916114865e27ec.png)
CREATE TRIGGER audit_log AFTER INSERT
ON employees_test FOR EACH ROW
BEGIN
INSERT INTO audit VALUES(NEW.ID,NEW.NAME);
END;
42.删除emp_no重复的记录,只保留最小的id对应的记录
![d02db184b0ea2d004dbefe4ce56b1454.png](https://i-blog.csdnimg.cn/blog_migrate/78127a31a252554a43ca894d894fc531.png)
DELETE FROM titles_test
WHERE id NOT IN (SELECT MIN(id) FROM titles_test)
43.将所有to_date为9999-01-01的全部更新为NULL
![ad53c017645f2eafc547bffbcc1b0d49.png](https://i-blog.csdnimg.cn/blog_migrate/117119a2a9c5a865edc795fcbe124f54.png)
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
![1826d1fd41ee1bbbf915e873684f093b.png](https://i-blog.csdnimg.cn/blog_migrate/de8d4e96313a7320d2cd8c8b994c43fd.png)
UPDATE titles_test
SET emp_no = REPLACE(emp_no,10001,10005)
WHERE id = 5
注:
Sqlite数据库字符串处理函数replace
replace(X,Y,Z) ,x:要处理的字符串,y:被替换的字符串,z:替换后的字符串
45.将titles_test表名修改为titles_2017
![caa4c862553582fc78f9c85d20d513d3.png](https://i-blog.csdnimg.cn/blog_migrate/2847285fac7b388fb6467441de637014.png)
ALTER TABLE titles_test RENAME TO titles_2017
46.在audit表上创建外键约束,其emp_no对应employees_test表的主键id
![57a4f87739ec7ef5d4a625abe8ad7e95.png](https://i-blog.csdnimg.cn/blog_migrate/1d23556e62c17892711b0c3dccae132c.png)
DROP TABLE audit;
CREATE TABLE audit(
EMP_no INT NOT NULL,
create_date datetime NOT NULL,
FOREIGN KEY(EMP_no) REFERENCES employees_test(ID));
注:牛客网的这套系统不能用MySQL的ALTER TABLE <表名> ADD [CONSTRAINT <约束名>] FOREIGN KEY(外键字段名)REFERENGCES 被参照表(主键字段名)
47.如何获取emp_v和employees有相同的数据no
![507661530f055d4c5b83b81366074197.png](https://i-blog.csdnimg.cn/blog_migrate/a4710b0a8be0389ba389d57a2888925e.png)
SELECT ev.*
FROM emp_v ev,employees em
WHERE ev.emp_no = em.emp_no;
48.将所有获取奖金的员工当前薪水增加10%
![c94e0b71e743bfe2d6d10687fa3f907d.png](https://i-blog.csdnimg.cn/blog_migrate/cea310c6399a9a2e3f62bca8a30f6dc7.png)
UPDATE salaries SET salary = salary*1.1
WHERE emp_no IN (SELECT emp_no FROM emp_bonus)
49.针对库中的所有表生成select count(*)对应的SQL语句
![eb4cdacb13f84487c248abdf707ee37e.png](https://i-blog.csdnimg.cn/blog_migrate/a92298f432337f653332712d862863e2.png)
SELECT "select count(*) from "||name||";" AS cnts FROM sqlite_master
WHERE type='table'
注:在SQLite系统表sqlite_master中可以获得所有表的索引,其中name表示表的名字,type=‘table’表示当前查找的是表,而type的值永远都是table。
50.将employees表中的所有员工的last_name和first_name通过(')连接起来。
![68319ac7b27a06db95048ec26f57bbb3.png](https://i-blog.csdnimg.cn/blog_migrate/dbc9a850b2c0cf788fd92e199d0657af.png)
![2eac1afe3e240618a0e7f4b69bcba251.png](https://i-blog.csdnimg.cn/blog_migrate/16a575b75ea0026ef95acca74bb692f5.png)
SELECT last_name||"'"||first_name AS name
FROM employees
51.查找字符串'10,A,B'中逗号','出现的次数cnt
题目描述:查找字符串'10,A,B' 中逗号','出现的次数cnt。
SELECT length('10,A,B') - length(replace('10,A,B',",",""))
52.获取Employees中的first_name
![b64d25343ab0771fa79f45645a159e63.png](https://i-blog.csdnimg.cn/blog_migrate/a578334f46e8e280008bc73a567dbdb2.png)
SELECT first_name
FROM employees
ORDER BY SUBSTR(first_name,length(first_name)-1,2)
注:
解释来自
substr函数的用法,取得字符串中指定起始位置和长度的字符串 ,默认是从起始位置到结束的子串。
substr( string, start_position, [ length ] ) substr('目标字符串',开始位置,长度)
如:
substr('This is a test', 6, 2) would return 'is'
substr('This is a test', 6) would return 'is a test'
substr('TechOnTheNet', -3, 3) would return 'Net'
substr('TechOnTheNet', -6, 3) would return 'The'select substr('Thisisatest', -4, 2) value from dual
53.按照dept_no进行汇总
![f9e3ed5c6f142da3ed6d4e2209101119.png](https://i-blog.csdnimg.cn/blog_migrate/a02d36f1a9e76bc4daa28228b4976645.png)
SELECT dept_no,group_concat(emp_no) AS employees
FROM dept_emp
GROUP BY dept_no
54.查找排除当前最大、最小salary之后的员工的平均工资avg_salary
![faad67d0f960232f810fd730d57432ae.png](https://i-blog.csdnimg.cn/blog_migrate/1073e09b25e48a51b20aa37fc20b75d6.png)
SELECT AVG(salary) AS avg_salary
FROM salaries
WHERE to_date = '9999-01-01'
AND salary NOT IN (SELECT MAX(salary) FROM salaries)
AND salary NOT IN (SELECT MIN(salary) FROM salaries)
55.分页查询employees表,每5行一页,返回第2页的数据
![8f723efc50a51bf2e8f41ba97318ec4d.png](https://i-blog.csdnimg.cn/blog_migrate/379cc89de52f4219e7dfa8147c80c7db.png)
SELECT *
FROM employees
LIMIT 5,5
56.获取所有员工的emp_no
![8d763cc4a784f085d850e71b8b1e28e6.png](https://i-blog.csdnimg.cn/blog_migrate/324d7c7ec91290cc602ff487ab8b33cf.png)
![f454d5d41a05396808846054bcce4c6e.png](https://i-blog.csdnimg.cn/blog_migrate/1266eb71189d8160e2d67b66d8ec2d40.png)
SELECT de.emp_no, de.dept_no, eb.btype, eb.recevied
FROM dept_emp AS de LEFT JOIN emp_bonus AS eb
ON de.emp_no = eb.emp_no
注:神题~~没找到emp_bonus表,还是看了讨论才知道的
57.使用含有关键字exists查找未分配具体部门的员工的所有信息
![7a414b06a604c4e3a70cc5eaa26978bf.png](https://i-blog.csdnimg.cn/blog_migrate/93af50e6e339edf3f2ad7098c11a7494.jpeg)
SELECT *
FROM employees
WHERE NOT EXISTS(SELECT emp_no
FROM dept_emp
WHERE emp_no = employees.emp_no)
58.获取employees中的行数据,且这些行也存在于emp_v中
![0fb0d887f75f5e606b9c32e477382575.png](https://i-blog.csdnimg.cn/blog_migrate/bdf929e28bc1f631527edb8b80904c7a.jpeg)
SELECT em.*
FROM employees AS em, emp_v AS ev
WHERE em.emp_no = ev.emp_no
59.获取有奖金的员工相关信息
![f261bd5185dbd5e5d025d774723ea587.png](https://i-blog.csdnimg.cn/blog_migrate/6f71c12ec584c36cc3c92b5dc020cf12.png)
![195683fb79edbafdae020c2a7b27b579.png](https://i-blog.csdnimg.cn/blog_migrate/f8b57c486934f022cc2ed35b824eeaec.jpeg)
SELECT em.emp_no, em.first_name, em.last_name, eb.btype, sal.salary,
(CASE eb.btype
WHEN 1 THEN sal.salary * 0.1
WHEN 2 THEN sal.salary * 0.2
ELSE sal.salary * 0.3 END) AS bonus
FROM employees AS em INNER JOIN emp_bonus AS eb
ON em.emp_no = eb.emp_no
INNER JOIN salaries AS sal
ON em.emp_no = sal.emp_no
AND sal.to_date = '9999-01-01'
注:最好自己做一个连接图表示表与表之间的联系,这道题很明显没用到dept_emp表。
60.统计salary的累计和running_total
![51ae79495d88af411cf035455313a0f8.png](https://i-blog.csdnimg.cn/blog_migrate/5397a9d71011ad23019d38d316879609.png)
SELECT s1.emp_no, s1.salary,
(SELECT SUM(s2.salary)
FROM salaries AS s2
WHERE s2.emp_no <= s1.emp_no
AND s2.to_date = '9999-01-01') AS running_total
FROM salaries AS s1
WHERE s1.to_date = '9999-01-01'
ORDER BY s1.emp_no;
61.对于employees表中,给出奇数行first_name
![21e8937601f54b2b689afebcbafb5176.png](https://i-blog.csdnimg.cn/blog_migrate/ad741b39fed0e44f9b709cf43a57f941.png)
SELECT first_name FROM (
SELECT e2.first_name,
(SELECT COUNT(*)
FROM employees e1
WHERE e1.first_name <= e2.first_name ) AS rownum
FROM employees e2
WHERE rownum % 2 =1
);