常用SQL查詢語句

常用SQL查詢語句

一、簡單查詢語句

1、檢視表結構

desc emp;
           

2、查詢所有列

select * from emp;
           

3、查詢指定列

select empmo, ename, mgr FROM emp;
select distinct mgr FROM emp; -- 顯示非重複的資料
           

4、查詢指定行

SELECT * FROM emp WHERE job='CLERK';
           

5、使用算術表達式

SELECT ename, sal*13+nvl(comm,1)  FROM emp; 
nvl(comm,1)的意思是，如果comm中有值，則nvl(comm,1)=comm; comm中無值，則nvl(comm,1)=0。
SELECT ename, sal*13+nvl(comm,0) year_sal FROM emp; （year_sal為别名，可按别名排序）
SELECT * FROM emp WHERE hiredate>'01-1月-82';
           

6、使用like操作符（%，_）

%表示一個或多個字元，_表示一個字元，[charlist]表示字元列中的任何單一字元，[^charlist]或者[!charlist]不在字元列中的任何單一字元。
SELECT * FROM emp WHERE ename like 'S__T%';
           

7、在where條件中使用in

select * from emp WHERE job in('clerk', 'analyst');
           

8、查詢字段内容為空/非空的語句

select * from emp where mgr is/is not null;
           

9、使用邏輯操作符号

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

10、将查詢結果按字段的值進行排序

select * from emp order by deptno, sal DESC;(按部門升序，并按薪酬降序)
           

二、複雜查詢

1、資料分組（max ,min ,avg ,sum ,count）

select max(sal), min(age), avg(sal), sum(sal) from emp;
select * from emp where sal = (select max(sal) from emp);
select count(*) from emp;
           

2、group by（用于對查詢結果的分組統計）和having子句（使用者限制分組顯示結果）

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

對于資料分組的總結：

• a.分組函數隻能出現在選擇清單、having、order by 子句中（不能出現在where中）
• b.如果select語句中同時包含有group by,having,order by，那麼它們的順序是group by,having ,order by.
• c.在選擇列中如果有列，表達式，和分組函數，那麼這些列和表達式必須出現在group by子句中，否則就是會出錯。 注：使用group by不是使用having的前提條件

having與where最大的差別是having 能帶聚合函數，而where不可以

select emp_id from t group by emp_id having count(*) = 1 --正确
select emp_id from t group by emp_id where count(*) = 1 --錯誤
如果用where要達到這個，估計這樣
select t1.emp_id from (select emp_id,count(*) as cnt from t group by emp_id)t1 where t1.cnt=1
           

3、多表查詢

SELECT e.name,e.sal,d.dname FROM emp e, dept d WHERE e.deptno=d.deptno order by d.deptno;
SELECT e.ename,e.sal,s.grade FROM emp e,salgrade s WHER e.sal BETWEEN s.losal AND s.hisal;
           

4、自連接配接（指同一張表的連接配接查詢）

select er.ename, ee.ename mgr_name from emp er, emp ee where er.mgr = ee.empno;
           

5、子查詢（嵌入到其他sql語句中的select語句，也叫做嵌套查詢）

5.1單行子查詢

select ename from emp where deptno=(select deptno from emp where ename='SMITH');
查詢表中與Smith同部門的人員名字。因為傳回結果隻有一行，是以用“=”連接配接子查詢語句
           

5.2多行子查詢

select ename,job,sal,deptno from emp where job in(select distinct job from emp where deptno=10);
查詢表中與部門編号為10的工作相同的員工的姓名、工作、薪水、部門号。因為傳回結果又多行，是以用“in”連接配接子查詢語句
           

in與exists的差別：exists()後面的子查詢被稱做相關子查詢，它是不傳回清單的值的。值是傳回一個true或false的結果，其運作方式是先運作主查詢一次，再去子查詢與其對應的結果。如果是true則輸出，反之則不輸出。再根據主查詢中的每一行去子查詢裡去查詢。in()後面的子查詢，是傳回結果集的，換句話說執行次序和exists()不一樣。子查詢先産生結果集，然後主查詢再去結果集裡去找符合要求的字段清單去。符合要求的輸出，反之則不輸出

5.3使用ALL

select ename,sal,deptno from emp where sal>ALL(select sal from emp where deptno=30);
或select ename,sal,deptno from emp where sal>(select max(sal) from emp where deptnp=30);
查詢工資比部門号為30的所有員工工資都高的員工的姓名、薪水和部門号。以上兩個語句在功能上是一樣的，但執行效率上，函數會高很多。
           

5.4使用ANY

select ename,sal,deptno from emp where sal> ANY(select sal from emp where deptno=30);
或select ename,sal,deptno from emp where sal>(select min(sal) from emp where deptno=30);
查詢工資比部門号為30号的任意一個員工工資高（隻要比某一員工工資高即可）的員工的姓名、薪水和部門号。以上兩個語句在功能上是一樣的，但執行效率上，函數會高得多。
           

5.5多列子查詢

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

5.6在from子句中使用子查詢

select emp.deptno ,emp.ename ,emp.sal ,t_avgsal.avgsal from emp, (select emp.deptno ,avg(emp.sal) avgsal from emp group by emp.deptno)t_avgsal where emp.deptno=t_avgsal.deptno AND emp.sal>t_avgsal.avgsal order by emp.deptno;
           

5.7 分頁查詢

資料庫的每行資料都有一個對應的行号，稱為rownum
select a2.* from (select a1.* , ROWNUM rn from (select * from emp Order by sal)a1 where Rownum<=10)a2 where rn>=6;
指定查詢列、查詢結果排序等，都隻需要修改最裡層的子查詢即可
           

5.8用查詢結果建立新表

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

5.9合并查詢（union并集，intersect交集，union all并集+交集，minus差集）

select ename,sal,job from emp where sal>2500 UNION(INTERSECT/UNION ALL/MINUS) SELECT ename ,sal,job from emp where job='manager';
 合并查詢的執行效率遠高于and，or等邏輯查詢
           

5.10使用子查詢插入資料

create table myEMP(
     empID number(4),
     name varchar2(20),
     sal number(6),
     job varchar2(10),
     dept number(2));
     先建一張空表
insert into myEMP(empID,name,sal,job,dept) select empno,ename,sal,job,deptno from emp where deptno =10;
再将emp表中部門号為10的資料插入到新表myEMP中，實作資料的批量查詢。
           

5.11使用子查詢更新表中的資料

update emp set (job,sal,comm) = (select job,sal,comm from emp where ename='smith') where ename='scott';