Oracle数据库查询简析

原创

没文化 2018-09-13 09:53:12 ©著作权

文章标签 Oracle 查询 文章分类 运维

©著作权归作者所有：来自51CTO博客作者没文化的原创作品，请联系作者获取转载授权，否则将追究法律责任

一、环境准备

   操作系统：CentOS6.8  数据库：Oracle 11.2.0.4（企业版）
	   客户端工具：Oracle SQL Developer

二、简单查询

简单查询：查询一张表的所有数据 语法格式： SELECT [DISTINCT] * 字段 [别名][字段[别名]] FROM 表名称 [别名];

范例：查询dept表全备记录 SELECT * FROM dept; 范例：查询出每个雇员编号、姓名、基本工资 SELECT empno,ename,sal FROM emp;

范例：查询每个雇员职位 SELECT job FROM emp; 这个时候发现查询处理的job内容中出现了重复的数据，可以用DISTINCT 消除掉所有重复内容： SELECT DISTINCT job FROM emp; 但是，对于重复数据，指的是一行的每个列的记录都重复，才叫重复数据。范例：查询每个雇员姓名、职位 SELECT DISTINCT ename,job FROM emp;

 在进行查询操作中，可以使用各个属性的size运算符

范例：显示每个雇员姓名、职位、基本年薪 SELECT ename,job,sal12 FROM emp; 这个时候显示一个“SAL12”,ZHEG 肯定显示的是查询列，但是这个列名称不方便浏览，所有此时可以起一个别名。 SELECT ename,job,sal*12 AS income FROM emp;

范例：由于公司福利好，每个月都有200元的饭食补助以及100元车费补助，这个时候年薪： SELECT ename,job, (sal+200+100)*12 AS income FROM emp;

公司每年的年底多发一个月基本工资 SELECT ename,job, (sal+200+100)*12+sal AS income FROM emp; 范例：观察“||”的使用 SELECT empno || ',' || ename FROM emp; 由于“，”属于原样输出字符串，所以必须使用“，”括起来，即：在SQL语句之中，“，”表示的是字符串。范例：要求限制的数据安装如下格式显示： SELECT '雇员编号是：' || empno || '雇员姓名是：' || ename || ',基本工资是：' || sal || ',职位是：' || job AS 雇员信息 FROM emp;

三、限定查询

在之前的简单查询之中，是将所有的记录显示，但是现在可以对显示的记录镜像过来操作，这就是限定查询。限定查询就是在之前语法的基础

上增加了一个WHERE子句，用户限定条件，语法如下： SELECT [DISTINCT] * 字段 [别名][字段[别名]] FROM 表名称 [别名] [WHERE 条件(S)]; 在WHERE自己之后可以增加多个条件，最常见的条件就是基本的关系运算：>、>=、<=、！=（<>）、BETWEEN...AND、 LIKE、IN、IS NULL、，AND、OR、NOT;

1、关系运算 范例：查询基本工资高于1500的所有雇员信息 SELECT * FROM emp WHERE sal>1500;

范例：查询所有职位是办事员的雇员信息： SELECT * FROM emp WHERE job='clerk'; 这个时候没有返回相应查询结果，原因是在oracle数据库中，所有的数据都是区分大小写的，大面修改如下： SELECT * FROM emp WHERE job='CLERK'; 以上只是操作一个条件，现在也可以操作多个条件，而这多个条件之间可以使用AND或OR进行连接操作。范例：查询工资在1500-3000之间的全部雇员信息 SELECT * FROM emp WHERE sal BETWEEN 1000 AND 1500; 等价于 SELECT * FROM emp WHERE sal>=1000 AND sal<=1500;

范例：查询职位是CLERK,或是SALESMAN的全部信息： SELECT * FROM emp WHERE job='CLERK' OR job='SALESMAN';

范例：查询职位是CLERK，或是SALESMAN的全部信息，并且要求这些雇员的工作大于1200 SELECT * FROM emp WHERE (job='CLERK' OR job='SALESMAN') AND sal>1200;

范例：查询所有不是职位不是CLERK的信息： SELECT * FROM emp WHERE job<>'CLERK'; 等价于 SELECT * FROM emp WHERE job!='CLERK'; 等价于 SELECT * FROM emp WHERE NOT job='CLERK';

2、范围判断 BETWEEN... AND "BETWEEN 最小值 AND 最大值" ，表示一个范围间的判断过程。范例：查询基本工资在1500-3000的雇员信息 SELECT * FROM emp WHERE sal BETWEEN 1500 AND 3000;

范例：现在也可以对BETWEEN...AND操作求反 SELECT * FROM emp WHERE NOT sal BETWEEN 1500 AND 3000;

"BETWEEN...AND"操作不仅可以对数字有用，而且对于日期也同样有用。

范例：要求查询出在雇员的全部雇员信息时间范围：1981_01_01-1981_12-31，使用hiredate字段表示雇佣日期； hiredate字段上的内容可以使用字符串表示：‘01-JAN-81’-‘31-DEC-81’ SELECT * FROM emp WHERE hiredate BETWEEN '01-JAN-81' AND '03-DEC-81'; 故障

3、判断是否为空：IS(NOT)NULL 使用次语法可以判断某一个字段的内容是否为“null”,但是null和数字0以及空字符是两个概念。范例：查询所有领取奖金的雇员信息： SELECT * FROM emp WHERE comm IS NOT NULL; 等价于 SELECT * FROM emp WHERE NOT comm IS NULL; 范例：查询所有不领取奖金的雇员 SELECT * FROM emp WHERE comm IS NULL;

4、知道范围的判断：IN操作符 IN操作符表示的是指定一个查询范围，例如：范例：查询雇员编号是7369、7566、7799的雇员信息如果按照最早做法，使用OR操作：

SELECT * FROM emp WHERE empno=7369 OR empno=7566 OR empno=7799; 使用新操作符IN，则代码简单： SELECT * FROM emp WHERE empno IN (7369,7566,7799); 而如果使用NOT IN呢？，则表示不在制定范围之中。 SELECT * FROM emp WHERE empno NOT IN (7369,7566,7799); 注意点：关于NOT IN 问题如果现在使用IN 操作符，查询范围之中存在null，不影响查询 SELECT * FROM emp WHERE empno IN (7369,7566,7799，null); 如果现在使用NOT IN 操作符，查询范围有null,则表示查询全部数据难点 SELECT * FROM emp WHERE empno NOT IN (7369,7566,7799,null);

5、模糊查询：LIKE子句 LIKE 子句的功能提供了模糊查找的操作，例如：某些程序出现的搜索操作，都属于LIKE子句的实现，但是必须提醒，搜索引擎上的查询不是LIKE. 要想使用LIKE子句，必须认识两个匹配符号：匹配单个字符： _
匹配任意多个字符：% 范例：要求查询雇员姓名中以字母A开通的全部雇员信息： SELECT * FROM emp WHERE ename LIKE 'A%'; 范例：要求查询出雇员姓名中第二个字母是A的全部雇员信息： SELECT * FROM emp WHERE ename LIKE '_A%';

现在也可以使用NOT 操作，对LIKE 求反

SELECT * FROM emp WHERE ename NOT LIKE '%A%'; 另外对于LIKE子句，不一定只能在字符串中使用，可以在任意数据上表示 SELECT * FROM emp WHERE ename LIKE '%1%' OR hiredate LIKE '%1%' OR sal LIKE '%1%'; 说明：原因LIKE子句使用在开发之中，数据库的模糊查询肯定使用LIKE子句，但是在使用LIKE子句的时候有一个最多的注意点：如果在模糊查询上不设置任何的查询关键字的话（'%%'）,则表示查询所有记录：

3.1 数据的排序 当数据返回查询结果后，所有数据默认情况是安装雇员标号排序。我们也可以使用“ORDER BY”子句指定所需要排序的操作列，语法如下：

SELECT [DISTINCT] * 字段 [别名][字段[别名]] FROM 表名称 [别名] [WHERE 条件(S)]; [ORDER BY 字段 [ASC|DESC] [字段 [ASC|DESC],...]]

“ORDER BY”子句是写在所有的SQL语句最好的内容，而且对于排序有一下几点说明：排序的时候可以指定多个排序的字段；排序的方式有两种：升序（ASC）：默认，不写也是升序；降序（DESC）:需指定，由大到小排序；范例：查询所有雇员信息，要求安装工资排序 SELECT * FROM emp ORDER BY sal; SELECT * FROM emp ORDER BY sal DESC;

范例：查询所有雇员信息，安装工作由高到低排序，如果工作相同，则安装雇佣日期由早到晚排序此时肯定需要两个字段排序：工资（DESC），雇佣日期（ASC）; SELECT * FROM emp ORDER BY sal DESC,hiredate ASC; 对于排序操作，一般只在需要的地方使用，而且一定要记住的是，ORDER BY 子句是写在所有SQL语句最后

3.2单行函数 虽然各个数据库都是支持SQL语句，但是每一个数据库也有子句所支持的操作函数，这些就是单行函数，而如果要想进行数据库开发的话，除了要会使用SQL外，就是要多学习函数。单行函数主要分为以下五类：字符函数、数字函数、转换函数、统一函数；

3.2.1 字符函数 字符函数的功能主要是进行字符串函数的操作，下面给谁几个字符函数： UPPER (字符串|列)：将输入的字符串变为大写返回； LOWER(字符串|列)：将输入的字符串变为小写返回； INITCAP(字符串|列)：开头首字母大写； LENGTH(字符串|列)：求字符串长度； REPLACE(字符串|列)：进行替换； SUBSTR(字符串|列)，开始点[结束点]：字符串截取； Oracle 之中有一点比较麻烦，即使要验证字符串，也必须写完整的SQL语句，所以在Oracle数据库中为了用户的查询方便专门提供一个“dual”的虚拟表。 SELECT UPPER('hello') FROM dual; 大写转换作用：在一边使用之中，用户输入数据的时候不会关心数据本身存放是大写还是小写 SELECT * FROM emp WHERE ename='&str'; 当然以上的“&”的操作属于替代变量的内容。范例：观察转小写的操作，将所有的雇员姓名安装小写字母返回 SELECT LOWER(ename) FROM emp;

范例：将每个雇员姓名的开头首字母大写 SELECT INITCAP (ename) FROM emp;

范例：查询出每个雇员姓名的长度 SELECT ename,LENGTH(ename) FROM emp; 范例：查询姓名长度正好是5的雇员信息 SELECT ename ,LENGTH(ename) FROM emp ename WHERE LENGTH(ename)=5; 范例：使用字母“”替换姓名中的所有字母“A” SELECT REPLACE(ename,'A','') FROM emp;

字符串截取操作有两种语法；语法一：SUBSTR(字符串|列，开始点)，表示从开始点一直截取到结尾； SELECT ename,SUBSTR(ename,3) FROM emp; 语法二：SUBSTR(字符串|列，开始点，结束点)，表示从开始点截图到结束点，截取部门内容； SELECT ename,SUBSTR(ename,0,3) FROM emp; 等价于 SELECT ename,SUBSTR(ename,1,3) FROM emp; 范例：要求截取每个雇员姓名的后三个字母正常思路：通过长度-2确定开始点 SELECT ename,SUBSTR(ename,LENGTH(ename)-2) FROM emp; 等价于 SELECT ename,SUBSTR(ename,-3) FROM emp;

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

四、多表查询

之前的查询都是在一张表里进行，如果现在使用一张以上的表，就成为多表查询。多表查询语法：

SELECT [DISTICNT] * | 查询列1 别名1，查询列2，别名2，... FROM 表名称1 别名1，表名称2，别名2，... [WHERE 条件（s）] [ORDER BY 排序字段1 ASC|DESC,排序字段2 ASC|DESC]

范例：下面使用多表查询，同时查询emp和dept表 SELECT * FROM emp,dept; 以上查询使用2个表。从查询结果上发现返回的数据是56行，但是emp是14行。

范例：查询emp表中的记录数 SELECT COUNT(*) FROM emp；从查询结果上看emp中有14条

范例：查询dept表的记录数 SELECT COUNT(*) FROM dept; 从查询结果上看dept中有4条

使用多表查询会产生笛卡尔乘积。如果表的数据越多，那么笛卡尔乘积就会越大，如果现在假设有3张表，每张表有1000条记录，100010001000

所有多表查询是不建议过多使用

要想去掉笛卡尔乘积必须使用字段就那些关联操作。在emp中表存在一个deptno的字段，在dept表中也存在一个deptno字段，而且emp表中的deptno中的字段是与dept表中的deptno关联的，所有deptno属于关联字段。

在多表查询要使用WHERE子句来消除笛卡尔积

范例：修改前面的查询 SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno; 查询结果是14行，此时消除了笛卡尔积，但是如果现在名称过长时，不是很好用，一般会对表起个别名。

SELECT * FROM emp e, dept d FROM e.deptno=d.deptno;

结果是相同的，所以一般使用多表查询，最好指定表的别名

范例：要求查询出雇员的编号、雇员的姓名、部门的编号、部门名称及部门位置 SELECT e.empno,e.ename,d.deptno,dname,d.loc FROM emp e, dept d WHERE e.deptno=d.deptno;

范例：要求查询出每个雇员姓名、工作、雇员的直接上级领导的姓名在emp表中mgr字段表示一个雇员的上级领导的编号，如果要查询一个雇员的上级领导的姓名，则肯定要将 emp和emp自己进行关联，这种叫自关联。

SELECT e.ename,e.job,m.ename FROM emp e, emp m WHERE e.mgr=m.mgr;

范例：要求进一步扩展，将雇员所在部门名称同时列出 SELECT e.ename,e.job,m.ename ,d.dname FROM emp e, emp m,dept d WHERE e.mgr=m.empno and e.deptno=d.deptno;

思考题：要求查询每个雇员的姓名、工资、部门名称、工资在公司的等级（salgrade），及其领导的姓名及工资所在公司的等级。 SELECT e.ename,e.sal,d.dname,s.grade FROM emp e, dept d, salgrade s WHERE e.deptno=d.deptno And e.sal BETWEEN s.losal AND s.hisal;

4.1左连接、右连接

在dept表中存在4条记录现在emp表和dept表关联查询，查询一下指定的字段。 SELECT e.empno,e.ename,d.deptno,d.dname FROM emp e, dept d WHERE e.deptno=d.deptno; 此时发现查询结果中，deptno字段里没有出现40的记录，我们查看在dept表中的部门编号有40，这是为什么？因为在雇员表中没有是40部门的雇员

SELECT e.empno,e.ename,d.deptno,d.dname FROM emp e,dept d WHERE e.deptno(+)=d.deptno; 如果想要40部门显示出来，在WHERE子句中（+）写在等会的左边，发现40部门出现。这个时候是右连接。因此，（+）在=右边是左连接-----连接的时候以左边为准（+）在=左边是右连接-----连接的时候以右边为准

注意：在开发中左、右连接使用较多，要理解实际上在之间的查询中查找雇员姓名及每一位雇员的领导的时候就应该使用左右连接 SELECT e.empno,e.ename,m.empno,m.ename FROM emp e,emp m WHERE e.mgr=m.empno; 一共13行数据，但是emp表中有14条数据 SELECT e.empno,e.ename,m.empno,m.ename FROM emp e,emp m WHERE e.mgr=m.empno(+);

SQL语法

交叉连接[CROSS JOIN table2]产生笛卡尔积 SELECT * FROM emp CROSS JOIN dept; 产生56条记录，效果和一下查询一样 SELECT * FROM emp,dept;

自然连接[CROSS JOIN table2]自动进行关联字段的匹配 SELECT * FROM emp NATURAL JOIN dept; 查询的结果是14条记录，自动进行字段的关联匹配，不用写WHERE子句的关联条件，效果和一下查询结果一样 SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno;

[JOIN table2 USING dept(column_name)]直接指定关联的操作列 SELECT * FROM emp JOIN dept USING(deptno);

[JOIN table2 ON(table2.column_name=table2.column_name)]用户子句变相连接的条件 SELECT * FROM emp JOIN dept ON (emp.deptno=dept.deptno);

[LEFT OUTER JOIN table2 ON(table1.column_name=table2.column_name)]左外连接 [RIGHT OUTER JOIN table2 ON (table1.column_name=table2.column_name)]右外连接 SELECT * FROM emp e RIGHT OUTER JOIN dept d ON (e.deptno=d.deptno);

4.2 组函数和分组统计 组函数也称为聚合函数例如：我们把学生可以分为难受和女生两个组，如果想求每组的人数，平均身高，平均年龄等，就需要用到分组函数

在SQL中常用的组函数有一下几个： COUNT();求全部记录数 MAX()：求出一组中的最大值 MIN():求出一组中的最小值 AVG():求出一组中的平均值 SUM():求和

范例：COUNT()函数 SELECT COUNT(empno) FROM emp; 我们常用COUNT(),最好能够用字段代替 范例：MAX()、MIN（）函数，求最大最小值，一般是针对于数值的字段的，求出所有员工的最高工资，和最低工资和平均工资。 SELECT MAX(sal) 最高工资,MIN(sal) 最低工资,AVG(sal) 平均工资 FROM emp;

范例：求出部门10的所有员工工资的总和 SELECT SUM(sal) 工资总和 FROM emp WHERE deptno=10;

如果如下查询输出部门编号和其部门所有员工的工资总和，会产生错误。 SELECT deptno,SUM(sal) 工资总和 FROM emp WHERE deptno=10; 发生以上的错误信息，是应为这样的查询需要进行分组统计 SELECT deptno,SUM(sal) 工资总和 FROM emp WHERE deptno=10 GROUP BY deptno;

分组统计语法格式 SELECT [DISTINCT] * | 查询列1 列别名1，查询列2 列别名2，... FROM 表名称1 表别名1，表名称2 表别名2，... [WHERE 条件] [ORDER BY 排序字段1，排序字段2 ASC|DESC] [GROUP BY 分组字段]

范例：求出每个部门的雇员数量分析：应该按照部门编号deptno进行分组 SELECT deptno,COUNT(empno) FROM emp GROUP BY deptno;

进一步查询部门编号是10的雇员的总工资， SELECT deptno,SUM(sal) FROM emp WHERE deptno=10 GROUP BY deptno;

可是如果我们想找出总工资大于9000的部门 SELECT deptno,SUM(sal) FROM emp WHERE SUM(sal)>9000 GROUP BY deptno; 会出现以下错误

ORA-00934: 此处不允许使用分组函数 00934. 00000 - "group function is not allowed here" *Cause:
*Action: 行 1 列 39 出错

分组函数只能在分组中使用，不容许在WHERE语句之中出现，那么如果现在要指定分组的条件，只能通过 HAVING子句此时的SQL语法格式 SELECT [DISTINCT] * | 查询列1 列别名1，查询列2 列别名2，... FROM 表名称1 表别名1，表名称2 表别名2，... [WHERE 条件] [ORDER BY 排序字段1，排序字段2 ASC|DESC] [GROUP BY 分组字段 [HAVING 分组条件]]

SELECT deptno ,SUM(sal) FROM emp GROUP BY deptno HAVING SUM(sal)>9000;

范例：显示非销售人员工作名称以及从事同一工作雇员的月工资的总和，并且要满足从事同一工作的雇员的月工资合计大于5000，输出结果按月工资的合计升序排列

分析：1、显示非销售人员条件是：job<>'SALESMAN' SELECT * FROM emp WHERE job<>'SALESMAN';

分析：2、从事同一工作安装工作分组：GROUP BY job 工作名称、月工资的总和----分组查询的是工作名称和工资合计：job,SUM(sal) SELECT job,SUM(sal) FROM emp WHERE job<>'SALESMAN' GROUP BY job; 分析：3、工资合计大于5000---分组条件：HAVING SUM(sal）> 5000 SELECT job,SUM(sal) FROM emp WHERE job<>'SALESMAN' GROUP BY job HAVING SUM(sal)>5000;

分析：4、输出结果按月工资的合计排列----ORDER BY SELECT job, SUM(sal) FROM emp WHERE job<>'SALESMAN' GROUP BY job HAVING SUM(sal)>5000 ORDER BY SUM(sal) ASC;

因为排序的时候使用SUM(sal)不方便，我们常用列别名解决这个问题 SELECT job, SUM(sal) S FROM emp WHERE job<>'SALESMAN' GROUP BY job HAVING SUM(sal)>5000 ORDER BY S ASC;

小结： 1、如果使用了分组函数，则有两种可以使用的情况 2、如果不使用应分组的话，则只能单独使用分组函数

分组的简单原则：只要一列存在重复的内容，才可以考虑到分组分组函数可以嵌套使用，但是在组函数使用的时候，不能再出现分组条件的查询语句

范例：求出平均工资最高的部门的编号和部门的工资 SELECT deptno,MAX(SUM(sal)) FROM emp GROUP BY deptno; ORA-00937: 不是单组分组函数

结果出现错误，修改代码，去掉deptno字段的显示 SELECT MAX(SUM(sal)) FROM emp GROUP BY deptno;

如果需要查询平均工资最高的部门编号，修改以上的代码： SELECT a.d FROM (SELECT deptno d,AVG(sal) av FROM emp GROUP BY deptno) a WHERE a.av>=(SELECT MAX(AVG(sal)) FROM emp GROUP BY deptno);

以上问题的解决采用的子查询

五、子查询

子查询：在一个查询的内部还包括另外的查询，则此查询称为子查询

范例：要求查询比7654工资要高的所有雇员的信息

分析：先查询出7654的工资 SELECT sal FROM emp WHERE empno=7654;

分析：在此查询的基础上再查询比7654工资高的雇员，只要sal>7654即可 SELECT * FROM emp WHERE sal>(SELECT sal FROM emp WHERE empno=7654);

所有子查询必须在（）中编写

子查询在操作中有分为以下三类：单列查询：返回的结果是一列的一个内容单列子查询：返回多个列，有可能是一个完整的记录多行子查询：返回多条子记录

范例：要求查询出工资比7654高，同时与7788从事相同工资的全部雇员信息根据刚才查询分析：条件有两个，一个是sal>7654的工资，同时job=7788的job SELECT * FROM emp WHERE sal>(SELECT sal FROM emp WHERE empno=7654) AND job=(SELECT job FROM emp WHERE empno=7788);

范例：要求查询出工资最低的雇员姓名、工作、工资。分析：工资最低，需要采用到分组函数MIN(sal），首先查询最低工资 SELECT MIN(sal) FROM emp; 分析：在这个基础上完善查询的条件 SELECT ename,job,sal FROM emp WHERE sal=(SELECT MIN(sal) FROM emp);

思考：要求查询出，部门名称、部门的员工数，部门的平均工资，部门的最低收入雇员的姓名和最高收入雇员的姓名。分析：查询涉及到两张表emp和dept

1、如果要想求出每个部门的员工数量及平均工资，则肯定需要使用分组统计，按deptno进行分组 SELECT deptno,COUNT(empno),AVG(sal) FROM emp GROUP BY deptno;

2、但是如果想查询出部门的名称，就需要与dept表进行关联。于是有人写下一下代码： SELECT d.dname,COUNT(e.empno),AVG(sal)FROM emp e, dept d WHERE e.deptno=d.deptno GROUP BY e.deptno; ORA-00979: 不是 GROUP BY 表达式

可是出现了这样的错误。在第一步查出部门编号、部门人数、部门平均工资的基础上，应该把这个作为一个临时的表与dept表进行关联。这样才能正确的显示出部门的名称。 SELECT d.dname,l.c,l.a FROM (SELECT deptno,COUNT(empno) c,AVG(sal) a FROM emp GROUP BY deptno) l,dept d WHERE l.deptno=d.deptno;

3、部门的最低收入和最高收入，应该用到MAX()和MIN()函数，依然是根据部门进行分组的，修改以上的代码，同时显示出部门中工资最低和工资最高的员工姓名 SELECT d.dname,l.c,l.a,e.ename,e.sal FROM (SELECT deptno,COUNT(empno) c,AVG(sal) a,MAX(sal) max,MIN(sal) min FROM emp GROUP BY deptno) l,dept d,emp e WHERE l.deptno=d.deptno AND (e.sal=l.min OR e.sal=l.max);