Oracle 常用SQL语句

Oracle SQL 内置函数大全

SQL中的单记录函数

1.ASCII 返回与指定的字符对应的十进制数;

SQL> select ascii('A') A,ascii('a') a,ascii('0') zero,ascii(' ') space from dual; A A ZERO SPACE 65 97 48 32

2.CHR 给出整数,返回对应的字符;

SQL> select chr(54740) zhao,chr(65) chr65 from dual; ZH C -- - 赵 A

3.CONCAT 连接两个字符串;

SQL> select concat('010-','88888888')||'转23' 高乾竞电话 from dual; 高乾竞电话 ---------------- 010-88888888转23

4.INITCAP 返回字符串并将字符串的第一个字母变为大写;

SQL> select initcap('smith') upp from dual; UPP

----- Smith

5.INSTR(C1,C2,I,J) 在一个字符串中搜索指定的字符,返回发现指定的字符的位置;

C1 被搜索的字符串 C2 希望搜索的字符串 I 搜索的开始位置,默认为1 J 出现的位置,默认为1

SQL> select instr('oracle traning','ra',1,2) instring from dual; INSTRING --------- 9

6.LENGTH 返回字符串的长度;

SQL> select name,length(name),addr,length(addr),sal,length(to_char(sal)) from .nchar_tst;

NAME LENGTH(NAME) ADDR LENGTH(ADDR) SAL LENGTH(TO_CHAR(SAL)) 高乾竞 3 北京市海锭区 6 9999.99 7

7.LOWER 返回字符串,并将所有的字符小写

SQL> select lower('AaBbCcDd')AaBbCcDd from dual; AABBCCDD -------- aabbccdd

8.UPPER 返回字符串,并将所有的字符大写

SQL> select upper('AaBbCcDd') upper from dual; UPPER -------- AABBCCDD

9.RPAD和LPAD(粘贴字符)

RPAD 在列的右边粘贴字符 LPAD 在列的左边粘贴字符

SQL> select lpad(rpad('gao',10,'*'),17,'*')from dual; LPAD(RPAD('GAO',1 *******gao******* 不够字符则用*来填满

10.LTRIM和RTRIM

LTRIM 删除左边出现的字符串 RTRIM 删除右边出现的字符串

SQL> select ltrim(rtrim(' gao qian jing ',' '),' ') from dual; LTRIM(RTRIM(' gao qian jing

11.SUBSTR(string,start,count)

取子字符串,从start开始,取count个

SQL> select substr('13088888888',3,8) from dual;

SUBSTR(' -------- 08888888

12.REPLACE('string','s1','s2')

string 希望被替换的字符或变量 s1 被替换的字符串 s2 要替换的字符串

SQL> select replace('he love you','he','i') from dual; REPLACE('HELOVEYOU','HE','I') i love you

13.SOUNDEX 返回一个与给定的字符串读音相同的字符串

SQL> create table table1(xm varchar(8)); SQL> insert into table1 values('weather'); SQL> insert into table1 values('wether'); SQL> insert into table1 values('gao');

SQL> select xm from table1 where soundex(xm)=soundex('weather'); XM -------- weather wether

14.TRIM('s' from 'string')

LEADING 剪掉前面的字符 TRAILING 剪掉后面的字符 如果不指定,默认为空格符

15.ABS 返回指定值的绝对值

SQL> select abs(100),abs(-100) from dual; ABS(100) ABS(-100) 100 100

16.ACOS 给出反余弦的值

SQL> select acos(-1) from dual; ACOS(-1) --------- 3.1415927

17.ASIN 给出反正弦的值

SQL> select asin(0.5) from dual; ASIN(0.5) .52359878

18.ATAN 返回一个数字的反正切值

SQL> select atan(1) from dual; ATAN(1)

.78539816

19.CEIL 返回大于或等于给出数字的最小整数

SQL> select ceil(3.1415927) from dual; CEIL(3.1415927) 4

20.COS 返回一个给定数字的余弦

SQL> select cos(-3.1415927) from dual; COS(-3.1415927) -1

21.COSH 返回一个数字反余弦值

SQL> select cosh(20) from dual; COSH(20) --------- 242582598

22.EXP 返回一个数字e的n次方根

SQL> select exp(2),exp(1) from dual; EXP(2) EXP(1) 7.3890561 2.7182818

23.FLOOR 对给定的数字取整数

SQL> select floor(2345.67) from dual;

FLOOR(2345.67) 2345

24.LN 返回一个数字的对数值

SQL> select ln(1),ln(2),ln(2.7182818) from dual; LN(1) LN(2) LN(2.7182818) 0 .69314718 .99999999

25.LOG(n1,n2) 返回一个以n1为底n2的对数

SQL> select log(2,1),log(2,4) from dual; LOG(2,1) LOG(2,4) 0 2

26.MOD(n1,n2) 返回一个n1除以n2的余数

SQL> select mod(10,3),mod(3,3),mod(2,3) from dual; MOD(10,3) MOD(3,3) MOD(2,3) 1 0 2

27.POWER 返回n1的n2次方根

SQL> select power(2,10),power(3,3) from dual; POWER(2,10) POWER(3,3) 1024 27

28.ROUND和TRUNC

按照指定的精度进行舍入

SQL> select round(55.5),round(-55.4),trunc(55.5),trunc(-55.5) from dual; ROUND(55.5) ROUND(-55.4) TRUNC(55.5) TRUNC(-55.5) 56 -55 55 -55

29.SIGN 取数字n的符号,大于0返回1,小于0返回-1,等于0返回0

SQL> select sign(123),sign(-100),sign(0) from dual; SIGN(123) SIGN(-100) SIGN(0) 1 -1 0

30.SIN 返回一个数字的正弦值

SQL> select sin(1.57079) from dual; SIN(1.57079) 1

31.SIGH 返回双曲正弦的值

SQL> select sin(20),sinh(20) from dual; SIN(20) SINH(20) .91294525 242582598

32.SQRT 返回数字n的根

SQL> select sqrt(64),sqrt(10) from dual; SQRT(64) SQRT(10) 8 3.1622777

33.TAN 返回数字的正切值

SQL> select tan(20),tan(10) from dual; TAN(20) TAN(10) 2.2371609 .64836083

34.TANH

返回数字n的双曲正切值

SQL> select tanh(20),tan(20) from dual; TANH(20) TAN(20) 1 2.2371609

35.TRUNC

按照指定的精度截取一个数

SQL> select trunc(124.1666,-2) trunc1,trunc(124.16666,2) from dual; TRUNC1 TRUNC(124.16666,2) 100 124.16

36.ADD_MONTHS

增加或减去月份

SQL> select to_char(add_months(to_date('199912','yyyymm'),2),'yyyymm') from dual; TO_CHA 200002

SQL> select to_char(add_months(to_date('199912','yyyymm'),-2),'yyyymm') from dual; TO_CHA 199910

37.LAST_DAY

返回日期的最后一天

SQL> select to_char(sysdate,'yyyy.mm.dd'),to_char((sysdate)+1,'yyyy.mm.dd') from dual;

TO_CHAR(SY TO_CHAR((S 2004.05.09 2004.05.10

SQL> select last_day(sysdate) from dual; LAST_DAY(S 31-5月 -04

38.MONTHS_BETWEEN(date2,date1)

给出date2-date1的月份

SQL> select months_between('19-12月-1999','19-3月-1999') mon_between from dual;

MON_BETWEEN 9

SQL>selectmonths_between(to_date('2000.05.20','yyyy.mm.dd'),to_date('2005.05.20','yyyy.dd')) mon_betw from dual; MON_BETW

-60

39.NEW_TIME(date,'this','that')

给出在this时区=other时区的日期和时间

SQL> select to_char(sysdate,'yyyy.mm.dd hh24:mi:ss') bj_time,to_char(new_time 2 (sysdate,'PDT','GMT'),'yyyy.mm.dd hh24:mi:ss') los_angles from dual; BJ_TIME LOS_ANGLES

2004.05.09 11:05:32 2004.05.09 18:05:32

40.NEXT_DAY(date,'day')

给出日期date和星期x之后计算下一个星期的日期

SQL> select next_day('18-5月-2001','星期五') next_day from dual; NEXT_DAY 25-5月 -01

41.SYSDATE 用来得到系统的当前日期

SQL> select to_char(sysdate,'dd-mm-yyyy day') from dual; TO_CHAR(SYSDATE,' 09-05-2004 星期日

trunc(date,fmt)按照给出的要求将日期截断,如果fmt='mi'表示保留分,截断秒 SQL> select to_char(trunc(sysdate,'hh'),'yyyy.mm.dd hh24:mi:ss') hh, 2 to_char(trunc(sysdate,'mi'),'yyyy.mm.dd hh24:mi:ss') hhmm from dual; HH HHMM

2004.05.09 11:00:00 2004.05.09 11:17:00

42.CHARTOROWID 将字符数据类型转换为ROWID类型 SQL> select rowid,rowidtochar(rowid),ename from scott.emp; ROWID ROWIDTOCHAR(ROWID) ENAME

AAAAfKAACAAAAEqAAA AAAAfKAACAAAAEqAAA SMITH AAAAfKAACAAAAEqAAB AAAAfKAACAAAAEqAAB ALLEN AAAAfKAACAAAAEqAAC AAAAfKAACAAAAEqAAC WARD AAAAfKAACAAAAEqAAD AAAAfKAACAAAAEqAAD JONES

43.CONVERT(c,dset,sset)

将源字符串 sset从一个语言字符集转换到另一个目的dset字符集 SQL> select convert('strutz','we8hp','f7dec') \ conver strutz

44.HEXTORAW 将一个十六进制构成的字符串转换为二进制 45.RAWTOHEXT 将一个二进制构成的字符串转换为十六进制 46.ROWIDTOCHAR 将ROWID数据类型转换为字符类型 47.TO_CHAR(date,'format')

SQL> select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual; TO_CHAR(SYSDATE,'YY 2004/05/09 21:14:41

48.TO_DATE(string,'format') 将字符串转化为ORACLE中的一个日期 49.TO_MULTI_BYTE 将字符串中的单字节字符转化为多字节字符

SQL> select to_multi_byte('高') from dual; TO -- 高

50.TO_NUMBER

将给出的字符转换为数字

SQL> select to_number('1999') year from dual; YEAR 1999

51.BFILENAME(dir,file)指定一个外部二进制文件

SQL>insert into file_tb1 values(bfilename('lob_dir1','image1.gif'));

52.CONVERT('x','desc','source') 将x字段或变量的源source转换为desc

SQL> select sid,serial#,username,decode(command, 2 0,'none', 3 2,'insert', 4 3, 5 'select', 6 6,'update',

7 7,'delete', 8 8,'drop',

9 'other') cmd from v$session where type!='background'; SID SERIAL# USERNAME CMD 1 1 none 2 1 none 3 1 none 4 1 none 5 1 none 6 1 none 7 1275 none 8 1275 none 9 20 GAO select 10 40 GAO none

53.DUMP(s,fmt,start,length)

DUMP函数以fmt指定的内部数字格式返回一个VARCHAR2类型的值 SQL> col global_name for a30 SQL> col dump_string for a50 SQL> set lin 200

SQL> select global_name,dump(global_name,1017,8,5) dump_string from global_name;

GLOBAL_NAME DUMP_STRING

ORACLE.WORLD Typ=1 Len=12 CharacterSet=ZHS16GBK: W,O,R,L,D

54.EMPTY_BLOB()和EMPTY_CLOB()

这两个函数都是用来对大数据类型字段进行初始化操作的函数

55.GREATEST

返回一组表达式中的最大值,即比较字符的编码大小. SQL> select greatest('AA','AB','AC') from dual; GR -- AC

SQL> select greatest('啊','安','天') from dual; GR -- 天

56.LEAST

返回一组表达式中的最小值

SQL> select least('啊','安','天') from dual; LE -- 啊

57.UID

返回标识当前用户的唯一整数

SQL> show user USER 为\

SQL> select username,user_id from dba_users where user_id=uid; USERNAME USER_ID GAO 25

58.USER

返回当前用户的名字 SQL> select user from dual; USER GAO

59.USEREVN

返回当前用户环境的信息,opt可以是:

ENTRYID,SESSIONID,TERMINAL,ISDBA,LABLE,LANGUAGE,CLIENT_INFO,LANG,VSIZE

ISDBA 查看当前用户是否是DBA如果是则返回true SQL> select userenv('isdba') from dual; USEREN FALSE

SQL> select userenv('isdba') from dual; USEREN TRUE

SESSION 返回会话标志

SQL> select userenv('sessionid') from dual; USERENV('SESSIONID') 152 ENTRYID 返回会话人口标志

SQL> select userenv('entryid') from dual; USERENV('ENTRYID') 0

INSTANCE

返回当前INSTANCE的标志

SQL> select userenv('instance') from dual; USERENV('INSTANCE') 1

LANGUAGE 返回当前环境变量

SQL> select userenv('language') from dual; USERENV('LANGUAGE')

SIMPLIFIED CHINESE_CHINA.ZHS16GBK LANG

返回当前环境的语言的缩写

SQL> select userenv('lang') from dual; USERENV('LANG') ZHS TERMINAL

返回用户的终端或机器的标志

SQL> select userenv('terminal') from dual; USERENV('TERMINA GAO VSIZE(X)

返回X的大小(字节)数

SQL> select vsize(user),user from dual; VSIZE(USER) USER 6 SYSTEM

60.AVG(DISTINCT|ALL)

all表示对所有的值求平均值,distinct只对不同的值求平均值 SQLWKS> create table table3(xm varchar(8),sal number(7,2)); 语句已处理。

SQLWKS> insert into table3 values('gao',1111.11); SQLWKS> insert into table3 values('gao',1111.11); SQLWKS> insert into table3 values('zhu',5555.55); SQLWKS> commit;

SQL> select avg(distinct sal) from gao.table3; AVG(DISTINCTSAL) 3333.33

SQL> select avg(all sal) from gao.table3; AVG(ALLSAL) 2592.59

61.MAX(DISTINCT|ALL)

求最大值,ALL表示对所有的值求最大值,DISTINCT表示对不同的值求最大值,相同的只取一次

SQL> select max(distinct sal) from scott.emp; MAX(DISTINCTSAL) 5000

62.MIN(DISTINCT|ALL)

求最小值,ALL表示对所有的值求最小值,DISTINCT表示对不同的值求最小值,相同的只取一次

SQL> select min(all sal) from gao.table3; MIN(ALLSAL) 1111.11

63.STDDEV(distinct|all)

求标准差,ALL表示对所有的值求标准差,DISTINCT表示只对不同的值求标准差 SQL> select stddev(sal) from scott.emp; STDDEV(SAL)

1182.5032

SQL> select stddev(distinct sal) from scott.emp; STDDEV(DISTINCTSAL) 1229.951

64.VARIANCE(DISTINCT|ALL) 求协方差

SQL> select variance(sal) from scott.emp; VARIANCE(SAL) 1398313.9

65.GROUP BY 主要用来对一组数进行统计

SQL> select deptno,count(*),sum(sal) from scott.emp group by deptno; DEPTNO COUNT(*) SUM(SAL) 10 3 8750 20 5 10875 30 6 9400

66.HAVING 对分组统计再加限制条件

SQL> select deptno,count(*),sum(sal) from scott.emp group by deptno having nt(*)>=5;

DEPTNO COUNT(*) SUM(SAL) 20 5 10875 30 6 9400

SQL> select deptno,count(*),sum(sal) from scott.emp having count(*)>=5 group by tno ;

DEPTNO COUNT(*) SUM(SAL) 20 5 10875 30 6 9400

67.ORDER BY 用于对查询到的结果进行排序输出

SQL> select deptno,ename,sal from scott.emp order by deptno,sal desc; DEPTNO ENAME SAL 10 KING 5000 10 CLARK 2450 10 MILLER 1300 20 SCOTT 3000 20 FORD 3000 20 JONES 2975 20 ADAMS 1100 20 SMITH 800 30 BLAKE 2850 30 ALLEN 1600 30 TURNER 1500 30 WARD 1250 30 MARTIN 1250

30 JAMES 950

常用SQL语句总结

1.曾经不小心把开发库的数据库表全部删除，当时吓的要死。结果找到下面的语句恢复到了1个小时之前的数据！很简单。 注意使用管理员登录系统：

select * from 表名 as of timestamp sysdate-1/12 //查询两个小时前的某表数据！既然两小时以前的数据都得到了，继续怎么做，知道了吧。。 drop table 表名;

数据库误删除表之后恢复:（ 绝对ok，我就做过这样的事情，汗 ）不过要记得删除了哪些表名。

flashback table 表名 to before drop; 2.查询得到当前数据库中锁，以及解锁：

查锁

SELECT /*+ rule */ s.username, decode(l.type,'TM','TABLE LOCK', 'TX','ROW LOCK', NULL) LOCK_LEVEL,

o.owner,o.object_name,o.object_type,

s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser FROM v$session s,v$lock l,dba_objects o WHERE l.sid = s.sid

AND l.id1 = o.object_id(+) AND s.username is NOT NULL;

解锁

alter system kill session 'sid,serial';

如果解不了。直接倒os下kill进程kill －9 spid 3.关于查询数据库用户，权限的相关语句：

Sql代码

1.查看所有用户：

1. select * from dba_user; 2. select * from all_users; 3. select * from user_users; 4. 5.

2.查看用户系统权限：

6. select * from dba_sys_privs; 7. select * from all_sys_privs; 8. select * from user_sys_privs;

3.查看用户对象权限：

9. select * from dba_tab_privs; 10.select * from all_tab_privs; 11.select * from user_tab_privs;

4.查看所有角色：

12.select * from dba_roles;

5.查看用户所拥有的角色：

13.select * from dba_role_privs; 14.select * from user_role_privs;

4.几个经常用到的oracle视图：注意表名使用大写....................

1. 查询oracle中所有用户信息

select * from dba_user;

2. 只查询用户和密码

select username,password from dba_users;

3. 查询当前用户信息

select * from dba_ustats;

4. 查询用户可以访问的视图文本

select * from dba_varrays;

5. 查询数据库中所有视图的文本

select * from dba_views;

6.查询全部索引

select * from user_indexes;

7.查询全部表格

select * from user_tables;

8.查询全部约束

select * from user_constraints;

9.查询全部对象

select * from user_objects;

5.查看当前数据库中正在执行的语句，然后可以继续做很多很多事情，例如查询执行计划等等

(1).查看相关进程在数据库中的会话

1. Select a.sid,a.serial#,a.program, a.status , 2. substr(a.machine,1,20), a.terminal,b.spid

3. from v$session a, v$process b 4. where a.paddr=b.addr 5. and b.spid = &spid; 6.

(2).查看数据库中被锁住的对象和相关会话

7. select a.sid,a.serial#,a.username,a.program, 8. c.owner, c.object_name

9. from v$session a, v$locked_object b, all_objects

c

10. where a.sid=b.session_id and 11. c.object_id = b.object_id; 12.

(3).查看相关会话正在执行的SQL

13. select sql_text from v$sqlarea where address =

14. ( select sql_address from v$session where sid =

&sid );

6.查询表的结构：表名大写！！

select t.COLUMN_NAME, t.DATA_TYPE,

nvl(t.DATA_PRECISION, t.DATA_LENGTH), nvl(T.DATA_SCALE, 0), c.comments

from all_tab_columns t, user_col_comments c whEre t.TABLE_NAME = c.table_name and t.COLUMN_NAME = c.column_name

and t.TABLE_NAME = UPPER('OM_EMPLOYEE_T') order by t.COLUMN_ID

7.行列互换： 建立一个例子表：

1. CREATE TABLE t_col_row( 2. ID INT,

3. c1 VARCHAR2(10), 4. c2 VARCHAR2(10), 5. c3 VARCHAR2(10));

6. INSERT INTO t_col_row VALUES (1, 'v11', 'v21', 'v31'); 7. INSERT INTO t_col_row VALUES (2, 'v12', 'v22', NULL); 8. INSERT INTO t_col_row VALUES (3, 'v13', NULL, 'v33'); 9. INSERT INTO t_col_row VALUES (4, NULL, 'v24', 'v34'); 10.INSERT INTO t_col_row VALUES (5, 'v15', NULL, NULL); 11.INSERT INTO t_col_row VALUES (6, NULL, NULL, 'v35'); 12.INSERT INTO t_col_row VALUES (7, NULL, NULL, NULL); 13.COMMIT; 14.

下面的是列转行:创建了一个视图

15.CREATE view v_row_col AS 16.SELECT id, 'c1' cn, c1 cv 17.FROM t_col_row 18.UNION ALL

19.SELECT id, 'c2' cn, c2 cv 20.FROM t_col_row 21.UNION ALL

22.SELECT id, 'c3' cn, c3 cv FROM t_col_row; 23.

下面是创建了没有空值的一个竖表: 24.CREATE view v_row_col_notnull AS 25.SELECT id, 'c1' cn, c1 cv 26. FROM t_col_row 27.where c1 is not null 28.UNION ALL

29.SELECT id, 'c2' cn, c2 cv 30. FROM t_col_row

31.where c2 is not null 32.UNION ALL

33.SELECT id, 'c3' cn, c3 cv 34. FROM t_col_row

35.where c3 is not null;

8.下面可能是dba经常使用的oracle视图

1.示例：已知hash_value:3111103299，查询sql语句：

select * from v$sqltext where hashvalue='3111103299' order by piece

2.查看消耗资源最多的SQL：

SELECT hash_value, executions, buffer_gets, disk_reads, parse_calls FROM V$SQLAREA

WHERE buffer_gets > 10000000OR disk_reads > 1000000 ORDERBY buffer_gets + 100 * disk_reads DESC;

3.查看某条SQL语句的资源消耗：

SELECT hash_value, buffer_gets, disk_reads, executions, parse_calls FROM V$SQLAREA

WHERE hash_Value = 228801498AND address = hextoraw('CBD8E4B0');

4.查询sql语句的动态执行计划:

1. 首先使用下面的语句找到语句的在执行计划中的address和hash_code

SELECT sql_text, address, hash_value FROM v$sql t

where (sql_text like '%FUNCTION_T(表名大写!)%')

2. 然后:

SELECT operation, options, object_name, cost FROM v$sql_plan

WHERE address = 'C00000016BD6D248' AND hash_value = 664376056;

5.查询oracle的版本:

select * from v$version; 6.查询数据库的一些参数: select * from v$parameter 7.查找你的session信息

SELECT SID, OSUSER, USERNAME, MACHINE, PROCESS FROM V$SESSION WHERE audsid = userenv('SESSIONID');

8.当machine已知的情况下查找session

SELECT SID, OSUSER, USERNAME, MACHINE, TERMINAL FROM V$SESSION

WHERE terminal = 'pts/tl' AND machine = 'rgmdbs1';

9.查找当前被某个指定session正在运行的sql语句。假设sessionID为100

select b.sql_text

from v$session a,v$sqlarea b

where a.sql_hashvalue=b.hash_value and a.sid=100

9.树形结构connect by 排序：

1.查询树形的数据结构，同时对一层里面的数据进行排序

SELECT last_name, employee_id, manager_id, LEVEL FROM employees

START WITH employee_id = 100

CONNECT BY PRIOR employee_id = manager_id

下面是查询结果

LAST_NAME EMPLOYEE_ID MANAGER_ID LEVEL ------------------------- ----------- ---------- ---------- King 100 1 Cambrault 148 100 2 Bates 172 148 3 Bloom 169 148 3 Fox 170 148 3 Kumar 173 148 3 Ozer 168 148 3 Smith 171 148 3 De Haan 102 100 2 Hunold 103 102 3 Austin 105 103 4 Ernst 104 103 4 Lorentz 107 103 4 Pataballa 106 103 4 Errazuriz 147 100 2 Ande 166 147 3 Banda 167 147 3

10.有时候写多了东西，居然还忘记最基本的sql语法，下面全部写出来，基本的oracle语句都在这里可以找到了。是很基础的语句！ 1.在数据字典查询约束的相关信息：

SELECT constraint_name, constraint_type,search_condition

FROM user_constraints WHERE table_name = 'EMPLOYEES'; //这里的表名都是大写！

2对表结构进行说明： desc Tablename

3查看用户下面有哪些表

select table_name from user_tables; 4查看约束在那个列上建立：

SELECT constraint_name, column_name FROM user_cons_columns

WHERE table_name = 'EMPLOYEES';

5添加主键:

alter Table EMP add constraint my_emp_id_pk primary key (ID);

6添加列：// alter table EMP add column (dept_id number(7))；错误！！

alter table EMP add (dept_id number(7));

7删除一列：

alter table emp drop column dept_id;

8添加列名同时和约束：

alter table EMP add (dept_id number(7)

constraint my_emp_dept_id_fk references dept(ID)); 9改变列：//注意约束不能够修改 的！！

alter table dept80 modify(last_name varchar2(30));//这里使用的是modify而不是alter!

10结合变量查找相关某个表中约束的相关列名：

select constraint_name,column_name from user_cons_columns where table_name = '&tablename'

11添加一个有check约束的新列：

alter table EMP

add (COMMISSION number(2) constraint emp_commission_ck check(commission>0))

12查询数据字典看中间的元素： SELECT object_name, object_type FROM user_objects

WHERE object_name LIKE 'EMP%' OR object_name LIKE 'DEPT%' 13普通的建表语句：

CREATE TABLE dept (deptno NUMBER(2), dname VARCHAR2(14), loc VARCHAR2(13));

14查询对象类型：

SELECT DISTINCT object_type FROM user_objects ;

15使用子查询建立表:

CREATE TABLE dept80

AS SELECT employee_id, last_name, salary*12 ANNSAL,

hire_date FROM employees WHERE department_id = 80;

16删除表：

drop table emp;

17改变对象名：（表名，视图,序列） rename emp to emp_newTable

18添加表的注释：

COMMENT ON TABLE employees IS 'Employee Information';

19创建视图：

CREATE VIEW empvu80

AS SELECT employee_id, last_name, salary

FROM employees WHERE department_id = 80;

20查看视图结构：

describe view_name

21删除视图：

drop view view_name

22找到工资最高的5个人。（top-n分析）（行内视图） select rownum,employee_id from (select employee_id,salary from

employees order by salary desc) where rownum<5;

23在数据字典中查看视图信息：

select viewe_name,text from user_views

24增加一行：

insert into table_name values();

25查看数据字典中的序列：

select * from user_sequences

26建立同义词：

create synonym 同义词名 for 原来的名字

或者 create public synonym 同义词名 for 原来的名字

27建立序列：（注意，这里并没有出现说是哪个表里面的序列！！）

CREATE SEQUENCE dept_deptid_seq INCREMENT BY 10 START WITH 120 MAXVALUE 9999 NOCACHE NOCYCLE

28使用序列：

insert into dept(ID,NAME) values(DEPT_ID_SEQ.nextval,'Administration');

29建立索引：//默认就是nonunique索引，除非使用了关键字：unique

CREATE INDEX emp_last_name_idx ON employees(last_name);

30建立用户：（可能有错，详细查看帮助）

create user username(用户名) identified by oracle(密码)

default tablespace data01(表空间名//默认存在system表空间里面)

quota 10M(设置大小，最大为unlimited) on 表空间名//必须分配配额！

31创建角色：create ROLE manager

赋予角色权限：grant create table,create view to manage

赋予用户角色：grant manager to DENHAAN,KOCHHAR( 两个用户)

32分配权限：

GRANT update (department_name, location_id) ON departments TO scott, manager;

??回收权限

REVOKE select, insert ON departments FROM scott;

33得到所有的时区名字信息：

select * from v$timezone_names

34显示对时区‘US/Eastern’的时区偏移量

select TZ_OFFSET('US/Eastern') from DUAL--dual英文意思是‘双重的’

??显示当前会话时区中的当前日期和时间:

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';--修改显示时间的方式的设置

ALTER SESSION SET TIME_ZONE = '-5:0';--修改时区

SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL;--真正有用的语句！

SELECT CURRENT_TIMESTAMP FROM DUAL;--返回的时间是当前日期和时间，含有时区 SELECT CURRENT_TIMESTAMP FROM DUAL;--返回的时间是当前日期和时间，不含有时区！！！

35显示数据库时区和会话时区的值：

select datimezone,sessiontimezone from dual;

36从时间中提取年，月，日：使用函数extract

select extract(year from sysdate) year,extract(month from sysdate), extract(day from sysdate) from dual;

37使用函数得到数月之后的日期：to_yminterval（‘01-02’）表示加上1年2月，不能够到天！！

select hire_date,hire_date +to_yminterval('01-02') as hire_date_new from employees

where department_id=20

得到多少天之后的日期：直接日期加数字！

select hire_date +3 from employees where department_id=20

38一般的时间函数：

MONTHS_BETWEEN ('01-SEP-95','11-JAN-94')--两个日期之间的月数，返回一个浮点数 ADD_MONTHS ('11-JAN-94',6)--添加月数

NEXT_DAY ('01-SEP-95','FRIDAY') --下一个星期五的日期 LAST_DAY('01-FEB-95')--当月的最后一天！ ROUND(SYSDATE,'MONTH') --四舍五入月 ROUND(SYSDATE ,'YEAR') --四舍五入年 TRUNC(SYSDATE ,'MONTH') --阶段月 TRUNC(SYSDATE ,'YEAR') --截断年

39 group语句：和高级的应用语句：

SELECT department_id, job_id, SUM(salary), COUNT(employee_id) FROM employees GROUP BY department_id, job_id ; 使用having进行约束：

1.group by rollup:对n列组合得到n+1种情况

SELECT department_id, job_id, SUM(salary) FROM employees WHERE department_id < 60 GROUP BY ROLLUP(department_id, job_id);

2.group by cube:得到2的n次方种情况

SELECT department_id, job_id, SUM(salary) FROM employees WHERE department_id < 60 GROUP BY CUBE (department_id, job_id) ;

3.使用grouping得到一行中构成列的情况，只是返回1和0：是空的话就返回1，否则返回0(注意不要弄反了！)

SELECT department_id DEPTID, job_id JOB, SUM(salary), GROUPING(department_id) GRP_DEPT, GROUPING(job_id) GRP_JOB

FROM employees WHERE department_id < 50 GROUP BY ROLLUP(department_id, job_id);

4.grouping sets:根据需要得到制定的组合情况

SELECT department_id, job_id, manager_id,avg(salary) FROM employees GROUP BY GROUPING SETS ((department_id,job_id), (job_id,manager_id));

40from中使用子查询：返回每个部门中大于改部门平均工资的与员工信息

SELECT a.last_name, a.salary, a.department_id, b.salavg FROM employees a,--下面的地方就是子查询了，主要返回的是一组数据！

(SELECT department_id, AVG(salary) salavg FROM employees GROUP BY department_id) b

WHERE a.department_id = b.department_id AND a.salary > b.salavg;

41exists语句的使用：

SELECT employee_id, last_name, job_id, department_id

FROM employees outer--下面的 exists里面的select选择出来的是随便的一个字符或者数字都可以

WHERE EXISTS ( SELECT 'X' FROM employees WHERE manager_id = outer.employee_id);

42厉害的with语句：

WITH

dept_costs AS (--定义了一个临时的表

SELECT d.department_name, SUM(e.salary) AS dept_total--其间定义了一个临时的列dept_total

FROM employees e, departments d WHERE e.department_id = d.department_id GROUP BY d.department_name),/*注意这里有逗号*/ avg_cost AS (

SELECT SUM(dept_total)/COUNT(*) AS dept_avg

FROM dept_costs)--这里的第二张临时表里面就引用了前面定义的临时表和之间的列！

SELECT * FROM dept_costs WHERE dept_total > (SELECT dept_avg FROM avg_cost) ORDER BY department_name;---最后的查询语句中使用了前面的临时表

43遍历树：

SELECT employee_id, last_name, job_id, manager_id FROM employees

START WITH employee_id = 101

CONNECT BY PRIOR manager_id = employee_id ;--自底向上的遍历树。

44.更新语句

UPDATE employees SET

job_id = 'SA_MAN', salary = salary + 1000, department_id = 120 WHERE first_name||' '||last_name = 'Douglas Grant';

UPDATE TABLE (SELECT projs

FROM dept d WHERE d.dno = 123) p SET p.budgets = p.budgets + 1 WHERE p.pno IN (123, 456);

11.导入导出dmp文件:

imp 用户名/密码@数据库 ignore=y file=备份文件 log=D:\\DBtest\\db_bak\\imp.log

exp system/manager@TEST file=d:\\daochu.dmp full=y

Oracle 如何使用超过1.7G的内存

2010-08-03 11:34

如果你的ORACLE 版本是32位的，如果不做一些配置你是无论如何使用不到1.7G以上内存的。

前两天公司里一个软件系统，需要配置这样的环境，对于我这样的ORACLE所谓的高手，有点不知所措，甚至要硬着头皮去搞定它， 这可是到新公司接到的第一件活。必须搞定，否则ORACLE DBA就图有虚名了。 于是马上上网搜一下，文章果然，但是大都雷同，

而且针对的是ORACLE9I 的。我在实验的时候，也把客户的环境搞杂了，实在惭愧呀。不过经过一天多的折腾，也算是搞出来了。 软件环境：

Oracle 10.2.0.1

OS:Windows 2003 Server SP2 32bit 硬件环境: 内存：4G

CPU:Intel Core(TM)2 6300 1.86Ghz

这个环境很重要，特别是ORACLE的版本，跟ORACLE9i的配置肯定是不一样的。

1) 操作系统配置AWE,主要是修改boot.ini文件

右击\我的电脑\，选择\属性\高级选项卡，找到“启动与故障恢复”，单击“设置”，然后单击“编辑”，

加/PAE选项，修改后的文件如下： [boot loader] timeout=30

default=multi(0)disk(0)rdisk(0)partition(2)\\WINDOWS [operating systems]

multi(0)disk(0)rdisk(0)partition(2)\\WINDOWS=\Windows Server 2003, Enterprise\/pae

multi(0)disk(0)rdisk(0)partition(1)\\WINDOWS=\XP Professional\

2) 配置oracle可以使用的内存，修改注册表。 找到ORACLE的注册表项

HKEY_LOCAL_MACHINE\\SOFTWARE\\ORACLE\\KEY_OraDb10g_home1，再你的机器上KEY_OraDb10g_home1不是这个名称,

添加一个字符串值项：AWE_WINDOW_MEMORY ，这个值是你准备分配给数据库用的最大内存数(以BYTE为单位),例如你想分配4G,那这个值就是

4*1024*1024*1024

3) 获取可编辑的ORACLE初始化参数文件 Windows 命令行

Sqlplus \REM (如果登陆不进去将当前用户加入到ora_dba组中)

SQLPLUS>CREATE PFILE='初始化参数文件的路径' from spfile SQLPLUS>Shutdown immediate SQLPLUS>exit

4) 编辑ORACLE初始化参数文件

请用Ｕlatra editor(当然其他的也可以，但是千万不要用记事本) 打开在第三步生成的初始化参数文件(最好能备份一下，以便出现差错的时候还可以使用最初的文件启动) 第一注销掉以下行： sga_max_size

sga_target

DB_CACHE_SIZE

修改或者添加以下参数

*.db_block_buffers=262144 #等于原来的

DB_CACHE_SIZE/db_block_size, 例如你希望DB_CACHE_SIZE的大小是2G,则此值是2*1024*1024K/8K= 262144

*.SHARED_POOL_SIZE=419430400 #是以byte为单位的 400M（这个参数设置过大会报错的，因为ORACLE AWE的配置不是针对他的） *.log_buffer=73400320 #是以byte为单位的 70M（这个参数设置过大会报错的，因为ORACLE AWE的配置不是针对他的）

*.use_indirect_data_buffers=true # 这个参数的含义是我们是通过设置db_block_buffers来计算得到DB_CACHE_SIZE

本文来源：https://www.bwwdw.com/article/5go3.html