sqlserver數(shù)據(jù)庫操作大全

我的小園地 2014-04-30

展開全文

本文為累計(jì)整理，有點(diǎn)亂，湊合著看吧！

☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆
☆
☆
☆ sql 寶典
☆
☆
☆ 2012年-8月修訂版
☆
☆ 修訂人：情誼續(xù)寫經(jīng)典
☆
☆ 鄭重聲明：本版本并非國際標(biāo)準(zhǔn)，使用前請查詢數(shù)據(jù)庫文檔是否支持,轉(zhuǎn)載請注明出處！
☆
☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆
MS SQL里沒有括號時(shí)，運(yùn)算進(jìn)行的次序?qū)⑹窍瘸撕蟪倌：蠹訙p
減號(-)也有兩種用途：1.作為負(fù)號使用 2.從某一列中減去另一列
and or not
如果一個(gè)where子句中同時(shí)出現(xiàn)這三個(gè)操作符
最先評估not 然后是and 然后是or
coalesce 哪個(gè)不為空用哪個(gè)
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)建一個(gè)數(shù)據(jù)庫表
drop table --從數(shù)據(jù)庫中刪除表
alter table --修改數(shù)據(jù)庫表結(jié)構(gòu)
create view --創(chuàng)建一個(gè)視圖
drop view --從數(shù)據(jù)庫中刪除視圖
create index --為數(shù)據(jù)庫表創(chuàng)建一個(gè)索引
drop index --從數(shù)據(jù)庫中刪除索引
create proceduer --創(chuàng)建一個(gè)存儲過程
drop proceduer --從數(shù)據(jù)庫中刪除存儲過程
create trigger --創(chuàng)建一個(gè)觸發(fā)器
drop trigger --從數(shù)據(jù)庫中刪除觸發(fā)器
create schema --向數(shù)據(jù)庫添加一個(gè)新模式
drop schema --從數(shù)據(jù)庫中刪除一個(gè)模式
create domain --創(chuàng)建一個(gè)數(shù)據(jù)值域
alter domain --改變域定義
drop domain --從數(shù)據(jù)庫中刪除一個(gè)域
--數(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ù)訪問計(jì)劃
open --檢索查詢結(jié)果打開一個(gè)游標(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ù)時(shí)可以用：
insert into 表名(列名1，列名2...) values (值1，值2...)
插入大量數(shù)據(jù)時(shí)可以用：
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 語句的另外一個(gè)用處對表進(jìn)行備份：
select * into 臨時(shí)表名 from 源表名
----------SQL中truncate table和delete和drop的區(qū)別 ----------
truncate table在功能上與不帶 Where 子句的 Delete 語句相同,二者均刪除表中的全部行
但truncate table比 Delete 速度快，且使用的系統(tǒng)和事務(wù)日志資源少。
Delete 語句每次刪除一行，并在事務(wù)日志中為所刪除的每行記錄一項(xiàng)。
truncate table通過釋放存儲表數(shù)據(jù)所用的數(shù)據(jù)頁來刪除數(shù)據(jù)，并且只在事務(wù)日志中記錄頁的釋放。
truncate table刪除表中的所有行，但表結(jié)構(gòu)及其列、約束、索引等保持不變。
新行標(biāo)識所用的計(jì)數(shù)值重置為該列的種子。如果想保留標(biāo)識計(jì)數(shù)值，請改用 Delete。
對于由 FOREIGN KEY 約束引用的表，不能使用truncate table，而應(yīng)使用不帶 Where 子句的 Delete 語句。
由于truncate table不記錄在日志中，所以它不能激活觸發(fā)器。
truncate table不能用于參與了索引視圖的表。
truncate,delete,drop的異同點(diǎn)：
注意:這里說的delete是指不帶where子句的delete語句
相同點(diǎn):truncate和不帶where子句的delete, 以及drop都會刪除表內(nèi)的數(shù)據(jù)
不同點(diǎn):
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,這個(gè)操作會放到rollback segement中,事務(wù)提交之后才生效;
如果有相應(yīng)的trigger,執(zhí)行的時(shí)候?qū)⒈挥|發(fā).
truncate,drop是ddl, 操作立即生效,原數(shù)據(jù)不放到rollback segment中,不能回滾. 操作不觸發(fā)trigger.
3.delete語句不影響表所占用的extent, 高水線(high w2atermark)保持原位置不動
顯然drop語句將表所占用的空間全部釋放
truncate 語句缺省情況下將空間釋放到 minextents個(gè) extent,除非使用reuse storage;
truncate會將高水線復(fù)位(回到最開始).
4.速度,一般來說: drop> truncate > delete
5.安全性:小心使用drop 和truncate,尤其沒有備份的時(shí)候.否則哭都來不及
使用上：
想刪除部分?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 將返回兩個(gè)查詢的結(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 返回兩個(gè)表中共有的行
SELECT * FROM FOOTBALL
INTERSECT
SELECT * FROM SOFTBALL
4.MINUS (相減)
返回的記錄是存在于第一個(gè)表中但不存在于第二個(gè)表中的記錄
SELECT * FROM FOOTBALL
MINUS
SELECT * FROM SOFTBALL
5.in(滿足括號里任意一個(gè)條件即可)
SELECT * FROM FRIENDS WHERE STATE IN('CA','CO','LA')
6.BEWTEEN (滿足區(qū)間)
SELECT * FROM PRICE WHERE WHOLESALE BETWEEN 0.25 AND 0.75
7.連接(||)
可以將兩個(gè)字符串連接起來
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)知道了你想要進(jìn)行排序的列是表中的第一列的話，那么你可以用ORDER
BY 1 來代替輸入列的名字
GROUP BY (分組查詢)
SELECT 列名 FROM 表名 GROUP BY 列名
HAVING (對你需要進(jìn)行分組的數(shù)據(jù)進(jìn)行限制)
SELECT 列名1，AVG(列名2) FROM 表名 GROUP BY 列名 HAVING AVG(列名3)>66
---表的不等值聯(lián)合
不等值聯(lián)合則是在WHERE 子句中使用除了等號以外的其它比較運(yùn)算符
例句：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ù)的功能是將給定的日期增加一個(gè)月)
例句：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 (得到給定的兩個(gè)日期中有多少個(gè)月)
例句1：SELECT MONTHS_BETWEEN(ENDDATE,STARTDATE) AS 新列名 FROM 表名
例句2：SELECT * FROM 表名 WHERE MONTHS_BETWEEN(DATETIME1,DATETIME2)>0
SYSDATE (將返回系統(tǒng)的日期和時(shí)間)
例句：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() 與||符號相同，表示將兩個(gè)字符串連接起來
例句：SELECT CONCAT(列名1,列名2) FROM 表名
INITCAP() 該函數(shù)將參數(shù)的第一個(gè)字母變?yōu)榇髮?此外其它的字母則轉(zhuǎn)換成小寫
例句：SELECT INITCAP(列名) as 新列名 FROM 表名
LOWER() 將參數(shù)全部轉(zhuǎn)換為小寫字母
UPPER() 將參數(shù)全部轉(zhuǎn)換為大寫字母
LENGTH() 將返回指定字符串的長度
----------------------轉(zhuǎn)換函數(shù)----------------------
TO_CHAR() 將一個(gè)數(shù)字轉(zhuǎn)換為字符型
TO_NUMBER() 將一個(gè)字符串型數(shù)字轉(zhuǎn)換為數(shù)值型
---其它函數(shù)
GREATEST() 將會返回在字母表中最靠后的字符開頭的字符串,函數(shù)是返回幾個(gè)表達(dá)式中最大的；
例句：SELECT GREATEST(‘ALPHA’,’BRAVO’,’FOXTROT’,’DELTA’,’FP’) FROM 表名
LEAST() 函數(shù)是返回幾個(gè)表達(dá)式中最小的！
例句：SELECT DISTINCT LEAST(34,567,3,45,1090) FROM 表名
USER() 函數(shù)將返回當(dāng)前使用數(shù)據(jù)庫的用戶的名字
例句：SELECT DISTINCT USER FROM 表名
-------------------條件語句后用的關(guān)鍵字---------------
EXISTS
從子查詢中返回的行數(shù)至少有一行時(shí)，EXIST返回為true。返回為空時(shí)，EXIST返回為false。
例句：
SELECT NAME FROM ORDERS
WHERE EXISTS(SELECT * FROM ORDERS WHERE NAME ='MOSTLY HARMLESS')
ANY/SOME ANY與SOME具有同樣的功能
ANY與子查詢中的每一行與主查詢進(jìn)行比較,并對子查詢中的每一行返回一個(gè)TRUE值
區(qū)別：
IN只相當(dāng)于多個(gè)等號的作用,IN不能用于大于或小于的判斷。
而ANY 和SOME 則可以使用其它的比較運(yùn)算符如大于或小于。
例句：
SELECT NAME FROM ORDERS WHERE NAME > ANY
(SELECT NAME FROM ORDERS WHERE NAME ='JACKS BIKE')
ALL 關(guān)鍵字的作用在于子查詢中的所有結(jié)果均滿足條件時(shí)它才會返回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ù)先說明及定義的變局變量。
引用全局變量時(shí)，必須以“@@”開頭。
局部變量的名稱不能與全局變量的名稱相同、否則會在應(yīng)用中出錯(cuò)
----------------------事務(wù)-視圖-索引------------------
事務(wù)是一種機(jī)制，用以維護(hù)數(shù)據(jù)庫的完整性。
事務(wù)有4個(gè)屬性：原子性（Atomicity）、一致性（Consistency）、隔離性（Isolation）以及持久性（Durability），也稱作事務(wù)的ACID屬性。
原子性：事務(wù)內(nèi)的所有工作要么全部完成，要么全部不完成，不存在只有一部分完成的情況。
一致性：事務(wù)內(nèi)的然后操作都不能違反數(shù)據(jù)庫的然后約束或規(guī)則，事務(wù)完成時(shí)有內(nèi)部數(shù)據(jù)結(jié)構(gòu)都必須是正確的。
隔離性：事務(wù)直接是相互隔離的，如果有兩個(gè)事務(wù)對同一個(gè)數(shù)據(jù)庫進(jìn)行操作，比如讀取表數(shù)據(jù)。
任何一個(gè)事務(wù)看到的所有內(nèi)容要么是其他事務(wù)完成之前的狀態(tài)，要么是其他事務(wù)完成之后的狀態(tài)。
一個(gè)事務(wù)不可能遇到另一個(gè)事務(wù)的中間狀態(tài)。
持久性：事務(wù)完成之后，它對數(shù)據(jù)庫系統(tǒng)的影響是持久的，即使是系統(tǒng)錯(cuò)誤，重新啟動系統(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ù)方式。
例如在刪除一個(gè)表記錄的時(shí)候，如果這條記錄有主外鍵關(guān)系的時(shí)候，刪除就會受主外鍵約束的影響，那么這個(gè)刪除就會取消。
可以設(shè)置事務(wù)進(jìn)入隱式方式：set implicit_transaction on;
c、隱式事務(wù)
隱式事務(wù)是指當(dāng)事務(wù)提交或回滾后，SQL Server自動開始事務(wù)。因此，隱式事務(wù)不需要使用begin transaction顯示開始，
只需直接失業(yè)提交事務(wù)或回滾事務(wù)的T-SQL語句即可。
使用時(shí)，需要設(shè)置set implicit_transaction on語句，將隱式事務(wù)模式打開，下一個(gè)語句會啟動一個(gè)新的事物，再下一個(gè)語句又將啟動一個(gè)新事務(wù)。
開始事務(wù)： begin transaction
提交事務(wù)： commit transaction
回滾事務(wù)： rollback transaction
創(chuàng)建視圖：create view 視圖名 as <select語句>
刪除視圖：drop view 試圖名
視圖定義中的select語句中不能包括下列：
1.order by子句，除非select語句的選擇列有top子句
2.into關(guān)鍵字
3.引用臨時(shí)表或變量
創(chuàng)建索引：create unique 【clustered | nonclustered】 index 索引名 on 表名(列名) 【with fillfactor=x】
unique 可選，指定唯一索引
clustered , nonclustered 可選，指定是聚集索引或非聚集索引
fillfactor 可選，表示填充因子，指定一個(gè)0-100的值，該值指示索引頁填滿的空間所占的百分比
刪除索引：drop index 表名.索引名
下面的表總結(jié)了何時(shí)使用聚集索引或非聚集索引(很重要)。
動作描述使用聚集索引使用非聚集索引
外鍵列應(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)
一個(gè)或極少不同值不應(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:\project\MySchool_data.mdf', --主數(shù)據(jù)文件的物理名稱
size=5MB, --主數(shù)據(jù)文件的初始大小
maxsize=100MB, --主數(shù)據(jù)文件增長的最大值
filegrowth=15% --主數(shù)據(jù)文件的增長率
)
log on
(
--日記文件的具體描述，各參數(shù)含義同上
name='MySchool_log',
filename='D:\project\MySchool_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:\mssql7backup\MyNwind_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是查詢語句，檢測某個(gè)查詢是否存在
drop table Student
6、說明：
增加一個(gè)列：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)
刪除列的語法：
你刪除的時(shí)候會提示你，有默認(rèn)約束依賴該字段，那么你需要先刪除默認(rèn)約束（錯(cuò)誤提示里會有默認(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、說明：幾個(gè)簡單的基本的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
最?。簊elect min(field1) as minvalue from table1
11、說明：幾個(gè)高級查詢運(yùn)算詞
A： UNION 運(yùn)算符
UNION 運(yùn)算符通過組合其他兩個(gè)結(jié)果表(例如 TABLE1 和 TABLE2)并消去表中任何重復(fù)行而派生出一個(gè)結(jié)果表。
當(dāng) ALL 隨 UNION 一起使用時(shí)(即 UNION ALL)，不消除重復(fù)行。兩種情況下，派生表的每一行不是來自 TABLE1 就是來自 TABLE2。
B： EXCEPT 運(yùn)算符
EXCEPT 運(yùn)算符通過包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重復(fù)行而派生出一個(gè)結(jié)果表。
當(dāng) ALL 隨 EXCEPT 一起使用時(shí) (EXCEPT ALL)，不消除重復(fù)行。
C： INTERSECT 運(yùn)算符
INTERSECT 運(yùn)算符通過只包括 TABLE1 和 TABLE2 中都有的行并消除所有重復(fù)行而派生出一個(gè)結(jié)果表。
當(dāng) ALL 隨 INTERSECT 一起使用時(shí) (INTERSECT ALL)，不消除重復(fù)行。
注：使用運(yùn)算詞的幾個(gè)查詢結(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：
全外連接：不僅包括符號連接表的匹配行，還包括兩個(gè)連接表中的所有記錄。
其次，大家來看一些不錯(cuò)的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ù)時(shí)間
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ù)范圍時(shí)包括了邊界值,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開始時(shí)間,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ù)行而派生出一個(gè)結(jié)果表
(select a from tableA ) except (select a from tableB) except (select a from tableC)
17、說明：隨機(jī)取出10條數(shù)據(jù)
select top 10 * from tablename order by newid()
18、說明：隨機(jī)選擇記錄
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可以方便地實(shí)現(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
手機(jī) B 3
手機(jī) 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
隨機(jī)選擇數(shù)據(jù)庫記錄的方法(使用Randomize函數(shù)，通過SQL語句實(shí)現(xiàn))
對存儲在數(shù)據(jù)庫中的數(shù)據(jù)來說，隨機(jī)數(shù)特性能給出上面的效果，但它們可能太慢了些。
你不能要求ASP“找個(gè)隨機(jī)數(shù)”然后打印出來。實(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)的一個(gè)隨機(jī)數(shù)(假設(shè)500就是數(shù)據(jù)庫內(nèi)記錄的總數(shù))。
然后，你遍歷每一記錄來測試ID 的值、檢查其是否匹配RNumber。滿足條件的話就執(zhí)行由THEN 關(guān)鍵字開始的那一塊代碼。
假如你的RNumber 等于495，那么要循環(huán)一遍數(shù)據(jù)庫花的時(shí)間可就長了。
雖然500這個(gè)數(shù)字看起來大了些，但相比更為穩(wěn)固的企業(yè)解決方案這還是個(gè)小型數(shù)據(jù)庫了，
后者通常在一個(gè)數(shù)據(jù)庫內(nèi)就包含了成千上萬條記錄。這時(shí)候不就死定了?
采用SQL，你就可以很快地找出準(zhǔn)確的記錄并且打開一個(gè)只包含該記錄的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，你只需要檢查匹配情況即可。只要你對以上代碼的工作滿意，你自可按需操作“隨機(jī)”記錄。
Recordset沒有包含其他內(nèi)容，因此你很快就能找到你需要的記錄這樣就大大降低了處理時(shí)間。
再談隨機(jī)數(shù)
現(xiàn)在你下定決心要榨干Random 函數(shù)的最后一滴油，那么你可能會一次取出多條隨機(jī)記錄或者想采用一定隨機(jī)范圍內(nèi)的記錄。
把上面的標(biāo)準(zhǔn)Random 示例擴(kuò)展一下就可以用SQL應(yīng)對上面兩種情況了。
為了取出幾條隨機(jī)選擇的記錄并存放在同一recordset內(nèi)，你可以存儲三個(gè)隨機(jī)數(shù)，然后查詢數(shù)據(jù)庫獲得匹配這些數(shù)字的記錄：
SQL = "SELECT * FROM Customers WHERE ID = " & RNumber & " OR ID = " & RNumber2 & " OR ID = " & RNumber3
假如你想選出10條記錄(也許是每次頁面裝載時(shí)的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ā)記錄。
隨機(jī)讀取若干條記錄，測試過
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查詢語句時(shí)，將返回查詢得到的記錄集。用法為：
Set 對象變量名=連接對象.Execute("SQL 查詢語言")
Execute方法調(diào)用后，會自動創(chuàng)建記錄集對象，并將查詢結(jié)果存儲在該記錄對象中，通過Set方法，將記錄集賦給指定的對象保存，以后對象變量就代表了該記錄集對象。
2.執(zhí)行SQL的操作性語言時(shí)，沒有記錄集的返回。此時(shí)用法為：
連接對象.Execute "SQL 操作性語句" [, RecordAffected][, Option]
·RecordAffected 為可選項(xiàng)，此出可放置一個(gè)變量，SQL語句執(zhí)行后，所生效的記錄數(shù)會自動保存到該變量中。通過訪問該變量，就可知道SQL語句隊(duì)多少條記錄進(jìn)行了操作。
·Option 可選項(xiàng)，該參數(shù)的取值通常為adCMDText，它用于告訴ADO，應(yīng)該將Execute方法之后的第一個(gè)字符解釋為命令文本。通過指定該參數(shù)，可使執(zhí)行更高效。
·BeginTrans、RollbackTrans、CommitTrans方法
這三個(gè)方法是連接對象提供的用于事務(wù)處理的方法。BeginTrans用于開始一個(gè)事物;RollbackTrans用于回滾事務(wù);CommitTrans用于提交所有的事務(wù)處理結(jié)果，即確認(rèn)事務(wù)的處理。
事務(wù)處理可以將一組操作視為一個(gè)整體，只有全部語句都成功執(zhí)行后，事務(wù)處理才算成功;若其中有一個(gè)語句執(zhí)行失敗，則整個(gè)處理就算失敗，并恢復(fù)到處里前的狀態(tài)。
BeginTrans和CommitTrans用于標(biāo)記事務(wù)的開始和結(jié)束，在這兩個(gè)之間的語句，就是作為事務(wù)處理的語句。
判斷事務(wù)處理是否成功，可通過連接對象的Error集合來實(shí)現(xiàn)，若Error集合的成員個(gè)數(shù)不為0，則說明有錯(cuò)誤發(fā)生，事務(wù)處理失敗。
Error集合中的每一個(gè)Error對象，代表一個(gè)錯(cuò)誤信息。
SQL語句大全精要
DELETE語句
DELETE語句：用于創(chuàng)建一個(gè)刪除查詢，可從列在 FROM 子句之中的一個(gè)或多個(gè)表中刪除記錄，且該子句滿足 WHERE 子句中的條件，可以使用DELETE刪除多個(gè)記錄。
語法：DELETE [table.*] FROM table WHERE criteria
語法：DELETE * FROM table WHERE criteria='查詢的字'
說明：table參數(shù)用于指定從其中刪除記錄的表的名稱。
criteria參數(shù)為一個(gè)表達(dá)式，用于指定哪些記錄應(yīng)該被刪除的表達(dá)式。
可以使用 Execute 方法與一個(gè) DROP 語句從數(shù)據(jù)庫中放棄整個(gè)表。不過，若用這種方法刪除表，將會失去表的結(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兩個(gè)字段的數(shù)據(jù)是完整的
表 B中原來ID,LASTNAME兩個(gè)字段的數(shù)據(jù)是完整的
現(xiàn)在要把表 B中的LASTNAME字段的相應(yīng)的數(shù)據(jù)填入到A表中LASTNAME相應(yīng)的位置。兩個(gè)表中的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ù)庫所在機(jī)器操作系統(tǒng)參數(shù)
exec master..xp_msver
3. 查看數(shù)據(jù)庫啟動的參數(shù)
sp_configure
4. 查看數(shù)據(jù)庫啟動時(shí)間
select convert(varchar(30),login_time,120) from master..sysprocesses where spid=1
查看數(shù)據(jù)庫服務(wù)器名和實(shí)例名
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ù)器時(shí)孤立用戶時(shí),可以用的fix_orphan_user腳本或者LoneUser過程
更改某個(gè)數(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ù)庫下某個(gè)數(shù)據(jù)對象的大小
sp_spaceused @objname
還可以用sp_toptables過程看最大的N(默認(rèn)為50)個(gè)表
查看某數(shù)據(jù)庫下某個(gè)數(shù)據(jù)對象的索引信息
sp_helpindex @objname
還可以用SP_NChelpindex過程查看更詳細(xì)的索引情況
SP_NChelpindex @objname
clustered索引是把記錄按物理順序排列的，索引占的空間比較少。
對鍵值DML操作十分頻繁的表我建議用非clustered索引和約束，fillfactor參數(shù)都用默認(rèn)值。
查看某數(shù)據(jù)庫下某個(gè)數(shù)據(jù)對象的的約束信息
sp_helpconstraint @objname
9.查看數(shù)據(jù)庫里所有的存儲過程和函數(shù)
use @database_name
sp_stored_procedures
查看存儲過程和函數(shù)的源代碼
sp_helptext '@procedure_name'
查看包含某個(gè)字符串@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ù)庫里用戶和進(jìn)程的信息
sp_who
查看SQL Server數(shù)據(jù)庫里的活動用戶和進(jìn)程的信息
sp_who 'active'
查看SQL Server數(shù)據(jù)庫里的鎖的情況
sp_lock
進(jìn)程號1--50是SQL Server系統(tǒng)內(nèi)部用的,進(jìn)程號大于50的才是用戶的連接進(jìn)程.
spid是進(jìn)程編號,dbid是數(shù)據(jù)庫編號,objid是數(shù)據(jù)對象編號
查看進(jìn)程正在執(zhí)行的SQL語句
dbcc inputbuffer ()
推薦大家用經(jīng)過改進(jìn)后的sp_who3過程可以直接看到進(jìn)程運(yùn)行的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í)行計(jì)劃
在查詢分析器->查詢->顯示估計(jì)的評估計(jì)劃(D)-Ctrl-L 或者點(diǎn)擊工具欄里的圖形
文本方式顯示查詢執(zhí)行計(jì)劃
set showplan_all {on | off}
set showplan_text { on | off }
set statistics profile { on | off }
13.出現(xiàn)不一致錯(cuò)誤時(shí)，NT事件查看器里出3624號錯(cuò)誤，修復(fù)數(shù)據(jù)庫的方法
先注釋掉應(yīng)用程序里引用的出現(xiàn)不一致性錯(cuò)誤的表，然后在備份或其它機(jī)器上先恢復(fù)然后做修復(fù)操作
alter database [@error_database_name] set single_user
修復(fù)出現(xiàn)不一致錯(cuò)誤的表
dbcc checktable('@error_table_name',repair_allow_data_loss)
或者可惜選擇修復(fù)出現(xiàn)不一致錯(cuò)誤的小型數(shù)據(jù)庫名
dbcc checkdb('@error_database_name',repair_allow_data_loss)
alter database [@error_database_name] set multi_user
CHECKDB 有3個(gè)參數(shù)：
repair_allow_data_loss 包括對行和頁進(jìn)行分配和取消分配以改正分配錯(cuò)誤、結(jié)構(gòu)行或頁的錯(cuò)誤，以及刪除已損壞的文本對象，這些修復(fù)可能會導(dǎo)致一些數(shù)據(jù)丟失。
修復(fù)操作可以在用戶事務(wù)下完成以允許用戶回滾所做的更改。
如果回滾修復(fù)，則數(shù)據(jù)庫仍會含有錯(cuò)誤，應(yīng)該從備份進(jìn)行恢復(fù)。
如果由于所提供修復(fù)等級的緣故遺漏某個(gè)錯(cuò)誤的修復(fù)，則將遺漏任何取決于該修復(fù)的修復(fù)。
修復(fù)完成后，請備份數(shù)據(jù)庫。
repai*_**st 進(jìn)行小的、不耗時(shí)的修復(fù)操作，如修復(fù)非聚集索引中的附加鍵。
這些修復(fù)可以很快完成，并且不會有丟失數(shù)據(jù)的危險(xiǎn)。
repair_rebuild 執(zhí)行由 repai*_**st 完成的所有修復(fù)，包括需要較長時(shí)間的修復(fù)（如重建索引）。
執(zhí)行這些修復(fù)時(shí)不會有丟失數(shù)據(jù)的危險(xiǎn)。

本站是提供個(gè)人知識管理的網(wǎng)絡(luò)存儲空間，所有內(nèi)容均由用戶發(fā)布，不代表本站觀點(diǎn)。請注意甄別內(nèi)容中的聯(lián)系方式、誘導(dǎo)購買等信息，謹(jǐn)防詐騙。如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請點(diǎn)擊一鍵舉報(bào)。

轉(zhuǎn)藏 分享

QQ空間 QQ好友新浪微博微信

獻(xiàn)花（0） +1

來自：我的小園地 > 《c#》

舉報(bào)/認(rèn)領(lǐng)