MySQL语法之DQL数据查询语言（数据库的查询）

Java知识点总结：想看的可以从这里进入

2.5.4、DQL数据查询

数据库的基本功能，对数据进行查询。关键字select，从数据库查询所需要的数据，是数据库最核心的语言，也是使用最多的语句。不同的数据库select语句有细微的差别。

下面是select语句的书写顺序：

#方式1：
SELECT [DISTINCT] 要返回的字段....
FROM 表 [ JOIN] 表
[ON] 多表的连接条件
WHERE 条件
[GROUP BY  分组] 
[HAVING 分组中函数过滤条件]
[ORDER BY 排序的字段  ASC/DESC]
[LIMIT 分页];

下面是 select 语句的执行顺序：

执行顺序	子句	作 用
1、	form	获取到将要查询的表所有数据（如果是连表的数据，需要根据笛卡尔积获取表数据，然后通过on进行筛选，然后再根据JOIN是的连接方式添加外部行）
2、	where	对数据根据条件进行筛选
3、	group by	配合聚合函数，对筛选后的数据进行分组
4、	having	having子句对分组后的数据进行过滤，可以检索聚合函数
5、	select	将查询到的结果，生成一个临时表
6、	distinct	去除临时表中的重复数据
7、	order by	对去重后的临时数据按需要进行排序
8、	limit	进行分页显示

1、简单查询

SELECT…FROM….，是DQL中最简单的查询语句。

--  无条件查询所有表中信息   *会显示所有的字段(主要注意的是*这个通配符是不建议使用的，因为使用*会影响效率)
SELECT * FROM `表名` ;

--  查询特定字段的信息
 SELECT 字段1,字段2... FROM `表名`;
 
-- 给需要查询的字段起别名（as也可以给数据表起名字，当数据表很多时可以使用）
 SELECT 字段1 AS '别名', 字段2 AS '别名'... FROM `表名`;

SELECT后可以跟WHERE子句，用来设置条件，根据条件查询所需数据。

-- 根据where的条件查询数据   多条件（and与  or或 not非【where not id=1 排除id=1的数据】）
SELECT 字段... FROM 表名 WHERE 表达式 and 表达式...;

SELECT语句是可以直接计算表达式的。

--  直接计算表达书
select 1+1*50 as '计算结果'; 

--  查询自增的步数,每次自增几？（计算变量）
select @@auto_increment_increment

在SELECT中用于去重的关键字是 DISTINCT，写在SELECT的后面。

--  去重查询：查出来的重复数据，会自定去除,只显示一条
SELECT DISTINCT  字段 FROM 表名;

2、模糊查询

主要使用比较运算符进行一些判断操作

操作符	描述	实例
BETWEEN AND	在某个区间内	BETWEEN 2 and 5 在[2,5]内
NOT BETWEEN	不在两值之间
IN	在集合中
NOT IN	不在集合中
LIKE	模糊匹配
IS NULL	为空	可查询某字段为空的所有数据
IS NOT NULL	不为空	可查询某字段不为空的所有数据

• LIKE 的==%== ，类似于正则中的*，可匹配任意个字符。
• LIKE的 _ ,可以匹配一个字符。
• 如果没有 %和_，就相当于运算符 =
• 使用 AND 或者 OR 指定一个或多个条件。
• 可以在 DELETE 或 UPDATE 命令中使用 WHERE…LIKE 子句来指定条件。

--  模糊查询 LIKE   （%代表任意个字符，_代表一个字符【__代表2个字符】）
-- 查询用户中姓李的
SELECT * FROM 表名 WHERE 姓名字段 LIKE '李%'; 

--查询用户中姓李的且名字只有两个字符的：
SELECT * FROM 表名 WHERE 姓名字段 LIKE '李_';   

--查询用户名字中间带寻的
SELECT * FROM 表名 WHERE 姓名字段 LIKE '%寻%'

--查询在为1,2,3班的学生	，多条结果集时
SELECT * FROM 表名 WHERE 班级id字段 IN (1,2,3);

--查询某字段为空的数据  IS NULL  或者 =''
SELECT * FROM 表名 WHERE 字段 IS NULL;		-- IS NOT NULL    不为空
SELECT * FROM 表名 WHERE 字段='';

3、连表查询

关联查询，指两个或两个以上的表一起完成的，查询的关键是这些表之间有关联字段。操作关键字 JOIN 。

连表查询主要分为三种：

• INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。

  

  SELECT 字段1，字段2.....
  FROM 表1 INNER JOIN 表2
  ON 关联条件
  WHERE 其他子句;
  

  

• **LEFT JOIN（左连接）：**获取左表所有记录，即使右表没有对应匹配的记录。

  

  SELECT 字段1，字段2.....
  FROM 表1 LEFT JOIN 表2
  ON 关联条件
  WHERE 其他子句;
  

  

• RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

  

  SELECT 字段1，字段2.....
  FROM 表1 RIGHT JOIN 表2
  ON 关联条件
  WHERE 其他子句;
  

  

• 多张表连接

  

join的几种情况：（oracle中还有两种情况，但MySQL中不支持 full join）

注意事项

笛卡尔积是一个数学运算，它是两个集合的最大组合数，即两个集合之间数据的乘积。

笛卡尔积的产生：在表连接过程中如果没有条件进行约束会产生笛卡尔积，即查询的最终结果=第一个表行数*第二个表行数，所以我们在进行连表查询时，一定要带上查询的条件，避免笛卡尔积的产生。（ n个表,至少需要n-1个连接条件。两表至少1个条件，3表至少2个条件）

ON和WHERE的区别

1、join on是一个具体的语法，生成临时表时使用的条件，它不管on中的条件是否为真，都会返回(左连接返回左表全部记录)（右连接返回右表全部记录）。

2、where是等值查询，是在生成临时表以后再进行过滤，对左右表都进行筛选。

on后的条件用来生成左右表关联的临时表，where后的条件对临时表中的记录进行过滤。

阿里编程规范：

【 强制 】对于数据库中表记录的查询和变更，只要涉及多个表，都需要在列名前加表的别名（或表名）进行限定。

说明 ：对多表进行查询记录、更新记录、删除记录时，如果对操作列没有限定表的别名（或表名），并且操作列在多个表中存在时，就会抛异常。

这种规范是因为不同表中的字段很有可能会出现重名的情况，比如name这个字段，在两张表中都存在，如果不加以限制，就会出现异常。

4、自连接

特殊的一种表连接，以一张表为基础，在逻辑上当成多张表进行处理，简单来说就是自己和自己连接。把一张表用语法拆成两张一样的表，只是变量名不同。

自连接在表中的数据有层次结构的时候使用，比如：商品分类表（一张表中有商品的多级分类），权限表（表中有上下级的关系），菜单表（多级菜单），省市联动表等等。在商品类别表有的二级菜单有一个父id的字段，存放上级菜单的id（一级菜单的电器类id为1，二级菜单手机的id为2，父id字段为1，则表示手机是电器的下级分类）。

具体把一张表当成几张表使用，需要根据需求比如下面的省市区的数据表。

• 一张国省市区（县）的数据表，用自连接将其显示出来 (表使用父级id的字段来区分上下级关系)

  

• 显示国省市区四级关系

  

• 显示省市区三级关系

  

• 显示两级关系

  

5、UNION

UNION 操作符用来合并查询结果，它用于将两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。

• 连接的两个sql语句返回的字段数量必须相同。
• 两个语句返回的字段名不一样时，只显示第一个语句中返回的字段值
• UNION会进行去重，记录不会重复，按字段的默认规则排序。UNION ALL是返回所有，不会进行排序。从效率上说，UNION ALL 要比 UNION 更快。

SELECT 字段 FROM 表 [WHERE 条件]
UNION [ALL]				-- 连接两个select查询的结果：ALL为显示所有
SELECT 字段 FROM 表 [WHERE 条件];

6、排序

排序 [ORDER BY ] 写在WHERE条件之后，可以对查询结果按某一字段(数字、字母等)进行降序DESC / 升序ASC 的排列。

--  排序
SELECT 字段... FROM 表...
WHERE  条件.....
ORDER BY 字段 DESC / ASC(默认) 

[ORDER BY ]后面也可以跟多个字段进行排序，在对多列进行排序的时候，首先排序的第一个字段必须有相同的值，这样才会对第二列进行排序，如果第一列数据中所有值都是唯一的，将不再对第二列进行排序。

7、分页查询

分页 [LIMIT] 一般写在 SELECT语句的最后，按设置给数据进行分页显示。当数据过多时，使用过程中需要进行分页来提供良好的体验，同时分页可以缓解数据库的压力，提升查询效率。

--  排序
SELECT 字段... FROM 表...
WHERE  条件.....
LIMIT 位置偏移量, 行数;

LIMIT 页面起始值,页面行数pageSize      
公式：LIMIT(当前页数-1)*每页条数，每页条数
-- 是查询语句中处于最后面的语句
-- 起始值：就是当前页第一条数据是查询数据的第几条。从0开始而非1
-- 页面行数：页面最大显示数

每页显示五条数据：
第一页： limit 0,5    -- (1-1)pageSize,pageSize
第二页： limit 5,5    -- (2-1)pageSize,pageSize
第三页： limit 10,5   -- (3-1)pageSize,pageSize
第四页： limit 15,5   -- (4-1)pageSize,pageSize
...
第n页： limit (n-1)pageSize,pageSize

实际网页中分页需要的数据：
1、当前页：n
2、每页最大显示数量：pageSize
3、总页数：数据总数/pageSize
4、每页的起始值：(n-1)pageSize

8、分组查询

[GROUP BY ]分组查询，是先将数据按一个或多个字段进行分组后，再将数据按需要检索。分组在使用时必须配合聚合函数（count、sum、avg、max、min）一起使用

SELECT 普通字段, 聚合函数处理 
FROM  表
WHERE 条件
GROUP BY  按某字段分组(可以一列或多列分组);

WHERE 子句在 GROUP BY 语句之前，对分组前的数据起筛选作用，所以无法对分组后的数据进行过滤，同时where无法和聚合函数同用，所以这时候时候 HAVING子句对分组后的数据进行过滤。HAVING子句必须配合GROUP BY使用，无法单独使用。

SELECT 普通字段, 聚合函数处理 
FROM  表
WHERE 条件
GROUP BY  按某字段分组(可以一列或多列分组)
HAVING 可以对聚合函数进行条件检索;

WHERE和HAVING对比：

1、WHERE 可以直接使用表中的字段作为筛选条件，但不能使用分组中的函数作为筛选条件；
HAVING 必须要与 GROUP BY 配合使用，可以把分组计算的函数和分组字段作为筛选条件。

这是因为在查询语法结构中，WHERE执行 在 GROUP BY 之前，所以无法对分组结果进行筛选。HAVING 在 GROUP BY 之后，可以使用分组字段和分组中的计算函数，对分组的结果集进行筛选，这个功能是 WHERE 无法完成的。另外，WHERE排除的记录不再包括在分组中。

2、如果需要从关联表中获取需要的数据，WHERE是先筛选后连接，HAVING 是先连接后筛选。 所以在关联查询中，WHERE 比 HAVING 更高效。因为 WHERE 可以先筛选，用一个筛选后的较小数据集和关联表进行连接，这样占用的资源比较少，执行效率也比较高。HAVING 则需要先把结果集准备好，用未被筛选的数据集进行关联，然后对这个大的数据集进行筛选，这样占用的资源就比较多，执行效率也较低。

所以在使用时，一般包含分组统计函数的条件用 HAVING，普通条件用 WHERE。

• 在数据库通过0和1保存性别，以性别分组分别查询各自的数量

  

• 以班级分组，查询各个班级的人数：

  

• 如果仅仅是想查询班级人数>10的班级，那么就需要对COUNT(*)进行条件筛选，这时候就需要用到HAVING 了

  

GROUP BY 在使用聚合函数统计出来数据，只能知道一些大致的情况，比如班级学生数量，但是我们如果还想知道班级内学生的姓名时，可以使用 group_concat函数。

group_concat(字段 [排序：order by asc / desc] [separator'分隔符'（默认,）])

9、子查询in

子查询就是在where语句中嵌套一个select语句，关键字 一般为 IN 和 EXISTS 或者是 比较运算符

在 WHERE 子句中 IN 可以过滤某个字段的多个值（WHERE 字段 IN（值1,值2…）），当 IN 后的值不是固定的几个值，而是某条 SELECT 语句的查询结果时，使用 IN 关键字可以将原表中特定列的值与子查询返回的结果集中的值进行比较，如果某行的特定列的值存在，则在 SELECT 语句的查询结果中就包含这一行。

1. IN 过滤字段的值

   SELECT * FROM 表 WHERE 字段 IN (value1,value2...);
   

   

2. IN 子查询

   1. 如果子查询的SELECT返回的是一条数据的话，可以使用 运算符 =、>、>=、<、<=、<>

      SELECT * FROM 表 WHERE 字段 运算符 (SELECT * FROM 表 [WHERE .......])
      

      

   2. 如果子查询的SELECT返回的是多条语句：

      SELECT * FROM 表 WHERE 字段 IN (SELECT * FROM 表 [WHERE .......])
      

      

​

10、子查询EXISTS

EXISTS是查询中用于测试内部查询是否返回行的布尔值运算符，将主查询的数据放到子查询中做条件验证，根据结果决定主查询数据是否保留。

EXISTS: 是否存在的意思, EXISTS子查询就是用来判断某些条件是否满足(跨表),

EXISTS是接在 WHERE 之后

EXISTS返回的结果只有0和1.

SELECT * FROM 表 WHERE EXISTS (SELECT * FROM 表 WHERE... )

例：如果存在数学考试则列出整个表的记录