MySQL基础篇 | 蒲公英

目标：

完成 MySQL 的安装，掌握命令客户端的登录、退出等基本操作；

掌握数据库的 CRUD 操作；

掌握数据表的 CRUD 操作；

掌握数据的 CRUD 操作。

前面介绍 JavaWeb 时说过，数据库是其极为重要的一个组成部分。那不免产生疑问：数据库是什么，我们为什么要使用它？使用 Excel、TXT 等文件存储数据不可以吗？

实际上，早前我们做系统，数据存储采用的就是文件存储，将数据存储到文件中至少比放到内存中要安全，可以保证系统关闭数据不会丢失，但是文件存储是有它的弊端的。

假设有这样一个场景，用 TXT 文件存储我们班级的同学信息：

姓名	年龄	性别	住址
张三	16	男	北京
李四	17	男	重庆
王五	19	男	广东

现在如果要修改李四这条数据的年龄和住址，我们可以通过之前学习的 IO 技术将文件中的数据读取到内存中，然后修改后再回存到文件中。通过这种方式实现当然可以。但是现在只存储了 3 条数据，那如果文件中存储的是全国所有人的信息呢，那这个文件就相当大了，已经超出了我们个人电脑甚至是大型服务器的内存范围，已经没有办法将整个文件加载到内存中了。为了解决这个问题，就需要引入新的技术了，那数据库就是这样的一门技术。

下面，我们就来学习下数据库。本节课程计划分三部分来推进：

首先，介绍下数据库相关的概念；
然后，我们会学习一款非常主流的数据库管理软件，即 MySQL；
最后，还会学习一门非常重要的编程语言，SQL，通过 SQL 来操作数据库。

# 数据库相关概念

# 数据库

首先，我们来看下数据库的概念：

全称：Database，简称 DB；
用来存储和管理数据的仓库。

后面我们在开发 Web 网站的时候可以将数据存储在数据库里，而且数据库存储数据是有组织的进行存储的。那么如何理解这个有组织的进行存储？

其一，数据以特定格式进行存储；
其二，存储过程要安全、高效，存取的过程中数据不能出现错乱。

# 数据库管理系统

接下来我们了解一下数据库管理系统的概念，它其实是管理数据库的大型软件系统，英文全称：DataBase Management System，简称DBMS。

也就是说，当我们安装好数据库管理系统以后，就可以通过这个系统创建数据库用以存储数据，还可以对数据库中的数据进行增、删、改、查等操作。

我们经常说的 MySQL 数据库到底是数据库呢，还是数据库管理系统？

遇到这样的提问，实际上我们在没了解数据库和数据库管理系统的概念之前靠猜都能给出答案。答案肯定是后者了，虽然我们经常叫 MySQL 数据库，但它实际上是一个 DBMS，只不过我们口头上都省略了管理系统这几个字。

# 常见的数据库管理系统

下面来简单介绍一下它们：

Oracle：收费的大型数据库，Oracle(甲骨文)公司的产品；
MySQL：开源免费的中小型数据库，最初 MySQL 被 Sun 公司收购了，后来 Oracle 又收购了 Sun 公司；
SQL Server：MicroSoft 公司收费的中型的数据库。C#、.net 等语言常使用；
PostgreSQL：开源免费中小型的数据库；
DB2：IBM 公司的大型收费数据库产品；
SQLite：嵌入式的微型数据库，如：作为 Android 内置数据库；
MariaDB：开源免费中小型的数据库。

上面的数据库除 MySQL 外其他的我们简单了解一下即可，后面我们要重点学习MySQL，因为，它在国内比较流行，大部分企业都在使用。

讲到这里，就会产生两个问题：

我们只学习了 MySQL，如果后续要用 Oracle 或者 PostgreSQL 怎么办？
之前已经说了，通过 DBMS 可以操作数据库，对数据库中的数据进行增、删、改、查等操作，那用户如何与 DBMS 进行交互？

为了解决这两个问题，我们就要学习一门编程语言，也即是 SQL。下面我们就来看下 SQL 的概念。

# SQL

SQL，英文全称：Structured Query Language，简称 SQL，翻译过来叫做结构化查询语言，是操作关系型数据库的编程语言，至于关系型该如何理解，我们后面会进行详细介绍。SQL 定义了操作所有关系型数据库的统一标准，因此它不仅可以操作 MySQL，还可以操作其他类型的关系型数据库，比如 Oracle、PostgreSQL 等等。

# MySQL 安装、配置

介绍完数据库相关概念后，下面我们就来开始第二部分 MySQL 的学习，首先我们按照下面的流程来安装一下 MySQL。

# 确认环境

操作系统	位数
Win10	64 位

# 下载安装包

访问MySQL 官方下载地址，我们可以看到下载页面，在页面中选择我们需要安装的操作系统环境以及 MySQL 软件版本即可下载。

MySQL 5.7.24，32、64 位 Windows 解压安装包已经放到随堂资料里面，当前我们就不需要在下载了

# 解压安装

将安装包解压至非中文路径目录下，我们就得到 MySQL 5.7.24 的软件本体。下面我们来了解一下它的目录结构：

# 配置 MySQL

# 环境变量

MySQL 解压后，bin 目录里面存放了相关的.exe可执行文件，为了方便后续我们在任何目录下都可以执行 MySQL 相关的命令，就需要进行环境变量的配置：

桌面 -> 此电脑 -> 鼠标右键 -> 属性 -> 高级系统设置 -> 环境变量：
在 系统变量 中添加MYSQL_HOME，变量值复制 MySQL 解压文件目录路径：
在 系统变量 中找到Path，双击 Path，点击新建添加%MYSQL_HOME%\bin：
验证是否添加成功，以管理员权限打开命令提示符：

环境变量的作用

在命令提示符(即 CMD)中输入一个可执行程序的名字，Windows 会先在环境变量中的Path所指的路径中寻找一遍，如果找到了就直接执行，没找到就在当前工作目录找，如果还没找到，就报错。我们添加环境变量的目的就是能够在任意一个黑框直接调用 MySQL 中的相关程序而不用总是修改工作目录，大大简化了我们的操作。

# 配置 my.ini 文件

my.ini 是 MySQL 数据库中使用的配置文件，MySQL 服务器启动时会读取这个配置文件，我们可以通过修改这个文件，达到更新配置的目的。一般情况下，my.ini 在 MySQL 安装的根目录下，如果没有的话我们也可以手动创建它。

在我本机的D:\itcast\Tool\MySQL\mysql-5.7.24-winx64目录下没有 my.ini 文件，需要先创建一下。创建时要注意文件扩展名，然后再将文件名及后缀修改为 my.ini 。

然后使用文本编辑工具打开 my.ini ，将如下内容复制到文件当中：

[mysql]
default-character-set=utf8

[mysqld]
character-set-server=utf8
default-storage-engine=INNODB
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

1
2
3
4
5
6
7

上面代码的作用就是配置数据库的默认编码字符集为utf-8和默认存储引擎为INNODB。

# 初始化 MySQL

配置好环境变量和 my.ini 文件后，下面来完成 MySQL 的初始化。MySQL 通过在命令提示符(管理员权限)中输入mysqld --initialize-insecure进行初始化，其作用是在不设置 MySQL 管理员账户root密码的情况下对 MySQL 初始化。初始化完成后，在 MySQL 安装根目录下会自动生成 data 目录，该目录中 MySQL 的数据文件，而且我们还可以不使用密码登录 MySQL 服务。

接下来我们来实际操作一下。首先，我们以同样的方式以管理员权限打开命令提示符，输入初始化命令：

特别注意

执行 MySQL 初始化命令，一定要使用管理员权限使用CMD命令提示符，否则可能会出现如下报错信息：

# 注册 MySQL 服务为开机自启

我们个人电脑在安装软件时，一般会提供是否开启启动的可选项，MySQL 也可以进行设置，只不过通过执行名来来进行设置。因为后面我们要经常使用 MySQL，建议大家按照下面的教程来将 MySQL 服务注册为 Windows 系统服务，实现开机自启。

通过 任务管理器 -> 启动 我们可以查看自己电脑有哪些开机启动的软件，而且还可以进行启用、禁用等操作。那如何查看本机启动了哪些服务呢？有三种方式：

方式一： 此电脑 -> 右键 -> 管理 -> 计算机管理(本地) -> 服务和应用程序 -> 服务
方式二： 以管理员权限在命令提示符中输入命令 services.msc
方式三： 使用 Win + r 组合键，输入 services.msc 即可打开服务管理界面

通过上面的方法，我们打开系统服务管理界面，看下当前系统是否存储 MySQL 服务。

接下来我们将 MySQL 服务注册为系统服务，注册 MySQL 服务同样需要以管理员权限打开命令提示符，在命令提示符界面输入如下命令 mysqld -install

至此，我们正式完成了 MySQL 数据库管理系统的安装。

# 启动 MySQL 服务

上面我们已经成功安装了 MySQL，下面我们来介绍下怎么启动 MySQL 服务。启动 MySQL 服务有两个方法：

方法一： 通过以管理员权限打开命令提示符，输入 net start mysql 命令可以启动 MySQL 服务
方法二： 通过系统服务管理界面，选中 MySQL 服务，鼠标右键点击启动即可

当 MySQL 服务启动成功后，我们的电脑就可以称为一台MySQL 数据库服务器了。

# 修改默认账户密码

前面我们提到过通过mysqld --initialize-insecure初始化 MySQL，是不给管理员账户，也就是 root 设置密码的，下面我们登录下 MySQL 数据库管理系统来验证一下。

在命令提示符中(可以不使用管理员权限)输入 mysql -uroot -p，这条命令的作用就是登录 MySQL 数据库管理系统，命令输入后会提示我们输入密码，不用输入密码直接回车即可。

MySQL 数据库是用来存储和管理我们的数据的，如果不设置密码，就没办法保证数据安全。因此，我们要给 root 账户设置密码，通过执行下面的命令即可完成：

mysqladmin -u root password 1234

-u：后面指定要设置密码的用户，也就是 root；
password：后面指定要设置的密码。

# 简单操作 MySQL

# 登录 MySQL

MySQL 安装后的 bin 目录下提供给我们一个工具 mysql.exe，通过它我们可以与 MySQL 数据库管理系统进行交互。我们之前配置环境变量也是为了更方便的使用 bin 目录下的这些工具。我们可以在命令提示符界面输入mysql --help来查看这个工具提供了哪些功能。

大家可以看到罗列出了很多参数项，这里我们不一一介绍，捡取几个重点项来学习下：

-u 或者 --user=name ：登录用户
-p 或者 --password[=name] ：明文指定登录密码，或者采用交互的模式输入密文密码
-h 或者 --host=name ：指定要连接的 MySQL 数据库服务器 IP 地址，不设置默认为本机地址 localhost 或者 127.0.0.1
-P 或者 --port=# ：指定要连接的 MySQL 数据库服务器的端口号，不设置默认为 3306

那登录本机 MySQL 服务的命令就可以这样写：

# 指定明文密码以root用户连接本机MySQL
mysql -uroot -p1234
# 通过交互模式以root用户连接本机MySQL，回车后会提示输入密码
mysql -uroot -p

1
2
3
4

或者

# 指定明文密码以root用户连接本机127.0.0.1，3306端口上的MySQL
mysql -h127.0.0.1 -P3306 -uroot -p1234
# 通过交互模式以root用户连接本机127.0.0.1, 3306端口上的MySQL，回车后会提示输入密码
mysql -h127.0.0.1 -P3306 -uroot -p

1
2
3
4

出现上图所示内容，代表登录成功。

# 退出 MySQL

退出 MySQL 成功后，我们会回到命令提示符界面。

小技巧

在命令提示符界面，MySQL 登录成功后的界面下面，可以通过 $\uparrow$ 来查找之前输入的命令。

# 停止 MySQL 服务

停止 MySQL 服务与启动类似，有两种方式：

方式一：以管理员权限打开命令提示符，在命令提示符界面输入 net stop mysql 停止服务
方式二：通过系统服务管理界面，选中 MySQL 服务右键停止即可

# 卸载 MySQL

停止 MySQL 服务后，即可卸载 MySQL。首先通过管理员权限执行 mysqld -remove mysql 命令将 MySQL 服务从系统服务中删除，然后删除 MySQL 安装目录及环境变量即可。

# MySQL 数据模型

前面介绍 SQL 概念时我们了解到其是操作关系型数据库的一门编程语言。那什么是关系型数据库呢？为了更直观的介绍关系型概念，咱们先看下下面的两张表：

上面的 订单信息表 、客户信息表 都是用怎么经常使用的 Excel 进行编辑的，表格有行、列两个维度，可以很直观的给我们展示订单、客户的详细信息。但是，还有一个缺点，当前形式的表格还没有办法给订单、客户两者之间的关系，比如：想要知道 001 号订单由哪个客户下单，或者 1 号客户都下了哪些订单。因此，我们需要再完善这两个表格，如下：

通过在 订单表 中添加一列 客户编号 我们就可以将订单与客户关联起来。我们就能知道 001 号订单由 1 号客户下单，1 号客户除下单 001 号订单外，还下单了 003 号订单。

上面的 订单表 和 客户表 我们就可以将它们视为有关系的两张二维表。那现在我们就可以回答上面的问题了：关系型数据库是建立在关系模型基础上的数据库，简单地说，关系型数据库是由多张能互相连接的二维表组成的数据库。

介绍完关系型的概念，接下来我们看下 MySQL 的数据模型：

如上图，客户端以 SQL 语言通过数据库管理系统可以创建数据库，在数据库中创建表，在表中添加数据。创建的每一个数据库对应磁盘上的一个文件夹。我们来演示一下：

-- 首先创建一个数据库 db1
create database db1;
-- 在db1下创建一个测试表test
create table test (
  id int,
  name varchar(20)
);

1
2
3
4
5
6
7

上面的 SQL 语句后面我们会详细学习，当前了解作用即可。

在执行创建数据库、数据表之前，我们看下 MySQL 的目录结构：

接下来，我们执行上面的命令看下效果：

登录 MySQL：mysql -uroot -p
创建数据库：create database db1;
我们可以看到 data 目录下创建了一个 db1 文件夹，文件夹下面只有一个 db.opt 的文件
创建 test 表：
```
create table test (
 id int,
 name varchar(20)
);
```
1
2
3
4
我们可以看到 db1 文件下新创建了两个文件 test.frm、test.ibd 。

通过上面的演示，我们可以看到：在 MySQL 中一个数据库对应磁盘上的一个文件夹。数据库下可以创建多张表，每张表在磁盘上有两个文件与之对应。其中，.frm 后缀文件为表结构文件，.ibd 后缀文件为表数据文件，通过这两个文件就可以将快速查询数据并以二维表进行展示。

小结

关系型数据库底层都是用二维表结构，格式一致，易于维护；
可以使用通用的 SQL 语言操作关系型数据库，方便高效，且可以实现复杂的查询逻辑；
MySQL 中可以创建多个数据库，每个数据库对应磁盘上的一个文件夹；
每个数据库可以创建多张表，每张表磁盘上有两个文件：.frm、.ibd 文件与之对应；
每张表可以存储多条数据，数据会被存储到磁盘中的 .ibd 文件中。

# MySQL 数据类型

在学习 SQL 语句之前，我们需要先了解一下 MySQL 的数据类型，因为在创建表时我们要根据需求指定合适的数据类型。

Excel 的使用大家应该都比较熟悉，我们在使用 Excel 时肯定有设置过单元格格式：

MySQL 支持多种类型，大致可以分为三类：数值、日期/时间和字符串类型。

# 数值类型

类型	大小	有符号范围	无符号范围	用途
TINYINT	1 Bytes	(-128，127)	(0，255)	小整数值
SMALLINT	2 Bytes	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 Bytes	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT 或 INTEGER	4 Bytes	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 Bytes	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 Bytes	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度浮点数值
DOUBLE	8 Bytes	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度浮点数值
DECIMAL	取决于 DECIMAL(M, D)的定义，如 M>D，为 M+2 或者为 D+2	依赖于 M 和 D 的值	依赖于 M 和 D 的值	小数值

后面我们经常使用的类型有TINYINT、INT、DOUBLE，所有定义字段的格式与 Java 定义变量的方式刚好相反：字段名字段类型

TINYINT：小整数型，占 1 个字节
INT：大整数型，占 4 个字节，比如定义年龄字段，可以这样写：age int
DOUBLE：双精度浮点型，
- 使用：字段名 double(总长度, 小数点后保留位数)
- 比如定义百分制成绩，保留两位小数，可以这样写：double(5, 2)

# 日期和时间类型

MySQL 日期和时间类型为 DATETIME、DATE、TIMESTAMP、以及 TIME 和 YEAR。

类型	大小(Bytes)	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	'-838:59:59'/'838:59:59'	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	'1000-01-01 00:00:00' 到 '9999-12-31 23:59:59'	YYYY-MM-DD hh:mm:ss	混合日期和时间值
TIMESTAMP	4	'1970-01-01 00:00:01' UTC 到 '2038-01-19 03:14:07' UTC，结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038 年 1 月 19 日凌晨 03:14:07	YYYY-MM-DD hh:mm:ss	混合日期和时间值

经常使用的类型有DATE和DATETIME：

DATE：日期值，包含年月日，比如我们定义生日，可以：birthday date
DATETIME：混合日期和时间值，包含年月日时分秒

# 字符串类型

类型	大小(Bytes)	用途
CHAR	0-255	定长字符串
VARCHAR	0-65535	变长字符串
TINYBLOB	0-255	不超过 255 个字符的二进制字符串
TINYTEXT	0-255	短文本字符串
BLOB	0-65 535	二进制形式的长文本数据
TEXT	0-65 535	长文本数据
MEDIUMBLOB	0-16 777 215	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215	中等长度文本数据
LONGBLOB	0-4 294 967 295	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295	极大文本数据

经常使用的类型为CHAR和VARCHAR：

CHAR：定长字符串
- 优点：存储性能高
- 缺点：浪费空间
- 示例：name char(10)，如果存储的数据字符个数不足 10 个，也会占用 10 个空间
VARCHAR：变长字符串
- 优点：节省空间
- 缺点：存储性能低
- 示例：name varchar(10)，如果存储的数不足 10 个，根据数据的长度来分配空间

# SQL

# SQL 简介

在 SQL 概念章节我们已经介绍过 SQL，下面在简单复述一遍。SQL 不仅是操作关系型数据库的一门编程语言，它实际上定义了操作关系型数据库的一个统一标准。因此，SQL 不仅能操作 MySQL，还可以操作其他类型的关系型数据库，如：Oracle、DB2 等。不过，不同类型的关系型数据库在实现 SQL 标准的前提下还会进行一些扩展，这些扩展的内容我们称之为方言。所以，对于同一个需求，不同的数据库实现方式可能会有一些细微的差别。

# 通用语法

SQL 语句可以单行或多行书写，以分号结尾，语句不以分号结尾，客户端不认为 SQL 语句书写完成，所以不会执行；
SQL 语句不区分大小写，不过关键字建议大写；
SQL 注释分为：
- 单行注释：-- 注释内容 或 MySQL 方言实现的注释 #注释内容；
- 多行注释：/* 注释内容 */，在命令客户端演示效果不佳，后面我们可以在图形化客户端 Navicat 中再演示一遍。

注意

使用 --添加单行注释时，--后面一定要加空格，示例：-- 这是注释，而 MySQL 特有的#没有这个要求。

# SQL 分类

SQL 大致分为四类，下面来逐个介绍一下它们：

DDL(Data Definition Language)：数据定义语言，用来定义数据库对象：数据库，表，列等。简单理解就是用来操作数据库，表的。
DML(Data Manipulation Language)：数据操作语言，用来对表中数据进行增、删、改。
DQL(Data Query Language)：数据查询语言，用来查询数据表中的数据。
DCL(Data Control Language)：数据控制语言，用来定义数据库的访问权限和安全级别，及创建用户。简单理解就是对数据库进行权限控制，比如：让某一个数据库只能让某一个用户进行访问操作等。

# SQL 练习

我们通过 SQL 与数据库管理系统进行交互，那我们肯定需要编写 SQL 语句来告诉 DBMS 以下几个内容：

操作对象是什么？数据库、数据表还是数据；
执行什么操作？增、删、改、查；
操作内容是什么？创建库、修改表、删除数据。

SQL 语句的格式大致上有三个要素组成：操作操作对象操作内容；

# DDL：操作数据库练习

# 查询所有的数据库

操作：show；

操作对象：数据库管理系统所有的数据库，单个数据库为database，那所有的数据库应该以复数形式体现databases；

操作内容：只是查询，不做其他操作。

SQL 语句：

show databases;

这个语句实际上咱们之前已经执行过了，作用就是查询当前数据库管理系统中所有的数据库名称。

# 创建数据库

操作：create；

操作对象：创建单个数据库database；

操作内容：创建数据库，总得指定一个数据库名称。

SQL 语句：

create database 数据库名称;

通过这个语句来创建一个叫db1的数据库：

那这样会产生一个问题，如果指定的数据库名称已经存在了怎么办？理论上讲 DBMS 不应该拒绝执行创建数据库的操作，并提示错误信息。

那有没有一种方式在创建时，如果指定的名称已经存在不执行任何操作，不存在执行创建数据库的操作呢？

SQL 语句：

create database if not exists 数据库名称;

咱们执行一下看看效果：

# 删除数据库

操作：drop；

对象：database；

内容：删除数据库需要指定要删除的数据库名称。

SQL 语句：

drop database 数据库名称;

通过删除语句删除之前创建的db1数据库：

与创建数据库一样，同样有一个问题，如果要删除的库不存在怎么办？参照创建数据库的语法来解决，创建时不存在再创建，删除是不是应该是存在再删除。

SQL 语句：

drop database if exists 数据库名称;

# 使用数据库

在学习如何使用数据库前，我们先介绍一条 SQL 语句，这条 SQL 语句的作用是查询当前正在使用的数据库。

SQL 语句：

select database(); -- 查询当前正在使用的数据库

接下来分析下使用数据库的各组成要素：

操作：use

对象: database，使用的概念只针对数据库，因此可以省略

内容：使用数据库，需要指定响应的数据库名称

SQL 语句:

use 数据库名称;

# DDL：操作数据表练习

# 查询数据库下所有表

操作：show

对象：table，查询所有表就需要以复数形式进行表示tables

内容：只查询所有表名，不做其他操作

SQL 语句：

show tables;

因为我们新创建的数据库db1当中暂时没有创建任何表，所以需要我们切换到mysql库下进行演示：

# 查询指定表的表结构

操作：desc

对象：table 查看表结构的概念只针对表，因此可以省略

内容：查询指定表表结构，需要指定表名

SQL 语句：

desc 表名;

比如，查询mysql库下func表的表结构：desc func;

但是，如果我们要查询的表的字段很多的话，展示就会比较混乱，比如: desc proc;

这种展示效果很不友好，可以使用下面的命令来优化展示效果：

desc 表名称\G

至于\G有什么作用，我们可以通过\h指令来查看帮助信息：

# 创建表

首先我们来分析下创建表需要定义哪些内容：

操作：create

对象：table，与之前的创建数据库类比，在此应需指定表名

内容：创建表与创建库相比要复杂一些，我们在创建 Excel 表格时需要指定列头、单元格格式，那创建表同样需要指定字段名、 字段类型

SQL 语句：

create table 表名 (
  字段名1 数据类型1,
  字段名2 数据类型2,
  字段名3 数据类型3,
  ...
  字段名n 数据类型n
);

1
2
3
4
5
6
7

注意

最后一个定义字段的末尾，不能加逗号

下面我们根据创建表的语句来在db1库下创建如下结构的表

create table tb_user (
  id int,
  username varchar(20),
  password varchar(32) -- 注意不能加逗号
);

1
2
3
4
5

练习完基础的建表语句，接下来结合MySQL 数据类型咱们实现一个较为复杂的案例：

需求：设计一张学生表，请注重数据类型、长度的合理性
  1. 编号
  2. 姓名，姓名最长不超过10个汉字
  3. 性别，因为取值只有两种可能，因此最多一个汉字
  4. 生日，取值为年月日
  5. 入学成绩，小数点后保留两位
  6. 邮件地址，最大长度不超过 64
  7. 家庭联系电话，不一定是手机号码，可能会出现 - 等字符
  8. 学生状态（用数字表示，正常、休学、毕业...）

1
2
3
4
5
6
7
8
9

SQL 语句设计如下：

create table student (
  id int,
  name varchar(10),
  gender char(1),
  birthday date,
  score double(5,2),
  email varchar(15),
  tel varchar(15),
  status tinyint
);

1
2
3
4
5
6
7
8
9
10

# 修改表

数据表有表名、字段名、字段类型等等属性，接下来我们就来实际操作一下针对表各项属性的修改：

修改表名

操作	对象	*内容(修改表名)*
alter	table 表名	rename to 新表名

修改表名 SQL 语句格式：

alter table 表名 rename to 新表名;

-- 将刚才创建的student表名修改为stu
alter table student rename to stu;

1
2
3
4

添加字段

操作	对象	*内容(添加字段)*
alter	table 表名	add 字段名字段数据类型

添加字段 SQL 语句格式：

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

-- 往刚才修改过表名的stu表中添加地址address字段，字段类型为varchar(64)
alter table stu add address varchar(64);

1
2
3
4

修改字段类型

操作	对象	*内容(修改字段类型)*
alter	table 表名	modify 字段名新字段类型

修改字段类型 SQL 语句格式：

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

-- 修改stu表中address地址字段的类型为char(64)
alter table stu modify address char(64);

1
2
3
4

修改字段名和字段类型

操作	对象	*内容(修改字段名和字段类型)*
alter	table 表名	change 字段名新字段名新字段类型

修改字段名和字段类型 SQL 语句格式：

alter table 表名 change 字段名 新字段名 新字段类型;

-- 将stu表中address地址字段及类型修改为addr varchar(64)
alter table stu change address addr varchar(64);

1
2
3
4

删除字段

操作	对象	*内容(删除字段)*
alter	table 表名	drop 字段名

删除字段 SQL 语句格式：

alter table 表名 drop 列名;

-- 删除stu表中addr地址字段
alter table stu drop addr;

1
2
3
4

# 删除表

操作	对象	*内容(删除表)*
drop	table	[ if exists ] 表名

删除表 SQL 语句格式：

-- if exists 可选项：当表存在时删除，不存在时避免报错
drop table [ if exists ] 表名;

-- 删除stu表
drop table stu; -- 或者使用 drop table if exists stu;

1
2
3
4
5

通过上面的学习，在命令行中输入 SQL 语句是不是特别不方便？尤其是在编写复杂语句的时候，那么有没有刚好的客户端工具给我们使用呢？在学习 DML、DQL 前我们就先来了解一款比较强大的图形化 MySQL 客户端连接工具--Navicat。

# DML 练习

DML 主要是对表中数据进行增(insert)、删(delete)、改(update)等操作。

# 添加数据

操作	对象	*内容(删除表)*
insert into	表名	给指定列添加数据、给全部列添加数据、批量添加数据

给指定列添加数据

操作	对象	*内容(指定列添加数据)*
insert into	表名(指定列)	values(设置数据)

给指定列添加数据 SQL 语句格式：

insert into 表名(列名1, 列名2, ..., 列名n) values(值1, 值1, ..., 值n);

给全部列添加数据

给全部列添加数据理论上与给指定列添加数据一样，完全可以通过指定全部列来实现，不过 SQL 提供了一种便捷的方式，就是省略指定列这一部分。但是在实际开发中，不建议省略指定列这一部分内容，因为代码不易读。

操作	对象	*内容(全部列添加数据)*
insert into	表名	values(设置全部列数据)

insert into 表名 values(值1, 值2, ..., 值n);

批量添加数据

操作	对象	*内容(批量添加数据)*
insert into	表名(指定列)	values(设置指定列数据 1), (设置指定列数据 2), ..., (设置指定列数据 n)
insert into	表名	values(设置全部列数据 1), (设置全部列数据 2), ..., (设置全部列数据 n)

需求：使用咱们之前设计的 学生表 的建表语句建表，并完成如下操作
  1. 添加学生张三，该学生我们只了解到学生编号为1，姓名为张三，其他数据未知；
  2. 添加学生李四，该学生编号为2，姓名李四，性别男，生日1997-09-07，成绩88.88，邮箱lisi@itcast.cn，手机号码13112341234，状态正常
  3. 添加王五，赵六，刘七三个学生的信息，学生编号分别为3,4,5，性别都为男，状态为休学，其他数据未知。

1
2
3
4

为了演示效果，确认增删改等操作是否成功，我们先给出一条查询表总所有数据的 SQL 语句，后面我们在学习 DQL 的时候会再介绍：

-- 查询 stu 表中所有的数据
select * from stu;

1
2

select * from stu; -- 查询stu表中所有数据

/*
  添加学生张三的数据
  id: 1 name: 张三
*/
insert into stu(id, name) values(1, '张三');

/*
  添加学生李四的数据
  id: 2 name: 李四 gender: 男 birthday: 1997-09-07 score: 88.88 email: lisi@itcast.cn tel: 13112341234 status: 2
*/
insert into stu values(2, '李四', '男', '1997-09-07', 88.88, 'lisi@itcast.cn', '13112341234', 1);

/*
  添加王五、赵六、刘七三个学生的数据
  id: 3 name: 王五 gender: 男 status: 2
  id: 4 name: 赵六 gender: 男 status: 2
  id: 5 name: 刘七 gender: 男 status: 2
*/
insert into stu(id, name, gender, status) values(3, '王五', '男', 2), (4, '赵六', '男', 2), (5, '刘七', '男', 2);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

# 修改数据

操作	对象	*内容(修改数据)*
update	表名	需要指定修改哪些列，哪些行的数据

修改数据 SQL 语句格式：

-- set 指定列
-- where 限定行 []括起来的部分可以省略
update 表名 set 列名1 = 值1, 列名2 = 值2, ..., 列名n = 值n [ where 限定条件 ];

1
2
3

练习：

修改张三性别为女
修改张三生日为 1999-12-12，分数为 99.99

select * from stu; -- 查询stu表所有数据

-- 修改张三性别为女
update stu set gender = '女' where name = '张三';

-- 修改张三生日为1999-12-12，分数为99.99
update stu set birthday = '1999-12-12', score = 99.99 where name = '张三';

1
2
3
4
5
6
7

特别注意

修改语句如果不加条件，则将所行的数据都修改, 如 update stu set gender = '女';将修改所有学生的性别为女。

# 删除数据

操作	对象	*内容(删除数据)*
delete from	表名	需要指定删除哪些行的数据

删除数据 SQL 语句格式：

delete from 表名 [ where 限定条件 ];

练习：

删除学生张三
删除 stu 表中所有数据

select * from stu; -- 查询stu表所有数据

-- 删除张三
delete from stu where name = '张三';

-- 删除所有数据
delete from stu;

1
2
3
4
5
6
7

# DQL 练习

下面是我之间做的一个系统页面：

页面上展示的数据肯定是在数据库中的项目表中进行存储的，而我们需要将数据库中的数据查询出来并展示给用户看。上图中是最基本的查询效果，实际上表中的数据有很多，不可能将所有的数据显示到一个页面上，因此我们要添加分页的效果，上图我们也可以看到默认是通过时间倒序排列的。

从上面的例子我们可以看出，对于数据库的查询灵活多变，需要根据具体的需求来实现，而数据库查询操作也是最重要的操作，此部分需要我们重点掌握。

为了方便我们演示查询效果，首先需要准备表及一些数据：

-- 删除stu表
drop table if exists stu;


-- 创建stu表
CREATE TABLE stu (
 id int, -- 编号
 name varchar(20), -- 姓名
 age int, -- 年龄
 sex varchar(5), -- 性别
 address varchar(100), -- 地址
 math double(5,2), -- 数学成绩
 english double(5,2), -- 英语成绩
 hire_date date -- 入学时间
);

-- 添加数据
INSERT INTO stu(id,NAME,age,sex,address,math,english,hire_date)
VALUES
(1,'马运',55,'男','杭州',66,78,'1995-09-01'),
(2,'马花疼',45,'女','深圳',98,87,'1998-09-01'),
(3,'马斯克',55,'男','香港',56,77,'1999-09-02'),
(4,'柳白',20,'女','湖南',76,65,'1997-09-05'),
(5,'柳青',20,'男','湖南',86,NULL,'1998-09-01'),
(6,'刘德花',57,'男','香港',99,99,'1998-09-01'),
(7,'张学右',22,'女','香港',99,99,'1998-09-01'),
(8,'德玛西亚',18,'男','南京',56,65,'1994-09-02');

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

接下来咱们从最基本的查询语句开始学起。

# 基础查询

我们继续使用 SQL 语句组成要素来分析下基础查询的语法结构：

操作(查询)	内容(查询内容)	对象(表)
select	指定列	from 表名

基础查询 SQL 语法：

select [ distinct ] 字段1 [ as 字段别名 ], 字段2, ..., 字段n from 表名;
select * from 表名; -- 查询所有数据

1
2

练习：

查询 age、sex 两列

select age, sex from stu;

上面我们只查询了两列数据，如果一张表有几十甚至上百列，我们要查询所有列的数据时该怎么办？是否有更便捷的方式查询所有列的数据？有，查询所有列的数据，字段列表可以使用*代替

select * from stu;

不知道大家有没有发现，在查询 age、sex 两列数据的时有重复数据出现，那能否查询时不展示重复的数据呢？

也是有的，可以通过在字段列表前添加distinct关键字进行过滤
```
select distinct age, sex from stu;
```
1
大家在操作 Excel 表格时，如果列头定义的不合适，我们可以直接修改，那 SQL 有没有办法实现类似的效果呢？
```
-- 为列定义别名，as 可以省略
select age as 年龄, sex 性别 from stu;
```
1
2

注意

查询所有列数据使用的*在实际开发项目时不建议使用，一是这样写不方便我们阅读 SQL 语句，二是阿里制定的开发手册当中有明确要求，所以大部分企业都会采纳这种要求：

不过在我们使用客户端工具，或者在练习过程中，为了节约时间，我们一般都会使用*来查询所有列数据。

# 条件查询

操作(查询)	内容(查询内容)	对象(表)	查询范围(限定条件)
select	指定列	from 表名	where 条件列表

条件查询 SQL 语法格式：

select 字段列表 from 表名 where 条件列表;

条件：条件列表中可以使用下面的运算符

符号	功能
>	大于
<	小于
>=	大于等于
<=	小于等于
=	等于
<>或!=	不等于
between ... and ...	在某个范围之内(都包含)
in (...)	多选一
like 占位符	模糊查询，`_` 匹配单个任意字符，`%` 匹配多个任意字符
is null	判断是否为 null
is not null	与`is null`相反
and 或 &&	逻辑运算，并且
or 或 \|\|	逻辑运算，或者
not 或 !	逻辑运算，非

练习：

查询年龄大于 20 岁的学生

select * from stu where age > 20;

查询年龄大于等于 20 岁的学员

select * from stu where age >= 20;

查询年龄大于等于 20 岁并且年龄小于等于 30 岁的学员信息

/*
  && 和 and 都表示并且的意思，建议使用 and，方便阅读
*/
select * from stu where age >= 20 &&  age <= 30;
select * from stu where age >= 20 and  age <= 30;

/*
  当然也可以用 between ... and ... 来实现
*/
select * from stu where age BETWEEN 20 and 30;

1
2
3
4
5
6
7
8
9
10

查询入学日期在'1998-09-01' 到 '1999-09-01' 之间的学员信息

select * from stu where hire_date BETWEEN '1998-09-01' and '1999-09-01';

查询年龄等于 18 岁的学员信息

select * from stu where age = 18;

查询年龄不等于 18 岁的学员信息

/*
  建议使用 != 方便阅读
*/
select * from stu where age != 18;
select * from stu where age <> 18;

1
2
3
4
5

查询年龄等于 18 岁或者年龄等于 20 岁或者年龄等于 22 岁的学员信息

select * from stu where age = 18 or age = 20 or age = 22;
select * from stu where age in (18,20 ,22);

1
2

查询英语成绩为 null 的学员信息，null 值的比较不能使用 = 或者 != 。需要使用 is 或者 is not

select * from stu where english = null; -- 这个语句是不行的
select * from stu where english is null;
select * from stu where english is not null;

1
2
3

# 模糊查询

模糊查询使用like关键字，可以使用通配符进行占位：

_：代表单个任意字符

%：代表任意个数字符

练习：

查询姓马的学员信息

select * from stu where name like '马%';

查询姓名第二个字是花的学员信息

select * from stu where name like '_花%';

查询名字中包含德字的学员

select * from stu where name like '%德%';

# 排序查询

操作(查询)	内容(查询内容)	对象(表)	查询范围(限定条件)(可选)	排序(排序字段排序方式)
select	指定列	from 表名	[ where 条件列表 ]	order by 排序方式

排序 SQL 语法格式：

select 字段列表 from 表名 [ where 条件列表 ] order by 排序方式;

语法格式中的排序方式有两种：升序、降序

ASC：升序 (默认)
DESC：降序

注意

如果有多个排序条件，当前边的条件值一样时，才会根据第二条进行排序。

练习：

查询所有学员信息并根据年龄倒序

select * from stu order by age desc;

查询年龄大于 30 的学员，并根据数学成绩升序

select * from stu where age > 30 order by math;
select * from stu where age > 30 order by math asc;

1
2

查询男性，首先数学成绩降序排列，如果数学成绩一样，根据英语成绩升序排列

select * from stu where sex = '男' order by math desc, english;

# 聚合函数

在学习聚合函数之前，我们来先看一个 Excel 表格：

上图我们在 Excel 表格中通过使用函数计算了 最小年龄、最大年龄、数学平均分、数学总分、男生数学平均分、女生数学平均分、 英语总分、总人数 等数据。那通过 SQL 我们能否实现同样的效果呢？实际上 SQL 给我们提供了一些与 Excel 函数类似的聚合函数，通过使用聚合函数我们就可以完成以上的计算。

概念：聚合函数就是将一列数据作为一个整体，进行纵向运算的函数。

分类：

函数名	功能
count(列名)	统计数量(一般选用不为 null 的列，因为 null 值不参与任何聚合函数的运算)
max(列名)	最大值
min(列名)	最小值
sum(列名)	求和
avg(列名)	平均值

语法格式：

操作(查询)	内容(查询内容)	对象(表)	查询范围(限定条件)(可选)
select	聚合函数(列名)	from 表名	[ where 条件列表 ]

select 聚合函数(列名) from 表名 [ where 条件列表 ];

练习：

统计一共有多少学生

select count(id) from stu;
select count(english) from stu; -- 该条语句执行结果比 count(id) 结果少1，因为有一学员英语成绩为null

1
2

上面语句根据某个字段进行统计，如果该字段某一行的值为 null 的话，将不会被统计。可以通过 count(*) 来实现。* 表示所有字段数据，一行中也不可能所有的数据都为null，所以建议使用 count(*)。该建议在阿里巴巴开发手册中也有提及：

select count(*) from stu;

查询数学成绩的最高分

select max(math) from stu;

查询数学成绩的最低分

select min(math) from stu;

查询数学成绩的总分

select sum(math) from stu;

查询数学成绩的平均分，男、女数学成绩平均分

select avg(math) from stu;
select avg(math) from stu where sex = '男';
select avg(math) from stu where sex = '女';

1
2
3

上面查询男、女数学成绩平均分时执行了两条 SQL 语句，那能否简化为一条语句呢？接下来我们就来学习下分组查询。

# 分组查询

分组查询结合聚合函数，可以实现更复杂一些的需求，比如：分别统计男、女学员人数，分别计算男、女学员数学平均分等。

语法格式:

select 字段列表 from 表名 [ where 分组前限定条件 ] group by 分组字段名 [ having 分组后限定条件 ];

练习：

查询男、女学员各自的数学平均分

select sex, avg(math) from stu group by sex;

-- 分组之后，查询的字段为聚合函数和分组字段，查询其他字段无任何意义
select name, sex, avg(math) from stu group by sex;

1
2
3
4

注意

分组之后，查询的字段为聚合函数和分组字段，查询其他字段无任何意义

查询男同学和女同学各自的数学平均分，以及各自人数

select sex, avg(math), count(*) from stu group by sex;

查询男同学和女同学各自的数学平均分，以及各自人数，要求：分数低于 70 分的不参与分组

select sex, avg(math), count(*) from stu where math > 70 group by sex;

查询男同学和女同学各自的数学平均分，以及各自人数，要求：分数低于 70 分的不参与分组，分组之后人数大于 2 个的

select sex, avg(math), count(*) from stu where math > 70 group by sex having count(*) > 2;

where 和 having 的区别

执行时机不一样：where 是分组之前进行限定，不满足 where 条件，则不参与分组，而 having 是分组之后对结果进行过滤。
可判断的条件不一样：where 不能对聚合函数进行判断，having 可以。

# 分页查询

分页查询就是将数据一页一页的展示给用户，用户也可以通过点击查看下一页的数据。我们在很多网站应该都见过类似的效果。

就比如我们的stu表，假设我们现在针对stu表里面的数据分页查询，每页我们指定显示 3 条数据，那 8 条数据我们应该分为 3 页，为了方便找规律，在 Excel 表格里面模拟stu表的数据，并添加一索引列，索引从 0 开始，如下图：

从图中我们可以看到：

第一页：索引为 0 ~ 2
第二页：索引为 3 ~ 5
第三页：索引为 6 ~ 7 因为第三页只有两条数据

接下来我们来看下分页查询的语法格式：

-- 起始索引是从0开始的
select 字段列表 from 表名 limit 起始索引, 每页数量;

1
2

练习：

查询前 3 条数据

select * from stu limit 0, 3;

每页显示 3 条，查询第一页数据

select * from stu limit 0, 3;

每页 3 条，查询第二页数据

select * from stu limit 3, 3;

每页 3 条，查询第三也数据

select * from stu limit 6, 3;

总结

pageNo: 页码 pageSize: 每页显示数据量 pageIndex: 每页起始索引 pageIndex = (pageNo - 1) * pageSize

小结

DQL 查询语句，我们学习了基础查询、条件查询、模糊查询(实际上是一种特殊的条件查询)、排序、聚合函数、分组查询、分页查询。我们来总结一下 DQL 语句的完整语法结构：

select
    字段列表
from
    表名列表
where
    条件列表
group by
    分组字段
having
    分组后条件
order by
    排序字段
limit
    分页限定

1
2
3
4
5
6
7
8
9
10
11
12
13
14

# Navicat 工具

# Navicat 概述

Navicat for MySQL 是管理和开发 MySQL 或 MariaDB 的理想解决方案；
这套全面的客户端工具为数据库管理、开发和维护提供了一款直观而强大的图形界面；
官网： http://www.navicat.com.cn。

# Navicat 安装

安装

双击navicat111_mysql_cs_x86.exe ，然后一路下一步，安装成功。

激活

双击PatchNavicat.exe，选择安装目录中的navicat.exe ，完成激活。 navicat 默认安装路径：C:\Program Files (x86)\PremiumSoft\Navicat for MySQL

# Navicat 使用

# 连接 MySQL

点击连接，选择 MySQL
填写要连接数据库的信息

以上操作没有问题的话，点击确定即可连接成功。

# 使用 Navicat 操作 MySQL

连接成功后，就可以看到下面的界面：

修改表结构：按照下图操作可修改表结构

点击 设计表 后即出现下图所示界面，在图中红框标记的地方可直接修改字段名，类型等信息：
编写 SQL 语句并执行：按照下图操作书写 SQL 语句并执行