sqlserver數(shù)據(jù)庫操作大全——常用語句/技巧集錦/經(jīng)典語句

MS?SQL里沒有括號時，運算進行的次序?qū)⑹窍瘸撕蟪倌：蠹訙p

減號(-)也有兩種用途：1.作為負(fù)號使用?2.從某一列中減去另一列

and???or???not??
如果一個where子句中同時出現(xiàn)這三個操作符
最先評估not???然后是and??然后是or?


coalesce?哪個不為空用哪個?
coalesce(i.ProductID,d.ProductID)

@@rowcount
返回上一條語句影響的行數(shù)

SQL判斷某列中是否包含中文字符或者英文字符?
??select?*?from?表名?where?某列?like?'%[吖-座]%'??
??select?*?from?表名?where?某列?like?'%[a-z]%'

--數(shù)據(jù)操作，中英文對照?
select?--從數(shù)據(jù)庫表中檢索數(shù)據(jù)行和列??
insert?--向數(shù)據(jù)庫表添加新數(shù)據(jù)行??
delete?--從數(shù)據(jù)庫表中刪除數(shù)據(jù)行??
update?--更新數(shù)據(jù)庫表中的數(shù)據(jù)??
--數(shù)據(jù)定義??
create?table?--創(chuàng)建一個數(shù)據(jù)庫表??
drop?table?--從數(shù)據(jù)庫中刪除表??
alter?table?--修改數(shù)據(jù)庫表結(jié)構(gòu)??
create?view?--創(chuàng)建一個視圖??
drop?view?--從數(shù)據(jù)庫中刪除視圖??
create?index?--為數(shù)據(jù)庫表創(chuàng)建一個索引??
drop?index?--從數(shù)據(jù)庫中刪除索引??
create?proceduer?--創(chuàng)建一個存儲過程??
drop?proceduer?--從數(shù)據(jù)庫中刪除存儲過程??
create?trigger?--創(chuàng)建一個觸發(fā)器??
drop?trigger?--從數(shù)據(jù)庫中刪除觸發(fā)器??
create?schema?--向數(shù)據(jù)庫添加一個新模式??
drop?schema?--從數(shù)據(jù)庫中刪除一個模式??
create?domain?--創(chuàng)建一個數(shù)據(jù)值域??
alter?domain?--改變域定義??
drop?domain?--從數(shù)據(jù)庫中刪除一個域??
--數(shù)據(jù)控制??
grant?--授予用戶訪問權(quán)限??
deny?--拒絕用戶訪問??
revoke?--解除用戶訪問權(quán)限??
--事務(wù)控制??
commit?--結(jié)束當(dāng)前事務(wù)??
rollback?--中止當(dāng)前事務(wù)??
set?transaction?--定義當(dāng)前事務(wù)數(shù)據(jù)訪問特征??
--程序化SQL??
declare?--為查詢設(shè)定游標(biāo)??
explan?--為查詢描述數(shù)據(jù)訪問計劃??
open?--檢索查詢結(jié)果打開一個游標(biāo)??
fetch?--檢索一行查詢結(jié)果??
close?--關(guān)閉游標(biāo)??
prepare?--為動態(tài)執(zhí)行準(zhǔn)備SQL?語句??
execute?--動態(tài)地執(zhí)行SQL?語句??
describe?--描述準(zhǔn)備好的查詢?


------------------SQL中插入數(shù)據(jù)的技巧?-----------------

插入少量數(shù)據(jù)時可以用：
insert?into?表名(列名1，列名2...)?values?(值1，值2...)

插入大量數(shù)據(jù)時可以用：
????insert?into?要復(fù)制的表名?select?*?from?源表名
或：insert?into?要復(fù)制的表名(列名1，列名2...)?select?(列名1，列名2...)?from?源表名

insert?select?語句要求你遵循如下規(guī)則：
??SELECT?語句不能從被插入數(shù)據(jù)的表中選擇行
??INSERT?INTO?中的列數(shù)必須與SELECT?語句返回的列數(shù)相等
??INSERT?INTO?中的數(shù)據(jù)類型要與SELECT?語句返回的數(shù)據(jù)類型相同

insert?select?語句的另外一個用處對表進行備份：
??select?*?into?臨時表名?from?源表名



----------SQL中truncate?table和delete和drop的區(qū)別?----------

truncate?table在功能上與不帶?Where?子句的?Delete?語句相同,二者均刪除表中的全部行
但truncate?table比?Delete?速度快，且使用的系統(tǒng)和事務(wù)日志資源少。
Delete?語句每次刪除一行，并在事務(wù)日志中為所刪除的每行記錄一項。
truncate?table通過釋放存儲表數(shù)據(jù)所用的數(shù)據(jù)頁來刪除數(shù)據(jù)，并且只在事務(wù)日志中記錄頁的釋放。
truncate?table刪除表中的所有行，但表結(jié)構(gòu)及其列、約束、索引等保持不變。
新行標(biāo)識所用的計數(shù)值重置為該列的種子。如果想保留標(biāo)識計數(shù)值，請改用?Delete。
對于由?FOREIGN?KEY?約束引用的表，不能使用truncate?table，而應(yīng)使用不帶?Where?子句的?Delete?語句。
由于truncate?table不記錄在日志中，所以它不能激活觸發(fā)器。?
truncate?table不能用于參與了索引視圖的表。?

truncate,delete,drop的異同點：??
注意:這里說的delete是指不帶where子句的delete語句?
??
相同點:truncate和不帶where子句的delete,?以及drop都會刪除表內(nèi)的數(shù)據(jù)??

不同點:??
1.truncate和?delete只刪除數(shù)據(jù)不刪除表的結(jié)構(gòu)(定義)??
??drop語句將刪除表的結(jié)構(gòu)被依賴的約束(constrain),觸發(fā)器(trigger),索引(index);?
??依賴于該表的存儲過程/函數(shù)將保留,但是變?yōu)閕nvalid狀態(tài).?
??
2.delete語句是dml,這個操作會放到rollback?segement中,事務(wù)提交之后才生效;
??如果有相應(yīng)的trigger,執(zhí)行的時候?qū)⒈挥|發(fā).??
??truncate,drop是ddl,?操作立即生效,原數(shù)據(jù)不放到rollback?segment中,不能回滾.?操作不觸發(fā)trigger.?

3.delete語句不影響表所占用的extent,?高水線(high?w2atermark)保持原位置不動??
???顯然drop語句將表所占用的空間全部釋放??
???truncate?語句缺省情況下將空間釋放到?minextents個?extent,除非使用reuse?storage;???
???truncate會將高水線復(fù)位(回到最開始).?

4.速度,一般來說:?drop>?truncate?>?delete?

5.安全性:小心使用drop?和truncate,尤其沒有備份的時候.否則哭都來不及?

使用上：?
想刪除部分?jǐn)?shù)據(jù)行用delete,注意帶上where子句.?回滾段要足夠大.?

想刪除表,當(dāng)然用drop?

想保留表而將所有數(shù)據(jù)刪除.?如果和事務(wù)無關(guān),用truncate即可.?

如果和事務(wù)有關(guān),或者想觸發(fā)trigger,還是用delete.?

如果是整理表內(nèi)部的碎片,可以用truncate跟上reuse?stroage,再重新導(dǎo)入/插入數(shù)據(jù)?

---------------------------好句收藏--------------------------

1.UNION?將返回兩個查詢的結(jié)果并去除其中的重復(fù)部分
?SELECT?NAME?FROM?SOFTBALL
?UNION
?SELECT?NAME?FROM?FOOTBALL

?distinct?(去掉完全重復(fù)的記錄)
?select?distinct?*?from?表名
?
?with?ties?(并列顯示完全重復(fù)的記錄)
?select?Top?10?with?ties?*?from?表名

2.UNION?ALL?不去掉重復(fù)的記錄
?SELECT?NAME?FROM?SOFTBALL
?UNION?ALL
?SELECT?NAME?FROM?FOOTBALL

3.INTERSECT?返回兩個表中共有的行
?SELECT?*?FROM?FOOTBALL
?INTERSECT
?SELECT?*?FROM?SOFTBALL

4.MINUS?(相減)
返回的記錄是存在于第一個表中但不存在于第二個表中的記錄
?SELECT?*?FROM?FOOTBALL?
?MINUS
?SELECT?*?FROM?SOFTBALL

5.in(滿足括號里任意一個條件即可)
?SELECT?*?FROM?FRIENDS?WHERE?STATE?IN('CA','CO','LA')

6.BEWTEEN?(滿足區(qū)間)
?SELECT?*?FROM?PRICE?WHERE?WHOLESALE?BETWEEN?0.25?AND?0.75

7.連接(||)
可以將兩個字符串連接起來
?SELECT?(NAMEa?||?NAMEb)?as?新列名?FROM?FRIENDS
格式化連接
?SELECT?(NAMEa?||?','?||?NAMEb)?as?新列名?FROM?FRIENDS?--在連接的數(shù)據(jù)中間加上自定義字符串

8.STARTING?WITH?(它的作用與like相似,用之前要測試sql解釋器是否支持此語法)
?SELECT?列名1，列名2...??FROM?表名?WHERE?列名?STARTING?WITH('Ca')



ORDER?BY?(排序)
升序：SELECT?*?FROM?表名?ORDER?BY?列名
降序：SELECT?*?FROM?表名?ORDER?BY?列名?DESC
技巧：假如你已經(jīng)知道了你想要進行排序的列是表中的第一列的話，那么你可以用ORDER
BY?1?來代替輸入列的名字

GROUP?BY?(分組查詢)
?SELECT?列名?FROM?表名?GROUP?BY?列名

HAVING?(對你需要進行分組的數(shù)據(jù)進行限制)
?SELECT?列名1，AVG(列名2)?FROM?表名?GROUP?BY?列名?HAVING?AVG(列名3)>66

---表的不等值聯(lián)合
不等值聯(lián)合則是在WHERE?子句中使用除了等號以外的其它比較運算符
例句：SELECT?O.NAME,O.PARTNUM,P.PARTNUM?FROM?ORDERS?as?O,PART?as?P?WHERE?O.PARTNUM?>?P.PARTNUM

---表的自我聯(lián)合
?WHERE?F.PARTNUM?=?S.PARTNUM?AND?F.DESCRIPTION?<>?S.DESCRIPTION

------------------------日期函數(shù)--------------------------

VARIANCE?(返回某一列數(shù)值的方差)?
例句：SELECT?VARIANCE(列名)?AS?新列名?FROM?表名??--列必須為int或double等數(shù)值類型

STDDEV?(返回某一列數(shù)值的標(biāo)準(zhǔn)差)
例句：SELECT?STDDEV(列名)?AS?新列名?FROM?表名??--列必須為int或double等數(shù)值類型

ADD_MONTHS?(該函數(shù)的功能是將給定的日期增加一個月)
例句：SELECT?ADD_MONTHS(ENDDATE,1)?AS?新列名?FROM?表名??--ENDDATE為datetime類型
??????ADD_MONTHS(ENDDATE,1)可以使用于where條件

LAST_DAY?(可以返回指定月份的最后一天是幾號)
例句：SELECT?LAST_DAY(ENDDATE)?AS?新列名?FROM?表名

DISTINCT?(得到唯一的結(jié)果,就是去掉重復(fù)的結(jié)果)
例句：SELECT?DISTINCT?列名?FROM?表名

MONTHS_BETWEEN?(得到給定的兩個日期中有多少個月)
例句1：SELECT?MONTHS_BETWEEN(ENDDATE,STARTDATE)?AS?新列名?FROM?表名
例句2：SELECT?*?FROM?表名?WHERE?MONTHS_BETWEEN(DATETIME1,DATETIME2)>0

SYSDATE??(將返回系統(tǒng)的日期和時間)
例句：SELECT?DISTINCT?SYSDATE?FROM?表名

----------------------數(shù)學(xué)函數(shù)--------------------------

ABS()?函數(shù)返回給定數(shù)字的絕對值
CEIL()?返回與給定參數(shù)相等或比給定參數(shù)在的最小整數(shù)
FLOOR()?返回與給定參數(shù)相等或比給定參數(shù)在的最大整數(shù)
MOD(A,B)?返回A?與B?相除后的余數(shù)
SIGN()??如果參數(shù)的值為負(fù)數(shù)返回-1?,如果參數(shù)的值為正數(shù)返回1?,如果參數(shù)為零返回零
SQRT()?該函數(shù)返回參數(shù)的平方根,由于負(fù)數(shù)是不能開平方的所以不能將該函數(shù)應(yīng)用于負(fù)數(shù)

-----------------------字符函數(shù)---------------------------

CHR()?該函數(shù)返回與所給數(shù)值參數(shù)等當(dāng)?shù)脑贏SCLL碼字符,返回的字符取決于數(shù)據(jù)庫所依賴的字符集
例句：SELECT?CHR(列名)?FROM?表名

CONCAT()?與||符號相同，表示將兩個字符串連接起來
例句：SELECT?CONCAT(列名1,列名2)?FROM?表名

INITCAP()?該函數(shù)將參數(shù)的第一個字母變?yōu)榇髮?此外其它的字母則轉(zhuǎn)換成小寫
例句：SELECT?INITCAP(列名)?as?新列名?FROM?表名

LOWER()?將參數(shù)全部轉(zhuǎn)換為小寫字母
UPPER()?將參數(shù)全部轉(zhuǎn)換為大寫字母
LENGTH()?將返回指定字符串的長度

----------------------轉(zhuǎn)換函數(shù)----------------------

TO_CHAR()?將一個數(shù)字轉(zhuǎn)換為字符型
TO_NUMBER()?將一個字符串型數(shù)字轉(zhuǎn)換為數(shù)值型

---其它函數(shù)
GREATEST()?將會返回在字母表中最靠后的字符開頭的字符串,函數(shù)是返回幾個表達式中最大的；
例句：SELECT?GREATEST(‘ALPHA’,’BRAVO’,’FOXTROT’,’DELTA’,’FP’)?FROM?表名

LEAST()????函數(shù)是返回幾個表達式中最小的！
例句：SELECT?DISTINCT?LEAST(34,567,3,45,1090)?FROM?表名

USER()?函數(shù)將返回當(dāng)前使用數(shù)據(jù)庫的用戶的名字
例句：SELECT?DISTINCT?USER?FROM?表名


-------------------條件語句后用的關(guān)鍵字---------------

EXISTS
從子查詢中返回的行數(shù)至少有一行時，EXIST返回為true。返回為空時，EXIST返回為false。
例句：
SELECT?NAME?FROM?ORDERS
WHERE?EXISTS(SELECT?*?FROM?ORDERS?WHERE?NAME?='MOSTLY?HARMLESS')


ANY/SOME???ANY與SOME具有同樣的功能
ANY與子查詢中的每一行與主查詢進行比較,并對子查詢中的每一行返回一個TRUE值
區(qū)別：
IN只相當(dāng)于多個等號的作用,IN不能用于大于或小于的判斷。
而ANY?和SOME?則可以使用其它的比較運算符如大于或小于。
例句：
SELECT?NAME?FROM?ORDERS?WHERE?NAME?>?ANY
(SELECT?NAME?FROM?ORDERS?WHERE?NAME?='JACKS?BIKE')


ALL?關(guān)鍵字的作用在于子查詢中的所有結(jié)果均滿足條件時它才會返回TRUE，ALL常起雙重否定的作用。
例句：
SELECT?NAME?FROM?ORDERS?WHERE?NAME?<>?ALL
(SELECT?NAME?FROM?ORDERS?WHERE?NAME?='JACKS?BIKE')


-------------------局部變量和全局變量--------------------

局部變量必須以“@”開頭，而且必須先用DECLARE命令說明后才可使用。
語法：DECLARE?@變量名?變量類型?

局部變量賦值必須使用SELECT或SET命令來設(shè)定變量的值
語法：??SELECT?@局部變量=變量值
	SET??@局部變量=變量值

全局變量不是由用戶的程序定義的，它們是在服務(wù)器級定應(yīng)義的。
只能使用預(yù)先說明及定義的變局變量。
引用全局變量時，必須以“@@”開頭。
局部變量的名稱不能與全局變量的名稱相同、否則會在應(yīng)用中出錯




----------------------事務(wù)-視圖-索引------------------

事務(wù)是一種機制，用以維護數(shù)據(jù)庫的完整性。

事務(wù)有4個屬性：原子性（Atomicity）、一致性（Consistency）、隔離性（Isolation）以及持久性（Durability），也稱作事務(wù)的ACID屬性。?

原子性：事務(wù)內(nèi)的所有工作要么全部完成，要么全部不完成，不存在只有一部分完成的情況。?

一致性：事務(wù)內(nèi)的然后操作都不能違反數(shù)據(jù)庫的然后約束或規(guī)則，事務(wù)完成時有內(nèi)部數(shù)據(jù)結(jié)構(gòu)都必須是正確的。?

隔離性：事務(wù)直接是相互隔離的，如果有兩個事務(wù)對同一個數(shù)據(jù)庫進行操作，比如讀取表數(shù)據(jù)。
????????任何一個事務(wù)看到的所有內(nèi)容要么是其他事務(wù)完成之前的狀態(tài)，要么是其他事務(wù)完成之后的狀態(tài)。
????????一個事務(wù)不可能遇到另一個事務(wù)的中間狀態(tài)。?

持久性：事務(wù)完成之后，它對數(shù)據(jù)庫系統(tǒng)的影響是持久的，即使是系統(tǒng)錯誤，重新啟動系統(tǒng)后，該事務(wù)的結(jié)果依然存在。

事務(wù)的模式?

????????a、?顯示事務(wù)?

????????顯示事務(wù)就是用戶使用T-SQL明確的定義事務(wù)的開始（begin?transaction）和提交（commit?transaction）或回滾事務(wù)（rollback?transaction）?

????????b、?自動提交事務(wù)?

????????自動提交事務(wù)是一種能夠自動執(zhí)行并能自動回滾事務(wù)，這種方式是T-SQL的默認(rèn)事務(wù)方式。
????????例如在刪除一個表記錄的時候，如果這條記錄有主外鍵關(guān)系的時候，刪除就會受主外鍵約束的影響，那么這個刪除就會取消。?

????????可以設(shè)置事務(wù)進入隱式方式：set?implicit_transaction?on;?

????????c、?隱式事務(wù)?

????????隱式事務(wù)是指當(dāng)事務(wù)提交或回滾后，SQL?Server自動開始事務(wù)。因此，隱式事務(wù)不需要使用begin?transaction顯示開始，?
????????只需直接失業(yè)提交事務(wù)或回滾事務(wù)的T-SQL語句即可。?

????????使用時，需要設(shè)置set?implicit_transaction?on語句，將隱式事務(wù)模式打開，下一個語句會啟動一個新的事物，再下一個語句又將啟動一個新事務(wù)。



開始事務(wù)：?begin????transaction
提交事務(wù)：?commit???transaction
回滾事務(wù)：?rollback?transaction


創(chuàng)建視圖：create?view?視圖名?as刪除視圖：drop?view?試圖名
視圖定義中的select語句中不能包括下列：
	??1.order?by子句，除非select語句的選擇列有top子句
	??2.into關(guān)鍵字
	??3.引用臨時表或變量

創(chuàng)建索引：create?unique?【clustered?|?nonclustered】?index?索引名?on?表名(列名)?【with?fillfactor=x】
??????????unique??可選，指定唯一索引
??????????clustered?,?nonclustered?可選，指定是聚集索引或非聚集索引
??????????fillfactor?可選，表示填充因子，指定一個0-100的值，該值指示索引頁填滿的空間所占的百分比

刪除索引：drop?index?表名.索引名


下面的表總結(jié)了何時使用聚集索引或非聚集索引(很重要)。

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

?	外鍵列??		??應(yīng)???		???????	應(yīng)?
?	主鍵列??		??應(yīng)??			應(yīng)?
?列經(jīng)常被分組排序(order?by)??	??應(yīng)??			應(yīng)?
????返回某范圍內(nèi)的數(shù)據(jù)??	??應(yīng)??			不應(yīng)?
?????小數(shù)目的不同值??		??應(yīng)??			不應(yīng)?
?????大數(shù)目的不同值??		?不應(yīng)??			應(yīng)?
??????頻繁更新的列?		?不應(yīng)???		應(yīng)?
?????頻繁修改索引列??		?不應(yīng)??			應(yīng)?
????一個或極少不同值??		?不應(yīng)??			不應(yīng)?




-------------------------操作數(shù)據(jù)庫----------------------

SQL分類：

DDL類型包括數(shù)據(jù)庫、表的創(chuàng)建，修改，刪除，聲明—數(shù)據(jù)定義語言(CREATE，ALTER，DROP，DECLARE)

DML類型包括數(shù)據(jù)表中記錄的查詢，刪除，修改，插入—數(shù)據(jù)操縱語言(SELECT，DELETE，UPDATE，INSERT)

DCL類型包括數(shù)據(jù)庫用戶賦權(quán)，廢除用戶訪問權(quán)限，?提交當(dāng)前事務(wù)，中止當(dāng)前事務(wù)—數(shù)據(jù)控制語言(GRANT，REVOKE，COMMIT，ROLLBACK)

首先,簡要介紹基礎(chǔ)語句：

1、說明：創(chuàng)建數(shù)據(jù)庫

CREATE?DATABASE?db1(db1代表數(shù)據(jù)庫，可自命名)
on?????primary??????--默認(rèn)屬于primary主文件組，可省略
(
?--數(shù)據(jù)文件的具體描述
?name='MySchool_data',???????--主數(shù)據(jù)文件的邏輯名稱
?filename='D:projectMySchool_data.mdf',?--主數(shù)據(jù)文件的物理名稱
?size=5MB,???????????????????--主數(shù)據(jù)文件的初始大小
?maxsize=100MB,??????????????--主數(shù)據(jù)文件增長的最大值
?filegrowth=15%??????????????--主數(shù)據(jù)文件的增長率
)
log?on
(
?--日記文件的具體描述，各參數(shù)含義同上
?name='MySchool_log',
?filename='D:projectMySchool_data.ldf',
?size=2MB,
?filegrowth=1MB
)

2、說明：刪除數(shù)據(jù)庫

drop?database?db1(db1代表數(shù)據(jù)庫，可自命名)

3、說明：備份sql?server

---?創(chuàng)建?備份數(shù)據(jù)的?device

USE?master

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

---?開始?備份

BACKUP?DATABASE?pubs?TO?testBack

4、說明：創(chuàng)建新表

create?table?tb1
(
Id?int?not?null?primary?key,?--設(shè)置為主鍵
one?int?identity(1,1),???????--設(shè)為標(biāo)識列
name?varchar?not?null,	?????--非空
phone?nvarchar(100),?????????--可以為空
...
)


根據(jù)已有的表創(chuàng)建新表：

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

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


5、說明：

刪除新表：drop?table?tb1
use??MySchool??--將當(dāng)前數(shù)據(jù)庫設(shè)置為MySchool
if???exists(select?*?from?MySchool?where?name='Student')?--exist是查詢語句，檢測某個查詢是否存在
drop?table?Student

6、說明：

增加一個列：Alter?table?表名?add?字段名?字段類型?字段說明/約束

添加帶主鍵及約束的語法：
alter?table??表名
add?constraint??約束名??約束類型??具體的約束說明

--添加主鍵約束(將StudentNo作為主鍵)
alter?table?Student
add?constraint?PK_stuNo?primary?key?(StudentNo)
--添加唯一約束(身份證號唯一)
alter?table?Student
add?constraint?UQ_stuID?unique?(身份證號列名)
--添加默認(rèn)約束(如果地址不填，默認(rèn)為“地址不詳”)
alter?table?Student
add?constraint?DF_stuAddress?default?('地址不詳')?for?Address
--添加檢查約束(要求出生日期在1980年1月1日之后)
alter?table?Student
add?constraint?CK_stuBornDate?check?(BornDate?>=?'1980-01-01')
--添加外鍵約束(主表Student和從表Result建立關(guān)系，關(guān)聯(lián)列為StudentNo)
alter?table?Result
add?constraint?FK_stuNo?
	foreign?key?(stuNo)?references?Student(stuNo)


刪除列的語法：

你刪除的時候會提示你，有默認(rèn)約束依賴該字段，那么你需要先刪除默認(rèn)約束（錯誤提示里會有默認(rèn)約束名），再刪除字段：
ALTER?TABLE?表名?DROP?CONSTRAINT?默認(rèn)約束名
GO
ALTER?TABLE?表名???DROP?COLUMN	字段名
GO

刪除約束：
alter?table?Student
drop?constraint?約束名

例句：
alter?table?Student
add?constraint?PK_stuNo

7、說明：

添加主鍵：Alter?table?tabname?add?primary?key(ID)(設(shè)置某字段為主鍵，ID可自由設(shè)置，主鍵數(shù)據(jù)不可重復(fù)）

說明：

刪除主鍵：Alter?table?tabname?drop?primary?key(ID)（刪除某字段主鍵）

8、說明：

創(chuàng)建索引：create?[unique]?index?idxname?on?tabname(col….)

刪除索引：drop?index?idxname

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

9、說明：

創(chuàng)建視圖：create?view?viewname?as?select?statement

刪除和修改視圖
alter?view??yourviewname???as...
drop?view??yourviewname???as...
加密視圖
alter??view??yourviewname??with?encryption?as...
加密了之后連你自己也看不到原代碼了


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

選擇：select?*?from?table1?where?Id=1(Id=1為條件語句，根據(jù)自己情況自定義）

插入：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]

總數(shù)：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?運算符通過組合其他兩個結(jié)果表(例如?TABLE1?和?TABLE2)并消去表中任何重復(fù)行而派生出一個結(jié)果表。
當(dāng)?ALL?隨?UNION?一起使用時(即?UNION?ALL)，不消除重復(fù)行。兩種情況下，派生表的每一行不是來自?TABLE1?就是來自?TABLE2。

B：?EXCEPT?運算符

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

C：?INTERSECT?運算符

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

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

12、說明：使用外連接

A、left?outer?join：

左外連接(左連接)：結(jié)果集幾包括連接表的匹配行，也包括左連接表的所有行。

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:

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

C：full?outer?join：

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

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

1、說明：復(fù)制表(只復(fù)制結(jié)構(gòu),源表名：a?新表名：b)?(Access可用)

法一：select?*?into?b?from?a?where?1<>1?（僅用于SQlServer）

法二：select?top?0?*?into?b?from?a

2、說明：拷貝表(拷貝數(shù)據(jù),源表名：a?目標(biāo)表名：b)?(Access可用)

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

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

insert?into?b(a,?b,?c)?select?d,e,f?from?b?in?‘具體數(shù)據(jù)庫’?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、說明：顯示文章、提交人和最后回復(fù)時間

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限制查詢數(shù)據(jù)范圍時包括了邊界值,not?between不包括

select?*?from?table1?where?time?between?time1?and?time2

select?a,b,c,?from?table1?where?a?not?between?數(shù)值1?and?數(shù)值2

9、說明：in?的使用方法

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

10、說明：兩張關(guān)聯(lián)表，刪除主表中已經(jīng)在副表中沒有的信息

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

11、說明：四表聯(lián)查問題：

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?語句搞定數(shù)據(jù)庫分頁

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值相同的數(shù)據(jù)中對應(yīng)的a最大的記錄的所有信息(類似這樣的用法可以用于論壇每月排行榜,每月熱銷產(chǎn)品分析,按科目成績排名,等等.)

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

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

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

17、說明：隨機取出10條數(shù)據(jù)

select?top?10?*?from?tablename?order?by?newid()

18、說明：隨機選擇記錄

select?newid()

19、說明：刪除重復(fù)記錄

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

20、說明：列出數(shù)據(jù)庫里所有的表名

select?name?from?sysobjects?where?type='U'

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

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

22、說明：列示type、vender、pcs字段，以type字段排列，case可以方便地實現(xiàn)多重選擇，類似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

顯示結(jié)果：

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

隨機選擇數(shù)據(jù)庫記錄的方法(使用Randomize函數(shù)，通過SQL語句實現(xiàn))

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

Randomize

RNumber?=?Int(Rnd*499)?+1

While?Not?objRec.EOF

If?objRec("ID")?=?RNumber?THEN

...?這里是執(zhí)行腳本?...

end?if

objRec.MoveNext

Wend

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

采用SQL，你就可以很快地找出準(zhǔn)確的記錄并且打開一個只包含該記錄的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沒有包含其他內(nèi)容，因此你很快就能找到你需要的記錄這樣就大大降低了處理時間。

再談隨機數(shù)

現(xiàn)在你下定決心要榨干Random?函數(shù)的最后一滴油，那么你可能會一次取出多條隨機記錄或者想采用一定隨機范圍內(nèi)的記錄。
把上面的標(biāo)準(zhǔn)Random?示例擴展一下就可以用SQL應(yīng)對上面兩種情況了。

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

SQL?=?"SELECT?*?FROM?Customers?WHERE?ID?=?"?&?RNumber?&?"?OR?ID?=?"?&?RNumber2?&?"?OR?ID?=?"?&?RNumber3

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

SQL?=?"SELECT?*?FROM?Customers?WHERE?ID?BETWEEN?"?&?RNumber?&?"?AND?"?&?RNumber?&?"+?9"

注意：以上代碼的執(zhí)行目的不是檢查數(shù)據(jù)庫內(nèi)是否有9條并發(fā)記錄。

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

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

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

mysql?select?*?From?表名?Order?By?rand()?Limit?n

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

語法?select?table1.fd1,table1,fd2,table2.fd2?From?table1?left?join?table2?on?table1.fd1,table2.fd1?where?...

使用SQL語句?用...代替過長的字符串顯示

語法：

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

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

Conn.Execute說明

Execute方法

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

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

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

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

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

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

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

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

·BeginTrans、RollbackTrans、CommitTrans方法

這三個方法是連接對象提供的用于事務(wù)處理的方法。BeginTrans用于開始一個事物;RollbackTrans用于回滾事務(wù);CommitTrans用于提交所有的事務(wù)處理結(jié)果，即確認(rèn)事務(wù)的處理。

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

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

SQL語句大全精要

DELETE語句

DELETE語句：用于創(chuàng)建一個刪除查詢，可從列在?FROM?子句之中的一個或多個表中刪除記錄，且該子句滿足?WHERE?子句中的條件，可以使用DELETE刪除多個記錄。

語法：DELETE?[table.*]?FROM?table?WHERE?criteria

語法：DELETE?*?FROM?table?WHERE?criteria='查詢的字'

說明：table參數(shù)用于指定從其中刪除記錄的表的名稱。

criteria參數(shù)為一個表達式，用于指定哪些記錄應(yīng)該被刪除的表達式。

可以使用?Execute?方法與一個?DROP?語句從數(shù)據(jù)庫中放棄整個表。不過，若用這種方法刪除表，將會失去表的結(jié)構(gòu)。
不同的是當(dāng)使用?DELETE，只有數(shù)據(jù)會被刪除;表的結(jié)構(gòu)以及表的所有屬性仍然保留，例如字段屬性及索引。

UPDATE

有關(guān)UPDATE，急!!!!!!!!!!!

在ORACLE數(shù)據(jù)庫中

表?A?(?ID?,FIRSTNAME,LASTNAME?)

表?B(?ID,LASTNAME)

表?A?中原來ID,FIRSTNAME兩個字段的數(shù)據(jù)是完整的

表?B中原來ID,LASTNAME兩個字段的數(shù)據(jù)是完整的

現(xiàn)在要把表?B中的LASTNAME字段的相應(yīng)的數(shù)據(jù)填入到A表中LASTNAME相應(yīng)的位置。兩個表中的ID字段是相互關(guān)聯(lián)的。

update?a?set?a.lastname=(select?b.lastname?from?b?where?a.id=b.id)

常用sql語句命令的作用
1.?查看數(shù)據(jù)庫的版本????????

???select?@@version?

??2.?查看數(shù)據(jù)庫所在機器操作系統(tǒng)參數(shù)????????

??exec?master..xp_msver?


??3.?查看數(shù)據(jù)庫啟動的參數(shù)?????????

??sp_configure?


??4.?查看數(shù)據(jù)庫啟動時間?????????

??select?convert(varchar(30),login_time,120)?from?master..sysprocesses?where?spid=1?

??查看數(shù)據(jù)庫服務(wù)器名和實例名?

??print?'Server?Name...............：'?+?convert(varchar(30),@@SERVERNAME)?????????

??print?'Instance..................：'?+?convert(varchar(30),@@SERVICENAME)???
????????
??5.?查看所有數(shù)據(jù)庫名稱及大小?????

??sp_helpdb?

??重命名數(shù)據(jù)庫用的SQL?

??sp_renamedb?'old_dbname',?'new_dbname'?


??6.?查看所有數(shù)據(jù)庫用戶登錄信息?????

??sp_helplogins?

??查看所有數(shù)據(jù)庫用戶所屬的角色信息????????

??sp_helpsrvrolemember?

??修復(fù)遷移服務(wù)器時孤立用戶時,可以用的fix_orphan_user腳本或者LoneUser過程?

??更改某個數(shù)據(jù)對象的用戶屬主?
??
??sp_changeobjectowner?[@objectname?=]?'object',?[@newowner?=]?'owner'?

??注意：更改對象名的任一部分都可能破壞腳本和存儲過程。?

??把一臺服務(wù)器上的數(shù)據(jù)庫用戶登錄信息備份出來可以用add_login_to_aserver腳本?

??查看某數(shù)據(jù)庫下,對象級用戶權(quán)限?

??sp_helprotect?


??7.?查看鏈接服務(wù)器????????????
??
??sp_helplinkedsrvlogin?

??查看遠(yuǎn)端數(shù)據(jù)庫用戶登錄信息
??
??sp_helpremotelogin?


??8.查看某數(shù)據(jù)庫下某個數(shù)據(jù)對象的大小?

??sp_spaceused?@objname?

??還可以用sp_toptables過程看最大的N(默認(rèn)為50)個表?

??查看某數(shù)據(jù)庫下某個數(shù)據(jù)對象的索引信息?

??sp_helpindex?@objname?

??還可以用SP_NChelpindex過程查看更詳細(xì)的索引情況?

??SP_NChelpindex?@objname?

??clustered索引是把記錄按物理順序排列的，索引占的空間比較少。??

??對鍵值DML操作十分頻繁的表我建議用非clustered索引和約束，fillfactor參數(shù)都用默認(rèn)值。?

??查看某數(shù)據(jù)庫下某個數(shù)據(jù)對象的的約束信息?

??sp_helpconstraint?@objname

9.查看數(shù)據(jù)庫里所有的存儲過程和函數(shù)?


??use?@database_name?


??sp_stored_procedures?


??查看存儲過程和函數(shù)的源代碼?


??sp_helptext?'@procedure_name'?


??查看包含某個字符串@str的數(shù)據(jù)對象名稱?


??select?distinct?object_name(id)?from?syscomments?where?text?like?'%@str%'?


??創(chuàng)建加密的存儲過程或函數(shù)在AS前面加WITH?ENCRYPTION參數(shù)?


??解密加密過的存儲過程和函數(shù)可以用sp_decrypt過程?



??10.查看數(shù)據(jù)庫里用戶和進程的信息?

??sp_who?

??查看SQL?Server數(shù)據(jù)庫里的活動用戶和進程的信息?

??sp_who?'active'?

??查看SQL?Server數(shù)據(jù)庫里的鎖的情況?

??sp_lock?

??進程號1--50是SQL?Server系統(tǒng)內(nèi)部用的,進程號大于50的才是用戶的連接進程.?
??
??spid是進程編號,dbid是數(shù)據(jù)庫編號,objid是數(shù)據(jù)對象編號?

??查看進程正在執(zhí)行的SQL語句?

??dbcc?inputbuffer?()?

??推薦大家用經(jīng)過改進后的sp_who3過程可以直接看到進程運行的SQL語句?

??sp_who3?

??檢查死鎖用sp_who_lock過程?

??sp_who_lock?
???????????????
??11.查看和收縮數(shù)據(jù)庫日志文件的方法?

??查看所有數(shù)據(jù)庫日志文件大小???????????

??dbcc?sqlperf(logspace)?

??如果某些日志文件較大，收縮簡單恢復(fù)模式數(shù)據(jù)庫日志，收縮后@database_name_log的大小單位為M?

??backup?log?@database_name?with?no_log?

??dbcc?shrinkfile?(@database_name_log,?5)?


??12.分析SQL?Server?SQL?語句的方法：

??set?statistics?time?{on?|?off}?

??set?statistics?io?{on?|?off}?

??圖形方式顯示查詢執(zhí)行計劃?

??在查詢分析器->查詢->顯示估計的評估計劃(D)-Ctrl-L????或者點擊工具欄里的圖形?

??文本方式顯示查詢執(zhí)行計劃?

??set?showplan_all?{on?|?off}?

??set?showplan_text?{?on?|?off?}?

??set?statistics?profile?{?on?|?off?}?


??13.出現(xiàn)不一致錯誤時，NT事件查看器里出3624號錯誤，修復(fù)數(shù)據(jù)庫的方法?
??先注釋掉應(yīng)用程序里引用的出現(xiàn)不一致性錯誤的表，然后在備份或其它機器上先恢復(fù)然后做修復(fù)操作?

??alter?database?[@error_database_name]?set?single_user?

??修復(fù)出現(xiàn)不一致錯誤的表?

??dbcc?checktable('@error_table_name',repair_allow_data_loss)?

??或者可惜選擇修復(fù)出現(xiàn)不一致錯誤的小型數(shù)據(jù)庫名?

??dbcc?checkdb('@error_database_name',repair_allow_data_loss)?

??alter?database?[@error_database_name]?set?multi_user?

??CHECKDB?有3個參數(shù)：

??repair_allow_data_loss?包括對行和頁進行分配和取消分配以改正分配錯誤、結(jié)構(gòu)行或頁的錯誤，以及刪除已損壞的文本對象，這些修復(fù)可能會導(dǎo)致一些數(shù)據(jù)丟失。?

??修復(fù)操作可以在用戶事務(wù)下完成以允許用戶回滾所做的更改。?

??如果回滾修復(fù)，則數(shù)據(jù)庫仍會含有錯誤，應(yīng)該從備份進行恢復(fù)。?

??如果由于所提供修復(fù)等級的緣故遺漏某個錯誤的修復(fù)，則將遺漏任何取決于該修復(fù)的修復(fù)。?

??修復(fù)完成后，請備份數(shù)據(jù)庫。??

??repai*_**st?進行小的、不耗時的修復(fù)操作，如修復(fù)非聚集索引中的附加鍵。?

??這些修復(fù)可以很快完成，并且不會有丟失數(shù)據(jù)的危險。??

??repair_rebuild?執(zhí)行由?repai*_**st?完成的所有修復(fù)，包括需要較長時間的修復(fù)（如重建索引）。?

??執(zhí)行這些修復(fù)時不會有丟失數(shù)據(jù)的危險。