数据库讲义5

SCHEMA: is a collection of database objects owned by a special user.
schema 与 用户同名

select * from schema.table;


1、表
分类
堆表：插入数据快，无序
索引表
临时表
簇表
分区表

1.1 数据类型
数值：
number(p,s):p是精度，p最大38位（有效数字个数），s小数点位数
number(10,2)
number(5)

字符:
char(n byte|char):最长2000字节
varchar2(n byte|char):最长4000字节
char定长,占固定长度存储空间
varchar2变长，根据数据长度分配空间

drop table t01;
create table t01(n char(2000char));
insert into t01 values(lpad('a',2000,'b'));
insert into t01 values(lpad('你',4000,'好'));
select * from t01;
select length(n),vsize(n)　from t01;

日期：
date，保存年月日时分秒
timestamp，保存年月日时分秒1~9小数秒

大对象类型：
blob: 而进制大对象，存电影、mp3、图片、word文档，最大128T
clob：字符型大对象，小说，最大128T

1.2 表维护语法
创建：
create table 表名(
列名 数据类型 [default 默认值]
[,....]
);
create table student(
stu_no varchar2(10),
stu_name varchar2(100),
stu_age number(2),
stu_gender varchar2(2) default '男',
stu_iden varchar2(18),
stu_birthday date
);

select * from user_tables;
select * from user_tab_columns where table_name='STUDENT';

--闪回技术，闪回删除，删表时没有直接删除，只是改名放在回收站
drop table t01;
select * from user_recyclebin;
flashback table t01 to before drop;
select * from t01;
--清空回收站
purge recyclebin;

--彻底删除
drop table t01 purge;
修改
增加列
alter table 表名 add (列名 类型 [default 默认值][,...]);
alter table student add (address varchar2(100));
修改列（改长度，改类型，改名）
alter table 表名 modify (列名 类型 [default 默认值][,...]);
alter table student modify(address varchar2(500));
alter table student modify(address number);
alter table student rename column address to postcode;
删除列
alter table 表名 drop column 列名;
alter table 表名 drop (列名1[,...]) ;
alter table student drop (postcode);
删除
drop table 表名 [cascade constraint]
cascade constraint: 当表主键或唯一约束被其他表的外键引用时，可级联删除约束，
否则表不能删除

create table t02(id number primary key,name varchar2(100));
create table t03(n number references t02(id));
drop table t02 cascade constraint;

改名
rename 表名 to 新表名;
rename t03 to xxx;
select * from user_tables;
drop table xxx cascade constraint;

1.3

约束
约束：防止无效数据进行数据库
nosql，大数据,hadoop
分类：
主键：primary key,唯一标识一行数据
外键：foreign key,引用其他表的主键或唯一约束列的数据
唯一：unique，唯一标识一行数据
非空：not null,不能空
检查：check，根据条件验证数据

表级约束、列级约束：
列级约束：指在列定义时同时定义约束
表级约束：指所有列定义后，在列定义下方单独定义约束

约束命名：
表名_列名_约束类型缩写

student_id_pk
student_classno_fk
student_name_nn
student_age_ck
student_iden_uni

主键：
--列级约束
create table student(
stu_no varchar2(20) [constraint student_stu_no_pk] primary key,
stu_name varchar2(100)
);

--表级约束
create table student(
stu_no varchar2(20) ,
stu_name varchar2(100),
[constraint student_stu_no_pk] primary key(id)
);
create table student(
stu_no varchar2(20) ,
stu_name varchar2(100),
[constraint student_stu_no_pk] primary key(id,name)
);

--修改表加主键约束
alter table student add [constraint student_stu_no_pk] primary key(id,name);


drop table student purge;
create table student(
stu_no varchar2(20) constraint student_stu_no_pk primary key,
stu_name varchar2(100)
);

select * from user_constraints;
select * from user_cons_columns where table_name='STUDENT';
唯一：
drop table student purge;

create table student(
stu_no varchar2(20),
stu_name varchar2(100),
stu_iden varchar2(18) unique
);
create table student(
stu_no varchar2(20),
stu_name varchar2(100),
stu_iden varchar2(18),
unique(stu_iden)
);


alter table student add unique(stu_iden);

非空：
drop table student purge;

create table student(
stu_no varchar2(20),
stu_name varchar2(100) not null,
stu_iden varchar2(18)
);

alter table student modify (stu_name varchar2(100) not null);

检查：
drop table student purge;

create table student(
stu_no varchar2(20),
stu_name varchar2(100),
stu_iden varchar2(18),
stu_age number(2) check(stu_age between 20 and 60),
stu_gender varchar2(20) check (stu_gender in ('男','女'))
);


create table student(
stu_no varchar2(20),
stu_name varchar2(100),
stu_iden varchar2(18),
stu_age number(2),
stu_gender varchar2(20)，
check(stu_age between 20 and 60)，
check (stu_gender in ('男','女'))
);


alter table student add check (stu_gender in ('男','女'));

外键:
create table tblclass(
clano varchar2(10) primary key,
claname varchar2(10)
);

drop table student purge;
create table student (
stuname varchar2(20),
clano varchar2(10) references tblclass(clano)
);

create table student (
stuname varch

ar2(20),
clano varchar2(10),
foreign key(clano) references tblclass(clano)
);

alter table student add foreign key(clano) references tblclass(clano);



select * from tblclass;
insert into tblclass values(1,'c1');
select * from student;
insert into student values('zs',1);

delete from tblclass;

on delete cascade: 删主表数据同时，从表数据同时删除
on delete set null：删主表数据同时，从表外键列值置空

select * from user_constraints where table_name='STUDENT';
alter table student drop constraint SYS_C006644;
alter table student add foreign key(clano) references tblclass(clano)
on delete cascade;
delete from tblclass;


alter table student drop constraint SYS_C006645;
alter table student add foreign key(clano) references tblclass(clano)
on delete set null;

约束维护
增加约束
删除约束
alter table student drop constraint 约束名;
禁用和启用约束
alter table student modify constraint 约束名 disable|enable;
alter table student modify constraint SYS_C006646 disable;
alter table student modify constraint SYS_C006646 enable;
改名
alter table student rename constraint 旧名 to 新名;
alter table student rename constraint SYS_C006646 to student_clano_fk;

建表练习
1）按照下面表格信息写出创建表的语句
部门表 tbldept
------------------------------------------
字段 类型 说明
deptid number(5) 部门编号，主键
deptname varchar2(30) 部门名称

员工表 tblemp
------------------------------------------
字段 类型 说明
empid number(5) 员工编号，主键
empname varchar2(10) 员工名称

create table tbldept (deptid number(5) primary key,
deptname varchar2(30));
create table tblemp(
empid number(5) primary key,
empname varchar2(10));


2）按照下面要求分别写出修改表结构的语句
修改表tblemp，增加列phone，类型varchar2(20)
修改表tblemp，将列empname类型修改为varchar2(20)
修改表tblemp，删除列phone

alter table tblemp add (phone varchar2(20));
alter table tblemp modify(empname varchar2(20));
alter table tblemp drop (phone);
3）按照下面要求分别写出修改表名的语句
将表tbldept修改为 dept1
将表tblemp修改为 emp1
select * from user_constraints;

rename tbldept to dept1;
alter table tblemp rename to emp1;

4) 修改表emp1增加列deptid，外键，引用dept1表 的 deptid列。
alter table emp1 add (deptid number(5) references dept1(deptid));

5）修改表dept1，增加部门所在房间号roomno，唯一约束。
alter table dept1 add(roomno number unique);

6）修改表emp1，删除在deptid上的外键约束，重建增加外键约束
引用dept1表的deptid列并指定级联删除（on delete cascade）。
select * from user_constraints where table_name='EMP1';
alter table emp1 drop constrai

nt SYS_C006660;
alter table emp1 add foreign key(deptid) references dept1(deptid)
on delete cascade;
7）按照下面要求写出创建工资表salary的语句。
员工编号，8位长，主键
姓名，非空
年龄，要求介于20-60之间，
入职时间
工资
性别，只能 是男或女
手机号，唯一
部门编号，外键，引用dept1的deptid列

drop table salary;
create table salary(
empno number(8) primary key,
empname varchar2(100) not null,
empage number(2) check(empage between 20 and 60),
hiredate date,
sal number(10,2),
gender varchar2(2) check (gender ='男' or gender='女'),
phonenum varchar2(11) unique,
deptid number(5) references dept1(deptid)
);

primary key和Not Null：不能空
unique：可空
check：可空
foreign key: 可空

1.4 索引表
create table 表名(列定义) organization index;
表的数据相对静态，例如编码表

查询快，更新速度慢、成本高

1.5 临时表
表：数据变化必须记录日志
临时表：不记日志

可做中间统计信息的结果存储

create global temporary table 表名(列定义);

1.6 簇

cluster: 一堆，按特定列对数据进行分组

create cluster dept_emp(deptid number);
create index dept_emp_index on cluster dept_emp;

create table dept1(did number,dname varchar2(20)) cluster dept_emp(did);
create table emp1(eid number,ename varchar2(20),did number) cluster dept_emp(did);

select * from user_clusters;

1.7 分区表

100万
1000万以上算大表

分区：将大表拆成多个小表

分类：
范围分区
列表分区
哈希分区

范围-列表
范围-哈希

范围分区：

create table people(
iden varchar2(18) primary key,
name varchar2(20),
age number,
province varchar2(20),
gender varchar2(20),
birthday date
)
partition by range(age)
(
partition p1 values less than (21),
partition p2 values less than (41),
partition p3 values less than (61),
partition p4 values less than (81),
partition p5 values less than (maxvalue)
)

insert into people values('111','a',15,'辽宁','男',to_date('1999-10-01','yyyy-mm-dd'));
insert into people values('112','b',35,'辽宁','男',to_date('1979-10-01','yyyy-mm-dd'));

select * from people ;
select * from people partition (p4);


列表分区:

create table people1(
iden varchar2(18) primary key,
name varchar2(20),
age number,
province varchar2(20),
gender varchar2(20),
birthday date
)
partition by list(province)
(
partition p1 values ('辽宁'),
partition p2 values ('吉林'),
partition p3 values ('黑龙江'),
partition p4 values (default)
)


select * from user_tables;
select * from user_tab_partitions;


哈希分区:

create table people2(
iden varchar2(18) primary key,
name varchar2(20),
age number,
province varch

ar2(20),
gender varchar2(20),
birthday date
)
partition by hash(name)
(
partition p1,
partition p2,
partition p3,
partition p4
)

组合分区
范围-列表
create table people3(
iden varchar2(18) primary key,
name varchar2(20),
age number,
province varchar2(20),
gender varchar2(20),
birthday date
)
partition by range(age)
subpartition by list(province)
(
partition p1 values less than(21)
(
subpartition p1_1 values('辽宁'),
subpartition p1_2 values(default)
),

partition p2 values less than(maxvalue)
(
subpartition p2_1 values('辽宁'),
subpartition p2_2 values(default)
)
)

范围-哈希
create table people4(
iden varchar2(18) primary key,
name varchar2(20),
age number,
province varchar2(20),
gender varchar2(20),
birthday date
)
partition by range(age)
subpartition by hash(province)
(
partition p1 values less than(21)
(
subpartition p1_1,
subpartition p1_2
),

partition p2 values less than(maxvalue)
(
subpartition p2_1,
subpartition p2_2
)
)


sqlplus system/oracle
grant create view,create synonym,create database link to scott;
2、视图
视图是一个定义好的查询语句。

create view 视图名[(列名1，......)]
as 子查询
[with check option| with read only];

with read only: 视图只读
with check option: 视图上的数据操作必须符合创建视图时指定的条件

select * from user_tables;

create table sal(
id number,
name varchar2(20),
sal number);

--基于单表且包含所有主键和非空列的视图，可以做数据修改
create view v1 as select * from sal;
insert into v1 values(1,'a',5000);
select * from sal;

create view v4(empno,empname,salary) as select * from sal;
select * from v4;
--只读
create view v2 as select * from sal with read Only;

--带检查约束
create view v3 as select * from sal where sal>3000
with check option;

select * from v3;
insert into sal values(2,'b',2000);

insert into v3 values(3,'c',8000);
select * from sal;
insert into v3 values(4,'d',2500); X
update v3 set sal=5000 where sal=2000;X
update v3 set sal=2000 where sal=8000;X

select * from sal;
3、序列
自增数字,一般用作表的主键值。

create sequence 序列名
start with 起始值(默认1)
increment by 递增值(默认1)
minvalue 最小值(默认1)
maxvalue 最大值(默认10^26)
cycle|nocycle(是否循环使用)
order|noorder(按顺序)
cache n(预分配)|nocache

create sequence seq1 start with 10 increment by 5;

伪列：
currval: 取当前会话最后取到的序列值,必须在nextval使用后才可用
nextval: 取下一个值

select seq1.nextval from dual;
select seq1.currval from dual;

insert into sal values(seq1.nextval,'b',2000);
select * from sal;

4、同义词

别名
create synonym s1 for sal;
select * from s1;

5、数据库链接
分布式事务需要借助数据库链接。
create database link link1
connect to scott
identified by tiger
using 'ora72';

select * from sal;
select * from t01@link1;

create synonym t01 for t01@link1;
select * from t01;

6、索引
索引：冗余数据结构，为了提升查询效率。

主键、唯一约束自动创建索引。
create index 索引名 on 表(列1[,...]);

多列索引：
选择率高（挑出来的数据最少） ,......

索引建在哪些列：
主键
唯一
查询条件出现频率高（谓词）
外键




PL/SQL 对象
存储过程
函数
包
触发器