MySQL- sql语句基础

1.select后对表进行修改（delete）

报错：You can’t specify target table ‘Person’ for update in FROM clause
原因：mysql不能在同一语句中先select出同一表中的某些值，然后对这个表做修改
错误写法

delete from Person 
where id not in(
  select min(id) id
  from Person 
  group by email 
)

解决方法：使用子查询，添加临时表

正确写法

delete from Person 
where id not in(
  select * from (
  select min(id) id
  from Person 
  group by email 
  ) t1
)

子查询（内查询）
子查询，顾名思义就是存在于主查询内部的查询（所以错误写法里面不算是子查询），且子查询在主查询之前执行。

2.函数GROUP_CONCAT()

我们可以使用函数 GROUP_CONCAT() 将多行中的多个值组合成一个字符串。下面显示了 GROUP_CONCAT() 函数的语法：

GROUP_CONCAT(
    DISTINCT 字段 ----去重
    ORDER BY 字段 ----ASC/DESC
    SEPARATOR sep ---分隔符： SEPARATOR ','
);

用例

select 
  sell_date ,
  count(distinct product) num_sold ,
  group_concat(
    distinct product 
    separator ','
  ) products
from 
  Activities 
group by 
  sell_date 

3.使用正则表达式

思路
一般来说，如果你被要求匹配一个字符串，应该最先想到写一个正则表达式模式进行匹配。

正则表达式提供各种功能，以下是一些相关功能：

正则表达式书写格式
^：表示一个字符串或行的开头
[a-z]：表示一个字符范围，匹配从 a 到 z 的任何字符。
[0-9]：表示一个字符范围，匹配从 0 到 9 的任何字符。
[a-zA-Z]：这个变量匹配从 a 到 z 或 A 到 Z 的任何字符。请注意，你可以在方括号内指定的字符范围的数量没有限制，您可以添加想要匹配的其他字符或范围。
[^a-z]：这个变量匹配不在 a 到 z 范围内的任何字符。请注意，字符 ^ 用来否定字符范围，它在方括号内的含义与它的方括号外表示开始的含义不同。
[a-z]*：表示一个字符范围，匹配从 a 到 z 的任何字符 0 次或多次。
[a-z]+：表示一个字符范围，匹配从 a 到 z 的任何字符 1 次或多次。
.：匹配任意一个字符。
\.：表示句点字符。请注意，反斜杠用于转义句点字符，因为句点字符在正则表达式中具有特殊含义。还要注意，在许多语言中，你需要转义反斜杠本身，因此需要使用\.。
$：表示一个字符串或行的结尾。

例子

SELECT user_id, name, mail
FROM Users
-- 请注意，我们还转义了`@`字符，因为它在某些正则表达式中具有特殊意义
WHERE mail REGEXP '^[a-zA-Z][a-zA-Z0-9_.-]*\\@leetcode\\.com$';

4.DATE_FORMAT()

将日期值格式化为特定格式，请使用DATE_FORMAT函数。 DATE_FORMAT函数的语法如下：

DATE_FORMAT(date,format);  ---date 字段，format 需要格式
DATE_FORMAT(trans_date,'%Y-%m')  ---%Y-%m 2023-08

format	date
%D	英文后缀如：0th, 1st, 2nd等的一个月之中的第几天
%d	如果是1个数字(小于10)，那么一个月之中的第几天表示为加前导加0， 如：00, 01,02, …31
%H	24小时格式的小时，前导加0，例如：00,01…23
%h	小时，12小时格式，带前导零，例如：01，02 … 12
%Y	表示年份，四位数，例如2000，2001
%y	表示年份，两位数，例如00，01
%m	具有前导零的月份名称，例如：00,01,02，… 12
%M	月份全名称，例如：January, February,…December

format	date
%Y-%m-%d	2017/04/30
%Y-%m	2017/04

5.count() 加条件

1. COUNT( state=‘approved’ OR NULL ) >需要在后面 + or null

2. COUNT( IF (state = ‘approved’, 1, NULL ) )

需要注意的是，count只有在字段数据为NULL时才不计入数量，如果IF (state = ‘approved’, 1, NULL ) 改为IF (state = ‘approved’, 1, 0 ) ，那么非 approved的数据也会被计入其中导致错误。

6.关于GROUP BY 中使用聚合函数去跟普通数据做比较而导致查询结果错误的点

-------------- min(order_date) = customer_pref_delivery_date --------------
题目：

delivery_id 是表的主键。
该表保存着顾客的食物配送信息，顾客在某个日期下了订单，并指定了一个期望的配送日期（和下单日期相同或者在那之后）。
如果顾客期望的配送日期和下单日期相同，则该订单称为 「即时订单」，否则称为「计划订单」。
「首次订单」是顾客最早创建的订单。我们保证一个顾客只会有一个「首次订单」。

要求：写一条 SQL 查询语句获取即时订单在所有用户的首次订单中的比例。保留两位小数。

 select customer_pref_delivery_date
  from Delivery 
  group by customer_id
  having min(order_date) = customer_pref_delivery_date

这个sql执行结果是错误的，因为部分数据会被忽略掉，比如那些有多条数据的（一个customer_id可以有多条消费记录）， having min(order_date) = customer_pref_delivery_date 拿到最小的order_date后去跟这个组内的customer_pref_delivery_date去比较，但我们不能保证它拿的是不是min(order_date) 对应的那个customer_pref_delivery_date，所以这里的 “ = ”，即比较，是有问题的（这种情况下应该是取组内第一条数据的customer_pref_delivery_date去跟min(order_date) 做比较，因为98的可以取到，其他3组取不到）

以上是符合条件的，但是会被筛选掉，导致错误
如果非要这么写，那么需要搞清楚要比较的是什么customer_pref_delivery_date，比如这里要的是最小的customer_pref_delivery_date（初步判断，不建议这么写）

  select customer_pref_delivery_date
  from Delivery 
  group by customer_id
  having min(order_date) = min(customer_pref_delivery_date)

正确的完整写法

select round(count(order_date=customer_pref_delivery_date or null) *100 / count(*) ,2) immediate_percentage 
from Delivery 
where (customer_id,order_date) 
in (
  select customer_id,min(order_date) order_date
  from Delivery 
  group by customer_id 
)

思路是先查询拿到所有消费者的「首次订单」，再拿原表去筛选并计算order_date=customer_pref_delivery_date的记录数，最后求比率

6.窗口函数（可用于部门绩效排名，学校成绩排名等）

<窗口函数> over (partition by <用于分组的列名>
order by <用于排序的列名>
rows/range子句<用于定义窗口大小> )

--------------------------- 案例 -----------------------------
公司的主管们感兴趣的是公司每个部门中谁赚的钱最多。一个部门的 高收入者 是指一个员工的工资在该部门的 不同 工资中 排名前三 。

select t3.Department ,t3.Employee ,t3.salary
from (
  select t2.name Department ,t1.name Employee , 
  dense_rank() over(partition by t1.departmentId order by t1.salary desc) dr, 
  t1.salary 
  from Employee t1 
  left join Department t2 
  on t1.departmentId = t2.id 
) t3 
where t3.dr <=3

-------------------------------------------------------------

窗口函数>可以放以下两种函数：

1） 专用窗口函数，即rank, dense_rank, row_number等。

• Rank：存在并列排名，且占用原来名次，会跳数字。
• Dense_rank: 存在并列排名，不占用原来名次，不跳数
• Row_number：不存在并列排名

成绩	Rank	Dense_rank	Row_number
100	1	1	1
100	1	1	2
100	1	1	3
98	4	2	4

2） 聚合函数，如sum，avg，count，max，min等

group by 与 partition by 的区别

滑动窗口：rows&range用法

• ROWS: 表示按照行规定范围，根据order by子句排序后，取的前N行及后N行的数据计算。常用：rows n preceding表示从当前行到前n行（包括当前行）
• RANGE：表示按照值规定范围，根据order by子句排序后，指定当前行对应值的范围取值，行数不固定，只要行值在范围内，对应行都包含在内。适用于对日期、时间、数值排序分组

ROWS	解释
Current Row	当前行
n preceding	前 n 行，n 为数字， 比如 2 Preceding 表示前2行
unbounded preceding	当前行到分区中的开头行
n following	后n行，n 为数字， 比如 2 following 表示后2行
unbounded following	当前行到分区中的结尾行
rows between 1 preceding and 1 following	窗口范围是当前行、前一行、后一行，共三行记录

ROWS例子

select *,sum(weight) over(order by turn asc) TotalWeight
from Queue 

注意：这里sum（）是求 当前行 + 当前行前面的所有行

因为order by 后面不加 窗口范围 则默认查看 当前行 + 当前行前面的所有行，相当于rows unbounded preceding，如图所示

RANGE	解释
range interval 6 day preceding	最近7天的值（6天前+当天的值）
range between interval 1 day preceding and interval 1 day following	前后一天和当天的值

interval
/ˈɪntərvl/
n.间隔;(时间上的)间隙;间歇
preceding
/prɪˈsiːdɪŋ/
v.先于;在…之前发生(或出现);走在…前面
adj.在前的;前面的
following
/ˈfɑːloʊɪŋ/
adj.下列的;下述的;(时间上)接着的
n.下列;如下;下述;(统称)拥护者，追随者
v.跟随;跟着;(指时间或顺序)在…后发生，因…而发生;在…后做

CHAR_LENGTH

计算字符串中字符数的最佳函数是 CHAR_LENGTH(str)，它返回字符串 str 的长度。另一个常用的函数 LENGTH(str) 可能会返回不同的结果，因为该函数返回字符串 str 的字节数。