《SQL Server 2008从入门到精通》–20180716

发布时间:2019-01-22  栏目:sqlite  评论:0 Comments

1.锁

当五个用户同时对同一个数码举办修改时会爆发并发问题,使用工作就足以解决那个题目。但是为了以防其他用户修改另一个还没形成的政工中的数据,就须要在工作中用到锁。
SQL Server
2008提供了多种锁方式:排他锁,共享锁,更新锁,意向锁,键范围锁,架构锁和大容量更新锁。
查询sys.dm_tran_locks视图可以急忙明白SQL Server 2008内的加锁情形。

SELECT * FROM sys.dm_tran_locks;

注:关于锁的知识书中没细讲,将在其后的博客中补充。

约束

2.游标

游标是近乎于C语言指针一样的结构,是一种多少访问机制,允许用户访问单独的数据行。游标主要由游标结果集和游标地点组成。游标结果集是概念游标的SELECT语句重临行的集合,游标地点是指向那几个结果集中某一行的指针。
示例1:用游标检索出student表中每行记录
Student表记录如图所示
图片 1
实践下列语句

USE test
DECLARE stu_cursor CURSOR FOR
SELECT * FROM student--声明student表的游标stu
OPEN stu_cursor--打开游标
FETCH NEXT FROM stu_cursor--移动该记录指针
WHILE @@FETCH_STATUS=0--@@FETCH_STATUS用于保存FETCH操作的结束信息,=0表示有记录检索成功
BEGIN
FETCH NEXT FROM stu_cursor--游标指针移动到下一条记录
END
CLOSE stu_cursor--关闭游标
DEALLOCATE stu_cursor--释放游标资源

结果如图所示
图片 2

主关键字约束(Primary Key Constraint)

用来指定表中的一列或几列组合的值在表中具备唯一性。建立主键的目的是让外键来引用。

2.1.游标定义的参数LOCAL和GLOBAL

游标定义参数LOCAL表示该游标只好成效于此次批处理或函数或存储进度。游标定义参数GLOBAL表示该游标可以成效于大局。
履行下列语句

DECLARE stu_cursor CURSOR LOCAL
FOR SELECT * FROM student
GO
OPEN stu_cursor
GO

执行结果如下
图片 3
语句中,声明了一个student表的游标stu_cursor,在开拓游标时指示游标不存在。因为该游标参数是LOCAL,只能够成效于当下批处理语句中,而开辟游标语句和表明语句不在一个批处理中。假设去掉首个GO,使五个语句在同一个批处理中,就能得偿所愿推行不会报错。
执行下列语句

DECLARE stu_cursor1 CURSOR GLOBAL
FOR SELECT * FROM student
GO
OPEN stu_cursor1
GO

实施结果:命令已成功做到
和LOCAL参数相比,GOLBAL参数设置游标作用于大局,因而OPEN和DECLARE语句不在同一个批处理中依然可以成功举行。

Primary Key的创始方式

在开立表时创造Primary Key

CREATE TABLE table1(
    t_id VARCHAR(12) ,
    t_name VARCHAR(20),
    t_phone VARCHAR(20),
    CONSTRAINT t_idss PRIMARY KEY(t_id)
);

对t_id列创制主键,约束名为t_idss。

2.2.游标分为游标变量和游标类型

一般来说列语句

--语句1
DECLARE stu_cursor CURSOR
FOR SELECT * FROM student--声明student表的游标名称为stu_cursor并赋值
GO

--语句2
DECLARE @stu_cursor CURSOR--声明游标类型的变量@stu_cursor
SET @stu_cursor=CURSOR FOR--给该变量赋值
SELECT * FROM student

在语句1中直接申明了一个游标并赋值,而语句2中扬言了游标类型的变量@stu_cursor,然后给该变量赋值。这二者是例外的。

删除Primary Key
ALTER TABLE table1
DROP CONSTRAINT t_idss;

自律名与列名不雷同,此处填写约束名

2.3.游标参数FORWARD_ONLY和SCROLL

FORWARD_ONLY参数设置游标只好从结果集的伊始向为止方向读取,使用FETCH语句时不得不用NEXT,而SCROLL参数设置游标可以从结果集的即兴方向,任意地方移动。如下列语句

--语句1,默认FORWARD_ONLY
DECLARE stu_cursor CURSOR LOCAL
FOR SELECT * FROM student
OPEN stu_cursor
FETCH NEXT FROM stu_cursor
GO
--语句2,FORWARD_ONLY参数,FETCH时只能从开始往结束方向
DECLARE stu_cursor CURSOR FORWARD_ONLY LOCAL
FOR SELECT * FROM student
OPEN stu_cursor 
FETCH NEXT FROM stu_cursor
GO
--语句3,SCROLL参数,FETCH时可以从任意位置往任意方向
DECLARE stu_cursor CURSOR SCROLL LOCAL
FOR SELECT * FROM student
OPEN stu_cursor
FETCH LAST FROM stu_cursor
GO
向已有表中添加Primary Key
ALTER TABLE table1
ADD CONSTRAINT t_idss
PRIMARY KEY(t_id);

2.4.游标的简易利用

示例2:将student表中stu_enter_score大于600分的学员都减去100分
Student表中的数据如图所示
图片 4
实践下列语句

--游标的简单应用
DECLARE stu_cursor CURSOR FORWARD_ONLY LOCAL
FOR SELECT stu_no,stu_enter_score FROM student
OPEN stu_cursor
DECLARE @score INT
DECLARE @stu_no VARCHAR(8)
FETCH NEXT FROM stu_cursor INTO @stu_no,@score--把游标值赋值给变量@score
WHILE @@FETCH_STATUS=0 
BEGIN
IF @score>=600
BEGIN
UPDATE student
SET stu_enter_score=@score-100 WHERE stu_no=@stu_no 
END
FETCH NEXT FROM stu_cursor INTO @stu_no,@score--把游标值赋值给变量@score
END
CLOSE stu_cursor
DEALLOCATE stu_cursor
GO

结果如图所示
图片 5

增进Primary Key的另一种示例
ALTER TABLE Products
ADD PRIMARY KEY(prod_id);

虽说上述代码运行没问题,查看表格设计也足以看看Primary
Key设置成功,可是在剔除Primary Key操作时会提醒:
消息3728,级别16,状态1,第1 行
‘prod_id’ 不是封锁。
消息3727,级别16,状态0,第1 行
决不可以删除约束。请参阅前边的错误音讯。
由来是加上Primary
Key语句中绝非用CONSTRAINT指明约束名,系统自动生成了主键名和约束名,要先查看主键名和约束名,删除时填写的也是束缚名。
那种景色的不易删除方法

ALTER TABLE Products
DROP CONSTRAINT CK__Products__prod_p__1A14E395;
ALTER TABLE Products
DROP CONSTRAINT PK__Products__56958AB222AA2996;

3.储存进度

存储进度是一组用于达成一定成效的语句集,经过编译后存储在数据库中。在SQL
Server 2008中,既可以用T-SQL编写存储进度,也得以用CLR编写存储进度。

多列组合添加主键约束
CREATE TABLE table1(
    t_id VARCHAR(12),
    s_id VARCHAR(20),
    score FLOAT,
    CONSTRAINT ts_id PRIMARY KEY(t_id,s_id)
);

3.1.用户定义的积存进度

该种存储进度是指封装了可接纳代码的模块或者经过,有2体系型:T-SQL存储进度和CLR存储进程。
T-SQL存储进度是指保存的T-SQL语句集合
CLR存储进程是指对Microsoft .NET Framework公共语言运行时(CLR)方法的引用

外关键字约束(Foreign Key Constraint)

概念了表之间的涉嫌,用来维护七个表之间的一致性的涉及。
在制造表时成立Foreign Key Constraint

CREATE TABLE table2(
    s_id VARCHAR(20),
    s_name VARCHAR(12),
    s_tellphone VARCHAR(11),
    s_address VARCHAR(20),
    CONSTRAINT PK_s_id PRIMARY KEY(s_id),
);--首先新建table2,设置s_id为主键

CREATE TABLE table1(
    t_id VARCHAR(12),
    s_id VARCHAR(20),
    score FLOAT,
    CONSTRAINT pk_ts_id PRIMARY KEY(t_id,s_id),--新建table1,对t_id和s_id设置联合主键,键名pk_ts_id
    CONSTRAINT fk_s_id FOREIGN KEY(s_id)--对s_id设置外键fk_s_id
    REFERENCES table2(s_id)--外键fk_s_id外键关联table2的列s_id
    ON DELETE CASCADE--设置在table1的s_id删除时table2的s_id同时删除
    ON UPDATE CASCADE--设置在table1的s_id更新时table2的s_id同时更新
);

注:对table1设置外键关联table2,在插入数据时索要先插入table2的数额,才能得逞插入table1的多少。更改table2.s_id数据,table1.s_id数据也会自行改变。可是更改table1.s_id数据,执行时报外键争执。不问可知对table1设置外键关联table2后,table1的多少跟着table2走,不可能反着来。

丰富和删除外键约束同主键。

3.2.恢弘存储进程

扩大存储进程是指能够动态加载和运行的DLL,允许行使编程语言(如C语言)制造和谐的外表例程。扩张存储进程一向在SQL
Server 2008的实例的地点空间中运行,可以行使SQL
Server伸张存储进程API已毕编程。

UNIQUE约束

除主键外另一种可以定义唯一约束的档次,允许空值。添加UNIQUE的不二法门同上,那里只简单举例。

USE test
GO
ALTER TABLE table2
ADD CONSTRAINT uk_s_tellphone
UNIQUE(s_tellphone);

3.3.系统存储进度

系统存储进程是指储存在源数据库中,以sp开端的贮存进度,现身在每个系统定义数据库和用户定义数据库的sys架构中。

CHECK约束

分成表约束和列约束,用于限制字段值在某个范围。

3.3.1.成立存储进度规则

在统筹和成立存储进程时,应该满意一定的羁绊和规则。

  • CREATE
    PROCEDURE定义自己可以包涵自由数量和类型的SQL语句,但下表中的语句除外。不可以在存储进度的其他地点应用那几个言辞。
  • 可以引用在统一存储进度中创立的目的,只要引用时已制造了该对象
  • 能够在存储进度内引用临时表
  • 假如在仓储进度中创设了本土临时表,该临时表仅为该存储进度而存在,退出该存储进度后,该临时表会消失
  • 倘诺履行的蕴藏进度调用了另一个储存进程,被调用的储存进程可以访问第二个存储进程的有着目的,包涵临时表
  • 万一执行对长距离SQL Server
    2008实例进行改动的中远距离存储进程,那个改变将不可以被回滚。远程存储进程不参加事务处理
  • 存储进度中的参数的最大数量为2100
  • 仓储进程中的局地变量的最大数据仅受可用内存的范围
  • 根据可用内存的不一致,存储进程最大可达128MB
语句 语句 语句
CREATE AGGREGATE CREATE RULE CREATE DEFAULT
CREATE SCHEMA CREATE(ALTER) FUNCTION CREATE(ALTER) TRIGGER
CREATE(ALTER) PROCEDURE CREATE(ALTER) VIEW SET PARSEONLY
SET SHOWPLAN_ALL SET SHOWPLAN_TEXT SET SHOWPLAN_XML
USE database_name
添加CHECK约束
ALTER TABLE table2
ADD sex CHAR(2);--在table表中添加sex,数据类型为CHAR,用来存放性别
GO
ALTER TABLE table2
ADD CONSTRAINT ck_sex CHECK(sex in('男','女'));

注:此时sex列数据类型无法是bit,若是填写bit,只好存储0和1,用CHECK约束限制结果为男和女就会报错。

3.3.2.范围存储进程内的名号

在仓储进度内,如果用于语句的靶子没有界定架构,则架构将默许为该存储进程的架构。若是创立该存储进度的用户没有限定INSERT,SELECT,UPDATE或DELETE语句中援引的表名或试图名,则默许情形下通过该存储进程进展的造访将蒙受该进度成立者权限的界定。如若有其他用户要选用存储进程,则具有用于数据定义语言(DDL)的说话(如CREATE,ALTER,EXECUTE,DROP,DBCC或动态SQL语句)的靶子名应当用该目的架构的称谓来界定。

在创设表的时候添加CHECK 约束
CREATE TABLE table3(
    t3_id VARCHAR(12),
    t3_name VARCHAR(20),
    t3_class VARCHAR(12),
    t3_type VARCHAR(12),
    CONSTRAINT ck_t3_type CHECK(t3_type in('类型1','类型2','类型3')) 
)

添加了约束后假设插入不相符约束的数额

INSERT INTO table3(
    t3_id,
    t3_name,
    t3_class,
    t3_type
)VALUES(
'2018038219',
'李建',
'社会与科学',
'任何数据'
);

消息547,级别16,状态0,第1 行
INSERT 语句与CHECK
约束”ck_t3_type”争持。该冲突时有暴发于数据库”test”,表”dbo.table3″, column
‘t3_type’。

说话已偃旗息鼓。

3.3.3.加密存储进度的概念

比方要创设存储进程并确保其他用户不能查看该存储进度的概念,则能够运用WITH
ENCRYPTION,那样,进度定义将以不足读的样式储存。

删除CHECK约束
ALTER TABLE table3
DROP CONSTRAINT ck_t3_type;

3.3.4.SET语句选项

当创制或者更改T-SQL存储进度后,数据库引擎将保留SET
QUOTED_IDENTIFIER和SET
ANSI_NULLS的安装,执行存储进度时将利用这几个原本设置而忽视任何客户端会话的ET
QUOTED_IDENTIFIER和SET
ANSI_NULLS设置。其他SET选项在创立或变更存储进度后不保留。

DEFAULT约束

通过定义列的默认值或选取数据库的默许值对象绑定表列,来指定列的默许值。

3.4.用到存储进程

在建表时添加DEFAULT约束
CREATE TABLE table3(
    t3_id VARCHAR(12),
    t3_name VARCHAR(20),
    t3_class VARCHAR(12),
    t3_type VARCHAR(12) DEFAULT '类型1' 
)

3.4.1.成立存储进程

示例3:将示例2用存储进度落成
Student表的多寡如图所示
图片 6
实践下列语句

CREATE PROCEDURE alter_data
@a int--参数
AS
BEGIN
DECLARE stu_cursor CURSOR FORWARD_ONLY LOCAL
FOR SELECT stu_no,stu_enter_score FROM student
OPEN stu_cursor
DECLARE @score INT
DECLARE @stu_no VARCHAR(8)
FETCH NEXT FROM stu_cursor INTO @stu_no,@score--把游标值赋值给变量@score
WHILE @@FETCH_STATUS=0 
BEGIN
IF @score>=@a
BEGIN
UPDATE student
SET stu_enter_score=@score-100 WHERE stu_no=@stu_no 
END
FETCH NEXT FROM stu_cursor INTO @stu_no,@score--把游标值赋值给变量@score
END
CLOSE stu_cursor
DEALLOCATE stu_cursor
END
GO
EXEC dbo.alter_data '600'

结果如图所示
图片 7

删除DEFAULT约束
ALTER TABLE table3
DROP CONSTRAINT DF__table3__t3_type__3D5E1FD2;
--DF__table3__t3_type__3D5E1FD2是DEFAULT约束的约束名

3.4.2.翻看存储进程

可以由此运用系统存储进程或者目录视图查看存储过程的概念

累加封锁不指定约束名
ALTER TABLE table3
ADD DEFAULT '类型2' FOR t3_type;
GO
3.4.2.1.图形化界面

如下图
图片 8

加上封锁指定约束名
ALTER TABLE table3
ADD CONSTRAINT df_t3_type
DEFAULT '类型2' FOR t3_type;
GO
3.4.2.2.种类存储进程sp_helptext查看存储进程定义

举行下列语句

EXEC sp_helptext 'alter_data'

结果如图所示
图片 9

NOT NULL约束

自律字段值不为空。

3.4.2.3.系统存储进程sp_depends查看存储进度有关音信

举办下列语句

EXEC sp_depends 'alter_data'

结果如图所示
图片 10

建表时设置NOT NULL约束
CREATE TABLE table3(
    t3_id VARCHAR(12) NOT NULL,
    t3_name VARCHAR(20) NOT NULL,
    t3_class VARCHAR(12) NOT NULL,
    t3_type VARCHAR(12) NOT NULL 
)
3.4.2.4.目录视图查看存储进程

实践下列语句

SELECT * FROM sys.procedures

结果如图所示
图片 11

为已存在的列添加NOT NULL约束
ALTER TABLE table3
ALTER COLUMN t3_type VARCHAR(12) NOT NULL;

3.4.3.改动存储进度

ALTER
PROCEDURE
语句修改存储进度,只需将上面示例中的CREATE修改成ALTER运行就行了。

删除NOT NULL约束
ALTER TABLE table3
ALTER COLUMN t3_type VARCHAR(12) NULL;
GO

3.4.4.剔除存储进程

执行下列语句删除存储进度

DROP PROCEDURE alter_data

自定义默许值对象有限支持数据完整性

CREATE DEFAULT date_today AS GETDATE();
--新建默认值对象名date_today,默认值为getdate()函数,获取当前日期
GO
EXEC sp_addtype date_time,'date','NULL';
--利用存储过程新建自定义数据类型date_time,参照系统数据类型date
GO
EXEC sp_bindefault 'date_today','date_time';
--将默认值对象date_today绑定到自定义数据类型date_time上
GO
CREATE TABLE table3(--新建table3,设置字段t3_date的数据类型为date_time
    t3_id VARCHAR(12),
    t3_name VARCHAR(20),
    t3_class VARCHAR(12),
    t3_type VARCHAR(12),
    t3_date date_time
);
GO
INSERT INTO table3(--为新建表table3插入一条数据,不指定t3_date的值,看默认值是否有效
    t3_id,
    t3_name,
    t3_class,
    t3_type
)VALUES(
'2018038220',
'李建',
'社会与科学',
'类型1'
);
GO
SELECT * FROM table3;--查询table3数据,看t3_date是否有默认值为当前日期

询问结果如下
图片 12

ALTER TABLE table3
ADD t3_date1 DATE;--在table3表中新增一列t3_date1,数据类型为DATE
GO
EXEC sp_bindefault 'date_today','table3.t3_date1';
--直接将默认值对象date_today绑定到table3的t3_date1列
GO
INSERT INTO table3(--为新建表table3插入一条数据,不指定t3_date和t3_date1的值,看默认值是否有效
    t3_id,
    t3_name,
    t3_class,
    t3_type
)VALUES(
'2018038221',
'李建',
'社会与科学',
'类型'
);
GO
SELECT * FROM table3;
GO

询问结果如下
图片 13

储存进程查询默许值对象的持有者
USE test
EXEC sp_help date_today;
GO

结果如图所示
图片 14

删去默许值对象
DROP DEFAULT date_today;

删除不成事,提醒以下新闻:
消息3716,级别16,状态3,第1 行
没辙删除默许值’date_today’,因为它已绑定到一个或多个列。
那么我们领会,当一个默许值对象绑定了列其后,就不可以删除它,即使想要删除,就必须先解绑。在上头的操作中,我们的默许值对象date_today绑定了test数据库table3表的t3_date1字段。

对列解绑默认值对象
USE test
GO
EXEC sp_unbindefault 'table3.t3_date1';

这会儿大家重新尝试删除默许值对象,发现如故老大,此时要小心,在上头的操作中,默认值对象date_today不止绑定了t3_date1列,还绑定了自定义数据类型date_time,并将该数据类型定义给了t3_date列,大家还要求对date_time解绑默许值对象。

EXEC sp_unbindefault 'date_time';

那时候再一次删除默许值对象,就可以成功删除。删除默许值对象后,原先绑定的字段不会再有默许值。

自定义规则维护数据完整性

规则是对列或自定义数据类型的值的确定和限量。自定义规则的表明式一定要赶回布尔类型的值,并且表达式中不可能包括八个变量。

CREATE RULE score_rule AS @math_score>=0;
GO--新建规则score_rule,参数@math_score
EXEC sp_addtype 'score_type','float','NULL';
GO--新建自定义数据类型score_type
CREATE TABLE table_score(--新建表table_score,预设mt_score和at_score字段用于绑定规则
s_id VARCHAR(4),
s_name VARCHAR(10),
mt_score float,--该字段将用于规则score_rule绑定到列
at_score score_type--该字段将用于规则score_rule绑定到自定义数据类型
);
GO
EXEC sp_bindrule 'score_rule','score_type';
GO--将score_rule规则绑定到自定义数据类型score_type
EXEC sp_bindrule 'score_rule','table_score.mt_score';
GO--将score_rule规则绑定到table_score表的mt_score列

----以下进行规则测试
INSERT INTO table_score(
s_id,
s_name,
mt_score,
at_score
)VALUES(
'0001',
'张华',
'-1',
'-1'
);
GO

开展违反规则的插入后,数据库报错
消息513,级别16,状态0,第1 行
列的插入或更新与原先的CREATE RULE
语句所指定的规则爆发争论。该语句已偃旗息鼓。顶牛时有暴发于数据库’test’,表’dbo.table_score’,列’mt_score’。

言语已偃旗息鼓。
很明显,mt_score的插入值为-1,违反了亟须高于等于0的规则,数据库报错。将mt_score的插入值改成符合规则的数额,再次运行插入语句,数据库如故会报错,因为at_score字段的插入值也是违反规则的。将四个数据改成符合规则的回到,执行成功。

注:新建规则时表明式一定若是回来布尔类型的值,否则会报错

消息4145,级别15,状态1,过程sum_score,第1 行
在应选择规范的上下文(在’;’ 附近)中指定了非布尔类型的表明式。

删除自定义规则

和自定义默认值对象一样,删除自定义规则必要该规则先与字段和自定义数据类型解绑。在上面的操作中,score_rule规则与自定义数据类型score_type以及列mt_score已绑定。由此实施以下语句:

EXEC sp_unbindrule 'score_type';
GO--解除规则score_rule与score_type之间的绑定
EXEC sp_unbindrule 'table_score.mt_score';
GO--解除规则score_rule与表table_score的mt_score列的绑定
DROP RULE score_rule;--删除score_rule规则

注:经过试验,一个列只好绑定1条规则,如果对一个列绑定2条规则,前一条规则会被后一条规则顶替。

查阅自定义规则
EXEC sp_help 'score_rule';

结果如图所示
图片 15

翻看自定义规则的概念新闻
EXEC sp_helptext 'score_rule';
GO

结果如图所示
图片 16

留下评论

网站地图xml地图