MySQL
-
数据库(DataBase)
- 概念:DataBase,简称DB。按照一定格式存储数据的一些文件的组合。顾名思义:存储数据的仓库,实际上就是一堆文件,这些文件中存储了具有特定格式的数据
-
数据库管理系统(DataBaseManagementSystem)
- 概念:DataBaseManagementSystem,简称DBMS。数据库管理系统是专门用来管理数据库中的数据的,数据库管理系统可以对数据库当中的数据进行增删改查。
- 常见的数据库管理系统:MySQL , Oracle, MS, SqlServer, DB2, sybase等
-
SQL(Structured Query Language)
- 概念:结构化查询语言
- 程序员需要学习SQL语句,程序员通过编写SQL语句,然后DBMS负责执行SQL语句,最终来完成数据库中数据的增删改查操作
- 注意:SQL是一套标准,SQL除了在mysql中可以使用,在其他数据库中也可以使用
-
数据库、数据库管理系统和SQL之间的关系
- DBMS ---------> SQL------------->DB
-
在Windows操作系统中,如何使用命令来启动和关闭mysql服务?
- net stop 服务名称
- net start 服务名称
-
表:
- 数据库当中最基本的单元是表
- 任何一张表都有行和列
- 行(row):称为数据/记录
- 列(column):称为字段(字段包括字段名、数据类型、约束等)
-
SQL语句的分类:
- DQL(Data Query Language)数据查询语言:
- 特征:带有select关键字
- DML(Data Manipulation Language)数据操纵语言:
- 特征:对表中的数据进行增删改
- insert delete update
- DDL(Data Definition Language)数据定义语言:
- 特征:DDL主要操作的是表的结构,不是表的数据
- create drop alter truncate
- TCL(Transaction Control Language)事务控制语言:
- 事务提交:commit
- 事务回滚:rollback
- DCL(Data Control Language)数据控制语言:
- 授权:grant
- 撤销权限:revoke
- DQL(Data Query Language)数据查询语言:
-
一些常用的命令:
-
查看表的结构:
desc users;
-
查看MySQL的版本:
select version();
-
查看当前使用的数据库:
select database();
-
在数据库中不能使用=来判断null(is null 或者 is not null)
-
产看建表/建数据库语句
show create table user; show create database it_cast;
-
模糊查询(like)
- %:任意多个字符
- _:任意一个字符
- 注意:如果查询内容含有%或者_,需要用\进行转义
-
单行处理函数/数据处理函数
- 特点:一个输入对应一个输出 (多行:多个输入对应一个输出)
-
常见的单行处理函数:
- lower
- upper
- trim
- length
- substr(被截取的字符串,起始下标(从1开始),截取的长度)
- concat :字符串拼接
- str_to_date
- date_format
- format
- round(数字,保留的位数)
- rand
- ifnull
- 在所有数据库中,只要有null参与的数学运算,最终结果就是null
- ifnull(数据,被当作哪个值)
- case … when… then… when… then… else… end
-
常见的分组函数/多行处理函数
- count
- sum
- avg
- max
- min
- 注意:
- 分组函数在使用的时候必须先分组,然后才能使用,如果没有对数据进行分组,整张表默认为一组
- 多个输入对用一个输出
- 分组函数自动忽略null值,你不用提前对null进行处理
- 分组函数不能直接使用在where子句中
-
-
分组查询
-
概念:在实际应用中,肯有这样的需求,需要先进行分组,然后对每一组的数据进行操作,这个时候需要使用分组查询
-
select ... from ... group by ...
-
在一条select语句中,如果有group by 语句的话,select后面只能根:参加分组的字段以及分组函数
-
使用having可以对完组之后的数据进一步过滤,having不能单独使用,having不能代替where,having必须和group by联合使用(having的效率较低)
-
-
之前所学的关键字组合:
-
select ... from ... where ... group by ... having ... order by ... limit ...
-
执行顺序:from -> where -> group by -> having -> select -> order by->limit
-
-
为什么分组函数不能直接使用在where后面?
因为分组函数在使用的时候,必须先分组,然后才能使用
-
查询结果去除重复记录:
- distinct + 【字段名】
- distinct只能出现在所有字段的最前方,表示联合后面多个字段,去除重复记录
-
连接查询
-
概念:从一张表中单独查询,称为单表查询。emp表和dept表联合起来查询数据,从emp表中取员工名字,从dept表中取部门名字,这种跨表查询,多张表联合起来查询数据,被称为连接查询
-
根据表连接的方式划分:
- 内连接:
- 等值连接
- 非等值连接
- 自连接
- 外连接
- 左外连接(左连接)
- 右外连接(右链接)
- 全连接(使用较少)
- 内连接:
-
笛卡尔积:当两张表进行连接查询,没有任何条件限制的时候,最终查询结果条数,是两张表数据条数的乘机,这种现象称为笛卡尔积现象
-
内连接与外连接的区别?
内连接没有主次关系,两张或多张表是平等的,只查询出满足满足条件的数据; 外连接有主次关系,left 左边为主表,right 右边为主表,主表的数据会全部查询出来,捎带着符合条件的数据
-
-
子查询
-
概念:select语句中嵌套select语句,被嵌套的select语句称为子查询
-
子查询可以出现的位置:
select ... from ... where ...
-
-
union 连接查询结果
- 注意事项:
- 要求连个结果集的列数相同
- 要求结果集合并时列和列的数据类型也要相同
- 相对于表连接的优点:在减少匹配次数的情况下,完成结果集的拼接
- 注意事项:
-
limit
- 作用:将查询的结果集的一部分取出来,通常用在分页查询中。提高用户体验
- 用法:limit startIndex, length startIndex从0开始,length是长度
- 缺省用法:limit n; 显示前n条数据
- 注意:limit在order by后执行
-
DDL
-
表的创建:
create table 【if not exists】 表名 ( 字段名 字段类型 【约束】【注释】, 字段名 字段类型 【约束】【注释】, ... 字段名 字段类型 【约束】【注释】 ) ;
-
表的修改:
1、添加列 alter table 表名 add column 列名 类型 【first|after 字段名】; 2、修改列的类型或约束 alter table 表名 modify column 列名 新类型 【新约束】; 3、修改列名 alter table 表名 change column 旧列名 新列名 类型; 4、删除列 alter table 表名 drop column 列名; 5、修改表名 alter table 表名 rename 【TO】 新表名;
-
表的删除:
方式一:drop table 【if exists】 表名; 方式二:truncate table 【if exists】 表名;
-
表的复制:
1、复制表的结构 create table 表名 link 旧表; 2、复制表的某些字段 create table 表名 select 字段1,字段2,... from 旧表 where 0; 3、复制表的结构+数据 create table 表名 select 查询列表 from 旧表 【where 筛选条件】; 4、复制表的某些字段+数据 create table 表名 select 字段1,字段2,... from 旧表【where 筛选条件】;
-
-
mysql5.7表中无法插入中文数据?
mysql5.7默认的表和数据库编码是latin的字符编码,需要在my.ini中修改字符编码为utf-8 mysql8.0中不用修改字符集,默认是utf-8mp4
-
常用的集成工具:SQLyog、Navicat、dbeaver
-
DUAL是一张虚拟表
-
使用集成开发工具登陆mysql8.0失败解决方式:
- 升级开发工具的版本
- 修改本地密码加密方式
-
SQL大小写规范(建议遵守)
-
mysql在Windows环境下是大小写不敏感的(Windows文件名不区分大小写)
-
mysql在Linux环境下是大小写敏感的
- 数据库名、表名、表的别名、变量名是严格区分大小写的
- 关键字、函数名、列名(或字段名)、列的别名是忽略大小写的
-
推荐采用统一的书写规范:
- 数据库名、表名、表的别名、字段名、字段别名等都小写
- SQL关键字、函数名、绑定变量等都大写
-
列的别名:
- as (alias)别名
- 列的别名可以用“”双引号括起来
-
显示表约束:
describe 或者 desc user;
-
只要有NULL参与比较运算,结果一定为NULL,而不是1或者0
- <=>为NULL而生,用于判断NULL值参与的比较
- 建议使用IS NULL和IS NOT NULL和ISNULl来进行NULL值的判断
-
正则表达式:REGEXP或者RLIKE
-
逻辑运算:XOR
-
如果没有使用排序,默认是按照数据添加顺序显示
-
多表查询:
- sql优化的角度:建议多表查询时,每个字段前都指明其所在的表
- 如果给表起了别名,一旦在select或者where使用表名的话,则必须使用表的别名,而不能再使用表的原名
-
表的连接:
-
内连接:合并具有同一列的两个以上的表的行,结果集中不包含一个表与另一个表不匹配的行
-
外连接:合并具有同一列的两个以上的表的行,结果集中除了包含一个表与另一个表匹配的行之外,还查询到了左表或者右表中不匹配的行
-
MySQL不支持慢外连接
-
-
UNION 和UNION ALL
- union:返回两个查询结果集的并集,去除重复记录
- union all:返回两个查询结果集的并集,对于两个结果集的重复部分,不去重
- 注意:执行union all语句时,所需要的资源比union语句少。如果明确知道合并数据后的结果不存在重复数据,或者不需要去除重复的数据,则尽量使用union all语句,以提高查询的效率
-
-
JOIN的7种实现:
-
常用的字符串函数:(字符串的索引从1开始)
- char_length:获取字符的个数
- length:获取字节的个数,在utf-8mp3中,一个中文占3个字节
- concat-ws(char, s, s…):用某个字符连接字符串
- replace(str, a, b):用b字符串来替换str中的a字符串
- upper:大写
- lower:小写
- left(str, n):从左边开始取n个字符
- right(str,n):从右边开始取n个字符
- lpad(str, len, pad):要求该字符一共占len位,如果不够,就从左边开始添加pad,直到符合要求(右对齐)
- rpad(str, len, pad):要求该字符一共占len位,如果不够,就从右边开始添加pad,直到符合要求(左对齐)
- strcmp(str1, str2):通过ASCII码值,比较两个字符串的大小
- substr(str,index,len):返回从字符串str的index位置及其后面len个字符
-
日期和时间函数:
-
获取日期、时间
函数 用法 curdate()、current_date() 返回当前日期,只包含年月日 curtime()、current_time() 返回当前时间,只包含时分秒 now() 返回当前系统日期和时间 utc_date() 返回UTC(世界标准时间)日期 utc_time() 返回UTC(世界标准时间)时间 -
时间与时间戳的转换
函数 用法 UNIX_TIMESTAMP() 以Unix时间戳的形式返回当前时间 UNIX_TIMESTAMP(date) 将时间以date以unix时间戳的形式返回 FROM_UNIXTIME(timestamp) 将unix时间戳的时间转换为普通格式的时间 -
获取月份、星期、星期数、天数等函数
函数 用法 YEAR(date)/MONTH(date)/DAY(date) 返回具体的日期值 HOUR(time)/MINUTE(time)/SECOND(time) 返回具体的时间值 MONTHNAME(date) 返回月份 DAYNAME(date) 返回星期几 WEEKDAY(date) 返回周几 QUARTER(date) 返回日期对应的季度 WEEK(date), WEEKOFYEAR(date) 返回一年中的第几周 DAYOFYEAR(date) 返回日期是一年中的第几天 DAYOFMONTH(date) 返回日期位于所在月份的第几天 DAYOFWEEK(date) 返回周几,注意:周日是1, 周一是2…周六是7 -
时间和秒钟的转化函数
函数 用法 TIME_TO_SEC 将time转化为秒并返回结果值 SEC_TO_TIME 将seconds描述转化为包含时分秒的时间
。。。。。。。。好多函数,不想写了
-
-
流程控制函数
- if(value, value1, value2):如果value的值为TRUE,返回value1, 否则返回value2
- ifnull(value1, value2):如果value1不为NULL,返回value1, 否则返回value2
- case when 条件1 then 结果1 when 条件2 then 结果2 else 默认的结果 end
-
加密函数
- 概述:加密与解密函数主要用于对数据库中的数据进行加密和解密处理,以防止数据被他人窃取,这些函数在保证数据库安全是非常有用
- MD5(str):返回字符串str的md5加密后的值,是一种加密方式。若参数为NULL,则会返回NULL值
- SHA(str):从原明文密码str计算并返回加密后的密码字符串,当参数为NULL时,返回NULL。(SHA加密算法比MD5更加安全)
-
MySQL信息相关的函数
函数 用法 version() 返回当前MySQL的版本号 connection_id() 返回当前MySQL服务器的连接数 database(),schema() 返回MySQL命令行当前所在的数据库 user(),current_user(), system_user(), session_user 返回当前连接MySQL的用户名,返回格式为“主机名@用户名” charset(value) 返回字符串value自变量的字符集 collation(value) 返回字符串value的比较规则 -
如果需要统计表中的记录数,使用count(*)、count(1)、count(具体字段)哪个效率更高?
- 如果使用的是MyISAM存储引擎,则三者效率相同,都是O(1)
- 如果使用的是InnoDB存储引擎,则三者效率:count(*) > count(1) > count(具体字段)
-
GROUP BY
-
group by后面的字段顺序对结果没有影响
-
where和having的区别:
- 从适用范围上来讲,having的适用范围更广
- 如果过滤条件中没有聚合函数:这种情况下,where的执行效率要高于having
-
优点 缺点 where 先筛选数据再关联 不能使用分组函数进行筛选 having 可以使用分组函数 在最后的结果集中进行筛选,执行效率低
-
-
SQL语句的执行流程:
FROM -> ON -> (LEFT/RIGHT JOIN) -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT ->ORDER BY -> LIMIT
-
-
创建与管理数据库
-
判断数据库是否已经存在,不存在则创建数据库并指明字符集
create database if not exists it_cast character set 'utf8mb4'; #如果要创建的数据库已经存在,则创建不成功,但不会报错
-
修改数据库的字符集
alter database it_cast character set 'gbk';
-
删除数据库
drop database if exists it_cast;
-
数据库的名字不可修改!
-
-
创建与管理表
-
基于现有的表创建新的表
create table mytable as (select username, password from user);
-
修改表:
-
添加一个字段:
alter table user add salary double(10, 2); #可以通过first和after指定字段的位置
-
修改一个字段的长度和默认值:
alter table user modify username varchar(255) default 'zhangsan';
-
修改字段名同时修改字段(长度,类型)
alter table user change email my_email varchar(50);
-
删除一个字段
alter table user drop column email;
-
重命名表:
rename table user to myuser;
-
删除表:
drop table if exists user;
-
清空表中的数据,保留表结构:
truncate table user;
-
-
-
DCL中commit和rollback
- commit:提交数据,一旦执行commit,则数据就被永久的保存在了数据库中,意味着数据不可以回滚
- rollback:回滚数据,一旦执行rollback,则可以实现数据的回滚,回滚到最近的的一次commit之后
-
对比truncate table和delete from
- 相同点:都可以实现对表中的数据的删除,同时保留表结构
- 不同点:
- truncate table:一旦执行此操作,表中的数据会全部删除,同时数据是不可以回滚的
- delete from:一旦执行此操作,表数据可以全部清除(不带where)。同时表中的数据是可以实现回滚的
-
DDL和DML的一些说明:
-
DDL的操作一旦执行,就不可以回滚,指令set autocommit = FALSE对DDL操作失效。(因为在执行完DDL操作之后,一定会执行一次commit,而此commit操作是不受上述指令的影响的)
-
DML的操作情况:一旦执行,也是不可以回滚的,但是如果在执行DML之前,执行了set autocommit = false,则执行完的DML操作就可以实现回滚。
-
阿里开发规范:
【参考】truncate比delete的速度更快,且使用的系统和事务日志资源少,但truncate无事务且不触发trigger,有可能造成事故,故不建议在开发代码中使用此语句。 说明:truncate table在功能上与不带where字句的delete语句相同
-
-
阿里巴巴《Java开发手册》之MySQL字段命名
- 【强制】表名、字段名必须使用小写字母或数字,禁止出现数字开头,禁止两个下划线中间只出现数字。数据库字段名的修改代价很大,因为无法进行预发布,所以字段名称需要慎重考虑。
- 正例:alyun_admin, rdc_config, level3_name
- 反例:AliyunAdmin, rdcConfig, lever_3_name
- 【强制】禁用保留字,如desc、range、match、delayed等,请参考MySQL官方保留字
- 【强制】表必备三字段:id, gmt_create, gmt_modified
- 说明:其中id必为主键,类型为BIGINT UNSIGNED、单表时自增、步长为1。gmt_create, gmt_modified的类型均为DATETIME类型,前者现在时表示主动式创建,后者过去分词表示被动式更新
- 【推荐】表名最好是遵循“业务名称_表的作用”
- 正例:alipay_task, force_project, trade_config
- 【推荐】库名应和应用名称尽量一致
- 【强制】表名、字段名必须使用小写字母或数字,禁止出现数字开头,禁止两个下划线中间只出现数字。数据库字段名的修改代价很大,因为无法进行预发布,所以字段名称需要慎重考虑。
-
数据处理之增删改:
-
插入数据
-
将查询结果插入表中:
insert into emp1(id, name, salary, hire_date) select employee_id, last_name, salary, hire_date from employees where department_id in (8, 9); #注意: #1. 查询的字段一定要与添加到表中的字段一一对应 #2.emp1表中要添加数据的字段的长度不能低于employees表中的查询的字段的长度,否则就会有添加失败的风险
-
-
更新数据
- 格式:update set 字段名1=值1, 字段名2=值2 where …(可以通过where可以实现批量操作)
-
删除数据
- 格式:delete from 表名 where …(不加where删除所以数据)
-
MySQL8.0新特性:计算列
-
创建表的时候使用:
create table user ( a int, b int, c int generated always as (a + b) virtual );
-
-
-
数据类型:
-
整数类型:
整数类型 字节 有符号取值范围 TINYINT 1 -2^7 - 2^7 - 1 SMALLINT 2 -2^15 - 2^15 - 1 MEDIUMINT 3 -2^23 - 2^23 - 1 INT, INTEGER 4 -2^31 - 2^31 - 1 BIGINT 8 -2^63 - 2^63 - 1 -
可选项:
-
UNSIGNED
create table user ( a int unsigned );
-
-
浮点类型:
-
数据精度说明:
对于浮点类型,在MySQL中单精度使用4个字节,双精度使用8个字节。 【1】MySQL允许使用非标准语法(其他数据库未必支持,因此如果涉及到数据迁移,则最好不要这么做):float(M,D)或者double(M,D),这里M称为精度,D称为标度。(M,D)中M=整数位+小数位,D=小数位,D<=M<=255, 0<=D<=30 【2】float和double类型在不指定精度和标度时,默认会按照实际的精度(由实际的硬件和操作系统决定)来显示。 【3】在MySQL8.0中float和double已经不建议使用了
-
浮点类型最大的缺点:存在误差
-
-
定点数类型:
-
MySQL中定点数类型只要decimal一种类型
数据类型 字节数 含义 DECIMAL(M, D), DEC, NUMERIC M + 2字节 有效范围由M和D决定 -
定点数在MySQL内部使用字符串的形式进行存储的,这就决定了它一定是精准的。
-
当decimal类型不指定精度和标度时,其默认为decimal(10 ,0)。当数据的精度超出了定点数类型的精度范围时,则MySQL同样会进行四舍五入处理
-
-
开发经验:
"由于decimal数据类型的精准性,在我们的项目中,除了极少数(比如商品编号)用到整数类型外,其他的数值都用到是decimal,原因就是这个项目所处的零售行业,要求精准,一分钱也不能差。"
-
位类型:
-
BIT类型中存储的时二进制值
二进制字符串类型 长度 长度范围 占用空间 BIT(M) M 1<=M<=64 约为(M+7)/8个字节
-
-
BLOB类型
-
BLOB是一个二进制大对象,可以容纳可变数量的数据
-
MySQL中的BLOB类型包括tinyblob、blob、mediumblob和longblob4种类型,它们可以容纳的最大长度不同,可以存储一个二进制的大对象,比如图片音频和视频等
-
需要注意的是,在实际工作中往往不会在MySQL数据库中使用blob类型储存大对象数据,通常会将图片、音频、视频文件存储到服务器的磁盘上,并将图片、音频和视频的访问路径存储到MySQL中
BLOB类型 长度大小 tinyblob blob 64kb mediumblob 16mb longblob 4gb
-
-
日期时间类型数据:
-
timestamp和datatime的区别:
- timestamp存储空间比较小,表示的日期时间范围也比较小
- 底层存储方式不同,timestamp底层存储的是毫秒值,距离1970-1-1 0:0:0 0毫秒的毫秒值
- 两个日期比较大小或日期计算时,timestamp更方便、更快
- timestamp和时区有关,timestamp会根据用户的时区不同,显示不同点结果,而datatime则只能反映出插入时当地的时区,其他时区的人查看数据必然会有误差
-
开发经验:
用的最多的日期时间类型,就是datetime,虽然MySQL也支持year,time,date以及timestamp类型。但是在实际项目中,尽量使用datetime类型。因为这个数据类型包括了完整的日期和时间信息,取值范围也最大,使用起来比较方便。毕竟,如果日期信息分散在好几个字段,很不容易记忆,而且查询的时候,SQL语句也会变复杂。 此外,一般存注册时间、商品发布时间等,不建议使用datetime存储,而是使用时间戳,因为datetime虽然直观,但不便于计算。
-
-
文本字符串类型:
-
在实际项目中,我们还经常遇到一种数据,就是字符串数据。
文本字符串类型 值得长度 长度范围 占用的存储空间 CHAR(M) M [0, 255] M字节 VARCHAR(M) M [0, 65535] M+1字节 TINYTEXT L [0, 255] L+2字节 TEXT L [0, 65535] L+2字节 MEDIUMTEXT L [0, 16777215] L+3字节 LONGTEXT L [0, 4294967295] L+4字节 ENUM L [1, 65535] 1或者2个字节 SET L [0, 64] 1, 2, 3, 4,或8个字节 -
开发经验:
text文本类型,可以存比较大的文本段,搜索速度稍慢,因此如果不是特别大的内容,建议使用char和varchar来替代。还用text类型不用加默认值,加了也没用。而且text和blob类型的数据删除后容易导致“空洞”,使得文件碎片比较多,所以频繁使用的表不建议包含text类型字段,建议单独分出去,单独使用一个表
-
枚举类型:
create table test_enum( season ENUM('spring', 'summer', 'autumn', 'winter', 'unknow') ); insert into test_enum values ('spring'), ('winter');
-
SET类型:
create table test_set( s set('a', 'b', 'c') ); #插入重复的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');
-
-
JSON类型:(详细可以百度)
- JSON(JavaScript Object Notation)是一种轻量级的数据交换格式。简洁和清晰的层次结构使得JSON成为理想的数据交换语言。它易于人的阅读和编写,同时也易于机器解析和生成,并有效的提升网络传输效率。JSON可以将JavaScript对象中表示的一组数据转化为字符串,然后就可以在网络或者程序之间传递这个字符串,并在需要的时候将它还原为各编程语言所支持的数据格式。
-
小结以及选择建议:
-
在定义数据类型时,如果确定时整数,就用int;如果是小数,一定用定点数类型decimal(M,D);如果是日期与时间,就用datetime
-
这样做的好处是,首先确保你的系统不会因为数据类型定义出错。不过凡是都有两面性,可靠性好,并不意味着高效。比如text虽然使用方便,但是效率不如char(M)和varchar(M)
-
阿里巴巴《Java开发手册》之MySQL数据库:
1. 任何字段如果为非负数,必须是unsigned 2. 【强制】小数类型时decimal,禁止使用float和double 说明:在存储的时候,float和double都存在精度损失的问题,很可能在比较值的时候,得到不正确 的结果。如果存储的数据超过decimal的范围,建议将数据拆成整数和小数并分开存储。 3. 【强制】如果存储的字符串长度相等,使用char定长字符串类型 4. 【强制】varchar是可变长字符串,不预先分配空间,长度不要超过5000,如果存储长度大于此值,定义字段为text,独立出来一张表,用主键对于,避免影响其他字段索引效率
-
-
-
约束
-
概念:约束是表级的强制规定
-
约束的分类:
-
角度1:约束的字段个数
- 单列约束
- 多列约束
-
角度2:约束的作用范围
- 列级约束:将此约束声明在对应字段的后面
- 表级约束:在表中所有字段都声明完,在所有字段的后面声明约束
-
角度3:约束的作用
-
非空约束:not null
特点: 1.只能用在列级约束
-
唯一性约束:unique
特点: 1. 同一个表可以有多个唯一性约束 2. 唯一性约束可以使得某一列的值唯一,也可以使得多个列的组合的值唯一 3. 唯一性约束允许列值为null,并且可以有多个 4. 在创建唯一约束的时候,如果不给唯一约束命名,就和默认列名一样 5. MySQL会给唯一约束的列默认创建一个唯一索引
#复合的唯一性约束: create table user( id int, name varchar(15), #表级约束 constraint uk_user_name_pwd unique (name, password) #第二种写法 unique(name, password) )
-
主键约束:primary key
特点: 1. 主键约束相当于唯一约束 + 非空约束的组合,主键约束不允许重复,也不允许出现空值 2. 一个表中最多只能有一个主键约束,建立主键约束可以在列级别创建,也可以在表级别上创建 3. 主键约束对应着表中的一列或者多列(复合主键) 4. 如果是多列组合的复合主键约束,那么这些列都不允许为空值,并且组合的值不允许重复 5. MySQL主键名总是primary,就算自己命名了主键约束名也没用 6. 当创建主键约束时,系统默认会在所有的列或者组合上建立对应的主键索引。如果删除主键约束了,主键约束对应的索引就自动删除了 7. 需要注意的一点是,不要修改主键字段的值,因为主键是数据记录的唯一标识,如果修改了主键的值,就有可能会破坏数据的完整性
- 自增长(auto_increment)
- 作用:某个字段的值自增
- 特点和要求:
- 一张表最多只能有一个自增长列
- 当需要产生唯一标识符或顺序值时,可设置自增长
- 自增长列约束必须是键列(主键列,唯一键列)
- 自增约束的列的数据必须是整数类型
- 如果自增列指定了0和null,会在当前最大值的基础上自增;如果自增列手动指定了具体的值,直接赋值为具体值
- 注意:开发中,一旦主键作用的字段上声明有auto_increment,则我们在添加数据时,就不要给主键对应的字段去赋值了
- MySQL8.0新特性:自增变量的持久化
- 自增长(auto_increment)
-
外键约束:foreign key
-
作用:限定某个表的某个字段的引用完整性
- 例:员工表的员工所在部门的选择,必须在部门表中能找到对应的部分
-
主表和从表/父表和子表
- 主表(父表):被引用的表,被参考的表
- 从表(子表):引用别人的表,参考别人的表
- 例如:员工表的员工所在的这个字段的值要参考部门表:部门表是主表,员工表是从表
-
特点:
-
从表的外键列,必须引用/参考主表的键主键或者唯一约束的列
原因:因为被依赖/被参考的值必须是唯一的
-
在创建外键约束的时候,如果不给外键约束命名,默认不是列名,而是自动生成了一个外键约束名
-
创建表时就指定外键的话,先创建主表,再创建从表
-
删表时,先删除从表(或者先删除外键约束),再删除主表
-
当主表的记录被从表参照时,主表的记录讲不允许删除,如果要删除数据的话,需要删除从表中依赖该记录的数据,然后才可以删除主表的数据
-
在“从表”中指定外键约束,并且一个表可以建立多个外键约束
-
从表的外键列与主表被参照的列名字可以不相同,但是数据类型必须一样,逻辑意义一致,如果类型不一样,创建子表时,就会出现错误
-
当创建外键约束时,系统默认会在所在的列上建立对应的普通索引。但是索引是列名,不是外键的约束名(根据外键查询效率很高)
-
删除外键约束后,必须手动删除对应的索引
-
-
创建外键约束:
create table emp( emp_id int primary key auto_increment, emp_name varchar(15), dept_id int, constraint fk_emp_dept_id foreign key (dept_id) references dept(dept_id) );
-
约束等级:
- Cascade方式:在父表上update或者delete记录时,同步update和delete掉子表的匹配记录
- Set null方式:在父表上update或者delete记录时,将子表上匹配记录的列设为null,但是要注意子表的外键列不能为null
- No action方式:如果子表中中有匹配的记录,则不允许对父表对应候选键进行update或者delete操作
- Restrict方式:同no action,都是立即检查外键约束
- Set default方式:父表中有变更时,子表将外键列设置为一个默认的值,但innodb不能识别
- 如果没有指定等级,就相当于Restrict方式,对于外键约束,最好采用on update cascade on delete Restrict的方式
-
小结:
在MySQL里,外键约束是有成本的额,需要消耗系统资源。对于大并发的SQL操作,有可能会不适合,比如大型网站的中央数据库,可能会因为外键约束的系统开销而变得非常慢。所以,MySQL允许你不使用系统自带的外键约束,在应用层面完成检查数据一致性的逻辑,即使你不使用外键约束,也要想办法应用层面的附加逻辑,来实现外键约束的功能,确保数据的一致性。 阿里开发规范:【强制】不得使用外键与级联,一切外键概念必须在应用层解决
-
-
检查约束:check
-
作用:检查某个字段的值是否符合某某要求,一般指的是值的范围
-
MySQL5.7不支持,MySQL8.0支持,Oracle一直都支持
-
代码示例:
create table test( id int, name varchar(15), salary decimal(10, 2) check(salary > 2000) );
-
-
默认值约束:default
-
作用:给某个字段或者列指定默认值,一旦设置默认值,在插入数据时,如果此字段没有显示赋值,则赋值为默认值
-
代码演示:
create table user( id int, user_name varchar(15) default 'zhangsan' );
-
面试:
-
为什么建表时,加not null default ‘’ 或者 default 0
不想让表中出现null值
-
为什么不想要null的值
1)不好比较,null是一种特殊值,比较时只能用专门的is null和is not null来比较,碰到预算符,通常返回null 2)效率不高。影响提高索引的效果。
-
带auto_increment约束的字段值是从1开始的吗?
在MySQL中,默认的auto_increment的初始值是1,每新增一条记录,字段值自动加一。设置自增属性的时候,还可以指定第一条插入记录的自增字段的值,这样新插入的记录的自增字段从初始值开始递增,如在表中插入第一条记录,同时指定id值为5,则以后插入的记录的id值就会从6开始往上增加,添加主键约束时,往往都需要设置字段自动增加属性。
-
并不是每个表都可以任意选择存储引擎?
外键约束不能跨引擎使用。 MySQL支持多种存储引擎,每一个表都可以指定一个不同的存储引擎,需要注意的是:外键约束是用来保证数据的参照完整性的,如果表之间需要关联外键,却指定了不同的存储引擎,那么这些表之间是不能创建外键约束的。所以说,存储引擎的选择也不完全是随意的。
-
-
-
-
-
-
视图:
-
常见的数据库对象:
对象 描述 表(table) 表是存储数据的逻辑单元,以行和列的形式存在,列就是字段,行就是记录 数据字典 就是系统表,存放数据库相关信息的表。系统表的数据通常由数据库系统维护,程序员通常不应改修改,只可查看 约束(constraint) 执行数据校验的规则,用于保证数据完整性的规则 视图(view) 一个或者多个数据表里的数据的逻辑显示,视图并不存储数据 索引(index) 用于提高查询性能,相当于书的目录 存储过程(procedure) 用于完成一次完整的业务处理,没有返回值,但可以通过传出参数将多个值传给调用环境 存储函数(function) 用于完成一次特定的计算,具有一个返回值 触发器(trigger) 相当于一个事件监听器,当数据库发生特定事件后,触发器被触发,完成相应的处理 -
视图概述:
-
视图是一种虚拟的表,本身是不具有数据的,占用很少的内存空间,它是SQL中的一个重要概念
-
视图建立在已有表的基础上,视图依赖以建立的表称为基表
-
视图的建立和删除只影响视图本身,不影响对应的基表,但是当对视图中的数据进行增减、删除和修改操作时,数据表中的数据会相应的发生变化,反之亦然
-
向视图提供数据内容的语句为select语句,可以将视图理解为存储起来的select语句
- 在数据库中,视图不会保存数据,数据真正保存在数据表中,当对视图中的数据进行增减、删除和修改操作时,数据表中的数据会相应的发生变化,反之亦然
-
视图,是向用户提供基表数据的一种形式。通常情况下,小型项目的数据库可以不使用视图,但是在大型项目中,以及数据表比较复杂的情况下,视图的价值就凸显出来了,他可以帮助我们把经常查询的结果集放到虚拟表中,提升使用效率。理解和使用起来都非常方便。
-
-
视图的创建与查询
-
视图的创建:
create [or replace] [algorithm = {undefined | merge | temptable}] view 视图名称 [(字段列表,用于起别名)] as 查询语句 [with [cascaded | local] check option]
-
精简版:
create view 视图名称 as 查询语句
-
-
查看视图:
-
查看数据库的表对象、视图对象
show tables;
-
查看视图的结构
desc / describe 视图名称;
-
查看视图的属性信息
show table status likt '视图名称'\G
-
查看视图的详细信息
show create view 视图名称;
-
-
不可更新的视图:概述:要使得视图可更新,视图的行和底层的基本表中的行必须存在
一对一
的关系。否则视图就不可更新,具体情况略。虽然可以更新视图数据,但总的来说,视图作为虚拟表,主要用于方便查询,不建议更新视图的数据。对视图数据的更改,都是通过对实际数据表里的数据的操作来完成的
-
修改视图:
-
方式1:通过create or replace view字句修改视图
create or replace view empvu80(id_number, name, sal, department_id) as select ..... from employees where...
-
方式二:alter view 视图名称
alter view 视图名称 as 查询语句
-
-
删除视图:
-
代码:
drop view id exists view_name1, view_name2;
-
注意:
- 删除视图只是删除视图的定义,并不会删除基表的数据
- 基于视图a、b创建了新的视图c,如果将视图a或者视图b删除,会导致视图c查询失败,这样的视图c需要手动删除或修改,否则影响使用
-
-
小结:
- 优点:
- 操作简单
- 减少数据冗余
- 数据安全
- 适应灵活多变的需求
- 能够分解复杂的查询逻辑
- 缺点:维护成本高
- 优点:
-
-
存储过程与函数
-
存储过程概述:
- 含义:存储过程的英文是stored procedure。它的思想很简单,就是一组经过预先编译的SQL语句的封装。执行过程:存储过程预先存储在MySQL服务区上,需要执行的时候,客户端只需要向服务器端发出调用存储过程的命令,服务器端就可以把预先存储好的这一系列SQL语句全部执行。
- 好处:
- 简化操作,提高了SQL语句的重用性,减少了开发程序员的压力
- 减少操作过程中的失误,提高效率
- 减少网络传输量(客户端不需要把所有的SQL语句通过网路发送给服务器)
- 减少了SQL语句暴露在网上的风险,也提高了数据查询的安全性
- 和视图、函数的对比:
- 他和视图有着同样的优点,清晰,安全,还可以减少网络传输量,不过他和视图不同,视图是虚拟表,通常不对底层数据表直接操作,而存储过程是程序化的SQL,可以直接操作底层数据表,相比于面向集合的操作方式,能够实现一些更复杂的数据处理。
- 一旦存储过程被创建出来,使用它就行使用函数一样简单,我们直接通过调用存储过程名即可。相较于函数,存储过程是没有返回值的。
-
分类:
存储过程的参数类型可以是IN、OUT和INOUT。根据这点分类如下:
-
没有参数(无参数无返回)
-
仅仅带有IN类型(有参数无返回)
-
仅仅带OUT类型(有参数有返回)
-
既带IN又带OUT(有参数有返回)
-
带INOUT(有参数有返回)
注意:IN、OUT、INOUT都可以在一个存储过程中带多个
-
-
创建存储过程
-
语法:
create procedure 存储过程(IN | OUT | INOUT 参数名 参数类型) [characteristics ...] begin 存储过程体 end
-
调用:
call xxxx();
-
具体参数说明略
-
代码示例:
-
无参无返回值
delimiter // create procedure select_all_goods() begin select * from goods; end // delimiter ;
-
带out的存储过程
#创建存储过程 delimiter @ create procedure select_cheap_goods(out ms float) begin select min(price) into ms from goods; end @ delimiter ; #调用存储过程 call select_cheap_goods(@ms); select @ms;
-
带out的存储过程
#创建存储过程 delimiter @ create procedure select_name_by_account(in ac int) begin select * from goods where account = ac; end @ delimiter ; #调用存储过程 #方式一 call select_name_by_account(9); #方式二 set @ac = 100; call select_name_by_account(@ac);
-
带INOUT的存储过程
#创建存储过程 delimiter @ create procedure get_account_by_id(inout id0 int) begin select account into id0 from goods where id = id0; end @ delimiter ; #调用存储过程 set @ac = 3; call get_account_by_id(@ac);
-
-
-
存储函数的使用
-
语法
create function 函数名(参数名 参数类型,...) returns 返回值类型 [characteristics] begin 函数体#函数体重肯定有return语句 end
说明:
-
参数列表:指定参数为in、out或者INOUT只对procedure是合法的,function中总是默认为in参数
-
returns type语句表示函数返回数据的类型
reuturns字句只能对function做指定,对函数而言这是强制的,它用来指定函数的返回值类型,而且函数体必须包含一个return value语句
-
characteristic创建函数指定的对函数的约束,取值与创建存储过程相同
-
函数体也可以用begin…end来表示SQL代码的开始和结束。如果函数体只有一条语句,也可以省略begin…end
-
-
调用存储函数
在MySQL中,存储函数的使用方法与MySQL内部函数的使用方式是一样的。换言之,用户自己定义的存储函数与MySQL内部函数是一个性质的。
select 函数名(实参列表)
-
无参数
#创建存储函数 delimiter @ create function name_by_id() returns varchar(255) deterministic contains sql reads sql data begin return (select name from goods where id = 3); end @ delimiter ; #调用方式 select name_by_id();
-
有参数
#创建存储函数 delimiter @ create function name_by_id1(id1 int) returns varchar(255) deterministic contains sql reads sql data begin return (select name from goods where id = id1); end @ delimiter ; #调用方式一 select name_by_id1(3); #调用方式二 set @id1=2; select name_by_id1(@id1);
-
-
对比存储过程和存储函数
关键字 调用语法 返回值 应用场景 存储过程 procedure call 存储过程() 理解为有0个或者多个 一般用于更新 存储函数 function select函数() 只能时一个 一般用于查询结果为一个值并返回时 此外,存储函数可以放在查询语句中使用,存储过程不行。反之,存储过程的功能更加强大,包括能够执行对表的操作和事务操作,这些操作时存储函数不具备的。
-
存储过程和存储函数的查看、修改、删除
-
查看
-
基本语法结构:
show create {procedure | function} 存储过程名或函数名
-
查看状态信息
show procedure status;
-
从information_schema.Routines表中查看存储过程和函数的信息
代码(略)
-
-
修改
修改存储过程或函数,不影响存储过程或函数功能,只能修改相关特性,使用alter语句实现(略)
-
删除
可以使用drop语句,语法结构:
drop {procedure | function} [if exists] 存储过程或函数的名
-
-
关于存储过程使用的争议
-
优点:
- 存储过程可以一次编译多次使用
- 可以减少开发工作量
- 存储过程的安全性强
- 可以减少网络的传输量
- 良好的封装性
-
缺点:
阿里开发规范: 【强制】禁止使用存储过程,存储过程难以调试和扩展,更没有移植性
- 可移植性差
- 调试困难
- 存储过程的版本控制很困难
- 他不适合高并发的场景
-
-
-
变量、流程控制与游标
-
变量
在MySQL数据库的存储过程和函数中,可以使用变量来存储查询或计算的中间结果数据,或者输出的最终的结果数据
在MySQL数据库中,变量分为系统变量和用户自定义变量
-
系统变量:
变量由系统定义,不是用户定义,属于服务层面。启动MySQL服务实例期间,MySQL将为MySQL服务器内存中的系统变量赋值,这些系统 变量定义了当前MySQL服务实例的属性、特征。这些系统变量的值要么是编译MySQL时参数的默认值,要么是配置文件中的参数值。 系统变量分为全局系统变量(需要添加global关键字)以及会话系统变量(需要添加session关键字),有时也把全局系统变量简称为全局变量,有时也把会话系统变量称为local变量。如果不写,默认会话级别。静态变量属于特殊的全局系统变量 每一个MySQL客户机成功连接MySQL服务器后,都会产生与之对应的会话。会话期间,MySQL服务实例会在MySQL服务器内存中生成与该会话对应的会话系统变量,这些会话系统变量的初始值是全局系统变量的复制
-
查看所有或者部分系统变量
#查看所有全局变量 show global variables; #查看所有会话变量 show session variables ; 或者 show variables;
#查看满足条件的部分系统变量 show global variables like '%标识符%'; show session variables like '%标识符%';
-
查看指定系统变量
作为MySQL编码规范,MySQL中的系统变量以两个“@”开头,其中@@global仅用于标记全局系统变量,“@@session"仅用于标记会话系统变量。”@@"首先会标记会话系统变量,如果会话系统变量不存在,则标记全局系统变量
#查看指定的系统变量的值 select @@global.变量名; #查看指定的会话变量的值 select @@session.变量名; #或者 select @@变量名;
-
修改系统变量
有些时候,数据库管理员需要修改系统变量的默认值,以便修改当前会话或者MySQL服务实例的属性、特征。
方式1:修改MySQL配置文件。继而修改MySQL系统变量的值(需要重启MySQL服务)
方式2:在MySQL服务运行期间,使用“set”命令重新设置系统变量的值
#为某个系统变量赋值 #方式1 set @@global.变量名 = 变量值; #方式2 set global 变量名 = 变量值; #针对于当前的MySQL数据库实例是有效的,一旦重启服务,就失效了 #会话变量将global修改为session即可 #针对于当前的会话是有效的,一旦结束会话,重新建立起新的会话就失效了
-
用户变量:
用户变量是用户自己定义的,作为MySQL编码规范,MySQL中的用户变量以一个@开头,根据作用范围不同,又分为会话用户变量和局部变量 1)会话用户变量:作用域和会话变量一样,只对当前连接会话有效 2)局部变量:只在begin和end语句块中有效。局部变量只能在存储过程和函数中使用
-
会话用户变量
-
变量的定义和赋值:
#方式1:"=" 或者 ":=" set @用户变量 = 值 set @用户变量 := 值
#方式2:":=" 或 into关键字 select @用户变量 := 表达式[from 等字句] select 表达式 into @用户变量 [from 等字句]
-
使用
select @用户变量
-
实例代码:
#方式一 set @m1 = 10; set @m2 = 20; set @sum := @m1 + @m2; select @sum;
#方式二: select @count := count(*) from goods; select count(*) from goods into @count; select @count;
-
-
局部变量
1. 必须使用declare声明 2. 声明必须使用在begin...end中(使用在存储过程和存储函数中) 3. declare方式声明的局部变量必须声明在begin的首行的位置
-
声明
declare 变量名 类型 【default 值】; #如果没有default字句,初始值为NULL
-
赋值
#方式1: set 变量名 = 值 set 变量名 := 值
#方式2: select 字段名或表达式 into 变量名 from 表;
-
使用
select 局部变量名;
-
-
对比会话用户变量和局部变量
作用域 定义位置 语法 会话用户变量 当前会话 会话的任何地方 加@符号,不用指定类型 局部变量 定义它的begin end中 begin end的第一句话 一般不用加@,需要指定类型,还可以指定默认值
-
-
定义条件与处理函数
- 类似于异常处理机制
- 具体内容(略)
-
-
流程控制
-
分支结构之IF
-
语法结构:
if 表达式1 then 操作1 elseif 表达式2 then 操作2 ... else 操作n end if;
-
注:
- 不同的表达式对应不同的操作
- 使用在begin end中
- elseif是连在一起的(Java中是分开的)
-
-
分支结构之CASE
-
case语法结构1:
case 表达式 when 值1 then 结果1或语句1 when 值2 then 结果2或语句2 ... else 结果n或语句n end[ case ]如果放在begin end中需要case,如果放在select后面不需要
-
case语法机构2:
case when 条件1 then 结果1或语句1 when 条件1 then 结果2或语句2 ... else 结果n或结果n end [case]如果放在begin end中需要加上case,如果放在select后面不需要
-
-
循环结构之loop
-
语法结构:
[loop_label:]LOOP 执行循环的语句 END LOOP [loop_label];
-
实例代码(在存储结构中)
delimiter // create procedure loop_procedure() begin declare num int default 0; #开始循环 loop_label:loop set num = num + 1; select * from goods where id = num; #结束循环判断语句 if num >= 2 then leave loop_label; end if; #结束循环 end loop loop_label; end // delimiter ;
-
-
循环结构之while
-
语法结构:
[while_label:]while 循环条件 DO 循环体 END WHILE [while_label];
-
实例代码
drop procedure if exists test_while; delimiter // create procedure test_while() begin declare num int default 1; #开始循环 while_label:while num < 10 do select num from dual; set num = num + 1; #结束循环 end while while_label; end // delimiter ; call test_while();
-
-
循环结构之repeat
-
语法结构:
[repeat_label:] repeat 循环体的语句 until 结束循环体的条件表达式 (注意这里没有分号) end repeat [repeat_label];
-
示例代码:
drop procedure if exists test_repeat; delimiter // create procedure test_repeat() begin declare i int default 1; repeat select i from dual; set i = i + 1; until i > 10 end repeat; end // delimiter ;
-
-
对比三种循环结构:
- 这三种循环结构都可以省略名称,但如果循环中加入了循环控制语句(leave或iterate)则必须添加名称
- loop:一般用于实现简单的死循环
- while:先判断再执行
- repeat:先执行后判断,无条件至少执行一次
-
跳转语句
- leave 相当于break
- 语法:leave 标记名;
- iterate 相当于continue
- 语法:iterate 标记名;
- leave 相当于break
-
-
游标
-
什么是游标(光标)?
虽然我们可以通过筛选条件where和having,或者是限定返回记录的关键字limit返回一条记录,但是,却无法在结果集中像指针一样,向前定位一条记录或者向后定位一条记录,或者是随意定义到某一条记录,并对记录的数据进行处理。
这个时候就可以用到游标。游标,提供了一种灵活的操作方式,让我们能够对结果集中的每一条记录进行定位,并对指向的记录中的数据进行操作的数据结构。游标让SQL这种面向集合的语言有了面向过程开发的能力。
在SQL中,游标是一种临时的数据库对象,可以指向存储在数据库表中的数据进行指针。这里游标充当了指针的作用,我们可以通过操作游标来对数据进行操作。
MySQL中游标可以在存储过程和函数中使用。
-
代码示例:
drop procedure if exists test_cursor; delimiter // create procedure test_cursor() begin declare i int default 0; declare temp int; #声明游标 declare cursor1 cursor for select id from goods; #开启游标 open cursor1; loop_label : loop #使用游标 fetch cursor1 into temp; select temp from dual; set i = i + temp; if i >= 6 then leave loop_label; end if; end loop loop_label; #关闭游标 close cursor1; end // delimiter ;
-
小结:
- 游标是MySQL的一个重要的功能,为逐条读取结果集中的数据,提供了完美的解决方案,跟在应用层面实现相同的功能相比,游标可以在存储过程中使用,效率高,程序也更加简洁
- 但同时也会带来性能问题,比如在使用游标的过程中,会对数据进行假锁,这样在业务并发量大的时候,不仅会影响业务之间的效率,还会消耗系统资源,造成内存不足,这是因为游标是在内存中进行处理的结果。
- 建议:养成用完之后就关闭的习惯,这样才能提高系统的整体效率。
-
-
触发器
-
触发器概述:
MySQL从5.0.2版本开始支持触发器,MySQL的触发器和存储过程一样,都是嵌入到MySQL服务器的一段程序。
触发器是由事件来触发某个操作,这些事件包括insert、update、delete事件,所谓事件就是指用户的动作或者触发某项行为,如果定义了触发程序,当数据库执行这些语句时候,就相当于事件发生了,就会自动激发触发器执行相应的操作。
当对数据表中的数据进行插入、更新和删除操作时,需要自动执行一些数据库逻辑,可以使用触发器来了实现。
-
触发器的创建
delimiter // create trigger 触发器名称 {before | after} {insert | update | delete} on 表名 for each row begin 触发器执行的语句块; end // delimiter ;
-
代码示例:
delimiter // create trigger salary_check_trigger before insert on employees for each row begin declare mgr_sal double; select salary into mer_sal from employees where employee_id = NEW.manager_id; if NEW.salary > mgr_sal then signal sqlstate 'HY000' set message.text = '薪资高于领导薪资错误'; end if; end // delimiter ;
-
-
MySQL的其他特性
-
MySQL8.0的新增特性
-
更简便的NoSQL支持
NoSQL泛指非关系型数据库和数据存储,随着互联网平台的规模飞速发展,传统的关系型数据库已经越来越来不能满足需求。从5.6版本开始,MySQL就开始支持简单的NoSQL存储功能。MySQL8对这一功能做了优化,以更灵活的方式实现NoSQL功能,不再依赖模式(schema)。
-
更好的索引
在查询中,正确的使用索引可以提高查询的效率。MySQL8中新增了隐藏索引和降序索引。隐藏索引可以用来测试去掉索引对查询性能的影响。在查询中混合存在多级索引时,使用降序索引可以提高查询的性能。
-
更完善的JSON支持
MySQL从5.7开始支持原生的JSON数据的存储,MySQL8对这一功能做了优化,增加了聚合函数JSON_ARRAYAGG()和JSON_OBJECTAGG(),将参数聚合为JSON数组或对象,新增了行内操作符->>,是列路径运算符->的增强,对JSON排序做了提升,并优化了JSON的更新操作。
-
安全和账户管理
MySQL8中新增了cashing_sha2_password授权插件、角色、密码历史记录和FIPS模式的支持,这些特性也提高了数据库的安全性和性能,是数据库管理员能够灵活地进行账户管理工作。
-
InnoDB的变化
InnoDB是MySQL的默认存储引擎,是事务型数据库的首选引擎,支持事务安全表(ACID),支持行级锁和外。在MySQL8版本中,InnoDB在自增、索引、加密、死锁等方面做了大量的改进和优化,并且支持原子数据定义语言(DDL),提高了数据安全性,对事务提供更好的支持。
-
数据字典
-
原子数据定义语句
-
资源管理
-
字符集支持
MySQL8中默认支持的字符集从latin1更改为utf-8mb4,并首次增加了日语多特定使用的集合。
-
优化器增强
-
公用表达式(略)
公用表达式(Common Table Expression)简称CTE,MySQL现在支持递归和非递归两种形式的CTE。CTE通过在select语句或其他特定语句前使用with语句对临时结果集进行命名。 基础语法: with cte_name (col_name, col_name2..) as (subquery) select * from cte_name; subquery代表子查询,子查询前使用with语句将结果集命名为cte_name,在后续的查询中即可使用cte_name进行查询。
-
窗口函数(略)
MySQL8开始支持窗口函数,在之前的版本中已经存在的大部分聚合函数在MySQL8.0中也可以作为窗口函数来使用。
-
正则表达式
-
内部临时表
-
日志记录
-
备份锁
-
增强的MySQL复制
-
-
MySQL8.0移除的旧特性
- 查询缓存
- 加密相关
- 空间函数相关
- \N和NULL
-