MySQL 表操作

1、创建表

• create table + 表名（ 字段1名称 数据类型 约束 备注，字段2名称 数据类型 约束 备注，字段3名称 数据类型 约束 备注 ）engine=INNODB default CHARSET=utf8 comment'学生表'
  • engine=INNODB    存储引擎
  • CHARSET=utf8　　字符编码集
  • comment'学生表'　　备注

• mysql 常用的数据类型
  • int　　存储整数　　
  • float　　存储小数　　
  • char　　存储字符（固定字符长度）
  • varchar　　存储字符（可变长字符长度）
  • 注意点
    • int(3)　　int(M) 　　M 指最大显示宽度　　
    • char(20)　　char(M)　　M 指最大能存储 20 个字符　　

• 约束条件
  • 约束用于对表中字段进行限制，保证表中数据的正确性和唯一性

• 约束类型
  • primary key　　主键约束
    • 说明：非空，唯一，用于唯一标识对应的记录，类似身份证　　
  •  foreign key　　外键约束
    • 说明：用于表与表建立关系模型，使表与表紧密的结合起来
  •  not null　　非空约束
    • 说明：字段值不能为 Null
  • default　　默认值约束
    • 说明：默认给字段指定默认值　　
  • auto_increment　　自增约束
    • 说明：作用在整数类型，字段默认从 1 开始自增　　

# 创建 t_student 表
mysql> create table t_student(id int(3) primary key comment'学生id', name varchar(30) comment'学生姓名')engine=INNODB default CHARSET=utf8 comment'学生表'；

# 查看验证 t_student 表创建成功
mysql> show tables;
+-------------------+
| Tables_in_student |
+-------------------+
| t_student         |
+-------------------+ 

# 创建 shanghai 表，表中带有 primary key、auto_increment、not null、default 约束
mysql> create table shanghai (sid int(3) primary key auto_increment,name varchar(20) not null, age int(3) default 22);

mysql> desc shanghai;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| sid   | int(3)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | NO   |     | NULL    |                |
| age   | int(3)      | YES  |     | 22      |                |
+-------+-------------+------+-----+---------+----------------+

 

2、查看表结构

• desc + 表名

mysql> desc t_student;

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(3)      | NO   | PRI | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

• 修改表名
  • alter table + 表名 rename + 新表名

# 修改 t_student 表名称为 t_stu
mysql> alter table t_student rename t_stu;

# 查看验证修改成功
mysql> show tables;

+-------------------+
| Tables_in_student |
+-------------------+
| t_stu             |
+-------------------+

• 修改表字段
  • alter table + 表名 change + 原字段名 + 新字段名 数据类型，约束

# 修改 t_stu 表字段 id 为 uid
mysql> alter table t_stu change id uid int(3) comment'用户id';

# 查看验证修改成功
mysql> desc t_stu;

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| uid   | int(3)      | NO   | PRI | 0       |       |
| name  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

• 修改主键 uid 为自增长
  • alter table + 表名 change + 原字段名 + 新字段名 数据类型 auto_increment

# 修改主键 uid 设为自增长
mysql> alter table t_stu change uid uid int(3) auto_increment;

# 查看验证修改成功
mysql> desc t_stu;

+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(3)      | YES  |     | NULL    |                |
| uid   | int(3)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(30) | YES  |     | NULL    |                |
| age   | int(3)      | YES  |     | NULL    |                |
| sex   | varchar(10) | YES  |     | NULL    |                |
| phone | varchar(11) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

• 删除自增长约束

mysql> desc shenzhen;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| sid   | int(3)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | NO   |     | NULL    |                |
| age   | int(3)      | YES  |     | 22      |                |
+-------+-------------+------+-----+---------+----------------+

mysql> alter table shenzhen change sid uid int(3);

mysql> desc shenzhen;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| uid   | int(3)      | NO   | PRI | 0       |       |
| name  | varchar(20) | NO   |     | NULL    |       |
| age   | int(3)      | YES  |     | 22      |       |
+-------+-------------+------+-----+---------+-------+

• 删除 primary key 主键约束
  • sql：alter table TableName drop primary key;　　
  • 注意：删除主键约束之前必须先删除 auto_increment 自增长约束，否则报错：ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key　　

mysql> desc shenzhen;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| uid   | int(3)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | NO   |     | NULL    |                |
| age   | int(3)      | YES  |     | 22      |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> alter table shenzhen drop primary key;

# 报错如下
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

mysql> desc shenzhen;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| uid   | int(3)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | NO   |     | NULL    |                |
| age   | int(3)      | YES  |     | 22      |                |
+-------+-------------+------+-----+---------+----------------+

mysql> alter table shenzhen change uid uid int(3);

mysql> desc shenzhen;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| uid   | int(3)      | NO   | PRI | 0       |       |
| name  | varchar(20) | NO   |     | NULL    |       |
| age   | int(3)      | YES  |     | 22      |       |
+-------+-------------+------+-----+---------+-------+

mysql> alter table shenzhen drop primary key;

mysql> desc shenzhen;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| uid   | int(3)      | NO   |     | 0       |       |
| name  | varchar(20) | NO   |     | NULL    |       |
| age   | int(3)      | YES  |     | 22      |       |
+-------+-------------+------+-----+---------+-------+

• 添加表字段，并放到第一个字段前
  • alter table + 表名 add + 字段名 数据类型 约束 first

# 在 t_stu 第一个字段前添加表字段 id
mysql> alter table t_stu add id int(3) first;

# 查看验证添加成功
mysql> desc t_stu;

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(3)      | YES  |     | NULL    |       |
| uid   | int(3)      | NO   | PRI | 0       |       |
| name  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

• 添加表字段，并放到某个字段后
  • alter table + 表名 add + 字段名 数据类型 约束 after +字段名

# 在 t_stu 表中 name 字段后添加 age 字段
mysql> alter table t_stu add age int(3) after name;

# 查看验证添加成功
mysql> desc t_stu;

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(3)      | YES  |     | NULL    |       |
| uid   | int(3)      | NO   | PRI | 0       |       |
| name  | varchar(30) | YES  |     | NULL    |       |
| age   | int(3)      | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

• 同时添加两个字段，默认添加到字段最后
  • alter table + 表名 add(字段1 数据类型，字段2 数据类型)

# 在表 t_stu 中同时新增 sex 和 phone 两个字段
mysql> alter table t_stu add(sex varchar(10),phone varchar(11));

# 查看验证新增成功
mysql> desc t_stu;

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(3)      | YES  |     | NULL    |       |
| uid   | int(3)      | NO   | PRI | 0       |       |
| name  | varchar(30) | YES  |     | NULL    |       |
| age   | int(3)      | YES  |     | NULL    |       |
| sex   | varchar(10) | YES  |     | NULL    |       |
| phone | varchar(11) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

• 删除表字段
  • alter table + 表名 drop + 字段

# 删除表中 uid 字段 
mysql> alter table t_stu drop uid;

# 验证删除成功
mysql> desc t_stu;

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(3)      | YES  |     | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
| age   | int(3)      | YES  |     | NULL    |       |
| sex   | varchar(10) | YES  |     | NULL    |       |
| phone | varchar(11) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

• 删除表两个字段
  • alter table + 表名 drop 字段1，drop 字段2

# 同时删除 sex 和 phone 两个字段
mysql> alter table t_stu drop sex,drop phone;

# 验证删除成功
mysql> desc t_stu;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(3)      | YES  |     | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
| age   | int(3)      | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

• 删除表（删除表数据和表结构）
  • drop table + 表名

drop table t_stu;

• 修改表的备注
  • alter table + 表名 comment "备注说明"

mysql> alter table t_student comment"student table";

• 修改表字段的备注
  • alter table + 表名 modify + 字段名 数据类型 comment "备注说明"

mysql> alter table t_student modify id int(3) comment "student id";