十五、mysql详解系列（基础部分）

一、为什么要使用数据库

• 持久化(persistence)：把数据保存到可掉电式存储设备中以供之后使用。大多数情况下，特别是企业级应用，数据持久化意味着将内存中的数据保存到硬盘上加以”固化”，而持久化的实现过程大多通过各种关系数据库来完成。持久化的主要作用是将内存中的数据存储在关系型数据库中，当然也可以存储在磁盘文件、XML数据文件中。
• 持久化的主要作用是将内存中的数据存储在关系型数据库中，当然也可以存储在磁盘文件、XML数据文件中。

二、数据库与数据库管理系统

1. 数据库的相关概念

2. 数据库与数据库管理系统的关系

数据库管理系统(DBMS)可以管理多个数据库，一般开发人员会针对每一个应用创建一个数据库。为保存应用中实体的数据，一般会在数据库创建多个表，以保存程序中实体用户的数据。
数据库管理系统、数据库和表的关系如图所示：

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）

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平台，命令行操作。 性能较高，支持集群，适应于安全性要求极高的系统，尤其是银行，证券系统的应用。

三、 MySQL介绍

1. 概述

2. MySQL发展史重大事件

MySQL的历史就是整个互联网的发展史。互联网业务从社交领域、电商领域到金融领域的发展，推动着应用对数据库的需求提升，对传统的数据库服务能力提出了挑战。高并发、高性能、高可用、轻资源、易维护、易扩展的需求，促进了MySQL的长足发展。

3. 关于MySQL 8.0

MySQL从5.7版本直接跳跃发布了8.0版本 ，可见这是一个令人兴奋的里程碑版本。MySQL 8版本在功能上做了显著的改进与增强，开发者对MySQL的源代码进行了重构，最突出的一点是多MySQL Optimizer优化器进行了改进。不仅在速度上得到了改善，还为用户带来了更好的性能和更棒的体验。

4. Why choose MySQL?

为什么如此多的厂商要选用MySQL？大概总结的原因主要有以下几点：

1. 开放源代码，使用成本低。
2. 性能卓越，服务稳定。
3. 软件体积小，使用简单，并且易于维护。
4. 历史悠久，社区用户非常活跃，遇到问题可以寻求帮助。
5. 许多互联网公司在用，经过了时间的验证。

5. Oracle vs MySQL

Oracle 更适合大型跨国企业的使用，因为他们对费用不敏感，但是对性能要求以及安全性有更高的要求。
MySQL 由于其体积小、速度快、总体拥有成本低，可处理上千万条记录的大型数据库，尤其是开放源码这一特点，使得很多互联网公司、中小型网站选择了MySQL作为网站数据库（Facebook，Twitter，YouTube，阿里巴巴/蚂蚁金服，去哪儿，美团外卖，腾讯）。

四、 RDBMS 与 非RDBMS

从排名中我们能看出来，关系型数据库绝对是 DBMS 的主流，其中使用最多的 DBMS 分别是 Oracle、MySQL 和 SQL Server。这些都是关系型数据库（RDBMS）。

1. 关系型数据库(RDBMS)

⑴ 实质

• 这种类型的数据库是 最古老 的数据库类型，关系型数据库模型是把复杂的数据结构归结为简单的
  二元关系 （即二维表格形式）。
  

• 关系型数据库以 行(row) 和 列(column) 的形式存储数据，以便于用户理解。这一系列的行和列被称为 表(table) ，一组表组成了一个库(database)。

• 表与表之间的数据记录有关系(relationship)。现实世界中的各种实体以及实体之间的各种联系均用关系模型 来表示。关系型数据库，就是建立在 关系模型 基础上的数据库。

• SQL 就是关系型数据库的查询语言。
  

⑵ 优势

• 复杂查询 可以用SQL语句方便的在一个表以及多个表之间做非常复杂的数据查询。
• 事务支持 使得对于安全性能很高的数据访问要求得以实现。

2. 非关系型数据库(非RDBMS)

⑴ 介绍

非关系型数据库，可看成传统关系型数据库的功能 阉割版本 ，基于键值对存储数据，不需要经过SQL层的解析， 性能非常高 。同时，通过减少不常用的功能，进一步提高性能。

目前基本上大部分主流的非关系型数据库都是免费的。

⑵ 有哪些非关系型数据库

相比于 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等。

⑶ NoSQL的演变

由于 SQL 一直称霸 DBMS，因此许多人在思考是否有一种数据库技术能远离 SQL，于是 NoSQL 诞生了，但是随着发展却发现越来越离不开 SQL。到目前为止 NoSQL 阵营中的 DBMS 都会有实现类似 SQL 的功能。下面是“NoSQL”这个名词在不同时期的诠释，从这些释义的变化中可以看出 NoSQL 功能的演变 ：

NoSQL 对 SQL 做出了很好的补充，比如实际开发中，有很多业务需求，其实并不需要完整的关系型数据库功能，非关系型数据库的功能就足够使用了。这种情况下，使用 性能更高 、 成本更低 的非关系型数据库当然是更明智的选择。比如：日志收集、排行榜、定时器等。

⑷ 小结

NoSQL 的分类很多，即便如此，在 DBMS 排名中，还是 SQL 阵营的比重更大，影响力前 5 的 DBMS 中有4 个是关系型数据库，而排名前 20 的 DBMS 中也有 12 个是关系型数据库。所以说，掌握 SQL 是非常有必要的。整套课程将围绕 SQL 展开。

五、关系型数据库设计规则

1. 表、记录、字段

• E-R（entity-relationship，实体-联系）模型中有三个主要概念是： 实体集 、 属性 、 联系集 。
• 一个实体集（class）对应于数据库中的一个表（table），一个实体（instance）则对应于数据库表中的一行（row），也称为一条记录（record）。一个属性（attribute）对应于数据库表中的一列（column），也称为一个字段（field）。
  

ORM思想 (Object Relational Mapping)体现：
数据库中的一个表 <—> Java或Python中的一个类
表中的一条数据 <—> 类中的一个对象（或实体）
表中的一个列 <----> 类中的一个字段、属性(field)

2. 表的关联关系

• 表与表之间的数据记录有关系(relationship)。现实世界中的各种实体以及实体之间的各种联系均用关系模型来表示。
• 四种：一对一关联、一对多关联、多对多关联、自我引用

⑴ 一对一关联（one-to-one）

• 在实际的开发中应用不多，因为一对一可以创建成一张表。

• 举例：设计 学生表 ：学号、姓名、手机号码、班级、系别、身份证号码、家庭住址、籍贯、紧急联系人、…
  ○ 拆为两个表：两个表的记录是一一对应关系。
  ○ 基础信息表 （常用信息）：学号、姓名、手机号码、班级、系别
  ○ 档案信息表 （不常用信息）：学号、身份证号码、家庭住址、籍贯、紧急联系人、…

• 两种建表原则：
  ○ 外键唯一：主表的主键和从表的外键（唯一），形成主外键关系，外键唯一。
  ○ 外键是主键：主表的主键和从表的主键，形成主外键关系。

⑵ 一对多关系（one-to-many）

• 常见实例场景： 客户表和订单表 ， 分类表和商品表 ， 部门表和员工表 。
• 举例：
  ○ 员工表：编号、姓名、…、所属部门
  ○ 部门表：编号、名称、简介
• 一对多建表原则：在从表(多方)创建一个字段，字段作为外键指向主表(一方)的主键
  
  
  

⑶ 多对多（many-to-many）

要表示多对多关系，必须创建第三个表，该表通常称为 联接表 ，它将多对多关系划分为两个一对多系。将这两个表的主键都插入到第三个表中。

• 举例1：学生-课程
  ○ 学生信息表 ：一行代表一个学生的信息（学号、姓名、手机号码、班级、系别…）
  ○ 课程信息表 ：一行代表一个课程的信息（课程编号、授课老师、简介…）
  ○ 选课信息表 ：一个学生可以选多门课，一门课可以被多个学生选择
  
• 举例2：产品-订单
  “订单”表和“产品”表有一种多对多的关系，这种关系是通过与“订单明细”表建立两个一对多关系来定义的。一个订单可以有多个产品，每个产品可以出现在多个订单中。
  ○ 产品表 ：“产品”表中的每条记录表示一个产品。
  ○ 订单表 ：“订单”表中的每条记录表示一个订单。
  ○ 订单明细表 ：每个产品可以与“订单”表中的多条记录对应，即出现在多个订单中。一个订单可以与“产品”表中的多条记录对应，即包含多个产品。

⑷ 举例3：用户-角色

多对多关系建表原则：需要创建第三张表，中间表中至少两个字段，这两个字段分别作为外键指各自一方的主键。

⑸ 自我引用(Self reference)

六、 MySQL环境搭建

1. MySQL的卸载

⑴ 步骤1：停止MySQL服务

在卸载之前，先停止MySQL8.0的服务。按键盘上的“Ctrl + Alt + Delete”组合键，打开“任务管理器”对话框，可以在“服务”列表找到“MySQL8.0”的服务，如果现在“正在运行”状态，可以右键单击服务，选择“停止”选项停止MySQL8.0的服务，如图所示。

⑵ 步骤2：软件的卸载

1）方式1：通过控制面板方式

卸载MySQL8.0的程序可以和其他桌面应用程序一样直接在“控制面板”选择“卸载程序”，并在程序列表中找到MySQL8.0服务器程序，直接双击卸载即可，如图所示。这种方式删除，数据目录下的数据不会着删除。

2） 方式2：通过360或电脑管家等软件卸载

略

3） 方式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
如果自己单独指定过数据目录，就找到自己的数据目录进行删除即可。

注意：请在卸载前做好数据备份
在操作完以后，需要重启计算机，然后进行安装即可。如果仍然安装失败，需要继续操作如下步骤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的下载、安装、配置

⑴ MySQL的4大版本

⑵ 软件的下载

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。
   

   
   
   
   

⑶ 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”（安装完成），如图所示。

⑷ 配置MySQL8.0

MySQL安装之后，需要对服务器进行配置。具体的配置步骤如下。
步骤1：在上一个小节的最后一步，单击“Next”（下一步）按钮，就可以进入产品配置窗口。

步骤2：单击“Next”（下一步）按钮，进入MySQL服务器类型配置窗口，如图所示。端口号一般选择默认端口号3306。

其中，“Config Type”选项用于设置服务器的类型。单击该选项右侧的下三角按钮，即可查看3个选项，如图所示。

步骤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：结束安装和配置。

⑸ 配置MySQL8.0 环境变量

⑹ MySQL5.7 版本的安装、配置

• 安装
  此版本的安装过程与上述过程除了版本号不同之外，其它环节都是相同的。所以这里省略了MySQL5.7.34版本的安装截图。

以下是在安装mysql8.0的前提下安装mysql5.7的步骤：

访问my5.7

• 配置
  配置环节与MySQL8.0版本确有细微不同。大部分情况下直接选择“Next”即可，不影响整理使用。这里配置MySQL5.7时，重点强调：与前面安装好的MySQL8.0不能使用相同的端口号。

⑺ 安装失败问题

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，下载相应的环境。

• 问题2：卸载重装MySQL失败？
  该问题通常是因为MySQL卸载时，没有完全清除相关信息导致的。
  解决办法是，把以前的安装目录删除。如果之前安装并未单独指定过服务安装目录，则默认安装目录是“C:\Program Files\MySQL”，彻底删除该目录。同时删除MySQL的Data目录，如果之前安装并未单独指定过数据目录，则默认安装目录是“C:\ProgramData\MySQL”，该目录一般为隐藏目录。删除后，重新安装即可。

• 问题3：如何在Windows系统删除之前的未卸载干净的MySQL服务列表？
  操作方法如下，在系统“搜索框”中输入“cmd”，按“Enter”（回车）键确认，弹出命令提示符界面。然后输入“sc delete MySQL服务名”,按“Enter”（回车）键，就能彻底删除残余的MySQL服务了。

3. MySQL的登录

⑴ 服务的启动与停止

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服务名

说明：

1. start和stop后面的服务名应与之前配置时指定的服务名一致。
2. 如果当你输入命令后，提示“拒绝服务”，请以 系统管理员身份 打开命令提示符界面重新尝试。

⑵ 自带客户端的登录与退出

当MySQL服务启动完成后，便可以通过客户端来登录MySQL数据库。注意：确认服务是开启的。

登录方式1：MySQL自带客户端

开始菜单 → 所有程序 → MySQL → MySQL 8.0 Command Line Client

说明：仅限于root用户

登录方式2：windows命令行

• 格式：

  mysql -h 主机名 -P 端口号 -u 用户名 -p密码
  

• 举例：

  mysql -h localhost -P 3306 -u root -p123456 # 这里我设置的root用户的密码是123456
  

  

  注意：
  

  也可以在命令行通过以下方式获取MySQL Server服务版本的信息：

  c:\> mysql -V
  
  c:\> mysql --version
  

  或登录后，通过以下方式查看当前版本信息：

  mysql> select version();
  

  退出登录

  exit 
  或
  quit
  

4. MySQL演示使用

⑴ MySQL的使用演示

1、查看所有的数据库

 show databases;

为什么 Workbench 里面我们只能看到“demo”和“sys”这 2 个数据库呢？

这是因为，Workbench 是图形化的管理工具，主要面向开发人 员，“demo”和“sys”这 2 个数据库已经够用了。如果有特殊需求，比如，需要监控 MySQL 数据库各项性能指标、直接操作 MySQL 数据库系统文件等，可以由 DBA 通过 SQL 语句，查看其它的系统数据库。

2、创建自己的数据库

create database 数据库名; 
#创建kejizhentan数据库，该名称不能与已经存在的数据库重名。 
create database kejizhentan;

3、使用自己的数据库

use 数据库名; 

#使用atguigudb数据库 
use kejizhentan;

说明：如果没有使用use语句，后面针对数据库的操作也没有加“数据名”的限定，那么会报“ERROR 1046 (3D000): No database selected”（没有选择数据库）

使用完use语句之后，如果接下来的SQL都是针对一个数据库操作的，那就不用重复use了，如果要针对另一个数据库操作，那么要重新use。

4、查看某个库的所有表格

show tables from 数据库名;

5、创建新的表格

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,'李四');

报错：

字符集的问题。

8、查看表的创建信息

show create table 表名称\G

#查看student表的详细创建信息 
show create table student\G

上面的结果显示student的表格的默认字符集是“latin1”不支持中文。

9、查看数据库的创建信息

show create database 数据库名\G 

#查看kejizhentan数据库的详细创建信息 
show create database kejizhentan\G

上面的结果显示kejizhentan数据库也不支持中文，字符集默认是latin1。

10、删除表格

drop table 表名称;

#删除学生表 
drop table student;

11、删除数据库

drop database 数据库名;

#删除kejizhentan数据库 
drop database kejizhentan;

⑵ MySQL的编码设置

MySQL5.7中

问题再现：命令行操作sql乱码问题

mysql> INSERT INTO t_stu VALUES(1,'张三','男'); 
ERROR 1366 (HY000): Incorrect string value: '\xD5\xC5\xC8\xFD' for column 'sname' at row 1

问题解决

步骤1：查看编码命令

show variables like 'character_%'; 
show variables like 'collation_%';

步骤2：修改mysql的数据目录下的my.ini配置文件

default-character-set=utf8 #默认字符集 
[mysqld] # 大概在76行左右，在其下添加 
... 
character-set-server=utf8
collation-server=utf8_general_ci

注意：建议修改配置文件使用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社区版作为一个免费开源的产品，和其他类似的软件相比，在功能和易用性上都毫不逊色。
下载地址：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;

6. MySQL目录结构与源码

⑴ 主要目录结构

⑵ 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 的开发人员对话一样，十分有趣。

问题2：mysql命令报“不是内部或外部命令”

如果输入mysql命令报“不是内部或外部命令”，把mysql安装目录的bin目录配置到环境变量path中。如下：


问题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 0728db charset Latin1; 
Query OK, 1 row affected (0.00 sec)

mysql> use 0728db; 
Database changed

七、基本的SELECT语句

点击下载用于学习的sql文件：https://kejizhentan.lanzouj.com/i0JtF06fq5ud下载后解压执行

1. SQL概述

⑴ SQL背景知识

自从 SQL 加入了 TIOBE 编程语言排行榜，就一直保持在 Top 10。

⑵ SQL 分类

2. SQL语言的规则与规范

⑴ SQL大小写规范 （建议遵守）

⑵ 注 释

可以使用如下格式的注释结构

⑶ 命名规则（暂时了解）

举例：

⑷ 数据导入指令

在命令行客户端登录mysql，使用source指令导入

mysql> source d:\mysqldb.sql

3. 基本的SELECT语句

⑴ SELECT…

SELECT 1; #没有任何子句 
SELECT 9/2; #没有任何子句

⑵ SELECT … FROM

• 语法：

  SELECT 标识选择哪些列 
  FROM 标识从哪个表中选择
  

• 选择全部列：

  SELECT * FROM departments;
  

  

• 选择特定的列：

  SELECT department_id, location_id 
  FROM departments;
  

  

⑶ 列的别名

• 重命名一个列

• 便于计算

• 紧跟列名，也可以在列名和别名之间加入关键字AS，别名使用双引号，以便在别名中包含空格或特殊的字符并区分大小写。

• AS 可以省略

• 建议别名简短，见名知意

• 举例

  SELECT last_name AS name, commission_pct comm 
  FROM employees;
  

  

  SELECT last_name "Name", salary*12 "Annual Salary" 
  FROM employees;
  

  

⑷ 去除重复行

默认情况下，查询会返回全部行，包括重复行。

SELECT department_id 
FROM employees;

在SELECT语句中使用关键字DISTINCT去除重复行

SELECT DISTINCT department_id 
FROM employees;

针对于：

SELECT DISTINCT department_id,salary 
FROM employees;

这里有两点需要注意：

1. DISTINCT 需要放到所有列名的前面，如果写成 SELECT salary, DISTINCT department_id FROM employees 会报错。
2. DISTINCT 其实是对后面所有列名的组合进行去重，你能看到最后的结果是 74 条，因为这 74 个部 门id不同，都有 salary 这个属性值。如果你想要看都有哪些不同的部门（department_id），只需 要写 DISTINCT department_id 即可，后面不需要再加其他的列名了。

⑸ 空值参与运算

• 所有运算符或列值遇到null值，运算的结果都为null

  SELECT employee_id,salary,commission_pct,
  12 * salary * (1 + commission_pct) "annual_sal"
  FROM employees;
  

  这里你一定要注意，在 MySQL 里面， 空值不等于空字符串。一个空字符串的长度是 0，而一个空值的长度是空。而且，在 MySQL 里面，空值是占用空间的。

⑹ 着重号

• 错误的
  
• 正确的
  
• 结论
  我们需要保证表中的字段、表名等没有和保留字、数据库系统或常用方法冲突。如果真的相同，请在SQL语句中使用一对``（着重号）引起来。

⑺ 查询常数

SELECT 查询还可以对常数进行查询。对的，就是在 SELECT 查询结果中增加一列固定的常数列。这列的取值是我们指定的，而不是从数据表中动态取出的。
你可能会问为什么我们还要对常数进行查询呢？
SQL 中的 SELECT 语法的确提供了这个功能，一般来说我们只从一个表中查询数据，通常不需要增加一个固定的常数列，但如果我们想整合不同的数据源，用常数列作为这个表的标记，就需要查询常数。
比如说，我们想对 employees 数据表中的员工姓名进行查询，同时增加一列字段 corporation ，这个
字段固定值为“柯基侦探”，可以这样写

SELECT '柯基侦探' as corporation, last_name FROM employees;

4. 显示表结构

使用DESCRIBE 或 DESC 命令，表示表结构。

DESCRIBE employees; 
或
DESC employees;

5. 过滤数据

• 背景：
  
  

八、 运算符

1. 算术运算符

2. 比较运算符

比较运算符用来对表达式左边的操作数和右边的操作数进行比较，比较的结果为真则返回1，比较的结果为假则返回0，其他情况则返回NULL。
比较运算符经常被用来作为SELECT查询语句的条件来使用，返回符合条件的结果记录。

3. 逻辑运算符

4. 位运算符

5. 运算符的优先级

6. 拓展：使用正则表达式查询

九、排序与分页

1. 排序数据

⑴ 排序规则

⑵ 单列排序

SELECT last_name, job_id, department_id, hire_date 
FROM employees 
ORDER BY hire_date ;

SELECT last_name, job_id, department_id, hire_date 
FROM employees 
ORDER BY hire_date DESC ;

SELECT employee_id, last_name, salary*12 annsal 
FROM employees 
ORDER BY annsal;

⑶ 多列排序

SELECT last_name, department_id, salary 
FROM employees 
ORDER BY department_id, salary DESC;

2.分页

⑴ 背景

背景1：查询返回的记录太多了，查看起来很不方便，怎么样能够实现分页查询呢？
背景2：表里有 4 条数据，我们只想要显示第 2、3 条数据怎么办呢？

⑵ 实现规则

⑶ 拓展

十、多表查询

1. 一个案例引发的多表连接

⑴ 案例说明

从多个表中获取数据：

#案例：查询员工的姓名及其部门名称 
SELECT last_name, department_name 
FROM employees, departments;

查询结果：

分析错误情况：

SELECT COUNT(employee_id) FROM employees; 
#输出107行 

SELECT COUNT(department_id)FROM departments; 
#输出27行 

SELECT 107*27 FROM dual;

我们把上述多表查询中出现的问题称为：笛卡尔积的错误。

⑵ 笛卡尔积（或交叉连接）的理解

笛卡尔乘积是一个数学运算。假设我有两个集合 X 和 Y，那么 X 和 Y 的笛卡尔积就是 X 和 Y 的所有可能组合，也就是第一个对象来自于 X，第二个对象来自于 Y 的所有可能。组合的个数即为两个集合中元素个数的乘积数。

SQL92中，笛卡尔积也称为 交叉连接 ，英文是 CROSS JOIN 。在 SQL99 中也是使用 CROSS JOIN表示交叉连接。它的作用就是可以把任意表进行连接，即使这两张表不相关。在MySQL中如下情况会出现笛卡尔积：

#查询员工姓名和所在部门名称 
SELECT last_name,department_name FROM employees,departments; 
SELECT last_name,department_name FROM employees CROSS JOIN departments; 
SELECT last_name,department_name FROM employees INNER JOIN departments;
SELECT last_name,department_name FROM employees JOIN departments;

⑶ 案例分析与问题解决

• 笛卡尔积的错误会在下面条件下产生：
  ○ 省略多个表的连接条件（或关联条件）
  ○ 连接条件（或关联条件）无效
  ○ 所有表中的所有行互相连接

• 为了避免笛卡尔积， 可以在 WHERE 加入有效的连接条件。

• 加入连接条件后，查询语法：

  SELECT table1.column, table2.column 
  FROM table1, table2 
  WHERE table1.column1 = table2.column2; #连接条件
  

  ○ 在 WHERE子句中写入连接条件。

• 正确写法：

  #案例：查询员工的姓名及其部门名称 
  SELECT last_name, department_name 
  FROM employees, departments 
  WHERE employees.department_id = departments.department_id;
  

• 在表中有相同列时，在列名之前加上表名前缀。

2. 多表查询分类讲解

⑴ 分类1：等值连接 vs 非等值连接

等值连接

SELECT employees.employee_id, employees.last_name, 
	employees.department_id, 	departments.department_id, 
	departments.location_id 
FROM employees, departments 
WHERE employees.department_id = departments.department_id;

拓展1：多个连接条件与 AND 操作符

拓展2：区分重复的列名

• 多个表中有相同列时，必须在列名之前加上表名前缀。
• 在不同表中具有相同列名的列可以用 表名 加以区分。

SELECT employees.last_name, departments.department_name,employees.department_id 
FROM employees, departments 
WHERE employees.department_id = departments.department_id;

拓展3：表的别名

• 使用别名可以简化查询。
• 列名前使用表名前缀可以提高查询效率。

SELECT e.employee_id, e.last_name, e.department_id, 
d.department_id, d.location_id 
FROM employees e , departments d 
WHERE e.department_id = d.department_id;

拓展4：连接多个表

总结：连接 n个表,至少需要n-1个连接条件。 比如，连接三个表，至少需要两个连接条件。

练习：查询出公司员工的 last_name,department_name, city

非等值连接

SELECT e.last_name, e.salary, j.grade_level 
FROM employees e, job_grades j 
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;

⑵ 分类2：自连接 vs 非自连接

• 当table1和table2本质上是同一张表，只是用取别名的方式虚拟成两张表以代表不同的意义。然后两个表再进行内连接，外连接等查询。

题目：查询employees表，返回“Xxx works for Xxx”

SELECT CONCAT(worker.last_name ,' works for ' , 
manager.last_name) 
FROM employees worker, employees manager 
WHERE worker.manager_id = manager.employee_id ;

⑶ 分类3：内连接 vs 外连接

除了查询满足条件的记录以外，外连接还可以查询某一方不满足条件的记录。

• 内连接: 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行
• 外连接: 两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行 ，这种连接称为左（或右） 外连接。 没有匹配的行时, 结果表中相应的列为空(NULL)。
• 如果是左外连接，则连接条件中左边的表也称为 主表 ，右边的表称为 从表 。
  如果是右外连接，则连接条件中右边的表也称为 主表 ，左边的表称为 从表 。

1）SQL92：使用(+)创建连接

• 在 SQL92 中采用（+）代表从表所在的位置。即左或右外连接中，(+) 表示哪个是从表。

• Oracle 对 SQL92 支持较好，而 MySQL 则不支持 SQL92 的外连接。

  #左外连接 
  SELECT last_name,department_name 
  FROM employees ,departments 
  WHERE employees.department_id = departments.department_id(+); 
  
  #右外连接 
  SELECT last_name,department_name 
  FROM employees ,departments 
  WHERE employees.department_id(+) = departments.department_id;
  

• 而且在 SQL92 中，只有左外连接和右外连接，没有满（或全）外连接。

3. SQL99语法实现多表查询

⑴ 基本语法

• 使用JOIN…ON子句创建连接的语法结构：

SELECT table1.column, table2.column,table3.column 
FROM table1 
	JOIN table2 ON table1 和 table2 的连接条件 
		JOIN table3 ON table2 和 table3 的连接条件

它的嵌套逻辑类似我们使用的 FOR 循环：

for t1 in table1:
	for t2 in table2: 
		if condition1:
			for t3 in table3: 
				if condition2: 
					output t1 + t2 + t3

SQL99 采用的这种嵌套结构非常清爽、层次性更强、可读性更强，即使再多的表进行连接也都清晰可见。如果你采用 SQL92，可读性就会大打折扣。

• 语法说明：
  ○ 可以使用 ON 子句指定额外的连接条件。
  ○ 这个连接条件是与其它条件分开的。
  ○ ON 子句使语句具有更高的易读性。
  ○ 关键字 JOIN、INNER JOIN、CROSS JOIN 的含义是一样的，都表示内连接

⑵ 内连接(INNER JOIN)的实现

• 语法：

  SELECT 字段列表 
  FROM A表 INNER JOIN B表 
  ON 关联条件 
  WHERE 等其他子句;
  

题目1：

SELECT e.employee_id, e.last_name, e.department_id,
 	d.department_id, d.location_id 
 FROM employees e JOIN departments d 
 ON (e.department_id = d.department_id);

题目2：

SELECT employee_id, city, department_name 
FROM employees e 
JOIN departments d 
ON d.department_id = e.department_id 
JOIN locations l 
ON d.location_id = l.location_id;

⑶ 外连接(OUTER JOIN)的实现

1）左外连接(LEFT OUTER JOIN)

• 语法：

  #实现查询结果是A 
  SELECT 字段列表 
  FROM A表 LEFT JOIN B表 
  ON 关联条件 
  WHERE 等其他子句;
  

• 举例：

  SELECT e.last_name, e.department_id, d.department_name 
  FROM employees e 
  LEFT OUTER JOIN departments d 
  ON (e.department_id = d.department_id) ;
  

  

2）右外连接(RIGHT OUTER JOIN)

• 语法：

  #实现查询结果是B 
  SELECT 字段列表 
  FROM A表 RIGHT JOIN B表 
  ON 关联条件 
  WHERE 等其他子句;
  

• 举例：

  SELECT e.last_name, e.department_id, d.department_name 
  FROM employees e 
  RIGHT OUTER JOIN departments d 
  ON (e.department_id = d.department_id) ;
  

  

3）满外连接(FULL OUTER JOIN)

• 满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。
• SQL99是支持满外连接的。使用FULL JOIN 或 FULL OUTER JOIN来实现。
• 需要注意的是，MySQL不支持FULL JOIN，但是可以用 LEFT JOIN UNION RIGHT join代替。

4. UNION的使用

合并查询结果 利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。合并时，两个表对应的列数和数据类型必须相同，并且相互对应。各个SELECT语句之间使用UNION或UNION ALL关键字分隔。

语法格式：

SELECT column,... FROM table1 
UNION [ALL] 
SELECT column,... FROM table2

UNION操作符

UNION 操作符返回两个查询的结果集的并集，去除重复记录。

UNION ALL操作符

UNION ALL操作符返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重。

举例：查询部门编号>90或邮箱包含a的员工信息

#方式1 
SELECT * FROM employees WHERE email LIKE '%a%' OR department_id>90;

#方式2 
SELECT * FROM employees WHERE email LIKE '%a%' 
UNION 
SELECT * FROM employees WHERE department_id>90;

举例：查询中国用户中男性的信息以及美国用户中年男性的用户信息

SELECT id,cname FROM t_chinamale WHERE csex='男' 
UNION ALL 
SELECT id,tname FROM t_usmale WHERE tGender='male';

5. 7种SQL JOINS的实现

⑴ 代码实现

#中图：内连接 A∩B 
SELECT employee_id,last_name,department_name 
FROM employees e JOIN departments d 
ON e.`department_id` = d.`department_id`;

#左上图：左外连接 
SELECT employee_id,last_name,department_name 
FROM employees e LEFT JOIN departments d 
ON e.`department_id` = d.`department_id`;

#右上图：右外连接 
SELECT employee_id,last_name,department_name 
FROM employees e RIGHT JOIN departments d 
ON e.`department_id` = d.`department_id`;

#左中图：A - A∩B 
SELECT employee_id,last_name,department_name 
FROM employees e LEFT JOIN departments d 
ON e.`department_id` = d.`department_id` 
WHERE d.`department_id` IS NULL

#右中图：B-A∩B 
SELECT employee_id,last_name,department_name 
FROM employees e RIGHT JOIN departments d 
ON e.`department_id` = d.`department_id` 
WHERE e.`department_id` IS NULL

#左下图：满外连接 
# 左中图 + 右上图 A∪B 
SELECT employee_id,last_name,department_name 
FROM employees e LEFT JOIN departments d 
ON e.`department_id` = d.`department_id` 
WHERE d.`department_id` IS NULL 
UNION ALL #没有去重操作，效率高 
SELECT employee_id,last_name,department_name 
FROM employees e RIGHT JOIN departments d 
ON e.`department_id` = d.`department_id`;

#右下图 
#左中图 + 右中图 A ∪B- A∩B 或者 (A - A∩B) ∪ （B - A∩B） 
SELECT employee_id,last_name,department_name 
FROM employees e LEFT JOIN departments d 
ON e.`department_id` = d.`department_id` 
WHERE d.`department_id` IS NULL 
UNION ALL 
SELECT employee_id,last_name,department_name 
FROM employees e RIGHT JOIN departments d 
ON e.`department_id` = d.`department_id` 
WHERE e.`department_id` IS NULL	

⑵ 语法格式小结

• 左中图

  #实现A - A∩B 
  select 字段列表
  from A表 left join B表 
  on 关联条件 
  where 从表关联字段 is null and 等其他子句;
  

• 右中图

  #实现B - A∩B 
  select 字段列表
  from A表 right join B表 
  on 关联条件 
  where 从表关联字段 is null and 等其他子句;
  

• 左下图

  #实现查询结果是A∪B 
  #用左外的A，union 右外的B 
  select 字段列表
  from A表 left join B表 
  on 关联条件 
  where 等其他子句 
  
  union 
  
  select 字段列表
  from A表 right join B表 
  on 关联条件 
  where 等其他子句;
  

• 右下图

  #实现A∪B - A∩B 或 (A - A∩B) ∪ （B - A∩B） 
  #使用左外的 (A - A∩B) union 右外的（B - A∩B） 
  select 字段列表
  from A表 left join B表 
  on 关联条件 
  where 从表关联字段 is null and 等其他子句 
  
  union 
  
  select 字段列表
  from A表 right join B表 
  on 关联条件 
  where 从表关联字段 is null and 等其他子句
  

6. SQL99语法新特性

⑴ 自然连接

SQL99 在 SQL92 的基础上提供了一些特殊语法，比如 NATURAL JOIN 用来表示自然连接。我们可以把自然连接理解为 SQL92 中的等值连接。它会帮你自动查询两张连接表中 所有相同的字段 ，然后进行 等值 连接 。

在SQL92标准中：

SELECT employee_id,last_name,department_name 
FROM employees e JOIN departments d 
ON e.`department_id` = d.`department_id` 
AND e.`manager_id` = d.`manager_id`;

在 SQL99 中你可以写成：

SELECT employee_id,last_name,department_name 
FROM employees e NATURAL JOIN departments d;

⑵ USING连接

当我们进行连接的时候，SQL99还支持使用 USING 指定数据表里的 同名字段 进行等值连接。但是只能配合JOIN一起使用。比如：

SELECT employee_id,last_name,department_name 
FROM employees e JOIN departments d 
USING (department_id);

你能看出与自然连接 NATURAL JOIN 不同的是，USING 指定了具体的相同的字段名称，你需要在 USING的括号 () 中填入要指定的同名字段。同时使用 JOIN…USING 可以简化 JOIN ON 的等值连接。它与下面的 SQL 查询结果是相同的：

SELECT employee_id,last_name,department_name 
FROM employees e ,departments d 
WHERE e.department_id = d.department_id;

7. 章节小结

表连接的约束条件可以有三种方式：WHERE, ON, USING

• WHERE：适用于所有关联查询
• ON ：只能和JOIN一起使用，只能写关联条件。虽然关联条件可以并到WHERE中和其他条件一起写，但分开写可读性更好。
• USING：只能和JOIN一起使用，而且要求两个关联字段在关联表中名称一致，而且只能表示关联字段值相等

#关联条件 
#把关联条件写在where后面 
SELECT last_name,department_name 
FROM employees,departments 
WHERE employees.department_id = departments.department_id;

#把关联条件写在on后面，只能和JOIN一起使用 
SELECT last_name,department_name 
FROM employees INNER JOIN departments 
ON employees.department_id = departments.department_id;

SELECT last_name,department_name 
FROM employees CROSS JOIN departments 
ON employees.department_id = departments.department_id;

SELECT last_name,department_name 
FROM employees JOIN departments 
ON employees.department_id = departments.department_id;

#把关联字段写在using()中，只能和JOIN一起使用 
#而且两个表中的关联字段必须名称相同，而且只能表示= 
#查询员工姓名与基本工资 
SELECT last_name,job_title 
FROM employees INNER JOIN jobs USING(job_id);

#n张表关联，需要n-1个关联条件 
#查询员工姓名，基本工资，部门名称 
SELECT last_name,job_title,department_name FROM employees,departments,jobs 
WHERE employees.department_id = departments.department_id 
AND employees.job_id = jobs.job_id;

SELECT last_name,job_title,department_name 
FROM employees INNER JOIN departments INNER JOIN jobs 
ON employees.department_id = departments.department_id 
AND employees.job_id = jobs.job_id;

注意：
我们要 控制连接表的数量 。多表连接就相当于嵌套 for 循环一样，非常消耗资源，会让 SQL 查询性能下降得很严重，因此不要连接不必要的表。在许多 DBMS 中，也都会有最大连接表的限制。

8. 附录：常用的 SQL 标准有哪些

十一、单行函数

1. 函数的理解

⑴ 什么是函数

函数在计算机语言的使用中贯穿始终，函数的作用是什么呢？它可以把我们经常使用的代码封装起来，需要的时候直接调用即可。这样既 提高了代码效率 ，又 提高了可维护性 。在 SQL 中我们也可以使用函数对检索出来的数据进行函数操作。使用这些函数，可以极大地 提高用户对数据库的管理效率 。

从函数定义的角度出发，我们可以将函数分成 内置函数 和 自定义函数 。在 SQL 语言中，同样也包括了内置函数和自定义函数。内置函数是系统内置的通用函数，而自定义函数是我们根据自己的需要编写的，本章及下一章讲解的是 SQL 的内置函数。

⑵ 不同DBMS函数的差异

我们在使用 SQL 语言的时候，不是直接和这门语言打交道，而是通过它使用不同的数据库软件，即DBMS。DBMS 之间的差异性很大，远大于同一个语言不同版本之间的差异。 实际上，只有很少的函数是被 DBMS 同时支持的。比如，大多数 DBMS 使用（||）或者（+）来做拼接符，而在 MySQL 中的字符串拼接函数为concat()。大部分 DBMS 会有自己特定的函数，这就意味着采用 SQL 函数的代码可移植性是很差的，因此在使用函数的时候需要特别注意。

⑶ MySQL的内置函数及分类

MySQL提供了丰富的内置函数，这些函数使得数据的维护与管理更加方便，能够更好地提供数据的分析与统计功能，在一定程度上提高了开发人员进行数据分析与统计的效率。
MySQL提供的内置函数从 实现的功能角度 可以分为数值函数、字符串函数、日期和时间函数、流程控制函数、加密与解密函数、获取MySQL信息函数、聚合函数等。这里，我将这些丰富的内置函数再分为两类： 单行函数 、 聚合函数（或分组函数） 。

两种SQL函数

单行函数

• 操作数据对象
• 接受参数返回一个结果
• 只对一行进行变换
• 每行返回一个结果
• 可以嵌套
• 参数可以是一列或一个值

2. 数值函数

⑴ 基本函数

举例：

SELECT 
ABS(-123),ABS(32),SIGN(-23),SIGN(43),PI(),CEIL(32.32),CEILING(-43.23),FLOOR(32.32), 
FLOOR(-43.23),MOD(12,5) FROM DUAL;

SELECT RAND(),RAND(),RAND(10),RAND(10),RAND(-1),RAND(-1) 
FROM DUAL;

SELECT 
ROUND(12.33),ROUND(12.343,2),ROUND(12.324,-1),TRUNCATE(12.66,1),TRUNCATE(12.66,-1) 
FROM DUAL;

⑵ 角度与弧度互换函数

SELECT RADIANS(30),RADIANS(60),RADIANS(90),DEGREES(2*PI()),DEGREES(RADIANS(90)) 
FROM DUAL;

⑶ 三角函数

举例：

ATAN2(M,N)函数返回两个参数的反正切值。 与ATAN(X)函数相比，ATAN2(M,N)需要两个参数，例如有两个点point(x1,y1)和point(x2,y2)，使用ATAN(X)函数计算反正切值为ATAN((y2-y1)/(x2-x1))，使用ATAN2(M,N)计算反正切值则为ATAN2(y2-y1,x2-x1)。由使用方式可以看出，当x2-x1等于0时，ATAN(X)函数会报错，而ATAN2(M,N)函数则仍然可以计算。

ATAN2(M,N)函数的使用示例如下：

SELECT 
SIN(RADIANS(30)),DEGREES(ASIN(1)),TAN(RADIANS(45)),DEGREES(ATAN(1)),DEGREES(ATAN2(1,1) )
FROM DUAL;

⑷ 指数与对数

⑸ 进制间的转换

3. 字符串函数

4. 日期和时间函数

⑴ 获取日期、时间

举例：

SELECT 
CURDATE(),CURTIME(),NOW(),SYSDATE()+0,UTC_DATE(),UTC_DATE()+0,UTC_TIME(),UTC_TIME()+0 
FROM DUAL;

⑵ 日期与时间戳的转换

举例：

⑶ 获取月份、星期、星期数、天数等函数

举例：

SELECT YEAR(CURDATE()),MONTH(CURDATE()),DAY(CURDATE()), 
HOUR(CURTIME()),MINUTE(NOW()),SECOND(SYSDATE()) 
FROM DUAL;

SELECT MONTHNAME('2021-10-26'),DAYNAME('2021-10-26'),WEEKDAY('2021-10-26'), 
QUARTER(CURDATE()),WEEK(CURDATE()),DAYOFYEAR(NOW()), 
DAYOFMONTH(NOW()),DAYOFWEEK(NOW()) 
FROM DUAL;

⑷ 日期的操作函数

EXTRACT(type FROM date)函数中type的取值与含义：

SELECT EXTRACT(MINUTE FROM NOW()),EXTRACT( WEEK FROM NOW()), 
EXTRACT( QUARTER FROM NOW()),EXTRACT( MINUTE_SECOND FROM NOW()) 
FROM DUAL;

⑸ 时间和秒钟转换的函数

举例：

⑹ 计算日期和时间的函数

第1组：

上述函数中type的取值：

举例：

SELECT DATE_ADD(NOW(), INTERVAL 1 DAY) AS col1,DATE_ADD('2021-10-21 23:32:12',INTERVAL 1 SECOND) AS col2, 
ADDDATE('2021-10-21 23:32:12',INTERVAL 1 SECOND) AS col3, 
DATE_ADD('2021-10-21 23:32:12',INTERVAL '1_1' MINUTE_SECOND) AS col4, 
DATE_ADD(NOW(), INTERVAL -1 YEAR) AS col5, #可以是负数 
DATE_ADD(NOW(), INTERVAL '1_1' YEAR_MONTH) AS col6 #需要单引号 
FROM DUAL;

SELECT DATE_SUB('2021-01-21',INTERVAL 31 DAY) AS col1, 
SUBDATE('2021-01-21',INTERVAL 31 DAY) AS col2, 
DATE_SUB('2021-01-21 02:01:01',INTERVAL '1 1' DAY_HOUR) AS col3 
FROM DUAL;

第2组：

举例：

SELECT 
ADDTIME(NOW(),20),SUBTIME(NOW(),30),SUBTIME(NOW(),'1:1:3'),DATEDIFF(NOW(),'2021-10- 01'), 
TIMEDIFF(NOW(),'2021-10-25 22:10:10'),FROM_DAYS(366),TO_DAYS('0000-12-25'), 
LAST_DAY(NOW()),MAKEDATE(YEAR(NOW()),12),MAKETIME(10,21,23),PERIOD_ADD(20200101010101, 10) 
FROM DUAL;

举例：查询 7 天内的新增用户数有多少？

SELECT COUNT(*) as num FROM new_user WHERE TO_DAYS(NOW())-TO_DAYS(regist_time)<=7

⑺ 日期的格式化与解析

上述 非GET_FORMAT 函数中fmt参数常用的格式符：

GET_FORMAT函数中date_type和format_type参数取值如下：

举例：

5. 流程控制函数

流程处理函数可以根据不同的条件，执行不同的处理流程，可以在SQL语句中实现不同的条件选择。MySQL中的流程处理函数主要包括IF()、IFNULL()和CASE()函数。

6. 加密与解密函数

加密与解密函数主要用于对数据库中的数据进行加密和解密处理，以防止数据被他人窃取。这些函数在保证数据库安全时非常有用。

可以看到，ENCODE(value,password_seed)函数DECODE(value,password_seed)函数互为反函数。
举例：

7. MySQL信息函数

MySQL中内置了一些可以查询MySQL信息的函数，这些函数主要用于帮助数据库开发或运维人员更好地对数据库进行维护工作。

举例：

8. 其他函数

MySQL中有些函数无法对其进行具体的分类，但是这些函数在MySQL的开发和运维过程中也是不容忽视的。

十二、聚合函数

我们上一章讲到了 SQL 单行函数。实际上 SQL 函数还有一类，叫做聚合（或聚集、分组）函数，它是对一组数据进行汇总的函数，输入的是一组数据的集合，输出的是单个值。

1. 聚合函数介绍

• 什么是聚合函数
  聚合函数作用于一组数据，并对一组数据返回一个值。
  

• 聚合函数类型
  ○ AVG()
  ○ SUM()
  ○ MAX()
  ○ MIN()
  ○ COUNT()

• 聚合函数语法
  

• 聚合函数不能嵌套调用。比如不能出现类似“AVG(SUM(字段名称))”形式的调用。

⑴ AVG和SUM函数

可以对数值型数据使用AVG 和 SUM 函数。

SELECT AVG(salary), MAX(salary),MIN(salary), SUM(salary) 
FROM employees 
WHERE job_id LIKE '%REP%';

⑵ MIN和MAX函数

可以对任意数据类型的数据使用 MIN 和 MAX 函数。

SELECT MIN(hire_date), MAX(hire_date) 
FROM employees;

⑶ COUNT函数

• COUNT(*)返回表中记录总数，适用于任意数据类型。

  SELECT COUNT(*) 
  FROM employees 
  WHERE department_id = 50;
  

  

• COUNT(expr) 返回expr不为空的记录总数。

  SELECT COUNT(commission_pct) 
  FROM employees 
  WHERE department_id = 50;
  

  
  

2. GROUP BY

⑴ 基本使用

可以使用GROUP BY子句将表中的数据分成若干组

SELECT column, group_function(column) 
FROM table 
[WHERE condition] 
[GROUP BY group_by_expression] 
[ORDER BY column];

在SELECT列表中所有未包含在组函数中的列都应该包含在 GROUP BY子句中

SELECT department_id, AVG(salary) 
FROM employees 
GROUP BY department_id ;

包含在 GROUP BY 子句中的列不必包含在SELECT 列表中

SELECT AVG(salary) 
FROM employees 
GROUP BY department_id ;

⑵ 使用多个列分组

SELECT department_id dept_id, job_id, SUM(salary) 
FROM employees 
GROUP BY department_id, job_id ;

⑶ GROUP BY中使用WITH ROLLUP

使用 WITH ROLLUP 关键字之后，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所有记录的总和，即统计记录数量。

SELECT department_id,AVG(salary) 
FROM employees 
WHERE department_id > 80 
GROUP BY department_id WITH ROLLUP;

3. HAVING

⑴ 基本使用

过滤分组：HAVING子句

1. 行已经被分组。
2. 使用了聚合函数。
3. 满足HAVING 子句中条件的分组将被显示。
4. HAVING 不能单独使用，必须要跟 GROUP BY 一起使用。

SELECT department_id, MAX(salary) 
FROM employees 
GROUP BY department_id 
HAVING MAX(salary)>10000 ;

• 非法使用聚合函数 ： 不能在 WHERE 子句中使用聚合函数。如下：

  SELECT department_id, AVG(salary) 
  FROM employees 
  WHERE AVG(salary) > 8000 
  GROUP BY department_id;
  

  

⑵ WHERE和HAVING的对比

区别1：WHERE 可以直接使用表中的字段作为筛选条件，但不能使用分组中的计算函数作为筛选条件；HAVING 必须要与 GROUP BY 配合使用，可以把分组计算的函数和分组字段作为筛选条件。

这决定了，在需要对数据进行分组统计的时候，HAVING 可以完成 WHERE 不能完成的任务。这是因为，在查询语法结构中，WHERE 在 GROUP BY 之前，所以无法对分组结果进行筛选。HAVING 在GROUP BY 之后，可以使用分组字段和分组中的计算函数，对分组的结果集进行筛选，这个功能是WHERE 无法完成的。另外，WHERE排除的记录不再包括在分组中。
区别2：如果需要通过连接从关联表中获取需要的数据，WHERE 是先筛选后连接，而 HAVING 是先连接后筛选。 这一点，就决定了在关联查询中，WHERE 比 HAVING 更高效。因为 WHERE 可以先筛选，用一个筛选后的较小数据集和关联表进行连接，这样占用的资源比较少，执行效率也比较高。HAVING 则需要先把结果集准备好，也就是用未被筛选的数据集进行关联，然后对这个大的数据集进行筛选，这样占用的资源就比较多，执行效率也较低。

小结如下：

开发中的选择：
WHERE 和 HAVING 也不是互相排斥的，我们可以在一个查询里面同时使用 WHERE 和 HAVING。包含分组统计函数的条件用 HAVING，普通条件用 WHERE。这样，我们就既利用了 WHERE 条件的高效快速，又发挥了 HAVING 可以使用包含分组统计函数的查询条件的优点。当数据量特别大的时候，运行效率会有很大的差别。

4. SELECT的执行过程

⑴ 查询的结构

#方式1： 
SELECT ...,....,... 
FROM ...,...,.... 
WHERE 多表的连接条件 
AND 不包含组函数的过滤条件 
GROUP BY ...,... 
HAVING 包含组函数的过滤条件 
ORDER BY ... ASC/DESC 
LIMIT ...,... 

#方式2： 
SELECT ...,....,... 
FROM ... JOIN ... 
ON 多表的连接条件 
JOIN ... 
ON ... 
WHERE 不包含组函数的过滤条件 
AND/OR 不包含组函数的过滤条件 
GROUP BY ...,... 
HAVING 包含组函数的过滤条件 
ORDER BY ... ASC/DESC 
LIMIT ...,... 

#其中： 
#（1）from：从哪些表中筛选 
#（2）on：关联多表查询时，去除笛卡尔积 
#（3）where：从表中筛选的条件 
#（4）group by：分组依据 
#（5）having：在统计结果中再次筛选 
#（6）order by：排序 
#（7）limit：分页

⑵ SELECT执行顺序

你需要记住 SELECT 查询时的两个顺序：

1. 关键字的顺序是不能颠倒的：

SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT...

2.SELECT 语句的执行顺序（在 MySQL 和 Oracle 中，SELECT 执行顺序基本相同）：

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT

比如你写了一个 SQL 语句，那么它的关键字顺序和执行顺序是下面这样的：

SELECT DISTINCT player_id, player_name, count(*) as num # 顺序 5 
FROM player JOIN team ON player.team_id = team.team_id # 顺序 1 
WHERE height > 1.80 # 顺序 2 
GROUP BY player.team_id # 顺序 3 
HAVING num > 2 # 顺序 4 
ORDER BY num DESC # 顺序 6 
LIMIT 2 # 顺序 7

在 SELECT 语句执行这些步骤的时候，每个步骤都会产生一个 虚拟表 ，然后将这个虚拟表传入下一个步骤中作为输入。需要注意的是，这些步骤隐含在 SQL 的执行过程中，对于我们来说是不可见的。

⑶ SQL 的执行原理

SELECT 是先执行 FROM 这一步的。在这个阶段，如果是多张表联查，还会经历下面的几个步骤：

1. 首先先通过 CROSS JOIN 求笛卡尔积，相当于得到虚拟表 vt（virtual table）1-1；
2. 通过 ON 进行筛选，在虚拟表 vt1-1 的基础上进行筛选，得到虚拟表 vt1-2；
3. 添加外部行。如果我们使用的是左连接、右链接或者全连接，就会涉及到外部行，也就是在虚拟表 vt1-2 的基础上增加外部行，得到虚拟表 vt1-3。

当然如果我们操作的是两张以上的表，还会重复上面的步骤，直到所有表都被处理完为止。这个过程得到是我们的原始数据。

当我们拿到了查询数据表的原始数据，也就是最终的虚拟表 vt1 ，就可以在此基础上再进行 WHERE 阶 段 。在这个阶段中，会根据 vt1 表的结果进行筛选过滤，得到虚拟表 vt2 。

然后进入第三步和第四步，也就是 GROUP 和 HAVING 阶段 。在这个阶段中，实际上是在虚拟表 vt2 的基础上进行分组和分组过滤，得到中间的虚拟表 vt3 和vt4。

当我们完成了条件筛选部分之后，就可以筛选表中提取的字段，也就是进入到 SELECT 和 DISTINCT 阶段 。

首先在 SELECT 阶段会提取想要的字段，然后在 DISTINCT 阶段过滤掉重复的行，分别得到中间的虚拟表vt5-1 和vt5-2 。

当我们提取了想要的字段数据之后，就可以按照指定的字段进行排序，也就是 ORDER BY 阶段 ，得到虚拟表 vt6 。

最后在 vt6 的基础上，取出指定行的记录，也就是LIMIT 阶段，得到最终的结果，对应的是虚拟表vt7 。

当然我们在写 SELECT 语句的时候，不一定存在所有的关键字，相应的阶段就会省略。

同时因为 SQL 是一门类似英语的结构化查询语言，所以我们在写 SELECT 语句的时候，还要注意相应的关键字顺序，所谓底层运行的原理，就是我们刚才讲到的执行顺序。

十三、子查询

子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从MySQL 4.1开始引入。

SQL 中子查询的使用大大增强了 SELECT 查询的能力，因为很多时候查询需要从结果集中获取数据，或者需要从同一个表中先计算得出一个数据结果，然后与这个数据结果（可能是某个标量，也可能是某个集合）进行比较。

1. 需求分析与问题解决

⑴ 实际问题

现有解决方式：

#方式一： 
SELECT salary 
FROM employees 
WHERE last_name = 'Abel'; 

SELECT last_name,salary 
FROM employees 
WHERE salary > 11000;


#方式二：自连接 
SELECT e2.last_name,e2.salary 
FROM employees e1,employees e2 
WHERE e1.last_name = 'Abel' 
AND e1.`salary` < e2.`salary`

#方式三：子查询 
SELECT last_name,salary 
FROM employees 
WHERE salary > ( 
		SELECT salary 
		FROM employees 
		WHERE last_name = 'Abel' 
		);

⑵ 子查询的基本使用

• 子查询的基本语法结构：
  
• 子查询（内查询）在主查询之前一次执行完成。
• 子查询的结果被主查询（外查询）使用 。
• 注意事项
  ○ 子查询要包含在括号内
  ○ 将子查询放在比较条件的右侧
  ○ 单行操作符对应单行子查询，多行操作符对应多行子查询

⑶ 子查询的分类

分类方式1：
我们按内查询的结果返回一条还是多条记录，将子查询分为 单行子查询 、 多行子查询 。

• 单行子查询
  
• 多行子查询
  

分类方式2：

我们按内查询是否被执行多次，将子查询划分为相关(或关联)子查询和 不相关(或非关联)子查询 。

子查询从数据表中查询了数据结果，如果这个数据结果只执行一次，然后这个数据结果作为主查询的条件进行执行，那么这样的子查询叫做不相关子查询。

同样，如果子查询需要执行多次，即采用循环的方式，先从外部查询开始，每次都传入子查询进行查询，然后再将结果反馈给外部，这种嵌套的执行方式就称为相关子查询。

2. 单行子查询

⑴ 单行比较操作符

⑵ 代码示例

题目：查询工资大于149号员工工资的员工的信息

题目：返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资

SELECT last_name, job_id, salary 
FROM employees 
WHERE job_id = 
				(SELECT job_id 
				FROM employees 
				WHERE employee_id = 141) 
AND salary > 
				(SELECT salary 
				FROM employees 
				WHERE employee_id = 143);

题目：返回公司工资最少的员工的last_name,job_id和salary

SELECT last_name, job_id, salary 
FROM employees 
WHERE salary = 
			(SELECT MIN(salary) 
			FROM employees);

题目：查询与141号或174号员工的manager_id和department_id相同的其他员工的employee_id， manager_id，department_id

实现方式1：不成对比较

SELECT employee_id, manager_id, department_id 
FROM employees 
WHERE manager_id IN 
		(SELECT manager_id 
				FROM employees 
				WHERE employee_id IN (174,141)) 
AND department_id IN 
		(SELECT department_id 
			FROM employees 
			WHERE employee_id IN (174,141)) 
AND employee_id NOT IN(174,141);

实现方式2：成对比较

SELECT employee_id, manager_id, department_id 
FROM employees 
WHERE (manager_id, department_id) IN 
			(SELECT manager_id, department_id 
			FROM employees 
			WHERE employee_id IN (141,174)) 
AND employee_id NOT IN (141,174);

⑶ HAVING 中的子查询

• 首先执行子查询。
• 向主查询中的HAVING 子句返回结果。

题目：查询最低工资大于50号部门最低工资的部门id和其最低工资

SELECT department_id, MIN(salary) 
FROM employees 
GROUP BY department_id 
HAVING MIN(salary) > 
		(SELECT MIN(salary) 
		FROM employees 
		WHERE department_id = 50);

⑷ CASE中的子查询

在CASE表达式中使用单列子查询：

题目：显式员工的employee_id,last_name和location。其中，若员工department_id与location_id为1800 的department_id相同，则location为’Canada’，其余则为’USA’。

SELECT employee_id, last_name, 
	(CASE department_id 
		WHEN 
			(SELECT department_id FROM departments 
			WHERE location_id = 1800) 
			THEN 'Canada' ELSE 'USA' END) location 
FROM employees;

⑸ 子查询中的空值问题

SELECT last_name, job_id 
FROM employees 
WHERE job_id = 
		(SELECT job_id 
		FROM employees 
		WHERE last_name = 'Haas');

⑹ 非法使用子查询

SELECT employee_id, last_name 
FROM employees 
WHERE salary = 
				(SELECT MIN(salary) 
				FROM employees 
				GROUP BY department_id);

3. 多行子查询

• 也称为集合比较子查询
• 内查询返回多行
• 使用多行比较操作符

⑴ 多行比较操作符

⑵ 代码示例

题目：返回其它job_id中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id 以及salary

题目：返回其它job_id中比job_id为‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id以及salary

题目：查询平均工资最低的部门id

#方式1： 
SELECT department_id 
FROM employees 
GROUP BY department_id 
HAVING AVG(salary) = ( 
			SELECT MIN(avg_sal) 
			FROM (
				SELECT AVG(salary) avg_sal 
				FROM employees 
				GROUP BY department_id 
				) dept_avg_sal 
			)

#方式2： 
SELECT department_id 
FROM employees 
GROUP BY department_id 
HAVING AVG(salary) <= ALL ( 
			SELECT AVG(salary) avg_sal 
			FROM employees 
			GROUP BY department_id 
)

⑶ 空值问题

SELECT last_name 
FROM employees 
WHERE employee_id NOT IN ( 
			SELECT manager_id 
			FROM employees 
			);

4. 相关子查询

⑴ 相关子查询执行流程

如果子查询的执行依赖于外部查询，通常情况下都是因为子查询中的表用到了外部的表，并进行了条件关联，因此每执行一次外部查询，子查询都要重新计算一次，这样的子查询就称之为 关联子查询 。

相关子查询按照一行接一行的顺序执行，主查询的每一行都执行一次子查询。

说明：子查询中使用主查询中的列

⑵ 代码示例

题目：查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id

方式一：相关子查询

方式二：在 FROM 中使用子查询

SELECT last_name,salary,e1.department_id 
FROM employees e1,(SELECT department_id,AVG(salary) dept_avg_sal FROM employees GROUP BY department_id) e2 
WHERE e1.`department_id` = e2.department_id 
AND e2.dept_avg_sal < e1.`salary`;

在ORDER BY 中使用子查询：

题目：查询员工的id,salary,按照department_name 排序

SELECT employee_id,salary 
FROM employees e 
ORDER BY ( 
		SELECT department_name 
		FROM departments d 
		WHERE e.`department_id` = d.`department_id`
		);

题目：若employees表中employee_id与job_history表中employee_id相同的数目不小于2，输出这些相同id的员工的employee_id,last_name和其job_id

SELECT e.employee_id, last_name,e.job_id 
FROM employees e 
WHERE 2 <= (SELECT COUNT(*) 
			FROM job_history 
			WHERE employee_id = e.employee_id);

⑶ EXISTS 与 NOT EXISTS关键字

• 关联子查询通常也会和 EXISTS操作符一起来使用，用来检查在子查询中是否存在满足条件的行。
• 如果在子查询中不存在满足条件的行：
  ○ 条件返回 FALSE
  ○ 继续在子查询中查找
• 如果在子查询中存在满足条件的行：
  ○ 不在子查询中继续查找
  ○ 条件返回 TRUE
• NOT EXISTS关键字表示如果不存在某种条件，则返回TRUE，否则返回FALSE。

题目：查询公司管理者的employee_id，last_name，job_id，department_id信息

方式一：

SELECT employee_id, last_name, job_id, department_id 
FROM employees e1 
WHERE EXISTS ( SELECT * 
				FROM employees e2 
				WHERE e2.manager_id = e1.employee_id);

方式二：自连接

SELECT DISTINCT e1.employee_id, e1.last_name, e1.job_id, e1.department_id 
FROM employees e1 JOIN employees e2 
WHERE e1.employee_id = e2.manager_id;

方式三：

SELECT employee_id,last_name,job_id,department_id 
FROM employees 
WHERE employee_id IN ( 
				SELECT DISTINCT manager_id 
				FROM employees 
				);

题目：查询departments表中，不存在于employees表中的部门的department_id和department_name

SELECT department_id, department_name 
FROM departments d 
WHERE NOT EXISTS (SELECT 'X' 
				FROM employees 
				WHERE department_id = d.department_id);

⑷ 相关更新

UPDATE table1 alias1 
SET column = (SELECT expression 
			FROM table2 alias2 
			WHERE alias1.column = alias2.column);

使用相关子查询依据一个表中的数据更新另一个表的数据。

题目：在employees中增加一个department_name字段，数据为员工对应的部门名称

# 1） 
ALTER TABLE employees 
ADD(department_name VARCHAR2(14)); 

# 2） UPDATE employees e 
SET department_name = (SELECT department_name 
					FROM departments d 
					WHERE e.department_id = d.department_id);

⑸ 相关删除

DELETE FROM table1 alias1 
WHERE column operator (SELECT expression 
						FROM table2 alias2 
						WHERE alias1.column = alias2.column);

使用相关子查询依据一个表中的数据删除另一个表的数据。

题目：删除表employees中，其与emp_history表皆有的数据

DELETE FROM employees e 
WHERE employee_id in 
			(SELECT employee_id 
			FROM emp_history 
			WHERE employee_id = e.employee_id);

5. 抛一个思考题

问题： 谁的工资比Abel的高？
解答：

#方式1：自连接 
SELECT e2.last_name,e2.salary 
FROM employees e1,employees e2 
WHERE e1.last_name = 'Abel' 
AND e1.`salary` < e2.`salary`

#方式2：子查询 
SELECT last_name,salary 
FROM employees 
WHERE salary > ( 
				SELECT salary 
				FROM employees 
				WHERE last_name = 'Abel' 
				);

问题： 以上两种方式有好坏之分吗？
解答： 自连接方式好！

题目中可以使用子查询，也可以使用自连接。一般情况建议你使用自连接，因为在许多 DBMS 的处理过程中，对于自连接的处理速度要比子查询快得多。
可以这样理解：子查询实际上是通过未知表进行查询后的条件判断，而自连接是通过已知的自身数据表进行条件判断，因此在大部分 DBMS 中都对自连接处理进行了优化。

十四、创建和管理表

1. 基础知识

⑴ 一条数据存储的过程

存储数据是处理数据的第一步 。只有正确地把数据存储起来，我们才能进行有效的处理和分析。否则，只能是一团乱麻，无从下手。

那么，怎样才能把用户各种经营相关的、纷繁复杂的数据，有序、高效地存储起来呢？ 在 MySQL 中，一个完整的数据存储过程总共有 4 步，分别是创建数据库、确认字段、创建数据表、插入数据。

我们要先创建一个数据库，而不是直接创建数据表呢？

因为从系统架构的层次上看，MySQL 数据库系统从大到小依次是 数据库服务器 、 数据库 、 数据表 、数据表的 行与列 。

MySQL 数据库服务器之前已经安装。所以，我们就从创建数据库开始。

⑵ 标识符命名规则

⑶ MySQL中的数据类型

其中，常用的几类类型介绍如下：

2. 创建和管理数据库

⑴ 创建数据库

⑵ 使用数据库

⑶ 修改数据库

⑷ 删除数据库

3. 创建表

⑴ 创建方式1

• 必须具备：
  ○ CREATE TABLE权限
  ○ 存储空间

• 语法格式：

  CREATE TABLE [IF NOT EXISTS] 表名( 
  	字段1, 数据类型 [约束条件] [默认值], 
  	字段2, 数据类型 [约束条件] [默认值], 
  	字段3, 数据类型 [约束条件] [默认值], 
  	……
  	[表约束条件] 
  );
  

  

• 必须指定：
  ○ 表名
  ○ 列名(或字段名)，数据类型，长度

• 可选指定：
  ○ 约束条件
  ○ 默认值

• 创建表举例1：

  CREATE TABLE emp (
  	-- int类型 
  	emp_id INT,
  	-- 最多保存20个中英文字符 
  	emp_name VARCHAR(20),
  	-- 总位数不超过15位 
  	salary DOUBLE,
  	-- 日期类型 
  	birthday DATE 
  );
  

  DESC emp;
  

  
  MySQL在执行建表语句时，将id字段的类型设置为int(11)，这里的11实际上是int类型指定的显示宽度，默认的显示宽度为11。也可以在创建数据表的时候指定数据的显示宽度。

• 创建表举例2：

  CREATE TABLE dept(
  	-- int类型，自增 
  	deptno INT(2) AUTO_INCREMENT, 
  	dname VARCHAR(14), 
  	loc VARCHAR(13),
  	-- 主键 
  	PRIMARY KEY (deptno) 
  );
  

  DESCRIBE dept;
  

  

⑵ 创建方式2

• 使用 AS subquery 选项，将创建表和插入数据结合起来
  

• 指定的列和子查询中的列要一一对应

• 通过列名和默认值定义列

  CREATE TABLE emp1 AS SELECT * FROM employees; 
  CREATE TABLE emp2 AS SELECT * FROM employees WHERE 1=2; -- 创建的emp2是空表
  

  CREATE TABLE dept80 
  AS
  SELECT employee_id, last_name, salary*12 ANNSAL, hire_date 
  FROM employees 
  WHERE department_id = 80;
  

  DESCRIBE dept80;
  

  

⑶ 查看数据表结构

在MySQL中创建好数据表之后，可以查看数据表的结构。MySQL支持使用 DESCRIBE/DESC 语句查看数据表结构，也支持使用 SHOW CREATE TABLE 语句查看数据表结构。

语法格式如下：

SHOW CREATE TABLE 表名\G

使用SHOW CREATE TABLE语句不仅可以查看表创建时的详细语句，还可以查看存储引擎和字符编码。

4. 修改表

修改表指的是修改数据库中已经存在的数据表的结构。

使用 ALTER TABLE 语句可以实现：

• 向已有的表中添加列
• 修改现有表中的列
• 删除现有表中的列
• 重命名现有表中的列

⑴ 追加一个列

语法格式如下：

ALTER TABLE 表名 ADD 【COLUMN】 字段名 字段类型 【FIRST|AFTER 字段名】;

举例：

ALTER TABLE dept80 
ADD job_id varchar(15);

⑵ 修改一个列

• 可以修改列的数据类型，长度、默认值和位置

• 修改字段数据类型、长度、默认值、位置的语法格式如下：

  ALTER TABLE 表名 MODIFY 【COLUMN】 字段名1 字段类型 【DEFAULT 默认值】【FIRST|AFTER 字段名 2】;
  

• 举例：

  ALTER TABLE dept80 
  MODIFY last_name VARCHAR(30);
  

  ALTER TABLE dept80 
  MODIFY salary double(9,2) default 1000;
  

• 对默认值的修改只影响今后对表的修改

• 此外，还可以通过此种方式修改列的约束。这里暂先不讲。

⑶ 重命名一个列

使用 CHANGE old_column new_column dataType子句重命名列。语法格式如下：

ALTER TABLE 表名 CHANGE 【column】 列名 新列名 新数据类型;

举例：

ALTER TABLE dept80 
CHANGE department_name dept_name varchar(15);

⑷ 删除一个列

删除表中某个字段的语法格式如下：

ALTER TABLE 表名 DROP 【COLUMN】字段名

举例：

ALTER TABLE dept80 
DROP COLUMN job_id;

5. 重命名表

• 方式一：使用RENAME

  RENAME TABLE emp 
  TO myemp;
  

• 方式二：

  ALTER table dept 
  RENAME [TO] detail_dept; -- [TO]可以省略
  

• 必须是对象的拥有者

6. 删除表

• 在MySQL中，当一张数据表 没有与其他任何数据表形成关联关系 时，可以将当前数据表直接删除。

• 数据和结构都被删除

• 所有正在运行的相关事务被提交

• 所有相关索引被删除

• 语法格式：

  DROP TABLE [IF EXISTS] 数据表1 [, 数据表2, …, 数据表n];
  

  IF EXISTS 的含义为：如果当前数据库中存在相应的数据表，则删除数据表；如果当前数据库中不存在相应的数据表，则忽略删除语句，不再执行删除数据表的操作。

• 举例：

  DROP TABLE dept80;
  

• DROP TABLE 语句不能回滚

7. 清空表

• TRUNCATE TABLE语句：
  ○ 删除表中所有的数据
  ○ 释放表的存储空间

• 举例：

  TRUNCATE TABLE detail_dept;
  

• TRUNCATE语句不能回滚，而使用 DELETE 语句删除数据，可以回滚

• 对比：

  SET autocommit = FALSE; 
  
  DELETE FROM emp2; 
  #TRUNCATE TABLE emp2; 
  
  SELECT * FROM emp2; 
  
  ROLLBACK; SELECT * FROM emp2;
  

  

8. 内容拓展

十五、数据处理之增删改

1. 插入数据

⑴ 实际问题

解决方式：使用 INSERT 语句向表中插入数据。

⑵ 方式1：VALUES的方式添加

使用这种语法一次只能向表中插入一条 数据。

情况1：为表的所有字段按默认顺序插入数据

INSERT INTO 表名 
VALUES (value1,value2,....);

值列表中需要为表的每一个字段指定值，并且值的顺序必须和数据表中字段定义时的顺序相同。

举例：

INSERT INTO departments 
VALUES (70, 'Pub', 100, 1700);

INSERT INTO departments 
VALUES (100, 'Finance', NULL, NULL);

情况2：为表的指定字段插入数据

INSERT INTO 表名(column1 [, column2, …, columnn]) 
VALUES (value1 [,value2, …, valuen]);

为表的指定字段插入数据，就是在INSERT语句中只向部分字段中插入值，而其他字段的值为表定义时的默认值。

在 INSERT 子句中随意列出列名，但是一旦列出，VALUES中要插入的value1,…valuen需要与column1,…columnn列一一对应。如果类型不同，将无法插入，并且MySQL会产生错误。

举例：

INSERT INTO departments(department_id, department_name) 
VALUES (80, 'IT');

情况3：同时插入多条记录

INSERT语句可以同时向数据表中插入多条记录，插入时指定多个值列表，每个值列表之间用逗号分隔开，基本语法格式如下：

INSERT INTO table_name 
VALUES 
(value1 [,value2, …, valuen]), 
(value1 [,value2, …, valuen]), 
……
(value1 [,value2, …, valuen]);

或者

INSERT INTO table_name(column1 [, column2, …, columnn]) 
VALUES 
(value1 [,value2, …, valuen]), 
(value1 [,value2, …, valuen]), 
……
(value1 [,value2, …, valuen]);

举例：

mysql> INSERT INTO emp(emp_id,emp_name)
-> VALUES (1001,'shkstart'),
-> (1002,'atguigu'),
-> (1003,'Tom'); 
Query OK, 3 rows affected (0.00 sec) 
Records: 3 Duplicates: 0 Warnings: 0

使用INSERT同时插入多条记录时，MySQL会返回一些在执行单行插入时没有的额外信息，这些信息的含义如下：
●　Records：表明插入的记录条数。
●　Duplicates：表明插入时被忽略的记录，原因可能是这些记录包含了重复的主键值。
●　Warnings：表明有问题的数据值，例如发生数据类型转换。

小结：

• VALUES 也可以写成 VALUE ，但是VALUES是标准写法。
• 字符和日期型数据应包含在单引号中。

⑶ 方式2：将查询结果插入到表中

INSERT还可以将SELECT语句查询的结果插入到表中，此时不需要把每一条记录的值一个一个输入，只需要使用一条INSERT语句和一条SELECT语句组成的组合语句即可快速地从一个或多个表中向一个表中插入多行。

基本语法格式如下：

INSERT INTO 目标表名 
(tar_column1 [, tar_column2, …, tar_columnn]) 
SELECT (src_column1 [, src_column2, …, src_columnn]) 
FROM 源表名 
[WHERE condition]

• 在 INSERT 语句中加入子查询。
• 不必书写 VALUES 子句。
• 子查询中的值列表应与 INSERT 子句中的列名对应。

举例：

INSERT INTO emp2 
SELECT * 
FROM employees 
WHERE department_id = 90;

INSERT INTO sales_reps(id, name, salary, commission_pct) 
SELECT employee_id, last_name, salary, commission_pct 
FROM employees 
WHERE job_id LIKE '%REP%';

2. 更新数据

• 使用 UPDATE 语句更新数据。语法如下：

  UPDATE table_name 
  SET column1=value1, column2=value2, … , column=valuen 
  [WHERE condition]
  

• 可以一次更新 多条 数据。

• 如果需要回滚数据，需要保证在DML前，进行设置：SET AUTOCOMMIT = FALSE;

• 使用 WHERE 子句指定需要更新的数据。

  UPDATE employees 
  SET department_id = 70 
  WHERE employee_id = 113;
  

• 如果省略 WHERE 子句，则表中的所有数据都将被更新。

  UPDATE copy_emp 
  SET department_id = 110;
  

• 更新中的数据完整性错误

  UPDATE employees 
  SET department_id = 55 
  WHERE department_id = 110;
  

  

3. 删除数据

• 使用 DELETE 语句从表中删除数据
  

  DELETE FROM table_name [WHERE <condition>];
  

  table_name指定要执行删除操作的表；“[WHERE ]”为可选参数，指定删除条件，如果没有WHERE子句，DELETE语句将删除表中的所有记录。

• 使用 WHERE 子句删除指定的记录。

  DELETE FROM departments 
  WHERE department_name = 'Finance';
  

• 如果省略 WHERE 子句，则表中的全部数据将被删除

  DELETE FROM copy_emp;
  

• 删除中的数据完整性错误

  DELETE FROM departments 
  WHERE department_id = 60;
  

  

4. MySQL8新特性：计算列

什么叫计算列呢？简单来说就是某一列的值是通过别的列计算得来的。例如，a列值为1、b列值为2，c列不需要手动插入，定义a+b的结果为c的值，那么c就是计算列，是通过别的列计算得来的。

在MySQL 8.0中，CREATE TABLE 和 ALTER TABLE 中都支持增加计算列。下面以CREATE TABLE为例进行讲解。

举例：定义数据表tb1，然后定义字段id、字段a、字段b和字段c，其中字段c为计算列，用于计算a+b的值。 首先创建测试表tb1，语句如下：

CREATE TABLE tb1( 
id INT, 
a INT, 
b INT, 
c INT GENERATED ALWAYS AS (a + b) VIRTUAL 
);

插入演示数据，语句如下：

INSERT INTO tb1(a,b) VALUES (100,200);

查询数据表tb1中的数据，结果如下：

更新数据中的数据，语句如下：

十六、MySQL数据类型精讲

1. MySQL中的数据类型

常见数据类型的属性，如下：

2. 整数类型

⑴ 类型介绍

整数类型一共有 5 种，包括 TINYINT、SMALLINT、MEDIUMINT、INT（INTEGER）和 BIGINT。

它们的区别如下表所示：

⑵ 可选属性

整数类型的可选属性有三个：

1） M

举例：

CREATE TABLE test_int1 ( x TINYINT, y SMALLINT, z MEDIUMINT, m INT, n BIGINT );

查看表结构 （MySQL5.7中显式如下，MySQL8中不再显式范围）

举例：

CREATE TABLE test_int2(
f1 INT,
f2 INT(5),
f3 INT(5) ZEROFILL 
)

DESC test_int2; 

INSERT INTO test_int2(f1,f2,f3) 
VALUES(1,123,123); 

INSERT INTO test_int2(f1,f2) 
VALUES(123456,123456); 

INSERT INTO test_int2(f1,f2,f3) 
VALUES(123456,123456,123456);

2） UNSIGNED

3）ZEROFILL

⑶ 适用场景

⑷ 如何选择？

3. 浮点类型

⑴ 类型介绍

浮点数和定点数类型的特点是可以 处理小数 ，你可以把整数看成小数的一个特例。因此，浮点数和定点数的使用场景，比整数大多了。 MySQL支持的浮点数类型，分别是 FLOAT、DOUBLE、REAL。

问题1：FLOAT 和 DOUBLE 这两种数据类型的区别是啥呢？
FLOAT 占用字节数少，取值范围小；DOUBLE 占用字节数多，取值范围也大。
问题2：为什么浮点数类型的无符号数取值范围，只相当于有符号数取值范围的一半，也就是只相当于有符号数取值范围大于等于零的部分呢？

⑵ 数据精度说明

⑶ 精度误差说明

浮点数类型有个缺陷，就是不精准。下面我来重点解释一下为什么 MySQL 的浮点数不够精准。比如，我们设计一个表，有f1这个字段，插入值分别为0.47,0.44,0.19，我们期待的运行结果是：0.47 + 0.44 + 0.19 = 1.1。而使用sum之后查询：

4. 定点数类型

⑴ 类型介绍

• MySQL中的定点数类型只有 DECIMAL 一种类型。

⑵ 开发中经验

5. 位类型：BIT

注意：在向BIT类型的字段中插入数据时，一定要确保插入的数据在BIT类型支持的范围内。

6. 日期与时间类型

日期与时间是重要的信息，在我们的系统中，几乎所有的数据表都用得到。原因是客户需要知道数据的
时间标签，从而进行数据查询、统计和处理。
MySQL有多种表示日期和时间的数据类型，不同的版本可能有所差异，MySQL8.0版本支持的日期和时间类型主要有：YEAR类型、TIME类型、DATE类型、DATETIME类型和TIMESTAMP类型。

• YEAR 类型通常用来表示年
• DATE 类型通常用来表示年、月、日
• TIME 类型通常用来表示时、分、秒
• DATETIME 类型通常用来表示年、月、日、时、分、秒
• TIMESTAMP 类型通常用来表示带时区的年、月、日、时、分、秒

⑴ YEAR类型

YEAR类型用来表示年份，在所有的日期时间类型中所占用的存储空间最小，只需要 1个字节 的存储空
间。
在MySQL中，YEAR有以下几种存储格式：

• 以4位字符串或数字格式表示YEAR类型，其格式为YYYY，最小值为1901，最大值为2155。
• 以2位字符串格式表示YEAR类型，最小值为00，最大值为99。
  当取值为01到69时，表示2001到2069；
  当取值为70到99时，表示1970到1999；
  当取值整数的0或00添加的话，那么是0000年；
  当取值是日期/字符串的’0’添加的话，是2000年。

从MySQL5.5.27开始，2位格式的YEAR已经不推荐使用。YEAR默认格式就是“YYYY”，没必要写成YEAR(4)， 从MySQL 8.0.19开始，不推荐使用指定显示宽度的YEAR(4)数据类型。

CREATE TABLE test_year(
f1 YEAR,
f2 YEAR(4) );

INSERT INTO test_year VALUES('2020','2021');

INSERT INTO test_year VALUES('45','71'); 

INSERT INTO test_year VALUES(0,'0');

⑵ DATE类型

DATE类型表示日期，没有时间部分，格式为 YYYY-MM-DD ，其中，YYYY表示年份，MM表示月份，DD表示日期。需要 3个字节 的存储空间。在向DATE类型的字段插入数据时，同样需要满足一定的格式条件。

• 以 YYYY-MM-DD 格式或者 YYYYMMDD 格式表示的字符串日期，其最小取值为1000-01-01，最大取值为9999-12-03。YYYYMMDD格式会被转化为YYYY-MM-DD格式。
• 以YY-MM-DD 格式或者 YYMMDD 格式表示的字符串日期，此格式中，年份为两位数值或字符串满足YEAR类型的格式条件为：当年份取值为00到69时，会被转化为2000到2069；当年份取值为70到99时,会被转化为1970到1999。
• 使用 CURRENT_DATE() 或者 NOW() 函数，会插入当前系统的日期。

举例：
创建数据表，表中只包含一个DATE类型的字段f1。

CREATE TABLE test_date1(
f1 DATE 
);
Query OK, 0 rows affected (0.13 sec)

插入数据：

INSERT INTO test_date1 
VALUES ('2020-10-01'), ('20201001'),(20201001); 

INSERT INTO test_date1 
VALUES ('00-01-01'), ('000101'), ('69-10-01'), ('691001'), ('70-01-01'), ('700101'), ('99-01-01'), ('990101');

INSERT INTO test_date1 
VALUES (000301), (690301), (700301), (990301); 

INSERT INTO test_date1 
VALUES (CURRENT_DATE()), (NOW());

SELECT * FROM test_date1;

⑶ TIME类型

TIME类型用来表示时间，不包含日期部分。在MySQL中，需要 3个字节 的存储空间来存储TIME类型的数据，可以使用“HH:MM:SS”格式来表示TIME类型，其中，HH表示小时，MM表示分钟，SS表示秒。

在MySQL中，向TIME类型的字段插入数据时，也可以使用几种不同的格式。
(1)可以使用带有冒号的
字符串，比如 ' D HH:MM:SS'、' HH:MM:SS '、' HH:MM '、' D HH:MM '、' D HH '或' SS '格式，都能被正确地插入TIME类型的字段中。其中D表示天，其最小值为0，最大值为34。如果使用带有D格式的字符串插入TIME类型的字段时，D会被转化为小时，计算格式为D*24+HH。当使用带有冒号并且不带D的字符串表示时间时，表示当天的时间，比如12:10表示12:10:00，而不是00:12:10。
(2)可以使用不带有冒号的字符串或者数字，格式为' HHMMSS '或者 HHMMSS 。如果插入一个不合法的字符串或者数字，MySQL在存储数据时，会将其自动转化为00:00:00进行存储。比如1210，MySQL会将最右边的两位解析成秒，表示00:12:10，而不是12:10:00。
(3)使用 CURRENT_TIME() 或者NOW() ，会插入当前系统的时间。

举例：
创建数据表，表中包含一个TIME类型的字段f1。

CREATE TABLE test_time1(
f1 TIME );
Query OK, 0 rows affected (0.02 sec)

INSERT INTO test_time1 
VALUES('2 12:30:29'), ('12:35:29'), ('12:40'), ('2 12:40'),('1 05'), ('45'); 

INSERT INTO test_time1 
VALUES ('123520'), (124011),(1210); 

INSERT INTO test_time1 
VALUES (NOW()), (CURRENT_TIME()); 

SELECT * FROM test_time1;

⑷ DATETIME类型

DATETIME类型在所有的日期时间类型中占用的存储空间最大，总共需要 8 个字节的存储空间。在格式上为DATE类型和TIME类型的组合，可以表示为 YYYY-MM-DD HH:MM:SS ，其中YYYY表示年份，MM表示月份，DD表示日期，HH表示小时，MM表示分钟，SS表示秒。

在向DATETIME类型的字段插入数据时，同样需要满足一定的格式条件。

• 以 YYYY-MM-DD HH:MM:SS 格式或者 YYYYMMDDHHMMSS 格式的字符串插入DATETIME类型的字段时，最小值为1000-01-01 00:00:00，最大值为9999-12-03 23:59:59。
  ○ 以YYYYMMDDHHMMSS格式的数字插入DATETIME类型的字段时，会被转化为YYYY-MM-DD HH:MM:SS格式。

• 以 YY-MM-DD HH:MM:SS 格式或者YYMMDDHHMMSS格式的字符串插入DATETIME类型的字段时，两位数的年份规则符合YEAR类型的规则，00到69表示2000到2069；70到99表示1970到1999。

• 使用函数 CURRENT_TIMESTAMP() 和 NOW() ，可以向DATETIME类型的字段插入系统的当前日期和
  时间。

举例：
创建数据表，表中包含一个DATETIME类型的字段dt。

CREATE TABLE test_datetime1( 
dt DATETIME 
);
Query OK, 0 rows affected (0.02 sec)

插入数据：

INSERT INTO test_datetime1 
VALUES ('2021-01-01 06:50:30'), ('20210101065030'); 

INSERT INTO test_datetime1 
VALUES ('99-01-01 00:00:00'), ('990101000000'), ('20-01-01 00:00:00'), ('200101000000'); 

INSERT INTO test_datetime1 
VALUES (20200101000000), (200101000000), (19990101000000), (990101000000); 

INSERT INTO test_datetime1 
VALUES (CURRENT_TIMESTAMP()), (NOW());

⑸TIMESTAMP类型

TIMESTAMP类型也可以表示日期时间，其显示格式与DATETIME类型相同，都是 YYYY-MM-DD HH:MM:SS ，需要4个字节的存储空间。但是TIMESTAMP存储的时间范围比DATETIME要小很多，只能存储“1970-01-01 00:00:01 UTC”到“2038-01-19 03:14:07 UTC”之间的时间。其中，UTC表示世界统一时间，也叫作世界标准时间。

• 存储数据的时候需要对当前时间所在的时区进行转换，查询数据的时候再将时间转换回当前的时区。因此，使用TIMESTAMP存储的同一个时间值，在不同的时区查询时会显示不同的时间。

向TIMESTAMP类型的字段插入数据时，当插入的数据格式满足YY-MM-DD HH:MM:SS和YYMMDDHHMMSS时，两位数值的年份同样符合YEAR类型的规则条件，只不过表示的时间范围要小很多。

如果向TIMESTAMP类型的字段插入的时间超出了TIMESTAMP类型的范围，则MySQL会抛出错误信息。

举例：
创建数据表，表中包含一个TIMESTAMP类型的字段ts。

CREATE TABLE test_timestamp1( 
ts TIMESTAMP 
);

插入数据：

INSERT INTO test_timestamp1 
VALUES ('1999-01-01 03:04:50'), ('19990101030405'), ('99-01-01 03:04:05'), ('990101030405'); 

INSERT INTO test_timestamp1 
VALUES ('2020@01@01@00@00@00'), ('20@01@01@00@00@00'); 

INSERT INTO test_timestamp1 
VALUES (CURRENT_TIMESTAMP()), (NOW()); 

#Incorrect datetime value 
INSERT INTO test_timestamp1 
VALUES ('2038-01-20 03:14:07');

TIMESTAMP和DATETIME的区别：

• TIMESTAMP存储空间比较小，表示的日期时间范围也比较小

• 底层存储方式不同，TIMESTAMP底层存储的是毫秒值，距离1970-1-1 0:0:0 0毫秒的毫秒值。

• 两个日期比较大小或日期计算时，TIMESTAMP更方便、更快。

• TIMESTAMP和时区有关。TIMESTAMP会根据用户的时区不同，显示不同的结果。而DATETIME则只能反映出插入时当地的时区，其他时区的人查看数据必然会有误差的。

  CREATE TABLE temp_time( 
  d1 DATETIME, 
  d2 TIMESTAMP 
  );
  

  INSERT INTO temp_time VALUES('2021-9-2 14:45:52','2021-9-2 14:45:52'); 
  
  INSERT INTO temp_time VALUES(NOW(),NOW());
  

  

  #修改当前的时区 
  SET time_zone = '+9:00';
  

  

⑹ 开发中经验

用得最多的日期时间类型，就是 DATETIME 。虽然 MySQL 也支持 YEAR（年）、 TIME（时间）、DATE（日期），以及 TIMESTAMP 类型，但是在实际项目中，尽量用 DATETIME 类型。因为这个数据类型包括了完整的日期和时间信息，取值范围也最大，使用起来比较方便。毕竟，如果日期时间信息分散在好几个字段，很不容易记，而且查询的时候，SQL 语句也会更加复杂。

此外，一般存注册时间、商品发布时间等，不建议使用DATETIME存储，而是使用 时间戳 ，因为DATETIME虽然直观，但不便于计算。

7. 文本字符串类型

在实际的项目中，我们还经常遇到一种数据，就是字符串数据。

MySQL中，文本字符串总体上分为 CHAR 、 VARCHAR 、 TINYTEXT 、 TEXT 、 MEDIUMTEXT 、 LONGTEXT 、 ENUM 、 SET 等类型。

⑴ CHAR与VARCHAR类型

CHAR和VARCHAR类型都可以存储比较短的字符串。

CHAR类型：

• CHAR(M) 类型一般需要预先定义字符串长度。如果不指定(M)，则表示长度默认是1个字符。
• 如果保存时，数据的实际长度比CHAR类型声明的长度小，则会在 右侧填充 空格以达到指定的长度。当MySQL检索CHAR类型的数据时，CHAR类型的字段会去除尾部的空格。
• 定义CHAR类型字段时，声明的字段长度即为CHAR类型字段所占的存储空间的字节数。

VARCHAR类型：

• VARCHAR(M) 定义时， 必须指定 长度M，否则报错。
• MySQL4.0版本以下，varchar(20)：指的是20字节，如果存放UTF8汉字时，只能存6个（每个汉字3字节） ；MySQL5.0版本以上，varchar(20)：指的是20字符。
• 检索VARCHAR类型的字段数据时，会保留数据尾部的空格。VARCHAR类型的字段所占用的存储空间为字符串实际长度加1个字节

哪些情况使用 CHAR 或 VARCHAR 更好

情况1：存储很短的信息。比如门牌号码101，201……这样很短的信息应该用char，因为varchar还要占个byte用于存储信息长度，本来打算节约存储的，结果得不偿失。

情况2：固定长度的。比如使用uuid作为主键，那用char应该更合适。因为他固定长度，varchar动态根据长度的特性就消失了，而且还要占个长度信息。

情况3：十分频繁改变的column。因为varchar每次存储都要有额外的计算，得到长度等工作，如果一个非常频繁改变的，那就要有很多的精力用于计算，而这些对于char来说是不需要的。

情况4：具体存储引擎中的情况：

⑵TEXT类型

在MySQL中，TEXT用来保存文本类型的字符串，总共包含4种类型，分别为TINYTEXT、TEXT、 MEDIUMTEXT 和 LONGTEXT 类型。

在向TEXT类型的字段保存和查询数据时，系统自动按照实际长度存储，不需要预先定义长度。这一点和VARCHAR类型相同。

每种TEXT类型保存的数据长度和所占用的存储空间不同，如下：

由于实际存储的长度不确定，MySQL 不允许 TEXT 类型的字段做主键。 遇到这种情况，你只能采用CHAR(M)，或者 VARCHAR(M)。

举例：
创建数据表：

CREATE TABLE test_text( 
tx TEXT 
);

INSERT INTO test_text 
VALUES('kejizhentan   '); 

SELECT CHAR_LENGTH(tx) 
FROM test_text; #14

说明在保存和查询数据时，并没有删除TEXT类型的数据尾部的空格。

开发中经验：

8. ENUM类型

ENUM类型也叫作枚举类型，ENUM类型的取值范围需要在定义字段时进行指定。设置字段值时，ENUM类型只允许从成员中选取单个值，不能一次选取多个值。

其所需要的存储空间由定义ENUM类型时指定的成员个数决定。

举例：
创建表如下：

CREATE TABLE test_enum( 
season ENUM('春','夏','秋','冬','unknow') 
);

添加数据：

INSERT INTO test_enum 
VALUES('春'),('秋'); 

# 忽略大小写 
INSERT INTO test_enum 
VALUES('UNKNOW'); 

# 允许按照角标的方式获取指定索引位置的枚举值 
INSERT INTO test_enum 
VALUES('1'),(3); 

# Data truncated for column 'season' at row 1 
INSERT INTO test_enum VALUES('ab'); 

# 当ENUM类型的字段没有声明为NOT NULL时，插入NULL也是有效的 
INSERT INTO test_enum 
VALUES(NULL)

9. SET类型

SET表示一个字符串对象，可以包含0个或多个成员，但成员个数的上限为 64 。设置字段值时，可以取取值范围内的 0 个或多个值。

当SET类型包含的成员个数不同时，其所占用的存储空间也是不同的，具体如下：

SET类型在存储数据时成员个数越多，其占用的存储空间越大。注意：SET类型在选取成员时，可以一选择多个成员，这一点与ENUM类型不同。

举例：
创建表：

CREATE TABLE test_set( 
s SET ('A', 'B', 'C') 
);

向表中插入数据：

INSERT INTO test_set (s) VALUES ('A'), ('A,B'); 

#插入重复的SET类型成员时，MySQL会自动删除重复的成员 
INSERT INTO test_set (s) VALUES ('A,B,C,A'); 

#向SET类型的字段插入SET成员中不存在的值时，MySQL会抛出错误。 
INSERT INTO test_set (s) VALUES ('A,B,C,D'); 

SELECT * FROM test_set;

举例：

CREATE TABLE temp_mul( 
gender ENUM('男','女'), 
hobby SET('吃饭','睡觉','打豆豆','写代码') 
);

INSERT INTO temp_mul VALUES('男','睡觉,打豆豆'); #成功 

# Data truncated for column 'gender' at row 1 
INSERT INTO temp_mul VALUES('男,女','睡觉,写代码'); #失败 

# Data truncated for column 'gender' at row 1 
INSERT INTO temp_mul VALUES('妖','睡觉,写代码');#失败 

INSERT INTO temp_mul VALUES('男','睡觉,写代码,吃饭'); #成功

10. 二进制字符串类型

11. JSON 类型

JSON（JavaScript Object Notation）是一种轻量级的数据交换格式 。简洁和清晰的层次结构使得 JSON 成为理想的数据交换语言。它易于人阅读和编写，同时也易于机器解析和生成，并有效地提升网络传输效率。JSON 可以将 JavaScript 对象中表示的一组数据转换为字符串，然后就可以在网络或者程序之间轻松地传递这个字符串，并在需要的时候将它还原为各编程语言所支持的数据格式。

在MySQL 5.7中，就已经支持JSON数据类型。在MySQL 8.x版本中，JSON类型提供了可以进行自动验证的JSON文档和优化的存储结构，使得在MySQL中存储和读取JSON类型的数据更加方便和高效。 创建数据表，表中包含一个JSON类型的字段 js 。

CREATE TABLE test_json( 
js json 
);

向表中插入JSON数据。

INSERT INTO test_json (js) 
VALUES ('{"name":"songhk", "age":18, "address":{"province":"beijing", "city":"beijing"}}');

查询t19表中的数据。

mysql> SELECT *
	-> FROM test_json;

当需要检索JSON类型的字段中数据的某个具体值时，可以使用“->”和“->>”符号。

12. 空间类型

13. 小结及选择建议

十七、约束

1. 约束(constraint)概述

⑴ 为什么需要约束

⑵ 什么是约束

⑶ 约束的分类

• 查看某个表已有的约束

  #information_schema数据库名（系统库） 
  #table_constraints表名称（专门存储各个表的约束） 
  SELECT * FROM information_schema.table_constraints 
  WHERE table_name = '表名称';
  

2. 非空约束

⑴ 作用

⑵ 关键字

NOT NULL

⑶ 特点

• 默认，所有的类型的值都可以是NULL，包括INT、FLOAT等数据类型
• 非空约束只能出现在表对象的列上，只能某个列单独限定非空，不能组合非空
• 一个表可以有很多列都分别限定了非空
• 空字符串’'不等于NULL，0也不等于NULL

⑷ 添加非空约束

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允许为空 
insert into student values(3,null,null,'110222198912032547');#失败 
ERROR 1048 (23000): Column 'sname' cannot be null

2）建表后

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. 唯一性约束

⑴ 作用

⑵ 关键字

UNIQUE

⑶ 特点

• 同一个表可以有多个唯一约束。
• 唯一约束可以是某一个列的值唯一，也可以多个列组合的值唯一。
• 唯一性约束允许列值为空。
• 在创建唯一约束的时候，如果不给唯一约束命名，就默认和列名相同。
• MySQL会给唯一约束的列上默认创建一个唯一索引。

⑷ 添加唯一约束

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');

2）建表后指定唯一键约束

#字段列表中如果是一个字段，表示该列的值唯一。如果是两个或更多个字段，那么复合唯一，即多个字段的组合是唯 一的
#方式1： 
alter table 表名称 add unique key(字段列表)


#方式2： 
alter table 表名称 modify 字段名 字段类型 unique;

举例：

ALTER TABLE USER 
ADD UNIQUE(NAME,PASSWORD);

ALTER TABLE USER 
ADD CONSTRAINT uk_name_pwd 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(字段列表) #字段列表中写的是多个字段名，多个字段名用逗号分隔，表示那么是复合唯一，即多 个字段的组合是唯一的 
);

⑹ 删除唯一约束

• 添加唯一性约束的列上也会自动创建唯一索引。
• 删除唯一约束只能通过删除唯一索引的方式删除。
• 删除时需要指定唯一索引名，唯一索引名就和唯一约束名一样。
• 如果创建唯一约束时未指定名称，如果是单列，就默认和列名相同；如果是组合列，那么默认和()中排在第一个的列名相同。也可以自定义唯一性约束名。

SELECT * FROM information_schema.table_constraints WHERE table_name = '表名'; #查看都有哪 些约束

ALTER TABLE USER 
DROP INDEX uk_name_pwd;

4. PRIMARY KEY 约束

⑴ 作用

用来唯一标识表中的一行记录。

⑵ 关键字

primary key

⑶ 特点

⑷ 添加主键约束

1）建表时指定主键约束

create table 表名称( 
	字段名 数据类型 primary key, #列级模式 
	字段名 数据类型, 
	字段名 数据类型 
);


create table 表名称( 
	字段名 数据类型, 
	字段名 数据类型, 
	字段名 数据类型, 
	[constraint 约束名] primary key(字段名) #表级模式 
);

举例：

再举例：

• 列级约束

  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) 
  );
  

2）建表后增加主键约束

ALTER TABLE 表名称 ADD PRIMARY KEY(字段列表); #字段列表可以是一个字段，也可以是多个字段，如果是多 个字段的话，是复合主键

ALTER TABLE student ADD PRIMARY KEY (sid);

ALTER TABLE emp5 ADD PRIMARY KEY(NAME,pwd);

⑸ 关于复合主键

create table 表名称( 
	字段名 数据类型, 
	字段名 数据类型, 
	字段名 数据类型, 
	primary key(字段名1,字段名2) #表示字段1和字段2的组合是唯一的，也可以有更多个字段 
);

• 再举例

  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. 自增列：AUTO_INCREMENT

⑴ 作用

某个字段的值自增

⑵ 关键字

auto_increment

⑶ 特点和要求

1）一个表最多只能有一个自增长列
2）当需要产生唯一标识符或顺序值时，可设置自增长
3）自增长列约束的列必须是键列（主键列，唯一键列）
4）自增约束的列的数据类型必须是整数类型
5）如果自增列指定了 0 和 null，会在当前最大值的基础上自增；如果自增列手动指定了具体值，直接赋值为具体值。

错误演示：

⑷ 如何指定自增约束

1）建表时

2）建表后

⑸ 如何删除自增约束

⑹ MySQL 8.0新特性—自增变量的持久化

在MySQL 8.0之前，自增主键AUTO_INCREMENT的值如果大于max(primary key)+1，在MySQL重启后，会重置AUTO_INCREMENT=max(primary key)+1，这种现象在某些情况下会导致业务主键冲突或者其他难以发现的问题。 下面通过案例来对比不同的版本中自增变量是否持久化。 在MySQL 5.7版本中，测试步骤如下： 创建的数据表中包含自增主键的id字段，语句如下：

6. FOREIGN KEY 约束

⑴ 作用

限定某个表的某个字段的引用完整性。
比如：员工表的员工所在部门的选择，必须在部门表能找到对应的部分。

⑵ 关键字

FOREIGN KEY

⑶ 主表和从表/父表和子表

⑷ 特点

① 从表的外键列，必须引用/参考主表的主键或唯一约束的列
为什么？因为被依赖/被参考的值必须是唯一的
② 在创建外键约束时，如果不给外键约束命名，默认名不是列名，而是自动产生一个外键名（例如 student_ibfk_1;），也可以指定外键约束名。
③ 创建(CREATE)表时就指定外键约束的话，先创建主表，再创建从表
④ 删表时，先删从表（或先删除外键约束），再删除主表
⑤ 当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖
该记录的数据，然后才可以删除主表的数据
⑥ 在“从表”中指定外键约束，并且一个表可以建立多个外键约束
⑦ 从表的外键列与主表被参照的列名字可以不相同，但是数据类型必须一样，逻辑意义一致。如果类
型不一样，创建子表时，就会出现错误“ERROR 1005 (HY000): Can’t create table’database.tablename’(errno: 150)”。
例如：都是表示部门编号，都是int类型。
⑧ 当创建外键约束时，系统默认会在所在的列上建立对应的普通索引。但是索引名是外键的约束名。（根据外键查询效率很高）

⑨ 删除外键约束后，必须 手动 删除对应的索引

⑸ 添加外键约束

1）建表时

2）建表后

⑹ 演示问题

1）失败：不是键列

2）失败：数据类型不一致

3）成功，两个表字段名一样

4）添加、删除、修改问题

⑺ 约束等级

1）演示1：on update cascade on delete set null

2）演示2：on update set null on delete cascade

3）演示：on update cascade on delete cascade

⑻ 删除外键约束

流程如下：

1)第一步先查看约束名和删除外键约束 
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';#查看某个 表的约束名 

ALTER TABLE 从表名 DROP FOREIGN KEY 外键约束名; 

2）第二步查看索引名和删除索引。（注意，只能手动删除） 
SHOW INDEX FROM 表名称; #查看某个表的索引名 

ALTER TABLE 从表名 DROP INDEX 索引名;

举例：

⑼ 开发场景

(10) 阿里开发规范

7. CHECK 约束

⑴ 作用

检查某个字段的值是否符号xx要求，一般指的是值的范围

⑵ 关键字

CHECK

⑶ 说明：MySQL 5.7 不支持

MySQL5.7 可以使用check约束，但check约束对数据验证没有任何作用。添加数据时，没有任何错误或警告

但是MySQL 8.0中可以使用check约束了。

8. DEFAULT约束

⑴ 作用

给某个字段/某列指定默认值，一旦设置默认值，在插入数据时，如果此字段没有显式赋值，则赋值为默认值。

⑵ 关键字

DEFAULT

⑶ 如何给字段加默认值

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 '' #默认是空字符串 
);

insert into employee values(1,'汪飞','男','13700102535'); #成功

insert into employee(eid,ename) values(2,'天琪'); #成功

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 
);

alter table employee modify gender char default '男'; #给gender字段增加默认值约束 
alter table employee modify tel char(11) default ''; #给tel字段增加默认值约束

alter table employee modify tel char(11) default '' not null;#给tel字段增加默认值约束，并 保留非空约束

⑷ 如何删除默认值约束

9. 面试

十八、视图

1. 常见的数据库对象

2. 视图概述

⑴ 为什么使用视图？

视图一方面可以帮我们使用表的一部分而不是所有的表，另一方面也可以针对不同的用户制定不同的查询视图。比如，针对一个公司的销售人员，我们只想给他看部分数据，而某些特殊的数据，比如采购的价格，则不会提供给他。再比如，人员薪酬是个敏感的字段，那么只给某个级别以上的人员开放，其他人的查询视图中则不提供这个字段。

刚才讲的只是视图的一个使用场景，实际上视图还有很多作用。最后，我们总结视图的优点。

⑵ 视图的理解

3. 创建视图

• 在 CREATE VIEW 语句中嵌入子查询

CREATE [OR REPLACE] 
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] 
VIEW 视图名称 [(字段列表)] 
AS 查询语句 
[WITH [CASCADED|LOCAL] CHECK OPTION]

• 精简版

CREATE VIEW 视图名称 
AS 查询语句

⑴ 创建单表视图

⑵ 创建多表联合视图

⑶ 基于视图创建视图

4. 查看视图

语法1：查看数据库的表对象、视图对象

SHOW TABLES;

语法2：查看视图的结构

DESC / DESCRIBE 视图名称;

语法3：查看视图的属性信息

# 查看视图信息（显示数据表的存储引擎、版本、数据行数和数据大小等） 
SHOW TABLE STATUS LIKE '视图名称'\G

执行结果显示，注释Comment为VIEW，说明该表为视图，其他的信息为NULL，说明这是一个虚表。
语法4：查看视图的详细定义信息

SHOW CREATE VIEW 视图名称;

5. 更新视图的数据

⑴ 一般情况

MySQL支持使用INSERT、UPDATE和DELETE语句对视图中的数据进行插入、更新和删除操作。当视图中的数据发生变化时，数据表中的数据也会发生变化，反之亦然。

举例：UPDATE操作

⑵ 不可更新的视图

6. 修改、删除视图

⑴ 修改视图

⑵ 删除视图

7. 总结

⑴ 视图优点

⑵ 视图不足

十九、存储过程与函数

MySQL从5.0版本开始支持存储过程和函数。存储过程和函数能够将复杂的SQL逻辑封装在一起，应用程序无须关注存储过程和函数内部复杂的SQL逻辑，而只需要简单地调用存储过程和函数即可。

1. 存储过程概述

⑴ 理解

⑵ 分类

2. 创建存储过程

⑴ 语法分析

⑵ 代码举例

3. 调用存储过程

⑴ 调用格式

⑵ 代码举例

⑶ 如何调试

4. 存储函数的使用

前面学习了很多函数，使用这些函数可以对数据进行的各种处理操作，极大地提高用户对数据库的管理效率。MySQL支持自定义函数，定义好之后，调用方式与调用MySQL预定义的系统函数一样。

⑴ 语法分析

⑵ 调用存储函数

⑶ 代码举例

⑷ 对比存储函数和存储过程

5. 存储过程和函数的查看、修改、删除

⑴ 查看

⑵ 修改

⑶ 删除

6. 关于存储过程使用的争议

尽管存储过程有诸多优点，但是对于存储过程的使用，一直都存在着很多争议，比如有些公司对于大型项目要求使用存储过程，而有些公司在手册中明确禁止使用存储过程，为什么这些公司对存储过程的使用需求差别这么大呢？

⑴ 优点

⑵ 缺点

二十、变量、流程控制与游标

1. 变量

在MySQL数据库的存储过程和函数中，可以使用变量来存储查询或计算的中间结果数据，或者输出最终的结果数据。
在 MySQL 数据库中，变量分为 系统变量 以及 用户自定义变量 。

⑴ 系统变量

1) 系统变量分类

2) 查看系统变量

⑵ 用户变量

1) 用户变量分类

2) 会话用户变量

3) 局部变量

4) 对比会话用户变量与局部变量

2. 定义条件与处理程序

⑴ 案例分析

⑵ 定义条件

⑶ 定义处理程序

⑷ 案例解决

3. 流程控制

⑴ 分支结构之 IF

⑵ 分支结构之 CASE

⑶ 循环结构之LOOP

⑷ 循环结构之WHILE

⑸ 循环结构之REPEAT

⑹ 跳转语句之LEAVE语句

⑺ 跳转语句之ITERATE语句

4. 游标

⑴ 什么是游标（或光标）

⑵ 使用游标步骤

⑶ 举例

⑷ 小结

5. 补充：MySQL 8.0的新特性—全局变量的持久化

二十一、触发器

1. 触发器概述

2. 触发器的创建

⑴ 创建触发器语法

⑵ 代码举例

3. 查看、删除触发器

⑴ 查看触发器

⑵ 删除触发器

4. 触发器的优缺点

⑴ 优点

⑵ 缺点

⑶ 注意点

二十二、MySQL8其它新特性

1. MySQL8新特性概述

⑴ MySQL8.0 新增特性

⑵ MySQL8.0移除的旧特性

2. 新特性1：窗口函数

⑴ 使用窗口函数前后对比

⑵ 窗口函数分类

MySQL官方网站窗口函数的网址为 https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_row-number

⑶ 语法结构

⑷ 分类讲解

⑸ 小 结

3. 新特性2：公用表表达式

⑴ 普通公用表表达式

⑵ 递归公用表表达式

⑶ 小 结