目录
一、统计函数
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表中共有多少学生 SELECTCOUNT(*)FROM `stus`;# 统计Math >=130的学生个数 SELECTCOUNT(*)FROM `stus` WHERE `Math` >=130;
运行结果 :
演示Ⅱ——
向表中添加两条id = NULL的记录，stus表如下图所示 :
编辑
现要求——
①统计英语成绩在80分以下的学生个数；
②统计英语成绩在80分以下的，且id不等于NULL的学生个数；
代码如下 :
# 统计英语成绩在80分以下的学生的个数 SELECTCOUNT(*)FROM `stus` WHERE `English` <80;# 统计英语成绩在80分以下的并且id不为空的学生的个数 SELECTCOUNT(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 `sts`Math` =NULLWHERE `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) : 保留小数位数（四舍五入）；
7° HEX(DecimalNumber) : 转十六进制；
8° LEAST(number_1 [, number_2...number_n]) : 求最小值；
9° MOD(numerator, denominator) : 取余；
10° RAND([seed]) : 求0~1.0之间的随机数，若想要返回的随机数不变化，可以给出一个seed（种子）。
2.代码演示 :
1ABS(num) : 求绝对值；
# DUAL 亚元表，是一个系统表，可以作为默认的测试表来使用 SELECT ABS(0.00)AS `a1`, ABS(2333)AS `a2`, ABS(-6.66)AS `a3`, ABS(-333)AS `a4` FROM DUAL;
编辑
2BIN(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) : 在当前日期的基础上减去一段时间或日期；
先来建一张新闻消息表，代码如下：
CREATETABLE IF NOT EXISTS `timetable`( `id` MEDIUMINTNOTNULL DEFAULT 0, `name` VARCHAR(50)NOTNULL DEFAULT '', `send_time` TIMESTAMPNOTNULL DEFAULT CURRENT_TIMESTAMP ONUPDATE CURRENT_TIMESTAMP );INSERTINTO `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 120YEAR),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');CREATETABLE IF NOT EXISTS `test_users`( `id` MEDIUMINTNOTNULL DEFAULT 0, `name` VARCHAR(32)NOTNULL DEFAULT '', `pwd` CHAR(32)NOTNULL DEFAULT '');INSERTINTO `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°SELECTCASE 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°SELECTCASE WHEN expr_1 THEN expr_2
WHEN expr_3 THEN expr_4...
WHEN expr_n-1 THEN expr_n
ELSE expr_e END;
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-----------------------------------------------------------------------------");
