常用SQL语句收藏 <四>

约束

约束是在修改一个表,或增加一个表记录时,规定表的各个字段之间必须满足的关系.ALTER TABLE studentADD CONSTRAINT age_negative CHECK (student.age >= 0 );约束是表结构的一部分内容,此例的约束是检查年龄字段不要为负值.Update student set age=-1;//将会报错.删除一个约束:alter table student drop constraint age_negative;

主键

主键:表通常具有包含唯一标识表中每一行的值的一列或一组列。这样的一列或多列称为表的主键 (PK)，用于强制表的实体完整性。在创建或修改表时，您可以通过定义 PRIMARY KEY 约束来创建主键。一个表只能有一个 PRIMARY KEY 约束，并且 PRIMARY KEY 约束中的列不能接受空值。由于 PRIMARY KEY 约束可保证数据的唯一性，因此经常对标识列定义这种约束。create table dbo.test6(col1 nvarchar(25) NOT NULL DEFAULT 'abc', col2 int DEFAULT 23 primary key);首先将原来的主键删除,再建联合主键alter table test6 ADD CONSTRAINT  constraint_1 PRIMARY KEY (col1,col2);在两个字段上建联合主键.

外键

外键 (FK) 是用于建立和加强两个表数据之间的链接的一列或多列。当创建或修改表时可通过定义 FOREIGN KEY 约束来创建外键。在外键引用中，当一个表的列被引用作为另一个表的主键值的列时，就在两表之间创建了链接。这个列就成为第二个表的外键。ALTER TABLE student ADD CONSTRAINT FK_class FOREIGN KEY (class) REFERENCES class (class) ; 删除外键:alter table student drop CONSTRAINT  FK_CLASS建立外键时,要注意几点:外键在外表中要是主键或索引. 两个表的此字段名称最好一样.其他注意事项,项目参考相关数据库的联机帮助文档.例如:外键在外表中重新生成,或重新组织后,或外键的索引被删除后,引起的后果.需要仔细调试.

游标

set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER PROCEDURE [dbo].[get_avage_age] -- Add the parameters for the stored procedure here@class_name nchar(10),@CheckDate [datetime]ASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON;select avg(age) from student where class=@class_name and time<@CheckDate    -- Insert statements for procedure here

DECLARE @cur_age   as int DECLARE @avg_age as numeric(8) DECLARE @count as intDECLARE curObject CURSOR FOR  select age from student where class=@class_name and born<@CheckDate order by ageset @avg_age=0set @count=0OPEN  curObjectFETCH NEXT FROM curObject INTO @cur_ageWHILE(@@FETCH_STATUS=0)BEGIN       set @avg_age=@avg_age+@cur_age  set @count=@count+1   FETCH NEXT FROM curObject INTO @cur_ageENDset @avg_age=@avg_age/@countSELECT'AVAGE AGE' = @avg_age,'Student Sum' = @countclose curObjectdeallocate curObjectEND在此存储过程中, 使用游标来读取结果集中每个记录的字段值,再求平均值,看看与avg函数的功能是否一样的

使用游标逐条更新记录

DECLARE @cur_age   as int DECLARE @avg_age as numeric(8) DECLARE @count as intDECLARE curObject CURSOR FOR  select age from student where class=@class_name and born<@CheckDate order by age FOR UPDATE OF age,englishset @avg_age=0set @count=0OPEN  curObjectFETCH NEXT FROM curObject INTO @cur_ageWHILE(@@FETCH_STATUS=0)BEGIN       set @avg_age=@avg_age+@cur_age  set @count=@count+1   update student set english=@count where current of curObject  FETCH NEXT FROM curObject INTO @cur_ageENDset @avg_age=@avg_age/@countSELECT'AVAGE AGE' = @avg_age,'Student Sum' = @countclose curObjectdeallocate curObject根据游标更新表,还有很多选项, 涉及到的细节请具体参考相关数据库的联机帮助!