day38---MySQL查询语句

1. 关键字说明

关键字的执行优先级

from
where
group by
having
select
distinct
order by
limit

SELECT语句关键字的定义顺序

SELECT DISTINCT <select_list>
FROM <left_table>
<join_type> JOIN <right_table>
ON <join_condition>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
ORDER BY <order_by_condition>
LIMIT <limit_number>

SELECT语句关键字的执行顺序

(7) SELECT 
(8) DISTINCT <select_list>
(1) FROM <left_table>
(3) <join_type> JOIN <right_table>
(2) ON <join_condition>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list>
(6) HAVING <having_condition>
(9) ORDER BY <order_by_condition>
(10) LIMIT <limit_number>

2. 单表查询

单表查询的语法

SELECT 字段名1,字段名2... FROM 表名 WHERE 条件 GROUP BY 字段名 HAVING 筛选条件 ORDER BY 字段名 LIMIT n,m

普通查询

# 简单查询：
SELECT * FROM 表名;
-- 查询表中所有的数据
SELECT 字段1,字段2... FROM 表名;
-- 根据指定字段查找表中对应的所有数据

# 去重查询：
SELECT DISTINCT 字段 FROM 表名;
-- 去重查询(中间如果指定多字段需要所有字段的值一样才会被去重，否则会显示全部的数据)

# 运算查询：
SELECT 字段1的加减乘除运算 FROM 表名;
-- 查询字段对应数据的值进行运算，显示出计算结果
SELECT 字段1,字段2的加减乘除运算 AS 别名 FROM 表名;
-- 查询字段对应数据的值进行运算，显示出计算结果；AS是给字段起一个别名
SELECT 字段1,字段2的加减乘除运算 AS 别名, 字段3的加减乘除运算 别名 FROM 表名;
-- 查询字段对应数据的值进行运算，显示出计算结果；AS别名关键字可以省略

# 定义格式查询：
SELECT CONCAT('关键字1':',字段1,' '关键字2':, 字段2的加减乘除运算) AS 别名 FROM 表名;
-- 根据CONCAT()中的参数查询结果按照指定关键字对应字段数据的格式显示
SELECT CONCAT_WS('分隔符',字段1,字段2的加减乘除运算) AS 别名 FROM 表名;
-- 根据CONCAT_WS()中第一个参数指定的符号，当作后面字段查询结果的分隔符显示

where条件约束

where条件的种类：

• 比较运算符：'='、'>'、'<'、'>='、'<='、'!='、'<>'

• 逻辑运算符：and(&&)、or(||)、not

• 成员运算符：in、not in

• 区间范围：between...to...

• 模糊查询：like '%_'

  • 模糊查询可以使用'%'，也可以使用'_'

  • '%'代表多个字符

  • '_'代表一个字符

  • 's%'代表以s开头的

  • '%s'代表以s结尾的

  • '%s%'代表包含s的

• 正则表达式查询：regexp '正则表达式语法格式'

• is [not] null：判断字段是否为空或非空(判断时使用is，修改时使用等号'=')

# 单条件查询
SELECT 字段名 FROM 表名 WHERE 字段名=值;

# 多条件查询
SELECT 字段名 FROM 表名 WHERE 字段名1 > 值1 AND 字段名2 <= 值2;
-- 查询同时满足and左右两边条件的数据
SELECT 字段名 FROM 表名 WHERE 字段名1 != 值1 OR 字段名2 < 值2;
-- 查询满足or左右两边任意一个条件的数据

# IN集合查询
SELECT 字段名 FROM 表名 WHERE 字段名 IN (值1,值2...);
-- 查询字段值存在于集合中的数据
SELECT 字段名 FROM 表名 WHERE 字段名 not IN (值1,值2...);
-- 查询字段值存在于集合中以外的数据

# 区间范围查询
SELECT 字段名 FROM 表名 WHERE 字段名 between 值1 and 值2;
-- 查询从值1到值2之间的数据，包含值1和值2

# 模糊查询
SELECT 字段名 FROM 表名 WHERE 字段名 LIKE 's%';
-- 查询以s开头的所有数据
SELECT 字段名 FROM 表名 WHERE 字段名 LIKE '_s';
-- 查询以s结尾，并且只有两位字符的数据
SELECT 字段名 FROM 表名 WHERE 字段名 LIKE '%s_';
-- 查询包含s，并且s后只有一位字符的数据

# 正则表达式查询：
SELECT 字段名 FROM 表名 WHERE 字段名 REGEXP '正则表达式语法格式';
-- 根据指定字段查询能匹配到正则表达式结果的值

# IS NULL语句
SELECT 字段名 FROM 表名 WHERE 字段名='';
-- 查询字段为空字符串的数据
SELECT 字段名 FROM 表名 WHERE 字段名 IS NULL;
-- 查询字段为NULL的数据
SELECT 字段名 FROM 表名 WHERE 字段名 IS NOT NULL;
-- 查询字段不为NULL的数据
UPDATE 表名 SET 字段名=NULL;
-- 修改指定字段的值为NULL

查询排序(ORDER BY)

# 单列排序
SELECT 字段名 FROM 表名 ORDER BY 字段名 [ASC];
-- 根据指定字段正序排序(ASC可以不用指定，默认是正序排序)
SELECT 字段名 FROM 表名 ORDER BY 字段名 DESC;
-- 根据指定字段反序排序

# 多列排序
SELECT 字段名 FROM 表名 ORDER BY 字段名1,字段名2...;
-- 先根据字段名1的值排序，如果字段名1中的值有相同的，再按照字段名2的值排序，以此类推

分组查询(GROUP BY)

• 说明：按照指定字段的相同数据进行归类

• 分组使用在where之后，group by是基于where条件过滤之后再进行分组

• 分组之后默认只能查看到分组字段的数据

SELECT 字段名 FROM 表名 ORDER BY 字段名;
-- 查看根据指定字段分组后的数据(类似去重)
SELECT 字段名1 GROUP_CONCAT(字段2) FROM 表名 ORDER BY 字段名1;
-- 查看根据指定字段分组后的数据以及组中指定字段的数据，以逗号','分割
SELECT 字段名 COUNT(字段名) FROM 表名 ORDER BY 字段名;
-- 查看根据指定字段分组后的数据以及统计每个组中的数据个数

聚合函数

• 聚合函数是组的内容，如果没有分组，默认是一组

  • MAX()：求最大值

  • MIN()：求最小值

  • SUM()：求和

  • AVG()：求平均值

  • COUNT()：统计总数量(默认写表中的主键字段)

HAVING过滤

• 执行优先级(从高到低)：where > group by > having

• having与where的区别：

  • where发生在分组之前，where可以有任意字段，但是不能有聚合函数

  • having发生在分组之后，所以having只能使用分组的字段，默认情况下无法获取其他字段的值(通过聚合函数可以获取)

SELECT 字段名 FROM 表名 WHERE 条件 GROUP BY 字段名 HAVING 聚合函数(字段名) 筛选条件;

查询分页(LIMIT)

SELECT 字段名 FROM 表名 LIMIT m;
-- 查询数据的前m行(默认从第1行查找)
SELECT 字段名 FROM 表名 LIMIT 0,m;
-- 从第一行查询数据，查询m行(第一行的索引下标是0)
SELECT 字段名 FROM 表名 LIMIT n,m;
-- 从第n+1行开始查询数据，查询m行(n是指索引下标，实际是第n+1行)

子查询(嵌套查询)

• 将一个查询语句嵌套在另一个查询语句中

• 内层查询语句的查询结果，可以为外层查询语句提供查询条件

• 子查询中可以包含各种比较运算符等过滤条件

• 子查询中也可以包含：EXISTS(存在)和NOT EXISTS(不存在)关键字，不会返回查询的结果，而是返回一个布尔值，True或False

• 使用EXISTS关键字时：

  • 返回True时，执行外部查询

  • 返回False时，不会执行外部查询

• 使用NOT EXISTS关键字时：

  • 返回True时，不会执行外部查询

  • 返回False时，执行外部查询

SELECT 字段名 FROM 表名 WHERE 字段名 运算符 (SELECT 字段名 FROM 表名 WHERE 条件)
-- 把括号内的查询的结果当作括号外的查询条件再次进行查询(返回单个结果可以使用比较运算符，返回多个结果可以使用成员运算符)

# EXISTS和NOT EXISTS关键字
SELECT 字段名 FROM 表名 WHERE EXISTS (SELECT 字段名 FROM 表名 WHERE 条件)
-- 括号内中的结果为True时，执行外部查询，结果为False时，不执行外部查询
SELECT 字段名 FROM 表名 WHERE EXISTS (SELECT 字段名 FROM 表名 WHERE 条件)
-- 括号内中的结果为True时，不执行外部查询，结果为False时，执行外部查询

3. 多表查询

多表连接查询

# 多表连接查询语法
SELECT 字段名 FROM 表1 INNER|LEFT|RIGHT JOIN 表2 ON 表1.字段 = 表2.字段;

# 交叉连接：不适用任何匹配条件(生成笛卡尔积)
SELECT 字段名 FROM 表1,表2;

# 内连接：只连接匹配的行
SELECT 表1.字段名,表2.字段名 FROM 表1 INNER JOIN 表2 on 表1.相同字段=表2.相同字段
-- 也可以直接下面这样写
SELECT 表1.字段名,表2.字段名 FROM 表1,表2 where 表1.相同字段=表2.相同字段

# 外链接之左连接：优先显示左表全部记录(在内连接的基础上增加左边有右边没有的数据结果，没有的数据使用NULL填充)
SELECT 表1.字段名,表2.字段名 FROM 表1 LEFT JOIN 表2 on 表1.相同字段=表2.相同字段

# 外链接之右连接：优先显示右表全部记录(在内连接的基础上增加右边有左边没有的结果，没有的数据使用NULL填充)
SELECT 表1.字段名,表2.字段名 FROM 表1 RIGHT JOIN 表2 on 表1.相同字段=表2.相同字段

# 全外连接：显示左右两个表全部记录(在内连接的基础上增加左边有右边没有的和右边有左边没有的结果)
# MySQL不支持全外连接FULL JOIN，可以使用UNION或UNION ALL的方式实现(UNION ALL会显示所有的数据，UNION会去掉相同的数据)
SELECT 表1.字段名,表2.字段名 FROM 表1 LEFT JOIN 表2 on 表1.相同字段=表2.相同字段 UNION ALL SELECT 表1.字段名,表2.字段名 FROM 表1 RIGHT JOIN 表2 on 表1.相同字段=表2.相同字段
-- 全外连接，显示所有的数据
SELECT 表1.字段名,表2.字段名 FROM 表1 LEFT JOIN 表2 on 表1.相同字段=表2.相同字段 UNION SELECT 表1.字段名,表2.字段名 FROM 表1 RIGHT JOIN 表2 on 表1.相同字段=表2.相同字段
-- 全外连接，显示去重后的数据

符合条件连接查询

使用多表连接查询的方式可以使用条件过滤，结合where、order by等限制进行条件过滤后的数据结果查询，在查询后直接跟条件即可