整理SQLServer数据库基本知识点.docx
《整理SQLServer数据库基本知识点.docx》由会员分享，可在线阅读，更多相关《整理SQLServer数据库基本知识点.docx（21页珍藏版）》请在冰点文库上搜索。
整理SQLServer数据库基本知识点
（整理）SQLServer数据库基本知识点.
SQLServer数据库基本知识点
一、数据类型
数据类型
类型
描述
是否常用
bit
整型
bit数据类型是整型，其值只能是0、1或空值。
这种数据类型用于存储只有两种可能值的数据，如Yes或No、True或False、On或Off
int
整型
int数据类型可以存储从-231（-2147483648）到231?
（2147483647）之间的整数。
存储到数据库的几乎所有数值型的数据都可以用这种数据类型。
这种数据类型在数据库里占用4个字节
是
smallint
整型
smallint数据类型可以存储从-215（-32768）到215（32767）之间的整数。
这种数据类型对存储一些常限定在特定范围内的数值型数据非常有用。
这种数据类型在数据库里占用2字节空间
tinyint
整型
tinyint数据类型能存储从0到255之间的整数。
它在你只打算存储有限数目的数值时很有用。
这种数据类型在数据库中占用1个字节
numeric
精确数值型
numeric数据类型与decimal型相同
decimal
精确数值型
decimal数据类型能用来存储从-1038-1到1038-1的固定精度和范围的数值型数据。
使用这种数据类型时，必须指定范围和精度。
范围是小数点左右所能存储的数字的总位数。
精度是小数点右边存储的数字的位数
money
货币型
money数据类型用来表示钱和货币值。
这种数据类型能存储从-9220亿到9220亿之间的数据，精确到货币单位的万分之一
smallmoney
货币型
smallmoney数据类型用来表示钱和货币值。
这种数据类型能存储从-214748.3648到214748.3647之间的数据，精确到货币单位的万分之一
float
近似数值型
float数据类型是一种近似数值类型，供浮点数使用。
说浮点数是近似的，是因为在其范围内不是所有的数都能精确表示。
浮点数可以是从-1.79E+308到1.79E+308之间的任意数
是
real
近似数值型
real数据类型像浮点数一样，是近似数值类型。
它可以表示数值在-3.40E+38到3.40E+38之间的浮点数
datetime
日期时间型
datetime数据类型用来表示日期和时间。
这种数据类型存储从1753年1月1日到9999年12月31日间所有的日期和时间数据，精确到三百分之一秒或3.33毫秒
是
Smalldatetime
日期时间型
smalldatetime数据类型用来表示从1900年1月1日到2079年6月6日间的日期和时间，精确到一分钟
cursor
特殊数据型
cursor数据类型是一种特殊的数据类型，它包含一个对游标的引用。
这种数据类型用在存储过程中，而且创建表时不能用
是
timestamp
特殊数据型
timestamp数据类型是一种特殊的数据类型，用来创建一个数据库范围内的唯一数码。
一个表中只能有一个timestamp列。
每次插入或修改一行时，timestamp列的值都会改变。
尽管它的名字中有“time”，但timestamp列不是人们可识别的日期。
在一个数据库里，timestamp值是唯一的
Uniqueidentifier
特殊数据型
Uniqueidentifier数据类型用来存储一个全局唯一标识符，即GUID。
GUID确实是全局唯一的。
这个数几乎没有机会在另一个系统中被重建。
可以使用NEWID函数或转换一个字符串为唯一标识符来初始化具有唯一标识符的列
char
字符型
char数据类型用来存储指定长度的定长非统一编码型的数据。
当定义一列为此类型时，你必须指定列长。
当你总能知道要存储的数据的长度时，此数据类型很有用。
例如，当你按邮政编码加4个字符格式来存储数据时，你知道总要用到10个字符。
此数据类型的列宽最大为8000个字符
是
varchar
字符型
varchar数据类型，同char类型一样，用来存储非统一编码型字符数据。
与char型不一样，此数据类型为变长。
当定义一列为该数据类型时，你要指定该列的最大长度。
它与char数据类型最大的区别是，存储的长度不是列长，而是数据的长度
是
text
字符型
text数据类型用来存储大量的非统一编码型字符数据。
这种数据类型最多可以有231-1或20亿个字符
nchar
统一编码字符型
nchar数据类型用来存储定长统一编码字符型数据。
统一编码用双字节结构来存储每个字符，而不是用单字节（普通文本中的情况）。
它允许大量的扩展字符。
此数据类型能存储4000种字符，使用的字节空间上增加了一倍
nvarchar
统一编码字符型
nvarchar数据类型用作变长的统一编码字符型数据。
此数据类型能存储4000种字符，使用的字节空间增加了一倍
ntext
统一编码字符型
ntext数据类型用来存储大量的统一编码字符型数据。
这种数据类型能存储230?
-1或将近10亿个字符，且使用的字节空间增加了一倍
binary
二进制数据类型
binary数据类型用来存储可达8000字节长的定长的二进制数据。
当输入表的内容接近相同的长度时，你应该使用这种数据类型
varbinary
二进制数据类型
varbinary数据类型用来存储可达8000字节长的变长的二进制数据。
当输入表的内容大小可变时，你应该使用这种数据类型
image
二进制数据类型
image数据类型用来存储变长的二进制数据，最大可达231-1或大约20亿字节
二、常用语句（用到的数据库Northwind）
查询语句
简单的Transact-SQL查询只包括选择列表、FROM子句和WHERE子句。
它们分别说明所查询列、查询的?
表或视图、以及搜索条件等。
例如，下面的语句查询Customers表中公司名称为“AlfredsFutterkiste”的ContactName字段和Address字段。
?
SELECTContactName,Address
FROMCustomers
WHERECompanyName='AlfredsFutterkiste'?
（一）选择列表?
选择列表（select_list）指出所查询列，它可以是一组列名列表、星号、表达式、变量（包括局部变量和全局变量）等构成。
?
1、选择所有列?
例如，下面语句显示Customers表中所有列的数据：
?
SELECT*?
FROMCustomers
2、选择部分列并指定它们的显示次序查询结果集合中数据的排列顺序与选择列表中所指定的列名排列顺序相同。
?
例如：
?
SELECTContactName,Address
FROMCustomers
3、更改列标题?
在选择列表中，可重新指定列标题。
定义格式为：
?
列标题as列名?
列名列标题如果指定的列标题不是标准的标识符格式时，应使用引号定界符，例如，下列语句使用汉字显示列标题：
?
SELECTContactNameas联系人名称,Addressas地址
FROMCustomers
4、删除重复行?
SELECT语句中使用ALL或DISTINCT选项来显示表中符合条件的所有行或删除其中重复的数据行，默认?
为ALL。
使用DISTINCT选项时，对于所有重复的数据行在SELECT返回的结果集合中只保留一行。
?
SELECTDISTINCT（Country）
FROMCustomers
5、限制返回的行数?
使用TOPn[PERCENT]选项限制返回的数据行数，TOPn说明返回n行，而TOPnPERCENT时，说明n是?
表示一百分数，指定返回的行数等于总行数的百分之几。
?
例如：
?
SELECTTOP2*?
FROMCustomers
SELECTTOP20PERCENT*?
FROMCustomers
（二）FROM子句?
FROM子句指定SELECT语句查询及与查询相关的表或视图。
在FROM子句中最多可指定256个表或视图，它们之间用逗号分隔。
在FROM子句同时指定多个表或视图时，如果选择列表中存在同名列，这时应使用对象名限定这些列?
所属的表或视图。
例如在Orders和Customers表中同时存在CustomerID列，在查询两个表中的CustomerID时应?
使用下面语句格式加以限定：
?
select*fromOrders,Customers
whereOrders.CustomerID=Customers.CustomerID
在FROM子句中可用以下两种格式为表或视图指定别名：
?
表名as别名?
表名别名?
select*fromOrdersasa,Customersasb
wherea.CustomerID=b.CustomerID
SELECT不仅能从表或视图中检索数据，它还能够从其它查询语句所返回的结果集合中查询数据。
?
例如：
?
select*fromCustomerswhereCustomerIDin（selectCustomerIDfromOrderswhereEmployeeID=4）
此例中，将SELECT返回的结果集合给予一别名CustomerID，然后再从中检索数据。
?
（三）使用WHERE子句设置查询条件?
WHERE子句设置查询条件，过滤掉不需要的数据行。
例如下面语句查询年龄大于20的数据：
?
selectCustomerIDfromOrderswhereEmployeeID=4
WHERE子句可包括各种条件运算符：
?
比较运算符（大小比较）：
>、>=、=、<、<=、<>、!
>、!
范围运算符（表达式值是否在指定的范围）：
BETWEEN…AND…?
NOTBETWEEN…AND…?
列表运算符（判断表达式是否为列表中的指定项）：
IN（项1,项2……）?
NOTIN（项1,项2……）?
模式匹配符（判断值是否与指定的字符通配格式相符）:
LIKE、NOTLIKE?
空值判断符（判断表达式是否为空）：
ISNULL、NOTISNULL?
逻辑运算符（用于多条件的逻辑连接）：
NOT、AND、OR?
1、范围运算符例：
ageBETWEEN10AND30相当于age>=10ANDage<=30?
2、列表运算符例：
countryIN（'Germany','China'）?
3、模式匹配符例：
常用于模糊查找，它判断列值是否与指定的字符串格式相匹配。
可用于char、?
varchar、text、ntext、datetime和smalldatetime等类型查询。
?
可使用以下通配字符：
?
百分号%：
可匹配任意类型和长度的字符
插入语句
语法：
Insertinto表（字段1,字段2,….字段n）values（值1,值2,….值n）
例如：
InsertintoRegion（RegionID,RegionDescription）values（5,'Southern'）
更新语句
语法：
update表set字段1=值1,字段2=值2,字段n=值nwhere条件
如：
updateCustomerssetContactName='John',Address='Avda.dela'whereCustomerID='ALFKI'
where条件同查询语句
删除语句
语法：
deletefrom表where条件
如：
deletefromCustomerswhereCustomerID='ALFKI'
where条件同查询语句
清空表：
truncatetable表清空表中所有数据，表中如果有自增长字段，该字段在插入时会重新开始
注意：
由于更新语句与删除语句会更改表数据对公司业务主要表进行更新与删除操作时一定要先进行备份，然后进行操作
B.环境影响登记表备份语句
2.规划环境影响报告书的审查内容select * into Customers20101019from Customers
目标表（Customers20101019）不存在时会自动创建
2.早期介入原则；
三、存储过程
同建设项目安全评价相关但又有不同的还有：
《地质灾害防治管理办法》规定的地质灾害危险性评估，《地震安全性评价管理条例》中规定的地震安全性评价，《中华人民共和国职业病防治法》中规定的职业病危害预评价等。
优点：
发现规划存在重大环境问题的，审查时应当提出不予通过环境影响报告书的意见；1.存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
2.当对数据库进行复杂操作时（如对多个表进行Update,Insert,Query,Delete时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。
3.存储过程可以重复使用,可减少数据库开发人员的工作量
新增加的六个内容是：
风险评价；公众参与；总量控制；清洁生产和循环经济；水土保持；社会环境影响评价。
4.安全性高,可设定只有某此用户才具有对指定存储过程的使用权
在评估经济效益不能直接估算的自然资源方面，机会成本法是一种很有用的评价技术。
机会成本法特别适用于对自然保护区或具有唯一性特征的自然资源的开发项目的评估。
语法
（2）综合规划环境影响篇章或者说明的内容。
CREATEPROC[EDURE][owner.]procedure_name[;number]
一、安全评价[{@parameterdata_type}
[VARYING][=default][OUTPUT]
][,...n]
[WITH
{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]
[FORREPLICATION]
ASsql_statement[...n]
参数
owner
拥有存储过程的用户ID的名称。
owner必须是当前用户的名称或当前用户所属的角色的名称。
procedure_name
新存储过程的名称。
过程名必须符合标识符规则，且对于数据库及其所有者必须唯一。
;number
是可选的整数，用来对同名的过程分组，以便用一条DROPPROCEDURE语句即可将同组的过程一起除去。
例如，名为orders的应用程序使用的过程可以命名为orderproc;1、orderproc;2等。
DROPPROCEDUREorderproc语句将除去整个组。
如果名称中包含定界标识符，则数字不应包含在标识符中，只应在procedure_name前后使用适当的定界符。
@parameter
过程中的参数。
在CREATEPROCEDURE语句中可以声明一个或多个参数。
用户必须在执行过程时提供每个所声明参数的值（除非定义了该参数的默认值，或者该值设置为等于另一个参数）。
存储过程最多可以有2.100个参数。
使用@符号作为第一个字符来指定参数名称。
参数名称必须符合标识符的规则。
每个过程的参数仅用于该过程本身；相同的参数名称可以用在其它过程中。
默认情况下，参数只能代替常量，而不能用于代替表名、列名或其它数据库对象的名称。
data_type
参数的数据类型。
除table之外的其他所有数据类型均可以用作存储过程的参数。
但是，cursor数据类型只能用于OUTPUT参数。
如果指定cursor数据类型，则还必须指定VARYING和OUTPUT关键字。
对于可以是cursor数据类型的输出参数，没有最大数目的限制。
VARYING
指定作为输出参数支持的结果集（由存储过程动态构造，内容可以变化）。
仅适用于游标参数。
default
参数的默认值。
如果定义了默认值，不必指定该参数的值即可执行过程。
默认值必须是常量或NULL。
如果过程将对该参数使用LIKE关键字，那么默认值中可以包含通配符（%、_、[]和[^]）。
OUTPUT
表明参数是返回参数。
该选项的值可以返回给EXEC[UTE]。
使用OUTPUT参数可将信息返回给调用过程。
Text、ntext和image参数可用作OUTPUT参数。
使用OUTPUT关键字的输出参数可以是游标占位符。
如：
CREATEPROCEDURECustOrdersDetail@OrderIDint
AS
SELECTProductName,
UnitPrice=ROUND（Od.UnitPrice,2）,
Quantity,
Discount=CONVERT（int,Discount*100）,
ExtendedPrice=ROUND（CONVERT（money,Quantity*（1-Discount）*Od.UnitPrice）,2）
FROMProductsP,[OrderDetails]Od
WHEREOd.ProductID=P.ProductIDandOd.OrderID=@OrderID
GO
四：
游标：
游标（Cursor）是处理数据的一种方法，为了查看或者处理结果集中的数据，游标提供了在结果集中一次以行或者多行前进或向后浏览数据的能力。
我们可以把游标当作一个指针，它可以指定结果中的任何位置，然后允许用户对指定位置的数据进行处理。
1.声明游标
2.打开游标
3.读取游标数据
4.关闭游标
5.释放游标
常用于需要把多行的数据进行拼接处理
如：
declare@ContactNamevarchar（50）
declare@AllContactNamevarchar（5000）
set@AllContactName=’’
DECLAREGetContactName_CursorCURSOR
FORselectContactNamefromCustomerswhereCustomerId='ALFKI'
OPENGetContactName_Cursor
FETCHNEXTFROMGetContactName_Cursorinto@ContactName
WHILE@@FETCH_STATUS=0
BEGIN
set@AllContactName=AllContactName+@ContactName
print@AllContactName
FETCHNEXTFROMGetContactName_Cursorinto@ContactName
END
CLOSEGetContactName_Cursor
DEALLOCATEGetContactName_Cursor
五：
常用系统函数和变量：
系统变量：
select@@ERROR --返回最后执行的Transact-SQL语句的错误代码（integer）
select@@IDENTITY --返回最后插入的标识值
SelectUSER_NAME（） --返回用户数据库用户名
select@@ERROR --返回最后执行的Transact-SQL语句的错误代码
select@@CONNECTIONS --返回自上次SQL启动以来连接或试图连接的次数。
selectGETDATE（）--当前时间
select@@CPU_BUSY/100 --返回自上次启动SQL以来CPU的工作时间，单位为毫秒
USEtempdbSelect@@DBTS --为当前数据库返回当前timestamp数据类型的值。
这一timestamp值保证在数据库中是唯一的。
select@@IDENTITY--返回最后插入的标识值
Select@@IDLE --返回SQL自上次启动后闲置的时间，单位为毫秒
Select@@IO_BUSY --返回SQL自上次启动后用于执行输入和输出操作的时间，单位为毫秒
Select@@LANGID --返回当前所使用语言的本地语言标识符（ID）。
Select@@LANGUAGE --返回当前使用的语言名
Select@@LOCK_TIMEOUT --当前会话的当前锁超时设置，单位为毫秒。
Select@@MAX_CONNECTIONS --返回SQL上允许的同时用户连接的最大数。
返回的数不必为当前配置的数值
EXECsp_configure --显示当前服务器的全局配置设置
Select@@MAX_PRECISION--返回decimal和numeric数据类型所用的精度级别，即该服务器中当前设置的精度。
默认最大精度38。
select@@OPTIONS --返回当前SET选项的信息。
Select@@PACK_RECEIVED --返回SQL自启动后从网络上读取的输入数据包数目。
Select@@PACK_SENT --返回SQ自上次启动后写到网络上的输出数据包数目。
Select@@PACKET_ERRORS --返回自SQL启动后，在SQL连接上发生的网络数据包错误数。
Select@@SERVERNAME--返回运行SQL服务器名称。
Select@@SERVICENAME--返回SQL正在其下运行的注册表键名
Select@@TIMETICKS--返回SQL服务器一刻度的微秒数
Select@@TOTAL_ERRORS --返回SQL服务器自启动后，所遇到的磁盘读/写错误数。
Select@@TOTAL_READ --返回SQL服务器自启动后读取磁盘的次数。
Select@@TOTAL_WRITE --返回SQL服务器自启动后写入磁盘的次数。
Select@@TRANCOUNT --返回当前连接的活动事务数。
Select@@VERSION --返回SQL服务器安装的日期、版本和处理器类型。
系统函数：
1.字符转换函数
LOWER（）和UPPER（）
LOWER（）将字符串全部转为小写；UPPER（）将字符串全部转为大写
STR（）把数值型数据转换为字符型数据。
2.去空格函数
3.LTRIM（）把字符串头部的空格去掉。
4.RTRIM（）把字符串尾部的空格去掉。
5.取子串函数
left（）
LEFT（，）
返回character_expression左起integer_expression个字符。
RIGHT（）
RIGHT（
升级会员 