oracle

第一课：客户端

        1. sql plus(客户端），命令行直接输入：sqlplus，然后按提示输入用户名，密码。

        2. 从开始程序运行:sqlplus，是图形版的sqlplus.

        3. [url]http://localhost:5560/isqlplus[/url]

        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 ,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 '$'比如:select ename from emp where ename like '%$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;

        显示:

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

        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

       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

       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<e2.sal));

       e.平均薪水最高的部门编号

       select deptno,avg_sal from

       (select avg(sal)avg_sal,deptno from emp group by deptno)

       where avg_sal=

       (select max(avg_sal)from

       (select avg(sal) avg_sal,deptno from emp group by deptno)

       )

       f.平均薪水最高的部门名称

       select dname from dept where deptno=

      (

        select deptno from

        (select avg(sal)avg_sal,deptno from emp group by deptno)

        where avg_sal=

        (select max(avg_sal)from

        (select avg(sal) avg_sal,deptno from emp group by deptno)

        )

      g.求平均薪水的等级最低的部门的部门名称

        组函数嵌套

        如：平均薪水最高的部门编号，可以e.更简单的方法如下：

        select deptno,avg_sal from

        where avg_sal =

        (select max(avg(sal)) from emp group by deptno)

        组函数最多嵌套两层

        分析：

        首先求

        1.平均薪水： select avg(sal) from group by deptno;

        2.平均薪水等级：  把平均薪水当做一张表，需要和另外一张表连接salgrade

        select  deptno,grade avg_sal 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)

        上面结果又可当成一张表。

        deptno    grade    avg_sal

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

        30           3   1566.66667

        20           4   2175

        10           4   2916.66667

        3.求上表平均等级最低值

        select min(grade) from

        (

          select deptno,grade,avg_sal 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.hisa)

         )

        4.把最低值对应的2结果的那张表的对应那张表的deptno, 然后把2对应的表和另外一张表做连接。

          select dname ,deptno,grade,avg_sal from

            (

         select deptno,grade,avg_sal 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)

             ) t1

            join dept on (t1.deptno = dept.deptno)

            where t1.grade =

              select deptno,grade,avg_sal 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)

               )

            )

         结果如下：

        dname    deptno     grade    avg_sal

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

        sales        30        3    1566.6667

       h: 视图（视图就是一张表，一个字查询）

       g中语句有重复，可以用视图来简化。

       conn sys/bjsxt as sysdba;

       grant create table,create view to scott;

       conn scott/tiger

       创建视图：

       create view v$_dept_avg-sal_info as

       select deptno,grade,avg_sal from

        ( select deptno,avg(sal) avg_sal from emp group by deptno)t

       join salgrade s on 9t.avg_sal between s.losal and s.hisal)

       然后

       select * from v$_dept_avg-sal_info

       结果如下：

       deptno      grade    avg_sal

       然后g中查询可以简化成：

       select  dname,t1.deptno,grade,avg_sal from

       v$_dept_avg-sal_info t1

       join dept on9t1.deptno =dept.deptno)

       where t1.grade=

       (

 select min(grade) from v$_dept_avg-sal_info t1