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; countij¸ö×Ö¶Î,Èç¹ûÕâ¸ö×ֶβ»Îª¿Õ¾ÍËãÒ»¸ö.
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 ×ÔÁ´½Ó±í

°Ñij¸öÈ˵ÄÃû×ÖÒÔ¼°ËûµÄ¾­ÀíÈ˵ÄÃû×ÖÇó³öÀ´(¾­ÀíÈ˼°Õâ¸öÈËÔÚ±íÖÐͬ´¦Ò»ÐÐ)

·ÖÎö:Ê×ÏÈÇó³öÕâ¸öÈ˵ÄÃû×Ö,È¡ËûµÄ±àºÅ,È»ºó´ÓÁíÒ»ÕűíÓëÆäÏà¶ÔÓ¦±àºÅ,È»ºóÕÒµ½¾­ÀíµÄÃû×Ö.

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

empno±àºÅºÍMGR¶¼ÊDZàºÅ.

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.Çóƽ¾ùнˮµÄµÈ¼¶×îµÍµÄ²¿ÃŵIJ¿ÃÅÃû³Æ

×麯ÊýǶÌ×
È磺ƽ¾ùнˮ×î¸ßµÄ²¿ÃűàºÅ£¬¿ÉÒÔ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;