MYSQL基础知识(四)__数据库编程

MYSQL编程

第一章 存储过程

编程就是程序员按照需求或功能，预先写好代码，最终按照代码自动执行，而mysql编程是将编程语言和sql语句结合，自动执行很多数据库操作，完成复杂的功能。存储过程是mysql编程的容器，所有的代码必须写在存储过程中进行保存，当需要执行这些代码时，调用存储过程的名字，实现相应的功能。

1.模板代码：
    /*
    delimiter //
    create procedure 存储过程名称(参数1，参数2，……参数n)
    begin
    程序代码；
    end;
    //
    delimiter ;
    */
注意：
delimiter 就是分隔符的意思，在存储过程中，每一行代码结束后都会有一个分号
如果系统仍旧使用分号作为分隔，会将每一行看作是一个语句，直接执行
delimiter // 表示告诉系统不要用分号作为分隔符，而是使用//，其他符号也可以，如$$
delimiter ; 表示告诉系统重新将分号作为分隔符，delimiter后面必须有空格，不能换行
存储过程可以有参数也可以没有，具体根据需要。即使没有参数括号必须有
存储过程具体要实现什么功能，相关的语句和代码写在begin和end之间

2.打印hello world
-- mysql中要打印输出一个结果使用：select ……;
DELIMITER //
CREATE PROCEDURE hello()
BEGIN
SELECT 'hello world';
END;
//
DELIMITER ;

-- 执行过后只是将存储过程创建并保存，并不会执行其中的语句
-- 如果要调用存储过程，使用 call 存储过程名称;
CALL hello();

/*
存储过程中根据需要可以定义参数
参数是临时存储和传递值的中间量，可以在存储过程外赋值，然后将值传递到存储过程内
参与运算，得到相应的结果；也可以在存储过程内获取运算的结果，传递到存储过程外

定义参数的方式：参数类型 参数名称 数据类型
in类型表示将数据从外部传递到内部的参数
out类型表示将结果从内部传递到外部的参数
in out 上述两个功能都具备的参数
*/

-- 编写一个存储过程，要求输入一个员工编号，查询出该员工的工资
DELIMITER //
CREATE PROCEDURE pro_sal(IN a INT)
BEGIN
SELECT sal FROM emp
WHERE empno = a;
END;
//
DELIMITER ;

-- 调用过程，有参数的过程必须给参数赋值
CALL pro_sal(7698);

-- 编写一个存储过程，要求能够实现乘法运算
/*
要给参数或者变量赋值，有2种方法
1.手动赋值
set 参数/变量=value;

2.将查询结果赋值给参数或变量
select ... into 参数/变量 from ……;
*/
DELIMITER //
CREATE PROCEDURE pro_time(IN a INT,IN b INT,OUT c INT)
BEGIN
SET c=a*b;
END;
//
DELIMITER ;

-- out类型的参数必须使用变量进行替代，变量会获取到out的值
-- 在sql语句中，变量直接使用@变量名
CALL pro_time(12,@result,@result)
-- 变量获取到参数的值之后，可以进行打印查看数据
SELECT @result;

-- 输入两个员工姓名，计算他们的工资之差，使用out类型的参数获取
/*
存储过程中可以声明变量来临时存储和传递值，但是只能在内部使用
不像参数可以从内部或外部双向传递值
变量声明：declare 变量名称 数据类型 [default 默认值];
必须先声明变量才可以使用
*/
DROP PROCEDURE IF EXISTS pro_minus;
DELIMITER //
CREATE  PROCEDURE pro_minus(IN a VARCHAR(20),IN b VARCHAR(20),OUT c FLOAT(7,2))
BEGIN
DECLARE asal FLOAT(7,2) DEFAULT 0;
DECLARE bsal FLOAT(7,2) DEFAULT 0;
SELECT sal INTO asal FROM emp
WHERE ename = a;
SELECT sal INTO bsal FROM emp
WHERE ename = b;
SET c=asal-bsal;
END;
//
DELIMITER ;

CALL pro_minus('KING','JACK',@sal);

SELECT @sal;

3.【练习】
①.编写一个存储过程，要求输入一个部门编号，
能够将该部门的员工人数进行统计，并打印出来。
DELIMITER //
CREATE PROCEDURE pro_dept(IN a INT)
BEGIN
SELECT COUNT(ename) FROM emp
WHERE deptno = a;
END;
//
DELIMITER ;

CALL pro_dept(40);

②.编写一个存储过程，要求输入一个员工的id号，
过程能把该员工的所属部门名称和他的直接领导manager的姓名打印出来。
DELIMITER //
CREATE PROCEDURE dname_mgr(IN a INT,OUT b VARCHAR(10),OUT c VARCHAR(10))
BEGIN
SELECT dname INTO b FROM dept
WHERE deptno =
(SELECT deptno FROM emp WHERE empno = a);
SELECT ename INTO c FROM emp
WHERE empno =
(SELECT mgr FROM emp WHERE empno = a);
END;
//
DELIMITER ;

CALL dname_mgr(7839,@d,@m);

SELECT @d,@m;

第二章 控制结构

通常，没有控制结构的语句是从上到下依次执行；有时候，在完成一些复杂的功能时，可能需要有选择的执行语句或者重复的执行，此时需要使用控制结构不再按照上下顺序执行

1.条件控制
/*
if 单分支

if 条件判断 then
执行语句;
end if;
条件满足则执行语句，条件不满足则直接结束
*/
-- 输入一个成绩，如果小于60分则打印不及格
DELIMITER //
CREATE PROCEDURE score(IN a FLOAT(4,1))
BEGIN
IF a < 60 THEN
	SELECT '不及格';
END IF;
END;
//
DELIMITER ;

CALL score(100);

/*
双分支
if 条件判断 then
执行语句1；
else 执行语句2；
end if;
条件满足则执行语句1，条件不满足则执行语句2
*/
-- 输入一个成绩，如果小于60分打印不及格，其他打印及格
DELIMITER //
CREATE PROCEDURE score2(IN a FLOAT(4,1))
BEGIN
IF a < 60 THEN
	SELECT '不及格';
ELSE
	SELECT '及格';
END IF;
END;
//
DELIMITER ;

CALL score2(100);

/*
多分支

if 条件判断1 then
执行语句1；
elseif 条件判断2 then
执行语句2；
……
elseif 条件判断n then
执行语句n；
else 执行语句n+1；
end if；
如果条件1满足则执行语句1，如果条件2满足则执行语句2，
……
如果条件n满足则执行语句n，如果以上条件都不满足，执行语句n+1
*/
-- 输入一个成绩，如果低于60分打印不及格
-- 60到75之间打印及格，76到90之间打印良好
-- 90分以上打印优秀
DROP PROCEDURE IF EXISTS score3;
DELIMITER //
CREATE PROCEDURE score3(IN a FLOAT(4,1))
BEGIN
IF a BETWEEN 0 AND 100 THEN
	IF a < 60 THEN
		SELECT '不及格';
	ELSEIF a<=75 THEN
		SELECT '及格';
	ELSEIF a<=90 THEN
		SELECT '良好';
	ELSE
		SELECT '优秀';
	END IF;
ELSE
	SELECT '分数输入错误';
END IF;

END;
//
DELIMITER ;

CALL score3(101);

/*
case 变量名
when value1 then 执行语句1；
when value2 then 执行语句2；
……
when valueN then 执行语句n；
else 执行语句n+1；
end case；
*/

【练习】
练习1：
编写一个函数，可以输入一个部门编号，对该部门员工的薪资进行统计。要求
对该部门最高薪水－最低薪水之差进行判断，如果差距大于等于2000,输出标记‘H’
如果差距小于2000，大于等于1000，输出标记‘M’，如果差距小于1000，输出标记‘L’
用IF实现
DELIMITER //
CREATE PROCEDURE dept_sal(IN a INT)
BEGIN
DECLARE b FLOAT(7,2);
SELECT MAX(sal)-MIN(sal) INTO b FROM emp
WHERE deptno = a;
IF b>=2000 THEN
	SELECT 'H';
ELSEIF b>=1000 THEN
	SELECT 'M';
ELSE
	SELECT 'L';
END IF;
END;
//
DELIMITER ;

CALL dept_sal(40);

练习2：
编写一个函数可以输入一个员工编号，并对员工的工资进行判断。
如果员工的工资等级为5，则输出wealthy
工资等级为4，输出rich
工资等级为3，输出average
工资等级为2，输出poor
工资等级为1，输出misery
用CASE实现
DELIMITER //
CREATE PROCEDURE sal_grade(IN a INT)
BEGIN
DECLARE b INT;
SELECT grade INTO b FROM salgrade
WHERE losal <=
(SELECT sal FROM emp
WHERE empno =a)
AND hisal >=
(SELECT sal FROM emp
WHERE empno =a);
CASE b
WHEN 5 THEN SELECT 'wealthy';
WHEN 4 THEN SELECT 'rich';
WHEN 3 THEN SELECT 'average';
WHEN 2 THEN SELECT  'poor';
ELSE SELECT 'misery';
END CASE;
END;
//
DELIMITER ;

CALL sal_grade(7055);

2.循环控制
/*
while 进入循环的条件 do
循环体；
end while;
*/
-- 计算1+2+3……+100的结果
DROP PROCEDURE IF EXISTS add1;
DELIMITER //
CREATE PROCEDURE add1(IN c INT)
BEGIN
DECLARE a INT DEFAULT 1;
DECLARE b INT DEFAULT 0;
WHILE a<=c DO
	SET b=b+a;
	SET a=a+1;
END WHILE;
SELECT b;
END;
//
DELIMITER ;

CALL add1(1000);

/*
repeat
循环体；
until 退出循环的条件
end repeat;

while先判断条件，如果条件不满足可能一次循环都不执行
repeat先执行语句，无论条件如何，至少执行一次循环
*/
DROP PROCEDURE IF EXISTS add2;
DELIMITER //
CREATE PROCEDURE add2(IN c INT)
BEGIN
DECLARE a INT DEFAULT 1;
DECLARE b INT DEFAULT 0;
REPEAT
	SET b=b+a;
	SET a=a+1;
UNTIL a>c
END REPEAT;
SELECT b;
END;
//
DELIMITER ;

CALL add2(1000);

/*
循环名称:loop
循环体；
if 条件判断 then
leave 循环名称；
循环体；
end loop；

离开循环的条件写在中间，任意语句之后需要判断是否离开都可以
*/
-- 输入两个正整数，求出它们的最小公倍数
DROP PROCEDURE IF EXISTS num2;
DELIMITER //
CREATE PROCEDURE num2(IN a INT,IN b INT)
BEGIN
DECLARE c INT DEFAULT a;
IF a>0 AND b>0 THEN
	lp1:LOOP
		IF MOD(c,a)=0 AND MOD(c,b)=0 THEN
			SELECT c;
			LEAVE lp1;
		END IF;
		SET c=c+1;
	END LOOP;
ELSE
	SELECT '请输入正整数';
END IF;
END;
//
DELIMITER ;

CALL num2(8,0);

【练习】
自定义一张表，字段（id(主键)，NAME（不能重复），phno）用循环向这张表中插入1000行记录。
要求：id，NAME不能重复。
用WHILE或REPEAT循环来实现
-- 先创建表
CREATE TABLE user1
(
id INT PRIMARY KEY,
uname VARCHAR(20) UNIQUE,
phno CHAR(11)
);
SELECT * FROM user1;

-- 创建存储过程放入数据
DELIMITER //
CREATE PROCEDURE pro1(IN a INT,IN b VARCHAR(20),IN c CHAR(5),IN d INT)
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i <= d DO
	INSERT INTO user1
	VALUES (a+i,CONCAT(b,i),CONCAT(c,'00',i));
	SET i=i+1;
END WHILE;
END;
//
DELIMITER ;

CALL pro1(5000,'zhangsan',13500,10000);

第三章 函数

-- 之前所用的函数是系统定义好的，直接可以使用
-- 现在要讲的函数是在程序开发过程中，自己需要实现一些特定的功能，自己写函数
-- 存储过程执行一系列操作，函数进行运算返回结果
/*
create function 函数名称(参数1，参数2，……参数n)
returns 数据类型
begin
程序代码；
return 变量;
end;

函数里面的参数默认都是in类型的，所以不用写参数的类型
*/
-- 定义一个函数，要求输入一个日期，返回该日期在平年还是闰年
DROP FUNCTION IF EXISTS year1;
DELIMITER //
CREATE FUNCTION year1(a DATE)
RETURNS CHAR(4)
BEGIN
DECLARE b INT;
SET b=YEAR(a);
IF MOD(b,400)=0 THEN
	RETURN 'RUN';
ELSEIF MOD(b,4)=0 AND MOD(b,100)!=0 THEN
	RETURN 'RUN';
ELSE
	RETURN 'PING';
END IF;
END;
//
DELIMITER ;

-- 在select语句中使用函数
SELECT year1('1900-08-28');

第四章 触发器

/*
触发器是在某种条件下自动触发并且执行语句的对象
触发器通常和dml语句绑定，当执行这些语句时会触发相应条件，自动执行语句

create trigger 触发器名称
触发时间 （before/after）
触发操作 （insert/update/delete）
触发表    （ on 表名）
for each row
begin
执行语句；
end;
*/
-- 如果对emp表进行删除操作，记录每次删除的时间
-- 创建一张时间表，记录删除时间
CREATE TABLE record(del_time DATETIME);
SELECT * FROM record;

DELIMITER //
CREATE TRIGGER emp_del
AFTER
DELETE
ON emp
FOR EACH ROW
BEGIN
INSERT INTO record VALUES (NOW());
END;
//
DELIMITER ;

DELETE FROM emp
WHERE ename = 'jones';

SELECT * FROM emp;

ROLLBACK;

SET autocommit=0;