MySQL_知识点_00000

数据库概述

查看数据库排名网址：https://db-engines.com

为什么要使用数据库？

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

数据库与数据库管理系统

数据库的相关概念

• DB：数据库（Database）
  即存储数据的“仓库”，其本质是一个文件系统。它保存了一系列有组织的数据。
• DBMS：数据库管理系统（Database Management System）
  是一种操纵和管理数据库的大型软件，用于建立、使用和维护数据库，对数据库进行统一管理和控制。用户通过数据库管理系统访问数据库中表内的数据。
• SQL：结构化查询语言（Structured Query Language）
  专门用来与数据库通信的语言。

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

数据库管理系统（DBMS）可以管理多个数据库，一般开发人员会针对每一个应用创建一个数据库。为保存应用中实体的数据，一般会在数据库创建多个表，以保存程序中实体用户的数据。

数据库管理系统、数据库和表的关系如图所示：

MySQL介绍

关系型数据库（RDBMS）

• 这种类型的数据库是最古老的数据库类型，关系型数据库模型是把复杂的数据结构归结为简单的二元关系 （即二维表格形式）。
• 关系型数据库以行(row)和列(column) 的形式存储数据，以便于用户理解。这一系列的行和列被称为表(table) ，一组表组成了一个库(database)。
• 表与表之间的数据记录有关系(relationship)。现实世界中的各种实体以及实体之间的各种联系均用关系模型来表示。关系型数据库，就是建立在关系模型基础上的数据库。
• SQL 就是关系型数据库的查询语言。

优势：

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

非关系型数据库（非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等。

关系型数据库设计规则

• 关系型数据库的典型数据结构就是数据表 ，这些数据表的组成都是结构化的（Structured）。
• 将数据放到表中，表再放到库中。
• 一个数据库中可以有多个表，每个表都有一个名字，用来标识自己。表名具有唯一性。
• 表具有一些特性，这些特性定义了数据在表中如何存储，类似Java和Python中 “类”的设计。

表、记录、字段

• E-R（entity-relationship，实体-联系）模型中有三个主要概念是： 实体集 、 属性 、 联系集 。
  
  一个实体集（class）对应于数据库中的一个表（table），一个实体（instance）则对应于数据库表中的一行（row），也称为一条记录（record）。一个属性（attribute）对应于数据库表中的一列（column），也称为一个字段（field）。
  
  ORM思想 (Object Relational Mapping)体现：
  数据库中的一个表 <-> Java中的一个类
  表中的一条数据 <-> 类中的一个对象（或实体）
  表中的一个列 <-> 类中的一个字段、属性(field)

表的关联关系

表与表之间的数据记录有关系(relationship)。现实世界中的各种实体以及实体之间的各种联系均用关系模型来表示。

四种：一对一关联、一对多关联、多对多关联、自我引用。

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

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

举例：设计学生表 ：学号、姓名、手机号码、班级、系别、身份证号码、家庭住址、籍贯、紧急联系人、…

拆为两个表：两个表的记录是一一对应关系。

基础信息表 （常用信息）：学号、姓名、手机号码、班级、系别
档案信息表 （不常用信息）：学号、身份证号码、家庭住址、籍贯、紧急联系人、…

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

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

常见实例场景： 客户表和订单表，分类表和商品表，部门表和员工表 。

举例：
员工表：编号、姓名、…、所属部门
部门表：编号、名称、简介

一对多建表原则：在从表（多方）创建一个字段，字段作为外键指向主表（一方）的主键。

多对多（many-to-many）

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

举例1：学生-课程

学生信息表 ：一行代表一个学生的信息（学号、姓名、手机号码、班级、系别…）
课程信息表 ：一行代表一个课程的信息（课程编号、授课老师、简介…）
选课信息表 ：一个学生可以选多门课，一门课可以被多个学生选择

学号   课程编号 
1    1001
2    1001
1    1002

举例2：产品-订单
“订单”表和“产品”表有一种多对多的关系，这种关系是通过与“订单明细”表建立两个一对多关系来定义的。一个订单可以有多个产品，每个产品可以出现在多个订单中。

产品表 ：“产品”表中的每条记录表示一个产品。
订单表 ：“订单”表中的每条记录表示一个订单。
订单明细表 ：每个产品可以与“订单”表中的多条记录对应，即出现在多个订单中。一个订单可以与“产品”表中的多条记录对应，即包含多个产品。

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

自我引用（Self reference）

MySQL 环境搭建

MySQL 的卸载

步骤一：停止MySQL服务。
在卸载之前，先停止MySQL8.0的服务。

打开“服务”对话框的方式：
1、在“运行”对话框中输入“services.msc”。
2、按键盘上的“Ctrl + Alt + Delete”组合键，打开“任务管理器”对话框，选择“服务”列表。
3、此电脑->管理->服务和应用程序->服务。

在“服务”对话框中找到“MySQL8.0”的服务，如果是“正在运行”状态，可以右键单击服务，选择“停止”选项停止MySQL8.0的服务。

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

方式2：通过安装包提供的卸载功能卸载。
也可以通过安装向导程序进行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”即可，如图所示。

方式2：通过第三方软件
比如：360软件管家等软件。

步骤三：残余文件的清理。
如果再次安装不成功，可以卸载后对残余文件进行清理后再安装。
1、服务目录：mysql服务的安装目录。
2、数据目录：默认在C:\ProgramData\MySQL。如果自己单独指定过数据目录，就找到自己的数据目录进行删除即可。

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

步骤五：删除环境变量配置。
找到path环境变量，将其中关于mysql的环境变量删除，切记不要全部删除。
例如：删除 D:\develop_tools\mysql\MySQLServer8.0.26\bin; 这个部分。

MySQL的下载、安装、配置

• MySQL Community Server 社区版本，开源免费，自由下载，但不提供官方技术支持，适用于大多数普通用户。
• MySQL Enterprise Edition 企业版本，需付费，不能在线下载，可以试用30天。提供了更多的功能和更完备的技术支持，更适合于对数据库的功能和可靠性要求较高的企业客户。
• MySQL Cluster 集群版，开源免费。用于架设集群服务器，可将几个MySQL Server封装成一个Server。需要在社区版或企业版的基础上使用。
• MySQL Cluster CGE 高级集群版，需付费。

此外，官方还提供了MySQL Workbench（GUITOOL），一款专为MySQL设计的图形界面管理工具。MySQLWorkbench又分为两个版本，分别是社区版（MySQL Workbench OSS）、 商用版（MySQLWorkbenchSE）。

下载

下载地址：
MySQL官网_英文：https://www.mysql.com

步骤：
1、进入官网首页。

2、选择“DOWNLOADS”。

3、点击“MySQL Community (GPL) Downloads »”下载社区版。

4、点击“MySQL Community Server”
在General Availability(GA) Releases中选择适合的版本。

Windows平台下提供两种安装文件：
（1）MySQL二进制分发版（.msi安装文件）
（2）免安装版（.zip压缩文件）。

一般来讲，应当使用二进制分发版，因为该版本提供了图形化的安装向导过程，比其他的分发版使用起来要简单，不再需要其他工具启动就可以运行MySQL。
这里在Windows系统下推荐下载 MSI安装程序 ；点击 Go to Download Page 进行下载即可。

5、点击“Go to Download Page”进入如下界面

Windows下的MySQL8.0安装有两种安装程序
（1）mysql-installer-web-community-8.0.26.0.msi，安装时需要联网安装组件。
（2）mysql-installer-community-8.0.26.0.msi，安装时离线安装即可。推荐。

如果安装MySQL5.7版本的话，选择Archives ，接着选择MySQL5.7的相应版本即可。

6、点击“Download”进入如下界面。

7、点击“No thanks,just start my download”,进入如下界面。

安装

MySQL下载完成后，找到下载文件，双击进行安装，具体操作步骤如下。
步骤一：双击下载的mysql-installer-community-8.0.26.0.msi文件，打开安装向导。
步骤二：打开“Choosing a Setup Type”（选择安装类型）窗口，在其中列出了5种安装类型，分别是1、Developer Default（默认安装类型）
2、Server only（仅作为服务器）
3、Client only（仅作为客户端）
4、Full（完全安装）
5、Custom（自定义安装）。

这里选择“Custom（自定义安装）”类型按钮，单击“Next(下一步)”按钮。

步骤三：打开“Select Products” （选择产品）窗口，可以定制需要安装的产品清单。例如，选择“MySQL Server 8.0.26-X64”后，单击“→”按钮，即可选择安装MySQL服务器，如图所示。采用通用的方法，可以添加其他你需要安装的产品。

此时如果直接“Next”（下一步），则产品的安装路径是默认的。如果想要自定义安装目录，则可以选中对应的产品，然后在下面会出现“Advanced Options”（高级选项）的超链接。

单击“Advanced Options”（高级选项）则会弹出安装目录的选择窗口，如图所示，此时你可以分别设置MySQL的服务程序安装目录和数据存储目录。如果不设置，默认分别在C盘的Program Files目录和ProgramData目录（这是一个隐藏目录）。如果自定义安装目录，请避免“中文”目录。另外，建议服务目录和数据目录分开存放。

步骤4：在上一步选择好要安装的产品之后，单击“Next”（下一步）进入确认窗口，如图所示。单击“Execute”（执行）按钮开始安装。

步骤五：安装完成后在“Status”（状态）列表下将显示“Complete”（安装完成），如图所示。

配置

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

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

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

Development Machine（开发机器）：该选项代表典型个人用桌面工作站。此时机器上需要运行多个应用程序，那么MySQL服务器将占用最少的系统资源。
Server Machine（服务器）：该选项代表服务器，MySQL服务器可以同其他服务器应用程序一起运行，例如Web服务器等。MySQL服务器配置成适当比例的系统资源。
Dedicated Machine（专用服务器）：该选项代表只运行MySQL服务的服务器。MySQL服务器配置成使用所有可用系统资源。

步骤三：单击“Next”（下一步）按钮，打开设置授权方式窗口。其中，上面的选项是MySQL8.0提供的新的授权方式，采用SHA256基础的密码加密方法；下面的选项是传统授权方法（保留5.x版本兼容性）。

步骤四：单击“Next”（下一步）按钮，打开设置服务器root超级管理员的密码窗口，如图所示，需要输入两次同样的登录密码。也可以通过“Add User”添加其他用户，添加其他用户时，需要指定用户名、允许该用户名在哪台/哪些主机上登录，还可以指定用户角色等。此处暂不添加用户。

步骤五：单击“Next”（下一步）按钮，打开设置服务器名称窗口，如图所示。该服务名会出现在Windows服务列表中，也可以在命令行窗口中使用该服务名进行启动和停止服务。此处将服务名设置为“MySQL80”。如果希望开机自启动服务，也可以勾选“Start the MySQL Server at System Startup”选项（推荐）。
下面是选择以什么方式运行服务？可以选择“Standard System Account”(标准系统用户)或者“Custom User”(自定义用户)中的一个。这里推荐前者。

步骤六：单击“Next”（下一步）按钮，打开确认设置服务器窗口，单击“Execute”（执行）按钮。

步骤七：完成配置，如图所示。单击“Finish”（完成）按钮，即可完成服务器的配置。

步骤八：如果还有其他产品需要配置，可以选择其他产品，然后继续配置。如果没有，直接选择“Next”（下一步），直接完成整个安装和配置过程。

步骤九：结束安装和配置。

设置环境变量

如果不配置MySQL环境变量，就不能在命令行直接输入MySQL登录命令。

配置MySQL的环境变量的步骤：
步骤一：在桌面上右击【此电脑】图标，在弹出的快捷菜单中选择【属性】菜单命令。
步骤二：打开【系统】窗口，单击【高级系统设置】链接。
步骤三：打开【系统属性】对话框，选择【高级】选项卡，然后单击【环境变量】按钮。
步骤四：打开【环境变量】对话框，在系统变量列表中选择path变量。
步骤五：单击【编辑】按钮，在【编辑环境变量】对话框中，将MySQL应用程序的bin目录（C:\Program Files\MySQL\MySQL Server 8.0\bin）添加到变量值中。
步骤六：添加完成之后，单击【确定】按钮，这样就完成了配置path变量的操作，然后就可以直接输入MySQL命令来登录数据库了。

安装失败解决方案

问题一：无法打开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://docs.microsoft.com/en-US/cpp/windows/latest-supported-vc-redist?view=msvc-170下载相应的环境。

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

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

MySQL的登录

服务的启动与停止

MySQL安装完毕之后，需要启动服务器进程，不然客户端无法连接数据库。
在前面的配置过程中，已经将MySQL安装为Windows服务，并且勾选当Windows启动、停止时，MySQL也自动启动、停止。

方式一：使用图形界面工具
步骤1：打开Windows服务。

• 方式（1）：计算机（点击鼠标右键）→ 管理（点击）→ 服务和应用程序（点击）→ 服务（点击）
• 方式（2）：控制面板（点击）→ 系统和安全（点击）→ 管理工具（点击）→ 服务（点击）
• 方式（3）：任务栏（点击鼠标右键）→ 启动任务管理器（点击）→ 服务（点击）
• 方式（4）：单击【开始】菜单，在搜索框中输入“services.msc”，按Enter键确认。

步骤2：找到MySQL80（点击鼠标右键）→ 启动或停止（点击）

方式二：使用命令行工具。
启动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的用户名为：root，密码为：root

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

举例：
mysql -uroot -proot
mysql -h localhost -P 3306 -u root -proot

注意：
1、-p与密码之间不能有空格，其他参数名与参数值之间可以有空格也可以没有空格。如：

mysql -hlocalhost -P3306 -uroot -proot

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服务版本的信息：

mysql -V
mysql --version

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

mysql>select version();

退出登录

exit或quit或ctrl+c强制退出。

MySQL的使用

使用演示

一、查看所有的数据库

show databases;

“information_schema”是 MySQL 系统自带的数据库，主要保存 MySQL 数据库服务器的系统信息，比如数据库的名称、数据表的名称、字段名称、存取权限、数据文件所在的文件夹和系统使用的文件夹，等等。
“performance_schema”是 MySQL 系统自带的数据库，可以用来监控 MySQL 的各类性能指标。
“sys”数据库是 MySQL 系统自带的数据库，主要作用是以一种更容易被理解的方式展示 MySQL 数据库服务器的各类性能指标，帮助系统管理员和开发人员监控 MySQL 的技术性能。
“mysql”数据库保存了 MySQL 数据库服务器运行时需要的系统信息，比如数据文件夹、当前使用的字符集、约束检查信息，等等。

字符编码

MySQL8.x及以上版本默认编码是utf-8，而MySQL5.x默认编码不是utf-8。所以如果安装MySQL5.x版本则需要修改编码。

查看所有字符集：SHOW VARIABLES LIKE ‘character_set_%’;
设置当前连接的客户端字符集 “SET NAMES GBK;”

修改数据库和表的字符编码：
1、停止服务。
2、修改my.ini文件。
3、重新启动服务。

说明：
如果是在修改my.ini之前建的库和表，那么库和表的编码还是原来的Latin1，此时要么删了重建，要么使用alter语句修改编码。
create database 0728db charset Latin1;
use 0728db;
show create table student\G;
alter table student charset utf8; #修改表字符编码为utf-8。
show create table student\G;
alter table student modify name varchar(20) charset utf8; #修改字段字符编码为utf-8。
show create table student\G;
show create database 0728db;
alter database 0728db charset utf8; #修改数据库的字符编码为utf-8。

mysql> create database 0728db charset Latin1;
Query OK, 1 row affected (0.00 sec)
mysql> use 0728db;
Database changed
北京宏福校区：010-56253825  深圳西部硅谷校区：0755-23060254  上海大江商厦校区：021-57652717
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
mysql> show create table student\G
*************************** 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 0728db;;
+--------+-----------------------------------------------------------------+
|Database| Create Database                         |
+------+-------------------------------------------------------------------+
|0728db| CREATE DATABASE `0728db` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+------+-------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> alter database 0728db charset utf8; #修改数据库的字符编码为utf8
Query OK, 1 row affected (0.00 sec)
mysql> show create database 0728db;
+--------+-----------------------------------------------------------------+
|Database| Create Database                         |
+--------+-----------------------------------------------------------------+
| 0728db | CREATE DATABASE `0728db` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+--------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

步骤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
步骤3、重启服务。
步骤4、查看编码。
命令：
show variables like ‘character_%’;
show variables like ‘collation_%’;

MySQL5.x在配置完以后，如何修改配置文件？
为什么要修改my.ini文件？ 默认的数据库使用的字符集是latin1，需要修改为：utf8
修改哪些信息？

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

修改完以后，需要重启服务。
net stop mysql服务名;
net start mysql服务名;

MySQL源码

进入MySQL下载界面，在“Select Operating System”选项选择“Source Code”。下载Windows版本选择“Windows (Architecture Independent), ZIP Archive”。

密码忘记问题

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、即可用修改后的新密码登陆。

SQL

SQL分类

• DDL（Data Definition Languages：数据定义语言）：这些语句定义了不同的数据库、表、视图、索引等数据库对象，还可以用来创建、删除、修改数据库和数据表的结构。
  主要的语句关键字包括：create、drop、alter、rename、truncate等。
• DML（Data Manipulation Language：数据操作语言）：用于添加、删除、更新、查询数据库记录，并检查数据的完整性。
  主要的语句关键字包括：insert、update、delete、select等。
• DCL（Data Control Language：数据控制语言）：用于定义数据库、表、字段、用户的访问权限和安全级别。
  主要的语句关键字包括：grant、roolback、commit、revoke、savepoint等。

因为查询语句使用的非常频繁，所以很多人把查询语句单拎出来一类：
DQL（数据查询语言）。
还有单独将 commit、 rollback取出来称为
TCL（Transaction Control Language：事务控制语言）。

CRUD：创建（create）、更新（update）、读取（retrieve）和删除（delete）

SQL规范

每条命令以;或\g或\G结束。

SQL大小写规范：
1、MySQL在Windows下是大小写不敏感的。
2、MySQL在Linux环境下是大小写敏感的。
数据库名、表名、表的别名、变量名是严格区分大小写的
关键字、函数名、列名(或字段名)、列的别名(字段的别名) 是忽略大小写的。

SQL注释

1、单行注释：
#注释文字(MySQL特有的方式)。
– 注释文字(–后面必须包含一个空格。)。
2、多行注释：/* 注释文字 */

表结构

表设计

字段命名规则

• 必须保证字段没有和保留字、数据库系统或常用方法冲突。如果坚持使用，请在SQL语句中使用`（着重号）引起来。
• 保持字段名和类型的一致性，在命名字段并为其指定数据类型的时候一定要保证一致性。假如数据类型在一个表里是整数，那在另一个表里可就别变成字符型了。

字段类型

字符

• char
  长度固定，不足使用空格填充；
  查询速度快，浪费空间；
  char(11)存储’abc’，占11位；
  最多容纳2000个字符。
• varchar
  长度不固定；
  查询速度慢，节省空间；
  varchar(11)存储’abc’，只占3位；
  最多容纳4000个字符。
  oracle中为varchar2。

面试题：char和varchar有什么区别？
char为定长字符串，char(n)，n最大为255
varchar为不定长字符串，varchar(n)，n最大长度为65535
比如：char(10)和varchar(10)存储abc，
char保存10个字符，abc占三个，其它会用空格填充；而varchar只用abc三个位置。

数字

• int int(3) oracle不支持，其使用number替代

• tinyint tinyint(1)

• long

• numeric(5,2) 3位整数，2位小数

• decimal和numeric日常理解为等效;decimal(8, 2)

  word:numeric [njuː’merɪk] adj.数字的；数值的 n.数；数字
  decimal ['desɪml] adj.小数的；十进位的 n.小数

日期

• date 包含年、月、日
• datetime 包含年、月、日和时、分、秒
• timestamp 时间戳，不是日期，而是从1970年1月1日到指定日期的毫秒数

面试题：datetime和timestamp有什么区别？
数据库字段提供对日期类型的支持有date（日期）、time（时间）、datetime（日期+时间）、timestamp（时间戳）
datetime：日期+时间，存储和显示是一样的。
timestamp：时间戳，存储的不是个日期，而是从1970年1月1日到指定日期的毫秒数。

图片

blob 二进制数据，可以存放图片、声音，容量4g。早期有这样的设计，但目前主流都不会直接存储这样的数据，而只存储其访问路径，文件放在磁盘上。

word:blob [blɒb] n.小圆块；小斑点；一滴 v.弄脏

其他

• text
• float
• double

约束（constraint）

主键约束

主键是一条记录的唯一标识，具有唯一性，不能重复。

drop table tb_user;

create table tb_user(
    id int,
    name varchar(30),
    primary key(id)
);

insert into tb_user (id, name) values (1, ‘jack’);

唯一约束

name字段创建了唯一约束，插入数据时数据库会进行检查，如果插入的值相同，就会检查报错。

drop table tb_user;

create table tb_user(
    id int,
    name varchar(30) unique not null,
    phone varchar(20) unique not null,
    email varchar(30) unique not null,
    primary key(id)
);

desc tb_user;

insert into tb_user (id, name) values (1, ‘jack’);
insert into tb_user (id, name) values (2, ‘jack’);

执行上面语句出错：

Query : INSERT INTO tb_user (id,NAME) VALUES(2,‘jack’)
Error Code : 1062
Duplicate entry ‘jack’ for key ‘name’

展示表结构:

desc tb_user;

非空约束

drop table tb_user;

create table tb_user(
    id int auto_increment,
    name varchar(30) unique not null,
    age int,
    phone varchar(20) unique not null,
    email varchar(30) unique not null,
    primary key (id)
);

desc tb_user;

id为自增主键，插入数据时，指定id为null值无效，数据库会自动用下一个id值替代。
age字段类型因为设置为null，所以插入数据时可以为null。

insert into tb_user (id, age) values(null, null);

默认约束

drop table if exists stu; #如果表存在则删除，慎用,会丢失数据。

create table stu(
    id int primary key not null auto_increment, #自增主键
    name varchar(50) not null unique, #非空，唯一索引
    sex char(2) default ‘男’, #默认值
    phone char(18),
    age int,
    createdTime date default now()
);

desc stu;

主键、外键、唯一索引的区别

• Primary Key 主键约束，自动创建唯一索引
• Foreign Key 外键约束，外键字段的内容是引用另一张表的字段内容。
• Unique Index 唯一索引，唯一值但不是主键，

对于约束的好处是，数据库会进行检查，违反约束会报错，操作失败。数据库提供了丰富的约束检查，还有其他约束，但现今弱化关系型数据库的前提下，基本已经很少使用，记住上面三个即可。

表操作

创建表

学生表

create table student(
    id int(4), #学号
    name varchar(20), #姓名
    sex char(2), #性别
    birthday date, #出生日期
    salary numeric(7, 2) #奖学金
);

班级表

create table class(
    classid int, #班级编号
    name varchar(30) #班级名称
);

查看表结构

desc student;
desc class;

注意：SQL不区分大、小写，MySQL中习惯表名、字段名全部大写，oracle中则习惯小写。

修改表

添加字段

alter table student add(classid int);

修改字段长度
注意长度不能小于已有的数据长度，否则会被永久破坏，不可修复。

alter table student modify column name varchar(20);

删除字段

alter table student drop column classid;

修改表名

rename table student to tb_student;

删除表

删除表中数据（清空）

delete from tb_student;
delete from class;

删除表；数据都被删除，数据将永久性丢失。

drop table tb_student;
drop table class;

drop、delete和truncate之间的区别

drop删除库或者表。数据和结构定义都会被删除。
delete和truncate只是删除表的数据。
delete可以指定where条件，删除满足条件的记录，tuncate删除所有记录。
对于自增字段的表，delete不会自增值清零，而truncate是把表记录和定义都删除，然后重建表的定义，所以自增主键会重新开始计数。

delete from student; #删除表中的所有数据，主键自增值不会重新计数。
truncate student; #删除表中的所有数据，主键自增值会从1开始计数。

drop table student; #删除表，包括表结构和表中的所有数据。

数据操作（CRUD）

新增

不声明字段，顺序必须和表的一致。

insert into student values (1, ‘宋江’, ‘男’, ‘1974-10-11’, 3000);

声明全部字段，顺序可以不一致，推荐方式。

insert into student (id, name, sex, salary, birthday) values (2, ‘卢俊义’, ‘女’, 30000, ‘2020-02-02’);

声明部分字段，没有的字段默认值null。

insert into student (id, name) values (3, ‘宋江’);

修改

修改一个字段，必须设置where条件

update student set sex=‘女’ where id=1;

修改多个字段，必须设置where条件。

update student set name=‘吴用’, birthday=‘2020-02-05’ where id=2;

修改某个字段；设置为null。

update student set name=null where id=3;

修改

update student set name=‘吴用’;

删除

删除一条记录，必须有where条件。

delete from student where id=3;

删除所有数据

delete from student;

查询

别名

起别名时，as都可以省略。
如果字段别名中没有空格，那么可以省略"“。
如果字段别名中有空格，那么不能省略”"。

数据导入指令

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

source d:\one_database.sql

空值（null）

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

select employee_id “员工编号”, salary “月薪”, commission_pct “奖金率”, salary*12*(1 + ifnull(commission_pct, 0)) “年薪” from employees;

伪表

select 1+1, 2*3;
select 1+1, 2*3 from dual; #dual伪表。

着重号

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

select * from `order`;

查询常数

select ‘宋江’ as corporation, last_name from employees;

查看表结构

describe employees;
desc employees;

别名

select employee_id 员工编号, last_name as 员工姓名, department_id “部门编号”, salary * 12 as “annual sal”
from employees;

distinct

select distinct department_id
from employees;

where

select *
from employees
where last_name=‘King’;

select employee_id, last_name, job_id, department_id
from employees
where department_id=90 ;

运算符

算术运算符

运算符	名称	作用	示例
+	加法运算符	计算两个值或表达式的和	SELECT A+B
-	减法运算符	计算两个值或表达式的差	SELECT A-B
*	乘法运算符	计算两个值或表达式的乘积	SELECT A*B
/或DIV	除法运算符	计算两个值或表达式的商	SELECT A/B或SELECT A DIV B
%或MOD	求模（余）运算符	计算两个值或表达式的余数	SELECT A%B或SELECT MOD B

加法与减法运算符

案例：

SELECT 1, 2+0, 3-0, 4+5, 6+2*3, 1+2.1, 3-11, 3-1.4
FROM DUAL;

SELECT 100+‘1’
FROM DUAL; #result:101

SELECT 100 + ‘a’ #此时将’a’看做0处理。
FROM DUAL;

SELECT 100 + NULL # null值参与运算，结果为null。
FROM DUAL;

1、一个整数类型的值对整数进行加法和减法操作，结果是一个整数。
2、一个整数类型的值对浮点数进行加法和减法操作，结果是一个浮点数。
3、在MySQL中“+”运算符只表示数值相加。如果遇到非数值类型，先尝试转成数值，如果转失败，就按0计算。（MySQL中字符串拼接要使用字符串函数CONCAT()实现。）。

乘法与除法运算符

SELECT 100, 1001, 1001.0, 100/1.0, 100/2, 100+2*5/2, 100/3, 100 DIV 0 #分母如果为0，则结果为null。
FROM DUAL;
#result:100 100 100.0 100.0000 50.0000 105.0000 33.3333 Null

SELECT employee_id,salary,salary * 12 annual_sal
FROM employees;

1、一个数乘以整数1和除以整数1后仍得原数。
2、一个数乘以浮点数1和除以浮点数1后变成浮点数，数值与原数相等。
3、一个数除以整数后，不管是否能除尽，结果都为一个浮点数。
4、一个数除以另一个数，除不尽时，结果为一个浮点数，并保留到小数点后4位。
5、乘法和除法的优先级相同，进行先乘后除操作与先除后乘操作，得出的结果相同。
6、在数学运算中，0不能用作除数，在MySQL中，一个数除以0为null。

取模（余）运算符

取模运算：%或mod。

SELECT 12 % 3, 12 % 5, 12 MOD -5, -12 % 5, -12 % -5
FROM DUAL; #result:0 2 2 -2 -2

SELECT 12 % 3, 12 MOD 5
FROM DUAL; #result:0 2

#查询employee_id是偶数的员工。
SELECT * FROM employees
WHERE employee_id MOD 2 = 0;

#查询员工id为偶数的员工信息。
SELECT employee_id,last_name,salary
FROM employees
WHERE employee_id % 2 = 0;

比较运算符

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

运算符	名称	作用	示例
=	等于运算符	判断两个值、字符串或表达式是否相等	SELECT C FROM TABLE WHERE A = B
<=>	安全等于运算符	安全地判断两个值、字符串或表达式是否相等	SELECT C FROM TABLE WHERE A <=> B
<>或!=	不等于运算符	判断两个值、字符串或表达式是否不相等	SELECT C FROM TABLE WHERE A <> B或SELECT C FROM TABLE WHERE A != B
<	小于运算符	判断前面的值、字符串或表达式是否小于后面的值、字符串或表达式	SELECT C FROM TABLE WHERE A < B
<=	小于等于运算符	判断前面的值、字符串或表达式是否小于等于后面的值、字符串或表达式	SELECT C FROM TABLE WHERE A <= B
>	大于运算符	判断前面的值、字符串或表达式是否大于后面的值、字符串或表达式	SELECT C FROM TABLE WHERE A > B
>=	大于等于运算符	判断前面的值、字符串或表达式是否大于等于后面的值、字符串或表达式	SELECT C FROM TABLE WHERE A >= B

等于运算符

等号运算符（=）判断等号两边的值、字符串或表达式是否相等，如果相等则返回1，不相等则返回0。

在使用等号运算符时，遵循如下规则：
1、如果等号两边的值、字符串或表达式都为字符串，则MySQL会按照字符串进行比较，其比较的是每个字符串中字符的ANSI编码是否相等。
2、如果等号两边的值都是整数，则MySQL会按照整数来比较两个值的大小。
3、如果等号两边的值一个是整数，另一个是字符串，则MySQL会将字符串转化为数字进行比较。
4、如果等号两边的值、字符串或表达式中有一个为NULL，则比较结果为NULL。

SQL中赋值符号使用:=

SELECT 1 = 1, 1 = ‘1’, 1 = 0, ‘a’ = ‘a’, (5 + 3) = (2 + 6), ‘’ = NULL , NULL = NULL
FROM DUAL; #result:1 1 0 1 1 Null Null

SELECT 1 = 2, 1 != 2, 1 = ‘1’, 1 = ‘a’, 0 = ‘a’ #字符串存在隐式转换。如果转换数值不成功，则看做0。
FROM DUAL; #result:0 1 1 0 1

SELECT 1 = 2, 0 = ‘abc’, 1 = ‘abc’
FROM DUAL; #result:0 1 0

#查询salary=10000。
SELECT employee_id,salary
FROMJ employees
WHERE salary = 10000;

SELECT 1 <=> ‘1’, 1 <=> 0, ‘a’ <=> ‘a’, (5 + 3) <=> (2 + 6), ‘’ <=> NULL, NULL <=> NULL
FROM DUAL; #result:1 0 1 1 0 1

#查询commission_pct等于0.40
SELECT employee_id,commission_pct
FROM employees
WHERE commission_pct = 0.40;

SELECT employee_id,commission_pct
FROM employees
WHERE commission_pct <=> 0.40;

SELECT ‘a’ = ‘a’,‘ab’ = ‘ab’,‘a’ = ‘b’ #两边都是字符串的话，则按照ANSI的比较规则进行比较。
FROM DUAL; #result:1 1 0

SELECT 1 = NULL,NULL = NULL # 只要有null参与判断，结果就为null。
FROM DUAL; #result:Null Null

SELECT last_name,salary,commission_pct
FROM employees
WHERE salary = 6000;

SELECT 1 <=> 2,1 <=> ‘1’,1 <=> ‘a’,0 <=> ‘a’
FROM DUAL; #result:0 1 0 1

SELECT 1 <=> NULL, NULL <=> NULL
FROM DUAL; #result:0 1

#查询表中commission_pct为null的数据有哪些
SELECT last_name,salary,commission_pct
FROM employees
WHERE commission_pct <=> NULL;

使用安全等于运算符时，两边的操作数的值都为NULL时，返回的结果为1而不是NULL，其他返回结果与等于运算符相同。

不等于运算符

不等于运算符（<>和!=）用于判断两边的数字、字符串或者表达式的值是否不相等，如果不相等则返回1，相等则返回0。不等于运算符不能判断NULL值。如果两边的值有任意一个为NULL，或两边都为NULL，则结果为NULL。

SELECT 3 <> 2,‘4’ <> NULL, ‘’ != NULL, NULL != NULL
FROM DUAL; #result:1 Null Null Null

SELECT 1 <> 1, 1 != 2, ‘a’ != ‘b’, (3+4) <> (2+6), ‘a’ != NULL, NULL <> NULL;
FROM DUAL; #result:0 1 1 1 Null Null

非符号运算符

运算符	名称	作用	示例
IS NOT NULL	不为空运算符	判断值、字符串或表达式是否不为空	SELECT B FROM TABLE WHERE A IS NOT NULL
LEAST	最小值运算符	在多个值中返回最小值	SELECT D FROM TABLE WHERE C LEAST(A, B)
GREATEST	最小值运算符	在多个值中返回最小值	SELECT D FROM TABLE WHERE C GREATEST(A, B)
BETWEEN AND	两值之间的运算符	判断一个值是否在两个值之间	SELECT D FROM TABLE WHERE C BETWEEN A AND B
ISNULL	为空运算符	判断一个值、字符串或表达式是否为空	SELECT B FROM TABLE WHERE A ISNULL
IN	属于运算符	判断一个值是否为列表中的任意一个值	SELECT D FROM TABLE WHERE C IN(A, B)
NOT IN	不属于运算符	判断一个值是否不是一个列表中的任意一个值	SELECT D FROM TABLE WHERE C NOT IN(A, B)
LIKE	模糊匹配运算符	判断一个值是否符合模糊匹配规则	SELECT C FROM TABLE WHERE A LIKE B
REGEXP	正则表达式运算符	判断一个值是否符合正则表达式的规则	SELECT C FROM TABLE WHERE A REGEXP B
RLIKE	正则表达式运算符	判断一个值是否符合正则表达式的规则	SELECT C FROM TABLE WHERE A RLIKE B

空运算符（IS NULL或者ISNULL）

#判断一个值是否为NULL，如果为NULL则返回1，否则返回0。
SELECT NULL IS NULL, ISNULL(NULL), ISNULL(‘a’), 1 IS NULL
FROM DUAL; #result:1 1 0 0

#查询commission_pct等于NULL。
SELECT employee_id,commission_pct
FROM employees
WHERE commission_pct IS NULL;
或
SELECT employee_id,commission_pct
FROM employees
WHERE commission_pct <=> NULL;
或
SELECT employee_id,commission_pct
FROM employees
WHERE ISNULL(commission_pct);

SELECT last_name, manager_id
FROM employees
WHERE manager_id IS NULL;

#查询表中commission_pct为null的数据。
SELECT last_name,salary,commission_pct
FROM employees
WHERE commission_pct IS NULL;
#或
SELECT last_name,salary,commission_pct
FROM employees
WHERE ISNULL(commission_pct);

非空运算符

非空运算符（IS NOT NULL）判断一个值是否不为NULL，如果不为NULL则返回1，否则返回0。

SELECT NULL IS NOT NULL, ‘a’ IS NOT NULL, 1 IS NOT NULL
FROM DUAL; #result:0 1 1

SELECT employee_id,commission_pct
FROM employees
WHERE NOT commission_pct <=> NULL;
#或
SELECT employee_id,commission_pct
FROM employees
WHERE NOT ISNULL(commission_pct);

#查询表中commission_pct不为null的数据。
SELECT last_name,salary,commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;
#或
SELECT last_name,salary,commission_pct
FROM employees
WHERE NOT commission_pct <=> NULL;

最小值运算符

最大值运算符

BETWEEN AND运算符

IN运算符

NOT IN运算符

LIKE运算符

ESCAPE

REGEXP运算符

逻辑运算符

逻辑非

逻辑与

逻辑或

逻辑异或

位运算符

查询所有数据

select * from student;

查询id为1的记录

select * from student where id=1;

查询姓名为呼延灼的记录

select * from student where name = ‘呼延灼’;

查询姓呼的记录

select * from student where name like ‘呼%’;

查询姓名中含有呼的记录

select * from student where name like ‘%呼%’;

日期字段的年月日

select year(birthday) as 年, year(birthday) 年, month(birthday) 月, day(birthday) 日 from student;

某年的记录

select * from student where year(birthday) = 1988;

某个日期段的记录

select * from student
where year(birthday)>=1988 and year(birthday)<=2020;

select * from student
where year(birthday) between 1988 and 2020;

相关null

select * from student where sex = null;
select * from student where sex is null;
select * from student where sex is not null;

非，不包括null

select * from student where not sex=‘男’;
select * from t_user where not username=“宋江”;

排序，默认正序，desc倒序

select * from student
    where salary
    is not null
    order by salary;

select * from student
    where salary
    is not null
    order by salary asc;

select * from student
    where salary
    is not null
    order by salary desc;

保存点（savepoint）

可以看到删除数据是非常危险的，那如果我们直接在企业的生产环境操作这样的语句怎么防止误操作呢？
先设置手动提交，默认是自动提交

set @@autocommit = 0; 默认是1，手动提交
savepoint chen;
delete from student;
rollback to chen;
release savepoint chen;
先设置存储点，如果发现发现误操作删了数据，还可以回滚
set @@autocommit=0;
begin;
……语句
rollback;

事务

事务的特性

事务必须满足4个条件（ACID）：

• 原子性（Atomicity，或称不可分割性） atomicity [ˌætəmˈɪsɪti] n. 原子数; 原子价; 原子化合 力; 原子力;
• 一致性（Consistency） consistency [kənˈsɪstənsi] n.连贯性，一致性; 稠度;
• 隔离性（Isolation，又称独立性） isolation [ˌaɪsəˈleɪʃn] n.隔离; 隔离状态; 孤独; 孤立状态;
• 持久性（Durability） durability [ˌdjʊərə’bɪlətɪ] n.耐久性; 持久性;

• 原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中如果发生错误，会被回滚到事务开始前的状态，就像这个事务从来没有执行过一样。
• 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
• 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
• 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

事务的四大特性：原子性，一致性，隔离性，持久性。

• 原子性（Atomicity）
  原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，这和事务的功能是一样的概念，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。
• 一致性（Consistency）
  一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。
• 隔离性（Isolation）
  隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。即要达到这么一种效果：对于任意两个并发的事务T1和T2，在事务T1看来，T2要么在T1开始之前就已经结束，要么在T1结束之后才开始，这样每个事务都感觉不到有其他事务在并发地执行。
• 持久性（Durability）
  持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

下面举例说明，首先我们创造一张表；一列是用户，一列是金额。

id	user	money
1	A	1000
2	B	1000

(1) 原子性（Atomicity）
现在有一个任务要让A账户向B账户转100元，那么就需要执行两句：
第一个是A账户-100
第二个是B账户+100
　　
原子性就是保证这两条数据要么都成功要么都不成功，否则就会出现总数多出100或者少100的情况，这样就会造成顾客损失或者公司损失，所以出现不成功或者成功一半就要回滚。
⑵ 一致性（Consistency）
拿转账来说，假设用户A和用户B两者的钱加起来一共是2000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加总数还得是2000，这就是事务的一致性。
⑶ 隔离性（Isolation）
比如一个人A在发出转账请求时，B去同时查看两个账户余额，B要么看见的都是转账前的状态，都是1000块，要么看到的都是转账后的状态，一个900，一个1100，而不会看到一个900，一个1000或者一个1000，一个1100这种中间状态。

关于事务的隔离性数据库提供了多种隔离级别。
⑷ 持久性（Durability）
例如我们在使用JDBC操作数据库时，在提交事务方法后，提示用户事务操作完成，当程序执行完成直到看到提示后，就可以认定事务以及正确提交，即使这时候数据库出现了问题，也必须要将我们的事务完全执行完成，否则就会造成我们看到提示事务处理完毕，但是数据库因为故障而没有执行事务的重大错误。

数据库隔离级别

1、读未提交（Read Uncommited）
指的是一个事务读取到另外一个事务还没有提交的内容。这种情况是必须要避免的。因为其他事务未提交的数据，是随时有可能进行回滚的，所以，任何时候，都不应该允许程序读取到某个事务还未提交的数据。如果读取到了别的事务未提交的数据，这种情况称为脏读。要想解决脏读的问题，可以提高数据库的事务隔离级别，把事务隔离级别设置为读已提交。
2、读已提交（Read Committed）
这个隔离级别可以解决脏读的问题。
在该隔离级别下，不允许2个未提交的事务之间并行执行，但它允许在一个事务执行的过程中，另外一个事务得到执行并提交。这样，会出现一种情况，第一个事务前后两次select出来的某行数据，值可能不一样。值改变的原因是，穿插执行的事务2对该行数据进行了update操作。在同一个事务中，两次select出来的值不相同的问题称为不可重复读问题。要想解决不可重复读问题，需要把数据的隔离级别设置为可重复读。
3、可重复读（Repeatable Read）
在这个隔离级别下，可以解决不可重复读的问题。
在该隔离级别下，在一个事务使用某行的数据的过程中，不允许别的事务再对该行数据进行操作。可重复读应该是给数据库的行加上了锁。这种隔离级别下，依旧允许别的事务在该表中插入和删除数据，于是就会出现，在事务1执行的过程中，如果先后两次select出符合某个条件的行，如果在这两次select直接另一个事务得到了执行，insert或delete了某些行，就会出现先后两次select出来的符合同一个条件的结果不一样，第一次select好像出现了幻觉一样，因此，这个问题也被称为幻读。要想解决幻读问题，需要将数据库的隔离级别设置为串行化。
4、串行化（Serialization）
串行化可以解决幻读的问题。
它要求事务的执行完全串行执行。所以失去了并发的效率。

Mysql的默认隔离级别为可重复读。
总而言之，数据的事务隔离级别分为4种，从低到高依次为读未提交，读已提交，可重复读，串行化。与数据库事务隔离级别相关的问题有3个，分别是脏读，不可重复读，幻读。
脏读问题需要用读已提交来解决，但读已提交会存在不可重复读问题。
不可重复读问题需要用可重复读来解决，但可重复读会存在幻读问题。
幻读问题需要用串行化来解决。

前提

• 在MySQL中只有使用了InnoDB数据库引擎的数据库或表才支持事务。
• 事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行。
• 事务用来管理insert、update、delete语句，因为这些操作才会“破坏”数据，查询（select）语句是不会的。
• 默认MySQL数据库的事务是开启，执行SQL后自动提交。
  

提交（commit）

多条语句时，批量执行，事务提交。

set @@autocommit = 0;
begin;
insert into student (id) values (5);
update student set name=‘陈曦’ where id=5;
commit;

有了事务，多步操作就形成了原子性操作，高并发下也不会引起数据错乱。

回滚（rollback）

多条语句，批量执行，insert插入重复的主键导致失败，事务回滚。

set @@autocommit = 0;
begin;
insert into student (id) values (5);
update student set name=‘陈曦2’ where id=5;
rollback;