首页 > 代码库 > 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、查询