day011. oracle的相关概念1. 术语数据库

Oracle 数据库是数据的物理存储。 这就包括（数据文件 ORA 或者 DBF、控制文件、联机日志、参数文件）。其实 Oracle 数据库的概念和其它数据库不一样，这里的数据库是一个操作系统只有一个库。可以看作是 Oracle 就只有一个大数据库

实例

一个 Oracle 实例（Oracle Instance）有一系列的后台进程（Backguound Processes)和内存结构（Memory Structures)组成。 一个数据库可以有 n 个实例。

用户

用户是在实例下建立的。不同实例可以建相同名字的用户

表空间

表空间是 Oracle 对物理数据库上相关数据文件（ORA 或者 DBF 文件） 的逻辑映射。一个数据库在逻辑上被划分成一到若干个表空间，每个表空间包含了在逻辑上相关联的一组结构。每个数据库至少有一个表空间(称之为 system 表空间)。

每个表空间由同一磁盘上的一个或多个文件组成，这些文件叫数据文件(datafile)。一个数据文件只能属于一个表空间。

数据文件（dbf、ora）

数据文件是数据库的物理存储单位。 数据库的数据是存储在表空间中的， 真正是在某一个或者多个数据文件中。 而一个表空间可以由一个或多个数据文件组成，一个数据文件只能属于一个表空间。一旦数据文件被加入到某个表空间后，就不能删除这个文件，如果要删除某个数据文件，只能删除其所属于的表空间才行。

注： 表的数据，是有用户放入某一个表空间的，而这个表空间会随机把这些表数据放到一个或者多个数据文件中。

由于 oracle 的数据库不是普通的概念， oracle 是有用户和表空间对数据进行管理和存放的。但是表不是有表空间去查询的，而是由用户去查的。因为不同用户可以在同一个表空间建立同一个名字的表！这里区分就是用户了

2. 结构ORCL(实例)（192.168.106.10:1521/orcl）
​
    表空间（存放东西的文件）
​
        用户（system）
​
            table1
​
            table2
​
            ...

3. oracle创建一个表：1. 创建表空间

表空间是Oracle数据库的逻辑单元，一个表空间可以和多个数据文件（物理结构）关联

一个数据库下可以建立多个表空间，一个表空间可以建立多个用户、一个用户下可以建立多个表。

create tablespace itcast  -- 表空间名称
datafile 'c:\itcast.dbf'  -- 指定表空间对应的数据文件
size 100m  -- 表空间初始大小
autoextend on  -- 自动增长
next 10m  -- 一次自动增长的大小2. 创建用户

Oracle的数据库的表和它的数据库对象都是存储在用户下的。

create user itcastuser
identified by itcast  -- 密码
default tablespace itcast  --表空间名称3. 用户赋予权限

三种角色：connect 基本的；resource 授予开发人员 dba 最高权限

需要在system用户下赋予权限

grant dba to itcastuser

4. Oracle的数据类型No数据类型描述
1Varchar,varchar2表示字符串
2NUMBERNUMBER(m,n)表示一个小数，总长度为m，小数位n，整数为m-n
3DATE表示日期类型
4CLOB大对象，存大文本数据类型，可存4G
5BLOG大对象，存二进制数据，可存4G5. 建表
create table person(
    pid number(10),
    name varchar2(10),
    gender number(1) default 1,
    birthday date
);
​
insert into person(pid,name,gender,birthday) values(1,'张三',1,to_date('1999-12-22','yyyy-MM-dd'));
​中文乱码的问题解决
select userenv('language') from dual;--查看服务器端编码，结果为AMERICAN_AMERICA.ZHS16GBK

再设置环境变量：变量名：NLS_LANG，值为查到的值

重启PLSQL

4. 表的操作1. 表的删除
drop table xxx2. 表的修改

修改表的结构

---修改表结构
---添加一列
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;3. 数据的增加和修改

注意手动提交事务

---查询表中记录
select * from person;
----添加一条记录
insert into person (pid, pname) values (1, '小明');
commit;
----修改一条记录
update person set pname = '小马' where pid = 1;
commit;4. 数据的删除
----三个删除
--删除表中全部记录
delete from person;
--删除表结构
drop table person;
--先删除表，再次创建表。效果等同于删除表中全部记录。
--在数据量大的情况下，尤其在表中带有索引的情况下，该操作效率高。
--索引可以提高查询效率，但是会影响增删改效率。
truncate table person;5. 序列

Oracle靠序列完成自动增长

----序列不真的属于任何一张表，但是可以逻辑和表做绑定。
----序列：默认从1开始，依次递增，主要用来给主键赋值使用。
----dual：虚表，只是为了补全语法，没有任何意义。
-- nextval 取得序列的下一个内容 currval取得序列的当前内容
create sequence s_person;
select s_person.nextval from dual;
----添加一条记录
insert into person (pid, pname) values (s_person.nextval, '小明');
commit;
select * from person;5. Scott用户的表
----scott用户，密码tiger。
--解锁scott用户
alter user scott account unlock;
--解锁scott用户的密码【此句也可以用来重置密码】
alter user scott identified by tiger;
--切换到scott用户下1. 单行函数
--单行函数：作用于一行，返回一个值。
---字符函数
select upper('yes') from dual;--YES
select lower('YES') from dual;--yes
----数值函数
select round(56.16, -2) from dual;---四舍五入，后面的参数表示保留的位数
select trunc(56.16, -1) from dual;---直接截取，不在看后面位数的数字是否大于5.
select mod(10, 3) from dual;---求余数
----日期函数
----查询出emp表中所有员工入职距离现在几天。
select sysdate-e.hiredate from emp e;
----算出明天此刻
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 round((sysdate-e.hiredate)/7) from emp e;
----转换函数
---日期转字符串
select to_char(sysdate, 'fm yyyy-mm-dd hh24:mi:ss') from dual;
---字符串转日期
select to_date('2018-6-7 16:39:50', 'fm yyyy-mm-dd hh24:mi:ss') from dual;
----通用函数
---算出emp表中所有员工的年薪
----奖金里面有null值，如果null值和任意数字做算术运算，结果都是null。
select e.sal*12+nvl(e.comm, 0) from emp e;
---条件表达式
---条件表达式的通用写法，mysql和oracle通用
---给emp表中员工起中文名
select e.ename,
       case e.ename
         when 'SMITH' then '曹贼'
           when 'ALLEN' then '大耳贼'
             when 'WARD' then '诸葛小儿'
               --else '无名'
                 end
from emp e;
---判断emp表中员工工资，如果高于3000显示高收入，如果高于1500低于3000显示中等收入，
-----其余显示低收入
select e.sal,
       case
         when e.sal>3000 then '高收入'
           when e.sal>1500 then '中等收入'
               else '低收入'
                 end
from emp e;
----oracle中除了起别名，都用单引号。
----oracle专用条件表达式
select e.ename,
        decode(e.ename,
          'SMITH',  '曹贼',
            'ALLEN',  '大耳贼',
              'WARD',  '诸葛小儿',
                '无名') "中文名"            
from emp e;
​2. 多行函数
--多行函数【聚合函数】：作用于多行，返回一个值。
select count(1) from emp;---查询总数量
select sum(sal) from emp;---工资总和
select max(sal) from emp;---最大工资
select min(sal) from emp;---最低工资
select avg(sal) from emp;---平均工资
​3. 分组查询
-- 分组查询
-- 查询出每个部门的平均工资
select e.deptno,avg(e.sal)
from emp e
group by e.deptno;
-- 查询出平均工资高于2000的部门信息
select e.deptno,avg(e.sal) asal
from emp e
group by e.deptno
having avg(e.sal)>2000
-- 查询出每个部门工资高于800的员工的平均工资
select e.deptno,avg(e.sal) asal
from emp e
where e.sal>800
group by e.deptno;
-- 查询出每个部门工资高于800的员工的平均工资，再查询出平均工资高于2000的部门
select e.deptno,avg(e.sal) asal
from emp e
where e.sal>800
group by e.deptno
having avg(e.sal)>2000;6. 多表查询
-- 笛卡尔积
-- 等值连接
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 e1.ename,e2.ename
from emp e1,emp e2
where e1.mgr=e2.empno;
-- 查询员工姓名，员工部门名称，员工领导姓名，员工领导部门名称
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;子查询
-- 子查询返回一个值
-- 查询出工资和Scott一样的员工信息
select * from emp where sal in(
select sal from emp where ename='SCOTT');
-- 子查询返回一个集合
-- 查询出工资和10号部门任意员工一样的员工信息
select * from emp where sal in
(select sal from emp where deptno=10);
-- 子查询返回一张表
-- 查询出每个部门最低工资，和最低工资员工姓名，和该员工所在部门名称
-- 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.msal=e.sal
and e.deptno=d.deptno;分页

rownum行号：当我们做select操作的时候，每查询出一行记录，就会在该行上加上一个行号，行号从1开始，依次递增，不能跳着走。

排序操作会影响rownum的顺序，如果涉及到排序，还是要用rownum的话，可以再次嵌套查询

select rownum,t.* from(
select rownum,e.* from emp e order by e.sal desc)t;
-- emp表工资倒序排列后，每页五条记录，查询第二页
-- rownum不能写上大于一个正数
select * from(
    select rownum rn,tt.* from(
        select * from emp order by sal desc
    )tt where rownum<11
)where rn>5;day02

1. 重要概念1. 视图视图的概念：

视图就是提供一个查询的窗口，所有数据来自于原表

视图是一种数据库对象，是从一个或者多个数据表或视图中导出的虚表，视图所对应的数据并不真正地存储在视图中，而是存储在所引用的数据表中，视图的结构和数据是对数据表进行查询的结果。

-- 查询语句创建表
create table emp as select * from scott.emp;
select * from emp;
-- 创建视图【必须有dba权限】
create view v_emp as select ename,job from emp;
-- 查询视图
select * from v_emp;
-- 修改视图【不推荐】
update v_emp set job='CLERK' where ename='ALEEN';
commit;
-- 创建只读视图
create view v_emp1 as select ename,job from emp with read only;视图的作用

第一：视图可以屏蔽掉一些敏感字段

第二：保证总部和分布数据及时统一

详细：

1.简化数据操作：视图可以简化用户处理数据的方式。2.着重于特定数据：不必要的数据或敏感数据可以不出现在视图中。3.视图提供了一个简单而有效的安全机制，可以定制不同用户对数据的访问权限。4.提供向后兼容性：视图使用户能够在表的架构更改时为表创建向后兼容接口。

2. 索引索引的概念

索引就是在表的列上构建一个二叉树

-- 单列索引
-- 创建单列索引
create index idx_ename on emp(ename);
-- 单列索引触发规则，条件必须是索引列中的原始值
-- 单行函数，模糊查询，都会影响索引的触发
select * from emp where ename='SCOTT';
-- 复合索引
-- 创建复合索引
create index idx_enamejob on emp(ename,job);
-- 复合索引中第一列为优先检索列
-- 如果要触发复合索引，必须包含有优先检索列中的原始值
select * from emp where ename='SCOTT' and job='xx';--触发复合索引
select * from emp where ename='SCOTT' or job='xx';--不触发索引
select * from emp where ename='SCOTT';--触发单列索引索引作用

达到大幅度提高查询效率的目的。但是会影响增删改的效率

2. pl/sql编程语言1. 概念

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

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

pl/sql编程语言主要用来编写存储过程和存储函数等

基本语法结构

declare
-- 声明变量
begin
-- 代码逻辑
exception
-- 异常处理
end;

2. 声明方法

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

declare
    i number(2) :=10;
    s varchar2(10) :='小明';
    ena emp.ename%type; -- 引用型变量
    emprow emp%rowtype; -- 记录型变量
begin
    dbms_output.put_line(i);
    dbms_output.put_line(s);
    select ename into ena from emp where empno=7788;
    dbms_output.put_line(ena);
    select * into emprow from emp where empno=7788;
    dbms_output.put_line(emprow.ename || '的工作为：'||emprow.job);
end;3. if判断
-- 输入小于18的数字，输出未成年
-- 输入大于18小于40的数字，输出中年人
-- 输入大于40的数字，输出老年人
declare
    i number(3) := &ii;
begin
    if i<18 then
        dbms_output.put_line('未成年');
    elsif i<40 then
        dbms_output.put_line('中年人');
    else
        dbms_output.put_line('老年人');
    end if;
end;4. 三种loop循环
-- 用三种方式输出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
​
begin
    for i in 1..10 loop
        dbms_output.put_line(i);
    end loop;
end;4. 游标

可以存放多个对象，多行记录

可以把游标理解为PL/SQL中的结果集

declare
    cursor c1 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 emp.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;
​
-- 查询10号部门员工信息
select * from emp where deptno=10;5. 存储过程

就是提前已经编译好的一段pl/sql语言，放置在数据库端，可以直接被调用，这一段pl/sql一般都是固定步骤的业务

-- 给指定员工涨100块钱
create or replace procedure p1(eno emp.empno%type)
is
begin
    update emp set sal=sal+100 where empno=eno;
    commit;
end;
​
select * from emp where empno=7788;
-- 测试p1
declare
​
begin
    p1(7788);
end;6. 存储函数
-- 通过存储函数实现计算指定员工的年薪
-- 存储过程和存储函数的参数都不能带长度
-- 存储函数的返回值类型不能带长度
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 empno.comm%type;
begin
    select sal*12,nvl(comm,0) into s,c from emp where empno=eno;
    yearsal :=s+c;
end;
​
-- 测试p_yearsal
declare
    yearsal number(10);
begin
    p_yearsal(7788,yearsal);
    dbms_output.put_line(yearsal);
end;in和out类型参数的区别是什么

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

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

• 语法区别：关键字不一样，存储函数比存储过程多了两个return
• 本质区别：存储函数有返回值，存储过程没有返回值
  
  

存储过程：涨工资 --> 修改语句-->相当于没有返回值的方法

对数据库的增删改

存储函数：对数据库进行查询，有返回值，在存储函数中查询数据库，然后把结果返回给调用处。

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

我们可以使用存储函数有返回值的特性，自定义函数；而存储过程不能自定义函数

-- 案例需求：查询员工姓名，员工所在部门名称
-- 使用存储函数来实现提供一个部门编号，输出一个部门名称
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;7. 触发器概念

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

• 语句级触发器：不包含for each row的触发器
• 行级触发器：包含for each row的就是行级触发器
  加for each row是为了使用:old或者:new对象或者一行记录
  
  

-- 语句级触发器
-- 插入一条记录，输出一个新员工入职
create or replace trigger t1
after
insert
on person
declare
​
begin
    dbms_output.put_line('一个新员工入职');
end;
-- 触发t1
insert into person values(1,'小红');
commit;
select * from person;
​
-- 行级别触发器
-- 不能给员工降薪
-- raise_application_error(-20001~-20999之间,'错误提示信息');
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
select * from emp where empno = 7788;
update emp set sal=sal-1 where empno=7788;
commit;

触发器实现主键自增 【行级触发器】
-- 分析：在用户做插入操作之前，拿到即将插入的数据给该数据的主键列赋值
create or replace trigger auid
before
insert
on person
for each row
declare
​
begin
    select s_person.nextval into :new.pid from dual;
end;
-- 查询person表数据
select * from person;
-- 使用auid实现主键自增
insert into person(pname) values('a');
commit;
insert into person values(1,'b');
commit;3. java对Oracle的调用

包括调用存储过程和存储函数

pom文件配置

<dependencies>
        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc14</artifactId>
            <version>10.2.0.4.0</version>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
    </dependencies>

OracleDemo.java

public class OracleDemo {
​
    @Test
    public void javaCallOracle() throws Exception{
        // 加载数据库驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
        // 得到Connection连接
        Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@192.168.106.10:1521:orcl", "scott", "tiger");
        // 得到预编译的Statement对象
        PreparedStatement pstm = connection.prepareStatement("select * from emp");
//        pstm.setObject(1,1);
        // 执行数据库查询操作
        ResultSet rs = pstm.executeQuery();
        // 输出结果
        while(rs.next()){
            System.out.println(rs.getString("ename"));
        }
        rs.close();
        pstm.close();
        connection.close();
    }
​
    /**
     *  java调用存储过程
     *  {?= call <procedure-name>[(<arg1>,<arg2>, ...)]} 调用存储函数使用
     *    {call <procedure-name>[(<arg1>,<arg2>, ...)]} 调用存储过程使用
     * @throws Exception
     */
    @Test
    public void javaCallProcedure() throws Exception{
        // 加载数据库驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
        // 得到Connection连接
        Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@192.168.106.10:1521:orcl", "scott", "tiger");
        // 得到预编译的Statement对象
        CallableStatement pstm = connection.prepareCall("{call p_yearsal(?,?)}");
        pstm.setObject(1,7788);
        pstm.registerOutParameter(2, OracleTypes.NUMBER);
        // 执行数据库查询操作
        pstm.execute();
        // 输出结果
        System.out.println(pstm.getObject(2));
        pstm.close();
        connection.close();
    }
​
    @Test
    public void javaCallFunction() throws Exception{
        // 加载数据库驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
        // 得到Connection连接
        Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@192.168.106.10:1521:orcl", "scott", "tiger");
        // 得到预编译的Statement对象
        CallableStatement pstm = connection.prepareCall("{?=call f_yearsal(?)}");
        pstm.setObject(2,7788);
        pstm.registerOutParameter(1, OracleTypes.NUMBER);
        // 执行数据库查询操作
        pstm.execute();
        // 输出结果[第一个参数]
        System.out.println(pstm.getObject(1));
        pstm.close();
        connection.close();
    }
}

day011. oracle的相关概念1. 术语数据库

Oracle 数据库是数据的物理存储。 这就包括（数据文件 ORA 或者 DBF、控制文件、联机日志、参数文件）。其实 Oracle 数据库的概念和其它数据库不一样，这里的数据库是一个操作系统只有一个库。可以看作是 Oracle 就只有一个大数据库

实例

一个 Oracle 实例（Oracle Instance）有一系列的后台进程（Backguound Processes)和内存结构（Memory Structures)组成。 一个数据库可以有 n 个实例。

用户

用户是在实例下建立的。不同实例可以建相同名字的用户

表空间

表空间是 Oracle 对物理数据库上相关数据文件（ORA 或者 DBF 文件） 的逻辑映射。一个数据库在逻辑上被划分成一到若干个表空间，每个表空间包含了在逻辑上相关联的一组结构。每个数据库至少有一个表空间(称之为 system 表空间)。

每个表空间由同一磁盘上的一个或多个文件组成，这些文件叫数据文件(datafile)。一个数据文件只能属于一个表空间。

数据文件（dbf、ora）

数据文件是数据库的物理存储单位。 数据库的数据是存储在表空间中的， 真正是在某一个或者多个数据文件中。 而一个表空间可以由一个或多个数据文件组成，一个数据文件只能属于一个表空间。一旦数据文件被加入到某个表空间后，就不能删除这个文件，如果要删除某个数据文件，只能删除其所属于的表空间才行。

注： 表的数据，是有用户放入某一个表空间的，而这个表空间会随机把这些表数据放到一个或者多个数据文件中。

由于 oracle 的数据库不是普通的概念， oracle 是有用户和表空间对数据进行管理和存放的。但是表不是有表空间去查询的，而是由用户去查的。因为不同用户可以在同一个表空间建立同一个名字的表！这里区分就是用户了

2. 结构ORCL(实例)（192.168.106.10:1521/orcl）
​
    表空间（存放东西的文件）
​
        用户（system）
​
            table1
​
            table2
​
            ...

3. oracle创建一个表：1. 创建表空间

表空间是Oracle数据库的逻辑单元，一个表空间可以和多个数据文件（物理结构）关联

一个数据库下可以建立多个表空间，一个表空间可以建立多个用户、一个用户下可以建立多个表。

create tablespace itcast  -- 表空间名称
datafile 'c:\itcast.dbf'  -- 指定表空间对应的数据文件
size 100m  -- 表空间初始大小
autoextend on  -- 自动增长
next 10m  -- 一次自动增长的大小2. 创建用户

Oracle的数据库的表和它的数据库对象都是存储在用户下的。

create user itcastuser
identified by itcast  -- 密码
default tablespace itcast  --表空间名称3. 用户赋予权限

三种角色：connect 基本的；resource 授予开发人员 dba 最高权限

需要在system用户下赋予权限

grant dba to itcastuser

4. Oracle的数据类型No数据类型描述
1Varchar,varchar2表示字符串
2NUMBERNUMBER(m,n)表示一个小数，总长度为m，小数位n，整数为m-n
3DATE表示日期类型
4CLOB大对象，存大文本数据类型，可存4G
5BLOG大对象，存二进制数据，可存4G5. 建表
create table person(
    pid number(10),
    name varchar2(10),
    gender number(1) default 1,
    birthday date
);
​
insert into person(pid,name,gender,birthday) values(1,'张三',1,to_date('1999-12-22','yyyy-MM-dd'));
​中文乱码的问题解决
select userenv('language') from dual;--查看服务器端编码，结果为AMERICAN_AMERICA.ZHS16GBK

再设置环境变量：变量名：NLS_LANG，值为查到的值

重启PLSQL

4. 表的操作1. 表的删除
drop table xxx2. 表的修改

修改表的结构

---修改表结构
---添加一列
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;3. 数据的增加和修改

注意手动提交事务

---查询表中记录
select * from person;
----添加一条记录
insert into person (pid, pname) values (1, '小明');
commit;
----修改一条记录
update person set pname = '小马' where pid = 1;
commit;4. 数据的删除
----三个删除
--删除表中全部记录
delete from person;
--删除表结构
drop table person;
--先删除表，再次创建表。效果等同于删除表中全部记录。
--在数据量大的情况下，尤其在表中带有索引的情况下，该操作效率高。
--索引可以提高查询效率，但是会影响增删改效率。
truncate table person;5. 序列

Oracle靠序列完成自动增长

----序列不真的属于任何一张表，但是可以逻辑和表做绑定。
----序列：默认从1开始，依次递增，主要用来给主键赋值使用。
----dual：虚表，只是为了补全语法，没有任何意义。
-- nextval 取得序列的下一个内容 currval取得序列的当前内容
create sequence s_person;
select s_person.nextval from dual;
----添加一条记录
insert into person (pid, pname) values (s_person.nextval, '小明');
commit;
select * from person;5. Scott用户的表
----scott用户，密码tiger。
--解锁scott用户
alter user scott account unlock;
--解锁scott用户的密码【此句也可以用来重置密码】
alter user scott identified by tiger;
--切换到scott用户下1. 单行函数
--单行函数：作用于一行，返回一个值。
---字符函数
select upper('yes') from dual;--YES
select lower('YES') from dual;--yes
----数值函数
select round(56.16, -2) from dual;---四舍五入，后面的参数表示保留的位数
select trunc(56.16, -1) from dual;---直接截取，不在看后面位数的数字是否大于5.
select mod(10, 3) from dual;---求余数
----日期函数
----查询出emp表中所有员工入职距离现在几天。
select sysdate-e.hiredate from emp e;
----算出明天此刻
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 round((sysdate-e.hiredate)/7) from emp e;
----转换函数
---日期转字符串
select to_char(sysdate, 'fm yyyy-mm-dd hh24:mi:ss') from dual;
---字符串转日期
select to_date('2018-6-7 16:39:50', 'fm yyyy-mm-dd hh24:mi:ss') from dual;
----通用函数
---算出emp表中所有员工的年薪
----奖金里面有null值，如果null值和任意数字做算术运算，结果都是null。
select e.sal*12+nvl(e.comm, 0) from emp e;
---条件表达式
---条件表达式的通用写法，mysql和oracle通用
---给emp表中员工起中文名
select e.ename,
       case e.ename
         when 'SMITH' then '曹贼'
           when 'ALLEN' then '大耳贼'
             when 'WARD' then '诸葛小儿'
               --else '无名'
                 end
from emp e;
---判断emp表中员工工资，如果高于3000显示高收入，如果高于1500低于3000显示中等收入，
-----其余显示低收入
select e.sal,
       case
         when e.sal>3000 then '高收入'
           when e.sal>1500 then '中等收入'
               else '低收入'
                 end
from emp e;
----oracle中除了起别名，都用单引号。
----oracle专用条件表达式
select e.ename,
        decode(e.ename,
          'SMITH',  '曹贼',
            'ALLEN',  '大耳贼',
              'WARD',  '诸葛小儿',
                '无名') "中文名"            
from emp e;
​2. 多行函数
--多行函数【聚合函数】：作用于多行，返回一个值。
select count(1) from emp;---查询总数量
select sum(sal) from emp;---工资总和
select max(sal) from emp;---最大工资
select min(sal) from emp;---最低工资
select avg(sal) from emp;---平均工资
​3. 分组查询
-- 分组查询
-- 查询出每个部门的平均工资
select e.deptno,avg(e.sal)
from emp e
group by e.deptno;
-- 查询出平均工资高于2000的部门信息
select e.deptno,avg(e.sal) asal
from emp e
group by e.deptno
having avg(e.sal)>2000
-- 查询出每个部门工资高于800的员工的平均工资
select e.deptno,avg(e.sal) asal
from emp e
where e.sal>800
group by e.deptno;
-- 查询出每个部门工资高于800的员工的平均工资，再查询出平均工资高于2000的部门
select e.deptno,avg(e.sal) asal
from emp e
where e.sal>800
group by e.deptno
having avg(e.sal)>2000;6. 多表查询
-- 笛卡尔积
-- 等值连接
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 e1.ename,e2.ename
from emp e1,emp e2
where e1.mgr=e2.empno;
-- 查询员工姓名，员工部门名称，员工领导姓名，员工领导部门名称
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;子查询
-- 子查询返回一个值
-- 查询出工资和Scott一样的员工信息
select * from emp where sal in(
select sal from emp where ename='SCOTT');
-- 子查询返回一个集合
-- 查询出工资和10号部门任意员工一样的员工信息
select * from emp where sal in
(select sal from emp where deptno=10);
-- 子查询返回一张表
-- 查询出每个部门最低工资，和最低工资员工姓名，和该员工所在部门名称
-- 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.msal=e.sal
and e.deptno=d.deptno;分页

rownum行号：当我们做select操作的时候，每查询出一行记录，就会在该行上加上一个行号，行号从1开始，依次递增，不能跳着走。

排序操作会影响rownum的顺序，如果涉及到排序，还是要用rownum的话，可以再次嵌套查询

select rownum,t.* from(
select rownum,e.* from emp e order by e.sal desc)t;
-- emp表工资倒序排列后，每页五条记录，查询第二页
-- rownum不能写上大于一个正数
select * from(
    select rownum rn,tt.* from(
        select * from emp order by sal desc
    )tt where rownum<11
)where rn>5;day02

1. 重要概念1. 视图视图的概念：

视图就是提供一个查询的窗口，所有数据来自于原表

视图是一种数据库对象，是从一个或者多个数据表或视图中导出的虚表，视图所对应的数据并不真正地存储在视图中，而是存储在所引用的数据表中，视图的结构和数据是对数据表进行查询的结果。

-- 查询语句创建表
create table emp as select * from scott.emp;
select * from emp;
-- 创建视图【必须有dba权限】
create view v_emp as select ename,job from emp;
-- 查询视图
select * from v_emp;
-- 修改视图【不推荐】
update v_emp set job='CLERK' where ename='ALEEN';
commit;
-- 创建只读视图
create view v_emp1 as select ename,job from emp with read only;视图的作用

第一：视图可以屏蔽掉一些敏感字段

第二：保证总部和分布数据及时统一

详细：

1.简化数据操作：视图可以简化用户处理数据的方式。2.着重于特定数据：不必要的数据或敏感数据可以不出现在视图中。3.视图提供了一个简单而有效的安全机制，可以定制不同用户对数据的访问权限。4.提供向后兼容性：视图使用户能够在表的架构更改时为表创建向后兼容接口。

2. 索引索引的概念

索引就是在表的列上构建一个二叉树

-- 单列索引
-- 创建单列索引
create index idx_ename on emp(ename);
-- 单列索引触发规则，条件必须是索引列中的原始值
-- 单行函数，模糊查询，都会影响索引的触发
select * from emp where ename='SCOTT';
-- 复合索引
-- 创建复合索引
create index idx_enamejob on emp(ename,job);
-- 复合索引中第一列为优先检索列
-- 如果要触发复合索引，必须包含有优先检索列中的原始值
select * from emp where ename='SCOTT' and job='xx';--触发复合索引
select * from emp where ename='SCOTT' or job='xx';--不触发索引
select * from emp where ename='SCOTT';--触发单列索引索引作用

达到大幅度提高查询效率的目的。但是会影响增删改的效率

2. pl/sql编程语言1. 概念

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

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

pl/sql编程语言主要用来编写存储过程和存储函数等

基本语法结构

declare
-- 声明变量
begin
-- 代码逻辑
exception
-- 异常处理
end;

2. 声明方法

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

declare
    i number(2) :=10;
    s varchar2(10) :='小明';
    ena emp.ename%type; -- 引用型变量
    emprow emp%rowtype; -- 记录型变量
begin
    dbms_output.put_line(i);
    dbms_output.put_line(s);
    select ename into ena from emp where empno=7788;
    dbms_output.put_line(ena);
    select * into emprow from emp where empno=7788;
    dbms_output.put_line(emprow.ename || '的工作为：'||emprow.job);
end;3. if判断
-- 输入小于18的数字，输出未成年
-- 输入大于18小于40的数字，输出中年人
-- 输入大于40的数字，输出老年人
declare
    i number(3) := &ii;
begin
    if i<18 then
        dbms_output.put_line('未成年');
    elsif i<40 then
        dbms_output.put_line('中年人');
    else
        dbms_output.put_line('老年人');
    end if;
end;4. 三种loop循环
-- 用三种方式输出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
​
begin
    for i in 1..10 loop
        dbms_output.put_line(i);
    end loop;
end;4. 游标

可以存放多个对象，多行记录

可以把游标理解为PL/SQL中的结果集

declare
    cursor c1 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 emp.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;
​
-- 查询10号部门员工信息
select * from emp where deptno=10;5. 存储过程

就是提前已经编译好的一段pl/sql语言，放置在数据库端，可以直接被调用，这一段pl/sql一般都是固定步骤的业务

-- 给指定员工涨100块钱
create or replace procedure p1(eno emp.empno%type)
is
begin
    update emp set sal=sal+100 where empno=eno;
    commit;
end;
​
select * from emp where empno=7788;
-- 测试p1
declare
​
begin
    p1(7788);
end;6. 存储函数
-- 通过存储函数实现计算指定员工的年薪
-- 存储过程和存储函数的参数都不能带长度
-- 存储函数的返回值类型不能带长度
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 empno.comm%type;
begin
    select sal*12,nvl(comm,0) into s,c from emp where empno=eno;
    yearsal :=s+c;
end;
​
-- 测试p_yearsal
declare
    yearsal number(10);
begin
    p_yearsal(7788,yearsal);
    dbms_output.put_line(yearsal);
end;in和out类型参数的区别是什么

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

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

• 语法区别：关键字不一样，存储函数比存储过程多了两个return
• 本质区别：存储函数有返回值，存储过程没有返回值
  
  

存储过程：涨工资 --> 修改语句-->相当于没有返回值的方法

对数据库的增删改

存储函数：对数据库进行查询，有返回值，在存储函数中查询数据库，然后把结果返回给调用处。

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

我们可以使用存储函数有返回值的特性，自定义函数；而存储过程不能自定义函数

-- 案例需求：查询员工姓名，员工所在部门名称
-- 使用存储函数来实现提供一个部门编号，输出一个部门名称
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;7. 触发器概念

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

• 语句级触发器：不包含for each row的触发器
• 行级触发器：包含for each row的就是行级触发器
  加for each row是为了使用:old或者:new对象或者一行记录
  
  

-- 语句级触发器
-- 插入一条记录，输出一个新员工入职
create or replace trigger t1
after
insert
on person
declare
​
begin
    dbms_output.put_line('一个新员工入职');
end;
-- 触发t1
insert into person values(1,'小红');
commit;
select * from person;
​
-- 行级别触发器
-- 不能给员工降薪
-- raise_application_error(-20001~-20999之间,'错误提示信息');
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
select * from emp where empno = 7788;
update emp set sal=sal-1 where empno=7788;
commit;

触发器实现主键自增 【行级触发器】
-- 分析：在用户做插入操作之前，拿到即将插入的数据给该数据的主键列赋值
create or replace trigger auid
before
insert
on person
for each row
declare
​
begin
    select s_person.nextval into :new.pid from dual;
end;
-- 查询person表数据
select * from person;
-- 使用auid实现主键自增
insert into person(pname) values('a');
commit;
insert into person values(1,'b');
commit;3. java对Oracle的调用

包括调用存储过程和存储函数

pom文件配置

<dependencies>
        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc14</artifactId>
            <version>10.2.0.4.0</version>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
    </dependencies>

OracleDemo.java

public class OracleDemo {
​
    @Test
    public void javaCallOracle() throws Exception{
        // 加载数据库驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
        // 得到Connection连接
        Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@192.168.106.10:1521:orcl", "scott", "tiger");
        // 得到预编译的Statement对象
        PreparedStatement pstm = connection.prepareStatement("select * from emp");
//        pstm.setObject(1,1);
        // 执行数据库查询操作
        ResultSet rs = pstm.executeQuery();
        // 输出结果
        while(rs.next()){
            System.out.println(rs.getString("ename"));
        }
        rs.close();
        pstm.close();
        connection.close();
    }
​
    /**
     *  java调用存储过程
     *  {?= call <procedure-name>[(<arg1>,<arg2>, ...)]} 调用存储函数使用
     *    {call <procedure-name>[(<arg1>,<arg2>, ...)]} 调用存储过程使用
     * @throws Exception
     */
    @Test
    public void javaCallProcedure() throws Exception{
        // 加载数据库驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
        // 得到Connection连接
        Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@192.168.106.10:1521:orcl", "scott", "tiger");
        // 得到预编译的Statement对象
        CallableStatement pstm = connection.prepareCall("{call p_yearsal(?,?)}");
        pstm.setObject(1,7788);
        pstm.registerOutParameter(2, OracleTypes.NUMBER);
        // 执行数据库查询操作
        pstm.execute();
        // 输出结果
        System.out.println(pstm.getObject(2));
        pstm.close();
        connection.close();
    }
​
    @Test
    public void javaCallFunction() throws Exception{
        // 加载数据库驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
        // 得到Connection连接
        Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@192.168.106.10:1521:orcl", "scott", "tiger");
        // 得到预编译的Statement对象
        CallableStatement pstm = connection.prepareCall("{?=call f_yearsal(?)}");
        pstm.setObject(2,7788);
        pstm.registerOutParameter(1, OracleTypes.NUMBER);
        // 执行数据库查询操作
        pstm.execute();
        // 输出结果[第一个参数]
        System.out.println(pstm.getObject(1));
        pstm.close();
        connection.close();
    }
}