最详细的Oracle基础入门（没有之一）

本人常用的操作oracle数据库的方式有两种（1）SQL  Plus(命令行模式)（2）PLSQL Developer(图形可视化模式)

建议新手先使用SQL  Plus，这样才不会形成对oracle的集成开发工具的依赖，下面我将分为两部分来讲，先讲SQL  Plus(命令行模式)，再讲PLSQL Developer(图形可视化模式),下面的各种命令都是在scott用户下进行的操作。

讲SQL Plus之前先来讲一下SQL  和SQL Plus的区别

SQL:是一种语言，关键词不能缩写（select,update,insert,delete），使用语句控制数据库中的表的定义信息和表中的数据

SQL Plus:是一种环境，Oracle的特性之一，关键词可以缩写（for desc col）,命令不能改边数据库中的值

 

SQL  Plus：

（1）基本查询命令

• spool d:\基本查询;  ——开启录频
• spool off;  ——终止录屏
• show user;  ——查看当前用户
• select * from tab;  ——查看某张表
• show linesize;  ——显示行宽
• set linesize 120;  ——设置行宽
• col  ename for a8;  ——设置列宽（a代表一个字符，a8表示8个字符）
• col  sal for 9999 ; ——设置列宽（9999代表4位数）
• 任何与null的运算结果都返回false(>,<,=,!=),所以判断是否为null,一般使用 is null
• select  distinct  job  from emp;  ——distinct去掉重复的job
• select distinct deptno ,job from emp;  ——distinct是作用于后面所有的列（即组合列没有重复）
• select concat('Hello','world') from dual;    ——拼接字符串（在mysql里面不用写from 表,但是oracle一定要写from 表，而oracle提供了一张伪表dual,只要操作跟表没有关系都使用from  dual）
• select 3+2 from dual; 
• select 'hello' || 'world' 字符串 from emp;   ——||连接符  字符串是列的别名

（2）过滤与排序

• ——where过滤——
• select * from emp where deptno =10;
• select * from emp where ename='King';  ——字符串大小写敏感（mysql大小写不敏感）
• select * from emp where hiredate = '17-11月-81';  ——日期格式敏感（默认的格式：dd-mon-yy）
• select * from emp where sal between 100 and 200;   ——包含边界，小值在前，大值在后
• select  * from emp  where deptno not in (10,20);
• 如果集合含有null，不能使用not in ,但可以使用in
• a not in (10,20,null)   => a!=10 and a!=20 and a!=null(false)  =>false
• a in (10,20,null) => a=10 or a=20 or a=null(false) 
• select * from emp where ename like 'S%';   ——模糊查询
• select * from emp where ename like '%\_%' escape   '\';   ——因为‘_’有特殊含义，所以要用转义字符‘\’
• rollback;  ——oracle默认开启事务
• select * from emp where  condition1 and condition2;  ——从右往左判断，一般把false的条件放在condition2（优化SQL）
• ——order by排序——
• order by  后面 +列 ，表达式，别名，序号（默认升序，加上desc表示降序）
• select * from emp order by sal  desc ——列
• select empno,ename,sal,sal*12  from emp order by sal*12;   ——表达式   
• select empno,ename,sal,sal*12 年薪  from emp order by 年薪;   ——别名   
•  select empno,ename,sal,sal*12 年薪  from emp order by 3;   ——序号   
• select * from emp order by deptno sal desc;   ——desc只作用于sal,deptno还是按照升序
• order by 作用于后面所有的列，先按照第一列排序，再后面的列，desc只作用于离他最近的

（3）单行函数

• ——字符函数——
• select lower('Hello World')  转小写，upper('Hello World') 转大写，initcap('Hello World')  首字母大写 from dual;
• select substr('Hello World',4)  子串 from dual;  ——从第四个字符开始取
• select substr('Hello World',4,3) 字串 from dual;  ——从第四个字符开始取取3个
• select length('Hello World') 字符，lengthb('Hello World') 字节 from dual;  ——得到字符数和字节数
• select instr('Hello World','ll') 位置 from dual;  ——查找对应的位置
• select lpad('abcd',10,'*') 左，rpad('abcd',10,'*') 右 from dual;   ——左右填充
• select trim('H',from 'Hello WorldH') from dual;    ——去除前后指定的字符
• ——数字函数——
• select round(45.926,2) 小数点后两位，round(45.926,1) 小数点后一位，round(45.926,0)  个位 from dual;
• ——时间函数——
• select sysdate from dual;  ——查看当前时间
• select (sysdate-1) 昨天，sysdate  今天，(sysdate+1) 明天 from  dual;  ———日期跟数字只能做加减，不能做乘除
• select next_day(sysdate,'星期五') from dual;   ——下一个星期五
• ——转换函数——
• select to_char(99999) from dual;  ——使用默认格式，无需在第二个参数指定转换格式
• select to_char(sal,'L9,999.99') from emp;  ——L为本地货币符号，以9,999.99（千位符，小数点后两位）输出
• select to_char(sysdate) from dual;  ——使用默认格式
• select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;  ———指定格式
• select to_char(sysdate,'day') from dual;  ——查看当前日期是星期几
• select to_number('99999') from dual; ——使用默认格式
• select to_number('$12345.678', '$99999.999') from dual;   ——要将字符串转换为数字，注意转换格式要跟字符串格式相同
• select to_date('12-5月-21') from dual; ——使用默认格式
• select to_date('2017-12-01'，'yyyy-mm-dd') from dual;      ——要将字符串转换为日期，注意转换格式要跟字符串格式相同
• ——通用函数——
• select sal*12+nvl2(comm,comm,0) from emp;  ——当comm=null时，返回0,否则返回comm
• nullif(a,b) 当a=b的时候，返回null ,否则返回a
• select nullif('abc','abc') from dual;  ——返回null
• select nullif('abc','abcd') from dual;  ——返回abc
• select comm,sal,coalesce(comm,sal)  from dual;  ——返回第一个不为null的值
• select ename,job,sal 涨前
• case job  when 'PERSIDENT' then sal+1000 
• when 'MANAGER' then sal+800 
• else sal+400
• end 涨后
• from dual;   ——针对特定的job，增加特定的工资

（4）多行函数

• select sum(sal) from emp;   ——求总和
• select count(*) from emp;  ——求条数
• select avg(sal) from emp;  ——求平均工资
• select deptno,avg(sal) from emp group by deptno; ——分组排序，未包含在组函数里的列，必须在group by里面
• select deptno ,job,sum(sal) from emp group by deptno,job order by 1;  ——多列分组，按先后顺序分组，再按照第一列升序
• -----where跟having的区别：where不能使用多行函数，而having可以
• having是先分组再过滤，where是过滤再分组，从优化的角度推荐使用where 

 （5）多表查询

• select e.empno ,e.ename,e.sal,d.dname from emp e,dept d where e.deptno=d.deptno;  ——等值连接
• select  e.empno,e.ename,e.sal,s.grade from emp e,salgrade s where e.sal between s.losal and s.hisal;  ——不等值连接
• ——左连接：当条件不成立的时候，等号左边的表仍然被包含在最后的结果中——
• select d.deptno 部门号，d.dname 部门名称，count(e.empno)  人数 
• from emp e ,dept d 
• where e.deptno(+) = d.deptno
• group by d.deptno,d.dname;
• ——右连接：当条件不成立的时候，等号右边的表仍然被包含在最后的结果中——
• select d.deptno 部门号，d.dname 部门名称，count(e.empno)  人数 
• from emp e ,dept d 
• where e.deptno= d.deptno(+)
• group by d.deptno,d.dname;
• ——子连接：通过表的别名，将同一张表视为多张表(不适合大表)——
• select e.ename 员工姓名 ，b.ename 老板姓名 from emp e,emp b where  e.mgr = b.empno;

   （6）子查询

• ——可以在主查询的where,select,having,from后面使用子查询——
• ——不可以在group by后面使用子查询——
• ——主查询跟子查询可以不是同一张表，只要返回结果主查询可以用即可——
• ——一般先执行子查询再执行主查询——
• select empno,ename,sal,(select job from emp where empno = 7839) from emp;
• select * from (select empno,ename,sal from emp);
• select * from emp where deptno=(select deptno from dept where dname='SALES');
• select * from emp where sal > any(select sal from emp where deptno=30);
• select * from emp where sal > all(select sal from emp where deptno=30);

（7）创建管理表（主要的数据库对象）

• create table test1(tid number,tname varchar2(20));
• ——rowid（伪列）行地址——
• select rowid ,empno,ename,sal from emp;  ——查找出对应行行地址
• create table emp10 as select * from emp where 1=2; ——将emp的表结构赋予table表
• create table emp10 as select * from emp where 1=1; ——将emp的表结构及数据赋予table表
• alter table test1 modify tname varchar2(40);   ——修改列
• alter table test1 add photo varchar2(10); ——增加列
• alter table test1 drop column photo;   ——删除列 
• alter table test1 rename column tname to username； ——重命名列
• rename test1 to test2; ——重命名表名
• select * from tab;  ——查找当前用户下存在的表
• drop table test1; ——删除表（扔到oracle的回收站）
• show recyclebin; ——查看回收站
• purge recyclebin; ——清空回收站
• select * from "recyclebin name";  ——从回收站找回删除的那张表
• ——注意管理员（system）没有回收站，所以drop table 要小心——
• create  table student(
• sid number constraint student_pk primary key,  ——主键约束
• sname varchar2(20) constraint student_name_notnull not null, ——非空约束
• gender varchar2(2) constraint student_gender check(gender in ('男','女')),  ——检查性约束
• email  varchar2(40)  constraint student_email_unique unique ——唯一性约束
• constraint student_email_notnull not null,
• deptno number constraint student_fk references dept(deptno) on delete set null  ——外键约束
• );

（7）其他的数据库对象

• grant create view to scott;  ——先以管理员的身份赋予用户创建视图的权限
• create or replace view empinfoview   ——由于视图不能修改所以一般使用create or  replace
• as
• select e.empno,e.ename,e.sal,d.dname
• from emp e,dept d
• where e.deptno=d.deptno;  ——创建视图
• create sequence myseq;   ——创建序列
• create index myindex on emp(deptno);   ——创建索引

 

PLSQL Developer：

（1）PL_SQL

• 入门程序

declare 
  -- Local variables here
 
begin
  -- Test statements here
  dbms_output.put_line('Hello World');
end;

• 基本语法——定义变量

--引用型变量：查询并打印7839的姓名和薪水
declare 
  -- Local variables here
  pename emp.ename%type;
  psal emp.sal%type;
begin
  -- Test statements here
  select ename,sal into pename,psal from emp where empno=7839;
  dbms_output.put_line(pename||'的薪水是'||psal);
end;

• 基本语法——条件判断

declare 
  -- Local variables here
 pnum number :=1;
begin
  -- Test statements here
  if pnum = 1 then dbms_output.put_line('这是1');
     elsif pnum =2 then dbms_output.put_line('这是2');
     else dbms_output.put_line('其他数字'); 
  end if;
end;

• 基本语法——循环

--打印1-10
declare 
  -- Local variables here
 pnum number :=1;
begin
  -- Test statements here
  loop
    exit when pnum>10;
    dbms_output.put_line(pnum);
    pnum := pnum+1;
  end loop;
end;

（2）游标

--查询并打印员工的姓名和薪水
/*
光标的属性：%isopen  %rowcount(影响的行数)
            %found   %notfound
*/
declare 
  -- Local variables here
 cursor cemp is select ename,sal from emp;
 pename emp.ename%type;
 psal emp.sal%type;
begin
  -- Test statements here
 open cemp;
 loop
   ---取当前记录
   fetch cemp into pename,psal;
   exit when cemp%notfound;
   dbms_output.put_line(pename||'的薪水是'||psal);
   
 end loop;
 
 ---关闭
 close cemp;
end;

（3）异常例外

---被0除
declare 
  -- Local variables here
 pnum number;
begin
  -- Test statements here
 pnum :=1/0;

exception
  when zero_divide then dbms_output.put_line('1:0不能做分母');
  when value_error then dbms_output.put_line('算术或者转换错误');
  when others then dbms_output.put_line('其他例外');

end;

declare 
  -- Local variables here
  cursor cemp is select ename from emp where deptno=50;
  pename emp.ename%type;
 
  ---自定义例外
  no_emp_found exception;
begin
  open cemp;
  fetch cemp into pename;
  if cemp%notfound then
    ---抛出例外
    raise no_emp_found;
  end if;
  close cemp;
 
exception
  when  no_emp_found then dbms_output.put_line('没有找到员工');
  when  others then dbms_output.put_line('其他例外');
end;

（4）存储过程

• 存储过程（没有输出参数）

create or replace procedure raiseSalary(eno in number)
is
 ----定义变量保存涨前的薪水
  psal emp.sal%type;
begin
  ---得到涨前的薪水
  select sal into psal from emp where empno = eno;
  
  ---涨100
  update emp set sal=sal+100 where empno = eno;
  
   dbms_output.put_line('涨前:'||psal||'涨后：'||(psal+100));
 
end raiseSalary;

• 存储过程（有输出参数）

create or replace procedure queryEmpInfo(eno in number,
                                         pename out varchar2,
                                         psal out number,
                                         pjob out varchar2) 
is
begin
  select ename,sal,job into pename,psal,pjob from emp where empno = eno;
end queryEmpInfo;

•    测试：找到对应的存储过程，右击，选择测试便会生成对应的测试程序，再输入相应的参数完成测试

（5）包和包体

• 定义包头

create or replace package mypackage is

 ---查询某个部门中所有员工的信息----->返回集合
 type empcursor is ref cursor;
 procedure queryEmpList(dno in number, empList out empcursor);

end mypackage;

• 定义包体

create or replace package body mypackage is

 procedure queryEmpList(dno in number, empList out empcursor)
    as
    begin
      open empList for select * from emp where deptno=dno;
    end; 
    
end mypackage;

• 说明：包头中可以申明多个存储过程，只要能够在包体中实现这些存储过程就可以了
• 测试：点击包体，选择需要测试的存储过程，右击选择测试

（6）触发器

• 语句（表）级触发器

create or replace trigger securityEmp
  before insert
  on  emp
declare
  -- local variables here
begin
  if to_char(sysdate,'day')  in ('星期六') or
     to_number(to_char(sysdate,'hh24')) not between 9 and 17 then
     ---禁止insert
     raise_application_error(-20001,'禁止在非工作时间插入新员工');
  end if;     

end securityEmp;

• 行级触发器

/*
对于部门人数达到5人的，不再接受新员工
*/
create or replace trigger limitEmp
  before insert
  on emp 
  for each row
declare
  -- local variables here
  num number;
begin
   select count(*) into num from emp where deptno=:new.deptno;
  if  
    (num>5)  then 
      raise_application_error(-20003,'部门人数已满');
  end if;     
end limitEmp;

• 区别：如果一条INSERT语句在TABLE表中插入500行，那么语句级触发器只执行一次，而行级触发器就要执行500次了