MYSQL原理、设计与应用

概述

数据库(Database，DB)是按照数据结构来组织、存储和管理数据的仓库，其本身可被看作电子化的文件柜，用户可以对文件中的数据进行增删改查等操作。

数据库系统是指在计算机系统中引入数据库后的系统，除了数据库，还包括数据库管理系统(Database Management System，DBMS)、数据库应用程序。

数据库技术的发展

🐬人工管理阶段

• 数据不在计算机中长期保存
• 没有专门的数据管理软件，数据需要应用程序自己管理
• 数据是面向应用程序的，不同的应用程序之间无法共享数据
• 数据不具有独立性，完全依赖于应用程序

🐬文件系统阶段

• 数据可以在计算机外存设备上长期保存，可以对数据反复进行操作
• 通过文件系统管理数据，文件系统提供了文件管理功能和存取方法
• 在一定程度上实现了数据独立性和共享性，但非常的薄弱

🐬数据库系统阶段

• 数据结构化
• 数据共享
• 数据独立性高
• 数据统一管理与控制

三级模式

美国国家标准协会(American National Standards Institute，ANSI)所属的标准计划与需求委员会(Standards Planning and Requirements Committee，SPARC)在1971年公布的研究报告中提出了 ANSI-SPARC体系结构，即三级模式结构(或三层体系结构)。

ANSI-SPARC最终没有成为正式标准，但它仍然是理解数据库管理系统的基础。

三级模式是指数据库管理系统从三个层次来管理数据，分别是外部层(External Level)、概念层(Conceptual Level)和内部层(Internal Level)。

这三个层次分别对应三种不同的模式，分别是外模式、概念模式和内模式。

在外模式与概念模式之间，以及概念模式与内模式之间，还存在映像，即二级映像。

外模式面向应用程序，描述用户的数据视图；内模式(又称物理模式、存储模式)面向物理上的数据库，描述数据在磁盘中如何存储；概念模式(又称为模式、逻辑模式)面向数据库设计人员，描述数据的整体逻辑结构。

由于三级模式比较抽象，为了好理解，假设有一个电子表格来保存商品信息表，如下：

🐬概念模式

概念模式类似于表格的列，他描述了商品表中包含哪些信息。

概念模式在数据库中描述的信息还有很多，如多张表之间的联系，表中每一列的数据类型和长度等。

🐬内模式

Excel表格另存为文件时，可以选择保存的文件路径、保存类型等，这些与存储想关的描述信息相当于内模式。

在数据库中内模式描述数据的物理结构和存储方式，如堆文件、索引文件、散列(Hash)文件等。

🐬外模式

在打开一个电子表格会显示表格中全部的数据，将数据提供给用户时，出于权限，安全控制等因素考虑，只允许用户看到一部分数据，或不同用户看到不同的数据，这样的需求就可以用视图来实现。视图和基本表的关系如下：

基本表中的数据是实际储存在数据库中的，而视图中的数据是查询或计算出来的。由此可见，外模式可以为不同的用户需求创建不同的视图，由于不同的用户需求不同，数据显示方式也会多种多样。

一个数据库中会有多个外模式，而概念模式和内模式则只有一个

二级映像

三级模式是数据的三个抽象级别，每个级别关系的重点不同。为了使三级模式之间产生关联，数据库管理系统在三级模式之间提供了二级映像功能。

二级映像是一种规则，它规定了映像双方如何进行转换。通过二级映像，体现了逻辑和物理两个层面的数据独立性。

🐬逻辑独立性

外模式/概念模式映像体现了逻辑独立性。逻辑独立性是指当修改了概念模式，不影响上一层的外模式。

例如，将表库存和销量拆分到另一张表中，此时概念模式发生了更改，但可以通过改变外模式/概念模式的映像，继续为用户提供原有视图。

由此可见，逻辑独立性能够让使用视图的用户感觉不到基本表的改变。

🐬物理独立性

概念模式/内模式映像体现了物理独立性。物理独立性是指修改了内模式，不影响上层的概念模式和外模式。

例如，在 Excel 中将 .xls 文件另存为 .xlsx 文件，虽然文件格式变了，但打开文件后显示的表格内容一般不会发生改变。

在数据中，更换了更先进的存储结构，或者创建索引来加快查询速度，内模式会发生改变。此时，只需要改变概念模式/内模式映像，就不会影响到原有的概念模式。

物理独立性使得用户不必了解数据库内部的存储原理，即可正常使用数据库来保存数据。数据库管理系统会自动将用户的操作转换成物理级数据库的操作。

数据库相关的人员

数据库系统涉及一些人员，主要包括数据库管理员(Database Administrator,DBA)、应用程序员(Application Programmer)和最终用户(End User)。

🐬数据库管理员

数据库管理员负责管理和维护数据库，参与数据库的设计、测试和部暑。

🐬应用程序员

应用程序员负责为最终用户设计和编写程序，并进行调试和安装，以便最终用户利用应用程序来对数据库进行存取操作。

🐬最终用户

最终用户一般为非计算机专业人员，通过应用程序访问数据库。

数据模型

数据建模

数据建模是对现实世界中的各类数据的抽象组织，以确定数据库的管辖范围、数据的组织形式等。

数据建模大致分为3个阶段，分别是概念建模阶段、逻辑建模阶段、物理建模阶段，相应的产物分别是概念模型、逻辑模型和物理模型。

概念模型是现实世界到机器世界的中间层，它将现实世界中的客观对象(如学生、班级、课程）抽象成信息世界的数据。

逻辑模型是指数据的逻辑结构，可以选择层次模型、网状模型或关系模型。

在完成逻辑模型后，最后使用物理模型描述数据如何进行实际存储，也就是将逻辑模型转换成计算机能够识别的模型。

概念模型的术语

在概念模型中可以通过一些术语来对现实世界进行抽象。

🐬实体

实体(Entity)是指客观存在并可相互区分的事物，如学生、班级、课程。

🐬属性

属性(Attribute)是指实体所具有的某一特性，一个实体可以由若干个属性来描述。

例如，学生实体的属性有学号、学生姓名和学生性别。属性由两部分组成，分别是属性名和属性值。

🐬联系

联系(Relationship)是指实体与实体之间的联系，有一对一、一对多、对多对三种情况。

例如，每个学生都有一个学生证，学生和学生证之间是一对一的联系；一个班级有多个学生，班级和学生是一对多的联系；一个学生可以选修多门课程，一门课程又可以被多个学生选修，学生和课程之间就形成了多对多的联系。

🐬实体型

实体型(EntityType)即实体类型，通过实体名(如学生)及其属性名集合(如“学号、学生姓名、学生性别”)来抽象描述同类实体。

🐬实体集

实体集(Entity Set)是指同一类型的实体集合，如全校学生就是一个实体集。

E-R图

E-R图也称为实体-联系图(Entity Relationship Diagram)，是一种用图形表示的实体联系模型，由Peter Chen于1976年提出。

E-R图提供了表示实体型、属性和联系的方法，用来描述现实世界的概念模型。其通用的表示方式如下:

实体： 用矩形框表示，将实体名写在框内。

属性： 用椭圆框表示，将属性名写在框内，用连线将实体与属性连接。

联系： 用菱形框表示，将联系名写在框内，用连线将相关的实体连接，并在连线旁标注联系类型(一 0 对 一 “1：1”、一对多 “1：n”、多对多 “n：m”)。

关系模型

关系模型由IBM公司研究员 Edgar Frank Codd 于1970年发表的论文中提出，经过多年的发展，已经成为目前最常用、最重要的模型之一。在关系模型中有一些基本的概念。

🐬关系

关系(Relation)一词与数学领域有关，它是集合基础上的一个重要的概念，用于反映元素之间的联系和性质。

从用户角度来看，关系模型的数据结构是二维表，即通过二维表来组织数据。一个关系对应一张二维表，表中的数据包括实体本身的数据和实体间的联系。

🐬属性

二维表中的列称为属性(Attribute)，每个属性都有一个属性名。

🐬元组

二维表中的每一行数据称为一个元组(Tuple)。

根据不同的习惯，属性也可以称为字段(Field），元组也可以称为记录(Record)。

🐬域

域(Domain)是指属性的取值范围。

🐬关系模式

关系模式(Relation Schema)是关系的描述，通常可以简记为“关系名（属性1，属性2，…，属性n)。

🐬键

在二维表中，若要唯一标识某一条记录，需要用到键（又称为关键字、码）。

关系模型的完整性

为了保证数据库中数据的正确性和相容性，需要对关系模型进行完整性约束。完整性通常包括实体完整性、参照完整性和用户自定义完整性。

🐬实体完整性

实体完整性要求关系中的主键不能重复，且不能取空值。

空值是指不知道、不存在或无意义的值。

由于关系中的元组对应现实世界中互相之间可区分的个体，这些个体使用主键来唯一标识，若主键为空或重复，则无法唯一标识每个个体。

🐬参照完整性

参照完整性要求关系中的外键要么取空值，要么取被参照关系中的某个元组的主键值。

例如，通过学生所属的班级号可以找到对应的班级，这就符合参照完整性；而如果对应的班级被删除了，学生通过班级号找不到班级，就不符合参照完整性。

🐬用户自定义完整性

用户自定义完整性是用户针对具体的应用环境定义的完整性约束条件，由DBMS检查用户自定义的完整性

关系运算

关系模型可以使用关系代数(Relational Algebra)来进行关系运算。关系代数是一种抽象的查询语言，是研究关系模型的数学工具。关系代数运算符主要包括并、差、交、笛卡儿积、选择、投影、连接和除。

并、差、交

并(Union)、差(Difference)、交(Intersection)运算要求参与运算的两个关系具有相同数量的属性，其运算结果是一个具有相同数量属性的新关系。

笛卡尔积

选择、投影

选择(Selection)是在一个关系中将满足条件的元组找出来，即水平方向筛选。

投影(Projection)是在一个关系中去掉不需要的属性，保留需要的属性，即垂直方向筛选。

连接

连接是在两个关系的笛卡儿积中选取属性间满足一定条件的元组。

由于笛卡儿积的结果可能会包含很多没有意义的元组，所以相比之下连接运算更为实用。

常用的连接方式有等值连接(Equi-Join)和自然连接(Natural Join)。

自然连接是一种特殊的等值连接，要求R和S必须有相同的属性组，进行等值连接后再去除重复的属性组。

除

如果把笛卡儿积看作乘法运算，则除法是笛卡儿积的逆运算。

R÷S1 表示查询学号为2的学生所选的课程；R÷S2 表示查询学号为2和3的学生共同选择的课程。

SQL语言

SQL (Structured Query Language,结构化查询语言) 是一种数据库查询语言和程序设计语言，主要用于管理数据库中的数据，如存取数据、查询数据、更新数据等。

SQL是IBM公司于1975-1979年开发出来的，在20世纪80年代，SQL被美国国家标准学会（ANSI）和国际标准化组织（International Organization for Standardization,ISO）定义为关系数据库语言的标准。

SQL是由4部分组成的。

🐬数据定义语言

数据库定义语言(Data Definition Language,DDL)主要用于定义数据库、表等。

例如，CREATE语句用于创建数据库、数据表等，ALTER语句用于修改表的定义等，DROP语句用于删除数据库、删除表等。

🐬数据操作语言

数据操作语言(Data Manipulation Language,DML)主要用于对数据库进行添加、修改和删除操作。

例如，INSERT语句用于插入数据，UPDATE语句用于修改数据，DELETE语句用于删除数据。

🐬数据查询语言

数据查询语言(Data Query Language,DQL)主要用于查询数据。

例如，使用SELECT语句可以查询数据库中的一条数据或多条数据。

🐬数据控制语言

数据控制语言(Data Control Language，DCL)主要用于控制用户的访问权限。

例如，GRANT语句用于给用户增加权限，REVOKE语句用于收回用户的权限，COMMIT语句用于提交事务，ROLLBACK语句用于回滚事务。

安装MYSQL

下载地址： https://dev.mysql.com/downloads/mysql/

本文安装的是mysql5.7.22版作为演示。

0️⃣解压下载好的mysql压缩包，我这里解压到C:\mysql5.7目录。

1️⃣打开命令提示符窗口，在命令模式下，切换到 MYSQL 安装目录下的bin目录

cd C:\mysql5.7\bin

2️⃣输入安装命令

mysqld -install

🐬在安装MySQL时，还有一些常见的问题需要注意

MySQL安装的服务名默认为 “MySQL”，如果该名称已经存在，则会安装失败，提示The service already exists!

此时可能是系统中已经安装了MySQL，可以通过如下命令进行卸载，卸载后再进行安装。

mysqld -remove

MySQL允许在安装或卸载时指定服务名称，从而实现多个MySQL服务共存。

例如，当需要同时安装MySQL5.7和8.0时，分别指定不同的服务名称即可实现。

mysqld -install "服务名称"

mysqld -remove "服务名称"

MySQL服务默认监听3306端口，如果该端口被其他服务占用，会导致客户端无法连接服务器。

在命令行中可用 netstat -ano 命令查看端口占用情况。

为了获知该进程是哪一个程序，执行 tasklist | findstr "4204” 命令

当前是 mysqld.exe 占用了3306端口，说明MySQL服务正在工作。如果是其他程序占用了3306端口，只需将对应的服务停止即可。

3️⃣使用文本编辑器创建配置文件 C:\mysql5.7\my.ini，编写如下：

[mysqld]basedir=C:\mysql5.7datadir=C:\mysql5.7\dataport=3306

basedir 表示MySQL的安装目录；datadir 表示数据库文件的保存目录；port 表示MySQL服务的端口号。

在没有配置文件的情况下，MySQL会自动检测安装目录、数据文件目录。

但由于不同MySQL版本的路径可能有区别，所以建议通过配置文件来指定。

另外，Linux系统中通常使用 my.cnf 作为配置文件的文件名，在Windows系统中也可以使用该文件名。

4️⃣初始化数据库

创建my.ini配置文件后，数据库文件目录 C:\mysql5.7\data 还没有创建。接下来需要通过MySQL的初始化功能，自动创建数据文件目录

mysqld --initialize-insecure

“-initialize”表示初始化数据库，“-insecure”表示忽略安全性。

当省略 “insecure” 时，MySQL将自动为默认用户 “root” 生成一个随机的复杂密码，而加上 “insecure” 时，“root”用户的密码为空。由于自动生成的密码输人比较麻烦，因此这里选择忽略安全性。

MySQL5.5和5.6版本中已经提供了data目录，不需要初始化数据库。只有安装5.7和8.0版本时需要执行上述命令。

5️⃣管理MySQL服务

MySQL安装完成后，需要启动服务进程，否则客户端无法连接数据库。在前面的配置过程中，已经将MySQL安装为Windows服务。

MySQL服务不仅可以通过Windows服务管理器启动，还可以通过命令行来启动。

使用管理员身份打开命令提示符，输人如下命令启动名称为 MySQL 的服务。

net start MySQL

停止服务

net stop MySQL

6️⃣登录MYSQL

在 MySQL 的 bin 目录中，mysql.exe 是 MySQL 提供的命令行客户端工具，用于访问数据库。

该程序不能直接双击运行，需要打开命令行窗口，执行 cd C:\mysql5.7\bin 命令切换工作目录，然后执行如下命令登录MySQL服务器。

mysql -u root

“mysql”表示运行当前目录下的 mysql.exe。

“-u root” 表示以root用户的身份登录，其中，“-u”和“root”之间的空格可以省略。

如果需要退出MySQL，可以直接使用 exit 或 quit 命令。

命令行客户端工具还有一些常用选项。其中，“-h” 用于指定登录的MySQL服务器地址（域名或IP)，如“-hlocalhost” 或 “-h 127.0.0.1” 表示登录本地服务器。选项“-P”（必须用大写字母P）用于指定连接的端口号，如“-P3306”表示连接3306端口。

7️⃣设置密码

为了保护数据库的安全，需要为登录MvSQL服务器的用户设置密码。

下面以设置root用户的密码为例，登录MySQL后，执行如下命令即可。

mysql>ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';

上述命令表示为 localhost 主机中的 root 用户设置密码，密码为“123456”。

当设置密码后，退出MySQL，然后重新登录时，就需要输入刚才设置的密码。

在登录有密码的用户时，需要使用的命令如下。

mysql -uroot -p123456

在上述命令中，“-p123456” 表示使用密码 “123456” 进行登录。如果在登录时不希望密码被直接看到，可以省略“-p”后面的密码，然后按回车键，会提示输入密码。

在设置密码后，如果需要取消密码，可以使用如下命令。

mysql>ALTER USER 'root'@ 'localhost' IDENTIFIED BY ''

上述命令将密码设为空，即可免密码登录。

8️⃣设置环境变量

在启动 MYSQL 客户端前，确保命令提示符当前位于 C:\mysql5.7\bin 目录，执行命令将 bin 目录添加到环境变量。

setx PATH "%PATH%;C:\mysql5.7\bin"

执行上述命令后，关闭当前命令行窗口，重新打开一个新的命令行窗口即可生效。

数据库操作

创建数据库

创建数据库就是在数据库系统中划分一块存储数据的空间。

🐬语法格式

CREATE DATABASE 数据库名称[库选项]

CREATE DATABASE 表示创建数据库；

数据库名称可以是字母、数字和下划线组成的任意字符串；

“库选项” 用于设置此数据库的相关特性，如字符集CHARSET，校对集COLLATE。

语法内使用 “[ ]” 括起来的选项表示可选参数。

下面创建一个名称为 mydb 的数据库，具体SQL语句与执行结果如下。

mysql>CREATE DATABASE mydb;Query OK,1 row affected (0.00 sec)

在创建数据库后，MySQL.会在存储数据的data目录中创建一个与数据库同名的子目录（即mydb），同时会在mydb目录下生成一个 db.opt 文件，保存数据库选项，打开data\mydb\db.opt文件，如下所示。

default-character-set=latin1default-collation-Latin1——swedish_ci

mydb 数据库的默认字符集为 latin1，校对集为 Latin1_swedish_ci。

创建的数据库已存在，则程序会报错，可以在创建数据库名称前添加 IN NOT EXISTS 。表示指定的数据库不存在时执行创建，否则忽略此操作。

查看数据库

🐬查看所有的数据库

SHOW DATABASES

MySQL服务器已有4个数据库，这些数据库都是MySQL安装时自动创建的。

+-------------------+|information_schema ||performance_schema ||mysql||sys |+-------------------+

information_schema 和 performance_schema 数据库分别是 MySQL 服务器的数据字典（保存所有数据表和库的结构信息）和性能字典（保存全局变量等的设置）。

“mysql” 数据库主要负责MySQL服务器自己需要使用的控制和管理信息，如用户的权限关系等。

sys 是系统数据库，包括了存储过程、自定义函数等信息。

🐬查看指定数据库的创建信息

SHOW CREATE DATABASE 数据库名称；

选择数据库

由于MySQL服务器中的数据需要存储到数据表中，而数据表需要存储到对应的数据库下，并且MYSQL服务器中又可以同时存在多个数据库，因此，在对数据和数据表进行操作前，首先需要选择数据库。

USE 数据库名称；

🐬登录 MYSQL 时选择数据库

mysql -u 用户名 -p 密码 数据库名

删除数据库

DROP DATABASE 数据库名称;

若删除数据库不存在，mysql服务器会报错，可以在数据库名前添加 IF EXISTS 来判断删除的数据库是否存在，存在就删，不存在就不执行删除操作。

数据表操作

创建数据表

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] 表名(字段名 字段类型 [字段属性] …) [表选项]

TEMPORARY： 表示临时表，仅在当前会话中可见，并且在会话关闭时自动删除。

字段名：指的是数据表的列名。

字段类型：设置字段中保存的数据类型，如时间日期类型等。

字段属性：指的是字段的某些特殊约束条件。

表选项：用于设置表的相关特性，如存储引擎（ENGINE）、字符集（CHARSET）和校对集(COLLATE)。

在操作数据表之前，应该使用 USE 数据库名 指定操作是在哪个数据库中进行，否则会抛出 No database selected 错误。

🐬例如：创建名为 goods 的数据表

create table goods (id int comment '编号',name varchar(32) comment '商品名');

INT用于设置字段数据类型是整型。

VARCHAR(L)表示可变长度的字符串，L表示字符数，如VARCHAR(32)表示可变的字符数是32。

COMMENT用于在创建表时添加注释内容，并将其保存到表结构中。

查看数据表

SHOW TABLES [LIKE 匹配模式];

若不添加可选项 LIKE匹配模式，表示查看当前数据库中的所有数据表；

若添加则按照 匹配模式 查看数据表。其中，匹配模式符有两种，分别为 % 和 _。前者表示匹配一个或多个字符，代表任意长度的字符串，长度也可以为0，后者仅可以匹配一个字符。

LIKE 后的匹配模式必须使用单引号或双引号包裹

🐬查看数据表的相关信息

SHOW TABLES STATUS [FROM 数据库名] [LIKE 匹配模式];

修改数据表

🐬修改数据表名称

语法格式1：

ALTER TABLE 旧表名 RENAME [TO|AS] 新表名;

语法格式2：

RENAME TABLE 旧表名1 TO 新表名1 [旧表名2 TO 新表名2] ...;

🐬修改表选项

ALTER TABLE 表名 表选项 = 值;

查看表结构

🐬查看数据表的字段信息

查看所有字段的信息

{DESCRIBE | DESC} 数据表名;

查看指定字段信息

{DESCRIBE | DESC} 数据表名 字段名;

🐬查看数据的创建语句

SHOW CREATE TABLE 表名;

🐬查看数据表结构

语法格式1：

SHOW [FULL] COLUMNS FROM 数据表名 [FROM 数据库名];

语法格式2：

SHOW [FULL] COLUMNS FROM 数据库名.数据表名;

可选项 FULL 表示显示详细内容，在不添加的情况下查询结果与 DESC 的结果相同。

在添加 FULL 选项时此语句不仅可以查看到 DESC 语句查看的信息，还可以查看到字段的权限、COMMENT字段的注释信息等。

修改表结构

🐬修改字段名

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

数据类型表示新字段名的数据类型，不能为空，即使与旧字段的数据类型相同，也必须重新设置。

🐬修改字段类型

ALTER TABLE 数据表名 MODIFY [COLUMN] 字段名 新字段类型 [字段属性];

🐬修改字段的位置

ALTER TABLE 数据表名 MODIFY [COLUMN] 字段名1 数据类型 [字段属性] [FIRST | AFTER 字段名2];

🐬新增字段

语法格式1：新增一个字段，并可指定其位置

ALTER TABLE 数据表名 ADD [COLUMN] 新字段名 字段类型 [FIRST | AFTER字段名];

语法格式2：同时新增多个字段

ALTER TABLE 数据表名 ADD [COLUMN] (新字段名1 字段类型1, 新字段名2 字段类型2,…);

在不指定位置的情况下，新增的字段默认添加到表的最后。另外，同时新增多个字段时不能指定字段的位置。

🐬删除字段

ALTER TABLE 数据表名 DROP [COLUMN] 字段名;

删除数据表

DROP [TEMPORARY] TABLE [IF EXISTS] 数据表 1 [,数据表2] ...;

数据操作

添加数据

🐬为所有的字段添加数据

严格按照数据表结构(字段的位置)插入对应的值。

INSERT [INTO] 数据表名 {VALUES | VALUE} (值1 [,值2] ...);

在MySQL中，若创建的数据表未指定字符集，则数据表及表中的字段将使用默认的字符集latin1。

因此，若用户插入的数据中含有中文，则会出现错误提示。

为为了解决以上中文插入的问题，通常在创建数据表时添加表选项，设置数据表的字符集。

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] 表名 (字段名 字段类型[字段属性] ...) [DEFAULT] {CHARACTER SET[CHARSET} [=] utf8;

🐬为部分字段添加数据

除了为数据表中所有字段添加数据外，还可以通过指定字段名的方式增加数据。

其中指定的字段名可以是数据表中全部的字段，也可以是部分的字段。

INSERT [INTO] 数据表名(字段名1 [,字段名2 ...) {VALUES | VALUE} (值1[, 值2] ...);

除此之外，MySQL中还提供了另外一种使用 INSERT 语句为指定字段添加数据的方式。

INSERT [INTO] 数据表名 SET 字段名1 = 值1 [,字段名2 = 值2] ...;

🐬一次添加多行数据

INSERT [INTO] 数据表名 [(字段列表)] {VALUES | VALUE} (值列表)[,值列表] ...;

在多数据插入时，若一条数据插人失败，则整个插人语句都会失败。

查询数据

🐬查询表中全部数据

SELECT * FROM 数据表名;

🐬查询表中部分字段

SELECT (字段名1, 字段名2, 字段名3, ...) FROM 数据表名;

🐬简单条件查询数据

SELECT (字段名1, 字段名2, 字段名3, ...) FROM 数据表名 WHERE 字段名 = 值;

修改数据

UPDATE 数据表名 SET 字段名1 = 值1[,字段名2 = 值2] [WHERE 条件表达式];

若没有where条件，那么表中对应的字段全部都会被修改

删除数据

DELETE FROM 数据表名 [WHERE 条件表达式];

常用转义字符

数据类型

数字类型

🐬整数类型

不同整数类型所占用的字节数和取值范围都是不同的。

占用字节数最小的是TINYINT，占用字节数最大的是BIGINT。

不同整数类型的取值范围可以根据字节数计算出来。例如，TINYINT 类型的整数占用1字节，1字节是8位，那么，TINYINT 类型无符号数的最大值就是 2^8 - 1（即255）， 有符号数的最大值就是 2^7 - 1（即127）。

若使用无符号数据类型，需要在数据类型右边加上 UNSIGNED 关键字来修饰，例如，INT UNSIGNED 表示无符号 INT 类型。

+------+-----------------+----+----+--------+------+|Field |TYPE  |NUll|KEY |Default |Extra |+------+-----------------+----+----+--------+------+|int_1 |int(11) |YES |   |NULL    |   ||int_2 |int(10) unsigned |YES |    |NULL    |   |+------+-----------------+----+----+--------+------+

数据类型右边使用小括号数字标注了显示宽度。默认情况下，显示宽度是取值范围所能表示的最大宽度。对于有符号类型，符号也占用一个宽度。

例如，255的显示宽度为3，一128的显示宽度为4。

❗显示宽度与取值范围无关，若数值的位数小于显示宽度，会填充空格，若大于显示宽度，则不影响显示结果。

为字段设置零填充(ZEROFILL)时，若数值宽度小于显示宽度，会在左侧填充0。

例如，在 my_Int 表中设置零填充和宽度

CREATE TABLE my_Int (int_1 INT(3) ZEROFILL,int_2 TINYINT(6) ZEROFILL);

设置零填充后，字段自动设为无符号类型，这是因为负数不能使用负填充。

🐬浮点数类型

列举的取值范围是理论上的极限值，但根据不同的硬件或操作系统，实际范围可能会小。

当浮点数类型使用 UNSIGNED 修饰为无符号时，取值范围将不包含负数。

浮点数类型虽然取值范围很大，但是精度并不高。FLOAT的精度为6位或7位，DOUBLE 的精度大约为15位。如果超出精度，可能会导致给定的数值与实际保存的数值不一致，发生精度损失。

🐬定点数类型

定点数类型(DECIMAL) 通过 DECIMAL(M,D) 设置位数和精度，其中，M表示数字总位数（不包括“.”和“—”)，最大值为65，默认值为10；D表示小数点后的位数，最大值为30，默认值为0。

例如，DECIMAL(5，2) 表示的取值范围是 －999.99～999.99。

若小数部分超出范围，会进行四舍五人，并出现 Data truncated(数据截断) 警告。

若整数部分超出范围，数据会插人失败，提示 Out of range value(超出取值范围）错误。

浮点数类型也可以设置位数和精度，如 float(8,2)，但仍有可能损失精度。在实际使用时应避免使用浮点数类型，以免出现不能人为控制的问题。因此，对于小数类型的设置，推荐使用定点数类型并设置合理的范围可以使计算更为准确。

🐬BIT类型

BIT(位) 类型用于存储二进制数据，语法为BIT(M)，M表示位数，范围为1~64。

时间和日期类型

YYYY：表示年，MM：表示月，DD：表示日，每种日期和时间类型的取值范围都是不同的。

❗如果插入的数值不合法，系统会自动将对应的零值插人数据库中。

🐬YEAR类型

YEAR 类型用于表示年份。

在 MySQL 中，可以使用以下 3 种格式指定 YEAR 类型的值。

• 使用 4 位字符串或数字表示，为 '1901'~'2155' 或 1901~2155。
  • 例如，输入 ‘2020’ 或 2020 ，插入到数据库中的值均为 2020。
• 使用两位字符串表示为 '00'~'99'，其中，'00'~'69' 的值会被转换为 2000~2069 的YEAR值，'70'~'99' 的值会被转换为 1970~1999 的YEAR值。
  • 例如，输入 ‘20’，插入到数据库中的值为 2020。
• 使用两位数字表示为 1~99 ，其中，1~69 的值会被转换为 2001~2069 的YEAR值，70~99的值会被转换为 1970~1999 的YEAR值。
  • 例如，输入 20 ，插入到数据库中的值为 2020。

❗当使用 YEAR 类型时，一定要区分 '0' 和 0。因为字符串格式的 '0' 表示的 YEAR 值是 2000，而数字格式的 0表示的 YEAR 值是 0000 。

🐬DATE类型

DATE 类型用于表示日期值，不包含时间部分。

在 MySQL 中，可以使用以下 4 种格式指定 DATE 类型的值。

• 以 'YYYY-MM-DD' 或者 'YYYYMMDD' 字符串格式表示。
  • 例如，输入 ‘2020-01-21’ 或 ‘20200121’，插入数据库中的日期都为 2020-01-21。
• 以 'YY-MM-DD' 或者 'YYMMDD' 字符串格式表示。YY表示的是年，为 '00'~'99'，其中 '00'~'69' 的值会被转换为2000~2069的值，70'~'99' 的值会被转换为 1970~1999 的值。
  • 例如，输入’20-01-21’或 ‘200121’，插入数据库中的日期都为2020-01-21。
• 以 YY-MM-DD 或者 YYMMDD 数字格式表示。
  • 例如，输入 20-01-21 或 200121，插入数据库中的日期都为 2020-01-21。
• 使用 CURRENT_DATE 或者 NOW() 输入当前系统日期。

通过 SELECT CURRENT_DATE 或 SELECT NOW() 可查看当前日期。

日期中的分隔符 - 还可以用 . ， / 等符号替代。

🐬TIME类型

TIME类型用于表示时间值，它的显示形式一般为 HH:MM:SS，其中，HH表示小时，MM表示分，SS表示秒。

在 MySQL 中，可以使用以下 3 种格式指定 TIME 类型的值。

• 以 'HHMMSS' 字符串或者 HHMMSS 数字格式表示。
  • 例如，输入 '345454' 或 345454 ，插入数据库中的时间为 34:54:54(34小时54分54秒)。
• 以 D HH:MM:SS 字符串格式表示。其中，D 表示日，可以取 0～34 之间的值，插入数据时，小时的值等于(D×24+HH)。
  • 例如，输入'2 11:30:50'，插入数据库中的时间为 59:30:50；输入 '11:30:50'，插入数据库中的时间为11：30：50。
• 使用 CURRENT_TIME 或 NOW()输入当前系统时间。

🐬DATETIME类型

DATETIME 类型用于表示日期和时间，它的显示形式为 'YYYY-MM-DD HH:MM:SS'，其中，YYYY表示年，MM表示月，DD表示日，HH表示小时，MM表示分，SS表示秒。

在MySQL中，可以使用以下 4 种格式指定 DATETIME 类型的值。

• 以 'YYYY-MM-DD HH:MM:SS' 或者 'YYYYMMDDHHMMSS' 字符串格式表示的日期和时间，取值范围为'1000-01-01 00:00:00'~'9999-12-31 23:59:59' 。

  • 例如，输入 '2014-01-22 09:01:23' 或 20140122090123 ，插入数据库中的 DATETIME 值都为 2014-01-22 09:01:23。

• 以 'YYYY-MM-DD HH:MM:SS' 或者 'YYMMDDHHMMSS' 字符串格式表示的日期和时间，其中 YY 表示年，取值范围为 '00'～'99'。与 DATE 类型中的 YY 相同，'00'～'69' 范围的值会被转换为 2000～2069范围的值，'70'~'99' 范围的值会被转换为 1970~1999 范围。

• 以 YYYYMMDDHHMMSS 或者 YYMMDDHHMMSS 数字格式表示的日期和时间。

  • 插人 20140122090123 或者 140122090123 ，插入数据库中的 DATETIME 值都为 2014-01-22 09:01:23。

• 使用 NOW() 来输入当前系统的日期和时间。

🐬TIMESTAMP类型

TIMESTAMP(时间戳) 类型用于表示日期和时间，它的显示形式与 DATETIME 相同、但取值范围比DATETIME小。

TIMESTAMP 类型与DATATIME类型不同的形式：

• 使用 CURRENT_TIMESTAMP 来输入系统当前日期和时间。
• 无任何输入，或输入 NULL 时，实际保存的是系统当前日期和时间。

在MySQL中，TIMFSTAMP 字段默认情况下会自动设置 NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 属性。

• NOT NULL 表示非空约束，该字段将不允许保存 NULL 值。
• DEFAULT表示默认约束，当字段无任何输入时，自动设置某个值作为默认值。此处设为CURRENT_TIMESTAMP 表示使用系统当前日期和时间作为默认值。
• ON UPDATE 用于当一条记录中的其他字段被 UPDATE 语句修改时，自动更改该字段为某个值。此处设为 CURRENT_TIMESTAMP 表示每次修改时保存修改时的系统日期和时间。

若为 TIMESTAMP 字段手动设置 DEFAULT 属性时，该字段将不会自动设置 ON UPDATE 属性。

字符串类型

🐬CHAR和VARCHAR类型

CHAR 和 VARCHAR 类型都用来保存字符串数据。不同的是，VARCHAR 可以存储可变长度的字符串。

CHAR(M)

VARCHAR(M)

M指的是字符串的最大长度。为了对比 CHAR 和 VARCHAR 之间的区别，下面以 CHAR(4) 和VARCHAR(4) 为例进行说明。

对于 CHAR(4) 无论插入值的长度是多少，所占用的存储空间都是4字节，而 VARCHAR(4) 占用的字节数为实际长度加1。

🐬TEXT类型

TEXT类型用于保存大文本数据，例如，文章内容、评论等比较长的文本。

❗TEXT类型所能保存的最大字符数量取决于字符串实际占用的字节数。

🐬ENUM类型

ENUM 类型又称为枚举类型。

ENUM('值1', '值2', '值3', ...,'值n')

('值1', '值2', '值3', ...,'值n') 称为枚举列表，ENUM类型的数据只能从枚举列表中取，并且只能取一个。

在MySQL中，枚举列表最多可以有 65535 个值，每个值都有一个顺序编号，实际保存在记录中的是顺序编号，而不是列表中的值，因此不必担心过长的值占用空间。但在使用 SELECT、INSERT 等语句进行操作时，仍然使用列表中的值。

🐬SET类型

SET类型用于保存字符串对象，其定义格式与ENUM类型类似。

SET('值1', '值2', '值3', ...,'值n')

SET类型的列表中最多可以有 64 个值，且列表中的每个值都有一个顺序编号，为了节省空间，实际保存在记录中的也是顺序编号，但在使用 SELECT、INSERT 等语句进行操作时，仍然要使用列表中的值。

SET类型与ENUM的区别在于，它可以从列表中选择一个或多个值来保存，多个值之间用逗号 , 分隔。

1️⃣ENUM 类型类似于单选框，SET 类型类似于复选框。
2️⃣ENUM 和 SET 类型的优势在于规范数据本身，限定只能插入规定的数据项，节省了存储空间，查询速度比 CHAR、VARCHAR 类型快。
3️⃣ENUM 和 SET 类型列表中的值都可以使用中文，但必须设置支持中文的字符集，例如 CREATE TABLE my_enum (gender ENUM('男', '女')) CHARSET=GBK;。
4️⃣ENUM 和 SET 类型在填写列表、插入值、查找值等操作时，都会自动忽略末尾的空格。

🐬BINARY和VARBINARY类型

BINARY 和 VARBINARY 类型类似于 CHAR 和 VARCHAR，不同的是，它们所表示的是二进制数据。

BINARY(M)

VARBINARY(M)

M 是指二进制数据的最大字节长度。

BINARY 类型的长度是固定的，如果数据的长度不足最大长度，将在数据的后面用 \0 补齐，最终达到指定长度。

例如，指定数据类型为 BINARY(3) ，当插入a 时，实际存储的数据为 a\0\0 ，当插入 ab 时，实际存储的数据为 ab\0 。

在查询 BINARY 类型时，查询条件字符串也需要加上 \0 填充符，否则查询不到该记录，并且BINARY 和 VARBINARY 都区分大小写。

例如，指定数据类型为 BINARY(3) ，当查询 a 时，WHERE条件值要输入 a\0\0，否则查询不到该记录。

🐬BLOB类型

BLOB类型用于保存数据量很大的二进制数据，如图片、PDF文档等。

BLOB 类型与 TEXT 类型很相似，但 BLOB 类型数据是根据二进制编码进行比较和排序，而 TEXT 类型数据是根据文本模式进行比较和排序。

JSON数据类型

MySQL从 5.7.8 版本开始提供了JSON 数据类型。

JSON是一种轻量级的数据交换格式，由 JavaScript 语言发展而来，其本质是一个字符串。

MySQL中 JSON 类型值常见的表现方式有两种，分别为 JSON数组 和 JSON对象。

# 1. JSON 数组["abc", 10, null, true, false]# 2.JSON对象["k1", "value", "k2", "10"]

JSON数组中保存的数据可以是任意类型。

与直接使用 MySQL 字符串类型相比，JSON 数据类型具有自动验证格式、优化存储格式的优点。

JSON 数据类型所需的空间大致与 LONGBLOB 或 LONGTEXT 相同，且不能有默认值。

CREATE TABLE myjson (j1 JSON, j2 JSON);INSERT INTO myjson VALUES ('{"k1", "value", "k2", "10"}, ["run", "sing"]');

二进制方式比较字符串

若要在 CHAR、VARCHAR、TEXT 类型的字符串比较时严格区分大小写，有多种实现方式。下面两种比较常见的方式。

🐬使用 BINARY 关键字

在字段名或某个值的前面加上 BINARY 关键字可以将类型转换为二进制，转换后进行比较就可以严格区分大小写和空格。

SELECT 'a ' = 'A';#比较结果：1（相等）SELECT BINARY 'a' = 'A', 'a' = BINARY 'A';#比较结果：0（不相等）SELECT BINARY 'A ' = 'A';#比较结果：0（不相等）SELECT BINARY 'A' = 'A', 'A' = BINARY 'A';#比较结果：1（相等）

🐬设置字段的校对集

latin1、gbk、utf8 编码默认的校对集分别为 latin1_swedish_ci、gbk_chinese_ci、utf8_general_ci，将其分别改为 latin1_bin、gbk_bin、utf8_bin 即可区分大小写，但在比较时仍会忽略字符串末尾的空格。

# 1.创建表时设置字段的校对集CREATE TABLE mychar (c1 CHAR(2) CHARACTER SET latin1 COLLATE latin1_bin,c2 CHAR(2) CHARACTER SET gbk COLLATE gbk_bin,c3 CHAR(2) CHARACTER SET utf8 COLLATE utf8_bin);# 2.插入测试数据INSERT INTO mychar VALUES('A', 'A', 'A')# 3.查询数据SELECT c1 = 'a', c2 = 'a', c3 = 'a' FROM mychar;#结果：0（不想等）SELECT c1 = 'A', c2 = 'A', c3 = 'A' FROM mychar;#结果：1（想等）SELECT c1 = 'A ', c2 = 'A ', c3 = 'A ' FROM mychar;#结果：1（想等）

注意

1️⃣CHAR 和 VARCHAR 类型在插入数据时，若字符串末尾有空格，CHAR 类型会自动去掉空格后保存，而VARCHAR、TEXT 类型会保留空格。

2️⃣在使用 “=” 等运算符对 CHAR 、VARCHAR 、TEXT 进行比较时，字符串末尾的空格会被忽略。

例如，使用 WHERE 查询 ‘a’ 字符串，查询结果中可能包含 a 后面有空格的情况，反之，若查询条件字符串末尾有空格（如 'a ')，空格也会被忽略。

3️⃣由于默认情况下创建的数据库和表使用的校对集 (latin1_swedish_ci) 对大小写不敏感，因此CHAR、VARCHAR、TEXT、ENUM、SET 类型都不区分大小写。

例如，使用 WHERE 查询 ‘a’ 字符串，则 “a” 和 “A” 都会被查询出来。而 BINARY、VARBINARY、BLOB类型区分大小写，这是因为它们使用二进制方式保存数据。

4️⃣MySQL 默认规定一条记录的最大长度是 65535 字节，一般来说，字段分配的存储空间和额外开销加在一起不能超过 65535 字节，如果超过了这个限制，在SQL 严格模式下表会创建失败，提示 Row size too large。但 TEXT 和 BLOB 类型字段的存储空间不受此限制，它们只占用额外开销（大约12字节)。

5️⃣在没有超过 65535 限制的情况下，CHAR 字段的 M 最大值为255，VARCHAR字段的 M 的最大值取决于字符集，常用的字符集有 latin1(默认）、gbk 和 utf8，对应的M最大值分别为 65533、32766 和 21844，若表中只有一个字段且设置了非空约束，M 可达到最大值，否则 M 的最大值会减小。

6️⃣从执行效率上来说，TEXT 和 BLOB 不如 CHAR、VARCHAR 类型，建议只有当需要保存大量数据时，才选择使用 TEXT 或 BLOB 类型。

表的约束

为了防止数据表中插入错误的数据，MySQL定义了一些维护数据库完整性的规则，即表的约束。

默认约束

默认约束用于为数据表中的字段指定默认值，即当在表中插人一条新记录时，如果没有给这个字段赋值，那么，数据库系统会自动为这个字段插入默认值。

字段名 数据类型 DEFAULT 默认值;

使用 ALTER TABLE 修改列属性即可添加或删除默认约束。

BLOB、TEXT 数据类型不支持默认约束。

非空约束

非空约束指的是字段的值不能为NULL。

字段名 数据类型 NOT NULL;

为现有的表添加或删除非空约束的方式与默认约束类似，使用ALTER TABLE修改列属性即可。
若目标列中已经保存了NULL值，添加非空约束会失败，提示 “Invalid use of NULL value”，只要将NULL值改为其他值即可解决。

唯一约束

唯一约束用于保证数据表中字段的唯一性，即表中字段的值不能重复出现。

#列级约束字段名 数据类型 UNIQUE;#表级约束UNIQUE(字段名1,字段名2, ...);

列级约束定义在一个列上，只对该列起约束作用；表级约束是独立于列的定义，可以应用在一个表的多个列上。

当表级约束仅建立在一个字段上时，其作用效果与列级约束相同。

若为一个现有的表添加或删除唯一约束，无法通过修改字段属性的方式操作，而是按照索引的方式来操作。

添加唯一约束的完整语法

UNIQUE KEY 索引名(字段列表)

添加唯一约束时创建索引，用于加快查询速度。

索引名可以自己指定，也可以省略，MySQL会自动使用字段作为索引名。当需要对索引进行删除时，需要指定这个索引名。

在表级唯一性约束创建时，UNIQUE() 的字段列表中，可以添加多个字段，组成复合唯一键，其特点是只有多个字段的值相同时才视为重复记录。

主键约束

为了快速查找表中的某条信息，可以通过设置主键来实现。主键可以唯一标识表中的记录，类似身份证用于标识人的身份一样。

主键约束通过 PRIMARY KEY 定义，它相当于唯一约束和非空约束的组合，要求被约束字段不允许重复，也不允许出现 NULL 值，每个表最多只允许含有一个主键。

# 列级约束字段名 数据类型 PRIMARY KEY#表级约束PRIMARY KEY（字段名1,字段名2,…）

表级约束的字段若只有一个，则为单字段主键与列级约束添加的效果相同；若有多个，则为复合主键。复合主键需要用多个字段来确定一条记录的唯一性，类似于复合唯一键。

# 1.删除主键约束mysql>ALTER TABLE my_primary DROP PRIMARY KEY;# 2.删除id字段的非空约束mysql>ALTER TABLE my_primary MODIFY id INT UNSIGNED;# 3.添加主键约束mysql>ALTER TABLE my_primary ADD PRIMARY KEY(id);

删除 id 字段的主键约束后，该字段的非空约束并没有被同时删除。若需要删除 id 字段的非空约束，执行第2步删除操作即可。

自动增长

为数据表设置主键约束后，每次插入记录时，都需要检查主键的值，防止插入的值重复导致插入失败，可以利用MySQL提供的自动增长功能来自动生成主键的值。

字段名 数据类型 AUTO_INCREMENT

🐬在使用 AUTO_INCREMENT 时，需要注意以下4点：

    👉 一个表中只能有一个自动增长字段，该字段的数据类型是整数类型，且必须定义为键，如 UNIQUE KEY 、PRIMARY KEY。

    👉 若为自动增长字段插入 NULL、0、DEFAULT 或在插入时省略该字段，则该字段就会使用自动增长值；若插入的是一个具体值，则不会使用自动增长值。

    👉 自动增长值从 1 开始自增，每次加 1。若插入的值大于自动增长的值，则下次插入的自动增长值会自动使用最大值加 1；若插入的值小于自动增长值，则不会对自动增长值产生影响。

    👉 使用 DELETE 删除记录时，自动增长值不会减小或填补空缺。

🐬查看自动增长值

SHOW CREATE TABLE 数据库名\G

🐬修改自动增长

ALTER TABLE 数据库名 AUTO_INCREMENT = 值;

🐬删除自动增长

ALTER TABLE 数据库名 MODIFY 字段名 字段类型 [字段属性];

🐬添加自动增长

ALTER TABLE 数据库名 MODIFY 字段名 字段类型 [字段属性] AUTO_INCREMENT;

通过 SHOW VARIABLES LIKE 'auto_increment%'; 可以查看 MySQL 中用于维护自动增长的变量分别是 auto increment_ increment(默认值1) 和 auto_increment_offset(默认值为1)，通过更改这两个变量可以改变自动增长的计算方式，可以参考官方手册中的详细说明。

字符集与校对集

MySQL默认使用的字符集为latin1(ISO-8859-1的别名)，这是一种单字节编码的字符集。

由于中文汉字属于多字节编码的字符，若要保存中文，需要设置成其他支持中文的字符集，如GBK、UTF8。

校对集是字符之间的比较关系，在比较、排序等操作时都会用到校对集。

字符集与校对集概述

🐬字符集

字符(character)指计算机中保存的各种文字和符号，包括各种国家的文字、标点符号、图形符号、数字等。

由于计算机采用二进制保存数据，用户输入的字符将会按照一定的规则转换为二进制后保存，这个过程就是字符编码(character encoding)，将一系列字符的编码规则组合起来就形成了字符集(character set，charset)。

MySQL提供了各种字符集的支持，通过 SHOW CHARACTER SET; 可以查看可用字符集。

常用字符集

单字符占用的存储空间越多，所支持的语言越多。

表中列举的字符集都向下兼容 ASCII(美国信息交换标准代码)，若输入的字符在 ASCII 范围内(0x00~0x7F)，则编码结果是完全相同的。

由于历史原因，MySQL 中的 utf8 编码与标准的 UTF-8(RFC3629) 存在一些差别。标准的 UTF-8 规定一个字符最多使用 4 字节，而 MySQL 中的 utf8 编码一个字符最多使用 3 字节，这导致 UTF-8 中的一些特殊字符（如emoji符号）无法在 MySQL 的 utf8 编码中使用。为了解决这个问题，MySQL从5.5.3版本开始新增了 utf8mb4 编码，将一个字符扩展到4字节。因此，如果要考虑支持 RFC3629 规范，应使用 utf8mb4 编码。

🐬校对集

MySQL中提供了许多校对集，用于为不同字符集指定比较和排序规则。

例如，latin1字符集默认的校对集为 latin1_swedish_ci。

校对集的名称由 _ 分隔的3部分组成，开头是对应的字符集，中间是国家名或 general，结尾是 ci、cs或bin。其中，ci 表示不区分大小写，cs 表示区分大小写，bin 表示以二进制方式比较。

通过 SHOW COLLATION; 可以查看 MySQL 可用的校对集。

字符集与校对集的设置

🐬MySQL环境

使用 MySQL 命令 SHOW VARIABLES LIKE 'character%';可以查看与字符集相关的变量。

+-------------------------------+--------------------------------+| Variable_name          |Value |+-------------------------------+--------------------------------+| character_set_client| gbk || character_set_connection| gbk || character_set_database| latin1 || character_set_filesystem| binary || character_set_results| gbk || character_set_server| latin1 || character_set_system| utf8 || character_sets_dir    | C:\mysql5.7\share\charsets\    |+-------------------------------+--------------------------------+

上述结果表示当前会话(Session)使用的字符集，在不同客户端环境中的输出结果可能不同。

这里所说的会话，是指从客户端登录服务器到退出的整个过程。例如，依次打开两个客户端并登录服务器，就产生了两个会话，不同客户端属于不同的会话。

不同的客户端可以指定不同的字符集环境配置，服务器会按照不同的配置进行处理。

重点关注的变量是 character_set_client、 character_set_connection、character_set_results 和 character_set_server。

character_set_server 决定了新创建的数据库默认使用的字符集。数据库的字符集决定了数据表的默认字符集，数据表的字符集决定了字段的默认字符集。

character_set_client、character_set_connection 和character_set_results 分别对应客户端、连接层和查询结果的字符集。

通常情况下，这3个变量的值是相同的，具体值由客户端的编码而定，从而使客户端输入的字符和输出的查询结果都不会出现乱码。

通过 set 变量名 = 值; 命令可以更改变量的值。

set character_set_client=gbk;set character_set_connection=gbk;set character_set_results=gbk;

上述命令输入比较麻烦，在 MySQL 中还可以通过 set names 字符集; 直接修改上述3个变量的值，即set names gbk;。

🐬注意：

    👉 使用 set或 set names 修改字符集只对当前会话有效，不影响其他会话，且会话结束后，下次会话仍然使用默认值。

    👉 与 character_set_connection、character_set_database、character_set_server 对应的校对集分别通过变量 collation_connection、collation_database、collation_server来指定。

    👉 若字段使用 utf8 字符集，而客户端使用 gbk 字符集，MySQL 会自动进行编码转换。由于 utf8 和 gbk 本质上是不同的字符集，虽然大部分常见的字符可以转换成功，但若遇到其中一个字符集中没有的特殊字符，则可能会出现乱码。

🐬数据库

在创建数据库时设定字符集和校对集。

[DEFAULT] CHARACTER SET [=] charset_name[DEFAULT] COLLATE [=] collation_name

CHARACTER SET 用于指定字符集，COLLATE 用于指定校对集。

若仅指定字符集，表示使用该字符集的默认校对集；若仅指定校对集，表示使用该校对集对应的字符集。

示例

# 创建数据库，指定字符集为 utf8，使用默认校对集 utf8_general_ciCREATE DATABASE 数据库名 CHARACTER SET utf8;# 创建数据库，指定字符集为 utf8，校对集为 utf8_binCREATE DATABASE 数据库名 CHARACTER SET utf8 COLLATE utf8_bin;

🐬数据表

数据表的字符集与校对集在表选项中设定。

[DEFAULT] CHARACTER SET [=] charset_name[DEFAULT] COLLATE [=] collation_name

若没有为数据表指定字符集，则自动使用数据库的字符集。

🐬字段

字段的字符集与校对集在字段属性中设定。

[CHARACTER SET charset_name] [COLLATE collation_name]

若没有为字段设定字符集与校对集，则会自动使用数据表的字符集与校对集。

示例

CREATE TABLE 数据表名 (username VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_bin);

数据库设计

数据库设计要求设计人员对数据库有深入的理解，才能设计出高质量的数据库。数据库设计一般分为 6 个阶段，分别是需求分析、概念数据库设计、逻辑数据库设计、物理数据库设计、数据库实施、数据库运行和维护。

🐬需求分析

需求分析阶段，数据库设计人员需要分析用户的需求，将分析结果记录下来，形成需求分析报告。

在这个阶段中，双方需要进行深入的沟通，以避免理解不准确导致后续的工作出现问题。

在需求分析中有许多琐碎、耗时的工作，常见的工作如下：

• 收集数据
• 解决冲突，包括命名冲突(同名异义、异名同义）、属性冲突、结构冲突
• 为数据形成一些标准

🐬概念数据库设计

在概念数据库设计阶段，将对用户的需求进行综合、归纳、抽象，形成概念模型。

概念模型使设计人员摆脱数据库系统的具体技术问题，将精力集中在分析数据及数据之间联系等方面。一般通过绘制E-R图，直观呈现数据库设计人员对用户需求的理解。

🐬逻辑数据库设计

逻辑数据库设计面向数据库系统，在概念数据库设计中完成 E-R 图等成果后，将其转换为 DBMS 支持的数据模型(如关系模型)，完成实体、属性和联系的转换。

在进行逻辑数据库设计时，应遵循一些规范化理论，如范式。不规范的设计可能会导致数据库出现大量冗余、插入异常、删除异常等问题。

🐬物理数据库设计

物理数据库设计阶段需要确定数据库的存储结构、文件类型等。

通常DBMS为了保证其独立性与可移植性，承担了大部分任务，数据库设计人员只需要考虑硬件、操作系统的特性，为数据表选择合适的存储引擎，为字段选择合适的数据类型等，以及评估磁盘空间需求等工作。

🐬数据库实施

数据库实施就是将前面那些工作的成果实施起来，比如使用SQL语句创建数据库、数据表，编写与调试应用程序等。

🐬数据库运行和维护

数据库运行和维护就是将数据库系统正式投入运行，在运行后进行一些维护、调整、备份、升级等工作。

数据库设计范式

数据库设计对数据的存储性能、数据的操作都有很大的关系。为了避免不规范的数据库出现数据冗余，造成插入、删除、更新操作异常等情况，就要满足一定的规范化要求，这就是范式(Normal Form)。

根据要求的程度不同，范式有多种级别，最常用的有第一范式(1NF)、第二范式(2NF) 和 第三范式(3NF)，它们由 Edgar Frank Codd 于1971年相继提出。后来，又有人提出了 Boyce-Codd 范式(BCNF)、第四范式(4NF) 和 第五范式(5NF)等。

一般来说，数据库设计只需满足第三范式(3NF)就可以了。

第一范式

第一范式(1NF) 是指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值，或不能有重复的属性。

简而言之，第一范式遵从原子性，属性不可再分。

🐬演示

❌不满足第一范式的情况。

👉表格中倾斜的字体为主键。

👉表1 的问题在于 “联系方式” 包含了多个值，可以再细分；表2 的问题在于 “手机号” 属性重复。

✔️为了满足第一范式，应将用户和联系方式分成两个表保存，两个表是一对多的联系，如下图所示。

第二范式

第二范式(2NF) 是在第一范式的基础上建立起来的，满足第二范式必须先满足第一范式。

第二范式要求实体的属性完全依赖于主键，不能仅依赖主键的一部分(对于复合主键而言)。

简而言之，第二范式遵从唯一性，非主键字段需完全依赖主键。

🐬演示

❌不满足第二范式的情况。

👉表格中倾斜的字体为主键。

👉在表6中，用户编号和订单编号组成了复合主键，付款状态完全依赖复合主键，而用户名只依赖用户编号。

👉采用上述方式设计的用户表存在如下问题：

• 插入异常：如果一个用户没有下过订单，则该用户无法插入；
• 删除异常：如果删除一个用户所有的订单，则该用户也会被删除；
• 更新异常：由于用户名冗余，修改一个用户时需要修改多条记录。如果稍有不慎，漏改某些记录，会出现更新异常。

✔️为了满足第二范式，将复合主键移动到订单表中，如下图所示。

第三范式

第三范式(3NF)是在第二范式的基础上建立起来的，即满足第三范式必须先满足第二范式。

第三范式要求一个数据表中每一列数据都和主键直接相关，而不能间接相关。

简而言之，第三范式就是非主键字段不能相互依赖。

🐬演示

❌不满足第三范式的情况。

👉表格中倾斜的字体为主键。

👉在表9中，用户享受的折扣与用户等级相关，两者存在依赖关系。

👉采用这种方式设计的用户表存在如下问题：

• 插入异常：新插入用户的等级如果在1、2之外，其享受的折扣无处参考；
• 删除异常：如果删除某个等级下所有的用户，该等级对应的折扣也被删除；
• 更新异常：如果修改某个用户的等级，折扣也必须随之修改；如果修改某个等级的折扣，又因为折扣存在冗余，容易发生漏改。

✔️为了满足第三范式，将等级与折扣拆分到单独的表中，如下图所示。

单表操作

复制表结构和数据

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] 表名 LIKE 旧表名;

在上述语法中，仅能从 旧表名 中复制一份相同的表结构，但不会复制表中保存的数据。

🐬复制已有的表数据

数据复制也可称为蠕虫复制，是新增数据的一种方式，它是从已有的数据中获取数据，并且将获取到的数据插入到对应的数据表中，实现成倍的增加。

此种方式获取数据与插入数据的表结构要相同，否则可能会遇到插入不成功的情况。

INSERT [INTO] 数据表名1 [(字段列表)] SELECT [(字段列表)] FROM 数据表名2;

❗若数据表中含有主键，而主键又具有唯一性，所以在数据复制时还要考虑主键冲突的问题。

临时表的使用

临时表指的是一种仅在当前会话中可见，并在当前会话关闭时自动删除的数据表。它主要用于临时存储数据。

临时表的语法很简单，只需在CREATE与TABLE关键宇中间添加 TEMPORARY 即可。

# 方式1：创建临时表CREATE TEMPORARY TABLE mydb.tem1 (id INT);#方式2：创建临时表CREATE TEMPORARY TABLE mydb.tem2 SELECT id, name FROM mydb.goods ;

在上迷语句中，创建临时表时指定的数据库可以是 MySQL 服务器中存在的数据库，也可以是不存在的数据库。

若数据库不存在，操作临时表时必须使用 数据库.临时表名 指定临时表所在的数据库。

临时表中数据的操作与普通表相同，都可以进行 SELECT、INSERT、UPDATE 和 DELETE 操作。

SHOW TABLES 不能查看指定数据库下有哪些临时表，并且临时表的表名必须使用 ALTER TABLE 修改，而不能使用 RENAME TABLE … TO 修改。

解决主键冲突

在对数据表插人数据时，若表中的主键含有实际的业务意义，因此在插入数据时若不能确定对应的主键是否存在，往往会出现主键冲突的情况。

例如，mydb.my_goods 表经过数据复制以后，再插入编号为 20 的商品信息(橡皮，文具类，用于修正书写错误），具体SQL语句及执行结果如下。

INSERT INTO mydb.my_goods(id, name, content, keyword) VALUES (20, '橡皮', '用于修正书写错误', '文具');ERROR 1062 (23000): Duplicate entry '20' for key 'PRIMARY' 

从上述的执行结果可知，系统提示插入数据的主键发生冲突。若要解决这类问题，MySQL中提供了两种方式，分别为主键冲突更新和主键冲突替换。

🐬主键冲突更新

主键冲突更新操作指的是，当插人数据的过程中若发生主键冲突，则插入数据操作利用更新的方式实现。

INSERT [INTO] 数据表名 [(字段列表)] {VALUES | VALUE} (值列表) ON DUPLICATE KEY UPDATE 字段名1 = 新值1 [,字段名2 = 新值2] ...;

在 INSERT 语句后添加 ON DUPLICATE KEY UPDATE 可在发生主键冲突时，更新此条记录中通过 字段名1 = 新值1 [,字段名2 = 新值2] ... 设置的字段名对应的新值。

例如，修改以上发生主键冲突的插入语句。

INSERT INTO mydb.my_goods(id, name, content, keyword) VALUES (20, '橡皮', '用于修正书写错误', '文具') ON DUPLICATE KEY UPDATE name = '橡皮', content = '用于修正书写错误', keyword = '文具';

🐬主键冲突替换

主键冲突替换操作指的是，当插入数据的过程中若发生主键冲突，则删除此条记录，并重新插入。

REPLACE [INTO] 数据表名 [(字段列表)] {VALUES | VALUE} (值列表) [,(值列表)] ...;

REPLACE 语句与 INSERT 语句的使用类似，区别在于前者每执行一次就会发生两个操作(删除记录和插入记录)。

具体SQL语句及执行结果如下。

REPLACE INTO mydb.my_goods(id, name, content, keyword) VALUES (20, '橡皮', '用于修正书写错误', '文具');

清空数据

TRUNCATE [TABLE] 表名;

TRUNCATE 操作虽然与 DELETE 语句的使用非常相似，但是两者在本质上有一定的区别，具体如下。

• 实现方式不同

  • TRUNCATE 本质上先执行删除 (DROP) 数据表的操作，然后再根据有效的表结构文件 (.frm) 重新创建数据表的方式来实现数据清空操作。
  • DELETE 语句则是逐条地删除数据表中保存的记录。

• 执行效率不同

  • 在针对大型数据表(如千万级的数据记录)时，TRUNCATE 清空数据的实现方式决定了它比 DELETE 语句删除数据的方式执行效率更高。

• 对 AUTO_INCREMENT 的字段影响不同

  • TRUNCATE 清空数据后，再次向表中添加数据，自动增长字段会从默认的初始值重新开始。
  • 使用DELETE 语句删除表中的记录时，则不影响自动增长值。

• 删除数据的范围不同

  • TRUNCATE 语句只能用于清空表中的所有记录，而 DELETE 语句可通过 WHERE 指定删除满足条件的部分记录。

• 返回值含义不同

  • TRUNCATE 操作的返回值一般是无意义的，而 DELETE 语句则会返回符合条件被删除的记录数。

• 所属SQL语言的不同组成部分
  -DELETE语句属于DML数据操作语句，而 TRUNCATE 通常被认为是 DDL 数据定义语句。

当删除的数据量很小时，DELETE 的执行效率要比 TRUNCATE 高；只有删除的数据量很大时，才能看出 TRUNCATE 的执行效率比 DELETE 高。

去除重复记录

SELECT select选项 字段列表 FROM 数据表

select选项 默认值为 All，表示保存所有查询到的记录；当设置为 DISTINCT 时，表示去除重复记录，只保留一条。

❗当查询记录的字段有多个时，必须所有字段的值完全相同才被认为是重复记录。

排序

🐬单字段排序

单字段排序指的是查询时仅按照一个指定字段进行升序或降序排序。

SELECT [* | (字段列表)] FROM 数据表名 ORDER BY 字段名 [ASC | DESC];

ASC 表示升序，DESC 表示降序，而 ORDER BY 默认值为 ASC。

🐬多字段排序

当在开发中需要根据多个条件对查询的数据进行排序时，可以采用多字段排序。

SELECT [* | (字段列表)] FROM 数据表名 ORDER BY 字段名1 [ASC | DESC] [, 字段名2 [ASC | DESC]];

多字段排序首先按照 字段名1 进行排序，当字段1 的值相同时，再按照 字段名2 进行排序，依此类推。

数据表的字符集是 utf8，当排序的字段为中文时，默认不会按照中文拼音的顺序排序。那么在不改变数据表结构的情况下，可以使用 CONVERT(字段名 USING gbk) 函数强制让指定的字段按中文排序。

在按照指定字段进行升序排列时，如果某条记录的字段值为 NULL，则系统会将 NULL 看作是最小的值，从而将其显示在查询结果中的 第一条 记录的位置。

限量

对于一次性查询出的大量记录，不仅不便于阅读查看，MySQL中提供了一个关键字 LIMIT，可以限定记录的数量，也可以指定查询从哪一条记录开始。

SELECT [select选项] 字段列表 FROM 数据表名 [WHERE条件表达式] [ORDER BY 字段 ASC | DESC] LIMIT [OFFSET,] 记录数;

记录数 表示限定获取的最大记录数量，也就是说，在记录数大于数据表符合要求的实际记录数量时，以实际记录数为准。

当 LIMIT 后仅含有此参数时，表示从第1条记录开始获取。

OFFSET 表示偏移量，用于设置从哪条记录开始，MySQL中默认第1条记录的偏移量值为0，第2条记录的偏移量值为1，依此类推。

🐬排序后限量更新或删除数据

在MySQL中除了对查询记录进行排序和限量外，对数据表中记录的更新与删除操作也可以进行排序和限量。

# 数据更新的排序与限量UPDATE 数据表名 SET 字段 = 新值, ... [WHERE 条件表达式] ORDER BY 字段 ASC|DESC LIMIT 记录数;#数据删除的排序与限量DELETE FROM 数据表名[WHERE 条件表达式] ORDER BY 字段 ASC|DESC LIMIT 记录数;

UPDATE和 DELETE 操作中添加 ORDER BY 表示根据指定的字段，按顺序更新或删除符合条件的记录。如果 UPDATE 和 DELETE 操作没有添加 WHERE 条件，则可以使用 LIMIT 来限制更新和删除的数量。

例如，为sh_goods表中价格最便宜的两种商品库存设置为500。

UPDATE sh_goods SET stock = 500 ORDER BY price ASC LIMIT 2;

别名

在MySQL中执行查询操作时，可以根据具体情况为获取的字段设置别名。

SELECT 字段1 [AS] 别名1, 字段2 [AS] 别名2 [, ...] FROM 表名;

AS 用于为其前面的字段、表达式、函数等设置别名。也可以省略AS使用空格代替。

在MySQL中还可以使用 AS 为数据表设置别名。

SELECT 表别名.字段 [, ...] FROM 表名 [AS] 表别名

同样地，在为数据表设置别名时，AS也可以省略，使用空格代替。

字段与表设置别名后，在排序和分组中可以使用原来的字段名等，也可以使用别名。表的别名主要在多表查询中使用。

聚合函数

常用的聚合函数

COUNT()、SUM()、AVG()、MAX()、MIN() 和 GROUP_CONCAT() 函数中可以在参数前添加DISTINCT，表示对不重复的记录进行相关操作。

COUNT() 的参数设置为 * 时，表示统计符合条件的所有记录（包含NULL)。

分组

分组统计

在查询数据时，在 WHERE 条件后添加 GROUP BY 即可根据指定的字段进行分组。

SELECT [select选项] 字段列表 FROM 数据表名 [WHERE 条件表达式] GROUP BY 字段名;

分组排序

在MySQL中，默认情况下为分组操作的字段提供了升序排序的功能，因此在分组时可以为指定的字段进行升序或降序排序。

SELECT [select选项] 字段列表 FROM 数据表名 [WHERE 条件表达式] GROUP BY 字段名[ASC | DESC];

GROUP BY 分组排序的实现不需要使用 ORDER BY，直接在分组字段后添加 ASC(升序，默认值可省略) 或 DESC(降序) 即可。

多分组统计

在对数据进行分组统计时，MySQL中还支持数据按照某个字段进行分组后，对已经分组的数据进行再次分组的操作，以实现多分组统计。

SELECT [select选项] 字段列表 FROM 数据表名 [WHERE 条件表达式] GROUP BY 字段名1 [ASC | DESC],[字段名2 [ASC |DESC]]...;

查询出的数据首先按照字段1进行分组排序，再将字段1相同的结果按照字段2进行分组排序，依此类推。

回溯统计

回溯统计可以简单地理解为在根据指定字段分组后，系统又自动对分组的字段向上进行了一次新的统计并产生一个新的统计数据，且该数据对应的分组字段值为 NULL。

回溯统计的实现很简单，只需要在 GROUP BY 字段 后添加 WITH ROLLUP 即可。

SELECT [select 选项] 字段列表 FROM 数据表名 [WHERE 条件表达式] GROUP BY 字段名1 [ASC |DESC],[字段名2 [ASC | DESC]] ... WITH ROLLUP;

下面查看 sh_goods 表中每个分类 category_id 下的商品数量为例演示回溯统计的使用。

SELECT category_id, COUNT(*) FROM sh_goods GROUP BY category_id WITH ROLLUP;+-------------+---------+| category_id | COUNT(*)|+-------------+---------+|3     |3   ||6  |1   ||   NUll   |     4   |+-------------+---------+

从上述执行结果可知，在获取每种商品分类 category_id 下的商品数量后，系统又自动对获取的数量进行了一次累加统计，并且此累加的新数据(如4)对应的分组字段(如category_id)的值为NULL。此行的记录就是对category_id分组的一次回溯统计。

在了解了单字段的分组回溯统计后，演示如何在MySQL中对多分组进行回溯统计。

SELECT score, comment_count, COUNT(*) FROM sh_goods GROUP BY score, comment_count WITH ROLLUP;+-------+---------------+----------+| score | comment_count | COUNT(*) |+-------+---------------+----------+| 2.5   |200     | 1     || 2.5   |NUll  |  1   || 3.9   |500|    2     || 3.9   |NUll |    2     || NUll  |NUll |    3     |+-------+---------------+----------+

在上述SQL语句中，分组操作根据 GROUP BY 后的字段从前往后依次执行，即先按 score 分组，然后再按 comment_count 分组；

数据分组后系统再进行回溯统计，它与分组的操作正好相反，从 GROUP BY 后最后一个指定的分组字段开始回溯统计，并将结果上报，然后根据上报结果依次向前一个分组的字段进行回溯统计，即先回溯统计 comment_count 分组的结果，再根据 comment_count 的回溯结果对 score 分组进行回溯统计。

从执行结果可看出，score 值相同的情况下，按 comment_count 分组后，实现了 2 次回溯统计，对应的分组字段 comment_count 含有 2 个 NULL 值，统计结果分别进行了累加；接着按上次的结果对score 分组字段进行了1次回溯统计，对应的分组字段 score 中含有一个NULL值，然后又对上一次的统计结果再次进行累加。

虽然回溯统计对数据的分析很有帮助，但是MySQL的同一个查询语句中回溯统计(WITH ROLLUP) 与排序(ORDER BY) 仅能出现一个。

统计筛选

当对查询的数据执行分组操作时，可以利用 HAVING 根据条件进行数据筛选，与WHERE功能相同，但是在实际运用时两者有一定的区别。

    👉 WHERE 操作是从数据表中获取数据，将数据从磁盘存储到内存中，而 HAVING 是对已存放到内存中的数据进行操作。

    👉 HAVING 位于GROUP BY 子句后，而 WHERE 位于 GROUP BY 子句之前。

    👉 HAVING 关键字后可以使用聚合函数，而 WHERE 则不可以。通常情况下，HAVING 关键字与GROUP BY 一起使用，对分组后的结果进行过滤。

SELECT [select选项] 字段列表 FROM 数据表名 [WHERE 条件表达式] GROUP BY 字段名 [ASC | DESC], ...[WITH ROLLUP] HAVING 条件表达式;

WHERE 条件之后的所有语句都是对内存中的数据进行操作。而 HAVING 则根据条件表达式对分组后的内容进行过滤。

运算符

在数据库操作中，数据的 SELECT、UPDATE 和 DELETE 等操作都可以使用条件表达式，用于获取、更新 或 删除 给定条件的数据。

算术运算符

算术运算符适用于数值类型的数据，通常应用在 SELECT 查询结果的字段中，在 WHERE 条件表达式中应用较少。

运算符两端的数据可以是真实的数据(如5)，或数据表中的字段(如price)，而参与运算的数据一般称之为操作数，操作数与运算符组合在一起统称为表达式(如5+2)。

在MySQL中可以直接利用SELECT查看数据的运算结果。

🐬无符号的加减乘法运算

在MySQL中，若运算符 + － 和 * 的操作数都是无符号整型，则运算结果也是无符号整型。

例如，商品表 sh_goods 中的 id 字段就是无符号整型，下面对 sh_goods 表中前2条记录的 id 进行加1、减1 以及 乘2 操作。

SELECT id, id+1, id-1, id*2 FROM sh_goods LIMIT ;+----+------+------+------+| id | id+1 | id-1 | id*2 |+----+------+------+------+|  1 |  2   |  0   |   2  ||  2 |  3   |  1   |   4  |+----+------+------+------+

🐬有符号的减法运算结果

MySQL中，默认情况下运算符 － 的操作数若都为无符号整型，则结果一定是无符号的整型、若操作数的差值为负数，那么系统就会报错。

例如，对 sh_ goods 表中前2条记录的id执行减3操作。

SELECT id-3 FROM sh_goods LIMIT 2;ERROR 1690 (22003): BIGINI UNSIGNED value is out of range in '('shop'.'sh_goods'.'id' -3)'

从上述执行结果可知，减法运算的结果已经超出了无符号 BIGINT 的最大范围。因此，无论运算符 － 的操作数是否含有符号，若要获得一个有符号的运算结果，可以使用 CAST(... AS SIGNED） 将无符号整型 id 强制转换为有符号的整型数据。

SELECT CAST(id AS SIGNED)-3 FROM sh_goods LIMIT 2;+----------------------+| CAST(id AS SIGNED)-3 |+----------------------+|  -2 ||-1 |+----------------------+

上述执行结果，-2 和 －1 中的符号 －，表示负号，它是一个一元操作符，仅有一个操作数，如2。而运算符 － 表示减法运算时，是一个二元操作符，有两个操作数，如 id-3 中的 id 和3。

🐬含有精度的运算

算术运算除了可以对整数运算外，还可以对浮点数进行运算。

在对浮点数进行加减运算时，运算结果中的精度(小数点后的位数)等于参与运算的操作数的最大精度。

例如 1.2+1.400，1.400 的精度最大为 3，则运算结果的精度就为 3；在对浮点数进行乘法运算时，运算结果中的精度，以参与运算的操作数的精度和为准。例如 1.2*1.400，1.2 的精度为 1，1.400 的精度为3，则运算结果中的精度就为4(1+3)。

🐬/ 运算

运算符 / 在MySQL中用于除法操作，且运算结果使用浮点数表示，浮点数的精度等于被除数( / 运算符左侧的操作数) 的精度加上系统变量 div_precision_increment 设置的除法精度增长值，读者可通过以下SQL语句查找其默认值。

SHOW VARIABLES LIKE 'div_precision_increment'+-------------------------+-------+| Variable_name           | Value |+-------------------------+-------+| div_precision_increment |   4   |+-------------------------+-------+

🐬NULL参与算术运算

在算术运算中，NULL是一个特殊的值，它参与的算术运算结果均为NULL。

🐬DIV与MOD运算符

在MySQL中，运算符DIV 与 / 都能实现除法运算，区别在于前者的除法运算结果会去掉小数部分，只返回整数部分。

MySQL中的运算符 MOD 与 % 功能相同，都用于取模运算。

❗取模运算结果的正负与被模数(%左边的操作数)的符号相同，与模数(%右边的操作数)符号无关。

MySQL中还提供了很多进行数学运算的函数，常用的数学函数如下。

RAND() 函数用于返回 0 到 1 之间的随机数，若要获取指定区间 (min≤num 内的随机数时,使用表达式 FLOOR(min+RAND()*(max-min)) 获取。

BETWEEN 条件1 AND 条件2

SELECT id, name, content, FROM sh_goods WHERE content REGEXP '人|必备';

SELECT id, name, price FROM sh_goods WHERE keyword = '电子产品' && score = 5;

SELECT id, name, price FROM sh_goods WHERE (keyword, score) = ('电子产品', 5);

SELEC ... UNION [ALL | DISTINCT] SELECT ... [UNION [ALL | DISTINCT] SELECT ...];

SELECT 查询字段 FROM 表1 CROSS JOIN 表2;

SELECT 查询字段 FROM 表1 [INNER] JOIN 表2 ON 匹配条件;

SELECT 查询字段 FROM 表1 LEFT [OUTER] JOIN 表2 ON 匹配条件;

SELECT 查询字段 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 匹配条件;

SELECT 查询字段 FROM 表1 [CROSS | INNER | LEFT | RIGHT] JOIN 表2 USING(同名的联结字段);

WHERE 条件判断 {= | <>} (SELECT 字段名 FROM 数据源 [WHERE] [GROUP BY] [HAVING] [ORDER BY][LIMIT]);

WHERE 条件判断 {IN | NOT IN} (SELECT 字段名 FROM 数据源 [WHERE] [GROUP BY] [HAVING] [ORDER BY] [LIMIT]);

WHERE (指定字段名1, 指定字段名2, ...) =(SELECT 字段名1, 字段名2, ... FROM 数据源 [WHERE] [GROUP BY] [HAVING] [ORDER BY] [LIMIT]);

SELECT 字段列表 FROM (SELECT 语句) [AS] 别名 [WHERE] [GROUP BY] [HAVING] [ORDER BY] [LIMIT];

WHERE EXISTS (子查询语句);

WHERE 表达式 比较运算符 ANY (子查询语句);

WHERE 表达式 比较运算符 ALL(子查询语句);

[CONSTRAINT symbol] FOREIGN KEY [index_name] (index_col_name, ...)REFERENCES tbl_name (index_col_name, ...)[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}][ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]

# 1.在mydb数据库下创建主表CREATE TABLE mydb.department (id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '部门编号', name VARCHAR(50) NOT NULL COMMENT'部门名称') DEFAULT CHARSET=utf8;# 2.在mydb数据库下创建从表，添加外键约束CREATE TABLE mydb.employees (id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '员工编号', name VARCHAR(120) NOT NULL COMMENT '员工姓名', dept_id INT UNSIGNED NOT NULL COMMENT '部门分类编号',CONSTRAINT FK_ID FOREIGN KEY(dept_id) REFERENCES department (id) ON DELETE RESTRICT ON UPDATE CASCADE) DEFAULT CHARSET=utf8;

ALTER TABLE mydb.employeesADD CONSTRAINT FK_ID FOREIGN KEY (dept_id) REFERENCES department (id) ON DELETE RESTRICT ON UPDATE CASCADE;

DESC mydb.employees dept_id;+---------+------------------+------+-----+---------+-------+|  Field  |       Type       | NULL | Key | Default | Extra |+---------+------------------+------+-----+---------+-------+| dept_id | int(10) unsigned |  NO  | MUL |   NULL  |       |+---------+------------------+------+-----+---------+-------+

SHOW CREATE TABLE mydb.employees\G*************1.row*************Table: employeesCreate Table: CREATE TABLE 'employees' (`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '员工编号',`name` varchar(120) NOT NULL COMMENT '员工姓名',`dept_id` int(10) unsigned NOT NULL COMMENT '商品分类编号',PRIMARY KEY (`id`),KEY `FK_ID` (`dept_id`),CONSTRAINT FK_ID FOREIGN KEY (`dept_id`) REFERENCES `department` (`id`) ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)

ALTER TABLE 表名 DROP FOREIGN KEY 外键名;

ALTER TABLE mydb.employees  DROP FOREIGN KEY FK_ID;

DESC mydb.employees dept_id;+---------+------------------+------+-----+---------+-------+|  Field  |       Type       | NULL | Key | Default | Extra |+---------+------------------+------+-----+---------+-------+| dept_id | int(10) unsigned |  NO  | MUL |   NULL  |       |+---------+------------------+------+-----+---------+-------+

SHOW CREATE TABLE mydb.employees\G*************1.row*************Table: employeesCreate Table: CREATE TABLE 'employees' (`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '员工编号',`name` varchar(120) NOT NULL COMMENT '员工姓名',`dept_id` int(10) unsigned NOT NULL COMMENT '商品分类编号',PRIMARY KEY (`id`),KEY `FK_ID` (`dept_id`),CONSTRAINT FK_ID FOREIGN KEY (`dept_id`) REFERENCES `department` (`id`) ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)

ALTER TABLE mydb.employees  DROP KEY FK_ID;

SELECT host, user FROM mysql.user+-----------+----------------+| host      | user           |+-----------+----------------+| localhost | mysql.session  || localhost | mysql.sys      || localhost | root |+-----------+----------------+

SELECT plugin, authentication_string FROM mysql.user WHERE user = 'root'+-----------------------+-------------------------------------------+| plugin    | authentication_string |+-----------------------+-------------------------------------------+| mysql_native_password | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |+-----------------------+-------------------------------------------+

SHOW VARIABLES LIKE 'have_openssl';+---------------+-----------+| Variable_name | Value     |+---------------+-----------+| have_openssl  | DISABLED  |+---------------+-----------+

CREATE USER [IF NOT EXISTS]账户名 [用户身份验证选项] [, 账户名 [用户身份验证选项]] ...[WITH 资源控制选项] [密码管理选项 | 账户锁定选项]

CREATE USER 'test1';

SELECT host, user FROM mysql.user+-----------+----------------+| host      | user           |+-----------+----------------+| %         | text1          || localhost | mysql.session  || localhost | mysql.sys      || localhost | root |+-----------+----------------+

CREATE USER 'test2'@'localhost' IDENTIFIED BY '123456';

SELECT plugin, authentication_string FROM mysql.user;+-----------------------+-------------------------------------------+| plugin    | authentication_string |+-----------------------+-------------------------------------------+| mysql_native_password | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |+-----------------------+-------------------------------------------+

CREATE USER 'test2'@'localhost'IDENTIFIED WITH 'mysql_native_password' BY '123456';

CREATE USER 'test2'@'localhost' IDENTIFIED BY '333333';'test3'@'localhost' IDENTIFIED BY '444444';

CREATE USER 'test5'@'localhost' IDENTIFIED BY '555555'WITH MAX_UPDATES_PER_HOUR 10;

SELECT max_updates FROM user WHERE user='test5';+-------------+| max_updates |+-------------+|  10  |+-------------+

CREATE USER 'test6'@'localhost' IDENTIFIED BY '666666' PASSWORD EXPIRE INTERVAL 180 DAY;

CREATE USER 'test7'@'localhost' IDENTIFIED BY '777777' PASSWORD EXPIRE ACCOUNT LOCK;

SELECT account_locked FROM mysql.user WHERE user = 'test7';

# 第1种语法ALTER USER 账户名 IDENTIFIED BY '明文密码';# 第2种语法SET PASSWORD [FOR 账户名] = '明文密码';

ALTER USER 'test1'@'%' IDENTFIED BY '123456';

ALTER USER USER() IDENTFIED BY '000000';

SELECT CURRENT_USER();+----------------+| CURRENT_USER   |+----------------+| root@localhost |+----------------+

mysqladmin -u 用户名 [-h主机地址] -p password 新密码

mysqladmin -u root -p password 123456Enter password: 

ALTER USER [IF EXISTS]账户名 [用户身份验证选项], [账户名 [用户身份验证选项]] ...[WITH 资源限制选项] [密码管理选项 | 账户锁定选项]

ALTER USER test1IDENTIFIED WITH sha256_password BY '111111' PASSWORD EXPIRE;

ALTER USER 'test'@'localhost' ACCOUNT UNLOCK;

ALTER USER'test1' IDENTFIED WITH mysql_native_password,'test2'@'localhost' IDENTFIED BY '222222',WITH max_user_connections 2;

RENAME USER 旧用户名1 TO 新用户名1 [, 旧用户名2 TO 新用户名2] ...

DROP USER [IF EXISTS] 账户名 [, 账户名] ...;

DROP USER IF EXISTS test7;

GRANT权限类型 [字段列表] [, 权限类型 [字段列表]] ... ON [目标类型] 权限级别TO 账户名 [用户身份验证选项] [, 账户名[用户身份验证选项]] ...[REQUIRE 连接方式][WITH [GRANT OPTION | 资源控制选项}]

SHOW GRANTS FOR 'root'@'localhost';+--------------------------------------------------------------------+|Grants for root@localhost               |+--------------------------------------------------------------------+|GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION ||GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |+--------------------------------------------------------------------+

SHOW GRANTS FOR 'test1'@'%';+------------------------------------------+|Grants for test1@%     |+------------------------------------------+|GRANT ALL PRIVILEGES ON *.* TO 'test'@'%' |+------------------------------------------+

GRANT SELECT, INSERT (name, price) ON shop.sh_goods TO 'test1'@'%';

SELECT db, table_name, table_priv, column_priv FROM mysql.tables_priv WHERE user = 'test1';+-----+-----------+-----------+------------+|db   |table_name |table_priv |column_priv |+-----+-----------+-----------+------------+|shop |sh_goods   |Select     |Insert      |+-----+-----------+-----------+------------+

SELECT db, table_name, column_priv, column_priv FROM mysql.tables_priv WHERE user = 'test1';+-----+-----------+------------+------------+|db   |table_name |column_priv |column_priv |+-----+-----------+------------+------------+|shop |sh_goods   |name        |Insert      ||shop |sh_goods   |price       |Insert      |+-----+-----------+------------+------------+

REVOKE 权限类型 [(字段列表)] [, 权限类型[(字段列表)]] ...ON [目标类型] 权限级别 FROM 账户名[, 账户名] ...

REVOKE INSERT (name, price) ON shop.sh_goods FROM 'test'@'%';

# 回收数据、结构、管理所有的权限以及可为其他用户授权的权限REVOKE ALL [PRIVILEGES], GRANT OPTION FROM 账户名 [, 账户名] ...#回收用户的代理权限REVOKE PROXY ON 账户名 FROM 账户名1 [, 账户名2] ...

FLUSH PRIVILEGES;

# 方式1msyqladmin -uroot -p reload#方式2msyqladmin -uroot -p flush-privileges

CREATE [OR REPLACE] [ALGORITHM = (UNDEFINED | MERGE | TEMPTABLE)][DEEINER = { user | CURRENT_USER }][SQL SECURITY { DEFINER | INVOKER }]VIEW view_name [(column_list)]AS select_statement[WITH [CASCADED | LOCAL] CHECK OPTION]

CREATE VIEW view_goods_cate ASSELECT g.id, g.name, c.name category_name FROM sh_goodsLEFT JOIN sh_goods_category cON g.category_id = c.id;

CREATE VIEW view_goods_promo (sn, title, promotion_price) ASSELECT id, name, price * 0.8 FROM sh_goods;

# 创建测试用户 shop_testCREATE USER shop_test;# 创建第1个视图，权限控制使用默认值CREATE VIEW view_goods_t1 AS SELECT id, name FROM sh_goods LIMIT 1;# 创建第2个视图，设置 DEFINER 为 shop_test 用户CREATE DEFINER = 'shop_test' VIEW view_goods_t2 AS SELECT id, name FROM sh_goods LIMIT 1;# 创建第3个视图，设置 SQL SECURITY 为 INVOKERCREATE SQL SECURITY INVOKER VIEW view_goods_t3 AS SELECT id, name FROM sh_goods LIMIT 1;# 为shop_test用户赋予前面创建的3个视图的SELECT权限GRANT SELECT ON view_goods_t1 TO 'shop_test';GRANT SELECT ON view_goods_t2 TO 'shop_test';GRANT SELECT ON view_goods_t3 TO 'shop_test';

# 创建视图CREATE VIEW view_goods AS SELECT id, name, price FROM sh_goods;#修改以有视图CREATE OR REPLACE VIEW view_goods AS SELECT id, name FROM sh_goods;

ALTER [ALGORITHM = (UNDEFINED | MERGE | TEMPTABLE)][DEEINER = { user | CURRENT_USER }][SQL SECURITY { DEFINER | INVOKER }]VIEW view_name [(column_list)]AS select_statement[WITH [CASCADED | LOCAL] CHECK OPTION]

DROP VIEW [IF EXISTS] 视图名1 [,视图名2]...

# 创建第一个视图CREATE VIEW view_cate_t1 AS SELECT id, name, FROM sh_goods_category WHERE id < 30;# 创建第二个视图，使用CASCADED(级联)检查CREATE VIEW view_cate_t2 AS SELECT id, name, FROM view_cate_t1 WHERE id > 20 WITH CHECK OPTION;  #相当于 WITH CASCADED CHECK OPTION# 插入数据，id必须大于20小于30才可以插入成功# 创建第三个视图，使用LOCAL(本视图，非级联)检查CREATE VIEW view_cate_t3 AS SELECT id, name, FROM view_cate_t1 WHERE id > 20 WITH LOCAL CHECK OPTION; # 插入数据，只需满足id大于20即可插入

START TRANSACTION;

COMMIT;

ROLLBACK;

 SELECT  @@autocommit;

SET AUTOCOMMIT =0;

SAVEPOINT 保存点名;

ROLLBACK TO SAVEPOINT 保存点名;

RELEASE SAVEPOINT 保存点名;

COMMIT [AND [NO] CHAIN] [[NO] RELEASE]ROLLBACK [AND [NO] CHAIN] [[NO] RELEASE]

# 查看全局隔离级SELECT @global.transaction_isolation;# 查看当前会话中的隔离级SELECT @@session.transaction_isolation;# 查看下一个事务的隔离级SELECT @@transaction_isolation;

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL 参数值

# 设置只读事务SET [SESSION | GLOBAL] TRANSACTION READ ONLY# 恢复成读写事务SET [SESSION | GLOBAL] TRANSACTION READ WRITE