oracle 11g 学习笔记 10_26

一、oracle基本查询

这是对表的最重要的一个操作。
*查看表的结构

desc dept;

.查询所有列

select * from dept;

.查询指定的一些列

select empno, ename, job, deptno from emp where ename = 'SMITH';

在oracle中，用单引号括起来的内容是区分大小写的。
.取消重复行 

select distinct deptno from emp;

.使用算数表达式
.使用别名

select ename '姓名',sal*2 as "年收入"from emp;

*如何处理null值
使用nvl函数来处理

select sal*13+nvl(comm,0)*13 "年工资",ename ,comm from emp;

其中nvl(comm,0)表示的是，如果comm为空，那么就将comm表示为0，如果不为空，则不改变。
.如何连接字符串

select ename || 'is a ' || job from emp;

.使用where子句?   
显示工资高于3000的 员工;  

SELECT * FROM emp WHERE sal > 3000;  

查找1982.1.1后入职的员工;  

SELECT ename,hiredate FROM emp WHERE hiredate >'1-1月-1982'; 

这里要注意一下日期的格式。 
显示工资在2000到3000的员工  

SELECT ename,sal FROM emp WHERE sal >=2000 AND sal <= 3000;  

*如何使用like操作符  
%：表示0到多个字符  _：表示任意单个字符  
显示首字符为S的员工姓名和工资  

SELECT ename,sal FROM emp WHERE ename like 'S%';  

显示第三个字符为大写O的所有员工的姓名和工资  

SELECT ename,sal FROM emp WHERE ename like '__O%'; 

.在where条件中使用in   
显示empno为7844, 7839,123,456 的雇员情况  

SELECT * FROM emp WHERE empno in (7844, 7839,123,456);

其中 in (7844, 7839,123,456);表示的是在empno包含7844，7839，123，456；  
.使用is null的操作符  
显示没有上级的雇员的情况  
错误写法：select * from emp where mgr = '';  
正确写法：SELECT * FROM emp WHERE mgr is null; 
*order by 的使用
显示emp表工资由高到低排序

select * from emp order by sal desc;

其中order by sal表示的是按sal这一列来排序，desc表示是按降序来排序，asc表示的是按升序来排序（默认）。
显示按照部门号升序而雇员的工资降序排列。

select * from emp order by deptno, empno desc;

查询操作时间可以用set timing on;

当要查询某个字段中值为空的数据时，可以这样写

select * from student where birthdya is null;

注意：中文内容要加双引号

二、复杂查询

.数据分组
-max,min,avg,sum,count
.显示所有员工中最高工资和最低工资

select max(sal),min(sal) from emp;

显示最高工资的员工姓名和工资；
开始我也觉得这个题很简单，但是我先像以下那样试做了，结果不对：
(错)SQL> select ename,sal from emp
  2  where sal = max(sal)
  3  group by ename,sal;
ORA-00934: 此处不允许使用分组函数
(错)SQL> select ename,max(sal) as "最高工资" from emp;
ORA-00937: 不是单组分组函数
如果有一列是分组函数，那么其他列都要求是分组函数
正确：

select ename, sal from emp where sal =(select max(sal) from emp);

.显示工资高于平均工资的员工信息
*group by 和 having子句
group by用于对查询的结果分组统计
having子句用于限制分组显示结果
.显示每个部门的平均工资和最高工资 

select deptno,avg(sal),max(sal) from emp group by deptno;

.显示每个部门的每种岗位的平均工资和最低工资

select avg(sal),max(sal),deptno,job from emp group by deptno,job;

.显示平均工资低于2000的部门号和它的平均工资。

 select avg(sal),max(sal),deptno from emp group by deptno having avg(sal)>2500;

*对数据分组的总结
i 分组函数只能出现在选择列表，having，order by 子句中，不能当成一个条件来使用，比如在select，where中的条件啊。
ii 如果在select 语句中同时包含有group by，having，order by 那么他们的顺序是group by，having，order by
iii 在选择列中如果有列，表达式，和分组函数，那么这些列和表达式中必须有一个出现在group by子句中，否则就会出错。

三、多表查询

多表查询是指基于两个和两个以上的表或是视图的查询。在实际应用中，查询单个表是不能满足要求的。
.显示雇员姓名，雇员工资及所在部门的名字，并按部门排序。

select ename, sal, dname from emp, dept where emp.deptno = dept.deptno order by dname;

四、子查询

子查询是指嵌入在其它sql语句中的select语句。也叫嵌套查询
*单行子查询
单行子查询是指子查询语句只返回一行数据的查询；
.显示与SMITH同一部门的所有员工。

select * from emp
   where deptno = (select deptno from emp where ename = 'SMITH');

*多行子查询
多行子查询指返回多行数据的子查询
.查询和部门10的工作相同的雇员的名字、岗位、工资、部门号

select * from emp where job in(select distinct job from emp where deptno = 10);

*多列子查询
多列子查询是指查询返回多个列数据的子查询语句；
.查询与smith的部门和岗位完全相同的所有雇员。

select ename from emp where deptno = (select deptno from emp where ename = 'SMITH') 
	and job = (select job from emp where ename = 'SMITH');

或者是

select ename from emp where (deptno, job) = (select deptno, job from emp where ename = 'SMITH');