使用DDL語句創(chuàng)建和管理表
- 創(chuàng)建表對象
- 約束條件的創(chuàng)建和管理
- 修改表
- 刪除表
創(chuàng)建一個(gè)簡單表
create table dept1(
deptno number(2),
dname varchar2(10),
loc varchar2(10));
desc dept; 注意desc只能在命令窗口中執(zhí)行
create table dept2(
deptno int,
dname char(10),
loc char(10),
create_time date default sysdate);
insert into dept2 values(10,'sal','sh',default);
select * from dept2;
select length(dname) from dept1 where dname = 'sal'; 3
select length(dname) from dept2 where dname= 'sal'; 10
比較char和varchar的區(qū)別
修改表
create table test5(id int,name char(10));
insert into test5 values(1,'hanfei');
select * from test5;
alter table test5 add(sex varchar(10)) 添加
alter table test5 rename colum sex to xingbie 修改列名為xingbie
alter table test5 drop colum xingbie; 刪除xingbie的這一列
alter table test5 rename to ts5; 重命名這個(gè)列為ts5
select table_name from user_tables; 查看所有的表
alter table ts5 read only # 只讀表
alter table ts5 read write; 讀和寫
insert into ts5 values(2,'shanghai');
drop table ts5; 刪除表
創(chuàng)建其他方案對象
- 創(chuàng)建視圖
- 創(chuàng)建，維護(hù)和使用序列
- 創(chuàng)建和維護(hù)索引
- 創(chuàng)建同義詞
grant create view to scott,hr; 創(chuàng)建視圖
alter user soctt identified by oracle_4U
conn scott/oracle_4U;
create table empcopy as select * from emp; 創(chuàng)建一個(gè)表
create view empv as select ename,sal from empcopy; 創(chuàng)建一個(gè)視圖
select * from empv; 可以看出來只有兩列
create view empv1 as select ename xingming, sal gongzi from empcopy; 使用別名創(chuàng)建視圖
select * from empv1 ; 可以看到 兩列都是xingming 和gongzi
insert into empv1 values('hanfei',5000) 視圖中添加一個(gè)行
select * from empcopy; 可以看到新增的已經(jīng)加上去了
拒絕DML操作
create view empv2 as select ename,sal from empcopy with read only;
insert into empv2 values('hanfei',5000) 會報(bào)錯(cuò)缀旁，只有讀的權(quán)限
drop view empv2 ; 刪除視圖
#創(chuàng)建序列
create sequence dept_deptno_seq
create table deptcopy as select * from dept;
insert into deptcopy(deptno,dname) values(dept_deptno_seq.nextval,'TEACH')
select * from deptcopy;
select dept_deptno_seq.currval from dual;
select dept_deptno_seq.nextval from dual;
alter sequence dept_deptno_seq
increment by 20
nocache
nocycle
select dept_deptno_seq.currval from dual;
insert into deptcopy(deptno,dname) values(dept_deptno_seq.nextval,'TEACH1');
select * from deptcopy; 查詢表
drop sequence dept_deptno_seq; 刪除索引
#創(chuàng)建和管理索引
create index dept_deptno_idx on deptcopy(deptno); #創(chuàng)建索引
drop index dept_deptno_idx; #刪除索引
#創(chuàng)建和管理同義詞
conn / as sysdba
grant create synonym to scott,hr;
create cynonym dept_syn for dept;
select * from dept_syn;
create public synonym dept_syn1 for scott.dept;
select * from dept1_syn1;
conn scott / oracle_4U
select * from dept_syn1;
drop synonym dept_syn;
管理數(shù)據(jù)庫存儲結(jié)構(gòu)
sqlplus / as sysdba
startup
set linesize 200;
set pagesize 100;
#確定技術(shù)文件和臨時(shí)文件的名稱和大小
select name,bytes from v$datafile union all select name,bytes from v$tempfile;
create tablespace newts1 datefile '/u01/app/oracle/oradata/orcl/newts01.dbf' size 20m autoallocate;
create tablespace newts2 datafile '/u01/app/oracle/oradata/orcl/newts02.dbf' size 20M uniform size 128k
create tablespace newts3 datafile '/u01/app/oracle/oradata/hf1/newts03.dbf' size 20M autoextend on next 2M maxsize 50M ;
create tablespace newts4 datafile '/u01/app/oracle/oradata/hf1/newts04.dbf' size 20M reuse uniform segment space management auto;
#查看表空間
select tablespace_name,block_size,max_size from dba_tablespaces;
select file_name,file_id,bytes,status from dba_data_files;
select tablespace_name,bytes from dba_free_sapce;
#管理表空間
select tablespace_name,status from dba_tablespaces;
select tablespace_name,status from dba_tablespaces;
#重新調(diào)整表空間大小
alter database datafile '/u01/app/oracle/oradata/orcl/newts04.dbf' resize 100M;
#刪除表空間
drop tablespace newts1 including cotents and datafiles;
管理用戶安全性
- 創(chuàng)建和管理用戶賬戶
- 身份認(rèn)證方式
- 授權(quán)和撤銷權(quán)限
- 創(chuàng)建和管理配置文件
select username,user_id,account_status from dba_users;
create user hanfei identified by 123;
create user 'hanfei' identified by 123;
