北大青鸟oracle学习资料TP2

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

2、库对象，用于存储数据通过 SQL可以实现与 Oracle 服务器的通信SELECT ename FROM Emp;发送 SQL 查询Oracle 服务器enameBLAKESMITH ALLEN DAVID MARTIN发送命令输出到用户端用户6SQL 简介 2-2SQL 支持下列类别的命令：数据定义语言（DDL）数据操纵语言（DML）事务控制语言（TCL）数据控制语言（DCL）数据定义语言 CREATEALTERDROP数据操纵语言 INSERTSELECTDELETEUPDATE事务控制语言COMMITSAVEPOINTROLLBACK数据控制语言 GRANTREVOKE7Oracle 数

3、据类型 5-1创建表时，必须为各个列指定数据类型以下是 Oracle常用 数据类型的类别：数据类型字符数值日期时间8Oracle 数据类型5-2字符数据类型CHARVARCHAR2LONG当需要固定长度的字符串时，使用 CHAR 数据类型。CHAR 数据类型存储字母数字值。CHAR 数据类型的列长度可以是 1 到 2000 个字节。VARCHAR2数据类型支持可变长度字符串VARCHAR2数据类型存储字母数字值VARCHAR2数据类型的大小在1至4000个字节范围内LONG 数据类型存储可变长度字符数据LONG 数据类型最多能存储 2GB9Oracle 数据类型 5-3数值数据类型可以存储正数

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

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

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

8、file TO MARTIN;SQL GRANT SELECT ON vendor_master TO accounts WITH GRANT OPTION;SQL REVOKE SELECT, UPDATE ON order_master FROM MARTIN;17SQL 操作符SQL 操作符集合操作符逻辑操作符比较操作符算术操作符连接操作符Oracle 支持的 SQL 操作符分类如下：18算术操作符算术操作符用于执行数值计算可以在SQL语句中使用算术表达式，算术表达式由数值数据类型的列名、数值常量和连接它们的算术操作符组成算术操作符包括加(+)、减(-)、乘(*)、除(/)SQL SEL

9、ECT 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;19比较操作符 比较操作符用于比较两个表达式的值比较操作符包括 =、!=、=、BETWEENAND、IN、LIKE 和 IS NULL等SQL SELECT itemdesc, re_level FROM itemfile WHERE qty_ha

10、nd 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;20逻辑操作符SQL SELECT * FROM order_master WHERE odate 10-5月-05 AND del_date SELECT orderno FROM order_master UNION SELECT orderno FROM order_detail;UNION

11、ALL操作符合并两个查询选定的所有行，包括重复的行SQLSELECT orderno FROM order_master UNION 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;22集合操作符集合操作符将两个查询的结果组合成一个结果集合操作符UNIONUNION ALLINTERSECTMINUSINTERSECT 操作符只返回两个查询都有的行SQLSELECT

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

13、dress FROM vendor_master WHERE vencode=V001;通过使用连接操作符可以将表中的多个列合并成逻辑上的一行列24操作符的优先级SQL 操作符的优先级从高到低的顺序是：算术操作符 -最高优先级连接操作符比较操作符NOT 逻辑操作符AND 逻辑操作符OR 逻辑操作符 -最低优先级 25SQL 函数Oracle 提供一系列用于执行特定操作的函数SQL 函数带有一个或多个参数并返回一个值以下是SQL函数的分类：SQL 函数标量函数分组函数26标量函数分类标量（单行）函数对于从表中查询的每一行只返回一个值可以出现在 SELECT 子句中和 WHERE 子句中 标量函数

14、可以大致划分为：日期函数数字函数字符函数转换函数其他函数27日期函数日期函数对日期值进行运算，并生成日期数据类型或数值类型的结果日期函数包括：ADD_MONTHSMONTHS_BETWEENLAST_DAYROUNDNEXT_DAYTRUNCEXTRACT日期函数ADD_MONTHS函数返回给指定的日期加上指定的月数后的日期值。格式为ADD_MONTHS(d,n),其中d是日期，n表示月数28SQLSELECT del_date,ADD_MONTHS(del_date,2) FROM order_master;日期函数MONTHS_BETWEEN函数返回两个日期之间的月数格式为MONTHS_B

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

16、期默认舍入为最靠近的那一天。如果指定格式为年”YEAR”,则舍入到年的开始，即1月1日；如果格式为月”MONTH”,则舍入到月的第一日；如果格式为周”DAY”,则舍入到最靠近的星期日。30SQLSELECT 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是日期，d

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

18、date,DAY) FROM dual;SQLSELECT TRUNC(sysdate) FROM dual;日期函数EXTRACT函数提取日期时间类型中都特定部分。格式为EXTRACT(fmt FROM d)详细：d是日期时间表达式，fmt是要提取的部分的格式。格式的取值可以是YEAR,MONTH,DAY,HOUR,MINUTE,SECOND,注意此处的格式不用使用单引号。33SQLSELECT EXTRACT (YEAR FROM SYSDATE) FROM dual;34字符函数 2-1 函数 输入 输出Initcap(char) Select initcap(hello) from d

19、ual;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; adamsRtrim(char,set) Select rtrim(xyzadams,ams) from dual; xyzad Translate(char, from, to) Select translate(jack,j ,b) from dual; back Replace(char

20、, 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字符函数字符函数接受字符输入并返回字符或

21、数值35字符函数 2-2SQL SELECT CHR(67) FROM dual;以下是一些其它的字符函数：CHR和ASCIILPAD和RPADTRIMLENGTHDECODESQL 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 venc

22、ode=v001;36数字函数数字函数接受数字输入并返回数值结果 函数 输入 输出Abs(n) Select abs(-15) from dual; 15Ceil(n) Select ceil(44.778) from dual; 45Cos(n) Select cos(180) from dual; -.5984601 Cosh(n) Select cosh(0) from dual; 1Floor(n) Select floor(100.2) from dual; 100Power(m,n) Select power(4,2) from dual; 16 Mod(m,n) Select m

23、od(10,3) from dual; 1Round(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数字函数37转换函数转换函数将值从一种数据类型转换为另一种数据类型常用的转换函数有：TO_CHARTO_DATETO_NUMBERSELECT 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;38其它函数以下是几个用来转换空值的函数：NVLNVL2NULLIFSELECT itemdesc, NVL(re_level,0) FROM itemfile;SELECT itemdesc, NVL2(re_level,re_level,max_level)