oracle基本语句以及函数

本文为大家分享oracle基本语句以及函数,通过本文您将了解到oracle,函数的相关内容,请参考。

1.. 处理NULL

NVL函数可把NULL转换成其它类型的符号

编程技巧: NVL函数在多条件模糊查询的时候比较有用

NVL函数可返回多种数据类型:

返回日期 NVL(start_date,'2002-02-01')

返回字符串 NVL(title,'no title')

返回数字 NVL(salary,1000)

2. 按指定的规则排序

SELECT expr FROM table [ORDER BY {column, expr} [ASC | DESC] ];

默认的排序是ASC升序(由小到大)

还可以ORDER BY 字段名的位置[1]| [2] ASC| DESC;

3. LIKE操作

% 零到任意多个字符 _ 一个字符

例如: 字段名 like 'M%' 字段名 like '%m%' 字段名 like 'job_'

如果要找含下划线的字符, 要加反斜线 例如:字段名 like '%X/_Y%' escape '/'

4. 日期字段的比较

举例:

日期字段 between to_date('2001-12-12','YYYY-MM-DD') and to_date('2002-02-01','YYYY-MM-DD')

日期字段> to_date('2001-12-12','YYYY-MM-DD') and日期字段<=

to_date('2002-02-01','YYYY-MM-DD');

5. 数字函数

ABS 取绝对值 POWER 乘方 LN 10为底数取0

SQRT 平方根 EXP e的n次乘方 LOG(m,n) m为底数n取0

数学运算函数:ACOS ATAN ATAN2 COS COSH SIGN SIN SINH TAN TANH

CEIL 大于或等于取整数

FLOOR 小于或等于取整数

MOD 取余数

ROUND(n,m) 按m的位数取四舍五入值如果round(日期): 中午12以后将是明天的日期.    

round(sysdate,'Y')是年的第一天

TRUNC(n,m) 按m的位数取小数点后的数值如果trunc(日期), 确省的是去掉时间

6. 字符函数

CHR 按数据库的字符集由数字返回字符

CONCAT(c1,c2) 把两个字符c1,c2组合成一个字符, 和 || 相同

REPLACE(c,s,r) 把字符c里出现s的字符替换成r, 返回新字符

SUBSTR(c,m,n) m大于0,字符c从前面m处开始取n位字符,m等于0和1一样,

m小与0,字符c从后面m处开始取n位字符

TRANSLATE(c,f1,t1) 字符c按f1到t1的规则转换成新的字符串

INITCAP 字符首字母大写,其它字符小写

LOWER 字符全部小写

UPPER 字符全部大写

LTRIM(c1,c2) 去掉字符c1左边出现的字符c2

RTRIM(c1,c2)

TRIM(c1,c2) 去掉字符c1左右两边的字符c2

LPAD(c1,n,c2) 字符c1按制定的位数n显示不足的位数用c2字符串替换左边的空位

RPAD(c1,n,c2)

7. 日期函数

ADD_MONTHS(d,n) 日期值加n月

LAST_DAY  返回当月的最后一天的日期

MONTHS_BETWEEN(d1,d2) 两个日期值间的月份,d1<d2 返回负数

NEXT_DAY  返回日期值下一天的日期

SYSDATE 当前的系统时间

DUAL是SYS用户下一个空表,它只有一个字段dummy

8. 转换函数(1)

TO_CHAR(date,'日期显示格式')

TO_CHAR(number) 用于显示或报表的格式对齐

TO_DATE(char,'日期显示格式')

TO_LOB 把long字段转换成lob字段

TO_NUMBER(char) 用于计算或者比较大小

9.转换函数(2)

to_date里日期显示格式

YYYY 年 YEAR YYY YY Y

Q 季度

MM 月 MONTH MON

W 星期 (week of month) WW, IW (week of year)

(说明:周计是按ISO标准,从1月1日的星期数到后面七天为一周,不一定是从周一到周日)

DD 日 DAY DY

HH24 小时 HH12 HH

MI 分钟

SS 秒

如果想固定日期的显示格式可以在数据库的参数文件initorasid.ora里新写一行参数     

NLS_DATE_FORMAT=yyyy-mm-dd

hh24:mi:ss可以在UNIX环境变量或者NT的注册表里的设置 NLS_DATE_FORMAT=yyyy-mm-dd hh24:mi:ss

10. 输入字符,返回数字的函数

instr(c1,c2) 字符c2出现在c1的位置, 不出现, 返回0, 常用于模糊查询

length  按数据库的字符集,算出字符c的长度,跟数据库的字符集有关, 一个汉字长度为1

6. 有逻辑比较的函数NVL(EXPR1, EXPR2)函数

解释: IF EXPR1=NULL RETURN EXPR2

ELSE RETURN EXPR1

DECODE(AA0V10R10V20R2....)函数

解释: IF AA=V1 THEN RETURN R1

IF AA=V2 THEN RETURN R2

..…

ELSE

RETURN NULL

举例: decode(id,1,'dept sale',2,'dept tech')

11. 数据表间的连接

简单的连接语法:

SELECT 字段名1, 字段名2, …… FROM 表名1, [表名2, ……]

WHERE 表名1.字段名 = 表名2. 字段名 [ AND ……] ;

SELECT 字段名1, 字段名2, …… FROM 表名1, [表名2, ……]

WHERE 表名1.字段名 = 表名2. 字段名(+) [ AND ……] ;

有(+)号的字段位置自动补空值

连接的分类:

等于的连接 =

不等于的连接 != BETWEEN … AND … IN 注意IN和OR不能一起用

外连接 有一个字段名(+) , 没有满足的条件补空值

自连接 同一个表自己跟自己连接 例如找重复记录

12.数据表间的连接例子

删除table_name表里字段名email重复的记录:

SQL>delete from table_name t1

where t1.rowid >

(select min(rowid) from table_name t2

where t1.email = t2.email

group by email

having count(email) > 1);

找到手机用户的服务区域:

SQL> select a.handphoneno,nvl(c.name,'null'),a.totalscore

from topscore a,chargeoperator cc,chargeoperatorinfo c

where substr(a.handphoneno,1,7)=cc.hpnohead(+)

and cc.chargetype=c.chargetype(+)

order by a.totalscore desc;

集合函数

经常和group by一起使用

二. 集合函数列表

AVG (DISTINCT | ALL | N) 取平均值

COUNT (DISTINCT | ALL | N | expr | * ) 统计数量

MAX (DISTINCT | ALL | N) 取最大值

MIN (DISTINCT | ALL | N) 取最小值

SUM (DISTINCT | ALL | N) 取合计值

STDDEV (DISTINCT | ALL | N) 取偏差值,如果组里选择的内容都相同,结果为0

VARIANCE (DISTINCT | ALL | N) 取平方偏差值

使用集合函数的语法

SELECT column, group_function FROM table

WHERE condition GROUP BY group_by_expression

HAVING group_condition ORDER BY column;

3. 使用count时的注意事项

SELECT COUNT(*)  FROM table;

SELECT COUNT(常量) FROM table;

都是统计表中记录数量,如果没有PK后者要好一些

SELECT COUNT(all 字段名) FROM table;

SELECT COUNT(字段名) FROM table;

不会统计为NULL的字段的数量

SUM,AVG时都会忽略为NULL的字段

4. 用group by时的限制条件

SELECT字段名不能随意, 要包含在GROUP BY的字段里

GROUP BY后ORDER BY时不能用位置符号和别名

限制GROUP BY的显示结果, 用HAVING条件

5. 例子

SQL> select title,sum(salary) payroll from s_emp

where title like 'VP%' group by title

having sum(salary)>5000 order by sum(salary) desc;

找出某表里字段重复的记录数, 并显示

SQL> select (duplicate field names) from table_name

group by (list out fields) having count(*)>1;

6.取出结果集的80 到100的SQL语句

ORACLE处理每个结果集只有一个ROWNUM字段标明它的逻辑位置,

并且只能 用ROWNUM<100, 不能用ROWNUM>80。

以下是经过分析后较好的两种ORACLE取得结果集80到100间的SQL语句

( ID是唯一关键字的字段名 )

语句写法:

SQL>select * from (

( select rownum as numrow, c.* from (

select [field_name,...] from table_name where 条件1 order by 条件2) c)

where numrow > 80 and numrow <= 100 )

order by 条件3;

7.绑定变量SQL语句的例子(1)

SQL> select id, last_name, salary from s_emp where dept_id = &department_number;

Enter value for department_number: 10

old 1: select id, last_name, salary from s_emp where dept_id=&department_number;

new 1: select id, last_name, salary from s_emp where dept_id= 10

SQL> SET VERIFY OFF | ON;可以关闭和打开提示确认信息old 1和new 1的显示.

select id, last_name, salary

from s_emp

where title = '&job_title';

Enter value for job_title: Stock Clerk

SQL> select id, last_name, salary

from s_emp

where hiredate >to_date( '&start_hire_date','YYYY-MM-DD');

Enter value for start_hire_date : 2001-01-01

把绑定字符串和日期类型变量时,变量外面要加单引号

也可绑定变量来查询不同的字段名

输入变量值的时候不要加;等其它符号

8.DEFINE的语法和例子

SQL> DEFINE variable = value

说明: variable 指变量名 value 指变量值

定义好了变良值后, 执行绑定变量的SQL语句时不再提示输入变量

使用DEFINE的例子:

SQL> DEFINE dname = sales

SQL> DEFINE dname

DEFINE dname = “sales” (CHAR)

SQL> select name from dept where lower(name)='&dname';

NAME

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

sales

sales

SQL> UNDEFINE dname

SQL> DEFINE dname

Symbol dname is UNDEFINED

文章内容就介绍到这儿,文章由web开发乐园收集自网络,希望对您有参考价值。

您可以选择一种方式赞助本站

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: