Oracle的简介经本人修改

1、一、 Oracle的简介Oracle最大最流行的数据库。(收购java sun公司)My SQL(sun):小型（因为Oracle涉嫌垄断而保留）SQLServer(2008):微软公司，中型。Net平台结合紧密Oracle:拉里.矮里森DB2：IBM(蓝色巨人)PC，服务器（大型数据库）二、 Oracle的安装与卸载1. Oracle的安装【建议】这些服务尽量使用手动的方式启动，否则会影响系统的启动时间。OracleOrgDb10G_HOME1TNSListener:表示监听服务，如果客户端想连接数据库，此服务必须打开，在日后的程序开发中，此服务起作用OracleServiceXXX:表示数

2、据库的主服务，命名规则OracleService数据库名，此服务必须启动，否则Oracle无法使用。2. Oracle的卸载Oracle的卸载最好按如下步骤完成：（1） 直接运行卸载程序（2） 删除硬盘上的残留程序，如果删除不了，则进入安全模式下进行删除。（3） 直接进入注册表中，删除与Oracle相关的配置项。（4） 终极方法？3. 监听问题监听服务是Oracle数据库中最主要的一个服务，但这个服务会经常出问题，所以下面给出最常见的两种解决方案：（1） 注册表使用了优化软件，删除了相关选项。对于每一个服务，都会在注册表中有所保存，那么Oracle监听服务的注册项是：HKEY_LOCAL_MA

3、CHINESYSTEMCurrentControlSetServicesOracleOraDb10g_home1TNSListener，里面有一个“ImagePath”的选项，这个选项有可能在使用优化软件的时候被删除，如果被删除，则应按照如下的方式建立这个项，并写上相应的内容：D:oracleproduct10.2.0db_1BINTNSLSNR【建议】尽量不要使用优化大师这类的优化软件4. sqlplus命令（重点）对于Oracle数据库操作主要使用命令行方式，而所有的命令都使用sqlplus完成，对于sqlplus有两种方式：l 一种是dos风格的sqlplus: sqlplus.exe;

4、l 一种是windows风格的sqlplus: sqlplusw.exe;在Oracle10g之中主要使用的是sqlplusw命令，因为其可以进行一些显示数据的调整（这些调整对于以后的程序开发而言没有任何意义，只是显示风格的区别）。【掌握两个格式设置命令】Set linesize 300; /设置每行显示的记录长度Set pagesize 30; /设置每页显示的记录长度这两个命令在sqlplus下的设置没什么效果。虽然sqlplusw显示上更方便一些，但是对于命令的编辑上却不是很方便，特别是修改错误的拼写时。所以在sqlplusw中提供了ed和指令，可以进行记事本的调用和执行。l 使用ed指

5、令，调用记事本程序：ed a;l 使用指令，执行记事本中的查询：a;我们也可以使用编辑软件，在外部编辑程序，那么在sqlplusw中调用外部文件时，必须制定完整路径：d:test.txt;【查看表的命令】Select * from tab;【查看当前登录用户的命令】Show user;Select * from session_privs;【切换登录用户的命令】Conn 用户名/密码 as sysdba(超级管理员);DBA:数据库系统管理员（5年以上的Oracle维护经验）【注意】当我们切换到 SYS 用户的时候，则不能直接查询EMP表，因为EMP表属于具体的某个用户，如果需要用SYS身份去

6、访问具体的某个用户的某个表的时候，需要采用如下的方式：Select * from用户名.表名一旦使用超级管理员身份登录，我们可以使用命令手工关闭或打开数据库：超级管理员关闭只能超级管理员开启关闭数据库命令：SHUTDOWN IMMEDIATE;一旦关闭数据库之后，则无法使用正常的用户进行sqlplusw命令登录，那么我们可以先使用sqlplus登录，再使用超级管理员身份登录，然后再启动数据库，启动数据库的命令是：用sqlplus 登陆sys/ change as sysdba账号，然后输入如下命令：STARTUP;三、 SQL语法学习(一)scott用户的表结构【查看表结构命令】DESC 表名

7、；（二）SQL简介SQL(Strutctured Query Language，结构化查询语言)是一个功能强大的数据库语言。SQL通常用于与数据库的通讯。ANSI(美国国家标准学会)声称，SQL是关系数据库管理（面向对象型的数据库：不成熟）（使用面向对象的开发语言和关系型数据库之间进行交互的时候，会产生一种阻抗不匹配，为了避免这种不匹配 ，后面要学习一种ORM（Object RelationShip Mapping）的框架，有hql语句）系统的标准语言。SQL功能强大，概括起来，它可以分成以下几类：l DML(Data Manipultation Language，数据操作语言):用于检索或修

8、改数据。（数据的增删改查）l DDL(Data Definition Language,数据定义语言):用于定义数据的结构，如创建、修改或者删除数据库对象（表、序列、索引、视图）。l DCL(Data Control Language，数据控制语言)：用于定义数据库用户的权限。（三）简单查询简单查询是指查询出一张表中的所有数据。简单查询的基本格式：SELECT DISTINCT * |字段列名,| FROM 表名别名【范例】查询出dept表中的全部记录Select * from dept;【范例】查询出每个雇员的编号、姓名、基本工资Select empno,ename,salFrom emp;

9、【范例】从EMP表中查询出所有的职位Select distinct（不同的，独一无二） jobFrom emp;【注意】 在简单查询中，可以使用四则运算的运算符。+-*/【范例】查询每个员工的姓名、职位、年薪。 Select ename,job,sal*12 (as) income From emp;【注意】as可以省略，别名尽量不要使用中文在简单查询中，也可以使用“|”连接查询的字段【范例】|的使用(表示将查询结果连接起来)Select ename|job from emp;Select ename|,|job from emp;注意，单引号表示的是字符串。【范例】要求数据按如下的方式显示：

10、“雇员编号是：xxx的雇员姓名是：nnn,基本工资是：$,职位是:ABC!” Select 雇员编号是：|empno| 的雇员姓名是：|ename| ,基本工资是：|sal| ,职位是:|job From emp;（四）限定查询限定查询就是在之前的语法基础上增加了一个WHERE子句，用于指定限定条件，此时的语法如下：SELECT DISTINCT *| 字段 别名 ， FROM 表名称 别名 where 限定条件;在WHERE子句之后可以增加多个条件，最常见的条件就是基本的关系运算：>、<、>=、<=、!=(<>)、BETWEE.AND、LIKE、IN、IS

11、 NULL、AND、OR、NOT;1.关系运算【范例】查询月薪大于1500的员工信息Select * From empWhere sal>1500;【范例】查询所有职位是办事员（CLERK）的职员信息Select *From empWhere jOb=CLERK;注意Oracle里面的数据是区分大小写（字段名不区分大小写）。字符串要用” ”分开【范例】查询所有月薪大于等于1100的办事员的员工信息SELECT * FROM empWhere sal>=1100 and job=CLERK;【范例】查询出所有办事员或销售人员的员工信息SELECT * FROM EMPWHERE jo

12、b=CLERK OR JOB=SALESMAN;【范例】查询出工资大于1300的办事员或销售人员的员工信息SELECT * FROM EMPWHERE (job=CLERK OR JOB=SALESMAN) AND SAL>1300;注意: 两个条件相连如果条件过长最好用括号括起来;【范例】查询所有职位不是办事员的员工的信息。SELECT *FROM EMPWHERE JOB <>CLERK;2.范围判断：BETWEENAND【范例】要求查询出工资在1500到3000范围之内的员工信息。Select * from emp where sal between 3000 and

13、1500; 注意 BETWEENAND(包含边界)不仅只对数字有效，对日期也是有效的。（日期格式要参照数据库里数据的具体信息）【范例】要求查询出1981年雇佣的所有员工的信息。Select *From empWhere hiredate between 01-1月-81 and 31-12月-81;3.判断是否为空：IS(NOT) NULL使用此语法可以判断某一个字段上的内容是否是”null”,注意null和数字0以及空字符串是两个概念。【范例】查询出所有领取奖金的员工信息Select * From empWhere comm is not null;4.指定范围的判断：IN(NOT IN)操

14、作符IN操作符表示指定一个查询的范围【范例】查询出雇员编号是：7369、7566、7799的雇员信息。 Select *From empWhere empno in (7369,7566,7799);5.模糊查询：LIKE子句(重点)LIKE子句提供了模糊查询的功能，但注意，和搜索引擎的查询是不同的。LIKE子句中常用的两个匹配符号：l 匹配单个字符：_l 匹配任意多个字符：%【范例】要求查询雇员姓名中以字母A开头的全部雇员信息。Select *From empWhere ename like A%;【范例】要求查询出雇员姓名中第二个字母是A的全部雇员信息。Select *From empWh

15、ere ename like _A%;【范例】要求查询出雇员姓名中带有字母A的雇员Select *From empWhere ename like %A%;也可以使用NOT操作符，对操作进行求反的功能：【范例】要求查询出雇员姓名中不带有字母A的雇员Select *From empWhere ename not like %A%;Lucene:全文搜索引擎：基于索引(如同字典里的偏旁索引或拼音索引);(数据库里的sql语句查询是逐条查询)SEO:搜索引擎优化（）（五）数据的排序使用ORDER BY子句指定所需字段的排序，排序的语法格式：SELECT DISTINCT *| 字段 别名 ， FRO

16、M 表名称 别名 WHERE 条件(s) ORDER BY 字段 ASC|DESC , 字段 ASC|DESC , 注意 ORDER BY子句是写在所有的SQL语句最后的内容，而且对于排序有以下几点说明：l 排序的时候可以指定多个排序的字段；l 排序的方式有两种：升序（ASC）默认,降序（DESC）【范例】要求查询出所有的雇员信息，按照工资由高到低排序，如果工资相同，则按照雇佣日期有早到晚排序。Select * From empOrder by sal desc,hiredate;（六）单行函数的使用虽然各个数据库都是支持SQL语句的，但是每一个数据库也有每一个数据库自己所支持的操作函数，这就

17、是单行函数，而如果要想进行数据库开发的话，除了要会使用SQL之外，还要多学习函数。单行函数主要分为以下五类：字符函数、数字函数、日期函数、转换函数、通用函数。1.字符函数字符函数的功能主要是进行字符串数据的操作，下面给出几个字符函数：（对查出的字符串结果进行操作，数据本身并无变化）l UPPER(字符串|列)：将输入的字符串变为大写返回；l LOWER(字符串|列)：将输入的字符串变为小写返回；l INITCAP(字符串|列)：开头首字母大写；l LENGTH(字符串|列)：求出字符串的长度；l REPLACE(字符串|列)：进行替换；l SUBSTR(字符串|列,开始点结束点)：字符串截取；

18、【范例】观察转小写的操作，将所有的雇员姓名按照小写字母返回注1：查询结果列名是按查询列名显示的，如下面的例子,列名是lower(name) ,通常我们会用as（可省）重命名。注2： 函数都是对“列名”进行操作，对显示的列的结果就行操作。Select lower(ename) from emp;【范例】将每一个雇员姓名的开头首字母大写 Select initcap(ename) from emp;【范例】查询出每个雇员姓名的长度Select ename,length(ename) from emp;【范例】要求查询出姓名长度正好是5的雇员信息 Select * From empWhere len

19、gth(ename)=5;【范例】使用字母”_”替换掉姓名中的所有字母“A”Select replace(ename,A,_) from emp;字符串截取操作有两种语法：l SUBSTR(字符串|列，开始点)：表示从开始点一直截取到结尾；Eg: SELECT ename,SUBSTR(ename,3) FROM emp;l SUBSTR(字符串|列，开始点，截取多少)：表示从开始点截取到结束点，截取部分内容；Eg: SELECT ename,SUBSTR(ename,0,3) FROM emp; SELECT ename,SUBSTR(ename,1,3) FROM emp;【范例】要求截取

20、每个雇员姓名的后三个字母<1>正常思路：通过长度-2确定开始点 Select ename,substr(ename,length(ename) -2) from emp;<2>新思路：设置负数，表示从后指定截取位置；Select ename,substr(ename,-3) from emp;注：（面试题）问substr下标是从1开始还是从0开始截取的。答：都一样；（其实是从1开始的，这样好理解）2.数字函数l ROUND(数字|列,保留小数的位数)：四舍五入的操作。l TRUNC(数字|列,保留小数的位数)：舍弃指定位置的内容。（注：就是不进位）l MOD(数字1 ，

21、数字2)：取模，取余数。【范例】验证ROUND函数SELECT ROUND(903.53576),ROUND(-903.53576),ROUND(903.53576,2), ROUND(903.53576,-1) FROM dual;负数表示从整数开始截取。无参数表示省略小数部分内容。 注意 dul是Oracle数据库为用户方便，提供的一个虚拟表。【验证TRUNC函数】SELECT TRUNC (903.53576), TRUNC (-903.53576), TRUNC (903.53576,2), TRUNC (903.53576,-1) FROM dual;【验证MOD函数】SELECT

22、MOD(10,3) FROM dual;3.日期函数要取得当前的日期，可以使用”SYSDATE”取得。Eg:SELECT SYSDATE FROM dual;在日期中也可以进行若干计算：日期 +/- 数字=日期，表示若干天之后/之后的日期。Eg:SELECT SYSDATE+3 FROM dual;【范例】求出每个雇员到今天为止的雇佣天数 Select ename, trunc(sysdate-hiredate) From emp;对于日期，还提供了如下四个操作函数：l LAST_DAY(日期)：求出指定日期所属月份的最后一天；【范例】求出本月的最后一天的日期SELECT LAST_DAY(S

23、YSDATE) FROM dual;l NEXT_DAY(日期，星期数)：求出指定日期下一个指定星期X的日期；注：星期数如果用汉字就对应，如果用数字按外国算法，星期日算是1；【范例】求出下一个周一的日期SELECT NEXT_DAY(SYSDATE,星期一) FROM dual;l ADD_MONTHS(日期，数字)：求出若干个月之后的日期；Eg:SELECT ADD_MONTHS(SYSDATE,4) FROM dual;l MONTHS_BETWEEN(日期1，日期2)：求出两个日期之间所经历的月数l【范例】求出每个雇员到今天为止的雇佣月数SELECT ename,hiredate,MON

24、THS_BETWEEN(SYSDATE,hiredate) FROM emp;SELECT ename,hiredate,TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate) FROM emp;【注意】在所有的开发之中，如果是日期的操作，建议使用以上的函数，因为这些函数可以避免闰年的问题。注：用到自己建的表的信息就无需使用daul表了；依然是操作日期字段。4.转换函数（核心）转换函数主要完成数据间的相互转换的操作，一共有三种转换函数：l TO_CHAR(字符串|列，格式字符串)：将日期或者是数字变为字符串显示；l TO_DATE(字符串，格式字符串)：将字符串变为DAT

25、E数据显示；l TO_NUMBER(字符串)：将字符串变为数字显示；<1>TO_CHAR()函数系统时间是按照“日-月-年”的格式显示，如果想要换成正常的“年-月-日”格式显示，这种情况下可以使用TO_CHAR()函数，有些常用的格式字符串需要大家记住：年（yyyy）,月（mm）,日(dd)。Eg:SELECT TO_CHAR(SYSDATE,yyyy-MM-dd hh-mi-ss) from dual;如果需要去掉前导0，可以加入一个fm来实现。Eg:SELECT TO_CHAR(SYSDATE, fmyyyy-mm-dd) from dual;要想显示时间，则需要增加标记：Eg

26、:SELECT TO_CHAR(SYSDATE,fmyyyy-mm-dd hh:mi:ss) day FROM dual;【注意】使用TO_CHAR()函数之后，所有的内容都是字符串，不再是之前的DATE型数据。TO_CHAR()函数也可以用于数字的格式上，这个时候每一个“9”表示一位数字的概念，而不是数字9的概念。Eg:select to_char(32884858585,L999,999,999,999,999) from dual;其中的字母“L”表示的是“Local”的含义，即当前所在的语言环境下的货币符号。<2>TO_DATE()函数此函数的主要功能是将一个字符串变为DA

27、TE类型数据。Eg:select to_date(1999-03-22,yyyy-mm-dd) from dual;一般此函数在更新数据库的时候使用较多.<3>TO_NUMBER()函数：用的较少TO_NUMBER()函数是将字符串变数字。Eg:select to_number(1)+to_number(2) from dual;Eg:select 1+2 from dual;5.通用函数（核心）通用函数主要有两个：NVL()、DECODE()，这两个函数算是Oracle自己的特色函数。<1>NVL()函数，处理null(将null换为0);【范例】要求查询出每个雇员的

28、全部年薪SELECT ename,sal,comm,(sal+comm)*12 from emp;修改：Select ename, (sal+NVL(comm,0)*12 from emp;<2>DECODE()函数：多数值判断DECODE()函数非常类似于程序中的ifelse语句，唯一不同的是DECODE()函数判断的是数值，而不是逻辑条件。此函数的语法如下：DECODE(数值|列，判断值 1，显示值1，判断值 2，显示值2，判断值 3，显示值3，.。【范例】要求显示全部雇员的职位，但是这些职位要求替换为中文显示Select empno,ename,job,DECODE(job,

29、CLERK,办事员,SALESMAN,销售员,MANAGER,经理,ANALYST,分析员,PRESIDENT,总裁) from emp;DECODE()函数是整个Oracle之中最具有特点的函数，一定要将其掌握。【习题作业】1.选择部门30中的所有员工。 Select * from emp where deptno=30;2.列出所有办事员（CLERK）的姓名、编号和部门编号。 Select ename,empno,deptno From empWhere job=CLERK;3.找出奖金高于薪金的员工。Select *From empWhere comm>sal;4.找出佣金高于薪金

30、60%的员工Select *From empWhere comm>sal*0.6;5.找出部门10中所有经理和部门20中所有办事员的职员信息。 Select *From empWhere (deptno=10 and job=MANAGER) or (deptno=20 and job=CLERK);6.找出部门10中所有经理（MANAGER）,部门20中所有办事员（CLERK）,既不是经理又不是办事员但其薪金大于或等于2000的所有员工的详细资料。SELECT *FROM empWhere (deptno=10 and job=MANAGER) OR (deptno=20 and jo

31、b=CLERK) or (job not in(MANAGER,CLERK) and sal>=2000);7.找出收取佣金的员工的不同工作 Select distinct job From emp Where comm is not null; 8.找出不收取佣金或收取的佣金低于100的员工 Select * From empWhere comm is null or comm<100;9.找出各月倒数第三天受雇的所有员工每一个雇员的雇佣日期肯定是不一样的，所以现在必须找到每一个雇员受雇佣所在月的最后一天，之后按照“日期-数字”的方式求出前三天的日期，这个日期必须和受雇佣日期相符

32、合才满足条件。 Select * From emp Where hiredate=(last_day(hiredate)-2);10找出早于30年前受雇佣的员工信息 Select *From empWhere months_between(sysdate,hiredate)/12>30;11.以首字母大写的方式显示所有员工的姓名 Select initcap(ename) from emp;12.显示正好为5个字符的员工的姓名 Select ename from emp where length(ename)=5;13.显示不带有“R”的员工的姓名Select enameFrom emp

33、Where ename not like %R%;14.显示所有员工姓名的前三个字符。 SELECT SUBSTR(ENAME,1,3) FROM EMP;15.显示所有员工的姓名，用“a”替换所有”A” Select replace(ename,A,a) FROM EMP;16.显示满10年服务年限的员工的姓名和受雇日期Select *From empWhere months_between(sysdate,hiredate)/12>10;17.显示员工的详细资料，按姓名排序 Select * from emp order by ename;18.显示员工的姓名和受雇日期，按照受雇日期

34、排列，将老员工排在前面 Select ename,hiredate From emp Order by hiredate;19.显示所有员工的姓名、工作和薪金，按工作的降序排列，若工作相同则按薪金排序。 Select ename,job,sal From emp Order by job desc,sal;20.显示所有员工姓名、加入公司的年份和月份，按受雇日期所在月排序，若月份相同则将最早年份的员工排在最前面。Select ename,hiredate, to_char(hiredate,yyyy) year,to_char(hiredate,mm) m From empOrder by m

35、,year;21.显示在一个月为30天的情况所有员工的日薪金，忽略余数。 Select trunc(sal/30)From emp;22.找出在（任何年份的）2月受雇的所有员工。 Select * from emp where to_char(hiredate,mm)=2;23.对于每个员工，显示其加入公司的天数。Select ename,trunc(sysdate-hiredate) from emp;24.显示姓名字段的任何位置包含“A”的所有员工的姓名Select ename from emp where ename like %A%;（七）多表查询（重点）1.多表查询的基本概念在之前所

36、使用的查询操作之中，都是从一张表中查询出所需要的内容，那么如果在一个查询语句中要显示多张表的数据，则必须使用多表查询的操作，而多表查询的语法如下：SELECT DISTINCT *| 字段 别名 ， FROM 表名称 别名 ,表名称 别名, WHERE 条件(s) ORDER BY 字段 ASC|DESC , 字段 ASC|DESC , 【笛卡尔积问题】我们执行SELECT * FROM emp,dept;这个多表查询语句，会发现查询出56条记录，笛卡尔积在数据库中表示的是多张表的数据乘积。使用字段关联，可以消除笛卡尔积的问题。Select *From emp e,dept dWhere e.

37、deptno = d.deptno;【注意】 当在多表查询之中，不同的表中有相同字段名称的时候，访问这些字段时，必须加上表名称，即“表名.字段”。(在开发中，经常需要给表起别名)SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno;SELECT * FROM emp e,dept d WHERE e.deptno = d.deptno;需要注意的是，使用多表查询的时候，如果遇到数据量很大的时候，多表查询的性能是很差的，因此在大数据量的时候，尽量少用多表查询。（本质：其实多表查询的这种消除笛卡尔积的方式只是从笛卡尔积中取出符合条件的记录，在数据库

38、查询时依然做了笛卡尔积）【范例】查询出每一位雇员的编号、姓名、职位、部门名称、部门所在位置(几段式) Select e.empno,e.ename,e.job,d.dname,d.loc From emp e,dept d Where e.deptno=d.deptno;【范例】查询出每一位雇员的姓名、职位、其领导的姓名Select e.ename,e.job,e2.ename（同一张表，两张表的效果）From emp e,emp e2Where e.mgr=e2.empno;【范例】查询出每个雇员的编号、姓名、基本工资、职位、领导的姓名、部门名称及位置。Select e.empno,e.en

39、ame,e.sal,e.job,e2.ename,d.dname,d.locFrom emp e,emp e2,dept dWhere e.mgr=e2.empno and e.deptno=d.deptno;【思考题】要求查询出每一个雇员的编号、姓名、工资、部门名称、工资所在公司的工资等级 Select e.empno,e.ename,e.sal,d.dname,s.grade From emp e,dept d,salgrade sWhere e.deptno=d.deptno and e.sal between s.losal and s.hisal;（注：要看关联的是什么）【作业】将上

40、一个范例中的每一个工资等级替换成具体的文字信息，例如1替换成第五等工资、2替换成第四等工资Select e.empno,e.ename,e.sal,d.dname,decode(s.grade,1,第五等工资,2,第四等工资) From emp e,dept d,salgrade sWhere e.deptno=d.deptno and e.sal between s.losal and s.hisal;2.左右连接左右连接指的是参考表的方向问题，参考左边表还是参考右边表，在Oracle中，使用“（+）”来表示左右连接，这种符号有以下两种使用情况：l (+)=:放在等号的左边，表示右连接，表示

41、参考右边的表(把右边表中的所有数据都查出来)l =(+):放在等号的右边，表示左连接，表示参考左边的表（把左边表中的所有数据都查出来）通常情况下，我们不用刻意去区分左还是右，可以根据查询结果而定，如果发现有些需要的数据没有显示出来，就使用此符号更改连接方向。【范例】查询每个雇员的姓名和领导的姓名 Select e1.ename,e2.enameFrom emp e1 ,emp e2Where e1.mgr=e2.empno(+);【注意】(+)是Oracle数据库自己所独有的，其他数据库不能使用。3.SQL:1999语法处了以上的表左右连接操作之外，在SQL语法之中，也提供了另外一套用于表连接

42、的操作SQL，格式如下：SELECT table1.column, table2.column FROM table1CROSS JOIN table2 | NATURAL JOIN table2 | JOIN table2 USING(column_name) | JOIN table2 ON(table1.column_name=table2.column_name) | LEFT|RIGHT|FULL OUTER JOIN table2 ON (table1.column_name=table2.column_name);(1)交叉连接（CROSS JOIN）:用于产生笛卡尔积 Sele

43、ct * from emp cross join dept;=select * from emp,dept;(2)自然连接（NATURAL JOIN）：自动找到匹配的关联字段，消除掉笛卡尔积Select * from emp natural join dept;并不是所有的字段都是关联字段，设置关联字段需要通过约束指定；（3）JOIN.USING子句：用户自己指定一个消除笛卡尔积的关联字段 Select * from emp join dept using (deptno);(4)JOINON子句：用户自己指定一个可以消除笛卡尔积的关联条件 Select * from emp e,dept d

44、 where e.deptno=d.deptno;Select * from emp e join dept d on (e.deptno = d.deptno);(5)连接方向的改变：l 左（外）连接：LEFT OUTER JOINON;l 右（外）连接：RIGHT OUTER JOINON;l 全（外）连接：FULL OUTER JOIN.ON;Select e1.ename,e2.enameFrom emp e1 left outer join emp e2On( e1.mgr=e2.empno);在Oracle之外的数据库都使用SQL:1999语法操作，所以这个语法还必须会。（八）统计

45、函数及分组查询（重点、难点）结合使用1. 常用的统计函数(又称为分组函数)有：l COUNT():查询表中的数据记录；l AVG():求平均值；l SUM():求和；l MAX():求最大值；l MIN():求最小值；【范例】统计出公司的所有雇员，每个月平均支付的平均工资及总工资 Select avg(sal),sum(sal) From emp;【范例】统计雇员中的最高和最低工资 Select max(sal),min(sal) from emp; 【注意】关于COUNT()函数 Select count(distinct job) from emp;COUNT()函数的主要功能是进行数据的

46、统计，但是在进行数据统计的时候，如果一张表中没有统计记录，COUNT()也会返回数据，只是这个数据是“0”；SELECT COUNT(ename) FROM BONUS;如果使用的是其他函数，则有可能返回null,但是COUNT()永远都会返回一个具体的数字。2.分组统计 分组统计主要使用GROUP BY 子句来完成，分组的SQL语法如下：SELECT DISTINCT * | 分组字段1 别名 ,分组字段2 别名 | 统计函数FROM 表名称 别名 ， 表名称 别名 WHERE 条件(s) GROUP BY 分组字段1 分组字段2,. ORDER BY 排序字段 ASC|DESC 排序字段

47、ASC|DESC 【范例】按照部门编号分组，求出每个部门的人数，平均工资Select count(empno),avg(sal)From empGroup by deptno【范例】按照职位分组，求出每个职位的最高和最低工资 Select job, max(sal),min(sal) From emp Group by job;【注意】一旦分组之后，对于语法上就会出现新的限制，对于分组有以下要求：l 分组函数可以在没有分组的时候单独使用，但不能出现其他的查询字段，如下的错误示例： Select count(job) ,job from emp;因为无法显示l 如果要进行分组，则SELECT 子

48、句之后，只能出现分组的字段（原因是：按它分的组）和统计函数，其他的字段不能出现：正确：错误：SELECT deptno,job,COUNT(empno),AVG(sal) FROM emp GROUP BY job;l 分组函数允许嵌套，但是嵌套之后的分组函数的查询之中不能再出现任何的其他字段。【范例】按照职位分组，统计平均工资最高的工资Select max(avg(sal)这个位置 From emp（上句话按嵌套函数举例）Group by job;【范例】查询出每个部门的名称、部门的人数、平均工资 Select d.dname,count(e.empno),avg(e.sal) From d

49、ept d,emp eWhere d.deptno=e.deptnoGroup by d.dname;（注意逻辑顺序）【范例】要求显示每个部门的编号、部门名称、位置、部门的人数、平均工资Select d.deptno,d.dname,d.loc,count(e.empno),avg(e.sal)From dept d,emp eWhere d.deptno = e.deptnoGroup by d.deptno,d.dname,d.loc;（后两个可以在分组中列出来，所以可以补充不满足第二条件的条件）【范例】要求显示平均工资大于2000的部门的编号、部门名称、位置、部门的人数、平均工资 Sel

50、ect d.deptno,d.dname,d.loc,count(e.empno),avg(e.sal) From dept d,emp eWhere d.deptno=e.deptno and avg(e.sal)>2000Group by d.deptno,d.dname,d.loc;(错误)显示错误：不允许使用分组函数 之所以会出现这个错误是因为WHERE子句只能用来对单行而不是行组进行过滤。（因为要先分组因，后分组过滤果）要过滤行组，可以使用HAVING子句。语法如下：SELECT DISTINCT * | 分组字段1 别名 ,分组字段2 别名 | 统计函数FROM 表

51、名称 别名 ， 表名称 别名 WHERE 条件(s) GROUP BY 分组字段1 分组字段2,. HAVING 分组后的过滤条件（可以使用统计函数） ORDER BY 排序字段 ASC|DESC 排序字段 ASC|DESC 修改上面的范例结果：Select d.deptno,d.dname,d.loc,count(e.empno),avg(e.sal) From dept d,emp eWhere d.deptno=e.deptno Group by d.deptno,d.dname,d.locHaving avg(e.sal)>2000;【注意点】WHERE 和 HAVING 的区别

52、l WHERE：是在执行GROUP BY 操作之前进行的过滤，表示从全部数据之中筛选出部分的数据，在WHERE之中不能使用统计函数；l HAVING:是在GROUP BY分组之后的再次过滤，可以在HAVING子句中使用统计函数；【综合范例】显示非销售人员工作名称以及从事同一工作雇员的月工资的总和，并且要满足从事同一工作的雇员的月工资合计大于1500，输出结果按月工资的合计升序排列；Select e.job,sum(e.sal)From emp e Where e.job<>SALESMANGroup by e.jobHaving sum(e.sal)>1500Order by

53、 sum(e.sal); （九）子查询（重点、核心）子查询=简单查询+限定查询+多表查询+统计查询的综合体通常多表查询不建议大家使用，因为其性能较差，但是多表查询最有利的替代者就是子查询，所以子查询在实际的开发之中使用的相当多。所谓子查询就是在一个查询之中嵌套了其他的若干查询，嵌套子查询之后的查询SQL语句如下：SELECT DISTINCT * | 分组字段1 别名 ,分组字段2 别名 | 统计函数( SELECT DISTINCT * | 分组字段1 别名 ,分组字段2 别名 | 统计函数FROM 表名称 别名 ， 表名称 别名 WHERE 条件(s) GROUP BY 分组字段1 分组字段2,. HAVING 分组后的过滤条件（可以使用统计函数） ORDER BY 排序字段 ASC|DESC 排序字段 ASC|DESC )FROM 表名称 别名 ， 表名称 别名 ( SELECT DISTINCT * | 分组字段1 别名 ,分组字段2 别名 | 统计函数FROM 表名称 别名 ， 表名称 别名 WHERE 条件(s) GROUP BY 分组字段1 分组字段2,. HAVING 分组后的过滤条件（可以使用统计函数） ORDER BY 排序字段 ASC|DESC 排序字段 ASC|DESC ) WHERE 条件(s) ( SELECT DISTINCT