来自 计算机教程 2020-04-25 11:31 的文章
当前位置: 美洲杯冠军竞猜 > 计算机教程 > 正文

MySQL自定义函数和存储过程示例详解篮球世界杯冠

前言

篮球世界杯冠军竞猜,运算符与函数
字符函数
字符函数<br>
1.CONCAT() ,字符连接<br>
SELECT CONCAT('a','-','b'); 结果为:a-b<br>
2.CONCAT_WS(), 使用指定的分隔符进行字符连接<br>
SELECT CONCAT_WS('|','A','B','C'); 结果为: A|B|C<br>
3.FORMAT() 数字格式化
SELECT FORMAT(12560.7,2); 结果:12,560.70
SELECT FORMAT(12560.78,1); 结果:12,560.8
4.LOWER() 转换成小写字母
5.UPPER() 转换成大写字母
6.LEFT() 获取左侧字符
SELECT LEFT('mysql',2); 结果:my
7.RIGHT() 获取右侧字符
8.LTRIM() 删除前导空格(=LEFT TRIM())
9.RTRIM() 删除后续空格
10.TRIM()删除前后两边的空格,还可以删除指定的前导和后续的字符,不能删除中间的字符
SELECT TRIM(LEADING'?','??MYSQL????'); 结果:MYSQL????
SELECT TRIM(TRAILING'?','??MYSQL????'); 结果:??MYSQL
SELECT TRIM(BOTH'?','??MYSQL???'); 结果:MYSQL
11.REPLACE() 替换字符
SELECT REPLACE('??MYSQL???','?','-'); 结果:--MYSQL---

本文主要给大家介绍的是关于MySQL自定义函数和存储过程的相关内容,分享出来供大家参考学习,下面话不多说了,来一起看看详细的介绍吧

  1. SUBSTRING(string,offset,length) 截取字符串
    SELECT SUBSTRING('MYSQL',2,3); 结果:SQL
    13.[NOT]LIKE 模糊匹配
    (%):代表任意个字符,0个或多个
    (_):代表任意一个字符,只有一个
    SELECT name FROM test WHERE name LIKE'%O%'; 结果:输入name 中带‘O’的name
    SELECT name FROM test WHERE name LIKE'%1%%' ESCAPE'1'; 找到中间带% 的匹配name
    数值运算符和函数
    CEIL()--进一取正
    FLOOR()--舍一取正
    DIV--整数除法
    MOD --取余
    POWER() --幂运算
    ROUND() --取余(四舍五入)
    TRUNCATE() --截断(不四舍五入)
    SELECT CEIL(3.01) ==>4 /*有n.xx 都是n 1 进一取整 向上取整
    SELECT FLOOR(3.99) ==>3 /*取n.xx 都是n 舍一取整 向下取整
    SELECT 3 DIV 4 ==> 0 /*整数除法
    SELECT 3/4 ==>0.75 /*除法
    SELECT 21 MOD 2 ==>1 /*取余数(取模)整数,小数都可以
    SELECT 21 % 2 ==>1 /*取余数(取模)整数,小数都可以
    SELECT POWER(3,4) ==>81 /*3的4次方 幂运算
    SELECT ROUND(3.1415926,4) ==>3.142 /*四舍五入
    SELECT TRUNCATE(123.89,1) ==>123.8 /*截取小数点后的位置
    SELECT TRUNCATE(123.89,0) ==>123 /*截取小数点后的位置,0位为整数部分
    SELECT TRUNCATE(123.89,-1) ==>120 /*截取-1,从个位起去掉后面的数值替换为0
    比较运算符和函数
    比较运算符和函数:(给出的都是闭合的区间)
    (1)想在first_name这个字段中查找哪个字段值为NULL,可以用IS [NOT] NULL,这比较运算符,如:SELECT * FROM test WHERE first_name IS NULL;
    (2)[NOT] IN 的具体用法,SELECT 16 IN(1,2,16,68),返回的是true就是1.
    (3)[NOT] BETWEEN....AND....具体用法:SELECT 6 BETWEEN 0 AND 29,返回的是true也就是1。也可以是字符串。
    ELECT 15 BETWEEND 1 AND 20 ==> 1 /*15 在1到20之间 ,返回值是1
    SELECT 15 NOT BETWEEND 1 AND 20 ==>0 /* 15在1到20之间,条件不成立 返回值是0
    SELECT 10 IN(5,10,15) ==> 1 /*值是否在给定的数值中,如果在返回1,不在返回0 条件成立为1不成立为0
    SELECT 10 NOT(5,10,15) ==> 0 /*数值是否不在给定的数值中,如果不在返回1,在返回0 条件成立为1不成立为0
    SELECT NULL IS NULL ==>1 /* 空是空 条件成立返回1,不成立返回0
    SELECT '' IS NULL ==> 0 /* 除了NULL空其它都是非空 返回都是1
    SELECT * FROM ABC WHERE ID IS NOT NULL;
    日期时间函数:
    NOW():显示当前日期和时间:
    SELECT NOW();
    CURDATE():显示当前日期:
    SELECT CURDATE();
    CURTIME():显示当前时间:
    SELECT CURTIME();
    DATE_ADD():指定日期加上一段日期:
    SELECT DATE_ADD('2016-6-6',INTERVAL 1 YEAR);//后面的单位可以是week,year,month等
    DATEDIFF():计算两个日期相差的时间:
    SELECT DATEDIFF('2016-6-6','2015-6-6');
    DATE_FORMAT():将日期以指定格式显示:
    SELECT DATE_FORMAT('2016-6-6','%m/%d/%Y');
    函数信息
    (1)CONNECTION_ID(); // 连接ID
    mysql> SELECT CONNECTION_ID();
    (2)SELECT DATABASE(); // 当前数据库
    (3)LAST_INSERT_ID(); // 最后句插入记录的 ID 号,如果是一次insert中插入的是多条记录,得到的是多条中的第一条(而不是最后一条!)
    (4)VERSION(); // 版本的信息
    (5)USER(); // 当前用户
    SELECT USER();
    聚合函数
    聚合函数只有一个返回值
    AVG() - 平均值
    SELECT ROUND(AVG(goods_price),2) AS avg_price FROM tdb_goods;
    COUNT() - 计数
    SELECT COUNT(goods_id) as counts FROM tdb_goods;
    MAX() - 最大值
    SELECT MAX(goods_price) as counts FROM tdb_goods;
    MIN() - 最小值
    SUM() - 求和
    SELECT SUM(goods_price) as counts FROM tdb_goods;
    加密函数
    1.MD5():信息摘要算法,为以后的Web页面做准备,尽量使用MD5()
    举例 SELECT MD5('admin');
    2.PASSWORD():密码算法,通过PASSWORD()修改(重要用于MYSQL数据库)当前用户和其他用户的密码,修改客户端自己的密码
    举例 SET PASSWORD=PASSWORD(‘dimitar’); 把密码修改成dimitar。
    总结
    MySQL内置函数分类:
    一、字符函数
    (1)CONCAT()//字符连接
    (2)CONCAT_WS()//使用指定的分隔符进行字符连接
    (3)FORMAT()//数字格式化
    (4)LOWER()//转化小写
    (5)UPPER()//转换大写
    (6)LEFT()//获取左侧字符
    (7)RIGHT()//获取右侧字符
    (8)LENGTH()//取得字符串长度
    (9)LTRIM(),RTRIM(),TRIM()//删除前导、后续空格或者指定字符
    (10)REPLACE()//替换
    (11)SUBSTRING()//字符串截取
    (12)[NOT] LIKE//模式匹配——百分号%代表任意个字符;下划线_代表任意一个字符

1、前置条件

二、数值函数
(1)CEIL()//进一取整
(2)FLOOR()//舍一取整
(3)DIV//整数除法
(4)MOD//取余数,与%一样
(5)POWER()//幂运算
(6)ROUND()//四舍五入
(7)TRUNCATE()//截断

MySQL数据库中存在表user_info,其结构和数据如下:

三、比较函数
(1)[NOT] BETWEEN...AND... //[不]范围之内
(2)NOT IN() //[不]在列出值范围内
(3)IS [NOT] NULL //[不]为空

mysql desc user_info; ----------- ---------- ------ ----- --------- ------- | Field | Type | Null | Key | Default | Extra | ----------- ---------- ------ ----- --------- ------- | id | int(10) | NO | PRI | NULL | || name | char(20) | NO | | NULL | || passwd | char(40) | NO | | NULL | || email | char(20) | NO | | NULL | || phone | char(20) | NO | | NULL | || role | char(10) | NO | | NULL | || sex | char(10) | NO | | NULL | || status | int(10) | NO | | NULL | || createAt | datetime | NO | | NULL | || exprAt | datetime | NO | | NULL | || validDays | int(10) | NO | | NULL | || delAt | datetime | YES | | NULL | | ----------- ---------- ------ ----- --------- ------- 12 rows in set (0.10 sec)mysql select * from user_info; ---- -------------- ---------- ------------ ------------- -------- ------ -------- --------------------- --------------------- ----------- ------- | id | name | passwd | email | phone | role | sex | status | createAt | exprAt | validDays | delAt | ---- -------------- ---------- ------------ ------------- -------- ------ -------- --------------------- --------------------- ----------- ------- | 1 | StephenWang7 | py123456 | 123@qq.com | 15103887470 | admin | male | 200 | 2019-04-12 20:11:30 | 2019-04-19 20:11:30 | 30 | NULL || 2 | StephenWang8 | 123456 | 123@qq.com | 15103887470 | viewer | male | 200 | 2019-04-12 20:11:30 | 2019-04-19 20:11:30 | 30 | NULL | ---- -------------- ---------- ------------ ------------- -------- ------ -------- --------------------- --------------------- ----------- ------- 2 rows in set (0.00 sec)

四、日期时间函数
(1)SELECT NOW(); // 当前日期时间
(2)SELECT CURDATE(); // 当前日期
(3)SELECT CURTIME(); // 当前时间
(4)SELECT DATE_ADD();//日期的变化
(5)DATEDIFF(); // 日期的差值
(6)DATE_FORMAT(); // 日期格式化

2、自定义函数

五、信息函数
(1)SELECT CONNECTION_ID() //连接ID
(2)SELECT DATABASE() //当前数据库
(3)SELECT LAST_INSERT_ID() //最后插入记录的ID
(4)SELECT USER() //当前用户
(5)SELECT VERSION() //版本信息

函数:可以完成特定功能的一段SQL集合。MySQL支持自定义函数来完成特定的业务功能。

六、聚合函数
(1)AVG()//平均值
(2)COUNT()//计数
(3)MAX()//最大值
(4)MIN()//最小值
(5)SUM()//求和

创建自定义函数的语法如下:

七、加密函数
(1)MD5();//信息摘要算法
(2)PASSWORD();//加密算法,主要用途修改当前用户密码

create function 函数名称 returns 类型return 函数主体


调用UDF的语法如下:

自定义函数
自定义函数

select 函数名称 ([参数])

1.自定义函数
用户自定义函数(user-defined function, UDF)是一种对MySQL扩展的途径, 其用法与内置函数相同。
自定义函数的两个必要条件:
a.参数
b.返回值
函数可以返回任意类型的值,同样可以接受这些类型的参数;
函数的参数与返回值之间,没有必然的联系。
2.创建自定义函数
CREATE FUNCTION function_name
RETURNS
{STRING|INTEGER|REAL|DECIMAL}
routine_body - 函数体
3.关于函数体
(1)函数体由合法的SQL语法构成;
(2)函数体可以是简单的SELECT或INSERT语句;
(3)函数体如果为复合结构则使用BEGIN...END语句;
(4)复合结构可以包括声明,循环,控制结构。
删除函数:
DROP FUNCTION [IF EXISTS] function_name;
必要条件:返回值(必须),参数(非必须) 函数可以返回任意类型的值,同样可以接收这些类型的参数,参数与返回值没有必然的内在联系
MySQL中参数的数量不能超过1024个
创建自定义函数:
CREATE FUNCTION function_name(参数) RETURNS 返回值类型
{STRING|INTEGER|REAL|DECIMAL}
routine_body
RETURN 返回值
创建不带参数的自定义
(1)用date_format()函数写出想要的日期表达方式
SELECT DATE_FORMAT(NOW(),'%Y年 %m月 %d日 %H点:%i分:%s秒');
(2)用自定义函数写出想要的日期表达方式
CREATE FUNCTION f1() RETURNS VARCHAR(30)//创建一个f1()函数
RETURN DATE_FORMAT(NOW(),'%Y年 %m月 %d日 %H点:%i分:%s秒');//返回值
SELECT f1();//调用自定义函数f1();
创建日期时间自定义函数:
create function f1() returns varchar(30)
return date_format(now(),'%Y年%m月%d日 %H点:%i分:%s秒');
select f1();
DATE_FORMAT()函数在格式化日期格式时,应该是百分号在代表年、月、日的字母前面,字母的大小写不同,所表示格式也有所区别:
select date_format(now(),'%Y-%m-%d'),其结果是:
2015-07-04
因此,各字母所表示的含义为:
Y:2015
y:15
M:july
m:07
D:4th
d:04
创建带参数的自定义函数
创建带参数的自定义函数:
CREATE FUNCTION f2(num1 SMALLINT UNSIGNED,num2 SMALLINT UNSIGNED)
RETURNS FLOAT(10,2) UNSIGNED
RETURN(num1 num2)/2;
删除函数:
DROP FUNCTION fun_name;

创建无参的UDF

创建具有复合结构函数体的自定义函数
修改分隔符:DELEMITER 分隔符
Eg:DELIMITER // /* 将命令分隔符”;”改为”//“ */
当函数体内需要执行的是多条语句时,要使用BEGIN...END语句
且当编写函数体内容的时候,需要使用 DELIMITER 关键字将分隔符先修改为别的,否则编写语句的时候写到 ';' 的时候会直接执行,导致函数编写失败
删除函数:DROP FUNCTION [IF EXISTS] function_name
Eg:
DELIMITER //
CREATE FUNCTION ADD_USER(username VARCHAR(20))
RETURNS INT UNSIGNED
BEGIN
INSERT user(username) VALUES(username);
RETURN LAST_INSERT_ID();
END
// /* 此处的”//“为告诉系统函数定义结束 */
总结
函数创建
create function()
必须有:1.参数 2.返回值(只能有一个)
【自定义函数】简称UDF;是对MySQL扩展的一种途径

示例1:查询user_info表中有多少条记录

语法: CREATE FUNCTION ....

| test | CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`test` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
创建一个函数:
没有参数的函数:
CREATE FUNCTION f1()
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;
说明 f1、f2是函数名 num1、num2是形参
RETURNS (returns) 后面跟的是返回值的数据类型
RETURN 后面跟的是函数体
创建一个执行多条sql语句的函数 f3(),并放回主键
如果有多条sql语句,必须放在 BEGIN 与END 之间
修改 结束符号成 ## 原本是; 当然你也可以改回来
1、DELIMITER ##
创建一个函数(功能是传递一个参数,参数是表 test 的test字段值,返回插入数据的主键)
2、CREATE FUNCTION f3(test varchar(20))
RETURNS INT UNSIGNED
BEGIN
INSERT `test`(`test`)values(test);
RETURN LAST_INSERT_ID();
END
##
修改结束符变回 ; (delimiter)
3、DELIMITER ;
删除一个函数:
DROP FUNCTION IF EXISTS `f1`;
调用一个函数:
SELECT f1();
SELECT f2(23,2);


MySQL存储过程

存储过程简介
输入SQL命令,MySQL引擎对命令进行分析,查看输入的语法是否正确,如果正确,再进行编译,编译成MySQL引擎可识别的命令,最后再进行执行,并将执行结果返回给客户端。
如果省略了语法分析和编译的阶段,则效率可提高。
存储过程:是SQL语句和控制语句的预编译集合,以一个名称存储并作为一个单元处理(类似函数)。存储过程存储在数据库内,可以由应用程序调用执行,而且允许用户申明变量以及进行流程控制。存储过程可以接受输入类型的参数和输出类型的参数,并且可以存在多个返回值。只在第一次调用时进行语法分析和编译,以后的调用直接调用编译的结果,效率大大提高。
优点:
1、增强SQL语句的功能和灵活性:可以通过控制语句对流程进行控制和判断
2、实现较快的执行速度:客户端第一次调用存储过程时,MySQL引擎会对其进行语法分析、编译等操作,然后将编译结果存储到内存中,所以第一次和之前的效率一样,然而以后会直接调用内存中的编译结果,效率提高
3、减少网络流量:例如删除一个记录,我们原本要输入DELETE FROM xx WHERE ...; 要传输的字符较多,如果写成存储过程,就只要调用存储过程的名字和相应参数就行,传输的字符数量较少,所以减少了网络流量。
存储过程语法结构分析
1、存储过程语法结构分析
CREATE
[DEFINER = {user|CURRENT_USER}]//定义时的用户,若是不写就默认为当前用户
PROCEDURE sp_name ([proc_parameter[,...]]) //可以带0到多个参数 sp_name存储过程的名字
[characteristic ...] routine_body
其中参数
proc_parameter:
[IN|OUT|INOUT] param_name type
IN, 表示该参数的值必须在调用存储过程时指定
OUT, 表示该参数的值可以被存储过程改变,并且可以返回
INOUT, 表示该参数的值调用时指定,并且可以被改变和返回
2.特性
COMMENT 'string'
{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA|SQL SECURITY{DEFINER|INVOKER}
COMMENT:注释
CONTAINS SQL:包含SQL语句, 但不包含读或写数据的语句
NO SQL:不包含SQL语句
READS SQL DATA:包含读数据的语句
MODIFIES SQL DATA:包含写数据的语句
SQL SECURITY {DEFINER|INVOKER}指明谁有权限来执行
3.过程体
(1)过程体由合法的SQL语句构成;
(2)过程体可以是任意SQL语句;对表格进行增删,连接,但是不能创建数据表<br>
(3)过程体如果为复合结构则使用BEGIN...END语句
(4)复合结构可以使用条件、循环等控制语句
创建不带参数的存储过程
1.创建没有参数的存储过程
CREATE PROCEDURE sp1() SELECT VERSION();
2.调用存储过程
CALL sp_name([parameter[,...]]) - 带参数的存储过程的调用
CALL sp_name[()] - 不带参数的存储过程调用
CALL sp1;
CALL sp1();
创建带有INT类型参数的存储过程
1.创建带有INT类型参数的存储过程
DESC users;
DELIMITER //
CREATE PROCEDURE removeUserByID(IN id INT UNSIGNED)
BEGIN
DELETE FROM users WHERE id = id;
END
//
DELIMITER ;
CALL removeUserById(3); //参数名称最好不要和表中的字段相同
SELECT * FROM users; //全删除了
DELIMITER //
CREATE PROCEDURE removeUserById(IN p_id INT UNSIGNED)
BEGIN DELETE FROM users WHERE id = p_id;
END
//
DELIMITER ;
SELECT * FROM users;
CALL removeUserById(22);
SELECT * FROM users WHERE id = 22;
2.修改存储过程
ALTER PROCEDURE sp_name [characteristic]
COMMENT 'string'
|{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}
|SQL SECURITY{DEFINER|INVOKER}
3.删除存储过程
DROP PROCEDURE [IF EXISTS] sp_name;
DROP removeUserById;
创建带有IN OUT类型参数的存储过程
1.用户变量:以"@"开始,形式为"@变量名"
用户变量跟mysql客户端是绑定的,设置的变量,只对当前用户使用的客户端生效
2.全局变量:定义时,以如下两种形式出现,set GLOBAL 变量名 或者 set @@global.变量名
对所有客户端生效。只有具有super权限才可以设置全局变量
3.会话变量:只对连接的客户端有效。
4.局部变量:作用范围在begin到end语句块之间。在该语句块里设置的变量
declare语句专门用于定义局部变量。set语句是设置不同类型的变量,包括会话变量和全局变量
DELIMITER //
CREATE PROCEDURE removerUserAndReturnUserName(IN showID INT UNSIGNED,OUT showName INT UNSIGNED)
BEGIN
DELETE FROM user WHERE id = showID;
SELECT count(ID) FROM user INTO showName;
END
//
SELECT count(ID) FROM user INTO showName; /** 该语句中的 INTO 含义就是将 SELECT 语句结果的表达式返回到 showName 变量中 */
CALL removerUserAndReturnUserName(10,@nums); /** @nums 所代表的就是用户变量,可用 SELECT @nums 输出 */
用 DECLARE 声明的变量是局部变量,局部变量只能存在于 BEGIN...END 之间,且声明时必须置于 BEGIN...END 的第一行
而通过 SELECT...INTO.../SET @id = 07 这种方法设置的变量我们称之为用户变量,只能存在于当前用户所使用的客户端有效。

CALL rmUserAndRtUserNums(27, @nums);
SELECT @nums; //@nums - 就是用户变量
DECLARE声明的变量都是在BEGIN与END之间,是局部变量
SET @i = 7; //通过@或SET设置的变量称为用户变量
out 类型参数是返回数据;
create procedure pro_name(IN id smallint unsigned OUT nums int unsigned)
begin
delete from tbl_t1 where 表字段= id;
select count(表字段) from tbl_t1 into(将select 表达式count()结果通过into关键字赋给out参数) nums;
end
call remove_user_return_nums(6,@自定义变量); #这里变量范围从begin到end结束
创建带有多个OUT类型参数的存储过程
创建带有多个OUT类型参数的存储过程:<br>
1.明确什么是OUT类型的参数,有什么作用?<br>
答:OUT类型参数,表示在调用存储过程时,该参数的值可以被存储过程改变,并且返回一个值。通常是这种类型时,调用时写入的参数都是变量用“@”符号开头的变量,在BEGIN和END之中的变量是局部变量,在调用存储过程时写入的变量是“用户变量”,比如有一个存储过程的名称叫做“addUser()”,调用时addUser(@sum),sum就是用户变量。
2.ROW_COUNT()函数就相当于PHP的MySQL函数库中的mysql_affected_rows()这个函数,两者的作用都是相同的,都是计算当插入记录和修改记录、以及添加和删除记录时的个数总和的。
3.例子:
SELECT ROW_COUNT() INTO delete_User ;是指调用ROW_COUNT()这个函数,并且把返回值存储在delete_User这个局部变量中。
SELECT COUNT(id) FROM users INTO remain_User ;是指在删除用户后,调用COUNT()函数对剩下的用户数做一个统计,在哪张表中要说明清楚,然后把返回值存储在remain_User这个局部变量中。
【因为delete_User和remain_User是OUT类型的参数,所以会返回一个值给调用时的用户变量,用"SELECT @a,@b"可以知道返回的值是多少了】
ROW_COUNT()函数相当于PHP的MySQL函数库中的mysql_affected_rows()这个函数,都是计算被影响的记录数(行数)的总和。
例子:
INSERT test(username) VALUES('A'),('B,'),('C');//写入三条记录
SELECT ROW_COUNT();==>3 //返回被修改的记录总数
DELIMITER //
CREATE PROCEDURE rmUserByAgeAndRtInfos(IN p_age SMALLINT UNSIGNED, OUT delNums SMALLINT UNSIGNED, OUT leftNums SMALLINT UNSIGNED)
BEGIN
DELETE FROM users WHERE age = p_age; //注意变量不同
SELECT ROW_COUNT() INTO delNums;//此处的SELECT... INTO…只是在赋值
SELECT COUNT(id) FROM users INTO leftNums;//同上
END
//
DELIMITER ;
CALL rmUserByAgeAndRtInfos(20, @a, @b);
SELECT @a, @b;
局部变量与用户函数:

  1. 类型声明
    SELECT…INTO…/SET 不需要声明类型,declare必须指定类型
    SET 变量名 = 表达式值 [,variable_name = expression ...]
    SELECT 表达式值 INTO 变量;
    DECLARE variable_name [,variable_name...] datatype [DEFAULT value];
    DECLARE为MySQL的数据类型,如:int, float, date, varchar(length)
    例如:DECLARE l_int int unsigned default 40;
  2. 位置
    SELECT…INTO…/SET 位置可以任意, DECLARE 必须在复合语句的开头,在任何其它语句之前
    3.作用范围
    DECLARE 定义的变量的作用范围是BEGIN … END块内,只能在块中使用,
    SET 定义的变量用户变量,只能存在于当前用户所使用的客户端有效。
    存储过程与自定义函数的区别
    存储过程与自定义函数的区别:
    1、存储过程实现的功能相对复杂,函数针对性较强
    2、存储过程可以返回多个值,函数只能有一个返回值
    3、存储过程一般独立执行,函数可以作为 sql 语句的组成部分来出现
    另外,存储过程也比通过API接口调用程序要快。
    修改存储过程 如图
    存储过程只能修改一些特性,不能修改过程体。如果要修改过程体 只能先删除存储过程
    //修改存储过程
    alter procedure sp_name [characteristic ...]
    comment 'string'
    | {contains sql | no sql | reads sql data | modifies sql data} | sql security {definer | invoker}
    存储过程只能修改简单的特性,并不能修改过程体
    //删除存储过程
    DROP PROCEDURE [IF EXISTS] sp_name;
    1、存储过程:是SQL语句和控制语句的预编译集合,以一个名字存储并作为一个单元来处理。
    2、参数:输入类型 输出类型 输入&&是输出
    3、创建:CREATE...PROCEDURE...
    4、注意事项:
    (1)创建存储过程或自定义函数时需要通过delimiter语句修改定界符。
    (2)如果函数体或过程题有多个语句,需要包含在BEGIN...END语句块中。
    (3)存储过后才能通过call来调用。

MySQL存储引擎
存储引擎
(1)MySQL可以将数据以不同的技术存储在文件(内存)中,这种技术就成为存储引擎。
每种存数引擎使用不同的存储机制、索引技巧、锁定水平,最终提供广泛且不同的功能。
(2)使用不同的存储引擎也可以说不同类型的表
(3)MySQL支持的存储引擎

  1. MyISAM
  2. InnoDB
  3. Memory
  4. CSV
  5. Archive

并发处理
1.存储引擎:MyISAM,InnoDB,Memory,CSV,Archive<br>
2.并发控制:<br>
当多个连接对记录进行修改时保证数据的一致性和完整性。系统使用锁系统来解决这个并发控制,这种锁分为:
1).共享锁(读锁)---在同一时间内,多个用户可以读取同一个资源,读取过程中数据不会发生任何变化。
2).排他锁(写锁)---在任何时候只能有一个用户写入资源,当进行写锁时会阻塞其他的读锁或者写锁操作。
3.锁的力度(也叫锁的颗粒)
锁颗粒(锁定时的单位)
---表锁,是一种开销最小的锁策略。得到数据表的写锁
---行锁,是一种开销最大的锁策略。并行性最大
表锁的开销最小,因为使用锁的个数最小,行锁的开销最大,因为可能使用锁的个数比较多。
事务处理
事务处理:整个过程每一个单元全部完成才算事务处理成功,某一个单元失败事务就会回滚.
主要作用:保证数据库的完整性
事务的特性:
原子性、一致性、隔离性、持久性
简称:A(Atomic)C(Consistency)I(Isolation)D(Durable)

外键与索引
外键:是保证数据一致性的一个策略
索引:对数据表中的一列或多列进行排序的一种结构,可以快速访问数据表的特定信息。如目录的作用,可快速定位
普通索引、唯一索引、全文索引
各存储引擎之间的区别
存储限制的不同、事务的支持、索引的支持、锁粒度的支持、数据压缩的支持、外键的支持

各种存储引擎的特点
Memory的存储限制是由内存的大小来决定。
索引:普通索引、唯一索引、全文索引、btree索引、hash索引……
CSV存储引擎不支持索引。
BlackHole:黑洞引擎,写入的数据都会消失,一般用做数据复制的中继。
使用最广泛的两种存储引擎:MyISAM/InnoDB
MyISAM:适用于事务的处理不多的情况。
InnoDB:适用于事务处理比较多,需要有外键支持的情况。
设置存储引擎
存储引擎:数据的系统的心脏,实现数据存储和查询的功能,对应的特点为,存储限制、事务安全、是否支持索引、锁的粒度大小、是否支持数据压缩、是否支持外键等。每个技术特点解决的问题是不一样的,不过本质就是为了实现数据存储和查询,是实现的技术不一样,特点不一样,所以我们应该根据需要选择最为合适的。
1:存储限制——数据库能存多少数据
2:事务安全——能否保证数据完整性
3:是否支持索引——能够实现快速查询
4:锁的粒度大小——用于并发控制,实现数据一致性和完整性
5:是否支持数据压缩——用于数据压缩,能够存储更多的数据或者改变数据存储结构
6:是否支持外键——能够保证数据的一致性。

(1)通过修改MySQL配置文件实现

  • default-storage-engine = engine
    (2)通过创建数据表命令来实现
  • CREATE TABLE tbl_name(
    ...
    ...
    ) ENGINE = engine;
    (3)通过修改数据表命令实现
  • ALTER TABLE tbl_name ENGINE [=] engine_name;

 

PS:MySQL图形化管理工具

篮球世界杯冠军竞猜 1

 

#定义函数mysql create function user_info_count() - returns int(10) - return - (select count(*) from user_info);

调用函数user_info_count()

mysql select user_info_count(); ------------------- | user_info_count() | ------------------- | 2 | ------------------- 1 row in set (0.00 sec)

创建有参UDF

示例2:根据id查询用户name。

#定义函数mysql create function queryNameById(uid int(10)) - returns char(20) - return - (select name from user_info where id=uid);Query OK, 0 rows affected (0.01 sec)

调用函数,查询id为1的用户名称。

mysql select queryNameById(1); ------------------ | queryNameById(1) | ------------------ | StephenWang7 | ------------------ 1 row in set (0.00 sec)

查看UDF

查询系统中所有的UDF

show function status;

查询指定的UDF

#show create function 函数名称;mysql show function queryNameById;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'queryNameById' at line 1mysql show function queryNameById();ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'queryNameById()' at line 1mysql show create function queryNameById();ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '()' at line 1mysql show create function queryNameById; --------------- ------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------- ---------------------- -------------------- | Function | sql_mode | Create Function | character_set_client | collation_connection | Database Collation | --------------- ------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------- ---------------------- -------------------- | queryNameById | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` FUNCTION `queryNameById`(uid int(10)) RETURNS char(20) CHARSET latin1return (select name from user_info where id=uid) | utf8 | utf8_general_ci | latin1_swedish_ci | --------------- ------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------- ---------------------- -------------------- 1 row in set (0.00 sec

修改UDF

如果想要修改函数的内容,先删除后再重新创建。

删除UDF

删除UDF语法如下:

drop function 函数名称;

示例3:删除函数queryNameId后再次调用并观察现象。

本文由美洲杯冠军竞猜发布于计算机教程,转载请注明出处:MySQL自定义函数和存储过程示例详解篮球世界杯冠

关键词: