学习Oracle.doc

成为一个oracle高手的秘诀就是：理解小的知识点做小练习把小的知识点连成线(知识线) 做oracle项目把知识线形成知识面深刻理解信心很重要参考书：Oracle实用教程 深入浅出OracleSql*plus常用命令(1)conn 连接命令 conn user/password(2)disconnect 说明：断开与当前数据库的连接 disc user(3)password 说明：修改用户密码。如果想要修改其他用户的密码，需要用sys/system登录Passw 回车Passw user(4)show user :显示当前用户名(5)exit 断开与当前数据库的连接，同时会退出文件操作命令(1) start和 说明：运行sql脚本 案例：sql d:a.sql或者sqlSTART D:a.sql (2) edit 编辑指定的sql脚本 案例：sqlstart d:aa.sql(3) spool 可以将sql*plus屏幕上的内容输出到指定文件中去 案例：sqlspool d:b.sql 并输入 sqlspool off 交互式命令(1)& 说明：可以替代变量，而该变量在执行时，需要用户输入。Sqlselect * from emp where job =&job/ 没用，基本没用显示和设置环境变量概述：可以用来控制输出的各种格式，set show如果希望永久的保存相关的设置，可以去修改glogin.sql脚本(1) linesize 说明：设置显示行的宽度，默认是80个字符 sqlshow linesize sqlset linesize 90 /貌似没用(2) pagesize 说明：设置每页显示的行数目，默认是14用法和linesize一样SqlShow pagesize;Sqlset pagesize number;/在打印报表（有要求）的的时候使用Oracle的用户管理创建用户(dba身份)：create user fanfan identified by m123;Create user关键字。Identified by关键字。创建的新用户是没有任何权限的，甚至连登陆数据库的权限也没有，需要为其指定相应的权限。给一个用户赋权限使用命令grant， 例:grant connect to fanfan; 一百四十多种权限。回收权限使用命令revoke。 使用profile管理用户口令概述：profile是口令限制，资源限制的命令集合，当建立数据库时，oracle会自动建立名称为default的profile，当建立用户没有指定profile选项，那oracle就会将default分配给用户。（1） 账户锁定 概述：指定该账户（用户）登录时最多可以输入密码的次数，也可以指定用户锁定的时间（天）一般用dba的身份去执行该命令 例子：指定tea这个用户最多只能尝试三次登录。锁定时间为2天，让我们看看怎么实现。创建profile文件Sqlcreate profile lock_account limitFailed_login_attempts 3 password_lock_time 2;Sqlalter user tea profile lock_account;（2） 给账户（用户）解锁Sqlalter user tea account unlock;(3) 终止口令 为了让用户定期修改密码可以使用终止口令的指令来完成，同样这个命令也需要dba身份来操作。例子：给前面创建的用户xiaohong创建一个profile文件，要求该用户每隔十天要修改自家的登录密码，宽限期为2天。看看怎么做。Sqlcreate profile myprofile limit password_life_time 10 password_grace_time 2;Sqlalter user xiaohong profile myprofile(4) 口令历史概述：如果希望用户在修改密码时，不能使用以前使用过的密码，可以使用口令历史，这样oracle就会将口令修改的信息存放到数据字典中，这样当用户修改密码是，oracle就会对新旧密码进行比较，当发现新旧密码一样时，就提示用户重新输入密码。例子：1） 建立profileSqlcreate profile password_history limit password_life_time 10 password_grace_time 2 password_reuser_time 10;Password_reuse_time /指定口令可以重用时间即10天后就可以重用。2） 分配给某个用户。00删除profile概述：当不需要某个profile文件时，可以删除该文件。Sqldrop profile password_history 【cascade】 系统权限1. 用户对数据库的相关权限。Create session权限，包含了这种权限，才能登陆到数据库。系统权限批量赋给一个角色，例，create session 赋给角色connectConnect角色里包含了七个权限。 对象权限1. 用户对其他用户的数据对象操作的权限。包含：select,insert,update,delete,all,create index权限赋权限。对权限的维护。 希望fanfan用户可以去查询zy的T_SYS_ROLE表，还希望fanfan可以把这个权限继续给别人。 Grant select on T_SYS_ROLE to fanfan with grant option;with grant option 关键字，能继续往下赋权。 数据对象比如表，视图，过程Connect ：是一个角色。角色分两种：oracle最精华的部分是角色控制和权限管理。1. 自定义角色Connect,dba,resource（让其在任何一个表空间建表）2. 预定义角色希望fanfan可以查询zy数据库中的S_test表。1.Grant select on S_test to fanfan 授权。 方案Select * from zy.S_test;Oracle 里是根据用户来组织数据的。修改密码：修改自己的密码sqlpassword；修改别人的密码 sqlpassword user;删除用户：一般以dba的身份去删除某个用户，如果用其他用户区删除用户需要具有drop user的权限；比如：drop user用户名cascade在删除用户的时候，注意：如果删除的用户已经创建了表，那么就需要在删除时 带一个参数 cascade；级联删除。 系统权限2. 用户对数据库的相关权限。 对象权限1.用户对其他用户的数据对象操作的权限。Varchar2习题：Define命令可以用于定义哪种变量。A number B charC varchar D dateSql*plus 命令可以访问数据库吗？A 可以 B 不可以建立sql脚本disp_emp.sql完成：1） 显示部门号、雇员号、雇员名、并按部门号进行排序2） 页头为“雇员报表”3） 每个部门只显示一次部门号4） 设置行宽为60个字符5） 设置页的总显示行数为40行6） 显示雇员工资时要带有本地货币符号7） 在select语句后清除所有选项设置图 星期三 5月15 第一页 雇员报表Pton empon ename sal10 7782 clark rmb2450.0020 7349 simth rmb2000.0030 7499 allen rmb1600.00已选择14行/创建用户练习1. 创建用户tea，stu,并给这两个用户resource，connect权限2. 使用scott用户把对emp表的select权限给tea使用tea查询 scott的emp表使用scott用户把对emp表的所有权限赋给tea使用tea更新/删除/插入 scott的emp表使用scott收回权限3. 想办法将让tea把自己拥有的对scott.emp的权限转给stu；使用stu查询scott用户的emp表使用tea收回给stu的权限收获：更新语句UPDATE table_nameSET column_name = new_value（新值）WHERE column_name = some_value（原值）删除语句delete from table_name where column_name=;插入语句insert into table_name values(,);Time 5.3 星期四1. Oracle的表的管理2. 基本查询3. 复杂查询4. oracle数据库的创建对数据库而言，最难的是查询这块。1. 掌握oracle表的管理（创建/维护）2. 掌握对oracle表的各种查询技巧3. 学会创建新的oracle数据库表名和列的命名规则【1】 必须以字母开头【2】 长度不能超过30个字符【3】 不能使用oracle的保留字【4】 只能使用如下字符 A-Z,a-z,0-9,￥，#等Oracle的数据类型字符型Char 定长 最大2000字符 例子：char(10) 小韩前四个字符放小韩，后添6个空格补全查询速度极快 如果一个字段老是被查询，用char，例如身份证号(定长)查询，速度极快。Varchar2（10） 变长 最大4000字符例子：varchar2(20)小寒 oracle分配四个字符。这样可以节省空间 但是效率低clob(character large objiect) 字符型大对象最大4G数字型Number 范围 -10的38次方 10的38次方 可以表示整数，也可以表示小数。 Number(5,2)表示一个小数有五位有效数，2位小数范围 -999.99 999.99number(5)表示一个5位整数范围 -99999 99999 日期类型Date 包含年月日和时分秒Timestamp这是oracle9i对date数据的扩展 /时间戳。图片blob 二进制数据 可以存放图片/声音 4G实际上在真实的项目里，很少存放图片，声音，只是存放一个路径，图片和声音放在另外一个文件。出于安全考虑，才存放在数据库里 Oracle表的管理-怎样创建表-学生表 Sqlcreate table student (-表名 Xh number(4), -学号 Xm varchar2(20) , -姓名 /可以加长，缩短 Birthday date, -出生日期 Sal number(7,2) 奖学金);-班级表Sqlcreate table class(Classid number(2),Cname varchar2(20); 修改表添加一个字段Sqlalter table student add (classid number(2);修改字段的长度Sqlalter table student modify (xm varchar(30);修改字段类型/或是名字(不能有数据)Sqlalter table student modify (xm char(30);删除一个字段Sqlalter table student drop column sal; /建议不要轻易使用修改表名字Sqlrename student to stu; /很少有需求删除表Sqldrop table student;Oracle表的管理-添加数据Insert into student values (A001,张三,男,09-6月-05,10); ORACLE中默认的日期格式DD-MM-YY，dd 日子（天），mm 月份 yy 2位的年 09-6月-991999年6月9号。改日期的默认格式Alter session set nls_date_format = yyyy-mm-dd;修改后，可以用我们熟悉的格式添加日期类型；Insert into student values (A002,MIKE,男,1999-05-06,10);插入部分字段Insert into student (xh,xm,sex) values (A003,JOHN,女)；插入空值Insert into student(xh,xm,sex,birthday)values(3,aa,男,null);查询生日为空select * from student where birthday is null（is not null）;修改数据修改一个字段，修改多个字段Update student set sex=女,sal=sal/2 where xh=A001;修改含有null值的数据 is null； 删除数据Delete from student;删除所有记录，表结构还在，写日志，可以恢复的，速度慢Drop table student; 删除表的结构和数据Delete from student where xh=A001;删除一条记录Truncate table student;删除表中的所有记录，表结构还在，不写日志，无法找回删除的记录，速度快设置保存点 sqlsavepoint aa;回滚操作 sqlrollback to aa; 可以恢复删除的内容Oracle表基本查询 -介绍工欲善其事，必先利其器Select 语句非常有用，希望好好掌握查询表结构 Sqldesc table_name;查询所有列 Sqlselect * from table_name; 不要轻易使用select *查询指定列 Sqlselect ename,sal from emp;如何取消重复行SqlSelect distinct deptno,job from job;查看有多少行select count(*)from users2;疯狂赋值 insert into users2 (userId,username,userPass)select * from users2;?查询SMITH的薪水，工作，所在部门Sqlselect sal,job,depton from emp where ename=SMITH;SMITH 区分大小写查询每人一年挣多少工资Select sal*12 “年工资”,ename from emp;使用算术表达式？显示每个雇员的年工资，使用列的别名Select sal*12 “年工资”,ename from emp;如何处理null值使用nvl函数来处理select sal*13+nvl(comm,0)*13年薪水,ename from emp;nvl(comm,0)意思是查询出comm为null，那么comm用0替代，如果不为null，那么使用其值。如何连接字符串Select ename | is a | job from emp;使用where子句，？如何显示工资高于3000的员工select * from emp where sal3000；？如何查找1982.1.1后入职的员工select * from emp where edate1-1月-1982;select ename from emp where edate1-1月-1982;？如何显示工资在2000的2500的员工情况select * from emp where 2500=sal and sal=2000;使用like操作符% :表示任意0到多个字符 _ :表示任意单个字符。？如何显示首字符为S的员工姓名和工资SQL select ename,sal from emp where ename like S%;？如何显示第三个字符为大写O的所有员工的姓名好工资SQL select ename,sal from emp where ename like _O%;书到用时方恨少在where条件中使用in？如何显示empno 为123,456,800雇员的情况（1）SQL select ename,sal from emp where empon in(123,456,800);（2）SQL select ename,sal from emp where depton =123 or depton =456 or depton =800；使用is null的操作符？如何显示没有上级的雇员的情况。SQL select * from emp where msg is null;使用逻辑操作符号？查询工资高于500或是岗位为MANAGER的雇员，同时还要满足他们的姓名首写字母为大写的Jselect * from emp where (sal500 or job=sales)and ename like J%;使用order by语句？如何按照工资的从低到高的顺序显示雇员的信息select * from emp order by sal; / 从低到高SQL select * from emp order by sal desc; 从高到低？按照部门号升序而雇员的工资降序排列SQL select * from emp order by depton ,sal desc;使用列的别名排序,别名使用“”圈中。Select ename,sal*12 “年薪” from emp order by “年薪”asc;分页查询 按雇员的id号升序取出。 等学了子查询我们再讲吧Oracle表的复杂查询说明：在实际应用中经常需要执行复杂的数据统计，经常需要显示多张表的数据，现在我们给大家介绍较为复杂的select语句数据分组-max,min,avg,sum,count ?如何显示所有员工中最高工资和最低工资SQL select max(sal),min(sal) from emp;?如何显示所有员工中最高工资和最低工资并且显示是谁SQL select ename,sal from emp where sal=(select max(sal)from emp); ？显示所有员工的平均工资和工资总和SQL select avg(sal),sum(sal)from emp; ？计算共有多少员工SQL select count(*)from emp;扩展要求： ？请显示工资最高的员工的名字，工作岗位SQL select ename,sal from emp where sal