数据库教学课件第7天

1、数据库第7天课件关于PLSQL.DEV工具代码编写时的智能感应写sql代码时，自动补全相关代码设置如下多表联合查询要点多表联合查询要解决的问题通过相应的外键，找到主表的需要的其他字段例如通过主表STUDENT的STUD_ID(学号),找到学生姓名、班级号、年龄、性别等，这些字段中的一个，或多个通过主表CLASS_REG表的CLASS_ID,找CLASS_NAME、START_TIME、END_TIME通过订单明细表的产品ID，在主表产品表中，找零售价，可以通过零售价，及购物数量，计算本条目的购物金额小计通过订单表示的客户ID，在客户表中找客户的送货地址，客户电话等基本原则外键，本表一个字段的值

2、，能代表主表一行记录的全部字段的值。因为这个字段在主键是主键，可以标识这一行数据多表关联查询的方法如果是N个表关联，则必须要找到N-1外键这N1外键，可能分布在一个表中，也可能多个表中，只要能将这N表都关联起来，就不会产生迪卡尔积。关于ORACLE常用系统函数的用法每个数据库都有一些自己特殊的函数这些函数在其他数据库不能运行这些函数的范围有日期计算字符串的计算统计计算其他计算系统日期，sysdate-系统日期select sysdate from dual;-add_months(日期字段,增加月数)select add_months(sysdate,3) from dual; -在现有日日期

3、基础，增加N个月select * from class_regselect t.*,add_months(start_time,2) starTime加2个月 from class_reg t-last_day,返回本月最后一天的日期与时间select last_day(sysdate) from dual;select t.*,last_day(start_time) starTime当月最后天 from class_reg t-MONTHS_BETWEEN(日期1，日期2) 两个日期之间的月个数，小数点表示select MONTHS_BETWEEN(sysdate,to_date(2013

4、-5-1,yyyy-mm-dd) from dual;-相关日期计算，求2个日期之间天数，月数，年数，均有计算公式字符串计算-字符串计算select abc|处理 from dual;-求字符串长度 ,length(字符串变量)select length(abc|处理) from dual;select * from studentselect length(name)姓名长度,t.* from student t-求子字符串,substr(字符串变量,starIndex,字符个数) startIndex从1开始,字符串个数省略，表示求余下全部子字符串select substr(abc|处理,

5、3) from dual;select substr(name,2),t.* from student tselect length(abc|处理) 长度 from dual; -长度以字符为最小单位select length( abc|处理 ) 长度 from dual; -长度以字符为最小单位select ltrim( abc|处理 ) 清除左空格 , abc|处理 字符原样 from dual;select rtrim( abc|处理 ) 清除右空格 , abc|处理 字符原样 from dual;select trim( abc|处理 ) 清除左右空格 ,length(trim( ab

6、c|处理 ) 长度, abc|处理 字符原样 from dual;-upper 转大写select upper(rtrim( abc|处理 ) 清除右空格 , abc|处理 字符原样 from dual;-lower 转不写select lower(rtrim( aBC|处理 ) 清除右空格 , abc|处理 字符原样 from dual;CHR 按数据库的字符集由数字返回字符 CONCAT(c1,c2) 把两个字符c1,c2组合成一个字符, 和 | 相同 REPLACE(c,s,r) 把字符c里出现s的字符替换成r, 返回新字符 SUBSTR(c,m,n) m大于0,字符c从前面m处开始取n

7、位字符,m等于0和1一样, m小与0,字符c从后面m处开始取n位字符 TRANSLATE(c,f1,t1) 字符c按f1到t1的规则转换成新的字符串 INITCAP 字符首字母大写,其它字符小写 LOWER 字符全部小写 UPPER 字符全部大写 LTRIM(c1,c2) 去掉字符c1左边出现的字符c2 RTRIM(c1,c2) TRIM(c1,c2) 去掉字符c1左右两边的字符c2 LPAD(c1,n,c2) 字符c1按制定的位数n显示不足的位数用c2字符串替换左边的空位 RPAD(c1,n,c2)逻辑比较函数有逻辑比较的函数NVL(EXPR1, EXPR2)函数 解释: IF EXPR1=

8、NULL RETURN EXPR2 ELSE RETURN EXPR1 DECODE(AA0V10R10V20R2.)函数 解释: IF AA=V1 THEN RETURN R1 IF AA=V2 THEN RETURN R2 . ELSE RETURN NULL 举例: decode(id,1,dept sale,2,dept tech)-求一位同学多门课考试成绩中的最高分与最低分数据库的SQL语言分类DML语言sql语句的增、删、改、查DDL语言数据定义语言，通常有关键词create /drop table之类alter table column add/modify/drop name

9、varchar2(20)DCL语言数据库系统控制，包括权限控制create user grant dba,connect,resource to studsysdrop useralter user studsys account 。关于多个查询的结果集处理并集UNION(去掉重复记录) 并集UNION ALL(不去掉重复记录) 差集MINUS, 交集INTERSECT 关于ER图采用可视化方式，表示一个数据库的设计ER 图=Entity Relationship 图 实体 关系 图(的形式，表示数据库设计)采用PowerDesigner15进行的数据表设计这是sybase数据库提供的一个设计

10、工具，主要设计CDM，Concept Design Model这个设计适用于任何数据库PDM,Physical Design Model绑定了某个具体的数据库这个ER图设计完成后，可以直接生成数据库.sql文件数据库流字段类型了解一个字段的值特别大时，兴许有几百兆，或更大，此时采用流作为数据类型流作为数据类型BLOB二进制流，在所有数据库中均采用blob用java的流对数据作读、写操作，字节流CLOB字符流在mssql中有text表示用java的流对数据作读、写操作，字符流图片bfile-oracleimage-mssqlORACLE存储过程设计数据训的存储过程只适用每个数据库自身过程的定义，

11、procedure类比java类中的方法，在此称为过程最大的特点不通用也就是Oracle的存储过程，不能在MSSQL中运行存储过程的作用在数据库后台，用一个己编绎好的过程，或函数，对数据表作增、删、改、查操作运行的效率比较高前台只需要调用这个过程即可过程的工作框架在过程中写一个过程，可以向导在定义过程时，varchar2(nn),只需要写为varchar2,即可变量赋值时，要用name:=名称要在建立过程如果这个变量有返回值，则必须先声明变量名称过程没有参数时，无括号一个参数，既可能是输入，也可以是输出示例如下create or replace procedure MyProc2(userId

12、 in out varchar2, name in out varchar2) isbegin dbms_output.put_line(userId=| userId|,name=| name); userId:=new UserId; name:=张小红; end MyProc2;create or replace procedure TestMyProc is 无参数时，无括号begin dbms_output.put_line(这是一个简单的存储过程，没有任何参数,是我吗-888899); -等效于java中的System.out.println(.) end TestMyProc;运

13、行方法如下，可以在3个窗口中运行sql窗口，必须有程序体declare userid varchar2(20); name varchar2(20); begin userId:=lifangId; name:=李芳; MyProc2(userid,name); dbms_output.put_line(-userId=| userId|,-name=| name); end;在sqlwindow中运行begin TestMyProc();end;-关于过程运行begin dbms_output.put_line(这是一个简单的存储过程，没有任何参数-);end; 在命令窗口中运行需要设置输出

14、set serverout onexe 过程名称(参数1，参数2，参数n);-SQL set serverout on;SQL exec TestMyProc(); 这是一个简单的存储过程，没有任何参数 PL/SQL procedure successfully completedSQL declare 2 userid varchar2(20); 3 name varchar2(20); 4 5 begin 6 userId:=lifangId; 7 name:=李芳; 8 MyProc2(userid,name); 9 dbms_output.put_line(-userId=| user

15、Id|,-name=| name); 10 end; 11 / userId=lifangId,name=李芳-userId=new UserId,-name=张小红 PL/SQL procedure successfully completed SQL存储过程的调试,第3种方式运行在调试窗口中进行在此窗口输入过程代码与sql窗口运行的代码完全相同，但可以单步调试操作如下建一个test窗口编写调试代码单步运行操作示例事务处理概念日常生活的事务日常生活的事务，事务繁忙定义是执行某个任务，或业务，要涉及到数据库多个相互关联的操作，这些操作要么全部成功，要么全部失败，如果失败，则回到原始状态。解释一

16、个事务，是一个任务，或一个业务不是一个项目，更不是一个工程兴许在一个同类的数据库中，也兴许不在同一个类数据库中事务的属性，或性质，4个，ACID原子性，A,Atomicty，多个操作合起来是一个最小单位表示事务的多个操作是一个整体项，不能再细化，是一个最小单位一致性，C,Consistency，按计划，无错误表示事务的多个操作没有错误发生，全部按计划进行隔离性,I,Isolation，并发多个事务同时进行时，本事务不受到影响持久性，D，Durability表示事务一旦完成，不能撤消，或反悔，不能再回到原始状态关于事务的编程Oracle必须在一个procedure中进行每一段必须一程序体begi

17、n -在这个之间写plsql的语言代码end;异常处理代码exception when othersbegin rollback; 提示输出end;必须涉及数据表的2个或以上的新增、修改、删除项操作这个数据表可以相同，或不相同示例如下create or replace procedure MyTransection isbegin begin update study set score=146 where stud_id=10000 and course_id=1; insert into study values(10000,5,145.678); commit; -表示要么全部成功 db

18、ms_output.put_line(事务运行成功！); end; exception when others then begin rollback;-表示要么全部失败 dbms_output.put_line(事务运行失败,执行了回滚操作！); end;end MyTransection;运行command windowSQL exec MyTransection; 事务运行成功！ PL/SQL procedure successfully completed SQL在sql window，或test window代码相同begin MyTransection;end;在output窗口观察输出ORACLE数据备分与恢复备分的工具是exp.exe文件恢复的工具是imp.exe文件需要写脚本运行操作流程新建一个用户对新用户授权导出原有用户数据将导出的数据，导入新用户中示例代码如下新建用户SQL SQL create user testnew identified by orcl; User created SQL grant dba,connect,resource to testnew; Grant succeeded导出数据pauseexp studsys/o