关闭

Oracle命令(三):Oracle要点

标签: Oracle
139人阅读 评论(0) 收藏 举报
分类:

SQL 要点

附录:

1SQL 简介

2SQL 操作

3Oracle 常数据

4Oracle 函数

5Oracle 常SQL语法

 

串函数

LENGTH()     长度

LENTTHB()    字节长度;一汉字内存 2字节

 

LTRIMRTRIMTRIM

 

SUBSTR(表达式,位置,长度)

Oracle 无左右取串函数,但可以使变通方式完成。

左取串:    SUBSTR('abcdefg', 1, 3)

右取串:    SUBSTR('abcedfg', LENGTH('abcdefg')-3+1, 3)

 

时间函数

sysdatecurrent_day

设置时间式:    ALERTSESSION 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 a1FROM 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, nameFROM d

或者利用查询结果创建新表:

  CREATE TABLE tttAS ttt(SELECT * FROM e)

 

附加:

 

一、SQL 简介

 

SQL Structured QueryLanguage,结构化查询语言)持如下类别命令:

数据定义语言:CREATE(创建)、ALTER(更改)、TRUNCATE断)、DROP(删除)令。

数据操纵语言:INSERT(插入)、SELECT(选择)、DELETE(删除)、UPDATE(修改)令。

事务控制语言:COMMIT(提交)、SAVEPOINT(保存点)、ROLLBACK(回滚)令。

数据控制语言:GRANT(授予)、REVOKE(回收)令。

 

特点:

1过程语言,它同时可以访问多条记录。

2、所有关系型数据库的通型语言,可移植性

3、对于数据和对象的操作简单。

 

数据定义语言

    于改变数据库结构,包括创建、修改和删除数据库对象。

 

1CREATE TABLE 创建表

CREATE TABLE [schema.]table

(columname    datetype[, .]);

 

.表名的最大长度30

.表名首字母字母,可以下划线、数字和字母,但不能使和单引号;

.同一式下的不同表不能有相同的名称;

.表名、列名、户名、和其他对象名不分大小写,系统会自动转换成大写。

 

2ALTER TABLE 修改表

ALTER TABLE <tablename>

    MODIFY (column definition);

    ADD (column definition);

    DROP COLUMN column;

 

3TRUNCATE TABLE 取表

TRUNCATE TABLE <tablename>

    快速删除记录并释空间,不使事务处理,无法回滚,效率高。

 

DESC <tablename>  查看表结构

 

4DROP 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;

 

二、SQL 操作

 

操作

    表达式有NUMBER 数据型的列名、数常量和连接它们的算操作组成。(+ - * /

 

比较操作

    于比较两表达式的

    =!=<><=>=BETWEEN  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

 

三、Oracle 常数据

 

1、字数据

CHAR        固定长度字        长度 12000字节,指定则默认 1字节

VARCHAR2    可变长度字        长度 14000字节,定义时必指定大小

LONG        可变长度字        最多能存储 2GB,存储超过 VARCHAR2 的长文信息

                    ps.只有一列 LONG 数据型,

                      .LONG 列不能建立索引,

                      .存储过程不能接受LONG 数据型的参数

 

2、数数据

NUMBER 数据型可以存储正数、负数、零、定点数(小数点的?)和精度38的浮点数。

式:NUMBER [(precision 精度,数字位数138

        , scale 范围,小数点右边的位数-84127)]

 

3、时期时间数据

DATE 数据型,于存储表日期和时间数据。SYSDATE 函数功能就返回当前的日期和时间。

TIMESTAMP 数据型,存储时期、时间和时信息。SYSTIMEATAMP 功能就返回当前日期、时间和时

 

4、二进制数据

RAW         二进制数据或字节串    长度12000 字节,定义时应指定大小,可建索引

LONG RAW    可变长度的二进制数据    最大能存储2GB,限制等同于LONG 数据

 

5LOB 数据

“大对象”数据型,最多可存储多达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.

    通过使 ROWNUM 户可以限制查询返回的行数(或者分页?)

例:SELECT * FROM EMPWHERE ROWNUM <= 10;

 

四、Oracle 函数

 

    函数接受一或多参数并返回一个值

 

单行函数

    也称标量函数,对于从表查询的每一行,该函数都返回一个值

    单行函数出现 SLEECT/ WHERE 子句

 

1、日期函数

    对日期进行运算,根据途产生日期/值类型的结果。

ADD_MONTHS(d, n)        返回指定日期加上月数后的日期

MONTHS_BETWEEN(d1, d2)  返回日期间的月数

LAST_DAY(d)                返回指定日期当前的最后一日期

RONUD(d,[fmt])            返回指定日期四舍五入(YEARMONTHDAY)后的日期

NEXT_DAY(d,day)         返回指定日期下一星期几的日期

TRUNC(d,[fnt])               返回指定日期为格式后的日期

EXTRACT(fmtFROM 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, [ ,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 开头,具有相等得行排位相同,并且排位连续的。

 

五、Oracle 常SQL语法和数据对象

 

=============================================================================================

            ().数据控制语句(DML) 部分

=============================================================================================

1.INSERT  (往数据表里插入记录的语句)

 

INSERT INTO 表名(字段名1, 字段名2, ……) VALUES ( 1, 2, ……); 

INSERT INTO 表名(字段名1, 字段名2, ……)  SELECT 字段名1, 字段名2, …… FROM 另外的表名;

 

型的字段须用单引号括起来, 例如: ’GOODDAY

如果字段里包单引号’ 需要进行字串转换, 我们把它替换成两单引号''. 

型的字段超过定义的长度会出错, 好在插入前进行长度.

 

日期字段的字段可以当前数据库的系统时间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.)函数

解释: 

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, 它标记着记录的一性. 

 

=============================================================================================

            ().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;

 

=============================================================================================

            ().简单查询

=============================================================================================

 

             使表达式

=====================================================================

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 子句必须是最后一子句

 

============================================================

            ().分组查询语句

============================================================

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;

 

============================================================

            ().连接查询

============================================================

不等连接

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

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.enameAs 雇员, 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 雇员

 

============================================================

            ().子查询

============================================================

 

单行子查询

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

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

 

========================================================

            (十一).DDL使子查询

========================================================

create table 语句的子查询

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

create table dept1(deptno, dname, loc) as

select deptno, dname, loc from dept;

 

create table emp1as 

select * from emp;

========================================================

create view 使子查询

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

create or replace view dept_20as

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')

 

============================================================

            (十三).基础查询分

============================================================

查询--- 所有列、指定列、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子句可以使多行子查询,可以使多行运算

    innot inexistsnot existsallany 

 

============================================================

             (十四).Sql编写注意事项

============================================================

1.量少IN操作,基上所有的IN操作都可以EXISTS代替。

 

2.NOT IN操作,可以NOT EXISTS或者外连接+替代。

 

3.Oracle执行IN子查询时,首先执行子查询,将查询结果入临时表再执行查询。

   EXIST则首先检查查询,然后运行子查询直到匹配项。

   NOT EXISTS NOT IN 效率稍高。但具体选择INEXIST操作时,

   要根据子表数据量大小来具体考虑。

 

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子句引


0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:6711次
    • 积分:277
    • 等级:
    • 排名:千里之外
    • 原创:23篇
    • 转载:4篇
    • 译文:0篇
    • 评论:0条
    文章分类