DDL、DML和函数.ppt

第二章 DDL、DML和函数 本章主要目标 q了解 Oracle 数据类型 q了解数据定义语言和数据操纵语言 q了解事务控制语言和数据控制语言 q掌握 SQL 操作符和 SQL 函数 SQL 简介 2-1 qSQL 是 Structured Query Language（结构化查 询语言）的首字母缩写词 qSQL 是数据库语言，Oracle 使用该语言存储和 检索信息 q表是主要的数据库对象，用于存储数据 q通过 SQL可以实现与 Oracle 服务器的通信 SELECT ename FROM Emp; 发送 SQL 查询 Oracle 服务器 ename BLAKE SMITH ALLEN DAVID MARTIN 发送命令输出到用户端 用户 SQL 简介 2-2 qSQL 支持下列类别的命令： q数据定义语言（DDL） q数据操纵语言（DML） q事务控制语言（TCL） q数据控制语言（DCL） 数据定义语言 CREATEALTERDROP 数据操纵语言 INSERTSELECTDELETEUPDATE 事务控制语言 COMMIT SAVEPOINT ROLLBACK 数据控制语言 GRANTREVOKE Oracle 数据类型 5-1 q创建表时，必须为各个列指定数据类型 q以下是 Oracle 数据类型的类别： 数据类型 字符数值日期时间RAW/LONG RAWLOB Oracle 数据类型5-2 字符数据类型 CHARVARCHAR2LONG q 当需要固定长度的字符串时，使用 CHAR 数据类型。 q CHAR 数据类型存储字母数字值。 q CHAR 数据类型的列长度可以是 1 到 2000 个字节。 q VARCHAR2数据类型支持可变长度字符串 q VARCHAR2数据类型存储字母数字值 q VARCHAR2数据类型的大小在1至4000个字节范围内 q LONG 数据类型存储可变长度字符数据 q LONG 数据类型最多能存储 2GB Oracle 数据类型 5-3 q数值数据类型 q可以存储整数、浮点数和实数 q最高精度为 38 位 q数值数据类型的声明语法： qNUMBER ( p, s) qP表示精度，S表示小数点的位数 q日期时间数据类型存储日期和时间值，包括年、 月、日，小时、分钟、秒 q主要的日期时间类型有： qDATE - 存储日期和时间部分，精确到整个的秒 qTIMESTAMP - 存储日期、时间和时区信息，秒 值精确到小数点后6位 Oracle 数据类型 5-4 qRAW 数据类型用于存储二进制数据 qRAW 数据类型最多能存储 2000 字节 qLONG RAW 数据类型用于存储可变长度的二进 制数据 qLONG RAW 数据类型最多能存储 2 GB LOB qLOB 称为“大对象”数据类型，可以存储多达 4GB 的非结构化信息，例如声音剪辑和视频文 件等 qLOB 数据类型允许对数据进行高效、随机、分 段的访问 BLOBCLOBBFILE CLOB 即 Character LOB（字符 LOB），它能够存储大量字符数据 BLOB 即 Binary LOB（二进制 LOB），可以存储较大的二进制对 象，如图形、视频剪辑和声音文件 BFILE 即 Binary File（二进制文件 ），它用于将二进制数据存储在数 据库外部的操作系统文件中 Oracle 数据类型 5-5 qOracle 中伪列就像一个表列，但是它并没有存 储在表中 q伪列可以从表中查询，但不能插入、更新和删除 它们的值 q常用的伪列有ROWID和ROWNUM ROWID 是表中行的存储地址，该地址可以唯一地标识数据库中 的一行，可以使用 ROWID 伪列快速地定位表中的一行 ROWNUM 是查询返回的结果集中行的序号，可以使用它来限 制查询返回的行数 数据定义语言 q数据定义语言用于改变数据库结构，包括创建、 更改和删除数据库对象 q用于操纵表结构的数据定义语言命令有： qCREATE TABLE qALTER TABLE qTRUNCATE TABLE qDROP TABLE 数据操纵语言 q数据操纵语言用于检索、插入和修改数据 q数据操纵语言是最常见的SQL命令 q数据操纵语言命令包括： qSELECT qINSERT qUPDATE qDELETE DML SELECT 命令 2-1 q利用现有的表创建表 q语法： 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; DML SELECT 命令 2-2 SQL SELECT DISTINCT vencode FROM vendor_master; q选择无重复的行 q在SELECT子句，使用DISTINCT关键字 q使用列别名 q为列表达式提供不同的名称 q该别名指定了列标题 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; DML INSERT 命令 2-1 q插入日期类型的值 q日期数据类型的默认格式为“DD-MON-RR” q使用日期的默认格式 q使用TO_DATE函数转换 INSERT INTO my_table (date_col) VALUES (TO_DATE(2005-10-18, YYYY-MM-DD); INSERT INTO order_master VALUES(o001, 12-5月-05, V002, c, 25-5月-05); DML INSERT 命令 2-2 q插入来自其它表中的记录 q语法： 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; 事务控制语言 q事务是最小的工作单元，作为一个整体进行工作 q保证事务的整体成功或失败，称为事务控制 q用于事务控制的语句有： qCOMMIT - 提交并结束事务处理 qROLLBACK - 撤销事务中已完成的工作 qSAVEPOINT 标记事务中可以回滚的点 SQL UPDATE order_master SET del_date = 30-8月-05 WHERE orderno SAVEPOINT mark1; SQL DELETE FROM order_master WHERE orderno = o002; SQL SAVEPOINT mark2; SQL ROLLBACK TO SAVEPOINT mark1; SQL COMMIT; 数据控制语言 q数据控制语言为用户提供权限控制命令 q用于权限控制的命令有： qGRANT 授予权限 qREVOKE 撤销已授予的权限 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; SQL 操作符 SQL 操作符 集合操作符 逻辑操作符 比较操作符 算术操作符连接操作符 qOracle 支持的 SQL 操作符分类如下： 算术操作符 q算术操作符用于执行数值计算 q可以在SQL语句中使用算术表达式，算术表达式 由数值数据类型的列名、数值常量和连接它们的 算术操作符组成 q算术操作符包括加(+)、减(-)、乘(*)、除(/) 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; 比较操作符 q比较操作符用于比较两个表达式的值 q比较操作符包括 =、!=、=、 BETWEENAND、IN、LIKE 和 IS NULL等 SQL SELECT itemdesc, re_level FROM itemfile WHERE qty_hand 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; 逻辑操作符 SQL SELECT * FROM order_master WHERE odate 10-5月-05 AND del_date SELECT orderno FROM order_master INTERSECT SELECT orderno FROM order_detail; SQL SELECT orderno FROM order_master MINUS SELECT orderno FROM order_detail; MINUS 操作符返回从第一个查询结果中排除第二个查 询中出现的行。 连接操作符 q连接操作符用于将多个字符串或数据值合并成一 个字符串 SQL SELECT (venname| 的地址是 |venadd1| |venadd2 | |venadd3) address FROM vendor_master WHERE vencode=V001; 通过使用连接操作符可以将表中 的多个列合并成逻辑上的一行列 操作符的优先级 SQL 操作符的优先级从高到低的顺序是： q算术操作符 -最高优先级 q连接操作符 q比较操作符 qNOT 逻辑操作符 qAND 逻辑操作符 qOR 逻辑操作符 -最低优先级 SQL 函数 qOracle 提供一系列用于执行特定操作的函数 qSQL 函数带有一个或多个参数并返回一个值 q以下是SQL函数的分类： SQL 函数 单行函数 分析函数分组函数 单行函数分类 q单行函数对于从表中查询的每一行只返回一个值 q可以出现在 SELECT 子句中和 WHERE 子句中 q 单行函数可以大致划分为： q日期函数 q数字函数 q字符函数 q转换函数 q其他函数 日期函数 q日期函数对日期值进行运算，并生成日期数据类 型或数值类型的结果 q日期函数包括： qADD_MONTHS qMONTHS_BETWEEN qLAST_DAY qROUND qNEXT_DAY qTRUNC qEXTRACT 字符函数 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 字符函数 字符函数接受字符输入并返回字符或数值 字符函数 2-2 SQL SELECT CHR(67) FROM dual; q以下是一些其它的字符函数： qCHR和ASCII qLPAD和RPAD qTRIM qLENGTH qDECODE SQL SELECT LPAD(function,15,=) FROM dual; SQL SELECT TRIM(9 from 9999876789999) FROM dual; SQL SELECT LENGTH(frances) FROM dual; SQL SELECT vencode, DECODE(venname,frances,Francis) name FROM vendor_master WHERE vencode=v001; 数字函数 q数字函数接受数字输入并返回数值结果 函数 输入 输出 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 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(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 数字函数 转换函数 q转换函数将值从一种数据类型转换为另一种数据 类型 q常用的转换函数有： qTO_CHAR qTO_DATE qTO_NUMBER SELECT TO_CHAR(sysdate,YYYY“年“fmMM“月“fmDD“日“ HH24:MI:SS) FROM dual; SELECT TO_CHAR(itemrate,C99999) FROM itemfile; SELECT TO_DATE(2005-12-06 , yyyy-mm-dd) FROM dual; SELECT TO_NUMBER(100) FROM dual; 其它函数 q以下是几个用来转换空值的函数： qNVL qNVL2 qNULLIF 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; 分组函数 q分组函数基于一组行来返回结果 q为每一组行返回一个值 AVGMINMAXSUMCOUNT 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; 分组函数 GROUP BY和HAVING子句 qGROUP BY子句 q用于将信息划分为更小的组 q每一组行返回针对该组的单个结果 qHAVING子句 q用于指定 GROUP BY 子句检索行的条件 SELECT p_category, MAX(itemrate) FROM itemfile GROUP BY p_category; SELECT p_category