OCP 1Z0-007笔记（1）

--1 distinct
select distinct column1 from talbe1;
--2 在....之间(between and)
select * from
where column1 between 1 and  3500;
--3 in
select * from  table1
where column1 in(1,2,3,5);
--4 通配符%和like
select * from  table1
where column1 like '%abc%';
--5 null
select * from talbe1
where column1 is null;
--6 order by 和 desc
select * from table1
order by column1 desc
----------------------------------------------------------------------------------------------
--7大小写函数
--lower小写:abcdef
select lower(ABCdeF)from dual;
--upper大写:ABCDEF
select upper(ABCdeF)from dual;
--initcap首字母大写:Abcdef
select initcap(ABCdeF)from dual;
--8其他字符操作函数（concat,substr,lingth,instr,lpad,rpad,replace,trim）
--concat连接:HelloWorld
select concat('Hello', 'World') from dual;
--substr从第n个取向后取m个:Hello
select substr('HelloWorld',1,5) from dual;
--length取宽度:10
select length ('HelloWorld') from dual;
--instr查找指定字符的位置:6
select instr('HelloWorld','W') from dual;
--lpad指字宽度，靠右，左边用指定字符补齐:*****24000
select lpad(24000,10,'*') from dual;
--rpad指字宽度，靠左，右边用指定字符补齐:24000*****
select rpad(24000,10,'*') from dual;
--replace搜索替换:BLACK and BLUE
select  replace('JACK AND JUE','J',BL') from dual;
--trim左右末端去掉指定字符:elloHWorld
select trim('H' from 'HHellHoWorldHH') from dual;
--9数字函数
--round指字位数四舍五入:45.93
select round(45.926,2) from dual;
--trunc指字位数截断：45.92
select trunc(45.926,2) from dual;
--mond求模:1
select mod(3,2) from dual;
--10日期函数(months_between,add_months,next_day,last_day,round,trunc)
--months_between两个日期之间相差的月数:19.6774194
select months_between('01-SEP-95','11-JAN-94') from dual;
--add_months指字日期加上n个月的日期:'11-JUL-94'
select add_months('11-JAN-94',6) from dual;
--ruand四舍五入(sysdate='25-JUL-03')
select round(sysdate,'MONTH') from dual;--对月进行四舍五入01-AUG-03
select round(sysdate,'YEAR') from dual;--对年进行四舍五入01-JAN-04
--trunc截断
select trunc(sysdate,'MONTH') from dual;--对月进行截断01-JUL-03
select trunc(sysdate,'YEAR')  from dual;--对年进行截断01-JAN-03
--next_day指定日期后下一个满足要求的日期:'08-SEP-95'
select next_day('01-SEP-95','FRIDAY')from dual;--下一个星其五
--last_dat指定日期所在月的最后一天的日期:'28-FEB-95'
select last_day('01-FEB-95') from dual;
--11to_char函数
select to_char(sal,'$99,999.00') fro emp;
select to_char(sysdate,'yyyy-mm-dd') from dual;
--12to_date函数
select to_date('01-JAN-90','yyyy-mm-dd') from dual;
--13nvl指定遇空反回值
select nvl(column1,0)from tabl1;--当表table1的字段column1值是null的时侯返回0
--14nul2指定非空和遇空的反回值
select nvl2(column1,'a','b')
from tabl1;--当表table1的字段column1值地非null时返回a,空时反回b
--15nullif相等反加null
select nullif(column1,column2)
from tabl1;--column1!=nulumn2时反回column1,column1=nulumn2时反回null
--16coalesce列表中第一个非空的表达式是函数的返回值:7
select coalesce(null,null,7,2,3,null,null)from dual;
--17if...then...else
--case
select empno,job,
case job
when 'CLERK' then 100*sal
when 'ANALYST'then 0.001*sal
else 88888
end      "xxx"
* from emp;
--18decode
select column1,
decode(column2,1,'1xxx',
               2,'2xxx'
                 'xxxx')
from table1;  --column2的值为1时返回1xxx,为2时返加2xxx,为其他他值时返回xxxx
----------------------------------------------------------------------------------------------
--19group分组函数(avg,sum,count,max,min,stddev,variance)
select job,
avg(sal),      --平均值
sum(sal),      --合计值
max(sal),      --最大值
min(sal),      --最小值
stddev(sal),   --标准差
variance(sal), --方差
max(hiredate),
min(hiredate)
from emp
group by job
select job,count(*) from emp group by job;         --行数统计
select count(*),max(sal),min(sal),avg(sal)from emp;--不一定非得有个group by 子句,整个表也算是一组
select count(distinct(deptno)) from emp;           --统计不重复的行数
select job,sum(sal) from emp group by job having sum(sal)>5000; --结果再过滤
select avg(nvl(comm,0)) from emp;--当comm遇空时用0进行运算,因为在有任一空值参与的运算结果为空
----------------------------------------------------------------------------------------------
--20Nautral joins 自然连接
select a.column1,b.column1,a.column2
from table1 a nautral join table2 b   --这要求在table1和table2中有同名的字段column1,且数据类型相匹配
--21using clause uing子句
select a.column1,b.column1,a.column2,b.column2,a.column3
from table1 a,table2 b
using (column1)
     --在table1和table2中的字段column1和column2都是匹配的，这时需要用using子句要以哪个字段连接这两张表
--22使用on子句
select * from table1 a join table2 on a.column1=b.column3
--23Full(to two-sided)outer joins 完全外连接
select * from table1 a left  outer join table2 b where a.column1=b.column1 --左连保持左表table1的完整
select * from table1 a reght outer join table2 b where a.column1=b.column1 --右连保持右表table2的完整
select * from table1 a full  outer join table2 b where a.column1=b.column1 --完整连接，保持两张表table1和table2都完整
--24Cross Joins(Cartesian product笛卡尔积).
select * from table1 cross join table2  --两张表相乘(笛卡尔积)
--25self-join自身连接
select  e.last_name emp, m.last_name mgr
from employees e join employees m
on (e.manager_id = m.employee_id);
--26Non-Equijoins不等于连接
select e.last_name, e.salary, j.grade_level
from employees e join job_grades j on e.salary
between j.lowest_sal and j.highest_sal;
---------------------------------------------------------------------------------------------------------
--27Single-Row Subqueries和Multiple-Row Subqueries(单行子查询和多行子查询)
--Single-Row Subqueries(=,>,>=,<,<=,<>)
--Multiple-Row Subqueries(in,any,all)
select * from table1 where c1 > any(select c3 from table2)--大于任何一个
select * from table1 where c1 > all(select c3 from table2)--大于每一个
--------------------------------------------------------------------------------------------
--28Set Operators(集合运算符)
union:
union
union all:
union all
intersect:
intersect
minus:
minus
----------------------------------------------------------------------------------------------------
--29insert into插行
insert into table1 (column1,column2) values (1,'abc');
insert into table1                   values (2,'abc');                   --不指定字段默所有字段和顺序
insert into table1 (column1,column2) select column3,column4 from table2; --用子句insert没有values
insert into table1                   select column3,column4 from table2; --用子句insert没有values
insert into (select column1,column2 from table1 where column1=3)
            values
            (77,'anan');
--30update修改行
update table1 set column1=23,column2='eee' where column1=4;
--31delete删除行
delete from table1 where column1=23;
--32truncate截断表（重建表）
truncate table table1;  --属于DDL语句，不能简单回滚
--33rollback,savepoint回滚,保存位置
update table1 set column1=null;
rollback;                                       --回滚所有没有commit的更改
update table1 set column1=null;
savepoint  p1;                                  --回滚至p1位置
update table1 set column2=null;
rollback to p1;
--34commit提交
delete from table1;
commit;
--rollback和commit只是对MDL语句的操作
--35Read Consistency(读一致性)
--在一个用户没有commit之前其它用户看到的是这个用户操作之前的数据,并且其它用户的修改操作会等待这个用户的--commit;
----------------------------------------------------------------------------------------------------
--36DDL语句的对像(table,view,sequence序列,index,synonym同义词)
--table命名规则
  --字符起始
  --字符数字_$#
  --不能和同一用户下的其它对像重名
  --不能和oracl保留字重名
--37create table
create table table1
(column1 number,
column2 varchar2(10),
column3 char(10),
column4 date default sysdate,--默认值是sysdate
column5 number(9.2));
--38Data Type
varchar2(),
char(),
number(p,s),
date,
long,
raw and long raw,
blob,
bfile,
rowid)
--39Constraint约束(5种)
not null      非空
unique        唯一
primary key   主键
foreign key   外键
check         满足自定义条件
--在字段定义
create table t1
(
  c1 number  constraint pk1 primary key,
  c2 char(2)
);
create table t1
(
  c1 number  constraint uk1 unique,
  c2 char(2)
);
create table t2
(c3 number constraint pk2 primary key,
  c4 number constraint fk2 references t1(c1),    --没有“foreign key”
  c5 char(2)
);
create table t2
(
c3 number,
c4 number constraint ch1 check(c4>10)
);
--在表定义
create table t1
(
  c1 number,
  c2 char(2),
  constraint pk2 primary key(c1)
);
create table t1
(
  c1 number,
  c2 char(2),
  constraint uk1 unique(c1)
);
create table t2
(c3 number,
c4 number,
c5 char(2),
constraint pk2 primary key(c3),
constraint fk2 foreign key(c4) references t1(c1)
  on delete cascade(或on delete set null)--所引用的那个主键中的值行被删的时侯将这个值被级联删除(或设为null)
)
create table t2
(
  c3 number,
  c4 number,
  constraint ch1 check(c4>10)
)
--40Creating a Table by Using a Subquery(用子查询创建表)
create table t2
as select * from t1;
--41alter table(add a new column,drop a column,modify an existing column,define a default value)
alter table table1 add (column8 number,column9 number)
alter table table1 drop column column8              --只可以删除单个字段
alter table table1 drop (column1,column3,column3)   --删除多个字段
alter table table1 modify (column1 char(2),column2 char(2));--column1和column2需是空的
alter table tt modify (c2 default 'z')   --添加默认值
--42drop table
drop talbe 意味着
先commit
删除所有data,structure,indexes,constraint
再commit
DDL是无法rollback的
----------------------------------------------------------------------------------------------
--43create创建一个视图
create view v1 as
select * from table1 where column1<11;
--44create or replace修改一下视图
create or replace view v1 as
select * from table1 where column1<2;
--45with read only(否定对视图的DML操作)
create view v1 as
select * from table1 where column1<11 with read only; --这个视图v1是无法进行DML操的，虽然它是简单视图
--46drop view移除视图
drop view v1;
-47create sequence创建一个序列
create sequence s1
increment by 10      --增量为10
start with 120       --从120开始(第一个值是120,起始值不可以<=0)
maxvalue 9999        --最大值是9999
nocache              --不使用缓存(cache使用缓存,在选这个时格式是: cache n(n是一次取多少个放入缓存),别担心instance重启不会丢任何东西的)
nocycle;             --不循环(cycle循环，假设最后一个<=9999的值是9998,那么下一个值不是9998+10-9999=9 而是 1 )
                     --可以同时又是cache又是cycle(试了不行，可是不知道原因)
--48nextval某个sequence的下一个值
insert into table1
values
(s1.nextval,'a','b');    --nextval用了以后即便是rollback了，这个val在sequence中也已经用过了
--49currval某个sequence的当前值
select s1.currval from dual;
--50drop sequence删除一个sequence
drop sequence s1;
--51dba_sequences 查看当前所有的sequence
select * from sys.dba_sequences where sequence_name ='S1';
--52alter sequence修改sequence
alter sequence s1
increment by 100
maxvalue 99999
nocache
nocycle;    --start with 是不可以修改的
--53create index创建索引
create index i1 on table1(column1);  --对表table1的字段(column1)建索引
--54drop index删除索引
drop index i1;
--55user_indexes或all_indexes查看数据库中已经定义的索引
--56create synonym创建同义词
create synonym xx  for t1;  --数据库对象可以定义同义词(包括:table,view,sequence,index,synonym)
create synonym xxx for xx;
--57drop synonym删除同义词
drop sysnonym xx;
--58user_synonyms或all_synonyms查看数据库中已经定义的同义词
----------------------------------------------------------------------------------------------
--59 dictionary只有两个字段，记录字典表的名称以及描述
--user_objects
--all_objects
--user_tables
--user_tab_columns
--user_constraints
--user_cons_columns
--user_view
--user_sequences
--user_synonyms
--user_tab_synonyms?????
--60comment on table 添加表注释
comment on table t1
is'abcdefzzzz';
--all_col_comments
--user_col_comments
--all_tab_comments
--user_tab_comments
---------------------------------------------------------------------------------------------
--61create user创建一个用户
create user uuidentified by a123 --创建一个新用户uu密码设为a123
--62grant 系统权限 to 用户   :授于用户系统权限
grant create session,  create table,
      create sequence, create view,
      create procedure
to    uu;
--63create role创建一个角色
create role js1;
--64grant 系统权限 to 角色   :授于角色系统权限
grant create table,create view to js1;
--65grant 用色 to 用户       :授于用户角色(权限组)
grant js1 to uu;
--66alter user ... identified by 更新用户密码
alter user uu identified by aa12 --更新用户uu的密码为aa12
--67grant select,update,delete,insert on 对象名 to 用户(或public)  :授于用户对象权限
select select,delete on scott.emp to uu with grant option;--with grant option表示对权限可以传递
--68审核用系统表
--Data Dictionary View    Description
--ROLE_SYS_PRIVS    System privileges granted to roles
--ROLE_TAB_PRIVS    Table privileges granted to roles
--USER_ROLE_PRIVS    Roles accessible by the user
--USER_TAB_PRIVS_MADE    Object privileges granted on the user's objects
--USER_TAB_PRIVS_RECD    Object privileges granted to the user
--USER_COL_PRIVS_MADE    Object privileges granted on the columns of the user's objects
--USER_COL_PRIVS_RECD    Object privileges granted to the user on specific columns
--USER_SYS_PRIVS    System privileges granted to the user
--69revoke select,update,delete,insert on 对象 from 用户  :收回用户的对象权限
revoke delete on scott.emp from uu;