【2024】MySQL中常用函数和窗口函数的基本使用方式

电玩女神 2023-10-14 21:09 27阅读 0赞

#### MySQL中常用函数和窗口函数的基本使用方式 ####

*  一、基础函数
 *   *   *  1、聚合函数：
         *  2、字符串函数：
         *  3、日期和时间函数
         *  4、数值函数
         *  5、条件函数
 *  二、窗口函数(\*OVER\*)

## 一、基础函数 ##

#### 1、聚合函数： ####

*  `SELECT COUNT(*) FROM table_name;`：计算表中的行数。
 *  `SELECT SUM(column_name) FROM table_name;`：计算表中指定列的总和。
 *  `SELECT AVG(column_name) FROM table_name;`：计算表中指定列的平均值。
 *  `SELECT MAX(column_name) FROM table_name;`：返回表中指定列的最大值。
 *  `SELECT MIN(column_name) FROM table_name;`：返回表中指定列的最小值。

#### 2、字符串函数： ####

*  `SELECT CONCAT(first_name, '-', last_name) AS full_name FROM table_name;`：将 `first_name` 和 `last_name` 字段连接为一个完整的名字。
 *  `SELECT SUBSTRING(column_name, start_position, length) FROM table_name;`：从指定位置开始提取指定长度的子字符串。
 *  `SELECT LENGTH(column_name) FROM table_name;`：返回字符串的长度。
 *  `SELECT LOWER(column_name) FROM table_name;`：将字符串转换为小写。
 *  `SELECT UPPER(column_name) FROM table_name;`：将字符串转换为大写。

#### 3、日期和时间函数 ####

*  `SELECT NOW();`：返回当前日期和时间。
 *  `SELECT DATE(column_name) FROM table_name;`：从日期时间值中提取日期部分。
 *  `SELECT TIME(column_name) FROM table_name;`：从日期时间值中提取时间部分。
 *  `SELECT YEAR(column_name) FROM table_name;`：从日期中提取年份。
 *  `SELECT MONTH(column_name) FROM table_name;`：从日期中提取月份。

#   获取当前日期，当前时间，截取日期，截取时分秒，截取年，截取月，截取日
    select curdate() ,now(),date(now()),time(now()) ,year(now()),month(now()),day(now())

![在这里插入图片描述][ce14a12d15b24cef8a849d24e40a76f5.png]

*  `DATE_FORMAT() :日期格式化`

具体使用：

select DATE_FORMAT(CURDATE(), '%Y%m')
    
    select DATE_FORMAT('2023-06-20 19:10:45', '%Y%m')

![在这里插入图片描述][e2721e4ef71c4ec0a4582d08ccf5d6de.png]

#### 4、数值函数 ####

*  `SELECT ABS(column_name) FROM table_name;`：返回列中的绝对值。
 *  `SELECT ROUND(column_name, decimal_places) FROM table_name;`：将数值四舍五入到指定的小数位数。
 *  `SELECT CEILING(column_name) FROM table_name;`：向上取整。
 *  `SELECT FLOOR(column_name) FROM table_name;`：向下取整。
 *  `SELECT MOD(column_name, divisor) FROM table_name;`：返回两个数相除的余数。

#### 5、条件函数 ####

*  `CASE`：根据条件返回不同的值。

# CASE 使用和java的 switch 类似
    select name,length(name),
    	case 
    		when length(name) <= 10 then '名字长度太短'   #when  条件  then 输出
    		when  length(name) >10 and length(name) <= 20 then '名字长度正常'
    		else '名字长度过长'
    	end 'name长度判定'   # 列名
    from user

![在这里插入图片描述][d5b0e2bcccb34ddb9fd2c663fe6a117a.png]

*  `COALESCE()`：返回第一个非空表达式的值。

# COALESCE 返回第一个非null的值，以此类推，如果都是null才返回null
    select coalesce(null,0),coalesce(100,0)

![在这里插入图片描述][e685919b736642579e66e2dd01b0aacb.png]

*  `NULLIF(A,B)`：如果两个表达式相等，则返回 NULL，否则返回第一个表达式的值。

# 如果A=B则返回null，不等于则返回第一个值
    select nullif('MySQL','MySQL') as  相等,
    nullif('MySQL ','sqlserver ') as 不相等  from user

![在这里插入图片描述][45d808939a1e4ec19e31466e6475c654.png]

*  `IF()` ：类似三木表达式，

# IF()  满足条件则返回值1，不满足则返回值2
    select if(1>0 , "张三","ZHANG"),if(1<0 , "张三","ZHANG")

![在这里插入图片描述][523c43a94d014878967078d734b2d0b5.png]

## 二、窗口函数(*OVER*) ##

**OVER的定义**

OVER用于为行定义一个窗口，它对一组值进行操作，不需要使用GROUP BY子句对数据进行分组，能够在同一行中同时返回基础行的列和聚合列。

**OVER的语法**

> **OVER ( \[ PARTITION BY column \] \[ ORDER BY culumn \] )**

**`PARTITION BY`** 子句进行分组；

**`ORDER BY`** 子句进行排序。

窗口函数OVER()指定一组行，开窗函数计算从窗口函数输出的结果集中各行的值。

开窗函数不需要使用GROUP BY就可以对数据进行分组，还可以同时返回基础行的列和聚合列。

**OVER的用法**

OVER开窗函数必须与聚合函数或排序函数一起使用，聚合函数一般指`SUM(),MAX(),MIN,COUNT(),AVG()` 等常见函数。排序函数一般指`RANK(),ROW_NUMBER(),DENSE_RANK(),NTILE()`等。

**OVER在聚合函数中使用的示例**

我们以SUM和COUNT函数作为示例来给大家演示。

-建立测试表和测试数据
    CREATE TABLE Employee
    (
    ID INT PRIMARY KEY,
    Name VARCHAR(20),
    GroupName VARCHAR(20),
    Salary INT
    )
    INSERT INTO Employee
    VALUES(1,'小明','开发部',8000), (4,'小张','开发部',7600), (5,'小白','开发部',7000), (8,'小王','财务部',5000), (9, null,'财务部',NULL), (15,'小刘','财务部',6000), (16,'小高','行政部',4500), (18,'小王','行政部',4000), (23,'小李','行政部',4500), (29,'小吴','行政部',4700);

**SUM后的开窗函数**

SELECT *,
         SUM(Salary) OVER(PARTITION BY Groupname) 每个组的总工资,
         SUM(Salary) OVER(PARTITION BY groupname ORDER BY ID) 每个组的累计总工资,
         SUM(Salary) OVER(ORDER BY ID) 累计工资,
         SUM(Salary) OVER() 总工资
    from Employee

结果如下：

![在这里插入图片描述][2d7261e0e8874b67875b18c068ad3a49.png]

其中开窗函数的每个含义不同，我们来具体解读一下：

`SUM(Salary) OVER (PARTITION BY Groupname)`

只对PARTITION BY后面的列Groupname进行分组，分组后求解Salary的和。

`SUM(Salary) OVER (PARTITION BY Groupname ORDER BY ID)`

对PARTITION BY后面的列Groupname进行分组，然后按ORDER BY 后的ID进行排序，然后在组内对Salary进行累加处理。

`SUM(Salary) OVER (ORDER BY ID)`

只对ORDER BY 后的ID内容进行排序，对排完序后的Salary进行累加处理。

`SUM(Salary) OVER ()`

对Salary进行汇总处理

**COUNT后的开窗函数**

SELECT *,
           COUNT(*) OVER(PARTITION BY Groupname ) 每个组的个数,
           COUNT(*) OVER(PARTITION BY Groupname ORDER BY ID) 每个组的累积个数,
           COUNT(*) OVER(ORDER BY ID) 累积个数 ,
           COUNT(*) OVER() 总个数
    from Employee

返回的结果如下图：

![在这里插入图片描述][3de7c7bb2dfa499d83070a98524edd7d.png]

后面的每个开窗函数就不再一一解读了，可以对照上面SUM后的开窗函数进行一一对照。

**OVER在排序函数中使用的示例**

我们对4个排序函数一一演示

-先建立测试表和测试数据
    WITH t AS
    (SELECT 1 StuID,'一班' ClassName,70 Score
    UNION ALL
    SELECT 2,'一班',85
    UNION ALL
    SELECT 3,'一班',85
    UNION ALL
    SELECT 4,'二班',80
    UNION ALL
    SELECT 5,'二班',74
    UNION ALL
    SELECT 6,'二班',80
    )
    SELECT * INTO Scores FROM t;
    SELECT * FROM Scores

[ce14a12d15b24cef8a849d24e40a76f5.png]: https://img-blog.csdnimg.cn/ce14a12d15b24cef8a849d24e40a76f5.png
[e2721e4ef71c4ec0a4582d08ccf5d6de.png]: https://img-blog.csdnimg.cn/e2721e4ef71c4ec0a4582d08ccf5d6de.png
[d5b0e2bcccb34ddb9fd2c663fe6a117a.png]: https://img-blog.csdnimg.cn/d5b0e2bcccb34ddb9fd2c663fe6a117a.png
[e685919b736642579e66e2dd01b0aacb.png]: https://img-blog.csdnimg.cn/e685919b736642579e66e2dd01b0aacb.png
[45d808939a1e4ec19e31466e6475c654.png]: https://img-blog.csdnimg.cn/45d808939a1e4ec19e31466e6475c654.png
[523c43a94d014878967078d734b2d0b5.png]: https://img-blog.csdnimg.cn/523c43a94d014878967078d734b2d0b5.png
[2d7261e0e8874b67875b18c068ad3a49.png]: https://img-blog.csdnimg.cn/2d7261e0e8874b67875b18c068ad3a49.png
[3de7c7bb2dfa499d83070a98524edd7d.png]: https://img-blog.csdnimg.cn/3de7c7bb2dfa499d83070a98524edd7d.png