数据库系统概论 - Introduction to Database Systems

复习一下关系代数和 SQL

• 关系代数
• SQL

关系代数

传统集合运算

并

差

交

笛卡尔积

专门的关系运算

Notation

设关系模式 它的一个关系为 ，设 ，表示 是 的一个元组

1. 表示元组 中属于属性 的分量
2. 设属性列表 ，则 表示元组 在属性列 上的值
3. 若 则 表示 和 的连接，是一个元组
4. 给定一个关系 和 是属性列，则 当 时， 在 中的象集定义为 ，表示 中属性列 值为 的诸元组在 上分量的集合

选择 (selection) - where

其中 为选择条件，是一个逻辑表达式，形式为 ， 为 中属性列, 为关系运算符

投影 (projection) - select

其中 为 中属性列

连接 (join) - join

• 等值连接
• 自然连接 ，选取同名属性列进行比较，并在结果中去除出重复的属性列

两个关系 和 在连接时，两个关系中的元组都有可能出现在另一关系上找不到有相同公共属性的元组，每个关系中没有匹配的元组被称为悬浮元组

当连接操作舍弃全部悬浮元组时，称为外连接，记为 ，当保留左边悬浮元组（右边留空 NULL）时称为 左外连接，记为 ，反之称为 右外连接，记为

除(division)

给定关系 和 ，其中 为属性列。 中的 和 中的 可以有不同的属性名，但必须来自相同的域

与 的除运算得到一个新的关系 ， 是 中满足下列条件的元组在属性列 上的投影：元组在 上分量值 的象集 包含 在 上投影的集合，记作

的集合，可以理解为，其中每一个元组 ，其分量 的象集 包含整个 ，也就是当 时，可以在 值（元组）的集合中找到所有 中 值（元组）的集合，相当于是笛卡尔积的逆运算。

SQL

Identifier

标识符（名称）要么用双引号，要么什么都不用

Schema

create

create schema [<schema name>] authorization <username>;

未指定模式名则默认隐含为用户名

drop

drop schema <schema name><CASCADE|RESTRICT>;

CASCADE 和 RESTRICT 必选其一，CASCADE 为级联删除，删除模式时把该模式中所有的数据库对象全部删除。当模式中有数据库对象时，如果选择 RESTRICT 则会拒绝删除

PostgreSQL 中 schema 和 database 不相同，而 MySQL 则认为两者相同

Table

create

create table <table name> (<column name><data type>[column constraint]
                          [,<column name><data type>[column constraint]]
                          ...
                          [,<table constraint>]);

drop

drop table <table name> [RESTRICT | CASCADE]

alter

alter table <table name>
  [add[column]<new column name><data type>[column constraint]]
  [add <table constraint>]
  [drop[column]<column name>[CASCADE | RESTRICT]]
  [drop constraint<constraint name>[RESTRICT | CASCADE]]
  [rename column <column name> to <new column name>]
  [alter column <column name> type <data type>];

Index

create

create [unique][cluster]index <index name>
on <table name>(<column name>[<order>][,<column name>[<order>]]...);

alter

alter index <old index name> rename to <new index name>;

drop

drop index <index name>;

Common Data Types

类别	数据类型声明	含义与说明	示例
整数类型	SMALLINT	2 字节，小范围整数。范围：-32768 到 +32767。	user_age SMALLINT
	INT 或 INTEGER	4 字节，标准整数。范围：-2147483648 到 +2147483647。	product_id INT
	BIGINT	8 字节，大范围整数。范围：-9223372036854775808 到 +9223372036854775807。	transaction_id BIGINT
任意精度数字	NUMERIC(p, s)	精确数值类型，p 是总位数，s 是小数位数。用于需要高精度的场景，如货币。	price NUMERIC(10, 2)
浮点类型	REAL	4 字节，单精度浮点数。不精确，适用于科学计算。	temperature REAL
	DOUBLE PRECISION	8 字节，双精度浮点数。精度高于 REAL。	distance DOUBLE PRECISION
字符类型	CHAR(n) 或 CHARACTER(n)	定长字符串，长度为 n。不足时会用空格填充。	country_code CHAR(2)
	VARCHAR(n) 或 CHARACTER VARYING(n)	变长字符串，最大长度为 n。更节省空间。	username VARCHAR(50)
	TEXT	无长度限制的变长字符串。	article_content TEXT
二进制类型	BYTEA	用于存储二进制数据，如图片、文件等。	profile_picture BYTEA
日期时间类型	DATE	仅存储日期（年-月-日）。	birth_date DATE
	TIME	仅存储时间（时:分:秒）。	start_time TIME
	TIMESTAMP	存储日期和时间。	login_time TIMESTAMP
	TIMESTAMPTZ	TIMESTAMP WITH TIME ZONE 的缩写。存储带时区信息的日期和时间，会自动转换为数据库时区。	event_time TIMESTAMPTZ
布尔类型	BOOLEAN	存储逻辑值：TRUE（真）、FALSE（假）或 UNKNOWN（未知）。	is_active BOOLEAN
JSON 类型	JSON	存储 JSON 格式的数据。输入时会进行语法检查，但数据以文本形式存储，查询时需要解析。	config JSON
	JSONB	JSON Binary 的缩写。将 JSON 数据以二进制形式存储，支持索引，查询性能远高于 JSON。推荐优先使用。	user_preferences JSONB
数组类型	type[]	存储同一类型元素的集合。可以是一维或多维数组。	tags VARCHAR[], matrix INT[][]
枚举类型	ENUM('label1', 'label2', ...)	存储一个预定义的枚举值列表中的一个。	status ENUM('pending', 'processing', 'done')

Select

basic select

select [ALL|DISTINCT]<target expression>[alias name][,<target expression>[alias name]]...
from <table name or view name>[alias][,<target expression>[alias name]] ... | (<select expression>)[as]<alias name>
[where <condition expression>]
[group by <column name>[Having <condition expression>]]
[order by <column name>[asc|desc]]
[limit <line number>[offset <line number>]];

having 对每个组进行，where 对表进行

Common Query Conditions

查询条件类型	运算符 (谓词)	功能
比较运算	=	等于
	!=, <>	不等于
	>	大于
	<	小于
	>=	大于等于
	<=	小于等于
逻辑运算	AND	逻辑与，多个条件必须同时成立
	OR	逻辑或，多个条件中只要有一个成立
	NOT	逻辑非，对单个条件取反
范围查询	BETWEEN ... AND ...	判断值是否在指定的闭区间内
	NOT BETWEEN ... AND ...	判断值是否不在指定的闭区间内
列表查询	IN (...)	判断值是否在指定的列表中
	NOT IN (...)	判断值是否不在指定的列表中
空值判断	IS NULL	判断值是否为 NULL
	IS NOT NULL	判断值是否不为 NULL
模式匹配	LIKE '模式'	简单字符串匹配（%匹配任意多字符，_匹配单个字符）
	NOT LIKE '模式'	简单字符串不匹配
	SIMILAR TO '模式'	SQL 标准的正则表达式匹配
	~ '正则表达式'	POSIX 风格的正则表达式匹配
存在性判断	EXISTS (子查询)	判断子查询是否返回至少一行数据
	NOT EXISTS (子查询)	判断子查询是否没有返回任何数据
ALL/ANY/SOME	> ALL (...)	大于子查询结果中的所有值
	< ANY (...)	小于子查询结果中的任意一个值
	= SOME (...)	等于子查询结果中的任意一个值（与 IN 功能类似）

Aggregation Functions

函数名	功能描述	使用示例
COUNT([DISTINCT/ALL]<column name>)	统计行数或非 NULL 值的数量。	COUNT(*)：统计总行数。 COUNT(column_name)：统计指定列中非 NULL 值的数量。
SUM([DISTINCT/ALL]<column name>)	计算指定列的总和。	SUM(price)：计算 price 列所有值的总和。
AVG([DISTINCT/ALL]<column name>)	计算指定列的平均值。	AVG(salary)：计算 salary 列的平均值。
MAX([DISTINCT/ALL]<column name>)	找出指定列的最大值。	MAX(score)：找出 score 列的最高分。
MIN([DISTINCT/ALL]<column name>)	找出指定列的最小值。	MIN(price)：找出 price 列的最低价格。

Joiened Querying

外连接查询

outer join

select <column>[,<column>,...]
from <table name1>[,...]
[left|right｜full] outer join <table name2> on (<condition expression>);

表 1 和表 2 根据连接条件[左/右/全]外连接

内连接查询

outer join

select <column>[,<column>,...]
from <table name1>[,...]
inner join <table name2> on (<condition expression>);

谓词子查询

Query

select <column>[,<column>,...]
from <table name1>[,...]
where <condition expression> (<select expression>);

用于有多值的子查询的条件判断

带有 exists 的谓词子查询

Query

select <column>[,<column>,...]
from <table name1>[,...]
where exists (<select expression>);

• 带有该谓词的子查询只产生逻辑真值 “true” 或 “false”
• 可以利用 exists 来判断 ，，， 非空等是否成立

意义在与子查询中依赖于父查询的字段，也可以通过连接查询实现

Set Querying

Set Querying

<select expression1>
[union|intersect|except]
<select expression2>;

参与集合查询的各查询结果必须每列的数据类型相同

Derived Table Querying

select <column name>[,<column name>,...]
from <select expression> as <derived table name>(<column name1>, <column name2>,...)
where <condition expression>;

将子查询 alias 为一张虚表，进行字段选择、条件判断

Insertion

Basic

Insertion

insert into <table name>[(<attribute1>[,<attribute2>],...)]
values (<constant1>[,<constant2>]...);

insert into … select

insert into select

insert into <table name>[(<attribute1>[,<attribute2>],...)]
<select expression>;

select into

select into

select <column name>[,<column name>,...] into <new table name>
from <table name or view name>[alias][,<target expression>[alias name]] ... | (<select expression>)[as]<alias name>
[where <condition expression>]
[group by <column name>[Having <condition expression>]]
[order by <column name>[asc|desc]]
[limit <line number>[offset <line number>]];

将查询结果建表保存

Updation

update

update <table name>
set <column name>=<expression>[,<column name>=<expression>]...
[where <condition expression>];

Deletion

delete

delete from <table name>
[where <condition>];

View

create

create view <view name> [(<column name>[,<column name>]...)]
as <select expression>
[with check option];

• with check option 表示对视图进行 update、insert、delete 时保证操作后视图满足视图定义中的谓词条件
• 对视图的更新最终通过视图消解变为对表的更新

权限控制

Grant

Grant

grant <permission>[,<permission>]...
on <object type><object name>[,<object type><object name>]...
to <user>[,<user>]...
[with grant option];

with grant option 表示允许被授权用户二次授权给其他用户，但不允许循环授权，即被授权者不能再把权限授权回授权者或其祖先。

Revoke

Revoke

revoke <permisison>[,<permission>]...
on <object type><object name>[,<object type><object name>]...
from <user>[,<user>]...[CASCADE | RESTRICT];

若使用了 CASCADE 则级联收回授权，否则默认为 RESTRICT 不收回转授权限

Role

create role

create role <role name>;

grant permission to role

grant <permission>[,<permission>]...
on <object type><object name>[,<object type><object name>]...
to <role>[,<role>]...
[with grant option];

grant role to user

grant <role1>[,<role2>]...
to <role|user>[,<user|role>]...
[with admin option];

with admin option 允许被授予角色的用户授予其他用户被授予的角色

revoke permission from role

revoke <permisison>[,<permission>]...
on <object type><object name>[,<object type><object name>]...
from <role>[,<role>]...;

Entity integrity

primary key

create table <table name>(
  ...
  primary key (<column name1>[,<column name2>]...)
);

Reference integrity

foreign key

create table <table name>(
  ...
  foreign key(<column name>) references <table name>(<column name>)
  [,on delete <CASCADE | no action>
  [,on update <CASCADE | no action>]]

  [,foreign key(<column name>) references <table name>(<column name>)
  [,on delete <CASCADE | no action>
  [,on update <CASCADE | no action>]]]
);

User Defined Constraint

Column Constraint

• Column Constraint: NOT NULL | UNIQUE

column constraint

create table <table name>(
  <column name><data type>[column constraint][Check (<check condition>)]
  ...
);

check 短语指定列值需要满足指定条件

Tuple Constraint

tuple constraint

create table <table name>(
  ...
  Check (<check condition>)
);

对元组的约束

Integrity Constraint

integrity constraint

create table <table name>(
  ...
  constraint <constraint name><constraint expression>
);

drop constraint

alter table <table name>
drop constraint <constraint name>;

add constraint

alter table <table name>
add constraint <constraint name><constraint expression>;

Normalization

to be continue…