sql语法
表与列
创建表
在关系模型中，每个关系是一个数据实体。在sql中可以通过create table命令创建一个基本表来代表一个关系。如
create table 表名 (
列名 类型，
列名 类型，
...
);
删除表
删除表有两种模式，restricted和cascade模式，前者删除表的时候不会删除表的依赖项，例如视图触发器，外键等。后者会同时删除基本表相关的所有依赖项。默认为前者restricted模式。
drop table 表名 [restricted | cascade];
增加列
alter table 表名 add column 列名 类型;
如果基本表中已经存在数据，那么在增加了新的列之后，默认会将这个列中的值指定为NULL。
删除列
alter table 表名 drop column 列名;
修改列类型
alter table 表名 alter column 列名 type 类型;
修改列的类型会导致基本表中的数据同时被强制转换类型，因此需要数据库本身支持转换前的数据类型和转换后的数据类型满足“类型兼容”，这种转换有可能是无法进行的。
数据完整性检查
| 名称 | 方法 | 描述 |
|---|---|---|
| 域完整性 | NULL约束 | 可以制定一列中的值是否可以为NULL |
| CHECK约束 | 用来检查输入的值是否满足某一约束条件 | |
| DEFAULT约束 | 如果输入数据中没有制定该列具体的值，可以直接使用default约束指定的值 | |
| 实体完整性 | 主键 | 指定的键值组合在集合内只能有唯一的值（不可以包含NULL值） |
| UNIQUE约束 | 指定的键值组合在集合内只能有唯一的值（可以包含NULL值） | |
| 参照完整性 | 外键 | 指定的键值组合和外部的键值相对应 |
定义约束
例子：
create table students (
id integer primary key,
name varchar(10) NOT NULL,
city varchar(20) check(length(city) <> 0),
country varchar(20) default 'CN'
);
如果向students表中写入不符合约束的值，那么语句执行将会报错。
除了在列定义之后指定完整性约束外，还可以使用表级的完整性约束来指定。NULL约束只能在列定义上指定。
create table students (
id integer,
name varchar(10) NOT NULL,
city varchar(20),
country varchar(20) default 'CN',
constraint id primary key,
constraint city check(length(city) <> 0)
);
主键与外键
什么是主键与外键
-
关系型数据库中的一条记录中有若干个属性，若其中某一个属性组(注意是组)能唯一标识一条记录，该属性组就可以成为一个主键。
-
当一个表中的某一列或多个列签好引用的是另一个表的主键（或具有唯一性）时，可以考虑将其定义为外键，外键表示两个表之间相互的关联关系，包含主键的表通常可以成为主表，而包含外键的表通常称之为从表。
主键和外键的作用
为了维护关系数据库的完整性
-
主键是能确定一条记录的唯一标识，比如，一条记录包括身份正号，姓名，年龄。身份证号是唯一能确定你这个人的，其他都可能有重复，所以，身份证号是主键。
-
外键用于与另一张表的关联。是能确定另一张表记录的字段，用于保持数据的一致性。比如，A表中的一个字段，是B表的主键，那他就可以是A表的外键。
起约束作用
外键取值规则：空值或参照的主键值。
- 插入非空值时，如果主键表中没有这个值，则不能插入。
- 更新时，不能改为主键表中没有的值。
- 删除主键表记录时，你可以在建外键时选定外键记录一起级联删除还是拒绝删除。
- 更新主键记录时，同样有级联更新和拒绝执行的选择。
主键、外键和索引的区别
| 区别 | 主键 | 外键 | 索引 |
|---|---|---|---|
| 定义 | 唯一标识一条记录，不能有重复的，不允许为空 | 表的外键是另一表的主键, 外键可以有重复的, 可以是空值 | 该字段没有重复值，但可以有一个空值 |
| 作用 | 用来保证数据完整性 | 用来和其他表建立联系用的 | 是提高查询排序的速度 |
| 个数 | 主键只能有一个 | 一个表可以有多个外键 | 一个表可以有多个唯一索引 |
修改约束
alter table 表名 add primary key(列名); # 增加主键列
alter table 表名 add check(条件); # 增加check约束
alter table 表名 add foreign key(列名) references 表名(列名); # 增加外键引用
alter table 表名 add unique(列名, 列名, ...); # 增加唯一列
插入、删除、更新数据
insert into 表名 values(val1, val2, val3); # 插入
delete from 表名 where 条件; # 删除
update 表名 set 列名=val where 条件; # 更新
简单查询
select xx, xx, xx from 表名或子查询 where 条件;
连接操作
select xx, xx, xx from 表名1 left join 表名2 on 条件; # 左连接
select xx, xx, xx from 表名1 right join 表名2 on 条件; # 右连接
select xx, xx, xx from 表名1 inner join 表名2 on 条件; # 等值连接
select xx, xx, xx from 表名1 full join 表名2 on 条件; # 等值全连接
集合操作
| 名称 | 描述 |
|---|---|
| UNION | 并操作，将union关键字两端的结果做并集操作 |
| EXCEPT | 差操作，从左边的结果中删除掉右侧的结果集 |
| INTERSECT | 交集 |
三个操作其实都隐含了distinct操作。如果不需要去重可以通过制定all关键字实现。
select * from t1 union select * from t2; # 并集
select * from t1 except select * from t2; # 差集
select * from t1 intersect select * from t2; # 交集
聚集与分组操作
索引
为了提升数据的查询性能，可以为基本表创建索引。索引实际上是对基本表中的一列或多列数据进行预处理，例如B树索引是对数据进行排序之后，按照顺序创建基于磁盘的B树，从而提高访问效率。常见的索引有B树索引、hash索引、位图索引等。
create index 索引名 on 表名(列, 列, ...);
可以通过unique关键字来指定创建的索引是否具有唯一性。unique关键字制定的唯一性和主键的唯一性有一些不同，主键中的所有列不能有NULL值，而unique关键字创建的唯一索引可以允许有NULL值，由于NULL值在SQL中代表的是不确定的值，无法做等值比较，所以unique所以唯一性表现在可以具有NULL值，而且可以有多组NULL值。
参考：唯一索引和非唯一索引
视图与物化视图
模式
一个数据库通常分成外模式、模式和内模式三种模式：
- 外模式：也叫用户模式，使用户所能访问的一组数据视图，和某一应用的逻辑结构有关，是从模式中导出的一个子集，针对某一具体应用控制访问的可见性。
- 模式：数据库内所包含的逻辑结构，包括基本表的定义等。
- 内模式：数据库内部数据的存储方式，包括数据是否加密、压缩等。
视图
数据库中的视图属于数据库的外模式，可以在不暴露整个数据库逻辑模型的基础上，让用户访问所需数据。
例子：创建一个与warehouse表相关的视图，只能显示仓库的名称。
create view warehouse_name as select name from warehouse;
视图本身是一个“虚表”，是由模式映射出来的一种外模式，本身不保存数据，因此当基本表的数据发生变化时，视图中的数据也会同时发生改变。
物化视图
除了普通的视图之外，还有一种物化视图。物化视图本身是保存数据的，它和普通数据的区别在DML操作中，对普通视图的操作会映射到基本表，而对物化视图的操作则直接作用到物化视图本身。
例子：创建一个warehouse name相关的物化视图。
create material view warehouse_name as select name from warehouse;
访问控制
grant select on table warehouse to u1; # 将warehouse表的select权限授予用户u1。
revoke select on table warehouse from u1; # 将warehouse表的select权限从u1回收。
grant select (id, name) on table warehouse to u1; # 将warehouse表的(id, name)列的select权限授予用户u1。
revoke select (id, name) on table warehouse from u1; # 将warehouse表的(id, name)列的select权限从u1回收。
网友评论