中国领先的IT技术网站
|
|

拆解 MySQL 的高阶使用与概念

这里我们主要分享一下MySQL中的高阶使用,主要包括:函数、存储过程和存储引擎。

作者:佚名来源:36大数据|2017-06-15 09:01

沙龙活动 | 去哪儿、陌陌、ThoughtWorks在自动化运维中的实践!10.28不见不散!


前面我们主要分享了MySQL中的常见知识与使用。这里我们主要分享一下MySQL中的高阶使用,主要包括:函数、存储过程和存储引擎。

1 函数

函数可以返回任意类型的值,也可以接收这些类型的参数。

字符函数

函数可以嵌套使用。

% (百分号):代表任意个字符。

_ (下划线):代表任意一个字符。

  1. # 删除前导'?'符号 
  2. SELECT TRIM(LEADING '?' FROM '??MySQL???'); 
  3. # 删除后续'?'符号 
  4. SELECT TRIM(TRAILING '?' FROM '??MySQL???'); 
  5. # 删除前后'?'符号 
  6. SELECT TRIM(BOTH '?' FROM '??My??SQL???'); 
  7. # 将'?'符号替换成'!'符号 
  8. SELECT REPLACE('??My??SQL???''?''!'); 
  9. # 从中'MySQL'第1个开始,截取2个字符 
  10. SELECT SUBSTRING('MySQL', 1, 2); 
  11. # 从中'MySQL'截取最后1个字符 
  12. SELECT SUBSTRING('MySQL', -1); 
  13. # 从中'MySQL'第2个开始,截取至结尾 
  14. SELECT SUBSTRING('MySQL', 2); 

数值运算符函数

比较运算符函数

日期时间函数

  1. # 时间增加1年 
  2. SELECT DATE_ADD('2016-05-28', INTERVAL 365 DAY); 
  3. # 时间减少1年 
  4. SELECT DATE_ADD('2016-05-28', INTERVAL -365 DAY); 
  5. # 时间增加3周 
  6. SELECT DATE_ADD('2016-05-28', INTERVAL 3 WEEK); 
  7. # 日期格式化 
  8. SELECT DATE_FORMAT('2016-05-28''%m/%d/%Y'); 
  9. # 更多时间格式可以前往MySQL官网查看手册 

信息函数

聚合函数

加密函数

自定义函数

用户自定义函数(user-defined function,UDF)是一种对MySQL扩展的途径,其用法与内置函数相同。UDF是对MySQL扩展的一种途径。

必要条件

  • 参数:可以有零个或多个
  • 返回值:只能有一个

参数和返回值没有必然的联系。

创建自定义函数

  1. CREATE FUNCTION function_name RETURNS {STRING|INTEGER|REAL|DECIMAL} routine_body 

函数体(routine_body)

  • 函数体由合法的SQL语句构成;
  • 函数体可以是简单的SELECT或INSERT语句;
  • 函数体如果为复合结构则使用BEGIN…END语句;
  • 复合结构可以包含声明,循环,控制结构。

示例

  1. # 不带参数 
  2. CREATE FUNCTION f1() RETURNS VARCHAR(30) RETURN DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s'); 
  3.  
  4. # 带参数 
  5. CREATE FUNCTION f2(num1 SMALLINT UNSIGNED, num2 SMALLINT UNSIGNED) RETURNS FLOAT(10, 2) UNSIGNED RETURN (num1 + num2) / 2; 
  6.  
  7. # 具有复合结构函数体 
  8. # 可能需要使用DELIMITER命令修改分隔符 
  9. CREATE FUNCTION f3(username VARCHAR(20)) RETURNS INT UNSIGNED  
  10. BEGIN  
  11. INSERT test(username) VALUES(username); 
  12. RETURN LAST_INSERT_ID(); 
  13. END 

2 存储过程

存储过程是SQL语句和控制语句的预编译集合,以一个名称存储作为一个单元处理。可以由用户调用执行,允许用户声明变量以及进行流程控制。存储过程可以接收输入类型的参数,也可以接收输出类型的参数,并可以存在多个返回值。执行效率比单一的SQL语句高。

优点

  • 增强SQL语句的功能和灵活性

在存储过程中可以写控制语句具有很强的灵活性,可以完成复杂的判断及较复杂的运算。

  • 实现较快的执行速度

如果某一操作包含了大量的SQL语句,那么这些SQL语句都将被MySQL引擎执行语法分析、编译、执行,所以效率相对过低。而存储过程是预编译的,当客户端第一次调用存储过程时,MySQL的引擎将对它进行语法分析、编译等操作,然后把这个编译的结果存储到内存中,所以说第一次使用的时候效率和以前是相同的。但是以后客户端再次调用这个存储过程时,直接从内存中执行,所以说效率比较高,速度比较快。

  • 减少网络流量

如果通过客户端每一个单独发送SQL语句让服务器来执行,那么通过http协议来提交的数据量相对来说较大。

创建

  1. CREATE [DEFINER = {user|CURRENT_USER}] PROCEDURE sp_name ([proc_parameter[, ...]]) [characteristic ...] routine_body 

proc_parameter :

[IN | OUT | INOUT] param_name type

参数:

IN ,表示该参数的值必须在调用存储过程时指定。

OUT ,表示该参数值可以被存储过程改变,并且可以返回。

INOUT ,表示该参数的调用时指定,并且可以被改变和返回。

特性:

COMMENT 注释

CONTAINS SQL 包含SQL语句,但不包含读或写数据的语句。

NO SQL 不包含SQL语句。

READS SQL DATA 包含读写数据的语句。

MODIFIES SQL DATA 包含写数据的语句。

SQL SECURITY {DEFINER | INVOKER} 指明谁有权限来执行。

过程体

  • 过程体由合法的SQL语句构成;
  • 过程体可以是任意SQL语句;
  • 不能通过存储过程来创建数据表、数据库。可以通过存储过程对数据进行增、删、改、查和多表连接操作。
  • 过程体如果为复合结构则使用BEGIN…END语句;
  • 复合结构中可以包含声明、循环、控制结构。

调用

  1. CALL sp_name ([parameter[, ...]]) 
  2. CALL sp_name[()] 

删除

  1. DROP PROCEDURE [IF EXISTS] sp_name 

修改

  1. ALTER PROCEDURE sp_name [characteristic ...] COMMENT 'string' 
  2. | {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA} 
  3. | SQL SECURITY {DEFINER | INVOKER} 

存储过程与自定义函数的区别

  • 存储过程实现的功能要复杂一些,而函数的针对性更强。
  • 存储过程可以返回多个值,函数只能有一个返回值。
  • 存储过程一般独立执行,函数可以作为其他SQL语句的组成部分来实现。

示例:

  1. # 创建不带参数的存储过程 
  2. CREATE PROCEDURE sp1() SELECT VERSION(); 
  3.  
  4. # 创建带有IN类型参数的存储过程(users为数据表名) 
  5. # 参数的名字不能和数据表中的记录名字一样 
  6. CREATE PROCEDURE removeUserById(IN p_id INT UNSIGNED) 
  7. BEGIN 
  8. DELETE FROM users WHERE id = p_id; 
  9. END 
  10.  
  11. # 创建带有INOUT类型参数的存储过程(users为数据表名) 
  12. CREATE PROCEDURE removeUserAndReturnUserNumsById(IN p_id INT UNSIGNED, OUT userNums INT UNSIGNED) 
  13. BEGIN 
  14. DELETE FROM users WHERE id = p_id; 
  15. SELECT COUNT(id) FROM users INTO userNums; 
  16. END 
  17.  
  18. # 创建带有多个OUT类型参数的存储过程(users为数据表名) 
  19. CREATE PROCEDURE removeUserAndReturnInfosByAge(IN p_age SMALLINT UNSIGNED, OUT delUser SMALLINT UNSIGNED,  OUT userNums SMALLINT UNSIGNED) 
  20. BEGIN 
  21. DELETE FROM users WHERE age = p_age; 
  22. SELECT ROW_COUNT INTO delUser; 
  23. SELECT COUNT(id) FROM users INTO userNums; 
  24. END 

3 存储引擎

MySQL可以将数据以不同的技术存储在文件(内存)中,这种技术就称为存储引擎。

每一种存储引擎使用不同的存储机制、索引技巧、锁定水平,最终提供广泛且不同的功能。

共享锁(读锁):在同一时间段内,多个用户可以读取同一个资源,读取过程中数据不会发生任何变化。

排他锁(写锁):在任何时候只能有一个用户写入资源,当进行写锁时会阻塞其他的读锁或者写锁操作。

  • 锁颗粒

表锁:是一种开销最小的锁策略。

行锁:是一种开销最大的锁策略。

  • 并发控制

当多个连接记录进行修改时保证数据的一致性和完整性。

  • 事务

事务用于保证数据库的完整性。

  1. 举例:用户银行转账
  2. 用户A 转账200元 用户B

实现步骤:

1)从当前账户减掉200元(账户余额大于等于200元)。

2)在对方账户增加200元。

事务特性:

1)原子性(atomicity)

2)一致性(consistency)

3)隔离性(isolation)

4)持久性(durability)

  • 外键

是保证数据一致性的策略。

  • 索引

是对数据表中一列或多列的值进行排序的一种结构。

类型

MySQL主要支持以下几种引擎类型:

  • MyISAM
  • InnoDB
  • Memory
  • CSV
  • Archive

各类存储引擎特点

CSV:实际上是由逗号分隔的数据引擎,在数据库子目录为每一个表创建一个 .csv 的文件,这是一种普通的文本文件,每一个数据行占用一个文本行。不支持索引。

BlackHole:黑洞引擎,写入的数据都会消失,一般用于做数据复制的中继。

MyISAM:适用于事务的处理不多的情况。

InnoDB:适用于事务处理比较多,需要有外键支持的情况。

索引分类:普通索引、唯一索引、全文索引、btree索引、hash索引…

修改存储引擎

  • 通过修改MySQL配置文件
  1. default-storage-engine=engine_name 
  • 通过创建数据表命令实现
  1. CREATE TABLE table_name(...)ENGINE=engine_name 
  • 通过修改数据表命令实现
  1. ALTER TABLE table_name ENGINE[=]engine_name 

4 管理工具

  • phpMyAdmin

需要有PHP环境

  • Navicat
  • MySQL Workbench

【编辑推荐】

  1. 性能优化 | MySQL常见SQL错误用法
  2. Mysql死锁以及死锁日志分析
  3. MySQL服务器发现大量TIME_WAIT解决办法
  4. MySQL表名忽略大小写问题记录
  5. MySQL死锁问题分析及解决方法实例详解
【责任编辑:武晓燕 TEL:(010)68476606】

点赞 0
分享:
大家都在看
猜你喜欢

读 书 +更多

精通EJB 3.0

本书是关于EJB 3.0的专业技术教程,专注于EJB的概念、方法、开发过程的介绍。同时,本书还研究许多高端的EJB知识,使得开发者能够真正理解...

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊
× Python最火的编程语言