笔记分享: 墨尔本大学INFO20003数据库系统——02. SQL概述

更多 资料与 笔记

$\text{SQL (Structured Query Language)}$

$\text{1. Relational Algebra}$

$\text{1.0. Overview}$

1️⃣关系操作：包括五个$\text{Basic Operation}$

$\text{Operations}$	符号	原关系	操作(得到新关系)
$\text{Projection}$	$\pi$	原关系	$\underset{2.去除重复行}{\overset{1.保留想要的列(竖直过滤)}{\to }}$
$\text{Selection}$	$\sigma$	原关系	$\stackrel{保留想要的行(水平过滤)}{\to }$
$\text{Union}$	$\cup$	原关系$1+$原关系$2$	$\underset{2.去除重复项}{\overset{1.合并两表所有\text{Tuple}}{\to }}$
$\text{Intersection}$	$\cap$	原关系$1+$原关系$2$	$\underset{2.去除重复项}{\overset{1.提取两个表都出现的\text{Tuple}}{\to }}$
$\text{Set-difference/Minus}$	$-$	原关系$1+$原关系$2$	$\stackrel{移除关系2所含\text{Tuple}(在关系1中)}{\to }$
$\text{Cross-product}$	$\times$	原关系$1+$原关系$2$	$\stackrel{\text{Tuple}无条件互相组合(笛卡尔积)}{\to }$
$\text{Join}$	$\bowtie$	原关系$1+$原关系$2$	$\underset{2.按条件\text{Selection}}{\overset{1.\text{Tuple}无条件互相组合}{\to }}$

2️⃣$\text{Condition Expressions:}$

类型	符号
$\text{Arithmetic Expressions}$	>, <, >=, <=, =, !=
$\text{AND/OR Clauses}$	AND → Λ, OR → V

1. 示例：${\sigma }_{\text{rating}\ge 9\wedge \text{ age<50}}({\text{S}}_2)$

   SELECT * FROM S2 
   WHERE rating >= 9 AND age < 50;
   

2. 注意事项
   • $\text{SQL}$与$\text{C++}$等不同，等于就是$\text{=/}$不是$\text{==}$
   • 不等于的两种表达：!=或者<>

$\text{1.1. Projection}(\pi )\text{ \& Selection}(\sigma )$

1️⃣$\text{Projection}(\pi )$

1. 操作：仅保留$\text{Projection List}$中的列$\to$去除重复的行
2. 特点：$\text{Projection is a costly operation, DB won’t operate it by default.}$
3. 示例：注意$\text{SQL}$中可控制投影后去不去出重复项，但$\text{Relational Algebra}$里默认去除重复项

   SELECT * FROM Student; -- 选中所有列(左表)
   SELECT age FROM Student; -- 选中年龄这一列，但是SQL操作中默认不去除重复项(中表)
   SELECT DISTINCT age FROM Student; -- 选中年龄这一列，删除重复项(右表)
   

   

2️⃣$\text{Selection}(\sigma )$

1. 操作：仅保留$\text{Selection}$条件的行，并且不会有任何重复行
2. 示例：在$\text{SQL}$中体现在WHERE上

   SELECT * FROM Student WHERE Student.rating >= 9;
   

   

3️⃣$\text{Projection}(\pi )\text{ \& Selection}(\sigma )$

1. 注意事项
   • 一般先$\text{Selection}$后$\text{Projection}$的$(\pi {}_{\text{A}}(\sigma {}_{\text{B}}))$结构，这是因为$\text{Selection}$不会改变表格结构
   • 如果一定要用先$\text{Projection}$再$\text{Selection}$的$(\sigma {}_{\text{A}}(\pi {}_{\text{B}}))$结构，要确保$\text{A}\subseteq \text{B}$
2. 示例：在$\text{SQL}$中体现在WHERE和SELECT上

   SELECT sname, rating FROM Student WHERE Student.rating >= 9;
   

   

$\text{1.2. Union/Set-Difference/Intersection}$

对应的$\text{SQL}$操作叫做$\text{Set Operations}$

0️⃣条件($\text{Union-Compatible}$)：

1. 两表$\text{Column}$数一致，相应的列的$\text{Data Type}$相同
   • 两表属性名不一定要相同，$\text{SQL}$默认取上表的名
2. 该条件适用$\text{Union/Set Difference/Intersection}$三者

1️⃣$\text{Union:}$

1. 操作：将两表上下拼接在一起，并在$\text{Relational Algebra}$中默认消除重复项
2. 特性：对称性($\text{Symmetric}$)
3. 示例：在$\text{SQL}$代码中体现在UNION上(但不默认消除重复项)

   SELECT sid, sname, rating, age FROM S1 -- 此操作去除重复行
   UNION
   SELECT sid, sname, rating, age FROM S2;
   SELECT sid, sname, rating, age FROM S1 -- 此操作保留重复行
   UNION ALL
   SELECT sid, sname, rating, age FROM S2;
   

   

2️⃣$\text{Set-Difference:}$

1. 操作：从一个关系中，删除另一个关系中存在的$\text{Tuple}$
2. 特性：无对称性($\text{Asymmetric}$)
3. 示例：$\text{SQL}$中并没有直接的求差集子句，可以通过子查询变相实现

   SELECT sid, sname, rating, age
   FROM S1
   WHERE (sid, sname, rating, age) NOT IN (
       SELECT sid, sname, rating, age
       FROM S2
   );
   

   

3️⃣$\text{Intersection: Compound Operator}$

1. 操作：找出两个关系中共存的$\text{Tuples}$，本质上是混合运算$\text{R}\cap \text{S}\equiv \text{R}-(\text{R}-\text{S})$
2. 示例：注意操作有对称性；同样也需要用$\text{SQL}$子查询变相实现

   SELECT sid, sname, rating, age
   FROM S1
   WHERE (sid, sname, rating, age) IN (
       SELECT sid, sname, rating, age
       FROM S2
   );
   

   

$\text{1.3. Cross product \& Joins}$

$\text{SQL Inter Table Operations}$

$\text{1.3.1. Cross Product}(\times )$

1️⃣操作：

1. 用一个表的每行，依次去扫描另一个表的每一行，输出组合
2. 结果会保留所有的列(即使重名了，也不要紧)
   

2️⃣重命名$\rho$：

1. 合并后的属性名称可能一样，由此需要重命名
2. 比如$\rho (\text{C}(1\to \text{sid1},5\to \text{sid2}),\text{R}\times \text{S})$
   
   • $1\to \text{sid1}$的意思是：第一列改成$\text{sid1}$

3️⃣$\text{SQL}$代码

SELECT * FROM R, S;	-- Cross product between R, S

$\text{1.3.2. Inner Join}(\bowtie ):\text{Conditional Cross-Product}$

1️⃣$\text{Inner Join(AKA Condition Join):}$ $\text{R}\bowtie {}_{\text{Condition}}\text{S}={\sigma }_{\text{Condition}}(\text{R×S})$

1. $\text{R}{\bowtie }_{\text{(S}\text{.sid<R}\text{.sid)}}\text{S}$中，先计算$\text{R}\times \text{S}$
   
2. 再筛选满足$\text{(R}\text{.sid<S}\text{.sid)}$的行
   
3. $\text{SQL: }$⚠️⚠️⚠️当两表中有$\text{Column}$名撞了，应该在$\text{Column}$名前加上表名

   SELECT * FROM R INNER JOIN S ON R.sid < S.sid;
   

2️⃣$\text{Natural Join(AKA Join):}$ 一种隐式的$\text{Inner Join}$

1. 原理/计算步骤
   • 先计算$\text{R}\times \text{S}$，找到两组(一组一或多个)名字相同的$\text{Column}$(此处为$\text{sid}$)
     
   • 筛选两同名列值相同的行，然后只保留其中一列。即为$\text{R}\bowtie \text{S}\equiv \text{R}\bowtie {}_{\text{R.sid=S.sid}}\text{S}$
     
2. 注意事项：多个$\text{Column}$相同匹配时，NATURAL JOIN要多列相等
   
   即为$\text{R}\bowtie \text{S}\equiv \text{R}\bowtie {}_{\text{R.sid=S.sid }\wedge \text{ R.bid=S.bid}}\text{S}$
3. $\text{SQL: }$⚠️⚠️⚠️没有$\text{Column}$相同匹配时，NATURAL JOIN语句会被执行为$\text{Cross Product}$

   SELECT * FROM R NATURAL JOIN S
   

3️⃣$\text{Equi Join:}$ 一种$\text{Inner Join}$的特殊情况，比如$\text{R}{\bowtie }_{{\text{(S}}_1{\text{.sid=R}}_2\text{.sid)}}\text{S}$(条件是等式)

SELECT * FROM R INNER JOIN S ON R.sid = S.sid;

$\text{1.3.3. Outer Join}:\text{Include Every Record}$

1️⃣概述

$\text{Outer Join}$	操作
$\text{Left Outer Join}$	保留左表中所有记录，右表中不匹配的用$\text{NULL}$代替
$\text{Right Outer Join}$	保留右表中所有记录，左表中不匹配的用$\text{NULL}$代替
$\text{Full Outer Join}$	保留左右两表中所有记录，两表中不匹配的用$\text{NULL}$代替

2️⃣示意图：$\text{Left/Right/Full}$

3️⃣示例

客户表	$\text{CID}$	属性$\text{1}$	属性$\text{2}$	账户表	$\text{CID}$	$\text{AID}$	属性$\text{3}$
$\text{/}$	$\text{1}$	$\text{X1}$	$\text{Y1}$	$\text{/}$	$\text{1}$	$\text{1}$	$\text{Z1}$
$\text{/}$	$\text{2}$	$\text{X2}$	$\text{Y2}$	$\text{/}$	$\text{1}$	$\text{2}$	$\text{Z2}$
$\text{/}$	$\text{3}$	$\text{X3}$	$\text{Y3}$	$\text{/}$	$\text{2}$	$\text{3}$	$\text{Z3}$
$\text{/}$	$\text{/}$	$\text{/}$	$\text{/}$	$\text{/}$	$\text{4}$	$\text{4}$	$\text{Z4}$

1. $\text{Left Outer Join:}$ Customer.CustomerID = 3时有表无匹配，所以代之以$\text{NULL}$

   SELECT * FROM Customer LEFT OUTER JOIN Account
   ON Customer.CustomerID = Account.CustomerID; 
   -- 左外联，保留左表所有CustomerID
   

   $\text{CID}$	属性$\text{1}$	属性$\text{2}$	$\text{AID}$	属性$\text{3}$
   $\text{1}$	$\text{X1}$	$\text{Y1}$	$\text{1}$	$\text{Z1}$
   $\text{1}$	$\text{X1}$	$\text{Y1}$	$\text{2}$	$\text{Z2}$
   $\text{2}$	$\text{X2}$	$\text{Y2}$	$\text{3}$	$\text{Z3}$
   $\text{3}$	$\text{X3}$	$\text{Y3}$	$\text{NULL}$	$\text{NULL}$

2. $\text{Right Outer Join:}$ Account.CustomerID = 4时有表无匹配，所以代之以$\text{NULL}$

   SELECT * FROM Customer RIGHT OUTER JOIN Account
   ON Customer.CustomerID = Account.CustomerID; 
    -- 右外联，保留右表所有CustomerID
   

   $\text{CID}$	属性$\text{1}$	属性$\text{2}$	$\text{AID}$	属性$\text{3}$
   $\text{1}$	$\text{X1}$	$\text{Y1}$	$\text{1}$	$\text{Z1}$
   $\text{1}$	$\text{X1}$	$\text{Y1}$	$\text{2}$	$\text{Z2}$
   $\text{2}$	$\text{X2}$	$\text{Y2}$	$\text{3}$	$\text{Z3}$
   $\text{4}$	$\text{NULL}$	$\text{NULL}$	$\text{4}$	$\text{Z4}$

3. $\text{Full Outer Join:}$ 也可以认为是$\text{Right Outer Join结果}\cup \text{Leftt Outer Join结果}$

   SELECT * FROM Customer FULL OUTER JOIN Account
   ON Customer.CustomerID = Account.CustomerID; 
    -- 全外联，保留两表所有CustomerID
   

   $\text{CID}$	属性$\text{1}$	属性$\text{2}$	$\text{AID}$	属性$\text{3}$
   $\text{1}$	$\text{X1}$	$\text{Y1}$	$\text{1}$	$\text{Z1}$
   $\text{1}$	$\text{X1}$	$\text{Y1}$	$\text{2}$	$\text{Z2}$
   $\text{2}$	$\text{X2}$	$\text{Y2}$	$\text{3}$	$\text{Z3}$
   $\text{3}$	$\text{X3}$	$\text{Y3}$	$\text{NULL}$	$\text{NULL}$
   $\text{4}$	$\text{NULL}$	$\text{NULL}$	$\text{4}$	$\text{Z4}$

$\text{1.3.4. }$复合$\text{Join}$

1️⃣示例的$\text{ABC}$表格

$\text{A}$	$\text{AID}$	$\text{AName}$	$\text{B}$	$\text{BID}$	$\text{AID}$	$\text{BName}$	$\text{C}$	$\text{CID}$	$\text{CType}$	$\text{BName}$
$\text{/}$	$\text{1}$	AName1	$\text{/}$	$\text{101}$	$\text{1}$	BName1	$\text{/}$	$\text{1001}$	CType1	BName2
$\text{/}$	$\text{2}$	AName2	$\text{/}$	$\text{102}$	$\text{1}$	BName2	$\text{/}$	$\text{1002}$	CType2	BName3
$\text{/}$	$\text{/}$	$\text{/}$	$\text{/}$	$\text{201}$	$\text{2}$	BName3	$\text{/}$	$\text{/}$	$\text{/}$	$\text{/}$

2️⃣$\text{Inner Join}$

A INNER JOIN B ON A.AID = B.AID

$\text{A.AID}$	$\text{A.AName}$	$\text{B.ID}$	$\text{B.AID}$	$\text{B.BName}$
$\text{1}$	AName1	$\text{101}$	$\text{1}$	BName1
$\text{1}$	AName1	$\text{102}$	$\text{1}$	BName2
$\text{2}$	AName2	$\text{201}$	$\text{2}$	BName3

2️⃣复合$\text{Inner Join:}$ 拿上面那个$\text{Join}$的结果再去$\text{Join}$

A INNER JOIN B ON A.AID = B.AID INNER JOIN C ON B.BName = C.BName

$\text{A.AID}$	$\text{A.AName}$	$\text{B.ID}$	$\text{B.AID}$	$\text{B.BName}$	$\text{C.CID}$	$\text{C.CType}$	$\text{C.BName}$
$\text{1}$	AName1	$\text{101}$	$\text{1}$	BName2	$\text{1001}$	CType1	BName2
$\text{2}$	AName2	$\text{201}$	$\text{2}$	BName3	$\text{1002}$	CType2	BName3

$\text{2. SQL Overview}$

$\text{2.1. CRUD Commends}$

$\text{Create/Read/Update/Delete Commands}$

$\text{Command Type}$	功能	$\text{Description}$
$\text{DDL}$	$\text{Definition}$	定义并建立数据库
$\text{DML}$	$\text{Manipulation}$	维护并使用(查询)数据库，获取数据的价值与信息
$\text{DCL}$	$\text{Control}$	控制用户对数据的访问权
$\text{Other}$	$\text{N/A}$	管理数据库

$\text{2.1.1. DDL Commands: Set up DB}$

1️⃣创建表格操作CREAT，其实也就是$\text{Implementation}$操作

-- 创建Customer表
CREATE TABLE Customer (
CustomerID INT PRIMARY KEY,
CustFirstName VARCHAR(45)
);
-- 创建Account表
CREATE TABLE Account (
-- 若表中现有最后一行ID是1001，插入的新ID自动变成1002
AccountID INT AUTO_INCREMENT PRIMARY KEY, 
-- 枚举数据类型，只允许账户号码为三者之一
AccountNumber ENUM('Num1', 'Num2', 'Num3');
Customer_CustomerID INT,
FOREIGN KEY (Customer_CustomerID) REFERENCES Customer(CustomerID)
);

1. 关于$\text{Key}$
   • 要用PRIMARY KEY指定哪个变量时主键
   • 要用FOREIGN KEY REFERENCES指定$\text{Foreign Key}$引用的哪个变量，否则会$\text{Syntax Error}$
2. 补充事项
   • AUTO_INCREMENT: 用来标记主键字段，插入一个记录后被标记字段自动$\text{+1}$
   • ENUM: 枚举数据类型类型，只允许属性为预设值

2️⃣修改表格系列操作

操作	关键字
增加表格的$\text{Attributes}$	ALTER TABLE <表名> ADD <属性名> <属性类型>
减少表格的$\text{Attributes}$	ALTER TABLE <表名> DROP <属性名>
表格重命名	RENAME TABLE <旧表名> TO <新表名>
快速清空表格记录($\text{Can’t Roll Back}$)	TRUNCATE TABLE <表名>
慢速清空表格记录($\text{Can Roll Back}$)	DELETE * FROM <表名>
彻底删除(杀死)一个表格	DROP * FROM <表名>

-- 添加属性: 客户表中添加CustLastName属性
ALTER TABLE Customer ADD CustLastName VARCHAR(45); 
-- 减少属性: 账户表中删除AccountNumber属性
ALTER TABLE Account DROP AccountNumber;   
-- 重命名: 将Customer表重命名为NewCustomers
RENAME TABLE Customer TO NewCustomers 
-- 快速清空表记录: 清空所有客户的数据，不回退
TRUNCATE TABLE Customer
-- 慢速清空表记录: 清空所有客户的数据，可回退
DELETE * FROM  Customer
-- 彻底删除表格: 把客户表的数据/客户表都根除
DROP TABLE Customer

3️⃣查看表格操作: VIEW 视图，只是一种虚拟表(不存储在内存中)

-- 创建虚拟表格CustomerAccountView视图
CREATE VIEW CustomerAccountView AS
SELECT Customer.CustomerID, Account.AccountNumber
FROM Customer JOIN Account 
ON Customer.CustomerID = Account.Customer_CustomerID;

-- 通过视图来查询数据，就像查询普通表格一样，简化了查询
SELECT * FROM CustomerAccountView;

$\text{2.1.2. DCL Commands:}$ 用户与权限

1️⃣创建/删除用户: CREATE USER/DROP USER

-- 创建用户，初始密码为123
CREATE USER 'john'@'localhost' IDENTIFIED BY '123';
-- 删除john用户
DROP USER 'john'@'localhost';

2️⃣分配/撤销用户权限: GRANT / REVOKE

-- 授予john在mydatabase数据库Table1表的SELECT和INSERT权限
GRANT SELECT,INSERT ON mydatabase.Table1 TO 'john'@'localhost';
-- 撤销john在mydatabase数据库Table1表的INSERT权限
REVOKE INSERT ON mydatabase.Table1 FROM 'john'@'localhost';

3️⃣设置密码: SET PASSWORD

-- 设置新密码为0123
SET PASSWORD FOR 'john'@'localhost' = PASSWORD('0123');

$\text{2.1.3. Other: Database Administration}$

1️⃣意外删除的恢复: BACKUP TABLE/RESTORE TABLE(二者其实并非标准$\text{SQL}$命令)

2️⃣展示表格模式($\text{Scheme}$): DESCRIBE <表名>

CREATE TABLE Customer (
CustomerID INT AUTO_INCREMENT PRIMARY KEY,
CustFirstName VARCHAR(45),
DateOfBirth DATE
);
-- 查看Customer表的结构，结果如下表
DESCRIBE Customer

$\text{Field}$	$\text{Type}$	$\text{Null}$	$\text{Key}$	$\text{Default}$	$\text{Extra}$
$\text{CustomerID}$	$\text{INT}$	$\text{NO}$	$\text{PRI}$	$\text{NULL}$	$\text{auto\_increment}$
$\text{CustFirstName}$	$\text{VARCHAR(45)}$	$\text{YES}$		$\text{NULL}$
$\text{DateOfBirth}$	$\text{DATE}$	$\text{YES}$		$\text{NULL}$

3️⃣USE <db_name>: 挑选进入哪个数据库来操作

$\text{2.2. SQL}$ 语法特点(大小写问题)

1️⃣$\text{SQL}$的属性名大小/关键词写不敏感($\text{Case Inseneitive}$)，但一般大写。如下两段查询含义相同

SELECT * FROM Furniture WHERE 
(Type = 'Chair' AND Colour = 'Black') OR (Type = 'Lamp' AND Colour = 'Black');
select * from furniture where 
(type = 'Chair' and colour = 'Black') or (type = 'Lamp' and colour = 'Black');

3️⃣$\text{SQL}$的表名区分大小写

$\text{3. SQL Core: DML Commands}$

$\text{3.1. }$变更表中内容

$\text{3.1.1. Insert Data:}$ Insert Into

0️⃣示例表格

$\text{CustID}$	$\text{FirstName}$	$\text{MiddleName}$	$\text{LastName}$	$\text{BusinessName}$	$\text{CustType}$
$\text{1}$	$\text{Peter}$	$\text{NULL}$	$\text{Smith}$	$\text{NULL}$	$\text{Personal}$
$\text{2}$	$\text{James}$	$\text{NULL}$	$\text{Jones}$	$\text{JJ}$	$\text{Company}$
$\text{3}$	$\text{NULL}$	$\text{NULL}$	$\text{Smythe}$	$\text{NULL}$	$\text{Company}$

1️⃣显式插入：指名要给哪几个属性插入数据

-- 一次性可以插入一条数据
INSERT INTO Customer (FirstName, LastName, CustType) VALUES 
("Peter", "Smith", 'Personal');

-- 也可以多条
INSERT INTO Customer (FirstName, LastName, CustType) VALUES 
("Peter" , "Smith" , 'Personal'),
("Jamnes", "Jones" , 'Company' ),
(""      , "Smythe", 'Company' );

2️⃣隐式插入：省略具体的属性，将提供的值按顺序依次插入属性

-- 插入第一条数据，DEFAULT子句会让CustID从0开始自动+1(此处+1后CustID=1)
INSERT INTO Customer VALUES 
(DEFAULT, "Peter", "", "Smith", "", 'Personal');

-- 一次性插入多条数据
INSERT INTO Customer VALUES  
(DEFAULT, "Peter", ""  , "Smith" , ""  , 'Personal');
(DEFAULT, "James", NULL, "Jones" , "JJ", 'Company' );
(DEFAULT, ""     , NULL, "Smythe", ""  , 'Company' );

3️⃣从其他表格插入数据

-- 将所有Customer中的记录，全部插入NewCustomer中
INSERT INTO NewCustomer SELECT * FROM Customer;

👽REPLACE和INSERT功能几乎一致，区别仅仅在于，当待插入记录的$\text{PK}\underset{冲突}{\overset{相同}{\leftrightarrow }}$已存在记录的$\text{PK}$

1. INSERT：操作费费，插入失败
2. REPLACE：操作合法，并且待插入记录覆盖冲突的已存在记录

$\text{3.1.2. Changes Existing Data:}$ UPDATESET 结构

1️⃣UPDATE 示例

-- Block1: 所有薪水小于100000的涨薪5%
UPDATE Salaried SET AnnualSalary = AnnualSalary * 1.05
WHERE AnnualSalary <= 100000;
-- Block2: 所有薪水大于100000的涨薪10%
UPDATE Salaried SET AnnualSalary = AnnualSalary * 1.10
WHERE AnnualSalary > 100000;

1. 子句执行的顺序会影响结果
2. 没有WHERE子句时，更新会应用到表格的每一行

2️⃣CASE $\text{Command}$优化示例

UPDATE Salaried SET AnnualSalary = 
CASE 
    WHEN AnnualSalary <= 100000 THEN AnnualSalary * 1.05
    ELSE AnnualSalary * 1.10    
END;

$\text{3.1.3. Deleting Existing Data:}$ DELETE

1️⃣简答例子

-- 删除表中所有记录，危险操作
DELETE FROM Employee
-- 删除表中满足条件的操作
DELETE FROM Employee WHERE Name = "Grace"

2️⃣删除的外键约束

约束子句	尝试操作	执行操作
ON DELETE CASCADE	$\text{A}$表尝试删除一行	操作总被允许，引用该行的$\text{B}$表行都删除
ON DELETE RESTRICT	$\text{A}$表尝试删除一行	如果改行被$\text{B}$表引用，则删除操作被禁止

CREATE TABLE B (
Bid INT PRIMARY KEY,
Bname VARCHAR(50),
Aid INT,
FOREIGN KEY (Aid) REFERENCES A(Aid) ON DELETE CASCADE
FOREIGN KEY (Aid) REFERENCES A(Aid) ON DELETE RESTRICT  -- 二选一
);

$\text{3.2. }$查询表中内容: SELECT FROM + XXX

$\text{3.2.1. }$SELECT有关结构

1️⃣最基本结构：

1. 投影，原理详见关系代数

   SELECT * FROM Student; -- 选中所有列, Heap Scan
   SELECT age FROM Student; -- 选中年龄这一列，但是SQL操作中默认不去除重复项
   

2. 关于DISTINCT，其后所有属性都要删除重复项

   SELECT DISTINCT age FROM Student; -- 选中年龄这一列，删除重复项
   SELECT DISTINCT age,name FROM Student; -- 年龄+姓名的组合要DISTINCT,删除重复项
   

2️⃣聚合函数$\text{Aggregare Function}$

1. 概述

   $\text{Function}$	$\text{Description}$
   AVG()	$\text{Average Value}$
   MIN()	$\text{Minimum Value}$
   MAX()	$\text{Maximum Value}$
   COUNT()	$\text{Number of Values}$(行数)
   SUM()	$\text{Sum of Values}$

2. 示例

   $\text{CustomerID}$	$\text{AccountID}$	$\text{Balance}$
   $\text{1}$	$\text{1}$	$\text{200}$
   $\text{1}$	$\text{2}$	$\text{100}$
   $\text{2}$	$\text{1}$	$\text{400}$
   $\text{3}$	$\text{1}$	$\text{300}$

   • COUNT示例

     SELECT COUNT(*) FROM Customer; -- 表中有多少Tuples
     SELECT COUNT(CustomerID) FROM Customer; -- 多少个CustomerID
     SELECT COUNT(DISTINCT CustomerID) FROM Customer; -- 多少个Uniqe的ID
     

     $\text{COUNT(CustomerID)}$	\	$\text{COUNT(DISTINCT CustomerID)}$
     $\text{4}$	\	$\text{3}$

   • AVG/MAX/MAX示例

     SELECT AVG(Balance) FROM Account; -- 所有账户余额的平均
     SELECT MAX(Balance) FROM Account WHERE CustomerID=1; -- 用户1最高账户余额
     SELECT SUM(Balance) FROM Account GROUP BY CustomerID; -- 各用户账户总余额
     

     $\text{AVG(Balance)}$	$\text{/}$	$\text{MAX(Balance)}$	$\text{/}$	$\text{SUM(Balance)}$
     $\text{250}$	$\text{/}$	$\text{200}$	$\text{/}$	$\text{300}$
     $\text{/}$	$\text{/}$	$\text{/}$	$\text{/}$	$\text{400}$
     $\text{/}$	$\text{/}$	$\text{/}$	$\text{/}$	$\text{300}$

3. SUM()补充：
   • 当用于数值类型属性时，会遍历每行求出并返回总和
   • 当用于布尔类型属性时，会遍历每行(布尔真$\text{=1/}$布尔假$\text{=0}$)，返回总和

3️⃣重命名子句AS $\text{Clause}$

SELECT CustType, COUNT(CustomerID) FROM Customer -- 不重命名
SELECT CustType, COUNT(CustomerID) AS Count FROM Customer -- 重命名为Count

不重命名	$\text{CustType}$	$\text{Count(CustomerID)}$	重命名	$\text{CustType}$	$\text{Count}$
$\text{/}$	$\text{Type1}$	$\text{3}$	$\text{/}$	$\text{Type1}$	$\text{3}$
$\text{/}$	$\text{Type2}$	$\text{6}$	$\text{/}$	$\text{Type2}$	$\text{6}$

4️⃣合并列子句CONCAT: 将两个不同的列，合并为一列

SELECT CONCAT(FirstName, LastName) AS FullName
FROM Name

$\text{3.2.2. }$FROM有关结构

1️⃣最基本功能：选定要操作的表格

SELECT * FROM R

2️⃣跨表格操作：$\text{Cross-Product/Join}$，原理详见关系代数部分

1. $\text{Cross-Product}$

   SELECT * FROM R, S;	-- Cross product between R, S
   

2. $\text{Join}$

   SELECT * FROM R NATURAL JOIN S -- Nature Join
   
   SELECT * FROM R INNER JOIN S ON R.sid < S.sid; -- Inner Join
   SELECT * FROM R INNER JOIN S ON R.sid = S.sid; -- Equi Join
   
   SELECT * FROM R LEFT OUTER JOIN S ON R.sid = S.sid;  -- Left Outer Join 
   SELECT * FROM R RIGHT OUTER JOIN S ON R.sid = S.sid; -- Right Outer Join 
   SELECT * FROM R FULL OUTER JOIN S ON R.sid = S.sid;  -- Full Outer Join 
   

$\text{3.2.3. }$+XXX有关结构

$\text{3.2.3.1. }$WHERE 有关结构

1️⃣WHERE $\text{Clause:}$ 本是上是一种$\text{Selection}$操作，过滤满足条件

SELECT * FROM Student WHERE Student.rating >= 9;

2️⃣LIKE $\text{Clause:}$ 与WHERE配合使用，实现字符串的匹配==(约等于)==

1. 匹配的关键字

   $\text{1}$个不确定的字符	$\text{0-M}$个不确定的字符
   $\text{\_}$	$\text{\%}$

2. 匹配子句示例

   $\text{Clause}$	CustomerName $\text{Maches}$	示例
   WHERE CustomerName LIKE 'a%'	以$\text{a}$开始的	axxxxx
   WHERE CustomerName LIKE '%a'	以$\text{a}$结束的	xxxxxa
   WHERE CustomerName LIKE '%a%'	包含$\text{a}$的	xxaxxx
   WHERE CustomerName LIKE '_a%'	$\text{a}$在第二位的	xaxxxx
   WHERE CustomerName LIKE 'a_%_%'	以$\text{a}$开始，后至少两字符	axx/axxx
   WHERE CustomerName LIKE 'a%o'	以$\text{a}$开始$\text{o}$结尾	axxxxo

3. $\text{SQL}$示例

   SELECT CustLastName FROM Customer WHERE CustLastName LIKE "Sm%"
   -- 匹配的会有Smith/Smyth/Smize......
   

$\text{3.2.3.2. }$GROUP BY有关结构

1️⃣GROUP BY $\text{Clause:}$

1. 讲记录根据一个/多个属性，分为若干小组
2. 通常和聚合函数结合使用，为每个小组计算出独立结果
3. 示例

   SELECT CustID, AVG(Balance) AS AveBalance
   FROM Account
   GROUP BY CustID;
   

   $\text{CustID}$	$\text{Account}$	$\text{Balance}$	$\underset{查询}{\overset{结果}{\to }}$	$\text{CustID}$	$\text{AveBalance}$
   $\text{1}$	$\text{101}$	$\text{500}$	$\text{/}$	$\text{1}$	$\text{325}$
   $\text{1}$	$\text{102}$	$\text{150}$	$\text{/}$	$\text{2}$	$\text{250}$
   $\text{2}$	$\text{103}$	$\text{200}$	$\text{/}$	$\text{3}$	$\text{450}$
   $\text{2}$	$\text{104}$	$\text{300}$	$\text{/}$	$\text{/}$	$\text{/}$
   $\text{3}$	$\text{105}$	$\text{450}$	$\text{/}$	$\text{/}$	$\text{/}$

4. 复杂一些的例子：GROUP BY <复合属性>

   SELECT Subject, Semester, COUNT(*) FROM Example_Table
   GROUP BY Subject, Semester; -- Subject, Semester都相同才能分为一类
   

   $\text{Subject}$	$\text{Semester}$	$\text{Attendee}$	$\underset{查询}{\overset{结果}{\to }}$	$\text{Subject}$	$\text{Semester}$	$\text{COUNT(*)}$
   $\text{ITB001}$	$\text{1}$	$\text{John}$	$\text{/}$	$\text{ITB001}$	$\text{1}$	$\text{3}$
   $\text{ITB001}$	$\text{1}$	$\text{Bob}$	$\text{/}$	$\text{ITB001}$	$\text{2}$	$\text{2}$
   $\text{ITB001}$	$\text{1}$	$\text{Mickey}$	$\text{/}$	$\text{IMK114}$	$\text{1}$	$\text{2}$
   $\text{ITB001}$	$\text{2}$	$\text{Jenny}$	$\text{/}$	$\text{/}$	$\text{/}$	$\text{/}$
   $\text{ITB001}$	$\text{2}$	$\text{James}$	$\text{/}$	$\text{/}$	$\text{/}$	$\text{/}$
   $\text{IMK114}$	$\text{1}$	$\text{John}$	$\text{/}$	$\text{/}$	$\text{/}$	$\text{/}$
   $\text{IMK114}$	$\text{1}$	$\text{Erica}$	$\text{/}$	$\text{/}$	$\text{/}$	$\text{/}$

2️⃣HAVING $\text{Clause:}$ 适用于GROUP BY体质的WHERE，只能和GROUP BY一起出现

1. 作用：作为聚合函数的补充，用于筛选GROUP BY出来的组
2. 对比：

   HAVING $\text{Clause}$	WHERE $\text{Clause}$
   后接关系$\text{Attribute}$有关条件	后接聚合函数(数结)果有关条件

3. 示例：注意区分WHERE和HAVING

   $\text{CustID}$	$\text{Account}$	$\text{Balance}$
   $\text{1}$	$\text{101}$	$\text{500}$
   $\text{1}$	$\text{102}$	$\text{150}$
   $\text{2}$	$\text{103}$	$\text{200}$
   $\text{2}$	$\text{104}$	$\text{300}$
   $\text{3}$	$\text{105}$	$\text{450}$

   • WHERE <条件表达式>：直接过滤原始的数据(过滤数据)

     SELECT CustID, Balance FROM Customer WHERE Balance > 400;
     

   • HAVING <条件表达式>：对分组的结果进行过滤(过滤组)

     查询	$\text{CustID}$	$\text{AveBalance}$	HAVING后	$\text{CustID}$	$\text{AveBalance}$
     $\text{/}$	$\text{1}$	$\text{325}$	$\text{/}$	$\text{1}$	$\text{325}$
     $\text{/}$	$\text{2}$	$\text{250}$	$\text{/}$	$\text{3}$	$\text{450}$
     $\text{/}$	$\text{3}$	$\text{450}$	$\text{/}$	$\text{/}$	$\text{/}$

     SELECT CustID, AVG(Balance) AS AveBalance FROM Account
     GROUP BY CustID HAVING AveBalance > 300;
     

$\text{3.2.3.3. }$ORDER BY 有关结构

1️⃣ORDER BY $\text{Clause:}$

1. 将查询得到的结果按照某一属性排序

   SELECT Name, Type FROM Customer ORDER BY Name; -- 按名字(字典序)默认升序
   SELECT Name, Type FROM Customer ORDER BY Name ASC;  -- 升序
   SELECT Name, Type FROM Customer ORDER BY Name DESC; -- 降序
   

2. 将查询得到的结果按照符合属性排序

   SELECT Name, Type FROM Customer
   ORDER BY Name DESC, Type ASC;
    -- 先按Name降序排序
    -- 再对于Name相同的Tuple，按照Type升序排序
   

2️⃣LIMIT/OFFSET $\text{Clause:}$

1. 含义

   $\text{Clause}$	功能
   LIMIT N	选取排序结果的前$\text{N}$个
   OFFSET M	跳过$\text{M}$个排序结果
   LIMIT N OFFSET M	跳过排序结果的前$\text{M}$个，依次选取后面的$\text{N}$个

2. 示例：
   

   SELECT Name, Type FROM Customer ORDER BY Name LIMIT 5; -- 红色
   SELECT Name, Type FROM Customer ORDER BY Name LIMIT 5 OFFSET 3; -- 绿色
   

$\text{3.3. }$查询表中内容: $\text{Sub/Nesting Query}$

$\text{3.3.1. Subquery}$结构

0️⃣概述

1. SELECT结构相当于一个查询，在一个SELECT中插入另一个SELECT，则后者就是前者子查询
2. 执行顺序：先执行子查询$\to$将子查询结果传给主查询$\to$执行主查询
3. 相关子查询：子查询可以直接使用主(外层)查询的列/值

1️⃣在SELECT字句中插入子查询，一般使用AS重命名子查询返回的列

SELECT 
    name,
    (SELECT COUNT(*) FROM orders) AS order_count
FROM customers;

2️⃣在FROM字句中插入子查询，子查询此时相当于一个临时表，所以必须使用AS重命名

SELECT tmp.average_sales
FROM  (SELECT AVG(amount) AS average_sales FROM sales) AS tmp;

3️⃣在WHERE子句中

SELECT name, age
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

4️⃣在EXISTS中，返回布尔值的特殊类子查询

SELECT name
FROM products
WHERE EXISTS (SELECT 1 FROM inventory WHERE inventory.quantity > 0);

$\text{3.3.2. Subquery}$有关子句

0️⃣示例表格

1. $\text{Artefact/Seller/Buyer}$表

   $\text{AID}$	$\text{Name}$	$描述$	\small\text{\\}	$\text{SID}$	$\text{Name}$	$\text{Phone}$	\small\text{\\}	$\text{BID}$	$\text{Name}$	$电话$
   $\text{1}$	$\text{Vase}$	$\text{Old}$	\small\text{\\}	$\text{1}$	$\text{Abby}$	$\text{0232}$	\small\text{\\}	$\text{1}$	$\text{Magg}$	$\text{0333}$
   $\text{2}$	$\text{Knife}$	$\text{Old}$	\small\text{\\}	$\text{2}$	$\text{Ben}$	$\text{0311}$	\small\text{\\}	$\text{2}$	$\text{Nicole}$	$\text{0444}$
   $\text{3}$	$\text{Pot}$	$\text{Old}$	\small\text{\\}	$\text{3}$	$\text{Carl}$	$\text{0333}$	\small\text{\\}	$\text{3}$	$\text{Oleg}$	$\text{0555}$

2. $\text{Offer}$表

   $\text{AID}$	$\text{SID}$	$\text{BID}$	$\text{Date}$	$\text{Amount}$	$\text{Acceptance}$
   $\text{1}$	$\text{1}$	$\text{1}$	$\text{2012-06-20}$	$\text{81223.23}$	$\text{N}$
   $\text{1}$	$\text{1}$	$\text{2}$	$\text{2012-06-20}$	$\text{82223.23}$	$\text{N}$
   $\text{1}$	$\text{2}$	$\text{1}$	$\text{2012-06-20}$	$\text{19.95}$	$\text{N}$
   $\text{2}$	$\text{2}$	$\text{2}$	$\text{2012-06-20}$	$\text{23.00}$	$\text{N}$

1️⃣IN/NOT IN $\text{Clause:}$ 记录是否在子查询的结果中

SELECT * FROM Buyer
WHERE BID IN (SELECT BID FROM Offer WHERE AID = 1)

1. 子查询结果：BuyerID = 1/2，主查询结果

   $\text{BID}$	$\text{Name}$	$\text{Phone}$
   $\text{1}$	$\text{Magg}$	$\text{0333}$
   $\text{2}$	$\text{Nicole}$	$\text{0444}$

2. 基于$\text{Join}$的优化：执行的效率会更高

   ELECT Buyer.*
   FROM Buyer JOIN Offer ON Buyer.BID = Offer.BID
   WHERE Offer.AID = 1;
   

2️⃣ANY/ALL/EXISTS

1. ANY：满足至少一个内部条件，实际上可以把所有的IN改为=ANY(至少一个等于)

   SELECT empno, sal FROM emp
   WHERE sal > ANY (SELECT....返回:Tuple1, Tuple2, Tuple3);
   
   -- equals to
   SELECT empno, sal FROM emp
   WHERE sal > Tuple1 OR sal > Tuple2 OR sal> Tuple3;
   

2. ALL：满足所有内部条件

   SELECT empno, sal FROM emp
   WHERE sal > ALL (SELECT....返回:Tuple1, Tuple2, Tuple3);
   
   -- equals to
   SELECT empno, sal FROM emp
   WHERE sal > Tuple1 AND sal > Tuple2 AND sal> Tuple3;
   

3️⃣EXISIS：内部查询会返回$\text{True/False}$，从而决定外查询执行/不执行

SELECT * FROM Buyer WHERE EXISTS(
SELECT * FROM Offer -- 内查询中，SELECT什么不重要，因为最后只会输出True/False
WHERE Buyer.BuyerID = Offer.BuyerID AND ArtefactID = 1
)

1. 先遍历Buyer(外表)，对其每一行向内Offer(内表)表查询是否有行满足条件

    Buyer.BuyerID = Offer.BuyerID AND ArtefactID = 1
   

2. 如果至少有一行条件满足，则执行下列外查询，把满足条件的行打印出来

    SELECT * FROM Buyer
   

3. 一行行遍历$\text{+}$打印后，结果为:

   $\text{BID}$	$\text{Name}$	$\text{Phone}$
   $\text{1}$	$\text{Magg}$	$\text{0333}$
   $\text{2}$	$\text{Nicole}$	$\text{0444}$

$\text{3.4. }$其他$\text{SQL}$子句

1️⃣COALESCE(): 返回列表中第一个非NULL值

-- 遍历每一行，如果A2非空则返回A2，如果A2空则返回''(首个非空值)
SELECT A1, COALESCE(A2, '') AS A2New, A3 FROM users;

初始表格	$\text{A1}$	$\text{A2}$	$\text{A3}$	查询结果	$\text{A1}$	$\text{A2New}$	$\text{A3}$
$\text{/}$	$\text{John}$	$\text{NULL}$	$\text{Doe}$	$\text{/}$	$\text{John}$		$\text{Doe}$
$\text{/}$	$\text{Jane}$	$\text{A.}$	$\text{Smith}$	$\text{/}$	$\text{Jane}$	$\text{A.}$	$\text{Smith}$
$\text{/}$	$\text{Emily}$	$\text{NULL}$	$\text{Davis}$	$\text{/}$	$\text{Emily}$		$\text{Davis}$

2️⃣LENGTH(): 求出一段字符串的长度

ORDER BY LENGTH(Steing)      -- String长度从小到大排列
ORDER BY LENGTH(Steing) ASC  -- String长度从小到大排列
ORDER BY LENGTH(Steing) DEC  -- String长度从大到小排列

3️⃣CASE WHEN语法

1. 语法

   CASE WHEN <条件表达式> THEN <条件满足时返回这个值> <条件不满足时返回这个值> END
   

2. 不重命名示例

   SELECT 
   	id,
   	amount,
   	CASE WHEN amount > 100 THEN 'High' ELSE 'Low' END
   	CASE WHEN amount > 100 THEN 'High' ELSE 'Low' END AS class -- 重命名
   FROM orders;
   

   不重命名	id	CASE	amount	重命名	id	CASE	class
   $\text{/}$	$\text{1}$	$\text{High}$	$\text{150}$	$\text{/}$	$\text{1}$	$\text{High}$	$\text{150}$
   $\text{/}$	$\text{2}$	$\text{Low}$	$\text{80}$	$\text{/}$	$\text{2}$	$\text{Low}$	$\text{80}$
   $\text{/}$	$\text{3}$	$\text{High}$	$\text{120}$	$\text{/}$	$\text{3}$	$\text{High}$	$\text{120}$

4️⃣$\text{Set Operation:}$ 目的是合并两个查询结果，原理详见关系代数

SELECT sid, sname, rating, age FROM S1 -- 此操作去除重复行
UNION
SELECT sid, sname, rating, age FROM S2;
SELECT sid, sname, rating, age FROM S1 -- 此操作保留重复行
UNION ALL
SELECT sid, sname, rating, age FROM S2;

$\text{4. }补充\text{Data Type}$

$\text{4.1. }$字符类型

1️⃣概述

类型	存储	备注
CHAR(M)	定长字符串	$\text{M∈(0,255)}$表示字符长度
VARCHAR(M)	变长字符串	$\text{M∈(1,65535)}$
BIT/BOOL/CHAR	同CHAR(1)	$\text{N/A}$
BLOB	二进制文件==(音视频)==	存储最多$\text{65535}$字节的$\text{0/1}$序列
TEXT	大段文字	最多$\text{65535}$个字符
ENUM(a,b,c...)	列表(多选一)	列表中最多$\text{65535}$个成员，索引从左到右/从$\text{0}$开始
SET(a,b,c...)	列表(多选多)	列表中最多$\text{64}$个成员

2️⃣补充

1. CHAR(M)中对于定长字符，如果长度不够则会用空格填充

   Name CHAR(5) -- 如果赋值Name='Kan'则实际存储Name='Kan<空格><空格>'
   

2. TEXT中说的是最多$\text{65535}$字符，不是字节；一个字符可占$\text{1-4}$字节
3. SET(a,b,c...)示例

   SET(a1,a2,a3) -- 可存储<空>/a1/a1a2/a1a2a3
   

$\text{4.2. }$整数类型

1️⃣概述

$\text{Type}$	$\text{Signed Range}$	$\text{Unsigned Range}$
TINYINT[(M)]	$-128\to 127$	$0\to 255$
SMALLINT[(M)]	$-32768\to 32767$	$0\to 65535$
INT[(M)]/INTEGER[(M)]	…	…
BIGINT[(M)]	…	…

2️⃣示例：TINYINT(4)

1. 数字显示宽度为$\text{4}$
2. 如果存储的数字为$\text{12}$，则会显示为$\text{0012}$

$\text{4.3. }$实数类型

1️⃣概述

$\text{Type}$	$\text{Precision}$	存储类型
FLOAT[(M,D)]	单精度	$\text{Binary}$
DOUBLE[(M,D)]	双精度(范围更大)	$\text{Binary}$
REAL[(M,D)]	双精度(范围更大)	$\text{Binary}$
DECIMAL[(M[,D])]	定点类型	字符串

2️⃣参数含义

1. M: 小数点左右加起来，一共有多少位
2. D: 小数位数

$\text{4.4. Data \& Time Type}$

$\text{Type}$	$\text{Format}$	$\text{Range}$
DATA	$\text{YYYY-MM-DD}$	$\text{1000-01-01}\to \text{9999-12-31}$
TIME	$\text{HH:MM:SS}$	$\text{-838:59:59}\to \text{838:59:59}$
DATETIME	$\text{YYYY-MM-DD HH:MM:SS}$	$\text{1000-01-01 00:00:00}\to \text{9999-12-31 23:59:59}$
TIMESTAMP	$\text{YYYY-MM-DD HH:MM:SS}$	$\text{1970-01-01 00:00:00 UTC}\to \text{2037}$
YEAR[4]	$\text{YYYY}$	$\text{1901}\to \text{2155}$

1️⃣TIMESTAMP是$\text{DB}$记录的时间点，会将时间转换为当地时间

2️⃣NOW()函数会以$\text{YYYY-MM-DD HH:MM:SS}$格式返回当前时间