第一篇：Oracle-SQL精妙SQL語句講解

Oracle: SQL精妙SQL語句講解

一、重復操作查詢

--where條件得distinct systemdicid作為唯一標識 select *

from dmis_zd_systemdic t WHERE typeid = '06012'

and t.systemdicid in(select min(systemdicid)from dmis_zd_systemdic where typeid = '06012'

group by name)order by orderno;

二、檢查表是否存在

select count(tname)from tab where tname = upper('表名');

三、日期函數

--返回當前日期年度的第一天

select trunc(sysdate,'year')from dual;--返回當前日期月份的第一天

select trunc(sysdate,'month')from dual;--上月最后一天

select last_day(add_months(sysdate,-1))from dual;--給定日期后最近星期幾得日期

select next_day(to_date('2009-12-01', 'yyyy-mm-dd'), '星期一')next_day from dual;

四、同一張表中，根據一個字段更新另一個字段

update(select t.fgenerationtime as ftime, t.fgeneratedateall as str from dmis_fs_approvebook t where t.fgenerationtime is not null)set str = TO_CHAR(ftime, 'yyyy-mm-dd')where str is null;

五、重復數據查詢 select * FROM EMP E WHERE E.ROWID >(SELECT MIN(X.ROWID)FROM EMP X WHERE X.EMP_NO = E.EMP_NO);

六、合并不同表的數據（merge into）merge into student s using(select id, name, tel from test001)x on(s.s_id = x.id)when matched then

update set s_name = x.name when not matched then

insert(s_id, s_name, s_age)values(x.id, x.name, x.tel);commit;

七、查詢執行sql（v$sql）

select t.module, t.first_load_time, t.sql_text from v$sql t order by first_load_time desc;

2、數據庫精度修改處理--Create table /*drop table temp_data;*/ create table temp_data(FID VARCHAR2(40)not null, USEHOURS NUMBER(10)default 0, FVOLTAGE NUMBER(10)default 0, INVOLTAGE NUMBER(10)default 0);alter table TEMP_DATA add constraint tempfid primary key(FID);

insert into temp_data select a.fid, a.usehours, a.fvoltage, a.involtage from dmis_fs_factorymonthdetail a;

update dmis_fs_factorymonthdetail t set t.usehours = '', t.fvoltage = '', t.involtage = '';

alter table DMIS_FS_FACTORYMONTHDETAIL modify USEHOURS NUMBER(10,1);alter table DMIS_FS_FACTORYMONTHDETAIL modify FVOLTAGE NUMBER(10,1);alter table DMIS_FS_FACTORYMONTHDETAIL modify INVOLTAGE NUMBER(10,1);

update(select a.usehours as tusehours, b.usehours as fusehours, a.fvoltage as tfvoltage, b.fvoltage as ffvoltage, a.involtage as tinvoltage, b.involtage as finvoltage, a.fid as ffid, b.fid as tfid from dmis_fs_factorymonthdetail a, temp_data b where a.fid = b.fid)tt set tt.tusehours = tt.fusehours, tt.tfvoltage = tt.ffvoltage, tt.tinvoltage = tt.finvoltage where ffid = tfid;drop table temp_data;commit;

3、恢復drop掉的存儲過程 用sys用戶登陸，執行如下的查詢：

SQL> select text from dba_source as of timestamp to_timestamp('2009-03-06 09:45:00', 'YYYY-MM-DD

HH24:MI:SS')

where

owner='IPRA'

and

name=

'P_IPACCHECK_NC' order by line;

4、刪除某個用戶下的對象--刪除某個用戶下的對象 set heading off;set feedback off;spool c:dropobj.sql;prompt--Drop constraint select 'alter table '||table_name||' drop constraint '||constraint_name||';' from user_constraints where constraint_type='R';prompt--Drop tables select 'drop table '||table_name ||';' from user_tables;

prompt--Drop view select 'drop view ' ||view_name||';' from user_views;

prompt--Drop sequence select

--行列轉換 行轉列

DROP TABLE t_change_lc;CREATE TABLE t_change_lc(card_code VARCHAR2(3), q NUMBER, bal NUMBER);INSERT INTO t_change_lc SELECT '001' card_code, ROWNUM q, trunc(dbms_random.VALUE * 100)bal FROM dual CONNECT BY ROWNUM <= 4 UNION

SELECT '002' card_code, ROWNUM q, trunc(dbms_random.VALUE * 100)bal FROM dual CONNECT BY ROWNUM <= 4;SELECT * FROM t_change_lc;SELECT a.card_code, SUM(decode(a.q, 1, a.bal, 0))q1, SUM(decode(a.q, 2, a.bal, 0))q2, SUM(decode(a.q, 3, a.bal, 0))q3, SUM(decode(a.q, 4, a.bal, 0))q4 FROM t_change_lc a GROUP BY a.card_code ORDER BY 1;--行列轉換 列轉行

DROP TABLE t_change_cl;CREATE TABLE t_change_cl AS SELECT a.card_code, SUM(decode(a.q, 1, a.bal, 0))q1, SUM(decode(a.q, 2, a.bal, 0))q2, SUM(decode(a.q, 3, a.bal, 0))q3, SUM(decode(a.q, 4, a.bal, 0))q4 FROM t_change_lc a GROUP BY a.card_code ORDER BY 1;SELECT * FROM t_change_cl;SELECT t.card_code, t.rn q, decode(t.rn, 1, t.q1, 2, t.q2, 3, t.q3, 4, t.q4)bal FROM(SELECT a.*, b.rn FROM t_change_cl a,(SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM <= 4)b)t ORDER BY 1, 2;

--行列轉換 行轉列 合并

DROP TABLE t_change_lc_comma;CREATE TABLE t_change_lc_comma AS SELECT card_code,'quarter_'||q AS q FROM t_change_lc;SELECT * FROM t_change_lc_comma;SELECT t1.card_code, substr(MAX(sys_connect_by_path(t1.q, ';')), 2)q FROM(SELECT a.card_code, a.q, row_number()over(PARTITION BY a.card_code ORDER BY a.q)rn FROM t_change_lc_comma a)t1 START WITH t1.rn = 1

CONNECT BY t1.card_code = PRIOR t1.card_code AND t1.rn1 = PRIOR t1.rn GROUP BY t1.card_code;SELECT * FROM t_change_cl_comma;SELECT t.card_code, substr(t.q, instr(';' || t.q, ';', 1, rn), instr(t.q || ';', ';', 1, rn)-instr(';' || t.q, ';', 1, rn))q FROM(SELECT a.card_code, a.q, b.rn FROM t_change_cl_comma a,(SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM <= 100)b WHERE instr(';' || a.q, ';', 1, rn)> 0)t ORDER BY 1, 2;

--實現一條記錄根據條件多表插入 DROP TABLE t_ia_src;CREATE TABLE t_ia_src AS SELECT 'a'||ROWNUM c1, 'b'||ROWNUM c2 FROM dual CONNECT BY ROWNUM<=5;DROP TABLE t_ia_dest_1;CREATE TABLE t_ia_dest_1(flag VARCHAR2(10), c VARCHAR2(10));DROP TABLE t_ia_dest_2;CREATE TABLE t_ia_dest_2(flag VARCHAR2(10), c VARCHAR2(10));DROP TABLE t_ia_dest_3;CREATE TABLE t_ia_dest_3(flag VARCHAR2(10), c VARCHAR2(10));SELECT * FROM t_ia_src;SELECT * FROM t_ia_dest_1;SELECT * FROM t_ia_dest_2;SELECT * FROM t_ia_dest_3;INSERT ALL

WHEN(c1 IN('a1','a3'))THEN

INTO t_ia_dest_1(flag,c)VALUES(flag1,c2)WHEN(c1 IN('a2','a4'))THEN

INTO t_ia_dest_2(flag,c)VALUES(flag2,c2)ELSE

INTO t_ia_dest_3(flag,c)VALUES(flag1||flag2,c1||c2)SELECT c1,c2, 'f1' flag1, 'f2' flag2 FROM t_ia_src;

--如果存在就更新，不存在就插入用一個語句實現 DROP TABLE t_mg;CREATE TABLE t_mg(code VARCHAR2(10), NAME VARCHAR2(10));SELECT * FROM t_mg;MERGE INTO t_mg a USING(SELECT 'the code' code, 'the name' NAME FROM dual)b ON(a.code = b.code)WHEN MATCHED THEN

UPDATE SET a.NAME = b.NAME WHEN NOT MATCHED THEN

INSERT(code, NAME)VALUES(b.code, b.NAME);

--抽取/刪除重復記錄 DROP TABLE t_dup;CREATE TABLE t_dup AS SELECT 'code_'||ROWNUM code, dbms_random.string('z',5)NAME FROM dual CONNECT BY ROWNUM<=10;INSERT INTO t_dup SELECT 'code_'||ROWNUM code, dbms_random.string('z',5)NAME FROM dual CONNECT BY ROWNUM<=2;SELECT * FROM t_dup;SELECT * FROM t_dup a WHERE a.ROWID <>(SELECT MIN(b.ROWID)FROM t_dup b WHERE a.code=b.code);SELECT b.code, b.NAME FROM(SELECT a.code, a.NAME, row_number()over(PARTITION BY a.code ORDER BY a.ROWID)rn FROM t_dup a)b WHERE b.rn > 1;--IN/EXISTS的不同適用環境--t_orders.customer_id有索引 SELECT a.*

FROM t_employees a WHERE a.employee_id IN

(SELECT b.sales_rep_id FROM t_orders b WHERE b.customer_id = 12);SELECT a.*

FROM t_employees a WHERE EXISTS(SELECT 1 FROM t_orders b WHERE b.customer_id = 12

AND a.employee_id = b.sales_rep_id);--t_employees.department_id有索引 SELECT a.*

FROM t_employees a WHERE a.department_id = 10 AND EXISTS

(SELECT 1 FROM t_orders b WHERE a.employee_id = b.sales_rep_id);SELECT a.*

FROM t_employees a WHERE a.department_id = 10

AND a.employee_id IN(SELECT b.sales_rep_id FROM t_orders b);--FBI DROP TABLE t_fbi;CREATE TABLE t_fbi AS

SELECT ROWNUM rn, dbms_random.STRING('z',10)NAME , SYSDATE + dbms_random.VALUE * 10 dt FROM dual CONNECT BY ROWNUM <=10;CREATE INDEX idx_nonfbi ON t_fbi(dt);DROP INDEX idx_fbi_1;CREATE INDEX idx_fbi_1 ON t_fbi(trunc(dt));SELECT * FROM t_fbi WHERE trunc(dt)= to_date('2006-09-21','yyyy-mm-dd');--不建議使用

SELECT * FROM t_fbi WHERE to_char(dt, 'yyyy-mm-dd')= '2006-09-21';--LOOP中的COMMIT/ROLLBACK DROP TABLE t_loop PURGE;create TABLE t_loop AS SELECT * FROM user_objects WHERE 1=2;SELECT * FROM t_loop;--逐行提交 DECLARE BEGIN

FOR cur IN(SELECT * FROM user_objects)LOOP INSERT INTO t_loop VALUES cur;COMMIT;END LOOP;END;

--模擬批量提交 DECLARE

v_count NUMBER;BEGIN

FOR cur IN(SELECT * FROM user_objects)LOOP INSERT INTO t_loop VALUES cur;v_count := v_count + 1;IF v_count >= 100 THEN COMMIT;END IF;END LOOP;COMMIT;END;

--真正的批量提交 DECLARE CURSOR cur IS

SELECT * FROM user_objects;TYPE rec IS TABLE OF user_objects%ROWTYPE;recs rec;BEGIN OPEN cur;WHILE(TRUE)LOOP FETCH cur BULK COLLECT INTO recs LIMIT 100;

--forall 實現批量

FORALL i IN 1..recs.COUNT

INSERT INTO t_loop VALUES recs(i);COMMIT;EXIT WHEN cur%NOTFOUND;END LOOP;CLOSE cur;END;

--悲觀鎖定/樂觀鎖定 DROP TABLE t_lock PURGE;CREATE TABLE t_lock AS SELECT 1 ID FROM dual;SELECT * FROM t_lock;

--常見的實現邏輯，隱含bug DECLARE v_cnt NUMBER;BEGIN

--這里有并發性的bug SELECT MAX(ID)INTO v_cnt FROM t_lock;--here for other operation v_cnt := v_cnt + 1;INSERT INTO t_lock(ID)VALUES(v_cnt);COMMIT;END;

--高并發環境下，安全的實現邏輯 DECLARE v_cnt NUMBER;BEGIN

--對指定的行取得lock SELECT ID INTO v_cnt FROM t_lock WHERE ID=1 FOR UPDATE;

--在有lock的情況下繼續下面的操作

SELECT MAX(ID)INTO v_cnt FROM t_lock;--here for other operation v_cnt := v_cnt + 1;INSERT INTO t_lock(ID)VALUES(v_cnt);COMMIT;--提交并且釋放lock END;

--硬解析/軟解析

DROP TABLE t_hard PURGE;CREATE TABLE t_hard(ID INT);SELECT * FROM t_hard;DECLARE

sql_1 VARCHAR2(200);BEGIN

--hard parse--java中的同等語句是 Statement.execute()FOR i IN 1..1000 LOOP sql_1 := 'insert into t_hard(id)values(' || i || ')';EXECUTE IMMEDIATE sql_1;END LOOP;COMMIT;--soft parse--java中的同等語句是 PreparedStatement.execute()sql_1 := 'insert into t_hard(id)values(:id)';FOR i IN 1..1000 LOOP EXECUTE IMMEDIATE sql_1 USING i;END LOOP;COMMIT;END;

--正確的分頁算法 SELECT *

FROM(SELECT a.*, ROWNUM rn FROM(SELECT * FROM t_employees ORDER BY first_name)a WHERE ROWNUM <= 500)WHERE rn > 480;

--分頁算法(why not this one)SELECT a.*, ROWNUM rn FROM(SELECT * FROM t_employees ORDER BY first_name)a WHERE ROWNUM <= 500 AND ROWNUM > 480;

--分頁算法(why not this one)SELECT b.*

FROM(SELECT a.*, ROWNUM rn FROM t_employees a WHERE ROWNUM < = 500 ORDER BY first_name)b WHERE b.rn > 480;--OLAP

--小計合計 SELECT CASE

WHEN a.deptno IS NULL THEN '合計'

WHEN a.deptno IS NOT NULL AND a.empno IS NULL THEN '小計' ELSE

'' || a.deptno END deptno, a.empno, a.ename, SUM(a.sal)total_sal FROM scott.emp a GROUP BY GROUPING SETS((a.deptno),(a.deptno, a.empno, a.ename),());

--分組排序 SELECT a.deptno, a.empno, a.ename, a.sal,--可跳躍的rank rank()over(PARTITION BY a.deptno ORDER BY a.sal DESC)r1,--密集型rank dense_rank()over(PARTITION BY a.deptno ORDER BY a.sal DESC)r2,--不分組排序

rank()over(ORDER BY sal DESC)r3 FROM scott.emp a ORDER BY a.deptno,a.sal DESC;--當前行數據和前/后n行的數據比較 SELECT a.empno, a.ename, a.sal,--上面一行

lag(a.sal)over(ORDER BY a.sal DESC)lag_1,--下面三行

lead(a.sal, 3)over(ORDER BY a.sal DESC)lead_3 FROM scott.emp a ORDER BY a.sal DESC;

第二篇：Oracle SQL精妙SQL語句講解

SQL*PLUS界面：

登錄：輸入SQLPLUS回車；輸入正確的ORACLE用戶名并回車；輸入用戶口令并回車，顯示提示符：SQL>

退出：輸入EXIT即可。

2）命令的編輯與運行：

在命令提示符后輸入SQL命令并運行，以分號結束輸入；以斜杠結束輸入；以空行結束輸入；

利用SQL緩沖區進行PL/SQL塊的編輯和運行；

利用命令文件進行PL/SQL塊的編輯和運行。

數據庫查詢

用SELECT語句從表中提取查詢數據。語法為

SELECT [DISTINCT] {column1,column2,…} FROM tablename WHERE {conditions} GROUP BY {conditions} ORDER BY {expressions} [ASC/DESC];

說明：SELECT子句用于指定檢索數據庫的中哪些列，FROM子句用于指定從哪一個表或視圖中檢索數據。

SELECT中的操作符及多表查詢WHERE子句。(LIKE,IS,…)

WHERE子句中的條件可以是一個包含等號或不等號的條件表達式，也可以是一個含有IN、NOT IN、BETWEEN、LIKE、IS NOT NULL等比較運算符的條件式，還可以是由單一的條件表達通過邏輯運算符組合成復合條件。

ORDER BY 子句

ORDER BY 子句使得SQL在顯示查詢結果時將各返回行按順序排列，返回行的排列順序由ORDER BY 子句指定的表達式的值確定。

連接查詢

利用SELECT語句進行數據庫查詢時，可以把多個表、視圖的數據結合起來，使得查詢結果的每一行中包含來自多個表達式或視圖的數據，這種操作被稱為連接查詢。

連接查詢的方法是在SELECT命令的FROM子句中指定兩個或多個將被連接查詢的表或視圖，并且在WHERE子句告訴ORACLE如何把多個表的數據進行合并。根據WHERE子句中的條件表達式是等還是不等式，可以把連接查詢分為等式連接和不等式連接。

子查詢

如果某一個SELECT命令（查詢1）出現在另一個SQL命令（查詢2）的一個子句中，則稱查詢1是查詢2的子查詢。

基本數據類型（NUMBER,VARCHAR2,DATE）O

RACEL支持下列內部數據類型：

VARCHAR2 變長字符串，最長為2000字符。

NUMBER 數值型。

LONG 變長字符數據，最長為2G字節。

DATE 日期型。

RAW 二進制數據，最長為255字節。

LONG RAW 變長二進制數據，最長為2G字節。

ROWID 二六進制串，表示表的行的唯一地址。

CHAR 定長字符數據，最長為255。

常用函數用法：

一個函數類似于一個算符，它操作數據項，返回一個結果。函數在格式上不同于算符，它個具有變元，可操作0個、一個、二個或多個變元，形式為：

函數名（變元，變元，…）

函數具有下列一般類形：

單行函數

分組函數

單行函數對查詢的表或視圖的每一行返回一個結果行。它有數值函數，字符函數，日期函數，轉換函數等。

分組函數返回的結果是基于行組而不是單行，所以分組函數不同于單行函數。在許多分組函數中可有下列選項：

DISTRNCT 該選項使分組函數只考慮變元表達式中的不同值。

ALL該選項使分組函數考慮全部值，包含全部重復。

全部分組函數（除COUNT（*）外）忽略空值。如果具有分組函數的查詢，沒有返回行或只有空值（分組函數的變元取值的行），則分組函數返回空值。

5、數據操縱語言命令：

數據庫操縱語言（DML）命令用于查詢和操縱模式對象中的數據，它不隱式地提交當前事務。它包含UPDATE、INSERT、DELETE、EXPLAIN PLAN、SELECT和LOCK TABLE 等命令。下面簡單介紹一下：

1)UPDATE tablename SET {column1=expression1,column2=expression2,…} WHERE {conditions};

例如：S QL>UPDATE EMP

SET JOB =’MANAGER’

WHERE ENAME=’MAPTIN’;

SQL >SELECT * FROM EMP;

UPDATE子句指明了要修改的數據庫是EMP,并用WHERE子句限制了只對名字(ENAME)為’MARTIN’的職工的數據進行修改,SET子句則說明修改的方式,即把’MARTION’的工作名稱(JOB)改為’MARAGER’.2)INSERT INTO tablename {column1,column2,…} VALUES {expression1,expression2,…};

例如：SQL>SELECT INTO DEPT（DNAME，DEPTNO）

VALUES（‘ACCOUNTING’，10）

3)DELETE FROM tablename WHERE {conditions};

例如：SQL>DELETE FROM EMP

WHERE EMPNO = 7654;

DELETE命令刪除一條記錄,而且DELETE命令只能刪除整行,而不能刪除某行中的部分數據.4）事務控制命令

提交命令(COMMIT):可以使數據庫的修改永久化.設置AUTOCOMMIT為允許狀態:SQL >SET AUTOCOMMIT ON;

回滾命令(ROLLBACK):消除上一個COMMIT命令后的所做的全部修改,使得數據庫的內容恢復到上一個COMMIT執行后的狀態.使用方法是:

SQL>ROLLBACK;

創建表、視圖、索引、同義詞、用戶。、表是存儲用戶數據的基本結構。

建立表主要指定義下列信息：

列定義

完整性約束

表所在表空間

存儲特性

可選擇的聚集

從一查詢獲得數據

語法如下：CREATE TABLE tablename

(column1 datatype [DEFAULT expression] [constraint], column1 datatype [DEFAULT expression] [constraint], ……）

[STORAGE子句] [其他子句…]；

例如：

SQL>CREATE TABLE NEW_DEPT(DPTNO NUMBER(2), DNAME CHAR(6), LOC CHAR(13);

更改表作用：

增加列

增加完整性約束

重新定義列（數據類型、長度、缺省值）

修改存儲參數或其它參數

使能、使不能或刪除一完整性約束或觸發器

顯式地分配一個范圍

2）、視圖

視圖是一個邏輯表，它允許操作者從其它表或視圖存取數據，視圖本身不包含數據。視圖所基于的表稱為基表。

引入視圖有下列作用：

提供附加的表安全級，限制存取基表的行或/和列集合。

隱藏數據復雜性。

為數據提供另一種觀點。

促使ORACLE的某些操作在包含視圖的數據庫上執行，而不在另一個數據庫上執行。

3）、索引

索引是種數據庫對象。對于在表或聚集的索引列上的每一值將包含一項，為行提供直接的快速存取。在下列情況ORACLE可利用索引改進性能：

按指定的索引列的值查找行。

按索引列的順序存取表。

建立索引： CREATE [UNIQUE] INDEX indexname ON tablename(column ,。。)；

例如：SQL>CREAT INDEX IC_EMP

ON CLUSTER EMPLOYEE

4）、同義詞

同義詞：為表、視圖、序列、存儲函數、包、快照或其它同義詞的另一個名字。使用同義詞為了安全和方便。對一對象建立同義詞可有下列好處：

引用對象不需指出對象的持有者。

引用對象不需指出它所位于的數據庫。

為對象提供另一個名字。

建立同義詞：

CREATE SYNONYM symnon_name FOR [username.]tablename;

例如：CREAT PUBLIC SYNONYM EMP

FOR SCOTT.EMP @SALES

5）、用戶

CREATE USER username IDENTIFIED BY password;

例如：SQL>CREATE USER SIDNEY

IDENTIFIED BY CARTON;

Oracle擴展PL/SQL簡介

PL/SQL概述。

PL/SQL是Oracle對SQL規范的擴展，是一種塊結構語言，即構成一個PL/SQL程序的基本單位（過程、函數和無名塊）是邏輯塊，可包含任何數目的嵌套了快。這種程序結構支持逐步求精方法解決問題。一個塊（或子塊）將邏輯上相關的說明和語句組合在一起，其形式為：

DECLARE

---說明

BEGIN

---語句序列

EXCEPTION

---例外處理程序

END；

它有以下優點:

支持SQL;

生產率高;

性能好;

可稱植性;

與ORACLE集成.PL/SQL體系結構

PL/SQL運行系統是種技術，不是一種獨立產品，可認為這種技術是PL/SQL塊和子程序的一種機，它可接收任何有效的PL/SQL塊或子程序。如圖所示：

PL/SQL機可執行過程性語句，而將SQL語句發送到ORACLE服務器上的SQL語句執行器。在ORACLE預編譯程序或OCI程序中可嵌入無名的PL/SQL塊。如果ORACLE具有PROCEDURAL選件，有名的PL/SQL塊（子程序）可單獨編譯，永久地存儲在數據庫中，準備執行。

PL/SQL基礎：

PL/SQL有一字符集、保留字、標點、數據類型、嚴密語法等，它與SQL有相同表示，現重點介紹。

1）、數據類型：如下表所示

數據類型 子類型

純量類型 數值 BINARY_INTEGER NATURAL,POSITIVE

NUMBER DEC,DECIMAL,DOUBLE PRECISION,PLOAT,INTEGER,INT,NUMERIC,REAL,SMALLINT

字符 CHAR CHARACTER,STRING

VARCHAR2 VARCHAR

LONG

LONG RAW

RAW

RAWID

邏輯 BOOLEAN

日期 DATE

組合 類型 記錄 RECORD

表 TABLE

2）、變量和常量

在PL/SQL程序中可將值存儲在變量和常量中，當程序執行時，變量的值可以改變，而常量的值不能改變。

3）、程序塊式結構：

DECLARE

變量說明部分；

BEGIN

執行語句部分；

[EXCEPTION

例外處理部分；] END;控制語句：

分支語句：

IF condition THEN

Sequence_of_statements;

END IF;

IF condition THEN

Sequence_of_statement1;

ELSE

Sequence_of_statement2;

END IF;

IF condition1 THEN

Sequence_of_statement1;

ELSIF condition2 THEN

Sequence_of_statement2;

ELSIF condition3 THEN

Sequence_of_statement3;

END IF;

循環語句：

LOOP

Sequence_of_statements;

IF condition THEN

EXIT;

END IF;

END LOOP;

WHILE condition LOOP

Sequence_of_statements;

END LOOP;

FOR counter IN lower_bound..higher_bound LOOP

Sequence_of_statements;

END LOOP;

子程序：

存儲過程：

CREATE PROCEDURE 過程名(參數說明1，參數說明2，[局部說明]

BEGIN

執行語句；

END 過程名；

。)IS。

存儲函數：

CREATE FUNCTION 函數名(參數說明1，參數說明2。。)RETURN 類型 IS [局部說明] BEGIN

執行語句；

END 函數名

Oracle SQL精妙SQL語句講解

好東西，大家趕緊收藏吧~~~

--行列轉換 行轉列

DROP TABLE t_change_lc;CREATE TABLE t_change_lc(card_code VARCHAR2(3), q NUMBER, bal NUMBER);

INSERT INTO t_change_lc

SELECT '001' card_code, ROWNUM q, trunc(dbms_random.VALUE * 100)bal FROM dual CONNECT BY ROWNUM <= 4 UNION

SELECT '002' card_code, ROWNUM q, trunc(dbms_random.VALUE * 100)bal FROM dual CONNECT BY ROWNUM <= 4;

SELECT * FROM t_change_lc;

SELECT a.card_code，SUM(decode(a.q, 1, a.bal, 0))q1，SUM(decode(a.q, 2, a.bal, 0))q2，SUM(decode(a.q, 3, a.bal, 0))q3，SUM(decode(a.q, 4, a.bal, 0))q4

FROM t_change_lc a GROUP BY a.card_code ORDER BY 1;

--行列轉換 列轉行

DROP TABLE t_change_cl;CREATE TABLE t_change_cl AS SELECT a.card_code，SUM(decode(a.q, 1, a.bal, 0))q1，SUM(decode(a.q, 2, a.bal, 0))q2，SUM(decode(a.q, 3, a.bal, 0))q3，SUM(decode(a.q, 4, a.bal, 0))q4

FROM t_change_lc a GROUP BY a.card_code ORDER BY 1;

SELECT * FROM t_change_cl;

SELECT t.card_code，t.rn q，decode(t.rn, 1, t.q1, 2, t.q2, 3, t.q3, 4, t.q4)bal

FROM(SELECT a.*, b.rn

FROM t_change_cl a，(SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM <= 4)b)t ORDER BY 1, 2;

--行列轉換 行轉列 合并

DROP TABLE t_change_lc_comma;CREATE TABLE t_change_lc_comma AS SELECT card_code,'quarter_'||q AS q FROM t_change_lc;

SELECT * FROM t_change_lc_comma;

SELECT t1.card_code, substr(MAX(sys_connect_by_path(t1.q, ';')), 2)q

FROM(SELECT a.card_code，a.q，row_number()over(PARTITION BY a.card_code ORDER BY a.q)rn

FROM t_change_lc_comma a)t1 START WITH t1.rn = 1 CONNECT BY t1.card_code = PRIOR t1.card_code

AND t1.rn1 = PRIOR t1.rn GROUP BY t1.card_code;

SELECT * FROM t_change_cl_comma;SELECT t.card_code，substr(t.q，instr(';' || t.q, ';', 1, rn)，instr(t.q || ';', ';', 1, rn)-instr(';' || t.q, ';', 1, rn))q

FROM(SELECT a.card_code, a.q, b.rn

FROM t_change_cl_comma a，(SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM <= 100)b

WHERE instr(';' || a.q, ';', 1, rn)> 0)t ORDER BY 1, 2;

--實現一條記錄根據條件多表插入 DROP TABLE t_ia_src;CREATE TABLE t_ia_src AS SELECT 'a'||ROWNUM c1, 'b'||ROWNUM c2 FROM dual CONNECT BY ROWNUM<=5;DROP TABLE t_ia_dest_1;CREATE TABLE t_ia_dest_1(flag VARCHAR2(10), c VARCHAR2(10));DROP TABLE t_ia_dest_2;CREATE TABLE t_ia_dest_2(flag VARCHAR2(10), c VARCHAR2(10));DROP TABLE t_ia_dest_3;CREATE TABLE t_ia_dest_3(flag VARCHAR2(10), c VARCHAR2(10));

SELECT * FROM t_ia_src;SELECT * FROM t_ia_dest_1;SELECT * FROM t_ia_dest_2;SELECT * FROM t_ia_dest_3;

INSERT ALL WHEN(c1 IN('a1','a3'))THEN

INTO t_ia_dest_1(flag,c)VALUES(flag1,c2)WHEN(c1 IN('a2','a4'))THEN

INTO t_ia_dest_2(flag,c)VALUES(flag2,c2)ELSE INTO t_ia_dest_3(flag,c)VALUES(flag1||flag2,c1||c2)SELECT c1,c2, 'f1' flag1, 'f2' flag2 FROM t_ia_src;

--如果存在就更新，不存在就插入用一個語句實現 DROP TABLE t_mg;CREATE TABLE t_mg(code VARCHAR2(10), NAME VARCHAR2(10));

SELECT * FROM t_mg;

MERGE INTO t_mg a USING(SELECT 'the code' code, 'the name' NAME FROM dual)b ON(a.code = b.code)WHEN MATCHED THEN

UPDATE SET a.NAME = b.NAME WHEN NOT MATCHED THEN

INSERT(code, NAME)VALUES(b.code, b.NAME);

--抽取/刪除重復記錄

DROP TABLE t_dup;CREATE TABLE t_dup AS SELECT 'code_'||ROWNUM code, dbms_random.string('z',5)NAME FROM dual CONNECT BY ROWNUM<=10;

INSERT INTO t_dup SELECT 'code_'||ROWNUM code, dbms_random.string('z',5)NAME FROM dual CONNECT BY ROWNUM<=2;

SELECT * FROM t_dup;

SELECT * FROM t_dup a WHERE a.ROWID <>(SELECT MIN(b.ROWID)FROM t_dup b WHERE a.code=b.code);

SELECT b.code, b.NAME

FROM(SELECT a.code，a.NAME，row_number()over(PARTITION BY a.code ORDER BY a.ROWID)rn

FROM t_dup a)b WHERE b.rn > 1;

--IN/EXISTS的不同適用環境--t_orders.customer_id有索引 SELECT a.*

FROM t_employees a WHERE a.employee_id IN

(SELECT b.sales_rep_id FROM t_orders b WHERE b.customer_id = 12);

SELECT a.*

FROM t_employees a WHERE EXISTS(SELECT 1

FROM t_orders b

WHERE b.customer_id = 12

AND a.employee_id = b.sales_rep_id);

--t_employees.department_id有索引 SELECT a.*

FROM t_employees a WHERE a.department_id = 10

AND EXISTS(SELECT 1 FROM t_orders b WHERE a.employee_id = b.sales_rep_id);

SELECT a.*

FROM t_employees a WHERE a.department_id = 10

AND a.employee_id IN(SELECT b.sales_rep_id FROM t_orders b);

--FBI DROP TABLE t_fbi;CREATE TABLE t_fbi AS SELECT ROWNUM rn, dbms_random.STRING('z',10)NAME , SYSDATE + dbms_random.VALUE * 10 dt FROM dual

CONNECT BY ROWNUM <=10;

CREATE INDEX idx_nonfbi ON t_fbi(dt);

DROP INDEX idx_fbi_1;CREATE INDEX idx_fbi_1 ON t_fbi(trunc(dt));

SELECT * FROM t_fbi WHERE trunc(dt)= to_date('2006-09-21','yyyy-mm-dd');

--不建議使用

SELECT * FROM t_fbi WHERE to_char(dt, 'yyyy-mm-dd')= '2006-09-21';

--LOOP中的COMMIT/ROLLBACK DROP TABLE t_loop PURGE;create TABLE t_loop AS SELECT * FROM user_objects WHERE 1=2;

SELECT * FROM t_loop;

--逐行提交 DECLARE BEGIN

FOR cur IN(SELECT * FROM user_objects)LOOP

INSERT INTO t_loop VALUES cur;

COMMIT;

END LOOP;END;

--模擬批量提交 DECLARE

v_count NUMBER;BEGIN

FOR cur IN(SELECT * FROM user_objects)LOOP

INSERT INTO t_loop VALUES cur;

v_count := v_count + 1;

IF v_count >= 100 THEN

COMMIT;

END IF;

END LOOP;

COMMIT;END;

--真正的批量提交 DECLARE

CURSOR cur IS

SELECT * FROM user_objects;

TYPE rec IS TABLE OF user_objects%ROWTYPE;

recs rec;BEGIN

OPEN cur;

WHILE(TRUE)LOOP

FETCH cur BULK COLLECT

INTO recs LIMIT 100;

--forall 實現批量

FORALL i IN 1..recs.COUNT

INSERT INTO t_loop VALUES recs(i);

COMMIT;

EXIT WHEN cur%NOTFOUND;

END LOOP;

CLOSE cur;END;

--悲觀鎖定/樂觀鎖定

DROP TABLE t_lock PURGE;CREATE TABLE t_lock AS SELECT 1 ID FROM dual;

SELECT * FROM t_lock;

--常見的實現邏輯，隱含bug DECLARE

v_cnt NUMBER;BEGIN

--這里有并發性的bug

SELECT MAX(ID)INTO v_cnt FROM t_lock;

--here for other operation

v_cnt := v_cnt + 1;

INSERT INTO t_lock(ID)VALUES(v_cnt);

COMMIT;END;

--高并發環境下，安全的實現邏輯 DECLARE

v_cnt NUMBER;BEGIN

--對指定的行取得lock

SELECT ID INTO v_cnt FROM t_lock WHERE ID=1 FOR UPDATE;

--在有lock的情況下繼續下面的操作

SELECT MAX(ID)INTO v_cnt FROM t_lock;

--here for other operation

v_cnt := v_cnt + 1;

INSERT INTO t_lock(ID)VALUES(v_cnt);

COMMIT;--提交并且釋放lock END;

--硬解析/軟解析

DROP TABLE t_hard PURGE;CREATE TABLE t_hard(ID INT);

SELECT * FROM t_hard;

DECLARE

sql_1

VARCHAR2(200);BEGIN

--hard parse

--java中的同等語句是 Statement.execute()

FOR i IN 1..1000 LOOP

sql_1 := 'insert into t_hard(id)values(' || i || ')';

EXECUTE IMMEDIATE sql_1;

END LOOP;

COMMIT;

--soft parse

--java中的同等語句是 PreparedStatement.execute()

sql_1

:= 'insert into t_hard(id)values(:id)';

FOR i IN 1..1000 LOOP

EXECUTE IMMEDIATE sql_1

USING i;

END LOOP;

COMMIT;END;

--正確的分頁算法

SELECT *

FROM(SELECT a.*, ROWNUM rn

FROM(SELECT * FROM t_employees ORDER BY first_name)a

WHERE ROWNUM <= 500)WHERE rn > 480;

--分頁算法(why not this one)SELECT a.*, ROWNUM rn

FROM(SELECT * FROM t_employees ORDER BY first_name)a WHERE ROWNUM <= 500 AND ROWNUM > 480;

--分頁算法(why not this one)SELECT b.*

FROM(SELECT a.*, ROWNUM rn

FROM t_employees a

WHERE ROWNUM < = 500

ORDER BY first_name)b WHERE b.rn > 480;

--OLAP--小計合計 SELECT CASE

WHEN a.deptno IS NULL THEN

'合計'

WHEN a.deptno IS NOT NULL AND a.empno IS NULL THEN

'小計'

ELSE

'' || a.deptno

END deptno，a.empno，a.ename，SUM(a.sal)total_sal

FROM scott.emp a GROUP BY GROUPING SETS((a.deptno),(a.deptno, a.empno, a.ename),());

--分組排序 SELECT a.deptno，a.empno，a.ename，a.sal，--可跳躍的rank

rank()over(PARTITION BY a.deptno ORDER BY a.sal DESC)r1，--密集型rank

dense_rank()over(PARTITION BY a.deptno ORDER BY a.sal DESC)r2，--不分組排序

rank()over(ORDER BY sal DESC)r3

FROM scott.emp a

ORDER BY a.deptno,a.sal DESC;

--當前行數據和前/后n行的數據比較 SELECT a.empno，a.ename，a.sal，--上面一行

lag(a.sal)over(ORDER BY a.sal DESC)lag_1，--下面三行

lead(a.sal, 3)over(ORDER BY a.sal DESC)lead_3

FROM scott.emp a ORDER BY a.sal DESC;

一、數據表設計圖

二、創建語句

/*================*/ /* DBMS name: ORACLE Version 9i */ /* Created on: 2008-11-10 23:39:24 */ /*================*/

alter table “emp”

drop constraint FK_EMP_REFERENCE_DEPT;

drop table “dept” cascade constraints;

drop table “emp” cascade constraints;

drop table “salgrade” cascade constraints;

/*================*/ /* Table: “dept” */ /*================*/

create table dept(deptno NUMBER(11)not null, dname VARCHAR2(15)not null, loc VARCHAR2(15)not null, constraint PK_DEPT primary key(deptno));

/*================*/ /* Table: “emp” */ /*================*/

create table emp(empno NUMBER(11)not null, deptno NUMBER(11), ename VARCHAR2(15)not null, sal NUMBER(11)not null, job VARCHAR2(15)not null, mgr NUMBER(11)not null, hirdate DATE not null, comm NUMBER(11)not null, constraint PK_EMP primary key(empno));

/*================*/ /* Table: salgrade */ /*================*/

create table salgrade(grade NUMBER(11)not null, losal NUMBER(11)not null, hisal NUMBER(11)not null, constraint PK_SALGRADE primary key(grade));

alter table emp add constraint FK_EMP_REFERENCE_DEPT foreign key(deptno)references dept(deptno);

三、測試要求及語句

/** *公司工資最高的員工列表 子查詢 */

select t.ename,t.sal from emp t where t.sal =(select max(sal)from emp)

/** *查詢每一個員工的經理人及自己的名字 */

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

/** *查詢公司平均薪水的等級 */

select s.grade from salgrade s where(select avg(t.sal)from emp t)between s.losal and s.hisal

/** *求部門中那些人的工資最高 */

select d.dname,ename,sal from(select t.deptno,ename,sal from(select deptno,max(sal)as max_sal from emp group by deptno)e join emp t on(e.deptno = t.deptno and t.sal = max_sal))et join dept d on(d.deptno = et.deptno)

/** *查詢部門平均薪水的等級 */

select d.dname,avg_sal,grade from(select deptno,avg_sal,grade from(select deptno,avg(sal)as avg_sal from emp group by deptno)e join salgrade s on(e.avg_sal between s.losal and s.hisal))es join dept d on(es.deptno = d.deptno)

/** *求部門的平均薪水等級 */

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

/** * 求那些人是經理人 */

select ename from emp e where empno in(select distinct mgr from emp)

/** *不準用組函數 求薪水的最高值 */

select ename from emp where empno not in(select distinct e1.empno from emp e1 join emp e2 on(e1.sal

/** *平均薪水最高的部門編號與名稱 */

select d.deptno,dname from(select deptno,avg(sal)avg_sal from emp group by deptno)t1 join dept d on(d.deptno = t1.deptno)where avg_sal =(select max(avg_sal)from(select deptno,avg(sal)avg_sal from emp group by deptno)t2)

/** *求平均薪水的等級最低的部門名稱 */

select dname from dept d where d.deptno in(select deptno from(select deptno,grade from(select deptno,avg(sal)avg_sal from emp group by deptno)t1 join salgrade g on(avg_sal between g.losal and g.hisal))t2 where t2.grade =(select min(grade)from(select deptno,grade from(select deptno,avg(sal)avg_sal from emp group by deptno)t1 join salgrade g on(avg_sal between g.losal and g.hisal))t3))

/** *求部門經理人中平均薪水最低的部門名稱 */

select d.dname,t1.avg_sal from dept d join(select deptno,avg(sal)avg_sal from(select e2.deptno,e2.ename,e2.sal from emp e1 join emp e2 on(e1.mgr = e2.empno))t group by deptno)t1 on(d.deptno = t1.deptno)where avg_sal =(select min(avg_sal)from(select deptno,avg(sal)avg_sal from(select e2.deptno,e2.ename,e2.sal from emp e1 join emp e2 on(e1.mgr = e2.empno))t group by deptno))/** *求必普通員工的最高薪水還要高的經理人名稱 */

select ename from(select e2.ename,e2.empno,e2.sal from emp e1 join emp e2 on(e1.mgr = e2.empno))t where t.sal >(select max(e.sal)from emp e where e.empno not in(select e1.mgr from emp e1 join emp e2 on(e1.mgr = e2.empno)))

/** *求薪水最高的第6名到10名雇員 */

SELECT * FROM(SELECT A.*, ROWNUM RN FROM(SELECT * FROM(select e1.ename,e1.sal from emp e1 order by e1.sal desc))A WHERE ROWNUM <= 10)WHERE RN >= 6

第三篇：Oracle SQL精妙SQL語句講解

好東西，大家趕緊收藏吧~~~ 轉自junsansi

--行列轉換 行轉列

DROP TABLE t_change_lc;CREATE TABLE t_change_lc(card_code VARCHAR2(3), q NUMBER, bal NUMBER);

INSERT INTO t_change_lc

SELECT '001' card_code, ROWNUM q, trunc(dbms_random.VALUE * 100)bal FROM dual CONNECT BY ROWNUM <= 4 UNION

SELECT '002' card_code, ROWNUM q, trunc(dbms_random.VALUE * 100)bal FROM dual CONNECT BY ROWNUM <= 4;

SELECT * FROM t_change_lc;

SELECT a.card_code，SUM(decode(a.q, 1, a.bal, 0))q1，SUM(decode(a.q, 2, a.bal, 0))q2，SUM(decode(a.q, 3, a.bal, 0))q3，SUM(decode(a.q, 4, a.bal, 0))q4 FROM t_change_lc a GROUP BY a.card_code ORDER BY 1;

--行列轉換 列轉行

DROP TABLE t_change_cl;CREATE TABLE t_change_cl AS SELECT a.card_code，SUM(decode(a.q, 1, a.bal, 0))q1，SUM(decode(a.q, 2, a.bal, 0))q2，SUM(decode(a.q, 3, a.bal, 0))q3，SUM(decode(a.q, 4, a.bal, 0))q4 FROM t_change_lc a GROUP BY a.card_code ORDER BY 1;

SELECT * FROM t_change_cl;

SELECT t.card_code，t.rn q，decode(t.rn, 1, t.q1, 2, t.q2, 3, t.q3, 4, t.q4)bal FROM(SELECT a.*, b.rn

FROM t_change_cl a，(SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM <= 4)b)t ORDER BY 1, 2;

--行列轉換 行轉列 合并

DROP TABLE t_change_lc_comma;CREATE TABLE t_change_lc_comma AS SELECT card_code,'quarter_'||q AS q FROM t_change_lc;

SELECT * FROM t_change_lc_comma;

SELECT t1.card_code, substr(MAX(sys_connect_by_path(t1.q, ';')), 2)q FROM(SELECT a.card_code，a.q，row_number()over(PARTITION BY a.card_code ORDER BY a.q)rn

FROM t_change_lc_comma a)t1 START WITH t1.rn = 1 CONNECT BY t1.card_code = PRIOR t1.card_code

AND t1.rn1 = PRIOR t1.rn GROUP BY t1.card_code;

SELECT * FROM t_change_cl_comma;

SELECT t.card_code，substr(t.q，instr(';' || t.q, ';', 1, rn)，instr(t.q || ';', ';', 1, rn)-instr(';' || t.q, ';', 1, rn))q FROM(SELECT a.card_code, a.q, b.rn

FROM t_change_cl_comma a，(SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM <= 100)b

WHERE instr(';' || a.q, ';', 1, rn)> 0)t ORDER BY 1, 2;

--實現一條記錄根據條件多表插入 DROP TABLE t_ia_src;CREATE TABLE t_ia_src AS SELECT 'a'||ROWNUM c1, 'b'||ROWNUM c2 FROM dual CONNECT BY ROWNUM<=5;DROP TABLE t_ia_dest_1;CREATE TABLE t_ia_dest_1(flag VARCHAR2(10), c VARCHAR2(10));DROP TABLE t_ia_dest_2;CREATE TABLE t_ia_dest_2(flag VARCHAR2(10), c VARCHAR2(10));DROP TABLE t_ia_dest_3;CREATE TABLE t_ia_dest_3(flag VARCHAR2(10), c VARCHAR2(10));

SELECT * FROM t_ia_src;SELECT * FROM t_ia_dest_1;SELECT * FROM t_ia_dest_2;SELECT * FROM t_ia_dest_3;

INSERT ALL WHEN(c1 IN('a1','a3'))THEN

INTO t_ia_dest_1(flag,c)VALUES(flag1,c2)WHEN(c1 IN('a2','a4'))THEN

INTO t_ia_dest_2(flag,c)VALUES(flag2,c2)ELSE INTO t_ia_dest_3(flag,c)VALUES(flag1||flag2,c1||c2)SELECT c1,c2, 'f1' flag1, 'f2' flag2 FROM t_ia_src;

--如果存在就更新，不存在就插入用一個語句實現 DROP TABLE t_mg;CREATE TABLE t_mg(code VARCHAR2(10), NAME VARCHAR2(10));

SELECT * FROM t_mg;

MERGE INTO t_mg a USING(SELECT 'the code' code, 'the name' NAME FROM dual)b ON(a.code = b.code)WHEN MATCHED THEN UPDATE SET a.NAME = b.NAME WHEN NOT MATCHED THEN INSERT(code, NAME)VALUES(b.code, b.NAME);

--抽取/刪除重復記錄

DROP TABLE t_dup;CREATE TABLE t_dup AS SELECT 'code_'||ROWNUM code, dbms_random.string('z',5)NAME FROM dual CONNECT BY ROWNUM<=10;INSERT INTO t_dup SELECT 'code_'||ROWNUM code, dbms_random.string('z',5)NAME FROM dual CONNECT BY ROWNUM<=2;

SELECT * FROM t_dup;

SELECT * FROM t_dup a WHERE a.ROWID <>(SELECT MIN(b.ROWID)FROM t_dup b WHERE a.code=b.code);

SELECT b.code, b.NAME FROM(SELECT a.code，a.NAME，row_number()over(PARTITION BY a.code ORDER BY a.ROWID)rn

FROM t_dup a)b WHERE b.rn > 1;

--IN/EXISTS的不同適用環境--t_orders.customer_id有索引 SELECT a.* FROM t_employees a WHERE a.employee_id IN

(SELECT b.sales_rep_id FROM t_orders b WHERE b.customer_id = 12);

SELECT a.* FROM t_employees a WHERE EXISTS(SELECT 1

FROM t_orders b

WHERE b.customer_id = 12

AND a.employee_id = b.sales_rep_id);

--t_employees.department_id有索引 SELECT a.* FROM t_employees a WHERE a.department_id = 10 AND EXISTS(SELECT 1 FROM t_orders b WHERE a.employee_id = b.sales_rep_id);

SELECT a.* FROM t_employees a WHERE a.department_id = 10 AND a.employee_id IN(SELECT b.sales_rep_id FROM t_orders b);--FBI DROP TABLE t_fbi;CREATE TABLE t_fbi AS SELECT ROWNUM rn, dbms_random.STRING('z',10)NAME , SYSDATE + dbms_random.VALUE * 10 dt FROM dual CONNECT BY ROWNUM <=10;

CREATE INDEX idx_nonfbi ON t_fbi(dt);

DROP INDEX idx_fbi_1;CREATE INDEX idx_fbi_1 ON t_fbi(trunc(dt));

SELECT * FROM t_fbi WHERE trunc(dt)= to_date('2006-09-21','yyyy-mm-dd');

--不建議使用

SELECT * FROM t_fbi WHERE to_char(dt, 'yyyy-mm-dd')= '2006-09-21';

--LOOP中的COMMIT/ROLLBACK DROP TABLE t_loop PURGE;create TABLE t_loop AS SELECT * FROM user_objects WHERE 1=2;

SELECT * FROM t_loop;

--逐行提交 DECLARE BEGIN FOR cur IN(SELECT * FROM user_objects)LOOP

INSERT INTO t_loop VALUES cur;

COMMIT;END LOOP;END;

--模擬批量提交 DECLARE v_count NUMBER;BEGIN FOR cur IN(SELECT * FROM user_objects)LOOP

INSERT INTO t_loop VALUES cur;

v_count := v_count + 1;

IF v_count >= 100 THEN COMMIT;

END IF;END LOOP;COMMIT;END;

--真正的批量提交 DECLARE CURSOR cur IS

SELECT * FROM user_objects;TYPE rec IS TABLE OF user_objects%ROWTYPE;recs rec;BEGIN OPEN cur;WHILE(TRUE)LOOP

FETCH cur BULK COLLECT

INTO recs LIMIT 100;

--forall 實現批量

FORALL i IN 1..recs.COUNT

INSERT INTO t_loop VALUES recs(i);

COMMIT;

EXIT WHEN cur%NOTFOUND;END LOOP;CLOSE cur;END;

--悲觀鎖定/樂觀鎖定

DROP TABLE t_lock PURGE;CREATE TABLE t_lock AS SELECT 1 ID FROM dual;

SELECT * FROM t_lock;

--常見的實現邏輯，隱含bug DECLARE v_cnt NUMBER;BEGIN--這里有并發性的bug SELECT MAX(ID)INTO v_cnt FROM t_lock;

--here for other operation v_cnt := v_cnt + 1;INSERT INTO t_lock(ID)VALUES(v_cnt);COMMIT;END;

--高并發環境下，安全的實現邏輯 DECLARE v_cnt NUMBER;BEGIN--對指定的行取得lock SELECT ID INTO v_cnt FROM t_lock WHERE ID=1 FOR UPDATE;--在有lock的情況下繼續下面的操作

SELECT MAX(ID)INTO v_cnt FROM t_lock;

--here for other operation v_cnt := v_cnt + 1;INSERT INTO t_lock(ID)VALUES(v_cnt);COMMIT;--提交并且釋放lock END;

--硬解析/軟解析

DROP TABLE t_hard PURGE;CREATE TABLE t_hard(ID INT);

SELECT * FROM t_hard;

DECLARE sql_1 VARCHAR2(200);BEGIN--hard parse--java中的同等語句是 Statement.execute()FOR i IN 1..1000 LOOP

sql_1 := 'insert into t_hard(id)values(' || i || ')';

EXECUTE IMMEDIATE sql_1;END LOOP;COMMIT;

--soft parse--java中的同等語句是 PreparedStatement.execute()sql_1 := 'insert into t_hard(id)values(:id)';FOR i IN 1..1000 LOOP

EXECUTE IMMEDIATE sql_1

USING i;END LOOP;COMMIT;END;

--正確的分頁算法

SELECT * FROM(SELECT a.*, ROWNUM rn

FROM(SELECT * FROM t_employees ORDER BY first_name)a

WHERE ROWNUM <= 500)WHERE rn > 480;

--分頁算法(why not this one)SELECT a.*, ROWNUM rn FROM(SELECT * FROM t_employees ORDER BY first_name)a WHERE ROWNUM <= 500 AND ROWNUM > 480;

--分頁算法(why not this one)SELECT b.* FROM(SELECT a.*, ROWNUM rn

FROM t_employees a

WHERE ROWNUM < = 500

ORDER BY first_name)b WHERE b.rn > 480;--OLAP--小計合計 SELECT CASE

WHEN a.deptno IS NULL THEN

'合計'

WHEN a.deptno IS NOT NULL AND a.empno IS NULL THEN

'小計'

ELSE

'' || a.deptno

END deptno，a.empno，a.ename，SUM(a.sal)total_sal FROM scott.emp a GROUP BY GROUPING SETS((a.deptno),(a.deptno, a.empno, a.ename),());

--分組排序 SELECT a.deptno，a.empno，a.ename，a.sal，--可跳躍的rank

rank()over(PARTITION BY a.deptno ORDER BY a.sal DESC)r1，--密集型rank

dense_rank()over(PARTITION BY a.deptno ORDER BY a.sal DESC)r2，--不分組排序

rank()over(ORDER BY sal DESC)r3 FROM scott.emp a ORDER BY a.deptno,a.sal DESC;--當前行數據和前/后n行的數據比較 SELECT a.empno，a.ename，a.sal，--上面一行

lag(a.sal)over(ORDER BY a.sal DESC)lag_1，--下面三行

lead(a.sal, 3)over(ORDER BY a.sal DESC)lead_3 FROM scott.emp a ORDER BY a.sal DESC;

第四篇：精妙SQL語句

精妙SQL語句

? 說明：復制表(只復制結構,源表名：a 新表名：b)select * into b from a where 1<>1

? 說明：拷貝表(拷貝數據,源表名：a 目標表名：b)insert into b(a, b, c)select d,e,f from b;? 說明：顯示文章、提交人和最后回復時間

select a.title,a.username,b.adddate from table a,(select max(adddate)adddate from table where table.title=a.title)b

? 說明：外連接查詢(表名1：a 表名2：b)

select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c ? 說明：日程安排提前五分鐘提醒

select * from 日程安排 where datediff('minute',f開始時間,getdate())>5 ? 說明：兩張關聯表，刪除主表中已經在副表中沒有的信息

delete from info where not exists(select * from infobz where info.infid=infobz.infid)

? 說明：--

SQL:

SELECT A.NUM, A.NAME, B.UPD_DATE, B.PREV_UPD_DATE

FROM TABLE1，(SELECT X.NUM, X.UPD_DATE, Y.UPD_DATE PREV_UPD_DATE

FROM(SELECT NUM, UPD_DATE, INBOUND_QTY, STOCK_ONHAND

FROM TABLE2

WHERE TO_CHAR(UPD_DATE,'YYYY/MM')= TO_CHAR(SYSDATE, 'YYYY/MM'))X，(SELECT NUM, UPD_DATE, STOCK_ONHAND

FROM TABLE2

WHERE TO_CHAR(UPD_DATE,'YYYY/MM')=

TO_CHAR(TO_DATE(TO_CHAR(SYSDATE, 'YYYY/MM')|| '/01','YYYY/MM/DD')1 FROM Handle a)

? 一個SQL語句的問題:行列轉換 select * from v_temp 上面的視圖結果如下: user_name role_name-------------------------系統管理員 管理員

feng 管理員

feng 一般用戶

test 一般用戶

想把結果變成這樣: user_name role_name---------------------------系統管理員 管理員

feng 管理員,一般用戶

test 一般用戶

=================== create table a_test(name varchar(20),role2 varchar(20))insert into a_test values('李','管理員')insert into a_test values('張','管理員')insert into a_test values('張','一般用戶')insert into a_test values('常','一般用戶')

create function join_str(@content varchar(100))returns varchar(2000)as begin declare @str varchar(2000)set @str='' select @str=@str+','+rtrim(role2)from a_test where [name]=@content select @str=right(@str,len(@str)-1)return @str end go

--調用：

select [name],dbo.join_str([name])role2 from a_test group by [name]

--select distinct name,dbo.uf_test(name)from a_test ? 快速比較結構相同的兩表

結構相同的兩表，一表有記錄3萬條左右，一表有記錄2萬條左右，我怎樣快速查找兩表的不同記錄？

============================ 給你一個測試方法，從northwind中的orders表取數據。select * into n1 from orders select * into n2 from orders

select * from n1 select * from n2

--添加主鍵，然后修改n1中若干字段的若干條

alter table n1 add constraint pk_n1_id primary key(OrderID)alter table n2 add constraint pk_n2_id primary key(OrderID)

select OrderID from(select * from n1 union select * from n2)a group by OrderID having count(*)> 1

應該可以，而且將不同的記錄的ID顯示出來。下面的適用于雙方記錄一樣的情況，select * from n1 where orderid in(select OrderID from(select * from n1 union select * from n2)a group by OrderID having count(*)> 1)至于雙方互不存在的記錄是比較好處理的--刪除n1,n2中若干條記錄

delete from n1 where orderID in('10728','10730')delete from n2 where orderID in('11000','11001')

--*************************************************************--雙方都有該記錄卻不完全相同

select * from n1 where orderid in(select OrderID from(select * from n1 union select * from n2)a group by OrderID having count(*)> 1)union--n2中存在但在n1中不存的在10728,10730 select * from n1 where OrderID not in(select OrderID from n2)union--n1中存在但在n2中不存的在11000,11001 select * from n2 where OrderID not in(select OrderID from n1)? 四種方法取表里n到m條紀錄：

1.select top m * into 臨時表(或表變量)from tablename order by columnname--將top m筆插入 set rowcount n select * from 表變量 order by columnname desc

2.select top n * from(select top m * from tablename order by columnname)a order by columnname desc

3.如果tablename里沒有其他identity列，那么： select identity(int)id0,* into #temp from tablename

取n到m條的語句為：

select * from #temp where id0 >=n and id0 <= m

如果你在執行select identity(int)id0,* into #temp from tablename這條語句的時候報錯,那是因為你的DB中間的select into/bulkcopy屬性沒有打開要先執行： exec sp_dboption 你的DB名字,'select into/bulkcopy',true

4.如果表里有identity屬性，那么簡單：

select * from tablename where identitycol between n and m ? 如何刪除一個表中重復的記錄？

create table a_dist(id int,name varchar(20))

insert into a_dist values(1,'abc')insert into a_dist values(1,'abc')insert into a_dist values(1,'abc')insert into a_dist values(1,'abc')

exec up_distinct 'a_dist','id'

select * from a_dist

create procedure up_distinct(@t_name varchar(30),@f_key varchar(30))--f_key表示是分組字段﹐即主鍵字段 as begin declare @max integer,@id varchar(30),@sql varchar(7999),@type integer select @sql = 'declare cur_rows cursor for select '+@f_key+' ,count(*)from ' +@t_name +' group by ' +@f_key +' having count(*)> 1' exec(@sql)open cur_rows fetch cur_rows into @id,@max while @@fetch_status=0 begin

select @max = @max-1 set rowcount @max

select @type = xtype from syscolumns where id=object_id(@t_name)and name=@f_key if @type=56 select @sql = 'delete from '+@t_name+' where ' + @f_key+' = '+ @id if @type=167 select @sql = 'delete from '+@t_name+' where ' + @f_key+' = '+''''+ @id +'''' exec(@sql)fetch cur_rows into @id,@max end

close cur_rows deallocate cur_rows set rowcount 0 end

select * from systypes select * from syscolumns where id = object_id('a_dist')? 查詢數據的最大排序問題（只能用一條語句寫）

CREATE TABLE hard(qu char(11),co char(11),je numeric(3, 0))

insert into hard values('A','1',3)insert into hard values('A','2',4)insert into hard values('A','4',2)insert into hard values('A','6',9)insert into hard values('B','1',4)insert into hard values('B','2',5)insert into hard values('B','3',6)insert into hard values('C','3',4)insert into hard values('C','6',7)insert into hard values('C','2',3)

要求查詢出來的結果如下：

qu co je

---------------------------A 6 9 A 2 4 B 3 6 B 2 5 C 6 7 C 3 4

就是要按qu分組，每組中取je最大的前2位！而且只能用一句sql語句！！

select * from hard a where je in(select top 2 je from hard b where a.qu=b.qu order by je)

? 求刪除重復記錄的sql語句？

怎樣把具有相同字段的紀錄刪除，只留下一條。例如，表test里有id,name字段

如果有name相同的記錄 只留下一條，其余的刪除。name的內容不定，相同的記錄數不定。有沒有這樣的sql語句？

============================== A:一個完整的解決方案：

將重復的記錄記入temp1表: select [標志字段id],count(*)into temp1 from [表名] group by [標志字段id] having count(*)>1

2、將不重復的記錄記入temp1表: insert temp1 select [標志字段id],count(*)from [表名] group by [標志字段id] having count(*)=1

3、作一個包含所有不重復記錄的表：

select * into temp2 from [表名] where 標志字段id in(select 標志字段id from temp1)

4、刪除重復表: delete [表名]

5、恢復表：

insert [表名] select * from temp2

6、刪除臨時表: drop table temp1 drop table temp2 ================================ B: create table a_dist(id int,name varchar(20))

insert into a_dist values(1,'abc')insert into a_dist values(1,'abc')insert into a_dist values(1,'abc')insert into a_dist values(1,'abc')

exec up_distinct 'a_dist','id'

select * from a_dist

create procedure up_distinct(@t_name varchar(30),@f_key varchar(30))--f_key表示是分組字段﹐即主鍵字段 as begin declare @max integer,@id varchar(30),@sql varchar(7999),@type integer select @sql = 'declare cur_rows cursor for select '+@f_key+' ,count(*)from ' +@t_name +' group by ' +@f_key +' having count(*)> 1' exec(@sql)open cur_rows

fetch cur_rows into @id,@max while @@fetch_status=0 begin

select @max = @max-1 set rowcount @max

select @type = xtype from syscolumns where id=object_id(@t_name)and name=@f_key if @type=56 select @sql = 'delete from '+@t_name+' where ' + @f_key+' = '+ @id if @type=167 select @sql = 'delete from '+@t_name+' where ' + @f_key+' = '+''''+ @id +'''' exec(@sql)fetch cur_rows into @id,@max end

close cur_rows deallocate cur_rows set rowcount 0 end

select * from systypes select * from syscolumns where id = object_id('a_dist')? 行列轉換--普通

假設有張學生成績表(CJ)如下

Name Subject Result 張三 語文 80 張三 數學 90 張三 物理 85 李四 語文 85 李四 數學 92 李四 物理 82

想變成

姓名 語文 數學 物理

張三 80 90 85 李四 85 92 82

declare @sql varchar(4000)set @sql = 'select Name'

select @sql = @sql + ',sum(case Subject when '''+Subject+''' then Result end)['+Subject+']'

from(select distinct Subject from CJ)as a

select @sql = @sql+' from test group by name' exec(@sql)

行列轉換--合并

有表A, id pid 1 1 1 2 1 3 2 1 2 2 3 1

如何化成表B: id pid 1 1,2,3 2 1,2 3 1

創建一個合并的函數

create function fmerg(@id int)returns varchar(8000)as begin

declare @str varchar(8000)set @str=''

select @str=@str+','+cast(pid as varchar)from 表A where id=@id set @str=right(@str,len(@str)-1)return(@str)End go

--調用自定義函數得到結果

select distinct id,dbo.fmerg(id)from 表A ? 如何取得一個數據表的所有列名

方法如下：先從SYSTEMOBJECT系統表中取得數據表的SYSTEMID,然后再SYSCOLUMN表中取得該數據表的所有列名。

SQL語句如下：

declare @objid int,@objname char(40)set @objname = 'tablename'

select @objid = id from sysobjects where id = object_id(@objname)

select 'Column_name' = name from syscolumns where id = @objid order by colid

或

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME ='users'

? 通過SQL語句來更改用戶的密碼

修改別人的,需要sysadmin role

EXEC sp_password NULL, 'newpassword', 'User'

如果帳號為SA執行EXEC sp_password NULL, 'newpassword', sa ? 怎么判斷出一個表的哪些字段不允許為空？

select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where IS_NULLABLE='NO' and TABLE_NAME=tablename ? 如何在數據庫里找到含有相同字段的表？

a.查已知列名的情況

SELECT b.name as TableName,a.name as columnname From syscolumns a INNER JOIN sysobjects b ON a.id=b.id AND b.type='U'

AND a.name='你的字段名字'

? 未知列名查所有在不同表出現過的列名

Select o.name As tablename,s1.name As columnname From syscolumns s1, sysobjects o Where s1.id = o.id And o.type = 'U' And Exists（Select 1 From syscolumns s2 Where s1.name = s2.name And s1.id <> s2.id)

? 查詢第xxx行數據

假設id是主鍵：

select * from(select top xxx * from yourtable)aa where not exists(select 1 from(select top xxx-1 * from yourtable)bb where aa.id=bb.id)

如果使用游標也是可以的

fetch absolute [number] from [cursor_name] 行數為絕對行數

? SQL Server日期計算

a.一個月的第一天

SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)b.本周的星期一

SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)c.一年的第一天

SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)d.季度的第一天

SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)e.上個月的最后一天

SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))f.去年的最后一天

SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))g.本月的最后一天

SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))h.本月的第一個星期一

select DATEADD(wk, DATEDIFF(wk,0, dateadd(dd,6-datepart(day,getdate()),getdate())), 0)

i.本年的最后一天

SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))。

? 獲取表結構[把 'sysobjects' 替換 成 'tablename' 即可]

SELECT CASE IsNull(I.name, '')When '' Then '' Else '*'

End as IsPK，Object_Name(A.id)as t_name, A.name as c_name，IsNull(SubString(M.text, 1, 254), '')as pbc_init, T.name as F_DataType，CASE IsNull(TYPEPROPERTY(T.name, 'Scale'), '')WHEN '' Then Cast(A.prec as varchar)

ELSE Cast(A.prec as varchar)+ ',' + Cast(A.scale as varchar)END as F_Scale，A.isnullable as F_isNullAble FROM Syscolumns as A JOIN Systypes as T

ON(A.xType = T.xUserType AND A.Id = Object_id('sysobjects'))LEFT JOIN(SysIndexes as I JOIN Syscolumns as A1

ON(I.id = A1.id and A1.id = object_id('sysobjects')and(I.status & 0x800)= 0x800 AND A1.colid <= I.keycnt))

ON(A.id = I.id AND A.name = index_col('sysobjects', I.indid, A1.colid))LEFT JOIN SysComments as M

ON(M.id = A.cdefault and ObjectProperty(A.cdefault, 'IsConstraint')= 1)ORDER BY A.Colid ASC

? 提取數據庫內所有表的字段詳細說明的SQL語句

SELECT

(case when a.colorder=1 then d.name else '' end)N'表名', a.colorder N'字段序號', a.name N'字段名'，(case when COLUMNPROPERTY(a.id,a.name,'IsIdentity')=1 then ''else '' end)N'標識'，(case when(SELECT count(*)FROM sysobjects WHERE(name in(SELECT name FROM sysindexes

WHERE(id = a.id)AND(indid in(SELECT indid

FROM sysindexkeys

WHERE(id = a.id)AND(colid in(SELECT colid FROM syscolumns

WHERE(id = a.id)AND(name = a.name)))))))AND(xtype = 'PK'))>0 then '' else '' end)N'主鍵', b.name N'類型'，a.length N'占用字節數'，COLUMNPROPERTY(a.id,a.name,'PRECISION')as N'長度'，isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0)as N'小數位數',(case when a.isnullable=1 then ''else '' end)N'允許空', isnull(e.text,'')N'默認值'，isnull(g.[value],'')AS N'字段說明' FROM syscolumns a left join systypes b on a.xtype=b.xusertype inner join sysobjects d

on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' left join syscomments e on a.cdefault=e.id

left join sysproperties g

on a.id=g.id AND a.colid = g.smallid order by object_name(a.id),a.colorder

? 快速獲取表test的記錄總數[對大容量表非常有效]

快速獲取表test的記錄總數:

select rows from sysindexes where id = object_id('test')and indid in(0,1)

update 2 set KHXH=(ID+1)2 2行遞增編號

update [23] set id1 = 'No.'+right('00000000'+id,6)where id not like 'No%' //遞增

update [23] set id1= 'No.'+right('00000000'+replace(id1,'No.',''),6)//補位遞增 delete from [1] where(id%2)=1 奇數

? 替換表名字段

update [1] set domurl = replace(domurl,'Upload/Imgswf/','Upload/Photo/')where domurl like '%Upload/Imgswf/%' ? 截位

SELECT LEFT(表名, 5)

下列語句部分是Mssql語句，不可以在access中使用。

SQL分類：

DDL-數據定義語言(CREATE，ALTER，DROP，DECLARE)

DML-數據操縱語言(SELECT，DELETE，UPDATE，INSERT)

DCL-數據控制語言(GRANT，REVOKE，COMMIT，ROLLBACK)

首先,簡要介紹基礎語句：

1、說明：創建數據庫

CREATE DATABASE database-name

2、說明：刪除數據庫

drop database dbname

3、說明：備份sql server

---創建 備份數據的 device

USE master

EXEC sp_addumpdevice 'disk', 'testBack', 'c:mssql7backupMyNwind_1.dat'

---開始 備份

BACKUP DATABASE pubs TO testBack

4、說明：創建新表

create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)

根據已有的表創建新表：

A：create table tab_new like tab_old(使用舊表創建新表)

B：create table tab_new as select col1,col2… from tab_old definition only

5、說明：刪除新表drop table tabname

6、說明：增加一個列

Alter table tabname add column col type

注：列增加后將不能刪除。DB2中列加上后數據類型也不能改變，唯一能改變的是增加varchar類型的長度。

7、說明：添加主鍵： Alter table tabname add primary key(col)

說明：刪除主鍵： Alter table tabname drop primary key(col)

8、說明：創建索引：create [unique] index idxname on tabname(col….)

刪除索引：drop index idxname

注：索引是不可更改的，想更改必須刪除重新建。

9、說明：創建視圖：create view viewname as select statement

刪除視圖：drop view viewname

10、說明：幾個簡單的基本的sql語句

選擇：select * from table1 where 范圍

插入：insert into table1(field1,field2)values(value1,value2)

刪除：delete from table1 where 范圍

更新：update table1 set field1=value1 where 范圍

查找：select * from table1 where field1 like ?%value1%?---like的語法很精妙，查資料!

排序：select * from table1 order by field1,field2 [desc]

總數：select count * as totalcount from table1

求和：select sum(field1)as sumvalue from table1

平均：select avg(field1)as avgvalue from table1

最大：select max(field1)as maxvalue from table1

最小：select min(field1)as minvalue from table1

11、說明：幾個高級查詢運算詞

A： UNION 運算符

UNION 運算符通過組合其他兩個結果表（例如 TABLE1 和 TABLE2）并消去表中任何重復行而派生出一個結果表。當 ALL 隨 UNION 一起使用時（即 UNION ALL），不消除重復行。兩種情況下，派生表的每一行不是來自 TABLE1 就是來自 TABLE2。

B： EXCEPT 運算符

EXCEPT 運算符通過包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重復行而派生出一個結果表。當 ALL 隨 EXCEPT 一起使用時(EXCEPT ALL)，不消除重復行。

C： INTERSECT 運算符

INTERSECT 運算符通過只包括 TABLE1 和 TABLE2 中都有的行并消除所有重復行而派生出一個結果表。當 ALL 隨 INTERSECT 一起使用時(INTERSECT ALL)，不消除重復行。

注：使用運算詞的幾個查詢結果行必須是一致的。

12、說明：使用外連接

A、left outer join：

左外連接（左連接）：結果集幾包括連接表的匹配行，也包括左連接表的所有行。

SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c

B：right outer join:

右外連接(右連接)：結果集既包括連接表的匹配連接行，也包括右連接表的所有行。

C：full outer join：

全外連接：不僅包括符號連接表的匹配行，還包括兩個連接表中的所有記錄。

其次，大家來看一些不錯的sql語句

1、說明：復制表(只復制結構,源表名：a 新表名：b)(Access可用)

法一：select * into b from a where 1<>1

法二：select top 0 * into b from a

2、說明：拷貝表(拷貝數據,源表名：a 目標表名：b)(Access可用)

insert into b(a, b, c)select d,e,f from b;

3、說明：跨數據庫之間表的拷貝(具體數據使用絕對路徑)(Access可用)

insert into b(a, b, c)select d,e,f from b in ?具體數據庫? where 條件

例子：..from b in '“&Server.MapPath(”.“)&”data.mdb“ &”' where..4、說明：子查詢(表名1：a 表名2：b)

select a,b,c from a where a IN(select d from b)或者: select a,b,c from a where a IN(1,2,3)

5、說明：顯示文章、提交人和最后回復時間

select a.title,a.username,b.adddate from table a,(select max(adddate)adddate from table where table.title=a.title)b

6、說明：外連接查詢(表名1：a 表名2：b)

select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c

7、說明：在線視圖查詢(表名1：a)

select * from(SELECT a,b,c FROM a)T where t.a > 1;

8、說明：between的用法,between限制查詢數據范圍時包括了邊界值,not between不包括

select * from table1 where time between time1 and time2

select a,b,c, from table1 where a not between 數值1 and 數值2

9、說明：in 的使用方法

select * from table1 where a [not] in(?值1?,?值2?,?值4?,?值6?)

10、說明：兩張關聯表，刪除主表中已經在副表中沒有的信息

delete from table1 where not exists(select * from table2 where table1.field1=table2.field1)

11、說明：四表聯查問題：

select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where.....12、說明：日程安排提前五分鐘提醒

SQL: select * from 日程安排 where datediff('minute',f開始時間,getdate())>5

13、說明：一條sql 語句搞定數據庫分頁

select top 10 b.* from(select top 20 主鍵字段,排序字段 from 表名 order by 排序字段 desc)a,表名 b where b.主鍵字段 = a.主鍵字段 order by a.排序字段

14、說明：前10條記錄

select top 10 * form table1 where 范圍

15、說明：選擇在每一組b值相同的數據中對應的a最大的記錄的所有信息(類似這樣的用法可以用于論壇每月排行榜,每月熱銷產品分析,按科目成績排名,等等.)

select a,b,c from tablename ta where a=(select max(a)from tablename tb where tb.b=ta.b)

16、說明：包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重復行而派生出一個結果表

(select a from tableA)except(select a from tableB)except(select a from tableC)

17、說明：隨機取出10條數據

select top 10 * from tablename order by newid()

18、說明：隨機選擇記錄

select newid()

19、說明：刪除重復記錄

Delete from tablename where id not in(select max(id)from tablename group by col1,col2,...)

20、說明：列出數據庫里所有的表名

select name from sysobjects where type='U'

21、說明：列出表里的所有的

select name from syscolumns where id=object_id('TableName')

22、說明：列示type、vender、pcs字段，以type字段排列，case可以方便地實現多重選擇，類似select 中的case。

select type,sum(case vender when 'A' then pcs else 0 end),sum(case vender when 'C' then pcs else 0 end),sum(case vender when 'B' then pcs else 0 end)FROM tablename group by type

顯示結果：

type vender pcs

電腦 A 1

電腦 A 1

光盤 B 2

光盤 A 2

手機 B 3

手機 C 3

23、說明：初始化表table1

TRUNCATE TABLE table1

24、說明：選擇從10到15的記錄

select top 5 * from(select top 15 * from table order by id asc)table_別名 order by id desc

隨機選擇數據庫記錄的方法（使用Randomize函數，通過SQL語句實現）

對存儲在數據庫中的數據來說，隨機數特性能給出上面的效果，但它們可能太慢了些。你不能要求ASP“找個隨機數”然后打印出來。實際上常見的解決方案是建立如下所示的循環：

Randomize

RNumber = Int(Rnd*499)+1

While Not objRec.EOF

If objRec(“ID”)= RNumber THEN

...這里是執行腳本...end if

objRec.MoveNext

Wend

這很容易理解。首先，你取出1到500范圍之內的一個隨機數（假設500就是數據庫內記錄的總數）。然后，你遍歷每一記錄來測試ID 的值、檢查其是否匹配RNumber。滿足條件的話就執行由THEN 關鍵字開始的那一塊代碼。假如你的RNumber 等于495，那么要循環一遍數據庫花的時間可就長了。雖然500這個數字看起來大了些，但相比更為穩固的企業解決方案這還是個小型數據庫了，后者通常在一個數據庫內就包含了成千上萬條記錄。這時候不就死定了？

采用SQL，你就可以很快地找出準確的記錄并且打開一個只包含該記錄的recordset，如下所示：

Randomize

RNumber = Int(Rnd*499)+ 1

SQL = “SELECT * FROM Customers WHERE ID = ” & RNumber

set objRec = ObjConn.Execute(SQL)

Response.WriteRNumber & “ = ” & objRec(“ID”)& “ ” & objRec(“c_email”)

不必寫出RNumber 和ID，你只需要檢查匹配情況即可。只要你對以上代碼的工作滿意，你自可按需操作“隨機”記錄。Recordset沒有包含其他內容，因此你很快就能找到你需要的記錄這樣就大大降低了處理時間。

再談隨機數

現在你下定決心要榨干Random 函數的最后一滴油，那么你可能會一次取出多條隨機記錄或者想采用一定隨機范圍內的記錄。把上面的標準Random 示例擴展一下就可以用SQL應對上面兩種情況了。

為了取出幾條隨機選擇的記錄并存放在同一recordset內，你可以存儲三個隨機數，然后查詢數據庫獲得匹配這些數字的記錄：

SQL = “SELECT * FROM Customers WHERE ID = ” & RNumber & “ OR ID = ” & RNumber2 & “ OR ID = ” & RNumber3

假如你想選出10條記錄（也許是每次頁面裝載時的10條鏈接的列表），你可以用BETWEEN 或者數學等式選出第一條記錄和適當數量的遞增記錄。這一操作可以通過好幾種方式來完成，但是 SELECT 語句只顯示一種可能（這里的ID 是自動生成的號碼）：

SQL = “SELECT * FROM Customers WHERE ID BETWEEN ” & RNumber & “ AND ” & RNumber & “+ 9”

注意：以上代碼的執行目的不是檢查數據庫內是否有9條并發記錄。

隨機讀取若干條記錄，測試過

Access語法：SELECT top 10 * From 表名 ORDER BY Rnd(id)

Sql server:select top n * from 表名 order by newid()

mysqlelect * From 表名 Order By rand()Limit n

Access左連接語法(最近開發要用左連接,Access幫助什么都沒有,網上沒有Access的SQL說明,只有自己測試, 現在記下以備后查)

語法elect table1.fd1,table1,fd2,table2.fd2 From table1 left join table2 on table1.fd1,table2.fd1 where...使用SQL語句 用...代替過長的字符串顯示

語法：

SQL數據庫：select case when len(field)>10 then left(field,10)+'...' else field end as news_name,news_id from tablename

Access數據庫：SELECT iif(len(field)>2,left(field,2)+'...',field)FROM tablename;

Conn.Execute說明

Execute方法

該方法用于執行SQL語句。根據SQL語句執行后是否返回記錄集，該方法的使用格式分為以下兩種：

1．執行SQL查詢語句時，將返回查詢得到的記錄集。用法為：

Set 對象變量名=連接對象.Execute(“SQL 查詢語言”)

Execute方法調用后，會自動創建記錄集對象，并將查詢結果存儲在該記錄對象中，通過Set方法，將記錄集賦給指定的對象保存，以后對象變量就代表了該記錄集對象。

2．執行SQL的操作性語言時，沒有記錄集的返回。此時用法為：

連接對象.Execute “SQL 操作性語句” [, RecordAffected][, Option]

·RecordAffected 為可選項，此出可放置一個變量，SQL語句執行后，所生效的記錄數會自動保存到該變量中。通過訪問該變量，就可知道SQL語句隊多少條記錄進行了操作。

·Option 可選項，該參數的取值通常為adCMDText，它用于告訴ADO，應該將Execute方法之后的第一個字符解釋為命令文本。通過指定該參數，可使執行更高效。

·BeginTrans、RollbackTrans、CommitTrans方法

這三個方法是連接對象提供的用于事務處理的方法。BeginTrans用于開始一個事物；RollbackTrans用于回滾事務；CommitTrans用于提交所有的事務處理結果，即確認事務的處理。

事務處理可以將一組操作視為一個整體，只有全部語句都成功執行后，事務處理才算成功；若其中有一個語句執行失敗，則整個處理就算失敗，并恢復到處里前的狀態。

BeginTrans和 CommitTrans用于標記事務的開始和結束，在這兩個之間的語句，就是作為事務處理的語句。判斷事務處理是否成功，可通過連接對象的Error集合來實現，若Error集合的成員個數不為0，則說明有錯誤發生，事務處理失敗。Error集合中的每一個Error對象，代表一個錯誤信息。

常用SQL語句書寫技巧(轉)

關鍵詞： SQL

SQL結構化查詢字符串的改寫，是實現數據庫查詢性能提升的最現實、最有效的手段，有時甚至是唯一的手段，比如在不允許大幅度修改現有數據庫結構的情況下。

通過優化SQL語句提高查詢性能的關鍵是：

l 根據實際需求情況，建立合適的索引； l 使用一切可能的方式去利用好索引，避免全表掃描；

l 盡量減少內存及數據I/O方面的開銷

一、建立索引

（一）建立“適當”的索引，是快速查詢的基礎。

索引（index）是除表之外另一重要的、用戶定義的存儲在物理介質上的數據結構。當根據索引碼的值搜索數據時，索引提供了對數據的快速訪問。事實上，沒有索引，數據庫也能根據SELECT語句成功地檢索到結果，但隨著表變得越來越大，使用“適當”的索引的效果就越來越明顯。注意，在這句話中，我們用了“適當”這個詞，這是因為，如果使用索引時不認真考慮其實現過程，索引既可以提高也會破壞數據庫的工作性能。

索引實際上是一種特殊的目錄，SQL SERVER提供了兩種索引：

l 聚集索引（clustered index，也稱聚類索引、簇集索引）

我們把這種正文內容本身就是一種按照一定規則排列的目錄稱為“聚集索引”。

例如：

漢語字典中按拼音查某一個字，就是使用“聚集索引”，實際上，你根本用不著查目錄，直接在字典正文里找，就能很快找到需要的漢字（假設你知道發音）。

l 非聚集索引（nonclustered index，也稱非聚類索引、非簇集索引）

我們把目錄純粹是目錄，正文純粹是正文的排序方式稱為“非聚集索引”。

例如：

漢語字典中按部首查某一個字，部首目錄和正文一定要刻意的通過頁碼才能聯系到一起，其順序不是天然一致的。

聚集索引與非聚集索引的最大區別就在于：聚集索引是對原數據表進行排序，因此只要符合索引條件，就能夠直接連續的讀取數據記錄，幾乎可以達到對數據表的零掃描；而非聚集索引僅僅只是另外建了一張索引表，取數據的時候，從索引表取得結果后，還需要到指針所指的數據行讀取相應數據，因此，在性能上，聚集索引會大大優于非聚集索引。

但是在一張表中，聚集索引只允許一個，是比較寶貴的，因此要盡可能的用于那些使用頻率最高的索引上。另外，查詢時必需要用到索引的起始列，否則索引無效。另外，起始列也必需是使用頻繁的列，那樣的索引性能才會達到最優化。

（二）表：何時應使用聚集索引或非聚集索引

動作描述 使用聚集索引 使用非聚集索引

列經常被分組排序 ○ ○

返回某范圍內的數據 ○

一個或極少不同值

小數目的不同值 ○

大數目的不同值 ○

頻繁更新的列 ○

外鍵列 ○ ○

主鍵列 ○ ○

頻繁修改索引列 ○

（三）索引建立的一些注意項

1、不要把聚集索引浪費在主鍵上，除非你只按主鍵查詢

雖然SQL SERVER默認是在主鍵上建立聚集索引的，但實際應用中，這樣做比較浪費。通常，我們會在每個表中都建立一個ID列，以區分每條數據，并且這個ID列是自動增大的，步長一般為1。此時，如果我們將這個列設為主鍵，SQL SERVER會將此列默認為聚集索引。這樣做有好處，就是可以讓您的數據在數據庫中按照ID進行物理排序，但這樣做實用價值不大。

從我們前面談到的聚集索引的定義我們可以看出，使用聚集索引的最大好處就是能夠根據查詢要求，迅速縮小查詢范圍，避免全表掃描。在實際應用中，因為ID號是自動生成的，我們并不知道每條記錄的ID 號，所以我們很難在實踐中用ID號來進行查詢。這就使讓ID號這個主鍵作為聚集索引成為一種資源浪費。聚集索引相對與非聚集索引的優勢是很明顯的，而每個表中只能有一個聚集索引的規則，這使得聚集索引變得更加寶貴，應該用在其他查詢頻率高的字段上。其次，讓每個ID號都不同的字段作為聚集索引也不符合“大數目的不同值情況下不應建立聚合索引”規則；當然，這種情況只是針對用戶經常修改記錄內容，特別是索引項的時候會負作用，但對于查詢速度并沒有影響。

2、索引的建立要根據實際應用需求來進行

并非是在任何字段上簡單地建立索引就能提高查詢速度。聚集索引建立的規則大致是“既不能絕大多數都相同，又不能只有極少數相同”。舉個例子，在公文表的收發日期字段上建立聚合索引是比較合適的。在政務系統中，我們每天都會收一些文件，這些文件的發文日期將會相同，在發文日期上建立聚合索引對性能的提升應該是相當大的。在群集索引下，數據物理上按順序存于數據頁上，重復值也排列在一起，因而在范圍查找時，可以先找到這個范圍的起末點，且只在這個范圍內掃描數據頁，避免了大范圍掃描，提高了查詢速度。

另一個相反的例子：比如在雇員表的“性別”列上只有“男”與“女”兩個不同值，因此就完全沒必要建立索引。

3、在聚集索引中加入所有需要提高查詢速度的字段，形成復合索引

根據一些實驗的結果，我們可以得出一些可供參考的結論：

ü 僅用復合聚集索引的起始列作為查詢條件和同時用到復合聚集索引的全部列的查詢，速度是幾乎一樣的，甚至比后者還要快（在查詢結果集數目一樣的情況下）；

ü 僅用復合聚集索引的非起始列作為查詢條件的話，這個索引是不起任何作用的。

ü 復合聚集索引的所有列都用上，而且因為查詢條件嚴格，查詢結果少的話，會形成“索引覆蓋”，性能可以達到最優。

ü 最重要的一點：無論是否經常使用復合聚合索引的其他列，其起始列一定要是使用最頻繁的列。

4.根據實踐得出的一些其他經驗，特定情況下有效

ü 用聚合索引比用不是聚合索引的主鍵速度快；

ü 用聚合索引比用一般的主鍵作order by速度快，特別是在小數據量情況；

ü 使用聚合索引內的時間段，搜索時間會按數據占整個數據表的百分比成比例減少，而無論聚合索引使用了多少個；

ü 日期列不會因為有分秒的輸入而減慢查詢速度；

ü 由于改變一個表的內容，將會引起索引的變化。頻繁的insert,update,delete語句將導致系統花費較大的代價進行索引更新，引起整體性能的下降。一般來講，在對查詢性能的要求高于對數據維護性能要求時，應該盡量使用索引，否則，就要慎重考慮一下付出的代價。在某些極端情況下，可先刪除索引，再對數據庫表更新大量數據，最后再重建索引，新建立的索引總是比較好用。

二、編寫優化的SQL語句，充分利用索引

下面就某些SQL語句的where子句編寫中需要注意的問題作詳細介紹。在這些where子句中，即使某些列存在索引，但是由于編寫了劣質的SQL，系統在運行該SQL語句時也不能使用該索引，而同樣使用全表掃描，這就造成了響應速度的極大降低。

SQL語句在提交給數據庫進行操作前，都會經過查詢分析階段，SQLSERVER內置的查詢優化器會分析查詢條件的的每個部分，并判斷這些條件是否符合掃描參數（SARG）的標準。只有當一個查詢條件符合SARG的標準，才可以通過預先設置的索引，提升查詢性能。

SARG的定義：用于限制搜索操作的一種規范，通常是指一個特定的匹配，一個確定范圍內的匹配或者兩個以上條件的AND連接。一般形式如下：

列名 操作符 <常數 或 變量> 或

<常數 或 變量> 操作符 列名

列名可以出現在操作符的一邊，而常數或變量出現在操作符的另一邊。如：

Name=?張三?

價格>5000

5000<價格

Name=?張三? and 價格>5000

如果一個表達式不能滿足SARG的形式，那它就無法限制搜索的范圍了，也就是說SQL SERVER必須對每一行都判斷它是否滿足WHERE子句中的所有條件，既進行全表掃描。所以，一個索引對于不滿足SARG形式的表達式來說是無用的, 如：當查詢條件為“價格*2 >5000”時，就無法利用建立在價格字段上的索引。

SQLSERVER內置了查詢優化器，能將一些條件自動轉換為符合SARG標準，如：將“價格*2 >5000” 轉換為“價格 >2500/2 ”，以達到可以使用索引的目的，但這種轉化不是100%可靠的，有時會有語義上的損失，有時轉化不了。如果對“查詢優化器”的工作原理不是特別了解，寫出的SQL語句可能不會按照您的本意進行查詢。所以不能完全依賴查詢優化器的優化，建議大家還是利用自己的優化知識，盡可能顯式的書寫出符合SARG標準的 SQL語句，自行確定查詢條件的構建方式，這樣一方面有利于查詢分析器分析最佳索引匹配順序，另一方面也有利于今后重讀代碼。

介紹完SARG后，我們再結合一些實際運用中的例子來做進一步的講解：

1、Like語句是否屬于SARG取決于使用%通配符的樣式

如：name like ?張%?，這就屬于SARG

而：name like ?%張? ,就不屬于SARG

通配符%在字符串首字符的使用會導致索引無法使用，雖然實際應用中很難避免這樣用，但還是應該對這種現象有所了解，至少知道此種用法性能是很低下的。

2、“非”操作符不滿足SARG形式，使得索引無法使用

不滿足SARG形式的語句最典型的情況就是包括非操作符的語句，如：NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE等。

下面是一個NOT子句的例子：

...where not(status ='valid')

not運算符也隱式的包含在另外一些邏輯運算符中，比如<>運算符。見下例：

...where status <>'invalid';

再看下面這個例子：

select * from employee where salary<>3000;

對這個查詢，可以改寫為不使用not：

select * from employee where salary<3000 or salary>3000;

雖然這兩種查詢的結果一樣，但是第二種查詢方案會比第一種查詢方案更快些。第二種查詢允許對salary列使用索引，而第一種查詢則不能使用索引。

3、函數運算不滿足SARG形式，使得索引無法使用

例：下列SQL條件語句中的列都建有恰當的索引，但執行速度卻非常慢：

select * from record where substring(card_no,1,4)=′5378′(13秒)

select * from record where amount/30< 1000（11秒）

select * from record where convert(char(10),date,112)=′19991201′（10秒）

分析：

where子句中對列的任何操作結果都是在SQL運行時逐列計算得到的，因此它不得不進行全表掃描，而沒有使用該列上面的索引；如果這些結果在查詢編譯時就能得到，那么就可以被SQL優化器優化，使用索引，避免表搜索，因此將SQL重寫成下面這樣：

select * from record where card_no like ′5378%′（< 1秒）

select * from record where amount < 1000*30（< 1秒）

select * from record where date= ′1999/12/01′（< 1秒）

你會發現SQL明顯快很多

4、盡量不要對建立了索引的字段，作任何的直接處理

select * from employs where first_name + last_name ='beill cliton';

無法使用索引

改為：

select * from employee where

first_name = substr('beill cliton',1,instr('beill cliton',' ')-1)and

last_name = substr('beill cliton',instr('beill cliton',' ')+1)

則可以使用索引

5、不同類型的索引效能是不一樣的，應盡可能先使用效能高的

比如：數字類型的索引查找效率高于字符串類型，定長字符串char，nchar的索引效率高于變長字符串varchar,nvarchar的索引。

應該將

where username='張三' and age>20

改進為

where age>20 and username='張三'

注意：

此處，SQL的查詢分析優化功能可以做到自動重排條件順序，但還是建議預先手工排列好。

6、盡量不要使用 is null 與 is not null作為查詢條件

任何包含null值的列都將不會被包含在索引中，如果某列數據中存在空值，那么對該列建立索引的性能提升是值得懷疑的，尤其是將null作為查詢條件的一部分時。建議一方面避免使用is null和is not null, 另一方面不要讓數據庫字段中存在null, 即使沒有內容，也應利用缺省值，或者手動的填入一個值，如:?? 空字符串。

7、某些情況下IN 的作用與OR 相當，且都不能充分利用索引

例：表stuff有200000行，id_no上有非群集索引，請看下面這個SQL：

select count(*)from stuff where id_no in(′0′,′1′)（23秒）

where條件中的′in′在邏輯上相當于′ or′，所以語法分析器會將in(′0′,′1′)轉化為id_no =′0′ or id_no=′1′來執行。我們期望它會根據每個or子句分別查找，再將結果相加，這樣可以利用id_no上的索引；但實際上,它卻采用了“OR策略”，即先取出滿足每個or子句的行，存入臨時數據庫的工作表中，再建立唯一索引以去掉重復行，最后從這個臨時表中計算結果。因此，實際過程沒有利用id_no 上索引，并且完成時間還要受tempdb數據庫性能的影響。

實踐證明，表的行數越多，工作表的性能就越差，當stuff有620000行時，執行時間會非常長！如果確定不同的條件不會產生大量重復值，還不如將or子句分開：

select count(*)from stuff where id_no=′0′

select count(*)from stuff where id_no=′1′

得到兩個結果，再用union作一次加法合算。因為每句都使用了索引，執行時間會比較短，select count(*)from stuff where id_no=′0′

union

select count(*)from stuff where id_no=′1′

從實踐效果來看，使用union在通常情況下比用or的效率要高的多，而exist關鍵字和in關鍵字在用法上類似，性能上也類似，都會產生全表掃描，效率比較低下，根據未經驗證的說法，exist可能比in要快些。

8、使用變通的方法提高查詢效率

like關鍵字支持通配符匹配，但這種匹配特別耗時。例如：select * from customer where zipcode like “21_ _ _”，即使在zipcode字段上已建立了索引，在這種情況下也可能還是采用全表掃描方式。如果把語句改為：select * from customer where zipcode >“21000”，在執行查詢時就會利用索引，大大提高速度。但這種變通是有限制的，不應引起業務意義上的損失，對于郵政編碼而言，zipcode like “21_ _ _” 和 zipcode >“21000” 意義是完全一致的。

9、組合索引的高效使用

假設已在date，place，amount三個字段上建立了組合索引

select count(*)from record

where date > ′19991201′ and date < ′19991214′ and amount > 2000

(< 1秒)

select date,sum(amount)from record group by date

（11秒）

select count(*)from record

where date > ′19990901′ and place in(′BJ′,′SH′)

（< 1秒）

這是一個設置較合理的組合索引。它將date作為前導列，使每個SQL都可以利用索引，并且在第一和第三個SQL中形成了索引覆蓋，因而性能達到了最優。如果索引不便于更改，修正SQL中的條件順序以配合索引順序也是可行的。

10、order by按聚集索引列排序效率最高

排序是較耗時的操作，應盡量簡化或避免對大型表進行排序，如縮小排序的列的范圍，只在有索引的列上排序等等。

我們來看：（gid是主鍵，fariqi是聚合索引列）

select top 10000 gid,fariqi,reader,title from tgongwen

用時：196 毫秒。掃描計數 1，邏輯讀 289 次，物理讀 1 次，預讀 1527 次。

select top 10000 gid,fariqi,reader,title from tgongwen order by gid asc

用時：4720毫秒。掃描計數 1，邏輯讀 41956 次，物理讀 0 次，預讀 1287 次。

select top 10000 gid,fariqi,reader,title from tgongwen order by gid desc

用時：4736毫秒。掃描計數 1，邏輯讀 55350 次，物理讀 10 次，預讀 775 次。

select top 10000 gid,fariqi,reader,title from tgongwen order by fariqi asc

用時：173毫秒。掃描計數 1，邏輯讀 290 次，物理讀 0 次，預讀 0 次。

select top 10000 gid,fariqi,reader,title from tgongwen order by fariqi desc

用時：156毫秒。掃描計數 1，邏輯讀 289 次，物理讀 0 次，預讀 0 次。

從以上我們可以看出，不排序的速度以及邏輯讀次數都是和“order by 聚集索引列” 的速度是相當的，但這些都比“order by 非聚集索引列”的查詢速度是快得多的。

同時，按照某個字段進行排序的時候，無論是正序還是倒序，速度是基本相當的。

三、關于節省數據查詢系統開銷方面的措施

1、使用TOP盡量減少取出的數據量

TOP是SQL SERVER中用來提取前幾條或前某個百分比數據的關鍵詞。

select top 20 gid,fariqi,reader,title from tgongwen order by gid desc select top 60 percent gid,fariqi,reader,title from tgongwen order by gid desc

在實際的應用中，應該經常利用top 剔除掉不必要的數據，只保留必須的數據集合。這樣不僅可以減少數據庫邏輯讀的次數，還能避免不必要的內存浪費，對系統性能的提升都是有好處的。

2、字段提取要按照“需多少、提多少”的原則，避免“select *”

這個舉個例子：

select top 10000 gid,fariqi,reader,title from tgongwen order by gid desc

用時：4673毫秒

select top 10000 gid,fariqi,title from tgongwen order by gid desc

用時：1376毫秒

select top 10000 gid,fariqi from tgongwen order by gid desc

用時：80毫秒

由此看來，字段大小越大，數目越多，select所耗費的資源就越多，比如取int類型的字段就會比取char的快很多。我們每少提取一個字段，數據的提取速度就會有相應的提升。提升的幅度根據舍棄的字段的大小來判斷。

3、count(*)與 count(字段)方法比較

我們來看一些實驗例子（gid為Tgongwen的主鍵）：

select count(*)from Tgongwen

用時：1500毫秒

select count(gid)from Tgongwen

用時：1483毫秒

select count(fariqi)from Tgongwen

用時：3140毫秒

select count(title)from Tgongwen 用時：52050毫秒

從以上可以看出，用count(*)和用 count(主鍵)的速度是相當的，而count(*)卻比其他任何除主鍵以外的字段匯總速度要快，而且字段越長，匯總速度就越慢。如果用 count(*)，SQL SERVER會自動查找最小字段來匯總。當然，如果您直接寫count(主鍵)將會來的更直接些。

4、有嵌套查詢時，盡可能在內層過濾掉數據

如果一個列同時在主查詢和where子句中出現，很可能當主查詢中的列值改變之后，子查詢必須重新查詢一次。而且查詢嵌套層次越多，效率越低，因此應當盡量避免子查詢。如果子查詢不可避免，那么要在子查詢中過濾掉盡可能多的行。

5、多表關聯查詢時，需注意表順序，并盡可能早的過濾掉數據

在使用Join進行多表關聯查詢時候，應該使用系統開銷最小的方案。連接條件要充份考慮帶有索引的表、行數多的表，并注意優化表順序；說的簡單一點，就是盡可能早的將之后要做關聯的數據量降下來。

一般情況下，sqlserver 會對表的連接作出自動優化。例如：

select name,no from A

join B on A.id=B.id

join C on C.id=A.id

where name='wang'

盡管A表在From中先列出，然后才是B,最后才是C。但sql server可能會首先使用c表。它的選擇原則是相對于該查詢限制為單行或少數幾行，就可以減少在其他表中查找的總數據量。絕大多數情況下，sql server 會作出最優的選擇，但如果你發覺某個復雜的聯結查詢速度比預計的要慢，就可以使用SET FORCEPLAN語句強制sql server按照表出現順序使用表。如上例加上：SET FORCEPLAN ON…….SET FORCEPLAN OFF 表的執行順序將會按照你所寫的順序執行。在查詢分析器中查看2種執行效率，從而選擇表的連接順序。SET FORCEPLAN的缺點是只能在存儲過程中使用。

小結：

? 聚集索引比較寶貴，應該用在查詢頻率最高的地方；

? 在數據為“既不是絕大多數相同，也不是極少數相同”狀態時，最能發揮聚集索引的潛力；

? 復合索引的設置和使用要注意保持順序一致；

? 條件子句的表達式最好符合SARG規范，是可利用索引的；

? 任何對列的操作都導致全表掃描，如數據庫函數、計算表達式等，查詢時應盡可能將操作移至等號的某一邊；

? 要注意含有null值時，是不能充分利用索引的；

? exist, in、or等子句常會使索引失效；

如果不產生大量重復值，可以考慮把子句拆開，再用union拼合；

? 排序時應充分利用帶索引的字段；

? 盡可能早，快的過濾掉無用的數據，只將必須的數據帶到后續的操作中去

從前面講敘的內容可以看出，SQL語句優化的實質就是在結果正確的前提下，用分析優化器可以識別的SARG規范語句，充份利用索引，減少數據的I/O次數，盡量避免全表掃描的發生。

以上內容有些是指導性的理論原則，有些是實際摸索的經驗，大家在使用時應靈活處理，根據實際情況，選擇合適的方法。本文中列舉的實驗數據僅作比對用，不具備普遍意義。大家在實際項目中，應充分利用性能監測和分析工具（如SQLSERVER帶的相關工具）來檢驗自己的優化效果。

此外，還有很重要的一點要提醒大家，同樣復雜的數據操作，在SQLSERVER數據庫級別完成的代價要遠遠小于在應用端用程序代碼完成的代價，所以建議大家全面，深入的學習SQL語法中重要關鍵字的應用，如：Group By，Having等，盡量把數據操作任務放在數據庫系統中完成。數據庫應用系統的性能優化是一個復雜的過程，上述這些只是在SQL語句層次的一種體現，深入研究還會涉及數據庫層的資源配置、網絡層的流量控制以及操作系統層的總體設計等等，這些將在以后的文章中詳細論述

如何獲得sql查詢侯滿足條件的記錄數

select kch into :ls_kch from cj_cjb where kch = :s_kch and kscj < 60 using ltr;

請問如何知道符合條件kch的紀錄數。SELECT KCH, COUNT(*)INTO :ls_kch, :ll_count cj_cjb WHERE kch = :s_kch AND kscj < 60 UAING ltr;

ll_count

SRY少了個FROM

執行完后看：

sqlca.sqlnrows

呵呵select只能返回一條記錄啊，要是有多條記錄符合條件pb是會報錯的。

are2000(烏鴉與土狗)說的對呀

只是想知道符合條件的紀錄數，直接這樣寫：

SELECT COUNT(*)

INTO :ll_count

from cj_cjb

WHERE kch = :s_kch

AND kscj < 60

UAING sqlca;

樓上說的對啊

呵呵

多行的話要用游標的

用游標取的時候,在for 循環中加個變量就可以知道條數,數據也取出來了~~

如何查詢SQLSERVER數據庫DB1中所有表的記錄數？

use DB1

if object_id('tempdb..##')is not null drop table ##

select cast(null as sysname)as 表名稱, 1 as 記錄數 into ## where 1 = 0

declare @TableName sysname

declare testcur cursor for select [name] from sysobjects where xtype ='U' order by [name]

open testcur

fetch next from testcur into @TableName

while @@fetch_status = 0

begin

exec('insert into ## select ''' + @TableName + ''',(select count(1)from ' + @TableName + ')')

fetch next from testcur into @TableName end

close testcur

deallocate testcur

select * from ##

drop table ##

Pubwin EP數據備份:

SQL server2000版本重裝前，需要備份哪些數據

SQL server2000版本重裝前，需要備份數據庫文件，文件路徑為： HintsoftPubwinServerdatabase 下的“local_Data.MDF”和“local_log.LDF”

除了備份數據庫文件以外還有就是需要備份數據庫的備份文件 其路徑為 ： HintsoftPubwinServerappServserverwebappsNetCafebackuplongtermdata 還有一個方面需要注意的是 網吧的會員是否存在頭像與身份證證件照 如有的話 請備份

HintsoftPubwinServerappServserverwebappsNetCafeheadphotos HintsoftPubwinServerappServserverwebappsNetCafephotos ； 備份好后，重新安裝將備份文件放置相應的目錄即可。如何防止遠程修改數據庫

一、屏蔽1433端口（以win2000為例）： 設置安全策略：

“控制面板”—〉“管理工具”—〉“本地安全策略”

選擇IP安全策略—〉創建IP安全策略—〉建立名稱—〉“激活默認響應規則”下一步—〉初始身份驗證方法選擇“win2000默認（V5）”—〉彈出的警告界面直接確認—〉完成建立安全策略。

選擇你新建的策略—〉屬性—〉添加—〉選擇“此規則不指定隧道” —〉網絡類型選擇“所有網絡連接”—〉身份驗證方法“win2000默認（V5）” —〉彈出的警告界面直接確認—〉“所有IP通訊”—〉篩選器選擇“要求安全設置”—〉繼續下一步完成選中“所有 IP 通訊”—〉點“編輯”按鈕，打開“IP篩選器列表”—〉繼續點“編輯”按鈕，打開“篩選器 屬性” —〉在“尋址”中，源地址選擇“任何IP地址”，目的地址選擇“我的IP地址”，同時選中“鏡像”—〉在“協議”中，協議選擇“TCP”，設置協議端口為 “從任意端口”到“到此端口：1433” —〉確定，為了安全起見，最好再新建一個IP篩選器屏蔽1434端口。

完成上面配置后，在剛配置的策略點擊右鍵，選擇指派，完成后重新啟動機器。如何驗證數據庫的1433已經不能連接？

1）局域網內找一個機器（非本機）安裝企業管理，添加注冊剛剛配制過安全策略的服務器，應該是那個等待注冊的畫面，狀態中顯示：“正在驗證注冊信息”或拒絕連接或服務未開啟的提示。

2）局域網內找一個機器（非本機），在dos控制臺下，輸入telnet EP服務器IP 1433 如果安全策略應用成功的話，應該不能夠連接，會出現如下的話：正在連接到xxxxxxx...無法打開到主機的連接 在端口 1433 : 連接失敗。如果應用安全策略失敗，則能夠連接成功。

二、關閉不安全的服務

第一步只是屏蔽了其它機器連接數據庫的端口，但是操作系統本身還存在一些漏洞，這些漏洞同樣會導致數據庫不安全。可以運行services.msc進入本地服務管理，禁用WMI和Server服務（屏蔽WMI和 IPC共享漏洞），但是有些網吧需要用到Server服務的部分功能，所以對于Server服務，可以使用以下兩種較為靈活的方法來屏蔽： 1）批處理自啟動法：

打開記事本，輸入以下內容（記得每行最后要回車）： net share ipc$ /delete net share admin$ /delete net share c$ /delete net share d$ /delete net share e$ /delete ……（你有幾個硬盤分區就寫幾行這樣的命令）

保存為NotShare.bat（注意后綴！），然后把這個批處理文件拖到“程序”→“啟動”項，這樣每次開機就會運行它，也就是通過net命令關閉共享。

如果哪一天你需要開啟某個或某些共享，只要重新編輯這個批處理文件即可（把相應的那個命令行刪掉）。2）注冊表改鍵值法 “ 開始”→“運行”輸入“regedit”確定后，打開注冊表編輯器，找到“HKEY_LOCAL_MACHINESYSTEM CurrentControlSetServiceslanmanserverparameters”項，雙擊右側窗口中的 “AutoShareServer”項將鍵值由1改為0，這樣就能關閉硬盤各分區的共享。如果沒有AutoShareServer項，可自己新建一個再改鍵值。然后還是在這一窗口下再找到“AutoShareWks”項，也把鍵值由1改為0，關閉admin$共享。最后到 “HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlLsa”項處找到 “restrictanonymous”，將鍵值設為1，關閉IPC$共享。

PUBWIN后臺網頁應該映射那個端口? 8443，443

sql server多表關聯update

UPDATE Tab1 SET a.Name = b.Name FROM Tab1 a,Tab2 b WHERE a.ID = b.ID

批量insert數據

insert into student(sno,sname,ssex,sage,sdept)select '95001','李勇','男','20','cs' union select '95002','劉晨','女','19','is' union select '95003','王敏','女','18','ma' union select '95004','張立','男','19','is'

第五篇：精妙SQL語句收集

SQL編程經典-精妙SQL語句收集

一、基礎

1、說明：創建數據庫

CREATE DATABASE database-name

2、說明：刪除數據庫

drop database dbname

3、說明：備份sql server---創建 備份數據的 device USE master

EXEC sp_addumpdevice ’disk’, ’testBack’, ’c:mssql7backupMyNwind_1.dat’

---開始 備份

BACKUP DATABASE pubs TO testBack

4、說明：創建新表

create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)根據已有的表創建新表：

A：create table tab_new like tab_old(使用舊表創建新表)

B：create table tab_new as select col1,col2… from tab_old definition only

5、說明：刪除新表

drop table tabname

6、說明：增加一個列

Alter table tabname add column col type 注：列增加后將不能刪除。DB2中列加上后數據類型也不能改變，唯一能改變的是增加varchar類型的長度。

7、說明：添加主鍵： Alter table tabname add primary key(col)說明：刪除主鍵： Alter table tabname drop primary key(col)

8、說明：創建索引：create [unique] index idxname on tabname(col….)

刪除索引：drop index idxname

注：索引是不可更改的，想更改必須刪除重新建。

9、說明：創建視圖：create view viewname as select statement 刪除視圖：drop view viewname

10、說明：幾個簡單的基本的sql語句

選擇：select * from table1 where 范圍

插入：insert into table1(field1,field2)values(value1,value2)刪除：delete from table1 where 范圍

更新：update table1 set field1=value1 where 范圍

查找：select * from table1 where field1 like ’%value1%’---like的語法很精妙，查資料!排序：select * from table1 order by field1,field2 [desc] 總數：select count as totalcount from table1 求和：select sum(field1)as sumvalue from table1 平均：select avg(field1)as avgvalue from table1 最大：select max(field1)as maxvalue from table1 最小：select min(field1)as minvalue from table1

11、說明：幾個高級查詢運算詞

A： UNION 運算符

UNION 運算符通過組合其他兩個結果表（例如 TABLE1 和 TABLE2）并消去表中任何重復行而派生出一個結果表。當 ALL 隨 UNION 一起使用時（即 UNION ALL），不消除重復行。兩種情況下，派生表的每一行不是來自 TABLE1 就是來 自 TABLE2。

B： EXCEPT 運算符

EXCEPT 運算符通過包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重復行而派生出一個結果表。當 ALL 隨 EXCEPT 一起使用時(EXCEPT ALL)，不消除重復行。

C： INTERSECT 運算符

INTERSECT 運算符通過只包括 TABLE1 和 TABLE2 中都有的行并消除所有重復行而派生出一個結果表。當 ALL 隨 INTERSECT 一起使用時(INTERSECT ALL)，不消除重復行。

注：使用運算詞的幾個查詢結果行必須是一致的。

12、說明：使用外連接 A、left outer join：

左外連接（左連接）：結果集幾包括連接表的匹配行，也包括左連接表的所有行。

SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c

B：right outer join:

右外連接(右連接)：結果集既包括連接表的匹配連接行，也包括右連接表的所有行。

C：full outer join：

全外連接：不僅包括符號連接表的匹配行，還包括兩個連接表中的所有記錄。

二、提升

1、說明：復制表(只復制結構,源表名：a 新表名：b)(Access可用)法一：select * into b from a where 1<>1 法二：select top 0 * into b from a

2、說明：拷貝表(拷貝數據,源表名：a 目標表名：b)(Access可用)insert into b(a, b, c)select d,e,f from b;

3、說明：跨數據庫之間表的拷貝(具體數據使用絕對路徑)(Access可用)insert into b(a, b, c)select d,e,f from b in ‘具體數據庫’ where 條件

例子：..from b in ’“&Server.MapPath(”.“)&”data.mdb“ &”’ where..4、說明：子查詢(表名1：a 表名2：b)

select a,b,c from a where a IN(select d from b)或者: select a,b,c from a where a IN(1,2,3)

5、說明：顯示文章、提交人和最后回復時間

select a.title,a.username,b.adddate from table a,(select max(adddate)adddate from table where table.title=a.title)b

6、說明：外連接查詢(表名1：a 表名2：b)

select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c

7、說明：在線視圖查詢(表名1：a)

select * from(SELECT a,b,c FROM a)T where t.a > 1;

8、說明：between的用法,between限制查詢數據范圍時包括了邊界值,not between不包括

select * from table1 where time between time1 and time2

select a,b,c, from table1 where a not between 數值1 and 數值2

9、說明：in 的使用方法

select * from table1 where a [not] in(‘值1’,’值2’,’值4’,’值6’)

10、說明：兩張關聯表，刪除主表中已經在副表中沒有的信息

delete from table1 where not exists(select * from table2 where table1.field1=table2.field1)

11、說明：四表聯查問題：

select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where.....12、說明：日程安排提前五分鐘提醒

SQL: select * from 日程安排 where datediff(’minute’,f開始時間,getdate())>5

13、說明：一條sql 語句搞定數據庫分頁

select top 10 b.* from(select top 20 主鍵字段,排序字段 from 表名 order by 排序字段 desc)a,表名 b where b.主鍵字段 = a.主鍵字段 order by a.排序字段

14、說明：前10條記錄

select top 10 * form table1 where 范圍

15、說明：選擇在每一組b值相同的數據中對應的a最大的記錄的所有信息(類似這樣的用法可以用于**每月排行榜,每月熱銷產品分析,按科目成績排名,等等.)

select a,b,c from tablename ta where a=(select max(a)from tablename tb where tb.b=ta.b)

16、說明：包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重復行而派生出一個結果表

(select a from tableA)except(select a from tableB)except(select a from tableC)

17、說明：隨機取出10條數據

select top 10 * from tablename order by newid()

18、說明：隨機選擇記錄

select newid()

19、說明：刪除重復記錄

Delete from tablename where id not in(select max(id)from tablename group by col1,col2,...)

20、說明：列出數據庫里所有的表名 select name from sysobjects where type=’U’

21、說明：列出表里的所有的

select name from syscolumns where id=object_id(’TableName’)

22、說明：列示type、vender、pcs字段，以type字段排列，case可以方便地實現多重選擇，類似select 中的case。

select type,sum(case vender when ’A’ then pcs else 0 end),sum(case vender when ’C’ then pcs else 0 end),sum(case vender when ’B’ then pcs else 0 end)FROM tablename group by type 顯示結果：

type vender pcs 電腦 A 1 電腦 A 1 光盤 B 2 光盤 A 2 手機 B 3 手機 C 3

23、說明：初始化表table1

TRUNCATE TABLE table1

24、說明：選擇從10到15的記錄

select top 5 * from(select top 15 * from table order by id asc)table_別名 order by id desc

三、技巧1、1=1，1=2的使用，在SQL語句組合時用的較多

“where 1=1” 是表示選擇全部 “where 1=2”全部不選，如：

if @strWhere!=’’

begin

set @strSQL = ’select count(*)as Total from *’ + @tblName + ’+ where ’ + @strWhere end else begin

set @strSQL = ’select count(*)as Total from *’ + @tblName + ’+’ end

我們可以直接寫成

set @strSQL = ’select count(*)as Total from *’ + @tblName + ’+ where 1=1 安定 ’+ @strWhere

2、收縮數據庫--重建索引

DBCC REINDEX

DBCC INDEXDEFRAG--收縮數據和日志

DBCC SHRINKDB DBCC SHRINKFILE

3、壓縮數據庫

dbcc shrinkdatabase(dbname)

4、轉移數據庫給新用戶以已存在用戶權限

exec sp_change_users_login ’update_one’,’newname’,’oldname’

go

5、檢查備份集

RESTORE VERIFYONLY from disk=’E:dvbbs.bak’

6、修復數據庫

ALTER DATABASE [dvbbs] SET SINGLE_USER GO

DBCC CHECKDB(’dvbbs’,repair_allow_data_loss)WITH TABLOCK GO

ALTER DATABASE [dvbbs] SET MULTI_USER GO

7、日志清除 SET NOCOUNT ON

DECLARE @LogicalFileName sysname, @MaxMinutes INT, @NewSize INT

USE tablename--要操作的數據庫名

SELECT @LogicalFileName = ’tablename_log’,--日志文件名

@MaxMinutes = 10,--Limit on time allowed to wrap log.@NewSize = 1--你想設定的日志文件的大小(M)

--Setup / initialize

DECLARE @OriginalSize int SELECT @OriginalSize = size FROM sysfiles

WHERE name = @LogicalFileName

SELECT ’Original Size of ’ + db_name()+ ’ LOG is ’ +

CONVERT(VARCHAR(30),@OriginalSize)+ ’ 8K pages or ’ + CONVERT(VARCHAR(30),(@OriginalSize*8/1024))+ ’MB’

FROM sysfiles

WHERE name = @LogicalFileName CREATE TABLE DummyTrans

(DummyColumn char(8000)not null)

DECLARE @Counter INT, @StartTime DATETIME, @TruncLog VARCHAR(255)SELECT @StartTime = GETDATE()，@TruncLog = ’BACKUP LOG ’ + db_name()+ ’ WITH TRUNCATE_ONLY’

DBCC SHRINKFILE(@LogicalFileName, @NewSize)EXEC(@TruncLog)

--Wrap the log if necessary.WHILE @MaxMinutes > DATEDIFF(mi, @StartTime, GETDATE())--time has not expired AND @OriginalSize =(SELECT size FROM sysfiles WHERE name = @LogicalFileName)AND(@OriginalSize * 8 /1024)> @NewSize BEGIN--Outer loop.SELECT @Counter = 0

WHILE((@Counter < @OriginalSize / 16)AND(@Counter < 50000))BEGIN--update

INSERT DummyTrans VALUES(’Fill Log’)DELETE DummyTrans

SELECT @Counter = @Counter + 1 END

EXEC(@TruncLog)END

SELECT ’Final Size of ’ + db_name()+ ’ LOG is ’ + CONVERT(VARCHAR(30),size)+ ’ 8K pages or ’ + CONVERT(VARCHAR(30),(size*8/1024))+ ’MB’ FROM sysfiles

WHERE name = @LogicalFileName DROP TABLE DummyTrans SET NOCOUNT OFF

8、說明：更改某個表

exec sp_changeobjectowner ’tablename’,’dbo’

9、存儲更改全部表

CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch @OldOwner as NVARCHAR(128), @NewOwner as NVARCHAR(128)AS

DECLARE @Name as NVARCHAR(128)DECLARE @Owner as NVARCHAR(128)

DECLARE @OwnerName as NVARCHAR(128)

DECLARE curObject CURSOR FOR select ’Name’ = name，’Owner’ = user_name(uid)from sysobjects

where user_name(uid)=@OldOwner order by name

OPEN curObject

FETCH NEXT FROM curObject INTO @Name, @Owner WHILE(@@FETCH_STATUS=0)BEGIN

if @Owner=@OldOwner begin

set @OwnerName = @OldOwner + ’.’ + rtrim(@Name)exec sp_changeobjectowner @OwnerName, @NewOwner end

--select @name,@NewOwner,@OldOwner

FETCH NEXT FROM curObject INTO @Name, @Owner END

close curObject

deallocate curObject GO

10、SQL SERVER中直接循環寫入數據

declare @i int set @i=1 while @i<30 begin

insert into test(userid)values(@i)set @i=@i+1 end