oracle(知识总结).doc

Oracle总结一.数据库相关概念介绍1、DB（Database）：数据库DBA（DataBase Administrator）：数据库管理员DBMS（DataBase Management System）：数据库管理系统2、数据库的种类:oracle / sybase / db2(IBM) sql server / mysql / access3、数据表结构：由行和列组成.行: row / record 列: column / field 4、SQL分类：DQL（数据库查询语言）: selectDML（数据库管理语言）: insert / update / delete DDL（数据库定义语言）: create / drop / alter / truncate DCL（数据库控制语言）: grant / revoke TCL（事务控制语言）: commit / rollback / savepoint注：5、远程登录到Oracle数据库服务器的方式:登录到远程主机：C:telnet 192.168.0.26用户名/密码: openlab/open123这组是远程机器的用户名和密码远程登录后连接数据库:sqlplussqlplus是Oracle数据库提供的命令行客户端工具sunv210% sqlplus openlab/open123这组是数据库的用户名和密码6、了解练习常用到的数据表:emp: 职员表dept: 部门表salgrade: 薪水等级表desc命令:查看一个表的结构.如：desc dept7、 Oracle 数据库中常用的数据类型 varchar2(长度) 可变长字符串，它按照字符串的实际长度存储。 如：varchar2（10）表示最大长度为10的可变长字符串char(长度) 定长字符串，它存储不足的会用空格补齐,默认长度 1 个字节 ，如：char（10），表示固定长度为10的字符串Number() 表示整数或者浮点数精度为 38位，取值范围 138之间 number(7,2)表示数字类型，一共7位，小数点后两位，最大 99999.99number(5) 表示5位整数， 最大99999number 随便date 日期类型，blob 大对象类型，存二进制对象(声音、图片）二、数据库查询：select格式：select 列1，列2 别名，. from 表名 where 过滤条件；说明：1、- *号表示查询全部列 select * from dept;/查询dept表中的所有列select ename, sal, comm from emp; /查询emp表中的ename、sal、comm列 2、- 列别名：当对某一列取了别名后 3、列的算术表达式： 如：select ename，sal+comm total_sal from emp; 4、WHERE子句用来选择符合条件的记录。 、= 表示结果符合等于指定条件如：/查询emp表中所有的职位为经理的员工名字和职位elect ename, job from emp where job = MANAGER;、between . and . 表示结果在这之间，between and 是一个闭区间 如：/查询emp表中薪水在1000到2000之间的员工名字和薪水Select ename ，sal from emp where sal between 1000 and 2000；、!=，= 这三个都可以表示不等于； 如：/查询emp表中不在部门10工作的员工姓名和工作部门Select ename ，deptno from emp where deptno ！=10；、in (va1,val2,.) 判断结果是否在这个集合中存在 如：/查询emp表中在部门10和20 工作的员工姓名和部门Select ename ，deptno from emp where deptno in （10,20）；、like . 表示字符串通配查询，%表示 0 或多个字符， _ 表示一个字符； 如：/查询emp表中 员工姓名中包含字母“M”的员工，列出其姓名Select ename from emp where ename like %M%; 注： escape转义的用法：like S_% escape ，表示查询时匹配字符“_”，而不是表示一个字符. and . 表示只有两个条件同时满足； 如：/ 查询emp表中在部门10中工作且薪水大于2000的员工，列出姓名薪水，部门号Select ename，sal,deptno from emp where sal2000 and deptno=10;、. or . 表示条件只要满足其中之一就可以； 、all . 是要求都满足条件； 、not . 可以与以上的条件产生相反的效果； 注：空值与任何值比较结果都为空 、. is null 用来判断值是否为空。 6、 ORDER BY子句 格式：order by 目标列名（别名） 排序顺序（不写则默认为升序）说明：ORDER BY子句使得 SQL在显示查询结果时按指定行的指定顺序排列ASC（默认，升序） ，不怎么时默认DESC（降序） 例如： /查询emp表中ename列，并按ename升序排序select ename from emp order by ename; /查询emp表中ename列，并按ename降序排序select ename from emp order by ename desc; 3、 oracle 数据库函数注意:dual 表(哑表)是专门用于函数测试和运算的. 单行函数 ：一个值输入，一个值输出组函数：多个值输入，一个值输出1、字符函数 (字符是大小写敏感 的 ) 转小写 lower(字段名) 转大写 upper(字段名) 首字母大写 initcap(字段名) 字符串拼接 concat(字段 1, 字段 2) 截取子串 substr(字段名, 起始位置，取字符个数) 字符串长度 length() 空值函数 nvl() ，常用 例: select first_name,substr(first_name,2,2) sub from s_emp;（从名字的第二个字符开始取两个字符） select first_name,substr(first_name,-2,2) sub from s_emp; （从名字的倒数第二个字符开始取两个字符） 2、数值函数 、 四舍五入函数 round(数据,保留到小数点后几位) 1 表示保留到小数点后一位，-1 表示保留到小数点前一位。 例：select round(15.36,1) from dual; /结果为15.4 、 截取数值函数 trunc(数据，保留到小数点后几位) 例：select trunc(123.456,1) from dual; /结果为123.4 截取到小数点后一位,注意:与 round 函数不同,不会四舍五入。、取余函数 mod（被除数，除数） 列：select mod（16,5）from dual； /结果：13、 日期函数 、取系统时间: sysdate(Oracle独有的函数)select sysdate from dual;、日期的加减select sysdate-1,sysdate,sysdate+1 from dual; 取系统时间的前一天，当天和后一天 select sysdate,sysdate+1/144 from dual; 延迟 10 分钟 、1、months_between(sysdate,hiredate):月份在当前时间和入职时间之间。2、add_months(sysdate,6):在当前时间上加6个月。3、sysdate+6:当前时间加6天。4、next_day(sysdate,FRI):下一个周五（中文环境下要写“星期五”）。5、next_day(sysdate,5):下一个周四（数字中英文通用）。4、不同数据类型间的转换、将日期转换为指定格式字符串 to_char（date,日期格式) /将当前系统时间转换为 年月日 时（24小时制）分秒的形式select to_char(sysdate,yyyy mm dd hh24:mi:ss)from dual; 、将指定格式字符串转换为日期 to_date（字符串，日期格式）/如：将字符串“2011-03-20”转换为日期Select to_date（2011-03-20，yyyy-mm-dd） from dual；、to_number($12345.67,$99999.99):将字符串“$12345.67”转换成数字12345.67（后面$99999.99表示前面数字的显示格式，）、to_char(1234,9999.00):结果为：1234.00，后面的0表示不足位用0补。5、 group by 分组函数group by 分组子句 对分组后的子句进行过滤还可以用having不能用where如：/查询平均薪水大于2000的部门，并且按部门分组，列出部门号和平均薪水select deptno ，avg（sal） from emp group by deptno having avg（sal） 2000；-获得每个部门每个职位的平均薪水select deptno, job, avg(sal) from emp group by deptno, job order by deptno, job;6、 其他常用组函数Avg（） 求平均值函数Count（）统计数量的函数Max（）求最大值函数Min（）求最小值函数Sum（）求和值函数注：所有组函数会忽略空值（可用空值处理函数解决）, avg sum只能作用于数字类型/求emp表中所有人的奖金的平均值select avg(nvl(comm,0 ) ) from s_emp; 4、 表连接概念区分：等值连接、非等值连接；内连接、外连接、自连接；1、等值连接、非等值连接：当连接运算符为=时，称为等值连接。使用其它运算符称为非等值连接。如：/查询员工名字和所在的部门名字select e.ename,d.dname from emp e ,dept d where e.deptno=d.deptno;2、 内连接、外连接、自连接a) 、自连接:连接操作不仅可以在两个表之间进行，也可以是一个表与其自己进行连接，这种连接称为表的自身连接b) 、内连接：相对于外连接而言，进行连接的两个表对应的相匹配的字段完全相同的连接。默认空值不匹配c） 、外连接：相对于内连接而言，匹配空值，分为左外连接、右外连接和全外连接三者分别表示：匹配左边为空的情况、匹配右边为空的情况、匹配左右任意一边为空的情况作用：例如：列出所有没有员工的部门select e.ename,d.dname from emp e,dept d where e.deptno(+)=d.deptnoand e.deptno is null; 列出所有没有部门的员工select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno（+）and d.deptno is null; 列出所有员工及所有的部门，包括没有员工的部门和没有部门的员工 Select e.ename d.dname from emp e full outer join dept d on(e.deptno=d.deptno);列出不是领导的员工select manager.ename from emp worker right outer join emp manageron worker.mgr = manager.empno where worker.empno is null;5、 子查询1、概念：可以嵌在 sql 语句中的 select 语句。Sql语句执行时会先执行嵌套的子查询语句，再将查询结果带到主查询中执行。2、 应用如：找出员工中薪水最低的员工，列出姓名及其薪水：Select ename ，sal from emp where sal =(select min(sal) from emp);查询谁的薪水比本部门的平均薪水高select e.ename e.sal a.avgsal from emp e ,(select deptno, avg(sal) from emp group by deptno ) a where e.deptno=a.deptno and e.sala.avgsal;六、对表本身的操作1、 创建表 a) 、create table mytable （列1 类型 约束，列2 类型 .）;2、 复制表(包括结构和数据) a) 、create table emp_copy as select * from emp;b) 、creare table emp_copy1 as select empno,ename,sal,deptno from emp;c) 、create table emp_copy as select * from emp where 1=0;/ 后面where给的条件永远为false ，结果为只复制结构，没有数据。七、表中数据的操作1、 insert 操作格式：insert into 表名（列名1，列名2，.） values （列1值，列2值，.）；注：当插入的记录包括所有列时，不用再表名后注明列名如：假设有表student 只包含两列为stu_id，stu_name时，插入记录 insert into student values （1001，张三）；a) 、insert 一条记录 Insert into emp（empno，ename，sal） values （1001，张三，1500）；b) 、insert 多条记录：后面接上select的结果（要求结果和要插入的表结构一致）Insert into student select empno，ename from emp；2、 update 更新数据格式：update 表名 set 列名=此列新的值 where 过滤条件a) 、将7788号员工的薪水加800元Update emp set sal =sal+800 where empno=7788；b） 、改7788号员工的薪水为1800同时改job 为salsmanUpdate emp set sal = 1800,job = salsman where empno=7788;/ 多个字段用“，”隔开4、 delete 删除表中数据 格式：delete 表名 where 过滤条件；a) 、删除emp表中部门号为10的数据delete emp where deptno=10；注意：当表中数据存在被子表应用时，是不能直接删除的 ，如：delete * from dept where deptno =10;/子表 emp中存在deptno=10的引用但如果子表中有on delete 约束时 就可以 如：create table student_wan(id number(10) primary key ,name varchar2(30) ,mid number(2), constraint stunwan_mid_fk foreign key (mid) references major_wan(mid) on delete set null);/ 删除部门10 后 子表中相关数据将设置为空。5、 事务的概念：当开始执行DML操作时，就开始了一个事务。当事务开始后正在操作的数据是加锁的，别人不能同时操作（挂起）。事务的结束动作就是 commit； DDL, DCL语句执行会自动提交 commit。 sqlplus 正常退出是会自动做提交动作的 commit;，当系统异常退出时，会执行回滚（撤销改动）操作 rollback;。 一个没有结束的事务，叫做活动的事务 (active transaction),活动的事务中修改的数据时，只有本会话(session)才能看见。 用处：操作过程中可以用savepoint保存状态，再用rollback to 返回设置的保存点。（前提是没有提交commit）如：delete from emp；/删除emp中数据 Savepoint A； Rollback to A；/数据恢复了八、数据库的主要对象1、 视图：（很向软连接）a) 、创建视图：create or replace view_name as .;Create or replace view v_emp_copy as select ename ，sal ， from emp_copy；/ 取emp_copy表的ename和sal字段创建视图 v_emp_copy；/ 视图是虚的，如果没有就创建，如有就replace 注：Create or replace view v_emp_copy as select deptno，avg（sal）from emp_copy group by deptno；/ 错;/ 视图的来源中的数据不是基表原始数据，而是计算后的数据，这样的视图叫复杂视图，复杂视图中avg（sal）不能作为列名，需要给出别名如下：Create or replace view v_emp_copy as select deptno，avg（sal） avg_sal from emp_copy group by deptno；b） 、和查询表一样查询视图Select * from v_emp_copy ；c） 、删除视图:Drop view view_name;注：此时往表base table 基表）中插入数据时：表中没有变化，示图中的数据发生改变，从示图中插数据时相对应的表会发生改变:往示图中插数据时，会直接插进基表中，查看示图中的数据时，相当于就是执行创建时的select语句。简单示图：能进行DML操作。复杂示图：来源于多张表，不能执行DML操作。2、 数据字典数据字典的种类：(*表示table、view等对象user_*; 当前用户下的表、视图等对象all_*;当前用户的和可访问的其他用户下的表、视图等对象dba_*;数据库下所有用户下的表、视图等对象注：dba_*包含all_*,all_*包含user_*;a) 、用户名下的数据表：user_tables（只读的，系统自动管理）Select count(*) from user_tables;/结果 n个表Select table_name from user_tables where rownum1000);. 十、Oracle 中的伪列1、伪列就像 Oracle 中的一个表列，但实际上它并未存储在表中。伪列可以从表中查询，但是不能插入、更新或删除它们的值。2、常用的伪列：rowid 和 rownum。 rowid：数据库中的每一行都有一个行地址，rowid 伪列返回该行地址。可以使用rowid 值来定位表中的一行。通常情况下，rowid 值可以唯一地标识数据库中的一行。 rowid