oracle 简单记录

2008 05 05
Oracle
SQL: Structured Query Language（只关心做什么，不关心怎么做）第四代语言
PL／SQL（过程化的SQL，只适用于Oracle数据库）

PK:Primary Key,为了唯一的区分表这中的记录 主键（要求：非空、唯一）
FK：Foreign Key,为了建立多张表间的关联关系 外键（要求：外键所引用列中的值，可以为空）

Oracle9i：internet
Oracle10g:grid(网格化计算)

实际项目中不建议用＊号；
1)
2)
给列指定别名：
1）as关键字可加可不加
2）当别名有大小写区分时，用“”号
   
   
   
Nvl：用来使用指定的值来代替列中的空值   
注意：NVL两个参数的类型必须一致
distinct：去除重复项。

number(p,s)数据类型 P:percision精度
   s:scale刻度 表示小数点后的取值位数
varchar:变长字符串
char：定长字符串
varchar2类型 是Oracle公司自己的，不是国际标准

1)order by 后面可以跟：
列名、列的别名、select 列表中列的位置
2）order by 默认按列值的

rownum:"哑列"  限制：＝,>=号只能作用于1；>号无意义，<，<=号可以没有限制

select e.last_name,e.salary,d.department_name
from employees e,departments d
where e.department_id=d.department_id(+);
等价：      
select e.last_name,e.salary,d.department_name
from employees e
left outer join departments d
on e.department_id=d.department_id;

查出所有部门，包括部门没有员工的
select e.last_name,e.salary,d.department_name
from employees e,departments d
where e.department_id(+)=d.department_id;
等价：
select e.last_name,e.salary,d.department_name
from employees e
right outer join departments d
on e.department_id=d.department_id;

union 集合并
union all 集合相加
minus, 集合相减的
intersect 集合的交

select last_name,salary
  2  from s_emp
  3  where rownum<=20
  4  minus
  5  select last_name,salary
  6  from s_emp
  7  where rownum<=10;

自连接：
select worker.last_name,worker.salary,manager.last_name
from s_emp worker,s_emp manager
where worker.manager_id=manager.id;

第五章  组功能（函数）
select count(*)
from s_emp;

在select 列表中所有没有放到主函数中的列，必须要放到group by 语句中

在where 子句中不能出现组函数，如果要用组函数用 having
select last_name,salary,dept_id
from s_emp
where dept_id=(select dept_id
from s_emp
where last_name like 'Smith');
不能在子查询里有order by (因为那样无意义)
1.查平均工资大于32部门平均工资的部门id和部门平均工资
select dept_id,avg(salary)
from s_emp
having avg(salary)>(select avg(salary) from s_emp where dept_id=32)
group by dept_id;
2.找出和Smith在同一部门，并且相同职称的员工的信息
select last_name,salary,dept_id,title
from s_emp
where dept_id=(select dept_id from s_emp where last_name='Smith')
and title=(select title from s_emp where last_name='Smith');
或
select last_name,salary,dept_id,title
from s_emp
where (dept_id,title)=(select dept_id,title from s_emp where last_name='Smith');

show verify
verify 现示变量被替换的过程

select last_name,to_char(start_date,'fmdd "of" Month yyyy
fmhh24:mi:ss am') HIREDATE
from s_emp;

主键：
为什么要有主键？
唯一区分表中的记录
主键的要求：非空，唯一
一个表可建几个主键，主键能否为多列？
表中可以没有主键，但一个表只能建一个主键（能当主键的可以有多个），主键可以是多个列，称为联合主键。
主键的类型：
 1）自然主键
 自然主键有商业含义，有可能发生变化（耦合性太强（不好））
 2）逻辑主键（代理主键）
 没有商业含义，不需要变化（低耦合）

外键：
为了建立多张表之间的关联关系
外键的要求：所引用的值为列中的值或null
一个表可建几个外键，外键能否为多列？
可建多个外键，外键可以是多个列称为联合外键。
外键的类型:
 1）关联其他表的外键
 2）关联自身表的外键
char :定长字符
varchar：可变长度字符
varchar2：Oracle中定义的  暂时和varchar没区别
表级别约束：
create table nizh_test(
id number(7),
name varchar2(25) default user,
age number(3),
sdate date default sysdate，
constraint nizh_test__id_pk primary key(id)
);
列级别约束：
删表
drop table nizh_test;
create table nizh_test(
id number(7),
name varchar2(25),
age number(3),
sdate date default sysdate,
constraint nizh_test_name_u unique(name,age),
constraint nizh_test_id_pk primary key(id)
);

insert into nizh_test(id,name,age)
values(1,'briup',10);

not null 只能用在列级别约束上
unique 约束可以加在表级别上，也可以加在列级别上；使用unique约束的列会自动创建一个索引
primary key 主键约束 可以加在表级别上，也可以加在列级别上，使用primary key 约束

foreign key 外键约束 可以加在表级别上，也可以加在列级别上

check 约束 可以加在表级别上，也可以加在列级别上

drop table nizh_test2;
create table nizh_test2(
id1 number(7),
id2 number(7),
name varchar2(25),
constraint nizh_test2_id_pk primary key(id1,id2)
);

create table nizh_test(
id number(7) constraint nizh_test_id_pk primary key,
name varchar2(25),
age number(3),
sdate date default sysdate,
t_id number(7) constraint nizh_test_t_id_fk references nizh_test2(id)
);

表级别：
create table nizh_test(
id number(7) constraint nizh_test_id_pk primary key,
name varchar2(25),
age number(3),
sdate date default sysdate,
id1 number(7),
id2 number(7),
constraint nizh_test_t_id_fk foreign
key(id1,id2) references nizh_test2(id1,id2)
);

create table S_NIZH(
ID  number(7) constraint S_NIZH_ID_PK primary key,
NAME  varchar2(25) constraint S_NIZH_NAME_NN not null,
REGION_ID number(7) constraint S_NIZH_REGION_ID_FK references s_region(id),
constraint S_NIZH_NAME_REGION_ID_U2 unique(NAME,REGION_ID)
);

在已有表的基础上构建新的表，会拷贝表的结构（除了not null约束以外，其他约束都丢失），也会拷贝表中 的数据。

//只拷结构不拷数据
create table nizh_emp
as
select id,last_name,salary,dept_id
from s_emp
where 1=2;

 

insert into nizh_emp
values(1,'briup',2000,41);
drop table nizh_test;
create table nizh_test(
id  number(7) primary key,
name  varchar2(25) default user,
sdate  date default sysdate
);

insert into nizh_test
values(1,'briup1',sysdate);

insert into nizh_test(id,name)
values(2,'briup1');

insert into nizh_test
values(3,'briup3',null);

insert into nizh_test
values(4,'briup4',to_date('2008-08-08','yyyy-mm-dd'));

insert into nizh_test
values(5,'briup5',to_date('08-may-95','dd-mon-yy'));

insert into nizh_test
values(6,'briup6',to_date('08-may-95','dd-mon-rr'));

insert into nizh_test
values(&id,'&name',&sdate);

delete from nizh_test;

insert into nizh_test
select id,last_name,start_date
from s_emp
where dept_id=41;

update nizh_test
set name='briup'
where id=2;

delete from nizh_test
where id=6;

2008 05 08
 
create table nizh_test(
id  number(7) primary key,
name  varchar2(25)
);

insert into nizh_test
values(1,'briup1');

insert into nizh_test
values(2,'briup2');

insert into nizh_test
values(3,'briup3');

insert into nizh_test
values(4,'briup4');

savepoint p4;

insert into nizh_test
values(5,'briup5');
insert into nizh_test
values(6,'briup6');

drop table nizh_test;
create table nizh_test(
id  number(7) primary key,
name  varchar2(25)
);

//加列
alter table nizh_test
add( age  number(3) default 10,
 address varchar2(50) not null);
//删列
alter table nizh_test
drop column address;
//修改
alter table nizh_test
modify name varchar2(50) default 'briup' not null;

drop table nizh_emp;
create table nizh_emp
as
select id,last_name,salary
from s_emp
where 1=2;
//查约束
select constraint_name,constraint_type
from user_constraints
where lower(table_name)='nizh_emp';
//加约束
alter table nizh_emp
add constraint nizh_emp_id_pk primary key(id);
//删除约束
alter table nizh_emp
drop constraint nizh_emp_id_pk;
练习：
1)nizh_emp加一列 dept_id关联外键nizh_test(id)
2)加一列age，并加上非空约束
alter table nizh_emp
modofy age number(3) not null;
3）删除last_name上的非空约束
//查列上的约束 再删
select constraint_name,column_name
from user_cons_columns
where lower(table_name)='nizh_emp';

1.2解
alter table nizh_emp
add( dept_id number(7) constraint nizh_emp_dept_id_FK references nizh_test(id),
 age  number(3) constraint nizh_emp_age_nn not null);
3解：
alter table nizh_emp
drop constraint SYS_C0093304;
//使age的非空约束失效
alter table nizh_emp
disable constraint nizh_emp_age_nn;
//使age的非空约束有效
alter table nizh_emp
enable constraint nizh_emp_age_nn;

create nizh_test1(
id  number(6) primary key,
name  varchar2(25),
tid  number(7) feferences nizh_test2(id)
);

create table nizh_test2(
id  number(6) primary key,
name  varchar2(25),
);

drop table nizh_test2;//删不掉(因为有关联表）
drop table nizh_test2 cascade constraints;//连同关联表的关联一起删除
//重命名
rename nizh_test1 to nizh_newtest;

truncate与delete 的区别？
truncate是ddl自动提交会释放空间
delete是dml不会自动提交 不会释放空间

给表加注释：
create table nizh_testx(
id  number(7) primary key,
name  varchar2(25)
);
给表加注释
comment on table nizh_testx
is 'this is comment test table';
查表的注释
select comments
from user_tab_comments
where lower(table_name)='nizh_test';
给列加注释
comment on column nizh_test.id
is 'this is primary key';
查列的注释
select column_name,comments
from user_col_comments
where

create sequence nizh_seq
increment by 2
start with 100
maxvalue 1000
minvalue 100
nocycle
cache 10;

insert into nizh_test
values(nizh_seq.nextval,'briup',12);
//改sequence
alter sequence nizh_seq
increment by 1
maxvalue 10000
minvalue 200
cycle
cache 20;
注意：修改sequence 事，minvalue要<=currval
//删除sequence
drop sequence nizh_seq;

14章 View

create table nizh_view
as
select id,last_name,salary,dept_id
from s_emp
where id=41;

create or replace view nizh_viewtest(id,myname,dept_id)
as
select id,last_name name,dept_id
from nizh_emp;

create or replace view nizh_viewtest(id,myname,dept_id)//给出视图名
as
select id,last_name,dept_id
from nizh_emp;

create view nizh_deptview(name,minsalary,maxsalary,avgsalary)
as
select d.name,min(e.salary),max(e.salary),avg(e.salary)
from s_emp e,s_dept d
where e.dept_id=d.id
group by d.name;

update nizh_viewtest
set last_name='briup'
where id=2;

create or replace view nizh_viewtest
as
select id,last_name name,dept_id
from nizh_emp
with read only;

数据字典表：user_views；

15 创建索引：

create index nizh_index
on nizh_deptview(name);

使用索引：
1）表中的数据量比较大
2）列经常作为查询条件，即出现在where字句中
3）数据量不超过总数据的％2－％4；

不使用索引：索引会延缓修改操作，所以经常修改的表也不建议使用索引。
select index_name,UNIQUENESS
from user_indexes
where lower(table_name)='nizh_test'

select index_name,column_name
from user_ind_columns
where lower(table_name)='nizh_test';

练习：