1. 创建表
1.1 创建表通用形式
A_i为属性名,D_i为属性A_i的域(指定了A_i的类型与可选的约束,用于限制允许的A_i的取值集合)
create table table_name(
A_1 D_1 not null,
A_2 D_2,
...,
A_n D_n,
<完整性约束_1>,
...,
<完整性约束_1>
);
1.2 创建表的一些例子
create table classroom
(building varchar(15),
room_number varchar(7),
capacity numeric(4,0),
primary key (building, room_number)
);
create table department
(dept_name varchar(20),
building varchar(15),
budget numeric(12,2) check (budget > 0),
primary key (dept_name)
);
create table instructor
(ID varchar(5),
name varchar(20) not null,
dept_name varchar(20),
salary numeric(8,2) check (salary > 29000),
primary key (ID),
foreign key (dept_name) references department(dept_name)
on delete set null
);
1.3 创建表的扩展
- 创建与现有某个表模式相同的表(不带数据):create table table_name_2 like table_name_1
create table temp_instructor like instructor;
-
把查询的结果存储成一个新表(带数据)
create table music_instructor as (select * from instructor where dept_name = "Music" )
2. 删除表
- 格式:drop table table_name
- delete from table_name 仅删除表数据不删除表模式,而drop table table_name 删除表数据且删除表模式
3. 修改表
- 格式:alter table table_name (others)
- 添加属性
- 删除属性
References:
[1] Abraham Silberschatz, Henry F Korth, S Sudarshan. Database System Concepts. New York: McGraw-Hill, 2010
Database System Concepts