第一章 Oracle概述 Oracle服务器由Oracle 数据库和 Oracle 实例组成 Oracle实例由系统全局区内存结构和用于管理数据库的后台进程组成 Oracle中用于访问数据库的主要查询工具有 SQL*Plus、iSQL*Plus 和 PL/SQL Oracle企业管理器是用于管理、诊断和调整多个数据库的工具 Oracle中的SYSTEM用户和SYS用户具有管理权限,而 SCOTT 用户只有基本的权限 PL/SQL是sql的一种扩展语言 Grant 命令为用户分配权限或角色 授予权限: 系统权限:grant succeeded(succeeded表示系统权限,如建库、建表) 对象权限:grant select on emp to s1;(对象权限表示一个用户有哪些操作,用户能否访问其他表) 授予连接权限:grant connet to s1;(connet角色允许用户连接至数据库) 授予常用操作权限:grant resoure to s1;(resoure角色允许用户使用数据库中的存储过程) 第二章 SQL函数 SQL 是数据库语言,Oracle 使用该语言在数据库中存储和检索信息 Oracle 支持各种数据类型,如 VARCHAR2、NUMBER、LONG、RAW 和 DATE 等 数据操纵语言用于查询和修改表中的数据 事务控制语言管理事务的一致性 SQL 操作符包括算术、比较、逻辑、集合和连接操作符 SQL 函数可以大致分为单行函数、聚合函数和分析函数 SQL 是通用的数据库语言 SQL 命令可分为数据定义语言、数据操纵语言、事务控制语言和数据控制语言 Oracle 支持的数据类型包括字符、数值、日期时间、RAW 和 LOB 等 SQL 支持的操作符包括算术、比较、逻辑、集合和连接操作符 SQL 函数可大致分为单行函数、聚合函数和分析函数 create user s1 identified by s1; --创建新用户 create table abc(id number, name varchar2(8), birthday date, text clob); --创建表 create sequence s_sa; --创建序列 show user --当前用户 conn system --切换其他用户 SQL 支持下列类别的命令: 数据定义语言(DDL) 数据操纵语言(DML) 事务控制语言(TCL) 数据控制语言(DCL) 序列: create sequence s_a --创建序列 select s_a.textval from dual --系统自带的一个表,只有一行一列 Cycle 最大值/最小值是否循环 insert into a values(s_a.nextval,'bb') --向表a中插入数据 select s_a.currval from dual ---当前序列的值是多少 select s_a nextVal from dual ---查看序列里的下一个值 数据类型 日期数据类型的默认格式为“DD-MON-RR” 插入来自其它表中的记录:insert into <table_name> [(cloumn_list)] select column_names FROM <other_table_name>; ed 编辑上一条命令 / 重新执行命令 savepoint 保存一个回滚点 rollback 回滚点 commit提交 select id || 'my' from a 使用连接操作符,可以将表中的多个列合并成逻辑上的一行列 SQL 操作符的优先级从高到低的顺序是: 算术操作符 --------最高优先级 连接操作符 比较操作符 NOT 逻辑操作符 AND 逻辑操作符 OR 逻辑操作符 --------最低优先级 decode : 字符替换 select decode(name,'cc','cccc',name) name from a 转换日期函数: SELECT TO_CHAR(sysdate,'YYYY"年"fmMM"月"fmDD"日" HH24:MI:SS')FROM dual; //查询早上出生的 Select * from admin_userInfo where to_number(to_char(birthday,’hh24’))<12; 替换:NVL Select nvl(comm,0) from emp; 查找员工一年的奖金: select(nvl(comm,0)+sal)* 12 from emp; select sal*12 + nvl(comm,0) from emp; case, when , then 的使用 查找用户名,分数,是否及格 select * from user_score; select username,score,(case when score>=60 then ’及格’ else score<60 then ’不及格’ end)’是否及格’ from user_score; 子查询 查询一行使用 =号,查询单行单列,返回一个值 select * from emp where sal=(select max(sal) form emp); //查找工资最高的 返回多个值使用in select empno,ename,sal,deptno from emp where sal in(select max(sal) from emp group by deptno); select * from emp e join (select max(sal) m, deptno from emp group by deptno) s on e.sal=s.m and e.DEPTNO = s.deptno; //查询每个公司里的员工最高工资的信息 数据库类型 伪列rownum:是在得到结果集的时候产生的,它是逐次递加的。 只有有rownum=1的记录,才可能有rownum=2的记录 在rownum=1,或者rownum<=n(n>1)的时候,没有问题,但无法取得rownum>n的值 mySQl分页: Linit2,3; //从哪里2起查3多少条数据 Select * from emp limit 2,3;//从第二条开始查3条数据 Select ename from emp where rownum<3; //结果集的时候才产生 Select ename from emp where rownum>3 and rownum<6; //查询不到数据 使用rownum来执行分页: select ename from(select ename,rownum r from emp)where r>3 and r<6;//根据子查询 ——-- (select ename,rownum r from emp)查找出来的是个临时表 //查找部门平均薪水最高的值(显示平均薪水) select max(sal) from (select avg(sal) sal from emp group by deptno); //查找部门平均薪水最高的值(显示部门编号和平均薪水) select * from(select avg(sal)s,deptno from emp group by deptno) where s =(select max(s) from (select avg(sal)s from emp group by deptno)); //查找学生,科目,成绩 select y.username as "学生", y.score as "语文",s.score as "数学" from (select * from user_score where subjectname='语文')y join (select * from user_score where subjectname='数学')s on y.username = s.username; 选择无重复的行 在SELECT子句,使用distinct关键字 //查找mydept表的部门名称和myemp表的d-ip人数。统计部门和人数 select distinct mydept.devName as "部门", (select count(*) from myemp where gender='male' and d_ip =mydept.id)"男", (select count(*) from myemp where gender='female' and d_ip =mydept.id)”女" from myemp join mydept on myemp.d_ip = mydept.id//最后一句可以换成这样, from mydept 好处:可以消除笛卡尔积; 第三章 锁和表区分 锁用于保护多用户环境下被修改的数据 锁分为两种级别,即行级锁和表级锁 表分区允许将一个表划分成几部分,以改善大型应用系统的性能 分区方法包括范围分区、散列分区、复合分区和列表分区 分区维护操作包括添加、删除、截断、合并和拆分分区 第五章 使用 PL/SQL set server output on/Off; PL/SQL主要用来写存储过程,触发器,包程序 优化数据库:建索引。索引是与表相关的一个可选结构,用于提高 SQL 语句执行的性能 PL/SQL 是过程语言(Procedural Language)与结构化查询语言(SQL)结合而成的编程语言 PL/SQL 是对 SQL 的扩展 支持多种数据类型,如大对象和集合类型,可使用条件和循环等控制结构 可用于创建存储过程、触发器和程序包,给SQL语句的执行添加程序逻辑 与 Oracle 服务器和 Oracle 工具紧密集成,具备可移植性、灵活性和安全性 PL/SQL分为三个部分: 1. 声明部分(declare) 2. 可执行部分(begin) 3. 异常处理部分(exception) 示例: Declare myname varchar2(8);//声明变量 Begin myname := ‘琴琴’; //赋值 dbms_output.put_line(myname); //打印输出 exception when others then dbms_output.put_line(‘出错了:’||sqlerrm); end; / 变量赋值: := 常量赋值: 使用select into 语句 PL/SQL 支持的内置数据类型 数据类型: 标量类型 LOB类型 属性类型:%TYPE(提供数据库某列的数据库类型,表中一列数据类型) %ROWTYPE(提供表中一行的记录类型,表中的一行数据类型) 示例: //表中一列的数据类型 declare myname emp.ename %type; begin select ename into myname from emp where empno = 7369; dbms_output.put_line(myname); //表中一行的数据类型 declare var1 emp.ename %rowtype; Begin Select ename into var1 from emp where empno = 7369; dbms_output.put_line(var1); 复合类型:table:类似数组, Record:类似一个类 index by:下标增长 binary_integer:是一种整型数据类型,用来计数的。 示例: 控制结构:条件控制:if语句、case语句 循环控制:loop循环,while循环、for循环 顺序控制 If语句根据条件执行一系列语句,有3种形式:If-then if-then-else if-then-elsif 示例: If irate > 200 then Update emp set sal = sal – 200 Where deptno = icode Else End if; End; 循环控制有3种类型:loop----无条件循环 while-----根据条件循环 for---循环固定的次数 示例:循环打印输出1.到.9 declare j number :=1; begin while j<10 loop dbms_output.put_line(j); j := j+1; end loop; end; 循环打印输出1.到.10 begin for k in 1..10 loop dbms_output.put_line(k); end loop; end; 错误处理-------异常 异常分为: 预定义异常:当 PL/SQL 程序违反 Oracle 规则或超越系统限制时隐式引发 用户定义异常raise关键字:用户可以在 PL/SQL 块的声明部分定义异常,自定义的异常通过 RAISE 语句显式引发 引发应用程序错误的语法: Paise_application_error(error_number,error_message); 第六章 游标 游标的类型:显示游标 ,隐式游标 , Ref 游标, 隐式游标: 显示游标:在PL、SQL块的声明部分定义查询,该查询可返回多行 显示游标的操作过程:声明, 打开游标,关闭游标, 示例: 声明游标:cursor toy_cur is Select toyprice from toys Where toyprice<250; //声明游标 Begin open toy_cur; //打开游标 Loop fetch toy_cur into my_toy_price; //提取行 exit where toy_cur%notfound; //退出条件有数据才打印输出 dbms_output.put_line(‘打印语句….’); //遍历游标 End loop; Close toy_cur;//退出游标 Ref 游标:在执行时才给他值 示例: declare sa emp%rowtype; type cu is ref cursor; c cu; begin open c for select * from emp; //在执行时给数据 loop fetch c into sa; exit when c%notfound; dbms_output.put_line(sa.job); dbms_output.put_line(sa.sal); end loop; close c; end;