MySQL 数据库函数教程
MySQL LAG() 函数
1.牛客网
- 1.2 分数
  - 1.2.1 题解 | #考试分数(一)
  - 1.2.2 考试分数2
mysql 分组查询取各分组的前两名及子查询性能优化
字符串截取功能
SQL30 统计每种性别的人数
- 题解 | #计算25岁以上和以下的用户数量
问题分解：
日期函数
SQL把一个表中数据更新到另一个表的多种方法
插入数据
- replace into 插入重复数据
时间差TIMESTAMPDIFF
- 明确考点：
创建一张新表

MySQL 数据库函数教程

https://www.begtut.com/mysql/mysql-sample-database.html

MySQL LAG() 函数

https://www.begtut.com/mysql/mysql-lag-function.html

简介：在本教程中，您将学习如何使用MySQL LAG()函数从同一结果集中的当前行访问上一行的数据。

LAG()函数是一个窗口函数，允许您回顾多行并从当前行访问行的数据。

以下说明了LAG()函数的语法：

LAG(<expression>[,offset[, default_value]]) OVER (
    PARTITION BY expr,...
    ORDER BY expr [ASC|DESC],...
)

expression

LAG()函数返回expression当前行之前的行的值，其值为offset 其分区或结果集中的行数。

offset

offset是从当前行返回的行数，以获取值。offset必须是零或文字正整数。如果offset为零，则LAG()函数计算expression当前行的值。如果未指定offset，则LAG()默认情况下函数使用一个。

default_value

如果没有前一行，则LAG()函数返回default_value。例如，如果offset为2，则第一行的返回值为default_value。如果省略default_value，则默认LAG()返回函数NULL。

`PARTITION BY` 子句

PARTITION BY子句将结果集中的行划分LAG()为应用函数的分区。如果省略PARTITION BY子句，LAG()函数会将整个结果集视为单个分区。

`ORDER BY` 子句

ORDER BY子句指定在LAG()应用函数之前每个分区中的行的顺序。

LAG()函数可用于计算当前行和上一行之间的差异

SQL练习 - 图1

Customers: 用户

Products: 比例模型车列表

ProductLines: 产品系列类别列表

Orders: 销售订单

OrderDetails: 每个销售订单的单行项目

Payments: 根据账户付款

Employees: 存储员工组织架构，向谁报告

Offices: 销售办公室数据

1.牛客网

SQL1 查找最晚入职员工的所有信息

https://www.nowcoder.com/practice/218ae58dfdcd4af195fff264e062138f?tpId=82&tags=&title=&difficulty=0&judgeStatus=0&rp=1

drop table if exists  `employees` ; 
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`));
INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO employees VALUES(10003,'1959-12-03','Parto','Bamford','M','1986-08-28');
INSERT INTO employees VALUES(10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01');
INSERT INTO employees VALUES(10005,'1955-01-21','Kyoichi','Maliniak','M','1989-09-12');
INSERT INTO employees VALUES(10006,'1953-04-20','Anneke','Preusig','F','1989-06-02');
INSERT INTO employees VALUES(10007,'1957-05-23','Tzvetan','Zielinski','F','1989-02-10');
INSERT INTO employees VALUES(10008,'1958-02-19','Saniya','Kalloufi','M','1994-09-15');
INSERT INTO employees VALUES(10009,'1952-04-19','Sumant','Peac','F','1985-02-18');
INSERT INTO employees VALUES(10010,'1963-06-01','Duangkaew','Piveteau','F','1989-08-24');
INSERT INTO employees VALUES(10011,'1953-11-07','Mary','Sluis','F','1990-01-22');

解法1 ：按照入职时间排序然后取出第一条



select * from employees   order by hire_date desc   limit 1 ;
SELECT * FROM employees order by hire_date desc limit 0,1
SELECT * FROM employees WHERE hire_date == (SELECT MAX(hire_date) FROM employees)

SQL 3 : SQL3 查找当前薪水详情以及部门编号dept_no

--  select     d.emp_no ,s.salary ,s.from_date ,d.to_date,d.dept_no 
 --  from dept_manager d  left  join salaries s on  d.emp_no =s.emp_no  order by emp_no  ;

 select     d.emp_no ,s.salary ,s.from_date ,d.to_date,d.dept_no 
 from dept_manager d  , salaries s  where   d.emp_no =s.emp_no  order by emp_no  ;

SQL4 查找所有已经分配部门的员工的last_name和first_name以及dept_no


select  e.last_name,e.first_name,d.dept_no from employees e ,dept_emp d where e.emp_no =d.emp_no 
and d.dept_no is not null

SQL5 :

select  e.last_name,e.first_name,d.dept_no from employees e left   join dept_emp d   on e.emp_no =d.emp_no

1.2 分数

1.2.1 题解 | #考试分数(一)

题目链接：http://www.nowcoder.com/practice/f41b94b4efce4b76b27dd36433abe398

drop table if exists grade;
CREATE TABLE  grade(
`id` int(4) NOT NULL,
`job` varchar(32) NOT NULL,
`score` int(10) NOT NULL,
PRIMARY KEY (`id`));

INSERT INTO grade VALUES
(1,'C++',11001),
(2,'C++',10000),
(3,'C++',9000),
(4,'Java',12000),
(5,'Java',13000),
(6,'JS',12000),
(7,'JS',11000),
(8,'JS',9999);
预期：
输出：
Java|12500.000
JS|10999.667
C++|10000.333

题解：

先按照job 分组然后统计每个分组的求和除以统计个数、然后保留小数3位。再按照平均分排序
-- select job|avg(1.0*score) from grade  group by job  ;
   select   job ,  round((1.0*sum(score)/count(id) ),3) as a from grade  group by job  order by a desc ;

1.2.2 考试分数2

：http://www.nowcoder.com/practice/f456dedf88a64f169aadd648491a27c1

drop table if exists grade;
CREATE TABLE  grade(
`id` int(4) NOT NULL,
`job` varchar(32) NOT NULL,
`score` int(10) NOT NULL,
PRIMARY KEY (`id`));

INSERT INTO grade VALUES
(1,'C++',11001),
(2,'C++',10000),
(3,'C++',9000),
(4,'Java',12000),
(5,'Java',13000),
(6,'JS',12000),
(7,'JS',11000),
(8,'JS',9999),
(9,'Java',12500);
输出：
1|C++|11001
5|Java|13000
6|JS|12000
7|JS|11000

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

   -- select job|avg(1.0*score) from grade  group by job  ;
      select   job ,  round((1.0*sum(score)/count(id) ),3) as a from grade  group by job  order by a desc ;

mysql 分组查询取各分组的前两名及子查询性能优化

https://blog.csdn.net/sinat_41780498/article/details/79956561

背景求出运动员前两个比赛记录

如何找出每个程序最近的日志条目？如何找出每个用户的最高分？在每个分类中最受欢迎的商品是什么?通常这类“找出每个分组中最高分的条目”的问题可以使用相同的技术来解决。在这篇文章里我将介绍如何解决这类问题，而且会介绍如何找出最高的前几名而不仅仅是第一名。

https://www.cnblogs.com/zhuiluoyu/p/6862547.html

1.2.3 考试分数3 重要

找出每个岗位分数排名前2名的用户，得到的结果先按照language的name升序排序，再按照积分降序排序，最后按照grade的id升序排序

drop table if exists grade;
drop table if exists language;
CREATE TABLE `grade` (
`id` int(4) NOT NULL,
`language_id` int(4) NOT NULL,
`score` int(4) NOT NULL,
PRIMARY KEY (`id`));

CREATE TABLE `language` (
`id` int(4) NOT NULL,
`name` varchar(32) NOT NULL,
PRIMARY KEY (`id`));

INSERT INTO grade VALUES
(1,1,12000),
(2,1,13000),
(3,2,11000),
(4,2,10000),
(5,3,11000),
(6,1,11000),
(7,2,11000);

INSERT INTO language VALUES
(1,'C++'),
(2,'JAVA'),
(3,'Python');

2|C++|13000
1|C++|12000
3|JAVA|11000
7|JAVA|11000
4|JAVA|10000
5|Python|11000

核心思路: 将当前表分数跟一个临时表进行比较，当临时表没有一个分数，或者只有一个分数比当前表高时，说明该分数排名是第一名，或者第二名。
举例：只看C++语言，id为1时，12000<13000，说明这是第二名，id为2时，13000统计出来为0个，没人比他小，说明他是第一名，id为6时，可以知道11000小于12000和13000，为2，说明是第三名。
避坑点：一定要去重！不然在java语言的时候，10000小于两个12000，就是第三名了，然而题目要求可以并列，为第二名


      select   g1.id ,la.name ,g1.score  from  grade  g1    ,language la where
        g1.language_id = la.id  and 
         ( select count( distinct  g2.score) from grade g2 where  g1.language_id=g2.language_id 
         and g1.score < g2.score)  in (0,1)  order by la.name  asc,   g1.score desc ,g1.id asc ;

SQL75 考试分数(四) 分组分数中位数较难

SQL练习 - 图2

drop table if exists grade;
CREATE TABLE  grade(
`id` int(4) NOT NULL,
`job` varchar(32) NOT NULL,
`score` int(10) NOT NULL,
PRIMARY KEY (`id`));

INSERT INTO grade VALUES
(1,'C++',11001),
(2,'C++',10000),
(3,'C++',9000),
(4,'Java',12000),
(5,'Java',13000),
(6,'B',12000),
(7,'B',11000),
(8,'B',9999);

解法1 ：

  select  job, FLOOR((count(*)+1)/2)  as "start" ,CEIL((count(*)+1)/2) as "end" 
  from  grade   group by job  order by  job ;


  select  job, FLOOR((count(*)+1)/2)  as "start" ,floor((count(*)+2)/2) as "end" 
  from  grade   group by job  order by  job ;

解法2 ：

case when ：

 select job,
 case  when   count(score)%2=0  then floor(count(score)/2) 
 else floor( count(score)/2 +1)  end  as "start", 
 case  when  count(score)%2 =0 then floor(count(score)/2)+1 else floor(count(score)/2)+1
 end  as "end" 
 from   grade   group by job order by  job ;

SQL76 考试分数(五)

字符串截取功能

SQL30 统计每种性别的人数

CREATE TABLE `user_submit` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`profile` varchar(100) NOT NULL,
`blog_url` varchar(100) NOT NULL
);
INSERT INTO user_submit VALUES(1,2138,'180cm,75kg,27,male','http:/url/bisdgboy777');
INSERT INTO user_submit VALUES(1,3214,'165cm,45kg,26,female','http:/url/dkittycc');
INSERT INTO user_submit VALUES(1,6543,'178cm,65kg,25,male','http:/url/tigaer');
INSERT INTO user_submit VALUES(1,4321,'171cm,55kg,23,female','http:/url/uhsksd');
INSERT INTO user_submit VALUES(1,2131,'168cm,45kg,22,female','http:/url/sysdney');


select * from  user_submit  

select    case  PROFILE when like '%male' then 1 when   like '%fmale' then 0  else -1 end   from user_submit ;


select SUBSTRING_INDEX(PROFILE,',',-1)  as gender ,count(*) from  user_submit    group by gender  ;

字符串截取：

https://www.cnblogs.com/zdz8207/p/3765073.html

1、LOCATE(substr , str )：返回子串 substr 在字符串 str 中第一次出现的位置，如果字符substr在字符串str中不存在，则返回0；

2、POSITION(substr IN str )：返回子串 substr 在字符串 str 中第一次出现的位置，如果字符substr在字符串str中不存在，与LOCATE函数作用相同；

3、LEFT(str, length)：从左边开始截取str，length是截取的长度；

4、RIGHT(str, length)：从右边开始截取str，length是截取的长度；

5、SUBSTRING_INDEX(str ,substr ,n)：返回字符substr在str中第n次出现位置之前的字符串;

6、SUBSTRING(str ,n ,m)：返回字符串str从第n个字符截取到第m个字符；

7、REPLACE(str, n, m)：将字符串str中的n字符替换成m字符；

8、LENGTH(str)：计算字符串str的长度。

SQL31 提取博客URL中的用户名

select  device_id ,SUBSTRING_INDEX(blog_url,'/',-1)  as user_name   from user_submit ;


2138    bisdgboy777
3214    dkittycc
6543    tigaer
4321    uhsksd
2131    sysdney

SQL67 牛客每个人最近的登录日期(二)

drop table if exists login;
drop table if exists user;
drop table if exists client;
CREATE TABLE `login` (
`id` int(4) NOT NULL,
`user_id` int(4) NOT NULL,
`client_id` int(4) NOT NULL,
`date` date NOT NULL,
PRIMARY KEY (`id`));

CREATE TABLE `user` (
`id` int(4) NOT NULL,
`name` varchar(32) NOT NULL,
PRIMARY KEY (`id`));

CREATE TABLE `client` (
`id` int(4) NOT NULL,
`name` varchar(32) NOT NULL,
PRIMARY KEY (`id`));

INSERT INTO login VALUES
(1,2,1,'2020-10-12'),
(2,3,2,'2020-10-12'),
(3,1,3,'2020-10-12'),
(4,2,2,'2020-10-13'),
(5,3,2,'2020-10-13');

INSERT INTO user VALUES
(1,'tm'),
(2,'fh'),
(3,'wangchao');

INSERT INTO client VALUES
(1,'pc'),
(2,'ios'),
(3,'anroid'),
(4,'h5');

牛客每个人最近的登录日期(二)题解
发表于 2020-08-17 17:56:21
先根据用户分组，查出每个用户登录的最新日期(一):
1
select user_id,max(date) from login group by login.user_id ;
然后查找出所有用户的名字，所有的登录设备，所有的登录日期(二)：
1
2
3
4
5
select user.name as u_n, client.name as c_n,
login.date
from login 
join user on login.user_id=user.id
join client on login.client_id=client.id
查找的结果应该是:

那么再根据用户id和最新的登录日期(一)，可以在所有的数据（二)里面，从而确定唯一一组数据，最后再按照名字排序(三)：
复制代码

select user.name as u_n, client.name as c_n,
login.date
from login 
join user on login.user_id=user.id
join client on login.client_id=client.id
where (login.user_id,login.date) in
(select user_id,max(date) from login group by login.user_id )
order by user.name;
然后查找出所有用户的名字，所有的登录设备，所有的登录日期(二)：

select user.name as u_n, client.name as c_n,
login.date
from login 
join user on login.user_id=user.id
join client on login.client_id=client.id
查找的结果应该是:

那么再根据用户id和最新的登录日期(一)，可以在所有的数据（二)里面，从而确定唯一一组数据，最后再按照名字排序(三)：
复制代码

select user.name as u_n, client.name as c_n,
login.date
from login 
join user on login.user_id=user.id
join client on login.client_id=client.id
where (login.user_id,login.date) in
(select user_id,max(date) from login group by login.user_id )
order by user.name;

SQL18 分组计算练习题

每个学校每种性别用户数，30天内平均活跃天数和平均发帖数量

问题分解：

按照学校性别分组：group by gender, university

用户数：count(device_id)

30天内活跃数avg(active_days_within_30)

平均发帖数量： avg（question_cnt）



select gender ,university 
count(device_id) as user_num ,
avg(activity_days_within_30)
as  avg_activity_days, 
avg(question_cnt) as avg_question_cnt
from user_profile group by ,university ;

SQL19 分组过滤练习题

现在运营想查看每个学校用户的平均发贴和回帖情况，寻找低活跃度学校进行重点运营，请取出平均发贴数低于5的学校或平均回帖数小于20的学校。

https://www.nowcoder.com/practice/ddbcedcd9600403296038ee44a172f2d?tpId=199&tqId=1975670&ru=%2Fexam%2Foj&qru=%2Fta%2Fsql-quick-study%2Fquestion-ranking&sourceUrl=%2Fexam%2Foj

题解：

https://www.nowcoder.com/practice/ddbcedcd9600403296038ee44a172f2d?tpId=199&tqId=1975670&ru=%2Fexam%2Foj&qru=%2Fta%2Fsql-quick-study%2Fquestion-ranking&sourceUrl=%2Fexam%2Foj

限定条件：平均发帖数低于5 或者平局回帖数低于20 avg（question_cnt）<5 or

avg(answer_cnt) <20 ,聚合函数结果作为筛选条件时，不能用where ,而是having 语法

按照学校输出：对每个学校统计其平局回帖数，group by university

--     平均发帖数小question_cnt  < 5 
 --  平均回帖数answer_cnt < 20 

select university ,
avg(question_cnt)  as avg_question_cnt
,avg(answer_cnt) as 
avg_answer_cnt 
from user_profile   group by university 
     having avg_question_cnt< 5 or  avg_answer_cnt <20
;

统计每个学校的答过题的用户的平均答题数

https://www.nowcoder.com/practice/88aa923a9a674253b861a8fa56bac8e5?tpId=199&tags=&title=&difficulty=0&judgeStatus=0&rp=0

问题分解：

每个学校：按照学校分组 group by university

平均答题数量：每个学校的分组内，用总答题数量除以总人数即可得到平均答题数量

count(question_id )/count(distinct device_id)

表链接：学校和答题信息不在同一个表


select u.university ,  count(q.question_id)/count(DISTINCT q.device_id)  from question_practice_detail  q   inner join 
user_profile  u  where  q.device_id =u.device_id  group by u.university

SQL25 查找山东大学或者性别为男生的信息

注意输出的顺序，先输出学校为山东大学再输出性别为男生的信息）

SELECT  device_id ,gender,age ,gpa   from user_profile 
where university ='山东大学' 
UNION all 
SELECT  device_id ,gender,age ,gpa   from user_profile where 
gender ='male' ;

题解 | #计算25岁以上和以下的用户数量



select  '25岁以下' as   age_cut  ,   count(device_id)  as  number  from user_profile 
where age <25 OR age is null  

union all 
select '25岁及以上'  as  age_cut  ,count(device_id)  as number  from  user_profile 
where age >=25 ;

SQL27 查看不同年龄段的用户明细

问题分解：

限定条件：无；
划分年龄段：数值条件判断，可以用多重if，不过更方便的是用case when [expr] then [result1]...else [default] end

https://www.nowcoder.com/practice/ae44b2b78525417b8b2fc2075b557592?tpId=199&tags=&title=&difficulty=0&judgeStatus=0&rp=0

正确：

select  device_id ,gender , case  when 
  age <20  then '20岁以下'
  when  age >= 20 and age <=24 then '20-24岁'
  when age >=25  then '25岁及以上'
   else '其他' end as age_cut 
from  user_profile ;

2138|male|20-24岁
3214|male|其他
6543|female|20-24岁
2315|female|20-24岁
5432|male|25岁及以上
2131|male|25岁及以上
4321|male|25岁及以上

错误; case age when


select  device_id ,gender , case age when 
  age <20  then '20岁以下'
  when  age >= 20 and age <=24 then '20-24岁'
  when age >=25  then '25岁及以上'
   else '其他' end as age_cut 
from  user_profile ;

2138|male|其他
3214|male|其他
6543|female|其他
2315|female|其他
5432|male|其他

日期函数

SQL28 计算用户8月每天的练题数量

https://www.nowcoder.com/practice/847373e2fe8d47b4a2c294bdb5bda8b6?tpId=199&tags=&title=&difficulty=0&judgeStatus=0&rp=0

分析：

2021 年8 月

用 year/month 函数的

year(date)=2021 and month(date) =8 比如用date_format 函数的 date_format(date,”%Y-%m”)=”202108” 每天：按照天分组 group by date 题目数量 : count(question_id)

select  day(date) as  day , count(question_id) as     question_cnt from question_practice_detail  where month (date)=8 and year(date) =2021 group by date

SQL29 计算用户的平均次日留存率

将一张表的内容插入到另外一张表：

某打车公司要将驾驶里程（drivedistanced）超过5000里的司机信息转存到一张称为seniordrivers 的表中，他们的详细情况被记录在表drivers 中，正确的sql语句为（）

https://www.nowcoder.com/test/question/done?tid=51750013&qid=326265#summary

insert into 目标表（字段1，字段2， ,…） select 字段1，字段2 ,..

from 来源表

sql insert into test ( …) select ( …) from mcc

SQL把一个表中数据更新到另一个表的多种方法

https://blog.csdn.net/tiguer/article/details/77197796?spm=1001.2101.3001.6650.1&utm_medium=distribute.pc_relevant.none-task-blog-2~default~CTRLIST~Rate-1.pc_relevant_paycolumn_v3&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2~default~CTRLIST~Rate-1.pc_relevant_paycolumn_v3&utm_relevant_index=2

SQL39 21年8月份练题总数

现在运营想要了解2021年8月份所有练习过题目的总用户数和练习过题目的总次数，请取出相应结果

SELECT  count( distinct device_id)  as did_cnt, count(question_id) as 
question_cnt  from question_practice_detail 
where MONTH(date) =8 and   YEAR(date)=2021 ;

插入数据

replace into 插入重复数据

https://www.nowcoder.com/practice/978bcee6530a430fb0be716423d84082?tpId=240&tqId=2223556&ru=/ta/sql-advanced&qru=/ta/sql-advanced/question-ranking

 replace  into  examination_info(exam_id,tag,difficulty,duration ,release_time ) 
 values(9003,'SQL','hard',90,'2021-01-01 00:00:00');

等效 :

DELETE FROM examination_info
WHERE exam_id=9003;
INSERT INTO examination_info
VALUES(NULL,9003, 'SQL','hard', 90, '2021-01-01 00:00:00')

时间差TIMESTAMPDIFF

https://www.nowcoder.com/practice/d331359c5ca04a3b87f06b97da42159c?tpId=240&tags=&title=&difficulty=0&judgeStatus=0&rp=0

明确考点：

删除记录的方式汇总：

根据条件删除：DELETE FROM tb_name [WHERE options] [ [ ORDER BY fields ] LIMIT n ]
全部删除（表清空，包含自增计数器重置）：TRUNCATE tb_name

时间差：

TIMESTAMPDIFF(interval, time_start, time_end)可计算time_start-time_end的时间差，单位以指定的interval为准，常用可选：
- SECOND 秒
- MINUTE 分钟（返回秒数差除以60的整数部分）
- HOUR 小时（返回秒数差除以3600的整数部分）
- DAY 天数（返回秒数差除以3600*24的整数部分）
- MONTH 月数
- YEAR 年数


delete from exam_record where TIMESTAMPDIFF(MINUTE,start_time,submit_time)<5 and score <60 ;

删除最早的三条：

delete from    exam_record where
TIMESTAMPDIFF(MINUTE,start_time,submit_time)<5 or submit_time is 
null   order by start_time  limit 3 ;

删除表内容并且重置自增主键

方法1 ： truncate table exam_record

方法2：

创建一张新表

 create table  user_info_vip (
   id int   primary key  auto_increment   not null    comment '自增ID' ,
   uid int  unique  not  null  comment '用户ID'  ,
   nick_name varchar(64)   comment '昵称',
   achievement int  default 0  comment '成就值',
   level   int  comment '用户等级',
   job    varchar(32) comment '职业方向',
   register_time datetime default CURRENT_TIMESTAMP comment '注册时间' 
 )CHARACTER SET utf8 COLLATE utf8_general_ci;

SQL 12 创建索引

http://www.nowcoder.com/practice/f2ea9ccf33c740d58576608940981807

解析：

https://www.nowcoder.com/practice/f2ea9ccf33c740d58576608940981807?tpId=240&tqId=2223570&ru=/exam/oj&qru=/ta/sql-advanced/question-ranking&sourceUrl=%2Fexam%2Foj%3Ftab%3DSQL%25E7%25AF%2587%26topicId%3D240

创建唯一索引

create 方式创建

create  unique  --  唯一索引
 fulltext   --  全文索引
 index  index_name  on  table_name  -- 不指定唯一或者
 全文默认是普通索引
 (column1[(length) [DESC|ASC]] [,column2,...]) -- 可以对多列建立组合索引

alter 方式创建索引： alter table tb_name add [unique | fulltext] [ index ]

index_content(content)

drop 方式删除索引 drop index <索引名> on <表名>

alter 方式删除索引： alter table < 表名> drop index <索引名>

索引的使用：

索引使用的时候满足最左匹配原则，即对组合索引（col1,col2）,在不考虑引擎优化时，条件必须是col1 在前面col2 在后，或者只使用col1 ,索引才会生效。

索引不包含有null 值得列

    create index idx_duration on examination_info(duration);
    create unique index uniq_idx_exam_id on examination_info(exam_id);
    create   fulltext index full_idx_tag on examination_info(tag);

删除索引：

    drop  index uniq_idx_exam_id on examination_info;
    drop   index full_idx_tag on examination_info;

SQL14 SQL类别高难度试卷得分的截断平均值


SELECT   rt.tag,rt.difficulty,  ROUND((sum(score) -max(score) -min(score))/ (count(score)-2),1)
       as clip_avg_score        from exam_record   er  join examination_info  rt  USING(exam_id)
where rt.tag='SQL' and rt.difficulty='hard';

1 using()用于两张表的join查询，要求using()指定的列在两个表中均存在，并使用之用于join的条件。 示例： select a.*, b.* from a left join b using(colA); 等同于： select a.*, b.* from a left join b on a.colA = b.colA; 2 多表查询就使用多个这样的j

1 using()用于两张表的join查询，，要求using()指定的列在两个表中均存在，并使用之用于join的条件。 示例： select a.*, b.* from a left join b using(colA); 等同于： select a.*, b.* from a left join b on a.colA = b.colA;

2 多表查询就使用多个这样的join。 示例： select a.*, b.* from a left join b using(colA) left join c using(colB); 要求colA在a, b中均存在，colB在a, c表中均存在。

总之，using() 只是join中指定连接条件的简写，在简单的连接中常用。在列名称不同时或连接条件复杂时就无法用了，使用 a left join b on ... 是更常见的做法。
————————————————
版权声明：本文为CSDN博主「镰刀刀」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/weixin_42356552/article/details/113170473

603. 连续空余座位

# Write your MySQL query statement below
解题思路
此处撰写解题思路

cinema 表内连接 第一种情况：t2.seat_id= t1.seat_id+1 t1(3) --t2(4)
cinema 表内连接 第一种情况：t2.seat_id-1= t1.seat_id t1(4) --t2(5)
3 union 去重
代码

# Write your MySQL query statement below

select * from (
 select  t1.seat_id from  cinema  t1 ,cinema  t2  where    t1.free =1  
 and t2.seat_id= t1.seat_id+1  and t2.free=1
 union 
select  t2.seat_id from  cinema  t1 ,cinema  t2  where    t1.free =1  
and t2.seat_id-1= t1.seat_id and t2.free=1 ) a  order by seat_id;

作者：zengjx
链接：https://leetcode-cn.com/problems/consecutive-available-seats/solution/union-by-zengjx-caol/
来源：力扣（LeetCode）
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。
  select  distinct a.seat_id from cinema a ,cinema b
  where abs(a.seat_id-b.seat_id) =1 and a.free=true and b.free=true   order by  a.seat_id ;

511. 游戏玩法分析 I

活动表 Activity：

写一条 SQL 查询语句获取每位玩家第一次登陆平台的日期。

查询结果的格式如下所示：

Activity 表：
+—————-+—————-+——————+———————+
| player_id | device_id | event_date | games_played |
+—————-+—————-+——————+———————+
| 1 | 2 | 2016-03-01 | 5 |
| 1 | 2 | 2016-05-02 | 6 |
| 2 | 3 | 2017-06-25 | 1 |
| 3 | 1 | 2016-03-02 | 0 |
| 3 | 4 | 2018-07-03 | 5 |
+—————-+—————-+——————+———————+

Result 表：
+—————-+——————-+
| player_id | first_login |
+—————-+——————-+
| 1 | 2016-03-01 |
| 2 | 2017-06-25 |
| 3 | 2016-03-02 |
+—————-+——————-+

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/game-play-analysis-i
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

#  按照palayer_id 分组 ，order by event_date  取最少值

select  player_id ,min(event_date)  as first_login   from activity   
where  event_date  group by player_id ;

over partiotion

select player_id, event_date  as first_login from (
select player_id ,
event_date,
dense_rank()   over(partition  by player_id  order by event_date) as 排名
from activity)  as temp  where 排名=1  ;

512. 游戏玩法分析 II

able: Activity

查询结果格式在以下示例中：

Activity table:
+—————-+—————-+——————+———————+
| player_id | device_id | event_date | games_played |
+—————-+—————-+——————+———————+
| 1 | 2 | 2016-03-01 | 5 |
| 1 | 2 | 2016-05-02 | 6 |
| 2 | 3 | 2017-06-25 | 1 |
| 3 | 1 | 2016-03-02 | 0 |
| 3 | 4 | 2018-07-03 | 5 |
+—————-+—————-+——————+———————+

Result table:
+—————-+—————-+
| player_id | device_id |
+—————-+—————-+
| 1 | 2 |
| 2 | 3 |
| 3 | 1 |
+—————-+—————-+

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/game-play-analysis-ii
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

SQL：方法一

select activity.player_id, activity.device_id from activity, (
select player_id, min(event_date) as first_login
from activity group by player_id
) as temp
where activity.player_id = temp.player_id
and activity.event_date = temp.first_login;
解析
先是用子查询查出每个 player_id 最小的 event_date 命名为 temp 临时表
activity 和 temp 连查，筛选出 player_id 相等，并且 activity.event_date = temp.first_login 相等的数据
SQL：方法二

select player_id, device_id from (
select
player_id,
device_id,
dense_rank() over(partition by player_id order by event_date) as 排名
from activity
) as temp where temp.排名 = 1;
解析
这个方法和游戏玩法分析 I 中的方法二一样。

SQL：方法三

select player_id, device_id from activity a1 where event_date <= all(
select a2.event_date from activity a2 where a1.player_id = a2.player_id
);
解析
使用 all 配合 <= 筛选出 a1.player_id = a2.player_id 的 event_date 数据

Tips

all 方法使用

作者：uccs
链接：https://leetcode-cn.com/problems/game-play-analysis-ii/solution/san-chong-fang-fa-jie-ti-by-uccs-ht2k/
来源：力扣（LeetCode）
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

any、all关键字必须与一个比较操作符一起使用。any关键词可以理解为“对于子查询返回的列中的任一数值，如果比较结果为true，则返回true”。all的意思是“对于子查询返回的列中的所有值，如果比较结果为true，则返回true”

any 可以与=、>、>=、<、<=、<>结合起来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的任何一个数据。

all可以与=、>、>=、<、<=、<>结合是来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的其中的所有数据。

举个例子

select s1 from t1 where s1 > any (select s1 from t2);

假设any后面的s1返回了三个值，那其实就等价于

select s1 from t1 where s1 > result1 or s1 > result2 or s2 > result3

而all的用法相当于把上述语句的‘or’缓冲‘and’

说道这你可能已经知道了any和or常用于两表的对比，当你不知道where后面具体值是可以用any，all帮你判定。

534. 游戏玩法分析 III

Table: Activity

编写一个 SQL 查询，同时报告每组玩家和日期，以及玩家到目前为止玩了多少游戏。也就是说，在此日期之前玩家所玩的游戏总数。详细情况请查看示例。

查询结果格式如下所示：

Activity table:
+—————-+—————-+——————+———————+
| player_id | device_id | event_date | games_played |
+—————-+—————-+——————+———————+
| 1 | 2 | 2016-03-01 | 5 |
| 1 | 2 | 2016-05-02 | 6 |
| 1 | 3 | 2017-06-25 | 1 |
| 3 | 1 | 2016-03-02 | 0 |
| 3 | 4 | 2018-07-03 | 5 |
+—————-+—————-+——————+———————+

Result table:
+—————-+——————+——————————-+
| player_id | event_date | games_played_so_far |
+—————-+——————+——————————-+
| 1 | 2016-03-01 | 5 |
| 1 | 2016-05-02 | 11 |
| 1 | 2017-06-25 | 12 |
| 3 | 2016-03-02 | 0 |
| 3 | 2018-07-03 | 5 |
+—————-+——————+——————————-+
对于 ID 为 1 的玩家，2016-05-02 共玩了 5+6=11 个游戏，2017-06-25 共玩了 5+6+1=12 个游戏。
对于 ID 为 3 的玩家，2018-07-03 共玩了 0+5=5 个游戏。
请注意，对于每个玩家，我们只关心玩家的登录日期。

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/game-play-analysis-iii
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

https://leetcode-cn.com/problems/game-play-analysis-iii/solution/you-xi-wan-fa-fen-xi-iiisi-lu-qing-xi-by-cfhj/

解题思路
该题目的解答重点是知道如何进行内联，连接表的结构是什么样子的，然后再进行分组查询
内联：连接条件a.player_id=b.player_id and a.event_date<=b.event_date,以实例为例，执行该连接后的表为：

SQL练习 - 图3

然后在按照b表player_id分组，再按照event_date分组
最后注意的是查询条件为b.event_date,sum(a.games_played) as games_played_so_far,至于player_id,用表a或表b的都可以

 select   a.player_id,b.event_date,sum(a.games_played) as games_played_so_far from activity  a inner join activity b  on a.player_id =b.player_id   and a.event_date <=b.event_date
 group by b.player_id ,b.event_date;
 ;

？解法2：在 sum 函数后面可以使用 over 对其按照 player_id 分组，并按照 event_date 排序。

select 
player_id,
event_date,
sum(games_played)  over (partition by player_id  order by event_date) as games_played_so_far  from  activity;

{*}SQL：方法一

select player_id, event_date,
sum(
case
when @pre_player_id = player_id then @n:=@n+games_played
when @pre_player_id:= player_id then @n:=games_played
end
) as games_played_so_far
from (
select * from activity order by player_id, event_date
) temp, (select @pre_player_id:=null, @n:=0) init
group by player_id, event_date;
解析
因为最终的结果是计算每个用户在某天玩游戏的次数，所以需要按照 player_id 和 event_date 分组。

因为 order by 执行的顺序在 sum 函数后面执行，所以这里需要先对 player_id 和 event_date 先进行排序。

具体实现：

将 activity 按照 player_id 和 event_date 升序排序，命名为 temp 临时表
将临时表 temp 按照 player_id 和 event_date 进行分组
使用 case … when … then …end 语句对分组后的 temp 中 games_played 进行输出，并用 sum 求和

作者：uccs
链接：https://leetcode-cn.com/problems/game-play-analysis-iii/solution/san-chong-fang-fa-jiang-jie-by-uccs-vanc/
来源：力扣（LeetCode）
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

550. 游戏玩法分析 IV

难度中等65

SQL架构

Table: Activity

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| player_id    | int     |
| device_id    | int     |
| event_date   | date    |
| games_played | int     |
+--------------+---------+
（player_id，event_date）是此表的主键。
这张表显示了某些游戏的玩家的活动情况。
每一行是一个玩家的记录，他在某一天使用某个设备注销之前登录并玩了很多游戏（可能是 0）。

编写一个 SQL 查询，报告在首次登录的第二天再次登录的玩家的比率，四舍五入到小数点后两位。换句话说，您需要计算从首次登录日期开始至少连续两天登录的玩家的数量，然后除以玩家总数。

查询结果格式如下所示：

Activity table:
+-----------+-----------+------------+--------------+
| player_id | device_id | event_date | games_played |
+-----------+-----------+------------+--------------+
| 1         | 2         | 2016-03-01 | 5            |
| 1         | 2         | 2016-03-02 | 6            |
| 2         | 3         | 2017-06-25 | 1            |
| 3         | 1         | 2016-03-02 | 0            |
| 3         | 4         | 2018-07-03 | 5            |
+-----------+-----------+------------+--------------+

Result table:
+-----------+
| fraction  |
+-----------+
| 0.33      |
+-----------+
只有 ID 为 1 的玩家在第一天登录后才重新登录，所以答案是 1/3 = 0.33

首次登录第二天再次登录：

577. 员工奖金

难度简单26

SQL架构

选出所有 bonus < 1000 的员工的 name 及其 bonus。

Employee 表单

+-------+--------+-----------+--------+
| empId |  name  | supervisor| salary |
+-------+--------+-----------+--------+
|   1   | John   |  3        | 1000   |
|   2   | Dan    |  3        | 2000   |
|   3   | Brad   |  null     | 4000   |
|   4   | Thomas |  3        | 4000   |
+-------+--------+-----------+--------+
empId 是这张表单的主关键字

Bonus 表单

+-------+-------+
| empId | bonus |
+-------+-------+
| 2     | 500   |
| 4     | 2000  |
+-------+-------+
empId 是这张表单的主关键字

输出示例：

+-------+-------+
| name  | bonus |
+-------+-------+
| John  | null  |
| Dan   | 500   |
| Brad  | null  |
+-------+-------+

算法
首先需要知道每个员工的奖金数量，因此需要首先将 Employee 表与 Bonus 表连接。注意需要使用外连接，以处理员工没有出现在 Bonus 表上的情况。这里因为不存在员工只出现在 Bonus 表中的情况，所以只需要使用左外连接（left join 或 left outer join）。

MySQL

select name, bonus
from Employee left join Bonus
on Employee.EmpId = Bonus.EmpId
对于题目中的样例，上面的代码运行可以得到如下输出：

name	bonus
Dan	500
Thomas	2000
Brad
John

其中 Brad 和 John 的 bonus 值为空，空值在数据库中的表示为 null。我们使用 bonus is null（而不是 bonus = null）判断奖金是否为 null。随后即可用 where 子句筛选奖金小于 1000 或者为空的员工。

MySQL

select name, bonus
from Employee left join Bonus
on Employee.EmpId = Bonus.EmpId
where bonus is null or bonus < 1000

作者：LeetCode-Solution
链接：https://leetcode-cn.com/problems/employee-bonus/solution/yuan-gong-jiang-jin-by-leetcode-solution/
来源：力扣（LeetCode）
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

607. 销售员

表: SalesPerson

表: Company

表: Orders

编写一个SQL查询，报告没有任何与名为 “RED” 的公司相关的订单的所有销售人员的姓名。

以任意顺序返回结果表。

查询结果格式如下所示。

示例 1:

输入:
SalesPerson 表:
+—————+———+————+————————-+——————+
| sales_id | name | salary | commission_rate | hire_date |
+—————+———+————+————————-+——————+
| 1 | John | 100000 | 6 | 4/1/2006 |
| 2 | Amy | 12000 | 5 | 5/1/2010 |
| 3 | Mark | 65000 | 12 | 12/25/2008 |
| 4 | Pam | 25000 | 25 | 1/1/2005 |
| 5 | Alex | 5000 | 10 | 2/3/2007 |
+—————+———+————+————————-+——————+
Company 表:
+————+————+—————+
| com_id | name | city |
+————+————+—————+
| 1 | RED | Boston |
| 2 | ORANGE | New York |
| 3 | YELLOW | Boston |
| 4 | GREEN | Austin |
+————+————+—————+
Orders 表:
+—————+——————+————+—————+————+
| order_id | order_date | com_id | sales_id | amount |
+—————+——————+————+—————+————+
| 1 | 1/1/2014 | 3 | 4 | 10000 |
| 2 | 2/1/2014 | 4 | 5 | 5000 |
| 3 | 3/1/2014 | 1 | 1 | 50000 |
| 4 | 4/1/2014 | 1 | 4 | 25000 |
+—————+——————+————+—————+————+
输出:
+———+
| name |
+———+
| Amy |
| Mark |
| Alex |
+———+
解释:
根据表 orders 中的订单 ‘3’ 和 ‘4’ ，容易看出只有 ‘John’ 和 ‘Pam’ 两个销售员曾经向公司 ‘RED’ 销售过。
所以我们需要输出表 salesperson 中所有其他人的名字。

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/sales-person
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

解法：先找出所有red 关联的员工。在salsperson 找出不在其中的。

select  o.sales_id  from company c ,orders   o where   o.com_id =c.com_id and   c.name='red'


  select   s.name   from salesperson  s where s.sales_id not in 
  ( 
      select  o.sales_id  from company c ,orders   o where   o.com_id =c.com_id and   c.name='red'
) ;

解法2：


select s.name  from salesperson s  
left  join 
 orders o on s.sales_id =o.sales_id
left  join  company c  on  o.com_id =c.com_id 
 group by s.name   HAVING
    SUM(IF(C.name = 'RED', 1, 0))  = 0    order by s.sales_id ;

1045. 买下所有产品的客户

难度中等30

SQL架构

Customer 表：

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| customer_id | int     |
| product_key | int     |
+-------------+---------+
product_key 是 Customer 表的外键。

Product 表：

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| product_key | int     |
+-------------+---------+
product_key 是这张表的主键。

写一条 SQL 查询语句，从 Customer 表中查询购买了 Product 表中所有产品的客户的 id。

示例：

Customer 表：
+-------------+-------------+
| customer_id | product_key |
+-------------+-------------+
| 1           | 5           |
| 2           | 6           |
| 3           | 5           |
| 3           | 6           |
| 1           | 6           |
+-------------+-------------+

Product 表：
+-------------+
| product_key |
+-------------+
| 5           |
| 6           |
+-------------+

Result 表：
+-------------+
| customer_id |
+-------------+
| 1           |
| 3           |
+-------------+
购买了所有产品（5 和 6）的客户的 id 是 1 和 3 。

解法：因为已经是外键，不需要考虑在customer 而不在product表，按照 customer_id 分组之后统计count(product_key) 个数与product的count(product_key) 相同

执行用时：616 ms, 在所有 MySQL 提交中击败了12.81%

  -- 不同产品个数

  --  select  count(product_key) from product ;


 select  c.customer_id  
 from customer c
   group by customer_id   having  count(distinct product_key)  in ( select  count(    distinct(product_key)) from product);

在所有 MySQL 提交中击败了25.11%

在所有 MySQL 提交中击败了100.00%


 select  c.customer_id  
 from customer c
   group by customer_id   having  count(distinct product_key)  = ( select  count(    distinct(product_key)) from product);

 select  count(product_key) from product ;
SELECT customer_id,number FROM (SELECT customer_id, COUNT(*) AS 'number'
 FROM (SELECT customer_id, product_key FROM 
Customer GROUP BY customer_id, product_key) a
GROUP BY customer_id) b  WHERE number =

(SELECT COUNT(DISTINCT product_key) FROM Product);

SQL练习

MySQL 数据库函数教程

MySQL LAG() 函数

expression

offset

default_value

PARTITION BY 子句

ORDER BY 子句

1.牛客网

1.2 分数

1.2.1 题解 | #考试分数(一)

1.2.2 考试分数2

mysql 分组查询取各分组的前两名及子查询性能优化

1.2.3 考试分数3 重要

SQL75 考试分数(四) 分组分数中位数 较难

SQL76 考试分数(五)

字符串截取功能

SQL30 统计每种性别的人数

题解 | #计算25岁以上和以下的用户数量

问题分解：

日期函数

SQL把一个表中数据更新到另一个表的多种方法

插入数据

replace into 插入重复数据

时间差TIMESTAMPDIFF

明确考点：

创建一张新表

SQL 12 创建索引

解析：

索引的使用：

all 方法使用

举个例子

{*}SQL：方法一

`PARTITION BY` 子句

`ORDER BY` 子句

SQL75 考试分数(四) 分组分数中位数较难