SQL系列 - 查询语句

SQL - 这篇文章属于一个选集。

§ 3: 本文

1. SELECT 语句
#

SELECT 语句用于从表中选取数据。结果被存储在一个结果表中（称为结果集）。

SELECT 列名称 FROM 表名称
SELECT * FROM 表名称
SELECT LastName,FirstName FROM Persons

2. DISTINCT
#

在表中，可能会包含重复值。可以用DISTINCT仅仅列出不同的值。

SELECT DISTINCT 列名称 FROM 表名称
SELECT DISTINCT Company FROM Orders

3. WHERE 子句
#

SELECT 列名称 FROM 表名称 WHERE 列 运算符 值

在 SQL 语句中，聚合函数不能出现在 WHERE 子句中。
- 因为 WHERE 子句用于对行进行过滤，而聚合函数是对行组进行操作，它们是在数据被检索之后、但在分组之前计算的。因此，在 WHERE 子句中使用聚合函数是不合法的。

3.1. 常用操作符
#

操作符	描述
=	等于
<>	不等于
>	大于
<	小于
>=	大于等于
<=	小于等于
BETWEEN	在某个范围内
LIKE	搜索某种模式

在某些版本的 SQL 中，操作符 <> 可以写为 !=

示例：

SELECT * FROM Persons WHERE City='Beijing'

-- 文本值
-- 这是正确的：
SELECT * FROM Persons WHERE FirstName='Bush'
-- 这是错误的：
SELECT * FROM Persons WHERE FirstName=Bush

-- 数值
-- 这是正确的：
SELECT * FROM Persons WHERE Year>1965
-- 这是错误的：
SELECT * FROM Persons WHERE Year>'1965'

3.2. AND 和 OR 运算符
#

AND 和 OR 可在 WHERE 子语句中把两个或多个条件结合起来。
如果第一个条件和第二个条件都成立，则满足 AND 运算。
如果第一个条件和第二个条件中只要有一个成立，则满足 OR 运算。

示例：

SELECT * FROM Persons 
WHERE FirstName='Thomas' AND LastName='Carter'

SELECT * FROM Persons 
WHERE firstname='Thomas' OR lastname='Carter'

SELECT * FROM Persons 
WHERE (FirstName='Thomas' OR FirstName='William') 
AND LastName='Carter'

4. ORDER BY 语句
#

ORDER BY 语句用于根据指定的列对结果集进行排序。
ORDER BY 语句默认按照升序对记录进行排序。
如果您希望按照降序对记录进行排序，可以使用 DESC 关键字。

示例：

-- 以字母顺序显示公司名称：
SELECT Company, OrderNumber FROM Orders ORDER BY Company

-- 以字母顺序显示公司名称（Company），并以数字顺序显示顺序号（OrderNumber）
SELECT Company, OrderNumber FROM Orders ORDER BY Company, OrderNumber

-- 以逆字母顺序显示公司名称：
SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC

-- 以逆字母顺序显示公司名称，并以数字顺序显示顺序号
SELECT Company, OrderNumber 
FROM Orders 
ORDER BY Company DESC, OrderNumber ASC

5. TOP 子句
#

TOP 子句用于规定要返回的记录的数目。
对于拥有数千条记录的大型表来说，TOP 子句是非常有用的。
并非所有的数据库系统都支持 TOP 子句。

示例：

-- MySQL 语法
SELECT column_name(s)
FROM table_name
LIMIT number

-- 实例
SELECT *
FROM Persons
LIMIT 5

6. 通配符和 LIKE 操作符
#

在搜索数据库中的数据时，SQL 通配符可以替代一个或多个字符。
SQL 通配符必须与 LIKE 运算符一起使用。
在 SQL 中，可使用以下通配符：

通配符	描述
%	替代一个或多个字符
_	仅替代一个字符
[charlist]	字符列中的任何单一字符
[^charlist]或者[!charlist]	不在字符列中的任何单一字符

LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern

从"Persons" 表中选取居住在以 “N” 开始的城市里的人

SELECT * FROM Persons
WHERE City LIKE 'N%'

从"Persons" 表中选取居住在以 “g” 结尾的城市里的人

SELECT * FROM Persons
WHERE City LIKE '%g'

从 “Persons” 表中选取居住在包含 “lon” 的城市里的人

SELECT * FROM Persons
WHERE City LIKE '%lon%'

从 “Persons” 表中选取居住在不包含 “lon” 的城市里的人

SELECT * FROM Persons
WHERE City NOT LIKE '%lon%'

从"Persons" 表中选取名字的第一个字符之后是 “eorge” 的人

SELECT * FROM Persons
WHERE FirstName LIKE '_eorge'

从 “Persons” 表中选取姓氏以 “C” 开头，然后是一个任意字符，然后是 “r”，然后是任意字符，然后是 “er”

SELECT * FROM Persons
WHERE LastName LIKE 'C_r_er'

从"Persons" 表中选取居住的城市以 “A” 或 “L” 或 “N” 开头的人

SELECT * FROM Persons
WHERE City LIKE '[ALN]%'

从"Persons" 表中选取居住的城市不以 “A” 或 “L” 或 “N” 开头的人

SELECT * FROM Persons
WHERE City LIKE '[!ALN]%'

7. JOIN 语句
#

有时为了得到完整的结果，需要执行 join从两个或更多的表中获取结果。
数据库中的表可通过键将彼此联系起来。
每个主键的值都是唯一的。这样做的目的是在不重复每个表中的所有数据的情况下，把表间的数据交叉捆绑在一起。

7.1. INNER JOIN
#

在表中存在至少一个匹配时，INNER JOIN 返回所有匹配的行。

SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName

7.2. LEFT JOIN
#

从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行。

SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
LEFT JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName

7.3. RIGHT JOIN
#

从右表 (table_name2) 那里返回所有的行，即使在左表 (table_name1) 中没有匹配的行。

SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
RIGHT JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName

7.4. FULL JOIN
#

返回所有行, 匹配不上的对应列保留空值。

SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name=table_name2.column_name

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
FULL JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName

8. UNION 语句
#

用于合并两个或多个 SELECT 语句的结果集。
UNION 内部的 SELECT 语句必须拥有相同数量, 相似数据类型, 相同顺序的列。
UNION 操作符默认选取不同的值。如果允许重复的值，请使用 UNION ALL。

SELECT column_name(s) FROM table_name1 
UNION SELECT column_name(s) FROM table_name2

SELECT column_name(s) FROM table_name1 
UNION ALL SELECT column_name(s) FROM table_name2

SELECT E_Name FROM Employees_China 
UNION SELECT E_Name FROM Employees_USA

SELECT E_Name FROM Employees_China 
UNION ALL SELECT E_Name FROM Employees_USA

9. 子查询
#

在 SQL 中，子查询通常可以添加在 SELECT、FROM、WHERE 或 HAVING 子句中
但不能直接添加在 GROUP BY 或者 ON 子句中。
- 不能在 GROUP BY 子句中直接使用子查询，因为 GROUP BY 子句用于指定分组的列，而子查询返回的结果不一定与主查询的数据行一致，所以在这种情况下是不允许的。
- 同样，在 JOIN 的 ON 子句中也不能直接使用子查询，因为 ON 子句用于指定连接条件，而子查询返回的结果可能不是一个条件表达式。
下面是一些常见的用法：

SELECT 子句：子查询可以用于在 SELECT 子句中返回一个值或一组值，例如获取一个聚合函数的结果。
```
SELECT column1, (SELECT MAX(column2) FROM table2) AS max_value
FROM table1;
```
FROM 子句：子查询可以作为表来使用，从而充当 FROM 子句中的一个数据源。
```
SELECT column1
FROM (SELECT * FROM table1 WHERE condition) AS subquery;
```

WHERE 子句：子查询可以用于限制主查询中返回的行。

SELECT column1
FROM table1
WHERE column2 IN (SELECT column2 FROM table2 WHERE condition)

HAVING 子句：子查询可以用于对 GROUP BY 分组后的结果进行过滤。

SELECT column1, COUNT(*)
FROM table1
GROUP BY column1
HAVING COUNT(*) > (SELECT AVG(count_column) FROM count_table);

10. 窗口函数
#

窗口函数是一种高级SQL技术，可以在查询结果的子集（窗口）上执行计算。窗口函数可以用来解决许多常见的数据分析问题，例如计算排名、累积和、移动平均等。

窗口函数基本语法如下：

‹窗口函数› over (partition by ‹用于分组的列名›  order by ‹用于排序的列名›)

<窗口函数> 的位置可以放下面两种类型函数：
- 专用窗口函数：RANK, DENSE_RANK, ROW_NUMBER
- 聚合函数：SUM, AVG, COUNT, MAX, MIN等
RANK()：计算结果集中每一行的排名，如果有相同的值，则会出现相同的排名，并且下一个排名会跳过相同数量的值。
DENSE_RANK()：与 RANK() 类似，但是不会跳过排名。如果有相同的值，它们会得到相同的排名，下一个排名会按照排名的顺序递增。
ROW_NUMBER()：为结果集中的每一行分配一个唯一的数字，按照指定的排序顺序分配。不会对数据进行聚合操作。
NTILE(n)：将结果集划分为 n 个相同大小的桶，并为每一行分配一个桶号，其中 1 表示最小的桶，n 表示最大的桶。
PATITION BY 用于分组，如果不分组可以不用，从而对整个表进行rank或者其它操作
ORDER BY 的顺序决定了赋予rank的顺序

下面是一些例子：

对每个班级的成绩进行排名

SELECT *, 
       RANK() OVER (PARTITION BY 班级 ORDER BY 成绩 DESC) 
        AS ranking 
FROM 班级表;

与前两个学生取平均（每次一共3个学生），如果前面不够两个学生就取自己或者前一个

SELECT *, 
       AVG(成绩) OVER (ORDER BY 学号 ROWS 2 PRECEDING) 
        AS current_avg 
FROM 班级表;

计算每个销售日期及其前三天的销售总额

SELECT
    sales_date,
    sales_amount,
    SUM(sales_amount) OVER 
        (ORDER BY sales_date 
            ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) 
        AS rolling_sum
FROM
    sales;

计算每个销售日期及其后三天的销售总额

SELECT
    sales_date,
    sales_amount,
    SUM(sales_amount) OVER 
        (ORDER BY sales_date 
            ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING) 
        AS rolling_sum
FROM
    sales;

查找每个学生成绩最高的2个科目（由于 SQL 执行顺序，需要子查询先计算出ranking再判断成绩最高的两个科目）

SELECT * FROM (
		SELECT *, 
		       ROW_NUMBER() OVER 
                    (PARTITION BY 姓名 ORDER BY 成绩 DESC) 
               AS ranking  
		FROM 成绩表) AS a 
WHERE ranking ‹= 2;

查找单科成绩高于该科目平均成绩的学生名单

SELECT * FROM (
		SELECT *, 
		       avg(成绩) OVER (PARTITION BY 科目) 
                AS avg_score             
		FROM 成绩表) AS b 
WHERE 成绩 › avg_score;

11. 优化SQL查询性能
#

使用合适的索引
- 创建索引：为频繁查询的列创建索引，尤其是用于WHERE、JOIN、ORDER BY和GROUP BY子句的列。
- 复合索引：为多个列组合创建复合索引，特别是在查询中经常一起使用的列。
- 避免过多索引：索引会占用磁盘空间，并在插入、更新和删除操作时增加开销，因此要平衡索引数量和性能。
优化查询语句
- 选择必要的列：在SELECT语句中只选择必要的列，而不是使用SELECT *。
- 使用WHERE子句过滤数据：使用WHERE子句过滤数据，减少返回的行数。
- 避免复杂的计算和函数：在WHERE子句中避免使用复杂的计算和函数，因为这可能会阻止索引的使用。
优化JOIN操作
- 选择合适的JOIN类型：使用INNER JOIN、LEFT JOIN、RIGHT JOIN等根据需求选择合适的JOIN类型。
- 减少JOIN数量：尽量减少JOIN的数量，优化表的设计和查询逻辑。
- 确保JOIN列有索引：确保用于JOIN的列上有索引，以提高JOIN操作的效率。
使用子查询和CTE
- 避免嵌套子查询：嵌套子查询可能会导致性能问题，可以使用JOIN或CTE（Common Table Expression）替代。
- 使用CTE：CTE可以使查询更清晰，有时还可以提高查询性能。
优化排序和分组
- 使用合适的排序方法：在ORDER BY和GROUP BY子句中，确保排序的列有索引。
- 限制排序的行数：在ORDER BY子句中使用LIMIT来限制返回的行数。
调整数据库设计
- 规范化和反规范化：根据需求平衡数据库的规范化和反规范化，规范化减少数据冗余，反规范化减少JOIN操作。
- 分区表：对于大表，可以使用表分区技术来提高查询性能。
使用EXPLAIN分析查询计划
- EXPLAIN语句：使用EXPLAIN语句分析查询计划，了解查询的执行顺序和索引使用情况。
- 优化查询计划：根据EXPLAIN的输出，调整索引、查询语句和数据库设计，以优化查询计划。
配置数据库参数
- 调整缓冲区大小：根据硬件配置调整数据库的缓冲区大小，如InnoDB的缓冲池大小。
- 连接池：使用连接池技术减少连接创建和销毁的开销。
- 其他参数：调整数据库服务器的其他参数，如缓存大小、最大连接数等，以提高性能。