一 建立PetStore数据库

1.创建PetStore数据库

CREATE DATABASE petstore DEFAULT CHARACTER SET gb2312 COLLATE gb2312_chinese_ci;

2.创建用户表account

USE Petstore;

CREATE TABLE account(

userid char(6) NOT NULL,

fullname varchar(10) NOT NULL,

password varchar(20) NOT NULL,

sex char(2) NOT NULL,

address varchar(40) NULL,

email varchar(20) NULL,

phone varchar(11) NOT NULL,

PRIMARY KEY(userid)

);

3.创建商品分类表category

CREATE TABLE category(

catid char(10) NOT NULL,

catname varchar(20)NULL,

cades text NULL,

PRIMARY KEY(catid)

);

4.创建商品表product

CREATE TABLE product(

productid char(10) NOT NULL,

catid char(10) NOT NULL,

name varchar(30) NULL,

descn text NULL,

listprice decimal(10,2) NULL,

unitcost decimal(10,2) NULL,

qty int(11) NOT NULL,

PRIMARY KEY (productid)) ;

5.创建订单表olders

CREATE TABLE orders (

orderid int (11) NOT NULL AUTO_INCREMENT,

userid char (6) NOT NULL,

orderdate datetime NOT NULL,

totalprice decimal(10, 2) DEFAULT NULL,

status tinyint(1) DEFAULT NULL,

PRIMARY KEY(orderid));

6.创建订单明细表lineitem

CREATE TABLE lineitem (
        orderid int(11) NOT NULL,
        itemid char(10) NOT NULL,
        qualtity int(11) NOT NULL,
        unitprice decimal(10,2) NOT NULL,
        PrimARY KEY(orderid,itemid)
        );

二  PetStore 数据输入

1.用户表account

为用户表account插入记录的SQL 代码如下:

INSERT INTO account VALUES('u0001','刘晓和','123456','男','广东深圳市','liuxh@163.com','13512345678');

INSERT INTO account VALUES('u0002','张嘉庆','123456','男','广东深圳市','zhangjq@163.com','13512345679');

INSERT INTO account VALUES('u0003','罗红红','123456','女','广东深圳市','longhh@163.com','13512345689');

INSERT INTO account VALUES('u0004','李昊华','123456','女','广东广州市','lihh@163.com','13812345679');

INSERT INTO account VALUES('u0005','吴美霞','123456','女','广东珠海市','wumx@163.com','13512345879');

INSERT INTO account VALUES('u0006','王天赐','123456','男','广东中山市','wangtc@163.com','13802345679');

2.商品分类表category

为商品分类表category 记录的SQL 代码如下:

INSERT INTO category VALUES('01','鸟类','');

        INSERT INTO category VALUES('02','猫','');

        INSERT INTO category VALUES('03','狗','');

        INSERT INTO category VALUES('04','鱼','');

        INSERT INTO category VALUES('05','爬行类','');

3.商品表product

为商品表product 插入记录的SQL代码如下:

INSERT INTO product VALUES('AV-CB-01','05','亚马逊鹦鹉','75 岁以上高龄的好伙伴',50.00,60.00,100);

INSERT INTO product VALUES('AV-SB-02','05','燕雀','非常好的减压宠物',45.00,50.00,98);

INSERT INTO product VALUES('FI-FW-O1','O1','锦鲤','来自日本的淡水鱼',45.50,45.50,300);

INSERT INTO product VALUES('FI-FW-02','O1','金鱼','来自中国的淡水鱼',6.80,6.80,100);

INSERT INTO product VALUES('FI-SW-O1','01','天使鱼','来自澳大利亚的海水鱼',10.00,10.00,100);

INSERT INTO product VALUES('FI-SW-02','O1','虎鲨','来自澳大利亚的海水鱼',18.50,20.00,200);

INSERT INTO product VALUES('FL-DLH-02','04','波斯猫','友好的家居猫,像公主一样高贵',1000.00,1200.00,15);

INSERT INTO product VALUES('FL-DSH-01','04','马恩岛猫','灭鼠能手',80.00,100.00,40);

INSERT INTO product VALUES('K9-BD-01','02','牛头犬','来自英格兰的友好的狗',1350.00,1500.00,5);

INSERT INTO product VALUES('K9-CW-01','02','吉娃娃犬','很好的陪伴狗',180.00,200.00,120);

INSERT INTO product VALUES('K9-DL-01','02','斑点狗','来自消防队的大狗',3000.00,3000.00,1);

INSERT INTO product VALUES('K9-PO-02','02','狮子犬,'来自法国的可爱的狗',2000.00,2000.00,3);

INSERT INTO product VALUES('K9-RT-01','02','金毛猎犬','大家庭的狗',300.00,300.00.200):

INSERT INTO product VALUES('K9-RT-02','02','拉布拉多猎大','大猎狗',800.00,800.00,30):

INSERT INTO product VALUES('RP-LI-02','03','策蜥','友好的绿色朋友',60.00,78.00,40):

INSERT INTO product VALUES('RP-SN-01','03','呵尾蛇','兼当看门狗',200.00,240.00,10):

4.订单表orders

为订单表orders 插入记录的 SQL代码如下:

INSERT INTO orders VALUES(20130411,'u0001','2013-04-11 15:07:34',500.00,0);

        INSERT INTO orders VALUES(20130412,'u0002','2013-04-09 15:08:11',305.60,0);

        INSERT INTO orders VALUES(20130413,'u0003','2013-04-15 15:09:00',212.40,0);

        INSERT INTO orders VALUES(20130414,'u0004','2013-04-16 15:09:30',120.4,1);

        INSERT INTO orders VALUES(20130415,'u0005','2013-04-02 15:10:05',120.30,0);

5.订单明细表lineitem

小单明细表lineitem插入数据的SQL代码如下:

INSERT INTO lineitem VALUES(20130411,'FI-SW-01',10,18.50);

        INSERT INTO lineitem VALUES(20130411,'EL-SW-O2',12,16.50);

        INSERT INTO lineitem VALUES(20130412,'KO-RD-O1',2,120.00);

        INSERT INTO lineitem VALUES(20130412,'K9-PO-02',1,220.00);

        INSERT INTO lineitem VALUES(20130413,'K9-DL-01',1,130.00);

        INSERT INTO lineitem VALUES(20130414,'RP-SN-01',2,125.00);

        INSERT INTO lineitem VALUES(20130415,'AV-SB-02',2,50.00);

三 PetStore数据查询

1 SELECT语句的基本使用

1）查询account表中客户的姓名(fullname)、地址(address)和电话(Phone)，显示的列要求显示 姓名、地址、电话。

2）查询lineitem表中商品编号(itemid)和单价(unitprice)，要求消除重复行。

3）计算lineitem表中每条记录的商品金额。

4）査询account表中客户的姓名(fu11name)和性别(sex)，要求性别为。"男"时显示为1, "女"时显示为0.

5) 查询product表中商品名(name)和档次。档次按单价(unitcost)划分，1000元以下显示为低价商品，1000元到2000元之间为"中档商品"，2 000元以上时显示为"高档商品"。

2.条件查询

1）显示orders表单笔高于200元的客户号(userid)、成交金额(totalprice)和订单状态(status)

2）査询olders表中2013年4月份的所有订单。

3）查询account表中女客户的姓名(fullname)、地址(address)和电话(Phone)，显示的列标题要求用中文姓名、地址和电话表示

4）查询account表中姓吴的客户信息

5）査询olders表成交总额200元～500元的订单信息。

6）查询product表中商品编号(productid)倒数第4个标号为W的商品信息。

3.多表查询

1）查询lineitem表中订单编号、商品名称和购买数量。

2）显示orders表单笔高于300元的客户名和订单总价。

3）查询"刘晓和"的基本情况和订单情况。

4)统计2013年5月前订购了商品的女客户姓名和订购总额。

5）査找购买了商品编号为FI-sw-o2的订单号、客户号和订购日期。

6）查找product表中价格不低于"波斯猫"商品的信息。

4.分类汇总与排序

1）统计客户总数。

2）计算orders表每单平均价。

3）计算orders表成交总额。

4）显示oFders表单笔最高成交额和最低成交总额。

5）按性别统计客户人数。

6）按商品类别统计各类商品总数、平均单价。

7）将客户信息按电话号码从大到小排序

8）将olders表按客户号从小到大排序，客户号相同的按订购日期从大到小排序。

    9）显示lineitem表中商品的购买总数量超过2件的商品编号和购买总数量，并按购买数量从小到大排序