SQLServer数据库基本知识点教案资料

1、Good is good, but better carries it.精益求精，善益求善。SQLServer数据库基本知识点-SQLServer数据库基本知识点一、数据类型数据类型类型描述是否常用bit整型bit数据类型是整型，其值只能是0、1或空值。这种数据类型用于存储只有两种可能值的数据，如Yes或No、True或False、On或Offint整型int数据类型可以存储从-231(-2147483648)到231(2147483647)之间的整数。存储到数据库的几乎所有数值型的数据都可以用这种数据类型。这种数据类型在数据库里占用4个字节是smallint整型smallint数据类型可以存

2、储从-215(-32768)到215(32767)之间的整数。这种数据类型对存储一些常限定在特定范围内的数值型数据非常有用。这种数据类型在数据库里占用2字节空间tinyint整型tinyint数据类型能存储从0到255之间的整数。它在你只打算存储有限数目的数值时很有用。这种数据类型在数据库中占用1个字节numeric精确数值型numeric数据类型与decimal型相同decimal精确数值型decimal数据类型能用来存储从-1038-1到1038-1的固定精度和范围的数值型数据。使用这种数据类型时，必须指定范围和精度。范围是小数点左右所能存储的数字的总位数。精度是小数点右边存储的数字的位数

3、money货币型money数据类型用来表示钱和货币值。这种数据类型能存储从-9220亿到9220亿之间的数据，精确到货币单位的万分之一smallmoney货币型smallmoney数据类型用来表示钱和货币值。这种数据类型能存储从-214748.3648到214748.3647之间的数据，精确到货币单位的万分之一float近似数值型float数据类型是一种近似数值类型，供浮点数使用。说浮点数是近似的，是因为在其范围内不是所有的数都能精确表示。浮点数可以是从-1.79E+308到1.79E+308之间的任意数是real近似数值型real数据类型像浮点数一样，是近似数值类型。它可以表示数值在-3.4

4、0E+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数据类型是一种特殊的数据类型，用来创建一个数

5、据库范围内的唯一数码。一个表中只能有一个timestamp列。每次插入或修改一行时，timestamp列的值都会改变。尽管它的名字中有“time”，但timestamp列不是人们可识别的日期。在一个数据库里，timestamp值是唯一的Uniqueidentifier特殊数据型Uniqueidentifier数据类型用来存储一个全局唯一标识符，即GUID。GUID确实是全局唯一的。这个数几乎没有机会在另一个系统中被重建。可以使用NEWID函数或转换一个字符串为唯一标识符来初始化具有唯一标识符的列char字符型char数据类型用来存储指定长度的定长非统一编码型的数据。当定义一列为此类型时，你必须

6、指定列长。当你总能知道要存储的数据的长度时，此数据类型很有用。例如，当你按邮政编码加4个字符格式来存储数据时，你知道总要用到10个字符。此数据类型的列宽最大为8000个字符是varchar字符型varchar数据类型，同char类型一样，用来存储非统一编码型字符数据。与char型不一样，此数据类型为变长。当定义一列为该数据类型时，你要指定该列的最大长度。它与char数据类型最大的区别是，存储的长度不是列长，而是数据的长度是text字符型text数据类型用来存储大量的非统一编码型字符数据。这种数据类型最多可以有231-1或20亿个字符nchar统一编码字符型nchar数据类型用来存储定长统一编码

7、字符型数据。统一编码用双字节结构来存储每个字符，而不是用单字节(普通文本中的情况)。它允许大量的扩展字符。此数据类型能存储4000种字符，使用的字节空间上增加了一倍nvarchar统一编码字符型nvarchar数据类型用作变长的统一编码字符型数据。此数据类型能存储4000种字符，使用的字节空间增加了一倍ntext统一编码字符型ntext数据类型用来存储大量的统一编码字符型数据。这种数据类型能存储230-1或将近10亿个字符，且使用的字节空间增加了一倍binary二进制数据类型binary数据类型用来存储可达8000字节长的定长的二进制数据。当输入表的内容接近相同的长度时，你应该使用这种数据类型

8、varbinary二进制数据类型varbinary数据类型用来存储可达8000字节长的变长的二进制数据。当输入表的内容大小可变时，你应该使用这种数据类型image二进制数据类型image数据类型用来存储变长的二进制数据，最大可达231-1或大约20亿字节常用语句(用到的数据库Northwind)查询语句简单的Transact-SQL查询只包括选择列表、FROM子句和WHERE子句。它们分别说明所查询列、查询的表或视图、以及搜索条件等。例如，下面的语句查询Customers表中公司名称为“AlfredsFutterkiste”的ContactName字段和Address字段。SELECTCont

9、actName,AddressFROMCustomersWHERECompanyName=AlfredsFutterkiste(一)选择列表选择列表(select_list)指出所查询列，它可以是一组列名列表、星号、表达式、变量(包括局部变量和全局变量)等构成。1、选择所有列例如，下面语句显示Customers表中所有列的数据：SELECT*FROMCustomers2、选择部分列并指定它们的显示次序查询结果集合中数据的排列顺序与选择列表中所指定的列名排列顺序相同。例如：SELECTContactName,AddressFROMCustomers3、更改列标题在选择列表中，可重新指定列标题。定

10、义格式为：列标题as列名列名列标题如果指定的列标题不是标准的标识符格式时，应使用引号定界符，例如，下列语句使用汉字显示列标题：SELECTContactNameas联系人名称,Addressas地址FROMCustomers4、删除重复行SELECT语句中使用ALL或DISTINCT选项来显示表中符合条件的所有行或删除其中重复的数据行，默认为ALL。使用DISTINCT选项时，对于所有重复的数据行在SELECT返回的结果集合中只保留一行。SELECTDISTINCT(Country)FROMCustomers5、限制返回的行数使用TOPnPERCENT选项限制返回的数据行数，TOPn说明返回n

11、行，而TOPnPERCENT时，说明n是表示一百分数，指定返回的行数等于总行数的百分之几。例如：SELECTTOP2*FROMCustomersSELECTTOP20PERCENT*FROMCustomers(二)FROM子句FROM子句指定SELECT语句查询及与查询相关的表或视图。在FROM子句中最多可指定256个表或视图，它们之间用逗号分隔。在FROM子句同时指定多个表或视图时，如果选择列表中存在同名列，这时应使用对象名限定这些列所属的表或视图。例如在Orders和Customers表中同时存在CustomerID列，在查询两个表中的CustomerID时应使用下面语句格式加以限定：se

12、lect*fromOrders,CustomerswhereOrders.CustomerID=Customers.CustomerID在FROM子句中可用以下两种格式为表或视图指定别名：表名as别名表名别名select*fromOrdersasa,Customersasbwherea.CustomerID=b.CustomerIDSELECT不仅能从表或视图中检索数据，它还能够从其它查询语句所返回的结果集合中查询数据。例如：select*fromCustomerswhereCustomerIDin(selectCustomerIDfromOrderswhereEmployeeID=4)此例中

13、，将SELECT返回的结果集合给予一别名CustomerID，然后再从中检索数据。(三)使用WHERE子句设置查询条件WHERE子句设置查询条件，过滤掉不需要的数据行。例如下面语句查询年龄大于20的数据：selectCustomerIDfromOrderswhereEmployeeID=4WHERE子句可包括各种条件运算符：比较运算符(大小比较)：、=、=、=、!、!=10ANDage=302、列表运算符例：countryIN(Germany,China)3、模式匹配符例：常用于模糊查找，它判断列值是否与指定的字符串格式相匹配。可用于char、varchar、text、ntext、dateti

14、me和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.delawhereCustomerID=ALFKIwhere条件同查询语句删除语句语

15、法：deletefrom表where条件如：deletefromCustomerswhereCustomerID=ALFKIwhere条件同查询语句清空表：truncatetable表清空表中所有数据，表中如果有自增长字段，该字段在插入时会重新开始注意：由于更新语句与删除语句会更改表数据对公司业务主要表进行更新与删除操作时一定要先进行备份，然后进行操作备份语句select*intoCustomers20101019fromCustomers目标表（Customers20101019）不存在时会自动创建存储过程优点：1.存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQ

16、L语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。2.当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。3.存储过程可以重复使用,可减少数据库开发人员的工作量4.安全性高,可设定只有某此用户才具有对指定存储过程的使用权语法CREATEPROCEDUREcedure_name;numberparameterdata_typeVARYING=defaultOUTPUT,.nWITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCR

17、YPTIONFORREPLICATIONASsql_statement.n参数owner拥有存储过程的用户ID的名称。owner必须是当前用户的名称或当前用户所属的角色的名称。procedure_name新存储过程的名称。过程名必须符合标识符规则，且对于数据库及其所有者必须唯一。;number是可选的整数，用来对同名的过程分组，以便用一条DROPPROCEDURE语句即可将同组的过程一起除去。例如，名为orders的应用程序使用的过程可以命名为orderproc;1、orderproc;2等。DROPPROCEDUREorderproc语句将除去整个组。如果名称中包含定界标识符，则数字不应包含

18、在标识符中，只应在procedure_name前后使用适当的定界符。parameter过程中的参数。在CREATEPROCEDURE语句中可以声明一个或多个参数。用户必须在执行过程时提供每个所声明参数的值（除非定义了该参数的默认值，或者该值设置为等于另一个参数）。存储过程最多可以有2.100个参数。使用符号作为第一个字符来指定参数名称。参数名称必须符合标识符的规则。每个过程的参数仅用于该过程本身；相同的参数名称可以用在其它过程中。默认情况下，参数只能代替常量，而不能用于代替表名、列名或其它数据库对象的名称。data_type参数的数据类型。除table之外的其他所有数据类型均可以用作存储过程的

19、参数。但是，cursor数据类型只能用于OUTPUT参数。如果指定cursor数据类型，则还必须指定VARYING和OUTPUT关键字。对于可以是cursor数据类型的输出参数，没有最大数目的限制。VARYING指定作为输出参数支持的结果集（由存储过程动态构造，内容可以变化）。仅适用于游标参数。default参数的默认值。如果定义了默认值，不必指定该参数的值即可执行过程。默认值必须是常量或NULL。如果过程将对该参数使用LIKE关键字，那么默认值中可以包含通配符（%、_、和）。OUTPUT表明参数是返回参数。该选项的值可以返回给EXECUTE。使用OUTPUT参数可将信息返回给调用过程。Tex

20、t、ntext和image参数可用作OUTPUT参数。使用OUTPUT关键字的输出参数可以是游标占位符。如：CREATEPROCEDURECustOrdersDetailOrderIDintASSELECTProductName,UnitPrice=ROUND(Od.UnitPrice,2),Quantity,Discount=CONVERT(int,Discount*100),ExtendedPrice=ROUND(CONVERT(money,Quantity*(1-Discount)*Od.UnitPrice),2)FROMProductsP,OrderDetailsOdWHEREOd.P

21、roductID=P.ProductIDandOd.OrderID=OrderIDGO四：游标：游标（Cursor）是处理数据的一种方法，为了查看或者处理结果集中的数据，游标提供了在结果集中一次以行或者多行前进或向后浏览数据的能力。我们可以把游标当作一个指针，它可以指定结果中的任何位置，然后允许用户对指定位置的数据进行处理。声明游标打开游标读取游标数据关闭游标释放游标常用于需要把多行的数据进行拼接处理如：declareContactNamevarchar(50)declareAllContactNamevarchar(5000)setAllContactName=DECLAREGetConta

22、ctName_CursorCURSORFORselectContactNamefromCustomerswhereCustomerId=ALFKIOPENGetContactName_CursorFETCHNEXTFROMGetContactName_CursorintoContactNameWHILEFETCH_STATUS=0BEGINsetAllContactName=AllContactName+ContactNameprintAllContactNameFETCHNEXTFROMGetContactName_CursorintoContactNameENDCLOSEGetContac

23、tName_CursorDEALLOCATEGetContactName_Cursor五：常用系统函数和变量：系统变量：selectERROR-返回最后执行的Transact-SQL语句的错误代码(integer)selectIDENTITY-返回最后插入的标识值SelectUSER_NAME()-返回用户数据库用户名selectERROR-返回最后执行的Transact-SQL语句的错误代码selectCONNECTIONS-返回自上次SQL启动以来连接或试图连接的次数。selectGETDATE()-当前时间selectCPU_BUSY/100-返回自上次启动SQL以来CPU的工作时间，单

24、位为毫秒USEtempdbSelectDBTS-为当前数据库返回当前timestamp数据类型的值。这一timestamp值保证在数据库中是唯一的。selectIDENTITY-返回最后插入的标识值SelectIDLE-返回SQL自上次启动后闲置的时间，单位为毫秒SelectIO_BUSY-返回SQL自上次启动后用于执行输入和输出操作的时间，单位为毫秒SelectLANGID-返回当前所使用语言的本地语言标识符(ID)。SelectLANGUAGE-返回当前使用的语言名SelectLOCK_TIMEOUT-当前会话的当前锁超时设置，单位为毫秒。SelectMAX_CONNECTIONS-返回S

25、QL上允许的同时用户连接的最大数。返回的数不必为当前配置的数值EXECsp_configure-显示当前服务器的全局配置设置SelectMAX_PRECISION-返回decimal和numeric数据类型所用的精度级别，即该服务器中当前设置的精度。默认最大精度38。selectOPTIONS-返回当前SET选项的信息。SelectPACK_RECEIVED-返回SQL自启动后从网络上读取的输入数据包数目。SelectPACK_SENT-返回SQ自上次启动后写到网络上的输出数据包数目。SelectPACKET_ERRORS-返回自SQL启动后，在SQL连接上发生的网络数据包错误数。Select

26、SERVERNAME-返回运行SQL服务器名称。SelectSERVICENAME-返回SQL正在其下运行的注册表键名SelectTIMETICKS-返回SQL服务器一刻度的微秒数SelectTOTAL_ERRORS-返回SQL服务器自启动后，所遇到的磁盘读/写错误数。SelectTOTAL_READ-返回SQL服务器自启动后读取磁盘的次数。SelectTOTAL_WRITE-返回SQL服务器自启动后写入磁盘的次数。SelectTRANCOUNT-返回当前连接的活动事务数。SelectVERSION-返回SQL服务器安装的日期、版本和处理器类型。系统函数：字符转换函数LOWER()和UPPER

27、()LOWER()将字符串全部转为小写；UPPER()将字符串全部转为大写STR()把数值型数据转换为字符型数据。去空格函数LTRIM()把字符串头部的空格去掉。RTRIM()把字符串尾部的空格去掉。取子串函数left()LEFT(，)返回character_expression左起integer_expression个字符。RIGHT()RIGHT(，)返回character_expression右起integer_expression个字符。SUBSTRING()SUBSTRING(，length)返回从字符串左边第starting_position个字符起length个字符的部分。字符串比较函数CHARINDEX()返回字符串中某个指定的子串出现的开始位置。PATINDEX()返回字符串中某个指定的子串出现的开始位置。字符串操作函数REP