SQL-查询和-SQL-函数PPT课件

SQL查询和SQL函数,Oracle服务器由Oracle数据库和Oracle实例组成Oracle实例由系统全局区内存结构和用于管理数据库的后台进程组成Oracle中用于访问数据库的主要查询工具有SQL*Plus、iSQL*Plus和PL/SQLOracle企业管理器是用于管理、诊断和调整多个数据库的工具Oracle中的SYSTEM用户和SYS用户具有管理权限，而SCOTT用户只有基本的权限,回顾,3,目标,了解Oracle数据类型了解数据定义语言和数据操纵语言了解事务控制语言和数据控制语言掌握SQL操作符和SQL函数,4,SQL简介2-1,SQL是StructuredQueryLanguage（结构化查询语言）的首字母缩写词SQL是数据库语言，Oracle使用该语言存储和检索信息表是主要的数据库对象，用于存储数据,通过SQL可以实现与Oracle服务器的通信,发送SQL查询,用户,5,SQL简介2-2,SQL支持下列类别的命令：数据定义语言（DDL）数据操纵语言（DML）事务控制语言（TCL）数据控制语言（DCL）,数据定义语言,CREATE,ALTER,DROP,数据操纵语言,INSERT,SELECT,DELETE,UPDATE,事务控制语言,COMMIT,SAVEPOINT,ROLLBACK,数据控制语言,GRANT,REVOKE,6,Oracle数据类型5-1,创建表时，必须为各个列指定数据类型以下是Oracle数据类型的类别：,数据类型,字符,数值,日期时间,RAW/LONGRAW,LOB,7,Oracle数据类型5-2,字符数据类型,CHAR,VARCHAR2,LONG,当需要固定长度的字符串时，使用CHAR数据类型。CHAR数据类型存储字母数字值。CHAR数据类型的列长度可以是1到2000个字节。,VARCHAR2数据类型支持可变长度字符串VARCHAR2数据类型存储字母数字值VARCHAR2数据类型的大小在1至4000个字节范围内,LONG数据类型存储可变长度字符数据LONG数据类型最多能存储2GB,8,Oracle数据类型5-3,数值数据类型可以存储整数、浮点数和实数最高精度为38位数值数据类型的声明语法：NUMBER(p,s)P表示精度，S表示小数点的位数,日期时间数据类型存储日期和时间值，包括年、月、日，小时、分钟、秒主要的日期时间类型有：DATE-存储日期和时间部分，精确到整个的秒TIMESTAMP-存储日期、时间和时区信息，秒值精确到小数点后6位,9,Oracle数据类型5-4,RAW数据类型用于存储二进制数据RAW数据类型最多能存储2000字节LONGRAW数据类型用于存储可变长度的二进制数据LONGRAW数据类型最多能存储2GB,LOB,LOB称为“大对象”数据类型，可以存储多达4GB的非结构化信息，例如声音剪辑和视频文件等LOB数据类型允许对数据进行高效、随机、分段的访问,BLOB,CLOB,BFILE,CLOB即CharacterLOB（字符LOB），它能够存储大量字符数据,BLOB即BinaryLOB（二进制LOB），可以存储较大的二进制对象，如图形、视频剪辑和声音文件,BFILE即BinaryFile（二进制文件），它用于将二进制数据存储在数据库外部的操作系统文件中,10,Oracle数据类型5-5,Oracle中伪列就像一个表列，但是它并没有存储在表中伪列可以从表中查询，但不能插入、更新和删除它们的值常用的伪列有ROWID和ROWNUM,ROWID是表中行的存储地址，该地址可以唯一地标识数据库中的一行，可以使用ROWID伪列快速地定位表中的一行,ROWNUM是查询返回的结果集中行的序号，可以使用它来限制查询返回的行数,11,数据定义语言,数据定义语言用于改变数据库结构，包括创建、更改和删除数据库对象用于操纵表结构的数据定义语言命令有：CREATETABLEALTERTABLETRUNCATETABLEDROPTABLE,12,数据操纵语言,数据操纵语言用于检索、插入和修改数据数据操纵语言是最常见的SQL命令数据操纵语言命令包括：SELECTINSERTUPDATEDELETE,13,DMLSELECT命令2-1,利用现有的表创建表语法：CREATETABLEASSELECTcolumn_namesFROM;,SQLCREATETABLEnewitemfileASSELECT*FROMitemfile;,SQLCREATETABLEnewitemfile1ASSELECTitemcode,itemdesc,qty_handFROMitemfile;,SQLCREATETABLEnewitemfile2ASSELECT*FROMitemfileWHERE1=2;,14,DMLSELECT命令2-2,SQLSELECTDISTINCTvencodeFROMvendor_master;,选择无重复的行在SELECT子句，使用DISTINCT关键字,使用列别名为列表达式提供不同的名称该别名指定了列标题,SQLSELECTitemcode,itemdesc,max_level,max_level*2ASNEW_MAXLEVELFROMitemfile;,SQLSELECTitemcode,itemdesc,max_level,max_level*2“NewMaximumLevel”FROMitemfile;,15,DMLINSERT命令2-1,插入日期类型的值日期数据类型的默认格式为“DD-MON-RR”使用日期的默认格式使用TO_DATE函数转换,INSERTINTOmy_table(date_col)VALUES(TO_DATE(2005-10-18,YYYY-MM-DD);,INSERTINTOorder_masterVALUES(o001,12-5月-05,V002,c,25-5月-05);,16,DMLINSERT命令2-2,插入来自其它表中的记录语法：INSERTINTO(cloumn_list)SELECTcolumn_namesFROM;,SQLINSERTINTOnewvendor_masterSELECT*FROMvendor_master;,SQLINSERTINTOnewvendor_master(vencode,venname)SELECTvencode,vennameFROMvendor_master;,17,事务控制语言,事务是最小的工作单元，作为一个整体进行工作保证事务的整体成功或失败，称为事务控制用于事务控制的语句有：COMMIT-提交并结束事务处理ROLLBACK-撤销事务中已完成的工作SAVEPOINT标记事务中可以回滚的点,SQLUPDATEorder_masterSETdel_date=30-8月-05WHEREordernoSAVEPOINTmark1;SQLDELETEFROMorder_masterWHEREorderno=o002;SQLSAVEPOINTmark2;SQLROLLBACKTOSAVEPOINTmark1;SQLCOMMIT;,18,数据控制语言,数据控制语言为用户提供权限控制命令用于权限控制的命令有：GRANT授予权限REVOKE撤销已授予的权限,SQLGRANTSELECT,UPDATEONorder_masterTOMARTIN;,SQLGRANTUPDATE(qty_hand,re_level)ONitemfileTOMARTIN;,SQLGRANTSELECTONvendor_masterTOaccountsWITHGRANTOPTION;,SQLREVOKESELECT,UPDATEONorder_masterFROMMARTIN;,19,SQL操作符,SQL操作符,集合操作符,逻辑操作符,比较操作符,算术操作符,连接操作符,Oracle支持的SQL操作符分类如下：,20,算术操作符,算术操作符用于执行数值计算可以在SQL语句中使用算术表达式，算术表达式由数值数据类型的列名、数值常量和连接它们的算术操作符组成算术操作符包括加(+)、减(-)、乘(*)、除(/),SQLSELECTitemdesc,max_level-qty_handavble_limitFROMitemfileWHEREp_category=spares;,SQLSELECTitemdesc,itemrate*(max_level-qty_hand)FROMitemfileWHEREp_category=spares;,21,比较操作符,比较操作符用于比较两个表达式的值比较操作符包括=、!=、=、BETWEENAND、IN、LIKE和ISNULL等,SQLSELECTitemdesc,re_levelFROMitemfileWHEREqty_handSELECTordernoFROMorder_masterWHEREdel_dateIN(06-1月-05,05-2月-05);,SQLSELECTvencode,venname,tel_noFROMvendor_masterWHEREvennameLIKEj_s;,22,逻辑操作符,SQLSELECT*FROMorder_masterWHEREodate10-5月-05ANDdel_dateSELECTordernoFROMorder_masterINTERSECTSELECTordernoFROMorder_detail;,SQLSELECTordernoFROMorder_masterMINUSSELECTordernoFROMorder_detail;,MINUS操作符返回从第一个查询结果中排除第二个查询中出现的行。,24,连接操作符,连接操作符用于将多个字符串或数据值合并成一个字符串,SQLSELECT(venname|的地址是|venadd1|venadd2|venadd3)addressFROMvendor_masterWHEREvencode=V001;,通过使用连接操作符可以将表中的多个列合并成逻辑上的一行列,25,操作符的优先级,SQL操作符的优先级从高到低的顺序是：算术操作符-最高优先级连接操作符比较操作符NOT逻辑操作符AND逻辑操作符OR逻辑操作符-最低优先级,26,SQL函数,Oracle提供一系列用于执行特定操作的函数SQL函数带有一个或多个参数并返回一个值以下是SQL函数的分类：,SQL函数,单行函数,分析函数,分组函数,27,单行函数分类,单行函数对于从表中查询的每一行只返回一个值可以出现在SELECT子句中和WHERE子句中单行函数可以大致划分为：日期函数数字函数字符函数转换函数其他函数,28,日期函数,日期函数对日期值进行运算，并生成日期数据类型或数值类型的结果日期函数包括：ADD_MONTHSMONTHS_BETWEENLAST_DAYROUNDNEXT_DAYTRUNCEXTRACT,29,字符函数2-1,字符函数,字符函数接受字符输入并返回字符或数值,30,字符函数2-2,SQLSELECTCHR(67)FROMdual;,以下是一些其它的字符函数：CHR和ASCIILPAD和RPADTRIMLENGTHDECODE,SQLSELECTLPAD(function,15,=)FROMdual;,SQLSELECTTRIM(9from9999876789999)FROMdual;,SQLSELECTLENGTH(frances)FROMdual;,SQLSELECTvencode,DECODE(venname,frances,Francis)nameFROMvendor_masterWHEREvencode=v001;,31,数字函数,数字函数接受数字输入并返回数值结果,数字函数,32,转换函数,转换函数将值从一种数据类型转换为另一种数据类型常用的转换函数有：TO_CHARTO_DATETO_NUMBER,SELECTTO_CHAR(sysdate,YYYY年fmMM月fmDD日HH24:MI:SS)FROMdual;,SELECTTO_CHAR(itemrate,C99999)FROMitemfile;,SELECTTO_DATE(2005-12-06,yyyy-mm-dd)FROMdual;,SELECTTO_NUMBER(100)FROMdual;,33,其它函数,以下是几个用来转换空值的函数：NVLNVL2NULLIF,SELECTitemdesc,NVL(re_level,0)FROMitemfile;,SELECTitemdesc,NVL2(re_level,re_level,max_level)FROMitemfile;,SELECTitemdesc,NULLIF(re_level,max_level)FROMitemfile;,34,分组函数,分组函数基于一组行来返回结果为每一组行返回一个值,AVG,MIN,MAX,SUM,COUNT,SELECTAVG(re_level)FROMitemfileWHEREp_category=accessories;,SELECTMAX(max_level)FROMitemfile;,SELECTSUM(itemrate*max_level)FROMitemfile;,SELECTCOUNT(*)FROMitemfile;,SELECTCOUNT(itemrate)FROMitemfile;,SELECTCOUNT(DISTINCTqty_hand)FROMitemfile;,分组函数,35,GROUPBY和HAVING子句,GROUPBY子句用于将信息划分为更小的组每一组行返回针对该组的单个结果HAVING子句用于指定GROUPBY子句检索行的条件,SELECTp_category,MAX(itemrate)FROMitemfileGROUPBYp_category;,SELECTp_category,MAX(itemrate)FROMitemfileGROUPBYp_categoryHAVINGp_categoryNOTIN(accessories);