ACCP V4.0 第二章 SQL 查询和 SQL 函数

ACCP V4.0  Oracle 服务器由 Oracle 数据库和 Oracle 实例组 成  Oracle 实例由系统全局区内存结构和用于管理数 据库的后台进程组成  Oracle 中用于访问数据库的主要查询工具有 SQL*Plus 、 iSQL*Plus 和 PL/SQL  Oracle 企业管理器是用于管理、诊断和调整多个 数据库的工具  Oracle 中的 SYSTEM 用户和 SYS 用户具有管理 权限，而 SCOTT 用户只有基本的权限 回顾

ACCP V4.0 3 目标  了解 Oracle 数据类型  了解数据定义语言和数据操纵语言  了解事务控制语言和数据控制语言  掌握 SQL 操作符和 SQL 函数

ACCP V4.0 4 SQL 简介 2-1  SQL 是 Structured Query Language （结构化查 询语言）的首字母缩写词  SQL 是数据库语言， Oracle 使用该语言存储和 检索信息  表是主要的数据库对象，用于存储数据  通过 SQL 可以实现与 Oracle 服务器的通信 SELECT ename FROM Emp ; 发送 SQL 查询 Oracle 服务器 ename BLAKE SMITH ALLEN DAVID MARTIN 发送命令输出到用户端 用户

ACCP V4.0 5 SQL 简介 2-2  SQL 支持下列类别的命令：  数据定义语言（ DDL ）  数据操纵语言（ DML ）  事务控制语言（ TCL ）  数据控制语言（ DCL ） 数据定义语言 CREATEALTERDROP 数据操纵语言 INSERTSELECTDELETE UPDATE 事务控制语言 COMMIT SAVEPOINT ROLLBACK 数据控制语言 GRANT REVOKE

ACCP V4.0 6 Oracle 数据类型 5-1  创建表时，必须为各个列指定数据类型  以下是 Oracle 数据类型的类别： 数据类型 字符数值日期时间 RAW/LONG RAW LOB

ACCP V4.0 7 Oracle 数据类型 5-2 字符数据类型 CHAR VARCHAR2 LONG  当需要固定长度的字符串时，使用 CHAR 数据类型。  CHAR 数据类型存储字母数字值。  CHAR 数据类型的列长度可以是 1 到 2000 个字节。  VARCHAR2 数据类型支持可变长度字符串  VARCHAR2 数据类型存储字母数字值  VARCHAR2 数据类型的大小在 1 至 4000 个字节范围内  LONG 数据类型存储可变长度字符数据  LONG 数据类型最多能存储 2GB

ACCP V4.0 8 Oracle 数据类型 5-3  数值数据类型  可以存储整数、浮点数和实数  最高精度为 38 位  数值数据类型的声明语法：  NUMBER [( p[, s])]  P 表示精度， S 表示小数点的位数  日期时间数据类型存储日期和时间值，包括年、 月、日，小时、分钟、秒  主要的日期时间类型有：  DATE - 存储日期和时间部分，精确到整个的秒  TIMESTAMP - 存储日期、时间和时区信息，秒值精 确到小数点后 6 位

ACCP V4.0 9 Oracle 数据类型 5-4  RAW 数据类型用于存储二进制数据  RAW 数据类型最多能存储 2000 字节  LONG RAW 数据类型用于存储可变长度的二进 制数据  LONG RAW 数据类型最多能存储 2 GB LOB  LOB 称为 “ 大对象 ” 数据类型，可以存储多达 4GB 的非结构化信息，例如声音剪辑和视频文 件等  LOB 数据类型允许对数据进行高效、随机、分 段的访问 BLOB CLOB BFILE CLOB 即 Character LOB （字符 LOB ），它能够存储大量字符数据 BLOB 即 Binary LOB （二进制 LOB ），可以存储较大的二进制对 象，如图形、视频剪辑和声音文件 BFILE 即 Binary File （二进制文 件），它用于将二进制数据存储在 数据库外部的操作系统文件中

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

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

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

ACCP V DML – SELECT 命令 2-1  利用现有的表创建表  语法： CREATE TABLE AS SELECT column_names FROM ; SQL> CREATE TABLE newitemfile AS SELECT * FROM itemfile; SQL> CREATE TABLE newitemfile1 AS SELECT itemcode, itemdesc, qty_hand FROM itemfile; SQL> CREATE TABLE newitemfile2 AS SELECT * FROM itemfile WHERE 1 = 2;

ACCP V DML – SELECT 命令 2-2 SQL> SELECT DISTINCT vencode FROM vendor_master;  选择无重复的行  在 SELECT 子句，使用 DISTINCT 关键字  使用列别名  为列表达式提供不同的名称  该别名指定了列标题 SQL> SELECT itemcode, itemdesc, max_level, max_level*2 AS NEW_MAXLEVEL FROM itemfile; SQL> SELECT itemcode,itemdesc, max_level, max_level* 2 “New Maximum Level” FROM itemfile;

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

ACCP V DML – INSERT 命令 2-2  插入来自其它表中的记录  语法： INSERT INTO [(cloumn_list)] SELECT column_names FROM ; SQL> INSERT INTO newvendor_master SELECT * FROM vendor_master; SQL> INSERT INTO newvendor_master(vencode,venname) SELECT vencode, venname FROM vendor_master;

ACCP V 事务控制语言  事务是最小的工作单元，作为一个整体进行工作  保证事务的整体成功或失败，称为事务控制  用于事务控制的语句有：  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;

ACCP V 数据控制语言  数据控制语言为用户提供权限控制命令  用于权限控制的命令有：  GRANT 授予权限  REVOKE 撤销已授予的权限 SQL> GRANT SELECT, UPDATE ON order_master TO MARTIN; SQL> GRANT UPDATE(qty_hand, re_level) ON itemfile TO MARTIN; SQL> GRANT SELECT ON vendor_master TO accounts WITH GRANT OPTION; SQL> REVOKE SELECT, UPDATE ON order_master FROM MARTIN;

ACCP V SQL 操作符 集合操作符 逻辑操作符 比较操作符 算术操作符 连接操作符  Oracle 支持的 SQL 操作符分类如下：

ACCP V 算术操作符  算术操作符用于执行数值计算  可以在 SQL 语句中使用算术表达式，算术表达式 由数值数据类型的列名、数值常量和连接它们的 算术操作符组成  算术操作符包括加 (+) 、减 (-) 、乘 (*) 、除 (/) SQL> SELECT itemdesc, max_level - qty_hand avble_limit FROM itemfile WHERE p_category='spares'; SQL > SELECT itemdesc, itemrate*(max_level - qty_hand) FROM itemfile WHERE p_category='spares';

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

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

ACCP V 集合操作符  集合操作符将两个查询的结果组合成一个结果 集合操作符 UNION UNION ALL INTERSECT MINUS INTERSECT 操作符只返回两个查询的公共行。 SQL> SELECT orderno FROM order_master INTERSECT SELECT orderno FROM order_detail; SQL> SELECT orderno FROM order_master MINUS SELECT orderno FROM order_detail; MINUS 操作符返回从第一个查询结果中排除第二个查 询中出现的行。

ACCP V 连接操作符  连接操作符用于将多个字符串或数据值合并成一 个字符串 SQL> SELECT (venname|| ' 的地址是 ' ||venadd1||' '||venadd2 ||' '||venadd3) address FROM vendor_master WHERE vencode='V001'; 通过使用连接操作符可以将表中 的多个列合并成逻辑上的一行列

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

ACCP V SQL 函数  Oracle 提供一系列用于执行特定操作的函数  SQL 函数带有一个或多个参数并返回一个值  以下是 SQL 函数的分类： SQL 函数 单行函数 分析函数 分组函数

ACCP V 单行函数分类  单行函数对于从表中查询的每一行只返回一个值  可以出现在 SELECT 子句中和 WHERE 子句中  单行函数可以大致划分为：  日期函数  数字函数  字符函数  转换函数  其他函数

ACCP V 日期函数  日期函数对日期值进行运算，并生成日期数据类 型或数值类型的结果  日期函数包括：  ADD_MONTHS  MONTHS_BETWEEN  LAST_DAY  ROUND  NEXT_DAY  TRUNC  EXTRACT

ACCP V 字符函数 2-1 函数 输入 输出 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 字符函数  字符函数接受字符输入并返回字符或数值

ACCP V 字符函数 2-2 SQL> SELECT CHR(67) FROM dual;  以下是一些其它的字符函数：  CHR 和 ASCII  LPAD 和 RPAD  TRIM  LENGTH  DECODE SQL> SELECT LPAD(‘function’,15,’=’) FROM dual; SQL> SELECT TRIM(9 from ) FROM dual; SQL> SELECT LENGTH('frances') FROM dual; SQL> SELECT vencode, DECODE(venname,'frances','Francis') name FROM vendor_master WHERE vencode='v001';

ACCP V 数字函数  数字函数接受数字输入并返回数值结果 函数 输入 输出 Abs(n)Select abs(-15) from dual;15 Ceil(n)Select ceil(44.778) from dual;45 Cos(n)Select cos(180) from dual; Cosh(n)Select cosh(0) from dual; 1 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( ,2) from dual; Trunc(m,n)Select trunc( ,2) from dual; Sqrt(n)Select sqrt(4) from dual;2 Sign(n)Select sign(-30) from dual; 数字函数

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

ACCP V 其它函数  以下是几个用来转换空值的函数：  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;

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

ACCP V GROUP BY 和 HAVING 子句  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');

ACCP V 分析函数 2-1  分析函数根据一组行来计算聚合值  用于计算完成聚集的累计排名、移动平均数等  分析函数为每组记录返回多个行 分析函数 DENSE_RANK RANK ROW_NUMBER

ACCP V 分析函数 2-2  以下三个分析函数用于计算一个行在一组有序行中的排位， 序号从 1 开始  ROW_NUMBER 返回连续的排位，不论值是否相等  RANK 具有相等值的行排位相同，序数随后跳跃  DENSE_RANK 具有相等值的行排位相同，序号是连 续的 SELECT ename, job, deptno, sal, ROW_NUMBER() OVER (ORDER BY sal DESC) AS SAL_RANK FROM SCOTT.EMP; SELECT deptno, ename, sal, comm, RANK() OVER (PARTITION BY deptno ORDER BY sal DESC, comm) RANK FROM emp; 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;

ACCP V 总结  SQL 是通用的数据库语言  SQL 命令可分为数据定义语言、数据操纵语言、 事务控制语言和数据控制语言  Oracle 支持的数据类型包括字符、数值、日期时 间、 RAW 和 LOB 等  SQL 支持的操作符包括算术、比较、逻辑、集合 和连接操作符  SQL 函数可大致分为单行函数、聚合函数和分析 函数