《SQLServer数据库学习笔记.docx》由会员分享，可在线阅读，更多相关《SQLServer数据库学习笔记.docx（18页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、数据库一、 数据库基础知识1、 发展阶段萌芽期（文件系统） 初级阶段（层次模型和网状模型）主要代表为IBM公司的IMS（信息管理系统） 中级阶段（关系型数据库）主要代表为甲骨文公司Oracle，微软SQL Server和IBM的DB2 高级阶段（关系对象型数据库）2、 数据模型层次模型：结构型数据库主要数据模型，代表数据库为IMS网状模型：构成网状数据库的主要数据模型关系模型：采用二维表结构采用关系模型组织数据查询效率有时不高面向对象模型：3、数据库系统结构（1）用户级数据库：外模式（2）概念机数据库：模式（3）乌力吉数据库：内模式，最接近数据的物理存储与组织级别4、数据库三模式（1）外模式：
2、模式的子集，应用程序都是和外模式打交道（2）模式：（3）内模式：只有一个内模式5、数据库的二级映像（1）外模式/模式映像：保障物理独立性（2）模式/内模式映像：保证逻辑独立性6、关系模型完整性约束实体完整性（所有主属性都不能取空值）；参照完整性（多个表之间）；用户自定义的完整性7、SQL：（结构化查询语言）T-SQL ：SQL Server，包括数据定义语言，数据操作语言，数据查询语言，数据控制语言PL_SQL：Oracle，声明，执行体开始，异常处理，执行体结束。二、SQL Server数据库基础1、系统数据库：包括Resource数据库（只读数据库）、master数据库、msdb数据库、t
3、empdb数据库、model数据库（数据库模板）；系统数据库存储在隐藏的系统表中2、用户数据库：数据库的存储机构包括逻辑存储结构和物理存储结构SQL Server数据文件类型：主数据文件（mdf文件，只能有一个）、辅助数据文件（ndf，可以没有，也可以有多个）文件、日志文件（ldf，记录对数据库记录的操作，至少有一个，可以有多个）；数据存储的基本单位是“页”，一个页的大小为8Kb，一个区有8个页。（1）创建数据库名称遵循规则：第一个字符必须是字母或“_”“”“#”；数据库名称不能是Transaction-SQL的保留字；不允许嵌入空格或其他特殊字符。USE masterif exists(se
4、lect * from sysdatabases where name = mytest)drop database mytestcreate database myteston primary( name = mytest, filename = D:Program FilesSQL Server DataSQL Server DataDatamytest.mdf, size = 5MB, maxsize = 100MB, filegrowth = 15%),(name = mytest1,filename = D:Program FilesSQL Server DataSQL Server
5、 DataDatamytest1.mdf,size = 5MB,maxsize = 100MB,filegrowth = 15%)log on(name = mytest_log,filename = D:Program FilesSQL Server DataSQL Server DataDatamytest_log.ldf,size = 2MB,filegrowth = 1MB)Go（2）删除数据库DROP DATABASE 数据库名（3）收缩数据库手动收缩和自动收缩3、数据表数据完整性：实体完整性约束，表中不能存在相同的数据项；域完整性，给定列的输入内容具有有效性；引用完整性，保存表之间
6、的定义关系；自定义完整性主键：一个表只能有一个主键，可以没有主键，选择时，要确保最少性和稳定性外键：确保子表中的数据对应主表中的主键或者唯一键常用数据类型：数字类型：int，float，decimal（必须制定范围和精度）文本类型：char，varchar，nvarchar，textBit数据类型：bool日期时间类型：datatime货币型数据：money（1） 创建数据表（2） 删除数据表USE TrainingBasegoif exists(select * from sysobjects where name = Trainee)drop table Traineecreate tab
7、le Trainee(TraineeNo int NOT NULL,TraineeName nvarchar(50) not null,Sex bit not null,GradeID int not null,Phone nvarchar(50) null,Address nvarchar(255) null,BornDate datetime not null,Email nvarchar(50) null,IdentityCard varchar(18) not null)（3）创建删除约束主键约束：主键值必须唯一ALTER TABLE TraineeADD CONSTRANINT PK
8、_Trainee PRIMARY KEY(TraineeNo)非空约束：ALTER TABLE TraineeADD CONSTRANINT PK_Trainee PRIMARY KEY(TraineeNo)唯一约束：ALTER TABLE TraineeADD CONSTRANINT UQ_IdentityCard UNIQUE(IdentityCard)检查约束：ALTER TABLE TraineeADD CONSTRANINT CK_BornDate CHECK(BornDate1980-1-1)默认约束：ALTER TABLE TraineeADD CONSTRANINT DF_Ad
9、dress DEFAULT (地址不详) for Address外键约束：ALTER TABLE TraineeADD CONSTRANINT FK_Grade FOREIGN KEY (GradeId) REFERENCE GradeId4、SQL Server身份验证（1）Windows身份验证（更安全）（2）SQL Server身份验证（Windows身份验证改为SQLServer身份验证的方式：首先，属性更改，然后改安全性中的内容）5、访问权限对数据库服务器控制权限对数据库中数据控制权限添加用户6、导入导出数据7、数据库优化（1）调整数据库结构的设计（2）调整应用程序机构设计（3）调整
10、数据库SQL语句（4）调整服务器内存分配避免多表连接查询使用同一的SQL语句规范批量导入数据时，设置恢复模式为“大容量日志恢复模式”，导入前禁用索引。一、 T_SQL1、运算符算术运算符：+、-、*、/，%赋值运算符：=比较运算符：，=，!=，=，=逻辑运算符：AND，OR，NOT2、表操作插入：一般插入一行，INSERT INTO GradeGradeName VALUES(实习阶段)，可用关键字DEFAULT表示用默认值修改：可更新一行数据，也可更新多行数据，也可能一行数据都不更新，UPDATE 表明 SET（字段名）=更新值WHERE 更新条件 UPDATE Result SET Tra
11、ineeResult = TraineeResult + 5 WHERE TraineeResult 90 AND SubjectNO =2 AND ExamDate= 2010-2-15删除：DELETE FROM 表名 WHERE，删除数据行数与WHERE删除条件确定的行数相关，删除有外键关系的数据时，必须先把外键表中的数据删除掉：DELETE FROM Trainee WHERE TraineeName = 张庆艳，TRUNCATE TABLE 表名，数据不能恢复，效率更高，删除所有行数，但不删除表结构、索引、关系等，不能删除有外键的表3、数据查询（1）简单的查询语句：全部查询：SELE
12、CT * FROM 表名（尽量少使用，效率比较低）；条件查询：SELECT 字段名，字段名，字段名 FROM 表名 WHERE 查询条件使用别名：SELECT 列名 AS 列的别名 FROM 表名；SELECT 列名 列的别名 FROM 表名；SELECT 列的别名=列名 FROM表名（使用中文别名时，可不用引号，但不能使用全角引号；使用的英文别名超过两个单词时，必须使用引号将别名括起来）查询中使用常量列：SELECT 姓名=TraineeName，地址=Address，河北新龙AS实习地点FROM Trainee查询空值：SELECT 字段名 FROM 表名 WHERE 字段名 IS(NOT
13、) NULL限制查询返回的行数：SELECT TOP 数量 字段名 FROM 表名 WHERE条件；按百分比SELECT TOP 数量PERCENT字段名 FROM 表名 WHERE条件顺序排列查询结果：查询语句 ORDER BY 字段名 DESD/ASC（2）查询中使用函数：字符串函数：日期函数：数学函数：系统函数：（3）模糊查询：通配符：替代字符，通配符必须加上LIKELIKE：仅与字符数据类型联合使用BETWEENAND：包含起始值和终止值，起始值不能大于终止值IN（NOT IN）：查询匹配的记录，SELECT TraineeName AS实习生姓名FROM Trainee WHERE
14、Address IN（北京市海淀区，广州，上海虹桥）聚合函数：基于列进行计算SUM：求和，用于数字类型的列AVG：求平均值，所有数字的平均值MAX()和MIN():最值COUNT():求行数；COUNT(*)，检索所有列；COUNT(1),检所使用列；COUNT(列名)检索指定列非空的行数分组查询：使用GROUP BY子句实现分组统计，HAVING，对分组进行筛选SELECT SubjectNo，AVG（TraineeResult）AS课程平均成绩FROM Result GROUP BY SubjectNo HAVING AVG(TraineeResult) =60（4）创建新表：SELECT
15、 INTO,新表必须不存在SELECT Trainee.TraineeName,Trainee.Address,Trainee.Email INTO NewAddressList FROM TraineeWHERE 11（使查询条件不成立）查询示例：SELECT * FROM Trainee AS A WHERE A.TraineeNo IN(SELECT B.TraineeNo FROM Result B WHERE B.TraineeResult 80 AND B.Subject = (SELECT C.SubjectNo FROM Subject C WHERE UPPER( C.Sub
16、jectName )=SQLSERVER)SELECT * FROM Trainee A JOIN Result B ON A.TraineeNo = B.TraineeNoJOIN Subject C ON C.SubjectNo = B.SubjectNoWHERE UPPER(C.SubjectName) = SQLServerAND B.TraineeResult 804、多表查询（1）内联接：从两个或两个以上的表组合中挑选出符合联接条件的数据，如果数据无法满足联接条件则将其丢弃，在内联接中，参与联接的表的地位是平等的。SELECT 表名1.字段名，表名2.字段名， FROM 表名1，
17、表名2 WHERE 表名1.字段名 = 表名2.字段名SELECT Trainee.TraineeName,Result.SubjectNo,Result.ExamDate,Result.TraineeResult FROM Trainee,Result WHERE Trainee.TraineeNo = Result.TraineeNoSELECT 表名1.字段名，表名2.字段名， FROM 表名1，表名2 INNER JOIN 表名2 ON 表名1.字段名=表名2.字段名SELECT Trainee.TraineeName,Result.SubjectNo,Result.ExamDate,
18、Result.TraineeResult FROM Trainee INNER JION Result ON (Trainee.TraineeNo = Result.TraineeNo)（2）外联结：外联结中参与联接的表有主从之分，以主表的每行数据匹配从表的数据列，将符合联接条件的数据直接返回到结果集中；对那些不符合联接条件的列，将被填上NULL值后再返回到结果集中。左外联接 LEFT JOIN = LEFT OUTER JOIN SELECT T.TraineeName,R.SubjectNo,R.TraineeResult FROM Trainee（主表） AS T LEFT JOIN R
19、esult AS R ON T.TraineeNo = R.TraineeNo右外联接 RIGHT JOIN = RIGHT OUTER JOINSELECT T.TraineeName,R.SubjectNo,R.TraineeResult FROM Trainee AS T RIGHT JOIN Result（主表） AS R ON T.TraineeNo = R.TraineeNo（3）交叉连接5、使用Union合并多个查询结果所有查询的列数和列的顺序必须相同；要合并的数据类型必须相同，至少也要可以转换合并的查询结果集的字段名称以第一个查询结果的字段名称为名，其他的查询集的字段名称会被忽
20、略SELECT 查询语句1 UNIONALL将全部行并入结果中，其中包括重复行 SELECT 查询语句2三、数据查询安全性和性能优化1、SQL注入：将SQL代码插入到应用程序的输入参数中，之后，SQL代码将被传递到数据库执行，从而达到对应用程序的攻击目的。原理：检测注入点判断数据库类型传递恶意代码上传木马，盗取账号，获取管理员权限，发现Web目录防范：限制错误信息的输出；使用参数命令传递参数；使用存储过程；限制输入长度；URL重写技术；传递参数尽量不用字符串2、SQL优化：低效SQL危害：系统响应变慢（8秒定律）；死锁；客户失去信心，软件失败根源：硬件原因；没有建索引，或者SQL没有走索引；S
21、QL过于复杂；频繁访问数据库SQL执行原理：解释解析优化编译执行优化SQL语句：完善开发管理；检测SQL查询的效率查询SQL语句查询时间SET STATISTICS IO ONSET STATASTICS TIME ON清楚缓存DBCC DROPCLEANBUFFERSDBCC FREEPROCCACHESQL优化：避免对索引字段进行数值操作；使用Top语句限制返回的数据集；SELECT字段需要多少提取多少；ORDER BY后跟的尽量是索引字段；使用存储过程优化。四、T-SQL程序1、变量全局变量：先声明，后赋值，必须以作为标记前缀：DECLARE variable_name DateType
22、;使用SET或者SELECT为变量赋值，SET一般用于赋给变量指定的数据常量，不支持多个变量赋值和表达式返回多个值，表达式未返回值时，变量被赋NULL值；SELECT从表中查询数据，然后赋给变量，支持多个变量赋值，在返回多个值时，将返回最后一个值赋给变量，在表达式未返回值时，变量保持原值。SET语句执行效率比SELECT语句要高局部变量：必须与标志作为前缀2、数据类型转换使用CAST转换数据类型：CAST（expression(有效表达式) AS data_type(length)）SELECT GradeId AS 阶段,CAST(COUNT(GradeId) AS CHAR(2) + 人
23、AS 人数 FROM Trainee GROUP BY GradeId使用CONVERT转换数据类型：CONVERT (data_type ( length), expression , style)3、逻辑控制语句：顺序结构控制语句：BEGIN-END语句BEGINSql_statementEND分支结构控制语句：IF-ELSE语句和CASE-END语句IF(Boolean-expression)sql_statementELSEsql_statementCASEWHEN 条件1 THEN 结果1WHEN 条件2 THEN 结果2ELSE 其他结果END循环控制语句：WHILE语句WHILE
24、(Boolean_expression)BEGINSql_statement|statement_blockBREAK|CONTINUEEND4、批处理：包含一个或多个SQL语句的组，从应用程序一次性地发送到SQL Server执行，GO是批处理的标志，GO命令和SQL语句不能在同一行里，必须单起一行，表示SQL Server将这些T-SQL语句编译为一个执行单元，提高执行效率：SQL Server规定，如果是建库、建表语句、以及存储过程和视图等，必须在语句末尾添加添加GO批处理标志5、联合查询6、子查询：SELECT（UPDATE、INSERT、DELETE） FROM 表1 WHERE 列
25、1 运算符(子查询)，嵌套查询先从最内层子查询开始分析,子查询可以嵌套在SQL语句中任何表达式出现的位置子查询语句必须放置在括号内，将子查询与比较运算符联合使用，必须保证子查询返回的值不能多于一个SELECTTraineeNo,TraineeName,Sex,BornDate,Address From Trainee WHERE BornDate ( SELECT BornDate FROM Trainee WHERE TraineeName = Lily)IN、NOTIN子查询：IN后面的子查询可以返回多条记录，常用IN替换等于（=）的比较子查询SELECT TraineeName FROM
26、 Trainee WHERE TraineeNo IN (SELECT TraineeNo FROM Result WHERE SubjectNo = (SELECT SubjectNo FROM SubjectWHERE SubjectName = SQLServer) AND TraineeResult)EXISTS：常用与IF EXISTS（子查询），结果非空则EXISTS返回true,否则返回false相关子查询：父查询对子查询产生影响ALL、ANY、SOME子查询：ALL:父查询中大于子查询中所有记录SELECT SubjectName 科目名称,ClassHour 学时 FROM
27、Subject WHERE ClassHuor ALL(SELECT AVG(ClassHour) FROM Subject GROUP BY GradeId)SOME、ANY：父查询中大于子查询中任意一条记录即可SELECT SubjectName 科目名称,ClassHour 学时 FROM Subject WHERE ClassHuor ALL(SELECT AVG(ClassHour) FROM Subject GROUP BY GradeId)8、事务处理事务：一系列的人物组成的逻辑工作单元，这个逻辑单元中的所有任务必须作为一个整体要么全部完成，要么全部失败，保证数据完整性和数据可恢
28、复性。事务特性：原子性，事务的各步操作是不可分的；一致性，但事务完成时，数据必须处于一致状态；隔离性，事务必须是独立的，不应以任何方式依赖于或影响其他事务；永久性，事务完成后，他对数据库的修改被永久保持，事务日志能够保持事务的永久性；开始事务：BEGIN TRANSACTION；提交事务：COMMIT TRANSACTION；回滚事务：ROLLBACK TRANSACTION；编写事务原则：事务尽量简短；事务访问的数据量尽量最少；查询数据时尽量不要使用事务；在事务处理过程中尽量不要出现等待用户输入的操作嵌套事务：事务的内部可以包含其他事务；忽略内层事务的COMMIT语句，只提交外层事务的COM
29、MIT语句；事务的分类：显式事务；隐式事务；自动提交事务锁与事务： 五、视图与索引1、视图：将多个物理数据表抽象为一个逻辑数据表，命名规范：V_视图名，创建视图名：CREATE VIEW 视图名 AS SELECT studentNo，stuName FROM WHERE Grade = 1 WITH CHECK OPTION，强制针对视图执行的修改都必须符合查询语句中设置的条件。删除视图，DROP VIEW 视图名；更新视图，ALTER VIEW 视图名；加密视图：CREATE VIEW 视图名 WITH ENCRYPTION AS SELECT studentNo，stuName FROM
30、 WHERE Grade = 1 WITH CHECK OPTION基本原则：2、索引：提高查询性能，但是影响插入操作性能聚集索引：索引的键值的逻辑顺序决定了表中相应行的物理顺序，一个表中只能有一个聚集索引；适用于范围查询，使用运算符（如BETWEEN、 0begin print -该级别下开设科目信息如下-select GradeName,SubjectName,ClassHourfrom Gradeleft join subject on Grade.GradeId = Subject.GradeIDwhere GradeName = GradeNameselect CourseNum =
31、 COUNT(SubjectNo),HourNum = SUM(ClassHour)from Gradeinner join Subject on Grade.GradeID = Subject.GradeIDwhere GradeName = GradeNamereturn 1endelsereturn 0go注意事项：在存储过程中，可以包含CREATE TABLE语句，但不能包含CREATE PROCEDURE和CREATE VIEW语句；在存储过程中可以声明和使用局部变量；在存储过程中，可以创建和使用临时表；存储过程可以嵌套调用，被调用的存储过程可以使用调用的存储过程中的对象。存储过程的
32、安全与性能优化主要是扩展存储过程，如xp_cmdshell存储过程，可以执行系统命令编写安全扩展存储过程新建类库程序然后，将类库文件拷贝到安装目录的VC目录下,使用命令提示生成密钥文件输入：sn k helperkey.snk(密钥名)编译类库文件，生成动态链接库，输入： csc /t:library /keyfile:helperkey.snk ExtendPro.cs（类库名称）注册动态链接库，输入：regasm /tlb:ExtendPro.tlb ExtendePro.dll /codebase生成.tlb文件在SQLServer中利用OLE自动化存储过程调用dll文件首先，创建对象，
33、Declare Object int -返回创建的对象Declare hr int -过程返回值Declare retrurn varchar(255) -dll方法返回值Declare src varchar(255),desc varchar(255) -过程的错误原因、描述Exec hr = sp_OACreate 命名空间.类名,Object outputIf hr 0BeginExec sp_OAGetErrorInfo Object,src output,desc outputSelect hr = convert(varbinary(4),hr),Source = src,Des
34、cription = descreturnend调用方法，Exec hr = sp_OAMethod Object,方法名,desc outputIf hr 0BeginExec sp_OAGetErrorInfo Object,src output,desc outputSelect hr = convert(varbinary(4),hr),Source = src,Description = descreturnend销毁实例Exec hr = sp_OADestroy ObjectIf hr 0BeginExec sp_OAGetErrorInfo Object,src output,
35、desc outputSelect hr = convert(varbinary(4),hr),Source = src,Description = descreturnend启用与禁用ole自动化存储过程的方法(第二个参数为1时启用，为0时禁用，禁用时，要先保持Sp_configure show advanced options,1的状态，禁用Sp_configure ole automation procedures,0;)Sp_configure show advanced options,1GoReconfigureGoSp_configure ole automation proce
36、dures,1GoReconfigurego创建加密存储过程语法：create proc with encryption as 七、触发器触发器时数据库服务器发生事件时，自动执行的特殊存储过程。作用：强制业务规则；强化约束；跟踪变化；级联运行种类：DML触发器，包括对表或视图发出update、insert或delete语句，又分为after触发器和instead of触发器；DDL触发器，主要是以create、alter和drop开头的语句修改和删除：alter语句和drop语句1、创建after触发器：create trigger 触发器名称 on 表名 for （after） delete
37、，insert，update as sql语句例1、删除：create trigger Employee_Delete on Employeefor deleteas insert into EmployeeOLD select * from deleted例2、更新：Create trigger Bank_Update On Bank For updateAs(if update()可以检测列是否被修改)Declare beforeMoney Money,afterMoney MoneySelect beforeMoney = CurrentMoney from deletedSelect
38、afterMoney = CurrentMoney from insertedIf ABS(afterMoney - beforeMoney) 20000BeginRollback tranEnd例3、插入：Create trigger Borrow_InsertOn BorrowFor insertAsDeclare count intSelect count = BookCount from BookWhere BookId = (select BookId from Inserted)If count 0Update Book set BookCount = BookCount - 1Where BookId = (select BookId from inserted)ElseRollback tran2、创建instead of触发器，适用于表或视图：create trigger 触发器名 on 表名 instead of delete，insert，update as sql语句例：create trigger vw_Book_Borrow_Insteadof_DeleteOn vw_Book_BorrowInstead of deleteAs Delete from Borrow BookId in (select BookId from deleted)3