1.1從表中檢索所有行和列

? ? select * from emp;

1.2從表中檢索部分行

select empno,ename,job,sal,mgr,hiredate,comm,deptno from emp;

1.3查找滿足多個條件的行

select * from emp where deptno=10

select * from emp where deptno=10 or comm is not null or sal<= 2000 and deptno = 20

select * from emp where (deptno=10 or comm is not null or sal<= 2000) and deptno = 20

1.4從表中檢索部分列

select ename,deptno,salfrom emp

1.5為列取有意義的名稱

select sal as salary,comm as commission from emp

1.6在WHERE子句中引用取別名的列 將查詢作為內(nèi)聯(lián)視圖就可以引用其中區(qū)別的列了：

select * from ? (select sal as salary,comm as commission ? from emp) x where salary < 5000

1.7連接列值

DB2 Oracle PostgreSQL:這些數(shù)據(jù)庫使用雙豎線作為連接運算符 select ename || ' WORKS AS A ' || job as msg from emp where deptno = 10

mysql:這個庫支持CONCAT函數(shù)

select concat(ename,' WORKS AS A ',job) as msg from emp where deptno = 10

SQLServer：使用 "+"運算符進行連接操作

select ename + 'WORKS AS A ' + job as msg from emp where deptno = 10

1.8在SELECT語句中使用條件邏輯 使用CASE表達式直接在SELECT語句中執(zhí)行條件邏輯

select ename,sal,

? ? case when sal <= 2000 then 'UNDERPAID'

? ? ? ? ? ? when sal >= 4000 then 'OVERPAID' ? else 'OK' ? end as status from emp

1.9限制返回的行數(shù)--分頁查詢 使用數(shù)據(jù)庫提供的內(nèi)置函數(shù)來控制返回的行數(shù)

DB2

? ? select * from emp fetch first 5 rows only

MySQL和PostgreSQL

select * from limit 5

Oracle

select * from emp where rownum <=5

SQLServer

select top 5 * from emp

1.10 從表中隨機返回n條記錄 使用DBMS支持的內(nèi)置函數(shù)來生成隨機數(shù)值粘姜。在ORDER BY 子句中使用該函數(shù)宰翅，對行進行隨機排序穴墅，然后使用前面問題 介紹的技巧孽水，來限制所返回的行(順序隨機)的數(shù)目

DB2

select ename,job from emp order by rahnd() fetch first 5 rows only

MySQL

select ename,job from emp order by rand() limit 5

PostgreSQL

select ename,job order by random() limit 5

Oracle

select * from( select ename,job from emp order by dbms_random.value() ) where rownum <= 5 SQLServer select top ename,job from emp order by newid()

1.11查找空值 要確定值是否為空票腰，必須使用 IS NULL

select * from emp where comm is null

1.12將空值轉(zhuǎn)換為實際值

select coalesce(comm,0) from emp

1.13按模式搜索 使用LIKE運算符和SQL通配符"%"

select ename,job from emp ? where deptno in(10,20) and (ename like '%I%' or job like '%ER')