商城表实例的分析

按照用户的购物流程进行分析

  • 用户表
  • 订单表
  • 商品表
  • 商品分类表
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
-- 用户表 (用户的ID,用户名,密码,手机)
create table user(
    uid int primary key auto_increment,
    name varchar(10),
    password varchar(20),
    mobile varchar(11)
  );

-- 订单表 (订单编号,总价,订单时间 外键-->用户的ID)
create table orders(
    oid int primary key auto_increment,
    total double,
    otime timestamp,
    uno int,
    foreign key(uno) references user(uid)
);

-- 商品分类表(分类ID,分类名称,分类描述)
create table category(
    cid int primary key auto_increment,
    cname varchar(20),
    cdesc varchar(50)
);

-- 商品表 (商品ID, 商品名称,商品价格,商品描述,外键cno)
create table product(
    pid int primary key auto_increment,
    pname varchar(10),
    price double,
    pdesc varchar(50),
    cno int,
    foreign key(cno) references category(cid)
);

--  订单项: 中间表(订单ID,商品ID,商品数量,订单项总价)
  create table orderitem(
    ono int,
    pno int,
    num int,
    subtotal double,
    foreign key(ono) references orders(oid),
    foreign key(pno) references product(pid)
  );

-- 准备数据: 假设张三用户注册
insert into user values(1,'zs','123','110');

-- 准备数据: 插入分类表的数据
insert into category values(1,'手机数码','黑马生产的小手机');
insert into category values(2,'鞋靴箱包','传智生产的包包');
insert into category values(3,'香烟酒水','黄鹤楼,茅台');
insert into category values(4,'馋嘴零食','卫龙辣条,周黑鸭');
insert into category values(5,'电脑办公','联想电脑外星人');

-- 准备数据 : 插入商品表的数据
insert into product values(1,'锤子',2999,'可以砸榴莲的手机',1);
insert into product values(2,'小米',1599,'为发烧而生',1);
insert into product values(3,'李宁',99,'不走寻常路',2);
insert into product values(4,'耐克',399,'just do it',2);
insert into product values(5,'黄鹤楼',20,'饭后一根烟',3);
insert into product values(6,'黄鹤楼',20,'饭后一根烟',3);
insert into product values(7,'卫龙辣条',5,'卫龙辣条加料不加价',4);
insert into product values(8,'老干妈辣椒酱',9,'永远不变的味道',4);
insert into product values(9,'老干娘辣椒酱',null,'永远不变的味道',4);
insert into product values(10,'热干面',10,'大武汉热干面',null);

-- 准备数据: 假设张三在我们的商城下两个订单
 -- zs买东西: 
 -- 1号订单:
    -- 锤子: 1  10  29990
    -- 李宁: 3  2 198
-- -- 向 订单表 插入1号订单信息(订单编号,订单总价,订单时间,用户编号)   
insert into orders values(1,30000,null,1);
-- -- 向 订单项表 插入1号订单包含的商品信息(订单编号,商品编号,商品数量,商品小计)
insert into orderitem values(1,1,10,29990);
insert into orderitem values(1,3,2,198);

 -- 2号订单:
    -- 卫龙辣条: 7  10 50
    -- 老干妈辣椒酱:8 2 18
    -- 李宁: 3  1 99 
-- -- 向 订单表 插入2号订单信息(订单编号,订单总价,订单时间,用户编号)       
insert into orders values(2,150,null,1);
-- -- 向 订单项表 插入2号订单所包含的商品信息(订单编号,商品编号,商品数量,商品小计)
insert into orderitem values(2,7,10,50);
insert into orderitem values(2,8,2,18);
insert into orderitem values(2,3,1,99);

商城表多表查询

多表查询

  • 交叉连接查询 笛卡尔积 : 两张表的乘积,查询的结果没有实际意义

  • 内连接查询:

隐式内连接: select * from product p,category c where p.cno = c.cid;

显式内连接: select * from product p inner join category c on p.cno = c.cid;

  • 外连接查询:

  • 左外连接: 以左表为基础,将左表中所有的记录都查询出来,如果没有对应的记录,用null值填充

    Select * from product p left outer join category c on p.cno = c.cid;

  • 右外连接:以右表为基础,将右表中的所有记录都查询出来,如果没有对应的记录,用null值填充

    select * from product p right outer join category c on p.cno= c.cid;

多表查询练习

查询出商品名称,商品分类名称信息

1
select p.pname,c.cname from product p left outer join category c on p.cno = c.cid;

查询分类名称为手机数码的所有商品信息

1
2
select * from product p left outer join category c on p.cno = c.cid;
select * from product p left outer join category c on p.cno = c.cid where c.cname='手机数码';

按照商品分类的名称统计商品的个数: 

1
2
3
4
 select c.cname,COUNT(*) from product p left outer join category c on p.cno = c.cid group by c.cname;

-- 按照商品分类的编号统计商品的个数
select cno,count(*) from product group by cno;

查询1号订单的订单项信息(orderitem)对应的商品信息(product)

1
select * from orderitem oi,product p where oi.pno = p.pid and oi.ono=1;