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


OCP之黄伟_sqlOCP之黄伟_数据_02


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

OCP之黄伟_sqlOCP之黄伟_数据_02


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是将空值当做无穷大来处理的.


OCP之黄伟_sqlOCP之黄伟_数据_02


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;


OCP之黄伟_sqlOCP之黄伟_数据_02


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;