实验二 数据查询(2)

on

(name=cap_data,--数据文件的逻辑名称,注意不能与日志逻辑同名 filename='d:\\sql_data\\cap_data.mdf' ,--物理名称，注意路径必须存在 size=10,--数据初始长度为5M maxsize=50,--最大长度为10M

filegrowth=5%)--数据文件每次增长5% log on

( name=cap_log,

filename='d:\\sql_data\\cap_log.ldf ' , size=2 , maxsize=5 , filegrowth=1) go

use CAP go

create table customers (cid char(4) not null, cname varchar(13), city varchar(20), discnt real, primary key(cid));

create table agents (aid char(3) not null, aname varchar(13), city varchar(20), per smallint, primary key (aid));

create table products (pid char(3) not null, pname varchar(13), city varchar(20), quantity integer, price money, primary key(pid));

create table orders (ordno integer not null, month char(3), cid char(4), aid char(3), pid char(3),

qty integer, dollars money, primary key(ordno));

2、 脚本文件（import.sql）的内容

Insert into customers select * from

openrowset('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=e:\\cap.xls',[客户$]);

Insert into agents select * from

openrowset('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=e:\\cap.xls',[代理商$]);

insert into products select * from

openrowset('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=e:\\cap.xls',[产品$]);

insert into orders select * from

openrowset('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=e:\\cap.xls',[订单$]);