ITPub博客

首页 > 数据库 > Oracle > SQL查询和SQL函数

SQL查询和SQL函数

Oracle 作者:清幽小溪 时间:2013-10-20 20:20:24 0 删除 编辑
Oracle 服务器由Oracle 数据库和 Oracle 实例组成
Oracle 实例由系统全局区内存结构和用于管理数据库的后台进程组成
Oracle 中用于访问数据库的主要查询工具有 SQL*Plus、iSQL*Plus 和 PL/SQL 
Oracle 企业管理器是用于管理、诊断和调整多个数据库的工具
Oracle 中的 SYSTEM 用户和 SYS 用户具有管理权限,而 SCOTT 用户只有基本的权限

SQL 支持下列类别的命令:
数据定义语言(DDL):create alter drop
数据操纵语言(DML): insert delete select update
事务控制语言(TCL): commit savepoint rollback
数据控制语言(DCL): grant revoke

创建表时,必须为各个列指定数据类型
以下是 Oracle 数据类型的类别:
SQL查询和SQL函数
SQL查询和SQL函数
当需要固定长度的字符串时,使用 CHAR 数据类型。
CHAR 数据类型存储字母数字值。
CHAR 数据类型的列长度可以是 1 到 2000 个字节。

VARCHAR2数据类型支持可变长度字符串
VARCHAR2数据类型存储字母数字值
VARCHAR2数据类型的大小在1至4000个字节范围内

LONG 数据类型存储可变长度字符数据
LONG 数据类型最多能存储 2GB
一张表中只能出来一次
不能在Long类型的列上建立主键,唯一约束,索引
不能用于存储过程的参数类型

数值数据类型
可以存储整数、浮点数和实数
最高精度为 38 位
数值数据类型的声明语法:
NUMBER [( p[, s])]
P表示精度,S表示小数点的位数

日期时间数据类型存储日期和时间值,包括年、月、日,小时、分钟、秒
主要的日期时间类型有:
DATE - 存储日期和时间部分,精确到整个的秒
TIMESTAMP - 存储日期、时间和时区信息,秒值精确到小数点后6位

RAW 数据类型用于存储二进制数据
RAW 数据类型最多能存储 2000 字节
LONG RAW 数据类型用于存储可变长度的二进制数据
LONG RAW 数据类型最多能存储 2 GB

LOB 称为“大对象”数据类型,可以存储多达 4GB 的非结构化信息,例如声音剪辑和视频文件等
LOB 数据类型允许对数据进行高效、随机、分段的访问

SQL查询和SQL函数

CLOB 即 Character LOB(字符 LOB),它能够存储大量字符数据
BLOB 即 Binary LOB(二进制 LOB),可以存储较大的二进制对象,如图形、视频剪辑和声音文件
BFILE 即 Binary File(二进制文件),它用于将二进制数据存储在数据库外部的操作系统文件中

Oracle 中伪列就像一个表列,但是它并没有存储在表中
伪列可以从表中查询,但不能插入、更新和删除它们的值
常用的伪列有ROWID和ROWNUM
ROWID 是表中行的存储地址,该地址可以唯一地标识数据库中的一行,可以使用 ROWID 伪列快速地定位表中的一行
ROWNUM 是查询返回的结果集中行的序号,可以使用它来限制查询返回的行数

数据定义语言用于改变数据库结构,包括创建、更改和删除数据库对象
用于操纵表结构的数据定义语言命令有:
CREATE TABLE
ALTER TABLE
TRUNCATE TABLE
DROP TABLE

数据操纵语言用于检索、插入和修改数据
数据操纵语言是最常见的SQL命令
数据操纵语言命令包括:
SELECT
INSERT
UPDATE
DELETE

利用现有的表创建表
语法:
          CREATE TABLE AS
          SELECT column_names FROM ;
选择无重复的行
在SELECT子句,使用DISTINCT关键字
SELECT DISTINCT vencode FROM vendor_master;

插入日期类型的值
日期数据类型的默认格式为“DD-MON-RR”
使用日期的默认格式
使用TO_DATE函数转换
INSERT INTO order_master 
VALUES('o001', '12-5月-05', 'V002', 'c', '25-5月-05');
INSERT INTO my_table (date_col)
VALUES (TO_DATE('2005-10-18', 'YYYY-MM-DD'));

事务是最小的工作单元,作为一个整体进行工作
保证事务的整体成功或失败,称为事务控制
用于事务控制的语句有:
COMMIT - 提交并结束事务处理
ROLLBACK -  撤销事务中已完成的工作
SAVEPOINT – 标记事务中可以回滚的点
SQL>  UPDATE order_master 
           SET del_date = ‘30-8月-05’ 
           WHERE orderno <= ’o002’;
SQL>  SAVEPOINT mark1;
SQL>  DELETE FROM order_master WHERE orderno = ‘o002’;
SQL>  SAVEPOINT mark2;
SQL>  ROLLBACK TO SAVEPOINT mark1;
SQL>  COMMIT;

数据控制语言为用户提供权限控制命令 
用于权限控制的命令有:
GRANT 授予权限
REVOKE 撤销已授予的权限
 GRANT SELECT ON vendor_master 
     TO accounts WITH GRANT OPTION;
 REVOKE SELECT, UPDATE ON order_master
     FROM MARTIN;
Oracle 支持的 SQL 操作符分类如下:
算术操作符用于执行数值计算
可以在SQL语句中使用算术表达式,算术表达式由数值数据类型的列名、数值常量和连接它们的算术操作符组成
算术操作符包括加(+)、减(-)、乘(*)、除(/)
 SELECT itemdesc, max_level - qty_hand avble_limit 
     FROM itemfile WHERE p_category='spares';
 SELECT itemdesc, itemrate*(max_level - qty_hand)
      FROM itemfile 
      WHERE p_category='spares';

比较操作符用于比较两个表达式的值
比较操作符包括 =、!=、<、>、<=、>=、BETWEEN…AND、IN、LIKE 和 IS NULL等
SELECT itemdesc, re_level
     FROM  itemfile
     WHERE qty_hand < max_level/2;
 SELECT orderno FROM order_master 
     WHERE del_date IN (‘06-1月-05’,‘05-2月-05');
 SELECT vencode,venname,tel_no 
     FROM vendor_master 
     WHERE venname LIKE 'j___s';

逻辑操作符用于组合多个计较运算的结果以生成一个或真或假的结果。
逻辑操作符包括与(AND)、或(OR)和非(NOT)。
SELECT * FROM order_master 
     WHERE odate > ‘10-5月-05' 
     AND del_date < ‘26-5月-05’;

集合操作符将两个查询的结果组合成一个结果 : union ;union all; intersect; minus
NTERSECT 操作符只返回两个查询的公共行。
 SELECT orderno FROM order_master 
     INTERSECT 
     SELECT orderno FROM order_detail;
MINUS 操作符返回从第一个查询结果中排除第二个查
询中出现的行。
SELECT orderno FROM order_master 
     MINUS
     SELECT orderno FROM order_detail;
连接操作符用于将多个字符串或数据值合并成一个字符串
SELECT (venname|| ' 的地址是 '
     ||venadd1||' '||venadd2 ||' '||venadd3) address
     FROM vendor_master WHERE vencode='V001';

SQL 操作符的优先级从高到低的顺序是:
算术操作符           --------最高优先级
连接操作符
比较操作符
NOT 逻辑操作符
AND 逻辑操作符
OR   逻辑操作符   --------最低优先级 

Oracle 提供一系列用于执行特定操作的函数
SQL 函数带有一个或多个参数并返回一个值
以下是SQL函数的分类:
单行函数对于从表中查询的每一行只返回一个值
可以出现在 SELECT 子句中和 WHERE 子句中 
单行函数可以大致划分为:
日期函数
数字函数
字符函数
转换函数
其他函数

日期函数对日期值进行运算,并生成日期数据类型或数值类型的结果
日期函数包括:
ADD_MONTHS              在日期上加(减)月份
MONTHS_BETWEEN 比较两个日期月份差
LAST_DAY 返回某日期当月最后一天
ROUND 四舍五入
NEXT_DAY   下一个指定星期的日期
TRUNC        截断
EXTRACT   截取日期部分

字符函数接受字符输入并返回字符或数值
   函数   输入   输出
Initcap(char) Select initcap(‘hello’) from dual; Hello 
Lower(char) Select lower(‘FUN’) from dual; fun 
Upper(char) Select upper(‘sun’) from dual; SUN 
Ltrim(char,set) Select ltrim( ‘xyzadams’,’xyz’) from dual; adams
Rtrim(char,set) Select rtrim(‘xyzadams’,’ams’) from dual; xyzad 
Translate(char, from, to) Select translate(‘jack’,’j’ ,’b’) from dual; back 
Replace(char, searchstring,[rep string]) Select replace(‘jack and jue’ ,’j’,’bl’) from dual; black and blue 
Instr (char, m, n) Select instr (‘worldwide’,’d’) from dual; 5 
Substr (char, m, n) Select substr(‘abcdefg’,3,2) from dual; cd 
Concat (expr1, expr2) Select concat (‘Hello’,’ world’) from dual; Hello world

以下是一些其它的字符函数:
CHR和ASCII
LPAD和RPAD
TRIM
LENGTH
DECODE

SELECT LENGTH('frances') FROM dual;
SELECT vencode, 
     DECODE(venname,'frances','Francis') name 
     FROM vendor_master WHERE vencode='v001';
SELECT LPAD(‘function’,15,’=’) FROM dual;
SELECT CHR(67) FROM dual;
SELECT TRIM(9 from 9999876789999) FROM dual;

数字函数接受数字输入并返回数值结果
   函数   输入  输出
Abs(n) Select abs(-15) from dual; 15
Ceil(n) Select ceil(44.778) from dual; 45
Cos(n) Select cos(180) from dual; -.5984601 
Sin(n) Select sin(0) from dual; 0
Floor(n) Select floor(100.2) from dual; 100
Power(m,n) Select power(4,2) from dual; 16 
Mod(m,n) Select mod(10,3) from dual; 1
Round(m,n) Select round(100.256,2) from dual; 100.26 
Trunc(m,n) Select trunc(100.256,2) from dual; 100.25 
Sqrt(n) Select sqrt(4) from dual; 2 
Sign(n) Select sign(-30) from dual; -1

转换函数将值从一种数据类型转换为另一种数据类型
常用的转换函数有:
TO_CHAR
TO_DATE
TO_NUMBER
SELECT TO_DATE(‘2005-12-06’ , ‘yyyy-mm-dd’)
FROM dual;
SELECT TO_CHAR(sysdate,'YYYY"年"fmMM"月"fmDD"日" HH24:MI:SS')
FROM dual; 
SELECT TO_NUMBER('100') FROM dual;
SELECT TO_CHAR(itemrate,‘C99999’) FROM itemfile;

以下是几个用来转换空值的函数:
NVL
NVL2
NULLIF
SELECT itemdesc, NVL(re_level,0) FROM itemfile;
SELECT itemdesc, NVL2(re_level,re_level,max_level) 
FROM itemfile;
SELECT itemdesc, NULLIF(re_level,max_level) 
FROM itemfile;

分组函数基于一组行来返回结果
为每一组行返回一个值
SELECT COUNT(*) FROM itemfile;
SELECT COUNT(DISTINCT qty_hand) FROM itemfile;
SELECT COUNT(itemrate) FROM itemfile;
SELECT AVG(re_level) FROM  itemfile WHERE p_category='accessories';
SELECT SUM(itemrate*max_level) FROM itemfile;
SELECT MAX(max_level) FROM  itemfile;
SELECT MIN(max_level) FROM  itemfile;

GROUP BY子句
用于将信息划分为更小的组
每一组行返回针对该组的单个结果
HAVING子句
用于指定 GROUP BY 子句检索行的条件
SELECT p_category, MAX(itemrate) FROM itemfile GROUP BY p_category;
SELECT p_category, MAX(itemrate) FROM itemfile
GROUP BY p_category
HAVING p_category NOT IN ('accessories');

分析函数根据一组行来计算聚合值
用于计算完成聚集的累计排名、移动平均数等
分析函数为每组记录返回多个行
以下三个分析函数用于计算一个行在一组有序行中的排位,序号从1开始
ROW_NUMBER 返回连续的排位,不论值是否相等
RANK 具有相等值的行排位相同,序数随后跳跃
DENSE_RANK 具有相等值的行排位相同,序号是连续的
SELECT d.dname, e.ename, e.sal, DENSE_RANK()
  OVER (PARTITION BY e.deptno ORDER BY e.sal DESC)
  AS DENRANK
FROM emp e, dept d WHERE e.deptno = d.deptno;
SQL查询和SQL函数
SQL查询和SQL函数
SQL查询和SQL函数
我的更多文章:
<!-- 正文结束 -->

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7649720/viewspace-1115217/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2009-03-04