精华内容
下载资源
问答
  • 1、标准sql规范 一、单个IF 1、 if a=... then ......... end if; 2、 if a=... then ...... else .... end if; 二、多个IF if a=.. then ...... elsif a=.. then .... end if; 这里中间是“ELSIF”,而不是...

    1、标准sql规范

    一、单个IF
    1、
    
    if a=...  then
    .........
    end if;
    
    2、
    
    if a=... then
    ......
    else
    ....
    end if;
    
    二、多个IF
    
    if a=..  then
    ......
    elsif a=..  then
    ....
    end if;     
    这里中间是“ELSIF”,而不是ELSE IF 。这里需要特别注意

    2、decode函数

    DECODE的语法

    DECODE(value,if1,then1,if2,then2,if3,then3,...,else)

    表示如果value等于if1时,DECODE函数的结果返回then1,...,如果不等于任何一个if值,则返回else。

    3、case when

    case when a='1'then 'xxxx'
         when a='2' then 'ssss'
    else
      'zzzzz'
    end as

    注意点: 

    1、以CASE开头,以END结尾 
    2、分支中WHEN 后跟条件,THEN为显示结果 
    3、ELSE 为除此之外的默认情况,类似于高级语言程序中switch case的default,可以不加 
    4、END 后跟别名  

    展开全文
  • l在 SQL 语句中使用IF-THEN-ELSE 逻辑 l l使用两种方法: •CASE 表达式:SQL99的语法,类似Basic,比较繁琐 •DECODE 函数:Oracle自己的语法,类似Java,比较简介 1 SQL> select ename,job, sal, ...
    l在 SQL 语句中使用IF-THEN-ELSE 逻辑
    l
    l使用两种方法:
    •CASE 表达式:SQL99的语法,类似Basic,比较繁琐
    •DECODE 函数:Oracle自己的语法,类似Java,比较简介

     

     

     1 SQL> select ename,job, sal, case job when 'PRESIDENT' then 1.1*sal
     2   2                               when 'MANAGER' then 1.2*sal
     3   3                                when 'CLERK' then 1.3*sal
     4   4                                 else 1.4*sal
     5   5                                end 涨后薪水
     6   6  from emp
     7   7  ;
     8  
     9 ENAME      JOB             SAL       涨后薪水
    10 ---------- --------- --------- ----------
    11 SMITH      CLERK        800.00       1040
    12 ALLEN      SALESMAN    1600.00       2240
    13 WARD       SALESMAN    1250.00       1750
    14 JONES      MANAGER     2975.00       3570
    15 MARTIN     SALESMAN    1250.00       1750
    16 BLAKE      MANAGER     2850.00       3420
    17 CLARK      MANAGER     2450.00       2940
    18 KING       PRESIDENT   5000.00       5500
    19 TURNER     SALESMAN    1500.00       2100
    20 JAMES      CLERK        950.00       1235
    21 FORD       ANALYST     3000.00       4200
    22 MILLER     CLERK       1400.00       1820
    23 jack_1234              2000.00       2800
    24  
    25 13 rows selected
    26 
    27 SQL> select ename,job, sal,decode(job,'PRESIDENT',1.1*sal,
    28   2                                   'MANAGER',1.2*sal,
    29   3                                   'CLERK',1.3*sal,
    30   4                                           1.4*sal)涨后薪水
    31   5  from emp;
    32  
    33 ENAME      JOB             SAL       涨后薪水
    34 ---------- --------- --------- ----------
    35 SMITH      CLERK        800.00       1040
    36 ALLEN      SALESMAN    1600.00       2240
    37 WARD       SALESMAN    1250.00       1750
    38 JONES      MANAGER     2975.00       3570
    39 MARTIN     SALESMAN    1250.00       1750
    40 BLAKE      MANAGER     2850.00       3420
    41 CLARK      MANAGER     2450.00       2940
    42 KING       PRESIDENT   5000.00       5500
    43 TURNER     SALESMAN    1500.00       2100
    44 JAMES      CLERK        950.00       1235
    45 FORD       ANALYST     3000.00       4200
    46 MILLER     CLERK       1400.00       1820
    47 jack_1234              2000.00       2800
    48  
    49 13 rows selected

     

    转载于:https://www.cnblogs.com/liuwt365/p/4181232.html

    展开全文
  • Oracle数据库中SQL语句用法(一)

    千次阅读 多人点赞 2019-09-29 17:03:43
    第1章:编写基本的SQL SELECT语句 第2章:限制和排序数据 第3章:单行函数 第4章:显示多个表的数据 第5章:使用分组函数聚集数据 第6章 子查询 第7章 使用 iSQL*Plus 产生可读的输出 第8章 处理数据

    Copyright © 2019 @Linyer. All Rights Reserved

    下接Oracle数据库中SQL语句用法(二)【点击以查看】

    目录

    本教程使用 Oracle 示例 数据库

    第1章:编写基本的SQL SELECT语句

    基本SELECT语句

    • SELCECT确定处理哪个
    • FROM确定处理哪个

    选择 departments 表中所有列

    select * from departments;
    

    在这里插入图片描述

    选择特定列

    选择 departments 表中 department_id,location_id 两列

    SELECT department_id, location_id 
    FROM departments;
    

    在这里插入图片描述

    SQL语句区分大小写
    可以为一行也可为多行

    算术运算符

    • 算术运算符有:
    符号意义
    +
    -
    *
    /

    所有员工薪水加 300

    SELECT last_name, salary, salary + 300
    FROM employees;
    

    在这里插入图片描述

    • 优先排序(从先到后) *–>/–>+–>-
      • 运算符优先顺序
    SELECT last_name, salary, 12*salary+100
    FROM employees;
    

    在这里插入图片描述

      • 使用括号
    SELECT last_name, salary, 12*(salary+100)
    FROM employees;
    

    在这里插入图片描述

    • 如果算术表达式中的任一列值为,则结果为

    列名别名

    • AS 可写可不写
      • 区分大小写
    SELECT last_name AS name, commission_pct comm
    FROM employees;
    

    在这里插入图片描述

      • 区分大小写,需加上双引号
    SELECT last_name "Name", salary*12 "Annual Salary"
    FROM employees;
    

    在这里插入图片描述

    SQL语句中只有这里用 双引号,其余都用 单引号

    连接运算符

    • 字符串连接到其他列,用||表示
    SELECT last_name||job_id AS "Employees"
    FROM employees;
    

    在这里插入图片描述

    文字字符串

    • 字符,数字,日期
    • 日期,字符文字值 必须包含在单引号
    SELECT last_name ||' is a '||job_id
    AS "Employee Details"
    FROM employees;
    

    在这里插入图片描述

    SELECT last_name ||': 1 Month salary = '||salary Monthly
    FROM employees;
    

    在这里插入图片描述

    重复行

    • 查询默认显示所有行,包括重复行
    SELECT department_id
    FROM employees;
    

    在这里插入图片描述

    • DISTINCTdistinct关键字可消除重复行
    SELECT distinct department_id
    FROM employees;
    

    在这里插入图片描述

    第2章:限制和排序数据

    限制选定的行

    • WHERE 子句可以比较列,文字值,算术表达式或函数的值。

    查询 department_id = 90 的员工

    SELECT employee_id, last_name, job_id, department_id
    FROM employees
    WHERE department_id = 90 ;
    

    在这里插入图片描述

    字符串和日期

    • 字符串和日期值包含在单引号
    • 字符值区分大小写,日起值区分格式,默认格式为 DD-MON-RR

    查询 last_name 为 Whalen 的员工

    SELECT last_name, job_id, department_id
    FROM employees
    WHERE last_name = 'Whalen';
    

    在这里插入图片描述

    名字大写后,不返回结果

    SELECT last_name, job_id, department_id
    FROM employees
    WHERE last_name = 'WHALEN';
    

    比较条件

    运算符比较

    符号意义
    =等于
    >大于
    >=大于等于
    <小于
    <=小于等于
    <> 或 != 或 ^=不等于

    WHERE 子句中 能使用别名

    查询薪水小于等于 3000 的员工的 last_name 和 salary

    SELECT last_name, salary
    FROM employees
    WHERE salary <= 3000;
    

    在这里插入图片描述

    其他比较

    符号意义
    between…and…介于俩值之间(包括俩值)
    in(set)与列表的任一值匹配
    like与某个字符模式匹配
    is null为空值

    between…and…

    查询薪水位于 2500 和 3500 的员工

    SELECT last_name, salary
    FROM employees
    WHERE salary between 2500 and 3500;
    

    在这里插入图片描述

    in(set)

    查询 manager_id 为 100,101,201 的员工

    SELECT employee_id, last_name, salary, manager_id
    FROM employees
    WHERE manager_id IN (100, 101, 201);
    

    在这里插入图片描述
    查询 last_name 为 Hartstein 和 Vargas 的员工

    SELECT employee_id, manager_id, department_id
    FROM employees
    WHERE last_name IN ('Hartstein', 'Vargas');
    

    在这里插入图片描述

    like

    符号代表
    %0 或 许多字符
    _一个字符

    查询 first_name 首字母为 S 的员工

    SELECT first_name
    FROM employees
    WHERE first_name LIKE 'S%';
    

    在这里插入图片描述
    查询 2005 年进入公司的员工

    SELECT last_name, hire_date
    FROM employees
    WHERE hire_date LIKE '%05';
    

    在这里插入图片描述
    查询 last_name 第二个字母为 o 的员工

    SELECT last_name
    FROM employees
    WHERE last_name LIKE '_o%';
    

    在这里插入图片描述

    • 查询 % 和 _ 本身需要ESCAPEescape条件来转码

    查询 job_id 包含 SA_ 的员工

    SELECT employee_id, last_name, job_id
    FROM employees
    WHERE job_id LIKE '%SA\_%' escape '\';
    

    在这里插入图片描述

    is null

    查询空值

    SELECT last_name, manager_id
    FROM employees
    WHERE manager_id IS NULL;
    

    在这里插入图片描述

    SELECT last_name, job_id, commission_pct
    FROM employees
    WHERE commission_pct IS NULL;
    

    在这里插入图片描述

    逻辑条件

    符号意义
    AND
    OR
    NOT

    查询薪水大于等于 10000 并且 job_id 中包含 MAN 的员工

    SELECT employee_id, last_name, job_id, salary
    FROM employees
    WHERE salary >=10000
    AND job_id LIKE '%MAN%';
    

    在这里插入图片描述
    查询薪水大于等于 10000 或者 job_id 中包含 MAN 的员工

    SELECT employee_id, last_name, job_id, salary
    FROM employees
    WHERE salary >= 10000
    OR job_id LIKE '%MAN%';
    

    在这里插入图片描述
    查询 job_id 不为 IT_PROG,ST_CLERK,SA_REP 的员工

    SELECT last_name, job_id
    FROM employees
    WHERE job_id
    NOT IN ('IT_PROG', 'ST_CLERK', 'SA_REP');
    

    在这里插入图片描述
    可以混合使用

    ... WHERE job_id NOT IN ('AC_ACCOUNT', 'AD_VP')
    ... WHERE salary NOT BETWEEN 10000 AND 15000
    ... WHERE last_name NOT LIKE '%A%'
    ... WHERE commission_pct IS NOT NULL
    
    • 优先顺序
    优先度运算符
    1算术运算符
    2连接运算符
    3比较运算符
    4is [not] null,like,[not] in
    5[not] between
    6NOT逻辑条件
    7AND逻辑条件
    8OR逻辑条件

    括号 改变优先级

    先 AND 后 OR

    SELECT last_name, job_id, salary
    FROM employees
    WHERE job_id = 'SA_REP'
    OR job_id = 'AD_PRES'
    AND salary > 15000;
    
    • 使用括号后,强制改变优先级

    先 OR 后 AND

    SELECT last_name, job_id, salary
    FROM employees
    WHERE (job_id = 'SA_REP'
    OR job_id = 'AD_PRES')
    AND salary > 15000;
    

    ORDER BY子句

    • 使用ORDER BY子句可对行进行排序
      • ASC 升序,默认
      • DESC降序

    按 hire_date 升序排列

    SELECT last_name, job_id, department_id, hire_date
    FROM employees
    ORDER BY hire_date ;
    

    在这里插入图片描述
    按 hire_date 降序排列

    SELECT last_name, job_id, department_id, hire_date
    FROM employees
    ORDER BY hire_date DESC ;
    

    在这里插入图片描述

    空值升序最后 显示,在 降序最先 显示

    • 按别名排序
    SELECT employee_id, last_name, salary*12 annsal
    FROM employees
    ORDER BY annsal;
    

    在这里插入图片描述

    • 按多个列排序

    先按部门排序,再按每个部门薪水降序排序

    SELECT last_name, department_id, salary
    FROM employees
    ORDER BY department_id, salary DESC;
    

    在这里插入图片描述

    即使该列 在SELECT列表中,仍可按该列排序

    第3章:单行函数

    大小写处理函数

    • 三种大小写处理函数用法
    函数语句结果
    LOWER(‘SQL Course’)sql course
    UPPER(‘SQL Course’)SQL COURSE
    INITCAP(‘SQL Course’)Sql Course
    函数意义
    LOWERlower所有字母 转换为 小写
    UPPERupper所有字母 转换为 大写
    INICAPinicap所有字母 转换为 首字母大写,其余小写

    查询员工的 小写 last_name

    SELECT employee_id, last_name, department_id
    FROM employees
    WHERE lower(last_name) = 'higgins';
    

    在这里插入图片描述
    查询员工的 首字母大写,其余小写 last_name

    SELECT employee_id, upper(last_name), department_id
    FROM employees
    WHERE inicap(last_name) = 'Higgins';
    

    字符处理函数

    函数语句结果
    CONCAT(‘Hello’, ‘World’)HelloWorld
    SUBSTR(‘HelloWorld’,1,5)Hello
    LENGTH(‘HelloWorld’)10
    INSTR(‘HelloWorld’, ‘W’)6
    LPAD(salary,10,’#’)#####24000
    RPAD(salary, 10, ‘#’)24000#####
    TRIM(‘H’ FROM ‘HelloWorld’)elloWorld
    函数意义
    CONCATconcat将俩字符串连一起
    SUBSTRsubstr将指定起点终点之间字符串截出
    LENGTHlength以数字显示字符串长度
    INSTRinstr以数字显示指定字符位于字符串中的位置
    LPADlpad按右对齐,在原字符串左边填充字符,并指定填充字符串加原字符串总长度
    RPADrpad按左对齐,在原字符串右边填充字符,并指定填充字符串加原字符串总长度
    TRIMtrim截去字符串中的头或尾的字符,或都截去
    SELECT employee_id, concat(first_name, last_name) NAME,
           job_id, length(last_name),
           instr(last_name, 'a') "Contains 'a'?"
    FROM employees
    WHERE substr(job_id, 4) = 'REP';
    

    在这里插入图片描述
    在这里插入图片描述

    SELECT employee_id, concat(first_name, last_name) NAME,
           LENGTH (last_name), instr(last_name, 'a') "Contains 'a'?"
    FROM employees
    WHERE substr(last_name, -1, 1) = 'n';
    

    在这里插入图片描述

    数字函数

    函数语句结果
    ROUND(45.926, 2)45.93
    TRUNC(45.926, 2)45.92
    MOD(1600, 300)100
    函数意义
    ROUNDround将值四舍五入到指定小数位
    TRUNCtrunc将值阶断到指定小数位(不舍入)
    MODmod返回除法运算余数

    处理日期

    • 日期默认格式为 DD-MON-RR(如07-JUN-94
    • SYSDATE 函数,返回数据库服务器当前日期和时间
    SELECT sysdate
    FROM DUAL;
    
    用法结果为意义
    日期+数字日期向日期中添加天数
    日期-数字日期向日期中减去天数
    日期-日期天数从一个日期减另一个日期
    日期+数字/24日期向日期中添加小时数

    查询员工已雇佣周数

    SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKS
    FROM employees
    WHERE department_id = 90;
    

    在这里插入图片描述

    日期函数

    假设SYSDATE为 25-JULY-95

    函数语句结果
    MONTHS_BETWEEN (‘01-SEP-95’,‘11-JAN-94’)19.6774194
    ADD_MONTHS (‘11-JAN-94’,6)‘11-JUL-94’
    NEXT_DAY (‘01-SEP-95’,‘FRIDAY’)‘08-SEP-95’
    LAST_DAY(‘01-FEB-95’)‘28-FEB-95’
    ROUND(SYSDATE,‘MONTH’)‘01-AUG-95’
    ROUND(SYSDATE ,‘YEAR’)‘01-JAN-96’
    TRUNC(SYSDATE ,‘MONTH’)‘01-JUL-95’
    TRUNC(SYSDATE ,‘YEAR’)‘01-JAN-95’
    函数意义
    MONTHS_BETWEENmonths_between两个日期之间的月数
    ADD_MONTHSadd_months将月份添加到日期当中
    NEXT_DAYnext_day指定日期的下一天
    LAST_DAYlast_day月份的最后一天
    ROUNDround舍入日期
    TRUNCtrunc截断日期

    显示聘用日期不足36个月的所有员工的员工编号、聘用日期、聘用的月数、六个月的复查日期、聘用日期之后的第一个星期五和聘用当月的最后一天。

    SELECT employee_id, hire_date,
           months_between(SYSDATE, hire_date) TENURE,
           add_months(hire_date, 6) REVIEW,
           next_day(hire_date, 'FRIDAY'), last_day(hire_date)
    FROM employees
    WHERE months_between(sysdate, hire_date) < 36;
    

    函数转换

    • 隐式转换 = 自动转换
      VARCHAR2或CHAR<自动互相转换>NUMBER
      VARCHAR2或CHAR<自动互相转换>DATE

    • 显式转换 = 手动转换

    TO_CHAR处理日期

    • to_char(date,'format_model')日期转指定样式
    SELECT employee_id, to_char(hire_date, 'MM/YY') Month_Hired
    FROM employees
    WHERE last_name = 'Higgins';
    

    在这里插入图片描述

    • 日期格式样式:
    样式意义
    YYYY用数字表示的完整年份
    YEAR用字母拼写的年份
    MM代表月份的两位数值
    MONTH月份的全名
    MON代表月份的三个字母缩写
    DY代表星期几的三个字母缩写
    DAY星期几的全名
    DD用数字表示的月份中的某日
    • 时间格式转换
      HH24:MI:SS AM–> 15:45:32 PM

    • 添加字符串
      DD “of” MONTH–> 12 of OCTOBER

    • 时间格式样式:

    样式意义
    AM 或 PM正午指示符
    A.M. 或 P.M带有句点的正午指示符
    HH 或 HH12 或 HH24天中的小时,或小时(1-12),或小时(0-23),SS秒(0-59)
    SSSSS午夜之后的秒数(0-86399)

    查询所有员工的姓氏和聘用日期,聘用日期的显示样式为 “17June1987”

    SELECT last_name,
           to_char(hire_date, 'fmDD Month YYYY') AS HIREDATE
    FROM employees;
    

    在这里插入图片描述

    此为 中文版 显示方法,在 英文版 中月份显示为 June 这样的月份

    按 “Seventh of June 1994 12:00:00AM” 格式来显示日期

    SELECT last_name,
           to_char(hire_date,'fmDdspth "of" Month YYYY fmHH:MI:SS AM') HIREDATE
    FROM employees;
    

    在这里插入图片描述

    TO_CHAR 处理数字

    SELECT to_char(salary, '$99,999.00') SALARY
    FROM employees
    WHERE last_name = 'Ernst';
    

    在这里插入图片描述

    常规函数

    NVL 函数

    • NVL (expr1, expr2)
      • expr1是可能包含空值的源值或表达式
      • expr2是转换空值的目标值
    • 将空值转换为实际值
      • 可以使用的数据类型为日期字符数字
      • 数据类型必须匹配
    SELECT last_name, salary, NVL(commission_pct, 0),
           (salary*12) + (salary*12*NVL(commission_pct, 0)) AN_SAL
    FROM employees;
    

    在这里插入图片描述

    NVL2 函数

    • NVL2(expr1, expr2, expr3)
      • expr1是可能包含空值的源值或表达式
      • expr2expr1 不为空时返回的值
      • expr3expr1 为空时返回的值
    • NVL2函数会检查第一个表达式:
      • 如果第一个表达式不为空,则NVL2函数会返回第二个表达式
      • 如果第一个表达式为空,则返回第三个表达式
    SELECT last_name, salary, commission_pct,
           NVL2(commission_pct,
           'SAL+COMM', 'SAL') income
    FROM employees WHERE department_id IN (50, 80);
    

    在这里插入图片描述

    NULLIF 函数

    • NULLIF (expr1, expr2)
      • expr1是要与 expr2 比较的源值
      • expr2是用来与 expr1 比较的源值(如果它不等于 expr1,则返回 expr1
    • NULLIF 函数用于比较两个表达式:
      • 如果它们相等,则函数返回空值
      • 如果它们不相等,函数就会返回第一个表达式不能为第一个表达式指定 NULL
    SELECT first_name, LENGTH(first_name) "expr1",
           last_name, LENGTH(last_name) "expr2",
           NULLIF(LENGTH(first_name), LENGTH(last_name)) result
    FROM employees;
    

    在这里插入图片描述

    COALESCE函数

    • COALESCE (expr1, expr2, ... exprn)
      • expr1如果此表达式不为空,则返回它
      • expr2如果第一个表达式为空,并且此表达式不为空,则返回它
      • exprn如果前面的表达式都为空,则返回此表达式
    • COALESCE 函数优于NVL函数之处在于:
      COALESCE 函数可以使用多个备选值
    • 如果第一个表达式不为空,则它返回该表达式;否则,将对余下的表达式执行 COALESCE 运算
    SELECT last_name,
    COALESCE(commission_pct, salary, 10) comm
    FROM employees
    ORDER BY commission_pct;
    

    在这里插入图片描述

    条件表达式

    CASE 表达式

    如果JOBIDITPROG,则薪金增加10%;如果JOBIDSTCLERK,薪金增加15%;如果JOBIDSAREP,薪金就增加20%。如果是其它职务角色,就不增加薪金

    SELECT last_name, job_id, salary,
           CASE job_id WHEN 'IT_PROG' THEN 1.10*salary
                       WHEN 'ST_CLERK' THEN 1.15*salary
                       WHEN 'SA_REP' THEN 1.20*salary
           ELSE salary END "REVISED_SALARY"
    FROM employees;
    

    在这里插入图片描述

    DECODE 表达式

    上述语句等效于下述语句

    SELECT last_name, job_id, salary,
           DECODE(job_id, 'IT_PROG', 1.10*salary,
                          'ST_CLERK', 1.15*salary,
                          'SA_REP', 1.20*salary,
                  salary)
           REVISED_SALARY
    FROM employees;
    
    月薪范围(Monthly Salary Range)税率(Rate)
    $0.00-1999.9900%
    $2,000.00-3,999.9909%
    $4,000.00-5,999.9920%
    $6,000.00-7,999.9930%
    $8,000.00-9,999.9940%
    $10,000.00-11,999.9942%
    $12,200.00-13,999.9944%
    $14,000.00或更高45%

    查询部门80中每位员工的适用税率

    SELECT last_name, salary,
    DECODE (TRUNC(salary/2000, 0),
                      0, 0.00,
                      1, 0.09,
                      2, 0.20,
                      3, 0.30,
                      4, 0.40,
                      5, 0.42,
                      6, 0.44,
                         0.45) TAX_RATE
    FROM employees
    WHERE department_id = 80;
    

    在这里插入图片描述

    第4章:显示多个表中的数据

    等值联结

    SELECT employees.employee_id, employees.last_name, employees.department_id,
           departments.department_id, departments.location_id
    FROM employees, departments
    WHERE employees.department_id = departments.department_id;
    

    在这里插入图片描述

    • 使用 AND 运算符
    SELECT last_name, employees.department_id, department_name
    FROM employees, departments
    WHERE employees.department_id = departments.department_id
    AND last_name = 'Matos';
    

    在这里插入图片描述

    • 使用表别名
      • 使用表别名可以简化查询
      • 使用表前缀可以提高性能
    SELECT e.employee_id, e.last_name, e.department_id,
           d.department_id, d.location_id
    FROM employees e , departments d
    WHERE e.department_id = d.department_id;
    

    在这里插入图片描述

    非等值联结

    SELECT e.last_name, e.salary, j.grade_level
    FROM employees e, job_grades j
    WHERE e.salary
          BETWEEN j.lowest_sal AND j.highest_sal;
    

    外联结

    • 使用外联结还可以查看不满足联结条件的行
    • 外联结运算符是加号 (+)

    (+) 可位于 = 任意一侧,但能两侧都有

    SELECT table1.column, table2.column
    FROM table1, table2
    WHERE table1.column(+) = table2.column;
    
    SELECT table1.column, table2.column
    FROM table1, table2
    WHERE table1.column = table2.column(+);
    

    查询员工姓氏、部门标识以及部门名称

    SELECT e.last_name, e.department_id, d.department_name
    FROM employees e, departments d
    WHERE e.department_id(+) = d.department_id ;
    

    在这里插入图片描述

    外联结运算符只能出现在表达式的一侧,即缺少信息的那一侧。它将从一个表中返回在另一个表中没有直接匹配的行。
    包含外联结的条件不能使用 IN 运算符,也不能通过 OR 运算符链接到另一个条件。

    自联结

    SELECT worker.last_name || ' works for '
    || manager.last_name
    FROM employees worker, employees manager
    WHERE worker.manager_id = manager.employee_id ;
    

    在这里插入图片描述

    交叉联结

    • CROSS JOIN 子句可以生成两个表的交叉乘积
    • 这与两个表之间的笛卡尔乘积是相同
    SELECT last_name, department_name
    FROM employees
    CROSS JOIN departments ;
    

    在这里插入图片描述
    等效于:

    SELECT last_name, department_name
    FROM employees, departments;
    

    自然联结

    • NATURAL JOIN 子句是以两个表中具有相同名称的所有列为基础的
    • 它将选择两个表中那些在所有匹配的列中值相等的行
    • 如果列具有相同的名称,但是数据类型不同,就会返回一个错误

    两表的 location_id 相同,查询出 location_id 相同的行

    SELECT department_id, department_name,
           location_id, city
    FROM departments
    NATURAL JOIN locations ;
    

    在这里插入图片描述
    等效于:

    SELECT department_id, department_name,
           departments.location_id, city
    FROM departments, locations
    WHERE departments.location_id = locations.location_id;
    

    查询限制 department_id 为 20 或 50 的行

    SELECT department_id, department_name,
           location_id, city
    FROM departments
    NATURAL JOIN locations
    WHERE department_id IN (20, 50);
    

    在这里插入图片描述

    USING子句联结

    SELECT l.city, d.department_name
    FROM locations l JOIN departments d USING (location_id)
    WHERE location_id = 1400;
    

    在这里插入图片描述

    SELECT e.employee_id, e.last_name, d.location_id
    FROM employees e JOIN departments d
    USING (department_id) ;
    

    在这里插入图片描述
    等效于:

    SELECT employee_id, last_name,
           employees.department_id, location_id
    FROM employees, departments
    WHERE employees.department_id = departments.department_id;
    

    ON子句联结

    SELECT e.employee_id, e.last_name, e.department_id,
           d.department_id, d.location_id
    FROM employees e JOIN departments d
    ON (e.department_id = d.department_id);
    

    在这里插入图片描述
    联结不同的列

    SELECT e.last_name emp, m.last_name mgr
    FROM employees e JOIN employees m
    ON (e.manager_id = m.employee_id);
    

    在这里插入图片描述
    三向联结

    SELECT employee_id, city, department_name
    FROM employees e
    JOIN departments d
    ON d.department_id = e.department_id
    JOIN locations l
    ON d.location_id = l.location_id;
    

    在这里插入图片描述
    等效于:

    SELECT employee_id, city, department_name
    FROM employees, departments, locations
    WHERE employees.department_id = departments.department_id
    AND departments.location_id = locations.location_id;
    

    OUTER JOIN联结

    • LEFT OUTER JOIN 联结
    SELECT e.last_name, e.department_id, d.department_name
    FROM employees e
    LEFT OUTER JOIN departments d
    ON (e.department_id = d.department_id) ;
    

    在这里插入图片描述
    等效于:

    SELECT e.last_name, e.department_id, d.department_name
    FROM employees e, departments d
    WHERE d.department_id (+) = e.department_id;
    
    • RIGHT OUTER JOIN 联结
    SELECT e.last_name, e.department_id, d.department_name
    FROM employees e
    RIGHT OUTER JOIN departments d
    ON (e.department_id = d.department_id) ;
    

    在这里插入图片描述
    等效于:

    SELECT e.last_name, e.department_id, d.department_name
    FROM employees e, departments d
    WHERE d.department_id = e.department_id (+);
    
    • FULL OUTER JOIN 联结
    SELECT e.last_name, e.department_id, d.department_name
    FROM employees e
    FULL OUTER JOIN departments d
    ON (e.department_id = d.department_id) ;
    

    在这里插入图片描述

    • 附加条件
    SELECT e.employee_id, e.last_name, e.department_id,
           d.department_id, d.location_id
    FROM employees e JOIN departments d
    ON (e.department_id = d.department_id)
    AND e.manager_id = 149 ;
    

    第5章:使用分组函数聚集数据

    AVG 和 SUM 函数

    函数意义
    AVG平均值,忽略空值
    SUM总计值,忽略空值
    SELECT AVG(salary), MAX(salary),
           MIN(salary), SUM(salary)
    FROM employees
    WHERE job_id LIKE '%REP%';
    

    在这里插入图片描述

    MIN 和 MAX 函数

    函数意义
    MIN最小值,忽略空值
    MAX最大值,忽略空值
    SELECT MIN(hire_date), MAX(hire_date)
    FROM employees;
    

    在这里插入图片描述

    COUNT函数

    • COUNT 函数有三种格式:
    函数意义
    COUNT( * )可以返回表中满足 SELECT 语句条件的行的数量,包括重复的行和任意列中含有空值的行;如果 SELECT 语句中包含 WHERE 子句,COUNT(*) 会返回满足 WHERE 子句中条件的行的数量
    COUNT(expr)返回由 expr 标识的列中空值的数量
    COUNT(DISTINCT expr)返回由 expr 标识的列中重复的、空值的数量

    返回表中行的数量

    SELECT COUNT(*)
    FROM employees
    WHERE department_id = 50;
    

    在这里插入图片描述

    • COUNT(expr) 返回 expr 为空值的行的数量

    显示 EMPLOYEES 表中部门值的数量,包括空值

    SELECT COUNT(commission_pct)
    FROM employees
    WHERE department_id = 80;
    

    在这里插入图片描述

    • 使用 DISTINCT 关键字
    • COUNT(DISTINCT expr) 返回 expr 的重复的非空值的数量

    显示 EMPLOYEES 表中重复的部门值的数量

    SELECT COUNT(DISTINCT department_id)
    FROM employees;
    

    在这里插入图片描述

    STDDEV 和 VARIANCE 函数

    函数意义
    STDDEV标准差,忽略空值
    VARIANCE方差,忽略空值

    分组函数和空值

    • 分组函数会忽略列中的空值
    SELECT AVG(commission_pct)
    FROM employees;
    

    在这里插入图片描述

    • NVL 函数可以强制分组函数包含空值
    SELECT AVG(NVL(commission_pct, 0))
    FROM employees;
    

    在这里插入图片描述

    创建数据组

    GROUP BY 子句语法

    • 能在 GROUP BY 子句中使用列别名
    • 在默认情况下,行按照包含在 GROUP BY 列表中的列的升序来排列。可以通过 ORDER BY 子句来改变这种排序方式

    查询不同部门的员工的平均薪水

    SELECT department_id, AVG(salary)
    FROM employees
    GROUP BY department_id ;
    

    在这里插入图片描述

    • GROUP BY 列不一定要出现在 SELECT 列表中
    SELECT AVG(salary)
    FROM employees
    GROUP BY department_id ;
    

    在这里插入图片描述
    按平均薪水排序

    SELECT department_id, AVG(salary)
    FROM employees
    GROUP BY department_id
    ORDER BY AVG(salary);
    

    在这里插入图片描述

    • SELECT 列表中 不是聚合函数 的任何列或表达式都 必须GROUP BY 子句中

    错误示例:

    SELECT department_id, COUNT(last_name)
    FROM employees;
    

    纠正后:

    SELECT department_id, count(last_name)
    FROM employees
    GROUP BY department_id;
    

    在这里插入图片描述

    • 不能使用 WHERE 子句来限制组
    • 可以使用 HAVING 子句来限制组
    • 不能WHERE 子句中使用分组函数

    错误示例:

    SELECT department_id, AVG(salary)
    FROM employees
    WHERE AVG(salary) > 8000
    GROUP BY department_id;
    

    纠正后:

    SELECT department_id, AVG(salary)
    FROM employees
    HAVING AVG(salary) > 8000
    GROUP BY department_id;
    

    在这里插入图片描述

    使用 HAVING 子句来限制组

    查询最高薪金高于$10,000的部门的部门编号和最高薪金

    SELECT department_id, MAX(salary)
    FROM employees
    GROUP BY department_id
    HAVING MAX(salary)>10000 ;
    

    在这里插入图片描述
    查询工资总额超过$13,000的每个职务的职务标识和月薪总额。该示例排除了销售代表,并按照月薪总额对列表进行排序

    SELECT job_id, SUM(salary) PAYROLL
    FROM employees
    WHERE job_id NOT LIKE '%REP%'
    GROUP BY job_id
    HAVING SUM(salary) > 13000
    ORDER BY SUM(salary);
    

    在这里插入图片描述

    嵌套分组函数

    查询最高平均薪水

    SELECT MAX(AVG(salary))
    FROM employees
    GROUP BY department_id;
    

    第6章 子查询

    • 子查询(内部查询)在执行主查询之前执行一次
    • 然后主查询(外部查询)会使用该子查询的结果

    内部查询用于确定员工Abel的薪金。外部查询采用内部查询的结果,并使用此结果来显示薪金超过此数额的所有员工。

    SELECT last_name
    FROM employees
    WHERE salary >
                  (SELECT salary
                  FROM employees
                  WHERE last_name = 'Abel');
    

    在这里插入图片描述

    单行子查询

    • 返回一行
    • 使用单行比较运算符

    单行运算符有:

    符号意义
    =等于
    >大于
    >=大于等于
    <小于
    <小于等于
    <>不等于

    显示职务标识和员工141相同的员工

    SELECT last_name, job_id
    FROM employees
    WHERE job_id =
                  (SELECT job_id
                  FROM employees
                  WHERE employee_id = 141);
    

    在这里插入图片描述

    查询职务标识和员工141相同,并且薪金高于员工143的员工

    SELECT last_name, job_id, salary
    FROM employees
    WHERE job_id =
                  (SELECT job_id
                  FROM employees
                  WHERE employee_id = 141)
    AND salary >
                (SELECT salary
                FROM employees
                WHERE employee_id = 143);
    

    在这里插入图片描述

    • 在子查询中使用分组查询

    查询薪金等于最低薪金的所有员工的姓氏、职务标识和薪金

    SELECT last_name, job_id, salary
    FROM employees
    WHERE salary =
                  (SELECT MIN(salary)
                  FROM employees);
    

    在这里插入图片描述

    • 具有子查询的 HAVING 语句
    SELECT department_id, MIN(salary)
    FROM employees
    GROUP BY department_id
    HAVING MIN(salary) >
                        (SELECT MIN(salary)
                        FROM employees
                        WHERE department_id = 50);
    

    在这里插入图片描述

    查找平均薪资最低的工作

    SELECT job_id, AVG(salary)
    FROM employees
    GROUP BY job_id
    HAVING AVG(salary) = (SELECT MIN(AVG(salary))
                         FROM employees
                         GROUP BY job_id);
    
    • 单行运算符 不能 用于多行子查询

    错误示例:

    SELECT employee_id, last_name
    FROM employees
    WHERE salary =
                  (SELECT MIN(salary)
                  FROM employees
                  GROUP BY department_id);
    

    多行子查询

    • 返回个行
    • 使用多行比较运算符

    多行运算符有:

    符号意义
    IN等于列表中的任意一个
    ANY将值与子查询返回的任意一个值进行比较
    ALL将值与子查询返回的每个值进行比较

    IN 运算符

    查薪金等于各个部门最低薪金的所有员工

    SELECT last_name, salary, department_id
    FROM employees
    WHERE salary IN (SELECT MIN(salary)
                    FROM employees
                    GROUP BY department_id);
    

    在这里插入图片描述

    ANY 运算符

    • ANY 运算符(和其同义词SOME运算符)将值与子查询返回的在意一个值进行比较
    符号意义
    < ANY低于最高值
    > ANY高于最低值
    = ANY等同于IN

    查询职务不是IT程序员并且薪金低于任何一个IT程序员的员工,程序员的最高薪金为$9,000

    SELECT employee_id, last_name, job_id, salary
    FROM employees
    WHERE salary < ANY
                       (SELECT salary
                       FROM employees
                       WHERE job_id = 'IT_PROG')
    AND job_id <> 'IT_PROG';
    

    在这里插入图片描述

    ALL 运算符

    • ALL 运算符会将一个值与子查询返回的每个值进行比较
    符号意义
    < ALL意味着低于最低值
    > ALL意味着高于最高值
    • NOT运算符可以和 INANYALL 运算符配合使用

    查询薪金低于所有职务标识为 IT_PROG 的员工且职务不是 IT_PROG 的员工

    SELECT employee_id, last_name, job_id, salary
    FROM employees
    WHERE salary < ALL
                       (SELECT salary
                       FROM employees
                       WHERE job_id = 'IT_PROG')
    AND job_id <> 'IT_PROG';
    

    在这里插入图片描述

    子查询中的空值

    显示没有任何下属的所有员工

    SELECT emp.last_name
    FROM employees emp
    WHERE emp.employee_id NOT IN
                                 (SELECT mgr.manager_id
                                 FROM employees mgr);
    

    该SQL语句没有返回任何行,因为内部查询返回的其中一个值是空值,所以整个查询不会返回任何行

    • 如果使用的是 IN 运算符,则子查询的结果集中存在空值就不会成为问题
      • IN 运算符等同于 = ANY

    查询具有下属的员

    SELECT emp.last_name
    FROM employees emp
    WHERE emp.employee_id IN
                             (SELECT mgr.manager_id
                             FROM employees mgr);
    

    在这里插入图片描述
    等效于:

    SELECT last_name FROM employees
    WHERE employee_id NOT IN
                             (SELECT manager_id
                             FROM employees
    WHERE manager_id IS NOT NULL); 
    

    第7章 使用 iSQL*Plus 产生可读的输出

    替代变亮

    • 使用iSQL*Plus替代变量可以执行以下任务:
      • 临时存储值
        单和号(&)
        双和号(&&)
        DEFINE 命令
      • 在SQL 语句之间传递变量值
      • 动态更改题头和页脚

    & 替代变量

    使用前缀有和号(&)的变量可以提示用户输入值

    SELECT employee_id, last_name, salary, department_id
    FROM employees
    WHERE employee_id = &employee_num ;
    

    在这里插入图片描述
    在这里插入图片描述

    指定 字符 和 日期值

    • 对于日期字符值使用引号
    • 可以将 和号 用于诸如 UPPERLOWER 之类的函数
      • 使用 UPPER(’&job title1) 可以使用户不必以大写输入职称

    查询会根据 iSQL*Plus 替代变量的职称值检索

    SELECT last_name, department_id, salary*12
    FROM employees
    WHERE job_id = '&job_title' ;
    

    在这里插入图片描述
    在这里插入图片描述

    指定列名、表达式和文本

    • 使用替代变量可以补充以下对象:
      WHER 条件
      ORDER BY 子句
      列表达试
      表名
      整个 SELLECT 语句

    查询满足任何条件的 员工编号 和 任何其它列

    SELECT employee_id, &column_name
    FROM employees
    WHERE &condition;
    

    在这里插入图片描述
    在这里插入图片描述
    查询 EMPLOYEES 表中的员 工编号、姓名、职称 和 用户在运行时指定的任何其它列

    SELECT employee_id, last_name, job_id,
           &column_name
    FROM employees
    WHERE &condition
    ORDER BY &order_column ;
    

    在这里插入图片描述
    在这里插入图片描述

    定义替代变量

    DEFINE 和 UNDEFINE 命令

    • 变量执行以下 任一 操作 之前 一直 保持 已定义状态
      • 使用 UNDEFIINE 命令清除它
      • 退出 iSQL*Plus
    • 可以使用 DEFINE 命令来验证更改

    使用 DEFINE 命令 处理 & 替代变量

    使用 DEFINE 命令创建一个员工编号的 iSQL*Plus 替代变量,并且在运行时显示该员工的员工编号、姓名、薪金和部门编号

    DEFINE employee_num = 200;
    
    SELECT employee_id, last_name, salary, department_id
    FROM employees
    WHERE employee_id = &employee_num ;
    

    在这里插入图片描述

    使用 && 替代变量

    SELECT employee_id, last_name, job_id, &&column_name
    FROM employees
    ORDER BY &column_name;
    

    在这里插入图片描述
    在这里插入图片描述

    使用 VERIFY 命令

    • 使用 VERIFY 命令可以切换替代变量的显示,分别显示 iSQL*Plus 用值替换替代变量前后的状况
    SET VERIFY ON
    SELECT employee_id, last_name, salary, department_id
    FROM employees
    WHERE employee_id = &employee_num;
    

    在这里插入图片描述

    在这里插入图片描述

    自定义 iSQL*Plus 环境

    SET 命令变量

    • 使用 SET 命令可以控制当前会话
    SET system variable value
    
    • 使用 SHOW 命令可以验证所设置的内容
    SET ECHO ON 
    
    SHOW ECHO
    echo ON
    
    • SET 命令变量
    命令意义
    ARRAYSIZE { 20 / n }arraysize设置数据库数据取数大小
    FEEDBACK { 6 / n / OFF / ON }feedback显示当查询选择了至少 n 条记录时将返回的记录的数量
    HEADING { OFF / ON }heading确定是否在报表中显示列标题
    LONG { 80 / n }long设置显示 LONG 值的最大宽度

    如:

    SET HEADING OFF
    
    SHOW HEADING
    HEADING OFF
    

    COLUMN 命令

    • COLUMN 命令选项
    命令意义
    CLEAR清除任何列格式
    HEADING text设置列标题(如果使用竖线(|),那么除非使用调整对齐,否则将强制在标题中换行)
    FORMAT format更改列数据的显示
    NOPRINT隐藏列
    NULL text指定在值为空时显示的文本
    PRINT显示列

    创建列标题

    COLUMN last_name HEADING 'Employee|Name'
    COLUMN salary JUSTIFY LEFT FORMAT $99,990.00
    COLUMN manager FORMAT 999999999 NULL 'No manager
    

    显示 LAST_NAME 当前设置

    COLUMN last_name
    

    在这里插入图片描述
    清除 LAST_NAME 设置

    COLUMN last_name CLEAR
    
    • COLUMN 格式样式
    命令用法结果
    9 取消前导零的数字999999123
    0 加上前导零099999001234
    $ 浮动的美元记号$9999$1234
    L 当地货币L9999L1234
    . 小数点的位置9999.991234.00
    , 千位分隔符9,9991,234

    BREAK 命令

    • 使用 BREAK 命令可以取消重复项
    BREAK ON job_id
    
    • 使用 CLEAR 命令可以清除所有的 BREAK 设置
    CLEAR BREAK
    

    TTITLE 和 BTITLE 命令

    • 显示 题头页脚
    TTITLE [text|OFF|ON]
    
    • 设置报表 题头
    TTITLE 'Salary|Report'
    
    • 设置报表页脚
    BTITLE 'Confidential'
    

    创建脚本文件以便运行报表

    • 如何创建脚本文件:
      1. SQL 提示符下创建 SQL SELECT 语句。确保报表所需的数据是准确的,然后再将语句保存到文件中,并应用格式命令。如果您要使用 BREAK 命令,请确保将相关的 ORDER BY 子句包含在内
      2. SELECT 语句保存到脚本文件中
      3. 编辑该脚本文件以便输入 iSQL*Plus 命令
      4. SELECT 语句之前添加必需的格式命令。一定不要将 iSQL*Plus 命令放置在 SELECT 语句中
      5. 确保 SELECT 语句后面跟着运行字符,可以是分号 ( ; ) 或斜线 ( / )
      6. 在运行字符之后添加清除格式的 iSQL*Plus 命令。或者,您可以将所有格式清除命令保存在一个重置文件中
      7. 将您的更改保存到该脚本文件中
      8. 将该脚本文件加载到 iSQL*Plus 文本窗口中,然后单击“执行”按钮
    • 准则
      • 在脚本中,iSQL*Plus 命令之间可以包含空白行
      • 如果 iSQL*PlusSQL*Plus 命令太长,则可以用一个连字符 ( - ) 终止当前行,然后在下一行继续该命令
      • 可以缩写 iSQL*Plus 命令
      • 将重置命令放置到文件的结尾处,可以还原最初的 iSQL*Plus 环境

    创建一个脚本文件,用它创建一个报表,以便显示薪金低于 $15,000 的所有员工的职务标识、姓氏和薪金。添加一个居中的两行题头 “Employee Report” 和一个居中的页脚 “Confidential” 。将职称列重命名为跨两行的 “Job Category” 。将员工姓名列重命名为 “Employee” 。将薪金列重命名为 “Salary” ,并将其格式设置为 $2,500.00 的样式。

    SET FEEDBACK OFF
    TTITLE 'Employee|Report'
    BTITLE 'Confidential'
    BREAK ON job_id
    COLUMN job_id HEADING 'Job|Category'
    COLUMN last_name HEADING 'Employee'
    COLUMN salary HEADING 'Salary' FORMAT $99,999.99
    REM ** Insert SELECT statement
    SELECT job_id, last_name, salary
    FROM employees
    WHERE salary < 15000
    ORDER BY job_id, last_name
    /
    REM clear all formatting commands ...
    SET FEEDBACK ON
    COLUMN job_id CLEAR
    COLUMN last_name CLEAR
    COLUMN salary CLEAR
    CLEAR BREAK
    ...
    

    第8章 处理数据

    向表中添加新行:INSERT 语句

    • 使用此语法一次只能添加一行
    • 为清楚起见,请在 INSERT 子句中使用列列表
    • 字符值日期值包含在单引号中,建议要将数字值包含在单引号
      • 因为如果使用了单引号,分配给 NUMBER 数据类型列的数字值可能会发生隐式转换

    插入新行

    INSERT INTO departments(department_id, department_name,
                manager_id, location_id)
    VALUES (70, 'Public Relations', 100, 1700);
    

    插入带有空值的行

    隐式:在列列表中省略该列

    INSERT INTO departments (department_id,
                department_name  )
    VALUES (30, 'Purchasing');
    

    显试:在 VALUES 子句中指定 NULL 关键字

    INSERT INTO departments
    VALUES (100, 'Finance', NULL, NULL);
    

    插入特殊值

    • SYSDATE 函数记录 当前 日期时间
    INSERT INTO employees (employee_id,
                first_name, last_name,
                email, phone_number,
                hire_date, job_id, salary,
                commission_pct, manager_id,
                department_id)
    VALUES (113,
           'Louis', 'Popp',
           'LPOPP', '515.124.4567',
           SYSDATE, 'AC_ACCOUNT', 6900,
           NULL, 205, 100);
    

    插入特定日期

    • 通常使用 DD-MON-YY 格式插入日期值,世纪默认为当前世纪,包含时间信息,默认时间为午夜 (00:00:00)
    • 如果必须以不同于默认格式的格式输入日期,例如要输入另一个世纪的日期或者特定时间,则必须使用 TO_DATE 函数

    在 EMPLOYEES 表中记录了员工 Raphealy 的信息,将 HIRE_DATE 列设置为1999年2月3日

    INSERT INTO employees
    VALUES (114,
           'Den', 'Raphealy',
           'DRAPHEAL', '515.127.4561',
           TO_DATE('FEB 3, 1999', 'MON DD, YYYY'),
           'AC_ACCOUNT', 11000, NULL, 100, 30);
    

    如果使用下面的语句而不是上述语句,hire_date 的年份将被解释为2099

    INSERT INTO employees
    VALUES (114,
           'Den', 'Raphealy',
           'DRAPHEAL', '515.127.4561',
           '03-FEB-99',
           'AC_ACCOUNT', 11000, NULL, 100, 30);
    

    创建脚本

    • SQL 语句中使用 & 替代变量来提示输入值
    • & 是变量值的占位符。
    INSERT INTO departments
                (department_id, department_name, location_id)
    VALUES (&department_id, '&department_name',&location);
    

    从其它表中复制行

    • 要使用 VALUES 子句。
    • 使 INSERT 子句中的列数与子查询中的列数匹配
    INSERT INTO sales_reps(id, name, salary, commission_pct)
                SELECT employee_id, last_name, salary, commission_pct
                FROM employees
                WHERE job_id LIKE '%REP%';
    

    创建表中行的复本,应在子查询中使用 SELECT *

    INSERT INTO copy_emp
                SELECT *
                FROM employees;
    

    更改表中的数据:UPDATE 语句

    • 使用 UPDATE 语句修改现有的行
    • 如果需要,一次可以更新个行

    更新表中的行

    • 如果指定了 WHERE 子句,则可以修改特定的某一行多个行
    UPDATE employees
    SET department_id = 70
    WHERE employee_id = 113;
    
    • 如果省略了 WHERE 子句,则会修改表中的所有行
    UPDATE copy_emp
    SET department_id = 110;
    

    用子查询更新两个列

    更新员工 114 的 职务 和 薪金,使之与员工 205 的 职务 和 薪金 相同

    UPDATE employees
    SET job_id = (SELECT job_id
                 FROM employees
                 WHERE employee_id = 205),
        salary = (SELECT salary
                 FROM employees
                 WHERE employee_id = 205)
    WHERE employee_id = 114;
    

    根据另一个表更新行

    根据另一个表中的 值,在 UPDATE 语句中使用 子查询 更新表中的 行

    UPDATE copy_emp
    SET department_id = (SELECT department_id
                        FROM employees
                        WHERE employee_id = 100)
    WHERE job_id = (SELECT job_id
                   FROM employees
                   WHERE employee_id = 200);
    

    从表中删除行:DELETE 语句

    从表中删除行

    • 如果指定了 WHERE 子句,则可以删除特定的行
    DELETE FROM departments
    WHERE department_name = 'Finance';
    
    • 如果省略了 WHERE 子句,则会删除表中的所有
    DELETE FROM copy_emp;
    

    根据另一个表删除行

    在 DELETE 语句中使用子查询,以便根据另一个表中的值来删除表中的行

    DELETE FROM employees
    WHERE department_id =
                          (SELECT department_id
                          FROM departments
                          WHERE department_name LIKE '%Public%');
    
    • 如果某行包含作为另一个表外键的主键,则能删除该行

    错误示例:
    示例尝试从 EMPLOYEES 表中删除部门编号60,但由于部门编号被用作EMPLOYEES表的外键,因而产生一个错误

    DELETE FROM departments
    WHERE department_id = 60;
    

    下面的语句之所以有效是因为部门70中没有员工

    DELETE FROM departments
    WHERE department_id = 70;
    

    在 INSERT 语句中使用子查询

    • 可以用子查询代替 INSERT 语句的 INTO 子句中的表名
    INSERT INTO
                (SELECT employee_id, last_name,
                email, hire_date, job_id, salary,
                department_id
                FROM employees
                WHERE department_id = 50)
    VALUES (99999, 'Taylor', 'DTAYLOR',
           TO_DATE('07-JUN-99', 'DD-MON-RR'),
           'ST_CLERK', 5000, 50);
    

    在 DML 语句中使用 WITH CHECK OPTION 关键字

    • 子查询用来指定 DML 语句的
    • WITH CHECK OPTION 关键字可以防止更改在子查询中的行
    • 指定 WITHCHECK OPTION 就指明了如下情况:
      • 如果在 INSERTUPDATEDELETE 语句中使用子查询代替表,则允许对该表进行更改以免产生子查询中包含的行

    示例中,使用了 WITH CHECKOPTION 关键字。子查询中指定了查询部门 50 中的行,但部门标识在 SELECT 中未指定,而且 VALUES 列表中也未提供部门标识的值。插入这样的行会产生为空值的部门标识,该部门标识不在子查询中

    INSERT INTO (SELECT employee_id, last_name, email,
                hire_date, job_id, salary
                FROM employees
                WHERE department_id = 50 WITH CHECK OPTION)
    VALUES (99998, 'Smith', 'JSMITH',
           TO_DATE('07-JUN-99', 'DD-MON-RR'),
           'ST_CLERK', 5000);
    

    显式默认功能:DEFAULT 语句

    • 利用显式默认功能,您可以在希望使用列默认值的地方将 DEFAULT 关键字作为列值
    • 这样用户就可以控制在何时何地将默认值应用于数据
    • 显式默认值可以在 INSERTUPDATE 语句中使用

    DEFAULT 用于 INSERT 语句

    示例中INSERT 语句使用了 MANAGER_ID 列的默认值。如果没有为该列定义默认值,则会插入一个空值

    INSERT INTO departments
                (department_id, department_name, manager_id)
    

    DEFAULT 用于 UPDATE 语句

    示例使用 UPDATE 语句将部门 10 的 MANAGER_ID 列设置为默认值。如果没有为该列定义默认值,则会将其更改为空值

    UPDATE departments
    SET manager_id = DEFAULT WHERE department_id = 10;
    

    合并行:MERGE 语句

    • 提供有条件地在数据库表中更新插入数据的功能
    • 如果该行存在就执行 UPDATE,如果是新行则执行 INSERT:
      • 避免单独更新
      • 增强性能和易用性
      • 在数据仓库应用程序中尤为有用
        在 COPY_EMP 表中插入或更新行,以便与 EMPLOYEES 表匹配
    MERGE INTO copy_emp c
               USING employees e
               ON (c.employee_id = e.employee_id)
    WHEN MATCHED THEN
         UPDATE SET
                    c.first_name = e.first_name,
                    c.last_name = e.last_name,
                    ...
                    c.department_id = e.department_id
    WHEN NOT MATCHED THEN
         INSERT VALUES (e.employee_id, e.first_name, e.last_name,
                       e.email, e.phone_number, e.hire_date, e.job_id,
                       e.salary, e.commission_pct, e.manager_id,
                       e.department_id);
    

    示例将 COPYEMP 表中的 EMPLOYEE_ID与 EMPLOYEES 表中的 EMPLOYEE_ID匹配。如果发现匹配项,就更新COPY_EMP 中的行,使之与 EMPLOYEES 表中的行匹配。如果没找到该行,则将其插入到COPY_EMP表中

    MERGE INTO copy_emp c
               USING employees e
               ON (c.employee_id = e.employee_id)
    WHEN MATCHED THEN
         UPDATE SET
                    c.first_name = e.first_name,
                    c.last_name = e.last_name,
                    c.email = e.email,
                    c.phone_number = e.phone_number,
                    c.hire_date = e.hire_date,
                    c.job_id = e.job_id,
                    c.salary = e.salary,
                    c.commission_pct = e.commission_pct,
                    c.manager_id = e.manager_id,
                    c.department_id = e.department_id
    WHEN NOT MATCHED THEN
         INSERT VALUES (e.employee_id, e.first_name, e.last_name,
                       e.email, e.phone_number, e.hire_date, e.job_id,
                       e.salary, e.commission_pct, e.manager_id,
                       e.department_id);
    

    数据库事务处理

    显式事务处理

    COMMIT 和 ROLLBACK 语句

    • 使用 COMMITROLLBACK 语句,可以:

      • 确保数据的一致性
      • 在使更改永久化之前预览数据更改
      • 逻辑关系对相关操作进行分组
    • 使用COMMITSAVEPOINTROLLBACK 语句可以控制事务处理的逻辑

    命令意义
    COMMIT将所有待定的数据更改永久化,从而结束当前的事务处理
    SAVEPOINT name在当前事务处理中标记一个保存点
    ROLLBACK放弃所有待定的数据更改,从而结束当前的事务处理
    ROLLBACK TO SAVEPOINT name将当前的事务处理回退指定的保存点,从而放弃从该保存点之后所作的所有 更改创建的保存点。如果省略了 TO SAVEPOINT 子句,ROLLBACK 语句将回退整个事务处理。由于保存点是逻辑的,因此无法列出您创建的保存点

    使用 SAVEPOINT 语句在当前事务处理中创建一个标记
    使用 ROLLBACK TO SAVEPOINT 语句回退到该标记处

    UPDATE...
    SAVEPOINT update_done;
    INSERT...
    ROLLBACK TO update_done;
    

    隐式事务处理

    COMMIT 或 ROLLBACK 操作之前数据的状态

    • 数据处理操作主要影响数据库缓冲区;因此可以将数据恢复到以前的状态
    • 当前用户可以通过对表进行查询来复查数据处理操作的结果
    • 其他用户无法查看当前用户进行的数据处理操作的结果
    • Oracle服务器将建立读一致性,从而确保每个用户看到的数据都是上次提交时的数据
    • 受影响的行将被锁定其他用户无法修改这些行中的数据

    COMMIT操作之后数据的状态

    • 数据更改被写入到数据库
    • 以前的数据状态永久丢失
    • 所有用户都可以查看该事务处理的结果
    • 受影响行的锁定被释放;其他用户可以对这些行进行的数据更改
    • 所有保存点都被清除

    提交数据

    进行更改

    DELETE FROM employees
    WHERE employee_id = 99999;
    
    INSERT INTO departments
    VALUES (290, 'Corporate Tax', NULL, 1700);
    

    提交更改

    COMMIT;
    

    ROLLBACK 操作之后数据的状态

    • 数据更改被撤消
    • 数据还原到以前的状态
    • 受影响行的锁定被释放

    语句级别回退

    • 如果在执行期间单个 DML 语句失败,则需回退语句。
    • Oracle 服务器实施隐式保存点。
    • 所有其它更改将被保留。
    • 用户应该通过执行 COMMITROLLLBACK 语句显式终止事务处理。

    读一致性

    • 保证数据库读取者写入者看到一致的数据
    • 保证读取者到正在被更改的数据
    • 保证写入者对数据库的更改在一致的方式下完成
    • 保证一个写入者进行的更改会打断另一个写入者正在进行的更改,也会与之冲突

    锁定

    • Oracle 数据库中,锁具有以下特性:
      • 防止并发事务处理之间的破坏性交互作用
      • 需要用户操作
      • 自动使用最低级别的限制
      • 在事务处理期间保持锁定
      • 具有两种类型:显式锁定和隐式锁定

    隐式锁定

    • 两种锁定模式:
      • 排它模式:允许其他用户访问
      • 共享模式:允许其他用户访问
    • 高级别数据并发:
      • DML:表共享,行排它
      • 查询:不需要锁定
      • DDL:保护对象定义
    • 在提交或回退之前一直保持锁定

    下接Oracle数据库中SQL语句用法(二)【点击以查看】

    展开全文
  • Oracle Study之---Oracle SQL语句中with的 一、官方对with使用的解释 About Oracle WITH clause  Starting in Oracle9i release 2 we see an incorporation of the SQL-99 “WITH clause”, a tool for ...
    Oracle Study之---Oracle SQL语句中with的
    一、官方对with使用的解释
    About Oracle WITH clause 
    Starting in Oracle9i release 2 we see an incorporation of the SQL-99 “WITH clause”, a tool for materializing subqueries to save Oracle from having to re-compute them multiple times.
    The SQL “WITH clause” is very similar to the use of Global temporary tables (GTT), a technique that is often used to improve query speed for complex subqueries. Here are some important notes about the Oracle “WITH clause”:
       • The SQL “WITH clause” only works on Oracle 9i release 2 and beyond.
       • Formally, the “WITH clause” is called subquery factoring
       • The SQL “WITH clause” is used when a subquery is executed multiple times
       • Also useful for recursive queries (SQL-99, but not Oracle SQL)
    To keep it simple, the following example only references the aggregations once, where the SQL “WITH clause” is normally used when an aggregation is referenced multiple times in a query. 
    We can also use the SQL-99 “WITH clause” instead of temporary tables. The Oracle SQL “WITH clause” will compute the aggregation once, give it a name, and allow us to reference it (maybe multiple times), later in the query.
    The SQL-99 “WITH clause” is very confusing at first because the SQL statement does not begin with the word SELECT. Instead, we use the “WITH clause” to start our SQL query, defining the aggregations, which can then be named in the main query as if they were “real” tables:

    with语法:
    WITH 
    subquery_name
    AS
    (the aggregation SQL statement)
    SELECT
    (query naming subquery_name);

    Retuning to our oversimplified example, let’s replace the temporary tables with the SQL “WITH  clause”:
    WITH
    sum_sales AS 
      select  
        sum(quantity) all_sales from stores
    number_stores AS 
      select  
        count(*) nbr_stores from stores
    sales_by_store AS
      select  
      store_name, sum(quantity) store_sales from 
      store natural join sales
    SELECT
       store_name
    FROM
       store,
       sum_sales,
       number_stores,
       sales_by_store
    where
       store_sales > (all_sales / nbr_stores)
    ;

    Note the use of the Oracle undocumented “materialize” hint in the “WITH clause”. The Oracle materialize hint is used to ensure that the Oracle cost-based optimizer materializes the temporary tables that are created inside the “WITH” clause. This is not necessary in Oracle10g, but it helps ensure that the tables are only created one time.
    It should be noted that the “WITH clause” does not yet fully-functional within Oracle SQL and it does not yet support the use of “WITH clause” replacement for “CONNECT BY” when performing recursive queries.
    To see how the “WITH clause” is used in ANSI SQL-99 syntax, here is an excerpt from Jonathan Gennick’s great work “Understanding the WITH Clause” showing the use of the SQL-99 “WITH clause” to traverse a recursive bill-of-materials hierarchy
    The SQL-99 “WITH clause” is very confusing at first because the SQL statement does not begin with the word SELECT. Instead, we use the “WITH clause” to start our SQL query, defining the aggregations, which can then be named in the main query as if they were “real” tables:

    with语法:
    WITH 
    subquery_name
    AS
    (the aggregation SQL statement)
    SELECT
    (query naming subquery_name);

    Retuning to our oversimplified example, let’s replace the temporary tables with the SQL “WITH” clause”:

    二、案例分析

    13:38:49 SCOTT@ test3 >with tt as
    13:38:56   2   (select deptno,sum(sal) sum_sal,avg(sal) avg_sal,max(sal) max_sal,min(sal) min_sal from emp group by deptno)
    13:39:02   3  select * from tt;

        DEPTNO    SUM_SAL    AVG_SAL    MAX_SAL    MIN_SAL
    ---------- ---------- ---------- ---------- ----------
            30       9400 1566.66667       2850        950
            20      10875       2175       3000        800
            10       8750 2916.66667       5000       1300

    Elapsed: 00:00:00.01

    13:39:07 SCOTT@ test3 >with t as
    13:41:10   2  (select * from emp)
    13:41:29   3  select * from t where deptno=10;

         EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
    ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
          7782 CLARK      MANAGER         7839 1981-06-09 00:00:00       2450                    10
          7839 KING       PRESIDENT            1981-11-17 00:00:00       5000                    10
          7934 MILLER     CLERK           7782 1982-01-23 00:00:00       1300                    10


    13:43:17 SCOTT@ test3 >with t1 as
    13:44:55   2    (select * from emp where deptno=10),
    13:44:55   3  t2 as
    13:44:55   4    (select * from emp where deptno=20)
    13:44:55   5    select * from t1
    13:44:55   6    union all
    13:44:55   7    select * from t2;

         EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
    ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
          7782 CLARK      MANAGER         7839 1981-06-09 00:00:00       2450                    10
          7839 KING       PRESIDENT            1981-11-17 00:00:00       5000                    10
          7934 MILLER     CLERK           7782 1982-01-23 00:00:00       1300                    10
          7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20
          7566 JONES      MANAGER         7839 1981-04-02 00:00:00       2975                    20
          7788 SCOTT      ANALYST         7566 1987-04-19 00:00:00       3000                    20
          7876 ADAMS      CLERK           7788 1987-05-23 00:00:00       1100                    20
          7902 FORD       ANALYST         7566 1981-12-03 00:00:00       3000                    20
    8 rows selected.
    Elapsed: 00:00:00.01

    1、查询出部门的总薪水大于所有部门平均总薪水的部门。部门表s_dept,员工表s_emp。分析:做这个查询,首先必须计算出所有部门的总薪水,然后计算出总薪水的平均薪水,再筛选出部门的总薪水大于所有部门总薪水平均薪水的部门。那么第1 步with 查询查出所有部门的总薪水,第2 步用with 从第1 步获得的结果表中查询出平均薪水,最后利用这两次 的with 查询比较总薪水大于平均薪水的结果,如下:
    12:49:32 SCOTT@ test3 >WITH DEPT_COSTS AS
    12:50:13   2   (SELECT D.DNAME, SUM(E.SAL) DEPT_TOTAL
    12:50:13   3  FROM DEPT D, EMP E
    12:50:13   4  WHERE E.DEPTNO = D.DEPTNO
    12:50:13   5   GROUP BY D.DNAME),
    12:50:13   6  AVE_COST AS
    12:50:13   7  (SELECT SUM(DEPT_TOTAL) / COUNT(*) AVG_SUM FROM DEPT_COSTS)
    12:50:13   8  SELECT dname from dept_costs,ave_cost where dept_total  >  avg_sum;
    DNAME
    --------------
    RESEARCH
    Elapsed: 00:00:00.32
    12:50:21 SCOTT@ test3 >


    以下内容转自网络:

    转自:http://www.linuxidc.com/Linux/2012-01/52108.htm

    1)  不用实际建表,可以轻松构建一个临时表,通过对这个表的处理测试一些功能;

    例如:with t as (

    select '010-82696948' telfrom dualunionall
    select'020 82167684'from dualunion all
    select'010-62102147\62104404'from dualunion all
    select'0860476-82321383'from dualunion all
    select'020-28876096'from dualunion all
    select'010-67260464-分机'from dual)
    select '086-0'||regexp_replace(replace(regexp_substr(tel,'[0-9]+[- ][0-9]{7}',1,1),'','-'),'^[0]*86[0]|^0','')from t;
    --对各种格式电话号码做规范化处理

    2)  复杂的查询会产生很大的sql,with table as语法可以把一些公共查询提出来,也可以显示一个个中间结果,可以使整个sql语句显得有条理些,可读性提高;
    3)  前面的中间结果可以被语句中的select或后面的中间结果表引用,类似于一个范围仅限于本语句的临时表,在需要多次查询某中间结果时可以提升效率 ,特别是对一些大数据量的表做多项统计时,可以大大提高效率。 

    例如: 

    with a as (select * from dba_objects where 某些查询条件),
         b as (select * from a where 某些查询条件)
         select * from b , a  where 其它查询条件;
    再比如:
    with tb as (select * from dba_objects where 某些查询条件),
              select count(*) from tb  where 其它查询条件1
              union
              select count(*) from tb  where 其它查询条件2
              union
              select count(*) from tb  where 其它查询条件3;

    1、with table as 相当于建个临时表(用于一个语句中某些中间结果放在临时表空间的SQL语句),Oracle 9i 新增WITH语法,可以将查询中的子查询命名,放到SELECT语句的最前面。
    语法就是
    with tempname as (select ....)
    select ...

    例子:
    with t as
     (select * from emp where depno=10)
    select * from t where empno=xxx

    with wd as 
    (select did,arg(salary) 平均工资 from work group by did),
    em as (select emp.*,w.salary from emp left join work w on emp.eid = w.eid)
    select * from wd,em where wd.did =em.did and wd.平均工资>em.salary;

    2、何时被清除
    临时表不都是会话结束就自动被PGA清除嘛! 但with as临时表是查询完成后就被清除了!
    23:48:58 SCOTT@orcl> with aa as(select * from dept)
    23:57:58   2  select * from aa;

        DEPTNO DNAME          LOC
    ---------- -------------- -------------
            10 ACCOUNTING     NEW YORK
            20 RESEARCH       DALLAS
            30 SALES          CHICAGO
            40 OPERATIONS     BOSTON
    已用时间:  00: 00: 00.12
    23:58:06 SCOTT@orcl> select * from aa;
    select * from aa
                  *
    第 1 行出现错误:
    ORA-00942: 表或视图不存在

    3、举例

    假定有张很大的表,有几年来的经营数据,数据量很大。如果要统计一段时间内的邮件状态,如果都从总表中统计,效率一定不高,而采用with tablename as 语句,先将一段时间内的数据取出来,再进行统计就会简单的多。
    with tb as (
    select b.city,a.mail_num,a.rcv_area from tb_evt_mail_clct a, tb_jg b
             where a.clct_date = to_date('20110816', 'yyyymmdd')
               and (a.rcv_area like '23%' or a.rcv_area like '24%')
               and a.clct_bureau_org_code = b.zj_code
               and not exists (select 1 from tb_evt_dlv c
                     where c.mail_num = a.mail_num
                       and c.dlv_sts_code = 'I')
                       )   -- 提取出查询数据
    select aa.city 收寄城市, aa.wtt 未妥投, bb.wtd 未投递, cc.wkc 未开拆
      from (select tb.city, count(*) wtt
              from tb
             group by tb.city) aa  -- 统计1
      left join (select tb.city, count(*) wtd
                   from tb
                    where  not exists
                  (select 1 from tb_evt_dlv c
                          where c.mail_num = tb.mail_num
                            and (c.dlv_sts_code = 'H' or c.dlv_sts_code = 'I'))
                  group by tb.city) bb on bb.city = aa.city  -- 统计2
      left join (select tb.city, count(*) wkc 
                   from tb
                  where not exists
                  (select 1  from tb_evt_dlv c
                          where c.mail_num = tb.mail_num
                            and (c.dlv_sts_code = 'H' or c.dlv_sts_code = 'I'))
                    and not exists
                  (select 1 from tb_evt_bag_mail_rela e
                          where e.mail_num = tb.mail_num
                            and e.bag_actn_code = '2'
                            and e.deal_org_code like
                              substr(tb.rcv_area, 1, 4) || '%')
                  group by tb.city) cc on cc.city = aa.city -- 统计3

    转自:http://my.oschina.net/GeminiLiu/blog/191651
    经常在开发过程中会用到视图或组合查询的情况,但由于涉及表数据经常达到千万级别的笛卡尔积,而且一段查询时会反复调用,但结果输出往往不需要那么多,可以使用with将过滤或处理后的结果先缓存到临时表(此处原理不太清楚,仅代表我的理解),可以大大提高查询效率
    /*
    查询当前EOMS流程组及组织架构信息
    create by Gemini.Liu
    2014-01-07
    */
    --create or replace view  v_getcurrent_workflow as
    --组信息
    with group_info as
    (
    select gp1.group_intid,gp1.group_id,gp1.group_name,gp1.group_fullname,
           decode(gp1.group_type,3,gp1.group_fullname,2,gp1.group_fullname,gp2.group_fullname) arch,
           decode(gp1.group_type,3,gp1.group_id,2,gp1.group_id,gp2.group_id) archid
    from ultraprocess_sysgroup gp1,ultraprocess_sysgroup gp2
    where 1=1
    and gp1.group_parentid=gp2.group_id
    and (gp2.group_type = 3 or gp2.group_type = 2)
    ),
    --组成员
    group_user as
    (
    select WMSYS.WM_CONCAT(us1.user_fullname) person,WMSYS.WM_CONCAT(us1.user_loginname) personid,us2.group_intid from ultraprocess_sysuser us1,ultraprocess_sysgroup us2,ultraprocess_sysgroupuser us3
    where 1=1
    and us1.user_id=us3.mgroup_userid
    and us2.group_id=us3.mgroup_groupid
    group by us2.group_intid
    ),
    --映射信息
    dp_map as
    (
    select hj.groupid,hj.processbaseschema from wf_app_dealprocess hj
    where  1=1
    and  hj.groupid is not null
    and  hj.processbaseschema is not null
    and  hj.edprocessaction<> 19
    group by groupid,processbaseschema
    )
    /*
    --工单信息
    ,form_info as
    (
    select info.basesn,info.baseschema,info.basename,info.basestatus,info.baseid,hj.group_x,hj.groupid,hj.flagactive from wf_app_base_infor info,wf_app_dealprocess hj
    where 1=1
    and info.baseid=hj.processbaseid
    and info.baseschema = hj.processbaseschema
    and info.basestatus <> '已作废'
    and hj.edprocessaction<> 19
    )
    */
    --展现信息
    select dp_map.processbaseschema 工单类别,
    group_info.group_intid 组ID,
    group_info.group_name 组名,
    group_user.person 组成员,
    group_user.personid 组成员登录名,
    group_info.arch 所属部门,
    group_info.archid 所属部门ID
    from dp_map,group_info,group_user
    where 1=1 
    and dp_map.groupid = group_info.group_intid
    and group_user.group_intid = group_info.group_intid


    转自:http://www.cnblogs.com/linjiqin/archive/2013/06/24/3152667.html
    --相当于建了个e临时表
    with e as (select * from scott.emp e where e.empno=7499)
    select * from e;
     
    --相当于建了e、d临时表
    with
         e as (select * from scott.emp),
         d as (select * from scott.dept)
    select * from e, d where e.deptno = d.deptno;
    其实就是把一大堆重复用到的sql语句放在with as里面,取一个别名,后面的查询就可以用它,这样对于大批量的sql语句起到一个优化的作用,而且清楚明了。


    向一张表插入数据的with as用法

    insert into table2
    with
        s1 as (select rownum c1 from dual connect by rownum <= 10),
        s2 as (select rownum c2 from dual connect by rownum <= 10)
    select a.c1, b.c2 from s1 a, s2 b where...;
    select s1.sid, s2.sid from s1 ,s2需要有关联条件,不然结果会是笛卡尔积。

    with as 相当于虚拟视图。

    with as短语,也叫做子查询部分(subquery factoring),可以让你做很多事情,定义一个sql片断,该sql片断会被整个sql语句所用到。有的时候,是为了让sql语句的可读性更高些,也有可能是在union all的不同部分,作为提供数据的部分。
      
    特别对于union all比较有用。因为union all的每个部分可能相同,但是如果每个部分都去执行一遍的话,则成本太高,所以可以使用with as短语,则只要执行一遍即可。如果with as短语所定义的表名被调用两次以上,则优化器会自动将with as短语所获取的数据放入一个temp表里,如果只是被调用一次,则不会。而提示materialize则是强制将with as短语里的数据放入一个全局临时表里。很多查询通过这种方法都可以提高速度。

    with
        sql1 as (select to_char(a) s_name from test_tempa),
        sql2 as (select to_char(b) s_name from test_tempb where not exists (select s_name from sql1 where rownum=1))
    select * from sql1
    union all
    select * from sql2
    union all
    select 'no records' from dual
           where not exists (select s_name from sql1 where rownum=1)
           and not exists (select s_name from sql2 where rownum=1);
    with as优点
    增加了sql的易读性,如果构造了多个子查询,结构会更清晰;
    更重要的是:“一次分析,多次使用”,这也是为什么会提供性能的地方,达到了“少读”的目标
    展开全文
  • 以下是对Oracle中PL/SQL中if语句的写法进行了详细的分析介绍,需要的朋友可以过来参考下
  • C#操作Oracle时的SQL语句参数的用法

    千次阅读 2010-03-10 20:09:00
    C#操作Oracle时的SQL语句参数的用法 OracleTransaction myTrans ; conn.Open(); myTrans =conn.BeginTransaction(IsolationLevel.ReadCommitted); comm.Transaction =myTrans; 
  • Oracle SQL语句大全

    千次阅读 2013-05-04 13:04:11
    Oracle SQL语句大全 2009-04-1315:39 1.desc(描述) emp 描述emp这张表 2.desc dept 部门表 3.desc salgrade 薪水等级 4.select *from table 查找表的元素 5.dual 是系统的一张空表 6....
  • 我们在orcal写查询sql语句时,往往会遇到判断某个字段的值并对其进行转码成我们需要的值,例如:数据库字段“是否贵宾客户”存的值为0、1,0表示是,1表示否,在我们查询时如果不在sql进行转码的话。...
  • Oracle SQL中IF ELSE

    万次阅读 2014-12-17 22:32:56
    SQL 的世界 CASE 语句有类似的效果。下面简单的介绍 CASE 语句用法。考虑下面的情况,假设有个 USER_INFO 表,定义如下: CREATE TABLE USER_INFO ( NAME VARCHAR2(20) NOT NULL, ---姓名 GENDE
  • ORACLE动态SQL语句

    万次阅读 2017-11-03 12:06:32
    问题的提出我们经常需要运行可变化的SQL语句,这种通常称为动态SQL,在ORACLE中执行动态的SQL语句,需要了解ORACLE的动态SQL语句的相关规定。 SQL动态语句是由程序或者存储过程生成的SQL语句,这种语句的特点是,不...
  • 在ABAP的SQL语句中Oracle Hints

    千次阅读 2017-10-16 14:18:17
    在ABAP的SQL语句中Oracle Hints ①用过的两个写法: 1、指定使用全表扫描:%_HINTS ORACLE 'FULL(table_name)' 2、指定索引:%_HINTS ORACLE 'INDEX(table_name index_name)' 其他Oracle Hints的写法可以...
  • Oracle 动态SQL语句

    千次阅读 2016-07-04 12:18:10
    EXECUTE IMMEDIATE代替了以前Oracle8iDBMS_SQLpackage包.  它解析并马上执行动态的SQL语句或非运行时创建的PL/SQL块.动态创建和执行SQL语句性能超前,EXECUTEIMMEDIATE
  • sql语句 sum 用法SQL provides different statements in order to select into existing table, variable, temp table in various database servers like Oracle, MySQL, PostgreSQL etc. In this tutorial we will ...
  • Oracle-sql语句中的判断

    2020-07-28 23:27:34
    1、select查询语句中if 判断,使用DECODE 函数(推荐),CASE 表达式(繁琐不推荐) decode(条件,值1,返回值1,值2,返回值2,…值n,返回值n,缺省值) sign(value)函数会根据value的值为0,正数,负数,分别返回0,1...
  • Oracle with语句用法

    千次阅读 2011-11-30 14:44:45
    Oracle with语句是经常可以见到的语句,下面就为您详细介绍Oracle with语句用法,如果您对Oracle with语句感兴趣的话,不妨一看。 当查询多次用到某一部分时,可以用Oracle with语句创建一个公共临时表。因为子...
  • 1、如何使用if语句 2、接受一个键盘输入(字符串) */ set serveroutput on --接受一个键盘输入 accept num prompt '请输入一个数字' --num:地址值,含义是:在该地址保存了输入的值 declare --
  • oracle常用sql语句

    千次阅读 2017-08-19 10:25:49
    – 首先,以超级管理员的身份登录oracle sqlplus sys/bjsxt as sysdba –然后,解除对scott用户的锁 alter user scott account unlock; –那么这个用户名就能使用了。 –(默认全局数据库名orcl) 1、select ...
  • Oracle中PL/SQLIF条件判断语句的使用

    万次阅读 2018-07-17 14:27:06
    PL/SQL IF条件判断语句 1、IF 语法: IF 条件 THEN 语句; END IF; 例子:如果从控制台输入数字1则输出“我是数字1” --打开屏幕输出开关(默认没有打开的) set serveroutput on; --接受键盘输入,变量num:...
  • 1.经典的select sql语句 //注意:包含空值的数学表达式求出的结果为空值 SQL> select sal+comm from emp; //连接员工编号与员工姓名这两个字段 SQL> select empno||ename as "员工编号和员工姓名" from emp; /...
  • Oracle常用SQL语句大全

    2020-06-02 11:10:11
    ORACLE常用SQL语句大全 一、基础1、说明:创建数据库 CREATE DATABASE database-name 2、说明:删除数据库 drop database dbname 3、说明:备份sql server--- 创建 备份数据的 device USE master EXEC sp_...
  • 不同的数据库,存储过程中if else 语句写法有一些差别。 如果是SQLServer数据库,存储过程的if, else语句可以这样写: 1 2 3 4 5 6 7 8 9 10 11 12 if?a>b Begin ???print?'a' End Else?if?a<b Begin...
  • ORACLE常用SQL语句大全

    千次阅读 2015-09-20 00:34:12
    ORACLE常用SQL语句大全 一、基础 1、说明:创建数据库 CREATE DATABASE database-name 2、说明:删除数据库 drop database dbname 3、说明:备份sql server --- 创建 备份数据的 device USE ...
  • oracle 常用sql语句

    千次阅读 2017-09-24 20:08:21
    -- 首先,以超级管理员的身份登录oracle  sqlplus sys/bjsxt as sysdba    --然后,解除对scott用户的锁  alter user scott account unlock;  --那么这个用户名就能使用了。  --(默认全局数据库名orcl) ...
  • oracle中复杂SQL语句——提升版

    千次阅读 多人点赞 2018-06-25 21:45:58
    一、SQL语句的执行顺序 1、select 、 from 、where(group by、 having、 order by) 语句的执行顺序是什么呢? select ——从纵向上进行过滤 from —— 一个表或多张表的连接 where —— 从横向上进行过滤 ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 24,684
精华内容 9,873
关键字:

oracle的sql语句中if的用法