oracle常用知识及sql语句--以scott帐号为例

冷不防 2022-05-16 06:24 233阅读 0赞

我们都知道oracle有一个内置的scott帐号，我们以超级管理员身份解锁scott用户，并为scott设置一个密码为tiger

解锁用户：alter user scott/hr account unlock;

设置密码：alter user scott/hr identified by tiger;

使用客户端sqlplus工具进入与退出orcl数据库

![70][]

\------以超级管理员角色进入

命令：sqlplus / as sysdba

![70 1][]

退出：exit

![70 2][]

\-----以scott用户身份进入

命令：sqlplus scott/tiger

![70 3][]

（01）oracle服务器由哪二部份组成？  
    答：数据库，实例  
            
（02）SQL92/99标准有哪四大分类？  
    答：数据操纵语言（DML）：select、update、delete、insert  
        数据定义语言(DDL)：create table、alter table、drop table  
        数据控制语言（DCL）:grant 权限 from scott、revoke 权限 from scott  
        事务控制语言：rollback、commit、rollback to savepoint...  
      
（03）select \*,ename from emp这样写对吗？  
    答：不对，\*不能和字段一起使用

（04）NVL(a,b)函数是什么意思？  
    答：如果a为null，则取b值，否则取a值  
        
（05）select sysdate from dual;  
    答：查询结果为当天的日期。如当天的日期是2018年08月12日，则查询结果为 12-08月-18  
     
（06）select ename from emp where ename like '%\\\_%' escape '\\'是什么意思？   
    答：查询emp表中的ename字段含有下划线\_的所有姓名

（07）如果判断null值？  
    答：可以使用nullif函数。格式：nullif(a,b)，在类型一致的情况下，如果a与b相同，则返回null，否则返回a  
    
（08）order by能用列号排序呢？从几开始？号  
    答：能。从1开始  
    
（09）between .. and ..和in..是什么意思？  
    答：between .. and ..表示范围，是且的关系，两个条件都要满足。  
    in.. 是或的关系，只要满足in后面的任意条件即可  
     
（10）select add\_months(sysdate,-1) from dual是什么意思？  
    答：查询上一个月的今天的日期

（01）select to\_char(sysdate，'yyyy"年"mm"月"dd"日"day') from dual是什么意思？  
答：把当前日期转换成yyyy"年"mm"月"dd"日"day的格式，例如2018年08月16日星期四  
        
（02）decode()函数有什么作用？max(hiredate)和min(hiredate)函数能用于数值型，还能用于什么类型？  
答：decode()函数可以实现多重判断，类似于java中的switch  
    max()、min()函数能用于数值型，还能用于日期型

（03）select deptno,avg(sal) from emp group by deptno是什么意思？   
答：查询各部门的平均工资

（04）where和having的哪个先执行？非等值连接只能使用<>或!=吗？  
答：where先于having执行。非等值连接除了可以使用<>或!=还能使用>  >= < <=   between ... and ...

（05）外连接能解决什么问题？  
答：外连接既能查询出符合条件的数据，也能根据一方强行查询出另一方的数据，能显示内连接查不到的数据

（06）子查询能解决什么问题？  
答：子查询能解决查询条件未知的问题  
        
（07）select \*   
      from emp   
      where sal < any/all (800,1200,1500,2200,3000,5000)是什么意思？  
答：any-->查询工资小于5000的员工信息  
    all-->查询工资小于800的员工信息

（08）自连接有什么特点？  
答：自连接看成是两张相同的单表连接，也可以使用内连接和外连接

（09）A集合 union B集合时，最终结果的列名由A集合还是B集合决定？  
答：由A决定

（10）rownum=1 和 rownum <=4 和rownum >= 4 哪个能查询出记录？  
答：rownum=1和rownum<=4能查询出记录

找到员工表中工资最高的前三名  
      ROWNUM EMPNO ENAME      SAL  
      ------ ----- ---------- -------  
      1  7839 KING          5000  
      2  7788 SCOTT         3000  
      3  7902 FORD          3000  
        
      select rownum,empno,ename,sal from (select \* from emp order by sal desc) where rownum <=3;

找到员工表中薪水大于【本】部门平均薪水的员工  
      EMPNO ENAME          SAL     AVGSAL  
      ----- ---------- ------- ----------  
      7499 ALLEN         1600       1566  
      7566 JONES         2975       2175  
      7698 BLAKE         2850       1566  
      7788 SCOTT         3000       2175  
      7839 KING          5000       2916  
      7902 FORD          3000       2175  
        
      select EMPNO,ENAME,SAL,avemp.AVGSAL from emp,(select deptno,trunc(avg(sal),0) AVGSAL from emp group by deptno) avemp  
      where emp.deptno(+) = avemp.deptno and emp.sal > avemp.AVGSAL;

显示emp表中3-8条记录（方式一：使用集合减运算）  
select rownum "伪列",emp.\* from emp where rownum<=8  
minus  
select rownum,emp.\* from emp where rownum<=2;

显示emp表中3-8条记录（方式二：使用子查询，在from子句中使用，重点）  
select xx.\*  
from (select rownum ids,emp.\* from emp where rownum<=8) xx   
where ids>=3;  
注意：在子查询中的别名，不可加""引号

显示emp表中5-9条记录  
select yy.\*  
from (select rownum ids,emp.\* from emp where rownum<=9) yy  
where ids>=5;  
注意:在项目中，from后台可能有真实表名，也可能用子查询看作的表名，  
     同时真实表和子查询看作的表要做连接查询

有一个员工表empinfo结构如下  
      create table empinfo(  
    fempno    varchar2(10) not null primary key,  
    fempname varchar2(20) not null,   
        fage number(2) not null,   
        fsalary number(10,2) not null  
      );  
      insert into empinfo(fempno,fempname,fage,fsalary) values('1','AA',30,7000);  
      insert into empinfo(fempno,fempname,fage,fsalary) values('2','BB',31,8000);  
      insert into empinfo(fempno,fempname,fage,fsalary) values('3','CC',32,9000);  
      insert into empinfo(fempno,fempname,fage,fsalary) values('4','DD',33,10000);  
      insert into empinfo(fempno,fempname,fage,fsalary) values('5','EE',34,11000);  
      insert into empinfo(fempno,fempname,fage,fsalary) values('6','FF',35,12000);  
      insert into empinfo(fempno,fempname,fage,fsalary) values('7','GG',36,13000);  
      insert into empinfo(fempno,fempname,fage,fsalary) values('8','FF',37,14000);

假如该表有大约1000万条记录，写一条最高效的SQL语句，计算以下4种人中每种员工的数量   
      第1种人：fsalary>9999 and fage>35  
      第2种人：fsalary>9999 and fage<35       
      第3种人：fsalary<9999 and fage>35      
      第4种人：fsalary<9999 and fage<35

sql语句：

select 
        sum(case when e.fsalary>9999 and e.fage>35 then 1 else 0 end) "第1种人"，
        sum(case when e.fsalary>9999 and fage<35 then 1 else 0 end) "第2种人",
        sum(case when e.fsalary<9999 and fage>35 then 1 else 0 end) "第3种人",
        sum(case when e.fsalary<9999 and fage<35 then 1 else 0 end) "第4种人"
        from empinfo e;

查询结果：

![70 4][]

[70]: /images/20220516/9b73cb0e84764a6ab3bc25226116ed60.png
[70 1]: /images/20220516/a9e7da981d6f44068e4217edd00d415d.png
[70 2]: /images/20220516/9efe47a6ebd149438fe1c7ab2ec43a1b.png
[70 3]: /images/20220516/d578d0c56bab49448331af6b2a8e7615.png
[70 4]: /images/20220516/3d0c654464b549479eaf337ca22136e3.png