Oracle数据库自学的基本操作

create table student_djz (
stuId integer
)
select * from  student_djz;
--创建用户
create user test identified by test;
--角色授权
grant connect,resource to test; 
--创建角色
create role TESTROLE;
--查询角色
select * from role_sys_privs where role='TESTROLE';
--查询角色
select * from role_role_privs;
--查询角色
select * from all_users;
--查询表
select * from dba_sys_privs;
--查询角色
select * from dba_roles where role='TESTROLE';
--删除角色
drop role testrole;
--解除权限
revoke connect,resource from test;

--4月15上午

--查询所有用户信息
select *from dba_users
--查询所有用户对象信息
select *from user_objects
--查询数据库所有用户信息
select *from all_users
--查询数据库所有用户对象信息
select*from all_objects
--查询日志分析字典文件信息
select * from v$logmnr_dictionary
--归档用户信息
select*from v$archived_log
--初始化参数信息
select*from v$parameter

--4月15下午
--查询动态视图来获取控制文件信息
select * from v$controlfile
--查询数据字典和动态视图来获取控制文件信息
select *from v$datafile
select * from dba_data_files
--获取联机重做日志
select * from v$log
select * from v$logfile
--获取归档日志
select * from v$archived_log
--获取区信息
select * from dba_extents
--获取段信息
select * from dba_segments
--获取表空间
select *from dba_Tablespaces
select * from v$tablespace

--4月16
--控制文件
alter database backup controlfile to 'c:\control.bkp';

--创建表空间tablespace_1(默认为小文件表空间)
create tablespace tablespace_1
 datafile 'e:\tbs1.dbf' size 10M;
--创建表空间tablespace_2，拥有两个数据文件
create tablespace tablespace_2 
datafile'e:\tbs2a.dbf'size 10M,'e:\tbs2b.dbf'size 10M;
--创建大文件表空间 tablespace_3
create bigfile tablespace tablespace_3 
datafile 'e:\tbs3.dbf' size 10M;
--创建自动扩展的表空间 tablespace_4
create tablespace tablespace_4 
datafile 'e:\tbs4.dbf' size 500k reuse autoextend on next 500k maxsize 100M;      
--创建表空间tablespace_6,日志记录属性为logging
create tablespace tablespace_6 
datafile'e:\tbs6.dbf' size 20M logging;
--创建本地管理表空间tablespace_7
create tablespace tablespace_7
datafile'e:\tbs7.dbf'size 10M extent management local;
--创建本地管理表空间tablespace_8，自动分配区大小
create tablespace tablespace_8
datafile'e:\tbs8.dbf' size 100M reuse extent management local autoallocate;
--创建本地管理表空间tablespace_，区大小128k
create tablespace tablespace_9
datafile'e:\tbs9.dbf'size 10M extent management local uniform size 128K;
--创建具有手动段空间管理的表空间tablespace_10
create tablespace tablespace_10
datafile'e:\tbs10.dbf'size 10M extent management local segment space management  manual;
--创建具有自动动段空间管理的表空间tablespace_11
create tablespace tablespace_11
datafile'e:\tbs11.dbf'size 10M extent management local segment space management auto;
--创建非标准块（16k）表空间tablcespace_12
alter system set db_16k_cache_size=10M;
create tablespace tablespace_12
datafile'e:\tbs12.dbf'size 100M blocksize 16K;
--查找已经创建的表空间
select * from dba_tablespaces
--创建临时表空间temp1
create temporary tablespace temp1
tempfile 'e:\temp1.dbf' size 5M autoextend on;
--创建UNDO表空间 undo1
create UNDO tablespace undo12
datafile'e:\undo12.dbf' size 10M autoextend on next 2M maxsize 100M;

--更改表空间大小
alter tablespace tablespace_3 resize 50M;
--将表空间脱机
alter tablespace tablespace_1 offline;
--将表空间联机
alter tablespace tablespace_1 online;
--将表空间进行立即脱机
alter tablespace tablespace_1 offline immediate;
--修改表空间名称
alter tablespace tablespace_1 rename to tablespace_1a;
--删除表空间
drop tablespace tablespace_1;
--删除表空间，包括表空间内容
drop tablespace tablespace_1 including contents;
--删除表空间，删除数据文件与相关操作系统文件
drop tablespace tablespace_1 including contents and datafiles; 
--添加数据文件
alter tablespace tablespace_1a
add datafile'e:\tbs1b.dbf'size 10M;
--添加数据文件
alter tablespace tablespace_1a
add datafile'e:\tbs1c.dbf'size 10m
autoextend on
next 1M
maxsize 50M;
--启用数据文件拓展功能
alter database
datafile 'e:\tbs1b.dbf'
autoextend on next 5M maxsize 100M;
--更改数据文件
alter database
datafile 'e:\tbs1b.dbf'
resize 40M;
--将表空间脱机
alter tablespace tablespace_1a offline normal;
--移动文件

e:\>move e:\tbs1a.dbf e:\tbs1ay.dbf
--重命名数据文件
alter tablespace tablespace_1a
rename datafile 'e:\tbs1a.dbf'to 'c:\tbsa1y.dbf';
--将表空间联机
alter tablespace tablespace_1a online;
--创建重做日志文件组
alter database
add logfile group 4('e:\tbs4a.dbf') size 100M;
--创建重做日志文件
alter database
add logfile member 'e:\tbs4b.dbf' to group 4;
--删除重做日志文件
alter database
drop logfile member'e:\tbs4b.dbf';
--重命名重做日志文件
alter database
rename file'e:\tbs4a.dbf' to 'e:\tbs4c.dbf'
--删除重做日志文件组
alter database drop logfile group 4;
--清除重做日志文件

--查看所有用户：
select?*?from dba_users;
select?*?from?all_users;
select?*?from?user_users;
 
--查看用户或角色系统权限(直接赋值给用户或角色的系统权限)：
select?*?from?dba_sys_privs;
select?*?from?user_sys_privs;
 
--查看角色(只能查看登陆用户拥有的角色)所包含的权限
sql>select?*?from?role_sys_privs;
 
--查看用户对象权限：
select?*?from?dba_tab_privs;
select?*?from?all_tab_privs;
select?*?from?user_tab_privs;
 
--查看所有角色：
select?*?from?dba_roles;
 
--查看用户或角色所拥有的角色：
select?*?from?dba_role_privs;
select?*?from?user_role_privs;

select * from dba_tablespaces
--查询单列数据
select deptno from scott.dept
--查询两列数据
select deptno,dname from scott.dept;
--查询所有数据
select * from scott.dept;
--查询单列数据取消重复行
select distinct deptno from scott.dept;
--查询单列数据并指定别名
select deptno a from scott.dept;
--查询单列数据并指定别名
select deptno as a from scott.dept;
--计算100+200的值
select 100+200 from dual;
--计算100*200的值
select 100*200 from dual;
--查询表中的dname列数据
select dname from scott.dept;
--查询所有数据并指定别名
select * from scott.dept dept;
--where子句
--查询表中列大于15的数据
select * from scott.dept where deptno>15;
--查询表中列大于15的数据，且dname为sales (sales要大写)
select * from scott.dept where deptno>15 and dname='SALES';
-- 查询表Scott。dept中deptno列在17和34之间的数据
select* from scott.dept where deptno between 17 and 34;
-- 查询表Scott。dept中deptno列在10或30的数据
select* from scott.dept where deptno in(10,30);
-- 查询表Scott。dept中deptno列为非空的数据
select* from scott.dept where deptno is not null;
-- 查询表Scott。dept中deptno列以SAL开头的数据
select* from scott.dept where dname like'SAL%';
-- 查询表Scott.dept和scott.emp中deptno列相同的数据
select e.empno,e.ename,e.job,d.dname,d.loc
from scott.emp e,scott.dept d
where e.deptno=d.deptno;

--order by
--按列对表排序
select * from scott.dept order by deptno;
--按列对表进行降序排序
select * from scott.dept order by deptno DESC;
--按dept和dename列对表进行排序
select * from scott.dept order by deptno ,dname;
--按dename列的数字位置对表进行排序
select deptno,dname from scott.dept order by 2;
--group by 字句
select deptno,count(empno),AVG(sal)
from scott.emp
group by deptno;
--having子句
select deptno,count(empno),AVG(sal)
from scott.emp
group by deptno
having AVG(sal)>2000;
--组函数
--查询表函数
select count(*)from scott.dept;
--查询非空值的行数
select count(deptno)from scott.dept;
--查询dept表中deptno列的最大值
select MAX (deptno)from scott.dept;
--查询列的总和
select SUM (deptno) from scott.dept;
--查询列的平均数
select AVG (deptno) from scott.dept;
--查询列的方差
select VARIANCE (deptno) from scott.dept;
--查询列的标准偏差
select STDDEV (deptno) from scott.dept;

--子查询
select * from scott.emp
--from字句子查询
select empno,ename
from(select empno,ename from scott.emp where deptno=20 );

select empno,ename 
from(select*from scott.emp where deptno=20);
--where中使用子查询
select ename,job from scott.emp
where empno =(select empno from scott.emp where mgr=7902);
--HAVING中使用子查询
select deptno,min(sal)
from scott.emp group by deptno
having min(sal)>(select min(sal)from scott.emp where deptno=20);
--insert字句中使用子查询
insert into
(select empno from scott.emp) values (3000);
--update字句中使用子查询
update scott.emp
set empno =(select AVG(empno)from scott.emp) where empno=3000;
--DELETE字句中使用子查询
delete from (select* from scott.emp)
where job='clerk'and sal=800;

--合并查询
create table T1(id int primary key)
create table T2(id int primary key)
insert into T1 values (4)
insert into T2 values (6)
select * from T2
--union 合并两个结果的集合
select * from T1;
select * from T2;
select * from T1 union select * from T2;
--union all获取两个结果集的并集，但是不会自动去除重复行
select * from T1 union all select * from T2
--INTERSECT取交集
select * from T1 intersect select * from T2;
--minus获得两个集合的差集
select * from T1 minus select * from T2;

--数据操作

create table table_1(
id int,
name  varchar2 (20)
)
-- 插入一行数据
insert into table_1
values (14,'Oracle');
--插入一列数据
insert into table_1(id)
values(15);
--更新数据
--更新表中id为一的数据为10
update table_1 set id=10 where id=1;
--更新表中id列所有数据为10
update table_1 set id=10;
--删除数据 
--删除表中id列为10的行
delete from table_1 where id=10;
--删除表中所有数据
delete from table_1;

--单行函数--书168
--ASCII
--返回字符串Aa和abc的第一个
select ascii ('A'),ascii('a'),ascii('ABC')from dual;
--CHR返回十进制ASCII码n对应的字符
select CHR(65),CHR(97)from dual;
--concat返回将b添加到a后面形成的字符串
select concat ('a','b')from dual;
--initcap返回将单词heLLo INux的每个首字母大写，其他字母都 小写的字符串
select initcap ('a','b')from dual;
--instr在corporate floor中从3开始搜索OR第二次出现的位置，并返回该位置的数字
select instr('corporate floor','or',3,2)from dual;
--length返回字符hello linux的长度
select length ('hellolinux')from dual;
--lower返回将字符hello Oracle 所有字母都小写的字符串
select lower('hello oracle')from dual;
--lpad在hello的后边填充*，直到字符串总长度达到7
select lpad('hello',7,'*')from dual;
--RPAD去掉hello 的右边填充,知道字符串的总长度到达7
select RPAD('hello',7,'*')from dual;
--LTRIM去掉Hello左边所包含的He中的任何字符,然后返回剩余的字符串
select ltrim('hello','he')from dual;
--RTRIM去掉Hello右边所包含的llo中的任何字符,然后返回剩余的字符串
select rtrim('hello','llo')from dual;
--REPLACE把字符Hello中出现的el都替换为EL,然后返回剩余的字符串
select replace('hello','el','EL')from dual;
--SUBSTR从第3个字符开始返回字符串ABCDEFGH中连续的4个字符
select substr ('abcdefg'3,4)from dual;
--TRANSLATE把所有在llo中出现的字符,用对应在LLO中出现的字符代替,然后返回被代替之后的Hello字符串
select translate ('hello','llo','LLO')from dual;
--TRIM返回将字符串aabcdefgaa去除开头、结尾和两者的字符
select trim(leading 'a'from'aabcdefgaa'),
TRIM(TRAILING'a'from'aaabcdefgaa'),
TRIM('a'from'aabcdefgaa')
from dual;
--UPPER返回将字符串Hello Oracle所有字母都大写的字符串
select upper('Hello Oracle')from dual;
--sysdate返回当前数据库的时间和日期
select sysdate from dual;
--last_day 返回当天日期所在月份的最后一天
select sysdate,last_day(sysdate)from dual;
--MONTHS BETWEEN返回日期月数的时间差
select months_between(to_date('02-02-2015','mm-dd-yyyy'),to_date('06-06-2016','mm-dd-yyyy'))
"months"from dual;
--NEXT_DAY返回日期12-6月-2015后的下一个星期一
select next_day ('12-6月-2015','星期一')from dual;

--其他函数
--GREATEST找最大数
select greatest(9,999,99)from dual;
select greatest('ABC','abc','abd','abda')from dual;
--LEAST找最小数
select least(9,999,99)from dual;
select least('ABC','abc','abd','abda')from dual;
--NVL2
select nvl2(1,2,3)from dual;
--ABS函数用来返回N/绝对值
select ABS(-15),ABS(15)from dual;
--ACOS函数用来返回余玄值
select ACOS(-1),ACOS(1)from dual;
--ASIN函数用来返回N的反正玄值
select ASIN(0.6)from dual;
--ATAN函数用来返回N的反正切值
select ANTN(-1),ANTN(1)from dual;
--CEIL函数用来返回大于或等于N的最小值
select CEIL(5.7),CEIL(-5.7),CEIL(5.4)from dual;
--COS函数用来返回N的余玄值
select cos(100)from dual;
--COSH函数用来返回N的双曲线余玄值
select COSH(0)from dual;
--LN函数用来返回自然对数，其中N》0
select LN(95)from dual;
--LOG用来返回以N2为底的N1的的对数，N2不为0
select LOG(10,100),LOG(3,27)from dual;
--MOD函数用来返回N2除以N1之后的余数
select MOD(11,4),MOD(-11,4)from dual;
--power函数用来返回幂
select power(2,6),power(2,3),power(2,3)from dual;
--ROUND返回四舍五入的值
select round(25.182,1)from dual;
--SIGN用来返回正负结果
select sign(-15),sign(15.6),sign(15)from dual;
--SIN用来返回N的正玄值
select sin(30*3.14159265359/180)from dual;
--SQRT用来返回平方根
select SQRT(26)FROM dual;
--TRUNC返回N1截断到N2位小数
 
--创建一个简单表
create table T1(
id int primary key,
username varchar(50),
userage int,
userxb varchar(50)
)
 
insert into T1 values (1,'Oracle',20,'男');
insert into T1 values (2,'大舅子爱邦',20,'男');
insert into T1 values (3,'狗头老高',21,'男');
insert into T1 values (4,'三水的校花涛',19,'女');

select * from T1
show usertest
 

--创建用户
create user usertest identified by 123456;
--角色授权
grant connect,resource to usertest; 
--创建角色
create role TESTROLE;
--创建表一
create table table_1(
id int,
name varchar2(20)
);
--在表空间users中创建表2
create table table_2(id int,name varchar2(20))tablespace users;
--创建表4，开启并行功能加速表的创建
create table table_4(id int,name varchar2(20))parallel;
--创建表5，记录重做日志
create table table_5(id int,name varchar2(20))logging;
--通过scott.dept来创建表6
create table table_6 as select * from scott.dept;

--启用表1的触发器
alter table table_1 enable all triggers
--禁用表1的触发器
alter table table_1 disable all triggers;

--禁用表1的表锁定
alter table table_1 disable table lock;
--启用表1的表锁定
alter table tab;e_1 enable table lock;

--解除表一分配未使用的空间
alter table table_1 deallocate unused;

--标记未使用列
conn scott/tiger
alter table dept set unused(dname);
select *from user_unused_col_tabs;                         

select * from table_1
--为表一添加一列
alter table table_1 add kkk varchar2(20);
--为表一添加两列
alter table table_1 add(duty number (2,2),visa varchar2(30));
--修改列  使用MODIFY字句修改现有列的属性
--将表一的id列数据类型改为carchar(40)
alter table table_1 modify id varchar2(40);
--删除列
conn scott/tiger
alter table table_1 drop(kkk);
--为表添加注释
comment on table table_1 is 'table name is table_1';
column comments fromat A10
select table_name,table_type,comments
       from dba_tab_comments
       where table_name='table_1';
       
--更改表的日志记录属性
--使表一不记录日志
alter table table_1 nologging;
--使表一记录日志
alter table table_1 logging;
--压缩表
--对表一不压缩
alter  table table_1 nocompress;
--对表一压缩
alter table table_1 compress;
select table_name,compression
   from dba_tables
   where table_name='table_1'and owner='sys';
--创建表一
create table table_1(
id int,
name varchar2(20)
);
--在表空间users中创建表2
create table table_2(id int,name varchar2(20))tablespace users;
--创建表4，开启并行功能加速表的创建
create table table_4(id int,name varchar2(20))parallel;
--创建表5，记录重做日志
create table table_5(id int,name varchar2(20))logging;
--通过scott.dept来创建表6
create table table_6 as select * from scott.dept;

--启用表1的触发器
alter table table_1 enable all triggers
--禁用表1的触发器
alter table table_1 disable all triggers;

--禁用表1的表锁定
alter table table_1 disable table lock;
--启用表1的表锁定
alter table tab;e_1 enable table lock;

--解除表一分配未使用的空间
alter table table_1 deallocate unused;

--标记未使用列
conn scott/tiger
alter table dept set unused(dname);
select *from user_unused_col_tabs;                         

select * from table_1
--为表一添加一列
alter table table_1 add kkk varchar2(20);
--为表一添加两列
alter table table_1 add(duty number (2,2),visa varchar2(30));
--修改列  使用MODIFY字句修改现有列的属性
--将表一的id列数据类型改为carchar(40)
alter table table_1 modify id varchar2(40);
--删除列
conn scott/tiger
alter table table_1 drop(kkk);
--为表添加注释
comment on table table_1 is 'table name is table_1';
column comments fromat A10
select table_name,table_type,comments
       from dba_tab_comments
       where table_name='table_1';
       
--更改表的日志记录属性
--使表一不记录日志
alter table table_1 nologging;
--使表一记录日志
alter table table_1 logging;
--压缩表
--对表一不压缩
alter  table table_1 nocompress;
--对表一压缩
alter table table_1 compress;
select table_name,compression
   from dba_tables
   where table_name='table_1'and owner='sys';
--约束
--创建notnull空值
--将表一的id列设置成不允许为空，指定名称为notnull_id
alter table table_1 modify id constraint notnull_id not null;
--将表一的id列设置成不允许为空
alter table table_1 modify id not null;

--主键约束
alter table table_1 add primary key(id);
select constraint_name,table_name
from dba_constraints
where table_name='table_1';
--在表一上添加唯一约束uq_id
alter table table_1 add constraint uq_id unique(id);
--在表一上添加核查约束CK_id
alter table table_1 add constraint ck_id check(id>0);
--在表二上添加外键约束fk_id
alter table table_2 add constraint fk_id foreign key(id) references table_1(id); 
--修改约束
--启用表一主键约束pk_id
alter table table_1 enable constraint pk_id;
--禁用表一主键约束pk_id
alter table table_1 disable constraint pk_id;

CREATE TABLE student
( student_id number(10) not null,
student_name varchar2(50) not null,
student_message varchar2(50),
CONSTRAINT student_pk PRIMARY KEY(student_id)
);
CREATE TABLE teacher
( teacher_id number(10) not null,
student_id number(10) not null,
CONSTRAINT teacher_pk PRIMARY KEY (teacher_id ),
CONSTRAINT fk_student
FOREIGN KEY (student_id)
REFERENCES student(student_id)
);
select a.constraint_name, a.column_name
from user_cons_columns a, user_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type = 'P' and a.table_name = 'STUDENT'
--视图
--创建简单视图view_1
create view view_1 as select * from scott.dept; 
select *from view_1
create or replace view view_1 as select * from scott.dept; 
--创建复杂视图view_2
create view view_2(name,minsal,maxsal,avgsal)
as
select dept.dname,min(emp.sal),max(emp.sal),avg(emp.sal)
from scott.emp emp,scott.dept dept
where emp.deptno=dept.deptno
group by dept.dname;
--创建只读视图view_3
create view view_3 as
select * from scott.dept with read only;
--创建check约束视图view_4
create view view_4 as
select deptno,dname from scott.dept where deptno>10
with check option constraint ck_deptno;
--创建连接视图view_5
create view view_5 as
select emp.ename,emp.empno,emp.job,dept.dname
from scott.emp emp,scott.dept dept
where emp.deptno in(10,30)
and emp.deptno=dept.deptno;
--强制创建视图view_6
create  force view view_6 as select * from scott.dept;
--视图中的数句操作
--通过视图view_1插入数据
insert into view_1(deptno,DNAME)
values(60,'kuroda');
--通过视图view_1更新数据
update view_1 set deptno=deptno*1.1 where deptno=66;
--通过视图view_1删除数据
delete from view_1 where deptno=66;
--重新编译视图
alter view view_1 compile;
column object_name format a10
select object_name,status
from dba_objects
where object_name='view_1';
--删除视图
drop view view_1;
--创建私有同义词
create synonym deptsyn for scott.dept;
--创建公用同义词
create public synonym public_dept for scott.dept;
--通过同义词查询数据
select * from deptsyn;
--通过同义词插入数据
insert into deptsyn(deptno,dname)
values (80,'lisi');
--通过同义词更新数据
update dept set deptno=90 where deptno=80;
--通过同义词删除数据
delete from dept where deptno=90;
--删除同义词
drop synonym deptsyn;
--删除公用同义词
drop public synonym public_dept;

--创建序列sequence_1
create sequence sequence_1
start with 1  --间隔起始值为一
increment by 1 --增量间隔不允许为0，也不允许为小数
nomaxvalue  --不设置最大值（最大值过大）
nocycle    --达到最大值自动循环
cache 10
order;
--使用nextval和currval
select sequence_1.nextval from dual;
select sequence_1.currval from dual;
--在insert语句中使用序列
insert into table_1(id,name)
values (sequence_1.nextval,'zhangsan');
select * from table_1;
--在update语句中使用序列
update table_1
set id=sequence_1.nextval
where id=3;
select * from table_1;
--修改序列sequence_1
alter sequence sequence_1
increment by 1
maxvalue 10000
nocycle
cache 20;
--删除序列
drop sequence sequence_1;

--索引
--创建单列索引index_1
create index index_1 on table_1(id);
--在表空间users上创建索引index_2
create index index_2 on table_1(id)tablespace users;
--创建复合索引index3
create index index_3 on table_1(id,name);
--创建复合索引index_4,按id列进行建压缩
create index index_4 on table_1(id,name)compress 1;
--创建索引index_5，不产生重做日志
create index index_5 on table_1(id)nosort nologging;
--创建索引index_6，设置表空间的存储参数
create index index_6 on table_1(id)
tablespace users
storage(initial 20k next 20k pctincrease 75);
--联机创建索引index_8
create index index_8 on table_1(id)online;
--创建位图索引index_11
create bitmap index index_11 on table_1(name);
--创建反向建索引index_12
create index  index_12 on table_1(id)reverse;
--重建现存的索引
alter index index_1 rebuild;
--联机重建现存索引index_1
alter index index_1 rebuild online;
--重建现有索引index_1,将索引入存储表空间users中
alter index index_1 rebuild tablespace users;
--重建现有索引index——1，在相反的顺序中存储索引块的字节
alter index index_1 rebuild reverse;
--收缩索引
alter index index_2 shrink space;
--合并索引
alter index index_1 coalesce;
--启用索引
alter index index_10 enable;
--禁用索引
alter index index_10 disable;
insert into table_1 values(1,'zhangsan');
insert into table_1 values(1,'zhangsan')
--重命名索引
alter index index_1 rename to index_1a;
--删除索引
drop index index_1;