目录
注,文中如无特别说明,均指 MySQL 数据库环境!
一. 逻辑运算符
1. 执行的优先级
括号 > Not(非) > And(与) > Or(或)
2. 多个 or 子句的简化写法 - IN
SELECT * FROM 数据表
WHERE 接单数 >= 20
AND 评分 >= 4.5
AND (区域 = '华东' OR 区域 = '华南' OR 区域 = '华中')
SELECT * FROM 数据表
WHERE 接单数 >= 20
AND 评分 >= 4.5
AND 区域 IN ('华东', '华南', '华中')
二. 模糊查询
通配符 与 Like
-
%
:在与Like
搭配使用时,代表 “ 任意 多个 任意 字符 ”SELECT * FROM tblMembers WHERE kitchen LIKE '%盒%'
-
_
: 下划线(_
)代表任意的1
个字符SELECT * FROM person WHERE uname LIKE '_峰'
注意: 如果需要查询的字符就是 “
%
” 或 “_
”,写Like
语句时,需要使用反斜线(\
)对通配符转义,变为普通字符。如下所示:SELECT * FROM person WHERE uname LIKE '%\%'
三. SQL语句中的计算字段
SELECT 中、WHERE 子句中、ORDER BY 字句中均中使用:
四. SQL 子句的执行顺序
FROM
⇒
\Rightarrow
⇒ WHERE
⇒
\Rightarrow
⇒ SELECT
⇒
\Rightarrow
⇒ ORDER BY
五. 系统函数
1. 拼接多个字符串字段
-
加号(+)
SELECT team + game as 完整名称 FROM football
-
兼容性:
- 1) MySQL:结果错误;
- 2) SQLServer 、Access:结果正确;
-
3)
Oracle:结果错误(需使用
||
代替加号‘+
’)
这也体现了不同数据库对 SQL 语言的支持不一样的特点。
-
CONCAT 函数
CONCAT 即拼接字符串(Concatenate)的缩写,绝大多数据库提供了该函数,实现字符串字段的拼接,如:
SELECT CONCAT( team, '(' , game , ')' ) AS 完整队名 FROM football
执行结果如下图所示:
注意: Oracle 数据库里只允许拼接两个字符串
-
其它函数
同时,绝大部分数据库中也支持以下函数。
兼容性:下表中列出的函数功能,不同数据库中也存在写法上的不同,如果不清楚,请查阅数据库文档,这点请注意!
数表达式 描述 LEFT(s,x)
取字符串 s
的左边x
位RIGHT(s,x)
取字符串 s
的右边x
位LENGTH(s)
或LEN(s)
计算 s
的长度,即字符个数或占用字节数,具体取决定数据库品牌TRIM(s)
去掉 s
两端的空格UPPER(s)
将 s
中的字母全变为 大写(Access 里使用Ucace
)LOWER(s)
将 s
中的字母全变为 小写(Access 里使用Lcace
)
2. 处理日期和时间
MySQL、SQLServer 等常见数据库中都有名为 DATETIME
的类型,可以保存 “ 日期 + 时间 ” 形式的完整时间数据。
有这样一张 打赏收入 表,其中 打赏时间 和 到帐时间 字段中保存的都是DATETIME
类型的数据。
SELECT * FROM 打赏收入 WHERE 打赏时间 = '2020-09-04 15:19:53'
SELECT * FROM 打赏收入 WHERE 打赏时间 = '2020/09/04 15:19:53'
虽然下面这句中表示时间的字符串与数据库中保存的时间格式不一致,但它们都能得到正确的结果。
原因在于 MySQL 认为用 “_
” 隔开的年月日,和用 “ /
” 隔开的年月日,都是合法的日期表示方法。
SELECT * FROM 打赏收入 WHERE 打赏时间 = '2020-09-04'
特别注意: 对于 DATETIME 类型,如果只写日期如 2020-09-04
,则默认为该日零点零分零秒: 2020-09-04 00:00:00
-
between … and
以查找 9 月份的交易记录为例:
SELECT * FROM 打赏收入 WHERE 打赏时间 BETWEEN '2020-09-01 0:0:0' AND '2020-09-30 23:59:59'
等价于
SELECT * FROM 打赏收入 WHERE 打赏时间 >= '2020-09-01 0:0:0' AND 打赏时间 < '2020-10-01 0:0:0'
注意,
between ... and
是一个 闭区间,包含了 起点 和 终点 。
-
DATE 函数
SELECT * FROM 打赏收入 WHERE DATE(打赏时间) = '2020-09-03'
注意:DATE函数比前面使用
>=
、<
、between... and
等等,在执行时间上,更长一些(效率更低)。
MySQL 常用时间函数:
函数 描述 DATE(d)
取得 d
代表的日期,忽略秒、分、时YEAR(d)
从日期 d
取得年份数字MONTH(d)
取得 d
的月份数字DAY(d)
取得 d
的日数字HOUR(d)
从日期 d
取得小时数字MINUTE(d)
从 d
取得分钟数字SECOND(d)
从日期 d
取得秒数DATEDIFF(d1,d2)
计算 d1
和d2
相差多少天注意:除
DATE
函数外,其余的都是返回数字,如MONTH('2020-09-01 0:0:0')
返回值是 数字9
,比较运算时不要打引号。
3. 聚集函数
这里重点讲解 Count 函数,其它函数略。
各种 聚集函数 的结果只可能是 一个 数字,所以最终查询结果中,只有 一条 记录。
SELECT count(*) FROM income
count(*)
:所有记录都被纳入统计。因为没有指定具体的字段名,所以记录中只要有一个字段中不存在Null,就符合 count(*)
的筛选条件
说明: 即假如要统计某个表中所有记录的个数,而 其中“某字段” 中存在Null
值,count(“某字段”)
,其结果则为表中总行数减去有Null
值的行数(Null值不计入统计)
所有聚集函数中,除 COUNT(*)
,均忽略null
。
根据 SQL标准,聚集函数 不 应该与普通字段 同时 出现在 SELECT 中, 除非 该字段用于 分组(MySQL没有严格执行规定,所以即使写错语句也不会报错,应 谨慎 使用)
应用举例:
- 删除重复后统计记录数
SELECT COUNT(distinct funs_id) FROM income
六. Group By - 分组查询
GROUP BY 字段等。按照指定字段(或计算字段、函数等)对记录进行分组。如果分组时发现 NULL
值,则 NULL
的记录单列一组。
可以使用聚集函数,对每一组数据分别进行统计。
SELECT funs_id , SUM(money) FROM income GROUP BY funs_id
如图:
起个别名,再加一个排序:
SELECT funs_id , SUM(money) AS 打赏总额
FROM income GROUP BY funs_id
ORDER BY 打赏总额 DESC
语句执行结果:
注意各子句间的先后次序(顺序绝对不能搞错):
SELECT
⇒
\Rightarrow
⇒ FROM
⇒
\Rightarrow
⇒ WHERE
⇒
\Rightarrow
⇒ GROUP BY
⇒
\Rightarrow
⇒ ORDER BY
此外,MySQL相比 SQLServer、Oracle 等数据库,特殊的地方在于:
- 允许
Group By
中使用 别名 ; - 允许 在
SELECT
中使用与 分组无关 的字段。
标准 SQL 语法:
如果使用了GROUP BY
,则SELECT
子句中只能出现GROUP BY
后面的字段,或 聚集函数。
而 MySQL 进行了特别设计,因而不受此规则限制。
动画演示: GROUP BY 分组统计
分组查询的注意事项:
WHERE
子句必须写在GROUP BY
子句之前;WHERE
子句中 不允许 使用 聚集统计函数;- 分组后使用
HAVING
筛选分组,而不是WHERE
。
七. Having 子句
HAVING 子句:对分组后的数据进行过滤,分组后选出符合条件的组。
HAVING
的用法和 WHERE
用法类似。区别在于 HAVING 子句必须写在 GROUP BY 分组之后,执行顺序也在分组之后,HAVING 可以使用聚合函数。
例如:
select date(pay_time) as 日期, sum(money) as 总额
from income
where gift_level = '玫瑰'
group by date(pay_time)
having sum(money) > 500
以上语句对分组后每一组的总金额进行过滤,保留大于500的组。