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 ( 数据控制语言 )，用于存取许可，存取权限，(GRAN
2、T，REVOKE) 。(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 和
3、OR 是运算符连接条件。NOT 是否定条件。3，AND 连接条件，并且权当两个条件为真时，才成立（返回 TRUE） 。4，OR 连接条件，但只要其中一个成立（返回 TRUE） 。例: 采购时，付款方式要求是信用卡，只能用（牡丹卡，龙卡，阳光卡）NOT (付款方式 = 信用卡) OR (信用卡 IN (牡丹卡, 龙卡, 阳光卡)四，全局变量1,SQL Server 全局变量使用两个 标志为前缀，局部变量则用一个 全局变量变量 说明 使用方式(或其它方式)ERROR 最一个 T-SQL 错误的错误号 SELECT ERRORIDENTITY 最后一次插入的标识值 同上或其它方式LANGUAGE 当
4、前使用的语言的名称 同上或其它方式MAX_CONNECTIONS 可以创建的同时连接的最大数目 同上或其它方式ROWCOUNT 受上一个 SQL 语句影的行数 同上或其它方式SERVERNAME 本地服务器名称 同上或其它方式SERVICENAME 该计算机上的 SQL 服务的名称 同上或其它方式TIMETICKS 当前计算机上每一刻度的微秒数 同上或其它方式TRANSCOUNT 当前连接打开的事务数 同上或其它方式VERSION SQL Server 的版本信息 同上或其它方式五，SQL 语集合1、创建数据库CREATE DATABASE database-name2、删除数据库drop d
5、atabase dbname3、备份 sql server- 创建 备份数据的 deviceUSE 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
6、table tabname add 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 *
7、into b from a where 1 武汉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 UsersTableWhere Birthday NOT BETWEEN 1991-08-01 AND 1993-07-0129、使用
8、IN 列举值SELECT * FROM UsersTableWhere 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 AVG(Score) AS 分数 FROM Where Score = 60(3) MAX 和 MIN (可用于计算日期 )SELECT AVG(Score)
9、 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 where Score = 6031、分组查询(1)Group By 分组查询语法: SELECT * FROM Table Group By ,SELECT AVG(Score) AS 平均分 FROM
10、 StudTable Group By CourseID(2)HAVING 分组筛选SELECT Id AS 学员编号, AVG(Score) AS 平均分 FROM StudTableGroup By Id, CourseID HAVING Count(Score) 132、多表连接查询(1) 左外联接: LEFT OUTER JOIN 或者 LEFT JOIN 左外连接（左连接）：结果集几包括连接表的匹配行，也包括左连接表的所有行。(2) 右外联接: RIGHT OUTER JOIN 或者 LEFT JOIN 右外连接(右连接)：结果集既包括连接表的匹配连接行，也包括右连接表的所有行。(3
11、) 完整联接: FULL OUTER JOIN 或者 FULL JOIN全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。33、内联(1) 在 Where 子句中指定联接条件 . (表名 1：StudTable 表名 2：ScoreTable)语法:SELECT 表 1.列名,表 2.列名, FROM 表 1,表 2, Where SELECT StudTable.Name,ScoreTable.Score FROM StudTable,ScoreTableWhere StudTable.Id = ScoreTable.Id (2) 在 FROM 子句中使用 JOINONSE
12、LECT 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 FROM StudTable AS SLEFT OUTER JOIN ScoreTable AS C ON S.Id = C.Id(2) 右外联接: RIGHT OUTER JOIN 或者 LEFT JOINSELECT StudTable.Name,ScoreTable.Sc
13、oreId,StudTable.Score FROM StudTable RIGHT OUTER JOIN ScoreTable ON StudTable.Id = ScoreTable.Id35、两张关联表，删除主表中已经在副表中没有的信息delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )36、四表联查问题：SELECT * FROM talbe1 LEFT INNER JOIN table2 ONtable1.Id = table2.Id RIGHT J
14、OIN table3 ON table1.Id = table3.Id INNER JOINtable4 ON table1.Id = table4.Id Where 37、日程安排提前五分钟提醒select * from 日程安排 where datediff(minute,f 开始时间,getdate()538、一条 sql 语句搞定数据库分页select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段39、选择
15、在每一组 b 值相同的数据中对应的 a 最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.)select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)40、包括所有在 TableA 中但不在 TableB 和 TableC 中的行并消除所有重复行而派生出一个结果表(select a from tableA ) except (select a from tableB) except (select a from tableC)-
16、高级查询-insert into stuInfoTablevalues(s100,张秋丽, 女, 18,1,湖北省武汉市 )insert into stuInfoTablevalues(s101,孝丽,女, 18,2,湖北省麻城市 )insert into stuInfoTablevalues(s102,雨漓,男, 20,3,湖北省麻城市 )insert into stuInfoTablevalues(s103,小白,女, 18,5,湖北省武汉市 )insert into stuInfoTablevalues(s104,lily,男 ,20,6,湖北省荆门市)insert into stuMa
17、rksTablevalues(101,s100,65,80)insert into stuMarksTablevalues(102,s101,85,56)insert into stuMarksTablevalues(103,s100,65,75)insert into stuMarksTablevalues(104,s103,80,85)insert into stuMarksTablevalues(105,s104,60,55)41、几个高级查询运算词(1) UNION 运算符UNION 运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去表中任何重复行而派生出一个结
18、果表。当 ALL 随 UNION 一起使用时（即 UNION ALL） ，不消除重复行。两种情况下，派生表的每一行不是来自 TABLE1 就是来自 TABLE2。(2) EXCEPT 运算符EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL)，不消除重复行。 (3) INTERSECT 运算符INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTER
19、SECT 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,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、局部变量的声名与属值
20、声名 Declare i int属值 Set i = 1 或 select i = 145、IF-ELSE 条件语句语法: IF (条件)BEGIN语句或语句块ENDELSEBEGIN语句或语句块END示例:DECLARE myavg floatSELECT myavg=AVG(writtenExam) from stuMarksTablePrint 本班平均成绩+Convert(varhcar(5),myavg)IF(myavg 70)BEGINPrint 全三名SELECT TOP 3 * FROM stuMarksTable ORDER BY writtenExam DESC ENDEL
21、SEBEGINPrint 后三名SELECT TOP 3 * FROM stuMarksTableORDER BY writtenExamEND46、WHIL 循环语句误法:WHILE(条件)语句或语句块BREAK示例:DECLARE k intWHILE(1=1) -条件永远成立BEGIN-统计不级格的人数SELECT k=COUNT(*) FROM stuMarksTable writtenExam 0)-每人加 2 分UPDATE stuMarksTable SET writtenExam= writtenExam+2ELSEBREAK -退出ENDPrint 加分如下SELECT *
22、FROM stuMarksTable 47、CASE 多分支语句语法:CASEWHEN 条件 1 THEN 结果 1WHEN 条件 2 THEN 结果 2ELSE 其它结果END示例:Print ABC 星级表SELECT stuName,成绩=CASEWHEN writtenExam (子查询)示例 1:SELECT * FROM stuInfoTable-筛选比孝丽年龄大的学生WHERE stuAge(SELECT stuAge FROM stuMarksTable WHERE stuName=孝丽)示例 1:SELECT stuName FROM stuInfoTable INNER J
23、OIN 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 查询SELECT stuName FROM stuInfoTableWHERE stuId NOT IN (SELECT stuId FR
24、OM stuMarksTable WHERE WrittenExam =60)50、EXISTS 和 NOT EXISTS(1)-EXISTS 的用法-判断要创建的数据是否存在,如果存在则删除重建IF EXISTS (SELECT * FROM sysDatabases WHERE name=studDB)DROP DATABASE studDBCREATE DATABASE studDB(2)-NOT EXISTS 的用法-根据考试难度加分IF NOT EXISTS(SELECT * FROM stuMarksTable WHERE WHERE WrittenExam60 AND LabEx
25、am 60)BEGINPrint 本次考试无人通过,每人加 3 分UPDATE stuMarksTable SET WrittenExam = WrittenExam+3, LabExam = LabExam+3SELECT * FROM stuMarksTableENDELSEBEGINPrint 本次考的一般,每人加 2 分UPDATE stuMarksTable SET WrittenExam = WrittenExam+2, LabExam = LabExam+2SELECT * FROM stuMarksTableEND51、事物(1),BEGIN TRANSACTION -开始事物
26、(2),COMMIT TRANSACTION -提交事物(3),ROLLBACK TRANSACTION -回滚事物(4),SET IMPLICIT_ TRANSACTIONS ON -隐性事物52、索引(1),UNIQUE -唯一索引(可选)(2),CLUSTERED , NONCLUSTERED -聚集索引还是非聚集索引(可选)(3),FILLFACTOR -表示填充因子 ,(0100)六,ATM 自动取款机( 事物,索引,)1,ATM 的创建use mastergoexec xp_cmdshell mkdir d:projectif exists(select * from sysdat
27、abases 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_log,filename = d:projectATM_log.ldf,size = 10mb,filegrowth = 1mb,maxsize = 50mb)gouse ATMgoset nocount onif exists(sele
28、ct * from sysobjects where name = transinfo)drop table transinfoif exists(select * from sysobjects where name = cardinfo)drop table cardinfoif exists(select * from sysobjects where name = userinfo)drop table userinfogo-建用户信息表create table userinfo(customerID int identity(100000,1) primary key not nul
29、l,customerName varchar(10) not null,PID bigint unique(PID) check(len(PID) = 15 or len(PID) = 18) not null,telephone varchar(20) check(telephone like %-_ or len(telephone) = 11) not null,address varchar(30)-建银行卡信息表create table cardinfo(cardID char(19) primary key check(cardID like 1010 3576 _ _) not
30、null,curType varchar(10) default(RMB) not null,savingType varchar(8) default(活期) check(savingType 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
31、)=6) not null,IsReportLoss char(2) default(否) 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(transMone
32、y 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 transinfo addconstraint FK_car
33、dID 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,telephone,address)select 雨漓,000
34、0214101965,0713-67898978,湖北麻城市 unionselect 小白,321245678912345678,0478-44442222,不详insert into cardinfo(cardID,savingType,openMoney,balance,customerID)select 1010 3576 1234 5678,活期,1,1,100000 unionselect 1010 3576 0000 1111,定期,1000,1000,100001select * from userinfoselect * from cardINFogo-常规业务模拟-修改密码u
35、pdate cardinfo set pass = 123456 where cardID = 1010 3576 1234 5678update cardinfo set pass = 123123 where cardID = 1010 3576 0000 1111-银行卡挂失update cardinfo set IsReportLoss = 是 where cardID = 1010 3576 0000 1111-统计赢余declare inmoney money,outmoney moneyselect inmoney = sum(transMoney) from transinfo
36、 where transType = 存入select outmoney = sum(transMoney) from transinfo where transType = 支取print银行流通余额总计为：+ convert(varchar(20),inmoney-outmoney)+RMBprint赢利结算为：+ convert(varchar(20),outmoney*0.008-inmoney*0.003)-查询本周开户的卡号信息print 本周开户的卡号有：declare week int,monday datetimeselect week = datepart(dw,getda
37、te()-获取现在时间的星期编号，注意：星期天为 1if week = 1select * from cardinfo where (opendate between getdate()-7 and 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 = car
38、dID from transinfo where transMoney = (select max(transmoney) from transinfo )print 本月交易金额最高的卡号是： +cardid-查询挂失帐号的客户信息select 客户姓名=customerName, 客户帐号=customerID, 生分证号 =PID,联系电话=telephone,客户地址=addressfrom userinfo where customerID in (select customerID from cardinfo where IsReportLoss = 是)-催款提醒select 客
39、户姓名=customerName,联系电话=telephone,帐上余额 =balance from userinfo inner join cardinfo on userinfo.customerID = cardinfo.customerID where balance 0begin-事物-rollback tranendelsebeginprint 删除用户成功！commit tranendendgo-修改密码的存储过程if exists(select * from sysobjects where name = proc_updatepass)drop procedure proc_
40、updatepassgocreate procedure proc_updatepasscardid varchar(20),oldpass int,newpass intasdeclare old intselect old = pass from cardinfo where cardID = cardidif(old = oldpass)beginupdate cardinfo set pass = newpass where cardID = cardidprint 修改成功！endelseprint你输入的原密码错误，无法修改！go-修改是否挂失的存储过程if exists(sele
41、ct * from sysobjects where name = proc_isReportloss)drop procedure proc_isReportlossgocreate procedure proc_isReportlosscardid varchar(20),yes char(2),pass intasdeclare old intselect old = pass from cardinfo where cardID = cardidif(old = pass)beginupdate cardinfo set isReportLoss = yes where cardID
42、= cardidprint 修改成功！endelseprint你输入的密码错误，无法挂失！go-查询余额的存储过程if exists(select * from sysobjects where name = proc_viewMoney)drop procedure proc_viewMoneygo-创建存储过程-create procedure proc_viewMoneycardid varchar(20),pass int,balance money outputasdeclare old intselect old = pass from cardinfo where cardID
43、= cardidif(old = pass)beginselect balance = balance from cardinfo where cardID = cardidendelseprint你输入的密码错误，无法查询！go -查询交易信息的存储过程if exists(select * from sysobjects where name = proc_viewtrans)drop procedure proc_viewtransgo-创建存储过程-create procedure proc_viewtranscardid varchar(20),pass int,Type char(4
44、) output,time smalldatetime output,money money outputasdeclare old intselect old = pass from cardinfo where cardID = cardidif(old = pass)beginselect time = transDate,Type = transType,money = transMoneyfrom transinfo where cardid = cardidendelseprint你输入的密码错误，无法查询！go-在交易信息表中创建触发器-create trigger trig_t
45、ransinfo on transinfoif exists(select name from sysobjects where name = trig_transinfo)drop trigger trig_transinfogocreate trigger trig_transinfoon transinfofor insertasdeclare type char(4),outMoney money,myCardID char(20),balance intselect type = transType,outMoney = transMoney,myCardID = cardID fr
46、om insertedselect balance = balance from cardinfo where cardID = myCardIDif(type=支取)if(balance 0)beginprint 交易失败！rollback transactionendelseprint交易成功！select balance = balance from cardinfo where cardID = myCardIDprint帐号： +myCardID+ 余额：+convert(varchar(20),balance)+元print go-创建转帐事务存储过程-create procedu
47、re proc_transfer if exists(select * from sysobjects where name = proc_transfer)drop procedure proc_transfergocreate procedure proc_transfercardid1 varchar(20),cardid2 varchar(20),tranmoney moneyasbegin trandeclare sumerror intset sumerror = 0if not exists(select cardID from cardinfo where cardID = cardid1)beginprint 您输入的您的卡号不存在，请确认！rollback transactionendelse if not exists(select cardID from c
