Oracle day06 火推

 Oracle 06



约束

回顾列级约束

在定义表的某一列时, 在列的后面追加约束的关键字, 叫做列级约束 !

创建一个表格 

    person
    id 主键
    name
    age

drop table person;
create table person(
    id numnber constraint person_id_pk primary key,
    name varchar2(100),
    age number
);
表级约束

在定义完毕表的所有的列之后,  再选择某些列进行约束的添加, 叫做表级约束 !

not null 非空约束, 只能使用列级约束 !

格式: 

    create table 表名(字段名1 字段类型,字段名2 字段类型...字段名n 字段类型 ,constraint 别名 约束关键字(约束字段));
表级约束 主键

drop table person;
create table person(
    id number,
    name varchar2(100),
    age number,
    constraint person_id_pk primary key(id)
);
表级约束-- 主键+唯一+检查

需求: 

    创建一个person表 , id为主键, name唯一, age必须大于18岁

drop table person;
create table person(
    id number,
    name varchar2(100),
    age number,
    constraint person_id_pk primary key(id),
    constraint person_name_uk unique(name),
    constraint person_age_ck check(age>=18)
);
联合约束

表级约束与列级约束最大的区别就是: 表级约束允许编写联合约束  !

什么是联合约束: 

    一个约束的约束规则, 应用到了两个字段上

drop table person;
create table person(
    id number,
    name varchar2(100),
    age number,
    constraint person_id_name_pk primary key(id,name)
);

insert into person values(10001,'a',18);
insert into person values(10001,'b',18);
commit;

上述表格的主键规则如下: 

    1.  id可以重复 , 不可为null
    2.  name可以重复 , 不可为null
    3.  id或name 不可同时重复 !
外键约束(完整性约束)

之前所学习的所有的约束, 都是针对一个表格 !

概念: 

    1.  外键约束 , 牵扯到了两张表
    2.  一张叫做主表(父表) ,一张叫做从表(子表) , 定义了外键约束的表格属于从表!
    3.  从表中的外键字段, 是在参考主表中的某一个字段 , 主表中的被参考字段必须是主键或者unique约束字段
    4.  从表外键的取值范围, 受限于主表的被参考字段值! 
    5.  除了被参考的字段值, 还可以给外键字段 传入null值

作用: 

    外键约束  保证了参数的完整性 , 限定了一个列的取值范围 !
外键如何去实现

格式: 
create table 从表表名(
    字段名 字段类型 references 参考表格(参考字段名)
);


-建立外键表格

1.  创建主表(用户)
    drop table user13;
    create table user13(
        id number constraint user13_id_pk primary key,
        uname varchar(30) constraint user13_uname_nn not null,
        upass varchar(30) constraint user13_upass_nn not null
    );

2.  创建从表(订单)
    drop table order13;
    create table order13(
        id number constraint order13_id_pk primary key,
        name varchar2(100),
        info varchar2(100),
        userid number references user13(id)
    );

3.  向主表插入数据(用户注册)
    insert into user13 values(10001,'kaikai','aizhiqiang'); 
3.  向从表插入数据

    (进行错误操作, 尝试插入不存在的参考数据)  
    insert into order13 values(10000001,'预约了三个月的肉','订单中的三个月的肉, 包含了早中晚深夜 不同套餐',10002);
    (正确的参考操作)
    insert into order13 values(10000001,'预约了三个月的肉','订单中的三个月的肉, 包含了早中晚深夜 不同套餐',10001);
删除外键关联的数据

1.  尝试删除id为10002的用户

    delete from user13 where id=10002;
    commit;

    我们在删除id为10002的用户时, 出现了错误, 提示发现子数据 !

    要删除被关联的字段数据,  必须先删除从表中引用的数据! 或者 使用级联操作 !


2.  尝试删除id为10002的用户 , 先删除他的订单信息
    delete from order13 where userid=10002;
    delete from user13 where id=10002;
    commit;
删除外键关联的表格

1.  尝试删除user13表格
    drop table user13;
    必须先把从表删除, 才可以删除主表 !

2.  尝试删除user13表格
    drop table order13;
    drop table user13;
删除主表时, 直接取消外键关联关系

1.  创建主表(用户)
    drop table user13;
    create table user13(
        id number constraint user13_id_pk primary key,
        uname varchar(30) constraint user13_uname_nn not null,
        upass varchar(30) constraint user13_upass_nn not null
    );

2.  创建从表(订单)
    drop table order13;
    create table order13(
        id number constraint order13_id_pk primary key,
        name varchar2(100),
        info varchar2(100),
        userid number constraint order13_userid_fk references user13(id)
    );

3.  删除主表时, 直接取消外键关联关系

    格式: drop table 主表名 cascade constraints;

    drop table user13 cascade constraints;
级联操作

两种不同操作: 

on delete cascade : 级联删除 : 当主表数据删除时, 从表中参考的所有数据删除

on delete set null: 级联置空 : 当主表数据删除时, 从表中所有的参考数据 设置为null


语法格式:  

    声明外键之后. 加入 级联关键字
创建表格加入级联操作 --级联置空

以后在删除表格时, 建议添加 cascade constraints; 
drop table user13 cascade constraints;
drop table order13 cascade constraints;

1.  创建主表(用户)
    drop table user13;
    create table user13(
        id number constraint user13_id_pk primary key,
        uname varchar(30) constraint user13_uname_nn not null,
        upass varchar(30) constraint user13_upass_nn not null
    );

2.  创建从表(订单)
    drop table order13;
    create table order13(
        id number constraint order13_id_pk primary key,
        name varchar2(100),
        info varchar2(100),
        userid number constraint order13_userid_fk references user13(id) on delete set null
    );

    我们指定了 级联操作后 , 在删除主表中的被参考数据时, 不会再报错, 而是直接执行级联置空/级联删除

    insert into user13 values(10001,'kaikai','aishengqi');
    insert into user13 values(10002,'kaikai2','aisajiao');
    commit;

    insert into order13 values(1,'买了一盒12个气球','能撑3斤水',10002);
    insert into order13 values(2,'促销10盒半价气球','能撑3斤水',10002);
    commit;

    删除id为10002的用户
    delete from user13 where id=10002;
    commit;
    select * from order13;
创建表格加入级联操作 --级联删除

以后在删除表格时, 建议添加 cascade constraints; 
drop table user13 cascade constraints;
drop table order13 cascade constraints;

1.  创建主表(用户)
    drop table user13;
    create table user13(
        id number constraint user13_id_pk primary key,
        uname varchar(30) constraint user13_uname_nn not null,
        upass varchar(30) constraint user13_upass_nn not null
    );

2.  创建从表(订单)
    drop table order13;
    create table order13(
        id number constraint order13_id_pk primary key,
        name varchar2(100),
        info varchar2(100),
        userid number constraint order13_userid_fk references user13(id) on delete cascade
    );

    我们指定了 级联操作后 , 在删除主表中的被参考数据时, 不会再报错, 而是直接执行级联置空/级联删除

    insert into user13 values(10001,'kaikai','aishengqi');
    insert into user13 values(10002,'kaikai2','aisajiao');
    commit;

    insert into order13 values(1,'买了一盒12个气球','能撑3斤水',10002);
    insert into order13 values(2,'促销10盒半价气球','能撑3斤水',10002);
    commit;

    删除id为10002的用户
    delete from user13 where id=10002;
    commit;
    select * from order13;
外键的表级约束

格式: 

create table 表名(列...,
    constraint 别名 foreign key(本表中的外键字段) references 引用的主表表名(主表中的字段) on delete cascade;
);


drop table user13 cascade constraints;
drop table order13 cascade constraints;

1.  创建主表(用户)
    drop table user13;
    create table user13(
        id number constraint user13_id_pk primary key,
        uname varchar(30) constraint user13_uname_nn not null,
        upass varchar(30) constraint user13_upass_nn not null
    );

2.  创建从表(订单)
    drop table order13;
    create table order13(
        id number constraint order13_id_pk primary key,
        name varchar2(100),
        info varchar2(100),
        userid number,
        constraint order13_userid_fk foreign key(userid) references user13(id) on delete set null
    );

    我们指定了 级联操作后 , 在删除主表中的被参考数据时, 不会再报错, 而是直接执行级联置空/级联删除

    insert into user13 values(10001,'kaikai','aishengqi');
    insert into user13 values(10002,'kaikai2','aisajiao');
    commit;

    insert into order13 values(1,'买了一盒12个气球','能撑3斤水',10002);
    insert into order13 values(2,'促销10盒半价气球','能撑3斤水',10002);
    commit;

    删除id为10002的用户
    delete from user13 where id=10002;
    commit;
    select * from order13;
先建立表格 后 添加约束 (针对所有约束)

表格中存在约束时, 插入数据操作的性能会变低  ~

有时我们存在一些需求, 需要在表格创建完毕后, 给其添加一些合理的初始化数据 , 
我们保证了这些数据是不违反表格约束的, 
那么为了提高性能, 我们在创建表格以及初始化数据时, 不给表格添加约束条件 .

当表格初始化完毕后, 我们再修改表结构, 给其添加约束 !

格式: alter table 表名 add constraint 别名 约束关键字(字段名);

1.  先创建表格 
    drop table person cascade constraints;
    create table person(
        id number,
        name varchar(20)
    );
    insert into person values(10001,'sb');
    insert into person values(10001,'db');
    insert into person values(10002,'db');
2.  修改表结构, 添加约束
    delete from person where id=10001;
    commit;
    alter table person add constraint person_id_pk primary key(id);

3.  删除约束

    alter table 表名 drop constraint 约束名;
数据库中的其它对象

这些对象 创建与销毁时, 使用的方式与表格基本一致 !
序列

序列 就是在数据库中存在的一个自增长的数据
mysql 主键自增长
序列的值时从1开始的
语法:

1.  创建序列: create sequence 名称;(就像我们在Java中创建一个变量)
    序列起名的规范: 表名_字段名_index  |  表名_字段名_seq

2.  使用序列: 名称.nextval

3.  删除序列 drop sequence 名称;

案例: 

    1.  创建一个表格

    create table person(
        id number constraint person_id_pk primary key,
        name varchar2(100),
        age number
    );
    2.  创建一个序列: 
    create sequence person_id_index;
    3.  插入数据
    insert into person values(person_id_index.nextval,'a',18);
    insert into person values(person_id_index.nextval,'a',18);
    commit;
索引

一个表格如果存在主键, 那么就已经存在了索引 ,系统在主键创建时, 就为其添加了索引 !

作用: 

    索引是通过消耗大量的时间 和 空间  来达到加速查询的目的!

如果对于一个已经存在很多数据的表格建立索引的话 , 会花费大量的时间!

语法: 

    创建: create index 索引名 on 表名(字段名);
    删除: drop index 索引名;

案例:
create index person_name_index on person(name);
drop index person_name_index;
视图

概念: 

    视图就是一条SELECT语句 ! 

    相对于select语句产生的数据结果而言, 视图本身的空间是可以忽略的;


创建一个视图: 

    create or replace view 视图名称 as select语句

案例: 

    根据员工表和部门表, 生成一个视图

    select e.id,last_name,salary,d.name from s_emp e,s_dept d where e.dept_id=d.id;

    create or replace view s_emp_dept as select e.id,last_name,salary,d.name from s_emp e,s_dept d where e.dept_id=d.id;

    查询上面创建的视图: 

    select salary from s_emp_dept;


    视图的作用: 

    可以对同一份数据 做出不同的表现
    可以用来简化子查询

    可以用来控制查询权限

    删除视图: 

    drop view 视图名;
分页查询

分页查询的常见公式: 

    开始范围:大于     (当前页数-1)*一页准备显示的数据

    结束范围:小于等于   当前页数*一页准备显示的数据

查询员工表格, 要求分页显示 一页显示10个

查询第一页:
select id from s_emp where id>0 and id<=10;

查询第二页

select id from s_emp where id>10 and id<=20;

查询第三页

select id from s_emp where id>20 and id<=30;
id的序列可能因为数据的删除 而出现断层, 我们不能通过id进行分页查询

伪列 : 在表中不存在, 但是可以使用的列 !

rownum: 结果集中当前行数据的 行号

查询行号: 

select rownum, id from s_emp;


查询第一页:
select id from s_emp where rownum>0 and rownum<=10;

查询第二页

select id from s_emp where rownum>10 and rownum<=20;
上述代码出现了BUG , 原因: 

rownum 特性: 在参与where语句时 是逐行判断, 它存在一个特点, 当某一行不匹配时 流程结束 !


如何解决这个特性带来的bug: 

    通过子查询 / 视图

我们把rownum转换为了子查询的 子语句的一个普通的列


子查询: 

    select rownum r,id from s_emp where rownum<=20;

    select rownum,r ,id from(select rownum r,id from s_emp where rownum<=20) where r>10;
排序 分页

三层嵌套子查询 

1.  最内层的查询 : 查询所有的数据, 并排序

2.  中间层的查询 : 负责 给 行号起别名, 以及部分数据的过滤 

3.  最外层的查询 : 使用中间层行号的别名进行数据的过滤


根据工资进行排序分页(一页显示10个) : 查询是s_emp表格

最内层:select id,salary from s_emp order by salary;

中间层:select id,salary,rounum r from () where rownum<=20;

最外层:select id,salary from() where r>10;

最终的语句: 

    select id,salary from(select id,salary,rounum r from (select id,salary from s_emp order by salary) where rownum<=20) where r>10;
设计数据库表格的范式

第一范式: 数据库中的表字段, 不可再分

第二范式: 要求数据库中的表在设计时 ,表中的每一行数据, 必须可以被唯一区分 (设计表时,  必须存在主键)

第三范式: 在满足第二范式的基础上, 消除依赖传递 !

    在创建外键时 , 外键参考的字段尽量不依赖其他字段, 而只依赖主键 !






练习: 

    根据部门编号进行排序分页: 查询s_emp表格