数据库系统概论（个人笔记）
文章目录
- 数据库系统概论（个人笔记）
- 3、SQL介绍
- 3.1 SQL查询语言概述
- 3.2 SQL数据定义
- 3.3 SQL查询的基本查询结构
- 3.4 其他基本操作
- 3.5 设置操作
- 3.6 空值
- 3.7 聚合函数
- 3.8 嵌套子查询
- 3.9 数据库的修改
3、SQL介绍
3.1 SQL查询语言概述
Qveriew of The SQL Query Language
History
IBM Sequel 语言是在 IBM 圣何塞研究实验室开发的 System R 项目的一部分。
重命名为结构化查询语言（SQL）
ANSI 和 ISO 标准 SQL:
- SQL数据库
- SQL-89
- SQL-92
- SQL:1999（语言名称符合2000年要求!）
- SQL数据库:2003年
商业系统提供了大部分（如果不是全部的话）SQL-92特性，以及来自后来标准的各种特性集和特殊的专有特性。
- 并非这里的所有示例都适用于您的特定系统。
SQL Parts
DDL 数据库定义语言——提供用于定义关系模式、删除关系和修改关系式的命令。
DML数据操作语言——提供从数据库查询信息、向数据库中插入元组、从数据库中删除元组以及修改元组的能力。
Integrity 完整性——DDL包括用于指定完整性约束的命令。
View definition 视图定义——DDL包括用于定义视图的命令。
Transaction control 事务控制——包括指定事务的开始和结束的命令。
Embedded SQL and dynamic SQL 嵌入式SQL和动态SQL——定义如何将SQL语句嵌入到通用编程语言中。
Authorization 授权——包括用于指定对关系与视图得访问权限得命令。
3.2 SQL数据定义
SQL Data Definition
Data Definition Language
SQL 数据定义语言（DDL）允许指定有关关系的信息，包括:
- 每个关系的模式。
- 与每个属性相关联的值的类型。
- 完整性约束
- 要为每个关系维护的索引集。
- 每个关系的安全性和授权信息。
- 每个关系在磁盘上的物理存储结构。
Domain Types in SQL
| SQL中的域类型 | |
|---|---|
| char(n). | 固定长度的字符串，用户指定长度为n |
| varchar(n). | 可变长度字符串，具有用户指定的最大长度n |
| int. | 整数（与机器相关的整数的有限子集） |
| smallint. | 小整数（依赖于计算机的整数域类型的子集） |
| numeric(p,d). | 定点数，用户指定精度为p位数，小数点右侧为d位数。（例如，数字(3,1)允许44.5精确地存储，但不允许444.5或0.32） |
| real, double precision. | 浮点数和双精度浮点数，具有与机器相关的精度 |
| float(n). | 浮点数，用户指定的精度至少为n位 |
| 更多内容将在第四章介绍 |
Create Table Construct
SQL关系是使用 create table 命令定义的：
Integrity Constraints in Create Table
完整性约束的类型
SQL阻止任何违反完整性约束的数据库更新。
And a Few More Relation Definitions
And more still
Updates to tables
Insert
insert into instructor values('10211', 'Smith', 'Biology', 66000);
Delete
- 移除 student 关系里的所有元组
delete from student
Drop Table
drop table r
Alter
-
alter table r add A D- 其中A是要添加到关系r中的属性的名称，D是A的域。
- 关系中所有现有的元组都被指定为null作为新属性的值。
-
alter table r drop A- 其中A是关系r的属性名
- 删除许多数据库不支持的属性。
3.3 SQL查询的基本查询结构
Basic Query Struction of SQL Queries
Basic Query Structure
一个典型的SQL查询有这样的表单：
等价于关系代数表达式：
∏
A
1
,
A
2
,
…
,
A
n
(
σ
P
(
r
1
×
r
2
×
…
×
r
m
)
)
∏_{A_1,A_2,…, A_n}(σ_P(r_1× r_2×…×r_m))
A1,A2,…,An∏(σP(r1×r2×…×rm))
SQL查询的结果是一个关系。
The select Clause
select 子句列出查询结果中所需的属性
- 对应于关系代数的投影运算
Example：查找所有 instructors 的名字：
select name from instructor
NOTE：SQL名称不区分大小写（即，您可以使用大写字母或小写字母）
- 例如，Name ≡ NAME ≡ name
- 有些人在我们用粗体的地方用大写（上面的紫色字体）。
SQL 允许关系和查询结果中的重复。
若要强制删除重复项，请在 select 后插入关键字 distinct。
查找所有 instructors 的 department names，并删除重复的名称:
select distinct dept_name from instructor
关键字 all 指定不应删除重复项:
select all dept_name from instructor
select 子句中的星号 asterisk 表示“所有属性”:
select * from instructor
属性可以是不带 from 子句的文字:
select '437'
-
Results是一个包含一列和一行值为“437”的表。
-
可以给列一个名称，使用:
-
select '437' as FOO
属性可以是**带有 from **子句的文字:
select 'A' from instructor
- 结果是一个包含一列和N行（instructors 表中的元组数量）的表，每行的值为“A”。
select 子句可以包含算术表达式，包括操作+、-、*和/，以及对元组的常量或属性进行操作：
select ID,name,salary/12 from instructor
-
查询将返回一个与教员关系相同的关系，只是属性salary的值除以12。
-
可以使用as子句重命名“salary/12”:
-
select ID,name,salary/12 as monthly_salary
The where Clause
where 子句指定结果必须满足的条件
- 对应于关系代数的选择谓词。
为了寻找 Comp.Sci.dept 里所有的 instructors：
select name from instructor where dept_name = 'Comp.Sci.'
SQL 允许使用逻辑连接词and、or 和 not
逻辑连接符的操作数可以是包含比较操作符<、<=、>、>=、=和<>的表达式
比较可以应用于算术表达式的结果
为了寻找 Comp.Sci.dept 里且 salary > 70000 所有的 instructors：
select name from instructor where dept_name = 'Comp.Sci.' and salary > 70000
The from Clause
from 子句列出查询中涉及的关系
- 对应于关系代数中的笛卡尔积运算。
寻找 instructor × teaches 的所有的笛卡尔积：
select * from instructor, teaches
- 生成所有可能的教师-教学对，并包含双方关系的所有属性。
- 对于公共属性（例如，ID），结果表中的属性使用关系名称（例如，instructor.ID）进行重命名。
笛卡尔积不是很直接有用，但与where子句条件（关系代数中的选择操作）结合使用很有用。
Examples
查找教过某门课程的所有教员的姓名和course_id
-
select name, course_id from instructor, teaches where instructor.ID = teaches.ID
查找艺术系所有教过某门课程的教师的姓名和course_id
-
select name, course_id from instructor, teaches where instructor.ID = teaches.ID and instructor.dept_name = 'Art'
3.4 其他基本操作
Additional Basic Operations
The Rename Operation
SQL允许使用 as 子句重命名关系和属性:
old-name as new-name
在“Comp. Sci”中找到所有薪水比某个教员高的教员的名字:
-
select distinct T.name from instructor as T, instructor as S where T.salary > S.salary and S.dept_name = 'Comp.Sci.'
关键字 as 是可选的，可以省略:
instructor as T ≡ instructor T
String Operations
SQL包含一个字符串匹配运算符，用于对字符串进行比较。操作符 like 使用使用两个特殊字符描述的模式:
- 百分比（%） %字符匹配任何子字符串。
- 下划线（ _ ） _ 字符匹配任何字符。
查找名称中包含子字符串“dar”的所有教员的名称：
-
select name from instructor where name like '％dar％'
匹配字符串" 100% "：
like '100\%' escape '\'
在上面的代码中，我们使用反斜杠（\）作为转义字符。
模式区分大小写。
模式匹配示例:
- 'Intro%'匹配任何以’Intro '开头的字符串。
- '%Comp%'匹配任何包含" Comp "作为子字符串的字符串。
- ‘_ _ _’ 匹配任何恰好三个字符的字符串。
- '_ _ _ %'匹配任何至少三个字符的字符串。
SQL支持各种字符串操作，例如：
- 串联（使用" || "）
- 从大写到小写的转换（反之亦然）
- 查找字符串长度，提取子字符串等
Ordering the Display of Tuples
请按字母顺序列出所有教员的姓名：
select distinct name from instructor order by name
我们可以为每个属性指定 desc 表示降序，asc 表示升序；升序是默认的:
-
Example：
order by name desc
可以对多个属性进行排序:
-
Example：
order by dept_name, name
Where Clause Predicates
SQL包含一个 between 比较操作符
Example：查找工资在$90,000到$100,000（即，≥$90,000 和 ≤$100,000）之间的所有教师的姓名：
-
select name from instructor where salary between 90000 and 100000
元组比较
-
select name, course_id from instructor, teaches where (instructor.ID, dept_name) = (teaches.ID, 'Biology')
3.5 设置操作
Set Operations
查找2017年秋季或2018年春季的课程:
(select course_id from section where sem = 'Fall' and year = 2017)
union
(select course_id from section where sem = 'Spring' and year = 2018)
查找2017年秋季和2018年春季的课程:
(select course_id from section where sem = 'Fall' and year = 2017)
intersect
(select course_id from section where sem = 'Spring' and year = 2018)
查找2017年秋季而不是2018年春季的课程:
(select course_id from section where sem = 'Fall' and year = 2017)
except
(select course_id from section where sem = 'Spring' and year = 2018)
设置操作 union, intersect 和 except
- 上述每个操作都会自动消除重复项
要保留所有副本，请使用:
- union all
- intersect all
- except all
假设一个元组在 r 中出现 m 次，在 s 中出现 n 次，那么，它出现:
- m + n times in r union all s
- min(m, n) times in r intersect all s
- max(0, m – n) times in r except all s
3.6 空值
Null Values
元组的某些属性可能有一个空值，用 null 表示
null 表示未知值或值不存在
任何涉及 null 的算术表达式的结果都是 null
-
Example:
5 + null returns null
谓词 is null 可用于检查空值
-
Example:
select name from instructor where salary is null
如果应用谓词的值 is not null ，则谓词不为空成功。
SQL将任何涉及空值的比较的结果视为 unknown（谓词为空和非空除外）。
-
Example:
5 < null or null <> null or null = null
where 子句中的谓词可以涉及布尔操作（and, or, not）；因此，布尔运算的定义需要扩展以处理 unknown 。
-
and：
(true and unknown) = unknown, (false and unknown) = false, (unknown and unknown) = unknown -
or：
(unknown or true) = true, (unknown or false) = unknown (unknown or unknown) = unknown
如果 where 子句谓词的计算结果为 unknown，则将其结果视为 false
3.7 聚合函数
Aggregate Functions
这些函数对关系列的多值集进行操作，并返回一个值
| avg | 平均值 |
|---|---|
| min | 最小值 |
| max | 最大值 |
| sum | 值求和 |
| count | 值数量 |
Aggregate Functions Examples
找出计算机科学系讲师的平均工资：
-
select avg (salary) from instructor where dept_name= 'Comp. Sci.'
查找在2018年春季学期教授某门课程的教师总数:
-
select count (distinct ID) from teaches where semester = 'Spring' and year = 2018
求课程关系中元组的个数:
-
select count (*) from course;
Aggregate Functions – Group By
求出各部门教员的平均工资:
-
select dept_name, avg (salary) as avg_salary from instructor group by dept_name
聚合函数之外的 select 子句中的属性必须出现在 group by 列表中:
-
/* erroneous query */ select dept_name, ID, avg (salary) from instructor group by dept_name
Aggregate Functions – Having Clause
查找平均工资大于42000的所有部门的名称和平均工资:
select dept_name, avg (salary) as avg_salary
from instructor group by dept_name having avg (salary) > 42000
Note：having 子句中的谓词在组形成之后应用，而 where 子句中的谓词在组形成之前应用
3.8 嵌套子查询
Nested Subqueries
Nested 嵌套
SQL 为子查询的嵌套提供了一种机制。子查询 A subquery 是嵌套在另一个查询中的 “select-from-where” 表达式。
嵌套可以在以下 SQL 查询中完成：
select A1, A2, ..., An from r1, r2, ..., rm where P
as follows:
-
From clause：ri 可以被任何有效的子查询替换。
-
Where clause：P 可以用如下形式的表达式代替:
B (subquery)
B是稍后定义的属性和。
-
Select clause:
Ai 可以替换为生成单个值的子查询。
Set Membership
查找2017年秋季和2018年春季提供的课程:
select distinct course_id
from section
where semester = 'Fall' and year= 2017 and
course_id in (select course_id
from section
where semester = 'Spring' and year= 2018)
查找2017年秋季但不是2018年春季提供的课程:
select distinct course_id
from section
where semester = 'Fall' and year= 2017 and
course_id not in (select course_id
from section
where semester = 'Spring' and year= 2018)
说出所有既不是 “Mozart” 也不是 “Einstein” 的老师的名字:
select distinct name
from instructor
where name not in ('Mozart', 'Einstein')
查找由 ID 为10101的讲师讲授的课程部分的（不同的）学生总数:
select count (distinct ID)
from takes
where (course_id, sec_id, semester, year) in
(select course_id, sec_id, semester, year
from teaches
where teaches.ID= 10101);
Note：上面的查询可以用更简单的方式编写。上面的公式只是为了说明 SQL 的特性
Set Comparison
Set Comparison – “some” Clause
找出工资高于生物系某些（至少一个）教师的姓名:
select distinct T.name
from instructor as T, instructor as S
where T.salary > S.salary and S.dept name = 'Biology'
相同的查询使用> some子句:
select name
from instructor
where salary > some (select salary
from instructor
where dept name = 'Biology')
Definition of “some” Clause
Set Comparison – “all” Clause
查找工资高于生物系所有教师工资的所有教师的姓名:
select name
from instructor
where salary > all (select salary
from instructor
where dept name = 'Biology')
Definition of “all” Clause
Test for Empty Relations
如果参数子查询非空，exists 构造返回值 true。
Use of “exists” Clause
这是指定查询“查找2017年秋季学期和2018年春季学期教授的所有课程”的另一种方式:
select course_id
from section as S
where semester = 'Fall' and year = 2017 and
exists (select *
from section as T
where semester = 'Spring' and year= 2018
and S.course_id = T.course_id)
关联名称 Correlation name ——外部查询中的变量 S
相关子查询 Correlated subquery ——内部查询
select distinct course_id
from section
where semester = 'Fall' and year= 2017 and
course_id in (select course_id
from section
where semester = 'Spring' and year= 2018)
Use of “not exists” Clause
找到所有修过生物系所有课程的学生:
select distinct S.ID, S.name
from student as S
where not exists ( (select course_id
from course
where dept_name = 'Biology')
except
(select T.course_id
from takes as T
where S.ID = T.ID))
- 第一个嵌套查询列出了生物学提供的所有课程
- 第二个嵌套查询列出了特定学生所修的所有课程
Test for Absence of Duplicate Tuples
unique 构造测试子查询的结果中是否有任何重复元组。
如果给定的子查询不包含重复项，则 unique 构造的计算结果为“true”。
查找2017年最多开设一次的所有课程:
select T.course_id
from course as T
where unique (select R.course_id
from section as R
where T.course_id= R.course_id
and R.year = 2017)
Subqueries in the From Clause
SQL 允许在 from 子句中使用子查询表达式
找出那些平均工资超过4.2万美元的院系教师的平均工资:
select dept_name, avg_salary
from ( select dept_name, avg (salary) as avg_salary
from instructor
group by dept_name)
where avg_salary > 42000
注意，我们不需要使用 having 从句
上述查询的另一种写法:
select dept_name, avg_salary
from (select dept_name, avg (salary)
from instructor
group by dept_name)
as dept_avg (dept_name, avg_salary)
where avg_salary > 42000
With Clause
with 子句提供了一种定义临时关系的方法，该临时关系的定义仅对出现 with 子句的查询可用。
找到所有有最大预算的部门:
with max_budget (value) as
(select max(budget)
from department)
select department.name
from department, max_budget
where department.budget = max_budget.value;
Complex Queries using With Clause
找出总工资大于所有部门总工资平均值的所有部门:
with dept _total (dept_name, value) as
(select dept_name, sum(salary)
from instructor
group by dept_name),
dept_total_avg(value) as
(select avg(value)
from dept_total)
select dept_name
from dept_total, dept_total_avg
where dept_total.value > dept_total_avg.value
Scalar Subquery
标量 Scalar 子查询是在需要单个值的地方使用的查询
列出所有部门以及每个部门的教师人数:
select dept_name,
(select count(*)
from instructor
where department.dept_name = instructor.dept_name)
as num_instructors
from department
如果子查询返回多个结果元组，则运行时错误
3.9 数据库的修改
Modification of the Database
- 从给定关系中删除元组
- 在给定的关系中插入新的元组
- 更新给定关系中某些元组中的值
Deletion
删除所有教员
delete from instructor
删除财务部门的所有教员
delete from instructor where dept_name = 'Finance'
删除教员关系中与Watson大楼中某个部门相关联的教员的所有元组
delete from instructor
where dept_name in (select dept_name
from department
where building = 'Watson')
删除所有工资低于教员平均工资的教员
delete from instructor
where salary < (select avg (salary)
from instructor)
- Problem：当我们从教员中删除元组时，平均工资发生了变化
- SQL中使用的解决方案：
- 首先，计算 avg （salary）并找到所有要删除的元组
- 接下来，删除上面找到的所有元组（不重新计算 avg 或重新测试元组）
Insertion
向course添加一个新的元组
insert into course values ('CS-437', 'Database Systems', 'Comp. Sci.', 4)
或者同样的
insert into course (course_id, title, dept_name, credits)
values ('CS-437', 'Database Systems', 'Comp. Sci.', 4)
添加一个新的元组到student, tot_creds设置为null
insert into student values ('3003', 'Green', 'Finance', null)
让每个获得超过144个学分的音乐系学生成为音乐系讲师，年薪18000美元
insert into instructor
select ID, name, dept_name, 18000
from student
where dept_name = 'Music' and total_cred > 144
在将 select from where 语句的任何结果插入到关系中之前，将对其进行完全求值。
否则，如下的查询将导致问题：
insert into table1 select * from table1
Updates
给所有教员加薪5%
update instructor set salary = salary * 1.05
对收入低于7万的指导员加薪5%
update instructor set salary = salary * 1.05 where salary < 70000
对工资低于平均水平的指导员，给予5%的加薪
update instructor
set salary = salary * 1.05
where salary < (select avg (salary)
from instructor)
对年薪超过10万美元的教员加薪3%，其他教员加薪5%
-
写两个 update 语句:
update instructor set salary = salary * 1.03 where salary > 100000 update instructor set salary = salary * 1.05 where salary <= 100000 -
顺序很重要
-
用 case 语句可以做得更好（如下）
Case Statement for Conditional Updates
与之前相同的查询，但使用case语句
update instructor
set salary = case
when salary <= 100000 then salary * 1.05
else salary * 1.03
end
Updates with Scalar Subqueries
重新计算并更新所有学生的tot_creds值
update student S
set tot_cred = (select sum(credits)
from takes, course
where takes.course_id = course.course_id and
S.ID= takes.ID.and
takes.grade <> 'F' and
takes.grade is not null)
对于没有上过任何课程的学生，将tot_creds设置为null
代替 sum（credits），使用:
case
when sum(credits) is not null then sum(credits)
else 0
end
