MySQL数据库学习日志(二):SQL语言进阶之DQL高级
DQL数据查询语言
(一)排序
通过ORDER BY子句,可以将查询出的结果进行排序,排序只影响显示结果,不会影响数据库中数据的顺序。
语法
SELECT 字段名 FROM 表名 [WHERE 条件] ORDER BY 字段名 [ASC|DESC];
ASC: 升序,默认是升序
DESC: 降序
1. 单列排序
单列排序就是使用一个字段排序。
示例
# 查询所有数据,使用年龄降序排序:
SELECT * FROM student3 ORDER BY age DESC;
2. 组合排序
语法
SELECT 字段名 FROM 表名 [WHERE 条件] ORDER BY 字段名1 [ASC|DESC],字段名2 [ASC|DESC];
示例
# 查询所有数据,在年龄降序排序的基础上,如果年龄相同再以数学成绩降序排序:
SELECT * FROM student3 ORDER BY age DESC, math DESC;
(二)单行函数
单行函数是指对于每一行数据进行计算后得到一行输出结果。SQL单行函数根据数据类型分为字符函数、数字函数、日期函数、转换函数等。
1. 数值函数
(1)abs(x)
返回 x 的绝对值
示例
SELECT ABS(-1) -- 返回1
(2)ceil(x)
返回大于或等于 x 的最小整数(向上取整)
示例
SELECT CEIL(1.5) -- 返回2
(3)floor(x)
返回小于或等于 x 的最大整数(向下取整)
示例
SELECT FLOOR(1.5) -- 返回1
(4)rand()
返回 0 到 1 的随机数
示例
SELECT RAND() --0.93099315644334
(5)round(x)
返回离 x 最近的整数(四舍五入取整)
示例
SELECT ROUND(1.23456) --返回1
2. 字符串函数
(1)concat(s1,s2…sn)
字符串 s1,s2 等多个字符串合并为一个字符串
示例
SELECT CONCAT("SQL ", "Runoob ", "Google ", "Facebook") AS ConcatenatedString;
--返回 SQLRunoobGooogleFacebook
(2)locate(s1,s)
从字符串s中获取s1的开始位置
示例
SELECT LOCATE('st','myteststring'); -- 5
(3)lower (s)
将字符串 s 的所有字母变成小写字母
示例
SELECT LOWER('RUNOOB'); -- runoob
(4)upper(s)
将字符串转换为大写
示例
SELECT UPPER("runoob"); -- RUNOOB
(5)replace(s,s1,s2)
将字符串 s2 替代字符串 s 中的字符串 s1
示例
SELECT REPLACE('abc','a','x'); --xbc
(6)substr(s, start, length)
从字符串 s 的 start 位置截取长度为 length 的子字符串
示例
SELECT SUBSTRING("RUNOOB", 2, 3) AS ExtractString; -- UNO
(7)trim(s)
去掉字符串 s 开始和结尾处的空格和制表符
示例
SELECT TRIM(' RUNOOB ') AS TrimmedString; --RUNOOB
(8)reverse(s)
反转字符串
示例
SELECT reverse(' dog'); -- 返回god
3. 时间日期函数
(1)now()
返回系统当前时间(包括年月日时分秒)
示例
SELECT now()
(2)sysdate()
返回系统当前时间(包括年月日时分秒)
示例
SELECT sysdate()
(3)curdate()
返回当前日期(年月日)
示例
select curdate()
(4)curtime()
返回当前时间(时分秒)
示例
select curtime()
(5)month(date)
返回参数日期的月份值
示例
select MONTH('2020-11-12')
(6)week(date)
返回参数日期是一年中的第几个星期
示例
select week('2020-11-12')
(7)day(date)
返回参数日期的日值
示例
select day('2020-11-12')
(8)date_add(date,INTERVAL expr type)
在参数日期上增加相应的时间
date 参数是合法的日期表达式。expr 参数是您希望添加的时间间隔。
type 参数可以是下列值:
示例
4. 流程控制函数
(1)if(condition,expr1,expr2)
condition条件为true时返回expr1。condition条件为false时,返回expr2。
示例
(2)ifnull(expr1,expr2)
expr1不为null时返回expr1。expr1为null时返回expr2。
示例
用此函数解决前面null运算结果为null的问题
解决方式
因为english有null值,所以用ifnull函数处理english
select name,math+ifnull(english,0) as total from student3
5. 其它函数
(1)version()
查看数据库版本
(2)user()
查看数据库当前用户
(3)md5(expr)
返回参数md5加密后的值
(三)聚合函数
聚合函数查询是纵向查询,它是对一列的值进行计算,然后返回一个结果值。
聚合函数会忽略空值。
五个聚合函数:
count: 统计指定列记录数,记录为NULL的不统计
sum: 计算指定列的数值和,如果不是数值类型,那么计算结果为0
max: 计算指定列的最大值
min: 计算指定列的最小值
avg: 计算指定列的平均值,如果不是数值类型,那么计算结果为0
1. count函数
统计指定列记录数
示例1
# 查询总条数
SELECT COUNT(*) FROM student3;
示例2
# 查询年龄大于40的总人数
SELECT COUNT(*) FROM student3 WHERE age>40;
说明:先查询后进行聚合函数统计
示例3
说明:忽略了null值
2. sum函数
计算指定列的数值和,如果不是数值类型,那么计算结果为0
示例
# 计算math的总和
SELECT SUM(math) FROM student3;
3. avg函数
计算指定列的平均值,如果不是数值类型,那么计算结果为0
示例
# 查询数学成绩平均分
SELECT AVG(math) FROM student3;
4. max函数
计算指定列的最大值
示例
# 查询数学成绩最高分
SELECT MAX(math) FROM student3;
5. min函数
计算指定列的最小值
示例
# 查询数学成绩最低分
SELECT MIN(math) FROM student3;
(四)分组
分组查询是指使用 GROUP BY语句对查询信息进行分组,相同数据作为一组。
语法
SELECT 字段1,字段2... FROM 表名 GROUP BY 分组字段 [HAVING 条件];
1. 分组查询
GROUP BY怎么分组的?将分组字段结果中相同内容作为一组
示例1
SELECT * FROM student3 GROUP BY sex;
# 这句话会将sex相同的数据作为一组。
结果:
发现:返回每组的第一条数据,作为查询结果。
说明
以上的操作是将分组字段结果中相同内容作为一组,并且返回每组的第一条数据。这样的操作显然没有什么意义。而且在有些数据库中(例如oracle)中,这样的语句是错误的!
分组的目的就是为了统计,一般分组会跟聚合函数一起使用。
示例2
# 查询男女各多少人
SELECT sex, COUNT(*) FROM student3 GROUP BY sex;
SELECT SUM(math), sex FROM student3 GROUP BY sex;
# 是将每组的math进行求和,返回每组统计的结果。
聚合函数是在分组后运行,统计每组的数据。
注意事项
当我们使用某个字段分组,在查询的时候最好将这个字段查询出来,否则看不到数据属于哪组的。
2. 分组时条件过滤
示例1
/*查询年龄大于25岁的人,按性别分组,统计每组的人数
先过滤掉年龄小于25岁的人;再分组;最后统计每组的人数*/
SELECT sex, COUNT(*) FROM student3 WHERE age > 25 GROUP BY sex;
示例2
# 查询年龄大于25岁的人,按性别分组,统计每组的人数,并只显示性别人数大于2的数据。
# 错误示范
SELECT sex, COUNT(*) FROM student3 WHERE age > 25 GROUP BY sex WHERE COUNT(*) >2;
一个SQL语句中不应该出现两个where子句!
使用having子句解决
SELECT sex, COUNT(*) FROM student3 WHERE age > 25 GROUP BY sex HAVING COUNT(*) >2;
# 只有分组后人数大于2的`男`这组数据显示了出来。
3. where和having的对比
-
having是在分组后对数据进行过滤,where是在分组前对数据进行过滤
-
having语句中可以使用聚合函数,where后面不可以使用聚合函数
(五)limit
LIMIT是限制的意思,所以LIMIT的作用就是限制查询记录的条数。
准备数据
INSERT INTO student3(id,NAME,age,sex,address,math,english) VALUES
(9,'唐僧',25,'男','长安',87,78),
(10,'孙悟空',18,'男','花果山',100,66),
(11,'猪八戒',22,'男','高老庄',58,78),
(12,'沙僧',50,'男','流沙河',77,88),
(13,'白骨精',22,'女','白虎岭',66,66),
(14,'蜘蛛精',23,'女','盘丝洞',88,88);
语法
SELECT *|字段列表 [as 别名] FROM 表名 [WHERE子句] [GROUP BY子句][HAVING子句][ORDER BY子句][LIMIT子句];
注意:limit子句要放在SQL语句的最后
limit语法格式
LIMIT offset,length;或者limit length;
offset是指偏移量,可以认为是跳过的记录数量,默认为0,表示从哪开始
length是指需要显示的总记录数,表示显示几条
示例
# 查询学生表中数据,从第三条开始显示,显示6条。
SELECT * FROM student3 LIMIT 2,6;
limit使用场景
分页。比如我们登录京东、淘宝,返回的商品信息可能有几万条,不是一次全部显示出来,是一页显示固定的条数。 假设我们以每页显示5条记录的方式来分页。
SQL语句如下
-- 每页显示5条
-- 第一页: LIMIT 0,5; 跳过0条,显示5条
-- 第二页: LIMIT 5,5; 跳过5条,显示5条
-- 第三页: LIMIT 10,5; 跳过10条,显示5条
SELECT * FROM student3 LIMIT 0,5;
SELECT * FROM student3 LIMIT 5,5;
SELECT * FROM student3 LIMIT 10,5;
注意
-
如果第一个参数是0可以简写:
SELECT * FROM student3 LIMIT 0,5;
SELECT * FROM student3 LIMIT 5;
-
LIMIT 10,5; – 如果不够5条,则有多少显示多少。
(六)SELECT语句总结
1. 书写顺序
SELECT 字段 FROM 表名 WHERE 条件 GROUP BY 字段 HAVING 条件 ORDER BY 字段 LIMIT offset,length;
其中,SELECT字段,FROM表名为 必写,其余均可根据情况而定。
2. 执行顺序
1、from表名
2、where条件
3、group by 字段
4、having 条件
5、select 字段
6、order by 字段
7、limit
end
被人民日报一段话鼓励到,如果自己不够强大就把自己暂时隐藏起来,真正的强者不是不流泪的人,而是含着眼泪奔跑的人。人生沟沟壑壑,身处低谷时不抱怨、不放弃,能铿锵前行,那么往后余生就都是向上的路。