Oracle 笔记（四）、SQL 几个要点

傷城~ 2022-05-19 13:51 266阅读 0赞

附录：  
[1、SQL 简介][1_SQL]  
[2、SQL 操作符][2_SQL]  
[3、Oracle 常用数据类型][3_Oracle]  
[4、Oracle 函数][4_Oracle]  
[5、\[转\] Oracle 常用SQL语法][5_ Oracle _SQL]  
  
  
  
**字符串函数**  
LENGTH() 字符长度  
LENTTHB() 字节长度；一个汉字内存中占用 2字节  
  
LTRIM、RTRIM、TRIM  
  
截串  
SUBSTR(表达式，位置，长度)  
Oracle 无左右取串函数，但可以使用变通方式完成。  
左取串： SUBSTR('abcdefg', 1, 3)  
右取串： SUBSTR('abcedfg', LENGTH('abcdefg')-3+1, 3)  
  
  
**时间函数**  
sysdate、current\_day  
设置时间格式： ALERT SESSION SET NLS\_DATE\_FORMAT = 'dd-mon-yyyy HH:mi:ss'  
求时间： NEXT\_DAY(sysdate, '星期三')  
  
  
**转换函数**  
TO\_CHAR(sysdate, 'yyyy-mm-dd hh24:mi:ss')  
TO\_DATE('12-3月-04')  
TO\_NUMBER('333') 必须是能转换  
TO\_TIMESTAMP('2007-10-10 00:00:00.0', 'yyyy-mm-dd hh24:mi:ssxff') 转换为时间戳格式  
  
**聚合函数**  
count(\*) ：查询表行数  
count(column) :查询列行数，会忽略空值，注意  
ps.聚合函数不能做为 where 里查询条件出现（因为聚合是对所有查询结果的运算？）  
  
  
**其他函数**  
USER：当前用户  
SUM(DECODE(SEX, '男', 1, 0)) 筛选出行被为男的记录 并加1  
SUM(DECODE(SEX, '女', 1, 0)) 筛选出行被为女的记录 并加1  
NVL(a2, '非输入') 布尔值判断，利用系统对空值进行处理  
SELECT DISTINCT a1 FROM aa  
  
  
**表连接**  
内连接：查询时，把能够公共匹配的数据完全查询出来。  
FROM e, d WHERE e.id = d.id  
标准： FROM e JOIN d ON e.id = d.id  
  
外连接：不完全匹配  
左连接： FROM e JOIN d ON e.id = d.id(+)  
左边数据全部显示，右边匹配不上的部分用空值代替  
右连接： FROM e JOIN d ON e.id(+) = d.id  
（同理左连接）  
  
  
**子查询**  
无关子查询  
相关子查询  
  
EXISTS()： 根据子查询返回是否存在数据来决定父查询。  
  
UNION： 将多个查询出来的信息行整合成一个结果集。  
SELECT eid, ename FROM e  
UNION  
SELECT id, name FROM d  
ps.UNION 查询出来的重复记录不会显示，UNION ALL 则显示全部（包括重复的）。  
  
INTERSECT: 返回查询出来信息行的交集，Oracle 独有。  
  
利用查询结果批量更新：  
INSERT INTO e(eid, ename) SELECT id, name FROM d  
或者利用查询结果创建新表：  
CREATE TABLE ttt AS ttt (SELECT \* FROM e)  
  
  
附加：

![ExpandedBlockStart.gif][]  
SQL 简介  
SQL （Structured Query Language，结构化查询语言）支持如下类别命令：  
数据定义语言：CREATE（创建）、ALTER（更改）、TRUNCATE（截断）、DROP（删除）命令。  
数据操纵语言：INSERT（插入）、SELECT（选择）、DELETE（删除）、UPDATE（修改）命令。  
事务控制语言：COMMIT（提交）、SAVEPOINT（保存点）、ROLLBACK（回滚）命令。  
数据控制语言：GRANT（授予）、REVOKE（回收）命令。  
  
特点：  
1、非过程语言，它同时可以访问多条记录。  
2、所有关系型数据库的通用型语言，可移植性强。  
3、对于数据和对象的操作简单。  
  
  
数据定义语言  
用于改变数据库结构，包括创建、修改和删除数据库对象。  
  
1、CREATE TABLE 创建表  
CREATE TABLE \[schema.\]table  
(columname datetype \[, ![dot.gif][].\]);  
  
.表名的最大长度为30个字符；  
.表名首字母为字母，可以用下划线、数字和字母，但不能使用空格和单引号；  
.同一用户模式下的不同表不能有相同的名称；  
.表名、列名、用户名、和其他对象名不区分大小写，系统会自动转换成大写。  
  
  
2、ALTER TABLE 修改表  
ALTER TABLE <tablename>  
MODIFY (column definition![dot.gif][]);  
ADD (column definition![dot.gif][]);  
DROP COLUMN column;  
  
3、TRUNCATE TABLE 截取表  
TRUNCATE TABLE <tablename>；  
快速删除记录并释放空间，不使用事务处理，无法回滚，效率高。  
  
DESC <tablename> 查看表结构  
  
4、DROP TABLE 删除表  
DROP TABLE <tablename>  
  
  
  
  
数据操纵语言  
DISTINCT 防止选择重复的行。  
  
  
  
事务控制语言  
COMMIT 提交并结束事务处理。  
SAVEPOINT 保存点，将很长的事务处理划分为较小的部分，用于标记事务中可以应用回滚的点。  
ROLLBACK 用来撤销在当前的事务中已完成的操作。可以回滚整个事务处理；也可以将事务回滚到某个保存点。  
  
UPDATE xxx;  
SAVEPOINT mark1;  
DELETE FROM xxx;  
SAVEPOINT mark2;  
ROLLBACK TO SAVEPOINT mark1;  
COMMIT;  
  
  
  
数据控制语言  
为用户提供权限控制命令。  
  
授予对象权限  
GRANT SELECT,UPDATE ON order\_master  
TO MARTIN;  
  
取消对象权限  
REVOKE SELECT,UPDATE ON order\_master  
FROM MARTIN;

![ExpandedBlockStart.gif][]  
SQL 操作符  
  
算术操作符  
算术表达式有 NUMBER 数据类型的列名、数值常量和连接它们的算术操作符组成。（+ - \* /）  
  
  
比较操作符  
用于比较两个表达式的值。  
=、!=、<、>、<=、>=、BETWEEN ![dot.gif][] AND （检查是否在两个值之间）  
\[NOT\] IN（与列表中的值匹配）  
\[NOT\] LIKE（匹配字符模式， \* \_ 通配符）  
\[NOT\] IS NULL（检查是否为空）  
  
  
逻辑操作符  
用于组合生成一个真或假的结果。AND OR NOT  
  
集合操作符  
集合操作符将两个查询的结果组合成一个结果集合。  
UNION（联合） 返回两个查询选定不重复的行。(删除重复的行)  
UNION ALL（联合所有） 合并两个查询选定的所有行，包括重复的行。  
INTERSECT（交集） 只返回两个查询都有的行。  
MINUS（减集） 在第一个查询结果中排除第二个查询结果中出现的行。 （第一 -- 第二）  
  
  
使用集合操作符连接起来的 SELECT 语句中的列应遵循以下规则：  
.通过集合操作连接的各个查询相同列数，匹配列的数据类型；  
.这种查询不应含有 LONG 类型的列；  
.列标题来自第一个 SELECT 语句。  
  
  
SELECT orderno FROM order\_master  
UNION  
SELECT orderno FROM order\_detail;  
  
  
  
  
连接操作符 （||）  
用于将两个或者多个字符串合并成一个字符串，或者将一个字符串与一个数值合并在一起。  
SELECT ('供应商'|| venname || '的地址是' || venaddress)  
FROM vendor\_master

![ExpandedBlockStart.gif][]  
Oracle 常用数据类型  
  
1、字符数据类型  
CHAR 固定长度字符串 长度 1～2000个字节，未指定则默认为 1字节  
VARCHAR2 可变长度字符串 长度 1～4000个字节，定义时必须指定大小  
LONG 可变长度字符串 最多能存储 2GB，存储超过 VARCHAR2 的长文本信息  
ps.一个表中只有一列为 LONG 数据类型，  
.LONG 列不能建立索引，  
.存储过程不能接受 LONG 数据类型的参数  
  
  
2、数值数据类型  
NUMBER 数据类型可以存储 正数、负数、零、定点数(不带小数点的？)和精度为38为的浮点数。  
格式： NUMBER \[(precision 精度，数字总位数 1～38间  
, scale 范围，小数点右边的位数 -84～127间)\]  
  
  
3、时期时间数据类型  
DATE 数据类型，用于存储表中日期和时间数据。SYSDATE 函数功能就是返回当前的日期和时间。  
TIMESTAMP 数据类型，存储时期、时间和时区信息。SYSTIMEATAMP 功能就是返回当前日期、时间和时区。  
  
  
4、二进制数据类型  
RAW 二进制数据或字节串 长度 1～2000 字节，定义时应指定大小，可建索引  
LONG RAW 可变长度的二进制数据 最大能存储 2GB，限制等同于 LONG 数据类型  
  
  
5、LOB 数据类型  
“大对象”数据类型，最多可存储多达 4GB的信息。LOB 可以是外部的，也可以是内部的，取决于相对于数据库位置。  
CLOB Character LOB 存储大量的字符数据  
BLOB Binary LOB 存储大量的二进制对象（多媒体对象等）  
BFILE Binary FIle 能够将二进制文件存储在数据库外部的操作系统文件中  
BFILE 存储一个 BFILE 定位器，它指向位于服务器文件系统上的二进制文件。  
ps.一个表中可以有多个 LOB 列，每个 LOB 列可以是不同的 LOB 类型。  
  
  
  
6、伪列  
Oracle 中的一个表列，但实际上未存储表中。可以从表中查询，但是不能插入，更新或者删除。  
  
ROWID 返回行记录的行地址，通常情况下，ROWID 值可以唯一地标识数据库中的一行。  
作用： .能最快形式访问表中的一行。  
.能显示表中的行是如何存储的。  
.可以作为表中行的唯一标识。  
例：SELECT ROWID, \* FROM EMP WHERE empno='7900';  
  
  
ROWNUM 返回一个数值单表行的次序，第一行为1，第二行为2![dot.gif][]![dot.gif][].  
通过使用 ROWNUM 用户可以限制查询返回的行数(或者分页？)  
例：SELECT \* FROM EMP WHERE ROWNUM <= 10;

![ExpandedBlockStart.gif][]  
Oracle 函数  
函数接受一个或多个参数并返回一个值。  
  
单行函数  
也称标量函数，对于从表中查询的每一行，该函数都返回一个值。  
单行函数出现在 SLEECT / WHERE 子句中。  
  
1、日期函数  
对日期值进行运算，根据用途产生日期/数值类型的结果。  
ADD\_MONTHS(d, n) 返回 指定日期加上月数后的 日期值  
MONTHS\_BETWEEN(d1, d2) 返回 两个日期间的 月数  
LAST\_DAY(d) 返回 指定日期当前的最后一天的 日期值  
RONUD(d,\[fmt\]) 返回 指定日期四舍五入格式(YEAR、MONTH、DAY)后的 日期值  
NEXT\_DAY(d,day) 返回 指定日期下一个星期几的 日期值  
TRUNC(d,\[fnt\]) 返回 指定日期截断为格式后的 日期值  
EXTRACT(fmt FROM d) 返回 指定日期提取的格式的 值  
  
  
2、字符函数  
字符函数接受字符输入，并返回字符或数值。  
INITCAP(char) 首字母大写  
LOWER(char) 转换为小写  
UPPER(char) 转换为大写  
LTRIM(char, set) 左裁切  
RTRIM(char, set) 右裁切  
TRANSLATE(char, from, to) 按字母翻译  
REPLACE(char, search\_str, replace\_str) 字符串替换  
INSTR(char, substr\[,pos\]) 查找子串位置  
SUBSTR(char, pos, len) 取子字符串  
CONCAT(char1, char2) 连接字符串  
  
CHR(ascii) 根据 ASCII 码返回对应字符串  
LPAD / RPAD 左 / 右 填充  
LPAD ('function', 15 , '=') 返回 '=======function'  
TRAM 开头或结尾(或 开头和结尾)裁剪特定的字符，默认裁剪空格。  
TRIM (\[LEADING | TRAILING\] trim\_char)  
LENGTH(char) 返回字符串长度  
DECODE 逐个值进行字符串替换  
DECODE (expr, search1, result1, search2, result2, ![dot.gif][]![dot.gif][]\[ ,default\])  
DECODE (ostalus, 'p', '准备处理', 'c', '已完成')  
  
  
  
3、数字函数  
数字函数接受数字输入并返回数值作为输出结果。  
ABS(n) 取绝对值  
CEIL(n) 向上取值  
FLOOR(n) 向下去整  
SIN(n) 正弦值  
COS(n) 余弦值  
POWER(m, n) 指数函数  
SQRT(n) 平方根  
MOD(m, n) 取余  
ROUND(m, n) 小数点后精度四舍五入  
TRUNC(m, n) 小数点后精度截断  
  
  
4、转换函数  
转换函数将一种数据类型转换为另一种数据类型。  
TO\_CHAR (d|n, \[,fmt\]) 格式化 日期 / 数值  
TO\_DATE (char \[,fmt\]) 将 fmt模型格式的字符串 转换为日期型  
TO\_NUMBER (char) 将 包含数字的的字符串转换为 数值型  
  
  
5、其他函数  
NVL (exp, exp2) 如果 exp 为空返回 exp2；如果非空返回 exp  
NVL2 (exp, exp2, exp3) 如果 exp 为空返回 exp3；如果非空返回 exp2  
NULLIF (exp1, exp2) 比较两表达式，相等返回空值，不等则返回 exp1  
  
  
  
  
  
分组函数 / 聚合函数  
分组函数基于一组行返回结果，即为每一组行返回单个值。  
  
AVG (columname) 返回指定列的平均值  
MAX (columname) 返回指定列的最大值  
MIN (columname) 返回指定列的最小值  
SUM (columname) 返回指定列的总值  
COUNT  
COUNT (\*) 统计所有行个数，包括重复行和空值得行  
COUNT (columname) 统计指定列非空值的个行数  
COUNT (DISTINCR columname) 统计指定列中 非重复，非空值得行个数  
  
  
GROUP BY 子句  
用于将信息表划分为组，对查询结果按组进行聚合运算，为每组返回一个结果。  
HAVING 子句  
用来指定 GROUP BY 子句的检索条件。  
  
  
  
  
分析函数  
分析函数根据一组行来计算聚合值。这些函数通常用来完成对聚集的累积排名、移动平均数和报表计算。  
分析函数与聚合函数不同的是他们为每组记录返回多个行。  
  
ROW\_NUMBER () OVER (\[PARTITION BY colum\] ORDER BY colum)  
为有序组中的每一行返回一个唯一的排序值，序号由 ORDER BY 子句指定，从 1 开始，即使具有相等的值，排位也不同。  
PARTITION BY colum 按列值进行区分，各分组内在进行排序。  
  
RANK () OVER (\[PARTITION BY colum\] ORDER BY colum)  
计算一个值在一个组中的地位，由 1 开头，具有相等值得行排位相同，序数随后跳跃相应的数值。  
  
DENSE\_RANK () OVER (\[PARTITION BY colum\] ORDER BY colum)  
计算一个值在一个组中的地位，由 1 开头，具有相等值得行排位相同，并且排位是连续的。

**5、\[转\] Oracle 常用SQL语法和数据对象**

![ExpandedBlockStart.gif][]  
=============================================================================================  
一.数据控制语句 (DML) 部分  
=============================================================================================  
1.INSERT (往数据表里插入记录的语句)  
  
INSERT INTO 表名(字段名1, 字段名2, ……) VALUES ( 值1, 值2, ……);  
INSERT INTO 表名(字段名1, 字段名2, ……) SELECT 字段名1, 字段名2, …… FROM 另外的表名;  
  
字符串类型的字段值必须用单引号括起来, 例如: ’GOOD DAY’  
如果字段值里包含单引号’ 需要进行字符串转换, 我们把它替换成两个单引号''.  
字符串类型的字段值超过定义的长度会出错, 最好在插入前进行长度校验.  
  
日期字段的字段值可以用当前数据库的系统时间SYSDATE, 精确到秒  
或者用字符串转换成日期型函数TO\_DATE(‘2001-08-01’,’YYYY-MM-DD’)  
TO\_DATE()还有很多种日期格式, 可以参看ORACLE DOC.  
年-月-日 小时:分钟:秒 的格式YYYY-MM-DD HH24:MI:SS  
  
INSERT时最大可操作的字符串长度小于等于4000个单字节, 如果要插入更长的字符串, 请考虑字段用CLOB类型,  
方法借用ORACLE里自带的DBMS\_LOB程序包.  
  
INSERT时如果要用到从1开始自动增长的序列号, 应该先建立一个序列号  
CREATE SEQUENCE 序列号的名称 (最好是表名+序列号标记) INCREMENT BY 1 START WITH 1  
MAXVALUE 99999 CYCLE NOCACHE;  
其中最大的值按字段的长度来定, 如果定义的自动增长的序列号 NUMBER(6) , 最大值为999999  
INSERT 语句插入这个字段值为: 序列号的名称.NEXTVAL  
  
\------------------------------------------------------------------  
2.DELETE (删除数据表里记录的语句)  
  
DELETE FROM表名 WHERE 条件;  
  
注意：删除记录并不能释放ORACLE里被占用的数据块表空间. 它只把那些被删除的数据块标成unused.  
  
如果确实要删除一个大表里的全部记录, 可以用 TRUNCATE 命令, 它可以释放占用的数据块表空间  
TRUNCATE TABLE 表名;  
此操作不可回退.  
  
\------------------------------------------------------------------  
3.UPDATE (修改数据表里记录的语句)  
  
UPDATE表名 SET 字段名1=值1, 字段名2=值2, …… WHERE 条件;  
  
如果修改的值N没有赋值或定义时, 将把原来的记录内容清为NULL, 最好在修改前进行非空校验;  
值N超过定义的长度会出错, 最好在插入前进行长度校验..  
  
\------------------------------------------------------------------  
注意事项:  
A. 以上SQL语句对表都加上了行级锁,  
确认完成后, 必须加上事物处理结束的命令 COMMIT 才能正式生效,  
否则改变不一定写入数据库里.  
如果想撤回这些操作, 可以用命令 ROLLBACK 复原.  
  
B. 在运行INSERT, DELETE 和 UPDATE 语句前最好估算一下可能操作的记录范围,  
应该把它限定在较小 (一万条记录) 范围内,. 否则ORACLE处理这个事物用到很大的回退段.  
程序响应慢甚至失去响应. 如果记录数上十万以上这些操作, 可以把这些SQL语句分段分次完成,  
其间加上COMMIT 确认事物处理.  
  
=============================================================================================  
二.数据定义 (DDL) 部分  
=============================================================================================  
  
1.CREATE (创建表, 索引, 视图, 同义词, 过程, 函数, 数据库链接等)  
  
ORACLE常用的字段类型有  
CHAR 固定长度的字符串  
VARCHAR2 可变长度的字符串  
NUMBER(M,N) 数字型M是位数总长度, N是小数的长度  
DATE 日期类型  
  
创建表时要把较小的不为空的字段放在前面, 可能为空的字段放在后面  
  
创建表时可以用中文的字段名, 但最好还是用英文的字段名  
  
创建表时可以给字段加上默认值, 例如 DEFAULT SYSDATE  
这样每次插入和修改时, 不用程序操作这个字段都能得到动作的时间  
  
创建表时可以给字段加上约束条件  
例如 不允许重复 UNIQUE, 关键字 PRIMARY KEY  
  
\------------------------------------------------------------------  
2.ALTER (改变表, 索引, 视图等)  
  
改变表的名称  
ALTER TABLE 表名1 TO 表名2;  
  
在表的后面增加一个字段  
ALTER TABLE表名 ADD 字段名 字段名描述;  
  
修改表里字段的定义描述  
ALTER TABLE表名 MODIFY字段名 字段名描述;  
  
给表里的字段加上约束条件  
ALTER TABLE 表名 ADD CONSTRAINT 约束名 PRIMARY KEY (字段名);  
ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE (字段名);  
  
把表放在或取出数据库的内存区  
ALTER TABLE 表名 CACHE;  
ALTER TABLE 表名 NOCACHE;  
  
\------------------------------------------------------------------  
3.DROP (删除表, 索引, 视图, 同义词, 过程, 函数, 数据库链接等)  
  
删除表和它所有的约束条件  
DROP TABLE 表名 CASCADE CONSTRAINTS;  
  
\------------------------------------------------------------------  
4.TRUNCATE (清空表里的所有记录, 保留表的结构)  
  
TRUNCATE 表名;  
  
=============================================================================================  
三.查询语句 (SELECT) 部分  
=============================================================================================  
  
SELECT字段名1, 字段名2, …… FROM 表名1, \[表名2, ……\] WHERE 条件;  
  
字段名可以带入函数  
例如: COUNT(\*), MIN(字段名), MAX(字段名), AVG(字段名), DISTINCT(字段名),  
TO\_CHAR(DATE字段名,'YYYY-MM-DD HH24:MI:SS')  
\---------------------------------------------------------------  
NVL(EXPR1, EXPR2)函数  
解释:  
IF EXPR1=NULL  
RETURN EXPR2  
ELSE  
RETURN EXPR1  
\---------------------------------------------------------------  
DECODE(AA﹐V1﹐R1﹐V2﹐R2![dot.gif][].)函数  
解释:  
IF AA=V1 THEN RETURN R1  
IF AA=V2 THEN RETURN R2  
..…  
ELSE  
RETURN NULL  
\---------------------------------------------------------------  
LPAD(char1,n,char2)函数  
解释:  
字符char1按制定的位数n显示，不足的位数用char2字符串替换左边的空位  
\---------------------------------------------------------------  
字段名之间可以进行算术运算  
例如: (字段名1\*字段名1)/3  
\---------------------------------------------------------------  
  
查询语句可以嵌套  
例如: SELECT …… FROM  
(SELECT …… FROM表名1, \[表名2, ……\] WHERE 条件) WHERE 条件2;  
\---------------------------------------------------------------  
两个查询语句的结果可以做集合操作  
例如: 并集 UNION (去掉重复记录),  
并集 UNION ALL(不去掉重复记录),  
差集 MINUS,  
交集 INTERSECT  
  
\----------------------------------------------------------------  
分组查询  
SELECT字段名1, 字段名2, …… FROM 表名1, \[表名2, ……\] GROUP BY字段名1  
\[HAVING 条件\] ;  
  
两个以上表之间的连接查询  
  
SELECT 字段名1, 字段名2, ……  
FROM 表名1, \[表名2, ……\]  
WHERE 表名1.字段名 = 表名2. 字段名  
\[ AND ……\] ;  
  
SELECT字段名1, 字段名2, ……  
FROM 表名1, \[表名2, ……\]  
WHERE 表名1.字段名 = 表名2. 字段名(+)  
\[ AND ……\] ;  
  
有(+)号的字段位置自动补空值  
\----------------------------------------------------------  
查询结果集的排序操作, 默认的排序是升序ASC, 降序是DESC  
  
SELECT字段名1, 字段名2, …… FROM 表名1, \[表名2, ……\]  
ORDER BY字段名1, 字段名2 DESC;  
\----------------------------------------------------------  
字符串模糊比较的方法  
  
INSTR(字段名, ‘字符串’)>0  
字段名 LIKE ‘字符串%’ \[‘%字符串%’\]  
  
每个表都有一个隐含的字段ROWID, 它标记着记录的唯一性.  
  
=============================================================================================  
四.ORACLE里常用的数据对象 (SCHEMA)  
=============================================================================================  
  
1.索引 (INDEX)  
  
CREATE INDEX 索引名ON 表名 ( 字段1, \[字段2, ……\] );  
ALTER INDEX 索引名 REBUILD;  
  
一个表的索引最好不要超过三个 (特殊的大表除外), 最好用单字段索引, 结合SQL语句的分析执行情况,  
也可以建立多字段的组合索引和基于函数的索引  
  
ORACLE8.1.7字符串可以索引的最大长度为1578 单字节  
ORACLE8.0.6字符串可以索引的最大长度为758 单字节  
  
ORACLE DOC上说字符串最大可以建索引的长度约是:数据块的大小(db\_block\_size)\*40%  
  
\----------------------------------------------------------------  
2.视图 (VIEW)  
  
CREATE VIEW 视图名AS SELECT …. FROM …..;  
ALTER VIEW视图名 COMPILE;  
  
视图仅是一个SQL查询语句, 它可以把表之间复杂的关系简洁化.  
  
\----------------------------------------------------------------  
3.同义词 (SYNONMY)  
CREATE SYNONYM同义词名FOR 表名;  
CREATE SYNONYM同义词名FOR 表名@数据库链接名;  
  
\----------------------------------------------------------------  
4.数据库链接 (DATABASE LINK)  
CREATE DATABASE LINK数据库链接名CONNECT TO 用户名 IDENTIFIED BY 密码 USING ‘数据库连接字符串’;  
  
数据库连接字符串可以用NET8 EASY CONFIG或者直接修改TNSNAMES.ORA里定义.  
  
数据库参数global\_name=true时要求数据库链接名称跟远端数据库名称一样  
  
数据库全局名称可以用以下命令查出  
SELECT \* FROM GLOBAL\_NAME;  
  
查询远端数据库里的表  
SELECT …… FROM 表名@数据库链接名;  
  
=============================================================================================  
五.权限管理 (DCL) 语句  
=============================================================================================  
  
1.GRANT 赋于权限  
常用的系统权限集合有以下三个:  
CONNECT(基本的连接), RESOURCE(程序开发), DBA(数据库管理)  
常用的数据对象权限有以下五个:  
ALL ON 数据对象名, SELECT ON 数据对象名, UPDATE ON 数据对象名,  
DELETE ON 数据对象名, INSERT ON 数据对象名, ALTER ON 数据对象名  
  
GRANT CONNECT, RESOURCE TO 用户名;  
GRANT SELECT ON 表名 TO 用户名;  
GRANT SELECT, INSERT, DELETE ON表名 TO 用户名1, 用户名2;  
  
\-------------------------------------------------------------------  
2.REVOKE 回收权限  
  
REVOKE CONNECT, RESOURCE FROM 用户名;  
REVOKE SELECT ON 表名 FROM 用户名;  
REVOKE SELECT, INSERT, DELETE ON表名 FROM 用户名1, 用户名2;  
  
=============================================================================================  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
=============================================================================================

![ExpandedBlockStart.gif][]  
=============================================================================================  
二.数据定义 (DDL) 部分  
=============================================================================================  
1.CREATE (创建表, 索引, 视图, 同义词, 过程, 函数, 数据库链接等)  
  
ORACLE常用的字段类型有  
CHAR 固定长度的字符串  
VARCHAR2 可变长度的字符串  
NUMBER(M,N) 数字型M是位数总长度, N是小数的长度  
DATE 日期类型  
  
创建表时要把较小的不为空的字段放在前面, 可能为空的字段放在后面  
  
创建表时可以用中文的字段名, 但最好还是用英文的字段名  
  
创建表时可以给字段加上默认值, 例如 DEFAULT SYSDATE  
这样每次插入和修改时, 不用程序操作这个字段都能得到动作的时间  
  
创建表时可以给字段加上约束条件  
例如 不允许重复 UNIQUE, 关键字 PRIMARY KEY  
  
\------------------------------------------------------------------  
2.ALTER (改变表, 索引, 视图等)  
  
改变表的名称  
ALTER TABLE 表名1 TO 表名2;  
  
在表的后面增加一个字段  
ALTER TABLE表名 ADD 字段名 字段名描述;  
  
修改表里字段的定义描述  
ALTER TABLE表名 MODIFY字段名 字段名描述;  
  
给表里的字段加上约束条件  
ALTER TABLE 表名 ADD CONSTRAINT 约束名 PRIMARY KEY (字段名);  
ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE (字段名);  
  
把表放在或取出数据库的内存区  
ALTER TABLE 表名 CACHE;  
ALTER TABLE 表名 NOCACHE;  
  
\------------------------------------------------------------------  
3.DROP (删除表, 索引, 视图, 同义词, 过程, 函数, 数据库链接等)  
  
删除表和它所有的约束条件  
DROP TABLE 表名 CASCADE CONSTRAINTS;  
  
\------------------------------------------------------------------  
4.TRUNCATE (清空表里的所有记录, 保留表的结构)  
  
TRUNCATE 表名;  
  
=============================================================================================  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
=============================================================================================

![ExpandedBlockStart.gif][]  
================================================================================  
三.查询语句 (SELECT) 部分  
================================================================================  
  
SELECT 字段名1, 字段名2, ……  
FROM 表名1, \[表名2, ……\]  
WHERE 条件;  
\--------------------------  
字段名可以带入函数  
例如: COUNT(\*), MIN(字段名), MAX(字段名), AVG(字段名), DISTINCT(字段名),  
TO\_CHAR(DATE字段名,'YYYY-MM-DD HH24:MI:SS')  
\------------------------------  
NVL(EXPR1, EXPR2)函数  
解释:  
IF EXPR1=NULL  
RETURN EXPR2  
ELSE  
RETURN EXPR1  
\------------------------------  
DECODE(AA﹐V1﹐R1﹐V2﹐R2![dot.gif][].)函数  
解释:  
IF AA=V1 THEN RETURN R1  
IF AA=V2 THEN RETURN R2  
..…  
ELSE  
RETURN NULL  
\------------------------------  
LPAD(char1,n,char2)函数  
解释:  
字符char1按制定的位数n显示，不足的位数用char2字符串替换左边的空位  
\------------------------------  
字段名之间可以进行算术运算  
例如: (字段名1\*字段名1)/3  
  
  
===============================================================  
查询语句可以嵌套  
\---------------------  
例如:  
SELECT …… FROM  
(  
SELECT ……  
FROM表名1, \[表名2, ……\]  
WHERE 条件1  
)  
WHERE 条件2;  
  
===============================================================  
两个查询语句的结果可以做集合操作  
\--------------------------------  
例如: 并集 UNION (去掉重复记录),  
并集 UNION ALL(不去掉重复记录),  
差集 MINUS,  
交集 INTERSECT  
  
===============================================================  
分组查询  
\------------  
SELECT 字段名1, 字段名2, ……  
FROM 表名1, 表名2, ……  
GROUP BY 字段名1  
HAVING 条件  
  
  
===============================================================  
两个以上表之间的连接查询  
\-----------------------  
SELECT 字段名1, 字段名2, ……  
FROM 表名1, \[表名2, ……\]  
WHERE 表名1.字段名 = 表名2. 字段名  
\[ AND ……\] ;  
  
SELECT 字段名1, 字段名2, ……  
FROM 表名1, \[表名2, ……\]  
WHERE 表名1.字段名 = 表名2. 字段名(+)  
\[ AND ……\] ;  
  
有(+)号的字段位置自动补空值  
  
===============================================================  
查询结果集的排序操作, 默认的排序是升序ASC, 降序是DESC  
\----------------------------------------------------  
SELECT 字段名1, 字段名2, ……  
FROM 表名1, \[表名2, ……\]  
ORDER BY 字段名1, 字段名2 DESC;  
  
===============================================================  
字符串模糊比较的方法  
\----------------------  
INSTR(字段名, ‘字符串’)>0  
字段名 LIKE ‘字符串%’ \[‘%字符串%’\]  
  
  
\----------------------------------------------------------------  
每个表都有一个隐含的字段ROWID, 它标记着记录的唯一性.  
  
=============================================================================================  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
=============================================================================================

![ExpandedBlockStart.gif][]  
=============================================================================================  
四.ORACLE里常用的数据对象 (SCHEMA)  
=============================================================================================  
  
1.索引 (INDEX)  
  
CREATE INDEX 索引名ON 表名 ( 字段1, \[字段2, ……\] );  
ALTER INDEX 索引名 REBUILD;  
  
一个表的索引最好不要超过三个 (特殊的大表除外), 最好用单字段索引, 结合SQL语句的分析执行情况,  
也可以建立多字段的组合索引和基于函数的索引  
  
ORACLE8.1.7字符串可以索引的最大长度为1578 单字节  
ORACLE8.0.6字符串可以索引的最大长度为758 单字节  
  
ORACLE DOC上说字符串最大可以建索引的长度约是:数据块的大小(db\_block\_size)\*40%  
\---------------------------------------------------------------------------------------  
  
\----------------------------------------------------------------  
2.视图 (VIEW)  
  
CREATE VIEW 视图名AS SELECT …. FROM …..;  
ALTER VIEW视图名 COMPILE;  
  
视图仅是一个SQL查询语句, 它可以把表之间复杂的关系简洁化.  
  
\----------------------------------------------------------------  
3.同义词 (SYNONMY)  
CREATE SYNONYM同义词名FOR 表名;  
CREATE SYNONYM同义词名FOR 表名@数据库链接名;  
  
\----------------------------------------------------------------  
4.数据库链接 (DATABASE LINK)  
CREATE DATABASE LINK数据库链接名CONNECT TO 用户名 IDENTIFIED BY 密码 USING ‘数据库连接字符串’;  
  
数据库连接字符串可以用NET8 EASY CONFIG或者直接修改TNSNAMES.ORA里定义.  
  
数据库参数global\_name=true时要求数据库链接名称跟远端数据库名称一样  
  
数据库全局名称可以用以下命令查出  
SELECT \* FROM GLOBAL\_NAME;  
  
查询远端数据库里的表  
SELECT …… FROM 表名@数据库链接名;  
  
=============================================================================================  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
=============================================================================================

![ExpandedBlockStart.gif][]  
=============================================================================================  
五.权限管理 (DCL) 语句  
=============================================================================================  
  
1.GRANT 赋于权限  
常用的系统权限集合有以下三个:  
CONNECT(基本的连接), RESOURCE(程序开发), DBA(数据库管理)  
常用的数据对象权限有以下五个:  
ALL ON 数据对象名, SELECT ON 数据对象名, UPDATE ON 数据对象名,  
DELETE ON 数据对象名, INSERT ON 数据对象名, ALTER ON 数据对象名  
  
GRANT CONNECT, RESOURCE TO 用户名;  
GRANT SELECT ON 表名 TO 用户名;  
GRANT SELECT, INSERT, DELETE ON表名 TO 用户名1, 用户名2;  
  
\-------------------------------------------------------------------  
2.REVOKE 回收权限  
  
REVOKE CONNECT, RESOURCE FROM 用户名;  
REVOKE SELECT ON 表名 FROM 用户名;  
REVOKE SELECT, INSERT, DELETE ON表名 FROM 用户名1, 用户名2;  
  
=============================================================================================  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
=============================================================================================

\--------------------------------------------------------------------------------

![ExpandedBlockStart.gif][]  
=====================================================================  
使用表达式  
=====================================================================  
  
Select ename || ' 是一位 ' || job As 雇员细节,  
to\_char(hiredate,'yyyy-mm-dd') As 雇佣时间,  
sal\*1.2  
From emp;  
  
=====================================================================  
取消重复行 distinct  
=====================================================================  
  
Select Distinct deptno, job From emp;  
  
=====================================================================  
指定列排序 order by  
=====================================================================  
asc 升序，desc 降序  
\--------------------------------------------  
Select \* From emp  
where sal between 1500 and 3000  
Order By deptno Desc,ename;  
  
\--------------------------------------------  
如果使用dsitinct,排序列必须是选择列  
\---------------------------  
select distinct depton, job  
from emp  
order by job;  
\---------------------------------------------  
order by 子句必须是最后一个子句  
=====================================================================  
=====================================================================

![ExpandedBlockStart.gif][]  
============================================================  
分组查询语句  
============================================================  
1。组处理函数不能出现在 where 子句中  
2。选择列表中的列、表达式，必须出现在 group by 子句中  
3。组处理函数中可以指定 all 和 distinct  
============================================================  
分组函数  
\---------------------------------------  
select avg(sal) as avg1,  
avg(distinct sal) as avg2,  
max(sal) as max,  
min(sal) as min,  
sum(sal) as sum,  
count(\*) as cnt1,  
count(sal) as cnt2,  
count(distinct sal) as cnt3  
from emp  
where deptno = 30;  
  
============================================================  
单列分组  
\---------------------------------------------  
select deptno, avg(sal), max(sal) from emp  
group by deptno;  
  
select deptno, avg(sal), max(sal) from emp  
group by deptno  
order by avg(sal);  
  
============================================================  
多列分组  
\---------------------------------------------  
select deptno, job, avg(sal), max(sal), from emp  
group by deptno, job;  
  
============================================================  
============================================================  
rollup 用于生成横向统计信息  
\--------------------------  
Select deptno, job, Avg(sal), Max(sal) From emp  
Group By rollup(deptno,job)  
  
\--------------------------------------------------  
cube 用于生产纵向统计信息  
\------------------------------  
Select deptno, job, Avg(sal), Max(sal) From emp  
Group By Cube(deptno,job)  
  
  
============================================================  
having 子句  
============================================================  
select deptno, avg(sal), max(sal) from emp  
group by deptno  
having avg(sal) > 2000;  
  
============================================================  
============================================================

![ExpandedBlockStart.gif][]  
===========================================================  
不等连接  
\----------------------------  
Select e.ename, e.sal, s.grade  
From emp e,salgrade s  
Where e.sal Between s.losal And s.hisal  
And e.deptno = 30  
  
===========================================================  
自连接  
\---------------------------------------  
Select e.ename As 雇员, p.ename As 管理员  
From emp e,emp p  
Where e.mgr = p.empno  
And e.deptno = 30  
  
===========================================================  
合并查询  
===========================================================  
union --- 两个集合的并集，去掉重复行，按第一列结构排序  
\-------------------------  
Select empno, ename, mgr From emp  
Where deptno = 30  
Union  
Select empno, ename, mgr From emp  
Where job = 'MANAGER'  
===========================================================  
union all --- 两个集合并集，不去重复行，不排序  
\-------------------------  
Select empno, ename, mgr From emp  
Where deptno = 30  
Union All  
Select empno, ename, mgr From emp  
Where job = 'MANAGER'  
  
===========================================================  
Intersect --- 只会显示同时存在两个集合中的数据  
\-----------------------------------  
Select empno, ename, mgr From emp  
Where deptno = 30  
Intersect  
Select empno, ename, mgr From emp  
Where job = 'MANAGER'  
  
===========================================================  
minus --- 在一个集合存在，在第二个集合不存在的数据，按第一个排序  
\-----------------------------------  
Select empno, ename, mgr From emp  
Where deptno = 30  
Minus  
Select empno, ename, mgr From emp  
Where job = 'MANAGER'  
  
===========================================================  
合并查询中，只能有一个 order by 子句。在这个子句中使用列名或  
第一个查询的别名。  
\--------------------------------------  
Select empno, ename 雇员, mgr From emp  
Where deptno = 30  
Minus  
Select empno, ename 雇员, mgr From emp  
Where job = 'MANAGER'  
Order By 雇员  
  
===========================================================  
===========================================================

![ExpandedBlockStart.gif][]  
============================================================  
单行子查询  
\------------------------------  
Select ename, deptno, sal From emp  
Where sal = (Select Max(sal) From emp);  
  
============================================================  
多行子查询 --- where子句中使用多行子查询，必须使用多行运算符，  
（in,notin,exists,not exists,all,any）  
\---------------------------------------------------  
Select ename, deptno, sal, job From emp  
Where job In ( Select Distinct job From emp Where deptno = 20 )  
  
\------------------------------------------------------------  
Select e.ename, e.job, e.sal  
From emp e  
Where sal > All ( Select sal From emp Where emp.deptno = 20)  
  
\------------------------------------------------------------  
Select e.ename, e.job, e.sal  
From emp e  
Where sal > Any ( Select sal From emp Where emp.deptno = 20)  
  
============================================================  
相关子查询  
\------------------------------  
Select deptno,  
( Select Max(sal) From emp b Where b.deptno = a.deptno ) maxsal  
From emp a  
Order By deptno  
\------------------------------  
Select ename, deptno, sal, job From emp  
Where Exists  
(  
Select 'x' From dept  
Where dept.deptno = emp.deptno And dept.loc = 'NEW YORK'  
)  
  
============================================================  
标量子查询 --- 显示每个部门的最高工资员工信息  
\-----------------------  
select distinct deptno,  
(select max(sal) from emp b where b.deptno = a.deptno) maxsal  
from emp a  
order by deptno;  
  
============================================================  
多列子查询 --- 显示与smith部门和岗位完全相同的所有雇员信息。  
\-----------------------  
Select ename, deptno, sal, job From emp  
Where (deptno, job) =  
(Select deptno, job From emp Where ename = 'SMITH')  
\-----------------------  
显示岗位或者管理员匹配于部门编号为20的所有雇员信息.  
\-----------------------  
Select ename, deptno, sal, job, mgr From emp  
Where job In ( Select job From emp Where deptno = 20 )  
Or mgr In ( Select mgr From emp Where deptno = 20 )  
Order By deptno  
  
============================================================

![ExpandedBlockStart.gif][]  
========================================================  
DDL 中使用子查询  
========================================================  
create table 语句中的子查询  
\--------------------------  
create table dept1 (deptno, dname, loc) as  
select deptno, dname, loc from dept;  
  
create table emp1 as  
select \* from emp;  
========================================================  
create view 中使用子查询  
\-----------------------  
create or replace view dept\_20 as  
select \* from emp1 where deptno = 20 order by empno;  
  
  
========================================================  
DML 中使用子查询  
========================================================  
update 语句中使用子查询  
\-----------------------  
update emp1 set (sal,comm) =  
( select sal, comm from emp1 where ename = 'WARD' )  
where job = ( select job from emp1 where ename= 'WARD' )  
  
========================================================  
delete 语句中使用子查询  
\-----------------------  
delete from emp1  
where deptno = (select deptno from dept1 where dname = 'ACCOUNTING' )  
  
========================================================  
insert 语句中使用子查询  
\-----------------------  
insert into emp1  
select \* from emp  
where deptno = (select deptno from where dname = 'ACCOUNTING')  
  
========================================================

\-----------------------------------------------------------------------------------

![ExpandedBlockStart.gif][]  
============================================================  
基础查询分类  
============================================================  
基本查询 --- 所有列、指定列、where子句、order by子句  
\---------------------------------------------------  
分组查询 --- 组处理函数、group by子句、having子句  
\---------------------------------------------------  
连接查询 --- 相等连接、不等连接、自我连接  
============================================================  
  
============================================================  
合并查询 --- UNION，UNION ALL，INTERSECT，MINUS  
\-----------------------------------------------------  
子查询 --- 单行、多行、相关、标量、多列、DDL中、DML中  
============================================================  
  
  
============================================================  
  
1。order by 子句必须放在最后。  
  
2。组处理函数只能出现在选择列表、order by子句、having子句中，  
不能出现在where子句和group by子句中。  
  
3。在选择列表中包含的列、表达式，则一定要出现在group by子句中。  
  
4。where子句中可以使用单行子查询，可以使用单行运算符。  
（ ＝，>，<，>=，<=，<> ）  
  
5。where子句中可以使用多行子查询，可以使用多行运算符。  
（ in，not in，exists，not exists，all，any ）  
  
============================================================

![ExpandedBlockStart.gif][]  
===================================================================================  
基本的Sql编写注意事项  
\---------------------  
1。尽量少用IN操作符，基本上所有的IN操作符都可以用EXISTS代替。  
  
2。不用NOT IN操作符，可以用NOT EXISTS或者外连接+替代。  
  
3。Oracle在执行IN子查询时，首先执行子查询，将查询结果放入临时表再执行主查询。  
而EXIST则是首先检查主查询，然后运行子查询直到找到第一个匹配项。  
NOT EXISTS 比 NOT IN 效率稍高。但具体在选择IN或EXIST操作时，  
要根据主子表数据量大小来具体考虑。  
  
4。不用“<>”或者“!=”操作符。对不等于操作符的处理会造成全表扫描，  
可以用“<” or “>”代替。  
  
5。Where子句中出现IS NULL或者IS NOT NULL时，Oracle会停止使用索引而执行全表扫描。  
可以考虑在设计表时，对索引列设置为NOT NULL。这样就可以用其他操作来取代判断NULL的操作。  
  
6。当通配符“%”或者“\_”作为查询字符串的第一个字符时，索引不会被使用。  
  
7。对于有连接的列“||”，最后一个连接列索引会无效。尽量避免连接，  
可以分开连接或者使用不作用在列上的函数替代。  
  
8。如果索引不是基于函数的，那么当在Where子句中对索引列使用函数时，索引不再起作用。  
  
9。Where子句中避免在索引列上使用计算，否则将导致索引失效而进行全表扫描。  
  
10。对数据类型不同的列进行比较时，会使索引失效。  
  
11。用“>=”替代“>”。  
  
12。UNION操作符会对结果进行筛选，消除重复，数据量大的情况下可能会引起磁盘排序。  
如果不需要删除重复记录，应该使用UNION ALL。  
  
13。Oracle从下到上处理Where子句中多个查询条件，所以表连接语句应写在其他Where条件前，  
可以过滤掉最大数量记录的条件必须写在Where子句的末尾。  
  
14。Oracle从右到左处理From子句中的表名，所以在From子句中包含多个表的情况下，  
将记录最少的表放在最后。  
  
15。Order By语句中的非索引列会降低性能，可以通过添加索引的方式处理。  
严格控制在Order By语句中使用表达式。  
  
16。不同区域出现的相同的Sql语句，要保证查询字符完全相同，以利用SGA共享池，  
防止相同的Sql语句被多次分析。  
  
17。当在Sql语句中连接多个表时，使用表的别名，并将之作为每列的前缀。这样可以减少解析时间。  
  
======================================================================================  
我们可以总结一下可能引起全表扫描的操作：  
\------------------------------------------  
1。在索引列上使用NOT或者“<>”;  
  
2。对索引列使用函数或者计算；  
  
3。NOT IN操作；  
  
4。通配符位于查询字符串的第一个字符；  
  
5。IS NULL或者IS NOT NULL；  
  
6。多列索引，但它的第一个列并没有被Where子句引用；  
======================================================================================

转载：[http://www.blogjava.net/kiant/articles/234781.html][http_www.blogjava.net_kiant_articles_234781.html]

[1_SQL]: http://www.blogjava.net/kiant/articles/234781.html#1
[2_SQL]: http://www.blogjava.net/kiant/articles/234781.html#2
[3_Oracle]: http://www.blogjava.net/kiant/articles/234781.html#3
[4_Oracle]: http://www.blogjava.net/kiant/articles/234781.html#4
[5_ Oracle _SQL]: http://www.blogjava.net/kiant/articles/234781.html#5
[ExpandedBlockStart.gif]: /images/20220519/58144a1265a148659c1f0a5cbf7db79f.png
[dot.gif]: /images/20220519/f4a1498affbb4d32913ca77c6fc64b3e.png
[http_www.blogjava.net_kiant_articles_234781.html]: http://www.blogjava.net/kiant/articles/234781.html