牛客网-SQL数据库实战题1

2021/11/11
LEFT JOIN 关键字从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL。

select * from employees as e left join dept_manager as d on e.emp_no = d.emp_no

select concat(last_name,’ ',first_name) from employees
mysql concat拼接字符串

SQL42 删除emp_no重复的记录，只保留最小的id对应的记录。

正向思维：找出所有需要删除的记录的id

step1:找出所有出现的频次>1 emp_no 及最小id

1
2 SELECT MIN(id) AS id,emp_no FROM titles_test
GROUP BY emp_no HAVING COUNT(emp_no) > 1
±-----±-------+
|    1 |  10001 |
|    2 |  10002 |
|    3 |  10003 |
±-----±-------+

step2:联合titles_test 找出所有 需要删除的id

1
2
3
4 SELECT a.id FROM titles_test a ,
    (SELECT MIN(id) AS id,emp_no FROM titles_test
        GROUP BY emp_no HAVING COUNT(emp_no) > 1) AS b
WHERE a.emp_no = b.emp_no AND a.id > b.id
±—+
|  5 |
|  6 |
|  7 |
±—+

step3:根据id删除数据

1
2
3
4
5
6
7 DELETE FROM titles_test WHERE id IN (
    SELECT id FROM(
                SELECT a.id FROM titles_test a ,
                   (SELECT MIN(id) AS id,emp_no FROM titles_test
                    GROUP BY emp_no HAVING COUNT(emp_no) > 1) AS b
                WHERE a.emp_no = b.emp_no AND a.id > b.id )t
)

逆向思维：找出所有emp_no组中最小的那些id

step1: 找出所有emp_no组中最小的那些id

1 SELECT MIN(id) FROM titles_test GROUP BY emp_no

step2: 根据id删除数据

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

先按照user_id分组，并选出每个组最大的date的情况。 后面再排序

SQL72 考试分数(一)

select job, ROUND(avg(score),3) as avg from grade group by job order by avg(score) desc

考点：按job分组，求每个组的平均分，round()函数

SQL 84  实习广场投递简历分析(一)

请你写出SQL语句查询在2025年内投递简历的岗位和数量，并且按数量降序排序

考点：按job分组，降序 ORDER BY DESC，日期在2025年
筛选出2025年即可，可以使用
1
2
3
4
5
6
7 like ‘2025%’
或
year(date) = 2025
或
between ‘2025-01-01’ and ‘2025-12-31’
或
date >= ‘2025-01-01’ and date <= ‘2025-12-31’

SQL中等难度
SQL 5

INNER JOIN 两边表同时有对应的数据，即任何一边缺失数据就不显示。
LEFT JOIN 会读取左边数据表的全部数据，即便右边表无对应数据。
RIGHT JOIN 会读取右边数据表的全部数据，即便左边表无对应数据。

SQL11 获取所有员工当前的manager

2021-11-12
SQL19 查找所有员工的last_name和first_name以及对应的dept_name
两次left join 连表查询

SQL22 统计各个部门的工资记录数
考点：聚合函数group by

SQL29 使用join查询方式找出没有分类的电影id以及名称

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

is null和= null的区别
NULL 值，给你一个正确的理解：把 NULL 理解为 UNKNOWN。
主要意思是’不知道’，就是它可能是任何值；
另外一层意思是’信息缺失’，比如某个存储姓名信息的字段值是 NULL，代表姓名信息缺失。
所以 NULL 值不是任意一个确定的值！
默认情况下推荐使用is null 作条件判断，因为sql默认情况下对where xx =null的判断永远返回0行，不提示语法错误。

SQL30 使用子查询的方式找出属于Action分类的所有电影对应的title,description
子查询解法：

1
2
3
4 select f.title,f.description from film as f
where f.film_id in (select fc.film_id from film_category as fc
               where fc.category_id in (select c.category_id from category as c
                                        where c.name = ‘Action’));

非子查询解法：

1
2
3
4 select f.title,f.description
from film as f inner join film_category as fc on f.film_id = fc.film_id
               inner join category as c on c.category_id = fc.category_id
where c.name = ‘Action’;

SQL33  创建一个actor表

考点：主键的定义

create table actor(
actor_id smallint(5) not null ,
first_name varchar(45) not null,
last_name varchar(45) not null,
last_update date not null,
primary key(actor_id)
);

SQL35 批量插入数据，不使用replace操作

如果不存在则插入，如果存在则忽略
INSERT OR IGNORE INTO tablename VALUES(…);
 
如果不存在则插入，如果存在则替换
INSERT OR REPLACE INTO tablename VALUES(…);
 
这里指的存在表示的是unique属性的列值存在的情况下，unique表示键值唯一

SQL36 创建一个actor_name表
请你创建一个actor_name表，并且将actor表中的所有first_name以及last_name导入该表.
actor_name表结构如下，题目最后会查询actor_name表里面的数据来对比结果输出

drop table if exists actor_name;
create table actor_name(
first_name varchar(45) not null,
last_name varchar(45) not null
);

insert into actor_name (select first_name,last_name from actor)

SQL37 建立索引
//唯一索引
create UNIQUE index uniq_idx_firstname on actor (first_name);

//普通索引
create index idx_lastname on actor (last_name);

1. create (unique) index 索引名 on 表名（列名）

create unique index uniq_idx_firstname on actor(first_name);
create index idx_lastname on actor(last_name);

2. alter table 表名 add (unique) index 索引名（列名）

   alter table actor add unique index uniq_idx_firstname(first_name);
   alter table actor add index idx_lastname(last_name);

SQL38  针对actor表创建视图actor_name_view

create VIEW actor_name_view as (
select first_name as first_name_v,last_name as last_name_v
from actor
)

SQL CREATE VIEW 语法

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

注释：视图总是显示最新的数据！每当用户查询视图时，数据库引擎通过使用视图的 SQL 语句重建数据。

SQL39 针对上面的salaries表emp_no字段创建索引idx_emp_no
考点：创建索引、强制索引查找

create index idx_emp_no on salaries(emp_no);
select * from salaries FORCE INDEX (idx_emp_no) where emp_no = 10005;
//索引名一定要有括号( ),否则不通过

SQL40 在last_update后面新增加一列名字为create_date

alter table actor add create_date datetime not null DEFAULT ‘2020-10-01 00:00:00’

SQL41  构造一个触发器audit_log

构造一个触发器audit_log，在向employees_test表中插入一条数据的时候，触发插入相关的数据到audit中。

drop TRIGGER if EXISTS audit_log;
create trigger audit_log
after insert on employees_test
for each ROW
begin
insert ignore into audit select ID as EMP_no,NAME from employees_test;
END

1.创建触发器使用语句：CREATE TRIGGER trigname;
2.指定触发器触发的事件在执行某操作之前还是之后，使用语句：BEFORE/AFTER [INSERT/UPDATE/ADD] ON tablename
3.触发器触发的事件写在BEGIN和END之间；
4.触发器中可以通过NEW获得触发事件之后2对应的tablename的相关列的值，OLD获得触发事件之前的2对应的tablename的相关列的值

create trigger audit_log
after insert on employees_test
for each row
begin
insert into audit values(new.id,new.name);
end

不写 for each row ，不通过

SQL46 在audit表上创建外键约束，其emp_no对应employees_test表的主键id

alter table audit add FOREIGN KEY (emp_no) REFERENCES employees_test(id);

外键的添加方式：
1、创建时

CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)

2、更改时

ALTER TABLE Orders
ADD FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)

SQL48 将所有获取奖金的员工当前的薪水增加10%

update salaries set salary=salary*1.1  where emp_no in  (select emp_no from emp_bonus)

2021/11/14
SQL50 将employees表中的所有员工的last_name和first_name通过引号连接起来。

//方式一
select concat(last_name,’’’,first_name) from employees
//方式二
select concat(last_name,"’",first_name) from employees
 
 concat(str1, str2,…) 返回结果为连接参数产生的字符串，如果有任何一个参数为null，则返回值为null。

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

select (LENGTH(“10,A,B”) - length(REPLACE(“10,A,B”,",",""))) as cnt

SQL52 获取Employees中的first_name

获取Employees中的first_name，查询按照first_name最后两个字母，按照升序进行排列

select first_name from employees order by SUBSTRING(first_name,-2) asc

1、left（name,4）截取左边的4个字符
2、right（name,2）截取右边的2个字符
3、SUBSTRING(name,5,3) 截取name这个字段 从第五个字符开始 只截取之后的3个字符

SQL Aggregate 函数
SQL Aggregate 函数计算从列中取得的值，返回一个单一的值。
有用的 Aggregate 函数：
AVG() - 返回平均值
COUNT() - 返回行数
FIRST() - 返回第一个记录的值
LAST() - 返回最后一个记录的值
MAX() - 返回最大值
MIN() - 返回最小值
SUM() - 返回总和

SQL Scalar 函数
SQL Scalar 函数基于输入值，返回一个单一的值。
有用的 Scalar 函数：
UCASE() - 将某个字段转换为大写
LCASE() - 将某个字段转换为小写
MID() - 从某个文本字段提取字符，MySql 中使用
SubString(字段，1，end) - 从某个文本字段提取字符
LEN() - 返回某个文本字段的长度
ROUND() - 对某个数值字段进行指定小数位数的四舍五入
NOW() - 返回当前的系统日期和时间
FORMAT() - 格式化某个字段的显示方式

SQL53 按照dept_no进行汇总

按照dept_no进行汇总，属于同一个部门的emp_no按照逗号进行连接，结果给出dept_no以及连接出的结果employees

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

MySQL之concat以及group_concat的用法
SQL54 平均工资

查找排除在职(to_date = ‘9999-01-01’ )员工的最大、最小salary之后，其他的在职员工的平均工资avg_salary。

select
avg(salary)
from
salaries
where
to_date = ‘9999-01-01’
and salary > (select min(salary) from salaries where to_date = ‘9999-01-01’ )
and salary < (select max(salary) from salaries where to_date = ‘9999-01-01’ )

写法1：> < 比较
写法2：not in
where 后面不能跟分组函数，因为执行顺序是先执行where 之后才执行分组，还没有分组的时候是无法运行min,max,avg，sum,count这类函数的！

SQL55 分页查询employees表，每5行一页，返回第2页的数据

limit子句用于限制查询结果返回的数量，常用于分页查询

select * from employees limit 5,5

SQL57 使用含有关键字exists查找未分配具体部门的员工的所有信息。

使用含有关键字exists查找未分配具体部门的员工的所有信息。

select * from employees
where not EXISTS(
SELECT emp_no from dept_emp where dept_emp.emp_no = employees.emp_no)

EXISTS 运算符
EXISTS 运算符用于判断查询子句是否有记录，如果有一条或多条记录存在返回 True，否则返回 False。
EXISTS 可以与 NOT 一同使用
SQL63 刷题通过的题目排名

请你根据上表，输出通过的题目的排名，通过题目个数相同的，排名相同，此时按照id升序排列
//我写的，排序有问题
select id,number,dense_rank() over(order by number desc) as t_rank
from passing_number
ORDER BY number desc

SELECT
    id,number,
    dense_rank ( ) over ( ORDER BY number DESC ) rank 
FROM
    passing_number 
ORDER BY
    rank,
    id

Sql 四大排名函数（ROW_NUMBER、RANK、DENSE_RANK、NTILE）
RANK
　　rank函数用于返回结果集的分区内每行的排名， 行的排名是相关行之前的排名数加一。简单来说rank函数就是对查询出来的记录进行排名，与row_number函数不同的是，rank函数考虑到了over子句中排序字段值相同的情况，如果使用rank函数来生成序号，over子句中排序字段值相同的序号是一样的，后面字段值不相同的序号将跳过相同的排名号排下一个，也就是相关行之前的排名数加一，可以理解为根据当前的记录数生成序号，后面的记录依此类推。可能我描述的比较苍白，理解起来也比较吃力，我们直接上代码，rank函数的使用方法与row_number函数完全相同。
select RANK() OVER(order by [UserId]) as rank,* from [Order]

DENSE_RANK
　　dense_rank函数的功能与rank函数类似，dense_rank函数在生成序号时是连续的，而rank函数生成的序号有可能不连续。dense_rank函数出现相同排名时，将不跳过相同排名号，rank值紧接上一次的rank值。在各个分组内，rank()是跳跃排序，有两个第一名时接下来就是第四名，dense_rank()是连续排序，有两个第一名时仍然跟着第二名。将上面的Sql语句改由dense_rank函数来实现。
select DENSE_RANK() OVER(order by [UserId]) as den_rank,* from [Order]

SQL73 考试分数(二)

请你写一个sql语句查询用户分数大于其所在工作(job)分数的平均分的所有grade的属性，并且以id的升序排序，

select id,job,score from grade g1
where g1.score > (select avg(score) from grade g2 where g1.job = g2.job)

SQL78 牛客的课程订单分析(二)

请你写出一个sql语句查询在2025-10-15以后，同一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程的user_id，并且按照user_id升序排序，

select user_id from order_info
where status = ‘completed’ and date > ‘2025-10-15’ and product_name != ‘JS’
group by user_id
having count(*) >=2
order by user_id asc

考点：group by ;聚合函数count()， having，where