SQL语法入门

创建数据库

create database 库名;
create database if not exists 库名;
1
2

创建数据表

create table 表名
(
  列名 数据类型 列约束,
  列名 数据类型 列约束,
  ...
  表约束
);
1
2
3
4
5
6
7

列约束

• null - 可空

• not null - 非空

• default x - 默认值

• primary key - 主键

• references 表名[(列名)] - 外键

  如果仅指定表名，则会自动使用表的主键，这时要求表必须有主键

  如果同时指定了列名，则要求列必须有 unique 约束

• [constraint 约束名] check (约束条件)

  当前列的约束条件

• [constraint 约束名] unique - 取值唯一

表约束

• [constraint 约束名] primary key (主键列, 主键列, …)

• [constraint 约束名] foreign key (外键列, …) references 表名 (主键列, …)

• [constraint 约束名] check (约束条件)

  可以定义列与列之间的约束条件

• [constraint 约束名] unique (列, 列, …)

表约束中，通常需要列出多个列名，列名应写在括号内。

外键约束

• foreign key … references … on delete cascade

  删除被参照关系的元组时，同时删除参照关系中的元组

• foreign key … references … on delete set null

  删除被参照关系的元组时，将参照关系中相应属性值置为空值

  这种情况下仍可以给这个属性设置非空约束，这时被参照关系的相应元组将无法删除

示例

    create table Poduct
    (
      product_id      char(4)       not null,
      product_type    varchar(32)   not null,
      sale_price      integer       ,
      regist_date     date          default CURRENT_DATE,
      primary key (product_id)
    );

    create table test1 (
      x integer check( x > 0 and  x < 10),  -- 检查约束
      y integer unique                      -- 唯一性约束
    );

    create table test2 (
      x integer,
      y integer,
      check ( x > 0 and y < 10),            -- 检查约束
      unique ( x, y )                       -- 唯一性约束
    );
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

按查询结果创建表

create table 新表名 as select ... from ...;
1

• 本方法从查询结果中创建出新表，同时新表中将包含查询到的所有数据，此外还可以得到列名和数据类型信息，但不会包含列约束和行约束

约束

主键

主键包括以下特点：

• 任意两行的主键值都不相同
• 每行都具有一个主键值（即列中不允许NULL值）
• 包含主键值的列从不修改或更新
• 主键值不能重用，如果从表中删除某一行，其主键值不能分配给新行

外键

外键是表中的一列，其值必须列在另一表的主键中。在定义外键后，DBMS不允许删除在另一个表中具有关联行的行。

唯一性约束

唯一性约束用来保证一列（或一组列）中的数据是唯一的，但与主键不同：

• 表中可包含多个唯一性约束
• 唯一约束列可包含 NULL 值
• 唯一约束列可修改或更新
• 唯一约束列的值可重复使用

检查约束

检查约束用来保证一列（或一组列）中的数据满足一组指定的条件。

删除数据表

drop table 表名;
1

修改数据表

alter table 表名 rename to <新表名>;
alter table 表名 add [column] <列定义>;
alter table 表名 drop column <列名>;
alter table 表名 alter column 列名 set default x/null;
alter table 表名 alter column 列名 drop default;
alter table 表名 alter column 列名 set|drop not null;
alter table 表名 modify 列名 数据类型;
alter table 表名 add constraint 约束名 primary key (主键列);
alter table 表名 add constraint 约束名 foreign key (外键列) references 表名 (主键列);
alter table 表名 add constraint 约束名 unique (列名, 列名, ...);
alter table 表名 add constraint 约束名 check (约束条件);
alter table 表名 drop constraint 约束名;

alter table "TestB" add constraint pk primary key (name);
alter table "TestA" add constraint fk foreign key (name) references "TestB" (name);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

插入数据

insert into 表名 values (值, 值, ...)[,(值, 值, ...), ...];  
insert into 表名 [("列名", "列名", ...)] values (值, 值, ...);  
insert into 表名 [("列名", "列名", ...)] values (值, 值, ...)[,(值, 值, ...), ...];  
insert into 表名 [("列名", "列名", ...)] select ... from ...;  
1
2
3
4

• 如果要插入 null，直接插入 null 关键字
• 如果要插入默认值，直接插入 default 关键字
• 列清单可以省略某些列，插入时列值自动设为默认值或null

删除数据行

delete from 表名;             -- 删除全部数据行
delete from 表名 where 条件;
truncate 表名;                -- 重新定义表，原表丢弃，所以速度很快
1
2
3

更新数据

update 表名 set 列名=值 [, 列名=值 ...];
update 表名 set (列名[, 列名 ...]) = (值 [, 值 ...]);
update 表名 set 列名=值 [, 列名=值 ...] where 条件;
1
2
3

• SET 子句中赋值表达式的右边不仅可以是单纯的值，还可以是包含列的表达式。

示例

update C set Credit=Credit+1;
1

---

查询

select [all|distinct] "字段" [[as] 别名][,"字段" [[as] 别名], ...]
from 表 [[as] 别名][,表 [[as] 别名] ...]
on 联结条件
where 行过滤
group by "字段"[,"字段", ...]
having 分组过滤
order by "字段" [asc|desc] [,"字段" [asc|desc] , ...]
limit x
offset x;
1
2
3
4
5
6
7
8
9

• 当列(表)名含有大写字母时，应使用双引号（不含单引号），否则列(表)名自动转换为全小写
• 可以使用 * 号来指代所有列
• 列可以是常数，如: select '商品' as str;
• distinct 表示删除重复的行，重复指的是列组合的重复，而不是单指一个列或者每个列，或者说是以行为单位进行判断
• 列名的 as 别名 可以使用中文，这时需要使用双引号包围，其中还可以包含空格
• 不管是字段别名的 as 还是表别名的 as，都可以省略 as 这个关键字
• 如果对表进行了重命名，那么在需要使用表名的时候（比如通过表名限定列），都必须使用新的表名
• on 必须写在 where 之前
• group by 子句中不能使用列别名
• order by 子句默认使用升序排序，如果要进行降序排序，可指定 desc 关键字
• 只有 select、having、order by 子句能够使用聚合函数
• 使用聚合函数时，SELECT 子句中只能存在以下三种元素:
  常数
  聚合函数
  GROUP BY子句中指定的列名（也就是聚合键）
• having子句中只能存在以下三种元素:
  常数
  聚合函数
  GROUP BY子句中指定的列名（也就是聚合键）

谓词

• = <> != < <= > >= (不适合与 NULL 比较)

• between x and y

  between 包含了两个临界值（两个临界值也符合条件）

• in (x, y, …)

• is null

  除非使用 is null，否则返回的结果中总是不会包含 null

• is not null

• like ‘xxx %_’

  like 中 % 代表“0个字符或以上的任意字符串”，_ 代表“任意 1 个字符”

  like '%' 不会匹配 null

  like 后所跟字符串必须使用单引用，不能使用双引号

  为了能在字符串中使用 % 和 _，需要使用转义符号，例如：like 'ab\%cd' escape '\'

• not 某一条件

• sqlite 和 postgreSQL 都不支持 !< 和 !>

• 可以使用函数表达式，例如: length(name) > 10

• 可以使用 and 或 or 来组合多个条件

  and 的优先级比 or 高

• 要控制优先级请使用圆括号

select num from users order by num;
select num from users order by "lastName";
select * from users where "lastName" like '%';
select * from users where "lastName" like '%ab\\cd\%' escape'\'; -- 以 ab\cd% 结尾的字符串
1
2
3
4

EXISTS（存在）

• EXISTS 表示存在
• NOT EXISTS 表示不存在

SELECT Cno, Cname 
from C 
WHERE NOT EXISTS (SELECT * from SC where SC.Cno = C.Cno);
1
2
3

上例中，从 C 表中筛选出行，要求筛选出的行在执行 SELECT * from SC where SC.Cno = C.Cno 时结果为空，注意，not exists 后面的 select 语句中，C 就代表筛选的行。其中的 select 子句 SELECT * from SC where SC.Cno = C.Cno 是不能单独执行的（C 表没有包含在 from 子句中）。

ANY / ALL

示例:

select Sname, Age
from S
where Age < ALL (select age from S where SD='计算机')
  and SD <> '计算机';
  
<=>
  
select Sname, Age
from S
where Age < (select MIN(age) from S where SD='计算机')
  and SD <> '计算机';
1
2
3
4
5
6
7
8
9
10
11

计算字段

字段可以是一个表达式，如函数调用、运算操作等，此外，可以使用 as 给计算字段指定别名

• || 字符串拼接
• + - * / % ^算术运行，注意，整数相除结果仍为整数

字符串函数

• trim() ltrim() rtrim()
• upper() lower()
• substring() left() right()
• length()
• md5()

数学函数

• ceil() floor() round() trunc()
• random() setseed()

时间函数

• now() current_date current_time
• make_date(year int, month int, day int)
• make_time(hour int, min int, sec double precision)

聚集函数

• avg([distinct] 字段) - 某列的平均数
• count([distinct] 字段 / *) - 某列的行数
• max(字段) - 某列的最大值
• min(字段) - 某列的最小值
• sum([distinct] 字段) - 某列之和

select '"'||trim(prod_name)||'"' as prod_name from Products;
select count(distinct prod_price) from Products;
-- 查询其他系中比计算机系所有学生年龄都要小的学生的姓名和年龄
select name, age
from S
where age < ALL(select age from S where SD='计算机') and SD <> '计算机';
1
2
3
4
5
6

---

子查询

子查询就是嵌套在查询内部的查询，通常如下使用：

1. 子查询返回一列多行，这时子查询可用于外部查询的 where in 子句

2. 子查询返回一个值（使用聚集函数），这时子查询可用于外部查询的 select 子句，每一行外部查询执行一次子查询

3. 子查询返回一个值（一行一列）的子查询称为标量子查询，这时子查询可用于外部查询的 where 单值比较子句

   • 标量子查询的书写位置并不仅仅局限于 WHERE 子句中，通常任何可以使用单一值的位置都可以使用。也就是说，能够使用常数或者列名的地方，无论是 SELECT 子句、GROUP BY 子句、HAVING 子句，还是ORDER BY 子句，几乎所有的地方都可以使用

4. 子查询可理解为一次性视图，所以可以当成一个虚拟表来使用，比如直接置于 from 子句中

• 原则上子查询必须设定名称，应尽量从处理内容的角度出发为子查询设定恰当的名称

SELECT product_type, cnt_product
FROM ( SELECT product_type, COUNT(*) AS cnt_product
       FROM Product
       GROUP BY product_type ) 
     AS ProductSum
-- 本例中 AS ProductSum 就是为子查询设置了名称 "ProductSum"
1
2
3
4
5
6

示例

select cust_id from orders
where order_num in (
    select order_num from OrderItems where prod_id = 'RGAN01'
);

select cust_name, cust_state, (
    select count(*) from Orders where Orders.cust_id = Customers.cust_id
) as Orders 
from Customers;

select cust_id, cust_name, cust_contact
from Customers
where cust_name = (
    select cust_name from Customers where cust_contact = 'Jim Jones'
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

内联结

内联结相当于从笛卡儿积中筛选出有意义的数据，方法是为 from 子句提供多个表以创建笛卡儿积，为 where 子句提供条件以筛选出有意义的数据。
此外，笛卡儿积（交叉联结）可以使用特殊的 cross join 语法形式，内联结可以使用特殊的 ... inner join ... on ... 语法形式。

示例

select vend_name, prod_name, prod_price
from Vendors, Products
where Vendors.vend_id = Products.vend_id;

select vend_name, prod_name, prod_price
from Vendors inner join Products
on Vendors.vend_id = Products.vend_id;
1
2
3
4
5
6
7

外联结

外联结与内联结类似，内联结会筛选行，所以可能某些行不会包含到筛选结果中。而外联结则会把筛选掉的行也包含进来。

示例

    shop=# select * from "TestA";
     describe | name
    ----------+------
     a        |    1
     aa       |    1
     b        |    2
     c        |    3
     d        |    4

    shop=# select * from "TestB";
     des | name
    -----+------
     A   |    1
     B   |    2
     E   |    5

    shop=# select A.*, B.des from
    shop-# "TestA" as A inner join "TestB" as B
    shop-# on A.name = B.name;
     describe | name | des
    ----------+------+-----
     a        |    1 | A
     aa       |    1 | A
     b        |    2 | B

    shop=# select A.*, B.des from
    shop-# "TestA" as A left outer join "TestB" as B
    shop-# on A.name = B.name;
     describe | name | des
    ----------+------+-----
     a        |    1 | A
     aa       |    1 | A
     b        |    2 | B
     c        |    3 |
     d        |    4 |

    shop=# select A.*, B.des from
    shop-# "TestA" as A right outer join "TestB" as B
    shop-# on A.name = B.name;
     describe | name | des
    ----------+------+-----
     a        |    1 | A
     aa       |    1 | A
     b        |    2 | B
              |      | E

    shop=# select A.*, B.des from
    shop-# "TestA" as A full outer join "TestB" as B
    shop-# on A.name = B.name;
     describe | name | des
    ----------+------+-----
     a        |    1 | A
     aa       |    1 | A
     b        |    2 | B
     c        |    3 |
     d        |    4 |
              |      | E
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57

组合查询

-- 并集:
select ... union select ... ;         -- 两个集合中的重复行只包含一次
select ... union all select ... ;     -- 两个集合中的重复行都包含
-- 交集:
select ... intersect select ... ;
-- 差集:
select ... except select ... ;
1
2
3
4
5
6
7

• union 会自动剔除多个 select 语句中重复的行，但 union all 则会保留
• 组合查询只能使用一个 order by 子句，而且必须置于最后
• 多个查询只要返回的列数量相同、数据类型兼容即可组合，不管列名或表名是否一致

示例

    shop=# select * from "TestA";
     describe | name
    ----------+------
     a        |
     aa       |
     b        |
     c        |
     d        |

    shop=# select * from "TestB";
     des | name
    -----+------
     A   |    1
     B   |    2
     E   |    5

    shop=# select * from "TestA" union select * from "TestB";
     describe | name
    ----------+------
     a        |
     c        |
     B        |    2
     A        |    1
     d        |
     E        |    5
     aa       |
     b        |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

事务

SQL 标准规定当一条 SQL 语句被执行，就隐式地开始了一个事务，commit work 和 rollback work 之一会结束一个事务

begin transaction;
rollback; rollback work;
commit; commit work;
end transaction;

通常数据库是自动提交事务的，但 执行 begin 后会关闭自动提交:
begin transaction;
insert into atable values (1,2,3);
commit work;
end transaction;
1
2
3
4
5
6
7
8
9
10

COMMIT：事务提交。该操作表示事务成功地结束，它将通知事务管理器该事务的所有更新操作现在可以被提交或永久地保留。

ROLLBACK：事务回滚。该操作表示事务非成功地结束，它将通知事务管理器出故障了，数据库可能处于不一致状态，该事务的所有更新操作必须回滚或撤销。

事务的隔离级别

1. read uncommitted (读未提交): 最低级别，任何情况都无法保证
2. read committed (读已提交): 可避免读脏数据
3. repeatable read (可重复读): 可避免读脏数据，不可重复读
4. serializable (串行化): 最高级别，可避免读脏数据、不可重复读、幻读
   • 幻读：事务 A 查询得到 N 条数据，然后事务 B 又插入了 M 条数据，或者改变了这 N 条数据之外的 M 条符合事务 A 搜索条件的数据，导致事务 A 再次搜索发现有 N+M 条数据了，这就产生了幻读，与不可重复读的区别是，不可重复读指的是同一组数据在不同时刻读出不同的值，幻读是指不同时刻能读出不同数量的数据。

示例

set transaction isolation level repeatable read; -- 适用于 pgsql 和 m

show transaction_isolation; -- postgresql 查看当前的隔离级别(默认为 read committed)
select @@transaction_isolation; -- mysql 查看当前的隔离级别(默认为 REPEATABLE-READ)
1
2
3
4

视图

视图实际上就是一个虚拟表，是一个通过 select 查询出来的数据表。所以视图有以下特点：

• 可以使用表的地方，通常也可以使用视图
• 视图既是虚拟的，通常情况下是只读的，不过如果视图中的数据行确实与真实表中的数据行对应，则也是可写的

创建视图

create view 视图名 [(列名, 列名, ...)] as select ... [with check option];
1

• 视图中的列名不必与 select 子句中的列名相同，它们只是在位置上形成对应关系

• 应当避免在视图的基础上创建视图

• 定义视图时，select 子句中不能使用 order by 子句

• with check option 表示对 update、insert、delete 操作时保证更新、插入或删除的行满足视图定义中的谓词条件（即子查询中的条件表达式）

  如果一个视图用于显示性别为男的员工，当通过这个视图来插入数据时，默认可以插入性别为女的员工，如果要求通过视图插入数据时必须符合谓词条件（性别为男），可以加上 with check option 子句。

• 如果定义视图的 SELECT 语句能够满足下面这些条件，那么这个视图就可以被更新:

  • SELECT 子句中未使用 DISTINCT
  • FROM 子句中只有一张表
  • 未使用 GROUP BY 子句
  • 未使用 HAVING 子句

删除视图

drop view 视图名;
1

索引

创建索引

create [unique] [cluster] index 索引名 on 表名 (列名 [次序], 列名 [次序], ...);
1

• unique 表明此索引的每一个索引值只对应唯一的数据记录
• cluster 表明要建立的索引是聚簇索引，索引项的顺序是与表中记录的物理顺序一致的索引组织
• 次序可以取 asc 表示升序，desc 表示降序，默认为升序

删除索引

drop index 索引名;
1

存储过程

存储过程(Procedure)是一组为了完成特定功能的 SQL 语句集合，经编译后存储在数据库中，用户通过指定存储过程的名称并给出参数来执行。

存储过程中可以包含逻辑控制语句和数据操纵语句，它可以接受参数、输出参数、返回单个或多个结果集以及返回值。

由于存储过程在创建时即在数据库服务器上进行了编译并存储在数据库中，所以存储过程运行要比单个的 SQL 语句块要快。

create procedure 存储过程名 (in|out|in out 参数 数据类型, in|out|in out 参数 数据类型...)
[as]
begin
  <SQL>
end
1
2
3
4
5

• 参数类型可以是 in、out、in out，表示输入型参数、输出型参数和输入输出型参数
• 参数类型可以不指定，默认为 in
• 参数的数据类型只需要指明类型名即可，不需要指定宽度

权限的授予和收回

-- 授权
grant 权限 on table/database 表名/视图名/数据库名
to 用户1, 用户2, .../public
[with grant option];

-- 收回授权
revoke 权限 on table/database 表名/视图名/数据库名
from 用户1, 用户2, .../public
[restrict | cascade];
1
2
3
4
5
6
7
8
9

• public 表示将权限授予所有人
• with grant option 表示获得了这个权限的用户还可以将权限赋给其他用户
• restrict 表示只收回语句中指定的用户的权限
• cascade 表示除了收回指定用户的权限外，还收回该用户赋予的其他用户的权限

示例

grant all privileges
on table S, P, J
to USER1, USER2;

grant createtab
on database SPJ
to USER1;

revoke update(Sno) on table S from User1; -- 收回对 Sno 列的更新权限
1
2
3
4
5
6
7
8
9

触发器

创建触发器

create trigger <触发器名> [before|after]
[delete|insert|update[of 列名清单]]
on 表名
referencing <临时视图名>
[for each row | for each statement]
when <触发条件>
begin [atomic]
  <触发动作>
end [触发器名]
1
2
3
4
5
6
7
8
9

• 触发器运行过程中，系统会生成两个临时视图，分别存放更新前和更新后的值，对于行级触发器，为 OLD ROW 和 NEW ROW，对于语句级触发器，为 OLD TABLE 和 NEW TABLE。

  可以使用 referencing new row as nrow, old row as orow 来为这些视图重新命名

• FOR EACH ROW：表示为行级触发器，对每一个被影响的元组（即每一行）执行一次触发过程。

• FOR EACH STATEMENT：表示为语句级触发器，对整个事件只执行一次触发过程，为默认方式。

  如果执行一条语句时更新了多行，那么在 FOR EACH STATEMENT 方式下，也只是触发一次

• atomic 表示触发动作的操作是原子性的，要么都是做，要么都不做

示例

create trigger 触发器名 after update on Tab
referencing new row as nrow, old row as orow
for each row
when nrow.level <= 0
begin atomic
  <触发动作>
end
1
2
3
4
5
6
7

更新触发器

alter trigger 触发器名 [before|after]
delete|insert|update [of 列名]
on 表名|视图名
as
begin
  ...
end
1
2
3
4
5
6
7

删除触发器

drop trigger 触发器名;
1

游标

-- 定义游标，用于一条一条地从查询结果中获取记录
declare <游标名> cursor for
  <select 语句>

-- 打开游标，执行游标定义中的 Select 语句，并将游标指针指向查询结果的第一行之前
open <游标名>

-- 推进游标
fetch from <游标名> into <变量表>

-- 关闭游标
close <游标名>
1
2
3
4
5
6
7
8
9
10
11
12