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;
最内层:负责排序
中间层:负责编号 和 起别名 部分过滤
最外层:负责使用别名 过滤
