USE master

 

 

 

--删除已经存在的eshop数据库

 

IF EXISTS ( SELECT * FROM dbo.sysdatabases WHERE name = 'eshop' )

 

       DROP DATABASE eshop

 

 

 

--创建数据库eshop

 

CREATE DATABASE eshop

 

 

 

GO

 

 

 

USE eshop

 

 

 

--删除已经存在的members数据表

 

IF EXISTS ( SELECT * FROM dbo.sysobjects WHERE name = 'members' AND xtype = 'U' )

 

       DROP TABLE members

 

 

 

--创建数据表members

 

CREATE TABLE members

 

(

 

       m_account            VARCHAR(20),                                  --金融帐号

 

       m_name                      VARCHAR(20),                                  --客户姓名

 

       m_sex                  CHAR(2),                                          --性别

 

       m_birth                DATETIME,                                      --出生年月日

 

       m_address            VARCHAR(50),                                  --家庭住址

 

       m_salary               MONEY,                                           --月薪

 

       m_password         VARCHAR(20)                                   --密码

 

)

 

 

 

GO

 

 

 

--删除已经存在的products数据表

 

IF EXISTS ( SELECT * FROM dbo.sysobjects WHERE name = 'products' AND xtype = 'U' )

 

       DROP TABLE products

 

 

 

--创建数据表products

 

CREATE TABLE products

 

(

 

       p_no                    CHAR(10),                                        --商品编号

 

       p_name                VARCHAR(30),                                  --商品名称

 

       p_date                  DATETIME,                                      --生产日期

 

       p_quantity             INT,                                                        --数量

 

       p_price                 MONEY,                                           --单价

 

       p_information        VARCHAR(50)                                   --商品详细描述信息

 

)

 

 

 

GO

 

 

 

--删除已经存在的orders数据表

 

IF EXISTS ( SELECT * FROM dbo.sysobjects WHERE name = 'orders' AND xtype = 'U' )

 

       DROP TABLE orders

 

 

 

--创建数据表orders

 

CREATE TABLE orders

 

(

 

       m_account            VARCHAR(20),                                  --金融帐号

 

       p_no                    CHAR(10),                                        --商品编号

 

       o_quantity             INT,                                                        --订购数量

 

       o_date                  DATETIME,                                      --订购日期

 

       o_confirm_state    CHAR(1),                                          --购买确认状态

 

       o_pay_state           CHAR(1),                                          --支付状态

 

       o_send_state        CHAR(1)                                           --发送状态

 

)

 

 

 

GO

 

 

 

--插入数据到members数据表中

 

INSERT INTO members VALUES('Jinjin', '津津有味', '女', '1982-04-14', '北京市', 8200.0, 'jinjin')

 

INSERT INTO members VALUES('Lfz', '刘法治', '男', '1976-08-26', '天津市', 4500.0, 'lfz0826')

 

INSERT INTO members VALUES('liuzc518', '刘志成', '男', '1972-05-18', '湖南株洲', 3500.0, 'liuzc518')

 

INSERT INTO members VALUES('Wangym', '王咏梅', '女', '1974-08-06', '湖南长沙', 4000.0, 'wangym0806')

 

INSERT INTO members VALUES('Zhangzl', '张自梁', '男', '1975-04-20', '湖南株洲', 4300.0, 'zhangzl')

 

INSERT INTO members VALUES('zhao888', '赵爱云', '男', '1972-02-12', '湖南株洲', 5500.0, 'zhao888')

 

 

 

--插入数据到products数据表中

 

INSERT INTO products VALUES('0130810324', '清华同方电脑', '2005-12-11', 7, 8000.0, '优惠多多')

 

INSERT INTO products VALUES('0140810330', '洗衣粉', '2005-05-31', 1000, 8.6, '特价销售')

 

INSERT INTO products VALUES('0140810332', '红彤彤腊肉', '2005-05-20', 43, 15.0, '是一种卫生食品')

 

INSERT INTO products VALUES('0140810333', '力士牌香皂', '2005-05-06', 22, 6.0, '是一种清洁用品')

 

INSERT INTO products VALUES('0240810330', '电动自行车', '2005-05-31', 10, 1586.0, '价廉物美')

 

INSERT INTO products VALUES('0240810333', '自行车', '2005-05-31', 10, 586.0, '价廉物美')

 

INSERT INTO products VALUES('0910810001', '爱国者MP3', '2005-05-31', 100, 450.0, '价廉物美')

 

INSERT INTO products VALUES('0910810002', '商务通', '2005-05-20', 10, 850.0, '价廉物美')

 

INSERT INTO products VALUES('0910810003', '名人好记星', '2005-05-31', 100, 550.0, '价廉物美')

 

INSERT INTO products VALUES('0910810004', '奥美嘉 U盘', '2005-05-31', 100, 350.0, '价廉物美')

 

 

 

--插入数据到orders数据表中

 

INSERT INTO orders VALUES('jinjin', '0910810004', 2, '2005-06-06', '1', '0', '0')

 

INSERT INTO orders VALUES('jinjin', '0910810004', 1, '2005-08-09', '1', '1', '1')

 

INSERT INTO orders VALUES('lfz', '0910810001', 1, '2005-08-09', '0', '0', '0')

 

INSERT INTO orders VALUES('lfz', '0910810004', 2, '2005-06-06', '1', '1', '1')

 

INSERT INTO orders VALUES('lfz', '0910810004', 2, '2005-08-09', '1', '1', '1')

 

INSERT INTO orders VALUES('liuzc518', '0140810324', 1, '2005-10-09', '0', '0', '0')

 

INSERT INTO orders VALUES('liuzc518', '0910810001', 1, '2005-10-09', '1', '1', '0')

 

INSERT INTO orders VALUES('liuzc518', '0910810004', 2, '2005-10-09', '1', '1', '0')

 

INSERT INTO orders VALUES('wangym', '0910810001', 1, '2005-08-09', '1', '0', '0')

 

INSERT INTO orders VALUES('zhao888', '0240810333', 2, '2005-06-06', '1', '1', '0')