第六章 SQL查询二(函数)

第六章 SQL查询二

一,MySQL 中的函数

常用的几类函数
字符串函数
用于控制返回给用户的字符串
日期函数
用于操作日期值
数学函数
用于对数值进行代数运算

二,字符串

函数名	描　　述	示　　例
CONCAT(s1,s2…,sn)	将s1,s2…,sn连接成字符串	SELECT CONCAT('a‘,'b‘,‘c’)
LENGTH	返回传递给它的字符串长度	SELECT LENGTH(‘MySQL 课程’) 返回：12
UPPER	把传递给它的字符串转换为大写	SELECT UPPER(‘MySQL课程’) 返回：MYSQL 课程
LTRIM	清除字符左边的空格	SELECT LTRIM (’ 周智宇 ') 返回：周智宇 （后面的空格保留）
RTRIM	清除字符右边的空格	SELECT RTRIM (’ 周智宇 ') 返回： 周智宇（前面的空格保留）
RIGHT	从字符串右边返回指定数目的字符	SELECT RIGHT(‘买卖提.吐尔松’,3) 返回：吐尔松
REPLACE	替换一个字符串中的字符	SELECT REPLACE(‘莫乐可切.杨可’,‘可’,‘兰’) 返回：莫乐兰切.杨兰
INSERT(str,x,y,instr)	将字符串str从第x位置开始，y个字符长的子串替换为字符串instr，返回结果	SELECT INSERT(‘AbcdeFG’,2,4,‘我的音乐我的世界’) 返回：A我的音乐我的世界EFG

 -- 连接字符串
 SELECT CONCAT('a','b','c','d');
 #返回传递给它的字符串长度
SELECT LENGTH("hello word!");
#把传递给它的字符串转换为大写
SELECT UPPER("abcdefg");
 #清除字符左边的空格
SELECT TRIM(' asdasd ');
#从字符串右边返回指定数目的字符
SELECT right('hello word!',3);
#替换一个字符串中的字符
SELECT REPLACE('hello word!','o','0');
#将字符串str从第x位置开始，y个字符长的子串替换为字符串instr，返回结果
SELECT INSERT('hello word!',3,5,'abcd');

三,日期函数

#系统时间
SELECT CURDATE();
#当前的日期和时间
SELECT NOW();
#当前的系统时间
SELECT CURTIME();
#将指定的数值添加到指定的日期部分后的日期
SELECT DATE_ADD('2020-03-22',INTERVAL 2 YEAR);
#从日期减去指定的时间间隔
SELECT DATE_SUB('2020-03-22',INTERVAL 2 MONTH);
#格式化日期 y xx Y xxxx
SELECT DATE_FORMAT('2020-03-22','%Y年%m月%d日');
SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日 %H:%i:%S');

格式	描述
%a	缩写星期名
%b	缩写月名
%c	月，数值
%D	带有英文前缀的月中的天
%d	月的天，数值(00-31)
%e	月的天，数值(0-31)
%f	微秒
%H	小时 (00-23)
%h	小时 (01-12)
%I	小时 (01-12)
%i	分钟，数值(00-59)
%j	年的天 (001-366)
%k	小时 (0-23)
%l	小时 (1-12)
%M	月名
%m	月，数值(00-12)
%p	AM 或 PM
%r	时间，12-小时（hh:mm:ss AM 或 PM）
%S	秒(00-59)
%s	秒(00-59)
%T	时间, 24-小时 (hh:mm:ss)
%U	周 (00-53) 星期日是一周的第一天
%u	周 (00-53) 星期一是一周的第一天
%V	周 (01-53) 星期日是一周的第一天，与 %X 使用
%v	周 (01-53) 星期一是一周的第一天，与 %x 使用
%W	星期名
%w	周的天 （0=星期日, 6=星期六）
%X	年，其中的星期日是周的第一天，4 位，与 %V 使用
%x	年，其中的星期一是周的第一天，4 位，与 %v 使用
%Y	年，4 位
%y	年，2 位

四,数学函数

函数名	描　　述	示　　例
RAND	返回从 0 到 1 之间的随机 float 值	SELECT RAND( ) 返回：0.79288062146374
ABS	取数值表达式的绝对值	SELECT ABS(-43) 返回：43
CEILING	取大于或等于指定数值、表达式的最小整数	SELECT CEILING(43.5) 返回：44
FLOOR	取小于或等于指定表达式的最大整数	SELECT FLOOR(43.5) 返回：43
POWER	取数值表达式的幂值	SELECT POWER(5,2) 返回：25
ROUND	将数值表达式四舍五入为指定精度	SELECT ROUND(43.543,1) 返回：43.5
SIGN	对于正数返回+1，对于负数返回-1，对于0则返回0	SELECT SIGN(-43) 返回：-1
SQRT	取浮点表达式的平方根	SELECT SQRT(9) 返回：3

#返回从 0 到 1 之间的随机 float 值 
SELECT RAND();
#取数值表达式的绝对值
SELECT ABS(-100);
#取大于或等于指定数值、表达式的最小整数
SELECT CEIL(-99.3);
#取小于或等于指定表达式的最大整数
SELECT FLOOR(99.3);
#取数值表达式的幂值
SELECT POWER(5,2);
#将数值表达式四舍五入为指定精度
SELECT ROUND(36.35);
#对于正数返回+1，对于负数返回-1，对于0则返回0
SELECT SIGN(-90);
#取浮点表达式的平方根
SELECT SQRT(9);

五,聚合函数

# 求和  求出所有菜的总价
SELECT sum(price) from menus;
# 求平均值 avg 求出所有菜的平均价格
SELECT	avg(price) from menus;
# 最大值 max 求出所有菜的最高的价格
SELECT max(price) from menus;
#最小值	min 求出所有菜的最低的价格
SELECT min(price) FROM menus;
# count 统计次数  统计有多少个菜品类型是 1(好吃不贵)
SELECT count(*) from menus where typeid=1;

六,DISTINCT关键字

消除结果集中的重复行
对表只选择其某些列时，可能会出现重复行。例如，若对XSCJ数据库的XS表只选择专业名和总学分，则出现多行重复的情况。可以使用DISTINCT或DISTINCTROW关键字消除结果集中的重复行，其格式是：
SELECT DISTINCT | DISTINCTROW column_name [ , column_name…]
其含义是对结果集中的重复行只选择一个，保证行的唯一性。
【例】 对XSCJ数据库的XS表只选择专业名和总学分，消除结果集中的重复行。
SELECT DISTINCT 专业名，总学分
FROM XS;

-- 数据重复
SELECT studentname FROM student where studentname='张三';
-- 去重
SELECT DISTINCT studentname FROM student where studentname='张三';
-- 把studentname,sex 两个都重复的数据去重
SELECT DISTINCT studentname,sex FROM student where studentname='张三';

七,替换查询

替换查询结果中的数据
在对表进行查询时，有时对所查询的某些列希望得到的是一种概念而不是具体的数据。例如查询XS表的总学分，所希望知道的是学习的总体情况，这时，就可以用等级来替换总学分的具体数字。
要替换查询结果中的数据，则要使用查询中的CASE表达式，格式为：
CASE
WHEN 条件1 THEN 表达式1
WHEN 条件2 THEN 表达式2
……
ELSE 表达式
END
【例】 查询XS表中计算机系各同学的学号、姓名和总学分，对其总学分按以下规则进行替换：若总学分为空值，替换为“尚未选课”；若总学分小于50，替换为“不及格”；若总学分在50～52之间，替换为“合格”；若总学分大于52，替换为“优秀”。列标题更改为“等级”。
SELECT学号, 姓名,
CASE
WHEN 总学分 IS NULL THEN ‘尚未选课’
WHEN 总学分 < 50 THEN ‘不及格’
WHEN 总学分 >=50 and 总学分<=52 THEN ‘合格’
ELSE ‘优秀’
END AS 等级
FROM XS
WHERE 专业名=‘计算机’;

#  小于60 不及格 60~80 及格  80~100 优秀
SELECT studentNo,studentresult ,CASE 
	WHEN studentresult>80 and studentresult<=100 THEN
		'优秀'
			WHEN studentresult>=60  THEN
		'及格'
	ELSE
		'不及格'
END as '等级'
from result;

八,分组查询

语法

SELECT …… FROM  <表名>  
WHERE  ……
GROUP BY ……

SELECT COUNT(*) AS 人数, GradeId AS 年级
FROM  Students
 GROUP BY GradeId

-- 每个年级的人数
SELECT COUNT(*),gradeid FROM student GROUP  BY gradeid; 
-- 每个年级的男生女数量

SELECT COUNT(*),gradeid,sex FROM student GROUP  BY gradeid; 
# group by 有一个原则,就是 select 后面的所有列中,没有使用聚合函数的列,
#必须出现在 group by 后面
SELECT COUNT(*),gradeid,sex FROM student GROUP  BY gradeid,sex; 

注意

SELECT列表中只能包含：
1、被分组的列
2、为每个分组返回一个值的表达式，如聚合函数

如何查询每门课程的平均分？

-- 如何查询每门课程的平均分？
SELECT   subjectNo,avg(studentresult) '平均分' FROM result GROUP BY subjectNo;

查询每门课程的平均分，并且按照分数由低到高的顺序排列显示

-- 查询每门课程的平均分，并且按照分数由低到高的顺序排列显示 
-- 排序要放在分组后边,否则会报错
SELECT   subjectNo,avg(studentresult) '平均分' FROM result GROUP BY subjectNo ORDER BY avg(studentresult);

有限定条件的查询

-- 分组筛选
-- 如何获得总人数超过15人的年级？
-- 聚合函数在where中无法使用
SELECT COUNT(*),gradeid '年级' FROM student where count(*)>3 GROUP  BY gradeid ; 
-- having 作用是对已经分组的数据进行筛选
SELECT COUNT(*),gradeid '年级' FROM student GROUP  BY gradeid HAVING count(*)>3; 
-- 获得男生人数超过15人的年级
SELECT COUNT(*),gradeid '年级' FROM student where sex='男' GROUP  BY gradeid HAVING count(*)>2;

WHERE与HAVING对比

WHERE子句：
用来筛选 FROM 子句中指定的操作所产生的行
HAVING子句：
用来从分组的结果中筛选行