OCP课程学习二

使用DDL语句创建和管理表

• 创建表对象
• 约束条件的创建和管理
• 修改表
• 删除表

创建一个简单表
create table dept1(
deptno  number(2),
dname  varchar2(10),
loc  varchar2(10));
desc dept; 注意desc只能在命令窗口中执行

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的区别
修改表
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; 重命名这个列为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; 删除表

创建其他方案对象

• 创建视图
• 创建，维护和使用序列
• 创建和维护索引
• 创建同义词

grant create view to scott,hr;  创建视图
alter user soctt identified by oracle_4U 
conn  scott/oracle_4U;
create table empcopy as select * from emp;  创建一个表
create view empv as select ename,sal from empcopy; 创建一个视图
select * from empv;  可以看出来只有两列
create view empv1 as select ename xingming, sal gongzi from empcopy;  使用别名创建视图
select * from empv1 ; 可以看到 两列都是xingming 和gongzi 
insert into empv1 values('hanfei',5000)   视图中添加一个行
select * from empcopy;  可以看到新增的已经加上去了
拒绝DML操作 
create view empv2 as select ename,sal from empcopy with read only;
insert into empv2 values('hanfei',5000) 会报错，只有读的权限 
drop view empv2 ; 删除视图
#创建序列 
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;  删除索引
#创建和管理索引
create index dept_deptno_idx on deptcopy(deptno); #创建索引
drop index dept_deptno_idx; #删除索引
#创建和管理同义词
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;

管理数据库存储结构

sqlplus / as sysdba 
startup 
set linesize 200;
set pagesize 100;
#确定技术文件和临时文件的名称和大小
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;
#重新调整表空间大小 
alter database datafile  '/u01/app/oracle/oradata/orcl/newts04.dbf'  resize 100M;
#删除表空间 
drop tablespace newts1 including cotents and datafiles; 

管理用户安全性

• 创建和管理用户账户
• 身份认证方式
• 授权和撤销权限
• 创建和管理配置文件

select username,user_id,account_status from dba_users;
create user hanfei identified by 123;
create user 'hanfei' identified by 123;