牛客网学习SQL在线编程，牛客网在线编程，一共82道
用于实践的网站，在线运行SQL
目前43道，刷不动了，剩下的之后找机会搞
只记录有疑问的题目
简单
SQL196 查倒数第三
查找入职员工时间排名倒数第三的员工所有信息
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
);
不解
排行榜前几的代码都是这样，这个代码确实解决了倒数第一和倒数第二存在多条记录的问题，但如果倒数第三存在多条记录，LIMIT只取一条记录，所以只取了一个人啊？
通过实践，发现LIMIT是取一条记录，但小括号中的一个值给到hire_date。就可以匹配多条记录了
实践
CREATE TABLE Person (
PersonID int,
City int
);
INSERT INTO Person VALUES (1, 1);
INSERT INTO Person VALUES (2, 1);
INSERT INTO Person VALUES (3, 2);
INSERT INTO Person VALUES (4, 2);
INSERT INTO Person VALUES (5, 3);
INSERT INTO Person VALUES (6, 3);
INSERT INTO Person VALUES (7, 3);
SELECT *
FROM Person
WHERE City = (
SELECT DISTINCT City
FROM Person
LIMIT 2,1
);
查询结果
5|3
6|3
7|3
SQL201 GROUP BY
查找薪水记录超过15条的员工号emp_no以及其对应的记录次数t
SELECT emp_no, COUNT(*)
FROM salaries
GROUP BY emp_no
HAVING COUNT(*)>15;
注
使用聚合函数（SUM\COUNT…），必须使用GROUP BY和HAVING，不然会报错
SQL204 查非……
获取所有非manager的员工emp_no
SELECT emp_no
FROM employees
WHERE emp_no NOT IN (
SELECT emp_no
FROM dept_manager
);
注
NOT IN+子查询
SQL211 查薪资第二
获取当前薪水第二多的员工的emp_no以及其对应的薪水salary
SELECT emp_no, salary
FROM salaries
WHERE salary = (
SELECT salary
FROM salaries
GROUP BY salary
ORDER BY salary DESC
LIMIT 1,1
)
理解
- 因为同一薪资可能有多名员工，所以用
GROUP BY - 找薪资高的，用倒序
- emp_no默认就是升序
SQL228 批量插入
批量插入数据
INSERT INTO actor(actor_id, first_name, last_name, last_update)
VALUES ('1', 'PENELOPE', 'GUINESS', '2006-02-15 12:34:33'),
('2', 'NICK', 'WAHLBERG', '2006-02-15 12:34:33');
注
- 列名没引号，值有引号
- 插入多条数据用
,分隔
SQL236 删重复，保留最小id
删除emp_no重复的记录，只保留最小的id对应的记录。
DELETE FROM titles_test
WHERE id NOT IN (
SELECT * FROM (
SELECT MIN(id)
FROM titles_test
GROUP BY emp_no
)a
);
注
- 保留最小，就删掉NOT IN最小
- 不能变查边删：
DELETE FROM titles_test
WHERE id NOT IN(
SELECT MIN(id)
FROM titles_test
GROUP BY emp_no
);
只能将得出的新表重命名
SQL238 replace
将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005
UPDATE titles_test
SET emp_no=REPLACE(emp_no, 10001, 10005)
WHERE id=5;
SQL239 修改表名
将titles_test表名修改为titles_2017
alter table titles_test rename to titles_2017;
SQL258 外连接
找到每个人的任务
SELECT P.id, P.name, T.content
FROM person AS P LEFT OUTER JOIN task AS T
ON P.id=T.person_id;
SQL260 max
牛客每个人最近的登录日期(一)
SELECT user_id, MAX(date)
FROM login
GROUP BY user_id
ORDER BY user_id;
注
- MAX函数直接写在要查询的内容中
- ORDER BY还是要加的，不能偷懒
SQL266 保留三位小数
考试分数(一)
SELECT job, ROUND(AVG(score),3) as s
FROM grade
GROUP BY job
ORDER BY s DESC;
中等
SQL205 获取部门经理
获取所有员工当前的manager
SELECT E.emp_no, M.emp_no AS manager
FROM dept_emp AS E, dept_manager AS M
WHERE E.dept_no = M.dept_no
AND E.emp_no != M.emp_no;
SQL213 三表外连接
查找所有员工的last_name和first_name以及对应的dept_name
SELECT E.last_name, E.first_name, a.dept_name
FROM employees AS E LEFT OUTER JOIN (
SELECT D.dept_no, D.dept_name, DE.emp_no //注意
FROM departments AS D, dept_emp AS DE
WHERE D.dept_no = DE.dept_no
)AS a
ON E.emp_no = a.emp_no;
注
- 三表外连接查询，先两表连接命名为a，再外连接
- 两表连接时不能SELECT *，因为两表都有dept_no属性
SQL223 NULL
使用join查询方式找出没有分类的电影id以及名称
SELECT F.film_id, F.title
FROM film AS F LEFT OUTER JOIN film_category AS FC
ON F.film_id = FC.film_id
WHERE FC.category_id is NULL;
注
- 查询没有某属性的记录，先外连接，然后通过WHERE筛选
SQL224 子查询
使用子查询的方式找出属于Action分类的所有电影对应的title,description
SELECT F.title, F.description
FROM film AS F LEFT OUTER JOIN film_category AS FC
ON F.film_id = FC.film_id
WHERE FC.film_id IN (
SELECT FC.film_id
FROM category AS C LEFT OUTER JOIN film_category AS FC
ON C.category_id = FC.category_id
WHERE C.name = 'Action'
);
注
- 记得连接要加ON
- 查出多条语句的话用IN
SQL231 建立索引
对first_name创建唯一索引uniq_idx_firstname
ALTER TABLE actor ADD UNIQUE uniq_idx_firstname(first_name);
ALTER TABLE actor ADD INDEX idx_lastname(last_name);
SQL232 创建视图
针对actor表创建视图actor_name_view
CREATE VIEW actor_name_view (first_name_v, last_name_v) AS
SELECT first_name, last_name
FROM actor;
SQL233 强制索引
针对上面的salaries表emp_no字段创建索引idx_emp_no
SELECT *
FROM salaries
FORCE INDEX (idx_emp_no)
WHERE emp_no = 10005;
补充：
-
另一种添加索引的语法：
create index idx_emp_no on salaries(emp_no); -
为什么要使用强制索引
SQL234 添加新列
在last_update后面新增加一列名字为create_date
ALTER TABLE actor ADD COLUMN
create_date datetime NOT NULL DEFAULT '2020-10-01 00:00:00';
SQL235 触发器
构造一个触发器audit_log
CREATE TRIGGER audit_log
AFTER INSERT ON employees_test
FOR EACH ROW
BEGIN
INSERT INTO audit VALUES(new.ID, new.NAME);
END
理解
- 这个触发器就是向employees_test插入数据时，取数据的前两列，插入到audit表中
-
AFTER表示触发时间，INSERT表示触发事件
SQL240 外键
在audit表上创建外键约束，其emp_no对应employees_test表的主键
ALTER TABLE audit
ADD CONSTRAINT FOREIGN KEY (emp_no)
REFERENCES employees_test(id);
SQL242 薪水增加10%
将所有获取奖金的员工当前的薪水增加10%
UPDATE salaries AS s INNER JOIN emp_bonus as e
ON s.emp_no = e.emp_no
SET salary = salary * 1.1
WHERE to_date = '9999-01-01';
SQL244 concat
将employees表中的所有员工的last_name和first_name通过引号连接起来
SELECT CONCAT(RTRIM(last_name),"'",LTRIM(first_name)) AS name
FROM employees;
SQL245 逗号次数
查找字符串中逗号出现的次数
SELECT id, LENGTH(string)-LENGTH(REPLACE(string, ',', '')) AS cnt
FROM strings;
SQL246 取两个字母
获取employees中的first_name
SELECT first_name
FROM employees
ORDER BY RIGHT(first_name, 2);
SQL247 分组合并字符串
按照dept_no进行汇总文章来源：https://www.toymoban.com/news/detail-446923.html
SELECT dept_no, GROUP_CONCAT(emp_no) AS employees
FROM dept_emp
GROUP BY dept_no;
SQL248 去除部分值取平均
平均工资文章来源地址https://www.toymoban.com/news/detail-446923.html
SELECT (SUM(salary)-MAX(salary)-MIN(salary)) / (COUNT(1)-2) AS avg_salary
FROM salaries
WHERE to_date = '9999-01-01';
到了这里，关于SQL在线刷题的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
