第九章SQL
来源：互联网 发布：汽车ecu编程器 编辑：程序博客网 时间：2024/05/17 04:08
--创建建库bankDBCREATE DATABASE bankDB ON ( NAME='bankDB_data', FILENAME='d:\project\bankDB_data.mdf', SIZE=5mb, FILEGROWTH=15% ) LOG ON ( NAME= 'bankDB_log', FILENAME='d:\project\bankDB_log.ldf', SIZE=5mb, FILEGROWTH=15% )GO/*$$$$$$$$$$$$$建表$$$$$$$$$$$$$$$$$$$$$$$$*/USE bankDBGOCREATE TABLE userInfo --用户信息表( customerID INT IDENTITY(1,1), customerName CHAR(8) NOT NULL, PID CHAR(18) NOT NULL, telephone CHAR(20) NOT NULL, address VARCHAR(50))GOCREATE TABLE cardInfo --银行卡信息表( cardID CHAR(19) NOT NULL, curID VARCHAR(10) NOT NULL, savingID INT 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)GOCREATE TABLE tradeInfo --交易信息表( tradeDate DATETIME NOT NULL, tradeType CHAR(4) NOT NULL, cardID CHAR(19) NOT NULL, tradeMoney MONEY NOT NULL, remark TEXT )GOCREATE TABLE Deposit --存款类型表( savingID INT IDENTITY(1,1), savingName VARCHAR(20) NOT NULL, descrip VARCHAR(50))GOALTER TABLE Deposit ADD CONSTRAINT PK_savingID PRIMARY KEY(savingID)GOALTER TABLE userInfo ADD CONSTRAINT PK_customerID PRIMARY KEY(customerID), CONSTRAINT CK_PID CHECK( len(PID)=18 or len(PID)=15 ), CONSTRAINT UQ_PID UNIQUE(PID), CONSTRAINT CK_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 telephone like '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' or telephone like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' )GOALTER TABLE cardInfo ADD 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_curID DEFAULT('RMB') FOR curID, 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), CONSTRAINT FK_savingID FOREIGN KEY(savingID) REFERENCES deposit(savingID)GOALTER TABLE tradeInfo ADD CONSTRAINT CK_tradeType CHECK(tradeType IN ('存入','支取')), CONSTRAINT FK_cardID FOREIGN KEY(cardID) REFERENCES cardInfo(cardID), CONSTRAINT CK_tradeMoney CHECK(tradeMoney>0), CONSTRAINT DF_tradeDATE DEFAULT(getdate()) FOR tradeDateGO--存款类型INSERT INTO deposit (savingName,descrip) VALUES ('活期','按存款日结算利息')INSERT INTO deposit (savingName,descrip) VALUES ('定期一年','存款期是1年')INSERT INTO deposit (savingName,descrip) VALUES ('定期二年','存款期是2年')INSERT INTO deposit (savingName,descrip) VALUES ('定期三年','存款期是3年')INSERT INTO deposit (savingName) VALUES ('定活两便')INSERT INTO deposit (savingName) VALUES ('通知')INSERT INTO deposit (savingName,descrip) VALUES ('零存整取一年','存款期是1年')INSERT INTO deposit (savingName,descrip) VALUES ('零存整取二年','存款期是2年')INSERT INTO deposit (savingName,descrip) VALUES ('零存整取三年','存款期是3年')INSERT INTO deposit (savingName,descrip) VALUES ('存本取息五年','按月支取利息')SELECT * FROM DEPOSITINSERT INTO userInfo(customerName,PID,telephone,address ) VALUES('张三','123456789012345','010-67898978','北京海淀')INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID) VALUES('1010 3576 1234 5678',1,1000,1000,1)INSERT INTO userInfo(customerName,PID,telephone) VALUES('李四','321245678912345678','0478-44443333')INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID) VALUES('1010 3576 1212 1134',2,1,1,2)INSERT INTO userInfo(customerName,PID,telephone) VALUES('王五','567891234532124670','010-44443333')INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID) VALUES('1010 3576 1212 1130',2,1,1,3)INSERT INTO userInfo(customerName,PID,telephone) VALUES('丁六','567891321242345618','0752-43345543')INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID) VALUES('1010 3576 1212 1004',2,1,1,4)SELECT * FROM userInfoSELECT * FROM cardInfoGOINSERT INTO tradeInfo(tradeType,cardID,tradeMoney) VALUES('支取','1010 3576 1234 5678',900) UPDATE cardInfo SET balance=balance-900 WHERE cardID='1010 3576 1234 5678'INSERT INTO tradeInfo(tradeType,cardID,tradeMoney) VALUES('存入','1010 3576 1212 1130',300) UPDATE cardInfo SET balance=balance+300 WHERE cardID='010 3576 1212 1130'INSERT INTO tradeInfo(tradeType,cardID,tradeMoney) VALUES('存入','1010 3576 1212 1004',1000) UPDATE cardInfo SET balance=balance+1000 WHERE cardID='1010 3576 1212 1004'INSERT INTO tradeInfo(tradeType,cardID,tradeMoney) VALUES('支取','1010 3576 1212 1130',1900) UPDATE cardInfo SET balance=balance+1900 WHERE cardID='010 3576 1212 1130'INSERT INTO tradeInfo(tradeType,cardID,tradeMoney) VALUES('存入','1010 3576 1212 1134',5000) UPDATE cardInfo SET balance=balance+5000 WHERE cardID='1010 3576 1212 1134'GOSELECT * FROM cardInfoSELECT * FROM tradeInfoupdate cardInfo set pass='123456' WHERE cardID='1010 3576 1234 5678' update cardInfo set pass='123123' WHERE cardID='1010 3576 1212 1134' --查询账户信息SELECT * FROM cardInfoupdate cardInfo set IsReportLoss=1 WHERE cardID='1010 3576 1212 1134' SELECT * FROM cardInfoGOSELECT cardid 卡号,curID 货币,savingName 储蓄种类,opendate 开户日期,openmoney 开户金额,balance 余额,pass 密码, case IsReportLoss WHEN 1 THEN '挂失' WHEN 0 THEN '未挂失' ELSE NULL end 是否挂失, customerName 客户姓名FROM CardInfo, Deposit, UserInfoWHERE CardInfo.savingID=Deposit.savingID and CardInfo.customerID = UserInfo.customerIDDECLARE @inMoney moneyDECLARE @outMoney moneyDECLARE @profit moneySELECT * FROM tradeInfo SELECT @inMoney=sum(tradeMoney) FROM tradeInfo WHERE (tradeType='存入')SELECT @outMoney=sum(tradeMoney) FROM tradeInfo WHERE (tradeType='支取')print '银行流通余额总计为:'+ convert(varchar(20),@inMoney-@outMoney)+'RMB'set @profit=@outMoney*0.008-@inMoney*0.003print '盈利结算为:'+ convert(varchar(20),@profit)+'RMB'GOSELECT c.cardID 卡号,u.customerName 姓名,c.curID 货币,d.savingName 存款类型,c.openDate 开户日期,c.openMoney 开户金额,c.balance 存款余额, CASE c.IsReportLoss WHEN 0 THEN '正常账户' WHEN 1 THEN '挂失账户' ELSE NULL END 账户状态FROM cardInfo c INNER JOIN userInfo u ON (c.customerID = u.customerID)INNER JOIN Deposit d ON (c.savingID = d.savingID )WHERE (DATEDIFF(Day,getDate(),openDate)<DATEPART(weekday,openDate))--或SELECT c.cardID 卡号,u.customerName 姓名,c.curID 货币,d.savingName 存款类型,c.openDate 开户日期,c.openMoney 开户金额,c.balance 存款余额, CASE c.IsReportLoss WHEN 0 THEN '正常账户' WHEN 1 THEN '挂失账户' ELSE NULL END 账户状态FROM cardInfo c INNER JOIN userInfo u ON (c.customerID = u.customerID)INNER JOIN Deposit d ON (c.savingID = d.savingID )WHERE (DATEDIFF(Day,getDate(),openDate)<7)SELECT * FROM tradeInfoSELECT DISTINCT cardID FROM tradeInfo WHERE tradeMoney=(SELECT Max(tradeMoney) FROM tradeInfo WHERE DATEPART(mm,tradeDate)=DATEPART(mm,getdate())AND DATEPART(yy,tradeDate)=DATEPART(yy,getdate()))SELECT customerName as 客户姓名,telephone as 联系电话 FROM userInfo WHERE customerID IN (SELECT customerID FROM cardInfo WHERE IsReportLoss=1) SELECT customerName as 客户姓名,telephone as 联系电话,balance as 存款余额 FROM userInfo INNER JOIN cardInfo ON userInfo.customerID=cardInfo.customerID WHERE balance<200if exists (select * from sysobjects where name = 'vw_userInfo')drop view vw_userInfogocreate VIEW vw_userInfo --客户信息表视图 AS select customerID as 客户编号,customerName as 开户名, PID as 身份证号, telephone as 电话号码,address as 居住地址 from userInfoGO--使用视图SELECT * FROM vw_userInfoGO--2.创建视图：查询银行卡信息if exists (select * from sysobjects where name = 'vw_cardInfo')drop view vw_cardInfogocreate VIEW vw_cardInfo --银行卡信息表视图 AS select c.cardID as 卡号,u.customerName as 客户,c.curID as 货币种类, d.savingName as 存款类型,c.openDate as 开户日期, c.balance as 余额,c.pass 密码, case c.IsReportLoss when 0 then '挂失' when 1 then '正常' end as 是否挂失 from cardInfo c, deposit d,userinfo u where c.savingID=d.savingID and c.customerID=u.customerIDGO--使用视图SELECT * FROM vw_cardInfoGO--3.创建视图：查看交易信息if exists (select * from sysobjects where name = 'vw_tradeInfo')drop view vw_tradeInfogocreate VIEW vw_tradeInfo --交易信息表视图 AS select tradeDate as 交易日期,tradeType as 交易类型, cardID as 卡号,tradeMoney as 交易金额, remark as 备注 from tradeInfo GO--使用视图SELECT * FROM vw_tradeInfoif exists (select * from sysobjects where name = 'usp_takeMoney')drop proc usp_takeMoneygocreate procedure usp_takeMoney @card char(19), @m money, @type char(4), @inputPass char(6)='' AS print '交易正进行,请稍后......' if (@type='支取') if ((SELECT pass FROM cardInfo WHERE cardID=@card)<>@inputPass ) begin raiserror ('密码错误!',16,1) return -1 end DECLARE @mytradeType char(4),@outMoney MONEY,@myCardID char(19) SELECT @mytradeType=tradeType,@outMoney=tradeMoney ,@myCardID=cardID FROM tradeInfo where cardID=@card DECLARE @mybalance money SELECT @mybalance=balance FROM cardInfo WHERE cardID=@card if (@type='支取') if (@mybalance>=@m+1) update cardInfo set balance=balance-@m WHERE cardID=@myCardID else begin raiserror ('交易失败！余额不足！',16,1) print '卡号'+@card+' 余额：'+convert(varchar(20),@mybalance) return -2 end else update cardInfo set balance=balance+@m WHERE cardID=@card print '交易成功！交易金额：'+convert(varchar(20),@m) SELECT @mybalance=balance FROM cardInfo WHERE cardID=@card print '卡号'+@card+' 余额：'+convert(varchar(20),@mybalance) INSERT INTO tradeInfo(tradeType,cardID,tradeMoney) VALUES(@type,@card,@m) RETURN 0GOdeclare @card char(19)select @card=cardID from cardInfo Inner Join userInfo ON cardInfo.customerID=userInfo.customerID where customerName='张三'EXEC usp_takeMoney @card,10 ,'支取','123456' GOselect * from cardInfo Inner Join userInfo ON cardInfo.customerID=userInfo.customerID where customerName='张三'declare @card char(19)select @card=cardID from cardInfo Inner Join userInfo ON cardInfo.customerID=userInfo.customerID where customerName='李四'EXEC usp_takeMoney @card,500 ,'存入'select * from vw_cardInfoselect * from vw_tradeInfoGOif exists (select * from sysobjects where name = 'usp_randCardID')drop proc usp_randCardIDgocreate procedure usp_randCardID @randCardID char(19) OUTPUT AS DECLARE @r numeric(15,8) DECLARE @tempStr char(10) SELECT @r=RAND((DATEPART(mm, GETDATE()) * 100000 )+ (DATEPART(ss, GETDATE()) * 1000 ) + DATEPART(ms, GETDATE()) ) set @tempStr=convert(char(10),@r) set @randCardID='1010 3576 '+SUBSTRING(@tempStr,3,4)+' '+SUBSTRING(@tempStr,7,4) --组合为规定格式的卡号GO--测试产生随机卡号DECLARE @mycardID char(19) EXECUTE usp_randCardID @mycardID OUTPUTprint '产生的随机卡号为：'+@mycardIDGOif exists (select * from sysobjects where name = 'usp_openAccount')drop proc usp_openAccountGOcreate procedure usp_openAccount @customerName char(8),@PID char(18),@telephone char(13) ,@openMoney money,@savingName char(8),@address varchar(50)='' AS DECLARE @mycardID char(19),@cur_customerID int, @savingID int --调用产生随机卡号的存储过程获得随机卡号 EXECUTE usp_randCardID @mycardID OUTPUT while exists(SELECT * FROM cardInfo WHERE cardID=@mycardID) EXECUTE usp_randCardID @mycardID OUTPUT print '尊敬的客户,开户成功!系统为您产生的随机卡号为:'+@mycardID print '开户日期'+convert(char(10),getdate(),111)+' 开户金额:'+convert(varchar(20),@openMoney) IF not exists(select * from userInfo where PID=@PID) INSERT INTO userInfo(customerName,PID,telephone,address ) VALUES(@customerName,@PID,@telephone,@address) SELECT @savingID = savingID FROM deposit WHERE savingName =@savingNameif @savingID is NULL BEGIN RAISERROR('存款类型不正确,请重新输入!',16,1) RETURN -1 END select @cur_customerID=customerID from userInfo where PID=@PID INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID) VALUES(@mycardID,@savingID,@openMoney,@openMoney,@cur_customerID)GO--调用存储过程重新开户EXEC usp_openAccount '王五','334456889012678','2222-63598978',1000,'活期','河南新乡' EXEC usp_openAccount '赵二','213445678912342222','0760-44446666',1,'定期' select * from vw_userInfoselect * from vw_cardInfoGOif exists (select * from sysobjects where name = 'usp_CheckSheet')drop proc usp_CheckSheetGOCREATE PROCEDURE usp_CheckSheet @cardID varchar(19), @date1 datetime=NULL, @date2 datetime=NULLASDECLARE @custName varchar(20)DECLARE @curName varchar(20)DECLARE @savingName varchar(20)DECLARE @openDate datetimeSELECT @cardID=c.cardID, @curName=c.curID, @custName=u.customerName, @savingName=d.savingName , @openDate=c.openDate FROM cardInfo c, userInfo u, deposit dWHERE c.customerID=u.customerID and c.savingID = d.savingID and cardID = @cardID PRINT '卡号：' + @cardIDPRINT '姓名:' + @custNamePRINT '货币:' + @curNamePRINT '存款类型：' + @savingNamePRINT '开户日期：' + CAST(DATEPART(yyyy,@openDate) AS VARCHAR(4))+'年' + CAST(DATEPART(mm,@openDate) AS VARCHAR(2))+'月' + CAST(DATEPART(dd,@openDate) AS VARCHAR(2))+'日'PRINT ' 'print '--------------------------------------------------------------------' IF @date1 IS NULL AND @date2 IS NULL BEGINSELECT tradeDate 交易日, tradeType 类型, tradeMoney 交易金额, remark 备注FROM tradeInfoWHERE cardID='1010 3576 1212 1134'ORDER BY tradeDateRETURN END ELSE IF @date2 IS NULL SET @date2 = getdate() SELECT tradeDate 交易日, tradeType 类型, tradeMoney 交易金额, remark 备注FROM tradeInfoWHERE cardID=@cardID AND tradeDate BETWEEN @date1 AND @date2ORDER BY tradeDateGO--测试打印对帐单EXEC usp_CheckSheet '1010 3576 1212 1134'EXEC usp_CheckSheet '1010 3576 1212 1134','2009-11-2','2009-11-30'if exists (select * from sysobjects where name = 'usp_pagingDisplay')DROP PROCEDURE usp_pagingDisplayGOCREATE PROCEDURE usp_pagingDisplay @records int = 10, @page int = 1AS SET NOCOUNT ON DECLARE @rec1 int SET @rec1 = @records DECLARE @rec2 int SET @rec2 = (@page - 1) * @records DECLARE @statement nvarchar(200) SET @statement='SELECT TOP ' + CAST(@rec1 AS varchar(10)) + ' tradeDate 交易日期,tradeType 交易类型,cardID 卡号,trademoney 交易金额 FROM tradeInfo WHERE cardID not in (SELECT TOP '+ CAST(@rec2 AS varchar(10)) + ' cardID FROM tradeInfo)' EXEC SP_EXECUTESQL @statement,N'@rec1 int,@rec2 int',@rec1,@rec2GO--EXEC usp_pagingDisplay 2,2if exists (select * from sysobjects where name = 'usp_getWithoutTrade')drop proc usp_getWithoutTradeGOcreate procedure usp_getWithoutTrade @Num int output, @Amount decimal(18,2) output, @date1 datetime = NULL, @date2 datetime = NULLAS IF @date1 IS NULL BEGINdeclare @dateStr varchar(50)set @dateStr = convert(varchar(4),DATEPART(YY,GETDATE())) + '-'+convert(varchar(2),DATEPART(mm,GETDATE())) + '-1 00:00:00.000' set @date1 = convert(datetime, @datestr,101) END IF @date2 IS NULLSET @date2 = getdate() SELECT distinct u.customerID 客户号,u.customerName 客户姓名,u.PID 身份证号,u.telephone 电话,address 地址 FROM userInfo u JOIN cardInfo c ON u.customerID = c.customerID WHERE c.cardID NOT IN (SELECT cardID FROM tradeInfo WHERE tradeDate Between @date1 and @date2) SELECT @Num=COUNT(customerID), @Amount=SUM(balance) FROM cardInfo WHERE cardID NOT IN (SELECT cardID FROM tradeInfo WHERE tradeDate Between @date1 and @date2)GODECLARE @NUM intDECLARE @Amount decimal(18,2)DECLARE @date1 datetimeDECLARE @date2 datetimeSET @date1 = '2009-1-1'SET @date2 = getdate()EXEC usp_getWithoutTrade @NUM OUTPUT, @Amount OUTPUT--, @date1, @date2PRINT '统计未发生交易的客户'PRINT '---------------------------------------'PRINT '客户人数：' + CAST(@NUM AS varchar(10)) + ' 客户总余额：' + CAST(@Amount AS varchar(20))if exists (select * from sysobjects where name = 'usp_getTradeInfo')drop proc usp_getTradeInfoGOcreate procedure usp_getTradeInfo @Num1 int output, @Amount1 decimal(18,2) output, @Num2 int output, @Amount2 decimal(18,2) output, @date1 datetime, @date2 datetime = NULL, @address varchar(20) = NULLAS -- 初始化变量 SET @Num1 = 0 SET @Amount1 = 0 SET @Num2 = 0 SET @Amount2 = 0 IF @date2 IS NULLSET @date2 = getdate() IF @address IS NULL BEGIN SELECT @Num1=COUNT(tradeMoney), @Amount1=SUM(tradeMoney) FROM tradeInfo WHERE tradeDate BETWEEN @date1 AND @date2 AND tradeType='存入' SELECT @Num2=COUNT(tradeMoney), @Amount2=SUM(tradeMoney) FROM tradeInfo WHERE tradeDate BETWEEN @date1 AND @date2 AND tradeType='支取' END ELSE BEGIN SELECT @Num1=COUNT(tradeMoney), @Amount1=SUM(tradeMoney) FROM tradeInfo JOIN cardInfo ON tradeInfo.cardID = cardInfo.cardID JOIN userInfo ON cardInfo.customerID = userInfo.customerID WHERE tradeDate BETWEEN @date1 AND @date2 AND tradeType='存入' AND address Like '%'+@address+'%' SELECT @Num2=COUNT(tradeMoney), @Amount2=SUM(tradeMoney) FROM tradeInfo JOIN cardInfo ON tradeInfo.cardID = cardInfo.cardID JOIN userInfo ON cardInfo.customerID = userInfo.customerID WHERE tradeDate BETWEEN @date1 AND @date2 AND tradeType='支取' AND address Like '%'+@address+'%' ENDGODECLARE @CNT1 intDECLARE @Total1 decimal(18,2)DECLARE @CNT2 intDECLARE @Total2 decimal(18,2)DECLARE @date1 datetimeDECLARE @date2 datetimeSET @date1 = '2009-1-1'SET @date2 = getdate()EXEC usp_getTradeInfo @CNT1 OUTPUT, @Total1 OUTPUT, @CNT2 OUTPUT, @Total2 OUTPUT, @date1, @date2--, '北京'PRINT '统计银行卡交易量和交易额'PRINT ''PRINT '起始日期：' + CONVERT(varchar(10),@date1,102) + ' 截止日期：' + CONVERT(varchar(10),@date2,102)PRINT '-----------------------------------------------------------'PRINT '存入笔数：' + CAST(@CNT1 AS varchar(20)) + ' 存入金额：' + CAST(@Total1 AS varchar(20))PRINT '支取笔数：' + CAST(@CNT2 AS varchar(20)) + ' 支取金额：' + CAST(@Total2 AS varchar(20))PRINT '-----------------------------------------------------------'PRINT '发生笔数：' + CAST(@CNT1+@CNT2 AS varchar(20)) + ' 结余金额：' + CAST(@Total1-@Total2 AS varchar(20))GO--转帐的事务存储过程if exists (select * from sysobjects where name = 'usp_tradefer')drop proc usp_tradeferGOcreate procedure usp_tradefer @card1 char(19), @pwd char(6), @card2 char(19), @outmoney money AS DECLARE @date1 datetime DECLARE @date2 datetime SET @date1 = getdate() begin tran print '开始转帐，请稍后......' DECLARE @errors int set @errors=0 DECLARE @result int EXEC @result=usp_takeMoney @card1,@outmoney ,'支取',@pwd --'123123' set @errors=@errors+@@error if (@errors > 0 or @result <> 0)begin print '转帐失败！' rollback tran RETURN -1end EXEC @result=usp_takeMoney @card2,@outmoney ,'存入' set @errors=@errors+@@error if (@errors > 0 or @result <> 0) begin print '转帐失败！' rollback tran RETURN -1 end else begin print '转帐成功！' commit tran SET @date2 = getdate() print '打印转出账户对账单' PRINT '-------------------' EXEC usp_CheckSheet @card1,@date1,@date2 print '打印转入账户对账单' PRINT '-------------------' EXEC usp_CheckSheet @card2,@date1,@date2 RETURN 0 endGOdeclare @card1 char(19),@card2 char(19)select @card1=cardID from cardInfo Inner Join userInfo ON cardInfo.customerID=userInfo.customerID where customerName='李四'select @card2=cardID from cardInfo Inner Join userInfo ON cardInfo.customerID=userInfo.customerID where customerName='张三'EXEC usp_tradefer @card1,'123123',@card2,2000select * from vw_userInfoselect * from vw_cardInfoselect * from vw_tradeInfoGO--1.添加SQL登录帐号CREATE LOGIN appAdmin WITH PASSWORD='bank1234'GO--2.创建数据库用户 USE bankDBGOCREATE USER appAdmin FOR LOGIN appAdmin GO--3. 给数据库用户appAdmin授权(增删改查的权限) GRANT select,insert,update,delete,select ON vw_userInfo TO appAdmin GRANT select,insert,update,delete,select ON vw_tradeInfo TO appAdmin GRANT select,insert,update,delete,select ON vw_cardInfo TO appAdminGO 0 0
- SQL笔记 第九章
- SQL第九章
- sql 第九章
- 第九章SQL
- SQL 第九章
- 第九章 PL/SQL包
- 第九章 PL/SQL包
- 第九章 PL/SQL包
- Head First SQL 第九章
- Hello Android 之 第九章 SQL 实战
- SQL基础第九章行函数
- SQL必知必会 笔记 第九章 汇总数据
- SQL 高级 -- 第九章查询小项目
- SQL Server 2000 程序设计学习笔记--第九章 触发器
- SQL第九章数据查询基础上机题1-4
- 第九章
- 第九章
- 第九章
- 魔幻数字47
- 需要精读3遍的8个健身知识
- 银行ATM机存取款
- 373. Find K Pairs with Smallest Sums
- TabCtrl控件的使用
- 第九章SQL
- S2 第九章项目
- 数据结构之 AVL平衡树 (c++)
- 银行ATM存取机-----代码
- 第九章：银行ATM存取款机系统
- Android开发之获取屏幕宽高
- Lesson16-20
- 知识库--Handling Write Skew Anomaly 处理写偏差（123）
- ImageView保持前景src和ImageView的高度一致
