结构化查询语言之 SQL DDL(以Mysql为例)

@[TOC]

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