主键约束:Primary Key constraint
唯一约束:Unique Constraint
检查约束:Check Constraint (某列取值范围限制、格式限制等,如有关年龄的约束)
默认约束:Default Constraint(某列的默认值,如我们的男性学员较多,性别默认为“男”)
外键约束:Foreign Key Constraint(用于在两表之间建立关系,需要指定引用主表的哪一列)
添加约束:
例:
ALTER TABLE 表名
ADD CONSTRAINT 约束名 约束类型 具体的约束说明(约束名的命名规则推荐采用"约束类型_约束字段"样式
ALTER TABLE 表
DROP CONSTRAINT 约束名
添加Windows登陆账户需要调用SQL server内置的系统存储过程sp_grantlogin,调用语法:
EXEC sp_grantlogin 'windows域名\域账户'(EXEC关键字表示调用执行,如果是本机,可用计算机名替换'windows域名'
添加SQL用户登录需要调用系统存储过程sp_adlogin,调用语法.
EXEC sp_addlogin '账户名','密码'
创建数据库用户
创建数据库用户需要调用系统存储过程sp_grantdbaccess调用语法:
EXEC sp_grantdbaccess '登录账户','数据库用户'(其中,数据库用户为可选参数,默认为登录账户,即数据库用户默认和登录账户同名)
给用户授权,语法:
GRANT 权限 [ON 表名] TO 数据库用户
T-SQL编程
变量是可以存储数据值的对象。可以使用局部变量像SQL语句传递数据。在T-SQL中执行一批SQL语句时,可以声明许多变量以便临时使用。声明变量以后,可以再批处理中用一条T-SQL语句设置该变量的值。该批处理中的下一条语句可以从该变量中检索数值,并给出结果。T-SQL中的变量分为局部变量和全局变量。局部变量的使用也是先声明或复制。而全局变量由系统定义和维护,我们可以直接使用,但一般不自定义全局变量。
局部变量
局部变量的名称必须以标记@作为前缀。
声明局部变量的语句如下。
DECLARE @variable_name DataType
其中,variable_name为局部变量的名称,DataType为数据类型
DECLARE @name varchar(8) --声明一个存放学员姓名的变量name,做多可以存储8个字符
DECLARE @seat int --声明一个存放学员座位号的变量seat
局部变量的赋值有两种方法:使用SET语句或SELECT语句。
语法:
SET @variable_name=value或SELECT @variable_name=value
例:
/*--查找"马文才"的信息--*/
DECLARE @name varchar(8) --学员姓名
SET @name='马文才' --使用SET赋值
SELECT * FROM stuInfo where stuName = @name
/*--查找"马文才"的左右同桌--*/
DECLARE @seat int --座位号
SELECT @seat=stuSeat from stuInfo where stuName=@name --使用SELECT赋值
SELECT * FROM stuInfo where (stuSeat = @seat+1) or (stuSeat = @seat-1)
GO
输出语句
T-SQL中支持输出语句,用于输出显示处理的数据结果。
常用的输出语句有两种,语法:
print 局部变量或字
符串
SELECT 局部变量 AS 自定义列名
其中,第二种方法就是查询语句的特殊应用。
例:
print '服务器名称:'+@@SERVERNAME
SELECT @@SERVERNAME AS '服务器名称'
IF-ELSE条件语句,语句:
IF (条件)
语句或语句块
ELSE
语句或语句块
如果有多条语句,需要使用语句块,语句块使用BEGIN...END表示,其作用类似于Java语言的"{}"符号。
IF (条件)
BEGIN
语句1
语句2
...
END
ELSE
...
WHILE循环语句--语法:
WHILE (条件)
语句或语句块
[BREAK]
使用BREAK关键字从最内层的WHILE循环中退出
CASE多分支语句,语法:
CASE
WHEN 条件1 THEN 结果1
WEHN 条件2 THEN 结果2
[ELSE 其他结果]
END
CASE语句表示如果"条件1"成立,则执行"结果1",其余类推
批处理语句就是已用过的"GO"就是批处理的标志。它是一条或多条SQL语句的集合,SQL Server将批处理语句编译成一个可执行单元,此单元称为执行计划。每个批处理可以编译成单个执行计划,从而提高执行效率。如果批处理包含多条SQL语句,则执行这些语句所需的优化的步骤将编译在单个执行计划中。
在多用户环境中,用户可能同时访问数据库,这将增加网络流量。在单用户环境中,用户可能需要对数据库执行多个任务,如更新表以及对SELECT查询语句的结果进行计算等,这需要向数据库发送一系列命令。
以一个包含员工详细信息及其工作详细信息的数据库为例。该数据库的一个用户想要根据基本薪水的详细信息、工作的总天数来计算每个员工的纯收入。为了重复执行该任务(以便计算每个员工的收入),将这些命令存储在一个文件中,并作为单个执行计划向数据库发送所有命令,将会更容易。以一条命令的方式来处理一组命令的过程成为批处理。
批处理的主要好处是能够简化数据库管理。例如,如果需要更改存储在用户计算机上的现有查询语句,可能需要在所有用户的计算机上进行更改。但是如果将该查询语句集中存储在服务器上,不管是作为文件还是作为存储过程,我们只需在服务器端更改一次即可。这样可以节省大量的时间和精力。
子查询例:
SELECT * FROM stuInfo WHERE stuAge>(SELECT stuAge FROM stuInfo where stuName='李斯文')
GO
==
SELECT ... FROM 表1 WHERE 字段1 > {子查询}
习惯上外面的查询称为父查询,括号中的嵌入的查询称为子查询。SQL Server执行时,先执行查询部分,求出子程序的值,然后在执行整个父查询。它的执行效率比采用SQL 变量实现的方案要高,所有推荐采用子查询。因为子查询作为WHERE条件的一部分,所以还可以和UPDATE、INSERT、DELETE一起使用,语法类似于SELECT 语句。
提示:将子查询和比较运算
符联合使用,必须保证子查询返回的值不能多于一个。
上述子查询将多个结果集合合并在一起,除此之外,还可以将多表间的数据组合在一起,从而替换连接(JOIN)查询。
SELECT stuName FROM stuInfo INNER JOIN stuMarks --INNER JOIN内部连接
ON stuInfo.stuNo=stuMarks.stuNo WHERE writenExam=60
GO
==
SELECT stuName FROM stuInfo
WHERE stuNo=(SELECT stuNo FROM stuMarks WHERE writtenExam=60)
GO
EXISTS和NOT EXISTS子查询
事务--创建事务
Transact-SQL
开始事务:BEGIN TRANSACTION
提交事务:COMMIT TRANSACTION
回滚(撤销):ROLLBACK TRANSACTION
事务的分类有以下3种.
显示事务:用BEGIN TRANSACTION 明确指定事务的开始
隐式事务:通过设置SET IMPLICIT_TRANSACTIONS ON语句,将隐式事务模式设置为打开。当以隐式事务操作时,SQL Server将在提交或回滚事务后自动启动新事务。无法描述事务的开始,只需提交或回滚每个事务
自动提交事务:这是SQL Server的默认模式,它将每条单独的T-SQL语句视为一个事务。如果成功执行,则自动提交。如果错误,则自动回滚。
实际开发中最常用的就是显示事务,它明确地指定事务的开始边界。
判断T-SQL语句是否有错,将使用到曾讲过的全局变量@@ERROR,它用来判断当前T-SQL语句执行是否有错,若有错误则返回非零值。
例子:(银行转账)
USE stuDB
GO
--恢复原来的数据
SET NOCOUNT ON --不显示受影响的行数信息
print '查看转账事务前的余额'
SELECT * FROM bank
GO
/*--开始事务(指定事务从此处开始,后续的T-SQL语句都是一个整体)--*/
BEGIN TRANSACTION
/*--定义变量,永宇累计事务执行过程中的错误--*/
DECLARE @errorSum INT
SET @errorSum=0 --初始化为0,即无错误
/*--转账:张三的账户少1000元,李四的账户多1000元--*/
UPDATE bank SET currentMoney=currentMoney-1000 WHERE customerName='张三'
SET @errorSum+@error --累计是否有错误
UPDATE bank SET currentMoney=curentMoney+1000
SET @errorSum+@error --累计是否有错误
print '查看转账事务过程中余额'
SELECT * FROM bank
/*--根据是否有错误,确定事务是提交还是撤销--*/
IF @errorSum<>0 --如果有错误
BEGIN
print '交易失败,回滚事务'
ROLLBACK TRANSACTION
END
ELSE
BEGIN
print '交易成功,提交事务,写入硬盘,永久地保存'
COMMIT TRANSACTION
END
GO
索引提供指针以只想存储在表中指定列的数据值,然后根据指定的排列次序排列这些指针。数据库使用索引的方式与使用书的目录很相似:通过搜索索引找到特定的值,然后跟随指针到达包含该值的行。
使用T-SQL语句创建索引
CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED] INDEX index_name
ON table_name (column_name[,column_name]...)
[WITH
FILLFACTOR=X
]
其中
1、UNIQUE指定唯一索引,可选
。
2、CLUSTERED、NONCLUSTERED指定是聚集索引还是非聚集索引,可选。
3、FILEFACTOR 表示填充因子,指定一个0-100的值,该值只是索引页填满的空间所占的百分比
因为成绩表stuMarks中的笔试列(writtenExam)经常被查询,为了加快查询速度,现创建索引。由于笔试成绩可能会重复,索引只能创建非聚集索引,实例:
USE stuDB
GO
IF EXISTS (SELECT name FROM sysindexes WHERE name = 'IX_stuMarks_writtenExam')
DROP INDEX stuMARKS.IX_stuMarks_writtenExam --删除索引
/*--笔试列创建非聚集索引:填充因子为30%--*/
CREATE NONCLUSTERED INDEX IX_stuMarks_writtenExam
ON stuMarks(writtenExam)
WITH FILLFACTOR = 30
GO
/*--指定按索引:IX_stuMarks_writtenExam查询--*/
SELECT * FROM stuMarks
(INDEX=IX_stuMarks_writtenExam) WHERE writtenExam BETWEEN 60 ADN 90
视图:
视图是保存在数据库中的SELECT 查询,因此,对查询执行的大多数操作也可在视图上进行,使用视图的原因有二:一是出于安全上考虑,用户不必看到真个数据库结构,而隐藏部分数据;二是符合用户日常业务逻辑,使他们对数据更容易理解。
视图通常用来:
筛选表中的行
防止未经许可的用户访问敏感数据
将多个物理数据表抽象为一个逻辑数据表
对用户和开发人员带来的好处:
1、对最终用户的好处
结果更容易理解。创建视图时,可以将列名改为有意义的名称,使用户更容易理解列所代表的内容。在视图中修改列名不会影响基表的列名。
获得数据更容易。很多人对SQL不太了解,因此对他们来说,创建对多个表的复杂查询很困难。因此可以通过创建视图来方便用户访问多个表中的数据。
2、对开发人员好处
限制数据检索。开发人员优势需要隐藏某些行货列中的信息。通过使用视图,用户可以灵活的访问他们需要的数据,同时保证同一个表或其他表中的其他数据的安全性。要实现这一目标,可以再创建视图时将要对用户保密的列排除在外。
维护应用程序更方便。调试视图比调试查询更容易。跟踪视图中过程的各个步骤中的错误更为容易,这是因为所有的步骤都是视图的组成部分。
使用T-SQL语句创建视图,语法为:
CREATE VIEW view_name
AS
集合。存储过程存储在数据库内,可由应用程序通过一个调用执行,而且允许用户声明变量、逻辑控制语句以及其他强大的编程功能。
存储过程可包含逻辑控制语句和数据操作语句,它可以接收参数、输出参数、返回单个或多个结果集以及返回值。
存储过程在创建时即在服务器上进行编译,所以执行起来比单个SQL语句块。
类似于JAVA语言中的类库,SQL Server提供了一些预编译的存储过程,用以管理SQL Server和现实有关数据库和用户的信息。这些存储过程成为"系统存储过程".
SQL Server中的存储过程的特征如下。
接受输入参数,并向调用过程或语句返回值。
包含在数据库中执行操作或调用其他存储过程的编程语句。
向调用过程返回状态值,只是执行过程是否成功(如果失败,还返回失败原因)
存储过程可以只包含一条SELECT语句,也可以包含一系列使用控制流的SQL语句。
使用存储过程有下列优点。
1、允许模块化程序设计
2、允许更快地执行
3、减少网络流量
4、可作为安全机制使用
常用的系统存储过程
SQL Server提供系统存储过程,它们是一组预编译的T-SQL语句。系统存储过程提供了管理数据库和更新表的机制,并充当从系统表中检索信息的快捷方式。
通过配置SQL Server,可以生成对象、用户、权限的信息和定义,这些信息和定义存储在系统表中。每个数据库都分别有一个包含配置信息的系统表集,用户数据库的系统表是在创建数据库时自动创建的。用户可以通过系统存储过程访问和更新系统表。
所有系统存储过程的名称都以"sp_"开头,并存放在master数据表中。系统管理员拥有这些存储过程的使用权限。可以再任何数据库中运行系统存储过程,但执行的结果会反映在当前数据库中。
常用的系统存储过程
系统存储过程 说明
sp_databases 列出服务器上的所有数据库
sp_helpdb 报告有关指定数据库或所有数据库的信息
sp_renamedb 更改数据库的名称
sp_tables 返回当前环境下可查询的对象的列表
sp_columns 返回某个表列的信息
sp_help 查看某个表的所有信息
sp_helpconstraint 查看某个表的约束
sp_helpindex 查看某个表的索引
sp_stored_procedure 列出当前环境中的所有存储过程
sp_password 添加或修改登录账户的密码
sp_helptext 显示默认值、未
加密的存储过程、用户定义的存储过
程、触发器或视图的实际文本
示例1:
--Purpose:常用系统存储过程的使用
EXEC sp_databases --列出当前系统中的数据库
EXEC sp_renamedb 'Northwind','Northwind1' --改变数据库名称(单用户访问)
USE stuDB
GO
EXEC sp_tables --当前数据库中可查询对象的列表
EXEC sp_columns stuInfo --查看表stuInfo中列的信息
EXEC sp_help stuInfo --查看表stuInfo的所有信息
EXEC sp_helpconstraint stuInfo --查看表stuInfo的约束
EXEC sp_helpindex stuMarks --查看表stuMarks的索引
EXEC sp_helptext 'view_stuInfo_stuMarks' --查看视图的语句文本
EXEC sp_stored_procedures --返回当前数据库中的存储过程列表
另外,还有一个常用的扩展存储过程:xp_cmdshell,它可以完成DOS命名下的一些操作,诸如创建文件夹、列出文件列表等。例如,希望创建的数据库保存在D:\project目录。如果当前没有此目录,使用CREATE DATABASE语句创建时会报错,如何解决呢?我们就可以使用扩展存储过程来创建文件夹。语法如下:
EXEC xp_cmdshell DOS命令 [NO_OUTPUT]
其中EXEC表示调用存储过程,NO_OUTPUT为可选参数,设置执行DOS命令后是否输出返回信息。
-- Purpose:xp_cmdshell 扩展存储过程的使用
USE master
GO
/*--创建数据库bankDB,要求保存在D:\bank--*/
EXEC xp_cmdshell 'mkdir D:\bank',NO_OUTPUT --创建文件夹D:\bank
----创建建库bankDB
IF exists(SELECT * FROM sysdatabases WHERE name='bankDB')
DROP DATABASE bankDB
GO
CREATE DATABASE bankDB
ON
{
NAME='bankDB_data',
FILENAME='D:\bank\bankDB_data.mdf',
SIZE=3MB
FILEGROWTH=15%
}
LOG ON
{
NAME='bankDB_log',
FILENAME='D:\bank\bankDB_log.ldf',
SIZE=3MB,
FILEGROWTH=15%
}
GO
EXEC xp_cmdshell 'dir D:\bank\' --查看文件
用户定义的存储过程
用于创建存储过程的T-SQL语句为CREATE PROCEDURE.所有存储过程都创建在当前数据库中。
创建不带参数的存储过程,语法如下:
CREATE PROC[EDURE] 存储过程名
[ {@参数1 数据类型}[=默认值] [OUTPUT],
......,
{@参数n 数据类型}[=默认值] [OUTPUT]
]
AS
SQL语句
其中,参数部分可选。
USE stuDB
GO
/*--检测是否存在:存储过程存放在系统表sysobjects中--*/
IF EXISTS (SELECT * FROM sysobjects WHERE NAME = 'proc_stu')
DROP PROCEDURE proc_stu
GO
/*--创建存储过程--*/
CREATE PROCEDURE proc_stu
AS
DECLARE @writtenAvg float,@labAvg float --笔试平均分和机试平均分变量
SELECT @writtenAvg=AVG(writtenExam),@labAvg=AVG(labExam) FROM stuMarks
print '笔试平均分:'+convert(varchar(5),@writtenAvg)
print '机试平均分:'+convert(varchar(5),@labAvg)
IF (@writtenAvg>70 AND @labAvg>70)
print '本班考试成绩:优秀'
ELSE
print '本班考试成绩
:较差'
print '-------------------------------------------------------------'
print ' 参加本次考试没有通过的学员 '
SELECT stuName,stuInfo.stuNo,writtenExam,labExam FROM stuInfo INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo
WHERE writtenExam<60 OR labExam<60GO
/*--调用执行存储过程--*/
EXEC proc_stu --调用存储过程的语法:EXEC 过程名 [参数]
创建带输入参数的存储过程
在java中,调用带参数的方法时,我们需要传递实际参数值给形式参数(参数)。例如,调用求两个数之和的方法int sum(int a,int b),求5和8之和,则调用形式为:int c=sum(5,8),返回值将赋值给变量c。
存储过程中的参数与此非常类似,存储过程中的参数分为以下两种。
输入参数:可以在调用时向存储过程传递参数,此类参数可用来在存储过程中传入值。
输出参数:如果希望返回值,则可以使用输出参数,输出参数后有"OUTPUT"标记,执行存储过程后,将把返回值存放在输出参数中,可供其他T-SQL语句读取访问。
带输入参数的存储过程T-SQL语法如下
CREATE PROC[EDURE] 存储过程名
@参数1 数据类型 [=默认值] [OUTPUT]
...,
@参数n 数据类型 [=默认值] [OUTPUT]
AS
SQL语句
其中,如果参数后面有"OUTPUT"关键字,表示此参数为输出参数,否则视为普通的输入参数,输入参数还可以设置默认值。
示例:
USE stuDB
GO
/*--检测是否存在:存储过程存放在系统表sysobjects中--*/
IF EXISTS (SELECT * FROM sysobjects WHERE name='proc_stu')
DROP PROCEDURE proc_stu
GO
/*--创建存储过程--*/
CREATE PROCEDURE proc_stu
@writtenPass int, --输入参数:笔试及格线
@labPass int --输入参数:机试及格线
AS
print '---------------------------------------------'
print ' 参加本次考试没有通过的学员: '
SELECT stuName,stuInfo.stuNo,writtenExam,labExam FROM stuInfo INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo
WHERE writtenExam<@writtenPass OR labExam<@labPass
GO
/*--调用存储过程--*/
--假定本次考试机试偏难,机试的几个线定为55分,笔试及格线定为60分
EXEC proc_stu 60,55
--或这样调用:EXEC proc_stu @labPass=55,@writtenPass=60
调用带参数的存储过程时,参数60和55将分别传递给输入参数@writtenPass
上述带参数的存储过程确实比较方便,调用者可以随时更改每次考试的及格线。但如果考试的难易程度合适,则调用者还是必须如此调用。
EXEC proc_stu 60,60
这就显得比较麻烦,能不能在调用时,存储过程的参数变为可选呢?例如如果调用形式如下:
EXEC proc_stu 55 --表示笔试及格线55分,机试及格线默认为60分
EXEC proc_stu --表示笔试和机试及格线都默认为标准的60分
答案是肯定的。存储过
程的输入参数允许采用默认值。
创建带输出参数的存储过程
如果希望调用存储过程后,发牛一个或多个值,这时需要使用输出(OUTPUT)参数
@notpassSum int OUTPUT --OUTPUT 关键字,否则视为输入参数
/*--调用存储过程--*/
DECLARE @sum int --定义变量,用于存放调用存储过程时返回的结果
EXEC proc_stu @sum OUTPUT,64 --调用时也带OUTPUT关键字,机试及格线默认为60
反复强调的是,使用输出参数创建存储过程时,在参数后面需要跟随"OUTPUT"关键字,调用时也需要在变量后跟随"OUTPUT"关键字。
处理错误信息
如果存储过程变得越来越复杂,则需要在存储过程中加入错误检查语句。在存储过程中,可以使用PRINT语句显示用户定义的错误信息。但是,这些信息时临时的,且只能显示给用户。RAISERROR返回用户定义的错误信息时,可指定严重级别,设置系统变量记录所发生的错误。
RAISERROR语法:
RAISERROR ({msg_id|mag_str}{,severity,state}[WITH option[,...n]])
其中,
msg_id:在sysmessages系统表中指定的用户定义错误信息
msg_str:用户定义的特定信息,最长255个字符
severity:与特定信息想关联,表示用户定义的严重性级别。用户可使用的级别为0-18级。19-25级是为sysadmin固定角色的成员预留的,并且需要指定WITH LOG选项。20-25级错误被认为是致命错误。
state:表示错误的状态,是1-127的值。
option:指示是否将错误记录到服务器错误日志中。
/*--调用存储过程--*/
DECLARE @sum int,@t int
EXEC proc_stu @sum OUTPUT,604 --笔试及格线输入604分
SET @t=@@ERROR --如果出现了错误,执行了RAISERROR语句,系统全局@@ERROR将不等于0,表示有错
print '错误号:'+convert(varchar(5),@t)
IF @t<>0
RETURN --退出批处理,后续语句不再执行
print '-------------------------------------'
IF @sum>=3
print '未通过人数:'+convert(varchar(5),@sum)+'人,超过60%,及格分数线还应下调'
ELSE
print '未通过人数:'+convert(varchar(5),@sum)+'人,已控制在60%以下,及格分数线适中‘
GO
当调用者将笔试及格线误输入为604分,将执行RAISERROR('及格线错误,请指定0-100之间的分数,统计中断退出',16,1)
引用系统错误,指定错误的严重级别为16,调用状态1(默认)。错误的严重级别大于10,将自动设置系统全局变量@@ERROR为非零值,表示语句执行出错。所以,我们可以在调用存储过程后,判断全局变量@@ERROR是否为0,决定是否还继续执行后续语句。