SQL语句、PL/SQL块与SQL*Plus命令的区别

SQL语句是以数据库对象为操作对象的语言，主要包括数据定义语言DDL，数据操纵语言DML，数据控制语言DCL和数据存储语言DSL等。当输入SQL语句后，SQL*Plus将其保存在内部缓冲区中，当SQL命令输入完毕后，可以通过在命令行的末尾输入分好“;”并按回车结束，或者在单独一行上用斜杠“/”或空行表示结束。

PL/SQL块同样是以数据库对象为操作对象，但由于SQL本身不具备过程控制功能，所以为了能够与其他语言一样具备过程控制的处理功能，在SQL中加入诸如循环和选择等面向过程的处理功能，由此形成了PL/SQL。所有PL/SQL语句的解释均由PL/SQL引擎来完成，使用PL/SQL块可编写过程，触发器和包等数据库永久对象。

SQL*Plus命令主要用来格式化查询结果，设置选择，编辑以及存储SQL命令，设置查询结果的显示格式，并且可以设置环境选项。可以编辑交互语句，可以与数据库进行“对话”。

Silver

Written @13:55 2009-2-2

DML是data manipulation language的缩写，是数据操纵语言的意思。主要用于对数据库表和视图进行操作。一般来说，DML是指select，insert，update，delete。在Oracle 11g中，DML还包括CALL，EXPLAIN PLAN，LOCK TABLE和MERGE语句。

a. 使用INSERT语句插入表数据
（1）一般insert语句的使用
insert语句主要用于向表中插入数据，其语法是：
INSERT INTO [user.]table[@db_link][(column1[,column2]...)]]
VALUES(express1[,express2]...)

table:要插入的表名
db_link:数据库链接名
column:表的列名
express:表达式
values:给出要插入的值列表
（2）多表insert语句的使用
oracle从9i开始可以用一条insert语句实现向多个表中插入数据：
INSERT [ALL] [conditional_insert_clause]
[insert_into_clause values_clause](subquery)

subquery:子查询语句，可以是任何合法的select语句

conditional_insert_clause如下：
[ALL][FIRST]
[WHEN condition THEN][insert_into_clause values_clause]
[ELSE] [insert_into_clause values_clause]
此语法包含了无条件和有条件两种情况：

无条件INSERT ALL：
INSERT ALL
INTO sal_history VALUES(EMPID,HIREDATE,SAL)
INTO mgr_history VALUES(EMPID,MGR,SAL)
SELECT employee_id EMPID,hire_date HIREDATE,salary SAL,manager_id MGR
FROM employees
WHERE employee_id>200;
因为没有附加任何限制于检索出的记录，所以所有检出返回数据将根据其列名插入相应的表中。

有条件的INSERT ALL：
INSERT ALL
WHEN SAL>10000 THEN
INTO sal_history VALUES(EMPID,HIREDATE,SAL)
WHEN MGR>200 THEN
INTO mgr_history VALUES(EMPID,MGR,SAL)
SELECT employee_id EMPID,hire_date HIREDATE,salary SAL,manager_id MGR
FROM employees
WHERE employee_id>200;

这里将进行比较，返回的记录将比较其SAL，如果大于10000则插入SAL_HISTORY，同理，MGR大于200的记录将插入MGR_HISTORY，其余都将丢弃。

有条件的FIRST INSERT：
FIRST和ALL的区别在于当遇到第一个求值为true的子句之后，停止对WHEN子句求值，而ALL不论求值是否为true。
INSERT FIRST
WHEN SAL>10000 THEN
INTO sal_history VALUES(EMPID,HIREDATE,SAL)
WHEN SAL<5000 THEN
INTO sal_history VALUES(EMPID,HIREDATE,SAL)
WHEN MGR>200 THEN
INTO mgr_history VALUES(EMPID,MGR,SAL)
SELECT employee_id EMPID,hire_date HIREDATE,salary SAL,manager_id MGR
FROM employees
WHERE employee_id>200;

上面的例子中，如果第一个when子句求值为true,则其后的when子句将不会被执行，反之将直至遇到第一个满足条件的子句执行为止。

b. 使用update语句更新表数据

在需要修改表中的数据时，可使用update命令，命令由三部分组成：
update后跟一个或者多个要修改的表，必选部分。
set后跟一个或多个要修改的表列，也是必选。
where后跟更新限定条件，这部分可选的。

例如把customer表的state—id为MA的客户的sales改为0，那么语句就是：
update customer set sales=0 where state-id=‘MA'；

c. 使用delete语句删除表数据

该命令组成为：
关键字delete from后跟表名，必选
关键字where后跟删除条件，可选

删除customer表state-id为MA的记录，如下：
delete from customer where state-id=’MA‘；

d. TRUNCATE语句的使用
truncate table语句一般用于删除表中的所有行，是一种快速，无日志记录的方法。truncate table与不带where子句的delete语句功能上相同，不过truncate table的速度更快，并且使用更少的系统资源和事务日志资源。

Silver

Written @00：30 2009-2-2

DDL部分主要用于创建各种类型的对象，包括表，视图，索引，主键，外键，序列，同义词以及数据库链接等

a. 表                
用create table的语句可以创建表，简单语法如下：
CREATE TABLE [[database.]owner.]table_name
(column_name datatype [not null|null] INDENTITY[seed,increment][constraint]
[,column_name datatype [not null|null] INDENTITY[seed,increment]]
[constraint] ...)
ON file group]
用户不仅要给出表的名称，还必须给出表中所有的列。                                                                                                         

b. 主键
主键用来约束表的一个列或者几个列，主键的取值是唯一并且是非空的。在关系数据库中，使用主键来实现数据的一致性和完整性。可以使用关键字Primary Key来建立主键。在Oracle系统中，一旦在表的某一列或者几列上创建了主键约束，则Oracle会自动为该表建立一个唯一性索引。
Oracle数据库引擎强制主键约束规则，在创建主键时，首先，所有主键值都系唯一的，其次，它们必须有一个值，也就是说，作为主键的列不能为NULL 。
主键约束执行可以暂时的disable，然后再enable。使用alter table语句来完成，约束选项为:DISABLE或ENABLE，约束的这两种状态一个很大的好处就是在大批量加载数据时，可以将约束置为disable，disable约束可以减少数据加载时间，是因为索引被disable了，因此索引不需要被更新。加载完后，enable约束，约束规则仅仅应用于新加载的数据。

1. 在创建表时定义主键
示例：在创建表dept时，在deptno列上定义了主键（可以不指定主键名称，Oracle会自动为该主键添加名字）
create table dept
（deptno number（2），
loc varchar（10），
Primary Key （deptno）
）;
或者
create table dept
（deptno number（2）Primary Key，
loc varchar（10）
）;
或者更详细一点
create table dept
（deptno number（2），
loc varchar（10），
Constraint pk_deptno PRIMARY KEY (deptno)
USING INDEX TABLESPACE indx
STROAGE (INITIAL 10M NEXT 5M PCTINCRESE 0)
)TABLESPACE user STROAGE (INITIAL 10M NEXT 5M PCTINCRESE 0)

Oracle强烈建议，任何一个应用程序的库表至少需要创建两个表空间，其中之一用于存储表数据，而另一个用于存储索引数据。因为如果把两者摆在一起的话，会引起表数据和索引数据I/O操作的竞争，影响系统性能，如果能把两个空间从物理上分开，那就更好啦！

2. 使用alter table语句定义主键
如果在创建表的时候没有定义主键，可以使用alter table... ADD CONSTRAINT PRIMARY KEY来创建主键，语法如下：
ALTER TABLE dept
ADD CONSTRAINT pk_deptno PRIMARY KEY (deptno)

3. 使用alter table语句修改主键约束
alter table dept disable constraint pk_deptno
alter table dept enable constraint pk_deptno
如果存在引用该主键的外键值，必须首先删除主外键关系，才能使主键无效

4. 删除主键
alter table dept DROP CONSTRAINT pk_deptno [CASCADE];--如果加CASCADE，系统自动删除时，也按照一下顺序操作：
5. 使依赖该主键的外键无效，然后删除该外键
6. 使该主键无效，然后删除该主键
alter table dept DROP PRIMARY KEY；
或者
alter table dept DROP constraint pk_deptno ；

c. 外键
外键约束可以保证该数据列与所引用的主键约束的数据列一致。在创建外键前，先要完成引用的主键的创建，然后才能声明外键和引用。
1. 在创建表时定义外键
create table dept
（deptno number（2），
loc varchar（10），
Constraint pk_deptno Primary Key （deptno）
）;
create table emp
（empno number（4），
empname varchar（10），
deptno number(2),
Constraint fk_deptno FOREIGN Key （deptno） REFERENCES dept(deptno) ON DELETE CASCADE
);
2. 使用alter table语句定义外键
alter table emp add constraint fk_deptno FOREIGN Key （deptno） REFERENCES dept(deptno) ON DELETE CASCADE
3. 修改外键的状态
alter table emp disable constraint fk_deptno；
4. 删除外键
alter table emp drop constraint fk_deptno；

d. 约束

约束也称为完整性约束，包括主键，外键，check，not null和unique index共五种类型。
1. CHECK约束
用于增强表中数据内容规则的一致性，语法是：
CONSTRAINT [constraint_name] CHECK (condition)

例如：
create table dept
（deptno number（2），
loc varchar（10），
sex char(2) CHECK (sex='male' or sex ='female'),
Constraint pk_deptno Primary Key （deptno）
）;

f. NOT NULL约束

NOT NULL 约束应用在单一列上，说明该数据列必须有值。
create table dept
（deptno number（2），
loc varchar（10）NOT NULL，
sex char(2) CHECK (sex='male' or sex ='female'),
Constraint pk_deptno Primary Key （deptno）
）;
也可以创建表后再进行指定
alter table dept MODIFY loc NOT NULL;

g. 唯一性约束（UNIQUE）
此约束要求一列或一组列的每一个值都是唯一的。唯一值约束允许有空值，除非此列也应用了NOT NULL非空约束。
create table dept
（deptno number（2），
loc varchar（10），
Constraint unique_deptno UNIQUE (deptno)
USING INDEX TABLESPACE indx
STROAGE (INITIAL 10M NEXT 5M PCTINCRESE 0)
)TABLESPACE user STROAGE (INITIAL 10M NEXT 5M PCTINCRESE 0)

使其无效：
alter table dept DISABLE Constraint unique_deptno；

删除：
alter table dept DROP Constraint unique_deptno；

直接使用create unique index 语句创建唯一性索引：
create unique index index—name on dept（deptno） tablespace indx

h. 索引

索引是关系数据库中用于存放表中每一条记录位置的一种对象，主要目的是加快读取速度和完整性检查。
创建索引：
create unique index index—name
ON table-name（column...)
[CLUSTER schema.cluster]
[INITRANS n]
[MAXTRANS n]
[PCTFREE n]
[STROAGE storage]
[TABLESPACE tablespace]
[NO SORT]

create unique index index—name on dept（deptno） tablespace indx

修改索引：
alter unique index index-name REBUILD storage （INITIAL 10M NEXT 5M PCTINCRESE 0）；
修改为逆键索引：
alter unique index index-name REBUILD reverse；
删除索引：
DROP index schema.index

Silver

Written @01：06 2009-2-1

select语句的完整语法描述如下：
SELECT[ALL|DISTINCT TOP n [PERCENT]WITH TIES select_list --必要元素，制定列
[INTO[new_table_name]]
[FROM {table_name|view_name}[(optimizer_hints)]--指定表
[[,{table_name2|view_name2}[(optimizer_hints)]
[...,table_name16|view_name16][(optimizer_hints)]]]
[WHERE clause]--指定行
[GROUP BY clause]
[HAVING clause]
[ORDER BY clause]
[COMPUTE clause]
[FOR BROWSE]

关键字解释：

a. FROM子句指定查询中包含的行和列所在的表
[FROM {table_name|view_name}[(optimizer_hints)]--指定表
[[,{table_name2|view_name2}[(optimizer_hints)]
[...,table_name16|view_name16][(optimizer_hints)]]]
在查询其他角色对应的方案中的表，需要指定该方案的名称，例如查询方案tiger的temp1表的所有行，其语句为：
select * FROM tiger.temp1
可以在FROM中指定多个表，用逗号分隔表名就可以了，例如
select * from table1，table2，table3

b. select_list，必要元素，用来指定列，跟在关键字SELECT后面。
SELECT column_name_1,column_name_n
FROM table_1,table_n
利用指定列的方法可以改变创建表时列的顺序来显示查询的结果，并且可以通过在多个地方指定同一列来多次显示同一个列。
当然也可以使用通配符*来指定所有列，但通常我们应该少用通配符*，因为这样将检索所有行，效率十分低。

c. WHERE子句指定行
SELECT语句指定了表中所有列的部分列，而所有行都被检索，在很多情况下，用户仅仅需要有目的地检索部分行，这时候就要使用where子句了。
where子句构成一个行选择表达式，尽可能小范围地指定检索的行，带有where的select语句可以返回一个单独的行，甚至没有相匹配的行。如果没有行返回的话，并不返回错误，而是显示”未选定行“。
SELECT column_name_1,column_name_n
FROM table_1,table_n
WHERE column_name comparation_operator value

在where子句中使用比较运算符
在where子句中允许在表列名（column_name)和列值（value）之间使用比较运算符（comparation_operator)包括=，!=,<>,<,>,<=,>=,LIKE.
比较运算符LIKE后面跟一个值的部分而不是一个完整的列值。一个百分号%可以用来匹配任何长度的字符，而通配符下划线"_"则是用来匹配一个字母的。

在where子句中使用布尔操作和其他关键词
可以使用布尔操作符可以组成多个检索条件，更加精确地控制被检索的行。
SELECT column_name_1,column_name_n
FROM table_1,table_n
WHERE column_name comparation_operator value
Boolean_operator column_name comparation_operator value
 OR操作符:”或“的意思，即由OR操作符连接的两个条件只要任意满足一个即可获得逻辑真值。
 AND操作符:”与“的意思，即由AND操作符连接的两个条件必须同时满足才获得逻辑真值。
 NOT操作符:”非“的意思，即由NOT操作符后面的条件不满足时才获得逻辑真值。
 BETWEEN选择值的范围：使用between和and来指定要检索值的范围。
 IN来指定一条列值：使用between和and可以指定某个范围内的所有值，而IN关键词用来指定几个特定的值。实际上，IN就等同于使用多个OR操作符。

d. ORDER BY 子句
数据在数据库中的存储是无序的。使用select语句可以指定检索和显示行的顺序。通过order by子句可以对select语句检索得到的数据进行排序。其语法如下：
SELECT column_name_1,column_name_n
FROM table_1,table_n
ORDER BY column_name_1,column_name_n
使用该语句，首先根据column_name_1这一列进行排序，如果该列的值出现了相同的值，则根据column_name_n这一列的值进行排序。
默认情况下，根据指定的列值进行升序排序，如果要降序排列的话，可以在列名的后面跟上关键词DESC（descend）

e. 使用DISTINCT来检索唯一的列值
再次强调，数据在表中的存储往往是无序的甚至是无规则的。因此，在表中存储了重复数据的行也是可能的。当然，用户在设计数据库的表时，可以通过定义限制，使用唯一的关键字来禁止向表中存储重复的行。然而，某一列的数据总是可能重复的，而用户往往想知道在某一列中导读存在哪些不同的数据，就可以使用DISTINCT来检索这一列的唯一值了。
SELECT DISTINCT column_name_1
FROM table_1

f. 使用算术运算符
在SQL语句中还可以使用算术运算符，而带有运算符的表达式同样是从左到右按照先乘除后加减的顺序进行运算。
SELECT column_name_1*2，column_name_n --column_name_1乘以2
FROM table_1

g. 使用组函数
组函数也称为集合函数，返回基于多个行的单一结果，行的准确数量无法确定，除非查询被执行并且所有的结果都被包含在内。与单行函数不同，在解析时所有的行都是已知的。这些函数可以在select或者select的having子句中使用，当用于select子串时常常都与group by一起使用。
AVG([{DISTINCT|ALL}]) 返回数值的平均值，默认设置为ALL
MAX([{DISTINCT|ALL}]) 返回选择列表项目的最大值，默认设置为ALL
MIN([{DISTINCT|ALL}]) 返回选择列表项目的最小值，默认设置为ALL
STDDEV([{DISTINCT|ALL}]) 返回选择列表项目的标准差，默认设置为ALL
SUM([{DISTINCT|ALL}]) 返回选择列表项目的数值的总和，默认设置为ALL
VARIANCE([{DISTINCT|ALL}]) 返回选择列表项目的统计方差。

h. 用GROUP BY给数据分组
group by子句将表按行分组，每组中的行对指定的列具有相同的值，每个不同值的重复值被放在相同的组中，分组使用户可以对行的组执行同样的函数。
在一条语句中可以用任意数量的列进行分组，在选择清单中的列必须是在group bu子句中或有一个函数使用它，包含group by的select语句的语法如下：
SELECT column_name_1，column_name_n
FROM table_1
GROUP BY  column_name_1，column_name_n
group by在按列升序排序后，仅仅定位唯一的列值。
还可以使用HAVING子句选择特殊的组，HAVING子句将组的一些属性和一些常数值比较，如果一个组满足HAVING子句中的逻辑表达式，它就被包括在查询的结果中，使用HAVING子句的select语句的语法如下：
SELECT column_name_1，column_name_n
FROM table_1
GROUP BY  column_name_1，column_name_n
HAVING expression --e.g. HAVING count(*)=3

i. 用HAVING子句限制分组数据
组函数只能用于两个子串中，组函数不能用于where子句中

j. 使用复杂的select语句

-----集合操作：交，并，差------

交：UNION和UNION ALL

使用UNION语句可以合并两个或多个查询的结果。UNION用第二个查询结果合并第一个查询结果。它不显示两个查询中重复的行。而UNION ALL操作符返回查询所检索出的所有行，包括重复的行。其语法是：
SELECT column_name_1，column_name_n
FROM table_1，table_n
WHERE column_name comparasion_operator value
[GROUP BY  ...]
[HAVING ...]
UNION [ALL]
SELECT column_name_1，column_name_n
FROM table_1，table_n
WHERE column_name comparasion_operator value
[GROUP BY  ...]
[HAVING ...]
[ORDER BY ...]  --注意：如果使用order by子句进行排序，该子句只能出想在最后一个查询的后面。

并（INTERSECT）操作：返回两个查询所检索出的共有行
差（MINUS）操作：返回将第二个查询检索的行从第一个查询检索出的行中减去之后剩余的行

----子查询----
可以在一个select语句中嵌入另一个完整的select语句，则嵌入到select语句中的select语句称为子查询。子查询应用括号进行区分。

----表的连接----
我们可以使用from指定多个表进行查询，但一般来说指定的多个表一定会有某种关系，用户需要综合这些表来查询特定的信息。在设计表格时，就考虑了查询的问题。在表中创建主关键字约束和外部关键字约束来使两张表联系起来，在查询的时候，使用where子句设置一个表中的主关键字等于另一个表中的外部关键字的方法来实施一种关系连接，进行查询。这种关系连接称为等值链接和自然连接。

----CASE语句的使用----
CASE语句可以在SQL中实现if-then-else型的逻辑，而不必使用PL/SQL。
有两种类型的CASE语句：
简单CASE语句，使用表达式确定返回值。
搜索CASE语句，是有条件确定返回值。

简单CASE语句
CASE search_expression
 WHEN expression1 THEN result1
 WHEN expression2 THEN result2
 ...
 WHEN expressionN THEN resultN
 ELSE default_result
END
其中search_expression是待求的表达式
expression1，...，expressionN是要与search_expression进行比较的表达式
result1，... ，resultN是（每一个可能的表达式所对应的）返回值。
default_result是无法找到匹配的表达式时的默认返回值

搜索CASE语句
CASE
 WHEN condition1 THEN result1
 WHEN condition2 THEN result2
 ...
 WHEN conditionN THEN resultN
 ELSE default_result
END
其中condition1，condition2，... ，conditionN是待求的表达式
result1，... ，resultN是返回值。
default_result是无法找到匹配的表达式时的默认返回值 

----强大的DECODE函数----
DECODE(value,search_value,result,default_value)对value与search_value进行比较。如果这两个值相等，DECODE()返回result，否则返回default_result。DECODE()函数允许在SQL中执行if-then-else型的逻辑，而不必使用PL/SQL。

Silver

Written @2009-1-31

看了一些关于数据库知识的书籍，觉得有必要写一些笔记和做一些练习，好加深记忆。

一 SQL语言基础和分类
SQL是Structure Query Language（结构化查询语言）的简称，是用户与数据库交流所需要的标准语言，是应用程序和数据库进行交互操作的接口。SQL语言主要有以下几类：
1.数据查询语言DQL语句（select）：用户查询数据可数据。在sql所有语句中，select语句的功能和语法最复杂和最灵活。
2.数据操纵语言DML：用于改变数据库数据，包括数据插入INSERT，更新已存在数据UPDATE，和删除数据DELETE
3.事务控制语言TCL：用于维护数据的一致性，包括用于确认已进行的数据库改变COMMIT，用于取消已进行的数据改变ROLLBACK和用于设置保存点以取消部分数据库改变的SAVEPOINT
4.数据定义语言DDL：用于建立，修改和删除数据库对象，例如使用CREATE TABLE可以创建表。ALTER TABLE则可以对表的结构进行修改，而如果想删除某个表的话，可以用DROP TABLE语句。DDL数据是会自动提交事务的。
5.数据控制语句DCL：用于执行权限授予和收回的操作，包括GRANT（用于给用户或者角色授予权限）和REVOKE（用于收回用户或者角色所具有的权限）两条命令。

二 SQL的语句编写规则：
SQL关键字不区分大小写。
对象名和列名不区分大小写。
字符值和日期区分大小写。
当语句的文本很长时，可以将语句文本分布在多行上，并且可以通过使用跳格和缩进提高可读性。
另外，在SQL*PLUS中SQL语句要以分号结束。

Silver

Written @2009-1-31