第4章_SQL语言基础

1、第4章 SQL语言基础OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程本章本章 学习导航学习导航Oracle 11g安装与配置数据库技术基础回顾数据库备份与恢复数据库应用程序开发闪回技术数据库存储管理数据库操作数据表操作查询操作视图和索引操作PL/SQL编程基础高级管理基本管理数据库设计数据库管理数据库应用数据库安全管理OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程本章学习要点本章学习要点（1）SQL查询的查询的基本语法基本语法（2）简单查询简单查询的形式和实现的形式和实现（3）连接查询连接查询的形式和实现的形式和实现（4）子查询子查询的实现和

2、应用的实现和应用（5）联合查询联合查询及其应用及其应用（6） 数据定义语句数据定义语句（7） 数据操纵语句数据操纵语句（8）数据控制语句）数据控制语句计划：计划：6+4课时课时OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程 Oracle Oracle的常用账户的常用账户用户名用户名角色角色默认密码默认密码说明说明syssys超级管理员change_on_change_on_installinstall所有所有oracleoracle的数据字典的基表和的数据字典的基表和视图都存放在视图都存放在syssys用户中，这些基用户中，这些基表和视图对于表和视图对于oracleo

3、racle的运行是至的运行是至关重要的，由数据库自己维护，关重要的，由数据库自己维护，任何用户都不能手动更改。任何用户都不能手动更改。syssys用用户拥有户拥有dbadba，sysdbasysdba，sysopersysoper等等角色或权限，是角色或权限，是oracleoracle权限最高权限最高的用户。的用户。systemsystem普通管理员managermanager用于存放次一级的内部数据，如用于存放次一级的内部数据，如oracleoracle的一些特性或工具的管理的一些特性或工具的管理信息。信息。systemsystem用户拥有普通用户拥有普通dbadba角角色权限。可用来创建其

4、他用户。色权限。可用来创建其他用户。scottscott普通用户普通用户（练习常（练习常用）用）tigertiger在默认情况下从在默认情况下从Oracle10gOracle10g开始，开始，scottscott不能不能登陆登陆，被被禁用了。需要禁用了。需要手工解锁。手工解锁。OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程v 用户管理用户管理命令命令1. 1. 更改用户登录更改用户登录命令格式：命令格式：conn conn 用户名用户名/ /密码密码 AS SYSDBA AS SYSDBA 注意：如果连接的是超级管理员（注意：如果连接的是超级管理员（SYSSYS），必

5、须写上），必须写上 AS SYSDBA AS SYSDBA 2. 2. 用户加锁和解锁用户加锁和解锁加锁：加锁：SQL SQL alter user alter user 用户名用户名 account lock; account lock; 解锁：解锁：SQL SQL alter user alter user 用户名用户名 account unlock; account unlock;v SQL*PLUS的操作命令OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程3.3.创建用户创建用户用具有创建新用户权限的用户登录到数据库，用具有创建新用户权限的用户登录到数据库，才能

6、进行新用户的创建。例如：才能进行新用户的创建。例如：syssys、system,system,以及以及被授予该权限的其他用户。被授予该权限的其他用户。命令格式：命令格式：SQL SQL create user create user 用户名用户名 identified by identified by 密码密码; ;SQL*PLUS的操作命令OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程4. 4. 更改用户密码更改用户密码命令格式：命令格式：SQL SQL alter user alter user 用户名用户名 identified by identified by

7、 密码密码; ;注意，如果忘记所有用户的密码，可启动注意，如果忘记所有用户的密码，可启动SQLSQL* * PlUSPlUS，输入以下命令：，输入以下命令：conn / as sysdbaconn / as sysdbaSQL SQL alter user alter user 用户名用户名 identified by identified by 密码密码; ;SQL*PLUS的操作命令OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程SQL*PLUS的其他操作命令(1)(1)查看当前连接用户查看当前连接用户SQL SQL show usershow user (2)(2

8、)查看当前用户拥有的表对象查看当前用户拥有的表对象( (查看数据字典查看数据字典user_tables)user_tables) SQL SQLSELECT table_name FROM user_tables;SELECT table_name FROM user_tables;(3) (3) 查看查看表或视图各个列的名称以及属性表或视图各个列的名称以及属性 describe describe命令命令, ,可缩写为可缩写为descdesc命令格式：命令格式： SQL SQL desc object_name ;desc object_name ;OracleOracle数据库管理与应用实例

9、教程数据库管理与应用实例教程其他常用命令其他常用命令(4)(4)继续使用上次命令继续使用上次命令命令格式：命令格式： SQL SQL / /(5)(5)清空屏幕清空屏幕命令格式：命令格式： SQL SQL cl scr; cl scr; (6)(6)命令命令 用于执行脚本文件。用于执行脚本文件。命令格式：命令格式： SQL SQL 文件名文件名; ;注意：需写文件路径；注意：需写文件路径；sqlsql文件的后缀可以不写。文件的后缀可以不写。OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程Oracle常用数据类型OracleOracle类型类型含义含义CHAR(lengt

10、h) CHAR(length) 存储固定长度的字符串。若存储的字符串长度较小，存储固定长度的字符串。若存储的字符串长度较小，在末尾填充空格。在末尾填充空格。VARCHAR2(length)VARCHAR2(length)存储可变长度的字符串。存储可变长度的字符串。LengthLength指明最大长度。若指明最大长度。若字符串长度较小，也不用填充空格。字符串长度较小，也不用填充空格。DATEDATE存储日期和时间。纪元、存储日期和时间。纪元、4 4位的年、月、日、时位的年、月、日、时(24(24小时制小时制) )INTEGERINTEGER存储整数。例如：存储整数。例如：1 1、1010、125

11、125NUMBER(NUMBER(precision,precision,scalescale) )存储浮点数，也可以用来存储整数。存储浮点数，也可以用来存储整数。PrecisionPrecision是是这个数字可以使用的最大位数，这个数字可以使用的最大位数，OracleOracle数据库支持数据库支持的最大精度是的最大精度是3838。ScaleScale表示小树点右边的最大位表示小树点右边的最大位数。数。BINARY_FLOATBINARY_FLOATOracleOracle 10g 10g提供的新类型，用来存储单精度的提供的新类型，用来存储单精度的3232位位浮点数。浮点数。BINARY_

12、DOUBLEBINARY_DOUBLE存储单精度的存储单精度的6464位浮点数。位浮点数。OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程4.1 示例数据库v OracleOracle数据库中有个默认用户数据库中有个默认用户SCOTTSCOTT，就是，就是Bruce ScottBruce Scott，而，而SCOTTSCOTT用户的默认密码用户的默认密码tigertiger，是当时，是当时 Scott Scott养的一养的一只猫的名字。只猫的名字。v scott.sqlscott.sql脚本文件位于脚本文件位于 $ORACLE_HOMERDBMSADMIN$ORACLE

13、_HOMERDBMSADMIN下下v 在在SQLSQL* *PLUSPLUS下，以下，以scottscott用户连接到用户连接到OracleOracle数据库，运行数据库，运行该脚本，则可建立该脚本，则可建立scottscott示例数据库。示例数据库。OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程什么是“方案”方案的概念方案的概念l方案，就是一系列数据库对象的集合，是数据库中方案，就是一系列数据库对象的集合，是数据库中存储数据的一个存储数据的一个逻辑表示或描述逻辑表示或描述。l在在Oracle 11g数据库中，每个用户都拥有自己的方数据库中，每个用户都拥有自己的方案，

14、创建了一个用户，就创建了一个同名的方案，案，创建了一个用户，就创建了一个同名的方案，方方案与数据库用户是对应案与数据库用户是对应的。的。 l用户在数据库中创建了一个方案对象后，这个方案用户在数据库中创建了一个方案对象后，这个方案对象默认地属于这个用户的方案。对象默认地属于这个用户的方案。 OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程scott方案中的表v雇员表（雇员表（empemp）OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程scott数据库中的表v部门表（部门表（deptdept）OracleOracle数据库管理与应用实例教程数据库管

15、理与应用实例教程scott数据库中的表v奖金表（奖金表（bonusbonus）OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程scott数据库中的表v工资等级表（工资等级表（salgradesalgrade）OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程4.2 SQL语言v结构化查询语言，是一种数据库查询和程序设结构化查询语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理计语言，用于存取数据以及查询、更新和管理关系数据库系统。关系数据库系统。分为分为3 3类类：1 1数据定义语言（数据定义语言（DDLDDL）用来定义和管理数

16、据库中的对象（如表、视图、用来定义和管理数据库中的对象（如表、视图、存储过程、触发器）。由存储过程、触发器）。由CREATECREATE、ALTERALTER、DROPDROP命命令构成令构成。OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程SQL语言2 2数据操纵语言（数据操纵语言（DMLDML）针对数据库中存储的数据进行相关操作，主要包括针对数据库中存储的数据进行相关操作，主要包括: : 增加（增加（insertinsert）删除（删除（deletedelete）更新（更新（updateupdate）查询（查询（selectselect）在实际工作中增加、删除和更新

17、所占使用在实际工作中增加、删除和更新所占使用DMLDML比例为比例为20%20%，而查询所占比例为而查询所占比例为80%80%。OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程SQL语言v3 3数据控制语言（数据控制语言（DCLDCL） 用来管理用户对指定数据库对象的使用权限。用来管理用户对指定数据库对象的使用权限。常用操作有分配（常用操作有分配（grantgrant）和回收（）和回收（revokerevoke）。）。OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程4.3 查询的基本语法 查询基本语法查询基本语法l数据查询是一种从数据库中检索符合

18、搜索条件的记录生成数据记录集数据查询是一种从数据库中检索符合搜索条件的记录生成数据记录集合，并把它们存入数据记录集对象中的操作。合，并把它们存入数据记录集对象中的操作。 OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程4.3.1 简单查询 选择列选择列1选择所有列选择所有列 2选择指定列选择指定列 3使用别名使用别名 4. 计算列计算列l【例例1-1】查询雇员的所有信息。查询雇员的所有信息。l【例例1-2】 人力资源管理人员在查询雇员信息时只需人力资源管理人员在查询雇员信息时只需要了解所有雇员的编号、姓名和工作职位以及雇佣日要了解所有雇员的编号、姓名和工作职位以及雇佣日

19、期。期。OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程l【例例1-31-3】 查询雇员的姓名、职位、雇佣日期和月薪，用查询雇员的姓名、职位、雇佣日期和月薪，用中文显示这些中文显示这些字段名。字段名。SELECT SELECT enameename AS AS 姓名姓名 , job AS , job AS 职位职位 , , hiredatehiredate AS AS 工作日期工作日期 , , salsal AS AS 工资工资 FROM FROM empemp ; ;v 【例例1-41-4】查看工资等级表查看工资等级表salgradesalgrade中同一等级最高工资

20、中同一等级最高工资和最低工资的差距，列的标题使用形象的别名。和最低工资的差距，列的标题使用形象的别名。SELECT 表达式表达式 AS 列别名列别名 FROM 数据源数据源使用列的别名使用列的别名 4.3.1 简单查询 OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程4.3.1 简单查询条件查询条件查询使用使用wherewhere子句过滤行子句过滤行l使用使用WHERE子句来指定想要查询的行。子句来指定想要查询的行。lWHERE后的条件可以是以下情况：后的条件可以是以下情况：l关系表达式l逻辑表达式l空值l用SQL操作符构造的条件 OracleOracle数据库管理与应

21、用实例教程数据库管理与应用实例教程4.3.1 简单查询 比较运算符比较操作符比较操作符说说 明明= =等于等于、!=!=不等于不等于=大于等于大于等于= 大于大于 小于小于ANYANY与一个列表中的任何值进行比较与一个列表中的任何值进行比较ALLALL与一个列表中的所有值进行比较与一个列表中的所有值进行比较OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程比较运算符vANYANY 在在WHEREWHERE子句中可以使用子句中可以使用ANYANY操作符将一个值与操作符将一个值与列表中的任何值作比较。列表中的任何值作比较。 ANYANY之前可以添加之前可以添加 = = 、 、

22、 、 = 或或 = = 例如例如： customer_idcustomer_idANY(2,3,4) ANY(2,3,4) 表示只要行的表示只要行的customer_idcustomer_id大于、中的任何一个都大于、中的任何一个都可以。可以。OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程比较运算符vALLALL 在在WHEREWHERE子句中可以使用子句中可以使用ALLALL操作符将一个值与操作符将一个值与列表中的所有值作比较。列表中的所有值作比较。 ALLALL之前可以添加之前可以添加 = = 、 、 、 = 或或 = = 例如例如： customer_idcus

23、tomer_idALL(2,3,4) ALL(2,3,4) 表示行的表示行的customer_idcustomer_id大于、中的所有值时，大于、中的所有值时，是满足要求的。是满足要求的。OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程4.3.1 简单查询条件查询示例条件查询示例l【例例1-5】 需要了解雇员中编号为需要了解雇员中编号为“7521”的雇员的雇员的所有信息。的所有信息。l【例例1-6】 需要职位为需要职位为CLERK或或ANALYST的雇员的雇员的编号、姓名和雇佣日期的编号、姓名和雇佣日期。OracleOracle数据库管理与应用实例教程数据库管理与应用实

24、例教程SQLSQL运算符运算符说说 明明BETWEENBETWEEN指定条件在两个值之间，包括边界值指定条件在两个值之间，包括边界值LIKELIKE匹配的字符样式，一般用于模糊查询匹配的字符样式，一般用于模糊查询ININ匹配的一个列表值匹配的一个列表值IS NULLIS NULL匹配空值匹配空值IS NAN匹配匹配NAN这个特殊值，意思是非数字（这个特殊值，意思是非数字（Oracle 10g新增的）新增的）IS INFINITE匹配无穷的匹配无穷的BINARY_FLOAT和和BINARY_DOUBLE（Oracle 10g新增的）新增的）SQLSQL操作符操作符4.3.1 简单查询注意：注意：

25、表中表中SQLSQL运算符可以与运算符可以与NOTNOT运算符运算符取反处理，取反处理，例如，例如，NOT LIKENOT LIKE，NOT BETWEENNOT BETWEEN和和IS NOT NULLIS NOT NULL等。等。OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程v模式匹配的字符：模式匹配的字符：匹配指定位置的一个字符。匹配指定位置的一个字符。匹配从指定位置开始的任意个字符。匹配从指定位置开始的任意个字符。例如：例如： _a% _a%表示第一个字符为任意，第二个字符为表示第一个字符为任意，第二个字符为a a，的字符串。，的字符串。LIKELIKE运算符

26、运算符OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程4.3.1 简单查询 条件查询示例条件查询示例l【例例1-7】 需要查询雇员名称中以需要查询雇员名称中以S开头的雇员的编开头的雇员的编号、姓名、职位和月薪信息。号、姓名、职位和月薪信息。l【例例1-8】 需要查询编号在（需要查询编号在（7369 , 7521 , 7789）中的雇员的信息。中的雇员的信息。l【例例1-9】需要查询没有获得奖金的雇员的信息。需要查询没有获得奖金的雇员的信息。l【例例1-10】需要查询月薪在需要查询月薪在1500-2000之间的雇员的之间的雇员的信息。信息。OracleOracle数据库管

27、理与应用实例教程数据库管理与应用实例教程4.3.1 简单查询 运算符运算符说明说明NOTNOT取反，当条件为真时，结果为假；当条件为取反，当条件为真时，结果为假；当条件为假时，结果为真。假时，结果为真。ANDAND与，当两个条件为真时，结果为真与，当两个条件为真时，结果为真OROR或，当两个条件中有一个为真时，结果为真或，当两个条件中有一个为真时，结果为真使用逻辑运算符可以将简单的条件组合起来。使用逻辑运算符可以将简单的条件组合起来。OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程4.3.1 简单查询 条件查询示例条件查询示例l【例例1-11 】需要查询月薪在需要查询月

28、薪在1500,2000 的雇员的信的雇员的信息。息。l【例例1-12】 需要查询职位为需要查询职位为CLERK或或SALESMAN 的雇员的信息。的雇员的信息。l【例例1-13】 需要查询月薪低于需要查询月薪低于1000或高于或高于2000的雇的雇员的信息。员的信息。OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程4.3.1 简单查询 选择行选择行- -去掉重复记录去掉重复记录l在输出查询数据记录集时，如果需要去掉重复的记在输出查询数据记录集时，如果需要去掉重复的记录，则可以使用录，则可以使用DISTINCT来实现。来实现。 l【例1-14】在雇员表中查询雇员来自哪些部

29、门，输查询雇员来自哪些部门，输出部门编号。出部门编号。l【例1-15】在雇员表中查询有哪些是属于管理者，查询有哪些是属于管理者，输出他们的员工编号。输出他们的员工编号。 OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程4.3.1 简单查询 选择行选择行- -查询结果排序查询结果排序l如果需要定制查询数据记录集的输出顺序，则可以使用如果需要定制查询数据记录集的输出顺序，则可以使用ORDER BY子句来实现。子句来实现。 ORDER BY子句能够按照指定的一个或多个列（表达式）的升子句能够按照指定的一个或多个列（表达式）的升序或降序来重新排列查询数据记录集的输出顺序。序或降

30、序来重新排列查询数据记录集的输出顺序。 l【例例1-16】需要查询雇员的信息，并按奖金由低到需要查询雇员的信息，并按奖金由低到高的顺序进行输出。高的顺序进行输出。l【例例1-17】需要查询雇员的奖金信息，并按奖金由需要查询雇员的奖金信息，并按奖金由低到高，月薪由高到低的顺序进行输出。低到高，月薪由高到低的顺序进行输出。 OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程4.3.1 简单查询 关于关于rowidrowid和和rownumrownumlrowidrowid 表中的每一行的唯一的行标识符。表中的每一行的唯一的行标识符。OracleOracle数据库内部数据库内部

31、使用行标识符来存储行的物理位置。它是一个使用行标识符来存储行的物理位置。它是一个1818位的数位的数字，使用字，使用base-64base-64编码。编码。lrownumrownum 是一个伪列，它返回每一行在结果集中的行号。例如，是一个伪列，它返回每一行在结果集中的行号。例如，查询返回的第一行的查询返回的第一行的rownumrownum是是1 1，第二行的，第二行的rownumrownum是是2 2。l【例例1-181-18】查询雇员表中雇员的详细信息，输出包含查询雇员表中雇员的详细信息，输出包含rowidrowid和和rownumrownum。 OracleOracle数据库管理与应用实例

32、教程数据库管理与应用实例教程4.3.1 简单查询 查询结果分组查询结果分组、聚合函数l通过使用通过使用GROUP BY子句来实现。如果需要在分组的基础上进行组的子句来实现。如果需要在分组的基础上进行组的过滤，则可以结合过滤，则可以结合GROUP BY子句再使用子句再使用HAVING子句来实现。对于需子句来实现。对于需要对查询数据集进行汇总以生成统计报表的情况，则需要使用要对查询数据集进行汇总以生成统计报表的情况，则需要使用COMPUTE子句。子句。 OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程4.3.1 简单查询 选择行选择行- -查询结果分组查询结果分组- -使用

33、聚合函数使用聚合函数l【例例1-19】查询每一个部门的雇员人数。查询每一个部门的雇员人数。l【例例1-20】查询同一工作职位的雇员的平均奖金和查询同一工作职位的雇员的平均奖金和平均月薪。平均月薪。l【例例1-21】查询雇员中获得的最高奖金和最低奖金查询雇员中获得的最高奖金和最低奖金数。数。l【例例1-22】查询工作职位为查询工作职位为CLERK或或SALESMAN的雇员的平均奖金和平均月薪。的雇员的平均奖金和平均月薪。OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程4.3.2 连接查询 连接查询连接查询u实际上，数据库实例中的各个表之间可能存在某些实际上，数据库实例中的

34、各个表之间可能存在某些内在内在关联关联，通过这些关联，可以为应用程序提供一些涉及多，通过这些关联，可以为应用程序提供一些涉及多个表的复杂信息，如主表和外表之间就存在主键和外键个表的复杂信息，如主表和外表之间就存在主键和外键的关联。的关联。u将存在关联的多个表连接起来进行查询，称为将存在关联的多个表连接起来进行查询，称为连接查询连接查询 u根据连接类型不同，可分为：根据连接类型不同，可分为： 内连接和外内连接和外连接连接OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程u内连接是内连接是使用比较运算符使用比较运算符作为连接条件的连接方式。内作为连接条件的连接方式。内连接作为

35、一种典型的默认连接方式，关键字连接作为一种典型的默认连接方式，关键字INNER默认默认提供。提供。u使用内连接方式时，只有那些满足连接条件的数据记录使用内连接方式时，只有那些满足连接条件的数据记录被显示，不满足连接条件的数据记录将不被显示。被显示，不满足连接条件的数据记录将不被显示。u根据连接条件中的关系运算符是否使用根据连接条件中的关系运算符是否使用“=”，内连接可，内连接可以分为以分为等连接等连接和和不等连接不等连接（使用较少）（使用较少）。连接查询连接查询内连接内连接4.3.2 连接查询 OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程u等连接的语法示例：等连接的

36、语法示例： SELECT SELECT 表表1.1.字段字段1,1,表表1.1.字段字段2,2,表表2.2.字段字段1 1 FROM FROM 表表1 1 INNER JOININNER JOIN 表表2 2 ONON 表表1.1.字段字段 = = 表表2.2.字段字段 WHEREWHERE ORDER BY ORDER BY SQL/92SQL/92标准下的内连接标准下的内连接4.3.2 连接查询 OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程4.3.2 连接查询 内连接内连接等连接等连接l【例例2-1】 需要查询每个员工的员工号、姓名和所在需要查询每个员工的员工号

37、、姓名和所在部门名称及地址。部门名称及地址。【语句语句1，SQL/86标准标准】 SELECT E.empno, E.ename, D.dname, D.locFROM emp E, dept DWHERE E.deptno= E.deptno;OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程4.3.2 连接查询 内连接内连接等连接等连接l【例例2-1】需要查询每个员工的员工号、姓名和所在需要查询每个员工的员工号、姓名和所在部门名称及地址。部门名称及地址。SQL/92标准下的写法：标准下的写法：SELECT E.empno, E.ename, D.dname, D.lo

38、cFROM emp E INNER JOIN dept DON E.deptno= D.deptno;使用使用USINGUSING子句简化子句简化( (等连接且列名相同等连接且列名相同) ) ： SELECT SELECT E.ename,E.job,E.sal,SG.gradeE.ename,E.job,E.sal,SG.grade FROM FROM empemp E INNER JOIN E INNER JOIN salgradesalgrade SG SG USING ( USING (deptnodeptno) )；OracleOracle数据库管理与应用实例教程数据库管理与应用实例

39、教程u连接的条件是除了连接的条件是除了= = 之外的操作符，包括：之外的操作符，包括：、 、= 、 = =、 LIKE LIKE、 IN IN、 BETWEEN BETWEEN。u不等连接示例：不等连接示例： 查询员工的姓名、职位、工资及工资级别。查询员工的姓名、职位、工资及工资级别。 内连接内连接不等连接不等连接4.3.2 连接查询 SQL/92SQL/92： SELECT SELECT E.ename,E.job,E.sal,SG.gradeE.ename,E.job,E.sal,SG.grade FROM FROM empemp E INNER JOIN E INNER JOIN sal

40、gradesalgrade SG SG ON ON E.salE.sal BETWEEN BETWEEN SG.losalSG.losal AND AND sg.hisalsg.hisal; ; OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程4.3.2 连接查询 自连接自连接若用于连接的两个表或视图来源于同一个表或视图，若用于连接的两个表或视图来源于同一个表或视图，这样的内连接也被称之为这样的内连接也被称之为自连接自连接。为了分别表示一个表与其自身，需要引入表的别名。为了分别表示一个表与其自身，需要引入表的别名。l【例例2-3】需要查询每个员工和其管理者的姓名。需要查

41、询每个员工和其管理者的姓名。SELECT E1.ename 员工员工,E2.ename 管理者管理者FROM emp E1 JOIN emp E2ON E2.mgr=E1.empno;SELECT E1.ename SELECT E1.ename 员工员工,E2.ename ,E2.ename 管理者管理者FROM FROM empemp E1,emp E2 E1,emp E2WHERE E2.mgr=E1.empno;WHERE E2.mgr=E1.empno;OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程4.3.2 连接查询 外连接查询外连接查询l即使连接中的列包

42、含一个空值，外连接也会返回一行。即使连接中的列包含一个空值，外连接也会返回一行。l在连接条件中可以使用外连接操作符来执行一个外连在连接条件中可以使用外连接操作符来执行一个外连接。接。l左外连接l右外连接OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程4.3.2 连接查询 外连接查询外连接查询- -左外连接左外连接l查询数据记录集包含来自一个表的所有数据记录和查询数据记录集包含来自一个表的所有数据记录和另一个表中的匹配数据记录的连接称为左外连接。另一个表中的匹配数据记录的连接称为左外连接。l对于左外连接，对于左外连接，第一个表第一个表中的所有数据记录将被显中的所有数据记录

43、将被显示，示，第二个表第二个表（匹配表）如果找不到相匹配的数据记（匹配表）如果找不到相匹配的数据记录，相应的列将显示为空值（录，相应的列将显示为空值（NULL），否则显示匹），否则显示匹配数据记录。配数据记录。例：查询每名员工的上级管理者。例：查询每名员工的上级管理者。SELECT E1.ename SELECT E1.ename 员工员工,E2.ename ,E2.ename 管理者管理者FROM FROM empemp E1 E1 LEFT JOIN LEFT JOIN empemp E2 E2ON ON E2.mgr=E1.empno; E2.mgr=E1.empno;OracleOra

44、cle数据库管理与应用实例教程数据库管理与应用实例教程4.3.2 连接查询 外连接查询外连接查询- -右外连接右外连接l查询数据记录集包含来自第二个表的所有数据记录查询数据记录集包含来自第二个表的所有数据记录和第一个表中的匹配数据记录的连接称为右外连接。和第一个表中的匹配数据记录的连接称为右外连接。l对于对于右外连接右外连接，第二个表中的所有数据记录将被显第二个表中的所有数据记录将被显示示，第一个表（匹配表）如果找不到相匹配的数据记，第一个表（匹配表）如果找不到相匹配的数据记录，相应的列将显示为空值（录，相应的列将显示为空值（NULL），否则显示匹），否则显示匹配数据记录。配数据记录。例：查询

45、员工的部门信息，将暂时没有分配部门的员工信息例：查询员工的部门信息，将暂时没有分配部门的员工信息也显示出来。也显示出来。SELECT SELECT E.empnoE.empno 员工编号员工编号, , E.enameE.ename 姓名姓名, ,D.dnameD.dname 部门部门FROM FROM empemp E E RIGHT JOIN RIGHT JOIN deptdept D DON ON E.deptnoE.deptno= =D.deptnoD.deptno; ;OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程4.3.4子查询 子查询子查询 概念概念l将一

46、个查询语句嵌套在另一个查询语句中的查询称为嵌套查询将一个查询语句嵌套在另一个查询语句中的查询称为嵌套查询或子查询。或子查询。l子查询语句一般嵌入在另一个查询语句的子查询语句一般嵌入在另一个查询语句的WHERE子句或子句或HAVING子句中，另外，子查询语句也可以嵌入在一个数据记录子句中，另外，子查询语句也可以嵌入在一个数据记录更新或删除语句中。更新或删除语句中。 例如：例如：SELECT empno,enameFROM empWHERE sal(SELECT AVG(hisal) FROM salgrade)例如：例如：UPDATE empSET sal=(SELECT AVG(hisal)

47、FROM salgrade)WHERE empno=7521例如：例如：DELETE FROM empWHERE sal(SELECT AVG(hisal) FROM salgrade)OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程4.3.4子查询 在在WHEREWHERE子句中使用子查询子句中使用子查询l语法格式为：语法格式为：WHERE 表达式表达式 操作符操作符（子查询）（子查询）说明：此处的操作符可以是说明：此处的操作符可以是 =、 = (SELECT AVG(sal) FROM emp)OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程

48、4.3.4子查询 ININ子查询子查询l对列表操作的子查询通过对列表操作的子查询通过IN关键字实现父查询和子关键字实现父查询和子查询之间的连接，判断查询之间的连接，判断指定列的值是否出现在子查询指定列的值是否出现在子查询的查询数据记录集中的查询数据记录集中。使用。使用IN的子查询语句返回的查的子查询语句返回的查询数据记录集一般由单列多行值组成，这也是子查询询数据记录集一般由单列多行值组成，这也是子查询中最常用的一种形式。中最常用的一种形式。对于使用对于使用IN的子查询的连接条件，其语法格式为：的子查询的连接条件，其语法格式为：WHERE 表达式表达式 NOT IN （子查询）（子查询）Orac

49、leOracle数据库管理与应用实例教程数据库管理与应用实例教程4.3.4 子查询 ININ子查询子查询【例例 4-2】假如学生选课系统有表假如学生选课系统有表student(sno,sname,sex, dept)、course(cno,cname,grade)、selCourse(sno,cno, score)查询参加（或未参加）选修的学生的学号、姓名。查询参加（或未参加）选修的学生的学号、姓名。OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程4.3.4 子查询 ANYANY或或ALLALL子查询子查询l【例例4-3】需要了解比职位为需要了解比职位为“CLERK”的

50、员工获得奖金数高的员工获得奖金数高的员工信息，查询结果按奖金数降序排列。的员工信息，查询结果按奖金数降序排列。【语句语句】SELECT * FROM emp WHERE comm ALL(SELECT comm FROM emp WHERE job=CLERK)ORDER BY comm DESC;OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程4.3.4 子查询 EXISTSEXISTS子查询子查询lEXISTS操作符用于检查子查询返回行的存在性。它并不关心子操作符用于检查子查询返回行的存在性。它并不关心子查询返回多少行，只关心子查询是否返回任何行。查询返回多少行，只

51、关心子查询是否返回任何行。l【例例4-4】 查询职位是查询职位是CLERK且有佣金的员工记录。且有佣金的员工记录。例如：例如：SELECT empno,enameFROM empWHERE job=CLERK AND comm IS NOT NULL;例如：例如：SELECT empno,enameFROM empWHERE EXISTS(SELECT * FROM empWHERE job=CLERK AND comm IS NOT NULL);OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程4.3.5 集合查询 UNION UNION 联合查询联合查询l 使用使用U

52、NION操作符可将两个表中的结果组合起来操作符可将两个表中的结果组合起来(不包括重复不包括重复行行)。l UNION ALL将两个表中的结果组合起来将两个表中的结果组合起来(包括包括重复行重复行)。例例【4-5】：查询：查询emp表及表及bonus表中员工的姓名、职位、月薪表中员工的姓名、职位、月薪和佣金。和佣金。SELECT ename, job, sal, commFROM emp UNION SELECT ename, job, sal, commFROM bonus;OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程4.3.5 集合查询 UNION UNION 联

53、合查询联合查询例例【4-6】：查询：查询emp表中员工姓名中包含表中员工姓名中包含S字符和字符和T字符的员工字符的员工信息。信息。SELECT empno,enameFROM emp WHERE ename LIKE %S%UNION SELECT empno,enameFROM emp WHERE ename LIKE %T%;OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程4.3.5 集合查询 INTERSECT INTERSECT 查询查询lINTERSECT为交运算，可取到两个表中的共同记录。为交运算，可取到两个表中的共同记录。l示例：示例：SELECT *FR

54、OM empWHERE ename LIKE S%INTERSECTSELECT * FROM empWHERE ename like SM%OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程4.3.5 集合查询 MINUS MINUS 查询查询lMINUS为差运算，可取到第一个表中不同于第二个为差运算，可取到第一个表中不同于第二个表的数据行。表的数据行。l例如：例如：SELECT empno,enameFROM empWHERE sal2500MINUSSELECT empno,ename FROM empWHERE sal1000 AND sal2600;Oracle

55、Oracle数据库管理与应用实例教程数据库管理与应用实例教程根据sql查询功能，结合Oracle的示例数据库，尝试各种查询操作，并编写对应的查询语句。 任务任务OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程4.4 数据定义v定义数据对象，包括：表、视图和索引。定义数据对象，包括：表、视图和索引。v在在OracleOracle数据库中，还有其他数据对象，如：触数据库中，还有其他数据对象，如：触发器、游标、过程、程序包等。发器、游标、过程、程序包等。vSQLSQL的数据定义语句：的数据定义语句：操作对象操作对象创建创建删除删除修改修改表表CREATE TABLECREAT

56、E TABLEDROPDROP TABLE TABLEALTER TABLEALTER TABLE视图视图CREATECREATE VIEW VIEWDROPDROP VIEW VIEW 索引索引CREATECREATE INDEX INDEXDROPDROP INDEX INDEXOracleOracle数据库管理与应用实例教程数据库管理与应用实例教程4.4.1创建表v创建表的创建表的简化简化SQLSQL语法：语法：CREATE TABLECREATE TABLE ( ( CONSTRAINT CONSTRAINT cnstraint_defcnstraint_def DEFAULT DEF

57、AULT default_expdefault_exp , , CONSTRAINT CONSTRAINT constraint_defconstraint_def DEFAULT DEFAULT default_expdefault_exp ););OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程Oracle常用数据类型OracleOracle类型类型含义含义CHAR(length) CHAR(length) 存储固定长度的字符串。若存储的字符串长度较小，存储固定长度的字符串。若存储的字符串长度较小，在末尾填充空格。在末尾填充空格。VARCHAR2(length)VA

58、RCHAR2(length)存储可变长度的字符串。存储可变长度的字符串。LengthLength指明最大长度。若指明最大长度。若字符串长度较小，也不用填充空格。字符串长度较小，也不用填充空格。DATEDATE存储日期和时间。纪元、存储日期和时间。纪元、4 4位的年、月、日、时位的年、月、日、时(24(24小时制小时制) )INTEGERINTEGER存储整数。例如：存储整数。例如：1 1、1010、125125NUMBER(NUMBER(precision,precision,scalescale) )存储浮点数，也可以用来存储整数。存储浮点数，也可以用来存储整数。PrecisionPreci

59、sion是是这个数字可以使用的最大位数，这个数字可以使用的最大位数，OracleOracle数据库支持数据库支持的最大精度是的最大精度是3838。ScaleScale表示小树点右边的最大位表示小树点右边的最大位数。数。BINARY_FLOATBINARY_FLOATOracleOracle 10g 10g提供的新类型，用来存储单精度的提供的新类型，用来存储单精度的3232位位浮点数。浮点数。BINARY_DOUBLEBINARY_DOUBLE存储单精度的存储单精度的6464位浮点数。位浮点数。OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程LOB数据类型v 用于大型的、

60、未被结构化的数据，如二进制文件、图片文用于大型的、未被结构化的数据，如二进制文件、图片文件等。件等。LOBLOB数据类型又分为数据类型又分为BLOBBLOB、CLOBCLOB和和BFILEBFILE三种。三种。v BLOBBLOB类型：用于存储二进制对象。如图像、音频、视频。类型：用于存储二进制对象。如图像、音频、视频。v CLOBCLOB类型：用于存储字符格式的大型对象。类型：用于存储字符格式的大型对象。OracleOracle将数据将数据转换成转换成UnicodeUnicode格式。格式。v BFILEBFILE类型：将二进制文件作为操作系统文件存储在数据类型：将二进制文件作为操作系统文件