Oracle数据

MySQL数据源
driver: com.mysql.jdbc.Drvier
url: jdbc:mysql://localhost:3306/[database]
username:root
password:root

Oracle数据库源
driver: oracle.jdbc.OracleDriver
url: jdbc:oracle:thin:@localhost:1521:orcl
username: zhangsan
password: zhangsan

Oracle分组函数
distinct（去重）

count（）统计数据条数
min（）最小值
max（）最大值
avg（）平均值
sum（）求和

分组统计
group by

分组统计条件限定查询
having 

exists使用
in关键字少使用，可以使用exists来代替性能很高
exists（）【存在】
not exists（）【不存在】

并集union和union all

union去重数据
union all 不去重复数据

数据库的变更
insert into 表名（列名） values （值）

update 表名 set 列名1=值1，…… where 修改条件

delete from 表名 where 删除条件

数据库事务
增删改操作都需要开启事务
commit 【提交】
rollback【回滚】事务在没有提交前可以回滚

select * from empty for update 带着事务的查询【for update 带着事务的查询】

Oracle的DDL
数据类型
varchar，varchar2  字符串类型
number    数值类型
data    日期类型
CLOB    大文本数据类型，可存4G
BLOB    二进制数据，可存4G

建表
Create table 表名（
字段1 数据类型 [default  默认值]，
……
字段n 数据类型 [default  默认值]
）;【create 创建、table 表格、default 默认值】

删表
drop table 表名【drop 除名】

表的修改
添加语法：
alter table 表名称 add（列名1 数据类型 [default 默认值]，列名2 数据类型 [default 默认值]……）;【alter 改动、add 添加】
修改语法：
alter table 表名称 modify（列名1 数据类型 [default 默认值]，列名1 数据类型 [default 默认值]……）;【alter 改动、modify 修改】

截断表
truncate table 表名；（可以立即释放资源不需要提交commit）【谨慎使用】

约束
（使用约束可以保证数据的完整性）

主键约束
（主键约束都是在ID上使用，而且本身已经默认了内容不能为空，可以在建表的时候指定。）
create table 表名（
    列名1    数据类型  primary key,（主键是唯一的，不能为空）【primary key 主键约束】
    列名2    数据类型,
    列名2    数据类型
）;

自定义主键名称
create table 表名（
    列名1    数据类型，
    列名2    数据类型，
    列名2    数据类型，
    constraint  自定义主键名称（PK） primary key(指定列为主键，列名)【constraint 约束条】
）;

非空约束
create table 表名（
    列名1    数据类型，
    列名2    数据类型    not null，【not null 非空约束】
    列名2    数据类型，
    constraint  自定义主键名称（PK） primary key(指定列为主键，列名)【constraint 约束条件、primary key 主键约束】
）;

唯一约束
create table 表名（
    列名1    数据类型，
    列名2    数据类型    unique，【unique 唯一】
    列名3    数据类型
）;

自定义唯一约束名称
create table 表名（
    列名1    数据类型，
    列名2    数据类型，
    列名3    数据类型，
    constraint 自定义唯一约束名称（UK）unique （指定列名唯一约束）【constraint 约束条件 、unique 唯一】
）;

检查约束
（检查约束可以约束字段值得合法范围，创建一张表性别只能是1或2）
create table 表名（
    列名1    数据类型，
    列名2    数据类型    check (列名2 in（1，2）)，【check 检查】
    列名3    数据类型
）;

自定义检查约束名称
create table 表名（
    列名1    数据类型，
    列名2    数据类型，
    列名3    数据类型，
    constraint  自定义检查约束名称（CK）check（指定列名 in（1,2））【constraint 约束条件、check检查】
）;

外键约束
（主键约束、非空约束、唯一约束、检查约束都是单表约束，外键约束是两张表的约束，可以保证关联数据的完整性。）
商品表名
create table orders（
    orders_id        number(4),
    totalPrice        number(7.2),
    order_time    date,
    constraint orders_orders_id_pk primary key(orders_id)
）;
明细表
create table order_detail（
    detail_id        number(4),
    item_name    varchar2(10),
    price        number(7.2),
    quantity        number(4),
    orders_id        number(4),
    constraint order_detail_detail_id_pk primary key(detail_id),
    constraint order_detail_orders_id_fk foreign key(orders_id) references orders(orders_id)
）;【constraint 约束条件、foreign key 外键约束、references 参照】

注意：
1、子表插入时有外键约束，主表删除数据时也有外键约束，如果想要删除需要先删除字表的数据在删除主表。
2、主表删除时受外键的约束，需要先删除字表在删除主表。
可以强制删除表：
drop table orders cascade constraint；（不建议使用）
删除主表数据可以删除字表的关联数据，在删主表，也可以使用级联删除级联删除在外键约束上要加上 on delete cascade 如：
constraint order_detail_orders_id_fk foreign key(orders_id) references orders(orders_id) on delete cascade
这样删除主表数据的时候会把子表的关联数据一同删除

商品表
create table 表名（
    列名1        数据类型,
    列名2        数据类型,
    列名3        数据类型,
    constraint 自定义商品表名主键名称 primary key(指定列名为主键)
）;
明细表
create table 表名（
    列名1        数据类型,
    列名2        数据类型,
    列名3        数据类型,
    列名4        数据类型,
    列名5        数据类型,
    constraint 自定义明细表主键名称（PK） primary key(指定列名为主键),
    constraint 自定义外键约束名称（FK） foreign key(指定明细表列名) references 商品表名(商品表主键) on delete cascade
）;【constraint 约束条件、primary key 主键约束 、foreign key 外键约束、references 参照、on delete cascade 级联删除】

rownum和分页
【rownum 行号】（rownum 不支持大于号）
select rownum, * from 表名

分页步骤
第一步查询全量的数据
select * from emp;
第二步以第一步的结果集作为一张表，限定条件是rownum小于结束行号，结果列把rownum作为结果集
select rownum rw, a.* from (
    select * from emp
     ) a where rownum < 6;

第三步以第二步的结果集作为一张表，限定条件是第二步的rownum列大于开始行号，结果列是*
select * from (select rownum rw, a.* from (
    select * from emp
) a where rownum < 6 ) b where b.rw > 0;

【已知条件
pageNO:当前的页码
pageSize:每页记录数

未知条件
startNum：开始行号
endNum：结束行号

pageNo    pageSize    startNum    endNum
    1         5           0          6
    2         5           5          11
    3         5           10          16
    startNum = （pageNo - 1）* pageSize；
    endNum = pageNo * pageSize + 1；
】

视图view
create view 视图名 select  * from 表名  where  条件；
创建视图或覆盖视图
create or replace view 视图名 select * from 表名  where 条件； 
修改视图，视图不建议修改
update 视图名 set 修改的值  where 修改的条件；
创建只读的视图
create or replace view 视图名 as select * from 表名 where 条件 with read only；

序列
语法：create sequence 序列名
    [increment by n]
    [start with n]
    [{maxvalue/ minvalue n|nomaxvalue}]
    [{cycle|nocycle}]
    [{cache n|nocache}];
范例：创建一个seqpersonid的序列，验证自动增长的操作
create sequence seqpersonid;(序列虽然是给某个表使用，但是序列并没有绑定自某一张表，任何一张表使用这个序列都可以。)
查询序列的下一个值（重点）
select seqpersonid.nextval from dual;
查看当前值是多少
select seqpersonid.currval from dual; 

创建单列索引
单列索引是基于单个列所建立的索引
create index 索引名 on 表名（列名）；

创建复合索引
复合索引是基于两个列或多个列的索引，查询数据使用的时候是有索引顺序的 
create index 索引名 on 表名（列名1,列名2）；
（索引使用原则：
1、在大表上建立索引才有意义。
2、在where子句后面或者是连接条件上建立索引。
3、索引的层次不要超过4层。
）

pl/sql
程序语法：
    declare
        说明部分    （变量说明，游标申明，例外说明）
    begin
        语句序列    （dml语句）……
    exception
        例外处理语句
    end；

常量和变量定义
变量的基本数据类型就是 Oracle 中的建表时字段的变量（char，varchar2，date，number，Boolean，long）
变量定义：
    varl    char(15);
    psal    number(9.2);（赋值 psalm := "null"）
常量定义：
    married    Boolean:=true;

基本数据类型变量和常量
    declare
        pname    carchear2(10);变量定义
        age    number(3):= 20;常量定义
    begin
        pname  :=  'zhangsan';
        dbms_output.put_line(pname);
        dbms_output.put_line(age);
    exception

    end;

引用数据类型变量
    myname     emp.ename%type;【变量名  表名.列名%type】
    引用型变量，即 my_name 的类型与 emp 表中 ename 列的类型一样
    在SQL中使用 into 来赋值（重点）

    declare
        pname  myemp.ename%type;【变量名  表名.列名%type】
    begin
        select t.ename into pname from myemp t where t.empno=7369;【把 t.ename into 赋值给pname 】
        dbms_output.put_line(pname);
    exception

    end;

记录类型变量
    emprec emp%rowtype；【变量名 表名%rowtype】

    declare
        prec myemp%rowtype;
    begin
        select * into prec from myemp t where t.empno = 7369;
        dbms_output.put_line(prec.ename ||  '    ' ||  prec.sal );
    end;

PLSQL的if分支
语法1：
    if    条件    then    语句1；
    语句2；
    end if；
语法2：
    if    条件    then    语句序列1；
    else    语句序列2；
    end    if；
语法3：
    if    条件    then    语句；
    else    条件    then    语句；
    else    语句；
    end    if；

经商不灵活，门前客不多，见人三分笑，客人跑不掉
语法1示例：
    declare
        pno    number(4)  :=  &num;
    beign
        if  pno < 5
          dbms_output.put_lin('编号小于5');
        end if；
    end；
语法2示例：
    declare
        pno    number(4)  :=  &num;
    begin
        if  pno = 1  then
           dbms_output.put_line('我是1');
        else 
           dbms_output.put_line('我不是1');
        end if；
    end；
语法3示例：
    declare
        pno    number(4)      := &num;
    begin
        if  pno  =  1  then
          dbms_output.put_line('我是1')；
        elsif pno  =  2 then
          dbms_output.put_line('我是2')；
        else 
          dbms_output.put_line('其他')；
        end if；
    end；

PLSQL的循环loop
语法1：
    while    total    <=25000    loop
    ...
    total := total+salary;
    end    loop;
语法2：(最常用的循环方式)
    loop
    exit(when    条件);
    ... ...
    end loop;
语法3：(适用于连续的数值遍历)
    for    i    in    1..3    loop
    语句序列；
    end    loop；

语法1示例：
    declare
        total     number(4)   :=  0;
    beign
        while  total < 100   loop
            total := total  + 1;
              dbms_output.put_line(total);
        end loop;
    exception

    end;
语法2示例：
    declare
        total  number(4)  :=  0;
    beign
        loop
            exit  when total  < 100;
            total := total +1;
              dbms_output.put_line(total);
        end loop;
    exception

    end;       
语法3示例：
    declare
        total  number(4)  :=  0;
    beign
        for  total in 1..100 loop
            dbms_output.put_line(total);
        end loop;
    exception

    end;

游标cursor
语法：
    cursor  游标名  [(参数名  数据类型，参数名 数据类型，……)]  is select 语句；
    列如：cursor  c1  is  select ename  from emp；
    使用步骤：
        打开游标：    open c1(打开游标执行查询)
        取一行游标的值：    fetch c1 into pjob;(取一行到变量中)
        关闭游标：    close c1；(关闭游标释放资源)
        游标的结束方式：    exit when c1%notfound
        注意：        上面的pjob必须与emp表中的job列类型一致
            定义：pjob emp.empjob%type；

使用游标方式输出emp表中的员工编号号姓名
    declare
        cursor c1 is select * from emp；--定义游标
        prec  emp%rowtype;--定义记录型的变量
    beign 
        open c1;--打开游标
        loop
            fetch c1 into prec;--从游标中取值，取值后游标会自动向下移动一步
            exit when c1%ontfund;
            dbms_output.put_line(prec.empno || '    ' ||prec.ename);
        end loop;--结束循环
        close c1;--关闭游标
    exception

    end;

按员工的工种涨工资，总裁涨1000元，经理涨800元，其他人员涨400元。
    declare
        cursor c1 select * from myemp;
        prec myemp%rowtype;
        addsal(4);
    beign
        open c1;
        loop
            fetch c1 into prec;
            exit when c1%notfound;
            if prec.job = 'PRESIDENT' then
                addsal :=1000;
            elsif prec.job = 'MANAGER'  then
                addsal := 800;
            else addsal := 400;
            end if;
            update myemp t set t.sal = t.sal + addsal where t.deptno = prec.deptno;
        end loop;
        close c1;
        commit;
    exception

    end;

带有参数的游标，写一段PL\SQL程序，为部门号为10的员工涨工资。
    declare
        cursor c1(dno myemp.deptno%type) is select * from myemp t where t.deptno = dno;【t.deptno要等于游标c1 (dno)传过来的参数】
        prec myemp%rowtype;
    beign
        open c1(10);--打开带有参数游标，除了此处其他地方游标都不用带参数
        loop
            fetch c1 into prec;
            exit when c1%notfound;
            update myemp t set t.sal = t.sal + 1000 where t.empno =prec.empno;
        end loop;
        close c1;--关闭游标
        commit;--提交
    exception

    end;

例外:
    no_data_found    (没有找到数据)
    too_many_rows    (select ...into 语句匹配多个行)
    zero_divide    (被零除)
    value_error    (算术或转换错误)
    timeout_on_resource    (在等待资源是发生超时)

--查询部门编号是50的员工
    declare
        prec emp%rowtype;
        cursor c1 is select * from emp r where e.deypno = 50;
        no_data exception;--异常类型的定义
    begin
        open c1;
        loop
            fetch c1 into prec;
            if c1%notfound then
            raise no_data;--抛出异常
            end if;
        end loop;
        close c1;
    exception
        when no_data then--处理异常
            dbms_output.put_line('没有员工');
        when others then
            dbms_output.put_line('其他异常');
    end；

存储过程procedure
创建存储过程语法：
    create [or replace] procedure 存储过程名 [(参数名 in/out 数据类型)]
    as
    begin
            PLSQL子程序体;
    end;

    或者
    
    create [or replace] procedure 存储过程名 [(参数名 in/out 数据类型)]
    is
    begin
        PLSQL子程序体;
    end 存储过程名；

范例：
    create or replace procedure helloworld is
    begin
        dbms_output.put_line('helloworld');
    end helloworld;
    调用存储过程
    在PLSQL中调用存储过程
    begin 
        --Call the procedure
        hellloworld;
    end;

范例2：给指定的员工涨100工资，并打印出涨前和涨后的工资
    create or replace procedure addsal(pno in myemp.empno%type) as
        prec myemp%rowtype;--定义变量
    begin
        select * into prec from myemp t where t.empno = pno;
        update myemp t set t.sal = t.sal+100 where t.empno = pno;--事务的提交要在调用端做
        dbms_output.put_line('涨工资前是：' || prec.sal ||    '涨工资后是：'|| (prec.sal + 100));
    end;

范例3：计算指定员工的年薪 
    create or replace procedure countYsal(pno in emp.empno%type, ysal out number) is
        psal emp.sal%type;
        pcomm emp.comm%type;
    begin
        --多个值得赋值是按照先后顺序来赋值的
        select t.sal, t.comm into psal, pcomm  from emp t where t.empno = pno;
        ysal := psal*12 +  nvl(pcomm,0);
    end countYsal;

PLSQL程序调用存储过程
    --通过pl/SQL程序调用存储过程
    declare
        ysal number;
    begin
        countYsal(7369, ysal);
    end;

function 存储函数
语法：
    create or replace function 函数名（name in type，name in type，…）return 数据类型 is
        结果变量 数据类型；
    begin
        return（结果变量）；
    end 函数名；
示例：
    create or replace function countysalf(pno in emp.empno%type)return number is 
        Result number;
        psal emp.sal%type;
        pcomm emp.comm%type;
    begin
        select t.sal, t.comm into psal, pcomm from emp t where t.empno = pno;
        Result := psal * 12 + nvl(pcomm, 0);
        return(Result);
    end countysalf;
存储过程和存储函数的区别
    一般来讲，过程和函数的区别在于函数可以有一个返回值；而过程没有返回值。
    但是过程都可以通过 out 指定一个或多个输出参数。我们可以利用 out 参数，在过程中实现    返回多个值。【建议使用存储过程】

java代码调存储过程和存储函数

触发器trigger
    触发器可用于
        数据确认
        实施复杂的安全性检查
        做审计，跟踪表上所做的数据操作等
        数据的备份和同步
    出发器的类型
        语句级触发器：在指定的操作语句操作之前或之后执行一次，不管这条语句影响了多少行。
        行级触发器（for each row）：触发语句作用的每一条记录都被触发。在行级触发器中使用 old 和 new 伪记录变量，识别值得状态。

    语法：
    create [or replace] trigger 触发器名
        {before | after}
        {delete | insert | update [of 列名]}
        on 表名
        [for each row [when （条件）]]
    begin
        plsql 块
    end 触发器名；

    语句级触发器
    范例：插入员工后打印一句话“员工插入成功”
        create or replace trigger 触发器名
            beofre insert  on 表名
        begin
            dbms_output.put_line('一条记录被插入');
        end 触发器名；
        
        insert into person values（seqpersonid.nextval, '王五', 1, sysdate）;

    范例：不能再休息时间插入员工
        create or replace trigger 触发器名
            before insert on person
        declare
            cruday varchar2(10);--定义字符串
        begin 
            select to_char(sysdate, 'day') into cruday from dual;
            if cruday in '星期三' then
            raise_application_error(-20001, '星期三不允许插入数据')；--定义提示错误
            end if；
        end 触发器名；

        触发语句        :old            :new
        insert        所有字段都是空（null）    将要插入的数据
        update        更新以前该行的值        更新后的值
        delete        删除以前该行的值        所有字段都是空（null）

    行级触发器
    范例：判断员工涨工资之后的工资的值一定要大于涨工资之前的工资
        create or replace trigger 触发器名
            before update of sal on myemp
            for each row --行级触发器条件
        begin
            if :new .sal <= old.sal then
            raise_application_error(-20002, '涨后的工资不能比涨前的低')；
            end if；
        end 触发器名；
    
        update myemp t set t.sal = t.sal + 100 where t.empno = 7369;

数据库建模