第六章 SQL查询二

一,MySQL 中的函数

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

二,字符串

 -- 连接字符串 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'); 

四,数学函数

#返回从 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子句：
用来从分组的结果中筛选行