MySQL基础(三)----DQL详解

一.DQL介绍

DQL(Data QueryLanguage) 数据查询语言:是一种用于从数据库中检索数据的语言。它是SQL（Structured Query Language）的一部分，是SQL语言的一个重要组成部分。(同时也是Doctrine ORM¹的查询语言。）

DQL语言主要用于查询数据库中的数据，可以使用SELECT语句来检索数据。
SELECT语句可以指定要检索的列、表、条件和排序方式等，还可以使用聚合函数（SUM、AVG、COUNT等）、
分组(GROUP BY)、排序(ORDER BY)和限制(LIMIT)结果集等高级功能。
1
2
3

二.常用命令

1. SELECT：用于查询数据表中的数据，可以指定要查询的字段和条件。

2. FROM：用于指定要查询的数据表。

3. WHERE：用于指定查询条件，可以使用比较运算符、逻辑运算符和通配符等。

4. JOIN：用于连接多个数据表，可以指定连接类型和连接条件。

5. GROUP BY：用于对查询结果进行分组，可以指定分组字段。

6. HAVING：用于对分组后的结果进行筛选，可以使用比较运算符和聚合函数等。

7. ORDER BY：用于对查询结果进行排序，可以指定排序字段和排序方式。

8. LIMIT：用于限制查询结果的数量，可以指定起始位置和返回的记录数。

9. UNION运算符:用于合并两个或多个 SELECT 语句结果集的操作符。UNION 操作符将两个或多个 SELECT 语句的结果集合并成一个结果集，并去除重复的行。

三.实例展示

3.1.select 和from

查询表格内字段数据的指令，经常搭配不同子句对查询范围加以限制以及更改查询结果的输出。
查询数据：

3.1.1.逐个查询：

  SELECT <列名1>, <列名2>, ... FROM <表名> ; //查询表下某(些)列的数据
  其中列名越靠前，输出位置越靠前，即输出时可以更改字段排列顺序。
1
2

例如：从 tb_students_info 表中获取 id、name 和 height 三列，SQL 语句和运行结果如下所示。

mysql> SELECT id,name,height
-> FROM tb_students_info;
1
2

mysql> SELECT height,name,id
-> FROM tb_students_info;

3.1.2通配符“*”查所有数据

SELECT * FROM 表名;
1

使用“*”查询时，只能按照数据表中字段的顺序进行排列，==不能改变字段的排列顺序。
==运行结果如图所示

3.2.where

MySQL中使用where子句指定搜索条件进行过滤

3.2.1 where子句位置

  1.from子句后面
  比如：
mysql> select * from user where age=18;  //输出age=18的用户信息
  2.order by之前
  mysql> select * from user where age = 18 order by name;
1
2
3
4
5

3.2.2 操作符

where子句支持8个操作符

= 	等于   //用于查询完全匹配的数据，注意MySQL默认不区分英文大小写.
<> 	不等于
!= 	不等于    //<> 和 !=这两个操作符实现的效果相同，均是匹配不相等的数据
< 	小于
<= 	小于等于
> 	大于
>=大于等于  //这四个操作符用于数值类型的列数据比较，但是如果作用于文本字段，MySQL也能执行只是返回的结果
             可能并不是你预期的数据
BETWEEN AND 	两个值之间的区间 比如 BETWEEN 1 AND 100
1
2
3
4
5
6
7
8
9

例如：
select * from user where name = ‘Lihua’;
select * from user where name <> ‘Li’;
select * from user where name != ‘Geny’;
select * from user where age <= 20;
select * from user where age between 20 and 50;

3.2.3 空值null

空值null指的是不包含数据，它可以在建表的时候指定其中的列是否可以包含空值。需要注意null和数据值类型的0，字符类型的空格不一样，空值null指的是没有值。
关于空值null的查询，MySQL提供了专门的where子句is null。

需求：
查询name为空值的数据
语句：

select * from user where name is null;
1

如果表中没有name值为空的值，结果为：

Empty set (0.00 sec)
1

此时可以使用is not null查询非空name的信息：

mysql> select * from user where name is not null;
1

3.2.4 Like模糊查询

MySQL 中的 LIKE 是一种用于模糊匹配字符串的操作符。LIKE 操作符可以用于 WHERE 子句中，用于筛选出符合指定模式的记录。

LIKE 操作符的语法如下：

SELECT column1, column2, ... FROM table_name WHERE column_name LIKE pattern;
1

其中，column1、column2 等表示要查询的列名，table_name 表示要查询的表名，column_name 表示要匹配的列名，pattern 表示要匹配的模式。

LIKE 操作符的模式可以使用通配符来表示，常用的通配符包括：

1. %：表示任意字符，可以匹配任意长度的字符串。

2. _：表示单个字符，可以匹配任意单个字符。

例如，如果要查询名字中包含 “张” 的学生记录，可以使用以下 SQL 语句：

SELECT * FROM student WHERE name LIKE '%张%';
1

如果要查询名字以 “张” 开头的学生记录，可以使用以下 SQL 语句：

SELECT * FROM student WHERE name LIKE '张%';
1

如果要查询名字以 “张” 结尾的学生记录，可以使用以下 SQL 语句：

SELECT * FROM student WHERE name LIKE '%张';
1

3.2.5 子查询

子查询指将一个查询语句嵌套在另一个查询语句中，可以在 SELECT、UPDATE 和 DELETE ，where子句中使用。外层的 SELECT 查询称为父查询，圆括号中嵌入的查询称为子查询（子查询必须放在圆括号内）。MySQL 在处理上例的 SELECT 语句时，执行流程为：先执行子查询，再执行父查询。
（1）IN | NOT IN
当表达式与子查询返回的结果集中的某个值相等时，返回 TRUE，否则返回 FALSE；若使用关键字 NOT，则返回值正好相反。
（2）EXISTS | NOT EXISTS
用于判断子查询的结果集是否为空，若子查询的结果集不为空，返回 TRUE，否则返回 FALSE；若使用关键字 NOT，则返回的值正好相反。

使用子查询在 tb_students_info 表和 tb_course 表中查询学习 Java 课程的学生姓名，SQL 语句和运行结果如下。

mysql> SELECT name FROM tb_students_info 
-> WHERE course_id IN (SELECT id FROM tb_course WHERE course_name = 'Java');
1
2
3
4

子查询也常常与操作符搭配使用查询数据。

3.3.join

SQL语句基本语法格式：
 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 >
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

3.3.1left join（左外连接）

A left join B 得到A表的所有字段，如果没有匹配到连接条件则用null填充
例如：

select A.*,B.* from A left join B on A.id = B.id;   //定义A∩B不等于空集，这里表示取A集合的全部
1

3.3.2right join（右外连接）

A right join B 得到B表所有的字段
例如：

select A.*,B.* from A right join B on A.id=B.id;  //这里表示取B集合的全部
1

3.3.3. inner join（内连接）

A inner join B得到（A和B的交集）

select A.*,B.* from A inner join B on A.id=B.id;
1

3.3.4CROSS JOIN（交叉连接）

交叉连接可以查询两个或两个以上的表，语法格式如下：

SELECT <字段名> FROM <表1> CROSS JOIN <表2> [WHERE子句]
或
SELECT <字段名> FROM <表1>, <表2> [WHERE子句] 

说明：
字段名：需要查询的字段名称。
<表1><表2>：需要交叉连接的表名。
WHERE 子句：用来设置交叉连接的查询条件。
1
2
3
4
5
6
7
8

注意：多个表交叉连接时，在 FROM 后连续使用 CROSS JOIN 或, 即可。以上两种语法的返回结果是相同的，但是第一种语法才是官方建议的标准写法。

当连接的表之间没有关系时，可以省略掉 WHERE 子句，这时返回结果就是两个表的笛卡尔积，返回结果数量就是两个表的数据行相乘。如果每个表有 10 行，那么返回结果的数量就有 10*10=100 行。

3.3.5 join的查询使用

1.查询（A -  A∩B）： select A.*,B.* from A left join B on A.id=B.id where B.id is null;
2.查询 （ B - A∩B ）：select A.*,B.* from A right join B on A.id=B.id where A.id is null;
3.查询（A∪B - A∩B） select A.*,B.* from A left join B on A.id=B.id where B.id is null
                    union
                    select A.*,B.* from A right join B on A.id=B.id where A.id is null;
 //利用union去重将上面的第1、第2种两条sql中间用union连接即可完成；即先完成一小部分的，然后将两个拼起来的思想。
 4.查询 AUB   select A.*,B.* from A left join B on A.id=B.id
              UNION
              select A.*,B.* from A right join B on A.id=B.id;
 //MySQL中求并集可以使用union关键字进行处理（自动去重）
1
2
3
4
5
6
7
8
9
10

3.4.group by

GROUP BY语法可以根据给定数据列的每个成员对查询结果进行分组统计，最终得到一个分组汇总表。
select子句中的列名必须为分组列或列函数，列函数对于group by子句定义的每个组返回一个结果。
某个员工信息表结构和数据如下：

id  name  dept  salary  edlevel    hiredate 
1    张三  开发部 2000     3          2009-10-11
2    李四  开发部 2500     3          2009-10-01
3    王五  设计部 2600     5   2010-10-02
4    王六  设计部 2300     4       2010-10-03
5    马七  设计部 2100     4       2010-10-06
6    赵八  销售部 3000     5       2010-10-05
7    钱九  销售部 3100     7       2010-10-07
8    孙十  销售部 3500     7       2010-10-06
1
2
3
4
5
6
7
8
9

列出每个部门最高薪水的结果，sql语句如下：

select dept , max(salary) AS MAXIMUM
FROM STAFF
GROUP BY DEPT
1
2
3

查询结果如下：

dept       MAXIMUM
开发部      4500
设计部      2600
销售部      3500
1
2
3
4

解释一下这个结果：

1、 满足“SELECT子句中的列名必须为分组列或列函数”，因为SELECT有group by中包含的列dept;

2、“列函数对于group by子句定义的每个组各返回一个结果”，根据部门分组，对每个部门返回一个结果，就是每个部门的最高薪水。

将where子句与group by子句一起使用

分组查询可以在形成组和计算列函数之前具有消除非限定行的标准where子句。必须在group by子句之前指定where子句

例如，查询公司2010年入职的各个部门每个级别里的最高薪水

SELECT dept,edlevel,MAX(salary) AS MAXIMUM
FROM STAFF
WHERE hiredate > '2010-01-01'
GROUP BY dept,edlevel
1
2
3
4

查询结果如下：

dept     edlevel     MAXIMUM
设计部      4           2300
设计部      5         2600
销售部      5         3000
销售部      7         3500
1
2
3
4
5

在SELECT语句中指定的每个列名也在GROUP BY子句中提到，未在这两个地方提到的列名将产生错误。GROUP BY子句对dept和edlevel的每个唯一组合各返回一行。

3.5.having

GROUP BY子句之后使用Having子句

可应用限定条件进行分组，以便系统仅对满足条件的组返回结果。因此，在GROUP BY子句后面包含了一个HAVING子句。HAVING类似于WHERE（唯一的差别是WHERE过滤行，HAVING过滤组）AVING支持所有WHERE操作符。

例如，查找雇员数超过2个的部门的最高和最低薪水：

SELECT dept ,MAX(salary) AS MAXIMUM ,MIN(salary) AS MINIMUM
FROM STAFF
GROUP BY dept
HAVING COUNT(*) > 2
ORDER BY dept
1
2
3
4
5

查询结果如下：

dept        MAXIMUM             MINIMUM
设计部      2600       2100
销售部      3500       3000
1
2
3

例如，查找雇员平均工资大于3000的部门的最高薪水和最低薪水:

SELECT dept,MAX(salary) AS MAXIMUM,MIN(salary) AS MINIMUM
FROM STAFF
GROUP BY dept
HAVING AVG(salary) > 3000
ORDER BY dept
1
2
3
4
5

查询结果如下：

dept    MAXIMUM   MINIMUM
销售部          3500       3000
1
2

3.6.order by

MySQL 中的 ORDER BY 是一种用于对查询结果进行排序的操作。ORDER BY 可以用于 SELECT 语句中，用于按照指定的列对查询结果进行排序。

ORDER BY 的语法如下：

SELECT column1, column2, ... FROM table_name ORDER BY column_name [ASC|DESC];
1

其中，column1、column2 等表示要查询的列名，table_name 表示要查询的表名，column_name 表示要排序的列名，ASC 表示升序排序，DESC 表示降序排序。

例如，如果要按照学生的成绩对查询结果进行降序排序，可以使用以下 SQL 语句：

SELECT * FROM student ORDER BY score DESC;
1

如果要按照学生的年龄和成绩对查询结果进行升序排序，可以使用以下 SQL 语句：

SELECT * FROM student ORDER BY age ASC, score ASC;
1

3.7.limit

在MySQL中，LIMIT是一个用于限制查询结果集的关键字。它可以用于SELECT语句中，用于指定从查询结果集中返回的行数。LIMIT语法如下：

SELECT column1, column2, ...
FROM table_name
LIMIT [offset,] row_count;
1
2
3

其中，row_count表示要返回的行数，offset表示要跳过的行数。如果省略offset，则默认为0，表示从第一行开始返回。如果省略row_count，则表示返回所有行。例如，LIMIT 10表示返回前10行，LIMIT 5, 10表示从第6行开始返回10行。LIMIT语句通常与ORDER BY语句一起使用，用于对查询结果进行排序。

3.8 UNION操作符

UNION 操作符是 MySQL 中用于合并两个或多个 SELECT 语句结果集的操作符，可以帮助开发人员更加灵活地进行数据查询和处理。
UNION 操作符的语法如下：

SELECT column1, column2, ... FROM table1
UNION
SELECT column1, column2, ... FROM table2;
1
2
3

其中，column1、column2 等表示要查询的列名，table1、table2 等表示要查询的表名。

特点：

1. UNION 操作符只能合并 SELECT 语句的结果集，不能合并其他类型的语句。

2. UNION 操作符合并的结果集中不包含重复的行，如果需要包含重复的行，可以使用 UNION ALL 操作符。

3. UNION 操作符合并的结果集中的列数必须相同，且列的数据类型必须兼容。

4. UNION 操作符合并的结果集中的列名是根据第一个 SELECT 语句的列名确定的，如果需要指定列名，可以使用别名。

5. UNION 操作符的效率较低，因为它需要将多个结果集合并成一个结果集，并去除重复的行。

Union 与 Join 的区别
Union 和 Join 子句不同，因为 union 总是垂直组合结果集，而 join水平附加输出。我们可以通过以下图例来理解

四.拓展

4.1. DISTINCT

在 MySQL 中使用 SELECT 语句执行简单的数据查询时，返回的是所有匹配的记录。如果表中的某些字段没有唯一性约束，那么这些字段就可能存在重复值。为了实现查询不重复的数据，MySQL 提供了 DISTINCT 关键字。

DISTINCT 关键字的主要作用就是对数据表中一个或多个字段重复的数据进行过滤，只返回其中的一条数据给用户。

DISTINCT 关键字的语法格式为：

SELECT DISTINCT <字段名> FROM <表名>;
1

其中，“字段名”为需要消除重复记录的字段名称，多个字段时用逗号隔开。

使用 DISTINCT 关键字时需要注意以下几点：

DISTINCT 关键字只能在 SELECT 语句中使用。
在对一个或多个字段去重时，DISTINCT 关键字必须在所有字段的最前面。
如果 DISTINCT 关键字后有多个字段，则会对多个字段进行组合去重，也就是说，只有多个字段组合起来完全是一样的情况下
才会被去重。
1
2
3
4

因为 DISTINCT 只能返回它的目标字段，而无法返回其它字段，所以在实际情况中，我们经常使用 DISTINCT 关键字来返回不重复字段的条数。

4.2. AS设置别名

表名或字段名很长或者执行一些特殊查询的时候，为了方便操作，可以为表指定一个别名，用这个别名代替表原来的名称。
基本语法格式为：

<表名>/<字段名> [AS] <别名>
注意：表的别名不能与该数据库的其它表同名。字段的别名不能与该表的其它字段同名。在条件表达式中不能使用字段的别名，否则会出现“ERROR 1054 (42S22): Unknown column”这样的错误提示信息。
AS关键字可以省略，省略后需要将字段名和别名用空格隔开。

表别名只在执行查询时使用，并不在返回结果中显示。
而字段定义别名之后，会返回给客户端显示，显示的字段为字段的别名。
1
2

这里注意一下数字要用双引号包裹，否则会报错。

---

1. 拓展
   Doctrine ORM 是一个基于 PHP 的对象关系映射（ORM）工具，它提供了一种将对象映射到关系型数据库的方法。
   它的主要目的是将开发人员从编写 SQL 查询和处理数据库连接的繁琐工作中解放出来，从而使开发更加高效和简单。
   Doctrine ORM 提供了一系列功能，包括：

   1. 实体映射：将 PHP 对象映射到数据库表中的行。
   2. 查询语言：使用 Doctrine Query Language（DQL）进行数据库查询。
   3. 数据库迁移：使用 Doctrine Migrations 进行数据库迁移和版本控制。
   4. 缓存：提供了多种缓存机制，包括文件缓存、APC 缓存和 Memcached 缓存等。
   5. 事件系统：提供了一个事件系统，可以在 ORM 操作的各个阶段触发事件。
   6. 数据库连接：提供了多种数据库连接方式，包括 PDO、MySQLi 和 Oracle 等。
      Doctrine ORM 的优点包括：
   7. 简化了数据库操作：使用 Doctrine ORM 可以将数据库操作简化到最小，从而提高开发效率。
   8. 提高了代码的可读性和可维护性：使用 Doctrine ORM 可以将数据库操作转化为面向对象的操作，使代码更加易读易维护。
   9. 支持多种数据库：Doctrine ORM 支持多种数据库，包括 MySQL、PostgreSQL、Oracle 和 Microsoft SQL Server 等。
   10. 提供了丰富的功能：Doctrine ORM 提供了丰富的功能，包括实体映射、查询语言、数据库迁移、缓存和事件系统等。
       总之，Doctrine ORM 是一个功能强大的 ORM 工具，可以帮助开发人员更加高效地进行数据库操作，提高代码的可读性和可维护性。
   ↩︎