表的基本查詢

1、         查看表結(jié)構(gòu)

Desc dept;

2、         查看所有列

Select * from dept;

3、         查看指定列

Select ename,sal,job,deptno from emp;

4、         如何取消重復(fù)

Select distinct deptno,job from emp;

5、         set timing on;顯示查詢時(shí)間

6、         使用算數(shù)表達(dá)式

？顯示每個(gè)雇員的年工資

Select (sal+nvl(comm,0))*12 as 年工資,ename as 姓名 from emp;

使用列的別名

Select eanme “姓名”,sal*12 as “年收入” from emp;

？如何使用null值

使用nvl函數(shù)來處理

      如何連接字符串（||）

Select ename || ‘is a ’ || job from emp;

7、         使用where子句

？如何顯示工資高于3000的員工

select * from emp where sal>3000;

？如何查找1982.1.1后入職的員工

select * from emp where hiredate>'1-1月-1982';

？如何顯示工資在2000到2500的員工情況

select * from emp where sal>2000 and sal<2500;

8、         如何使用like操作符

%：表示任意0到多個(gè)字符

_:表示任意單個(gè)字符

如何顯示首字符為S的員工姓名和工資

Select ename,sal from emp where ename like ‘S%’

？如何顯示第三個(gè)字符為大寫O的所有員工的姓名和工資

Select ename,sal from emp where ename like ‘__o%’;

9、         在where條件中使用in

如何顯示empno為123,345，80…的雇員情況

Select * from emp where empno in (123,345,);

10、    使用in null 的操作符

？如何顯示沒有上級(jí)的雇員的情況

      Select * from emp where mgr is null;

11、    使用邏輯操作符號(hào)

查詢工資高于500或是崗位為MANAGER的員工，同時(shí)還要滿足他們的姓名首寫字母為大寫的J？

select * from emp where (sal>500 or job='MANAGER') and ename like 'J%';

12、    使用order by 子句

如何按照工資的從低到高的順序顯示雇員的信息？

select * from emp order by sal;

按照部門號(hào)升序而雇員的工資降序排列？

select * from emp order by deptno asc,sal desc;

13、    使用列的別名排序

Select ename, sal*12 “年薪” from emp order by “年薪” asc;

表復(fù)雜查詢

14、    數(shù)據(jù)分組-max,min avg,sum,count

如何顯示所有員工中最高工資和最低工資？

Select max(sal),min(sal) from emp;

顯示所有員工的平均工資和工資總和？

select avg(sal+nvl(comm,0)),sum(sal+nvl(comm,0)) from emp;

計(jì)算共有多少員工？

Select count(*) from emp;

擴(kuò)展要求：

請(qǐng)顯示工資最高的員工的名字，工作崗位？

select ename,job from emp where sal=(select max(sal+nvl(comm,0)) from emp);

請(qǐng)顯示工資高于平均工資的員工信息？

select * from emp where sal>(select avg(sal) from emp);

15、    group by 和having子句

group by 用于對(duì)查詢的結(jié)果分組統(tǒng)計(jì)，

having 子句用于限制分組顯示結(jié)果。

如何顯示每個(gè)部門的平均工資和最高工資？

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

顯示每個(gè)部門的每種崗位的平均工資和最低工資？

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

顯示平均工資低于2000的部門號(hào)和它的平均工資

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

l     對(duì)數(shù)據(jù)分組的總結(jié)

1、         分組函數(shù)只能出現(xiàn)在選擇列表、having、order by子句中

2、         如果在select語句中同時(shí)包含group by ，having，order by那么他們的順序是group by，having, order by

3、         在選擇列中如果有列、表達(dá)式、和分組函數(shù)，那么這些列和表達(dá)式必須有一個(gè)出現(xiàn)在group by 子句中，否則就會(huì)出錯(cuò)

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

這里deptno就一定要出現(xiàn)在group by中    

16、    多表查詢

a)      顯示雇員名，雇員工資及所在部門的名稱【笛卡爾集】？

select emp.ename,emp.sal,dept.dname from emp,dept where emp.deptno=dept.deptno;

b)      如何顯示部門號(hào)為10的部門名、員工名、和工資？

select dept.dname,emp.ename,emp.sal from emp,dept where emp.deptno=dept.deptno and emp.deptno=10;

c)      顯示各個(gè)員工的姓名，工資，及其工資的級(jí)別？

select a1.ename,a1.sal,a2.grade from emp a1,salgrade a2 where a1.sal between a2.losal and a2.hisal;

d)      顯示雇員名，雇員工資及所在部門的名字，并按部門排序？

select emp.ename,emp.sal,dept.dname from emp,dept where emp.deptno=dept.deptno order by dept.dname;

17、    自連接

自連接是指在同一張彪的鏈接查詢。

顯示某個(gè)員工的上級(jí)領(lǐng)導(dǎo)的姓名？

子查詢

1、         什么是子查詢

子查詢是指嵌入在其它sql語句中的select語句,也叫嵌套查詢

2、         單行子查詢

單行子查詢是指只返回一行數(shù)據(jù)的子查詢語句。

如何顯示與SMITH同一部門的所有員工？

select * from emp where deptno=(select deptno from emp where ename='SMITH');

3、         多行子查詢

多行子查詢是指返回多行數(shù)據(jù)的子查詢

如何查詢和部門10的 工作相同的雇員的名字、崗位、工資、部門號(hào)？

select * from emp where job in (select distinct job from emp where deptno=10；

4、         在多行子查詢中使用all操作符

如何顯示工資比部門30的所有員工的工資高的員工的姓名、工資和部門號(hào)

5、         在多行子查詢中使用any操作符

如何顯示工資比部門30的任意一個(gè)員工的工資高的員工的姓名、工資和部門號(hào)

6、         多列子查詢

單行子查詢是指子查詢只返回單列、單行數(shù)據(jù)，多行子查詢是指返回單列多行數(shù)據(jù)，都是針對(duì)單列而言的，而多列子查詢則是指查詢返回多個(gè)列數(shù)據(jù)的子查詢語句。

如何查詢與smith的部門和崗位完全相同的所有雇員？

select * from emp where (deptno,job)=(select deptno,job from emp where ename='SMITH');

7、         在from子句中使用子查詢

如何顯示高于自己部門平均工資的員工信息？

select * from emp a1,(select deptno,avg(sal) mysal from emp group by deptno) a2 where a1.deptno=a2.deptno and a1.sal>a2.mysal;

select * from emp a1 where sal>(select avg(sal) from emp a2 where a1.deptno=a2.deptno);

*在from子句中使用子查詢

       這里需要說明的當(dāng)在from子句中使用子查詢時(shí)，該子查詢會(huì)被作為一個(gè)視圖對(duì)待，因此頁叫做內(nèi)嵌視圖，當(dāng)在from子句中使用子查詢時(shí)，必須給子查詢指定別名。

8、         分頁查詢

按雇員的id號(hào)升序取出

Oracle分頁一共分3中方式

a)    rownum分頁

select a1.*,rownum rn from (select * from emp) a1;

b)    顯示rownum[oracle分配]

select * from (select a1.*,rownum rn from (select * from emp) a1 where rownum<=10) where rn>=6;

1、         根據(jù)rowid來分

Select * from t_m where rowed in(select rid from (slect rownum rn,rid from (select rowed rid,cid from t_m order by cid cesc) where rownum<10000) where rn>9980) order by cid

2、         按分析函數(shù)來分

Select * from (select t.*,row_number() over(order by cid desc) rk from t_m t) where rk<10000 and rk>9980;

3、         按rownum來分

Select * from (select t.*,rownum rn from (select * from t_m order by cid desc) t where rownum<10000) where rn>9980;

其中t_xiaoxi 為表名稱，cid為表的關(guān)鍵字段，取按cid降序排序后第9981-9999條記錄，t_m 表中20000條記錄

1、         效率最好3、次之2、最差

9、         用查詢結(jié)果創(chuàng)建新表

這個(gè)命令是一種快捷的建表方法。

Create table mytable (id,name,sal,job,deptno) as select empno,ename,sal,job,deptno from emp;

10、    合并查詢

有時(shí)在實(shí)際應(yīng)用中，為了合并多個(gè)select語句的結(jié)果，可以使用集合操作符號(hào) union, union all,intersect,minus.

1）         union

該操作符用于取得兩個(gè)結(jié)果集的并集，當(dāng)使用該操作符時(shí)，會(huì)自動(dòng)去掉結(jié)果集中重復(fù)行。

Select ename,sal,job from emp where sal>2500 union

Select ename,sal ,job from emp where sal=’manager’;

                 2)union all

該操作賦予union相似，但是它不會(huì)取消重復(fù)行，而且不會(huì)排序。

Select ename,sal,job from emp where sal>2500 union all select ename,sal,job from emp where job=’MANAGER’；

3)     intersect

使用該操作符用于取得兩個(gè)結(jié)果集的交集。

Select ename,sal,job from where sal>2500 intersect

Select ename,sal,job from emp where job=’MANAGER’;

4)    minus

使用該操作符用于取得兩個(gè)結(jié)果集的差集，它只會(huì)顯示存在第一個(gè)集合中，而不存在第二個(gè)集合中的數(shù)據(jù)。

Select ename,sal,job from emp where sal>2500 minus

Select ename,sal,job from emp where job=’MANAGER’;