数据表的基本操作

一、创建数据表

1、创建表的语法格式

create table 表名(

字段名1 数据类型 约束条件,

字段名2 数据类型 约束条件,

......

);

2、实例

创建goods表

goods表结构

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

步骤一：创建数据库db-shop

create database db_shop;

步骤二：选择数据库db_shop

use db_shop;

步骤三：创建数据表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
);

步骤四：查看当前数据库中所有的数据表

show tables;

2、使用SQL语句设置约束条件

(1) 设置主键约束

语法格式1：

字段名 数据类型 primary key;

实例：创建的数据表goods中的id字段设置主键约束

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

语法格式2：

primary key(字段名);

实例：创建的数据表goods中的id字段设置主键约束

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

(2) 设置自增约束

语法格式：

字段名 数据类型 auto_increment;

实例：创建的数据表goods中的id字段设置了自增约束

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

(3) 设置非空约束

语法格式：

字段名 数据类型 not null;

实例：创建的数据表goods中的type字段设置了非空约束

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

(4) 设置唯一性约束

语法格式：

字段名 数据类型 unique;

实例：创建的数据表goods中的name字段设置了唯一性约束

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

(5) 设置无符号约束

语法格式：

字段名 数据类型 unsigned;

实例：创建的数据表goods中的price字段设置了无符号约束

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

(6) 设置默认约束

语法格式：

字段名 数据类型 default 值;

实例：创建的数据表goods中num字段设置了默认约束

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

(7) 设置外键约束

语法格式：

constraint 外键名 foreign key(从表中需要设置外键约束的字段名) references 主表名 (主表中主键的字段名);

实例：创建从表orders，为goods_id字段设置外键约束

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

(8) 设置表的存储引擎

实例：创建category表，并设置其存储引擎为MyISAM

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)
)engine=MyISAM;

二、查看表结构

使用SQL语句查看表结构

(1) 查看表基本结构

语法格式：

describe 表名;

查看goods表结构：

desc goods;

(2) 查看建表语句

语法格式：

show create table 表名\G;

三、修改表

使用SQL语句修改表结构

(1) 修改表名

语法格式：

alter table 旧表名 rename 新表名;

实例：将goods表名改为tb_goods

步骤一：

查看数据库中的数据表

show tables;

步骤二：

修改goods表名为tb_goods

alter table goods rename tb_goods;

步骤三：

查看数据库中的数据表

show tables;

(2) 修改字段数据类型

语法格式：

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

实例：将tb_goods表中type字段的数据类型修改为char(30)

alter table tb_goods modify type char(30);

(3) 修改字段名

语法格式：

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

实例：将tb_goods表中name字段名修改为g_name

alter table tb_goods change name g_name varchar(30);

(4) 添加字段

在表的最后一列添加字段

语法格式：

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

实例：在tb_goods表中添加picture字段

alter table tb_goods add picture varchar(255);

在表的第一列添加字段

语法格式：

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

实例：在tb_goods表中添加state字段

alter table tb_goods add state tinyint(4) first;

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

语法格式：

alter table 表名 add 字段名 数据类型 after 指定列;

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

alter table tb_goods add intro text after num;

(5) 删除字段

语法格式：

alter table 表名 drop 字段名;

实例：将tb_goods表中picture字段删除

alter table tb_goods drop picture;

(6) 修改字段顺序

修改某字段为表的第一个字段

语法格式：

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

修改字段名1为表的字段名2之后

语法格式：

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

实例：将tb_goods表中state字段移至id字段之后

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

(7) 修改存储引擎

语法格式：

alter table 表名 engine=e_name;

实例：将category表的存储引擎为MyISAM

alter table category engine=InnoDB;

(8) 修改约束条件

a 、主键约束的添加和修改

语法格式：

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

删除：alter table 表名 drop primary key;

实例：将goods表的主键约束删除，再添加

alter table goods drop primary key;

alter table goods add primary key(id);

b 、唯一性约束的添加和删除

语法格式：

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

删除：alter table 表名 drop key 字段名;

实例：将goods表的唯一性约束删除，在添加

alter table goods drop key name;

alter table goods add unique key(name);

c 、自增约束的添加和删除

语法格式：

添加：alter table 表名 modify 字段名 数据类型 auto-increment;

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

实例：将goods表的自增约束删除，在添加

alter table goods modify id int;

alter table goods modify id int auto_increment;

d 、默认值约束的添加和删除

语法格式：

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

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

实例：将goods表的默认值约束删除，再添加

alter table goods modify num int default null;

alter table goods modify num int default 0;

e 、非空约束的添加和删除

语法格式：

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

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

实例：将goods表的非空约束删除，在添加

alter table goods modify type varchar(30) null;

alter table goods modify type varchar(30) not null;

f 、无符号约束的添加和删除

语法格式：

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

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

实例：将goods表无符号约束删除，在添加

alter table goods modify price decimal(7,2);

alter table goods modify price decimal(7,2) unsigned;

g 、外键约束的添加和删除

语法格式;

添加：alter table 表名 add constraint 约束名 foreign key(外键列);

删除：第一步：删除外键

alter table 表名 drop foreign key 约束名;

第二步：删除索引（索引名和约束名一样）

alter table 表名 drop index 索引名;

四、删除表

使用SQL语句删除表

删除没有关联的表

语法格式：

drop table if exists 表名1,表名2,......;

实例：删除category表和不存在的tb_goods

drop table if exists category,tb_goods;

删除被其他表关联的主表

步骤一：删除外键

语法格式：

alter table 表名 drop foreign key 外键名;

步骤二：删除表

语法格式：

drop table 表名;

实例：comment表和reply表存在外键关系，先删除reply表中的外键，然后删除comment表

alter table reply drop foreign key rep_com;
drop table comment;