数据库笔记.doc

第一部分ORCAL数据库要点详解第1章 oracle介绍1. orcale的启动顺序：a.创建并启动oracle实例b.创建数据库和服务线程c.打开数据库2. oracle中的物理结构：分为三种数据文件（dbf）控制文件日志文件实例：为后台创建内存空间和进程内存空间：SAG（system globe area）3. sys和system的区别：A. 存储的数据不同，sys管理的是系统文件数据，system管理的只是普通数据B.角色不同，sys拥有dba和sysoper两种角色；system只有normal角色4. oracle中的启动方式：sqlpluswsqlplus/nolog浏览器方式：http:/localhost:5560/isqlplus开启oracle oradb10-TLlistner数据库的登录：conn scott/tiger f服务器连接字符串 as 连接方式5. 用户连接身份：sysdbasysopernormalsysdba： 即数据库管理员，权限包括：打开数据库服务器、关闭数据库服务器、备份数据库、恢复数据库、日志归档、会话限制、管理功能、创建数据库。sys用户必须用sysdba身份才能登录，system用户可以用普通身份登录。 sysyoper：即数据库操作员，权限包括：打开数据库服务器、关闭数据库服务器、备份数据库、恢复数据库、日志归档、会话限制。 normal：即普通用户，权限只有查询某些数据表的数据。默认的身份是normal用户。 6. 服务器连接字符串：连接字符串中包括连接服务器的协议，服务器的地址，服务器的端口等设置，Oracle服务名等，该配置文件在Oracle安装目录下的： network/ADMIN/ tnsnames.ora7. ed命令：调出上一sql并修改，保存修改后用“/”执行修改后（sql）命令。注意,在命令窗口不会执行缓存命令，如 desc8. 新建用户：create user scott indetified by tiger acount unlock;9. 用户角色主要有三种:connect resuorce dbasystem同时拥有connect 和 resuorce 两角色Resuorce角色可以创建数据库对象。角色授权：grant all on dept to scott;权限移除：revoke delte on dept from scott;10. 修改用户的密码语法是： ALTER USER 用户名 IDENTIFIED BY 新密码。 修改用户处于锁定（非锁定）状态 ALTER USER 用户名 ACCOUNT LOCK|UNLOCK 第二章SQL数据操作和查询1.Oracle SQL语句由如下命令组成： 数据定义语言（DDL），数据操纵语言（DML），数据查询语言（DQL），事务控制语言（TCL）数据控制语言（DCL）2. 数据类型：Char：，如果存储的字符串长度小于length，用空格填充。默认长度是1，最长不超过2000字节Varchar2：length指定了该字符串的最大长度。默认长度是1，最长不超过4000字符。Number(p,s)：p表示最大位数，s表示小数位数Date : to_date(1992-12-3,yyyy-mm-dd);Clob：存储大的文本，如非结构化的xml文档。Blob：存储二进制对象，如图形视屏、声音获取当前系统时间：select sysdate from dual;dual是续表，默认值为x3. 数据完整性：A.主键：一个表中只能有一个主键，这个主键可以由多个列构成B.数据完整性表现在：精确性+可靠性 包括实体完整性：主键唯一约束 域完整性：字段、域、数据类型 check 、default 引用完整性：外键约束创建约束：alter table 表名 add constraint 约束名 约束内容 Alter table infos add constraint pk_infos primary key(stuid); Alter table infos add constraint ck_infos_gender check(gender in (a,b)4. 表的创建、复制、插入根据结果集创建表：create infos as select * from infos;复制表结构：create table infos2 as select * from infos where 1=1;向表中插入一个常量结果集：insert into infos select s10006,scott,man,to_date( 2009-8-9 08:00:10,yyyy-mm-dd hh24:mi:ss ) from dual;5. 更新数据:update infos set classno=1002,stuaddress=losgels where stuname=xiaoli;6. 删除数据：delete from infos where stuid=s1000103;7. Truncate用法: truncate table table_name;Truncate 和delete区别：truncate(DDL)后数据不能恢复；delete（DML）删除后可以通过日志文件恢复，truncate删除速度快8. 消除重复行：select distinct deptno from emp;9. 空值查询：select ename,job ,comm from emp where comm is null;10. In操作： where job in(salman,president,analyst);11. Like模糊查询：%表示0个或多个字符，_ 表示一个任意字符12. 集合运算：insect 交集、union all 并集（包括重复记录）、union(不包括重复记录)、minus（补集）包含a但不包含b13. 内连接（inner join）和外连接（outer join）14. Rowid和rownub：rowid是数据存储的物理地址，rownub是行号分页查询：select * from (select rownum r,emp.* from emp) where r0;15. any子查询和all：Any是返回结果集中的任意一个元素，大于时表示取结果集中最小，小于时取最大All 比较的是结果集中的所有元素，大于时比较结果集中最大值，小于时比较最小值16. 聚合函数：maxminsumanycount17. Group by、order by、having用法：在查询时若有非分组列，那么该列必须出现在group by字句中，分组函数的条件筛选放在having字句后，普通条件放在where 条件后即可。注意having中的聚合函数不能使用别名。19.18道经典查询题1列出至少有一个员工的所有部门。 2列出薪金比“SMITH”多的所有员工。 3列出所有员工的姓名及其直接上级的姓名。 4列出受雇日期早于其直接上级的所有员工。 5列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。 6列出所有“CLERK”（办事员）的姓名及其部门名称。 7列出最低薪金大于1500的各种工作。 8列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。 9列出薪金高于公司平均薪金的所有员工。 10列出与“SCOTT”从事相同工作的所有员工。 11列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。 12列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。 13列出在每个部门工作的员工数量、平均工资和平均服务期限。 14列出所有员工的姓名、部门名称和工资。 15列出所有部门的详细信息和部门人数。 16列出各种工作的最低工资。 17列出各个部门的MANAGER（经理）的最低薪金。 18列出所有员工的年工资,按年薪从低到高排序。1.select count(emp.empno) nub,dept.deptno from dept left join emp on emp.deptno=dept.deptno having count(emp.empno)0 group by emp.deptno;2.select * from emp where emp.sal(select emp.sal from emp where emp.ename=SMITH);3.select e1.ename employee,e2.ename boss from emp e1 left join emp e2 on e1.mgr=e2.empno; 4.select e1.ename employee,e2.ename boss,e1.hiredate employee_date,e2.hiredate boss_date from emp e1 left join emp e2 on e1.mgr=e2.empno and e1.hiredate1500;8. select e.ename from dept d,emp e where d.dname=SALES and e.deptno=d.deptno;9.select e.sal ,e.ename mysal from emp e,(select avg(emp.sal) mysql from emp) t where e.salt.mysal ;10.select * from emp where job=(select job from emp where emp.ename=SCOTT);11.select * from emp where sal in(select emp.sal from emp where emp.deptno=30);12.select * from emp where salall(select sal from emp where emp.deptno=30);13.select dept.deptno, count(ename) 员工数,avg(sal)平均工资,avg(sysdate-emp.hiredate) 平均服务期限 from dept left join emp on dept.deptno=emp.deptno group by dept.deptno;14.select emp.ename,emp.sal,dept.dname from emp,dept where emp.deptno=dept.deptno;15. select dept.dname,dept.loc ,t.* from (select dept.deptno, count(ename) 人数 from dept left join emp on emp.deptno=dept.deptno group by dept.deptno)t,dept where dept.deptno=t.deptno;16.select emp.job, min(sal) from emp group by emp.job;17.select emp.*,t.* from (select emp.deptno, min(sal) from emp where emp.ename=MANAGER group by emp.deptno)t,emp where emp.deptno=t.deptno;18. select emp.*, emp.sal*12+nvl(m,0) 年薪 from emp order by 年薪 asc;/*A.求部门平均薪水的等级。*/select dsal.* ,sg.grade from (select emp.deptno,avg(emp.sal) mysal from emp ,salgrade sg group by emp.deptno) dsal,salgrade sg where dsal.mysal between sg.losal and sg.hisal;/*B.求部门平均的薪水等级*/select t.deptno, avg(t.grade) from (select e.sal,sg.grade,e.deptno from emp e,salgrade sg where e.sal between sg.losal and sg.hisal) t group by t.deptno;/* C.那些人是经理*/select e.ename from emp e where e.job=MANAGER;/*D.不准用聚合函数，求薪水的最高值（面试题）*/select rownum ,t.* from (select emp.* from emp order by emp.sal desc) t where rownum2;/* E.平均薪水最高的部门编号 */select tt.*,rownum from (select emp.deptno, avg(sal) from emp group by emp.deptno order by avg(sal) desc) tt where rownum2;/* F.平均薪水最高的部门名称*/select ttt.*,d.dname from (select tt.*,rownum from (select emp.deptno, avg(sal) from emp group by emp.deptno order by avg(sal) desc) tt where rownum2) ttt,dept dwhere ttt.deptno=d.deptno;/* G.求平均薪水的等级最低的部门的部门名称*/select tt.* from (select dsal.* ,sg.grade from (select emp.deptno,avg(emp.sal) mysal from emp ,salgrade sg group by emp.deptno) dsal,salgrade sg where dsal.mysal between sg.losal and sg.hisal order by sg.grade asc) tt where rownum900 THEN UPDATE emp SET comm=800 WHERE ename=JAMES; ELSE UPDATE emp SET comm=400 WHERE ename=JAMES; End if; END;DECLARE （case语句结构）v_grade CHAR(1):=UPPER(&p_grade); BEGIN CASE v_grade WHEN A THEN dbms_output.put_line(Excellent); WHEN B THEN dbms_output.put_line(Very Good); WHEN C THEN dbms_output.put_line(Good); ELSE dbms_output.put_line(No such grade); END CASE;END;DECLARE （while循环语句）counter number(3):=0; sumResult number:=0; BEGIN WHILE counter100 LOOP counter :=counter+1; sumResult := sumResult+counter; END LOOP; dbms_output.put_line(result is :|sumResult); END;（）DECLARE （for循环语句） counter number(3):=0; sumResult number:=0; BEGIN FOR counter IN 1.100 LOOP sumResult :=sumResult+counter; END LOOP; dbms_output.put_line(result is :|sumResult); END; 6.PL/SQL中动态执行SQL语句EXECUTE IMMEDIATE 动态语句字符串 INTO 变量列表 USING 参数列表 示例：sql_stmt := INSERT INTO dept VALUES (:1, :2, :3);EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location; 在ORACLE中的insert，update，delete语句都可以使用returning字句把操作影响的行中的数据返回。例如：EXECUTE IMMEDIATE sql_stmt USING emp_id RETURNING INTO salary; 7.自定义异常(两个关键点)：异常定义：采用EXCEPTION关键字声明异常，例如：myexception EXCEPTION;异常引发：在程序可执行区域，使用RAISE关键字进行引发.例如：RAISE myexception8. 引发系统级异常：oracle中为用户预留了自定义异常码，范围从-20000到-20999语法：RAISE_APPLICATION_ERROR（异常码，异常信息）， 例如：RAISE_APPLICATION_ERROR(-20001,SAL IS TO LESS!); 如果要处理未命名的内部异常，必须使用OTHERS异常处理器，也可以利用 PRAGMA EXCEPTION_INT把一个异常吗与异常名绑定。例如： DECLARE null_salary EXCEPTION; PRAGMA EXCEPTION_INIT(null_salary, -20101); 第7章 子程序和包1. 子程序：子程序是已经命名的PL/SQL块，它们编译后存储在数据库中可以随时从任何数据库客户端和应用程序中调用，同样具有声明部分、可执行部分、异常处理部分声明部分包含类型、游标、常量、异常声明。命名的PL/SQL程序包括过程和函数。 优点：模块化，可重用性，安全性。2. 过程：过程就是命名的PL/SQL程序库，编译后存储在数据库中，可以被调用 语法：CREATE OR REPLACE PROCEDURE PRO_NAME (过程参数列表)IS|AS-本地变量和常量声明在IS或AS后定义（过程没有DECLARE语句）BEGIN-可执行部分END;3. 过程的几种常用形式：a) 不带参数的存储过程b) 带参数的存储过程，参数用小括号SQLPLUS提示符下过程的调用：例如 EXECUTE updateSal(0.1,7499) 4. 过程中的三种参数模式：IN,OUT,IN OUT IN：默认模式参数只能由外界属兔，不能在过程中对IN参数重新赋值，可以有默认值 OUT：不能有默认值，不能接收数据，在过程中对OUT参数赋值后可以被调用者捕获 INT OUT:可以接收参数，也可以赋值，可被捕获。只能用变量作为参数，不能有默认值5. 函数：也是数据库中已命名的PL/SQL块，但是必须要返回一个默认值。可以被重载 语法：CREATE OR REPLACE FUNCTION function_name(参数列表) RETURN 数据类型IS|AS-变量，参数，异常等的声明部分BEGIN-可执行部分RETURN 返回参数;EXCEPTION-异常声明部分END;6. 函数的调用：SELECT语句调用：SELECT getTax(7499,1200) FROM DUAL;注意：如果函数内容中包含DML（会更新数据0）操作时有以下限制： 不能在SELECT语句中调用包含DML操作的函数。 不能在INESRT语句的values调用包含DML操作的函数。 不能在UPDATE语句的SET值中调用包含DML操作的函数7. 过程和函数的比较：过程 函数 可以作为PL/SQL语句执行。直接调用。 作为表达式的一部分进行调用。 声明中不包含RETURN语句。 必须在声明中包含RETURN语句。 不返回任何值。 必须返回一个值。 可以使用RETURN语句表示函数终止。RETURN后面不能有表达式。 必须包含至少一个RETURN语句，并且RETURN后面必须是一个表达式。 8. 自主事务：一个过程中的事务与调用者在同一个事务中，这样在过程中提交或者回滚事务时，调用者调用前对数据库的处理也同时会提交或者回滚。当一个子程序调用另一个过程时，任何提交或回滚语句均将影响这两个子程序中的事务处理。 自主事务定义的方法：使用编译指示命令：PRAGMA AUTONOMOUS_TRANSACTION，放在声明部分 9. 程序包：它对相关的PL/SQL类型、子程序、游标、异常、变量和常量的封装。包含两部分内容：程序包规范和程序包主体。在程序包规范中，可以声明类型、变量、常量、异常、游标和子程序，包规范中声明的内容全部都是公共的。程序包主体用于实现在程序包规范中定义的游标、子程序等，同时在程序包主体中可以声明局部变量和私有过程，私有过程只能在同一个包内部调用，外部无法调用。 包规范语法：CEREATE OR REPLACE PACKAGE 包名IS|AS-公共类型声明、常量、变量、异常和游标等声明 -子程序声明（只包含过程或函数的定义，并不实现）END;包主体语法：CREATE OR REPLACE BODY 包名-IS|AS私有声明：变量、常量、游标、异常和类型-BEGIN包的初始化语句-END;10.程序包的作用：Oracle包将相关的功能在逻辑上组织在一起，包比单独的过程更具有强大的优势：模块化，更轻松的程序设计，信息隐藏，子程序重载 第二部分JDBC数据库连接1. 使用jdbc连接数据方法：1. 加载数据库驱动：Class.forName(oracle.jdbc.driver.OracleDriver);2. 获取连连接:Connection conn=DriverManager.getConnection(url,user,pwd)；String url=jdbc:oracle:thin:ip:1521:sid;3. 通过数据库对象Connection获取Statement对象：Statement st=conn.createStatement();4. 使用Statement对象执行SQL语句；Int r=st.executeUpdate(sql);/注意executeUpdate方法执行的是insert、update、delete5. 操作结果集：使用ResultSetMetaData对象ResultSet rs=st.excuteUuery(sql);ResultSetMetaData rsm=rs.getMetaData();ResultSetMetaData中三个重要方法：rsm.getColumnCount();/获取结果集中有多少列 rsm.getColumnName(column);/获取结果集中列名rsm.getColumnTypeName(column)；/获取结果集中列的数据类型名2.利用PreparedStatement为sql语句填充参数String sql=insert into emp values(?,?,?,?,?,?,?,?);preparedStatement pst=conn.preparedStatement();Pst.execute(sql);pst.set(1,arg0);pst.setString(2,arg1);3.statement和preparedState4.辅助类操作：Public int executeSQLHelp(String sql,Object.params)Conn=DriverManager.getConnection(url,user,pwd);preparedStatement pst=conn.preparedStatement(sql);If(params!=null)For(int i=0;iparams.length;i+)st.setObject(i+1,paramsi);5.调用存储过程：callableStatementcs=conn.prepareCall(call pro_check_hasemp(?,?);.s.setInt(1, deptno);/设置第一个参数cs.registerOutParameter(2, OracleTypes.NUMBER);/注册输出参数cs.execute();/执行flag = cs.getInt(2);/获取存储过程out类型参数返回值6.使用事务提交和批处理：conn.setAutoCommit(false);Ps=conn.preparedStatement(sql);Ps.setD