OCP之黄伟
参考视频:第4集 - 10g OCP之007研究
第5集 - 10g OCP之007研究
第6集 - 10g OCP之007研究
Cluster table适合查询，因为聚簇表会将相关联的表信息数据存放在一个块中，这样读取数据时，会一次I/O读到全部信息，减少I/O,提高性能.
多表联合查询.不适合DML操作;
不适合TRUNCATE操作.不适合做全表扫描，因为你在进行I/O操作的时候，需要将相关联的、但是实际不需要的额外数据给读取出来，所以不适合全表扫描;
聚簇表，分为基于索引的和基于哈希的；
如果没有索引，是拒绝DML操作的.所以是先要进行创建索引，然后再进行DML操作.
在聚簇表中，相同的ROWID不能完全定位唯一一条记录，有可能是多条.这是聚簇表与普通heap表的区别.也就是说，在聚簇表中，表dept和表emp中拥有完全重复的rowid，是不足为奇的，不重复，就不正常了.
OCP 007课程概述
[*]数据查询语言：SELECT;
[*]数据定义语言：CREATE、ALTER、DROP、TRUNCATE、RENAME
[*]数据修改语言：INSERT、UPDATE、DELETE、MERGE;
[*]数据控制语言：GRANT、REVOKE;
[*]事务控制语言：COMMIT、ROLLBACK、SAVEPOINT;
[*]会话控制语言：ALTER SESSION;
[*]系统控制语言：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 *|{ column|expression ,...} FROM table;
页:
[1]