咱们在 MySQL 入门篇主要介绍了基本的 SQL 命令、数据类型和函数,在局部以上知识后,你就能够进行 MySQL 的开发工做了,可是若是要成为一个合格的开发人员,你还要具有一些更高级的技能,下面咱们就来探讨一下 MySQL 都须要哪些高级的技能mysql
数据库最核心的一点就是用来存储数据,数据存储就避免不了和磁盘打交道。那么数据以哪一种方式进行存储,如何存储是存储的关键所在。因此存储引擎就至关因而数据存储的发动机,来驱动数据在磁盘层面进行存储。算法
MySQL 的架构能够按照三层模式来理解sql
存储引擎也是 MySQL 的组建,它是一种软件,它所能作的和支持的功能主要有数据库
MySQL 默认支持多种存储引擎,来适用不一样数据库应用,用户能够根据须要选择合适的存储引擎,下面是 MySQL 支持的存储引擎缓存
默认状况下,若是建立表不指定存储引擎,会使用默认的存储引擎,若是要修改默认的存储引擎,那么就能够在参数文件中设置 default-table-type
,可以查看当前的存储引擎安全
show variables like 'table_type';
奇怪,为何没有了呢?网上求证一下,在 5.5.3 取消了这个参数服务器
能够经过下面两种方法查询当前数据库支持的存储引擎session
show engines \g
在建立新表的时候,能够经过增长 ENGINE
关键字设置新建表的存储引擎。数据结构
create table cxuan002(id int(10),name varchar(20)) engine = MyISAM;
上图咱们指定了 MyISAM
的存储引擎。架构
若是你不知道表的存储引擎怎么办?你能够经过 show create table
来查看
若是不指定存储引擎的话,从MySQL 5.1 版本以后,MySQL 的默认内置存储引擎已是 InnoDB了。建一张表看一下
如上图所示,咱们没有指定默认的存储引擎,下面查看一下表
能够看到,默认的存储引擎是 InnoDB
。
若是你的存储引擎想要更换,可使用
alter table cxuan003 engine = myisam;
来更换,更换完成后回显示 0 rows affected ,但其实已经操做成功
咱们使用 show create table
查看一下表的 sql 就知道
下面会介绍几个经常使用的存储引擎以及它的基本特性,这些存储引擎是 **MyISAM、InnoDB、MEMORY 和 MERGE **
在 5.1 版本以前,MyISAM 是 MySQL 的默认存储引擎,MyISAM 并发性比较差,使用的场景比较少,主要特色是
不支持事务
操做,ACID 的特性也就不存在了,这一设计是为了性能和效率考虑的。
不支持外键
操做,若是强行增长外键,MySQL 不会报错,只不过外键不起做用。
MyISAM 默认的锁粒度是表级锁
,因此并发性能比较差,加锁比较快,锁冲突比较少,不太容易发生死锁的状况。
MyISAM 会在磁盘上存储三个文件,文件名和表名相同,扩展名分别是 .frm(存储表定义)
、.MYD(MYData,存储数据)
、MYI(MyIndex,存储索引)
。这里须要特别注意的是 MyISAM 只缓存索引文件
,并不缓存数据文件。
MyISAM 支持的索引类型有 全局索引(Full-Text)
、B-Tree 索引
、R-Tree 索引
Full-Text 索引:它的出现是为了解决针对文本的模糊查询效率较低的问题。
B-Tree 索引:全部的索引节点都按照平衡树的数据结构来存储,全部的索引数据节点都在叶节点
R-Tree索引:它的存储方式和 B-Tree 索引有一些区别,主要设计用于存储空间和多维数据的字段作索引,目前的 MySQL 版本仅支持 geometry 类型的字段做索引,相对于 BTREE,RTREE 的优点在于范围查找。
数据库所在主机若是宕机,MyISAM 的数据文件容易损坏,并且难以恢复。
增删改查性能方面:SELECT 性能较高,适用于查询较多的状况
自从 MySQL 5.1 以后,默认的存储引擎变成了 InnoDB 存储引擎,相对于 MyISAM,InnoDB 存储引擎有了较大的改变,它的主要特色是
可重复读(repetable-read)
、经过MVCC(并发版本控制)
来实现的。可以解决脏读
和不可重复读
的问题。行级锁
,并发性能比较好,会发生死锁的状况。.frm文件存储表结构
定义,可是不一样的是,InnoDB 的表数据与索引数据是存储在一块儿的,都位于 B+ 数的叶子节点上,而 MyISAM 的表数据和索引数据是分开的。MEMORY 存储引擎使用存在内存中的内容来建立表。每一个 MEMORY 表实际只对应一个磁盘文件,格式是 .frm
。 MEMORY 类型的表访问速度很快,由于其数据是存放在内存中。默认使用 HASH 索引
。
MERGE 存储引擎是一组 MyISAM 表的组合,MERGE 表自己没有数据,对 MERGE 类型的表进行查询、更新、删除的操做,其实是对内部的 MyISAM 表进行的。MERGE 表在磁盘上保留两个文件,一个是 .frm
文件存储表定义、一个是 .MRG
文件存储 MERGE 表的组成等。
在实际开发过程当中,咱们每每会根据应用特色选择合适的存储引擎。
咱们会常常碰见的一个问题就是,在建表时如何选择合适的数据类型,一般选择合适的数据类型可以提升性能、减小没必要要的麻烦,下面咱们就来一块儿探讨一下,如何选择合适的数据类型。
char 和 varchar 是咱们常常要用到的两个存储字符串的数据类型,char 通常存储定长的字符串,它属于固定长度的字符类型,好比下面
值 | char(5) | 存储字节 |
---|---|---|
'' | ' ' | 5个字节 |
'cx' | 'cx ' | 5个字节 |
'cxuan' | 'cxuan' | 5个字节 |
'cxuan007' | 'cxuan' | 5个字节 |
能够看到,无论你的值写的是什么,一旦指定了 char 字符的长度,若是你的字符串长度不够指定字符的长度的话,那么就用空格来填补,若是超过字符串长度的话,只存储指定字符长度的字符。
这里注意一点:若是 MySQL 使用了非
严格模式
的话,上面表格最后一行是能够存储的。若是 MySQL 使用了严格模式
的话,那么表格上面最后一行存储会报错。
若是使用了 varchar 字符类型,咱们来看一下例子
值 | varchar(5) | 存储字节 |
---|---|---|
'' | '' | 1个字节 |
'cx' | 'cx ' | 3个字节 |
'cxuan' | 'cxuan' | 6个字节 |
'cxuan007' | 'cxuan' | 6个字节 |
能够看到,若是使用 varchar 的话,那么存储的字节将根据实际的值进行存储。你可能会疑惑为何 varchar 的长度是 5 ,可是却须要存储 3 个字节或者 6 个字节,这是由于使用 varchar 数据类型进行存储时,默认会在最后增长一个字符串长度,占用1个字节(若是列声明的长度超过255,则使用两个字节)。varchar 不会填充空余的字符串。
通常使用 char 来存储定长的字符串,好比身份证号、手机号、邮箱等;使用 varchar 来存储不定长的字符串。因为 char 长度是固定的,因此它的处理速度要比 VARCHAR 快不少,可是缺点是浪费存储空间,可是随着 MySQL 版本的不断演进,varchar 数据类型的性能也在不断改进和提升,因此在许多应用中,VARCHAR 类型更多的被使用。
在 MySQL 中,不一样的存储引擎对 CHAR 和 VARCHAR 的使用原则也有不一样
通常在保存较少的文本的时候,咱们会选择 CHAR 和 VARCHAR,在保存大数据量的文本时,咱们每每选择 TEXT 和 BLOB;TEXT 和 BLOB 的主要差异是 BLOB 可以保存二进制数据
;而 TEXT 只能保存字符数据
,TEXT 往下细分有
BLOB 往下细分有
三种,它们最主要的区别就是存储文本长度不一样和存储字节不一样,用户应该根据实际状况选择知足需求的最小存储类型,下面主要对 BLOB 和 TEXT 存在一些问题进行介绍
TEXT 和 BLOB 在删除数据后会存在一些性能上的问题,为了提升性能,建议使用 OPTIMIZE TABLE
功能对表进行碎片整理。
也可使用合成索引来提升文本字段(BLOB 和 TEXT)的查询性能。合成索引就是根据大文本(BLOB 和 TEXT)字段的内容创建一个散列值,把这个值存在对应列中,这样就可以根据散列值查找到对应的数据行。通常使用散列算法好比 md5() 和 SHA1() ,若是散列算法生成的字符串带有尾部空格,就不要把它们存在 CHAR 和 VARCHAR 中,下面咱们就来看一下这种使用方式
首先建立一张表,表中记录 blob 字段和 hash 值
向 cxuan005 中插入数据,其中 hash 值做为 info 的散列值。
而后再插入两条数据
插入一条 info 为 cxuan005 的数据
若是想要查询 info 为 cxuan005 的数据,能够经过查询 hash 列来进行查询
这是合成索引的例子,若是要对 BLOB 进行模糊查询的话,就要使用前缀索引。
其余优化 BLOB 和 TEXT 的方式:
浮点数指的就是含有小数的值,浮点数插入到指定列中超过指定精度后,浮点数会四舍五入,MySQL 中的浮点数指的就是 float
和 double
,定点数指的是 decimal
,定点数可以更加精确的保存和显示数据。下面经过一个示例讲解一下浮点数精确性问题
首先建立一个表 cxuan006 ,只为了测试浮点数问题,因此这里咱们选择的数据类型是 float
而后分别插入两条数据
而后执行查询,能够看到查询出来的两条数据执行的舍入不一样
为了清晰的看清楚浮点数与定点数的精度问题,再来看一个例子
先修改 cxuan006 的两个字段为相同的长度和小数位数
而后插入两条数据
执行查询操做,能够发现,浮点数相较于定点数来讲,会产生偏差
在 MySQL 中,用来表示日期类型的有 DATE、TIME、DATETIME、TIMESTAMP,在
这篇文中介绍过了日期类型的区别,咱们这里就再也不阐述了。下面主要介绍一下选择
下面来认识一下 MySQL 字符集,简单来讲字符集就是一套文字符号和编码、比较规则的集合。1960 年美国标准化组织 ANSI 发布了第一个计算机字符集,就是著名的 ASCII(American Standard Code for Information Interchange)
。自从 ASCII 编码后,每一个国家、国际组织都研究了一套本身的字符集,好比 ISO-8859-1
、GBK
等。
可是每一个国家都使用本身的字符集为移植性带来了很大的困难。因此,为了统一字符编码,国际标准化组织(ISO)
指定了统一的字符标准 - Unicode 编码,它容纳了几乎全部的字符编码。下面是一些常见的字符编码
字符集 | 是否认长 | 编码方式 |
---|---|---|
ASCII | 是 | 单字节 7 位编码 |
ISO-8859-1 | 是 | 单字节 8 位编码 |
GBK | 是 | 双字节编码 |
UTF-8 | 否 | 1 - 4 字节编码 |
UTF-16 | 否 | 2 字节或 4 字节编码 |
UTF-32 | 是 | 4 字节编码 |
对数据库来讲,字符集是很重要的,由于数据库存储的数据大多数都是各类文字,字符集对数据库的存储、性能、系统的移植来讲都很是重要。
MySQL 支持多种字符集,可使用 show character set;
来查看全部可用的字符集
或者使用
select character_set_name, default_collate_name, description, maxlen from information_schema.character_sets;
来查看。
使用 information_schema.character_set
来查看字符集和校对规则。
咱们上面介绍到了索引的几种类型并对不一样的索引类型作了阐述,阐明了优缺点等等,下面咱们从设计角度来聊一下索引,关于索引,你必需要知道的一点就是:索引是数据库用来提升性能的最经常使用工具。
全部的 MySQL 类型均可以进行索引,对相关列使用索引是提升 SELECT
查询性能的最佳途径。MyISAM 和 InnoDB 都是使用 BTREE
做为索引,MySQL 5 不支持函数索引
,可是支持 前缀索引
。
前缀索引顾名思义就是对列字段的前缀作索引,前缀索引的长度和存储引擎有关系。MyISAM 前缀索引的长度支持到 1000 字节,InnoDB 前缀索引的长度支持到 767 字节,索引值重复性越低,查询效率也就越高。
在 MySQL 中,主要有下面这几种索引
全局索引(FULLTEXT)
:全局索引,目前只有 MyISAM 引擎支持全局索引,它的出现是为了解决针对文本的模糊查询效率较低的问题,而且只限于 CHAR、VARCHAR 和 TEXT 列。哈希索引(HASH)
:哈希索引是 MySQL 中用到的惟一 key-value 键值对的数据结构,很适合做为索引。HASH 索引具备一次定位的好处,不须要像树那样逐个节点查找,可是这种查找适合应用于查找单个键的状况,对于范围查找,HASH 索引的性能就会很低。默认状况下,MEMORY 存储引擎使用 HASH 索引,但也支持 BTREE 索引。B-Tree 索引
:B 就是 Balance 的意思,BTree 是一种平衡树,它有不少变种,最多见的就是 B+ Tree,它被 MySQL 普遍使用。R-Tree 索引
:R-Tree 在 MySQL 不多使用,仅支持 geometry 数据类型,支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种,相对于 B-Tree 来讲,R-Tree 的优点在于范围查找。索引能够在建立表的时候进行建立,也能够单首创建,下面咱们采用单首创建的方式,咱们在 cxuan004 上建立前缀索引
咱们使用 explain
进行分析,能够看到 cxuan004 使用索引的状况
若是不想使用索引,能够删除索引,索引的删除语法是
建立索引的时候,要尽可能考虑如下原则,便于提高索引的使用效率。
索引位置
,选择索引最合适的位置是出如今 where
语句中的列,而不是 select
关键字后的选择列表中的列。惟一索引
,顾名思义,惟一索引的值是惟一的,能够更快速的肯定某条记录,例如学生的学号就适合使用惟一性索引,而学生的性别则不适合使用,由于无论搜索哪一个值,都差很少有一半的行。前缀索引
,若是索引的值很长,那么查询速度会受到影响,这个时候应该使用前缀索引,对列的某几个字符进行索引,能够提升检索效率。MySQL 从 5.0 开始就提供了视图功能,下面咱们对视图功能进行介绍。
视图的英文名称是 view
,它是一种虚拟存在的表。视图对于用户来讲是透明的,它并不在数据库中实际存在,视图是使用数据库行和列动态组成的表,那么视图相对于数据库表来讲,优点体如今哪里?
视图相对于普通的表来讲,优点包含下面这几项
视图的操做包括建立或者修改视图、删除视图以及查看视图定义。
使用 create view
来建立视图
为了演示功能,咱们先建立一张表 product
表,有三个字段,id,name,price,下面是建表语句
create table product(id int(11),name varchar(20),price float(10,2));
而后咱们向其中插入几条数据
insert into product values(1, "apple","3.5"),(2,"banana","4.2"),(3,"melon","1.2");
插入完成后的表结构以下
而后咱们建立视图
create view v1 as select * from product;
而后咱们查看一下 v1 视图的结构
能够看到咱们把 product 中的数据放在了视图中,也至关因而建立了一个 product 的副本,只不过这个副本跟表无关。
视图使用
show tables;
也能看到全部的视图。
删除视图的语法是
drop view v1;
可以直接进行删除。
视图还有其余操做,好比查询操做
你还可使用
describe v1;
查看表结构
更新视图
update v1 set name = "grape" where id = 1;
MySQL 从 5.0 开始起就支持存储过程和函数了。
那么什么是存储过程呢?
存储过程是在数据库系统中完成一组特定功能的 SQL 语句集,它存储在数据库系统中,一次编译后永久有效。那么使用存储过程有什么优势呢?
使用存储过程有什么缺点?
在认识到存储过程是什么以后,咱们就来使用一下存储过程,这里须要先了解一个小技巧,也就是 delimiter
的用法,delimiter 用于自定义结束符,什么意思呢,若是你使用
delimiter $$
的话,那么你在 sql 语句末使用 ;
是不能使 SQL 语句执行的,不信?咱们能够看下
能够看到,咱们在 SQL 语句的行末使用了 ;
可是咱们却没有看到执行结果。下面咱们使用
delimiter ;
恢复默认的执行条件再来看下
咱们建立存储过程首先要把 ;
替换为 $$
,下面是一个存储过程的建立语句
mysql> delimiter $$ mysql> create procedure sp_product() -> begin -> select * from product; -> end $$
存储过程其实是一种函数,因此建立完毕后,咱们可使用 call
方法来调用这个存储过程
由于咱们上面定义了使用 delimiter $$ 来结尾,因此这里也应该使用。
存储过程也能够接受参数,好比咱们定义一种接收参数的状况
而后咱们使用 call
调用这个存储过程
能够看到,当咱们调用 id = 2 的时候,存储过程的 SQL 语句至关因而
select * from product where id = 2;
因此只查询出 id = 2 的结果。
一次只能删除一个存储过程,删除存储过程的语法以下
drop procedure sp_product ;
直接使用 sp_product 就能够了,不用加 ()
。
存储过程建立后,用户可能须要须要查看存储过程的状态等信息,便于了解存储过程的基本状况
咱们可使用
show create procedure proc_name;
在 MySQL 中,变量可分为两大类,即系统变量
和用户变量
,这是一种粗略的分法。可是根据实际应用又被细化为四种类型,即局部变量、用户变量、会话变量和全局变量。
用户变量是基于会话变量
实现的,能够暂存,用户变量与链接有关,也就是说一个客户端定义的变量不能被其余客户端使用看到。当客户端退出时,连接会自动释放。咱们可使用 set
语句设置一个变量
set @myId = "cxuan";
而后使用 select
查询条件能够查询出咱们刚刚设置的用户变量
用户变量是和客户端有关系,当咱们退出后,这个变量会自动消失,如今咱们退出客户端
exit
如今咱们从新登录客户端,再次使用 select
条件查询
发现已经没有这个 @myId
了。
MySQL 中的局部变量与 Java 很相似 ,Java 中的局部变量是 Java 所在的方法或者代码块,而 MySQL 中的局部变量做用域是所在的存储过程。MySQL 局部变量使用 declare
来声明。
服务器会为每一个链接的客户端维护一个会话变量。可使用
show session variables;
显示全部的会话变量。
咱们能够手动设置会话变量
set session auto_increment_increment=1; 或者使用 set @@session.auto_increment_increment=2;
而后进行查询,查询会话变量使用
或者使用
当服务启动时,它将全部全局变量初始化为默认值。其做用域为 server 的整个生命周期。
可使用
show global variables;
查看全局变量
可使用下面这两种方式设置全局变量
set global sql_warnings=ON; -- global不能省略 /** 或者 **/ set @@global.sql_warnings=OFF;
查询全局变量时,可使用
或者是
MySQL 支持下面这些控制语句
IF 用于实现逻辑判断,知足不一样条件执行不一样的 SQL 语句
IF ... THEN ...
CASE 实现比 IF 稍微复杂,语法以下
CASE ... WHEN ... THEN... ... END CASE
CASE 语句也可使用 IF 来完成
LOOP 用于实现简单的循环
label:LOOP ... END LOOP label;
若是 ...
中不写 SQL 语句的话,那么就是一个简单的死循环语句
用来表示从标注的流程构造中退出,一般和 BEGIN...END 或者循环一块儿使用
ITERATE 语句必须用在循环中,做用是跳过当前循环的剩下的语句,直接进入下一轮循环。
带有条件的循环控制语句,当知足条件的时候退出循环。
REPEAT ... UNTIL END REPEAT;
WHILE 语句表示的含义和 REPEAT 相差无几,WHILE 循环和 REPEAT 循环的区别在于:WHILE 是知足条件才执行循环,REPEAT 是知足条件退出循环;
MySQL 从 5.0 开始支持触发器
,触发器通常做用在表上,在知足定义条件时触发,并执行触发器中定义的语句集合,下面咱们就来一块儿认识一下触发器。
举个例子来认识一下触发器:好比你有一个日志表和金额表,你每录入一笔金额就要进行日志表的记录,你会怎么样?同时在金额表和日志表插入数据吗?若是有了触发器,你能够直接在金额表录入数据,日志表会自动插入一条日志记录,固然,触发器不只只有新增操做,还有更新和删除操做。
咱们能够用以下的方式建立触发器
create trigger triggername triggertime triggerevent on tbname for each row triggerstmt
上面涉及到几个参数,我知道你有点懵逼,解释一下。
triggername
:这个指的就是触发器的名字triggertime
:这个指的就是触发器触发时机,是 BEFORE
仍是 AFTER
triggerevent
: 这个指的就是触发器触发事件,一共有三种事件:INSERT、UPDATE 或者 DELETE。tbname
:这个参数指的是触发器建立的表名,在哪一个表上建立triggerstmt
: 触发器的程序体,也就是 SQL 语句因此,能够建立六种触发器
BEFORE INSERT、AFTER INSERT、BEFORE UPDATE、AFTER UPDATE、BEFORE DELETE、AFTER DELETE
上面的 for each now
表示任何一条记录上的操做都会触发触发器。
下面咱们经过一个例子来演示一下触发器的操做
咱们仍是用上面的 procuct 表作例子,咱们建立一个 product_info 产品信息表。
create table product_info(p_info varchar(20));
而后咱们建立一个 trigger
咱们在 product 表中插入一条数据
insert into product values(4,"pineapple",15.3);
咱们进行 select 查询,能够看到如今 product 表中有四条数据
咱们没有向 product_info 表中插入数据,如今咱们来看一下 product_info 表中,咱们预想到是有数据的,具体来看下
这条数据是何时插入的呢?咱们在建立触发器 tg_pinfo
的时候插入了的这条数据。
触发器可使用 drop
进行删除,具体删除语法以下
drop trigger tg_pinfo;
和删除表的语法是同样的
咱们常常会查看触发器,能够经过执行 show triggers
命令查看触发器的状态、语法等信息。
另外一种查询方式是查询表中的 information_schema.triggers
表,这个能够查询指定触发器的指定信息,操做起来方便不少
注意:触发器的使用有两个限制
- 触发程序不能调用将数据返回客户端的存储程序。也不能使用 CALL 语句的动态 SQL 语句。
- 不能在触发器中开始和结束语句,例如 START TRANSACTION