实训数据库开发应用——数据完整性

1．写出定义满足如下条件的表的sql语句，表结构为：

学生表（

学号：字符型，长度为6，主码，

姓名：字符型，长度为6，取值不重，

性别：字符型，长度为2，取值只能是‘男’和女’，

年龄：小整型，取值范围为15~45，

所在系：字符型，长度为12，取值只能是‘IS’、‘CS’和‘MA’，

电子邮件：字符型，长度为14，取值形式为：6位名字（每一位必须为字母）+‘@263.net’)

createtablesss

(学号char(6)primarykey,

姓名char(6)unique,

性别char(2)check(性别in('男','女')),

年龄smallintcheck(年龄between15and45),

所在系char(12)check(所在系in('IS','CS','MA')),

电子邮件char(14)check(电子邮件like

'[a-z][a-z][a-z][a-z][a-z][a-z]@263.net'))

2．用CreateTable语句创建如下表，并实现其全部约束。

教师表，结构为：

教师号：普通字符编码定长字符型，长度为8，主码，

教师名：普通字符编码定长字符型，长度为8，非空，

职称：普通字符编码定长字符型，长度为6，取值范围为：{教授、副教授、讲师}，

工资：定点小数，小数点前4位，小数点后2位，大于1000

联系电话：普通字符编码定长字符型，长度为14，取值形式为：‘(3位区号)-8位号码’，除8位号码中的第一位从1开始之外，其他号码均为0~9

出生日期：小日期时间型，小于等于当前日期，

参加工作日期：小日期时间型，小于等于当前日期，

退休日期：小日期时间型，大于参加工作日期)

createtable教师表(

教师号char(8)primarykey,

教师名char(8)notnull,

职称char(6)check(职称in('教授','副教授','讲师')),

工资numeric(6,2)check(工资>1000),

联系电话char(14)check(联系电话like'([0-9][0-9][0-9])-[1-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),

出生日期smalldatetimecheck(出生日期<=getdate()),

参加工作日期smalldatetimecheck(参加工作日期<=getdate()),

退休日期smalldatetime,

check(参加工作日期<退休日期)

)

3．创建一个顾客购物数据库，在此数据库中创建如下两张表：

顾客表（顾客ID字符型长度为10，

顾客名字符型长度为10，

电话字符型长度为12，

地址字符型最长为30，

社会保险号码字符型长度为15）

订购表（商品ID字符型长度为15，

商品名称字符型长度为20，

顾客ID字符型长度为10，

订购数量整型，

订购日期短日期型，

交货日期短日期型）

使用图形化工具或SQL语句实现如下约束：

--1.为顾客表添加主码约束，为订购表添加主码约束

--2.为订购表添加外码约束

--3.限制顾客表电话号码的形式:三位区号-8位电话号码,每位均为数字

--4.当顾客没有提供地址时,使用默认值'UNKNOWN'

--5.限制订购表的"订购数量"必须大于0

--6.限制订购表的"订货日期"必须早于"交货日期"

--7.限制顾客表"社会保险号码"取值不能重复

createtable顾客表(

顾客IDchar(10)notnull,

顾客名char(12),

电话char(12),

地址varchar(30),

社会保险号码char(15))

droptable订购表

createtable订购表(

商品IDchar(20)notnull,

商品名称varchar(20)notnull,

顾客IDchar(10)notnull,

订购数量int,

订货日期smalldatetimenotnull,

交货日期smalldatetime)

--1.为顾客表添加主码约束

altertable顾客表

addconstraintpk_ID

primarykey(顾客ID)

--为订购表添加主码约束

altertable订购表

addconstraintpk_dg

primarykey(商品ID,顾客ID,订货日期)

--2.为订购表添加外码约束

altertable订购表

addconstraintfk_dg

foreignkey(顾客ID)references顾客表(顾客ID)

--3.限制顾客表电话号码的形式:三位区号-8位，号码,每位均为数字

altertable顾客表

addconstraintchk_phone

check(电话like'[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')

--4.当顾客没有提供地址时,使用默认值'UNKNOWN'

altertable顾客表

addconstraintdf_dz

default'UNKNOWN'for地址

--5.限制订购表的"订购数量"必须大于0

altertable订购表

addconstraintchk_dgsl

check(订购数量>0)

--6.限制订购表的"订货日期"必须早于"交货日期"

altertable订购表

addconstraintchk_1

check(订货日期<交货日期)

--7.限制顾客表"社会保险号码"取值不能重复

altertable顾客表

addconstraintun_1

unique(社会保险号码)

<!--EndFragment-->