数据库系统课程笔记03 SQL03 SQL

03 SQL

SQL查询语言概述

Structured Query Language

历史

最早Sequel语言在IBM的圣约瑟研究实验室被作为第一个关系型数据库System R项目的一部分而开发的，后被更名为Structured Query Language (SQL)

ANSI和ISO的SQL标准:
✓ANSI: SQL-86, SQL-89, SQL-92
✓ISO: SQL:1999, SQL:2003, SQL:2006, SQL:2008,
SQL:2011, SQL:2016, SQL:2019

商业系统提供了绝大部分的SQL标准功能（即使不是全部的SQL-92），再加上从后来的标准和特殊的专有功能（可能是非SQL标准）提供的不同功能集合

不只是“查询”（query）语言

DDL 数据定义语言

DML 数据操纵语言

完整性

视图定义

授权

事务控制

嵌入式SQL和动态SQL

SQL数据定义

数据定义语言 (DDL)

SQL的 数据定义语言 (DDL) 能够定义每个关系的信息

✓关系的模式
✓属性的取值类型、取值范围（属性域）
✓完整性约束（主外码）
✓关系的安全性和权限信息
✓还包括其它信息如：
• 每个关系维护的索引集合 *
• 每个关系在磁盘上的物理存储结构 *

SQL的基本类型

➢ char(n): 固定长度的字符串，用户指定长度n
➢ varchar(n): 可变长度字符串，用户指定最大长度n
➢ int: 整数类型 (4字节)
➢ smallint: 小整数类型 (2字节)
➢ numeric(p,d): 定点数，精度由用户指定。这个数有p位数字，
其中，d位数字在小数点右边
➢ real, double precision: 浮点数与双精度浮点数，精度与机
器相关
➢ float(n): 精度至少为n位的浮点数

基本关系模式定义

create table定义

create table 命令定义SQL关系

create table r ( A 1 D 1 ,
				...,
				A n D n ,
				<完整性约束 1 >,
				...,
				<完整性约束 k >)；(分号结束)

r 是关系名，A i 是关系r模式中的一个属性名，D i 是属性 A i 的域（即取值类型、范围）

eg

 create table instructor (	ID char(5) ,
							name varchar(20) ,
							dept_name varchar(20) ,
							salary numeric(8,2),
							primary key (ID));

Create Table中的完整性约束

➢ not null
➢ primary key (A 1 , …, A n )
➢ foreign key (A m , …, A n ) references r(A p , …, A q )

eg

create table instructor (
							ID char(5),
							name varchar(20) not null,
							dept_name varchar(20),
							salary numeric(8, 2),
							primary key (ID),
							foreign key (dept_name) references department );

简化版

 create table course (
							course_id varchar(8) primary key,
							title varchar(50),
							dept_name varchar(20),
							credits numeric(2,0),
							foreign key (dept_name) references department);

primary key声明的属性会自动为not null

SQL禁止破坏完整性约束的数据库更新

✓新插入的元组主码属性为空值、或取值与关系中的另一元组主码属性相同
✓破坏外码约束

删除和修改表

drop table

drop table student

删除表和其中内容

delete from

delete from student

删除表里的内容，但是保留表（关系模式）

alter table

增加、删除属性

alter table r add A D

• 其中 A 是要被添加到关系 r 的属性的名称，并且 D 是 A 的域。
• 关系中所有元组使用 null 作为新的属性值

alter table r drop A

• 其中，A 是关系 r 的属性的名称
• 许多数据库都不支持删除属性，但支持drop整个表

SQL查询的基本结构

select语句的基本结构

典型的SQL查询语句的形式

select A 1 , A 2 , ..., A n
from r 1 , r 2 , ..., r m
where P 1 and (or, not) P n ;

✓ A i 代表一个属性
✓ r i 代表关系实例
✓ P i 是一个谓词**（限定条件）**

一条SQL语句的结果是一个关系

SQL查询语句末尾有分号

select子句

选择子句列出的是查询语句需要的属性

对应关系代数中的投影操作（属性的选择）

eg

select name
from instructor ;

SQL语句是不区分大小写的 (即，既可以使用大写也可以使用小写)

SQL在查询结果和关系中默认允许重复

为了强制消除重复，可以在select后加上关键字distinct

eg

select distinct dept_name
from instructor;

distinct可以后接多个属性，表示选出在多个属性上都不重复的元组

关键字all 显式指定不消除重复

eg

select all dept_name
from instructor;

**“*”**在select子句中表示“所有属性”

eg

select *
from instructor;

select子句可以包含算术表达式，算术表达式中可以有+，-，*, / 运算符和对常量和属性的操作

eg

select ID, name, salary/12
from instructor;

select子句中还可以包含其他一些特殊的数据类型，如日期，以及算术函数

where子句

where子句表示结果必须满足的限定条件

对应关系代数的选择操作（元组的选择）

eg

select name
from instructor
where dept_name = ‘Comp. Sci.’ and salary > 80000;
#找出Comp. Sci.系中工资大于80000的教师的姓名

where子句中可以包含逻辑运算符and, or, 和not

逻辑运算符的运算对象可以是包含比较运算符> , >= , <， <= ，**=**和< > 的表达式

允许使用比较运算符来比较字符串、算术表达式以及日期类型等

from分句

from分句列出了查询中用到的关系

对应关系代数中笛卡尔积操作

eg

select *
from instructor, teaches;
#笛卡尔积instructor × teaches

✓生成每一个可能instructor–teaches对, 所有属性来自两个表
✓理解为一个**迭代（多重循环）**的过程

经常结合where子句使用 (关系代数中选择操作)

如果多关系中存在相同属性，则在select、where子句中须作区分 ，如： instructor.ID, teaches.ID

连接

eg

select name, course_id
from instructor, teaches
where instructor.ID = teaches.ID; #限制条件
#对所有上课的教师，查询他们的姓名及课程ID

select section.course_id, semester, year, title
from section, course
where section.course_id = course.course_id and dept_name = ‘Comp. Sci.’;
# 查询Comp. Sci开的每一门课course ID, semester, year, title

SQL查询语句

通常说来，一个SQL查询的含义可以 理解 如下：

1.为from子句中列出的关系产生笛卡尔积

2.在步骤1的结果上应用where子句中指定的谓词

3.对于步骤2结果中的每个元组，输出select子句中指定的属性（或表达式的结果）

上述过程不是SQL查询语句的执行顺序，实际SQL查询语言的执行是经过查询优化

➢ 如果省略where子句，则谓词P为true
➢ 与关系代数表达式不同，在SQL的查询结果中可以包含很多重复的元组

自然连接

自然连接会匹配两个关系中所有共同属性的相同值的元组, 去掉重复属性列

eg

select *
from instructor natural join teaches;

自然连接结果＝共同属性＋第一个关系属性＋第二个关系属性

eg

列出教师姓名、教师所教课程的编号

select name, course_id
from instructor, teaches
where instructor.ID = teaches.ID;

select name, course_id
from instructor natural join teaches;

以上两个SQL查询语句完全等价

谨防无关的属性具有相同的名字

错误：

select name, title
from instructor natural join teaches natural join course;

正确

select name, title
from instructor natural join teaches, course
where teaches.course_id = course.course_id;

select name, title
from (instructor natural join teaches)
			join course using(course_id);

附加的基本运算

更名运算

SQL允许对关系和属性进行更名操作

使用as子句

old-name as new-name

eg

select ID, name, salary/12 as monthly_salary
from instructor

#找出满足下面条件的所有教师的姓名，他们至少比某一个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

在Oracle数据库中，必须省去as关键字

在SQL标准中，重名关系的标识符（如T、S）称之为

✓相关名称（correlation name）
✓表别名（table alias）
✓相关变量（correlation variable）
✓元组变量（tuple variable）

字符串运算

SQL中包含字符串匹配操作符，用于字符串比较。SQL中like运算符可实现模式匹配，模式匹配用两个特殊的字符来表示：

✓百分号(%)匹配任意子字符串
✓下划线(_)匹配任意一个字符
✓SQL字符串用单引号；关系代数字符串用双引号
✓匹配模式是大小写敏感的（SQL标准）；但部分数据库不区分字符串大小写，如MySQL、SQL Server

eg

✓‘Intro%’匹配任意以“Intro”开头的字符串。
✓‘%Comp%’匹配任意包含“Comp”子串的字符串。
✓‘_ _ _ ’匹配只含三个字符的字符串。
✓‘ _ _ _ %’匹配至少含三个字符的字符串。

eg

select name
from instructor
where name like ‘%dar%’；
#找出姓名中包含“dar”的教师的姓名

当匹配模式中含有特殊字符（如”％”、”_”、””）时，须使用转义字符（通过escape定义）

like ‘100 %%’ escape ‘’：匹配“100%”开头的字符串

SQL支持各种字符串操作函数

✓串联（使用“||”）
✓大写转小写（小写转大写）（lower(), upper()）
✓字符串长度（length()），提取子串（substr()），等等

排列元组的显示次序

select distinct name
from instructor
order by name;
#按字母顺序列出所有教师

可以用 desc 表示降序，使用 asc 表示升序；默认使用升序

eg

order by name desc

排序可以在多个属性上进行

eg

order by dept_name, name

order by salary desc, name asc

where子句谓词

SQL提供 between 比较运算符（where子句中）

select name
from instructor
where salary between 90000 and 100000;
#找出工资在 $90,000 和 $100,000 之间的教师姓名

元组比较符 ：简化where子句条件

select name, course_id
from instructor, teaches
where (instructor.ID, dept_name) = (teaches.ID,‘Biology’);

不建议使用

集合运算

union

并

(select course_id from section where sem = ‘Fall’ and year = 2009)
union
(select course_id from section where sem = ‘Spring’ and year = 2010)
#找出在2009年秋季开课，或者在2010年春季开课或两个学期都开课的所有课程id号

自动消除冗余

intersect

交

(select course_id from section where sem = ‘Fall’ and year = 2009)
intersect
(select course_id from section where sem = ‘Spring’ and year = 2010)
#找出在2009年秋季和2010年春季同时开课的所有课程id号

自动消除冗余

except

差

(select course_id from section where sem = ‘Fall’ and year = 2009)
except
(select course_id from section where sem = ‘Spring’ and year = 2010)
#找出在2009年秋季学期开课但不在2010年春季学期开课的所有课程id号

自动消除冗余

保留所有的冗余

要保留所有的冗余要使用相应的多集操作版本union all,intersect all 和 except all

假如一个元组在 r 关系出现了 m 次，在 s 关系出现了 n 次，那么该元组：
✓在 r union all s 中 出现 m + n 次
✓在 r intersect all s 中 出现 min(m,n）次
✓在 r except all s 中 出现 max(0, m – n)次

空值

属性值可以被置为空值，以null表示

空值表示一个未知值或者该值不存在

所有涉及到空的算术表达式的结果为null

eg

5 + null 返回null

使用null的谓词可以用来测试空值（is null, is not null）

select name
from instructor
where salary is null;
#找出没有记录薪水的教师

不是 = null

涉及空值的任何比较运算的结果返回unknown

eg

5 < null， null <> null， null = null

三值逻辑可以处理unknown

✓OR: (unknown or true) = true,
(unknown or false) = unknown
(unknown or unknown) = unknown
✓AND: (true and unknown) = unknown,
(false and unknown) = false,
(unknown and unknown) = unknown
✓NOT: (not unknown) = unknown

如果谓词 P 等于 unknown，则 “P is unknown” 为真

如果 where 子句的谓词结果为unknown ， 可当做false来处理(不计入结果集)

如果元组在所有属性上取值相等，那么它们就被当作是相同元组，即使某些值为空

eg

{(‘A’, null), (‘A’, null)} 在去除重复元组时，只包留上述元组的一个拷贝

(‘A’, null) ＝ (‘A’, null) 逻辑判断结果为unknown

distinct子句和谓词中对待空值的方式不同

聚集函数

聚集函数是以值的一个集合（集或多重集）为输入、返回单个值的函数

avg: 平均值 输入必须是数字集
min: 最小值 返回一个标量（单行单列）
max: 最大值 返回一个标量（单行单列）
sum: 总和 输入必须是数字集
count: 计数

基本聚集

avg()

eg

select avg(salary)
from instructor
where dept_name =‘Comp. Sci.’;
#找出 Computer Science系教师的平均工资

count ()

eg

select count (distinct ID)
from teaches
where semester =‘Spring’ and year = 2010;
#找出在2010年春季讲授一门课程的教师总数

select count (*)
from course;
#找出course 关系中的元组数

分组聚集 – group by 子句

在group by子句中所有属性相同的元组被分在一组

eg

select dept_name, avg(salary)
from instructor
group by dept_name;
#找出每个系的平均工资

没有教师的系不会在结果中出现

分组聚集规定

出现在 select 语句中但没有被聚集的属性只能是出现在group by 子句中的那些属性。即在select子句中出现、但没有在出现group by子句中的属性，只能出现在聚集函数的内部（如sum、count、avg等）

eg

/* 错误查询 */
select dept_name, ID, avg (salary)
from instructor
group by dept_name;

ID未出现

分组聚集–having子句

having子句：分组限定条件

where子句：元组限定条件

eg

select dept_name, avg (salary)
from instructor
group by dept_name
having avg (salary) > 42000;
#找出所有教师平均工资超过 42000 美元的系的名字和平均工资

having子句中的谓词在形成分组之后才起作用，因此可以使用聚集函数

任何出现在having子句中但没有被聚集的属性，必须出现在group by子句中

空值和聚集

sum求和运算忽略输入集合中工资为 null 的值

eg

select sum(salary )
from instructor
#工资总额的查询

聚集函数根据以下原则处理空值

除了count(*)之外，所有的聚集函数都忽略输入集合中的空值

如果聚集函数输入集合只有空值（即空集）？
• count函数运算返回 0
• 其他聚集函数都返回 null

聚集函数一般在select、having子句中使用

SQL语句操作顺序

如果在同一个查询中同时存在where子句和having子句以及聚集运算，那么按如下顺序来理解

1. 根据from子句计算出一个关系；
2. 应用where子句中的谓词；
3. 满足where 谓词的元组通过group by子句形成分组；
4. having 子句若存在，就将其作用于每一分组。不符合having子句谓词的分组将被抛弃；
5. 剩余的分组被select子句用来应用聚集函数产生查询结果元组

嵌套子查询

SQL提供嵌套子查询机制

子查询是嵌套在另一个查询中的select-from-where表达式

通常用于对集合的成员资格（是否在集合中）、集合的比较以及集合的基数进行检查

✓集合成员资格测试
✓集合的比较
✓空关系测试
✓重复元组存在性测试
✓from子句中的子查询
✓with子句
✓标量子查询

集合成员资格测试

eg

in

select distinct course_id
from section
where semester = ’Fall’ and year= 2009 and
course_id in ( select course_id
from section
where semester = ’Spring’ and year= 2010);
#找出在2009年秋季和2010年春季学期同时开课的所有课程id

not in

select distinct course_id
from section
where semester = ’Fall’ and year= 2009 and
course_id not in ( select course_id
from section
where semester = ’Spring’ and year= 2010);
#找出所有在2009年秋季学期开课但不在2010年春季学期开课的课程id

多关系测试查询举例

eg

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);
#找出（不同的）学生总数，他们选修了ID为10101的教师所讲授的课程段

空关系测试

用于测试一个子查询结果是否为空集（是否存在元组）

exists结构在作为参数的子查询非空时返回true值

✓若 exists r 逻辑表达式为 r不为空集时为true
✓若 not exists r 逻辑表达式为 r为空集时为true

eg

select course_id
from section as S
where semester =‘Fall’ and year= 2009 and
exists ( select *
from section as T
where semester =‘Spring’ and year= 2010
and S.course_id = T.course_id);
#找出在2009年秋季学期和2010年春季学期同时开课的所有课程

相关子查询

使用了来自外层查询中出现的表的列的子查询

相关名称作用域：在一个子查询中，可以使用此子查询本身定义的、或者包括此子查询的任何查询中定义的相关名称；类似于编程语言中的变量作用域

From子句中的子查询

eg

select dept_name, avg_salary
from ( select dept_name, avg (salary) as avg_salary
from instructor
group by dept_name)
where avg_salary > 42000;
#找出系平均工资超过$42,000的那些系中教师的平均工资

不需要另外使用having子句

lateral

lateral关键字：使得from子句中的子查询使用来自其他关系的相关变量

select name, salary, avg_salary
from instructor as I1, lateral (
select avg(salary) as avg_salary
from instructor as I2
where I2.dept_name = I1.dept_name);
#查询每位老师的姓名及其工资和所在系的平均工资

目前只有少数SQL实现支持lateral子句

标量子查询

该子查询返回包含单个属性的单个元组（count、max）

标量子查询可以出现在select、where、having子句中

如果子查询被执行后其结果中有不止一个元组，则产生一个运行错误

用于select 子句

eg

select dept_name,
( select count(*)
from instructor
where department.dept_name = instructor.dept_name)
as num_instructors
from department;
#列出所有系名及其教师数

用于where 子句

eg

select name
from instructor
where salary * 10 >
( select budget from department
where department.dept_name = instructor.dept_name);

不带from子句的标量

某些查询语句需要计算，无需引用任何关系

eg

select (select count(*) from teaches) / (select count(*) from instructor)
#查询平均每位教师所讲授（无论是学年还是学期）的课程段数，其中由多位教师所讲授的课程段对每位教师计数一次

由于上述查询用整数除以另一个整数，在大多数数据库中，结果也是一个整数，会有精度损失

✓可以在除法之前将两个子查询的结果 乘以1.0
✓或者使用cast类型强制转换

数据库的修改

✓在给定的关系中删除元组
✓在给定的关系中插入新元组
✓在给定的关系中更新某些元组

删除

eg

delete from instructor
#删除instructors关系中的所有元组

delete from instructor
where dept_name =‘Finance’;
#删除Finance 系教师

delete from instructor
where dept_name in (select dept_name
from department
where building =‘Watson’);
#删除所有在位于Watson大楼的系工作的教师

问题

delete from instructor
where salary < (select avg (salary)
from instructor);
#删除工资低于大学平均工资的教师记录

问题: 当我们删除元组时，平均工资会发生变化
SQL的解决方案:
1.首先,计算平均工资，找出并标记需删除的元组
2.然后,删除需删除的元组 (无需重新计算平均值或重新测试元组)

插入

eg

insert into coursevalues (’CS-437’, ’Database Systems’, ’Comp.Sci.’, 4);
#将一个新元组插入course

insert into course (course_id, title, dept_name, credits) values (’CS-437’, ’Database Systems’, ’Comp.Sci.’, 4);
#将一个新元组插入course

insert into student values (’3003’, ’Green’, ’Finance’, null);
#将一个新元组插入student并且使 tot_creds置为空

可以在select子句中加入标量

更新

#给工资超过$100,000 的教师涨 3%的工资,其余教师涨 5%
update instructor
set salary = salary * 1.03
where salary > 100000;
update instructor
set salary = salary * 1.05
where salary <= 100000;

✓上述语句顺序非常重要，否则工资略少于100000美元的老师会涨8.15%
✓如果使用 case 语句会更好

case

case
when pred1 then result1
when pred2 then result2
…
when predn then resultn
else result0
end

eg

#给工资超过$100,000 的教师涨 3%的工资,其余教师涨 5%
update instructor
set salary = case
when salary <= 100000 then salary * 1.05
else salary * 1.03
end

总结

SQL语言不仅仅是一个“查询”语言，它包括哪几个部分

DDL 数据定义语言

DML 数据操纵语言

完整性

视图定义

授权

事务控制

嵌入式SQL和动态SQL

SQL提供select, from, where查询数据库的语言结构，分别对应于关系代数的哪些操作

SQL还提供属性、关系重命名；集合操作、聚集函数运算、嵌套子查询等操作

SQL不仅可以提供数据库的查询，还可以对其进行修改、插入和删除信息

习题

insert into student values (’3003’, ’Green’, ’Finance’, null);
#将一个新元组插入student并且使 tot_creds置为空

可以在select子句中加入标量

更新

#给工资超过$100,000 的教师涨 3%的工资,其余教师涨 5%
update instructor
set salary = salary * 1.03
where salary > 100000;
update instructor
set salary = salary * 1.05
where salary <= 100000;

✓上述语句顺序非常重要，否则工资略少于100000美元的老师会涨8.15%
✓如果使用 case 语句会更好

case

case
when pred1 then result1
when pred2 then result2
…
when predn then resultn
else result0
end

eg

#给工资超过$100,000 的教师涨 3%的工资,其余教师涨 5%
update instructor
set salary = case
when salary <= 100000 then salary * 1.05
else salary * 1.03
end

总结

SQL语言不仅仅是一个“查询”语言，它包括哪几个部分

DDL 数据定义语言

DML 数据操纵语言

完整性

视图定义

授权

事务控制

嵌入式SQL和动态SQL

SQL提供select, from, where查询数据库的语言结构，分别对应于关系代数的哪些操作

[外链图片转存中…(img-FZ9oIQsC-1674988330905)]

SQL还提供属性、关系重命名；集合操作、聚集函数运算、嵌套子查询等操作

SQL不仅可以提供数据库的查询，还可以对其进行修改、插入和删除信息

习题

最后

以上就是坦率鼠标为你收集整理的数据库系统课程笔记03 SQL03 SQL的全部内容，希望文章能够帮你解决数据库系统课程笔记03 SQL03 SQL所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。