Oracle11gR2_SQL+教程

Oracle11g SQL

SQL语句 类别

Select（DQL）

insert

update DML

delete

merge

create

alter

drop

rename

DDL

truncate

comment

grant

revoke

DCL

commit

rollback

transaction

savepoint

control

*：表示非空

o：表示可以为空

#*：表示主键（主键必须非空）

Sql Developer

Sqlplus

1 Select语句（Data Retrieval Using the SQL SELECT

Statement）

使用SQL的select语句来检索数据。

第 1 页

1.1 Select语句基础

Capabilities（能力）：从一张表或多张表中查询数据。

Keywords（关键字）：select、from……

Clauses：

注意：应用程序中，列出所需查询的所有列，以提高效率，不要使用“*”。

写SQL：

SQL语句大小写不敏感。（规范和风格：keyword大写，列名小写）

可写在一行，也可写在多行。

Keywords不能缩写，不能跨行。

Clauses（子句）通常单写一行。

可以用缩进增强可读性。

在SQLPLUS中，以“分号”结束，或另起一行打“/”。

结果集中，缺省的列头：

全部大写。

字符和日期型左对齐。

数字右对齐。（可修改）

1.2 算术表达式（arithmetic）和NULL

算术表达式（arithmetic expressions）：加减乘除

+，-，*，/ （字符串只可使用+,-）

用于From子句外的所有子句

使用括号改变优先级

NULL值：

unavailable, unassigned, unknown, or inapplicable

不等于0或空字符串

算术表达式中有Null参与，结果既是null

1.3 列别名（column alias）

结果集中，重命名一列。

第 2 页

方便引用。

空格或as隔开列名和别名。

如果别名不想大写，或别名中有空格、特殊符号或关键字时，可加双引号扩起别名。

1.4 连接操作符（concatenation operators），DISTINCT

concatenation operators：

连接一列和字符串或其他列。

“||”，列连接符。

结果集中的该列，是字符表达式。

例如：

select department_name ||' department' || q'<'s manager ID : >'|| manager_id from departments;

select department_name ||' department' || q'['s manager ID : ]'|| manager_id from departments;

select department_name ||' department' || q'{'s manager ID : }'|| manager_id from departments;

select department_name ||' department' || q'('s manager ID : )'|| manager_id from

departments;

注意：以上语句，SQLPLUS中可以执行，PL/SQL developer中不能执行。

DISTINCT取消重复行。

DISTINCT后面所有列的组合要唯一。

1.5 字符串字面语义（literal character string）

Select语句中的常值，用单引号扩起，每行都会输出该值。

literal character string：

数字，字符，日期等常量。

单引号扩起。

每行输出一次该常量。

1.6 引用操作符

alternative quote(Q) operator:

需要在字符串中使用单引号时，可用[],<>,{}。

q'<'s Job>'

第 3 页

1.7 DESC指令

描述表结构。

Desc table_name

2 过滤和排序数据（Restricting and sort data）

过滤、排序

包含：

Where子句筛选

Order by 子句排序

替代变量

2.1 使用where子句

Where字句用到多种操作符：

比较操作符

逻辑操作符

Between …and…

In

Like

Is null

注意：

where子句是不能使用别名的。

String是大小写敏感的。Where upper(last_name) = „WILSON‟ 、lower（）

2.1.1 使用比较操作符（comparision operator）

大于、小于、等于…

=,<,<=,>,>=,!=(<>,^=)

（==是不能使用的）

Select * from table1 where a>0

第 4 页

2.1.2 Between…and…

Select * from table1 where a between 0 and 100

Between….and….也可以比较字符串

2.1.3 In

IN效果等同于where…or…or…，但效率稍微逊色。

2.1.4 Like

通配符：“%”，通配任意个字符

例如：Date like ‟%95‟

通配符：“_”，表示通配一个字符

例如：name like „_a%‟，(表示查询name列里，第二个字符为a的记录)

2.1.5 Is null

该列为空值

Where a is not null

2.1.6 使用逻辑操作符（logical operators）

And

Or

Not 返回条件失败的记录

2.1.7 转义

Escape

例如：

Where job like „_a_%‟ escape „‟

2.2 使用order by 子句

ASC [nulls last]

DESC [nulls first]

Order by 3 --表示select后面第三列。

第 5 页

升序null值默认放在最后。

也可以按照多列排序。

2.3 替代变量(Substitution variables)

使用&或&&来声明替换变量

使用范围：

Where条件

Order by子句

列表达式

表名

全部select语句

输入字符串时，需用单引号扩起。例如：WHERE JOBID = '&V_NAME'

注意：类似于C中的宏定义，只是简单的替换。

使用&&号，简化多次输入。

使用定义指令。例如：DEFINE V_NAME = 200

Select 语句

UNDEFINE V_NAME

Verify开关：（显示变量替换过程）

Set Verify on

Set Verify off

3 单行函数（Single-Row Functions）

3.1 函数（Functions）

单行函数（Single-Row Functions）输入一行，输出一行

多行函数（Multiple-Row Functions | Aggregate Functions）输入多行，输出一行

第 6 页

3.2 字符函数Character Functions

3.2.1 大小写转换函数（case-conversion）

Upper()：大写

Lower()：小写

Initcap()：单词首字符大写

3.2.2 字符操作函数（character manipulation functions）

LENGTH()

CONCAT()

SUBSTR()

INSTR()

LPAD | RPAD：填充

TRIM：除去

REPLACE：替换

3.3 数字函数（Number Functions）

Round

Trunc

Mod：求余数

3.4 日期函数（Date Functions）

MONTHS_BETWEEN()

ADD_MONTHS()

LAST_DAY()

NEXT_DAY()

SYSDATE()

ROUND()

TRUNC()

第 7 页

RRRR格式年份算法：

4 转换函数和条件表达式（Using Conversion Functions and

Conditional Expressions）

4.1 隐式和显式数据类型转换（Implicit and explicit data type

conversion）

4.1.1 隐式（Implicit）

Oracle内部自动转换：

数值插入字符串列

字符串类型数字插入数值列

日期字符串插入日期列

……等

注意：尽量使用显式转换，方便阅读，另外隐式转换容易报错。

Select…… where hire_date > ‟01-JAN-90‟

第 8 页

4.1.2 显式（explicit）

Oracle提供的转换函数。

4.1.2.1 to_char,to_date,to_number函数

to_char:

TO_CHAR(nchar | clob | nclob)

TO_CHAR({ datetime | interval } [, fmt [, 'nlsparam' ] ])

TO_CHAR(n [, fmt [, 'nlsparam' ] ])

注：fm去掉前导零

to_date

严格转换+fx

to_number

4.2 函数嵌套（Nesting functions）

第 9 页

4.3 通用函数（General functions）

NVL（expr1,expr2）1空返2

NVL2(expr1,3) 1空返3

NULLIF(expr1,expr2)12等返null，不等返1

COALELCE(expr1,expr2,…,exprn)从左数，返回第一个非null

4.4 条件表达式（Conditional expressions）

4.4.1 CASE

Sql国际标准中的条件表达式

case exp1

when exp2 then exp3

when exp4 then exp5

……

else expn

end

4.4.2 DECODE

Oracle特定

decode(exp1,serch1 ,return1 ,

serch2 ,reruen2 ,

……

default

)

5 使用组函数产生聚合数据（Reporting Aggregated Data Using

the Group Functions）

第 10 页

5.1 组函数（Group functions）

多行输入，返回一行

类型和语法（Types and syntax）

Stddev（）取标准方差

使用avg、sum、min、max、count

使用distinct关键字在组函数中

Count(distinct col)

组函数中的null值，组函数不计有null的记录

Avg（nvl（com，0））

5.2 Group子句

使用group by 子句，把表分成更小的组。

Select 查询的列，如果不在组函数中，必须包含在group by 后的表达式中。

Group by …… order by ……

位于order by 子句之前

对多列进行分组

Group by department_id,job_id

5.3 Having子句

Where …

Group by …

Having …

Order by ……

执行顺序，先where， group by ，再组函数，最后having

注意：where 子句中不能使用组函数。

第 11 页

组函数嵌套

6 从多张表中陈列数据（Displaying Data from Multiple

Tables）

6.1 Join类型和语法

通用语法：

Oracle语法:

注意：oracle中select语句的from子句，表的别名不可加“as”。（不支持国际标准）

注意：oracle中select语句的from子句，如果表名使用别名，那么where子句中，如果有“.”调用，就必须使用别名，不能使用原表名。

第 12 页

6.2 自然连接（natural join）

6.2.1 Natural join

将两个表中有相同列名和相同类型的所有列，做等值连接。

如果两个表中，表名相同的列，数据类型不同，返回错误。

------------------------------

select department_id ,department_name , location_id , city

from departments

natural join locations;（后面还可以再加where子句，where department_id in

(20,50)）

--oracle句法：

select ment_id , ment_name,

on_id,

from departments d, locations l

where on_id = on_id;

------------------

6.2.2 Using子句

使用using子句，明确指明，join的列。

如果using子句join的两列，有相同的名称，但数据类型不匹配，oracle数据库会自动执行隐式数据类型转换。

Using子句，如果有多列匹配，只使用一列。

Using子句，和natural join 是互斥的，不能同时使用。

select employee_id ,last_name,

location_id,department_id

from employees join departments

using (department_id);

注意：oracle可使用简单的where等值连接。

注意：凡是using子句引用连接的列，在where子句中不需要加表前缀。

第 13 页

6.2.3 On子句

连接两个表，表中列名不同

select ee_id,_name,ment_id,

ment_id,on_id

from employees e join departments d

on (ment_id = ment_id);--and（……）

三张表join：

select employee_id,city,department_name

from employees e

join departments d

on ment_id = ment_id

join locations l

on on_id = on_id;

6.3 自己连接（self join）

句法：

select _name emp , _name mgr

from employees worker join employees manager

on (r_id = ee_id);

6.4 非等值连接（nonequijoins）

select _name , ,_level

from employees e join job_grades j

on between _sal and t_sal;

第 14 页

6.5 外连接（outer join）

将不匹配的数据，也显示出来。

6.5.1 左外（left outer join）

select _name , ment_id , ment_name

from employees e left outer join departments d

on (ment_id = ment_id);

6.5.2 右外（right outer join）

select _name , ment_id , ment_name

from employees e right outer join departments d

on (ment_id = ment_id);

6.5.3 全外（full outer join）

select _name , ment_id , ment_name

from employees e full outer join departments d

on (ment_id = ment_id);

oracle中：

使用(+)，实现外连接

select _name ,ment_id,ment_name

from employees e, departments d

where ment_id(+) = ment_id;

哪边用加号，就把另一边表中不匹配的查询出来

6.6 笛卡尔乘积（cross join）

select last_name,department_name

from employees

cross join departments;

第 15 页

7 使用子查询（Using Subqueries to Slove Queries）

7.1 子查询的类型，语法和原则

子查询的位置：

Where子句

From子句

Having子句

7.2 单行子查询（single-row）

7.2.1 子查询返回单行值

select last_name ,salary

from employees

where salary >

(select salary

from employees

where last_name = 'Abel') ;

7.2.2 子查询中使用组函数

select last_name ,job_id,salary

from employees

where salary = (select min(salary)

from employees);

7.2.3 Having子句子查询

select department_id,min(salary)

from employees

group by department_id

having min(salary) >(select min(salary) from employees

第 16 页

where department_id = 50);

7.3 多行子查询(multiple-row)

使用all和any操作符

>any：大于any中的最小值

=any：等于any，相当于in

>all：是大于all的最大值

=all：0行返回，但不报错

7.4 子查询中的null值

难点

Not in ！= all

In == any

8 集合操作符（Using the Set Operators）

8.1 集合操作符：类型和使用指导原则

集合操作原则：

select 列数相同。

列类型要一一匹配，同属于一个data type group。

运算优先级相同，可用括号改变优先级。

Order by 子句，只能出现在最后的结果的后面。

第 17 页

8.2 Union 和 Union all

并集

Union没有重复行。

Union all有重复行。

最终结果使用第一个查询的列名。

除了union all 以外，其他几种操作是会排序的，按照第一个查询的第一列的升序排序。

8.3 Intersect

交集

8.4 Minus

差集

8.5 匹配的Select语句

使用to_char(null)补齐字符列

select location_id , department_name "Department", to_char(null)

"Warehose location"

from departments

Union

select location_id, to_char(null) "Department",state_province

from locations;

使用0补齐数字列

8.6 集合操作符中的Order by

除了union all 以外的连接，都按照第一个查询的第一列，升序排列。

Order by子句只能出现一次，而且只能出现在复合查询的最后。

单独查询是不能包含order by 子句的。

Order by 子句，只能识别第一个select查询的列名。

第 18 页

9 DML操纵数据（Manipulating Data）

9.1 插入行

Insert语句

Insert into table_name(…) values(…)

9.2 更新行

Update语句

Update table_name set (…=…) where ( … )

9.3 删除行

Delete语句

Delete from table_name where (…)

Truncate语句

truncate table table_name;

9.4 事物控制

ACID特性

Start；……end；中间

事物开始：

当第一条DML语句执行的时候，事物就开始了。

事物结束：

1) Commit和rollback时，事物结束。

2) DDL,DCL开始实行时，上一个事物结束。

3) 正常退出sqlplus时，事物提交。kill 进程会回滚。

4) 系统崩溃，下次启动时，会把事物回滚。，

第 19 页

Rollback

Commit

Savepoint：

Savepoint a;

….

Rollback to a;

Set auto on;

9.5 读一致性（read consistency）

从任何一个用户的视角看，会看到数据的一致状态。

一个用户对数据的修改，不会对另一个用户对数据的修改发生冲突。

对同一数据操作时：

1) 读者不会等待写者。

2) 写者不会等待读者。

3) 写者会等待写者。

9.6 在select语句中使用for update子句对指定的记录加锁

Select语句中使用for update 给查出来的数据加锁，其他用户查询时会等待。

Rollback或commit时，才解锁。

10 DDL（Using DDL Statements to Create and Manage Tables）

使用DDL语句，创建和管理表

10.1 数据库对象

Table

View

Sequence

Index

第 20 页

Synonym ：为一个对象，取一个别名，方便引用。

对象的命名规则：

1) 必须以字母开头。

2) 最多30个字符。

3) 大小写字母，0-9的数字，和三个特殊字符：“_”、“$”、“#”(不推荐使用$#)

4) 同一类型的对象，应该用不同的名称。

5) 避免关键字（KEY WORD），使用双引号处理关键字。

10.2 建表语句

建表前所拥有的特权：

1) 拥有建表的权限。

2) 有一个表空间。

访问其他用户的表

Select * from _name;

缺省值（default value）：

Create table ttt1(a int default 0 ,b int);

10.3 数据类型

10.4 约束

Not null

Unique：唯一

constraint emp_emal_uk unique(email)

Primary key:

(Col1 int constraint aaa_col1_pk primary key)

Constraint aaa_col1_pk primary key(col1)

Foreign key

第 21 页

On delete cascade

On delete set null

Check ：自定义约束

constraint check_min check (salary >0)

查询表定义：

select dbms__ddl('table','emp1') from dual;

SQLplus待查….

10.5 使用子查询建表

create table ttt1 as

select * from ttt2;

10.6 修改表（ALTER TABLE）

Read only tables

Alter table ttt1 read only;--只读依然可以删除

10.7 删除表

Drop table ttt1;

Drop table ttt1 purge; --直接删

Recycle bin—10G才有的垃圾箱

第 22 页

11 其他模式对象（Creating Other Schema Objects）

11.1 视图（Views）

Create or replace force view view_name as sq with check

option […]

with read only

对视图使用DML语句：

1) 简单视图，可以修改数据。

如果视图包括以下几点，不能对视图，移除一行：

1) 有组函数

2) 有group by子句

3) 有distinct关键字

4) 有rownum关键字

11.2 序列（Sequences）

句法：

第 23 页

NEXTVAL：推进

CURRVAL：当前值

11.3 索引（Index）

Create index index_name on

Table_name （col）;

11.4 同义词（Synonyms）

第 24 页