`

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


分享到:
评论

相关推荐

    Oracle基本语句和基本函数

    这个压缩包文件"Oracle基本语句和基本函数.doc"显然包含了关于Oracle数据库操作的基础知识,包括SQL语句和内置函数的使用。 一、Oracle SQL基本语句 1. 数据查询:SQL的SELECT语句用于从数据库中检索数据,例如`...

    Oracle Sql语句转换成Mysql Sql语句

    5. **函数和操作符**:两者内置的函数和操作符有所不同,例如Oracle的NVL对应MySQL的IFNULL,Oracle的TO_DATE对应MySQL的STR_TO_DATE。 6. **序列**:Oracle使用序列(SEQUENCE)来生成唯一ID,MySQL则常使用自增...

    Oracle中实现Split函数功能

    Oracle 中实现 Split 函数功能 Oracle 中实现 Split 函数功能是指在 Oracle 数据库中实现类似于 C# 等编程语言中的 Split 函数,用于拆分字符的功能。下面是实现 Oracle 中 Split 函数功能的详细介绍。 首先,...

    oracle 函数大全 参考函数 手册 速查 chm格式

    9. PL/SQL控制结构:如CASE语句、IF...THEN...ELSE...END IF结构等,用于编写过程和函数。 10. 游标和查询相关函数:如CURSOR(定义游标)、FETCH(从游标获取数据)以及嵌套查询中的聚合函数。 11. 连接函数:如...

    Oracle数据库语句大全.doc

    Oracle 数据库语句大全还包括创建表空间、删除表空间、查询表空间基本信息、创建用户、更改用户、锁定用户、删除用户等语句。 二.数据操纵语言(DML) 数据操纵语言(DML)用于操作数据库中的数据,包括插入、...

    oracle日期时间判断函数

    针对标题"oracle日期时间判断函数"和描述中提到的内容,我们可以深入探讨两个关键的函数,它们主要用于判断字符串是否可以转换为合法的日期时间格式。 1. TO_DATE函数: TO_DATE是Oracle中用于将字符串转换为日期...

    Oracle-Sql语句资料oracle+110个常用函数经典SQL语句大全.zip

    Oracle_Sql语句资料oracle+110个常用函数经典SQL语句大全,可供学习参考。

    oracle 身份证号码校验函数

    Oracle身份证号码校验函数,可以将15位身份号码升为18位,检验出生年月等信息

    oracle自定义日期函数

    Oracle 自定义日期函数 Oracle 日期处理函数是数据库管理系统中非常重要的一部分,最近看到有人在做 T-SQL 与 PL/SQL 的转化,问了些有关时间函数的处理。在 PL/SQL 中,Oracle 提供了其他的方法来处理日期函数,...

    hibernate 调用oracle函数

    文档可能涵盖了更复杂的场景,如处理多参数函数、处理复杂返回类型、以及在事务管理中调用函数等。 总的来说,Hibernate调用Oracle函数涉及到了数据库设计、ORM框架的使用、原生SQL查询和结果映射等多个方面,熟练...

    Oracle常用语句-总结文档汇总

    包括SELECT的基本语法,聚合函数(如COUNT、SUM、AVG、MAX、MIN)的使用,GROUP BY和HAVING子句进行数据分组,以及ORDER BY用于排序结果。此外,LEFT JOIN、RIGHT JOIN、INNER JOIN和FULL OUTER JOIN等连接查询也是...

    oracle case语句的介绍

    ### Oracle CASE 语句介绍及应用案例 #### 一、Oracle CASE 语句概述 在 Oracle 数据库中,CASE 语句是一种非常实用且灵活的条件判断表达式,它可以用于根据不同的条件返回不同的值。CASE 语句可以分为两种类型:...

    Oracle字符相似度函数

    Oracle字符相似度函数是Oracle数据库系统提供的一种功能,用于评估两个字符串之间的相似性。这个功能在数据清洗、信息检索、文本分析等领域具有广泛的应用。在SQL查询中,我们可以直接使用这些函数来比较不同字符串...

    oracle sql语句学习

    1. **PL/SQL**:Oracle的Procedural Language/SQL,是一种结合了SQL与过程编程的语言,用于编写存储过程、函数、触发器等。 2. **子查询**:在查询语句中嵌套其他查询,用于获取更复杂的查询结果。 3. **连接(JOIN...

    oracle常用SQL语句(汇总版).docx

    Oracle 常用 SQL 语句汇总 Oracle 是一个功能强大且复杂的关系数据库管理系统,它提供了多种 SQL 语句来管理和操作数据库。在本文中,我们将详细介绍 Oracle 中常用的 SQL 语句,包括数据控制语句(DML)、数据定义...

    Oracle数据库常用sql语句的分类和常用函数.docx

    根据提供的文档信息,本文将详细解析Oracle数据库中的关键SQL语句分类、常用SQL语句以及Oracle函数的应用场景。此外,还将简要介绍Oracle数据库的一些基本管理命令,如启动与关闭服务、用户管理等。 ### 一、Oracle...

    Oracle基本sql语句

    本资源“Oracle基本sql语句”旨在为初学者提供一个全面的入门指南,包括SQL语法、实用案例以及函数应用等内容。 一、SQL基础 SQL(Structured Query Language,结构化查询语言)是用于管理和处理关系数据库的标准...

    经典的ORACLE查询语句教学资料

    "第二天"的资料可能涉及了SQL基础,讲解了如何使用SELECT语句进行基本的数据查询,包括选择字段、过滤记录(WHERE子句)、排序结果(ORDER BY子句)、分组数据(GROUP BY子句)和聚合函数(COUNT、SUM、AVG、MAX、...

    oracle函数大全(CHM格式).rar

    在这个"Oracle函数大全"的CHM文件中,我们可以期待找到分类清晰、全面详尽的Oracle函数文档,包括函数的语法、参数、返回值、使用示例以及可能遇到的问题和解决方法。 Oracle数据库中的函数可以分为多种类型,包括...

    java调用oracle存储过程或者函数

    调用Oracle函数的过程与调用存储过程类似,但创建CallableStatement时的SQL语句略有不同。由于函数会返回一个值,所以格式通常是`{? = call function_name(?, ?, ...)}`。在执行后,通过`CallableStatement....

Global site tag (gtag.js) - Google Analytics