oracle实用教程.pdf

【实例简介】
基础和实例、维护。
grant select on emp to xiaoming 希望 Xiaoming用户可以去修改 scott的emp表 grant update on emp to xiaoming 希望 XIaomIng用户可以去修改/删除,查询,添加 scott的emp表 grant all on emp to xiaoming scot希望收回 XIaomIng对emp表的查询权限 revoke select on emp from xiaoming /对权限的维护。 希望 xIaoming用户可以去查询 scott的emp表/还希望 Xiaoming可以把这个权限继续给别人。 如果是对象权限,就加入 with grant option grant select on emp to xiaoming with grant option 我的操作过程 SQL> conn scott/tiger 已连接 SQL> grant select on scott. emp to xiaoming with grant option 授权成功。 SQL> conn system/p; 已连接。 SQL> create user xiaohong identified by m12 用户已创建。 SQL> grant connect to xiaohong 授权成功。 SQL> conn xiaoming/m12; 已连接。 SQL> grant select on scott. emp to xiaohong; 授权成功。 如果是系统权限。 system给 XIaomIng权限时: grant connect to xiaoming with admin option 问题:如果scot把 XIaomIng对emp表的查询权限回收,那么 xiaohong会怎样? 答案:被回收 下面是我的操作过程 SQL> conn scott/tiger 已连接。 SQL> revoke select on emp from xiaoming 撤销成功。 QL> conn xiaohong/m123 已连接。 SQL> Select* from scott. emp select *from scott. emp 第1行出现错误: oRA-00942:表或视图不存在 结果显示:小红受到诛连了。 使用 profile管理用户口令 概述: profile是口令限制,资源限制的命令集合,当建立数据库的, oracle会自动建立名称为 default的 profile。当建立用户没 Oracle笔记 有指定 profile选项,那么 oracle就会将 default分配给用户 1账户锁定 概述:指定该账户(用户)登陆时最多可以输入密码的次数,也可以指定用户锁定的时间(大)般用dba的身份去执行该命令。 例子:指定 scott这个用户最多只能尝试3次登陆,锁定时间为2天,让我们看看怎么实现。 创建 profile文件 SQL> create profile lock account limit failed login attempts 3 password lock time 2: SQL> alter user scott profile lock_account 2给账户(用户)解锁 SQL> alter user tea account unlock, 3终止口令 为了让用户定期修改密码可以使用终止口令的指令来完成,同样这个命令也需要dba的身份来操作。 例子:给前面创建的用户tea创建一个 profile文件,要求该用户每隔10天要修改自己的登陆密码,宽限期为2天。看看怎么徹。 SQL> create profile myprofile limit password life time 10 password grace time 2: SQL> alter user tea profile myprofile 口令历史 概述:如果希望用户在修改密码时,不能使用以前使用过的密码,可使用口令历史,这样 oracle就会将口令修改的信息存放到 数据字典屮,这样当用户修改密码时, oracle就会对新旧密码进行比较,当发现新旧密码一烊时,就提示用户重新输入密码。 例子: 1)建立 profile SQL>create profile password- history limit password life_ time 10 password _grace_ time 2 password_reuse_time 10 password reuse time指定口令可重用时间即10天后就可以重用 2)分配绘某个用户 删除 profile 概述:当不需要某个 profile文件时,可以删除该文件。 SQL> drop profile password history【 casade】 注意:文件删除后,用这个文件去约束的那些用户通通也都被群放∫。 加了 casade,就会把级联的相关东西也给删除掉 ± oracle表的管理(数据类型,表创建删除,数据CRUD操作) 期望目标 1.掌握 oracle表的管理(创建/维扩 2.掌握对 oracle表的各和查询技巧 3.学会创建新的 oracle数据库 oracle的表的管理 表名和列的命名规则 必须以字母开头 ·长度不能超过30个字符 不能使用 oracle的保留字 只能使用如下字符A-Z,a-z,0-9,$,#等 oracle支持的数据类型v 字符类 char 定长最大2000个字符 Oracle笔记 例子:char(10)‘小韩’前四个字符放‘小韩’,后添6个空格补全如‘小帏 varchar2(20)变长最大4000个字符。 例子: varchar2(10)‘小韩’ oracle分配四个字符。这样可以节省空间。 cob( character large ob ject)字符型大对象最大4G char査询的速度极快浪费空间,査询比较多的数据用。 varchar节省空间 数字型 mber范围-10的38次方到10的38次方 丁以表示整数,也可以表示小数 表小一位小数有5位有效数,2位小数 范围:-999.99到999.9 number(5) 表示一个5位整数 范围9999到-99999 日期类型 dato包含年月日和时分秒 oracle默认格式1-1月-1999 t1 mestamp这是 oracle9i对date数据类型的扩展。可以精确到亳秒 v图片 blob二进制数据可以存放图片/声音4G一殷米讲,在真实项目中是不会把图片和声音真的往数据库里存放,一般存放 图片、视频的路径,如果安全需要比较高的话,则放入数据库 怎样创建表 建表 学生表 create table student 表名 nunber(4) 学号 Xm varchar2 (20) 姓名 char(2 阡别 birthday dat 出生日期 sal humor(7,2)奖学金 班级表 CREATE TABLE class( classId NUMBER (2) cName VARCHAR2(40) 修改表 添加一个字段 SQL>ALTER TABLE Student add (classId NUMBER (2)) v修改一个字段的长度 SQL >ALTER TABLE student MODIFY (xm VARCIIAR2(30)) ν修改字段的类型/或是名字(不能有缴据)不建议做 SQL>ALTER TABLE student modify (xm CHAR(30)) 删除一个宇段不建议做(删∫之后,顺序就变∫。加就没问题,应为是加在后面) SQL>ALTER TABLE Student DROP COLUMN sal ace笔记 5 V修改表的名字很少有这种需求 SQL>RENAME student to stu v删除表 SQL>DROP TABLE student 添加数据 所有字段都插入数据 INSERT INTo student values(A001’,’张三’,’男’,'01-5月-05’,10) oracle中默认的日期格式‘ dd-mon-yy’d日子(天)mon月份yy2位的年096月99’1999年6月9日 修改日期的默认格式(临时修改,数据库重启后仍为默认;如要修改需要修改注册表 ALTER SESSION SET NLS DATE FORMAT yyyy-mm-dd 修改后,可以用我们熟悉的格式添加日期类型: INSERT INTo student values('A002’,'MIRE,’男”,'1905-05-06,10) 插入部分字段 INSERT INTO student(xh,xm,sex) VALUES(A003,’ JOHN,’女 插入空值 INSERT INTO student(xh, xm, sex, birthday) VALUES (A004', MARTIN,' null) 问题来了,如果你要查询 student表里 birthday为nul1的记录,怎么写sq1呢? 错误写法: select* from student where birthday=null 正确写法: select* from student where birthday is nul 如果要查询 birthday不为nu11,则应该这样写: select from student where birthday is not null 修改数据 ν修改一个字段 〕 ate student set sex-女 WHERE Xh-A001 修改多个宁段 PdatE student set sex=’男, birthday='1984-04-01’ WHERE Xh=A001 修改含有nul值的数据 不要用=nul1而是用 is null seleCt k From student Where birthday Is null 删除数据 DELETE FROM student 删除所有记录,表结构还在,写日志,可以恢复的,速度慢。 Delete的数据可以恢复。 savepoint a;--创建侏存点 DELETE FROM student rollback to 恢复到保存点 个有经验的DBA,在确倮完成无误的情况下要定期创建还原点。 DROP TABLE student;-删除表的结构和数据: delete from student Where xh=’A001;-删狳一条记录 truncate table student;--删除表中的所有记录,表结构还在,不写日志,无法找回删除的记录,速度快。 5.0 racle表查询(1) 在我们讲解的过程中我们利用 scott用广存在的几张表(emp,dept)为大家演示如何使用 select语句, select语句在软件编 程中非常冇用,希望大家好好的掌握 Oracle笔记 6 emp雇员表 clerk普员工 salesman 销售 manager经理 anal yst分析师 president总裁 mgr上级的编号 hiredate入职时间 sal月工资 COm奖金 deptno部门 dept部门表 deptno部门编号 accounting财务部 rescarch研发韶 operations业务部 1oc部门所在地点 sagrada 工资级别 ade 级别 losa l 最低工资 sisal 最高工资 简单的查询语句 v查看表结构 DESC emp 查询所有列 SELECt FrOM dept 切忌动不动就用 select* SET TIMING ON;打开显示操作时间的开关,在下面显示查询时间 CREATE TABLE uSers(userId VARCHAR2(10), uName VARCHAR2(20), uPassw VARCHAR2(30)) INSERT INTO users values(a0001,’啊啊啊啊,’ aaaaadadaaaaaaaaaaa aaaa’) 从自己复制,加大数据量大概几万行就可以了可以用来测试sq1语句执行效率 INSERT INTO users (userId, UNAME, UPASSW)SELECT FROM users SELECT COUNT(*) FROM users;统计行数 ∨查询指定列 SELECT ename, sal, job, deptno FRoM emp ν如何取消重复行 DISTINGT SELECT DISTINCT deptno, job FROM emp ?查洵 SMITH所在部门,工作,薪水 seleCt deptno, job, sal FRoM emp WHERE ename SMITH 注意: oracle对内容的人小写是区分的,所以 ename='SMIT和 ename= smith’是不同的 ace笔记 使用算术表达式nv1u11 问题:如何显示每个雇员的年工资? select Sal*13+nvl(com,0)*13"年薪”, ename, comm FROM emp; v使用列的别名 SELECT ename"姓名",sal来12AS"年收入" FROM emp; 如何处理nu11值 使用nvl函数来处理 ν如何连接字符串(|) SELECT ename ,is a'I job FROM emp v使用 where子句 问题:如何显示工资高于3000的员工? SELECT i FROM emp WHERE sal 3000 问题:如何查找1982.1.1后入职的员工? SELECT ename, hiredate FROM emp WhEre hiredate >1-1 -1982 问题:如何显示工资在2000到3000的员工? SELECT cname, sal FROM emp WheRE sal >-2000 AND sal < 3000 如何使用like操作符 %;表示0到多个字符:表小任意单个字符 问题:如何显示首字符为S的员工姓名和工资? SELECT ename, sal FroM emp WhErE ename like S% 如何显示第三个字符为人写0的所有员工的姓名和工资? SELECT ename, sal FROM emp WHERE ename Iike O% V在 where条件中使用in 问题:如何显示 empno为7844,7839,123,456的雇员情况 SELECt FRom emp WhERE empno in(7844, 7839, 123, 456) v使用 is null的操作符 问顾:如何显示没有上级的雇员的情况? 错误写法: select* from emp where mgr- 正确写法: SELECT* FROM cmp WherE mgr is nu1l 6. oracle表查询(2) ν使用逻辑操作符号 问题:查询工资高于500或者是岗位为 MANAGER的雇员,同时还要满足他们的姓名首字母为人写的J? SELECT k FROM emp WHERE(sal )500 or job= MANAGER') and ename L I KE ' J%) ν使用 order by字句默认Esc 问题:如何按照工资的从低刭高的顺序显示雇员的信息? SELECT k From emp order by sal 问题:按照部门号升序而雇员的工资降序排列 SELECT k FroM emp OrdeR by deptno, sa dESc ν使用列的别名排序 问题:按年薪排序 select ename,( saltnvl(comm,0))*12"年新" from emp order by"年新"asc 别名需要使用“”号圈中,英文不需要“”号 Oracle笔记 8 V分页查 等学了子査询再说吧。。。ε。。。。 Clear清屏命令 oracle表复杂查询 说明 在实际应用中经常需要执行复杂的数据统计,经常需要显示多张表的效据,现在我们给大家介绍较为复杂的 select语句 数据分组一-max,min,avg,sum, count 问题:如何显示所有员工中最高工资和最低工资? SELECT MAX (sal), min(sal) FROM emp e 最高工资那个人是谁? 错误写法: select ename, sal from emp where sal-max(sal) 正确写法: select cname, sal from cmp where sal-( select max(sal) from cmp 注意: select ename,max(sa) from emp;这语句执行的时候会报错,说ORA0937:非单组分组函数。因为max是分组函数, 而 ename不是分组函数 但是 select min(sal1),max(sel) from emp;这句是可以执行的。因为min和max都是分组函数,就是说:如果列里面有一个 分组函数,其它的都必须是分组函数,合则就出错。这是语法规定的 问题:如何显示所有员工的平均工资和工资总和? 问题:如何计算总共有多少员问题:如何 扩展要求 杳询最高工资员工的名字,工作岗位 SELECT ename, job, sal FROM emp e where sal =(SELECT MAX(sal) FROM emp) 显示工资高于平均工资的员工信息 SELECT* FROM emp e where sal >(SELECI AVG(sal) FROM emp) v group by和 having子句 group by用于对查洵的结果分组统计, having」句用丁限制分组显示结果。 问顾:如何显示每个部门的平均工资和最高工资? SELECT AVG(sal), MAX(sa1), deptno From emp group by deptno (注意:这里暗藏了一点,如果你要分组查询的话,分组的字段 deptno一定要出现在查询的列表里面,否则会报错。因为分组 的字段都不出现的话,就没办法分组了) 问题:显示每个部门的每和岗位的平均工资和最低工资? SELECT min(sal), AvG(sal), deptno, job FROM emp grouP by deptno, job 问题:显示均工资低于2000的部门号和它的平均工资 SELECT AVG(sal), MAX(sa1), deptno From cmp group by deptno having AVG(sal)( 2000 v对数据分组的总结 1分组函数只能出现在选择列表、 having、 order by子句中(不能出现在were中) 2如果在 select语句中同时包含有 group by, having, order by那么它们的顺序是 group by, having, order by 3在选择列中如果有列、表达式和分组函数,那么这些列和表达式必须有一个出现在 group by子句中,香则就会出错。 kn select deptno, AVG(sal), MAX(sal)FROM emp group by deptno HAVING AVG(sal)< 2000 这里 deptno就一定要出现在 group by中 多表查询 说明 多表査询是指基丁两个和两个以上的表或是视图的查询。在实际应用中,查询单个表可能不能满足你的需求,(如显示 sales 部门位置和其员工的姓名),这种情况下需要使用到(dept表和emp表) ace笔记 问题:显小雇员名,雇员工资及所在部门的名字【笛卡尔集】? 规定:多表査询的条件是至少不能少于表的个数-1才能排除笛卡尔集 (如果有N张表联合查询,必须得有N-1个条件,才能避免笛卡尔集合) SELECT e ename, e. sal, d dname FROM emp e, dept d Where e deptno =d deptno 问题:显示部门号为10的部门名、员工名和资? select d dname, e ename, e. sal FROM emp e, dept d Where e deptno -d deptno and e. deptno-10 问题:显示冬个员工的姓名,工资及工资的级别? 先看 sagrada的表结构和记录 SQL>Select k fr GRADE LOSAL HISAL l201 1400 1401 2000 3001 9999 SELECt C cnamc, c. sal, s grade FROM emp c, salgrade s WHERE C. sal BEtWeen S losal AND S.hisal: 护展要求 问题:显小雇员名,雇员工资及所在部门的名字,并按部门排序? SELECT e ename, e. sal, d dname FROM emp e, dept d Where e deptno d deptno order by e deptno (注意:如果用 group by,一定要把e. deptno放到查询列里面) v白连接 自连接是指在同一张表的连接查询 问题:显小某个员工的上级领导的姓名? 比如显示员工“FORD’的上级 SELECT worker ename, boss. ename FROM emp worker, emp boss Where worker. mgr= boss empno AND worker ename = FORD 子查询 什么是子查询 子査询是指嵌入在其他sq1语句中的 select浯句,也叫嵌套查询 单行子查询 单行子查询是指只返回一行数据的子查询语句 请思考:显示与 SMITH同部门的所有员工? 思路 1查询出 SMITH的部门号 select deptno from cmp WHERE cnamc- SMITH 2显 SELECT FROM emp Where deptno =(select deptno from emp WHERE ename SMITH') 数据库在执行sq1是从左到右扫描的,如果有括号的话,括号里面的先被优先执行。 ∨多行」查询 多行子查询指返回多行数据的子查询 请思考:如何查询和部门10的工作相同的雇员的名字、岗位、工资、部门号 SELECT DISTINCT job FROM emp Where deptno =10 SELECT FROM emp Where job IN (SELECT DiSTINCT job FROM cmp WHere deptno- 10) (注意:个能用jcb=..,因为等号=是一对一的) 在多行子查询中使用a11操作符 Oracle笔记 【实例截图】
【核心代码】