使用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;
