【重学MySQL】十三、基本的 select 语句

【重学MySQL】十三、基本的 select 语句

基本的SELECT语句是SQL（Structured Query Language，结构化查询语言）中最常用的语句之一，用于从数据库表中检索数据。一个基本的SELECT语句的结构非常直观，主要包括SELECT关键字、要检索的列名（或表达式）、FROM子句来指定数据来源的表名，以及可选的WHERE子句来指定检索数据的条件。

基本结构

SELECT column1, column2, ...
FROM table_name
WHERE condition;

• SELECT：指定要从表中检索的列名。你可以指定一个或多个列名，或者使用星号（*）来检索表中的所有列。
• FROM：指定要从中检索数据的表名。
• WHERE（可选）：指定用于过滤结果的条件。只有满足条件的行才会被检索出来。如果省略了WHERE子句，那么会检索表中的所有行。

示例

假设有一个名为Employees的表，其中包含员工的ID、姓名、年龄和部门ID等信息。

检索所有列

SELECT * FROM Employees;

这条语句会检索Employees表中的所有列和所有行。

检索特定列

SELECT EmployeeID, LastName, FirstName FROM Employees;

这条语句只会检索EmployeeID、LastName和FirstName这三列的数据。

带有条件的检索

SELECT * FROM Employees WHERE DepartmentID = 5;

这条语句会检索Employees表中所有DepartmentID为5的行。

dual

在MySQL中，DUAL是一个虚拟表，它主要用于那些不需要从任何表中检索数据但需要返回一个结果集的场合。DUAL表允许你执行没有指定FROM子句的SELECT语句，这在一些情况下特别有用，比如当你只是想通过SELECT语句来执行一些计算或转换，而不是查询表中的数据时。

然而，值得注意的是，虽然DUAL在Oracle数据库中是一个常见的概念，但在MySQL中，它并不是严格必需的，因为MySQL允许你执行没有FROM子句的SELECT语句。这意味着，在MySQL中，你可以直接写出一个只包含SELECT和可能的一些函数的查询，而不需要显式地引用DUAL表。

例如，在Oracle中，你可能会这样写：

SELECT 5 * 10 FROM DUAL;

这条语句会返回50，因为Oracle要求所有的SELECT语句都必须指定一个FROM子句，即使你并不打算从任何表中检索数据。

但在MySQL中，你可以直接写：

SELECT 5 * 10;

这条语句同样会返回50，而不需要引用DUAL表。

尽管MySQL不严格要求使用DUAL，但在一些从Oracle迁移到MySQL的数据库项目中，你可能会遇到在代码中显式引用DUAL的情况。在这种情况下，MySQL仍然能够处理这样的查询，因为MySQL会忽略这个DUAL的引用，并直接执行SELECT语句中的计算或函数。

总的来说，DUAL在MySQL中是一个可选的概念，主要用于与那些期望在所有数据库系统中都有DUAL表的概念的代码兼容。但在实际使用中，你通常可以省略它，直接在MySQL中执行你的SELECT语句。

列的别名

在MySQL中，列的别名（Alias）是一个给查询结果集中的列指定的临时名称。使用列的别名可以使结果集更加易于理解，特别是在进行复杂的查询、连接（JOINs）、分组（GROUP BY）和聚合（如SUM、AVG等）操作时。

基本的列别名使用

在SELECT语句中，你可以通过在列名后紧跟AS关键字和别名来指定列的别名。如果省略AS关键字，MySQL也会识别紧随列名之后的标识符为别名，假如中间有空格，可以用一对双引号引起来，不要使用单引号。

示例1：使用AS关键字

SELECT employee_name AS Name, salary AS Salary
FROM employees;

在这个例子中，employee_name列的别名被指定为Name，salary列的别名被指定为Salary。

示例2：省略AS关键字

SELECT employee_name Name, salary Salary
FROM employees;

这个查询与上一个示例产生相同的结果，只是省略了AS关键字。

别名在表达式中的使用

列的别名还可以在SELECT语句的表达式中使用，但需要注意的是，别名在定义它的SELECT列表中是不可见的，也就是说，你不能在同一个SELECT列表的另一个表达式中直接使用它。然而，你可以在HAVING子句、ORDER BY子句或外部查询中引用它（如果这是一个子查询或视图定义的一部分）。

示例：不能在同一个SELECT列表的表达式中直接使用别名

-- 错误的用法
SELECT salary AS Salary, Salary + 100 AS SalaryPlusBonus  -- 这里Salary是未知的，因为别名是在它之后定义的
FROM employees;

-- 正确的用法
SELECT salary, salary + 100 AS SalaryPlusBonus
FROM employees;

在ORDER BY子句中使用别名

SELECT employee_name AS Name, salary AS Salary
FROM employees
ORDER BY Salary DESC;  -- 这里使用了别名Salary进行排序

别名在聚合函数中的应用

在使用聚合函数（如SUM、AVG、COUNT等）时，为结果列指定别名尤其有用，因为它可以清晰地表示该列包含的数据类型或含义。

示例：使用聚合函数并指定别名

SELECT AVG(salary) AS AverageSalary
FROM employees;

这个查询计算了employees表中所有员工的平均薪水，并将结果列的别名指定为AverageSalary。

distinct

在MySQL中，DISTINCT关键字用于在查询结果中返回唯一不同的值。当你从表中检索数据时，如果表中有重复的行，并且你只希望看到每个唯一值一次，那么就可以使用DISTINCT。

DISTINCT通常与SELECT语句一起使用，放在需要返回唯一值的列名之前。你也可以对多个列使用DISTINCT，但这意味着MySQL会考虑这些列的组合作为唯一性的判断依据。

基本用法

单列使用DISTINCT

SELECT DISTINCT column_name
FROM table_name;

这个查询会返回column_name中所有唯一的值。

多列使用DISTINCT

SELECT DISTINCT column1, column2
FROM table_name;

这个查询会返回column1和column2的组合中所有唯一的值对。只有当column1和column2的组合是唯一的时，它们才会出现在结果集中。

注意事项

• DISTINCT关键字作用于它之后的所有列，直到遇到下一个逗号或查询的结束。
• 使用DISTINCT可能会影响查询的性能，因为它需要MySQL对结果集进行排序和去重。在大数据集上使用时尤其要注意这一点。
• 如果查询中包含了聚合函数（如COUNT()、MAX()、MIN()、SUM()等），并且你想要基于某些列的唯一值来计算聚合结果，那么可能需要结合GROUP BY子句来使用，而不是直接使用DISTINCT。

示例

假设有一个名为employees的表，其中包含department_id和employee_name两列。

返回所有唯一的department_id

SELECT DISTINCT department_id
FROM employees;

返回所有唯一的department_id和employee_name组合

注意：这实际上会返回所有行，因为假设每个员工都位于不同的部门或每个部门都有不同的员工名称（这通常不是真实情况，但用于说明）。

SELECT DISTINCT department_id, employee_name
FROM employees;

如果每个部门都有多个员工，但你想要按部门计算员工数，你应该使用GROUP BY而不是DISTINCT。

按部门计算员工数

SELECT department_id, COUNT(employee_name) AS employee_count
FROM employees
GROUP BY department_id;

空值参与运算

在MySQL中，空值（NULL）参与运算时，结果通常会根据运算的类型和上下文而有所不同。空值在数据库中代表缺失或未知的数据，因此在进行数学运算、字符串连接或其他类型的计算时，需要特别注意它们的行为。

数学运算

当NULL参与数学运算（如加法、减法、乘法、除法等）时，结果通常是NULL。这是因为MySQL无法对未知的值进行数学计算。

SELECT NULL + 10;  -- 结果为 NULL
SELECT 10 - NULL;  -- 结果为 NULL
SELECT NULL * 10;  -- 结果为 NULL
SELECT 10 / NULL;  -- 结果为 NULL，尽管在真实数学中除以0是未定义的，但这里是因为有NULL

字符串连接

当使用CONCAT()等字符串函数时，如果任何参数是NULL，则整个CONCAT()函数的结果也是NULL。不过，可以使用CONCAT_WS()（其中WS代表With Separator），它允许在参数之间插入分隔符，并且会忽略NULL值。

SELECT CONCAT('Hello', NULL, 'World');  -- 结果为 NULL
SELECT CONCAT_WS(' ', 'Hello', NULL, 'World');  -- 结果为 'Hello World'

比较运算

当NULL参与比较运算时，结果也通常是特殊的。例如，任何值与NULL的比较结果都不是TRUE或FALSE，而是NULL。这意味着你不能直接使用=、<、>等操作符来检查一个值是否为NULL。相反，应该使用IS NULL或IS NOT NULL。

SELECT 1 = NULL;  -- 结果为 NULL
SELECT 1 IS NULL;  -- 结果为 FALSE
SELECT NULL IS NULL;  -- 结果为 TRUE

逻辑运算

在逻辑运算中（如AND、OR、NOT），NULL的行为可能会更加复杂，因为它既不是TRUE也不是FALSE。但是，通常你可以预期如果逻辑表达式中的任何部分为NULL，则整个表达式的结果可能也是NULL，或者至少不会按你期望的TRUE或FALSE来评估。

处理NULL的函数

MySQL提供了几个函数来帮助处理NULL值，如IFNULL()（或COALESCE()），它们允许你为NULL值指定一个替代值。

SELECT IFNULL(NULL, 0) + 10;  -- 结果为 10
SELECT COALESCE(NULL, 0, 'default') + 10;  -- 结果为 10，因为COALESCE返回第一个非NULL值

总之，当在MySQL中编写查询并处理可能包含NULL值的列时，重要的是要了解NULL在不同运算和函数中的行为，并相应地调整你的查询逻辑。

着重号

在MySQL中，“着重号”（通常指的是反引号`）主要用于标识符（如数据库名、表名、列名等）的引用，特别是在这些标识符是MySQL的保留字或者包含特殊字符（如空格、连字符等）时。使用反引号可以确保这些标识符被正确地识别和处理。

为什么使用着重号（反引号）？

1. 避免保留字冲突：如果你的表名或列名与MySQL的保留字相同，使用反引号可以避免语法错误。
2. 支持特殊字符：如果你的标识符包含空格、连字符（-）、点（.）等特殊字符，或者以数字开头，使用反引号可以确保MySQL能够正确解析这些标识符。

示例

避免保留字冲突

假设你想创建一个名为order的表，但order是MySQL的保留字。为了避免冲突，你可以这样写：

CREATE TABLE `order` (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(255)
);

支持特殊字符

如果你想创建一个包含空格的表名，你可以这样做：

CREATE TABLE `my table` (
    id INT AUTO_INCREMENT PRIMARY KEY,
    data VARCHAR(255)
);

或者，如果你的列名包含连字符：

CREATE TABLE example (
    `user-id` INT,
    `first-name` VARCHAR(50)
);

注意事项

• 虽然在某些情况下使用反引号是有必要的，但过度使用可能会使SQL语句看起来杂乱无章，因此建议仅在必要时使用。
• 在某些SQL方言（如PostgreSQL）中，使用双引号（"）而不是反引号来引用标识符。
• 当使用MySQL命令行工具时，你可能需要根据你的操作系统和MySQL的配置，使用特定的转义字符来在命令行中包含反引号。例如，在Windows命令行中，你可能需要使用`order`（注意外部的双引号用于命令行字符串的界定，内部的反引号用于SQL标识符的界定）。然而，在许多现代的IDE和数据库管理工具中，这个问题通常会自动得到处理。

查询常数

注意

• 在使用SELECT语句时，应尽量避免使用*来检索所有列，特别是当表中有大量列而你只需要其中几列时。这样做可以提高查询效率，减少数据传输量。
• 在WHERE子句中使用的条件可以是任何有效的表达式，包括比较运算符（如=、<>、<、>等）、逻辑运算符（如AND、OR、NOT）等。
• SQL是大小写不敏感的，但出于可读性和一致性考虑，推荐使用特定的命名约定（如关键字大写，表名和列名小写）。
• 不同的数据库系统（如MySQL、SQL Server、Oracle等）可能在SQL方言上有所不同，但基本的SELECT语句在大多数系统中都是通用的。