模块六-阶段2

1、
建立一个商店的数据库store，记录顾客及其购物情况，由下面三个表组成：
商品（商品号，商品名，单价，商品类别，供应商）；
顾客（顾客号，姓名，住址）；
购买（顾客号，商品号，购买数量）；

试用SQL语言完成下列功能： 建表，在定义中要求声明：
（1）每个表的主外码；
（2）顾客的姓名和商品名不能为空值；
（3）单价必须大于0；
（4）购买数量必须在0到20之间；

2、
写出定义满足如下条件的表的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')
)
3、
用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(参加工作日期<退休日期)
)

4、
创建一个顾客购物数据库，在此数据库中创建如下两张表：
顾客表
（顾客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(社会保险号码)