北大青鸟oracle学习资料TP2.ppt

1、第二章,SQL SQL 函数,Oracle 网络配置 Oracle基本用户管理 Oracle 中用于访问数据库的主要查询工具有 SQL*Plus、iSQL*Plus 和 PL/SQL Oracle 企业管理器是用于管理、诊断和调整多个数据库的工具,回顾,本章任务,使用SQL 执行数据查询 控制数据的对象权限,技能目标,了解 Oracle 数据类型 了解数据定义语言和数据控制语言 掌握 SQL 操作符和 SQL 函数,SQL 简介 2-1,SQL 是 Structured Query Language（结构化查询语言）的首字母缩写词 SQL 是数据库语言，Oracle 使用该语言存储和检索信息

2、表是主要的数据库对象，用于存储数据,通过 SQL可以实现与 Oracle 服务器的通信,发送 SQL 查询,用户,SQL 简介 2-2,SQL 支持下列类别的命令： 数据定义语言（DDL） 数据操纵语言（DML） 事务控制语言（TCL） 数据控制语言（DCL）,数据定义语言,CREATE,ALTER,DROP,数据操纵语言,INSERT,SELECT,DELETE,UPDATE,事务控制语言,COMMIT,SAVEPOINT,ROLLBACK,数据控制语言,GRANT,REVOKE,Oracle 数据类型 5-1,创建表时，必须为各个列指定数据类型 以下是 Oracle常用 数据类型的类别：,

3、数据类型,字符,数值,日期时间,Oracle 数据类型5-2,字符数据类型,CHAR,VARCHAR2,LONG,当需要固定长度的字符串时，使用 CHAR 数据类型。 CHAR 数据类型存储字母数字值。 CHAR 数据类型的列长度可以是 1 到 2000 个字节。,VARCHAR2数据类型支持可变长度字符串 VARCHAR2数据类型存储字母数字值 VARCHAR2数据类型的大小在1至4000个字节范围内,LONG 数据类型存储可变长度字符数据 LONG 数据类型最多能存储 2GB,Oracle 数据类型 5-3,数值数据类型 可以存储正数，负数，零，定点数和浮点数 浮点数最高精度为 38 位

4、数值数据类型的声明语法： NUMBER ( p, s) P表示精度，S表示小数点的位数,日期时间数据类型存储日期和时间值，包括年、月、日，小时、分钟、秒 主要的日期时间类型有： DATE - 存储日期和时间部分，精确到整个的秒 TIMESTAMP - 存储日期、时间和时区信息，秒值精确到小数点后6位,数据定义语言,数据定义语言用于改变数据库结构，包括创建、更改和删除数据库对象 用于操纵表结构的数据定义语言命令有： CREATE TABLE ALTER TABLE TRUNCATE TABLE DROP TABLE,创建表,创建表命令用途 语法： CREATE TABLE schema.tabl

5、e (column datatype,column datatype ,),SQL CREATE TABLE vendor_master(vencode VARCHAR2(5),venname VARCHAR2(20),venadd1 VARCHAR2(20),venadd2 VARCHAR2(20),venadd3 VARCHAR2(20);,表命名规则,表名应该严格遵循下列命名规则 1.表名首字符应该为字母 2.不能使用oracle保留字来为表命名 3.表名的最大长度为30个字符 4.同一用户模式下的不同表不能具有相同的名称 5.可以使用下划线，数字和字母，但不能使用空格和单引号 6.Or

6、acle中的表名（还有列名，用户名和其他对象名）不区分大小写，系统会自动转换成大写,更改表命令,用途：有以下几种情况 添加新列 更改列的数据类型或数据类型的宽度 添加或删除完整性约束条件 删除现有列,更改表,语法 ALTER TABLE MODIFY (column definition); ALTER TABLE ADD (column definition); ALTER TABLE DROP COLUMN column;,SQLALTER TABLE vendor_master MODIFY(venname varchar2(25);,SQLALTER TABLE vendor_mast

7、er ADD (tel_no varchar2(12),tngst_no_number(12);,SQLALTER TABLE vendor_master DROP COLUMN tngst_no,删除表,用途 语法 DROP TABLE,SQLDROP TABLE vendor_master;,数据控制语言,数据控制语言为用户提供权限控制命令 用于权限控制的命令有： GRANT 授予权限 REVOKE 撤销已授予的权限,SQL GRANT SELECT, UPDATE ON order_master TO MARTIN;,SQL GRANT UPDATE(qty_hand, re_level

8、) 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语句中使用算术表达式，算术表达式由数值数据类型的列名、数值常量和连接它们的算术操作符组成 算术操作符包括加(+)、减(

9、-)、乘(*)、除(/),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

10、 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的订

11、单信息,逻辑操作符用于组合多个计较运算的结果以生成一个或真或假的结果。 逻辑操作符包括与(AND)、或(OR)和非(NOT)。,集合操作符,集合操作符将两个查询的结果组合成一个结果,集合操作符,UNION,UNION ALL,INTERSECT,MINUS,UNION操作符返回两个查询选定都所有不重复都行。,SQLSELECT orderno FROM order_master UNION SELECT orderno FROM order_detail;,UNION ALL操作符合并两个查询选定的所有行，包括重复的行,SQLSELECT orderno FROM order_master U

12、NION ALL SELECT orderno FROM order_detail;,SQLSELECT qty_hand,max_level FROM itemfile UNION SELECT qty_ord,qty_deld FROM order_detail ORDER BY 2;,集合操作符,集合操作符将两个查询的结果组合成一个结果,集合操作符,UNION,UNION ALL,INTERSECT,MINUS,INTERSECT 操作符只返回两个查询都有的行,SQLSELECT orderno FROM order_master INTERSECT SELECT orderno FRO

13、M order_detail;,MINUS操作符只返回由第一个查询选定但是没有被第二个查询选定的行，也就是在第一个查询结果中排除在第二个查询结果中出现的行,SQLSELECT orderno FROM order_master MINUS SELECT orderno FROM order_detail;,连接操作符,连接操作符用于将多个字符串或数据值合并成一个字符串,SQL SELECT (供应商|venname| 的地址是 |venadd1| |venadd2 | |venadd3) address FROM vendor_master WHERE vencode=V001;,通过使用连接

14、操作符可以将表中 的多个列合并成逻辑上的一行列,操作符的优先级,SQL 操作符的优先级从高到低的顺序是： 算术操作符 -最高优先级 连接操作符 比较操作符 NOT 逻辑操作符 AND 逻辑操作符 OR 逻辑操作符 -最低优先级,SQL 函数,Oracle 提供一系列用于执行特定操作的函数 SQL 函数带有一个或多个参数并返回一个值 以下是SQL函数的分类：,SQL 函数,标量函数,分组函数,标量函数分类,标量（单行）函数对于从表中查询的每一行只返回一个值 可以出现在 SELECT 子句中和 WHERE 子句中 标量函数可以大致划分为： 日期函数 数字函数 字符函数 转换函数 其他函数,日期函数

15、,日期函数对日期值进行运算，并生成日期数据类型或数值类型的结果 日期函数包括： ADD_MONTHS MONTHS_BETWEEN LAST_DAY ROUND NEXT_DAY TRUNC EXTRACT,日期函数,ADD_MONTHS 函数返回给指定的日期加上指定的月数后的日期值。 格式为ADD_MONTHS(d,n),其中d是日期，n表示月数,SQLSELECT del_date,ADD_MONTHS(del_date,2) FROM order_master;,日期函数,MONTHS_BETWEEN 函数返回两个日期之间的月数 格式为MONTHS_BETWEEN（d1，d2） 详细：d

16、1和d2是日期，输出结果为一个数值。如果d1晚于d2，则结果为正数；否则为负数。如果d1和d2为某月中的同一天或月份的最后一天，则结果始终为整数；否则oracle将根据一个月31天来计算结果的小数部分，并考虑d1和d2时间部分的差。 LAST_DAY 函数返回指定日期当月的最后一天的日期值 格式为LAST_DAY(d),SQLSELECT SYSDATE,LAST_DAY(SYSDATE) FROM dual;,日期函数,ROUND 函数返回日期值，此日期四舍五入为格式模型指定的单位。 格式为ROUND(d，fmt) 详细：d是日期，fmt是格式模型。fmt是一个可选项，日期默认舍入为最靠近的

17、那一天。如果指定格式为年”YEAR”,则舍入到年的开始，即1月1日；如果格式为月”MONTH”,则舍入到月的第一日；如果格式为周”DAY”,则舍入到最靠近的星期日。,SQLSELECT del_date,ROUND(del_date,YEAR) FROM order_master WHERE vencoe=V001;,SQLSELECT ROUND(DATE2005-5-10,MONTH),ROUND(DATE2005-6-16,MONTH) FROM dual;,日期函数,NEXT_DAY 函数返回指定的下一个星期几的日期。 格式为NEXT_DAY(d,day) 详细：d是日期，day指周内

18、任何一天。,SQLSELECT NEXT_DAY(SYSDATE,星期二) FROM dual;,日期函数,TRUNC 函数将指定日期截断为由格式模型指定的单位的日期，与ROUND函数不同的是它只舍不入 格式为TRUNC(d,fmt)它使用与ROUND函数相同的格式模型。如果不指定fmt，则日期被截断为天 详细：d是日期时间表达式，fmt是要提取的部分的格式。格式的取值可以是YEAR,MONTH,DAY,HOUR,MINUTE,SECOND,注意此处的格式不用使用单引号。,SQLSELECT TRUNC(sysdate,YEAR) FROM dual;,SQLSELECT TRUNC(sysd

19、ate,DAY) FROM dual;,SQLSELECT TRUNC(sysdate) FROM dual;,日期函数,EXTRACT 函数提取日期时间类型中都特定部分。 格式为EXTRACT(fmt FROM d) 详细：d是日期时间表达式，fmt是要提取的部分的格式。格式的取值可以是YEAR,MONTH,DAY,HOUR,MINUTE,SECOND,注意此处的格式不用使用单引号。,SQLSELECT EXTRACT (YEAR FROM SYSDATE) FROM dual;,字符函数 2-1,字符函数,字符函数接受字符输入并返回字符或数值,字符函数 2-2,SQL SELECT CHR

20、(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;,数字函数,数

21、字函数接受数字输入并返回数值结果,数字函数,转换函数,转换函数将值从一种数据类型转换为另一种数据类型 常用的转换函数有： 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 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;,分组函数,分组函数基于一组行来返回结果 为每一组行