1，  为什么要学习oracle

主流数据库 微软的          Sql server 和access

               瑞典mySQL公司   mysql 免费的，性能也不弱

               Ibm公司的                  db2

Ibm公司的                  informix

               美国Sybase公司的   Sybase

               美国oracle公司的        oracle

Oracle认证

dba认证（数据库管理员认证）分成三类，金字塔型的。

Ocm（大师认证）

Ocp认证（专家认证，不好考了，要考三门）

Oca认证  （oracle初级认证）

Orcale安装过程中会自动生成sys用户和system用户

1.Sys用户是超级用户，具有最高权限，具有sysdba角色，有create database（）的权限 ，该用户默认的密码是manager

2.system用户是管理操作员，权限也很大，具有sysoper（操作员）角色，没有create database权限。默认密码是change_on_install

最大的区别就是有没有create database权限

3一般来讲，对数据库的维护用system登陆就可以了。

Oracle的启动：使用事前需要手动启动OracleOraHome92TNSListener（数据库监听，可以连接JDBC）和OracleServiceMYORA1（数据库实例），也可以同时启动多个实例。

点击我的电脑，管理，在服务里面有很多oracle打头的服务，找到OracleServiceMYORA1点击启动，这就相当于一个数据库。

与sql server的区别，oracle启动其实就是启动了单个数据库，如果你又创建了一个数据库，那么我的电脑，管理中就又会多了一个服务的。

不同的用户登录到同一个数据库看到的表，什么的就可能不同，

 

Oracle的卸载：不能再控制面板卸载，需要在进注册表，删除，重启，等。

Oracle管理工具介绍 4种

1，  开始——程序-oracle orachome90 ——application development——sql*plus  运行输入 sqlplusw

主要用于执行sql语句，plsql。（存储过程变成语言）

2，  在运行中输入sqlplus就行没有w，进入了dos窗口。

3，  开始程序，——oracle orachome90——enterprise manager console 企业管理器是个图形化界面环境。

4，  Plsqldeveloper 属于第三方软件，主要用于开发，册数，优化oracle的存储过程，需要单独安装，

4是最好的

Sql*plus常用命令

1 conn 用户名/密码@数据库名 如果同特殊用户身份登录必须带上as [as  sysdba/sysoper]

       当用特权身份连接时，必须带上as sysdba或者 as sysoper

SQL> conn scott/tiger;

已连接。

SQL>

2，disc[onnect]

说明，断开与当前数据库德连接

3，修改密码（修改自己的密码和修改别人的密码）如果要想修改其他用户的密码

需要用system或者sys登陆

passw[ord]

已连接。

SQL> conn scott/tiger;

已连接。

SQL> passw

更改SCOTT的口令

旧口令: *****

新口令: *********

重新键入新口令:*********

口令已更改

4 show user 显示当前用户名  5 exit  该命令会断开与数据库的链接，同时会退出sql*plus

文件操作命令：

1 start 和@

运行sql脚本，eg：start d:a.sql:   运行d盘的sql脚本

2 edit 编辑指定的sql脚本，就是吧文件打开来编辑

edit d:a.sql:

3 spool 可以将sql*plus屏幕上的内容输出到指定文件中去，输入之后再输入spool off

Eg：spool d:aa.sql    要记录到文件中的文字  spool off

 

交互式命令：

1 &

select * from emp where ename ='SMIH';  （=‘&name'; ）括号里这样写会弹出一个对话框，手动输入一个表中字段名字

显示和设置环境变量，可以控制输出的各种格式，打报表的时候可能会用到。

1 lingsize 设置显示行的宽度，默认是80个字符

       set lingsize 50;

2 pagesize 设置每页显示的行数目，默认是14

用户的管理

1创建新用户，用create  user用普通权限的用户是不行的 一般需要dba（数据库管理员）权限才可以。

SQL> create user xiaoming identified by m123;//xiaoming用户名，m123是密码，create   identified都是关键字identified（被识别的）

用户已创建

2 给用户修改密码 给自己修改密码就password 用户名

给别人修改密码则需要dba权限或者拥有alter user的系统权限。如果你拥有dba权限修改别人的密码也用password 用户名

3 删除用户 一般删除用户要sys或者System用户权限才行，自己删除自己是不行的。

drop user 用户名 cascade；

在删除用户的时候特别说明一点，注意，如果要删除的用户已经创建了表，那么就需要在删除的时候带一个参数 cascade;(意思就是要吧这个用户的表也删除)

4 给用户赋予相应的权限（grant授权同意的意思）或者收回权限（revoke收回资源），创建的新用户是没有权限的甚至登录数据库的权限都没有，

 

引出了权限的问题，oracle大约有140种权限，这块很复杂，但是ERP就是因为他的精细才用它

SQL> grant connect to xiaoming;      connect就是种预定义角色、

授权成功。

权限

       |--系统权限：指用户对数据库的相关权限，比如登录数据库，修改密码等

       |--对象权限：指的是用户对其他用户的数据对象（用户创建的表啊，触发器，视图）操作的权限。

角色：权限的批量授权给他，，就是如果想给一个普通用户权限，用系统权限一个一个赋予太慢了，可以把批量的权限给一个角色，然后让普通用户成为这种角色。

       |--自定义角色：

       |--预定义角色：在安装数据库德时候可能就定好了。connect就是预定义权限。

预定义角色除了connect之外还有几种，dba其实已经把connect包含了，

dba角色 ：如果把dba角色授权给普通用户，那么这个普通用户就有了管理员权限，不能轻易授权

resource角色：可以让普通用户在任何一个表空间建表。（他属于对象权限，建表之类的，上边那俩是系统权限的角色。）

 

对象权限包括

select 查询权限

insert  增加权限

update 修改权限

delete  删除权限

all（上面所有权限都包括）

create index

如果想让xiaoming这个用户可以出查询scott的emp表，但是不能修改等操作，就

grant  select   on emp  to  xiaoming； //能授权xiaoming这种权限的可以使sys或者system，还有scott，因为自己可以授权别人来看自己的表。

 

方案：select * form scott .emp：//scott吧查询的权限给了xiaoming，xiaoming在查询的时候查的不是自己的表

                                          是scoff下的表，所以要写成scott .emp，这就叫做方案。这样的话，不同的用户名表名可以相同的。

收回权限 revoke select on emp form xiaoming；收回赋予小命的查询权限，谁赋予它权限，谁收回。

对权限的维护（权限传递），希望xiaoming用户可以去查询scott的emp表，还希望小命把这个权限传递给别人。

--如果是对象权限就在后面加上with grant option

grant  select   on emp  to  xiaoming with grant option ；

--如果是系统权限，system给xiaoming权限

grant  connect to xiaoming  whit  admin option；

如果scott吧xiaoming对emp表查询权限回收了，那么传递给xiaohong的权限会怎么样呢？xiaohong的权限也会被回收了（株连）

Oracle profile管理用户口令

Profile是口令限制，资源限制的命令集合，当建立数据库时，oracle会自动建立名为default的profile。

1账户锁定：指定.0 该账户登录时最多可以输入密码的次数，也可以指定用户锁定的时间，一般用dba的身份去执行该命令

        Eg，指定scott这个用户最多只能尝试三次登录，锁定时间为2天，怎么实现，

a，   创建profile文件，可以理解为先创建了一个规则，但是写在profile文中中

Sql>create profile  lock_account   limit         //limit限制的意思，黑色的地方时固定的不能改，lock_account是名称 Failed_login_attempts 3  password_lock_time 2;   //failed失败的，login登录，attempts是尝试意思3是尝试的次数2代表锁定的天数。

Sql>alter user  tea profile  lock_account;    // 吧这个规则文件制定给tea， alter修改

 

2 账户解锁     

Sql>alter user tea account  unlock;   //dba的用户才可以操作 alter是改变的意思

3终止口令  为了让用户定期修改密码可以用终止口令指令来完成。也是需要dba权限才行

Sql>creat  profile  myprofile  limit  password_life_time  10 password_grance_time 2; //黑色是固定的，10代表10天使用时间，2代表宽限期为2天。

Sql>alter user  tea profile  myprofile; //吧这个规则文件制定给tea

口令历史：希望用户修改密码时，不能使用之前使用过的密码，可以使用口令历史，用户在输入新密码的时候就会与旧密码进行比对，如果输入的和旧密码一样，那么提示重新输入

Sql>creat  profile  myprofile  limit  password_life_time  10 password_grance_time 2 password_reuse_time 10

//password_reuse_time 10 指定就密码10天后可以重用

Sql>alter user  tea profile  myprofile; //吧这个规则文件制定给tea

4 删除profile   当不需要profile文件时，可以删除文件。如果删除了规则，那么被他约束的用户不在受限制。

Sql>drop  profile password_history 【cascade】// password_history文件名，如果后边加上cascade的话，就会把它相关的东西都删除

                      第二天   oracle表的管理，基本查询，复杂查询  数据库的创建 查询是重点

1掌握oracle表的管理创建和维护

2掌握对oracle表的各种查询

3学会创建新的oracle数据库

Oracle的表的管理

1表明和列名规则

A必须以字母开头，长度不能超过30字符，不能使用oracle的保留字，只能使用如下字符，A a，9 0 #     ￥

oracle的数据类型

字符型

char 定长字符，最大2000字符，查询速度快，效率高。char（10），如果存放两个字，后边六个位置用空格补全。如果存身份证，一定要定义为char（18），这样查询速度快。

varchar2 变长字符，最大40000字符。   Varchar2是varchar的升级版。节省空间，但是查询速度没有定长字符快

clob 字符型大对象，最大4G，如果4000个字符还不行就用clob

数字型

number 可以标示整数和小数，范围 -10的38次方到10的38次方。

nmber（5，2）标示一共有5位，其中包含2位小数  范围  -999.99到999.99

number(5) 表示一个5位整数 范围-99999  到99999

日期类型

date 包含年月日和时分秒

timestamp 这是oracle9i对date数据类型的扩展。他比date精度高，精确到毫秒，银行项目可能会用到。

 

图片类型

blob 二进制数据，可以存放图片，/声音  4G。  一般实际项目中是不会吧声音图片存放进入，只是存个路径，如果要求保密性很高的声音图片等才会放进数据库

 

建表SQL> create table student (

  2      xh number(4),

  3      xm varchar2(20),

  4     sex char(2),

  5     birthday date,

  6     sal number(7,2)

  7     );

SQL> create table classes(

  2  classId number(2),

  3  cname varchar2(40)

  4  );

Table created

添加一个字段

Sql>alter table student add （classid number（2））；// SQL>  desc student; 可以查看表的结构

修改字段的长度

Sql>alter  table  student modify (xm varchar2(30)); // modify 修改的意思

修改字段类型/或是名字（不能有数据）

Sql>alter table student modify (xm char(30));

删除一个字段（工作的时候慎用，多一个就多一个没什么）

Sql>alter student to stu;

修改表名字

 rename  student to stu; //rename 重新命名

删除表

drop table student;

 

添加数据 不同方式

1将所有字段都插入

insert  into  student  values  (‘a001’,’张三’,’男’,’01-5月-05’,’10’);//在插入日期的时候，oracle默认的时期格式是 天-月-年，

alter session set nls_date_format =’yyyy-mm-dd’; //如果想用正常的顺序插入年月日就必须加入一条语句。date_forma日期格式

2插入部分字段

insert  into  student  (xh,xm,sex)  values ('003','刘一锋','男');

3插入空值

SQL> insert  into  student(xh,xm,sex,birthday)  values('23','程换','男',null);

但是如果要查询某个字段为空值的信息时候就要这么查，比如说查询生日为空的

Select * from student where biethday is null;    //是 is  null；如果查询不是空的值就is not  null

 

修改数据

1改一个字段

SQL> update student set sex='女' where xh='23';

1 row updated

SQL> select * from student;

   XH XM                 SEX BIRTHDAY       SAL CLASSID

----- -------------------- --- ----------- --------- -------

    1 小明               男 1997-8-9     58.45       1

    3 刘一锋            男                      

   23 程换               女 

2修改多个字段

SQL> update student set sex='女', xm='女朋友'  where xh='23';

3修改含有null值的数据

 

删除数据

第一种方式，删除之后，表结构还在，写了日志，可以恢复的

delete  from student ；设置保存点，删完之后可以恢复 ，意思就是

                                   //有经验的管理员都会设置保存点，两个重要的命令

                                   savepoint aa；//设置保存点  rollback to aa；回滚到保存点

第二种凡是，删除表的结构和数据，不能回复

drop table student；

第三种方式 删除一条记录，

delete from student where xh=’A001’;

第四种方式，删除表中所有记录，表结构还在，不写日志，无法找回删除的记录，但是删除速度快，如果表很大，要求快速删除，就可以用这个

       truncate  table  student；

 

查询，重点 dept,emp都是表名。

查看表的结构

desc dept;   能看到字段，类型，是否为空，权限，有没有注释等

查询所有列 

Select * from dept；

查询指定列（不要轻易地使用 select * 查询很慢）

Select ename ,sal , job from emp;// ename ,sal , job deptno都是字段名

取消重复行

Select distinct deptno , job  from  emp；//记住关键字 distinct  不同的，意思就是在这两个字段中，吧不重复的找出来，重复的不显示了

 

小练习，查询emp表中SMITH的部门，工作，和工资  SQL> select job, deptno,sal from emp where ename='SMITH';

这里记住一点oracle数据库中的字段名，就是单引号内的是区分大小写的。

 

使用算术表达式查询 查询一年的工资

select sal*12 from emp; //sal 看着很难看，所以在oracle中可以给他取一个别名select sal*12 "年工资",ENAME "姓名" from emp;

  年工资 姓名

---------- ----------

      9600 SMITH

     19200 ALLEN

如何处理null值，使用nvll函数来处理，查询一个人的年工资和奖金，但是如果这样写的话

Select sal*13+comm*13 “年工资” ,ename,comm from emp;  没有奖金comm的人的年工资也是空的，

应该写，select sal*13 + nvl(comm,0)*13  “年工资” ,ename,comm from emp;  //（comm，0）的意思就是如果comm值为null就返回0

如果comm不是空的，就用本身的值代替这段代码

 

使用where语句

1 查询显示工资高于3000的员工

select sal ,ename from emp where sal>3000;

2 查询1982.1.1号后入职的员工 （注意日期格式）

select hiredate ,ename from emp where hiredate>'1-1月-1982';

3 如何显示工资在2000到2500的员工情况 多个条件用and隔开

select ename ,sal from emp where sal>2000  and sal<2500;

4 like操作符  %代表任意0到多个字符，  _ 下划线代表任意单个字符

如何显示首字母为S的员工姓名和工资

SQL> select ename ,sal from emp where ename like 'S%';

 

ENAME        SAL

---------- ---------

SMITH      800.00

SCOTT     3000.00

显示第三个字符为大写O的所有员工的姓名和工资

select ename ,sal from emp where ename like '__O%';//两个下划线

where in语句

如何显示员工号empno为123.或者为345.或者为800，可以用where empon=123 or where empon=345等等查询，但是很慢，很麻烦

直接这样  select ename ,sal from emp where empno in(123,234,456);

查询没有上级的员工情况

select ename ,sal from emp where MGR is null;

使用逻辑操作符号

查询工资在500以上或者job为 manager，同时名字里第一个字母为J的员工信心 ，看清要求，其实是两大与的关系。

select * from emp where (sal>500 or job='MANAGER') and ename like "J%"   这个括号是考点，

order by 字句

1如何按照工资的从低到高的顺序显示员工的信息，

select * from emp  order by sal;  如果是从高到底就select * from emp  order by sal desc;

2按照部门号升序而员工的工资降序排列，意思就是把部门号小的员工放在上面，部门号一样的员工又按工资降序排列

SQL> select * from emp order by deptno ,sal desc;

3 使用列的别名排序

SQL> select ename, (sal+nvl(comm,0))*12  "年薪" from emp  order by "年薪";

 

Oracle的复杂查询

数据分组，max  ， min  ，avg平均 ，sum总数 ，count

1显示所有员工中最高工资和最低工资。用到了子查询

SQL> select  ename , sal  from  emp  where  sal=(select max(sal) from  emp);

2请显示工资高于平均工资的员工信息

SQL> select * from emp where sal>(select  avg(sal) from emp);

group by 和 having子句//group by用于对查询的结果分组统计，having字句用于限制分组显示结果。Having是给group服务的

1 显示每个部门的平均工资和最高工资，deptno这个字段一定要出现在查询中，因为就是根据他查询的

SQL> select avg(sal),max(sal),deptno from emp group by  deptno;

2 显示每个部门下的每种岗位的平均工资和最低工资和最高工资

SQL> select min(sal),avg(sal),max(sal),deptno,job from emp group by deptno,job;

3显示平均工资低于2000的部门号和他的平均工资

select avg(sal),max(sal),deptno  from emp group by deptno having avg(sal)>2000;

 

对数据分组的总结

1分组函数只能出现在选择列表,having，order by 子句中

Eg：select avg(sal),max(sal),deptno from emp group by  deptno;

select avg(sal),max(sal),deptno  from emp group by deptno having avg(sal)>2000;

2，   如果在select语句中同时包含有group by ，having，order by，那么他们的顺序是group by ，having，order by

3，   在选择列中如果有列，表达式，和分组函数，那么这些列和表达式必须有一个出现在group by子句中，否则就会出错

如select avg(sal),max(sal),deptno  from emp group by deptno having avg(sal)>2000; 这里的deptno一定要出现在group by中

 

多表查询（重点）

 指基于两个或者两个以上的表或者是试图的查询，实际中单表查询可能不能满足需求

 1 显示雇员名，雇员工资和所在的部门名字//多表查询，涉及到dept表盒emp表

select  a1.ename,a1.sal, a2.dname from emp a1, dept a2 where a1.deptno = a2.deptno;//因为分不清各个字段隶属于那个表，所以给表起了别名，a1，a2，   emp a1, dept a2

a1.deptno = a2.deptno 为什么加了这句话就能查出来了呢，涉及到了笛卡尔集的东西

笛卡尔集规定：多表查询的条件是至少不能少于表的个数减一，笛卡尔集就是每个表中的信息个数互相乘，数量是很大的。

2 显示部门号码为10的部门名，员工名，和工资，

SQL> select  a1.ename , a1.sal, a2.dname from emp a1,dept a2 where a1.deptno=10 and a2.deptno=10;

3 显示各个员工的姓名，工资，及其工资的级别 其中用到了工资级别表 salgrade

SQL>  select a1.ename,a1.sal ,a2.grade from emp a1,salgrade a2 where a1.sal between a2.losal and a2.hisal;

4 显示员工的姓名，工资及所在部门的名字，并按照部门排序

SQL> select a1.ename,a1.sal ,a2.dname from emp a1,dept a2 where a1.deptno =a2.deptno order by a1.deptno;

自连接 ：指在同一张表的查询链接，eg，emp，每个员工上边都有个领导，比较有代表性的多表查询，就是要查询的内容在同一张表内，但是也属于多表查询

显示某个员工的上级领导的名字

select worker.ename, boss.ename from emp worker, emp boss where worker.mgr=boss.empno;

子查询：嵌入在其他sql语句中的select语句。也叫嵌套查询

单行子查询：是指只返回一行数据的子查询语句

如何显示与smith同一部门的所有员工

SQL> select * from emp where deptno=(select deptno from emp where ename='SMITH');

多行子查询：指返回多行数据的子查询

如何查询和部门10的工作相同的雇员的名字，岗位，工资，部门号

SQL> select * from emp where job in (select distinct job from emp where deptno=10 );

在多行子查询中使用all操作符

1如何显示工资比30号部门所有员工的工资还高的员工姓名，工资，和部门号

第一种方法 select ename ,sal, deptno from emp where sal > all (select sal from emp where deptno=30);

第二种select ename ,sal, deptno from emp where sal >  (select max(sal) from emp where deptno=30);

在多长子查询中使用any操作符

如何显示工资比部门30的任意一个员工的工资高的员工的姓名，工资，和部门号

第一种方法 select ename ,sal, deptno from emp where sal > any(select sal from emp where deptno=30);

第二种select ename ,sal, deptno from emp where sal >  (select min(sal) from emp where deptno=30);

多列子查询

单列子查询只返回单行，单行数据，多行子查询是指返回单行多列数据，都是针对单列而言的，而多列子查询则是指查询返回多个列数据的子查询语句，

1 如何查询与smith的部门和岗位完全相同的所有员工

SQL> select * from emp where (deptno, job) =(select deptno,job from emp where ename='SMITH');

//(deptno, job) =(select deptno, 这里的顺序一定是要对应的。

2，如何显示高于自己部门平均工资的员工的信息

逻辑要清晰，分两步

1，找出各个部门的平均工资

((select deptno ,avg(sal) from emp  group by deptno) a1)

2, 吧上面的当做一个子表，并且起个别名

 select a2.ename,a2.sal, a1.mysql from emp a2,(select deptno ,avg(sal) mysql from emp  group by deptno) a1 where a2.deptno = a1.deptno and a2.sal >mysql;

总结：当在from子句中使用子查询时，该子查询会被当做一个视图来对待，因此叫做内嵌视图

当在from子句中使用子查询时，必须给子查询指定别名。

分页查询 （在一个表中，所有的信息只取一部分，比如说1到20号个信息，只取6-10的，就需要用到分页查询）一共有三种方式

第一种方式 rownum分页，其他两种不好理解

(select * from emp)

2显示rownum

select a1.* ,rownum rn  from(select * from emp) a1;

如果要查询1-20中6-10的雇员信息，首先要去掉10号之后的，然后在去掉6号之前的

select * from (select a1.*, rownum rn from (select ename,sal from emp order by sal ) a1 where rownum<=10) where rn>=6;

//这句话的意思就是查询出rn号从6到10的员工信息并且按照sal排序 感觉就是多层嵌套

几个查询变化

A指定查询列，只需修改最里层的子查询

B 如何排序，只需要修改最里层的子查询

 

用查询结果创建新表

create table mytable (id,name,sal,job,deptno) as select empno,ename ,sal,job,deptno from emp ;

合并查询，一般很少用到

 有时候在实际应用中，为了合并多个select语句的结果，可以使用集合操作符 union，union all，intersect，minus

1

1 union 重复的意思 该操作符用于取得两个结果集的并集，当使用该操作符时，会自动去掉结果集中重复的行（如果有两个一样的，那么就去掉一个一样的） 

QL> select ename ,sal,job from emp where sal >2500 union

   select ename,sal,job from emp where job='MANAGER';//这两行是一句话，这两句话的意思就是，先找到工资大约2500的人，然后在找到job是manager的人，如果这两个查询中有重复的是同一个人的话，就去掉重复的，并不是要同时满足两个查询条件，满足一个就可

2 union all  和union相似，但是不会取消重复行，而且不会排序

3 intersect 取交集的关系 使用该操作符用于取得两个结果集的交集， ，就是把他们结果重复的取出来

select ename ,sal,job from emp where sal >2500 intersect

   select ename,sal,job from emp where job='MANAGER';

4 minus 减去的意思  使用该操作符用于取得两个结果的差集，它只会显示存在第一个集合中，而不存在第二个集合中的数据

select ename ,sal,job from emp where sal >2500 minus      （就理解为第一个集合减去第二集合的结果，如果减不动就为空）

   select ename,sal,job from emp where job='MANAGER';

 

创建新的数据库 两种方法

1，   用oracle提供的向导工具 dbca（数据库配置助手）

2，   可以用手工步骤直接创建