Oracle05

2018-12-17  本文已影响0人  WJ_a4e6

s_emp s_dept

first_name  名                        id    部门编号       

salary    月薪                      name  部门名             

dept_id    部门编号              region_id 地区编号

1.按照部门编号分组 统计每个部门的平均工资  显示平均工资小于1400 的

  最终显示 部门编号  和 平均工资

2.按照部门编号分组 统计每个部门的人数  并且要显示部门名

最终显示  部门编号  人数    部门名 

3.建立一张表  叫 myorder表    有如下字段

  id    number    pk

  name  varchar2(30) 

  money  number

  odate  date 

  要求给主键约束 起名字 

4.给第三题建立的表  插入两条数据  数据如下

  1    test1  1000.85  当前时间

  2    test1  100.99    2017-1-18 18:56:35

  注意提交数据

5.写出sql中的五种约束  并写出对应的英文 

-------------------------------------------------------------------------

1.建立一张表  叫 myemp100 

  字段有 id  number  pk        fname  varchar2(30)  uk

  sname  char(30)  nn

  要求建表之前 先删除表    要求按照表名_字段名_约束简称 给约束起名字

  测试约束是否起作用。

  drop    table  myemp100;

  create  table  myemp100(

      id    number    constraint myemp100_id_pk  primary key,

      fname  varchar2(30) constraint myemp100_fname_uk  unique,

      sname  char(30)  constraint  myemp100_sname_nn  not null

  ); 

  insert  into  myemp100 values(1,'a',a);

2.检查约束的  列级约束实现

  drop    table  myemp100;

  然后建立这张表时 加一个  salary 的字段  要求这个字段的值 大于3500

  create  table  myemp100(

      id    number    constraint myemp100_id_pk  primary key,

      fname  varchar2(30) constraint myemp100_fname_uk  unique,

      sname  char(30)  constraint  myemp100_sname_nn  not null,

      salary  number  constraint  myemp100_salary_ck  check(salary > 3500)

  ); 

  insert into  myemp100  values(1,'cc','cc',3501);

  3.主键的表级约束实现

  这是给 id  通过 表级约束加了 主键约束

  create  table  table_cons(

      id    number,

      fname  varchar(30),

      sname  char(30), constraint  table_cons_id_pk  primary key(id)

  );

  表级约束最大的优势 在于 可以完成联合约束 

  create  table  table_cons2(

      id    number,

      fname  varchar(30),

      sname  char(30),constraint  table_cons_idfname_pk  primary key(id,fname)

  );

  4.建立一张表  叫 myemp200 

  字段有 id  number  pk        fname  varchar2(30)  uk

  sname  char(30)            salary  number    ck  要求工资大于 3500

  要求建表之前 先删除表    要求按照表名_字段名_约束简称 给约束起名字 并且这些约束都要使用

表级约束完成。

drop  table  myemp200;

/* 不支持not null 的表级约束实现  */

create table myemp200(

    id    number,

    fname  varchar2(30),

    sname  char(30) constraint  myemp200_sname_nn  not null,

    salary  number,constraint  myemp200_id_pk  primary key(id),

    constraint  myemp200_fname_uk unique(fname),

    constraint  myemp200_salary_ck check(salary>3500)

    );

联合约束的解释:

    id  fname

    1    null

    1    b

    1    b

  create  table  table_cons3(

      id    number,

      fname  varchar(30),

      sname  char(30),constraint  table_cons_idfname_uk  unique(id,fname)

  );         

5.外键约束

  5.1 概念

      外键约束涉及到两张表  一张父表(主表)  一张 子表(从表)

      定义了外键的表 叫 子表

      外键字段的取值  受限于 父表中被引用的字段的取值。

      具体的受限过程是  子表中外键字段的值  要么取NULL 要么 取父表中字段对应的值。

  5.2 具体的语法

  a.建表的顺序

      先建父表  后建子表  除非你先建表 后加外键

  create table  parent100(

      id  number  constraint  parent100_id_pk  primary key,

      name  varchar2(30)

  ); 

  create table  child100(

      id  number  constraint  child100_id_pk  primary key,

      name  varchar2(30),

      fid  number  constraint  child100_fid_fk references  parent100(id)

  );

  b.插入数据

      先插入父表数据  后插入子表数据  除非子表的外键值使用null值

  insert  into child100 values(1,'ca',1);

  ERROR at line 1:

ORA-02291: integrity constraint (SYSTEM.CHILD100_FID_FK) violated - parent key

not found

  insert  into child100 values(1,'ca',null);

  commit;

  insert  into parent100 values(1,'pa');

  insert  into parent100 values(2,'pb');

  commit;

  insert  into child100 values(2,'cb',1);

  insert  into child100 values(3,'cc',1);

  commit;

  c.删除数据?

      先删子表 后删父表  除非使用 级联

  d.删除表

      先删子表  后删父表否则违反外键规定 , 除非使用 cascade constraints

  drop table  parent100;

  ERROR at line 1:

  ORA-02449: unique/primary keys in table referenced by foreign keys

  cascade constraints  意思是 先解除主外键关系  然后删除表就不受外键限制

      这样的好处 可以打破删表的顺序。

  drop  table  parent100 cascade constraints;       

  drop  table  child100 cascade constraints;

  5.3 练习

  建立一张员工表  emp100              一张部门表  dept100

  id  number  pk                  id    number  pk

  name varchar2(30)                name  varchar2(30)

  salary  number                   

  dept_id  number  fk

  要求 给约束起名字  并且 建表之前先删除表

插入如下数据  部门表    1  test1      2    test2  提交数据

            员工表  1  ea  8000  1    2  eb  10000  1    3  ec  12000  1

            4  ed  25000 2    5    ee  32000 2  提交数据

  drop table  dept100 cascade constraints;         

  drop table  emp100 cascade constraints;

  create  table dept100(

      id  number  constraint dept100_id_pk  primary key,

      name varchar2(30)

  );

  insert into dept100 values(1,'test1');

  insert into dept100 values(2,'test2');

  commit;

  create table  emp100(

      id  number  constraint emp100_id_pk primary key,

      name  varchar2(30),

      salary  number,

      dept_id  number  constraint emp100_dept_id_fk references dept100(id)

  );

  insert into emp100 values(1,'ea',8000,1);

  insert into emp100 values(2,'eb',10000,1);

  insert into emp100 values(3,'ec',12000,1);

  insert into emp100 values(4,'ed',25000,2);

  insert into emp100 values(5,'ee',32000,2);

  commit;         

  5.4 级联

  on delete  cascade    级联删除

  on delete  set  null  级联置空

  上面关键字 加在外键上 

  drop table  dept100 cascade constraints;         

  drop table  emp100 cascade constraints;

  create  table dept100(

      id  number  constraint dept100_id_pk  primary key,

      name varchar2(30)

  );

  insert into dept100 values(1,'test1');

  insert into dept100 values(2,'test2');

  commit;

  create table  emp100(

      id  number  constraint emp100_id_pk primary key,

      name  varchar2(30),

      salary  number,

      dept_id  number  constraint emp100_dept_id_fk references dept100(id)

      on delete set null

  );

  insert into emp100 values(1,'ea',8000,1);

  insert into emp100 values(2,'eb',10000,1);

  insert into emp100 values(3,'ec',12000,1);

  insert into emp100 values(4,'ed',25000,2);

  insert into emp100 values(5,'ee',32000,2);

  commit;         

  5.5 外键的表级约束实现

  drop table  dept100 cascade constraints;         

  drop table  emp100 cascade constraints;

  create  table dept100(

      id  number  constraint dept100_id_pk  primary key,

      name varchar2(30)

  );

  create table  emp100(

      id  number  constraint emp100_id_pk primary key,

      name  varchar2(30),

      salary  number,

      dept_id  number,constraint emp100_dept_id_fk foreign key(dept_id)

      references dept100(id) on delete set null

  );

  5.6 先建表 后加外键

  drop table  dept100 cascade constraints;         

  drop table  emp100 cascade constraints;

  create  table dept100(

      id  number  constraint dept100_id_pk  primary key,

      name varchar2(30)

  );

  create table  emp100(

      id  number  constraint emp100_id_pk primary key,

      name  varchar2(30),

      salary  number,

      dept_id  number

  );

  alter table emp100  add constraint emp100_dept_id_fk foreign key(dept_id)

      references dept100(id); 

  alter table emp100  disable constraint emp100_dept_id_fk;

  6.数据库中的其它对象

    6.1 序列  sequence

    6.1.1 作用

        用来产生数据库表的主键值

    6.1.2 语法

    create sequence  序列名;

        在需要主键值的地方 使用  序列名.nextval

        偶尔使用  序列名.currval 

    6.1.3 举例

    create  Table  testseq(

        id  number  constraint  testseq_id_pk  primary key,

        name  varchar2(30)

    );

    create  sequence  testseq_id_seq;

    insert  into testseq values(testseq_id_seq.nextval,

      'test'||testseq_id_seq.currval);

    6.1.4  删除序列

    drop  sequence  序列名;

    drop  sequence  testseq_id_seq;

    6.1.5  复杂的序列

        可以设置 起始值  最大值  最小值  �i进  缓存 等待

    CREATE SEQUENCE s_customer_id

  MINVALUE 1

  MAXVALUE 9999999

  INCREMENT BY 1

  START WITH 216

  NOCACHE

  NOORDER

  NOCYCLE;

  6.2 索引  index    dba  (了解)

  6.2.1  作用

      为了加速查询的

  3亿        不使用索引        500s

                    使用索引            0.01s

  6.2.2  如何实现的

      底层通过树状结构  通过消耗大量的空间  和  时间 来达到加速查询的目的。

  6.2.3  语法

  create  index  索引名  on  表名(字段名);

      注意不要在唯一性字段上 建立索引  因为系统已经自动建立过了。                       

  set timing on;

  create index  s_emp_first_name_ind on s_emp(first_name);

  6.2.4  删除所有

  drop  index  索引名;

  6.3 视图  view  (了解)

  6.3.1  view 本质

  view 的本质就是一条sql,所以相对于它对应的数据而言  视图本身的空间可以忽略。

  6.3.2  语法

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

  create  or replace  view  myview  as  select id,first_name name,salary

      from  s_emp;

  6.3.3 作用

      可以对同一份物理数据  作出不同的表现  起到权限控制的作用

      它可以简化子查询   

  7.Oracle 中的 分页

      根据谁来取?  id?(可能不连续  没有规律)

    oracle  rownum  伪列 

    mysql  limit  m,n

    select  rownum,first_name,salary from  s_emp;

        一页显示 11 条  要第一页

    select  rownum,first_name,salary from  s_emp  where rownum < 12; 

        一页显示 11 条  要第二页    因为rownum  碰到 第一条不满足条件的数据就停止查找 

    导致这个查询 没有结果

    select  rownum,first_name,salary from  s_emp 

        where rownum < 23 and  rownum>11;

  select r,first_name,salary from (select  rownum r,first_name,salary from  s_emp 

        where rownum < 23) where r>11;

      按照工资排序  一页显示 11 条数据  显示 第二页数据 

      先排序  还是先编号?

  select first_name,salary from (

  select rownum r,first_name,salary from

  (select  first_name,salary from s_emp  order by salary)where rownum < 23

  )where r > 11;

  最内层:负责排序 

  中间层:负责编号  和 起别名 部分过滤

最外层:负责使用别名 过滤 

上一篇下一篇

猜你喜欢

热点阅读