SQLServer数据库基本知识点.

-SQL Server 数据库基本知识点一、数据类型数据类型类型描述是否常用bit整型bit 数据类型是整型，其值只能是0、1或空值。这种数据类型用于存储只有两种可能值的数据，如Yes 或No、True 或Fa lse 、On 或Offint整型int 数据类型可以存储从- 231(-2147483648)到231(2147483 647)之间的整数。存储到数据库的几乎所有数值型的数据都可以用这种数据类型。这种数据类型在数据库里占用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月3 1日间所有的日期和时间数据， 精确到三百分之一秒或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数据类型最大的区别是，存储的长度不是列长，而是数据的长度是te*t字符型te*t 数据类型用来存储大量的非统一编码型字符数据。这种数据类型最多可以有231-1或20亿个字符nchar统一编码字符型nchar 数据类型用来存储定长统一编码字符型数据。统一编码用双字节结构来存储每个字符，而不是用单字节(普通文本中的情况)。它允大量的扩展字符。此数据类型能存储4000种字符，使用的字节空间上增加了一倍nvarchar统一编码字符型nvarchar 数据类型用作变长的统一编码字符型数据。此数据类型能存储4000种字符，使用的字节空间增加了一倍nte*t统一编码字符型nte*t 数据类型用来存储大量的统一编码字符型数据。这种数据类型能存储230-1或将近10亿个字符，且使用的字节空间增加了一倍binary二进制数据类型binary数据类型用来存储可达8000 字节长的定长的二进制数据。当输入表的容接近相同的长度时，你应该使用这种数据类型varbinary二进制数据类型varbinary 数据类型用来存储可达8000 字节长的变长的二进制数据。当输入表的容大小可变时，你应该使用这种数据类型image二进制数据类型image 数据类型用来存储变长的二进制数据，最大可达231-1或大约20亿字节二、 常用语句 (用到的数据库Northwind)查询语句简单的Transact-SQL查询只包括选择列表、FROM子句和WHERE子句。它们分别说明所查询列、查询的表或视图、以及搜索条件等。例如，下面的语句查询Customers表中公司名称为Alfreds Futterkiste”的ContactName字段和Address字段。SELECT ContactName, AddressFROM CustomersWHERE panyName=Alfreds Futterkiste(一) 选择列表选择列表(select_list)指出所查询列，它可以是一组列名列表、星号、表达式、变量(包括局部变量和全局变量)等构成。1、选择所有列例如，下面语句显示Customers表中所有列的数据：SELECT *FROM Customers2、选择部分列并指定它们的显示次序查询结果集合中数据的排列顺序与选择列表中所指定的列名排列顺序相同。例如：SELECT ContactName, AddressFROM Customers3、更改列标题在选择列表中，可重新指定列标题。定义格式为：列标题 as 列名列名 列标题如果指定的列标题不是标准的标识符格式时，应使用引号定界符，例如，下列语句使用汉字显示列标题：SELECT ContactName as 联系人名称, Address as地址FROM Customers4、删除重复行SELECT语句中使用ALL或DISTINCT选项来显示表中符合条件的所有行或删除其中重复的数据行，默认为ALL。使用DISTINCT选项时，对于所有重复的数据行在SELECT返回的结果集合中只保留一行。SELECT DISTINCT(Country)FROM Customers5、限制返回的行数使用TOP n PERCENT选项限制返回的数据行数，TOP n说明返回n行，而TOP n PERCENT时，说明n是表示一百分数，指定返回的行数等于总行数的百分之几。例如：SELECT TOP 2 *FROM CustomersSELECT TOP 20 PERCENT *FROM Customers(二)FROM子句FROM子句指定SELECT语句查询及与查询相关的表或视图。在FROM子句中最多可指定256个表或视图，它们之间用逗号分隔。在FROM子句同时指定多个表或视图时，如果选择列表中存在同名列，这时应使用对象名限定这些列所属的表或视图。例如在Orders和Customers表中同时存在CustomerID列，在查询两个表中的CustomerID时应使用下面语句格式加以限定：select * from Orders,Customerswhere Orders.CustomerID =Customers.CustomerID在FROM子句中可用以下两种格式为表或视图指定别名：表名 as 别名表名 别名select * from Orders as a,Customers as bwhere a.CustomerID =b.CustomerIDSELECT不仅能从表或视图中检索数据，它还能够从其它查询语句所返回的结果集合中查询数据。例如：select * from Customers where CustomerID in (select CustomerID from Orders where EmployeeID=4)此例中，将SELECT返回的结果集合给予一别名CustomerID，然后再从中检索数据。(三) 使用WHERE子句设置查询条件WHERE子句设置查询条件，过滤掉不需要的数据行。例如下面语句查询年龄大于20的数据：select CustomerID from Orders where EmployeeID=4 WHERE子句可包括各种条件运算符：比较运算符(大小比较)：、=、=、=、!、!=10 AND age=302、列表运算符例：country IN (Germany,China)3、模式匹配符例：常用于模糊查找，它判断列值是否与指定的字符串格式相匹配。可用于char、varchar、te*t、nte*t、datetime和smalldatetime等类型查询。可使用以下通配字符：百分号%：可匹配任意类型和长度的字符插入语句语法：Insert into 表(字段1,字段2,.字段n) values(值1,值2,.值n)例如：Insert into Region(RegionID, RegionDescription)values(5,Southern)更新语句语法：update 表 set 字段1 = 值1,字段2=值2,字段n=值n where 条件如：update Customers set ContactName=John,Address =Avda. de la where CustomerID=ALFKIwhere 条件同查询语句删除语句语法：delete from 表 where 条件如：delete from Customerswhere CustomerID=ALFKIwhere 条件同查询语句清空表：truncate table 表 清空表中所有数据，表中如果有自增长字段，该字段在插入时会重新开始注意：（6）环境影响评价结论的科学性。由于更新语句与删除语句会更改表数据对公司业务主要表进行更新与删除操作时一定要先进行备份，然后进行操作B.环境影响登记表备份语句2. 规划环境影响报告书的审查内容select*intoCustomers20101019 fromCustomers目标表（Customers20101019）不存在时会自动创建2.早期介入原则；三、 存储过程同建设项目安全评价相关但又有不同的还有：地质灾害防治管理办法规定的地质灾害危险性评估，地震安全性评价管理条例中规定的地震安全性评价，中华人民*国职业病防治法中规定的职业病危害预评价等。优点：发现规划存在重大环境问题的，审查时应当提出不予通过环境影响报告书的意见；1.存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。2.当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。3.存储过程可以重复使用,可减少数据库开发人员的工作量新增加的六个内容是：风险评价；公众参与；总量控制；清洁生产和循环经济；水土保持；社会环境影响评价。4.安全性高,可设定只有*此用户才具有对指定存储过程的使用权在评估经济效益不能直接估算的自然资源方面，机会成本法是一种很有用的评价技术。机会成本法特别适用于对自然保护区或具有唯一性特征的自然资源的开发项目的评估。语法（2）综合规划环境影响篇章或者说明的内容。CREATE PROC EDURE owner. procedure_name ; number 一、安全评价 parameter data_type VARYING = default OUTPUT ,.n WITH REPILE | ENCRYPTION | REPILE , ENCRYPTION FOR REPLICATION AS sql_statement .n 参数owner 拥有存储过程的用户 ID 的名称。owner 必须是当前用户的名称或当前用户所属的角色的名称。procedure_name 新存储过程的名称。过程名必须符合标识符规则，且对于数据库及其所有者必须唯一。;number 是可选的整数，用来对同名的过程分组，以便用一条 DROP PROCEDURE 语句即可将同组的过程一起除去。例如，名为 orders 的应用程序使用的过程可以命名为 orderproc;1、orderproc;2 等。DROP PROCEDURE orderproc 语句将除去整个组。如果名称中包含定界标识符，则数字不应包含在标识符中，只应在 procedure_name 前后使用适当的定界符。parameter 过程中的参数。在 CREATE PROCEDURE 语句中可以声明一个或多个参数。用户必须在执行过程时提供每个所声明参数的值（除非定义了该参数的默认值，或者该值设置为等于另一个参数）。存储过程最多可以有 2.100 个参数。使用 符号作为第一个字符来指定参数名称。参数名称必须符合标识符的规则。每个过程的参数仅用于该过程本身；相同的参数名称可以用在其它过程中。默认情况下，参数只能代替常量，而不能用于代替表名、列名或其它数据库对象的名称。data_type 参数的数据类型。除 table 之外的其他所有数据类型均可以用作存储过程的参数。但是，cursor 数据类型只能用于 OUTPUT 参数。如果指定 cursor 数据类型，则还必须指定 VARYING 和 OUTPUT 关键字。对于可以是 cursor 数据类型的输出参数，没有最大数目的限制。VARYING 指定作为输出参数支持的结果集（由存储过程动态构造，容可以变化）。仅适用于游标参数。default 参数的默认值。如果定义了默认值，不必指定该参数的值即可执行过程。默认值必须是常量或 NULL。如果过程将对该参数使用 LIKE 关键字，则默认值中可以包含通配符（%、_、 和 ）。OUTPUT表明参数是返回参数。该选项的值可以返回给 E*ECUTE。使用 OUTPUT 参数可将信息返回给调用过程。Te*t、nte*t 和 image 参数可用作 OUTPUT 参数。使用 OUTPUT 关键字的输出参数可以是游标占位符。如：CREATE PROCEDURE CustOrdersDetail OrderID intASSELECT ProductName, UnitPrice=ROUND(Od.UnitPrice, 2), Quantity, Discount=CONVERT(int, Discount * 100), E*tendedPrice=ROUND(CONVERT(money, Quantity * (1 - Discount) * Od.UnitPrice), 2)FROM Products P, Order Details OdWHERE Od.ProductID = P.ProductID and Od.OrderID = OrderIDGO四：游标：游标（Cursor）是处理数据的一种法，为了查看或者处理结果集中的数据，游标提供了在结果集中一次以行或者多行前进或向后浏览数据的能力。我们可以把游标当作一个指针，它可以指定结果中的任位置，然后允用户对指定位置的数据进行处理。1. 声明游标2. 打开游标3. 读取游标数据4. 关闭游标5. 释放游标常用于需要把多行的数据 进行拼接处理如：declare ContactName varchar(50)declare AllContactName varchar(5000)set AllContactName =DECLARE GetContactName_Cursor CURSOR FOR select ContactName from Customers where CustomerId = ALFKIOPEN GetContactName_Cursor FETCH NE*T FROM GetContactName_Cursor into ContactNameWHILE FETCH_STATUS = 0BEGIN set AllContactName= AllContactName+ContactNameprint AllContactNameFETCH NE*T FROM GetContactName_Cursor into ContactNameEND CLOSE GetContactName_Cursor DEALLOCATE GetContactName_Cursor五：常用系统函数和变量：系统变量：select ERROR -返回最后执行的 Transact-SQL 语句的错误代码(integer)select IDENTITY -返回最后插入的标识值Select USER_NAME() -返回用户数据库用户名select ERROR -返回最后执行的 Transact-SQL 语句的错误代码select CONNECTIONS -返回自上次SQL启动以来连接或试图连接的次数。select GETDATE() -当前时间select CPU_BUSY/100 -返回自上次启动SQL 以来 CPU 的工作时间，单位为毫秒USE tempdb Select DBTS -为当前数据库返回当前 timestamp 数据类型的值。这一 timestamp 值保证在数据库中是唯一的。select IDENTITY -返回最后插入的标识值Select IDLE -返回SQL自上次启动后闲置的时间，单位为毫秒Select IO_BUSY -返回SQL自上次启动后用于执行输入和输出操作的时间，单位为毫秒Select LANGID -返回当前所使用语言的本地语言标识符(ID)。Select LANGUAGE -返回当前使用的语言名Select LOCK_TIMEOUT -当前会话的当前锁超时设置，单位为毫秒。Select MA*_CONNECTIONS -返回SQL上允的同时用户连接的最大数。返回的数不必为当前配置的数值E*EC sp_configure -显示当前服务器的全局配置设置Select MA*_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. 去空格函数LTRIM() 把字符串头部的空格去掉。RTRIM() 把字符串尾部的空格去掉。3. 取子串函数left()LEFT (， )返回character_e*pression 左起 integer_e*pression 个字符。RIGHT()RIGHT (， )返回character_e*pression 右起 integer_e*pression 个字符。SUBSTRING()SUBSTRING (， ， length)返回从字符串左边第starting_ position 个字符起length个字符的部分。4. 字符串比较函数CHARINDE*()返回字符串中*个指定的子串出现的开始位置。PATINDE*()返回字符串中*个指定的子串出现的开始位置。5. 字符串操作函数REPLACE()返回被替换了指定子串的字符串。SPACE()返回一个有指定长度的空白字符串。6. 数据类型转换函数CAST()CAST ( AS length )CONVERT()CONVERT ( length ， ， style)1）data_type为SQL Server系统定义的数据类型，用户自定义的数据类型不能在此使用。2）length用于指定数据的长度，缺省值为30。3）把CHAR或VARCHAR类型转换为诸如INT或SAMLLINT这样的INTEGER类型、结果必须是带正号或负号的数值。4）TE*T类型到CHAR或VARCHAR类型转换最多为8000个字符，即CHAR或VARCHAR数据类型是最大长度。5）IMAGE类型存储的数据转换到BINARY或VARBINARY类型，最多为8000个字符。6）把整数值转换为MONEY或SMALLMONEY类型，按定义的的货币单位来处理，如人民币、美元、英镑等。7）BIT类型的转换把非零值转换为1，并仍以BIT类型存储。8）试图转换到不同长度的数据类型，会截短转换值并在转换值后显示+”，以标识发生了这种截断。9）用CONVERT（） 函数的style 选项能以不同的格式显示日期和时间。style 是将DATATIME 和SMALLDATETIME 数据转换为字符串时所选用的由SQL Server 系统提供的转换样式编号，不同的样式编号有不同的输出格式。7. 日期函数DATEADD()DATEADD (， ， )返回指定日期date 加上指定的额外日期间隔number 产生的新日期。DATEDIFF()DATEDIFF (， ， )返回两个指定日期在datepart 面的不同之处，即date2 超过date1的差距值，其结果值是一个带有正负号的整数值。DATEPART()DATEPART (， )以整数值的形式返回日期的指定部分。此部分由datepart 来指定。DATEPART (dd， date) 等同于DAY (date)DATEPART (mm， date) 等同于MONTH (date)DATEPART (yy， date) 等同于YEAR (date)GETDATE()以DATETIME 的缺省格式返回系统当前的日期和时间. z.