数据表的基本操作---创建表、修改表、删除表、查看表

一、创建数据表

1.创建表的基本格式

create table  <表名> (
	     字段名1 数据类型 约束条件，
	     字段名2 数据类型 约束条件，
	     字段名3 数据类型 约束条件，
	     ......
	     约束条件
);	

格式一：定义字段时设置主键

• create 为创建数据表的关键字
• 不同字段之间使用逗号(,)进行分隔
• 语句的最后以分好(;)结束

  2.设置约束条件

  （1）设置主键约束

  主键也称为主码，用于唯一标识该条记录。

• 关键字为：primary key
• 一张表只能有一个主键
• 主键值不能为空

格式一：定义字段时设置主键

字段名 数据类型 `primary key

格式二：定义所有字段后设置主键

primary key(字段名)

（2）设置自增约束

如果用户希望某个字段能够按照顺序自动生成编号，可以为该字段设置自增约束。

• 关键字为：auto_increment
• 一张表只能设置一个字段为自增约束，并且该字段必须为主键
• 默认的初始值为1，每增加一条记录，字段值自动增加1
• 字段类型必须为整数类型

语法格式

字段名 数据类型 auto_increment

（3）设置非空约束

• 关键字为not null
• 作用是：规定字段值不能为空
• 用户在向数据表中插入数据时，如果设置非空约束的字段没有指定值，系统就会报错

语法格式

（4）设置唯一性约束

当数据表中某个字段的值不允许重复时，可以使用唯一性约束。

• 关键字为：unique
  -设置了唯一性约束的字段，插入的数据与数据表中已存在的数据相同时，系统会报错

格式一：定义字段时设置唯一性约束

字段名 数据类型 unique

（5）设置无符号约束

• 关键字：unsigned
• 作用：规定该字段所存储的数据不为负数

格式

字段名 数据类型 unsigned

（6）设置默认约束

• 关键字：default
• 对于设置了默认约束的字段插入记录时，如果没有为该字段赋值，系统会自动将默认值插入到此字段中。
• 没有设置默认约束的字段，系统会自读设置默认值为null

格式

字段名 数据类型 default 值

（7）设置外键约束

设置外键约束的作用是可以将两张表关联在一起。

格式

constraint 约束名 foreign key(字段名) references 主表名(主表中的字段名)

8）设置表的存储引擎

格式

3.实训案例

任务一：根据下表的数据信息创建数据表goods

格式一：

字段	数据类型	约束
id	int(11)	主键、自增
type	varchar(30)	非空
name	varchar（30）	唯一
price	decimal（7,2）	无符号
num	int（11）	默认值为0
add_time	datetime

格式一：
sql语句如下：

create table goods (                                                              |
|       |   id int(11) primary key auto_increment,                                                 |
|       |   type varchar(30) not null,                                                      |
|       |   name varchar(30) unique,                                                  |
|       |   price decimal(7,2) unsigned,                                                |
|       |   num int default 0,                                                            |
|       |   add_time datetime,                                                 |
|       | );

格式二：
sql语句如下：

create table goods (                                                              |
|       |   id int(11) auto_increment,                                                 |
|       |   type varchar(30) not null,                                                      |
|       |   name varchar(30),                                                  |
|       |   price decimal(7,2) unsigned,                                                |
|       |   num int default 0,                                                            |
|       |   add_time datetime,                                                 |
|       |   PRIMARY KEY (`id`),                                                               |
|       |   UNIQUE KEY `name` (`name`)                                                        |
|       | );

任务二、根据下表的数据信息创建数据表orders

字段	数据类型	约束
o_id	int(11)	主键
add_time	datetime
goods_id	int(11)	外键

sql语句如下：

create table orders(
            o_id int(11) primary key,
            add_time datetime,
            goods_id int(11),
            constraint goo_ord foreign key(goods_id) references goods(id)
);

任务三、根据下表的数据信息创建数据

字段	数据类型	约束
id	int(11)	主键
name	varchar(30)
p_id	int(11)

sql语句如下：

create table category(
             id int(11) primary key,
             name varchar(30),
             p_id int(11)
);

任务四、根据下表的数据信息创建数据表comment

字段	数据类型	约束
id	int(11)	主键 自增
goods_id	int(11)	非空 无符号
user_id	int(11)	非空 无符号
content	text	默认
add_time	datatime

sql语句如下：

create table comment(
           id int(11) primary key auto_increment,
           goods_id int(11) unsigned not null,
           user_id int(11) unsigned not null,
           content text,
           add_time datetime
 );

任务五、根据下表的数据信息创建数据表reply

字段	数据类型	约束
id	int(11)	主键、自增
comment_id	int(11)	非空 无符号
user_id	int(11)	非空 无符号
r_content	text
add_time	datetime

sql语句如下：

create table reply(
              id int(11) primary key auto_increment,
              comment_id int(11) unsigned not null,
              user_id int(11) unsigned not null,
              r_content text,
              add_time datetime
);

二、查看表结构

1.查看表基本结构

查看表基本结构的关键字为describe，语法格式如下：

• Field:表示字段名称。
• Type：表示数据类型。
• Null：表示是否可以存储空值。
• Key：表示是否创建索引，PRI表示主键索引，UNI表示唯一索引。
• Default：表示默认值。
• Extra：表示与字段有关的附加信息。
  提示
  一般情况下，describe可以简写为desc

 2.查看建表语句

show create table 表名;

三、修改表

1.修改表名

语法格式：

alter table 旧表名 rename 新表名;

【案例】修改goods表的名称为tb_goods

sql语句如下：

 alter table goods rename tb_goods;

2.修改字段数据类型

语法格式：

alter table 表名 modify 字段名 新数据类型;

案例：将tb_goods表中的type字段的数据类型修改为char（30）

sql语句如下：

alter table tb_goods modify type char(30);

注意
修改数据类型会到质表中不符合数据类型的数据被清空，所有表中已有数据时，需要谨慎操作。

3.修改字段名

语法格式：

alter table 表名 change 旧字段名 新字段名 数据类型;

案例：将tb_goods表中的name字段的数据类型修改为g_name

sql语句如下：

alter table tb_goods change name g_name varchar(30);

提示
使用上述语句也可以同时修改数据类型。例如，将g_name字段名称改为name，数据类型为char(30),sql语句如下：

alter table tb_goods change g_name name varchar(30);

4.添加字段

(1)在表的最后一列添加字段

语法格式：

alter table 表名 add 字段名 数据类型;

【案例】在tb_goods表最后一列添加picture字段,数据类型为varchar(255)

alter table tb_goods add picture varchar(255);

(2)在表的第一列添加字段

alter table 表名 add 字段名 数据类型 first;

【案例】在tb_goods表中第一列添加state字段，数据类型为tinyint(4)

alter table tb_goods add state tinyint(4) first;

(3)在表的指定列之后添加字段

alter table 表名 add 字段名 数据类型 after 字段名2

【案例】在tb_goods表中num字段之后添加intro字段,数据类型为text

alter table tb_goods add intro text after num;

5.删除字段

语法格式：

alter table 表名 drop 字段名;

【案例】将tb_goods表中的picture字段删除
sql语句如下：

alter table tb_goods drop picture;

6.修改字段顺序

语法格式

alter table 表名 modify 字段1名 数据类型 first|after 字段2名;

• first:表示调整该字段到表的第一列。
• after：表示调整该字段到表的某一列后面。

【案例】将tb_goods表的state字段位置修改为id字段之后

sql语句如下：

alter table tb_goods modify state tinyint(4) after id;

7.修改存储引擎

语法格式

alter table 表名 engine=新存储引擎名;

【案例】修改category表的存储引擎为InnoDB

alter table category engine=InnoDB;

8.修改约束条件

当表创建完成后，需要添加或删除约束条件，可以按照如下操作进行：

（1）主键约束的添加和删除

语法格式：

添加：alter table 表名 add primary key(字段名);
删除：alter table 表名 drop primary key;

（2）唯一性约束的添加和删除

语法格式：

添加：alter table 表名 add unique key(字段名);
删除：alter table 表名 drop key 字段名;

（3）自增约束的添加和删除

语法格式：

添加：alter table 表名 modify 字段名 数据类型 auto_increment;
删除：alter table 表名 modify 字段名 数据类型;

【案例】删除tb_goods表的自增约束，然后再添加。

（4）默认值约束的添加和删除

语法格式：

添加：alter table 表名 modfiy 字段名 数据类型 default 值;
删除：alter table 表名 modify 字段名 数据类型 default null;

【案例】删除tb_goods表的默认值约束，然后再添加。

（5）非空约束的添加和删除

【案例】删除tb_goods表的非空约束，然后再添加。

添加：alter table 表名 modfiy 字段名 数据类型 not null;
删除：alter table 表名 modify 字段名 数据类型 null;

（6）无符号约束的添加和删除

添加：alter table 表名 modfiy 字段名 数据类型 unsigned;
删除：alter table 表名 modify 字段名 数据类型;

（7）外键约束的添加和删除

添加:alter table 表名 add constraint 约束名 foreign key(外键列) references 主键表（主键列）
删除：alter table 表名 drop foreign key 约束名

四、删除表

1.删除没有被关联的表

语法格式：

drop table 表名；
【案例】删除category表

drop table category;

2.删除被其他表关联的主表

（1）从表不保留

删除从表—>删除主表
【案例】删除orders表后删除goods表

2）从表保留

删除从表外键—>删除主表
alter table 表名 drop foreign key 外键约束名；
【案例】保留orders表，删除goods表