东北大学数据库笔记3-SQL

Domian Types in SQL

• char(n)：定长字符串，用户指定长度n
• varchar(n)：可变长度字符串，具有用户指定的最大长度 n
• int 整数：（与机器相关的整数的有限子集）
• smallint：小整数（整数域类型的机器相关子集）
• numeric(p,d)： 定点数，用户指定精度为 p 位，小数点右侧有 d 位
• real, double precision： 浮点数和双精度浮点数，具有与机器相关的精度
• float(n)：浮点数，用户指定的精度至少为 n 位
• 更多内容将在第 4 章中介绍。

创建表

略

删除表

drop table

改变表结构 Alter Table Constructs

使用alter table
添加列：
其中 A 是要添加到关系 r 的属性的名称，D 是 A 的域
关系中的所有元组都被分配为 null 作为新属性的值

| 1

| alter table r add A D

|
| — | — |

删除列：

| 1

| alter table r drop A

|
| — | — |

mysql里面删除是不可撤回的

基本查询

和关系代数是对应的

SQL 名称不区分大小写（即，您可以使用大写或小写字母。）
SQL 允许在关系和查询结果中重复（duplicates）
一些基本用法略

ESCAPE

• 回避特殊符号的：使用转义符。例如：将[%]转为[ %]、[]转为[ ]，然后再加上[ESCAPE‘$’]即可。
  | 1
  2
  3

| SELECT job_id
FROM jobs
WHERE job_id LIKE ‘IT\_%‘;

|
| — | — |

• 如果使用\表示转义，要省略ESCAPE。如果不是\，则要加上ESCAPE。
  | 1
  2
  3

| SELECT job_id
FROM jobs
WHERE job_id LIKE ‘ITKaTeX parse error: Expected group after '_' at position 1: _̲%‘ escape ‘‘;

|
| — | — |

Set Operations

union，intersect，except表示并，交，减
他们都是自动去重的，要保留重复，需要加上all

聚合函数 Aggregate Functions

略
注意下聚合函数不能嵌套，只能用having过滤
count里面可以加distinct，计算去除重复后的数量

Null Values

记住判断NULL要用is null

在OR的情况下：true>unknown>false
结果是等级高的词
在AND的情况下：true>unknown>false
结果是等级低的词

子查询 Nested Subqueries

子查询有三个使用方法

• Set Membership
• Set Comparison
• Set Cardinality

这里可以用表连接，也可以用子查询
表连接+判断可以起到some判断的效果

这里提到了some和all
正如字面意思，some就是存在 all就是任意
但要注意加入否定的情况下

SQL的all、any和some的用法
1、首先t1和t2表如下： t1(2,3) t2(1,2,3,4)
2、all,any,some 的子查询
2.1 all 父查询中的结果集大于子查询中每一个结果集中的值,则为真
select * from t2 where n>all (select n from t1) —4
2.2 any,some 父查询中的结果集大于子查询中任意一个结果集中的值,则为真
select * from t2 where n>any(select n from t1) –3 4
select * from t2 where n>some(select n from t1) –3 4
2.3 any 与子查询in相同
select * from t2 where n=any (select n from t1) —2 3
xxselect * from t2 where n in (select n from t1) —dsd2 3
2.4 any 与not in
1）any
–or作用 父查询中的结果集不等于子查询中的a或者b或者c,则为真（只要有一个不成立就为True）
select * from t2 where n <>any(select * from t1) —–1 2 3 4
2）not in
–and作用 父查询中的结果集不等于子查询中任意一个结果集中的值,则为真
select * from t2 where n not in(select * from t1) —1 4

复杂查询

exists 和not exists

如果参数子查询非空，exists 构造返回值 true

exists的例子

再来看看前面同一个问题的解决方法：

unique

在查找“最多只有一个xxx”时使用

在**“至少两个时”**使用not unique

在from中使用子查询

with语句

View 视图

在某些情况下，并不希望所有用户都看到整个逻辑模型（即存储在数据库中的所有实际关系）。
考虑一个需要知道客户姓名、贷款编号和分行名称的人，但是 无需查看贷款金额。 此人应该看到用 SQL 描述的关系
视图是由几个基本表导出的表，但不存储在数据库中，数据库中只有视图的定义，而不存放对应的数据
View创建方法：

• 定义视图后，视图名称可用于引用视图生成的虚拟关系。
• 创建视图时，查询表达式存储在数据库中;使用视图将表达式替换为查询

即View也可以写在from后面，当作表

• View还可以通过其他View生成

可以在定义另一个视图的表达式中使用一个视图
如果使用V2定义V1，则据说视图关系V1直接依赖（depend directly）于视图关系V2
如果v1 直接依赖于v2 或者存在从v1 到v2 的依赖路径，则称视图关系v1 依赖（depend on）于视图关系v2
如果视图关系 v 依赖于自身，则称它是递归（recursive）的
其他点：
视图的创建和删除只影响视图本身，不影响对应的基表。但是当对视图中的数据进行增加、删除和 修改操作时，数据表中的数据会相应地发生变化，反之亦然。
说明1：实际上就是我们在 SQL 查询语句的基础上封装了视图 VIEW，这样就会基于 SQL 语句的结果集形成一张虚拟表。
说明2：在创建视图时，没有在视图名后面指定字段列表，则视图中字段列表默认和SELECT语句中的字段列表一致。如果SELECT语句中给字段取了别名，那么视图中的字段名和别名相同。

虽然可以更新视图数据，但总的来说，视图作为 虚拟表 ，主要用于 方便查询 ，不建议更新视图的 数据。
对视图数据的更改，都是通过对实际数据表里数据的操作来完成的。
删除视图：

| 1

| DROP VIEW IF EXISTS 视图名称;

|
| — | — |

说明：基于视图a、b创建了新的视图c，如果将视图a或者视图b删除，会导致视图c的查询失败。这 样的视图c需要手动删除或修改，否则影响使用。
修改视图

| 1
2
3

| ALTER VIEW 视图名称
AS
查询语句

|
| — | — |

删除

删除行使用delete 不需要加*
delete会删除整行，和select *效果相同

删除中的问题：
删除所有余额低于银行平均水平的账户记录：

| 1
2
3

| delete from account
where balance < (select avg (balance )
from account )

|
| — | — |

问题：当我们从存款中删除元组时，平均余额发生变化
SQL中使用的解决方案：
1.首先，计算平均余额并找到所有要删除的元组
2.接下来，删除上面找到的所有元组（无需重新计算平均或重新测试元组）
也就是说在删除过程中原理的聚合函数值保持不变

插入

在插入时可以给出列名也可以不给出

也可以插入查询的内容

更新

使用update set
使用where指定需要修改的内容

| 1
2
3

| UPDATE employees
SET department_id = 70
WHERE employee_id = 113;

|
| — | — |

如果不使用where，则所有数据都会被更新
更新中也会遇到order问题

使用case：

通过视图更新

在视图的更新过程中如果没有赋值的属性在原表中会被赋值为null
通过视图的一些更新是不可能转化为数据库关系的更新
大多数 SQL 实现只允许更新在单个关系上定义的简单视图（没有聚合）

Joined Relations

• 加入操作采取两次关系并随后返回另一个关系
• 这些附加操作通常在FROM子句中用作子查询表达式
• 连接条件 - 定义两个关系匹配中的哪些元组，以及连接结果中存在哪些属性

一般用自然连接，注意题中给出的条件有没有all，判断是否需要外连接

题

Intersect

| 1
2
3
4

| SELECT A.LASTNAME, A.FIRSTNAME
FROM AUTHOR A, BOOK B1, BOOK B2
WHERE B1.AID=A.AID AND B2.AID=A.AID AND
B1.SUBJECT =‘art’ and B2.SUBJECT=‘business’

|
| — | — |

等价