牛客网SQL练习题（Mysql-8）

文章目录

• 单表
• • SQL1
  • SQL 2
  • SQL 7：错误次数 1
  • SQL17
  • SQL 32：出错次数 1
  • SQL 34：出错次数 1
  • SQL 42：出错次数 1
  • SQL43：出错次数 1
  • SQL 45：出错次数 1
  • SQL 62
  • SQL 66
  • SQL 72：出错次数 1
  • SQL 77：出错次数 1
  • SQL 84：出错次数 1
• 两个表
• • SQL 3
  • SQL 4
  • SQL 5
  • SQL 8：错误次数 1
  • SQL10：错误次数 1
  • SQL11：出错次数 1
  • SQL15：出错次数 1
  • SQL16
  • SQL 64
• 三个表
• • SQL19：出错次数 1
  • SQL22 ！

单表

SQL1

题目：最大

答案1

SELECT *
FROM employees
ORDER BY hire_date DESC
LIMIT 1;
           

答案2：考虑到最晚雇佣不止一个员工

SELECT *
FROM employees
WHERE hire_date = (SELECT MAX(hire_date)
        			FROM employees);
           

SQL 2

题目：第三大

LIMIT m,n

SELECT *
FROM employees
WHERE hire_date = (SELECT hire_date
			        FROM employees
			        ORDER BY hire_date DESC
			        LIMIT 2, 1);
           

SQL 7：错误次数 1

题目：group by

• GROUP BY
• 聚集函数（count）不可用于WHERE语句中，只能用在 HAVING 中。

答案

SELECT emp_no, count(emp_no) AS t
FROM salaries
GROUP BY emp_no HAVING t>15;
           

错误示例

SELECT emp_no, count(emp_no) AS t
FROM salaries
WHERE count(emp_no)>15;
           

SQL17

题目：第二大

考虑到第二高工资不止一个员工

SELECT emp_no, salary
FROM salaries
WHERE salary = (SELECT salary
                FROM salaries
                ORDER BY salary DESC
                LIMIT 1,1);
           

SQL 32：出错次数 1

拼接

SELECT CONCAT(last_name,' ',first_name) AS Name
FROM employees;
           

SQL 34：出错次数 1

题目：批量insert

INSERT INTO actor
VALUES (1,
        'PENELOPE',
        'GUINESS',
        '2006-02-15 12:34:33'),
        (2,
        'NICK',
        'WAHLBERG',
        '2006-02-15 12:34:33');
           

SQL 42：出错次数 1

题目：delete, 子查询

mysql 中，delete、update 数据中若出现 select子句，不能为同一张表

解决：将子语句作为 from 表再包裹一层，

select * from（子句） as t

，给表起别名

答案：

DELETE FROM titles_test
WHERE id NOT IN (SELECT *
                 FROM (SELECT MIN(id)
                FROM titles_test
                GROUP BY emp_no)AS t);		# 起别名
           

错误示例：

DELETE FROM titles_test
WHERE id NOT IN (SELECT MIN(id)
                FROM titles_test			# 不能与 delete 同一张表
                GROUP BY emp_no);

           

SQL43：出错次数 1

update

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

SQL 45：出错次数 1

改名

SQL 62

题目：group by

SELECT number
FROM grade
GROUP BY number HAVING COUNT(*)>=3;
           

SQL 66

题目：group by

SELECT user_id, MAX(date) AS d
FROM login
GROUP BY user_id
ORDER BY user_id;
           

SQL 72：出错次数 1

题目：group by、保留小数点

处理函数 round()：保留小数几位

SELECT job, ROUND(AVG(score),3) AS avg
FROM grade
GROUP BY job
ORDER BY avg DESC;
           

SQL 77：出错次数 1

题目：比较时间

处理函数 datediff()：计算两个date相差几天

SELECT *
FROM order_info
WHERE DATEDIFF(date, '2025-10-15')>0            # date>'2025-10-15' 
AND status='completed'
AND product_name IN ('Java', 'Python', 'C++')
ORDER BY id;
           

SQL 84：出错次数 1

题目：group by、时间

SELECT job, sum(num) AS cnt
FROM resume_info
WHERE YEAR(date)=2025
GROUP BY job
ORDER BY cnt DESC;
           

两个表

SQL 3

题目：inner join

SELECT salaries.*, dept_no
FROM salaries INNER JOIN dept_manager
ON salaries.emp_no = dept_manager.emp_no
ORDER BY salaries.emp_no;
           

SQL 4

题目：inner join

联合查询

答案1

SELECT last_name, first_name, dept_no
FROM employees, dept_emp
WHERE dept_emp.emp_no = employees.emp_no;
           

答案2：与答案1等效

SELECT last_name, first_name, dept_no
FROM employees 
INNER JOIN dept_emp
ON dept_emp.emp_no = employees.emp_no;
           

SQL 5

题目：left join

SELECT last_name, first_name, dept_no
FROM employees LEFT JOIN dept_emp
ON employees.emp_no = dept_emp.emp_no;
           

SQL 8：错误次数 1

题目：distinct 和 group by

• GROUP BY 可代替 DISTINCT （答案1、2）
• WHERE 和 HAVING 的区别（答案3、答案4）

答案1：不建议用 DISTINCT ，效率低

SELECT DISTINCT salary
FROM salaries
ORDER BY salary DESC;
           

答案2：推荐

SELECT salary
FROM salaries
GROUP BY salary 
ORDER BY salary DESC;
           

SQL10：错误次数 1

题目：left join，差

• 多表查询尽量用 JOIN ON，效率更高（答案1、2）
• LEFT JOIN … ON…WHERE …IS NULL

答案1

SELECT emp_no
FROM employees
WHERE emp_no NOT IN (SELECT emp_no
                    FROM dept_manager);
           

答案2

SELECT employees.emp_no
FROM employees LEFT JOIN dept_manager
ON employees.emp_no = dept_manager.emp_no
WHERE dept_no IS NULL;
           

SQL11：出错次数 1

题目：两个表有两个列相关（两个外键）, left join

SELECT t1.emp_no, t2.emp_no AS manager
FROM dept_emp AS t1 LEFT JOIN dept_manager AS t2
ON t1.dept_no = t2.dept_no
WHERE t1.emp_no != t2.emp_no;
           

SQL15：出错次数 1

题目：奇数

• 过滤奇数：取余为1

SELECT *
FROM employees
WHERE emp_no%2=1 AND last_name!='Mary'
ORDER BY hire_date DESC;
           

SQL16

题目：inner join

SELECT t.title, AVG(s.salary)
FROM titles AS t INNER JOIN salaries AS s
ON t.emp_no = s.emp_no
GROUP BY title
ORDER BY AVG(s.salary);
           

SQL 64

题目：left join

SELECT person.id, person.name, task.content
FROM person LEFT JOIN task
ON person.id = task.person_id
ORDER BY person.id;
           

三个表

SQL19：出错次数 1

三个表的联结

SELECT e.last_name, e.first_name, d2.dept_name
FROM (employees AS e LEFT JOIN dept_emp AS d1 
      ON e.emp_no = d1.emp_no)
LEFT JOIN departments AS d2
ON d1.dept_no = d2.dept_no;
           

SQL22 ！

SELECT d2.dept_no, d2.dept_name, COUNT(s.salary) AS sum
FROM (dept_emp AS d1 INNER JOIN salaries AS s
     ON d1.emp_no = s.emp_no) INNER JOIN departments AS d2
ON d1.dept_no = d2.dept_no
GROUP BY d2.dept_no
ORDER BY d2.dept_no;