聚合函数
数据准备
-- 创建数据库
DROP DATABASE IF EXISTS mydb;
CREATE DATABASE mydb;
USE mydb;
-- 创建student表
CREATE TABLE student (
sid CHAR(6),
sname VARCHAR(50),
age INT,
gender VARCHAR(50) DEFAULT 'male'
);
-- 向student表插入数据
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1001', 'lili', 14, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1002', 'wang', 15, 'female');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1003', 'tywd', 16, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1004', 'hfgs', 17, 'female');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1005', 'qwer', 18, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1006', 'zxsd', 19, 'female');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1007', 'hjop', 16, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1008', 'tyop', 15, 'female');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1009', 'nhmk', 13, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1010', 'xdfv', 17, 'female');
所谓聚合,就是将多行汇总成一行;其实,所有的聚合函数均如此——输入多行,输出一行。聚合函数具有自动滤空的功能,若某一个值为NULL,那么会自动将其过滤使其不参与运算。
Count()
统计表中数据的行数或者统计指定列其值不为NULL的数据个数
-
示例文章来源地址https://www.toymoban.com/news/detail-486695.html
select count(*) from student;
Max()
计算指定列的最大值,如果指定列是字符串类型则使用字符串排序运算
-
示例
select max(age) from student;
Min()
计算指定列的最小值,如果指定列是字符串类型则使用字符串排序运算
-
示例
SELECT MIN(age) FROM student;
Sum()
计算指定列的数值和,如果指定列类型不是数值类型则计算结果为0
-
示例
select sum(age) from student;
Avg()
计算指定列的平均值,如果指定列类型不是数值类型则计算结果为0
-
示例
select avg(age) from student; select avg(sname) from student;
其他常用函数
时间函数
SELECT NOW(); # 2023-06-11 09:38:22
SELECT DAY (NOW()); # 11
SELECT DATE (NOW()); # 2023-06-11
SELECT TIME (NOW()); # 09:38:23
SELECT YEAR (NOW()); # 2023
SELECT MONTH (NOW()); # 6
SELECT CURRENT_DATE(); # 2023-06-11
SELECT CURRENT_TIME(); # 09:38:24
SELECT CURRENT_TIMESTAMP(); # 2023-06-11 09:38:24
SELECT ADDTIME('14:23:12','01:02:01'); # 15:25:13
SELECT DATE_ADD(NOW(),INTERVAL 1 DAY); # 2023-06-12 09:38:25
SELECT DATE_ADD(NOW(),INTERVAL 1 MONTH); # 2023-07-11 09:38:25
SELECT DATE_SUB(NOW(),INTERVAL 1 DAY); # 2023-06-10 09:38:25
SELECT DATE_SUB(NOW(),INTERVAL 1 MONTH); # 2023-05-11 09:38:26
SELECT DATEDIFF('2019-07-22','2019-05-05'); # 78
字符串函数
SUBSTRING函数:切割字符串([start:end])
-
语法格式
SUBSTRING(s, start, length)
如果SUBSTRING()函数接收2个参数;第一个参数为待截取的字符串,第二个参数为截取的起始位置。如果第二个参数为负整数,则为倒数的起始位置
如果接受3个参数;则第一个参数为待截取的字符串,第二个参数为截取的起始位置,第三个参数为截取的长度。如果第二个参数为负整数,则为倒数的起始位置
-
示例
-- 建表语句 DROP TABLE IF EXISTS order_detail; CREATE TABLE order_detail( order_id VARCHAR(8), order_time VARCHAR(32) ) ENGINE = InnoDB DEFAULT CHARSET = utf8; INSERT INTO order_detail (order_id,order_time) VALUE ('o001','2020-06-15 09:12:33') ,('o002','2020-06-15 13:24:09') ,('o003','2020-06-16 10:44:24') ,('o004','2020-06-17 08:05:52') ,('o005','2020-06-18 18:03:43'); -- 查询语句 SELECT * , SUBSTRING(order_time,1,4) AS order_year, SUBSTRING(order_time,6,2) AS order_month, SUBSTRING(order_time,9,2) AS order_day FROM order_detail;
SUBSTRING_INDEX函数:切割字符串(split)
-
语法格式
SUBSTRING_INDEX(str, delimiter, number)
返回从字符串str的第number个出现的分隔符delimiter之前的子串;
如果number是正数,那么就是从左往右数,返回第number个分隔符的左边的全部内容;
相反,如果number是负数,那么就是从右边开始数,第number个分隔符右边的所有内容
注意:如果number超过了实际分隔符的个数,则返回实际个数的字符串
-
示例演示
-- 案例1 SELECT SUBSTRING_INDEX('a*b','*',1) -- a -- 案例2 SELECT SUBSTRING_INDEX('a*b','*',-1) -- b -- 案例3 SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('a*b*c*d*e','*',3),'*',-1) -- c -- 案例3可拆解成以下两个步骤 SELECT SUBSTRING_INDEX('a*b*c*d*e','*',3); -- a*b*c SELECT SUBSTRING_INDEX('a*b*c','*',-1) -- c -- 案例4 -- 如果任一输入参数为NULL,则返回NULL SELECT SUBSTRING_INDEX('https://www.google.com', null, 2); SELECT SUBSTRING_INDEX('https://www.google.com', '.', null); SELECT SUBSTRING_INDEX(null, '.', 2); -- 案例5 -- 如果number超过了实际分隔符的个数,则返回实际个数的字符串 -- https://www.google.com SELECT SUBSTRING_INDEX('https://www.google.com', '.', 4);
-
示例实战
drop table if exists user_submit; CREATE TABLE `user_submit` ( `id` int NOT NULL, `device_id` int NOT NULL, `profile` varchar(100) NOT NULL, `blog_url` varchar(100) NOT NULL ); INSERT INTO user_submit VALUES(1,2138,'180cm,75kg,27,male','http:/url/bisdgboy777'); INSERT INTO user_submit VALUES(2,3214,'165cm,45kg,26,female','http:/url/dkittycc'); INSERT INTO user_submit VALUES(3,6543,'178cm,65kg,25,male','http:/url/tigaer'); INSERT INTO user_submit VALUES(4,4321,'171cm,55kg,23,female','http:/url/uhsksd'); INSERT INTO user_submit VALUES(5,2131,'168cm,45kg,22,female','http:/url/sysdney'); SELECT device_id, SUBSTRING_INDEX(blog_url,'/',-1)AS user_name FROM user_submit;
LOWER&UPPER函数:大小写转化
-
语法格式
LOWER(str)
将字符串str的所有字母转换成小写字母
UPPER(str)
将字符串str的所有字母转换成大写字母
-
示例
DROP TABLE IF EXISTS `vendors`; CREATE TABLE `vendors` ( `id` int(11) NOT NULL, `vend_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; INSERT INTO `vendors` VALUES (1, 'Acme'); INSERT INTO `vendors` VALUES (2, 'Anvils R Us'); INSERT INTO `vendors` VALUES (3, 'Furball Inc.'); INSERT INTO `vendors` VALUES (4, 'Jet Set'); INSERT INTO `vendors` VALUES (5, 'Jouets Ft Ours'); INSERT INTO `vendors` VALUES (6, 'LT Supplies');
SELECT vend_name, UPPER(vend_name)AS vend_name_upper, LOWER(vend_name)AS vend_name_lower FROM vendors ORDER BY vend_name;
CONCAT&CONCAT_WS函数:连接字符串
-
CONCAT语法格式
SELECT CONCAT(str1,str2,…) FROM [表名];
将多个字段拼接为一个字段 (字符串 str1,str2 等多个字符串合并为一个字符串,多个字符串之间用逗号分隔)
-
注意事项
可以有很多个参数,如果参数有一个是NULL,则结果返回NULL(使用CONCAT()函数对包含NULL的数据进行拼接时,结果为NULL,所以在对数据进行拼接之前,应该先检查要拼接的字段是否存在NULL)
-
示例
-- 结果是MySQL SELECT CONCAT('My', 'S', 'QL'); -- 结果是SQL Runoob Gooogle Facebook SELECT CONCAT("SQL ", "Runoob ", "Gooogle ", "Facebook"); -- 结果是Null SELECT CONCAT('My', NULL, 'QL'); -- 一个数字参数被变换为等价的字符串形式 -- 结果是143 SELECT CONCAT(14,3);
-
CONCAT_WS语法格式
SELECT CONCAT_WS(‘拼接符’,str1,str2,…) FROM [表名];
可以理解成在CONCAT的基础上增添了拼接符
-
注意事项
多字段拼接,字段间使用同样的拼接符(会自动跳过NULL)
-
示例
-- 结果是data_frog_study SELECT CONCAT_WS("_", "data", "frog", "study"); -- 结果是SQL-Tutorial-is-fun! SELECT CONCAT_WS("-", "SQL", "Tutorial", "is", "fun!")AS ConcatenatedString; -- 结果是hello-word! SELECT CONCAT_WS("-", "hello", null, "word!")AS ConcatenatedString;
REPLACE函数:字符串替换
将字符串to_str替代字符串str中的字符串from_str,返回替换后的字符串str
-
语法格式
格式: REPLACE(str,from_str,to_str)
-
注意事项
如果from_str不在str内,直接跳过
-
示例
-- 结果是http:www.google.com.cn SELECT REPLACE('http://www.google.com.cn/','/',''); -- 将字符串abc中的字符a替换为字符x SELECT REPLACE('abc','a','x') -- xbc -- 13922221111 SELECT REPLACE('13922221111','136','138');
--
SELECT INSTR ();
--统计长度
SELECT LENGTH();
数学函数
ROUND函数:四舍五入
把数值字段舍入为指定的小数位数
-
语法格式
SELECT ROUND(column_name,[decimals]) FROM [表名];
- column_name: 要舍入的字段,必需
- decimals: 规定要返回的小数位数,可选
-
示例一:ROUND(X)
返回’参数X’四舍五入的一个整数
-- ROUND(X)函数将值X四舍五入之后保留整数部分 -- -7 SELECT ROUND(-6.6); -- -8 SELECT ROUND(-8.49); -- -9 SELECT ROUND(-8.50); -- 1 SELECT ROUND(1); -- 2 SELECT ROUND(1.58); -- 3 SELECT ROUND(3.49); -- 4 SELECT ROUND(3.50); -- ROUND(X)与ROUND(X,0)所得结果相同 -- 1123 SELECT ROUND(1123.26723); -- 1123 SELECT ROUND(1123.26723,0);
-
示例二:ROUND(X,D)
返回’参数X’四舍五入的有 D 位小数的一个数字。
- 如果D为0,结果将没有小数点或小数部分;
- 如果D为负数,这时是指定小数点左边的D位整数位为0,同时小数位均为0
-- 1123.27 SELECT ROUND(1123.26723,2); -- 1123.3 SELECT ROUND(1123.26723,1); -- 1123 SELECT ROUND(1123.26723,0); -- 1120 SELECT ROUND(1123.26723,-1); -- 1100 SELECT ROUND(1123.26723,-2); -- 1000 SELECT ROUND(1123.26723,-3); -- 根据参数D值,将参数X四舍五入后得到保留小数点后D位的值,X值的小数位不够D位的补零 -- 如果D为负值,则保留小数点左边D位,先进行四舍五入操作,再将相应的位数值取零 -- -6.7 SELECT ROUND(-6.66,1); -- 3.33 SELECT ROUND(3.33,3); -- 90 SELECT ROUND(88.66,-1); -- 100 SELECT ROUND(88.46,-2);
其他函数
IFNULL函数:判断是否为NULL(类比与dict.get())
-
语法格式
IFNULL(expression, alt_value)
-
expression: 要测试的值
-
alt_value: expression表达式为NULL时返回的值
-
-
扩展
如果要检查值是否为
NULL
,则可以在WHERE
子句中使用IS NULL
或IS NOT NULL
文章来源:https://www.toymoban.com/news/detail-486695.html -
示例
-- 第一个参数为NULL SELECT IFNULL(NULL, "World"); -- World -- 第一个参数不为NULL SELECT IFNULL("Hello", "World"); -- Hello -- 1不为NULL SELECT IFNULL(1,0); -- 1 -- ''字符串不为NULL SELECT IFNULL('',1); -- '' SELECT IFNULL(NULL,'IFNULL function'); -- IFNULL function
CREATE TABLE IF NOT EXISTS contacts ( contact_id INT AUTO_INCREMENT PRIMARY KEY, contact_name VARCHAR(20) NOT NULL, business_phone VARCHAR(15), home_phone VARCHAR(15) ); INSERT INTO contacts(contact_name,business_phone,home_phone) VALUES('John Doe','(541) 754-3009',NULL), ('Cindy Minsu',NULL,'(541) 754-3110'), ('Sue Greenspan','(541) 754-3010','(541) 754-3011'), ('Lily Bush',NULL,'(541) 754-3111'); # 如果联系人的商务电话不可用,可以通过查询获得联系人的家庭电话也是一个获取联系方式的办法 SELECT contact_name, IFNULL(business_phone, home_phone)AS phone FROM contacts;
-- 绝对值
SELECT ABS(-136); # 136
-- 向下取整
SELECT FLOOR(3.14); # 3
-- 向上取整
SELECT CEILING(3.14); # 4
到了这里,关于【MySQL】SQL的函数用法的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!