T-SQL总结

数据库的设计

（1）什么是数据库的设计？

数据库设计就是将数据库中的数据实体以及这些数据实体之间的关系，进行规划和结构化的过程。

 

（2）整个项目开发的的流程：

需求分析à概要设计à详细设计à代码编写à运行测试à打包发行

 

（3）绘制E-R图的工具：

Microsoft Visio

 

（4）三大范式理论：

Ø  第一范式(NormalFormate, 1NF)的目标是确保每列的原子性。如果每列（或者每个属性值）都是不可再分的最小数据单元（也称为最小的原子单元），则满足第一范式。

 

Ø  第二范式(2NF)在第一范式的基础上更进一层，其目标是确保表中的每列都和主键相关。

 

Ø  第三范式(3NF)在第二范式的基础上更进一层，第三范式的目标是确保每列都和主键直接相关，而不是间接相关。

数据库的实现

（1）创建数据库的语法：

create database 数据库名

  on primary

(

     <数据文件参数>  [,……] [<文件组参数>]

)

log on

(

     {<日志文件参数>}  [,……]

)

（2）数据文件的具体参数如下。

name=逻辑文件名,

filename=物理文件名,

size=大小

maxsize={最大容器|unlimited}

filegrowth=增长量

（3）文件组的具体参数如下

filegroup 文件组明 <文件参数>[,……n]

（4）删除数据库。

语法：drop database 数据库名

一般前面会加：

if exists(select * from sysdatabases wherename=’MySchool’)

（5）SQL Server中的数据类型

类型

数据类型

描述

整型

int

占用4个字节的整数

smallint

占用2个字节的整数

tinyint

占用一个字节的整数

浮点型

real

近似数值类型

float

近似数值类型

decimal

固定精度和范围的数值类型。使用时，必须制定范围和精度。范围是小数点左右所能存储的数字的总位数，精度是小数点右边存储的数字的位数

numeric

与decimal相同

字符型

char

固定长度非Unicode字符数据，最大8000个字节

varchar

可变长度非Unicode字符数据。

text

非Unicode字符数据

Unicode型

nchar

固定长度Unicode字符数据，最大4000个字节

nvarchar

可变程度Unicode字符数据。

ntext

Unicode字符数据

是/否型

bit

只能是0、1或空值

二进制型

binary

定长的二进制数据

varbinary

变长的二进制数据

image

变长的二进制数据

货币型

money

固定精度和范围的数值型数据

smallmoney

同money，但小于money的取值范围

日期时间型

datetime

表示日期和时间，可以精确到11300秒或3.33毫秒

smalldatetime

同datetime，精确到一分钟

特殊类型

timestamp

创建一个数据库范围内的唯一时间戳

uniqueidentifier

一个全局的唯一标识符

 

（6）创建表

create table 表名

 (

         列1 数据类型 列的特征,

         列2 数据类型 列的特征,

         …….

)

 

（7）删除表

drop table 表名

 

（8）数据合法的机制

Ø  实体完整性

Ø  域完整性

Ø  引用完整性

Ø  自定义完整性

（9）约束类型

Ø  主键约束(PrimaryKey Constraint)：要求主键列数据唯一，并且不允许为空。

Ø  非空约束(Not Null)：要求列不能存在空值。

Ø  唯一约束(UniqueConstraint)：要求该列的值必须唯一，允许为空，但只能出现一个空值。

Ø  检查约束(CheckConstraint)：某列的取值范围限制、格式限制等。

Ø  默认约束(DefaultConstraint)：某列的默认值。

Ø  外键约束(ForeignKey Constraint)：用于在两表之间建立关系，需要指定引用主表的哪一列。

 

（10）添加约束

alter table 表名

       add constraint 约束名 约束类型 具体的约束说明

 

（11）删除约束

alter table 表名

       drop constraint 约束名

 

（12）打开SQL Server联机丛书的三种方法：

1.        工具栏à目录

2.        索引à输入关键字查找

3.        光标放置需要查找语法的关键字上方，按F1键。

 

SQL编程

（1）局部变量

声明语法：declare @变量名变量类型

赋值语法：

                   set@变量名=值

或者

                   select@变量名=值

set语句和select语句的区别

 

set

select

同时对多个变量赋值

不支持

支持

表达式返回多个值时

出错

将返回的最后一个值赋给变量

表达式未返回值时

变量被赋值为NULL

变量保持原型

 

 

（2）全局变量

SQL Server中的所有全局变量都使用两个@@符号作为前缀

 

（3）输出语句

print 局部变量或字符串

select 局部变量 as 自定义列名

使用print语句要求以单个或字符串表达式作为参数，而“+”运算符作为连接两个字符串的连接符，要求“+”运算符两侧的操作数的数据类型必须一致，

正解à例：print ‘当前错误号’+convert(varchar(5),@@error)

 

（4）cast()和convert()函数

例：cast(@Result as varchar(10))

语法：     cast(表达式 as 数据类型)

                   convert(数据类型[(长度)], 表达式[,样式])

 

（5）begin-end 语句

语法：

         begin

                   语句或语句块

         end

begin-end语句的作用类似于C#语言的“{  }”，表示语句块的开始和结束。

 

（6）if-else 语句

语法：

         if(条件)

                   语句或语句块1

         else

                   语句或语句块2

 

（7）while循环语句

语法：

         while(条件)

                   begin

                            语句或语句块

                            [begin | continue ]

                   end

 

（8）case 多分支语句

语法1：

         case

                   when条件1   then   结果1

                   when条件2   then   结果2

                   [else其他结果]

         end

语法2：

         case列名

                   when固定值1   then   结果1

                   when固定值2   then   结果2

         end

（9）不显示/显示“n行受影响”

set nocount on    --不显示

set nocount off         --显示

 

（10）批处理（go指令）

它是一条或多条SQL语句的集合，SQL Server将批处理指令编译成一个可执行单元，此单元称为执行计划。每个批处理可以编译成单个执行计划，从而提高执行效率。如果批处理包含多条SQL语句，则执行这些语句所需的所有优化的步骤将编译在单个执行计划中。

当一个批处理出现错误时，并不会影响其他批处理总SQL代码的运行。

好处：简化数据库的管理。

高级查询

（1）简单子查询

语法：select …… from 表1  where  列1 > (子查询)

SQL Server执行时，先执行子查询部分，求出子查询部分的值，再执行整个父查询。

注意：表连接都可以用子查询替换，但反过来却不一定。

 

（2）in 和 not in子查询

使用in关键字可以使父查询匹配子查询返回的多个单列值。

 

（3）exists子查询

语法：

         ifexists(子查询)

                   语句

（4）子查询注意事项

Ø  子查询语句可以嵌套在SQL语句中任何表达式出现的位置。

Ø  在子查询的select字句中不能出现text、ntext、image数据类型的值。

Ø  只出现在子查询中而没有出现在父查询中的表不能包含在输出列中。

事务、视图、索引和触发器

（1）事务

事务是单个的工作单元。如果某一事务成功，则在该事务中进行的所有数据更改均会提交，成为数据库中的永久组成部分。如果事务遇到错误且必须取消或回滚，则所有数据更改均被清除。

 

（2）事务的4个特效。

Ø  原子性：事务是一个完整的操作。事务的各元素是不可分的（原子的）。

Ø  一致性：当事务完成时，数据必须处于一致状态。

Ø  隔离性：对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务。

Ø  持久性：事务的持久性不管系统是否发生了故障，事务处理的结果都是永久的。

 

（3）执行事务

开始事务：

         begintransaction

提交事务：

         committransaction

回滚事务：

         rollbacktransaction

 

（4）事务分类

Ø  显示事务：用begintransaction 明确执行事务的开始

Ø  隐式事务：通过设置setimplicit_transactions on 语句，将隐式事务模式设置为打开。当以隐式事务操作时SQL Server将在提交或回滚事务后自动启动新事务。不需要描述每个事务的开始，字谣提交或回滚每个事务即可。

Ø  自动提交事务：这是SQLServer的默认模式，它将每条单独的T-SQL语句视为一个事务。如果成功执行，则自动提交。如果错误，则自动回滚。

注：实际开发中最常用的就是显示事务，它明确地指定事务的开始边界。

 

（5）视图

视图是另一种查看数据库中一个或多个表中数据的方法。视图是一种虚拟表，通常是作为来自一个或多个表的行或列的子集创建的。

视图通常进行以下3种操作：

Ø  筛选表中的行。

Ø  防止未经许可的用户访问敏感数据。

Ø  将多个物理数据表抽象为一个逻辑数据表。

 

（6）视图的好处

1、对最终用户的好处：

结果更容易理解。创建视图时，可以将列名改为有意义的名称，使用户更容易理解列所代表的内容。在视图中修改列明不会影响基表的列名。

获得数据更容易。很多人对SQL不太了解，因此对于他们来说，创建对多个表的复杂查询很困难，可以通过创建视图来方便用户访问多个表中的数据。

 

2、对开发人员的好处

Ø  限制数据检索更容易。开发人员有时需要隐藏某些行或列中的信息。通过使用视图，用户可以很灵活的访问它们需要的数据，同时保证同一个表或其他表中的其他数据的安全性。要实现这一目标，可以在创建视图时将对用户保密的列排除在外。

Ø  维护应用程序更方便。调试视图比调试查询更容易，跟踪视图中各个步骤的错误更为容易，这是因为所有的步骤都是视图的组成部分。

（7）创建视图

有两种方式：

1、  使用MicrosoftSQL Server Management Studio创建。

2、  使用SQL语句：

语法：

           create view view_name

           as

           <select 语句>

 

删除视图：

if exists(select * from sysobjects wherename=’view_name’)

drop view view_name

 

个人总结视图：

虽然可以利用限制列名的方式查询想显示数据，但不及单单用一条查询语句查询来的快。如：
select * from view_name

视图就是把代码简化吧，方便调用，仅此而已。

 

（8）索引概述

索引提供指针以指向存储在表中指定列的数据值，然后根据指定的排序次序排列这些指针。

什么是索引？

在数据库中，索引使数据库程序无须对整个表进行扫描，就可以在其中找到所需数据。数据库中的索引是某个表中一列或若干列值的集合和响应的指向表中物理标识这些值的数据页的逻辑指针清单。

 

（9）索引的6类

1、唯一索引：

         唯一索引不允许两行具有相同的索引值。

2、  主键索引

3、  聚集索引（一个表只能包含一个）

在聚集索引中，表中各行的物理顺序与键值的逻辑（索引）顺序相同。

4、  非聚集索引

5、  复合索引

6、  全文索引

 

（10）创建索引

有两种方法：

1、  使用MicrosoftSQL Server Managenment Studio创建索引。

2、  使用T-SQL语句创建索引。

语法：

create [unique][clustered | nonclustered] index index_name

on table_name(column_name[, column_name]…)

[withfillfactor=x]

 

其中：

unique指唯一索引，可选。

clustered、nonclustered指定是聚集索引还是非聚集索引。可选。

fillfactor表示填充印子，指定一个0~100的值，该值指示索引页填满的空间所占的百分比。

 

删除索引：

语法：

         dropindex tabel_name.index_name

删除表时，该表的所有索引将同时被删除。

如果要删除表的所有索引，则先要删除非聚集索引，再删除聚集索引。

 

索引的使用注意事项：

Ø  频繁搜索的列

Ø  经常用作查询选择的列

Ø  经常排序，分组的列

Ø  经常用作连接的列（主键/外键）

 

（11）查看索引

有两种方式查看已建立的索引信息：

1、  用系统存储过程sp_helpindex查看

语法：

           sp_helpindextable_name

例：exec sp_helpindex result

2、  用视图sys.indexes查看

语法：

           select * from sys.indexes

 

（12）触发器

触发器更多用在insert、update、delete

create trigger myTrigger

on jiao

for insert

as    

         ……….

         begin

         end

go

 

存储过程

（1）概念

存储过程是在数据库管理系统中保存的、预先编译的、能实现某种功能的SQL程序，它是数据库应用中运用比较广泛的一种数据对象。

（2）存储过程的优点

1、模块化程序设计

2、执行速度快，效率高

3、减少网络流量

4、具有良好的安全性

 

（3）系统存储过程

SQL Server提供系统存储过程，它们是一组预编译的T-SQL语句。系统存储过程提供了管理数据库和更新表的机制，并充分从系统表中检索信息的快捷方式。

 

SQL Server的系统存储过程的名称以“sp_”开头，并存在Resource数据库中。

 

（4）常用的扩展存储过程

语法：

         execxp_cmdshell DOS命令 [no_output]

 

（5）用户自定义的存储过程

一个完整的存储过程包括以下3部分。

Ø  输入参数和输出参数。

Ø  在存储过程中执行的T-SQL语句。

Ø  存储过程的返回值。

 

（6）创建存储过程

语法：

         createproc[edure]  存储过程名

                   [{@参数1        数据类型}        [=默认值]         [OUTPUT],

                   …………

                    {@参数N       数据类型}        [=默认值]         [OUTPUT],

                   ]

         AS

                   <SQL语句>

 

删除存储过程：

drop proc[edure]存储过程名