数据表基本操作——创建数据表、查看表结构、修改表、删除表

1.创建数据表

目录

1.创建数据表

1.1创建表的语法格式

案例1：根据所提示数据信息创建数据表goods

1.2使用SQL语句设置约束条件

1.2.1设置主键约束

1.2.2设置自增约束

1.2.3设置非空约束

1.2.4设置唯一性约束

1.2.5设置无符号约束

1.2.6设置默认约束

1.2.7设置外键约束

案例2：根据所提示数据信息创建数据表orders

1.2.8设置表的存储引擎 

 案例3：根据所提示数据信息创建数据表category

 案例4：根据所提示数据信息创建数据表comment

 案例5：根据所提示数据信息创建数据表reply

2.查看表结构

2.1查看表基本结构

  案例1：查看goods表结构 ，执行该语句describe goods最终结果如图所示：

2.2查看建表语句

  案例2：查看goods表的建表语句 ，执行该语句show create table goods \G最终结果如图所示：

3.修改表

3.1修改表名

  案例1：将goods表的名称改成tb_goods，先查看数据库中的数据表，再将goods表的名称修改成tb_goods，最后再次查看数据库中的数据表

3.2修改字段数据类型

案例2：将tb_goods表中的type字段的数据类型修改为char(30),查看tb_goods的基本结构 

3.3修改字段名

案例3：将tb_goods表中name字段的名称改为g_name，查看tb_goods表的基本结构

3.4添加字段

案例4：在tb_goods表中添加picture字段 

案例5：在tb_goods表中添加state字段 

案例6：在tb_goods表中num字段之后添加intro字段

3.5删除字段

案例7：将tb_goods表中的picture字段删除

3.6修改字段顺序

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

3.7修改存储引擎

案例9：修改category表的存储引擎为InnoDB

4.删除表

4.1删除没有被关联的表

案例10：删除category表和不存在的tb_goods表，并查看数据库中的所有表

4.2删除被其他表关联的主表

案例11： 创建的orders表和comment表存在外键关联，如果直接删除comment表，系统会提示错误，需要先删除orders表中的外键，然后删除comment表

实训案例 ：

---

1.1创建表的语法格式

创建数据表，实际上是规定列属性和实现数据完整性约束的过程，基本语法格式如下：

create table table_name(
col_namel data_type[constraints],
col_name2 data_type[constraints],
...
col_namen data_type[constraints]
);

注意：

create为创建数据表的关键字，table_name表示数据表的名称，表的结构在括号中定义，col_name表示字段名，data_type表示数据类型，constraints表示约束条件，各个字段之间使用逗号进行分隔，语句的最后要以分号结束。

数据表表名应遵循以下原则：

• 长度最好不超过30个字符；
• 多个单词之间使用下画线“_”分隔，不允许有空格；
• 不允许为mysql关键字；
• 不允许与同一数据库中的其他数据表同名。

案例1：根据所提示数据信息创建数据表goods

goods表结构

字段	数据类型	约束	注释
id	int(11)	主键、自增	商品编号
type	varchar(30)	非空	商品类别
name	varchar(30)	唯一	商品名称
price	decimal(7,2)	无符号	商品价格
num	int(11)	默认值为0	商品库存
add_time	datetime		商品时间

如果没有创建数据库，先创建数据库，比如我创建的数据库是zxj，执行以下语句创建数据库：

create database zxj；

创建数据表需要选择数据库，比如我上一步创建的数据库是zxj，那么我选择的数据库应该是zxj，执行以下语句选择数据库：

use zxj；

选择数据库之后就可以开始创建我们的goods表，执行以下语句创建数据表：

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(11) default 0,
add_time datetime
);

创建成功后，执行以下SQL语句，可以查看当前数据库中所有的数据表

show tables;

执行以上语句最终结果如图所示：

1.2使用SQL语句设置约束条件

1.2.1设置主键约束

主键，也称主码，用于标识表中唯一的一条记录。一张表中只能有一个主键，并且主键值不能为空。设置主键约束的关键字为primary key，使用SQL语句可以在定义字段时设置主键约束，也可以在定义好表后中所有字段后再设置主键约束。

第一种方法：定义字段时设置主键约束

语法格式：col_name data_type primary key

 在案例1当中，我们创建了数据表goods，我们可以为goods表中的id字段设置主键约束，执行以下语句:

create table goods(
id int(11) primary key,
......
);

注意：是创建表的时候给定义字段设置主键约束。

第二种方法：定义所有字段后设置主键约束

语法格式：primary key(col_name)

 为goods表id字段设置主键约束也可以使用这种方法，执行以下语句:

create table goods(
id int(11),
......
primary key(id)
);

1.2.2设置自增约束

向数据表中插入数据时，希望自动生成并且按顺序排列，可以为该字段设置自增约束。设置自增约束的关键字为auto_increment

语法格式：col_name data_type auto_increment

 在案例1当中，我们创建了数据表goods，我们可以为goods表中的id字段设置自增约束，执行以下语句:

create table goods(
id int(11) primary key auto_increment,
......
);

设置自增约束应注意以下几点：

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

1.2.3设置非空约束

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

语法格式：col_name data_type not null

 在案例1当中，我们创建数据表了goods，我们可以为goods表中的type字段设置非空约束，执行以下语句:

create table goods(
type varchar(30) not null,
......
);

1.2.4设置唯一性约束

当数据表中某个字段的值不允许重复时，可以使用唯一性约束。当用户在设置了唯一性约束的字段中插入的数据与数据表中已存在的数据相同时，系统会报错。设置唯一性约束的关键字为unique,同主键约束一样，唯一性约束有两种设置方法：

第一种方法：定义字段时设置唯一性约束

语法格式：col_name data_type unique

 在案例1当中，我们创建数据表了goods，我们可以为goods表中的name字段设置唯一性约束，执行以下语句: 

create table goods(
name varchar(30) unique,
......
);

第二种方法：定义所有字段后设置唯一约束

语法格式：unique key(col_name)

1.2.5设置无符号约束

 设置无符号约束的关键字为unsigned，作用是规定此列所有所存储的数据不为负数。

语法格式：col_name data_type unsigned;

create tables goods(
price decimal(7,2) unsigned,
......
);

1.2.6设置默认约束

当向数据表中插入一条记录时，如果没有为某个字段赋值，那么系统就会将其值设置为null，如果字段设置默认约束，则系统会自动将默认值插到此字段中。设置默认约束的关键字为default

语法格式：col_name data_type default value

  在案例1当中，我们创建数据表了goods，我们可以为goods表中的num字段设置默认约束，执行以下语句: 

create table goods(
num int(11) default 0,
......
);

1.2.7设置外键约束

设置外键约束的主要作用是保证数据的完整性，也就是说一张数据表中的某个字段可能是另外一张数据表的主键，可以为这个字段设置外键约束。外键约束也可以不是所属数据表的主键，但会对应着另外一张数据表的主键。设置外键约束的关键字为constraint、foreign key、references

语法格式：constraint key_name foreign key (child_col_name) references parent_table_name(parent_col_name)

注意：

key_name表示外键名、child_col_name表示从表中需要设置外键约束的字段名、parent_table_name表示主表名、parent_col_name表示主表中主键的字段名

设置外键约束时应注意以下几点：

• 主表和从表必须使用InnoDB存储引擎。
• 设置外键约束的字段和关联的主键必须具有相同的数据类型。整数类型的大小和符号必须相同，字符串类型的长度可以不相同，但对于非二进制字符串，字符集和排序规则必须相同。

案例2：根据所提示数据信息创建数据表orders

orders表结构

字段	数据类型	约束	注释
o_id	int(11)	主键	订单编号
add_time	datetime		添加时间
goods_id	int(11)	外键	商品编号

选择数据库后，执行以下SQL语句，创建数据表orders

use zxj;
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)
);

 执行以上语句最终结果如图所示：

1.2.8设置表的存储引擎 

在创建表时，如果不使用默认存储引擎，就需要单独设置存储引擎。

 案例3：根据所提示数据信息创建数据表category

category表结构

字段	数据类型	约束	注释
id	int(11)	主键	类别编号
name	varchar(30)		类别名称
p_id	int(11)		父类编号

选择数据库后，执行以下SQL语句，创建数据表category

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

 执行以上语句最终结果如图所示：

 案例4：根据所提示数据信息创建数据表comment

comment表结构

字段	数据类型	约束	注释
id	int(11)	主键、自增	评价编号
goods_id	int(11)	非空、无符号	评价商品
user_id	int(11)	非空、无符号	评价用户
content	text	默认	评价内容
add_time	datetime		添加时间

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
);

 执行以上语句最终结果如图所示：

 案例5：根据所提示数据信息创建数据表reply

reply表结构

字段	数据类型	约束	注释
id	int(11)	主键、自增	回复编号
comment_id	int(11)	非空、无符号	评价编号
user_id	int(11)	非空、无符号	评价用户
r_content	text		回复内容
add_time	datetime		添加时间

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
);

 执行以上语句最终结果如图所示：

2.查看表结构

2.1查看表基本结构

查看表结构的关键字为describe

语法格式：describe table_name；

  案例1：查看goods表结构 ，执行该语句describe goods最终结果如图所示：

注意： 

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

提示：

一般情况下，describe可以简写为desc  

2.2查看建表语句

使用show create table 语句可以查看表的建表语句 

语法格式：show create table table_name \G

  案例2：查看goods表的建表语句 ，执行该语句show create table goods \G最终结果如图所示：

3.修改表

3.1修改表名

修改数据表名称的关键字为rename

语法格式：alter table old_table_name rename new_table_time;

  案例1：将goods表的名称改成tb_goods，先查看数据库中的数据表，再将goods表的名称修改成tb_goods，最后再次查看数据库中的数据表

show tables;
alter table goods rename tb_goods;
show tables;

执行以上语句最终结果如图所示：  

3.2修改字段数据类型

修改字段数据类型的关键字为modify

语法格式：alter table table_name modify col_name new_date_type;

案例2：将tb_goods表中的type字段的数据类型修改为char(30),查看tb_goods的基本结构 

describe tb_goods;
alter table tb_goods modify type char(30);
desc tb_goods;

执行以上语句最终结果如图所示：  

3.3修改字段名

修改数据表字段名称的关键字为change

语法格式：alter table table_name change old_col_name new_col_name data_tyep;

 使用上述语句也可以同时修改数据类型 

案例3：将tb_goods表中name字段的名称改为g_name，查看tb_goods表的基本结构

desc tb_goods;
alter table tb_goods change name g_name varchar(30);
desc tb_goods;

将g_name字段名称修改为name，数据类型修改为char(30)

alter table tb_goods change g_name name char(30);

执行以上语句最终结果如图所示：  

 

3.4添加字段

添加字段的关键字add

第一种方法：在表的最后一列添加字段

语法格式：alter table table_name add col_name data_type;

案例4：在tb_goods表中添加picture字段 

desc tb_goods;
alter table tb_goods add picture varchar(255);
desc tb_goods;

执行以上语句最终结果如图所示：  

第二种方法：在表的第一列添加字段

语法格式： alter table table_name add col_name data_type first;

案例5：在tb_goods表中添加state字段 

desc tb_goods;
alter table tb_goods add state tinyint(4) first;
desc tb_goods;

执行以上语句最终结果如图所示：   

第三种方法：在表的指定列之后添加字段

语法格式：alter table table_name add col_name1 data_type after col_name2;

案例6：在tb_goods表中num字段之后添加intro字段

desc tb_goods;
alter table tb_goods add intro text after num;
desc tb_goods;

执行以上语句最终结果如图所示：   

3.5删除字段

删除数据表字段的关键词为drop

语法格式：alter table table_name drop col_name;

案例7：将tb_goods表中的picture字段删除

desc tb_goods;
alter table tb_goods drop picture;
desc tb_goods;

 执行以上语句最终结果如图所示：

3.6修改字段顺序

修改字段顺序的关键字为modify

语法格式：alter table table_name modify col_name data_type first | after col_name2;

col_name表示修改的字段，data_type表示字段数据类型，first col_name1表示修改为表的第一个字段、after col_name2表示修改为col_name2字段之后

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

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

  执行以上语句最终结果如图所示：

修改某字段为表的第一字段，语法格式：alter table table_name modify col_name data_type first； 

3.7修改存储引擎

用户可以在创建表时设置存储引擎，也可以在表创建之后修改表的存储引擎

语法格式：alter table table_name engine=e_name;

在修改存储引擎之前，往往需要首先查看表当前的存储引擎

语法格式：show creare table table_name \G

案例9：修改category表的存储引擎为InnoDB

show create table category \G
alter table category engine=InnoDB;

  执行以上语句最终结果如图所示： 

   

由查询可知，category表的存储引擎为MyISAM ，修改category表后的存储引擎为InnoDB

4.删除表

4.1删除没有被关联的表

使用drop关键字可以一次性删除一个或多个没有被其他表关联的表

语法格式：

drop table [if exits] table_name1,table_name2,table_name3,......,table_namen; 

案例10：删除category表和不存在的tb_goods表，并查看数据库中的所有表

show tables;
drop table if exists category,tb_goods;
show tables;

   执行以上语句最终结果如图所示： 

4.2删除被其他表关联的主表

语法格式：alter table table_name drop foreigne key key_name; 

案例11： 创建的orders表和comment表存在外键关联，如果直接删除comment表，系统会提示错误，需要先删除orders表中的外键，然后删除comment表

show tables;
drop table if exists comment,tb_goods;
alter table orders drop foreign key goo_ord;
drop table comment;
show tables;

   执行以上语句最终结果如图所示： 

实训案例 ：

根据所提示信息，创建tb_worker表结构，使用合适的存储引擎、数据表类型和字符集，并对表进行各种操作，如添加约束、查看表结构、修改表结构等。

tb_worker表结构

字段	数据类型	约束	注释
id	int(11)	主键、自增	员工编号
name	varchar(30)	非空	姓名
sex	enum('w','m')	默认值‘m’	m为男，w为女
hobby	set('football','baskball','vollyball')		爱好
score	float(3,1)	无符号	绩效评分
mobile	varchar(11)	唯一，非空	手机号
intro	text		简介
enrty_time	date		入职时间

 步骤1：选择数据库

use zxj;

步骤2：根据所提示信息创建数据表tb_worker，并添加各类约束

create table tb_worker(
id int(11) primary key auto_increment,
name varchar(30) not null,
sex enum('w','m') default 'm',
hobby set('football','basketball','volleyball'),
score float(3,1) unsigned,
mobile char(11) unsigned not null,
entry_name date
)engine=MyISAM;

步骤3：查看表结构

desc tb_worker;

 步骤4：将mobile字段的数据类型修改为varchar(11)

 alter table tb_worker modify mobile varchar(11);

步骤5：在mobile字段后添加intro字段

alter table tb_worker add intro text after mobile;

   执行以上语句最终结果如图所示：