【SQL】MySQL基础1：对表的DQL，DML，DDL

原创已于 2025-04-02 08:25:41 修改 · 1.9k 阅读

39 ·

CC 4.0 BY-SA版权

文章标签：

#sql #mysql

于 2025-02-13 10:43:56 首次发布

SQL 专栏收录该内容

7 篇文章

订阅专栏

文章目录

1. 一些概念
- 1.1 数据库模式
- 1.2 SQL语言类型
2. 查询
3. 连接
4. 组合查询
- 4.1 case1：基本用法
- 4.2 case2：为何要使用UNION
5. 数据插入
- 5.1 直接插入数据
- 5.2 插入检索的数据
6. 更新和删除数据
7. 表的DML
8. 补充
- 8.1 tips
- 8.2 数据库安全需要关注的地方
9. 参考

1. 一些概念

数据库：保存有组织的数据的容器
表：一种结构化的文件，用于存储特定类型的数据。一个数据库里的表名称必须唯一。
模式（schema）：关于数据库和表的布局及特性的信息。
列：表的资格字段，一张表由多列构成，每列由固定的数据类型。
行：也称记录，表是关于某个对象的多维度的多条信息，则行这个对象的这些纬度下的一条数据。
主键：特殊的一列或者几列，不同记录在主键上的取值是唯一的，从而保证主键可以唯一标示一条记录。主键的特点：任两行主键值不同；主键各列不许为空值；主键列中的值不许修改或者更新；主键值不可重用，如果某记录被删除，则它占用的主键值不能用于后续新建的记录上。
子句：SQL关键字加上数据构成，SELECT * FROM Products; 其中FROM关键字加上表名称就是FROM子句。

1.1 数据库模式

模式：描述了数据的逻辑结构和组织方式，包含数据项的定义，数据项之间的关系、数据约束条件和数据操作规则。模式有三个层次：概念模式、外模式、内模式
概念模式：整个数据库的数据结构和关系。一个数据库有一个概念模式。
外模式：用户视角下的数据视图。不同角色的用户将看到整个数据的部分。一个数据库可以有多个概念模式。
内模式：物理存储方式。一个数据库只有一个内模式。
三级模式的二级映射：外模式/模式；模式/内模式。
数据的逻辑独立性：应用程序只能看到外模式，如果修改模式，外模式不变，应用程序无感知。
数据的物理独立性：修改数据库的物理存储方式，数据库的模式不变，从而外模式不变，应用程序不会受到影响。

1.2 SQL语言类型

DDL（Data Define Language）：创建（CREATE）、修改（ALTER）和删除（DROP）数诸如数据库、表、视图、索引、存储过程等数据库对象。
DQL（Date Query Language）：用于查询（SELECT）数据。可以对数据进行筛选，分组，排序，限制结果集（选多少行，选哪些列）。
DML（Data Manage Language）：插入（INSERT INTO）、修改（UPDATE）、删除（DELETE FROM）数据。
DCL（Date Control Language）：主要用于权限控制（GRANT、REVOKE）
TCL（Transaction Control Language）：用于使用事务。START TRANSACTION、ROLLBACK、COMMIT

2. 查询

-- 语法顺序
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT

-- 执行顺序：取表，筛选，分组筛选，选择列，指定的列排序，限制行
FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
LIMIT

2.1 WHERE子句：过滤条件

WHERE子句在SELECT语句中位于FROM语句之后。
过滤即可以放在数据库层面做，也可放在应用层去做。放在应用层做不妥，因为数据库和应用之间传递大量多于数据，浪费网络带宽。另外，优化的数据库过滤操作效率也更高。

2.1.1 WHERE子句的条件操作符

注意不同的DBMS支持不同的条件操作符
NULL值表示无值，不同于值为0，空字符串，空格的情形。在进行非匹配的时候，NULL值的情况不会被返回。

条件操作符	说明
=	等于
<>, !=	不等于
<	小于
>	大于
!>, <=	小于等于
!<, >=	大于等于
BETWEEN AND	闭区间
IS NULL	为NULL

2.1.2 单过滤条件

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

-- 判断NULL值，注意判断NULL值要用专门的IS NULL子句
SELECT prod_name
FROM Products
WHERE prod_price IS NULL;

2.1.3 多过滤条件

使用逻辑操作符组合多个过滤条件。常用的操作符有：AND， OR，IN，NOT
OR操作符的的第一个条件得到满足就返回，不再判断二个条件。
AND和OR的顺序

-- 在WHERE子句中，先过滤AND后的条件prod_price >= 10，然后再过滤完的结果上执行OR操作符的过滤。AND操作有比OR高的优先级。
SELECT prod_name, prod_price
FROM Products
WHERE vend_id = 'DLL01' OR venc_id = 'BRS01'
    AND prod_price >= 10;

-- ()有比AND和OR都搞定优先级，给整个OR过滤条件加上括号，则过滤操作先执行括号里的OR语句。
SELECT prod_name, prod_price
FROM Products
WHERE (vend_id = 'DLL01' OR venc_id = 'BRS01')
    AND prod_price >= 10;

当过滤需要同时使用AND和OR时，应当总是使用圆括号。
IN:用于判断查询字段值是否在给定离散值范围内。用括号包含给定的离散值，每个值用逗号隔开。
IN比一组OR过滤更快。

SELECT prod_name, prod_price
FROM Products
WHERE vend_id in ('DLL01', 'BRS01')  -- 等价于 WHERE vend_id = 'DLL01' OR vend_id = 'BRS01'
ORDER BY prod_name;

2.1.4 通配符

通配符用于表示一些特殊字符，含有通配符的字符串可以作为用于搜索的搜索模式，搜索模式描述了一个特定的字符串集合，字段值如果匹配搜索模式的话将被选中。SQL中的统配符：%（表示0个或者多个任意字符），_ （表示任意单个字符），[] (落入一个字符集的单个字符，字符集内加上^前缀则表示该字符集合之外的字符集)。
MySQL中谓词LIKE可以使用的通配符：% _。REGEXP/RLIKE可以使用的通配符：. * [] + ^ $。

-- LIKE通配符%
SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE 'Fish%';  -- 'Fish bean bag toy'

-- LIKE通配符_
ELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE '__ inch teddy bear';  -- '12  inch teddy bear'

-- REGEXP通配符 ^：查找以A开头的用户名
SELECT * FROM users WHERE username REGEXP '^A';

特点

通配符%不会匹配NULL值。
通配效率较低，如果其他操作符能达到同样的效果优先使用其他方案。
需要使用通配符时，不应把它放在最前面而优先处理。

2.2 GROUP BY子句：分组

2.2.1 特点

GROUP BY子句可以包含任意数量的列，聚集将在逐层分组后的最后一个列上进行。
GROUP BY子句不支持别名
GROUP BY子句不允许列带有长度可变的数据类型（文本类型，备注类型）
如果被分组的列有NULL值，则NULL值也被作为一组。
GROUP BY子句在WHER子句之后，ORDER BY子句之前。

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

-- 多层分组
SELECT vend_id, sub_vend_id, COUNT(*) AS num_prods
FROM Products
GROUP BY (vend_id, sub_vend_id);

-- 相对位置指示列
SELECT vend_id, sub_vend_id, COUNT(*) AS num_prods
FROM Products
GROUP BY (1, 2);

2.2.2 分组的过滤

WHERE在分组前进行过滤，HAVING在分组后过滤。注意HAVING主要用于过滤分组，每个分组都有一个聚集值，因此HAVING过滤的条件针对分组的聚集值。

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

2.3 DISTINCT: 查询某列不同的值

-- 单列去重
SELECT DISTINCT vend_id 
FROM Prodcuts;

-- 多列去重（笛卡尔积后这些列的不同值）。
SELECT DISTINCT vend_id, prod_price
FROM Prodcuts;

2.4 LIMIT OFFSET：限制查询结果数量

2.4.2 取前n行

-- MySQL MariaDB, PostgreSQL, SQLite
SELECT prod_name
FROM Prodcuts
LIMIT 5;

2.4.2 取从m行起的n行数据

-- 返回从第2行起的5行数据，此处行号从0起算。
SELECT prod_name
FROM Prodcuts
LIMIT 5 OFFSET 2;

-- 简化版本（MySQL MariaDB，SQLite），注意2是起始行号，5是行数
SELECT prod_name
FROM Prodcuts
LIMIT 2,5;

2.5 ORDER BY子句：排序

ORDER BY子句应是SELECT语句的最后一个子句，否则报错。默认升序排序。

2.5.1 单列排序

SELECT prod_name
FROM Products
ORDER BY prod_name;

2.5.2 多列排序

-- 先按照prod_price排序，再按照prod_name排序
SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price, prod_name;

-- 通过列的相对位置进行多行排序，即prod_price排序，再按照prod_name排序
SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY 2, 3;

2.5.3 指定排序方向

-- 单列排序，指定排序方向
SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price DESC;

/* 
多列情况下，指定排序方向
排序方向关键字DESC仅仅作用于prod_price，而prod_name仍使用默认的升序。
如果需要指定更多列采取DESC，则应每列都加上DESC关键字。
 */
SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price DESC， prod_name;

2.6 计算字段

存储在数据库中的数据格式不是应用所需要的，需要直接从数据库中检索出转换，计算或者格式化后的数据，而非检索出数据让应用程序去转换，计算或者格式化。同样的数据的转换、计算或者格式化放在SQL语句中完成要比在应用中完成高效得多。

2.6.1 格式调整

-- SQL Server
SELECT vend_name + '(' + vend_counutry + ')'
FROM Vendors
ORDER BY vend_name;

-- MySQL: 使用函数，两个被合并的字段之间用空格填充，从而保持足以容纳两个字段值的固定列宽。
SELECT CONCAT(vend_name, '(', vend_counutry, ')')
FROM Vendors
ORDER BY vend_name;

/*
Rtrim()去除字段值右边的空字符，LTrim()去除右边的空字符，Trim()去除两边的空字符
*/

-- MySQL：使用RTIM()去掉字段之间的空格
SELECT CONCAT(RTrim(vend_name), '(', RTrim(vend_counutry), ')') AS vend_tittle  -- AS关键字，用于设置列别名/导出列
FROM Vendors
ORDER BY vend_name;

2.6.2 算数计算

SELECT prod_if,
       quantity,
       item_price,
       quantity*item_price AS expanded_price
FROM OrderItems
WHERE order_num = 2008;

2.7 函数

类型：文本处理，算术操作，时间日期处理，格式化，返回特殊信息。

2.7.1 常用文本处理函数

常用的文本处理函数

函数	说明
LEFT()	返回给定字符串左右的字符
LENGTH()	返回字符串长度
LOWER()	将字符串转换为小写
UPPER()	将字符串转换为大写
Trim()	去除字符串两边的空格
RTrim()	去除字符串右边的空格
LTrim()	去除字符串左边的空格
SUBSTR()/SUBSTRING()	提取字符串子串
SOUNDEX()	返回字符串的SOUNDEX值

case1：按分隔符截取

SUBSTRING_INDEX分割后的第一项或者最后一个项可以直接截取

+----+-----------+----------------------+-----------------------+
| id | device_id | profile              | blog_url              |
+----+-----------+----------------------+-----------------------+
|  1 |      2138 | 180cm,75kg,27,male   | http:/url/bisdgboy777 |
|  1 |      3214 | 165cm,45kg,26,female | http:/url/dkittycc    |
|  1 |      2131 | 168cm,45kg,22,female | http:/url/sysdney     |
+----+-----------+----------------------+-----------------------+

-- 提取性别
SELECT SUBSTRING_INDEX(profile,",",-1) number FROM user_submit;
+--------+
| number |
+--------+
| male   |
| female |
| female |
+--------+

-- 提取身高
SELECT SUBSTRING_INDEX(profile,",", 1) number FROM user_submit;
+--------+
| number |
+--------+
| 180cm  |
| 165cm  |
| 168cm  |
+--------+

若要提取分割后位于中间的seg，则需要嵌套SUBSTRING_INDEX，提取任意中间的seg都只需要两层SUBSTRING_INDEX。

-- 一层SUBSTR_INDEX无法单独提取体重
SELECT SUBSTRING_INDEX(profile,",", 2) number FROM user_submit;
+------------+
| number     |
+------------+
| 180cm,75kg |
| 165cm,45kg |
| 168cm,45kg |
+------------+

-- 嵌套SUBSTR_INDEX单独提取体重
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(profile, ",", 2),",", -1) number FROM user_submit;
+--------+
| number |
+--------+
| 75kg   |
| 45kg   |
| 45kg   |
+--------+

-- 提取年龄
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(profile, ",", -2),",", 1) number FROM user_submit;
+--------+
| number |
+--------+
| 27     |
| 26     |
| 22     |
+--------+

2.7.2 日期和时间处理函数

MySQL常用日期函数

YEAR()，MONTH(), DAY()：分别提取date类型字段中的年，月，日。
TO_DATE()：将字符串转换为date类型值。
DATE_FORMAT()：别名strftime，按照指定格式从date类型字段中提取字符串。格式如下表。

说明符	描述
%Y	四位数的年份（例如：2024）
%y	两位数的年份（例如：24）
%m	两位数的月份（01 - 12）
%c	一位或两位数的月份（1 - 12）
%d	两位数的日（01 - 31）
%e	一位或两位数的日（1 - 31）
%H	24 小时制的小时数（00 - 23）
%h 或 %I	12 小时制的小时数（01 - 12）
%i	分钟数（00 - 59）
%s	秒数（00 - 59）
%p	AM 或 PM
%W	星期的完整名称（例如：Sunday）
%a	星期的缩写名称（例如：Sun）
%M	月份的完整名称（例如：January）
%b	月份的缩写名称（例如：Jan）

case1：根据年份进行等值筛选

-- 提取年份再筛选：DATE_FORMAT()
SELECT order_num
FROM Orders
WHERE DATE_FORMAT(order_date, '%Y') = 2020;

-- 提取年份再筛选：YEAR()
SELECT order_num
FROM Orders
WHERE YEAR(order_date) = 2020;

-- 日期范围查询

case2：根据年份进行范围筛选

直接使用字符串类型的上下限，这会导致字符串类型 -> date类型的隐式类型转换

-- 使用between and
select order_num
FROM Orders;
WHERE order_date BETWEEN '2020-01-01' AND '2020-12-31';

-- 使用关系运算符
select order_num
FROM Orders;
WHERE order_date >= '2020-01-01' AND order_date <= '2020-12-31';

显式类型转换方式，上下限转换为date类型再比较

-- 使用between and
select order_num
FROM Orders;
WHERE order_date BETWEEN to_date('2020-01-01') AND order_date <= to_date('2020-12-31');

-- 直接使用关系运算符
select order_num
FROM Orders;
WHERE order_date >= to_date('2020-01-01') AND order_date <= to_date('2020-12-31');

2.7.3 数值处理函数

常用的数值处理函数：ABS()，COS()，EXP()，PI()，SIN()，SQRT()，TAN()。

2.7.4 聚集函数

聚集函数的作用是将查询的字段的多个值作为输入，计算得到一个值。
常见的聚集函数：AVG()，COUNT()，MAX()，MIN()，SUM()。

2.7.5 条件函数

二分支判断

-- 统计女生人数
+-----+------+------+--------+-------+
| uid | name | age  | gender | class |
+-----+------+------+--------+-------+
|   1 | u1   |    1 | male   |     1 |
|   3 | u3   |    3 | male   |     2 |
|   7 | u7   |    7 | female |     1 |
|   9 | u9   |    9 | female |     3 |
|  13 | u13  |   13 | female |     3 |
+-----+------+------+--------+-------+
select sum(if(gender='female', 1,0)) as female_total_cnt
from users;
+------------------+
| female_total_cnt |
+------------------+
|                3 |
+------------------+

-- 统计每个班女生人数
select class, sum(if(gender='female', 1,0)) as femle_class_cnt
from users
group by class;
+-------+-----------------+
| class | femle_class_cnt |
+-------+-----------------+
|     1 |               1 |
|     2 |               0 |
|     3 |               2 |
+-------+-----------------+

-- 统计每个班男生女生各多少人，每个班有两行，一行记录男生数量，一行记录女生数量
SELECT 
    class,
    SUM(IF(gender = 'female', 1, 0))  AS female_cnt,
    SUM(IF(gender = 'male', 1, 0)) AS male_cnt
FROM 
    users
GROUP BY 
    class;
+-------+------------+----------+
| class | female_cnt | male_cnt |
+-------+------------+----------+
|     1 |          1 |        1 |
|     2 |          0 |        1 |
|     3 |          2 |        0 |
+-------+------------+----------+

多分支判断

-- 分年龄段筛选：小于3岁，3岁到10岁，10岁以上的人数
select (case 
		when age < 3 then '<3 years old' 
		when age >= 3 and age <= 10 then '3-10 years old' 
		else '>10 years old' 
	end) as age_cut, 
	count(*) as cnt 
from users 
group by age_cut;
+----------------+-----+
| age_cut        | cnt |
+----------------+-----+
| <3 years old   |   1 |
| 3-10 years old |   3 |
| >10 years old  |   1 |
+----------------+-----+

2.7.6 特点

avg()，min()，max()，sum()忽略列值为NULL的行。
count(*)计数包含NULL值的情况，count(column)计数不包含NULL值的情况。

-- 去重后再聚集, DISTINCT不能用于count(*)，只能用于count(DISTINCT column)
SELECT AVG(DISTINCT prod_price) AS avg_price
FROM Products
WHERE cend_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;

2.8 子查询

作为子查询的SELECT语句只能是单列。子查询效率不高。

2.8.1 位于WEERE子句的子查询

/* 例子，查出某个订购商品的用户信息
1. 根据产品ID查询订单产品表，得到订单编号
2. 根据订单编号查询订单表，得到顾客id
*/

-- 1. 根据产品ID查询订单产品表，得到订单编号
SELECT order_num
FROM OrderItems
WHERE prod_id = 'RGAN01';  -- 20007, 20008

-- 2. 根据订单编号查询订单表，得到顾客id
SELECT cust_id
FROM Orders
WHERE order_num IN (20007, 20008);

-- 使用子查询
SELECT cust_id
FROM Orders
WHERE order_num IN (SELECT order_num
                    FROM OrderItems
                    WHERE prod_id = 'RGAN01');

-- 使用多层嵌套的子查询：
SELECT cust_name, cust_contact
FROM Customs
WHERE cust_id IN (SELECT cust_id
                  FROM Orders
                  WHERE order_num IN (SELECT order_num
                                      FROM OrderItems
                                      WHERE prod_id = 'RGAN01');)

2.8.2 作为计算字段的子查询

SELECT cust_name,
       cust_state,
       (SELECT COUNT(*)
        FROM Orders
        WHERE Orders.cust_id = Customers.cust_id) AS orders
FROM Customers
ORDER BY cust_name;
-- 注意子查询中使用了完全限定列名

3. 连接

3.1 关系表的概念

同一供应商生产多种产品，产品需要记录的信息包括：产品描述、价格、生产该产品的供应商等。而供应商需要记录的数据包含：供应商名称、地址、联系方式等。用一张表将产品信息和供应商信息一起存储，一行代表一个产品，则产品有关供应商信息的字段会大量重复（坏处：浪费存储空间，查询耗时高，维护供应商信息很麻烦，输入产品信息可能出错导致信息不一致）。
正确的做法是，将产品数据和供应商信息分开置表存放。Vendors只存放供应商信息（主键为供应商ID），Products表只存放产品数据，Products表存有供应商ID字段。像这样Products表和Vendors表用共同的供应商ID字段关联起来，它们就是关系表。关系表具有良好的可扩展性。

3.2 连接表进行查询

数据被放置在不同的表中，需要在一条SELECT语句中连接这些表进行检索。

3.2.1 内连接

case1: 内连接就是等值链接

-- 等值连接/内连接: 使用WHERE
SELECT vend_name, prod_name, prod_price
FROM Vendors, Products
WHERE Vendors.vend_id = Products.vend_id;

-- 等值连接/内连接: 使用INNER JOIN
SELECT vend_name, prod_name, prod_price
FROM Vendors
INNER JOIN Products ON Vendors.vend_id = Products.vend_id;

case2：理解等值连接

如果A中有1行id=1的记录，B中有3行id=1的记录，则A inner join B on A.id=B.id的结果中有3行关于id=1的记录。

CREATE TABLE `TmpTbl1` (
  `id` int NOT NULL DEFAULT '1',
  `name` char(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `TmpTbl2` (
  `id` int NOT NULL DEFAULT '1',
  `order_id` char(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `TmpTbl3` (
  `id` int NOT NULL DEFAULT '1',
  `name` char(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

insert into TmpTbl1 values(1, ’aa’), (2, ‘bb’);
insert into TmpTbl2 values(1, ‘001’), (1, ‘002’), (2, ‘003’), (3, ‘004’);
insert into TmpTbl3 values(1, ‘aa’), (2, ‘bb’), (2, ‘bb’);

select t1.id, t1.name, t2.order_id
from TmpTbl1 as t1
inner join TmpTbl2 as t2 on t1.id = t2.id;
/*
+----+------+----------+
| id | name | order_id |
+----+------+----------+
|  1 | aa   | 001      |
|  1 | aa   | 002      |
|  2 | bb   | 003      |
+----+------+----------+
*/


select t1.id, t1.name
from TmpTbl1 as t1
inner join TmpTbl3 as t3 on t1.id = t3.id;
/*
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | bb   |
|  2 | bb   |
+----+------+
*/

3.2.2 叉连接

-- 笛卡尔积，叉连接
SELECT vend_anme, prod_name, prod_price
FROM Vendors, Products

3.2.3 组合与嵌套中的等值条件

-- 嵌套子查询使用连接
SELECT cust_name, cust_contact
FROM Customs
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 Customs 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';

3.3 其他连接类型

3.3.1 自连接

-- cust_name为客户所在公司名，cust_contact为客户名。找出和Jim Jones在同一公司的顾客

-- 方法一：使用子查询
SELECT cust_id, cust_name, cust_contact
FROM Customres
WHERE cust_name = (SELECT cust_name
                   FROM Customers
                   WHERE cust_contact = 'Jim Jones'); 

-- 方法二：使用自连接
SELECT c1.cust_id, c1.cust_name, c1.cust_contact
FROM Customers AS c1, Custoners AS c2
WHERE c1.cust_name = c2.cust_name
  AND c2.cust_contact = 'Jim Jones';

3.3.2 自然连接

具备共同的列的表才能被连接起来，DBMS在处理连接时，位于不同表中的共同列可能重复出现。自然连接指那些去除了重复公共列的连接（内连接不会去除各个表中的连接字段）。
自然连接不显示指定连接条件（不显示指定用于连接的公共字段），而DBMS默认找到公共字段并进行匹配。

-- case1: 提取所有列的内连接，结果列中会出现两个vend_id，这不是自然连接
SELECT *
FROM Vendors, Products
WHERE Vendors.vend_id = Products.vend_id;

-- case2: 提取的行中没有重复的列，这是自然连接
SELECT vend_name, prod_name, prod_price
FROM Vendors, Products
WHERE Vendors.vend_id = Products.vend_id;

-- 等同于
SELECT *
FROM Products
NATURAL JOIN Vendors;

-- case3: 通过给第一个表提取所有列，其他表挑选指定列来实现自然连接。
SELECT C.*, O.order_num, O.order_date,
        OI.prod_id, OI.quantity, OI.item_price
FROM Customers AS C, Order AS O, OrderItems AS OI
WHERE C.cust_id = O.cust_id
  AND OI.order_num = O.order_num
  AND prod_id = 'RGAN01';

3.3.3 外连接

有时候需要查看以一个表的关联列的所有取值为参照来进行连接。当连接条件不满足时的记录也出现在结果中，这被称为外连接。外连接分为左外连接，右外连接，全外连接。左外连接以左表的关联列取值为参照，右外连接以右表的关联列取值为参照，全外连接则合并了左外连接和右外连接。
可以通过转换左右表位置来得到左右外连接的效果。SQLite没有右外连接，可以通过交换左右表的位置来实现右外连接。·
MySQL，MariaDB，SQLite不支持 FULL OUTER JOIN，可以通过UNION来合并左右外连接得到全外连接的效果。

左外连接

inner join和left join的区别：假设A表中有一行id=1的记录，B表中2行id=1的记录和1行id=2的记录，则A inner join B on A.id=B.id结果中包含2行id=1的记录（从A中取出id=1，扫描B全表，发现2个匹配行）；A left join A.id = B.id 结果中有2行id=1的记录，原理跟inner join相同，但是 B left join A on B.id = A.id 结果就和inner join不同了，结果中有2行id=1的记录和1行id=2的记录，2行id=1的记录仍然适用inner join的等值匹配法则，但是对1行id=2的记录，从B表id列中取得的id=2在A表中找不到匹配项，这一行就成了id=2，其余字段为null的一行。

TmpTbl2
+----+----------+
| id | order_id |
+----+----------+
|  1 | 001      |
|  1 | 002      |
|  2 | 003      |
|  3 | 004      |
+----+----------+

TmpTbl1
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | bb   |
+----+------+


select t1.id, t2.id, name, order_id from TmpTbl2 t2 left join TmpTbl1 t1 on t1.id = t2.id;
+------+----+------+----------+
| id   | id | name | order_id |
+------+----+------+----------+
|    1 |  1 | aa   | 001      |
|    1 |  1 | aa   | 002      |
|    2 |  2 | bb   | 003      |
| NULL |  3 | NULL | 004      |
+------+----+------+----------+


select t1.id, t2.id, name, order_id from TmpTbl1 t1 left join TmpTbl2 t2 on t1.id = t2.id;     
+----+------+------+----------+
| id | id   | name | order_id |
+----+------+------+----------+
|  1 |    1 | aa   | 002      |
|  1 |    1 | aa   | 001      |
|  2 |    2 | bb   | 003      |
+----+------+------+----------+

右外连接

-- 右表式Customers，右外连接将以Orders的cust_id列的所有取值为参照进行连接
SELECT Customers.cust_id, Orders.order_num
FROM Customers
RIGHT OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id;

-- Orders表中有两条cust_id为1000000001的记录，则将两次用1000000001匹配左表中的cust_id=1000000001的行
+------------+-----------+
| cust_id    | order_num |
+------------+-----------+
| 1000000001 |     20005 |
| 1000000001 |     20009 |
| 1000000003 |     20006 |
| 1000000004 |     20007 |
| 1000000005 |     20008 |
+------------+-----------+

3.3.4 对连接表使用聚合

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

-- 关于cust_id的内连接中cust_id=1000000001有两个匹配行
+------------+-----------+
| cust_id    | order_num |
+------------+-----------+
| 1000000001 |     20005 |
| 1000000001 |     20009 |
| 1000000003 |     20006 |
| 1000000004 |     20007 |
| 1000000005 |     20008 |
+------------+-----------+

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;

+------------+---------+
| cust_id    | num_ord |
+------------+---------+
| 1000000001 |       2 |
| 1000000003 |       1 |
| 1000000004 |       1 |
| 1000000005 |       1 |
+------------+---------+

-- 对左外连接表使用聚合的例子
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;

-- 左表中cust_id=1000000002在右表中没有匹配的行，因此计数为0
+------------+---------+
| cust_id    | num_ord |
+------------+---------+
| 1000000001 |       2 |
| 1000000002 |       0 |
| 1000000003 |       1 |
| 1000000004 |       1 |
| 1000000005 |       1 |
+------------+---------+

4. 组合查询

UNION操作符用于将多条SELECT语句执行结果合并。UNION要求各个结果集有相同的列，不要求各个SELECT语句中列的顺序相同。

4.1 case1：基本用法

SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL', 'IN', 'MI');
+---------------+--------------+-----------------------+
| cust_name     | cust_contact | cust_email            |
+---------------+--------------+-----------------------+
| Village Toys  | John Smith   | sales@villagetoys.com |
| Fun4All       | Jim Jones    | jjones@fun4all.com    |
| The Toy Store | Kim Howard   | NULL                  |
+---------------+--------------+-----------------------+

SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_name = 'Fun4All';
+-----------+--------------------+-----------------------+
| cust_name | cust_contact       | cust_email            |
+-----------+--------------------+-----------------------+
| Fun4All   | Jim Jones          | jjones@fun4all.com    |
| Fun4All   | Denise L. Stephens | dstephens@fun4all.com |
+-----------+--------------------+-----------------------+

-- 合并查询结果，去除了重复的行，而不是简单的追加
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL', 'IN', 'MI')
UNION  -- 使用UNION ALL将不去重
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_name = 'Fun4All'
ORDER BY cust_name, cust_contact;  -- 对合并后的结果集进行排序
+---------------+--------------------+-----------------------+
| cust_name     | cust_contact       | cust_email            |
+---------------+--------------------+-----------------------+
| Fun4All       | Denise L. Stephens | dstephens@fun4all.com |
| Fun4All       | Jim Jones          | jjones@fun4all.com    |
| The Toy Store | Kim Howard         | NULL                  |
| Village Toys  | John Smith         | sales@villagetoys.com |
+---------------+--------------------+-----------------------+

4.2 case2：为何要使用UNION

需要考察多个筛选条件并逻辑下的结果，可以直接使用where子句，用or来连接多个筛选条件，但是得到的结果可能不是按照每个筛选条件排列在一起的。使结果更加清晰的方法是考虑每个查询只使用一个筛选条件，然后使用union将结果拼接在一起，这样每个筛选条件下的结果排列在一起，展示更为清晰。

+-----+------+------+--------+-------+
| uid | name | age  | gender | class |
+-----+------+------+--------+-------+
|   1 | u1   |    1 | male   |     1 |
|   3 | u3   |    3 | male   |     2 |
|   7 | u7   |    7 | female |     1 |
|   9 | u9   |    9 | female |     3 |
|  13 | u13  |   13 | female |     3 |
+-----+------+------+--------+-------+
-- 使用or连接对个删选条件
select * from users where gender='female' or class = 1；
+-----+------+------+--------+-------+
| uid | name | age  | gender | class |
+-----+------+------+--------+-------+
|   1 | u1   |    1 | male   |     1 |
|   7 | u7   |    7 | female |     1 |
|   9 | u9   |    9 | female |     3 |
|  11 | u11  |   11 | male   |     1 |
|  13 | u13  |   13 | female |     3 |
+-----+------+------+--------+-------+

-- 使用union逐个拼接筛选结果
select * 
from users
where gender = 'female'
union (
	select * 
	from users
	where class = 1
);
+-----+------+------+--------+-------+
| uid | name | age  | gender | class |
+-----+------+------+--------+-------+
|   7 | u7   |    7 | female |     1 |
|   9 | u9   |    9 | female |     3 |
|  13 | u13  |   13 | female |     3 |
|   1 | u1   |    1 | male   |     1 |
|  11 | u11  |   11 | male   |     1 |
+-----+------+------+--------+-------+

5. 数据插入

当列允许NULL值或者给出默认值时，插入操作可以省略这些列。

5.1 直接插入数据

-- 给出列名进行插入，推荐（表结构变化依然可用）
INSERT INTO Customers(csut_id,
                      cust_name,
                      cust_address,
                      cust_city,
                      cust_state,
                      cust_zip,
                      cust_country,
                      cust_contact,
                      cust_email)
VALUES(1000000006
       'Tony Land'
       '123 Any Street',
       'New York',
       'NY',
       '11111',
       'USA',
       NULL,
       NULL);

-- 根据表定义中列的次序来插入，不推荐（表结构变化后可能不可用）
INSERT INTO Customers
VALUES(1000000006
       'Tony Land'
       '123 Any Street',
       'New York',
       'NY',
       '11111',
       'USA',
       NULL,
       NULL);

5.2 插入检索的数据

-- 将表CustNew中的数据插入到Customers中
INSERT INTO Customers(csut_id,
                      cust_contact,
                      cust_email,
                      cust_name,
                      cust_address,
                      cust_city,
                      cust_state,
                      cust_zip,
                      cust_country)

SELECT csut_id,
       cust_contact,
       cust_email,
       cust_name,
       cust_address,
       cust_city,
       cust_state,
       cust_zip,
       cust_country
FROM CustNew;

-- 复制表
CREATE TABLE CustCopy AS SELECT * FROM Customers;

6. 更新和删除数据

6.1 更新操作

-- 更新一列的值
UPDATE Customers
SET cust_email = 'kim@thetoystore.com'
WHERE cust_id = 1000000005;

-- 更行多列的值
UPDATE Customers
SET cust_contact = 'Sam Roberts',
    cust_email = 'sam@toyland.com'
WHERE cust_id = 1000000006;

6.2 删除数据

// 删除表的全部数据，而不删除表结构
delete from tableName;		// 自增计数不清空，记录数据变动到事务日志
truncate table tableName;	// 自增计数清空，不会记录数据变动到事务日志，删除更快

// 筛选后删除
delete from tableName where id = 1;

6.3 外键对于删除和插入的约束

Products(prod_id, vend_id, prod_name, prod_price, prod_desc)，其中Products表的主键为prod_id，vend_id为Vendors表的主键，因而vend_id在Products作为外键存在，Products和Vendors存在外键约束。在外键vend_id的约束下，Products无法新增vend_id值不在Vendors.vend_id列中的新纪录，Vendors也无法删除vend_id值在Products表中出现过的那些记录。

7. 表的DML

7.1 创建表

一个列可以指定为使用NULL值（默认）或者不适用NULL值，主键列必须指定为不使用NULL值。

-- 指定默认值
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 events (
    event_id INT AUTO_INCREMENT PRIMARY KEY,
    event_date DATE NOT NULL DEFAULT (CURRENT_DATE()),  -- 要将函数CURRENT_DATE()用括号包裹起来。
    event_time TIME,
    event_datetime DATETIME,
    event_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- MYSQL中常用的时间类型有 DATE('2025-01-01')、TIME('00:00:00')、DATETIME('2025-01-01 00:00:00')、TIMESTAMP

7.1 更改表结构

-- 增加列
ALTER TABLE Vendors
ADD COLUMN vend_phone CHAR(20);

-- 修改列
ALTER TABLE Vendors
MODIFY COLUMN vend_phone vendPhone VARCHAR(11);

-- 删除列
ALTER TABLE Vendors
DROP COLUMN vend_phone;

7.3 删除表

如果表是某个关系到的组成部分，则无法删除，除非先删除关系。

DROP TABLE CustCopy;

7.4 重命名表

RENAME TABLE CustCopy TO CustBak;
ALTER TABLE CustCopy RENAME [TO] CustBak;

8. 补充

8.1 tips

SQL不区分大小写，一般SQL关键字用大写，表名列名小写；SQL忽略空格，但是一般分行写SQL以增强可读性。
相同数据出现多次是不好的，数据库设计需要避免这种情况（将冗余字段分出去单独成表，然后与原表的建立关系）。
许多DBMS处理连接比处理子查询要快很多尽量使用default value而不是NULL值在使用ALTER
TABLE最好先备份，数据库表的更改无法撤销。
在 MySQL 里，IF语句属于流程控制语句，这类语句只能在存储过程、函数、触发器等特定的程序结构中使用，不能直接在普通的 SQL 脚本中使用。
局部变量是使用declare显式声明在存储过程、函数、触发器等数据库对象中的变量，作用域仅在这些数据库对象内部。
每个客户端连接到MySQL服务器都会有一个独立的会话，会话变量作用域在会话中，前缀为@，不需要显式声明。
主键不要修改，主键值不要重用。

8.2 数据库安全需要关注的地方

数据库管理能力（创建、删除、更新表）
用户账户管理能力（DBMS用户）
访问特定数据库或者表
访问类型（增删改查，删改属于敏感操作，需要保护）
通过视图或者存储过程访问（存储过程或者视图涉及重要表和敏感信息时，需要保护）

9. 参考

贝内特（Bennett, F. X.）. (2020). SQL 必知必会（第 5 版） (钟鸣，刘晓霞，译). 北京：人民邮电出版社.