学习笔记-mysql_日志
MySQL学习日记
- 1、笔记
- 1、升序降序
- 2、in的用法
- 3、奇数偶数方法
- 4、不重复查询
- 5、where和and的区别
- 6、函数rank()、dense_rank()、row_number()
- 7、group by原理和理解
- 8、SQL语句中同时出现count()、where、group by 的先后执行顺序
- 9、LIKE 语法
- 10、CONCAT()的用法
- 11、插入语句之value与values区别
- 12、replace方法
- 13、MySQL中四种方式给字段添加索引
- 14、MySQL中视图
- 15、MySQL中强制索引查询
- 16、新增字段
- 17、构造一个触发器
- 18、修改表名
- 19、创建外键约束
- 20、substr函数连接
- 21、AVG函数求平均值
- 22、EXISTS的用法
- 23、over的用法
- 24、round的用法
- 25、case when 用法
- 26、max与group by连用
- 27、COUNT(*),COUNT(字段),CONUT(DISTINCT 字段)的区别
- 28、partition by和group by对比
- 29、sum() over()
- 30、floor（）使用
- 31、rank() over(partition by)的使用
- 32、between函数
- 33、日期函数格式转换
- 34、right 的作用
- 35、union的作用
- 2、错题集
- 1、查找最晚入职员工的所有信息
- 2、查找薪水记录超过15次的员工号emp_no以及其对应的记录次数t
- 3、找出所有员工具体的薪水salary情况，对于相同的薪水只显示一次,并按照逆序显示
- 4、找出所有非部门领导的员工
- 5、获取所有的员工和员工对应的经理，如果员工本身是经理的话则不显示
- 6、获取每个部门中当前员工薪水最高的相关信息
- 7、查找在职员工自入职以来的薪水涨幅情况
- 9、创建一个actor表
- 10、添加多条记录
- 11、对于表actor插入如下数据,如果数据已经存在，请忽略
- 12、创建一个actor_name表并从其他表中添加数据
- 13、删除emp_no重复的记录，只保留最小的id对应的记录。
- 14、将所有to_date为9999-01-01的全部更新为NULL
- 15、查找字符串 10,A,B 中逗号,出现的次数cnt
- 16、对于employees表中，给出奇数行的first_name
- 17、牛客每个人最近的登录日期
- 18、统计一下牛客每个日期登录新用户个数
- 19、每天有很多人登录，请你统计一下牛客每个日期新用户的次日留存率
- 20、请你写出一个sql语句查询刷题信息
- 21、请你找出每个岗位分数排名前2名的用户
- 22、查询在每年投递简历的每个岗位
1、笔记
1、升序降序
SELECT * FROM table_name ORDER BY field ASC //升序
SELECT * FROM table_name ORDER BY field DESC//降序
2、in的用法
in常用于where表达式中，其作用是查询某个范围内的数据。
用法：select * from where field in (value1,value2,value3,…)
not in与in作用相反，用法和示例如下：
用法：select * from where field not in (value1,value2,value3,…)
3、奇数偶数方法
使用按位与
-- 奇数
select * from table WHERE id & 1;
-- 偶数 【 id先除以2然后乘2 如果与原来的相等就是偶数】
select * from table WHERE id=(id>>1)<<1;
正则匹配
正则匹配最后一位
-- 奇数
select * from table WHERE id regexp '[13579]$';
-- 偶数
select * from table WHERE id regexp '[02468]$';
除2取余
-- 奇数
select * from table WHERE id % 2 = 1;
select * from table WHERE mod(id, 2) = 1;
-- 偶数
select * from table WHERE id % 2 = 0;
select * from table WHERE mod(id, 2) = 0;
-1的奇数次方和偶数次方
-- 奇数
select * from table WHERE POWER(-1, id) = -1;
--偶数
select * from table WHERE POWER(-1, id) = 1;
4、不重复查询
用distinct来返回不重复的用户名：
select distinct name from user;
5、where和and的区别
select * from tb_a LEFT JOIN tb_b ontb_a.pid=tb_b.pid
select * from tb_a LEFT JOIN tb_b ontb_a.pid=tb_b.pid and tb_a.name=‘tt’
select * from tb_a LEFT JOIN tb_b on tb_a.pid=tb_b.pidwhere tb_a.name=‘tt’
在使用left jion时，and和where条件的区别如下：
1、 and条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。和右边的表关联不上，右边表的字段显示null；
2、where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。
6、函数rank()、dense_rank()、row_number()
rank()
按照某字段的排序结果添加排名，但它是跳跃的、间断的排名，例如两个并列第一名后，下一个是第三名。
示例代码
SELECT Score,rank() over(ORDER BY Score desc) as 'Rank'
FROM score
查询结果
row_number()
它是将某字段按照顺序依次添加行号。
示例代码
SELECT Score,row_number() over(ORDER BY Score desc) as 'Rank'
FROM score
查询结果
dense_rank()
dense 英语中指“稠密的、密集的”。dense_rank()是的排序数字是连续的、不间断。当有相同的分数时，它们的排名结果是并列的，例如，1,2,2,3。
示例代码
SELECT Score,dense_rank() over(ORDER BY Score desc) as 'Rank'
FROM score
查询结果
7、group by原理和理解
SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;
SELECT @@sql_mode;
set @@GLOBAL.sql_mode=(select replace(@@GLOBAL.sql_mode,'ONLY_FULL_GROUP_BY',''));
group by的常规用法是配合聚合函数，利用分组信息进行统计，常见的是配合max等聚合函数筛选数据后分析，以及配合having进行筛选后过滤。
聚合函数max
select max(user_id),grade from user_info group by grade ;
这条sql的含义很明确，将数据按照grade字段分组，查询每组最大的user_id以及当前组内容。注意，这里分组条件是grade，查询的非聚合条件也是grade。这里不产生冲突。
having
select max(user_id),grade from user_info group by grade having grade>‘A’
select * from test1; 结果如下图:
select count(a),b,c from test1 group by b,c;
可以看出 group by 两个条件的工作过程:
先对第一个条件b列的值 进行分组,分为 第一组:1-5, 第二组6-8,
然后又对已经存在的两个分组用条件二 c列的值进行分组,发现第一组又可以分为两组 1-4,5
select count(a),b,c from test1 group by c,b;
8、SQL语句中同时出现count()、where、group by 的先后执行顺序
如：select count(*) from tab1 where publi_id=‘1000000141’ and cent_id=‘3702000001’
它是先根据条件利用where查询出所有数据，然后利用count将每条数据汇总，就是算一下有多少条数据
如：select count(*) from tab1 where publi_id=‘1000000141’ and cent_id=‘3702000001’ group by comp_id
它是先根据where条件查询出所有的数据，然后按照group by 来对comp_id进行分组，分完组后count会对每组的每条数据进行汇总
（注意：group by 后，count是对每组中的数据进行汇总）
9、LIKE 语法
LIKE运算符用于WHERE表达式中，以搜索匹配字段中的指定内容，语法如下：WHERE column LIKE pattern
WHERE column NOT LIKE pattern
在LIKE全面加上NOT运算符时，表示与LIKE相反的意思，即选择column不包含pattern的数据记录
LIKE通常与通配符%一起使用，%表示通配pattern中出现的内容，而不加通配符%的LIKE语法，表示精确匹配，其实际效果等同于 = 等于运算符
MySQL LIKE 大小写MySQL LIKE 匹配字符的时候，默认情况下是不区分大小写的，如果在需要区分大小写的时候，可以加入BINARY操作符：
SELECT * FROM username WHERE LIKE BINARY ‘%azz%’
SELECT * FROM username WHERE LIKE BINARY ‘%Azz%’
MySQL LIKE 中文匹配由于数据存储编码问题，在某些情况下，MySQL进行LIKE搜索返回的数据除了符合要求的数据外，往往还会返回许多不相干的数据，这时候也需要在LIKE后面加上BINARY操作符进行二进制比较
SELECT * FROM username WHERE LIKE BINARY ‘%小%’
10、CONCAT()的用法
mysql CONCAT（）函数用于将多个字符串连接成一个字符串，是最重要的mysql函数之一，下面就将为您详细介绍mysql CONCAT（）函数，供您参考
mysql CONCAT(str1,str2,…)
返回结果为连接参数产生的字符串。如有任何一个参数为NULL ，则返回值为 NULL。或许有一个或多个参数。 如果所有参数均为非二进制字符串，则结果为非二进制字符串。 如果自变量中含有任一二进制字符串，则结果为一个二进制字符串。一个数字参数被转化为与之相等的二进制字符串格式；若要避免这种情况，可使用显式类型 cast, 例如： SELECT CONCAT(CAST(int_col AS CHAR), char_col)
mysql> SELECT CONCAT(’My’, ‘S’, ‘QL’);
-> ‘MySQL’
mysql> SELECT CONCAT(’My’, NULL, ‘QL’);
-> NULL
mysql> SELECT CONCAT(14.3);
-> ‘14.3′
mysql CONCAT_WS(separator,str1,str2,…)CONCAT_WS() 代表 CONCAT With Separator ，是CONCAT()的特殊形式。 第一个参数是其它参数的分隔符。分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串，也可以是其它参数。如果分隔符为 NULL，则结果为 NULL。函数会忽略任何分隔符参数后的 NULL 值。
mysql> SELECT CONCAT_WS(’,’,’First name’,'Second name’,'Last Name’);
-> ‘First name,Second name,Last Name’
mysql> SELECT CONCAT_WS(’,’,’First name’,NULL,’Last Name’);
-> ‘First name,Last Name’
mysql CONCAT_WS()不会忽略任何空字符串。 (然而会忽略所有的 NULL）。
11、插入语句之value与values区别
应该在插入单行的时候使用VALUES，在插入多行的时候使用VALUE
12、replace方法
一、replace函数
语法：replace(object,search,replace)
语义：把object对象中出现的的search全部替换成replace。
实例：
update hellotable set helloCol = replace(helloCol,'helloSearch','helloReplace')
二、replace into函数
为什么会接触到replace into函数，是因为业务需要向数据库中插入数据，前提是重复的不能再次插入。以前用where解决的，今天才知道还有一个更简洁的方法replace。
replace具备替换拥有唯一索引或者主键索引重复数据的能力，也就是如果使用replace into插入的数据的唯一索引或者主键索引与之前的数据有重复的情况，将会删除原先的数据，然后再进行添加。
语法：replace into table( col1, col2, col3 ) values ( val1, val2, val3 )
语义：向table表中col1, col2, col3列replace数据val1，val2，val3
实例：
REPLACE INTO users (id,name,age) VALUES(123, ‘chao’, 50);
三、唯一的组合索引
因为在创建索引的时候发现很多问题，放在这儿做一下记录。
首先我们使用replace的原因就是因为它有“防重”的作用，但是它的“防重”作用仅仅是局限在唯一索引上或者主键索引上。所以我们使用replace时必须要有一个唯一索引。
而业务的逻辑需要四个字段唯一确定一条记录，也就是说我要建立一个组合索引也即多列索引。
最终确定需求就是我要create出一个unique的组合索引，很简单是不是，后面讲坑。。。
先讲讲组合（多列）索引是什么鬼？！
组合索引的生效原则是 从前向后依次生效，如果中间某个索引没有使用， 那么断点前面的索引部分起作用，断点后面的索引没有起作用，即最左优先原则
例如创建多列索引（a,b,c)
where a=3 and b=45 and c=5...
这种三个索引顺序使用中间没有断点，全部发挥作用；
where a=3 and c=5...
这种情况下b就是断点，a发挥了效果，c没有效果;
where b=3 and c=4...
这种情况下a就是断点，在a后面的索引都没有发挥作用，这种写法联合索引没有发挥任何效果；
where b=45 and a=3 and c=5...
这个跟第一个一样，全部发挥作用，abc只要用上了就行，跟写的顺序无关;
如何创建组合索引
语法：CREATE UNIQUE INDEX index ON table( col1, col2, col3 )
实例：CREATE UNIQUE INDEX index_unique ON app(pkgName,version,device,osver)
13、MySQL中四种方式给字段添加索引
- 添加主键
ALTER TABLE tbl_name ADD PRIMARY KEY (col_list);
// 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
- 添加唯一索引
ALTER TABLE tbl_name ADD UNIQUE index_name (col_list);
// 这条语句创建索引的值必须是唯一的。
//也可以
create unique index index_name on tbl_name (col_list);
- 添加普通索引
ALTER TABLE tbl_name ADD INDEX index_name (col_list);
// 添加普通索引，索引值可出现多次。
//也可以
create index index_name on tbl_name (col_list)
- 添加全文索引
ALTER TABLE tbl_name ADD FULLTEXT index_name (col_list);
// 该语句指定了索引为 FULLTEXT ，用于全文索引。
//也可以
create FULLTEXT index index_name on tbl_name (col_list);
PS: 附赠删除索引的语法：
DROP INDEX index_name ON tbl_name;
// 或者
ALTER TABLE tbl_name DROP INDEX index_name；
ALTER TABLE tbl_name DROP PRIMARY KEY;
14、MySQL中视图
创建视图 用AS：
CREATE [TEMP | TEMPORARY] VIEW view_name AS
SELECT column1, column2..... FROM table_name
WHERE [condition];
举个例子
CREATE VIEW actor_name_view (first_name_v, last_name_v) AS
SELECT first_name, last_name FROM actor
或者
create view actor_name_view as
select first_name as first_name_v, last_name as last_name_v from actor
视图（View）只不过是通过相关的名称存储在数据库中的一个 SQLite 语句。视图（View）实际上是一个以预定义的 SQLite 查询形式存在的表的组合。
视图（View）可以包含一个表的所有行或从一个或多个表选定行。视图（View）可以从一个或多个表创建，这取决于要创建视图的 SQLite 查询。、
视图（View）是一种虚表，允许用户实现以下几点：
用户或用户组查找结构数据的方式更自然或直观。
限制数据访问，用户只能看到有限的数据，而不是完整的表。
汇总各种表中的数据，用于生成报告。
SQLite 视图是只读的，因此可能无法在视图上执行 DELETE、INSERT 或 UPDATE 语句。但是可以在视图上创建一个触发器，当尝试 DELETE、INSERT 或 UPDATE 视图时触发，需要做的动作在触发器内容中定义。
15、MySQL中强制索引查询
MYSQL中强制索引查询使用：FORCE INDEX(indexname);
select * from salaries
FORCE INDEX (idx_emp_no)
where emp_no = 10005
16、新增字段
新增加一列名字为create_date, 类型为datetime, NOT NULL，默认值为’2020-10-01 00:00:00’
alter table actor
add
create_date datetime NOT NULL default '2020-10-01 00:00:00'```
<hr color=#000000 size=1">
# 2、错题集
<hr color=#000000 size=1">
## 1、查找employees里最晚入职员工的所有信息
```sql
select * from employees order by hire_date desc limit 0,1
select * from employees where hire_date=(
select MAX(hire_date) from employees
)
17、构造一个触发器
构造触发器时注意以下几点：
1、用 CREATE TRIGGER 语句构造触发器，用 BEFORE或AFTER 来指定在执行后面的SQL语句之前或之后来触发TRIGGER
2、触发器执行的内容写出 BEGIN与END 之间
3、可以使用 NEW与OLD 关键字访问触发后或触发前的employees_test表单记录
举例
构造一个触发器audit_log，在向employees_test表中插入一条数据的时候，触发插入相关的数据到audit中。
create trigger audit_log
after insert on employees_test for each row
begin
insert into audit values(new.id,new.name);
end;
18、修改表名
alter table titles_test rename titles_2017
19、创建外键约束
在audit表上创建外键约束，其EMP_no 对应employees_test表的主键ID。
alter table audit
add foreign key (EMP_no )
references employees_test(ID)
20、substr函数连接
substr(X,Y,Z) 或 substr(X,Y) 函数的使用。其中X是要截取的字符串。Y是字符串的起始位置（注意第一个字符的位置为1，而不为0），取值范围是±(1~length(X))，当Y等于length(X)时，则截取最后一个字符；当Y等于负整数-n时，则从倒数第n个字符处截取。Z是要截取字符串的长度，取值范围是正整数，若Z省略，则从Y处一直截取到字符串末尾；若Z大于剩下的字符串长度，也是截取到字符串末尾为止。
SELECT first_name FROM employees ORDER BY substr(first_name,length(first_name)-1)
SELECT first_name FROM employees ORDER BY substr(first_name,-2)
21、AVG函数求平均值
select avg(salary) as avg_salary from salaries
22、EXISTS的用法
执行employees.length次
指定一个子查询，检测行的存在。遍历循环外表，然后看外表中的记录有没有和内表的数据一样的。匹配上就将结果放入结果集中。
举例
select emp_no,birth_date,first_name,last_name,gender,hire_date
from employees s
where not exists(
select emp_no from dept_emp where s.emp_no=dept_emp.emp_no
);
23、over的用法
over不能单独使用，要和分析函数：rank(),dense_rank(),row_number()，sum()等一起使用。
over函数的参数：over（partition by columnname1 order by columnname2）
含义，按columname1指定的字段进行分组排序，或者说按字段columnname1的值进行分组排序。
例子：
select deptno,ename,sal,
sum(sal) over (partition by deptno order by ename) 部门连续求和,–各部门的薪水"连续"求和
sum(sal) over (partition by deptno) 部门总和, – 部门统计的总和，同一部门总和不变
100*round(sal/sum(sal) over (partition by deptno),4) “部门份额(%)”,
sum(sal) over (order by deptno,ename) 连续求和, --所有部门的薪水"连续"求和
sum(sal) over () 总和, – 此处sum(sal) over () 等同于sum(sal)，所有员工的薪水总和
100*round(sal/sum(sal) over (),4) “总份额(%)”
from emp;
例子
统计salary的累计和running_total
select emp_no,salary,sum(salary) over(order by emp_no) as running_total
from salaries
where to_date = '9999-01-01'
24、round的用法
在mysql中，round函数用于数据的四舍五入，它有两种形式：
1、round(x,d) ，x指要处理的数，d是指保留几位小数
这里有个值得注意的地方是，d可以是负数，这时是指定小数点左边的d位整数位为0,同时小数位均为0；
25、case when 用法
MySQL 的 case when 的语法有两种：
简单函数
CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END
举例
现在有一个需求，让你统计正常用户发送给正常用户邮件失败的概率
1为黑名单用户，0为正常用户。
select e.date,round(
sum(case e.type when "no_completed" then 1 else 0 end )/count(*)
,3) p
from email e
join user u1 on e.send_id = u1.id
join user u2 on e.receive_id = u2.id
where u1.is_blacklist = 0 and u2.is_blacklist = 0
group by e.date
order by date
搜索函数
CASE WHEN [expr] THEN [result1]…ELSE [default] END
26、max与group by连用
mysql> select * from test;
mysql> select id,name,max(age),class from test group by class; --错误的sql
虽然找到的age是最大的age，但是与之匹配的用户信息却不是真实的信息，而是group by分组后的第一条记录的基本信息。
解决方法1：先按age排序再按class分组查询，不使用max函数
mysql> select * from (
-> select * from test order by age desc) as b
-> group by class;
解决方法2：先找出最大的age，通过相关子查询与原表id关联，再以age排序，不使用group by关键词
mysql> select * from test t where t.age = (
-> select max(age) from test where id = t.id ) as b
-> order by age desc;
27、COUNT(*),COUNT(字段),CONUT(DISTINCT 字段)的区别
COUNT(*).明确的返回数据表中的数据个数,是最准确的
COUNT(列),返回数据表中的数据个数,不统计值为null的字段
COUNT(DISTINCT 字段) 返回数据表中不重复的的数据个数,不统计值为null的字段
28、partition by和group by对比
group by是分组函数，partition by是分析函数（然后像sum()等是聚合函数）；
在执行顺序上，
以下是常用sql关键字的优先级
from > where > group by > having > order by
而partition by应用在以上关键字之后，实际上就是在执行完select之后，在所得结果集之上进行partition。
29、sum() over()
原表
select organization,sum(number) from test group by organization;
select organization,sum(number) over(partition by organization) as sum_num from test;
select organization,ranking,number,sum(number) over(partition by organization order by ranking) as sum_num from test;
over(partition by)只是单纯的将同一个分类里的数值相加，但是如果后面跟上order by或者order by desc的话，前面的sum()值便会在同一个组内按照排名进行数值累加，
30、floor（）使用
mysql> select floor(1.23),floor(-1.23);
+-------------+--------------+
| floor(1.23) | floor(-1.23) |
+-------------+--------------+
| 1 | -2 |
+-------------+--------------+
1 row in set (0.00 sec)
31、rank() over(partition by)的使用
SELECT s.name, s.c1ass,RANK() OVER(PARTITION BY s.class ORDER BY s.score DESC)S FROM students s
SELECT s.name, s.class,DENSE_RANK() OVER(PARTITION BY s.class ORDER BY s.score DESC)s FROM students s
32、between函数
select job,sum(num) sb from resume_info
where date between '2025-01-01' and '2025-12-31'
group by job order by sb desc
33、日期函数格式转换
DATE_FORMAT(date,format)
DATE_FORMAT() 函数用于以不同的格式显示日期/时间数据。
date 参数是合法的日期。format 规定日期/时间的输出格式。
可以使用的格式有：
举例
-- 按月统计数量并排序
select job,date_format(date,'%Y-%m') as mon,sum(num) as cnt
from resume_info
where date like '2025%' -- 符合最左前缀匹配原则，也走索引
group by job,mon
order by mon desc,cnt desc;
34、right 的作用
返回字符串 str 中最右边的 len 个字符；如果任何参数为NULL，则返回NULL。
right 的语法格式
RIGHT(str,len)
例子
SELECT RIGHT('foobarbar', 5); # arbar
SELECT RIGHT(1234567890, 5); # 67890
SELECT RIGHT(NULL, 5); # NULL
35、union的作用
下面的 SQL 语句从 “Websites” 和 “apps” 表中选取所有不同的country（只有不同的值）：
SELECT country FROM Websites
UNION
SELECT country FROM apps
ORDER BY country;
注释：UNION 不能用于列出两个表中所有的country。如果一些网站和APP来自同一个国家，每个国家只会列出一次。UNION 只会选取不同的值。请使用 UNION ALL 来选取重复的值！
下面的 SQL 语句使用 UNION ALL 从 “Websites” 和 “apps” 表中选取所有的country（也有重复的值）：
SELECT country FROM Websites
UNION ALL
SELECT country FROM apps
ORDER BY country;
带有 WHERE 的 SQL UNION ALL
下面的 SQL 语句使用 UNION ALL 从 “Websites” 和 “apps” 表中选取所有的中国(CN)的数据（也有重复的值）：
SELECT country, name FROM Websites
WHERE country='CN'
UNION ALL
SELECT country, app_name FROM apps
WHERE country='CN'
ORDER BY country;
2、错题集
1、查找最晚入职员工的所有信息
select * from employees where hire_date=(
select MAX(hire_date) from employees
)
select *
from employees
order by hire_date DESC
limit 1;
2、查找薪水记录超过15次的员工号emp_no以及其对应的记录次数t
还有就是为什么没有用where而是用的having，记住下面的两句话就好了。
WHERE语句在GROUP BY语句之前；SQL会在分组之前计算WHERE语句。
HAVING语句在GROUP BY语句之后；SQL会在分组之后计算HAVING语句。
select emp_no,count(emp_no) as t from salaries group by emp_no having t >15
3、找出所有员工具体的薪水salary情况，对于相同的薪水只显示一次,并按照逆序显示
select salary from salaries group by salary order by salary desc
一般大表一般用distinct效率不高，大数据量的时候都禁止用distinct，建议用group by解决重复问题。
select distinct salary from salaries order by salary desc
4、找出所有非部门领导的员工
select e.emp_no from employees e left join dept_manager d on e.emp_no=d.emp_no where d.emp_no is null
select emp_no from employees where emp_no not in (select emp_no from dept_manager)
5、获取所有的员工和员工对应的经理，如果员工本身是经理的话则不显示
有一个员工表dept_emp简况如下:
第一行表示为员工编号为10001的部门是d001部门。
有一个部门经理表dept_manager简况如下:
第一行表示为d001部门的经理是编号为10002的员工。
获取所有的员工和员工对应的经理，如果员工本身是经理的话则不显示，以上例子如下:
select de.emp_no,dm.emp_no as manager
from dept_emp de left join dept_manager dm
on de.dept_no=dm.dept_no
where de.emp_no <> dm.emp_no
6、获取每个部门中当前员工薪水最高的相关信息
有一个薪水表salaries简况如下:
获取每个部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary，按照部门编号升序排列，以上例子输出如下:
（注意: Mysql与Sqlite select 非聚合列的结果可能不一样)
select de.dept_no,de.emp_no,s.salary as maxSalary
from dept_emp de inner join salaries s
on de.emp_no=s.emp_no
where s.salary in (
select max(s2.salary)
from dept_emp d2
inner join salaries s2
on d2.emp_no=s2.emp_no
and d2.dept_no = de.dept_no
) order by de.dept_no
不相关子查询
内部查询的执行独立于外部查询，内部查询仅执行一次，执行完毕后将结果作为外部查询的条件使用
select * from score as a where a.cou_id=1 and a.score>(select avg(b.score) from score as b where b.cou_id=1);
相关子查询
内部查询的执行依赖于外部查询的数据，外部查询每执行一次，内部查询也会执行一次。每一次都是外部查询先执行，取出外部查询表中的一个元组，将当前元组中的数据传递给内部查询，然后执行内部查询。根据内部查询执行的结果，判断当前元组是否满足外部查询中的where条件，若满足则当前元组是符合要求的记录，否则不符合要求。然后，外部查询继续取出下一个元组数据，执行上述的操作，直到全部元组均被处理完毕。
select * from score as a where a.score>(select avg(b.score) from score as b where a.cou_id=b.cou_id);
7、查找在职员工自入职以来的薪水涨幅情况
有一个员工表employees简况如下:
有一个薪水表salaries简况如下:
请你查找在职员工自入职以来的薪水涨幅情况，给出在职员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序，以上例子输出为
（注: to_date为薪资调整某个结束日期，或者为离职日期，to_date='9999-01-01’时，表示依然在职，无后续调整记录）
select e.emp_no,(s2.salary-s1.salary) as growth
from employees e inner join salaries s1
on e.emp_no=s1.emp_no and s1.from_date=e.hire_date
inner join salaries s2
on e.emp_no=s2.emp_no and s2.to_date='9999-01-01'
order by growth asc
9、创建一个actor表
create table actor(
actor_id smallint(5) primary key not null,
first_name varchar(45) not null,
last_name varchar(45) not null,
last_update date not null
)
10、添加多条记录
insert into actor
value(1,'PENELOPE','GUINESS','2006-02-15 12:34:33'),
(2,'NICK','WAHLBERG','2006-02-15 12:34:33');
11、对于表actor插入如下数据,如果数据已经存在，请忽略
insert ignore into actor
values(
'3','ED','CHASE','2006-02-15 12:34:33'
);
12、创建一个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
13、删除emp_no重复的记录，只保留最小的id对应的记录。
经查询，MySQL的UPDATE或DELETE中子查询不能为同一张表，可将查询结果再次SELECT。
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
) t1
)
14、将所有to_date为9999-01-01的全部更新为NULL
将所有to_date为9999-01-01的全部更新为NULL,且 from_date更新为2001-01-01。
update titles_test set to_date = null,from_date = "2001-01-01"
where id in (
select upid from (
select id upid from titles_test where to_date = '9999-01-01'
) t1
)
15、查找字符串 10,A,B 中逗号,出现的次数cnt
select ( length("10,A,B") - length(replace("10,A,B",",","")) ) as cnt
16、对于employees表中，给出奇数行的first_name
select e.first_name from employees e join (
select first_name,row_number() over (order by first_name) t from employees
) a
on e.first_name=a.first_name
where a.t %2 = 1
//或者
select e1.first_name
from employees e1
where (select count(*) from employees e2 where e1.first_name >= e2.first_name)%2=1
17、牛客每个人最近的登录日期
牛客每天有很多人登录，请你统计一下牛客新登录用户的次日成功的留存率，
有一个登录(login)记录表，简况如下:
请你写出一个sql语句查询新登录用户次日成功的留存率，即第1天登陆之后，第2天再次登陆的概率,保存小数点后面3位(3位之后的四舍五入)，上面的例子查询结果如下:
mysql里查找某一天的后一天的用法是:DATE_ADD(yyyy-mm-dd,INTERVAL 1 DAY)，四舍五入的函数为round)
select round(count(distinct user_id) *1.0 / (select count(distinct user_id) from login),3) p
from login
where(user_id,date)
in (select user_id,date_add(min(date),interval 1 day) from login group by user_id)
18、统计一下牛客每个日期登录新用户个数
请你写出一个sql语句查询每个日期登录新用户个数，并且查询结果按照日期升序排序，上面的例子查询结果如下:
解题思路
通过userid分组再按照时间排序，得出最早登陆的那天（row_number根据将某字段按照顺序依次添加行号无重复排列的序号）
根据序号为1的就是最早登陆的那天，最后输出日期和计算子查询中根据userid分组序号为1的日期的总和
select date,sum(
case when a.myrank = 1
then 1
else 0
end
)
from (
select date,row_number() over(partition by user_id order by date) myrank
from login
) a
group by date
19、每天有很多人登录，请你统计一下牛客每个日期新用户的次日留存率
select date,ifnull(round(
(sum(
#分子：当前日期作为后一天有该用户的登录记录 并且是第一次登录。（12号作为前一天有这个人，说明13号有这个人）
case when (l.user_id,date_add(l.date,interval 1 day)) in
(select user_id,date from login group by user_id)
then 1 else 0
end
))
/
(sum(
#分母：由于已经分过组，因此遍历当前天的组内每个用户，获得当天的新用户（为每个用户的最小登陆时间），比如12号第一次登录的用户有几个
case when (l.user_id,l.date) in
(select user_id,min(date) from login group by user_id)
then 1 else 0
end
))
,3),0) p
from login l
group by date
order by date
20、请你写出一个sql语句查询刷题信息
有一个刷题（passing_number)表
还有一个用户(user)表，简况如下:
请你写出一个sql语句查询刷题信息
select u.name u_n,date,
sum(pn.number) over(partition by pn.user_id order by pn.date) ps_num
from user u
join passing_number pn
on pn.user_id=u.id
order by pn.date asc,u_n asc;
21、请你找出每个岗位分数排名前2名的用户
请你找出每个岗位分数排名前2名的用户，得到的结果先按照language的name升序排序，再按照积分降序排序，最后按照grade的id升序排序，得到结果如下:
select g.id,l.name,g.score
from language l
join (
select *,dense_rank() over(partition by language_id order by score desc) s_rank
from grade
) g
on g.language_id = l.id and g.s_rank<=2
order by l.name asc,g.score desc,g.id asc
22、查询在每年投递简历的每个岗位
请你写出SQL语句查询在2025年投递简历的每个岗位，每一个月内收到简历的数目，和对应的2026年的同一个月同岗位，收到简历的数目，最后的结果先按first_year_mon月份降序，再按job降序排序显示，以上例子查询结果如下:
SELECT h1.job,first_year_mon,first_year_cnt,second_year_mon,second_year_cnt
from (
select job,date_format(date,'%Y-%m') first_year_mon,sum(num) first_year_cnt
from resume_info
where date like '2025%'
group by job,first_year_mon
) h1 inner join(
select job,date_format(date,'%Y-%m') second_year_mon,sum(num) second_year_cnt
from resume_info
where date like '2026%'
group by job,second_year_mon
) h2
on h1.job=h2.job AND right(first_year_mon,2)=right(second_year_mon,2)
order by first_year_mon desc,h1.job desc;