DQL基本语法（查询）

下面的查询基于的表有：

•  "Websites" 表：

•  access_log表：

• students表：

 

1、DQL的基本介绍

DQL（Data Query Language，查询数据）：DQL就是用来查询表中的数据的，为用户提供查询数据的能力，这也是通常最频繁的数据库日常操作。

 

2、基本查询（select）

要查询数据库表的数据，我们使用如下的SQL语句：

SELECT * FROM <表名>;
# 示例：
SELECT * FROM students;  # 查询students表的所有数据

查询结果也是一个二维表，它包含列名和每一行的数据。

SELECT语句其实并不要求一定要有FROM子句：

SELECT 100+200;

上述查询会直接计算出表达式的结果。虽然SELECT可以用作计算，但它并不是SQL的强项。但是，不带FROM子句的SELECT语句有一个有用的用途，就是用来判断当前到数据库的连接是否有效。许多检测工具会执行一条SELECT 1;来测试数据库连接。

 

2.1、起别名（AS或者空格）

当我们想给查询出来的字段另起一个名字时，可以用 AS 关键字或者直接在该字段后面加空格然后加别名的方式来给查询出的字段重新命名：

SELECT name as my_name from websites;    -- as 关键字起别名
 
SELECT name my_name from websites;  -- 直接用空格添加别名也行

 

3、条件查询（where、AND、OR、NOT、<>）

条件查询语法：

SELECT * FROM <表名> WHERE <条件表达式>
# 示例：
SELECT * FROM students WHERE score >= 80;

常用的条件表达式有：

=、>、>=、<、<=、<>（不等于，在 SQL 的一些版本中，不等于可被写成 !=）、like（相似）

 

3.1、AND（并且）

条件表达式可以用<条件1> AND <条件2>表达满足条件1并且满足条件2，代码示例：

SELECT * FROM students WHERE score >= 80 AND gender = 'M';  # 假设gender列存储的是字符串，那就需要用单引号括起来

 

3.2、OR（或者）

也可以用<条件1> OR <条件2>来表示满足条件1或者满足条件2，代码示例：

SELECT * FROM students WHERE score >= 80 OR gender = 'M';

 

3.3、<>、NOT（不等于）

还有NOT <条件>，表示“不符合该条件”的记录。NOT条件其实等价于<>，因此，NOT查询不是很常用。代码示例：

SELECT * FROM students WHERE NOT class_id = 2;
# 等价于：
SELECT * FROM students WHERE class_id <> 2;

 

3.4、小括号() 

要组合三个或者更多的条件，就需要用小括号()表示如何进行条件运算。例如，编写一个复杂的条件：分数在80以下或者90以上，并且是男生：

SELECT * FROM students WHERE (score < 80 OR score > 90) AND gender = 'M';

如果不加括号，条件运算按照 NOT > AND > OR 的优先级进行，即 NOT 优先级最高，其次是 AND，最后是 OR。加上括号可以改变优先级。

 

3.5、between...and...（查询在某个区间的值）

between and 可以查询在某个区间之间的值：

Select * from access_log where count between 10 and 100;

查询结果：

 

3.6、in（查询属于某个集合内的值）

可以用 in 列出一个集合，查询字段的值属于该集合内的行：

SELECT * FROM access_log WHERE count IN (45, 10);

查询结果：

 

3.7、exists（过滤符合条件的数据）

exists 关键字该语法可以理解为：将主查询的数据，放到子查询中做条件验证，根据验证结果（TRUE或FALSE）来决定主查询的数据结果是否得以保留。（子查询里面应该用到外面的表SQL才能正常执行）

比如查询为：

SELECT a,b FROM table1
WHERE EXISTS(
    SELECT c FROM table2 WHERE t1.id=table2.id
) 

这里面的EXISTS是如何运作呢？子查询返回的是 c 字段，可是外面的查询要找的是 a 和 b 字段，这两个字段肯定不在 c 里面啊，这是如何匹配的呢？

实际上 EXISTS 用于指定一个子查询，在该子查询里面过滤外面表的符合条件的数据，该子查询实际上并不返回任何数据，而是返回值True或False。也就是说，实际上是在子查询里面过滤外面的表里属于 true 的数据，而子查询返回什么并不重要，比如可以是 select *、select 1、select 'xxx'，官方说法是实际执行时会忽略 select 返回的值，因此返回什么并无区别。

上面的 SQL 等价于：

SELECT a,b FROM table1 t1
WHERE EXISTS(
    SELECT * FROM table2 WHERE t1.id=table2.id
) 

 

exists 语句的特点：

• EXISTS(subquey)只返回TRUE或FALSE，因此子查询中的SELECT * 也可以是 SELECT 1 或select ‘X’，官方说法是实际执行时会忽略SELECT清单，因此没有区别。
• EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比，如果担忧效率问题，可进行实际检验以确定是否有效率问题。
• EXISTS子查询往往也可以用条件表达式，其他子查询或者JOIN来替代，何种最优需要具体问题具体分析

 

3.8、LIKE（模糊查询）

可以使用 LIKE 进行模糊查询，比如：LIKE '%abc%'、LIKE '_abc_'，其中占位符 _  表示任意单个字符，% 表示任意多个（即0或者多个）字符。

SELECT * FROM apps WHERE url LIKE '%qq%';

查询结果：

 

SELECT * FROM apps WHERE app_name LIKE '_宝%';

查询结果：

 

4、去除重复（SELECT DISTINCT）

在表中，一个列可能会包含多个重复值，我们可以使用 SELECT DISTINCT 语句来去除重复值，返回某个字段的所有唯一值。

比如查询 website 表中 country 字段的所有值并去除重复：

SELECT DISTINCT country FROM Websites;

查询结果：

 当用 distinct 来查询多列时，是查询多列组合起来没有重复的，如果指定的列当中有一列不一样，则认为不是重复，也会查询出来。比如：

SELECT DISTINCT country,name FROM Websites;

此时认为只有当 country 和 name 字段的值都一样才认为是重复，否则不认为是重复。如果有多组不重复的则都会被查出来。

 

5、排序查询（ORDER BY）

可以用 ORDER BY 关键字来对查询出来的结果集进行排序，可以对一个列或者多个列进行排序。ORDER BY 关键字默认按照升序（ASC）对记录进行排序，如果需要降序，可以使用 DESC 关键字。

如果对多个字段进行排序时，先比较字段1，如果字段1的值一样就会比较字段2的值，以此类推。

语法：

ORDER BY 字段1 排序方式1, 字段2 排序方式2...

-- 示例
SELECT * FROM access_log ORDER BY count ASC;

查询结果：

 

6、聚合函数（COUNT、MAX、MIN、SUM、AVG）

SQL 提供的聚合函数：

• COUNT：计算个数
• MAX：计算最大值
• MIN：计算最小值
• SUM：计算和
• AVG：计算平均值

聚合函数的查询结果仍然是一个二维表，只是这个二维表只有一行一列，并且列名是 关键字(字段名)，类似于 SUM(num)


注意，聚合函数的计算不会把 null 值计算在内。比如某个表有3条记录，但某个记录的 name 字段的值为 null，则用 count(name) 计算列数时不会把这条记录计算进去，即得出的结果只有 2 条记录。

示例：

SELECT count(id) FROM websites;

SELECT MAX(count) FROM access_log;

SELECT MIN(count) FROM access_log;

SELECT SUM(count) FROM access_log;

SELECT AVG(count) FROM access_log;

 

7、分组查询（GROUP BY）

我们可以用 group by 关键字来进行分组查询。分组查询可以理解为分类。

示例：

SELECT gender, AVG(score) FROM students GROUP BY gender;

查询结果：

在进行分组后，select 查询的字段一般会是分组字段、聚合函数，或者是 where 查询条件里面的字段，或者是跟这些字段有强关联的字段，比如用字段 id 进行分组，每个 id 对应着唯一的 name，则此时也可以查询 name 字段的值。

除了上述一些字段，使用 group by 时查询其他的字段意义不大。比如上面如果此时查询 id 字段，虽然 SQL 不会报错，但只会查询出第一个有分组字段的记录，没有什么实际意义，有时还有可能会查询出错误的数据。

 

7.1、对多个字段进行分组

GROUP BY x1, x2 意思是只有当 x1 和 x2 都相同才认为是同一个组，否则都会认为是另一个不同的组。对多个字段进行分组时，实际上是对筛选为同一个组多加了限制条件。

示例：

SELECT count(1),class_id,gender FROM `students` GROUP BY class_id,gender;

结果：

 

7.2、HAVING（对分组之后的数据进行过滤）

在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用。通过 having 关键字就可以将聚合函数放在作为筛选条件来过滤查询记录，HAVING 子句可以让我们筛选分组后的各组数据。

示例：

SELECT name, gender, AVG(score) FROM students WHERE score > 50 GROUP BY gender HAVING count(id) >= 5;   -- 先筛选score大于50的数据。筛选完之后，得到分组数据，然后只保留分组之后的所有的记录数量大于等于5的分组数据

查询结果：

where 在分组之前对数据进行过滤，如果不满足条件将不会参与分组。而 having 是对分组之后得到的数据进行过滤，如果不满足则不会被查询出来。

 

8、分页查询（limit）

当数据量过大时，一般我们会采用分页查询，即每次只查询固定数量的记录。

比如，MySQL 的分页语句是：

select * from students limit 开始索引, 一页查询的数量;

其中 开始的索引 = （当前页码-1）* 每页的数量

示例，每次查询 3 条记录：

SELECT * FROM students LIMIT 0,3;   -- 第一页，查询第1~第3条记录

SELECT * FROM students LIMIT 3,3;   -- 第二页，查询第4~第6条记录

查询结果：

               

需要注意，Oracle 和 MySQL 的分页查询语句并不一样。

 

9、null值的比较和运算

9.1、null 值的比较

在MySQL中，NULL是一个特殊的值，代表未知或缺失的值。MySQL中，NULL与任何值的比较（包括NULL本身）都会返回NULL，而不是TRUE或FALSE。例如，NULL = NULL，NULL < 10，NULL > 5等比较都会返回NULL而不是FALSE。

• NULL和任何值都不能比较
• NULL值与任何其它值的比较（即使是NULL）结果都是 NULL，永远不会为“真”
• NULL只能用is null 或者is not null 来判断，不能用=或者！=来判断

所以说在查询中用 = 或 != 都不会包含 null 值的数据，要想包含应该用 is null。示例如下：

• 查询结果1：

select
    *
from
    access_log lg
left join websites web
    on lg.aid = web.id

 

• 结果2：

select
    *
from
    access_log lg
left join websites web
    on lg.aid = web.id
where web.country = 'CN'

select
    *
from
    access_log lg
left join websites web
    on lg.aid = web.id
where web.country != 'CN'

分别是：

 都不会包含 null 值，要想包含 null 值，应该用 is null，如下：

select
    *
from
    access_log lg
left join websites web
    on lg.aid = web.id
where web.country = 'CN' or web.country is null