NOVOTS KMS 词汇表 Glossary    联系我们 Contact Us
查询 Search  
   
按类别浏览 Browse by Category
NOVOTS KMS .: 数据库 .: Oracle数据库自学之一------ Select语句笔记

Oracle数据库自学之一------ Select语句笔记


 select 语句

1.计算数据可以用空表:比如:select 2*3 from dual

2.select ename,sal*12 annual_sal from emp;与select ename,sal*12 "annual sal" from emp;区别,加双引号保持原大小写。不加全变大写。

3. select ename || ’abcd‘ 如果连接字符串中含有单引号,用两个单引号代替一个单引号。

4. select ename || 'abcd''efg'//两个单引号。

distinct
select deptno from emp;
select distinct deptno from emp;

select distinct deptno from emp;
select distinct deptno ,job from emp
去掉deptno,job两者组合的重复。更多的项,就是这么多项的组合的不重复组合。

Where
select * from emp where deptno =10;
select * from emp where deptno <>10;不等于10
select * from emp where ename ='bike';
select ename,sal from emp where sal between 800 and 1500 (>=800 and <=1500)

空值处理:
select ename,sal,comm from emp where comm is (not) null;

in : 在一个范围中
select ename,sal,comm from emp where ename ( not)in ('smith','king','abc');

日期处理:
select ename, sal, hiredate from emp where hiredate > '20-sep-81';//目前只能按照特定的格式来写

模糊查询:
select ename from emp where ename like '_A%'; _代表一个字母, %代表0个或多个字母. 如果查询%
可用转义字符.\%. 还可以用escape '$'比如:select ename from emp where ename like '%$a%' escape '$';

order by

select * from dept;
select * from dept order by dept desc;(默认:asc)
select ename,sal,deptno from emp order by deptno asc,ename desc;

sql function  

select ename,sal*12 annual_sal from emp
where ename not like '_A%' and sal>800
order by sal desc;

select lower(ename) from emp;

select ename from emp
where lower(ename) like '_a%';等同于
select ename from emp where ename like '_a%' or ename like '_A%';

select substr(ename,2,3) from emp;从第二字符截,一共截三个字符.
select chr(65) from dual 结果为:A
select ascii('a') from dual 结果为:65
select round(23.652,1) from dual; 结果为: 23.7
select round(23.652,-1) from dual; 20
 
select to_char(sal,'$99_999_999') from emp;
select to_char(sal,'L99_999_999') from emp;人民币符号,L:代表本地符号9代表一个数字,可以不显示。 0代表必须显示

这个需要掌握牢:
select birthdate from emp;
显示为:
BIRTHDATE
----------------
17-12月-80
----------------

改为:
select to_char(birthdate,'YYYY-MM-DD HH:MI:SS') from emp;

显示:

BIRTHDATE
-------------------
1980-12-17 12:00:00
-------------------

select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual; //也可以改为:HH12
TO_CHAR(SYSDATE,'YY
-------------------
2007-02-25 14:46:14


to_date函数:

select ename,birthdate from emp where birthdate > to_date('1981-2-20 12:34:56','YYYY-MM-DD HH24:MI:SS');
如果直接写 birthdate>'1981-2-20 12:34:56'会出现格式不匹配,因为表中的格式为: DD-MM月-YY.
 

select sal from emp where sal>888.88 无错.但
select sal from emp where sal>$1,250,00;
会出现无效字符错误.
改为:
select sal from emp where sal>to_number('$1.250.00','$9,999,99');

处理空值:把空值改为0

select ename,sal*12+nvl(comm,0) from emp;
这样可以防止comm为空时,sal*12相加也为空的情况.


Group function 组函数
 
max,min,avg ,sum,count 函数

select to_char(avg(sal),'99999999,99') from emp;

select round(avg(sal),2) from emp;
结果:2073.21
 
select count(*) from emp where deptno=10;
select count(ename) from emp where deptno=10; count某个字段,如果这个字段不为空就算一个.
select count(distinct deptno) from emp;
select sum(sal) from emp;

Group by语句

需求:现在想求,求每个部门的平均薪水.
select avg(sal) from emp group by deptno;
select deptno avg(sal) from emp group by deptno;

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

求薪水值最高的人的名字.
select ename,max(sal) from emp;出错,因为max只有一个值,但等于max值的人可能好几个,不能匹配.
应如下求:
select ename from emp where sal=(select max(sal) from emp);

Group by语句应注意,

!!出现在select列表中的字段,如果没出现在组函数中,必须出现在Group by语句中.

select ename, max(sal) from emp group by deptno;
上面的例子:ename出现在了列表中,但是组函数和Group by语句中都没有!

asc
desc
   
Having 对分组结果筛选 

Where是对单条纪录进行筛选,Having是对分组结果进行筛选.

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

查询工资大于1200雇员,按部门编号进行分组,分组后平均薪水大于1500,按工薪倒充排列.
select * from emp
where sal>1200
group by deptno
having avg(sal)>1500
order by avg(sal) desc;

子查询

谁挣的钱最多(谁:这个人的名字,  钱最多)

select 语句中嵌套select 语句,可以在where,from后.

   
问那些人工资,在平均工资之上.

select ename,sal from emp where sal>(select avg(sal) from emp);


查找每个部门挣钱最多的那个人的名字.
select ename ,deptno from emp where sal in(select max(sal) from ename group by deptno) 查询会多值.

应该如下:

select  max(sal),deptno from emp group by deptno;当成一个表.语句如下:
select ename, sal from emp
join(select  max(sal) max_sal,deptno from emp group by deptno) t
on(emp.sal=t.max_sal and emp.deptno=t.deptno);

每个部门的平均薪水的等级.
分析:首先求平均薪水(当成表),把平均薪水和另外一张表连接.


self_table_connection 自链接表

把某个人的名字以及他的经理人的名字求出来(经理人及这个人在表中同处一行)

分析:首先求出这个人的名字,取他的编号,然后从另一张表与其相对应编号,然后找到经理的名字.

select e1.ename ,e2.ename from emp e1,emp e2 where e1.mgr= e2.empno.

empno编号和MGR都是编号.


SQL1999_table_connections   

select ename,dname,grade from emp e,dept d, sqlgrade s
where e.deptno = d.deptno and e.sql between s.losal and s.hisal and
job<>'CLERK';

有没有办法把过滤条件和连接条件分开来? 出于这样考虑,Sql1999标准推出来了.有许多人用的还是
旧的语法,所以得看懂这种语句.

select ename,dname from emp,dept;(旧标准).
select ename,dname from emp cross join dept;(1999标准)

select ename,dname from emp,dept where emp.deptno=dept.deptno (旧)
select ename,dname from emp join dept on(emp.deptno = dept.deptno); 1999标准.没有Where语句.
select ename,dname from emp join dept using(deptno);等同上句,但不推荐使用.

select ename,grade from emp e join salgrade s on(e.sal between s.losal and s.hisal);
join 连接语句, on过滤条件。连接,条件一眼分开。如果用Where语句较长时,连接语句和过滤语句混在一起。

三张表连接:
slect ename,dname, grade from
emp e join dept d on(e.deptno=d.deptno)
join salgrade s on(e.sal between s.losal and s.hisal)
where ename not like '_A%';
把每张表连接 条件不混在一起,然后数据过滤条件全部区分开来。读起来更清晰,更容易懂一点。

select e1.ename,e2.ename from emp e1 join emp e2 on(e1.mgr = e2.emptno);

左外连接:会把左边这张表多余数据显示出来(不能和另一张表连接)。
select e1.ename,e2,ename from emp e1 left join emp e2 on(e1.mgr =e2.empno);left 后可加outer
右外连接:
    select ename,dname from emp e right outer join dept d on(e.deptno =d.deptno); outer可以取掉。

即把左边多余数据,也把右边多余数据拿出来,全外连接。
select ename,dname from emp e full join dept d on(e.deptno =d.deptno);


求部门平均薪水的等级

----A.求部门平均薪水的等级。

select deptno,avg_sal,grade from
(select deptno,avg(sal) avg_sal from emp group by deptno)t
join salgrade s on(t.avg_sal between s.losal and s.hisal)

----B.求部门平均薪水的平均等级
select deptno,avg(grade) from
(select deptno,ename, grade from emp join salgrade s on(emp.sal between s.losal and
s.hisal)) t
group by deptno

----C.那些人是经理
select ename from emp where empno in(select mgr from emp);
select ename from emp where empno in(select distinct mgr from emp); //高效

----D.不准用组函数,求薪水的最高值(面试题)

select distinct sal from emp where sal not in(
select distinct e1.sal from emp e1 join emp e2 on (e1.sal<e2.sal));

----E.平均薪水最高的部门编号

select deptno,avg_sal from
(select avg(sal)avg_sal,deptno from emp group by deptno)
where avg_sal=
(select max(avg_sal)from
(select avg(sal) avg_sal,deptno from emp group by deptno)
)

----F.平均薪水最高的部门名称
select dname from dept where deptno=
(
select deptno from
(select avg(sal)avg_sal,deptno from emp group by deptno)
where avg_sal=
(select max(avg_sal)from
(select avg(sal) avg_sal,deptno from emp group by deptno)
)
)

----G.求平均薪水的等级最低的部门的部门名称

组函数嵌套
如:平均薪水最高的部门编号,可以E.更简单的方法如下:
select deptno,avg_sal from
(select avg(sal) avg_sal,deptno from emp group by deptno)
where avg_sal =
(select max(avg(sal)) from emp group by deptno)

组函数最多嵌套两层

分析:
首先求
1.平均薪水: select avg(sal) from group by deptno;

2.平均薪水等级:  把平均薪水当做一张表,需要和另外一张表连接salgrade
select  deptno,grade avg_sal from
  ( select deptno,avg(sal) avg_sal from emp group by deptno) t
join salgrade s on(t.avg_sal between s.losal and s.hisal)

上面结果又可当成一张表。

DEPTNO    GRADE    AVG_SAL
--------  -------  ----------
30  3   1566.66667
20  4   2175
10  4   2916.66667

3.求上表平均等级最低值

select min(grade) from
(
  select deptno,grade,avg_sal from
    (select deptno,avg(sal) avg_sal from emp group by deptno)t
  join salgrade s on(t.avg_sal between s.losal and s.hisa)
)

4.把最低值对应的2结果的那张表的对应那张表的deptno, 然后把2对应的表和另外一张表做连接。
 
select dname ,deptno,grade,avg_sal from
  (
  select deptno,grade,avg_sal from
    (select deptno,avg(sal) avg_sal from emp group by deptno)t
  join salgrade s on(t.avg_sal between s.losal and s.hisal)
  ) t1
join dept on (t1.deptno = dept.deptno)
where t1.grade =
(
  select deptno,grade,avg_sal from
    (select deptno,avg(sal) avg_sal from emp group by deptno) t
  join salgrade s on(t.avg_sal between s.losal and s.hisal)
    )
)
结果如下:

DNAME    DEPTNOGRADE    AVG_SAL
--------  -------  --------   --------
SALES303    1566.6667


----G补充: 视图(视图就是一张表,一个字查询,是虚表)

G中语句有重复,可以用视图来简化。
conn sys/bjsxt as sysdba;
grant create table,create view to scott;
conn scott/tiger

创建视图:
create view v$_dept_avg_sal_info as
select deptno,grade,avg_sal from
( select deptno,avg(sal) avg_sal from emp group by deptno)t
join salgrade s on (t.avg_sal between s.losal and s.hisal)

然后
select * from v$_dept_avg_sal_info

结果如下:
DEPTNOGRADE    AVG_SAL
--------  -------  ----------
30  3   1566.66667
20  4   2175
10  4   2916.66667

然后G中查询可以简化成:
select  dname,t1.deptno,grade,avg_sal from
v$_dept_avg_sal_info t1
join dept on(t1.deptno =dept.deptno)
where t1.grade=
(
 select min(grade) from v$_dept_avg_sal_info t1
)


----H :求部门经理人中平均薪水最低的部门名称(思考题)

----I : 求比普通员工的最高薪水还要高的经理人名称

select ename from emp
where empno in (select distinct mgr from emp where mgr is not null)
and
sal >
(
  select max(sal) from emp where empno not in
    (select distinct mgr from emp where mgr is not null)
)

----J: 求薪水最高的前5名员工:
SQL> select ename, sal from
  (select ename, sal from emp order by sal desc) where rownum <= 5;

ENAME             SAL
---------- ----------
KING             5000
SCOTT            3000
FORD             3000
JONES            2975
BLAKE            2850

----K: 求薪水最高的第6到10名雇员(重点掌握):

第一步先排序:
select ename, sal, rownum r from
( select ename, sal from emp order by sal desc );

ENAME             SAL          R
---------- ---------- ----------
KING             5000          1
FORD             3000          2
SCOTT            3000          3
JONES            2975          4
BLAKE            2850          5
CLARK            2450          6
ALLEN            1600          7
TURNER           1500          8
MILLER           1300          9
WARD             1250         10
MARTIN           1250         11

ENAME             SAL          R
---------- ---------- ----------
ADAMS            1100         12
JAMES             950         13
SMITH             800         14

14 rows selected.

之后使用子查询:
select ename, sal from
   (
     select ename, sal, rownum r from
       ( select ename, sal from emp order by sal desc )
   )
 where r between 6 and 10;

ENAME             SAL
---------- ----------
CLARK            2450
ALLEN            1600
TURNER           1500
MILLER           1300
WARD             1250

----L: 求最后入职的5名员工

----M: 比较效率(面试题)
select * from emp where deptno = 10 and ename like '%A%';
select * from emp where ename like '%A%' and deptno = 10;


这篇文章对你多有用?

相关文章

article ORACLE数据库常见问题诊断方法
ORACLE数据库是大家都非常熟悉的数据库系统,很多...

(No rating)  5-28-2011    Views: 1299   
article Oracle数据库中的字符处理技巧
在数据库开发与维护中,数据库管理员接触最多的数...

  5-31-2009    Views: 1676   
article Oracle数据库
Oracle Database,又名Oracle...

(No rating)  4-16-2012    Views: 1164   

用户评语

添加评语
当前还没有评语.


.: .: .: .: .:
[ 登陆 ]
北京护航科技有限公司 2006

Novots Technologies Limited