本文介绍了数据库中的常见对象，如表、视图和索引，强调了它们在数据管理和查询优化中的作用。此外，详细探讨了存储过程的概念、优缺点及创建与调用方法，包括参数类型和注意事项。
---------------------------------- 常见数据库对象 （了解）------------------------
表（ TABLE ） : 有行有列，行又叫做记录；列又叫做（属性）字段
备份表：在数据库中创建临时备份表
CREATE TABLE 表名 AS SELECT * FROM 表名 [WHERE 查询条件];
CREATE TABLE tmp_emp2 AS SELECT * FROM emp;
SELECT * FROM tmp_emp2;
DESC tmp_emp2; -- 临时表不会继承原表的主外键约束，但是表结构和表数据是一样的
备注：不建议创建过多的临时备份表在数据库，会影响数据库的空间
DROP TABLE tmp_emp;
DROP TABLE tmp_emp2;
-- 视图（view）
是一种虚拟的表，具有和物理表相同的功能，视图可以进行编辑，但是对基本表没有影响，作用是可以保护数据的
隐私，同时让复杂的查询变得简单。比如：出于数据安全考虑，只对外暴露部分数据，就可以创建一个视图，把要
对外开放的数据放在视图当中，让别人访问视图就好。
例如：查询王五的选课情况（课程名称，老师，课时）
# 比较复杂的查询语句
SELECT cname,chour,tname FROM course WHERE cid IN(
SELECT cid FROM score WHERE sid=(
SELECT sid FROM student WHERE sname='王五'));
# 把查询的数据放在视图当中，后面再需要就直接从视图中查询获取王五的选课信息，不让看别人的
CREATE VIEW v0329 AS SELECT cname,chour,tname FROM course WHERE cid IN(
SELECT cid FROM score WHERE sid=(
SELECT sid FROM student WHERE sname='王五'));
# 后期需要查询王五的选课，就直接查询视图 v0329,一是很快，二是看不了别人，安全
SELECT * FROM v0329;
-- 索引：就是加快检索的速度，是优化数据查询效率的常见方法之一
索引：加快了数据库的检索速度，但是降低了插入、删除、修改等维护类的任务的执行速度
索引主要是对表中数据量特别大的时候有效果，有索引会让查询快一些，但是索引不是创建越多越好，索引的本质是牺牲
空间换取了效率。
一般索引都是添加在具体的列上面，如果有需要，开发还会添加一些联合索引。
CREATE INDEX 索引名 ON 表名(列名);
SELECT * FROM user_tab WHERE user_name='孙权'
CREATE INDEX in0329 ON user_tab(user_name);
DESC user_tab;
删除索引： DROP INDEX 名称 ON 表名;
DROP INDEX in0329 ON user_tab;
------------------------------ 存储过程（存过）-----------------------------
mysql 5.0 以后版本才开始支持存储过程
存储过程：是一种在数据库中存储的复杂的程序，以便外部程序或者其他应用程序对数据进行调用的数据库对象。
两个要点：存储在数据库中 复杂的程序
被调用
简单的来说：存储过程就是在数据库中编写代码，目的是为了完成特定的任务或者功能的 SQL 的集合，它是一个
语句集，经过编辑保存到数据库中，可以让指定的用户对其进行调用，提升效率。
即：是数据库中 SQL 层面的代码的封装和调用。
优点：
1、可封装，并且隐藏复杂的商业逻辑
2、可以进行回传值，并且可以接收参数
3、可以让复杂的语句封装成简单的函数或者子程序
4、可以用在数据校验，企业规则，内部逻辑比较复杂的业务上
缺点：
1、存储过程对数据库的依赖很大，定制化在特定的数据库上，支持的语言规范不一样
2、可移植性一般，复用率低
3、无法直接使用 SELECT 指令进行运行，因为是子程序
--------------------------------------------------------------------------
创建存储过程的语法：
CREATE PROCEDURE 过程名([IN OUT INOUT] 参数名 数据类型,[IN OUT INOUT] 参数名 数据类型,...)
BEGIN
过程体;
END;
----------------------------
# 存储过程的参数
mysql 中参数的定义，一共支持三种类型： IN OUT INOUT
IN 输入参数：表示调用者向过程传入值（可以是具体的值，也可以是变量）
OUT 输出参数：表示过程向调用者传出值（可以返回多个值，传出的值只能是变量）;
INOUT 输入输出参数：表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）。
建议： 输入值就用 IN 参数
输出值使用 OUT 参数
INOUT 参数尽量少用
注意：存储过程支持没有参数，即使没有参数，但是过程名后面的()必须要有，不可以省略；
参数的名字不能等于列名，否则的话在执行过程体的时候，参会会被当做整个列来出来。
# 过程体
由合法的 SQL 语句构成，即 SQL 命令集，可执行，过程体包含了过程调用执行的所有的 SQL 语句：
例如： DQL 、DML 、DDL 、IF-THEN-ELSE 等等，以及申明变量。
过程体的格式： 以 BEGIN 开始，以 END 结束（可以嵌套）
注意：每个嵌套块中每条命令结束，都必须使用 ; 结束。
# 调用
1）可以使用一个 CALL 命令通过名字对存储进行调用；
CALL 存储过程的名称();
2) 可以供外部程序调用，比如 Java 程序，比如 python 。
---------------------------- 存储过程实例 -----------------------------------
# 实例：不带参数的存储过程
CREATE PROCEDURE p1()
BEGIN
SELECT 'hello world!' ;
END;
/*
错误代码： 1064
You have an error in your SQL syntax; check the manual that corresponds to
your MySQL server version for the right syntax to use near '' at line 3
*/
-- 报错的原因，是语句命令没有正确结束，是因为 ; 界定符造成的，解决办法，修改界定符
# 修改界定符 ; 为 // 或者 $$ , 目的是让 ; 暂时失效，不表示语句结束
-- 修改界定符 delimiter
DELIMITER //
CREATE PROCEDURE p1()
BEGIN
SELECT 'hello world!' ;
END;
//
-- 调用
CALL p1; -- 每调用一次存储过程，就相当于把过程体执行一次
CALL p1(); -- 调用的时候，没有参数的存储过程，()可以加，也可以不加
------------------------------------------------------------------
# 实例2 ：带 in 类型的参数的存储过程
DELIMITER //
CREATE PROCEDURE select1(IN num INT)
BEGIN
SELECT num FROM DUAL;
END;
//
CALL select1(456);
# 把查询 sql 放在过程体当中
例如：根据输入的部门编号，查询部门的员工信息；
SELECT * FROM emp WHERE deptno=10 ; -- 以部门编号为10做例子
DELIMITER //
CREATE PROCEDURE select2(IN ptno INT)
BEGIN
SELECT * FROM emp WHERE deptno=ptno;
END;//
CALL select2(30);
-- 如果参数的名称和列名时一样的，会是什么情况？
DELIMITER //
CREATE PROCEDURE select2_1(IN deptno INT)
BEGIN
SELECT * FROM emp WHERE deptno=deptno;
END;//
CALL select2_1(20); # 出现了参数被当做整个列的情况，即使输入参数，查询的结果始终是所有部门员工，参数失效
#如果是删除的存储，就会删掉整个数据
-------------------------
SELECT * FROM user_tab WHERE user_name='孙权';
DELETE FROM user_tab WHERE user_name='孙权';
# 按照用户的姓名删除对应用户的信息
DELIMITER//
CREATE PROCEDURE del_user_name(IN name1 VARCHAR(50))
BEGIN
DELETE FROM user_tab WHERE user_name=name1;
END;
CALL del_user_name('孙权');
# 让参数也叫: user_name,跟列名一样，验证：
DELIMITER//
CREATE PROCEDURE del_user_name1(IN user_name VARCHAR(50))
BEGIN
DELETE FROM user_tab WHERE user_name=user_name;
END;
CALL del_user_name1('司马懿'); -- 调用存储过程，把表中所有数据都删除掉了，没有只是删掉参数对应的信息
-- 工作中，一定要注意，不要参数和列名叫一样的
# 存储过程编写的思路
前提：进入数据，保证可以正常使用存储过程要执行的数据库
1、修改 mysql 的界定符（语句结束符）
DELIMITER // 或者 DELIMITER $$
2、创建存储过程
CREATE PROCEDURE 名字()
BEGIN
过程体;
END;//
3、调用存储过程；
CALL 过程名();
练习：熟悉语法和创建步骤
1、输入两个参数，求和，用存储过程实现
DELIMITER //
CREATE PROCEDURE sum11(IN a INT,IN b INT)
BEGIN
SELECT a+b;
END;
CALL sum11(511,799);
2、给部门信息表中插入一个部门信息，分别用带参数的存储过程实现；
SELECT * FROM dept WHERE deptno=41;
INSERT INTO dept VALUES(42,'测试','西安');
DELIMITER //
CREATE PROCEDURE insert_dept(IN ptno INT,IN name11 VARCHAR(30))
BEGIN
INSERT INTO dept VALUES(ptno,name11,CONCAT(ptno,name11));
END;
CALL insert_dept(44,'火箭部2');
----------------------------
# 带变量的存储过程
变量：变量在存储过程中的使用，和编程的使用的过程类似，可以进行申明变量，用来取一类值。
# 用户变量
定义方式 SET @变量名=变量值
例如： SET @username='abc';
SELECT @username;
备注：用户变量，只是针对变量的创建者可以使用，用户断开以后，用户变量就会被销毁
作用：将 SQL 语句查询的结果存入用户变量，方便后面使用。
例如：刚刚删除用户信息的存储过程，修改一下，删除以后，让返回统计剩余多少行数，存储过程中需要2个参数，一个
输入，一个输出，即一个输入 IN 类型，一个输出用 OUT 类型
DELIMITER//
CREATE PROCEDURE del_user_name23(IN name1 VARCHAR(50),OUT user_num INT)
BEGIN
DELETE FROM user_tab WHERE user_name=name1;
SELECT COUNT(*) FROM user_tab INTO user_num;
SET @nums=user_num;
END;
SELECT * FROM user_tab;
CALL del_user_name23('周瑜',@nums); -- 把count()统计结果放在用户变量@nums这里
SELECT @nums; # 用 select 来查看验证结果
注意：不建议频繁使用用户变量，滥用用户变量会导致存过很难理解和管理。
用户变量的特点决定的：
1、每个用户都可以创建属于自己的私有变量，称为用户变量
2、用户变量用来临时存放一些数据，一旦用户下线，变量就失效销毁
3、用户变量只能定义者使用，其他用户无权使用
例如：将查询结果存入用户变量，类型 是 OUT 的参数
1）查询职工表的最高工资： SELECT MAX(sal) FROM emp;
2）后面想查看结果，定义一个用户变量， @abc ,把查询的结果值写入 INTO 变量
SELECT MAX(sal) FROM emp INTO @abc;
3) 这个变量就作为返回的结果
SELECT @abc;
# 用存储过程实现
DELIMITER //
CREATE PROCEDURE sel_max_sal(OUT abc INT)
BEGIN
SELECT MAX(sal) FROM emp INTO abc;
SET @abc=abc;
END;
CALL sel_max_sal123(@abc);
SELECT @abc;
备注:修改存储过程，删除原来的存储过程，新建修改后的存过；找到原来的存储过程，右键：改变存过，修改完成后，
一定要重新编译通过。
----------------------------------------------------------------
# 局部变量
只是在存储过程内部使用
定义方式： DECLARE 变量名称 数据类型 DEFAULT 0; -- default 0 ，表示变量的默认值是0，不是所有变量都需要默认值
例如： DECLARE aa INT DEFAULT 0 ; # 定义申明变量 aa 为 int 类型，默认值是0
DECLARE aa INT; # 定义 aa 为int 类型的变量
SET aa=0 ; # 给变量aa 赋值 0
例如：定义两个局部变量，并且赋值，将这两个局部变量的值求和
思路： DECLARE aa INT;
DECLARE bb INT;
SET aa=10;
SET bb=20;
求和： aa+bb -- 输出的结果可以存放在有一个用户变量中 @cc
# 存储过程实现
DELIMITER //
CREATE PROCEDURE sum_ab()
BEGIN
DECLARE aa INT;
DECLARE bb INT;
SET aa=10;
SET bb=20;
SET @cc=aa+bb;
END;
CALL sum_ab(11111,222234);
SELECT @cc;
--------------------- 存储过程中经常会出现分支语句，常见的 IF ELSE 条件
# 带一组 if else 选择分支的存储过程
IF 条件 THEN
语句块1;
ELSE
语句块2;
END IF;
例如：设置不同的参数值，按照条件输出信息
DELIMITER //
CREATE PROCEDURE test001()
BEGIN
IF 0>1 THEN
SELECT '哈哈';
ELSE
SELECT '呵呵';
END IF;
END;
CALL test001();
# 带参数的条件输出存储过程
DELIMITER //
CREATE PROCEDURE test002(IN num INT)
BEGIN
IF num=0 THEN
SELECT '哈哈';
ELSE
SELECT '呵呵';
END IF;
END;
CALL test002(123);
# 存储过程中带有多个分支结构条件语句：即 有多个 if ...else
IF 条件 THEN
语句1;
ELSEIF 条件2 THEN
语句2;
ELSEIF 条件3 THEN
语句3;
ELSE
语句4;
END IF;
# 带参数实现输入两个值，比较大小
DELIMITER //
CREATE PROCEDURE test004_1(IN aa INT ,IN bb INT)
BEGIN
IF aa>bb THEN
SELECT 'aa 大于 bb';
ELSEIF aa=bb THEN
SELECT '两个相等';
ELSE
SELECT 'aa 小于 bb';
END IF;
END;
CALL test004_1(123,678)
# 分支语句中还可以进行嵌套
DROP PROCEDURE test004_2;
DELIMITER //
CREATE PROCEDURE test004_2(IN aa INT ,IN bb INT)
BEGIN
IF aa>bb THEN
IF aa-bb>10 THEN
SELECT 'aa比bb大10以上';
ELSE
SELECT 'aa比bb只大一点！';
END IF;
ELSEIF aa=bb THEN
SELECT '两个相等';
ELSE
SELECT 'aa 小于 bb';
END IF;
END;
CALL test004_2(10,15);
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
