技术文档

Appearance

表的基本操作 ​

数据库建好之后，我们就可以接着创建真正存储数据的表了。创建表的时候首先需要描述清楚这个表长什么样，它有哪些列，这些列都是用来存什么类型的数据等等，这个对表的描述称为表的结构或者定义。有了表的结构之后，我们就可以着手把数据塞到这个表里了。表中的一行叫做一条记录，一列叫做一个字段。

准备工作 ​

bash

- CREATE DATABASE IF NOT EXISTS testdemo;  # 创建一个名为testdemo的数据库

- USE testdemo;  # 使用testdemo数据库

展示当前数据库中的表 ​

下边的语句用于展示当前数据库中有哪些表

bash

SHOW TABLES;

我们的当前数据库是 testdemo，然后用上述语句查看下 testdemo 数据库中都有哪些表：

bash

mysql> SHOW TABLES;
Empty set (0.00 sec)

mysql>

很抱歉，当前 testdemo 数据库中一个表也没有，所以得到的结果就是 Empty set。我们赶紧在当前数据库中创建几个表噻。

创建表 ​

基本语法 ​

创建一个表时至少需要完成下列事情：

  • 给表起个名

  • 给表定义一些列,并且给这些列都个名

  • 每一个列都需要定义一种数据类型

  • 如果有需要的话,可以给这些列定义一些列的属性.比如不允许存储NULL,设置默认值等等,具体的后面会介绍

创建表 ​

MySQL 中创建表的基本语法如下：

bash

CREATE TABLE 表名 (
   列名 数据类型 [列属性],
   列名 数据类型 [列属性],
   ....
    列名 数据类型 [列属性],
);

也就说:

  • 在 CREATE TABLE 后面写清楚我们要创建的表的名称

  • 然后在小括号()中定义上这个表的各个列的信息，包括列的名称、列的数据类型，如果有需要的话也可以定义这个列的属性（列的属性用中括号[]引起来的意思就是这部分是可选的，也就是可有可无的）。

  • 列名、数据类型、列的属性之间用空白字符分开就好，然后各个列的信息之间用逗号,分隔开。

INFO

小贴士：

我们也可以把这个创建表的语句都放在单行中，而示例中将建表语句分成多行并且加上缩进仅仅是为了美观而已～

先创建一个超级简单的表

bash
CREATE TABLE fist_TABLE(
 ID INT UNSIGNED ,
 age TINYINT UNSIGNED
)

这个表的名称叫做 first_table，它有两个列：ID 和 age。ID 列的数据类型是 INT UNSIGNED，age 列的数据类型是 TINYINT UNSIGNED。我们并没有给这两个列定义任何属性，所以这两个列都允许存储 NULL 值。

  • 在客户端执行语句
bash
CREATE TABLE fist_TABLE(
 ID INT UNSIGNED ,
 age TINYINT UNSIGNED
)
> OK
> 查询时间: 0.013s

为建表语句添加注释 ​

  • 注释里面必须使用单引号

我们可以在创建表时将该表的用处以注释的形式添加到语句中，只要在建表语句最后加上 COMMENT 语句就好，如下：

bash
CREATE TABLE 表名 (
    各个列的信息 ...
) COMMENT '表的注释信息';

比如我们可以这样写user表的建表语句：

bash

CREATE TABLE user(
 id INT UNSIGNED,
 age TINYINT UNSIGNED
) COMMENT '用户信息表';

注释没必要太长，言简意赅即可，毕竟是给人看的，让人看明白是个啥意思就好了。为了我们自己的方便，也为了阅读你创建的人的方便，请遵守一下职业道德，写个注释吧～

实操(创建现实生活中的表) ​

有了创建 user 的经验，我们就可以着手用 MySQL 把之前提到的学生基本信息表和成绩表给创建出来了，先把学生基本信息表搬下来看看：

学生基本信息表

学号姓名性别身份证号学院专业入学时间
20180101杜子腾男158177199901044792计算机学院计算机科学与工程2018/9/1
20180102杜琦燕女151008199801178529计算机学院计算机科学与工程2018/9/1
20180103范统男17156319980116959X计算机学院软件工程2018/9/1
20180104史珍香女141992199701078600计算机学院软件工程2018/9/1

很显然，这个表有学号、姓名、性别、身份证号、学院、专业、入学时间这几个列，其中的学号是整数类型的，入学时间是日期类型的，由于身份证号是固定的 18 位，我们可以把身份证号这一列定义成固定长度的字符串类型，性别一列只能填男或女，所以我们这里把它定义为 ENUM 类型的，其余各个列都是变长的字符串类型。看一下创建学生基本信息表的语句：

bash

CREATE TABLE STUDENT_INFO(
 number INT,
 name VARCHAR(255),
 SEX ENUM('男','女'),
 id_number CHAR(18),
 department VARCHAR(30),
 major VARCHAR(30),
 enrollment_tme DATE
)COMMENT '学生信息表';

然后再看一下学生成绩表：

学号科目成绩
20180101母猪的产后护理78
20180101论萨达姆的战争准备88
20180102母猪的产后护理100
20180102论萨达姆的战争准备98
20180103母猪的产后护理59
20180103论萨达姆的战争准备61
20180104母猪的产后护理55
20180104论萨达姆的战争准备46

这个表有学号、科目、成绩这几个列，学号和成绩是整数类型的，科目是字符串类型的，所以我们可以这样写建表语句

bash
CREATE TABLE student_score ( number INT, SUBJECT VARCHAR ( 255 ), score TINYINT ) COMMENT '学生成绩表';

待这几个表创建成功之后，我们使用 SHOW TABLES 语句看一下当前数据库（testdemo 数据库）中有哪些表：

bash
fist_table
student_info
student_score
user

我们刚才创建的表就都被展示出来了

IF NOT EXISTS ​

和重复创建数据库一样，如果创建一个已经存在的表的话是会报错的，我们来试试重复创建一下 student_score 表：

bash

CREATE TABLE IF NOT EXISTS student_score ( number INT, SUBJECT VARCHAR ( 255 ), score TINYINT ) COMMENT '学生成绩表';

可以看到语句执行成功了，只是结果中有 1 个 warning 而已。

删除表 ​

如果我们觉得某个表以后都用不到了，就可以把它删除掉。在真实工作环境中删除表一定要慎重谨慎，失去了的就再也回不来了～ 看一下删除的语法：

bash

DROP TABLE 表1, 表2, ..., 表n;
  • 我们试着删除一下创建的 user 表：
BASH
mysql> DROP TABLE user;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW TABLES;
+---------------------+
| Tables_in_testdemo |
+---------------------+
| student_info        |
| student_score       |
+---------------------+
2 rows in set (0.00 sec)

mysql>

IF EXISTS ​

如果我们尝试删除某个不存在的表的话会报错：

bash
mysql> DROP TABLE first_table;
ERROR 1051 (42S02): Unknown table 'testdemo.first_table'
mysql>

执行结果提示了一个 ERROR，提示我们要删除的表并不存在，如果想避免报错，可以使用这种删除语法：

bash
DROP TABLE IF EXISTS 表名;

然后再删除一下不存在的 first_table 表：

bash

mysql> DROP TABLE IF EXISTS first_table;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>

查看表结构 ​

有时候我们可能忘记了自己定义的表的结构，可以使用下边这些语句来查看，它们起到的效果都是一样的：

bash

DESCRIBE 表名;
DESC 表名;
EXPLAIN 表名;
SHOW COLUMNS FROM 表名;
SHOW FIELDS FROM 表名;

比如我们看一下 student_info 这个表的结构：

bash

mysql> DESC student_info;
+-----------------+-------------------+------+-----+---------+-------+
| Field           | Type              | Null | Key | Default | Extra |
+-----------------+-------------------+------+-----+---------+-------+
| number          | int(11)           | YES  |     | NULL    |       |
| name            | varchar(5)        | YES  |     | NULL    |       |
| sex             | enum('男','女')   | YES  |     | NULL    |       |
| id_number       | char(18)          | YES  |     | NULL    |       |
| department      | varchar(30)       | YES  |     | NULL    |       |
| major           | varchar(30)       | YES  |     | NULL    |       |
| enrollment_time | date              | YES  |     | NULL    |       |
+-----------------+-------------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

mysql>

如果你看不惯这种以表格的形式展示各个列信息的方式，我们还可以使用下边这个语句来查看表结构：

bash

SHOW CREATE TABLE 表名;

比如:

bash

mysql> SHOW CREATE TABLE student_info;
+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table        | Create Table                                                                                                                                                                                                                                                                                                                                                          |
+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student_info | CREATE TABLE `student_info` (
  `number` int(11) DEFAULT NULL,
  `name` varchar(5) DEFAULT NULL,
  `sex` enum('男','女') DEFAULT NULL,
  `id_number` char(18) DEFAULT NULL,
  `department` varchar(30) DEFAULT NULL,
  `major` varchar(30) DEFAULT NULL,
  `enrollment_time` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生基本信息表'          |
+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

修改表 ​

在表创建好之后如果对表的结构不满意，比如想增加或者删除一列，想修改某一列的数据类型或者属性，想对表名或者列名进行重命名，这些操作统统都算是修改表结构。MySQL 给我们提供了一系列修改表结构的语句。

修改表名 ​

我们可以通过下边这两种方式来修改表的名称：

  • 方式一
bash
ALTER TABLE 旧表名 RENAME TO 新表名;
  • 方式二
bash
RENAME TABLE 旧表名 TO 新表名;

比如，我们想把 student_info 表的名称修改为 student_info_new，那么我们可以使用下边这两种方式：

bash

mysql> ALTER TABLE student_info RENAME TO student_info_new;
Query OK, 0 rows affected (0.00 sec)

增加列 ​

我们可以使用下边的语句来增加表中的列：

bash
ALTER TABLE 表名 ADD COLUMN 列名 数据类型 [列的属性];

比如我们向 first_table 里添加一个名叫 third_column 的列就可以这么写：

bash
mysql> ALTER TABLE first_table ADD COLUMN third_column CHAR(4) ;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE first_table\G
*************************** 1. row ***************************
       Table: first_table
Create Table: CREATE TABLE `first_table` (
  `first_column` int(11) DEFAULT NULL,
  `second_column` varchar(100) DEFAULT NULL,
  `third_column` char(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='第一个表'
1 row in set (0.01 sec)

mysql>

增加列到指定的特定位置 ​

默认的情况下列都是加到现有列的最后一列后面，我们也可以在添加列的时候指定它的位置，常用的方式如下：

  • 添加到第一列：
bash
ALTER TABLE 表名 ADD COLUMN 列名 列的类型 [列的属性] FIRST;
  • 添加到指定列的后面：
bash
ALTER TABLE 表名 ADD COLUMN 列名 列的类型 [列的属性] AFTER 指定列名;

比如，我们想向 first_table 表中添加一个 fourth_column 列，并且把它放到 first_column 列的后面，那么我们可以这么写：

bash
mysql> ALTER TABLE first_table ADD COLUMN fourth_column CHAR(4) AFTER first_column;
Query OK, 0 rows affected (0.05 sec)

删除列 ​

bash
ALTER TABLE 表名 DROP COLUMN 列名;

比如，我们想删除 first_table 表中的 third_column 列，那么我们可以这么写：

bash
mysql> ALTER TABLE first_table DROP COLUMN third_column;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE first_table DROP COLUMN fourth_column;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE first_table DROP COLUMN fifth_column;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE first_table\G
*************************** 1. row ***************************
       Table: first_table
Create Table: CREATE TABLE `first_table` (
  `first_column` int(11) DEFAULT NULL,
  `second_column` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='第一个表'
1 row in set (0.00 sec)

mysql>

修改列的信息 ​

  • 方式一
bash
ALTER TABLE 表名 MODIFY 列名 新数据类型 [新属性];

我们来修改一下 first_table 表的 second_column 列，把它的数据类型修改为 VARCHAR(2)：

bash
mysql> ALTER TABLE first_table MODIFY second_column VARCHAR(2);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE first_table\G
*************************** 1. row ***************************
       Table: first_table
Create Table: CREATE TABLE `first_table` (
  `first_column` int(11) DEFAULT NULL,
  `second_column` varchar(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='第一个表'
1 row in set (0.00 sec)

mysql>

INFO

  • 如果改完后新的列不能存储下原先的数据 比如 varchar(100)变成 varchar(2),那么就会直接报错
  • 方式二
bash
ALTER TABLE 表名 CHANGE 旧列名 新列名 新数据类型 [新属性];

我们也可以使用 CHANGE 关键字来修改列的信息，比如我们想把 first_table 表的 second_column 列修改为 third_column 列，并且把它的数据类型修改为 VARCHAR(2)：

bash
mysql> ALTER TABLE first_table CHANGE second_column second_column1 VARCHAR(2)
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE first_table\G
*************************** 1. row ***************************
       Table: first_table
Create Table: CREATE TABLE `first_table` (
  `first_column` int(11) DEFAULT NULL,
  `second_column1` varchar(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='第一个表'
1 row in set (0.00 sec)

mysql>

修改列的排列位置 ​

将列设为表的第一列 ​

bash
ALTER TABLE 表名 MODIFY 列名 列的类型 列的属性 FIRST;
  • 举例
bash
mysql> ALTER TABLE first_table MODIFY second_column1 VARCHAR(2) FIRST;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE first_table\G
*************************** 1. row ***************************
       Table: first_table
Create Table: CREATE TABLE `first_table` (
  `second_column1` varchar(2) DEFAULT NULL,
  `first_column` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='第一个表'
1 row in set (0.00 sec)

mysql>

将列放到指定列的后边 ​

语法 ​

bash
ALTER TABLE 表名 MODIFY 列名 列的类型 列的属性 AFTER 指定列名;
  • 举例
bash
mysql> ALTER TABLE first_table MODIFY second_column1 VARCHAR(2) AFTER first_column;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE first_table\G
*************************** 1. row ***************************
       Table: first_table
Create Table: CREATE TABLE `first_table` (
  `first_column` int(11) DEFAULT NULL,
  `second_column1` varchar(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='第一个表'
1 row in set (0.00 sec)

mysql>

一条语句中包含多个修改操作 ​

如果对同一个表有多个修改操作的话，我们可以把它们放到一条语句中执行，就像这样：

语法 ​

bash
ALTER TABLE 表名 操作1, 操作2, ..., 操作n;
  • 举例
bash
ALTER TABLE first_table DROP COLUMN third_column, DROP COLUMN fourth_column, DROP COLUMN fifth_column;

网站备案号:津ICP备15001525号-2