SQL 教程：执行顺序、经典SQL语句、高频 SQL

擒贼先擒王

已于 2024-09-20 15:16:14 修改

阅读量2.1k

点赞数

分类专栏： SQL 文章标签： mysql 数据库 php

于 2017-07-21 14:37:08 首次发布

原文链接：http://www.runoob.com/sql/sql-tutorial.html

版权

SQL 专栏收录该内容

6 篇文章 2 订阅

订阅专栏

W3School SQL 教程：http://www.w3school.com.cn/sql/index.asp
SQL 教程：http://www.runoob.com/sql/sql-tutorial.html
SQL 教程：https://www.yiibai.com/sql

MySQL 必备的几个示例数据库：https://blog.csdn.net/horses/article/details/106795844
官网样例数据：https://dev.mysql.com/doc/index-other.html

1、SQL 语法

所有 SQL 语句都以：select，insert，update，delete，alter，drop，create，use，show 等关键字开头，所有语句都以分号(;)结尾。

SQL 不区分大小写。通常SQL关键字以大写形式编写。( MySQL 表名区分大小写 )
SQL语句依赖于文本行，可以在一个或多个文本行上放置一个SQL语句。
使用 SQL 语句可以在数据库中执行大多数操作。
SQL依赖于关系代数和元组关系演算。

SQL 执行顺序

总结

下面每条语句的前面都标明了执行顺序号。如果有兴趣可以查看MySQL源码。每个步骤都会产生一个虚拟表，该虚拟表被用作下一个步骤的输入。这些虚拟表对调用者(客户端应用程序或者外部查询)不可用。只有最后一步生成的表才会会给调用者。如果没有在查询中指定某一个子句，将跳过相应的步骤。

(8) SELECT：对 VT7 进行处理，产生 VT8
(9) DISTINCT：将重复的行从 VT8 中删除，产生 VT9
(11) <Top Num> <select list>： MySQL 没有 TOP 关键字（这是 SQL Server 中的关键字）。从VC10的开始处选择指定数量或比例的行，生成表VT11
(1) FROM [left_table]：对 FROM 后跟的表执行笛卡尔积(交叉联接)，生成虚拟表 VT1。
(3) <join_type> JOIN <right_table>：如果指定了OUTER JOIN (相对于 CROSS JOIN 或 INNER JOIN)，但是保留表(preserved table 翻译为保留表。注意：左外部联接把左表标记为保留表，右外部联接把右表标记为保留表，完全外部联接把两个表都标记为保留表 ) 中未找到匹配的行将作为外部行添加到 VT2，生成VT3。如果FROM子句包含两个以上的表，则对上一个联接生成的结果表和下一个表重复执行步骤1到步骤3，直到处理完所有的表为止。生成 VT3
(2) ON <join_condition>：对虚表VT1应用 ON 筛选器，只有那些使<join_condition>为真的行才被插入VT2。
(4) WHERE <where_condition>：对VT3应用 WHERE 筛选器。只有使 <where_condition>为 true 的行才被插入VT4。
(5) GROUP BY <group_by_list>：按 GROUP BY子句中的 条件列表(列列表) 对 VT4 中的行进行分组，生成 VT5
(6) WITH <CUBE | RollUP>：把超组(Suppergroups)插入VT5，生成 VT6。
(7) HAVING <having_condition>：对 VT6 应用 HAVING 筛选器。只有使<having_condition> 为 true 的组才会被插入 VT7
(10) ORDER BY <order_by_list>：将 VT9 中的行按 ORDER BY 子句中 条件列表(列列表) 顺序，生成一个游标(VC10)。这一步不同于其它步骤的是 "它不返回有效的表，而是返回一个游标"。SQL是基于集合理论的。集合不会预先对它的行排序，它只是成员的逻辑集合，成员的顺序无关紧要。当对表进行排序查询时可以返回一个对象，ANSI 把这种对象称为游标，游标包含按特定物理顺序组织的行。理解这一步是正确理解SQL的基础。因为这一步不返回表（而是返回游标），使用了ORDER BY子句的查询不能用作表表达式。表表达式包括：视图、内联表值函数、子查询、派生表和共用表达式。它的结果必须返回给期望得到物理记录的客户端应用程序。例如，下面的派生表查询无效，并产生一个错误：
select *
from ( select orderid,customerid from orders order by orderid )
as d
下面的视图也会产生错误：
create view my_view
as
select *
from orders
order by orderid
在SQL中，表表达式中不允许使用带有ORDER BY子句的查询，而在 T-SQL 中却有一个例外（应用TOP选项）。所以要记住，不要为表中的行假设任何特定的顺序。换句话说，除非你确定要有序行，否则不要指定 ORDER BY 子句。排序是需要成本的。
(11) LIMIT <limit_number>： 从VC10的开始处选择指定数量或比例的行，生成表VT11

总结：

在 MySQL 中，通常是先执行 WHERE、GROUP BY、HAVING 等子句进行数据筛选和分组，然后再执行 ORDER BY 进行排序，最后执行 LIMIT 进行结果数量限制。所以在写查询 sql 的时候应当遵守以下顺序。
执行 FROM 子句，确定查询的表。
执行 WHERE 子句，筛选行。
执行 GROUP BY 子句，根据条件执行分组。
执行 HAVING 子句，筛选符合条件的分组。
执行 SELECT 子句，确定要返回的列。
执行 ORDER BY 子句，对结果进行排序。
执行 LIMIT 子句，限制返回的行数。
示例写法：SELECT XXX FROM XXX WHERE XXX GROUP BY XXX HAVING XXX ORDER BY XXX LIMIT XXX;

养成日常 sql 好习惯，也就是在实现功能同时有考虑性能的思想

数据库是能进行集合运算的工具，应该尽量利用这个工具，所谓集合运算实际就是批量运算，就是尽量减少在客户端进行大数据量的循环操作，而用SQL语句或者存储过程代替。
只返回需要的数据。返回数据到客户端至少需要数据库提取数据、网络传输、客户端接收以及客户端处理等环节。如果返回不需要的数据，就会增加服务器、网络和客户端的负担。
不要写 SELECT * 的语句，而是选择你需要的字段。当在 SQL 语句中连接多个表时，可以使用表的别名，并把别名前缀于每个Column上。这样一来，就可以减少解析的时间并减少那些由 Column 歧义引起的语法错误。
如有表 table1（ID,col1）和 table2 （ID,col2）
Select A.ID, A.col1, B.col2
-- Select A.ID, col1, col2 –不要这么写，不利于将来程序扩展
from table1 A inner join table2 B on A.ID=B.ID Where …
合理写 WHERE 子句。不要写没有 WHERE 的 SQL 语句。没有WHERE条件时使用 SELECT TOP N *
减少多次的数据转换
杜绝不必要的子查询和连接表，子查询在执行计划一般解释成外连接，多余的连接表带来额外的开销。
尽量使用索引，尽量不要对索引字段进行运算而是要想办法做变换，尽量不要对索引字段进行格式转，尽量不要对索引字段使用函数，尽量不要对索引字段进行多字段连接
多表连接的时候，连接条件必须写全，宁可重复，不要缺漏。连接条件尽量使用聚集索引。注意 ON、WHERE 和 HAVING 部分条件的区别。ON是最先执行， WHERE次之，HAVING最后。因为ON是先把不符合条件的记录过滤后才进行统计，它就可以减少中间运算要处理的数据，按理说应该速度是最快的，WHERE也应该比 HAVING快点的，因为它过滤数据后才进行SUM，在两个表联接时才用ON的，所以在一个表的时候，就剩下WHERE跟HAVING比较了
减少判断的次数，在 IN 后面值的列表中，将出现最频繁的值放在最前面，出现得最少的放在最后面，减少判断的次数
在没有必要时不要用 DISTINCT
减少访问数据库的次数

执行 FROM 语句

SQL 语句在执行过程中都会产生一个虚拟表用来保存SQL语句的执行结果（这是重点），现在就来跟踪这些虚拟表的变化，来追踪查询结果的执行顺序和生成过程。

第一步，执行FROM语句。首先需要知道最开始从哪个表开始的，这就是FROM告诉我们的。现在有了<left_table>和<right_table>两个表，我们到底从哪个表开始，还是从两个表进行某种联系以后再开始呢？它们之间如何产生联系呢？——笛卡尔积

关于什么是笛卡尔积，请自行 Google 补脑。经过 FROM 语句对两个表执行笛卡尔积，会得到一个虚拟表，暂且叫 VT1（vitual table 1），内容如下：

+-------------+----------+----------+-------------+
| customer_id | city     | order_id | customer_id |
+-------------+----------+----------+-------------+
| 163         | hangzhou |        1 | 163         |
| 9you        | shanghai |        1 | 163         |
| baidu       | hangzhou |        1 | 163         |
| tx          | hangzhou |        1 | 163         |
| 163         | hangzhou |        2 | 163         |
| 9you        | shanghai |        2 | 163         |
| baidu       | hangzhou |        2 | 163         |
| tx          | hangzhou |        2 | 163         |
| 163         | hangzhou |        3 | 9you        |
| 9you        | shanghai |        3 | 9you        |
| baidu       | hangzhou |        3 | 9you        |
| tx          | hangzhou |        3 | 9you        |
| 163         | hangzhou |        4 | 9you        |
| 9you        | shanghai |        4 | 9you        |
| baidu       | hangzhou |        4 | 9you        |
| tx          | hangzhou |        4 | 9you        |
| 163         | hangzhou |        5 | 9you        |
| 9you        | shanghai |        5 | 9you        |
| baidu       | hangzhou |        5 | 9you        |
| tx          | hangzhou |        5 | 9you        |
| 163         | hangzhou |        6 | tx          |
| 9you        | shanghai |        6 | tx          |
| baidu       | hangzhou |        6 | tx          |
| tx          | hangzhou |        6 | tx          |
| 163         | hangzhou |        7 | NULL        |
| 9you        | shanghai |        7 | NULL        |
| baidu       | hangzhou |        7 | NULL        |
| tx          | hangzhou |        7 | NULL        |
+-------------+----------+----------+-------------+

总共有28（table1的记录条数 * table2的记录条数）条记录。这就是 VT1 的结果，接下来的操作就在 VT1 的基础上进行。

执行 ON 过滤

执行完笛卡尔积以后，接着就进行 ON a.customer_id = b.customer_id 条件过滤，根据ON中指定的条件，去掉那些不符合条件的数据，得到VT2表，内容如下：

+-------------+----------+----------+-------------+
| customer_id | city     | order_id | customer_id |
+-------------+----------+----------+-------------+
| 163         | hangzhou |        1 | 163         |
| 163         | hangzhou |        2 | 163         |
| 9you        | shanghai |        3 | 9you        |
| 9you        | shanghai |        4 | 9you        |
| 9you        | shanghai |        5 | 9you        |
| tx          | hangzhou |        6 | tx          |
+-------------+----------+----------+-------------+

VT2 就是经过 on 条件筛选以后得到的有用数据，而接下来的操作将在VT2的基础上继续进行。

添加外部行

这一步只有在连接类型为 OUTER JOIN 时才发生，如LEFT OUTER JOIN、RIGHT OUTER JOIN和FULL OUTER JOIN。在大多数时候都会省略 OUTER 关键字，但OUTER表示的就是外部行的概念。SQL 的各种 JOIN 用法：https://www.runoob.com/w3cnote/sql-join-image-explain.html

LEFT OUTER JOIN 把左表记为保留表，得到的结果为：

+-------------+----------+----------+-------------+
| customer_id | city     | order_id | customer_id |
+-------------+----------+----------+-------------+
| 163         | hangzhou |        1 | 163         |
| 163         | hangzhou |        2 | 163         |
| 9you        | shanghai |        3 | 9you        |
| 9you        | shanghai |        4 | 9you        |
| 9you        | shanghai |        5 | 9you        |
| tx          | hangzhou |        6 | tx          |
| baidu       | hangzhou |     NULL | NULL        |
+-------------+----------+----------+-------------+

RIGHT OUTER JOIN 把右表记为保留表，得到的结果为：

+-------------+----------+----------+-------------+
| customer_id | city     | order_id | customer_id |
+-------------+----------+----------+-------------+
| 163         | hangzhou |        1 | 163         |
| 163         | hangzhou |        2 | 163         |
| 9you        | shanghai |        3 | 9you        |
| 9you        | shanghai |        4 | 9you        |
| 9you        | shanghai |        5 | 9you        |
| tx          | hangzhou |        6 | tx          |
| NULL        | NULL     |        7 | NULL        |
+-------------+----------+----------+-------------+

FULL OUTER JOIN 把左右表都作为保留表，得到的结果为：

+-------------+----------+----------+-------------+
| customer_id | city     | order_id | customer_id |
+-------------+----------+----------+-------------+
| 163         | hangzhou |        1 | 163         |
| 163         | hangzhou |        2 | 163         |
| 9you        | shanghai |        3 | 9you        |
| 9you        | shanghai |        4 | 9you        |
| 9you        | shanghai |        5 | 9you        |
| tx          | hangzhou |        6 | tx          |
| baidu       | hangzhou |     NULL | NULL        |
| NULL        | NULL     |        7 | NULL        |
+-------------+----------+----------+-------------+

添加外部行的工作就是在VT2表的基础上添加保留表中被过滤条件过滤掉的数据，非保留表中的数据被赋予NULL值，最后生成虚拟表VT3。

由于我在准备的测试SQL查询逻辑语句中使用的是LEFT JOIN，过滤掉了以下这条数据：

| baidu       | hangzhou |     NULL | NULL        |

现在就把这条数据添加到VT2表中，得到的VT3表如下：

+-------------+----------+----------+-------------+
| customer_id | city     | order_id | customer_id |
+-------------+----------+----------+-------------+
| 163         | hangzhou |        1 | 163         |
| 163         | hangzhou |        2 | 163         |
| 9you        | shanghai |        3 | 9you        |
| 9you        | shanghai |        4 | 9you        |
| 9you        | shanghai |        5 | 9you        |
| tx          | hangzhou |        6 | tx          |
| baidu       | hangzhou |     NULL | NULL        |
+-------------+----------+----------+-------------+

接下来的操作都会在该VT3表上进行。

执行 WHERE 过滤

对添加外部行得到的VT3进行WHERE过滤，只有符合<where_condition>的记录才会输出到虚拟表VT4中。当我们执行WHERE a.city = 'hangzhou'的时候，就会得到以下内容，并存在虚拟表VT4中：

+-------------+----------+----------+-------------+
| customer_id | city     | order_id | customer_id |
+-------------+----------+----------+-------------+
| 163         | hangzhou |        1 | 163         |
| 163         | hangzhou |        2 | 163         |
| tx          | hangzhou |        6 | tx          |
| baidu       | hangzhou |     NULL | NULL        |
+-------------+----------+----------+-------------+

但是在使用WHERE子句时，需要注意以下两点：

由于数据还没有分组，因此现在还不能在WHERE过滤器中使用where_condition=MIN(col)这类对分组统计的过滤；
由于还没有进行列的选取操作，因此在SELECT中使用列的别名也是不被允许的，如：SELECT city as c FROM t WHERE c='shanghai';是不允许出现的。

执行 GROUP BY 分组

GROU BY子句主要是对使用WHERE子句得到的虚拟表进行分组操作。我们执行测试语句中的GROUP BY a.customer_id，就会得到以下内容：

+-------------+----------+----------+-------------+
| customer_id | city     | order_id | customer_id |
+-------------+----------+----------+-------------+
| 163         | hangzhou |        1 | 163         |
| baidu       | hangzhou |     NULL | NULL        |
| tx          | hangzhou |        6 | tx          |
+-------------+----------+----------+-------------+

得到的内容会存入虚拟表VT5中，此时，我们就得到了一个VT5虚拟表，接下来的操作都会在该表上完成。

执行 HAVING 过滤

HAVING子句主要和GROUP BY子句配合使用，对分组得到的VT5虚拟表进行条件过滤。当我执行测试语句中的HAVING count(b.order_id) < 2时，将得到以下内容：

+-------------+----------+----------+-------------+
| customer_id | city     | order_id | customer_id |
+-------------+----------+----------+-------------+
| baidu       | hangzhou |     NULL | NULL        |
| tx          | hangzhou |        6 | tx          |
+-------------+----------+----------+-------------+

这就是虚拟表VT6。

执行 SELECT 列表

现在才会执行到SELECT子句，不要以为SELECT子句被写在第一行，就是第一个被执行的。

我们执行测试语句中的SELECT a.customer_id, COUNT(b.order_id) as total_orders，从虚拟表VT6中选择出我们需要的内容。我们将得到以下内容：

+-------------+--------------+
| customer_id | total_orders |
+-------------+--------------+
| baidu       |            0 |
| tx          |            1 |
+-------------+--------------+

不，还没有完，这只是虚拟表VT7。

执行 DISTINCT 子句

如果在查询中指定了DISTINCT子句，则会创建一张内存临时表（如果内存放不下，就需要存放在硬盘了）。这张临时表的表结构和上一步产生的虚拟表VT7是一样的，不同的是对进行DISTINCT操作的列增加了一个唯一索引，以此来除重复数据。

由于我的测试SQL语句中并没有使用DISTINCT，所以，在该查询中，这一步不会生成一个虚拟表。

执行 ORDER BY 子句

对虚拟表中的内容按照指定的列进行排序，然后返回一个新的虚拟表，我们执行测试SQL语句中的ORDER BY total_orders DESC，就会得到以下内容：

+-------------+--------------+
| customer_id | total_orders |
+-------------+--------------+
| tx          |            1 |
| baidu       |            0 |
+-------------+--------------+

可以看到这是对 total_orders 列进行降序排列的。上述结果会存储在VT8中。

执行 LIMIT 子句

从上一步得到的虚拟表中选出从指定位置开始的指定行数据。对于没有应用ORDER BY的LIMIT子句，得到的结果同样是无序的，所以，很多时候，我们都会看到LIMIT子句会和ORDER BY子句一起使用。用法：LIMIT n, m

常用 SQL 语句

sql server 经典常用函数：https://www.cnblogs.com/daimaxuejia/p/7929089.html
SQL Server 常用函数总结：https://www.cnblogs.com/studydp/p/9114419.html

SQL SELECT 语句
SELECT column1, column2....columnN
FROM table_name;
SQL DISTINCT 子句
SELECT DISTINCT column1, column2....columnN
FROM table_name;
SQL WHERE 子句
SELECT column1, column2....columnN
FROM table_name
WHERE CONDITION;
SQL AND/OR 子句
SELECT column1, column2....columnN
FROM table_name
WHERE CONDITION-1 {AND|OR} CONDITION-2;
SQL IN 子句
SELECT column1, column2....columnN
FROM table_name
WHERE column_name IN (val-1, val-2,...val-N);
SQL BETWEEN 子句
SELECT column1, column2....columnN
FROM table_name
WHERE column_name BETWEEN val-1 AND val-2;
SQL Like 子句
SELECT column1, column2....columnN
FROM table_name
WHERE column_name LIKE { PATTERN };
SQL ORDER BY 子句
SELECT column1, column2....columnN
FROM table_name
WHERE CONDITION
ORDER BY column_name {ASC|DESC};
SQL GROUP BY 子句
SELECT SUM(column_name)
FROM table_name
WHERE CONDITION
GROUP BY column_name;
SQL COUNT 子句
SELECT COUNT(column_name)
FROM table_name
WHERE CONDITION;
SQL HAVING 子句
SELECT SUM(column_name)
FROM table_name
WHERE CONDITION
GROUP BY column_name
HAVING (arithematic function condition);
SQL CREATE TABLE 语句
CREATE TABLE table_name(
column1 datatype,
column2 datatype,
column3 datatype,
.....
columnN datatype,
PRIMARY KEY( one or more columns )
);
SQL DROP TABLE 语句
DROP TABLE table_name;
SQL CREATE INDEX 语句
CREATE UNIQUE INDEX index_name
ON table_name ( column1, column2,...columnN);
SQL DROP INDEX 语句
ALTER TABLE table_name
DROP INDEX index_name;
SQL DESC 语句
DESC table_name;
SQL TRUNCATE TABLE 语句
TRUNCATE TABLE table_name;
SQL ALTER TABLE 语句
ALTER TABLE table_name {ADD|DROP|MODIFY} column_name {data_ype};
SQL ALTER TABLE(rename) 语句
ALTER TABLE table_name RENAME TO new_table_name;
SQL INSERT INTO 语句
INSERT INTO table_name( column1, column2....columnN)
VALUES ( value1, value2....valueN);
SQL UPDATE 语句
UPDATE table_name
SET column1 = value1, column2 = value2....columnN=valueN
[ WHERE CONDITION ];
SQL DELETE 语句
DELETE FROM table_name
WHERE {CONDITION};
SQL CREATE DATABASE 语句
CREATE DATABASE database_name;
SQL DROP DATABASE 语句
DROP DATABASE database_name;
SQL USE 语句
USE database_name;
SQL COMMIT 语句
COMMIT;
SQL ROLLBACK 语句
ROLLBACK;

选择：select * from table1 where 范围
插入：insert into table1(field1,field2) values(value1,value2)
删除：delete from table1 where 范围
更新：update table1 set field1=value1 where 范围
查找：select * from table1 where field1 like ’%value1%’ ---like的语法很精妙，查资料!
排序：select * from table1 order by field1,field2 [desc]
总数：select count as totalcount from table1
求和：select sum(field1) as sumvalue from table1
平均：select avg(field1) as avgvalue from table1
最大：select max(field1) as maxvalue from table1
最小：select min(field1) as minvalue from table1

前10条记录：select top 10 * form table1 where 范围

开头到N条记录：Select Top N * From 表
N到M条记录(要有主索引ID)：Select Top M-N * From 表 Where ID in (Select Top M ID From 表) Order by ID   Desc

选择从10到15的记录：select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc
N到结尾记录：Select Top N * From 表 Order by ID Desc

子查询：(表名1：a 表名2：b)：

select a,b,c from a where a IN (select d from b )

或者: select a,b,c from a where a IN (1,2,3)

或者：select * from (SELECT a,b,c FROM a) T where t.a > 1;

外连接查询：(表名1：a 表名2：b)
      select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c

between 用法：between 限制查询数据范围时包括了边界值，not between 不包括

      select * from table1 where time between time1 and time2
      select a,b,c, from table1 where a not between 数值1 and 数值2

in 的使用方法：select * from table1 where a [not] in ('值1','值2','值3')

两张关联表，删除主表中已经在副表中没有的信息：delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )

四表联查问题：select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where.....

日程安排提前五分钟提醒：SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5

查询所有在 TableA中但不在TableB和TableC中的行，并消除所有重复行而派生出一个结果表：(select a from tableA ) except (select a from tableB) except (select a from tableC)

列出数据库里所有的表名：select name from sysobjects where type='U' // U代表用户

列出表里的所有的列名：select name from syscolumns where id=object_id('TableName')

案例：

例如1：一张表有一万多条记录，表的第一个字段 RecID 是自增长字段，写一个SQL语句，找出表的第31到第40个记录。

select top 10 recid from A where recid not in(select top 30 recid from A)

分析：如果这样写会产生某些问题，如果recid在表中存在逻辑索引。

select top 10 recid from A where……是从索引中查找，而后面的select top 30 recid from A则在数据表中查找，这样由于索引中的顺序有可能和数据表中的不一致，这样就导致查询到的不是本来的欲得到的数据。

解决方案

1，用 order by select top 30 recid from A order by ricid 如果该字段不是自增长，就会出现问题

2，在那个子查询中也加条件：select top 30 recid from A where recid>-1

例 2：查询表中的最后以条记录，并不知道这个表共有多少数据,以及表结构。

set @s = 'select top 1 * from T where pid not in (select top ' + str(@count-1) + ' pid from T)'

print @s exec sp_executesql @s

9：获取当前数据库中的所有用户表

select Name from sysobjects where xtype='u' and status>=0

10：获取某一个表的所有字段

select name from syscolumns where id=object_id('表名')

select name from syscolumns where id in (select id from sysobjects where type = 'u' and name = '表名')

两种方式的效果相同

11：查看与某一个表相关的视图、存储过程、函数

select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%表名%'

12：查看当前数据库中所有存储过程

select name as 存储过程名称 from sysobjects where xtype='P'

13：查询用户创建的所有数据库

select * from master..sysdatabases D where sid not in(select sid from master..syslogins where name='sa')
或者
select dbid, name AS DB_NAME from master..sysdatabases where sid <> 0x01

14：查询某一个表的字段和数据类型

select column_name,data_type from information_schema.columns
where table_name = '表名'

15：不同服务器数据库之间的数据操作

--创建链接服务器

exec sp_addlinkedserver   'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或ip地址 '

exec sp_addlinkedsrvlogin  'ITSV ', 'false ',null, '用户名 ', '密码 '

--查询示例

select * from ITSV.数据库名.dbo.表名

--导入示例

select * into 表 from ITSV.数据库名.dbo.表名

--以后不再使用时删除链接服务器

exec sp_dropserver  'ITSV ', 'droplogins '

--连接远程/局域网数据(openrowset/openquery/opendatasource)

--1、openrowset

--查询示例

select * from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)

--生成本地表

select * into 表 from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)

--把本地表导入远程表

insert openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)

select *from 本地表

--更新本地表

update b

set b.列A=a.列A

from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)as a inner join 本地表 b

on a.column1=b.column1

--openquery用法需要创建一个连接

--首先创建一个连接创建链接服务器

exec sp_addlinkedserver   'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或ip地址 '

--查询

select *

FROM openquery(ITSV,  'SELECT *  FROM 数据库.dbo.表名 ')

--把本地表导入远程表

insert openquery(ITSV,  'SELECT *  FROM 数据库.dbo.表名 ')

select * from 本地表

--更新本地表

update b

set b.列B=a.列B

FROM openquery(ITSV,  'SELECT * FROM 数据库.dbo.表名 ') as a

inner join 本地表 b on a.列A=b.列A

--3、opendatasource/openrowset

SELECT   *

FROM   opendatasource( 'SQLOLEDB ',  'Data Source=ip/ServerName;User ID=登陆名;Password=密码 ' ).test.dbo.roy_ta

--把本地表导入远程表

insert opendatasource( 'SQLOLEDB ',  'Data Source=ip/ServerName;User ID=登陆名;Password=密码 ').数据库.dbo.表名

select * from 本地表

SQL 数据类型

在数据库中，表的每列都具有特定的数据类型。数据类型指定列(字段)可以容纳的数据类型，例如字符串，数值和日期时间值。

SQL 中通用的数据类型：

数据类型	描述
CHARACTER(n)	字符/字符串。固定长度 n。
VARCHAR(n) 或 CHARACTER VARYING(n)	字符/字符串。可变长度。最大长度 n。
BINARY(n)	二进制串。固定长度 n。
BOOLEAN	存储 TRUE 或 FALSE 值
VARBINARY(n) 或 BINARY VARYING(n)	二进制串。可变长度。最大长度 n。
INTEGER(p)	整数值（没有小数点）。精度 p。
SMALLINT	整数值（没有小数点）。精度 5。
INTEGER	整数值（没有小数点）。精度 10。
BIGINT	整数值（没有小数点）。精度 19。
DECIMAL(p,s)	精确数值，精度 p，小数点后位数 s。例如：decimal(5,2) 是一个小数点前有 3 位数，小数点后有 2 位数的数字。
NUMERIC(p,s)	精确数值，精度 p，小数点后位数 s。（与 DECIMAL 相同）
FLOAT(p)	近似数值，尾数精度 p。一个采用以 10 为基数的指数计数法的浮点数。该类型的 size 参数由一个指定最小精度的单一数字组成。
REAL	近似数值，尾数精度 7。
FLOAT	近似数值，尾数精度 16。
DOUBLE PRECISION	近似数值，尾数精度 16。
DATE	存储年、月、日的值。
TIME	存储小时、分、秒的值。
TIMESTAMP	存储年、月、日、小时、分、秒的值。
INTERVAL	由一些整数字段组成，代表一段时间，取决于区间的类型。
ARRAY	元素的固定长度的有序集合
MULTISET	元素的可变长度的无序集合
XML	存储 XML 数据

然而，不同的数据库对数据类型定义提供不同的选择。下面的表格显示了各种不同的数据库平台上一些数据类型的通用名称：

数据类型	Access	SQLServer	Oracle	MySQL	PostgreSQL
boolean	Yes/No	Bit	Byte	N/A	Boolean
integer	Number (integer)	Int	Number	Int Integer	Int Integer
float	Number (single)	Float Real	Number	Float	Numeric
currency	Currency	Money	N/A	N/A	Money
string (fixed)	N/A	Char	Char	Char	Char
string (variable)	Text (<256) Memo (65k+)	Varchar	Varchar Varchar2	Varchar	Varchar
binary object	OLE Object Memo	Binary (fixed up to 8K) Varbinary (<8K) Image (<2GB)	Long Raw	Blob Text	Binary Varbinary

SQL 运算符

运算符的优先级

：https://blog.csdn.net/z_johnny/article/details/114108040

运算符的优先级决定了不同的运算符在表达式中计算的先后顺序，一般情况下，级别高的运算符优先进行计算，如果级别相同，MySQL 按表达式的顺序从左到右依次计算，在无法确定优先级的情况下，可以使用圆括号 “()” 来改变优先级，并且这样会使计算过程更加清晰

优先级 ( 由低到高 )	运算符
1	=(赋值运算）、:=
2	\|\|、OR
3	XOR
4	&&、AND
5	NOT
6	BETWEEN、CASE、WHEN、THEN、ELSE
7	=(比较运算）、<=>、>=、>、<=、<、<>、!=、 IS、LIKE、REGEXP、IN
8	\|
9	&
10	<<、>>
11	-(减号）、+
12	*、/、%
13	^
14	-(负号）、~（位反转）
15	!

算术运算符

假设变量 a 的值是：10，变量 b 的值是：20

操作符	描述	示例
`+`	加法，执行加法运算。	`a + b = 30`
`-`	减法，执行减法运算。	`a + b = -10`
`*`	除法，执行除法运算。	`a * b = 200`
`/`	用左操作数除右手操作数。	`b / a = 2`
`%`	用左手操作数除左手操作数并返回余数。	`b % a = 0`

示例：

select 10 + 20;
select 10 * 20;
select 10 / 5;
select 14 % 5;

比较运算符

包括 "大于、小于、等于、不等于" 等等。可以用于比较数字、字符串和表达式的值。比较结果为真，则返回 1，为假则返回 0，比较结果不确定则返回 NULL。

等于(=)运算符不能用于比较 NULL值。要比较空(null)值，用 IS NULL 运算符
字符串的比较是不区分大小写的。只有“<=>”才支持NULL的比较，其他比较运算对有NULL操作数时返回的结果就是NULL，永远返回false，即 NULL = NULL 返回false

变量 a 的值是：10，变量 b 的值是：20

操作符	描述	示例
=	比较两个操作数的值是否相等，如果相等则为真(true)。	(a = b) 结果为false。
!=	检查两个操作数的值是否相等，如果值不相等则条件为真(true)。	(a != b) 结果为：true
<>	检查两个操作数的值是否相等，如果值不相等则条件为真(true)。	(a <> b) 结果为：true
>	检查左操作数的值是否大于右操作数的值，如果是，则条件为真(true)。	(a > b) 结果为：false
<	检查左操作数的值是否小于右操作数的值，如果是，则条件为真(true)。	(a < b) 结果为：true
>=	检查左操作数的值是否大于或等于右操作数的值，如果是，则条件为真(true)。	(a >= b) 结果为：false
<=	检查左操作数的值是否小于或等于右操作数的值，如果是，则条件为真(true)。	(a <= b) 结果为：true
!<	检查左操作数的值是否不小于右操作数的值，如果是，则条件变为真(true)。	(a !< b) 结果为：false
!>	检查左操作数的值是否不大于右操作数的值，如果是，则条件变为真(true)。	(a !> b) 结果为：true

SELECT
employee_id, first_name, last_name, department_id
FROM
employees
WHERE
department_id <> 8
AND department_id <> 10
ORDER BY first_name , last_name;

SELECT
employee_id, first_name, last_name, salary
FROM
employees
WHERE
salary > 12000
ORDER BY salary DESC;

逻辑运算符

与比较运算符类似，逻辑运算符返回值为三个：true，false 或 unknown 的其中一个。

SQL中可用的所有逻辑运算符。

操作符	描述
ALL	ALL 运算符用于将值与另一个值集中的所有值进行比较。
AND	AND 运算符允许在SQL语句的 WHERE 子句中指定多个条件。
ANY	ANY 运算符用于根据条件将值与列表中的任何适用值进行比较。
BETWEEN	BETWEEN 运算符用于搜索在给定最小值和最大值内的值。
EXISTS	EXISTS 运算符用于搜索指定表中是否存在满足特定条件的行。
IN	IN 运算符用于将值与已指定的文字值列表进行比较。
LIKE	LIKE 运算符用于使用通配符运算符将值与类似值进行比较。
NOT	NOT运算符反转使用它的逻辑运算符的含义。例如：NOT EXISTS, NOT BETWEEN, NOT IN 等等，这是一个否定运算符。
OR	NOT 运算符反转使用它的逻辑运算符的含义。例如：NOT EXISTS, NOT BETWEEN, NOT OR 运算符用于组合 SQL 语句的 WHERE 子句中的多个条件。
IS NULL	NULL 运算符用于将值与 NULL 值进行比较。
UNIQUE	UNIQUE 运算符搜索指定表的每一行的唯一性(无重复项)。

与、或、非、异或

运算符	作用	说明
NOT 或者 !	逻辑非	1. 当操作数为 0（假）时，返回值为 1 2. 当操作数为非零值时，返回值为 0 3. 当操作数为 NULL 时，返回值为 NULL
AND 或者 &&	逻辑与	1. 当所有操作数都为非零值并且不为 NULL 时，返回值为 1； 2. 当一个或多个操作数为 0 时，返回值为 0； 3. 操作数中有任何一个为 NULL 时，返回值为 NULL。注意：AND 运算符可以有多个操作数，但要注意多个操作数运算时，AND 两边一定要使用空格隔开，不然会影响结果的正确性。
OR 和 \|\|	逻辑或	1. 当两个操作数都为非 NULL 值时，如果有任意一个操作数为非零值，则返回值为 1，否则结果为 0； 2. 当有一个操作数为 NULL 时，如果另一个操作数为非零值，则返\回值为 1，否则结果为NULL； 3. 假如两个操作数均为 NULL 时，则返回值为 NULL。
XOR	逻辑异或	1. 当任意一个操作数为 NULL 时，返回值为 NULL； 2.对于非 NULL 的操作数，如果两个操作数都是非 0 值或者都是 0 值，则返回值为 0； 3. 如果一个为0值，另一个为非 0 值，返回值为 1。

位运算符

所谓位运算，就是按照内存中的比特位（Bit）进行操作，这是计算机能够支持的最小单位的运算。程序中所有的数据在内存中都是以二进制形式存储的，位运算就是对这些二进制数据进行操作。

包括按位与、按位或、按位取反、按位异或、按位左移和按位右移等位运算符。位运算必须先将数据转换为补码，然后在根据数据的补码进行操作。运算完成后，将得到的值转换为原来的类型（十进制数），返回给用户。

位运算一般用于操作整数，对整数进行位运算才有实际的意义。
整数在内存中是以补码形式存储的，正数的补码形式和原码形式相同，而负数的补码形式是它的补码，而不是它的原码。
MySQL中的整数字面量（常量整数，也就是直接书写出来的整数）默认以 8 个字节（Byte）来表示，也就是 64 位（Bit）

MySQL 支持 6 种位运算符:

运算符	作用	格式	示例	说明
\|	位或	a \| b	5 \| 8	1. 运算的两个二进制位有一个为 1 时，结果就为 1 2. 两个都为 0 时结果才为 0
&	位与	a & b	5 & 8	1. 运算的两个二进制位都为 1 时，结果就为 1，否则为 0 注意：任何数和 -1 进行位与运算时，最终结果都为任何数本身的十进制数
^	位异或	a ^ b	5 ^ 8	1. 运算的两个二进制位不同时，结果为 1，相同时，结果为 0
~	位取反	~a	~5	1. 就是做 NOT 操作，1 取反后变 0，0 取反后按指定值的补码形式进行左移，左移指定位数之后，左边高位的数值被移出并丢弃，右边低位空出的位置用 0 补齐
<<	位左移	a << b	5 << 2 表示整数 5 按位左移 2 位	1. 按指定值的补码形式进行左移，左移指定位数之后，左边高位的数值被移出并丢弃，右边低位空出的位置用 0 补齐
>>	位右移	a >> b	5 >> 2 表示整数 5 按位右移 2 位	1. 按指定值的补码形式进行右移，右移指定位数之后，右边低位的数值被移出并丢弃，左边高位空出的位置用 0 补齐。

SQL 表达式

表达式是一个或多个值，运算符和SQL函数的组合，它们计算结果为确定的值。这些SQL 表达式就像公式，它们是用查询语言编写的。还可以使用它们在数据库中查询特定的数据集。

有三种不同类型的SQL表达式，如下所述 -

布尔表达式
数字表达式
日期表达式

示例：布尔表达式

SELECT
employee_id, first_name, last_name, salary
FROM
employees e
WHERE
salary=12000;

示例：数字表达式

SELECT (150 + 55) AS ADDITION;
SELECT COUNT(*) AS "total_rows" FROM employees;

示例：日期表达式

SELECT CURRENT_TIMESTAMP;
SELECT GETDATE();

2、MySQL 常用命令

MySQL 官网：https://www.mysql.com/cn/products/
MySQL 8.0文档：https://dev.mysql.com/doc/refman/8.0/en/

MySQL是一个关系型数据库管理系统，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型网站的开发都选择MySQL作为网站数据库。由于其社区版的性能卓越，搭配 PHP 和 Apache 可组成良好的开发环境。现在主要介绍常用的MySQL命令，包括连接数据库，修改密码，管理用户，操作数据库，操作数据表，数据库备份等

注意：你必须首先登录到 MySQL 中，以下操作都是在 MySQL 的提示符下进行的，而且每个命令以分号结束。

例如，如果在文件my_file.sql 中存放有查询，可如下执行这些查询：
例如，如果您想将建表语句提前写在sql.txt中:
mysql > mysql -h myhost -u root -p database < sql.txt

SQL 示例数据库

：https://www.yiibai.com/sql/sql-sample-database.html

在这个HR 示例数据库有7个表：

employees表存储员工的数据信息。
jobs表存储工作数据信息，包括职位和工资范围。
departments表存储部门数据信息。
dependents表存储员工的家属信息。
locations表存储公司各部门的所在位置信息。
countries表存储公司开展业务的国家/地区的数据。
regions表存储亚洲，欧洲，美洲，中东和非洲等地区的数据。这些国家分为不同的地区。

MySQL中创建数据库

CREATE TABLE regions (
    region_id INT (11) AUTO_INCREMENT PRIMARY KEY,
    region_name VARCHAR (25) DEFAULT NULL
);

CREATE TABLE countries (
    country_id CHAR (2) PRIMARY KEY,
    country_name VARCHAR (40) DEFAULT NULL,
    region_id INT (11) NOT NULL,
    FOREIGN KEY (region_id) REFERENCES regions (region_id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE locations (
    location_id INT (11) AUTO_INCREMENT PRIMARY KEY,
    street_address VARCHAR (40) DEFAULT NULL,
    postal_code VARCHAR (12) DEFAULT NULL,
    city VARCHAR (30) NOT NULL,
    state_province VARCHAR (25) DEFAULT NULL,
    country_id CHAR (2) NOT NULL,
    FOREIGN KEY (country_id) REFERENCES countries (country_id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE jobs (
    job_id INT (11) AUTO_INCREMENT PRIMARY KEY,
    job_title VARCHAR (35) NOT NULL,
    min_salary DECIMAL (8, 2) DEFAULT NULL,
    max_salary DECIMAL (8, 2) DEFAULT NULL
);

CREATE TABLE departments (
    department_id INT (11) AUTO_INCREMENT PRIMARY KEY,
    department_name VARCHAR (30) NOT NULL,
    location_id INT (11) DEFAULT NULL,
    FOREIGN KEY (location_id) REFERENCES locations (location_id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE employees (
    employee_id INT (11) AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR (20) DEFAULT NULL,
    last_name VARCHAR (25) NOT NULL,
    email VARCHAR (100) NOT NULL,
    phone_number VARCHAR (20) DEFAULT NULL,
    hire_date DATE NOT NULL,
    job_id INT (11) NOT NULL,
    salary DECIMAL (8, 2) NOT NULL,
    manager_id INT (11) DEFAULT NULL,
    department_id INT (11) DEFAULT NULL,
    FOREIGN KEY (job_id) REFERENCES jobs (job_id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (department_id) REFERENCES departments (department_id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (manager_id) REFERENCES employees (employee_id)
);

CREATE TABLE dependents (
    dependent_id INT (11) AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR (50) NOT NULL,
    last_name VARCHAR (50) NOT NULL,
    relationship VARCHAR (25) NOT NULL,
    employee_id INT (11) NOT NULL,
    FOREIGN KEY (employee_id) REFERENCES employees (employee_id) ON DELETE CASCADE ON UPDATE CASCADE
);

导入数据

/*Data for the table regions */

INSERT INTO regions(region_id,region_name) VALUES (1,'欧洲');
INSERT INTO regions(region_id,region_name) VALUES (2,'美洲');
INSERT INTO regions(region_id,region_name) VALUES (3,'亚洲');
INSERT INTO regions(region_id,region_name) VALUES (4,'中东和非洲');

/*Data for the table countries */
INSERT INTO countries(country_id,country_name,region_id) VALUES ('AR','阿根廷',2);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('AU','澳大利亚',3);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('BE','比利时',1);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('BR','巴西',2);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('CA','加拿大',2);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('CH','瑞士',1);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('CN','中国',3);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('DE','德国',1);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('DK','丹麦',1);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('EG','埃及',4);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('FR','法国',1);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('HK','香港',3);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('IL','以色列',4);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('IN','印度',3);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('IT','意大利',1);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('JP','日本',3);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('KW','科威特',4);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('MX','墨西哥',2);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('NG','尼日利亚',4);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('NL','荷兰',1);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('SG','新加坡',3);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('UK','英国',1);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('US','美国',2);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('ZM','赞比亚',4);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('ZW','津巴布韦',4);



/*Data for the table locations */
INSERT INTO locations(location_id,street_address,postal_code,city,state_province,country_id) VALUES (1400,'2014 Jabberwocky Rd','26192','Southlake','Texas','US');
INSERT INTO locations(location_id,street_address,postal_code,city,state_province,country_id) VALUES (1500,'2011 Interiors Blvd','99236','South San Francisco','California','US');
INSERT INTO locations(location_id,street_address,postal_code,city,state_province,country_id) VALUES (1700,'2004 Charade Rd','98199','Seattle','Washington','US');
INSERT INTO locations(location_id,street_address,postal_code,city,state_province,country_id) VALUES (1800,'147 Spadina Ave','M5V 2L7','Toronto','Ontario','CA');
INSERT INTO locations(location_id,street_address,postal_code,city,state_province,country_id) VALUES (2400,'8204 Arthur St',NULL,'London',NULL,'UK');
INSERT INTO locations(location_id,street_address,postal_code,city,state_province,country_id) VALUES (2500,'Magdalen Centre, The Oxford Science Park','OX9 9ZB','Oxford','Oxford','UK');
INSERT INTO locations(location_id,street_address,postal_code,city,state_province,country_id) VALUES (2700,'Schwanthalerstr. 7031','80925','Munich','Bavaria','DE');



/*Data for the table jobs */

INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (1,'会计师',4200.00,9000.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (2,'会计经理',8200.00,16000.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (3,'行政助理',3000.00,6000.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (4,'主席',20000.00,40000.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (5,'行政副主席',15000.00,30000.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (6,'会计',4200.00,9000.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (7,'财务经理',8200.00,16000.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (8,'人力资源代表',4000.00,9000.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (9,'程序员',4000.00,10000.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (10,'市场经理',9000.00,15000.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (11,'市场代表',4000.00,9000.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (12,'公关代表',4500.00,10500.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (13,'采购职员',2500.00,5500.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (14,'采购经理',8000.00,15000.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (15,'销售经理',10000.00,20000.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (16,'销售代表',6000.00,12000.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (17,'运输职员',2500.00,5500.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (18,'库存职员',2000.00,5000.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (19,'库存管理',5500.00,8500.00);



/*Data for the table departments */

INSERT INTO departments(department_id,department_name,location_id) VALUES (1,'管理',1700);
INSERT INTO departments(department_id,department_name,location_id) VALUES (2,'市场营销',1800);
INSERT INTO departments(department_id,department_name,location_id) VALUES (3,'采购',1700);
INSERT INTO departments(department_id,department_name,location_id) VALUES (4,'人力资源',2400);
INSERT INTO departments(department_id,department_name,location_id) VALUES (5,'运输',1500);
INSERT INTO departments(department_id,department_name,location_id) VALUES (6,'IT',1400);
INSERT INTO departments(department_id,department_name,location_id) VALUES (7,'公共关系',2700);
INSERT INTO departments(department_id,department_name,location_id) VALUES (8,'销售',2500);
INSERT INTO departments(department_id,department_name,location_id) VALUES (9,'行政人员',1700);
INSERT INTO departments(department_id,department_name,location_id) VALUES (10,'财务',1700);
INSERT INTO departments(department_id,department_name,location_id) VALUES (11,'会计',1700);



/*Data for the table employees */
INSERT INTO `employees` VALUES ('100', 'Steven', 'Lee', 'steven.lee@yiibai.com', '0532-86011111', '1987-06-17', '4', '24000.00', null, '9');
INSERT INTO `employees` VALUES ('101', 'Neena', 'Wong', 'neena.wong@kaops.com', '0551-4243311', '1989-09-21', '5', '17000.00', '100', '9');
INSERT INTO `employees` VALUES ('102', 'Lex', 'Liang', 'lex.liang@kaops.com', '0571-87622362', '1993-01-13', '5', '17000.00', '100', '9');
INSERT INTO `employees` VALUES ('103', 'Alexander', 'Lee', 'alexander.lee@kaops.com', '020-95105105', '1990-01-03', '9', '9000.00', '102', '6');
INSERT INTO `employees` VALUES ('104', 'Bruce', 'Wong', 'bruce.wong@yiibai.com', '0371-68356666', '1991-05-21', '9', '6000.00', '103', '6');
INSERT INTO `employees` VALUES ('105', 'David', 'Liang', 'david.liang@kaops.com', '0512-67513131', '1997-06-25', '9', '4800.00', '103', '6');
INSERT INTO `employees` VALUES ('106', 'Valli', 'Chen', 'valli.chen@yiibai.com', '0535-95105175', '1998-02-05', '9', '4800.00', '103', '6');
INSERT INTO `employees` VALUES ('107', 'Diana', 'Chen', 'diana.chen@yiibai.com', '025-95105105', '1999-02-07', '9', '4200.00', '103', '6');
INSERT INTO `employees` VALUES ('108', 'Nancy', 'Chen', 'nancy.chen@yiibai.com', '0531-86012520', '1994-08-17', '7', '12000.00', '101', '10');
INSERT INTO `employees` VALUES ('109', 'Daniel', 'Chen', 'daniel.chen@yiibai.com', '021-8008207890', '1994-08-16', '6', '9000.00', '108', '10');
INSERT INTO `employees` VALUES ('110', 'John', 'Chen', 'john.chen@yiibai.com', '0592-2088888', '1997-09-28', '6', '8200.00', '108', '10');
INSERT INTO `employees` VALUES ('111', 'Ismael', 'Su', 'ismael.su@yiibai.com', '029-95105688', '1997-09-30', '6', '7700.00', '108', '10');
INSERT INTO `employees` VALUES ('112', 'Max', 'Su', 'max.su@yiibai.com', '021-95105105', '1998-03-07', '6', '7800.00', '108', '10');
INSERT INTO `employees` VALUES ('113', 'Min', 'Su', 'min.su@yiibai.com', '027-88068888', '1999-12-07', '6', '6900.00', '108', '10');
INSERT INTO `employees` VALUES ('114', 'Avg', 'Su', 'avg.su@yiibai.com', '0755-82328647', '1994-12-07', '14', '11000.00', '100', '3');
INSERT INTO `employees` VALUES ('115', 'Alexander', 'Su', 'alexander.su@yiibai.com', '0431-86122222', '1995-05-18', '13', '3100.00', '114', '3');
INSERT INTO `employees` VALUES ('116', 'Shelli', 'Zhang', 'shelli.zhang@kaops.com', '0771-2222222', '1997-12-24', '13', '2900.00', '114', '3');
INSERT INTO `employees` VALUES ('117', 'Sigal', 'Zhang', 'sigal.zhang@yiibai.com', '0791-6101074', '1997-07-24', '13', '2800.00', '114', '3');
INSERT INTO `employees` VALUES ('118', 'Guy', 'Zhang', 'guy.zhang@kaops.com', '0411-82603331', '1998-11-15', '13', '2600.00', '114', '3');
INSERT INTO `employees` VALUES ('119', 'Karen', 'Zhang', 'karen.zhang@yiibai.com', '010-51019999', '1999-08-10', '13', '2500.00', '114', '3');
INSERT INTO `employees` VALUES ('120', 'Matthew', 'Han', 'matthew.Han@yiibai.com', '0574-56163111', '1996-07-18', '19', '8000.00', '100', '5');
INSERT INTO `employees` VALUES ('121', 'Max', 'Han', 'Max.han@yiibai.com', '0731-2637122', '1997-04-10', '19', '8200.00', '100', '5');
INSERT INTO `employees` VALUES ('122', 'Min', 'Liu', 'Min.liu@yiibai.com', '023-63862607', '1995-05-01', '19', '7900.00', '100', '5');
INSERT INTO `employees` VALUES ('123', 'Shanta', 'Liu', 'shanta.liu@yiibai.com', '311-87600111', '1997-10-10', '19', '6500.00', '100', '5');
INSERT INTO `employees` VALUES ('126', 'Irene', 'Liu', 'irene.liu@kaops.com', '0752-95105688', '1998-09-28', '18', '2700.00', '120', '5');
INSERT INTO `employees` VALUES ('145', 'John', 'Liu', 'john.liu@yiibai.com', null, '1996-10-01', '15', '14000.00', '100', '8');
INSERT INTO `employees` VALUES ('146', 'Karen', 'Liu', 'karen.liu@yiibai.com', null, '1997-01-05', '15', '13500.00', '100', '8');
INSERT INTO `employees` VALUES ('176', 'Jonathon', 'Yang', 'jonathon.yang@yiibai.com', null, '1998-03-24', '16', '8600.00', '100', '8');
INSERT INTO `employees` VALUES ('177', 'Jack', 'Yang', 'jack.yang@yiibai.com', null, '1998-04-23', '16', '8400.00', '100', '8');
INSERT INTO `employees` VALUES ('178', 'Kimberely', 'Yang', 'kimberely.yang@yiibai.com', null, '1999-05-24', '16', '7000.00', '100', '8');
INSERT INTO `employees` VALUES ('179', 'Charles', 'Yang', 'charles.yang@yiibai.com', null, '2000-01-04', '16', '6200.00', '100', '8');
INSERT INTO `employees` VALUES ('192', 'Sarah', 'Yang', 'sarah.yang@kaops.com', '0351-2233611', '1996-02-04', '17', '4000.00', '123', '5');
INSERT INTO `employees` VALUES ('193', 'Britney', 'Zhao', 'britney.zhao@yiibai.com', '0351-2233611', '1997-03-03', '17', '3900.00', '123', '5');
INSERT INTO `employees` VALUES ('200', 'Jennifer', 'Zhao', 'jennifer.zhao@yiibai.com', '021-66050000', '1987-09-17', '3', '4400.00', '101', '1');
INSERT INTO `employees` VALUES ('201', 'Michael', 'Zhou', 'michael.zhou@yiibai.com', '010-67237328', '1996-02-17', '10', '13000.00', '100', '2');
INSERT INTO `employees` VALUES ('202', 'Pat', 'Zhou', 'pat.zhou@yiibai.com', '0755-28114518', '1997-08-17', '11', '6000.00', '201', '2');
INSERT INTO `employees` VALUES ('203', 'Susan', 'Zhou', 'susan.zhou@yiibai.com', '0755-83587526', '1994-06-07', '8', '6500.00', '101', '4');
INSERT INTO `employees` VALUES ('204', 'Hermann', 'Wu', 'hermann.wu@yiibai.com', '0513-83512816', '1994-06-07', '12', '10000.00', '101', '7');
INSERT INTO `employees` VALUES ('205', 'Shelley', 'Wu', 'shelley.wu@yiibai.com', '0898-31686222', '1994-06-07', '2', '12000.00', '101', '11');
INSERT INTO `employees` VALUES ('206', 'William', 'Wu', 'william.wu@yiibai.com', '022-26144822', '1994-06-07', '1', '8300.00', '205', '11');



/*Data for the table dependents */

INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (1,'Penelope','Wu','Child',206);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (2,'Nick','Wu','Child',205);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (3,'Ed','Zhao','Child',200);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (4,'Jennifer','Lee','Child',100);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (5,'Johnny','Wong','Child',101);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (6,'Bette','Liang','Child',102);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (7,'Grace','Chen','Child',109);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (8,'Matthew','Chen','Child',110);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (9,'Joe','Su','Child',111);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (10,'Christian','Su','Child',112);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (11,'Zero','Su','Child',113);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (12,'Karl','Chen','Child',108);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (13,'Uma','Zhou','Child',203);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (14,'Vivien','Lee','Child',103);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (15,'Cuba','Wong','Child',104);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (16,'Fred','Liang','Child',105);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (17,'Helen','Chen','Child',106);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (18,'Dan','Chen','Child',107);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (19,'Bob','Zhou','Child',201);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (20,'Lucille','Zhou','Child',202);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (21,'Kirsten','Wu','Child',204);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (22,'Elvis','Su','Child',115);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (23,'Sandra','Zhang','Child',116);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (24,'Cameron','Zhang','Child',117);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (25,'Kevin','Zhang','Child',118);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (26,'Rip','Zhang','Child',119);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (27,'Julia','Su','Child',114);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (28,'Woody','Liu','Child',145);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (29,'Alec','Liu','Child',146);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (30,'Sandra','Yang','Child',176);

删除表。以下是用于刷新示例数据库时，删除所有表的脚本。

DROP TABLE employees;
DROP TABLE dependents;
DROP TABLE departments;
DROP TABLE locations;
DROP TABLE countries;
DROP TABLE regions;
DROP TABLE jobs;

SQL 数据库操作

"创建、删除、查看、备份" 数据库

mysql> show databases; // 显示用户拥有权限的数据库
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sqldemo |
| sys |
+--------------------+
7 rows in set
mysql> USE sqldemo; // 选择数据库
mysql> CREATE DATABASE testdb; // 创建数据库
mysql> DROP DATABASE database_name; // 删除数据库
mysql> RENAME DATABASE old_db_name TO new_db_name; // 重命名数据库

SQL 表操作

"创建、删除、查看" 表

创建新表所需的最低信息是表名和列名。table_name 指定的表名在数据库中必须是唯一的。如果创建的表的名称与已存在的表相同，则数据库系统将发出错误。在CREATE TABLE语句中，指定以逗号分隔的列定义列表。每个列定义由列名，列的数据类型，默认值和一个或多个列约束组成。列的数据类型指定列可以存储的数据类型。列的数据类型可以是数字，字符，日期等。列约束控制可以存储在列中的值的类型。例如，NOT NULL约束确保列不包含NULL值。
列可能有多个列约束。例如，users表的username列可以同时具有NOT NULL和UNIQUE约束。如果约束包含多个列，则使用表约束。例如，如果表的主键包含两列，则在这种情况下，必须使用PRIMARY KEY表约束。

CREATE TABLE table_name(
column_name_1 data_type default value column_constraint,
column_name_2 data_type default value column_constraint,
...,
table_constraint
);

create temporary table zengchao(name varchar(10)); 创建临时表：(建立临时表zengchao)
create table if not exists students(……); 创建表时先判断表是否存在
create table table2 select * from table1 where 1<>1; 从已经有的表中复制表的结构
create table table2 select * from table1; 复制表
alter table table1 rename as table2; 对表重新命名

根据已有的表创建新表：
A：create table tab_new like tab_old --(使用旧表创建新表)
B：create table tab_new as select col1,col2… from tab_old definition only

示例：

CREATE TABLE courses (
course_id INT AUTO_INCREMENT PRIMARY KEY,
course_name VARCHAR(50) NOT NULL
);

CREATE TABLE trainings (
employee_id INT,
course_id INT,
taken_date DATE,
PRIMARY KEY (employee_id , course_id)
);

drop table MYTABLE; 删除表

DROP TABLE table_name1,table_name2,...; // 删除多个表

// 要删除表中的所有数据，可以使用 DELETE 或 TRUNCATE TABLE 语句。

// 要删除由另一个表的外键约束引用的表，必须在删除表之前禁用或删除外部约束。

delete from MYTABLE; 清空表

show tables; 列出数据库中的所有表

describe tablename; 表的详细描述。显示表结构. 等价 show columns from tableName;

desc mytestdb; 显示表 mytestdb 的结构

要删除表中的所有数据，可使用不带WHERE子句的 DELETE 语句。但是对于具有几百万行的大表，DELETE 语句很慢且效率不高。要快速删除大表中的所有行，使用 TRUNCATE TABLE 语句：TRUNCATE TABLE table_name; 在此语法中，指定要在TRUNCATE TABLE子句后删除数据的 table_name。某些数据库系统(如MySQL和PostgreSQL)允许直接省略TABLE关键字，
因此 TRUNCATE TABLE 语句为：TRUNCATE table_name;
一次截断多个表：TRUNCATE TABLE table_name1, table_name2, ...;

更改表名

有时，表的名称没有意义或因为一些其它原因需要更改。

用法：
ALTER TABLE table_name
RENAME TO new_table_name;
或者 RENAME old_table _name To new_table_name;
示例：
ALTER TABLE Students
RENAME TO Student_bak;
或者：RENAME Students TO Student_bak;

更改表结构 ( 添加、修改、删除列 )

ALTER TABLE 语句用于对现有表执行以下操作：

使用 ADD 子句添加新列。如果省略AFTER子句，则将在表的最后一列之后添加新列。
使用 MODIFY 子句修改列的属性，例如：约束，默认值等。
使用 DROP 子句删除列。

添加列

ALTER TABLE table_name ADD [COLUMN] column_definition;
列定义的典型语法如下：column_name data_type constraint;

列增加后将不能删除。列的数据类型也不能改变，唯一能改变的是增加varchar类型的长度。

格式：
ALTER TABLE table_name
ADD new_colum data_type column_constraint [AFTER existing_column];

在MySQL中的表中添加一列：
ALTER TABLE table_name ADD [COLUMN] column_definition;
在MySQL中向表中添加多列：
ALTER TABLE table_name
ADD [COLUMN] column_definition,
ADD [COLUMN] column_definition,
...
ADD [COLUMN] column_definition;

示例：

// 添加一列
ALTER TABLE courses ADD credit_hours INT NOT NULL;
// 添加多列
ALTER TABLE courses
ADD fee NUMERIC (10, 2) AFTER course_name,
ADD max_limit INT AFTER course_name;

修改列的属性

MODIFY子句用于更改现有列的某些属性，例如，NOT NULL，UNIQUE和数据类型。注意，应该修改没有数据的表的列的属性。因为更改已包含数据的表中列的属性可能会导致永久性数据丢失。

示例：
ALTER TABLE courses
MODIFY fee NUMERIC (10, 2) NOT NULL;

修改列的类型
alter table table1 modify id int unsigned; //修改列id的类型为int unsigned
alter table table1 change id sid int unsigned; //修改列id的名字为sid，而且把属性修改为int unsigned

删除列

当表的列已过时且未被任何其他数据库对象(如触发器，视图，存储过程和存储过程)使用时，需要将其从表中删除。

用法：
ALTER TABLE table_name
DROP COLUMN column_name1,
[DROP COLUMN column_name2];
示例：
ALTER TABLE courses DROP COLUMN fee;

ALTER TABLE courses
DROP COLUMN max_limit,
DROP COLUMN credit_hours;

复制表

如果要将SQL表复制到同一数据库中的另一个表中，可以使用select语句。

用法：SELECT * INTO <destination_table> FROM <source_table>
示例：
将hr_employee表的记录复制到employee表中。
SELECT * INTO employee FROM hr_employee;

SqlServer:
select * into table_new from table_old ; 复制结构和数据
select * into table_new from table_old where 1=2; 只复制结构
select * into table_new from table_old where 1 <> 1
select top 0 * into table_new from table_old
//复制了表student 的表结构到 studets中了
select top 0 * into studets from student
Oracle:
create table table_new as select * from table_old; 复制结构和数据
create table table_new as select * from table_old where 1=0; 只复制结构
create table table_new as select * from table_old where 1 <> 1
create table table_new as select * from table_old where 1=2
MySql：
----- 复制表结构及数据到新表
CREATE TABLE 新表 SELECT * FROM 旧表
----- 只复制表结构到新表
CREATE TABLE 新表 SELECT * FROM 旧表 WHERE 1=2
DB2：
create table table_name_new as (select * from table_name_old) definition only;   --复制表结构
insert into table_name_new （select * from table_name_old);     --插入数据

(只复制结构,源表名：a 新表名：b) (Access可用)
方法一：select * into b from a where 1<>1（仅用于SQlServer）
方法二：select top 0 * into b from a

(拷贝数据,源表名：a 目标表名：b) (Access可用)：insert into b(a, b, c) select d,e,f from b;

跨数据库之间表的拷贝

(具体数据使用绝对路径) (Access可用)
insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件
例子：..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where..

临时表

临时表只在当前会话可见，当会话结束时，临时表会自动被删除。但也可以使用 DROP TEMPORARY TABLE 语句手动删除临时表。如果语句很复杂，连接太多，可以考虑用临时表和表变量分步完成。

如果需要多次用到一个大表的同一部分数据，考虑用临时表和表变量暂存这部分数据。
如果需要综合多个表的数据，形成一个结果，可以考虑用临时表和表变量分步汇总这多个表的数据。其他情况下，应该控制临时表和表变量的使用。

临时表可以在运行时创建，并且可以像普通表一样执行各种操作。这些临时表是在tempdb数据库中创建的。根据行为和范围，有两种类型的临时表。

使用 CREATE TEMPORARY TABLE 语句创建临时表。

局部临时变量
全局临时变量

局部临时表：仅在当前连接时可用。当用户与实例断开连接时，它会自动删除。它以哈希(#) 符号开头。
CREATE TABLE #local temp table (
User_id int,
User_name varchar (50),
User_address varchar (150)
)

全局临时表：名称以双哈希(##)开头。创建此表后，它就像一个永久表。它始终为所有用户准备好，并且在撤消总连接之前不会被删除。

CREATE TABLE ##new global temp table (
User_id int,
User_name varchar (50),
User_address varchar (150)
)

SQL 约束

主键约束

Alter table tabname add primary key(col)
说明：删除主键： Alter table tabname drop primary key(col)

表由列和行组成。通常，表具有一列或多列，列的值唯一地标识表中的每一行。此列或多列称为主键。

每个表都有一个且只有一个主键。主键不接受NULL或重复值。
两列或更多列组成的主键也称为复合主键。如果主键由两列或更多列组成，则值可能在一列中重复，但主键中所有列的值组合必须是唯一的。

创建项目(projects)表：
CREATE TABLE projects (
project_id INT PRIMARY KEY,
project_name VARCHAR(255),
start_date DATE NOT NULL,
end_date DATE NOT NULL
);
列定义中添加PRIMARY KEY，以使project_id列成为projects表的主键。
以下语句等效于上述语句，但它不使用PRIMARY KEY约束作为列约束，而是使用表约束。
CREATE TABLE projects (
project_id INT,
project_name VARCHAR(255),
start_date DATE NOT NULL,
end_date DATE NOT NULL,
CONSTRAINT pk_id PRIMARY KEY (project_id)
);
使用CREATE TABLE语句末尾的CONSTRAINT子句将 project_id 列指定为主键。
要存储表示分配给哪个项目的项目分配，需要使用以下语句创建project_assignments表：
CREATE TABLE project_assignments (
project_id INT,
employee_id INT,
join_date DATE NOT NULL,
CONSTRAINT pk_assgn PRIMARY KEY (project_id , employee_id)
);
由于主键由两列组成：project_id和employee_id，因此必须使用PRIMARY KEY作为表约束。

使用 CREATE TABLE 创建没有主键的表不是好习惯。
使用 ALTER TABLE 语句将主键添加到表中。
示例：创建没有主键的project_milestones表。 project_milesones存储项目的进度。
CREATE TABLE project_milestones(
milestone_id INT,
project_id INT,
milestone_name VARCHAR(100)
);
现在，可以使用以下ALTER TABLE语句将milestone_id列设置为主键。
ALTER TABLE project_milestones
ADD CONSTRAINT pk_milestone_id PRIMARY KEY (milestone_id);
也可以跳过CONSTRAINT子句：
ALTER TABLE project_milestones
ADD PRIMARY KEY (milestone_id);
删除主键约束一般很少删除表的主键。但是，如果必须这样做，可以使用ALTER TABLE语句。
ALTER TABLE table_name
DROP CONSTRAINT primary_key_constraint;
如果使用的是MySQL，则删除主键的语法更简单，如下所示：
ALTER TABLE table_name DROP PRIMARY KEY;
例如，要删除 project_milestones 表的主键约束，请使用以下语句。
ALTER TABLE project_milestones DROP CONSTRAINT pk_milestone_id;

索引

create [unique] index idxname on tabname(col….)
删除索引：drop index idxname
注：索引是不可更改的，想更改必须删除重新建。

创建索引
alter table table1 add index ind_id (id);
create index ind_id on table1 (id);
create unique index ind_id on table1 (id); //建立唯一性索引

删除索引
drop index idx_id on table1;
alter table table1 drop index ind_id;

视图

create view view_name as select statement
删除视图：drop view view_name

外键约束

第一个表的主键列(或多个列)由第二个表的列(或列)引用。第二个表的列(或多列)成为成为第一个表的外键。

在创建或更改表时，可以使用FOREIGN KEY约束创建外键。

示例：
CREATE TABLE projects (
project_id INT AUTO_INCREMENT PRIMARY KEY,
project_name VARCHAR(255),
start_date DATE NOT NULL,
end_date DATE NOT NULL
);

CREATE TABLE project_milestones (
milestone_id INT AUTO_INCREMENT PRIMARY KEY,
project_id INT,
milestone_name VARCHAR(100),
FOREIGN KEY (project_id)
REFERENCES projects (project_id)
);
FOREIGN KEY子句将project_milestones表的project_id设置为引用project表的project_id列的外键。
可以为FOREIGN KEY约束指定名称，如下所示：
CREATE TABLE project_milestones (
milestone_id INT AUTO_INCREMENT PRIMARY KEY,
project_id INT,
milestone_name VARCHAR(100),
CONSTRAINT fk_project FOREIGN KEY (project_id)
REFERENCES projects (project_id)
);
可以使用 ALTER TABLE语句将FOREIGN KEY约束添加到现有表。
ALTER TABLE table_1
ADD CONSTRAINT fk_name FOREIGN KEY (fk_key_column)
REFERENCES table_2(pk_key_column)

ALTER TABLE project_milestones
ADD CONSTRAINT fk_project FOREIGN KEY(project_id)
REFERENCES projects(project_id);
删除外键约束，还要使用ALTER TABLE语句
ALTER TABLE table_name
DROP CONSTRAINT fk_name;
如果使用的是MySQL，则可以使用更清晰的语法，如下所示：
ALTER TABLE table_name
DROP FOREIGN KEY fk_name;
例如，要删除fk_project外键约束，请使用以下语句：
ALTER TABLE project_milestones
DROP CONSTRAINT fk_project;

唯一约束

有时，希望确保一列或多列中的值不重复。例如，employees表中不能接受的重复电子邮件。由于电子邮件列不是主键的一部分，因此防止电子邮件列中重复值的唯一方法是使用UNIQUE约束。

PRIMARY KEY约束最多只能有一个，而表中可以有多个UNIQUE约束。如果表中有多个UNIQUE约束，则所有UNIQUE约束必须在不同的列集。

与 PRIMARY KEY 约束不同，UNIQUE约束允许NULL值。这取决于RDBMS要考虑NULL值是否唯一。例如，MySQL将NULL值视为不同的值，因此，可以在参与UNIQUE约束的列中存储多个NULL值。但是，Microsoft SQL Server或Oracle数据库不是这种情况。

在创建表时创建UNIQUE约束。以下CREATE TABLE语句定义users表，其中username列是唯一的。
CREATE TABLE users (
user_id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(255) NOT NULL UNIQUE,
password VARCHAR(255) NOT NULL
);
要为列创建UNIQUE约束，需要在列定义中添加UNIQUE关键字。在这个示例中，创建了UNIQUE约束作为列约束。
如果插入或更新与username列中已存在的值相同的值，则RDBMS将拒绝更改并返回错误。以下语句等效于使用表约束语法创建的UNIQUE约束的上述语句。
CREATE TABLE users (
user_id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(255) NOT NULL,
password VARCHAR(255) NOT NULL,
CONSTRAINT uc_username UNIQUE (username)
);
在这个示例中，将CONSTRAINT子句放在CREATE TABLE语句的末尾。
将UNIQUE约束添加到现有表如果表已存在，则可以为列添加UNIQUE约束，前提条件是参与UNIQUE约束的列或列组合必须包含唯一值。
假设创建的users表没有为username列定义UNIQUE约束。要将UNIQUE约束添加到username列，请使用ALTER TABLE语句，如下所示：
ALTER TABLE users
ADD CONSTRAINT uc_username UNIQUE(username);
如果要添加新列并为创建UNIQUE约束，请使用以下形式的ALTER TABLE语句。
ALTER TABLE users
ADD new_column data_type UNIQUE;
例如，以下语句将带有UNIQUE约束的email列添加到users表。
ALTER TABLE users
ADD email VARCHAR(255) UNIQUE;
删除UNIQUE约束要删除UNIQUE约束，请使用ALTER TABLE语句，如下所示：
ALTER TABLE table_name
DROP CONSTRAINT unique_constraint_name;
例如，要删除users表中的uc_username唯一约束，请使用以下语句。
ALTER TABLE users
DROP CONSTRAINT uc_username;

Not Null 约束

NOT NULL约束是一个列约束，它定义将列限制为仅具有非NULL值的规则。这意味着当使用INSERT语句向表中插入新行时，必须指定NOT NULL列的值。

CREATE TABLE table_name(
...
column_name data_type NOT NULL,
...
);

逻辑上，NOT NULL约束等同于CHECK约束，因此，上述语句等效于以下语句。
CREATE TABLE table_name (
...
column_name data_type,
...
CHECK (column_name IS NOT NULL)
);

CREATE TABLE training (
employee_id INT,
course_id INT,
taken_date DATE NOT NULL,
PRIMARY KEY (employee_id , course_id)
);

ALTER TABLE table_name MODIFY column_name data_type NOT NULL;

ALTER TABLE training MODIFY taken_date date NOT NULL;

检查约束

CHECK 约束是SQL中的完整性约束，它允许指定列或列集中的值必须满足布尔表达式。
可以在单个列或整个表上定义CHECK约束。

如果在单个列上定义CHECK约束，则CHECK约束仅检查此列的值。
如果在表上定义CHECK约束，则会根据同一行的其他列中的值限制列中的值。

CHECK约束由关键字CHECK后跟括号中的布尔表达式组成：CHECK(Boolean_expression)
如果要为CHECK约束指定名称：CONSTRAINT constraint_name CHECK(Boolean_expression)
值得注意的是，当布尔表达式返回true或NULL值时，视为满足CHECK约束。如果其中一个操作数为NULL，则布尔表达式求值为NULL，它们不会阻止约束列存储NULL值。若要确保该列不包含NULL值，请使用NOT NULL约束。

创建CHECK约束的例子。
要创建一个products表，其products_price列中的值必须为正数，请使用以下CREATE TABLE语句：
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(255) NOT NULL,
selling_price NUMERIC(10,2) CHECK (selling_price > 0)
);
CHECK约束位于列的数据类型之后。如果使用负值插入或更新售价，则表达sell_price> = 0将返回false，并且RDMBS将返回错误。
可以为CHECK约束指定单独的名称。约束名称可帮助明确RDBMS返回的错误消息，并确切地知道该值违反了哪个约束。
要为约束指定名称，请使用CONSTRAINT关键字，后跟约束的名称。
例如，以下语句将positive_selling_price指定为sell_price列上的CHECK约束的名称。
分配CHECK约束名称的语法如下：
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(255) NOT NULL,
selling_price NUMERIC(10,2) CONSTRAINT positive_selling_price CHECK (selling_price > 0)
);
可以定义引用多个列的CHECK约束。假设在product表中存储了销售价格和成本，并且希望确保成本始终低于销售价格。
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR (255) NOT NULL,
selling_price NUMERIC (10, 2) CHECK (selling_price > 0),
cost NUMERIC (10, 2) CHECK (cost > 0),
CHECK (selling_price > cost)
);
首先，有两个与sell_price和cost列相关联的CHECK约束，以确保每列中的值为正。其次，有另一个未附加到任何列的CHECK约束，而是显示为CREATE TABLE语句中的最后一个子句。
前两个约束是列约束，而第三个约束是表约束。表约束不与任何列关联。使用以下语法为表约束指定名称。
CREATE TABLE table_name (
…,
CONSTRAINT check_constraint_name CHECK (Boolean_expression)
);
例如，以下语句为上面的CHECK约束指定了一个名称。
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR (255) NOT NULL,
selling_price NUMERIC (10, 2) CHECK (selling_price > 0),
cost NUMERIC (10, 2) CHECK (cost > 0),
CONSTRAINT valid_selling_price CHECK (selling_price > cost)
);

数据操作 ( 增、删、改、查 )

增加

INSERT语句用于：

向表中插入一行
向表中插入多行
将行从一个表复制到另一个表中。

如果值序列与表中列的顺序匹配，则无需指定列：INSERT INTO table1 VALUES (value1, value2,...); 但是，这不是一个好的做法。

推荐用法：INSERT INTO (column1, column3) VALUES (column1, column3);

示例：
//插入一列
INSERT INTO dependents (
first_name,
last_name,
relationship,
employee_id
)VALUES('Max','Su','Child',176);
//插入多列
INSERT INTO dependents (
first_name,
last_name,
relationship,
employee_id
)
VALUES
('Avg','Lee','Child',192),
('Michelle','Lee','Child',192);

// 插入多行

INSERT INTO student (ID, NAME)
SELECT 1, 'ARMAAN'
UNION ALL
SELECT 2, 'BILLY'
UNION ALL
SELECT 3, 'CHARLIE';

// 查询来自一个或多个表的数据，并将其插入另一个表中
INSERT INTO table1 (column1, column2)
SELECT
column1,
column2
FROM
table2
WHERE
condition1;

// 如果表结构相同，可以省略列
INSERT INTO dependents_archive
SELECT
*
FROM
dependents;

删除

要从表中删除一行或多行，请使用DELETE语句。用法：
  DELETE
  FROM
    table_name
  WHERE
      condition;

首先，提供要删除行的表名称(table_name)。
其次，在WHERE子句中指定条件以标识需要删除的行记录。如果省略WHERE子句，则将删除表中的所有行记录。因此，应始终谨慎使用DELETE语句。

示例：
DELETE FROM dependents WHERE dependent_id = 16;
DELETE FROM dependents WHERE employee_id IN (100 , 101, 102);

大多数数据库系统都支持外键约束，因此当删除表中的一行时，外键表中的行也会自动删除。

修改

UPDATE 可以改表中现有数据

语法：
UPDATE table_name
SET column1 = value1,
column2 = value2
WHERE
condition;

在 UPDATE 子句中指明要更新的表。
在 SET 子句中指定要修改的列。 SET 子句中未列出的列的值不会被修改。
指定 WHERE 子句中要更新的行。

UPDATE 语句根据WHERE子句中的条件影响表中的一行或多行。
例如，如果 WHERE 子句包含主键表达式，则 UPDATE 语句仅更改一行。
但是，将修改 WHERE 条件改为 true 时，将会影响所有行。
因为WHERE子句是可选的，所以，如果省略它，表中的所有行都将受到影响。

示例：
// 更新一列
UPDATE employees SET last_name = 'Zhang' WHERE employee_id = 192;
// 更新多列
SELECT
employee_id,
first_name,
last_name,salary
FROM
employees
WHERE
salary<3000;

// 带有子查询示例的SQL UPDATE。省略了WHERE，更新了dependents表中的所有行
UPDATE dependents
SET last_name = (
SELECT
last_name
FROM
employees
WHERE
employee_id = dependents.employee_id
);

UPDATE JOIN 可使用一个表和连接条件来更新另一个表。

UPDATE 和 JOIN 语句的查询语法
UPDATE customer_table
INNER JOIN
Customer_table
ON customer_table.rel_cust_name = customer_table.cust_id
SET customer_table.rel_cust_name = customer_table.cust_name

在table2中有两行：column1的值是21和31，假设想要将table1中的值更新为table2中column1为21和31行的值。
仅更新column2和column3的值。
最简单和最常用的方法是在update语句中使用join子句并在update语句中使用多个表。

UPDATE table1 t1
LEFT JOIN table2 t2
ON t1.column1 = t2.column1
SET t1.column2 = t2.column2,
t1.column3 = t2.column3
where t1.column1 in(21,31);

更新日期数据

用法：
UPDATE table
SET Column_Name = 'YYYY-MM-DD HH:MM:SS'
WHERE Id = value

示例：注意：SQL自动附加默认的00:00:00.000
UPDATE table
SET EndDate = '2019-03-16 00:00:00.000'
WHERE Id = 1

查找

SELECT column1, column2, column3 FROM table_name;
SELECT * FROM table_name;

除了SELECT和FROM子句之外，SELECT语句还可以包含许多其他子句

WHERE - 用于根据指定条件过滤数据
ORDER BY - 用于对结果集进行排序
LIMIT - 用于限制返回的行
JOIN - 用于查询来自多个相关表的数据
GROUP BY - 用于根据一列或多列对数据进行分组
HAVING - 用于过滤分组

MySQL中 select 命令类似于其他编程语言里的 print 或者 write，可以用它来显示一个字符串、数字、数学表达式的结果等等

select version(), current_date; 显示当前 mysql 版本和当前日期
select now(); 查询时间
select user(); 查询当前用户
select version(); 查询数据库版本
select database(); 查询当前使用的数据库

select dayofmonth(current_date); 显示年月日
select month(current_date);
select year(current_date);

SELECT "welecome to my blog!"; 显示字符串
select ((4 * 4) / 10 ) + 25; 当计算器用

Order By 排序

SELECT
column1, column2
FROM
table_name
ORDER BY column1 ASC ,
column2 DESC;

在此语法中，ORDER BY子句放在FROM子句之后。如果SELECT语句包含WHERE子句，则ORDER BY子句必须放在WHERE子句之后。

要对结果集进行排序，请指定要排序的列以及排序顺序的类型：

升序(使用：ASC表示)
降序(使用：DESC表示)

如果未指定排序顺序，则数据库系统通常默认按升序(ASC)对结果集进行排序。

当在ORDER BY子句中包含多个列时，数据库系统首先根据第一列对结果集进行排序，然后根据第二列对排序的结果集进行排序，依此类推。

Group By 分组

一张表，一旦分组完成后，查询后只能得到组相关的信息。

GROUP BY子句是SELECT语句的可选子句，它根据指定列中的匹配值将行组合成组，每组返回一行。如果使用GROUP BY子句而不使用聚合函数，则GROUP BY子句的行为类似于DISTINCT运算符。经常将GROUP BY与MIN，MAX，AVG，SUM或COUNT等聚合函数结合使用，以计算为每个分组提供信息的度量。
GROUP BY 语法：
SELECT
column1,
column2,
AGGREGATE_FUNCTION (column3)
FROM
table1
GROUP BY
column1,
column2;
在SELECT子句中包含聚合函数不是强制性的。但是，如果使用聚合函数，它将计算每个组的汇总值。
如果要在分组之前过滤行，请添加WHERE子句。但是要过滤组，请使用HAVING子句。需要强调的是，在对行进行分组之前应用WHERE子句，而在对行进行分组之后应用HAVING子句。换句话说，WHERE子句应用于行，而HAVING子句应用于分组。
要对组进行排序，请在GROUP BY子句后添加ORDER BY子句。
GROUP BY子句中出现的列称为分组列。如果分组列包含NULL值，则所有NULL值都汇总到一个分组中，因为GROUP BY子句认为NULL值相等。

示例：
SELECT
department_id,
COUNT(employee_id) headcount
FROM
employees
GROUP BY
department_id;
示例：
SELECT
e.department_id,
department_name,
COUNT(employee_id) headcount
FROM
employees e
INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
e.department_id;
示例：
SELECT
e.department_id,
department_name,
COUNT(employee_id) headcount
FROM
employees e
INNER JOIN
departments d ON d.department_id = e.department_id
GROUP BY e.department_id
ORDER BY headcount DESC;
示例：
SELECT
e.department_id,
department_name,
COUNT(employee_id) headcount
FROM
employees e
INNER JOIN
departments d ON d.department_id = e.department_id
GROUP BY e.department_id
HAVING headcount > 5
ORDER BY headcount DESC;
示例：
SELECT
e.department_id,
department_name,
MIN(salary) min_salary,
MAX(salary) max_salary,
ROUND(AVG(salary), 2) average_salary
FROM
employees e
INNER JOIN
departments d ON d.department_id = e.department_id
GROUP BY e.department_id;
示例：
SELECT
e.department_id,
department_name,
SUM(salary) total_salary
FROM
employees e
INNER JOIN
departments d ON d.department_id = e.department_id
GROUP BY e.department_id;

示例：GROUP BY多列

Having 分组条件

使用 HAVING 指定分组的条件，HAVING子句紧跟在GROUP BY子句之后出现。HAVING子句通常与SELECT语句中的GROUP BY子句一起使用。如果使用带GROUP BY子句的HAVING子句，HAVING子句的行为类似于WHERE子句。

HAVING 与 WHERE

在通过GROUP BY子句将行汇总到分组之前，WHERE子句将条件应用于各个行。但是，HAVING子句在将行分组到组之后将条件应用于组。因此，需要注意的是，在GROUP BY子句之前应用WHERE子句，之后应用 HAVING子句。

HAVING 语法：
SELECT
column1,
column2,
AGGREGATE_FUNCTION (column3)
FROM
table1
GROUP BY
column1,
column2
HAVING
group_condition;

示例：
SELECT
manager_id,
first_name,
last_name,
COUNT(employee_id) direct_reports
FROM
employees
WHERE
manager_id IS NOT NULL
GROUP BY manager_id
HAVING direct_reports >= 5;
示例：
SELECT
department_id, SUM(salary)
FROM
employees
GROUP BY department_id
HAVING SUM(salary) BETWEEN 20000 AND 30000
ORDER BY SUM(salary);
示例：
SELECT
e.department_id,
department_name,
MIN(salary)
FROM
employees e
INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
e.department_id
HAVING
MIN(salary) >= 10000
ORDER BY
MIN(salary);
示例：
SELECT
e.department_id,
department_name,
ROUND(AVG(salary), 2)
FROM
employees e
INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
e.department_id
HAVING
AVG(salary) BETWEEN 5000
AND 7000
ORDER BY
AVG(salary);

DISTINCT 删除重复项

从结果集中删除重复数据项。用法：
SELECT DISTINCT
column1, column2, ...
FROM
table1;

如果在DISTINCT运算符后使用一列，则数据库系统使用该列来计算重复。如果使用两列或更多列，数据库系统将使用这些列的组合进行重复检查。
要删除重复项，数据库系统首先按SELECT子句中指定的每个列对结果集进行排序。然后，它从上到下扫描表，以识别彼此相邻的重复项。如果结果集很大，则排序和扫描操作可能会降低查询的性能。

示例：
SELECT DISTINCT
job_id,
salary
FROM
employees
ORDER BY
job_id,
salary DESC;

NULL值在SQL中是一个特别的值。它在某些情况下用作标记，比如：缺少信息或信息不适用。因此，NULL无法与任何值进行比较。即使NULL也不等于它自己。如果列中有两个或多个NULL值，数据库系统是否将它们视为相同或不同的值？
通常，DISTINCT运算符将所有NULL值视为相同的值。因此在结果集中，DISTINCT运算符只保留一个NULL值，并从结果集中删除其它的NULL值。

LIMIT 和 OFFSET

limit(选出10到20条)<第一个记录集的编号是0>
select * from students order by id limit 9,10;

使用 LIMIT 和 OFFSET 子句，可以查询返回结果的一部分。用法：
SELECT
column_list
FROM
table1
ORDER BY column_list
LIMIT row_count OFFSET offset;

row_count 确定将返回的行数。
OFFSET 子句在开始返回行之前跳过偏移行。 OFFSET 子句是可选的。如果同时使用LIMIT 和 OFFSET 子句，OFFSET 会在 LIMIT 约束行数之前先跳过偏移行。

示例：
SELECT
employee_id, first_name, last_name
FROM
employees
ORDER BY first_name
LIMIT 5 OFFSET 3;

LIMIT OFFSET子句的较短形式。
SELECT
employee_id, first_name, last_name
FROM
employees
ORDER BY first_name
LIMIT 3 , 5;

SELECT
employee_id, first_name, last_name, salary
FROM
employees
WHERE
salary = (SELECT DISTINCT
salary
FROM
employees
ORDER BY salary DESC
LIMIT 1 , 1);

Where 子句

WHERE 子句的语法：
SELECT
column1, column2, ...
FROM
table
WHERE
condition;
WHERE 子句紧跟在 FROM 子句之后出现。 WHERE 子句包含一个或多个逻辑表达式，用于计算表中的每一行。如果条件计算为 true，则满足条件的行记录将包含在结果集中; 否则，它将被排除在外。
请注意，SQL 具有三值逻辑，即 TRUE，FALSE 和 UNKNOWN。这意味着如果某行导致条件计算为 FALSE 或 NULL，则不会返回该行。
请注意，WHERE 子句后面的逻辑表达式也称为谓词。可以使用各种运算符来形成 WHERE子句中使用的行选择条件。

如果想查询1999年加入公司的员工，可以通过以下几种方式实现：

使用 YEAR函数从 hire_date 列获取年份数据，并使用等于(=)运算符来形成表达式。
使用AND运算符使用两个表达式。
使用BETWEEN运算符。

WHERE salary > 14000
WHERE department_id = 5
WHERE last_name = 'Zhang';
WHERE hire_date >= '1999-01-01'
WHERE YEAR (hire_date) = 1999

AND 运算符、短路计算

AND 运算符是一个逻辑运算符，它在SELECT，UPDATE或DELETE语句的WHERE子句中组合了两个布尔表达式。

SELECT
first_name, last_name, job_id, salary
FROM
employees
WHERE
job_id = 9
AND salary > 5000
and YEAR (hire_date) >=1997
AND YEAR (hire_date) <= 1998;

短路计算：短路功能允许数据库系统在确定结果后立即停止评估逻辑表达式的其余部分。

1 = 0 AND 1 = 1; 数据库系统首先处理两个比较，并使用AND运算符来评估两个结果。
但是，使用短路评估功能，数据库系统只需要评估表达式的左侧部分，因为左侧部分(1 = 0)返回false，导致整个条件返回false，而不管右侧部分条件的结果如何。因此，短路功能可以减少CPU计算时间，并且在某些情况下有助于防止运行时错误。
1 = 0 AND 1/0; 如果数据库系统支持短路功能，则不会评估导致除零错误的表达式(1/0)的右侧部分。

OR 运算符

OR运算符通常用于在SELECT，UPDATE或DELETE语句的WHERE子句中，以形成灵活的条件。如果任一表达式为true，则 OR 运算符始终返回true。

示例：
SELECT
first_name, last_name, hire_date
FROM
employees
WHERE
YEAR (hire_date) = 1997 OR YEAR (hire_date) = 1998
ORDER BY
first_name, last_name;

示例：
SELECT
first_name, last_name, hire_date, department_id
FROM
employees
WHERE
department_id = 3
AND (
YEAR (hire_date) = 1997
OR YEAR (hire_date) = 1998
)
ORDER BY
first_name, last_name;

示例：
SELECT
first_name, last_name, hire_date, department_id
FROM
employees
WHERE
department_id = 3
AND YEAR (hire_date) = 1997
OR YEAR (hire_date) = 1998
ORDER BY
first_name, last_name;

示例：
SELECT
first_name,
last_name,
hire_date
FROM
employees
WHERE
YEAR (hire_date) IN (1990, 1999, 2000)
ORDER BY
hire_date;

BETWEEN 运算符

逻辑运算符。它返回true，false或unknown值。 BETWEEN 运算符用于SELECT，UPDATE或DELETE语句中以查找范围内的值。

BETWEEN运算符的语法：expression BETWEEN low AND high;

表达式是在低和高定义的范围内测试的表达式。
low和high可以是表达式或文字值，要求low的值小于high的值。

expression >= low and expression <= high
expression NOT BETWEEN low AND high
expression < low OR expression > high

示例：
SELECT
employee_id, first_name, last_name, salary
FROM
employees
WHERE
salary NOT BETWEEN 2500 AND 10000
ORDER BY salary;
示例：
SELECT
employee_id, first_name, last_name, hire_date
FROM
employees
WHERE
hire_date BETWEEN '1999-01-01' AND '2000-12-31'
ORDER BY hire_date;

In 运算符

IN 运算符是一个逻辑运算符，用于将值与一组值进行比较。如果值在值集内，则IN运算符返回true。否则，它返回 false 或 unknown。

示例：
SELECT
employee_id, first_name, last_name, job_id
FROM
employees
WHERE
job_id IN (8, 9, 10)
ORDER BY
job_id;
示例：
SELECT
employee_id, first_name, last_name, salary
FROM
employees
WHERE
department_id IN (SELECT
department_id
FROM
departments
WHERE
department_name = '市场营销'
OR department_name = 'IT')

Like 运算符 ( 模糊查询 )

语法：expression LIKE pattern

如果表达式与模式匹配，则 LIKE 运算符返回true。否则它返回false。

LIKE 运算符通常用于 SELECT，UPDATE 或 DELETE 语句的 WHERE 子句中。

要构造模式，请使用两个SQL通配符：

%百分号匹配零个，一个或多个字符。
_下划线符号匹配单个字符。

一些模式及其含义：

模式含义
LIKE 'Yii%' 匹配以Yii开始的字符串
LIKE '%su' 匹配以su结尾的字符串
LIKE '%ch% 匹配包含ch的字符串
LIKE 'Luc_' 以Luc开始，后面只有一个字符，例如：Lucy，Lucc等
LIKE '_cy' 以cy结尾，前面只有一个字符，例如：Lcy，ucy等
LIKE '%yiibai_' 包含yiibai，以任意数量的字符开头，最多以一个字符结尾。
LIKE '_yiibai%' 包含yiibai，最多以一个字符开头，以任意数量的字符结尾。

如果要匹配通配符%或_，则必须使用反斜杠字符\来对其进行转义。如果要使用其它的转义字符而不是反斜杠，可以在LIKE表达式中使用ESCAPE子句，

示例：
SELECT
employee_id, first_name, last_name
FROM
employees
WHERE
first_name LIKE 'M%'
AND first_name NOT LIKE 'Ma%'
ORDER BY
first_name;

模式	含义
`LIKE 'Yii%'`	匹配以`Yii`开始的字符串
`LIKE '%su'`	匹配以`su`结尾的字符串
`LIKE '%ch%`	匹配包含`ch`的字符串
`LIKE 'Luc_'`	以`Luc`开始，后面只有一个字符，例如：`Lucy`，`Lucc`等
`LIKE '_cy'`	以`cy`结尾，前面只有一个字符，例如：`Lcy`，`ucy`等
`LIKE '%yiibai_'`	包含`yiibai`，以任意数量的字符开头，最多以一个字符结尾。
`LIKE '_yiibai%'`	包含`yiibai`，最多以一个字符开头，以任意数量的字符结尾。

Is Null 运算符

什么是 NULL？

NULL在SQL中很特殊。 NULL表示数据未知的值，可以简单理解为表示：不适用或不存在的值。换句话说，NULL表示数据库中缺少数据。
例如，如果员工没有任何电话号码，可以将其存储为空字符串。但是，如果在插入员工记录时不知道他的电话号码，我们将使用未知电话号码的NULL值。
NULL值是特殊的，因为任何与NULL值的比较都不会导致 true 或 false，但在第三个逻辑结果中：未知。
语句返回 null 值：SELECT NULL = 5;
NULL值甚至不等于自身：SELECT NULL = NULL; // 结果为NULL值
不能使用比较运算符的等于(=)将值与NULL值进行比较。
SELECT
employee_id, first_name, last_name, phone_number
FROM
employees
WHERE
phone_number = NULL;

IS NULL 和 IS NOT NULL 运算符

检查表达式或列的值是否为NULL，使用 IS NULL 运算符：expression IS NULL; 如果结果为NULL，则 IS NULL 运算符返回 true; 否则它返回false
检查表达式或列是否不为 NULL，请使用IS NOT运算符：expression IS NOT NULL;

示例：
SELECT
employee_id,
first_name,
last_name,
phone_number
FROM
employees
WHERE
phone_number IS NOT NULL;

Not 运算符

NOT 运算符来否定 SELECT 语句 WHERE 子句中的布尔表达式。

示例：
SELECT
employee_id,
first_name,
last_name,
salary
FROM
employees
WHERE
department_id = 5
AND NOT salary > 5000
ORDER BY
salary;

示例：
SELECT
employee_id,
first_name,
last_name,
department_id
FROM
employees
WHERE
department_id NOT IN (1, 2, 3)
ORDER BY
first_name;
示例：
SELECT
first_name,
last_name
FROM
employees
WHERE
first_name NOT LIKE 'M%'
ORDER BY
first_name;
示例：
SELECT
employee_id,
first_name,
last_name,
salary
FROM
employees
WHERE
salary NOT BETWEEN 3000
AND 5000
ORDER BY
salary;
示例：
SELECT
employee_id,
first_name,
last_name,
salary
FROM
employees
WHERE
salary NOT BETWEEN 3000
AND 5000
ORDER BY
salary;

别名

SQL 别名用于在执行查询期间为表或列分配临时名称。有两种类型的别名："表别名" 和 "列别名"。几乎所有关系数据库管理系统都支持列别名和表别名。

列别名
示例：
SELECT
inv_no AS invoice_no,
amount,
due_date AS '截止日期',
cust_no '客户编号'
FROM
invoices;

示例：
SELECT
count(employee_id) headcount
FROM
employees;

示例：
SELECT
department_id,
count(employee_id) headcount
FROM
employees
GROUP BY
department_id
HAVING
headcount >= 5;

表别名

使用表别名的是节省输入冗长名称的时间并使查询更容易理解。
使用表别名，可以在单个查询中多次引用同一个表。例如经常在内联接，左联接和自联接中找到此类查询。

示例：
SELECT
d.department_name
FROM
departments AS d // d是departments表的表别名。 AS关键字是可选。

示例：
SELECT
employee_id,
first_name,
last_name,
employees.department_id,
department_name
FROM
employees
INNER JOIN departments ON departments.department_id = employees.department_id
ORDER BY
first_name;

示例：
SELECT
employee_id,
first_name,
last_name,
e.department_id,
department_name
FROM
employees e
INNER JOIN departments d ON d.department_id = e.department_id
ORDER BY
first_name;

示例：
SELECT
e.first_name AS employee,
m.first_name AS manager
FROM
employees e
LEFT JOIN employees m ON m.employee_id = e.manager_id
ORDER BY
manager;

多表连接

连接表的过程称为 Join。 SQL提供了多种连接，如内连接，左连接，右连接，全外连接等。

SQL 的各种 JOIN 用法：https://www.runoob.com/w3cnote/sql-join-image-explain.html

join 分两种：

内连接（INNER JOIN）。显示左右两表能完全匹配的数据
外连接（OUTER JOIN）。外连接的 "OUTER" 关键字可以省略不写。其中外连接分为：
1. 左外连接 ( left (outer) join )
左外连接（左连接）：显示左表所有数据，右表匹配不上的显示为NULL。
SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c

2. 右外连接 ( right (outer) join )
右外连接 (右连接)：显示右表所有数据，左表匹配不上的显示为NULL

3. 全外连接 ( full/cross (outer) join )
全外连接：显示左右两个的表所有数据，两表匹配不上的显示为NULL

inner join ( 内连接 )

示例：
假设有两个表：A和B。
表A有四行：(1,2,3,4)，表B有四行：(3,4,5,6)
当表A使用内部联接与表B连接时，我们得到结果集(3,4)，它是表A和表B的交集。

对于表A中的每一行，内连接查找表B中的匹配行。如果匹配行，则它将包含在最终结果集中。

假设 A＆B 表的列名是n，以下语句说明了内连接子句：
SELECT
A.n
FROM A
INNER JOIN B ON B.n = A.n;

INNER JOIN子句出现在FROM子句之后。在ON关键字之后指定表A和表B之间匹配的条件。这种情况称为连接条件，即B.n = A.n

INNER JOIN 可以连接三个或更多表，只要它们具有关系，通常是外键关系。

例如，以下语句说明了如何连接3个表：A，B和C：
SELECT
A.n
FROM A
INNER JOIN B ON B.n = A.n
INNER JOIN C ON C.n = A.n;

示例：INNER JOIN 2个表

使用 employees 和 departments 表来演示 INNER JOIN 子句的工作原理。这两个表的结构和关系如下所示：

每个员工都属于一个且只有一个部门，而每个部门可以拥有多个员工。员工和部门表之间的关系是一对多的。
employees表中的department_id列是将员工链接到departments表的外键列。
要获取部门ID为：1,2和3的信息，请使用以下语句。

SELECT
first_name,
last_name,
employees.department_id,
departments.department_id,
department_name
FROM
employees
INNER JOIN
departments ON departments.department_id = employees.department_id
WHERE
employees.department_id IN (1 , 2, 3);

示例：INNER JOIN 3个表

每个员工都有一个工作岗位，而一个工作岗位可能会有多个员工。 jobs表和employees表之间的关系是一对多的。

以下数据库图说明了employees, departments 和 jobs 表之间的关系：

以下查询使用内部联接子句连接3个表：员工，部门和工作岗位，以获取在部门ID为：1,2和3中工作的员工的名字，姓氏，职位和部门名称。
SELECT
first_name, last_name, job_title, department_name
FROM
employees e
INNER JOIN departments d ON d.department_id = e.department_id
INNER JOIN jobs j ON j.job_id = e.job_id
WHERE
e.department_id IN (1, 2, 3);

left join ( 左外连接 )

左连接将返回左表中的所有行，而不管右表中是否存在匹配的行。

假设有两个表A和B。表A有四行：1,2,3和4。表B还有四行：3,4,5,6。
当将表A与表B连接时，表A中的所有行(左表)都包含在结果集中，而不管无论表B中是否存在匹配的行。

语法将表A与表B连接起来。
SELECT
A.n
FROM
A
LEFT JOIN B ON B.n = A.n;
LEFT JOIN子句出现在FROM子句之后。 ON关键字后面的条件称为连接条件B.n = A.n。

示例：
SELECT
c.country_name, c.country_id, l.country_id, l.street_address, l.city
FROM
countries c
LEFT JOIN locations l ON l.country_id = c.country_id
WHERE
c.country_id IN ('US', 'UK', 'CN')

示例：
SELECT
country_name
FROM
countries c
LEFT JOIN locations l ON l.country_id = c.country_id
WHERE
l.location_id IS NULL
ORDER BY
country_name;

示例：左外链接 3个表

请参阅下表：regions, countries和locations的结构和关系。

一个地区可能有零个或多个国家，而每个国家都位于一个地区。 countries和regions表之间的关系是一对多的。 countries表中的region_id列是国家和地区表之间的链接。
以下语句演示了如何连接3个表：regions, countries和locations：

right join ( 右外连接 )

full outer join ( 全外连接 )

全外连接是左连接和右连接的组合。完整外连接包括连接表中的所有行，无论另一个表是否具有匹配的行。

如果连接表中的行不匹配，则完整外连接的结果集包含缺少匹配行的表的每列使用NULL值。对于匹配的行，结果集中包含从连接表填充列的行。

两个表的完全外连接的语法：
SELECT column_list
FROM A
FULL OUTER JOIN B ON B.n = A.n;
请注意，OUTER 关键字是可选的。

-- 创建表1
CREATE TABLE fruits (
fruit_id INTEGER PRIMARY KEY,
fruit_name VARCHAR (255) NOT NULL,
basket_id INTEGER
);
-- 创建表2
CREATE TABLE baskets (
basket_id INTEGER PRIMARY KEY,
basket_name VARCHAR (255) NOT NULL
);

-- 插入数据1
INSERT INTO baskets (basket_id, basket_name)
VALUES
(1, 'A'),
(2, 'B'),
(3, 'C');

-- 插入数据2
INSERT INTO fruits (
fruit_id,
fruit_name,
basket_id
)
VALUES
(1, 'Apple', 1),
(2, 'Orange', 1),
(3, 'Banana', 2),
(4, 'Strawberry', NULL);

示例：
SELECT
basket_name,
fruit_name
FROM
fruits
FULL OUTER JOIN baskets ON baskets.basket_id = fruits.basket_id;

示例：
SELECT
basket_name,
fruit_name
FROM
fruits
FULL OUTER JOIN baskets ON baskets.basket_id = fruits.basket_id
WHERE
fruit_name IS NULL;

示例：
SELECT
basket_name,
fruit_name
FROM
fruits
FULL OUTER JOIN baskets ON baskets.basket_id = fruits.basket_id
WHERE
basket_name IS NULL;

cross Join ( 交叉连接 )

交叉连接是一种连接操作，它生成两个或多个表的笛卡尔积。

在数学中，笛卡尔积是一种返回多组产品集的数学运算。

例如，有两组集合：A {x，y，z}和B {1,2,3}，A x B的笛卡尔乘积是所有有序对(x，1)，(x，2)， (x，3)，(y，1),(y，2)，(y，3)，(z，1)，(z，2)，(z，3)。

下图说明了A和B的笛卡尔积：

类似地，在SQL中，两个表A和B的笛卡尔乘积是结果集，其中第一个表(A)中的每一行与第二个表(B)中的每一行配对。假设A表有n行，而B表有m行，那么A和B表的交叉连接结果有n x m行。

以下是 CROSS JOIN 子句的语法：
SELECT column_list
FROM A
CROSS JOIN B;

与INNER JOIN，LEFT JOIN和FULL OUTER JOIN不同，CROSS JOIN子句没有连接条件。

等价于 CROSS JOIN 语句：
SELECT
column_list
FROM
A,
B;

下图说明了表A和表B之间的交叉连接的结果。在图中，表A具有三行记录:1,2和3，而表B也具有三行记录：x，y和z。那么笛卡尔积结果有九行：

示例：
SELECT
sales_org,
channel
FROM
sales_organization
CROSS JOIN sales_channel;
示例：
SELECT
sales_org,
channel
FROM
sales_organization,
sales_channel;

示例：在某些数据库系统(如PostgreSQL和Oracle)中，
可以使用INNER JOIN子句，其条件始终求值为true以执行交叉连接，
SELECT
sales_org,
channel
FROM
sales_organization
INNER JOIN sales_channel ON 1 = 1;

自连接

将表连接到自身是很有用的。这种类型的连接称为自连接。
我们将一张表连接到自身来评估同一个表中其他行的行。要执行自联接，我们使用内连接或左连接子句。因为同一张表在单个查询中出现两次，所以必须使用表别名。

用法：
SELECT
column1,
column2,
column3,
...
FROM
table1 A
INNER JOIN table1 B ON B.column1 = A.column2;
在此语句中，使用INNER JOIN子句将table1连接到自身。 A和B是table1的表别名。 B.column1 = A.column2是连接条件。
除了INNER JOIN子句，还可以使用LEFT JOIN子句。

将employees表连接到自身，以查询每位员工的上级经理信息。
SELECT
CONCAT(e.first_name, ' ', e.last_name) as employee,
CONCAT(m.first_name, ' ', m.last_name) as manager
FROM
employees e
INNER JOIN
employees m ON m.employee_id = e.manager_id
ORDER BY manager;
由于内部联接子句仅包括在另一个表中具有匹配行的行，因此主席未显示在上述查询的结果集中。
主席(president)没有任何经理。在employees表中，包含主席的行的manager_id为NULL。
要在结果集中包含主席，使用LEFT JOIN子句而不是INNER JOIN子句作为以下查询。
SELECT
CONCAT(e.first_name, ' ', e.last_name) as employee,
CONCAT(m.first_name, ' ', m.last_name) as manager
FROM
employees e
LEFT JOIN
employees m ON m.employee_id = e.manager_id
ORDER BY manager;

SQL 聚合函数

聚合函数用于对一组值进行计算并返回一个单一的值。因为聚合函数是对一组值进行操作，所以它通常与 SELECT语句的 GROUP BY 子句一起使用。以便对数据进行分组并在每个组上应用聚合函数。用法：
SELECT c1, aggregate_function(c2)
FROM table
GROUP BY c1;

常用的SQL聚合函数：

AVG() - 返回集合的平均值。
用法：SELECT AVG(column_name) FROM table_name;
例如：SELECT AVG(price) FROM products; 计算 products 表中 price 列的平均值。
COUNT() - 返回集合中的项目数。
用法：
SELECT COUNT(*) FROM table_name; 计算表中的总行数。
SELECT COUNT(column_name) FROM table_name; 计算特定列中非 NULL 值的数量。
例如：SELECT COUNT(id) FROM customers;
MAX() - 返回集合中的最大值。
用法：SELECT MAX(column_name) FROM table_name;
例如：SELECT MAX(price) FROM products; 返回 products 表中 price 列的最大值。
MIN() - 返回集合中的最小值。
用法：SELECT MIN(column_name) FROM table_name;
例如：SELECT MIN(price) FROM products; 返回 products 表中 price 列的最小值。
SUM() - 返回集合中所有或不同值的总和。
用法：SELECT SUM(column_name) FROM table_name;
例如：SELECT SUM(quantity) FROM orders; 计算 orders 表中 quantity 列的总和。

除 COUNT() 函数外，SQL聚合函数忽略null值。只能将聚合函数用作表达式，如下所示：

SELECT语句的选择列表，子查询或外部查询。
一个HAVING子句

AVG() 平均值

AVG() 函数返回集合中的平均值。语法：AVG([ALL|DISTINCT] expression)
ALL关键字指示AVG()函数计算所有值的平均值，而DISTINCT关键字强制函数仅对不同的值进行操作。默认情况下，使用ALL选项。
以下示例演示如何使用AVG()函数计算每个部门的平均工资：

示例：
SELECT
department_name, ROUND(AVG(salary), 0) avg_salary
FROM
employees
INNER JOIN
departments USING (department_id)
GROUP BY department_name
ORDER BY department_name;

使用ALL关键字，AVG函数将获取计算中的所有值。默认情况下，无论是否指定，AVG函数都使用ALL。
如果明确指定DISTINCT关键字，AVG函数将仅在计算中采用唯一值。
例如，有一组数据集(1,2,3,3,4)并将AVG(ALL)应用于此集合，AVG函数将执行以下计算：(1+2+3+3+4)/5 = 2.6
但是，如果指定：AVG(DISTINCT)将按如下方式处理：(1+2+3+4)/4 = 2.5

示例：
SELECT
department_id,
AVG(salary)
FROM
employees
GROUP BY
department_id;

示例：
SELECT
e.department_id,
department_name,
AVG(salary)
FROM
employees e
INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
e.department_id;

示例：
SELECT
e.department_id,
department_name,
AVG(salary)
FROM
employees e
INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
e.department_id
ORDER BY
AVG(salary) DESC;

示例：
SELECT
e.department_id,
department_name,
AVG(salary) AS avgsalary
FROM
employees e
INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
e.department_id
HAVING avgsalary<5000
ORDER BY
AVG(salary) DESC;

示例：
SELECT
AVG(employee_sal_avg)
FROM
(
SELECT
AVG(salary) employee_sal_avg
FROM
employees
GROUP BY
department_id
) t;

MIN()、MAX() 最小、最大

MIN()函数返回集合的最小值。语法：MIN(column | expression)
例如，以下语句返回每个部门中员工的最低工资：
SELECT
department_name, MIN(salary) min_salary
FROM
employees
INNER JOIN
departments USING (department_id)
GROUP BY department_name
ORDER BY department_name;

MAX()函数返回集合的最大值，语法：MAX(column | expression)
例如，以下语句返回每个部门中员工的最高薪水：
SELECT
department_name, MAX(salary) highest_salary
FROM
employees
INNER JOIN
departments USING (department_id)
GROUP BY department_name
ORDER BY department_name;

示例：
SELECT
employee_id,
first_name,
last_name,
salary
FROM
employees
WHERE
salary = (
SELECT
MAX(salary)
FROM
employees
);
示例：
SELECT
d.department_id,
department_name,
MAX(salary)
FROM
employees e
INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
e.department_id;
示例：
SELECT
d.department_id,
department_name,
MAX(salary)
FROM
employees e
INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
e.department_id
ORDER BY
MAX(salary) DESC;
示例：
SELECT
d.department_id,
department_name,
MAX(salary)
FROM
employees e
INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
e.department_id
HAVING
MAX(salary) > 12000;

COUNT() 数量

COUNT() 函数返回集合中的项目数。语法：COUNT ( [ALL | DISTINCT] column | expression | *)

默认情况下，COUNT函数使用ALL关键字，无论是否指定它。 ALL关键字表示考虑组中的所有项目，包括重复值。例如，如果有一个数据集合(1,2,3,3,4,4)并应用COUNT函数，则结果为6。
如果明确指定DISTINCT关键字，则仅考虑唯一的非NULL值。如果将COUNT函数应用于数据集(1,2,3,3,4,4)，则COUNT函数返回4。
COUNT(*)函数返回表中的行数，包括包含NULL值的行。

例如，以下示例使用COUNT(*)函数返回每个部门的人数：
SELECT
department_name, COUNT(*) headcount
FROM
employees
INNER JOIN
departments USING (department_id)
GROUP BY department_name
ORDER BY department_name;

示例：
SELECT COUNT(*) FROM employees;
SELECT COUNT(*) FROM employees WHERE department_id = 6;
示例：
SELECT
e.department_id,
department_name,
COUNT(*)
FROM
employees e
INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
e.department_id;
示例：
SELECT
e.department_id,
department_name,
COUNT(*)
FROM
employees e
INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
e.department_id
ORDER BY
COUNT(*) DESC;
示例：
SELECT
e.department_id,
department_name,
COUNT(*)
FROM
employees e
INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
e.department_id
HAVING
COUNT(*) > 5
ORDER BY
COUNT(*) DESC;

SUM() 求和

SUM() 函数返回所有值的总和。语法：SUM(ALL | DISTINCT column)

SUM 函数是一个聚合函数，它返回所有或不同值的总和。SUM 函数应用于数字列。
有一组集合值：(1,2,3,3，NULL)。使用SUM函数将返回9，SUM函数会忽略NULL值。
要计算唯一值的总和，可以使用DISTINCT运算符，例如，集合(1,2,3,3，NULL)的SUM(DISTINCT)为6。

例如，以下语句返回每个部门中所有员工的总薪水：
SELECT
department_id, SUM(salary)
FROM
employees
GROUP BY department_id;

示例：
SELECT
e.department_id,
department_name,
SUM(salary)
FROM
employees e
INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
e.department_id
HAVING
SUM(salary) > 30000
ORDER BY
SUM(salary) DESC;

CONCAT 拼接字符串

拼接字符串
select CONCAT(f_name, " ", l_name)
AS Name
from employee_data
where title = 'Marketing Executive';
这里用到CONCAT()函数，用来把字符串串接起来。
另外，AS 用来给结果列'CONCAT(f_name, " ", l_name)'起个别名。

select concat(id,':',name,'=') from students; 拼接字符或者多个列(将列id与":"和列name和"="连接)

高级查询

1、1=1，1=2 的使用

"where 1=1" 是表示选择全部 "where 1=2" 全部不选

GROUPING SETS 分组集

使用 UNION ALL 运算符组合查询。UNION ALL要求所有结果集具有相同的列数，因此如果列不够，可以将NULL添加到每个查询的选择列表中，

没使用 group sets 之前
SELECT
warehouse,
product,
SUM(quantity) qty
FROM
inventory
GROUP BY
warehouse,
product
UNION ALL
SELECT
warehouse,
null,
SUM(quantity) qty
FROM
inventory
GROUP BY
warehouse
UNION ALL
SELECT
null,
product,
SUM(quantity) qty
FROM
inventory
GROUP BY
product
UNION ALL
SELECT
null,
null,
SUM(quantity) qty
FROM
inventory;
使用之后
SELECT
warehouse,
product,
SUM (quantity) qty
FROM
inventory
GROUP BY
GROUPING SETS(
(warehouse,product),
(warehouse),
(product),
()
);

Rollup

ROLLUP是GROUP BY子句的扩展。 ROLLUP选项允许包含表示小计的额外行，通常称为超级聚合行，以及总计行。通过使用ROLLUP选项，可以使用单个查询生成多个分组集。

ROLLUP的基本语法：
SELECT
c1, c2, aggregate_function(c3)
FROM
table
GROUP BY ROLLUP (c1, c2);
MySQL的语法略有不同，如下所示：
SELECT
c1, c2, aggregate_function(c3)
FROM
table_name
GROUP BY c1, c2 WITH ROLLUP;

示例：
SELECT
warehouse, SUM(quantity)
FROM
inventory
GROUP BY ROLLUP(warehouse);
示例：
SELECT
COALESCE(warehouse, 'All warehouses') AS warehouse,
SUM(quantity)
FROM
inventory
GROUP BY ROLLUP (warehouse);
示例：
SELECT
warehouse, product, SUM(quantity)
FROM
inventory
GROUP BY warehouse, product;
示例：
SELECT
warehouse, product, SUM(quantity)
FROM
inventory
GROUP BY ROLLUP (warehouse , product);
示例：
SELECT
warehouse, product, SUM(quantity)
FROM
inventory
GROUP BY warehouse, ROLLUP (product);

Union 联合 ( 并集 )

UNION运算符将两个或多个SELECT语句的结果集合并到一个结果集中。

语法：
SELECT
column1, column2
FROM
table1
UNION [ALL]
SELECT
column3, column4
FROM
table2;

UNION：会对合并后的结果集进行去重操作，即如果两个结果集中有重复的行，只保留其中一行。
UNION ALL：不会进行去重操作，会直接合并两个结果集，包括所有重复的行。因为它不需要进行去重操作，减少了比较和排序的开销。允许重复数据推荐使用 UNION ALL

要使用UNION运算符，可以编写单独的SELECT语句，并通过关键字UNION将它们连接起来。
SELECT语句返回的列必须具有相同或可转换的数据类型，大小和相同的顺序。

数据库系统首先执行两个SELECT语句来处理查询。然后，它将两个单独的结果集合并为一个，并消除重复的行。为了消除重复的行，数据库系统对每列的组合结果进行排序，并扫描它以查找彼此相邻的匹配行。要保留结果集中的重复行，使用UNION ALL运算符。

示例：
SELECT id FROM a
UNION ALL
SELECT id FROM b;
示例：
SELECT id FROM a
UNION ALL
SELECT id FROM b;
ORDER BY id DESC;

数据库系统执行以下步骤：

首先，分别执行每个SELECT语句。
其次，组合结果集并删除重复行以创建组合结果集。
第三，按ORDER BY子句中指定的列对组合结果集进行排序。

Intersect 交集

大多数关系数据库系统支持INTERSECT运算符，如Oracle数据库，Microsoft SQL Server，PostgreSQL等。但是，某些数据库系统(MySQL)不提供INTERSECT运算符。可以使用 INNER JOIN子句

Minus / except 差集

except 运算符：通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL)，不消除重复行。

子查询 ( 嵌套查询 )

子查询是一个 SELECT 查询，它嵌套在 SELECT、INSERT、UPDATE、DELETE 语句或其它子查询中。子查询就是放在括号内的查询。任何允许使用表达式的地方都可以使用子查询。子查询也称为内部查询或内部选择。包含子查询的查询称为外部查询或外部选择。要执行查询，首先，数据库系统必须执行子查询并将括号之间的子查询替换为其结果。

如果子查询的条件中使用了其外层的表的字段，这种子查询就叫作相关子查询。相关子查询可以用IN、NOT IN、EXISTS、NOT EXISTS引入。

使用 IN 或 NOT IN 运算符，同时 IN 的相关子查询可以用 EXISTS 代替
比如
select pub_name from publishers
where pub_id in
( select pub_id from titles where type = 'business' )
可以用下面语句代替：
select pub_name from publishers where exists
( select 1 from titles where type = 'business' and pub_id= publishers.pub_id )
使用 EXISTS 或 NOT EXISTS 运算符。EXISTS 运算符检查子查询返回的行是否存在。如果子查询包含任何行，则返回true。否则，它返回false。
EXISTS 语法：EXISTSE (subquery )
NOT EXISTS 与 EXISTS 语法相反：NOT EXISTS (subquery)
如果保证子查询没有重复，IN、EXISTS 的相关子查询可以用 INNER JOIN 代替。
比如：
select pub_name
from publishers
where pub_id in ( select pub_id from titles where type = 'business' )
可以改写成：
select a.pub_name --select distinct a.pub_name
from publishers a inner join titles b
on b.type = 'business' and a.pub_id=b. pub_id
NOT IN、NOT EXISTS 的相关子查询可以改用 LEFT JOIN 代替
比如：
select pub_name
from publishers
where pub_id not in (select pub_id from titles where type = 'business')
可以改写成：
select a.pub_name
from publishers a left join titles b
on b.type = 'business' and a.pub_id=b.pub_id
where b.pub_id is null
又比如：
select title from titles
where not exists ( select title_id from sales where title_id = titles.title_id )
可以改写成：
select title
from titles left join sales
on sales.title_id = titles.title_id
where sales.title_id is null
使用 ANY 或 ALL 运算符。
ANY 查询的语法：comparison_operator ANY (subquery)
例如，如果x大于子查询返回的任何值，则以下条件的计算结果为true。
因此，如果x大于1，则条件：x > SOME(1,2,3) 的计算结果为true。
SOME运算符是ANY运算符的同义词，因此可以互换使用：x > ANY (subquery)
在 FROM 子句中
在 SELECT 子句中
比较运算符中
不要用 COUNT(*) 的子查询判断是否存在记录，最好用 LEFT JOIN 或者 EXISTS
比如：
select job_desc from jobs
where ( select count(*) from employee where job_id=jobs.job_id)=0
应该写成：
select jobs.job_desc from jobs left join employee
on employee.job_id=jobs.job_id
where employee.emp_id is null
还有
select job_desc from jobs
where (select count(*) from employee where job_id=jobs.job_id)<>0
应该写成：
select job_desc from jobs
where exists (select 1 from employee where job_id=jobs.job_id)

SELECT
employee_id, first_name, last_name
FROM
employees
WHERE department_id IN (
SELECT
department_id
FROM
departments
WHERE
location_id = 1700
)
ORDER BY first_name , last_name;

示例：
SELECT
employee_id, first_name, last_name, salary
FROM
employees
WHERE
salary = (SELECT
MAX(salary)
FROM
employees)
ORDER BY first_name , last_name;

示例：
SELECT
employee_id, first_name, last_name, salary
FROM
employees
WHERE
salary > (SELECT
AVG(salary)
FROM
employees);
示例：
SELECT
department_name
FROM
departments d
WHERE
EXISTS( SELECT
1
FROM
employees e
WHERE
salary > 10000
AND e.department_id = d.department_id)
ORDER BY department_name;
示例：
SELECT
employee_id, first_name, last_name, salary
FROM
employees
WHERE
salary >= SOME (SELECT
MAX(salary)
FROM
employees
GROUP BY department_id);
示例：在此语法中，表别名是必需的，因为FROM子句中的所有表都必须具有名称。
SELECT * FROM (subquery) AS table_name
示例：
SELECT
ROUND(AVG(average_salary), 0)
FROM
(SELECT
AVG(salary) average_salary
FROM
employees
GROUP BY department_id) department_salary;
示例：
SELECT
employee_id,
first_name,
last_name,
salary,
(SELECT
ROUND(AVG(salary), 0)
FROM
employees) average_salary,
salary - (SELECT
ROUND(AVG(salary), 0)
FROM
employees) difference
FROM
employees
ORDER BY first_name , last_name;

Exists、NOT EXISTS

EXISTS 运算符检查子查询返回的行是否存在。如果子查询包含任何行，则返回true。否则，它返回false。
EXISTS 语法：EXISTSE (subquery )
NOT EXISTS 与 EXISTS 语法相反：NOT EXISTS (subquery)

如果子查询返回NULL，则EXISTS运算符仍返回结果集。这是因为EXISTS运算符仅检查子查询返回的行的存在。行是否为NULL无关紧要。

示例：子查询返回NULL，但EXISTS运算符仍然计算为true：

SELECT
employee_id, first_name, last_name
FROM
employees
WHERE
EXISTS( SELECT NULL)
ORDER BY first_name , last_name;

All 运算符

ALL运算符是一个逻辑运算符，它将单个值与子查询返回的单列值集进行比较。

以下是SQL ALL运算符的语法：WHERE column_name comparison_operator ALL (subquery)

SQL ALL运算符必须以比较运算符开头，例如：>，>=，<，<=，<>，=，后跟子查询。某些数据库系统(如Oracle)允许使用文字值列表而不是子查询。
请注意，如果子查询不返回任何行，则WHERE子句中的条件始终为true。假设子查询返回一行或多行，下表说明了SQL ALL运算符的含义：

条件描述
c > ALL(…) c列中的值必须大于要评估为true的集合中的最大值。
c >= ALL(…) c列中的值必须大于或等于要评估为true的集合中的最大值。
c < ALL(…) c列中的值必须小于要评估为true的集合中的最小值。
c <= ALL(…) c列中的值必须小于或等于要评估为true的集合中的最小值。
c <> ALL(…) c列中的值不得等于要评估为true的集合中的任何值。
c = ALL(…) c列中的值必须等于要评估为true的集合中的任何值。

查询查找column_name列中的值大于子查询返回的最大值的行：
SELECT
first_name, last_name, salary
FROM
employees
WHERE
salary > ALL (SELECT
salary
FROM
employees
WHERE
department_id = 2)
ORDER BY salary;

条件	描述
`c > ALL(…)`	`c`列中的值必须大于要评估为`true`的集合中的最大值。
`c >= ALL(…)`	`c`列中的值必须大于或等于要评估为`true`的集合中的最大值。
`c < ALL(…)`	`c`列中的值必须小于要评估为`true`的集合中的最小值。
`c <= ALL(…)`	`c`列中的值必须小于或等于要评估为`true`的集合中的最小值。
`c <> ALL(…)`	`c`列中的值不得等于要评估为`true`的集合中的任何值。
`c = ALL(…)`	`c`列中的值必须等于要评估为`true`的集合中的任何值。

Any 运算符

ANY运算符是一个逻辑运算符，它将值与子查询返回的一组值进行比较。 ANY运算符必须以比较运算符：>，>=，<，<=，=，<>开头，后跟子查询。

以下是ANY运算符的语法：WHERE column_name comparison_operator ANY (subquery)

如果子查询不返回任何行，则条件的计算结果为false。假设子查询不返回零行，下面说明了ANY运算符与每个比较运算符一起使用时的含义：

条件表示含义
x = ANY (…) c列中的值必须与集合中的一个或多个值匹配，以评估为true。
x != ANY (…) c列中的值不能与集合中的一个或多个值匹配以评估为true。
x > ANY (…) c列中的值必须大于要评估为true的集合中的最小值。
x < ANY (…) c列中的值必须小于要评估为true的集合中的最大值。
x >= ANY (…) c列中的值必须大于或等于要评估为true的集合中的最小值。
x <= ANY (…) c列中的值必须小于或等于要评估为true的集合中的最大值。

查找工资等于所在部门平均工资的所有员工，请使用以下查询：
SELECT
first_name,
last_name,
salary
FROM
employees
WHERE
salary = ANY (
SELECT
AVG(salary)
FROM
employees
GROUP BY
department_id)
ORDER BY
first_name,
last_name,
salary;

条件	表示含义
`x = ANY (…)`	`c`列中的值必须与集合中的一个或多个值匹配，以评估为`true`。
`x != ANY (…)`	`c`列中的值不能与集合中的一个或多个值匹配以评估为`true`。
`x > ANY (…)`	`c`列中的值必须大于要评估为`true`的集合中的最小值。
`x < ANY (…)`	`c`列中的值必须小于要评估为`true`的集合中的最大值。
`x >= ANY (…)`	`c`列中的值必须大于或等于要评估为`true`的集合中的最小值。
`x <= ANY (…)`	`c`列中的值必须小于或等于要评估为`true`的集合中的最大值。

3、LeetCode 高频 SQL

基础版 + 进阶版：https://blog.csdn.net/zhou_zzzzhou/article/details/135122962

经典 SQL 练习题

：https://github.com/bladeXue/sql50

基础版

题目考查类型   题号
查询   1-5
连接   6-14
聚合函数   15-22
排序和分组   23-29
高级查询和连接   30-36
子查询   37-43
高级字符串函数 / 正则表达式 / 子句   44-50

1757. 可回收且低脂的产品

：https://leetcode.cn/problems/recyclable-and-low-fat-products/description/

SELECT product_id
FROM Products
WHERE low_fats='Y' and recyclable='Y';

584. 寻找用户推荐人

：https://leetcode.cn/problems/find-customer-referee/description/

select name
from Customer
where referee_id != 2 or referee_id is null

595. 大的国家：https://leetcode.cn/problems/big-countries/description/
select name,population,area
from World
where area>=3000000 or population>=25000000

1148. 文章浏览 I：https://leetcode.cn/problems/article-views-i/description/
select distinct author_id as id
from Views
where author_id=viewer_id
order by id

1683. 无效的推文：https://leetcode.cn/problems/invalid-tweets/description/
select tweet_id
from Tweets
where length(content)>15

1378. 使用唯一标识码替换员工ID：https://leetcode.cn/problems/replace-employee-id-with-the-unique-identifier/description/
select unique_id,name
from Employees left join EmployeeUNI
on EmployeeUNI.ID=Employees.ID

1068. 产品销售分析 I：https://leetcode.cn/problems/product-sales-analysis-i/description/
select product_name,year,price
from Sales join Product
on Sales.product_id = Product.product_id

1581. 进店却未进行过交易的顾客：https://leetcode.cn/problems/customer-who-visited-but-did-not-make-any-transactions/description/
select customer_id, count(*) as count_no_trans
from Visits v left join Transactions t on
v.visit_id = t.visit_id
where transaction_id is null
group by customer_id

197. 上升的温度：https://leetcode.cn/problems/rising-temperature/description/
select w2.id id
from Weather w1 join Weather w2
on w1.recordDate = w2.recordDate-interval 1 day
where w1.Temperature < w2.Temperature

1661. 每台机器的进程平均运行时间：https://leetcode.cn/problems/average-time-of-process-per-machine/description/
# 运行时间17%
select start.machine_id, round(avg(end.timestamp-start.timestamp),3) processing_time
from
(select *
from Activity
where activity_type ='start') as start
join
(select *
from Activity
where activity_type ='end') as end
on
start.machine_id=end.machine_id and start.process_id=end.process_id
group by start.machine_id

# 运行时间45%
select machine_id,
round((2*sum(timestamp*(case when activity_type = 'start' then -1 else 1 end)))/count(activity_type),3) as processing_time
from Activity
group by machine_id

11.577. 员工奖金：https://leetcode.cn/problems/employee-bonus/description/

select name,bonus
from Employee left join Bonus
on Employee.empId = Bonus.empid
where bonus<1000 or bonus is null

12.1280. 学生们参加各科测试的次数：https://leetcode.cn/problems/students-and-examinations/description/

select a.student_id,a.student_name,a.subject_name,ifnull(attended_exams,0) attended_exams
from

(select *
from subjects join students) a
left join

(select *,count(e.student_id) as attended_exams
from Examinations e
group by e.student_id,e.subject_name) b

on a.student_id = b.student_id
and a.subject_name = b.subject_name

order by a.student_id,a.subject_name

13.570. 至少有5名直接下属的经理：https://leetcode.cn/problems/managers-with-at-least-5-direct-reports/description/

select e2.name name
from Employee e2
left join Employee e1
on e1.managerId=e2.id
group by e2.id
having count(*)>=5

14.1934. 确认率：https://leetcode.cn/problems/confirmation-rate/description/

select s.user_id,round(sum(if(action='confirmed',1,0))/count(*),2) confirmation_rate
from Signups s
left join Confirmations c
on s.user_id = c.user_id
group by s.user_id

15.620. 有趣的电影：https://leetcode.cn/problems/not-boring-movies/description/

select *
from cinema
where description!='boring' and id%2!=0
order by rating desc

16.1251. 平均售价：https://leetcode.cn/problems/average-selling-price/description/

select p.product_id,ifnull(round((sum(price*units)/sum(units)),2),0) as average_price
from Prices p left join UnitsSold u
on p.product_id = u.product_id
and u.purchase_date between p.start_date and p.end_date
group by p.product_id

17.1075. 项目员工 I：https://leetcode.cn/problems/project-employees-i/description/

select project_id,round(avg(experience_years),2) as average_years
from Project p left join Employee e
on p.employee_id = e.employee_id
group by project_id

18.1633. 各赛事的用户注册率：https://leetcode.cn/problems/percentage-of-users-attended-a-contest/description/

select contest_id,round(count(contest_id)/(select count(*) from Users)*100,2) as percentage
from Register r left join Users u
on r.user_id = u.user_id
group by contest_id
order by percentage desc,contest_id

19.1211. 查询结果的质量和占比：https://leetcode.cn/problems/queries-quality-and-percentage/description/

select query_name,round(avg(rating/position),2) as quality,
round((100*sum(case when rating<3 then 1 else 0 end)/count(*)),2) as poor_query_percentage
from Queries
group by query_name
having query_name is not null

20.1193. 每月交易 I：https://leetcode.cn/problems/monthly-transactions-i/description/

select left(trans_date,7) as month,
country,count(*) as trans_count,
sum(case when state='approved' then 1 else 0 end) as approved_count,
sum(amount) as trans_total_amount,
sum((case when state='approved' then 1 else 0 end)*amount) as approved_total_amount
from Transactions
group by month,country

21.1174. 即时食物配送 II：https://leetcode.cn/problems/immediate-food-delivery-ii/description/

select round((sum(case when customer_pref_delivery_date=order_date then 1 else 0 end)*100/count(*)),2) as immediate_percentage
from
(select customer_id,min(order_date) as order_date,min(customer_pref_delivery_date) as customer_pref_delivery_date
from Delivery
group by customer_id) as first_order

22.550. 游戏玩法分析 IV：https://leetcode.cn/problems/game-play-analysis-iv/description/

# 卡了很久最小时间
select round(count(*)/(select count(distinct player_id) from Activity),2) as fraction
from
((select player_id,min(event_date) as event_date
from Activity
group by player_id) as a1

join Activity a2
on a1.player_id=a2.player_id
and a1.event_date=a2.event_date - interval 1 day)

23.2356. 每位教师所教授的科目种类的数量：https://leetcode.cn/problems/number-of-unique-subjects-taught-by-each-teacher/description/

select teacher_id,count(distinct subject_id) as cnt
from teacher
group by teacher_id

24.1141. 查询近30天活跃用户数：https://leetcode.cn/problems/user-activity-for-the-past-30-days-i/description/

select activity_date as day,count(distinct user_id) as active_users
from Activity
group by activity_date
having activity_date between ("2019-07-27"- interval 29 day) and "2019-07-27"

25.1084. 销售分析III：https://leetcode.cn/problems/sales-analysis-iii/description/

# 注意sum=count的用法，用于“所有都是……”的场景
select s.product_id,product_name
from Sales s left join Product p
on s.product_id=p.product_id
group by s.product_id
having sum(s.sale_date between "2019-01-01" and "2019-03-31")=count(*)

596. 超过5名学生的课：https://leetcode.cn/problems/classes-more-than-5-students/description/
select class
from Courses
group by class
having count(*)>=5

27.1729. 求关注者的数量：https://leetcode.cn/problems/find-followers-count/description/

select user_id,count(*) as followers_count
from Followers
group by user_id
order by user_id

28.619. 只出现一次的最大数字：https://leetcode.cn/problems/biggest-single-number/description/

select max(num) num
from
(select num
from MyNumbers
group by num
having count(*)=1) num1

29.1045. 买下所有产品的客户：https://leetcode.cn/problems/customers-who-bought-all-products/description/

select customer_id
from Customer
group by customer_id
having count(distinct product_key)= (select count(*) from Product)

30.1731. 每位经理的下属员工数量：https://leetcode.cn/problems/the-number-of-employees-which-report-to-each-employee/

select e2.employee_id,e2.name,count(*) as reports_count,
round(avg(e1.age),0) as average_age
from Employees e1 left join Employees e2
on e1.reports_to = e2.employee_id
group by e2.employee_id
having e2.employee_id is not null
order by employee_id

31.1789. 员工的直属部门：https://leetcode.cn/problems/primary-department-for-each-employee/description/

(select employee_id,department_id
from Employee
where primary_flag ='Y')
UNION
(select employee_id,department_id
from Employee
group by employee_id
having count(*)=1)
order by employee_id

32.610. 判断三角形：https://leetcode.cn/problems/triangle-judgement/description/

select *,
(case when (x+y>z and x+z>y and z+y>x) then "Yes"
else "No"
end) as triangle
from Triangle

33.180. 连续出现的数字：https://leetcode.cn/problems/consecutive-numbers/description/

select distinct L1.num as ConsecutiveNums
from Logs L1
join Logs L2 on L1.id=L2.id-1
join Logs L3 on L2.id=L3.id-1
where L1.num=L2.num and L2.num=L3.num

34.1164. 指定日期的产品价格：https://leetcode.cn/problems/product-price-at-a-given-date/description/

select product_id, new_price as price
from Products
where (product_id,change_date) in
(select product_id,max(change_date)
from Products
where change_date<="2019-08-16"
group by product_id)

union

select product_id,10 as price
from Products
where product_id not in (select product_id from Products where change_date<="2019-08-16")

35.1204. 最后一个能进入巴士的人：https://leetcode.cn/problems/last-person-to-fit-in-the-bus/description/

select q1.person_name
from Queue q1
join Queue q2 on q1.turn>=q2.turn
group by q1.person_id
having sum(q2.weight)<=1000
order by q1.turn desc limit 1

36.1907. 按分类统计薪水：https://leetcode.cn/problems/count-salary-categories/description/

select "Low Salary" category,count(*) accounts_count
from Accounts
where income<20000

union

select "Average Salary" category,count(*) accounts_count
from Accounts
where income between 20000 and 50000

union

select "High Salary" category,count(*) accounts_count
from Accounts
where income>50000

37.1978. 上级经理已离职的公司员工：https://leetcode.cn/problems/employees-whose-manager-left-the-company/description/

select employee_id
from Employees
where salary<30000 and manager_id not in (select employee_id from Employees)
order by employee_id

38.626. 换座位：https://leetcode.cn/problems/exchange-seats/description/

select
(case when id%2!=0 and id!=(select count(*) from Seat) then id+1
when id%2=0 then id-1
else id end) as id,student
from Seat
order by id

39.1341. 电影评分：https://leetcode.cn/problems/movie-rating/description/

(select name as results
from Users u
join MovieRating r1 on u.user_id = r1.user_id
group by u.user_id
order by count(*) desc,name limit 1)

union all

(select title as results
from Movies m
join MovieRating r2 on m.movie_id = r2.movie_id and left(r2.created_at,7) = "2020-02"
group by r2.movie_id
order by avg(rating) desc,title limit 1)

40.1321. 餐馆营业额变化增长：https://leetcode.cn/problems/restaurant-growth/description/

#注意join时where的用法以及分组之后avg函数的使用
select a.visited_on,sum(c.amount) as amount,round((sum(c.amount))/7,2) as average_amount
from
(select distinct visited_on
from Customer) as a
left join customer c
on (c.visited_on>=a.visited_on - interval 6 day) and (c.visited_on<=a.visited_on)
where a.visited_on>=(select min(visited_on) from customer)+6
group by a.visited_on
order by a.visited_on

41.602. 好友申请 II ：谁有最多的好友：https://leetcode.cn/problems/friend-requests-ii-who-has-the-most-friends/description/

select a.id,count(*) as num
from
(select requester_id as id from RequestAccepted r1
union all
select accepter_id as id from RequestAccepted r2) as a
group by id
order by num desc limit 1

42.585. 2016年的投资：https://leetcode.cn/problems/investments-in-2016/description/

select round(sum(tiv_2016),2) tiv_2016
from Insurance
where tiv_2015 in
(select tiv_2015 from Insurance
group by tiv_2015 having count(*)>1)

and concat(lat, lon) in
(select concat(lat, lon)
from Insurance
group by concat(lat, lon)
having count(*)=1)

43.185. 部门工资前三高的所有员工：https://leetcode.cn/problems/department-top-three-salaries/description/

select d.name as Department,e.name as Employee,e.salary
from Employee e left join Department d
on e.departmentId=d.id
where e.id in
(select e1.id
from Employee e1 left join Employee e2
on e1.departmentId=e2.departmentId and e1.salary<e2.salary
group by e1.id
having count(distinct e2.salary)<=2)

44.1667. 修复表中的名字：https://leetcode.cn/problems/fix-names-in-a-table/description/

select user_id,concat(upper(left(name,1)),lower(SUBSTRING(name,2))) name
from Users
order by user_id

45.1527. 患某种疾病的患者：https://leetcode.cn/problems/patients-with-a-condition/description/

select *
from Patients
where conditions like "DIAB1%" or conditions like "% DIAB1%"

46.196. 删除重复的电子邮箱：https://leetcode.cn/problems/delete-duplicate-emails/description/

delete from
Person
where id not in
(select id from(select min(id) id
from Person
group by email) as a)

47.176. 第二高的薪水：https://leetcode.cn/problems/second-highest-salary/description/

select ifnull((
select distinct salary
from Employee
order by salary desc
limit 1 offset 1),null) as SecondHighestSalary

48.1484. 按日期分组销售产品：https://leetcode.cn/problems/group-sold-products-by-the-date/description/

select sell_date,count(distinct product) as num_sold,
group_concat(distinct product order by product SEPARATOR ',') as products
from Activities
group by sell_date
order by sell_date

49.1327. 列出指定时间段内所有的下单产品：https://leetcode.cn/problems/list-the-products-ordered-in-a-period/description/

select product_name,sum(unit) as unit
from Products p join Orders o
on p.product_id=o.product_id and left(o.order_date,7)="2020-02"
group by product_name
having sum(unit)>=100

50.1517. 查找拥有有效邮箱的用户：https://leetcode.cn/problems/find-users-with-valid-e-mails/description/

SELECT user_id, name, mail
FROM Users
WHERE mail REGEXP '^[a-zA-Z][a-zA-Z0-9_.-]*\\@leetcode\\.com$';

进阶版

题目考查类型   题号
查询   1-5
连接   6-11
聚合函数   12-19
排序和分组   20-26
高级查询和连接   27-35
子查询   36-43
高级字符串函数 / 正则表达式 / 子句   44-50

1.1821. 寻找今年具有正收入的客户
select customer_id
from Customers
where year=2021 and revenue>0

2.183. 从不订购的客户

select name as Customers
from Customers
where id not in
(select customerId from Orders)

1873. 计算特殊奖金
select employee_id,salary*(case when employee_id%2!=0 and left(name,1)!="M" then 1 else 0 end) as bonus
from Employees
order by employee_id

4.1398. 购买了产品 A 和产品 B 却没有购买产品 C 的顾客

# 方法一
select customer_id,customer_name
from Customers
where customer_id in
(select customer_id
from Orders
where product_name="A" or product_name="B"
group by customer_id
having count(distinct product_name)=2)

and customer_id not in
(select customer_id
from Orders
where product_name="C"
group by customer_id)

# 方法二：巧用sum
select customer_id,customer_name
from Customers
where customer_id in
(select customer_id
from Orders
group by customer_id
having sum(product_name="A")*sum(product_name="B")>0
and sum(product_name="C")=0)

5.1112. 每位学生的最高成绩

select student_id,min(course_id) course_id,grade
from Enrollments
where (student_id,grade) in
(
select student_id,max(grade) grade
from Enrollments
group by student_id)
# 注意这里的group by，因为取了min，所有要group by
group by student_id
order by student_id,course_id

6.175. 组合两个表

select firstName,lastName,city,state
from Person p left join Address a on p.PersonId = a.personId

1607. 没有卖出的卖家
select seller_name
from Seller s
where seller_id not in
(select seller_id
from Orders
where left(sale_date,4)="2020")
order by seller_name

1407. 排名靠前的旅行者
select name,ifnull(sum(r.distance),0) travelled_distance
from Users u
left join Rides r on u.id=r.user_id
group by u.id
order by travelled_distance desc,name

9.607. 销售员

select name
from SalesPerson
where sales_id not in
(select sales_id
from Orders o join Company c
on o.com_id = c.com_id and c.name="RED" )

10.1440. 计算布尔表达式的值

select e.*,
(case when operator=">" and v1.value>v2.value then "true"
when operator="<" and v1.value<v2.value then "true"
when operator="=" and v1.value=v2.value then "true"
else "false"
end) as value
from Expressions e
left join Variables v1 on v1.name=e.left_operand
left join Variables v2 on v2.name=e.right_operand

11.1212. 查询球队积分

select t.team_id,t.team_name,
sum(case when m.host_team = t.team_id and host_goals>guest_goals then 3
when m.guest_team = t.team_id and host_goals<guest_goals then 3
when host_goals=guest_goals then 1
else 0
end) as num_points
from Matches m
right join Teams t on m.host_team = t.team_id or m.guest_team=t.team_id
group by t.team_id
order by num_points desc,team_id

12.1890. 2020年最后一次登录

select user_id,max(time_stamp) as last_stamp
from Logins
where left(time_stamp,4)='2020'
group by user_id

13.511. 游戏玩法分析 I

select player_id,min(event_date) as first_login
from Activity
group by player_id

14.1571. 仓库经理

select name as warehouse_name,sum(units*Width*Length*Height) as volume
from Warehouse w left join Products p
on w.product_id=p.product_id
group by w.name

15.586. 订单最多的客户

select customer_number
from Orders
group by customer_number
order by count(customer_number) desc limit 1

16.1741. 查找每个员工花费的总时间

select event_day as day,emp_id,sum(out_time-in_time) as total_time
from Employees
group by event_day,emp_id

17.1173. 即时食物配送 I

select round(100*sum(case when order_date=customer_pref_delivery_date then 1
else 0
end)/(count(delivery_id)),2) as immediate_percentage
from Delivery

18.1445. 苹果和桔子

select sale_date,sum(case when fruit="apples" then sold_num
else -sold_num
end ) as diff
from Sales
group by sale_date

19.1699. 两人之间的通话次数

# 先找到适合的顺序
select
(case when from_id<to_id then from_id else to_id end)as person1,
(case when from_id<to_id then to_id else from_id end)as person2,
count(*) as call_count,
sum(duration) as total_duration
from Calls
group by person1,person2

20.1587. 银行账户概要 II

select u.name as NAME,sum(t.amount) as BALANCE
from Transactions t left join Users u on t.account=u.account
group by t.account
having sum(amount)>10000

21.182. 查找重复的电子邮箱

select email Email
from Person
group by email
having count(*)>=2

22.1050. 合作过至少三次的演员和导演

select actor_id,director_id
from ActorDirector
group by actor_id,director_id
having count(*)>=3

23.1511. 消费者下单频率

select o.customer_id,c.name
from Orders o join Product p on o.product_id=p.product_id
join Customers c on o.customer_id=c.customer_id
group by customer_id
having sum(case when left(o.order_date,7)="2020-06" then quantity*price else 0 end)>=100
and sum(case when left(o.order_date,7)="2020-07" then quantity*price else 0 end)>=100

24.1693. 每天的领导和合伙人

select date_id,make_name,count(distinct lead_id) unique_leads,count(distinct partner_id) unique_partners
from DailySales
group by date_id,make_name

25.1495. 上月播放的儿童适宜电影

select distinct title
from Content c left join TVProgram t on c.content_id =t.content_id
where c.Kids_content='Y' and left(t.program_date,7)="2020-06" and content_type="Movies"

26.1501. 可以放心投资的国家

select co.name as country
from Person p join Country co on left(p.phone_number,3)=co.country_code
join Calls ca on p.id=ca.caller_id or p.id=ca.callee_id
group by co.country_code
having avg(duration)>(select avg(duration) from Calls)

27.603. 连续空余座位

select distinct c1.seat_id
from Cinema c1 join Cinema c2
on abs(c1.seat_id - c2.seat_id)=1
where c1.free=1 and c2.free=1
order by c1.seat_id

28.1795. 每个产品在不同商店的价格

select product_id,"store1" as store,store1 price
from Products
where store1 is not null
union all
select product_id,"store2" as store,store2 price
from Products
where store2 is not null
union all
select product_id,"store3" as store,store3 price
from Products
where store3 is not null

29.613. 直线上的最近距离

select min(abs(p1.x-p2.x)) as shortest
from Point p1 join Point p2 on p1.x!=p2.x

30.1965. 丢失信息的雇员

select employee_id
from Employees
where employee_id not in (select employee_id from Salaries)
union
select employee_id
from Salaries
where employee_id not in (select employee_id from Employees)
order by employee_id

31.1264. 页面推荐

select distinct page_id as recommended_page
from Likes
where user_id in(
select user2_id user_id
from Friendship
where user1_id ="1"
union
select user1_id user_id
from Friendship
where user2_id ="1")
and page_id not in (select page_id from Likes where user_id=1)

32.608. 树节点

select id,(case when p_id is null then "Root"
when id not in (select p_id from Tree where p_id is not null)then "Leaf"
else "Inner"
end )type
from Tree

33.534. 游戏玩法分析 III

SELECT a2.player_id,a2.event_date,sum(a1.games_played) as games_played_so_far
FROM Activity a1 left join Activity a2 on a1.player_id=a2.player_id and a2.event_date>=a1.event_date
group by a2.event_date,player_id

34.1783. 大满贯数量

select p.player_id,p.player_name,sum(c.Wimbledon=p.player_id)+sum(c.Fr_open=p.player_id)+sum(c.US_open=p.player_id)+sum(c.Au_open=p.player_id)grand_slams_count
from Players p,Championships c
group by p.player_id
having grand_slams_count>0

35.1747. 应该被禁止的 Leetflex 账户

select account_id
from(select account_id,login,logout,lead (login,1) over() as ll,nums
from(select *,row_number() over (partition by account_id order by login) as nums
from loginfo)aaa)bbb
where nums=1 and ll between login and logout

36.1350. 院系无效的学生

select id,name
from Students s
where s.department_id not in (select id from Departments)

37.1303. 求团队人数

select e1.employee_id,count(*) as team_size
from Employee e1 left join Employee e2 on e1.team_id=e2.team_id
group by e1.employee_id

38.512. 游戏玩法分析 II

select player_id,device_id
from Activity
where (player_id,event_date) in
(select player_id,min(event_date)
from Activity
group by player_id)

39.184. 部门工资最高的员工

select d.name Department,e.name Employee,e.salary
from Employee e left join Department d on e.departmentId=d.id
where (e.departmentId,e.salary) in (select departmentId,max(salary)
from Employee
group by departmentId )

40.1549. 每件商品的最新订单

select p.product_name,o.product_id,o.order_id,o.order_date
from Orders o left join Products p on o.product_id=p.product_id
where (o.product_id,o.order_date) in
(select product_id,max(order_date)
from Orders
group by product_id)
order by p.product_name,o.product_id,o.order_id

41.1532. 最近的三笔订单

select c.name as customer_name,c.customer_id,o2.order_id,o2.order_date
from Orders o1 left join Orders o2 on o1.customer_id=o2.customer_id and o1.order_date>=o2.order_date
left join Customers c on o1.customer_id=c.customer_id
group by o2.order_id
#比我日期更近的order_date不超过3个，所以是前三
having count(o1.order_date)<=3
order by c.name,c.customer_id,o2.order_date desc

42.1831. 每天的最大交易

select transaction_id
from Transactions
where (day,amount) in(select day,max(amount)
from Transactions
group by day)
order by transaction_id

43.1077. 项目员工 III

# 解题思路同39题
select p.project_id,p.employee_id
from Project p left join Employee e on p.employee_id=e.employee_id
where (p.project_id,e.experience_years) in
(select p.project_id,max(experience_years)
from Project p left join Employee e on p.employee_id=e.employee_id
group by p.project_id)

44.1285. 找到连续区间的开始和结束数字

#开窗函数，还看不太懂
select min(log_id) as start_id,max(log_id) as end_id
from
(SELECT
log_id,
log_id - row_number() over() diff
FROM logs)as t
group by diff

45.1596. 每位顾客最经常订购的商品

select o.customer_id,o.product_id,p.product_name
from
(select customer_id,product_id,
rank() over(partition by customer_id order by count(product_id) desc)rnk
from Orders
group by customer_id,product_id)o
join products p on o.product_id=p.product_id
where rnk=1

46.1709. 访问日期之间最大的空档期

select user_id,max(datediff(next_day,visit_date)) as biggest_window
from
(select user_id,visit_date,LEAD(visit_date,1,'2021-1-1') over(partition by user_id order by visit_date)as next_day
from UserVisits)as tmp
group by user_id
order by user_id

47.1270. 向公司 CEO 汇报工作的所有人

select distinct employee_id
from
(select employee_id from Employees where manager_id=1
union all
(select employee_id from Employees
where manager_id in (select employee_id from Employees where manager_id=1 ))
union all
select employee_id from Employees
where manager_id in (select employee_id from Employees
where manager_id in (select employee_id from Employees where manager_id=1 )))e
where employee_id!=1

SELECT e1.employee_id
FROM Employees e1
JOIN Employees e2 ON e1.manager_id = e2.employee_id
JOIN Employees e3 ON e2.manager_id = e3.employee_id
WHERE e1.employee_id != 1 AND e3.manager_id = 1

48.1412. 查找成绩处于中游的学生

select tmp.student_id,s.student_name
from
(select *,
if(dense_rank() over(partition by exam_id order by score desc)=1,1,0) d_rank,
if(dense_rank() over(partition by exam_id order by score )=1,1,0) a_rank
from Exam)tmp
left join Student s on tmp.student_id=s.student_id
group by tmp.student_id
having sum(d_rank)=0 and sum(a_rank)=0
order by tmp.student_id

49.1767. 寻找没有被执行的任务对

with recursive table1 as(select task_id,subtasks_count subtask_id
from Tasks
union all
select task_id,subtask_id-1 from table1 where subtask_id > 1)

select task_id,subtask_id
from table1
left join Executed E using(task_id, subtask_id)
where E.task_id is null

1225. 报告系统状态的连续日期

select type as period_state,min(date) as start_date,max(date) as end_date
from
(select type,date,subdate(date,row_number() over(partition by type order by date))as diff
from
(select "failed" as type,fail_date as date from Failed
union all
select "succeeded" as type,success_date as date from Succeeded)tmp1)tmp2
where date between "2019-01-01" and "2019-12-31"
group by type,diff
order by start_date