SQL 基础

Posted 2023-10-26 Updated 2023-10- 28

By Shichen Peng

69~88 min read

SQL 基础

2023年10月19日

摘要

本文将以 MariaDB 为平台（基于 MySQL）总结一些 SQL 的基本语法和用法，以及一些相应的理解，以供未来快速查找。

背景

由于最近开始养猫，想在空余时间开发一款记录猫咪成长的 App，网上的 App 要么全是广告，要么怕哪天倒闭跑路，数据还是留在自己这里放心，索性自己开发一个。又因为自己平时 iOS 和 Android 的设备都有在使用，跨平台数据同步比较麻烦，因此决定使用前后端分离，利用上我的服务器，在服务器上部署一个 MariaDB，然后数据扔进去，手机只负责存取数据并显示即可。之所以使用 MariaDB 而不是简单的 Pandas 操作 CSV，是因为记录的数据在未来可能会有各种各样的拉取需求，虽然 SQL 前期部署和架构搭建会复杂一些，但长远来看明显更加灵活。

这种工具性的语言用一次就很久不会再用，之前学习过，用 Notability 记过手写版的笔记，但是不便查阅。本次将复习一遍并总结一些用法。

读取数据

检索数据

从表中检索一个或多个列。

检索一列

SELECT prod_name
FROM Products

检索多列

SELECT prod_id, prod_name, prod_price
FROM Products;

检索所有列

SELECT *
FROM Products;

检索不同的值

SELECT DISTINCT vend_id
FROM Products;

如果涉及多列，则得到不重复的组合。

检索前几个值

SELECT prod_name
FROM Products
LIMIT 5;

检索第几个开始的前几个值

SELECT prod_name
FROM Products
LIMIT 5 OFFSET 5;

排序数据

根据需要排序检索出的数据。

单列正向排序

SELECT prod_name
FROM Products
ORDER BY prod_name;

ORDER 语句必须在末尾。

多列正向排序

SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price, prod_name;

先出现的优先级高。

按选中的列位置排序

SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY 2, 3;

倒序排序

SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price DESC;

混合顺序排序

SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price DESC, prod_name;

过滤数据

使用 SELECT 语句的 WHERE 子句指定搜索条件。

操作符有：

操作符	含义
=	等于
<>	不等于
!=	不等于
<	小于
<=	小于等于
!<	不小于
>	大于
>=	大于等于
!>	不大于
BETWEEN	在两个指定值之间
IS NULL	是空值

过滤单个条件

SELECT prod_name, prod_price
FROM Products
WHERE prod_price < 10;

范围过滤

SELECT prod_name, prod_price
FROM Products
WHERE prod_price BETWEEN 5 AND 10;

空值检查

SELECT prod_name
FROM Products
WHERE prod_price IS NULL;

多条件交集

SELECT prod_id, prod_price, prod_name
FROM Products
WHERE vend_id = 'DLL01'
  AND prod_price <= 4;

多条件并集

SELECT prod_id, prod_price, prod_name
FROM Products
WHERE vend_id = 'DLL01'
   OR vend_id = 'BRS01';

逻辑优先级

AND 比 OR 优先级高，可以用括号。

SELECT prod_name, prod_price
FROM Products
WHERE (vend_id = 'DLL01' OR vend_id = 'BRS01')
  AND prod_price >= 10;

存在性过滤

SELECT prod_name, prod_price
FROM Products
WHERE vend_id IN ('DLL01', 'BRS01');

逻辑取反

SELECT prod_name
FROM Products
WHERE NOT vend_id = 'DLL01';

通配符过滤

什么是通配符、如何使用通配符，以及怎样使用 LIKE 操作符进行通配搜索。

任意个字符通配

SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE 'Fish%';

单个字符匹配

SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE '__ inch teddy bear';

计算字段

何创建计算字段，以及如何从应用程序中使用别名引用它们。

字段拼接

SELECT Concat(vend_name, ' (', vend_country, ')')
FROM Vendors
ORDER BY vend_name;

拼接时去掉空格

SELECT Concat(RTRIM(vend_name), ' (', RTRIM(vend_country), ')')
FROM Vendors
ORDER BY vend_name;

除了 RTRIM() 去掉右边空格，还有 LTRIM() 去掉左边空格和 TRIM() 去掉两边空格。

含 `NULL` 拼接

如果拼接元素包含 NULL，则 CONCAT 函数的结果也会是 NULL。如果遇到包含 NULL 的拼接，可以用 CONCAT_WS 或是 IFNULL。

SELECT Concat_WS('-', vend_name, vend_country)
FROM Vendors
ORDER BY vend_name;

-- OR

SELECT Concat(IFNULL(vend_name, ''), vend_country)
FROM Vendors
ORDER BY vend_name;

计算字段别名

SELECT Concat(RTrim(vend_name), ' (',
              RTrim(vend_country), ')') AS vend_title
FROM Vendors
ORDER BY vend_name;

数学计算

SELECT prod_id,
       quantity,
       item_price,
       quantity * item_price AS expanded_price
FROM OrderItems
WHERE order_num = 20008;

函数

字符串相关

函数	用途
`LEFT()`	左边 n 个字符
`RIGHT()`	右边 n 个字符
`LOWER()`	转换成小写
`UPPER()`	转换成大写
`LTRIM()`	去掉左边空格
`RTRIM()`	去掉右边空格
`TRIM()`	去掉两边空格
`SUBSTRING()`	提取字符串组成部分
`LENGTH()`	获取字符串长度
`SOUNDEX()`	返回字符串的 SOUNDEX 值

数值相关

函数	用途
`ABS()`	绝对值
`COS()`	余弦函数
`SIN()`	正弦函数
`TAN()`	正切函数
`SQRT()`	平方根
`EXP()`	指数
`PI()`	圆周率

时间相关

获取日期

SELECT CURDATE();

获取时间

SELECT CURTIME();

获取日期时间

SELECT NOW();

日期转字符串

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');

字符串转日期

SELECT STR_TO_DATE('2023-10-24 21:38:57', '%Y-%m-%d %H:%i:%s');

筛选时间段

SELECT order_num
FROM Orders
WHERE EXTRACT(year FROM order_date) = 2020;

SELECT order_num
FROM Orders
WHERE YEAR(order_date) = 2020;

逻辑相关

逻辑判断

SELECT COUNT(IF(gender = 'male', 1, NULL)) AS man_number,
       SUM(IF(gender = 'female', 1, 0))    AS woman_number
FROM Students

分支判断

SELECT name,
       score,
       CASE
           WHEN score >= 90 THEN '优秀'
           WHEN score >= 80 THEN '良好'
           WHEN score >= 60 THEN '及格'
           ELSE '不及格'
           END
           AS level
FROM Students

数据汇总

SQL 的聚集函数，利用它们汇总表的数据。

普通函数是针对列中的每一个值的，每个值得到一个值。

聚集函数是针对整个列的，一个列整体得到一个值。

平均数

SELECT AVG(prod_price) AS avg_price
FROM Products
WHERE vend_id = 'DLL01';

求和

SELECT SUM(item_price*quantity) AS total_price
FROM OrderItems
WHERE order_num = 20005;

条目数计数

SELECT COUNT(*) AS num_cust
FROM Customers;

最大值

SELECT MAX(prod_price) AS max_price
FROM Products;

最小值

SELECT MIN(prod_price) AS min_price
FROM Products;

去重聚集

SELECT AVG(DISTINCT prod_price) AS avg_price
FROM Products
WHERE vend_id = 'DLL01';

组合聚集

SELECT COUNT(*)        AS num_items,
       MIN(prod_price) AS price_min,
       MAX(prod_price) AS price_max,
       AVG(prod_price) AS price_avg
FROM Products;

数据分组统计

分组数据，以便汇总表内容的子集。

单列分组

选择一列的不同值作为分组依据，将每一组的其他列进行统计得到一个值。

一般都要搭配聚集函数进行组内整合。

SELECT vend_id, COUNT(*) AS num_prods
FROM Products
GROUP BY vend_id;

多列分组

按照先后顺序分大组小组。

SELECT vend_id, some_other, COUNT(*) AS num_prods
FROM Products
GROUP BY vend_id, some_other;

多列分组序号表示

可以用 SELECT 的第几个来表示用哪几个来分组。

SELECT vend_id, some_other, COUNT(*) AS num_prods
FROM Products
GROUP BY 1, 2;

分组结果过滤

在分组过后得到的表格可以再次过滤。

SELECT cust_id, COUNT(*) AS orders
FROM Orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;

条目过滤和分组过滤搭配

WHERE 先对整体进行过滤，然后 GROUP BY 分组，分组统计后的结果再被 HAVING 过滤。

SELECT vend_id, COUNT(*) AS num_prods
FROM Products
WHERE prod_price >= 4
GROUP BY vend_id
HAVING COUNT(*) >= 2;

单次查询顺序与总结：

子句说明是否必须

SELECT 要返回的列或表达式是

FROM 从中检索数据的表仅在从表选择数据时使用

WHERE 行级过滤否

GROUP BY 分组依据仅在按组计算聚集时使用

HAVING 组级过滤否

ORDER BY 输出排序顺序否

子句	说明	是否必须
`SELECT`	要返回的列或表达式	是
`FROM`	从中检索数据的表	仅在从表选择数据时使用
`WHERE`	行级过滤	否
`GROUP BY`	分组依据	仅在按组计算聚集时使用
`HAVING`	组级过滤	否
`ORDER BY`	输出排序顺序	否

子查询

什么是子查询，如何使用它们。

主要是跨表查询数据。

条件子查询

先进行内部查找，找到的值作为筛选条件供外部查询使用。

SELECT cust_id
FROM Orders
WHERE order_num IN (SELECT order_num
                    FROM OrderItems
                    WHERE prod_id = 'RGAN01');

作为计算字段的子查询

根据当前表的某些条件，去查找另一个表满足条件部分的聚集整合值。

如果当前表的条件有重复，则计算字段会计算多次，因为有好几个行都满足这个条件。

涉及下个章节的联结。

SELECT cust_name,
       cust_state,
       (SELECT COUNT(*)
        FROM Orders
        WHERE Orders.cust_id = Customers.cust_id) AS orders
FROM Customers
ORDER BY cust_name;

分类讨论：

主查询如果有重复的关联选择，则自查询会重复多个相同的，所以一般用主查询表的 key。
自查询如果有重复的关联选择，这是常规操作，因为要统计他们的某个属性得到一个值。

联结表

多个表信息联结，避免重复数据，可扩展性好。

笛卡尔全排列组合

不给定 WHERE 条件联结两个表，得到两个表的所有条目的全排列组合。

SELECT vend_name, prod_name, prod_price
FROM Vendors,
     Products;

但是返回的结果毫无关联，会存在很多毫不相关的数据出现在同一排。

等值联结

根据两个的共同属性来查找出合并表结果。

SELECT vend_name, prod_name, prod_price
FROM Vendors,
     Products
WHERE Vendors.vend_id = Products.vend_id;

联结属性至少要在其中一个表里是 key，这样才能诞生一条查找链路：

值（表 1） -> 对应关联值（表 1） -> 对应关联主键（表 2） -> 值（表 2）

否则如果“对应关联（表 2）”不是主键而是普通值，则可能查找出多个“值（表 2）”。此时，如果非要统计，则可以像上一章节的作为计算字段的子查询计算一个聚合整合值。

不太建议使用这个语法，不清不楚的，建议使用后文的内联结。

内联结

上文的等值联结其实就是隐式的内联结。

在某个关联条件满足处将两个表联结在一起，联结处在其中一个表必须是主键。

SELECT vend_name, prod_name, prod_price
FROM Vendors
         INNER JOIN Products ON Vendors.vend_id = Products.vend_id;

多表串联等值联结

多个表的内联结依然可以串联起来。

SELECT prod_name, vend_name, prod_price, quantity
FROM OrderItems,
     Products,
     Vendors
WHERE Products.vend_id = Vendors.vend_id
  AND OrderItems.prod_id = Products.prod_id
  AND OrderItems.order_num = 20007;

多表串联内联结

当然，用 INNER JOIN 的语法进行多表串联内联结是更好的。

SELECT prod_name, vend_name, prod_price, quantity
FROM OrderItems
         INNER JOIN Products ON OrderItems.prod_id = Products.prod_id
         INNER JOIN Vendors ON Products.vend_id = Vendors.vend_id
WHERE OrderItems.order_num = 20007;

多表内联结的自查询实现

有些最终输出只涉及最外层表的内联结可以写成自查询，但很低效。以下两个是等效的。

SELECT cust_name, cust_contact
FROM Customers
WHERE cust_id IN (SELECT cust_id
                  FROM Orders
                  WHERE order_num IN (SELECT order_num
                                      FROM OrderItems
                                      WHERE prod_id = 'RGAN01'));

SELECT cust_name, cust_contact
FROM Customers,
     Orders,
     OrderItems
WHERE Customers.cust_id = Orders.cust_id
  AND OrderItems.order_num = Orders.order_num
  AND prod_id = 'RGAN01';

高级联结

如何使用表别名，如何对被联结的表使用聚集函数。

表别名

前文讲过对计算字段起别名，其实对表也可以取别名。

SELECT cust_name, cust_contact
FROM Customers AS C,
     Orders AS O,
     OrderItems AS OI
WHERE C.cust_id = O.cust_id
  AND OI.order_num = O.order_num
  AND prod_id = 'RGAN01';

自联结

联结条件就是通过一个值去找另一个对应关联值的过程，如果这个过程发生在表内，则很自然就是自联结。

SELECT c1.cust_id, c1.cust_name, c1.cust_contact
FROM Customers AS c1,
     Customers AS c2
WHERE c1.cust_name = c2.cust_name
  AND c2.cust_contact = 'Jim Jones';

整个过程就是：在 c2 中通过联系人找到这个公司，然后将这个公司名对应至 c1 并显示所有。

自联结过程由于只涉及一个表的输出，因此也可以用自联结实现。

SELECT cust_id, cust_name, cust_contact
FROM Customers
WHERE cust_name = (SELECT cust_name
                   FROM Customers
                   WHERE cust_contact = 'Jim Jones');

注意，以上操作和下面的代码逻辑上截然不同，完全不一样。

SELECT cust_id, cust_name, cust_contact
FROM Customers
WHERE cust_contact = 'Jim Jones';

这个只是通过联系人找到这个公司，并没有继续往下查。

自联结就是从一个条目查一个根，再从根展开。

自然联结

因为但凡存在联结就会存在两个表中有同一列的情况，普通的联结会让两个列被重复的检索出来，自然联结通过显示的指定联结进来的其他表只选中哪几列（规避掉重复列）来避免重复。

SELECT C.*, O.order_num, O.order_date, OI.prod_id, OI.quantity, OI.item_price
FROM Customers AS C,
     Orders AS O,
     OrderItems AS OI
WHERE C.cust_id = O.cust_id
  AND OI.order_num = O.order_num
  AND prod_id = 'RGAN01';

外联结

内联结是取交集，外联结是取两个圈中的一整个圈，包括了交集部分和其中一个表没有满足条件的部分。

SELECT Customers.cust_id, Orders.order_num
FROM Customers
         LEFT OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id;

SELECT Customers.cust_id, Orders.order_num
FROM Customers
         RIGHT OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id;

总体来说，就是满足就正常联结，不满足就把联结进来的表的部分填 null。

分组聚集联结

联结得到的表，如果某一列（例如日期，ID）一个值有多个条目，则同样可以分组使用聚集函数统计。

对于一个联结得到的拥有顾客 ID 和订单号的表：

SELECT Customers.cust_id, Orders.order_num
FROM Customers
         INNER JOIN Orders ON Customers.cust_id = Orders.cust_id;

可以统计每个顾客有几个订单：

SELECT Customers.cust_id, COUNT(Orders.order_num) AS num_ord
FROM Customers
         INNER JOIN Orders ON Customers.cust_id = Orders.cust_id
GROUP BY Customers.cust_id;

当然也可以用外联结将没有订单的顾客也显示出来：

SELECT Customers.cust_id, COUNT(Orders.order_num) AS num_ord
FROM Customers
         LEFT OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id
GROUP BY Customers.cust_id;

组合查询

如何利用 UNION 操作符将多条 SELECT 语句组合成一个结果集。

组合多条查询

如果多个查询输出格式相同，则可以对他们进行组合。

以两个独立的查询为例：

SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL', 'IN', 'MI');

SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_name = 'Fun4All';

可以使用 OR 进行并集：

SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL', 'IN', 'MI')
   OR cust_name = 'Fun4All';

也可以使用 UNION 组合查询：

SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL', 'IN', 'MI')
UNION
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_name = 'Fun4All';

类型和列名可以不相同，类型会隐式转换，但列名会以第一个查询为准，想当有歧义。

组合查询不去重

直接使用 UNION 会默认进行去重，如果不想去重，则使用 UNION ALL。

SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL', 'IN', 'MI')
UNION ALL
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_name = 'Fun4All';

组合查询当作新表

使用组合查询后的结果可以当作一个新表，类似子查询。

SELECT excrete_timestamp, type, excrete_status
FROM (SELECT urine_timestamp as excrete_timestamp, 'urine' AS 'type', urine_status AS excrete_status
      FROM UrineRecords
      UNION ALL
      SELECT stool_timestamp as excrete_timestamp, 'stool' AS 'type', stool_status AS excrete_status
      FROM StoolRecords) AS ExcreteRecords
WHERE excrete_status != 'normal';

写入数据

插入数据

按次序插入完整行

可以直接按照次序提供每一列的数据然后插入，缺一不可，主键不可重复。

INSERT INTO Customers
VALUES (1000000006,
        'Toy',
        '123 Any Street',
        'New York',
        'NY',
        '11111',
        'USA',
        NULL,
        NULL);

最好不要用这种办法，应该像下文一样给出每一列的名字。

按键值对插入完整行

如果提供每一列的名称，则可以用任意顺序提供值。

INSERT INTO Customers(cust_id,
                      cust_name,
                      cust_address,
                      cust_city,
                      cust_state,
                      cust_zip,
                      cust_country,
                      cust_contact,
                      cust_email)
VALUES (1000000007,
        'Toy Land',
        '123 Any Street',
        'New York',
        'NY',
        '11111',
        'USA',
        NULL,
        NULL);

按键值对插入部分行

一旦给出列名，就可以只插入行中的部分数据。

INSERT INTO Customers(cust_id,
                      cust_name,
                      cust_address,
                      cust_city,
                      cust_state,
                      cust_zip,
                      cust_country)
VALUES(1000000006,
       'Toy Land',
       '123 Any Street',
       'New York',
       'NY',
       '11111',
       'USA');

插入检索出的数据

插入数据的来源不一定要是代码中给出的，也可以是别处检索出来的。

INSERT INTO Customers(cust_id,
                      cust_contact,
                      cust_email,
                      cust_name,
                      cust_address,
                      cust_city,
                      cust_state,
                      cust_zip,
                      cust_country)
SELECT cust_id,
       cust_contact,
       cust_email,
       cust_name,
       cust_address,
       cust_city,
       cust_state,
       cust_zip,
       cust_country
FROM CustNew;

当然，也可以在里面用 WHERE 来进行一定的筛选。

只有 INSERT 和 SELECT 一起搭配的时候才能一次插入多行。

复制表

从一个表的 SELECT 结果创建一个新的表。

CREATE TABLE CustCopy AS SELECT * FROM Customers;

更新和删除数据

利用 UPDATE 和 DELETE 语句操作表数据。

⚠️警告！一定要提供 WHERE 进行筛选！否则将直接修改所有行！修改前一定要先用 SELECT 进行测试！

更新一个数据

只要提供要更新的表，列名和新值，以及定位行的筛选条件即可。

UPDATE Customers
SET cust_email = 'kim@thetoystore.com'
WHERE cust_id = 1000000005;

可以使用子查询。

更新多个数据

只需要一个 SET，多个数据用逗号隔开即可。

UPDATE Customers
SET cust_contact = 'Sam Roberts',
    cust_email   = 'sam@toyland.com'
WHERE cust_id = 1000000006;

删除一个值

将要删除的值设置为 NULL 即可。

UPDATE Customers
SET cust_email = NULL
WHERE cust_id = 1000000005;

删除一列

将一个列名不经过 WHERE 筛选直接设置为 NULL 即可。

UPDATE Customers
SET cust_email = NULL

删除一行

使用 DELETE 语句即可。

DELETE FROM Customers
WHERE cust_id = 1000000007;

删除所有行

不提供 WHERE 进行筛选则会删除所有行。

DELETE FROM Customers;

此操作不删除表，只会删除表中的数据，留下一个空表。

创建和操纵表

创建、更改和删除表。

有几个准则：

理想情况下，不要在表中包含数据时对其进行更新。应该在表的设计过程中充分考虑未来可能的需求，避免今后对表的结构做大改动。

所有的 DBMS 都允许给现有的表增加列，不过对所增加列的数据类型（以及 NULL 和 DEFAULT 的使用）有所限制。

许多 DBMS 不允许删除或更改表中的列。

多数 DBMS 允许重新命名表中的列。

许多 DBMS 限制对已经填有数据的列进行更改，对未填有数据的列几乎没有限制。

创建表

给出表名，列名，列数据类型和是否允许 NULL 即可。

CREATE TABLE Products
(
    prod_id    char(10)      NOT NULL,
    vend_id    char(10)      NOT NULL,
    prod_name  char(255)     NOT NULL,
    prod_price decimal(8, 2) NOT NULL,
    prod_desc  text          NULL
);

指定默认值

在创建表时使用 DEFAULT 指定即可。

CREATE TABLE OrderItems
(
    order_num  int           NOT NULL,
    order_item int           NOT NULL,
    prod_id    char(10)      NOT NULL,
    quantity   int           NOT NULL DEFAULT 1,
    item_price decimal(8, 2) NOT NULL
);

默认日期也是一个尤为常见的场景。

CREATE TABLE Notes
(
    date date     NOT NULL DEFAULT CURRENT_DATE,
    note char(10) NOT NULL
);

其中时间类型和默认值为：

类型	默认值
`date`	`CURRENT_DATE`/`CURDATE()`
`time`	`CURRENT_TIME`/`CURTIME()`
`datetime`	`CURRENT_TIMESTAMP`/`NOW()`

默认值中的关键字其实就是后面几个函数的别名。

添加列

给出表名和新列信息即可。

ALTER TABLE Vendors
ADD vend_phone CHAR(20);

删除列

给出表名和要删除的列信息即可。

ALTER TABLE Vendors
DROP COLUMN vend_phone;

复杂的表结构更改一般需要手动删除过程，它涉及以下步骤:

用新的列布局创建一个新表;
使用 INSERT SELECT 语句从旧表复制数据到新表。有必要的话，可以使用转换函数和计算字段;
检验包含所需数据的新表;
重命名旧表（如果确定，可以删除它）;
用旧表原来的名字重命名新表;
根据需要，重新创建触发器、存储过程、索引和外键。

删除表

没有确认步骤也不可撤销，请格外小心。

DROP TABLE CustCopy;

重命名表

每个 DBMS 都不太一样，以 MySQL 为例。

RENAME TABLE MyClass TO YouClass;

视图

什么是视图，它们怎样工作，何时使用它们，如何利用视图简化前几课中执行的某些 SQL 操作。

创建视图

视图可以生成一张本来需要复杂联结才能得到的表，类似于一个查询函数。只需要用提供视图名称和生成这张表的查询方式即可。

CREATE VIEW CustomerProducts AS
SELECT cust_name, cust_contact, prod_id
FROM Customers
         INNER JOIN Orders ON Customers.cust_id = Orders.cust_id
         INNER JOIN OrderItems ON Orders.order_num = OrderItems.order_num;

之后只需要像一张表一样使用即可。

SELECT cust_name, cust_contact
FROM ProductCustomers
WHERE prod_id = 'RGAN01';

使用视图格式化数据

可以在视图中直接格式化数据，最好提供新的列名。

CREATE VIEW VendorLocations AS
SELECT CONCAT(RTRIM(vend_name), ' (', RTRIM(vend_country), ')')
           AS vend_title
FROM Vendors;

使用视图过滤数据

创建视图时可以直接完成一定的是数据筛选。

CREATE VIEW CustomerEMailList AS
SELECT cust_id, cust_name, cust_email
FROM Customers
WHERE cust_email IS NOT NULL;

视图中使用计算字段

可以在视图中直接完成一定的计算统计。

CREATE VIEW OrderItemsExpanded AS
SELECT order_num,
       prod_id,
       quantity,
       item_price,
       quantity * item_price AS expanded_price
FROM OrderItems

约束

管理如何插入或处理数据库数据的规则。

主键

可以在定义表的时候添加。

CREATE TABLE Vendors
(
    vend_id      char(10) NOT NULL PRIMARY KEY,
    vend_name    char(50) NOT NULL,
    vend_address char(50) NULL,
    vend_city    char(50) NULL,
    vend_state   char(5)  NULL,
    vend_zip     char(10) NULL,
    vend_country char(50) NULL
);

也可以创建好之后修改添加。

ALTER TABLE Vendors
    ADD CONSTRAINT PRIMARY KEY (vend_id);

外键

可以在表定义的时候添加。

CREATE TABLE Orders
(
    order_num  int      NOT NULL,
    order_date datetime NOT NULL,
    cust_id    char(10) NOT NULL REFERENCES Customers (cust_id)
);

也可以创建好之后修改添加。

ALTER TABLE Orders
    ADD CONSTRAINT
        FOREIGN KEY (cust_id) REFERENCES Customers (cust_id);

唯一约束

除了主键以外的列，也可以添加唯一性约束。

CREATE TABLE Employees
(
    id             int      NOT NULL,
    insurance_code int      NOT NULL UNIQUE,
    name           char(10) NOT NULL
);

也可以创建好之后修改添加。

ALTER TABLE Employees
    ADD CONSTRAINT UNIQUE (insurance_code);

值约束

对列中所储存的值也可以做一定的规则检查。

CREATE TABLE OrderItems
(
    order_num  int           NOT NULL,
    order_item int           NOT NULL,
    prod_id    char(10)      NOT NULL,
    quantity   int           NOT NULL CHECK (quantity > 0),
    item_price decimal(8, 2) NOT NULL
);

也可以创建好之后修改添加。

ADD CONSTRAINT CHECK (quantity > 0);

索引

可以在一个或多个列上定义索引，使 DBMS 保存其内容的一个排过序的列表。

有些需要注意的：

索引改善检索操作的性能，但降低了数据插入、修改和删除的性能。在执行这些操作时，DBMS 必须动态地更新索引。
索引数据可能要占用大量的存储空间。
并非所有数据都适合做索引。取值不多的数据（如州）不如具有更可能值的数据（如姓或名），能通过索引得到那么多的好处。
索引用于数据过滤和数据排序。如果你经常以某种特定的顺序排序数据，则该数据可能适合做索引。
可以在索引中定义多个列（例如，州加上城市）。这样的索引仅在以州加城市的顺序排序时有用。如果想按城市排序，则这种索引没有用处。

创建索引

给出索引名和需要加索引的列即可。

CREATE INDEX prod_name_ind
    ON Products (prod_name);

存储过程

什么是存储过程，为什么要使用存储过程，如何使用存储过程，以及创建和使用存储过程的基本语法。

每个 DBMS 差别很大，还没看

触发器

每个 DBMS 差别很大，还没看

教程, 知识与技术

License: CC BY 4.0

SQL 基础

摘要

背景

读取数据

检索数据

检索一列

检索多列

检索所有列

检索不同的值

检索前几个值

检索第几个开始的前几个值

排序数据

单列正向排序

多列正向排序

按选中的列位置排序

倒序排序

混合顺序排序

过滤数据

过滤单个条件

范围过滤

空值检查

多条件交集

多条件并集

逻辑优先级

存在性过滤

逻辑取反

通配符过滤

任意个字符通配

单个字符匹配

计算字段

字段拼接

拼接时去掉空格

含 NULL 拼接

计算字段别名

数学计算

函数

字符串相关

数值相关

时间相关

获取日期

获取时间

获取日期时间

日期转字符串

字符串转日期

筛选时间段

逻辑相关

逻辑判断

分支判断

数据汇总

平均数

求和

条目数计数

最大值

最小值

去重聚集

组合聚集

数据分组统计

单列分组

多列分组

多列分组序号表示

分组结果过滤

条目过滤和分组过滤搭配

子查询

条件子查询

作为计算字段的子查询

联结表

笛卡尔全排列组合

等值联结

内联结

多表串联等值联结

多表串联内联结

多表内联结的自查询实现

高级联结

表别名

自联结

自然联结

外联结

分组聚集联结

组合查询

组合多条查询

含 `NULL` 拼接