Oracle中的一些sql语句与函数总结

第一篇：Oracle中的一些sql语句与函数总结

Oracle中的一些sql语句与函数总结

1.concat函数

concat(字串1，字串2)：将字符串

1、字符串2 连在一起；CONCAT()只允许两个参数；

换言之，一次只能将两个字串串连起来。不过，在Oracle中，我们可以用'||来一次串连

多个字串。

SQL Server中是:select id+’, ’+name from table1 where id=’1’ 结果就是：1,王华。

2.ltrim与rtrim用法

ltrim: 也就是按照y中的字符一个一个截掉x中的字符，并且是从左边开始执行的，只要遇

到y中有的字符, x中的字符都会被截掉, 直到在x的字符中遇到y中没有的字符为止函数命

令才结束例如ltrim('abcdab','ab')结果并不是按‘ab’字符串来截断'abcdba'，而是分别按照字

母a和b分别去找，分别按不是a和b的字符处开始截取。ltrim函数是从匹配函数开始之

后出现在子串中任何字符都被屏蔽掉了;结果将是'cdab'。Rtrim用法与ltrim相反是从右边截

取。

3.MONTHS_BETWEEN函 MONTHS_BETWEEN(x, y)用于计算x和y之间有几个月。如果x在日历中比y早，那

么MONTHS_BETWEEN()就返回一个负数。

第二篇：oracle维护常用sql语句(主要)

1、oracle表空间利用率

SELECT UPPER(F.TABLESPACE_NAME)“表空间名”，D.TOT_GROOTTE_MB “表空间大小(M)”，D.TOT_GROOTTE_MBF.TOTAL_BYTES)D.TOT_GROOTTE_MB * 100,2),'990.99')||'%' “使用比(%)”，F.TOTAL_BYTES “空闲空间(M)”，F.MAX_BYTES “最大块(M)”

FROM(SELECT TABLESPACE_NAME，ROUND(SUM(BYTES)/(1024 * 1024), 2)TOTAL_BYTES，ROUND(MAX(BYTES)/(1024 * 1024), 2)MAX_BYTES

FROM SYS.DBA_FREE_SPACE

GROUP BY TABLESPACE_NAME)F，(SELECT DD.TABLESPACE_NAME，ROUND(SUM(DD.BYTES)/(1024 * 1024), 2)TOT_GROOTTE_MB

FROM SYS.DBA_DATA_FILES DD

GROUP BY DD.TABLESPACE_NAME)D

WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME

ORDER BY F.TABLESPACE_NAME;查询结果显示：

/

非系统表空间使用和(M)

select sum(“已使用空间(M)”)“已使用空间(M)和” from(SELECT UPPER(F.TABLESPACE_NAME)“表空间名”，D.TOT_GROOTTE_MB “表空间大小(M)”，D.TOT_GROOTTE_MBF.TOTAL_BYTES)/ D.TOT_GROOTTE_MB * 100,2),'990.99')||'%' “使用比(%)”，F.TOTAL_BYTES “空闲空间(M)”，F.MAX_BYTES “最大块(M)”

FROM(SELECT TABLESPACE_NAME，ROUND(SUM(BYTES)/(1024 * 1024), 2)TOTAL_BYTES，ROUND(MAX(BYTES)/(1024 * 1024), 2)MAX_BYTES

FROM SYS.DBA_FREE_SPACE

GROUP BY TABLESPACE_NAME)F，(SELECT DD.TABLESPACE_NAME，ROUND(SUM(DD.BYTES)/(1024 * 1024), 2)TOT_GROOTTE_MB

FROM SYS.DBA_DATA_FILES DD

GROUP BY DD.TABLESPACE_NAME)D

WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME and F.TABLESPACE_NAME NOT IN('SYSAUX','USERS','UNDOTBS1','SYSTEM'))

2、查询当前用户默认表空间的使用情况

select tablespacename,sum(totalContent),sum(usecontent),sum(sparecontent),avg(sparepercent)from(SELECT b.file_id as id,b.tablespace_name as tablespacename,b.bytes as totalContent,(b.bytes-sum(nvl(a.bytes,0)))as usecontent,sum(nvl(a.bytes,0))as sparecontent,sum(nvl(a.bytes,0))/(b.bytes)*100 as sparepercent FROM dba_free_space a,dba_data_files b WHERE a.file_id=b.file_id and b.tablespace_name =(select default_tablespace from dba_users where username = user)

group by b.tablespace_name,b.file_name,b.file_id,b.bytes)

GROUP BY tablespacename;

3、查询所有用户表使用大小的前三十名

select * from(select segment_name,bytes from dba_segments where owner = USER order by bytes desc)where rownum <= 30

4、查询单张表的使用情况 select segment_name,bytes from dba_segments where segment_name = 'RE_STDEVT_FACT_DAY' and owner = USER

RE_STDEVT_FACT_DAY是您要查询的表名称

5、计算每个用户占用的磁盘空间 select owner,sum(bytes)/1024/1024/1024 “Space(G)” from dba_segments group by owner order by 2;

6、计算某个用户占用的磁盘空间

select owner,sum(bytes)/1024/1024/1024 “Space(G)” from dba_segments where owner='LIAOJL' group by owner;

7、查看表空间的名称及大小（分配大小）:

select t.tablespace_name, round(sum(bytes/(1024*1024)),0)ts_size

from dba_tablespaces t, dba_data_files d where t.tablespace_name = d.tablespace_name group by t.tablespace_name;

8、查看表空间物理文件的名称及大小（分配大小）:

select tablespace_name, file_id, file_name,round(bytes/(1024*1024),0)total_space from dba_data_files order by tablespace_name;

9、查看回滚段名称及大小: select segment_name, tablespace_name, r.status,(initial_extent/1024)InitialExtent,(next_extent/1024)NextExtent, max_extents, v.curext CurExtent From dba_rollback_segs r, v$rollstat v Where r.segment_id = v.usn(+)order by segment_name;

10、如何查看某个回滚段里面，跑的什么事物或者正在执行什么sql语句: select d.sql_text,a.name from v$rollname a,v$transaction b,v$session c,v$sqltext d where a.usn=b.xidusn and b.addr=c.taddr and c.sql_address=d.address and c.sql_hash_value=d.hash_value and a.usn==2;(备注：你要看哪个，就把usn=?写成几就行了)

查看控制文件:

SQL>select * from v$controlfile;

查看日志文件:

SQL> col member format a50 SQL>select * from v$logfile;

11、如何查看当前SQL*PLUS用户的sid和serial#:

SQL>select sid, serial#, status from v$session where audsid=userenv('sessionid');

12、怎样识别IO竞争和负载平衡:

SQL>col 文件名 format a35 SQL>select df.name 文件名, fs.phyrds 读次数, fs.phywrts 写次数,(fs.readtim/decode(fs.phyrds,0,-1,fs.phyrds))读时间,(fs.writetim/decode(fs.phywrts,0,-1,fs.phywrts))写时间 from v$datafile df, v$filestat fs where df.file#=fs.file# order by df.name

13、查看有哪些用户连接

select s.osuser os_user_name, decode(sign(48command), 1, to_char(command), 'Action Code #' || to_char(command))action, status session_status，s.osuser os_user_name,s.sid,p.spid ,s.serial# serial_num,nvl(s.username, '[Oracle process]')user_name,s.terminal terminal,s.program program,st.value criteria_value from v$sesstat st，v$session s , v$process p

where st.sid = s.sid and

st.statistic# = to_number('38')and

('ALL' = 'ALL' or s.status = 'ALL')and p.addr = s.paddr order by st.value desc, p.spid asc, s.username asc, s.osuser asc

21、监控当前数据库谁在运行什么SQL语句: SELECT osuser, username, sql_text from v$session a, v$sqltext b where a.sql_address =b.address order by address, piece;

Column type format a8

22、查询每个用户执行过的sql语句 select B.USER_NAME,C.OSUSER,C.MACHINE,C.PROGRAM,C.MODULE, A.USER_IO_WAIT_TIME,B.HASH_VALUE,C.SERVICE_NAME, A.SQL_TEXT from v$sqlarea a, v$open_cursor b,v$session c where A.ADDRESS=B.ADDRESS and C.USERNAME=B.USER_NAME order by C.OSUSER,B.USER_NAME

第三篇：SQL语句常用函数

SQL语句常用函数、字符转换函数

1、ASCII()

返回字符表达式最左端字符的ASCII 码值。在ASCII（）函数中，纯数字的字符串可不用‘’括起来，但含其它字符的字符串必须用‘’括起来使用，否则会出错。

2、CHAR()

将ASCII 码转换为字符。如果没有输入0 ~ 255 之间的ASCII 码值，CHAR（）返回NULL。

3、LOWER()和UPPER()

LOWER()将字符串全部转为小写；UPPER()将字符串全部转为大写。

4、STR()

把数值型数据转换为字符型数据。

STR([，length[，]])

length 指定返回的字符串的长度，decimal 指定返回的小数位数。如果没有指定长度，缺省的length 值为10，decimal 缺省值为0。

当length 或者decimal 为负值时，返回NULL；

当length 小于小数点左边（包括符号位）的位数时，返回length 个*； 先服从length，再取decimal ；

当返回的字符串位数小于length，左边补足空格。

二、去空格函数

1、LTRIM()把字符串头部的空格去掉。

2、RTRIM()把字符串尾部的空格去掉。

三、取子串函数

1、left()

LEFT(，)

返回character_expression 左起 integer_expression 个字符。

2、RIGHT()

RIGHT(，)

返回character_expression 右起 integer_expression 个字符。

3、SUBSTRING()

SUBSTRING(，，length)

返回从字符串左边第starting_ position 个字符起length个字符的部分。

四、字符串比较函数

1、CHARINDEX()

返回字符串中某个指定的子串出现的开始位置。

CHARINDEX(<’substring_expression’>，)

其中substring _expression 是所要查找的字符表达式，expression 可为字符串也可为列名表达式。如果没有发现子串，则返回0 值。

此函数不能用于TEXT 和IMAGE 数据类型。

2、PATINDEX()

返回字符串中某个指定的子串出现的开始位置。

PATINDEX(<’%substring _expression%’>，)其中子串表达式前后必须有百分号“%”否则返回值为0。

与CHARINDEX 函数不同的是，PATINDEX函数的子串中可以使用通配符，且此函数可用于CHAR、VARCHAR 和TEXT 数据类型。

五、字符串操作函数

1、QUOTENAME()

返回被特定字符括起来的字符串。

QUOTENAME(<’character_expression’>[，quote_ character])其中quote_ character 标明括字符串所用的字符，缺省值为“[]”。

2、REPLICATE()

返回一个重复character_expression 指定次数的字符串。

REPLICATE(character_expression integer_expression)如果

integer_expression 值为负值，则返回NULL。

3、REVERSE()

将指定的字符串的字符排列顺序颠倒。

REVERSE()其中character_expression 可以是字符串、常数或一个列的值。

4、REPLACE()

返回被替换了指定子串的字符串。

REPLACE(，，)用string_expression3 替换在string_expression1 中的子串string_expression2。

5、SPACE()

返回一个有指定长度的空白字符串。

SPACE()如果integer_expression 值为负值，则返回NULL。

6、STUFF()

用另一子串替换字符串指定位置、长度的子串。

STUFF(，，，)

如果起始位置为负或长度值为负，或者起始位置大于

character_expression1 的长度，则返回NULL 值。

如果length 长度大于character_expression1 中 start_ position 以右的长度，则character_expression1 只保留首字符。

六、数据类型转换函数

1、CAST()

CAST( AS [ length ])

2、CONVERT()

CONVERT([ length ]， [，style])

1）data_type为SQL Server系统定义的数据类型，用户自定义的数据类型不能在此使用。

2）length用于指定数据的长度，缺省值为30。

3）把CHAR或VARCHAR类型转换为诸如INT或SAMLLINT这样的INTEGER类型、结果必须是带正号或负号的数值。

4）TEXT类型到CHAR或VARCHAR类型转换最多为8000个字符，即CHAR或VARCHAR数据类型是最大长度。

5）IMAGE类型存储的数据转换到BINARY或VARBINARY类型，最多为8000个字符。

6）把整数值转换为MONEY或SMALLMONEY类型，按定义的国家的货币单位来处理，如人民币、美元、英镑等。

7）BIT类型的转换把非零值转换为1，并仍以BIT类型存储。

8）试图转换到不同长度的数据类型，会截短转换值并在转换值后显示“+”，以标识发生了这种截断。

9）用CONVERT（）函数的style 选项能以不同的格式显示日期和时间。style 是将DATATIME 和SMALLDATETIME 数据转换为字符串时所选用的由SQL Server 系统提供的转换样式编号，不同的样式编号有不同的输出格式。

七、日期函数

1、day(date_expression)

返回date_expression中的日期值

2、month(date_expression)

返回date_expression中的月份值

3、year(date_expression)

返回date_expression中的年份值

4、DATEADD()

DATEADD(，，)

返回指定日期date 加上指定的额外日期间隔number 产生的新日期。

5、DATEDIFF()

DATEDIFF(，，)

返回两个指定日期在datepart 方面的不同之处，即date2 超过date1的差距值，其结果值是一个带有正负号的整数值。

6、DATENAME()

DATENAME(，)

以字符串的形式返回日期的指定部分此部分。由datepart 来指定。

7、DATEPART()

DATEPART(，)

以整数值的形式返回日期的指定部分。此部分由datepart 来指定。DATEPART(dd，date)等同于DAY(date)

DATEPART(mm，date)等同于MONTH(date)

DATEPART(yy，date)等同于YEAR(date)

8、GETDATE()

以DATETIME 的缺省格式返回系统当前的日期和时间。

八、统计函数

AVG（）-返回的平均价值

count（）-返回的行数

first（）-返回第一个值

last（）-返回最后一个值

max（）-返回的最大价值

min（）-返回最小的价值

total（）-返回的总和

九、数学函数

abs(numeric_expr)求绝对值

ceiling(numeric_expr)取大于等于指定值的最小整数 exp(float_expr)取指数

floor(numeric_expr)小于等于指定值得最大整数 pi()3.1415926.........power(numeric_expr,power)返回power次方

rand([int_expr])随机数产生器

round(numeric_expr,int_expr)安int_expr规定的精度四舍五入 sign(int_expr)根据正数,0,负数，返回+1,0,-1 sqrt(float_expr)平方根

十、系统函数

suser_name()用户登录名

user_name()用户在数据库中的名字 user用户在数据库中的名字 show_role()对当前用户起作用的规则

db_name()数据库名object_name(obj_id)数据库对象名

col_name(obj_id,col_id)列名

col_length(objname,colname)列长度

valid_name(char_expr)是否是有效标识符

第四篇：Oracle SQL精妙SQL语句讲解

SQL*PLUS界面：

登录：输入SQLPLUS回车；输入正确的ORACLE用户名并回车；输入用户口令并回车，显示提示符：SQL>

退出：输入EXIT即可。

2）命令的编辑与运行：

在命令提示符后输入SQL命令并运行，以分号结束输入；以斜杠结束输入；以空行结束输入；

利用SQL缓冲区进行PL/SQL块的编辑和运行；

利用命令文件进行PL/SQL块的编辑和运行。

数据库查询

用SELECT语句从表中提取查询数据。语法为

SELECT [DISTINCT] {column1,column2,…} FROM tablename WHERE {conditions} GROUP BY {conditions} ORDER BY {expressions} [ASC/DESC];

说明：SELECT子句用于指定检索数据库的中哪些列，FROM子句用于指定从哪一个表或视图中检索数据。

SELECT中的操作符及多表查询WHERE子句。(LIKE,IS,…)

WHERE子句中的条件可以是一个包含等号或不等号的条件表达式，也可以是一个含有IN、NOT IN、BETWEEN、LIKE、IS NOT NULL等比较运算符的条件式，还可以是由单一的条件表达通过逻辑运算符组合成复合条件。

ORDER BY 子句

ORDER BY 子句使得SQL在显示查询结果时将各返回行按顺序排列，返回行的排列顺序由ORDER BY 子句指定的表达式的值确定。

连接查询

利用SELECT语句进行数据库查询时，可以把多个表、视图的数据结合起来，使得查询结果的每一行中包含来自多个表达式或视图的数据，这种操作被称为连接查询。

连接查询的方法是在SELECT命令的FROM子句中指定两个或多个将被连接查询的表或视图，并且在WHERE子句告诉ORACLE如何把多个表的数据进行合并。根据WHERE子句中的条件表达式是等还是不等式，可以把连接查询分为等式连接和不等式连接。

子查询

如果某一个SELECT命令（查询1）出现在另一个SQL命令（查询2）的一个子句中，则称查询1是查询2的子查询。

基本数据类型（NUMBER,VARCHAR2,DATE）O

RACEL支持下列内部数据类型：

VARCHAR2 变长字符串，最长为2000字符。

NUMBER 数值型。

LONG 变长字符数据，最长为2G字节。

DATE 日期型。

RAW 二进制数据，最长为255字节。

LONG RAW 变长二进制数据，最长为2G字节。

ROWID 二六进制串，表示表的行的唯一地址。

CHAR 定长字符数据，最长为255。

常用函数用法：

一个函数类似于一个算符，它操作数据项，返回一个结果。函数在格式上不同于算符，它个具有变元，可操作0个、一个、二个或多个变元，形式为：

函数名（变元，变元，…）

函数具有下列一般类形：

单行函数

分组函数

单行函数对查询的表或视图的每一行返回一个结果行。它有数值函数，字符函数，日期函数，转换函数等。

分组函数返回的结果是基于行组而不是单行，所以分组函数不同于单行函数。在许多分组函数中可有下列选项：

DISTRNCT 该选项使分组函数只考虑变元表达式中的不同值。

ALL该选项使分组函数考虑全部值，包含全部重复。

全部分组函数（除COUNT（*）外）忽略空值。如果具有分组函数的查询，没有返回行或只有空值（分组函数的变元取值的行），则分组函数返回空值。

5、数据操纵语言命令：

数据库操纵语言（DML）命令用于查询和操纵模式对象中的数据，它不隐式地提交当前事务。它包含UPDATE、INSERT、DELETE、EXPLAIN PLAN、SELECT和LOCK TABLE 等命令。下面简单介绍一下：

1)UPDATE tablename SET {column1=expression1,column2=expression2,…} WHERE {conditions};

例如：S QL>UPDATE EMP

SET JOB =’MANAGER’

WHERE ENAME=’MAPTIN’;

SQL >SELECT * FROM EMP;

UPDATE子句指明了要修改的数据库是EMP,并用WHERE子句限制了只对名字(ENAME)为’MARTIN’的职工的数据进行修改,SET子句则说明修改的方式,即把’MARTION’的工作名称(JOB)改为’MARAGER’.2)INSERT INTO tablename {column1,column2,…} VALUES {expression1,expression2,…};

例如：SQL>SELECT INTO DEPT（DNAME，DEPTNO）

VALUES（‘ACCOUNTING’，10）

3)DELETE FROM tablename WHERE {conditions};

例如：SQL>DELETE FROM EMP

WHERE EMPNO = 7654;

DELETE命令删除一条记录,而且DELETE命令只能删除整行,而不能删除某行中的部分数据.4）事务控制命令

提交命令(COMMIT):可以使数据库的修改永久化.设置AUTOCOMMIT为允许状态:SQL >SET AUTOCOMMIT ON;

回滚命令(ROLLBACK):消除上一个COMMIT命令后的所做的全部修改,使得数据库的内容恢复到上一个COMMIT执行后的状态.使用方法是:

SQL>ROLLBACK;

创建表、视图、索引、同义词、用户。、表是存储用户数据的基本结构。

建立表主要指定义下列信息：

列定义

完整性约束

表所在表空间

存储特性

可选择的聚集

从一查询获得数据

语法如下：CREATE TABLE tablename

(column1 datatype [DEFAULT expression] [constraint], column1 datatype [DEFAULT expression] [constraint], ……）

[STORAGE子句] [其他子句…]；

例如：

SQL>CREATE TABLE NEW_DEPT(DPTNO NUMBER(2), DNAME CHAR(6), LOC CHAR(13);

更改表作用：

增加列

增加完整性约束

重新定义列（数据类型、长度、缺省值）

修改存储参数或其它参数

使能、使不能或删除一完整性约束或触发器

显式地分配一个范围

2）、视图

视图是一个逻辑表，它允许操作者从其它表或视图存取数据，视图本身不包含数据。视图所基于的表称为基表。

引入视图有下列作用：

提供附加的表安全级，限制存取基表的行或/和列集合。

隐藏数据复杂性。

为数据提供另一种观点。

促使ORACLE的某些操作在包含视图的数据库上执行，而不在另一个数据库上执行。

3）、索引

索引是种数据库对象。对于在表或聚集的索引列上的每一值将包含一项，为行提供直接的快速存取。在下列情况ORACLE可利用索引改进性能：

按指定的索引列的值查找行。

按索引列的顺序存取表。

建立索引： CREATE [UNIQUE] INDEX indexname ON tablename(column ,。。)；

例如：SQL>CREAT INDEX IC_EMP

ON CLUSTER EMPLOYEE

4）、同义词

同义词：为表、视图、序列、存储函数、包、快照或其它同义词的另一个名字。使用同义词为了安全和方便。对一对象建立同义词可有下列好处：

引用对象不需指出对象的持有者。

引用对象不需指出它所位于的数据库。

为对象提供另一个名字。

建立同义词：

CREATE SYNONYM symnon_name FOR [username.]tablename;

例如：CREAT PUBLIC SYNONYM EMP

FOR SCOTT.EMP @SALES

5）、用户

CREATE USER username IDENTIFIED BY password;

例如：SQL>CREATE USER SIDNEY

IDENTIFIED BY CARTON;

Oracle扩展PL/SQL简介

PL/SQL概述。

PL/SQL是Oracle对SQL规范的扩展，是一种块结构语言，即构成一个PL/SQL程序的基本单位（过程、函数和无名块）是逻辑块，可包含任何数目的嵌套了快。这种程序结构支持逐步求精方法解决问题。一个块（或子块）将逻辑上相关的说明和语句组合在一起，其形式为：

DECLARE

---说明

BEGIN

---语句序列

EXCEPTION

---例外处理程序

END；

它有以下优点:

支持SQL;

生产率高;

性能好;

可称植性;

与ORACLE集成.PL/SQL体系结构

PL/SQL运行系统是种技术，不是一种独立产品，可认为这种技术是PL/SQL块和子程序的一种机，它可接收任何有效的PL/SQL块或子程序。如图所示：

PL/SQL机可执行过程性语句，而将SQL语句发送到ORACLE服务器上的SQL语句执行器。在ORACLE预编译程序或OCI程序中可嵌入无名的PL/SQL块。如果ORACLE具有PROCEDURAL选件，有名的PL/SQL块（子程序）可单独编译，永久地存储在数据库中，准备执行。

PL/SQL基础：

PL/SQL有一字符集、保留字、标点、数据类型、严密语法等，它与SQL有相同表示，现重点介绍。

1）、数据类型：如下表所示

数据类型 子类型

纯量类型 数值 BINARY_INTEGER NATURAL,POSITIVE

NUMBER DEC,DECIMAL,DOUBLE PRECISION,PLOAT,INTEGER,INT,NUMERIC,REAL,SMALLINT

字符 CHAR CHARACTER,STRING

VARCHAR2 VARCHAR

LONG

LONG RAW

RAW

RAWID

逻辑 BOOLEAN

日期 DATE

组合 类型 记录 RECORD

表 TABLE

2）、变量和常量

在PL/SQL程序中可将值存储在变量和常量中，当程序执行时，变量的值可以改变，而常量的值不能改变。

3）、程序块式结构：

DECLARE

变量说明部分；

BEGIN

执行语句部分；

[EXCEPTION

例外处理部分；] END;控制语句：

分支语句：

IF condition THEN

Sequence_of_statements;

END IF;

IF condition THEN

Sequence_of_statement1;

ELSE

Sequence_of_statement2;

END IF;

IF condition1 THEN

Sequence_of_statement1;

ELSIF condition2 THEN

Sequence_of_statement2;

ELSIF condition3 THEN

Sequence_of_statement3;

END IF;

循环语句：

LOOP

Sequence_of_statements;

IF condition THEN

EXIT;

END IF;

END LOOP;

WHILE condition LOOP

Sequence_of_statements;

END LOOP;

FOR counter IN lower_bound..higher_bound LOOP

Sequence_of_statements;

END LOOP;

子程序：

存储过程：

CREATE PROCEDURE 过程名(参数说明1，参数说明2，[局部说明]

BEGIN

执行语句；

END 过程名；

。)IS。

存储函数：

CREATE FUNCTION 函数名(参数说明1，参数说明2。。)RETURN 类型 IS [局部说明] BEGIN

执行语句；

END 函数名

Oracle SQL精妙SQL语句讲解

好东西，大家赶紧收藏吧~~~

--行列转换 行转列

DROP TABLE t_change_lc;CREATE TABLE t_change_lc(card_code VARCHAR2(3), q NUMBER, bal NUMBER);

INSERT INTO t_change_lc

SELECT '001' card_code, ROWNUM q, trunc(dbms_random.VALUE * 100)bal FROM dual CONNECT BY ROWNUM <= 4 UNION

SELECT '002' card_code, ROWNUM q, trunc(dbms_random.VALUE * 100)bal FROM dual CONNECT BY ROWNUM <= 4;

SELECT * FROM t_change_lc;

SELECT a.card_code，SUM(decode(a.q, 1, a.bal, 0))q1，SUM(decode(a.q, 2, a.bal, 0))q2，SUM(decode(a.q, 3, a.bal, 0))q3，SUM(decode(a.q, 4, a.bal, 0))q4

FROM t_change_lc a GROUP BY a.card_code ORDER BY 1;

--行列转换 列转行

DROP TABLE t_change_cl;CREATE TABLE t_change_cl AS SELECT a.card_code，SUM(decode(a.q, 1, a.bal, 0))q1，SUM(decode(a.q, 2, a.bal, 0))q2，SUM(decode(a.q, 3, a.bal, 0))q3，SUM(decode(a.q, 4, a.bal, 0))q4

FROM t_change_lc a GROUP BY a.card_code ORDER BY 1;

SELECT * FROM t_change_cl;

SELECT t.card_code，t.rn q，decode(t.rn, 1, t.q1, 2, t.q2, 3, t.q3, 4, t.q4)bal

FROM(SELECT a.*, b.rn

FROM t_change_cl a，(SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM <= 4)b)t ORDER BY 1, 2;

--行列转换 行转列 合并

DROP TABLE t_change_lc_comma;CREATE TABLE t_change_lc_comma AS SELECT card_code,'quarter_'||q AS q FROM t_change_lc;

SELECT * FROM t_change_lc_comma;

SELECT t1.card_code, substr(MAX(sys_connect_by_path(t1.q, ';')), 2)q

FROM(SELECT a.card_code，a.q，row_number()over(PARTITION BY a.card_code ORDER BY a.q)rn

FROM t_change_lc_comma a)t1 START WITH t1.rn = 1 CONNECT BY t1.card_code = PRIOR t1.card_code

AND t1.rn1 = PRIOR t1.rn GROUP BY t1.card_code;

SELECT * FROM t_change_cl_comma;SELECT t.card_code，substr(t.q，instr(';' || t.q, ';', 1, rn)，instr(t.q || ';', ';', 1, rn)-instr(';' || t.q, ';', 1, rn))q

FROM(SELECT a.card_code, a.q, b.rn

FROM t_change_cl_comma a，(SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM <= 100)b

WHERE instr(';' || a.q, ';', 1, rn)> 0)t ORDER BY 1, 2;

--实现一条记录根据条件多表插入 DROP TABLE t_ia_src;CREATE TABLE t_ia_src AS SELECT 'a'||ROWNUM c1, 'b'||ROWNUM c2 FROM dual CONNECT BY ROWNUM<=5;DROP TABLE t_ia_dest_1;CREATE TABLE t_ia_dest_1(flag VARCHAR2(10), c VARCHAR2(10));DROP TABLE t_ia_dest_2;CREATE TABLE t_ia_dest_2(flag VARCHAR2(10), c VARCHAR2(10));DROP TABLE t_ia_dest_3;CREATE TABLE t_ia_dest_3(flag VARCHAR2(10), c VARCHAR2(10));

SELECT * FROM t_ia_src;SELECT * FROM t_ia_dest_1;SELECT * FROM t_ia_dest_2;SELECT * FROM t_ia_dest_3;

INSERT ALL WHEN(c1 IN('a1','a3'))THEN

INTO t_ia_dest_1(flag,c)VALUES(flag1,c2)WHEN(c1 IN('a2','a4'))THEN

INTO t_ia_dest_2(flag,c)VALUES(flag2,c2)ELSE INTO t_ia_dest_3(flag,c)VALUES(flag1||flag2,c1||c2)SELECT c1,c2, 'f1' flag1, 'f2' flag2 FROM t_ia_src;

--如果存在就更新，不存在就插入用一个语句实现 DROP TABLE t_mg;CREATE TABLE t_mg(code VARCHAR2(10), NAME VARCHAR2(10));

SELECT * FROM t_mg;

MERGE INTO t_mg a USING(SELECT 'the code' code, 'the name' NAME FROM dual)b ON(a.code = b.code)WHEN MATCHED THEN

UPDATE SET a.NAME = b.NAME WHEN NOT MATCHED THEN

INSERT(code, NAME)VALUES(b.code, b.NAME);

--抽取/删除重复记录

DROP TABLE t_dup;CREATE TABLE t_dup AS SELECT 'code_'||ROWNUM code, dbms_random.string('z',5)NAME FROM dual CONNECT BY ROWNUM<=10;

INSERT INTO t_dup SELECT 'code_'||ROWNUM code, dbms_random.string('z',5)NAME FROM dual CONNECT BY ROWNUM<=2;

SELECT * FROM t_dup;

SELECT * FROM t_dup a WHERE a.ROWID <>(SELECT MIN(b.ROWID)FROM t_dup b WHERE a.code=b.code);

SELECT b.code, b.NAME

FROM(SELECT a.code，a.NAME，row_number()over(PARTITION BY a.code ORDER BY a.ROWID)rn

FROM t_dup a)b WHERE b.rn > 1;

--IN/EXISTS的不同适用环境--t_orders.customer_id有索引 SELECT a.*

FROM t_employees a WHERE a.employee_id IN

(SELECT b.sales_rep_id FROM t_orders b WHERE b.customer_id = 12);

SELECT a.*

FROM t_employees a WHERE EXISTS(SELECT 1

FROM t_orders b

WHERE b.customer_id = 12

AND a.employee_id = b.sales_rep_id);

--t_employees.department_id有索引 SELECT a.*

FROM t_employees a WHERE a.department_id = 10

AND EXISTS(SELECT 1 FROM t_orders b WHERE a.employee_id = b.sales_rep_id);

SELECT a.*

FROM t_employees a WHERE a.department_id = 10

AND a.employee_id IN(SELECT b.sales_rep_id FROM t_orders b);

--FBI DROP TABLE t_fbi;CREATE TABLE t_fbi AS SELECT ROWNUM rn, dbms_random.STRING('z',10)NAME , SYSDATE + dbms_random.VALUE * 10 dt FROM dual

CONNECT BY ROWNUM <=10;

CREATE INDEX idx_nonfbi ON t_fbi(dt);

DROP INDEX idx_fbi_1;CREATE INDEX idx_fbi_1 ON t_fbi(trunc(dt));

SELECT * FROM t_fbi WHERE trunc(dt)= to_date('2006-09-21','yyyy-mm-dd');

--不建议使用

SELECT * FROM t_fbi WHERE to_char(dt, 'yyyy-mm-dd')= '2006-09-21';

--LOOP中的COMMIT/ROLLBACK DROP TABLE t_loop PURGE;create TABLE t_loop AS SELECT * FROM user_objects WHERE 1=2;

SELECT * FROM t_loop;

--逐行提交 DECLARE BEGIN

FOR cur IN(SELECT * FROM user_objects)LOOP

INSERT INTO t_loop VALUES cur;

COMMIT;

END LOOP;END;

--模拟批量提交 DECLARE

v_count NUMBER;BEGIN

FOR cur IN(SELECT * FROM user_objects)LOOP

INSERT INTO t_loop VALUES cur;

v_count := v_count + 1;

IF v_count >= 100 THEN

COMMIT;

END IF;

END LOOP;

COMMIT;END;

--真正的批量提交 DECLARE

CURSOR cur IS

SELECT * FROM user_objects;

TYPE rec IS TABLE OF user_objects%ROWTYPE;

recs rec;BEGIN

OPEN cur;

WHILE(TRUE)LOOP

FETCH cur BULK COLLECT

INTO recs LIMIT 100;

--forall 实现批量

FORALL i IN 1..recs.COUNT

INSERT INTO t_loop VALUES recs(i);

COMMIT;

EXIT WHEN cur%NOTFOUND;

END LOOP;

CLOSE cur;END;

--悲观锁定/乐观锁定

DROP TABLE t_lock PURGE;CREATE TABLE t_lock AS SELECT 1 ID FROM dual;

SELECT * FROM t_lock;

--常见的实现逻辑，隐含bug DECLARE

v_cnt NUMBER;BEGIN

--这里有并发性的bug

SELECT MAX(ID)INTO v_cnt FROM t_lock;

--here for other operation

v_cnt := v_cnt + 1;

INSERT INTO t_lock(ID)VALUES(v_cnt);

COMMIT;END;

--高并发环境下，安全的实现逻辑 DECLARE

v_cnt NUMBER;BEGIN

--对指定的行取得lock

SELECT ID INTO v_cnt FROM t_lock WHERE ID=1 FOR UPDATE;

--在有lock的情况下继续下面的操作

SELECT MAX(ID)INTO v_cnt FROM t_lock;

--here for other operation

v_cnt := v_cnt + 1;

INSERT INTO t_lock(ID)VALUES(v_cnt);

COMMIT;--提交并且释放lock END;

--硬解析/软解析

DROP TABLE t_hard PURGE;CREATE TABLE t_hard(ID INT);

SELECT * FROM t_hard;

DECLARE

sql_1

VARCHAR2(200);BEGIN

--hard parse

--java中的同等语句是 Statement.execute()

FOR i IN 1..1000 LOOP

sql_1 := 'insert into t_hard(id)values(' || i || ')';

EXECUTE IMMEDIATE sql_1;

END LOOP;

COMMIT;

--soft parse

--java中的同等语句是 PreparedStatement.execute()

sql_1

:= 'insert into t_hard(id)values(:id)';

FOR i IN 1..1000 LOOP

EXECUTE IMMEDIATE sql_1

USING i;

END LOOP;

COMMIT;END;

--正确的分页算法

SELECT *

FROM(SELECT a.*, ROWNUM rn

FROM(SELECT * FROM t_employees ORDER BY first_name)a

WHERE ROWNUM <= 500)WHERE rn > 480;

--分页算法(why not this one)SELECT a.*, ROWNUM rn

FROM(SELECT * FROM t_employees ORDER BY first_name)a WHERE ROWNUM <= 500 AND ROWNUM > 480;

--分页算法(why not this one)SELECT b.*

FROM(SELECT a.*, ROWNUM rn

FROM t_employees a

WHERE ROWNUM < = 500

ORDER BY first_name)b WHERE b.rn > 480;

--OLAP--小计合计 SELECT CASE

WHEN a.deptno IS NULL THEN

'合计'

WHEN a.deptno IS NOT NULL AND a.empno IS NULL THEN

'小计'

ELSE

'' || a.deptno

END deptno，a.empno，a.ename，SUM(a.sal)total_sal

FROM scott.emp a GROUP BY GROUPING SETS((a.deptno),(a.deptno, a.empno, a.ename),());

--分组排序 SELECT a.deptno，a.empno，a.ename，a.sal，--可跳跃的rank

rank()over(PARTITION BY a.deptno ORDER BY a.sal DESC)r1，--密集型rank

dense_rank()over(PARTITION BY a.deptno ORDER BY a.sal DESC)r2，--不分组排序

rank()over(ORDER BY sal DESC)r3

FROM scott.emp a

ORDER BY a.deptno,a.sal DESC;

--当前行数据和前/后n行的数据比较 SELECT a.empno，a.ename，a.sal，--上面一行

lag(a.sal)over(ORDER BY a.sal DESC)lag_1，--下面三行

lead(a.sal, 3)over(ORDER BY a.sal DESC)lead_3

FROM scott.emp a ORDER BY a.sal DESC;

一、数据表设计图

二、创建语句

/*================*/ /* DBMS name: ORACLE Version 9i */ /* Created on: 2008-11-10 23:39:24 */ /*================*/

alter table “emp”

drop constraint FK_EMP_REFERENCE_DEPT;

drop table “dept” cascade constraints;

drop table “emp” cascade constraints;

drop table “salgrade” cascade constraints;

/*================*/ /* Table: “dept” */ /*================*/

create table dept(deptno NUMBER(11)not null, dname VARCHAR2(15)not null, loc VARCHAR2(15)not null, constraint PK_DEPT primary key(deptno));

/*================*/ /* Table: “emp” */ /*================*/

create table emp(empno NUMBER(11)not null, deptno NUMBER(11), ename VARCHAR2(15)not null, sal NUMBER(11)not null, job VARCHAR2(15)not null, mgr NUMBER(11)not null, hirdate DATE not null, comm NUMBER(11)not null, constraint PK_EMP primary key(empno));

/*================*/ /* Table: salgrade */ /*================*/

create table salgrade(grade NUMBER(11)not null, losal NUMBER(11)not null, hisal NUMBER(11)not null, constraint PK_SALGRADE primary key(grade));

alter table emp add constraint FK_EMP_REFERENCE_DEPT foreign key(deptno)references dept(deptno);

三、测试要求及语句

/** *公司工资最高的员工列表 子查询 */

select t.ename,t.sal from emp t where t.sal =(select max(sal)from emp)

/** *查询每一个员工的经理人及自己的名字 */

select e1.ename,e2.ename from emp e1 join emp e2 on(e1.mgr = e2.empno)

/** *查询公司平均薪水的等级 */

select s.grade from salgrade s where(select avg(t.sal)from emp t)between s.losal and s.hisal

/** *求部门中那些人的工资最高 */

select d.dname,ename,sal from(select t.deptno,ename,sal from(select deptno,max(sal)as max_sal from emp group by deptno)e join emp t on(e.deptno = t.deptno and t.sal = max_sal))et join dept d on(d.deptno = et.deptno)

/** *查询部门平均薪水的等级 */

select d.dname,avg_sal,grade from(select deptno,avg_sal,grade from(select deptno,avg(sal)as avg_sal from emp group by deptno)e join salgrade s on(e.avg_sal between s.losal and s.hisal))es join dept d on(es.deptno = d.deptno)

/** *求部门的平均薪水等级 */

select deptno,avg(grade)from(select deptno,grade from emp e join salgrade s on(e.sal between s.losal and s.hisal))t group by t.deptno

/** * 求那些人是经理人 */

select ename from emp e where empno in(select distinct mgr from emp)

/** *不准用组函数 求薪水的最高值 */

select ename from emp where empno not in(select distinct e1.empno from emp e1 join emp e2 on(e1.sal

/** *平均薪水最高的部门编号与名称 */

select d.deptno,dname from(select deptno,avg(sal)avg_sal from emp group by deptno)t1 join dept d on(d.deptno = t1.deptno)where avg_sal =(select max(avg_sal)from(select deptno,avg(sal)avg_sal from emp group by deptno)t2)

/** *求平均薪水的等级最低的部门名称 */

select dname from dept d where d.deptno in(select deptno from(select deptno,grade from(select deptno,avg(sal)avg_sal from emp group by deptno)t1 join salgrade g on(avg_sal between g.losal and g.hisal))t2 where t2.grade =(select min(grade)from(select deptno,grade from(select deptno,avg(sal)avg_sal from emp group by deptno)t1 join salgrade g on(avg_sal between g.losal and g.hisal))t3))

/** *求部门经理人中平均薪水最低的部门名称 */

select d.dname,t1.avg_sal from dept d join(select deptno,avg(sal)avg_sal from(select e2.deptno,e2.ename,e2.sal from emp e1 join emp e2 on(e1.mgr = e2.empno))t group by deptno)t1 on(d.deptno = t1.deptno)where avg_sal =(select min(avg_sal)from(select deptno,avg(sal)avg_sal from(select e2.deptno,e2.ename,e2.sal from emp e1 join emp e2 on(e1.mgr = e2.empno))t group by deptno))/** *求必普通员工的最高薪水还要高的经理人名称 */

select ename from(select e2.ename,e2.empno,e2.sal from emp e1 join emp e2 on(e1.mgr = e2.empno))t where t.sal >(select max(e.sal)from emp e where e.empno not in(select e1.mgr from emp e1 join emp e2 on(e1.mgr = e2.empno)))

/** *求薪水最高的第6名到10名雇员 */

SELECT * FROM(SELECT A.*, ROWNUM RN FROM(SELECT * FROM(select e1.ename,e1.sal from emp e1 order by e1.sal desc))A WHERE ROWNUM <= 10)WHERE RN >= 6

第五篇：oracle复制表的sql语句

oracle复制表的sql语句

如下，表a是数据库中已经存在的表，b是准备根据表a进行复制创建的表：

1、只复制表结构的sql

create table b as select * from a where 1<>12、即复制表结构又复制表中数据的sql

create table b as select * from a3、复制表的制定字段的sql

create table b as select row_id,name,age from a where 1<>1//前提是row_id,name,age都是a表的列

4、复制表的指定字段及这些指定字段的数据的sql

create table b as select row_id,name,age from a

以上语句虽然能够很容易的根据a表结构复制创建b表，但是a表的索引等却复制不了，需要在b中手动建立。

5、insert into 会将查询结果保存到已经存在的表中

insert into t2(column1, column2,....)select column1, column2,....from t1rm_data001.dbf'