MySQL- sql语句基础-Toy模板网

这篇具有很好参考价值的文章主要介绍了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

MySQL- sql语句基础,mysql,sql,数据库,java

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() 加条件

COUNT( state=‘approved’ OR NULL ) >需要在后面 + or null
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 --------------
题目：
MySQL- sql语句基础,mysql,sql,数据库,java

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

要求：写一条 SQL 查询语句获取即时订单在所有用户的首次订单中的比例。保留两位小数。
MySQL- sql语句基础,mysql,sql,数据库,java

 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组取不到）

MySQL- sql语句基础,mysql,sql,数据库,java

以上是符合条件的，但是会被筛选掉，导致错误
如果非要这么写，那么需要搞清楚要比较的是什么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子句<用于定义窗口大小> )

--------------------------- 案例 -----------------------------
公司的主管们感兴趣的是公司每个部门中谁赚的钱最多。一个部门的高收入者是指一个员工的工资在该部门的不同工资中排名前三。
MySQL- sql语句基础,mysql,sql,数据库,java

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

MySQL- sql语句基础,mysql,sql,数据库,java
-------------------------------------------------------------

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

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 的区别

MySQL- sql语句基础,mysql,sql,数据库,java

滑动窗口：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（）是求 当前行 + 当前行前面的所有行
MySQL- sql语句基础,mysql,sql,数据库,java
因为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.跟随;跟着;(指时间或顺序)在…后发生，因…而发生;在…后做文章来源地址https://www.toymoban.com/news/detail-658089.html

到了这里，关于MySQL- sql语句基础的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！