网站开发基本构成,七台河新闻联播最新,企业销售型网站是什么,网站空间免文章目录第一章 数据库概念数据库的三级模式结构#xff1a;模式、外模式、内模式三级模式之间的映射第二章 Oracle12g体系结构Oracle的逻辑存储结构Oracle物理存储结构Oracle11g服务器结构系统全局区#xff08;SGA#xff09;程序全局区#xff08;PGA#xff09;第三章…
文章目录第一章 数据库概念数据库的三级模式结构模式、外模式、内模式三级模式之间的映射第二章 Oracle12g体系结构Oracle的逻辑存储结构Oracle物理存储结构Oracle11g服务器结构系统全局区SGA程序全局区PGA第三章 Oracle数据库的安装与配置第四章 SQLPLUS命令第五章 SQL语言基础及调优为列指定别名比较筛选使用特殊关键字筛选in关键字Between 关键字分组查询多表关联查询内连接左外连接右外连接自然连接自身连接常用函数子查询多行子查询使用 ANY查询使用ALL查询关联子查询第六章 PL/SQL编程PL/SQL游标SqlPlus column命令用法触发器例题选择题填空题判断题简答题设计题实验一实验二实验三第一章 数据库概念
1数据模型通常由数据结构、数据操作和完整性约束3部分组成的 2常见的数据模型 层次模型、网状模型和关系模型 1层次模型用树型结构表示实体类型及实体间联系的数据模型为层次模型 2网状模型用有向图结构表示实体类型及实体间联系的数据模型称为网状模型 3关系模型用二维表描述数据。 关系模型的基本术语
关系一个二维表就是一个关系
元组二维表中的一行即表中的记录。
属性: 二维表中的一列用类型和值表示
域每个属性取值的变化范围关系模型的完整性规则是对关系的某种约束条件。
实体完整性约束
参照完整性约束关系之间的基本约束。
用户自定义的完整性约束数据库系统由以下部分的构成 1数据库 2数据库管理系统 3应用系统 4数据库管理员 5用户
关系操作包括查询操作和更新操作: 查询操作 选择、投影、连接、除、并、交、差 更新操作 增加、删除、修改
命令 1、连接命令
conn 用户名/密码
或者 conn网络服务名称2、断开
disc[onnect] 用户名/密码3、修改密码
passw[ord] 自身
更改低级用户密码alter user 用户名 identity by 新密码4、创建用户并设定密码
create user 用户名 identity by 密码5、删除用户
drop user 用户名 [cascade] ----用户下建表情况下使用6、关闭数据库实例
shut down by 密码7、启动数据库
start up数据库的三级模式结构模式、外模式、内模式
1模式称为逻辑模式或概念模式是对数据库中全体数据的逻辑结构和特征的描述是所有用户的公共数据视图。一个数据库只有一个模式。
2外模式外模式是模式的子集。外模式也称为用户模式它是数据库用户能够看见和使用的对局部数据的逻辑结构和特征的描述是数据库用户的数据视图。
3内模式内模式也成为存储模式一个数据库只有一个内模式。
三级模式之间的映射
外模式/模式映射 同一个模式可以有任意多个外模式。对于每一个外模式数据库系统都有一个外模式/模式映射。
模式/内模式映射 数据库只有一个模式和外模式即模式/内模式映射是唯一的
关系是一种规范化的二维表格它具有以下特性 属性值具有原子性不可分解 没有重复的元组即没有重复的行
键的类型 超键在一个关系中能唯一标识元组的属性或属性集称为关系的超键。 候选键如果一个属性集能够唯一标识元组且又不含有多余的属性称为关系的候选键。
关系型数据库的设计范式 第一范式属性唯一在数据表中的字段都是单一的不可再分的 第二范式不存在部分函数依赖 第三范式不存在传递函数依赖。
第二章 Oracle12g体系结构
三个概念去理解实例、数据库、数据库服务器
Oracle的逻辑存储结构
Oracle数据库是由多个表空间组成而表空间又是有多个段组成段由多个数据区组成数据区由多个数据块组成。
一数据块 数据块是Oracle逻辑存储结构中的最小的逻辑单位也是执行数据库输入输出操作的最小存储单位。 Orale服务器以数据块为单位管理数据文件的存储空间。 二数据区 数据区是由一组连续的Oracle数据块所构成的Oracle存储结构一个或多个数据块组成一个数据区一个或多个数据区在组成一个段。 区为段分配空间它由连续的数据块组成。 当段中所有空间已完全使用时系统自动为该段分配一个新区。 区不能跨数据文件存在只能存在于一个数据文件中。
三段 段由一个或多个数据区组成。 段是构成表空间的逻辑存储结构段由一组区组成。 按照段所存储数据的特征将段分为若干种类型主要有数据段、索引段、回退段和临时段。
4种段 数据段、索引段、回滚段、临时段
四表空间 一个表空间由一个或多个数据文件组成一个数据文件只属于一个表空间。 Oracle数据的存储空间逻辑上表现为表空间而在物理上表现为数据文件。 每个数据库至少一个表空间表空间的大小等于所有从属于它的数据文件大小的总和。 1System表空间 系统表空间用于存放Oracle系统内部表和数据字典的数据例如 列名 2Sysaux表空间 主要存储除数据字典以外的其他数据中的对象 3UNDO表空间 撤销表空间用于存储撤销信息的表空间 4Users表空间 可以创建各种数据对象。
Oracle物理存储结构
Oracle数据库的物理存储结构有多种物理文件组成主要有数据文件、控制文件、重做日志文件、归档日志文件、口令文件和警告日志文件
一数据文件.DBF 数据文件是用于保护用户应用程序数据和Oracle系统内部数据的文件 (1)系统数据文件 (2)撤销数据文件 (3)用户数据文件
二控制文件.CTL 二进制文件记录数据库的物理结构 每个数据库至少拥有一个控制文件一个数据库可以同时拥有多个控制文件一个控制文件只能属于一个数据库
三日志文件.LOG 日志文件主要记录对数据所作的修改 1重做日志文件 用来记录数据库所有发生过的更改信息 SGA系统全局区 LGWR日志写入进程 ARCH归档进程
2归档日志文件 四密码文件、警告文件、跟踪文件 (1)密码文件 (2)警告文件 主要用来记录Oracle系统的运行信息和错误信息
3跟踪文件
Oracle11g服务器结构
Oracle服务器主要由实例、数据库、程序全局区和前台进程组成 实例可以分为系统全局区SGA、后台进程PMON、SMON两部分 程序全局区PGA是一个非共享的内存区域 内存结构PGASGA SGA系统全局区 实例启动共享 PGA程序全局区 服务器进程启动私有
系统全局区SGA
主要高速缓冲区、共享池、重做日志缓冲区、java池、大型池等内存结构组成分为三部分 脏数据区、空闲区、保留区
1重置日志缓冲区 用于存放对数据库进行修改操作时产生的日志信息。
2共享池保存生成执行计划
select count(*) from emp;//生成执行计划
scott: select * from help;共享池是SGA保留的内存区域。 共享池是对SQL、PL/SQL程序进行语法分析、编译、执行的内存区域。 共享池由库缓存和数据字典缓存组成。 共享池的大小直接影响数据库的性能。
4数据缓冲区 用于存储从磁盘数据文件中读入的数据所有用户共享。 Oracle的用户都是从数据缓冲区读数据。
服务器进程中将读入的数据保存在数据缓冲区中当后续的请求需要这些数据时可以在内存中找到不需要再从磁盘读取提高了读取速度。 数据缓冲区的大小对数据库的读取速度有直接的影响。
3日志缓冲区 日志记录数据库的所有修改信息日志信息首先产生于日志缓冲区。 当日志缓冲区的日志数据达到一定数量时由后台进程将日志数据写入日志文件中。 相对来说日志缓冲区对数据库的性能影响较小。 符合以下任一条件日志缓冲区数据写入日志文件中
1日志缓冲区容量达到三分之一
2日志缓冲区容量达到一兆
3三秒后4大型池
Oracle splile动态参数文件5Java池 6流池 用于数据库与数据库之前的信息共享。
程序全局区PGA
又称用户进程全局区它的内存在进程私有区而不是共享区中。 1私有SQL区 2会话区
1、会话是用户与Oracle服务器的单个连接
2、当用户与服务器断开连接时关闭会话
3、当用户与服务器建立连接时创建会话
4、从V$ Session中查询会话信息前台程序 A.用户进程 能生成或执行SQL语句称为用户进程 B.服务器进程
后台程序 Oracle后台程序是实例的重要组成部分其中SMONPMONDBWRLGWRCKPT这5个后台程序必须正常启动。 1.数据写入进程 数据写入操作DBWR的主要任务是负责将内存中的“脏”数据快回写到数据文件中。 “脏”数据是指高速数据缓冲区中的被修改过的数据块 1管理系统缓冲区将最近使用过的块保留是在内存中 2将修改后的缓冲区数据写入数据文件中。 2.检查点进程 检查点进程CKPT可以看做是一个事件当检查点事件发生时CKPT会要求DBWR将某些“脏数据”回写到数据文件。分为完全检查点和增量检查点。 完全检查点两种情况下触发 1、用户发出 alter checkpoint 2、除shutdown外正常关闭 3.日志写入进程LGWR 日志写入进程用于将重做日志文件缓冲区中的数据写入到重做日志文件。 1负责将日志缓冲区中的日志数据写入日志文件。 2系统有多个日志文件该进程以循环的方式将数据写入文件。 4.归档进程ARCH 只有当Oracle数据库处于归档模式时该进程才可能起到作用。 5.系统监控系统SMON 是数据库系统启动时执行恢复工作的强制性进程监控Oracle整个进程。 1.在实例失败之后重新打开数据库时自动恢复实例。 2.整理数据文件的自由空间将相邻区域结合起来。 3.释放不再使用的临时段。 6.进程监控进程PMON 用于监控其他进程的状态当有进程启动失败时PMON会清除失败的用户进程释放用户进程所用的资源。 1.清理出现故障的进程 2.释放所有当前挂起的锁定 3.释放故障进程使用的资源 第三章 Oracle数据库的安装与配置
启动startup数据库步骤 1创建并启动实例 2装载数据库 3打开数据库 Startup
1.NOMOUNT模式 只会创建实例并不会加载数据库也不会打开任何数据文件。
2.MOUNT 模式 启动实例加载数据库并保持数据库的关闭状态。
3 . Open模式 启动、加载、打开数据库
4 . Force模式 这种模式将终止实例并重新启动数据库这种启动模式具有一定的强制性。 关闭shutdown数据库步骤
1关闭数据库 2卸载数据库 3关闭实例
SHUTDOWN NORMAL 正常关闭模式 TRANSACTIONAL事务关闭方式 IMMEDIATE 立刻关闭 ABORT 终止关闭方式
第四章 SQLPLUS命令
1SET命令
Set pagesize value 表示该变量用来设置从顶部标题至结束之间的行数。Set newpage value 用来设置一页中空行的数量Set linesize value 一行所显示的最多字符总数Set pause Off默认不暂停 On输出的结果每一页都暂停用户按回车继续显示 Text在设置pause的值为on之后若在设置text 的值则每次暂停都会将显示该字符串。Off时无效。 5.Set numformat
2DESCIBE命令 查询指定数据对象的组成结构 DESC object_name; 3spool命令 把查询的结果输出到指定文件中 eg: spool c:\emp.txt; 4Define 命令 定义一个用户变量并且可以分配一个char值 Define vjob’SALESMAN’ 5save 命令 将最近的一条SQL语句或PL/SQL块保存到一个文件中语法如下 Save file_name; 6把一个SQL脚本文件的内容放进SQL缓存区 Egget c:\dept.sql; 7Start 和命令 这两个命令都可以用来执行一个SQL脚本文件 Eg: start c:\emp.txt; 格式化命令 Column Format 用于格式化指定的列需要在Format关键字的后面加一个掩码格式。 Ttitle和BTitle命令 头标题和底标题 Ttitle: Off: 表示禁止打印头标题 On允许打印头标题
第五章 SQL语言基础及调优
SQl语言的特点 集合性、统一性、易于移植性
模式与模式对象 模式是一个数据库对象的集合。模式为一个数据库用户所有并且具有与该用户相同的名称。 模式对象是由用户创建的逻辑结构用以存储或引用数据。
为列指定别名
select empno as 员工编号,ename as 员工名称,job as 职务 from emp;比较筛选
(A!B) 比较A与B不相等 使用all关键字过滤工资sal同时等于3000,950和800的员工记录 Egselect empno,ename,sal from emp where salall(3000,950,800);
使用特殊关键字筛选
like 关键字 即字符串模式匹配或字符串模糊查询like一般与通配符连用
如“_”,它代表一个字符 “%” 代表任意数量的字符如“K%”表示以字母K开头的任意长度的字符串“%M%”表示包含字符M的任意长度的字符串“_MRKJ”表示5个字符长度且后面4个字符是MRKJ的字符串。in关键字
Select empnoenamejob from emp where job in‘President’,’manger’,’analyst’;Between 关键字
查询工资在2000到3000之间员工之间的信息
Select empnoenamesal from emp where sal between 2000 and 3000分组查询
系统默认升序 ASC
1select deptno,job from emp group by deptno,job order by deptno;
2select deptno as 部门编号,avg(sal) 平均工资 from emp group by deptno having avg(sal)2000;多表关联查询
select e.empno as 员工编号,e.ename as 员工称呼d.dname as 部门2 from emp edept d3 where e.deptnod.deptno4 and e.jobMANAGER;内连接
内连接是一种常见的多表关联查询方式一般使用关键字inner join来实现inner可以省略
select e.empno as 员工编号e.ename as 员工名称d.dname as 部门2 from emp e inner join dept d3 on e.deptnod.deptno;外连接 左外连接关键字为LEFT OUTER JOIN 或 LEFT JOIN 右外连接关键字为RIGHT OUTER JOIN 或RIGHT JOIN 完全外连接 关键字为FULL OUTER JION 或FULL JOIN
左外连接
select e.empno,e.ename,e.job,d.deptno,d.dname2 from emp e left join dept d3 on e.deptnod.deptno;右外连接
select e.empno,e.ename,e.job,d.deptno,d.dname2 from emp e right join dept d3 on e.deptnod.deptno;自然连接
select empno,ename,job,dname2 from emp natural join dept3 where sal 2000;自身连接
select em2.ename 上层管理者,em1.ename as 下属员工2 from emp em1 left join emp em23 on em1.deptnoem2.deptno4 order by em1.mgr;常用函数 1.dual 是系统内部提供的一个用于实现临时数据计算的特殊表 2.ASCII 返回一个字符的ASCII码 3.ConcatS1S2 该函数将字符串S2连接到S1的后面 4.Initcap 第一个字母大写 5.InSTRs1s2ij 用于返回字符S2在字符串S1中第j次出现时的位置搜索从字符串S1的第i个字符开始。 6.Select instr ‘oracle 11g’’1’32 abc from dual ABC 7.LTRIM(S1,S2) 删除S1左边的s2 8.RTRIMS1S2 删除S1右边的S2 9.Trim 删除s1左右的说s2 10.Replace s1s2s3 用s3替换出现在S1字符中的所有s2字符串。 11.SUBSTRSi[j] 表示从字符串s的第i个位置开始截取长度为j子字符串。 数字类函数 13.Ceil(n) 返回大于或等于数值n的最小整数 14.FLORRn 返回小于或等于数值n的最大整数 转化型函数 15.to_char 将表达式转换为字符串 16.to_number(S[format[lan]]) 返回字符串S所代表的的数字 聚合函数 select countempno as 员工总数round(avg(sal),2) as 平均工资 from emp; 子查询 select empno,ename,job from empwhere deptnoselect deptno from dept where dnameRESEARCH;提示 子查询不能包括 Order by
多行子查询
使用 in查询 查询不是销售部门的员工信息
select empno,ename,job from emp
where deptno in
(select deptno from dept where dnameSALES);使用 ANY查询
查询工资大于部门标号为10的任意一个员工工资的其他部门的员工信息 select deptno,ename,sal from emp where sal any(select sal from emp where deptno10) and deptno10;使用ALL查询
查询工资大于部门编号为30的所有员工工资的员工信息
select empno,ename,job from emp wheresal ALL(select sal from emp where deptno30);关联子查询
查询工资大于同职位的平均工资的员工信息。
select empno,ename,salfrom emp fwhere sal (select avg(sal) from emp where jobf.job)order by job;插入语句 insert into dept(deptno,dname,loc) values (88,design,beijing);更新数据
update emp set salsal*1.2where jobSALSMAN;删除数据:
delete from scott.emp where empno7369;Truncate语句 删除表记录不可回滚。
事务 原子性、一致性、隔离性、持久性 操作事务 Commit、ROLLBACK、savepoint
EXISTS 是判断是否存在,和in类似,但效率要比in高,返回True 或False
SELECT * FROM EMP (基础表) WHERE EMPNO 0 AND EXISTS (SELECT ‘X FROM DEPT WHERE DEPT.DEPTNO EMP.DEPTNO AND LOC ‘MELB)SELECT * FROM EMP (基础表) WHERE EMPNO 0 AND DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC ‘MELB)第六章 PL/SQL编程
PL/SQL程序都是以块为基本单位分三部分 声明部分declare 执行部分begin开头 异常处理exception end
(1) declare 可以声明变量、常量、游标
(2) Begin ——end
(3) Exception计算两个数的和与两个数的差的商 Eg SQL set serveroutput on;在服务器端显示执行结果SQL declare 2 a int:100;3 b int:200;4 c number;5 begin6 c:(ab)/(a-b);7 dbms_output.put_line(c);8 exception9 when zero_divide then10 dbms_output.put_line(除数不能为零!);11 end数据类型、变量和常量
一基本数据类型 1.数值类型 数值类型主要包括number储存整数或浮点数、PLS_INTEGERpls_integer和BINARY_INTEGERbinary_interger只储存整数 Numberps 参数p: 表示精度有效数字 参数S表示刻度范围 number9,29表示这个数据的有效位数精度2表示两个小数位刻度例如1234567.89 允许有7位整数小数点后2位小数 二特殊类型 1.%TYPE类型 使用%type关键字声明一个与列名称类型完全相同的数据类型
Eg declare
var_job emp.job %type;输出emp表中编号为7369的员工名称和职务信息
set serveroutput on;
SQL declare 2 var_ename emp.ename%type;3 var_job emp.job%type;4 begin5 select ename,job into 6 var_ename,var_job7 from emp 8 where empno7369;9 dbms_output.putline(var_name||的职务是||var_job);10 end;
11 /2.RECORD类型记录类型 使用该类型可以储存由多个列值组成的一行数据。 举例声明一个记录类型emp_type,然后使用该类型的变量储存emp表中的一条记录信息并输出这条记录信息。
SQL set serveroutput on;
SQL declare2 type emp_type is record6 ( var_ename varchar2(20),var_job varchar2(20),var_sal number);7 empinfo emp_type;8 begin9 select ename,job,sal10 into emp 11 from emp12 where empno7369;13 dbms_output.putline(雇员||empinfo.var_ename||的职务是||empinfo.var_job||、工资是||empinfo.var_sal);14 end;15 /3.%ROWTYPE类型 可以根据数据表中行的结构定义一种特殊的数据类型用来储存存数据表中检索到的一行数据。 比如声明一个%rowtype类型的变量rowVar_emp,然后使用该变量储存emp表中的一行数据代码如下 SQL set serveroutput onSQL declare 2 rowVar_emp emp%rowtype;3 begin4 select * into rowVar_emp 5 from emp 6 where empno7369;7 dbms_output.putline();8 end;9 /流程控制语句
1.If…then end if 举例使用if…then 语句比较两个字符串变量
SQL set serveroutput on;
SQL declare2 var_name1 varchar2(50);3 var_name2 varchar2(60);4 begin 5 var_name1:EAST;6 var_name2:xiaoke;7 if length(var_name1)length(var_name2) then8 dbms_output.putline();9 end if;10 end;
11 /2.If…then … else end if 通过if…else语句实现只有年龄大于等于56岁才可以申请退休否则提示不可以退休。
SQL set serveroutput on;
SQL declare2 age int:55;3 begin 4 if age56 then5 dbms_output.put_line(你可以退休了);6 else7 dbms_output.put_line(你小于56岁不可以申请退休);8 end if;9 end;10 /
3.If…then … Elsif … then … Else … End if
Eg:set serveroutput on;
DelcareMonth int:10;
Begin If month10 thenDbms_ouput.putline(‘’);Elsif month4 and monthe6 thenDbms_output.putline(‘’);
Elsif month7 and mon9 thenDbms_output.putline(‘’);
Else4.Case语句
Case selector
When expression 1 then plsql_sentences_1;
When expression 2 then plsql_sentences_2;
When expression 3 then plsql_sentences_3;
When expression 4 then plsql_sentences_4;
End case;举例 指定一个季节使用case语句说明月份信息
Set serveroutput onDeclareSeason int:3;Aboutinfo varchar2(50);
BeginCase seasonWhen 1 then Aboutinfo:season||’季节包括12, 3月份’
When 2 then
Aboutinfoseason||’季节包括7,8,9月份’End case;Dbms_output.putline(Aboutinfo);End;/5.循环语句 Loop语句
Loop
...
Exit when
..
End if
End
/举例 用loop函数求前100个自然数的和输出到屏幕上去。
Set serveroutput on
Declare Sum_i int:0;i int:0;
BeginLoopii1Sum_iSum_ii;Exit when i100;End loop;Dbms_output.putline(‘’);End;
/ 6 . While 语句 举例
Declare Sum_i int:0;I int:0;
BeginWhile i99 loopii1;Sum_iSum_ii;
End loop;
Dbms_output.putline();
End;7 . for语句 举例前100个自然数中偶数之和
Declare
Sum_i :int0;
BeginFor i in reverse 1..100 loopIf mod(i,2) 0 then
Sum_iSum_ii;
End if;
End loop;
Dbms_output.putline();
End
/PL/SQL游标
通常用于查询结果集每次只能读取一行数据 4个步骤如下
声明游标、打开游标、读取游标、关闭游标1.声明游标 Cursor cur_name(intput _parameter1)Intput_parameter1:参数 定义如下
Para_name [in] datatype [{:|Default } para_value]举例声名一个游标用来读取emp表中职务为 销售员的信息
Declare Cursor cur_emp(var_job in varchar2:’SALESMAN’)Is select empnoenamesal
From emp
Where jobvar_job2.打开游标
Open cur_name(value)
Open cur_emp(‘MANAGER’);3.读取游标
Fetch cur_name into {variable}举例 Declare
Cursor cur_emp(var_job in varchar2:’SALESMAN’)
Is select empno,ename,salFrom emp
Where jobvar_job;
Type record_emp is record
( Var_emp emp.empno%type,Var_ename emp.ename%type,Var_sal emp.sal%type
);Emp_row record_emp;
BeginOpen cur_emp(‘MANAGER’);Fetch cur_emp into emp_row;
While cur_emp%found loop
Dbms_output.putline();Fetch cur_emp into emp_row;
End loop;
Close cur_emp;
End;
/4.关闭游标
Close cur_name;游标属性
%found:布尔型属性如果SQL语句至少影响一行数据该属性为true否则为false
%rowcount数字型属性返回SQL语句影响的行数。
%isopen:布尔型属性打开游标为true关闭为false。SQL set serveroutput on;
SQL declare2 var_ename varchar2(50);3 var_job varchar2(50);4 cursor cur_emp5 is select ename,job6 from emp7 where empno7499;8 begin9 open cur_emp10 fetch cur_emp into var_ename,var_job;11 if cur_emp%found then12 dbms‘’|| ||’, ’||;13 else14 dbms‘’;15 end if;16 end
17 /存储过程 只能通过execut命令执行 储存过程
create or replace pro_name is
Begin
...
Commit
End pro_name;
/举例
Create or replace procedure insert_dept(Num_deptnp in number,var_ename in varchar2,Var_loc in varchar2
) is
Begin Insert into dept
Values(num_deptno,var_ename,var_loc);
Commit;
End insert_dept;Oracle 逻辑存储结构 数据文件、控制文件、日志文件
Oracle物理存储结构 数据块、数据区、段、表空间
Oracle数据 由实例、数据库、程序全局区、前台进程组成
实例可以分为系统全局区SGA、后台进程PMONSMON PGA程序全局区是一个非共享的内存区域。
Spool命令把查询结果输入到指定文件中 事务 原子性、一致性、隔离性、持久性 声明游标、打开游标、读取游标、关闭游标 Execute(); 重命名表空间
Alter tablespace tbs_test_3 rename to tbname;
Constirant 约束
Alter table student drop(sex_age);Create or replace trigger tri_deptBefore insert or replace or delete
On dept;
Declare Var_tag varchar2(10);
BeginIf inserting then Var_tag:’插入’;Elsif update then Elsif insert thenElsif insert thenEnd if
Insert into dept
Values(var_tag,sysdate);
Create or repalce trigger tri_insert_good
Before insert
On goodsFor each now
Begin Select seq_id.nextvalInto :new .id
From dual;
End;
/Create or replace package package_emp is
Function fun_avg_sal(num_deptno number) return number isNum_avg_sal number;BeginSelect avg(sal) Into num_avg_salFrom empWhere deptnonum_deptno;
Return(num_sal);SqlPlus column命令用法
column是sqlplus里最实用的一个命令很多时候sql语句输出的列宽度不合适而影响查看都需要用到这个命令来更改select语句中指定列的宽度和标题。大部分时候我们可以简写column为col即可主要有以下用法
修改列宽度
col c1 format a20 –将列c1(字符型)显示最大宽度调整为20个字符
col c1 format 9999999 –将列c1(number型)显示最大宽度调整为7个字符修改列标题
col c1 heading c2 –将c1的列名输出为c2设置列的对齐方式
SQL col ename justify left/right/center;
SQL select empno, ename, job from emp;注意对于number类型的数据默认为右对齐其他默认为左对齐 隐藏某列显示col job noprint
SQL col job noprint;
SQL select empno, ename, job from emp;格式化number类型列的显示column sal format $999,999.00
SQL column sal format $999,999.00
SQL select empno, ename, sal from emp;设置列值若列植为空以text代替
SQL col comm null text
SQL select * from emp;显示列的当前属性
SQL column ename;重置为默认值
SQL clear columns;一行只显示数字位的长度, 超过长度折行, 加word_wrapped后, 单词不会折行
column info format a40 word_wrapped设置列头
sql column ename heading 姓名 format a15触发器例题
为emp表创建一个触发器当执行插入操作时统计操作后员工人数当执行更新工资操作时统计更新后员工平均工资当执行删除操作时统计删除后各个部门的人数。
CREATE OR REPLACE TRIGGER trg_emp_dml
AFTER INSERT OR UPDATE OR DELETE ON emp
DECLAREv_count NUMBER;v_sal NUMBER(6,2);
BEGINIF INSERTING THEN SELECT count(*) INTO v_count FROM emp;DBMS_OUTPUT.PUT_LINE(v_count);ELSIF UPDATING THENSELECT avg(sal) INTO v_sal FROM emp;DBMS_OUTPUT.PUT_LINE(v_sal);ELSEFOR v_dept IN (SELECT deptno,count(*) num FROM emp GROUP BY deptno) LOOPDBMS_OUTPUT.PUT_LINE(v_dept.deptno|| ||v_dept.num);END LOOP;END IF;
END trg_emp_dml; 为emp表创建一个触发器当插入新员工时显示新员工的员工号、员工名当更新员工工资时显示修改前后员工工资当删除员工时显示被删除的员工号、员工名。
CREATE OR REPLACE TRIGGER trg_emp_dml_row
BEFORE INSERT OR UPDATE OR DELETE ON emp
FOR EACH ROW
BEGINIF INSERTING THEN DBMS_OUTPUT.PUT_LINE(:new.empno|| ||:new.ename);ELSIF UPDATING THENDBMS_OUTPUT.PUT_LINE(:old.sal|| ||:new.sal);ELSEDBMS_OUTPUT.PUT_LINE(:old.empno|| ||:old.ename);END IF;
END trg_emp_dml_row;修改员工工资时保证修改后的工资高于修改前的工资。
CREATE OR REPLACE TRIGGER trg_emp_update_row
BEFORE UPDATE OF sal ON emp
FOR EACH ROW
WHEN(new.salold.sal)
BEGINRAISE_APPLICATION_ERROR(-20001,The salary is lower!);
END trg_emp_update_row; 创建一个包括员工及其所在部门信息的视图empdept然后向视图中插入一条记录2345,’TOM’,3000,’SALES’
create or replace trigger tri_insert_viewinstead of inserton view_emp_dept--创建一个关于view_emp_dept视图的替换触发器for each row--是行级视图
declarerow_dept dept%rowtype;
beginselect * into row_dept from dept where deptno :new.deptno;--检索指定部门编号的记录行if sql%notfound then--未检索到该部门编号的记录insert into dept(deptno,dname)values(:new.deptno,:new.dname);--向dept表中插入数据end if;insert into emp(empno,ename,deptno,job,hiredate)values(:new.empno,:new.ename,:new.deptno,:new.job,:new.hiredate);--向emp表中插入数据
end tri_insert_view;
/当数据库中执行CREATE操作时将创建的对象信息记录到ddl_creations表中。
CREATE TABLE ddl_creations (user_id VARCHAR2(30),object_type VARCHAR2(20),object_name VARCHAR2(30),object_owner VARCHAR2(30),creation_date DATE);CREATE OR REPLACE TRIGGER log_creations
AFTER CREATE ON DATABASE
BEGININSERT INTO ddl_creationsVALUES(ora_login_user, ora_dict_obj_type, ora_dict_obj_name, ora_dict_obj_owner, sysdate);
END log_creations;为了实现在更新员工所在部门或向部门插入新员工时部门中员工人数不超过8人可以在emp表上创建两个触发器同时创建一个共享信息的包。
CREATE OR REPLACE PACKAGE mutate_pkg
ASv_deptno NUMBER(2);
END;CREATE OR REPLACE TRIGGER rmutate_trigger
BEFORE INSERT OR UPDATE OF deptno ON EMP
FOR EACH ROW
BEGINshare_pkg.v_deptno::new.deptno;
END; CREATE OR REPLACE TRIGGER smutate_trigger
AFTER INSERT OR UPDATE OF deptno ON EMP
DECLAREv_num number(3);
BEGINSELECT count(*) INTO v_num FROM emp WHERE deptnoshare_pkg.v_deptno;IF v_num8 THENRAISE_APPLICATION_ERROR(-20003,TOO MANY EMPLOYEES IN DEPARTMENT ||share_pkg.v_deptno);END IF;
END; 选择题
1有4条与游标有关的语句它们在程序中出现的正确顺序是 B
OPEN abcCURSOR abc IS SELECT ename FROM empFETCH abc INTO vnameCLOSE abc A1、2、3、4 B2、1、3、4 C2、3、1、4 D1、3、2、4
2数据库运行在归档模式下如果发生日志切换为了保证不覆盖旧的日志信息系统将启动如下哪个进程 D ADBWR BLGWR CSMON DARCH
3对于ROLLBACK命令以下准确的说法是( C ) A撤销刚刚进行的数据修改操作 B撤销本次登录以来所有的数据修改 C撤销到上次执行提交或回退操作的点 D撤销上一个COMMIT命令
4创建表空间时可以指定表空间中存储对象的默认存储参数其中哪个参数用于设置分配给每一个对象的初始区大小( B ) ANEXT BINITIAL CPCTINCREASE DMINEXTENTS
5现将CONNECT 角色授予TEXT_ROLE角色下面哪个语句可以实现 D AGRANT ROLE CONNECT ON TEST_ROLE; BGRANT ROLE CONNECT TO TEST_ROLE; CGRANT CONNECT ON TEST_ROLE; DGRANT CONNECT TO TEST_ROLE;
填空题
1Oracle数据库系统的物理存储结构主要有三类文件组成分别 数据文件 和 控制文件 和 日志文件 。逻辑存储结构包括 数据块数据区段表空间 。 2SQL*Plus中直接调用过程的关键字是 EXECUT 查询中去掉重复的行必须使用的关键字是 DISTINCT ; 关键字 DESCRIBE 可以用来显示表的结构信息。 3使用EXPORT命令时可以有3种不同方式导出数据表方式T方式 就是导出一个指定的基本表包括表的定义和数据及表上的索引、约束等。 用户 方式U方式 是导出一个用户的所有对象包括表、视图、存储过程、序列等。 全库方式 Full方式 是导出数据库中所有的对象。 4在ORACLE数据库中将权限分为两类即 系统权限 和 对象权限 分别指在系统级控制数据库的存取和使用机制和在模式对象上控制存取和使用的机制。
判断题
1INSERT改为UPDATA事件触发器中可以使用:old伪记录DELETE事件触发器中可以使用:new伪记录。 ( 错 ) 2后台进程LGWR的作用是数据库写入程序。 ( 错 ) 3Alter user tempuser Identified by oracle Default tablespace users Default temporary tablsespace temp Quota 100M on users ( 对 ) 4SQL*PLUS中显示登录的用户的命令是DESCRIB user。 ( 错 ) 5默认登录Oracle Enterprise Manager Database Control 的端口号是1158。( 对 )
简答题
1How to connect remote oracle server by SQLPLUS? Please write down the steps and the operating statements? 如何通过SQLPLUS连接远程oracle服务器?请把操作步骤和操作报表写下来。
方式一 简易连接不用进行网络配置其实就是tnsname.ora文件,但只支持oracle10G以上。 命令sqlplus 用户名/密码ip地址[:端口]/service_name [as sysdba] 示例sqlplus sys/pwdip:1521/test as sysdba 备注使用默认1521端口时可省略输入 方式二进行网络配置 oracle9i和以前的版本
1.图形化操作:Net Configuration Assistant– 本地Net服务名配置–添加-服务名-协议(选tcp)-主机名称-端口-完成。
2.最常用配置监听配置tnsname.ora文件 例如
ORA11GR2 (DESCRIPTION (ADDRESS (PROTOCOL TCP)(HOST 192.168.2.6)(PORT 1521))(CONNECT_DATA (SERVER DEDICATED)(SERVICE_NAME PROD)))方式三EZConnect字符串 配置sqlnet.ora文件
secsecdb /home/oracle$ cat $ORACLE_HOME/network/admin/sqlnet.ora
#sqlnet.ora Network Configuration File: /oracle/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora
#Generated by Oracle configuration tools.
NAMES.DIRECTORY_PATH (TNSNAMES,EZCONNECT)
cmd窗口或者 linux下sqlplus 用户名/密码ip/实例名称2What is the difference between procedures and functions in the Oracle database(Oracle数据库中的过程和函数有什么不同)
1.存储过程定义关键字用procedure函数定义用function。 2.存储过程中不能用return返回值但函数中可以而且函数中必须有return子句。 3.函数可以可以在查询语句中直接调用单独执行存储过程必须通过execute执行单独调用 4.函数可以当做表达式使用
3读懂如下程序:
CREATE OR REPLACE FUNCTION ret_deptinfo(p_deptno dept.deptno%TYPE, p_num OUT NUMBER, p_max OUT NUMBER)
RETURN dept.dname%TYPE
ASv_dname dept.dname%TYPE;
BEGINSELECT dname INTO v_dname FROM dept WHERE deptnop_deptno;SELECT count(*),max(sal) INTO p_num,p_max FROM emp WHERE deptnop_deptno;RETURN v_dname;
END ret_deptinfo; 分析程序实现的功能
4输入和运行以下程序
CREATE OR REPLACE TRIGGER CHECK_SAL
BEFORE UPDATE ON emp
FOR EACH ROW
BEGIN
IF :new.jobCLERK AND (:new.sal500 OR :new.sal1000) THENRAISE_APPLICATION_ERROR(-20001, 工资修改超出范围,操作取消);
END IF;
END;分析程序实现的功能
设计题
1、创建一个存储过程以部门号为参数查询该部门的平均工资并输出该部门中比平均工资高的员工号、员工名。并且调用该存储过程,要求根据输入部门编号查询平均工资及输出比平均工资高的员工号、员工名。
CREATE OR REPLACE PROCEDURE show_emp( p_deptno emp.deptno%TYPE)
ASv_sal emp.sal%TYPE;
BEGINSELECT avg(sal) INTO v_sal FROM emp WHERE deptnop_deptno;DBMS_OUTPUT.PUT_LINE(p_deptno|| ||average salary is: ||v_sal);FOR v_emp IN (SELECT * FROM emp WHERE deptnop_deptno AND salv_sal) LOOPDBMS_OUTPUT.PUT_LINE(v_emp.empno|| ||v_emp.ename);END LOOP;
EXCEPTIONWHEN NO_DATA_FOUND THENDBMS_OUTPUT.PUT_LINE(The department doesn’’t exists!);
END show_emp;过程调用语句
declare
vdeptno emp.detpno%type;
Begin
vdeptno:deptno;
show_emp(vdeptno);
End; 2、创建一个函数以部门号为参数返回部门名、部门人数及部门平均工资。并且调用该函数输出所有有员工的部门的名称、部门人数和平均工资。
CREATE OR REPLACE FUNCTION ret_deptinfo( p_deptno dept.deptno%TYPE,p_num OUT NUMBER, p_avg OUT NUMBER)
RETURN dept.dname%TYPE
ASv_dname dept.dname%TYPE;
BEGINSELECT dname INTO v_dname FROM dept WHERE deptnop_deptno;SELECT count(*),avg(sal) INTO p_num,p_avg FROM emp WHERE deptnop_deptno;RETURN v_dname;
END ret_deptinfo;
函数调用语句
DECLAREv_avgsal emp.sal%TYPE;v_num NUMBER;v_dname dept.dname%TYPE;
BEGINFOR v_dept IN (SELECT DISTINCT deptno FROM emp) LOOPv_dname:ret_deptinfo(v_dept.deptno,v_num,v_avgsal);DBMS_OUTPUT.PUT_LINE(v_dname|| ||v_maxsal|| || v_avgsal|| ||v_num);END LOOP;
END;3、创建学生表studentsno,sname,sex,sage,要求学号sno主键姓名sname不能重复 性别sex只能是‘男’或者‘女’年龄sage在15到25之间。创建课程表course(cno,cname)要求课程号cno主键课程名cname唯一同时为主键约束列上的唯一性索引设置存储位置和存储参数。创建学生选课表SCsno,cno,grade,要求成绩grade大于0小于100有两位小数sno,cno都是外键而且sno,cno一起做主键。
CREATE TABLE student(sno NUMBER(6) CONSTRAINT S_PK PRIMARY KEY,sname VARCHAR2(20) UNIQUE,sex CHAR(2) CONSTRAINT S_CK1 CHECK(sex in(男, 女)),sage NUMBER(6,2) CONSTRAINT S_CK2 CHECK(sage between 15 and 25));
CREATE TABLE course(cno NUMBER(6) PRIMARY KEY,cname CHAR(20) UNIQUE USING INDEX TABLESPACE USERSTORAGE (INITIAL 64K NEXT 64K));
CREATE TABLE SC(sno NUMBER(6) REFERENCES student(sno),cno NUMBER(6) REFERENCES course(cno),grade NUMBER(5,2) CHECK(grade between 0 and100)CONSTRAINT SC_PK PRIMARY KEY(sno, cno) );4、创建用户user2口令为user2默认表空间为USERS在该表空间的配额为10 MB初始状态为锁定。创建用户user3口令为user3默认表空间为USERS在该表空间的配额为10 MB概要文件为example_profile(假设该概要文件已经创建)为用户user2授予CREATE SESSIONCREATE TABLE CREATE VIEW系统权限。user2获得权限后为用户user3授予CREATE TABLE权限。然后回收user2的CREATE TABLE权限。
SQL conn system/oracleorcl 或者 conn / as sysdba 仅供参考正确就行
SQLCREATE USER user2IDENTIFIED BY user2DEFAULT TABLESPACE USERS QUOTA 10M ON USERSACCOUNT LOCK;
SQL CREATE USER user3 IDENTIFIED BY user3 DEFAULT TABLESPACE USERS QUOTA 10M ON USERSPROFILE example_profile ;
SQL GRANT CREATE SESSION,CREATE TABLE,CREATE VIEWTO user2 WITH ADMIN OPTION;
SQL CONNECT user2/user2 ORCL
SQL GRANT CREATE TABLE TO user3;
SQL conn system/oracleorcl 或者 conn / as sysdba
SQL revoke CREATE TABLE from user2;5、使用EXPDP导出scott模式相关数据并使用IMPDP 将刚刚导出的scott模式相关数据导入到system模式写出核心操作语句。
1、创建DIRECTORY :create directory dir_dp as D:/oracle/dir_dp;
2、授权 :Grant read,write on directory dir_dp to scott;
3、执行导出
expdp scott/tigerorcl schemasscott directorydir_dp dumpfile expdp_test1.dmp logfileexpdp_test1.log;
4、执行导入
impdp system/oracleorcl directorydir_dp dumpfile expdp_test1.dmp remap_schemascott:system logfileimpdp_test1.log;实验一
1使用Scott用户 2显示所有薪水低于3000的雇员的工作、姓名和工资 3添加一个居中、两行显示的表头“Employee Report”和一个居中的页脚“Confidential” 4重新命名工作列标题为“Job Category”分两行。重新命名姓名列标题为“Employee”重新命名工资列标题为“Salary”并且格式化为$9,999.99 5文件操作 SQL select job,ename,sal from emp;
星期日 9月 22 第 1 Employee Report
Job Category Employee Salary CLERK SMITH 800 SALESMAN ALLEN 1600 SALESMAN WARD 1250 MANAGER JONES 2975 SALESMAN MARTIN 1250 MANAGER BLAKE 2850 MANAGER CLARK 2450 ANALYST SCOTT 3000 PRESIDENT KING 5000 SALESMAN TURNER 1500 CLERK ADAMS 1100 CLERK JAMES 950 ANALYST FORD 3000 CLERK MILLER 1300 Confidential
已选择14行。
SQL spo off;
实验二 分析 相同点两者都为子查询有着相似的格式对于查询的结果两者具有相同的作用。 不同点not in 前需跟上返回且需比较的列名。 not esists定义子查询不返回任何一行时条件为true,是对行”的比较 not in定义 与子查询返回结果中任何一个值都不等是对“数值”的比较需加上数值所属的列名 实验三 1、
Select empno,ename,(salnvl(comm,0))*12 as all_sal from emp order by all_sal;2、
select * from emp where hiredate to_date(‘1982/1/1’,’yyyy/mm/dd’);3、
select max(sal),avg(sal) from emp order by deptno,job;4、
select *from emp where (deptno, job)(select deptno,job from emp where enameSMITH);
select * from emp where deptno (select deptno from emp where enameSMITH)
and job (select job from emp where enameSMITH);5、
select e.ename,e.job,m.ename,d.dname from emp e,emp m,dept d
where e.mgrm.empno and e.deptno d.deptno;6、 待定
select dnamecount(*) from dept
where deptno in(select deptno from emp where sal1000 group by deptno having count(deptno)2);7、
select e1.empno,e1.ename from emp e1,emp e2where e1.jobe2.job and e1.hiredatee2.hiredate and e1.sale2.sal;8、 右连接
select empno,ename,dname from dept
right join emp
on dept.deptnoemp.deptno and dept.deptno20;select empno,ename,dname from dept,emp
where dept.deptno()emp.deptno and dept.deptno()20;左连接
select empno,ename,dname from dept
left join emp
on deptnoemp.deptno and dept.deptno20;select empno,ename,dname from dept,emp
where dept.deptnoemp.deptno() and dept.deptno()20;9、
select * from emp where empno
in(select mgr from emp where mgr is not null);10、
declare变量声明、初始化
begin 业务处理、逻辑代码
exception 异常捕获
end;11、
DECLARETYPE t_emp IS RECORD(empno NUMBER(4), ename CHAR(10),sal NUMBER(6,2));v_emp t_emp;
BEGINSELECT empno,ename,sal INTO v_emp FROM emp WHERE empno7844;DBMS_OUTPUT.PUT_LINE(v_emp.ename|| ||v_emp.sal);
END;12、
DECLARE v_empno NUMBER(4);
BEGINv_empno:x;UPDATE emp SET salsal100 WHERE empnov_empno;
END; create or replace procedure ChangeSal(p_empno in emp.empno%type,p_sal in emp.sal%type)
as
beginupdate set emp salp_sal where empnop_empno;
end;13、
create or replace procedure ChangeSal( p_empno in emp.empno%type)
asp_deptno emp.deptno%type;
beginselect deptno into p_deptno from emp where empnop_empnoif p_deptno 10 thenupdate emp set sal sal100 where empno p_empnoelsif p_deptno 20 thenupdate emp set sal sal160 where empnop_empnoelsif p_deptno 30 thenupdate emp set sal sal200 where empnop_empnoelseupdate emp set sal sal300 where empnop_empnoend if;
end;
14、
declare
p_ename emp.empno%type;
p_job emp.job%type
cursor cur_emp
is select ename,job from emp where empno7654;
begin
open cur_emp;
fetch cur_emp into p_ename,p_job;
if cur_emp%found then
dbms_output.put_line(名字||p_ename|| 职务||p_job)
else
dbms_output_line(无数据记录);
end if;
end;15、
CREATE OR REPLACE PROCEDURE return_deptinfo(p_deptno emp.deptno%TYPE,p_avgsal OUT emp.sal%TYPE,p_count OUT emp.sal%TYPE)
AS
BEGINSELECT avg(sal),count(*) INTO p_avgsal,p_count FROM emp WHERE deptnop_deptno;
EXCEPTIONWHEN NO_DATA_FOUND THENDBMS_OUTPUT.PUT_LINE(The department don’’t exists!);
END return_deptinfo; 16、
create or replace function return_avg(p_deptno emp.deptno%type)
return number
isp_avgsal emp.sal%type;
beginselect avg(sal) into p_avgsal from emp where deptno p_deptno;return (p_avgsal);
exceptionwhen no_data_found thendbms_output.putline(该部门编号不存在);return (0);
end;17、
create or replace trigger tri_emp
after insert or update or delete on emp
declarev_count number;v_sal number(6,2);
beginif inserting thenselect count(*) into v_count from emp;dbms_output.put_line(v_count);elsif updating thenselect avg(sal) into v_sal from emp;dbms_output.put_line(v_sal);elsefor v_dept in (select deptno,count(*) num from emp group by deptno)loopdbms_output.putline(v_dept.deptno|| ||v_dept.num);end loop;end if;
end;18、
create or replace package pack_emp isfunction return_avg(p_deptno emp.deptno%type) return number;procedure change_sal(p_job emp.job%type,p_sal emp.sal%type);end pack_emp;19、
create or replace package body pack_emp isfunction return_info(p_deptno emp.empno%type) return number isp_avgsal emp.sal%type;beginselect avg(sal) into p_avgsal from emo where deptno p_deptno;return (p_avgsal);exceptionwhen no_data_found thendbms_output.putline(该部门编号不存在);return 0;end return_avg;procedure change_sal(p_job emp.job%type,p_sal emp.sal%type)beginupdate emp set sal sal*(1p_sal) where jobp_job;end change_sal;
end;20、
declarep_deptno emp.deptno%type;p_job emp.job%type;p_avgsal emp.sal%type;p_sal emp.sal%type;
beginp_deptno :10;p_avgsal :pack_emp.return_avg(p_deptno);dbms_output.putline(p_deptno||号部门的平均工资是||p_avgsal);p_job :SALESMSN;p_sal :0.1;pack_emp.chang_sal(p_job,p_sal);
end;
