SQL/PLSQL
/* * PL/SQL - 过程化SQL语言（Procedural Language/SQL）对SQL语句的扩展。在普通SQL语句的使用上增加了编程语言的特点，所以PL/SQL把数据操作和查询语句组织在PL/SQL代码的过程性单元中，通过逻辑判断、循环等操作实现复杂的功能或者计算1: 程序端(实现相关逻辑处理) ->  多次数据库ARUD操作2: PL/SQL -> 数据库端(一个结构中 - 自身逻辑处理) 函数	function	用于完成一个特定的计算，具有返回值和参数存储过程	procedure	完成某项完整的业务处理，没有返回值，但可通过传出参数将多个值传个调用环境触发器	trigger	相当于一个事件的监听器，当数据库发生特定的事件后，触发器被触发，完成响应处理编译 - 保存在服务器端 -> 需要时调用*//** 函数1: 预定义函数2: 自定义函数	create function 函数名([参数, ...]) returns type begin ...return value;end;	*/
select upper('John');
select md5(MD5('123456'));-- 查看所有人工资 select id, name, salary + ifnull(bonus, 0) +  ifnull(mobileBonus, 0) +  ifnull(hotbonus, 0) +  ifnull(allbonus, 0) +  ifnull(oilbonus, 0) as salaryfrom employee;-- 函数创建报错 This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its de 错误解决办法set global log_bin_trust_function_creators=TRUE;-- 定义工资求和的函数  - 依据员工编号查询员工所有工资和drop function allSalary;create function allSalary(v_id int) returns int begin declare v_salary int; 			-- default 0;				-- 定义变量  default 0;  - 缺省值-- 变量赋值 set allSum = 10000;-- select .. into 变量   ->  seleclt查询的结果必须是一条select salary + ifnull(bonus, 0) +  ifnull(mobileBonus, 0) +  ifnull(hotbonus, 0) +  ifnull(allbonus, 0) +  ifnull(oilbonus, 0) into v_salary from employee where id = v_id;return v_salary;end;select allSalary(2) ;-- 函数 - 计算员工工资列create function salary(salary int, bonus int, mobileBonus int, hotbonus int, allbonus int, oilbonus int) returns int beginreturn salary + ifnull(bonus, 0) +  ifnull(mobileBonus, 0) +  ifnull(hotbonus, 0) +  ifnull(allbonus, 0) +  ifnull(oilbonus, 0);end;select id, name, salary(salary, bonus, mobileBonus, hotbonus, allbonus, oilbonus) as salary from employee;-- 银行转账(扣、加) drop function transferFun;-- 定义变量 - 不要与列名一致create function transferFun(myCode varchar(20), a_money double, tagetCode varchar(20)) returns int begin declare v_flag int default 0;    					 -- 转账成功declare v_money int default 0;						 -- 余额declare v_targetId int default 0;-- 判断余额是否足够转账select money into v_money from account where id = myCode;if v_money > a_money then 								 -- 可以转账update account set money = money - a_money where id = mycode;			-- 付款账号扣款select id into v_targetId from account where id = tagetCode;if v_targetId = 0 then 				-- 转账账号不存在set v_flag = 2;else update account set money = money + a_money where id = tagetCode;end if;	elseset v_flag = 1;														-- 余额不足end if;-- 判断对方账号是否存在return v_flag;end;start transaction;select transfer(2, 20000, 3);			-- 0-转账成功  1-余额不足  2-转账账号错误commit;rollback;-- --------------------------------------------------------------------------------------------------/* * 存储过程    in-传入数据(默认类型), out-传出数据(数据处理 -> 返回给调用者)， inout*    create procedure pname([参数,..]) *      begin *         ...*			end;*/-- 银行转账(扣、加) drop PROCEDURE transferPro;create procedure transferPro(myCode int, tagetCode int,a_money double, out v_flag int)begin declare v_money int default 0;						 -- 余额变量declare v_targetId int default 0;					 -- 缺省的不存在的账号select money into v_money from account where id = myCode;		-- 判断余额是否足够转账if v_money > a_money then 								 -- 可以转账select id into v_targetId from account where id = tagetCode;		-- 判断转账账号不存在if v_targetId = 0 then 									set v_flag = 2;											-- 转账账号不存在else -- start transaction;update account set money = money - a_money where id = mycode;			-- 付款账号扣款update account set money = money + a_money where id = tagetCode;	  -- 加款-- commit;set v_flag = 3;											-- 转账成功end if;	elseset v_flag = 1;														-- 余额不足end if;end;-- 调用存储过程		call proName(参数, ..., @v_flag)   -> 定义变量接受out的数据start transaction;call transferPro(3, 20000, 2, @flag);	select @flag;commit;rollback;-- 商品分页查询(count、pages, 数据)  -> 某种类中的商品分页数据create procedure itemPages(v_sid int, v_pageIndex int, v_pageNum int, out v_count long, out v_pages int) 				-- mysql数据 - 游标 - 不需要outbegin select count(*) into v_count from item where sid = v_sid;select ceil(v_count / v_pageNum) into v_pages;set v_pageIndex = (v_pageIndex - 1) * v_pageNum;select * from item where sid = v_sid order by id desc limit v_pageIndex, v_pageNum;end;call itemPages(4, 1, 20, @count, @pages);select @count;select @pages;-- 所有员工薪资调整(不同结构调整)  其他+500  3000以上 + 800  	5000以上 + 1000 	8000以下 + 1200  10000以上 + 1500-- 查询的数据有多条 -> 依据每条数据 -> 不同处理 -> 游标(光标)cursor-- open游标-- loop fetch获取一行数据-- close游标drop procedure addAll;create procedure addAll() begin-- 定义变量 接收fetch每行数据(变量数量与查询数量一致)declare v_id int;declare v_name varchar(255);declare v_salary int;declare v_addMoney int;declare done int default false;		-- 游标之前定义boolean变量游标处理 - 循环 declare allEmp cursor for select id, name, salary from employee;		-- 声明游标declare continue handler for not found set done = true;		-- 游标执行时 -> 游标无下一行open allEmp;				-- 打开游标fetch allEmp into v_id, v_name, v_salary;				-- 获取下一行while not done do 					-- 循环处理游标-- select v_id, v_name, v_salary;if v_salary > 10000 then set v_addMoney = 1500;elseif v_salary > 8000 then set v_addMoney = 1200;elseif v_salary > 5000 then set v_addMoney = 1000;elseif v_salary > 3000 then set v_addMoney = 800;else set v_addMoney = 500;end if;update employee set salary = salary + v_addMoney where id = v_id;fetch allEmp into v_id, v_name, v_salary;			-- 继续获取下一行end while;close allEmp;				-- 关闭游标end;call addAll();-- ---------------------------------------------------------------------------------------------------- 触发器	trigger	相当于一个事件的监听器，当数据库发生特定的事件后，触发器被触发，完成响应处理/*  CREATE TRIGGER trigger_name trigger_time 				-  BEFORE或AFTER，以指明触发程序是在激活它的语句之前或之后触发trigger_event				-  INSERT、UPDATE、DELETEON tbl_name FOR EACH ROW trigger_stmt				-  new.列名   old.列名*/create trigger upadteAccount before update on account for each row beginif old.id <= 5 theninsert into accountBak(id, money, adate, aid, updateDate) values(old.id, old.money, old.adate, old.aid, now());end if;end;create trigger deleteAccount before delete on account for each row begininsert into accountBak(id, money, adate, aid, updateDate) values(old.id, old.money, old.adate, old.aid, now());end;start transaction;update account set money = money + 800000000 where id = 3;delete from account where id = 3;commit;rollback;-- mySQL -> 物化视图( - 自定义 表 - 自己维护数据)delete from item where id > 20;select i.id, i.name, i.salePrice, s.`name` as smallKindNamefrom item i, smallKind s where i.sid = s.sid;insert into itemView (select i.id, i.name, i.salePrice, s.`name` as smallKindNamefrom item i, smallKind s where i.sid = s.sid);select * from itemView;create trigger itemTrigger after insert on item for each row begin -- 先删除原有所有数据delete from itemView;-- 再维护数据insert into itemView (select i.id, i.name, i.salePrice, s.`name` as smallKindNamefrom item i, smallKind s where i.sid = s.sid);end;insert -- commit;/* * 数据库优化1: 子查询 2: index索引3: 视图view4: 物化视图5: 存储过程、函数6: 数据库连接池*/