SQL

Last updated Jan 4, 2024

• 数据库

SQL 是使用 ANSI 标准的一种计算机语言, 除了必须符合标准的部分, 在不同版本的 SQL 中也有不同的方言.

• SQL: Structured Query Language 结构性查询语言。
• SQL是由国际标准化组织ISO制定的标准，对DBMS的统一操作方式。

• DATABASE数据库 -> TABLE表 -> COLUMN列
• 一个系统中有若干个数据库，一个数据库有若干张表，每一张表有若干行若干列
• 下面是一张表的例子：

• 以分号结尾，可以单行或多行
• 空格和换行不会当作输入
• 不区分大小写（通常大写关键字）

• Data Definition Language
• 用来操作数据库对象的结构：库、表、列

• Data Manipulation Language
• 用来操作数据库记录：增、删、改（会修改记录的操作）

• Data Control Language
• 用来定义和访问权限和安全级别，如用户创建和授权

• Data Query Language
• 用来查询记录（不会修改记录）

SQL 支持 compatible range:

• 任意长度的字符串可兼容
• 任意精度的数字可兼容

int

• 整数类型

double

• 双精度浮点数
• double(5,2) 表示最多5位，其中必须有2位小数

decimal

• 对十进制运算比较精确的小数，适合用在钱方面

BigDecimal

• 可以装极大的数

char

• 固长字符串, 最大 255 字符
• char(255)
• 例如用在身份证号
• 不同的编码最大可用字节数不同

varchar

• 变长字符串，最大 65535 Bytes
• varchar(65535)
• 每一个值需要1或2个字节记录长度
  • <=255 / <= 65535
• 不同的编码所对应的最大可存储的字符数不同

text 家族(MySQL特有, 在标准中是clot)

• 固长字符串

blob 家族

• 固长二进制

Integritätbedingung

unique, not null 每表有且仅有一个唯一的主键, 可以是一个或一组

$name$ $type$ references $tablename(columnname)$

表示 $a_2$ 是来自$t_1$ 的 $a_1$ 键 如果 $t_1$ 没有对应的键则不允许

设置默认值 $w_1$ e.g. default ‘Auto’

1

`product_id` int(11) DEFAULT NULL COMMENT '商品id',

给定限定条件 e.g. check (A <= 100)

PRIMARY KEY ($a_1$, …, $a_k$)

UNIQUE ($a_1$, …, $a_k$)

CHEKC(b)

foreign key $a_2$ references $t_1 (a_1)$

KNr INTEGER REFERENCES K(KNr)

foreign key (a5,a6) references B (b1,b2)

• on delete cascade 通常 b1, b2 在 A 表有对应项时无法更改, 如果增加此条属性, 则删除 B 中的项时, A 的对应项也会删除
• on update cascade 更改 B 中的项时, A 的对应项也会更新
• on delete set null 删除 B 中的项时, A 的对应项会设为 null

use database1;

data defination language

• meta 层

• $a_i$ 属性名称
• $d_i$ 属性类型 (domain)
• $c_i$ 可选约束

1
2
3
4
5
6

CREATE TABLE Mitarbeiter
(
	a1 d1 c1,
	...
	ak dk ck
)

e.g.

1
2
3
4
5
6
7

CREATE TABLE Mitarbeiter
(
	pNr    NUMBER(3) PRIMARY KEY,
	name   CHAR(20) DEFAULT 'empty',
    age    INT CHECK (Age > 0),
    salary DECIMAL(6,2)
)

1
2
3
4

ALTER TABLE tablename
	ADD (attr type); |
	MODIFY (attr type); |
	DROP (attr);

drop table $n_1$ 删除数据表

alter table $n_1$ add ($a_1\\ d_1\\ c_1,\\ a_2\\ d_2\\ c_2,\\ …$) 增加表的属性

alter table $n_1$ drop ($a_1\\ a_2$, …) 删除表的属性

alter table $n_1$ modify ($a_1\\ d_1\\ c_1,\\ a_2\\ d_2\\ c_2,\\ …$)

1

INSERT INTO MGA VALUES ('Weber', 001) 

1

DELETE FROM MGA WHERE ...

data manipulation language

纯读取操作

1
2
3
4

GRANT right1, right2, ...
ON table_name | view_name
TO user_name | TO PUBLIC
[WITH GRANT OPTION]

option: restrict, cascade

1
2
3
4
5
6

SELECT ...
FROM ...
[WHERE ...]
[GROUP BY ...
 [HAVING ...]]
[ORDER BY ...]

1. FROM WHERE
2. GROUP BY
3. Aggregation(即函数操作)
4. HAVING
5. SELECT(projection)

如果表中有至少一个匹配，则返回行

即使右表中没有匹配，也从左表返回所有的行

即使左表中没有匹配，也从右表返回所有的行

只要其中一个表中存在匹配，则返回行 缺省值会被设置为 NULL

自动using同名属性

![[attachments/Pasted image 20221108232850.png|500]]

可以出现在 con 位, 以及 select…from…where… 中的任意空位 ![[attachments/Pasted image 20221117115041.png|480]]

exists:

1
2
3

select * from kunde k1 where exists
(select * from Kunde k2 where k1.Adr=k2.Adr and...
)

forall: 双重否定 …where not exists (select…from…where not…)

和 exists 一样, 作为 con 的起始 some 差不多就是引导从句用

e.g. $A_i \\ \Theta \\ all (Subq) \equiv { … | \forall t \in Subq: A_i \\ \Theta\\ t}$ 比如可以用在找数值最高最低

$A_i \\ IN \\ (Subq)$ 可作为条件

1
2
3
4
5

select * from MagicNumbers where Wert in ( select
Zahl from Primzahlen)

select * from MagicNumbers where Wert not in ( select
Zahl from Primzahlen)

1
2
3

SELECT column_name,column_name_ 
FROM table_name  
ORDER BY column_name,column_name ASC|DESC;

• 默认是升序
• 可以设置多级排序, 列名间用逗号分隔
• 字符串类型不可排

1
2

SELECT site_id, SUM(access_log.count) AS nums  
FROM access_log GROUP BY site_id;

将某列数据按另一列的结果加总

相当于分组时的 WHERE 条件, 只显示满足条件的分组 在分好组后开始 HAVING 的筛选

1
2

SELECT Abteilung, Einheit, sum(Gehalt) FROM Mitarbeiter 
GROUP BY Abteilung, Einheit

![[attachments/Pasted image 20221124204053.png|500]]

1
2
3
4

SELECT Websites.name,COUNT(access_log.aid) AS nums FROM access_log  
LEFT JOIN Websites  
ON access_log.site_id=Websites.id  
GROUP BY Websites.name;

![[attachments/Pasted image 20221124203342.png|500]]

1
2

CREATE INDEX index_name ON table_name (a1, ..., an);
create index name_geb on Dozent (Name, Geburt);

1
2

DROP INDEX index_name;
ALTER INDEX index_name new_name; 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

/* 
* match query 
* 可以 partial 匹配, 但不能在不指定 i 的情况下指定 i+1
*/
SELECT * FROM t WHERE a1=... AND an=...;

/*
* range query
* 比较符，BETWEEN 都可用
*/
SELECT * FROM t WHERE a1=... AND ai=... AND ai+1<= ...;

/* 
* pointset query
* 可用 OR
*/
SELECT * FROM t WHERE a1=... AND ai=... AND ai+1 in (7, 77,177);

1

SELECT avg(column_name) FROM ...

字面

返回一个计数

1

SELECT COUNT(DISTINCT column_name) FROM table_name;

• COUNT(*)
• COUNT(column_name) = COUNT(ALL column_name)

字面

大小写

• 隐藏了表的底层结构, 限制了部分操作, 简化了数据访问操作的数据呈现结构
• 只存储了必需的 Relationschema
• 数据表的变化会影响视图, 视图的变化有限制地影响数据表
• 在实际访问操作中, 会将对视图的操作翻译成对数据表的操作, 再呈现为视图 ![[attachments/Pasted image 20221124211644.png|400]]

创建视图

1
2
3
4

CREATE VIEW view_name AS  
SELECT column_name(s)  
FROM table_name  
WHERE condition

撤销视图

1

DROP VIEW view_name

这里更新视图指的是让视图的显示数据变化, 而不能直接在视图上修改数据库中的内容

更新视图语法

1
2
3
4

CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

示例: 现在，我们希望向 “Current Product List” 视图添加 “Category” 列。我们将通过下列 SQL 更新视图：

1
2
3
4

CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName,Category
FROM Products
WHERE Discontinued=No

• 允许: selection, projection, crossproduct, union, intersection, difference, group, aggregation, subqueries
• 不允许: 排序

指通过 projection 操作创建的 view

• 删除视图的项无限制
• 增加视图的项时，如果有视图不包含而源数据表包含的列，则会用 NULL 代替该属性
• Kein insert/delete/update bei distinct/Arithmetik

指通过 selection 操作创建的 view

• 项更新后如果不满足 WHERE 条件, 视图中的项就会消失
• with check option 的话, 若更新后不满足 WHERE 则会出现错误消息

指通过 join 操作创建的 view

• join view 禁止 update. 原因是歧义.

• 可以增删改的视图都属于 Updatable view
• 不能有aggregation, group by, subquery, 不能用 join 创建 Aggregation, group by, Subquery in VIEW 不可使用