Orcale笔记-select语句(二)

来源：互联网发布：oracle数据库下载安装编辑：程序博客网时间：2024/05/21 12:42

二. SELETE 语句

spool  文件                    host   cls                     --清屏show  user                     --当前用户select *  from  tab            --查看所有表 (tab数据字典)  show  tablesshow  linesizeset  linesize  150       --/设置行宽col  列名  for  a8       --设置列宽8个字符/                 --执行上条sql语句

[注]: c /form/from //修改sql语句 : form --> from

1) sql中的null :
(1) 包含null的表达式, 结果为null
(2) null != null (无记录的列, 值不是null , null不是空格, 也不是0)

滤空函数 : nvl(a,b) : a==null ? b:a
判空函数 : is null

         select  sal*12+nvl(comm,0)  from  emp;  select * from emp where comm=null;           --无记录 select * from emp where comm  is  null;--有记录  ed --打开系统文本编辑器, 编辑上条sql语句

别名中, 如果有特殊符号 , 用""引起

2. distinct 去重 (作为形容词修饰其后所有列)

3. 连接符 || , concat函数
select concat('hello',' world') from dual;
select sal||'元' from emp;

dual表 : 查询内容与表无关时用的"伪表" , 用来满足语法要求

单引号 : 字符串 , 日期
双引号 : 列的别名
sql与sqlplus : sql的关键字不能缩写, sqlplus的关键字可以缩写 ;(format --> for)

4. where与order by
(1) between .. and .. : 有边界 , 小值在左边
(2) where条件中有null , 可以用in , 不能用not in
(3) like 模糊查询 : "_"单个字符 , "%":任意字符

    --查询名字中含有'_'的员工   (escape '转义字符')    select  *  from  emp  where  ename  like  '%\_% ' escape '\'

(4) order by + 列 / 表达式 / 别名 / 序号 , desc只是紧跟的一列

<span style="white-space:pre"></span>select deptno,sal from emp order by deptno,sal;              --先按前面的条件order set  pagesize  25 ;                                      --设置每页显示25条记录a (两个空格)desc ;                                       --上条sql命令后面加上desc (append) , 先打印空值 select  *  from emp order by comm;              --升序 , 最后打印空值select  *  from emp order by comm  desc  nulls  last  ;      --降序, 空值在最后最后打印

5. 函数
1) 单行函数
lower('str1') upper('str1') initcap('str1') 首字母大写
rim ('str1' from 'str2') : 从str2中左右删除str1
round (数字 , 位数) : 四舍五入, 保留小数点后几位
trunc (数字, 位数) : 截取小数点后几位 //位数可以是负数, 表示小数点前几位
mod (1600 , 300) : 余数100

    select  sysdate  from  dual ;--显示当前日期 (无时间)

    select  to_char(sysdate , 'yyyy-mm-dd hh24:mi:ss') from  dual ;--显示当前日期+时间    select  to_char(systimestamp , 'yyyy-mm-dd hh24:mi:ss:ff') from  dual ;

[注] 隐式转换 : 数字
  显示转换 : to_char
  to_num (列名 , 'L9,999.99') : L本地函
  to_date
  nvl2(a,b,c) 滤空 a==null ? c : b
  nullif(a,b)   a==b ? 空 : a
coalesce(expr1, expr2, expr3) : 从左向右, 找到第一个不是空的值

2) 条件表达式

(I)CASE : sql99语法
case expr when 值1 then 操作1
when 值2 then 操作2
else 操作3
end "别名"

        select  ename,job,sal,case job when 'PRESIDENT' then sal+1000 when 'MANAGER' then sal+400 else sal+200end "涨后工资"from  emp;

     -- 按范围区分的条件表达式 : case后不加内容, when后跟范围select  ename,job,sal,case    when sal>1000 then sal+1000when sal>500 then sal+400<span style="white-space:pre"></span>else sal+200end "涨后工资"from  emp;

(II) DECODE : orcale特有语法
decode(expr, 值1, 操作1,
值2,操作2,
操作3) "别名"

    select  ename,job,sal,            decode (job,'PRESIDENT',sal+1000,    'MANAGER',sal+800,     sal+400) "涨后工资"    from  emp;

3) 多行函数 (分组函数) ---- //对多行记录操作 , 得出结果
(1)平均值
(I) count, avg, sum : 组函数会自动滤空, 去掉null的记录

<span style="white-space:pre"></span>select count(*) , count(comm)  from  emp;    -- 结果: 14 和 4

(II) 可以嵌套滤空函数, 让null的记录=0, 再进行组函数操作

<span style="white-space:pre"></span>select count(*) , count(nvl(comm,0))  from  emp;

select sum(comm)/count(*) , sum(comm)/count(comm) , avg(comm) from emp; ----后两个相同

(2)group by与having子句

group by(按照什么分组)
(I) select里未在组函数出现的列要在group by中出现 ,
group by中的列可以不在select中出现//(查询的列要作为条件写出)

       select deptno,job,count(ename) from emp group by deptno,job order by 1; DEPTNO          JOB             COUNT(ENAME)------- -------- ------------10 CLERK                      110 MANAGER                    110 PRESIDENT                  120 ANALYST                    220 CLERK                      220 MANAGER                    130 CLERK                      130 MANAGER                   130 SALESMAN                   4

(II) 过滤分组
having : 对分组后的数据进行过滤
having 与 where : where不能接组函数 , 其他情况下可互换

group by rollup(a,b) : 增强版分组, = group by(a,b) + group(a)

     select  deptno , job , sum(sal)  from  emp  group  by  rollup(deptno , job) ;       break on deptno  skip 2;  (相同deptno只显示一次 , skip 不同deptno跳过2行)

DEPTNO         JOB         SUM(SAL)------- --------- ----------10 CLERK          1300<<span style="white-space:pre"></span>MANAGER        2450PRESIDENT      5000       875020 CLERK          1900ANALYST        6000MANAGER        2975       10875

三. 多表查询
1)等值连接 (where条件是等号)

     select e.ename,e.deptno,d.dname from emp e,dept d where e.deptno=d.deptno;

2)外连接 (在where条件的左/右加上(+))

     --按部门统计部门名称     select d.deptno,d.dname,count(e.empno) from emp e,dept d      where e.deptno(+)=d.deptno      group by d.deptno,d.dname;

3) 自连接(连接的是同一张表)

      --查询员工的老板名字      select e1.ename,e2.ename boss from emp e1,emp e2 where e1.mgr=e2.empno  group by e1.ename, boss;      --select count(*) from emp e1,emp e2

[注]: 自连接基于多表查询, 产生笛卡尔积, 不适合大表(用层次查询代替)

四. 层次查询 :

自连接使得每条记录产生关系 , 根据关系形成了一颗"树",用层次遍历的方式把这颗树查询出来

(1)起始条件
(2)数的形成条件
select .. from .. connect by prior 父节点的条件
start with 根节点的形成条件(伪列level)

select  level ,empno , ename , mgr from empconnect  by  prior empno=mgrstart with mgr is nullorder by 1;LEVEL   EMPNO ENAME             MGR------- ----- ---------- ----------1       7839 KING<span style="white-space:pre"></span>7566 JONES            78392       7698 BLAKE            78392       7782 CLARK            78393       7902 FORD             75663       7521 WARD             76983       7900 JAMES            76983       7934 MILLER           77823       7499 ALLEN            76983       7788 SCOTT            75663       7654 MARTIN           7698

五. 子查询 (返回一条记录 : 单行子查询 ; 返回多行记录 : 多行子查询)
1. 子查询语句外边括号
2. where , select , having , from 后跟子查询
1) select后只能跟单行子查询

<span style="white-space:pre"></span>select empno , ename , (select  job  from  emp  where  empno=7839);

0 0