25.1 MySQL SELECT语句

1. SQL概述

1.1 SQL背景知识

1946年, 世界上诞生了第一台电脑, 而今借由这台电脑的发展, 互联网已经成为一个独立的世界.
在过去几十年里, 许多技术和产业在互联网的舞台上兴衰交替.
然而, 有一门技术却从未消失, 甚至日益强大, 那就是SQL.

SQL(Structured Query Language, 结构化查询语言)是一种使用关系模型的数据库应用语言, 它的设计初衷是用来与数据直接交互.
上世纪70年代, IBM的研究员发布了一篇关于数据库技术的论文<<SEQUEL: 一门结构化的英语查询语言>>, 这可以说是SQL的奠基之作.
而到了现如今, 虽然有一些细微的变化, 但SQL作为一门结构化的查询语言并没有发生太大的变化.

无论是前端工程师还是后端算法工程师, 都需要与数据打交道, 并且需要了解如何高效地提取他们所需的数据.
尤其是对于数据分析师而言, 他们的工作就是与数据打交道, 整理不同的报告以引导业务决策.

SQL有多个版本和标准, 最重要的是SQL92和SQL99.
它们分别代表了1992年和1999年发布的SQL标准.
我们今天使用的SQL语言仍然遵循这些标准.

需要提醒的是, 不同的数据库厂商支持SQL语句, 但每个厂商可能会有一些特殊的内容或扩展.
因此, 在使用SQL时需要根据具体的数据库系统来选择合适的语法和功能.

TIOBE编程语言排行榜是一个根据搜索引擎查询结果和编程语言讨论的数据来评估编程语言受欢迎程度的指标.

SQL之所以一直保持高排名, 是因为它作为一种用于管理关系型数据库的标准语言, 被广泛应用于各个行业和领域.
无论是大型企业还是小型公司, 几乎都需要使用关系型数据库进行数据存储和查询.
而SQL作为关系型数据库的标准查询语言, 成为了处理和管理数据的核心工具.

排行榜地址: https://www.tiobe.com/tiobe-index/

1.2 SQL分类

SQL是一种用于管理和操作关系型数据库的标准化查询语言.
根据应用领域和功能特点, 可以将SQL分为以下几个主要的分类:
* 1. 数据定义语言(DDL): DDL语句用于定义和管理数据库的结构, 包括创建, 修改和删除数据库, 表, 索引, 视图等.
     常见的DDL语句包括CREATE, ALTER和DROP等.

* 2. 数据操作语言(DML): DML语句用于对数据库中的数据进行操作, 包括查询, 插入, 更新和删除数据.
     常见的DML语句包括SELECT, INSERT, UPDATE和DELETE等.

* 3. 数据查询语言(DQL): DQL语句用于从数据库中提取数据, 常用于查询和检索数据的需求.
     DQL的核心语句是SELECT语句, 可以通过选择特定的列, 过滤条件和排序来获取需要的数据.

* 4. 数据控制语言(DCL): DCL语句用于控制数据库用户的访问权限和数据完整性, 包括授权和回收权限, 事务处理和数据约束等.
     常见的DCL语句包括GRANT, REVOKE和COMMIT等.

* 5. 事务控制语言(TCL): TCL语句用于管理和控制事务, 可用于维护数据的一致性和完整性.
     常见的TCL语句包括BEGIN TRANSACTION, COMMIT和ROLLBACK等.

* 6. 数据库管理语言(DML): DML语句用于管理数据库的元数据, 包括数据库, 表和索引的创建, 修改和删除.
     常见的DML语句包括CREATE DATABASE, CREATE TABLE和CREATE INDEX等.

除了以上分类, 还有其他一些衍生的SQL语言和扩展, 如存储过程语言(如PL/SQL和T-SQL), 面向对象数据库语言(如SQL/OLB)等,
它们通过扩展SQL的功能, 提供了更强大和灵活的数据库操作能力.

1.3 SQL语言规则和约定

当涉及到使用SQL语言时, 遵守一些规则和规范是至关重要的.
遵守以下的规则和规范将有助于提高SQL代码的可读性, 可维护性和一致性.
以下是SQL语言的一些常用规则和约定:

* 1. 编写风格:
     - 为了增加可读性, 可以将SQL语句写在一行或多行, 并使用适当的缩进.
     - 每条语句应以分号(;), \g或\G结尾, 以此标志语句的结束.

# 多行书写, 分号结束:
SELECT 
	column1, column2
FROM
	table_name
WHERE 
	condition;
-- condition: 表示条件.

* 2. 标点符号:
     - 必须使用成对的括号, 单引号和双引号, 并正确结束(落单就会报错).
     - 在输入SQL语句时, 必须使用英文状态下的半角输入方式(只能识别英文符号, 不能识别中文符号!).
     - 使用单引号('')表示字符串型和日期时间类型的数据(双引号可以使用, 但是不推荐).
     - 使用双引号("")给字段取别名(推荐使用as关键字取别名, 单引号可以使用, 但是不推荐).

# 字段别名使用AS关键字来指定:
SELECT id AS 编号, name AS 姓名 FROM table_name; 

# 如果字段别名中不包含空格或特殊字符, 那么AS关键字可以省略, 简写格式如下:
SELECT id 编号, name 姓名 FROM table_name; 

# 如果字段别名中有空格, 那么不能省略, 必须使用引号:
SELECT id AS "编 号", name AS "姓 名" FROM table_name;  

* 3. 大小写规范(建议遵守):
     在大多数数据库系统中, 如MySQL, Oracle, SQL Server等, 
     数据库名, 表名, 表的别名和变量名, SQL关键字, 函数名, 列名和列的别名通常是不区分大小写的.
     虽然数据库系统本身可能不区分大小写, 但某些配置或设置可能会影响这种行为, 
     例如, 在MySQL中, 如果你使用了lower_case_table_names系统变量, 那么表名的大小写将根据该变量的设置而变化.
 
     尽管在日常操作中可以忽略大小写, 但为了保持代码的一致性和可读性, 许多开发者和数据库管理员仍会遵循某种命名约定,
     如使用小写字母和下划线来命名数据库和表(例如my_database和my_table), 以及使用大写字母来标识SQL关键字和命名函数.
     -----------------------------------------------------------------------------------------------------
     注意点: 数据库, 表, 字段, 别名, 函数, 变量名等, 在Windows环境下MySQL对大小写不敏感,
     但在Linux环境下MySQL对大小写是敏感的, 需要严格区分大小写.
     这种行为差异主要是由于操作系统对大小写的处理方式不同所导致的.
     
     在Windows环境下, 由于Windows系统的文件系统默认是不区分大小写的(例如FAT32或NTFS),
     因此MySQL数据库系统在这种环境下对大小写也不敏感.
     这意味着你可以使用大写或小写字母来命名数据库, 表, 别名, 变量名等, 而不会遇到任何问题.

     举个例子, 假设你在Windows环境下使用MySQL创建一个名为"MyDatabase"的数据库,
     你可以使用"MyDatabase", "mydatabase", "MYDATABASE"等任何大小写组合来引用它.

     然而, 在Linux环境下, 情况就有所不同了.
     Linux系统的文件系统默认是区分大小写的(例如ext4), 因此MySQL数据库系统在这种环境下对大小写是敏感的.
     这意味着你必须严格区分大小写来引用数据库, 表, 别名, 变量名等.

     举个例子, 假设你在Linux环境下使用MySQL创建一个名为"MyDatabase"的数据库,
     如果你尝试使用"mydatabase"或"MYDATABASE"等大小写组合来引用它, 将会收到一个错误信息, 指示找不到指定的数据库.
     你必须确保使用与创建数据库时相同的大小写来引用它, 即"MyDatabase".

# 以下是一个查询表格信息的语句格式(关键字大写, 其余名称小写):
SELECT
	column1, column2
FROM 
	table_name
WHERE 
	condition;

* 4. 注释:
     - 可以使用适当的注释结构来注释代码, 增加代码的可读性和可理解性.
     - 行级注释: # 注释单行 (MySQL特有的方式).
     - 行级注释：-- 注释单行 (-- 后面必须包含一个空格).
     - 块级注释：/* 注释多行 */ .

# 行行级注释

-- 行级注释

/*
块级注释1
块级注释2
...
*/

* 5. 命名规则:
     - 数据库名和表名不得超过30个字符, 变量名限制为29个字符.
     - 对象名只能包含A–Z, a–z, 0–9和下划线, 共计63个字符.
     - 对象名中不应包含空格.
     - 在同一个MySQL软件中, 数据库名和表名应保持唯一性(注意建立库和表的时间不要出现重名的情况).
     - 如果字段名与数据库系统保留关键字冲突时, 应使用``(着重号)引起来.

# 创建表命为学生信息的语句:
# create table student info(...); # 表名错误, 因为表名有空格
CREATE CREATE student_info(...);

# order与关键字重复, 使用需要``包裹:
CREATE TABLE order;  # 执行后报错, order是保留的关键字.
CREATE TABLE `order`;  # 可正常执行.

* 6. 保持字段名和类型的一致性:
     - 在为字段命名并指定其数据类型时, 应确保一致性.
     - 如果一个表中的某一字段是整数类型, 同名字段在其他表中也应保持整数类型.
       统一的数据类型使得数据库设计更加清晰和可维护.

# 创建两张表, 表中同名的字段使用相同的数据类型:
CREATE CREATE student(id int, name varchat, ...);
CREATE CREATE teacher(id int, name varchat, ...);

2. 导入数据库

首先学习数据的查询, 这里准备了一张表, 将数据导入到数据自己MySQL数据库中.

.sql文件: 是包含SQL(结构化查询语言)命令的纯文本文件.
SQL用于管理关系数据库系统, 例如MySQL, Oracle, PostgreSQL等.
这些文件通常包含数据库结构的定义(如创建表, 视图和触发器等)或者包含用于插入, 更新或删除数据的SQL语句.

2.1 SOURCE导入语句

SOURCE导入语句: 用于从文件中执行SQL语句.
当你有一个包含多条SQL语句的文本文件时, 可以使用SOURCE语句来执行这个文件中的所有语句.

语句格式: SOURCE /path/to/your/file.sql;
其中, /path/to/your/file.sql是包含SQL语句的文件的路径.

-- MySQL 8.1 使用关键字语句导入的方式, 这条语句可以不需要;结尾:
mysql> source C:\Users\13600\Desktop\atguigudb.sql
Query OK, 0 rows affected, 1 warning (0.00 sec)
...

# 查看数据库语句:
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| atguigudb          |  -- 增加这个库, 其他四个数据库是数据库系统自带的后续会介绍.
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test1              |
+--------------------+
6 rows in set (0.02 sec)

2.2 Navicat导入

* 1. 先使用Navicat 连接MySQL 5.7的服务器.

* 2. 选中数据右击选择运行SQL文件.

* 3.选择sql文件的路径-->点击开始进行导入.

* 4. 导入成功后点开关闭.

* 5. 刷新后展示数据库.

3. USE切换数据库语句

USE语句: 用于选择或切换到指定的数据库.

语句格式: USE database_name;
其中, database_name是你想要切换到的数据库的名称.

使用USE语句后, 就可以直接在该数据库中执行其他的SQL语句.

-- 确认使用的数据库, 这个语句可以省了分号结尾(不建议省略):
mysql> use atguigudb  # 往后基于这个库学习一段时间.
Database changed  # 提示数据库已更改.

4. DESC查看表结构语句

DESC语句: 用于描述表的结构.
当你对一个表使用DESC语句时, 它会返回该表的所有列及其相关的属性, 如数据类型, 是否允许为空, 默认值等.

语句格式: DESC table_name;
其中table_name是表的名称.

-- 查看员工表的结构信息:
mysql> desc employees;                    
+----------------+-------------+------+-----+---------+-------+
| Field          | Type        | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| employee_id    | int         | NO   | PRI | 0       |       |
| first_name     | varchar(20) | YES  |     | NULL    |       |
| last_name      | varchar(25) | NO   |     | NULL    |       |
| email          | varchar(25) | NO   | UNI | NULL    |       |
| phone_number   | varchar(20) | YES  |     | NULL    |       |
| hire_date      | date        | NO   |     | NULL    |       |
| job_id         | varchar(10) | NO   | MUL | NULL    |       |
| salary         | double(8,2) | YES  |     | NULL    |       |
| commission_pct | double(2,2) | YES  |     | NULL    |       |
| manager_id     | int         | YES  | MUL | NULL    |       |
| department_id  | int         | YES  | MUL | NULL    |       |
+----------------+-------------+------+-----+---------+-------+
11 rows in set (0.01 sec)

其中, 各个字段的含义如下(大概了解即可, 后续会详细说明):
* 1. Field: 表示字段名称.
* 2. Type: 表示字段类型(后续创建表格时会详细介绍数据类型).
* 3. Null: 表示该列是否可以存储NULL值.
* 4. Key: 表示该列是否已编制索引.
     PRI表示该列是表主键的一部分;
     UNI表示该列是UNIQUE索引的一部分;
     MUL表示在列中某个给定值允许出现多次.
* 6. Default: 表示该列是否有默认值.
* 6. Extra: 表示可以获取的与给定列有关的附加信息, 例如AUTO_INCREMENT(自动递增主键)等.

执行SQL查询语句后会返回结果末尾返回一个执行结果, 例如: 11 rows in set (0.01 sec) .
这个消息告诉你有多少行数据被返回, 以及查询执行了多长时间(秒).

5. SELECT查询语句

SELECT语句:　用于从数据库中选择数据的主要语句.
可以使用SELECT语句来检索数据, 对数据进行排序, 以及执行各种其他操作.

SELECT语句通常与FROM子句一起使用, 用于从数据库表中选择和检索数据.
但是, 有一些情况下, 可以使用SELECT语句而不使用FROM子句.

这种情况通常发生在你不需要从特定的表中选择数据, 而是想要返回一个常量值, 执行计算或者测试某些功能时.

下面是一些示例, 展示了如何在不使用FROM子句的情况下使用SELECT语句:

-- SELECT 1; 是一个简单的SQL语句, 它返回数字1.
-- 这个语句本身没有从任何数据库表中检索数据, 而是直接返回一个常量值. 一般用于"测试连接":
-- 当你想测试与数据库的连接是否正常时, 可以执行这个简单的查询来看是否返回结果, 而不必查询整个表或复杂的查询.
mysql> SELECT 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

这个语句在多种情境下可能有用:
* 1. 测试连接: 当你想测试与数据库的连接是否正常时, 可以执行这个简单的查询来看是否返回结果, 而不必查询整个表或复杂的查询.
* 2. 基准测试: 它可以用来测量数据库查询的延迟或响应时间, 因为这是一个非常简单和快速的查询.
* 3. SQL注入检测: 在某些安全测试场景中, 开发人员可能会使用这个查询来检测是否存在SQL注入的可能性.
* 4. 占位符: 在编写更复杂的SQL查询时, 开发人员可能会暂时使用这个查询作为占位符, 以便稍后进行替换.

-- 可以SELECT语句进行简单的计算, 执行 SELECT 9/2; 这个查询语句后, 将返回结果为4.5的列.
mysql> SELECT 9/2;
+--------+
| 9/2    |
+--------+
| 4.5000 |
+--------+
1 row in set (0.00 sec)

6. SELECT FROM检索语句

SELECT FROM语句: 是从数据库表中检索数据的基本语句, 常见两种语法: 
* 1. 选择全部字段查询语法: SELECT * FROM table_name;          -- 查询表中所有的字段的数据.
* 2. 选择字段查询语法:  SELECT column1, ... FROM table_name;  -- 查询部分字段的所有数据.
其中, column1表示某个字段, *表示所有字段.

一般情况下, 除非需要使用表中所有的字段数据, 最好不要使用通配符"*".
使用通配符虽然可以节省输入查询语句的时间, 但是获取不需要的列数据通常会降低查询的效率.
通配符的优势是, 当不知道所需要的列的名称时, 可以通过它获取它们.
在生产环境下或者不熟悉所要查询的表时, 不推荐直接使用 SELECT * 进行查询,
如果数据量过于庞大, 将所有的数据都加载的话可能会导致内存不足.

-- 查看整张部门表的数据:
mysql> SELECT * FROM departments;
+---------------+----------------------+------------+-------------+
| department_id | department_name      | manager_id | location_id |
+---------------+----------------------+------------+-------------+
|            10 | Administration       |        200 |        1700 |
|            20 | Marketing            |        201 |        1800 |
|            30 | Purchasing           |        114 |        1700 |
|            40 | Human Resources      |        203 |        2400 |
|            50 | Shipping             |        121 |        1500 |
|           ... | ...                  |         ...|         ... |  -- 省略
|           260 | Recruiting           |       NULL |        1700 |
|           270 | Payroll              |       NULL |        1700 |
+---------------+----------------------+------------+-------------+
27 rows in set (0.00 sec)

-- 查看部门表某些字段的数据:
mysql> SELECT department_id, department_name FROM departments;
+---------------+----------------------+
| department_id | department_name      |
+---------------+----------------------+
|            10 | Administration       | 
|            20 | Marketing            | 
|            30 | Purchasing           | 
|            40 | Human Resources      | 
|            50 | Shipping             | 
|           ... | ...                  |  -- 省略
|           260 | Recruiting           |
|           270 | Payroll              |
+---------------+----------------------+
27 rows in set (0.00 sec)

Navicat的查询方式:
* 1. 点击菜单栏的查询 --> 点击新建查询.

* 2. 选择服务器名称与数据库.

查询方式1: 
输入语句后点击运行, 会执行编辑器上所有的语句.
在下面的信息栏中可以点开结果的信息查看.

查询方式2: 
选中想要运行的语句, 这时执行选项会变成"运行已选择的"语句.

PS: 点击美化SQL, 将自动为SQL语句添加适当的缩进, 换行和空格, 以使其更易于阅读和理解.
如果没有选中需要优化的语句, 默认将所有语句美化.

7. AS语句

7.1 AS别名

AS语句: 用于为字段或表指定别名(起其别名, 最好起到见名知意).

起别名的好处主要包括以下几点:
* 1. 增加可读性: 别名可以使得数据更易于理解和解释.
     在某些情况下, 原始字段名可能过于复杂或含糊不清, 使用别名可以解决这个问题.
* 2. 简化操作: 通过给长字段名或复杂字段名设置别名, 可以简化查询和数据处理的操作.
* 3. 保护隐私: 在涉及到敏感数据的情况下, 别名可以用于隐藏原始字段名, 从而保护个人隐私.
* 4. 提高可维护性: 如果字段名需要更改, 只需更改别名而无需更改所有相关的查询和代码, 这可以降低维护成本.
总的来说, 别名可以使数据和查询更易于理解, 使用和维护.

以下是AS语句的一些基本用法:
* 1. 为字段指定别名: 当查询数据库时, 可以为返回的字段指定一个别名, 这样在结果集中该字段就会使用这个别名.
     示例: SELECT first_name AS FirstName, last_name AS LastName FROM employees;
     在这个例子中, first_name 字段在结果集中将被显示为 FirstName, last_name字段将被显示为LastName.
     
* 2. 为表指定别名: 在复杂的查询中, 特别是涉及多个表的查询(如连接查询), 为表指定别名可以使查询更加简洁.
     示例: SELECT e.FirstName, d.DepartmentName  FROM employees AS e  
          JOIN departments AS d ON e.department_id = d.id;
     在这个例子中, employees表被指定了别名e, 而departments表被指定了别名d.
     这使得在后续的查询中引用这些表时更加简洁.
     
* 3. 使用AS创建计算字段: 计算字段的值是基于查询中的其他字段计算得出的.
     示例: SELECT first_name, last_name, (salary + bonus) AS total_compensation FROM employees;
     在这个例子中, 一个新的字段 total_compensation 被创建出来, 它显示的是salary和bonus字段的和.

请注意, AS关键字是可选的, 你可以省略它而直接写别名, 但使用AS可以使SQL语句更易读.
不同的数据库系统可能对AS关键字的支持有所不同, 但大多数现代数据库系统都支持它.

-- 为department_id起一个别名为dept_id:
mysql> SELECT department_id AS dept_id, department_name AS dept_name FROM departments;
+---------+----------------------+
| dept_id | dept_name            |
+---------+----------------------+
|      10 | Administration       |
|      20 | Marketing            |
|      30 | Purchasing           |
|      40 | Human Resources      |
|      50 | Shipping             |
|      60 | IT                   |
|     ... | ...                  | -- 省略
|     270 | Payroll              |
+---------+----------------------+
27 rows in set (0.00 sec)

-- 查看员工表的信息(名字, 年收入):
mysql> SELECT first_name "name", salary * 12 FROM employees;
+-------------+-------------+
| name        | salary * 12 |  -- 该字段将显示为原始的计算表达式, 某些情况下对于阅读查询结果的人来说不太直观.
+-------------+-------------+
| Steven      |   288000.00 |
| Neena       |   204000.00 |
| Lex         |   204000.00 |
| Alexander   |   108000.00 |
| Bruce       |    72000.00 |
| ...         |         ... |  -- 省略
| Pat         |    72000.00 |
| Susan       |    78000.00 |
| Hermann     |   120000.00 |
| Shelley     |   144000.00 |
| William     |    99600.00 |
+-------------+-------------+
107 rows in set (0.00 sec)

-- 使用AS关键字的主要目的是为字段或计算字段提供一个别名, 这样在结果集中就可以使用这个别名来引用该字段.
-- 这在查询结果中使字段名更具有描述性, 并帮助提高代码的可读性.
mysql> SELECT first_name "name", salary * 12 AS "Annual Salary" FROM employees;
+-------------+---------------+
| name        | Annual Salary |
+-------------+---------------+
| Steven      |     288000.00 |
| Neena       |     204000.00 |
| Lex         |     204000.00 |
| Alexander   |     108000.00 |
| Bruce       |      72000.00 |
| ...         |           ... |  -- 省略
| Michael     |     156000.00 |
| Pat         |      72000.00 |
| Susan       |      78000.00 |
| Hermann     |     120000.00 |
| Shelley     |     144000.00 |
| William     |      99600.00 |
+-------------+---------------+
107 rows in set (0.00 sec)

7.2 AS查询常数

在SQL中, 可以使用SELECT语句对常数进行查询, 并在查询结果中添加一列固定的常数列.
这样做的原因可能是为了整合不同的数据源, 并使用常数列作为表的标记.

示例: SELECT 常数值 AS 常数列  FROM table_name;
示例中, 常数值是要查询返回的常数, 可以是数字, 字符串, 日期等任何有效的数据类型.
AS关键字后面的常数列是字段的名称, 可以根据您的需求进行命名.

查询常数与字段别名的语法是一样的.
虽然这两种情况的语法相似, 但它们在实际应用中有所不同.
一个是用于查询固定的常数值, 而另一个是用于查询表中字段的值并为其设置别名.

区分: 
如果表中没有该字段, 别名对应的列显示的所有值都将是AS前面的原名.
如果该字段存在, 则是显示字段对应的值.

如果你想对employees数据表中的员工的姓进行查询, 
同时增加一列字段corporation(团队), 这个字段的固定值为'MySQL', 可以使用以下查询语句:

-- 常数值是一个字符串需要使用引号引起来, 否则它会将MySQL当作是一个字段来查询, 这个字段不存就会报错.
-- 增加额外字段并为字段设置固定的值:
mysql> SELECT 'MySQL' AS corporation, last_name FROM employees;  
+-------------+-------------+
| corporation | last_name   |
+-------------+-------------+
| MySQL       | King        |
| MySQL       | Kochhar     |
| MySQL       | De Haan     |
| MySQL       | Hunold      |
| MySQL       | Ernst       |
| ...         | ...         |  -- 省略
| MySQL       | Higgins     |
| MySQL       | Gietz       |
+-------------+-------------+
107 rows in set (0.00 sec)

8. DISTINCT去重语句

默认情况下, 查询会返回全部行, 这些行中就可能包含一些重复的信息.

DISTINCT语句: 用于返回唯一的值.
当你在查询数据库时使用DISTINCT, 它会确保结果集中的每一行都是唯一的.

注意事项:
* 1. DISTINCT关键字需要放到所有字段的前面, 如果写成 SELECT salary, DISTINCT department_id FROM employees; 会报错.
* 2. DISTINCT会对后面所有字段进行组合去重.
     语法格式: SELECT DISTINCT column1, column2, ...  FROM table_name;
     在这个查询中, DISTINCT会确保column1和column2的组合是唯一的.
     也就是说, 只有当column1和column2的值同时相同时, 才会被视为重复, 并从结果集中删除.

-- 查看员工的部门id(一个部门下有多个员工):
mysql> SELECT department_id FROM employees;
+---------------+
| department_id |
+---------------+
|          NULL |
|            10 |
|            20 |
|            20 |
|            30 |
|            30 |
|            30 |
|           --- | -- 省略
|           110 |
|           110 |
+---------------+
107 rows in set (0.00 sec)

在SELECT语句中使用关键字DISTINCT去除重复行. 

-- 对员工的部门id去重(返回12条数据, 表明部门id有12种):
mysql>  SELECT DISTINCT department_id FROM employees;
+---------------+
| department_id |
+---------------+
|          NULL |
|            10 |
|            20 |
|            30 |
|            40 |
|            50 |
|            60 |
|            70 |
|            80 |
|            90 |
|           100 |
|           110 |
+---------------+
12 rows in set (0.00 sec)

-- 对员工的部门id和薪资进行组合去重, 当这两个条件的值都一样时才会去重:
mysql> SELECT DISTINCT department_id, salary FROM employees;
+---------------+----------+
| department_id | salary   |
+---------------+----------+
|            90 | 24000.00 |
|            90 | 17000.00 |
|            60 |  9000.00 |
|            60 |  6000.00 |
|            60 |  4800.00 |
|            60 |  4200.00 |
|            .. |    ..... | -- 省略
|            40 |  6500.00 |
|            70 | 10000.00 |
|           110 | 12000.00 |
|           110 |  8300.00 |
+---------------+----------+
74 rows in set (0.00 sec)
-- 部门id和salary的组合有74种.

-- 先提一嘴, 我就想去重某个字段, 还想展示别的字段的信息怎么办, 那么就要想想有没有这个需求的必要了(没有).
-- 看看下面的示例:
-- 如果想要返回与这些唯一department_id相关的salary(例如, 每个部门的最高薪水), 可以使用GROUP BY和聚合函数:
mysql> SELECT department_id, MAX(salary) as highest_salary FROM employees GROUP BY department_id;
+---------------+----------------+
| department_id | highest_salary |
+---------------+----------------+
|          NULL |        7000.00 |
|            10 |        4400.00 |
|            20 |       13000.00 |
|            30 |       11000.00 |
|            40 |        6500.00 |
|            50 |        8200.00 |
|            60 |        9000.00 |
|            70 |       10000.00 |
|            80 |       14000.00 |
|            90 |       24000.00 |
|           100 |       12000.00 |
|           110 |       12000.00 |
+---------------+----------------+
12 rows in set (0.00 sec)
-- 虽然GROUP BY本身不是为了去重而设计的, 但它确实可以间接地达到去重的效果.
-- 当你按某一列或多列进行分组时, 每个组内的这些列的值会是唯一的.
-- 使用GROUP BY后又必须为想要展示的字段设置额外的条件(因为有这个需求), 到此结束, 切莫钻牛角尖!!!

9. WHERE过滤子句

WHERE子句: 用于过滤记录, 只获取满足指定条件的记录.

语法格式: SELECT column2, column2, ... FROM 表名 WHERE 过滤条件;
WHERE子句后面可以跟各种条件, 包括比较运算符(=, !=, <, >, <=, >=), 逻辑运算符(AND, OR, NOT)以及其他SQL函数和操作符.

注意事项:
* 1. WHERE子句紧随FROM子句.
* 2. WHERE子句不能使用别名.

-- 例如, 过滤出部门编号为90的部分信息员工信息:
mysql> SELECT
    ->     employee_id,         -- 部门ID
    ->     first_name,          -- 名字
    ->     job_id,              -- 职位id
    ->     department_id        -- 部门id
    -> FROM
    ->     employees
    -> WHERE 
    ->     department_id = 90;  -- 匹配部门id = 90的行
+-------------+------------+---------+---------------+
| employee_id | first_name | job_id  | department_id |
+-------------+------------+---------+---------------+
|         100 | Steven     | AD_PRES |            90 |
|         101 | Neena      | AD_VP   |            90 |
|         102 | Lex        | AD_VP   |            90 |
+-------------+------------+---------+---------------+
3 rows in set (0.00 sec)

-- WHERE子句不能使用别名示例, 过滤出部门编号为90的部分信息员工信息:
mysql> SELECT employee_id, first_name, job_id, department_id AS "dept_id" FROM employees WHERE dept_id = 90;
ERROR 1054 (42S22): Unknown column 'dept_id' in 'where clause'
-- 错误1054(42S22): "where子句"中的未知列"dept_id".
-- 错误的原因: 过滤之前该别名还不存在!
-- sql语句会先执行FROM employees, 然后执行WHERE过滤条件, 最后执行SELECT筛选显示的字段, WHERE执行时别名还没有定义!

10. 空值与空字符串

在计算机科学中, 空值(NULL)和空字符串("")在存储和处理上有一些重要的区别.
在编程和数据库设计中, 正确处理NULL和空字符串是很重要的, 因为它们表示的含义和如何处理它们可能会有很大的差异.

* 1. NULL: 在数据库和许多编程语言中, NULL表示一个字段或变量的值是未知的或者不存在的.
     在物理存储层面, NULL通常不占用实际的存储空间.
     它仅仅是一个标识符, 表示某个位置的值没有被赋值,
     当你查询一个NULL字段时, 通常会返回NULL, 除非查询语句中特别处理了NULL值.

     在MySQL中, NULL是一种特殊的标记, 表示字段或数据项没有值或值未知.
     当一个字段被设置为NULL时, 表示该字段没有存储任何有效的数据.
     在存储NULL值的字段上, 不会分配任何实际的存储空间.
     相反, MySQL会在记录中存储一个指示该字段为NULL的标记.
     空值与任何其他值进行运算的结果通常都是NULL.
     这是因为空值表示缺失或未知的值, 因此无法与其他值进行准确的运算.
     在查询中, 需要使用 IS NULL 或 IS NOT NULL 运算符来判断某列是否为空值, 
     例如: SELECT * FROM table WHERE column IS NULL.

* 2. 空字符串(""): 这是一个长度为0的字符串, 但它确实占用了一定的存储空间.
     在大多数现代计算机系统中, 字符串是以字符数组的形式存储的, 所以即使是一个空字符串,
     也需要一个指针(或引用)指向这个字符数组, 以及存储字符数组本身的内存空间.
     
     在MySQL中, 空字符串是一个有效的数据项, 在查询中空字符串可以与其他字符串进行比较,
     例如: SELECT * FROM table WHERE column = "".
     空字符串对于可变长度字符串类型会占用1个字节的存储空间, 而对于固定长度字符串类型则会占用字段定义的存储空间.

以下是空值和空字符串的一些区别:
存储空间: NULL不需要占用实际的存储空间, 而空字符串("")需要占用一定的存储空间, 因为它是一个实际存在的值.
数据比较: 在MySQL中, NULL不能和任何其他值进行直接比较, 包括自身. 而空字符串("")可以进行比较操作.
数据处理: 在进行数据插入或更新操作时, 如果没有为字段指定值, MySQL将默认将其设置为NULL, 而不是空字符串("").

-- 部门字段可以参加运算, 这些字段的值可以进行数学运算或逻辑运算, 以产生新的计算结果.
-- 计算员工的年薪: 年薪 = 月薪 * (1 + 佣金百分比commission_pct) * 12:
mysql> SELECT
    ->     employee_id,                                       -- 员工id
    ->     salary,                                            -- 月薪
    ->     commission_pct,                                    -- 佣金百分比
    ->     salary * (1 + commission_pct) * 12                 -- 年薪 * 佣金比较
    -> AS                                                     -- 使用AS将字段 salary * (1 + commission_pct) * 12   
    ->     "annual_sal"                                       -- 年薪别名
    -> FROM 
    ->    employees;
+-------------+----------+----------------+------------+
| employee_id | salary   | commission_pct | annual_sal |
+-------------+----------+----------------+------------+
|         100 | 24000.00 |           NULL |       NULL |  -- 空值与任何其他值进行运算的结果通常都是NULL.
|         ... |    ...   |            ... |        ... |  -- 省略
|         145 | 14000.00 |           0.40 |  235200.00 |
+-------------+----------+----------------+------------+
107 rows in set (0.00 sec)

如果commission_pct的值为NULL, 那么在计算年薪时会出现问题, 因为NULL值参与运算后会导致计算结果是NULL.
为了处理这种情况, 可以使用COALESCE函数或IFNULL函数来将NULL值替换为0或其他默认值. 
COALESCE函数示例: COALESCE(commission_pct, 0), 如果commission_pct的值为NULL, 则返回0; 否则返回commission_pct本身的值.
这样可以确保在计算年薪时不会因为NULL值而出现错误.

以下是使用COALESCE函数修改后的查询语句:

mysql> SELECT
    ->     employee_id,                                              -- 员工id
    ->     salary,                                                   -- 月薪
    ->     commission_pct,                                           -- 佣金
    ->     12 * salary * (1 + COALESCE(commission_pct, 0))
    -> AS
    ->     "annual_sal"                                              -- 年薪
    -> FROM 
    ->    employees;
+-------------+----------+----------------+------------+
| employee_id | salary   | commission_pct | annual_sal |
+-------------+----------+----------------+------------+
|         100 | 24000.00 |           NULL |  288000.00 |
|         ... |    ...   |            ... |        ... | -- 省略
|         145 | 14000.00 |           0.40 |  235200.00 |
+-------------+----------+----------------+------------+

11. 练习

-- 1.查询员工的年薪(加上佣金比例), 并起别名为ANNUAL SALARY:
mysql> SELECT
    -> salary * (1 + COALESCE(commission_pct, 0)) * 12
    -> AS
    ->     "ANNUAL SALARY"
    -> FROM
    ->     employees;
+---------------+
| ANNUAL SALARY |
+---------------+
|     288000.00 |
|     204000.00 |
|     204000.00 |
|     108000.00 |
|           ... |
+---------------+
107 rows in set (0.02 sec)

-- 2. 查询employees表中去除重复的job_id(职位id)后的数据:
mysql> SELECT DISTINCT
    ->     job_id
    -> FROM
    ->     employees;
+------------+
| job_id     |
+------------+
| AC_ACCOUNT |
| AC_MGR     |
| AD_ASST    |
| ...        |
| SA_REP     |
| SH_CLERK   |
| ST_CLERK   |
| ST_MAN     |
+------------+
19 rows in set (0.01 sec)

-- 3.查询工资大于12000的员工姓名和工资:
mysql> SELECT
    ->     first_name,
    ->     last_name,
    ->     salary
    -> FROM
    ->     employees
    -> WHERE
    ->     salary > 12000;
+------------+-----------+----------+
| first_name | last_name | salary   |
+------------+-----------+----------+
| Steven     | King      | 24000.00 |
| Neena      | Kochhar   | 17000.00 |
| Lex        | De Haan   | 17000.00 |
| John       | Russell   | 14000.00 |
| Karen      | Partners  | 13500.00 |
| Michael    | Hartstein | 13000.00 |
+------------+-----------+----------+
6 rows in set (0.00 sec)

-- 4.查询员工号为176的员工的姓名和部门号:
mysql> SELECT
    ->     first_name,
    ->     last_name,
    ->     department_id
    -> FROM
    ->    employees
    -> WHERE
    ->    employee_id = 176;
+------------+-----------+---------------+
| first_name | last_name | department_id |
+------------+-----------+---------------+
| Jonathon   | Taylor    |            80 |
+------------+-----------+---------------+
1 row in set (0.01 sec)

-- 5.显示表departments的结构, 并查询其中的全部数据:
mysql> DESC departments;
+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| department_id   | int         | NO   | PRI | 0       |       |
| department_name | varchar(30) | NO   |     | NULL    |       |
| manager_id      | int         | YES  | MUL | NULL    |       |
| location_id     | int         | YES  | MUL | NULL    |       |
+-----------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> SELECT
    ->     *
    -> FROM
    ->    departments;
+---------------+----------------------+------------+-------------+
| department_id | department_name      | manager_id | location_id |
+---------------+----------------------+------------+-------------+
|            10 | Administration       |        200 |        1700 |
|            20 | Marketing            |        201 |        1800 |
|           ... | ...                  |        ... |         ... |
|           270 | Payroll              |       NULL |        1700 |
+---------------+----------------------+------------+-------------+
27 rows in set (0.01 sec)

-- 6. 着重号的使用:
-- 设计表名和字段时, 避免使用数据库的保留字或常用方法名称是非常重要的.
-- 如果使用这些名称, 可能会导致SQL查询出现错误或混淆.
-- 当名称冲突时, 可以使用反引号(``)将表名或字段名括起来; 反引号在键盘上通常与波浪号(~)共享一个键.

-- 查看库中所有表格, 其中有一张表是order, mysql中order是一个关键字, 主要用于对查询结果进行排序.
mysql> show tables;
+---------------------+
| Tables_in_atguigudb |
+---------------------+
| countries           |
| ...                 | -- 省略
| order               |
| regions             |
+---------------------+
10 rows in set (0.01 sec)

-- 错误的示例:
mysql> SELECT * FROM order;
ERROR 1064 (42000): You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the right syntax to use near 'order' at line 1

-- 错误 1064 (42000): 您的 SQL 语法有误;
-- 请检查与您的 MySQL 服务器版本相对应的手册, 以了解在第 1 行 'order' 附近应使用的正确语法.

-- 使用注重号:
mysql> SELECT * FROM `order`;
+----------+------------+
| order_id | order_name |
+----------+------------+
|        1 | shkstart   |
|        2 | tomcat     |
|        3 | dubbo      |
+----------+------------+
3 rows in set (0.00 sec)