• OCP之黄伟


    参考视频:

     [bbk5486]第4集 - 10g OCP之007研究

     [bbk5487]第5集 - 10g OCP之007研究

     [bbk5488]第6集 - 10g OCP之007研究

    Cluster table适合查询,因为聚簇表会将相关联的表信息数据存放在一个块中,这样读取数据时,会一次I/O读到全部信息,减少I/O,提高性能.

    多表联合查询.不适合DML操作;

    不适合TRUNCATE操作.不适合做全表扫描,因为你在进行I/O操作的时候,需要将相关联的、但是实际不需要的额外数据给读取出来,所以不适合全表扫描;

    聚簇表,分为基于索引的和基于哈希的;

    如果没有索引,是拒绝DML操作的.所以是先要进行创建索引,然后再进行DML操作.

    在聚簇表中,相同的ROWID不能完全定位唯一一条记录,有可能是多条.这是聚簇表与普通heap表的区别.也就是说,在聚簇表中,表dept和表emp中拥有完全重复的rowid,是不足为奇的,不重复,就不正常了.

    OCP 007课程概述

    1. 数据查询语言:SELECT;
    2. 数据定义语言:CREATE、ALTER、DROP、TRUNCATE、RENAME
    3. 数据修改语言:INSERT、UPDATE、DELETE、MERGE;
    4. 数据控制语言:GRANT、REVOKE;
    5. 事务控制语言:COMMIT、ROLLBACK、SAVEPOINT;
    6. 会话控制语言:ALTER SESSION;
    7. 系统控制语言:ALTER SYSTEM;

    Writing SQL Statements

    • SQL statements are not case-sensitive.
    • SQL statements can be on one or more lines.
    • Keywords cannot be abbreviated or split across lines.
    • Clauses are usually placed on separate lines.
    • Indents are used to enhance readability.
    • In iSQL*Plus,SQL statements can optionally be terminated by a semicolon(;).Semicolons are required if you execute multiple SQL statements.
    • In SQL*plus,you are required to end eac SQL statement with a semicolon(;);

    Column Heading Defaults

    • iSQL*Plus
      • Default heading alignment:Center
      • Default heading display:Uppercase
    • SQL*Plus
      • Character and Date column heading are Left-aligned.
      • Number column headings are right-aligned.
      • Default heading display:Uppercase.

    Defining a Null Value

    A null is a value that is unabailable,unassigned,unknown,or inapplicable.

    A null is not the same as a zero or a blank space.

    null value,就是没有分配内存地址,不知道,不适用的这么一个值.

    null value是不能做等值比较的.只能用is null或者is not null;

    注意:空值和空值是不相等的;

    在唯一约束上,是允许有空值的;而且允许又多个空值.因为空值和空值是不相等的。

    凡是空值参与的所有运算,最后的结果都是空值.

    Defining a Column Alias

    A column alias:

    • Renames a column heading
    • Is useful with calculations
    • Immediately follows the column name(There can also be the optional AS keyword between the column name nad alias.) 
    • Requires double quotation marks if it contains spaces or special characters or if it is case-sensitive 
    • Can`t be used in Where clause

    Concatenation Operator

    注意:列别名中使用的是双引号,而字符串拼接使用的是单引号.

    A concatenation operator:

    • Links columns or character strings to other columns
    • Is represented by two vertical bars(||)
    • Creates a resultant column that is a character expression
    SQL> select last_name || chr(39) || salary from emp;
    
    LAST_NAME||CHR(39)||SALARY
    ------------------------------------------------------------------
    Hartstein'13000
    Fay'6000
    Raphaely'11000
    Khoo'3100
    Baida'2900
    Tobias'2800
    Himuro'2600
    Colmenares'2500
    OConnell'2600
    Grant'2600
    Weiss'8000
    
    LAST_NAME||CHR(39)||SALARY
    ------------------------------------------------------------------
    Fripp'8200
    Kaufling'7900
    Vollman'6500
    Mourgos'5800
    Nayer'3200
    Mikkilineni'2700
    Landry'2400
    Markle'2200
    Bissot'3300
    Atkinson'2800
    Marlow'2500
    
    LAST_NAME||CHR(39)||SALARY
    ------------------------------------------------------------------
    Olson'2100
    Mallin'3300
    Rogers'2900
    Gee'2400
    Philtanker'2200
    Ladwig'3600
    Stiles'3200
    Seo'2700
    Patel'2500
    Rajs'3500
    Davies'3100
    
    LAST_NAME||CHR(39)||SALARY
    ------------------------------------------------------------------
    Matos'2600
    Vargas'2500
    Taylor'3200
    Fleaur'3100
    Sullivan'2500
    Geoni'2800
    Sarchand'4200
    Bull'4100
    Dellinger'3400
    Cabrio'3000
    Chung'3800
    
    LAST_NAME||CHR(39)||SALARY
    ------------------------------------------------------------------
    Dilly'3600
    Gates'2900
    Perkins'2500
    Bell'4000
    Everett'3900
    McCain'3200
    Jones'2800
    Walsh'3100
    Feeney'3000
    Mavris'6500
    Whalen'4400
    
    LAST_NAME||CHR(39)||SALARY
    ------------------------------------------------------------------
    Higgins'12008
    Gietz'8300
    Hunold'9000
    Ernst'6000
    Austin'4800
    Pataballa'4800
    Lorentz'4200
    Baer'10000
    Russell'14000
    Partners'13500
    Errazuriz'12000
    
    LAST_NAME||CHR(39)||SALARY
    ------------------------------------------------------------------
    Cambrault'11000
    Zlotkey'10500
    Tucker'10000
    Bernstein'9500
    Hall'9000
    Olsen'8000
    Cambrault'7500
    Tuvault'7000
    King'10000
    Sully'9500
    McEwen'9000
    
    LAST_NAME||CHR(39)||SALARY
    ------------------------------------------------------------------
    Smith'8000
    Doran'7500
    Sewall'7000
    Vishney'10500
    Greene'9500
    Marvins'7200
    Lee'6800
    Ande'6400
    Banda'6200
    Ozer'11500
    Bloom'10000
    
    LAST_NAME||CHR(39)||SALARY
    ------------------------------------------------------------------
    Fox'9600
    Smith'7400
    Bates'7300
    Kumar'6100
    Abel'11000
    Hutton'8800
    Taylor'8600
    Livingston'8400
    Johnson'6200
    King'24000
    Kochhar'17000
    
    LAST_NAME||CHR(39)||SALARY
    ------------------------------------------------------------------
    De Haan'17000
    Greenberg'12008
    Faviet'9000
    Chen'8200
    Sciarra'7700
    Urman'7800
    Popp'6900
    Grant'7000
    View Code
    SQL> select last_name || '''s' || salary from emp;
    
    LAST_NAME||'''S'||SALARY
    -------------------------------------------------------------------
    Hartstein's13000
    Fay's6000
    Raphaely's11000
    Khoo's3100
    Baida's2900
    Tobias's2800
    Himuro's2600
    Colmenares's2500
    OConnell's2600
    Grant's2600
    Weiss's8000
    
    LAST_NAME||'''S'||SALARY
    -------------------------------------------------------------------
    Fripp's8200
    Kaufling's7900
    Vollman's6500
    Mourgos's5800
    Nayer's3200
    Mikkilineni's2700
    Landry's2400
    Markle's2200
    Bissot's3300
    Atkinson's2800
    Marlow's2500
    
    LAST_NAME||'''S'||SALARY
    -------------------------------------------------------------------
    Olson's2100
    Mallin's3300
    Rogers's2900
    Gee's2400
    Philtanker's2200
    Ladwig's3600
    Stiles's3200
    Seo's2700
    Patel's2500
    Rajs's3500
    Davies's3100
    
    LAST_NAME||'''S'||SALARY
    -------------------------------------------------------------------
    Matos's2600
    Vargas's2500
    Taylor's3200
    Fleaur's3100
    Sullivan's2500
    Geoni's2800
    Sarchand's4200
    Bull's4100
    Dellinger's3400
    Cabrio's3000
    Chung's3800
    
    LAST_NAME||'''S'||SALARY
    -------------------------------------------------------------------
    Dilly's3600
    Gates's2900
    Perkins's2500
    Bell's4000
    Everett's3900
    McCain's3200
    Jones's2800
    Walsh's3100
    Feeney's3000
    Mavris's6500
    Whalen's4400
    
    LAST_NAME||'''S'||SALARY
    -------------------------------------------------------------------
    Higgins's12008
    Gietz's8300
    Hunold's9000
    Ernst's6000
    Austin's4800
    Pataballa's4800
    Lorentz's4200
    Baer's10000
    Russell's14000
    Partners's13500
    Errazuriz's12000
    
    LAST_NAME||'''S'||SALARY
    -------------------------------------------------------------------
    Cambrault's11000
    Zlotkey's10500
    Tucker's10000
    Bernstein's9500
    Hall's9000
    Olsen's8000
    Cambrault's7500
    Tuvault's7000
    King's10000
    Sully's9500
    McEwen's9000
    
    LAST_NAME||'''S'||SALARY
    -------------------------------------------------------------------
    Smith's8000
    Doran's7500
    Sewall's7000
    Vishney's10500
    Greene's9500
    Marvins's7200
    Lee's6800
    Ande's6400
    Banda's6200
    Ozer's11500
    Bloom's10000
    
    LAST_NAME||'''S'||SALARY
    -------------------------------------------------------------------
    Fox's9600
    Smith's7400
    Bates's7300
    Kumar's6100
    Abel's11000
    Hutton's8800
    Taylor's8600
    Livingston's8400
    Johnson's6200
    King's24000
    Kochhar's17000
    
    LAST_NAME||'''S'||SALARY
    -------------------------------------------------------------------
    De Haan's17000
    Greenberg's12008
    Faviet's9000
    Chen's8200
    Sciarra's7700
    Urman's7800
    Popp's6900
    Grant's7000
    
    107 rows selected.
    View Code

    '''s'->解读:第一个和第四个单引号是一对,第二个单引号是转义含义,第三个单引号是真正的字符.

    chr(39)含义与'''s'是相同的,都是返回的`s这个值.

    Literal Character Strings

    • A literal is a character,a number,or a date that is included in the SELECT statement.
    • Date and character literal values must be enclosed by single quotation marks.
    • Each character string is output once for each row returned.
    --二者是等价的
    SQL> select distinct department_id from employees; SQL> select unique department_id from employees;

    在排序的过程中,如果按照带有空值的字段进行排序,默认情况下是将空值字段放在最后的;之所以将控制放在最后,是因为oracle是将空值当做无穷大来处理的.

    SQL> select last_name,commission_pct from employees order by 2;
    
    LAST_NAME                 COMMISSION_PCT
    ------------------------- --------------
    Lee                                   .1
    Johnson                               .1
    Marvins                               .1
    Banda                                 .1
    Kumar                                 .1
    Ande                                  .1
    Greene                               .15
    Grant                                .15
    Tuvault                              .15
    Bates                                .15
    Smith                                .15
    
    LAST_NAME                 COMMISSION_PCT
    ------------------------- --------------
    Taylor                                .2
    Bloom                                 .2
    Fox                                   .2
    Cambrault                             .2
    Livingston                            .2
    Zlotkey                               .2
    Olsen                                 .2
    Sewall                               .25
    Hall                                 .25
    Bernstein                            .25
    Vishney                              .25
    
    LAST_NAME                 COMMISSION_PCT
    ------------------------- --------------
    Hutton                               .25
    Ozer                                 .25
    Abel                                  .3
    Smith                                 .3
    Partners                              .3
    Errazuriz                             .3
    Tucker                                .3
    Cambrault                             .3
    Doran                                 .3
    King                                 .35
    Sully                                .35
    
    LAST_NAME                 COMMISSION_PCT
    ------------------------- --------------
    McEwen                               .35
    Russell                               .4
    OConnell
    Grant
    Whalen
    Hartstein
    Fay
    Mavris
    Baer
    Higgins
    Gietz
    
    LAST_NAME                 COMMISSION_PCT
    ------------------------- --------------
    King
    Kochhar
    De Haan
    Hunold
    Ernst
    Austin
    Pataballa
    Lorentz
    Greenberg
    Faviet
    Chen
    
    LAST_NAME                 COMMISSION_PCT
    ------------------------- --------------
    Sciarra
    Urman
    Popp
    Raphaely
    Khoo
    Baida
    Tobias
    Himuro
    Colmenares
    Weiss
    Fripp
    
    LAST_NAME                 COMMISSION_PCT
    ------------------------- --------------
    Kaufling
    Vollman
    Mourgos
    Nayer
    Mikkilineni
    Landry
    Markle
    Bissot
    Atkinson
    Marlow
    Olson
    
    LAST_NAME                 COMMISSION_PCT
    ------------------------- --------------
    Mallin
    Rogers
    Gee
    Philtanker
    Ladwig
    Stiles
    Seo
    Patel
    Rajs
    Davies
    Matos
    
    LAST_NAME                 COMMISSION_PCT
    ------------------------- --------------
    Vargas
    Taylor
    Fleaur
    Sullivan
    Geoni
    Sarchand
    Bull
    Dellinger
    Cabrio
    Chung
    Dilly
    
    LAST_NAME                 COMMISSION_PCT
    ------------------------- --------------
    Gates
    Perkins
    Bell
    Everett
    McCain
    Jones
    Walsh
    Feeney
    
    107 rows selected.
    View Code

    假如想将控制字段排序时,放在前面可以在order by 子句后面添加关键字nulls first;想将空值放在最后,就将nulls first改为nulls last;

    SQL> select last_name,commission_pct from employees order by 2 nulls first;
    
    LAST_NAME                 COMMISSION_PCT
    ------------------------- --------------
    OConnell
    Grant
    Whalen
    Hartstein
    Fay
    Mavris
    Baer
    Higgins
    Gietz
    King
    Kochhar
    
    LAST_NAME                 COMMISSION_PCT
    ------------------------- --------------
    De Haan
    Hunold
    Ernst
    Austin
    Pataballa
    Lorentz
    Greenberg
    Faviet
    Chen
    Sciarra
    Urman
    
    LAST_NAME                 COMMISSION_PCT
    ------------------------- --------------
    Popp
    Raphaely
    Khoo
    Baida
    Tobias
    Himuro
    Colmenares
    Weiss
    Fripp
    Kaufling
    Vollman
    
    LAST_NAME                 COMMISSION_PCT
    ------------------------- --------------
    Mourgos
    Nayer
    Mikkilineni
    Landry
    Markle
    Bissot
    Atkinson
    Marlow
    Olson
    Mallin
    Rogers
    
    LAST_NAME                 COMMISSION_PCT
    ------------------------- --------------
    Gee
    Philtanker
    Ladwig
    Stiles
    Seo
    Patel
    Rajs
    Davies
    Matos
    Vargas
    Taylor
    
    LAST_NAME                 COMMISSION_PCT
    ------------------------- --------------
    Fleaur
    Sullivan
    Geoni
    Sarchand
    Bull
    Dellinger
    Cabrio
    Chung
    Dilly
    Gates
    Perkins
    
    LAST_NAME                 COMMISSION_PCT
    ------------------------- --------------
    Bell
    Everett
    McCain
    Jones
    Walsh
    Feeney
    Lee                                   .1
    Johnson                               .1
    Marvins                               .1
    Banda                                 .1
    Kumar                                 .1
    
    LAST_NAME                 COMMISSION_PCT
    ------------------------- --------------
    Ande                                  .1
    Greene                               .15
    Grant                                .15
    Tuvault                              .15
    Bates                                .15
    Smith                                .15
    Taylor                                .2
    Bloom                                 .2
    Fox                                   .2
    Cambrault                             .2
    Livingston                            .2
    
    LAST_NAME                 COMMISSION_PCT
    ------------------------- --------------
    Zlotkey                               .2
    Olsen                                 .2
    Sewall                               .25
    Hall                                 .25
    Bernstein                            .25
    Vishney                              .25
    Hutton                               .25
    Ozer                                 .25
    Abel                                  .3
    Smith                                 .3
    Partners                              .3
    
    LAST_NAME                 COMMISSION_PCT
    ------------------------- --------------
    Errazuriz                             .3
    Tucker                                .3
    Cambrault                             .3
    Doran                                 .3
    King                                 .35
    Sully                                .35
    McEwen                               .35
    Russell                               .4
    
    107 rows selected.
    View Code

    SQL statements Versus iSQL*Plus Commands

    SQL iSQL*Plus
    A language An enviromnet
    ANSI standard Oracle-proprietary
    Keyword cannot be abbreviated Keywords can be abbreviated
    Statements manipulate data and table definitions in the database Commands do not allow manipulation of values in the database
      Runs on a browser
      Centrally loaded;does not have to be implemented on each machine
    SQL statements iSQL*Plus commands

    Summary

    In this lesson,you should be have learned how to:

    • Write a SELECT statement that:
      • Returns all rows and columns from a table.
      • Returns specified columns from a table.
      • Uses column aliases to display more descriptive column headings
    • Use the iSQL*Plus enviroments to write,save,and execute SQL statements and iSQL*Plus command.
    SELECT *|{[DISTINCT] column|expression [alias],...} FROM table;
  • 相关阅读:
    urllib2使用总结
    Scrapy简介
    python3使用多代理访问网站
    ISO9000 质量管理和质量保证系列国际标准
    怎样花两年时间去面试一个人
    Robot Framework 快速入门_中文版
    PMP项目经理认证
    Scrapy安装介绍
    批处理写的俄罗斯方块
    TL9000 电信业质量体系管理标准
  • 原文地址:https://www.cnblogs.com/arcer/p/3156450.html
Copyright © 2020-2023  润新知