韩顺平oracle学习笔记.doc

韩顺平oracle学习笔记第0讲：如何学习oracle一、如何学习oracleOracle目前最流行的数据库之一，功能强大，性能卓越。学习oracle需要具备一定基础：1.学习过一门编程语言（如：java ，c）2.最好学习过一门别的数据库（sql server，mysql ， access）教程推荐：oracle使用教程， 深入浅出oracle记住：欲速则不达，做任何事情要遵循他的规律，循序渐进，信心很重要成为一个oracle高手过程：理解小知识点-做小练习-把小的只是点连成线-做oracle项目-形成只是面-深刻理解Oracle基础部分：oracle基础使用； oracle用户管理； oracle表管理Oracle高级部分：oracle表的查询； oracle的函数； oracle数据库管理；oracle的权角色； pl/sql 编程； 索引，约束和事物。期望目标：1 学会安装、启动、卸载oracle2 使用sql *plus工具3 掌握oracle用户管理4 学会在oracle中编写简单的select语句第1讲：基础语法内容介绍：1.为什么学习oracle2.介绍oracle及其公司的背景3.学会安装、启动、卸载oracle4.oracle开发工具5.Sql*plus的常用命令6.oracle用户管理一、主流数据库包括：微软：sql server 和 access瑞典：mysql AB公司IBM公司：DB2美国sybase公司：sybaseIBM公司：infromix美国oracle公司：oracle(目前最流行的之一)数据库分类：小型数据库中型数据库大型数据库accessmysqlsql serverinformixSybaseOracleDB2负载量在100人内，比如板，信息系信息留言统。比如在负载日访问量5000-15000负载可以处理海量数据库sybaseoracle服务和应用程序：服务-启动OracleServiceMYORA1(MYORA1是安装oracle时起的名字各有不同)和OracleOracleHome90TNSLlistener4.卸载oracle1、先关掉oralce，net stop OracleServiceORCL(ORCL是我的实例名字，换成你的)，或者去我的电脑服务中关闭2、开始程序Oracle - oracle的版本号，我的是10ghomeOracle Installation Products Universal Installer 卸载oracle3、进注册表，regedit，删除选择HKEY_LOCAL_MACHINESOFTWAREORACLE下所有的key。HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServices、HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServicesEventlogApplication这个里面所有有oracle这个字眼的，删除4、删除c盘Program FilesOracle目录的东西以及oracle安装目录下所有的文件以及文件夹5、环境变量中删除有关oracle的classpath和path三、oracle开发工具Sqlplus是Oracle自带的工具软件，主要用于执行sql语句，pl/sql块。如何使用： 1)在开始程序oracle orachome90 application developmentsql*plus(这个是在dos下操作)2)在运行栏中输入：sqlplusw或者sqlplus即可3)在开始程序oracle orahome90Application developmentsql*plus worksheet 4)pl/sql developer 属于第三方软件，主要用于开发，测试，优化oracle pl/sql 的存储过程.如：触发器，此软件oracle不带，需要单独安装。5)oracle的企业管理器(oem oracle enterprise manager)位置在 开始程序oracleoracle oraclehome90enterprise manager console 即可启动oracle的企业管理器，是一个图形界面环境四、Sql*plus的常用命令1.连接命令(1) connect 用法：conn 用户名/密码 网络服务名as sysdba/sysoper(角色登陆)当用特权用户身份连接时，必须带上as sysdba 或是 as sysoper例：conn scott/tiger;show user; 显示当前用户2.断开连接命令(2) disconnect 说明：该命令用来断开与当前数据库的连接(3)password 说明：该命令用于修改用户名的密码。如果要想修改其他用户的密码，需要用sys/system登陆。(4) show user 说明：显示当前用户名(5) exit 说明：该命令会断开与数据库的连接，同时会退出sql*plus3.文件操作命令(1) start和 说明：运行sql脚本 案例：sql d:a.sql 或者 sqlstart d:a.sql(2) edit 说明：该命令可以编辑指定的sql脚本。 案例：sqledit d:a.sql(3) spool 说明：该命令可以将sql*plus屏幕上的内容输出到指定文件中去。 案例：sqlspool d:b.sql 并输入 sqlspool off4交互式命令(1) 说明：可以替代变量，而该变量在执行时，需要用户输入。如：sqlselect * from emp where job=&job 输入job是注意大小写(2) edit 说明：该命令可以编辑指定的sql脚本 案例： sqledit d:a.sql5.显示和设置环境变量概述：可以用来控制输出的各种格式，set show 如果希望永久的保存相关的设置，可以去修改 glogin.sql脚本(1)linesize 说明：设置显示行的宽度，默认是80个字符 sqlshow linesize sqlset linesize 90(2)pagesize 说明：设置每页显示的行数目，默认是14，用法和linesize一样，至于其他环境参数的使用也是大同小异 sqlset pagesize 8 实际得到的不是14/8=2页 而是：14/（8-3）=5页五Oracle用户的管理1.创建用户概述：在oracle中要创建一个新的用户使用create user语句，一般是具有dba（数据库管理员）的权限才能使用。create user 用户名 identified by 密码；2.给用户修改密码概述：如果给自己修改密码可以直接使用 sqlpassword 用户名 如果给别人修改密码则需要具有dba的权限，或是拥有alter user的系统权限 sqlalter user 用户名 identified by 新密码 3.删除用户概述：一般以dba的身份去删除某个用户，如果用其他用户去删除用户则需要具有 drop user的权限。比如 drop user 用户名【cascade】注意：在删除用户时，如果要删除的用户已经创建了表，就需要在删除的时候带一个参数cascade4.用户管理的综合案例概述：创建的新用户是没有任何权限的，甚至连登陆的数据库的权限都没有，需要为其指定相应的权限。给一个用户赋权限（connect登陆，dba管理员,resource建表）使用命令grant,回收权限使用命令revoke.为了给讲清楚用户管理，给大家举一个案例。案例：新建一个用户lady 并给该用户赋可登陆可创建表Sqlcreate user lady identified by lady123(密码)受登陆权限:grant connect to lady 受可创建表：grant resource to lady可以多个同时授权：grant connect，resource to lady;注意授权用户级别应该为（dba以上sys/system）还可以使用PL/SQL工具创建：文件-新建-用户现在我要把scott用户里面的emp表的权限赋给lady用户但lady只能有对emp查询权限：grant select on emp to lady;(授权用户级别应该：授权本身，或者sys/system)lady 查询emp的方式：select * from scott.emp；案例：同上我想把update的权限也赋给leng grant update on emp to leng;2)我想把emp所有的(增，删，改，查/insert,delete,update,select这四个一起可以用all代替) 操作权限赋给leng grant all on emp to leng;(此时登陆用户为scott) 案例2：这时我想把权限收回来怎么办呢？这时用关键字revoke（撤回）revoke select on emp from lady; 收回lady的查权限案例： 现在scott希望吧查询emp表权限通过lady用户继续给别的用户？Scott grant select on emp to lady with grant option;Leng grant select on scott.emp to 新建用户案例： scott想把emp表的权限从leng用户赋权给新用户yoyo;Scott grant select on emp to leng with grant option;Leng grant select on scott.emp to yoyo;Yoyo select * from scott.emp;如果是对象权限，就加入with grant option 例：grant select on emp to xiaoming with grant option如果是系统管理权限，system 给xiaoming权限时：例：grant connect to xiaoming with admin option如果scott把xiaoming 对emp表的查询权限回收，那么xiaohong会怎么样Scott-xiaoming-xiaohong ，结果就是xiaoming,xiaohong,都不能查询emp表 5.oracle用户的管理使用profile管理用户口令概述：profile是口令限制，资源限制的命令集合，当建立数据库时，oracle会自动建立名称为default的profile.当建立用户没有指定profile选项，那oracle就会将default分配给用户。(1)账户锁定概述：指定该用户锁定登陆时最多多可以输入密码的次数，也可以指定用户锁定的时间一般用dba的身份去执行该命令 例子：指定scott这个用户最多只能尝试3次登陆使用profile管理用户口令 (1)账户锁定概述：指定该用户锁定登陆时对多可以输入密码的次数，也可以指定用户锁定的时间一般用dba的身份去执行该命令 例子：指定scott这个用户最多只能尝试3次登陆,锁定时间为2天，让我们看看怎么实现。创建profile文件（相当于创建规则）sqlcreate profile aaa1 limit(aaa1创建规则的名称)failed_login_attempts 3 password_lock_time 2;(设置登陆的次数为3次及锁定时间为：2天)结果：create profile aaa1 limit failed_login_attempts 3 password_lock_time 2;Sqlalter user leng profile aaa12)给账户（用户）解锁sql alter user leng account unlock;(3)终止口令为了让用户定期修改密码可以使用终止口令的指令来完成同样这个命令也需要dba身份来操作。例子：给前面创建的用户lady创建一个profile文件，要求该用户每隔10天要修改自家的登陆密码，宽限期为2天。看看怎么做。sql create profile myprofile limit password_life_time 10 password_grace_time 2;sql alter user leng profile myprofile;sql alter user leng account unlock;6口令历史概述：如果希望用户在修改密码时，不能使用以前使用过的密码，可使用口令历史，这样oralce就会将口令修改的信息存放到数据字典中，这样当用户修改密码时，oracle就会对新旧密码进行比较，当发现新旧密码一样时，就提示用户重新输入密码。例子： 1)建立profile sqlcreate profile password_history limit password_life_time 10 paaword_grace_time 2 password_reuse_time 10; create profile /创建的文件名。 Password_life_time/使用期为10天 passwrod_grace_time/宽限期为两天 password_reuse_time/指定口令可重用时间即10天后就可以重用： 修改密码在10天内不能与旧密码一样，10天就可以重用 2)分配给某个用户。删除profile概述:当不需要某个profile文件时，可以删除该文件。sqldrop profile password_history 【cascade】第2讲：表内容介绍：1、 oracle的表的管理（创建和维护）2、 基本查询3、 复杂查询4、 Oracle数据库的创建一表名和列的命名规则：表名必须以字母开头长度不能超过30字符不能使用oracle的保留字只能使用如下字符 A-Z,a-z,0-9,$,#等二oracle支持的数据类型1.字符型char 定长 最大2000字符例子：char(10) 小韩 前四个字符放小韩 ，后添6 个空格补全。 小韩 char型查询速度极快varchar2(20) 变长 最大4000字符。例子：varchar2(20) 小寒 oracle 分配四个字符。这样可以节省空间 varchar2 会自动去匹配clob 字符型大对象最大4G2.数字型number 范围 负 (10 的38次方) 到10的38次方可以表示整数，也可以表示小数。number(5,2)：括号里面的数据可改变表示一个小数有5位有效数，2位小数范围：-999.99999.99范围 负999.99 到 999.99number(5)表示一个五位整数范围 负99999 到 99999 3日期类型date 包括年月日和时分秒（一般项目足够用）timestamp 这是oracle9i对date数据类型的扩展(精度更高)4、二进制(可存放图片，声音，视频文件)blob 二进制数据 可以存放图片/声音 4G一般来讲不会把图片，声音，视频文件网数据库里面存放，一般存放他们的路径；如果这些数据保密性高，可以考虑存在数据库里面。三、表的管理1. 建表-班级表 sql create table class(-表名 classId number(2),-班级编号 cname varchar2(40),-班级名称 );班级表：sql create table class(classId number(2),cname varchar2(40)；-学生表 sql create table student (-表名 xh number(4),-学号 xm varchar2(20),-姓名 sex char(2),-性别 birthday date,-出生日期 sal number(7,2)-奖金 );方法2: 利用pl/sql developer 工具建在左边的工具栏的对象栏-选中Tables文件夹右键-新建填写内容单击应用分析存储栏：表示你要把所建的表存放在哪里，默认放在system这个表空间。表空间：表存放在一个文件里，存放在一个磁盘里，不会在内存了，最终会放在数据库某一空间里。所以表空间就是存放表的一个空间列：索引:主要用于提高速度2.修改表添加一个字段 sql alter table student add(classid number(2);查看表结构desc student；修改字段的长度 sql alter table student modify (xm varchar2(30);修改字段的类型 (不能有数据) sql alter table student modify (xm char(30);删除一个字段 sql alter table student drop column sal;修改表的名字 sql rename student to stu;删除表 sql drop table student；3.添加数据所有字段都插入insert into student values(A001,刘权,男,07-5月-89,10); ORACLE中默认的日期格式DD-MON-YY dd 日子(天) mon 月份 yy 2位的年 ？1996年6月9号改日期的默认格式 alter session set nls_date_format =yyyy-mm-dd;修改后，可以用我们熟悉的格式添加日期类型insert into student values(A002,李四,男,1990-05-28,10);插入部分字段Insert into student(xh,xm,sex) values(A002,john,女);插入空值Insert into student (xh,xm,sex,birthday)values(A003,MARTIN,男,null);查出没有生日的人Select * from student where birthday is null;查看有生日的人Select * from student where birthday is not null;4.修改数据改一个字段update student set sex=女 where xh=A001;修改多个字段update student set sex=男,birthday=1996-11-12 where xh=A001;修改含有null值的数据注意：select时查询null时，要用 is null5.删除数据删除数据delete from student;(只删数据不删除表，表结构还存在)数据回滚rollback;(恢复数据) 在rollback 之前要先设置savapoint才能恢复数据例：delete from student;Savapoint aa;Rollback to aa;有经验的dba工作之前一般会做一个savapoint删除所有记录，表结构还在，写日志，可以恢复的，速度慢drop table student;删除表的结构和数据delete from student where xh=A001; 删除一条记录truncate table student;删除表中的所有记录，表结构还在，不写日志，无法找回删除的记录，速度快四、表的查询介绍在我们讲解的过程中我们利用scott用户存在的几张表(emp,dept)为大家演示如何使用 select语句，select语句在软件编程中非常的有用，希望大家好好的掌握。select * from emp；查询结果如下：EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO- - - - - - - - 7369 SMITH CLERK 7902 1980-12-17 800.00 20 7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30 7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30 7566 JONES MANAGER 7839 1981-4-2 2975.00 20 7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30 7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30 7782 CLARK MANAGER 7839 1981-6-9 2450.00 10 7788 SCOTT CLERK 7566 1987-4-19 800.00 20 7839 KING PRESIDENT 1981-11-17 5000.00 10 7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30 7876 ADAMS CLERK 7788 1987-5-23 1100.00 20 7900 JAMES CLERK 7698 1981-12-3 950.00 30 7902 FORD ANALYST 7566 1981-12-3 3000.00 20 7934 MILLER CLERK 7782 1982-1-23 1300.00 10 14 rows selectedselect * from dept；结果为：DEPTNO DNAME LOC- - - 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON1、简单的查询语句查看表结构sql desc dept;查询所有列 select * from dept; 不要经常用* 查询，因为速度慢查询指定列select ename,sal,job,deptno from emp;如何取消重复行select distinct deptno,job from emp;(消除部门重复工作或清楚不重复地显示所有工作种类)?查询SMITH的薪水，工作，所在部门Select sal,job,deptno from where ename=SMITH删掉表中的某一列：alter table tablename drop column columnname; -column：列Set timing on 打开执行命令时间自我复制表内容：insert into student（xuehao,xingming,sex）select * from student;Select count(*) from student; 查看student表一共有多少行2、使用算数表达式？显示每个雇员的年工资SQL select sal*12,ename from emp; SAL*12 ENAME- - 9600 SMITH 19200 ALLEN 15000 WARD 35700 JONES 15000 MARTIN 34200 BLAKE 29400 CLARK 9600 SCOTT 60000 KING 18000 TURNER 13200 ADAMS 11400 JAMES 36000 FORD 15600 MILLER 14 rows selected12个月工资加奖金：SQL select ename,sal*12+comm*12from emp; ENAME SAL*12+COMM*12- -SMITH ALLEN 22800WARD 21000JONES MARTIN 31800BLAKE CLARK SCOTT KING TURNER 18000ADAMS JAMES FORD MILLER 14 rows selected对比发现comm为空的员工都没有工资了。Oracle如果一个值为null参见运输整个之为空正确写法为：select sal*12+nvl(comm,0)*13,ename from emp;AL*12+NVL(COMM,0)*13 ENAME- - 9600 SMITH 23100 ALLEN 21500 WARD 35700 JONES 33200 MARTIN 34200 BLAKE 29400 CLARK 9600 SCOTT 60000 KING 18000 TURNER 13200 ADAMS 11400 JAMES 36000 FORD 15600 MILLER 14 rows selected说明：nvl(comm,0) 如果comm为null则用0代替；使用列的别名select ename “姓名”,sal*12 as “年收入” from emp;oracle 中字符如果是英文的用 裹起来，如果是中文的则用“”括起来如何处理null值使用nvl函数处理select ename “姓名”,sal*12+nvl(comm,0)*13 as “年收入” from emp如何连接字符串（|）Select ename | is a | job from emp;3、使用where子句？如何显示工资高于3000的员工 答案：Select ename，sal from emp where sal3000;？如何查找1982.1.1后入职的员工 注意：1-1月-1982答案：select ename ，hiredate from emp where hiredate1-1月-1982;？如何显示工资在2000到2500的员工情况答案：select ename，sal from emp where sal=2000 and sal500 or job=manager) and ename like J%使用order by 子句(desc从高到低，默认是从低到高asc)？如何按照工资的从低到高的顺序显示雇员的信息select ename,sal from emp order by sal; （从高到低后面加desc）？按照部门号升序而雇员的工资降序排列 (order by deptno,sal desc)答案：select * from emp order by deptno asc,sal desc;使用列的别名排序select ename,(sal+nvl(comm,0)*12 as 年薪 from emp order by年薪 asc;列名需要使用“”号圈中5.复杂查询说明：在实际应用中经常需要执行复杂的数据统计，经常需要显示多张表的数据，现在我们给大家介绍较复杂的select语句 数据分组max,min,avg,sum,count? 如何显示所有员工中最高工资和最低工资 select max(sal),min(sal) from emp;？显示所有员工的平均工资和工资总和 select avg(sal),sum(sal) from emp;？计算共有多少员工扩展要求：？请显示工资最高的员工的名字，工作岗位select ename,job from emp where sal=(select max(sal) from emp);？请显示工资高于平均工资的员工信息select ename,sal from emp where sal(select avg(sal) from emp);6. group by 和 having 子句group by 用于对查询的结果分组统计，having 子句用于限制分组显示结果。?如何显示每个部门的平均工资和最高工资select avg(sal),max(sal),deptno from emp group by deptno;说明：前后必须有deptno这个词？显示每个部门的每种岗位的平均工资和最低工资select avg(sal),min(sal), deptno,job from emp group by deptno,job;？显示平均工资低于2000的部门号和他的平均工资select avg(sal),max(sal),deptno from emp group by deptno having avg(sal)havingorder by如 select deptno , avg(sal) , max(sal) from emp group by deptno having avg(sal)2000 order by avg(sal); 在选择列中如果有列，表达式，和分组函数，那么这些列和表达式有一个出现在group by 子句中 如 select deptno , avg(sal) , max(sal) from emp group by deptno having avg(sal) select * from emp where deptno = (select deptno from emp where ename = SMITH);数据库在执行sql时，是从左到右执行的，但我们在写的时候一般是从右到左。*多行子查询多行子查询只返回多行数据的子查询请思考：如何查询和部门10的工作相同的雇员的名字，岗位，工资，部门号select ename,job,sal,deptno from emp where job in(select job from emp where deptno=10);*在多行子查询中使用all操作符 请思考：如何显示工资比部门30的所有员工的工资高的员工的姓名，工资和部门号select ename,sal,deptno from emp where salall (select sal from emp where deptno=30);扩展要求：大家想想还有没有其他的查询方法？SQL select ename,sal,deptno from emp where sal(select max(sal) from emp where deptno=30);这两个方法中max()比all 效率高*在多行子查询中使用any 操作符请思考：如何显示工资比部门30的任意一个员工的工资高的员工的姓名，工资和部门号。select ename,sal,deptno from emp where salany (select sal from emp where deptno=30);扩展要求：大家想想还有没有其他的查询方法select * from emp where sal(select min(sal) from emp where deptno=30);*多列子查询单行子查询是指子查询只返回单列，单行数据，多行子查询是指返回单列多行的数据，都是正对单列而言的，而多列子查询则是只查询返回多个列数据的子查询语句。请思考：如何查询与smith的部门和岗位完全相同的所有雇员SQL select * from emp where deptno=(select deptno from emp where ename=SMITH) and job=(select job from emp where ename=SMITH);或者：SQL select * from emp where (deptno,job)=(selec