300字范文 > MySQL内置函数存储过程视图JDBC

MySQL内置函数存储过程视图JDBC

时间：2024-01-04 01:14:48

相关推荐

MySQL内置函数存储过程视图JDBC

MySQL存储过程&视图&JDBC-笔记

回顾

能够使用内连接进行多表查询

SELECT * FROM 表1 , 表2 WHERE 条件;

SELECT * FROM 表1 INNER JOIN 表2 ON 表连接条件;

能够使用左外和右外连接进行多表查询

SELECT * FROM 表1 LEFT OUTER JOIN 表2 ON 表连接条件; 满足条件的显示,左边不满足条件的也显示

SELECT * FROM 表1 RIGHT OUTER JOIN 表2 ON 表连接条件; 满足条件的显示,右边不满足条件的也显示

能够使用子查询

一个查询作为另一个查询的一部分

能够理解多表查询的规律

明确查询哪些表明确表连接条件后续查询

能够理解事务的概念

多条SQL语句组成一个功能,这多条SQL语句形成一个事务

开启事务: START TRANSACTION;

提交事务: COMMIT;

回滚事务: ROLLBACK;

理解mysql索引的作用

极大提交查询效率

学习目标

会使用mysql字符串函数会使用mysql日期函数能够独立完成mysql综合练习理解mysql表自关联能够理解JDBC的概念能够使用JDBC实现对单表数据增、删、改、查

准备数据

CREATE DATABASE day18;use day18;-- 部门表CREATE TABLE dept (id INT PRIMARY KEY PRIMARY KEY, -- 部门iddname VARCHAR(50), -- 部门名称loc VARCHAR(50) -- 部门位置);-- 添加4个部门INSERT INTO dept(id,dname,loc) VALUES (10,'教研部','北京'),(20,'学工部','上海'),(30,'销售部','广州'),(40,'财务部','深圳');-- 职务表，职务名称，职务描述CREATE TABLE job (id INT PRIMARY KEY,jname VARCHAR(20),description VARCHAR(50));-- 添加4个职务INSERT INTO job (id, jname, description) VALUES(1, '董事长', '管理整个公司，接单'),(2, '经理', '管理部门员工'),(3, '销售员', '向客人推销产品'),(4, '文员', '使用办公软件');-- 员工表CREATE TABLE emp (id INT PRIMARY KEY, -- 员工idename VARCHAR(50), -- 员工姓名job_id INT, -- 职务idmgr INT , -- 上级领导joindate DATE, -- 入职日期salary DECIMAL(7,2), -- 工资bonus DECIMAL(7,2), -- 奖金dept_id INT, -- 所在部门编号CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id));-- 添加员工INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES (1001,'孙悟空',4,1004,'2000-12-17','8000.00',NULL,20),(1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',30),(1003,'林冲',3,1006,'2001-02-22','12500.00','5000.00',30),(1004,'唐僧',2,1009,'2001-04-02','29750.00',NULL,20),(1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30),(1006,'宋江',2,1009,'2001-05-01','28500.00',NULL,30),(1007,'刘备',2,1009,'2001-09-01','24500.00',NULL,10),(1008,'猪八戒',4,1004,'-04-19','30000.00',NULL,20),(1009,'罗贯中',1,NULL,'2001-11-17','50000.00',NULL,10),(1010,'吴用',3,1006,'2001-09-08','15000.00','0.00',30),(1011,'沙僧',4,1004,'-05-23','11000.00',NULL,20),(1012,'李逵',4,1006,'2001-12-03','9500.00',NULL,30),(1013,'小白龙',4,1004,'2001-12-03','30000.00',NULL,20),(1014,'关羽',4,1007,'2002-01-23','13000.00',NULL,10);-- 工资等级表CREATE TABLE salarygrade (grade INT PRIMARY KEY,losalary INT,hisalary INT);-- 添加5个工资等级INSERT INTO salarygrade(grade,losalary,hisalary) VALUES (1,7000,12000),(2,1,14000),(3,14010,20000),(4,20010,30000),(5,30010,99990);

视图(了解)

目标

学习视图的创建，修改，查看，删除

视图的概述

视图（View）是一种虚拟存在的表。视图并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。通俗的讲，视图就是一条SELECT语句执行后返回的结果集。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。视图相对于普通的表的优势主要包括以下几项:

简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。

安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。

创建视图

创建视图的语法为：

CREATE [OR REPLACE] VIEW 视图名称 AS 查询语句;

示例 , 创建city_country_view视图 , 执行如下SQL

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CZJMQYa8-1597574271357)(1595558413341.png)]

-- 创建一个视图CREATE OR REPLACE VIEW view_emp_dept AS SELECT e.*, d.dname, d.loc FROM emp e INNER JOIN dept d ON e.dept_id=d.id;

使用视图

使用视图的语法为：

SELECT * FROM 视图名称;

查看视图

从 MySQL 5.1 版本开始，使用 SHOW TABLES 命令的时候不仅显示表的名字，同时也会显示视图的名字，而不存在单独显示视图的 SHOW VIEWS 命令。

SHOW TABLES;

结果：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-uRDNBfRv-1597574271362)(1595428494282.png)]

如果需要查询某个视图的定义，可以使用 SHOW CREATE VIEW 命令进行查看

-- 查看创建视图的sql语句SHOW CREATE VIEW view_emp_dept;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-VXISCh2R-1597574271379)(1595558554713.png)]

修改视图

修改视图的语法为：

ALTER VIEW 视图名称 AS 查询语句;

删除视图

语法 :

DROP VIEW [IF EXISTS] 视图名称;

示例 , 删除视图city_country_view

DROP VIEW view_emp_dept;

小结

视图在以后什么时候可以去使用？

复杂的多表查询语句.频繁使用,就可以使用视图来保存这个复杂的SQL语句

创建视图、删除视图、查看视图

创建视图: CREATE OR REPLACE VIEW 视图名称 AS 查询语句;删除视图: DROP VIEW IF EXISTS 视图名称;查看视图: SHOW TABLES;使用视图: SELECT * FROM 视图名;

MySQL的内置函数

MySQL字符串函数和数学函数（了解）

目标

了解MySQL中字符串函数和数学函数

MySQL中字符串函数如下:

示例代码

-- 显示所有员工的姓氏，截取从1开始计数SELECT SUBSTR(ename,1,1) FROM emp;-- 显示所有员工姓名和名字的长度SELECT ename, CHAR_LENGTH(ename) FROM emp;-- 将所有姓刘的员工，姓氏替换为牛SELECT e.`ename` 原名,REPLACE(e.`ename`,'刘','牛') FROM emp e;-- 将所有员工的编号和姓名拼接在一起SELECT CONCAT(e.`id`,e.`ename`) FROM emp e;

MySQL中数学函数:

示例代码

-- 统计每个部门的平均薪资,保留2位小数SELECT e.`dept_id`, ROUND(AVG(e.`salary`),2) FROM emp e GROUP BY e.`dept_id`;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-93VCLsoB-1597574271420)(1595412796544.png)]

MySQL日期函数(重点)

目标

了解日期函数

MySQL日期函数

示例代码

-- 统计每个员工入职的天数SELECT e.ename, DATEDIFF(CURDATE() ,e.joindate) FROM emp e; -- 统计每个员工的工龄 SELECT e.ename, ROUND(DATEDIFF(CURDATE(),e.joindate)/365,0) FROM emp e; -- 查询2001年入职的员工SELECT * FROM emp e WHERE YEAR(e.`joindate`)=2001; -- 统计入职以上的员工有多少个SELECT * FROM emp e WHERE DATEDIFF(CURDATE(),e.joindate)/365 > 19;

MySQL高级函数CASE和IF

目标

学习MySQL高级函数CASE和IF

CASE高级函数

在查询代码的过程中，可能我们需要对查询的结果进行判断, 就会使用到CASE函数, 语法如下:

-- case表达式语法1select 字段名1, 字段名2, case 字段名when 值1 then 返回的值when 值2 then 返回的值...else上面都不符合返回的值end 列名from 表名;-- case表达式语法2select 字段名1, 字段名2, case when 判断条件1 then 返回的值when 判断条件1 then 返回的值...else上面条件都不成立返回的值endfrom 表名;-- case表达式功能实现分支条件判断，与java的switch结构类似，当字段的值与when的值匹配时返回 then 后面值，都不符合返回else的值

举列：

-- 需求:查询每个员工的工资等级并排序-- 工资等级在1显示为 '努力赚钱'-- 工资等级在2显示为 '小康生活'-- 工资等级在3显示为 '可以娶媳妇'-- 工资等级在4显示为 '可以买车'-- 工资等级在5显示为 '可以买房'-- 工资等级不在以上列表中显示为 '土豪'-- 分析-- 确定表员工信息：emp表工资等级：salarygrade表-- 确定表连接SELECT e.ename, e.salary , s.grade,CASE s.gradeWHEN 1 THEN '努力赚钱'WHEN 2 THEN '小康生活'WHEN 3 THEN '可以娶媳妇'WHEN 4 THEN '可以买车'WHEN 5 THEN '可以买房'ELSE '土豪'END 建议信息FROM emp e INNER JOIN salarygrade s ON e.salary BETWEEN s.losalary AND s.hisalaryORDER BY s.grade DESC;-- 语法2 解决SELECT e.ename, e.salary , s.grade,CASE WHEN s.grade=1 THEN '努力赚钱'WHEN s.grade=2 THEN '小康生活'WHEN s.grade=3 THEN '可以娶媳妇'WHEN s.grade=4 THEN '可以买车'WHEN s.grade=5 THEN '可以买房'ELSE '土豪'END 建议信息FROM emp e INNER JOIN salarygrade s ON e.salary BETWEEN s.losalary AND s.hisalaryORDER BY s.grade DESC;

IF高级函数

IF函数也是用于条件判断, 语法如下

IF(条件, '条件成立返回的值', '条件不成立返回的值')

例子：

-- 工资(月薪)+奖金大于等于20000的员工显示'收入不错'，否则显示'继续努力'SELECT e.`ename`, e.`salary` + IFNULL(e.`bonus`, 0) 总收入, IF(e.`salary`+IFNULL(e.`bonus`, 0)>=20000, "收入不错", "继续努力") FROM emp e;

综合案例练习

目标

练习上面学习过的函数

练习1

需求：计算员工的日薪(按30天)，截断保留二位小数

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0oAslZSt-1597574271427)(1595432330140.png)]

-- 需求：计算员工的日薪(按30天)，截断保留二位小数SELECT e.`ename`, ROUND(e.`salary`/30,2) 日薪 FROM emp e;

练习2

需求：计算出员工的年薪，并且以年薪降序排序

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-8oZdb76P-1597574271430)(1595427707940.png)]

-- 需求：计算出员工的年薪，并且以年薪排序降序SELECT e.`ename`, e.`salary`*12+IFNULL(e.bonus,0) 年薪 FROM emp e ORDER BY e.`salary`*12+IFNULL(e.bonus,0) DESC;

练习3

找出奖金少于5000或者没有获得奖金的员工的信息

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dUKUUiaE-1597574271435)(1595427763714.png)]

-- 需求：找出奖金少于5000或者没有获得奖金的员工的信息SELECT e.`ename`,e.bonus 奖金 FROM emp e WHERE e.bonus<5000 OR e.bonus IS NULL;

练习4

需求：返回员工职务名称及其从事此职务的最低工资

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vUn4xxPW-1597574271443)(1595428068993.png)]

-- 需求4：返回员工职务名称及其从事此职务的最低工资-- 查看哪些表: emp e , job-- 清除笛卡尔积 e.job_id = j.id-- 条件分组 group by e.job_id-- 查询字段 SELECT j.`jname`, MIN(e.`salary`) FROM emp e INNER JOIN job jON e.`job_id` = j.`id` GROUP BY j.`id`;

练习5

需求：返回工龄超过，且2月份入职的员工信息

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xbDn5uPB-1597574271446)(1595428107322.png)]

-- 需求5：返回工龄超过，且2月份入职的员工信息SELECT * FROM emp e WHERE DATEDIFF(NOW(),e.`joindate`)/365>10 AND MONTH(e.`joindate`)=2;

练习6

需求：返回与猪八戒同一年入职的员工

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vTRq2mlz-1597574271449)(1595428140902.png)]

-- 需求6：返回与猪八戒同一年入职的员工-- 1. 找到二师兄入职的年份SELECT YEAR(e.`joindate`) FROM emp e WHERE e.`ename`='猪八戒';-- 2. 找到猪八戒同一年入职的员工SELECT * FROM emp e WHERE YEAR(e.`joindate`) = (SELECT YEAR(e.`joindate`) FROM emp e WHERE e.`ename`='猪八戒') AND e.`ename`!='猪八戒';

练习7

需求：返回工资为二等级（工资等级表）的职员名字（员工表）、部门名称（部门表）

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-8LAhLUGt-1597574271453)(1595428184689.png)]

-- 需求7：返回工资为二等级（工资等级表）的职员名字（员工表）、部门名称（部门表）-- 确认表 emp e , dept d , salarygrade s-- 清除笛卡尔积 e.dept_id = d.id , e.salary between s.losalary and s.hisalary-- 额外条件 s.grande=2-- 确认字段 SELECT e.`ename`,d.`dname`,s.`grade` FROM emp eINNER JOIN dept d ON e.`dept_id` = d.idINNER JOIN salarygrade s ON e.`salary` BETWEEN s.`losalary` AND s.`hisalary`WHERE s.`grade`=2;

练习8

需求：涨工资：董事长2000 经理1500 其他800

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-h5Az0pC5-1597574271455)(1595428240162.png)]

-- 练习8：涨工资：董事长2000 经理1500 其他800-- 确认表 emp e ,job j-- 清除笛卡尔积 e.job_id = j.id -- 额外条件无-- 确认字段 SELECT e.ename, j.`jname`, e.`salary`,CASE j.`jname`WHEN '董事长' THEN e.`salary`+2000WHEN '经理' THEN e.`salary`+1500ELSE e.`salary`+800END 涨后 FROM emp eINNER JOIN job j ON e.job_id = j.`id`;

存储过程入门(个别公司在使用)

目标

学习存储过程的创建，修改，查看，删除

存储过程和函数概述

存储过程和存储函数是事先经过编译并存储在数据库中的一段 SQL 语句的集合，调用存储过程和函数可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。

优点：

1 . 减少数据在数据库和应用服务器之间的传输，提高效率。

缺点：

由于存储过程是需要把SQL语句保存在MySQL的服务器上，在真实环境中，普通程序根本是不可能可以接触到真实数据库环境的，那么会造成调试非常的麻烦。

数据库的迁移，存储过程是没法迁移。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ym8LthGQ-1597574271457)(/1597547355013.png)]

创建存储过程

在MySQL中的存储过程或者是存储函数是没有{}表示范围

语法

CREATE PROCEDURE procedure_name([proc_parameter[,...]])BEGIN-- 多条SQL语句END;

示例

-- 创建一个打印hello world的存储过程-- 注意： mysql中默认结束语句使用; 但是创建一个存储过程出现了多次; 时候mysql就不知道你的函数的范围在哪里-- 所以我们在定义函数或者是存储过程的时候一定要修改默认的结束符号。 delimiter 关键字就可以修改结束符号-- 这个语句则代表了mysql的结束符号以及变成$DELIMITER $ CREATE PROCEDURE print_hello()BEGINSELECT 'hello world';END $DELIMITER ; -- 把MySQL的结束符号修改回;

DELIMITER

该关键字用来声明SQL语句的分隔符 , 告诉 MySQL 解释器，该段命令是否已经结束了，MySQL是否可以执行了。

默认情况下，delimiter是分号;。在命令行客户端中，如果有一行命令以分号结束，那么回车后，MySQL将会执行该命令。

调用存储过程

语法

CALL procedure_name();

示例：

-- 调用print_hello的存储过程CALL print_hello();

查看存储过程（了解）

SELECT * FROM mysql.proc WHERE db='数据库名';-- 查询存储过程的状态信息SHOW PROCEDURE STATUS;-- 查询某个存储过程的定义SHOW CREATE PROCEDURE print_hello;

结果：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zXuU960w-1597574271459)(1595429063604.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Kn19G1kt-1597574271461)(1595429071503.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-S91DlRsB-1597574271462)(1595429075754.png)]

删除存储过程

语法

DROP PROCEDURE [IF EXISTS] sp_name；

示例

-- 删除存储过程DROP PROCEDURE print_hello;

小结

创建存储过程

DELIMITER $CREATE PROCEDURE 存储过程名()BEGINSQL语句;END $DELIMITER ;

调用存储过程

CALL 存储过程名();

删除存储过程

DROP PROCEDURE 存储过程名();

存储过程编程-变量(了解)

存储过程是可以编程的，意味着可以使用变量，表达式，控制结构，来完成比较复杂的功能。

目标

在存储过程中使用变量

DECLARE定义变量

通过DECLARE可以定义一个局部变量，该变量的作用范围只能在BEGIN…END块中，语法:

DECLARE var_name type [DEFAULT value];

示例

DELIMITER $CREATE PROCEDURE pro_test01()BEGIN-- 定义一个变量DECLARE number INT DEFAULT 5;SELECT number + 10;END $DELIMITER ;CALL pro_test01();

效果

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-iZb07WBy-1597574271463)(/1596889239231.png)]

SET给变量赋值

直接赋值使用 SET，可以赋常量或者赋表达式，具体语法如下：

SET var_name = expr [, var_name = expr] ...

示例

DELIMITER $CREATE PROCEDURE pro_test02()BEGIN-- 定义一个变量DECLARE str VARCHAR(30) DEFAULT '中国';-- 给变量设置值通过set命令给变量设置值SET str = '我爱java，java不爱我';-- 查看变量SELECT str;END $DELIMITER ;

效果

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-QctFGKQT-1597574271465)(1595572831537.png)]

通过SELECT… INTO方式进行赋值操作

DELIMITER $CREATE PROCEDURE pro_test03()BEGIN-- 定义一个变量可以用于接收查询字段数值DECLARE num INT ;-- 查询员工的总数给num赋值给变量赋值的方式二： select 字段名 into 变量名 from 表 SELECT COUNT(*) INTO num FROM emp;-- 查看变量的值SELECT num;END $DELIMITER ;CALL pro_test03();

效果

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-u6RVoGPd-1597574271467)(1595573066601.png)]

IF关键条件判断

语法格式

if condition1 then statement_list[elseif condition2 then statement_list] ...[else statement_list]end if;

示例

需求：根据定义的身高变量，判定当前身高的所属的身材类型

180 及以上 ----------> 身材高挑

170 - 180 ---------> 标准身材

170 以下 ----------> 一般身材

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KU389ln8-1597574271470)(1595429514160.png)]

DELIMITER $CREATE PROCEDURE pro_test04()BEGIN -- 定义一个变量存储身高DECLARE height INT DEFAULT 175;-- 定义一个变量存储结果DECLARE result VARCHAR(10);-- 判断身高IF height>=180 THEN SET result = '身材高挑';ELSEIF height>=170 THEN SET result = "标准身材";ELSE SET result = "一般身材";-- 结束if语句END IF;SELECT result;END $DELIMITER ;-- 调用存储过程CALL pro_test04();

小结

IF语句的格式

if 条件1 then SQL语句1;[elseif 条件2 then SQL语句2;] ...[else SQL语句3;]end if;

存储过程编程-参数和返回值

目标

在存储过程中传递参数和返回数据

语法格式

create procedure procedure_name([in/out/inout] 参数名参数类型)...IN : 该参数可以作为输入，也就是需要调用方传入值 , 默认OUT: 该参数作为输出，也就是该参数可以作为返回值INOUT: 既可以作为输入参数，也可以作为输出参数

示例

需求：根据定义的身高变量，判定当前身高的所属的身材类型

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5u8MAflg-1597574271471)(1595429577377.png)]

IN-输入参数

-- 传入参数 IN 代表是输入参数DELIMITER $CREATE PROCEDURE pro_test05(IN height INT)BEGIN -- 定义一个变量存储结果DECLARE result VARCHAR(10);-- 判断身高IF height>=180 THEN SET result = '身材高挑';ELSEIF height>=170 THEN SET result = "标准身材";ELSE SET result = "一般身材";-- 结束if语句END IF;SELECT result;END $DELIMITER ;CALL pro_test05(163);

OUT-输出参数(相当于返回值)

需求:根据传入的身高变量，获取当前身高的所属的身材类型

示例

-- OUT 代表的是输出参数DELIMITER $CREATE PROCEDURE pro_test06(IN height INT, OUT result VARCHAR(10))BEGIN -- 判断身高IF height>=180 THEN SET result = '身材高挑';ELSEIF height>=170 THEN SET result = "标准身材";ELSE SET result = "一般身材";-- 结束if语句END IF;END $DELIMITER ;-- 调用的时候输出参数需要使用一个类似于变量的内容去接收即可。CALL pro_test06(175,@abc); -- @符号表示的是本次会话，只要不关闭连接，abc这个变量都可以使用。 SELECT @abc;

效果

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-i8dfnOdW-1597574271474)(1595429638534.png)]

扩展
@: 表示用户会话变量
@@: 表示系统变量

例如

@xxx: 在变量名称前面加上“@”符号，叫做用户会话变量，表示本次会话只要不关闭连接,这个变量都可以使用。

存储过程编程-循环

目标

在存储过程中使用循环

WHILE循环

语法结构

while search_condition dostatement_listend while;

**需求:**计算从 1加到n的值

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fXyZaJTw-1597574271476)(1595429704908.png)]

-- 需求：定义一个存储过程求1~n的和DELIMITER $CREATE PROCEDURE pro_test07(IN n INT)BEGIN -- 定义求和变量DECLARE SUM INT DEFAULT 0;-- 定义变量i, i从1变化到nDECLARE i INT DEFAULT 1;WHILE i <= n DOSET SUM = SUM + i; -- 求和SET i = i+1; -- i + 1END WHILE;SELECT SUM;END $DELIMITER ;-- 调用存储过程CALL pro_test07(5);

REPEAT循环

有条件的循环控制语句，当满足条件的时候退出循环。WHILE是满足条件才执行，REPEAT是满足条件就退出循环。

语法结构 :

REPEATstatement_listUNTIL conditionEND REPEAT;REPEAT当UNTIL的条件为true结束了循环

需求：计算从 1加到n的值

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-aZzYTUaj-1597574271477)(1595429762072.png)]

-- 需求：定义一个存储过程求1~n的和DELIMITER $CREATE PROCEDURE pro_test08(IN n INT)BEGIN -- 定义一个变量存储的求和结果DECLARE SUM INT DEFAULT 0;-- 定义变量i, 从1变化到nDECLARE i INT DEFAULT 1;REPEATSET SUM = SUM + i;SET i = i + 1;UNTIL i > nEND REPEAT;SELECT SUM;END $DELIMITER ;-- 调用存储过程CALL pro_test08(100);

LOOP语句

LOOP实现简单的循环，退出循环的条件需要使用其他的语句定义，通常可以使用 LEAVE 语句实现，具体语法如下：

[begin_label:] LOOPif 结束循环的条件 then leave 标签名end if; 循环体;END LOOP [end_label]

如果不在 statement_list 中增加退出循环的语句，那么 LOOP 语句可以用来实现简单的死循环。

[begin_label:]为循环前缀名，当使用leave关键字退出循环时要设置这个名字。

LEAVE语句

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-JZ2BpBKq-1597574271478)(1595429908291.png)]

用来从标注的流程构造中退出，通常和 BEGIN … END 或者循环一起使用。下面是一个使用 LOOP 和 LEAVE 的简单例子 , 退出循环：

-- 需求：定义一个存储过程求1~n的和DELIMITER $CREATE PROCEDURE pro_test09(IN n INT)BEGIN -- 定义一个变量存储的求和结果DECLARE SUM INT DEFAULT 0;-- 定义变量i, 从1变化到nDECLARE i INT DEFAULT 1;getSum:LOOP-- 结束循环的条件IF i > n THENLEAVE getSum; -- 离开LOOP循环, 相当于break结束循环END IF;SET SUM = SUM + i;SET i = i + 1;END LOOP getSum;SELECT SUM;END $DELIMITER ;-- 调用函数CALL pro_test09(100);

注意: leave后面必须设置循环前缀名, 否则报错

游标/光标(了解)

结果集: 通过查询语句查到的数据就是结果集

游标是用来存储查询结果集的数据类型, 在存储过程和函数中可以使用光标对结果集进行循环的处理。游标的使用

包括游标的声明、OPEN、FETCH 和 CLOSE，其语法分别如下。

声明游标：

DECLARE 游标名字 CURSOR FOR 查询语句;

OPEN 游标

OPEN 游标名字;

FETCH 游标：

FETCH 游标名字 INTO 变量名1 ,变量2...

CLOSE 游标：

CLOSE 游标名字;

**示例需求 😗*由于公司业务拓展，当前新增了一张表user表，将emp表中的id和ename数据保存到user表中。

CREATE TABLE USER (id INT,ename VARCHAR(50));

示例代码:

DELIMITER $CREATE PROCEDURE moveData()BEGIN-- 定义两个变量存储数据DECLARE lid INT;DECLARE lNAME VARCHAR(30);-- 定义一个变量用于结束循环语句DECLARE num INT DEFAULT 1;-- 定义一个游标存储查询的结果DECLARE result_data CURSOR FOR SELECT e.id,e.ename FROM emp e;-- 定义mysql游标退出机制，游标不断抓取的数据的时候，如果一旦没有抓取到数据，就会触发该机制，然后把num设置为0.DECLARE EXIT HANDLER FOR NOT FOUND SET num=0;-- 打开游标OPEN result_data;REPEAT-- 抓取里面的数据每FETCH一次就是抓取一行的数据FETCH result_data INTO lid, lNAME;-- 把数据插入user表中INSERT INTO USER VALUES(lid, lNAME);UNTIL num=0END REPEAT;-- 关闭游标CLOSE result_data;END $DELIMITER ;CALL moveData();

存储函数

函数与存储函数的区别在于，存储过程不能有返回值，函数可以返回值。

语法结构

CREATE FUNCTION function_name([param type ... ])RETURNS 返回值类型BEGIN...END;

案例

需求

定义一个存储函数, 计算指定员工编号的年薪返回 ;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Ug2OJ0Zu-1597574271480)(1595430186593.png)]

-- 定义一个存储函数, 计算指定员工编号的年薪返回 ;DELIMITER $CREATE FUNCTION getMoney(idnum INT)RETURNS INTBEGIN-- 定义一个变量存储年薪DECLARE money INT DEFAULT 0;SELECT (e.salary*12+IFNULL(e.bonus,0)) INTO money FROM emp e WHERE id=idnum;-- 返回年薪RETURN money;END $DELIMITER ;-- 调用函数SELECT getMoney(1001);

小结

函数和存储过程的区别

关键字不一样,函数使用FUNCTION输入参数不要写IN有返回值类型: returns 类型

JDBC的概念

目标

学习JDBC的概念学习JDBC的作用

客户端操作MySQL数据库的方式

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-mzbBumVk-1597574271481)(/1550289380140.png)]

使用DOS命令行方式使用第三方客户端来访问MySQL：SQLyog、Navicat、SQLWave、MyDB Studio、EMS SQL Manager for MySQL通过Java来访问MySQL数据库，今天要学习的内容

什么是JDBC

JavaDataBaseConnectivity：Java数据库连接(是一套规范,接口)

JDBC的作用

通过JDBC可以让Java操作数据库

JDBC的由来

直接写代码操作数据库

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-yqYi8khL-1597574271482)(/jdbc01.png)]

直接写代码操作数据库存在的问题：

不知道MySQL数据库的操作方式，解析方式代码繁琐，写起来麻烦MySQL和Oracle等其他数据库的操作方式和解析方式不同，每个数据库都要写一套代码MySQL和Oracle等其他数据库相互切换麻烦

JDBC规范定义接口，具体的实现由各大数据库厂商来实现

JDBC是Java访问数据库的标准规范。真正怎么操作数据库还需要具体的实现类，也就是数据库驱动。每个数据库厂商根据自家数据库的通信格式编写好自己数据库的驱动。所以我们只需要会调用JDBC接口中的方法即可。数据库驱动由数据库厂商提供。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-R7T7uWWo-1597574271484)(/1550373498072.png)]

JDBC的好处

我们只需要会调用JDBC接口中的方法即可，使用简单使用同一套Java代码，进行少量的修改就可以访问其他JDBC支持的数据库

小结

说出JDBC的概念？

java数据库连接

说出JDBC的作用？

通过JDBC可以让Java操作数据库

JDBC核心API的介绍

目标

学习JDBC四个核心对象

JDBC会用到的包

java.sql：JDBC访问数据库的基础包javax.sql： JDBC访问数据库的扩展包数据库的驱动，各大数据库厂商来实现

JDBC四个核心对象

这几个类都是在java.sql包中

DriverManager: 用于注册驱动Connection: 表示数据库的连接Statement: 执行SQL语句的对象ResultSet: 结果集或一张虚拟表

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-00k750O0-1597574271486)(/jdbc03.png)]

JDBC访问数据库的步骤

通过DriverManager注册驱动获取连接Connection通过Statement，将我们写的SQL语句运送到数据库执行返回结果集ResultSet

小结

JDBC四个核心对象？

DriverManager: 注册驱动

Connection: 数据库连接

Statement: 执行SQL语句的对象

ResultSet: 结果集

JDBC注册驱动

我们Java程序需要通过数据库驱动才能连接到数据库，因此需要注册驱动。

目标

学习导入mysql驱动Jar包

学习JDBC注册数据库驱动

在注册驱动前需要先导入驱动的Jar包

导入驱动Jar包

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dNMmvUn3-1597574271487)(/1555942738648.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Fp5yOUPW-1597574271489)(/1555942725646.png)]

注册驱动

我们Java程序需要通过数据库驱动才能连接到数据库，因此需要注册驱动。

MySQL的驱动的入口类是：com.mysql.jdbc.Driver

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gjCwzQ69-1597574271490)(/jdbc07.png)]

API介绍

java.sql.DriverManager类用于注册驱动。提供如下方法注册驱动

static void registerDriver(Driver driver) 向 DriverManager 注册给定驱动程序。

使用步骤

1.DriverManager.registerDriver(驱动对象); 传入对应参数即可

案例代码

public class Demo01 {public static void main(String[] args) throws Exception {// 注册驱动DriverManager.registerDriver(new com.mysql.jdbc.Driver());}}

通过查询com.mysql.jdbc.Driver源码，我们发现Driver类“主动”将自己进行注册

public class Driver extends NonRegisteringDriver implements java.sql.Driver {static {try {// 自己自动注册java.sql.DriverManager.registerDriver(new Driver());} catch (SQLException E) {throw new RuntimeException("Can't register driver!");}}public Driver() throws SQLException {}}

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SZ1NzrF1-1597574271491)(/jdbc08.png)]

注意：使用DriverManager.registerDriver(new com.mysql.jdbc.Driver());，存在两方面不足
硬编码，后期不易于程序扩展和维护驱动被注册两次

使用Class.forName("com.mysql.jdbc.Driver");加载驱动，这样驱动只会注册一次

public class Demo01 {public static void main(String[] args) throws Exception {Class.forName("com.mysql.jdbc.Driver"); // 后期可以将"com.mysql.jdbc.Driver"字符串写在文件中.}}

演示：Class.forName("包名.类名");会走这个类的静态代码块

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-8Jqi4e5B-1597574271493)(/jdbc10.png)]

通常开发我们使用Class.forName() 加载驱动。Class.forName("com.mysql.jdbc.Driver");会走Driver类的静态代码块。在静态代码块中注册一次驱动。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-T22pn8y4-1597574271494)(/jdbc09.png)]

总结：注册MySQL驱动使用Class.forName("com.mysql.jdbc.Driver");

小结

导入mysql驱动Jar包

如何注册数据库驱动？

Class.forName("com.mysql.jdbc.Driver");

获取Connection连接和Statement

Connection表示Java程序与数据库之间的连接，只有拿到Connection才能操作数据库。

目标

学习JDBC获取数据库连接

学习获取Statement对象

API介绍

java.sql.DriverManager类中有如下方法获取数据库连接

static Connection getConnection(String url, String user, String password) 连接到给定数据库 URL ，并返回连接。

参数说明

String url：连接数据库的URL，用于说明连接数据库的位置String user：数据库的账号String password：数据库的密码

连接数据库的URL地址格式：协议名:子协议://服务器名或IP地址:端口号/数据库名

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-h38gjtGf-1597574271496)(/jdbc13.png)]

MySQL写法：jdbc:mysql://localhost:3306/day16

如果是本地服务器，端口号是默认的3306，则可以简写：jdbc:mysql:///day16

注意事项

如果数据出现乱码需要在url加上参数: ?characterEncoding=utf8，表示让数据库以UTF-8编码来处理数据。
如: jdbc:mysql://localhost:3306/day16?characterEncoding=utf8

获取Statement对象

在java.sql.Connection接口中有如下方法获取到Statement对象

Statement createStatement() 创建一个 Statement 对象来将 SQL 语句发送到数据库

使用步骤

注册驱动调用方法：DriverManager.getConnection(url, user, password); 传入对应参数即可

案例代码

public class Demo01 {public static void main(String[] args) throws Exception {Class.forName("com.mysql.jdbc.Driver");// 连接到MySQL// url: 连接数据库的URL// user: 数据库的账号// password: 数据库的密码Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/day24", "root", "root");System.out.println(conn);}}

案例效果

1.连接成功

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-z9uE7OVB-1597574271497)(/jdbc14.png)]

2.连接失败

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-HJLFoR3P-1597574271499)(/jdbc15.png)]

小结

JDBC获取数据库连接使用哪个API？

DriverManager.getConnection(url, user, password);

通过JDBC连接mysql的URL写法？

jdbc:mysql://localhost:3306/数据库如果连接的是本机端口号是3306,可以省略jdbc:mysql:///数据库

JDBC实现对单表数据增、删、改

目标

学习JDBC实现对单表数据增、删、改

准备数据

-- 创建分类表CREATE TABLE category (cid INT PRIMARY KEY AUTO_INCREMENT,cname VARCHAR(100));-- 初始化数据INSERT INTO category (cname) VALUES('家电');INSERT INTO category (cname) VALUES('服饰');INSERT INTO category (cname) VALUES('化妆品');

JDBC实现对单表数据增、删、改

我们要对数据库进行增、删、改、查，需要使用Statement对象来执行SQL语句。

API介绍

Statement的API介绍

boolean execute(String sql)用执行任何SQL语句,如果是查询返回true,如果不是查询语句返回false; 通常不用

int executeUpdate(String sql)用于执行增删改等语句; 返回影响的行数

ResultSet executeQuery(String sql)用于执行查询语句; 返回查询到的结果集

executeQuery：用于执行查询SQL
executeUpdate：用于执行除查询外的SQL

使用步骤

注册驱动获取连接获取Statement对象使用Statement对象执行SQL语句释放资源

案例代码

public class Demo03 {public static void main(String[] args) throws Exception {Class.forName("com.mysql.jdbc.Driver");Connection conn = DriverManager.getConnection("jdbc:mysql:///day24", "root", "root");System.out.println(conn);// 从连接中拿到一个Statement对象Statement stmt = conn.createStatement();// 1.插入记录String sql = "INSERT INTO category (cname) VALUES ('手机');";int i = stmt.executeUpdate(sql);System.out.println("影响的行数:" + i);// 2.修改记录sql = "UPDATE category SET cname='汽车' WHERE cid=4;";i = stmt.executeUpdate(sql);System.out.println("影响的行数:" + i);// 3.删除记录sql = "DELETE FROM category WHERE cid=1;";i = stmt.executeUpdate(sql);System.out.println("影响的行数:" + i);// 释放资源stmt.close();conn.close();}}

案例效果

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-owetqplI-1597574271502)(/jdbc17.png)]

小结

使用JDBC操作数据库的步骤：

注册驱动获取连接Connection获取Statement小货车执行SQL语句 executeUpdate(SQL) 执行增删改查关闭资源

JDBC实现对单表数据查询

目标

JDBC实现对单表数据查询

ResultSet用于保存执行查询SQL语句的结果。

我们不能一次性取出所有的数据，需要一行一行的取出。

ResultSet的原理

ResultSet内部有一个指针，记录获取到哪行数据调用next方法, ResultSet内部指针会移动到下一行数据我们可以通过ResultSet得到一行数据 getXxx得到某列数据

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-uXI4Gm9Z-1597574271505)(/jdbc18.png)]

ResultSet获取数据的API

其实ResultSet获取数据的API是有规律的get后面加数据类型。我们统称getXXX()

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Gccwy5Uc-1597574271506)(/jdbc19.png)]

使用JDBC查询数据库中的数据的步骤

注册驱动获取连接获取到Statement使用Statement执行SQLResultSet处理结果关闭资源

案例代码

public class Demo04 {public static void main(String[] args) throws Exception {Class.forName("com.mysql.jdbc.Driver");Connection conn = DriverManager.getConnection("jdbc:mysql:///day24", "root", "root");Statement stmt = conn.createStatement();String sql = "SELECT * FROM category;";ResultSet rs = stmt.executeQuery(sql);// 内部有一个指针,只能取指针指向的那条记录while (rs.next()) {// 指针移动一行,有数据才返回true// 取出数据int cid = rs.getInt("cid");String cname = rs.getString("cname");System.out.println(cid + " == " + cname);}// 关闭资源rs.close();stmt.close();conn.close();}}