Oracle快速入门 | 黑马

-----创建表空间
create tablespace waterboss
 datafile 'd:\waterboss.dbf' //写表空间所对应的数据文件
 size 100m      //指定表空间大小
 autoextend on      //自动扩展
 next 10m;         //满了就增长10m 

-----创建用户

create user wateruser
indentified by 123456  //用户的密码
default tablespace waterboss;  //指定用户属于哪个表空间

-----给用户授权

--给wateruser赋予dba权限
grant dba to wateruser

二、数据类型

语句和mysql基本一样，但是数据类型不同，我们先学习Oracle的数据类型

字符型

CHAR：固定长度的字符类型，最多存2000个字节，少了后面自动空格补齐
VARCHAR2：多了个2，名字就是这样定义的，和mysql那个varchar是一样的，可变长度的字符串，不能超过4000个字节
LONG：大文本类型，最大可以存储2个G

数值型

NUMBERR(n) 表示一个整数，长度是n
NUMBER(m,n) 表示一个数，总长度为m，小数是n，整数是m-n

日期类型

DATA 表示日期类型

特殊

CLOB 大对象，表示大文本数据类型可存4G，图书
BLOB 大对象，表示二进制数据，可以存4G，一般存视频图片这些二进制文件

三、创建表、修改表和删除表

创建表

create table person(
    pid number(20),
    pname varchar2(10)
);

修改表

----添加一列
alter table person add (gender number(1));

----修改列类型
alter table person modify gender char(1);

----修改列的名称
alter table person rename column gender to sex;

----删除一列
alter table person drop column sex;

数据的增删改

----添加一条记录
insert into person (pid,pname) values (1,"小明");
commit;     //除了查询其他修改表数据完一定要提交事务 

----修改一条记录 
update person set pname = '小马' where pid = 1;

----三个删除
--删除表中全部记录
delete from person;    
--删除表结构
drop table person;  
--先删除表，再创建表，效果等同于删除表中全部记录，在表中有索引的情况下，操作效率高
truncate table person;

四、序列

我们发现我们的id是直接打进去的，但是实际中我们可能记不住他的增长，这时候就需要序列

----序列：默认从1开始，依次递增，主要用来给主键赋值
----序列不属于任何一张表，但是可以逻辑和表做绑定
create sequence s_person;

--dual是虚表，只是为了补全语法，没有任何意义，我们oracle语句查询就必须带from但是序列不属于任何表
select s_person.nextval from dual;//第一次要让他有值
--查询
select s_person.currval from dual;

--今后我们写插入
insert into person(pid,pname) values (s_person.nextval,'小张');
commit;

用户

system用户

超级管理员用户，密码orcl

scott用户

密码默认tiger，这个用户是初学者必须接触的，他可以模拟出各种复杂的查询

刚安装完scott用户是被锁定的，解锁需要超级管理员身份

--解锁scott用户
alter user scott account unlock；

--解锁scott用户的密码（也可以重置密码）
alter user scott identified by tiger;

--切换到scott用户下

查询语句

简单查询很简单，但是如果是复杂查询，我们就需要用到函数

一、单行函数

作用于一行，返回一个值

1、字符函数

---upper就是小写换大写 lower就是大写换小写
select upper('yes') from dual;
select lower('YES') from dual;

2、数值函数

----这个表示四舍五入，后面的数就是保留的位数
select round(26.14,1) from dual;

----求余数
select mod(10,3) from dual;

3、日期函数

----查询出emp表中所有员工入职距离现在几天
select sysdate-e.hiredate from emp e; 
//这里的e就相当于emp的别名 后面直接e.hiredate就可以调用emp表的属性了

----算出明天此刻
select sysdate+1 from dual;

----查出emp中所有员工入职距离现在几月
select months_between(sysdate,e.hiredate) from emp e;

----查出emp中所有员工入职距离现在几年
select months_between(sysdate,e.hiredate)/12 from emp e;

----查出emp中所有员工入职距离现在几周
select (sysdate-e.hiredate)/7 from emp e;
select round(sysdate-e.hiredate)/7 from emp e; //加个round四舍五入

----转换函数
--日期转字符串
select to_char(sysdate,'yyyy-mm-') from dual;
--字符串转日期
select to_date('2018-6-7 16:40:33','fm yyyy-mm-dd hh24:mi:ss') from dual;

4、通用函数

----算出年薪
--奖金里面有null直接年终加12月薪做算术会得null的
select e.sal*12+nvl(e.comm,0) from emp e; //如果不是null就e.comm，是null就用0

二、条件表达式

mysql和oracle通用的写法

--给emp表的员工起中文名
select e.ename,
    case e.ename
        when 'Tom' then '汤姆'
            when 'tim' then '提姆'
                when 'jack' then '杰克'
                    else '无名'     //如果不写else其余人别名都是null
                        end
from emp e;

--判断emp表中员工工资，如果高10000显示高收入，5000到10000是中收入，低于5000低收入
select e.sal,
    case 
        when e.sal>10000 then '高收入'
            when e.sal>5000 then '中等收入'
                else '低收入'
                    end
from emp e;

oracle专用条件表达式(不建议用这种）

--给emp表的员工起中文名
select e.ename,
     decode(e.ename
        'Tom' '汤姆',
            'tim' '提姆',
                'jack' '杰克',
                    '无名')"中文名" 
from emp e;

三、多行函数（聚合函数）

作用于多行，返回一个值

---查询总数量
select count(1) from emp; 这里的1相当于*

---工资总和
select sum(sal) from emp;

---最大工资
select max(sal) from emp;

---最低工资
select min(sal) from emp;

---平均工资
select avg(sal) from emp;

四、分组查询

---分组查询
---查询出每个部门的平均工资
select e.deptno,avg(e.sal) from emp e group by e,deptno;
---分组查询中，出现在group by后面的才能出现在select后面
---没有出现在的列，想在select后面出现必须加上聚合函数（多行函数）
---因为group by分组后会改变表的结构，一个组有很多个数据不能单独查一个
---但是聚合函数可以把多行变成一个值

---查询平均工资高于6000的部门信息
select e.deptno,avg(e.sal) 
from emp e 
group by e.deptno
having avg(e.sal)>6000;
---所有条件都不能使用别名来判断

---查询每个部门工资高于800的员工平均工资
select e.deptno,avg(e.sal) asal
from emp e
where e.sal>4000
group by e.deptno;
---where是过滤分组前的数据，having是过滤分组后的数据
---where必须在分组前，having必须在分组后

五、多表查询

---笛卡尔积
select *
from emp e,dept d;

---等值连接
select *
from emp e,dept d
where e.deptno=d.deptno;

---内连接（早期写法）
select *
from emp e inner join dept d
on e.deptno = d.deptno;

---查询所有部门，以及部门下的员工信息【外连接】
select *
from emp e right join dept d
on e.deptno = d.deptno;

---查询所有员工信息，以及员工所属的部门【外连接】
select *
from emp e left join dept d
on e.deptno = d.deptno;

---oracle专用外连接（尽量选择通用）
select *
from emp e,dept d
where e.deptno(+) = d.deptno;  //+在这边就显示那边的全部数据

六、自连接概念和练习

我们要查员工对应的领导时，我们发现emp表里有员工编号以及他的领导编号

当我们一员工编号来看这个表可以是员工表，也可以以领导的编号看编程领导表

这时候就可以用到自连接

把一张表当成两张表起别名，让1的领导编号等于2的员工编号

---查询员工姓名和员工领导姓名
select e1.ename,e2.ename
from emp e1,emp e2
where e1.mgr = e2.empno;

---查询员工姓名，员工部门名称，员工领导名称
select e1.ename,d.dname,e2.ename
from emp e1,emp e2,dept d
where e1.mgr = e2.empno
and e1.deptno=d.deptno;

---查询员工姓名，员工部门名称，员工领导名称，员工领导部门名称
select e1.ename,d1.dname,e2.ename,d2.dname
from emp e1,emp e2,dept d1,dept d2
where e1.mgr = e2.empno
and e1.deptno=d1.deptno
and e2.deptno=d2.deptno;

七、子查询

1、子查询返回一个值

---查询工资和scott一样的员工信息
select * from emp where sal = 
(select sal from emp where ename = 'scott');
--但是这个ename不是唯一的，如果重名就会查出集合 用=就报错
--所以保险起见我们这里改成in
select * from emp where sal in 
(select sal from emp where ename = 'scott');

2、子查询返回一个集合

---查询出工资和10号部门任意员工一样的员工信息
select * from emp where sal in
(select sal from emp where deptno = 10);

3、子查询返回一张表

---查询出每个部门最低工资，最低工资员工姓名，和改员工所在部门名称
--1、先查出每个部门最低工资
select deptno,min(sal) msal
from emp 
group by deptno;
--2、三表联查，得到最终结果
select t.deptno,t.msal,e.ename,d.dname
from (select deptno,min(sal) msal from emp group by deptno) t,emp e,dept d
where t.deptno = e.deptno 
and t.masl = e.sal 
and e.deptno = d.deptno

八、分页查询

orwnum行号：当我们做select操作的时候，没查询出一行记录，就会在改行上加一个行号

行号从1开始，依次递增，不能跳着走。

---我们先试试rownum
select rownum,e.* from emp e order by e.sal desc
---排序操作会影响rownum的顺序,rownum是查询出的行,当我们按照规则排序后这个行肯定乱的

---但是我们可以先按照规则排序再加上行号rownum
select rownum,t* from(
select rownum,e* from emp e order by e.sal desc) t;

---emp表工资倒叙排列后，每页五条记录，查询第二页
select rownum,e.* from(
    select * from emp order by sal desc
) e where rownum<11 and rownum>5 
---我们这样是错的 rownum必须从1开始连续的 所有rownum>5错了
---我们要用这种,这就是分页查询的固定格式
select * from(
    select rownum rn,e.* from(
        select * from emp order by sal desc
    ) e where rownum<11
) where rn>5

视图

1、概念

视图就是提供查询的窗口，里面没有存放数据，所有数据来自于原表

2、作用

可以屏蔽掉敏感的字段。比如工资这一列，我们为了不让大家看到工资就可以创建个没有工资的视图给大家看
保证总部和分部数据及时统一。总公司有雨伞10万把，分部卖的东西不能多余总部，不然没货了，如果他们都各自查表，这时候总部突然卖没货，分部还没来得及查，继续卖就超卖了。这时候就可以用视图防止超卖，让总部从表中查，分部去看视图，总部数据改变，分部立马可以看到

3、创建视图

（1）切换dba权限用户

要创建视图必须右dba权限，所有我们要切换用户

（2）创建表

创建视图必须先由表，我们把刚刚scott用户下的表查出来创建

create table emp as select * from scott.emp;
select * from emp;

（3）创建视图

create view v_emp as selcet ename,job from emp;
---view是关键字 v_emp是视图名称

4、管理视图

---查询视图
select * from v_emp;

---修改视图
update v_emp set job='CLERK' where ename='ALLEN';
commit;

视图可以修改，但是不推荐，因为操作的还是表数据，直接改表就行，公司为了防止修改视图，会创建只读视图

create view v_emp1 as select ename,job form emp with read only;

索引

1、索引

索引就是在表的列上构建一个二叉树，达到大幅度提高查询效率的目的

但是索引会影响增删改的效率

2、单列索引

---创建单列索引
create index idx_ename on emp(ename);

---索引触发规则，条件必须是索引列中的原始值
select * from emp where ename='scott'
---如果加了单行函数或者模糊查询都不是原始值，不会触发索引

3、复合查询

---创建复合索引
create index idx_enamejob on emp(ename,job);

---复合索引中第一列为优先检索列
---如果要触发复合索引，必须包含有优先检索列中的原始值
select * from emp where ename = 'scott' and job='xx';
---触发复合索引
select * from emp where ename = 'scott';
---假如他又创建了单列索引，又创建了复合索引，生效的是单列索引

select * from emp where ename = 'scott' or job='xx';
---这种就不会触发索引 or相当于两个查询语句 一个触发一个不触发最后就不会触发

plsql编程语言

一、概述

这种编程语言是对sql语言的扩展，使得sql语言具有过程化编程的特性。

pl/sql编程语言比一般的过程化编程语言，更加灵活高效

主要用来编写存储过程和存储函数等

二、声明与赋值

赋值操作可以使用:= 也可以使用into查询语句

declare
    i number(2) := 10;    
    s varchar2(10) := '小明';
    ena emp.ename%type;  ---找到emp表的ename的类型赋值到这里 引用型变量
    emprow emp%rowtype;  ---记录型变量
begin
    dbms_output.put_line(i);
    dbms_output.put_line(s);
    select ename into ena from emp where empno = 7788; ---查询语句into赋值
    dbms_output.put_line(ena);
    select * into emprow from emp where empno = 7788; ---查一行记录赋给emprow
    dbms_output.put_line(emprow.ename || '的工作为:' || emprow.job); 
    ---oracle的字符串拼接用||
end;

三、判断语句

---输入小于18，输出未成年
---输入18到50，输出中年人
---输入大于50，输出老年人
declare
    i number(3) := &ii;
begin    
    if i<18 then
        dbms_output.put_line('未成年');
    else if i<40 then
        dbms_output.put_line('中年人');
    else
        dbms_output.put_line('老年人');
    end if;
end;

四、循环

---用三种循环输出1-10
---while循环
declare
    i number(2) :=1;
begin
    while i<11 loop
        dbms_output.put_line(i);
        i:=i+1;
    end loop;
end;

---exit循环
declare
    i number(2) :=1;
begin
    loop
        exit when i>10
        dbms_output.put_line(i);
        i:=i+1;
    end loop;
end;

---for循环
declare
    i number(2) :=1;
begin
    for i in 1..10 loop
    dbms_output.put_line(i);
    end loop;
end;

五、游标

可以存放多个对象，多行记录，可以理解为集合关键字cursor

---输出emp中所有员工的姓名
declare
    cursor cl is select * from emp;
    emprow emp%rowtype;
begin
    open c1;       ---打开游标
        loop
            fetch c1 into emprow;
            exit when c1%notfound;
            dbms_output.put_line(emprow.ename);
        end loop;  ---关闭游标     
    close c1;
end;

---给指定部门员工涨工资
declare
    cursor c2(eno enp.deptno%type) 
    is select empno from emp where deptno=eno;
    en emp.empno%type;
begin
    open c2(10);
        loop
            fetch c2 into en;
            exit when c2%notfound;
            update emp set sal = sal +100 where empno = en;
            commit;
        end loop;
    close c2;
end;

六、存储

存储过程

存储过程就是提前已经编译好的一段pl/sql语言，放置在数据库端，可以直接被调用

这一段pl/sql一般都是固定步骤的业务

语法：create or replace procedure 过程名(参数名 in/out 数据类型)

---给指定员工涨100块钱
create or replace procedure p1(eno emp.empno%type)  --不写参数默认in
is

begin
    update emp set sal=sal+100 where empno=eno;
    commit;
end;

---这个时候执行就存储好了
---测试p1
declare

begin
    p1(7788);
end;

存储函数

---通过存储函数实现计算指定员工的年薪
---存储过程和存储函数的参数都不能带长度
---存储函数的返回值类型也不能带长度
create or replace function f_yearsal(eno emp.empno%type) return number
is
    s number(10);
begin
    select sal*12+nvl(comm,0) into s from emp where empno=eno;
    return s;
end;

---测试f_yearsal
---存储函数在调用的时候，返回值需要接收
declare
    s number(10)
begin
    s:= f_yearsal(7788);
    dbms_output.put_line(s);
end;

out类型参数

---用存储过程算年薪
create or replace procedure p_yearsal(eno emp.empno%type,yearsal out number)
is
    s number(10);
    c emp.comm%type;
begin    
    select sal*12,nvl(comm,0) into s,c from emp where empno=eno;
    ---把年薪和年终奖分别存到s和c里面
    yearsal := s+c;    
    ---再用yearsal把他们相加结果存起来
end;

---测试p_yearsal
declare
    yearsal number(10);
begin
    p_yearsal(7788,yearsal);
    dbms_out.put_line(yearsal);
end;

in和out类型的参数区别是什么？

凡是涉及到into查询语句赋值或者:=赋值操作的参数，都必须用out修饰，否则都用in

存储过程和存储函数的区别

语法区别：关键字不一样

本质区别：存储函数有返回值，而存储过程没有返回值。

如果存储过程想实现有返回值，必须使用out类型的参数，即便是存储过程使用out类型的参数，本质也不是真的有了返回值，而是在存储过程内部给out类型的参数赋值，执行完毕后我们直接拿到输出类型参数的值

---把scott用户下的dept表复制到当前用户下
create table dept as select * from scott.dept;
---使用传统方式实现案例需求

select e.ename,d.dname
from emp e,dept d
where e.deptno = d.deptno;

---使用存储函数来实现提供一个部门编号输出一个部门名称
create or replace function fdna(dno dept.deptno%type) return dept.dname%type
is
    dna dept.dname%type;
begin
    select dname into dna from dept where deptno=dno;
    return dna;
end;

---使用fdna存储函数实现案例需求
select e.ename,fdna(e.deptno)
from emp e;

触发器

触发器就是指定一个规则，在我们做增删改操作的时候，只要满足改规则，自动触发，无需调用

一、语句级触发器

不包含for each row的触发器

---插入一条记录，输出一个新员工入职的触发器
create or replace trigger t1
after
insert
on person
declare

begin
    dbms_output.put_line('一个新员工入职');
end;

---触发
insert into person values (1,"小红");
commit;

二、行级触发器

包含for each row就是行级触发器

加for each row是为了使用 :ord 或者 :new对象或一行记录

---不能给员工降薪
create or replace trigger t2
before
update
on emp
for each row
declare

begin
    if :old.sal>:new.sal then
        raise_application_error(-20001,'不能给员工降薪');
    end if;
end;

---触发t2
update emp set sal=sal-1 where empno = 7788;
commit;

三、触发器实现主键自增

---在用户插入之前，拿到插入的数据，给数据主键列赋值
create or replace trigger auid
before
insert
on person
for each row
declare

before
    select s_person.nextval into :new.pid from dual;
end;

---使用auto实现住建自增
insert into person (pname) values ('a');
commit;