SQLServer 2008 (三)

来源：互联网发布：foxpro数据库下载编辑：程序博客网时间：2024/06/06 04:17

检查约束check():

范围check：

if exists(select name from sysobjects where name='person' and xtype='u')drop table personcreate 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))gocreate 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 phonecreate table phone(number char(20) check(number like '010-[1-9][0-9]{7}'))godeclare @number varchar(20)while (COUNT(*)<=3)beginset @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)endgo

统计函数：
最小值函数 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)<>50begindeclare @score int;declare @id int;set @id=1+floor(rand()*100)set @score=ceiling(rand()*100);insert into score values(@id,@score)endgo

场景：其中考试结束了，考了语、数、外三门，成绩在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

0 0