??1?--------------------------------對(duì)數(shù)據(jù)庫(kù)操作-----------------------------------
??2?--建立數(shù)據(jù)庫(kù)
??3?create?database?Test
??4?
??5?--使用數(shù)據(jù)庫(kù)
??6?use?Test
??7?
??8?--刪除數(shù)據(jù)庫(kù)
??9?DROP?DATABASE?Test
?10?
?11?--------------------------------對(duì)表操作----------------------------------------
?12?--建立表
?13?create?table?Person
?14?(
?15?????ID?int?primary?key?not?null,--編號(hào)
?16?????Name?varchar(10)?not?null,--姓名
?17?????Birth?datetime?not?null,--生日
?18?????Address?varchar(50),--地址
?19?????Phone?varchar(50),--電話
?20?)
?21?create?table?person1
?22?(
?23?????pID?int?primary?key?not?null,--編號(hào)
?24?????Sex?varchar(10)?not?null,--性別
?25?)
?26?
?27?--創(chuàng)建新表,并從原有表中復(fù)制指定內(nèi)容
?28?create?table?person2
?29?(
?30?????pID?varchar(2),
?31?????pName?varchar(30),
?32?????pSex?varchar(2),
?33?)
?34?insert?into?person2?select?*?from?person?where?personSex='男'
?35?
?36?--刪除表
?37?drop?table?person
?38?
?39?--增加列
?40?alter?table?person?add?State?varchar(10)
?41?
?42?--刪除列
?43?alter?table?person?drop?column?State
?44?
?45?--修改列的數(shù)據(jù)類型
?46?alter?table?person?alter?column?State?varchar(20)
?47?
?48?--增加行
?49?insert?into?Person?values(1,'張超',1983-6-28,'北京','010-88256483')
?50?insert?into?Person?values(2,'李華',1983-5-20,'北京','010-88462582')
?51?insert?into?Person?values(3,'王梅',1983-2-23,'梅州','0753-6570211')
?52?insert?into?person1?values(1,'男')
?53?insert?into?person1?values(2,'男')
?54?insert?into?person1?values(3,'女')
?55?
?56?--修改行
?57?update?person?set?Name='張英'?where?ID=1
?58?
?59?--刪除行
?60?delete?person?where?ID=2
?61?
?62?--刪除表中所有數(shù)據(jù)
?63?truncate?table?person
?64?
?65?--簡(jiǎn)單查詢
?66?select?*?from?person
?67?select?*?from?person1
?68?
?69?--條件查詢
?70?select?*?from?person?where?ID=1
?71?select?*?from?person?where?ID=1?and?ID?is?not?null
?72?select?*?from?person?where?ID=1?or?ID?is?null
?73?select?*?from?person?where?ID?is?not?null
?74?select?*?from?person?where?ID?between?0?and?3?and?contains(Address,'北京')?--從屬運(yùn)算
?75?select?*?from?person?where?ID>0?and?ID<=3
?76?select?*?from?person?where?Address?like?'北_'??--通配一個(gè)字符
?77?select?*?from?person?where?ID?in(1,2,3)???--從屬運(yùn)算
?78?select?*?from?person?where?Address?like?'北%'??--通配多個(gè)字符
?79?select?*?from?person?where?ID?like?'%'
?80?select?count(*)?from?person?where?ID=3??--返回滿足條件的行的個(gè)數(shù)
?81?
?82?--聯(lián)合查詢
?83?select?*?from?person?where?ID>0?union?select?*?from?person?where?ID<=3
?84?select?(p.Name,p1.Sex)?from?(person?p,person?p1)--給不同的表取別名并聯(lián)合查詢指定列
?85?select?*?from?operator?intersect?select?*?from?operator2?--intersect相交,返回兩個(gè)表中共有的行
?86?
?87?--表關(guān)聯(lián)查詢
?88?select?distinct?*?from?person?as?p????--distinct防止重復(fù)
?89?join?person1?as?p1
?90?on?ID=pID
?91?where?ID=1
?92?
?93?--限制查詢
?94?select?top?10?percent?with?ties?*?from?person?order?by?ID?asc--升序
?95?select?top?10?percent?with?ties?*?from?person?order?by?ID?desc--降序
?96?select?top?10?*?from?person?order?by?ID?asc
?97?select?*?from?person?group?by?ID?having?ID<3??--進(jìn)行分組查詢
?98?
?99?--對(duì)查詢進(jìn)行輸出控制
100?select?ID?as?編號(hào),Name?as?姓名,Birth?as?生日,Address?as?地址,Phone?as?電話
101?from?person?order?by?ID?desc
102?
103?--------------------------------對(duì)視圖操作----------------------------------------
104?--可以象操作表一樣操作視圖,但應(yīng)少用insert?update?delete等語(yǔ)句
105?
106?--創(chuàng)建視圖
107?create?view?myView
108?as
109?select?*?from?Orders
110?
111?--修改視圖
112?alter?view?myView
113?as
114?select?*?from?Orders
115?
116?--刪除視圖
117?drop?view?myView
118?
119?--查看視圖
120?select?top?10?*?from?myView?where?OrderID>=10250--顯示頭10條記錄
121?
122?-----------------------------對(duì)索引的操作--------------------------------------
123?use?test
124?select?*?from?person
125?
126?--創(chuàng)建索引
127?create?index?index_person?on?person(ID)
128?
129?--創(chuàng)建unique類型的索引
130?create?unique?index?index_person?on?person(ID)
131?
132?--刪除索引
133?drop?index?person.index_person?
134?
135?-----------------------------對(duì)存儲(chǔ)過(guò)程的操作----------------------------------
136?--建立存儲(chǔ)過(guò)程
137?create?procedure?dbo.GetDataById
138?(
139?????@id?int
140?)
141?as
142?select?*?from?T_Person?where?id=@id????
143?return
144?
145?--修改存儲(chǔ)過(guò)程
146?alter?procedure?dbo.GetDataById
147?(
148?????@id?int
149?)
150?as
151?select?*?from?T_Person?where?id=@id????
152?return
153?
154?--執(zhí)行存儲(chǔ)過(guò)程
155?exec?GetDataById?2
156?
157?--刪除存儲(chǔ)過(guò)程
158?drop?procedure?dbo.GetDataById

轉(zhuǎn)載于:https://www.cnblogs.com/anllin/articles/2007115.html

總結(jié)

以上是生活随笔為你收集整理的数据库常用的基本操作的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。