1、成为一个 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 断开与当前数据库的连接，同时会退出文
2、件操作命令(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)Sqlset pagesize number;/在打印报表（有要求）的的时候使用Oracle 的用户管理创建用户(dba 身份)： create user fanfan identified by m123;Create us
3、er 关键字。Identified by 关键字。创建的新用户是没有任何权限的，甚至连登陆数据库的权限也没有，需要为其指定相应的权限。给一个用户赋权限使用命令grant， 例:grant connect to fanfan; 一百四十多种权限。回收权限使用命令 revoke。使用 profile 管理用户口令概述：profile 是口令限制，资源限制的命令集合，当建立数据库时，oracle 会自动建立名称为 default 的 profile，当建立用户没有指定 profile 选项，那 oracle 就会将 default 分配给用户。（1） 账户锁定 概述：指定该账户（用户）登录时最多可以
4、输入密码的次数，也可以指定用户锁定的时间（天）一般用 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)终止口令为了让用户定期修改密码可以使用终止口令的指令来完成，同样这
5、个命令也需要 dba 身份来操作。例子：给前面创建的用户 xiaohong 创建一个 profile 文件，要求该用户每隔十天要修改自家的登录密码，宽限期为 2 天。看看怎么做。Sqlcreate profile myprofile limit password_life_time 10 password_grace_time 2;Sqlalter user xiaohong profile myprofile(4)口令历史概述：如果希望用户在修改密码时，不能使用以前使用过的密码，可以使用口令历史，这样 oracle 就会将口令修改的信息存放到数据字典中，这样当用户修改密码是，oracle 就
6、会对新旧密码进行比较，当发现新旧密码一样时，就提示用户重新输入密码。例子：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】系
7、统权限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
8、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；修改别人的密码
9、 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
10、_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，co
11、nnect 权限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 =
12、 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 的保留字
13、【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 次方可以表示整数，也
14、可以表示小数。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 v
15、archar2(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);
16、删除一个字段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_forma
17、t = 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）;修改数据修改一个字段，修改多个字段Updat
18、e 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; 可以恢复删除的内容Or
19、acle 表基本查询 -介绍工欲善其事，必先利其器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;
20、?查询 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用
21、 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=2
22、000;使用 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,
23、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 语句？如何按照工资
24、的从低到高的顺序显示雇员的信息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 表的复杂查询说明：在实际应用中经常需要执行复杂的数据统计，
25、经常需要显示多张表的数据，现在我们给大家介绍较为复杂的 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 em
26、p;扩展要求：？请显示工资最高的员工的名字，工作岗位SQL select ename,sal from emp where sal=(select max(sal)from emp);？请显示工资高于平均工资的员工信息SQL select * from emp where sal(select avg(sal)from emp);Group by 和 having 字句Group by 用于对查询的结果分组统计Having 字句用于限制分组显示结果？如何显示每个部门的平均工资和最高工资SQL select max(sal),avg(sal),depton from emp group by d
27、epton;？显示每个部门的每种岗位的平均工资和最低工资？显示平均工资低于 2000 的部门号和它的平均工资SQL select avg(sal),max(sal),depton from emp group by depton having avg(sal)2000;扩展要求：对分组数据的总结1. 分组函数只能出现在选择列表、having、order by子句中2. 如果在 select 语句中同时包含有 group by，having ，order by 那么他们的顺序是 group by，having ，order by3. 在选择列中如果有列、表达式、和分组函数，那么这些列和表达式必须
28、有一个出现 group by 子句中，否则就会出错。如：select depton,avg(sal),max(sal)from emp group by depton having avg(sal)2000;这里 depton 就一定要出现在 group by 中，呵呵多表查询多表是指基于两个或两个以上的表或视图的查询，在实际应用中，查询单个表可能不能满足你的需求，（如果显示在 sales 部门位置和其他员工的姓名），这种情况下需要使用到 dept 表和 emp 表。？显示雇员名，雇员工资及所在部门的名字【笛卡尔集】?如何显示部门号为 10 的部门名，员工名和工资？显示各个员工的姓名、工资、及其工资的级别扩展要求：？显示雇员名，雇员工资及所在部门的名字，并按部门排序
