1、 Sql Server一，數據庫基礎概念1，Microsoft SQL Server 有4個系統數據庫(1),Master 數據庫 ，存儲服務器的基本信息。(2),Tempdb 數據庫，存儲臨時數據。(3),Model 數據庫，用于新建數據庫模板。(4),Msdb 數據庫，支持數據庫的管理功能。2，主鍵 (Primary Key) 和外鍵 (Foreign Key)。3，T-SQL的組成部分(1),DML (數據操作語言)，用于添加(INSERT)，修改(UPDATE)，查詢(SELECT)，刪除(DELETE)。(2),DCL ( 數據控制語言)，用于存取許可，存取權限，(GRANT，REV

2、OKE)。(3),DDL (數據庫定義語言)，用于建數據庫，數據庫對象和其列。如：(CREATE TABLE，CREATE VIEW 及 DROP TABLE)(4),除此外還包括，變量聲明，內嵌函數等其它命令。二，通配符 通配符 解釋 示例_一個字符Where A Like C_%任意長度字符串Where B Like CO_%括號內所指定范圍內的一個字符串Where C Like 9W01-2不在括號內所指定范圍內的任意一個字符Where C LIKE like 9w01-2三，邏輯表達式1，T-SQL 中的邏輯運算符 AND ，OR 和 NOT。2，AND 和 OR 是運算符連接條件。N

3、OT是否定條件。3，AND 連接條件，并且權當兩個條件為真時，才成立（返回TRUE）。4，OR 連接條件，但只要其中一個成立（返回 TRUE）。例: 采購時，付款方式要求是信用卡，只能用（牡丹卡，龍卡，陽光卡）NOT (付款方式 = 信用卡) OR (信用卡 IN (牡丹卡, 龍卡, 陽光卡)四，全局變量1,SQL Server 全局變量使用兩個 標志為前綴，局部變量則用一個 全局變量 變量 說明使用方式(或其它方式)ERROR最一個T-SQL 錯誤的錯誤號SELECT ERRORIDENTITY最后一次插入的標識值同上或其它方式LANGUAGE當前使用的語言的名稱同上或其它方式MAX_CON

4、NECTIONS可以創建的同時連接的最大數目同上或其它方式ROWCOUNT受上一個SQL語句影的行數同上或其它方式SERVERNAME本地服務器名稱同上或其它方式SERVICENAME該計算機上的SQL服務的名稱同上或其它方式TIMETICKS當前計算機上每一刻度的微秒數同上或其它方式TRANSCOUNT當前連接打開的事務數同上或其它方式VERSIONSQL Server 的版本信息同上或其它方式五，SQL語集合1、創建數據庫CREATE DATABASE database-name2、刪除數據庫drop database dbname3、備份sql server- 創建 備份數據的 devi

5、ceUSE masterEXEC sp_addumpdevice 'disk', 'testBack', 'c:mssql7backupMyNwind_1.dat'- 開始 備份BACKUP DATABASE pubs TO testBack 4、刪除新表drop table tabname5、增加一個列Alter table tabname add column col type注：列增加后將不能刪除。DB2中列加上后數據類型也不能改變，唯一能改變的是增加varchar類型的長度。6、添加主鍵： Alter table tabname add

6、primary key(col)說明：刪除主鍵： Alter table tabname drop primary key(col)7、創建索引：create unique index idxname on tabname(col.) 刪除索引：drop index idxname注：索引是不可更改的，想更改必須刪除重新建。8、創建視圖：create view viewname as select statement刪除視圖：drop view viewname9、復制表(只復制結構,源表名：a 新表名：b) (Access可用)法一：select * into b from a where

7、1<>1法二：select top 0 * into b from a10、拷貝表(拷貝數據,源表名：a 目標表名：b) (Access可用)insert into b(a, b, c) select d,e,f from b;11、跨數據庫之間表的拷貝(具體數據使用絕對路徑) (Access可用)insert into b(a, b, c) select d,e,f from b in 具體數據庫 where 條件例子：.from b in '"&Server.MapPath(".")&"data.mdb"

8、 &"' where.12、在線視圖查詢(表名1：a )select * from (SELECT a,b,c FROM a) T where t.a > 1;13、between的用法,between限制查詢數據范圍時包括了邊界值,not between不包括select * from table1 where time between time1 and time2select a,b,c, from table1 where a not between 數值1 and 數值214、in 的使用方法select * from table1 where a n

9、ot in (值1,值2,值4,值6)15、隨機取出10條數據select top 10 * from tablename order by newid()16、刪除重復記錄Delete from tablename where id not in (select max(id) from tablename group by col1,col2,.)17、循環插入條數據declare i int -變義變量iset i=1while i<101begininsert into Testvalues('胡杰','20','lily21k',

10、'湖北省麻城市乘馬崗鎮')set i=i+1end18、隨機取出條數據select top 10 * from spt_values order by newid() 19、修改指定列的數據語法:UPDATE <表名> SET <列名 = 更新值> WHERE <修改的條件>UPDATE UsersTableSET name = lily WHERE name=huUPDATE UsersTableSET age = age+2 WHERE age < =18UPDATE UserTable SET Sex = 020、DELETE F

11、ROM <表名> WHERE <刪除的條件>DELETE FROM UsersTable WHERE UsName=lily21、刪除表中所有數據,如有約束則不能刪除 TRUNCATE TABLE UsersTable22、條件查詢加排序語法: SELECT <列>,<列>,. FROM <表> WHERE <查詢條件> ORDER BY <排序的列> ASC 或 DESC23、表中兩個字段合為一個字段SELECT FirstName+.+LastName as 姓名From UsersTable24、查詢出不

12、等于某項的數據SELECT Name AS 姓名,Address AS 地址FROM UsersTable Where Address <> 武漢25、查詢出空行 SELECT FROM UsersTable Where Email IS NULL36、LIKE 模糊查詢 SELECT * FROM UserTable Where Uname LIKE 張%27、BETWEEN 查詢在某個范圍中的數據SELECT * FROM UsersTable Where age BETWEEN 18 AND 2128、查詢不在某個范圍內的數據SELECT * FROM UsersTable

13、Where Birthday NOT BETWEEN 1991-08-01 AND 1993-07-0129、使用 IN 列舉值SELECT * FROM UsersTable Where Address IN (武漢市,隨州市,麻城市)30、4個聚合函數(1), SUM 語法: SELECT SUM(列) FROM Table Where <條件>SELECT SUM(age) AS 年齡 FROM UsersTable Where name = lily21k(2)AVG 語法: SELECT AVG(列) FROM Table Where <條件>SELECT A

14、VG(Score) AS 分數 FROM Where Score >= 60(3) MAX 和 MIN (可用于計算日期) SELECT AVG(Score) AS 平均分數,MAX(Score) AS 最高分,MIN(Score) AS 最低分,SUM(Score) AS 總分 FROM StudTable Where Score >= 60(4)COUNT 用于數字和字符類型的列(計算出所有列)語法:SLECT COUNT(*) FROM <表> Where <條件> SELECT COUNT(*) as aaa FROM StudTable Score

15、 where Score >= 6031、分組查詢(1)Group By 分組查詢語法: SELECT * FROM Table Group By <條件>,<條件>SELECT AVG(Score) AS 平均分 FROM StudTable Group By CourseID(2)HAVING 分組篩選SELECT Id AS 學員編號, AVG(Score) AS 平均分 FROM StudTableGroup By Id, CourseID HAVING Count(Score) > 132、多表連接查詢(1) 左外聯接: LEFT OUTER JO

16、IN 或者 LEFT JOIN 左外連接（左連接）：結果集幾包括連接表的匹配行，也包括左連接表的所有行。(2) 右外聯接: RIGHT OUTER JOIN 或者 LEFT JOIN 右外連接(右連接)：結果集既包括連接表的匹配連接行，也包括右連接表的所有行。(3) 完整聯接: FULL OUTER JOIN 或者 FULL JOIN全外連接：不僅包括符號連接表的匹配行，還包括兩個連接表中的所有記錄。33、內聯(1) 在Where 子句中指定聯接條件. (表名1：StudTable 表名2：ScoreTable)語法:SELECT 表1.列名,表2.列名,. FROM 表1,表2,. Wher

17、e <條件>SELECT StudTable.Name,ScoreTable.Score FROM StudTable,ScoreTableWhere StudTable.Id = ScoreTable.Id (2) 在FROM 子句中使用 JOIN.ON SELECT S.Name,C.Score FROM StudTable AS S INNER JOIN ScoreTable AS C ON (S.Id = C.Id)34、外聯接(1) 左外聯接: LEFT OUTER JOIN 或者 LEFT JOIN SELECT S.Name, C.ScoreId,C.Score FR

18、OM StudTable AS SLEFT OUTER JOIN ScoreTable AS C ON S.Id = C.Id(2) 右外聯接: RIGHT OUTER JOIN 或者 LEFT JOINSELECT StudTable.Name,ScoreTable.ScoreId,StudTable.Score FROM StudTable RIGHT OUTER JOIN ScoreTable ON StudTable.Id = ScoreTable.Id35、兩張關聯表，刪除主表中已經在副表中沒有的信息delete from table1 where not exists ( sele

19、ct * from table2 where table1.field1=table2.field1 )36、四表聯查問題：SELECT * FROM talbe1 LEFT INNER JOIN table2 ONtable1.Id = table2.Id RIGHT JOIN table3 ON table1.Id = table3.Id INNER JOINtable4 ON table1.Id = table4.Id Where <條件>37、日程安排提前五分鐘提醒 select * from 日程安排 where datediff('minute',f開始

20、時間,getdate()>538、一條sql 語句搞定數據庫分頁select top 10 b.* from (select top 20 主鍵字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主鍵字段 = a.主鍵字段 order by a.排序字段39、選擇在每一組b值相同的數據中對應的a最大的記錄的所有信息(類似這樣的用法可以用于論壇每月排行榜,每月熱銷產品分析,按科目成績排名,等等.)select a,b,c from tablename ta where a=(select max(a) from tablename tb w

21、here tb.b=ta.b)40、包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重復行而派生出一個結果表(select a from tableA ) except (select a from tableB) except (select a from tableC)ß-高級查詢-àinsert into stuInfoTablevalues('s100','張秋麗','女',18,1,'湖北省武漢市')insert into stuInfoTablevalues('

22、;s101','孝麗','女',18,2,'湖北省麻城市')insert into stuInfoTablevalues('s102','雨漓','男',20,3,'湖北省麻城市')insert into stuInfoTablevalues('s103','小白','女',18,5,'湖北省武漢市')insert into stuInfoTablevalues('s104','lily&

23、#39;,'男',20,6,'湖北省荊門市')insert into stuMarksTablevalues('101','s100',65,80)insert into stuMarksTablevalues('102','s101',85,56)insert into stuMarksTablevalues('103','s100',65,75)insert into stuMarksTablevalues('104','s103'

24、,80,85)insert into stuMarksTablevalues('105','s104',60,55)41、幾個高級查詢運算詞(1) UNION 運算符UNION 運算符通過組合其他兩個結果表（例如 TABLE1 和 TABLE2）并消去表中任何重復行而派生出一個結果表。當 ALL 隨 UNION 一起使用時（即 UNION ALL），不消除重復行。兩種情況下，派生表的每一行不是來自 TABLE1 就是來自 TABLE2。(2) EXCEPT 運算符EXCEPT 運算符通過包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重復行而派

25、生出一個結果表。當 ALL 隨 EXCEPT 一起使用時 (EXCEPT ALL)，不消除重復行。 (3) INTERSECT 運算符INTERSECT 運算符通過只包括 TABLE1 和 TABLE2 中都有的行并消除所有重復行而派生出一個結果表。當 ALL 隨 INTERSECT 一起使用時 (INTERSECT ALL)，不消除重復行。注：使用運算詞的幾個查詢結果行必須是一致的。42、子查詢(表名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

26、,2,3)43、顯示文章、提交人和最后回復時間select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b44、局部變量的聲名與屬值聲名 Declare i int屬值 Set i = 1 或 select i = 145、IF-ELSE 條件語句語法: IF (條件)BEGIN語句或語句塊ENDELSEBEGIN語句或語句塊END 示例:DECLARE myavg floatSELECT myavg=AVG(writte

27、nExam) from stuMarksTablePrint 本班平均成績+Convert(varhcar(5),myavg)IF(myavg > 70)BEGINPrint 全三名SELECT TOP 3 * FROM stuMarksTable ORDER BY writtenExam DESC ENDELSEBEGINPrint 后三名SELECT TOP 3 * FROM stuMarksTableORDER BY writtenExamEND46、WHIL循環語句誤法:WHILE(條件)語句或語句塊BREAK 示例:DECLARE k intWHILE(1=1) -條件永遠成立

28、BEGIN -統計不級格的人數SELECT k=COUNT(*) FROM stuMarksTable writtenExam<60IF(k > 0)-每人加2分UPDATE stuMarksTable SET writtenExam= writtenExam+2ELSEBREAK -退出ENDPrint 加分如下SELECT * FROM stuMarksTable 47、CASE 多分支語句語法:CASEWHEN 條件1 THEN 結果1WHEN 條件2 THEN 結果2ELSE 其它結果END 示例:Print ABC星級表SELECT stuName,成績=CASEWHEN

29、 writtenExam < 60 THEN DWHEN writtenExam between 60 AND 79 THEN CWHEN writtenExam between 80 AND 89 THEN BELSE AENDFROM stuMarksTable 48、WHERE 子查詢 和 表連接查詢語法1:SELECT 字段或其它的. FROM 表1 WHERE 字段1 > (子查詢)示例1:SELECT * FROM stuInfoTable-篩選比孝麗年齡大的學生 WHERE stuAge>(SELECT stuAge FROM stuMarksTable WHE

30、RE stuName='孝麗')示例1:SELECT stuName FROM stuInfoTable INNER JOIN stuMarksTable-查出成績為分的同學 ON stuInfoTable.stuId = stuMarksTable.stuId WHERE WrittenExam =6049、IN 和 NOTIN 查詢(1) -IN查詢SELECT stuName FROM stuInfoTableWHERE stuId IN (SELECT stuId FROM stuMarksTable WHERE WrittenExam =60)(2)-NOT IN查詢

31、SELECT stuName FROM stuInfoTableWHERE stuId NOT IN (SELECT stuId FROM stuMarksTable WHERE WrittenExam =60)50、EXISTS 和 NOT EXISTS(1)-EXISTS 的用法 -判斷要創建的數據是否存在,如果存在則刪除重建IF EXISTS (SELECT * FROM sysDatabases WHERE name=studDB)DROP DATABASE studDB CREATE DATABASE studDB(2)-NOT EXISTS 的用法 -根據考試難度加分IF NOT

32、EXISTS(SELECT * FROM stuMarksTable WHERE WHERE WrittenExam>60 AND LabExam >60) BEGIN Print 本次考試無人通過,每人加3分UPDATE stuMarksTable SET WrittenExam = WrittenExam+3, LabExam = LabExam+3SELECT * FROM stuMarksTable ENDELSEBEGINPrint 本次考的一般,每人加2分UPDATE stuMarksTable SET WrittenExam = WrittenExam+2, LabE

33、xam = LabExam+2SELECT * FROM stuMarksTableEND51、事物(1),BEGIN TRANSACTION -開始事物(2),COMMIT TRANSACTION -提交事物(3),ROLLBACK TRANSACTION -回滾事物(4),SET IMPLICIT_ TRANSACTIONS ON -隱性事物52、索引(1),UNIQUE -唯一索引(可選)(2),CLUSTERED , NONCLUSTERED -聚集索引還是非聚集索引(可選)(3),FILLFACTOR -表示填充因子,(0100)六,ATM自動取款機(事物,索引,) 1,ATM的創建

34、use mastergoexec xp_cmdshell 'mkdir d:project'if exists(select * from sysdatabases where name = 'ATM')drop database ATMgo-建數據庫create database ATMon(name = 'ATM_data',filename = 'd:projectATM_data.mdf',size = 10mb,filegrowth = 1mb,maxsize = 50mb)log on(name = 'ATM_

35、log',filename = 'd:projectATM_log.ldf',size = 10mb,filegrowth = 1mb,maxsize = 50mb)gouse ATMgoset nocount onif exists(select * from sysobjects where name = 'transinfo')drop table transinfoif exists(select * from sysobjects where name = 'cardinfo')drop table cardinfoif exi

36、sts(select * from sysobjects where name = 'userinfo')drop table userinfogo-建用戶信息表create table userinfo(customerID int identity(100000,1) primary key not null,customerName varchar(10) not null,PID bigint unique(PID) check(len(PID) = 15 or len(PID) = 18) not null,telephone varchar(20) check(te

37、lephone like '%-_' or len(telephone) = 11) not null,address varchar(30)-建銀行卡信息表create table cardinfo(cardID char(19) primary key check(cardID like '1010 3576 _ _') not null,curType varchar(10) default('RMB') not null,savingType varchar(8) default('活期') check(savingTyp

38、e in ('活期','定活兩便','定期') not null,openDate smalldatetime default(getdate() not null,openMoney money check(openMoney >= 1) not null,balance money check(balance >= 1) not null,pass int default('888888') check(len(pass)=6) not null,IsReportLoss char(2) default('

39、否') check(IsReportLoss in ('是','否') not null,customerID int not null)-建交易信息表create table transinfo(transDate smalldatetime default(getdate() not null,cardID char(19) not null,transType char(4) check(transType in ('存入','支取') not null,transMoney money check(transMon

40、ey > 0) not null,remark varchar(30)go-添加外鍵約束if exists(select * from sysobjects where name = 'FK_cardID')alter table transinfo drop constraint FK_cardIDif exists(select * from sysobjects where name = 'FK_customerID')alter table cardinfo drop constraint FK_customerIDalter table tran

41、sinfo addconstraint FK_cardID foreign key (cardID) references cardinfo(cardID)alter table cardinfo addconstraint FK_customerID foreign key (customerID) references userinfo(customerID)goexec sp_helpconstraint cardinfoexec sp_helpconstraint transinfo-手動插入數據測試insert into userinfo(customerName,PID,telep

42、hone,address)select '雨漓','0000214101965','#39;,'湖北麻城市' unioninsert into cardinfo(cardID,savingType,openMoney,balance,customerID)select '1010 3576 1234 5678','活期',1,1,'100000' unionselect '1010 3576 0000 1111','定期',1000

43、,1000,'100001'select * from userinfoselect * from cardINFogo-常規業務模擬-修改密碼update cardinfo set pass = '123456' where cardID = '1010 3576 1234 5678'update cardinfo set pass = '123123' where cardID = '1010 3576 0000 1111'-銀行卡掛失update cardinfo set IsReportLoss = 

44、9;是' where cardID = '1010 3576 0000 1111'-統計贏余declare inmoney money,outmoney moneyselect inmoney = sum(transMoney) from transinfo where transType = '存入'select outmoney = sum(transMoney) from transinfo where transType = '支取'print'銀行流通余額總計為：'+ convert(varchar(20),in

45、money-outmoney)+'RMB'print'贏利結算為：'+ convert(varchar(20),outmoney*0.008-inmoney*0.003)-查詢本周開戶的卡號信息print '本周開戶的卡號有：'declare week int,monday datetimeselect week = datepart(dw,getdate()-獲取現在時間的星期編號，注意：星期天為1if week = 1select * from cardinfo where (opendate between getdate()-7 and

46、getdate()elsebeginselect monday = dateadd(dd,-week+1,getdate()select * from cardinfo where (opendate between monday and getdate()end-查詢本月交易金額最高的卡號declare cardid varchar(20)select distinct cardid = cardID from transinfo where transMoney = (select max(transmoney) from transinfo )print '本月交易金額最高的卡號

47、是：'+cardid-查詢掛失帳號的客戶信息select 客戶姓名=customerName,客戶帳號=customerID,生分證號=PID,聯系電話=telephone,客戶地址=addressfrom userinfo where customerID in (select customerID from cardinfo where IsReportLoss = '是')-催款提醒select 客戶姓名=customerName,聯系電話=telephone,帳上余額=balance from userinfo inner join cardinfo on us

48、erinfo.customerID = cardinfo.customerID where balance < 200 -在卡號上創建重復的索引if exists(select name from sysindexes where name = 'IX_cardID')drop index transinfo.IX_cardIDgocreate nonclustered index IX_cardID on transinfo(cardID) GOsp_helpindex transinfo-創建視圖if exists(select * from sysobjects w

49、here name = 'view_Userinfo')drop view view_Userinfogocreate view view_Userinfoasselect 客戶編號 = customerID,客戶姓名 = customerName,客戶生份證 = PID,客戶電話 = telephone,客戶地址 = address from Userinfogoif exists(select * from sysobjects where name = 'view_cardinfo')drop view view_cardinfogocreate view

50、 view_cardinfoasselect 卡號 = cardID,金額類型=curType,存款類型=savingType,開戶時間=opendate,余額=balance,密碼=pass,是否掛失=isreportloss,客戶編號=customerIDfrom cardinfogoif exists(select * from sysobjects where name = 'view_transinfo')drop view view_transinfogocreate view view_transinfoasselect 卡號=cardID,交易類型=transT

51、ype,交易金額=transMoney,交易時間=transDate,備注=remarkfrom transinfogo-產生隨機的不同卡號的存儲過程if exists(select * from sysobjects where name = 'proc_randCardID')drop procedure proc_randCardIDgocreate procedure proc_randCardIDcardID varchar(20) outputasdeclare r numeric(15,8)select r = rand(datepart(mm,getdate()

52、*100000)+(datepart(ss,getdate()*1000)+datepart(ms,getdate()select cardID = '1010 3576 '+substring(convert(varchar(20),r),3,4)+' '+substring(convert(varchar(20),r),7,4)go-添加新帳戶的存儲過程if exists(select * from sysobjects where name = 'proc_addnewUser')drop procedure proc_addnewUser

53、gocreate procedure proc_addnewUser Username varchar(10), Upid bigint, Utel varchar(20), openmoney money, pass int = '888888', Uaddress varchar(30) = null, savType varchar(10) = '活期', curType varchar(10) = 'RMB' asset nocount ondeclare mycardID varchar(20)exec proc_randCardID mycardID outputwhile exists(select * from cardinfo where cardID = mycardID) exec proc_randCardID mycardID output insert into userinfo (customerName,PID,telephone,address)values (Username,Upid,Utel,Uaddress)insert into cardi