可以参考：
http://wenku.baidu.com/link?url=1WRORoWZDu2A9I5XEiVJAd1u9DBKLj84j1ulbpITF0c9jjDRQ_RhVCVc-MaVSSHw-CID-i6DOD-D2ZZj3jSwviRjFQgGoRLZ7MdDr6Cidj7建立数据库模式、视图及索引
create databasea_book
go
use a_book
go
CREATE TABLE list
( list_name CHAR(8) PRIMARY KEY,
list_sex CHAR(4),
list_mobile_number CHAR(16),
list_birthday CHAR(8),
list_address CHAR(80)
)
go
CREATE TABLE family
( family_family_number CHAR(16) PRIMARY KEY,
family_family_address CHAR(80)
)
go
CREATE TABLE office
( office_office_number CHAR(16) PRIMARY KEY,
office_office_address CHAR(80),
office_e_mailCHAR(20)
)
go
CREATE TABLE ListFamily
(list_name CHAR(8),
family_family_number CHAR(16),
PRIMARY KEY (list_name,family_family_number),
FOREIGN KEY (list_name) REFERENCES list(list_name),
FOREIGN KEY (family_family_number) REFERENCES family(family_family_number)
)
go
CREATE TABLE ListOffice
(list_name CHAR(8),
office_office_number CHAR(16),
PRIMARY KEY (list_name,office_office_number),
FOREIGN KEY (list_name) REFERENCES list(list_name),
FOREIGN KEY (office_office_number) REFERENCES office(office_office_number)
)
视图
CREATE VIEW list_woman
AS
SELECT *
FROM list
WHERE list_sex='女'
go
CREATE VIEWlist_man
AS
SELECT *
FROM list
WHERE list_sex='男'
go
4.2 装载数据
insert intolist
values('李勇','男','13407389999','19891203','湖南娄底')
go
insert intolist
values('刘晨','女','15807387777','19900123','湖南娄底')
go
insert intolist
values('王敏','女','15207383333','19901101','湖南娄底')
go
insert intolist
values('张立','男','13807388888','19910706','湖南娄底')
go
insert intofamily
values('07382456777','湖南娄底氐星路号')
go
insert intofamily
values('07312456777','湖南长沙人民路号')
go
insert intofamily
values('07412456777','湖南怀化解放路号')
go
insert intooffice
values('07381111111','湖南娄底','
[email protected]')
go
insert intooffice
values('07382222222','湖南娄底','
[email protected]')
go
insert intooffice
values('0103333333','北京东城区','
[email protected]')
go
insert intooffice
values('07554444444','广东深圳','
[email protected]')
go
insert intoListFamily
values('李勇','07382456777')
go
insert intoListFamily
values('刘晨','07312456777')
go
insert intoListFamily
values('王敏','07382456777')
go
insert intoListFamily
values('张立','07412456777')
go
insert intoListOffice
values('李勇','07381111111')
go
insert intoListOffice
values('刘晨','07382222222')
go
insert intoListOffice
values('王敏','0103333333')
go
insert intoListOffice
values('张立','07554444444')
go
查询：
select * from list
select * from family
select * from office
select * from ListFamily
select * from ListOffice
select * from list_woman
select list_sex from list
select list_mobile_number from list
select list_birthday from list
select list_address from list
索引：
create index list_index on list (list_mobile_number )
create index office_index on office(office_office_number )
触发器：
create triger list_triger on list for delete
as
declear @list_mobile_number int
select @list_mobile_number from list
if @list_mobile_number = '110'
begin
rollback
raiserror （'不可删除',16,1）
end
过程：
create procedure check_number
@num char(11)
as
select list_mobile_number from list where list_mobile_number = @num
go
本回答被网友采纳