北大青鸟oracle学习资料TP2

ACCPV4.0第二章SQL查询和SQL函数ACCPV4.0Oracle网络配置Oracle基本用户管理Oracle中用于访问数据库的主要查询工具有SQL*Plus、iSQL*Plus和PL/SQLOracle企业管理器是用于管理、诊断和调整多个数据库的工具回顾ACCPV4.0本章任务使用SQL执行数据查询控制数据的对象权限3ACCPV4.04技能目标了解Oracle数据类型了解数据定义语言和数据控制语言掌握SQL操作符和SQL函数ACCPV4.05SQL简介2-1SQL是StructuredQueryLanguage（结构化查询语言）的首字母缩写词SQL是数据库语言，Oracle使用该语言存储和检索信息表是主要的数据库对象，用于存储数据通过SQL可以实现与Oracle服务器的通信SELECTenameFROMEmp;发送SQL查询Oracle服务器enameBLAKESMITHALLENDAVIDMARTIN发送命令输出到用户端用户ACCPV4.06SQL简介2-2SQL支持下列类别的命令：数据定义语言（DDL）数据操纵语言（DML）事务控制语言（TCL）数据控制语言（DCL）数据定义语言CREATEALTERDROP数据操纵语言INSERTSELECTDELETEUPDATE事务控制语言COMMITSAVEPOINTROLLBACK数据控制语言GRANTREVOKEACCPV4.07Oracle数据类型5-1创建表时，必须为各个列指定数据类型以下是Oracle常用数据类型的类别：数据类型字符数值日期时间ACCPV4.08Oracle数据类型5-2字符数据类型CHARVARCHAR2LONG当需要固定长度的字符串时，使用CHAR数据类型。CHAR数据类型存储字母数字值。CHAR数据类型的列长度可以是1到2000个字节。VARCHAR2数据类型支持可变长度字符串VARCHAR2数据类型存储字母数字值VARCHAR2数据类型的大小在1至4000个字节范围内LONG数据类型存储可变长度字符数据LONG数据类型最多能存储2GBACCPV4.09Oracle数据类型5-3数值数据类型可以存储正数，负数，零，定点数和浮点数浮点数最高精度为38位数值数据类型的声明语法：NUMBER[(p[,s])]P表示精度，S表示小数点的位数日期时间数据类型存储日期和时间值，包括年、月、日，小时、分钟、秒主要的日期时间类型有：DATE-存储日期和时间部分，精确到整个的秒TIMESTAMP-存储日期、时间和时区信息，秒值精确到小数点后6位ACCPV4.010数据定义语言数据定义语言用于改变数据库结构，包括创建、更改和删除数据库对象用于操纵表结构的数据定义语言命令有：CREATETABLEALTERTABLETRUNCATETABLEDROPTABLEACCPV4.0创建表创建表命令用途语法：CREATETABLE[schema.]table(columndatatype[,columndatatype[,…]])11SQLCREATETABLEvendor_master(vencodeVARCHAR2(5),vennameVARCHAR2(20),venadd1VARCHAR2(20),venadd2VARCHAR2(20),venadd3VARCHAR2(20));ACCPV4.0表命名规则表名应该严格遵循下列命名规则1.表名首字符应该为字母2.不能使用oracle保留字来为表命名3.表名的最大长度为30个字符4.同一用户模式下的不同表不能具有相同的名称5.可以使用下划线，数字和字母，但不能使用空格和单引号6.Oracle中的表名（还有列名，用户名和其他对象名）不区分大小写，系统会自动转换成大写12ACCPV4.0更改表命令用途：有以下几种情况添加新列更改列的数据类型或数据类型的宽度添加或删除完整性约束条件删除现有列13ACCPV4.0更改表语法ALTERTABLEtablenameMODIFY(columndefinition…);ALTERTABLEtablenameADD(columndefinition…);ALTERTABLEtablenameDROPCOLUMNcolumn;14SQLALTERTABLEvendor_masterMODIFY(vennamevarchar2(25));SQLALTERTABLEvendor_masterADD(tel_novarchar2(12),tngst_no_number(12));SQLALTERTABLEvendor_masterDROPCOLUMNtngst_noACCPV4.0删除表用途语法DROPTABLEtablename15SQLDROPTABLEvendor_master;ACCPV4.016数据控制语言数据控制语言为用户提供权限控制命令用于权限控制的命令有：GRANT授予权限REVOKE撤销已授予的权限SQLGRANTSELECT,UPDATEONorder_masterTOMARTIN;SQLGRANTUPDATE(qty_hand,re_level)ONitemfileTOMARTIN;SQLGRANTSELECTONvendor_masterTOaccountsWITHGRANTOPTION;SQLREVOKESELECT,UPDATEONorder_masterFROMMARTIN;ACCPV4.017SQL操作符SQL操作符集合操作符逻辑操作符比较操作符算术操作符连接操作符Oracle支持的SQL操作符分类如下：ACCPV4.018算术操作符算术操作符用于执行数值计算可以在SQL语句中使用算术表达式，算术表达式由数值数据类型的列名、数值常量和连接它们的算术操作符组成算术操作符包括加(+)、减(-)、乘(*)、除(/)SQLSELECTitemdesc,max_level-qty_handavble_limitFROMitemfileWHEREp_category='spares';SQLSELECTitemdesc,itemrate*(max_level-qty_hand)FROMitemfileWHEREp_category='spares';ACCPV4.019比较操作符比较操作符用于比较两个表达式的值比较操作符包括=、!=、、、=、=、BETWEEN…AND、IN、LIKE和ISNULL等SQLSELECTitemdesc,re_levelFROMitemfileWHEREqty_handmax_level/2;SQLSELECTordernoFROMorder_masterWHEREdel_dateIN(‘06-1月-05’,‘05-2月-05');SQLSELECTvencode,venname,tel_noFROMvendor_masterWHEREvennameLIKE'j___s';ACCPV4.020逻辑操作符SQLSELECT*FROMorder_masterWHEREodate‘10-5月-05'ANDdel_date‘26-5月-05’;显示2005-5月-10至2005-5月-26的订单信息逻辑操作符用于组合多个计较运算的结果以生成一个或真或假的结果。逻辑操作符包括与(AND)、或(OR)和非(NOT)。ACCPV4.021集合操作符集合操作符将两个查询的结果组合成一个结果集合操作符UNIONUNIONALLINTERSECTMINUSUNION操作符返回两个查询选定都所有不重复都行。SQLSELECTordernoFROMorder_masterUNIONSELECTordernoFROMorder_detail;UNIONALL操作符合并两个查询选定的所有行，包括重复的行SQLSELECTordernoFROMorder_masterUNIONALLSELECTordernoFROMorder_detail;SQLSELECTqty_hand,max_levelFROMitemfileUNIONSELECTqty_ord,qty_deldFROMorder_detailORDERBY2;ACCPV4.022集合操作符集合操作符将两个查询的结果组合成一个结果集合操作符UNIONUNIONALLINTERSECTMINUSINTERSECT操作符只返回两个查询都有的行SQLSELECTordernoFROMorder_masterINTERSECTSELECTordernoFROMorder_detail;MINUS操作符只返回由第一个查询选定但是没有被第二个查询选定的行，也就是在第一个查询结果中排除在第二个查询结果中出现的行SQLSELECTordernoFROMorder_masterMINUSSELECTordernoFROMorder_detail;ACCPV4.023连接操作符连接操作符用于将多个字符串或数据值合并成一个字符串SQLSELECT(‘供应商’||venname||‘的地址是‘||venadd1||’’||venadd2||’’||venadd3)addressFROMvendor_masterWHEREvencode=‘V001’;通过使用连接操作符可以将表中的多个列合并成逻辑上的一行列ACCPV4.024操作符的优先级SQL操作符的优先级从高到低的顺序是：算术操作符--------最高优先级连接操作符比较操作符NOT逻辑操作符AND逻辑操作符OR逻辑操作符--------最低优先级ACCPV4.025SQL函数Oracle提供一系列用于执行特定操作的函数SQL函数带有一个或多个参数并返回一个值以下是SQL函数的分类：SQL函数标量函数分组函数ACCPV4.026标量函数分类标量（单行）函数对于从表中查询的每一行只返回一个值可以出现在SELECT子句中和WHERE子句中标量函数可以大致划分为：日期函数数字函数字符函数转换函数其他函数ACCPV4.027日期函数日期函数对日期值进行运算，并生成日期数据类型或数值类型的结果日期函数包括：ADD_MONTHSMONTHS_BETWEENLAST_DAYROUNDNEXT_DAYTRUNCEXTRACTACCPV4.0日期函数ADD_MONTHS函数返回给指定的日期加上指定的月数后的日期值。格式为ADD_MONTHS(d,n),其中d是日期，n表示月数28SQLSELECTdel_date,ADD_MONTHS(del_date,2)FROMorder_master;ACCPV4.0日期函数MONTHS_BETWEEN函数返回两个日期之间的月数格式为MONTHS_BETWEEN（d1，d2）详细：d1和d2是日期，输出结果为一个数值。如果d1晚于d2，则结果为正数；否则为负数。如果d1和d2为某月中的同一天或月份的最后一天，则结果始终为整数；否则oracle将根据一个月31天来计算结果的小数部分，并考虑d1和d2时间部分的差。LAST_DAY函数返回指定日期当月的最后一天的日期值格式为LAST_DAY(d)29SQLSELECTSYSDATE,LAST_DAY(SYSDATE)FROMdual;ACCPV4.0日期函数ROUND函数返回日期值，此日期四舍五入为格式模型指定的单位。格式为ROUND(d，[fmt])详细：d是日期，fmt是格式模型。fmt是一个可选项，日期默认舍入为最靠近的那一天。如果指定格式为年”YEAR”,则舍入到年的开始，即1月1日；如果格式为月”MONTH”,则舍入到月的第一日；如果格式为周”DAY”,则舍入到最靠近的星期日。30