MYSQL存储过程基础入门教程
2023-09-11 14:16:34 时间
#创建一个简单的存储过程
DELIMITER $$
CREATE PROCEDURE selectStu()
BEGIN
SELECT * FROM stu WHERE id = 3;
END ;
$$
DELIMITER;
-- 调用存储过程
CALL selectStu();
#存储过程多个 查询
DELIMITER $$
CREATE PROCEDURE selectStuAndTeaAll()
BEGIN
SELECT * FROM stu WHERE id = 8;
SELECT * FROM tea WHERE id = 2;
END ;
$$
DELIMITER;
-- 调用存储过程会出现 两个结果集
CALL selectStuAndTeaAll();
# 存储过程的 变量 使用
DELIMITER $$
CREATE PROCEDURE finalSelectStu()
BEGIN
DECLARE name_stu VARCHAR(20) DEFAULT '';
SET name_stu = '默认值';
SELECT `name` INTO name_stu FROM stu WHERE id = 1;
SELECT name_stu;
END ;
$$
DELIMITER;
-- 调用存储过程 以前一直出错 可能出现字符集问题。
CALL finalSelectStu();
# 存储过程出现多个代码块
DELIMITER $$
CREATE PROCEDURE selectStuAndTeaMinMax()
BEGIN
BEGIN
DECLARE sum_stu INT DEFAULT 0;
DECLARE sum_tea INT DEFAULT 0;
SELECT COUNT(*) INTO sum_stu FROM stu ;
SELECT COUNT(*) INTO sum_tea FROM tea;
SELECT sum_stu,sum_tea;
END;
BEGIN
-- 记得数据类型要一直 我这里出现的错误
DECLARE regStuMax_time TIMESTAMP;
DECLARE regStuMin_time TIMESTAMP;
SELECT MAX(TIMESTAMP) ,MIN(TIMESTAMP) INTO regStuMax_time,regStuMin_time FROM stu;
END ;
END;
$$
DELIMITER;
-- 调用存储过程
CALL selectStuAndTeaMinMax()
# 存储过程传入参数 IN
DELIMITER $$
-- 传参 ( IN stuId INT) in不写默认
CREATE PROCEDURE parameterSelectStu(stuId INT)
BEGIN
DECLARE name_stu VARCHAR(20) DEFAULT '';
SELECT `name` INTO name_stu FROM stu WHERE id = stuId;
SELECT name_stu;
END;
$$
DELIMITER;
-- 调用存储过程
CALL parameterSelectStu(5);
#存储过程传出参数 OUT
DELIMITER $$
CREATE PROCEDURE outStuName(IN stuId INT,OUT stuName VARCHAR(20))
BEGIN
SELECT `name` INTO stuName FROM stu WHERE id = stuId;
SELECT stuName;
END;
$$
DELIMITER;
-- 调用存储过程,注意参数
CALL outStuName(2,@stuName);
# 存储过程 可变参数 INOUT 的使用,即是传入参数又是传出参数
DELIMITER $$
CREATE PROCEDURE inoutStuIdName(INOUT stuId INT ,INOUT stuName VARCHAR(20))
BEGIN
SELECT id,`name` INTO stuId,stuName FROM stu WHERE id = stuId;
SELECT stuId,stuName;
END;
$$
DELIMITER;
-- INOUT 不会调用啊 (还是百度的啊)
SET @stuIds = 8;
SET @stuNames = '';
CALL inoutStuIdName(@stuIds,@stuNames);
# 存储过程的条件语句
DELIMITER $$
CREATE PROCEDURE ifElseStu(IN stuId INT)
BEGIN
DECLARE stuName VARCHAR(20) DEFAULT '';
-- 注意 这里不是 == 是 = 在这里错过
IF(stuId%2=0)
THEN
SELECT `name` INTO stuName FROM stu WHERE id = stuId;
SELECT stuName;
ELSE
SELECT stuId;
-- 注意在这里是 end if 在这里错过
END IF;
END;
$$
DELIMITER;
-- 调用存储过程
CALL ifElseStu(2);
# 案例 如果 = 5 字段age + 10 ;如果id = 9 字段 age - 10 ;其他都+100
-- 修改前 id= 5 age = 33 ,id=9 age= 53
DELIMITER $$
CREATE PROCEDURE topIfThen(IN stuId INT)
BEGIN
DECLARE stuAge INT DEFAULT 0;
SELECT age INTO stuAge FROM stu WHERE id = stuId;
-- 如果age = 55 加10
IF(stuAge = 55)
THEN
UPDATE stu SET age=stuAge + 10 WHERE id = stuId;
-- 如果 age = 33 减 10;
ELSEIF (stuAge = 33)
UPDATE stu SET age=stuAge - 10 WHERE id = stuId;
-- 其他情况加 30
ELSE
UPDATE stu SET age = stuAge + 30 WHERE id = stuId;
END IF ;
END;
$$
DELIMITER;
#WHILE 循环语句
DELIMITER $$
CREATE PROCEDURE whileStu()
BEGIN
DECLARE i INT DEFAULT 0;
WHILE (i < = 10) DO
BEGIN
SELECT i;
SET i = i+1;
INSERT INTO stu (age) VALUES(2);
END;
END WHILE;
END;
$$
DELIMITER;
表
/*
SQLyog Ultimate v11.27 (32 bit)
MySQL - 5.5.56 : Database - test
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;
/*!40101 SET SQL_MODE=''*/;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`test` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `test`;
/*Table structure for table `stu` */
DROP TABLE IF EXISTS `stu`;
CREATE TABLE `stu` (
`id` int(36) NOT NULL AUTO_INCREMENT,
`name` varchar(12) DEFAULT NULL,
`age` int(3) DEFAULT NULL,
`reg` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8;
/*Data for the table `stu` */
insert into `stu`(`id`,`name`,`age`,`reg`) values (1,'荀攸',10,'2017-10-26 15:16:58'),(2,'郭嘉',10,'2017-10-26 16:07:26'),(3,'庞统',10,'2017-10-26 16:07:26'),(4,'刘备',10,'2017-10-26 16:07:26'),(5,'曹操',10,'2017-10-26 16:07:26'),(6,'孙权',10,'2017-10-26 16:07:26'),(7,'陆逊',10,'2017-10-26 16:07:26'),(8,'孙浩',10,'2017-10-26 16:07:26'),(9,'周瑜',10,'2017-10-26 16:07:26'),(10,'张飞',10,'2017-10-26 15:23:38'),(11,'张苞',10,'2017-10-26 16:07:26'),(12,'关羽',10,'2017-10-26 16:07:26'),(13,'赵子龙',10,'2017-10-26 16:07:26');
/*Table structure for table `tea` */
DROP TABLE IF EXISTS `tea`;
CREATE TABLE `tea` (
`id` int(32) NOT NULL AUTO_INCREMENT,
`name` varchar(6) DEFAULT NULL,
`age` int(5) DEFAULT NULL,
`reg` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;
/*Data for the table `tea` */
insert into `tea`(`id`,`name`,`age`,`reg`) values (1,'李斯',23,'2017-07-31 09:05:50'),(2,'蒙恬',85,'2017-09-12 09:05:56'),(3,'嬴政',45,'2017-09-18 09:06:00'),(4,'吕不韦',56,'2017-07-12 09:06:04'),(5,'嫪毐',75,'2017-10-09 09:06:09'),(6,'吴广',63,'2017-10-18 09:06:12'),(7,'项羽',78,'2017-12-08 09:06:15'),(8,'刘邦',22,'2018-01-19 09:06:19'),(9,'萧何',33,'2018-01-19 09:06:23'),(10,'张良',55,'2017-05-16 09:06:27'),(11,'赵高',68,'2017-07-03 09:06:32'),(12,'韩信',67,'2017-10-09 09:06:36'),(13,'吕后',13,'2017-10-02 09:06:39');
/* Procedure structure for procedure `finalSelectStu` */
/*!50003 DROP PROCEDURE IF EXISTS `finalSelectStu` */;
DELIMITER $$
/*!50003 CREATE DEFINER=`root`@`localhost` PROCEDURE `finalSelectStu`()
begin
declare name_stu varchar(20) default '';
set name_stu = '默认值';
select `name` into name_stu from stu where id = 1;
select name_stu;
end */$$
DELIMITER ;
/* Procedure structure for procedure `ifElseStu` */
/*!50003 DROP PROCEDURE IF EXISTS `ifElseStu` */;
DELIMITER $$
/*!50003 CREATE DEFINER=`root`@`localhost` PROCEDURE `ifElseStu`(in stuId int)
begin
declare stuName varchar(20) default '';
if(stuId%2=0)
then
select `name` into stuName from stu where id = stuId;
select stuName;
else
select stuId;
end if;
end */$$
DELIMITER ;
/* Procedure structure for procedure `inoutStuIdName` */
/*!50003 DROP PROCEDURE IF EXISTS `inoutStuIdName` */;
DELIMITER $$
/*!50003 CREATE DEFINER=`root`@`localhost` PROCEDURE `inoutStuIdName`(inout stuId int ,inout stuName varchar(20))
begin
select id,`name` into stuId,stuName from stu where id = stuId;
select stuId,stuName;
end */$$
DELIMITER ;
/* Procedure structure for procedure `outStuName` */
/*!50003 DROP PROCEDURE IF EXISTS `outStuName` */;
DELIMITER $$
/*!50003 CREATE DEFINER=`root`@`localhost` PROCEDURE `outStuName`(in stuId int,out stuName varchar(20))
begin
select `name` into stuName from stu where id = stuId;
select stuName;
end */$$
DELIMITER ;
/* Procedure structure for procedure `parameterSelectStu` */
/*!50003 DROP PROCEDURE IF EXISTS `parameterSelectStu` */;
DELIMITER $$
/*!50003 CREATE DEFINER=`root`@`localhost` PROCEDURE `parameterSelectStu`(stuId int)
begin
declare name_stu varchar(20) default '';
select `name` into name_stu from stu where id = stuId;
select name_stu;
end */$$
DELIMITER ;
/* Procedure structure for procedure `selectStu` */
/*!50003 DROP PROCEDURE IF EXISTS `selectStu` */;
DELIMITER $$
/*!50003 CREATE DEFINER=`root`@`localhost` PROCEDURE `selectStu`()
begin
select * from stu where id = 3;
end */$$
DELIMITER ;
/* Procedure structure for procedure `selectStuAndTeaAll` */
/*!50003 DROP PROCEDURE IF EXISTS `selectStuAndTeaAll` */;
DELIMITER $$
/*!50003 CREATE DEFINER=`root`@`localhost` PROCEDURE `selectStuAndTeaAll`()
begin
select * from stu where id = 8;
select * from tea where id = 2;
end */$$
DELIMITER ;
/* Procedure structure for procedure `selectStuAndTeaMinMax` */
/*!50003 DROP PROCEDURE IF EXISTS `selectStuAndTeaMinMax` */;
DELIMITER $$
/*!50003 CREATE DEFINER=`root`@`localhost` PROCEDURE `selectStuAndTeaMinMax`()
begin
begin
declare sum_stu int default 0;
declare sum_tea int default 0;
select count(*) into sum_stu from stu ;
select count(*) into sum_tea from tea;
select sum_stu,sum_tea;
end;
begin
declare regStuMax_time timestamp;
declare regStuMin_time timestamp;
select max(timestamp) ,min(timestamp) into regStuMax_time,regStuMin_time from stu;
end ;
end */$$
DELIMITER ;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
相关文章
- MySQL存储过程详解 mysql 存储过程
- 让 MySQL 支持 emoji 存储
- MySQL基础篇(05):逻辑架构图解和InnoDB存储引擎详解
- 【MySQL高级】MySql中常用工具及Mysql 日志
- MySQL 自动获取当前时间,且 timestamp 类型与 datetime 类型的区别
- MySQL 8 组件架构和错误日志
- 【MySQL进阶-02】mysql的explain执行计划以及索引优化
- MySQL InnoDB 引擎的持久性与性能
- mysql添加用户和密码
- 《PHP和MySQL Web开发从新手到高手(第5版)》一一第2章 MySQL简介
- MySql存储过程与函数详解
- mysql数据库修改字符编码问题
- mysql点滴_02程序中运行sql语句报字符集问题解决
- mysql中的触发器和事务的操作
- (2.12)Mysql之SQL基础——存储过程条件定义与错误处理
- mysql窗口函数、Mysql分析函数
- (2.6)Mysql之SQL基础——存储引擎的查看与修改
- MySQL多表关联查询效率高点还是多次单表查询效率高,为什么?
- MySQL数据库InnoDB存储引擎中的锁机制--转载
- MySQL 教程(基础篇)第04话:mysqld 和 mysql 命令的区别
- [Mysql] 分页查询