sql存储

最新推荐文章于 2024-11-08 23:31:31 发布

Alex__G

最新推荐文章于 2024-11-08 23:31:31 发布

阅读量34

点赞数

文章标签： java 开发语言

本文链接：https://blog.csdn.net/Alex__GRC/article/details/133973338

版权

-- =======root=======

-- 创建用户
CREATE USER xxx@'%' IDENTIFIED BY'1234';
-- 赋予权限
GRANT ALL ON warehouse.* TO xxx@'%';

-- =======xxx=======

USE warehouse

/*
存储过程：
plsql----完整的基础语言【大型数据库】
mysql----做了些精简，只能在固定的语法结构中使用，例如：存储过程，函数，触发器
包含编程语言该有的基础语法：
1）关键字
2）标识符
3）注释
4）变量与常量
5）运算符
6）流程语句
7）数组
8）函数
*/

-- 创建一个基本的存储过程

DELIMITER //
CREATE PROCEDURE pro_()
COMMENT''
BEGIN

END//
DELIMITER ;

DELIMITER //
CREATE PROCEDURE pro_allemp()
COMMENT'获取所有员工信息'
BEGIN
-- 定义变量
DECLARE var_num INT DEFAULT 0;
-- 获取数据
SELECT * FROM w_people;
-- 统计个数
SELECT COUNT(*) INTO var_num FROM w_people;
-- 输出
SELECT var_num;

END//
DELIMITER ;

-- 调用
CALL pro_allemp;

-- 显示存储过程
SHOW CREATE PROCEDURE pro_allemp;

-- 删除
DROP PROCEDURE pro_allemp;

/*
存储过程传参问题
in 参数：表示存储过程从调用者传入存储过程的参数
out 参数：表示存储过程执行完返回给调用者的参数
inout 参数：in 和 out 的结合
*/

-- in 传入参数 ----普通传参
DELIMITER //
CREATE PROCEDURE pro_invar(data_num INT,data_name VARCHAR(6))
COMMENT'传入参数'
BEGIN
-- 定义变量
DECLARE var_num INT DEFAULT 0;

-- 显示参数
SELECT data_num,data_name;

-- 处理参数
SET var_num=data_num * 100;

-- 显示数据
SELECT CONCAT_WS('_',data_name,'传入参数计算后：',var_num);

END//
DELIMITER ;

-- 调用
CALL pro_invar(123,'张三');

-- 根据传入不同的数据，显示不同表中的数据
DELIMITER //
CREATE PROCEDURE pro_selettablesdata()
COMMENT'根据传入的数据获取对应的表信息'
BEGIN
-- 选择查询
CASE data_num
WHEN 1 THEN
-- 显示员工表
SELECT * FROM w_people;
WHEN 2 THEN
-- 显示仓库表
SELECT * FROM w_supplier;
WHEN 3 THEN
-- 显示订单
SELECT * FROM w_list;
WHEN 4 THEN
-- 显示信息
SELECT * FROM w_wwarehouse;
ELSE
SELECT '不存在';
END CASE;
END //
DELIMITER ;

-- 调用
CALL pro_selettablesdata(0);

-- 定义变量
SET @dnum = 2;
CALL pro_selettablesdata(@dnumpro_selettablesdata)

-- out 类型变量的演示，传入的数据必须是一个用户变量 out后面的参数名在存储过程中当做一个变量使用
-- 调用存储过程的时候，out参数的位置必须是传入一个用户变量，用于接收存储过程返回数据
-- 编写一个基本的计算器
DELIMITER //
CREATE PROCEDURE pro_jsq(IN fh CHAR,IN a INT,IN b INT,OUT SUM INT)
COMMENT'计算器'
BEGIN
-- 判断
IF fh='+'THEN
SET SUM = a+b;
ELSEIF fh='-'THEN
SET SUM = a-b;
ELSEIF fh='*'THEN
SET SUM = a*b;
ELSE
SET SUM = a/b;
END IF;
END//
DELIMITER ;

-- 定义变量
SET @dnum = 0;

-- 调用
CALL pro_jsq('+',12,43,@dnum);

-- 显示变量
SELECT @dnum;

-- 返回值的演示
DELIMITER //
CREATE PROCEDURE pro_outvar(OUT d_num INT,OUT d_data TIMESTAMP,OUT d_mag VARCHAR(10))
COMMENT''
BEGIN
-- 赋值
SET d_num=1413;
SET d_data=NOW();
SET d_mag='今天真困，每天都很困，真的服了';

END//
DELIMITER ;

-- 接收值
CALL pro_outvar(@dnum,@ddata,@dmag);

-- 显示
SELECT @dnum,@ddate,@dmag;

-- inout参数，必须传入用户变量，既可以带数据进入存储过程内部使用，也可以让他从存储过程内部带数据返回
-- 数据计算传入一个值，返回特定平方
DELIMITER //
CREATE PROCEDURE pro_power(INOUT d_num INT)
BEGIN
-- 显示
SELECT d_num;
-- 计算
SET d_num=POWER(d_num,2);

-- 输出
SELECT '已到访，真™累';

END//
DELIMITER ;

-- 定义参数
SET @dnum=23;

-- 传入值调用
CALL pro_power(@dnum);

-- 显示
SELECT @dnum;

-- 定义存储过程，计算以下
-- 1：传入参数值，判断执行过程，
-- 如果是1，就计算1~200的和，
-- 如果是2，计算-1~-200的和，
-- 如果是3，计算-1+2-3+4-5……+200的和，
-- 如果是其他，不参与运算
-- 2：将结果通过out参数传出

DELIMITER //
CREATE PROCEDURE pro_gentle(IN sr INT,OUT p_num INT)
BEGIN
-- 判断
IF sr='1' THEN
-- 定义变量
DECLARE var_num INT DEFAULT 1;
WHILE (var_num<=200,var_num++) DO
SET p_num=var_num+var_num;
END WHILE;
END IF;
END//
DELIMITER ;

-- 编写存储过程，完成数据精度格式化的操作
-- 1：传入精度规则：
-- 1）：表示四舍五入
-- 2）：表示大于小数的最小整数
-- 其他表示获取小数的最大整数
-- 使用inout 参数进行传参和返回结果

/*
plsql中数组存储数据的概念
特殊用途---游标
游标：用于获取数据库中的数据，进行存储和获取使用

使用步骤：
1）定义游标命名：cur_名字
declare 游标名 cursor for 查询语句

2）开启
open 游标名字

3）获取数据使用
fetch 游标 into 变量

4）关闭
close 游标名字

备注：
1）游标和处理结果的定义需要在其他的变量之后定义
2）结果处理语法
DECLARE CONTINUE HANDLER FOR NOT FOUND SET 变量=值;
当游标遍历完成后，触发此事件执行，改变变量的值
3）fetch 游标，into 变量列表
变量列表的个数和顺序，类型需要和游标中存储的每一行值一直
4）对于游标来说，主要是在存储过程或者函数中想要获得数据库表中的数据来进行业务处理
需要使用查询语句获取存入游标---遍历游标获取数据
*/

-- 获取员工表的编号和工资显示

DELIMITER //
CREATE PROCEDURE pro_empdata()
BEGIN

-- 定义变量接收
DECLARE var_num VARCHAR(6) DEFAULT'';
DECLARE var_sal INT DEFAULT 0;

-- 定义值
DECLARE var_flag INT DEFAULT 0;

-- 定义游标
DECLARE cur_emp CURSOR FOR SELECT w_id,w_area FROM w_wwarehouse;

-- 定义结束标志结束事件处理方式
DECLARE CONTINUE HANDLER FOR NOT FOUND SET var_flag=1;

-- 开启游标
OPEN cur_emp;

-- 循环
REPEAT
-- 获取游标中存储的数据 fetch每次只能取一个值,into后面的变量应该与游标中数据每一行的每一个字段对应
FETCH cur_emp INTO var_num,var_sal;

-- 显示信息
SELECT var_num,var_sal;
UNTIL var_flag=1 END REPEAT;

-- 关闭
CLOSE cur_emp;

END//
DELIMITER ;

-- 调用
CALL pro_empdata();

-- 编写业务完成存储过程实现平均工资和总钱数的统计
DELIMITER //
CREATE PROCEDURE pro_sum_avg(OUT dsum INT,OUT davg INT)
BEGIN
-- 定义变量
DECLARE var_size INT DEFAULT -1;
DECLARE var_sal INT DEFAULT 0;-- 定义工资
DECLARE var_sumsal INT DEFAULT 0;-- 定义总工资

DECLARE var_flag INT DEFAULT 0;

-- 定义游标
DECLARE cur_sal CURSOR FOR SELECT p_wages FROM w_people;

-- 游标遍历结束
DECLARE CONTINUE HANDLER FOR NOT FOUND SET var_flag=1;

-- 开启游标
OPEN cur_sal;

-- 遍历获取数据
WHILE var_flag<>1 DO
-- 取出数据
FETCH cur_sal INTO var_sal;

-- 求和
SET var_sumsal = var_sumsal+var_sal;

-- 统计
SET var_size = var_size+1;
END WHILE;

-- 赋值
SET dsum=var_sums;
SET davg=var_sumsal/var_size;

END//
DELIMITER ;

-- 调用
CALL pro_sum_avg(@dsum,@davg);

SELECT @dsum,@davg;

--- 编写业务完成获取工资的最大值和最小值
DELIMITER //
CREATE PROCEDURE pro_dm(OUT dmax INT,OUT dmin INT)
BEGIN
-- 定义变量
DECLARE var_num INT DEFAULT 0;
DECLARE var_max INT DEFAULT 0;
DECLARE var_min INT DEFAULT 0;
DECLARE var_flag INT DEFAULT 0;

-- 定义游标
DECLARE cur_sal CURSOR FOR SELECT p_wages FROM w_people;

-- 游标遍历结束
DECLARE CONTINUE HANDLER FOR NOT FOUND SET var_flag=1;

-- 开启游标
OPEN cur_sal;

WHILE flag<>1 DO
-- 求最大值

END WHILE;

END//
DELIMITER ;

/*
自定义函数--
MySQL中给定了很多的函数帮助处理业务功能

自定义函数的语法
create function 【if not exists】函数名（参数列表）
returns 返回值类型
begin
函数体；
return 返回值；
end；
解释：函数名：要求标识符定义语法
参数【参数名类型】：只有传入参数
返回值类型：需要返回的数据类型，便于使用者接收
函数体：基本的函数业务，与存储过程begin和end之间的语法一致

调用：与数据库中预定义函数一样
删除函数：
drop function 函数名，
备注：因为函数是传入参数执行业务返回结果显示，所以不能直接使用select打印信息
*/

-- 获取工资中的最大值
DELIMITER //
CREATE FUNCTION fun_maxsal(d_name VARCHAR(6))
RETURNS INT
BEGIN
-- 定义变量
DECLARE var_flag INT DEFAULT 0;
DECLARE var_sal INT DEFAULT 0;
DECLARE var_index INT DEFAULT 0;
DECLARE var_max INT DEFAULT 0;

-- 定义游标获取数据
DECLARE cur_sal CURSOR FOR SELECT p_wages FROM w_people;

-- 游标结束的条件
DECLARE CONTINUE HANDLER FOR NOT FOUND SET var_flag=1;

-- 开启游标
OPEN cur_sal;

-- 循环
myloop:LOOP

-- 获取游标中的数据w_wwarehouse
FETCH cur_sal INTO var_sal;

-- 判断
IF var_flag=1 THEN
-- 结束循环
LEAVE myloop;
END IF;

-- 计数器
SET var_index=var_index+1;

-- 判断
IF var_index=1 THEN
SET var_max = var_sal;
ELSE
-- 判断
IF var_max<var_sal THEN
SET var_max=var_sal;
END IF;
END IF;
END LOOP;

-- 关闭游标
CLOSE cur_sal;

-- 输出
-- select concat_ws('---',d_name,'最大值'，var_max);

-- 返回结果
RETURN var_max;

END //
DELIMITER ;

-- 编写函数完成计算器功能

CREATE FUNCTION jsq(IN fh CHAR,IN a INT,IN b INT,OUT jsum INT)
RETURNS INT
BEGIN
-- 判断
IF fh='+'THEN
SET jsum = a+b;
ELSEIF fh='-'THEN
SET jsum = a-b;
ELSEIF fh='*'THEN
SET jsum = a*b;
ELSE
SET jsum = a/b;
END IF;
RETURN jsum;
END;

/*
触发器的概念
当某个行为发生自动触发执行功能
一些危险行为的补救措施

触发器定义的语法：
create trigger 触发器的名字
before 或 after insert 或 update 或 delete
on 表名
for each row
begin
触发器被触发的时候需要执行的业务语法
end；

调用：自动调用
删除：drop trigger 触发器名字

触发器的两个关键字：old / new，用来存储当前涉及到的事件和数据
insert：new 关键字有效 new表示新插入的数据
update：new 和 old 都有效 new表示更新后的数据，old表示更新前的数据
delete：old 关键字有效用于保存已删除的数据

触发器总结
触发器不是越多越好，触发器是被动的，并且占资源

*/

-- 创建触发器--插入数据时需要将数据拼接显示或者判断是否为空
DELIMITER //
CREATE TRIGGER tri_inserate BEFORE INSERT ON w_people FOR EACH ROW
BEGIN
-- 获取插入的数据进行判断和显示
SELECT CONCAT(new.w_id,new.p_id,new.p_wages)INTO @mywork;

END//
DELIMITER ;

SELECT @mywork;-- 空值
SELECT * FROM w_people;
INSERT INTO w_people(w_id,p_id,p_wages)VALUES('WH5','E6',1300);
SELECT @mywork;-- 把'WH5','E6',1300拼接起来输出

-- 删除数据备份
-- 根据已经存在的表创建一个新的表
CREATE TABLE w_bakemp LIKE w_people;
DESC w_bakemp;
SELECT * FROM w_bakemp;

-- 编写触发器需要在删除表的时候将删除的数据备份到w_bakemp中
DELIMITER //
CREATE TRIGGER tri_deldata BEFORE DELETE ON w_people FOR EACH ROW
BEGIN

-- 存入数据
INSERT INTO w_bakemp VALUES(old.w_id,old.p_id,old.p_wages);

END//
DELIMITER ;

DELETE FROM w_people WHERE w_id='WH5';

SELECT * FROM w_people;
SELECT * FROM w_bakemp;

-- 编写触发器要求，插入w_people表中的数据的时候，备份表也要更新
DELIMITER //
CREATE TRIGGER tri_news AFTER UPDATE ON w_people FOR EACH ROW
BEGIN
-- 修改备份表数据
UPDATE w_bakemp SET w_id=new.w_id,p_wages=new.p_wages WHERE p_id=old.p_id;
INSERT INTO w_bakemp VALUES(new.w_id,new.p_id,new.p_wages);

END//
DELIMITER ;

UPDATE w_people SET p_id=13 WHERE p_id='E3'
SELECT * FROM w_people;
SELECT * FROM w_bakemp;

DROP TRIGGER tri_news;

-- 编写触发器，完成插入数据，如果插入数据的主键已经存在，就不插入，给用户提示‘主键已存在，请重新编写’
DELIMITER //
CREATE TRIGGER tri_ BEFORE INSERT ON w_people FOR EACH ROW
BEGIN
-- 判断
IF

END//
DELIMITER ;

/*
mysql索引
索引：构建一种模式，快速找到想要的数据
例如：书和目录
概念：当下的大数据量级的都是为了提高数据查询效率，数据表中的数据进行处理，提供了引导检索的方案
简介：引导检索数据，不同的数据库引擎有区别，把以某一列或某几列构建索引--构建检索模式
检索模式有两种：
Btree 哈希表

缺点：构建Btree的结构是需要存储空间，构建的时候需要资源，删除不方便，不建议使用

索引的创建，删除
事务概念
数据库的命令备份
*/