目录
一、统计函数
1.基本语法 :
SELECT COUNT(*) / COUNT (column_name)... FROM table_name
[WHERE where_definitition];
注意事项——
①COUNT(*)表示查询整条记录,只要该记录满足WHERE子句的条件就会被统计在内;
②COUNT(column_name)表示在符合WHERE子句条件的基础上,还必须满足指定列不为NULL。
③可以在一条查询语句中使用多个COUNT(column_name),以同时统计多个列,分别显示各个列的统计结果。
④若想特意查询某列为空的记录,可以配合IF语句来使用,
eg : SELECT COUNT(IF(`ebonus` IS NULL, 1, NULL)) FROM `employee`; 该查询语句的意思是如果某条记录的`ebonus`字段为NULL,就返回一个非空字符(这里的1可以是任意的非空字符),继而被COUNT函数统计在内;如果某条记录的`ebonus`字段非NULL,就返回NULL,继而不被COUNT函数统计在内。
PS : 亦可以使用两个COUNT函数进行相减的操作,即用统计所有结果的COUNT函数 - 统计非空的COUNT函数。eg : SELECT COUNT(*) - COUNT(`ebonus`) FROM `employee`; [体现了SQL的灵活性!]⑤若希望COUNT函数的统计结果去重,可以在column_name前增加DISTINCT进行修饰,即COUNT(DISTINCT column_name)。
2.代码演示 :
演示Ⅰ——
现有学生表stus如下——
现要求——
①统计表中一共有几位学生(即表中一共有多少条记录);
②统计数学成绩大于等于130的学生个数;
代码如下 :
# 统计stus表中共有多少学生
SELECT COUNT(*) FROM `stus`;
# 统计Math >= 130的学生个数
SELECT COUNT(*) FROM `stus`
WHERE `Math` >= 130;
运行结果 :
演示Ⅱ——
向表中添加两条id = NULL的记录,stus表如下图所示 :
现要求——
①统计英语成绩在80分以下的学生个数;
②统计英语成绩在80分以下的,且id不等于NULL的学生个数;
代码如下 :
# 统计英语成绩在80分以下的学生的个数
SELECT COUNT(*) FROM `stus`
WHERE `English` < 80;
# 统计英语成绩在80分以下的并且id不为空的学生的个数
SELECT COUNT(id) FROM `stus`
WHERE `English` < 80;
运行结果 :
二、合计函数
1.基本语法 :
SELECT SUM(column_name), SUM(column_name)... FROM table_name
[WHERE where_definition];
注意事项——
①SUM函数仅对数值型字段起作用,否则无意义;
②可以同时对多个列求和。
2.代码演示 :
仍对stus表进行操作,stus表目前如下 :
现要求——
①统计所有学生的数学成绩;
②统计所有学生的英语成绩;
代码如下 :
SELECT SUM(`Math`) AS `Math_total`,SUM(`English`) AS `English_total`
FROM `stus`;
运行结果 :
三、平均函数
1.基本语法 :
SELECT AVG(column_name), AVG(column_name)... FROM table_name
[WHERE where_definition];
注意事项——
①若求平均值的列中有NULL数据,AVG函数会自动跳过该NULL数据。
②除了AVG函数,还可以使用SUM(column_name) / COUNT(column_name)的方式来统计某一字段的平均值。
2.代码演示 :
仍对stus表进行操作,stus表目前如下 :
现要求——
①查询所有学生的数学成绩的平均分以及英语成绩的平均分;
②将id = 2的学生Ice的数学成绩置为空,再次查询全体学生的数学平均分;
代码如下 :
SELECT AVG(`Math`), AVG(`English`) FROM `stus`;
UPDATE `stus`
SET `Math` = NULL
WHERE `id` = 2;
SELECT SUM(`Math`) / COUNT(`Math`) AS `average_Math` FROM `stus`;
运行结果 :
四、最值函数
1.基本语法 :
SELECT MAX(columun_name)... / MIN(column_name)... FROM table_name
[WHERE where_definition];
注意事项——
①与上面几个函数类似地,MAX和MIN函数也可以同时多个使用,并且可以混用。
2.代码演示 :
仍然操作stus表,目前stus表如下 :
现要求——
①查询全体学生中最高的数学成绩和最低的数学成绩;
②查询全体学生中最高的英语成绩和最低的英语成绩;
代码如下 :
SELECT MAX(`Math`), MIN(`Math`) FROM `stus`;
SELECT MAX(`English`), MIN(`English`) FROM `stus`;
运行结果 :
五、字符串函数
1.常用字符串函数 :
1° CHARSET(str) : 返回字段(具体的字符串)的字符集类型;
2° CONCAT(string, string2...) : 连接多个字符串,将多个列拼接成一列;
3° INSTR(string, substring) : 返回substring在string中出现的位置,没有则返回0;
4° UCASE(string) : 将当前字符串转换成大写;
5° LCASE(string) : 将当前字符串转换成小写;
6° LEFT(string, length) : 从string的左边起取length个字符;(RIGHT可从右取)
7° LENGTH(string) : 获取当前string的长度(字节);
8° REPLACE(str, old_string, new_string) : 用new_string替换掉str中的old_string);
9° STRCMP(string1, string2) : 按照逐个字符比较两字符串大小;
10° SUBSTRING(str, position [, length]) : 从str的position开始(默认是1),取LENGTH个字符;
11° LTRIM(string) / RTRIM(string) / TRIM(string) : 去掉前端空格 / 后端空格 / 两端空格。
2.代码演示 :
① CHARSET(str) : 返回字段的字符集类型;
# 查询ename字段和ecareer字段的字符集类型
SELECT CHARSET(`ename`), CHARSET(`ecareer`)
FROM `employee`;
② CONCAT(string, string2...) : 连接多个字符串;
# 查询自定义拼接后的列
SELECT CONCAT(`ename`, ' 是在 ', `hiredate`, ' 入职的') AS `hiredate_info`
FROM `employee`;
③ INSTR(string, substring) : 返回substring在string中出现的位置,没有则返回0;
# DUAL 亚元表,是一个系统表,可以作为默认的测试表来使用
SELECT INSTR('I love programming!', 'ing'),
INSTR('I love programming!', 'hahah') AS `test`
FROM DUAL;
④ UCASE(string) : 将当前字符串转换成大写;
SELECT UCASE(`ename`) FROM `employee`;
⑤ LCASE(string) : 将当前字符串转换成小写;
SELECT LCASE('I_loVe_YOU') AS `test` FROM DUAL;
⑥ LEFT(string, length) : 从string的左边起取length个字符;
SELECT LEFT(`ename`, 3) FROM `employee`;
⑦ LENGTH(string) : 获取当前string的长度(字节);
SELECT LENGTH('abc'),
LENGTH('哈哈哈'),
LENGTH('...'),
LENGTH('。。。')
FROM DUAL;
⑧ REPLACE(str, old_string, new_string) : 用new_string替换掉str中的old_string)
SELECT REPLACE('I love programming!', 'love ', 'relish ') AS `replace_result`
FROM DUAL;
⑨ STRCMP(string1, string2) : 按照逐个字符比较两字符串大小
SELECT STRCMP('abc', 'abcdefg') AS `t1`,
STRCMP('dsae','dsad') AS `t2`,
STRCMP('hah', 'hah') AS `t3`
FROM DUAL;
⑩ SUBSTRING(str, position [, length]) : 从str的position开始(默认是1),取LENGTH个字符;
SELECT SUBSTRING('I love programming forever!', 3, 4) AS `sub_res`
FROM DUAL;
⑩① LTRIM(string) / RTRIM(string) / TRIM(string) : 去掉前端空格 / 后端空格 / 两端空格。
SELECT LTRIM(' haha') AS `left_res`,
RTRIM('hah ') AS `Right_res`,
TRIM(' haah ') AS `Trim_res`
FROM DUAL;
六、数学函数
1.常用数学函数 :
1° ABS(num) : 求绝对值;
2° BIN(decimal_number) : 十进制转二进制;
3° CEILING(number) : 向上取整;
4° FLOOR(number) : 向下取整;
5° CONV(number, from_base, to_base) : 进制转换,将number以一个指定的进制转为另一个进制;
6° FORMAT(NUMBER, decimal_places) : decimal_places代表保留的小数位数(四舍五入);
7° HEX(DecimalNumber) : 转十六进制;
8° LEAST(number_1 [, number_2...number_n]) : 求最小值;
9° MOD(numerator, denominator) : 取余;
10° RAND([seed]) : 求0~1.0之间的随机数,若想要返回的随机数不变化,可以给出一个seed(种子)。
2.代码演示 :
1° ABS(num) : 求绝对值;
# DUAL 亚元表,是一个系统表,可以作为默认的测试表来使用
SELECT ABS(0.00) AS `a1`,
ABS(2333) AS `a2`,
ABS(-6.66) AS `a3`,
ABS(-333) AS `a4`
FROM DUAL;
2° BIN(decimal_number) : 十进制转二进制;
SELECT BIN(10),
BIN(11),
BIN(12)
FROM DUAL;
3° CEILING(number) : 向上取整;
SELECT CEILING(2.2) AS `c1`,
CEILING(0.8) AS `c2`,
CEILING(-0.99) AS `c3`,
CEILING(-1.01) AS `c4`
FROM DUAL;
4° FLOOR(number) : 向下取整;
SELECT FLOOR(2.2) AS `f1`,
FLOOR(0.8) AS `f2`,
FLOOR(-0.99) AS `f3`,
FLOOR(-1.01) AS `f4`
FROM DUAL;
5° CONV(number, from_base, to_base) : 进制转换
SELECT CONV(55, 10, 2) AS `conv_res1`,
CONV(55, 8, 10) AS `conv_res2`,
CONV(55, 16, 10) AS `conv_res3`
FROM DUAL;
6° FORMAT(NUMBER, decimal_places) : 保留小数位数(四舍五入);
SELECT FORMAT(22.222222, 2) AS `test1`,
FORMAT(22.22888, 2) AS `test2`,
FORMAT(-0.747, 2) AS `test3`
FROM DUAL;
7° HEX(DecimalNumber) : 转十六进制;
SELECT HEX(16),
HEX(65),
HEX(126)
FROM DUAL;
8° LEAST(number_1 [, number_2...number_n]) : 求最小值;
SELECT LEAST(0, -1.08, 22, 100) AS `least_1`,
LEAST(3, 8, 3, 11) AS `least_2`,
LEAST(1000, 233.3) AS `least_3`
FROM DUAL;
9° MOD(numerator, denominator) : 取余;
SELECT MOD(10, 3),
MOD(55, 10)
FROM DUAL;
10° RAND([seed]) : 求0~1.0之间的随机数
SELECT FORMAT(RAND(), 2) AS `random1`,
FORMAT(RAND(), 2) AS `random2`,
FORMAT(RAND(5), 2) AS `random3`
FROM DUAL;
七、日期函数
1.常用日期函数 :
1° CURRENT_DATE : 当前日期;
2° CURRENT_TIME : 当前时间;
3° CURRENT_TIMESTAMP : 当前时间戳;
4° DATE(datetime) : 返回当前时间的日期部分;
//以下四个函数的date类型可以是DATE, DATETIME, TIMESTAMP类型
5° DATE_ADD(date, INTERVAL d_value d_type) : 在当前日期的基础上加上一段时间或日期;(d_type 可以是YEAR, MINUTE, SECOND, DAY等)
6° DATE_SUB(date, INTERVAL d_value d_type) : 在当前日期的基础上减去一段时间或日期;(d_type 可以是YEAR, MINUTE, SECOND, DAY等)
7° DATEDIFF(date1, date2) : 返回两个日期的时间差,结果是天;
8° TIMEDIFF(date1, date2) : 返回两个时间的时间差,结果是时间;
9° NOW() : 返回当前时间;
10° UNIX_TIMESTAMP() : 返回1970-1-1到现在的秒数。
11° FROM_UNIXTIME() : 返回年月日。实际开发中,可能会使用int类型来保存一个时间戳,然后使用FROM_UNIXTIME()函数进行转换。
2.代码演示 :
1° CURRENT_DATE : 当前日期;
2° CURRENT_TIME : 当前时间;
3° CURRENT_TIMESTAMP : 当前时间戳;
SELECT CURRENT_DATE(),
CURRENT_TIME(),
CURRENT_TIMESTAMP()
FROM DUAL;
4° DATE(datetime) : 返回当前时间的日期部分;
5° DATE_ADD(date, INTERVAL d_value d_type) : 在当前日期的基础上加上一段时间或日期;
6° DATE_SUB(date, INTERVAL d_value d_type) : 在当前日期的基础上减去一段时间或日期;
先来建一张新闻消息表,代码如下:
CREATE TABLE IF NOT EXISTS `timetable`(
`id` MEDIUMINT NOT NULL DEFAULT 0,
`name` VARCHAR(50) NOT NULL DEFAULT '',
`send_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP
);
INSERT INTO `timetable`
VALUES(1, '震惊,XXX', '2023-5-14 10:35:22'),
(2, '卧槽,XXX', '2023-5-14 10:36:42'),
(3, '我趣,XXX', '2023-5-14 10:40:12');
SELECT * FROM `timetable`;
消息表如下 :
现要求——
①取出name中含有卧槽的新闻及其发布时间,且发布时间仅要求日期;
SELECT `name`, DATE(`send_time`)
FROM `timetable`
WHERE `name` LIKE '卧槽%';
②取出20分钟内发布的新闻;
/*
WHERE子句的含义是要求发布时间 + 20分钟后大于当前时间的新闻,
即20分钟内发布的新闻。
*/
# 思路一 :
SELECT * FROM `timetable`
WHERE DATE_ADD(`send_time`, INTERVAL 20 MINUTE) >= NOW();
# 思路二 :
SELECT * FROM `timetable`
WHERE DATE_SUB(NOW(), INTERVAL 20 MINUTE) <= `send_time`;
7° DATEDIFF(date1, date2) : 返回两个日期的时间差,结果是天;
8° TIMEDIFF(date1, date2) : 返回两个时间的时间差,结果是时间;
# 求一个2002-1-1日出生的人,活到120岁还能活几天
SELECT DATEDIFF(DATE_ADD('2002-1-1', INTERVAL 120 YEAR), DATE(NOW()))
FROM DUAL;
# 求时间差
SELECT TIMEDIFF('20:14:44', '14:22:11') FROM DUAL;
10° UNIX_TIMESTAMP() : 返回1970-1-1到现在的秒数。
11° FROM_UNIXTIME() : 返回年月日。实际开发中,可能会使用int类型来保存一个时间戳,然后使用FROM_UNIXTIME()函数进行转换。
SELECT UNIX_TIMESTAMP(),
FROM_UNIXTIME(UNIX_TIMESTAMP(), '%Y-%m-%d %H:%i:%s') AS `STAMP`
FROM DUAL;
八、加密函数
1.基本语法 :
1° SELECT USER() FROM table_name; ——查询当前登录到MySQL的用户及其IP;
2° SELECT DATABASE(); ——查询当前使用的数据库名称;
3° SELECT MD5(str); ——根据当前字符串,通过MD5特定算法得到一个32位的密码,常用于对数据库用户密码的加密,并且数据库中保存的用户密码,往往就是MD5加密后的密码;
4° SELECT * FROM mysql.user; ——查询mysql数据库中的所有用户。
2.代码演示 :
1° SELECT USER() FROM table_name;
2° SELECT DATABASE();
SELECT USER(), DATABASE();
3° SELECT MD5(str);
SELECT MD5('123456'), MD5('2333');
CREATE TABLE IF NOT EXISTS `test_users`(
`id` MEDIUMINT NOT NULL DEFAULT 0,
`name` VARCHAR(32) NOT NULL DEFAULT '',
`pwd` CHAR(32) NOT NULL DEFAULT ''
);
INSERT INTO `test_users`
VALUES(11, 'Cyan', MD5('123456')),
(12, 'Raina', MD5('2333'));
SELECT * FROM `test_users`
WHERE `pwd` = MD5('123456') OR `pwd` = MD5('2333');
4° SELECT * FROM mysql.user; ——查询mysql数据库中的所有用户
SELECT * FROM mysql.user;
九、 流程控制函数
1.基本语法 :
1° SELECT IF(expr_1, expr_2, expr_3) FROM table_name; ——若expr_1为TRUE则返回expr_2,否则返回expr_3)。(相当于java中的三目运算符)
2° SELECT IFNULL(expr_1, expr_2) FROM table_name; ——若expr_1为NULL,返回expr_2,否则返回expr_1)。
3° SELECT CASE WHEN expr_1 THEN expr_2
WHEN expr_3 THEN expr_4...
WHEN expr_n-1 THEN expr_n
ELSE expr_e END; ——若expr_1成立,则返回expr_2;若expr_3成立,则返回expr_4,依此类推;若所有条件都不成立,返回ELSE中的内容。(相当于java中自带break语句的case分支语句)。
2.代码演示 :
1° SELECT IF(expr_1, expr_2, expr_3) FROM table_name;
2° SELECT IFNULL(expr_1, expr_2) FROM table_name;
SELECT IF(2 < 3, 'haha', 'wuwu') AS `if_1`,
IF(2 > 3, 'hah', 'wuwuw') AS `if_2`,
IFNULL(NULL, 2333) AS `ifnull_1`,
IFNULL(666, 2333) AS `ifnull_2`;
3° SELECT CASE WHEN expr_1 THEN expr_2
WHEN expr_3 THEN expr_4...
WHEN expr_n-1 THEN expr_n
ELSE expr_e END;
`stus`表如下 :
SELECT `name`, `sex`, `English`, `Math`,
(SELECT CASE WHEN `sex` = 'female' THEN 'woman'
WHEN `sex` = 'male' THEN 'man'
ELSE 'unknown' END) AS `test`
FROM `stus`;
System.out.println("END-----------------------------------------------------------------------------");