SQLServer 2008 (三)

检查约束check():

范围check：

if exists(select name from sysobjects where name='person' and xtype='u')
drop table person

create table person(
person_id int identity constraint pk_id primary key,
person_name varchar(10)not null,
person_age int constraint ck_age check(person_age>=0 and person_age<=120)
)
go

create table person(
person_id int identity constraint pk_id primary key,
person_name varchar(10) not null,
person_age int constraint ck_age check(person_age between 0 and 120)
)
go

‘in’check：

create table person(
person_id int identity constraint pk_id primary key,
person_name varchar(10) not null,
person_age int constraint ck_age check(person_age between 0 and 120),
person_gender nvarchar(1) constraint ck_gender check(person_gender in ('男','女'))
)
go

‘like’ check：

create table person(
person_id int identity primary key,
person_name varchar(10) not null,
person_age int check(person_age between 0 and 120),
person_gender nchar(1) check(person_gender in ('男','女')),
person_phone char(6) check(person_phone like '[0-9]{6}')
)
go

新建一个表，场合是一个电话局用来生成北京市的电话号码10000个。要求，区号为
010，电话为8位，首位不能为0。号码样式：010-12345678 （010）12345678

if exists(select name from sysobjects where name='phone' and xtype='u')
drop table phone


create table phone(
number char(20) check(number like '010-[1-9][0-9]{7}')
)
go

declare @number varchar(20)
while (COUNT(*)<=3)
begin
set @number=('010-'+cast(ceiling(RAND()*10) as varchar(1))
					+cast(ceiling(RAND()*10) as varchar(1))
					+cast(ceiling(RAND()*10) as varchar(1))
					+cast(ceiling(RAND()*10) as varchar(1))
					+cast(ceiling(RAND()*10) as varchar(1))
					+cast(ceiling(RAND()*10) as varchar(1))
					+cast(ceiling(RAND()*10) as varchar(1))
					+cast(ceiling(RAND()*10) as varchar(1))
			) 
insert into phone values(@number)
end
go

统计函数：
最小值函数 min(column_name)
最大值函数 max(column_name)
求和函数：sum(column_name)
求平均值函数：avg(column_name)
行记录数：count(*),count(column_name),后者不统计空值列所在的行


建一个成绩表，有两列数据，编号（主键），成绩;编号50个，用100以内的随机数表示；
成绩在0-100之间，包括两者。

while (select count(*) from score)<>50
begin
	declare @score int;
	declare @id int;
	set @id=1+floor(rand()*100)
	set @score=ceiling(rand()*100);
	insert into score values(@id,@score)	
end
go

场景：其中考试结束了，考了语、数、外三门，成绩在0-100之间。
要求包括表列中有学号（自增），三门成绩，总分，平均分。

create table score
(
	scid int identity primary key,
	chinese int not null check(chinese between 0 and 100),
	math int not null check(math between 0 and 100),
	english int not null check(english between 0 and 100),
	total_score as (chinese+math+english),
	average as (chinese+math+english)/3.0,
)
go