首页 > 代码库 > 4、查询
4、查询
简单查询
(1)查询出一张表中的所有数据
Select [distinct] *|字段 [别名] ,[字段[别名]]from 表名称[别名]
例1:查询出每个雇员的编号、姓名、基本工资
Sql>Select empno,ename,sal from emp;
例2:查询出每个雇员的职位
Sql>Select job from emp;
(2)消除重复值
例3:job内容中出现了重复的数据
Sql>Select distinct[S15] job from emp;
例4:要求显示每一个雇员的姓名、职位、基本年薪
Sql>Select ename ,job,(sal*12) income[S16] from emp;
(3)使用“||”连接查询的字段
例1:Sql>Select empno||’, ’||ename from emp;
结果显示:
例2:要求现在的数据按照如下的格式显示为:“雇员编号是:7369的雇工姓名是:SMTTH,基本工资是:800,职位是:CLERK!”
Sql>Select ‘雇员编号是:‘||empno||‘雇工姓名是:‘||ename||‘,基本工资是:‘||sal||‘,职位是:‘||job||‘!‘from emp;
例3:列出所有员工的编号、姓名及其直接上级编号、姓名,显示的结果按领导年工资的降序排列。
SQL> select ‘员工号:‘||a.empno,‘员工姓名:‘||a.ename,‘其上司编号:‘||b.empno,‘其上司姓名:‘||b.ename,‘其上司工资:‘||b.sal from emp a,emp b where a.mgr=b.empno order by b.sal desc;
条件查询
在之前的简单查询中,是将所有的记录进行显示,但是现在可以对显示的记录进行过滤的操作,而这就属于限定查询的工作了,限定查询就是在之前语法的基础上增加了一个where子句,用于指定限定条件,此时语法如下:
Select [distinct] *|字段[别名],[字段[别名]] from 表名称[别名] where 条件(s);
在where子句之后可以增加多个条件,最常见的条件就是基本的关系运算>、<、<=、>=、!=(<>)、between..and、link、in、is null、is not null、and、or、not;
1.关系运算
(1)关系运算:>,<,<=,>=,!=(<>), between..and,in,or,not,and
例1:要求查询出基本工资高于1500的所有雇员信息
Select * from emp where sal>=1500;
例2;查询所有职位是办事员的雇员信息
Select * from emp where job=’clerk[S17] ’;
以上只是操作一个条件,现在也可以操作多个条件,而这多个条件之间使用AND或OR进行连接操作。
例3:查询工资在1500-3000之间的全部雇员信息
SQL>Select * from emp where sal>=1500 and sal<=3000;
例4:查询出职位为办事员或者是销售人员的全部信息
SQL>Select * from emp where job=’CLERK’ or job=’SALESMAN’;
例5:查询出职位为办事员或者是销售人员的全部信息,并且要求这些雇员的工资大于1200;
SQL>Select * from emp where (job=’CLERK’ or job=’SALESMAN’) and sal>1200;
例6:不是办事员的员工信息
SQL>Select * from emp where job<>’CLERK’;
SQL>Select * from emp where job!=’CLERK’;
SQL>Select * from emp where not job=’CLERK’;
例7:查询出名字为syc、scott的所有信息
SQL>Select * from emp where ename in (‘SYC’,’SCOTT’);
例8:查询每个员工工资所在的等级
SQL> select e.ename, e.sal, s.grade from EMP e, salgrade s where e.sal>=s.losal and e.sal<=s.hisal;
(2) between……and
“Between 最小值 and 最大值”,表示的是一个范围间的判断过程。
例1:要求查询出工资在1500—3000的雇员信息
Select * from emp where sal between 1500 and 3000;
例2:用between..and求反。
Select * from emp where not sal between 1500 and 3000;
例3:要求查询出生在1981年雇佣的全部雇员信息——Between…and也可以日期有效[S18]
Select * from emp where hiredate between ‘01-1月-1981’ and ’31-12月-1981’;
(3).判断是否为空:is(not) null
例1:查询所有领取奖金的雇员信息
Select * from EMP where comm is not null;
Select * from EMP where not comm is null;
例2:查询所有不领取奖金的雇员
Select * from EMP where comm is null;
(4).指定范围的判断:in操作符
例1:查询出雇员编号为7379、7566、7799的雇员信息;
Select * from EMP where empno=7369 or empno=7566 or empno 7799[S19] ;
Select * from EMP where empno in (7369, 7566, 7799)[S20] ;
例2:查询出雇员编号不为7379、7566、7799的雇员信息;
Select * from EMP where empno not in (7369, 7566, 7799);
例3: 查询出雇员编号不为7379、7566、7799和没有编号的雇员信息;
Select * from EMP where empno in (7369, 7566, null);[S21]
注意:Select * from EMP where empno not in (7369, 7566, null);[S22]
(5).模糊查询:like子句
Like子句的功能是提供了模糊查找的操作,例如:某些程序上出现的搜索操作,都属于like子句的实现,但是必须提醒的,搜索引擎上的查询可不是like。
但是要想使用like子句则必须认识两个匹配符号:
匹配单个字符:_;——>1个
匹配任意多个字符:%;——0个、1个、多个
例1:要求查询雇员姓名中以字母A开头的全部雇员信息
Select * from EMP where ename like ‘A%’;
例2:要求查询雇员姓名中第二个字母是A的全部雇员信息
Select * from EMP where ename like ‘_A%’;
例3:要求查询出雇员姓名中带有字母A的雇员
Select * from EMP where ename like ‘%A%’;
例4:不待A
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子句,但是在使用子句的时候有一个最大的注意点:如果在模糊查询上不设置任何的查询关键字的话(‘%%’)则表示查询全部记录:
Select * from EMP where ename like ‘%%’ or hiredate like ‘%%’ or sal like ’%%[S23] ’;
习题1
1.选择部门30中的所有员工。
Sql>Select * from emp where deptno=30;
2.列出所有办事员(clerk)的姓名,编号和部门编号
Sql>Select ename,empno,deptno from emp where lower(job)=’clerk’;
3.找出佣金大于薪金的员工
Sql>Select * from emp where comm>=sal;
4.找出佣金大于薪金的60%的员工
Sql>Select * from emp where comm>=sal*0.6;
5.找出部门10中所有经理(manager)和部门20中所有办事员(clerk)的详细资料
Sql>Select * from emp where (deptno=’10’and lower(job)=’manager’)or(deptno=’20’and lower(job)=’clerk’);
6.找出部门10中所有经理(manager)和部门20中所有办事员(clerk)的详细资料,既不是经理又不是办事员但其薪金大于等于2000的所有员工的资料。
Sql>Select * from emp where (deptno=’10’and lower(job)=’manager’)or(deptno=’20’and lower(job)=’clerk’)or(job not in(‘CLERK’,’MANAGER’)and sal>=2000);
7.找出收取佣金的员工的不同工作
Sql>Select distinct job from emp where comm is not null;
Sql>Select distinct job from emp where comm>0;
8.找出不收取佣金或收取的佣金低于100的员工
Sql>Select * from emp where comm is null or comm<=100;
Sql>Select * from emp where nvl(comm,0)=0 or comm<=100;
9.找出各月倒数第三天受雇的所有员工
每一个雇佣日期肯定是不一样的,所以现在必须找到每一个雇员所在月的最后一天,之后按照“日期-数字”的方式求出前三天的日期,这个日期相符合才满足条件。
Sql>Select * from emp where last_day(hiredate)-2=hiredate;
10.找出早于12年前受雇的员工
Sql>Select * from emp where months_between(sysdate,hiredate)/12>=12;
11.以首字母大写的方式显示所有员工的姓名
Sql>Select initcap(ename)from emp;
12.显示正好为5个字符的员工的姓名
Sql>Select ename from emp where length(ename)=’5’;
13.显示不带有”R”的员工的姓名
Sql>Select ename from emp where ename not like ‘%R%’;
14.显示所有员工姓名的前三个字符
Sql>Select substr(ename,0,3) from emp ;
15.显示所有员工姓名,用’a’替换所有的‘A‘
Sql>Select replace(ename,’A’,’a’) from emp;
16.显示满10年服务年限的员工姓名,受雇日期
Sql>Select ename ,hiredate from emp where months_between(sysdate,hiredate)/12>=10;
17.显示员工的详细资料,按姓名排序
Sql>Select * from emp order by ename;
18.显示员工的姓名和受雇日期,根据其服务年限,将最老的员工排在最前面
Sql>Select ename ,hiredate from emp order by hiredate;
19.显示所有员工的姓名、工作、薪金,按工作的降序排列,若工作相同则按照薪金排序
Select ename,job,sal from emp order by job desc ,sal;
20.显示所有员工姓名、加入公司的年份和月份,按照受雇日期所在月排序。若月份相同则将最早年份的员工排在最前面。本程序需要从日期之中取出年份和月份。
Sql>Select ename ,hiredate from emp order by to_char( hiredate,’mm’);
Select ename,to_char(hiredate,’yyyy’),to_char(hiredate,’mm’) from emp order by to_char( hiredate,’mm’);
21.显示在一个月为30天的情况下所有员工的薪金,忽略余数
Sql>Select ename,(sal*12+nvl(comm,0)*12) as 年薪 from emp order by 年薪;
22.找出在(任何年份的)2月受雇佣的所有员工。
Sql>Select * from emp where to_char (hiredate,’mm’)=2;
23.对于每个员工,显示其加入公司的天数
Sql>Select ename,sysdate-hiredate from emp;
24.显示姓名字段的任何位置包含“A”的所有员工的姓名
Sql>Select ename from emp where ename like ’%A%’;
25.以年月日的方式显示所有员工的服务年限。(要精确到天数)
第一步:求出每个雇员的雇佣年数:被雇佣的总月数/12=年数;
Sql>Select ename,hiredate,trunc(months_between(sysdate,hiredate)/12)year from emp;
第二步:求出月数,以上计算之中被忽略的小数点实际都是月份,所以直接取余即可
Sql>Select ename ,hiredate,trunc(months_between(sysdate,hiredate)/12)year,
Trunc(mod(months_between(sysdate,hiredate),12))months from emp;
第三步:求出天数,最准确的做法是在不超过30天的范围之内求;
现在已经知道当前的时间使用sysdate取出,而雇佣的日期使用hiredate取出,可是hiredate和sysdate之间的差距太大了,所以肯定会有误差,那么就必须想办法将日期提升到与sysdate差距在30天的范围之内。
在之前学习过的两个函数:
1.months_between():求出日期之间的月数,如果是:months_between(sysdate,hiredate)求出的是雇佣日期到今天为止的雇佣月份;
2.add_months():在一个日期上加入指定的月之后的日期,如果说hiredate+与今天相距的月数=一个新的日期,而且这个新的日期肯定和sysdate相距不超过30天
Sql>Select ename ,hiredate,trunc(months_between(sysdate,hiredate)/12)year,
Trunc(mod(months_between(sysdate,hiredate),12))months,
Trunc(Sysdate-Add_months(hiredate,months_between(sysdate,hiredate)))
day from emp;
多表查询
本节预讲知识点
1.多表查询的操作、限制、笛卡尔积的问题;
2.统计函数及分组统计的操作;
3.子查询的操纵,并且结合限定查询、数据排序、多表查询、统计查询一起完成各个复杂查询的操作;
4.数据库的更新操作:增加、修改、删除数据
5.数据伪列的作用:rownum、rowid;
6.完成一套复杂查询的应用案例;
1.多表查询
在之前所使用的查询操作之中,都是从一张表之中查询出所需要的内容,那么如果现在一个查询需要显示多张表的数据,则就必须应用多表查询,语法:
Select [distinct] *|列[别名][,列 别名,…] from 表名称[别名],[表名称【别名】,….][where 条件(s)] [order by 排序字段asc|desc[,排序字段 asc|desc ]];
但是如果要进行多表查询之前,首先必须先查询出几个数据---雇员表和部门表中的数据量,这个操作可以通过count()函数完成。
例1:查询emp表中的数据量-----返回14条记录
Sql>Select count(*) from emp;
例2:查询dept表中的数据量-----4条记录
Sql>Select count(*) from dept
在执行Select * from emp,dept;后,一共产生56条记录,而56条记录=雇员表的14条记录*部门表的4条记录,之所以会造成这样的问题,主要都是由数据库的查询机制所决定的。
Emp表的部分数据
7369 |
SMITH |
20 |
7902 |
FORD |
20 |
7566 |
JONES |
20 |
10 |
ACCOUNTING |
NEW YORK |
20 |
RESEARCH |
DALLAS |
30 |
SALES |
CHICAGO |
40 |
OPERATIONS |
BOSTON |
本问题在数据库的操作中被称为笛卡尔积,就表示多张表的数据乘积的意思,但是这种查询结果肯定不是用户所希望的,那么如何去掉笛卡尔积呢?
最简单的方式是采用关键字段的形式,emp表和dept表之间现在存在了deptno的关联字段,所以现在可以从这个字段上的判断开始。
在多表查询中,不同的表中有了相同字段名称的时候,访问这些字段必须加上表名称,即“表.字段“。
Select * from emp,dept where emp.deptno=dept.deptno;
那么如果说现在假设表名称很长,例如“yinhexi_diqiu_zhongguo_xichen_ren“。所以一般在进行多表查询的时候往往都会为表起一个别名,通过别名.字段的方式进行查询。
Select * from emp e,dept d where e.deptno=d.deptno;
例3:查询出每一位雇员的编号、姓名、职位、部门名称、位置
第一步:查询出每一位雇员的编号、姓名、职位
Sql>Select e.empno,e.ename,e.job from emp e;
第二步:为查询中引入部门表,同时需要增加一个消除笛卡尔积的条件
Sql>Select e.ename,e.empno,e.job,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno;
例4:要求查询出每一位雇员的姓名、职位、领导的姓名
确定关联字段:emp.mgr=memp.emono(雇员的领导编号=领导(雇员)的雇员编号)
第一步:查询每一位雇员的姓名、职位
Sql>Select e.ename,e.job from emp e;
第二步:查询领导信息,加入自身关联
Sql>Select e.ename,e.job,m.ename from emp e,emp m where e.mgr=m.empno;
例5:查询出每个雇员的编号、姓名、基本工资、职位、领导的姓名、部门名称及位置。
确定已知的关联字段:
雇员和部门:emp.Deptno=dept.deptno;
雇员与领导:emp.mgr=memp.empno;
第一步:查询出每个雇员的编号、姓名、基本工资、职位
Sql>Select empno,ename,sal,job from emp;
第二步:加入领导的信息,引入自身关联,同时增加消除笛卡尔积的条件
Sql>Select e.ename,e.empno,e.sal,e.job, m.ename from emp e, emp m where e.mgr=m.empno;
第三步:加入部门的信息,引入dept表,既然有新的表进来,则需要继续增加消除笛卡尔积的条件
Sql>Select e.ename,e.empno,e.sal,e.job,m.ename,d.dname ,d.loc from emp e, emp m,dept d where e.mgr=m.empno and e.deptno=d.deptno;
例6:现在要求查询出每一个雇员的编号、工资、部门名称、工资所在公司的工资等级。
确定已知的关联字段:
雇员和部门:emp.deptno=dept.deptno;
雇员和工资等级:emp.sal between salgrad.losal and hisal;
第一步:查询出每个雇员的编号、姓名、工资
Sql>Select ename,empno,sal from emp;
第二步:求出部门名称
Sql>Select e.empno,e.ename,e.job,d.dname from emp e,dept d where e.deptno=d.deptno;
第三步:雇员和工资等级:
Sql>Select e.empno,e.ename,e.job,d.dname,s.grade from emp e,dept d ,salgrade s where e.deptno=d.deptno and e.sal between losal and hisal;
如果现在有如下进一步的要求:将每一个工资等级替换成具体的文字信息,如:
例1:替换成第五等级,2替换成第四等级,3替换成第三等级,依次类推——>依靠decode()实现
Sql>Select e.empno,e.ename,e.job,d.dname,decode(s.grade,1,’第五等级’,2,’第四等级’,3,’第三等级’,4,’第二等级’,5,’第一等级’) from emp e,dept d ,salgrade s where e.deptno=d.deptno and e.sal between losal and hisal;
例2:.查询某人的工资等级和工资
Sql>select e.sal,s.grade from emp e,salgrade s where ename=upper(‘&str‘) and e.sal between losal and hisal;
4、查询