ORACLE-SQL语句学习教程

精品文档目 录概 述2第一章 SQL*PLUS 工具软件的使用2第二章 基本查询2第三章 条件查询2第四章 单行函数2第五章 多表查询2第六章 组函数2第七章 子查询2第八章 运行期间指定变量2第九章 创建基表2第十章 ORACLE数据字典2第十一章 操纵数据2第十二章 修改基表及其约束2第十三章 创建序列2第十四章 创建视图2第十五章 创建索引2概 述20世纪70年代初，E.F.Codd 在计算机学会（Association of Computer Machinery, 简写为ACM）期刊Communications of the ACM（ACM 通讯)发表了题为”A Relational Model of Data for Large Shared Data Banks”（大型共享数据库的数据关系模型）的论文，该论文提出的关系数据库模型成为今天最为权威的关系型数据库管理模型。IBM公司首先使用该模型开发出了结构化英语查询语言SEQUEL（Structured English Query Language），作为其关系数据库原型System R的操作语言，实现对关系数据库的信息检索。SEQUEL后来简写为SQL，即Structured Query Language（结构化查询语言）的缩写。ORACLE公司于1997年推出了第一个商业应用的SQL软件。20世纪80年代初，美国国家标准化组织（ANSI）开始着手制订SQL标准，最早的ANSI标准于1986年颁布，它也被称为SQL-86。标准的出台使SQL作为标准的关系数据库语言的地位得到加强。SQL标准几经修改和完善，目前SQL语言方面新的ANSI标准是1992年制定的ANSI X3.135-1992，“Database Language SQL”。此标准也被国际电工委员会(International Electro technical Commission, 即IEC）所属的国际标准化组织(International Standards Organization, 即ISO)所接受，并将它命名为ISO/IEC9075:1992, “Database Language SQL”。这两个标准又被简称为SQL-92。SQL是目前使用最广泛的数据库语言，就象SQL的名字一样，我们可以通过容易理解的查询语言，来和数据库打交道，从数据库中得到我们想要的数据。对于SQL语言，由下列四个组成部分： DML（Data Manipulation Language）：它们是SELECT、UPDATE、INSERT、DELETE，就象它的名字一样，这4条命令是用来对数据库里的数据进行操作的语言。 DDL（Data Definition Language）：DDL比DML要多，主要的命令有CREATE、ALTER、DROP等，DDL主要是用在定义或改变表（TABLE）的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用。DCL (Data Control Language)：包括 GRANT，REVOKE，主要用于对用户权限的授权和回收。Database Transactions：包括COMMIT, ROLLBACK, SAVEPOINT，主要用于对事务的提交，回收和设置保存点。 了解这四个部分的不同对于你今后使用时有很多方便，因为许多SQL语言中的约束也是根据这个分类来划分的，比如：你在ORACLE中建立数据库链，那么只有DML类语言能对链接的数据库进行操作。 SQL作为一个ANSI标准，一直在不断改进，现在最新的标准是SQL99。ORACLE不但对标准的SQL完全兼容，而且有自己更为方便的增强SQL，所以这里介绍的主要是ORACLE8i中使用的SQL语句。ORACLE的今天今天，座落在加利福尼亚Redwood Shores 的ORACLE公司为电子信息管理提供软件产品和服务。ORACLE是一个世界范围的软件提供者，1999年的收入将超过80亿美元。ORACLE的业务遍及世界90多个国家，其软件在100多种不同的计算机上运行，在信息高速公路中扮演着一个重要的角色。ORACLE是采用Internet计算模式的倡导者之一，在该模式下，组织者为分布式应用配置了万维网。Internet是发布应用的理想基础设施。在给定的组件为基础的模型中，Internet是将分散应用集成起来的理想场所，比较三层客户/服务器计算体系而言，Internet也是这些系统的理想场所。构成此模型的三层为：l 客户层有时称为表达层，既显示信息给用户，同时接收输入的要处理的信息。l 应用层所有的处理都将按系统实现的逻辑在该层进行。商业规则的加强、数据一致性的检查系统所需的复杂处理都在该层进行。此层是三层模型中处理事务最多的一层。l 数据层通常称为back end，是为满足其它两层的需求而存储信息。在很多情况下，在用户同系统交互时，关系数据库将优化从应用层接收到的以及返回给应用层的信息。ORACLE集团不遗余力地将最新的技术提供给客户。对未来技术发展趋势预见能力以及较早地拥有关键技术，不仅使得ORACLE得以生存，更使其在竞争中遥遥领先。ORACLE Server 是这一切的基石。接下来看一下ORACLE8i Server 以及是如何围绕它开发出一系列产品的。为什么ORACLE能取得现在的地位ORACLE的很多显著特征使得它走在日益发展的信息管理群体前列，信息技术不是一成不变的，接下来看一下其中的(但决非全部的)特性：1. 决策支持系统决策支持系统即DSS，扮演着一个重要角色。到处可以看到组织者发布以ORACLE Server 为基础的数据仓库的种种信息。对数据仓库的兴趣及其使用的与日俱增使得ORACLE Server 及其相关的联机分析处理(OLAP)产品成为管理的首选。随着Express及ORACLE Discoverer 等决策支持工具的开发，ORACLE已经不管在现在, 还是在将来都确立了其在该领域的地位。2. 海量数据管理数据库系统操作的硬件瞬息万变，去处理器或磁盘市场购买最快和功率最强的CPU，刚购买到，技术又进步了。ORACLE一直对海量数据的管理非常重视，在版本7.3 (circa 1997)中为此采用了数据分区的办法。采用数据分区后，海量数据分成很多可管理的块，当系统操作或用户会话处理查询时又能透明地将分块的数据组织起来。3. 保密机制ORACLE的高级保密机制通过各种各样的特权，控制对敏感数据的存取。用户根据连接到数据库的名称被赋予各种特权，如查看、修改和创建数据库等等。用这些机制来保证某些用户能查看敏感数据，而有的用户被禁止。4. 备份与恢复ORACLE提供了高级备份和恢复的子例程。备份创建ORACLE数据的一个副本，恢复把备份的数据恢复出来。ORACLE的备份和恢复把数据丢失的可能性降到最小，并使出现故障时的排错时间最少。ORACLE的服务器也提供了备份和恢复的机制，允许每天、每周、每年不间断地访问数据。5. 空间管理ORACLE提供了灵活的空间管理。用户可以为存放数据分配所需磁盘空间，也可以通过指示ORACLE为以后的需求留下多少空间来控制后继的分配。还有一系列为大型的数据库考虑而设计的特殊功能。事实上，在ORACLE8和ORACLE7.3中许多功能都是为数据仓库的考虑而设计的。从设计角度来说，数据仓库是典型的非常大的数据库。6. 开放式联接ORACLE提供和其他软件联接的开放式接口。使用ORACLE Access Manager，用户很容易就能将别的软件商开发的软件所运行的系统集成起来。例如，使用IBM的AS/400平台的管理器，用户在应用中采用如COBOL和C的第三代、第四代语言就能透明地访问ORACLE数据，也支持PL/SQL ，从用户的AS/400应用程序中可以调用远程的ORACLE存储过程。使用Access Manager配之以ORACLE的透明网关，企业管理者就可以保护其已经在IBM 软硬件上的投资。Access Manager驻留在非ORACLE数据库的机器上，用户数据由所在的操作系统决定，工业标准SQL 在下列方面支持ORACLE数据库：l DDL 即数据定义语言语法适用于对ORACLE数据库对象的定义(如CREATE TABLE或CREATE INDEX)、修改一个或多个用户的特权(如GRANT SELECT ON)或操纵支持ORACLE8i 的基础组件(如ALTER TABLESPACE)。l DML 即数据操纵语言用于产生新的数据(如INSERT INTO)、处理已存在的数据(如UPDATE)、删除已存在的行(如DELETE)、或者是用非常熟悉的SELECT关键字简单地查看数据。Access Manager 能便捷地从Computer Associate的IDMS、Datacom、Ingres、Microsoft 的SQL Server、Informix、Teradata的EDA/SQL、Sybase和IBM的CICS中访问数据。7. 开发工具ORACLE Server通常指数据库引擎，支持一系列开发工具、终端用户查询工具、流行的应用以及办公范围内的信息管理工具。ORACLE Form 和ORACLE report 是ORACLE提供开发工具的核心，与Web相连进行发布并利用Internet计算的三层体系结构。ORACLE企业开发套件中捆绑了一些组件，使得发布灵活、操作性强、易于维护，很容易开发出不同层次的应用。套件中有如下四个主要组件：l ORACLE Designer 用于定义系统元素(也就是数据源及其之间的关系)，生成应用和定义数据库。l ORACLE Developer 是一个快速应用开发环境，用于建立交互应用、事务处理或联机事务处理为基础的系统。l ORACLE Developer Server 是一个强壮的为多层次提供的开发环境。l ORACLE Application Server 是一个公开的解决方案，它是为分布式事务应用处理而设计的。ORACLE8i 服务器ORACLE8 i 服务器是一个完善的信息管理环境。它是一个大量数据的储藏所，并给用户提供对这些数据的快速访问。ORACLE8i 服务器允许应用系统之间共享数据。信息存放在一个地方并由许多应用系统来使用。ORACLE8I 服务器可运行在Sun系列以及Windows NT上。ORACLE8i 服务器运行在很多不同的计算机上，支持下列配置：l 基于主机的配置用户直接连到存放数据库的同一计算机上。l 客户机/服务器结构用户通过网络从他们的个人计算机(客户机)上访问数据库，数据库驻留在一个分离的计算机(服务器)上。l 分布式处理用户访问存放在不止一台计算机上的数据库。数据库分散在不止一台机器上，用户并不需要了解被存取数据的实际存放位置。l Web计算( Web Enabled Computing) 能从基于Internet的应用访问数据。ORACLE服务器已经有助于使ORACLE公司成为成功的顶尖信息软件供应商，而ORACLE8i/9I 将继续保持这一优势。第一章 SQL*PLUS 工具软件的使用SQL*PLUS是ORACLE数据库管理员和普通用户最常用的实用程序之一，它提供一个交互式SQL语句、PL/SQL语句块和SQL*PLUS命令的编辑、编译和执行环境。普通用户使用它可以实现各种数据库操作，数据库管理员使用它除能够实现基本的数据库操作之外，还能够完成数据库启动、关闭和恢复等管理工作。SQL、SQL*PLUS的工作流程SQL*PLUS 软件的主要功能包括：l 登录；l 描述表结构；l 执行SQL命令；l 编辑SQL命令；l 将SQL命令存入文件；l 执行文件中的SQL命令；l 将文件中的SQL命令调入缓存区；l 格式化输出结果。登陆；l 图形方式：点击SQL*PLUS 图标，输入用户名，口令和主机字符串即可。l 命令行格式：SQLPLUS username/password网络服务名其中：网络服务名包括：主机名称（IP地址），网络访问协议，数据库名称。SQLPLUS scott/tigerORACLEl 描述表结构SELECT * FROM cat；显示用户可存取的所有基表清单。DESC emp； 描述基表EMP的结构。l 执行SQL命令SELECT * FROM emp；显示基表EMP的所有数据记录。l 编辑SQL命令EDIT；编辑存储在SQL*PLUS缓存区的内容，即最后一条SQL命令。l 将SQL命令存入文件格式：SAVE filenameSAVE c:select_emp.sql; 将存储在SQL*PLUS缓存区的内容，即最后一条SQL命令存储为磁盘文件。l 将文件中的SQL命令调入缓存区 格式：EDIT filename EDIT c:select_emp.sqll 执行文件中的SQL命令格式：START filename 或 filenameSTART c:select_emp.sqlc:select_emp.sqll 格式化输出结果。设置列标题COL ename HEADING Employee|Name FORMAT A10;COL sal JUSTFY LEFT FORMAT $99,999.00;COL hiredate FORMAT A8 NULL not hired;COL comm FORMAT $99,999.00 NULL No字符和日期An设置显示的宽度（n）.数字9 Single zero-suppression digit.0 强制为0.$美元标识.L当前的货币符号.小数点位.,千位分隔符.l 显示列的格式设置COL ename；l 清除列的格式设置 COL ename CLEAR；l 将输出结果存储为磁盘文件。格式：SPOOL filename SPOOL OFFSPOOL c:emp.txtSELECT * FROM emp ORDER BY deptno;SPOOL OFF另外，ORACLE 8I 的SQL*PLUS 工具含有帮助功能：SQLHELP；SQLHELP INDEX；显示所有SQL*PLUS 可用命令。SQLHELP STARTUP；显示STARTUP的功能及用法。SQL*PLUS 系统常用命令介绍：假设当前执行命令为：SELECT * FROM tab;l (A)PPEND 添加文本到缓冲区当前行尾SQL A order by tname结果：SELECT * FROM tab ORDER BY tname; （注：A后面跟2个空格）l (C)HANGE/old/new 在当前行用新的文本替换旧的文本SQL C/*/tname结果：SELECT tname FROM tab;l (C)HANGE/text从当前行删除文本SQL C/tab结果：SELECT tname FROM;l DEL删除当前行l DEL n 删除第n行l (I)NPUT 文本 在当前行之后添加一行l (L)IST 显示缓冲区中所有行l (L)IST n 显示缓冲区中第 n 行l (L)IST m n 显示缓冲区中 m 到 n 行l RUN执行当前缓冲区的命令l / 执行当前缓冲区的命令l R 执行当前缓冲区的命令l 文件名 运行调入内存的sql文件，如：l EDIT s如果当前目录下不存在s.sql文件，则系统自动生成s.sql文件，在其中输入“select * from tab;”，存盘退出。l s系统会自动查询当前用户下的所有表、视图、同义词。l 文件名 在.sql文件中调用另一个.sql文件时使用l EXIT 退出SQL*PLUSl DESC 表名 显示表的结构l CLEAR SCREEN 清空当前屏幕显示SQL*PLUS系统环境变量SHOW和SET命令是两条用于维护SQL*PLUS系统变量的命令SHOW ALL查看所有68个系统变量值SHOW USER显示当前连接用户SHOW ERROR显示错误信息SET HEADING OFF禁止输出列标题，默认值为ONSET FEEDBACK OFF禁止显示最后一行的计数反馈信息，默认值为对6个或更多的记录，回送ONSET TIMING ON默认为OFF，设置查询耗时，可用来估计SQL语句的执行时间，测试性能SET SQLPROMPT SQL 设置默认提示符，默认值就是SQL SET LINESIZE 1000设置屏幕显示行宽，默认80SET AUTOCOMMIT ON设置是否自动提交，默认为OFFSET PAUSE ON默认为OFF，设置暂停，会使屏幕显示停止，等待按下ENTER键再显示下一页SET ARRAYSIZE 1默认为15SET LONG 1000默认为80第二章 基本查询教学目标l 编写SELECT 语句用于查询数据库数据；l 完成算术计算；l 处理NULL 值；l 确定使用别名作为列的标题；l 连接列；SELECT 语法SELECT DISTINCT *, column alias, FROM table;l SELECT 指明查询的列l FROM 指明查询的表书写规则：l 命令可以写在一行或多行。l 可以使用缩进，提高可读性。l 关键字不能缩写或拆分。l 大小写不敏感。l 命令存入缓冲区。最简单的查询语句包含如下两个子句：l SELECT 子句：用 * 代表所有列l FROM 子句例：显示dept 表的所有记录。SELECT * FROM dept;例：显示emp 表的所有记录。SELECT * FROM emp ;查询指定列l 在SELECT子句中列出要查询的列，用，分隔。查询结果中列的排列顺序由SELECT子句中位置而定。SELECT empno, ename, sal, deptno FROM emp;SELECT deptno, dname FROM dept;列标识的缺省对齐模式：l 日期、字符左对齐l 数字右对齐l 缺省的标识显示为大写.算术表达式l 可以对数字、日期类型使用算术表达式。l 算术运算符包括加(+) ，减(-) ，乘(*) ，除(/) 。例：显示每个雇员的年工资。SELECT empno, ename, sal*12 FROM emp;操作符优先级：l 先乘除后加减。l 同级算符从左到右。l ( )改变优先次序。 SELECT ename, 12*sal+100 FROM emp;SELECT ename, 12*(sal+100) FROM emp;使用别名改变列标识l 用于算术表达式。l 直接跟在列后面，可以在列后面使用关键字AS。l 若别名中由空格、特殊字符或大小写敏感，应使用“”。SELECT empno, ename name, sal*12 “Annual Salary” FROM emp;连接符（|） l 连接列或字符串。l 产生字符表达式。SELECT ename |-|sal|-|deptno FROM emp;空值处理l NULL 表示未定义、不可用且不占用存贮空间。l NULL 不是0或空格。l 包含null数学表达式也为 NULL。SELECT ename, sal, comm, sal+comm FROM emp;NVL 函数：将NULL转换成其他值.l 可用于数字、日期、字符.l 数据类型必须匹配.NVL (hiredate, 01-JAN-95)NVL (title, No Title Yet)NVL (salary, 1000)SELECT ename, sal, comm, sal+NVL(comm, 0) FROM emp;去重（DISTINCT）l 缺省的查询结果显示所有记录（包括重复的值）；SELECT deptno FROM emp;l 使用DISTINCT关键字去掉重复记录；SELECT DISTINCT deptno FROM emp;l DISTINCT 可以用于SELECT列表中的所有的列。l DISTINCT 可以用于SELECT列表中的多个列, 结果为列组合的去重。例：SELECT DISTINCT deptno, job FROM emp;在数据库中，空值用来表示实际值未知或无意义的情况。在一个表中，如果一行中的某列没有值，那么就称它为空值（NULL）。任何数据类型的列,只要没有使用非空（NOT NULL）或主键（PRIMARYKEY）完整性限制，都可以出现空值。在实际应用中，如果忽略空值的存在，将会造成造成不必要的麻烦。例如，在雇员表（EMP）中，雇员名（ENAME）为KING的行，因为KING为最高官员（PRESIDENT），他没有主管（MGR），所以其MGR为空值。因为不是所有的雇员都有手续费（COMM），所以列COMM允许有空值，除300、500、1400、0以外的其它各行COMM均为空值。以EMP表为例，具体讨论一下空值在日常应用中所具有的一些特性。空值的生成：l 如果一列没有非空（NOTNULL）完整性限制，那么其缺省的值为空值，即如果插入一行时未指定该列的值，则其值为空值。l 使用SQL语句INSERT插入行，凡未涉及到的列，其值为空值；涉及到的列，如果其值确实为空值，插入时可以用NULL来表示（对于字符型的列，也可以用来表示）。例：插入一行，其EMPNO为1、ENAME为JIA、SAL为10000、job和comm为空值。INSERT INTO emp(empno, ename, job, sal, comm) VALUES(1, JIA, NULL, 1000, NULL);SELECT * FROM emp WHERE empno=1;可以看到新插入的一行，除job和comm为空值外，mgr、hiredate、deptno三列由于插入时未涉及，也为空值。l 使用SQL语句UPDATE来修改数据，空值可用NULL来表示（对于字符型的列，也可以用来表示）。UPDATE emp set ename=NULL, sal=NULL WHERE empno=1;空值的特点：l 等价于没有任何值。l 与0、空字符串或空格不同。l 在WHERE条件中,ORACLE认为结果为NULL的条件为FALSE，带有这样条件的SELECT语句不返回行，并且不返回错误信息。但NULL和FALSE是不同的。l 排序时比其他数据都大。l 空值不能被索引。空值的测试：因为空值表示缺少数据，所以空值和其它值没有可比性，即不能用等于、不等于、大于或小于和其它数值比较，当然也包括空值本身（但是在decode中例外，两个空值被认为是等价）。测试空值只能用比较操作符IS NULL和IS NOT NULL。如果使用带有其它比较操作符的条件表达式，并且其结果依赖于空值，那么其结果必定是NULL。在WHERE条件中，ORACLE认为结果为NULL的条件为FALSE，带有这样条件的SELECT语句不返回行，也不返回错误信息。例：查询EMP表中MGR为NULL的行：SELECT * FROM emp WHERE mgr=;SELECT * FROM emp WHERE mgr=null;SELECT * FROM emp WHERE mgr IS NULL;第1、2句写法不妥，WHERE条件结果为NULL，不返回行。第三句正确，返回MGR为空值的行。例：SELECT ename, sal FROM emp WHERE salANY(3000, NULL);条件salANY(3000, NULL)等价于sal3000 OR salNULL。例：SELECT ename, sal FROM emp WHERE salALL(3000, NULL);条件salall(3000,NULL)等价于sal3000 AND salNULL，结果只能为NULL或FALSE，所以不返回行。第三章 条件查询教学目标l 使用ORDER BY 子句排序查询数据；l 使用WHERE 子句输入查询条件。ORDER BY子句：用于查询结果排序。l ASC 升序, 缺省；l DESC 降序；l 在SELECT命令中，ORDER BY 子句是最后一句；l 缺省的排列顺序是升序；l 可以使用关键字DESC改变排列顺序；l 可以对表达式和别名排列顺序；l 可以对列出现的位置进行排序；l 可以对多列进行排序；l 按照关键字ORDER BY 后面的列表的顺序进行排序；l 可以对未出现在SELECT 子句中的列进行排序。例：SELECT * FROM emp ORDER BY deptno, sal DESC;SELECT * FROM emp ORDER BY deptno, comm DESC;SELECT ename, sal*12 FROM emp ORDER BY 2; 条件查询使用WHERE子句限制显示的结果，实现条件查询，WHERE 子句跟在 FROM 子句后。条件有下面三部分组成：l 列、表达式、常数l 比较运算符l 结果字符串、日期的使用l 字符串、日期应用；l 字符串大小写敏感；l 缺省的日期格式为 DD-MON-YY。SELECT * FROM emp WHERE ename=SCOTT;SELECT * FROM emp WHERE LOWER(ename)=scott;l 比较运算符：= 、 、= 、 、=；l SQL 算符：BETWEEN . AND.、IN(list)、LIKE、IS NULL；l 逻辑算符：AND、OR、NOT。l 使用BETWEEN查找列值在指定范围内的记录。SELECT * FROM emp WHERE sal between 2000 AND 3000;l 使用IN 查找列值在列表内条件的记录。SELECT * FROM emp WHERE deptno in (10, 20);l LIKE 算符- 可以使用LIKE算符实现通配符匹配；- 比较条件中可以含有字符和数字；- “%” 代表无字符或多字符；- “_” 代表单个字符；SELECT * FROM emp WHERE ename LIKE M%;- 使用LIKE算符，有时可以替代BETWEEN算符；SELECT * FROM emp WHERE hiredate LIKE %81;- 可以拼接通配符.SELECT * FROM emp WHERE ename LIKE _A%;- 可以使用ESCAPE实现通配符的查找.INSERT INTO emp(empno,ename) VALUES (88,bill_gate);SELECT * FROM emp WHERE ename LIKE %ll_g% ESCAPE ;l IS NULL 算符- 测试是否为空值；- 不能使用 = 。SELECT * FROM emp WHERE comm IS NULL;SELECT * FROM emp WHERE comm IS NOT NULL;否定条件表达式：用于排除不符合条件的记录。l 逻辑运算符：!= 、 、 = ；l SQL 算符：NOT BETWEEN、NOT IN、NOT LIKE、IS NOT NULL。多条件查询：l 使用合成的条件；l 使用AND、OR组成多条件；l AND 要求两个条件都为真；l OR 要求任一条件为真。SELECT * FROM emp WHERE deptno=20 AND sal2000;SELECT * FROM emp WHERE deptno=20 OR sal2000;操作符的优先级l 所有的比较算符.l ANDl OR例：列出部门10，工资大于1000 和部门20的雇员信息。SELECT * FROM emp WHERE sal=1000 AND deptno=10 OR deptno=20;例：列出部门10和部门20，工资大于1000的雇员信息。SELECT * FROM emp WHERE sal=1000 AND (deptno=10 OR deptno=20);l 使用变量：在变量名称前加& SELECT * FROM emp WHERE deptno=&d_no;SELECT * FROM emp WHERE ename=&name;SELECT * FROM emp WHERE ename=&name;第四章 单行函数教学目标l 解释SQL语句中可以使用的各种类型函数；l 明确使用函数的基本概念；l 在SELECT 语句中，包含字符、数字和日期函数的种类；l 解释转换函数及其用法。SQL中函数的功能l 数值计算.l 改变数据.l 控制记录组输出.l 改变日期显示.l 类型转换.SQL 函数的类型l 单行函数（对每条记录有效）- 字符类- 数字类- 日期类- 转换类l 多行函数（对多条记录有效）- 组函数单行函数的功能：l 操纵数据l 根据命令返回数据l 单行操作，且每行返回值l 数据类型转换l 可嵌套语法格式：function_name(column | expression , argument1, argument2 )字符格式转换函数l LOWER转换字符串为小写字母；l UPPER转换字符串为大写字母；l INITCAP转换字符串的所有单词，使该单词均以大写字母开头；l CONCAT将两个输入字符串组合成一个；l SUBSTR返回一字符串的指定字符串；l LENGTH返回字符串的长度；l NVL需要两个参数，如果第一个参数为空，则返回第二个参数。函 数结 果LOWER(SQL Course)sql courseUPPER(SQL Course)SQL COURSEINITCAP(SQL Course)Sql CourseCONCAT(Good, String)GoodStringSUBSTR(String,1,3)StrLENGTH(String)6例：SELECT * FROM emp WHERE LOWER(ename)=scott;SELECT empno, ename, NVL(to_char(mgr), no manager) manager FROM emp;SELECT INITCAP(SQL) FROM dual;数字函数l ROUND将一个数值舍入成第二个参数指定形式的十进制数值；l TRUNC将一个数值截取成第二个参数指定形式的十进制数值；l MOD返回两数相除的余数。函 数结 果ROUND (45.923, 2)45.92ROUND (45.923, 0)46ROUND (45.923, -1)50TRUNC (45.923, 2)45.92TRUNC (45.923)45TRUNC (45.923, -1)40MOD(1600,300)100日期格式l ORACLE将日期按照内部格式将日期存成以下七个字节- 世纪、年、月、日、时、分、秒l 在ORACLE中日期缺省的显示格式为：DD-MON-YYl 可以使用系统变量SYSDATE获取系统时间l 查看SYSDATE时，可以使用虚拟表DUAL日期函数ORACLE的日期格式：DD-MON-YYl MONTHS_BETWEEN：返回两个日期之间的月份数；格式：MONTHS_BETWEEN(date1,date2)SELECT MONTHS_BETWEEN(TO_DATE(29-12月-1999,DD-MON-YYYY),TO_DATE(29-12月-1988,DD-MON-YYYY) FROM dual;l ADD_MONTHS：返回一个日期加上一制定的月份数；格式：ADD_MONTHS(date,months)SELECT ADD_MONTHS(TO_DATE(29-10月-1999,DD-MON-YYYY), 2)FROM dual;l NEXT_DAY：返回从实参日期开始，紧随其后的指定星期所对应的日期；格式：NEXT_DAY(date,weekday)SELECT NEXT_DAY(SYSDATE,星期一) FROM dual;l LAST_DAY：返回实参指定日期所对应月份的最后一天；格式：LAST_DAY(date)SELECT LAST_DAY(SYSDATE) FROM dual;l ROUND：将一个日期舍入成第二个参数指定形式的日期；SELECT ROUND(TO_DATE(24-11月-01, DD-MON-YY), MONTH)FROM dual;SELECT ROUND(TO_DATE(1-7月-01, DD-MON-YY), YEAR)FROM dual;l TRUNC：将一个日期截取成第二个参数指定形式的日期；SELECT TRUNC(TO_DATE(24-11月-01, DD-MON-YY), MONTH)FROM dual;SELECT TRUNC(TO_DATE(1-7月-01, DD-MON-YY), YEAR)FROM dual;转换函数l 在转换函数中需要使用格式串l TO_CHAR 将数字或日期转换为字符串TO_CHAR 转换日期：格式：TO_CHAR(date, fmt)格式串- 包含在 中- 大小写敏感- 可以是任意有效的日期格式- 使用fm去除占位符- 与日期之间使用，分隔日期格式串分类- YYYY 表示完整的四位数字年- YEAR 表示英文拼写的年- MM 表示两位数字月- MONTH 表示英文全拼的年- DY 表示三位缩写的星期- DAY 表示英文全拼的日期- 时间格式：HH24:MI:SS AM15:45:32 PM- 在格式串中加入字符串：DD of MONTH12 of OCTOBER- 用后缀拼出整个日期：ddspthfourteenth例：SELECT TO_CHAR(SYSDATE, day dd-mon-yyyy) FROM dual;SELECT TO_CHAR(SYSDATE, day dd-mm-yyyy) FROM dual;SELECT TO_CHAR(SYSDATE, ddd yyyy) FROM dual; -返回从今年开始的天数；SELECT TO_CHAR(SYSDATE, day dd-mon-yyyy HH24:MI:SS AM) FROM dual;SELECT TO_CHAR(SYSDATE, dd “of” month,yyyy) FROM dual;SELECT TO_CHAR(SYSDATE, Year, Month,ddspth) FROM dual;TO_CHAR 转换函数：使用此函数将数字转换成字符格式：TO_CHAR(number, fmt)- 9 表示数字- 0 强制为0- $ 设置美元符号- L 使用当前字符集的货币符号- . 小数点- , 千位分隔符TO_CHAR 转换数字- 如果出现#，表示格式中没有为数字设定足够的位数- ORACLE中可自动按格式将数字四舍五入l TO_NUMBER 将字符串转换成数字l TO_DATE将字符串转换成日期SELECT to_date(01-10月-01,dd-mon-yyyy) FROM dual;单行函数的嵌套l 单行函数可以任意嵌套l 嵌套函数从最内层的函数开始计算虚拟表dual 的用法：l SELECT USER FROM dual; 查询当前用户；l SELECT SYSDATE FROM dual; 查询数据库日期；l SELECT 25*35 FROM dual; 用作计算器；l CREATE SEQUENCE empse INCREMENT BY 1 START WITH 1;SELECT empse.NEXTVAL FROM dual;SELECT empse.CURRVAL FROM dual;第五章 多表查询教学目标l 编写SELECT语句，通过使用相等和非相等连接，用于访问多个表的数据；l 通过使用外连接，查找不能满足连接条件的数据；l 自连接。概念l 连接用于从多表中查询数据；l 通过使用公共值将记录行进行连接，最典型的是使用主键和外键值；l 连接方法：等值连接、非等值连接、外连接、自连接、集合操作符。笛卡尔乘积l 笛卡尔乘积的形成：- 连接条件被省略；- 连接条件无效；- 第一个基表的所有行被连接到第二个基表的所有行。l 为了避免笛卡尔乘积，总是在WHERE 子句中使用有效的连接条件。简单连接查询语法格式：SELECT table.column, table.column FROM tale1, table2WHERE table1.column1=table2.column2;l 在WHERE 子句中指定连接条件；l 为了明确，在每个列名前使用表名；l 当多个表中有相同的列名时，表名必须作为列名的前缀。l 使用表前缀可以提高性能；l 对于不同基表中，具有相同名字的列可以使用别名加以区别。基表别名l 使用表别名限定列；l 只有对SELECT语句有效；l 一旦创建了表别名，就使用表别名代替表名限定列。例：显示所有员工的基本信息，包括部门名称和工作地址SELECT empno, ename, job, d.deptno, dname, d.loc FROM emp e, dept dWHERE e.deptno=d.deptnoORDER BY e.deptno;例：查询工作地址在CHICAGO的员工的情况SELECT empno, ename, job, d.deptno, dname, d.loc FROM emp e, dept dWHERE e.deptno=d.deptno and loc=CHICAGO;非等值连接l 当一个表中没有列与另一个表中的列相匹配，就导致使用非等值连接；l 连接条件中包含了除等于号(=)之外的操作符。例：查询工资高于2000的员工及所在部门的情况SELECT ename, sal, e.deptno, d.dname, loc FROM emp e, dept dWHERE d.deptno=e.deptno AND sal2500ORDER BY e.deptno;例：查询每个员工的工资级别SELECT empno, ename, job, sal, deptno, grade FROM emp, salgradeWHERE sal BETWEEN losal AND hisalORDER BY deptno;外连接l 使用外连接查看那些通常不能满足连接条件的记录行；l 外连接操作符为加号(+)；l 将操作符放在没有值可以连接的一侧；l 包括外连接的条件不可以： - 使用IN 操作符；- 使用O