存储程序：可以分为存储过程和函数。

存储过程：就是一组SQL语句集，功能强大，实现复杂的逻辑功能，且因为其预编译与数据库内，执行效率较高。

存储过程

CREATE PROCEDURE 名字（[proc_parameter]）

[characteristies…]  routine_body

proc_parameter:指定存储过程中参数的列表；可选值为 IN 、OUT 、INOUT

IN: 表示输入参数

OUT：表示输出参数

INOUT：表示既可以输入也可以输出。

routine_body:是SQL代码的内容，用begin…end来表示SQL代码的开始和结束。

characteristies:指定存储过程的特性,（一般不指定该参数）取值如下：

LANGUAGE SQL

说明routine_body部分是由SQL语句组成，当前系统支持的语言为SQL，SQL是LANGUAGE特性的唯一值

[NOT]DETERMINSTIC

指明存储过程的执行结果是否正确

{CONTAINS SQL|

NO SQL|

REASD SQL DATA|

MODIFIES SQL DATA }

指明子程序使用SQL语句的限制，第一个表示子程序包含SQL，但不包含读写数据语句（默认）；第二个表示不包含SQL语句；第三个表示子程序包含读数据的语句；第四个表示子程序包含写数据语句。

SQL SECURITY{DEFINER|INVOKER}

指明谁有权限来执行。DEFINER表示只有定义者能执行（默认），INVOKER表示拥有权限调用者来执行。

COMMENT

注释信息

 

创建一个简单存储过程：

CREATE PROCEDURE count_user()
BEGIN
SELEC COUNT(id)  FROM t_user;
END

若使用的是MySQL客户端：

mysql>DELIMITER  //
mysql> CREATE PROCEDURE count_user()
BEGIN
SELEC COUNT(id)  FROM t_user;
END
Mysql> DELIMITER  ;

因为MySQL中默认结束符为分号，避免与存储过程中SQL语句结束符相冲突，需要改变结束符，完成之后修改回默认的分号。

存储过程使用 call 存储过程名字; 进行调用。

 

存储函数

常见存储函数

CREATE FUNCTION 函数名（）
RETURNS type
[characteristic] routine_body

 

RETURNS type：函数的返回值。

其他参数意义与存储过程相同

CREATE FUNCTION user_conunt()
RETURNS  CHAR(50)
RETURN (SELECT user_name  FROM t_user WHERE id=2);

 

存储过程是一个SQL语句的集合，同时也会包含逻辑业务，所以会用到一些逻辑语句，下面介绍一些常用的规则。

变量的使用

DECLARE 变量名1，变量名2… 变量类型 [DEFAULT value];

没有[DEFAULT]子句，则默认为null

如： DECLARE  u_name  VARCHAR（10）;      DECLARE  number INT  100;

  1. MySQL中使用SET语句为变量赋值

SET number=number+10;

  1. 通过SELECT…INTO 为一个或者多个变量赋值。
DECLARE f_name CHAR(50);
DECLARE f_price  INT;
SELECT fruit_name,fruit_price INTO f_name,f_price FROM t_fruits WHERE f_id=1;

注：@var_name表示用户变量，使用SET语句为其赋值，用户变量与连接有关，一个客户端定义的变量不能被其他客户端看到或使用，当客户端退出时，该客户端连接的所有变量将自动释放，DECLARE定义的变量只在存储过程中的begin和end之间生效。DECLARE类似局部变量，@用户变量则类似于全局变量。

光标的使用

查询语句可能返回多条记录，如果数据量非常大，需要在存储过程和存储函数中使用光标来逐条读取查询结果集中的记录。

光标必须在声明处理程序之前被声明，并且变量和条件还必须在声明光标或处理程序之前被声明。

声明光标：

DECLARE 光标名 CURSOR FOR 查询语句

如： DECLARE cursor_name CURSOR FOR SELECT user_name,user_password FROM t_user;

打开光标：

OPEN 光标名

使用光标

FETCH 光标名  INTO 变量名1，变量名2…ZZ

关闭光标

CLOSE 光标名。

MySQL中光标只能在存储过程和函数中使用

列子：

mysql数据库如何存储集合 mysql数据库存储数据_数据库

流程控制

IF语句

IF语句包含多个条件，根据判断条件为TRUE或FALSE执行相应的语句

IF  val IS NULL
THEN XXXX
ELSE XXXX;
END IF;

CASE语句  

CASE val
WHEN 1 THEN XXXX;
WHEN 2 THEN XXXX;
…
ELSE XXX;
END CASE;

LOOP语句

LOOP循环语句用来重复执行某些语句，LOOP语句只是创建一个循环操作的过程，并不进行条件判断。LOOP内的语句一直重复执行直到循环被退出，跳出循环过程，使用LEAVE语句。

[loop_lable:] LOOP
Statement_list
END LOOP [loop_lable]

Loop_able:表示LOOP语句的标注名称，可以省略；Statement_list表示参数循环执行的语句。

例子：

DECLARE id INT DEFAULT 0;
add_loop:LOOP
SET id=id+1;
IF id>10 THEN LEAVE
END IF
END LOOP add_loop;

 LEAVE语句

用来退出任何被标注的流程控制构造。

 

ITERATE语句

将执行顺序转到语句段开头处。ITERATE只可以出现在LOOP、REPEAT和WHILE语句内。意思为”再次循环”。

DECLARE id INT DEFAULT 0;
add_loop:LOOP
SET id=id+1;
IF id<10 ITERATE add_loop;
IF id>20 THEN LEAVE add_loop;
END IF
SELECT ‘ id is between 10 and 20’;
END LOOP add_loop;

当id小于10时执行+1操作，id大于等于10小于等于20时打印’id is between 10 and 20’，当id大于20退出循环

REPEAT语句 

创建一个带条件判断的循环过程，每次语句执行完毕之后，会对条件表达式进行判断，如果表达式为真，则循环结束；否则循环重复执行。（先执行后判断）

DECLARE  id INT DEFAULT 0;
REPEAT
SET id=id+1;
UNTIL id>=10
END REPEAT;

WHILE语句

创建一个带条件判断的循环过程，会先对条件表达式进行判断，如果表达式为真，则重复循环；否则循环结束。（先判断后执行）

DECLARE  id INT DEFAULT 0;
WHILE i<10 DO
SET i=i+1;
END WHILE;

注：

  1. 存储过程和存储函数本质上都是存储程序。函数只能通过return语句返回单个值或者表对象；而存储过程不允许执行return，但是可以通过out参数返回多个值。
  2. 存储过程一旦创建，不能修改存储过程中的代码，只能删除后重建。
  3. 存储过程之间能够相互调用，但不能使用DROP语句删除其他存储过程。
  4. 存储过程的变量名要避免与表字段名冲突，否则会出现无法预期的结果。

性能优化

一些MySQL数据库的性能参数。

SHOW  STATUS  LIKE  ‘value’;

其中，value是要查询的参数值，一些常用的性能参数如下：

Connections:连接MySQL服务器的次数。

Uptime:MySQL服务器的上线时间。

Slow_queries:慢查询的次数。

Com_select:查询操作的次数。

Com_insert:插入操作的次数。

Com_update:更新操作的次数。

Com_delete:删除操作的次数。

优化查询

EXPLAIN关键字分析查询语句。

EXPLAIN SELECT

如：

EXPLAIN  SELECT  *  FROM  sys_user

mysql数据库如何存储集合 mysql数据库存储数据_数据库_02

id:SELECT识别符，这是SELECT的查询序列号。

1. id相同时，执行顺序由上至下

2. 如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

3.id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行

select_type:表示SELECT语句的类型。取值如下：

取值

说明

SIMPLE

表示简单查询，其中不包括连接查询和子查询。

PRIMARY

表示主查询，或者是最外层的查询。

UNION

表示连接查询的第2个或后面的查询语句。

DEPENDENT  UNION

表示连接查询中的2个或后面的SELECT语句，取决于外面的查询

UNION  RESULT

连接查询的结果，

SUBQUERY

子查询中的第1个SELECT语句

DEPENDENT SUBQUERY

子查询中的第1个SELECT，取决于外面的查询

DERIVED

导出表的SELECT（FROM子句的子查询）。

 

table:表示查询的表

type:表示表的连接类型

下面按照最佳到最差罗列出选项的值

取值

说明

system

该表是仅有一行的系统表。这是const连接类型的一个特例，平时不会出现

const

表示通过索引一次就找到了，const即常量，它用于比较primary key或unique索引，因为只匹配一行数据，所以效率快，如将主键置于where条件中，mysql就能将该查询转换为一个常量。

eq_ref

对于每个来自前面的表的行组合，从该表中读取一行。（唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。）

ref

对于来自前面的表的任意行组合，将从该表中读取所有匹配的行。（非唯一性索引扫描，返回匹配某个单独值的行，可能多行。）

index_merge

表示使用了索引合并优化方法，key列包含了使用的索引清单，key_len包含了

使用的索引的最长关键元素。

range

只检索给定范围的行，使用一个索引来选择行。key显示使用了哪个索引，key_len包含了使用的索引的最长关键元素。

Index

该连接类型只是扫描完整的索引树。

ALL

对于前面表的任意组合，进行完整的整表扫描。

 

注：一般情况下,得至少保证达到range级别,最好能达到ref

possible_keys：指出MySQL使用哪个索引在表中找到了行，如果为NULL，表名没有使用索引。

key：表示查询中实际使用到的索引。

key_len:表示MySQL选择的索引字段按字节计算的长度。（可以确定实际使用组合索引时使用其中的哪几个字段）

ref:表示使用哪个列或者常数与索引一起来查询记录

rows:显示MySQL在表中进行查询时必须检查的行数。

Extra：MySQL处理查询时的详细信息。

取值

说明

Using where

使用了where进行过滤

Using temporary

表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询

Using filesort

MySQL中无法利用索引完成的排序操作称为“文件排序”

Using join buffer

该值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。

Impossible where

这个值强调了where语句会导致没有符合条件的行。（表示where子句的值总是false,不能用来获取任何元素）

Select tables optimized away

这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行

 

 

开启慢查询日志

慢查询日志是记录查询时长超过指定时间的日志。慢查询日志主要用来记录执行时间较长的查询语句。

在my.ini或者my.cnf开启慢查询日志。

[mysqld]
log-slow-queries[=path/[filename]]     //path为日志文件所在目录，filename为日志文件名。
long_query_time=n       //设置的查询最长时间，单位为秒，默认10秒

优化MySQL参数

MySQL服务的配置参数可以提高资源利用率，MySQL服务的配置参数都在my.cnf或者my.ini文件的[mysqld]组中，下面对几个性能影响比较大的参数进行详细介绍。

 

key_buffer_size:表示索引缓冲区的大小。增加索引缓冲区可以得到更好处理的索引；当然并不是越大越好，取决于内存的大小。

table_cache:表示同时打开的表的个数。

innodb_buffer_pool_size:表示InnoDB类型的表和索引的最大缓存。

query_cache_size:表示查询缓冲区的大小，该参数需要和query_cache_type配合使用。

当query_cache_type=0时，代表所有的查询都不使用查询缓冲区。

当query_cache_type=1时，代表所有的查询都使用查询缓冲区，除非在查询语句中指定 SQL_NO_CACHE,如：SELECT SQL_NO_CACHE *  FROM t_user.

当query_cache_type=2时，代表只有使用SQL_CACHE关键字，查询时才会使用查询缓冲区。