了解MySQL，一个开源免费的关系型数据库，掌握其登录、基本使用、数据库与表的创建及索引应用，学习如何通过SQL执行增删改查操作，并探索Sequelize ORM框架在Node.js中的数据库操作应用。
数据库（一）：MySQL
MySQL 是一款开源免费的关系型数据库，特点如下：
- 每个表都是独立的
- 表和表之间通过公共字段来建立关系
下载地址：MySQL Community Downloads
- 自定义
- 版本号
- 路径
- 账号/密码
- 环境变量
基本使用
通常情况下，MySQL 被安装在远程服务器上，所以必须连接到远程服务器并登录，才可以使用 MySQL，格式如下：
mysql -h <serverIP> -P<port> -u<username> -p<password>
示例如下：
mysql -h 127.0.0.1 -P3306 -uroot -p1234
如果 MySQL 服务器在本地，且默认使用 3306 端口号，那么主机地址（-h）和端口号（-P）可以省略，示例如下：
mysql -uroot -p1234
如果在参数 -p 之后未输入任何内容，那么将以隐藏密码的方式登录，示例如下：
mysql -uroot -p
综上所述，最常用的登录远程 MySQL 服务器（端口号：3306）时的指令格式如下：
mysql -h <serverIP> -uroot -p
此外，可以通过
exit;、quit和\q中的任何一个指令退出 MySQL 连接
在登录 MySQL 数据库后，可以通过 show databases; 指令查看所有数据库，输出内容如下：
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
以上所有的数据库均为系统数据库，内容如下所示：
| 数据库 | 内容 |
|---|---|
| information_schema | MySQL 服务器所维护的所有另外数据库的信息 |
| mysql | MySQL 服务器的用户名、密码和权限等信息 |
| performance_schema | MySQL 服务器性能相关的参数 |
| sys | 以视图的形式将 info_schema 和 perf_schema 合并，从而使数据更容易让人理解 |
基本概念
任何一个数据库的核心内容即为增删改查（CRUD）
- 数据库
- 表
- 字段、记录
MySQL 利用 SQL（Structured Query Language）语言实现此功能
SQL 语言是关系型数据库的通用语言，各个厂商通常会在标准 SQL 的基础之上扩展独特的内容，例如 SQL Server 实际上使用的是在 SQL 基础之上扩展的 T-SQL
SQL 语句以功能可以划分为如下类别：
| 功能 | 名称 | 含义 |
|---|---|---|
| 数据定义 | DDL | 定义实例（库、表、字段等） |
| 数据操纵 | DML | 操纵记录 |
| 数据查询 | DQL | 查询数据 |
| 数据控制 | DCL | 访问权限、安全级别 |
SQL 语言中常用的数据类型如下所示：
| 数据类型 | 含义 |
|---|---|
| double | 浮点数 |
| char | 固定长度字符串 |
| varchar | 可变长度字符串 |
| text | 字符串 |
| blob | 二进制 |
| date | 日期 |
| time | 时间 |
| datetime | 日期/时间 |
数据库
创建
格式如下：
create database [if not exists] <DBName> [charset=<utf8/gbk>];
示例如下：
create database db01;
上述示例为创建数据库最简单的方式，缺点在于如果创建已存在的数据库，那么将出现错误，此时可以使用 if not exists 约束，示例如下：
create database if not exists db01;
输出内容如下：
Query OK, 1 row affected, 1 warning (0.01 sec)
上述示例的含义是如果存在
db01数据库，那么自动忽略此语句，如果不存在则创建数据库，此外，在创建数据库时可以指定字符集，默认的字符集为utf8，示例如下：
create database if not exists db02 charset='gbk';
此时可以通过如下语句查看字符集：
show create database db02;
输出内容如下：
+----------+-------------------------------------------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------------------------------------------+
| db02 | CREATE DATABASE `db02` /*!40100 DEFAULT CHARACTER SET gbk */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+-------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
另外，如果创建数据库时以关键字或特殊字符命名，那么必须将数据库名称以 ` 字符包围，示例如下：
create database `create`;
查询
查看数据库的格式如下：
- 所有数据库
show databases;
- 某个数据库
show create database <DBName>;
示例如下：
show create database db01;
输入内容如下：
+----------+--------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------------------------------------------------------------------------+
| db01 | CREATE DATABASE `db01` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+--------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
删除
格式如下：
drop database [if exists] <DBName>;
示例如下：
drop database db02;
类似于创建数据库，if exists 表示如果待删除数据库存在则删除，否则自动忽略此语句，示例如下：
drop database if exists db02;
输出内容如下：
Query OK, 0 rows affected, 1 warning (0.01 sec)
修改
MySQL 中可以修改数据库的字符集，格式如下：
alter database <DBName> charset=<utf8/gbk>;
示例如下：
alter database db01 charset=gbk;
表
数据库中的每一张表都存储在某个具体的数据库中，所以在操作某个表之前，必须切换到表所在的数据库，MySQL 中切换数据库的格式如下：
use <DBName>;
示例如下：
use db01;
输出内容如下：
Database changed
创建
格式如下：
create table [if not exists] <TableName> (
FieldName DataType,
FieldName DataType,
FieldName DataType,
...
FieldName DataType,
FieldName DataType
);
if not exists 含义表示如果当前数据库中不存在待创建的表则创建，否则跳过此语句
示例如下：
create table if not exists student (
id int,
name text
);
必须注意的是，最后一个字段末尾不能写
,字符，此时若想查看创建的表，可以通过如下命令：
show tables;
示例如下：
show tables;
输入内容如下：
+----------------+
| Tables_in_db01 |
+----------------+
| student |
+----------------+
1 row in set (0.00 sec)
存储引擎
在 MySQL 中创建表时，可以指定存储引擎类型，格式如下：
create table <TableName> (
FieldName DataType,
FieldName DataType,
...
FieldName DataType
) engine=<EngineName>;
示例如下：
create talbe stu (
id int,
name text
) engine=MyISAM;
MySQL 中存储引擎的相关内容如下所示：
| 存储引擎 | 特点 |
|---|---|
| MyISAM | 安全性低、不提供事务和外键 |
| InnoDB | 安全性高、提供事务和外键 |
| Memory | 访问速度极快、不会永久存储数据 |
默认情况下，表的存储引擎为 InnoDB，实际上，数据库的本质是一个文件，在创建数据库时，系统将自动创建一个目录，在数据库中创建表时，系统将自动创建一系列文件，不同的存储引擎有不同的文件结构：
- InnoDB
- 如果以 InnoDB 为存储引擎，那么在创建表时系统将自动创建以 .ibd 为扩展名的文件，此文件保存了表的结构
- 如果向此表中存储数据，那么数据将被存储在名称为 ibdata1 的文件中，如果数据较多，系统将自动创建 ibdata2 以存储更多的数据
- MyISAM
- 如果以 MyISAM 为存储引擎，那么在创建表时系统将自动创建如下文件：
- .sdi
- 表的结构
- .MYD
- 表的数据
- .MYI
- 表的索引
- .sdi
- 如果以 MyISAM 为存储引擎，那么在创建表时系统将自动创建如下文件：
- Memory
- 如果以 Memory 为存储引擎，那么在创建表时将自动创建以 .sdi 为扩展名的文件，此文件保存了表的结构
- 必须注意的是，以 Memory 为存储引擎的表的数据不会保存到文件中，而是将直接被保存到内存中，此外，Memory 存储引擎不支持 text/blob 数据类型
查询
除了可以查询当前数据库中存在哪些表，也可以查看表中的具体结构，格式如下：
desc <TableName>;
示例如下：
desc student;
输出内容如下：
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | text | YES | | NULL | |
+-------+------+------+-----+---------+-------+
2 rows in set (0.01 sec)
删除
格式如下：
drop table [if exists] <TableName>;
示例如下：
drop table if exists student;
此时数据库中已不存在表，查看表后的内容如下：
Empty set (0.00 sec)
修改
数据库 db01 最初的样式如下所示：
+----------------+
| Tables_in_db01 |
+----------------+
| student |
+----------------+
1 row in set (0.00 sec)
表 student 最初的样式如下所示：
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | text | YES | | NULL | |
+-------+------+------+-----+---------+-------+
2 rows in set (0.00 sec)
- 修改表名
格式如下：
rename table <OriginalTableName> to <NewTableName>;
示例如下：
rename table student to person;
输出内容如下：
+----------------+
| Tables_in_db01 |
+----------------+
| person |
+----------------+
1 row in set (0.00 sec)
- 添加字段
格式如下：
alter table <TableName> add <NewFieldName> <NewFieldDataType> [Location];
示例如下：
alter table person add age int;
输出内容如下：
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | text | YES | | NULL | |
| age | int | YES | | NULL | |
+-------+------+------+-----+---------+-------+
3 rows in set (0.00 sec)
默认情况下，新添加的字段位于表的末尾，如果让新增字段位于第一行，可以设置可选参数 Location 为 first 实现，示例如下：
alter table person add score float first;
输出内容如下：
+-------+-------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------+------+-----+---------+-------+
| score | float | YES | | NULL | |
| id | int | YES | | NULL | |
| name | text | YES | | NULL | |
| age | int | YES | | NULL | |
+-------+-------+------+-----+---------+-------+
4 rows in set (0.00 sec)
如果让新增字段位于某一行之后，可以设置可选参数 Location 为 after (FieldName) 实现，示例如下：
alter table person add phone int after name;
输出内容如下：
+-------+-------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------+------+-----+---------+-------+
| score | float | YES | | NULL | |
| id | int | YES | | NULL | |
| name | text | YES | | NULL | |
| phone | int | YES | | NULL | |
| age | int | YES | | NULL | |
+-------+-------+------+-----+---------+-------+
5 rows in set (0.00 sec)
- 删除字段
格式如下：
alter table <TableName> drop <FieldName>;
示例如下：
alter table person drop phone;
输出内容如下：
+-------+-------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------+------+-----+---------+-------+
| score | float | YES | | NULL | |
| id | int | YES | | NULL | |
| name | text | YES | | NULL | |
| age | int | YES | | NULL | |
+-------+-------+------+-----+---------+-------+
4 rows in set (0.00 sec)
-
修改字段
- 数据类型
格式如下：
alter table <TableName> modify <FieldName> <NewDataType>;示例如下：
alter table person modify score double;输出内容如下：
+-------+--------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------+------+-----+---------+-------+ | score | double | YES | | NULL | | | id | int | YES | | NULL | | | name | text | YES | | NULL | | | age | int | YES | | NULL | | +-------+--------+------+-----+---------+-------+ 4 rows in set (0.00 sec)- 名称 & 数据类型
格式如下：
alter table <TableName> change <OriginalFieldName> <NewFieldName> <NewDataType>;示例如下：
alter table person change age address text;输出内容如下：
+---------+--------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+--------+------+-----+---------+-------+ | score | double | YES | | NULL | | | id | int | YES | | NULL | | | name | text | YES | | NULL | | | address | text | YES | | NULL | | +---------+--------+------+-----+---------+-------+ 4 rows in set (0.00 sec) -
修改引擎
格式如下：
alter table <TableName> engine=<EngineName>;
示例如下：
alter table person engine=MyISAM;
记录
记录相关的表的结构如下所示：
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | text | YES | | NULL | |
| age | int | YES | | NULL | |
+-------+------+------+-----+---------+-------+
3 rows in set (0.00 sec)
创建
格式如下：
insert into <TableName> (FieldName-1, FieldName-2, ..., FieldName-N) values (Value-1, Value-2, ..., Value-N) ... (Value-1, Value-2, ..., Value-N);
示例如下：
insert into student (id, name, age) values (1, 'reyn', 21);
此时若想查看创建的记录，可以通过如下命令：
select * from student;
输出内容如下：
+------+------+------+
| id | name | age |
+------+------+------+
| 1 | reyn | 21 |
+------+------+------+
1 row in set (0.00 sec)
实际上，在创建记录时，若添加数据时的字段顺序和表的结构相同，那么可以省略字段说明，示例如下：
insert into student values (2, 'lily', 20);
输出内容如下：
+------+------+------+
| id | name | age |
+------+------+------+
| 1 | reyn | 21 |
| 2 | lily | 20 |
+------+------+------+
2 rows in set (0.00 sec)
此外，在创建记录时，可以同时添加多条记录，示例如下：
insert into student values (3, 'jack', 22), (4, 'jhon', 21);
输出内容如下：
+------+------+------+
| id | name | age |
+------+------+------+
| 1 | reyn | 21 |
| 2 | lily | 20 |
| 3 | jack | 22 |
| 4 | jhon | 21 |
+------+------+------+
4 rows in set (0.00 sec)
实际上，在创建记录时，字段的说明顺序可以和表的结构不一致，但记录值的顺序必须和字段的说明顺序一致，示例如下：
insert into student (name, age, id) values ('jobs', 19, 5);
输出内容如下：
+------+------+------+
| id | name | age |
+------+------+------+
| 1 | reyn | 21 |
| 2 | lily | 20 |
| 3 | jack | 22 |
| 4 | jhon | 21 |
| 5 | jobs | 19 |
+------+------+------+
5 rows in set (0.00 sec)
若不一致将出现错误，示例如下：
insert into student (name, age, id) values (6, 'miles', 25);
输出内容如下：
ERROR 1366 (HY000): Incorrect integer value: 'miles' for column 'age' at row 1
查询
除了可以查询一个表中的所有数据之外，也可以查询某个或某些在具体条件下的特定字段的取值，格式如下：
select (FieldName-1, FieldName-2, ... FieldName-N) from <TableName> [where <Condition>]
示例如下：
select name, age from student;
输出内容如下：
+------+------+
| name | age |
+------+------+
| reyn | 21 |
| lily | 20 |
| jack | 22 |
| jhon | 21 |
| jobs | 19 |
+------+------+
5 rows in set (0.00 sec)
实际上，可以通过 where 选项附加条件以查询更加准确的内容，条件通常由字段名称和取值组成，由关键字和逻辑运算符连接，如下所示：
| 连接符 | 含义 |
|---|---|
= |
等于 |
!= |
不等于 |
<> |
不等于 |
< |
小于 |
<= |
小于等于 |
> |
大于 |
>= |
大于等于 |
in(...) |
是否在特定集合中 |
between ... and ... |
是否在某个区间 |
and |
逻辑与 |
or |
逻辑或 |
not |
逻辑非 |
like |
模糊查询 |
is null |
空 |
is not null |
非空 |
查询原理是遍历表中的所有记录，判断记录是否满足 where 附加条件，如果结果为真，那么将记录添加到查询结果，示例如下：
- 查询姓名为 reyn 的学生
select * from student where name='reyn';
输出内容如下：
+------+------+------+
| id | name | age |
+------+------+------+
| 1 | reyn | 21 |
+------+------+------+
1 row in set (0.00 sec)
- 查询年龄大于等于 20 岁的学生姓名
select name from student where age>=20;
输出内容如下：
+------+
| name |
+------+
| reyn |
| lily |
| jack |
| jhon |
+------+
4 rows in set (0.04 sec)
- 查询年龄在 18 到 21 岁的所有学生姓名及年龄
select name,age from student where age between 18 and 21;
输出内容如下：
+------+------+
| name | age |
+------+------+
| reyn | 21 |
| lily | 20 |
| jhon | 21 |
| jobs | 19 |
+------+------+
4 rows in set (0.01 sec)
- 查询年龄为 21 且姓名为 jhon 的学生
select * from student where age=21 and name='jhon';
输出内容如下：
+------+------+------+
| id | name | age |
+------+------+------+
| 4 |
最低0.47元/天 解锁文章
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
