首页  思维导图  详情

MySQL 8.0

2020-07-02 17:21:37   0  举报





仅支持查看

AI智能生成

MySQL 8.0

作者其他创作

大纲/内容

定义

由一批数据构成有序的集合，这些数据被存放在结构化的数据表里。数据表之间相互关联，反应了客观事物间的本质联系。数据库系统提供对数据的安全控制和完整性控制。

表

一系列二维数组的集合，用来存住数据和操作数据的逻辑结构。它由纵向的列和横向的行组成。行被称为记录，是组织数据的单位；列被称为字段，每一列表示记录的一个属性，有相应的描述信息，如数据类型、数据宽度等

主键

又称主码，用于唯一地标识表中的每一条记录。可以定义表中的一列或多列为主键，主键列上既不能有两行相同的值，也不能为空值。

特点

实现数据共享、减少数据冗余；采用特地给的数据类型；具有较高的数据独立性；具有统一的数据控制功能

数据类型

决定了数据再计算机中的存储格式，代表不同的信息类型。常用的数据类型有整数、浮点数、精确小数、二进制、日期时间、字符串等。

数据库系统

由硬件部分和软件部分共同构成。硬件部分主要用于存储数据库中的数据，包括计算机、存储设备等。软件部分主要包括DBMS、支持DBMS运行的操作系统，以及支持多种语言进行应用开发的访问技术等。数据库系统有3哥主要的组成部分：数据库，用于存储数据的地方；数据库管理系统，用于管理数据库的软件；数据库应用程序，为了提高数据库系统的处理能力所使用的管理数据库的软件补充。

数据库系统：提供了一个存储空间，用以存储各种数据，可以将数据库市委一个存储数据的容器。一个数据库可能包含许多文件，一个数据库系统中通常包含许多数据库

数据库管理系统：是用户创建、管理和维护数据库时所使用的软件，位于用户与操作系统之间，对数据库进行统一管理。DBMS能定义数据存储结构，提供数据的操作机制，维护数据库的安全习惯、完整性和可靠性。

数据库应用程序：虽然已经有了DBMS，但是在很多情况下，DBMS无法满足对数据管理的要求。数据库应用程序的使用可以满足对数据管理的更高要求，还可以是数据管理古城更加直观和友好。数据库应用程序负责与DBMS进行通信，访问和管理DBMS中存储的数据，允许用户插入、修改、删除DB中的数据。

SQL

对数据库进行查询和修改操作的语言叫SQL。SQL的含义是结构化查询语言（Structured Query Language）。SQL有3个主要的版本：ANSI（美国国家标准机构）SQL；对ANSI SQL 修改后再1992年采纳的标准，称为SQL-92或SQL2；最近的SQL-99标准，从SQL2扩充而来，并增加了对象关系特征和许多其他新功能。

数据定义语言（DDL）：DROP、CREATE、ALTER等语句

数据操作语言（DML）：INSERT、UPDATE、DELETE等语句

数据查询语句（SQL）：SELECT语句

数据控制语句（DCL）：GRANT、REVOKE、COMMIT、RELLBACK等语句

数据库访问接口

ODBC、JDBC、ADO.NET、PDO

MySQL Server版本

MySQL Community Server 社区版本，开源免费

MySQL Enterprise Server 企业版闭源收费

MySQL服务端实用工具

mysqld：SQL后台程序（MySQL服务器进程），必须再该程序运行之后，客户端才能通过链接服务器访问数据库

mysqld_safe:服务器启动脚本

mysql.server：服务器启动脚本。该脚本用于使用包含为特定级别的、运行启动服务的脚本的、运行目录的系统

mysql_multi：服务器启动脚本，可以启动或停止系统上的安装的多个服务器

myisamchk:用来描述、检查、优化和维护myisam表的实用工具

mysqlbug：MySQL缺陷报告脚本。它可以用来想MySQL邮件系统发送缺陷报告

mysql_install_db该脚本用默认权限创建MySQL授权表。通常只是在系统上首次安装MySQL时执行一次。

MySQL客户端实用工具

myisampack 压缩myisam表，以产生更小的只读表的一个工具

mysql 交互式输入SQL语句或从文件以批处理模式执行他们的命令行工具

mysqlaccess 检查访问主机名、用户名和数据库组合的权限的脚本

mysqladmin 执行管理操作的客户程序。例如创建或删除数据库、重载授权表、将表刷新到硬盘上以及重新打开日志文件。myssqladmin还用来检索版本、进程，以及服务器的状态信息

mysqlbinlog 从二进制日志读取语句的工具。在二进制日志文件中包含执行过的语句，可以用来帮助系统从崩溃中恢复

mysqlcheck 检查恢复分析以及优化表的表维护客户程序

mysqldump 将MySQL数据库转出道一个文件（例如sql语句或tab分隔符文本文件）的客户端程序

mysqlhotcopy 当服务器在运行时，快速备份myisam或isam表的工具

mysqlimport 使用load data infile将文本文件导入相关表的客户程序

mysqlshow 显示数据库、表、列以及索引相关信息的客户程序

perror 显示系统或MySQL错误代码含义的工具

MySQL 8.0新特性

数据字典

MySQL8.0包含一个事务数据字典，用于存储有关数据对象的信息。在MySQL8.0之前的版本中，字典数据存储在元数据文件和非事务表中

原子数据定义语句

MySQL8.0支持原子数据定义语言（DDL）语句。此功能称为原子DDL。原子DDL语句将与DDL操作关联的数据字典更新，存储引擎操作和二进制日志写入组合到单个原子事务中。即使服务器在操作期间暂停，也会提交事务，并将适用的更改保留到数据字典、存储引擎和二进制日志，或者回滚事务。通过在MySQL8.0中引入MySQL数据字典，可以实现原子DDL。在早期的MySQL版本中，元数据存储在元数据文件、非事务性表和存储引擎特定的字典中，需要中建提交。MySQL数据字典提供的集中式事务元数据存储消除了这一障碍，即使将DDL语句操作重组为原子事务成为可能。

安全和账户管理

MySQL数据库的授权表统一为InnoDB（事务性）表。每个语句都是事务性的，并且对所有创建的用户都是成功或者回滚，发生任何错误都无效。如果成功，就将语句写入二进制日志；如果失败则不写入，发生回滚并且不进行任何更改。

MySQL8.0开始支持角色，角色可以看成时一些权限的集合，为用户赋予统一的角色，权限的修改直接通过角色来进行，无须为每个用户单独授权。管理员可以创建和删除角色。

MySQL8.0开始维护有关密码历史的信息，从而限制了以前密码的重用。管理员可以在全局以及每个账户的基础上建立密码常用策略，从而在密码更改时限制使用以前使用过的密码

MySQL允许账户具有双密码，从而在多服务器系统中无缝地执行分阶段密码更改，无须停机

资源管理

MySQL现在支持资源组的创建和管理，并允许将服务器内运行的线程分配给特定的资源组。资源组属性可以控制其资源，以弃用或限制资源组中线程的资源消耗。数据库管理员可以根据不同的工作负载修改这些属性

InnoDB增强功能

MySQL8.0将自增主键的计数器持久化到重做日志中。每次计数器发生改变，都会将其写入重做日志中，如果数据库重启，innodb会根据重做日志中的信息来初始化计数器的内存值。为了尽量减小对系统性能的影响，计数器写入重做日志时，并不会马上刷新数据库系统

如果索引损坏，innodb将索引损坏标志写入重做日志，从而使得损坏标志安全。innodb还将内存中损坏标志数据写入每个检查点上的引擎专用系统表。在恢复期间，innodb从两个位置读取标志并在将内存表和索引对象标记为损坏之前合并结果。

新的动态变量innodb_deadlock_detect 可用于禁用死锁检测。在高兵法系统上，当许多线程等待同一个锁时，思索检测会导致速度减慢，此事禁用思索检测可能更有效。

字符集支持

默认字符集已经由litin1更改为utf8mb4.该utf8mb4字符集有几个新的排序规则，其中包括utf8mb4_ja_0900_as_cs

增强JSON功能

增加了->>运算符，相当于调用JSON_UNQUOTE()的结果

结果聚合为单个JSON数组。JSON_OBJECTAGG()取两个列或表达式，将其解释为键和值，并将结果作为单个JSON对象返回。

添加了JSON使用程序功能JSON_PRETTY（），JSON以易于阅读的格式输出先有值；每个JSON对象成员或数组值都打印在一个单独的行上，自对象或数组相对与其父对象是2个空格

添加的json_merge_patch()可以合并符合rfc 7396标准的json。在两个json对象上使用时，可以将他们合并为单个json对象

数据类型打支持

MySQL8.0支持将表达式用作数据类型的默认值，包括BLOB、TEXT、GEOMETRY和JSON数据类型，在以前的版本中是根本不会被分配默认值的。

查询的优化

MySQL8.0开始支持不可见索引，优化器根本不适用不可见索引，但会以其他方式正常维护。默认情况下，索引是可见的，数据库管理员可以检测索引对查询性能的影响，而不会进行破坏性的更改。

MySQL8.0开始支持降序索引。DESC在索引定义中不再被忽略，而且会降序存储索引字段。

共用表表达式

MySQL现在支持非递归和递归的共用表表达式。公用表表达式允许使用命名的临时结果集，通过允许with语句之前的select和某些其他语句来实现。

窗口函数

MySQL8.0版本中，新增了一个窗口函数，用它可以实现很多新的查询方式。窗口函数类似与sum（）、count()那样的集合函数，但它并不会将多行查询结果合并为一行，而是将结果放回多行当中。

统计直方图

MySQL8.0实现了统计直方图。利用直方图，用户可以对一张表的一列做数据分布的统计，特别是针对没有索引字段。这可以帮助查询优化器找到更有的执行计划。

备份锁

新类型的备份锁在联机备份期间允许DML，同时防止可能导致快照不一致的操作。新的备份锁由LOCK INSTANCE FOR BACKUP和UNLOCK INSTANCE语法支持。管理员拥有BACKUP_ADMIN权限才能使用这些语句。

数据库基本操作

CREATE DATABASE DATABASE_NAME;

drop databse DATABASE_NAME;

show create database DATABASE_NAME \G;

SHOW DATABSES/SCHEMAS;

USE DATABASE_NAME;

数据表

创建表的语法形式：CREATE TABLE <表名> (字段1，数据类型 [列级别约束条件] [默认值]，字段2，数据类型 [列级别约束条件] [默认值]，。。。。。);

单主键约束

在定义列的同时指定单字段主键：字段名数据类型 PRIMARY KEY [默认值]

在定义完所有列之后指定主键：[CONSTRAINT <约束名>] PRIMARY KEY [字段名]

多字段联合主键

PRIMARY KEY [字段1，字段2，。。。，字段n]

使用外键约束

外键用来在两个表的数据之间建立连接，可以是一列或者多列。一个表可以有一个或多个外键。外键对应的是参照完整性，一个表的外键可以为空值，若不为空值，则每个外键值必须等于另一个表中主键的某个值

外键：首先他是表中的一个字段，虽然可以不是本表的主键，但要对应另外一个表的主键。外键的主要作用是保证数据引用的完整性，定义外键后，不允许删除在另一个表中具有有关联关系的行。外键的作用是保持数据的一致性、完整性。

主表（父表）：对于两个具有关联关系的表而言，相关联字段中主键所在的那个表即是主表

从表（字表）：对于两个具有关联关系的表而言，相关联字段中外键所在的那个表即是从表

语法： [CONSTRAINT <外键名>] FOREIGN KEY 字段名1 [，字段名2，....] REFERFNCES <主表名> 主键列1 [，主键列2，...]

“外键名”为定义的外键约束的名称，一个表中不能有相同名称的外键；“字段名”表示子表需要天剑外键约束的字段列；“主表名”即被字表外键所依赖的表的名称；“主键列”表示主表中定义的主键列，或者列组合。

关联指的是在关系型数据库中相关表之间的联系。它是通过相容或相同的属性或属性组来表示的。字表的外键必须关联父表的主键，且关联字段的数据类型必须匹配，如果类型不一样，则创建字表时，就会出现错误 “1005”：can't create table

非空约束

指字段的值不能为空。对于使用了非空约束的字段，如果用户在添加数据时没有指定值，数据库系统会报错。

语法：字段名数据类型 not null

唯一性约束

要求该列唯一，允许为空，但只能出现一个空值。唯一约束可以确保一列或者几列不出现重复值。

语法：字段名数据类型 UNIQUE

UNIQUE和PRIMARY KEY的区别：一个表中可以有多个字段声明为UNIQUE，但只能有一个PRIMARY KEY声明；声明为primary key的列不允许有空值，但是声明为ubique的字段允许空值（null）的存在。

默认约束

指定某列的默认值。如果插入一条新的记录时没有为这个字段赋值，那么系统会自动为这个字段赋值

语法：字段名数据类型 DEFAULT 默认值

设置表的属性值自动增加

在数据库应用中，经常希望在每次插入新纪录时，系统自动生成字段的主键值。可以通过为表主键添加AUTO_INCREMENT关键字来实现。默认的在MySQL中AUTO_INCREMENT的初始值是1，每新增一条记录，字段值自动加1.一个表只能有一个字段使用AUTO_INCREMENT约束，且该字段必须为主键的一部分。AUTO_INCREMENT约束的字段可以是任何整数类型（TINYINT、SMALLINT、INT、BIGINT等）

语法：字段名数据类型 AUTO_INCREMENT

查看数据表结构

使用SQL语句创建好数据表之后，可以查看表结构的定义，以确认表的定义是否正确。在MySQL中，查看表结构可以使用describe和show create table语句。

语法1：describe/desc 表名

语法2: show create table <表名\G>;

如果不加\G 参数，显示的结果可能非常混乱，加上参数\G之后，可似的显示结果更加直观，易于查看。

修改数据表

修改表指的是修改数据库中已经存在的数据表的结构。MySQL使用alter table语句修改表。常用的修改表的操作有修改表名、修改字段数据类型、增加和删除字段，修改字段的排列位置、更改表的存储引擎、删除表的外键约束等

修改表明

ALTER TABLE <旧表名> [TO] <新表名> ；

TO为可选参数，使用与否不影响结果

修改字段的数据类型

ALTER TABLE <表名> MODIFY <字段名> <数据类型>

“表名”指要修改数据类型的字段所在表的名称，“字段名”指需要修改的字段，“数据类型”指修改后字段的新数据类型

修改字段名

ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>；

“旧字段名”指修改前的字段名，“新字段名”是修改后的字段名，“新数据类型”指修改后的字段类型，如果不需要修改字段的数据类型，将新数据类型设置为与原来一样即可，但数据类型不能为空

CHANGE也可以只修改数据类型，实现和MODIFY同样的效果，方法是将SQL语句中的“新字段名”和“旧字段名”设置为相同的名称，只改变“新数据类型”。由于不同的数据类型在机器中的存储方式及长度并不相同，修改数据类型可能会影响到数据表中已有的数据记录，因此当数据库表中已有数据时，不要轻易修改数据类型。

添加字段

ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] [FIRST | AFTER 已存在字段名];

“新字段名”为需要添加的字段的名称；“FIRST”为可选参数，其作用是将新添加的字段设置为表的第一个字段；“AFTER”为可选参数，起作用是将新添加的字段添加到指定的“已存在字段名”的后面。

FIRST 或 AFTER 已存在的字段名用于指定新增字段在表中的位置，如果SQL语句中没有这两个参数，则默认将新增字段设置为数据表的最后列

添加无完整性约束条件的字段ALTER TABLE tb_dept1 ADD managerId INT(10);

添加有完整性越是条件的字段 ALTER TABLE tb_dept1 ADD column1 VARCHAR(12) NOT NULL;

在表的第一列添加一个字段 ALTER TABLE TB_DEPT1 ADD COLUMN2 INT(11) FIRST;

在表的指定列之后添加一个字段： ALTER TABLE TB_DEPT1 ADD COLUMN3 INT(11) AFTER NAME;

删除字段

删除字段是将数据表中的某个字段从表中移除，

ALET TABLE <表名> DROP <字段名>

修改字段的排列位置

ALTER TABLE 表名 MODIFY 字段1 数据类型 FIRST|AFTER 字段2

更改表的存储引擎

ALTER TABLE 表名 engine=更改后的存储引擎名；

删除表的外键约束

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

删除数据表

DROP TABLE [IF EXISTS] 表1，表2，。。。。；

删除被其他表关联的主表

先删除外键条件约束，再删除主表

数据类型

数值类型

TINYINT、SMAIIINT、MEDIUMINT、INT、BIGINT、浮点小数数据类型FLOAT和DOUBLE，定点小数类型DECIMAL

整数类型

主要用来存储数字，MySQL提供了多种数值数据类型不同的数据类型提供不同的取值范围，可以存储的值泛微越大，其所需要的存储空间也会越大。MySQL主要提供的整数类型有TINYINT、SMALLINT、MEDIUMINT、INT（INTEGER）、BIGINT。整数类型的属性字段可以添加AUTI——INCREMENT自增约束条件。

TINYINT

-128～127 / 0~255

SMALLINT

-32768~32767/0~65535

MEDIUMINT

-8388608~8388607/0~16777215

INT(INTEGER)

-2147483648~2147483647/0~4294967295

BIGINT

-9223372036854775808~922337203

浮点数类型和定点数类型

MySQL中使用浮点数和定点数来表示小数。浮点数类型有两种：单精度浮点数（FLOAR）和双精度浮点类型（DOUBLE）。定点数类型只有一种：DECIMAL。浮点数类型和定点数类型都可以用（M，N）来表示。其中，M称为精度，表示总共的位数；N称为标度，表示小数的位数。

FLOAT

单精度浮点数 4字节

DOUBLE

双精度浮点数 8字节

DECIMAL（M，D），DEC

压缩的“严格”定点数 M+2字节

日期/时间类型

YEAR、TIME、DATE、DATETIME、TIMESTAMP

YEAR

YYYY 1901～2155 1字节

TIME

HH:MM:SS -838:59:59～838:59:59 3字节

DATE

YYYY-MM-DD 1000-01-01～9999-12-3 3字节

DATETIME

YYYY-MM-DD HH:MM:SS 1000-01-01 00：00：00～9999-12-31 23：59：59 8字节

TIMESTAMP

YYYY-MM-DD HH:MM:SS 1970-01-01 00：00：01 UTC ～2038-01-19 03：14：07 UTC 4字节

字符串类型

CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM、SET等。字符串类型又分为文本字符串和二进制字符串

CHAR（M）

固定长度非二进制字符串

M字节，1<=M<=255

VARCHAR(M)

变长非二进制字符串

L+1字节，在此L<=M 和1<=M<=255

TINYTEXT

非常小的非二进制字符串

TEXT

小的非二进制字符串

MEDIUMTEXT

中等大小的非二进制字符串

ENUM

枚举类型，只能有一个枚举字符串值

SET

一个设置，字符串对象可以有零个或多个SET成员

文本字符串类型

字符串类型用来存储字符串数据，除了可以存储字符串数据之外，还可以存储其他数据，比如图片和声音的二进制数据。MySQL支持两类字符型数据：文本字符串和二进制字符串。文本字符串可以进行区分或者不区分大小写的串比较，还可以进行模式匹配查找。在MySQL中，文本字符串类型是指CHAR、VARCHAR、TEXT、ENUM、SET

二进制字符串类型

类型

BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB

BIT类型

位字段类型。M表示每个值的位数，范围1～64.如果M被省略，默认位1.如果位BIT（M）列分配的值的长度小于M位，就在值的左边用0填充。

BINARY和VARBINARY类型

类似与CHAR和VARCHAR，不同的是它们包含二进制字节字符串

用法：列名称 BINARY(M)或者VARCHAR(M)

BLOB类型

BLOB是一个二进制大对象，用来存储可变数量的数据。
BLOB类型分为4钟：
TINYBLOB:最大长度255（2^8-1）B;
BLOB:最大长度65535（2^16-1）B;
MEDIUMBLOB：最大长度16777215（2^24-1）B
LONGBLOB：最大长度4294967295（2^32-1）B或4GB

BLOB列存储的是二进制字符串（字节字符串），TEXT列存储的事非二进制字符串（字符字符串）。BLOB列没有字符集，并且排序和比较基于列值字节的数值；TEXT列又一个字符集，并且根据字符集对值进行排序和比较。

运算符

算数运算符

加减乘除求余

比较运算符

大于小于等于大于等于小于等于不等于 in between and IS null greatest least like regexp

逻辑运算符

True 1，False 0；运算符：非（not或！）、逻辑与（and或&&）、逻辑或（or 或||）、逻辑异或（xor）

位运算符

位与（&）、位或（|）、位非（～）、位异或（^）、左移(<<)、右移（>>）

MySQL函数

函数表示对输入参数值返回一个具有特定关系的值，MySQL提供了大量丰富的函数，在进行数据库管理以及数据的查询和操作时讲会经常用到各种函数。通过对数据的处理，数据库功能可以变得更加强大，可以更加灵活地满足不同用户的需求。各类函数从功能方面主要分为数学函数、字符串函数、日期和时间函数、条件判断函数、系统信息函数和加密函数等其他函数

绝对值函数ABS(x)和返回圆周率的函数PI（）

平凡根函数SQRT（x）和求余函数MOD（X,Y）

获取整数的函数CEIL(x)、ceiling（x）和FLOOR（X）

获取随机数的函数RAND（）和RAND(X),范围在0到1之间，若已指定一个整数参数，则它被用作种子值，用来产生重复序列。

round(x)返回最接近参数的整数，对x值进行四舍五入
round(x,y)返回最接近参数x的数，其值保留到小数点后面y位，若y为负值，则将保留x值到小数点左边y位
truncate(x,y)返回被舍去至小数点后y位的数字x。若y的值为0，则结果不带有小数点或不带有小数部分。若y设为负数，则截去（归零）x小数点左起第y位开始后面所有低位的值。

符号函数SIGN（X），返回参数的符号，x的值位负、零或正十返回结果一次为-1、0或1

幂运算函数：
POW（X，Y）：返回x的y次乘方的结果值
POWER（X、Y）：返回x的y次乘方的结果值
EXP（X）：返回e的x乘方后的值

对数运算函数
LOG（X）返回x的自然对数，x相对于基数e的对数
LOG10（X）返回x的基数为10的对数

角度与弧度相互转换的函数

RANDIANS（X）

将参数x由角度转换为弧度

DEGREES（X）

将参数由弧度转化为角度

SIN（X）

返回x正弦

ASIN（X）

返回x反正弦