《Oracle数据库应用与开发》第02章[SQL命令与函数]理论课.ppt

SQL命令与函数,Oracle数据库应用与开发第02章,目标,了解 Oracle 数据类型 了解数据定义语言和数据操纵语言 了解事务控制语言和数据控制语言 掌握 SQL 操作符和 SQL 函数,SQL 简介 2-1,SQL 是 Structured Query Language（结构化查询语言）的首字母缩写词 SQL 是数据库语言，Oracle 使用该语言存储和检索信息 表是主要的数据库对象，用于存储数据,通过 SQL可以实现与 Oracle 服务器的通信,SQL 简介 2-2,SQL 支持下列类别的命令： 数据定义语言（DDL） 数据操纵语言（DML） 事务控制语言（TCL） 数据控制语言（DCL）,数据定义语言,CREATE,ALTER,DROP,数据操纵语言,INSERT,SELECT,DELETE,UPDATE,事务控制语言,COMMIT,SAVEPOINT,ROLLBACK,数据控制语言,GRANT,REVOKE,Oracle 数据类型 5-1,创建表时，必须为各个列指定数据类型 以下是 Oracle 数据类型的类别：,数据类型,字符,数值,日期时间,RAW/LONG RAW,LOB,Oracle 数据类型5-2,字符数据类型,CHAR,VARCHAR2,LONG,当需要固定长度的字符串时，使用 CHAR 数据类型。 CHAR 数据类型存储字母数字值。 CHAR 数据类型的列长度可以是 1 到 2000 个字节。,VARCHAR2数据类型支持可变长度字符串 VARCHAR2数据类型存储字母数字值 VARCHAR2数据类型的大小在1至4000个字节范围内,LONG 数据类型存储可变长度字符数据 LONG 数据类型最多能存储 2GB 一张表中只能出来一次 不能在Long类型的列上建立主键，唯一约束，索引 不能用于存储过程的参数类型,Oracle 数据类型 5-3,数值数据类型 可以存储整数、浮点数和实数 最高精度为 38 位 数值数据类型的声明语法： NUMBER ( p, s) P表示精度，S表示小数点的位数,日期时间数据类型存储日期和时间值，包括年、月、日，小时、分钟、秒 主要的日期时间类型有： DATE - 存储日期和时间部分，精确到整个的秒 TIMESTAMP - 存储日期、时间和时区信息，秒值精确到小数点后6位,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（二进制文件），它用于将二进制数据存储在数据库外部的操作系统文件中,Oracle 数据类型 5-5,Oracle 中伪列就像一个表列，但是它并没有存储在表中 伪列可以从表中查询，但不能插入、更新和删除它们的值 常用的伪列有ROWID和ROWNUM,ROWID 是表中行的存储地址，该地址可以唯一地标识数据库中的一行，可以使用 ROWID 伪列快速地定位表中的一行,ROWNUM 是查询返回的结果集中行的序号，可以使用它来限制查询返回的行数,数据定义语言,数据定义语言用于改变数据库结构，包括创建、更改和删除数据库对象 用于操纵表结构的数据定义语言命令有： CREATE TABLE ALTER TABLE TRUNCATE TABLE DROP TABLE,数据操纵语言,数据操纵语言用于检索、插入和修改数据 数据操纵语言是最常见的SQL命令 数据操纵语言命令包括： SELECT INSERT UPDATE DELETE,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;,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;,DML INSERT 命令 2-1,插入日期类型的值 日期数据类型的默认格式为“DD-MON-RR” 使用日期的默认格式 使用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,插入来自其它表中的记录 语法： 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;,事务控制语言,事务是最小的工作单元，作为一个整体进行工作 保证事务的整体成功或失败，称为事务控制 用于事务控制的语句有： COMMIT - 提交并结束事务处理 ROLLBACK - 撤销事务中已完成的工作 SAVEPOINT 标记事务中可以回滚的点,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;,数据控制语言,数据控制语言为用户提供权限控制命令 用于权限控制的命令有： 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;,SQL 操作符,SQL 操作符,集合操作符,逻辑操作符,比较操作符,算术操作符,连接操作符,Oracle 支持的 SQL 操作符分类如下：,算术操作符,算术操作符用于执行数值计算 可以在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;,比较操作符,比较操作符用于比较两个表达式的值 比较操作符包括 =、!=、=、BETWEENAND、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;,逻辑操作符,SQL SELECT * FROM order_master WHERE odate 10-5月-05 AND del_date 26-5月-05;,显示 2005-5月-10 至 2005-5月-26的订单信息,逻辑操作符用于组合多个计较运算的结果以生成一个或真或假的结果。 逻辑操作符包括与(AND)、或(OR)和非(NOT)。,集合操作符,集合操作符将两个查询的结果组合成一个结果,集合操作符,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 操作符返回从第一个查询结果中排除第二个查 询中出现的行。,连接操作符,连接操作符用于将多个字符串或数据值合并成一个字符串,SQL SELECT (venname| 的地址是 |venadd1| |venadd2 | |venadd3) address FROM vendor_master WHERE vencode=V001;,通过使用连接操作符可以将表中 的多个列合并成逻辑上的一行列,操作符的优先级,SQL 操作符的优先级从高到低的顺序是： 算术操作符 -最高优先级 连接操作符 比较操作符 NOT 逻辑操作符 AND 逻辑操作符 OR 逻辑操作符 -最低优先级,SQL 函数,Oracle 提供一系列用于执行特定操作的函数 SQL 函数带有一个或多个参数并返回一个值 以下是SQL函数的分类：,SQL 函数,单行函数,分析函数,分组函数,单行函数分类,单行函数对于从表中查询的每一行只返回一个值 可以出现在 SELECT 子句中和 WHERE 子句中 单行函数可以大致划分为： 日期函数 数字函数 字符函数 转换函数 其他函数,日期函数,日期函数对日期值进行运算，并生成日期数据类型或数值类型的结果 日期函数包括： ADD_MONTHS MONTHS_BETWEEN LAST_DAY ROUND(对日期四舍五入) NEXT_DAY(最近一星期是几号) TRUNC（日期取整） EXTRACT（提取日期某个部分）,字符函数 2-1,字符函数,字符函数接受字符输入并返回字符或数值,字符函数 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 9999876789999) FROM dual;,SQL SELECT LENGTH(frances) FROM dual;,SQL SELECT vencode, DECODE(venname,frances,Francis) name FROM vendor_master WHERE vencode=v001;,数字函数,数字函数接受数字输入并返回数值结果,数字函数,转换函数,转换函数将值从一种数据类型转换为另一种数据类型 常用的转换函数有： 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(2005-12-06 , yyyy-mm-dd) FROM dual;,SELECT TO_NUMBER(100) FROM dual;,其它函数,以下是几个用来转换空值的函数： NVL NVL2（不是null ,返回 ex2 ,是空 返回 ex3） NULLIF（ex1=ex2 返回 null ，否则返回 ex1 ）,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;,分组函数,分组函数基于一组行来返回结果 为每一组行返回一个值,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;,分组函数,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 (accesso