由于篇幅问题,查询素材,完整语法,基础查询,字段别名,去重,where子句查询和模糊查询等放在了上一篇。
点击直达:MySQL基础回顾(六):数据查询语言DQL—select查询语句总结(篇一)
6.联表查询
开始之前先来探讨一个问题:
在上面素材中student表里没有学生成绩,而result表里没有学生姓名。此时如果我们想一次查询出参加了考试的同学的:学号,姓名,科目编号,分数,怎么办呢?
显然,在一张表里是不可能完成操作的,此时我们就要使用join关键字进行联表查询。
内联
内连接使用 inner join …on,这是一个具体的语法,搭配where时时等值查询。
具体怎么做呢?可以分为下面几步:
- 1.分析需求,即分析要查询的字段来自于哪张表,如果超过了两张表就需要用到连接查询了;
- 2.确定使用哪种连接查询?有7种连接查询,(这里只介绍inner join,left join和right join);
- 3.确定交叉点(即这两个表中哪个数据是相同的),比如result表和student表中
StudentNo
是相同的。即学生表中的StudentNo
= 成绩表中的StudentNo
。
mysql> SELECT s.`StudentNo`,StudentName,`SubjectNo`,`StudentResult` --第一步确定要查询的字段
-> FROM student AS s -- 第二步确定从那张表开始查
-> INNER JOIN result AS r -- 要连接的表
-> WHERE s.StudentNo = r.StudentNo; -- 第三步判断叫交叉点,即等值条件
+-----------+--------------+-----------+---------------+
| StudentNo | StudentName | SubjectNo | StudentResult |
+-----------+--------------+-----------+---------------+
| 1000 | 郭靖 | 1 | 94 |
| 1000 | 郭靖 | 2 | 75 |
| 1000 | 郭靖 | 3 | 76 |
| 1000 | 郭靖 | 4 | 93 |
| 1000 | 郭靖 | 5 | 97 |
| 1000 | 郭靖 | 6 | 87 |
| 1000 | 郭靖 | 7 | 79 |
| 1000 | 郭靖 | 8 | 74 |
| 1000 | 郭靖 | 9 | 69 |
| 1000 | 郭靖 | 10 | 78 |
| 1000 | 郭靖 | 11 | 66 |
| 1000 | 郭靖 | 12 | 82 |
| 1000 | 郭靖 | 13 | 94 |
| 1000 | 郭靖 | 14 | 98 |
| 1000 | 郭靖 | 15 | 70 |
| 1000 | 郭靖 | 16 | 74 |
| 1001 | 李文才 | 1 | 76 |
| 1001 | 李文才 | 2 | 93 |
| 1001 | 李文才 | 3 | 65 |
| 1001 | 李文才 | 4 | 71 |
| 1001 | 李文才 | 5 | 98 |
| 1001 | 李文才 | 6 | 74 |
| 1001 | 李文才 | 7 | 85 |
| 1001 | 李文才 | 8 | 69 |
| 1001 | 李文才 | 9 | 63 |
| 1001 | 李文才 | 10 | 70 |
| 1001 | 李文才 | 11 | 62 |
| 1001 | 李文才 | 12 | 90 |
| 1001 | 李文才 | 13 | 97 |
| 1001 | 李文才 | 14 | 89 |
| 1001 | 李文才 | 15 | 72 |
| 1001 | 李文才 | 16 | 90 |
| 1002 | 李斯文 | 1 | 61 |
| 1002 | 李斯文 | 2 | 80 |
| 1002 | 李斯文 | 3 | 89 |
| 1002 | 李斯文 | 4 | 88 |
| 1002 | 李斯文 | 5 | 82 |
| 1002 | 李斯文 | 6 | 91 |
| 1002 | 李斯文 | 7 | 63 |
| 1002 | 李斯文 | 8 | 84 |
| 1002 | 李斯文 | 9 | 60 |
| 1002 | 李斯文 | 10 | 71 |
| 1002 | 李斯文 | 11 | 93 |
| 1002 | 李斯文 | 12 | 96 |
| 1002 | 李斯文 | 13 | 83 |
| 1002 | 李斯文 | 14 | 69 |
| 1002 | 李斯文 | 15 | 89 |
| 1002 | 李斯文 | 16 | 83 |
| 1003 | 武松 | 1 | 91 |
| 1003 | 武松 | 2 | 75 |
| 1003 | 武松 | 3 | 65 |
| 1003 | 武松 | 4 | 63 |
| 1003 | 武松 | 5 | 90 |
| 1003 | 武松 | 6 | 96 |
| 1003 | 武松 | 7 | 97 |
| 1003 | 武松 | 8 | 77 |
| 1003 | 武松 | 9 | 62 |
| 1003 | 武松 | 10 | 81 |
| 1003 | 武松 | 11 | 76 |
| 1003 | 武松 | 12 | 61 |
| 1003 | 武松 | 13 | 93 |
| 1003 | 武松 | 14 | 79 |
| 1003 | 武松 | 15 | 78 |
| 1003 | 武松 | 16 | 96 |
| 1004 | 张三 | 1 | 84 |
| 1004 | 张三 | 2 | 79 |
| 1004 | 张三 | 3 | 76 |
| 1004 | 张三 | 4 | 78 |
| 1004 | 张三 | 5 | 81 |
| 1004 | 张三 | 6 | 90 |
| 1004 | 张三 | 7 | 63 |
| 1004 | 张三 | 8 | 89 |
| 1004 | 张三 | 9 | 67 |
| 1004 | 张三 | 10 | 100 |
| 1004 | 张三 | 11 | 94 |
| 1004 | 张三 | 12 | 65 |
| 1004 | 张三 | 13 | 86 |
| 1004 | 张三 | 14 | 77 |
| 1004 | 张三 | 15 | 82 |
| 1004 | 张三 | 16 | 87 |
| 1005 | 张秋丽 | 1 | 82 |
| 1005 | 张秋丽 | 2 | 92 |
| 1005 | 张秋丽 | 3 | 80 |
| 1005 | 张秋丽 | 4 | 92 |
| 1005 | 张秋丽 | 5 | 97 |
| 1005 | 张秋丽 | 6 | 72 |
| 1005 | 张秋丽 | 7 | 84 |
| 1005 | 张秋丽 | 8 | 79 |
| 1005 | 张秋丽 | 9 | 76 |
| 1005 | 张秋丽 | 10 | 87 |
| 1005 | 张秋丽 | 11 | 65 |
| 1005 | 张秋丽 | 12 | 67 |
| 1005 | 张秋丽 | 13 | 63 |
| 1005 | 张秋丽 | 14 | 64 |
| 1005 | 张秋丽 | 15 | 99 |
| 1005 | 张秋丽 | 16 | 97 |
| 1006 | 肖梅 | 1 | 82 |
| 1006 | 肖梅 | 2 | 73 |
| 1006 | 肖梅 | 3 | 79 |
| 1006 | 肖梅 | 4 | 63 |
| 1006 | 肖梅 | 5 | 97 |
| 1006 | 肖梅 | 6 | 83 |
| 1006 | 肖梅 | 7 | 78 |
| 1006 | 肖梅 | 8 | 88 |
| 1006 | 肖梅 | 9 | 89 |
| 1006 | 肖梅 | 10 | 82 |
| 1006 | 肖梅 | 11 | 70 |
| 1006 | 肖梅 | 12 | 69 |
| 1006 | 肖梅 | 13 | 64 |
| 1006 | 肖梅 | 14 | 80 |
| 1006 | 肖梅 | 15 | 90 |
| 1006 | 肖梅 | 16 | 85 |
| 1007 | 欧阳峻峰 | 1 | 87 |
| 1007 | 欧阳峻峰 | 2 | 63 |
| 1007 | 欧阳峻峰 | 3 | 70 |
| 1007 | 欧阳峻峰 | 4 | 74 |
| 1007 | 欧阳峻峰 | 5 | 79 |
| 1007 | 欧阳峻峰 | 6 | 83 |
| 1007 | 欧阳峻峰 | 7 | 86 |
| 1007 | 欧阳峻峰 | 8 | 76 |
| 1007 | 欧阳峻峰 | 9 | 65 |
| 1007 | 欧阳峻峰 | 10 | 87 |
| 1007 | 欧阳峻峰 | 11 | 69 |
| 1007 | 欧阳峻峰 | 12 | 69 |
| 1007 | 欧阳峻峰 | 13 | 90 |
| 1007 | 欧阳峻峰 | 14 | 84 |
| 1007 | 欧阳峻峰 | 15 | 95 |
| 1007 | 欧阳峻峰 | 16 | 92 |
| 1008 | 梅超风 | 1 | 96 |
| 1008 | 梅超风 | 2 | 62 |
| 1008 | 梅超风 | 3 | 97 |
| 1008 | 梅超风 | 4 | 84 |
| 1008 | 梅超风 | 5 | 86 |
| 1008 | 梅超风 | 6 | 72 |
| 1008 | 梅超风 | 7 | 67 |
| 1008 | 梅超风 | 8 | 83 |
| 1008 | 梅超风 | 9 | 86 |
| 1008 | 梅超风 | 10 | 60 |
| 1008 | 梅超风 | 11 | 61 |
| 1008 | 梅超风 | 12 | 68 |
| 1008 | 梅超风 | 13 | 99 |
| 1008 | 梅超风 | 14 | 77 |
| 1008 | 梅超风 | 15 | 73 |
| 1008 | 梅超风 | 16 | 78 |
+-----------+--------------+-----------+---------------+
值得注意的一点是,上述查询中我们给两个表都起了一个别名,为什么呢?
因为两张表里都有StudentNo
这个字段,虽然都是一样的,但是如果不人为规定具体去查哪张表里的StudentNo
字段,就会产生歧义,机器就会不知所措,然后就会报错。
左联
左联使用left join…on
还是使用上面分析的步骤一步一步来查:
SELECT s.`StudentNo`,StudentName,`SubjectNo`,`StudentResult` -- 第一步:确定要查询的字段
FROM student AS s -- 第二步:确定从哪张表开始查
LEFT JOIN result AS r -- 确定左联的表
ON s.StudentNo = r.StudentNo; -- 第三步判断两表交叉点,即连接条件
查询结果和内联是一样的,就不贴了。
右联
右联使用right join…on
步骤一样:
SELECT s.`StudentNo`,StudentName,`SubjectNo`,`StudentResult` -- 第一步:确定要查询的字段
FROM student AS s -- 第二步:确定从哪张表开始查
RIGHT JOIN result AS r -- 确定右联的表
ON s.StudentNo = r.StudentNo; -- 第三步判断两表交叉点,即连接条件
结果也是和内联一样。
区别
在上面示例中左联和右联查出来的结果一样,那么左联和右联有什区别呢?
回答这个问题之前我们先来做一个事情,往student表里插入一条数据,学生姓名为tom,而result表里里不添加,再来看看左联和右联查出来的结果有什么区别?
左联:
mysql> SELECT s.`StudentNo`,StudentName,`SubjectNo`,`StudentResult`
-> FROM student AS s
-> LEFT JOIN result AS r
-> ON s.StudentNo = r.StudentNo;
+-----------+--------------+-----------+---------------+
| StudentNo | StudentName | SubjectNo | StudentResult |
+-----------+--------------+-----------+---------------+
| 1000 | 郭靖 | 1 | 94 |
...
...中间的数据省略
...
| 1008 | 梅超风 | 16 | 78 |
| 1009 | Tom | NULL | NULL |
+-----------+--------------+-----------+---------------+
右联:
mysql> SELECT s.`StudentNo`,StudentName,`SubjectNo`,`StudentResult`
-> FROM student AS s
-> RIGHT JOIN result AS r
-> ON s.StudentNo = r.StudentNo;
+-----------+--------------+-----------+---------------+
| StudentNo | StudentName | SubjectNo | StudentResult |
+-----------+--------------+-----------+---------------+
| 1000 | 郭靖 | 1 | 94 |
...
...
...
| 1008 | 梅超风 | 16 | 78 |
+-----------+--------------+-----------+---------------+
这样一比很容易就看出了查询结果的区别:
从student左联result表查询出来后,查询结果里有tom,只是科目编号和成绩为null;
但是通过右联查询,在结果中便没有tom这个人。
所以很容易得出结论:
- 左联会从左表中返回所有值,即使在右表中没有匹配;
- 右联会从右表中返回所有值,即使在左表中没有匹配。
小结
操作 描述
inner join 如果表中至少有一个匹配,就返回行。
left join 会从左表中返回所有值,即使在右表中没有匹配
right join 会从右表中返回所有值,即使在左表中没有匹配
拓展
除了上述所说的几种连接,还有自连接,和多表联表查询。
这两种方式,思想步骤和上面说的差不多。
对于自连接,记住一点就行:所谓自连接就是自己的表和自己的表连接,核心就是一张表拆为两张一模一样的表即可。
对于多表联表查询这里可以提供一个例子。
要求:查询参加了数据库结构-1考试的同学的信息:学号,学生姓名,科目名,分数
分析:学生姓名只有student表里有,分数只有result表里有,科目名只有subject表里有。
所以至少需要三张表来完成查询,接下来只用它们的交叉点或者说等值条件就可以了。
SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult` -- 要查询的字段,如果两张表中都有的字段,则要规定具体是哪张表的
FROM `student` AS s -- 第一步:确定从哪张表中开始查
INNER JOIN `result` AS r -- 第二步:确定要连接的第一张表
ON s.`StudentNo`=r.`StudentNo` -- 判断第前两张表的连接条件
INNER JOIN `subject` AS sub -- 第三步:确定要连接的第二张表
ON r.`SubjectNo`=sub.`SubjectNo` -- 判断后两张表等值条件
WHERE `SubjectName`='数据库结构-1' -- 加上约束约束条件
+-----------+--------------+-------------------+---------------+
| StudentNo | StudentName | SubjectName | StudentResult |
+-----------+--------------+-------------------+---------------+
| 1000 | 郭靖 | 数据库结构-1 | 94 |
| 1001 | 李文才 | 数据库结构-1 | 97 |
| 1002 | 李斯文 | 数据库结构-1 | 83 |
| 1003 | 武松 | 数据库结构-1 | 93 |
| 1004 | 张三 | 数据库结构-1 | 86 |
| 1005 | 张秋丽 | 数据库结构-1 | 63 |
| 1006 | 肖梅 | 数据库结构-1 | 64 |
| 1007 | 欧阳峻峰 | 数据库结构-1 | 90 |
| 1008 | 梅超风 | 数据库结构-1 | 99 |
| 1009 | 刘毅 | 数据库结构-1 | 91 |
| 1010 | 大凡 | 数据库结构-1 | 67 |
| 1011 | 奥丹斯 | 数据库结构-1 | 66 |
| 1012 | 多伦 | 数据库结构-1 | 91 |
| 1013 | 李梅 | 数据库结构-1 | 98 |
| 1014 | 张得 | 数据库结构-1 | 89 |
| 1015 | 李东方 | 数据库结构-1 | 63 |
| 1016 | 刘奋斗 | 数据库结构-1 | 62 |
| 1017 | 可可 | 数据库结构-1 | 85 |
+-----------+--------------+-------------------+---------------+
18 rows in set (0.02 sec)
7.分页查询和排序
分页查询使用limit关键字
排序使用order by关键字
①先来看看排序
排序分为升序和降序
升序使用ASC关键字
降序使用DESC关键字
语法:
select 字段列表 from 表名 order by 字段 [asc|desc];
就拿上面多表查询最后的例子举例,上面查出了查询参加了数据库结构-1考试的同学的信息:学号,学生姓名,科目名,分数。这时我们要让查询结果按分数降序排列,怎么做呢?
mysql> SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
-> FROM `student` AS s
-> INNER JOIN `result` AS r
-> ON s.`StudentNo`=r.`StudentNo`
-> INNER JOIN `subject` AS sub
-> ON r.`SubjectNo`=sub.`SubjectNo`
-> WHERE `SubjectName`='数据库结构-1'
-> ORDER BY `StudentResult` DESC ; -- desc降序排列
+-----------+--------------+-------------------+---------------+
| StudentNo | StudentName | SubjectName | StudentResult |
+-----------+--------------+-------------------+---------------+
| 1008 | 梅超风 | 数据库结构-1 | 99 |
| 1013 | 李梅 | 数据库结构-1 | 98 |
| 1001 | 李文才 | 数据库结构-1 | 97 |
| 1000 | 郭靖 | 数据库结构-1 | 94 |
| 1003 | 武松 | 数据库结构-1 | 93 |
| 1012 | 多伦 | 数据库结构-1 | 91 |
| 1009 | 刘毅 | 数据库结构-1 | 91 |
| 1007 | 欧阳峻峰 | 数据库结构-1 | 90 |
| 1014 | 张得 | 数据库结构-1 | 89 |
| 1004 | 张三 | 数据库结构-1 | 86 |
| 1017 | 可可 | 数据库结构-1 | 85 |
| 1002 | 李斯文 | 数据库结构-1 | 83 |
| 1010 | 大凡 | 数据库结构-1 | 67 |
| 1011 | 奥丹斯 | 数据库结构-1 | 66 |
| 1006 | 肖梅 | 数据库结构-1 | 64 |
| 1015 | 李东方 | 数据库结构-1 | 63 |
| 1005 | 张秋丽 | 数据库结构-1 | 63 |
| 1016 | 刘奋斗 | 数据库结构-1 | 62 |
+-----------+--------------+-------------------+---------------+
18 rows in set (0.00 sec)
上面使用desc是让查询结果按照分数降序排列,如果想按照升序排列,把desc换成asc就可以了。
②分页
分页使用limit关键字。
使用分页一个重要的原因就是为了缓解数据库压力,给用户更好的体验。
语法:
select 字段列表 from 表名 limit [offset,] count;
- offset是起始位置,offset从0开始,可以看成每条记录的索引号。
- count是数量,也就是一页显示的数据数量。
同样使用上面成绩的例子,给其分页,比如从第二个同学成绩开始(由于下标从零开始,所以第二个人的下标就是1),查询五个人的。
mysql> SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
-> FROM `student` AS s
-> INNER JOIN `result` AS r
-> ON s.`StudentNo`=r.`StudentNo`
-> INNER JOIN `subject` AS sub
-> ON r.`SubjectNo`=sub.`SubjectNo`
-> WHERE `SubjectName`='数据库结构-1'
-> ORDER BY `StudentResult` DESC
-> LIMIT 1,5 ; -- 一号下标代表从第二个人开始,一页查五个。
+-----------+-------------+-------------------+---------------+
| StudentNo | StudentName | SubjectName | StudentResult |
+-----------+-------------+-------------------+---------------+
| 1013 | 李梅 | 数据库结构-1 | 98 |
| 1001 | 李文才 | 数据库结构-1 | 97 |
| 1000 | 郭靖 | 数据库结构-1 | 94 |
| 1003 | 武松 | 数据库结构-1 | 93 |
| 1012 | 多伦 | 数据库结构-1 | 91 |
+-----------+-------------+-------------------+---------------+
5 rows in set (0.00 sec)
有一个小问题,上一篇完整语法处提到过,limit分页操作,必须排在所有查询语句的最后一个。
8.子查询和嵌套查询
之前使用where,where后面跟的要么是固定的值,要么是固定的条件。
而子查询则是在where语句中嵌套一个子查询语句。
例如:
where(select * from )
举个栗子:
比如我们要查询数据库结构-1的所有考试结果(包括学号,科目名,成绩),并降序排列。
- 第一种方法:使用上面学到的联表查询
mysql> SELECT `StudentNo`,`SubjectName`,`StudentResult` -- 查询字段
-> FROM `result` AS r -- 查询开始的表
-> INNER JOIN `subject` sub -- 连接的表
-> ON r.`SubjectNo`=sub.`SubjectNo` -- 连接条件
-> WHERE `SubjectName`='数据库结构-1' -- 约束条件
-> ORDER BY `StudentResult` DESC; -- 排序
+-----------+-------------------+---------------+
| StudentNo | SubjectName | StudentResult |
+-----------+-------------------+---------------+
| 1008 | 数据库结构-1 | 99 |
| 1013 | 数据库结构-1 | 98 |
| 1001 | 数据库结构-1 | 97 |
| 1000 | 数据库结构-1 | 94 |
| 1003 | 数据库结构-1 | 93 |
| 1012 | 数据库结构-1 | 91 |
| 1009 | 数据库结构-1 | 91 |
| 1007 | 数据库结构-1 | 90 |
| 1014 | 数据库结构-1 | 89 |
| 1004 | 数据库结构-1 | 86 |
| 1017 | 数据库结构-1 | 85 |
| 1002 | 数据库结构-1 | 83 |
| 1010 | 数据库结构-1 | 67 |
| 1011 | 数据库结构-1 | 66 |
| 1006 | 数据库结构-1 | 64 |
| 1005 | 数据库结构-1 | 63 |
| 1015 | 数据库结构-1 | 63 |
| 1016 | 数据库结构-1 | 62 |
+-----------+-------------------+---------------+
18 rows in set (0.00 sec)
- 第二种方法:使用子查询
mysql> SELECT `StudentNo`,`SubjectNo`,`StudentResult`
-> FROM `result`
-> WHERE `SubjectNo` = (SELECT `SubjectNo` FROM `subject` WHERE `SubjectName`='数据库结构-1')
-> ORDER BY `StudentResult` DESC;
+-----------+-----------+---------------+
| StudentNo | SubjectNo | StudentResult |
+-----------+-----------+---------------+
| 1008 | 13 | 99 |
| 1013 | 13 | 98 |
| 1001 | 13 | 97 |
| 1000 | 13 | 94 |
| 1003 | 13 | 93 |
| 1009 | 13 | 91 |
| 1012 | 13 | 91 |
| 1007 | 13 | 90 |
| 1014 | 13 | 89 |
| 1004 | 13 | 86 |
| 1017 | 13 | 85 |
| 1002 | 13 | 83 |
| 1010 | 13 | 67 |
| 1011 | 13 | 66 |
| 1006 | 13 | 64 |
| 1005 | 13 | 63 |
| 1015 | 13 | 63 |
| 1016 | 13 | 62 |
+-----------+-----------+---------------+
18 rows in set (0.00 sec)
可以看到查询结果是一样的,但是相比之下,还是联表查询逻辑更好懂,也更直白一点。
9.分组查询和having语句
①分组查询
分组查询使用group by这两个关键字。
语法:
select 字段列表 from 表名 group by 字段;
实际上,group by 平常主要用来统计(使用情景很多,比如统计上面例子的各科的总分数,平均分上面的),所以一般会配合一些统计函数来使用。简单的来看一些聚合函数:
函数名称 描述
count() 计数,查询一个表中有多少个记录
sum() 求和
avg() 平均值
max() 最大值
min() 最小值
②having语句
having功能与where类似,不过having的条件判断发生在数据在内存中时,所以可以使用在内存中才发生的数据,如“分组”,“字段别名”(前面提到过)等。
语法:
select 字段列表 from 表名 having 条件;
③搭配使用
group by搭配havaing语句往往可以做更多的事情。
举个栗子:
现在我们要查询不同课程的平均分,最高分和最低分。并且只查询平均分在80分以上的课程,怎么查呢?
核心就是根据不同的课程分组,然后用having语句进行二次筛选。
mysql> SELECT subjectname,AVG(studentresult) AS 平均分,MAX(StudentResult) AS 最高分,MIN(StudentResult) AS 最低分
-> FROM result AS r
-> INNER JOIN `subject` AS s
-> ON r.subjectno = s.subjectno
-> GROUP BY r.subjectno -- 使用group by通过subjectno排序
-> HAVING 平均分>80; -- 用having进行二次筛选,剔除平均分在八十分以下的课程
+-------------------+-----------+-----------+-----------+
| subjectname | 平均分 | 最高分 | 最低分 |
+-------------------+-----------+-----------+-----------+
| 高等数学-1 | 82.4444 | 100 | 61 |
| 高等数学-3 | 80.3889 | 97 | 60 |
| C语言-1 | 84.6111 | 98 | 65 |
| C语言-2 | 84.0556 | 97 | 64 |
| C语言-4 | 81.0556 | 97 | 66 |
| JAVA第二学年 | 81.5556 | 100 | 60 |
| 数据库结构-1 | 82.2778 | 99 | 62 |
| 数据库结构-2 | 80.1667 | 99 | 64 |
| 数据库结构-3 | 81.5000 | 99 | 65 |
| 数据库结构-4 | 81.7222 | 97 | 65 |
+-------------------+-----------+-----------+-----------+
10 rows in set (0.02 sec)
10.select与数据库表达式
上面提到了聚合函数,其实select还能配合很多MySQL中的函数使用。
- 数学运算函数
1.绝对值查询
mysql> SELECT ABS(-8);
+---------+
| ABS(-8) |
+---------+
| 8 |
+---------+
1 row in set (0.00 sec)
2.向上取整
mysql> SELECT CEILING(9.2);
+--------------+
| CEILING(9.2) |
+--------------+
| 10 |
+--------------+
1 row in set (0.01 sec)
3.向下取整
mysql> SELECT FLOOR(9.7);
+------------+
| FLOOR(9.7) |
+------------+
| 9 |
+------------+
1 row in set (0.00 sec)
4.返回一个0到1之间的一个随机数
mysql> SELECT RAND();
+---------------------+
| RAND() |
+---------------------+
| 0.30160361742750147 |
+---------------------+
1 row in set (0.00 sec)
5.返回参数的符号
mysql> SELECT SIGN(-8); -- 负数返回-1,正数返回1,0返回0
+----------+
| SIGN(-8) |
+----------+
| -1 |
+----------+
1 row in set (0.00 sec)
- 字符串函数
1.返回字符串的长度
mysql> SELECT CHAR_LENGTH('12');
+-------------------+
| CHAR_LENGTH('12') |
+-------------------+
| 2 |
+-------------------+
1 row in set (0.00 sec)
2.拼接字符串
mysql> SELECT CONCAT('我','爱','Java');
+----------------------------+
| CONCAT('我','爱','Java') |
+----------------------------+
| 我爱Java |
+----------------------------+
1 row in set (0.00 sec)
3.替换字符串函数
mysql> SELECT REPLACE('失败是成功之母','失败','努力') ;
+----------------------------------------------------+
| REPLACE('失败是成功之母','失败','努力') |
+----------------------------------------------------+
| 努力是成功之母 |
+----------------------------------------------------+
1 row in set (0.00 sec)
4.字符串转为小写
mysql> SELECT LOWER('HellO');
+----------------+
| LOWER('HellO') |
+----------------+
| hello |
+----------------+
1 row in set (0.00 sec)
5.字符串转为大写
mysql> SELECT UPPER('HellO');
+----------------+
| UPPER('HellO') |
+----------------+
| HELLO |
+----------------+
1 row in set (0.00 sec)
6.返回子串第一次出现时的索引,从索引1开始。
mysql> SELECT INSTR('helpo','l');
+--------------------+
| INSTR('helpo','l') |
+--------------------+
| 3 |
+--------------------+
1 row in set (0.00 sec)
7.返回指定位置子字符串,第二个参数为起始位置,第三个参数为截取长度。索引从1开始
mysql> SELECT SUBSTR('失败是成功之母',4,2);
+-------------------------------------+
| SUBSTR('失败是成功之母',4,2) |
+-------------------------------------+
| 成功 |
+-------------------------------------+
1 row in set (0.00 sec)
8.反转字符串
mysql> SELECT REVERSE('失败是成功之母') ;
+----------------------------------+
| REVERSE('失败是成功之母') |
+----------------------------------+
| 母之功成是败失 |
+----------------------------------+
1 row in set (0.00 sec)
- 时间和日期函数
1.获取当前日期(只有年月日)
mysql> SELECT CURRENT_DATE();
+----------------+
| CURRENT_DATE() |
+----------------+
| 2020-03-18 |
+----------------+
1 row in set (0.00 sec)
2.同上,获取当前日期
mysql> SELECT CURDATE();
+------------+
| CURDATE() |
+------------+
| 2020-03-18 |
+------------+
1 row in set (0.00 sec)
3.获取当前年月日时分秒
mysql> SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| 2020-03-18 15:24:58 |
+---------------------+
1 row in set (0.00 sec)
4.获取本地时间
mysql> SELECT LOCALTIME() ;
+---------------------+
| LOCALTIME() |
+---------------------+
| 2020-03-18 15:25:38 |
+---------------------+
1 row in set (0.00 sec)
5.获取系统时间
mysql> SELECT SYSDATE() ;
+---------------------+
| SYSDATE() |
+---------------------+
| 2020-03-18 15:26:13 |
+---------------------+
1 row in set (0.00 sec)
除了上面这几种获取时间的方式还可以这样来获取:
- SELECT YEAR(NOW()) 获取当前年
- SELECT MONTH(NOW()) 获取当前月
- SELECT DAY(NOW()) 获取当前日
- SELECT HOUR(NOW()) 获取当前时
- SELECT MINUTE(NOW()) 获取当前分
- SELECT SECOND(NOW()) 获取当前秒