有关MySQL的命令语句（三）

一、存储过程和函数 

  存储过程和函数是在数据库中定义一些SQL语句的集合，然后直接调用这些存储过程和函数来执行已经定义好的SQL语句。

存储过程和函数可以避免开发人员重复的编写相同的SQL语句。而且，存储过程和函数是在MySQL服务器中存储和执行的，可以减少客户端和服务器端的数据传输。 

1.存储过程

（1）基本语法：

CREATE PROCEDURE proc_name ( [proc_parameter] )
[characteristics ...] routine_body

 CREATE PROCEDURE：用来创建存储函数的关键字；proc_name：为存储过程的名称；默认在当前数据库中创建。

避免与MySQL的内置函数的名字一样。

proc_parameter：指定存储过程的参数列表； 列表的形式如下： 

[IN | OUT | INOUT ] param_name type

　　1）IN:输入参数； 

　　2）OUT:输出参数； 

　　3）INOUT:即可以输入也可以输出； 

　　4）param_name：表示参数名称； 

　　5）type：表示参数的类型，可以是MySQL数据库中的任意类型。 

Characteristic:存储过程的某些特征设定，分别介绍：
　　1）COMMENT'string': 用于对存储过程的描述，其中string为描述内容,comment为关键字。

　　2）LANGUAGE SQL: 指明编写这个存储过程的语言为SQL语言。这个选项可以不指定。

　　

 

 

 

  • No sql：表示不包含sql语句
  • Reads sql data ：表示存储过程只包含读数据的语句
  • Modifies sql data ：表示存储过程只包含写数据的语句

　　5）sql security:这个特征用来指定存储过程使用创建该存储过程的用户(definer)的许可来执行，还是使用调用者(invoker)的许可来执行。默认是definer.

Routine_body:存储过程的主体部分，包含了在过程调用的时候必须执行的sql语句。以BEGIN开始，以END结束。

如果存储过程体中只有一条sql语句,可以省略begin-end标志。

 

 

 

1.2 数据准备 

mysql> CREATE TABLE t_user
    -> (
    -> USER_ID INT NOT NULL AUTO_INCREMENT,
    -> USER_NAME CHAR(30) NOT NULL,
    -> USER_PASSWORD CHAR(10) NOT NULL,
    -> USER_EMAIL CHAR(30) NOT NULL,
    -> PRIMARY KEY (USER_ID),
    -> INDEX IDX_NAME (USER_NAME)
    -> )
    -> ENGINE=InnoDB DEFAULT CHARSET=utf8;

  插入数据：

INSERT INTO t_user (USER_ID, USER_NAME, USER_PASSWORD, USER_EMAIL)
VALUES
 (1,'林炳文','1234567@','ling2008@126.com'),
 (2,'123','123','fff'), 
(3,'1214gbdb','123456','fwsfg@126.com'),
(4,'fsjdjhihi','1234567','fsaf@126.com'),
(5,'fajkjiiuhi','123456','fsaff@126.com'),
(6,'hiouoewh','12345678','fdfas@126.com'),
(7,'fdasfdsaf','1234567','fsaf@126.com'),
(8,'林林之间','123456','lin@126.com'),
(9,'林炳文Evan...','123456','ling2008@com'),
(10,'ff23e林','123456@','ff@qq.com');

1. 3 IN、OUT、INOUT参数

带IN的存储过程

mysql> CREATE PROCEDURE SP_SEARCH(IN p_name CHAR(20))
    -> BEGIN
    -> IF p_name is null or p_name='' THEN
    -> SELECT * FROM t_user;
    -> ELSE
    -> SELECT * FROM t_user WHERE USER_NAME LIKE p_name;
    -> END IF;
    -> END
    -> //

 因为；会冲突，因此将//设置为结束运行符号。

mysql> delimiter //

PS：可以使用“DELIMITER;” 恢复默认结束符；DELIMITER也可定义其他符号作为结束符，但避免使用反斜杠“\”，因为“\”是SQL中的转义字符。

 调用：

//调用并输出结果 
CALL SP_SEARCH('林炳文')

 带OUT的存储过程

mysql> CREATE PROCEDURE SP_SEARCH2(IN p_name CHAR(20),OUT p_int INT)
    -> BEGIN
    -> IF p_name is null or p_name='' THEN
    -> SELECT * FROM t_user;
    -> ELSE
    -> SELECT * FROM t_user WHERE USER_NAME LIKE p_name;
    -> END IF;
    -> SELECT FOUND_ROWS() INTO p_int;
    -> END//

  调用输出：统计带林开头的人数；

mysql> CALL SP_SEARCH2('林%',@p_num);
    -> SELECT @p_num;

 带INOUT的存储过程

mysql> CREATE PROCEDURE sp_inout(INOUT p_num INT)
    -> BEGIN
    -> SET p_num=p_num*10;
    -> END//

//调用并输出结果 

mysql> SET @p_num=2;
    -> call sp_inout(@p_num);
    -> SELECT @p_num;
    -> //

2、 存储过程体    

存储过程体中可以使用各种sql语句和过程式语句的组合，来封装数据库应用中复杂的业务逻辑和处理规则，以实现数据库应用的灵活编程。

下面主要介绍几个用于构造存储过程体的常用语法元素。

2.1 局部变量

在存储过程体中可以声明局部变量，用来存储存储过程体中临时结果。

DECLARE var_name[,…] type [DEFAULT value] 
Var_name:指定局部变量的名称 
Type:用于声明局部变量的数据类型 
default子句:用于为局部变量指定一个默认值。若没有指定，默认为null.

如：

Declare cid int(10);

使用说明：

局部变量只能在存储过程体的begin…end语句块中声明。
局部变量必须在存储过程体的开头处声明。
局部变量的作用范围仅限于声明它的begin..end语句块，其他语句块中的语句不可以使用它。
局部变量不同于用户变量，两者区别：局部变量声明时，在其前面没有使用@符号，并且它只能在begin..end语句块中使用；

而用户变量在声明时，会在其名称前面使用@符号，同时已声明的用户变量存在于整个会话之中。

2.2 set语句

使用set语句为局部变量赋值

Set var_name=expr  
Set cid=910;  

2.3 select... into 语句

把选定列的值直接存储到局部变量中，语法格式

Select col_name[,…] into var_name[,…] table_expr 
Col_name:用于指定列名 
Var_name:用于指定要赋值的变量名 
Table_expr:表示select语句中的from字句及后面的语法部分

说明:存储过程体中的select…into语句返回的结果集只能有一行数据。

2.4 定义处理程序

是事先定义程序执行过程中可能遇到的问题。并且可以在处理程序中定义解决这些问题的办法。这种方式可以提前预测可能出现的问题，并提出解决方法。

DECLARE handler_type HANDLER FOR condition_value[,…] sp_statement 
handler_type:CONTINUE | EXIT | UNDO 
Condition_value:Sqlwarning | not found | sqlexception

3. 存储过程和函数的区别

存储过程，存储过程实现的功能比较复制，功能强大，可以执行包括修改表等一系列数据库操作。

存储函数，实现的功能针对性比较强。

返回值上的不同

存储过程：可以返回多个值，也可以不返回值，只是实现某种效果或动作。

存储函数：必须有返回值，而且只能有一个返回值。

参数不同

存储过程：存储过程的参数类型有三种，in,out,inout。

存储函数：参数类型只有一种，类似于in参数，调用函数时需要按照参数的类型指定值即可。

语法结构

存储过程，存储过程声明时不需要指定返回类型。

存储函数，函数声明时需要指定返回类型，且在函数体中必须包含一个有效的return语句。

调用方式

存储过程，用call语句进行调用

存储函数，嵌入在sql中使用的，可以在select中调用

4. 事务

事务必须满足的四个条件：

  1.  
    atomicity 原子性
  2.  
    consistency 一致性
  3.  
    lsolation 隔离性
  4.  
    durability 持久性
控制事务处理

rollback，回滚会结束用户的事务，并撤销正在进行的所有未提交的修改

commit，会提交事务，并使已对数据库进行的所有修改称为永久性的

savepoint identifier，允许在事务中创建一个保存点，一个事务中可以有多个savepoint

rollback to identifier，把事务回滚到标记点

事务处理主要有两种方法

用begin, rollback, commit来实现

  1. begin,start transaction开始一个事务
  2. rollback事务回滚
  3. commit事务确认

直接用set来改变mysql的自动提交模式

  1. set autocommit = 0 禁止自动提交
  2. set autocommit = 1 开始自动提交

4.1 innodb使用事务

从Mysql5.5版本开始,InnoDB是默认的表存储引擎。

innodb是事务型数据库的首选引擎，支持事务安全表。

MySql中 delimiter

默认下，delimiter是分号，在命令行客户端中，如果有一行命令以分号结束，那么回车后，mysql将会执行该命令。

(告诉mysql解释器，该段命令是否已经结束了，mysql是否可以执行了。)

什么是存储引擎：数据库存储引擎是数据库底层软件组件。数据库管理系统使用数据引擎进行创建，查询，更新和删除数据的操作。

mysql的核心就是存储引擎。

innodb存储引擎

  • 它为mysql提供了具有提交，回滚和崩溃恢复能力的事务安全存储引擎。
  • 对于处理巨大数据量的数据拥有很好的性能
  • innodb存储引擎支持外键完整性约束
  • innodb被用在众多需要高性能的大型数据库站点上

设置存储引擎：

  1. 设置服务器的存储引擎
  2. 在配置文件my.ini中的mysqld下面设置需要的存储引擎
  3. default-storage-engine=InnoDB
  4. 重启mysql服务器
创建表（单个）设置存储引擎
create table mytest(
 id int primary key,
 name varchar(10)
) engine = innodb default charset = utf8;

 

alter table tablename engine = engineName

   

5. 管理与维护

管理用户

 USE mysql;
 select user from user;

 

权限表：存储账号的权限信息表：user,db,host,tables_priv,columns_priv 和 procs_priv 。

各个权限表的作用

tables_priv表用来对表设置操作权限；columns_priv表用来对表的某一列设置权限；procs_priv表可以对存储过程和存储函数设置操作权限。

使用CREATE USER语句创建新用户

语法格式：

CREATE USER “user”@“host” [IDENTIFIED BY “password”];

 

使用DROP USER 语句删除用户

语法格式：

 DROP USER user[, user];
 例：使用DROP USER删除账户"rose"@"localhost":
 DROP USER "rose"@"localhost";

 

示例：

查看日志文件的路径
show variables like 'log_error';
 
创建新的日志信息表
flush logs;
 
创建新的日志信息表
mysqladmin -uroot -p flush-logs

 

posted @ 2020-12-06 16:01  哈哈哈嘿呀  阅读(322)  评论(0)    收藏  举报