第1章 数据库概述
1.为什么要使用数据
使用数据库可以高效且条理分明地存储数据，它使人们能够更加迅速和方便地管理数据，主要体现在以下几个方面。
数据库可以结构化存储大量的数据信息，方便用户进行有效的检索和访问。
数据库可以对数据进行分类保存，并且能够提供快速的查询。例如，我们平时使用百度搜索内容
时，百度也是基于数据库和数据分类技术来达到快速搜索的目的。
数据库可以有效地保持数据信息的一致性、完整性、降低数据冗余。
可以很好地保证数据有效、不被破坏，而且数据库自身有避免重复数据的功能，以此来降低数据的
冗余。
数据库可以满足应用的共享和安全方面的要求，把数据放在数据库中在很多情况下也是出于安全的
考虑。
例如，如果把所有员工信息和工资数据都放在磁盘文件上，则工资的保密性就无从谈起。如果把员
工信息和工资数据放在数据库中，就可以只允许查询和修改员工信息，而工资信息只允许指定人（如财务人员）查看，从而保证数据的安全性。
数据库技术能够方便智能化地分析，产生新的有用信息。
例如，超市中把物品销售信息保存在数据库中，每个月销售情况的排名决定了下半月的进货数量。
数据库查询的结果实际上产生了新的数据信息。
数据挖掘、联机分析等技术近年来发展非常快，其核心意义在于从一堆数据中分析出有用的信息。
DB
：数据库（
Database
）
即存储数据的
“
仓库
”
，其本质是一个文件系统。它保存了一系列有组织的数据。
DBMS
：数据库管理系统（
Database Management System
）
是一种操纵和管理数据库的大型软件，用于建立、使用和维护数据库，对数据库进行统一管理和控
制。用户通过数据库管理系统访问数据库中表内的数据。
SQL
：结构化查询语言（
Structured Query Language
）
专门用来与数据库通信的语言。
生活中的例子：
银行和金融机构：银行和金融机构使用数据库来管理客户账户信息、交易记录、信用卡信息等。
社交媒体：社交媒体平台使用数据库来存储用户信息、帖子、评论、点赞等数据，并通过数据分析
来提供更好的服务。
电子商务：电子商务网站使用数据库来存储产品信息、订单记录、客户信息等，并使用数据分析来
优化运营和销售。
电子邮件：电子邮件服务提供商使用数据库来存储用户的邮件、附件、联系人等信息。
医疗保健：医疗机构使用数据库来存储患者的病历、检查结果、诊断、药物配方等信息。
运输和物流：物流公司使用数据库来跟踪货物的位置、交付状态、客户信息等。
政府和公共服务：政府和公共服务机构使用数据库来管理人口信息、税收记录、公共设施管理等。
重点说一说在学校教育方面，我认为数据库也有一些应用，以下是一些常见的例子：
学生信息管理：学校使用数据库来管理学生的个人信息、学习成绩、选修课程、考试记录等。
考试管理：学校使用数据库来管理考试时间、考场分配、考试题目、考试成绩等信息。
图书馆管理：学校图书馆使用数据库来管理图书、期刊、电子书等资源，包括借阅记录、还书记
录、图书状态等信息。
课程管理：学校使用数据库来管理课程信息、教师信息、上课时间表、选课记录等。
教育研究：学校使用数据库来存储和管理教育研究数据、调查结果、学生评估结果等信息
2.数据库与数据库管理系统
2.1 数据库的相关概念
2.2 数据库与数据库管理系统的关系
数据库管理系统
(DBMS)
可以管理多个数据库，一般开发人员会针对每一个应用创建一个数据库。为保存
应用中实体的数据，一般会在数据库创建多个表，以保存程序中实体用户的数据。
数据库管理系统、数据库和表的关系如图所示：
2.3
常见的数据库管理系统排名
(DBMS)
目前互联网上常见的数据库管理软件有
Oracle
、
MySQL
、
MS SQL Server
、
DB2
、
PostgreSQL
、
Access
、
Sybase
、
Informix
这几种。以下是
2021
年
DB-Engines Ranking
对各数据库受欢迎程度进行调
查后的统计结果
:
（查看数据库最新排名
:
https://db-engines.com/en/ranking
）
对应的走势图：（
https://db-engines.com/en/ranking_trend
）
2.4 常见的数据库介绍
Oracle
1979
年，
Oracle 2
诞生，它是第一个商用的
RDBMS
（关系型数据库管理系统）。随着
Oracle
软件的
名气 越来越大，公司也改名叫
Oracle
公司。
2007
年，总计
85
亿美金收购
BEA Systems
。
2009
年，总计
74
亿美金收购
SUN
。此前的
2008
年，
SUN
以
10
亿美金收购
MySQL
。意味着
Oracle
同时拥
有了
MySQL
的管理权，至此
Oracle
在数据库领域中成为绝对的领导者。
2013
年，甲骨文超越
IBM
，成为继
Microsoft
后全球第二大软件公司。
如今
Oracle
的年收入达到了
400
亿美金，足以证明商用（收费）数据库软件的价值。
SQL Server
SQL Server
是微软开发的大型商业数据库，诞生于
1989
年。
C#
、
.net
等语言常使用，与
WinNT
完全集
成，也可以很好地与
Microsoft BackOffice
产品集成。
DB2
IBM
公司的数据库产品
,
收费的。常应用在银行系统中。
PostgreSQL
PostgreSQL
的稳定性极强，最符合
SQL
标准，开放源码，具备商业级
DBMS
质量。
PG
对数据量大的文本
以 及
SQL
处理较快。
SyBase
已经淡出历史舞台。提供了一个非常专业数据建模的工具
PowerDesigner
。
SQLite
嵌入式的小型数据库，应用在手机端。 零配置，
SQlite3
不用安装，不用配置，不用启动，关闭或者配置
数据库实例。当系统崩溃后不用做任何恢复操作，再下次使用数据库的时候自动恢复。
informix
IBM
公司出品，取自
Information
和
Unix
的结合，它是第一个被移植到
Linux
上的商业数据库产品。仅运
行
于
unix/linux
平台，命令行操作。 性能较高，支持集群，适应于安全性要求极高的系统，尤其是银行，
证 券系统的应用。
国产数据库排名
以下数据参考自墨天轮：
[
https://www.modb.pro/dbRank
]
3.MySQL 介绍3.1 概述
MySQL
是一个 开放源代码的关系型数据库管理系统 ，由瑞典
MySQL AB
（创始人
Michael
Widenius)
公司
1995
年开发，迅速成为开源数据库的
No.1
。
2008
被
Sun
收购（
10
亿美金），
2009
年
Sun
被
Oracle
收购。
MariaDB
应运而生。（
MySQL
的创
造者担心
MySQL
有闭源的风险，因此创建了
MySQL
的分支项目
MariaDB
）
MySQL6.x
版本之后分为 社区版 和 商业版 。
MySQL
是一种关联数据库管理系统，将数据保存在不同的表中，而不是将所有数据放在一个大仓库
内，这样就增加了速度并提高了灵活性。
MySQL
是开源的，所以你不需要支付额外的费用。
MySQL
是可以定制的，采用了
GPL
（
GNU General Public License)
协议，你可以修改源码来 开发
自己的
MySQL
系统。
MySQL
支持大型的数据库。可以处理拥有上千万条记录的大型数据库。
MySQL
支持大型数据库，支持
5000
万条记录的数据仓库，
32
位系统表文件最大可支持
4GB
，
64
位
系统支持最大的表文件为
8TB
。
MySQL
使用 标准的
SQL
数据语言 形式。
MySQL
可以允许运行于多个系统上，并且支持多种语言。这些编程语言包括
C
、
C++
、
Python
、
Java
、
Perl
、
PHP
和
Ruby
等。
3.2 MySQL
发展史重大事件
MySQL
的历史就是整个互联网的发展史。互联网业务从社交领域、电商领域到金融领域的发展，推动着
应用对数据库的需求提升，对传统的数据库服务能力提出了挑战。高并发、高性能、高可用、轻资源、
易维护、易扩展的需求，促进了
MySQL
的长足发展。
3.3
关于
MySQL 8.0
MySQL
从
5.7
版本直接跳跃发布了
8.0
版本 ，可见这是一个令人兴奋的里程碑版本。
MySQL 8
版本在功能
上 做了显著的改进与增强，开发者对
MySQL
的源代码进行了重构，最突出的一点是多
MySQL Optimizer
优化 器进行了改进。不仅在速度上得到了改善，还为用户带来了更好的性能和更棒的体验。
3.4 Why choose MySQL?
为什么如此多的厂商要选用
MySQL
？大概总结的原因主要有以下几点：
1.
开放源代码，使用成本低。
2.
性能卓越，服务稳定。
3.
软件体积小，使用简单，并且易于维护。
4.
历史悠久，社区用户非常活跃，遇到问题可以寻求帮助。
5.
许多互联网公司在用，经过了时间的验证。
3.5 Oracle vs MySQL
Oracle
更适合大型跨国企业的使用，因为他们对费用不敏感，但是对性能要求以及安全性有更高的要
求。
MySQL
由于其
体积小、速度快、总体拥有成本低，可处理上千万条记录的大型数据库，尤其是开放源码
这一特点，使得很多互联网公司、中小型网站选择了
MySQL
作为网站数据库（
Facebook
，
Twitter
，
YouTube
，阿里巴巴
/
蚂蚁金服，去哪儿，美团外卖，腾讯）。
4. RDBMS
与非
RDBMS
从排名中我们能看出来，关系型数据库绝对是
DBMS
的主流，其中使用最多的
DBMS
分别是
Oracle
、
MySQL
和
SQL Server
。这些都是关系型数据库（
RDBMS)
。
4.1
关系型数据库
(RDBMS)
4.1.1
实质
这种类型的数据库是 最古老 的数据库类型，关系型数据库模型是把复杂的数据结构归结为简单的二
元关系 （即二维表格形式）。
关系型数据库以 行
(row)
和 列
(column)
的形式存储数据，以便于用户理解。这一系列的行和列被为
表
(table)
，一组表组成了一个库
(database)
。
表与表之间的数据记录有关系
(relationship)
。现实世界中的各种实体以及实体之间的各种联系均用
关系模型来表示。关系型数据库，就是建立在 关系模型 基础上的数据库。
SQL
就是关系型数据库的查询语言。
4.1.2
优势
复杂查询
可以用
SQL
语句方便的在一个表以及多个表之间做非常复杂的数据查询。
事务支持
使得对于安全性能很高的数据访问要求得以实现。
4.2
非关系型数据库
(
非
RDBMS)
4.2.1
介绍
非关系型数据库
，可看成传统关系型数据库的功能 阉割版本 ，基于键值对存储数据，不需要经过
SQL
层
的解析， 性能非常高 。同时，通过减少不常用的功能，进一步提高性能。 目前基本上大部分主流的非关
系型数据库都是免费的。
4.2.2
有哪些非关系型数据库
相比于
SQL
，
NoSQL
泛指非关系型数据库，包括了榜单上的键值型数据库、文档型数据库、搜索引擎和
列存储等，除此以外还包括图形数据库。也只有用
NoSQL
一词才能将这些技术囊括进来。
键值型数据库
键值型数据库通过
Key-Value
键值的方式来存储数据，其中
Key
和
Value
可以是简单的对象，也可以是
复 杂的对象。
Key
作为唯一的标识符，优点是查找速度快，在这方面明显优于关系型数据库，缺点是无
法 像关系型数据库一样使用条件过滤（比如
WHERE
），如果你不知道去哪里找数据，就要遍历所有的
键， 这就会消耗大量的计算。
键值型数据库典型的使用场景是作为 内存缓存 。
Redis
是最流行的键值型数据库。
文档型数据库
此类数据库可存放并获取文档，可以是
XML
、
JSON
等格式。在数据库中文档作为处理信息的基本单位，
一个文档就相当于一条记录。文档数据库所存放的文档，就相当于键值数据库所存放的
“
值
”
。
MongoDB
是最流行的文档型数据库。此外，还有
CouchDB
等。
搜索引擎数据库
虽然关系型数据库采用了索引提升检索效率，但是针对全文索引效率却较低。搜索引擎
数据库是应用在 搜索引擎领域的数据存储形式，由于搜索引擎会爬取大量的数据，并以特定的格式进行
存储，这样在检 索的时候才能保证性能最优。核心原理是
“
倒排索引
”
。
典型产品：
Solr
、
Elasticsearch
、
Splunk
等。
列式数据库
列式数据库是相对于行式存储的数据库，
Oracle
、
MySQL
、
SQL Server
等数据库都是采用的行式存储
（
Row-based
），而列式数据库是将数据按照列存储到数据库中，这样做的好处是可以大量降低系统的
I/O
，适合于分布式文件系统，不足在于功能相对有限。典型产品：
HBase
等。
图形数据库
图形数据库，利用了图这种数据结构存储了实体（对象）之间的关系。图形数据库最典型的例子就是社
交网络中人与人的关系，数据模型主要是以节点和边（关系）来实现，特点在于能高效地解决复杂的关
系问题。
图形数据库顾名思义，就是一种存储图形关系的数据库。它利用了图这种数据结构存储了实体（对象）
之间的关系。关系型数据用于存储明确关系的数据，但对于复杂关系的数据存储却有些力不从心。如社
交网络中人物之间的关系，如果用关系型数据库则非常复杂，用图形数据库将非常简单。典型产品：
Neo4J
、
InfoGrid
等。
4.2.3 NoSQL
的演变
由于
SQL
一直称霸
DBMS
，因此许多人在思考是否有一种数据库技术能远离
SQL
，于是
NoSQL
诞生
了， 但是随着发展却发现越来越离不开
SQL
。到目前为止
NoSQL
阵营中的
DBMS
都会有实现类似
SQL
的功能。下面是
“NoSQL”
这个名词在不同时期的诠释，从这些释义的变化中可以看出
NoSQL
功能的演变
：
1970
：
NoSQL = We have no SQL
1980
：
NoSQL = Know SQL
2000
：
NoSQL = No SQL!
2005
：
NoSQL = Not only SQL
2013
：
NoSQL = No, SQL!
NoSQL
对
SQL
做出了很好的补充，比如实际开发中，有很多业务需求，其实并不需要完整的关系型数据
库功能，非关系型数据库的功能就足够使用了。这种情况下，使用 性能更高 、 成本更低 的非关系型数
据 库当然是更明智的选择。比如：日志收集、排行榜、定时器等。
4.3
小结
NoSQL
的分类很多，即便如此，在
DBMS
排名中，还是
SQL
阵营的比重更大，影响力前
5
的
DBMS
中
有
4
个是关系型数据库，而排名前
20
的
DBMS
中也有
12
个是关系型数据库。所以说，掌握
SQL
是非
常有 必要的。整套课程将围绕
SQL
展开。
5.
关系型数据库设计规则
关系型数据库的典型数据结构就是 数据表 ，这些数据表的组成都是结构化的（
Structured
）。 将
数据放到表中，表再放到库中。
一个数据库中可以有多个表，每个表都有一个名字，用来标识自己。表名具有唯一性。 表具有一
些特性，这些特性定义了数据在表中如何存储，类似
Java
和
Python
中
“
类
”
的设计。
5.1
表、记录、字段
E-R
（
entity-relationship
，实体
-
联系）模型中有三个主要概念是： 实体集 、 属性 、 联系集 。
一个实体集（
class
）对应于数据库中的一个表（
table
），一个实体（
instance
）则对应于数据库表
中的一行（
row
），也称为一条记录（
record
）。一个属性（
attribute)
对应于数据库表中的一列
（
column
），也称为一个字段（
field
）。
5.2
表的关联关系
表与表之间的数据记录有关系
(relationship)
。现实世界中的各种实体以及实体之间的各种联系均用
关系模型来表示。
四种：一对一关联、一对多关联、多对多关联、自我引用
5.2.1
一对一关联（
one-to-one
）
在实际的开发中应用不多，因为一对一可以创建成一张表。
举例：设计 学生表 ：学号、姓名、手机号码、班级、系别、身份证号码、家庭住址、籍贯、紧急
联系人、
...
拆为两个表：两个表的记录是一一对应关系。
基础信息表 （常用信息）：学号、姓名、手机号码、班级、系别
档案信息表 （不常用信息）：学号、身份证号码、家庭住址、籍贯、紧急联系人、
...
两种建表原则：
外键唯一：主表的主键和从表的外键（唯一），形成主外键关系，外键唯一。
外键是主键：主表的主键和从表的主键，形成主外键关系。
ORM
思想
(Object Relational Mapping)
体现：
数据库中的一个表
<---> Java
或
Python
中的一个类
表中的一条数据
<--->
类中的一个对象（或实体）
表中的一个列
<---->
类中的一个字段、属性
(field)
5.2.2
一对多关系（
one-to-many
）
常见实例场景： 客户表和订单表 ， 分类表和商品表 ， 部门表和员工表 。
举例：
员工表：编号、姓名、
...
、所属部门
部门表：编号、名称、简介
一对多建表原则：在从表
(
多方
)
创建一个字段，字段作为外键指向主表
(
一方
)
的主键
5.2.3
多对多（
many-to-many
）
要表示多对多关系，必须创建第三个表，该表通常称为 联接表 ，它将多对多关系划分为两个一对多关
系。将这两个表的主键都插入到第三个表中。
举例
1
：学生
-
课程
学生信息表 ：一行代表一个学生的信息（学号、姓名、手机号码、班级、系别
...
）
课程信息表 ：一行代表一个课程的信息（课程编号、授课老师、简介
...
）
选课信息表 ：一个学生可以选多门课，一门课可以被多个学生选择
学号 课程编号
1 1001
2 1001
1 1002
举例：产品
-
订单
“
订单
”
表和
“
产品
”
表有一种多对多的关系，这种关系是通过与
“
订单明细
”
表建立两个一对多关系来 定
义的。一个订单可以有多个产品，每个产品可以出现在多个订单中。
示例
产品表 ：
“
产品
”
表中的每条记录表示一个产品
订单表 ：
“
订单
”
表中的每条记录表示一个订单
订单明细表 ：每个产品可以与
“
订单
”
表中的多条记录对应，即出现在多个订单中。一个订单可
以与
“
产品
”
表中的多条记录对应，即包含多个产品
举例
3
：用户
-
角色
多对多关系建表原则：需要创建第三张表，中间表中至少两个字段，这两个字段分别作为外键指向
各自一方的主键。
5.2.4
自我引用
(Self reference)
第
2
章
MySQL
环境搭建
1.MySQL
的卸载
步骤
1
：停止
MySQL
服务
在卸载之前，先停止
MySQL8.0
的服务。按键盘上的
“Ctrl + Alt + Delete”
组合键，打开
“
任务管理器
”
对话
框，可以在
“
服务
”
列表找到
“MySQL8.0”
的服务，如果现在
“
正在运行
”
状态，可以右键单击服务，选择
“
停
止
”
选项停止
MySQL8.0
的服务，如图所示。
步骤
2
：软件的卸载
方式
1
：通过控制面板方式
卸载
MySQL8.0
的程序可以和其他桌面应用程序一样直接在
“
控制面板
”
选择
“
卸载程序
”
，并在程序列表中找到MySQL8.0
服务器程序，直接双击卸载即可，如图所示。这种方式删除，数据目录下的数据不会跟着
删除。
方式
2
：通过
360
或电脑管家等软件卸载
略
方式
3
：通过安装包提供的卸载功能卸载
你也可以通过安装向导程序进行
MySQL8.0
服务器程序的卸载。
① 再次双击下载的
mysql-installer-community-8.0.26.0.msi
文件，打开安装向导。安装向导会自动检测
已 安装的
MySQL
服务器程序。
② 选择要卸载的
MySQL
服务器程序，单击
“Remove”
（移除），即可进行卸载。
③ 单击
“Next”
（下一步）按钮，确认卸载。
④ 弹出是否同时移除数据目录选择窗口。如果想要同时删除
MySQL
服务器中的数据，则勾选
“Remove
the data directory”
，如图所示。
⑤ 执行卸载。单击
“Execute”
（执行）按钮进行卸载。
⑥ 完成卸载。单击
“Finish”
（完成）按钮即可。如果想要同时卸载
MySQL8.0
的安装向导程序，勾选
“Yes
，
Uninstall MySQL Installer”
即可，如图所示。
步骤
3
：残余文件的清理
如果再次安装不成功，可以卸载后对残余文件进行清理后再安装。
（
1
）服务目录：
mysql
服务的安装目录
（
2
）数据目录：默认在
C:\ProgramData\MySQL
如果自己单独指定过数据目录，就找到自己的数据目
录进行删除即可。
Caution
请在卸载前做好数据备份
在操作完以后，需要重启计算机，然后进行安装即可。
如果仍然安装失败，需要继续操作如下步骤
4
。
步骤
4
：清理注册表（选做）
如果前几步做了，再次安装还是失败，那么可以清理注册表。
如何打开注册表编辑器：在系统的搜索框中输入
regedit
HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\Eventlog\Application\MySQL
服务 目
录删除
HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\MySQL
服务 目录删除
HKEY_LOCAL_MACHINE\SYSTEM\ControlSet002\Services\Eventlog\Application\MySQL
服务 目
录删除
HKEY_LOCAL_MACHINE\SYSTEM\ControlSet002\Services\MySQL
服务 目录删除
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Application\MySQL
服
务目录 删除
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MySQL
服务删除
注册表中的
ControlSet001,ControlSet002,
不一定是
001
和
002,
可能是
ControlSet005
、
006
之类
!
步骤
5
：删除环境变量配置
找到
path
环境变量，将其中关于
mysql
的环境变量删除，
切记不要全部删除。
例如：删除
D:\develop_tools\mysql\MySQLServer8.0.26\bin;
这个部分
2.MySQL
的下载、安装、配置
2.1 MySQL
的
4
大版本
65/)
Note
MySQL Community Server
社区版本
，开源免费，自由下载，但不提供官方技术支持，适用
于大多数普通用户。
MySQL Enterprise Edition
企业版本
，需付费，不能在线下载，可以试用
30
天。提供了更多的
功能和更完备的技术支持，更适合于对数据库的功能和可靠性要求较高的企业客户。
MySQL Cluster
集群版
，开源免费。用于架设集群服务器，可将几个
MySQL Server
封装成一
个
Server
。需要在社区版或企业版的基础上使用。
MySQL Cluster CGE
*
高级集群版
*
，需付费。
目前最新版本为
8.4.0 .
本课程中使用
8.0.26
版本 。
Oracle MySQL
官方开发团队推出的新版本将过渡到新的
MySQL
版本模型。
MySQL 8.1.0
是第一个创新
版本，
8.0.34+
将只进行错误修复，直到
8.0
生命周期结束（
EOL
，定于
2026
年
4
月）。
MySQL 8.x
版本最终将成为
LTS
，这将为用户从
8.0.x
迁移到
8.x LTS
版本提供充足的时间。
对我们的影响
,
总结起来就是
:
如果用户想要
MySQL
数据库的最新功能、改进和
bug fix
，请使用创新版本（例如
8.1.x
、
8.2.x
、
8.3.x
等
)
。
如果用户
MySQL
只需要错误修复，请使用
8.0.x
版本（例如
8.0.35
、
8.0.36
、
8.0.37
等
)
。
如果用户使用的是国内云厂商的
RDS MySQL
数据库服务，具体版本支持计划请参考各个厂商的相
关公告。
此外，官方还提供了
MySQL Workbench
（
GUITOOL
）一款专为
MySQL
设计的 图形界面管理工具 。
MySQLWorkbench
又分为两个版本，分别是 社区版 （
MySQL Workbench OSS
）、 商用版 （
MySQL
WorkbenchSE
）。
2.2
软件的下载
1.
下载地址
官网：
https://www.mysql.com
2.
打开官网，点击
DOWNLOADS
然后，点击
MySQL Community(GPL) Downloads
3.
点击
MySQL Community Server
4.
在
General Availability(GA) Releases
中选择适合的版本
Windows
平台下提供两种安装文件：
MySQL
二进制分发版（
.msi
安装文件）和免安装版（
.zip
压缩文
件）。一般来讲，应当使用二进制分发版，因为该版本提供了图形化的安装向导过程，比其他的分发版
使用起来要简单，不再需要其他工具启动就可以运行
MySQL
。
这里在
Windows
系统下推荐下载
MSI
安装程序 ；点击
Go to Download Page
进行下载即可
Windows
下的
MySQL8.0
安装有两种安装程序
mysql-installer-web-community-8.0.26.0.msi
下载程序大小：
2.4M
；安装时需要联网安装
组件。
mysql-installer-community-8.0.26.0.msi
下载程序大小：
450.7M
；安装时离线安装即 可。
推荐。
如果安装
MySQL5.7
版本的话，选择
Archives
，接着选择
MySQL5.7
的相应版本即可。这里下载最
近
期的
MySQL5.7.34
版本。
2.3 MySQL8.0
版本的安装
MySQL
下载完成后，找到下载文件，双击进行安装，具体操作步骤如下。
步骤
1
：双击下载的
mysql-installer-community-8.0.26.0.msi
文件，打开安装向导。
步骤
2
：打开
“Choosing a Setup Type”
（选择安装类型）窗口，在其中列出了
5
种安装类型，分别是
Developer Default
（默认安装类型）、
Server only
（仅作为服务器）、
Client only
（仅作为客户端）、
Full
（完全安装）、
Custom
（自定义安装）。这里选择
“Custom
（自定义安装）
”
类型按钮，单击
“Next(
下 一步
)”
按钮。
步骤
3
：打开
“Select Products”
（选择产品）窗口，可以定制需要安装的产品清单。例如，选择
“MySQL
Server 8.0.26-X64”
后，单击
“
→
”
添加按钮，即可选择安装
MySQL
服务器，如图所示。采用通用的方法，
可 以添加其他你需要安装的产品。
此时如果直接
“Next”
（下一步），则产品的安装路径是默认的。如果想要自定义安装目录，则可以选中
对应的产品，然后在下面会出现
“Advanced Options”
（高级选项）的超链接。
单击
“Advanced Options”
（高级选项）则会弹出安装目录的选择窗口，如图所示，此时你可以分别设置
MySQL
的服务程序安装目录和数据存储目录。如果不设置，默认分别在
C
盘的
Program Files
目录和
ProgramData
目录（这是一个隐藏目录）。如果自定义安装目录，请避免
“
中文
”
目录。另外，建议服务
目 录和数据目录分开存放。
步骤
4
：在上一步选择好要安装的产品之后，单击
“Next”
（下一步）进入确认窗口，如图所示。单击
“Execute”
（执行）按钮开始安装。
步骤
5
：安装完成后在
“Status”
（状态）列表下将显示
“Complete”
（安装完成），如图所示。
2.4
配置
MySQL8.0
MySQL
安装之后，需要对服务器进行配置。具体的配置步骤如下。
步骤
1
：在上一个小节的最后一步，单击
“Next”
（下一步）按钮，就可以进入产品配置窗口。
步骤
2
：单击
“Next”
（下一步）按钮，进入
MySQL
服务器类型配置窗口，如图所示。端口号一般选择默
认 端口号
3306
。
其中，
“Config Type”
选项用于设置服务器的类型。单击该选项右侧的下三角按钮，即可查看
3
个选项，如
图所示。
Development Machine
（开发机器
)
：该选项代表典型个人用桌面工作站。此时机器上需要运行多
个应用程序，那么
MySQL
服务器将占用最少的系统资源。
Server Machine
（服务器
)
：该选项代表服务器，
MySQL
服务器可以同其他服务器应用程序一起 运
行，例如
Web
服务器等。
MySQL
服务器配置成适当比例的系统资源。
Dedicated Machine
（专用服务器
)
：该选项代表只运行
MySQL
服务的服务器。
MySQL
服务器配置
成使用所有可用系统资源。
步骤
3
：单击
“Next”
（下一步）按钮，打开设置授权方式窗口。其中，上面的选项是
MySQL8.0
提供的新
的 授权方式，采用
SHA256
基础的密码加密方法；下面的选项是传统授权方法（保留
5.x
版本兼容性）。
步骤
4
：单击
“Next”
（下一步）按钮，打开设置服务器
root
超级管理员的密码窗口，如图所示，需要输入
两次同样的登录密码。也可以通过
“Add User”
添加其他用户，添加其他用户时，需要指定用户名、允许
该用户名在哪台
/
哪些主机上登录，还可以指定用户角色等。此处暂不添加用户，用户管理在
MySQL
高级
特性篇中讲解。
步骤
5
：单击
“Next”
（下一步）按钮，打开设置服务器名称窗口，如图所示。该服务名会出现在
Windows
服务列表中，也可以在命令行窗口中使用该服务名进行启动和停止服务。本书将服务名设置为
“MySQL80”
。如果希望开机自启动服务，也可以勾选
“Start the MySQL Server at System Startup”
选项
（推 荐）。
下面是选择以什么方式运行服务？可以选择
“Standard System Account”(
标准系统用户
)
或者
“Custom
User” (
自定义用户
)
中的一个。这里推荐前者。
步骤
6
：单击
“Next”
（下一步）按钮，打开确认设置服务器窗口，单击
“Execute”
（执行）按钮。
步骤
7
：完成配置，如图所示。单击
“Finish”
（完成）按钮，即可完成服务器的配置。
步骤
8
：如果还有其他产品需要配置，可以选择其他产品，然后继续配置。如果没有，直接选择
“Next”
（下一步），直接完成整个安装和配置过程。
步骤
9
：结束安装和配置。
2.5
配置
MySQL8.0
环境变量
如果不配置
MySQL
环境变量，就不能在命令行直接输入
MySQL
登录命令。下面说如何配置
MySQL
的环境
变量：
步骤
1
：在桌面上右击【此电脑】图标，在弹出的快捷菜单中选择【属性】菜单命令。
步骤
2
：打开【系 统】窗口，单击【高级系统设置】链接。
步骤
3
：打开【系统属性】对话框，选择【高级】选项卡，然 后单击【环境变量】按钮。
步骤
4
：打开【环境变量】对话框，在系统变量列表中选择
path
变量。
步骤
5
：单击【编辑】按钮，在【编辑环境变量】对话框中，将
MySQL
应用程序的
bin
目录
（
C:\Program Files\MySQL\MySQL Server 8.0\bin
）添加到变量值中，用分号将其与其他路径分隔开。
步骤
6
：添加完成 之后，单击【确定】按钮，这样就完成了配置
path
变量的操作，然后就可以直接输入
MySQL
命令来登录 数据库了。
2.6 MySQL5.7
版本的安装、配置
安装
此版本的安装过程与上述过程除了版本号不同之外，其它环节都是相同的。所以这里省略了
MySQL5.7.34
版本的安装截图。
配置
配置环节与
MySQL8.0
版本确有细微不同。大部分情况下直接选择
“Next”
即可，不影响整理使用。
这里配置
MySQL5.7
时，重点强调：
与前面安装好的
MySQL8.0
不能使用相同的端口号。
2.7
安装失败问题
MySQL
的安装和配置是一件非常简单的事，但是在操作过程中也可能出现问题，特别是初学者。
问题
1
：无法打开
MySQL8.0
软件安装包或者安装过程中失败，如何解决？
在运行
MySQL8.0
软件安装包之前，用户需要确保系统中已经安装了
.Net Framework
相关软件，如果缺
少 此软件，将不能正常地安装
MySQL8.0
软件。
解决方案：到这个地址
https://www.microsoft.com/en-us/download/details.aspx?id=42642
下载
Microsoft .NET Framework 4.5
并安装后，再去安装
MySQL
。
另外，还要确保
Windows Installer
正常安装。
windows
上安装
mysql8.0
需要操作系统提前已安装好
Microsoft Visual C++ 2015-2019
。
解决方案同样是，提前到微软官网
(
https://support.microsoft.com/en-us/topic/the-latest-supported
visual-c-downloads-2647da03-1eea-4433-9aff-95f26a218cc0-c-downloads-2647da03-1eea-4433-9a
ff-95f26a218cc0
，下载相应的环境。
问题
2
：卸载重装
MySQL
失败？
该问题通常是因为
MySQL
卸载时，没有完全清除相关信息导致的。 解决办法是，把以前的安装目录删
除。如果之前安装并未单独指定过服务安装目录，则默认安装目录是
“C:\Program Files\MySQL”
，彻底
删除该目录。同时删除
MySQL
的
Data
目录，如果之前安装并未单独指定 过数据目录，则默认安装目录是
“C:\ProgramData\MySQL”
，该目录一般为隐藏目录。删除后，重新安装 即可。
问题
3
：如何在
Windows
系统删除之前的未卸载干净的
MySQL
服务列表？
操作方法如下，在系统
“
搜索框
”
中输入
“cmd”
，按
“Enter”
（回车）键确认，弹出命令提示符界面。然后输
入
“sc delete MySQL
服务名
”,
按
“Enter”
（回车）键，就能彻底删除残余的
MySQL
服务了。
3.MySQL
的登录
3.1
服务的启动和停止
MySQL
安装完毕之后，需要启动服务器进程，不然客户端无法连接数据库。
在前面的配置过程中，已经将
MySQL
安装为
Windows
服务，并且勾选当
Windows
启动、停止时，
MySQL
也 自动启动、停止。
方式
1
：使用图形界面工具
步骤
1
：打开
windows
服务
方式
1
：计算机（点击鼠标右键）
→
管理（点击）
→
服务和应用程序（点击）
→
服务（点
击）
方式
2
：控制面板（点击）
→
系统和安全（点击）
→
管理工具（点击）
→
服务（点击）
方式
3
：任务栏（点击鼠标右键）
→
启动任务管理器（点击）
→
服务（点击）
方式
4
：单击【开始】菜单，在搜索框中输入
“services.msc”
，按
Enter
键确认
步骤
2
：找到
MySQL80
（点击鼠标右键）
→
启动或停止（点击）
方式
2
：使用命令行工具
#
启动
MySQL
服务命令：
net
start
MySQL
服务名
#
停止
MySQL
服务命令：
net stop MySQL
服务名
Caution
说明：
1. start
和
stop
后面的服务名应与之前配置时指定的服务名一致。
2.
如果当你输入命令后，提示
“
拒绝服务
”
，请以 系统管理员身份 打开命令提示符界面重新尝试。
3.2
自带客户端的登录与退出
当
MySQL
服务启动完成后，便可以通过客户端来登录
MySQL
数据库。注意：确认服务是开启的。
登录方式
1
：
MySQL
自带客户端
开始菜单
→
所有程序
→
MySQL
→
MySQL 8.0 Command Line Client
Note
说明：仅限于
root
用户
登录方式
2
：
windows
命令行
格式：
mysql
-
h
主机名
-
P
端口号
-
u
用户名
-
p
密码
举例：
mysql
-
h
localhost
-
P
3306
-
u root
-
pabc123
#
这里我设置的
root
用户的密码是
abc123
Caution
注意：
（
1
）
-p
与密码之间不能有空格，其他参数名与参数值之间可以有空格也可以没有空格。如：
mysql -hlocalhost -P3306 -uroot -pabc123
（
2
）密码建议在下一行输入，保证安全
mysql -h localhost -P 3306 -u root -p
Enter password:
（
3
）客户端和服务器在同一台机器上，所以输入
localhost
或者
IP
地址
127.0.0.1
。同时，因为是连
接本 机：
-hlocalhost
就可以省略，如果端口号没有修改：
-P3306
也可以省略
简写成：
mysql -u root -p
Enter password:
连接成功后，有关于
MySQL Server
服务版本的信息，还有第几次连接的
id
标识。 也可以在命令行通过
以下方式获取
MySQL Server
服务版本的信息：
c:
\
>
mysql
-
V
c:
\
>
mysql
--
version
或登录后，通过以下方式查看当前版本信息
:
mysql
>
select
version();
退出登录
功能
SQL
查看所有的数据库
show databases
；
创建数据库
create database [if not exists] mydb1 [charset=utf8]
切换
(
选择要操作的
)
数据库
use mydb1
；
删除数据库
drop database [if exists] mydb1
；
修改数据库编码
alter database mydb1 character set utf8;
第
3
章
MySQL
数据库基本操作
-DDL
和
DML
1. DDL
解释
DDL(Data Definition Language)
，数据定义语言，该语言部分包括以下内容：
对数据库的常用操作
对表结构的常用操作
修改表结构
2.
对数据库的常用操作
exit
或
quit
2.1
对表结构的常用操作
-
创建表
数据类型
数据类型是指在创建表的时候为表中字段指定数据类型，只有数据符合类型要求才能存储起来，使用数
据类型的原则是
:
够用就行，尽量使用取值范围小的，而不用大的，这样可以更多的节省存储空间。
类型
大小
范围（有符号）
范围（无符
号）
用
途
TINYINT
1
byte
(-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)
极
大
整
数
值
数值类型
#
创建表
create table
[
if not exists
]
表名
(
字段名
1
类型
[(
宽度
)] [
约束条件
] [
comment
'
字段说明
'
]
,
字段名
2
类型
[(
宽度
)] [
约束条件
] [
comment
'
字段说明
'
]
,
字段名
3
类型
[(
宽度
)] [
约束条件
] [
comment
'
字段说明
'
]
)[
表的一些设置
]
;
创建表是构建一张空表，指定这个表的名字，这个表有几列，每一列叫什么名字，以及每一列存储的数据类型。
类型
大小
范围（有符号）
范围（无符
号）
用
途
FLOAT
4
bytes
(-3.402 823 466 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
，
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
依赖于
M
和
D
的值
依赖于
M
和
D
的值
小
数
值
类型
大小
用途
CHAR
0-255 bytes
定长字符串
VARCHAR
0-65535 bytes
变长字符串
TINYBLOB
0-255 bytes
不超过
255
个字符的二进制字符串
TINYTEXT
0-255 bytes
短文本字符串
BLOB
0-65 535 bytes
二进制形式的长文本数据
TEXT
0-65 535 bytes
长文本数据
MEDIUMBLOB
0-16 777 215 bytes
二进制形式的中等长度文本数据
MEDIUMTEXT
0-16 777 215 bytes
中等长度文本数据
LONGBLOB
0-4 294 967 295 bytes
二进制形式的极大文本数据
LONGTEXT
0-4 294 967 295 bytes
极大文本数据
日期和时间类型
类型
大小
(
bytes)
范围
格式
用途
DATE
3
1000-01-01/9999-12-31
YYYY-MM
DD
日期
值
字符串类型
功能
SQL
查看当前数据库的所有表名称
show tables;
查看指定某个表的创建语句
show create table
表名；
查看表结构
desc
表名
删除表
drop table
表名
类型
大小
(
bytes)
范围
格式
用途
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:00/2038
结束
时间是第
2147483647
秒，北京
时间
2038-1-19 11:14:07
，格林
尼治时间
2038
年
1
月
19
日 凌晨
03:14:07
YYYYMMDD
HHMMSS
混合
日期
和时
间
值，
时间
戳
2.2
对表结构的常用操作
—
其他操作
2.3
对表结构的常用操作
-
修改表结构格式
修改表添加列
alter table
表名
add
列名 类型
(
长度
) [
约束
]
;
修改列名和类型
alter table
表名
change
旧列名 新列名 类型
(
长度
)
约束
;
修改表删除列
.
alter table
表名
drop
列名
;
修改表名
rename table
表名
to
新表名
;
3. DML
基本介绍
DML
是指数据操作语言，英文全称是
Data Manipulation Language
，用来对数据库中表的数据记录进行
更新。关键字：
插入
insert
删除
delete
更新
update
3.1
数据插入
3.2
数据修改
3.3
数据删除
Caution
注意：
delete
和
truncate
原理不同，
delete
只删除内容，而
truncate
类似于
drop table
，可以理解
为是将整个表删除，然后再创建该表；
insert into
表
(
列名
1,
列名
2,
列名
3...
)
values
(
值
1,
值
2,
值
3...
)
；
//
向表中插入某些
insert into
表
values
(
值
1,
值
2,
值
3...
)
; //
向表中插入所有列
update
表名
set
字段名
=
值
,
字段名
=
值
...;
update
表名
set
字段名
=
值
,
字段名
=
值
...
where
条件
;
delete from
表名
[
where
条件
]
;
truncate table
表名 或者
truncate
表名
4.
使用演示
4.1 MySQL
的使用演示
1
、查看所有的数据库
“information_schema”
是
MySQL
系统自带的数据库，主要保存
MySQL
数据库服务器的系统信
息，比如数据库的名称、数据表的名称、字段名称、存取权限、数据文件 所在的文件夹和系统使
用的 文件夹，等等
“performance_schema”
是
MySQL
系统自带的数据库，可以用来监控
MySQL
的各类性能指标。
“sys”
数据库是
MySQL
系统自带的数据库，主要作用是以一种更容易被理解的方式展示
MySQL
数
据 库服务器的各类性能指标，帮助系统管理员和开发人员监控
MySQL
的技术性能。
“mysql”
数据库保存了
MySQL
数据库服务器运行时需要的系统信息，比如数据文件夹、当前使用
的 字符集、约束检查信息，等等
为什么
Workbench
里面我们只能看到
“demo”
和
“sys”
这
2
个数据库呢？
这是因为，
Workbench
是图形化的管理工具，主要面向开发人 员，
“demo”
和
“sys”
这
2
个数据库已经够
用 了。如果有特殊需求，比如，需要监控
MySQL
数据库各项性能指标、直接操作
MySQL
数据库系统文
件 等，可以由
DBA
通过
SQL
语句，查看其它的系统数据库。
2
、创建自己的数据库
3
、使用自己的数据库
说明：如果没有使用
use
语句，后面针对数据库的操作也没有加
“
数据名
”
的限定，那么会报
“ERROR 1046
(3D000): No database selected”
（没有选择数据库）
使用完
use
语句之后，如果接下来的
SQL
都是针对一个数据库操作的，那就不用重复
use
了，如果要针对
另 一个数据库操作，那么要重新
use
。
4
、查看某个库的所有表格
5
、创建新的表格
show databases
;
create database
数据库名
;
#
创建
atguigudb
数据库，该名称不能与已经存在的数据库重名。
create database
atguigudb;
use
数据库名
;
#
使用
atguigudb
数据库
use
atguigudb;
show tables
;
#
要求前面有
use
语句
show tables from
数据库名
;
create table
表名称
(
字段名 数据类型
,
字段名 数据类型
)
;
说明：如果是最后一个字段，后面就用加逗号，因为逗号的作用是分割每个字段。
create table
student
(
id int,
name varchar
(
20
)
#
说名字最长不超过
20
个字符
)
;
6
、查看一个表的数据
select
*
from
数据库表名称
;
#
查看学生表的数据
select
*
from
student;
7
、添加一条记录
insert into
表名称
values
(
值列表
)
;
#
添加两条记录到
student
表中
insert into
student
values
(
1
,
'
张三
'
)
;
insert into
student
values
(
2
,
'
李四
'
)
;
报错：
mysql
>
insert into
student
values
(
1
,
'
张三
'
)
;
ERROR
1366
(
HY000
)
: Incorrect string
value
:
'\xD5\xC5\xC8\xFD'
for column
'name'
at row
1
mysql
>
insert into
student
values
(
2
,
'
李四
'
)
;
ERROR
1366
(
HY000
)
: Incorrect string
value
:
'\xC0\xEE\xCB\xC4'
for column
'name'
at row
1
mysql
>
show create table
student;
字符集的问题。
8
、查看表的创建信息
show create table
表名称
\G
#
查看
student
表的详细创建信息
show create table
student
\G
#
结果如下
***************************
1.
row
***************************
Table
: student
Create Table
:
CREATE TABLE
`student`
(
上面的结果显示
student
的表格的默认字符集是
“latin1”
不支持中文。
9
、查看数据库的创建信息
上面的结果显示
atguigudb
数据库也不支持中文，字符集默认是
latin1
。
10
、删除表格
11
、删除数据库
4.2 MySQL
的编码设置
MySQL5.7
中
问题再现：命令行操作
sql
乱码问题
问题解决
步骤
1
：查看编码命令
`id`
int
(
11
)
DEFAULT
NULL
,
`name`
varchar
(
20
)
DEFAULT
NULL
)
ENGINE
=
InnoDB DEFAULT
CHARSET
=
latin1
1
row in set
(
0.00
sec
)
show create database
数据库名
\G
#
查看
atguigudb
数据库的详细创建信息
show create database
atguigudb
\G
***************************
1.
row
***************************
Database
: atguigudb
Create Database
:
CREATE DATABASE
`atguigudb`
/*!40100 DEFAULT CHARACTER SET
latin1 */
1
row in set
(
0.00
sec
)
drop table
表名称
;
#
删除学生表
drop table
student;
drop database
数据库名
;
#
删除
atguigudb
数据库
drop database
atguigudb;
mysql
>
INSERT INTO
t_stu
VALUES
(
1
,
'
张三
'
,
'
男
'
)
;
ERROR
1366
(
HY000
)
: Incorrect string
value
:
'\xD5\xC5\xC8\xFD'
for column
'sname'
at row
1
show variables like
'character_%'
;
show variables like
'collation_%'
;
步骤
2
：修改
mysql
的数据目录下的
my.ini
配置文件
[mysql] #
大概在
63
行左右，在其下添加
default-character-set=utf8 #
默认字符集
[mysqld] #
大概在
76
行左右，在其下添加
...
character-set-server=utf8 collation-server=utf8_general_ci
Caution
注意：建议修改配置文件使用
notepad++
等高级文本编辑器，使用记事本等软件打开修改后可能会
导致文件编码修改为
“
含
BOM
头
”
的编码，从而服务重启失败。
步骤
3
：重启服务
步骤
4
：查看编码命令
show variables like
'character_%'
;
show variables like
'collation_%'
;
如果是以上配置就说明对了。接着我们就可以新创建数据库、新创建数据表，接着添加包含中文的 数据
了。
MySQL8.0
中
在
MySQL 8.0
版本之前，默认字符集为
latin1
，
utf8
字符集指向的是
utf8mb3
。网站开发人员在数据库设
计 的时候往往会将编码修改为
utf8
字符集。如果遗忘修改默认的编码，就会出现乱码的问题。从
MySQL
8.0
开始，数据库的默认编码改为
utf8mb4
，从而避免了上述的乱码问题。
5. MySQL
图形化管理工具
MySQL
图形化管理工具极大地方便了数据库的操作与管理，常用的图形化管理工具有：
MySQL
Workbench
、
phpMyAdmin
、
Navicat Preminum
、
MySQLDumper
、
SQLyog
、
dbeaver
、
MySQL
ODBC Connector
。
工具
1. MySQL Workbench
MySQL
官方提供的图形化管理工具
MySQL Workbench
完全支持
MySQL 5.0
以上的版本。
MySQL
Workbench
分为社区版和商业版，社区版完全免费，而商业版则是按年收费。
MySQL Workbench
为数据库管理员、程序开发者和系统规划师提供可视化设计、模型建立、以及数据
库 管理功能。它包含了用于创建复杂的数据建模
ER
模型，正向和逆向数据库工程，也可以用于执行通常
需 要花费大量时间的、难以变更和管理的文档任务。
下载地址：
http://dev.mysql.com/downloads/workbench/
。
使用：
首先，我们点击
Windows
左下角的
“
开始
”
按钮，如果你是
Win10
系统，可以直接看到所有程序。接
着， 找到
“MySQL”
，点开，找到
“MySQL Workbench 8.0 CE”
。点击打开
Workbench
，如下图所示：
左下角有个本地连接，点击，录入
Root
的密码，登录本地
MySQL
数据库服务器，如下图所示：
这是一个图形化的界面，我来给你介绍下这个界面。
上方是菜单。左上方是导航栏，这里我们可以看到
MySQL
数据库服务器里面的数据 库，包括数据
表、视图、存储过程和函数；左下方是信息栏，可以显示上方选中的数据 库、数据表等对象的信
息。
中间上方是工作区，你可以在这里写
SQL
语句，点击上方菜单栏左边的第三个运行按 钮，就可以
执 行工作区的
SQL
语句了。
中间下方是输出区，用来显示
SQL
语句的运行情况，包括什么时间开始运行的、运行的 内容、运
行 的输出，以及所花费的时长等信息。
工具
2. Navicat
Navicat MySQL
是一个强大的
MySQL
数据库服务器管理和开发工具。它可以与任何
3.21
或以上版本的
MySQL
一起工作，支持触发器、存储过程、函数、事件、视图、管理用户等，对于新手来说易学易用。
其精心设计的图形用户界面（
GUI
）可以让用户用一种安全简便的方式来快速方便地创建、组织、访问和
共享信息。
Navicat
支持中文，有免费版本提供。 下载地址：
http://www.navicat.com/
。
工具
3. SQLyog
SQLyog
是业界著名的
Webyog
公司出品的一款简洁高效、功能强大的图形化
MySQL
数据库管理工具。
这款工具是使用
C++
语言开发的。该工具可以方便地创建数据库、表、视图和索引等，还可以方便地进行
插入、更新和删除等操作，同时可以方便地进行数据库、数据表的备份和还原。该工具不仅可以通过
SQL
文件进行大量文件的导入和导出，还可以导入和导出
XML
、
HTML
和
CSV
等多种格式的数据。 下载地
址：
http://www.webyog.com/
，读者也可以搜索中文版的下载地址。
工具
4
：
dbeaver
DBeaver
是一个通用的数据库管理工具和
SQL
客户端，支持所有流行的数据库：
MySQL
、
PostgreSQL
、
SQLite
、
Oracle
、
DB2
、
SQL Server
、
Sybase
、
MS Access
、
Teradata
、
Firebird
、
Apache Hive
、
Phoenix
、
Presto
等。
DBeaver
比大多数的
SQL
管理工具要轻量，而且支持中文界面。
DBeaver
社区版作为一个免费开 源的产品，和其他类似的软件相比，在功能和易用性上都毫不逊色。
唯一需要注意是
DBeaver
是用
Java
编程语言开发的，所以需要拥有
JDK
（
Java Development ToolKit
）
环 境。如果电脑上没有
JDK
，在选择安装
DBeaver
组件时，勾选
“Include Java”
即可。
下载地址：
https://dbeaver.io/download/
可能出现连接问题：
有些图形界面工具，特别是旧版本的图形界面工具，在连接
MySQL8
时出现
“Authentication plugin
'caching_sha2_password' cannot be loaded”
错误。
出现这个原因是
MySQL8
之前的版本中加密规则是
mysql_native_password
，而在
MySQL8
之后，加密规
则
是
caching_sha2_password
。解决问题方法有两种，第一种是升级图形界面工具版本，第二种是把
MySQL8
用户登录密码加密规则还原成
mysql_native_password
。
第二种解决方案如下，用命令行登录
MySQL
数据库之后，执行如下命令修改用户密码加密规则并更新用
户密码，这里修改用户名为
“root@localhost”
的用户密码规则为
“mysql_native_password”
，密码值为
“123456”
，如图所示。
#
使用
mysql
数据库
USE
mysql;
#
修改
'root'@'localhost'
用户的密码规则和密码
ALTER USER
'root'
@'localhost' IDENTIFIED WITH mysql_native_password BY 'abc123'
;
#
刷新权限
FLUSH PRIVILEGES
;
MySQL
的目录结构
说明
bin
目录
所有
MySQL
的可执行文件。如：
mysql.exe
MySQLInstanceConfig.exe
数据库的配置向导，在安装时出现的内容
data
目录
系统数据库所在的目录
my.ini
文件
MySQL
的主要配置文件
c:\ProgramData\MySQL\MySQL Server 8.0\data\
用户创建的数据库所在的目录
6. MySQL
目录结构与源码
6.1
主要目录结构
6.2 MySQL
源代码获取
首先，你要进入
MySQL
下载界面。 这里你不要选择用默认的
“Microsoft Windows”
，而是要通过下拉
栏， 找到
“Source Code”
，在下面的操作系统版本里面， 选择
Windows
（
Architecture
Independent
），然后点 击下载。
接下来，把下载下来的压缩文件解压，我们就得到了
MySQL
的源代码。
MySQL
是用
C++
开发而成的，我简单介绍一下源代码的组成。
mysql-8.0.22
目录下的各个子目录，包含了
MySQL
各部分组件的源代码：
sql
子目录是
MySQL
核心代码；
libmysql
子目录是客户端程序
API
；
mysql-test
子目录是测试工具；
mysys
子目录是操作系统相关函数和辅助函数；
源代码可以用记事本打开查看，如果你有
C++
的开发环境，也可以在开发环境中打开查看。
如上图所示，源代码并不神秘，就是普通的
C++
代码，跟你熟悉的一样，而且有很多注释，可以帮助你
理解。阅读源代码就像在跟
MySQL
的开发人员对话一样，十分有趣。
7.
常见问题的解决
问题
1
：
root
用户密码忘记，重置的操作
1:
通过任务管理器或者服务管理，关掉
mysqld(
服务进程
)
2:
通过命令行
+
特殊参数开启
mysqld mysqld --defaults
file="D:\ProgramFiles\mysql\MySQLServer5.7Data\my.ini" --skip-grant-tables
3:
此时，
mysqld
服务进程已经打开。并且不需要权限检查
4: mysql -uroot
无密码登陆服务器。另启动一 个客户端进行
5:
修改权限表
（
1
）
use mysql;
（
2
）
update user set authentication_string=password('
新密 码
') where user='root' and
Host='localhost';
（
3
）
flush privileges;
6:
通过任务管理器，关掉
mysqld
服务进程。
7:
再次通过服务管理，打开
mysql
服务。
8:
即可用修改后的新密码登陆。
方法
1
：通用方法
--
启动时跳过权限表
1>
停止数据库
C:\Users\Administrator>net stop mysql
MySQL
服务正在停止
..
MySQL
服务已成功停止。
2>
启动时跳过权限表
mysqld --console --skip-grant-tables --shared-memory
C:\Users\Administrator>mysqld --console --skip-grant-tables --shared
memory
2024-05-09T02:10:48.290981Z 0 [System] [MY-010116] [Server]
F:\mysql\mysql-8.0.36-winx64\bin\mysqld.exe (mysqld 8.0.36) starting as process
5344
2024-05-09T02:10:48.318791Z 1 [System] [MY-013576] [InnoDB] InnoDB
initialization has started.
2024-05-09T02:10:49.700249Z 1 [System] [MY-013577] [InnoDB] InnoDB
initialization has ended.
2024-05-09T02:10:51.926542Z 0 [Warning] [MY-011311] [Server] Plugin
mysqlx reported: 'All I/O interfaces are disabled, X Protocol won't be
accessible'
2024-05-09T02:10:52.119615Z 0 [Warning] [MY-010068] [Server] CA
certificate ca.pem is self signed.
2024-05-09T02:10:52.119846Z 0 [System] [MY-013602] [Server] Channel
mysql_main configured to support TLS. Encrypted connections are now supported for
this channel.
2024-05-09T02:10:52.215835Z 0 [System] [MY-010931] [Server]
F:\mysql\mysql-8.0.36-winx64\bin\mysqld.exe: ready for connections. Version:
'8.0.36' socket: '' port: 0 MySQL Community Server - GPL.
注意：不要关闭中断
3>
重新开启新的终端，登录并设置新密码
PS C:\Users\Administrator> mysql -uroot -p
Enter password:
直接敲回车键
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.36 MySQL Community Server - GPL
Copyright (c) 2000, 2024, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input
statement.
mysql> flush privileges;
Query OK, 0 rows affected (0.05 sec)
mysql> alter user root@localhost identified by '123456';
Query OK, 0 rows affected (0.02 sec)
4>
退出
MySQL
，关闭第一个终端，正常启动服务。
mysql> \q
Bye
PS C:\Users\Administrator> net start mysql
MySQL
服务正在启动
..
MySQL
服务已经启动成功。
PS C:\Users\Administrator> mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be
insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.36 MySQL Community Server - GPL
Copyright (c) 2000, 2024, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input
statement.
mysql> \q
Bye
方法
2
：参考官方文档
1>
停止服务
PS C:\Users\Administrator> net stop mysql
MySQL
服务正在停止
.
MySQL
服务已成功停止。
2>
创建一个文本文件，内如如下，保存为
mysql_init.txt
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
3>
启动同时使用上一步文件修改密码
要求管理员身份打开
cmd
来运行命令，暂时不要关闭终端：
C:\Users\Administrator>mysqld --init-file=F:\mysql\mysql-8.0.36-
winx64\mysql_init.txt
4>
打开另一个终端，登录测试
PS C:\Users\Administrator> mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can
be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.36 MySQL Community Server - GPL
Copyright (c) 2000, 2024, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input
statement.
mysql> \q
Bye
5>
关闭第一个终端，正常启动服务。
C:\Users\Administrator>mysqld --init-file=F:\mysql\mysql-8.0.36-
winx64\mysql_init.txt
^C
C:\Users\Administrator>net start mysql
MySQL
服务正在启动
..
MySQL
服务已经启动成功。
C:\Users\Administrator>mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can
be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.36 MySQL Community Server - GPL
Copyright (c) 2000, 2024, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input
statement.
mysql> \q
Bye
6>
删除文件
mysql_init.tx
问题
2
：
mysql
命令报不是内部或外部命令
如果输入
mysql
命令报
“
不是内部或外部命令
”
，把
mysql
安装目录的
bin
目录配置到环境变量
path
中。如
下：
问题
3
：错误
ERROR
：没有选择数据库就操作表格和数据
ERROR 1046 (3D000): No database selected
解决方案一：就是使用
“USE
数据库名
;”
语句，这样接下来的语句就默认针对这个数据库进行操作
解决方案二：就是所有的表对象前面都加上
“
数据库
.”
问题
4
：命令行客户端的字符集问题
mysql
>
INSERT INTO
t_stu
VALUES
(
1
,
'
张三
'
,
'
男
'
)
;
ERROR
1366
(
HY000
)
: Incorrect string
value
:
'\xD5\xC5\xC8\xFD'
for column
'sname'
at row
1
原因：服务器端认为你的客户端的字符集是
utf-8
，而实际上你的客户端的字符集是
GBK
。
查看所有字符集：
SHOW VARIABLES LIKE 'character_set_%';
解决方案，设置当前连接的客户端字符集
“SET NAMES GBK;”
问题
5
：修改数据库和表的字符编码
修改编码：
（
1)
先停止服务，（
2
）修改
my.ini
文件（
3
）重新启动服务
说明：
如果是在修改
my.ini
之前建的库和表，那么库和表的编码还是原来的
Latin1
，要么删了重建，要么使用
alter
语句修改编码。
mysql
>
create database
0728
db
charset
Latin1;
Query
OK,
1
row
affected
(
0.00
sec
)
mysql
>
use
0728
db;
Database changed
mysql
>
create table
student
(
id int , name varchar
(
20
))
charset
Latin1;
Query
OK,
0
rows affected
(
0.02
sec
)
mysql
>
show create table
student
\G
***************************
1.
row
***************************
Table
: student
Create Table
:
CREATE TABLE
`student`
(
`id`
int
(
11
)
NOT
NULL
,
`name`
varchar
(
20
)
DEFAULT
NULL
,
PRIMARY KEY
(
`id`
)
)
ENGINE
=
InnoDB DEFAULT
CHARSET
=
latin1
1
row in set
(
0.00
sec
)
mysql
>
alter table
student
charset
utf8;
#
修改表字符编码为
UTF8 Query OK, 0 rows
affected (0.01 sec)
Records:
0
Duplicates:
0
Warnings
:
0
***************************
1.
row
***************************
Table
: student
Create Table
:
CREATE TABLE
`student`
(
`id`
int
(
11
)
NOT
NULL
,
`name`
varchar
(
20
)
CHARACTER SET
latin1
DEFAULT
NULL
,
#
字段仍然是
latin1
编码
PRIMARY KEY (`id`)
)
ENGINE
=
InnoDB DEFAULT
CHARSET
=
utf8
1
row in set
(
0.00
sec
)
mysql
>
alter table
student
modify
name varchar
(
20
)
charset
utf8;
#
修改字段字符编码为
UTF8 Query OK, 0 rows affected (0.05 sec)
Records:
0
Duplicates:
0
Warnings
:
0
mysql
>
show create table
student
\G
***************************
1.
row
***************************
Table
: student
Create Table
:
CREATE TABLE
`student`
(
`id`
int
(
11
)
NOT
NULL
,
`name`
varchar
(
20
)
DEFAULT
NULL
,
PRIMARY KEY
(
`id`
)
)
ENGINE
=
InnoDB DEFAULT
CHARSET
=
utf8
1
row in set
(
0.00
sec
)
mysql
>
show create database
0728
db;;
+--------+-----------------------------------------------------------------+
|
Database
|
Create Database
|
+------+-------------------------------------------------------------------+
|
0728
db
|
CREATE DATABASE
`0728db`
/*!40100 DEFAULT CHARACTER SET latin1 */
|
+------+-------------------------------------------------------------------+
1
row in set
(
0.00
sec
)
mysql
>
alter database
0728
db
charset
utf8;
#
修改数据库的字符编码为
utf8 Query OK, 1
row affected (0.00 sec)
mysql
>
show create database
0728
db;
+--------+-----------------------------------------------------------------+
第
4
章 用户与权限管理
1.
用户管理
1.1
登录
MySQL
服务器
启动
MySQL
服务后，可以通过
mysql
命令来登录
MySQL
服务器，命令如下：
下面详细介绍命令中的参数：
-h
参数 后面接主机名或者主机
IP
，
hostname
为主机，
hostIP
为主机
IP
。
-P
参数 后面接
MySQL
服务的端口，通过该参数连接到指定的端口。
MySQL
服务的默认端口是
3306
， 不使用该参数时自动连接到
3306
端口，
port
为连接的端口号。
-u
参数 后面接用户名，
username
为用户名。
-p
参数 会提示输入密码。
DatabaseName
参数 指明登录到哪一个数据库中。如果没有该参数，就会直接登录到
MySQL
数据
库中，然后可以使用
USE
命令来选择数据库。
-e
参数 后面可以直接加
SQL
语句。登录
MySQL
服务器以后即可执行这个
SQL
语句，然后退出
MySQL
服务器。
举例：
1.2
创建用户
CREATE USER
语句的基本语法形式如下：
用户名参数表示新建用户的账户，由 用户（
User
） 和 主机名（
Host)
构成；
“[ ]”
表示可选，也就是说，可以指定用户登录时需要密码验证，也可以不指定密码验证，这样用户
可以直接登录。不过，不指定密码的方式不安全，不推荐使用。如果指定密码值，这里需要使用
IDENTIFIED BY
指定明文密码值。
CREATE USER
语句可以同时创建多个用户。
举例：
|
Database
|
Create Database
|
+--------+-----------------------------------------------------------------+
|
0728
db
|
CREATE DATABASE
`0728db`
/*!40100 DEFAULT CHARACTER SET utf8 */
|
+--------+-----------------------------------------------------------------+
1
row in set
(
0.00
sec
)
mysql –h hostname
|
hostIP –P port –u username –p DatabaseName –e
"SQL
语句
"
mysql
-
uroot
-
p
-
hlocalhost
-
P3306 mysql
-
e
"select host,user from user"
CREATE USER
用户名
[
IDENTIFIED
BY
'
密码
'
][
,
用户名
[
IDENTIFIED
BY
'
密码
'
]]
;
1.3
修改用户
修改用户名：
1.4
删除用户
方式
1
：使用
DROP
方式删除（推荐）
使用
DROP USER
语句来删除用户时，必须用于
DROP USER
权限。
DROP USER
语句的基本语法形式如
下：
举例：
方式
2
：使用
DELETE
方式删除
执行完
DELETE
命令后要使用
FLUSH
命令来使用户生效，命令如下：
举例
[!NOTE]
注意：不推荐通过
DELETE FROM USER u WHERE USER='li4'
进行删除，系统会有残留信息保
留。而
drop user
命令会删除用户以及对应的权限，执行命令后你会发现
mysql.user
表和
mysql.db
表 的相应记录都消失了。
1.5
设置当前用户
1
、使用
ALTER USER
命令来修改当前用户密码
用户可以使用
ALTER
命令来修改自身密码，如下语句代表
修 改当前登录用户的密码。基本语法如下：
CREATE USER
zhang3 IDENTIFIED
BY
'123123'
;
#
默认
host
是
%
CREATE USER
'kangshifu'
@'localhost' IDENTIFIED BY '123456'
;
UPDATE
mysql
.user
SET USER
=
'li4'
WHERE USER
=
'wang5'
;
FLUSH PRIVILEGES
;
DROP USER user
[
,
user
]
…;
DROP USER
li4 ;
#
默认删除
host
为
%
的用户
DROP USER
'kangshifu'
@'localhost'
;
DELETE FROM
mysql
.user
WHERE
Host
=
’hostname’
AND User
=
’username’;
FLUSH PRIVILEGES
;
DELETE FROM
mysql
.user
WHERE
Host
=
'localhost'
AND User
=
'Emily'
;
FLUSH PRIVILEGES
;
ALTER USER USER
()
IDENTIFIED
BY
'new_password'
;
2
、
使用
SET
语句来修改当前用户密码
使用
root
用户登录
MySQL
后，可以使用
SET
语句来修改密码，具体
SQL
语句如下：
该语句会自动将密码加密后再赋给当前用户
.
1.6
修改其他用户密码
1
、
使用
ALTER
语句来修改普通用户的密码
可以使用
ALTER USER
语句来修改普通用户的密码。基本语法
形 式如下：
2
、
使用
SET
命令来修改普通用户的密码
使用
root
用户登录到
MySQL
服务器后，可以使用
SET
语句来修改
普 通用户的密码。
SET
语句的代码如下
3
、
使用
UPDATE
语句修改普通用户的密码（不推荐）
1.7 MySQL8
密码管理
(
了解
)
密码过期策略
在
MySQL
中，数据库管理员可以 手动设置 账号密码过期，也可以建立一个 自动 密码过期策略。
过期策略可以是 全局的 ，也可以为 每个账号 设置单独的过期策略。
练习：
方式①：使用
SQL
语句更改该变量的值并持久化
方式②：配置文件
my.cnf
中进行维护
手动设置指定时间过期方式
2
：单独设置
每个账号既可延用全局密码过期策略，也可单独设置策略。在
CREATE USER
和
ALTER USER
语句上加
入
PASSWORD EXPIRE
选项可实现单独设置策略。下面是一些语句示例。
SET PASSWORD
=
'new_password'
;
ALTER USER user
[
IDENTIFIED
BY
'
新密码
'
] [
,
user
[
IDENTIFIED
BY
'
新密码
'
]]
…;
SET PASSWORD FOR
'username'
@'hostname'='new_password'
;
UPDATE
MySQL
.user
SET
authentication_string
=
PASSWORD
(
"123456"
)
WHERE User
=
"username"
AND
Host
=
"hostname"
;
ALTER USER user PASSWORD
EXPIRE;
ALTER USER
'kangshifu'
@'localhost'
PASSWORD
EXPIRE;
SET
PERSIST default_password_lifetime
=
180
;
#
建立全局策略，设置密码每隔
180
天过期
[
mysqld
]
default_password_lifetime
=
180
#
建立全局策略，设置密码每隔
180
天过期
#
设置
kangshifu
账号密码每
90
天过期：
CREATE USER
'kangshifu'
@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY; ALTER USER
'kangshifu'@'localhost'
PASSWORD
EXPIRE
INTERVAL
90
DAY;
#
设置密码永不过期：
CREATE USER
'kangshifu'
@'localhost' PASSWORD EXPIRE NEVER; ALTER USER
'kangshifu'@'localhost'
PASSWORD
EXPIRE NEVER;
#
延用全局密码过期策略：
CREATE USER
'kangshifu'
@'localhost' PASSWORD EXPIRE DEFAULT; ALTER USER
'kangshifu'@'localhost'
PASSWORD
EXPIRE
DEFAULT
;
密码重用策略
手动设置密码重用方式
1
：全局
方式①：使用
SQL
SET
PERSIST password_history
=
6
;
#
设置不能选择最近使用过的
6
个密码
SET
PERSIST password_reuse_interval
=
365
;
#
设置不能选择最近一年内的密码
方式②：
my.cnf
配置文件
[
mysqld
]
password_history
=
6
password_reuse_interval
=
365
手动设置密码重用方式
2
：单独设置
#
不能使用最近
5
个密码：
CREATE USER
'kangshifu'
@'localhost'
PASSWORD
HISTORY
5
;
ALTER USER
'kangshifu'
@'localhost'
PASSWORD
HISTORY
5
;
#
不能使用最近
365
天内的密码：
CREATE USER
'kangshifu'
@'localhost'
PASSWORD
REUSE
INTERVAL
365
DAY;
ALTER USER
'kangshifu'
@'localhost'
PASSWORD
REUSE
INTERVAL
365
DAY;
#
既不能使用最近
5
个密码，也不能使用
365
天内的密码
CREATE USER
'kangshifu'
@'localhost'
PASSWORD
HISTORY
5
PASSWORD
REUSE
INTERVAL
365
DAY;
ALTER USER
'kangshifu'
@'localhost'
PASSWORD
HISTORY
5
PASSWORD
REUSE
INTERVAL
365
DAY;
2.
权限管理
2.1
权限列表
MySQL
到底都有哪些权限呢？
（
1
）
CREATE
和
DROP
权限 ，可以创建新的数据库和表，或删除（移掉）已有的数据库和表。如果将
MySQL
数据库中的
DROP
权限授予某用户，用户就可以删除
MySQL
访问权限保存的数据库。
（
2) SELECT
、
INSERT
、
UPDATE
和
DELETE
权限 允许在一个数据库现有的表上实施操作。
（
3
）
SELECT
权限只有在它们真正从一个表中检索行时才被用到。
（
4) INDEX
权限 允许创建或删除索引，
INDEX
适用于已有的表。如果具有某个表的
CREATE
权限，就可以
在
CREATE TABLE
语句中包括索引定义。
（
5
）
ALTER
权限 可以使用
ALTER TABLE
来更改表的结构和重新命名表。
（
6
）
CREATE ROUTINE
权限 用来创建保存的程序（函数和程序），
ALTER ROUTINE
权限用来更改和删
除保存的程序，
EXECUTE
权限用来执行保存的程序。
（
7
）
GRANT
权限 允许授权给其他用户，可用于数据库、表和保存的程序。
（
8) FILE
权限 使用户可以使用
LOAD DATA INFILE
和
SELECT ... INTO OUTFILE
语句读或写服务器上的文
件，任何被授予
FILE
权 限的用户都能读或写
MySQL
服务器上的任何文件（说明用户可以读任何数据库目
录下的文件，因为服务 器可以访问这些文件）。
2.2
授予权限的原则
权限控制主要是出于安全因素，因此需要遵循以下几个 经验原则 ：
1
、只授予能 满足需要的最小权限 ，防止用户干坏事。比如用户只是需要查询，那就只给
select
权限就可
以了，不要给用户赋予
update
、
insert
或者
delete
权限。
2
、创建用户的时候 限制用户的登录主机 ，一般是限制成指定
IP
或者内网
IP
段。
3
、为每个用户 设置满足密码复杂度的密码 。
4
、 定期清理不需要的用户 ，回收权限或者删除用户。
2.3
授予权限
给用户授权的方式有
2
种，分别是通过把 角色赋予用户给用户授权 和 直接给用户授权 。用户是数据库
的 使用者，我们可以通过给用户授予访问数据库中资源的权限，来控制使用者对数据库的访问，消除安
全 隐患。
授权命令：
5.7
及以前版本该权限如果发现没有该用户，则会直接新建一个用户。
比如：
li4
用户用本地命令行方式，授予
atguigudb
这个库下的所有表的插删改查的权限。
mysql
>
show privileges
;
GRANT
权限
1,
权限
2,…
权限
n ON
数据库名称
.
表名称
TO
用户名
@
用户地址
[IDENTIFIED BY ‘
密码口
令
’];
授予通过网络方式登录的
joe
用户 ，对所有库所有表的全部权限，密码设为
123
。注意这里唯独不包括
grant
的权限
[!TIP]
我们在开发应用的时候，经常会遇到一种需求，就是要根据用户的不同，对数据进行横向和纵向的
分组。
所谓横向的分组，就是指用户可以接触到的数据的范围，比如可以看到哪些表的数据；
所谓纵向的分组，就是指用户对接触到的数据能访问到什么程度，比如能看、能改，甚至是 删
除。
2.4
查看权限
查看当前用户权限
查看某用户的全局权限
2.5
收回权限
收回权限就是取消已经赋予用户的某些权限。
收回用户不必要的权限可以在一定程度上保证系统的安全
性。
MySQL
中使用
REVOKE
语句 取消用户的某些权限。使用
REVOKE
收回权限之后，用户账户的记录将
从
db
、
host
、
tables_priv
和
columns_priv
表中删除，但是用户账户记录仍然在
user
表中保存（删除
user
表中 的账户记录使用
DROP USER
语句
)
。
[!NOTE]
注意：在将用户账户从
user
表删除之前，应该收回相应用户的所有权限。
收回权限命令
举例
#
收回全库全表的所有权限
GRANT SELECT
,
INSERT
,
DELETE
,
UPDATE ON
atguigudb.
*
TO
li4
@localhost
;
GRANT ALL PRIVILEGES ON
*
.
*
TO
joe
@'%' IDENTIFIED BY '123'
;
SHOW GRANTS
;
#
或
SHOW GRANTS FOR CURRENT_USER
;
#
或
SHOW GRANTS FOR CURRENT_USER
()
;
SHOW GRANTS FOR
'user'
@'
主机地址
'
;
REVOKE
权限
1,
权限
2,…
权限
n
ON
数据库名称
.
表名称
FROM
用户名
@
用户地址
;
REVOKE ALL PRIVILEGES ON
*
.
*
FROM
joe
@'%'
;
#
收回
mysql
库下的所有表的插删改查权限
注意： 须用户重新登录后才能生效
3.
权限表
3.1 user
表
user
表是
MySQL
中最重要的一个权限表， 记录用户账号和权限信息 ，有
49
个字段。如下图：
REVOKE SELECT
,
INSERT
,
UPDATE
,
DELETE ON
mysql.
*
FROM
joe
@localhost
;
这些字段可以分成
4
类，分别是范围列（或用户列）、权限列、安全列和资源控制列。
1.
范围列（或用户列）
host
： 表示连接类型
%
表示所有远程通过
TCP
方式的连接
IP
地址
如
(192.168.1.2
、
127.0.0.1)
通过制定
ip
地址进行的
TCP
方式的连接
机器名
通过制定网络中的机器名进行的
TCP
方式的连接
::1 IPv6
的本地
ip
地址，等同于
IPv4
的
127.0.0.1
localhost
本地方式通过命令行方式的连接 ，比如
mysql -u xxx -p xxx
方式的连接。
user
： 表示用户名，同一用户通过不同方式链接的权限是不一样的。
password
： 密码
所有密码串通过
password(
明文字符串
)
生成的密文字符串。
MySQL 8.0
在用户管理方面增加
了角色管理，默认的密码加密方式也做了调整，由之前的
SHA1
改为了
SHA2
，不可逆 。同
时 加上
MySQL 5.7
的禁用用户和用户过期的功能，
MySQL
在用户管理方面的功能和安全性都
较之 前版本大大的增强了。
5.7
及之后版本的密码保存到
authentication_string
字段中不再使用
password
字 段。
2.
权限列
Grant_priv
字段
表示是否拥有
GRANT
权限
Shutdown_priv
字段
表示是否拥有停止
MySQL
服务的权限
Super_priv
字段
表示是否拥有超级权限
Execute_priv
字段
表示是否拥有
EXECUTE
权限。拥有
EXECUTE
权限，可以执行存储过程和函数。
Select_priv , Insert_priv
等
为该用户所拥有的权限。
3.
安全列
安全列只有
6
个字段，其中两个是
ssl
相关的（
ssl_type
、
ssl_cipher)
，用于 加密 ；两个是
x509
相关
的（
x509_issuer
、
x509_subject)
，用于 标识用户 ；另外两个
Plugin
字段用于 验证用户身份 的插
件， 该字段不能为空。如果该字段为空，服务器就使用内建授权验证机制验证用户身份。
4.
资源控制列
资源控制列的字段用来 限制用户使用的资源 ，包含
4
个字段，分别为：
①
max_questions
，用户每小时允许执行的查询操作次数；
②
max_updates
，用户每小时允许执行的更新 操作次数；
③
max_connections
，用户每小时允许执行的连接操作次数； ④
max_user_connections
，用户 允
许同时建立的连接次数。
查看字段：
DESC
mysql
.user
;
查看用户
,
以列的方式显示数据：
SELECT
*
FROM
mysql
.user \G
;
查询特定字段：
3.2 db
表
使用
DESCRIBE
查看
db
表的基本结构：
1.
用户列
db
表用户列有
3
个字段，分别是
Host
、
User
、
Db
。这
3
个字段分别表示主机名、用户名和数据库 名。表
示从某个主机连接某个用户对某个数据库的操作权限，这
3
个字段的组合构成了
db
表的主键。
2.
权限列
Create_routine_priv
和
Alter_routine_priv
这两个字段决定用户是否具有创建和修改存储过程的权限。
3.3 tables_priv
表和
columns_priv
tables_priv
表用来 对表设置操作权限 ，
columns_priv
表用来对表的 某一列设置权限 。
tables_priv
表和
columns_priv
表的结构分别如图：
tables_priv
表有
8
个字段，分别是
Host
、
Db
、
User
、
Table_name
、
Grantor
、
Timestamp
、
Table_priv
和
Column_priv
，各个字段说明如下：
Host
、
Db
、
User
和
Table_name
四个字段分别表示主机名、数据库名、用户名和表名。
Grantor
表示修改该记录的用户。
Timestamp
表示修改该记录的时间。
Table_priv
表示对象的操作权限。包括
Select
、
Insert
、
Update
、
Delete
、
Create
、
Drop
、
Grant
、
References
、
Index
和
Alter
。
Column_priv
字段表示对表中的列的操作权限，包括
Select
、
Insert
、
Update
和
References
。
SELECT
host,
user
,authentication_string,select_priv,insert_priv,drop_priv
FROM
mysql
.user
;
DESCRIBE
mysql
.db
;
desc
mysql
.tables_priv
;
desc
mysql
.columns_priv
;
3.4 procs_priv
表
procs_priv
表可以对 存储过程和存储函数设置操作权限 ，表结构如图：
4.
访问控制
(
了解
)
4.1
连接核实阶段
当用户试图连接
MySQL
服务器时，服务器基于用户的身份以及用户是否能提供正确的密码验证身份来确
定接受或者拒绝连接。即客户端用户会在连接请求中提供用户名、主机地址、用户密码，
MySQL
服务器
接收到用户请求后，会
使用
user
表中的
host
、
user
和
authentication_string
这
3
个字段匹配客户端提
供信息
。
服务器只有在
user
表记录的
Host
和
User
字段匹配客户端主机名和用户名，并且提供正确的密码时才接受
连接。
如果连接核实没有通过，服务器就完全拒绝访问；否则，服务器接受连接，然后进入阶段
2
等待用
户请求。
4.2
请求核实阶段
一旦建立了连接，服务器就进入了访问控制的阶段
2
，也就是请求核实阶段。对此连接上进来的每个请
求，服务器检查该请求要执行什么操作、是否有足够的权限来执行它，这正是需要授权表中的权限列发
挥作用的地方。这些权限可以来自
user
、
db
、
table_priv
和
column_priv
表。
确认权限时，
MySQL
首先 检查
user
表 ，如果指定的权限没有在
user
表中被授予，那么
MySQL
就会继续
检查
db
表 ，
db
表是下一安全层级，其中的权限限定于数据库层级，在该层级的
SELECT
权限允许用户查
看指定数据库的所有表中的数据；如果在该层级没有找到限定的权限，则
MySQL
继续 检查
tables_priv
表
以及
columns_priv
表 ，如果所有权限表都检查完毕，但还是没有找到允许的权限操作，
MySQL
将 返回
错信息 ，用户请求的操作不能执行，操作失败。
[!NOTE]
提示：
MySQL
通过向下层级的顺序（从
user
表到
columns_priv
表）检查权限表，但并不是所有的
权 限都要执行该过程。例如，一个用户登录到
MySQL
服务器之后只执行对
MySQL
的管理操作，此
时只 涉及管理权限，因此
MySQL
只检查
user
表。另外，如果请求的权限操作不被允许，
MySQL
也
不会继 续检查下一层级的表。
desc
mysql
.procs_priv
;
5.
角色管理
5.1
角色的理解
引入角色的目的是 方便管理拥有相同权限的用户 。
恰当的权限设定，可以确保数据的安全性，这是至关
重要的。
5.2
创建角色
创建角色使用
CREATE ROLE
语句，语法如下：
角色名称的命名规则和用户名类似。如果
host_name
省略，默认为
%
，
role_name
不可省略 ，不可为
空。
练习：我们现在需要创建一个经理的角色，就可以用下面的代码：
5.3
给角色赋予权限
创建角色之后，默认这个角色是没有任何权限的，我们需要给角色授权。给角色授权的语法结构是：
上述语句中
privileges
代表权限的名称，多个权限以逗号隔开。可使用
SHOW
语句查询权限名称，图
11-
43
列出了部分权限列表。
CREATE
ROLE
'role_name'
[
@'host_name'] [,'role_name'[@'host_name'
]]
...
CREATE
ROLE
'manager'
@'localhost'
;
GRANT privileges ON table_name TO
'role_name'
[
@'host_name'
]
;
SHOW PRIVILEGES
\G
;
练习
1
：我们现在想给经理角色授予商品信息表、盘点表和应付账款表的只读权限，就可以用下面的代码
来实现：
5.4
查看角色的权限
赋予角色权限之后，我们可以通过
SHOW GRANTS
语句，来查看权限是否创建成功了：
只要你创建了一个角色，系统就会自动给你一个
“ USAGE ”
权限，意思是 连接登录数据库的权限 。代码
的最后三行代表了我们给角色
“manager”
赋予的权限，也就是对商品信息表、盘点表和应付账款表的只
读权限。
GRANT SELECT ON
demo
.settlement
TO
'manager'
;
GRANT SELECT ON
demo
.goodsmaster
TO
'manager'
;
GRANT SELECT ON
demo
.invcount
TO
'manager'
;
mysql
>
SHOW GRANTS FOR
'manager'
;
+-------------------------------------------------------+
|
Grants for
manager@
% |
+-------------------------------------------------------+
|
GRANT USAGE ON
*
.
*
TO
`manager`@`%`
|
|
GRANT SELECT ON
`demo`
.
`goodsmaster`
TO
`manager`@`%`
|
|
GRANT SELECT ON
`demo`
.
`invcount`
TO
`manager`@`%`
|
|
GRANT SELECT ON
`demo`
.
`settlement`
TO
`manager`@`%`
|
+-------------------------------------------------------+
结果显示，库管角色拥有商品信息表的只读权限和盘点表的增删改查权限。
5.5
回收角色的权限
角色授权后，可以对角色的权限进行维护，对权限进行添加或撤销。添加权限使用
GRANT
语句，与角色
授权相同。撤销角色或角色权限使用
REVOKE
语句。 修改了角色的权限，会影响拥有该角色的账户的权
限。
撤销角色权限的
SQL
语法如下：
练习
1
：撤销
school_write
角色的权限。
（
1
）使用如下语句撤销
school_write
角色的权限。
（
2
）撤销后使用
SHOW
语句查看
school_write
对应的权限，语句如下。
5.6
删除角色
当我们需要对业务重新整合的时候，可能就需要对之前创建的角色进行清理，删除一些不会再使用的角
色。删除角色的操作很简单，你只要掌握语法结构就行了。
注意， 如果你删除了角色，那么用户也就失去了通过这个角色所获得的所有权限 。
练习：执行如下
SQL
删除角色
school_read
。
5.7
给用户赋予角色
角色创建并授权后，要赋给用户并处于 激活状态 才能发挥作用。给用户添加角色可使用
GRANT
语句，语
法形式如下：
在上述语句中，
role
代表角色，
user
代表用户。可将多个角色同时赋予多个用户，用逗号隔开即可。
练习：给
kangshifu
用户添加角色
school_read
权限。
（
1
）使用
GRANT
语句给
kangshifu
添加
school_read
权 限，
SQL
语句如下。
（
2
）添加完成后使用
SHOW
语句查看是否添加成功，
SQL
语句如下。
REVOKE privileges ON
tablename
FROM
'rolename'
;
REVOKE INSERT
,
UPDATE
,
DELETE ON
school.
*
FROM
'school_write'
;
SHOW GRANTS FOR
'school_write'
;
DROP
ROLE role
[
,role2
]
...
DROP
ROLE
'school_read'
;
GRANT
role
[
,role2,...
]
TO user
[
,user2,...
]
;
GRANT
'school_read'
TO
'kangshifu'
@'localhost'
;
（
3
）使用
kangshifu
用户登录，然后查询当前角色，如果角色未激活，结果将显示
NONE
。
SQL
语句如
下。
5.8
激活角色
方式
1
：使用
set default role
命令激活角色
举例：
举例：使用
SET DEFAULT ROLE
为下面
4
个用户默认激活所有已拥有的角色如下：
方式
2
：将
activate_all_roles_on_login
设置为
ON
默认情况：
设置：
这条
SQL
语句的意思是，对所有角色永久激活 。运行这条语句之后，用户才真正拥有了赋予角色的所有
权限。
SHOW GRANTS FOR
'kangshifu'
@'localhost'
;
SELECT
CURRENT_ROLE
()
;
SET DEFAULT
ROLE
ALL TO
'kangshifu'
@'localhost'
;
SET DEFAULT
ROLE
ALL TO
'dev1'
@'localhost', 'read_user1'@'localhost',
'read_user2'@'localhost', 'rw_user1'@'localhost'
;
mysql
>
show variables like
'activate_all_roles_on_login'
;
+-----------------------------+-------+
|
Variable_name
|
Value
|
+-----------------------------+-------+
|
activate_all_roles_on_login
|
OFF
|
+-----------------------------+-------+
1
row in set
(
0.00
sec
)
SET GLOBAL
activate_all_roles_on_login
=
ON
;
5.9
撤销用户的角色
撤销用户角色的
SQL
语法如下：
练习：撤销
kangshifu
用户的
school_read
角色。
（
1
）撤销的
SQL
语句如下
（
2
）撤销后，执行如下查询语句，查看
kangshifu
用户的角色信息
执行发现，用户
kangshifu
之前的
school_read
角色已被撤销。
5.10
设置强制角色
(mandatory role)
方式
1
：服务启动前设置
方式
2
：运行时设置
第
5
章
MySQL
约束
1.
约束
(constraint)
概述
1.1
为什么需要约束
数据完整性（
Data Integrity
）是指数据的精确性（
Accuracy
）和可靠性（
Reliability
）。它是防止数据
库中 存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。
为了保证数据的完整性，
SQL
规范以约束的方式对
表数据进行额外的条件限制
。从以下四个方面考虑：
实体完整性（
Entity Integrity)
：例如，同一个表中，不能存在两条完全相同无法区分的记录
域完整性（
Domain Integrity
） ：例如：年龄范围
0-120
，性别范围
“
男
/
女
”
引用完整性（
Referential Integrity
） ：例如：员工所在部门，在部门表中要能找到这个部门
用户自定义完整性（
User-defined Integrity)
：例如：用户名唯一、密码不能为空等，本部门经理
的工资不得高于本部门职工的平均工资的
5
倍。
REVOKE
role
FROM user
;
REVOKE
'school_read'
FROM
'kangshifu'
@'localhost'
;
SHOW GRANTS FOR
'kangshifu'
@'localhost'
;
[
mysqld
]
mandatory_roles
=
'role1,role2@localhost,r3@%.atguigu.com'
SET
PERSIST mandatory_roles
=
'role1,role2@localhost,r3@%.example.com'
;
#
系统重启
后仍然有效
SET GLOBAL
mandatory_roles
=
'role1,role2@localhost,r3@%.example.com'
;
#
系统重启后
失效
1.2
什么是约束
约束是表级的强制规定。
可以在
创建表时规定约束（通过
CREATE TABLE
语句），或者在
表创建之后通过
ALTER TABLE
语句规
定
约束
。
1.3
约束的分类
根据约束数据列的限制
，约束可分为：
单列约束
：每个约束只约束一列
多列约束
：每个约束可约束多列数据
根据约束的作用范围
，约束可分为
列级约束
：只能作用在一个列上，跟在列的定义后面
表级约束
：可以作用在多个列上，不与列一起，而是单独定义
根据约束起的作用
，约束可分为：
NOT NULL
非空约束，规定某个字段不能为空
UNIQUE
唯一约束，
规定某个字段在整个表中是唯一的
PRIMARY KEY
主键
(
非空且唯一
)
约束
FOREIGN KEY
外键约束
CHECK
检查约束
DEFAULT
默认值约束
查看某个表已有的约束
2.
非空约束
2.1
作用
限定某个字段
/
某列的值不允许为空
位置 支持的约束类型 是否可以起约束名
列级约束： 列的后面 语法都支持，但外键没有效果 不可以
表级约束： 所有列的下面 默认和非空不支持，其他支持 可以（主键没有效果）
#information_schema
数据库名（系统库）
#table_constraints
表名称（专门存储各个表的约束
)
SELECT
*
FROM
information_schema
.table_constraints
WHERE table_name
=
'
表名称
'
;
2.2
关键字
NOT NULL
2.3
特点
默认，所有的类型的值都可以是
NULL
，包括
INT
、
FLOAT
等数据类型
非空约束只能出现在表对象的列上，只能某个列单独限定非空，不能组合非空
一个表可以有很多列都分别限定了非空
空字符串
''
不等于
NULL
，
0
也不等于
NULL
2.4
添加非空约束
（
1
）建表时
举例：
CREATE TABLE
表名称
(
字段名 数据类型
,
字段名 数据类型
NOT
NULL
,
字段名 数据类型
NOT
NULL
)
;
CREATE TABLE
emp
(
id INT
(
10
)
NOT
NULL
,
NAME VARCHAR
(
20
)
NOT
NULL
,
sex CHAR
NULL
)
;
CREATE TABLE
student
(
sid int,
sname varchar
(
20
)
not
null
, tel char
(
11
)
,
cardid char
(
18
)
not
null
)
;
insert into
student
values
(
1
,
'
张三
'
,
'13710011002'
,
'110222198912032545'
)
;
#
成功
insert into
student
values
(
2
,
'
李四
'
,
'13710011002'
,
null
)
;
#
身份证号为空
ERROR 1048 (23000): Column 'cardid' cannot be null
insert into
student
values
(
2
,
'
李四
'
,
null
,
'110222198912032546'
)
;
#
成功，
tel
允许为空
（
2
）建表后
举例：
2. 5
删除非空约束
举例：
3.
唯一性约束
3.1
作用
用来限制某个字段
/
某列的值不能重复。
3.2
关键字
UNIQUE
3.3
特点
同一个表可以有多个唯一约束。
唯一约束可以是某一个列的值唯一，也可以多个列组合的值唯一。
唯一性约束允许列值为空。
在创建唯一约束的时候，如果不给唯一约束命名，就默认和列名相同。
MySQL
会给唯一约束的列上默认创建一个唯一索引。
insert into
student
values
(
3
,
null
,
null
,
'110222198912032547'
)
;
#
失败
ERROR 1048 (23000): Column 'sname' cannot be null
alter table
表名称
modify
字段名 数据类型
not
null
;
ALTER TABLE
emp
MODIFY
sex VARCHAR
(
30
)
NOT
NULL
;
alter table
student
modify
sname varchar
(
20
)
not
null
;
alter table
表名称
modify
字段名 数据类型
NULL
;
#
去掉
not null
，相当于修改某个非注解字段，该
字段允
许为空
或
alter table
表名称
modify
字段名 数据类型
;
#
去掉
not null
，相当于修改某个非注解字段，该字段允
许为空
ALTER TABLE
emp
MODIFY
sex VARCHAR
(
30
)
NULL
;
ALTER TABLE
emp
MODIFY
NAME VARCHAR
(
15
)
DEFAULT
'abc'
NULL
;
3.4
添加唯一约束
（
1
）建表时
举例：
create table
表名称
(
字段名 数据类型
unique
,
字段名 数据类型
unique key
,
字段名 数据类型
)
;
create table
表名称
(
字段名 数据类型
,
字段名 数据类型
,
字段名 数据类型
,
[
constraint
约束名
]
unique key
(
字段名
)
)
;
create table
student
(
sid int,
sname varchar
(
20
)
,
tel char
(
11
)
unique
,
cardid char
(
18
)
unique key
)
;
CREATE TABLE
t_course
(
cid INT
UNIQUE
,
cname VARCHAR
(
100
)
UNIQUE
,
description VARCHAR
(
200
)
)
;
CREATE TABLE USER
(
id INT
NOT
NULL
,
NAME VARCHAR
(
25
)
,
PASSWORD
VARCHAR
(
16
)
,
--
使用表级约束语法
CONSTRAINT
uk_name_pwd
UNIQUE
(
NAME,
PASSWORD
)
)
;
表示用户名和密码组合不能重复
insert into
student
values
(
1
,
'
张三
'
,
'13710011002'
,
'101223199012015623'
)
;
insert into
student
values
(
2
,
'
李四
'
,
'13710011003'
,
'101223199012015624'
)
;
mysql
>
select
*
from
student;
+-----+-------+-------------+--------------------+
|
sid
|
sname
|
tel
|
cardid
|
+-----+-------+-------------+--------------------+
|
1
|
张三
|
13710011002
|
101223199012015623
|
|
2
|
李四
|
13710011003
|
101223199012015624
|
+-----+-------+-------------+--------------------+
2
rows
in set
(
0.00
sec
)
（
2
）建表后指定唯一键约束
举例：
3.5
关于复合唯一约束
insert into
student
values
(
3
,
'
王五
'
,
'13710011004'
,
'101223199012015624'
)
;
#
身份证号
重复
ERROR
1062
(
23000
)
: Duplicate entry
'101223199012015624'
for key
'cardid'
insert into
student
values
(
3
,
'
王五
'
,
'13710011003'
,
'101223199012015625'
)
;
ERROR
1062
(
23000
)
: Duplicate entry
'13710011003'
for key
'tel'
#
字段列表中如果是一个字段，表示该列的值唯一。如果是两个或更多个字段，那么复合唯一，即多个字段的组
合是唯一的
#
方式
1
：
alter table
表名称
add unique key
(
字段列表
)
;
#
方式
2
：
alter table
表名称
modify
字段名 字段类型
unique
;
ALTER TABLE USER
ADD UNIQUE
(
NAME,
PASSWORD
)
;
ALTER TABLE USER
MODIFY
NAME VARCHAR
(
20
)
UNIQUE
;
create table
student
(
sid int
primary key
,
sname varchar
(
20
)
,
tel char
(
11
)
,
cardid char
(
18
)
)
;
alter table
student
add unique key
(
tel
)
;
alter table
student
add unique key
(
cardid
)
;
create table
表名称
(
字段名 数据类型
,
字段名 数据类型
,
字段名 数据类型
,
unique key
(
字段列表
)
#
字段列表中写的是多个字段名，多个字段名用逗号分隔，表示那么是复合唯
一，即多个字段的组合是唯一的
)
;
#
学生表
create table
student
(
sid int,
#
学号
sname varchar
(
20
)
,
#
姓名
tel char
(
11
)
unique key
,
#
电话
cardid char
(
18
)
unique key
#
身份证号
)
;
#
课程表
create table
course
(
cid int,
#
课程编号
cname varchar
(
20
)
#
课程名称
)
;
#
选课表
create table
student_course
(
id int,
sid int,
cid int,
score int,
unique key
(
sid,cid
)
#
复合唯一
)
;
insert into
student
values
(
1
,
'
张三
'
,
'13710011002'
,
'101223199012015623'
)
;
#
成功
insert into
student
values
(
2
,
'
李四
'
,
'13710011003'
,
'101223199012015624'
)
;
#
成功
insert into
course
values
(
1001
,
'Java'
)
,
(
1002
,
'MySQL'
)
;
#
成功
mysql
>
select
*
from
student;
+-----+-------+-------------+--------------------+
|
sid
|
sname
|
tel
|
cardid
|
+-----+-------+-------------+--------------------+
|
1
|
张三
|
13710011002
|
101223199012015623
|
|
2
|
李四
|
13710011003
|
101223199012015624
|
+-----+-------+-------------+--------------------+
2
rows
in set
(
0.00
sec
)
mysql
>
select
*
from
course;
+------+-------+
|
cid
|
cname
|
+------+-------+
|
1001
|
Java
|
|
1002
|
MySQL
|
+------+-------+
2
rows
in set
(
0.00
sec
)
insert into
student_course
values
(
1
,
1
,
1001
,
89
)
,
(
2
,
1
,
1002
,
90
)
,
(
3
,
2
,
1001
,
88
)
,
(
4
,
2
,
1002
,
56
)
;
#
成功
mysql
>
select
*
from
student_course;
+----+------+------+-------+
|
id
|
sid
|
cid
|
score
|
+----+------+------+-------+
|
1
|
1
|
1001
|
89
|
|
2
|
1
|
1002
|
90
|
|
3
|
2
|
1001
|
88
|
|
4
|
2
|
1002
|
56
|
+----+------+------+-------+
4
rows
in set
(
0.00
sec
)
insert into
student_course
values
(
5
,
1
,
1001
,
88
)
;
#
失败
#ERROR 1062 (23000): Duplicate entry '1-1001' for key 'sid'
违反
sid-cid
的复合唯一
3.6
删除唯一约束
添加唯一性约束的列上也会自动创建唯一索引。
删除唯一约束只能通过删除唯一索引的方式删除。
删除时需要指定唯一索引名，唯一索引名就和唯一约束名一样。
如果创建唯一约束时未指定名称，如果是单列，就默认和列名相同；如果是组合列，那么默认和
()
中排在第一个的列名相同。也可以自定义唯一性约束名。
Caution
注意：可以通过
show index from
表名称
;
查看表的索引
Caution
注意：可以通过
show index from
表名称
;
查看表的索引
4. PRIMARY KEY
约束
4.1
作用
用来唯一标识表中的一行记录。
4.2
关键字
primary key
4.3
特点
主键约束相当于
唯一约束
+
非空约束的组合
，主键约束列不允许重复，也不允许出现空值。
SELECT
*
FROM
information_schema
.table_constraints
WHERE table_name
=
'
表名
'
;
#
查
看都有哪些约束
ALTER TABLE USER
DROP INDEX
uk_name_pwd;
一个表最多只能有一个主键约束，建立主键约束可以在列级别创建，也可以在表级别上创建。
主键约束对应着表中的一列或者多列（复合主键）
如果是多列组合的复合主键约束，那么这些列都不允许为空值，并且组合的值不允许重复。
MySQL
的主键名总是
PRIMARY
，就算自己命名了主键约束名也没用
当创建主键约束时，系统默认会在所在的列或列组合上建立对应的
主键索引
（能够根据主键查询
的，就根据主键查询，效率更高
)
。如果删除主键约束了，主键约束对应的索引就自动删除了。
需要注意的一点是，不要修改主键字段的值。因为主键是数据记录的唯一标识，如果修改了主键的
值，就有可能会破坏数据的完整性。
4.4
添加主键约束
（
1
）建表时指定主键约束
举例：
create table
表名称
(
字段名 数据类型
primary key
,
#
列级模式
字段名 数据类型
,
字段名 数据类型
)
;
create table
表名称
(
字段名 数据类型
,
字段名 数据类型
,
字段名 数据类型
,
[
constraint
约束名
]
primary key
(
字段名
)
#
表级模式
)
;
create table
temp
(
id int
primary key
,
name varchar
(
20
)
)
;
mysql
>
desc
temp;
+-------+-------------+------+-----+---------+-------+
|
Field
|
Type
|
Null
|
Key
|
Default
|
Extra
|
+-------+-------------+------+-----+---------+-------+
|
id
|
int
(
11
)
|
NO
|
PRI
|
NULL
| |
|
name
|
varchar
(
20
)
|
YES
| |
NULL
| |
+-------+-------------+------+-----+---------+-------+
2
rows
in set
(
0.00
sec
)
insert into
temp
values
(
1
,
'
张三
'
)
;
#
成功
insert into
temp
values
(
2
,
'
李四
'
)
;
#
成功
mysql
>
select
*
from
temp;
+----+------+
|
id
|
name
|
+----+------+
|
1
|
张三
|
|
2
|
李四
|
+----+------+
2
rows
in set
(
0.00
sec
)
insert into
temp
values
(
1
,
'
张三
'
)
;
#
失败
ERROR
1062
(
23000
)
: Duplicate
（重复）
entry
（键入，输入）
'1'
for key
'PRIMARY'
insert into
temp
values
(
1
,
'
王五
'
)
;
#
失败
ERROR
1062
(
23000
)
: Duplicate entry
'1'
for key
'PRIMARY'
insert into
temp
values
(
3
,
'
张三
'
)
;
#
成功
mysql
>
select
*
from
temp;
+----+------+
|
id
|
name
|
+----+------+
|
1
|
张三
|
|
2
|
李四
|
|
3
|
张三
|
+----+------+
3
rows
in set
(
0.00
sec
)
insert into
temp
values
(
4
,
null
)
;
#
成功
insert into
temp
values
(
null
,
'
李琦
'
)
;
#
失败
ERROR
1048
(
23000
)
:
Column
'id'
cannot be
null
mysql
>
select
*
from
temp;
+----+------+
|
id
|
name
|
+----+------+
|
1
|
张三
|
|
2
|
李四
|
|
3
|
张三
|
|
4
|
NULL
|
列级约束
表级约束
（
2
）建表后增加主键约束
4.5
关于符合主键
+----+------+
4
rows
in set
(
0.00
sec
)
#
演示一个表建立两个主键约束
create table
temp
(
id int
primary key
,
name varchar
(
20
)
primary key
)
;
ERROR
1068
(
42000
)
: Multiple
（多重的）
primary key
defined
（定义）
CREATE TABLE
emp4
(
id INT
PRIMARY KEY AUTO_INCREMENT
,
NAME VARCHAR
(
20
)
)
;
CREATE TABLE
emp5
(
id INT
NOT
NULL
AUTO_INCREMENT
,
NAME VARCHAR
(
20
)
,
pwd VARCHAR
(
15
)
,
CONSTRAINT
emp5_id_pk
PRIMARY KEY
(
id
)
)
;
ALTER TABLE
表名称
ADD PRIMARY KEY
(
字段列表
)
;
#
字段列表可以是一个字段，也可以是多个字段，如
果是多个字段的话，是复合主键
ALTER TABLE
emp5
ADD PRIMARY KEY
(
NAME,pwd
)
;
create table
表名称
(
字段名 数据类型
,
字段名 数据类型
,
字段名 数据类型
,
primary key
(
字段名
1,
字段名
2
)
#
表示字段
1
和字段
2
的组合是唯一的，也可以有更多个字段
)
;
#
学生表
create table
student
(
sid int
primary key
,
#
学号
sname varchar
(
20
)
#
学生姓名
)
;
#
课程表
create table
course
(
cid int
primary key
,
#
课程编号
cname varchar(20) #
课程名称
)
;
#
选课表
create table
student_course
(
sid int,
cid int,
score int,
primary key
(
sid,cid
)
#
复合主键
)
;
insert into
student
values
(
1
,
'
张三
'
)
,
(
2
,
'
李四
'
)
;
insert into
course
values
(
1001
,
'Java'
)
,
(
1002
,
'MySQL'
)
;
mysql
>
select
*
from
student;
+-----+-------+
|
sid
|
sname
|
+-----+-------+
|
1
|
张三
|
|
2
|
李四
|
+-----+-------+
2
rows
in set
(
0.00
sec
)
mysql
>
select
*
from
course;
+------+-------+
|
cid
|
cname
|
+------+-------+
|
1001
|
Java
| |
1002
|
MySQL
|
+------+-------+
2
rows
in set
(
0.00
sec
)
insert into
student_course
values
(
1
,
1001
,
89
)
,
(
1
,
1002
,
90
)
,
(
2
,
1001
,
88
)
,
(
2
,
1002
,
56
)
;
mysql
>
select
*
from
student_course;
+-----+------+-------+
|
sid
|
cid
|
score
|
+-----+------+-------+
|
1
|
1001
|
89
|
|
1
|
1002
|
90
|
|
2
|
1001
|
88
|
|
2
|
1002
|
56
|
+-----+------+-------+
4
rows
in set
(
0.00
sec
)
insert into
student_course
values
(
1
,
1001
,
100
)
;
ERROR
1062
(
23000
)
: Duplicate entry
'1-1001'
for key
'PRIMARY'
mysql
>
desc
student_course;
+-------+---------+------+-----+---------+-------+
|
Field
|
Type
|
Null
|
Key
|
Default
|
Extra
|
+-------+---------+------+-----+---------+-------+
|
sid
|
int
(
11
)
|
NO
|
PRI
|
NULL
| |
|
cid
|
int
(
11
)
|
NO
|
PRI
|
NULL
| |
|
score
|
int
(
11
)
|
YES
| |
NULL
| |
+-------+---------+------+-----+---------+-------+
3
rows
in set
(
0.00
sec
)
4.6
删除主键约束
举例：
Caution
说明：删除主键约束，不需要指定主键名，因为一个表只有一个主键，删除主键约束后，非空还存
在。
5.
自增列
AUTO_INCREMENT
5.1
作用
某个字段的值自增
5.2
关键字
auto_increment
5.3
特点和要求
（
1
）一个表最多只能有一个自增长列
（
2
）当需要产生唯一标识符或顺序值时，可设置自增长
（
3
）自增长列约束的列必须是键列（主键列，唯一键列）
（
4
）自增约束的列的数据类型必须是整数类型
（
5
）如果自增列指定了
0
和
null
，会在当前最大值的基础上自增；如果自增列手动指定了具体值，直接
赋值为具体值。
错误演示：
CREATE TABLE
emp6
(
id INT
NOT
NULL
,
NAME VARCHAR
(
20
)
,
pwd VARCHAR
(
15
)
,
CONSTRAINT
emp7_pk
PRIMARY KEY
(
NAME,pwd
)
)
;
alter table
表名称
drop primary key
;
ALTER TABLE
student
DROP PRIMARY KEY
;
ALTER TABLE
emp5
DROP PRIMARY KEY
;
5.4
如何指定自增约束
（
1
）建表时
示例
（
2
）建表后
create table
employee
(
eid int
auto_increment
,
ename varchar
(
20
)
)
;
# ERROR 1075 (42000): Incorrect table definition; there can be only one auto
column and it must be defined as a key
create table
employee
(
eid int
primary key
,
ename varchar
(
20
)
unique key auto_increment
)
;
# ERROR 1063 (42000): Incorrect column specifier for column 'ename'
因为
ename
不是
整数类型
create table
表名称
(
字段名 数据类型
primary key auto_increment
,
字段名 数据类型
unique key not
null
,
字段名 数据类型
unique key
,
字段名 数据类型
not
null
default
默认值
,
)
;
create table
表名称
(
字段名 数据类型
default
默认值
,
字段名 数据类型
unique key auto_increment
,
字段名 数据类型
not
null
default
默认值
,,
primary key
(
字段名
)
)
;
create table
employee
(
eid int
primary key auto_increment
,
ename varchar
(
20
)
)
;
mysql
>
desc
employee;
+-------+-------------+------+-----+---------+----------------+
|
Field
|
Type
|
Null
|
Key
|
Default
|
Extra
|
+-------+-------------+------+-----+---------+----------------+
|
eid
|
int
(
11
)
|
NO
|
PRI
|
NULL
|
auto_increment
|
|
ename
|
varchar
(
20
)
|
YES
| |
NULL
| |
+-------+-------------+------+-----+---------+----------------+
2
rows
in set
(
0.00
sec
)
alter table
表名称
modify
字段名 数据类型
auto_increment
;
示例：
5.5
如何删除自增约束
5.6 MySQL 8.0
新特性
—
自增变量的持久化
在
MySQL 8.0
之前，自增主键
AUTO_INCREMENT
的值如果大于
max(primary key)+1
，在
MySQL
重启
后，会重置
AUTO_INCREMENT=max(primary key)+1
，这种现象在某些情况下会导致业务主键冲突或者
其他难以发 现的问题。 下面通过案例来对比不同的版本中自增变量是否持久化。 在
MySQL 5.7
版本中，
测试步骤如 下： 创建的数据表中包含自增主键的
id
字段，语句如下：
插入
4
个空值，执行如下
:
查询数据表
test1
中的数据，结果如下：
create table
employee
(
eid int
primary key
,
ename varchar
(
20
)
)
;
mysql
>
desc
employee;
+-------+-------------+------+-----+---------+----------------+
|
Field
|
Type
|
Null
|
Key
|
Default
|
Extra
|
+-------+-------------+------+-----+---------+----------------+
|
eid
|
int
(
11
)
|
NO
|
PRI
|
NULL
|
auto_increment
|
|
ename
|
varchar
(
20
)
|
YES
| |
NULL
| |
+-------+-------------+------+-----+---------+----------------+
2
rows
in set
(
0.00
sec
)
#alter table
表名称
modify
字段名 数据类型
auto_increment;#
给这个字段增加自增约束
alter table
表名称
modify
字段名 数据类型
;
#
去掉
auto_increment
相当于删除
alter table
employee
modify
eid int;
mysql
>
desc
employee;
+-------+-------------+------+-----+---------+-------+
|
Field
|
Type
|
Null
|
Key
|
Default
|
Extra
|
+-------+-------------+------+-----+---------+-------+
|
eid
|
int
(
11
)
|
NO
|
PRI
|
NULL
| |
|
ename
|
varchar
(
20
)
|
YES
| |
NULL
| |
+-------+-------------+------+-----+---------+-------+
2
rows
in set
(
0.00
sec
)
CREATE TABLE
test1
(
id INT
PRIMARY KEY AUTO_INCREMENT
)
;
INSERT INTO
test1
VALUES
(
0
)
,
(
0
)
,
(
0
)
,
(
0
)
;
mysql
>
SELECT
*
FROM
test1;
+----+
|
id
|
+----+
|
1
|
|
2
|
|
3
|
|
4
|
+----+
4
rows
in set
(
0.00
sec
)
删除
id
为
4
的记录，语句如下：
DELETE FROM
test1
WHERE
id
=
4
;
再次插入一个空值，语句如下：
INSERT INTO
test1
VALUES
(
0
)
;
查询此时数据表
test1
中的数据，结果如下：
mysql
>
SELECT
*
FROM
test1;
+----+
|
id
|
+----+
|
1
|
|
2
|
|
3
|
|
5
|
+----+
4
rows
in set
(
0.00
sec
)
从结果可以看出，虽然删除了
id
为
4
的记录，但是再次插入空值时，并没有重用被删除的
4
，而是分配了
5
。 删除
id
为
5
的记录，结果如下：
DELETE FROM
test1
where
id
=
5
;
重启数据库
，重新插入一个空值。
INSERT INTO
test1
values
(
0
)
;
再次查询数据表
test1
中的数据，结果如下：
从结果可以看出，新插入的
0
值分配的是
4
，按照重启前的操作逻辑，此处应该分配
6
。出现上述结果的主
要原因是自增主键没有持久化。 在
MySQL 5.7
系统中，对于自增主键的分配规则，是由
InnoDB
数据字典
内部一个 计数器 来决定的，而该计数器只在 内存中维护 ，并不会持久化到磁盘中。当数据库重启时，
该 计数器会被初始化。
在
MySQL 8.0
版本中，上述测试步骤最后一步的结果如下：
从结果可以看出，自增变量已经持久化了。
MySQL 8.0
将自增主键的计数器持久化到 重做日志 中。每次计数器发生改变，都会将其写入重做日志
中。如果数据库重启，
InnoDB
会根据重做日志中的信息来初始化计数器的内存值。
6. FOREIGN KEY
约束
6.1
作用
限定某个表的某个字段的引用完整性。 比如：员工表的员工所在部门的选择，必须在部门表能找到对应
的部分。
mysql
>
SELECT
*
FROM
test1;
+----+
|
id
|
+----+
|
1
|
|
2
|
|
3
|
|
4
|
+----+
4
rows
in set
(
0.00
sec
)
mysql> SELECT * FROM test1;
+
----
+
| id |
+
----
+
|
1
|
|
2
|
|
3
|
|
6
|
+
----
+
4
rows
in set
(0.00 sec)
6.2
关键字
FOREIGN KEY
6.3
主表和从表
/
父表和子表
主表（父表）：被引用的表，被参考的表
从表（子表）：引用别人的表，参考别人的表 例如：员工表的员工所在部门这个字段的值要参考部门
表：部门表是主表，员工表是从表。 例如：学生表、课程表、选课表：选课表的学生和课程要分别参考
学生表和课程表，学生表和课程表是 主表，选课表是从表。
6.4
特点
（
1
）从表的外键列，必须引用
/
参考主表的主键或唯一约束的列
为什么？因为被依赖
/
被参考的值必须是唯一的
（
2
）在创建外键约束时，如果不给外键约束命名，
默认名不是列名，而是自动产生一个外键名
（例如
student_ibfk_1;
），也可以指定外键约束名。
（
3
）创建
(CREATE)
表时就指定外键约束的话，先创建主表，再创建从表
（
4
）删表时，先删从表（或先删除外键约束），再删除主表
（
5
）当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖
该记录的数据，然后才可以删除主表的数据
（
6
）在
“
从表
”
中指定外键约束，并且一个表可以建立多个外键约束
（
7
）从表的外键列与主表被参照的列名字可以不相同，但是数据类型必须一样，逻辑意义一致。如果类
型不一样，创建子表时，就会出现错误
“ERROR 1005 (HY000): Can't create
table'database.tablename'(errno: 150)”
。
例如：都是表示部门编号，都是
int
类型。
（
8
）
当创建外键约束时，系统默认会在所在的列上建立对应的普通索引
。但是索引名是外键的约束 名。
（根据外键查询效率很高
)
（
9)
删除外键约束后，必须 手动 删除对应的索引
6.5
添加外键约束
（
1
）建表时
示例
（
2
）建表后 一般情况下，表与表的关联都是提前设计好了的，因此，会在创建表的时候就把外键约束定
义好。不 过，如果需要修改表的设计（比如添加新的字段，增加新的关联关系），但没有预先定义外键
约束，那 么，就要用修改表的方式来补充定义。
格式
示例
create table
主表名称
(
字段
1
数据类型
primary key
,
字段
2
数据类型
)
;
create table
从表名称
(
字段
1
数据类型
primary key
,
字段
2
数据类型
,
[
CONSTRAINT
<
外键约束名称
>
]
FOREIGN KEY
（从表的某个字段
)
references
主表名
(
被参考字
段
)
)
;
#(
从表的某个字段
)
的数据类型必须与主表名
(
被参考字段
)
的数据类型一致，逻辑意义也一样
#(
从表的某个字段
)
的字段名可以与主表名
(
被参考字段
)
的字段名一样，也可以不一样
-- FOREIGN KEY:
在表级指定子表中的列
-- REFERENCES:
标示在父表中的列
create table
dept
(
#
主表
did int
primary key
,
#
部门编号
dname varchar
(
50
)
#
部门名称
)
;
create table
emp
(
#
从表
eid int
primary key
,
#
员工编号
ename varchar
(
5
)
,
#
员工姓名
deptid int,
#
员工所在的部门
foreign key
(
deptid
)
references
dept
(
did
)
#
在从表中指定外键约束
#emp
表的
deptid
和和
dept
表的
did
的数据类型一致，意义都是表示部门的编号
)
;
说明：
（
1
）主表
dept
必须先创建成功，然后才能创建
emp
表，指定外键成功。
（
2
）删除表时，先删除从表
emp
，再删除主表
dept
ALTER TABLE
从表名
ADD
[
CONSTRAINT
约束名
]
FOREIGN KEY
(
从表的字段
)
REFERENCES
主表名
(
被引用 字段
) [
on update
xx
][
on delete
xx
]
;
6.6
演示问题
（
1
）失败：不是键列
（
2
）失败：数据类型不一致
（
3
）成功，两个表字段名一样
create table
dept
(
did int
primary key
,
#
部门编号
dname varchar
(
50
)
#
部门名称
)
;
create table
emp
(
eid int
primary key
,
#
员工编号
ename varchar
(
5
)
,
#
员工姓名
deptid int
#
员工所在的部门
)
;
#
这两个表创建时，没有指定外键的话，那么创建顺序是随意
alter table
emp
add foreign key
(
deptid
)
references
dept
(
did
)
;
create table
dept
(
did int ,
#
部门编号
dname varchar
(
50
)
#
部门名称
)
;
create table
emp
(
eid int
primary key
,
#
员工编号
ename varchar
(
5
)
,
#
员工姓名
deptid int,
#
员工所在的部门
foreign key
(
deptid
)
references
dept
(
did
)
)
;
#ERROR 1215 (HY000): Cannot add foreign key constraint
原因是
dept
的
did
不是键列
create table
dept
(
did int
primary key
,
#
部门编号
dname varchar
(
50
)
#
部门名称
)
;
create table
emp
(
eid int
primary key
,
#
员工编号
ename varchar
(
5
)
,
#
员工姓名
deptid char,
#
员工所在的部门
foreign key
(
deptid
)
references
dept
(
did
)
)
;
#ERROR 1215 (HY000): Cannot add foreign key constraint
原因是从表的
deptid
字段和主表
的
did
字
段的数据类型不一致，并且要它俩的逻辑意义一致
create table
dept
(
did int
primary key
,
#
部门编号
dname varchar
(
50
)
#
部门名称
)
;
create table
emp
(
eid int
primary key
,
#
员工编号
ename varchar
(
5
)
,
#
员工姓名
did int,
#
员工所在的部门
foreign key
(
did
)
references
dept
(
did
)
#emp
表的
deptid
和和
dept
表的
did
的数据类型一致，意义都是表示部门的编号
#
是否重名没问题，因为两个
did
在不同的表中
)
;
（
4
）添加、删除、修改问题
create table
dept
(
did int
primary key
,
#
部门编号
dname varchar
(
50
)
#
部门名称
)
;
create table
emp
(
eid int
primary key
,
#
员工编号
ename varchar
(
5
)
,
#
员工姓名
deptid int,
#
员工所在的部门
foreign key
(
deptid
)
references
dept
(
did
)
#emp
表的
deptid
和和
dept
表的
did
的数据类型一致，意义都是表示部门的编号
)
;
insert into
dept
values
(
1001
,
'
教学部
'
)
;
insert into
dept
values
(
1003
,
'
财务部
'
)
;
insert into
emp
values
(
1
,
'
张三
'
,
1001
)
;
#
添加从表记录成功，在添加这条记录时，要求部门表有
1001
部门
insert into
emp
values
(
2
,
'
李四
'
,
1005
)
;
#
添加从表记录失败
ERROR
1452
(
23000
)
: Cannot
add
（添加）
or update
（修改）
a child
row
: a
foreign key
constraint
fails
(
`atguigudb`
.
`emp`
,
CONSTRAINT
`emp_ibfk_1`
FOREIGN KEY
(
`deptid`
)
REFERENCES
`dept`
(
`did`
))
从表
emp
添加记录失败，因为主表
dept
没有
1005
部门
mysql
>
select
*
from
dept;
+------+--------+
|
did
|
dname
|
+------+--------+
|
1001
|
教学部
|
|
1003
|
财务部
|
+------+--------+
2
rows
in set
(
0.00
sec
)
mysql
>
select
*
from
emp;
+-----+-------+--------+
|
eid
|
ename
|
deptid
|
+-----+-------+--------+
|
1
|
张三
|
1001
|
+-----+-------+--------+
1
row in set
(
0.00
sec
)
总结：约束关系是针对双方的
添加了外键约束后，主表的修改和删除数据受约束
添加了外键约束后，从表的添加和修改数据受约束
在从表上建立外键，要求主表必须存在 删除主表时，要求从表从表先删除，或将从表中外键引用该
主表的关系先删除
6.7
约束等级
Cascade
方式 ：在父表上
update/delete
记录时，同步
update/delete
掉子表的匹配记录
Set null
方式 ：在父表上
update/delete
记录时，将子表上匹配记录的列设为
null
，但是要注意子表
的外键列不能为
not null
No action
方式 ：如果子表中有匹配的记录，则不允许对父表对应候选键进行
update/delete
操作
Restrict
方式 ：同
no action
， 都是立即检查外键约束
Set default
方式 （在可视化工具
SQLyog
中可能显示空白）：父表有变更时，子表将外键列设置成
一个默认的值，但
Innodb
不能识别
如果没有指定等级，就相当于
Restrict
方式。
对于外键约束，最好是采用
: ON UPDATE CASCADE ON DELETE RESTRICT
的方式。
（
1
）演示
1
：
on update cascade on delete set null
update
emp
set
deptid
=
1002
where
eid
=
1
;
#
修改从表失败
ERROR
1452
(
23000
)
: Cannot
add
（添加）
or update
（修改）
a child
row
（子表的记录）
: a
foreign key constraint
fails
（外键约束失败）
(
`atguigudb`
.
`emp`
,
CONSTRAINT
`emp_ibfk_1`
FOREIGN KEY
(
`deptid`
)
REFERENCES
`dept`
(
`did`
))
#
部门表
did
字段现在
没有
1002
的值，所以员工 表中不能修改员工所在部门
deptid
为
1002
update
dept
set
did
=
1002
where
did
=
1001
;
#
修改主表失败
ERROR
1451
(
23000
)
: Cannot
delete
（删除）
or update
（修改）
a parent
row
（父表的记
录）
: a
foreign key constraint
fails
(
`atguigudb`
.
`emp`
,
CONSTRAINT
`emp_ibfk_1`
FOREIGN KEY
(
`deptid`
)
REFERENCES
`dept`
(
`did`
))
#
部门表
did
的
1001
字段已经被
emp
引用了，所以部门表的
1001
字
段就不能修改了。
update
dept
set
did
=
1002
where
did
=
1003
;
#
修改主表成功 因为部门表的
1003
部门没有被
emp
表引用，所以可以修改
delete from
dept
where
did
=
1001
;
#
删除主表失败
ERROR
1451
(
23000
)
: Cannot
delete
（删除）
or update
（修改）
a parent
row
（父表记录）
:
a
foreign key constraint
fails
(
`atguigudb`
.
`emp`
,
CONSTRAINT
`emp_ibfk_1`
FOREIGN KEY
(
`deptid`
)
REFERENCES
`dept`
(
`did`
))
#
因为部门表
did
的
1001
字段已经被
emp
引用了，所以部门表的
1001
字段对应的记录就不能被删除
create table
dept
(
did int
primary key
,
#
部门编号
dname varchar
(
50
)
#
部门名称
)
;
create table
emp
(
eid int
primary key
,
#
员工编号
ename varchar
(
5
)
,
#
员工姓名
deptid int,
#
员工所在的部门
foreign key
(
deptid
)
references
dept
(
did
)
on update cascade on delete set
null
#
把修改操作设置为级联修改等级，把删除操作设置为
set null
等级
)
;
insert into
dept
values
(
1001
,
'
教学部
'
)
;
insert into
dept
values
(
1002
,
'
财务部
'
)
;
insert into
dept
values
(
1003
,
'
咨询部
'
)
;
insert into
emp
values
(
1
,
'
张三
'
,
1001
)
;
#
在添加这条记录时，要求部门表有
1001
部门
insert into
emp
values
(
2
,
'
李四
'
,
1001
)
;
insert into
emp
values
(
3
,
'
王五
'
,
1002
)
;
mysql
>
select
*
from
dept;
mysql
>
select
*
from
emp;
#
修改主表成功，从表也跟着修改，修改了主表被引用的字段
1002
为
1004
，从表的引用字段就跟着修改为
1004
了
mysql
>
update
dept
set
did
=
1004
where
did
=
1002
;
Query
OK,
1
row
affected
(
0.00
sec
)
Rows matched:
1
Changed
:
1
Warnings
:
0
mysql
>
select
*
from
dept;
+------+--------+
|
did
|
dname
|
+------+--------+
|
1001
|
教学部
|
|
1003
|
咨询部
|
|
1004
|
财务部
|
#
原来是
1002
，修改为
1004
+------+--------+
3
rows
in set
(
0.00
sec
)
mysql
>
select
*
from
emp;
+-----+-------+--------+
|
eid
|
ename
|
deptid
|
+-----+-------+--------+
|
1
|
张三
|
1001
|
|
2
|
李四
|
1001
|
|
3
|
王五
|
1004
|
#
原来是
1002
，跟着修改为
1004
+-----+-------+--------+
3
rows
in set
(
0.00
sec
)
#
删除主表的记录成功，从表对应的字段的值被修改为
null
mysql
>
delete from
dept
where
did
=
1001
;
Query
OK,
1
row
affected
(
0.01
sec
)
mysql
>
select
*
from
dept;
+------+--------+
|
did
|
dname
|
#
记录
1001
部门被删除了
+------+--------+
|
1003
|
咨询部
|
|
1004
|
财务部
|
+------+--------+
2
rows
in set
(
0.00
sec
)
mysql
>
select
*
from
emp;
+-----+-------+--------+
|
eid
|
ename
|
deptid
|
+-----+-------+--------+
|
1
|
张三
|
NULL
|
#
原来引用
1001
部门的员工，
deptid
字段变为
null
|
2
|
李四
|
NULL
|
|
3
|
王五
|
1004
|
+-----+-------+--------+
3
rows
in set
(
0.00
sec
)
（
2
）演示
2
：
on update set null on delete cascade
create table
dept
(
did int
primary key
,
#
部门编号
dname varchar
(
50
)
#
部门名称
)
;
create table
emp
(
eid int
primary key
,
#
员工编号
ename varchar
(
5
)
,
#
员工姓名
deptid int,
#
员工所在的部门
foreign key
(
deptid
)
references
dept
(
did
)
on update set
null
on delete
cascade
#
把修改操作设置为
set null
等级，把删除操作设置为级联删除等级
)
;
insert into
dept
values
(
1001
,
'
教学部
'
)
;
insert into
dept
values
(
1002
,
'
财务部
'
)
;
insert into
dept
values
(
1003
,
'
咨询部
'
)
;
insert into
emp
values
(
1
,
'
张三
'
,
1001
)
;
#
在添加这条记录时，要求部门表有
1001
部门
insert into
emp
values
(
2
,
'
李四
'
,
1001
)
;
insert into
emp
values
(
3
,
'
王五
'
,
1002
)
;
mysql
>
select
*
from
dept;
+------+--------+
|
did
|
dname
|
+------+--------+
|
1001
|
教学部
|
|
1002
|
财务部
|
|
1003
|
咨询部
|
+------+--------+
3
rows
in set
(
0.00
sec
)
mysql
>
select
*
from
emp;
+-----+-------+--------+
|
eid
|
ename
|
deptid
|
+-----+-------+--------+
|
1
|
张三
|
1001
|
|
2
|
李四
|
1001
|
|
3
|
王五
|
1002
|
+-----+-------+--------+
3
rows
in set
(
0.00
sec
)
#
修改主表，从表对应的字段设置为
null
mysql
>
update
dept
set
did
=
1004
where
did
=
1002
;
Query
OK,
1
row
affected
(
0.00
sec
)
Rows matched:
1
Changed
:
1
Warnings
:
0
mysql
>
select
*
from
dept;
+------+--------+
|
did
|
dname
|
+------+--------+
|
1001
|
教学部
|
|
1003
|
咨询部
|
|
1004
|
财务部
|
#
原来
did
是
1002
+------+--------+
3
rows
in set
(
0.00
sec
)
mysql
>
select
*
from
emp;
+-----+-------+--------+
|
eid
|
ename
|
deptid
|
+-----+-------+--------+
|
1
|
张三
|
1001
|
|
2
|
李四
|
1001
|
|
3
|
王五
|
NULL
|
#
原来
deptid
是
1002
，因为部门表
1002
被修改了，
1002
没有对应的了，就
设置为
null
+-----+-------+--------+
3
rows
in set
(
0.00
sec
)
#
删除主表的记录成功，主表的
1001
行被删除了，从表相应的记录也被删除了
mysql
>
delete from
dept
where
did
=
1001
;
Query
OK,
1
row
affected
(
0.00
sec
)
mysql
>
select
*
from
dept;
+------+--------+
|
did
|
dname
|
#
部门表中
1001
部门被删除
+------+--------+
|
1003
|
咨询部
|
|
1004
|
财务部
|
+------+--------+
2
rows
in set
(
0.00
sec
)
mysql
>
select
*
from
emp;
+-----+-------+--------+
|
eid
|
ename
|
deptid
|
#
原来
1001
部门的员工也被删除了
+-----+-------+--------+
|
3
|
王五
|
NULL
|
+-----+-------+--------+
1
row in set
(
0.00
sec
)
（
3
）演示：
on update cascade on delete cascade
create table
dept
(
did int
primary key
,
#
部门编号
dname varchar
(
50
)
#
部门名称
)
;
create table
emp
(
eid int
primary key
,
#
员工编号
ename varchar
(
5
)
,
#
员工姓名
deptid int,
#
员工所在的部门
foreign key
(
deptid
)
references
dept
(
did
)
on update cascade on delete
cascade
#
把修改操作设置为级联修改等级，把删除操作也设置为级联删除等级
)
;
insert into
dept
values
(
1002
,
'
财务部
'
)
;
insert into
dept
values
(
1003
,
'
咨询部
'
)
;
insert into
emp
values
(
1
,
'
张三
'
,
1001
)
;
#
在添加这条记录时，要求部门表有
1001
部门
insert into
emp
values
(
2
,
'
李四
'
,
1001
)
;
insert into
emp
values
(
3
,
'
王五
'
,
1002
)
;
mysql
>
select
*
from
dept;
+------+--------+
|
did
|
dname
|
+------+--------+
|
1001
|
教学部
|
|
1002
|
财务部
|
|
1003
|
咨询部
|
+------+--------+
3
rows
in set
(
0.00
sec
)
mysql
>
select
*
from
emp;
+-----+-------+--------+
|
eid
|
ename
|
deptid
|
+-----+-------+--------+
|
1
|
张三
|
1001
|
|
2
|
李四
|
1001
|
|
3
|
王五
|
1002
|
+-----+-------+--------+
3
rows
in set
(
0.00
sec
)
#
修改主表，从表对应的字段自动修改
mysql
>
update
dept
set
did
=
1004
where
did
=
1002
;
Query
OK,
1
row
affected
(
0.00
sec
)
Rows matched:
1
Changed
:
1
Warnings
:
0
mysql
>
select
*
from
dept;
+------+--------+
|
did
|
dname
|
+------+--------+
|
1001
|
教学部
|
|
1003
|
咨询部
|
|
1004
|
财务部
|
#
部门
1002
修改为
1004
+------+--------+
3
rows
in set
(
0.00
sec
)
mysql
>
select
*
from
emp;
6.8
删除外键约束
流程如下：
示例
+-----+-------+--------+
|
eid
|
ename
|
deptid
|
+-----+-------+--------+
|
1
|
张三
|
1001
|
|
2
|
李四
|
1001
|
|
3
|
王五
|
1004
|
#
级联修改
+-----+-------+--------+
3
rows
in set
(
0.00
sec
)
#
删除主表的记录成功，主表的
1001
行被删除了，从表相应的记录也被删除了
mysql
>
delete from
dept
where
did
=
1001
;
Query
OK,
1
row
affected
(
0.00
sec
)
mysql
>
select
*
from
dept;
+------+--------+
|
did
|
dname
|
#1001
部门被删除了
+------+--------+
|
1003
|
咨询部
|
|
1004
|
财务部
|
+------+--------+
2
rows
in set
(
0.00
sec
)
mysql
>
select
*
from
emp;
+-----+-------+--------+
|
eid
|
ename
|
deptid
|
#1001
部门的员工也被删除了
+-----+-------+--------+
|
3
|
王五
|
1004
|
+-----+-------+--------+
1
row in set
(
0.00
sec
)
(
1
)
第一步先查看约束名和删除外键约束
SELECT
*
FROM
information_schema
.table_constraints
WHERE table_name
=
'
表名称
'
;
#
查
看某个表的约束名
ALTER TABLE
从表名
DROP FOREIGN KEY
外键约束名
;
（
2
）第二步查看索引名和删除索引。（注意，只能手动删除）
SHOW INDEX FROM
表名称
;
#
查看某个表的
索引名
ALTER TABLE
从表名
DROP INDEX
索引名
;
6.9
开发场景
问题
1
：如果两个表之间有关系（一对一、一对多），比如：员工表和部门表（一对多），它们之间是否
一定要建外键约束？
答：不是的
问题
2
：建和不建外键约束有什么区别？
答：建外键约束，你的操作（创建表、删除表、添加、修改、删除）会受到限制，从语法层面受到限
制。例如：在员工表中不可能添加一个员工信息，它的部门的值在部门表中找不到。
不建外键约束，你的操作（创建表、删除表、添加、修改、删 除）不受限制，要保证数据的 引用完整性
，只能依 靠程序员的自觉 ，或者是 在
Java
程序中进行限定 。例如：在员工表中，可以添加一个员工的
信息，它的部门指定为一个完全不存在的部门。
问题
3
：那么建和不建外键约束和查询有没有关系？
答：没有
Tip
在
MySQL
里，外键约束是有成本的，需要消耗系统资源。对于大并发的
SQL
操作，有可能会不适
合。比如大型网站的中央数据库，可能会 因为外键约束的系统开销而变得非常慢 。所以，
MySQL
允许你不使用系统自带的外键约束，在 应用层面 完成检查数据一致性的逻辑。也就是说，即使你不
用外键约束，也要想办法通过应用层面的附加逻辑，来实现外键约束的功能，确保数据的一致性。
6.10
阿里开发规范
【 强制 】不得使用外键与级联，一切外键概念必须在应用层解决。
说明：（概念解释）学生表中的
student_id
是主键，那么成绩表中的
student_id
则为外键。如果更新学
生表中的
student_id
，同时触发成绩表中的
student_id
更新，即为级联更新。外键与级联更新适用于 单
机低并发 ，不适合 分布式 、 高并发集群 ；级联更新是强阻塞，存在数据库 更新风暴 的风险；外键影响
数据库的 插入速度 。
mysql
>
SELECT
*
FROM
information_schema
.table_constraints
WHERE table_name
=
'emp'
;
mysql
>
alter table
emp
drop foreign key
emp_ibfk_1;
Query
OK,
0
rows affected
(
0.02
sec
)
Records:
0
Duplicates:
0
Warnings
:
0
mysql
>
show index from
emp;
mysql
>
alter table
emp
drop index
deptid;
Query
OK,
0
rows affected
(
0.01
sec
)
Records:
0
Duplicates:
0
Warnings
:
0
mysql
>
show index from
emp;
7. CHECK
约束
7.1
作用
检查某个字段的值是否符号
xx
要求，一般指的是值的范围
7.2
关键字
CHECK
7.3
说明：
MySQL 5.7
不支持
MySQL5.7
可以使用
check
约束，但
check
约束对数据验证没有任何作用。添加数据时，没有任何错误或
警 告
但是
MySQL 8.0
中可以使用
check
约束了。
8. DEFAULT
约束
8.1
作用
给某个字段
/
某列指定默认值，一旦设置默认值，在插入数据时，如果此字段没有显式赋值，则赋值为默
认值。
create table
employee
(
eid int
primary key
,
ename varchar
(
5
)
,
gender char
check
(
'
男
'
or
'
女
'
)
)
;
insert into
employee
values
(
1
,
'
张三
'
,
'
妖
'
)
;
mysql
>
select
*
from
employee;
+-----+-------+--------+
|
eid
|
ename
|
gender
|
+-----+-------+--------+
|
1
|
张三
|
妖
|
+-----+-------+--------+
1
row in set
(
0.00
sec
)
CREATE TABLE
temp
(
id INT
AUTO_INCREMENT
,
NAME VARCHAR
(
20
)
,
age INT
CHECK
(
age
>
20
)
,
PRIMARY KEY
(
id
)
)
;
age tinyint
check
(
age
>
20
)
或
sex char
(
2
)
check
(
sex
in
(
‘
男
’,’
女
’
))
CHECK
(
height
>=
0
AND
height
<
3
)
8.2
关键字
DEFAULT
8.3
如何给字段加默认值
（
1
）建表时
create table
表名称
(
字段名 数据类型
primary key
,
字段名 数据类型
unique key not
null
,
字段名 数据类型
unique key
,
字段名 数据类型
not
null
default
默认值
,
)
;
create table
表名称
(
字段名 数据类型
default
默认值
,
字段名 数据类型
not
null
default
默认值
,
字段名 数据类型
not
null
default
默认值
,
primary key
(
字段名
)
,
unique key
(
字段名
)
)
;
说明：默认值约束一般不在唯一键和主键列上加
create table
employee
(
eid int
primary key
,
ename varchar
(
20
)
not
null
,
gender char
default
'
男
'
,
tel char
(
11
)
not
null
default
''
#
默认是空字符串
)
;
mysql
>
desc
employee;
+--------+-------------+------+-----+---------+-------+
|
Field
|
Type
|
Null
|
Key
|
Default
|
Extra
|
+--------+-------------+------+-----+---------+-------+
|
eid
|
int
(
11
)
|
NO
|
PRI
|
NULL
| |
|
ename
|
varchar
(
20
)
|
NO
| |
NULL
| |
|
gender
|
char
(
1
)
|
YES
| |
男
| |
|
tel
|
char
(
11
)
|
NO
| | | |
+--------+-------------+------+-----+---------+-------+
4
rows
in set
(
0.00
sec
)
insert into
employee
values
(
1
,
'
汪飞
'
,
'
男
'
,
'13700102535'
)
;
#
成功
mysql
>
select
*
from
employee;
+-----+-------+--------+-------------+
|
eid
|
ename
|
gender
|
tel
|
+-----+-------+--------+-------------+
|
1
|
汪飞
|
男
|
13700102535
|
+-----+-------+--------+-------------+
1
row in set
(
0.00
sec
)
insert into
employee
(
eid,ename
)
values
(
2
,
'
天琪
'
)
;
#
成功
mysql
>
select
*
from
employee;
+-----+-------+--------+-------------+
|
eid
|
ename
|
gender
|
tel
|
+-----+-------+--------+-------------+
|
1
|
汪飞
|
男
|
13700102535
|
|
2
|
天琪
|
男
| |
+-----+-------+--------+-------------+
2
rows
in set
(
0.00
sec
)
insert into
employee
(
eid,ename
)
values
(
3
,
'
二虎
'
)
;
#ERROR 1062 (23000): Duplicate entry '' for key 'tel'
#
如果
tel
有唯一性约束的话会报错，如果
tel
没有唯一性约束，可以添加成功
CREATE TABLE
myemp
(
id INT
AUTO_INCREMENT PRIMARY KEY
,
NAME VARCHAR
(
15
)
,
salary DOUBLE
(
10
,
2
)
DEFAULT
2000
)
;
（
2
）建表后
alter table
表名称
modify
字段名 数据类型
default
默认值
;
#
如果这个字段原来有非空约束，你还保留非空约束，那么在加默认值约束时，还得保留非空约束，否则非空约
束就被删除了
#
同理，在给某个字段加非空约束也一样，如果这个字段原来有默认值约束，你想保留，也要在
modify
语句中
保留默认值约束，否则就删除了
alter table
表名称
modify
字段名 数据类型
default
默认值
not
null
;
create table
employee
(
eid int
primary key
,
ename varchar
(
20
)
,
gender char,
tel char
(
11
)
not
null
)
;
mysql
>
desc
employee;
+--------+-------------+------+-----+---------+-------+
|
Field
|
Type
|
Null
|
Key
|
Default
|
Extra
|
+--------+-------------+------+-----+---------+-------+
|
eid
|
int
(
11
)
|
NO
|
PRI
|
NULL
| |
|
ename
|
varchar
(
20
)
|
YES
| |
NULL
| |
|
gender
|
char
(
1
)
|
YES
| |
NULL
| |
|
tel
|
char
(
11
)
|
NO
| |
NULL
| |
+--------+-------------+------+-----+---------+-------+
4
rows
in set
(
0.00
sec
)
alter table
employee
modify
gender char
default
'
男
'
;
#
给
gender
字段增加默认值约束
alter table
employee
modify
tel char
(
11
)
default
''
;
#
给
tel
字段增加默认值约束
mysql
>
desc
employee;
+--------+-------------+------+-----+---------+-------+
|
Field
|
Type
|
Null
|
Key
|
Default
|
Extra
|
8.4
如何删除默认值约束
9.
面试问题
面试
1
、为什么建表时，加
not null default ''
或
default 0
答：不想让表中出现
null
值。
面试
2
、为什么不想要
null
的值
答
:
（
1
）不好比较。
null
是一种特殊值，比较时只能用专门的
is null
和
is not null
来比较。碰到运算符，
通常返回
null
。
+--------+-------------+------+-----+---------+-------+
|
eid
|
int
(
11
)
|
NO
|
PRI
|
NULL
| |
|
ename
|
varchar
(
20
)
|
YES
| |
NULL
| |
|
gender
|
char
(
1
)
|
YES
| |
男
| |
|
tel
|
char
(
11
)
|
YES
| | | |
+--------+-------------+------+-----+---------+-------+
4
rows
in set
(
0.00
sec
)
alter table
employee
modify
tel char
(
11
)
default
''
not
null
;
#
给
tel
字段增加默认值约
束，并保留非空约束
mysql
>
desc
employee;
+--------+-------------+------+-----+---------+-------+
|
Field
|
Type
|
Null
|
Key
|
Default
|
Extra
|
+--------+-------------+------+-----+---------+-------+
|
eid
|
int
(
11
)
|
NO
|
PRI
|
NULL
| |
|
ename
|
varchar
(
20
)
|
YES
| |
NULL
| |
|
gender
|
char
(
1
)
|
YES
| |
男
| |
|
tel
|
char
(
11
)
|
NO
| | | |
+--------+-------------+------+-----+---------+-------+
4
rows
in set
(
0.00
sec
)
alter table
表名称
modify
字段名 数据类型
;
#
删除默认值约束，也不保留非空约束
alter table
表名称
modify
字段名 数据类型
not
null
;
#
删除默认值约束，保留非空约束
alter table
employee
modify
gender char;
#
删除
gender
字段默认值约束，如果有非空约束，也一
并删除
alter table
employee
modify
tel char
(
11
)
not
null
;
#
删除
tel
字段默认值约束，保留非空约
束
mysql
>
desc
employee;
+--------+-------------+------+-----+---------+-------+
|
Field
|
Type
|
Null
|
Key
|
Default
|
Extra
|
+--------+-------------+------+-----+---------+-------+
|
eid
|
int
(
11
)
|
NO
|
PRI
|
NULL
| |
|
ename
|
varchar
(
20
)
|
YES
| |
NULL
| |
|
gender
|
char
(
1
)
|
YES
| |
NULL
| |
|
tel
|
char
(
11
)
|
NO
| |
NULL
| |
+--------+-------------+------+-----+---------+-------+
4
rows
in set
(
0.00
sec
)
（
2
）效率不高。影响提高索引效果。因此，我们往往在建表时
not null default ''
或
default 0
面试
3
、带
AUTO_INCREMENT
约束的字段值是从
1
开始的吗
？
答：在
MySQL
中，默认
AUTO_INCREMENT
的初始 值是
1
，每新增一条记录，字段值自动加
1
。设置自增
属性（
AUTO_INCREMENT
）的时候，还可以指定第 一条插入记录的自增字段的值，这样新插入的记录
的自增字段值从初始值开始递增，如在表中插入第一 条记录，同时指定
id
值为
5
，则以后插入的记录的
id
值就会从
6
开始往上增加。添加主键约束时，往往需要 设置字段自动增加属性。
面试
4
、并不是每个表都可以任意选择存储引擎
？外键约束（
FOREIGN KEY
）不能跨引擎使用。
MySQL
支持多种存储引擎，每一个表都可以指定一个不同的存储引擎，需要注意的是：外键约束是用来
保证数据的参照完整性的，如果表之间需要关联外键，却指定了不同的存储引擎，那么这些表之间是不
能创建外键约束的。所以说，存储引擎的选择也不完全是随意的。
第
6
章
MySQL
数据库基本操作
-DQL-
基本查询
1. SQL
概述
1.1 SQL
背景知识
1946
年，世界上第一台电脑诞生，如今，借由这台电脑发展起来的互联网已经自成江湖。在这几
十 年里，无数的技术、产业在这片江湖里沉浮，有的方兴未艾，有的已经几幕兴衰。但在这片浩荡
的 波动里，有一门技术从未消失，甚至
“
老当益壮
”
，那就是
SQL
。
45
年前，也就是
1974
年，
IBM
研究员发布了一篇揭开数据库技术的论文《
SEQUEL
：一门结
构 化的英语查询语言》，直到今天这门结构化的查询语言并没有太大的变化，相比于其他语
言，
SQL
的半衰期可以说是非常长 了。
不论是前端工程师，还是后端算法工程师，都一定会和数据打交道，都需要了解如何又快又准确地
提取自己想要的数据。更别提数据分析师了，他们的工作就是和数据打交道，整理不同的报告，以
便指导业务决策。
SQL
（
Structured Query Language
，结构化查询语言）是使用关系模型的数据库应用语言， 与数
据直 接打交道 ，由
IBM
上世纪
70
年代开发出来。后由美国国家标准局（
ANSI
）开始着手制定
SQL
标准， 先后有
SQL-86
，
SQL-89
，
SQL-92
，
SQL-99
等标准。
SQL
有两个重要的标准，分别是
SQL92
和
SQL99
，它们分别代表了
92
年和
99
年颁布的
SQL
标
准，我们今天使用的
SQL
语言依然遵循这些标准。
不同的数据库生产厂商都支持
SQL
语句，但都有特有内容。
不同的数据库生产厂商都支持
SQL
语句，但都有特有内容。
1.2 SQL
语言排行榜
自从
SQL
加入了
TIOBE
编程语言排行榜，就一直保持在
Top 10
。
TIOBE Index - TIOBE
1.3 SQL
分类
SQL
语言在功能上主要分为如下
3
大类：
DDL
（
Data Definition Languages
、数据定义语言），这些语句定义了不同的数据库、表、视图、
索引等数据库对象，还可以用来创建、删除、修改数据库和数据表的结构。
主要的语句关键字包括
CREATE
、
DROP
、
ALTER
等。
DML
（
Data Manipulation Language
、数据操作语言），用于添加、删除、更新和查询数据库记
录，并检查数据完整性。
主要的语句关键字包括
INSERT
、
DELETE
、
UPDATE
、
SELECT
等。
SELECT
是
SQL
语言的基础，最为重要。
DCL
（
Data Control Language
、数据控制语言），用于定义数据库、表、字段、用户的访问权限
和安全级别。
主要的语句关键字包括
GRANT
、
REVOKE
、
COMMIT
、
ROLLBACK
、
SAVEPOINT
等。
Note
因为查询语句使用的非常的频繁，所以很多人把查询语句单拎出来一类：
DQL
（数据查询语言）。
还有单独将
COMMIT
、
ROLLBACK
取出来称为
TCL
（
Transaction Control Language
，事务控制
语 言）。
2. SQL
语言的规则与规范
2.1
基本规则
SQL
可以写在一行或者多行。为了提高可读性，各子句分行写，必要时使用缩进
每条命令以
;
或
\g
或
\G
结束
关键字不能被缩写也不能分行
关于标点符号
必须保证所有的
()
、单引号、双引号是成对结束的
必须使用英文状态下的半角输入方式
字符串型和日期时间类型的数据可以使用单引号（
' '
）表示
列的别名，尽量使用双引号（
" "
），而且不建议省略
as
2.2 SQL
大小写规范 （建议遵守）
MySQL
在
Windows
环境下是大小写不敏感的
MySQL
在
Linux
环境下是大小写敏感的
数据库名、表名、表的别名、变量名是严格区分大小写的
关键字、函数名、列名
(
或字段名
)
、列的别名
(
字段的别名
)
是忽略大小写的。
推荐采用统一的书写规范：
数据库名、表名、表别名、字段名、字段别名等都小写
SQL
关键字、函数名、绑定变量等都大写
2.3
注 释
可以使用如下格式的注释结构
单行注释：
#
注释文字
(MySQL
特有的方式
)
单行注释：
--
注释文字
(--
后面必须包含一个空格。
)
多行注释：
/*
注释文字
*/
2.4
命名规则（暂时了解）
数据库、表名不得超过
30
个字符，变量名限制为
29
个
必须只能包含
A–Z, a–z, 0–9, _
共
63
个字符
数据库名、表名、字段名等对象名中间不要包含空格
同一个
MySQL
软件中，数据库不能同名；同一个库中，表不能重名；同一个表中，字段不能重名
必须保证你的字段没有和保留字、数据库系统或常用方法冲突。如果坚持使用，请在
SQL
语句中使
用
`
（着重号）引起来
保持字段名和类型的一致性，在命名字段并为其指定数据类型的时候一定要保证一致性。假如数据
类型在一个表里是整数，那在另一个表里可就别变成字符型了
举例：
2.5
数据导入指令
在命令行客户端登录
mysql
，使用
source
指令导入
#
以下两句是一样的，不区分大小写
show databases
;
SHOW DATABASES
;
#
创建表格
#create table student info(...); #
表名错误，因为表名有空格
create table
student_info(...);
#
其中
order
使用
``
飘号，因为
order
和系统关键字或系统函数名等预定义标识符重名了
CREATE TABLE
`order`
(
id INT,
lname VARCHAR
(
20
)
)
;
select
id
as
"
编号
"
,
`name`
as
"
姓名
"
from
t_stu;
#
起别名时，
as
都可以省略
select
id
as
编号
,
`name`
as
姓名
from
t_stu;
#
如果字段别名中没有空格，那么可以省略
""
select
id
as
编 号
,
`name`
as
姓 名
from
t_stu;
#
错误，如果字段别名中有空格，那么不能省
略
""
mysql
>
source
d:
\m
ysqldb
.sql
mysql
>
desc
employees;
+----------------+-------------+------+-----+---------+-------+
|
Field
|
Type
|
Null
|
Key
|
Default
|
Extra
|
+----------------+-------------+------+-----+---------+-------+
|
employee_id
|
int
(
6
)
|
NO
|
PRI
|
0
| |
|
first_name
|
varchar
(
20
)
|
YES
| |
NULL
| |
|
last_name
|
varchar
(
25
)
|
NO
| |
NULL
| |
|
email
|
varchar
(
25
)
|
NO
|
UNI
|
NULL
| |
|
phone_number
|
varchar
(
20
)
|
YES
| |
NULL
| |
|
hire_date
|
date
|
NO
| |
NULL
| |
|
job_id
|
varchar
(
10
)
|
NO
|
MUL
|
NULL
| |
|
salary
|
double
(
8
,
2
)
|
YES
| |
NULL
| |
|
commission_pct
|
double
(
2
,
2
)
|
YES
| |
NULL
| |
|
manager_id
|
int
(
6
)
|
YES
|
MUL
|
NULL
| |
3.
基本的
SELECT
语句
3.1
语法格式
简化版语法
3.2
数据准备
|
department_id
|
int
(
4
)
|
YES
|
MUL
|
NULL
| |
+----------------+-------------+------+-----+---------+-------+
11
rows
in set
(
0.00
sec
)
select
[
all
|
distinct
]
<
目标列的表达式
1
>
[
别名
]
,
<
目标列的表达式
2
>
[
别名
]
...
from
<
表名或视图名
>
[
别名
]
,
<
表名或视图名
>
[
别名
]
...
[
where
<
条件表达式
>
]
[
group by
<
列名
>
[
having
<
条件表达式
>
]]
[
order by
<
列名
>
[
asc
|
desc
]]
[
limit
<
数字或者列表
>
]
;
select
*|
列名
from
表
where
条件
--
创建数据库
create database if not
exist mydb2;
use
mydb2;
--
创建商品表：
create table
product
(
pid int
primary key auto_increment
,
--
商品编号
pname varchar
(
20
)
not
null
,
--
商品名字
price double,
--
商品价格
category_id varchar
(
20
)
--
商品所属分类
)
;
--
添加数据
insert into
product
values
(
null
,
'
海尔洗衣机
'
,
5000
,
'c001'
)
;
insert into
product
values
(
null
,
'
美的冰箱
'
,
3000
,
'c001'
)
;
insert into
product
values
(
null
,
'
格力空调
'
,
5000
,
'c001'
)
;
insert into
product
values
(
null
,
'
九阳电饭煲
’,200,'
c001
');
insert into product values(null,'
啄木鸟衬衣
',300,'
c002
');
insert into product values(null,'
恒源祥西裤
',800,'
c002
');
insert into product values(null,'
花花公子夹克
',440,'
c002
');
insert into product values(null,'
劲霸休闲裤
',266,'
c002
');
insert into product values(null,'
海澜之家卫衣
',180,'
c002
');
insert into product values(null,'
杰克琼斯运动裤
',430,'
c002
');
insert into product values(null,'
兰蔻面霜
',300,'
c003
');
insert into product values(null,'
雅诗兰黛精华水
',200,'
c003
');
insert into product values(null,'
香奈儿香水
',350,'
c003
');
insert into product values(null,'
SK
-
II
神仙水
',350,'
c003
');
3.3
简单查询
示例
:
Note
一般情况下，除非需要使用表中所有的字段数据，最好不要使用通配符
‘*’
。使用通配符虽然可以节
省输入查询语句的时间，但是获取不需要的列数据通常会降低查询和所使用的应用程序的效率。通
配符的优势是，当不知道所需要的列的名称时，可以通过它获取它们。
在生产环境下，不推荐你直接使用
SELECT *
进行查询。
Note
MySQL
中的
SQL
语句是不区分大小写的，因此
SELECT
和
select
的作用是相同的，但是，许多开发人
员习惯将关键字大写、数据列和表名小写，读者也应该养成一个良好的编程习惯，这样写出来的代
码更容易阅读和维护。
3.4
运算符
简介
数据库中的表结构确立后，表中的数据代表的意义就已经确定。通过
MySQL
运算符进行运算，就可以获
取到表结构以外的另一种数据。例如，学生表中存在一个
birth
字段，这个字段表示学生的出生年份。而
运用
MySQL
的算术运算符用当前的年份减学生出生的年份，那么得到的就是这个学生的实际年龄数据。
MySQL
支持
4
种运算符
算术运算符：算术运算符主要用于数学运算，其可以连接运算符前后的两个数值或表达式，对数值
或表达式进行加（
+
）、减（
-
）、乘（
*
）、除（
/
）和取模（
%
）运算。
insert into product values(null,'
资生堂粉底液
',180,'
c003
');
insert into product values(null,'
老北京方便面
',56,'
c004
');
insert into product values(null,'
良品铺子海带丝
',17,'
c004
');
insert into product values(null,'
三只松鼠坚果
',88,null);
-- 1.
查询所有的商品
.
select
*
from
product;
-- 2.
查询商品名和商品价格
.
select
pname,price
from
product;
-- 3.
别名查询
.
使用的关键字是
as
（
as
可以省略的）
.
-- 3.1
表别名
:
select
*
from
product
as
p;
-- 3.2
列别名：
select
pname
as
pn
from
product;
-- 4.
去掉重复值
.
select distinct
price
from
product;
-- 5.
查询结果是表达式（运算查询）：将所有商品的价格
+10
元进行显示
.
select
pname,price
+
10
from
product;
示例：
select
6
+
2
;
select
6
-
2
;
select
6
*
2
;
select
6
/
2
;
select
6
%
2
;
--
将每件商品的价格加
10
select
name,price
+
10
as
new_price
from
product;
--
将所有商品的价格上调
10%
select
pname,price
*
1.1
as
new_price
from
product;
比较运算符：比较运算符用来对表达式左边的操作数和右边的操作数进行比较，比较的结果为真则
返回
1
，比较的结果为假则返回
0
，其他情况则返回
NULL
。
比较运算符经常被用来作为
SELECT
查询语句的条件来使用，返回符合条件的结果记录。
示例：
--
查询商品名称为
“
海尔洗衣机
”
的商品所有信息：
select
*
from
product
where
pname
=
'
海尔洗衣机
'
;
--
查询价格为
800
商品
select
*
from
product
where
price
=
800
;
--
查询价格不是
800
的所有商品
select
*
from
product
where
price
!=
800
;
select
*
from
product
where
price
<>
800
;
select
*
from
product
where not
(
price
=
800
)
;
--
查询商品价格大于
60
元的所有商品信息
select
*
from
product
where
price
>
60
;
--
查询商品价格在
200
到
1000
之间所有商品
select
*
from
product
where
price
>=
200
and
price
<=
1000
;
select
*
from
product
where
price
between
200
and
1000
;
此外，还有非符号类型的运算符：
LIKE
运算符通常使用如下通配符：
“
%
”
：匹配
0
个或多个字符。
“_”
：只能匹配一个字符。
REGEXP
运算符
REGEXP
运算符用来匹配字符串，语法格式为：
expr REGEXP
匹配条件 。如果
expr
满足匹配条件，返回
1
；如果不满足，则返回
0
。若
expr
或匹配条件任意一个为
NULL
，则结果为
NULL
。
REGEXP
运算符在进行匹配时，常用的有下面几种通配符：
（
1
）
‘^’
匹配以该字符后面的字符开头的字符串。
（
2
）
‘$’
匹配以该字符前面的字符结尾的字符串。
（
3)‘.’
匹配任何一个单字符。
（
4
）
“[...]”
匹配在方括号内的任何字符。例如，
“[abc]”
匹配
“a”
或
“b”
或
“c”
。为了命名字符的范围，使
用一个
‘-’
。
“[a-z]”
匹配任何字母，而
“[0-9]”
匹配任何数字。
（
5
）
‘*’
匹配零个或多个在它前面的字符。例如，
“x*”
匹配任何数量的
‘x’
字符，
“[0-9]*”
匹配任何数量的
数字， 而
“*”
匹配任何数量的任何字符。
示例
--
查询商品价格是
200
或
800
的所有商品
select
*
from
product
where
price
=
200
or
price
=
800
;
select
*
from
product
where
price
in
(
200
,
800
)
;
--
查询含有
‘
裤
'
字的所有商品
select
*
from
product
where
pname
like
‘
%
裤
%
';
--
查询以
'
海
'
开头的所有商品
select * from product where pname like '
海
%
';
--
查询第二个字为
'
蔻
'
的所有商品
select * from product where pname like '
_
蔻
%
';
--
查询
category_id
为
null
的商品
select * from product where category_id is null;
--
查询
category_id
不为
null
分类的商品
select * from product where category_id is not null;
--
使用
least
求最小值
select least(10, 20, 30); -- 10
select least(10, null , 30); -- null
--
使用
greatest
求最大值
select greatest(10, 20, 30);
select greatest(10, null, 30); -- null
逻辑运算符：逻辑运算符主要用来判断表达式的真假，在
MySQL
中，逻辑运算符的返回结果为
1
、
0
或者
NULL
MySQL
中支持
4
种逻辑运算符如下：
位运算符：位运算符是在二进制数上进行计算的运算符。位运算符会先将操作数变成二进制数，然
后进行位运算， 最后将计算结果从二进制变回十进制数
MySQL
支持的位运算符如下
运算符优先级：数字编号越大，优先级越高，优先级高的运算符先进行计算。可以看到，赋值运算符的
优先级最低，使 用
“()”
括起来的表达式的优先级最高。
3.5
排序查询
简介
如果我们需要对读取的数据进行排序，我们就可以使用
MySQL
的
order by
子句来设定你想按哪个字段
哪种方式来进行排序，再返回搜索结果。
聚合函数
作用
count()
统计指定列不为
NULL
的记录行数；
sum()
计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为
0
max()
计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
min()
计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
avg()
计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为
0
特点
1.asc
代表升序，
desc
代表降序，如果不写默认升序
2.order by
用于子句中可以支持单个字段，多个字段，表达式，函数，别名
3.order by
子句，放在查询语句的最后面。
LIMIT
子句除外
示例：
3.6
聚合查询
之前我们做的查询都是横向查询，它们都是根据条件一行一行的进行判断，而使用聚合函数查询是纵向
查询，它是对一列的值进行计算，然后返回一个单一的值；另外聚合函数会忽略空值。
示例：
聚合查询
-NULL
值的处理
select
字段名
1
，字段名
2
，
……
from
表名
order by
字段名
1
[
asc
|
desc
]
，字段名
2
[
asc
|
desc
]
……
-- 1.
使用价格排序
(
降序
)
select
*
from
product
order by
price
desc
;
-- 2.
在价格排序
(
降序
)
的基础上，以分类排序
(
降序
)
select
*
from
product
order by
price
desc
,category_id
asc
;
-- 3.
显示商品的价格
(
去重复
)
，并排序
(
降序
)
select distinct
price
from
product
order by
price
desc
;
-- 1
查询商品的总条数
select count
(
*
)
from
product;
-- 2
查询价格大于
200
商品的总条数
select count
(
*
)
from
product
where
price
>
200
;
-- 3
查询分类为
'c001'
的所有商品的总和
select sum
(
price
)
from
product
where
category_id
=
'c001'
;
-- 4
查询商品的最大价格
select max
(
price
)
from
product;
-- 5
查询商品的最小价格
select min
(
price
)
from
product;
-- 6
查询分类为
'c002'
所有商品的平均价格
select avg
(
price
)
from
product
where
category_id
=
'c002'
;
示例：
3.7
分组查询
分组查询是指使用
group by
字句对查询信息进行分组。
格式
操作
Important
如果要进行分组的话，则
SELECT
子句之后，只能出现分组的字段和统计函数，其他的字段不能出
现：
分组之后的条件筛选
-having
分组之后对统计结果进行筛选的话必须使用
having
，不能使用
where
where
子句用来筛选
FROM
子句中指定的操作所产生的行
group by
子句用来分组
WHERE
子句的输出。
having
子句用来从分组的结果中筛选行
1
、
count
函数对
null
值的处理
如果
count
函数的参数为星号（
*
），则统计所有记录的个数。而如果参数为某字段，不统计含
null
值的记录个
数。
2
、
sum
和
avg
函数对
null
值的处理
这两个函数忽略
null
值的存在，就好象该条记录不存在一样。
3
、
max
和
min
函数对
null
值的处理
max
和
min
两个函数同样忽略
null
值的存在。
--
创建表
create table
test_null
(
c1 varchar
(
20
)
,
c2 int
)
;
--
插入数据
insert into
test_null
values
(
'aaa'
,
3
)
;
insert into
test_null
values
(
'bbb'
,
3
)
;
insert into
test_null
values
(
'ccc'
,
null
)
;
insert into
test_null
values
(
'ddd'
,
6
)
;
--
测试
select count
(
*
)
,
count
(
1
)
,
count
(
c2
)
from
test_null;
select sum
(
c2
)
,
max
(
c2
)
,
min
(
c2
)
,
avg
(
c2
)
from
test_null;
select
字段
1,
字段
2…
from
表名
group by
分组字段
having
分组条件
;
-- 1
统计各个分类商品的个数
select
category_id ,
count
(
*
)
from
product
group by
category_id ;
格式
操作
3.8
分页查询
分页查询在项目开发中常见，由于数据量很大，显示屏长度有限，因此对数据需要采取分页显示方式。
例如数据共有
30
条，每页显示
5
条，第一页显示
1-5
条，第二页显示
6-10
条。
格式
操作
3.9
其他
INSERT INTO SELECT
语句：将一张表的数据导入到另一张表中，可以使用
INSERT INTO SELECT
语句 。
格式
SELECT INTO FROM
语句
:
将一张表的数据导入到另一张表中，有两种选择
SELECT INTO
和
INSERT
INTO SELECT
。
格式
select
字段
1,
字段
2…
from
表名
group by
分组字段
having
分组条件
;
-- 2.
统计各个分类商品的个数
,
且只显示个数大于
4
的信息
select
category_id ,
count
(
*
)
from
product
group by
category_id
having count
(
*
)
>
1
;
--
方式
1-
显示前
n
条
select
字段
1
，字段
2...
from
表明
limit
n
--
方式
2-
分页显示
select
字段
1
，字段
2...
from
表明
limit
m,n
m:
整数，表示从第几条索引开始，计算方式 （当前页
-
1
）
*
每页显示条数
n:
整数，表示查询多少条数据
--
查询
product
表的前
5
条记录
select
*
from
product
limit
5
--
从第
4
条开始显示，显示
5
条
select
*
from
product
limit
3
,
5
insert into
Table2
(
field1,field2,…
)
select
value1,value2,…
from
Table1
或者：
insert into
Table2
select
*
from
Table1
要求目标表
Table2
必须存在
SELECT
vale1, value2
into
Table2
from
Table1
要求目标表
Table2
不存在，因为在插入时会自动创建表
Table2
，并将
Table1
中指定字段数据复制到
Table2
中。
3.10
正则表达式
示例
-- ^
在字符串开始处进行匹配
SELECT
'abc'
REGEXP
'^a'
;
-- $
在字符串末尾开始匹配
SELECT
'abc'
REGEXP
'a$'
;
SELECT
'abc'
REGEXP
'c$’;
-- .
匹配任意字符
SELECT '
abc
' REGEXP '
.b
';
SELECT '
abc
' REGEXP '
.c
';
SELECT '
abc
' REGEXP '
a.
';
-- [...]
匹配括号内的任意单个字符
SELECT '
abc
' REGEXP '
[
xyz
]
';
SELECT '
abc
' REGEXP '
[
xaz
]
';
-- [^...]
注意
^
符合只有在
[]
内才是取反的意思，在别的地方都是表示开始处匹配
SELECT '
a
' REGEXP '
[
^
abc
]
';
SELECT '
x
' REGEXP '
[
^
abc
]
';
SELECT '
abc
' REGEXP '
[
^
a
]
';
-- a*
匹配
0
个或多个
a,
包括空字符串。 可以作为占位符使用
.
有没有指定字符都可以匹配到数据
SELECT '
stab
' REGEXP '
.ta
*
b
';
SELECT '
stb
' REGEXP '
.ta
*
b
';
SELECT '' REGEXP '
a
*
';
-- a+
匹配
1
个或者多个
a,
但是不包括空字符
SELECT '
stab
' REGEXP '
.ta
+
b
';
SELECT '
stb
' REGEXP '
.ta
+
b
';
-- a?
匹配
0
个或者
1
个
a
SELECT '
stb
' REGEXP '
.ta
?b
';
SELECT '
stab
' REGEXP '
.ta
?b
';
SELECT '
staab
' REGEXP '
.ta
?b
';
-- a1|a2
匹配
a1
或者
a2
，
SELECT '
a
' REGEXP '
a
|
b
';
SELECT '
b
' REGEXP '
a
|
b
';
SELECT '
b
' REGEXP '
^
(
a
|
b
)
';
SELECT '
a
' REGEXP '
^
(
a
|
b
)
';
SELECT '
c
' REGEXP '
^
(
a
|
b
)
';
-- a{m}
匹配
m
个
a
SELECT '
auuuuc
' REGEXP '
au
{
4
}
c
';
SELECT '
auuuuc
' REGEXP '
au
{
3
}
c
';
-- a{m,n}
匹配
m
到
n
个
a,
包含
m
和
n
SELECT '
auuuuc
' REGEXP '
au
{
3
,
5
}
c
';
SELECT '
auuuuc
' REGEXP '
au
{
4
,
5
}
c
';
第
7
章
MySQL
多表操作
1.
多表关系
实际开发中，一个项目通常需要很多张表才能完成。
例如：一个商城项目就需要分类表
(category)
、商品表
(products)
、订单表
(orders)
等多张表。且这些表
的数据之间存在一定的关系，接下来我们将在单表的基础上，一起学习多表方面的知识。
MySQL
多表之间的关系可以概括为：一对一、一对多
/
多对一关系，多对多
1.1
一对一关系
一个学生只有一张身份证；一张身份证只能对应一学生。
在任一表中添加唯一外键，指向另一方主键，确保一对一关系。
一般一对一关系很少见，遇到一对一关系的表最好是合并表。
SELECT '
auuuuc
' REGEXP '
au
{
5
,
10
}
c
';
-- (abc) abc
作为一个序列匹配，不用括号括起来都是用单个字符去匹配，如果要把多个字符作为一个整体
去匹配就需要用到括号，所以括号适合上面的所有情况。
SELECT '
xababy
' REGEXP '
x
(
abab
)
y
';
SELECT '
xababy
' REGEXP '
x
(
ab
)
*
y
';
SELECT '
xababy
' REGEXP '
x
(
ab
){
1
,
2
}
y
';
1.2
一对多
/
多对一关系
部门和员工分析：一个部门有多个员工，一个员工只能对应一个部门
实现原则：在多的一方建立外键，指向一的一方的主键
1.3
多对多关系
学生和课程分析：一个学生可以选择很多门课程，一个课程也可以被很多学生选择
原则：多对多关系实现需要借助第三张中间表。中间表至少包含两个字段，将多对多的关系，拆成一对
多的关系，中间表至少要有两个外键，这两个外键分别指向原来的那两张表的主键
2.
多表联合查询
多表查询就是同时查询两个或两个以上的表，因为有的时候用户在查看数据的时候
,
需要显示的数据来自
多张表
.
多表查询有以下分类
交叉连接查询
[
产生笛卡尔积，了解
]
语法：
select * from A,B;
内连接查询
(
使用的关键字
inner join -- inner
可以省略
)
隐式内连接（
SQL92
标准）：
select * from A,B where
条件
;
显示内连接（
SQL99
标准）：
select * from A inner join B on
条件
;
外连接查询
(
使用的关键字
outer join -- outer
可以省略
)
左外连接：
left outer join select * from A left outer join B on
条件
;
右外连接：
right outer join select * from A right outer join B on
条件
;
满外连接
: full outer join select * from A full outer join B on
条件
;
子查询
select
的嵌套表自关联：
将一张表当成多张表来用
2.1
数据准备
2.2
交叉连接查询
交叉连接查询返回被连接的两个表所有数据行的笛卡尔积
笛卡尔积可以理解为一张表的每一行去和另外一张表的任意一行进行匹配
假如
A
表有
m
行数据，
B
表有
n
行数据，则返回
m*n
行数据
笛卡尔积会产生很多冗余的数据，后期的其他查询可以在该集合的基础上进行条件筛选
格式
示例
--
准备查询数据
use
mydb3;
--
创建部门表
create table if not exists
dept3
(
deptno varchar
(
20
)
primary key
,
--
部门号
name varchar
(
20
)
--
部门名字
)
;
--
创建员工表
create table if not exists
emp3
(
eid varchar
(
20
)
primary key
,
--
员工编号
ename varchar
(
20
)
,
--
员工名字
age int,
--
员工年龄
dept_id varchar
(
20
)
--
员工所属部门
)
;
--
给
dept3
表添加数据
insert into
dept3
values
(
'1001'
,
'
研发部
'
)
;
insert into
dept3
values
(
'1002'
,
'
销售部
'
)
;
insert into
dept3
values
(
'1003'
,
'
财务部
'
)
;
insert into
dept3
values
(
'1004'
,
'
人事部
'
)
;
--
给
emp
表添加数据
insert into
emp3
values
(
'1'
,
'
乔峰
'
,
20
,
'1001'
)
;
insert into
emp3
values
(
'2'
,
'
段誉
'
,
21
,
'1001'
)
;
insert into
emp3
values
(
'3'
,
'
虚竹
'
,
23
,
'1001'
)
;
insert into
emp3
values
(
'4'
,
'
阿紫
'
,
18
,
'1001'
)
;
insert into
emp3
values
(
'5'
,
'
扫地僧
'
,
85
,
'1002'
)
;
insert into
emp3
values
(
'6'
,
'
李秋水
'
,
33
,
'1002'
)
;
insert into
emp3
values
(
'7'
,
'
鸠摩智
'
,
50
,
'1002'
)
;
insert into
emp3
values
(
'8'
,
'
天山童姥
'
,
60
,
'1003'
)
;
insert into
emp3
values
(
'9'
,
'
慕容博
'
,
58
,
'1003'
)
;
insert into
emp3
values
(
'10'
,
'
丁春秋
'
,
71
,
'1005'
)
;
select
*
from
表
1,
表
2,
表
3….;
select
*
from
dept3,emp3;
2.3
内连接查询
格式
示例
2.4
外连接查询
外连接分为左外连接（
left outer join
）、右外连接
(right outer join)
，满外连接
(full outer join)
。
外连接（
Outer Join
）是一种
SQL JOIN
操作，它允许从一个表中选择所有的记录，而无论是否在另一个
表中有匹配的记录。如果记录在另一个表中没有匹配，那么结果集中的值将为
NULL
。
Note
注意：
oracle
里面有
full join,
可是在
mysql
对
full join
支持的不好。我们可以使用
union
来达到目的。
格式
隐式内连接（
SQL92
标准）：
select
*
from
A,B
where
条件
;
显示内连接（
SQL99
标准）：
select
*
from
A
inner join
B
on
条件
;
--
查询每个部门的所属员工
select
*
from
dept3,emp3
where
dept3
.deptno
=
emp3
.dept_id
;
select
*
from
dept3
inner join
emp3
on
dept3
.deptno
=
emp3
.dept_id
;
--
查询研发部和销售部的所属员工
select
*
from
dept3,emp3
where
dept3
.deptno
=
emp3
.dept_id
and
name
in
(
'
研发
部
'
,
'
销售部
'
)
;
select
*
from
dept3
join
emp3
on
dept3
.deptno
=
emp3
.dept_id
and
name
in
(
'
研发
部
'
,
'
销售部
'
)
;
--
查询每个部门的员工数
,
并升序排序
select
deptno,
count
(
1
)
as
total_cnt
from
dept3,emp3
where
dept3
.deptno
=
emp3
.dept_id
group by
deptno
order by
total_cnt;
select
deptno,
count
(
1
)
as
total_cnt
from
dept3
join
emp3
on
dept3
.deptno
=
emp3
.dept_id
group by
deptno
order by
total_cnt;
--
查询人数大于等于
3
的部门，并按照人数降序排序
select
deptno,
count
(
1
)
as
total_cnt
from
dept3,emp3
where
dept3
.deptno
=
emp3
.dept_id
group by
deptno
having
total_cnt
>=
3
order by
total_cnt
desc
;
select
deptno,
count
(
1
)
as
total_cnt
from
dept3
join
emp3
on
dept3
.deptno
=
emp3
.dept_id
group by
deptno
having
total_cnt
>=
3
order by
total_cnt
desc
;
示例
2.5
子查询
子查询就是指的在一个完整的查询语句之中，嵌套若干个不同功能的小查询，从而一起完成复杂查询的
一种编写形式，通俗一点就是包含
select
嵌套的查询。
特点
子查询可以返回的数据类型一共分为四种：
单行单列：返回的是一个具体列的内容，可以理解为一个单值数据；
单行多列：返回一行数据中多个列的内容；
多行单列：返回多行记录之中同一列的内容，相当于给出了一个操作范围；
多行多列：查询返回的结果是一张临时表
示例
左外连接：
left
outer join
select
*
from
A
left outer join
B
on
条件
;
右外连接：
right
outer join
select
*
from
A
right outer join
B
on
条件
;
满外连接
:
full outer join
select
*
from
A
full outer join
B
on
条件
;
--
外连接查询
--
查询哪些部门有员工，哪些部门没有员工
use
mydb3;
select
*
from
dept3
left outer join
emp3
on
dept3
.deptno
=
emp3
.dept_id
;
--
查询哪些员工有对应的部门，哪些没有
select
*
from
dept3
right outer join
emp3
on
dept3
.deptno
=
emp3
.dept_id
;
--
使用
union
关键字实现左外连接和右外连接的并集
select
*
from
dept3
left outer join
emp3
on
dept3
.deptno
=
emp3
.dept_id
union
select
*
from
dept3
right outer join
emp3
on
dept3
.deptno
=
emp3
.dept_id
;
--
查询年龄最大的员工信息，显示信息包含员工号、员工名字，员工年龄
select
eid,ename,age
from
emp3
where
age
=
(
select max
(
age
)
from
emp3
)
;
--
查询年研发部和销售部的员工信息，包含员工号、员工名字
select
eid,ename,t
.name
from
emp3
where
dept_id
in
(
select
deptno,name
from
dept3
where
name
=
'
研发部
'
or
name
=
'
销售部
'
)
;
--
查询研发部
20
岁以下的员工信息
,
包括员工号、员工名字，部门名字
select
eid,age,ename,name
from
(
select
*
from
dept
where
name
=
'
研发部
'
)
t1,
(
select
*
from
emp3
where
age
<
20
)
t2
子查询关键字
在子查询中，有一些常用的逻辑关键字，这些关键字可以给我们提供更丰富的查询功能，主要关键字如
下
:
1.ALL
关键字
格式
select
…from …where c
>
all
(
查询语句
)
--
等价于：
select
...
from
...
where
c
>
result1
and
c
>
result2
and
c
>
result3
特点
ALL:
与子查询返回的所有值比较为
true
则返回
true
ALL
可以与
=
、
>
、
>=
、
<
、
<=
、
<>
结合是来使用，分别表示等于、大于、大于等于、小于、小于等
于、不等于其中的所有数据。
ALL
表示指定列中的值必须要大于子查询集的每一个值，即必须要大于子查询集的最大值；
如果是小于号即小于子查询集的最小值。同理可以推出其它的比较运算符的情况。
示例
--
查询年龄大于
‘1003’
部门所有年龄的员工信息
select
*
from
emp3
where
age
>
all
(
select
age
from
emp3
where
dept_id
=
'1003’);
--
查询不属于任何一个部门的员工信息
select * from emp3 where dept_id != all(select deptno from dept3);
2.ANY
关键字
3.SOME
关键字
格式
select
…from …where c
>
any
(
查询语句
)
--
等价于：
select
...
from
...
where
c
>
result1
or
c
>
result2
or
c
>
result3
特点
ANY:
与子查询返回的任何值比较为
true
则返回
true
ANY
可以与
=
、
>
、
>=
、
<
、
<=
、
<>
结合是来使用，分别表示等于、大于、大于等于、小于、小于等
于、不等于其中的其中的任何一个数据。
表示指定列中的值要大于子查询中的任意一个值，即必须要大于子查询集中的最小值。同理可以推
出其它的比较运算符的情况。
SOME
和
ANY
的作用一样，
SOME
可以理解为
ANY
的别名
示例
--
查询年龄大于
‘1003’
部门任意一个员工年龄的员工信息
select
*
from
emp3
where
age
>
all
(
select
age
from
emp3
where
dept_id
=
'1003’);
4.IN
关键字
格式
select
…from …where c
in
(
查询语句
)
--
等价于：
select
...
from
...
where
c
=
result1
or
c
=
result2
or
c
=
result3
特点
IN
关键字，用于判断某个记录的值，是否在指定的集合中
在
IN
关键字前边加上
not
可以将条件反过来
示例
--
查询研发部和销售部的员工信息，包含员工号、员工名字
select
eid,ename,t
.name
from
emp3
where
dept_id
in
(
select
deptno
from
dept3
where
name
=
'
研发部
'
or
name
=
'
销售部
'
)
;
5.EXISTS
关键字
格式
select
…from …where
exists
(
查询语句
)
特点
该子查询如果
“
有数据结果
”(
至少返回一行数据
)
， 则该
EXISTS()
的结果为
“true”
，外层查询执行
该子查询如果
“
没有数据结果
”
（没有任何数据返回），则该
EXISTS()
的结果为
“false”
，外层查询不执
行
EXISTS
后面的子查询不返回任何实际数据，只返回真或假，当返回真时
where
条件成立
注意，
EXISTS
关键字，比
IN
关键字的运算效率高，因此，在实际开发中，特别是大数据量时，推荐
使用
EXISTS
关键字
示例
2.6
自连接查询
MySQL
有时在信息查询时需要进行对表自身进行关联查询，即一张表自己和自己关联，一张表当成多张
表来用。注意自关联时表必须给表起别名。
格式
示例
第
8
章
MySQL
函数
在
MySQL
中，为了提高代码重用性和隐藏实现细节，
MySQL
提供了很多函数。函数可以理解为别人封装
好的模板代码。
在
MySQL
中，函数非常多，主要可以分为以下几类
:
聚合函数
数学函数
--
查询公司是否有大于
60
岁的员工，有则输出
select
*
from
emp3 a
where exists
(
select
*
from
emp3 b
where
a
.age
>
60
)
;
--
查询有所属部门的员工信息
select
*
from
emp3 a
where exists
(
select
*
from
dept3 b
where
a
.dept_id
=
b
.deptno
)
;
select
字段列表
from
表
1 a ,
表
1 b
where
条件
;
或者
select
字段列表
from
表
1 a
[
left
]
join
表
1 b
on
条件
;
--
创建表
,
并建立自关联约束
create table
t_sanguo
(
eid int
primary key
,
ename varchar
(
20
)
,
manager_id int,
foreign key
(
manager_id
)
references
t_sanguo
(
eid
)
--
添加自关联约束
)
;
--
添加数据
insert into
t_sanguo
values
(
1
,
'
刘协
'
,
NULL
)
;
insert into
t_sanguo
values
(
2
,
'
刘备
'
,
1
)
;
insert into
t_sanguo
values
(
3
,
'
关羽
'
,
2
)
;
insert into
t_sanguo
values
(
4
,
'
张飞
'
,
2
)
;
insert into
t_sanguo
values
(
5
,
'
曹操
'
,
1
)
;
insert into
t_sanguo
values
(
6
,
'
许褚
'
,
5
)
;
insert into
t_sanguo
values
(
7
,
'
典韦
'
,
5
)
;
insert into
t_sanguo
values
(
8
,
'
孙权
'
,
1
)
;
insert into
t_sanguo
values
(
9
,
'
周瑜
'
,
8
)
;
insert into
t_sanguo
values
(
10
,
'
鲁肃
'
,
8
)
;
--
进行关联查询
-- 1.
查询每个三国人物及他的上级信息，如
:
关羽 刘备
select
*
from
t_sanguo a, t_sanguo b
where
a
.manager_id
=
b
.eid
;
字符串函数
日期函数
控制流函数
窗口函数
1.
聚合函数
在
MySQL
中，聚合函数主要由：
count,sum,min,max,avg,
这些聚合函数我们之前都学过，不再重复。
这里我们学习另外一个函数
:group_concat(
），该函数用户实现行的合并
group_concat()
函数首先根据
group by
指定的列进行分组，并且用分隔符分隔，将同一个分组中的值连
接起来，返回一个字符串结果。
格式
Note
（
1
）使用
distinct
可以排除重复值；
（
2
）如果需要对结果中的值进行排序，可以使用
order by
子句；
（
3
）
separator
是一个字符串值，默认为逗号。
示例
group_concat
([
distinct
]
字段名
[
order by
排序字段
asc
/desc
] [
separator
'
分隔符
'
])
create database
mydb4;
use
mydb4;
create table
emp
(
emp_id int
primary key auto_increment comment
'
编号
'
,
emp_name char
(
20
)
not
null
default
''
comment
'
姓名
'
,
salary decimal
(
10
,
2
)
not
null
default
0
comment
'
工资
'
,
department char
(
20
)
not
null
default
''
comment
'
部门
'
)
;
insert into
emp
(
emp_name,salary,department
)
values
(
'
张晶晶
'
,
5000
,
'
财务部
'
)
,
(
'
王飞飞
'
,
5800
,
'
财务部
'
)
,
(
'
赵刚
'
,
6200
,
'
财务部
'
)
,
(
'
刘小
贝
'
,
5700
,
'
人事部
'
)
,
(
'
王大鹏
'
,
6700
,
'
人事部
'
)
,
(
'
张小斐
'
,
5200
,
'
人事部
'
)
,
(
'
刘云云
'
,
7500
,
'
销售部
'
)
,
(
'
刘云
鹏
'
,
7200
,
'
销售部
'
)
,
(
'
刘云鹏
'
,
7800
,
'
销售部
'
)
;
--
将所有员工的名字合并成一行
select group_concat
(
emp_name
)
from
emp;
--
指定分隔符合并
select
department,
group_concat
(
emp_name
separator
';'
)
from
emp
group by
department;
--
指定排序方式和分隔符
select
department,
group_concat
(
emp_name
order by
salary
desc separator
';'
)
from
emp
group by
department;
函数名
描述
实例
ABS(x)
返回
x
的绝对值
返回
-1
的绝对值：
SELECT ABS(-1) --
返回
1
CEIL(x)
返回大于或等于
x
的最
小整数
SELECT CEIL(1.5) --
返回
2
FLOOR(x)
返回小于或等于
x
的最
大整数
小于或等于
1.5
的整数：
SELECT
FLOOR(1.5) --
返回
1
GREATEST(expr1,
expr2, expr3, ...)
返回列表中的最大值
返回以下数字列表中的最大值：
SELECT
GREATEST(3, 12, 34, 8, 25); -- 34
返回以下字
符串列表中的最大值：
SELECT
GREATEST("Google", "Runoob", "Apple"); --
Runoob
LEAST(expr1,
expr2, expr3, ...)
返回列表中的最小值
返回以下数字列表中的最小值：
SELECT
LEAST(3, 12, 34, 8, 25); -- 3
返回以下字符串
列表中的最小值：
SELECT LEAST("Google",
"Runoob", "Apple"); -- Apple
MAX(expression)
返回字段
expression
中的最大值
返回数据表
Products
中字段
Price
的最大
值：
SELECT MAX(Price) AS LargestPrice
FROM Products;
MIN(expression)
返回字段
expression
中的最小值
返回数据表
Products
中字段
Price
的最小
值：
SELECT MIN(Price) AS MinPrice FROM
Products;
MOD(x,y)
返回
x
除以
y
以后的余
数
5
除于
2
的余数：
SELECT MOD(5,2) -- 1
PI()
返回圆周率
(3.141593
）
SELECT PI() --3.141593
POW(x,y)
返回
x
的
y
次方
2
的
3
次方：
SELECT POW(2,3) -- 8
RAND()
返回
0
到
1
的随机数
SELECT RAND() --0.93099315644334
ROUND(x)
返回离
x
最近的整数
（遵循四舍五入）
SELECT ROUND(1.23456) --1
ROUND(x,y)
返回指定位数的小数
（遵循四舍五入）
SELECT ROUND(1.23456,3) –1.235
TRUNCATE(x,y)
返回数值
x
保留到小数
点后
y
位的值（与
ROUND
最大的区别是
不会进行四舍五入）
SELECT TRUNCATE(1.23456,3) -- 1.234
2.
数学函数
函数
描述
实例
CHAR_LENGTH(s)
返回字符串
s
的字符数
返回字符串
RUNOOB
的字符数
SELECT
CHAR_LENGTH("RUNOOB") AS
LengthOfString;
CHARACTER_LENGTH(s)
返回字符串
s
的字符数
返回字符串
RUNOOB
的字符数
SELECT
CHARACTER_LENGTH("RUNOOB") AS
LengthOfString;
CONCAT(s1,s2...sn)
字符串
s1,s2
等多个字
符串合并为一个字符串
合并多个字符串
SELECT CONCAT("SQL
", "Runoob ", "Gooogle ", "Facebook")
AS ConcatenatedString;
CONCAT_WS(x,
s1,s2...sn)
同
CONCAT(s1,s2,...)
函
数，但是每个字符串之
间要加上
x
，
x
可以是分
隔符
合并多个字符串，并添加分隔符：
SELECT CONCAT_WS("-", "SQL",
"Tutorial", "is", "fun!")AS
ConcatenatedString;
FIELD(s,s1,s2...)
返回第一个字符串
s
在
字符串列表
(s1,s2...)
中的
位置
返回字符串
c
在列表值中的位置：
SELECT FIELD("c", "a", "b", "c", "d", "e");
LTRIM(s)
去掉字符串
s
开始处的
空格
去掉字符串
RUNOOB
开始处的空格：
SELECT LTRIM(" RUNOOB") AS
LeftTrimmedString;-- RUNOOB
MID(s,n,len)
从字符串
s
的
n
位置截
取长度为
len
的子字符
串，同
SUBSTRING(s,n,len)
从字符串
RUNOOB
中的第
2
个位置截
取
3
个 字符：
SELECT MID("RUNOOB",
2, 3) AS ExtractString; -- UNO
POSITION(s1 IN s)
从字符串
s
中获取
s1
的
开始位置
返回字符串
abc
中
b
的位置：
SELECT
POSITION('b' in 'abc') -- 2
REPLACE(s,s1,s2)
将字符串
s2
替代字符串
s
中的字符串
s1
将字符串
abc
中的字符
a
替换为字符
x
：
SELECT REPLACE('abc','a','x') --xbc
REVERSE(s)
将字符串
s
的顺序反过来
将字符串
abc
的顺序反过来：
SELECT
REVERSE('abc') -- cba
RIGHT(s,n)
返回字符串
s
的后
n
个
字符
返回字符串
runoob
的后两个字符：
SELECT RIGHT('runoob',2) -- ob
RTRIM(s)
去掉字符串
s
结尾处的
空格
去掉字符串
RUNOOB
的末尾空格：
SELECT RTRIM("RUNOOB ") AS
RightTrimmedString; -- RUNOOB
STRCMP(s1,s2)
比较字符串
s1
和
s2
，
如果
s1
与
s2
相等返回
0
，如果
s1>s2
返回
1
，如果
s1<s2
返回
-1
比较字符串：
SELECT
STRCMP("runoob", "runoob"); -- 0
3.
字符串函数
函数
描述
实例
SUBSTR(s, start,
length)
从字符串
s
的
start
位置
截取长度为
length
的子
字符串
从字符串
RUNOOB
中的第
2
个位置截
取
3
个 字符：
SELECT
SUBSTR("RUNOOB", 2, 3) AS
ExtractString; -- UNO
SUBSTRING(s, start,
length)
从字符串
s
的
start
位置
截取长度为
length
的子
字符串
从字符串
RUNOOB
中的第
2
个位置截
取
3
个 字符：
SELECT
SUBSTRING("RUNOOB", 2, 3) AS
ExtractString; -- UNO
TRIM(s)
去掉字符串
s
开始和结
尾处的空格
去掉字符串
RUNOOB
的首尾空格：
SELECT TRIM(' RUNOOB ') AS
TrimmedString;
UCASE(s)
将字符串转换为大写
将字符串
runoob
转换为大写：
SELECT
UCASE("runoob"); -- RUNOOB
UPPER(s)
将字符串转换为大写
将字符串
runoob
转换为大写：
SELECT
UPPER("runoob"); -- RUNOOB
LCASE(s)
将字符串
s
的所有字母
变成小写字母
字符串
RUNOOB
转换为小写：
SELECT
LCASE('RUNOOB') -- runoob
LOWER(s)
将字符串
s
的所有字母
变成小写字母
字符串
RUNOOB
转换为小写：
SELECT
LOWER('RUNOOB') -- runoob
函数名
描述
实例
UNIX_TIMESTAMP()
返回从
1970-01-01 00:00:00
到当前毫秒值
select UNIX_TIMESTAMP() -> 1632729059
UNIX_TIMESTAMP(DATE_STRING)
将制定日期转为毫秒值时间戳
SELECT UNIX_TIMESTAMP('2011-12-07 13:01:03');
FROM_UNIXTIME(BIGINT
UNIXTIME[, STRING FORMAT])
将毫秒值时间戳转为指定格式日期
SELECT FROM_UNIXTIME(1598079966,'%Y-%m-%d %H:%i:%s');
(1598079966,'%Y-%m-%d %H:%i:%s'); -> 2020-08-22 15-06-06
CURDATE()
返回当前日期
SELECT CURDATE();-> 2018-09-19
CURRENT_DATE()
返回当前日期
SELECT CURRENT_DATE();-> 2018-09-19
CURRENT_TIME
返回当前时间
SELECT CURRENT_TIME();-> 19:59:02
CURTIME()
返回当前时间
SELECT CURTIME();-> 19:59:02
CURRENT_TIMESTAMP()
返回当前日期和时间
SELECT CURRENT_TIMESTAMP()-> 2018-09-19 20:57:43
DATE()
从日期或日期时间表达式中提取日期值
SELECT DATE("2017-06-15"); -> 2017-06-15
DATEDIFF(d1,d2)
计算日期
d1->d2
之间相隔的天数
SELECT DATEDIFF('2001-01-01','2001-02-02')-> -32
TIMEDIFF(time1, time2)
计算时间差值
SELECT TIMEDIFF("13:10:11", "13:10:10");-> 00:00:01
DATE_FORMAT(d,f)
按表达式
f
的要求显示日期
d
SELECT DATE_FORMAT('2011-11-11 11:11:11','%Y-%m-%d %r')->
2011-11-11 11:11:11 AM
STR_TO_DATE(string,
format_mask)
将字符串转变为日期
SELECT STR_TO_DATE("August 10 2017", "%M %d %Y");-> 2017-
08-10
DATE_SUB(date,INTERVAL expr
type)
函数从日期减去指定的时间间隔。
Orders
表中
OrderDate
字段减去
2
天：
SELECT
OrderId,DATE_SUB(OrderDate,INTERVAL 2 DAY) AS
OrderPayDateFROM Orders
ADDDATE/DATE_ADD(d
，
INTERVAL expr type)
计算起始日期
d
加上一个时间段后的日期，
type
值可以是：
MICROSECONDSECONDMINUTEHOURDAYWEEKMONTHQUARTERYEARDAY_MINUTEDAY_HOURYEAR_MONTH
SELECT DATE_ADD("2017-06-15", INTERVAL 10 DAY); -> 2017-06-
25 SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL 15
MINUTE);-> 2017-06-15 09:49:21 SELECT DATE_ADD("2017-06-15
09:34:21", INTERVAL -3 HOUR);->2017-06-15 06:34:21 SELECT
DATE_ADD("2017-06-15 09:34:21", INTERVAL -3 HOUR);->2017-
04-15
DATE_ADD(d
，
INTERVAL expr
type)
计算起始日期
d
加上一个时间段后的日期，
type
值可以是：
SECOND_MICROSECONDMINUTE_MICROSECONDMINUTE_SECONDHOUR_MICROSECONDHOUR_SECONDHOUR_MINUTEDAY_MICROSECONDDAY_SECONDDAY_MINUTEDAY_HOURYEAR_MONTH
SELECT DATE_ADD("2017-06-15", INTERVAL 10 DAY); -> 2017-06-
25 SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL 15
MINUTE);-> 2017-06-15 09:49:21 SELECT DATE_ADD("2017-06-15
09:34:21", INTERVAL -3 HOUR);->2017-06-15 06:34:21 SELECT
DATE_ADD("2017-06-15 09:34:21", INTERVAL -3 HOUR);->2017-
04-15
EXTRACT(type FROM d)
从日期
d
中获取指定的值，
*
*type**
指定返回的值。
*
*type
可取值为：
MICROSECONDSECONDMINUTEHOUR…..**
SELECT EXTRACT(MINUTE FROM '2011-11-11 11:11:11') -> 11
LAST_DAY(d)
返回给给定日期的那一月份的最后一天
SELECT LAST_DAY("2017-06-20");-> 2017-06-30
MAKEDATE(year, day-of-year)
基于给定参数年份
year
和所在年中的天数序号
day-of-year
返回一个日期
SELECT MAKEDATE(2017, 3);-> 2017-01-03
YEAR(d)
返回年份
SELECT YEAR("2017-06-15");-> 2017
MONTH(d)
返回日期
d
中的月份值，
1
到
12
SELECT MONTH('2011-11-11 11:11:11')->11
DAY(d)
返回日期值
d
的日期部分
SELECT DAY("2017-06-15"); -> 15
HOUR(t)
返回
t
中的小时值
SELECT HOUR('1:2:3')-> 1
MINUTE(t)
返回
t
中的分钟值
SELECT MINUTE('1:2:3')-> 2
SECOND(t)
返回
t
中的秒钟值
SELECT SECOND('1:2:3')-> 3
QUARTER(d)
返回日期
d
是第几季节，返回
1
到
4
SELECT QUARTER('2011-11-11 11:11:11')-> 4
MONTHNAME(d)
返回日期当中的月份名称，如
November
SELECT MONTHNAME('2011-11-11 11:11:11')-> November
MONTH(d)
返回日期
d
中的月份值，
1
到
12
SELECT MONTH('2011-11-11 11:11:11')->11
DAYNAME(d)
返回日期
d
是星期几，如
Monday,Tuesday
SELECT DAYNAME('2011-11-11 11:11:11')->Friday
DAYOFMONTH(d)
计算日期
d
是本月的第几天
SELECT DAYOFMONTH('2011-11-11 11:11:11')->11
DAYOFWEEK(d)
日期
d
今天是星期几，
1
星期日，
2
星期一，以此类推
SELECT DAYOFWEEK('2011-11-11 11:11:11')->6
DAYOFYEAR(d)
计算日期
d
是本年的第几天
SELECT DAYOFYEAR('2011-11-11 11:11:11')->315
4.
日期函数
函数名
描述
实例
WEEK(d)
计算日期
d
是本年的第几个星期，范围是
0
到
53
SELECT WEEK('2011-11-11 11:11:11')-> 45
WEEKDAY(d)
日期
d
是星期几，
0
表示星期一，
1
表示星期二
SELECT WEEKDAY("2017-06-15");-> 3
WEEKOFYEAR(d)
计算日期
d
是本年的第几个星期，范围是
0
到
53
SELECT WEEKOFYEAR('2011-11-11 11:11:11')-> 45
YEARWEEK(date, mode)
返回年份及第几周（
0
到
53
），
mode
中
0
表示周天，
1
表示周一，以此类推
SELECT YEARWEEK("2017-06-15");-> 201724
NOW()
返回当前日期和时间
SELECT NOW()-> 2018-09-19 20:57:43
格式
解释
案例
IF(expr,v1,v2)
如果表达式
expr
成立，返回结果
v1
；否则，返回结果
v2
。
SELECT IF(1 > 0,'
正确
','
错误
') -
>
正确
IFNULL(v1,v2)
如果
v1
的值不为
NULL
，则返回
v1
，否则返回
v2
。
SELECT IFNULL(null,'Hello
Word')->Hello Word
ISNULL(expression)
判断表达式是否为
NULL
SELECT ISNULL(NULL);->1
NULLIF(expr1,
expr2)
比较两个字符串，如果字符串
expr1
与
expr2
相等 返回
NULL
，否则返
回
expr1
SELECT NULLIF(25, 25);->
格式
解释
操作
CASE expression WHEN
condition1 THEN result1
WHEN condition2 THEN
result2 ... WHEN
conditionN THEN resultN
ELSE result END
CASE
表示函数开始，
END
表示
函数结束。如果
condition1
成
立，则返回
result1,
如果
condition2
成立，则返回
result2
，当全部不成立则返回
result
，而当有一个成立之后，后
面的就不执行了。
select case 100 when 50
then 'tom' when 100 then
'mary'else 'tim' end ;
select case when 1=2
then 'tom' when 2=2 then
'mary' else'tim' end ;
5.
控制流函数
if
逻辑判断语句
case when
语句
示例
use
mydb4;
--
创建订单表
create table
orders
(
oid int
primary key
,
--
订单
id
price double,
--
订单价格
payType int
--
支付类型
(1:
微信支付
2:
支付宝支付
3:
银行卡支付
4
：其他
)
)
;
insert into
orders
values
(
1
,
1200
,
1
)
;
insert into
orders
values
(
2
,
1000
,
2
)
;
insert into
orders
values
(
3
,
200
,
3
)
;
insert into
orders
values
(
4
,
3000
,
1
)
;
insert into
orders
values
(
5
,
1500
,
2
)
;
--
方式
1
select
*
,
第
9
章
MySQL
视图
1.
概念
视图（
view
）是一个虚拟表，非真实存在，其本质是根据
SQL
语句获取动态的数据集，并为其命
名，用户使用时只需使用视图名称即可获取结果集，并可以将其当作表来使用。
数据库中只存放了视图的定义，而并没有存放视图中的数据。这些数据存放在原来的表中。
使用视图查询数据时，数据库系统会从原来的表中取出对应的数据。因此，视图中的数据是依赖于
原来的表中的数据的。一旦表中的数据发生改变，显示在视图中的数据也会发生改变。
2.
作用
简化代码，可以把重复使用的查询封装成视图重复使用，同时可以使复杂的查询易于理解和使用。
安全原因，如果一张表中有很多数据，很多信息不希望让所有人看到，此时可以使用视图视，如：
社会保险基金表，可以用视图只显示姓名，地址，而不显示社会保险号和工资数等，可以对不同的
用户，设定不同的视图。
3.
特点
优点：
（
1
）为用户集中数据，简化用户的数据查询和处理。有时用户所需要的数据分散在多个表中，定义视图
可将它们集中在一起，从而方便用户的数据查询和处理。
（
2
）屏蔽数据库的复杂性。用户不必了解复杂的数据库中的表结构，并且数据库表的更改也不影响用户
对数据库的使用。
（
3
）简化用户权限的管理。只需授予用户使用视图的权限，而不必指定用户只能使用表的特定列，也增
加了安全性。
（
4
）便于数据共享。各用户不必都定义和存储自己所需的数据，可共享数据库的数据，这样同样的数据
只需存储一次。
（
5
）可以重新组织数据以便输出到其他应用程序中。
缺点：
性能：从数据库视图查询数据可能会很慢，特别是如果视图是基于其他视图创建的。
case
when
payType
=
1
then
'
微信支付
'
when
payType
=
2
then
'
支付宝支付
'
when
payType
=
3
then
'
银行卡支付
'
else
'
其他支付方式
'
end as
payTypeStr
from
orders;
--
方式
2
select
*
,
case
payType
when
1
then
'
微信支付
'
when
2
then
'
支付宝支付
'
when
3
then
'
银行卡支付
'
else
'
其他支付方式
'
end as
payTypeStr
from
orders;
表依赖关系：将根据数据库的基础表创建一个视图。每当更改与其相关联的表的结构时，都必须更
改视图。
4.
操作
4.1
创建视图
语法
示例
4.2
修改视图
修改视图是指修改数据库中已存在的表的定义。当基本表的某些字段发生改变时，可以通过修改视图来
保持视图和基本表之间一致。
MySQL
中通过
CREATE OR REPLACE VIEW
语句和
ALTER VIEW
语句来修改
视图。
格式
create
[
or replace
] [
algorithm
=
{
undefined
|
merge
|
temptable
}]
view
view_name
[(
column_list
)]
as
select_statement
[
with
[
cascaded
|
local
]
check option
]
参数说明：
（
1
）
algorithm
：可选项，表示视图选择的算法。
（
2
）
view_name
：表示要创建的视图名称。
（
3
）
column_list
：可选项，指定视图中各个属性的名词，默认情况下与
SELECT
语句中的查询的属性相同。
（
4
）
select_statement
：表示一个完整的查询语句，将查询记录导入视图中。
（
5
）
[
with
[
cascaded
|
local
]
check option
]
：可选项，表示更新视图时要保证在该视图的权限范
围之内。
create or replace view
view1_emp
as
select
ename,job
from
emp;
--
查看表和视图
show full tables
;
--
查看视图的结构
DESC
/
DESCRIBE
视图名称
;
--
查看视图的属性信息
#
查看视图信息（显示数据表的存储引擎、版本、数据行数和数据大小等）
SHOW TABLE STATUS LIKE
'
视图名称
'
\G
--
查看视图的详细定义信息
SHOW CREATE VIEW
视图名称
;
示例
4.3
更新视图
某些视图是可更新的。也就是说，可以在
UPDATE
、
DELETE
或
INSERT
等语句中使用它们，以更新基表的
内容。对于可更新的视图，在视图中的行和基表中的行之间必须具有一对一的关系。如果视图包含下述
结构中的任何一种，那么它就是不可更新的：
聚合函数（
SUM(), MIN(), MAX(), COUNT()
等）
DISTINCT
GROUP BY
HAVING
UNION
或
UNION ALL
位于选择列表中的子查询
JOIN
FROM
子句中不可更新视图
WHERE
子句中的子查询，引用
FROM
子句中的表。
仅引用文字值（在该情况下，没有要更新的基本表）
Important
视图中虽然可以更新数据，但是有很多的限制。一般情况下，最好将视图作为查询数据的虚拟表，
而不要通过视图更新数据。因为，使用视图更新数据时，如果没有全面考虑在视图中更新数据的限
制，就可能会造成数据更新失败。
示例
alter view
视图名
as select
语句
alter view
view1_emp
as
select
a
.deptno
,a
.dname
,a
.loc
,b
.ename
,b
.sal
from
dept a, emp b
where
a
.deptno
=
b
.deptno
;
-- ---------
更新视图
-------
create or replace view
view1_emp
as
select
ename,job
from
emp;
update
view1_emp
set
ename
=
'
周瑜
'
where
ename
=
'
鲁肃
'
;
--
可以修改
insert into
view1_emp
values
(
'
孙权
'
,
'
文员
'
)
;
--
不可以插入
-- ----------
视图包含聚合函数不可更新
--------------
create or replace view
view2_emp
as
select count
(
*
)
cnt
from
emp;
insert into
view2_emp
values
(
100
)
;
update
view2_emp
set
cnt
=
100
;
4.4
其他操作
重命名视图
删除视图
-- ----------
视图包含
distinct
不可更新
---------
create or replace view
view3_emp
as
select distinct
job
from
emp;
insert into
view3_emp
values
(
'
财务
'
)
;
-- ----------
视图包含
goup by
、
having
不可更新
------------------
create or replace view
view4_emp
as
select
deptno ,
count
(
*
)
cnt
from
emp
group by
deptno
having
cnt
>
2
;
insert into
view4_emp
values
(
30
,
100
)
;
-- ----------------
视图包含
union
或者
union all
不可更新
----------------
create or replace view
view5_emp
as
select
empno,ename
from
emp
where
empno
<=
1005
union
select
empno,ename
from
emp
where
empno
>
1005
;
insert into
view5_emp
values
(
1015
,
'
韦小宝
'
)
;
-- -------------------
视图包含子查询不可更新
--------------------
create or replace view
view6_emp
as
select
empno,ename,sal
from
emp
where
sal
=
(
select max
(
sal
)
from
emp
)
;
insert into
view6_emp
values
(
1015
,
'
韦小宝
'
,
30000
)
;
-- ----------------------
视图包含
join
不可更新
-----------------
create or replace view
view7_emp
as
select
dname,ename,sal
from
emp a
join
dept b
on
a
.deptno
=
b
.deptno
;
insert into
view7_emp
(
dname,ename,sal
)
values
(
'
行政部
'
,
'
韦小宝
'
,
30000
)
;
-- --------------------
视图包含常量文字值不可更新
-------------------
create or replace view
view8_emp
as
select
'
行政部
'
dname,
'
杨过
'
ename;
insert into
view8_emp
values
(
'
行政部
'
,
'
韦小宝
'
)
;
-- rename table
视图名
to
新视图名
;
rename table
view1_emp
to
my_view1
练习
第
10
章 存储过程
1.
概念
MySQL 5.0
版本开始支持存储过程。
简单的说，存储过程就是一组
SQL
语句集，功能强大，可以实现一些比较复杂的逻辑功能，类似于
JAVA
语言中的方法；
存储过就是数据库
SQL
语言层面的代码封装与重用。
2.
作用
存储过程通常有以下优点：
(1).
存储过程增强了
SQL
语言的功能和灵活性。存储过程可以用流控制语句编写，有很强的灵活性，可以
完成复杂的判断和较复杂的运算。
-- drop view
视图名
[,
视图名
…];
drop view if exists
view_student;
删除视图时，只能删除视图的定义，不会删除数据。
-- 1
：查询部门平均薪水最高的部门名称
select
dname
from
dept a ,
(
select
deptno,
avg
(
sal
)
from
emp
group by
deptno
order
by avg
(
sal
)
desc limit
1
)
b
where
a
.deptno
=
b
.deptno
;
-- 2
：查询员工比所属领导薪资高的部门名、员工名、员工领导编号
select
*
from
dept x,
(
select
a
.ename
aname ,a
.sal
asal,b
.ename
bname,b
.sal
bsal,a
.deptno
from
emp a, emp b
where
a
.mgr
=
b
.empno
and
a
.sal
>
b
.sal
)
y
where
x
.deptno
=
y
.deptno
;
-- 3
：查询工资等级为
4
级，
2000
年以后入职的工作地点为北京的员工编号、姓名和工资，并查询出薪资在前
三名的员工信息
create view
xxx
as
SELECT
e
.empno
,e
.ename
,e
.sal
,e
.hiredate
FROM
emp e,dept d,salgrade s
WHERE
(
e
.sal
BETWEEN
losal
AND
hisal
)
AND
s
.GRADE
=
4
AND
year
(
e
.hiredate
)
>
'2000'
AND
d
.loc
=
'
北京
'
;
select
*
from
(
select
*
,
dense_rank
()
over
(
order by
sal
desc
)
rn
from
xxx
)
t
where
t
.rn
<=
3
;
(2).
存储过程允许标准组件式编程。存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存
储过程的
SQL
语句。而且数据库专业人员可以随时对存储过程进行修改，对应用程序源代码毫无影响。
(3).
存储过程能实现较快的执行速度。如果某一操作包含大量的
Transaction-SQL
代码或分别被多次执
行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程
时查询，优化器对其进行分析优化，并且给出最终被存储在系统表中的执行计划。而批处理的
Transaction-SQL
语句在每次运行时都要进行编译和优化，速度相对要慢一些。
(4).
存储过程能过减少网络流量。针对同一个数据库对象的操作（如查询、修改），如果这一操作所涉及
的
Transaction-SQL
语句被组织程存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只
是该调用语句，从而大大增加了网络流量并降低了网络负载。
(5).
存储过程可被作为一种安全机制来充分利用。系统管理员通过执行某一存储过程的权限进行限制，能
够实现对相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证了数据的安全。
入门案例
格式
示例
3.
变量
局部变量
:
用户自定义，在
begin/end
块中有效
格式
示例
delimiter
自定义结束符号
create procedure
储存名
([
in
,
out
,
inout
]
参数名 数据类形
...
)
begin
sql
语句
end
自定义的结束符合
delimiter
;
--
创建存储过程
delimiter
$$
create procedure
proc01
()
begin
select
empno,ename
from
emp;
end
$$
delimiter
;
--
调用存储过程
call
proc01
()
;
语法： 声明变量
declare
var_name type
[
default
var_value
]
;
举例：
declare nickname varchar
(
32
)
;
delimiter
$$
create procedure
proc02
()
begin
declare
var_name01 varchar
(
20
)
default
‘aaa’;
--
定义局部变量
set
var_name01
=
‘zhangsan’;
select
var_name01;
end
$$
delimiter
;
--
调用存储过程
call
proc02
()
;
MySQL
中还可以使用
SELECT..INTO
语句为变量赋值。其基本语法如下：
select
col_name
[
...
]
into
var_name
[
,...
]
from table_name
wehre
condition
其中：
col_name
参数表示查询的字段名称；
var_name
参数是变量的名称；
table_name
参数指表的名称；
condition
参数指查询条件。
注意：当将查询结果赋值给变量时，该查询语句的返回结果只能是单行单列。
示例
delimiter
$$
create procedure
proc03
()
begin
declare
my_ename varchar
(
20
)
;
select
ename
into
my_ename
from
emp
where
empno
=
1001
;
select
my_ename;
end
$$
delimiter
;
--
调用存储过程
call
proc03
()
;
用户变量：用户自定义，当前会话（连接）有效。类比
java
的成员变量
格式
@var_name
不需要提前声明，使用即声明
示例
delimiter
$$
create procedure
proc04
()
begin
set
@var_name01
=
'ZS'
;
end
$$
delimiter
;
call
proc04
()
;
select
@var_name01
;
--
可以看到结果
系统变量：
系统变量又分为全局变量与会话变量全局变量
在
MYSQL
启动的时候由服务器自动将它们初始化为默认值，这些默认值可以通过更改
my.ini
这
个文件来更改。
会话变量在每次建立一个新的连接的时候，由
MYSQL
来初始化。
MYSQL
会将当前所有全局变
量的值复制一份。来做为会话变量。
也就是说，如果在建立会话以后，没有手动更改过会话变量与全局变量的值，那所有这些变量
的值都是一样的。
全局变量与会话变量的区别就在于，对全局变量的修改会影响到整个服务器，但是对会话变量
的修改，只会影响到当前的会话（也就是当前的数据库连接）。
有些系统变量的值是可以利用语句来动态进行更改的，但是有些系统变量的值却是只读的，对
于那些可以更改的系统变量，我们可以利用
set
语句进行更改。
系统变量
-
全局变量：由系统提供，在整个数据库有效。
格式
语法：
@@global.var_name
示例
--
查看全局变量
show global variables
;
--
查看某全局变量
select
@@global.auto_increment_increment
;
--
修改全局变量的值
set global
sort_buffer_size
=
40000
;
set
@@global.sort_buffer_size
=
40000
;
系统变量
-
会话变量：由系统提供，当前会话（连接）有效
格式
语法：
@@session.var_name
示例
4.
参数传递
in
in
表示传入的参数， 可以传入数值或者变量，即使传入变量，并不会更改变量的值，可以内部更改，仅
仅作用在函数范围内。
out
out
表示从存储过程内部传值给调用者
--
查看会话变量
show session variables
;
--
查看某会话变量
select
@@session.auto_increment_increment
;
--
修改会话变量的值
set session
sort_buffer_size
=
50000
;
set
@@session.sort_buffer_size
=
50000
;
--
封装有参数的存储过程，传入员工编号，查找员工信息
delimiter
$$
create procedure
dec_param01
(
in
param_empno varchar
(
20
))
begin
select
*
from
emp
where
empno
=
param_empno;
end
$$
delimiter
;
call
dec_param01
(
'1001'
)
;
--
封装有参数的存储过程，可以通过传入部门名和薪资，查询指定部门，并且薪资大于指定值的员工信息
delimiter
$$
create procedure
dec_param0x
(
in
dname varchar
(
50
)
,
in
sal decimal
(
7
,
2
)
,
)
begin
select
*
from
dept a, emp b
where
b
.sal
>
sal
and
a
.dname
=
dname;
end
$$
delimiter
;
call
dec_param0x
(
'
学工部
'
,
20000
)
;
-- ---------
传出参数：
out---------------------------------
use
mysql7_procedure;
--
封装有参数的存储过程，传入员工编号，返回员工名字
delimiter
$$
create procedure
proc08
(
in
empno int ,
out
out_ename varchar
(
50
) )
begin
select
ename
into
out_ename
from
emp
where
emp
.empno
=
empno;
end
$$
delimiter
;
call
proc08
(
1001
,
@o_ename
)
;
select
@o_ename
;
--
封装有参数的存储过程，传入员工编号，返回员工名字和薪资
inout
inout
表示从外部传入的参数经过修改后可以返回的变量，既可以使用传入变量的值也可以修改变量的值
（即使函数执行完）
小结
in
输入参数，意思说你的参数要传到存过过程的过程里面去，在存储过程中修改该参数的值不能被
返回
out
输出参数
:
该值可在存储过程内部被改变，并向外输出
inout
输入输出参数，既能输入一个值又能传出来一个值
)
5.
流程控制
5.1
流程控制
-
判断
格式
delimiter
$$
create procedure
proc09
(
in
empno int ,
out
out_ename varchar
(
50
)
,
out
out_sal
decimal
(
7
,
2
))
begin
select
ename,sal
into
out_ename,out_sal
from
emp
where
emp
.empno
=
empno;
end
$$
delimiter
;
call
proc09
(
1001
,
@o_dname
,
@o_sal
)
;
select
@o_dname
;
select
@o_sal
;
--
传入员工名，拼接部门号，传入薪资，求出年薪
delimiter
$$
create procedure
proc10
(
inout
inout_ename varchar
(
50
)
,
inout
inout_sal int
)
begin
select
concat
(
deptno,
"_"
,inout_ename
)
into
inout_ename
from
emp
where
ename
=
inout_ename;
set
inout_sal
=
inout_sal
*
12
;
end
$$
delimiter
;
set
@inout_ename
=
'
关羽
'
;
set
@inout_sal
=
3000
;
call
proc10
(
@inout_ename
,
@inout_sal
)
;
select
@inout_ename
;
select
@inout_sal
;
IF
语句包含多个条件判断，根据结果为
TRUE
、
FALSE
执行语句，与编程语言中的
if
、
else
if
、
else
语法类
似，其语法格式如下：
--
语法
if
search_condition_1
then
statement_list_1
[
elseif
search_condition_2
then
statement_list_2
]
...
[
else
statement_list_n
]
end if
示例
--
输入学生的成绩，来判断成绩的级别：
/*
score < 60 :
不及格
score >= 60 , score <80 :
及格
score >= 80 , score < 90 :
良好
score >= 90 , score <= 100 :
优秀
score > 100 :
成绩错误
*/
delimiter
$$
create procedure
proc_12_if
(
in
score int
)
begin
if
score
<
60
then
select
'
不及格
'
;
elseif
score
<
80
then
select
'
及格
'
;
elseif
score
>=
80
and
score
<
90
then
select
'
良好
'
;
elseif
score
>=
90
and
score
<=
100
then
select
'
优秀
'
;
else
select
'
成绩错误
'
;
end if
;
end
$$
delimiter
;
call
proc_12_if
(
120
)
--
输入员工的名字，判断工资的情况。
delimiter
$$
create procedure
proc12_if
(
in
in_ename varchar
(
50
))
begin
declare
result varchar
(
20
)
;
declare
var_sal decimal
(
7
,
2
)
;
select
sal
into
var_sal
from
emp
where
ename
=
in_ename;
if
var_sal
<
10000
then set
result
=
'
试用薪资
'
;
elseif
var_sal
<
30000
then set
result
=
'
转正薪资
'
;
else
set
result
=
'
元老薪资
'
;
end if
;
5.2
流程控制
-case
格式
示例
select
result;
end
$$
delimiter
;
call
proc12_if
(
'
庞统
'
)
;
CASE
是另一个条件判断的语句，类似于编程语言中的
switch
语法
--
语法一（类比
java
的
switch
）：
case
case_value
when
when_value
then
statement_list
[
when
when_value
then
statement_list
]
...
[
else
statement_list
]
end case
--
语法二：
case
when
search_condition
then
statement_list
[
when
search_condition
then
statement_list
]
...
[
else
statement_list
]
end case
--
语法一
delimiter
$$
create procedure
proc14_case
(
in
pay_type int
)
begin
case
pay_type
when
1
then
select
'
微信支付
'
;
when
2
then select
'
支付宝支付
'
;
when
3
then select
'
银行卡支付
'
;
else select
'
其他方式支付
'
;
end case
;
end
$$
delimiter
;
call
proc14_case
(
2
)
;
call
proc14_case
(
4
)
;
--
语法二
delimiter
$$
create procedure
proc_15_case
(
in
score int
)
begin
case
when
score
<
60
then
select
'
不及格
'
;
when
score
<
80
then
5.3
流程控制
-
循环
概述
循环是一段在程序中只出现一次
,
但可能会连续运行多次的代码。循环中的代码会运行特定的次数
,
或者是
运行到特定条件成立时结束循环
循环分类：
while
repeat
loop
循环控制：
leave
类似于
break
，跳出，结束当前所在的循环
iterate
类似于
continue
，继续，结束本次循环，继续下一次
select
'
及格
'
;
when
score
>=
80
and
score
<
90
then
select
'
良好
'
;
when
score
>=
90
and
score
<=
100
then
select
'
优秀
'
;
else
select
'
成绩错误
'
;
end case
;
end
$$
delimiter
;
call
proc_15_case
(
88
)
;
流程控制
-
循环
-while
格式
【标签
:
】
while
循环条件
do
循环体
;
end while
【 标签】
;
示例
--
创建测试表
create table user
(
uid int primary_key,
username varchar
(
50
)
,
password
varchar
(
50
)
)
;
-- -------
存储过程
-while
delimiter
$$
create procedure
proc16_while1
(
in
insertcount int
)
begin
declare
i int
default
1
;
label:
while
i
<=
insertcount
do
insert into user
(
uid,username,
`password`
)
values
(
i,concat
(
'user-
'
,i
)
,
'123456'
)
;
set
i
=
i
+
1
;
end while
label;
end
$$
delimiter
;
call
proc16_while
(
10
)
;
-- -------
存储过程
-while + leave
truncate table user
;
delimiter
$$
create procedure
proc16_while2
(
in
insertcount int
)
begin
declare
i int
default
1
;
label:
while
i
<=
insertcount
do
insert into user
(
uid,username,
`password`
)
values
(
i,concat
(
'user-
'
,i
)
,
'123456'
)
;
if
i
=
5
then leave
label;
end if
;
set
i
=
i
+
1
;
end while
label;
end
$$
delimiter
;
call
proc16_while2
(
10
)
;
-- -------
存储过程
-while+iterate
truncate table user
;
delimiter
$$
create procedure
proc16_while3
(
in
insertcount int
)
begin
declare
i int
default
1
;
label:
while
i
<=
insertcount
do
set
i
=
i
+
1
;
if
i
=
5
then iterate
label;
end if
;
insert into user
(
uid,username,
`password`
)
values
(
i,concat
(
'user-
'
,i
)
,
'123456'
)
;
end while
label;
end
$$
delimiter
;
call
proc16_while3
(
10
)
;
流程控制
-
循环
-repeat
格式
[
标签
:
]
repeat
循环体
;
until
条件表达式
end repeat
[
标签
]
;
示例
-- -------
存储过程
-
循环控制
-repeat
use
mysql7_procedure;
truncate table user
;
delimiter
$$
create procedure
proc18_repeat
(
in
insertCount int
)
begin
declare
i int
default
1
;
label:
repeat
insert into user
(
uid, username,
password
)
values
(
i,concat
(
'user-
'
,i
)
,
'123456'
)
;
set
i
=
i
+
1
;
until i
>
insertCount
end repeat
label;
select
'
循环结束
'
;
end
$$
delimiter
;
call
proc18_repeat
(
100
)
;
流程控制
-
循环
-loop
格式
[
标签
:
]
loop
循环体
;
if
条件表达式
then
leave
[
标签
]
;
end if
;
end loop
;
示例
-- -------
存储过程
-
循环控制
-loop
truncate table user
;
delimiter
$$
create procedure
proc19_loop
(
in
insertCount int
)
begin
declare
i int
default
1
;
label:
loop
insert into user
(
uid, username,
password
)
values
(
i,concat
(
'user-
'
,i
)
,
'123456'
)
;
set
i
=
i
+
1
;
if
i
>
5
then
leave
label;
end if
;
end loop
label;
select
'
循环结束
'
;
end
$$
delimiter
;
call
proc19_loop
(
10
)
;
5.4
游标
游标
(cursor)
是用来存储查询结果集的数据类型
,
在存储过程和函数中可以使用光标对结果集进行循环的
处理。光标的使用包括光标的声明、
OPEN
、
FETCH
和
CLOSE.
格式
示例
5.5
句柄
MySql
存储过程也提供了对异常处理的功能：通过定义
HANDLER
来完成异常声明的实现
.
官方文档：
https://dev.mysql.com/doc/refman/5.7/en/declare-handler.html
格式
--
声明语法
declare
cursor_name
cursor for
select_statement
--
打开语法
open
cursor_name
--
取值语法
fetch
cursor_name
into
var_name
[
, var_name
]
...
--
关闭语法
close
cursor_name
use
mysql7_procedure;
delimiter
$$
create procedure
proc20_cursor
(
in
in_dname varchar
(
50
))
begin
--
定义局部变量
declare
var_empno varchar
(
50
)
;
declare
var_ename varchar
(
50
)
;
declare
var_sal decimal
(
7
,
2
)
;
--
声明游标
declare
my_cursor
cursor for
select
empno , ename, sal
from
dept a ,emp b
where
a
.deptno
=
b
.deptno
and
a
.dname
=
in_dname;
--
打开游标
open
my_cursor;
--
通过游标获取每一行数据
label:
loop
fetch
my_cursor
into
var_empno, var_ename, var_sal;
select
var_empno, var_ename, var_sal;
end loop
label;
--
关闭游标
close
my_cursor;
end
--
调用存储过程
call
proc20_cursor
(
'
销售部
'
)
;
DECLARE
handler_action
HANDLER
FOR
condition_value
[
, condition_value
]
...
statement
handler_action:
{
CONTINUE
|
EXIT
|
UNDO
}
condition_value:
{
mysql_error_code
|
condition_name
|
SQLWARNING
|
NOT
FOUND
|
SQLEXCEPTION
Note
在语法中，变量声明、游标声明、
handler
声明是必须按照先后顺序书写的，否则创建存储过程出
错
示例
use
mysql7_procedure;
drop procedure if exists
proc21_cursor_handler;
--
需求：输入一个部门名，查询该部门员工的编号、名字、薪资 ，将查询的结果集添加游标
delimiter
$$
create procedure
proc20_cursor
(
in
in_dname varchar
(
50
))
begin
--
定义局部变量
declare
var_empno int;
declare
var_ename varchar
(
50
)
;
declare
var_sal decimal
(
7
,
2
)
;
declare
flag int
default
1
;
-- ---------------------
--
声明游标
declare
my_cursor
cursor for
select
empno,ename,sal
from
dept a, emp b
where
a
.deptno
=
b
.deptno
and
a
.dname
=
in_dname;
--
定义句柄，当数据未发现时将标记位设置为
0
declare continue handler for NOT
FOUND
set
flag
=
0
;
--
打开游标
open
my_cursor;
--
通过游标获取值
label:
loop
fetch
my_cursor
into
var_empno, var_ename,var_sal;
--
判断标志位
if
flag
=
1
then
select
var_empno, var_ename,var_sal;
else
leave
label;
end if
;
end loop
label;
--
关闭游标
close
my_cursor;
end
$$;
delimiter
;
call
proc21_cursor_handler
(
'
销售部
'
)
;
练习
需求
创建下个月的每天对应的表
user_2021_11_01
、
user_2021_11_02
、
...
需求描述：
我们需要用某个表记录很多数据，比如记录某某用户的搜索、购买行为
(
注意，此处是假设用数据库保存
)
，当
每天记录较多时，如果把所有数据都记录到一张表中太庞大，需要分表，我们的要求是，每天一张表，存当天的
统计数据，就要求提前生产这些表
——
每月月底创建下一个月每天的表！
预备知识
PREPARE
stmt_name
FROM
preparable_stmt
EXECUTE
stmt_name
[
USING
@var_name
[
,
@var_name
]
...
]
{
DEALLOCATE
|
DROP
}
PREPARE
stmt_name
--
知识点 时间的处理
-- EXTRACT(unit FROM date)
截取时间的指定位置值
-- DATE_ADD(date,INTERVAL expr unit)
日期运算
-- LAST_DAY(date)
获取日期的最后一天
-- YEAR(date)
返回日期中的年
-- MONTH(date)
返回日期的月
-- DAYOFMONTH(date)
返回日
--
思路：循环构建表名
user_2021_11_01
到
user_2020_11_30
；并执行
create
语句。
use
mysql7_procedure;
drop procedure if exists
proc22_demo;
delimiter
$$
create procedure
proc22_demo
()
begin
declare
next_year int;
declare
next_month int;
declare
next_month_day int;
declare
next_month_str char
(
2
)
;
declare
next_month_day_str char
(
2
)
;
--
处理每天的表名
declare
table_name_str char
(
10
)
;
declare
t_index int
default
1
;
-- declare create_table_sql varchar(200);
--
获取下个月的年份
第
11
章 存储函数
1.
概述
MySQL
存储函数（自定义函数），函数一般用于计算和返回一个值，可以将经常需要使用的计算或功能
写成一个函数。存储函数和存储过程一样，都是在数据库中定义一些
SQL
语句的集合。
存储函数与存储过程的区别
1.
存储函数有且只有一个返回值，而存储过程可以有多个返回值，也可以没有返回值。
2.
存储函数只能有输入参数，而且不能带
in,
而存储过程可以有多个
in,out,inout
参数。
set
next_year
=
year
(
date_add
(
now
()
,
INTERVAL
1
month
))
;
--
获取下个月是几月
set
next_month
=
month
(
date_add
(
now
()
,
INTERVAL
1
month
))
;
--
下个月最后一天是几号
set
next_month_day
=
dayofmonth
(
LAST_DAY
(
date_add
(
now
()
,
INTERVAL
1
month
)))
;
if
next_month
<
10
then set
next_month_str
=
concat
(
'0'
,next_month
)
;
else
set
next_month_str
=
concat
(
''
,next_month
)
;
end if
;
while
t_index
<=
next_month_day
do
if
(
t_index
<
10
)
then set
next_month_day_str
=
concat
(
'0'
,t_index
)
;
else
set
next_month_day_str
=
concat
(
''
,t_index
)
;
end if
;
-- 2021_11_01
set
table_name_str
=
concat
(
next_year,
'_'
,next_month_str,
'_'
,next_month_day_str
)
;
--
拼接
create sql
语句
set
@create_table_sql
=
concat
(
'create table user_'
,
table_name_str,
'(`uid` INT ,`ename` varchar(50) ,`information` varchar(50))
COLLATE=\'utf8_general_ci\' ENGINE=InnoDB'
)
;
-- FROM
后面不能使用局部变量！
prepare
create_table_stmt
FROM
@create_table_sql
;
execute
create_table_stmt;
DEALLOCATE prepare
create_table_stmt;
set
t_index
=
t_index
+
1
;
end while
;
end
$$
delimiter
;
call
proc22_demo
()
;
3.
存储过程中的语句功能更强大，存储过程可以实现很复杂的业务逻辑，而函数有很多限制，如不能在函
数中使用
insert,update,delete,create
等语句；
4.
存储函数只完成查询的工作，可接受输入参数并返回一个结果，也就是函数实现的功能针对性比较强。
5.
存储过程可以调用存储函数。但函数不能调用存储过程。
6.
存储过程一般是作为一个独立的部分来执行
(call
调用
)
。而函数可以作为查询语句的一个部分来调用
.
2.
格式
在
MySQL
中，创建存储函数使用
create function
关键字，其基本形式如下：
Caution
参数说明：
（
1
）
func_name
：存储函数的名称。
（
2
）
param_name type
：可选项，指定存储函数的参数。
type
参数用于指定存储函数的参数类
型，该类型可以是
MySQL
数据库中所有支持的类型。
（
3
）
RETURNS type
：指定返回值的类型。
（
4
）
characteristic
：可选项，指定存储函数的特性。（
5
）
routine_body
：
SQL
代码内容。
3.
示例
create function
func_name
([
param_name type
[
,...
]])
returns
type
[
characteristic ...
]
begin
routine_body
end
;
create database
mydb9_function;
--
导入测试数据
use
mydb9_function;
set global
log_bin_trust_function_creators
=
TRUE
;
--
信任子程序的创建者
--
创建存储函数
-
没有输输入参数
drop function if exists
myfunc1_emp;
delimiter
$$
create function
myfunc1_emp
()
returns
int
begin
declare
cnt int
default
0
;
select count
(
*
)
into
cnt
from
emp;
return
cnt;
end
$$
delimiter
;
--
调用存储函数
select
myfunc1_emp
()
;
--
创建存储过程
-
有输入参数
drop function if exists
myfunc2_emp;
第
12
章
MySQL
触发器
1.
概述
触发器，就是一种特殊的存储过程。触发器和存储过程一样是一个能够完成特定功能、存储在数据
库服务器上的
SQL
片段，但是触发器无需调用，当对数据库表中的数据执行
DML
操作时自动触发这
个
SQL
片段的执行，无需手动调用。
在
MySQL
中，只有执行
insert,delete,update
操作时才能触发触发器的执行
触发器的这种特性可以协助应用在数据库端确保数据的完整性
,
日志记录
,
数据校验等操作 。
使用别名
OLD
和
NEW
来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触
发器还只支持行级触发，不支持语句级触发。
触发器特性
1
、什么条件会触发：
I
、
D
、
U
2
、什么时候触发：在增删改前或者后
3
、触发频率：针对每一行执行
4
、触发器定义在表上，附着在表上
delimiter
$$
create function
myfunc2_emp
(
in_empno int
)
returns
varchar
(
50
)
begin
declare
out_name varchar
(
50
)
;
select
ename
into
out_name
from
emp
where
empno
=
in_empno;
return
out_name;
end
$$
delimiter
;
select
myfunc2_emp
(
1008
)
;
2.
格式
3.
示例
1
、创建只有一个执行语句的触发器
create trigger
触发器名
before
|
after
触发事件
on
表名
for each row
执行语句
;
2
、创建有多个执行语句的触发器
create trigger
触发器名
before
|
after
触发事件
on
表名
for each row
begin
执行语句列表
end
;
--
数据准备
create database if not exists
mydb10_trigger;
use
mydb10_trigger;
--
用户表
create table user
(
uid int
primary key
,
username varchar
(
50
)
not
null
,
password
varchar
(
50
)
not
null
)
;
--
用户信息操作日志表
create table
user_logs
(
id int
primary key auto_increment
,
time timestamp,
log_text varchar
(
255
)
)
;
--
如果触发器存在，则先删除
drop trigger if exists
trigger_test1;
--
创建触发器
trigger_test1
create trigger
trigger_test1
after insert on user
--
触发时机：当添加
user
表数据时触发
for each row
insert into
user_logs
values
(
NULL
,now
()
,
'
有新用户注册
'
)
;
--
添加数据，触发器自动执行并添加日志代码
insert into user values
(
1
,
'
张三
'
,
'123456'
)
;
--
如果触发器
trigger_test2
存在，则先删除
drop trigger if exists
trigger_test2;
--
创建触发器
trigger_test2
delimiter
$$
create trigger
trigger_test2
after update on user
--
触发时机：当修改
user
表数据时触发
for each row
--
每一行
begin
触发器类型
触发器类型
NEW
和
OLD
的使用
INSERT
型触发器
NEW
表示将要或者已经新增的数据
UPDATE
型触发器
OLD
表示修改之前的数据
, NEW
表示将要或已经修改后的数据
DELETE
型触发器
OLD
表示将要或者已经删除的数据
NEW
与
OLD
格式：
MySQL
中定义了
NEW
和
OLD
，用来表示触发器的所在表中，触发了触发器的那一行数据，来引
用触发器中发生变化的记录内容，具体地：
使用方法：
NEW.columnName
（
columnName
为相应数据表某一列名）
示例
Note
1.MYSQL
中触发器中不能对本表进行
insert ,update ,delete
操作，以免递归循环触发
2.
尽量少使用触发器，假设触发器触发每次执行
1s
，
insert table 500
条数据，那么就需要触发
500
次触发器，光是触发器执行的时间就花费了
500s
，而
insert 500
条数据一共是
1s
，那么这个
insert
的
效率就非常低了。
3.
触发器是针对每一行的；对增删改非常频繁的表上切记不要使用触发器，因为它会非常消耗资
源。
insert into
user_logs
values
(
NULL
,now
()
,
'
用户修改发生了修改
'
)
;
end
$$
delimiter
;
--
添加数据，触发器自动执行并添加日志代码
update user set password
=
'888888'
where
uid
=
1
;
create trigger
trigger_test3
after insert
on user for each row
insert into
user_logs
values
(
NULL
,now
()
,concat
(
'
有新用户添加，信息
为
:'
,NEW
.uid
,NEW
.username
,NEW
.password
))
;
--
测试
insert into user values
(
4
,
'
赵六
'
,
'123456'
)
;
--
查看触发器
show triggers
;
--
删除触发器
-- drop trigger [if exists] trigger_name
drop trigger if exists
trigger_test1;
第
13
章
MySQL
索引
1.
概述
索引是通过某种算法，构建出一个数据模型，用于快速找出在某个列中有一特定值的行，不使用索引，
MySQL
必须从第一条记录开始读完整个表，直到找出相关的行，表越大，查询数据所花费的时间就越
多，如果表中查询的列有一个索引，
MySQL
能够快速到达一个位置去搜索数据文件，而不必查看所有数
据，那么将会节省很大一部分时间。
索引类似一本书的目录，比如要查找
’student’
这个单词，可以先找到
s
开头的页然后向后查找，这个就类
似索引。
2.
分类
索引是存储引擎用来快速查找记录的一种数据结构，按照实现的方式类分，主要有
Hash
索引和
B+Tree
索
引
Hash
索引
B+Tree
索引
3.
操作
创建索引
-
单列索引
-
普通索引
单列索引：一个索引只包含单个列，但一个表中可以有多个单列索引
;
普通索引：
MySQL
中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为
了查询数据更快一点。
格式
示例
create database
mydb5;
use
mydb5;
--
方式
1-
创建表的时候直接指定
create table
student
(
sid int
primary key
,
card_id varchar
(
20
)
,
name varchar
(
20
)
,
gender varchar
(
20
)
,
age int,
birth date,
phone_num varchar
(
20
)
,
score double,
index
index_name
(
name
)
--
给
name
列创建索引
)
;
--
方式
2-
直接创建
-- create index indexname on tablename(columnname);
create index
index_gender
on
student
(
gender
)
;
--
方式
3-
修改表结构
(
添加索引
)
-- alter table tablename add index indexname(columnname)
alter table
student
add index
index_age
(
age
)
;
-- 1
、查看数据库所有索引
-- select * from mysql.`innodb_index_stats` a where a.`database_name` = '
数据库
名
’;
select
*
from
mysql.
`innodb_index_stats`
a
where
a.
`database_name`
=
'mydb5'
;
-- 2
、查看表中所有索引
-- select * from mysql.`innodb_index_stats` a where a.`database_name` = '
数据库名
'
and a.table_name like '%
表名
%’;
select
*
from
mysql.
`innodb_index_stats`
a
where
a.
`database_name`
=
'mydb5'
and
a
.table_name
like
'%student%'
;
-- 3
、查看表中所有索引
-- show index from table_name;
show index from
student;
删除索引
格式
drop index
索引名
on
表名
--
或
alter table
表名
drop index
索引名
操作
drop index
index_gender
on
student
--
或
alter table
student
drop index
index_name
创建索引
-
单列索引
-
唯一索引
唯一索引与前面的普通索引类似，不同的就是：
索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方
式：
操作
--
方式
1-
创建表的时候直接指定
create table
student2
(
sid int
primary key
,
card_id varchar
(
20
)
,
name varchar
(
20
)
,
gender varchar
(
20
)
,
age int,
birth date,
phone_num varchar
(
20
)
,
score double,
unique
index_card_id
(
card_id
)
--
给
card_id
列创建索引
)
;
--
方式
2-
直接创建
-- create unique index
索引名
on
表名
(
列名
)
create unique index
index_card_id
on
student2
(
card_id
)
;
--
方式
3-
修改表结构
(
添加索引
)
-- alter table
表名
add unique [
索引名
] (
列名
)
alter table
student2
add unique
index_phone_num
(
phone_num
)
删除索引
drop index
index_card_id
on
student2
--
或
alter table
student2
drop index
index_phone_num
创建索引
-
单列索引
-
主键索引
每张表一般都会有自己的主键，当我们在创建表时，
MySQL
会自动在主键列上建立一个索引，这就是主
键索引。主键是具有唯一性并且不允许为
NULL
，所以他是一种特殊的唯一索引。
创建索引
-
组合索引
组合索引也叫复合索引，指的是我们在建立索引的时候使用多个字段，例如同时使用身份证和手机
号建立索引，同样的可以建立为普通索引或者是唯一索引。
复合索引的使用复合最左原则。
格式
--
创建索引的基本语法
create index
indexname
on table_name
(
column1
(
length
)
,column2
(
length
))
;
操作
--
组合索引
use
mydb5;
--
创建索引的基本语法
--
普通索引
-- create index indexname on table_name(column1(length),column2(length));
create index
index_phone_name
on
student
(
phone_num,name
)
;
--
操作
-
删除索引
drop index
index_phone_name
on
student;
--
创建索引的基本语法
--
唯一索引
create unique index
index_phone_name
on
student
(
phone_num,name
)
;
select
*
from
student
where
name
=
'
张三
'
;
select
*
from
student
where
phone_num
=
'15100046637'
;
select
*
from
student
where
phone_num
=
'15100046637'
and
name
=
'
张三
'
;
select
*
from
student
where
name
=
'
张三
'
and
phone_num
=
'15100046637'
;
/*
三条
sql
只有
2
、
3
、
4
能使用的到索引
idx_phone_name,
因为条件里面必须包含索引前面的字段 才能
够进行匹配。
而
3
和
4
相比
where
条件的顺序不一样，为什么
4
可以用到索引呢？是因为
mysql
本身就有一层
sql
优化，他会
根据
sql
来识别出来该用哪个索引，我们可以理解为
3
和
4
在
mysql
眼中是等价的。
*/
索引的操作
-
全文索引
全文索引的关键字是
fulltext
#
参数名称
默
认
值
最
小
值
最大
值
作用
1
ft_min_word_len
4
1
3600
MyISAM
引擎表全文索引包含的
最小词长度
2
ft_query_expansion_limit
20
0
1000
MyISAM
引擎表使用
with query
expansion
进行全文搜索的最大
匹配数
3
innodb_ft_min_token_size
3
0
16
InnoDB
引擎表全文索引包含的
最小词长度
4
innodb_ft_max_token_size
84
10
84
InnoDB
引擎表全文索引包含的
最大词长度
全文索引主要用来查找文本中的关键字，而不是直接与索引中的值相比较，它更像是一个搜索引
擎，基于相似度的查询，而不是简单的
where
语句的参数匹配。
用
like + %
就可以实现模糊匹配了，为什么还要全文索引？
like + %
在文本比较少时是合适的，但
是对于大量的文本数据检索，是不可想象的。全文索引在大量的数据面前，能比
like + %
快
N
倍，
速度不是一个数量级，但是全文索引可能存在精度问题。
全文索引的版本、存储引擎、数据类型的支持情况：
MySQL 5.6
以前的版本，只有
MyISAM
存储引擎支持全文索引；
MySQL 5.6
及以后的版本，
MyISAM
和
InnoDB
存储引擎均支持全文索引
;
只有字段的数据类型为
char
、
varchar
、
text
及其系列才可以建全文索引；
在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用
create index
创建
fulltext
索
引，要比先为一张表建立
fulltext
然后再将数据写入的速度快很多；
测试或使用全文索引时，要先看一下自己的
MySQL
版本、存储引擎和数据类型是否支持全文索
引。
MySQL
中的全文索引，有两个变量，最小搜索长度和最大搜索长度，对于长度小于最小搜索长度和大于
最大搜索长度的词语，都不会被索引。通俗点就是说，想对一个词语使用全文索引搜索，那么这个词语
的长度必须在以上两个变量的区间内。这两个的默认值可以使用以下命令查看
:
参数解释
:
操作
show variables like
'%ft%'
;
--
创建表的时候添加全文索引
create table
t_article
(
id int
primary key auto_increment
,
title varchar
(
255
)
,
content varchar
(
1000
)
,
writing_date date
-- ,
-- fulltext (content) --
创建全文检索
)
;
insert into
t_article
values
(
null
,
"Yesterday Once More"
,
"When I was young I
listen to the radio"
,
'2021-10-01'
)
;
insert into
t_article
values
(
null
,
"Right Here Waiting"
,
"Oceans apart, day after
day,and I slowly go insane"
,
'2021-10-02'
)
;
insert into
t_article
values
(
null
,
"My Heart Will Go On"
,
"every night in my
dreams,i see you, i feel you"
,
'2021-10-03'
)
;
insert into
t_article
values
(
null
,
"Everything I Do"
,
"eLook into my eyes,You will
see what you mean to me"
,
'2021-10-04'
)
;
insert into
t_article
values
(
null
,
"Called To Say I Love You"
,
"say love you no new
year's day, to celebrate"
,
'2021-10-05'
)
;
insert into
t_article
values
(
null
,
"Nothing's Gonna Change My Love For You"
,
"if i
had to live my life without you near me"
,
'2021-10-06'
)
;
insert into
t_article
values
(
null
,
"Everybody"
,
"We're gonna bring the flavor show
U how."
,
'2021-10-07'
)
;
--
修改表结构添加全文索引
alter table
t_article
add fulltext
index_content
(
content
)
--
直接添加全文索引
create fulltext index
index_content
on
t_article
(
content
)
;
使用全文索引和常用的模糊匹配使用
like + %
不同，全文索引有自己的语法格式，使用
match
和
against
关键字，格式
:
match
(
col1,col2,...
)
against
(
expr
[
search_modifier
])
select
*
from
t_article
where match
(
content
)
against
(
'yo’); --
没有结果 单词数需要大
于等于
3
select * from t_article where match(content) against('
you
'); --
有结果
索引的操作
-
空间索引
MySQL
在
5.7
之后的版本支持了空间索引，而且支持
OpenGIS
几何数据模型
空间索引是对空间数据类型的字段建立的索引，
MYSQL
中的空间数据类型有
4
种，分别是
GEOMETRY
、
POINT
、
LINESTRING
、
POLYGON
。
MYSQL
使用
SPATIAL
关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引。创建
空间索引的列，必须将其声明为
NOT NULL
。
空间索引一般是用的比较少，了解即可。
类型
含义
说明
Geometry
空间数据
任何一种空间类型
Point
点
坐标值
LineString
线
有一系列点连接而成
Polygon
多边形
由多条线组成
操作
4.
特点
优点：
大大加快数据的查询速度
使用分组和排序进行数据查询时，可以显著减少查询时分组和排序的时间
创建唯一索引，能够保证数据库表中每一行数据的唯一性
在实现数据的参考完整性方面，可以加速表和表之间的连接
缺点：
创建索引和维护索引需要消耗时间，并且随着数据量的增加，时间也会增加
索引需要占据磁盘空间
对数据表中的数据进行增加，修改，删除时，索引也要动态的维护，降低了维护的速度
create table
shop_info
(
id int
primary key auto_increment comment
'id'
,
shop_name varchar
(
64
)
not
null
comment
'
门店名称
'
,
geom_point geometry
not
null
comment
'
经纬度
’,
spatial key geom_index(geom_point)
);
创建索引的原则
更新频繁的列不应设置索引
数据量小的表不要使用索引（毕竟总共
2
页的文档，还要目录吗？）
重复数据多的字段不应设为索引（比如性别，只有男和女，一般来说：重复的数据超过百分之
15
就
不该建索引）
首先应该考虑对
where
和
order by
涉及的列上建立索引
第
14
章
MySQL
事务
1.
概述
数据库存储引擎是数据库底层软件组织，数据库管理系统（
DBMS
）使用数据引擎进行创建、查
询、更新和删除数据。
不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能。现在许多不同的数据库管理系
统都支持多种不同的数据引擎。
MySQL
的核心就是存储引擎。
用户可以根据不同的需求为数据表选择不同的存储引擎
可以使用
SHOW ENGINES
命令 可以查看
Mysql
的所有执行引擎我们 可以看到 默认的执行引擎是
innoDB
支持事务，行级锁定和外键。
什么是事务？
在
MySQL
中的事务（
Transaction
）是由存储引擎实现的，在
MySQL
中，只有
InnoDB
存储引擎才支
持事务。
事务处理可以用来维护数据库的完整性，保证成批的
SQL
语句要么全部执行，要么全部不执行。
事务用来管理
DDL
、
DML
、
DCL
操作，比如
insert,update,delete
语句，默认是自动提交的。
理解事务
在银行转账时，必须保证转账绝对安全，这时需要事务参与
:
假如在第一次
update
之后，出现了意外、异常，没有执行第二次
update
，这时转账是否会出现异常？
2.
操作
MySQL
的事务操作主要有以下三种：
1
、开启事务：
Start Transaction
任何一条
DML
语句
(insert
、
update
、
delete)
执行，标志事务的开启
命令：
BEGIN
或
START TRANSACTION
2
、提交事务：
Commit Transaction
成功的结束，将所有的
DML
语句操作历史记录和底层硬盘数据来一次同步
命令：
COMMIT
3
、回滚事务：
Rollback Transaction
失败的结束，将所有的
DML
语句操作历史记录全部清空
命令：
ROLLBACK
之前的所有
SQL
操作其实也有事务，只是
MySQL
自动帮我们完成的，每执行一条
SQL
时
MySQL
就帮
我们自动提交事务，因此如果想要手动控制事务，则必须关闭
MySQL
的事务自动提交。
在
MySQL
中直接用
SET
来改变
MySQL
的自动提交模式
:
update
account
set
money
=
money
-
200
where
id
=
1
;
update
account
set
money
=
money
+
200
where
id
=
2
;
set autocommit
=
0
禁止自动提交
set autocommit
=
1
开启自动提交
create database if not exists
mydb12_transcation;
use
mydb12_transcation;
--
创建账户表
create table
account
(
id int
primary key
,
--
账户
id
name varchar
(
20
)
,
--
账户名
money double
--
金额
)
;
--
插入数据
insert into
account
values
(
1
,
'zhangsan'
,
1000
)
;
insert into
account
values
(
2
,
'lisi'
,
1000
)
;
3.
隔离级别
Isolate
，顾名思义就是将事务与另一个事务隔离开，为什么要隔离呢？如果一个事务正在操作的数据被
另一个事务修改或删除了，最后的执行结果可能无法达到预期。如果没有隔离性还会导致其他问题。
--
设置
MySQL
的事务为手动提交
(
关闭自动提交
)
select
@@autocommit
;
set autocommit
=
0
;
--
模拟账户转账
--
开启事务
begin
;
update
account
set
money
=
money
-
200
where
name
=
'zhangsan'
;
update
account
set
money
=
money
+
200
where
name
=
'lisi'
;
--
提交事务
commit
;
--
如果转账中的任何一条出现问题，则回滚事务
rollback
;
读未提交
(Read uncommitted)
一个事务可以读取另一个未提交事务的数据，最低级别，任何情况都无法保证
,
会造成脏读。
读已提交
(Read committed)
一个事务要等另一个事务提交后才能读取数据，可避免脏读的发生，会造成不可重复读。
可重复读
(Repeatable read)
就是在开始读取数据（事务开启）时，不再允许修改操作，可避免脏读、不可重复读的发生，但是会造
成幻读。
串行
(Serializable)
是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。但是
这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。
Mysql
的默认隔离级别是
Repeatable read
。
操作
第
15
章
MySQL
日志
1.
概述
在任何一种数据库中，都会有各种各样的日志，记录着数据库工作的方方面面，以帮助数据库管理员追
踪数据库曾经发生过的各种事件。
MySQL
也不例外。
2.
分类
错误日志
错误日志是
MySQL
中最重要的日志之一，它记录了当
mysqld
启动和停止时，以及服务器在运行过程中
发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时，可以首先查看此日志。
该日志是默认开启的 ， 默认存放目录为
mysql
的数据目录
,
默认的日志文件名为
hostname.err
（
hostname
是主机名）。
--
查看隔离级别
show variables like
'%isolation%’;
--
设置隔离级别
/*
set session transaction isolation level
级别字符串
级别字符串：
read uncommitted
、
read committed
、
repeatable read
、
serializable
*/
--
设置
read uncommitted
set session transaction isolation level read uncommitted;
--
设置
read committed
set session transaction isolation level read committed;
--
设置
repeatable read
set session transaction isolation level repeatable read;
--
设置
serializable
set session transaction isolation level serializable;
#
查看日志位置
show variables like
'log_error%'
;
二进制日志
二进制日志（
BINLOG
）记录了所有的
DDL
（数据定义语言）语句和
DML
（数据操纵语言）语句，但是
不包括数据查询语句。此日志对于灾难时的数据恢复起着极其重要的作用，
MySQL
的主从复制， 就是通
过该
binlog
实现的。
二进制日志，
MySQl8.0
默认已经开启，低版本的
MySQL
的需要通过配置文件开启，并配置
MySQL
日志
的格式。
Windows
系统：
my.ini Linux
系统
:my.cnf
#
配置开启
binlog
日志， 日志的文件前缀为
mysqlbin ----->
生成的文件名如
:
mysqlbin.000001,mysqlbin.000002
log_bin
=
mysqlbin
#
配置二进制日志的格式
binlog_format
=
STATEMENT
日志格式
STATEMENT
该日志格式在日志文件中记录的都是
SQL
语句（
statement
），每一条对数据进行修改的
SQL
都会记录在
日志文件中，通过
Mysql
提供的
mysqlbinlog
工具，可以清晰的查看到每条语句的文本。主从复制的时
候，从库（
slave
）会将日志解析为原文本，并在从库重新执行一次。
ROW
该日志格式在日志文件中记录的是每一行的数据变更，而不是记录
SQL
语句。比如，执行
SQL
语句 ：
update tb_book set status='1' ,
如果是
STATEMENT
日志格式，在日志中会记录一行
SQL
文件； 如果是
ROW
，由于是对全表进行更新，也就是每一行记录都会发生变更，
ROW
格式的日志中会记录每一行的
数据变更。
MIXED
混合了
STATEMENT
和
ROW
两种格式。
--
查看
MySQL
是否开启了
binlog
日志
show variables like
'log_bin'
;
--
查看
binlog
日志的格式
show variables like
'binlog_format'
;
--
查看所有日志
show binlog events
;
--
查看最新的日志
show master status
;
--
查询指定的
binlog
日志
show binlog events in
'binlog.000010'
;
select
*
from
mydb1
.emp2
;
select count
(
*
)
from
mydb1
.emp2
;
update
mydb1
.emp2
set
salary
=
8000
;
--
从指定的位置开始
,
查看指定的
Binlog
日志
show binlog events in
'binlog.000010'
from
156
;
--
从指定的位置开始
,
查看指定的
Binlog
日志
,
限制查询的条数
show binlog events in
'binlog.000010'
from
156
limit
2
;
--
从指定的位置开始，带有偏移，查看指定的
Binlog
日志
,
限制查询的条数
show binlog events in
'binlog.000010'
from
666
limit
1
,
2
;
--
清空所有的
binlog
日志文件
reset
master
查询日志
查询日志中记录了客户端的所有操作语句，而二进制日志不包含查询数据的
SQL
语句。
默认情况下， 查询日志是未开启的。如果需要开启查询日志，可以设置以下配置 ：
#
该选项用来开启查询日志 ， 可选值 ：
0
或者
1
；
0
代表关闭，
1
代表开启
general_log
=
1
#
设置日志的文件名 ， 如果没有指定， 默认的文件名为
host_name.log
general_log_file
=
file_name
--
查看
MySQL
是否开启了查询日志
show variables like
'general_log'
;
--
开启查询日志
set global
general_log
=
1
;
select
*
from
mydb1
.emp2
;
select
*
from
mydb6_view
.emp
;
select count
(
*
)
from
mydb1
.emp2
;
select count
(
*
)
from
mydb6_view
.emp
;
update
mydb1
.emp2
set
salary
=
9000
;
慢查询日志
慢查询日志记录了所有执行时间超过参数
long_query_time
设置值并且扫描记录数不小于
min_examined_row_limit
的所有的
SQL
语句的日志。
long_query_time
默认为
10
秒，最小为
0
， 精度
可以到微秒。
#
该参数用来控制慢查询日志是否开启， 可取值：
1
和
0
，
1
代表开启，
0
代表关闭
slow_query_log
=
1
#
该参数用来指定慢查询日志的文件名
slow_query_log_file
=
slow_query
.log
#
该选项用来配置查询的时间限制， 超过这个时间将认为值慢查询， 将需要进行日志记录， 默认
10s
long_query_time
=
10
3.
备份与恢复
3.1 GUI
工具
: navicat
Navicat
备份恢复
navicat
可以有
3
个地方提供备份恢复功能
backup/restore
backup:
打开数据库
-> Backups
（备份）右键
-> New Backup
（新建备份） 备份文件
$HOME/.config/navicat/settings/0/0/MySQL//<
日期时间
>.nb3
restore:
打开数据库
-> Backups
右键
-> Restore Backup from
（还原备份从）
dump/execute
dump:
打开数据库
->
右键点击
-> Dump SQL File -> Structure And Data
execute:
新建空白数据库
->
右键点击
-> Execute SQL File
export/import
export:
选中表
->
右键点击
-> export wizard
支持格式
: Dbase Text CSV HTML EXCEL SQL XML JSON
import:
选中表
->
右键点击
-> import wizard
3.2 mysqldump
语法
参数
示例
mysqldump
-
u username
-
p db_name table1 table2 ...
>
xxx
.sql
备份表结构
mysqldump
-
u root
-
p
数据库名 表
1
表
2
>
地址
-
d
只备份表结构
mysqldump
-
u root
-
p
-
d
数据库名 表
>
c:/kaifamiao/kfm/biao
.sql
不加备注
mysqldump
-
u root
-
p
-
d
--
skip
-
comments
数据库名 表
>
c:/kaifamiao/kfm/biao
.sql
备份多个数据库
指定数据库
mysqldump
-
u root
-
p
-
d
-
B
数据库
1
数据库
2
>
c:/kaifamiao/kfm/ku
.sql
所有数据库
mysqldump
-
u root
-
p
-
d
--
all
-
databases
>
c:/kaifamiao/kfm/suoyouku
.sql
将查询的结果保存为结果集
mysql
-
u root
-
p
-
e
"select * from
数据库
.
表名
"
>
地址
还原数据结构和数据
登录选中数据库之后执行，将数据还原到该数据库
source
地址
;
在服务外面使用
mysql
命令还原
mysql
-
u root
-
p
新数据库名
<
地址
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
