按照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
));
输出格式:
本题要用到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
SELECT dept_no, group_concat(emp_no) AS employees
FROM dept_emp GROUP BY dept_no
将employees表中的所有员工的last_name和first_name通过(’)连接起来。(sqlite不支持concat，请用||实现，mysql支持concat)
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 K
输出格式:
name
Facello’Georgi
Simmel’Bezale
在本题所用的SQLite数据库中，只支持用连接符号”||”来连接字符串，不支持用函数连接
方式一sqlite
select concat(last_name,'\'',first_name) from employees
方式二mysql
select concat(last_name,"'",first_name) from employees
有一个员工表employees简况如下
请你查找employees表所有emp_no为奇数，且last_name不为Mary的员工信息，并按照hire_date逆序排列，以上例子查询结果如下:
select * FROM employees where emp_no%2 = 1
and last_name != 'Mary' order by hire_date desc
三点需要注意：
1、员工号为奇数，则emp_no取余应为1
2、last_name不为Mary，用‘！=’表示
3.根据hire_date逆序排列，用desc
第1行表示用户id为1的用户选择了C++岗位并且考了11001分
。。。
第8行表示用户id为8的用户选择了JS岗位并且考了9999分
请你写一个sql语句查询各个岗位分数的平均数，并且按照分数降序排序，结果保留小数点后面3位(3位之后四舍五入)
注意: sqlite 1/2得到的不是0.5，得到的是0，只有1*1.0/2才会得到0.5，sqlite四舍五入的函数为round)
SELECT job,round(avg(score),3) avg FROM grade GROUP by job order by avg(score) desc
1.首先题目肯定是按工作分组了，那么语句肯定是如下的:
select xxx from grade
group by job
然后知道分组之后的分数和是
sum(score)
求平均数就是
round(sum(score)*1.0/count(id),3) as avg
联合再按avg降序排序为:
复制代码
select job,round(sum(score)*1.0/count(id),3) as avg from grade
group by job order by avg desc;
对于表actor插入如下数据,如果数据已经存在，请忽略(不支持使用replace操作)
因为题目判定系统使用的是sqlite3，所以必须按sqlite3的写法来做
insert or ignore into actor
values(3,'ED','CHASE','2006-02-15 12:34:33');
如果是mysql，那么把or去掉，像下面这样：
insert IGNORE into actor
values(3,'ED','CHASE','2006-02-15 12:34:33');
有一个员工employees表简况如下
请你查找employees里最晚入职员工的所有信息，以上例子输出如下:
其他top和limit方法有牵强之处，与给定数据集有关,最晚入职的当天未必就一个人，也许有多人，使用排序并限制得只能取得指定数量的结果
select * from employees
where hire_date =
(select max(hire_date) from employees)
SELECT * FROM employees ORDER BY hire_date DESC LIMIT 0,1;
现在在last_update后面新增加一列名字为create_date, 类型为datetime, NOT NULL，默认值为’2020-10-01 00:00:00
MySQL中default后不需要加括号
alter table actor add create_date datetime not null default '2020-10-01 00:00:00'
after last_update
牛客每天有很多人登录，请你统计一下牛客每个用户最近登录是哪一天。有一个登录(login)记录表，简况如下
第1行表示user_id为2的用户在2020-10-12使用了客户端id为1的设备登录了牛客网
。。。
第4行表示user_id为3的用户在2020-10-13使用了客户端id为2的设备登录了牛客网
请你写出一个sql语句查询每个用户最近一天登录的日子，并且按照user_id升序排序，上面的例子查询结果如下
先按照user_id分组，并选出每个组最大的date的情况。 后面再排序
select max(date) as d from login
group by user_id
order by user_id;
有一个员工表，employees简况如下
有一个部门表，dept_emp简况如下
请你查找所有已经分配部门的员工的last_name和first_name以及dept_no，也包括暂时没有分配具体部门的员工，以上例子如下
注意：
INNER JOIN 两边表同时有对应的数据，即任何一边缺失数据就不显示。
LEFT JOIN 会读取左边数据表的全部数据，即便右边表无对应数据。
RIGHT JOIN 会读取右边数据表的全部数据，即便左边表无对应数据
SELECT ep.last_name, ep.first_name, dp.dept_no
FROM employees ep
LEFT JOIN dept_emp dp
ON ep.emp_no = dp.emp_no
select last_name,first_name,dept_no from employees e
left join dept_emp d on e.emp_no = d.emp_no
有一个员工表，employees简况如下
有一个部门表，dept_emp简况如下
请你查找所有已经分配部门的员工的last_name和first_name以及dept_no，未分配的部门的员工不显示，以上例子如下
答案是按employees表中顺序输出的，所以使用内连接查询时，必须将employees表放在前面。
select last_name,first_name,dept_no from employees,dept_emp where dept_emp.emp_no = employees.emp_no;
使用左连接查询时，employees中没有分配部门的员工（没有被记录在dept_emp表）dept_no字段被自动取NULL然后被输出，所以应当剔除（复合条件连接查询）。
select last_name,first_name,dept_no
from employees left join dept_emp
on employees.emp_no = dept_emp.emp_no
where dept_emp.dept_no<>’’;
有一个薪水表salaries简况如下
请你获取薪水第二多的员工的emp_no以及其对应的薪水salary，
若有多个员工的薪水为第二多的薪水，则将对应的员工的emp_no和salary全部输出，并按emp_no升序排序
select e.last_name,e.first_name,d.dept_no from employees e
inner join dept_emp d on e.emp_no = d.emp_no
避免了2个问题：
(1) 首先这样可以解决多个人工资相同的问题；
(2) 另外，筛选出第二多的工资时要注意distinct salary，否则不能选出第二多的工资
1）首先要考虑到同一薪水有多人，假如薪水最高的有3人；薪水第二高的有2人，那么
select salary from salaries order by salary desc limit 1,1
通过上述语句limit 1,1 查出的仍然是薪水最高的第二人，所以要对salary分组查询薪水第二高的薪水（用distinct 或 group by）
select salary from salaries group by salary order by salary desc limit 1,1
（2）正确答案为：
select emp_no, salary
from salaries
where salary = (
select salary from salaries
group by salary
order by salary desc limit 1,1
)
and to_date = '9999-01-01'
有一个薪水表，salaries简况如下
请你查找薪水记录超过15次的员工号emp_no以及其对应的记录次数t，以上例子输出如下:
1.下一条salary高于本条才算涨幅，大部分答案都是只要出现了一条记录就算一次涨幅
2.个人比较严格的话，salary高于前一条才算涨幅，对于每条记录查询它上一条记录判断生成临时表，在做统计就好了
SELECT tmp.emp_no,
Count(c) AS t
FROM (SELECT emp_no,
IFNULL(s1.salary > (SELECT s2.salary
FROM salaries AS s2
WHERE s1.from_date > s2.from_date
and s1.emp_no = s2.emp_no
ORDER BY s2.from_date DESC
LIMIT 1),1) AS c,
s1.from_date
FROM salaries AS s1) tmp
GROUP BY tmp.emp_no
HAVING Count(*) > 15;
有一个员工表employees简况如下
有一个部门领导表dept_manager简况如下
请你找出所有非部门领导的员工emp_no，以上例子输出:
方法一：使用NOT IN选出在employees但不在dept_manager中的emp_no记录
SELECT emp_no FROM employees
WHERE emp_no NOT IN (SELECT emp_no FROM dept_manager)
方法二：先使用LEFT JOIN连接两张表，再从此表中选出dept_no值为NULL对应的emp_no记录
SELECT emp_no FROM (SELECT * FROM employees LEFT JOIN dept_manager
ON employees.emp_no = dept_manager.emp_no)
WHERE dept_no IS NULL
方法三：方法二的简版，使用单层SELECT语句即可
SELECT employees.emp_no FROM employees LEFT JOIN dept_manager
ON employees.emp_no = dept_manager.emp_no
WHERE dept_no IS NULL
针对如下表actor结构创建索引：
(注:在 SQLite 中,除了重命名表和在已有的表中添加列,ALTER TABLE 命令不支持其他操作，
mysql支持ALTER TABLE创建索引
对first_name创建唯一索引uniq_idx_firstname，对last_name创建普通索引idx_lastname
两种方法。语法记不住啊啊啊啊
- create (unique) index 索引名 on 表名（列名
create unique index uniq_idx_firstname on actor(first_name);
create index idx_lastname on actor(last_name);
- 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);
在牛客实习广场有很多公司开放职位给同学们投递，同学投递完就会把简历信息存到数据库里。
现在有简历信息表(resume_info)，部分信息简况如下
第1行表示，在2025年1月2号，C++岗位收到了53封简历
最后1行表示，在2026年1月4号，Java岗位收到了230封简历
请你写出SQL语句查询在2025年内投递简历的岗位和数量，并且按数量降序排序，以上例子查询结果如下:
筛选出2025年即可，可以使用
like '2025%'
或
year(date) = 2025
或
between '2025-01-01' and '2025-12-31'
或
date >= '2025-01-01' and date <= '2025-12-31'
select job, sum(num) cnt
from resume_info
where YEAR(date) = '2025'
group by job
order by cnt desc
现有电影信息表film，包含以下字段：
解题思路是运用 LEFT JOIN 连接两表，用 IS NULL 语句限定条件：
1、用 LEFT JOIN 连接 film 和 film_category，限定条件为 f.film_id = fc.film_id，即连接电影id和电影分类id，如果电影没有分类，则电影分类id显示null
2、再用 WHERE 来限定条件 fc.category_id IS NULL 选出没分类的电影
/** 注意：最后一句若写成 ON f.film_id = fc.film_id AND fc.category_id IS NULL，则意义变成左连接两表 film_id 相同的记录，且 fc.category 的值为 null，由于 fc.category的值不为null，即（f.film_id = fc.film_id AND fc.category_id IS NULL）恒为FALSE，所以左连接后只显示film表的数据，而 film_category 表的数据全为 null**/
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
select f.film_id,f.title from (film f
left join film_category fc on fc.film_id=f.film_id) as cc
where cc.category_id is null
现有员工表employees如下
请将employees表的所有员工的last_name和first_name拼接起来作为Name，中间以一个空格区分。
(注：sqllite,字符串拼接为 || 符号，不支持concat函数，mysql支持concat函数)
MySQL、SQL Server、Oracle等数据库支持CONCAT方法，
而本题所用的SQLite数据库只支持用连接符号”||”来连接字符串
CONCAT方法：
select CONCAT(CONCAT(last_name," "),first_name) as name from employees
或者
select CONCAT(last_name," "，first_name) as name from employees
本题中使用：
select last_name||" "||first_name as name from employees
有一个员工employees表简况如下
请你查找employees里入职员工时间排名倒数第三的员工所有信息，以上例子输出如下:
注意：可能会存在同一个日期入职的员工，所以入职员工时间排名倒数第三的员工可能不止一个。
考虑到入职日期可能会有多个重复，最合理的SQL语句应为:
SELECT * FROM employees
WHERE hire_date = (
SELECT DISTINCT hire_date FROM employees
ORDER BY hire_date DESC limit 2,1
);
有员工薪资简表salaries如下：
emp_no指员工编号；
salary指薪资；
from_date指该薪资的开始日期；
to_date指该薪资的结束日期
请你写出更新语句，将所有获取奖金的员工当前的(salaries.to_date=‘9999-01-01’)薪水增加10%。(emp_bonus里面的emp_no都是当前获奖的所有员工，不考虑获取的奖金的类型)。
以上示例更新后的结果salaries为
思路：先选择符合条件的emp_no，在筛选to_date，最后将符合的字段进行更新。推荐使用内连接查询，速度会快一些。
考点：更新语句的使用，sql里可以使用基本运算（如加减乘除）
方法一：内连接
update salaries T join emp_bonus T2 on T2.emp_no = T.emp_no
set salary = salary * 1.1
where to_date = '9999-01-01'
方法二：子查询
update salaries
set salary = salary * 1.1
where emp_no in (
select emp_no from emp_bonus
) and to_date = '9999-01-01'
针对salaries表emp_no字段创建索引idx_emp_no，查询emp_no为10005，使用强制索引。
SELECT * FROM salaries FORCE INDEX(idx_emp_no) WHERE emp_no = 10005;
按照salary的累计和running_total，其中running_total为前N个当前( to_date = ‘9999-01-01’)员工的salary累计和，其他以此类推。 具体结果如下Demo展示。。
CREATE TABLE
salaries
(
emp_no
int(11) NOT NULL,
salary
int(11) NOT NULL,
from_date
date NOT NULL,
to_date
date NOT NULL,
PRIMARY KEY (
emp_no
,
from_date
));
输出格式:
解题思路：
①本题关键在于把sum聚合函数作为窗口函数使用，所有聚合函数都能用做窗口函数，其语法和专用窗口函数完全相同。
sum(<汇总列>) over(<排序列>) as 别名；
②光看题目“前两个员工的salary累计和”不是很好理解，结合输出格式可以理解为running_total列是逐个员工的工资的累计和，每一行的工资都是前面所有行的工资总计。
③这有一个小bug，题目没有限定时间为当前，而按照输出格式来看和通过情况来看，只有限定时间为当前’9999-01-01’才能符合输出格式，才能通过，一开始考虑用员工分组，但是员工分组得到的结果并非题目本意，必须限定时间为当前
select emp_no,salary,
sum(salary) over(order by emp_no) as running_total
from salaries
where to_date= '9999-01-01';
使用含有关键字exists查找未分配具体部门的员工的所有信息。
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
));
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
));
/* 1. EXISTS */
SELECT *
FROM employees
WHERE NOT EXISTS (SELECT emp_no
FROM dept_emp
WHERE employees.emp_no = dept_emp.emp_no);
/* 2. IN */
SELECT *
FROM employees
WHERE emp_no NOT IN (SELECT emp_no
FROM dept_emp);
/*
1.什么时候用EXISTS，什么时候用IN？
主表为employees，从表为dept_emp，在主表和从表都对关联的列emp_no建立索引的前提下：
当主表比从表大时，IN查询的效率较高；
当从表比主表大时，EXISTS查询的效率较高；
原因如下：
in是先执行子查询，得到一个结果集，将结果集代入外层谓词条件执行主查询，子查询只需要执行一次
exists是先从主查询中取得一条数据，再代入到子查询中，执行一次子查询，判断子查询是否能返回结果，主查询有多少条数据，子查询就要执行多少次
*/
构造一个触发器audit_log，在向employees_test表中插入一条数据的时候，触发插入相关的数据到audit中。
CREATE TABLE employees_test(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
CREATE TABLE audit(
EMP_no INT NOT NULL,
NAME TEXT NOT NULL
);
后台会往employees_test插入一条数据:
INSERT INTO employees_test (ID,NAME,AGE,ADDRESS,SALARY)VALUES (1, ‘Paul’, 32, ‘California’, 20000.00 );
然后从audit里面使用查询语句:
select * from audit;
初识触发器
语言运行环境：Sql(mysql 8.0)
1.创建触发器：create trigger 触发器名称
2.用after指定触发条件：在表1中每插入一行 after insert on 表1 for each row
(注意insert后用on，for each row不能少否则会报错)
3.触发的指令写在begin和end之间，分号写在指令后，end后不用加分号
create trigger audit_log after insert on employees_test
for each row
begin
insert into audit values(NEW.ID,NEW.NAME);
end
在牛客实习广场有很多公司开放职位给同学们投递，同学投递完就会把简历信息存到数据库里。
现在有简历信息表(resume_info)，部分信息简况如下
第1行表示，在2025年1月2号，C++岗位收到了53封简历
最后1行表示，在2026年2月6号，C++岗位收到了231封简历
请你写出SQL语句查询在2025年内投递简历的每个岗位，每一个月内收到简历的数量，并且按先按月份降序排序，再按简历数目降序排序，以上例子查询结果如下:
“`sql
select job,
date_format(date,”%Y-%m”) as mon,
sum(num) as cnt
from resume_info
where left(date,4)=“2025”
group by job,mon
order by mon desc, cnt desc`
mysql常用函数：
EXTRACT(type FROM d) 从日期 d 中获取指定的值，type 指定返回的值。type可为year,week,moth等等
DATE_FORMAT(d,f) 按表达式 f的要求显示日期 d. f的表达式如'%Y-%m-%d %r' 是2011-11-11 11:11:11 AM。 '%Y-%m‘是2011-11.
LEFT(s,n) 返回字符串 s 的前 n 个字符
参考资料来源：https://www.runoob.com/mysql/mysql-functions.html
删除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');
删除后titles_test表为(注：最后会select * from titles_test表来对比结果)
delete from titles_test where id not in(
select min(id) from titles_test group by emp_no)
在mysql中这样写不行的，mysql不允许用一个表中查询出来的数据更新同一个表😂智熄的操作
改成
```sql
delete from titles_test where id not in(
select min_id from(
select min(id) as min_id from titles_test group by emp_no) a)
在更新操作之前再查一下就好了，对mysql来说，这道题给个中等难度都不为过啊😀
有一个员工表dept_emp简况如下
第一行表示为员工编号为10001的部门是d001部门。
有一个部门经理表dept_manager简况如下
第一行表示为d001部门的经理是编号为10002的员工。
获取所有的员工和员工对应的经理，如果员工本身是经理的话则不显示，以上例子如下
本题应注意以下三点：
1、用 INNER JOIN 连接两张表，因为要输出自己的经理，得知自己与经理的部门要相同，故有限制条件 de.dept_no = dm.dept_no
2、再用 WHERE 限制当前员工与当前经理的条件，即 dm.to_date 等于 ‘9999-01-01’ 、de.to_date 等于 ‘9999-01-01’ 、 de.emp_no 不等于 dm.emp_no
3、为了增强代码可读性，将 dept_emp 用别名 de 代替，dept_manager 用 dm 代替，最后根据题意将 de.emp_no 用别名 manager_no 代替后输出
SELECT de.emp_no, dm.emp_no AS manager_no
FROM dept_emp AS de INNER JOIN dept_manager AS dm
ON de.dept_no = dm.dept_no
WHERE dm.to_date = '9999-01-01' AND de.to_date = '9999-01-01' AND de.emp_no <> dm.emp_no
在牛客刷题有一个通过题目个数的(passing_number)表，id是主键，简化如下:
第1行表示id为1的用户通过了4个题目;
…
第6行表示id为6的用户通过了4个题目;
请你根据上表，输出通过的题目的排名，通过题目个数相同的，排名相同，此时按照id升序排列，数据如下
方法一.首先要通过题目降序，id升序，那么sql应该为:
select a.id,a.number
from passing_number a order by a.number desc, a.id asc;
要得到通过题目的排名，比如你通过了8分,，你同学a也通过了8分，找到大于等于你的成绩，一个9分，一个8分，一个8分，去重复，就一个9，一个8，count一下总数，第2名，如果有三个同学通过了7个呢，同理，9，8，8，7，7，7 后面比这个少的，已经死在了筛选条件，去重，9，8，7，count=3，所以sql为:
select count(distinct b.number) from passing_number b where b.number>=X
联立为:
复制代码
select a.id,a.number,
(select count(distinct b.number) from passing_number b where b.number>=a.number )
from passing_number a order by a.number desc, a.id asc;
方法二.使用mysql8.0(我强烈要求升级的mysql8.0，以便大家使用窗口函数)或者sqlite自带的dense_rank()函数，由于rank是mysql8.0的关键字，所以不能直接使用rank，要用” ` “号包裹，或者使用t_rank
SELECT
id,number,
dense_rank ( ) over ( ORDER BY number DESC ) `rank`
FROM
passing_number
ORDER BY
`rank`,
id