【MySQL】自用笔记

---

1. CASE WHEN

case简单函数写法：

SELECT EId,
(
CASE ESex WHEN '男' THEN '0' 
		  WHEN '女' THEN '1' 
		  ELSE '空' END
) AS sex
FROM employee;

---

case搜索函数写法：

SELECT Sno,
(
CASE WHEN mark>90 THEN '优秀' 
     WHEN mark>80 THEN '良好' 
     ELSE '不合格' END
) AS grade
FROM exam;

THEN后边的值与ELSE后边的值类型应一致，否则会报错。

2. LEFT（）

LEFT()函数从提供的字符串的左侧提取给定数量的字符。

LEFT ( input_string , number_of_characters )

SELECT LEFT('MySQL',2) result;

3. GROUP BY

GROUP BY 语句用于结合合计函数，根据一个或多个列对结果集进行分组。

SELECT ALevel,SUM(AId) FROM associations
GROUP BY ALevel;

GROUP BY 一个以上的列

select date_id,make_name,
Count(distinct lead_id) unique_leads,
Count(distinct partner_id)unique_partners 
from DailySales 
Group by date_id,make_name

4. INNER JOIN（JOIN）

在表中存在至少一个匹配时，INNER JOIN 关键字返回行。

Student表：

SC表：

SELECT student.Sno,student.Sname,sc.Tno 
FROM student INNER JOIN sc ON student.sno=sc.sno

5. LEFT JOIN

从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行。

SELECT student.Sno,student.Sname,sc.Tno 
FROM student LEFT JOIN sc ON student.sno=sc.sno

6. RIGHT JOIN

关键字会右表 (table_name2) 那里返回所有的行，即使在左表 (table_name1) 中没有匹配的行。

SELECT student.Sno,student.Sname,sc.Tno 
FROM student RIGHT JOIN sc ON student.sno=sc.sno

7. UNION

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

SELECT student.Sno FROM student
UNION
SELECT sc.sno FROM sc;

8.CONCAT()

用于将两个字符串连接为一个字符串

SELECT CONCAT(Cday,Cspot) AS place FROM sc

9. UPPER()

字母大小写转换函数，将字母转成大写

SELECT UPPER(sname) FROM student

10. LOWER()

字母大小写转换函数，将字母转成小写

SELECT LOWER(sname) FROM student

11. GROUP_CONCAT()

将组中的字符串连接成为具有各种选项的单个字符串。

SELECT sno,GROUP_CONCAT(cday) FROM sc GROUP BY sno;

12. 行转列

用union / union all

select product_id,'store1' as store,store1 as price
from Products where store1 is not null
union
select product_id,'store2' as store,store2 as price
from Products where store2 is not null
union
select product_id,'store3' as store,store3 as price
from Products where store3 is not null

原表的值作为新表的值，则列改名store1 as price
新增的中间列（store）作为桥梁，则人为设值’store1’ as store

13. 列转行

SELECT 
  product_id,
  SUM(IF(store = 'store1', price, NULL)) 'store1',
  SUM(IF(store = 'store2', price, NULL)) 'store2',
  SUM(IF(store = 'store3', price, NULL)) 'store3' 
FROM
  Products1 
GROUP BY product_id ;

14. LIMIT

来限制SELECT语句返回的行数。
LIMIT 后跟一个数字表示要取的数据量

SELECT * FROM sc LIMIT 2

LIMIT 后跟两个数字，第一个数表示要跳过的条数，第二个数表示要取的数据量

SELECT * FROM sc LIMIT 1,1

与OFFSET 一起用

表示在开始返回行之前跳过偏移行

SELECT * FROM sc LIMIT 1 OFFSET 1

15. 日期相关函数

获取时间

#获取当前日期和时间时间
SELECT NOW(); #2023-02-25 16:13:30

#获取当前日期
SELECT CURDATE(); #2023-02-25

#获取当前时间
SELECT CURTIME(); #16:15:19

#获取单独的年、月、日、时、分、秒
SELECT EXTRACT(YEAR FROM NOW()); #2023
SELECT EXTRACT(MONTH FROM NOW()); #2
SELECT EXTRACT(DAY FROM NOW()); #25
SELECT EXTRACT(HOUR FROM NOW()); #16
SELECT EXTRACT(MINUTE FROM NOW()); #21
SELECT EXTRACT(SECOND FROM NOW()); #2

修改时间（增加/减少）

#日期增加一天(2023-02-25 16:24:16)
SELECT DATE_ADD(NOW(),INTERVAL 1 DAY); #2023-02-26 16:24:16

#时间减少两小时
SELECT DATE_SUB(NOW(),INTERVAL 2 HOUR); #2023-02-25 14:24:16

#日期加/减月份
SELECT PERIOD_ADD(202302,4); #202306
SELECT PERIOD_ADD(202302,-1); #202301

#计算月份差值
SELECT PERIOD_DIFF(202304,202303); #1

#计算日期差值
SELECT DATEDIFF(20230322,20230225); #25

#计算时间差值
SELECT TIMEDIFF('2023-02-25 17:30:32','2023-02-25 17:30:48'); #-00:00:16

日期格式化

#格式化日期
SELECT DATE_FORMAT(NOW(),'%Y/%m/%d %H:00:00'); #2023/02/25 17:00:00
SELECT DATE_FORMAT(NOW(),'%Y.%m.%d %H:%i:%s'); #2023.02.25 17:19:48

#字符串转成日期
SELECT STR_TO_DATE('2023-02-25 17:19:48','%Y-%m-%d %H:%i:%s') #2023-02-25 17:19:48

16. IFNULL()

它接受两个参数，如果不是NULL，则返回第一个参数。 否则，IFNULL函数返回第二个参数。

SELECT IFNULL(1,0); #1
SELECT IFNULL('',0); #''
SELECT IFNULL(NULL,0); #0

17. HAVING 和 WHERE 的区别

• HAVING 是在分组后对数据进行过滤

• WHERE 是在分组前对数据进行过滤

• HAVING 后面可以使用聚合函数

• WHERE 后面不可以使用聚合

在查询过程中执行顺序：from>where>group（含聚合函数）>having>order>select。

18. RANK()

是一个Window函数，它为结果集的分区中的每一行分配一个排名。
分区中具有相同值的行将获得相同的排名。 分区中第一行的等级是1。 RANK()函数将绑定行的数量添加到绑定等级以计算下一行的等级，因此，等级可能不是连续的。
RANK()函数对于求解前N个和后N个报表很有用。

语法：

RANK() OVER (
[PARTITION BY partition_expression, … ]
ORDER BY sort_expression [ASC | DESC], …
)

• 首先，PARTITION BY子句划分应用该函数的结果集分区的行。
• 其次，ORDER BY子句指定应用该函数每个分区中行的逻辑排序顺序。

表结构如下

• RANK() OVER(ORDER BY …)

  #如果有相同的值会生成相同的序号，并且接下来的序号是不连序的。
  SELECT class,grade,RANK() OVER(ORDER BY grade DESC) AS rk FROM rankTest;
  

  

• RANK() OVER(PARTITION BY … ORDER BY …)

  #如果有相同的值会生成相同的序号，并且接下来的序号是不连序的。
  #先分组 再组内排序
  SELECT class,grade,
  RANK() OVER(PARTITION BY class ORDER BY grade DESC) 
  AS rk 
  FROM rankTest;
  

19. ROW_NUMBER（）

从1开始，为每条分组记录返回一个数字.

• ROW_NUMBER（） OVER(ORDER BY …)

  #连续数字序号
  SELECT class,grade,
  ROW_NUMBER() OVER(ORDER BY grade DESC) 
  AS rk 
  FROM rankTest;
  

  

• ROW_NUMBER（） OVER(PARTITION BY …ORDER BY …)

  #先分组 再组内排序 组内连续数字序号
  SELECT class,grade,
  ROW_NUMBER() OVER(PARTITION BY class ORDER BY grade DESC) 
  AS rk 
  FROM rankTest;
  

  

20. DENSE_RANK（）

RANK（）类似，不同的是如果有相同的序号，那么接下来的序号不会间断。

• DENSE_RANK（） OVER(ORDER BY …)

  #如果有相同的值会生成相同的序号，并且接下来的序号是连序的。
  SELECT class,grade,
  DENSE_RANK() OVER(ORDER BY grade DESC) 
  AS rk 
  FROM rankTest;
  

  

• DENSE_RANK（） OVER(PARTITION BY …ORDER BY …)

  #如果有相同的值会生成相同的序号，并且接下来的序号是连序的。
  #先分组再生成连续的数字序号
  SELECT class,grade,
  DENSE_RANK() OVER(PARTITION BY class ORDER BY grade DESC) 
  AS rk 
  FROM rankTest;
  

  

21. NTILE ()

按照指定的数目将数据进行分组，并为每一组生成一个序号。
语法：

NTILE (integer_expression) OVER ( [ <partition_by_clause> ] <order_by_clause > )

SELECT class,grade,
NTILE(2) OVER(PARTITION BY class ORDER BY grade DESC) 
AS '班级小组' 
FROM rankTest;

22. ROUND ()

ROUND 函数用于把数值字段舍入为指定的小数位数。

#保留两位小数
SELECT ROUND(AVG(grade),2)avg_grade FROM rankTest;

23.char_length()和length()

char_length():
计算单位是字符，不管是汉字还是数字、字母都算是一个字符
length（）：
计算单位是字节，
uft8：一个汉字是三个字节
gbk：一个汉字是两个字节

24. 判断奇偶

MySQL 中判断奇数的 6 种方法：

mod(x, 2) = 1 ，如果余数是 1 就是奇数。
power(-1, x) = -1 ， 如果结果是 -1 就是奇数
x % 2 = 1 ，如果余数是 1 就是奇数。
x & 1 = 1 ，如果是 1 就是奇数
x regexp ‘[1, 3, 5, 7, 9]$’ = 1 如果为 1 就是奇数
x>>1<<1 != x 如果右移一位在左移一位不等于原值，就是奇数

25.正则表达式 REGEXP

REGEXP 就是 regular expression 正则表达式 的意思

^ 表示以后面的字符为开头
[ ] 表示括号内任意字符
- 表示连续
* 表示重复前面任意字符任意次数
\ 用来转义后面的特殊字符，以表示字符原本的样子，而不是将其作为特殊字符使用
$ 表示以前面的字符为结尾

前缀名以字母开头：^[a-zA-Z]
前缀名包含字母（大写或小写）、数字、下划线_、句点. 和 或 横杠-：[a-zA-Z0-9\_\.\-]*
以域名’@leetcode.com’结尾：@leetcode\.com$

26 SELECT INTO

INSERT INTO 语句用于向一张表中插入新的行。
SELECT INTO 语句从一张表中选取数据插入到另一张表中。常用于创建表的备份复件或者用于对记录进行存档。

GROUP_CONCAT()

MySQL GROUP_CONCAT() 函数将一个分组中指定的列或表达式的值连接成一个字符串并返回。

SELECT GROUP_CONCAT(expr), ...
FROM table_name
[WHERE ...]
[GROUP BY group_expr1, group_expr2, ...];

JSON_ARRAYAGG()

MySQL JSON_ARRAYAGG() 函数将指定的列或者表达式的值聚合为一个 JSON 数组。

SELECT JSON_ARRAYAGG(expr), ...
FROM table_name
[WHERE ...]
[GROUP BY group_expr1, group_expr2, ...];

JSON_OBJECTAGG()

JSON_OBJECTAGG() 函数将由第一个参数作为键和第二个参数作为值的键值对聚合为一个 JSON 对象。

SELECT JSON_OBJECTAGG(key_expr, value_expr), ...
FROM table_name
[WHERE ...]
[GROUP BY group_expr1, group_expr2, ...];

JSON_OBJECT()

JSON_OBJECT() 函数返回一个包含了由参数指定的所有键值对的 JSON 对象。

JSON_OBJECT(key, value[, key2, value2, ...])