SQLServer语句建表建库建存储过程建视图ATM取款机示例

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