SQL数据表操作

创建表

创建表使用create table 语句，基本格式如下：

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(
<列名1> <数据类型> [<列选项>],
<列名2> <数据类型> [<列选项>],
…
<表选项>
)

• temporary ：该关键字表示用create 命令新建的表为临时表。不加该关键字创建的表通常称为永久表。

• 用户可以像操作持久表一样操作临时表。临时表的生命周期较短，而且只能对创建它的用户可见，当断开与该数据库的连接时，MySQL会自动删除它们。

• 列选项：

  • NULL 或 NOT NULL ：表示一列是否允许为空

  • default default_value ：为列指定默认值，必须为一个常量

  • auto_increment ：设置自增属性，只有整型列才能设置此属性。当插入NULL值或0到一个auto_increment 列时，列被设置为value+1，value是此前表中该列的最大值。auto_increment 顺序从1开始，每个表只能有一个auto_increment 列，且它必须被索引。

  • unique key|primary key ：都表示字段中的值是唯一的。primary key 表示设置为主键，主键必须为NOT NULL 。

  • comment 'string': 对于列的描述，string是描述的内容。

  • 表选项：在定义列选项的时候，可以将某列定义为primary key ，但是当主键是由多个列组成的多列索引时，定义列时无法定义此主键，这时就必须在语句最后加上一个primary key(col_name, ...) 子句定义的表选项。另外，表选项中还可以定义索引和外键。

    定义外键：

    foreign key (col_name) references tabel_name(col_name);

修改表结构

1. 增加属性列alter table .. add ...

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

   alter table student_info add hobby text NULL;

2. 删除属性列alter table ... drop ...

   alter table <基本表名> drop <属性列名> [cascade|restrict];

   cascade 表示在删除某属性列时，所有引用到该属性列的视图和约束也要一起自动删除；而restrict 表示在没有视图或约束引用该属性列时，才可删除该列，否则就拒绝删除操作。

   alter table student_info drop hobby;

3. 修改属性列alter table ... modify ...

   alter table <基本表名> modify <属性列名> <类型>;

   alter table student_info modify hobby varchar(50);

4. 补充定义主键alter table ... add primary key (...)

   在SQL中，并不要求每个表都需定义主键，可以在需要情况下随时定义，这称为主键的补充定义。补充定义主键的语句格式为：

   alter table <表名> add primary key (<列名表>)

   需要指出，被定义为主键的属性列应当是非空和满足唯一性要求的。

给一张表添加主键的三种方法

1. 创建表的时候直接在列选项中定义某个属性；

2. 创建表的时候在所有字段最后的表选项中定义一个或多个属性；

3. 在表创建好之后添加主键；alter

给一张表添加外键的四种方法

1. 创建表的时候直接在列选项中定义某个属性；

   references tabel_name(col_name);

2. 创建表的时候在所有字段最后的表选项中定义一个或多个属性；

   create table score
   (cscore int(11),
   st_id int(50),
   cs_id int(30),
   primary key(st_id,cs_id),
   FOREIGN KEY (st_id) REFERENCES student(id),
   FOREIGN KEY (cs_id) REFERENCES classes(id));

3. 添加约束

   create table score
   (cscore int(11),
   st_id int(50),
   cs_id int(30),
   primary key(st_id,cs_id),
   CONSTRAINT `FK_ID_ST` FOREIGN KEY (st_id) REFERENCES student(id),
   CONSTRAINT `FK_ID_CS` FOREIGN KEY (cs_id) REFERENCES classes(id));

4. 在表的定义外进行添加

   alter table score add constraint FK_ID foreign key(st_id) REFERENCES student(id);

   alter table student_info
   add foreign key(Major) references Major_info(MID)

删除表

drop [temporary] table [if exists] tbl_name[, tbl_name]...