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

 

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

  学生表（

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

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

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

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

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

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

create table sss

(学号 char(6) primary key,

姓名 char(6) unique,

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

年龄 smallint check(年龄 between 15 and 45),

所在系 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．用Create Table 语句创建如下表，并实现其全部约束。

教师表，结构为：

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

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

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

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

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

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

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

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

create table 教师表(

教师号 char(8) primary key,

教师名 char(8) not null,

职称 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]'),

出生日期 smalldatetime check(出生日期<=getdate()),

参加工作日期 smalldatetime check(参加工作日期<=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.限制顾客表"社会保险号码"取值不能重复

create table 顾客表 ( 

顾客ID  char(10) not null,

顾客名  char(12),

电话 char(12),

地址 varchar(30),

社会保险号码 char(15))

drop table 订购表

create table 订购表 ( 

  商品ID char(20) not null,

  商品名称 varchar(20) not null,

  顾客ID char(10) not null,

  订购数量 int,

  订货日期 smalldatetime not null,

  交货日期 smalldatetime )

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

alter table 顾客表

  add constraint pk_ID

  primary key(顾客ID)

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

alter table 订购表

add constraint pk_dg

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

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

alter table 订购表

  add constraint fk_dg

  foreign key(顾客ID) references 顾客表(顾客ID)

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

alter table 顾客表

 add constraint chk_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'

alter table 顾客表

 add constraint df_dz

 default 'UNKNOWN' for 地址

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

alter table 订购表

 add constraint chk_dgsl

 check (订购数量>0)

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

alter table 订购表

 add constraint chk_1

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

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

alter table 顾客表

 add constraint un_1

 unique(社会保险号码)