SQL 学习

SQL基础

SQL 的 domain type (数据类型)

char(n): 固定长度的char string （长度为n）
varchar(n): 可变长度的char string（长度最大为n）
int: int32
smallint: int16
numeric(p, d): 固定小数点数, 其意义为该小数长为 p，其中小数部分长度固定位 d。 eg: numeric(3, 1) 可以表示 44.5, 但是不能表示 1.32， 444.5 这种数
real: float32
double precision: float64

创建table

基础创建语法

/**多行注释方法
 * 注意，我们现在是直接创建了 relation instance 而不是 relation schema
 * 再重申一次，一个实例 r(R) 中, r是实例名称, R 是 schema 名称 !!!!! 
*/
create table r ( --这是单行注释，这里我们创建了一个 r instance （或者叫table）
ID char(5),
name varchar(20),
dept_name varchar(20),
salary numeric(8, 2) -- 注意最后一行不能有逗号！   
); -- 注意分号

Integrity Constrains 完整性约束语法

在SQL中可以非常方便的插入完整性约束statment, 这些constrain会在更新，插入，删除时被SQL自动检验，SQL支持的完整性约束包括:

1.primary key (A1, A2, ..., An): 主键约束, 以上语法表示设定 (A1, A2, ..., An) 为表格的主键, SQL会自动检查主键是否成立

2.foreign key (A1, A2, ..., An) reference f: 外键约束, 以上语法表示设定 (A1, A2, ..., An) 为来自表格f的外键, SQL会自动确保这些attribute的字段一定会在表格f中出现

3.not null: 非空约束, 表示不允许 not null 所在的那一行为null

4.unique: 唯一性约束, 确保一个或一组字段的值在整个表中是唯一的

5.check: 检查约束, 确保字段中的数据满足特定的条件。例如，年龄字段可能要求年龄必须大于18。

eg:
1
2
3
4
5
6
7
8
9
10
/* unique和check约束会在之后提及*/
create table r (
ID char(5),
name varchar(20) not null, -- 限制名称不可以是null
dept_name varchar(20),
salary numeric(8, 2),  
primary key (ID, name), -- 会自动检查 (id, name) 是否满足主键
foreign key (name) references student (Sname), -- 注意要点明 references 表中对应的 attribute
foreign key (dept_name) references department (Dname) -- 会自动检查department中是否有 dept_name 字段 
); 
注意要点，在设定 foreign key 和 primary key 的时候，在括号中输入的所有attribute会被当做一整个attribute set, 所以:
1
primary key (ID, name, salary) -- 首先，一个表中只可以有一个primarykey，否则报错，然后这个是符合primarykey，表示 (ID, name, salary) 共同决定一行
注意要点： 可以看以下两个例子，看看他们是不是等价的

statment 1:
1
foreign key (ID, name, salary) references stu (SID, Sname, Ssalary) -- 这里表示 (ID, name, salary) 是复合外键，当且仅当 (ID, name, salary) 这一个tuple出现在 stu 中时我们才会认定符合 foreign key 约束
statment 2:
1
2
3
4
/*这样 ID, name, salary 三个attribute相互独立，只要三者独立出现在 foreign key 中才行*/
foreign key (ID) references stu (SID)
foreign key (name) references stu (Sname)
foreign key (salary) references stu (Ssalary)
区别如下:
stu :

ID name salary

1 A 100

2 B 200

3 C 300

对于 statment1， (1, A, 100) 满足约束，但是 (1, B, 300) 不满足，因为即使每个元素都在stu中出现过，但是这个tuple，复合外键不在

对于 statment2，以上两个tuple都满足越苏，因为只要每个 attribute 独立满足即可

ID	name	salary
1	A	100
2	B	200
3	C	300

更新 table 语法

假定一下更新操作是对于 instructor 表操作的:

create table instructor (
ID char(5),
name varchar(20) not null,
dept_name varchar(20),
salary numeric(8, 2),  
primary key (ID, name),
foreign key (name) references student (Sname), -- 会自动检查department中是否又 dept_name 字段 
foreign key (dept_name) references department (Dname) -- 注意，不同的references需要分开，但是可以相同的ref可以共用
);

Insert 插入语法

1	insert into instructor values ('1021', 'Smith', 'Bio', 66000); -- 依据schema的格式插入数据

`Delete 删除所有的tuple`

1	delete from instructor; -- 删除instructor中所有的tuple！但是还是保留 instructor 的 schema

`Drop Table删除表 (连带着schema一起删了)`

1	drop table instructor; -- 删除instructor的schema，比 delete 更绝对！

`Alter 添加/删除 attribute (列)`

在创建后还可以通过 alter 命令来添加/删除列，添加的列会默认填充为 null

alter add 添加
1
alter table instructor add country varchar(10); -- 添加一列教授的国籍
alter drop 删除
1
alter table instructor drop country; -- 删除教授的国籍
总结: SQL 中 delete 和 drop 有区别，delete 是删除某个schema中所有的项目，保留schema而 drop是连schema一起删除！！

Query 查询请求

Basic Query Structure 标准查询结构

SQL中的标准查询结构应该为:
1
2
3
select A1, A2, ..., An -- 选取了 A1 到 An 的 attributes
from r1, r2, r3, ..., rm -- 从 r1 到 rm 这些table中选取
where P; -- 选择条件 ( 注意这三个本来就是一行里的，所以只在最后加分号)
这里面 from 的多个表是通过Cartesian Product 来合并的，where 是用来条件选择的

在relational algebra中这个操作可以表现为:

$\Pi_{A1, A2, ..., An} (\sigma_{P} (r1 \times r2 \times ... \times rm))$

eg:
1
2
3
select A1, A2, ..., An
from r1, r2, r3, ..., rm
where r1.key = r2.foreign_key AND r2.key = r3.foreign_key AND ...; 

Select clause 选择子句

select clause的作用是选择一个表中的某些行，然后返回, 和标准查询结构只差一个where 条件选择

返回的意思一般是通过用户的io设备打印出来，但是各大语言都提供了捕获select返回值然后赋值给变量的方法

select clause 用法:
1
2
select A1, A2, ..., An
from r1, r2 ..., rn; -- 少一个 where
select * 表示选择所有列:
1
2
select * -- join之后所有的列
from r1, r2 ..., rn; -- 少一个 where
在relational algebra中这个操作可以表现为:

$\Pi_{A1, A2, ..., An} (r1 \times r2 \times ... \times rm)$

补充: python 中捕获 select 返回值的方法:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
import sqlite3

# 连接到 SQLite 数据库
conn = sqlite3.connect('example.db')
c = conn.cursor()

# 执行 SELECT 语句并存储结果
c.execute('SELECT * FROM employees')
rows = c.fetchall()

# rows 变量现在包含了查询结果
for row in rows:
    print(row)

# 关闭连接
conn.close()

`select` 语句中显式去重与不去重

select 语句并不会对tuple去重, 所以有 select distinct 语句来指示 SQL 自动去重

1.select distinct 自动去重:
1
2
select distinct dept_name
from insructor;
2.select all 显式要求不去重那么加不加 all 有什么区别呢？在 where 出现后才有区别，没有where 这两个没有区别！:
1
2
select distinct dept_name
from insructor;

`select` 的其他技巧

1.利用select来返回字面量:

1 2	select '124'; -- 这样他会直接返回值 '124', 可以打印或者被其他编程语言捕获 select 14.5; -- 浮点说默认double

2.利用select创建 单列结果集

1	select '124'as FOO; -- 建立一个名字叫 FOO 的单列结果集

3.创建一个长度与某个表单相同的 单列结果集

1
2
3

select 'A'
from instructor; -- 这里和instructor的唯一关系就是生成的结果集长度和instructor长度一致
/* 相当于我们创建了一个: (A, A, A, A, ..., A) 长度和 instructor 相同*/

`select` 中的运算

select 中可以插入代数运算符就像 + - * / 等，可以用于某个数据或者某个attribute：
1
2
select ID, name, salary/12 -- 获取月薪, 相当于自动帮你给每个 salary 数据 / 12 然后再返回给你
from instructor;
和 select ... as ... 结合可以用于重命名行（返回结果集）：
1
select ID, name, salary/12 as monthly_salary; -- 这样就返回的是 多列结果集
结果集本质就是临时表单，如何转正后文会提及

`where` 语句中的逻辑运算符

where 中的逻辑运算符叫做: and, or, not, <, <=, =, >=, >, <>, 其中 <> 表示不等于, 和 != 等价

eg:
1
2
3
select name
from instructor
where dept_name = 'Comp.Sci.' and salary > 80000;

`from` 语句中的重命名操作

核心: 用 as 操作来命名（as 是 SQL 中专门用于命名的语法）
eg:

1
2
3

select preq.name 
from Courses as cour, Courses as preq -- 把 Courses 重命名为两个不同表，然后执行笛卡尔积（课程 -- 先修课程） 
where cour.preq_id = preq.id and cour.preq_name = 'Data Base'; -- 选取先修课程名称为 'Data Base' 的课程

String Operations

MySQL支持正则表达式，但是其他SQL可能不支持，但是他们都支持通配运算符。在MySQL实践中可以直接用正则表达式！

String matching operator 通配运算符

0.like 关键字: 在 string 前面加上 like 可以让他变成通配运算string，否则通配运算符不成立！

1.percent, %: 在SQL中，% 可以匹配任意substring, 比如:
1
2
3
select name
from instructor
where name like '%dar%'; -- 选取任何名字中带有 'dar' 的instructor （记得加上 like！） 
2.underscore _: 下划线可以匹配任何character, 比如:
1
2
3
select name
from instructor
where name like 'ab\%cd_'; -- 所有名字叫: "ab%cd" 后面跟一个字符的，比如 "ab%cdA", 但是 "ab%cdAB"不是，因为 _ 只匹配一个字符！
注意，\% 转义符可以让 % 在like 通配字符串中变回普通字符, 类似latex中的作用

Ordering 排序

1.order by 是SQL内建排序的方法, 默认从小到大

eg:
1
2
3
select distinct name
from instructor
order by name;
2.可以通过desc 降序和 asc 升序 (default) 调整

eg:
1
2
3
select distinct name
from instructor
order by name desc;
3.可以进行多attribute排序（排序方法是首先基于第一个attribute排序，然后对于第一个attribute相同的情况下基于第二个attribute降序排序）

eg:
1
2
3
select distinct name
from instructor
order by A desc, B asc; --还可以分别指定排序方法
4.补充： where 条件排序通式:
1
2
3
4
5
select distinct name
from instructor
where P -- where 插入到中间
order by A desc, B asc --还可以分别指定排序方法
limit 10; -- 找前 n 个

(补充) Where 中的语法糖: `between` （是带等号的，即两边都是闭的interval）

where语法中 d <= c and b >= a 和 b between a and c 等价:

1
2
3

select distinct name
from instructor
where salary between 1000 and 2000; -- 等价于 salary <= 2000 and salary >= 1000;

Set Operation

在 relational algebra 中，我们有 $\cap$ , $\cup$ , $-$ 三个运算符。在SQL中也有所体现:

1. $\cap$ : intersect, eg:

1
2
3

(select distinct name from instructor where salary between 1000 and 1600) 
intersect 
(select distinct name from instructor where salary between 1500 and 2000); -- 选取收入在 [1500, 1600] 的人

2. $\cup$ : union, eg:

1
2
3

(select distinct name from instructor where salary between 1000 and 2000) 
union 
(select distinct name from instructor where salary between 10000 and 12000); -- 选取收入在 [1000, 2000] 的人 和 [10000, 12000] 的人

3. $-$ : except, eg:

1
2
3

(select distinct name from instructor where salary between 1000 and 2000) 
except
(select distinct name from instructor where salary between 1500 and 2000); -- 只选取 [1000, 1500) 的人

注意，和select不同，集合操作是默认去重的，所以如果要默认不去重，需要用到 all 关键字

1
2
3

intersect all
union all
except all

null 和 unknown

null

null值可以通过is null 和 is not null 来判断, eg:
1
2
3
select name
frome instructor
where selary is not null; --  is null 也一样的

unknown

2.unknown 值用于表示null的逻辑运算结果，eg:
1
2
3
5 < null -- 返回 unknown
null <> null -- unkown
null = null -- 只要有null参与就是 unknown
3.unknown 参与逻辑运算返回值判断逻辑是: 如果unknown无论是true还是false，他的结果都一样，那么就正常返回，如果不一样就返回unknown！
eg:
1
2
true or unknown -- 反正有true了，他肯定输出 true，所以返回true
false or unknown -- 这时候就会返回 unknown 了， 因为 unknown 当做 true 还是 false 都有问题！
4.where 语句把unknown当做false 看待！！

aggregate function 内建函数

数值内建函数

SQL 提供以下数值内建函数

/*其中 A 是某个 attribute（列）*/
avg(A)
max(A)
min(A)
sum(A)
count(A)

使用例:

1
2
3

select avg(salary) -- 获取在 Comp.Sci. 工作的教授的平均工资（返回一个只有一个值的结果集）
from instructor
where dept_name = 'Comp.Sci.'

补充: 特殊语法count(*) 可以用于获取这个表（条件选取）中tuple的个数

1
2
3

select count(*) -- 获取在 Comp.Sci. 工作的教授个数
from instructor
where dept_name = 'Comp.Sci.'

Group By

特殊内建函数: group by 用于对某个表格子集执行内建函数
1
2
3
select dept_name, avg(salary) as avg_dept_salary -- 获取在 Comp.Sci. 工作的教授个数
from instructor
group by dept_name; -- 相当于把每个department聚合在一起然后单独算平均薪资
如图:

<img src = https://cdn-us.imgs.moe/2024/03/17/pic_JYf07gE0Lu.png>

Having

Having 语句的作用: having 子句在SQL中用于对 group by 聚合后的结果集进行条件过滤。它与 where 子句类似，但主要区别在于 having 用于过滤聚合后的结果，而 where 子句是在数据聚合之前对原始数据进行过滤。
1
2
3
4
select dept_name, avg(salary) as avg_dept_salary -- 获取在 Comp.Sci. 工作的教授个数
from instructor
group by dept_name -- 相当于把每个department聚合在一起然后单独算平均薪资
having avg(salary) > 42000; -- 只选取平均薪资大于42000的部门

in(单列结果集), some(单列结果集), all(单列结果集) 子查询命令

这三个子句实现的就是在 where 中嵌套 select-from-where 逻辑（i.e. 子查询），然后返回一个bool值

in (也是一个非常好用的语法糖，但是比 between 好用很多，他可以实现 “灵活的or”)

in() 相当于一个"subquery"
in() 运算符在SQL中用于比较某个字段的值是否匹配列表中的任一值。它使你能够在where子句中指定多个可能的值，从而检查目标列中的值是否在这个指定的列表中。使用in()可以使查询更加简洁，尤其是在对多个可能值进行测试时，比起使用多个or条件。
这是in()运算符的基本语法：

>select column_names
>from table_name
>where column_name IN (value1, value2, ..., valueN); -- i.e. column name = v1 or column name = v2 or ....
/*注意，in后面跟的是一个单列结果集，i.e. 你可以从一个表中抽出一列来比对！！！！！*/

但是真正让 in 运算符变得不可或缺的是 in 后面可以跟单列结果集，这是or根本不可能实现的功能！

>select column_names
>from table_name
>where column_name in (select col from r where P); -- i.e. column name = v1 or column name = v2 or ....
/*注意，in后面跟的是一个单列结果集，i.e. 你可以从一个表中抽出一列来比对！！！！！*/

select distinct course_id
from section
where semester = 'Fall' and year = 2017 and course_id in ( -- 选取 2017Fall 中 2018Spring 也开的课
    select course_id
    from section
    where semester = 'Spring' and year = 2018 -- 不需要分号，因为你只是返回一个单列结果集
);

当然也可以用 not in
eg:

select distinct course_id
from section
where semester = 'Fall' and year = 2017 and course_id not in ( -- 选取 2017Fall 中 2018Spring 不开的课
    select course_id
    from section
    where semester = 'Spring' and year = 2018 -- 不需要分号，因为你只是返回一个单列结果集
);

some 与 all 子句 (实现`存在 exist` 和 `任意 any`) 一阶逻辑

some(单列结果集) 与 all() 和 in() 的用法是非常类似的，都是对后面跟着的一整个单列结果集进行操作只不过三者实现的逻辑不一样:
in() 实现的是属于逻辑，表示选取所有属于单列结果集的值
some() 实现的是一阶逻辑中的 exist, 表示选取所有单列结果集中存在比待选取值大/小/相等的值
all() 实现的就是any逻辑, 用法和 exist 相似
eg: 以下两个命令等价

/*以下两个命令等价*/

-- 命令1 --
select distinct T.name -- 仔细想想，是不是选择的就是比生物部门最小值大的人
from instructor as T, instructor as S -- 这里面 T 和 S 可以用于前文select选择！
where T.salary > S.salary and S.depat_name = 'Biology';

-- 命令2 --
select distinct name -- 比生物部门的最小值大
from instructor
where salary > some ( -- 比某些生物部门的人的薪水高就行（生物部门中存在比待选择值低的薪水）
    select salary
    from instructor
    where dept_name = 'Biology' -- 不需要分号，因为你只是返回一个单列结果集
);

/*all 也差不多，不过逻辑变成了any*/
-- 命令3 --
-- 命令2 --
select distinct name -- 比生物部门的最大值大
from instructor
where salary > all ( -- 比所有生物部门的人的薪水都要高！
    select salary
    from instructor
    where dept_name = 'Biology' -- 不需要分号，因为你只是返回一个单列结果集
);

返回值判断语句

这里包括 exist, unique

exist

exist(单列结果集): 结果集中有东西就返回 true, 否则 false
not exist(): 对返回的boolean值去反

select course_id
from section as S
where semester = 'Fall' and year = 2017 and exist ( -- exist: 后面跟的单列结果集中有东西就返回1
    select *
    from section as T
    where semester = 'Spring' and year = 2018 and S.course_id = T.course_id  -- 注意，主query的重命名可以用在子query上
>);

unique

unique(单列结果集): 结果集中没有重复就返回 true, 否则 false

not unique(): 对返回的boolean值去反

如果输入的单列结果集是空集，那么unique()会返回true！！！
1
2
3
4
5
6
7
select course_id
from section as S
where semester = 'Fall' and year = 2017 and not unique ( -- unique: 后面跟的单列结果集中没有重复就返回1
    select *
    from section as T
    where semester = 'Spring' and year = 2018 and S.course_id = T.course_id  -- 注意，主query的重命名可以用在子query上
>);

From 中的 Subquery

反正 select 语句返回的是一个结果集，即临时表，那么我们from的表也可以是一个结果集啊

eg:
1
2
3
4
5
6
select dept_name, avg_salary
from (select dept_name, avg(salary) as avg_salary -- from 一个多列结果集
      from instructor
      group by dept_name
)
where avg_salary > 42000;

Database 的修改

条件 Deletion

where不止可以用于查找，还可以用于条件删除

1 2	delete from instructor where dept_name = 'Finance'; -- 不加就是全删

注意 avg() 返回的是单个值！:

1 2	delete from instructor where salary < (select avg(salary) from instructor); -- 这里为什么可以直接用小于呢? 因为 avg(salary) 返回值是一个值，而不是一个结果集

条件 Insert

有条件的从一个表中选出一些tuple插入另一个表中

insert into instructor -- 后面其实可以更一个结果集，而不是单纯一个tuple
  select ID, name, dept_name, 18000 -- 选择 student 中 credit 大于 144 的作为 instructor
  from student
  where dept_name = 'Music' and total_cred > 144;

统一 Update

允许你对某一个attribute进行更新

1
2
3

update instructor
set salary = salary * 1.05
where salary < (select avg(salary) from instructor)

Case 条件语句

在SQL中，CASE 语句是一种灵活的条件表达式，它允许基于一系列条件来对数据进行转换或分类。CASE 语句可以在查询的 SELECT 部分、ORDER BY 部分以及其他允许表达式的地方使用。

CASE 语句是条件判断语句，和C++中的Switch差不多

CASE 语句有两种格式：

简单 CASE 表达式:
比较一个表达式与一系列的值，如果匹配成功，则返回对应的结果。
语法：
1
2
3
4
5
6
CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ...
    [ ELSE default_result ]
END
例如，根据员工的评分给出不同的加薪比率：
1
2
3
4
5
6
7
SELECT name, rating,
       CASE rating
           WHEN 'Excellent' THEN salary * 1.10
           WHEN 'Good' THEN salary * 1.05
           ELSE salary
       END AS new_salary
FROM employees;
搜索 CASE 表达式:
允许设置更复杂的条件。每个条件是一个独立的布尔表达式。
语法：
1
2
3
4
5
6
CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    [ ELSE default_result ]
END
例如，根据员工的工龄和评级给出不同的加薪比率：
1
2
3
4
5
6
7
SELECT name, years_of_service, rating,
       CASE
           WHEN years_of_service > 10 AND rating = 'Excellent' THEN salary * 1.15
           WHEN years_of_service > 5 THEN salary * 1.10
           ELSE salary * 1.05
       END AS new_salary
FROM employees;
在这些例子中，CASE 语句评估给定的条件，一旦找到一个满足的条件，它就返回对应的结果，然后停止进一步的检查。如果没有任何条件满足，而且存在 ELSE 分支，那么就返回 ELSE 中的结果；如果没有提供 ELSE，则返回 NULL。

CASE 语句非常有用于基于行内数据的条件逻辑，特别是在数据转换、数据分析或结果的条件格式化显示中。

Group By 与 Count(*) 辨析

GROUP BY 子句和 COUNT(*) 函数是 SQL 中非常强大的工具，它们用于聚合数据，以下是它们的详细用途和如何配合使用：

GROUP BY 子句

数据分组：GROUP BY 子句用于将来自数据库表的多个记录组合成小组，这些小组基于一个或多个列的值。每个组包含具有相同值的行。

与聚合函数一起使用：在使用 GROUP BY 时，通常会与聚合函数（如 COUNT(), SUM(), AVG(), MAX(), MIN() 等）结合使用，以对每个分组的数据执行计算。

例如，如果你想知道每个部门有多少员工，你可以将数据按部门分组，然后计算每个组（部门）中的记录数。

人话：相当于对于所有数据，把GroupBy()中列出的attributes相同的数据单独“看做”一个“子表”。这样就可以把一整个表拆成几个“子表”的组合，然后所有操作对每一子表有效

COUNT(*) 函数

计数：COUNT(*) 是一个聚合函数，用于计算一组记录中的行数。当与 GROUP BY 结合使用时，COUNT(*) 会为每个分组返回行数。

与 GROUP BY 配合：结合 GROUP BY 使用时，COUNT(*) 可以返回每个分组的记录数。

例如，SELECT department, COUNT(*) FROM employees GROUP BY department; 这条查询会显示每个部门及其对应的员工数。

选出所有出现重复的 (WORKER_TITLE, AFFECTED_FROM) 组合

如果您的目标是找出所有在 title 表中 (WORKER_TITLE, AFFECTED_FROM) 组合出现重复的记录，您可以使用以下查询：
1
2
3
4
>SELECT WORKER_TITLE, AFFECTED_FROM, COUNT(*)
>FROM title
>GROUP BY WORKER_TITLE, AFFECTED_FROM
>HAVING COUNT(*) > 1;
在这个查询中：

GROUP BY WORKER_TITLE, AFFECTED_FROM 将数据按 WORKER_TITLE 和 AFFECTED_FROM 的组合分组。

COUNT(*) 计算每个组合出现的次数。

HAVING COUNT(*) > 1 筛选出那些出现超过一次的组合，即重复的 (WORKER_TITLE, AFFECTED_FROM) 组合。

通过这种方式，您能得到每个重复的职位和影响日期组合以及它们各自出现的次数。

SQL高阶语法

Join & Natural Join

Natural Join

Natrual Join 的实现非常简单。回顾from的本质，from 语句本质就是 Cartesian Product, 而 Natrual Join 就是在执行product后等值选取，在SQL中我们也是在from语句中加一小行字就可以实现了
1
2
3
4
5
6
7
8
-- Query 1 --
select name, course_id
from student, takes
where student.ID = takes.ID;

-- Query 2 (等价于query 1)--
select name, course_id
from student natural join takes; -- natural join
comment: Natrual join会自动识别两个表中的同名attribute，不支持部分同名 attribute join，也不支持不同名但是本质相同的attribute之间 join，所以使用上比较有限制

Outer Join

Outer Join 的核心是发现 可能有信息丢失，万一 A JOIN B 中 A 有的ID在中***根本没出现呢？**在natural join的时候就会直接无视这个数据，但是有时候我们还是想要这个数据怎么办呢？我们选择插入 NULL , 在插入null的标准上面又可以分 left outer join 和 right outer join。

请看以下例子:

假设我们有两个表：Employees 和 Departments。

Employees 表:

EmployeeID EmployeeName DepartmentID

1 Alice 1

2 Bob 2

3 Carol 3

4 Dave NULL

Departments 表:

DepartmentID DepartmentName

1 HR

2 Engineering

5 Marketing

1. LEFT OUTER JOIN

LEFT OUTER JOIN 返回左表（Employees）的所有行，即使右表（Departments）中没有匹配的行。如果右表中没有匹配的行，则结果中这些行的右表部分将包含 NULL。

查询示例:
1
2
3
>SELECT Employees.EmployeeName, Departments.DepartmentName
>FROM Employees
>LEFT OUTER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID; -- outer join 有 on，可以指定join的对象
结果:

EmployeeName DepartmentName

Alice HR

Bob Engineering

Carol NULL (没有匹配的部门)

Dave NULL (没有部门ID)

2. RIGHT OUTER JOIN

RIGHT OUTER JOIN 返回右表（Departments）的所有行，即使左表（Employees）中没有匹配的行。如果左表中没有匹配的行，则结果中这些行的左表部分将包含 NULL。

查询示例:
1
2
3
>SELECT Employees.EmployeeName, Departments.DepartmentName
>FROM Employees
>RIGHT OUTER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;
结果:

EmployeeName DepartmentName

Alice HR

Bob Engineering

NULL Marketing (没有员工在这个部门)

3. FULL OUTER JOIN

FULL OUTER JOIN 返回左表和右表中的所有行。当某一侧没有匹配时，另一侧将以 NULL 填充对应的列。

查询示例:
1
2
3
>SELECT Employees.EmployeeName, Departments.DepartmentName
>FROM Employees
>FULL OUTER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;
结果:

EmployeeName DepartmentName

Alice HR

Bob Engineering

Carol NULL(没有匹配的部门)

Dave NULL (没有部门ID)

NULL (没有员工在这个部门) Marketing

通过这些示例，你可以看到，外连接（尤其是 FULL OUTER JOIN）是必要的，因为它们允许我们从两个表中获取完整的记录，即使两个表中的一些行在另一个表中没有匹配项也一样。

如上文所示: SQL中如何实现 Outer Join? 也是一行字的事！Outer Join 还有一个无可比拟的优势：可以通过 ON 语句来实现部分 JOIN

Views

View的创建与删除

Views 的出现主要是为了应对权限管理问题，什么人可以看什么attribute，什么人不能看什么attribute是通过view来控制的

在 SQL 中，一个视图（View）是一种虚拟表，其内容由查询定义。与真实的表一样，视图包含一系列带有行和列的数据，但它们并不作为物理存储结构存在，而是在查询时动态生成。视图可以包含数据库中一个或多个表的所有行或特定行。

为什么使用视图：

安全性：通过视图，可以限制用户对某些数据的访问，因为视图可以选择性地展示表中的数据。

简便性：如果用户需要经常执行复杂的查询，可以将这些查询创建为视图，从而使用户能够简单地通过查询视图来获取所需的信息。

逻辑数据独立：视图可以作为表结构变化的抽象层，用户可以通过视图访问数据，即使底层数据表结构发生了变化。

视图的语法：

创建视图的基本语法如下：
1
2
3
4
>CREATE VIEW view_name AS
>SELECT column1, column2, ...
>FROM table_name
>WHERE condition;
这里，view_name 是你想要创建的视图的名称；SELECT 语句是定义视图内容的查询。

示例：

假设有一个名为 Employees 的表格，包含 ID，Name，Role 和 Department 字段。你可以创建一个只显示 ID 和 Name 的视图，如下所示：
1
2
3
4
>CREATE VIEW SimpleEmployeeData AS
.SELECT ID, Name
>FROM Employees;
一旦视图被创建，你可以像查询一个普通表一样查询视图：
1
>SELECT * FROM SimpleEmployeeData;
修改和删除视图：

修改视图：要修改视图，你通常需要删除并重新创建它。某些数据库系统可能支持 ALTER VIEW 语法。
1
2
3
4
DROP VIEW SimpleEmployeeData;  -- 删除旧视图
CREATE VIEW SimpleEmployeeData AS  -- 重新创建视图
SELECT ID, Name, Department
FROM Employees;
删除视图：使用 DROP VIEW 语句可以删除视图。
1
DROP VIEW SimpleEmployeeData;
请注意，虽然视图很强大，但它们也应谨慎使用，因为复杂的视图可能会影响数据库性能。

如何理解view？

View 其实和pyTorch中的共享内存机制比较像，view可以理解为一个虚拟的，限制修改的表（和普通物理表的唯一区别就是被限制修改了），所以在select中他的表现和正常的表是一样的

在数据库中，view 是一种虚拟表格，它的内容由SQL查询定义。与物理表不同，view 并不在数据库中以存储数据的形式存在，而是实时地通过执行定义它的SQL查询来生成数据。你可以像对待普通表一样对待视图，例如查询、更新（在一定限制下）和删除（在一定限制下），但背后它们是通过查询其他表（也可以是视图）来动态生成数据的。

请注意，虽然在许多情况下你可以像使用表一样使用视图（如执行SELECT语句），视图却不能完全像表那样使用。例如，一些视图不允许执行更新操作（UPDATE、INSERT、DELETE），特别是如果视图是基于复杂的SQL查询（如聚合函数、多个表的连接等）。

总的来说，view提供了一种强大的方式来封装复杂的查询，使得用户可以简单地通过引用视图来复用和管理复杂的SQL逻辑。但是，它们并不与底层表共享物理存储。

用 view 来定义 view（view的dependency）

view 的 depencdncy: 你可以用一个view来定义另一个view，比如:
1
2
3
4
CREATE v2 AS -- as 后面跟一个查找语句就行
SELECT a1, a2
FROM v1 -- 使用另一个view来创建
WHERE ...;
view 的 dependency: v1 is directly depend on v2 意思是 v1 的定义query中直接有v2参与

view 的 depend on: v1 is depend on v2 意思是v2可能通过某些view来间接参与构造v1, 比如: $v_{2} \rightarrow v_{1.5} \rightarrow v_{1}$

view 的 recursive dependency: 一个view的定义中包含他自己（depend on it self）

view的expansion

视图的展开：**视图的定义被替换或转换为基本的SELECT语句。这意味着当数据库处理查询时，它会将涉及视图的查询转换为操作实际表的原始SQL查询。**这个过程是透明的，用户不需要知道背后的转换细节。

例如，如果你有一个视图定义如下：
1
2
3
4
>CREATE VIEW my_view AS
>SELECT column1, column2
>FROM my_table
>WHERE column3 = 'value';
当你查询这个视图时：
1
>SELECT * FROM my_view;
数据库在内部将这个查询“展开”为基础的SELECT语句：
1
2
3
>SELECT column1, column2
>FROM my_table
>WHERE column3 = 'value';
这就是所谓的视图的“展开”或“解析”，这使得视图在使用上就像一个真实的表一样，尽管它是由其他表动态生成的。希望这解释了你的疑问！如果你有其他问题，请继续提问。

materialized views 物化视图

物化视图的本质其实就是.clone(), 我专门创建一个新的database表来给视图对应。那么这样以来就有一个问题，我更新我原来的表，我的物化视图不会变啊，那么怎么办呢？所以需要我手动更新。
当然，让我们来详细探讨和比较物化视图与普通视图的创建和维护方法。我们将通过一个简单的例子来说明这些概念：设想我们有一个包含订单信息的表格，我们想要创建视图来查询总销售额。

普通视图的创建和使用：

普通视图（或简称为视图）是一个虚拟表，它的内容由 SQL 查询定义。当从视图中查询数据时，数据库会执行视图定义中的 SQL 查询。

创建方法：
1
2
3
CREATE VIEW total_sales_view AS
SELECT SUM(amount) AS total_sales
FROM orders;
在这个例子中，total_sales_view 视图将允许用户查询 orders 表中所有订单的总销售额。每次查询视图时，数据库都会实时执行定义视图的 SQL 查询。

物化视图的创建和维护：

物化视图，与普通视图不同，会在磁盘上存储其查询结果，这意味着它可以提供更快的查询响应，但需要定期刷新以保持数据的最新状态。

创建方法：
1
2
3
CREATE MATERIALIZED VIEW total_sales_materialized AS -- CREATE MATERIALIZED
SELECT SUM(amount) AS total_sales
FROM orders;
与普通视图相比，创建物化视图的语法非常相似，但关键词是 MATERIALIZED。这个物化视图一旦创建，其结果就存储在磁盘上。

维护方法：

物化视图需要定期刷新以保证其数据的准确性。这可以通过手动或自动（根据数据库的支持和设置）完成。

手动刷新：
1
REFRESH MATERIALIZED VIEW total_sales_materialized;
这个命令会更新物化视图的数据，使之反映基础表中的最新状态。

对比：

更新机制：普通视图每次访问时都会执行定义视图的 SQL 查询，因此总是反映底层数据的最新状态。而物化视图则需要手动或自动刷新来更新其数据。

性能：物化视图通常能提供更快的查询响应，因为它们避免了每次都执行可能复杂和耗时的 SQL 查询。相比之下，普通视图可能在处理大量数据时表现得不够快捷。

存储：物化视图需要额外的磁盘空间来存储其数据，而普通视图不占用额外空间，因为它们只是查询的别名。

通过这个例子和对比，你应该能更清晰地理解物化视图与普通视图的区别以及它们各自的使用场景。物化视图非常适合用于数据分析和报表生成等场景，其中数据不需要实时更新，而普通视图更适合需要实时数据，但查询负载较轻的情况。

Update of a View

核心：所有View的插入都要变成Table的插入，那么view的attribute set是table的子集, view插入的行中肯定只是table的某一部分，那么这一条tuple中一定有的部分是没有定义的怎么办？-> NULL值！！！
1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE INSTRUCTOR (
    ID CHAR(5),
    NAME VARCHAR(20) NOT NULL,
    DEPT_NAME VARCHAR(20),
    SALARY NUMERIC(8, 2),  
);

CREATE VIEW FACULTY AS
SELECT ID, NAME, DEPT_NAME -- 可以看见我们只选了一部分attribute（不给看薪资）
FROM INSTRUCTOR;

INSERT INTO FACULTY
VALUES ('30765', 'Green', 'Music'); -- (30765', 'Green', 'Music', null) 被插入到源table上面
思考：对于以下view如何处理插入？：
1
2
3
4
CREATE VIEW HIST_FACULTY AS
  SELECT *
  FROM INSTRUCTOR
  WHERE DEPT_NAME = 'history' -- 用 FROM -WHERE 条件语句来定义view的话该如何插入？
大部分SQL语句都禁止非simpleview update

非simple定义:

FROM一个数据表（不允许cartesian product，因为cartesian product之后是临时结果集，没有记录的）

SELECT 里面不能有 distinct 等 expression，只能包括 attribute名称

不存在 GROUP BY 或者 HAVING

Transaction 事务

在 SQL 中，事务（Transaction）是一系列要么全部完成要么全部不完成的操作序列。一个事务以 BEGIN TRANSACTION 开始，以 COMMIT 或 ROLLBACK 结束，这取决于是否希望保留或撤销在事务内进行的更改。SQL transcation需要手动处理！！！

如何判定事务：

开始事务：

使用 BEGIN TRANSACTION（或在某些 SQL 方言中是简单的 BEGIN）来明确开始一个事务。

执行操作：

在 BEGIN 和 COMMIT/ROLLBACK 之间执行的所有 SQL 操作（如 INSERT, UPDATE, DELETE 等）都是事务的一部分。

判定条件：

如果所有操作都成功执行，那么事务可以通过 COMMIT 语句成功结束，这意味着所有更改都将永久应用到数据库中。

如果在执行操作时发生错误，或者如果你决定撤销事务中所做的更改，可以使用 ROLLBACK 语句来撤销所有更改，回到事务开始之前的状态。

事务的ACID属性：

事务的行为通常遵循四个主要属性，即 ACID：

原子性（Atomicity）：事务中的所有操作要么全部完成，要么全部不完成；

一致性（Consistency）：事务确保数据库从一个一致的状态转换到另一个一致的状态；

隔离性（Isolation）：并发执行的事务互不干扰；

持久性（Durability）：一旦事务提交，其结果就是永久的。

示例：

假设你在银行应用中要转账，这个操作涉及两步：从一个账户扣款和向另一个账户存款。这两个操作应该在一个事务中完成：
1
2
3
4
5
6
7
8
9
10
>BEGIN TRANSACTION;

>-- 扣除账户A的金额
>UPDATE accounts SET balance = balance - 100 WHERE account_id = 'A';

>-- 增加账户B的金额
>UPDATE accounts SET balance = balance + 100 WHERE account_id = 'B';

>-- 如果以上两步都成功了，提交事务
>COMMIT;
在这个例子中，如果中间有任何一步失败了，比如因为账户 A 的余额不足，那么你应该回滚事务：
1
>ROLLBACK;
这样，事务确保了两个操作要么同时成功，要么都不会对数据库产生影响，从而保持了数据的完整性和一致性。

注意，回滚需要手动执行！
1
2
3
4
5
6
7
8
9
10
11
12
13
BEGIN TRANSACTION;

>-- 尝试转账操作
>UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
>UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;

>-- 假设这里有一个条件判断，例如检查余额是否足够
>-- 如果不足，或其他检查失败，则回滚事务
>IF (业务逻辑不满足) THEN
   ROLLBACK TRANSACTION; -- 自行 rallback
>ELSE
   COMMIT TRANSACTION;
>END IF;
注意：transcation必须显示确定commit还是roll back！！

More on Constraints

以下语句皆出现在CREATE TABLE命令中

`not null`

NAME VARCHAR(12) NOT NULL 在CREATE TABLE的时候不允许null

`primary key`

设定 primary key并自动检查

`unique`

UNIQUE 命令式为了设定candidate key！！，保证他可以唯一决定一个tuple！！

eg (在create table命令中):
1
UNIQUE (A1, A2, ..., An) -- 设定 (A1, A2, ..., An) 为复合 candidate key

`check (P)`

这里的P是一个boolean表达式, 表示无论如何插入都要确保这个表达式

eg (在create table命令中):
1
CHECK (SEMESTER IN ('FALL', 'SPRING', 'SUMMER')) -- 确保不能插入别的什么有的没的东西

`FOREIGN KEY` 外键确保 Referential Intergrity （引用诚信）

确保一个键引用其他表的element时，那个element存在（注意，foreign key 必须是candidate key (Unique Integrity保证)!!）

eg:
1
2
FOREIGN KEY (DEPT_NAME) REFERENCES DEPARTMENT -- 如果不指定，默认引用primary key
FOREIGN KEY (DEPT_NAME) REFERENCES DEPARTMENT(NAME) -- 引用其他

Cascading （为了处理 referential integrity）

Cascade

在数据库管理中，"cascading"指的是在外键约束中设置的级联操作，这些操作定义了当在父表（即外键引用的表）中更改数据（如更新或删除行）时，子表（即包含外键的表）中相应行应如何自动响应。级联操作可以帮助维护数据库中的引用完整性，通过自动更新或删除依赖项来避免数据不一致。

cascading操作主要有以下几种类型：

CASCADE: 当父表中的行被更新或删除时，子表中的匹配行也会相应地被更新或删除。这是最直接的级联行为，用于确保数据的一致性和完整性。

SET NULL: 当父表中的行被删除或某些关键列被更新时，子表中对应行的外键列将被设置为 NULL。这适用于那些外键列允许 NULL 值的情况。

SET DEFAULT: 类似于 SET NULL，但是当父表中的行被删除或更新时，子表中的对应行会将外键列设置为一个默认值，而不是 NULL。这要求在子表的外键定义中指定了默认值。

NO ACTION 或 RESTRICT: 如果父表中有行因为更新或删除操作而变化，而这些变化会违反外键约束，则这些操作会被拒绝。这是默认的行为，意味着如果子表中有依赖的行存在，父表中的行不能被删除或相应的键值不能被更新。

级联规则需要在创建外键约束时定义。这里有一个例子，展示了如何在 SQL 中创建外键约束，并指定级联删除：
1
2
3
4
5
6
7
8
9
10
11
>CREATE TABLE parent (
   id INT PRIMARY KEY,
   data VARCHAR(100)
>);

>CREATE TABLE child (
   id INT PRIMARY KEY,
   parent_id INT,
   data VARCHAR(100),
   FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE -- 加载 FOREIGN KEY 后面，ON DELETE CASCADE 表示parent中删除了的element，child里面引用了被删值的列会全部被删除！ 
>);
在这个例子中，如果 parent 表中的一行被删除，那么 child 表中所有 parent_id 与 parent 表被删除行的 id 相匹配的行也会被自动删除。这样就通过级联操作维护了数据的完整性。

ON DELETE CASCADE 和 ON UPDATE CASCADE

DELETE CASCADE 和 UPDATE CASCADE 是两种常见的级联操作，用于在存在外键约束的数据库表之间维护数据一致性和完整性。下面是它们各自的详细说明：

DELETE CASCADE

当你在外键约束上设置了 DELETE CASCADE 选项，如果父表（也就是外键引用的表）中的一行被删除，那么在子表中所有依赖于那行的记录（即外键列匹配到父表中被删除行的主键或唯一键的记录）也会自动被删除。

这意味着 DELETE CASCADE 用于自动清理与被删除的父记录相关联的子记录，防止数据库中出现悬挂的引用，即存在外键引用但没有相应的父记录的子记录。

例如：
1
2
3
4
5
6
7
8
9
10
11
>CREATE TABLE parent (
   id INT PRIMARY KEY,
   name VARCHAR(50)
>);

>CREATE TABLE child (
   id INT PRIMARY KEY,
   parent_id INT,
   name VARCHAR(50),
   FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE
>);
在这个例子中，如果你从 parent 表中删除一条记录，那么 child 表中所有 parent_id 等于那条被删除记录的 id 的记录也会被自动删除。

UPDATE CASCADE

当你在外键约束上设置了 UPDATE CASCADE 选项，如果父表中的一行被更新（特别是涉及外键引用的键值），那么在子表中所有依赖于那行的记录（即外键列匹配到父表中被更新行的旧键值的记录）的外键列也会自动更新为新的键值。

UPDATE CASCADE 确保当引用的父表记录的键值改变时，所有的子记录也会相应地更新，保持引用的完整性和准确性。

例如：
1
2
3
4
5
6
7
8
9
10
11
>CREATE TABLE parent (
   id INT PRIMARY KEY,
   name VARCHAR(50)
>);

>CREATE TABLE child (
   id INT PRIMARY KEY,
   parent_id INT,
   name VARCHAR(50),
  FOREIGN KEY (parent_id) REFERENCES parent(id) ON UPDATE CASCADE
>);
在这个例子中，如果你更新 parent 表中一条记录的 id，那么 child 表中所有 parent_id 等于那条记录原来的 id 的记录的 parent_id 也会被自动更新为新的 id。

如果不加 cascade foreign key 如何处理异常？

如果在外键约束中没有明确指定级联行为（即没有使用 CASCADE、SET NULL、SET DEFAULT 或其他级联选项），则默认的行为通常是 NO ACTION 或 RESTRICT，具体取决于数据库系统的实现。这两种行为在大多数情况下是相似的：

NO ACTION

当设置为 NO ACTION，数据库不会在父表上自动进行任何级联操作。如果尝试删除父表中的一行或更新一个被子表引用的键，而该操作会违反外键约束，则该操作会被拒绝，并且会产生一个错误。这意味着，如果存在依赖（即子表中存在引用父表该行的记录），则不允许删除或更改父表中的相关记录。

在很多数据库系统中，NO ACTION 实际上会在事务结束时检查约束，而不是在外键被修改的瞬间检查。

RESTRICT

RESTRICT 的行为非常类似于 NO ACTION，但检查外键约束的时机可能不同。在尝试删除或更新父表中的记录时，如果存在任何子表的记录引用了这些父记录，操作会立即被拒绝。这与 NO ACTION 相比，RESTRICT 通常不等到整个事务结束时才进行检查，而是立即进行。

在实际使用中，RESTRICT 和 NO ACTION 的差别可能在于具体的数据库产品和版本，但两者都用于保证删除或更新操作不会违反外键约束。简而言之，如果外键约束没有指定级联操作，那么尝试删除或更改父表中的记录（如果这些记录被子表引用）时，默认情况下操作会被阻止，以维护数据完整性。

Built-in Data Type

Time

date '2020-7-27'

time '09:00:30'

timestamp '2020-7-27 09:00:30

Large-Object Types

blob: binart large object: vary lafge binary data (eg: .txt, .mp3, .mp4, pdf and so on)

clob: character large object: vary large char data

User-Defined Types

自定义 datatype

1	CREATE TYPE Dollars AS NUMERIC (12, 2) FINAL; -- 自定义一个 Dollar datatype

Domains

创建定义域

1 2	create domain person_name char(20) not null check (value in ('Bachelors', 'Masters', 'Doctorate')); -- 可以用domain来封装一些检查

Create Index

允许直接获取某一个tuple:

1 2	create index <name> on <relation-name> (A1, A2, ...., An); -- 语法格式 create index student_idx on stu(id)

EmployeeName	DepartmentName
Alice	HR
Bob	Engineering
Carol	NULL (没有匹配的部门)
Dave	NULL (没有部门ID)

SQL基础

SQL 的 domain type (数据类型)

创建table

基础创建语法

Integrity Constrains 完整性约束语法

更新 table 语法

Insert 插入语法

Delete 删除所有的tuple

Drop Table删除表 (连带着schema一起删了)

Alter 添加/删除 attribute (列)

Query 查询请求

Basic Query Structure 标准查询结构

Select clause 选择子句

select 语句中显式去重与不去重

select 的其他技巧

select 中的运算

where 语句中的逻辑运算符

from 语句中的重命名操作

String Operations

String matching operator 通配运算符

Ordering 排序

(补充) Where 中的语法糖: between （是带等号的，即两边都是闭的interval）

Set Operation

null 和 unknown

null

unknown

aggregate function 内建函数

数值内建函数

Group By

Having

in(单列结果集), some(单列结果集), all(单列结果集) 子查询命令

in (也是一个非常好用的语法糖，但是比 between 好用很多，他可以实现 “灵活的or”)

some 与 all 子句 (实现存在 exist 和 任意 any) 一阶逻辑

返回值判断语句

exist

unique

From 中的 Subquery

Database 的修改

条件 Deletion

条件 Insert

统一 Update

Case 条件语句

Group By 与 Count(*) 辨析

GROUP BY 子句

COUNT(*) 函数

选出所有出现重复的 (WORKER_TITLE, AFFECTED_FROM) 组合

SQL高阶语法

Join & Natural Join

Natural Join

Outer Join

Views

View的创建与删除

如何理解view？

用 view 来定义 view（view的dependency）

view的expansion

materialized views 物化视图

Update of a View

Transaction 事务

More on Constraints

not null

primary key

unique

check (P)

FOREIGN KEY 外键确保 Referential Intergrity （引用诚信）

Cascading （为了处理 referential integrity）

Cascade

ON DELETE CASCADE 和 ON UPDATE CASCADE

如果不加 cascade foreign key 如何处理异常？

Built-in Data Type

Time

Large-Object Types

User-Defined Types

Domains

Create Index

`Delete 删除所有的tuple`

`Drop Table删除表 (连带着schema一起删了)`

`Alter 添加/删除 attribute (列)`

`select` 语句中显式去重与不去重

`select` 的其他技巧

`select` 中的运算

`where` 语句中的逻辑运算符

`from` 语句中的重命名操作

(补充) Where 中的语法糖: `between` （是带等号的，即两边都是闭的interval）

some 与 all 子句 (实现`存在 exist` 和 `任意 any`) 一阶逻辑

`not null`

`primary key`

`unique`

`check (P)`

`FOREIGN KEY` 外键确保 Referential Intergrity （引用诚信）