目录
3.concat合并字符串,返回连接后的字符串,注意引号/逗号的位置产生的效果不同,合并显示和分段显示
4.insert(需要被替换的字符串 ,从哪个字符开始替换 ,需要替换几个字符,新替换的字符 )
6.left(str,length)/rigth(str,length)从左/右侧提取字符串,返回字符串左/右边len个字符
8.replace(目标字符串,需要被替换的字符,新替换的字符),返回替换后的新字符串
9.substring(目标字符串,从哪个字符开始截取,截取长度),返回截取出来的字符(串)
11.strcmp(表达式1,表达式2),表达式1小于表达式2返回-1,否则返回1,相等返回0,也可以用于正整数比较
12.locate(需要查找的字符,目标字符串,从哪个位置开始查找字符)/instr(目标字符串,需要查找的字符串),返回字符第一次出现的位置
1.curdate()/current_date()/current_date,返回当前系统的日期
2.curtime()/current_time()/current_time,返回当前系统的时间
3.now()/sysdate(),返回当前系统的日期和时间
4.date(时间)/time(时间),获取当前日期的日期/时间部分
5.month,monthname,day,daymonth,dayname,year,dayofweek,week,dayofyear
6.datediff(日期1,日期2),返回两个日期之间的相差天数
7.sec_to_time()/time_to_sec(),将秒转换为时间/将时间转换为秒数,转换为秒数时默认是从当天00:00:00开始计算
1.if(表达式,结果1,结果2),表达式为真时,返回结果1,否则返回结果2,为null返回结果2
2.ifnull(结果1,结果2),如果结果1为空返回结果2,不为空返回结果1
3.case 判断的值 when 表达式1 then 执行的内容1 when 表达式2 then 执行的内容2 else 其他情况需要执行的内容3 end;
一.聚合函数
1.count统计行数
count(*|字段名) from 表;指定了字段名时不会统计null字段
mysql8.0 [SLB]>select * from t3;
+----+------+----------+
| id | day | permoney |
+----+------+----------+
| 0 | NULL | NULL |
| 1 | 18 | 300 |
| 2 | 19 | 300 |
| 3 | 25 | 450 |
| 4 | 29 | 500 |
+----+------+----------+
5 rows in set (0.00 sec)
mysql8.0 [SLB]>select count(id) from t3;
+-----------+
| count(id) |
+-----------+
| 5 |
+-----------+
1 row in set (0.01 sec)
mysql8.0 [SLB]>select count(day) from t3;
+------------+
| count(day) |
+------------+
| 4 |
+------------+
1 row in set (0.00 sec)
2.avg平均值
avg(字段名) from 表;忽略null字段
mysql8.0 [SLB]>select AVG(day) from t3;
+----------+
| AVG(day) |
+----------+
| 22.7500 |
+----------+
1 row in set (0.00 sec)
mysql8.0 [SLB]>select AVG(permoney) from t3;
+---------------+
| AVG(permoney) |
+---------------+
| 387.5 |
+---------------+
1 row in set (0.01 sec)
3.sum求和
sum(字段名) from 表;
mysql8.0 [SLB]>select sum(day) from t3;
+----------+
| sum(day) |
+----------+
| 91 |
+----------+
1 row in set (0.00 sec)
mysql8.0 [SLB]>select sum(permoney) from t3;
+---------------+
| sum(permoney) |
+---------------+
| 1550 |
+---------------+
1 row in set (0.00 sec)
4.max最大值和min最小值
max|min(字段名);忽略null字段
mysql8.0 [SLB]>select max(permoney) from t3;
+---------------+
| max(permoney) |
+---------------+
| 500 |
+---------------+
1 row in set (0.00 sec)
mysql8.0 [SLB]>select min(permoney) from t3;
+---------------+
| min(permoney) |
+---------------+
| 300 |
+---------------+
1 row in set (0.00 sec)
5.聚合函数配合group by进行分组
select 字段名,聚合函数 from 表 group 不要 字段名;先分组再聚合
mysql8.0 [SLB]>select * from t3;
+----+------+----------+--------+
| id | day | permoney | deptid |
+----+------+----------+--------+
| 0 | 15 | 0 | 0 |
| 1 | 18 | 300 | 1 |
| 2 | 19 | 300 | 2 |
| 3 | 25 | 450 | 2 |
| 4 | 29 | 500 | 3 |
+----+------+----------+--------+
5 rows in set (0.00 sec)
mysql8.0 [SLB]>select deptid,sum(permoney) from t3 group by deptid;
+--------+---------------+
| deptid | sum(permoney) |
+--------+---------------+
| 0 | 0 |
| 1 | 300 |
| 2 | 750 |
| 3 | 500 |
+--------+---------------+
4 rows in set (0.00 sec)
mysql8.0 [SLB]>select deptid,avg(permoney) from t3 group by deptid;
+--------+---------------+
| deptid | avg(permoney) |
+--------+---------------+
| 0 | 0 |
| 1 | 300 |
| 2 | 375 |
| 3 | 500 |
+--------+---------------+
4 rows in set (0.00 sec)
6.having对分组后的结果再处理
类似于where,但where不能使用聚合函数
mysql8.0 [SLB]>select deptid,day from t3 group by deptid,day having avg(permoney)>300;
+--------+------+
| deptid | day |
+--------+------+
| 2 | 25 |
| 3 | 29 |
+--------+------+
2 rows in set (0.00 sec)
7.order by 进行排序
asc升序,desc降序
mysql8.0 [SLB]>select deptid,avg(permoney) from t3 group by deptid order by avg(permoney) desc;
+--------+---------------+
| deptid | avg(permoney) |
+--------+---------------+
| 3 | 500 |
| 2 | 375 |
| 1 | 300 |
| 0 | 0 |
+--------+---------------+
4 rows in set (0.00 sec)
8.group_concat多行数据合并
mysql8.0 [SLB]>select deptid,group_concat(permoney) from t3 group by deptid;
+--------+------------------------+
| deptid | group_concat(permoney) |
+--------+------------------------+
| 0 | 0 |
| 1 | 300 |
| 2 | 300,450 |
| 3 | 500 |
+--------+------------------------+
4 rows in set (0.00 sec)
9.limit限制结果的显示起始记录和数量
显示三行记录,默认从第一行开始
mysql8.0 [SLB]>select * from t3 limit 3;
+----+------+----------+--------+
| id | day | permoney | deptid |
+----+------+----------+--------+
| 0 | 15 | 0 | 0 |
| 1 | 18 | 300 | 1 |
| 2 | 19 | 300 | 2 |
+----+------+----------+--------+
3 rows in set (0.00 sec)
显示第一行及其后共三行记录
mysql8.0 [SLB]>select * from t3 limit 1,3;
+----+------+----------+--------+
| id | day | permoney | deptid |
+----+------+----------+--------+
| 1 | 18 | 300 | 1 |
| 2 | 19 | 300 | 2 |
| 3 | 25 | 450 | 2 |
+----+------+----------+--------+
3 rows in set (0.00 sec)
二.数值函数
1.abs求绝对值
mysql8.0 [SLB]>select * from t3;
+----+------+----------+--------+
| id | day | permoney | deptid |
+----+------+----------+--------+
| 0 | 15 | 0 | 0 |
| 1 | 18 | 300 | 1 |
| 2 | 19 | 300 | 2 |
| 3 | 25 | 450 | 2 |
| 4 | 29 | 500 | 3 |
+----+------+----------+--------+
5 rows in set (0.00 sec)
mysql8.0 [SLB]>select abs(deptid) from t3;
+-------------+
| abs(deptid) |
+-------------+
| 0 |
| 1 |
| 2 |
| 2 |
| 3 |
+-------------+
5 rows in set (0.00 sec)
2.sqrt求平方根
mysql8.0 [SLB]>select sqrt(permoney) from t3;
+--------------------+
| sqrt(permoney) |
+--------------------+
| 0 |
| 17.320508075688775 |
| 17.320508075688775 |
| 21.213203435596427 |
| 22.360679774997898 |
+--------------------+
5 rows in set (0.00 sec)
3.pow,power求值的幂次方
mysql8.0 [SLB]>select pow(permoney,2) from t3;
+-----------------+
| pow(permoney,2) |
+-----------------+
| 0 |
| 90000 |
| 90000 |
| 202500 |
| 250000 |
+-----------------+
5 rows in set (0.00 sec)
4.mod求余
mysql8.0 [SLB]>select mod(deptid,3) from t3;
+---------------+
| mod(deptid,3) |
+---------------+
| 0 |
| 1 |
| 2 |
| 2 |
| 0 |
+---------------+
5 rows in set (0.00 sec)
5.ceil,ceiling向上取整
mysql8.0 [SLB]>select ceil(4.1);
+-----------+
| ceil(4.1) |
+-----------+
| 5 |
+-----------+
1 row in set (0.00 sec)
mysql8.0 [SLB]>select ceil(4.9);
+-----------+
| ceil(4.9) |
+-----------+
| 5 |
+-----------+
1 row in set (0.00 sec)
mysql8.0 [SLB]>select ceil(3.9);
+-----------+
| ceil(3.9) |
+-----------+
| 4 |
+-----------+
1 row in set (0.00 sec)
6.floor向下取整
mysql8.0 [SLB]>select floor(3.9);
+------------+
| floor(3.9) |
+------------+
| 3 |
+------------+
1 row in set (0.00 sec)
mysql8.0 [SLB]>select floor(3.1);
+------------+
| floor(3.1) |
+------------+
| 3 |
+------------+
1 row in set (0.00 sec)
7.rand生成随机数
每一次生成都不一样,传入一个整数时产生相同随机数,但不对多行生成生效
+---------------------+
| rand() |
+---------------------+
| 0.22675975433249004 |
+---------------------+
1 row in set (0.00 sec)
mysql8.0 [SLB]>select rand();
+--------------------+
| rand() |
+--------------------+
| 0.4011339632804822 |
+--------------------+
1 row in set (0.00 sec)
mysql8.0 [SLB]>select rand(2);
+--------------------+
| rand(2) |
+--------------------+
| 0.6555866465490187 |
+--------------------+
1 row in set (0.00 sec)
mysql8.0 [SLB]>select rand(2) from t3;
+---------------------+
| rand(2) |
+---------------------+
| 0.6555866465490187 |
| 0.12234661925802624 |
| 0.64497318737672 |
| 0.8578261098431667 |
| 0.354211017819318 |
+---------------------+
5 rows in set (0.00 sec)
8.round四舍五入计算
mysql8.0 [SLB]>select round(4.1);
+------------+
| round(4.1) |
+------------+
| 4 |
+------------+
1 row in set (0.00 sec)
mysql8.0 [SLB]>select round(4.5);
+------------+
| round(4.5) |
+------------+
| 5 |
+------------+
1 row in set (0.00 sec)
9.sign返回参数的符号
正数为1,负数为-1,0为0
mysql8.0 [SLB]>select sign(4);
+---------+
| sign(4) |
+---------+
| 1 |
+---------+
1 row in set (0.00 sec)
mysql8.0 [SLB]>select sign(-4);
+----------+
| sign(-4) |
+----------+
| -1 |
+----------+
1 row in set (0.00 sec)
mysql8.0 [SLB]>select sign(0);
+---------+
| sign(0) |
+---------+
| 0 |
+---------+
1 row in set (0.00 sec)
三.字符串函数
1.length计算字符串长度,返回字节数
mysql8.0 [SLB]>select length("cdvdr");
+-----------------+
| length("cdvdr") |
+-----------------+
| 5 |
+-----------------+
1 row in set (0.00 sec)
mysql8.0 [SLB]>select length("");
+------------+
| length("") |
+------------+
| 0 |
+------------+
1 row in set (0.00 sec)
mysql8.0 [SLB]>select length(" ");
+-------------+
| length(" ") |
+-------------+
| 1 |
+-------------+
1 row in set (0.00 sec)
2.char_length计算字符串长度,返回字符长度
mysql8.0 [SLB]>select length("nci是");
+------------------+
| length("nci是") |
+------------------+
| 6 |
+------------------+
1 row in set (0.00 sec)
mysql8.0 [SLB]>select char_length("nci是");
+-----------------------+
| char_length("nci是") |
+-----------------------+
| 4 |
+-----------------------+
1 row in set (0.01 sec)
3.concat合并字符串,返回连接后的字符串,注意引号/逗号的位置产生的效果不同,合并显示和分段显示
mysql8.0 [SLB]>select concat("nci是,nsicni,koko");
+------------------------------+
| concat("nci是,nsicni,koko") |
+------------------------------+
| nci是,nsicni,koko |
+------------------------------+
1 row in set (0.00 sec)
mysql8.0 [SLB]>select concat("nci是","nsicni","koko");
+----------------------------------+
| concat("nci是","nsicni","koko") |
+----------------------------------+
| nci是nsicnikoko |
+----------------------------------+
1 row in set (0.00 sec)
4.insert(需要被替换的字符串 ,从哪个字符开始替换 ,需要替换几个字符,新替换的字符 )
mysql8.0 [SLB]>select insert("fasfasklkl",3,3,666);
+------------------------------+
| insert("fasfasklkl",3,3,666) |
+------------------------------+
| fa666sklkl |
+------------------------------+
1 row in set (0.00 sec)
5.lower/upper将字符串中的字母替换为小写/大写
mysql8.0 [SLB]>select lower("EFRScdsni");
+--------------------+
| lower("EFRScdsni") |
+--------------------+
| efrscdsni |
+--------------------+
1 row in set (0.00 sec)
mysql8.0 [SLB]>select upper("EFRScdsni");
+--------------------+
| upper("EFRScdsni") |
+--------------------+
| EFRSCDSNI |
+--------------------+
1 row in set (0.00 sec)
6.left(str,length)/rigth(str,length)从左/右侧提取字符串,返回字符串左/右边len个字符
mysql8.0 [SLB]>select left("EFRScdsni",2);
+---------------------+
| left("EFRScdsni",2) |
+---------------------+
| EF |
+---------------------+
1 row in set (0.00 sec)
mysql8.0 [SLB]>select right("EFRScdsni",2);
+----------------------+
| right("EFRScdsni",2) |
+----------------------+
| ni |
+----------------------+
1 row in set (0.00 sec)
7.trim删除字符串左右两侧的空格
mysql8.0 [SLB]>select trim(" EFRSvev "); #删除两侧空格
+---------------------+
| trim(" EFRSvev ") |
+---------------------+
| EFRSvev |
+---------------------+
1 row in set (0.00 sec)
mysql8.0 [SLB]>select (" EFRSvev "); #不删除两侧空格对比
+-------------+
| EFRSvev |
+-------------+
| EFRSvev |
+-------------+
1 row in set (0.00 sec)
8.replace(目标字符串,需要被替换的字符,新替换的字符),返回替换后的新字符串
mysql8.0 [SLB]>select replace("afwevghff","f","h");
+------------------------------+
| replace("afwevghff","f","h") |
+------------------------------+
| ahwevghhh |
+------------------------------+
1 row in set (0.00 sec)
9.substring(目标字符串,从哪个字符开始截取,截取长度),返回截取出来的字符(串)
mysql8.0 [SLB]>select substring("afwevghff",3,5);
+----------------------------+
| substring("afwevghff",3,5) |
+----------------------------+
| wevgh |
+----------------------------+
1 row in set (0.01 sec)
10.reverse字符反转,倒序
mysql8.0 [SLB]>select reverse("afwevghf");
+---------------------+
| reverse("afwevghf") |
+---------------------+
| fhgvewfa |
+---------------------+
1 row in set (0.00 sec)
11.strcmp(表达式1,表达式2),表达式1小于表达式2返回-1,否则返回1,相等返回0,也可以用于正整数比较
mysql8.0 [SLB]>select strcmp("abc","abc");
+---------------------+
| strcmp("abc","abc") |
+---------------------+
| 0 |
+---------------------+
1 row in set (0.00 sec)
mysql8.0 [SLB]>select strcmp("abc","Abc");
+---------------------+
| strcmp("abc","Abc") |
+---------------------+
| 0 |
+---------------------+
1 row in set (0.00 sec)
mysql8.0 [SLB]>select strcmp("abc","abcd");
+----------------------+
| strcmp("abc","abcd") |
+----------------------+
| -1 |
+----------------------+
1 row in set (0.00 sec)
mysql8.0 [SLB]>select strcmp("abcde","abcd");
+------------------------+
| strcmp("abcde","abcd") |
+------------------------+
| 1 |
+------------------------+
1 row in set (0.00 sec)
mysql8.0 [SLB]>select strcmp("abcde","dfgh");
+------------------------+
| strcmp("abcde","dfgh") |
+------------------------+
| -1 |
+------------------------+
1 row in set (0.00 sec)
mysql8.0 [SLB]>select strcmp(1,2);
+-------------+
| strcmp(1,2) |
+-------------+
| -1 |
+-------------+
1 row in set (0.00 sec)
mysql8.0 [SLB]>select strcmp(3,2);
+-------------+
| strcmp(3,2) |
+-------------+
| 1 |
+-------------+
1 row in set (0.00 sec)
mysql8.0 [SLB]>select strcmp(2,2);
+-------------+
| strcmp(2,2) |
+-------------+
| 0 |
+-------------+
1 row in set (0.00 sec)
12.locate(需要查找的字符,目标字符串,从哪个位置开始查找字符)/instr(目标字符串,需要查找的字符串),返回字符第一次出现的位置
mysql8.0 [SLB]>select locate("w","cbwbuw",2);
+------------------------+
| locate("w","cbwbuw",2) |
+------------------------+
| 3 |
+------------------------+
1 row in set (0.00 sec)
mysql8.0 [SLB]>select locate("w","cbwbuw",4);
+------------------------+
| locate("w","cbwbuw",4) |
+------------------------+
| 6 |
+------------------------+
1 row in set (0.00 sec)
mysql8.0 [SLB]>select instr("cbwbuw","w");
+---------------------+
| instr("cbwbuw","w") |
+---------------------+
| 3 |
+---------------------+
1 row in set (0.00 sec)
四.日期和时间函数
1.curdate()/current_date()/current_date,返回当前系统的日期
mysql8.0 [SLB]>select curdate();
+------------+
| curdate() |
+------------+
| 2023-06-05 |
+------------+
1 row in set (0.00 sec)
mysql8.0 [SLB]>select current_date();
+----------------+
| current_date() |
+----------------+
| 2023-06-05 |
+----------------+
1 row in set (0.00 sec)
mysql8.0 [SLB]>select current_date;
+--------------+
| current_date |
+--------------+
| 2023-06-05 |
+--------------+
1 row in set (0.00 sec)
2.curtime()/current_time()/current_time,返回当前系统的时间
mysql8.0 [SLB]>select curtime();
+-----------+
| curtime() |
+-----------+
| 13:33:29 |
+-----------+
1 row in set (0.00 sec)
mysql8.0 [SLB]>select current_time();
+----------------+
| current_time() |
+----------------+
| 13:33:50 |
+----------------+
1 row in set (0.00 sec)
mysql8.0 [SLB]>select current_time;
+--------------+
| current_time |
+--------------+
| 13:33:56 |
+--------------+
1 row in set (0.01 sec)
3.now()/sysdate(),返回当前系统的日期和时间
mysql8.0 [SLB]>select now();
+---------------------+
| now() |
+---------------------+
| 2023-06-05 13:34:59 |
+---------------------+
1 row in set (0.00 sec)
mysql8.0 [SLB]>select sysdate();
+---------------------+
| sysdate() |
+---------------------+
| 2023-06-05 13:35:30 |
+---------------------+
1 row in set (0.00 sec)
4.date(时间)/time(时间),获取当前日期的日期/时间部分
mysql8.0 [SLB]>select date('2023-06-05 13:35:30');
+-----------------------------+
| date("2023-06-05 13:35:30") |
+-----------------------------+
| 2023-06-05 |
+-----------------------------+
1 row in set (0.00 sec)
mysql8.0 [SLB]>select time('2023-06-05 13:35:30');
+-----------------------------+
| time("2023-06-05 13:35:30") |
+-----------------------------+
| 13:35:30 |
+-----------------------------+
1 row in set (0.00 sec)
5.month,monthname,day,daymonth,dayname,year,dayofweek,week,dayofyear
mysql8.0 [SLB]>select month('2023-06-05 13:35:30'); 这年中的第几月
+------------------------------+
| month("2023-06-05 13:35:30") |
+------------------------------+
| 6 |
+------------------------------+
1 row in set (0.00 sec)
mysql8.0 [SLB]>select monthname('2023-06-05 13:35:30'); #这月份英文名称
+----------------------------------+
| monthname("2023-06-05 13:35:30") |
+----------------------------------+
| June |
+----------------------------------+
1 row in set (0.00 sec)
mysql8.0 [SLB]>select day('2023-06-05 13:35:30'); #这个月中的第几天
+----------------------------+
| day("2023-06-05 13:35:30") |
+----------------------------+
| 5 |
+----------------------------+
1 row in set (0.00 sec)
mysql8.0 [SLB]>select dayofmonth('2023-06-05 13:35:30'); #这个月中的第几天
+-----------------------------------+
| dayofmonth("2023-06-05 13:35:30") |
+-----------------------------------+
| 5 |
+-----------------------------------+
1 row in set (0.00 sec)
mysql8.0 [SLB]>select dayname('2023-06-05 13:35:30'); #这周中的星期几的英文名称
+--------------------------------+
| dayname("2023-06-05 13:35:30") |
+--------------------------------+
| Monday |
+--------------------------------+
1 row in set (0.00 sec)
mysql8.0 [SLB]>select year('2023-06-05 13:35:30'); #第几年
+-----------------------------+
| year("2023-06-05 13:35:30") |
+-----------------------------+
| 2023 |
+-----------------------------+
1 row in set (0.00 sec)
mysql8.0 [SLB]>select dayofweek('2023-06-05 13:35:30'); #这个月中的第几周
+----------------------------------+
| dayofweek("2023-06-05 13:35:30") |
+----------------------------------+
| 2 |
+----------------------------------+
1 row in set (0.00 sec)
mysql8.0 [SLB]>select week('2023-06-05 13:35:30'); #这年中的第几周
+-----------------------------+
| week("2023-06-05 13:35:30") |
+-----------------------------+
| 23 |
+-----------------------------+
1 row in set (0.00 sec)
mysql8.0 [SLB]>select dayofyear('2023-06-05 13:35:30'); #这年中的第几天
+----------------------------------+
| dayofyear("2023-06-05 13:35:30") |
+----------------------------------+
| 156 |
+----------------------------------+
1 row in set (0.00 sec)
6.datediff(日期1,日期2),返回两个日期之间的相差天数
mysql8.0 [SLB]>select datediff('2007-12-30','2023-06-05');
+-------------------------------------+
| datediff('2007-12-30','2023-06-05') |
+-------------------------------------+
| -5636 |
+-------------------------------------+
1 row in set (0.00 sec)
7.sec_to_time()/time_to_sec(),将秒转换为时间/将时间转换为秒数,转换为秒数时默认是从当天00:00:00开始计算
mysql8.0 [SLB]>select sec_to_time('3659');
+---------------------+
| sec_to_time('3659') |
+---------------------+
| 01:00:59.000000 |
+---------------------+
1 row in set (0.00 sec)
mysql8.0 [SLB]>select time_to_sec('13:57');
+----------------------+
| time_to_sec('13:57') |
+----------------------+
| 50220 |
+----------------------+
1 row in set (0.00 sec)
五.流程控制函数
1.if(表达式,结果1,结果2),表达式为真时,返回结果1,否则返回结果2,为null返回结果2
mysql8.0 [SLB]>select if(1>2,"y","n");
+-----------------+
| if(1>2,"y","n") |
+-----------------+
| n |
+-----------------+
1 row in set (0.00 sec)
mysql8.0 [SLB]>select if(null,"y","n");
+------------------+
| if(null,"y","n") |
+------------------+
| n |
+------------------+
1 row in set (0.00 sec)
2.ifnull(结果1,结果2),如果结果1为空返回结果2,不为空返回结果1
mysql8.0 [SLB]>select ifnull("y","");
+----------------+
| ifnull("y","") |
+----------------+
| y |
+----------------+
1 row in set (0.00 sec)
mysql8.0 [SLB]>select ifnull(null,"n");
+------------------+
| ifnull(null,"n") |
+------------------+
| n |
+------------------+
1 row in set (0.00 sec)
3.case 判断的值 when 表达式1 then 执行的内容1 when 表达式2 then 执行的内容2 else 其他情况需要执行的内容3 end;
mysql8.0 [SLB]>select case "sulibao" when "lixinjin" then "f" when "sulibao" then "t" else "err" end;
+--------------------------------------------------------------------------------+
| case "sulibao" when "lixinjin" then "f" when "sulibao" then "t" else "err" end |
+--------------------------------------------------------------------------------+
| t |
+--------------------------------------------------------------------------------+
1 row in set (0.00 sec)