SQL数据库设计和高级查询.docx

1、SQL数据库设计和高级查询SQL数据库设计和高级查询第一章 数据库的设计1.1 为什么需要规范的数据库设计 糟糕的数据库设计表现为以下几点：效率低下 更新和检索数据时会出现许多问题 良好的数据库设计表现为一下几点：效率高 便于进一步扩展 使得使用程序开发变得更容易1.2 设计数据库的步骤 设计过程如下：需求分析阶段概要设计阶段详细设计阶段无论数据库的大小和复杂程度如何，在进行数据库的系统分析时，都可以参考下列基本步骤：a、收集对象 b、标识对象（实体）c、标识每个对象需要存储的详细信息（属性）d、标识对象之间的关系1.3 绘制E-R（实体-关系）图A.实体：客观存在的事物。B.属性：实体的特征

2、。C.关系：各实体之间的联系D.映射基数：通过关系与该实体关联的其他实体的个数（一对一、一对多、多对一、多对多）E.实体关系图：绘制E-R的步骤1.3.2 如何将E-R图转化为表第一步：将各实体转化为对应的表，将各属性转化为各表对应的列第二步：标识每个表的主键列第三步：在表之间体现实体之间的映射关系1.4 数据规范化1.4.1 设计问题信息重复 更新异常 插入异常（无法表示某些信息） 删除异常（丢失有用的信息）1.4.2 规范设计1.第一范式（1NF）：确保每列的原子性。即保证每列都是不可再拆分的。2.第二范式（2NF）：在1NF基础上，确保表中每列和主键相关。即保证每张表描述一件事情。3.第

3、三范式（3NF）：在2NF基础上，确保每列都和主键列直接相关，而非间接。1.4.3 规范化和性能的关系 为了满足三大范式，很有可能造成使得创建数据库中没有带来便捷，反而造成麻烦。所以，为了减少表间连接，提高数据库的访问性能，允许适当的数据冗余列，可能是最何时的数据库设计方案。第二章 数据库的实现2.1 T-SQL语句回顾1.添加数据INSERTA：单条插入： insert into 表名列名 values(插入值)B：多条插入：一共三种方法 a、通过insert select将现有表中数据添加到新表： insert into 新表(新列名) select 原列名 from 原表 b、通过sel

4、ect into将现有表中数据添加到新表： select 新列名 into 新表 from 原表 c、通过union关键字合并数据并插入： insert 表名(列名) select 插入值 union . select 插入值2.修改数据UPDATEupdate 表名 set 列名=更新值 where 更新条件3.查询数据SELECTselect 列名 from 表名 where 查询条件 order by 列名4.删除数据DELETEA：使用delete删除数据：delete from 表名 where 删除条件B：使用truncate table删除数据：清空表，相当于没有删除条件的whe

5、re，此方法比delete执行速度快，使用资源少。此方法不能用于有外键约束引用的表，这种情况需使用delete。truncate table 表名2.2 使用SQL语句创建和删除数据库 数据库文件由3部分组成：主数据文件：*.mdf 可有多个 次要数据文件：*.ndf 可选 日志文件：*.ldf 可有多个 数据库文件其他属性：文件存放位置 文件的增长设置 文件容量设置2.2.1 创建数据库复制内容到剪贴板 代码:create datebase 数据库名称on primary -是否是主数据库文件，一般在创建多数据库文件时使用( name = 存放时数据库的名称, filename = 存放的路

6、径, size = ?mb, -初始大小 maxsize = ?mb, -最大值 filegrowth = ?% -增长率，可使用%,或者?mb)log on( name = 存放时日志文件的名称, filename = 存放的路径, size = ?mb, -初始大小 maxsize = ?mb, -最大值 filegrowth = ?% -增长率，可使用%,或者?mb)go -GO 表示一批 T-SQL 语句结束，GO 之后的 T-SQL 语句属于另一个批处理的范围，在 T-SQL 所有语句的最后都默认有一个 GO。但是，请注意 GO 不是 T-SQL 语句，而只是一个能被SQL Serv

7、er 实用工具识别的命令。2.2.2 删除数据库 drop database 数据库名称 判断数据库是否存在：if exists (select * from sysdatabases where name=数据库名称)2.3 使用SQL语句创建和删除表2.3.1 创建表 create table 表名( 列名 数据类型 列的特征（是否是主键列等）, .)2.3.2 删除表 drop table 表名 判断表是否存在：if exists (select * from sysobjects where name=表名)2.4 使用SQL语句创建和删除约束 常用约束如下：主键约束(Primary

8、Key Constraint) 唯一约束(Unique Constraint) 检查约束(Check Constraint) 默认约束(Default Constraint) 外键约束(Foreign Key Constraint)2.4.1 添加约束 alter table 表名add constraint 约束名称 约束类型 约束说明2.4.2 删除约束 alter table 表名 drop constraint 约束名称2.5 使用SQL语句创建登陆2.5.1 创建登陆账户 登陆有两种方式：SQL身份验证：适合非Window平台用户和Internet用户，需要提供账号密码。 exec

9、sp_addlogin 账户名,密码Windows身份验证：适合Windows平台用户，不需要提供密码，和Windows集成验证。 exec sp_addgrantlogin windows域名域账户 -本机可用计算机名替换windows域名2.5.2 创建数据库用户 exec sp_grantbaccess 登陆账户,数据库用户2.5.3 给数据库用户授权 grant 权限 on 表名 to 数据库用户第三章 T-SQL编程3.1 使用变量3.1.1 局部变量 局部变量以作为前缀，声明语法： declare 局部变量名 变量类型局部变量的赋值有两种方法：使用set： set 局部变量名 =

10、value。一般用于赋给变量指定的数据常量。 使用select：select 局部变量名 = value。一般用于表中查询数据然后赋值给变量。如果select返回的记录多余一条，则只赋最后一条记录。3.1.2 全局变量 全局变量以作为前缀。 error 最后一个T-SQL错误的错误号 identity 最后一次插入的标识指，没有标识列返回null 3.2 输出语句 常用的输出语句有两种：print 局部变量或字符串 select 局部变量 as 自定义列名3.3 逻辑控制语句3.3.1 if-else条件语句 if (条件)begin /语句块开始 . /执行的语句end /语句块的结束 el

11、sebegin.end3.3.2 while循环语句 while(条件)begin.可使用continue 或 breakend3.3.3 case多分支语句 casewhen 条件 then 结果.else 其他结果End3.4 批处理语句批处理是包含一个或多个 SQL 语句的组，从应用程序一次性地发送到SQL Server执行。SQL Server 将批处理语句编译成一个可执行单元，此单元称为执行计划。执行计划中的语句每次执行一条。GO是批处理的标志，表示SQL Server将这些T-SQL语句编译为一个执行单元，提高执行效率。一般是将一些逻辑相关的业务操作语句，放置在同一批中，这完全由业

12、务需求和代码编写者决定 。SQLServer规定：如果是建库、建表语句、以及我们后面学习的存储过程和视图等，则必须在语句末尾添加 GO 批处理标志。一个变量的作用范围就是一个批处理语句。第四章 高级查询4.1 简单子查询 简单查询即是用select语句作为筛选的条件。一般来说，表连接都可以用子查询替换，但反过来说却不一定。有的子查询不能用表连接来替换。子查询比较灵活、方便，形式多样。表连接更适合查看多表的数据。4.2 in和not in子查询 在Sql Server中，当子查询跟随在=、!=、=之后，子查询只能返回单条记录。如果返回多条查询记录，应使用关键字in。4.3 exists和not

13、exists子查询 exists也可作为where语句的子查询，但一般用于if语句的存在检测。1、查询所有数据行和列select * from 表名2、查询部分行条件查询select 列名 from 表名 where 列名 = valueselect 列名 from 表名 where 列名 value3、在查询中使用列名select 列名 as 新列名 from 表名/合并列然后起新列名select 列名+列名 as 新列名4、查询空行select 列名 from 表名 where 列名 is nullselect 列名 from 表名 where 列名 is not null5、在查询中使用

14、常量列select 常量值 = 新列名 from 表名6、查询返回限制的行数/返回具体行数select top 行数 列名 from 表名/返回x%的行数select top 百分数 percent 列名 from 表名7、查询排序/升序，默认升序select * from 表名 order by 排序列 acs /降序select * from 表名 order by 排序列 desc8、使用like模糊查询select * from 表名 where 列名 like 模糊条件 /这里的模糊条件和正则表达式很类似。9、使用between在某个范围内进行查询select * from 表名 w

15、here 列名 between values_1 and values_210、使用in在列举值内进行查询select * from 表名 where 列名 in (values_1,values_2) /还可以使用not in11、聚合函数sum/sum只能用于数字类型的列，如有查询，只能返回一个值select sum(列名) from 表名12、聚合函数avg/avg只能用于数字类型的列，如有查询，只能返回一个值select avg(列名) from 表名13、聚合函数max和min/max最大值select max(列名) from 表名/min最小值select min(列名) fro

16、m 表名14、聚合函数countselect count(列名) from 表名15、使用group by进行分组查询select 分组列名 from 表名 group by 分组列16、使用having子句进行分组筛选select 分组列名 from 表名 group by 分组列 having 条件17、内联接/在where子句中置顶联接条件select * from 表名1,表名2 where 表名1.列名 = 表名2.列名/在from子句中使用innerjoin.onselect * from 表名1 inner join 表名2 on 表名1.列名 = 表名2.列名18、外联接/左外

17、联接select * from 表名1 left outer join 表名2 on 表名1.列名 = 表名2.列名/右外联接select * from 表名1 right outer join 表名2 on 表名1.列名 = 表名2.列名第五章 事务、索引和视图5.1 事务5.1.1 为什么需要事务 不管是生活中，还是程序中，每当我们处理一个事件时，很有可能因为一个问题而导致这个事件的非正常运行。如果我们知道这个问题应该停止事件的不正常运行。5.1.2 什么是事务 事务是一种机制、一个操作序列，它包含了一族数据库操作命令，并且所有的命令作为一个整体一起向系统提交或撤销操作请求。事务是一个不可

18、分割的逻辑单元，在数据库系统上执行并发操作时事务是作为最小的控制单元来使用的。他特别使用于多用户同时操作的数据库系统。一个逻辑工作单元必须有4个属性：原子性（Atomicity）：事务是一个完整的操作，它的各元素是不可分的，事务中任何元素失败，正式事务失败。一损俱损。 一致性（Consistency）：当事务完成时，数据必须处于一致状态。 隔离性（Isolation）：对数据进行修改的所有并发事务是彼此隔离的，这表名事务必须是独立的，它不应以任何方式依赖于或影响其他事务。 持久性（Durability）：事务完成之后，它对于系统的影响是持久性的。即修改即使出现系统故障，也将一直保持。5.1.3

19、 如何创建事务T-SQL中的事务：开始事务：begin transaction 提交事务：commit transaction 回滚（撤销）事务：rollback tracsaction事务的分类：显示事务：用begin transaction明确指定事务的开始。 隐式事务：通过设置set implicit_transactions on语句，将隐式事务模式设置为打开。当以隐式事务操作时，Sql Server将在提交或回滚事务后自动启动新事务。无法描述事务的开始，只需提交或回滚（撤销）每个事务。 自动提交事务：这是Sql Server默认模式，它将每条单独的T-SQL语句视为一个事务。如果成功

20、执行就自动提交，反之则自动回滚（撤销）。5.2 索引5.2.1 什么是索引 数据库中的索引是某个表中一列或者若干列值的集合，以及相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引：它是SQL Server编排数据的内部方法，为Sql Server提供一种方法来编排查询数据的路径。 索引页：数据库中存储索引的数据页。索引页存放检索数据行关键字页以及该数据行地址指针。 通过使用索引可以大大提高数据库的检索速度，改善数据库性能。索引可分为以下几类：唯一索引：唯一索引不允许两行具有相同的索引值。创建唯一约束自动创建唯一索引。尽管唯一索引有助于找到信息，但为了获得最佳性能，建议使用主键约束。 主键

21、索引：在数据库关系图中为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。主键索引要求主键中每个值是唯一的。当在查询中使用主键索引时，它还允许快速访问数据库。 聚集索引与非聚集索引：在聚集索引中，表中各行的物理顺序与键值的逻辑（索引）顺序相同。表只能包含一个聚集索引。聚集索引比非聚集索引有更快的数据访问速度。一个表中可以有多个非聚集索引。设置某列为主键，该列默认为聚集索引。5.2.2 如何创建索引使用Microsoft Sql Server Management Studio创建索引 使用T-SQL语句创建索引： create unique clustered | nonclus

22、tered index index_name on table_name (column_name , column_name.)with fillfactor = x/ unique 指定唯一索引，可选/ clustered 、nonclustered指定聚集索引还是非聚集索引/ fillfactor 表示填充因子，置顶一个0100的值，该值指示索引页填满的空间所占的百分比创建索引的规则和标准(该列用于频繁搜索,该列用于对数据进行排列)请不要对以下列创建索引(列中仅包含几个不同的值,表中仅包含几行.)为小型表创建索引可能不太划算,因为在SQL索引中搜索数据所花费的时间比在表中搜索所花费的时间

23、更长。5.3 视图5.3.1 什么是视图 视图是另种查看数据库中一个或多个表中的数据的方法。视图是一种虚拟表，通常是作为来自一个或多个表的行或列的子集创建的。当然，也可包含所有行和列。视图并不是数据库中存储数据值的集合。它的行和列来自查询中引用的表。 视图充当着查询中置顶的表的筛选器。定义试图的查询一个或多个表，也可基于其他视图、当前数据库或其他数据库。 视图给用户和开发人员带来的好处：A：对最终用户的好处：结果更容易理解；获得数据更容易。B：对开发人员的好处：限制数据检索更容易；维护应用程序更方便。5.3.2 如何创建视图使用T-SQL语句创建试图：Create view view_name

24、 As 第六章 存储过程6.1 什么是存储过程 存储过程就是一个方法体，里面放了些我们想要执行的东西。存储过程可包含逻辑控制语句和数据操作语句，它可以接收参数、输出参数、返回单个或多个结果集以及返回值。存储过程在创建时即在服务器上进行编译，所以执行起来比单个SQL语句快。 存储过程优点：1. 执行速度快。存储过程创建时，就已经通过语法检查和性能优化，在执行时无须每次编译。存储在数据库服务器，性能高。2. 允许模块化设计。只需创建一次存储过程并将其存储在数据库中，以后即可在程序中调用该过程任意次。3. 提高系统安全性。可将存储过程作为用户存取数据的管道。可以限制用户对数据表的存取权限。建立特定的

25、存储过程供用户使用，完成对数据库的访问。存储过程的定义文本可以被加密，使用户不能查看其内容。4. 减少网络流量。一个需要数百行T-SQL代码的操作由一条执行过程代码的单独语句就可实现，而不需要在网络中发送数百行代码。6.2 常用的系统存储过程1.由系统定义，存放在master数据库中。可以再任何数据库中运行系统存储过程，但执行结果会反映在当前数据库中。系统存储过程的名称都以“sp_”开头。常用的系统存储过程系统存储过程说明2.系统存储过程调用：EXEC 存储过程名。3.还有一个常用扩展存储过程：xp_cmdshell：可以执行DOS命令下的一些的操作 调用语法： EXEC xp_cmdshel

26、l DOS命令 NO_OUTPUT6.3 用户定义的存储过程创建存储过程时，需要注意事项：1. 只能在当前数据库中创建存储过程。2. 数据库的所有者可以创建存储过程，也可以授权其他用户创建存储过程。3. 存储过程与其他的SQL语句分开，单独放在一个批处理语句当中。创建存储过程语法： CREATE PROCEDURE 存储过程名 参数1 数据类型 = 默认值 OUTPUT, , 参数n 数据类型 = 默认值 OUTPUT AS SQL语句 GO和C语言的函数一样，参数可选参数分为输入参数、输出参数 输入参数允许有默认值 存储过程的参数分两种： 输入参数 输出参数 输入参数： 用于向存储过程传入值

27、，类似C语言的按值传递; 输出参数： 用于在调用存储过程后， 返回结果，类似C语言的 按引用传递; 6.4 处理错误信息可以使用PRINT语句显示错误信息，但这 些信息是临时的，只能显示给用户 RAISERROR 显示用户定义的错误信息时：可指定严重级别；设置系统变量ERROR；记录所发生的错误等。RAISERROR语句的用法如下： RAISERROR (msg_id | msg_str,severity, state WITH option,.n) msg_id：在sysmessages系统表中指定用户定义错误信息msg_str：用户定义的特定信息，最长255个字符severity：定义严重性级别。用户可使用的级别为018级state：表示错误的状态，1至127之间的值option：指示是否将错误记录到服务器错误日志中