SQLServer2005实验指导书

SQLServer2005实验指导书某银行拟开发一套ATM取款机系统，实现如下功能：1、开户（到银行填写开户申请单，卡号自动生成）2、取钱3、存钱4、查询余额5、转账（如使用一卡通代缴手机话费、个人股票交易等）现要求对“ATM柜员机系统”进行数据库的设计并实现，数据库保存在D:\bank目录下，文件增长率为15%。用户信息表：UserInfo字段名称说明customerID顾客编号自动编号（标识列），从1开始，主键customerName开户名必填PID身份证号必填，只能是18位，身份证号唯一约束telephone联系电话必填，格式为xxxx-xxxxxxxx或手机号13位address居住地址可选输入银行卡信息表：cardInfo字段名称说明cardID卡号必填，主健，银行的卡号规则和电话号码一样，一般前8位代表特殊含义，如某总行某支行等。假定该行要求其营业厅的卡号格式为：10103576xxxxxxx开始,每4位号码后有空格，卡号一般是随机产生。curType货币种类必填，默认为RMBsavingType存款类型活期/定活两便/定期openDate开户日期必填，默认为系统当前日期openMoney开户金额必填，不低于1元balance余额必填，不低于1元,否则将销户pass密码必填，6位数字，开户时默认为6个“8”IsReportLoss是否挂失必填，是/否值，默认为”否”customerID顾客编号外键，必填，表示该卡对应的顾客编号，一位顾客允许办理多张卡号交易信息表：transInfo字段名称说明transDate交易日期必填，默认为系统当前日期cardID卡号必填，外健，可重复索引transType交易类型必填，只能是存入/支取transMoney交易金额必填，大于0remark备注可选输入，其他说明实验七数据库一、技能目标：1．会使用SQL语句创建、修改和删除数据库；2．会使用SQL语句创建、修改和删除数据表；二、知识目标：1．掌握数据库创建、修改和删除语句格式；2．掌握表的创建、修改和删除语句格式；3．掌握各类约束的实现方法。三、实验内容及步骤：1．利用SQL语句创建数据库；--检验数据库是否存在，如果为真，删除此数据库--IFexists(SELECT*FROMsysdatabasesWHEREname='bankDB')DROPDATABASEbankDBGO--创建建库bankDBCREATEDATABASEbankDBON(NAME='bankDB_data',FILENAME='d:\bank\bankDB_data.mdf',SIZE=3mb,FILEGROWTH=15%)LOGON(NAME='bankDB_log',FILENAME='d:\bank\bankDB_log.ldf',SIZE=3mb,FILEGROWTH=15%)GO2.创建用户信息表、银行卡信息表和交易信息表USEbankDBGOCREATETABLEuserInfo--用户信息表(customerIDINTIDENTITY(1,1),customerNameCHAR(8)NOTNULL,PIDCHAR(18)NOTNULL,telephoneCHAR(13)NOTNULL,addressVARCHAR(50))GOCREATETABLEcardInfo--银行卡信息表(cardIDCHAR(19)NOTNULL,curTypeCHAR(5)NOTNULL,savingTypeCHAR(8)NOTNULL,openDateDATETIMENOTNULL,openMoneyMONEYNOTNULL,balanceMONEYNOTNULL,passCHAR(6)NOTNULL,IsReportLossBITNOTNULL,customerIDINTNOTNULL)GOCREATETABLEtransInfo--交易信息表(transDateDATETIMENOTNULL,transTypeCHAR(4)NOTNULL,cardIDCHAR(19)NOTNULL,transMoneyMONEYNOTNULL,remarkTEXT)GO3.分别给三张表添加约束条件/*$$$$$$$$$$$$$加约束$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$*//*userInfo表的约束customerID顾客编号自动编号（标识列），从开始，主键customerName开户名必填PID身份证号必填，只能是位或位，身份证号唯一约束telephone联系电话必填，格式为xxxx-xxxxxxxx或手机号位address居住地址可选输入*/ALTERTABLEuserInfoADDCONSTRAINTPK_customerIDPRIMARYKEY(customerID),CONSTRAINTCK_PIDCHECK(len(PID)=18),CONSTRAINTUQ_PIDUNIQUE(PID),CONSTRAINTCK_telephoneCHECK(telephonelike'[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'ortelephonelike'[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'orlen(telephone)=13)GO/*cardInfo表的约束cardID卡号必填，主健,银行的卡号规则和电话号码一样，一般前位代表特殊含义，如某总行某支行等。假定该行要求其营业厅的卡号格式为：3576xxxxxxx开始curType货币必填，默认为RMBsavingType存款种类活期/定活两便/定期openDate开户日期必填，默认为系统当前日期openMoney开户金额必填，不低于元balance余额必填，不低于元,否则将销户pass密码必填，位数字，默认为个IsReportLoss是否挂失必填，是/否值，默认为”否”customerID顾客编号必填，表示该卡对应的顾客编号，一位顾客可以办理多张卡*/ALTERTABLEcardInfoADDCONSTRAINTPK_cardIDPRIMARYKEY(cardID),CONSTRAINTCK_cardIDCHECK(cardIDLIKE'10103576[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),CONSTRAINTDF_curTypeDEFAULT('RMB')FORcurType,CONSTRAINTCK_savingTypeCHECK(savingTypeIN('活期','定活两便','定期')),CONSTRAINTDF_openDateDEFAULT(getdate())FORopenDate,CONSTRAINTCK_openMoneyCHECK(openMoney=1),CONSTRAINTCK_balanceCHECK(balance=1),CONSTRAINTCK_passCHECK(passLIKE'[0-9][0-9][0-9][0-9][0-9][0-9]'),CONSTRAINTDF_passDEFAULT('888888')FORpass,CONSTRAINTDF_IsReportLossDEFAULT(0)FORIsReportLoss,CONSTRAINTFK_customerIDFOREIGNKEY(customerID)REFERENCESuserInfo(customerID)GO/*transInfo表的约束transType必填，只能是存入/支取cardID卡号必填，外健，可重复索引transMoney交易金额必填，大于transDate交易日期必填，默认为系统当前日期remark备注可选输入，其他说明*/ALTERTABLEtransInfoADDCONSTRAINTCK_transTypeCHECK(transTypeIN('存入','支取')),ALTERTABLEtransInfoADDCONSTRAINTFK_cardIDFOREIGNKEY(cardID)REFERENCEScardInfo(cardID),ALTERTABLEtransInfoCONSTRAINTCK_transMoneyCHECK(transMoney0),ADDCONSTRAINTDF_transDATEDEFAULT(getdate())FORtransDateGO4．修改数据库主数据文件：初始大小为10mb，增长为10%AlterdatabsebankdbModifyfile(Name=’bankDB_data',Size=10mb,FileGrowth=10%)实验八T-SQL编程一、技能目标：1．会使用变量进行T-SQL编程2．会使用函数进行T-SQL编程3．会使用IF、WHILE、CASE语句进行T-SQL编程二、知识目标：1．掌握如何定义变量并赋值2．掌握如何输出显示数据3．掌握掌握IF、WHILE、CASE逻辑控制语句4．掌握函数的使用三、实验内容及步骤1．补充知识（1）RAND函数返回介于0和1之间的随机float值。语法RAND([seed])参数seed可以隐式转换为int的整数表达式，给出种子或起始值。返回值float（2）CONVERT函数将一种数据类型的表达式显式转换为另一种数据类型的表达式。语法CONVERT(data_type[(length)],expression[,style])参数expression任何有效的表达式。data_type作为目标的系统提供数据类型。这包括xml、bigint和sql_variant。不能使用别名数据类型。lengthnchar、nvarchar、char、varchar、binary或varbinary数据类型的可选参数。对于CONVERT，如果未指定length，则默认为30个字符。style用于将datetime或smalldatetime数据转换为字符数据（nchar、nvarchar、char、varchar、nchar或nvarchar数据类型）的日期格式的样式；或用于将float、real、money或smallmoney数据转换为字符数据（nchar、nvarchar、char、varchar、nchar或nvarchar数据类型）的字符串格式的样式。如果style为NULL，则返回的结果也为NULL。111表示yyyy/mm/dd2．开户过程的实现DECLARE@rnumeric(15,8)DECLARE@tempStrchar(10)DECLARE@randCardIDchar(19)SELECT@r=RAND((DATEPART(mm,GETDATE())*100000)+(DATEPART(ss,GETDATE())*1000)+DATEPART(ms,GETDATE()))set@tempStr=convert(char(10),@r)--产生.xxxxxxxx的数字,我们需要小数点后的八位数字set@randCardID='10103576'+SUBSTRING(@tempStr,3,4)+''+SUBSTRING(@tempStr,7,4)--组合为规定格式的卡号INSERTINTOuserInfo(customerName,PID,telephone,address)VALUES('张三','123456789012345','010-67898978','北京海淀')INSERTINTOcardInfo(CardID,savingType,openMoney,balance,customerID)VALUES(@randCardID,'活期',1000,1000,1)print'尊敬的客户,开户成功!系统为您产生的随机卡号为:'+@my