Oracle综述算是吧

WHERE  salary  BETWEEN 200 and 300
    工资在200到300之间，包括200和300。

IN
WHERE salary  IN  (200，300，400）
    工资必须是200，300，400之中的值。

------------------------------------------------------------------------------------------

语法：
COUNT[（*）|（DISTINCT|ALL|]COLUMN NAME）

注意：DISTINCT统计唯一的行；
           DISTINCT不能用于COUNT（*），只能用
             于COUNT（column_name）

------------------------------------------------------------------------------------------

 

用于创建与用户访问相关的对象，也控制着用户的权限分配

    ALTER PASSWORD改变口令

    GRANT为用户授予特权

    REVOKE从用户处收回特权

------------------------------------------------------------------------------------------

查询在指定表中是否存在一行符合某种条件的数据。
WHERE EXISTS
    （SELECT employee_id
        FROM  employee_tbl
        WHERE employee_id = ‘3333’)

 

WHERE salary IS NULL   
    工资为空值。

WHERE salary = NULL   
    工资中含有NULL字符。
------------------------------------------------------------------------------------------

例：
SELECT  emp_id, emp_name
    FROM  employee_tbl
    WHERE  emp_phone  IS NULL

------------------------------------------------------------------------------------------

 

使用匹配符将一个值同其相似的值比较。
匹配符包括：- 代表一个字符。
                % 代表多个字符。
如：
        WHERE salary LIKE  ‘-00’
        查找‘00’作为后两位数的值。
        WHERE salary LIKE  ‘200%’
        查找‘200’作为前三位数的值。

 

 

------------------------------------------------------------------------------------------

连接

AA表的基本情况:
create table AA as select empno, ename, deptno from emp;
select a.empno, a.ename, a.deptno from AA a;
1    7369    SMITH    20
2    7499    ALLEN    30
3    7521    WARD    30
4    7566    JONES    20
5    7654    MARTIN    30

BB表的基本情况:
create table BB as select empno, ename, deptno from empdemo;
select b.empno, b.ename, b.deptno from BB b;
1    1000    SMITH    20
2    1001    ALLEN    30
3    1002    WARD    30
4    1003    JONES    20
5    1004    MARTIN    10

1:联接可分为内联接和外联接
 (内联接): 从两个表中选择匹配的行.
 select a.empno, a.ename, a.deptno, b.empno, b.ename, b.deptno from AA a, BB b
 where a.deptno = b.deptno;
 //或写成如下方式也可:
 select a.empno, a.ename, a.deptno, b.empno, b.ename, b.deptno
 from AA a inner join BB b on a.deptno = b.deptno;
 //两个表中完全匹配的行,进行a(1) * b(n) = c(n)查询结果:
  3    7369    SMITH    20    1003    JONES    20
  1    7369    SMITH    20    1000    SMITH    20
  5    7499    ALLEN    30    1001    ALLEN    30
  8    7499    ALLEN    30    1002    WARD    30
  7    7521    WARD    30    1001    ALLEN    30
 10    7521    WARD    30    1002    WARD    30
  4    7566    JONES    20    1003    JONES    20
  2    7566    JONES    20    1000    SMITH    20
  6    7654    MARTIN    30    1001    ALLEN    30
  9    7654    MARTIN    30    1002    WARD    30

 select b.empno, b.ename, b.deptno, a.empno, a.ename, a.deptno from BB b, AA a
 where b.deptno = a.deptno;
 //或写成如下方式也可:
 select b.empno, b.ename, b.deptno, a.empno, a.ename, a.deptno
 from BB b inner join AA a on b.deptno = a.deptno;
  2    1000    SMITH    20    7566    JONES    20
  1    1000    SMITH    20    7369    SMITH    20
  6    1001    ALLEN    30    7654    MARTIN    30
  7    1001    ALLEN    30    7521    WARD    30
  5    1001    ALLEN    30    7499    ALLEN    30
  9    1002    WARD    30    7654    MARTIN    30
 10    1002    WARD    30    7521    WARD    30
  8    1002    WARD    30    7499    ALLEN    30
  4    1003    JONES    20    7566    JONES    20
  3    1003    JONES    20    7369    SMITH    20

 (外联接): 允许用户从一个表中选择所有的行并从另一个表中选择匹配的行(又分为左外联接和右外联接).
 (左外联接): 左边表的所有记录 + 右边表的匹配行记录
 select a.empno, a.ename, a.deptno, b.empno, b.ename, b.deptno from AA a, BB b
 where a.deptno = b.deptno(+);
 //或写成如下方式也可:
 select a.empno, a.ename, a.deptno, b.empno, b.ename, b.deptno
 from AA a left outer join BB b on a.deptno = b.deptno;
 2    7369    SMITH    20    1000    SMITH    20
10    7369    SMITH    20    1003    JONES    20
 5    7499    ALLEN    30    1001    ALLEN    30
 8    7499    ALLEN    30    1002    WARD    30
 7    7521    WARD    30    1002    WARD    30
 4    7521    WARD    30    1001    ALLEN    30
 9    7566    JONES    20    1003    JONES    20
 1    7566    JONES    20    1000    SMITH    20
 6    7654    MARTIN    30    1002    WARD    30
 3    7654    MARTIN    30    1001    ALLEN    30

 (右外联接): 左边表的匹配行记录 + 右边表的所有记录
 select a.empno, a.ename, a.deptno, b.empno, b.ename, b.deptno from AA a, BB b
 where a.deptno(+) = b.deptno;
 //或写成如下方式也可:
 select a.empno, a.ename, a.deptno, b.empno, b.ename, b.deptno
 from AA a right outer join BB b on a.deptno = b.deptno;
 1                1004    MARTIN    10
 4    7369    SMITH    20    1003    JONES    20
 2    7369    SMITH    20    1000    SMITH    20
 6    7499    ALLEN    30    1001    ALLEN    30
 9    7499    ALLEN    30    1002    WARD    30
 8    7521    WARD    30    1001    ALLEN    30
11    7521    WARD    30    1002    WARD    30
 5    7566    JONES    20    1003    JONES    20
 3    7566    JONES    20    1000    SMITH    20
 7    7654    MARTIN    30    1001    ALLEN    30
10    7654    MARTIN    30    1002    WARD    30

2:集合运算符:用于将两个查询的结果组合成一个结果集(分为并集和交集以及差集三种)
 (并集):又分为两种: UNION / UNION ALL
 A: union(无重复行)
 
 B: union all(有重复行)

 (交集): INTERSECT

 (差集): MINUS

------------------------------------------------------------------------------------------

 

ORACLE数据库性能优化的几个部分


数据库性能优化包括如下几个部分：


1、1、调整数据结构的设计。这一部分在开发信息系统之前完成，程序员需要考虑是否使用ORACLE数据库的分区功能，对于经常访问的数据库表是否需要建立索引等。


2、2、调整应用程序结构设计。这一部分也是在开发信息系统之前完成，程序员在这一步需要考虑应用程序使用什么样的体系结构，是使用传统的Client/Server两层体系结构，还是使用Browser/Web/Database的三层体系结构。不同的应用程序体系结构要求的数据库资源是不同的。


3、3、调整数据库SQL语句。应用程序的执行最终将归结为数据库中的SQL语句执行，因此SQL语句的执行效率最终决定了ORACLE数据库的性能。ORACLE公司推荐使用ORACLE语句优化器（Oracle Optimizer）和行锁管理器（row-level manager）来调整优化SQL语句。


4、4、调整服务器内存分配。内存分配是在信息系统运行过程中优化配置的，数据库管理员可以根据数据库运行状况调整数据库系统全局区（SGA区）的数据缓冲区、日志缓冲区和共享池的大小；还可以调整程序全局区（PGA区）的大小。需要注意的是，SGA区不是越大越好，SGA区过大会占用操作系统使用的内存而引起虚拟内存的页面交换，这样反而会降低系统。


5、5、调整硬盘I/O，这一步是在信息系统开发之前完成的。数据库管理员可以将组成同一个表空间的数据文件放在不同的硬盘上，做到硬盘之间I/O负载均衡。


6、6、调整操作系统参数，例如：运行在UNIX操作系统上的ORACLE数据库，可以调整UNIX数据缓冲池的大小，每个进程所能使用的内存大小等参数。


 


实际上，上述数据库优化措施之间是相互联系的。ORACLE数据库性能恶化表现基本上都是用户响应时间比较长，需要用户长时间的等待。但性能恶化的原因却是多种多样的，有时是多个因素共同造成了性能恶化的结果，这就需要数据库管理员有比较全面的计算机知识，能够敏感地察觉到影响数据库性能的主要原因所在。另外，良好的数据库管理工具对于优化数据库性能也是很重要的。


ORACLE数据库性能优化工具


常用的数据库性能优化工具有：


1、1、ORACLE数据库在线数据字典，ORACLE在线数据字典能够反映出ORACLE动态运行情况，对于调整数据库性能是很有帮助的。


2、2、操作系统工具，例如UNIX操作系统的vmstat，iostat等命令可以查看到系统系统级内存和硬盘I/O的使用情况，这些工具对于管理员弄清出系统瓶颈出现在什么地方有时候很有用。


3、3、SQL语言跟踪工具（SQL TRACE FACILITY），SQL语言跟踪工具可以记录SQL语句的执行情况，管理员可以使用虚拟表来调整实例，使用SQL语句跟踪文件调整应用程序性能。SQL语言跟踪工具将结果输出成一个操作系统的文件，管理员可以使用TKPROF工具查看这些文件。


4、4、ORACLE Enterprise Manager（OEM），这是一个图形的用户管理界面，用户可以使用它方便地进行数据库管理而不必记住复杂的ORACLE数据库管理的命令。


5、5、EXPLAIN PLAN──SQL语言优化命令，使用这个命令可以帮助程序员写出高效的SQL语言。


ORACLE数据库的系统性能评估


信息系统的类型不同，需要关注的数据库参数也是不同的。数据库管理员需要根据自己的信息系统的类型着重考虑不同的数据库参数。


1、1、在线事务处理信息系统（OLTP），这种类型的信息系统一般需要有大量的Insert、Update操作，典型的系统包括民航机票发售系统、银行储蓄系统等。OLTP系统需要保证数据库的并发性、可靠性和最终用户的速度，这类系统使用的ORACLE数据库需要主要考虑下述参数：


l　　　　 l　　　　 数据库回滚段是否足够？


l　　　　 l　　　　 是否需要建立ORACLE数据库索引、聚集、散列？


l　　　　 l　　　　 系统全局区（SGA）大小是否足够？


l　　　　 l　　　　 SQL语句是否高效？


2、2、数据仓库系统（Data Warehousing），这种信息系统的主要任务是从ORACLE的海量数据中进行查询，得到数据之间的某些规律。数据库管理员需要为这种类型的ORACLE数据库着重考虑下述参数：


l　　　　 l　　　　 是否采用B*-索引或者bitmap索引？


l　　　　 l　　　　 是否采用并行SQL查询以提高查询效率？


l　　　　 l　　　　 是否采用PL/SQL函数编写存储过程？


l　　　　 l　　　　 有必要的话，需要建立并行数据库提高数据库的查询效率


SQL语句的调整原则


SQL语言是一种灵活的语言，相同的功能可以使用不同的语句来实现，但是语句的执行效率是很不相同的。程序员可以使用EXPLAIN PLAN语句来比较各种实现方案，并选出最优的实现方案。总得来讲，程序员写SQL语句需要满足考虑如下规则：


1、1、尽量使用索引。试比较下面两条SQL语句：


语句A：SELECT dname, deptno FROM dept WHERE deptno NOT IN  


(SELECT deptno FROM emp);


语句B：SELECT dname, deptno FROM dept WHERE NOT EXISTS


(SELECT deptno　FROM emp WHERE dept.deptno = emp.deptno);


这两条查询语句实现的结果是相同的，但是执行语句A的时候，ORACLE会对整个emp表进行扫描，没有使用建立在emp表上的deptno索引，执行语句B的时候，由于在子查询中使用了联合查询，ORACLE只是对emp表进行的部分数据扫描，并利用了deptno列的索引，所以语句B的效率要比语句A的效率高一些。


2、2、选择联合查询的联合次序。考虑下面的例子：


SELECT stuff　FROM taba a, tabb b, tabc c


WHERE a.acol between :alow and :ahigh


AND b.bcol between :blow and :bhigh


AND c.ccol between :clow and :chigh


AND a.key1 = b.key1


AMD a.key2 = c.key2;


这个SQL例子中，程序员首先需要选择要查询的主表，因为主表要进行整个表数据的扫描，所以主表应该数据量最小，所以例子中表A的acol列的范围应该比表B和表C相应列的范围小。


3、3、在子查询中慎重使用IN或者NOT IN语句，使用where (NOT) exists的效果要好的多。


4、4、慎重使用视图的联合查询，尤其是比较复杂的视图之间的联合查询。一般对视图的查询最好都分解为对数据表的直接查询效果要好一些。


5、5、可以在参数文件中设置SHARED_POOL_RESERVED_SIZE参数，这个参数在SGA共享池中保留一个连续的内存空间，连续的内存空间有益于存放大的SQL程序包。


6、6、ORACLE公司提供的DBMS_SHARED_POOL程序可以帮助程序员将某些经常使用的存储过程“钉”在SQL区中而不被换出内存，程序员对于经常使用并且占用内存很多的存储过程“钉”到内存中有利于提高最终用户的响应时间。


CPU参数的调整


CPU是服务器的一项重要资源，服务器良好的工作状态是在工作高峰时CPU的使用率在90％以上。如果空闲时间CPU使用率就在90％以上，说明服务器缺乏CPU资源，如果工作高峰时CPU使用率仍然很低，说明服务器CPU资源还比较富余。


使用操作相同命令可以看到CPU的使用情况，一般UNIX操作系统的服务器，可以使用sar –u命令查看CPU的使用率，NT操作系统的服务器，可以使用NT的性能管理器来查看CPU的使用率。


数据库管理员可以通过查看v$sysstat数据字典中“CPU used by this session”统计项得知ORACLE数据库使用的CPU时间，查看“OS User level CPU time”统计项得知操作系统用户态下的CPU时间，查看“OS System call CPU time”统计项得知操作系统系统态下的CPU时间，操作系统总的CPU时间就是用户态和系统态时间之和，如果ORACLE数据库使用的CPU时间占操作系统总的CPU时间90％以上，说明服务器CPU基本上被ORACLE数据库使用着，这是合理，反之，说明服务器CPU被其它程序占用过多，ORACLE数据库无法得到更多的CPU时间。


数据库管理员还可以通过查看v$sesstat数据字典来获得当前连接ORACLE数据库各个会话占用的CPU时间，从而得知什么会话耗用服务器CPU比较多。


出现CPU资源不足的情况是很多的：SQL语句的重解析、低效率的SQL语句、锁冲突都会引起CPU资源不足。


1、数据库管理员可以执行下述语句来查看SQL语句的解析情况：


SELECT * FROM V$SYSSTAT


WHERE NAME IN


('parse time cpu', 'parse time elapsed', 'parse count (hard)');


这里parse time cpu是系统服务时间，parse time elapsed是响应时间，用户等待时间


waite time = parse time elapsed – parse time cpu


由此可以得到用户SQL语句平均解析等待时间＝waite time / parse count。这个平均等待时间应该接近于0，如果平均解析等待时间过长，数据库管理员可以通过下述语句


SELECT SQL_TEXT, PARSE_CALLS, EXECUTIONS FROM V$SQLAREA


ORDER BY PARSE_CALLS;


来发现是什么SQL语句解析效率比较低。程序员可以优化这些语句，或者增加ORACLE参数SESSION_CACHED_CURSORS的值。


2、数据库管理员还可以通过下述语句：


SELECT BUFFER_GETS, EXECUTIONS, SQL_TEXT FROM V$SQLAREA;


查看低效率的SQL语句，优化这些语句也有助于提高CPU的利用率。


3、3、数据库管理员可以通过v$system_event数据字典中的“latch free”统计项查看ORACLE数据库的冲突情况，如果没有冲突的话，latch free查询出来没有结果。如果冲突太大的话，数据库管理员可以降低spin_count参数值，来消除高的CPU使用率。


内存参数的调整


内存参数的调整主要是指ORACLE数据库的系统全局区（SGA）的调整。SGA主要由三部分构成：共享池、数据缓冲区、日志缓冲区。


1、　　1、　  共享池由两部分构成：共享SQL区和数据字典缓冲区，共享SQL区是存放用户SQL命令的区域，数据字典缓冲区存放数据库运行的动态信息。数据库管理员通过执行下述语句：


select (sum(pins - reloads)) / sum(pins) "Lib Cache"  from　v$librarycache;


来查看共享SQL区的使用率。这个使用率应该在90％以上，否则需要增加共享池的大小。数据库管理员还可以执行下述语句：


select (sum(gets - getmisses - usage - fixed)) / sum(gets) "Row Cache"　from v$rowcache;


查看数据字典缓冲区的使用率，这个使用率也应该在90％以上，否则需要增加共享池的大小。


2、　　2、　  数据缓冲区。数据库管理员可以通过下述语句：


SELECT name, value　 FROM v$sysstat　 WHERE name IN ('db block gets', 'consistent gets','physical reads');


来查看数据库数据缓冲区的使用情况。查询出来的结果可以计算出来数据缓冲区的使用命中率＝1 - ( physical reads / (db block gets + consistent gets) )。


这个命中率应该在90％以上，否则需要增加数据缓冲区的大小。


3、　　3、　  日志缓冲区。数据库管理员可以通过执行下述语句：


select name,value from v$sysstat where name in ('redo entries','redo log space requests');查看日志缓冲区的使用情况。查询出的结果可以计算出日志缓冲区的申请失败率：

申请失败率＝requests/entries，申请失败率应该接近于0，否则说明日志缓冲区开设太小，需要增加ORACLE数据库的日志缓冲区。

------------------------------------------------------------------------------------------

create table bb
(
 no number(3,2)
);
select * from bb;
alter table bb add nn number;
alter table bb modify(no date);
alter table bb drop column nn;
insert into bb values(to_date('2006-05-01', 'yyyy-mm-dd'));
update aa set no=25.2;
select systimestamp from dual;
select rownum, ename from emp where rownum<11;
create table aa as select * from emp;

truncate table aa;
delete aa;
select * from aa;
delete from aa;
rollback;
insert into aa select * from emp;
select distinct job as job_name from emp
update aa set comm=100 where empno=7369;
savepoint a1;
update aa set comm=1000 where empno=7566;
rollback to savepoint a1;

select sysdate from dual;
select round(sysdate, 'day') from dual;
select next_day(sysdate, 3) from dual;
select trunc(sysdate, 'day') from dual;
select extract(hour from systimestamp) from dual;
select initcap('hello') from dual;
select rtrim('xyzadams', 'ams') from dual;
select instr('welcome', 'oe') from dual;
select replace('welcome', 'w', 'www') from dual;
select substr('welcome', 4) from dual;
select chr('A') from dual;
SELECT rpad(LPAD('welcome', 10, '-'), 13, '-') from dual;
select translate('abcde','ace','FNM') from dual

select to_char(sysdate, 'yyyy-mm-dd') from dual;
select to_date('2006-04-26', 'yyyy/mm/dd') from dual;
select sqrt('100') from dual;
select * from aa;
select nvl(comm, 0) from aa;
select nvl2(comm, 100, 0) from aa;
select nullif(deptno, 30) from aa;

select * from emp;
select count(distinct job) from emp;
select job, max(sal) from emp group by job having deptno=20;

select deptno, ename, job, row_number() over(partition by deptno order by sal) as sal_rank from emp;

select rownum, ename, job from emp order by sal;
select * from emp;

select deptno, ename, sal, rank() over(partition by deptno order by sal desc) as rank
from emp;

select d.deptno, e.ename, e.sal,
dense_rank() over(partition by e.deptno order by e.sal desc) denserank
from emp e, dept d
where e.deptno = d.deptno;
select u_name,
case
    when u_role='A' then '管理员'
    when u_role='P' then '高级用户'
    else '用户'
end case
from tb_user;
select u_name,
case u_role
    when 'A' then '管理员'
    when 'P' then '高级用户'
    else '用户'
end case
from tb_user;
select
case
    when u_role = 'A' then NULL
    else u_role
end case
from tb_user;

select NULLIF(u_role, 'A') from tb_user;

------------------------------------------------------------------------------------------

 数据定义功能包括：基表、视图和索引的定义。

一、创建与删除基表

二、创建与删除视图

三、索引的建立与删除

------------------------------------------------------------------------------------------

 在特定的表中搜索每一行是否唯一。
WHERE UNIQUE
        (SELECT salary
          FROM  employee_tbl
          WHERE employee_id = ‘3333’)
测试满足条件的工资是否有重复数据。

------------------------------------------------------------------------------------------

 1、从单独的表中创建视图
CREATE  VIEW  emp _ view  AS
            SELECT  emp_id,emp_name,phone
            FROM  employee_tbl


2、从多表中创建视图
CREATE  VIEW employee_summary AS
     SELECT     
        e.emp_id,e.name,p.position,p.pay_rate
     FROM  
        employee_tbl  e,employee_pay_tbl  p
     WHERE
        e.emp_id = p.emp_id

------------------------------------------------------------------------------------------

 --表已创建

create table vendor_master
(
   vencode varchar2(5) ,
   venname varchar2(20),
   venadd1 varchar2(20),
   venadd2 varchar2(20),
   venadd3 varchar2(20)
);

--表已更改
alter table vendor_master modify vencode varchar2(10);

--表结构描述
describe vendor_master;

--在表中添加新列
alter table vendor_master add venadd4 varchar2(20);

--在表中添加多个新列
alter table vendor_master add (venadd4 varchar2(20),venadd5 varchar(20));
    

--在表中删除新列
alter table vendor_master drop column venadd4;

--在表中删除多个列
alter table vendor_master drop (venadd4,venadd5);

--表已截掉
truncate table vendor_master;


--设置vendor_master表中venname字段不能为空
alter table vendor_master modify venname not null;


--设置vendor_master表中venname字段能为空
alter table vendor_master modify venname null;


--怎样给vendor_master表中vencode字段加上主键？
alter table vendor_master add constraint pk_vendor_master_vencode primary key(vencode);

alter table vendor_master drop constraint pk_vendor_master_vencode;

--查看用户自定义的约束
select * from user_constraints;
--查看某一个约束的详细信息
select * from user_constraints where constraint_name=upper('pk_vendor_master_vencode');
--查看某个人创建的约束
select * from user_constraints where owner=upper('scott');
--查看某个表的某个约束
select * from user_constraints where table_name=upper('vendor_master');
--添加唯一约束
alter table vendor_master add constraint uq_vendor_master_venname unique(venname);
--添加一个列
alter table vendor_master add age int;
--为一个列添加一个检查约束
alter table vendor_master add constraint ck_vendor_master_age check(age>10 and age<20);
-- 为某一个列添加一个默认约束
alter table vendor_master modify age default(15);
--创建外键
create table student
(
 stuid int primary key,
 stuname varchar2(20) unique,
 stuage int check(stuage>=10 and stuage<=30)
);

 create table borrow
 (
  borrowid int not null,
  stuid int,
  borrowdate date
 );

alter table borrow add constraint fk_borrow_stuid foreign key(stuid) references student(stuid);

alter table borrow add constraint fk_borrow_stuid foreign key(stuid) references student(stuid) on delete cascade;

alter table borrow drop constraint fk_borrow_stuid;

--利用现有表创建新表
create table emp2 as select * from emp;


alter session set nls_date_format='yyyy-mm-dd'  【National   Language   Support  nls  国际语言支持。 】

clear screen;

select   *   from   NLS_DATABASE_PARAMETERS;   
select   *   from   Nls_Instance_Parameters;   
select   *   from   NLS_SESSION_PARAMETERS;   


select sysdate from dual;

alter session set nls_date_language='AMERICAN';
alter session set nls_date_language='simplified chinese';
select date'2005-10-25' from dual;

--insert 插入日期数据是要注意格式匹配 如：
insert into borrow values(1,1,'19-3月-07');


--也可以按自己指定的格式插入日期 如：
insert into borrow values(2,2,to_date('2007-3-19','yyyy-mm-dd'));

--从其他表中选择数据插入到存在的emp3表中去。
insert into emp3 select * from emp

--左连接
select * from emp e,dept d where e.deptno=d.deptno(+)
select * from emp e left join dept d on e.deptno=d.deptno;
--右连接
select * from emp e,dept d where e.deptno(+)=d.deptno
select * from emp e right join dept d on e.deptno=d.deptno;

select * from emp e full join dept d on d.deptno=e.deptno;

--事务保存点
savepoint save1;
delete from emp3 where empno='7369';
savepoint save2;
delete from emp3 where empno='7499';
select * from emp3;
rollback to savepoint save2;
select * from emp3;
rollback to savepoint save1;
select * from emp3;



--数据控制语言
--创建用户
conn sys/quan as sysdba;
create user AAA
identified by AAA;

--更改用户的密码
 conn sys/quan as sysdba;
alter user AAA
identified by BBB;

--解除用户锁定状态
alter user AAA account unlock
--恢复用户锁定状态
alter user AAA account lock

grant create session,resource to AAA;

conn scott/tiger;
grant select on emp3 to AAA;

CONN AAA/AAA;

select * from scott.emp3;


--日期函数:(请把注释写出)
--给指定的日期加上月份
select hiredate,add_months(hiredate,2) from emp;

--给指定的日期减上月份
select hiredate,add_months(hiredate,-2) from emp;



--add_months(hiredate,2)
select hiredate,add_months(hiredate,2),months_between(add_months(hiredate,2),hiredate) from emp;

select hiredate,add_months(hiredate,2),months_between(hiredate,add_months(hiredate,2)) from emp;


select hiredate,last_day(hiredate) from emp;


select hiredate,round(hiredate,'year') year,round(hiredate,'month') month,round(hiredate,'day') day from emp;


select hiredate,next_day(hiredate,'星期一') as 下个星期一 from emp;

select sysdate,next_day(sysdate,'星期一') 星期一 from emp;


select hiredate,trunc(hiredate,'year') as year from emp;

select hiredate,trunc(hiredate,'month') as month from emp;

select hiredate,trunc(hiredate,'day') as day from emp;

select trunc(sysdate,'year') from emp;

select trunc(sysdate,'month') from dual;


select extract(year from sysdate) from dual;
select to_char(sysdate,'yy') from dual;

select extract(month from sysdate) from dual;
select to_char(sysdate,'mm') from dual;

select extract(day from sysdate) from dual;
select to_char(sysdate,'dd') from dual;
select to_char(sysdate,'HH24') from dual;

select     extract(hour from timestamp '2005-10-10 10:11:12') from dual;
select to_char(sysdate,'hh') from dual;

select extract(minute from timestamp '2005-10-11 10:11:12') from dual;
select to_char(sysdate,'mi') from dual;

select extract(second from timestamp '2005-10-11 10:11:12') from dual;
select to_char(sysdate,'ss') from dual;


--字符函数
select initcap('hello') from dual;
select initcap('how are you') from dual;

select lower('HOW ARE YOU') from dual;

select upper('how are you') from dual;

select ltrim('    how are you   ') from dual;
select ltrim('xyzadams','xyz') from dual;

select rtrim('xyzadams','ams') from dual;

select rtrim('     xyzadams','ams') from dual;

--这个函数是:用第三个参数按位置代替第二个参数,然后用代替好的字符去取代第一个参数相对应的字符
select translate('jack','abcd','1234') from dual;
select translate('abc','ac','fn') from dual;


select replace('jack and jue','j','bl') from dual;

select instr('worldwide','d') from dual;
select instr('how are you','o') from dual;
--从指定的位置开始查找
select instr('how are you','o',3) from dual;

select substr('abcdefg',3,2) from dual;

select concat('hello',' world') from dual;


--一些其他字符函数:
select chr(65) from dual;

select lpad('how are you',20,'*') from dual;
select lpad('how',3,'*') from dual

select rpad('how are you',20,'&') from dual;


select trim('   how are you      ') from dual;
--去掉前面的9
select trim(leading 9 from 999978888876699999) from dual;

select trim(leading 'a' from 'aaaaaadgereraafdfeghrtraaaaaaaaaaaa') from dual;

--去掉后面的9
select trim(trailing 9 from 999978889899876699999) from dual

select trim(trailing 'a' from 'aaaaaadgereraafdfeghrtraaaaaaaaaaaa') from dual;

--不用leading也不用trailing的话就把前后的9都去掉
select trim(9 from 999877699999) from dual;
select trim('a' from 'aaaaaeeeaaabbbeertaaaaa') from dual;

--区分大小写
select trim('a' from 'AAAAAAAAbbbbbaderAAAAa') from dual;


select length('aaaaaaaaaaa') from dual;


--返回字符串所占空间的字节数
select vsize('abcde') from dual;


select empno,deptno,decode(deptno,20,'ccc',10,'aaa') from emp;



--数学函数:
select abs(-9) from dual;
select abs(9) from dual;


select ceil(44.99) from dual;
select ceil(44.11) from dual;


select ceil(-44.99) from dual;
select ceil(-44.11) from dual;


select floor(44.11) from dual;
select floor(44.99) from dual;

select floor(-44.11) from dual;
select floor(-44.99) from dual;


select power(2,3) from dual;
select power(3.3,3) from dual;
select power(3.3,3.2) from dual;

select round(100.23456,3) from dual;

select trunc(100.2345,3) from dual;

select sqrt(9) from dual;



--转换函数
--使用fm可以避免空格和数字0填充
select to_char(sysdate,'yyyy"年"fmMM"月"fmDD"日" HH24:MI:SS') FROM DUAL;
--24小时算法
select to_char(sysdate,'yyyy"年"MM"月"DD"日" HH24:MI:SS') FROM DUAL;
--12小时算法
select to_char(sysdate,'yyyy"年"MM"月"DD"日" HH:MI:SS') FROM DUAL;

select to_char(sal,'C999999') from emp;
select to_char(sal,'u999999') from emp;
select to_char(sal,'u0000000000') from emp;


select to_date('2005-12-06','yyyy-mm-dd') from dual;
select to_date('12-06-2005','dd-mm-yyyy') from dual;
select to_date('12-06-2005','mm-dd-yyyy') from dual;
select to_date('2005/12/06','yyyy/mm/dd') from dual;


select to_number('345678') from dual;



--其他函数：

select comm,nvl(comm,0) from emp;

select comm,nvl2(comm,comm,0) from emp;
select comm,nvl2(comm,1111,0000) from emp;


select nullif(12,12) from dual;
select nullif(12,23) from dual;
select nullif(23,12) from dual;
NULL指的是空值，或者非法值。
NVL (expr1, expr2)->expr1为NULL，返回expr2；不为NULL，返回expr1。注意两者的类型要一致
NVL2 (expr1, expr2, expr3) ->expr1不为NULL，返回expr2；为NULL，返回expr3。expr2和expr3类型不同的话，expr3会转换为expr2的类型
NULLIF (expr1, expr2) ->相等返回NULL，不等返回expr1


--集合操作符:
    --测试表
    create table test1
    (
         testid varchar2(20)
    );
    insert into test1 values('a001');
    insert into test1 values('a002');
    insert into test1 values('a003');
    insert into test1 values('a004');
    insert into test1 values('a005');

    create table test2
    (
         testno varchar2(20)
    );
    insert into test2 values('a004');
    insert into test2 values('a005');
    insert into test2 values('a006');
    insert into test2 values('a007');
    insert into test2 values('a008');
    insert into test2 values('a009');    

    select testid from test1
    union
    select testno from test2;


    select testid from test1
    union all
    select testno from test2;

    select testid from test1
    intersect
    select testno from test2;

    select testid from test1
    minus
    select testno from test2;


--分析函数:
select ename,job,deptno,sal,row_number() over(order by sal desc) as sal_rank from emp;
select ename,job,deptno,sal,row_number() over(order by sal asc)as sal_rank from emp;
select ename,job,deptno,sal,row_number() over(partition by deptno order by sal desc) as sal_rank from emp;
select ename,job,deptno,sal,comm,row_number() over(partition by deptno order by sal desc,comm) from emp;

select ename,job,deptno,sal,comm,row_number() over(partition by deptno order by sal desc) from emp;
select ename,job,deptno,sal,comm,row_number() over(partition by deptno order by sal desc,comm desc) from emp;

select empno,ename,job,sal,rank() over(order by sal desc) from emp;

select empno,ename,job,sal,comm,deptno,rank() over(partition by deptno order by sal desc) from emp;
select empno,ename,job,sal,comm,deptno,rank() over(partition by deptno order by sal desc,comm) from emp;
select empno,ename,job,sal,comm,deptno,rank() over(partition by deptno order by sal desc,comm desc) from emp;

select empno,ename,job,sal,comm,deptno,dense_rank() over(order by sal) from emp;
select empno,ename,job,sal,comm,deptno,dense_rank() over(partition by deptno order by sal desc) from emp;
select empno,ename,job,sal,comm,deptno,dense_rank() over(partition by deptno order by sal desc,comm asc) from emp;


select empno,ename,sal,comm,e.deptno,dname,dense_rank() over(partition by e.deptno order by sal
) denserank from emp e,dept d where e.deptno=d.deptno;


select deptno,sum(sal) from emp group by rollup(deptno);
select deptno,job,sum(sal) from emp group by rollup(deptno,job);

select deptno,sum(sal) from emp group by cube(deptno);
select deptno,job,sum(sal) from emp group by cube(deptno,job);




--rollup函数的使用
    --示例表是emp;
    select to_char(deptno),sum(sal) sumsal from emp group by deptno
    union all
    select '合计' as deptno,sum(sal) from emp;

    
    --rollup的使用
    select deptno,sum(sal) from emp group by rollup(deptno);

    select nvl(to_char(deptno),'合计') deptno,sum(sal) sumsal from emp group by rollup(deptno);

    select deptno,job,sum(sal) sumsal from emp group by rollup(deptno,job);

    --系统只是根据rollup的第一个参数deptno对结果集的数据做了汇总处理,
    --而没有对job做汇总分析处理,cube函数就是为了这个而设计的.
    
    select nvl(to_char(deptno),'总合计') deptno,nvl(to_char(job),'合计') job,sum(sal) sumsal from emp
    group by rollup(deptno,job);

    select job,sum(sal) from emp group by job;
    

    --cube的使用
    --可以看到,在cube函数的输出结果比使用rollup多出了几行统计数据.
    --这就是cube函数根据job做的汇总统计结果
    select nvl(to_char(deptno),'总合计') deptno,nvl(to_char(job),'合计') job,sum(sal) sumsal from emp
    group by cube(deptno,job);

    
    --从上面的结果中我们很容易发现,每个统计数据所对应的行都会出现null,
    --我们如何来区分到底是根据那个字段做的汇总呢,
    --这时候,oracle的grouping函数就粉墨登场了.
    --如果当前的汇总记录是利用该字段得出的,grouping函数就会返回1,否则返回0

    select decode(grouping(deptno),1,'all deptno',0,to_char(deptno)) deptno,
    decode(grouping(job),1,'all job',0,to_char(job)) job,sum(sal) sumsal
    from emp group by cube(deptno,job) order by deptno,job nulls last;

    

    select mgr from emp order by mgr nulls last;
    select mgr from emp order by mgr nulls first;

------------------------------------------------------------------------------------------

 
    
    
    1、请从emp表中查询出第三条记录之后的所有记录？
    


    2、请从emp表中查询价格大于sal>1200的前五条记录（注：不需要对sal字段排序查询）
    
    


    3、请从emp表中查询价格大于sal>1200的前五条记录（注：需要对sal字段排序查询后再查询前五条）
    
    


    4、请从emp表中查询价格大于sal>1200的后五条记录（注：不需要对sal字段排序查询）
    
    


    5、请从emp表中查询价格大于sal>1200的前五条记录（注：需要对sal字段排序查询后再查询前五条）
    


    6、要找出一个emp表中按sal从大到小排序的前10条纪录,假如有2条纪录的数量相同,那么就显示11条纪录.
    


    
    
    

    7、从emp表中查询第八条到第十条记录(请用两种以上的方法来作)
    第一解题思路：
    

    第二解题思路：
    


    
    第三解题思路：
    


    第四解题思路：

------------------------------------------------------------------------------------------

 1.列出至少有一个雇员的所有部门


2.列出薪金比'SMITH'多的所有雇员.


3.列出所有雇员的姓名及其上级的姓名.


4.列出入职日期早于其直接上级的所有雇员.


5.列出部门名称和这些部门的雇员,同时列出那些没有雇员的部门


6.列出所有'CLERK'的姓名及其部门名称.


7.列出各种类别的最低薪金，要求最低薪金大于1500


8.列出各种类别"SALES"工作的雇员的姓名，假定不知道
 销售部的部门编号.


9.列出薪金高于公司平均水平的所有雇员.


10.列出与"SCOTT"从事相同工作的所有雇员.

------------------------------------------------------------------------------------------

 函数通常是列名或表达式相连系的命令。

    1、统计函数
    2、字符函数
    3、算术函数

------------------------------------------------------------------------------------------

 IS NULL（是否为空）

BETWEEN（在某两个值之间）

IN（一系列值中）

LIKE（相似值的比较）

EXITS（是否存在符合条件的数据）

UNIQUE（是否唯一）

ALL/ANY（一组数据的所有/其中的任何一个）

AND/OR（逻辑与/或）

------------------------------------------------------------------------------------------

SQL：Structured query language
功能：查询、操纵、定义、控制
特点：  1、一体化；
    2、两种使用方式，统一的语法结构；
        方式一、联机交互使用方式；
        方式二、嵌入式。
    3、高度非过程化；
    4、语言简洁，易学易用

------------------------------------------------------------------------------------------

    用户管理数据库的事务命令：

COMMIT用于保护数据库的事务

ROLLBACK用于撤消数据库的事务

SAVEPOINT创建一组事务中的撤消点

SET TRANSACTION给事务命名

------------------------------------------------------------------------------------------

 视图对于数据库的重构造提供了一定程度的逻辑独立性。

简化了用户观点。

视图使不同的用户能以不同的方式看待同一数据。

视图对机密数据提供了自动的安全保护功能

------------------------------------------------------------------------------------------

 例4:把所有用户查询SC表权限收回.
REVOKE SELECT ON TABLE SC FROM PUBLIC;


例5:把用户U1修改student表的sno字段的权限收回.
REVOKE UPDATE(sno) ON TABLE student FROM U1;

------------------------------------------------------------------------------------------

 授权：将对指定操作对象的指定操作权限授予指定的用户

例1:把查询student表权限授给用户U1.
GRANT SELECT ON TABLE student TO U1;

例2:把查询sc表权限授给所有用户.
GRANT SELECT ON TABLE SC TO PUBLIC;

例3:把对表SC的INSERT权限授给用户U2,并允许他转授.
GRANT INSERT ON TABLE SC
               TO U2 WITH GRANT OPTION;
GRANT INSERT ON TABLE SC TO U3

------------------------------------------------------------------------------------------

 语法：
SELECT [ * | all | column1,column2 ]
    FROM  table1 [ ,table2 ]
    [ WHERE condition ]
    [ GROUP BY column1 ]
    [ ORDER BY column2(asc,desc) ]


例1：SELECT * FROM products_tbl     WHERE  cost < 5

例2：SELECT prod_desc,cost FROM products_tbl WHERE  prod_id = ‘119’

例3：多表查询
SELECT  employee_tbl.emp_name,     employee_pay_tbl.position
            FROM employee_tbl, employee_pay_tbl
        WHERE  employee_tbl.emp_id = employee_pay_tbl.emp_id

------------------------------------------------------------------------------------------

 审计和分析对数据库的操作，并可分析系统的执行过程

命令有：
    START AUDIT
    STOP AUDIT

------------------------------------------------------------------------------------------

 数据是存储在数据库中的信息集合。数据类型用于给特定的数据提供规则。

基本数据类型包括：
    字符串
    数字
    日期和时间值

------------------------------------------------------------------------------------------

 数据控制包括：数据的安全性控制
         完整性控制
         并发控制和恢复

------------------------------------------------------------------------------------------

 数据库中的表

数据和基本数据类型

------------------------------------------------------------------------------------------

 ABS（绝对值）、SIN、COS、TAN、
    EXP（指数）、SQRT（开方）、
    POWER（幂）

语法：函数名（表达式）

例：SELECT  SQRT(MATHS) * 10
          FROM  STUDENT_TBL

------------------------------------------------------------------------------------------

 统计函数：用于提供统计信息，如：累加、总和、平均值。

主要的统计函数：
COUNT：统计行数或不为NULL的列数。

SUM：求和。

MAX：求最大值。

MIN：求最小值。

AVG：求平均值。

------------------------------------------------------------------------------------------

 定长字符串：
        CHARACTER（n）
变长字符串：
        CHARACTER VARYING（n）

------------------------------------------------------------------------------------------

    UPPER（character string）：将指定字符串中的小写字母换成大写字母。

LOWER （character string）： 将指定字符串中的大写字母换成小写字母。

SUBSTRING （colunm name,startposition,length） 从指定字符串中取字符

------------------------------------------------------------------------------------------

   to_char()函数的格式：
---------------------------------
符号    说明
---------------------------------
9       每个9代表结果中的一位数字
0       代表要显示的先导0
＄      美元符号打印在数的左边
L       任意的当地货币符号
C    任意的当地货币符号
.       打印十进制的小数点
,       打印代表千分位的逗号
---------------------------------
例：
1.
select to_number('123.45')+to_number('234.56') form test;
to_number('123.45')+to_number('234.56')
----------------------------------------
                               358.01
2.
select to_char(987654321) from test;
to_char(987654321)
------------------
987654321

3.
select to_char(123,'＄9,999,999') a,to_char(54321,'＄9,999,999') b,to_char(9874321,'＄9,999,999') c from test;
a       b          c
------- ---------- -----------
＄123    ＄54,321    ＄9,874,321

4.
select to_char(1234.1234,'999,999.999') a,to_char(0.4567,'999,999.999') b,to_char(1.1,'999,999.999') from test;
a         b          c
--------- ---------- ------------
1,234.123 .457       1.100

日期函数的日期格式[fmt]：
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
格式代码　    说明   举例或可取值的范围
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
DD            该月某一天  1－31
DY 　　　     三个大写字母表示的周几 SUN，...SAT
DAY 　　　    完整的周几，大写英文 SUNDAY，...SATURDAY
MM            月份   1－12
MON　　　　　 三个大写字母表示的月份 JAN，...DEC
MONTH         完整   JANUARY,...DECEMBER
RM            月份的罗马数字  I,...XII
YY或YYYY      两位，四位数字年
HH:MI:SS　　　时：分：秒
HH12或HH24　　以12小时或24小时显示
MI　　　　　　分
SS　　　　　　秒
AM或PM　　　　上下午指示符
SP　　　　　　后缀SP要求拼写出任何数值字段
TH　　　　　　后缀TH表示添加的数字是序数　4th,1st
FM            前缀对月或日或年值，禁止填充 

------------------------------------------------------------------------------------------

 1: 查找EMP表中前5条记录
select * from emp where rownum<=5;

2: 查找EMP表中10条以后的记录
select * from (select e.*, rownum rn from emp) where rn > 10;

3: 查找EMP表中薪水第5高的员工
select * from
(select empno, ename, sal, row_number() over(order by sal desc) rn from emp)
where rn = 5;

4: 查找EMP表部门30中薪水第3的员工
select * from
(select e.*, row_number() over(order by sal desc) rn from emp e where deptno=30)
where rn = 3;

5: 查找EMP表中每部门薪水第3的员工
select * from
(select e.*, row_number() over(partition by deptno order by sal desc) rn from emp e)
where rn = 3;

6: 统计各部门的薪水总和.
select deptno, sal, sum(sal) from emp
group by rollup(deptno, sal);