Mysql数据表基本操作

一、 创建表

1. 创建表的格式

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

2.设置约束条件

（1）设置主键约束

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

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

格式一：字段名 数据类型 primary key
格式二：primary key(字段名）

建表完成后:

添加：alter table table_name add primary key(字段)
删除：alter table table_name drop primary key

【案例】删除tb_goods表的主键约束，然后再添加。**

（2）设置自增约束

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

格式：字段名 数据类型 auto_incerment

//建表完成后
添加：alter table table_name modify 列名  int auto_increment
删除：alter table table_name modify 列名  int

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

（3）设置非空约束

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

格式：字段名 数据类型 notnull

//建表完成后
添加：alter table table_name modify 列名 数据类型 not null
删除：alter table table_name modify 列名 数据类型 null

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

（4）设置唯一约束

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

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

格式一：字段名 数据类型 unique

建表完成后：

添加：alter table table_name add unique 约束名(字段)
删除：alter table table_name drop key 约束名

（5）设置无符号约束

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

格式：字段名 数据类型 unsigned

（6）设置默认约束

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

格式：字段名 数据类型 default 值

建表完成后：

添加：alter table table_name alter 列名 set default'值'
删除：alter table table_name alter 列名 drop default

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

（7）设置外键约束

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

• 关键字：constraint foreign key references

格式：constraint外键约束 foreign key(外键列) references 主键表(主键列)

建表完成后：

添加：alter table table_name add constraint约束名 foreign key(外键列) references 主键表(主键列)
删除：
	[1]第一步：删除外键
		alter table table_name drop foreign key约束名
	[2]第二步：删除索引
		alter table table_name drop index 索引名

（8）设置表的存储引擎

代码格式

engine=存储引擎名

3、任务实例

（1）创建goods表：

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

操作步骤：

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

（2）创建orders表：

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

操作步骤：

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

（3）创建category表：

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

操作步骤：

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

（4）创建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表：

字段	数据类型	约束	注释
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
			  );

二、查看表结构

1.查看表基本结构

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

describe 表名;

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

2.查看建表语句

show create table 表名;

三 、修改表的结构

1、修改表名

语法格式：

alter table 旧表名 rename 新表名;

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

2、修改字段数据类型

语法格式：

alter table 操作的表明 modify 被修改的字段名 修改后的数据类型;

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

3、修改字段名

语法格式：

alter table 操作的表明 change 旧字段名 新字段名 数据类型;

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

4、添加字段

（1）在表的最后一列

alter table 操作的表明 add 添加的字段名 数据类型;

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

（2）在表的第一列

alter table 操作的表明 add 添加的字段名 数据类型 first;

【案例】在tb_goods表中第一列添加state字段，数据类型为tinyint(4)
（3）在指定列之后

alter table 操作的表明 add 添加的字段名 数据类型 after(列名);

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

5、删除字段

语法格式：

alter table 表名 drop 字段名;

【案例】将tb_goods表中的picture字段删除

6、修改字段顺序

语法格式：

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

• first:表示调整该字段到表的第一列。
• after：表示调整该字段到表的某一列后面。
  【案例】将tb_goods表的state字段位置修改为id字段之后
  

7、修改存储引擎

语法格式：

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

四、删除表

1.删除没有被关联的表

语法格式：

drop table 表名；

【案例】删除category表

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

（1）从表不保留

删除从表—>删除主表

【案例】删除orders表后删除goods表

（2）从表保留

删除从表外键—>删除主表

语法格式：

alter table 表名 drop foreign key 外键约束名