SQLServer語(yǔ)句建表建庫(kù)建存儲(chǔ)過(guò)程建視圖ATM取款機(jī)示例

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

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

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

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

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

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

7、1<>1法二：select top 0 * into b from a10、拷貝表(拷貝數(shù)據(jù),源表名：a 目標(biāo)表名：b) (Access可用)insert into b(a, b, c) select d,e,f from b;11、跨數(shù)據(jù)庫(kù)之間表的拷貝(具體數(shù)據(jù)使用絕對(duì)路徑) (Access可用)insert into b(a, b, c) select d,e,f from b in 具體數(shù)據(jù)庫(kù) 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限制查詢數(shù)據(jù)范圍時(shí)包括了邊界值,not between不包括select * from table1 where time between time1 and time2select a,b,c, from table1 where a not between 數(shù)值1 and 數(shù)值214、in 的使用方法select * from table1 where a n

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

10、'湖北省麻城市乘馬崗鎮(zhèn)')set i=i+1end18、隨機(jī)取出條數(shù)據(jù)select top 10 * from spt_values order by newid() 19、修改指定列的數(shù)據(jù)語(yǔ)法: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、刪除表中所有數(shù)據(jù),如有約束則不能刪除 TRUNCATE TABLE UsersTable22、條件查詢加排序語(yǔ)法: SELECT <列>,<列>,. FROM <表> WHERE <查詢條件> ORDER BY <排序的列> ASC 或 DESC23、表中兩個(gè)字段合為一個(gè)字段SELECT FirstName+.+LastName as 姓名From UsersTable24、查詢出不

12、等于某項(xiàng)的數(shù)據(jù)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 查詢?cè)谀硞€(gè)范圍中的數(shù)據(jù)SELECT * FROM UsersTable Where age BETWEEN 18 AND 2128、查詢不在某個(gè)范圍內(nèi)的數(shù)據(jù)SELECT * FROM UsersTable

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

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

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

16、IN 或者 LEFT JOIN 左外連接（左連接）：結(jié)果集幾包括連接表的匹配行，也包括左連接表的所有行。(2) 右外聯(lián)接: RIGHT OUTER JOIN 或者 LEFT JOIN 右外連接(右連接)：結(jié)果集既包括連接表的匹配連接行，也包括右連接表的所有行。(3) 完整聯(lián)接: FULL OUTER JOIN 或者 FULL JOIN全外連接：不僅包括符號(hào)連接表的匹配行，還包括兩個(gè)連接表中的所有記錄。33、內(nèi)聯(lián)(1) 在Where 子句中指定聯(lián)接條件. (表名1：StudTable 表名2：ScoreTable)語(yǔ)法: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、外聯(lián)接(1) 左外聯(lián)接: 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) 右外聯(lián)接: RIGHT OUTER JOIN 或者 LEFT JOINSELECT StudTable.Name,ScoreTable.ScoreId,StudTable.Score FROM StudTable RIGHT OUTER JOIN ScoreTable ON StudTable.Id = ScoreTable.Id35、兩張關(guān)聯(lián)表，刪除主表中已經(jīng)在副表中沒(méi)有的信息delete from table1 where not exists ( sele

19、ct * from table2 where table1.field1=table2.field1 )36、四表聯(lián)查問(wèn)題：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開(kāi)始

20、時(shí)間,getdate()>538、一條sql 語(yǔ)句搞定數(shù)據(jù)庫(kù)分頁(yè)select top 10 b.* from (select top 20 主鍵字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主鍵字段 = a.主鍵字段 order by a.排序字段39、選擇在每一組b值相同的數(shù)據(jù)中對(duì)應(yīng)的a最大的記錄的所有信息(類似這樣的用法可以用于論壇每月排行榜,每月熱銷(xiāo)產(chǎn)品分析,按科目成績(jī)排名,等等.)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 中的行并消除所有重復(fù)行而派生出一個(gè)結(jié)果表(select a from tableA ) except (select a from tableB) except (select a from tableC)ß-高級(jí)查詢-à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,'湖北省荊門(mén)市')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、幾個(gè)高級(jí)查詢運(yùn)算詞(1) UNION 運(yùn)算符UNION 運(yùn)算符通過(guò)組合其他兩個(gè)結(jié)果表（例如 TABLE1 和 TABLE2）并消去表中任何重復(fù)行而派生出一個(gè)結(jié)果表。當(dāng) ALL 隨 UNION 一起使用時(shí)（即 UNION ALL），不消除重復(fù)行。兩種情況下，派生表的每一行不是來(lái)自 TABLE1 就是來(lái)自 TABLE2。(2) EXCEPT 運(yùn)算符EXCEPT 運(yùn)算符通過(guò)包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重復(fù)行而派

25、生出一個(gè)結(jié)果表。當(dāng) ALL 隨 EXCEPT 一起使用時(shí) (EXCEPT ALL)，不消除重復(fù)行。 (3) INTERSECT 運(yùn)算符INTERSECT 運(yùn)算符通過(guò)只包括 TABLE1 和 TABLE2 中都有的行并消除所有重復(fù)行而派生出一個(gè)結(jié)果表。當(dāng) ALL 隨 INTERSECT 一起使用時(shí) (INTERSECT ALL)，不消除重復(fù)行。注：使用運(yùn)算詞的幾個(gè)查詢結(jié)果行必須是一致的。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、顯示文章、提交人和最后回復(fù)時(shí)間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 條件語(yǔ)句語(yǔ)法: IF (條件)BEGIN語(yǔ)句或語(yǔ)句塊ENDELSEBEGIN語(yǔ)句或語(yǔ)句塊END 示例:DECLARE myavg floatSELECT myavg=AVG(writte

27、nExam) from stuMarksTablePrint 本班平均成績(jī)+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循環(huán)語(yǔ)句誤法:WHILE(條件)語(yǔ)句或語(yǔ)句塊BREAK 示例:DECLARE k intWHILE(1=1) -條件永遠(yuǎn)成立

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

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

30、RE stuName='孝麗')示例1:SELECT stuName FROM stuInfoTable INNER JOIN stuMarksTable-查出成績(jī)?yōu)榉值耐瑢W(xué) 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 的用法 -判斷要?jiǎng)?chuàng)建的數(shù)據(jù)是否存在,如果存在則刪除重建IF EXISTS (SELECT * FROM sysDatabases WHERE name=studDB)DROP DATABASE studDB CREATE DATABASE studDB(2)-NOT EXISTS 的用法 -根據(jù)考試難度加分IF NOT

32、EXISTS(SELECT * FROM stuMarksTable WHERE WHERE WrittenExam>60 AND LabExam >60) BEGIN Print 本次考試無(wú)人通過(guò),每人加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 -開(kāi)始事物(2),COMMIT TRANSACTION -提交事物(3),ROLLBACK TRANSACTION -回滾事物(4),SET IMPLICIT_ TRANSACTIONS ON -隱性事物52、索引(1),UNIQUE -唯一索引(可選)(2),CLUSTERED , NONCLUSTERED -聚集索引還是非聚集索引(可選)(3),FILLFACTOR -表示填充因子,(0100)六,ATM自動(dòng)取款機(jī)(事物,索引,) 1,ATM的創(chuàng)建

34、use mastergoexec xp_cmdshell 'mkdir d:project'if exists(select * from sysdatabases where name = 'ATM')drop database ATMgo-建數(shù)據(jù)庫(kù)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-手動(dòng)插入數(shù)據(jù)測(cè)試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-常規(guī)業(yè)務(wù)模擬-修改密碼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'-統(tǒng)計(jì)贏余declare inmoney money,outmoney moneyselect inmoney = sum(transMoney) from transinfo where transType = '存入'select outmoney = sum(transMoney) from transinfo where transType = '支取'print'銀行流通余額總計(jì)為：'+ convert(varchar(20),in

45、money-outmoney)+'RMB'print'贏利結(jié)算為：'+ convert(varchar(20),outmoney*0.008-inmoney*0.003)-查詢本周開(kāi)戶的卡號(hào)信息print '本周開(kāi)戶的卡號(hào)有：'declare week int,monday datetimeselect week = datepart(dw,getdate()-獲取現(xiàn)在時(shí)間的星期編號(hào)，注意：星期天為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-查詢本月交易金額最高的卡號(hào)declare cardid varchar(20)select distinct cardid = cardID from transinfo where transMoney = (select max(transmoney) from transinfo )print '本月交易金額最高的卡號(hào)

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

48、erinfo.customerID = cardinfo.customerID where balance < 200 -在卡號(hào)上創(chuàng)建重復(fù)的索引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-創(chuàng)建視圖if exists(select * from sysobjects w

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

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

51、ype,交易金額=transMoney,交易時(shí)間=transDate,備注=remarkfrom transinfogo-產(chǎn)生隨機(jī)的不同卡號(hào)的存儲(chǔ)過(guò)程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-添加新帳戶的存儲(chǔ)過(guò)程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