显示标签为“SQL”的博文。显示所有博文
显示标签为“SQL”的博文。显示所有博文

SQL server实验一


实验1

实验名称： SQL Server2005 服务器配置和Transact-SQL的使用
实验内容：1. 在Microsoft SQL Server Management Studio中熟悉服务器的各种配置方法。
          2. 练习SQLServer中Transact-SQL语法。
             a) 通过事务管理语言，练习事务的写法。
             b) 通过“帮助”及其他资料，学会case函数及日期函数的使用方法。
实验目的：1. 了解服务器配置方法和数据库创建的过程。配置服务器选项的过程就是为了充分利用系统资源、设置服务器行为的过程。合理配置服务器选项，可以加快服务器回应请求的速度、充分利用系统资源、提高工作效率。
2. 学会通过资料查找，掌握函数及T-SQL的语法规则。
实验方法：使用SQL Server Management Studio 工具 和sp_ configure存储过程进行配置。
实验步骤及要求：
一．服务器配置
1． 启动SQL Server Management Studio，输入连接信息，进入工具主窗口。
2． 右击数据库服务器名称，在快捷菜单中选择[属性]，则出现服务器配置选项卡。
3． 分别查看常规、内存、处理器、安全性、连接、数据库设置、高级、权限选项卡，看看里面包含的内容。
4． 在[高级]选项中，修改[两位数年份截至]选项。
5． 在新建查询中执行
EXEC sys.sp_configure 'show advanced options', '1'
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure 'two digit year cutoff', '2050'
GO
RECONFIGURE WITH OVERRIDE
GO
回到步骤4中的[高级]选项中，可以得到同样的修改效果。通过SQL语句体会设置的作用。
（你用什么方法证明你的设置生效了？）
练习一：
将上述步骤5中核心语句进行解释，写入实验报告。（通过“帮助”实现）

二．T-SQL语法练习
1．新建一张表account（可以自己定义一个新数据库，也可用示例数据库advantureworks，但不要在master中），只包含两个字段即可：姓名、余额。
2．使用account表，写一个简单事务：从张三账户转帐100元到李四账户，如果张三账户余额不够100，则该事务不执行。为体现事务执行的特点，请按如下逻辑编写：
事务开始
转帐
检查余额
如果满足条件
提交事务
否则
回滚
编写好后，测试，体会上学期所讲的事务的特点。
3．往account表中多输入一些数据，余额中包含空值、1000以下的，其他值的.写一条SQL语句，练习CASE函数的用法。
  SQL 语句要求：列出account的信息，余额不写具体值，余额写法如下：空值填写“不清”，0-1000之间，填写“较少”，剩余填写“其他”
4．时间函数练习:
  用T-SQL写出今年的十月一日是星期几
注意：不能直接使用2012-10-01，可以使用函数：得到当前日期 getdate()、取日期的年部分 ，得到今年是多少……
查找教材、帮助或互联网得到所需信息
练习二：
实现上述4个问题。将2-4写入实验报告，第3小题拷屏即可（因为涉及显示结果）

SQL server 函数和存储过程


本质上没区别。只是函数有如：只能返回一个变量的限制。而存储过程可以返回多个。而函数是可以嵌入在sql中使用的,可以在select中调用，而存储过程不行。执行的本质都一样。

函数限制比较多，比如不能用临时表，只能用表变量.还有一些函数都不可用等等.而存储过程的限制相对就比较少
　　1. 一般来说，存储过程实现的功能要复杂一点，而函数的实现的功能针对性比较强。
　　2. 对于存储过程来说可以返回参数，而函数只能返回值或者表对象。
　　3. 存储过程一般是作为一个独立的部分来执行，而函数可以作为查询语句的一个部分来调用，由于函数可以返回一个表对象，因此它可以在查询语句中位于FROM关键字的后面。
　　4. 当存储过程和函数被执行的时候，SQL Manager会到procedure cache中去取相应的查询语句，如果在procedure cache里没有相应的查询语句，SQL Manager就会对存储过程和函数进行编译。
　　Procedure cache中保存的是执行计划 (execution plan) ，当编译好之后就执行procedure cache中的execution plan，之后SQL SERVER会根据每个execution plan的实际情况来考虑是否要在cache中保存这个plan，评判的标准一个是这个execution plan可能被使用的频率;其次是生成这个plan的代价，也就是编译的耗时。保存在cache中的plan在下次执行时就不用再编译了。

源文档 <http://database.ctocio.com.cn/304/9355304.shtml>

存储过程:
存储过程可以使得对数据库的管理、以及显示关于数据库及其用户信息的工作容易得多。存储过程是 SQL 语句和可选控制流语句的预编译集合，以一个名称存储并作为一个单元处理。存储过程存储在数据库内，可由应用程序通过一个调用执行，而且允许用户声明变量、有条件执行以及其它强大的编程功能。
存储过程可包含程序流、逻辑以及对数据库的查询。它们可以接受参数、输出参数、返回单个或多个结果集以及返回值。
可以出于任何使用 SQL 语句的目的来使用存储过程，它具有以下优点：
1、可以在单个存储过程中执行一系列 SQL 语句。
2、可以从自己的存储过程内引用其它存储过程，这可以简化一系列复杂语句。
3、存储过程在创建时即在服务器上进行编译，所以执行起来比单个 SQL 语句快。

用户定义函数：
Microsoft SQL Server 2000 允许创建用户定义函数。与任何函数一样，用户定义函数是可返回值的例程。根据所返回值的类型，每个用户定义函数可分成以下三个类别：
1、返回可更新数据表的函数
如果用户定义函数包含单个 SELECT 语句且该语句可更新，则该函数返回的表格格式结果也可以更新。
2、返回不可更新数据表的函数
如果用户定义函数包含不止一个 SELECT 语句，或包含一个不可更新的 SELECT 语句，则该函数返回的表格格式结果也不可更新。
3、返回标量值的函数
用户定义函数可以返回标量值。

源文档 <http://bbs.csdn.net/topics/80237453>

SQL Server 2005数据库实验四

实验名称：SQL Server2005 视图、存储过程、触发器的创建及使用

实验内容：在SQL Server 中完成视图的定义并在此基础上进行更新等操作；
在SQL Server 中完成视存储过程的设计、创建及执行等操作；
在SQL Server中创建DML触发器，并设置相关操作使之运行。
实验目的：掌握视图的定义并了解什么情况下视图修改可以成功，体会视图与表的异同之处；
理解和掌握存储过程的创建及调用方法；
理解和掌握触发器的创建方法，体会触发器的执行条件和作用。
实验方法：在SQL Server 环境下，通过SQL语句完成相应操作。

实验步骤及要求：
一．视图
使用 pc、product表相同的model属性进行关联， 建一个视图testview，包含maker,model,price三个属性。
1.   修改视图中的price的值，观察视图和pc表中相关的变化。
2.   修改视图中的model的值，观察视图、pc、product表中相关的变化。
3.   上一步中的修改完成的有问题，两个原始表（pc、product）的内容不是都修改了，为完成相应操作，请设计一个触发器，当对视图的model进行修改时，触发器自动完成pc、product的修改。

二．存储过程（创建及调用的sql语句）
1．查询成绩在x和y之间的学生信息。（x，y是两个输入参数，学生信息包括学号、姓名、年龄）
2．计算指定课程的及格率（输入参数：课程编号，输出参数：该课程的及格率。）

三．触发器
1．修改SC表中学生成绩时，成绩（grade）的增加幅度不能超过10%，超过该范围，取消操作，并给出错误提示。
2．删除一条学生信息（S表）时，如果该生信息已经存在于成绩SC表中，给出提示，不能删除。

实验4要求下载
实验4实验数据 (实验4数据没有后缀名,在数据库管理界面SQL SERVER MANAGEMENT STUDIO添加到数据库即可)

在视图上建立触发器

对视图建立触发器，SQL Server 2000提供了instead of 方法，可以对原触发行为进行改写。
这里以一个简单的例子来说明使用对视图建立触发器的方法：
1. 创建两个Table (假设id唯一)
    create table test1(id varchar(50), name varchar(50))
    create table test2(id varchar(50), name varchar(50))
2. 创建一个视图
    create view viewTest as
    select test1.id, test1.name as name1, test2.name as name2 from test1 inner join test2 on test1.id = test2.id
3. 创建insert触发器
    create trigger triggerInsertViewTest on viewTest
    instead of insert
    as  
    begin
         insert test1(id, name) select id, name1 from inserted
         insert test2(id, name) select id, name2 from inserted
    end
4. 创建update触发器
update操作实际上是先进行delete, 再进行insert操作。
    create trigger triggerUpdateViewTest on viewTest
    instead of update
    as
    if update(id)
    begin
         update test1 set test1.id = inserted.id from test1, inserted, deleted where test1.id = deleted.id
         update test2 set test2.id = inserted.id from test2, inserted, deleted where test2.id = deleted.id
    end
    if update(name1)
    begin
        update test1 set test1.name = inserted.name1 from test1, inserted where test1.id = inserted.id
    end
    if update(name2)
    begin
         update test2 set test2.name = inserted.name2 from test2, inserted where test2.id = inserted.id
    end
5. 创建delete触发器
    create trigger triggerDeleteViewTest on viewTest
    instead of delete
    as
    begin
         delete test1 from test1, deleted where test1.id = deleted.id
         delete test2 from test2, deleted where test2.id = deleted.id
    end

引用网址: 1

Delete语句语法

DELETE FROM table_name
WHERE 条件

SQL Server数据类型


数据类型
类型
描             述
bit
整型
bit 数据类型是整型，其值只能是0、1或空值。这种数据类型用于存储只有两种可能值的数据，如Yes 或No、True 或Fa lse 、On 或Off
int
整型
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数据类型最大的区别是，存储的长度不是列长，而是数据的长度
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亿字节
参考

2012/06/14--更新

SQL数据类型----字符型数据分析

字符型数据类型可用来存储数字符号、字母以及特殊符号。一般，使用字符型数据时，要给数据加上单引号或双引号。

1.char---char数据类型使用固定长度来存储字符，最长可以容纳8000char数据类型的定义形式为：char[n]

其中，n表示所有字符占有的存储空间，以字节为单位。n必须是一个介于1~8000之间的数值。若不指定n值，则系统默认为1。
利用char数据类型来定义表列或者定义变量时，应该给定数据的最大长度。如果实际数据的字符长度短于给定的最大长度，则多余的字节会被以空格填充。如果实际数据的字符长度超过了给定的最大长度，则超过的字符将会被截断。在使用字符型常量为字符数据类型赋值时，必须使用双引号或单引号将字符型常量括起来。例如：'loving'，'舞'。
注意：使用char数据类型的最大好处就在于可以精确计算数据占有的空间。计算机占用的空间非常重要。在一些非常庞大的系统里定义一些表列长度时，几个字节的差距也许意味着上百兆数据空间的节省或浪费。

2.nchar---nchar用来定义固定长度的Unicode数据，最大长度为4000个字符。与char类型相似，nchar数据类型的定义形式为：nchar[n]

其中，n表示所有字符占有的存储空间，以字节为单位。n必须是一个介于1~4000之间的数值。
因为nchar类型采用UNICODE标准的数据类型，所以它比char多占用一倍的存储空间。使用UNICODE标准的好处是因其使用两个字节作为存储单位，故其一个存储单位的容纳量就大大增加了，可以将全世界的语言文字都包括在内，在一个数据列中就可以同时出现中文、英文、法文、德文等，而不会出现编码冲突。

3.varchar SQL利用varchar数据类型来存储最长可以达到8000个字符的变长字符数据。

与char数据类型不同，varchar数据类型的存储空间，随存储在表列中每一个数据的字符数的不同而变化。
例如，如果定义表列为varchar20)，那么存储在该列的数据最多可以长达20个字节。但是，在每列数据没有达到20个字节时，并不会在多余的字节上填充空格。
varchar数据类型的定义形式为：varchar[n]
其中，n表示所有字符占有的存储空间，以字节为单位。n必须是一个介于1~8000之间的数值。若输入的数据过长，SQL将会截掉其超出部分。
说明：当存储在列中数据的值的大小经常变化时，使用varchar数据类型可以有效地节省空间。

4.nvarchar

用来定义可变长度的二进制数据，最大长度为4000nvarchar数据类型的定义形式为：
nvarchar[n]
其中，n表示所有字符占有的存储空间，以字节为单位。n必须是一个介于1~4000之间的数据。nvarchar与nchar的区别和varchar与char的区别类似。
via

Quote Of The Day