(整理)SQLServer数据库基本知识点.

SQL Server 数据库基本知识点一、数据类型

二、常用语句(用到的数据库Northwind)

查询语句

简单的Transact-SQL查询只包括选择列表、FROM子句与WHERE子句。它们分别说明所查询列、查询的

表或视图、以及搜索条件等。例如,下面的语句查询Customers 表中公司名称为“Alfreds Futterkiste”的ContactName字段与Address字段。

SELECT ContactName, Address

FROM Customers

WHERE CompanyName='Alfreds Futterkiste'

(一) 选择列表

选择列表(select_list)指出所查询列,它可以就是一组列名列表、星号、表达式、变量(包括局部变量与全局变量)等构成。

1、选择所有列

例如,下面语句显示Customers表中所有列的数据:

SELECT *

FROM Customers

2、选择部分列并指定它们的显示次序查询结果集合中数据的排列顺序与选择列表中所指定的列名排列顺序相同。

例如:

SELECT ContactName, Address

FROM Customers

3、更改列标题

在选择列表中,可重新指定列标题。定义格式为:

列标题as 列名

列名列标题如果指定的列标题不就是标准的标识符格式时,应使用引号定界符,例如,下列语句使用汉字显示列标题:

SELECT ContactName as 联系人名称, Address as地址

FROM Customers

4、删除重复行

SELECT语句中使用ALL或DISTINCT选项来显示表中符合条件的所有行或删除其中重复的数据行,默认

为ALL。使用DISTINCT选项时,对于所有重复的数据行在SELECT返回的结果集合中只保留一行。

SELECT DISTINCT(Country)

FROM Customers

5、限制返回的行数

使用TOP n [PERCENT]选项限制返回的数据行数,TOP n说明返回n行,而TOP n PERCENT时,说明n就是

表示一百分数,指定返回的行数等于总行数的百分之几。

例如:

SELECT TOP 2 *

FROM Customers

SELECT TOP 20 PERCENT *

FROM Customers

(二)FROM子句

FROM子句指定SELECT语句查询及与查询相关的表或视图。在FROM子句中最多可指定256个表或视图,它们之间用逗号分隔。在FROM子句同时指定多个表或视图时,如果选择列表中存在同名列,这时应使用对象名限定这些列

所属的表或视图。例如在Orders与Customers表中同时存在CustomerID列,在查询两个表中的CustomerID时应

使用下面语句格式加以限定:

select * from Orders,Customers

where Orders、CustomerID =Customers、CustomerID

在FROM子句中可用以下两种格式为表或视图指定别名:

表名as 别名

表名别名

select * from Orders as a,Customers as b

where a、CustomerID =b、CustomerID

SELECT不仅能从表或视图中检索数据,它还能够从其它查询语句所返回的结果集合中查询数据。

例如:

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子句可包括各种条件运算符:

比较运算符(大小比较):>、>=、=、<、<=、<>、!>、!<

范围运算符(表达式值就是否在指定的范围):BETWEEN…AND…

NOT BETWEEN…AND…

列表运算符(判断表达式就是否为列表中的指定项):IN (项1,项2……)

NOT IN (项1,项2……)

模式匹配符(判断值就是否与指定的字符通配格式相符):LIKE、NOT LIKE

空值判断符(判断表达式就是否为空):IS NULL、NOT IS NULL

逻辑运算符(用于多条件的逻辑连接):NOT、AND、OR

1、范围运算符例:age BETWEEN 10 AND 30相当于age>=10 AND age<=30

2、列表运算符例:country IN ('Germany','China')

3、模式匹配符例:常用于模糊查找,它判断列值就是否与指定的字符串格式相匹配。可用于char、

varchar、text、ntext、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='ALFKI'

where 条件同查询语句

删除语句

语法:delete from 表where 条件

如:

delete from Customers where CustomerID='ALFKI'

where 条件同查询语句

清空表:

truncate table 表清空表中所有数据,表中如果有自增长字段,该字段在插入时会重新开始注意:

由于更新语句与删除语句会更改表数据对公司业务主要表进行更新与删除操作时一定要先进行备份,然后进行操作

备份语句

select * into Customers20101019 from Customers

目标表(Customers)不存在时会自动创建

三、存储过程

优点:

1、存储过程只在创造时进行编译,以后每次执行存储过程都不需再重新编译,而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。

2、当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete时),可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。

3、存储过程可以重复使用,可减少数据库开发人员的工作量

4、安全性高,可设定只有某此用户才具有对指定存储过程的使用权

语法

CREATE PROC[ EDURE ] [ owner、] procedure_name [ ; number ]

[ { @parameter data_type }

[ VARYING ] [ = default ] [ OUTPUT ]

] [ ,、、、n ]

[ WITH

{ RECOMPILE | ENCRYPTION | RECOMPILE , 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

表明参数就是返回参数。该选项的值可以返回给EXEC[UTE]。使用OUTPUT 参数可将信息返回给调用过程。T ext、ntext 与image 参数可用作OUTPUT 参数。使用OUTPUT 关键字的输出参数可以就是游标占位符。

如:

CREATE PROCEDURE CustOrdersDetail @OrderID int

AS

SELECT ProductName,

UnitPrice=ROUND(Od、UnitPrice, 2),

Quantity,

Discount=CONVERT(int, Discount * 100),

ExtendedPrice=ROUND(CONVERT(money, Quantity * (1 - Discount) * Od、UnitPrice), 2)

FROM Products P, [Order Details] Od

WHERE Od、ProductID = P、ProductID and Od、OrderID = @OrderID

GO

四:游标:

游标(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 = 'ALFKI'

OPEN GetContactName_Cursor

FETCH NEXT FROM GetContactName_Cursor into @ContactName

WHILE @@FETCH_STATUS = 0

BEGIN

set @ AllContactName= AllContactName+@ContactName

print @ AllContactName

FETCH NEXT FROM GetContactName_Cursor into @ContactName

END

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 @@MAX_CONNECTIONS --返回SQL上允许的同时用户连接的最大数。返回的数不必为当前配置的数值

EXEC sp_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. 去空格函数

LTRIM() 把字符串头部的空格去掉。

RTRIM() 把字符串尾部的空格去掉。

3. 取子串函数

left()

LEFT (, )

返回character_expression 左起integer_expression 个字符。

RIGHT()

RIGHT (, )

返回character_expression 右起integer_expression 个字符。

SUBSTRING()

SUBSTRING (, , length)

返回从字符串左边第starting_ position 个字符起length个字符的部分。

4. 字符串比较函数

CHARINDEX()

返回字符串中某个指定的子串出现的开始位置。

PATINDEX()

返回字符串中某个指定的子串出现的开始位置。

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)TEXT类型到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 的缺省格式返回系统当前的日期与时间