3.3数据定义-3.3.2 数据库表的创建（定义）、数据库表的删除与数据库表的修改——MySQL

MYSQL系列文章目录

3.2 学生-课程数据库
3.3.1 模式的定义（创建）与删除

3.3.3 索引的建立与删除
3.4 数据查询
3.5 数据更新

---

---

3.3.2 数据库表的定义（创建）、删除与修改

1.定义基本表（创建表）

创建了一个模式就建立了一个数据库的命名空间，一个框架。

create table <表名> (<列名><数据类型>[列级完整性约束条件]
				    [,<列名><数据类型>[列级完整性约束条件]]
					…
					[,<表级完整性约束条件>]);

如果完整性约束条件涉及该表的多个属性列，则必须定义在表级上，否则既可以定义在列级也可以定义在表级。
[例3.5] 建立一个“学生”表Student（列级约束条件，主键（主码），取唯一值）
代码如下（示例）：

/*创建一个Student表*/
create TABLE Student (Sno CHAR(9) PRIMARY KEY,/*列级完整性约束条件，Sno是主码*/
					Sname CHAR(20) UNIQUE, /*Sname取唯一值*/
					Ssex enum('男','女') not null default '女',/*默认为'女',不能为空*/
					Sage SMALLINT,
					Sdept CHAR(20)
					);

/*创建一个Student表*/
create TABLE Student (Sno CHAR(9),
					Sname CHAR(20) UNIQUE, /*Sname取唯一值*/
					Ssex enum('男','女') not null default '女',/*默认为'女',不能为空*/
					Sage SMALLINT,
					Sdept CHAR(20),
					constraint sno PRIMARY KEY(Sno)/*表级完整性约束条件，Sno是主码，sno是主码的名称*/
					/*constraint sno 可以省略*/
					);

[例3.6] 建立一个“课程”表Course（列级约束条件，不能为空值，外键约束条件）
代码如下（示例）：

/*创建一个Course表*/
create TABLE Course (Cno CHAR(4) PRIMARY KEY,/*列级完整性约束条件，Cno是主码*/
					Cname CHAR(40) NOT NULL, /*列级完整性约束条件，Cname不能取空值*/
					Cpno CHAR(4),/*Cpno的含义是先修课*/
					Ccredit SMALLINT,
					FOREIGN KEY (Cpno) REFERENCES Course(Cno)
					/*表级完整性约束条件，Cpno是外码，被参照表是Course，被参照列是Cno*/
					);

本例说明参照表和被参照表可以是同一个表。
[例3.7] 建立学生选课表SC（表级约束条件，外键约束条件，外键命名）
代码如下（示例）：

/*创建一个SC表*/
create TABLE SC (Sno CHAR(9),
					Cno CHAR(4),
					Grade SMALLINT,
					PRIMARY KEY (Sno,Cno),/*主码由两个属性构成，必须作为表级完整性进行定义*/
					constraint sc_fk_1 FOREIGN KEY (Sno) REFERENCES Student(Sno),
					/*表级完整性约束条件，sc_fk_1是外键名(建议写为:Sno)，Sno是外码，被参照表是Student，被参照列是Sno*/
					constraint sc_fk_2 FOREIGN KEY (Cno) REFERENCES Course(Cno)
					/*表级完整性约束条件，sc_fk_2是外键名(建议写为:Cno)，Cno是外码，被参照表是Course，被参照列是Cno*/
					);

创建外键约束条件并赋名字（名称）

• 本页面目录中有关于外键的约束条件的相关内容介绍跳转。
  代码如下（示例）：

/*创建一个SC表*/
create TABLE SC (Sno CHAR(9),
					Cno CHAR(4),
					Grade SMALLINT,
					PRIMARY KEY (Sno,Cno),/*主码由两个属性构成，必须作为表级完整性进行定义*/
					/*constraint sc_fk_1*/ FOREIGN KEY (Sno) REFERENCES Student(Sno),
					/*表级完整性约束条件，sc_fk_1是外键名（可以省略，有默认值），Sno是外码，被参照表是Student，被参照列是Sno*/
					/*constraint sc_fk_2*/ FOREIGN KEY (Cno) REFERENCES Course(Cno)
					/*表级完整性约束条件，sc_fk_2是外键名（可以省略，有默认值），Cno是外码，被参照表是Course，被参照列是Cno*/
					);

2.数据类型

数据类型	含义
CHAR(n),CHARACTER(n)	长度为n的定长字符串。（不能进行算术运算）
VARCHAR(n)	最大长度为n的变长字符串
CHARACTERVARCHAR(n)	最大长度为n的变长字符串
CLOB	字符串大对象
BLOB	二进制大对象
INT,INTEGER	长整数（4字节）
SMALLINT	短整数（2字节）
BIGINT	大整数（8字节）
NUMERIC(p,d)	定点数，有p位数字（不包含符号、小数点）组成，小数点后面有d位数字
DECIMAL(p,d),DEC(p,d)	同NUMERIC
BOOLEAN	逻辑布尔量
DATE	日期，包含年、月、日，格式为YYYY-MM-DD
TIME	时间，包含一日的时、分、秒，格式为HH:MM:SS
TIMESTAMP	时间戳类型
…	…

3.模式与表

4.修改基本表（修改表）

0. 修改数据表的名字（名称）

alter table <旧表名> rename to <新表名>;

alter table student rename to students;

1.添加新列、新的列级完整性约束条件

alter table <表名>
[add [column] <新列名><数据类型>[完整性约束条件]];

[例3.8] 向Student表增加“入学时间"列，其数据类型为日期型。

alter table student add s_entrance DATE;

不论基本表中原来是否已有数据，新增加的列一律为空值。

2.添加新的表级完整性约束条件

alter table <表名>
[add <表级完整性约束条件>];

[例3.10] 增加课程名称必须取唯一值的约束条件。增加学生名字必须取唯一值的约束条件。

alter table course add unique(Cname);
alter table student add unique(Sname);

alter table <表名> add [constraint 约束名] unique(<列名>);

alter table student add constraint sname unique(Sname);

3.drop 删除表中的列，如果指定了CASCADE短语，则自动删除引用了该列的其他对象，比如视图；
如果指定了RESTRICT短语，则如果该列被其他对象引用，RDBMS将拒绝删除该列。

alter table <表名>
[drop [column] <列名> [cascade | restrict]];

alter table student drop s_entrance;

4.drop constraint 删除指定的完整性约束条件。

alter table <表名>
[drop constraint <完整约束名> [cascade | restrict]];

alter table student drop constraint sname;

注意：这里是约束名

5.alter column 修改原有的列定义，包括修改列名和数据类型。

[例3.9] 将年龄的数据类型由字符型（假设原来的数据类型是字符型）改为整数。

alter table <表名>
[modify <列名><数据类型>];

alter table student modify Sage int;

alter table <表名>
[alter column <列名><数据类型>];
这个会报错。

alter table student alter COLUMN sage int;/*这个会报错*/

例3.8和3.9合并：添加一条新的列属性，修改某列的数据类型

alter table student add s_entrance DATE;/*在student表中，添加一个列，数据类型为时间*/
alter table student modify Sage smallint;/*在student表中，将Sage的数据库类型修改为smallint*/
desc student;/*显示student表的数据信息*/

5.删除基本表（删除表）

drop table <表名> [restrict | cascade];
cascade | restrict 必须任选其一
cascade（级联删除） 无论如何全部删除
restrict（限制删除） 如果子文件夹有文件就不能执行删除
默认情况为restrict

drop table Student cascade;
drop table Student;

6.清除表中的全部数据

delete from <表名>

delete from student;

添加外键约束条件和删除外键约束条件

如何获取外键约束条件的名称（名字）

select * from information_schema.referential_constraints;/*获取外键约束信息*/

select * from information_schema.key_column_usage where constraint_schema='school';
select * from information_schema.key_column_usage where constraint_schema='<数据库名称>';/*必须加''*/
/*可以获取指定数据库中的所有键约束的列信息，包括主键约束中的主键列，唯一约束中的唯一键列和外键约束中的引用列*/

删除外键约束条件

alter table sc drop constraint sc_fk_2;/*删除sc表中的Cno外键约束条件*/
alter table sc drop foreign key sc_fk_2;/*删除sc表中的Cno外键约束条件*/

添加外键约束条件

alter table <表名> add [constraint 约束名] foreign key(<列名>) references <表名> (<列名>,…);

alter table sc add foreign key (Cno) references course (Cno);/*添加sc表中的Cno外键约束条件*/
alter table sc add constraint sc_fk_2 foreign key (Cno) references course (Cno);/*添加sc表中的Cno外键约束条件*/

constraint sc_fk_2 添加外键约束条件的名称
（外键的名称可以自定义；如果不自定义(默认)外键的名称，则外键的名称有默认值(不是列名)）
如果外键的名称默认（不自定义），则索引的名称默认值为列名；
如果外键的名称自定义，则索引名称不为默认值，为外键自定义的名称；

添加主键约束条件和删除主键约束条件

对已有列添加主键约束条件

alter table <表名> add [constraint 约束名] primary key(<列名>,…);
中括号里面的内容可能会报错。

alter table student add constraint sno primary key (Sno);
/*没有添加上约束名，主键添加上了*/

alter table <表名>
[drop constraint <约束名>;

alter table student drop constraint sno;
/*会报错*/

关于自增的添加和删除（自增的字段需为主键且不为CHAR类型）

0. 自增，主键，添加列

alter table student add sno int auto_increment primary key first;/*添加Sno列名,自增，主键*/

1. 修改Sno列名并添加自增（此时的Sno是主键）

alter table student modify Sno int auto_increment;/*修改Sno列名，添加为自增*/

2. 修改Sno列名并删除自增（本质是修改了数据表的结构，没有使用删除功能）

alter table student modify Sno int;/*修改Sno列名，删除自增*/

3.在创建表时，让int类型的主键自增

/*创建一个Student表*/
create TABLE Student (Sno int auto_increment PRIMARY KEY,/*列级完整性约束条件，Sno自增，Sno是主码*/
					Sname CHAR(20) UNIQUE, /*Sname取唯一值*/
					Ssex enum('男','女') not null default '女',/*默认为'女',不能为空*/
					Sage SMALLINT,
					Sdept CHAR(20)
					);