参考视频:
[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课程概述
数据查询语言: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 *|{[DISTINCT] column|expression [alias],...} FROM table;
运维网声明
1、欢迎大家加入本站运维交流群:群②:261659950 群⑤:202807635 群⑦870801961 群⑧679858003
2、本站所有主题由该帖子作者发表,该帖子作者与运维网 享有帖子相关版权
3、所有作品的著作权均归原作者享有,请您和我们一样尊重他人的著作权等合法权益。如果您对作品感到满意,请购买正版
4、禁止制作、复制、发布和传播具有反动、淫秽、色情、暴力、凶杀等内容的信息,一经发现立即删除。若您因此触犯法律,一切后果自负,我们对此不承担任何责任
5、所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其内容的准确性、可靠性、正当性、安全性、合法性等负责,亦不承担任何法律责任
6、所有作品仅供您个人学习、研究或欣赏,不得用于商业或者其他用途,否则,一切后果均由您自己承担,我们对此不承担任何法律责任
7、如涉及侵犯版权等问题,请您及时通知我们,我们将立即采取措施予以解决
8、联系人Email:admin@iyunv.com 网址:www.yunweiku.com