Oracle基础知识点

Oracle基础知识点。

第一课：客户端
1. Sql Plus(客户端），命令行直接输入：sqlplus，然后按提示输入用户名，密码。
2. 从开始程序运行:sqlplus，是图形版的sqlplus.
3. https://localhost:5560/isqlplus

    Toad：管理， PlSql Developer:

第二课：更改用户
1. sqlplus sys/bjsxt as sysdba
2. alter user scott account unlock;(解锁)

第三课：table structure

    1. 描述某一张表：desc 表名
    2. select * from 表名

第四课：select 语句：

   1.计算数据可以用空表：比如：.select 2*3 from dual

   2.select ename,sal*12 annual_sal from emp;与select ename,sal*12 "annual sal" from emp;区别，加双引号保持原大小写。不加全变大写。

   3. select ename || "abcd" 如果连接字符串中含有单引号，用两个单引号代替一个单引号。

第五课：distinct
select deptno from emp;
select distinct deptno from emp;

    select distinct deptno from emp;
    select distinct deptno ,job from emp
    去掉deptno,job两者组合的重复。更多的项，就是这么多项的组合的不重复组合。

第六课：Where
select * from emp where deptno =10;
select * from emp where deptno <>10;不等于10
select * from emp where ename =’bike’;
select ename,sal from emp where sal between 800 and 1500 (>=800 and <=1500)
空值处理:
select ename,sal,comm from emp where comm is (not) null;
select ename,sal,comm from emp where ename ( not)in (‘smith’,’king’,’abc’);
select ename from emp where ename like ‘A%’;代表一个字母,%代表0个或多个字母. 如果查询%
可用转义字符.\%. 还可以用escape ‘′比如:selectenamefromempwhereenamelike′a%’ escape ‘$’;

第七课: orderby

     select * from dept; 
     select * from dept order by dept desc;(默认:asc)
     select ename,sal,deptno from emp order by deptno asc,ename desc;

第八课: sql function1:
select ename,sal*12 annual_sal from emp
where ename not like ‘_A%’ and sal>800
order by sal desc;

    select lower(ename) from emp;

    select ename from emp 
    where lower(ename) like '_a%';等同于
    select ename from emp where ename like '_a%' or ename like '_A%';

    select substr(ename,2,3) from emp;从第二字符截,一共截三个字符.
    select chr(65) from dual 结果为:A
    select ascii('a') from dual 结果为:65
    select round(23.652,1) from dual; 结果为: 23.7
    select round(23.652,-1) from dual; 20

    select to_char(sal,'$99_999_999') from emp;
    select to_char(sal,'L99_999_999') from emp;人民币符号,L:代表本地符号

    这个需要掌握牢:
    select birthdate from emp;
    显示为:
    BIRTHDATE
    ----------------
    17-12月-80
    ----------------

    改为:
    select to_char(birthdate,'YYYY-MM-DD HH:MI:SS') from emp;

    显示:

    BIRTHDATE
    -------------------
    1980-12-17 12:00:00
    -------------------

    select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual; //也可以改为:HH12
    TO_CHAR(SYSDATE,'YY
    -------------------
    2007-02-25 14:46:14


    to_date函数:
    select ename,birthdate from emp where birthdate > to_date('1981-2-20 12:34:56','YYYY-MM-DD HH24:MI:SS');
    如果直接写 birthdate>'1981-2-20 12:34:56'会出现格式不匹配,因为表中的格式为: DD-MM月-YY.


    select sal from emp where sal>888.88 无错.但
    select sal from emp where sal>$1,250,00;
    会出现无效字符错误. 
    改为:
    select sal from emp where sal>to_number('$1.250.00','$9,999,99');

    把空值改为0
    select ename,sal*12+nvl(comm,0) from emp;
    这样可以防止comm为空时,sal*12相加也为空的情况.

第九课: Group function 组函数
max,min,avg ,count,sum函数

    select to_char(avg(sal),'99999999,99') from emp;


    select round(avg(sal),2) from emp;
    结果:2073.21

    select count(*) from emp where deptno=10;
    select count(ename) from emp where deptno=10; count某个字段,如果这个字段不为空就算一个.
    select count(distinct deptno) from emp;
    select sum(sal) from emp;

第十课: Group by语句

    需求:现在想求,求每个部门的平均薪水.
    select avg(sal) from emp group by deptno;
    select deptno avg(sal) from emp group by deptno;

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

   求薪水值最高的人的名字.
   select ename,max(sal) from emp;出错,因为max只有一个值,但等于max值的人可能好几个,不能匹配.
   应如下求:
   select ename from emp where sal=(select max(sal) from emp);

   Group by语句应注意,

   出现在select中的字段,如果没出现在组函数中,必须出现在Group by语句中.

第十一课: Having 对分组结果筛选

   Where是对单条纪录进行筛选,Having是对分组结果进行筛选.

   select avg(sal),deptno from emp 
   group by deptno 
   having avg(sal)>2000;

   查询工资大于1200雇员,按部门编号进行分组,分组后平均薪水大于1500,按工薪倒充排列.
   select * from emp 
   where sal>1200
   group by deptno
   having avg(sal)>1500
   order by avg(sal) desc;

第十二课:字查询

   谁挣的钱最多(谁:这个人的名字,  钱最多)

   select 语句中嵌套select 语句,可以在where,from后.


   问那些人工资,在平均工资之上.

   select ename,sal from emp where sal>(select avg(sal) from emp);


   查找每个部门挣钱最多的那个人的名字.
   select ename ,deptno from emp where sal in(select max(sal) from ename group by deptno) 查询会多值.

   应该如下:

   select  max(sal),deptno from emp group by deptno;当成一个表.语句如下:
   select ename, sal from emp join(select  max(sal) max_sal,deptno from emp group
   by deptno) t on(emp.sal=t.max_sal and emp.deptno=t.deptno); 

   每个部门的平均薪水的等级. 
   分析:首先求平均薪水(当成表),把平均薪水和另外一张表连接.

第十四课:self_table_connection

   把某个人的名字以及他的经理人的名字求出来(经理人及这个人在表中同处一行)

   分析:首先求出这个人的名字,取他的编号,然后从另一张表与其相对应编号,然后找到经理的名字.

   select e1.ename ,e2.ename from emp e1,emp e2 where e1.mgr= e2.empno.

   empno编号和MGR都是编号.

第十15课: SQL1999_table_connections

  select ename,dname,grade from emp e,dept d, sqlgrade s
  where e.deptno = d.deptno and e.sql between s.losal and s.hisal and
  job<>'CLERK';

  有没有办法把过滤条件和连接条件分开来? 出于这样考虑,Sql1999标准推出来了.有许多人用的还是
  旧的语法,所以得看懂这种语句.



  select ename,dname from emp,dept;(旧标准).
  select ename,dname from emp cross join dept;(1999标准)

  select ename,dname from emp,dept where emp.deptno=dept.deptno (旧) 
  select ename,dname from emp join dept on(emp.deptno = dept.deptno); 1999标准.没有Where语句.
  select ename,dname from emp join dept using(deptno);等同上句,但不推荐使用.

  select ename,grade from emp e join salgrade s on(e.sal between s.losal and s.hisal);
  join 连接语句, on过滤条件。连接，条件一眼分开。如果用Where语句较长时，连接语句和过滤语句混在一起。

  三张表连接：
  slect ename,dname, grade from 
  emp e join dept d on(e.deptno=d.deptno)
  join salgrade s on(e.sal between s.losal and s.hisal)
  where ename not like '_A%';
  把每张表连接 条件不混在一起，然后数据过滤条件全部区分开来。读起来更清晰，更容易懂一点。

  select e1.ename,e2.ename from emp e1 join emp e2 on(e1.mgr = e2.emptno);

  左外连接：会把左边这张表多余数据显示出来。
  select e1.ename,e2,ename from emp e1 left join emp e2 on(e1.mgr =e2.empno);left 后可加outer
  右外连接：
select ename,dname from emp e right outer join dept d on(e.deptno =d.deptno); outer可以取掉。

  即把左边多余数据，也把右边多余数据拿出来，全外连接。
  select ename,dname from emp e full join dept d on(e.deptno =d.deptno); 

16-23 课：求部门平均薪水的等级

   A.求部门平均薪水的等级。

   select deptno,avg_sal,grade from 
   (select deptno,avg(sal) avg_sal from emp group by deptno)t
   join salgrade s on(t.avg_sal between s.losal and s.hisal)

   B.求部门平均的薪水等级
   select deptno,avg(grade) from 
   (select deptno,ename, grade from emp join salgrade s on(emp.sal between s.losal and
   s.hisal)) t
   group by deptno

   C.那些人是经理
   select ename from emp where empno in(select mgr from emp);
   select ename from emp where empno in(select distinct mgr from emp);

   D.不准用组函数，求薪水的最高值（面试题）

   select distinct sal from emp where sal not in(
   select distinct e1.sal from emp e1 join emp e2 on (e1.sal