跳转至

第 3 章：SQL 语言

像之前说过的那样，SQL 包含数据定义部分（DDL）、数据操作部分（DML）和数据控制部分（DCL）。

数据定义语言

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
create table teammate(
  id char(4),
  name varchar(20) not null,
  species varchar(20),
  hp numeric(8, 2),
  lv int,
  affection float(53),
  primary key (id),
  check(hp > 0)
);

注意名字里面不能写减号“-”，并且是大小写不敏感的。

域的类型

其中，char(n) 是个长度为 n 的定长字符串，varchar(n) 是个最大长度为 n 的变长字符串。

之外，还有 int 和 smallint。它们能表示的范围在不同机器上可能会不同。

numeric(p, d) 是一个定长数字，总共有 p 位的精度，其中小数点后有 d 位。除此之外还有 real 和 double precision；以及 float(n)，精度至少为 n 位的浮点数。

对于时间，有专门的 date、time 和 timestamp。

约束

primary key 定义了本张表的主码（不能为空）；not null 表示这一栏不能为空。

另外可以在 check() 里面写表达式。

删表和改表

删表：drop table r。

在表里面增加新的属性：alter table r add A D，其中 A 是新属性的名字，D 是对应的域。也可以 alter table r add (A1 D1, A2 D2, ...)。

在表里面删掉属性：alter table r drop A，其中 A 是新属性的名字。注意很多数据库都不支持。

修改属性：alter table r modify (A1 D1, A2 D2...)。

建立索引

create index indexname on tablename (attributelist)

create unique index indexname on tablename (attributelist)，可以指定一个候选码，这样的话效率会更高。

选择

1
select id, name from teammate where hp >= 100;

一个一般的 SQL 选择操作都长得像下面这样：

1
2
3
select A1, A2, ..., An
from r1, r2, ..., rm
where P;

返回的结果等价于 \(\Pi_{A_1, A_2, \cdots, A_n}(\sigma_P(r_1 \times r_2 \times \cdots \times r_m))\)，不过有的数据库不会去重。

select * 会选择所有的属性。

where

where 的逻辑连接词有 or、and、not 和 between and。

from

from 后面接的表会做笛卡尔积。

重命名

as 可以把一个属性或者一个表重命名。

1
2
select hp as hit_point
from teammate;

找到 HP 比至少一位月亮伊布高的成员名：

1
2
3
select distinct A.name
from teammate as A, teammate as B
where A.hp > B.hp and B.species = '月亮伊布';

distinct 会进行去重。

字符串操作

匹配

1
2
3
select name
from teammate
where species like '%喵%';

SQL 的通配符 % 代表任意字符串，_ 代表任意一个字符。用 \ 转义。

比如上面会匹配上“火斑喵”、“喵喵”、“魔幻假面喵”等；'喵% 只会匹配上“喵喵”。

串联

1
2
3
select '名字 = ' || name
from teammate
where ...

会有这样的输出：

1
2
3
4
名字 = 妙艾拉
名字 = 阿罗勒
名字 = 凯蒂
...

排序

1
2
3
4
select name, lv
from teammate
where species = '魔幻假面喵'
order by lv desc, name asc;

把所有的魔幻假面喵先按等级降序排序，其次按名字升序排序。

排序默认是升序排序。

多重集 multiset

\(\sigma_{\theta}, \Pi_{A}, \times\) 等符号代表运算结果是个多重集。

集合运算

SQL 提供 union、intersect 和 except，对应的分别是 \(\cup\)、\(\cap\) 和 \(-\)。

它们默认都是去重的；使用 union all、intersect all 和 except all 来不让它们去重。

1
2
3
4
5
6
7
(select name, species
from teammate
where species like '%路%')
union all
(select name, species
from teammate
where species like '%卡%')
name species
里奥 利欧路
帝牙卢卡 帝牙卢卡
凯蒂 路卡利欧
凯蒂 路卡利欧
... ...

some

all

exists；not exists

unique；not unique

聚合函数

  • avg(col)
  • min(col)
  • max(col)
  • sum(col)
  • count(col)
1
2
3
4
select species, avg(affection) as avg_affection
from teammate
group by species
having avg_affection >= 255;

视图 view

1
2
3
4
5
create view <view_name> (c1, c2, ...) as
select e1, e2, ...
from ...;

drop view <view_name>;

with

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
with
  max_hp as
    select top 1 *
    from teammate
    order by hp
select hp
from teammate, max_hp
where teammate.hp = max_hp.hp;

with
  max_hp(value) as
    select max(hp)
    from teammate
select hp
from teammate, max_hp
where teammate.hp = max_hp.value;

数据库的修改

删除

在一个表或者视图里面删除一些行：

1
2
delete from <table/view>
[where <condition>]

比如：

1
2
3
4
5
delete from tasklist
where name in(
  select name
  from branch
  where branch_type = 'Personal');

插入

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
insert into teammate
values ('0490', '玛纳霏', '玛纳霏', 69, 40, 240);

insert into teammate (id, name, species, hp, lv, affection)
values ('0488', '克雷色利亚', '克雷色利亚', 130, 50, 115);

insert into teammate
  select id, name, species, hp, lv, 70
  from wild_pokemons
  where species = '利欧路';

更新

有限定条件的更新：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
update teammate
set affection = affection * 1.01
where hp >= 100;

update teammate
set affection = case
  when hp >= 100
  then affection * 1.01
  else affection - 20
end;

视图的操作

现在有这样的一个视图。

1
2
3
create view exp_team as
  select id, name, species
  from teammates;

如果针对视图进行更新，比如：

1
2
insert into exp_team
values ('493', '阿尔宙斯', '阿尔宙斯');

那么变化会表现到原来的表上，也就是变成：

1
2
insert into teammate
values ('493', '阿尔宙斯', '阿尔宙斯', null, null ...

如果变化没法正确表现到原来的表上，那么更新就会失败。比如如果有视图和多个表有关，如果没有特别设计的话一般都没有办法。

事务 Transactions

帕路奇亚：从我的存款里面取 13000 宝可币出来转给帝牙卢卡。

兼职员工 黑夜魔灵：行，我这边给你登记一下。

一个事务（Transactions）里面包含了一系列原子性的操作，它们可不能从中间打断。

如果下面的语句才执行完第一条的时候，系统就崩了，会怎么样？

1
2
3
4
update account set balance = balance - 13000
where account_name = '帕路奇亚';
update account set balance = balance + 13000
where account_name = '帝牙卢卡';

通过 atomic 来让它们要么都不执行要么都执行。

1
2
3
4
5
6
begin atomic
  update account set balance = balance - 13000
  where account_name = '帕路奇亚';
  update account set balance = balance + 13000
  where account_name = '帝牙卢卡';
end

连接

1
2
3
4
inner join
left outer join
right outer join
full outer join

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
-- SQL Server
select teammate.id, teammate.name, pokedex.atk
from teammate left outer join pokedex on
  teammate.species = pokedex.species;

select teammate.id, teammate.name, pokedex.atk
from teammate, pokedex
where teammate.species *= pokedex.species;

-- Oracle
select teammate.id, teammate.name, pokedex.atk
from teammate, pokedex
where teammate.species = pokedex.species (+);