SQL分类

语言在功能上主要分为如下3大类：

  • DDL（Data Definition Languages、数据定义语言），这些语句定义了不同的数据库、表、视图、索引等数据库对象，还可以用来创建、删除、修改数据库和数据表的结构。
    • 主要的语句关键字包括CREATE、DROP、ALTER等。
  • DML（Data Manipulation Language、数据操作语言），用于添加、删除、更新和查询数据库记录，并检查数据完整性。
    • 主要的语句关键字包括INSERT、DELETE、UPDATE、SELECT等。
    • SELECT是SQL语言的基础，最为重要。
  • DCL（Data Control Language、数据控制语言），用于定义数据库、表、字段、用户的访问权限和安全级别。
    • 主要的语句关键字包括GRANT、REVOKE、COMMIT、ROLLBACK、SAVEPOINT等。

      因为查询语句使用的非常的频繁，所以很多人把查询语句单拎出来一类：DQL(数据查询语言)。 还有单独将COMMIT、ROLLBACK 取出来称为TCL (Transaction Control Language，事务控制语言)。

SQL书写规范

  • SQL 可以写在一行或者多行。为了提高可读性，各子句分行写，必要时使用缩进
  • 每条命令以;或\g或\G结束
  • 关键字不能被缩写也不能分行
  • 关于标点符号
    • 必须保证所有的()、单引号、双引号是成对结束的
    • 必须使用英文状态下的半角输入方式
    • 字符串型和日期时间类型的数据可以使用单引号（’ ‘）表示
    • 列的别名，尽量使用双引号（” “），而且不建议省略as
  • 推荐采用统一的书写规范：
    • 数据库名、表名、表别名、字段名、字段别名等都小写
    • SQL 关键字、函数名、绑定变量等都大写

      插入数据

      不指定列名

      特点: 语法简单, 但不安全。并且各个列的值高度依赖列的定义顺序, 插入值的顺序必须以它们在表定义中出现的次序填充，不能省略列，没有值的列要使用NULL值。 ```sql — 插入一条数据 INSERT INTO tab_name VALUES ( value1, value2,…valueN );

— 插入多条数据 INSERT INTO table_name VALUES (value1 [,value2, …, valuen]), (value1 [,value2, …, valuen]), …… (value1 [,value2, …, valuen]);

  1. <a name="C4C14"></a>
  2. ## 指定列名
  3. 因为提供了列名,`VALUES`必须以其指定的次序匹配指定的列名，列名不一定按各个列出现在实际表中的次序,且可以省略某些列。<br />省略列的条件： 该列允许NULL值; 定义该列时给了出默认值。插值时如果对表中不允许NULL值且没有默认值的列不给出值,则MySQL报错且插入失败。<br />指定列名插值的优点：即使表的结构改变， 此INSERT语句仍然能正确工作。
  4. ```sql
  5. -- 插入一条数据 
  6. INSERT INTO tab_name 
  7. ( field1, field2,...fieldN ) 
  8. VALUES 
  9. ( value1, value2,...valueN ); 
  11. -- 插入多条数据 
  12. INSERT INTO tab_name 
  13. (field1, field2,...fieldN) 
  14. VALUES 
  15. (valueA1,valueA2,...valueAN), 
  16. (valueB1,valueB2,...valueBN), 
  17. ...... 
  18. (valueC1,valueC2,...valueCN);

插入检索出的数据

INSERT的另外一种形式是将一条SELECT语句的结果插入到表中，这是由一条INSERT语句和一条SELECT语句组成的。INSERT SELECT中SELECT语句可包含WHERE子句以过滤插入的数据。

  1. -- 语法格式
  2. INSERT INTO dest_tab     -- 目标表
  3. (tar_col1 [, tar_col2, ..., tar_coln])
  4. SELECT
  5. (tar_col1 [, tar_col2, ..., tar_coln])
  6. FROM src_tab                     -- 源表
  7. [WHERE condition]
  9. -- 示例
  10. INSERT INTO sales_reps(id, name, salary, commission_pct)
  11. SELECT employee_id, last_name, salary, commission_pct
  12. FROM   employees
  13. WHERE  job_id LIKE '%REP%';

更新数据

通过UPDATE 命令修改 MySQL 数据表数据的通用 SQL 语法：

  1. -- 带WHERE子句的UPDATE更新tab表中特定的行; 不带WHERE子句的UPDATE更新tab表所有的行 
  2. UPDATE IGNORE tab 
  3. SET field1 = new_value1, field2 = new_value2, .... 
  4. [WHERE conditions] 
  6. UPDATE employees
  7. SET    department_id = 70
  8. WHERE  employee_id = 113;

UPDATE语句中可以使用子查询，使得能用SELECT语句检索出的数据更新列数据。
如果用UPDATE 语句更新多行，并且在更新这些行中的一行或多行时出现错误，则整个UPDATE 操作被取消（错误发生前更新的所有行被恢复到它们原来的值）。为了即使是发生错误，也继续进行更新，可在UPDATE 后使用IGNORE关键字

删除数据

  1. 如果没有指定 WHERE 子句，MySQL 表中的所有记录将被删除; 通过指定where 语句则删除满足条件的部分记录;
  2. 可以在 WHERE 子句中指定任何条件；
  3. 可以在单个表中一次性删除记录。 删除表以及表结构参见笔记：删除表 ```sql — 语法 DELETE FROM tab_name [WHERE conditions]

— 示例 DELETE FROM departments WHERE department_name = ‘Finance’;

  1. <a name="KVDTx"></a>
  2. # 计算列
  3. MySQL8的新特性之一就是计算列。计算列就是某一列的值是通过别的列计算得来的，CREATE TABLE 和 ALTER TABLE 中都支持增加计算列。
  4. ```sql
  5. CREATE TABLE tb1(
  6.     id INT,
  7.     a INT,
  8.     b INT,
  9.     c INT GENERATED ALWAYS AS (a + b) VIRTUAL
  10. );
  11. INSERT INTO tb1(a,b) VALUES (100,200);
  12. SELECT * FROM tb1;
  13. +------+------+------+------+
  14. | id   | a    | b    | c    |
  15. +------+------+------+------+
  16. | NULL |  100 |  200 |  300 |
  17. +------+------+------+------+
  18. UPDATE tb1 SET a = 500;
  19. SELECT * FROM tb1;
  20. +------+------+------+------+
  21. | id   | a    | b    | c    |
  22. +------+------+------+------+
  23. | NULL |  500 |  200 |  700 |
  24. +------+------+------+------+

查询数据⭐️⭐️⭐️

基本查询

查询单列、多列、所有列数据:

  1. -- 从表tab_name中查询名字为col_name的列。多个列名之间用逗号间隔。 
  2. SELECT col_name FROM <tab_name>;
  3. -- 查询表tab_name中所有的列 
  4. SELECT * FROM <tab_name>;

去除重复数据：distinct一般是用来去除查询结果中的重复记录的，而且这个语句只可以在select中使用。distinct对NULL是不进行过滤的，即返回的结果中是包含NULL值的。具体的语法如下：

  1. -- distinct与distinctrow同义 
  2. select distinct|distinctrow col1[,col2...] 
  3. from tab_name 
  4. [where conditions];

其中：expressions中可以是多个字段。当distinct应用到多个字段的时候，其应用的范围是其后面的所有字段，而不只是紧挨着它的一个字段，而且distinct只能放到所有字段的前面，如下语句是错误的：

  1.  -- 该语句是错误的 
  2. SELECT country, distinct province
  3. FROM person;

分页查询

使用SELECT查询时如果结果集数据量很大，比如几万行数据，放在一个页面显示的话数据量太大，则需要分页显示。分页实际上就是从结果集中取出指定条数的记录。使用方式如下：

  1. -- 第一种形式: 
  2. SELECT col,[col2,...] 
  3. FROM tab_name 
  4. LIMIT M OFFSET N; -- N表示起始的行下标(从0开始),M表示要查询的行数。 
  6. -- 第二种形式： 
  7. SELECT col,[col2,...] 
  8. FROM tab_name 
  9. LIMIT [N,] M; -- N开始位置, M为要查询的行数。N如果省略,则返回最大的记录行数目(小于等于M行)

LIMIT中指定要检索的行数为检索的最大行数。如果没有足够的行(例如:给出LIMIT 10, 5，但只有13行)，MySQL将只返回它能返回的那么多行。注意：LIMIT 子句必须放在整个SELECT语句的最后！
使用LIMIT OFFSET 分页时，随着N越来越大，查询效率也会越来越低。

ORDER BY子句

SELECT查询结果默认按主键升序排序。如需要自定义排序规则，则使用ORDER BY子句。

  • 可以使用任何字段来作为排序的条件，从而返回排序后的查询结果。
  • 可以设定多个字段来排序。
  • 可以使用ASC或DESC关键字来设置查询结果是按升序或降序排列。 默认是ASC。
  • 可以添加 WHERE…LIKE 子句来设置条件。 ```sql — 单列排序: 默认按照hire_date从小到大的顺序排序 SELECT last_name, job_id, department_id, hire_date FROM employees ORDER BY hire_date ;

— 多列排序: 按department_id升序排列，如果有相同的department_id再按salary列降序排序 — DESC表示倒序，ASC表示升序(默认的排序规则) SELECT last_name, department_id, salary FROM employees ORDER BY department_id, salary DESC;

  1. 使用ORDER BY和LIMIT的组合，能够找出一个列中最高或最低的值。
  2. ```sql
  3. # 查找 表tab_name中col_name列中的最大值/最小值 
  4. SELECT col_name FROM tab_name; 
  5. ORDER BY col_name 
  6. [DESC] LIMIT 1;

投影查询：只返回某些列的数据，而不是所有列的数据，让结果集仅包含指定列。投影查询同样可以接WHERE条件，实现复杂的查询。

条件查询（WHERE子句）

在同时使用ORDER BY和WHERE子句时，应该让ORDER BY位于WHERE之后，否则将会产生错误。

  1. SELECT filed1,filed2,... 
  2. FROM tab1,tab2,... 
  3. WHERE condition1 [AND |OR ] condition2 ... ; -- 从tab1表中查询满足条件的所有数据
  • 查询语句中可以使用一个或者多个表，表之间使用逗号分割，并使用WHERE语句来设定查询条件。
  • 可以在 WHERE子句中指定任何条件。
  • 可以使用 AND 或者 OR 指定一个或多个条件。
  • WHERE子句也可以运用于 SQL 的 DELETE或者 UPDATE命令。
  • WHERE子句类似于 if 条件语句，根据MySQL表中的字段值来读取指定的数据。

常用条件表达式如下：

比较运算符
操作符 描述 实例
= 等号左右两侧的值相等返回1，不相等返回0。 select 2=3;
返回0。
<=> 严格比较两个NULL值是否相等 两个操作码均为NULL时，其所得值为1；而当一个操作码为NULL时，其所得值为0。
其他值按正常的=比较规则。
<>, != 不等号左右两侧的值不相等返回1，否则返回0。 select 2 <> 3;
返回1。
> 大于号左边的值大于右边的值返回1，否则返回0。 select 2 > 3;
返回0。
< 小于号左边的值小于右边的值返回1，否则返回0 select 2 < 3;
返回1。
>= 大于等于号左边的值大于或等于右边的值返回1，否则返回0 select 2 >= 3;
返回0。
<= 小于等于号左边的值小于或等于右边的值返回1，否则返回0 select 2 ,= 3;
返回1。
A BETWEEN B AND C A在B和C之间返回1，否则返回0 select 10 between 5 and 20;
返回1
A NOT BETWEEN B AND C A不在B和C之间返回1，否则返回0 select 1 not between 10 and 20;
返回1
A IN B A在B中返回1，否则返回0 select 3 in (1,2,3,4,5);
返回1
NOT IN A不在B中返回1，否则返回0 select 7 in (1,2,3,4,5);
返回1



IS NULL 关于NULL的条件比较运算是比较特殊的。不能使用 =NULL 或 !=NULL 在列中查找 NULL值。

NULL值处理只能使用IS NULL和IS NOT NULL。

NULL值与任何其它值的比较(即使是 NULL)永远返回NULL，即 NULL = NULL 返回 NULL 。 |
| | IS NOT NULL | | | | LIKE判断相似 |

模糊匹配 | select ‘12345’ like ‘12%’;
返回1 | | REGEXP 或 RLIKE | 正则式匹配 | select ‘beijing’ REGEXP ‘jing’;
返回1 |

AND、OR、NOT查询

  1. -- 从student表中查询所有分数大于80的男生 
  2. SELECT * FROM students 
  3. WHERE score >= 80 AND gender = 'M'; 
  4. -- 从student表中查询所有分数大于80的人或男生 
  5. SELECT * FROM students 
  6. WHERE score >= 80 OR gender = 'M';
  7. -- 从student表中查询所有class_id不为2的学生 
  8. SELECT * FROM students 
  9. WHERE NOT class_id = 2;

如果不加括号，条件运算按照NOT、AND、OR的优先级进行，即NOT优先级最高，其次是AND，最后是OR。加上括号可以改变优先级。要组合三个或者更多的条件，就需要用小括号()表示如何进行条件运算。

模糊匹配查询

IN查询：IN操作符完成与OR相同的功能。但IN操作符优点：

  • 在使用长的合法选项清单时，IN操作符的语法更清楚且更直观；
  • 在使用IN时，计算的次序更容易管理（因为使用的操作符更少）；
  • IN操作符一般比OR操作符清单执行更快；
  • IN的最大优点是可以包含其他SELECT语句，使得能够更动态地建立WHERE子句。 ```sql SELECT prod_name, prod_price FROM products WHERE vend_id IN (1002,1003) ORDER BY prod_name;

上述语句等价于：

SELECT prod_name, prod_price FROM products WHERE vend_id = 1002 OR vend_id = 1003 ORDER BY prod_name; 

  3. LIKE查询：LIKE子句中使用百分号 %字符来表示任意字符，类似于正则表达式中的*。如果没有使用百分号 %, LIKE 子句与 = 的效果是一样的。<br />搜索模式(search pattern)：由字符串、通配符或两者组合构成的搜索条件。
  4. ```sql
  5. SELECT field1, field2,...fieldN 
  6. FROM tab_name 
  7. WHERE field1 
  8. LIKE condition1 [AND | OR] filed2 = 'somevalue' 
  10. -- like、not like 匹配 
  11. -- 列名 [NOT ] LIKE：匹配串中可包含如下四种通配符： 
  12. -- _：匹配任意一个字符, 只匹配单个字符; 
  13. -- %：代表搜索模式中给定位置的0个、1个或多个字符,但%不能匹配NULL; 
  14. -- [ ]：匹配[ ]中的任意一个字符(若要比较的字符是连续的，则可以用连字符“-”表 达 )； 
  15. -- [^]：不匹配[ ]中的任意一个字符。 '%a' 
  16. -- 以a结尾的数据 'a%' 
  17. -- 以a开头的数据 '%a%' 
  18. -- 含有a的数据 '_a_' 
  19. -- 三位且中间字母是a的 '_a' 
  20. -- 两位且结尾字母是a的 'a_' 
  21. -- 两位且开头字母是a的

使用通配符要记住的技巧。

  • 不要过度使用通配符。如果其他操作符能达到相同的目的，应该 使用其他操作符；
  • 在需要使用通配符时，除非绝对有必要，否则不要把它们用在搜索模式的开始处。把通配符置于搜索模式的开始处，搜索起来是最慢的；
  • 仔细注意通配符的位置。如果放错地方，可能不会返回想要的数据。

正则表达式：MySQL用WHERE子句对正则表达式提供了初步的支持，允许指定正则表达式过滤SELECT检索出的数据。
LIKE和REGEXP的区别： LIKE匹配整个串而REGEXP匹配子串。如果被匹配的文本在列值中出现，LIKE将不会找到它，相应的行也不被返回（除非使用通配符）。而REGEXP在列值内进行匹配，如果被匹配的文本在列值中出现，REGEXP将会找到它，相应的行将被返回。利用定位符，通过用^开始每个表达式，用$结束每个表达式，可以使 REGEXP的作用与LIKE一样。

  1. select prod_name from products where prod_name like '1000' order by prod_name; 
  2. -- 结果: Empty set (0.00 sec) 
  3. select prod_name from products where prod_name regexp '1000' 
  4. order by prod_name; 
  5. -- 有结果
  6. +--------------+ | prod_name | +--------------+ | JetPack 1000 | +--------------+

MySQL中的正则表达式匹配(自版本3.23.4后)不区分大小。为区分大小写，可使用BINARY关键字，该关键字跟在REGEXP之后，模式之前。
OR匹配：为了匹配多个模式串中的某一个串可以在模式中使用|表示’或’。

  1. select prod_name 
  2. from products 
  3. where prod_name REGEXP '1000|2000' # 匹配 1000 或 2000 
  4. order by prod_name;

匹配几个字符之一： [123]：匹配1或2或3，[^123]:匹配除1，2，3之外的字符

  1. select prod_name 
  2. from products 
  3. # []是另一种形式的OR语句。[123]Ton 为[1|2|3]Ton的缩写，也可以使用后者 
  4. where prod_name regexp '[123]Ton'
  5. order by prod_name;

匹配范围：[0123456789]：匹配0到9之间的字符，等价于[0-9],其中-用来定义一个范围。[a-zA-Z]：匹配任意字母。

  1. select prod_name 
  2. from products 
  3. where prod_name regexp '[1-5] Ton' 
  4. order by prod_name;

匹配特殊字符：为了匹配特殊字符，必须用\为前导。\\-表示查找-，\\**.**表示查找**.**,为了匹配反斜杠（\）字符本身，需要使用\\\。多数正则表达式实现使用单个反斜杠转义特殊字符，以便能使用这些字符本身。但MySQL要求两个反斜杠（MySQL自己解释一个，正则表达式库解释另一个）。

  1. select vend_name 
  2. from vendors 
  3. where vend_name regexp '\\.' 
  4. order by vend_name;

匹配字符类：预定义的字符集，称为字符类，其含义如下表。

字符类 含义
[:alnum:] 任意字符和数字(等价于:[a-zA-Z9-9])
[:alpha:] 任意字符,等价于[a-zA-Z]
[:blank:] 空格和制表符，等价于[\\t]
[:cntrl:] ASCII控制字符(ASCII 0~31和127)
[:digit:] 任意数字,等价于[0-9]
[:xdigit:] 任意十六进制数字,等价于[a-fA-F0-9]
[:lower:] 任意小写字母
[:upper:] 任意大写字母
[:print:] 任意可打印字符
[:graph:] 与[:print:]相同，但不包含空格
[:punct:] 即不在[:alnum:]又不在[:cntrl:]中的任意字符
[:space:] 包括空格在内的任意空白字符，等价于[\\f\\n\\r\\t\\v]

元字符：通过重复元字符来匹配多个实例。

元字符 说明
* 0个或多个匹配
+ 1个或多个匹配。等价于{1,}
？ 陪陪?前面出现的字符0次或1次,等价于{0,1}
{n} 指定数目n的匹配
{n,} 不少于指定数目n的匹配
{n,m} 匹配数目的范围(m不超过255) 
  1. select prod_name 
  2. from products 
  3. where prod_name REGEXP '\\([0-9] sticks?\\)' 
  4. order by prod_name;

定位符：为了匹配特定位置的文本，需要使用下表列出的定位符

元字符 说明
**^** 文本的开始
$ 文本的结尾
[[**:**<**:**]] 词的开始
[[**:**>**:**]] 词的结尾 
  1. select prod_name 
  2. from products 
  3. where prod_name REGEXP '^[0-9\\.]' # 匹配0~9或.开始的串 
  4. order by prod_name;

注意：**^**有两种用法。在集合中(用[和]定义)，用它来否定该集合；否则用来指串的开始处。

计算字段

存储在表中的数据不一定是应用程序所需要的，需要从数据库中检索出转换、计算或格式化过的数据,而不是检索出数据，然后再在客户机应用程序或报告程序中重新格式化(一般来说，在数据库服务器上完成这些操作比在客户机中完成要快得多)。计算字段实际上就是在对表中的某些列执行select语句时使用MySQL函数或操作符对列做一些处理。计算字段并不实际存在于数据库表中，而是运行时在SELECT语句创建的。
MySQL函数参考:MySQL 函数 | 菜鸟教程 (runoob.com)
拼接字段：CONCAT(s1,s2…,sn)函数用来将字符串 s1,s2 ,…,sn等多个字符串合并为一个字符串。

  1. SELECT Concat(vend_name,'(',vend_country,')') FROM vendors ORDER BY vend_name;

删除空格：LTRIM(str)：删除字符串str左边的空格; RTRIM:删除字符串str右边的空格；TRIM:删除字符串str两边的空格。
别名(alias)是一个字段或值的替换名，也称为导出列，用AS关键字赋予。

  1. # AS 关键字也可以省略 FROM vendors ORDER BY vend_name; 
  2. SELECT CONCAT(RTRIM(vend_name),'(',RTRIM(vend_country),')') AS vend_title

聚合查询

对于统计总数、平均数这类计算，SQL提供了专门的聚合函数。使用聚合函数进行查询就是聚合查询，它可以快速获得结果。

函数 说明
COUNT(expr) 使用COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值（NULL）还是非空值。
使用COUNT(col)对特定列中具有值的行进行计数，忽略NULL值。
SUM(col) 计算某一列的总和，该列必须为数值类型。
SUM()函数忽略列值为NULL的行。
AVG(col) 计算某一列的平均值，该列必须为数值类型。
AVG()只能用来确定特定数值列的平均值，为了获得多个列的平均值，必须使用多个AVG()函数。
AVG()函数忽略列值为NULL的行。
MAX(col) 计算某一列的最大值。
MAX()函数忽略列值为NULL的行。
一般用来找出最大的数值或日期值；
用于文本数据时，如果数据按相应的列排序，则MAX()返回最后一行。
MIN(col) 计算某一列的最小值。
MIN()函数忽略列值为NULL的行。
一般用来找出最大的数值或日期值；
用于文本数据时，如果数据按相应的列排序，则MIN()返回最前面一行。

注意：

  • 如果聚合查询的WHERE条件没有匹配到任何行，COUNT()会返回0，而SUM()、AVG()、MAX()和MIN()会返回NULL。
  • 聚合函数不能嵌套调用。比如不能出现类似“AVG(SUM(字段名称))”形式的调用。

组合聚集函数

  1. -- 示例1
  2. SELECT AVG(salary), MAX(salary),MIN(salary), SUM(salary)
  3. FROM   employees
  4. WHERE  job_id LIKE '%REP%';

对于聚合查询，SQL通过ORDER BY子句还提供了“分组聚合”的功能。也可以使用多个列进行分组。

  1. -- 示例2
  2. SELECT MIN(hire_date), MAX(hire_date)
  3. FROM      employees;
  5. -- 示例3
  6. SELECT   department_id dept_id, job_id, SUM(salary)
  7. FROM     employees
  8. GROUP BY department_id, job_id ;

分组查询

分组允许把数据分为多个逻辑组，以便能对每个组进行聚集计算。

  1. SELECT column, group_function(column)
  2. FROM table
  3. [WHERE    condition]
  4. [GROUP BY    group_by_expression]
  5. [ORDER BY    column];

使用GROUP BY子句需要遵循的规定：

  • GROUP BY子句可以包含任意数目的列。这使得能对分组进行嵌套， 为数据分组提供更细致的控制。
  • 如果在GROUP BY子句中嵌套了分组，数据将在最后规定的分组上 进行汇总。换句话说，在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）。
  • GROUP BY子句中列出的每个列都必须是检索列或有效的表达式 （但不能是聚集函数）。如果在SELECT中使用表达式，则必须在 GROUP BY子句中指定相同的表达式。不能使用别名。
  • 除聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY子 句中给出。
  • 如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。
  • GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。

GROUP BY中使用WITH ROLLUP：使用WITH ROLLUP关键字之后，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所有记录的总和，即统计记录数量。

注：当使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序，即ROLLUP和ORDER BY是互相排斥的。

  1. SELECT department_id,AVG(salary)
  2. FROM employees
  3. WHERE department_id > 80
  4. GROUP BY department_id WITH ROLLUP;

分组过滤

MySQL使用HAVING过滤分组。所有适用于WHERE子句的语法都使用于HAVING，二者的区别在于：WHERE过滤行，而HAVING过滤分组。WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。

  1. SELECT   department_id, MAX(salary)
  2. FROM     employees
  3. GROUP BY department_id
  4. HAVING   MAX(salary)>10000 ;

一般在使用GROUP BY子句时，应该也给 出ORDER BY子句。这是保证数据正确排序的唯一方法。另外：聚合函数结果作为筛选条件时，不能用在where中，而是需要用having语法，配合重命名即可；

  1. SELECT order_num, SUM(quantity * item_price) AS order_total 
  2. FROM orderitems 
  3. GROUP BY order_num 
  4. HAVING order_total >= 50; # 这里必须用having不能用where ORDER BY order_total; 
  6. # 取出平均发贴数低于5的学校或平均回帖数小于20的学校。 
  7. select university, AVG(question_cnt) AS avg_question_cnt, 
  8.        AVG(answer_cnt) AS avg_answer_cnt 
  9. from user_profile 
  10. group by university 
  11. having avg_question_cnt < 5 or avg_answer_cnt < 20;

WHERE和HAVING的对比

区别1：WHERE 可以直接使用表中的字段作为筛选条件，但不能使用分组中的计算函数作为筛选条件；HAVING 必须要与 GROUP BY 配合使用，可以把分组计算的函数和分组字段作为筛选条件。
这决定了，在需要对数据进行分组统计的时候，HAVING 可以完成 WHERE 不能完成的任务。这是因为，在查询语法结构中，WHERE 在 GROUP BY 之前，所以无法对分组结果进行筛选。HAVING 在 GROUP BY 之后，可以使用分组字段和分组中的计算函数，对分组的结果集进行筛选，这个功能是 WHERE 无法完成的。另外，WHERE排除的记录不再包括在分组中。
区别2：如果需要通过连接从关联表中获取需要的数据，WHERE 是先筛选后连接，而 HAVING 是先连接后筛选。 这就决定了在关联查询中，WHERE 比 HAVING 更高效。因为 WHERE 可以先筛选，用一个筛选后的较小数据集和关联表进行连接，这样占用的资源比较少，执行效率也比较高。HAVING 则需要先把结果集准备好，也就是用未被筛选的数据集进行关联，然后对这个大的数据集进行筛选，这样占用的资源就比较多，执行效率也较低。

优点 缺点
WHERE 先筛选数据再关联，执行效率高 不能使用分组中的计算函数进行筛选
HAVING 可以使用分组中的计算函数 在最后的结果集中进行筛选，执行效率较低

SELECT的执行过程⭐️⭐️⭐️

查询的结构

  1. -- 方式1：
  2. SELECT ..., ...., ...
  3. FROM ..., ..., ....
  4. WHERE 多表的连接条件
  5. AND/OR 不包含组函数的过滤条件
  6. GROUP BY ...,...
  7. HAVING 包含组函数的过滤条件
  8. ORDER BY ... ASC/DESC
  9. LIMIT ..., ...
  11. -- 方式2：
  12. SELECT ..., ...., ...
  13. FROM ... 
  14. JOIN ... 
  15. ON 多表的连接条件
  16. JOIN ...
  17. ON ...
  18. WHERE 不包含组函数的过滤条件
  19. AND/OR 不包含组函数的过滤条件
  20. GROUP BY ..., ...
  21. HAVING 包含组函数的过滤条件
  22. ORDER BY ... ASC/DESC
  23. LIMIT ..., ...
  25. --其中：
  26. --（1）from：从哪些表中筛选
  27. --（2）on：关联多表查询时，去除笛卡尔积
  28. --（3）where：从表中筛选的条件
  29. --（4）group by：分组依据
  30. --（5）having：在统计结果中再次筛选
  31. --（6）order by：排序
  32. --（7）limit：分页

SELECT执行顺序

关键字顺序如上一小节所示，SQL执行顺序如下所示：
image.png

  1. -- 顺序一
  2. FROM
  3. WHERE  
  4. GROUP BY  
  5. HAVING 
  6. SELECT 的字段 
  7. DISTINCT  
  8. ORDER BY 
  9. LIMIT
  11. -- 顺序二
  12. FROM <left_table>
  13. ON <join_condition>
  14. <join_type> JOIN <right_table>
  15. WHERE <where_condition>
  16. GROUP BY <group_by_list>
  17. HAVING <having_condition>
  18. SELECT 
  19. DISTINCT <select_list>
  20. ORDER BY <order_by_condition>
  21. LIMIT <limit_number>

在 SELECT 语句执行这些步骤的时候，每个步骤都会产生一个虚拟表，然后将这个虚拟表传入下一个步骤中作为输入。需要注意的是，这些步骤隐含在 SQL 的执行过程中，对于我们来说是不可见的。

SQL 的执行原理

一、SELECT 是先执行 FROM 这一步的。在这个阶段，如果是多张表联查，还会经历下面的几个步骤：

  1. 首先先通过 CROSS JOIN 求笛卡尔积，相当于得到虚拟表（virtual table）vt1-1；
  2. 通过 ON 进行筛选，在虚拟表 vt1-1 的基础上进行筛选，得到虚拟表 vt1-2；
  3. 添加外部行。如果使用的是左连接、右链接或者全连接，就会涉及到外部行，也就是在虚拟表 vt1-2 的基础上增加外部行，得到虚拟表 vt1-3。

当然如果操作的是两张以上的表，还会重复上面的步骤，直到所有表都被处理完为止。这个过程得到是原始数据。
二、当拿到了查询数据表的原始数据，也就是最终的虚拟表 vt1，就可以在此基础上再进行 WHERE 阶段。在这个阶段中，会根据 vt1 表的结果进行筛选过滤，得到虚拟表 vt2。
三、然后进入第三步和第四步，也就是 GROUP 和 HAVING 阶段。在这个阶段中，实际上是在虚拟表 vt2 的基础上进行分组和分组过滤，得到中间的虚拟表 vt3 和 vt4。
四、当完成了条件筛选部分之后，就可以筛选表中提取的字段，也就是进入到 SELECT 和 DISTINCT 阶段。首先在 SELECT 阶段会提取想要的字段，然后在 DISTINCT 阶段过滤掉重复的行，分别得到中间的虚拟表 vt5-1 和 vt5-2。
五、当提取了想要的字段数据之后，就可以按照指定的字段进行排序，也就是 ORDER BY 阶段，得到虚拟表 vt6。
六、最后在 vt6 的基础上，取出指定行的记录，也就是 LIMIT 阶段，得到最终的结果，对应的是虚拟表 vt7。
当然在写 SELECT 语句的时候，不一定存在所有的关键字，相应的阶段就会省略。
同时因为 SQL 是一门类似英语的结构化查询语言，所以在写 SELECT 语句的时候，还要注意相应的关键字顺序，所谓底层运行的原理，就是刚才讲到的执行顺序。

子查询⭐️⭐️⭐️

SELECT语句是SQL的查询。子查询（subquery）是嵌套在其他查询中的查询。子查询总是从内向外处理。在WHERE子句中使用子查询能够编写出功能很强并且很灵活的 SQL语句。对于能嵌套的子查询的数目没有限制，不过在实际使用时由于性能的限制，不能嵌套太多的子查询。子查询结构如下：
image.png
使用子查询有如下注意事项：

  • 子查询要包含在括号内；
  • 将子查询放在比较条件的右侧；
  • 单行操作符对应单行子查询，多行操作符对应多行子查询。

    子查询分类

    按内查询的结果返回一条还是多条记录，将子查询分为单行子查询、多行子查询。
    按内查询是否被执行多次，将子查询划分为相关子查询和不相关子查询。子查询从数据表中查询了数据结果，如果这个数据结果只执行一次，然后这个数据结果作为主查询的条件进行执行，那么这样的子查询叫做不相关子查询。如果子查询需要执行多次，即采用循环的方式，先从外部查询开始，每次都传入子查询进行查询，然后再将结果反馈给外部，这种嵌套的执行方式就称为相关子查询。

    单行子查询

    常用单行比较操作符
操作符 含义
= 等于
<> 不等于
> 大于
>= 大于等于
< 小于
<= 小于等于 
  1. -- 返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资
  2. SELECT last_name, job_id, salary
  3. FROM   employees
  4. WHERE  job_id =  
  5.                 (SELECT job_id
  6.                  FROM   employees
  7.                  WHERE  employee_id = 141)
  8. AND    salary >
  9.                 (SELECT salary
  10.                  FROM   employees
  11.                  WHERE  employee_id = 143);
  12. -- 返回公司工资最少的员工的last_name,job_id和salary
  13. SELECT last_name, job_id, salary
  14. FROM   employees
  15. WHERE  salary = 
  16.                 (SELECT MIN(salary)
  17.                  FROM   employees);
  19. -- 查询与141号或174号员工的manager_id和department_id相同的其他员工的
  20. -- employee_id，manager_id，department_id.
  21. -- 实现方式1: 不成对比较
  22. SELECT  employee_id, manager_id, department_id
  23. FROM    employees
  24. WHERE   manager_id IN
  25.           (SELECT  manager_id
  26.                    FROM    employees
  27.                    WHERE   employee_id IN (174,141))
  28. AND     department_id IN 
  29.           (SELECT  department_id
  30.                    FROM    employees
  31.                    WHERE   employee_id IN (174,141))
  32. AND    employee_id NOT IN(174,141);
  34. -- 实现方式2: 成对比较
  35. SELECT    employee_id, manager_id, department_id
  36. FROM    employees
  37. WHERE  (manager_id, department_id) IN
  38.                       (SELECT manager_id, department_id
  39.                        FROM   employees
  40.                        WHERE  employee_id IN (141,174))
  41. AND    employee_id NOT IN (141,174);

HAVING中的子查询

HAVING子查询的执行步骤：首先执行子查询，然后向主查询中的HAVING子句返回结果。

  1. -- 查询最低工资大于50号部门最低工资的部门id和其最低工资
  2. SELECT   department_id, MIN(salary)
  3. FROM     employees
  4. GROUP BY department_id
  5. HAVING   MIN(salary) >
  6.                        (SELECT MIN(salary)
  7.                         FROM   employees
  8.                         WHERE  department_id = 50);

CASE中的子查询

在CASE表达式中可以使用单列子查询。

  1. -- 显式员工的employee_id,last_name和location。其中，若员工department_id与
  2. -- location_id为1800的department_id相同，则location为’Canada’，其余则为’USA’。
  3. SELECT employee_id, last_name,
  4.        (CASE department_id
  5.         WHEN
  6.              (SELECT department_id FROM departments
  7.                             WHERE location_id = 1800)
  8.         THEN 'Canada' ELSE 'USA' END) location
  9. FROM   employees;

子查询中空值问题

  1. SELECT last_name, job_id
  2. FROM   employees
  3. WHERE  job_id =
  4.                 (SELECT job_id
  5.                  FROM   employees
  6.                  WHERE  last_name = 'Haas');    -- 子查询不返回任何行

非法使用子查询

  1. SELECT employee_id, last_name
  2. FROM   employees
  3. WHERE  salary =
  4.                 (SELECT   MIN(salary)
  5.                  FROM     employees
  6.                  GROUP BY department_id); -- 多行子查询使用单行比较符
  7. > Subquery returns more than 1 row

多行子查询

多行子查询也称集合比较子查询，内查询返回多行数据，外查询的where子句使用多行比较符。常用多行比较符如下表：

操作符 含义
IN 等于列表中的任意一个
ANY 需要和单行比较操作符一起使用，和子查询返回的某一个值比较
ALL 需要和单行比较操作符一起使用，和子查询返回的所有值比较
SOME 实际上是ANY的别名，作用相同，一般常使用ANY 
  1. -- 返回其它job_id中比‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id 以及salary
  2. SELECT employee_id, last_name, job_id, salary
  3. FROM employees
  4. WHERE salary < ANY (
  5.                                          SELECT salary
  6.                                          FROM employees
  7.                                          WHERE job_id = 'IT_PROG')
  8. AND     job_id <> 'IT_PROG';
  10. -- 返回其它job_id中比‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id以及salary
  11. SELECT employee_id, last_name, job_id, salary
  12. FROM employees
  13. WHERE salary < ALL (
  14.                                          SELECT salary
  15.                                          FROM employees
  16.                                          WHERE job_id = 'IT_PROG')
  17. AND     job_id <> 'IT_PROG';
  19. -- 查询平均工资最低的部门id
  20. SELECT department_id
  21. FROM employees
  22. GROUP BY department_id
  23. HAVING AVG(salary) <= ALL (
  24.                 SELECT AVG(salary) avg_sal
  25.                 FROM employees
  26.                 GROUP BY department_id);

空值问题

  1. SELECT last_name
  2. FROM employees
  3. WHERE employee_id NOT IN (
  4.             SELECT manager_id
  5.             FROM employees);

相关子查询

如果子查询的执行依赖于外部查询，通常是因为子查询中的表用到了外部的表，并进行了条件关联，因此每执行一次外部查询，子查询都要重新计算一次，这样的子查询就称为相关子查询。相关子查询按照一行接一行的顺序执行，主查询的每一行都执行一次子查询。
1554992898234.png
在使用相关子查询(涉及外部查询的子查询)时通常必须使用完全限定列名(表名和列名由一个句点分隔)，从而避免多个表之间列名可能重复的问题。

  1. SELECT cust_name, cust_state, 
  2.       ( SELECT COUNT(*) -- 使用子查询的另一方法是创建计算字段。
  3.         FROM orders 
  4.         WHERE orders.cust_id = customers.cust_id) AS orders 
  5. FROM customers 
  6. ORDER BY cust_name;

相关子查询

  1. -- 查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id
  2. -- 方式一:相关子查询
  3. SELECT last_name, salary, department_id
  4. FROM employees outer
  5. WHERE salary > ( SELECT AVG(salary)
  6.                                  FROM employees
  7.                                  WHERE department_id = outer.department_id);

在FROM中使用子查询

FROM型的子查询：子查询是作为FROM的一部分，子查询要用()引起来，并且要给这个子查询取别名，把它当成一张“临时的虚拟的表”来使用。

  1. -- 查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id
  2. -- 方式二: 在FROM中使用子查询
  3. SELECT last_name,salary,e1.department_id
  4. FROM employees e1,(
  5.                                         SELECT department_id,AVG(salary) dept_avg_sal 
  6.                                         FROM employees GROUP BY department_id) e2
  7. WHERE e1.`department_id` = e2.department_id
  8. AND e2.dept_avg_sal < e1.`salary`;

在ORDER BY中使用子查询

  1. --查询员工的id,salary,按照department_name 排序
  2. SELECT employee_id,salary
  3. FROM employees e
  4. ORDER BY (
  5.                         SELECT department_name
  6.                         FROM departments d
  7.                         WHERE e.`department_id` = d.`department_id`);

EXISTS与NOT EXISTS关键字

  • 关联子查询通常也会和 EXISTS操作符一起来使用，用来检查在子查询中是否存在满足条件的行。
  • 如果在子查询中不存在满足条件的行：
    • 条件返回 FALSE
    • 继续在子查询中查找
  • 如果在子查询中存在满足条件的行：
    • 不在子查询中继续查找
    • 条件返回 TRUE
  • NOT EXISTS关键字表示如果不存在某种条件，则返回TRUE，否则返回FALSE。

例1：查询公司管理者的employee_id，last_name，job_id，department_id信息。

  1. -- 方式1
  2. SELECT employee_id, last_name, job_id, department_id
  3. FROM   employees e1
  4. WHERE  EXISTS (  SELECT *
  5.                  FROM   employees e2
  6.                  WHERE  e2.manager_id =  e1.employee_id);
  8. -- 方式2 自连接
  9. SELECT DISTINCT e1.employee_id, e1.last_name, e1.job_id, e1.department_id
  10. FROM  employees e1 JOIN employees e2
  11. WHERE e1.employee_id = e2.manager_id;
  13. -- 方式3
  14. SELECT employee_id,last_name,job_id,department_id
  15. FROM employees
  16. WHERE employee_id IN (  SELECT DISTINCT manager_id
  17.                                                 FROM employees);

例2：查询departments中，不在employees中的department_id和department_name。

  1. SELECT department_id, department_name
  2. FROM departments d
  3. WHERE NOT EXISTS (    SELECT 'X'
  4.                                         FROM   employees
  5.                                         WHERE  department_id = d.department_id);

相关更新

使用相关子查询依据一个表中的数据更新另一个表的数据。

  1. UPDATE table1 alias1
  2. SET    column = (SELECT expression
  3.                  FROM   table2 alias2
  4.                  WHERE  alias1.column = alias2.column);

题目：在employees中增加一个department_name字段，数据为员工对应的部门名称。

  1. -- 增加字段
  2. ALTER TABLE employees
  3. ADD(department_name VARCHAR2(14));
  5. -- 更新新增字段
  6. UPDATE employees e
  7. SET department_name =  (SELECT department_name 
  8.                            FROM   departments d
  9.                            WHERE  e.department_id = d.department_id);

相关删除

使用相关子查询依据一个表中的数据删除另一个表的数据。

  1.  DELETE FROM table1 alias1
  2.  WHERE column operator (SELECT expression
  3.                         FROM   table2 alias2
  4.                         WHERE  alias1.column = alias2.column);

题目：删除表employees中，其与emp_history表皆有的数据。

  1. DELETE FROM employees e
  2. WHERE employee_id in  
  3.            (SELECT employee_id
  4.             FROM   emp_history 
  5.             WHERE  employee_id = e.employee_id);

连接查询⭐️⭐️⭐️

连接是一种机制，用来在一条SELECT语句中关联表。连接不是物理实体，它在实际的数据库表中不存在。连接由MySQL根据需要建立，它存在于查询的执行当中。连接查询对多个表进行JOIN运算，简单地说就是先确定一个主表作为结果集，然后把其他表的行有选择性地“连接”在主表结果集上。
image.png

自连接

进行连接的两个或多个表是同一张表，只是用取别名的方式虚拟成两张表以代表不同的意义，然后再对两个或多个表进行内连接、外连接等查询。自连接通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句。虽然最终的结果是相同的，但有时候处理联结远比处理子查询快得多。

  1. -- 示例1
  2. SELECT p1.prod_id, p1.prod_name 
  3. FROM products AS p1,products AS p2 
  4. WHERE p1.vend_id = p2.vend_id AND p2.prod_id = 'DTNTR'; 
  5. -- 等价于下面的SQL 
  6. SELECT prod_id, prod_name 
  7. FROM products 
  8. WHERE vend_id = ( SELECT vend_id FROM products WHERE prod_id = 'DTNTR'); 
  10. -- 示例2
  11. SELECT CONCAT(worker.last_name ,' works for ', manager.last_name)
  12. FROM   employees worker, employees manager
  13. WHERE  worker.manager_id = manager.employee_id ;

内连接(inner join)⭐️⭐️⭐️

从左表中取出每一条记录,去右表中与所有的记录进行匹配：匹配必须是某个条件在左表中与右表中相同最终才会保留结果,否则不保留。SQL对一条SELECT语句中可以连接的表的数目没有限制，但不要连接不必要的表。联结的表越多，性能下降越厉害。
多表查询时，可以通过使用**表名.列名**这样的方式来引用列和设置别名来避免结果集的列名重复问题。SQL也允许给表设置一个别名，这么做主要有两个理由:①缩短SQL语句；②允许在单条SELECT语句中多次使用相同的表。
在连接查询中条件部分通常不用WHERE而是用ON，因为WHERE没有ON效率高。ON指匹配到第一条成功的就结束，其他不匹配；若没有，不进行匹配。而WHERE会一直匹配，进行判断。
image.png

  1. -- 内连接(A∩B)语法如下 
  2. SELECT col1,col2,....         -- col1,col2,...是A和B中出现的字段名 
  3. FROM A [INNER] JOIN B         -- inner关键字可以加也可以不加 
  4. ON | WHERE conditions;         -- conditions表示连接条件, 根据连接使用的操作符分为: 
  5.                           -- 相等连接(使用等号操作符), 不等连接(不使用等号操作符) 
  7. -- 示例
  8. SELECT employee_id,last_name,department_name
  9. FROM employees e JOIN departments d
  10. ON e.`department_id` = d.`department_id`;

交叉连接(cross join，又称笛卡尔积)

内连接可以没有连接条件: 即没有on之后的内容,这时系统会保留所有结果(笛卡尔积)。
从一张表中循环取出每一条记录, 每条记录都去另外一张表进行匹配： 匹配一定保留(没有条件匹配), 而连接本身字段就会增加(保留),最终形成的结果叫做笛卡尔积。
一次查询两个或多个表的数据，查询的结果也是一个二维表，结果集的列数是tab1表和tab2表的列数之和，行数是tab1表和tab2表的行数之积。
使用笛卡尔查询时要非常小心，由于结果集是目标表的行数乘积,对两个各自有1万行记录的表进行笛卡尔查询将返回1亿条记录。

  1. -- 交叉连接 四种写法
  2. SELECT employee_id, department_name
  3. FROM employees, departments;
  5. SELECT employee_id, department_name
  6. FROM employees INNER JOIN departments;
  8. SELECT employee_id, department_name
  9. FROM employees JOIN departments;
  11. SELECT employee_id, department_name
  12. FROM employees  CROSS JOIN departments;

等值连接

在连接条件中使用等于号(=)运算符比较被连接列的列值，其查询结果中列出被连接表中的所有列，包括其中的重复列。

  1. -- 等值连接标准SQL语法
  2. SELECT col1, col2, ...
  3. from Table_A  A INNER JOIN Table_B  B 
  4. ON A.id = B.id;
  6. -- 示例: 
  7. SELECT employees.employee_id, employees.last_name, 
  8.        employees.department_id, departments.department_id,
  9.        departments.location_id
  10. FROM   employees, departments  -- 逗号连接多个表, 连接条件只能用where
  11. WHERE  employees.department_id = departments.department_id;
  14. SELECT employees.employee_id, employees.last_name, 
  15.        employees.department_id, departments.department_id,
  16.        departments.location_id
  17. FROM   employees INNER JOIN departments  -- JOIN连接多个表, 连接条件能用ON或WHERE
  18. WHERE  employees.department_id = departments.department_id;
  20. -- 连接n张表, 至少需要n-1个连接条件
  21. SELECT last_name,job_title,department_name 
  22. FROM employees INNER JOIN departments INNER JOIN jobs 
  23. ON employees.department_id = departments.department_id 
  24. AND employees.job_id = jobs.job_id;
  26. SELECT last_name,job_title,department_name 
  27. FROM employees INNER JOIN departments 
  28. ON employees.department_id = departments.department_id 
  29. INNER JOIN jobs 
  30. ON employees.job_id = jobs.job_id;

SQL99还支持使用 USING 指定多个数据表里的同名字段进行等值连接，但是只能配合JOIN一起使用，且要求两个关联字段在关联表中名称一致，且只能表示关联字段值相等。

  1. SELECT employee_id,last_name,department_name
  2. FROM employees e JOIN departments d
  3. USING (department_id);
  5. SELECT employee_id,last_name,department_name
  6. FROM employees e ,departments d
  7. WHERE e.department_id = d.department_id;

非等值连接

不等连接跟等值连接仅仅是连接条件中使用的运算符不一样，其余一致。非等值连接使用的是除等于号运算符以外的其它比较运算符，如>、>=、<=、<、!>、!<和<> , between…and，not between…and等。

  1. -- 示例
  2. SELECT e.last_name, e.salary, j.grade_level
  3. FROM   employees e, job_grades j
  4. WHERE  e.salary BETWEEN j.lowest_sal AND j.highest_sal;

自然连接(natural join)：

自然连接是一种特殊的等值连接，它要求两个关系中进行比较的分量必须是相同的属性组，并且在结果中把重复的属性列去掉。而等值连接并不去掉重复的属性列。
SQL99 在 SQL92 的基础上提供了NATURAL JOIN 语法用来表示自然连接。可以把自然连接理解为 SQL92 中的等值连接。它会自动查询两张连接表中所有相同的字段，然后进行等值连接。

  1. -- 等值连接不去除重复列, 所以manger_id前要加表名,否则报错
  2. SELECT employee_id,last_name,department_name, e.manger_id
  3. FROM employees e JOIN departments d
  4. ON e.`department_id` = d.`department_id`
  5. AND e.`manager_id` = d.`manager_id`;
  7. -- 自然连接去除重复列，所以manger_id列前不用加表名
  8. SELECT employee_id,last_name,department_name, manger_id
  9. FROM employees e NATURAL JOIN departments d;

自然连接与等值连接的区别：

  • 等值连接中不要求属性值完全相同，而自然连接要求两个关系中进行比较的列必须有同的值域（属性名可以不同）。
  • 等值连接不去掉重复列，而自然连接去掉重复列，也可以说自然连接是去掉重复列的等值连接。
    image.png

外连接⭐️⭐️⭐️

以某张表为主,取出里面的所有记录, 然后每条与另外一张表进行连接: 不管能不能匹配上条件,最终都会保留：能匹配,正确保留；不能匹配,其他表的字段都置NULL。
左外联结与右外联结唯一差别是所关联的表的顺序不同。

左外连接(LEFT OUTER JOIN)

左外连接其实就是两个表的交集+左表剩下的数据 ，当然这是在没其他过滤条件的情况下。显示左表student所有记录，如右表中没有与之匹配的项则以NULL值代替。
image.png

  1. -- 左外连接语法
  2. SELECT col1, col2, ...
  3. FROM Table_A A LEFT [OUTER] JOIN Table_B B 
  4. ON A.Key = B.Key 
  6. -- 示例 
  7. SELECT employee_id,last_name,department_name
  8. FROM employees e LEFT JOIN departments d
  9. ON e.`department_id` = d.`department_id`;

左连接排除内连接结果(LEFT JOIN EXCLUDING INNER JOIN )

将右表B以及两张表交集的部分过滤掉，得到的记录是左表中唯一存在的。
image.png

  1. -- 左连接排除内连接(A - A∩B)语法
  2. SELECT col1, col2, ...
  3. FROM Table_A A LEFT [OUTER] JOIN Table_B B 
  4. ON A.Key = B.Key 
  5. WHERE B.Key IS NULL;
  7. -- 示例 
  8. SELECT employee_id,last_name,department_name
  9. FROM employees e LEFT JOIN departments d
  10. ON e.`department_id` = d.`department_id`
  11. WHERE d.`department_id` IS NULL

右外连接(RIGHT OUTER JOIN)

显示右表所有记录，如左表中没有与之匹配的项则以NULL值代替。
image.png

  1. -- 右外连接语法
  2. SELECT col1, col2, ...
  3. FROM Table_A A RIGHT [OUTER] JOIN Table_B B 
  4. ON A.Key = B.Key;
  6. -- 示例 
  7. SELECT employee_id,last_name,department_name
  8. FROM employees e RIGHT JOIN departments d
  9. ON e.`department_id` = d.`department_id`;

右连接排除内连接结果（RIGHT JOIN EXCLUDING INNER JOIN）

将左表A以及两张表交集的部分过滤掉，得到的记录是右表中唯一存在的。即:右表唯一。
image.png

  1. -- 右连接排除内连接(B-A∩B)语法
  2. SELECT <select_list> 
  3. FROM Table_A A RIGHT [OUTER] JOIN Table_B B 
  4. ON A.Key = B.Key 
  5. WHERE A.Key IS NULL; 
  7. -- 示例
  8. SELECT employee_id,last_name,department_name
  9. FROM employees e RIGHT JOIN departments d
  10. ON e.`department_id` = d.`department_id`
  11. WHERE e.`department_id` IS NULL

全外联结(FULL OUTER JOIN)

显示两张表的并集，如果其中一张表的记录在另一张表中没有匹配的行，则对应的数据项填充NULL，图示如下：
image.png

  1. -- 全外连接标准SQL语法: MySQL不支持
  2. SELECT <select_list> 
  3. FROM Table_A A FULL OUTER JOIN Table_B B 
  4. ON A.Key = B.Key; 
  6. -- MySQL实现全外连接: 
  7. -- 1: 左外连接排除内连接+右外连接
  8. SELECT employee_id,last_name,department_name
  9. FROM employees e LEFT JOIN departments d
  10. ON e.`department_id` = d.`department_id`
  11. WHERE d.`department_id` IS NULL
  12. UNION ALL  #没有去重操作，效率高
  13. SELECT employee_id,last_name,department_name
  14. FROM employees e RIGHT JOIN departments d
  15. ON e.`department_id` = d.`department_id`;
  17. -- 2:左外连接+右外连接排除内连接
  18. SELECT employee_id,last_name,department_name
  19. FROM employees e LEFT JOIN departments d
  20. ON e.`department_id` = d.`department_id`
  21. UNION ALL  #没有去重操作，效率高
  22. SELECT employee_id,last_name,department_name
  23. FROM employees e RIGHT JOIN departments d
  24. ON e.`department_id` = d.`department_id`
  25. WHERE e.`department_id` IS NULL;

全外连接排除内连接结果（FULL OUTER JOIN EXCLUDING INNER JOIN）

查找两张表中没有关联的记录项。即非交集连接。
image.png

  1. -- 全外连接排除内连接标准SQL语法: MySQL不支持
  2. SELECT <select_list> 
  3. FROM Table_A A FULL OUTER JOIN Table_B B ON A.Key = B.Key 
  4. WHERE A.Key IS NULL OR B.Key IS NULL; 
  6. -- MySQL实现全外连接排除内连接: 左外连接排除内连接+右外连接排除内连接
  7. SELECT employee_id,last_name,department_name
  8. FROM employees e LEFT JOIN departments d
  9. ON e.`department_id` = d.`department_id`
  10. WHERE d.`department_id` IS NULL
  11. UNION ALL
  12. SELECT employee_id,last_name,department_name
  13. FROM employees e RIGHT JOIN departments d
  14. ON e.`department_id` = d.`department_id`
  15. WHERE e.`department_id` IS NULL

连接查询总结：

  • 内连接(inner join)：取出连接表中匹配到的数据，匹配不到的不保留；
  • 外连接(outer join)：取出连接表中匹配到的数据，匹配不到的值为NULL

image.png

组合查询 ⭐️⭐️

MySQL允许执行多个查询（多条SELECT语句），并将结果作为单个查询结果集返回。利用UNION关键字，可以将多条SELECT语句的查询结果组合成单个结果集。合并时，两个表对应的列数和数据类型必须相同，并且相互对应。各个SELECT语句之间使用UNION或UNION ALL关键字分隔。
有两种基本情况需要使用组合查询：

  • 在单个查询中从不同的表返回类似结构的数据；
  • 对单个表执行多个查询，按单个查询返回数据。

组合查询与多个WHERE条件：任何具有多个WHERE子句的SELECT语句都可以作为一个组合查询给出。两种方法在不同的查询中性能不同。如果需要每个条件的匹配行全部出现（包括重复行），则必须使用UNION ALL而不是WHERE。

  1. SELECT col1, col2, ..., coln     -- col1,col2,...是要检索的列 
  2. FROM tab                         -- tab是要检索的数据表 
  3. [WHERE conditions]               -- 检索条件 
  4. -- DISTINCT: 删除结果集中重复的数据(默认操作), ALL: 结果集包含重复数据。
  5. UNION [ALL | DISTINCT] 
  6. SELECT col1, col2, ..., coln     
  7. FROM tab 
  8. [WHERE conditions] 
  9. [ORDER BY col];                  -- 对组合结果集进行排序

UNION规则：

  • UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔；
  • UNION中的每个查询必须包含相同个数的的列、表达式或聚集函数，各个列不需要以相同的次序列出；
  • 列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型

UNION 操作符返回两个查询的结果集的并集，去除重复记录。UNION ALL操作符返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重。

注意：执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效率。

全文本搜索

MySQL两个最常使用的引擎：MyISAM、InnoDB， 前者支持全文本搜索，后者不支持。
在使用全文本搜索时，MySQL不需要分别查看每个行，不需要分别分析和处理每个词。MySQL创建指定列中各词的一个索引，搜索可以针对这些词进行。一般在创建表时启用全文本搜索，CREATE TABLE语句接受FULLTEXT子句，它给出被索引列的一个逗号分隔的列表。在定义之后，MySQL自动维护该索引。在增加、更新或删除行时， 索引随之自动更新。
不应该在导入数据时启用FULLTEXT索引，更新索引要花时间。应该首先导入所有数据，然后再修改表，定义FULLTEXT。这样有助于更快地导入数据。

  1. CREATE TABLE productnotes
  2. (
  3.   note_id    int           NOT NULL AUTO_INCREMENT,
  4.   prod_id    char(10)      NOT NULL,
  5.   note_date datetime       NOT NULL,
  6.   note_text  text          NULL ,
  7.   PRIMARY KEY(note_id),
  8.   FULLTEXT(note_text)      # 启动全文本搜索, FULLTEXT可以索引单个列，也可以指定多个列 
  9. ) ENGINE=MyISAM;

在索引之后，使用两个函数Match()和Against()执行全文本搜索， 其中Match()指定被搜索的列，Against()指定要使用的搜索表达式。传递给Match()的值必须与FULLTEXT()定义中的相同。如果指定多个列，则必须列出它们（而且次序正确）。除非使用BINARY方式， 否则全文本搜索不区分大小写。

  1. SELECT note_text 
  2. FROM productnotes 
  3. WHERE Match(note_text) Against('rabbit');

全文本搜索的一个重要部分就是对结果排序。具有较高等级的行先返回。下面的SQL在SELECT而不是WHERE子句中使用Match()和Against()。这使所有行都被返回（因为没有WHERE子句）。Match()和Against() 用来建立一个计算列（别名为rank），此列包含全文本搜索计算出的等级值。等级由MySQL根据行中词的数目、唯一词的数目、整个索引中词的 总数以及包含该词的行的数目计算出来。如果指定多个搜索项，则包含多数匹配词的 那些行将具有比包含较少词（或仅有一个匹配）的那些行高的等级值。

  1. SELECT note_text,Match(note_text) Against('rabbit') As rank 
  2. FROM productnotes;

查询扩展用来设法放宽所返回的全文本搜索结果的范围。在使用查询扩展时，MySQL对数据和 索引进行两遍扫描来完成搜索：

  • 首先，进行一个基本的全文本搜索，找出与搜索条件匹配的所有行；
  • 其次，MySQL检查这些匹配行并选择所有有用的词；
  • 最后，MySQL再次进行全文本搜索，这次不仅使用原来的条件，而且还使用所有有用的词。
    1. SELECT note_text 
    2. FROM productnotes 
    3. WHERE Match(note_text) Against('anvils' WITH QUERY EXPANSION);

MySQL支持全文本搜索的另外一种形式是布尔文本搜索。以布尔方式，可以提供关于如下内容的细节：①要匹配的词； ②要排斥的词（如果某行包含这个词，则不返回该行，即使它包含 其他指定的词也是如此）； ③ 排列提示（指定某些词比其他词更重要，更重要的词等级更高）； ④表达式分组；⑤另外一些内容。
布尔文本搜索方式即使没有定义 FULLTEXT索引，也可以使用它。但这是一种非常缓慢的操作，其性能将随着数据量的增加而降低。

全文本布尔操作符
布尔操作符 说明
+ 包含，此必须存在
- 排除，词必须不出现
> 包含,而且增加等级值
< 减少，而且减少等级值
( ) 把词组成子表达式(允许这些子表达式作为一个组被包含、排除、排列等)
~ 取消一个词的排序值
* 词尾的通配符
“” 定义一个短语(与单个词的列表不一样，它匹配整个短语以便包含或排除这个词语) 
  1. SELECT note_text
  2. FROM productnotes
  3. -- 搜索匹配包含词rabbit和bait的行
  4. WHERE Match(note_text) Against('+rabbit +bait' IN BOOLEAN MODE);
  6. SELECT note_text
  7. FROM productnotes
  8. --  匹配包含rabbit和bait中的至少一 个词的行
  9. WHERE Match(note_text) Against('rabbit bait' IN BOOLEAN MODE); 
  11. SELECT note_text
  12. FROM productnotes
  13. --  匹配短语rabbit bait而不是匹配两个词rabbit和 bait
  14. WHERE Match(note_text) Against('"rabbit bait"' IN BOOLEAN MODE);
  16. SELECT note_text
  17. FROM productnotes
  18. --  匹配rabbit和carrot，增加前者的等级，降低后者的等级
  19. WHERE Match(note_text) Against('>rabbit <carrot' IN BOOLEAN MODE);
  21. SELECT note_text
  22. FROM productnotes
  23. --  匹配短语rabbit bait而不是匹配两个词rabbit和 bait
  24. WHERE Match(note_text) Against('"rabbit bait"' IN BOOLEAN MODE);