MySQL重要但容易被忽略_MySQL自定义函数&存储过程

自定义SQL函数（UDF）

创建自定义函数

CREATE FUNCTION function_name
RETURNS
routime_body{#函数体
#可以带有（任意类型）多个参数、一个返回值
#参数数量理论上不能超过1024
#函数体由合法SQL语句构成
#也可以是简单的select/insert语句
#如果是符合结构请用BEGIN...END
#符合结构可以包含声明，循环，控制结构
}

实践

mysql -uroot -proot

Set names utf8

 

Create database test

Use test

Select now()

select DATE_FORMAT(NOW(),'%Y年%m月%d日 %H时%i分%s秒');

 

#封装上面为自定义函数

set names utf8;

#创建 不带参数 函数

CREATE FUNCTION formatDay() 

RETURNS VARCHAR(30) #返回参数类型

RETURN DATE_FORMAT(NOW(),'%Y年%m月%d日 %H时%i分%s秒'); #返回值

#创建 带参数 函数

CREATE FUNCTION f2 (num1 SMALLINT UNSIGNED,num2 SMALLINT UNSIGNED)

RETURNS FLOAT(10,2) UNSIGNED

RETURN (num1+num2)/2;

#创建 复合结构 函数

DELIMITER // #更改结束符

CREATE FUNCTION adduser(uname VARCHAR(20))

RETURNS INT UNSIGNED

<strong>BEGIN</strong>

INSERT test(uname) VALUES(uname);

RETURN LAST_INSERT_ID();

<strong>END</strong>

//

#调用函数

select formatDay();

#删除函数

drop function formatDay;

存储过程

Sql命令执行过程

 

存储过程是什么？

存储过程是SQL语句和控制语句的预编译集合，以一个名称存储并作为一个单元处理，允许输入类型参数，输出类型参数并可以存在多个参数值

 

优点：

增强sql语句功能和灵活性

实现较快的执行速度

较少网络流量

 

创建存储过程

CREATE 

[DEFINER = user_name] #创建用户

 PROCEDURE sp_name([param(IN | OUT | INOUT)])

routine_body{

    l 多个参数，多个返回值

    l 过程体只可操作CRUD

    l 如果是符合结构请用BEGIN...END

    l 符合结构可以包含声明，循环，控制结构

}

 

 

 

实践

#创建 不带参数 存储过程

CREATE PROCEDURE SP1() SELECT VERSION();

#创建 带IN参数 存储过程

DELIMITER //

CREATE PROCEDURE removeUserByid(IN test_id INT UNSIGNED)

BEGIN

DELETE FROM test WHERE id= test_id; #参数名不能与字段名重复（否则全删）

END

//

#创建 带IN&OUT参数 存储过程

DELIMITER // #不可加分号，否则就得//;才算结束

CREATE PROCEDURE removeUserandReturnUserNum(IN uid INT UNSIGNED,OUT unum INT UNSIGNED)

BEGIN

DELETE FROM test where id=uid;

SELECT COUNT(id) FROM test INTO unum;

END

//

 

CALL removeUserandReturnUserNum(1,@nums);

SELECT @nums;

#创建 带多个OUT参数 存储过程

DELIMITER //

CREATE PROCEDURE removeUserByIdandReturnUserNumandDeleteNums(IN uid INT UNSIGNED,OUT unum INT UNSIGNED,OUT dnum INT UNSIGNED)

BEGIN

DELETE FROM test WHERE id=uid;

SELECT ROW_COUNT() INTO dnum;

SELECT COUNT(id) FROM test INTO unum;

END

//

CALL removeUserByIdandReturnUserNumandDeleteNums(2,@unum,@dnum);

SELECT @unum,@dnum;

#修改存储过程

#只能修改注释，参数类型。。。不能修改过程体

ALTER PROCEDURE sp_name[]

COMMENT ‘string’

 

#调用存储过程

CALL sp1();

CALL removeUserandReturnUserNum(1,@nums);

SELECT @nums;

 

#删除存储过程

DROP PROCEDURE [IF EXISTS] sp_name

存储引擎

-Mysql以不同的技术存储在文件（内存）中，这种技术成为存储引擎

 

并发控制

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

这里用到锁

-共享锁（读锁）：在一段时间内，多个用户可以读取同一资源，读取过程中数据不会发生任何变化。

-排它锁（写锁）：在任何时间只能有一个用户写入资源，当进行写锁时阻塞其他读锁或者写锁操作

 

加锁只加对的，不加大的

加锁策略——锁颗粒

-表锁，开销最小的锁策略——用户对表操作时，拥有写锁策略

-行锁，开销最大的锁策略——每条记录都上写锁

 

事务

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

ACID原一隔

外键

-保持数据一致性的一种策略

索引

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

 

各种存储引擎特点

 

使用策略

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

InnoDB：使用事务处理多，且需要外键支持的情况

 

设置存储引擎

1.

Mysql.ini

-default-storage-engine=InnoDB

 

2.

CREATE TABLE `test` (

  `id` int(30) NOT NULL AUTO_INCREMENT,

  `username` varchar(30) NOT NULL,

  `age` int(5) NOT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

 

3.

ALTER TABLE tp1 ENGINE = MyISAM;