ITPub博客

首页 > 数据库 > Oracle > 马士兵Oracle 上

马士兵Oracle 上

Oracle 作者:eastmaui 时间:2013-12-10 17:39:43 0 删除 编辑

第一课:客户端
        1. Sql Plus(客户端),号令行间接输进:sqlplus,然后按提示输进用户名,密码。
        2. 从开端措施运转:sqlplus,是图形版的sqlplus.
        3. http://localhost:5560/isqlplus>
       
        Toad:经管, PlSql Developer:


第二课:变更用户
        1. sqlplus sys/密码 as sysdba
        2. alter user scott account unlock;(解锁账号)

第三课:table structure
       (系统自带的表有empsalgradedeptbonusdual)
        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");

 

模糊查询 like 

%代表随便任性数目标随便任性字符

_代表一个随便任性字符


        select ename from emp where ename like "_A%";

假设要查询露有%的,要用转义字符 

转义字符可以或许自界说: escape "自界说的转义字符" 比如:

select ename from emp where ename like "%$a%" escape "$";

第七课order by
       
         select from dept;

默许按升序(asc)摆列,要按降序(desc)用如下语句:
         select from dept order by dept desc;

         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,"$99999999") from emp;(用9即可以或许正在出有数字的中心没有隐示,假设用0的话一定会用0挖布满)
        select to_char(sal,"L99_999_999") from emp;  L:代表本地符号

        阿谁需供把握牢:
        select hiredate from emp;
        隐示为:
        BIRTHDATE
        ----------------
        17-12-80
        ----------------

        改成:
        select to_char(hiredate,"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,hiredate from emp where hiredate 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为空的中心用0取代,那样可以或许防止comm为空时,sal*12相加也为空的状态.


第九课Group function 组函数(即从多行中得到一个输出)

切记组函数:max(), min(), avg(), sum(), count()
       
        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语句

注重:count() 是计数没有是空值的数目


        需供:现正在想供每个部分的仄均薪水.
        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 avg(sal) 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) on (emp.sal=t.max_sal and emp.deptno=t.deptno);
      
       每个部分的仄均薪水的等第.
       解析:尾先供仄均薪水(当做表),把仄均薪水战另外一张表连接.
      
      
第十四课:immolation_table_connection
      
       把某小我的名字以及他的经理人的名字供出往(经理人及那小我正在表中同处一行)
      
       解析:尾先供出那小我的名字,取他的编号,然后从另外一张表取其相对应编号,然后找到经理的名字.
      
       select e1.ename ,e2.ename from emp e1,emp e2 where e1.mgr= e2.empno.
      
       empno编号战MGR都是编号.


第十15SQL1999_table_connections   
        
select ename, dname,grade from emp e,dept d, salgrade s

where e.deptno d.deptno and e.sal 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 join salgrade on(e.sal between s.losal and s.hisal);
      join 连接语句on过滤前提。连接,前提一眼合并。假设用Where语句较长时,连接语句战过滤语句混正在一同。
     
      三张表连接:
      slect ename,dname, grade from
      emp join dept on(e.deptno=d.deptno)
      join salgrade 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 right outer join dept on(e.deptno =d.deptno); outer可以或许取掉踪。
       
      全外连接: 即把左侧过剩数据,也把左侧过剩数据拿出往, 
      select ename,dname from emp full join dept on(e.deptno =d.deptno);

PS:所谓的“外”连接,即把过剩的数据隐示出往。Outer要害字可以或许省略

 


16-23 

供部分中哪些人的薪水最高:

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)

 

       A.供部分仄均薪水的等第。

       select deptno,avg_sal,grade from
       (select deptno,avg(sal) avg_sal from emp group by deptno)t
       join salgrade on(t.avg_sal between s.losal and s.hisal)
      
       B. 供部分仄均薪水的等第
      select deptno, avg(grade) from

(select deptno, ename, grade from emp join salgrade 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
      
       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)
        )
       )
     

       
        组函数嵌套
        如:仄均薪水最高的部分编号,可以或许用更简单的圆法如下:
        select deptno,avg_sal from
        (select avg(sal) avg_sal,deptno from emp group by deptno)
        where avg_sal =
        (select max(avg(sal)) from emp group by deptno)
       
        组函数最多嵌套两层

 

      G.供仄均薪水的等第最低的部分的部分名称


        解析:
        尾先供
        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 on(t.avg_sal between s.losal and s.hisal)
       
        上里成效又可当做一张表。
       
        DEPTNO    GRADE    AVG_SAL
      --------  -------  ----------
        30             1566.66667
        20             2175
        10             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 on(t.avg_sal between s.losal and s.hisa)
         )

        4.把最低值对应的2成效的那张表的对应那张表的deptno, 然后把2对应的表战另外一张表做连接。
         
select dname ,t1.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 on(t.avg_sal between s.losal and s.hisal)

 t1

join dept on (t1.deptno dept.deptno)

where t1.grade =

(

select min(grade) from

        select deptno,grade,avg_sal from

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

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

)
         成效如下:
        
       DNAME  DEPTNO  GRADE  AVG_SAL
      --------    -------    --------    --------
       SALES        30               1566.6667
    
        
       H: 视图(视图便是一张表,一个子查询)
       
       G中语句有重复,可以或许用视图往简化。

【默许scott账户出有创建视图的权限,可经过进程如下语句授权:
       尾先登岸超等经管员:conn sys/bjsxt as sysdba;
       授权:grant create table,create view to scott;
       再以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 on 9t.avg_sal between s.losal and s.hisal)
     

       然后G中查询可以或许简化成:
       select  dname,t1.deptno,grade,avg_sal from
       v$_dept_avg-sal_info t1
       join dept on (t1.deptno =dept.deptno)
       where t1.grade =
       (select min(grade) from v$_dept_avg-sal_info t1)

 

 

24-------供比通俗员工最高薪水还要高的经理人的名称 -------

    select ename, sal from emp where empno in

       (select distinct mgr from emp where mgr is not null)

    and sal >

    (

       select max(sal) from emp where empno not in

         (select distinct mgr from emp where mgr is not null)

)

 

25---里试题:对比苦守(实际上前一句苦守高,但实践上概略Oracle概略会主动对代码优化,所以没有见得后一句便会慢)

select from emp where deptno 10 and ename like "%A%";    //苦守高,果为将过滤力度大的放正在前里

       select from emp where ename like "%A% and deptno 10;

 

-------------------------------------------以上为select 语句的内容-----------------------------------------

 

 

----------------创建新用户---------------

1backup scott//备份

exp//导出

2create user

create user guohailong identified(认证by guohailong  default tablespace users quota(配额10M on users

grant create session(给它登录到效劳器的权限),create table, create view to guohailong

3import data

Imp

 

 

 

 

25----------勾销操纵--------

rollback

-----------事务确认语句--------

commit;//此时再实验rollback无效

当一般断开连接的时辰例如exit,事务主动提交。  当非一般断开连接,例如间接封闭dos窗心或关机,事务主动提交

 

-----表的备份

       create table dept2 as select from dept;

-----插进数据

        insert into dept2 values(50,"game","beijing");

      ----只对某个字段插进数据

insert into dept2(deptno,dname) values(60,"game2");

-----将一个表中的数据完全插进另外一个表中(表布局必须一样)

insert into dept2 select from dept;

 

 

-----供前五名员工的编号战名称(利用真字段rownum 只能利用  要利用 必须利用子查询)

select empno,ename from emp where rownum <= 5;

----10名雇员当前的雇员名称--------

select ename from (select rownum r,ename from emp) where 10;

----供薪水最高的前5小我的薪水战名字---------

select ename, sal from (select ename, sal from emp order by sal desc) where rownum <=5;

----供按薪水倒序摆列后的第6名到第10名的员工的名字战薪水--------

select ename, sal from

           (select ename, sal, rownum from

              (select ename, sal from emp order by sal desc)

           )

        where r>=6 and r<=10

 

 

-----里试题: 3个表SCSC 

SSNOSNAME)代表(教号,姓名) 

CCNOCNAMECTEACHER)代表(课号,课名,西席) 

SCSNOCNOSCGRADE)代表(教号,课号成绩) 

问题: 

1,找出出选过“拂晓”教师的一切教生姓名。 

2,列出2门以上(露2门)没有合格教生姓名及仄均成绩。 

3,即教过1号课程有教过2号课一切教生的姓名。

答案:

1

    select sname from join sc on(s.sno sc.sno) join on (sc.cno c.cno) where cteacher <> "拂晓";

2

    select sname where sno in (select sno from sc where scgrade 60 group by sno having count(*) >=2);

3

    select sname from where sno in (select sno, from sc where cno=1 and cno in

(select distinct sno from sc where cno 2);

     )

 

27--------------创建表--------------

       create table stu

  (

  id number(6),

  name varchar2(20) constraint stu_name_mm not null,

  *** number(1),

  age number(3),

  sdate date,

  grade number(2) default 1,

  class number(4),

  email varchar2(50) unique

);

28五种束缚前提:非空束缚、唯一束缚、主键束缚、外键束缚、搜检束缚(check

 

--------------name字段插手 非空 束缚,并给束缚一个名字,若没有取,系统默许取一个-------------

       create table stu

  (

  id number(6),

  name varchar2(20) constraint stu_name_mm not null,

  *** number(1),

  age number(3),

  sdate date,

  grade number(2) default 1,

  class number(4),

  email varchar2(50)

);

--------------nameemail字段插手 唯一 束缚 两个 null 没有为重复-------------

       create table stu

  (

  id number(6),

  name varchar2(20) constraint stu_name_mm not null,

  *** number(1),

  age number(3),

  sdate date,

  grade number(2) default 1,

  class number(4),

  email varchar2(50) unique

);

--------------两个字段的组合没有克没有及重复 束缚:表级束缚-------------

       create table stu

  (

  id number(6),

  name varchar2(20) constraint stu_name_mm not null,

  *** number(1),

  age number(3),

  sdate date,

  grade number(2) default 1,

  class number(4),

  email varchar2(50)

constraint stu_name_email_uni unique(email, name)

);



<!-- 正文结束 -->

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/21776310/viewspace-1115212/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2009-05-30