SqlServer基礎文法（二）

標籤：

先看一下腰實現的功能：

一：建立資料庫

/* 檢查資料庫是否存在，如果存在，刪除此資料庫 */if exists(select * from sysdatabases where name=‘bankDB‘)  drop database bankDB go /*建立資料庫bankDB*/ create database bankDB on (   name=‘bankDB_data‘,   filename=‘d:\bank\bankDB.mdf‘,   size=10,   --增長的速度   filegrowth=15%   )   log on   (   --記錄檔   name=‘bankDB_log‘,   filename=‘d:\bank\bankDB_log.ldf‘,   size=5,   filegrowth=15%   )   

 

二：建立表的語句：

    /* 建立表*/      use bankDB    go   create table userInfo   --使用者資訊表   (    customerID int identity(1,1),    customerName char(8) not null,    PID  char(18) not null,    telephone char(13) not null,    address varchar(50)    )        go        create table cardInfo  --銀行卡資訊表    (     cardID char(19) not null,     curType char(5) not null,     savingType char(8) not null,     openDate datetime not null,     openMoney money not null,     balance money not null,     pass char(6) not null,     IsReportLoss bit not null,     customerID  int not null     )     go         create table  transInfo  --交易資訊表    (      transDate  datetime not null,       transType  char(4) not null,     cardID  char(19) not null,     transMoney money not null,     remark text     )     go          

 三：對錶添加約束

     /* 為userInfo表添加約束      customerID(顧客編號):自動編號（識別欄位），從1開始，主鍵      PID(社會安全號碼):  只能是18位或15位，社會安全號碼唯一約束       telephone(聯絡電話): 格式為xxxx-xxxxxxxx或手機號13位        */          alter table userInfo     add       constraint pk_customerID primary key(customerID),      constraint chk_PID check(len(PID)=18 or len(PID)=15),      constraint uq_PID unique(PID),      constraint chk_telephone check(telephone like ‘[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]‘                                     or  len(telephone)=13)      go                         /*  cardInfo 表的約束     cardID卡號必填，主健，銀行的卡號規則和電話號碼一樣，一般前8位代表特殊含義，       如某總行某支行等。假定該行要求其營業廳的卡號格式為：1010 3576 xxxx xxx開始,每4位號碼後有空格，       卡號一般是隨機產生。curType貨幣種類必填，預設為RMBsavingType存款類型活期/定活兩便/定期openDate開戶日期必填，預設為系統當前日期openMoney開戶金額必填，不低於1元balance餘額必填，不低於1元,否則將銷戶pass密碼必填，6位元字，開戶時預設為6個“8”IsReportLoss是否掛失必填，是/否值，預設為”否”customerID顧客編號外鍵，必填，表示該卡對應的顧客編號，一位顧客允許辦理多張卡號*/alter table cardInfoadd   constraint pk_cardID primary key(cardID),   constraint ck_cardID check(cardID like ‘1010 3576 [0-9][0-9][0-9][0-9] [0-9][0-9][0-9][0-9]‘),   constraint df_curType default ‘RMB‘ for curType,   constraint ck_savingType check(savingType in (‘活期‘,‘定活兩便‘,‘定期‘)),   constraint df_openDate default getdate() for OpenDate,   constraint ck_openMoney  check(openMoney>=1),   constraint ck_balance  check(balance>=1),   constraint ck_pass check(pass like ‘[0-9][0-9][0-9][0-9][0-9][0-9]‘),   constraint df_pass default ‘888888‘ for pass,   constraint df_IsReportLoss default 0 for IsReportLoss,   constraint fk_customerID foreign key(customerID) references userInfo(customerID)go/* transInfo表的約束  transDate 交易日期必填，預設為系統當前日期cardID卡號必填，外健，可重複索引transType 交易類型必填，只能是存入/支取transMoney交易金額必填，大於0remark備忘可選輸入，其他說明*/alter table transInfoadd  constraint  df_transDate default getdate() for transDate,  constraint  ck_transType  check(transType in (‘存入‘,‘支取‘)),  constraint  fk_cardid  foreign key(cardid) references  cardInfo(cardID),  constraint  ck_transMoney  check(transMoney>0)  

 四：插入資料：

/* 張三開戶, 身份證:123456789012345, 電話:010-67898978, 地址:北京海澱   開戶金額:1000  活期   卡號: 1010 3576 1234 5678    李四開戶， 身份證:321245678912345678, 電話:0478-44443333,   開戶金額:1  定期   卡號:1010 3576 1212 1134   */  insert into userInfo(customerName,PID,telephone,address) values(‘張三‘,‘123456789012345‘,‘0102-67898978‘,‘北京海澱‘)  insert into cardInfo(cardID,savingType, openMoney, balance, customerID) values(‘1010 3576 1234 5678‘,‘活期‘, 1000,1000,3)      insert into userInfo(customerName,PID,telephone) values(‘李四‘,‘321245678912345678‘,‘0478-44443333‘)  insert into cardInfo(cardID,savingType, openMoney, balance, customerID) values(‘1010 3576 1212 1134‘,‘定期‘,1,1,2)  select * from userInfo select * from cardInfo select * from transInfo

/*   張三的卡號取款900元，李四的卡號存款5000元   要求儲存交易記錄，以便客戶查詢和銀行業務統計*/ --張三       /*  交易資訊表插入交易資訊 */   insert into transInfo(transType,cardID,transMoney)   values(‘支取‘,‘1010 3576 1234 5678‘,900)     /*更新銀行卡資訊表中的現有餘額*/  update cardInfo   set balance=balance-900  where cardID=‘1010 3576 1234 5678‘    --李四    /*  交易資訊表插入交易資訊 */    insert into transInfo(transType,cardID,transMoney)    values(‘存入‘,‘1010 3576 1212 1134‘,5000)       /*更新銀行卡資訊表中的現有餘額*/   update cardInfo    set balance=balance+5000   where cardID=‘1010 3576 1212 1134‘

 五:常規業務操作：

/*---------修改密碼-----*/--1.張三（卡號為1010 3576 1234 5678）修改銀行卡密碼為123456--2.李四（卡號為1010 3576 1212 1134）修改銀行卡密碼為123123   update cardInfo    set pass=‘123456‘   where cardID =‘1010 3576 1234 5678‘      update  cardInfo    set pass =‘123123‘   where cardID=‘1010 3576 1212 1134‘         /*--------- 李四的卡號掛失 ---------*/  update cardInfo   set IsReportLoss=1  where cardID =‘1010 3576 1212 1134‘    select * from cardInfo   

 六：一些比較複雜的操作：

/* 統計銀行的資金流通餘額和盈利結算*/ --統計說明：儲存代表資金流入，取款代表資金流出，假定存款利率為千分之三，貸款利率為千分之八     declare @inMoney money  declare @outMoney money  declare @profit money  select * from transInfo   select @inMoney=SUM(transMoney)  from transInfo  where transType=‘存入‘  select @outMoney=SUM(transMoney) from transInfo where transType =‘支取‘  print ‘銀行流通金額總計為:‘+convert(varchar(20),@inMoney-@outMoney)+‘RMB‘  set @profit=@outMoney*0.008-@inMoney*0.003  print ‘盈利結算為:‘+convert(varchar(20),@profit)+‘RMB‘  go  

    DATEDIFF: 函數返回兩個日期之間的天數。

SqlServer基礎文法（二）