Oracle 9i 1z0-007课程学习笔记

Oracle 9i 1z0-007课程学习笔记

Lesson 1  Writing Basic SQL SELECT Statements

 

Lesson 2 Restricting and Sorting Data(限制和排列数据结果集)

1、WHERE condition(s)

2、ORDER BY {column,expr,alias} [ASC|DESC]

 

Lesson 3 Single-Row Functions

1、Character

(1)LOWER 字母转换成小写

(2)UPPER字母转换成大写

(3)INITCAP 首字母大写

(4)CONCAT 相当于“||”

(5)SUBSTR(‘helloworld’,1,5) hello

(6)LENGTH(‘helloworld’) 10

(7)INSTR(‘helloworld’,’w’) 6

(8)LPAD(salary,10,’*’) *****24000

(9)RPAD(salary,10,’*’) 24000*****

(10)TRIM(‘h’ from ‘helloworld’) elloworld

2、Number

(1)ROUND(45.926,2) 45.93

(2)TRUNC(45.926,2) 45.92

(3)MOD(1600.300) 100

3、Date

(1)   SYSDATE

(2)   MONTHS_BETWEEN

(3)   ADD_MONTHS

(4)   NEXT_DAY

(5)   LAST_DAY

4、Conversion

(1)   TO_CHAR

(2)   TO_NUMBER

(3)   TO_DATE

5、General

(1)   NVL(expr1,expr2)

(2)   NVL2(expr1,expr2,expr3)

(3)   NULLIF(expr1,expr2)

(4)   COALESCE(expr1,expr2,…,exprn)

 

Lesson 4 Displaying Data from Multiple Table

多表连接查询

9i兼容SQL99标准

 

Lesson 5 Aggregating Data Using Group Functions

聚合函数

SUM、AVG、COUNT、MAX、MIN等，GROUP BY  HAVING

 

Lesson 6 Subqueries

子查询：单行子查询、多行子查询

 

Lesson 7 Producing Readable Output with iSQL*Plus

绑定变量&a、&&a，DEFINE and UNDEFINE

SET VERIFY OFF|ON(显示新值和旧值)

格式化输出报表

 

Lesson 8  Manipulating Data

DML statement

 

Lesson 9 Creating and Managing Tables

(1)CREATE TABLE

(2)ALTER TABLE

(3)DROP TABLE

(4)RENAME

(5)TRUNCATE

(6)COMMENT

 

Lesson 10 Including Constraints

(1)NOT NULL

(2)UNIQUE

(3)PRIMARY KEY

(4)FOREIGN KEY

(5)CHECK

DESC(RIBE) USER_CONSTRAINTS

 

Lesson 11 Creating View

 

Lesson 12 Other Database Objects

(1)Sequence     USER_SEQUENCES

(2)Index        USER_INDEXS,USER_IND_COLUMNS

(3)Synonym    

 

Lesson 13 Controlling User Access

安全管理

 

Lesson 15 Using SET Operators

并集 UNION/UNION ALL

交集 INTERSECT

差集 MINUS

 

Lesson 16 Oracle9i Datetime Functions

时区（TIME ZONE）

 

Lesson 17 Enhancements to the GROUP BY Clause

ROLLUP

CUBE

GROUPING

GROUPING SETS

 

Lesson 18 Advanced Subqueries

高级子查询IN EXISTS

 

Lesson 19 Hierarchical Retrieval

树形分级查询，显示格式

 

Lesson 20 Oracle9i Extensions to DML and DDL Statements

9i的DML和DDL扩展

INSERT…FIRST|ALL SELECT

EXTERNAL TABLE

创建表时同时创建索引