蓝桥学习数据库笔记

数据库Oracle笔记

白马非马

2016年9月11日

第1章数据库Oracle4

一、主流数据库4

二、结构化查询语言4

三、Oracle安装4

第2章查询和排序4

一、数据查询4

1.基本语句4

2.字段别名4

3.连字运算符||5

4.去重复行DISTINCT5

5.限定行WHERE5

6.优先规贝5

上课实例：5

二、排序6

第3-1章单行函数7

一、字符函数7

二、数字函数8

三、日期函数9

四、转换函数10

五、通用函数11

习题讲解12

第3-2章多表查询15

一、多表查询15

上课实例：16

二、内连接与外连接16

A.内连接16

B.外连接17

上课实例：18

补充：19

第4章子查询和组函数19

一、子查询19

1.单行子查询20

2.多行子查询21

上课实例：22

二、组函数（统计函数）23

1、统计函数23

2、分组查询23

注意点：WHERE和HAVING的区别26

上课实例：26

第5-1章操作数据27

一、数据增加28

上课实例：28

二、数据修改28

上课实例：29

2

三、数据删除29

上课实例：29

四、事务处理30

五、数据伪列30

第5-2章表的管理33

一、常用的数据字段33

二、表的创建34

三、表的复制35

四、为表重命名35

五、表的截断36

六、表的删除36

七、Oracle10g的新特性：闪回技术36

八、修改表结构37

九、思考题38

上课实例：39

第5-3章约束39

一、非空约束(NOTNULL)：NK39

二、唯一约束(UNIQUE)：UK40

三、主键约束(PrimaryKey)：PK41

四、检查约束(Check)：CK42

五、主-外键约束43

六、修改约束46

七、查询约束47

上课实例：48

第6章视图、序列、索引48

-、视图48

二、同义词50

三、索引51

四、集合52

五、序列53

上课实例：55

第7章JDBC55

・、通过名称登录55

二、查询Oracle数据库57

三、更新Oracle数据库数59

3

第1章数据库Oracle

一、主流数据库

l.Oracle数据库

2.MySQL数据库

3.SQLServer

4.DB2

二、结构化查询语言

SQL包括四部分

数据定义语言（DDL），用于定义SQL模式、表、视图、索引等数据库对象结构。

数据操作语言（DML）,用于插入、删除和更改数据。

数据查询语言（DQL）,用于查询数据、通常将数据操作语言和数据查询语言系统统称为数据

操作语言。

数据控制语言（DCL）,用于对表、视图等的授权、完整性规则的而描述和事务控制等。

三、Oracle安装

略

第2章查询和排序

一、数据查询

1.基本语句

1.SELECT字段列表FROM表名MHERE条件

2.ORDERBY字段名1[ASC|DESC][，字段名2[ASC|DESC]...];

*：表示选择表中的所有字段

字段名：选择表中的字段名称，可以选择多个字段，各个字段间用逗号分隔。

表达式：山字段、函数等组成的表达式。

表名：指定包含字段的表。

WHERE条件：查询的条件，可以通过该条件进行选择。

ORDERBY字段名：要求在查询的结果中进行排序，默认是升序ASC,降序DESC。

2.字段别名

在SELECT所选字段后面可以指定别名，字段名和别名之间用空格分开。在默认情况下,

别名标题用大写字母显示，如果别名中包含空格或特殊字符（列如#或&）,或者大小写敏

感，需要在别名放在双引号中。最好加匕AS关键字。

4

3.连字运算符||

使用连字运算符，可以进行字段与字段、字段与表达式、字段与常数之间的连接，来创建

•个字符表达式，连字运算符两边的字段被合成一个单个的列输出。

4.去重复行DISTINCT

SelectDISTINCT...FROM...

5.限定行WHERE

简单比较条件：=,后三个都是不等于）

BETWEEN...AND...:查询显示上下限之间的行。

IN：IN条件也称为成员条件，用以查询出所选字段中符合指定的一组值中的一个。

LIKE：LIKE条件也称为通配符，可以使用两个通配符来构造需要匹配的字符模板，其中“％”

表示零个或多个字符,表示一个字符，这里提到的字符可以说文字也可以是数字。

■需要匹配“％”和“，时，可以使用ESCAPE选项，该选项指定换码符是什么。如：

想搜索emp表中jobjd包含“AD_”的雇员职位编号、名字和薪水信息，可以用下面SQL

语句：

SELECTjob_id;name,salFROMempWHEREjobjdLIKE'%AD$_%'ESCAPE*';

此时ESCAPE选项指定美元符“$”为换码符，即不将美元符号后面的"」当作通配符看待。

Null|包括isnull条件和isnotnull条件。判断空值时不能使用“="，因为NUII不能等于或

不等于任何值。

逻辑andornot:与或非。

6.优先规则

默认的优先顺序：

（1）算数运算

（2）连字运算

（3）比较运算

（4）Is[not]null,like、[not]in«

（5）[not]betweeno

（6）Not逻辑条件。

（7）And逻辑条件。

（8）Or逻辑条件。

上课实例：

一查询全部员I：的全部信息

select*fromEMP;

一查询全部员工的部分信息

selectenarne,jobfromemp;

--查询所有员工薪水在800~1500的员工

select*fromempwheresalbetween800andl500;

select*fromempwheresal>=800andsal<=1500;

一查询所有员工工资大于1500的员工

5

select*fromempwheresal>1500;

--查询所有员工姓名中带A的员工

select*fromempwhereenamelike'%A%,;

--查询所有员工工资大于500且奖金大于500的员工信息

select*fromempwheresal>500andcomm>500;

一查询所有员工工资大于1500或奖金大于1000的员工信息

select*from叩wheresal>1500orcomm>1000;

--查询所有员工中姓J的员工工资大于1500或奖金大于1000的员工

select*fromempwhereenamelike'J%'and(sal>1500orcomm>1000);

一查询所有员工中姓名带A的员工的并按入职日期降序排序

select*fromempwhereenamelike'%A%'orderbyhiredatedesc;

一查询所有员工的月薪和奖金都按降序排序

select*fromemporderbysaldesc,commdesc;

一查询所有奖金为空的员工信息

select*fromempwherecommisnull;

一杳询所有发奖金的员工信息

select*fromempwherecommisnotnull;

--给列名起别名，给表名起别名

selectenameas"姓名"，salas"月薪"fromscott.emps

wheres.enamelike'%A%'andsal>1500orderbysaldesc;

--查询月薪为500,1000,1500,2000

select*fromempwheresalin(500,1000,1500,2000);

二、排序

要在一个不明确顺序的查询结果中对行进行排序，可以使用ORDERBY子句，它必须位于SQL

语句的最后。指定一个表达式、或者一个字段名、作为排序条件。ORDERBY子句的语法：

ORDERBY{COLUMN,EXPR}[ASC|DESC]

针对不同的数据类型，其默认升序：

1.对于数字类型，小的值在前面显示.

2.对于日期类型，早的日期在前面。

3.对于字符类型，a在前，z在后。

4.对于空值，升序在最后。降序在最前。

6

第3-1章单行函数

单行函数主要分为以下五类：字符函数、数字函数、日期函数、转换函数、通用函数；

一、字符函数

字符函数的功能主要是进行字符串数据的操作，下面给出几个字符函数：

・UPPER(字符串|列)：将输入的字符串变为大写返回；

・LOWER(字符串|列)：将输入的字符串变为小写返回；

・INITCAP(字符串|列)：开头首字母大写；

・LENGTH(字符串|歹U)：求出字符串的长度；

•REPLACE(字符串|歹I」)：进行替换；

・SUBSTR(字符串|歹开始点［，结束点］)：字符串截取；

Oracle之中有一点比较麻烦，即使要验证字符串，也必须编写完整的SQL语句，所以在

Oracle数据库之中为了用户查询方便，所以专门提供了•个“dual”的虚拟表。

范例：观察转大写的函数

SELECTUPPER('hello')FROMdual;

范例：观察转小写的操作，将所有的雇员姓名按照小写字母返回

SELECTLOWER(ename)FROMemp;

范例：将每一个雇员姓名的开头首字母大写

SELECTINITCAP(ename)FROMemp；

范例：查询出每个雇员姓名的长度

SELECTename,LENGTH(ename)FROMemp；

范例：要求查询出姓名长度正好是5的雇员信息

SELECTename,LENGTH(ename)FROMempWHERELENGTH(ename)-5；

范例：使用字母“一”替换掉姓名中的所有字母“A”

SELECTREPLACE(ename,'A',)FROMemp；

字符串截取操作有两种语法：

语法一：SUBSTR(字符串|歹ij,开始点)，表示从开始点一直截取到结尾；

SELECTename,SUBSTR(enamez3)FROMemp；

语法二：SUBSTR(字符串|歹ij,开始点，结束点)，表示从开始点截取到结束点，截

取部分内容：

SELECTename,SUBSTR(ename,0,3)FROMemp；

7

SELECTename,SUBSTR(ename,1,3)FROMemp；

范例：要求截取每个雇员姓名的后三个字母

・正常思路：通过长度-2确定开始点

SELECTename,SUBSTR(ename,LENGTH(ename)-2)FROMemp；

・新思路：设置负数，表示从后指定截取位置；

SELECTename,SUBSTR{ename,-3)FROMemp；

面试题：请问SUBSTR。函数截取的时候下标从0还是从1开始？

・在Oracle数据库之中，SUBSTR()函数从0或1开始都是一样的；

・SUBSTR。也可以设置为负数，表示由后指定截取的开始点；

二、数字函数

数字函数•共有三个：

・ROUND(数字|列［，保留小数的位数］)：四舍五入的操作；

•TRUNC(数字|列［，保留小数的位数］)：舍弃指定位置的内容；

・MOD(数字1,数字2)：取模，取余数；

范例：验证ROUND。函数

SELECTROUND(903.53567),ROUND(-903.53567),ROUND(903.53567,2),

ROUND(-90353567,1)FROMdual;

ROUND(90353567)ROUND(-90353567)ROUND(90353567.2)ROUND(-90353567.-1)|

范例：验证TRUNC。函数

SELECTTRUNC(903.53567),TRUNC(-903.53567),TRUNC(903.53567,2),

TRUNC(-90353567,1)FROMdual;

TRUNC(903.53567)|TRUNC(-903.53567)|TRUNC(903.53567.2)|TRUNq-90353567.-1)

范例：取模操作

SELECTMOD(10,3)FROMdual;

8

三、日期函数

如果现在要想进行日期的操作，则首先有一个必须要解决的问题，就是如何取得当前的日期,

这个当前日期可以使用“SYSDATE”取得，代码如下：

SELECTSYSDATEFROMdual;

除了以上的当前日期之外，在日期中也可以进行若干计算：

・II期+数字=日期，表示若干天之后的II期；

SELECTSYSDATE+3,SYSDATE+300FROMdual;

・日期一数字=日期，表示若干天前的日期；

SELECTSYSDATE3,SYSDATE300FROMdual;

・日期一日期=数字，表示的是两个日期间的天数，但是肯定是大日期一小日期;

范例：求出每个雇员到今天为止的雇佣天数

SELECTename,hiredate,SYSDATEhiredateFROMemp；

而且很多的编程语言之中，也都会提出一种概念，日期可以通过数字表示出来。

除了以上的三个公式之外，也提供了如下的四个操作函数：

・LAST_DAY（日期）：求出指定日期的最后一天；

范例：求出本月的最后•天日期

SELECTLAST_DAY（SYSDATE）FROMdual;

・NEXT_DAY（日期，星期数）：求出下一个指定星期X的日期；

范例：求出下一个周一

SELECTNEXT_DAY（SYSDATE,1一星期''）FROMdual;

・ADD_MONTHS（日期，数字）：求出若干月之后的日期；

范例：求出四个月后的日期

SELECTADD_MONTHS（SYSDATE,4）FROMdual;

・MONTHS_BETWEEN（日期1,日期2）：求出两个日期之间所经历的月份；

范例：求出每个雇员到今天为止的雇佣月份

SELECTename,hiredate,TRUNC（MONTHS_BETWEEN（SYSDATE,hiredate））FROMemp；

在所有的开发之中，如果是日期的操作，建议使用以上的函数，因为这些函数可以避免闰年

的问题。

9

四、转换函数

现在已经接触到了Oracle数据库之中的三种数据：数字(NUMBER)、字符串

(VARCHAR2),H期(DATE),转换函数的主要功能是完成这几种数据间的互相转换

操作，-共有三种转换函数：

・TO_CHAR(字符串|歹U，格式字符串)：将日期或者是数字变为字符串显示；

・TO_DATE(字符串，格式字符串)：将字符串变为DATE数据显示；

・TO_NUMBER(字符串)：将字符串变为数字显示；

a、TO_CHAR()函数

在之前查询过当前的系统11期时间：

SELECTSYSDATEFROMdual;

这个时候是按照“日-月-年”的格式显示，很明显这种显示格式不符合正常的思路，正常是“年

-月-日”，所以这种情况下可以使用TO_CHAR()函数，但是使用此函数的话需要一些格式

字符串：年(yyyy),月(mm),日(dd)。

SELECTTO_CHAR(SYSDATE,'yyyy-mm-dd'),TO_CHAR(SYSDATE,'yyyy')year,

TO_CHAR(SYSDATE,'mm')month,TO_CHAR(SYSDATE,'dd')dayFROMdual;

TO_CHAR(SYSDASE,'YYYY-MM-DD厂丫EARMONTHDAY

►1

但是这个时候的显示数据之中可以发现会存在前导0,如果要想消除掉这个0的话，可以加

入一个"fm”。

SELECTTO_CHAR(SYSDATE,'fmyyyy-tntn-dd')dayFROMdual；

正常人都加0,所以这个标记知道就行了，可是在Oracle之中，DATE里面是包含了时间

的，但是之前的代码没有显示出时间，要想显示时间则需要增加标记：

SELECTTO_CHAR(SYSDATE,1fmyyyy-mm-ddhh24:mi：ss')dayFROMdual;

一定要注意，使用TO_CHAR()函数之后，所有的内容都是字符串，不再是之前的DATE

型数据，TO_CHAR()函数也可以用于数字的格式化上，这个时候每一个“9”表示一位数字

的概念，而不是数字9的概念。

SELECTTO_CHAR(89078907890,'L999,999,999,999,999')FROMdual;

10

TO_CHAR(89078907890.'L999.999.

¥89,078,907.890

其中的字母“L”，表示的是“Local”的含义，即：当前的所在的语言环境下的货币符号。

b、TODATE()函数

此函数的主要功能是将一个字符串变为DATE型数据。

SELECTTO_DATE(,1989-09-12',•yyyy-mm-dd,)FROMdual;

TO_DATE('1989-09-12'/YYYY-MM-

1—

c、TONUMBER()函q：基本不用

TO_NUMBER()函数•看就知道是将字符串变数字的：

SELECTTO_NUMBER('1')+TO_NUMBER('2')FROMdual;

但是在Oracle之中真的很智能，所以以上的功能不使用TO_NUMBER()也可完成:

SELECT'l'+'2'FROMdual;

以上结果为：3

所以现在的TO_NUMBER()函数基本上已经是不考虑了，重点的函数在TO_CHAR()上,

其次是TO_DATE()函数。

五、通用函数

通用函数主要有两个：NVL()、DECODEO,这两个函数算是Oracle自己的特色函数了;

a,NVL()函金，处理null

范例：要求查询出每个雇员的全部年薪

SELECTename,sal,comm,(sal।comm)*12FROMemp；

ENAMESALCOMMl(SAL+COMM)*12

1

2ALLEN1600003000022B00

3

4JONES2975.00

5

6

7

11

这个时候有的雇员的年薪就变成了null,而造成这种问题的关键是在于comm字段上为

null,那么要想解决这个问题，就必须做一种处理：将null变为0,而这个就是NVL()函

数的作用。

SELECTename,sal,comm,(sal+NVL(comm,0))*12ZNVL(comm,0)FROMemp；

ENAMESALCOMM(SAL+NVL(COMM,0)f12NVL(COMM,0)

1I9600|

2ALLEN1600003000022800300|

31

4JONES297500357000

5

b、DECODE。函数：多数值判断

DECODE。函数非常类似于程序中的if…else…语句，唯不同的是DECODE。函数判断

的是数值，而不是逻辑条件。

例如，现在要求显示全部雇员的职位，但是这些职位要求替换为中文显示:

・CLERK：办事员；

・SALESMAN：销售；

•MANAGER：经理；

・ANALYST：分析员；

・PRESIDENT：总裁；

这种判断肯定是逐行进行判断，所以这个时候就必须采用DECODE。,而此函数的语法如

下：

DECODE(数值|列，判断值1,显示值1,判断值2,显示值2,判断值3,显示值3,...)

范例：实现显示的操作功能

SELECTempno,ename,job,DECODE(jobJCLERK一办事员—SALESMAN一销售人员

'JMANAGER一经理一ANALYST'J分析员一PRES工DENT一总裁i)FROMemp；

EMPNOENAMEJOBDECODEtJOB/CLERK17.

1

2

3

4

5

习题讲解

1、选择部门30中的所有员工。

SELECT*FROMempWHEREdeptno-30;

12

2、列出所有办事员(CLERK)的姓名，编号和部门编号。

SELECTempnozename,deptnoFROMempWHEREjob='CLERK'；

3、找出佣金高于薪金的员工。

SELECT*FROMempWHEREcomm>sal;

4、找出佣金高于薪金的60%的员工。

SELECT*FROMempWHEREcomm>sal*0.6;

5、找出部门10中所有经理(MANAGER)和部门20中所有办事员(CLERK)的详细资

料.

SELECT*FROMempWHERE(job'MANAGER'ANDdeptno10)OR(job'CLERK'ANDdeptno20);

6、找出部门10中所有经理(MANAGER),部门20中所有办事员(CLERK),既不是

经理又不是办事员但其薪金大于或等于2000的所有员工的详细资料。

SELECT*FROMempWHERE(job='MANAGER'ANDdeptno=10)OR(job='CLERK'AND

deptno20)OR(jobNOTIN(1MANAGER','CLERK')ANDsal>=2000);

7、找出收取佣金的员工的不同工作。

SELECTDISTINCTjobFROMempWHEREcommISNOTNULL;

8、找出不收取佣金或收取的佣金低于100的员工。

SELECT*FROMempWHEREcommISNULLORcomm<100;

9、找出各月倒数第3天受雇的所有员工。

每一个雇员的雇佣日期肯定是不…样的，所以现在必须找到每个雇员雇佣所在月的最后一

天，之后按照“日期-数字”的方式求出前三天的日期，这个日期必须和雇佣日期相符合才满

足条件。

SELECT*FROMempWHERELAST_DAY(hiredate)-2hiredate；

10、找出早于12年前受雇的员工。

如果要求年份，最准确的做法是使用总月数/12；

SELECT*FROMempWHEREMONTHS_BETWEEN(SYSDATE,hiredate)1212;

11、以首字母大写的方式显示所有员工的姓名。

SELECTINITCAP(ename)FROMemp；

12、显示正好为5个字符的员工的姓名。

SELECTenameFROMempWHERELENGTH(ename)=5;

13

13、显示不带有“R”的员工的姓名。

SELECTenameFROMempWHEREenameNOTLIKE'%R%'；

14、显示所有员工姓名的前三个字符。

SELECTSUBSTR(ename,0,3)FROMemp；

15、显示所有员工的姓名，用“a”替换所有“A。

SELECTREPLACE(ename,'A1,(a')FROMemp；

16、显示满10年服务年限的员工的姓名和受雇日期。

SELECTenamezhiredateFROMempWHEREMONTHS_BETWEEN(SYSDATEzhiredate)/12>10;

17、显示员工的详细资料，按姓名排序。

SELECT*FROMempORDERBYename;

18、显示员工的姓名和受雇日期，根据其服务年限，将最老的员工排在最前面。

SELECTename,hiredateFROMempORDERBYhiredate；

19、显示所有员工的姓名、工作和薪金，按工作的降序排序，若工作相同则按薪金排序。

SELECTename,job,salFROMempORDERBYjobDESC,sal；

20、显示所有员工姓名、加入公司的年份和月份，按受雇日期所有月排序，若月份相同则

将最早年份的员工排在最前面。

本程序需要从日期之中取出年份和月份，用TO_CHAR()函数完成。

SELECTename,TO_CHAR(hiredatez'yyyy')year,TO_CH2VR(hiredate,'mm')monthsFROM

empORDERBYmonthszyear；

21、显示在一个月为30天的情况所有员工的日薪金，忽略余数。

SELECTename,sal,TRUNC(sal/30)FROMemp；

22、找出在(任何年份的)2月受聘的所有员工。

SELECT*FROMempWHERETO_CHAR(hiredate,'mm')2;

23、对于每个员工，显示其加入公司的天数。

SELECTenamezSYSDATEhiredateFROMemp;

24、显示姓名字段的任何位置包含“A”的所有员工的姓名。

SELECTenameFROMempWHEREenameLIKE'%A%';

25、以年月II的方式显示所有员工的服务年限。

14

第一步：求出每个雇员的雇佣年数：被雇佣的总月数/12=年数：

SELECTename,hiredate,

TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)/12)yearFROMemp；

第二步：求出月数，以上计算之中被忽略的小数点实际上都是月份，所以直接取余即可；

SELECTename,hiredate,

TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)12)year,

TRUNC(MOD(MONTHS_BETWEEN(SYSDATE,hiredate),12))monthsFROMemp；

第三步：求出天数，最准确的做法是在不超过30天的范围之内求；

现在已经知道当前的时间使用SYSDATE取出，而雇佣的日期使用hiredate取出，可是

hiredate和SYSDATE之间的差距太大了，所以肯定会有误差，那么就必须想办法将

hiredate的11期提升到与SYSDATE差距在30天的范围之内。

在之前学习过两个函数：

・MONTHS_BETWEEN()：求出两个日期间的月数，如果是：

MONTHS_BETWEEN(SYSDATE,hiredate)求出的是雇佣日期到今天为止的雇佣月

份；

・ADD_MONTHS()：在一个II期上加入指定的月之后的II期,如果说hiredate+与

今天相距的月数=一个新的日期，而且这个新的日期肯定和SYSDATE相距不超过

30天。

SELECTenamezhiredate,

TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)/12)yea-,

TRUNC(MOD(MONTHS_BETWEEN(SYSDATE,hiredate)z12))monthsz

TRUNC(SYSDATEADD_MONTHS(hiredatezMONTHS_BETWEEN(SYSDATE,hiredate)))dayFROM

emp；

以上的这道程序，属于日期函数的综合应用。

第3-2章多表查询

一、多表查询

在之前所使用的查询操作之中，都是从一张表之中查询出所需要的内容，那么如果现在一个

查询语句需要显示多张表的数据,则就必须应用到多表查询的操作，而多表查询的语法如下:

SELECT［DISTINCT］*|字段as［别名］［，字段as［别名］,...］

FROM表名称［别名］,［表名称［别名］,...］

［WHERE条件(S)］

15

[ORDERBY排序字段[ASC|DESC][,排序字段[ASC|DESC],...]];

条件(s)：e.deptno-d.deptnoand...

上课实例：

一查询某部门员工姓名和部门名

selecte.ename,d.dname^e.deptnofromscott.empe,,scott.deptd

wheree.deptno=d.deptnoande.deptno=20;

--取出某员工姓名和所属部门名

selecte.enamed.dname^e•deptnofromscott.empe,,scott.deptd

wheree.ename:'JONES'ande.deptno=d.deptno;

--子查询

--查询部门名称包含A开头的部门员工姓名和部门编号及部门名称

selecte.enamed«deptnod.dnamefromscott.empe,scott.deptd

whered.deptnoin(selectdeptnofromscott.deptwhered.dnamelike'A%')

ande.deptno=d.deptno;

二、内连接与外连接

表TESTA,TESTB,TESTC,各有A,B两列

AB

00110A

00220A

AB

00110B

00330B

AB

00110C

00440C

连接分为两种：内连接与外连接。

A.内连接

内连接，即最常见的等值连接，例:

SELECT*

FROMTESTA.TESTB

WHERETESTA.A=TESTB.A

16

结果

ABAB

00110A00110B

上课实例：

一内连接innerjoinon拿出两张表里完全可以关联的数据

select*fromempeinnerjoindeptdond.deptno=e»deptno;

B.外连接

外连接分为左外连接，右外连接和全外连接。

1.左外连接leftouterjoin或者leftjoin

左外连接就是在等值连接的基础上加上主表中的未匹配数据，例：

SELECT*

FROMTESTA

LEFTOUTERJOINTESTB

ONTESTA.A=TESTB.A

Oracle支持另一种写法

SELECT.

FROMTESTA,TESTB

WHERETESTA.A=TESTB.A(+)

结果:

ABAB

00110A00110B

00220A

三个表做左外连接

SELECT*

FROMTESTA

LEFTOUTERJOINTESTB

ONTESTA.A=TESTB.A

LEFTOUTERJOINTESTC

ONTESTA.A=TESTC.A

Oracle支持的另外一种写法

SELECT*

FROMTESTA,TESTB,TESTC

WHERETESTA.A=TESTB.A(+)

ANDTESTA.A=TESTC.A(+)

结果:

ABABAB

00110A00110B00110C

00220A

2.右夕卜连接rightouterjoin或者rightjoin

右外连接是在等值连接的基础上加上被连接表的不匹配数据

17

SELECT*

FROMTESTA

RIGHTOUTERJOINTESTB

ONTESTA.A=TESTB.A

Oracle支持的另一种写法

SELECT*

FROMTESTA,TESTB

WHERETESTA.A(+)=TESTB.A

结果:

ABAB

00110A00110B

00330B

3.全外连接

fullouterjoin或者fulljoin

全外连接是在等值连接的基础上将左表和右表的未匹配数据都加上

SELECT*

FROMTESTA

FULLOUTERJOINTESTB

ONTESTA.A=TESTB.A

全外连接的等价写法，对同一表先做左连接，然后右连接

SELECTTESTA.*,TESTB.*

FROMTESTA

LEFTOUTERJOINTESTB

ONTESTA.A=TESTB.A

UNION

SELECTTESTA.*,TESTB.*

FROMTESTB

LEFTOUTERJOINTESTA

ONTESTA.A=TESTB.A

结果:

ABAB

00110A00110B

00220A

00330B

上课实例：

--外连接

--右连接rightjoinon

select*fromemperightjoindeptdone.deptno=d.deptno;

--左连接leftjoinon

select*fromempeleftjoindeptdone.deptno=d・deptno;

--全连接fullouterjoinon

select*fromempefullouterjoindeptdone.deptno=d.deptno;

18

补充：

a.交叉连接(CROSSJOIN)：用于产生笛卡尔积

SELECT*FROMempCROSSJOINdept;

笛卡尔枳本身并不是属于无用的内容，在某些情况下还是需要使用的。

b.自然连接(NATURALJOIN)：自动找到匹配的关联字段，消除掉笛卡尔积

SELECT*FROMempNATURALJOINdept;

但是并不是所有的字段都是关联字段，设置关联字段需要通过约束指定；

CJOIN...USING子句：用户自己指定一个消除笛卡尔积的关联字段

SELECT*FROMempJOINdeptUSING(deptno);

dJOIN…ON子句：用户自己指定•个可以消除笛卡尔积的关联条件

SELECT*FROMempJOINdeptON(emp.deptno=dept.deptno);

e.连接方向的改变：

・左(夕卜)连接：LEFTOUTERJOIN...ON;

・右(外)连接：RIGHTOUTERJOIN...ON;

・全(外)连接：FULLOUTERJOIN.ON;-＞把两张表中没有的数据都显示

SELECT*FROMempRIGHTOUTERJOINdeptON(emp.deptno=dept.deptno);

再次强调：多表查询的性能肯定不高，而且性能一定要在大数据量的情况下才能够发现。

第4章子查询和组函数

一、子查询

子查询=简单查询+限定查询+多表查询+统计查询的综合体；

在之前强调过多表查询不建议大家使用，因为性能很差，但是多表查询最有利的替代者就是

子查询，所以子查询在实际的开发之中使用的相当的多；

所谓的子查询指的就是在一个查询之中嵌套了其他的若干查询，嵌套子查询之后的查询

SQL语句如下：

SELECT［DISTINCT］*分组字段1［别名］［，分组字段2［别名］，…］统计函数，(

19

SELECT［DISTINCT］*分组字段［［别名］［，分组字段2［别名］,...］统计函数

FROM表名称［别名］,［表名称［别名］,...］

［WHERE条件（S）］

［GROUPBY分组字段1［,分组字段2，…］］

［HAVING分组后的过滤条件（可以使用统计函数）］

［ORDERBY排序字段ASC|DESC［,排序字段ASC|DESC］］）

FROM表名称［别名］,［表名称［别名］,（

SELECT［DISTINCT］*分组字段1［别名］［，分组字段2［别名］，…］统计函数

FROM表名称［别名］,［表名称［别名］，…］

［WHERE条件