oracle部门平均工资视图,Oracle05

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;

最内层:负责排序

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

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