sql在线编程题回顾_(题目来自牛客网)

自己的sql水平是真的菜，当时的时候也没有好好的重视，现在发现很多笔试之类的sql语句都没有办法写出来，还有啥好说的，好好练练呗——这个板块就权当是用来复习用的，因为有时候一股劲的做个几道题但是没有好好的回顾，没有好好的思考，也并不会有多大的收获（因为我自己存在这样的一种情况，当有难题时，不知不觉就变成了只要AC了就行了，完全不考虑方法以及思路的总结，这样的坏习惯估计是高一开始养成的所谓惰性循环吧估计——匪交匪舒，天子所予 。当是个提示吧）

1.

链接：https://www.nowcoder.com/questionTerminal/2435cc7b43c94d3b88ffbcfadc0241de
来源：牛客网

由于视图 emp_v 的记录是从 employees 中导出的，所以要判断两者中相等的数据，只需要判断emp_no相等即可。

方法一：用 WHERE 选取二者 emp_no 相等的记录

1	SELECT em.* FROM employees AS em, emp_v AS ev WHERE em.emp_no = ev.emp_no

方法二：用 INTERSECT 关键字求 employees 和 emp_v 的交集

可参考：http://www.sqlite.org/lang_select.html

1	SELECT * FROM employees INTERSECT SELECT * FROM emp_v

方法三：仔细一想，emp_v的全部记录均由 employees 导出，因此可以投机取巧，直接输出 emp_v 所有记录

1	SELECT * FROM emp_v

【错误方法：】用以下方法直接输出 *，会得到两张表中符合条件的重复记录，因此不合题意，必须在 * 前加表名作限定

1	SELECT * FROM employees, emp_v WHERE employees.emp_no = emp_v.emp_no

2.

链接：https://www.nowcoder.com/questionTerminal/f24966e0cb8a49c192b5e65339bc8c03
来源：牛客网

根据题意，每行5页，返回第2页的数据，即返回第6~10条记录，以下有两种方法可以解决：

方法一：利用 LIMIT 和 OFFSET 关键字。LIMIT 后的数字代表返回几条记录，OFFSET 后的数字代表从第几条记录开始返回（第一条记录序号为0），也可理解为跳过多少条记录后开始返回。

1	SELECT * FROM employees LIMIT 5 OFFSET 5

方法二：只利用 LIMIT 关键字。 注意：在 LIMIT X,Y 中，Y代表返回几条记录，X代表从第几条记录开始返回（第一条记录序号为0），切勿记反。

1	SELECT * FROM employees LIMIT 5 , 5

然后基于页数的考虑：

select * from employees

limit (2 - 1) * 5, 5;

3.

链接：https://www.nowcoder.com/questionTerminal/74d90728827e44e2864cce8b26882105
来源：牛客网

获取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`));
输出格式：

first_name
Chirstian
Tzvetan
Bezalel
Duangkaew
Georgi
Kyoichi
Anneke
Sumant
Mary
Parto
Saniya

解析:

链接：https://www.nowcoder.com/questionTerminal/74d90728827e44e2864cce8b26882105
来源：牛客网
排序的话主要体现在ORDER BY

本题考查 substr(X,Y,Z) 或 substr(X,Y) 函数的使用。其中 X是要截取的字符串。 Y是字符串的起始位置（注意第一个字符的位置为1，而不为0），取值范围是±(1~length(X))，当Y等于length(X)时，则截取最后一个字符；当Y等于负整数-n时，则从倒数第n个字符处截取。 Z是要截取字符串的长度，取值范围是正整数，若Z省略，则从Y处一直截取到字符串末尾；若Z大于剩下的字符串长度，也是截取到字符串末尾为止。

1	SELECT first_name FROM employees ORDER BY substr(first_name,length(first_name)- 1 )

1	SELECT first_name FROM employees ORDER BY substr(first_name,- 2 )

4.

题目描述

查找字符串'10,A,B' 中逗号','出现的次数cnt。

链接： https://www.nowcoder.com/questionTerminal/e3870bd5d6744109a902db43c105bd50
来源：牛客网

由于 SQLite 中没有直接统计字符串中子串出现次数的函数，因此本题用 length()函数与replace()函数的结合灵活地解决了统计子串出现次数的问题，属于技巧题，即 先用replace函数将原串中出现的子串用空串替换，再用原串长度减去替换后字符串的长度，最后除以子串的长度（本题中此步可省略，若子串长度大于1则不可省）。详情请参考：

http://www.cnblogs.com/huangtailang/p/5cfbd242cae2bcc929c81c266d0c875b.html

http://sqlite.org/lang_corefunc.html#replace

1	SELECT (length( "10,A,B" )-length(replace( "10,A,B" , "," , "" )))/length( "," ) AS cnt

还可以利用OJ系统的Bug 直接输出2次来通过测试

2	SELECT 2 AS cnt

5.

题目描述

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

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

链接：https://www.nowcoder.com/questionTerminal/2bec4d94f525458ca3d0ebf3bc8cd240
来源：牛客网

本题运用 REPLACE 有两种解法

方法一：全字段更新替换。由于 REPLACE 的新记录中 id=5，与表中的主键 id=5 冲突，故会替换掉表中 id=5 的记录，否则会插入一条新记录（例如新插入的记录 id = 10）。并且要将所有字段的值写出，否则将置为空。可参考：

http://blog.csdn.net/zhangjg_blog/article/details/23267761

1	REPLACE INTO titles_test VALUES ( 5 , 10005 , 'Senior Engineer' , '1986-06-26' , '9999-01-01' )

方法二：运用REPLACE(X,Y,Z)函数。其中X是要处理的字符串，Y是X中将要被替换的字符串，Z是用来替换Y的字符串，最终返回替换后的字符串。以下语句用 UPDATE和REPLACE 配合完成，用REPLACE函数替换后的新值复制给 id=5 的 emp_no。REPLACE的参数为整型时也可通过。可参考：

http://www.cnblogs.com/huangtailang/p/5cfbd242cae2bcc929c81c266d0c875b.html

http://sqlite.org/lang_corefunc.html#replace

1	UPDATE titles_test SET emp_no = REPLACE(emp_no, 10001 , 10005 ) WHERE id = 5

/** 另外可以利用OJ系统的漏洞，不用 REPLACE 实现  **/

1	UPDATE titles_test SET emp_no = 10005 WHERE id = 5

6.

链接：https://www.nowcoder.com/questionTerminal/6e86365af15e49d8abe2c3d4b5126e87
来源：牛客网

按照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	employees
d001	10001,10002
d002	10006
d003	10005
d004	10003,10004
d005	10007,10008,10010
d006	10009,10010

链接：https://www.nowcoder.com/questionTerminal/6e86365af15e49d8abe2c3d4b5126e87
来源：牛客网

本题要用到SQLite的 聚合函数group_concat(X,Y)，其中X是要连接的字段，Y是连接时用的符号，可省略，默认为逗号。 此函数必须与 GROUP BY 配合使用。此题以 dept_no 作为分组，将每个分组中不同的emp_no用逗号连接起来（即可省略Y）。可参考：

http://www.sqlite.org/lang_aggfunc.html#groupconcat

http://blog.csdn.net/langzxz/article/details/16807859

1 2	SELECT dept_no, group_concat(emp_no) AS employees FROM dept_emp GROUP BY dept_no

select  dept_no ,group_concat(emp_no)  as employees  from dept_emp  group by dept_no

7.

题目描述

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

链接：https://www.nowcoder.com/questionTerminal/3d92551a6f6d4f1ebde272d20872cf05
来源：牛客网

本题思路如下：先用 GROUP BY 和 MIN() 选出每个 emp_no 分组中最小的 id，然后用 DELETE FROM ... WHERE ... NOT IN ... 语句 删除 “非每个分组最小id对应的所有记录”

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

8.

将titles_test表名修改为titles_2017。

改变表名 - ALTER TABLE 旧表名 RENAME TO 新表名 

增加一列 - ALTER TABLE 表名 ADD COLUMN 列名 数据类型

9.

将employees表中的所有员工的last_name和first_name通过(')连接起来。

链接：https://www.nowcoder.com/questionTerminal/810bf4ee3ac64949b08983aa66ec7bee
来源：牛客网

在本题所用的SQLite数据库中，只支持用 连接符号"||" 来连接字符串，不支持用函数连接

1	SELECT last_name || "'" || first_name FROM employees

10.

题目描述

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

输入描述:

无

输出描述:

title	t
Assistant Engineer	2
Engineer	4
省略	省略
Staff	3

链接：https://www.nowcoder.com/questionTerminal/72ca694734294dc78f513e147da7821e
来源：牛客网

此题应注意以下三点：

1、用COUNT()函数和GROUP BY语句可以统计同一title值的记录条数

2、根据题意，输出每个title的个数为t，故用AS语句将COUNT(title)的值转换为t

3、由于WHERE后不可跟COUNT()函数，故用HAVING语句来限定t>=2的条件

1 2	SELECT title, COUNT(title) AS t FROM titles GROUP BY title HAVING t >= 2