- 目录
MySQL函数1. 聚合函数 格式补充 示例将所有员工的名字合并成一行指定分隔符合并指定排序方式和分隔符
2. 数学函数(即用即查,重在融会贯通与运用)
3. 字符串函数(即用即查,重在融会贯通与运用)
4. 日期函数(即用即查,重在融会贯通与运用)
5. 控制流函数(即用即查,重在融会贯通与运用)if逻辑判断语句case when语句 (类似于C语言的if(condition)语句——case) 举例实操创建订单表方法1方法1
MySQL函数
- MySQL中,函数非常多,主要可以分为以下几类:
1. 聚合函数
- 聚合函数主要由:count,sum,min,max,avg,这些聚合函数我之前都写过,不再重叙。
- 这里我们学习另外一个函数:group_concat(),该函数用户实现行的合并
- group_concat()函数首先根据group by指定的列进行分组,并且用分隔符分隔,将同一个分组中的值连接起来,返回一个字符串结果。
格式
group_concat([distinct] 字段名 [order by 排序字段 asc/desc] [separator '分隔符'])
补充
- (1)使用distinct可以排除重复值;
- (2)如果需要对结果中的值进行排序,可以使用order by子句;
- (3)separator是一个字符串值,默认为逗号。
示例
create database mydb4; use mydb4; create table emp( emp_id int primary key auto_increment comment '编号', emp_name char(20) not null default '' comment '姓名', salary decimal(10,2) not null default 0 comment '工资', department char(20) not null default '' comment '部门' ); insert into emp(emp_name,salary,department) values('张晶晶',5000,'财务部'),('王飞飞',5800,'财务部'),('赵刚',6200,'财务部'),('刘小贝',5700,'人事部'),('王大鹏',6700,'人事部'),('张小斐',5200,'人事部'),('刘云云',7500,'销售部'),('刘云鹏',7200,'销售部'),('刘云鹏',7800,'销售部');
将所有员工的名字合并成一行
select group_concat(emp_name) from emp;
指定分隔符合并
select department,group_concat(emp_name separator ';' ) from emp group by department;
指定排序方式和分隔符
select department,group_concat(emp_name order by salary desc separator ';' ) from emp group by department;
2. 数学函数(即用即查,重在融会贯通与运用)
- 数学函数
- 函数名
- 实例
- 实例
- ABS(x)
- 返回 x 的绝对值
- 返回 -1 的绝对值:SELECT ABS(-1) -- 返回1
- CEIL(x)
- 返回大于或等于 x 的最 小整数
- SELECT CEIL(1.5) -- 返回2
- FLOOR(x)
- 返回小于或等于 x 的最 大整数
- 小于或等于 1.5 的整数:SELECT FLOOR(1.5) -- 返回1
- GREATEST(expr1, expr2, expr3, ...)
- 返回列表中的最大值
- 返回以下数字列表中的最大值:SELECT GREATEST(3, 12, 34, 8, 25); -- 34返回以下字 符串列表中的最大值:SELECT GREATEST("Google", "Runoob", "Apple"); -- Runoob
- LEAST(expr1, expr2, expr3, ...)
- 返回列表中的最小值
- 返回以下数字列表中的最小值:SELECT LEAST(3, 12, 34, 8, 25); -- 3返回以下字符串 列表中的最小值:SELECT LEAST("Google", "Runoob", "Apple"); -- Apple
- MAX(expression)
- 返回字段 expression 中的最大值
- 返回数据表 Products 中字段 Price 的最大 值:SELECT MAX(Price) AS LargestPrice FROM Products;
- MIN(expression)
- 返回字段 expression 中的最小值
- 返回数据表 Products 中字段 Price 的最小 值:SELECT MIN(Price) AS MinPrice FROM Products;
- MOD(x,y)
- 返回 x 除以 y 以后的余 数
- 5 除于 2 的余数:SELECT MOD(5,2) -- 1
- PI()
- 返回圆周率 (3.141593)
- SELECT PI() --3.141593
- POW(x,y)
- 返回 x 的 y 次方
- 2 的 3 次方:SELECT POW(2,3) -- 8
- RAND()
- 返回 0 到 1 的随机数
- SELECT RAND() --0.93099315644334
- ROUND(x)
- 返回离 x 最近的整数 (遵循四舍五入)
- SELECT ROUND(1.23456) --1
- ROUND(x,y)
- 返回指定位数的小数 (遵循四舍五入)
- SELECT ROUND(1.23456,3) –1.235
- TRUNCATE(x,y)
- 返回数值 x 保留到小数 点后 y 位的值(与 ROUND 最大的区别是 不会进行四舍五入
- SELECT TRUNCATE(1.23456,3) -- 1.234
3. 字符串函数(即用即查,重在融会贯通与运用)
- 字符串函数
- 函数
- 实例
- 实例
- CHAR_LENGTH(s)
- 返回字符串 s 的字符数
- 返回字符串 RUNOOB 的字符数SELECT CHAR_LENGTH("RUNOOB") AS LengthOfString;
- CHARACTER_LENGTH(s)
- 返回字符串 s 的字符数
- 返回字符串 RUNOOB 的字符数SELECT CHARACTER_LENGTH("RUNOOB") AS LengthOfString;
- CONCAT(s1,s2...sn)
- 字符串 s1,s2 等多个字 符串合并为一个字符串
- 合并多个字符串SELECT CONCAT("SQL ", "Runoob ", "Gooogle ", "Facebook") AS ConcatenatedString;
- CONCAT_WS(x, s1,s2...sn)
- 同 CONCAT(s1,s2,...) 函 数,但是每个字符串之 间要加上 x,x 可以是分 隔符
- 合并多个字符串,并添加分隔符: SELECT CONCAT_WS("-", "SQL", "Tutorial", "is", "fun!")AS ConcatenatedString;
- FIELD(s,s1,s2...)
- 返回第一个字符串 s 在 字符串列表(s1,s2...)中的 位置
- 返回字符串 c 在列表值中的位置: SELECT FIELD("c", "a", "b", "c", "d", "e");
- LTRIM(s)
- 去掉字符串 s 开始处的 空格
- 去掉字符串 RUNOOB开始处的空格: SELECT LTRIM(" RUNOOB") AS LeftTrimmedString;-- RUNOOB
- MID(s,n,len)
- 从字符串 s 的 n 位置截 取长度为 len 的子字符 串,同 SUBSTRING(s,n,len)
- 去掉字符串 s 开始处的 空格
- POSITION(s1 IN s)
- 从字符串 s 中获取 s1 的 开始位置
- 返回字符串 abc 中 b 的位置:SELECT POSITION('b' in 'abc') -- 2
- REPLACE(s,s1,s2)
- 将字符串 s2 替代字符串 s 中的字符串 s1
- 将字符串 abc 中的字符 a 替换为字符 x:SELECT REPLACE('abc','a','x') --xbc
- REVERSE(s)
- 将字符串s的顺序反过来
- 将字符串 abc 的顺序反过来:SELECT REVERSE('abc') -- cba
- RIGHT(s,n)
- 返回字符串 s 的后 n 个 字符
- 返回字符串 runoob 的后两个字符: SELECT RIGHT('runoob',2) -- ob
- RTRIM(s)
- 去掉字符串 s 结尾处的 空格
- 去掉字符串 RUNOOB 的末尾空格: SELECT RTRIM("RUNOOB ") AS RightTrimmedString; -- RUNOOB
- STRCMP(s1,s2)
- 比较字符串 s1 和 s2, 如果 s1 与 s2 相等返回 0 ,如果 s1>s2 返回 1,如果 s1返回 -1
- 比较字符串:SELECT STRCMP("runoob", "runoob"); -- 0
- SUBSTR(s, start, length)
- 从字符串 s 的 start 位置 截取长度为 length 的子 字符串
- 从字符串 RUNOOB 中的第 2 个位置截 取 3个 字符:SELECT SUBSTR("RUNOOB", 2, 3) AS ExtractString; -- UNO
- TRIM(s)
- 去掉字符串 s 开始和结 尾处的空格
- 去掉字符串 RUNOOB 的首尾空格: SELECT TRIM(' RUNOOB ') AS TrimmedString;
- UCASE(s)
- 将字符串转换为大写
- 将字符串 runoob 转换为大写:SELECT UCASE("runoob"); -- RUNOOB
- UPPER(s)
- 将字符串转换为大写
- 将字符串 runoob 转换为大写:SELECT UPPER("runoob"); -- RUNOOB
- LCASE(s)
- 将字符串 s 的所有字母 变成小写字母
- 字符串 RUNOOB 转换为小写:SELECT LCASE('RUNOOB') -- runoob
- LOWER(s)
- 将字符串 s 的所有字母 变成小写字母
- 字符串 RUNOOB 转换为小写:SELECT LOWER('RUNOOB') -- runoob
4. 日期函数(即用即查,重在融会贯通与运用)
- 日期函数——用法+举例
- 函数名
- 描述
- 举例
- UNIX_TIMESTAMP()
- 返回从1970-01-01 00:00:00到当前毫秒值
- select UNIX_TIMESTAMP() -> 1632729059
- UNIX_TIMESTAMP(DATE_STRING)
- 将制定日期转为毫秒值时间戳
- SELECT FROM_UNIXTIME(1598079966,'%Y-%m-%d %H:%i:%s'); (1598079966,'%Y-%m-%d %H:%i:%s'); -> 2020-08-22 15-06-06
- FROM_UNIXTIME(BIGINT UNIXTIME[, STRING FORMAT])
- 将毫秒值时间戳转为指定格式日期
- SELECT CURDATE();-> 2024-06-19
- CURDATE()
- 返回当前日期
- SELECT CURRENT_DATE();-> 2022-02-19
- CURRENT_DATE()
- 返回当前日期
- SELECT CURRENT_TIME();-> 19:33:03
- CURRENT_TIME
- 返回当前时间
- SELECT CURTIME();-> 19:59:02
- CURTIME()
- 返回当前时间
- SELECT CURRENT_TIMESTAMP()-> 2013-09-19 22:57:42
- CURRENT_TIMESTAMP()
- 返回当前日期和时间
- SELECT DATE("2017-06-15"); -> 2017-06-15
- CURRENT_TIMESTAMP()
- 从日期或日期时间表达式中提取日期值
- SELECT DATEDIFF('2001-01-01','2001-02-02')-> -32
- DATEDIFF(d1,d2)
- 计算日期 d1->d2 之间相隔的天数
- SELECT TIMEDIFF("13:10:11", "13:10:10");-> 00:00:01
- TIMEDIFF(time1, time2)
- 计算时间差值
- SELECT DATE_FORMAT('2011-11-11 11:11:11','%Y-%m-%d %r')-> 2011-11-11 11:11:11 AM
- DATE_FORMAT(d,f)
- 按表达式 f的要求显示日期 d
- SELECT STR_TO_DATE("August 10 2017", "%M %d %Y");-> 2017- 08-10
- STR_TO_DATE(string, format_mask)
- 将字符串转变为日期
- Orders 表中 OrderDate 字段减去 2 天:SELECT OrderId,DATE_SUB(OrderDate,INTERVAL 2 DAY) AS OrderPayDateFROM Orders
- DATE_SUB(date,INTERVAL expr type)
- 函数从日期减去指定的时间间隔。
- Orders 表中 OrderDate 字段减去 2 天:SELECT OrderId,DATE_SUB(OrderDate,INTERVAL 2 DAY) AS OrderPayDateFROM Orders
- ADDDATE/DATE_ADD(d, INTERVAL expr type)
- 计算起始日期 d 加上一个时间段后的日期,type 值可以是:MICROSECONDSECONDMINUTEHOURDAYWEEKMON
- THQUARTER
- YEARDAY
- _MINUTEDAY_HOURYEAR_MONTH
- SELECT DATE_ADD("2017-06-15", INTERVAL 10 DAY); -> 2017-06- 25 SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL 15 MINUTE);-> 2017-06-15 09:49:21 SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL -3 HOUR);->2017-06-15 06:34:21 SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL -3 HOUR);->2017- 04-15
- DATE_ADD(d,INTERVAL expr type)
- 计算起始日期 d 加上一个时间段后的日期,type 值可以是: SECOND_MICROSECONDMINUTE
- _MICROSECONDMINUTE
- _SECONDHOUR_
- MICROSECONDHOUR_SECONDHOUR
- _MINUTEDAY_
- MICROSECONDDAY_SECONDDAY
- _MINUTEDAY_HOURYEAR
- _MONTH
- SELECT EXTRACT(MINUTE FROM '201-11-11 11:11:11') -> 11
- EXTRACT(type FROM d)
- 从日期 d 中获取指定的值,**type** 指定返回的值。
- type可取值为:
- MICROSECONDSECONDMINUTEHOUR…..**
- SELECT LAST_DAY("2017-06-20");-> 2017-06-30
- EXTRACT(type FROM d)
- 返回给给定日期的那一月份的最后一天
- SELECT MAKEDATE(2016, 3);-> 2016-01-03
- EXTRACT(type FROM d)
- 基于给定参数年份 year 和所在年中的天数序号
- day-of-year 返回一个日期
- SELECT YEAR("2015-7-8");-> 2015
- EXTRACT(type FROM d)
- 返回年份
- SELECT MONTH('2071-7-15 11:11:11')->11
- MONTH(d)
- 返回日期d中的月份值,1 到 12
- SELECT DAY("2015-08-15"); -> 15
- DAY(d)
- 返回日期值 d 的日期部分
- SELECT HOUR('1:2:3')-> 1
- HOUR(t)
- 返回 t 中的小时值
- SELECT MINUTE('1:2:3')-> 2
- MINUTE(t)
- 返回 t 中的分钟值
- SELECT SECOND('1:2:3')-> 3
- SECOND(t)
- 返回 t 中的秒钟值
- SELECT QUARTER('2021-7-18 13:13:14')-> 4
- QUARTER(d)
- 返回日期d是第几季节,返回 1 到 4
- SELECT MONTHNAME('2011-11-11 11:11:11')-> November
- QUARTER(d)
- 返回日期当中的月份名称,如 November
- SELECT MONTH('2022-1-12 2:21:31')->11
- MONTH(d)
- 返回日期d中的月份值,1 到 12
- SELECT DAYNAME('2012-11-11 11:11:11')->Friday
- DAYNAME(d)
- 返回日期 d 是星期几,如 Monday,Tuesday
- SELECT DAYOFMONTH('2001-3-5 5:6:17')->11
- DAYOFMONTH(d)
- 计算日期 d 是本月的第几天
- SELECT DAYOFWEEK('2013-1-12 13:21:11')->6
- DAYOFWEEK(d)
- 日期 d 今天是星期几,1 星期日,2 星期一,以此类推
- SELECT DAYOFYEAR('2013-4-1 1:12:51')->315
- DAYOFYEAR(d)
- 计算日期 d 是本年的第几天
- SELECT DAYOFYEAR('2011-11-11 11:11:11')->315
- WEEK(d)
- 计算日期 d 是本年的第几个星期,范围是 0 到 53
- SELECT WEEK('2011-11-11 11:11:11')-> 45
- WEEKDAY(d)
- 日期 d 是星期几,0 表示星期一,1 表示星期二
- SELECT WEEKDAY("2017-06-15");-> 3
5. 控制流函数(即用即查,重在融会贯通与运用)
if逻辑判断语句
- if逻辑判断语句
- 格式
- 解释
- 举例
- IF(expr,v1,v2)
- 如果表达式 expr 成立,返回结果 v1;否则,返回结果 v2。
- SELECT IF(1 > 0,'正确','错误') - >正确
- IFNULL(v1,v2)
- 如果 v1 的值不为 NULL,则返回 v1,否则返回 v2
- SELECT IFNULL(null,'Hello Word')->Hello Word
- ISNULL(expression)
- 判断表达式是否为 NULL
- SELECT ISNULL(NULL);->1
- NULLIF(expr1, expr2)
- 比较两个字符串,如果字符串 expr1 与 expr2 相等 返回 NULL,否则返 回 expr1
- SELECT NULLIF(25, 25);->
case when语句 (类似于C语言的if(condition)语句——case)
- case when语句
- 格式
- 解释
- 操作
- CASE expression WHEN condition1 THEN result1 WHEN condition2 THEN result2 ... WHEN conditionN THEN resultN ELSE result END
- CASE 表示函数开始,END 表示 函数结束。如果 condition1 成 立,则返回 result1, 如果 condition2 成立,则返回 result2,当全部不成立则返回 result,而当有一个成立之后,后 面的就不执行了。
- select case 100 when 50 then 'tom' when 100 then 'mary'else 'tim' end ; select case when 1=2 then 'tom' when 2=2 then 'mary' else'tim' end ;
举例实操
use mydb4
创建订单表
create table orders( oid int primary key, -- 订单id price double, -- 订单价格 payType int -- 支付类型(1:微信支付 2:支付宝支付 3:银行卡支付 4:其他) ); insert into orders values(1,1200,1); insert into orders values(2,1000,2); insert into orders values(3,200,3); insert into orders values(4,3000,1); insert into orders values(5,1500,2);
方法1
select * , case when payType=1 then '微信支付' when payType=2 then '支付宝支付' when payType=3 then '银行卡支付' else '其他支付方式' end as payTypeStr from orders;
方法2
select * , case payType when 1 then '微信支付' when 2 then '支付宝支付' when 3 then '银行卡支付' else '其他支付方式' end as payTypeStr from orders;
........