03｜第三话：基础篇-MySQL数据库和表的基本操作

用两篇文章介绍了MySQL比较零碎的概念，这些基础的概念是贯穿整个MySQL学习周期的，结束了这些概念后，我们来真正使用MySQL，第一个面对的就是【数据库】和【表】这两个对象。

本篇文章整体脉络如下：

一、数据库和表

数据库英文名叫做database，表的英文名叫做table，每个database下面管理若干张table，所以整体逻辑是：MySQL服务器管理若干database，每个database管理若干张table。

简而言之，数据库就是管理若干张表的大仓库，数据库根据业务需求进行创建，比如设计一个简单的在线美食商城，我们可以创建一个数据库叫做：yummy_shop，那么会有哪些表呢？这个其实就是业务设计的初期，根据场景进行合理的设计了，表结构可能包含：

用户表：存放注册用户的信息，包括用户的名字、密码、手机号码、性别等基础信息；
用户收货地址表：管理每个用户所创建的地址信息；
首页轮播图管理表：管理首页轮播图的展示情况；
商品分类表：比如甜点类、熟食类、蔬果类等等，还可能包括二级分类三级分类；
商品信息表：比如马卡龙这个具体商品的信息，这里可能会分为几张表，比如：商品主表包含商品名称等核心信息、商品参数表包含这个商品的具体产品参数、商品图片表包含了这个商品的各种图片、商品评论表包含关于这个商品用户的评价和打分等等；
订单表：包括订单主表、订单的每个商品详情表、订单的发货送货状态表等等；
支付信息表：管理该订单用户支付信息，包含用户支付方式、支付状态等信息；

当然了，要做成完善的电商系统，是极其复杂的，可能会有成百上千张表来支撑，甚至分为了多个database；

从这个例子中其实可以看出，从逻辑上看，表是组织实际数据的地方，比如用户数据实际上是存放在用户表中，订单数据都存放在订单相关的表中，要用到这些数据时，必须要找到对应的表进行操作。

我们的上层业务系统，比如JAVA所写的API，本质上都是操作这些表的数据，对数据库进行增删改查操作，从而在页面上做数据的展示，形如下面这种简单且普遍使用的系统架构：

对于很多程序员来说，MySQL就是一个提供增删改查API的黑盒系统，大多数的工作是围绕JAVA API工程做业务性质的开发，一方面提供API给前端调用，一方面操作数据库进行增删改查。

或许有读者朋友问为啥数据库要这样设计？我们可以类比到平时创建Excel表格这样的工作，老板可能会让你给他一张表格，里面需要一些信息：比如员工基本信息、员工的工作职责表、各个部门的基本信息和职责表等。

那么第一步是创建这个Excel，名字可能叫做《公司信息汇总.xslx》，对应到这里类似创建一个数据库database；其中可能包含三张表《员工基本信息表》、《员工的工作职责表》、《部门的基本信息和职责表》，你可能会这样创建：

那么创建数据库和创建表也是一样的思路，这个问题就变成了：你为啥创建了一个Excel文件，然后里面创建了三张表信息。

二、MySQL的基本操作

MySQL基本操作包括：

DDL（数据定义语言，data definition language）

包括用户、数据库、表、视图、索引、触发器、事件、存储过程和自定义函数等对象的操作，关键字包括：create、drop、alter等。

DML（数据操纵语言，data manipulation language）

数据操作语言，主要是数据库增删改三种操作，关键字包括：insert、update、delete等；

DQL（数据查询语言，data query language）

数据查询语言，主要是数据库查询操作，关键字包括：select等。

DCL（数据控制语言，data control language）

用来设置或者更改数据库用户角色权限等的语句，关键字包括：grant、revoke等。

也有文章归类为三类：DDL、DML和DCL，将DQL归类到DML中，无论怎么分类只要理清楚概念即可，本文关注的是对数据库和表的DDL操作，其实说白了，就是如何创建删除数据库、如何创建删除修改表，还不涉及对具体数据的操作。

三、数据库的操作

第一步是创建数据库，有了数据库才可以创建表，数据库的操作极其简单，我们来简单捋一遍。

🧷操作一：新建数据库

语法为：

CREATE DATABASE 数据库名;

比如我创建一个测试数据库，名字就叫做test_mysql：

mysql> CREATE DATABASE test_mysql;
Query OK, 1 row affected (0.01 sec)

看到ok说明已经创建成功，假设我再重复执行一遍如上语句会报错：

mysql> CREATE DATABASE test_mysql;
ERROR 1007 (HY000): Can't create database 'test_mysql'; database exists

报错提示该数据库已存在，此时我们可以做个判断：如果指定的数据库不存在的话就创建它，否则什么都不做，通过以下语法可以实现：

CREATE DATABASE IF NOT EXISTS 数据库名;

这样就不会出现报错了，下面的问题是如何看到已经创建的数据库有哪些呢？

🧷操作二：展示所有数据库

执行show databases;命令查看数据库列表（注意，本系列文章所使用的MySQL版本是5.7.38，后续将不再赘述）：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test_mysql         |
+--------------------+
5 rows in set (0.00 sec)

可以看到我们刚刚创建的一个新的数据库叫做test_mysql，此外还可以看到四个数据库：information_schema、mysql、performance_schema、sys，这四个库是MySQL的默认库，存放了所有数据库的元信息，比如MySQL服务器中有哪些数据库、哪些表、哪些列、列的权限、支持的字符集等等，尤其需要关注的是mysql数据库：

mysql：这是一个核心库，核心存储了用户以及对应的权限信息，比如我们root这个登陆用户就是在这个库的user表中管理的；

🧷操作三：切换数据库

如果需要使用新建的test_mysql数据库，则需要确定，否则MySQL不知道你要访问的是哪个数据库的这张表，语法也十分简单：

use 数据库名;

接下来才可以操作对应的表。

其实我们在新启动客户端连接服务器时就可以指定连接建立成功后客户端的当前数据库，只要把数据库名称写在启动客户端的命令mysql -h 主机名 -u 用户名 -p密码后边就好，如下：

mysql -h localhost -u root -p123456 test_mysql

🧷操作四：删除数据库

DROP DATABASE 数据库名;

此处应设置警告牌⚠️！

语法十分简单，但是删除数据库是一个极其需要慎重的事情，一旦误删现网数据库，将造成不可估量的灾难。

经过确认后执行了删除，提示成功：

mysql> drop database test_mysql;
Query OK, 0 rows affected (0.00 sec)

此时若重复执行此命令也会报错，提示数据库不存在，如何避免该错误呢？从上面创建数据库受到启发，我们需要做个判断：如果该数据库存在则删除，否则什么也不做，语法对应的就是：

DROP DATABASE IF EXISTS 数据库名;

四、表的基本操作

有了数据库，下面就需要创建表来存储具体数据了。

容易想到，表的操作相对数据库来说就比较复杂了，比如用户表，我们需要考虑这个表长什么样子，具体点说是包含哪些字段，字段用什么类型，字段的长度、字段的属性。

我们可以类比Excel表格，本质都是一样的思考方式，现在给读者朋友一个任务：设计一个Excel，来统计你们班同学的基本信息：学号、姓名、年龄、性别、家庭住址。

这个任务只要是个用过Excel的人应该都可以完成，可能会做成这样：

设计MySQL的时候也是一个道理，需要创建这么一张表，然后里面提前设计好字段（对应的就是这里的学号、姓名、年龄、性别、家庭住址这五个字段），也就是说一列对应一个字段；设计好字段后我们就可以填充数据，比如这里填充了三行数据，一行数据在MySQL中叫做一条记录。总结而言：表中的一行叫做一条记录，一列叫做一个字段。

我们可以注意到，每种数据可能类型不一样，比如年龄，一定是正整数，而不可能填写18.3这样的年龄；比如性别，要么是男要么是女，不可能填其他的；比如姓名和家庭住址，可能包含数字、字母、汉字等各种情况，这类其实就是普通的字符串。这些在设计MySQL表字段的时候需要关注。

除了类型，还有就是字段本身的属性，比如学号不能填重复（有唯一性要求）；比如某些女生不想暴露年龄这样的信息，而班主任说这个年龄可以不填，那么这一列可以默认置空；比如姓名要求必填，大家都不可以空着，说明这一列不能为空。

好了，通过这个例子我们大概知道下面需要做什么了，下面就是具体介绍新建、删除、修改这张表在MySQL如何操作。

🧷操作一：展示当前数据库中的表

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

SHOW TABLES;

使用上文中创建的数据库查看：

mysql> use test_mysql;
Database changed
mysql> show tables;
Empty set (0.00 sec)

其实我们还可以省去通过use test_mysql;来切换数据库，我们可以直接在语句中指定所查询的数据库：

mysql> show tables from test_mysql;
+----------------------+
| Tables_in_test_mysql |
+----------------------+
| student_info         |
+----------------------+
1 row in set (0.00 sec)

目前test_mysql数据库中一张表也没有，下面我们来创建一个简单的表。

🧷操作二：创建表

MySQL中创建表的基本语法就是这样的：

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

类似创建Excel表格的过程：

给每个表起个名；
给表定义一些列，并且给这些列都起个名；
每一个列都需要定义一种数据类型；
如果有需要的话，可以给这些列定义一些列的属性，比如不许存储NULL，设置默认值等等，这些我们后面详细说明，我们从最简单的开始。

我们对应这张表创建一个MySQL对应的表：

表名就叫做student_info，有五个字段：

CREATE TABLE student_info (
    id INT comment "学号",
    name VARCHAR(20) comment "姓名",
    age INT comment "年龄",
    sex TINYINT comment "性别：0:男；1:女",
    address VARCHAR(30) comment "家庭住址"
) COMMENT '学生基本信息表';

我们对这张表做个简单的解释：由于学号是整数，因此使用INT类型，字段名称叫做id；姓名叫做name，使用VARCHAR这样的字符串类型来标识；年龄叫做age，跟id差不多定义；性别叫做sex，这里选择使用了TINYINT类型，其实就是很短的整数，因为性别就两种，这样做可以节省点存储空间；地址叫做address，也是字符串类型。

我们目前只要知道，数字可以使用int类型，字符串可以使用varchar类型，至于还有哪些类型、如何正确选择和使用这些类型，我们下面将着重介绍。

后面的comment意思是该字段的注释，一看就明白这个字段大概是什么含义，对于系统的交接维护是十分重要的。

执行成功后，此时再次执行SHOW TABLES;可以看到此表了。

🧷操作三：查看表结构

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

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

比较常用的当然是DESC了，谁会愿意多打那么多字呢？

mysql> DESC student_info;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | YES  |     | NULL    |       |
| name    | varchar(20) | YES  |     | NULL    |       |
| age     | int(11)     | YES  |     | NULL    |       |
| sex     | tinyint(4)  | YES  |     | NULL    |       |
| address | varchar(30) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

这样就可以看到我们每个字段的名字、类型等信息。有的时候需要看创表语句，我们可以使用：

SHOW CREATE TABLE 表名;

mysql> SHOW CREATE TABLE student_infoG
*************************** 1. row ***************************
       Table: student_info
Create Table: CREATE TABLE `student_info` (
  `id` int(11) DEFAULT NULL COMMENT '学号',
  `name` varchar(20) DEFAULT NULL COMMENT '姓名',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  `sex` tinyint(4) DEFAULT NULL COMMENT '性别：0:男；1:女',
  `address` varchar(30) DEFAULT NULL COMMENT '家庭住址'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生基本信息表'
1 row in set (0.00 sec)

mysql>

这正是我们上面创建表所使用的语句，最后有一个ENGINE=InnoDB DEFAULT CHARSET=utf8其实是指定了表的默认存储引擎和默认字符集，那么InnoDB存储引擎到底有啥特点、我们为什么要用它；还有就是字符集问题，到底什么是字符集、有哪些字符集、各字符集的区别是什么。

带着无数的问题，我们后面逐一解决，我们后续的学习方向也就有了脉络。

🧷操作四：删除表

删除表的语法基本同数据库的操作：

DROP TABLE 表1, 表2, …, 表n;

同样，这里删除操作十分危险，此处应设置警告牌⚠️！

🧷操作五：修改表名

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

先来看看如何修改表名，修改表名有两种方式：

方式一：

ALTER TABLE 旧表名 RENAME TO 新表名;

方式二：

RENAME TABLE 旧表名1 TO 新表名1, 旧表名2 TO 新表名2, … 旧表名n TO 新表名n;

看操作：

mysql> show tables from test_mysql;
+----------------------+
| Tables_in_test_mysql |
+----------------------+
| student_info         |
+----------------------+
1 row in set (0.00 sec)

mysql> alter table student_info rename to student_info_new;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables from test_mysql;
+----------------------+
| Tables_in_test_mysql |
+----------------------+
| student_info_new     |
+----------------------+
1 row in set (0.00 sec)

mysql> rename table student_info_new to student_info;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables from test_mysql;
+----------------------+
| Tables_in_test_mysql |
+----------------------+
| student_info         |
+----------------------+
1 row in set (0.01 sec)

mysql>

修改表名的操作还提供了一个特殊的功能：通过指定数据库名的方式，我们可以将一个数据库下的表挪动到另外一个数据库中。

此时准备两个数据库：test_mysql和test_mysql_new，执行如下语句，可以将test_mysql的student_info表重命名为student_info_new并且将其挪到test_mysql_new数据库中去，请看操作：

mysql> create database test_mysql_new;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test_mysql         |
| test_mysql_new     |
| yummy_shop         |
+--------------------+
7 rows in set (0.00 sec)

mysql> show tables from test_mysql;
+----------------------+
| Tables_in_test_mysql |
+----------------------+
| student_info         |
+----------------------+
1 row in set (0.01 sec)

mysql> alter table test_mysql.student_info rename to test_mysql_new.student_info_new;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables from test_mysql;
Empty set (0.00 sec)

mysql> show tables from test_mysql_new;
+--------------------------+
| Tables_in_test_mysql_new |
+--------------------------+
| student_info_new         |
+--------------------------+
1 row in set (0.00 sec)

mysql>

这里是通过数据库名.表名的方式指定了操作哪个数据库的哪张表。

此外，不知道读者朋友有没有注意到，执行语句的时候大写小写其实都可以，对于这里的SQL来说并无区别。

🧷操作六：修改表结构

此时学生信息表需要增加新的列：身份证号码字段，语法为：

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

mysql> alter table student_info add column id_number char(18);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE student_infoG
*************************** 1. row ***************************
       Table: student_info
Create Table: CREATE TABLE `student_info` (
  `id` int(11) DEFAULT NULL COMMENT '学号',
  `name` varchar(20) DEFAULT NULL COMMENT '姓名',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  `sex` tinyint(4) DEFAULT NULL COMMENT '性别：0:男；1:女',
  `address` varchar(30) DEFAULT NULL COMMENT '家庭住址',
  `id_number` char(18) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生基本信息表'
1 row in set (0.00 sec)

mysql>

可以看到，身份证号码对应的id_number列已经成功加入，位置默认都是加到现有列的最后一列后面，其实我们还可以指定插入位置，比如插入到第一个位置是：

ALTER TABLE 表名 ADD COLUMN 列名列的类型 [列的属性] FIRST;

或者指定插到某个字段的屁股后面：

ALTER TABLE 表名 ADD COLUMN 列名列的类型 [列的属性] AFTER 指定列名;

这里不做繁琐的演示，请读者朋友自行实验。

如何删除列呢？语法是：

ALTER TABLE 表名 DROP COLUMN 列名;

mysql> alter table student_info drop column id_number;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE student_infoG
*************************** 1. row ***************************
       Table: student_info
Create Table: CREATE TABLE `student_info` (
  `id` int(11) DEFAULT NULL COMMENT '学号',
  `name` varchar(20) DEFAULT NULL COMMENT '姓名',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  `sex` tinyint(4) DEFAULT NULL COMMENT '性别：0:男；1:女',
  `address` varchar(30) DEFAULT NULL COMMENT '家庭住址'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生基本信息表'
1 row in set (0.00 sec)

mysql>

可以看到，刚刚新增的身份证号码这个字段已经成功被删除了。

最后是修改列的信息，比如修改字段名称、类型、属性等，也有两种方式。

方式一：

ALTER TABLE 表名 MODIFY 列名新数据类型 [新属性];

比如家庭住址有的同学比较长，这里长度30不够存放，修改address长度为100，那么可以这样操作：

mysql> alter table student_info modify address varchar(100);
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE student_infoG
*************************** 1. row ***************************
       Table: student_info
Create Table: CREATE TABLE `student_info` (
  `id` int(11) DEFAULT NULL COMMENT '学号',
  `name` varchar(20) DEFAULT NULL COMMENT '姓名',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  `sex` tinyint(4) DEFAULT NULL COMMENT '性别：0:男；1:女',
  `address` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生基本信息表'
1 row in set (0.01 sec)

mysql>

可以看到成功将address长度改为了100，这里有个注意点，修改后的数据类型和属性一定要兼容表中现有的数据！比如此时表中已经有数据了，若尝试将地址字段修改长度为1，此时就会报错，因为长度为1存不下现有的数据了，就像一枚戒指，改大一点还是可以套在手指上的，但是改小一圈就不一定可以套进手指啦。

还可以顺便指定下该列的位置，跟新增字段的用法一样，比如我修改address长度为255，并且位置修改为第一个：

mysql> alter table student_info modify address varchar(255) first;
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE student_infoG
*************************** 1. row ***************************
       Table: student_info
Create Table: CREATE TABLE `student_info` (
  `address` varchar(255) DEFAULT NULL,
  `id` int(11) DEFAULT NULL COMMENT '学号',
  `name` varchar(20) DEFAULT NULL COMMENT '姓名',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  `sex` tinyint(4) DEFAULT NULL COMMENT '性别：0:男；1:女'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生基本信息表'
1 row in set (0.00 sec)

mysql>

当然也可以指定放在某个字段后面，比如将address字段调整到name后面：

mysql> alter table student_info modify address varchar(255) after name;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE student_infoG
*************************** 1. row ***************************
       Table: student_info
Create Table: CREATE TABLE `student_info` (
  `id` int(11) DEFAULT NULL COMMENT '学号',
  `name` varchar(20) DEFAULT NULL COMMENT '姓名',
  `address` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  `sex` tinyint(4) DEFAULT NULL COMMENT '性别：0:男；1:女'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生基本信息表'
1 row in set (0.00 sec)

mysql>

方式二：

ALTER TABLE 表名 CHANGE 旧列名新列名新数据类型 [新属性];

可以看到这种修改方式需要我们填两个列名，也就是说在修改数据类型和属性的同时也可以修改列名！比如这里修改家庭住址字段address名字为新的home_address，并且修改长度为200。

mysql> desc student_info;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | YES  |     | NULL    |       |
| name    | varchar(20)  | YES  |     | NULL    |       |
| address | varchar(255) | YES  |     | NULL    |       |
| age     | int(11)      | YES  |     | NULL    |       |
| sex     | tinyint(4)   | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> ALTER TABLE student_info CHANGE address home_address VARCHAR(200);
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> desc student_info;
+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| id           | int(11)      | YES  |     | NULL    |       |
| name         | varchar(20)  | YES  |     | NULL    |       |
| home_address | varchar(200) | YES  |     | NULL    |       |
| age          | int(11)      | YES  |     | NULL    |       |
| sex          | tinyint(4)   | YES  |     | NULL    |       |
+--------------+--------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

mysql>