一、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');