Oracle管理表

insert 语句语法
insert into users
select 2,'xx',3 from dual
union select ..
insert into users values(2,'xx',default,6);
default不能参加任何运算.

create table user1
as select user_id,user_name
from users
where user_id>2;

create table user1
as select user_id,user_name
from users;

insert into (select user_id,user_name from users) values(7,'xxx');

insert into <select user_id,user_name from users where user_id=1) values(8,'xx');
对当前插入行约束
insert into (select user_id,user_name from users where user_id>5 with check option) values(6,'xx');
更新多行
update (select user_id,user_name from users where user_id>5) set user_name='xx';
更新以后满足什么条件
update (select user_id,user_name,room_id from users where room_id=3 with check option) set user_name='xx',room_id=4;

delete [from] table....

truncate table user;

set timing on

merge into copy_emp c
using employees e
on(c.employee_id=e.employee_id)
when matched then
update set .....
when not matched then
insert values ....
------------------
第五章  创建和管理表
set timing off;

create table users(
user_id integer,
user_name varchar2(30),
reg_date date default sysdate,
room_id integer);

create table room
(room_id integer,
room_type number(1,0),
room_name varchar2(30));

create table user1
as select user_id id,user_name name from users);

create table user1
as select user_id id,user_name name from users;
--------------------------
修改表
alter table xx {add|drop|modify}

alter table users add age integer default 0;

alter table users modify user_name varchar2(50);

alter table users modify user_name varchar2(5);

alter table users drop age; // error
alter table users drop column age;

create table user2 (
userid integer)
tablespace users;
删除多列
alter table table set unused (column);
alter table table drop unused columns;
删表
drop table table;
===============
第六章  内置约束
not null
check
unique
primary key
foreign key

create table users
(user_id int primary key,
user_name varchar2(30),
reg_date date default sysdate,
room_id int,
age int not null);

select constraint_name,constraint_type,search_condition from user_constraints where table_name='USERS;

desc user_cons_columns;
select constraint_name,table_name,colum_name from user_cons_columns;

create table room(
room_id integer constraint room_pk primary key,
room_type number(1,0),
constraint type_notnull unique (room_type));

alter table users add constraint user_room_fk foreign key(room_id) references room(room_id);

desc user_cons_columns;

select constraint_name,column_name,position from user_cons_columns where table_name in('xx','xx');

alter table users drop column room_id;

alter table add room_id constraint user_room_fk references room;

alter table users modify user_name not null;

alter table users modify user_name constraint name_notnull not null;

check:
伪列,系统函数,不能用
alter table room add constraint room_type check(room_type in(1,2,3));
--------------------
删除约束:

on delete cascade
on delete set null

select constraint_name from user_constraints where table_name='USERS';

alter table users drop constraint USER_ROOM_FK;

alter table users add constraint user_room_fk foreign key(room_id) references room on deletecascade;

alter table room drop constraint ROOM_PK cascade

===============================================

数据库对象:
table view sequence index synonym
create sequence sequence ...

索引:
desc user_indexes;
select index_name,index_type from user_indexes where table_name='xxx';

---------------------
rdbms/admin/utlxplan.sql
sqlplus/admin/plustrace.sql

@d:/utlxplan.sql
@d:/plustrce.sql
grant plustrace to hr;
set autotrace on;
set autotrace traceonly;
set autotrace off;
--------------------
create index room_index on users(room_id);

视图
create view user_room as
select user_id,user_name,age,room_type
from users,room
where users.room_id=room.room_id;

select * from user_room;

create index ui on user_room(age);
//error 视图不能建索引.

// 物化视图,on demand
create materialized view aaabbb
as select user_id,user_name,room_type
from users,room
where users.room_id=room.room_id;

create index uid ui on aaabbb(room_type);

create or replace user_room1
as select userid id..

物化视图不能做replace操作

只能在简单View(只关联一张表)上做DML操作,但也有限制.
一般做统计报表查询.也会加上with read only.

=====================
第九章 控制用户访问
grant connect,resource to hr;
create role manager;
grant xx to manager;
alter user scott ...
create user user identified by password;