免费下载的网站wordpress英文博客主题

当前位置： 首页 > news >正文

免费下载的网站,wordpress英文博客主题,wordpress替代笔记,360建筑网广州八臂猿李工数据库对象#xff1a; 常见的数据库对象#xff1a; 视图#xff1a; 视图是一种虚拟表#xff0c;本身是不具有数据的占用很少的内存空间。 视图建立在已有表的基础上#xff0c;视图赖以建立的这些表称为基表。 视图的创建和删除只影响视图本身#xff0c;不影响对…数据库对象 常见的数据库对象 视图 视图是一种虚拟表本身是不具有数据的占用很少的内存空间。 视图建立在已有表的基础上视图赖以建立的这些表称为基表。 视图的创建和删除只影响视图本身不影响对应的基表。但是对视图中的数据进行增加、删除和修改操作时数据表基表中的数据会相应地发生变化。 当对基表中的数据进行增加删除和操作时与之对应的视图中的数据记录也会发生相对应的变化。 在数据库中视图不会保存数据数据真正保存在数据表基表中。 创建视图 精简版 CREATE  VIEW  视图名称 AS  查询语句; 关于视图中字段的字段名若不起字段名视图中的字段的字段名即为查询字段的字段名 ①可以在查询语句的查询的字段后起别名查询字段的别名即为视图中字段的字段名。 ②在视图名称后加上括号( )在括号中写上相应的字段的字段名要一一匹配。 创建单表视图 例 CREATE VIEW view_emp1– 方式一ASSELECT empno id,ename name,deptno dept_idFROM emp; CREATE VIEW view_emp2(emp_id,name,dept_id)– 方式二ASSELECT empno,ename,deptnoFROM emp; 创建多表视图 例 CREATE VIEW view_emp4ASSELECT e.empno,e.ename,e.deptno,d.locFROM emp eJOIN dept dON e.deptno d.deptno; 利用视图对数据进行格式化 比如想显示员工的信息的格式为 员工号–姓名–部门号–部门所在地 CREATE VIEW view_emp5ASSELECT CONCAT(e.empno,–,e.ename,–,e.deptno,–,d.loc) 员工信息FROM emp eJOIN dept dON e.deptno d.deptno; 或 CREATE VIEW view_emp6(员工信息)ASSELECT CONCAT(e.empno,–,e.ename,–,e.deptno,–,d.loc)FROM emp eJOIN dept dON e.deptno d.deptno; 基于视图创建视图 例 CREATE VIEW view_emp7(id,name)ASSELECT empno,enameFROM view_emp1; 查看视图 查看数据库的表对象、视图对象 SHOW TABLES 查看视图的结构 DESC / DESCRIBE 视图名称; 查看视图的属性信息 SHOW TABLE STATUS LIKE 视图; 查看视图的详细信息 SHOW CREATE VIEW 视图名称; 更新视图中的数据 类似于更新表的数据 例 UPDATE view_emp7SET name TonyWHERE id 7936;DELETE FROM view_emp7WHERE id 7936; 完成UPDATE操作后不仅视图view_emp7中的id为7936的员工的名字改为了Tony基表emp中的id为7936的员工的名字也改为了Tony。通过视图删除了id为7936的员工基表中的id为7936的员工的信息也会跟着删除。 基表中的更新删除操作当然也会同步到视图。 如果视图中的字段是基表数据的组函数更新和删除便不会成功。要使得视图可以更新视图中的行与基表中的行必须保证有一一对应的关系。 虽然可以更新视图数据但是总的来说视图作为虚拟表主要用于方便查询不建议更新视图的数据。 对视图的修改都是通过对实际基表中的数据的操作来完成的。 修改视图 方式一 使用CREATE OR REPLACE VIEW子句修改视图 CREATE OR REPLACE VIEW view_emp1– 方式一 AS SELECT 语句; 方式二 使用ALTER VIEW的方式 ALTER VIEW 视图名 AS  SELECT 语句; 删除视图 DROP VIEW 视图名; 也可以删除多个视图 DROP VIEW 视图名视图名……; 假如基于一个或多个视图创建了额外的视图将此一个或多个视图删除后会导致此额外的视图的查询的失败。 视图的优点 操作简单。 减少数据冗余。 数据更安全用户不需要查询数据表可以直接通过视图来获取数据表中的信息。 适应灵活多变的需求。 能够分解复杂的查询逻辑。 视图的缺点 如果实际数据表的结构变了就需要及时对相关的视图进行相应的维护。视图过多会导致数据库运维成本的问题。 存储过程与存储函数 存储过程与存储函数能将复杂的SQL逻辑封装在一起供以调用。 存储过程 存储过程Stored Proceduce是一组经过预先编译的SQL语句的封装。 执行过程存储函数预先存储在MySQL服务器上需要执行的时候客户端只需要向服务器端发出调用存储过程的命令服务器端就可以将预先存储好的这一系列SQL语句全部执行。 优点 简化操作提高了SQL语句的复用性。 减少网络传输量。 提高了数据查询的安全性。 关于参数前的符号 存储过程的参数类型可以是IN、OUT、INOUT也可以不带参数。 IN当前参数为输入参数也就是表示入参。如果没有定义参数种类默认是IN。 OUT当前参数为输出参数也就是表示出参。 INOUT当前参数既可以为输入参数也可以为输出参数。 DELIMITER 因为MySQL默认的语句结束符号为‘;’。为了避免与存储过程中的语句结束符相冲突需要用DELIMITER改变存储过程的结束符。 格式DELIMITER 新的结束符 例 在存储过程的存储过程体中 DELIMITER \(   -- 将结束符设置为‘\)’CREATE PROCEDURE 存储过程名(参数列表)BEGIN……具体的存储过程的操作END \(DELIMITER ;   -- 将结束符设置为‘;’以方便后面的操作使用‘;’为结束符。 注使用DELIMITER时应该注意避免使用‘\’为结束符因为其为MySQL的转义字符。 存储过程的创建 格式 DELIMITER 新结束符 CREATE PROCEDURE 存储过程名(参数列表) [ characteristics] BEGIN ……具体的存储过程的操作 END 新结束符 DELIMITER ; characteristics 指定存储过程的特性有以下取值 LANGUAGE SQL 说明存储过程体部分是由SQL语句组成的当前系统支持的语言为 SQL。 DETERMINISTIC和NOT DETERMINISTIC指明存储过程执行的结果是否正确. DETERMINISTIC表示结果是确定的每次执行存储过程时相同的输入会得到相同的输i出。 NOT DETERMINISTIC 表示结果是不确定的相同的输入可能得到不同的输出。 如果没有指定任意一个值默认 为 NOT DETERMINISTIC。 { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }指明子程序使 用SQL语句的限制。 CONTAINS SQL表明子程序包含SQL语句但是不包括读写数据的语句 NO SQL表明子程序不包含SQL语句 READS SQL DATA说明子程序包含读数据 的语句 MODIFIES SQL DATA表明子程序包含写数据的语句。 默认情况下系统会指定 为 CONTAINS SQL。 SQL SECURITY {DEFINER | INVOKER } 表示执行当前存储过程的权限指明哪些用户有权限来执行。 DEFINER表示只有定义者才能执行。 INVOKER表示拥有权限的调用者可以执行。 默认情况下系统指定为 DEFINER。 COMMENT ‘string’注释信息可以用来描述存储过程或函数。 存储过程体是SQL代码的内容用BEGIN…END来表示存储过程体开始和结束。 存储过程的调用 使用CALL关键字 CALL 存储过程名(参数列表); ①无参的存储过程的使用 例创建一个查看所有部门的平均工资的存储过程并调用 DELIMITER \)CREATE PROCEDURE dept_avg_sal()BEGINSELECT deptno,AVG(sal) avg_salFROM test_pro_1GROUP BY deptno;END \(DELIMITER ;CALL dept_avg_sal;-- 因为无参也可以不写括号 ②带有传出参数带有OUT的存储过程的使用 例输出工资最低的员工的工资 DELIMITER \)CREATE PROCEDURE show_min_salary(OUT ms DECIMAL(7,2))BEGINSELECT MIN(sal) INTO msFROM emp;END \(DELIMITER ;CALL show_min_salary(ms);-- 调用show_min_salary过程并将输出值赋值给ms变量。SELECT ms;-- 查看ms变量。 ③带有传入参数带有IN的存储过程的使用 例输入员工的姓名根据输入的姓名查询员工的工资 DELIMITER \)CREATE PROCEDURE show_someone_sal(IN empname VARCHAR(50))BEGINSELECT salFROM empWHERE ename empname;END \(DELIMITER ;CALL show_someone_sal(KING);-- 调用输出名字为KING的员工的工资。 ④带有输入函数与输出函数带IN和OUT的存储过程的使用 例输入员工的姓名根据输入的姓名输出员工的工资 DELIMITER \)CREATE PROCEDURE show_sal(IN emp_name VARCHAR(50),OUT emp_salary DECIMAL(7,2))BEGINSELECT sal INTO emp_salaryFROM empWHERE ename emp_name;END \(DELIMITER ;CALL show_sal(KING,es);-- 调用show_sal存储过程将输出值赋值给变量es。SELECT es;-- 查看es。 ⑤带INOUT的参数的存储过程的使用 例查询某个员工领导的姓名并用INOUT参数输入员工姓名输出领导的姓名。 DELIMITER \)CREATE PROCEDURE show_mgr_name(INOUT emp_name VARCHAR(50))BEGINSELECT ename INTO emp_nameFROM empWHERE empno (SELECT mgrFROM empWHERE ename emp_name);END \(DELIMITER ;SET James JAMES;CALL show_mgr_name(James);SELECT James; 注由于show_mgr_name存储过程不仅要对参数emp_name进行读取还要对参数emp_name进行赋值代入的参数不能是一个字符串值只能读取不能赋值而应该是一个变量。 存储过程的缺点 可移植性差、调试困难、版本管理困难、不适合高并发的场景。 存储函数 函数可以对数据进行各种处理操作提高用户对数据库的管理效率。MySQL支持自定义函数存储函数自定义的调用方式与MySQL预定义的系统函数一样。 格式 DELIMITER 新结束符 CREATE FUNCTION 函数名(参数列表) RETURNS 返回值类型 [ characteristics] BEGIN ……函数体 END 新结束符 DELIMITER ; 注 函数中所有的参数都是IN类型的。 函数体中必定包含RETURN语句。 函数体用BEGIN和END来表示函数体中的SQL语句的开始和结束。如果函数体只有一条语句可省略BEGIN和END。建议统一加上作为习惯。 [ characteristics]部分和存储过程的一致。 如果创建存储函数时出现‘you *might* want to use the less safe log_bin_trust_function_creators variable’的报错有两种方式解决 ①加上必要的函数特性“DETERMINISTIC和NOT DETERMINISTIC”与“{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }”例 DELIMITER \)CREATE FUNCTION job_by_name()RETURNS VARCHAR(50)NOT DETERMINISTICCONTAINS SQLREADS SQL DATABEGINRETURN (SELECT job FROM emp WHERE ename KING);END \(DELIMITER ; ②执行SET GLOBAL log_bin_trust_function_creators 1; 关于带参数的存储函数的创建与调用与存储过程类似 DELIMITER \)CREATE FUNCTION job_by_id(emp_id INT)RETURNS VARCHAR(50)BEGINRETURN (SELECT job FROM emp WHERE empno emp_id);END $DELIMITER ;SELECT job_by_id(empno)FROM emp; 对比存储函数和存储过程 存储函数可以放在查询语句中使用存储过程不行。 存储过程的功能更强大包括能对表的操作和事务操作。 存储过程和存储函数的查看、修改、删除 查看 ①使用SHOW CREATE语句查看存储过程和存储函数的创建信息。 SHOW CREATE PROCEDURE/FUNCTION 存储过程名/存储函数名; ②使用SHOW STATUS语句查看存储过程和存储函数的状态信息。 SHOW PROCEDURE/FUNCTION [LIKE patten]; ③从infomation_schema.Routines表中查看存储函数和存储过程的信息。 SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME 存储函数名或存储过程名 [AND ROUTINE_TYPE FUNCTION/PROCEDURE]; 修改 修改存储过程或函数不影响存储函数或存储过程的功能不能修改存储函数体或存储过程体只是修改相关特性。使用ALTER语句实现。 ALTER CREATE PROCEDURE/FUNCTION 存储过程名/存储函数名 [ characteristics]; 删除 DROP CREATE PROCEDURE/FUNCTION [IF EXISTS] 存储过程名/存储函数名;