SQL基础知识

本文是参考《SQL基础教程》[日]MICK 书中的内容整理得来的，所以主要思路也是根据此书的章节内容展开。需要的可以去看这本书：《SQL基础教程》
https://www.ituring.com.cn/book/1880

1、 SQL概要

数据库管理系统（Database Management System,DBMS），根据数据保存格式（数据库的种类）可分为以下5种：

RDBMS通常使用客户端/服务器这样的系统结构，通过从客户端向服务器发送SQL语句来实现数据库的读写操作。数据库表采用二维表来管理数据，表的列（垂直方向）称为字段，表的行（水平方向）称为记录。关系数据库必须以行为单位进行数据读写。
SQL用关键字、表名、列名等组合成一条SQL语句来描述操作的内容。根据指令种类的不同，SQL语句可以分为以下三类：

SQL的基本书写规则：
1、以分号（；）结尾
2、不区分关键字大小写（SELECT = select）
3、字符串和日期常数通常用 ’ ’ 括起来
4、单词之间使用半角空格或者换行来分割

2、查询基础

2.1 、SELECT 语句基础

select <列名>,… FROM <表名>；查询指定列名的列；
select * FROM <表名>； 查询全部列；
可以使用AS关键字为列设置别名，别名可以使用中文，使用中文时需要用双引号 " " 括起来。

select product_id as"商品编号", product_name as "商品名称",purchase_price as "单价" from Product;

常数的查询：

使用DISTINCT删除重复数据：

使用DISTINCT删除多列重复的数据：（将多列数据组合，然后删除重复的数据）

where 语句过滤：
首先通过where子句查询出符合指定条件的记录，然后再选取出select语句指定的列。
where语句紧跟在from之后。
注释： – 注释一行； /* */ 注释多行；

2.2 算术运算符和比较运算符

运算符就是对其两边的列或者值进行运算（计算或者比较大小）的符号。
使用算术运算符可以进行四则运算（+ - * /）。

那么这样来看的话，select语句中的from子句并不是必不可少的，看下面：

可以通过这种方法简单实现一个计算器，但是在Oracle中不能省略from子句。
括号可以提升运算的优先顺序。

包含NULL的运算，其结果也是NULL。（如5+NULL 输出还是NULL）

比较运算符可以用来比较列或者值是否相等，还可以用来比较大小。
可分为以下6种（= , <>不等于，>=, > ,<=, <），这些运算符可以对字符、数字、日期等几乎所有数据类型的列和值进行比较。此外where子句的表达式中还可以使用计算表达式：

PS:字符串比较时需要注意字符串类型的数据按照字典序进行排序，与数字大小顺序不同。
如下：

SQL 不识别”=NULL“和”<>NULL“的判断语句，判断是否为NULL时，需要使用IS NULL 或者IS NOT NULL运算符。

2.3、逻辑运算符

NOT 用来否定某一条件。
AND 和 OR 用来对多个查询条件进行组合。注意，AND 运算符优先于OR运算符，因此如果需要OR优先于AND执行，需要借助于（）。

3、聚合与排序

3.1、对表进行聚合查询

COUNT：计算表中的记录数（行数）
SUM：计算表中数值列中数据的合计值
AVG：计算表中数值列中数据的合平均值
MAX：求出表中任意列中数据的最大值
MIN：求出表中任意列中数据的最小值
如上所述，用于汇总的函数称为聚合函数，所谓聚合就是将多行汇总成一行。

select count(*) from table; 计算表中全部数据的行数。（包括NULL）
select count(<列名>) from table;计算列名中除NULL之外的数据行数。（不包括NULL）

使用聚合函数删除重复值（关键字DISTINCT）
select count(distinct <列名>) from table；

3.2、对表进行分组

GROUP BY子句语法如下：

SELECT <列名1>，<列名2>，....
FROM <表名>
GROUP BY <列名1>，<列名2>...

3.3、为聚合结果指定条件

where 子句用来指定数据行的条件，having子句用来指定分组的条件。
语法如下:

SELECT <列名1>，<列名2>，....
FROM <表名>
GROUP BY <列名1>，<列名2>...
HAVING <分组对应的条件>；

eg:从按商品种类进行分组后的结果中，取出”包含的数据行数为2的行“的组

SELECT product_type, COUNT(*)
  FROM Product
 GROUP BY product_type
HAVING COUNT(*) = 2;

3.4、对查询结果进行排序

使用order by 子句对查询结果进行排序时，可以使用ASC关键字升序排序，也可以使用DESC降序排序（不加ASC或者DESC时默认升序排序）。语法如下：

SELECT <列名1>，<列名2>，....
FROM <表名>
ORDER BY <排序基准1>，<排序基准2>...

各子句关系：
SELECT子句>FROM子句>WHERE子句>GROUP BY 子句>HAVING 子句>ORDER BY 子句
ORDER BY子句中可以使用的列，可以是select子句中未包含的列，也可以是聚合函数count(*)

4、数据更新

4.1、数据的插入

INSERT INTO <表名> （列1，列2，...） VALUES (值1，值2，...)；#列数与值数需保持一致。

通常，执行依次INSERT会插入一行数据，但是也可以一次insert插入多行数据，只需将多条values子句通过逗号隔开即可。这种方法适用于Oracle之外的所有RDBMS。
Oracle的多行insert操作方法如下：

INSERT ALL INTO <表名> VALUES (第一行数据)
                      INTO <表名> VALUES (第二行数据)
                      ...
 SELECT * FROM DUAL;

DUAL是Oracle安装时自带的一种临时表，没有实际意义。
INSERT插入时，也可省略列清单，直接插入值。
也可以从其他表中选择数据复制插入到表中：

INSERT INTO <表2> （列1，列2，...）
SELECT (列1，列2，...) from <表1>

4.2、数据的删除

DROP TABLE <表名>； 删除整个表
DELETE FROM <表名>； 删除表中的数据，delete删除对象是行。加where条件可以删除部分行。
TRUNCATE <表名 >； 只能删除表中全部数据，不能使用where子句来指定条件删除。

4.3、数据的更新

UPDATE  <表名>
  SET <列1> = <表达式1>，<列2> = <表达式2>，...；

将更新对象和更新后的值都记录在SET子句中。
也可以使用where子句指定更新的行。

4.4、事务

事务就是需要在同一个处理单元中执行的一系列更新处理的集合。事务的语法如下：

事务开始语句；
   DML语句1；
   DML语句2；
   ...
事务结束语句（COMMIT 或者ROLLBACK）；

使用事务开始和结束语句，将一系列DML语句（INSERT/UPDATA/DELETE）括起来，就实现了一个事务处理。
事务的开始语句是由各个RDBMS来定义的，比如SQL Server和PostgreSQL中采用：BEGIN TRANSACTION
MySQL中采用：START TRANSACTION
而Oracle和DB2没有对应的开始语句。
那么事务是什么时候开始的呢？
通常有以下两种情况：
a)每条语句就是一个事务（自动提交模式）。
b)直到用户执行COMMIT或者ROLLBACK为止算作一个事务。

COMMIT提交处理，一旦提交就无法恢复到事务开始前的状态了。
ROLLBACK 取消处理，一旦回滚，数据库会恢复到事务开始前的状态。

DBMS中事务有四个特性：
1）原子性（Atomicity）：原子性实现完整性管理子系统，当事务结束时，所包含的更新要么完全不执行，要么全部执行。
2) 一致性(Consistency)：一致性实现并发控制子系统，事务中包含的处理要满足数据库提前设置的约束。
3）隔离性（Isolation)：隔离性实现安全控制管理子系统，不同事务之间互不干扰。
4）持久性（Durability）：持久性实现恢复管理子系统。
以上统称ACID特性。

5、复杂查询

5.1、视图

试图保存的是SELECT语句，其本身并不存储数据。
优点：1、节省存储设备的容量
2、将select语句保存成视图，方便调用。
语法如下：

CREATE VIEW 试图名称（<试图列名1>，<试图列名2>，...）
AS 
<SELECT 语句>

SELECT语句中列的排列和视图中列的排列顺序相同。
视图的两个限制：
1、视图中不能使用ORDER BY子句，因为视图和表一样，数据行都是没有顺序的。
2、不能对视图进行更新
删除视图：DROP VIEW <视图名>；

5.2、子查询

子查询就是一次性视图。

SELECT <列名1>，<列名2>，...
FROM(
SELECT <列名1>，<列名2>，...
FROM TABLE
) AS TABLE2;

先执行FROM（）子句内的查询，然后才会执行外层的SELECT语句。
子查询必须设置名称，使用AS关键字，该关键字有时也可省略。

5.3、关联子查询

比如选取商品售价大于平均售价的商品：

SELECT product_type, product_name, sale_price
  FROM Product P1
 WHERE sale_price > (SELECT AVG(sale_price)
                         FROM Product P2
                        WHERE P1.product_type = P2.product_type
                        GROUP BY product_type);

6、函数、谓词、CASE表达式

6.1、各种函数

6.2 谓词

谓词就是返回值为真值的函数。主要包括：LIKE 、BETWEEN、IS NULL、IS NOT NULL、IN、EXISTS
LIKE模糊查询，有三种使用方法（前方一致、中间一致、后方一致）
前方一致：LIKE ‘ddd%’，%代表0字符以上的任意字符串
中间一致：LIKE ‘%ddd%’
后方一致：LIKE ‘%ddd’
_ 代表任意一个字符；_ _ 任意两个字符；
BETWEEN … AND … 范围查询：
EXISTS通常可以用IN 或NOT IN来代替，所以不常用，它左侧没有参数，只需要在右侧书写一个参数，通常是一个子查询。

CASE 子查询

CASE WHEN <求值表达式> THEN <表达式>
           WHEN <求值表达式> THEN <表达式>
           WHEN <求值表达式> THEN <表达式>
           ...
           ELSE <表达式>
END

如果求值表达式结果为真，就返回THEN子句中的表达式。

eg:将商品分配给对应的类别：

SELECT product_name,
       CASE WHEN product_type = '衣服'    THEN 'A：' || product_type
            WHEN product_type = '办公用品' THEN 'B：' || product_type
            WHEN product_type = '厨房用具' THEN 'C：' || product_type
            ELSE NULL
       END AS abc_product_type i
  FROM Product;

执行结果：

7、集合运算

集合运算的注意事项：
a) 运算对象的记录的列数必须相同
b) 运算对象的记录的列的 类型必须一致
c) 可以使用任何SELECT 语句，但是ORDER BY语句只能在最后使用一次

7.1、表的加减法

1、表的加法——UNION

SELECT <列名1>，<列名2>，...
FROM 表1
UNION
SELECT <列名1>，<列名2>，...
FROM 表2

使用UNION 运算符会去掉重复的记录。
包含重复行的集合运算，在UNION后添加关键字ALL，变成UNION ALL即可。
2、选取表中重复部分——INTERSECT（交集）
3、记录的减法——EXCEPT（差集）
需注意减数和被减数的位置。另外Oracle中使用MINUS代替EXCEPT，MySQL中不支持EXCEPT。

7.2、联结

联结（JOIN）简单来说就是将其他表中的列添加过来进行“添加列”的运算。
1、内联结（INNER JOIN）

SELECT <表别名1>.<列名1>，<表别名2>.<列名2>，....
FROM  表1 as 表别名1   INNER JOIN   表2 as 表别名2
ON     表别名1.<列名1>=<表别名2>.<列名2>

注：Oracle 中不使用AS ；ON 必须在from和where之间； select指定的列必须带上表名
内联结选取的是两张表中共有的数据
2、外联结（OUTER JOIN）
外联结与内联结的区别：
只要数据存在于某一张表中，就能够读取出来。
外联结使用LEFT 和RIGHT关键字定义主表。
使用LEFT时FROM子句写在左侧的表是主表，使用RIGHT时右侧的表是主表。
3、交叉联结（CROSS JOIN）——笛卡儿积
交叉联结是对两张表进行交叉组合，通常是两张表中行数的乘积。

3、SQL高级处理

3.1窗口函数

窗口函数也称OLAP（OnLine Analytical Processing）函数。目前MySQL还不支持这一功能。
语法如下：

<窗口函数> OVER ([PARTITION BY <列清单>]
                     ORDER BY <排序用列清单>)

注：[]中的内容可以省略
窗口函数大体分为以下两种：
a) 能够作为窗口函数的聚合函数（SUM、AVG、COUNT、MAX、MIN）
b) RANK、DENSE_RANK、ROW_NUMBER等专用窗口函数
eg:

SELECT product_name, product_type, sale_price,
       RANK () OVER (PARTITION BY product_type
                         ORDER BY sale_price) AS ranking
  FROM Product;

以上SQL语句实现：根据不用的商品种类，按照销售单价从高到低排序，结果如下：

PARTITION BY 能够设定排序的对象范围；
ORDER BY 指定哪一列按照何种顺序进行排序。默认升序，DESC降序。
通过PARTITION BY 分组的记录集合称为窗口。这也是“窗口函数”名称的由来。

三种专用窗口函数的区别：

a) RANK函数：计算排序时如果存在相同位次的记录，则会跳过之后的位次
eg: 有 3条记录排在第一位时：1，1，1，4…
b) DENSE_RANK 函数:即使存在相同的排序，也不跳过之后的位次：
eg: 有 3条记录排在第一位时：1，1，1，2…
c) 赋予唯一的连续位次
eg: 有 3条记录排在第一位时：1，2，3，4…

作为窗口函数使用的聚合函数：

计算移动平均

使用“ROWS 2 PRECEDING” 将框架定义到“截至到之前2行”。
将PRECEDING替换成FOLLWING 就是之后~行。
“ ROWS BETWEEN 1 PRECEDING AND 1 FOLLWING "当前记录及前后1行。

8.2、GROUPING运算符

使用GROUP BY 子句无法同时得到小计和合计。除非使用以下方法：

这样不仅繁琐，而且内部成本也高。如果想同时得到，可以使用GROUPING运算符。
GROUPING运算符包含以下三种：ROLLUP、CUBE、GROUPING SETS

ROLLUP——同时得到小计和合计

最后一行相当于没有使用分组得到的合计值。默认使用NULL作为聚合键。

以上相当于对三种不同模式进行聚合：
a) GROUP BY() 得到最后的汇总
b) GROUP BY(product_type) 得到根据不同商品类型的汇总
c) GROUP BY(product_type, regist_date)

GROUPING函数——让NULL更加容易分辨

之前虽然提到超级分组的聚合键为NULL，但是怎么分辨一张表中哪些是聚合分组的NULL，哪些是真实的NULL呢。这里可以使用GROUPING函数

SELECT GROUPING(product_type) AS product_type, 
  GROUPING(regist_date) AS regist_date, SUM(sale_price) AS sum_price
  FROM Product
 GROUP BY ROLLUP(product_type, regist_date);

上图中红色框内1代表超级分组所产生的NULL。
那么知道怎么分辨超级分组的聚合键，我们就可以在键中插入适当的字符串了。

SELECT CASE WHEN GROUPING(product_type) = 1 
            THEN '商品种类 合计' 
            ELSE product_type END AS product_type,
       CASE WHEN GROUPING(regist_date) = 1 
            THEN '登记日期 合计' 
            ELSE CAST(regist_date AS VARCHAR(16)) END AS regist_date,
       SUM(sale_price) AS sum_price
  FROM Product
 GROUP BY ROLLUP(product_type, regist_date);

CUBE——用数据搭积木

将上面SQL语句中的ROLLUP换成CUBE，结果如下：

SELECT CASE WHEN GROUPING(product_type) = 1 
           THEN '商品种类 合计'
           ELSE product_type END AS product_type,
      CASE WHEN GROUPING(regist_date) = 1 
           THEN '登记日期 合计'
           ELSE CAST(regist_date AS VARCHAR(16)) END AS regist_date,
      SUM(sale_price) AS sum_price
 FROM Product
GROUP BY CUBE(product_type, regist_date);

它与上面SQL语句得到的结果区别在于，这里添加了把 regist_date 作为聚合键所得到的汇总结果。

GROUPING SETS——从之前CUBE的结果中取出每个列名各自作为聚合键的结果。

---

---

OVER

---

---