MySQL：DQL 数据查询语句盘点

本篇内容包括：DQL 的简介、SELECT 语句、WHERE 条件语句、JOIN 连接查询(多表查询)和分组、过滤、排序、分页、子查询的使用。

一、DQL 简介

DQL（Data QueryLanguage）语句，即数据查询语句

常用的语句关键字有：SELECT、FROM、WHERE、ORDER BY、HAVING、ASC|DESC

二、SELECT 语句

1、SELECT 语句

SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1] [,table.field[as alias2]][,...]]}
FROM table_name [as table_alias]
[left | right | inner join table_name2]  #联合查询
[WHERE ...]     	# 指定结果需满足的条件
[GROUP BY ...]    # 指定结果按照哪几个字段来分组
[HAVNG ...]    		# 过滤分组的记录必须满足的次要条件
[ORDER BY ...]    # 指定查询记录按一个或多个条件排序
[LIMIT { [offset,]row_count  |  row_count OFFSET offset}];  #指定查询的记录从哪条至哪条

PS:

• []括号代表可选的;
• {}括号代表必须的;
• #为MySQL语句中的注释符,也可以用 /**/

指定查询字段:

• 查询表中所有的数据列结果,采用"*"符号 ：SELECT * FROM 表名;
• 可指定查询的结果数据列：SELECT 字段1,字段2,.... FROM 表名;
• 如需要区分连接查询时两个表有同名的字段：SELECT 表1.字段1,表2.字段2,字段3,.... FROM 表1,表2;

2、AS 子句

SELECT StudentNo AS "学号" FROM student;	 			 # 可给数据列取一个新别名
SELECT a.StudentNo FROM student AS a;					 	# 可给表取一个新别名
SELECT Phone+1 AS Tel FROM student;							# 可把经计算或总结的结果用另外一个新名称来代替

3、DISTINCT 子句

SELECT DISTINCT 字段名1,字段名2...  FROM 表名			#掉SELECT查询返回的记录结果中重复的记录(所有返回列的值都相同),只返回一条

PS：ALL 关键字是默认的(不加DISTINCT默认ALL),返回所有的记录,与DISTINCT相反。

4、使用表达式的列

表达式一般由文本值、列值、NULL、函数和操作符等组成

应用场景:

1. SELECT语句返回结果列中使用
2. SELECT语句的ORDER BY 、HAVING等子句中使用
3. DML语句中的WHERE条件语句中使用表达式

PS：需要避免SQL返回结果中包含".“,”*"和括号等干扰开发语言程序

三、WHERE 条件语句

1、WHERE 条件语句

用于检索数据表中符合条件的记录，搜索条件可由一个或多个逻辑表达式组成，结果一般为真或假。

搜索条件的组成：

1. 逻辑操作符
2. 比较操作符

2、逻辑操作符

3、比较操作符

PS：1. 数值数据类型的记录之间才能进行算数运算；2. 相同数据类型的数据之间才能进行比较。

4、BETWEEN AND 范围查询子句

SELECT 字段1,字段2,.... FROM 表名 WHERE 字段X BETWEEN 值1 AND 值2				# 根据一个范围值来检索,等同于>=和<=联合使用

5、LIKE 模糊查询子句

在 WHERE 子句中,使用 LIKE 关键字进行模糊查询

• 与%一起使用,表示匹配 0 或任意多个字符
• 与_一起使用,表示匹配单个字符
• 如果需要显示%或者_,可以使用转义字符\，也可以用ESCAPE关键字自定义转义符,如:

6、IN范围查询

在 WHERE 子句中使用 IN 关键字进行范围查询

SELECT 字段列1,字段2,...FROM 表名 WHERE 字段X IN (值1,值2,值3...)

查询的字段X的值,至少与括号中的一个值相同，多个值之间用英文逗号隔开

7、NULL空值条件查询

• NULL代表"无值"
• 区别于零值0和空字符串""
• 只能出现在定义允许为NULL的字段
• 须使用IS NULL 或 IS NOT NULL比较操作符去比较

四、JOIN 连接查询(多表查询)

1、JOIN 连接查询

如需要多张数据表进行查询,则可通过连接运算符实现多个查询

分类包括内连接和外连接

2、内连接(inner join)

在表中至少一个匹配时,则返回记录

SELECT 字段1,字段2,... FROM table_1
INNER JOIN table_2 
ON table_1.字段X = table_2.字段Y;

3、等值连接

SELECT 字段1,字段2,.... FROM table_1,table_2 
WHERE table_1.字段X =table_2.字段Y;

• 与单表查询类似,都是SELECT语句
• 把多个表放在FROM后,并用逗号隔开
• 可使用AS关键字取别名,便于引用
• 如无重名查询字段则可省略数据表的指定

4、非等值连接

SELECT 字段1,字段2,.... FROM table_1,table_2 ;

• 返回记录数为两表记录数的乘积

5、自连接

SELECT 字段1,字段2,.... FROM 表1 别名1,表1 别名2;

• 数据表与自身进行连接

6、外连接(out join)——左连接

从左表(table_1)中返回所有的记录,即便在右表(table_2)中没有匹配的行

SELECT 字段1,字段2,... FROM table_1
LEFT JOIN table_2 
ON table_1.字段X =table_2.字段Y;

7、外连接(out join)——右连接

从右表(table_2)中返回所有的记录,即便在左表(table_1)中没有匹配的行

SELECT 字段1,字段2,... FROM table_1
RIGHT JOIN table_2 
ON table_1.字段X =table_2.字段Y;

8、不同的SQL JOIN对比

9、full join

mysql 不支持 full join，使用 left join 和 right join。可以相当与左右链接的结果

select * from t1 left join t2 on t1.id = t2.id
union
select * from t1 right join t2 on t1.id = t2.id

五、分组、过滤、排序、分页

1、GROUP BY 语句

• 对所有数据进行分组统计。分组的依据字段可以有多个，并依次分组
• 与HAVING结合使用，进行分组后的数据筛选
• GROUP BY的语句顺序在WHERE后面，ORDER BY 的前面
• 通常在对数据使用计算统计的时候，会用到GROUP BY分组

2、HAVING 语句

过滤分组的记录必须满足的次要条件。SELECT语句中，在GROUP BY分组之后再进行条件筛选，就不能使用WHERE，而是在GROUP BY后面通过HAVING进行分组后的条件筛选。HAVING的作用等同于WHERE。

3、ORDER BY 语句

ORDER BY 列名1 ASC/DESC[,列名2 ASC/DESC,...]

• 对 SELECT 语句查询得到的结果，按某些字段进行排序
• 与DESC 或 ASC搭配使用，默认为 ASC
• ASC 为升序排列，DESC 为降序排列

4、LIMIT

分页显示，对用户体验、网络传输、查询压力上都有好处

LIMIT[m,]n 或 LIMIT n OFFSET m

• 限制 SELECT 返回结果的行数
• m 指定第一个返回记录行的偏移量（显示的起始位置）
• n 指定返回记录行的最大数目（显示行数）
• m 不指定则偏移量为 0，从第一条开始返回前 n 条记录
• 在MySQL中，显示每页的行数可以使用 LIMIT (页码-1)*行数,行数

六、子查询

在查询语句中的WHERE条件子句中，又嵌套了另外一个查询语句

• 嵌套查询可由多个子查询组成，求解的方式是由里及外
• 子查询返回的结果一般都是集合，故而建议使用IN关键字

例如：

SELECT
  studentno "学号",
  studentname "姓名"
FROM
  student
WHERE
  studentno IN (
    SELECT
      studentno
    FROM
      result
    WHERE
      subjectno =(
        SELECT
          subjectno
        FROM
          `subject`
        WHERE
          subjectname = '高等数学-2'
      )
      AND studentresult >= 80
  )