MySQL多表查询
建表
CREATE DATABASE IF NOT EXISTS stroe CHARACTER SET utf8;
USE stroe;
CREATE TABLE goods( good_no INT AUTO_INCREMENT PRIMARY KEY COMMENT "商品编号" , goods_name VARCHAR(50) COMMENT "商品名称", cost DOUBLE COMMENT "商品成本", price DOUBLE COMMENT "商品售价", count
INT COMMENT "商品库存", create_time DATETIME COMMENT "商品上架时间", category_no INT COMMENT "商品分类编号" )ENGINE=INNODB DEFAULT CHARSET=utf8
CREATE TABLE category( no
INT AUTO_INCREMENT PRIMARY KEY COMMENT "商品分类编号", name
VARCHAR(50) COMMENT "商品类别名称" )ENGINE INNODB DEFAULT CHARSET=utf8
CREATE TABLE account( id INT AUTO_INCREMENT PRIMARY KEY COMMENT "用户编号", phone VARCHAR(11) COMMENT "用户手机号", password
VARCHAR(50) COMMENT "用户密码", type
BIT COMMENT "用户类型 0 管理员 1 会员", name
VARCHAR(50) COMMENT "用户名", point
INT COMMENT "用户积分", money
DOUBLE COMMENT "用户余额", create_time DATETIME COMMENT "用户创建时间" )ENGINE INNODB DEFAULT CHARSET=utf8
CREATE TABLE cart( id INT AUTO_INCREMENT PRIMARY KEY COMMENT "购物车编号", goods_no INT COMMENT "商品编号", num INT COMMENT "商品数量", account_id INT COMMENT "用户编号", create_time DATETIME COMMENT "加入购物车时间" )ENGINE INNODB DEFAULT CHARSET=utf8
INSERT INTO goods(goods_name,cost,price,count
,create_time,category_no) VALUES('火腿肠',0.5 ,2.5,50,'2023/3/13 19:50',1); INSERT INTO goods(goods_name,cost,price,count
,create_time,category_no) VALUES('薯片',1.5 ,3.5,30,'2023/3/10 19:50',1); INSERT INTO goods(goods_name,cost,price,count
,create_time,category_no) VALUES('鼠标垫',20.5 ,25.5,20,'2023/3/7 19:50',4); INSERT INTO goods(goods_name,cost,price,count
,create_time,category_no) VALUES('萝卜',0.8 ,1.5,50,'2023/3/8 19:50',2); INSERT INTO goods(goods_name,cost,price,count
,create_time,category_no) VALUES('白菜 ',0.2 ,0.8,50,'2023/3/9 19:50',2); INSERT INTO goods(goods_name,cost,price,count
,create_time,category_no) VALUES('车厘子',30 ,50,50,'2023/3/10 19:50',2); INSERT INTO goods(goods_name,cost,price,count
,create_time,category_no) VALUES('芒果',5 ,8,50,'2023/3/11 19:50',3); INSERT INTO goods(goods_name,cost,price,count
,create_time,category_no) VALUES('五粮液',1350 ,1700,60,'2023/3/12 19:50',5); INSERT INTO goods(goods_name,cost,price,count
,create_time,category_no) VALUES('鼠标',40 ,50,20,'2023/3/13 19:50',4);
INSERT INTO category(name
) VALUES('零食'); INSERT INTO category(name
) VALUES('蔬菜'); INSERT INTO category(name
) VALUES('水果'); INSERT INTO category(name
) VALUES('电子产品'); INSERT INTO category(name
) VALUES('酒水');
INSERT INTO account(phone,PASSWORD,TYPE,NAME,POINT,money,create_time) VALUES('111','111',0,'管理员',NULL,NULL,'2023/3/7 16:50'); INSERT INTO account(phone,PASSWORD,TYPE,NAME,POINT,money,create_time) VALUES('222','222',1,'张三',0,350,'2023/3/11 19:50'); INSERT INTO account(phone,PASSWORD,TYPE,NAME,POINT,money,create_time) VALUES('333','333',1,'李四',0,268.5,'2023/3/12 19:50'); INSERT INTO account(phone,PASSWORD,TYPE,NAME,POINT,money,create_time) VALUES('444','444',1,'王五',0,956.3,'2023/3/13 19:50'); INSERT INTO account(phone,PASSWORD,TYPE,NAME,POINT,money,create_time) VALUES('555','555',1,'孙六',2000,9,'2023/3/7 17:50');
INSERT INTO cart(goods_no,num,account_id,create_time) VALUES(1,2,2,'2023/3/11 19:50'); INSERT INTO cart(goods_no,num,account_id,create_time) VALUES(2,2,3,'2023/3/12 19:50'); INSERT INTO cart(goods_no,num,account_id,create_time) VALUES(4,10,4,'2023/3/13 19:50'); INSERT INTO cart(goods_no,num,account_id,create_time) VALUES(6,20,3,'2023/3/12 19:50'); INSERT INTO cart(goods_no,num,account_id,create_time) VALUES(9,1,2,'2023/3/12 19:50'); INSERT INTO cart(goods_no,num,account_id,create_time) VALUES(2,10,4,'2023/3/13 19:50');
题目
#查询商品库存等于50的所有商品,显示商品编号,商品名称,商品售价,商品库存。
SELECT good_no,goods_name,price,count
FROM goods WHERE count
=50
#查询商品成本等于60的所有商品,显示商品编号,商品名称,商品售价,商品库存。
SELECT good_no,goods_name,price,count
FROM goods WHERE cost=60
#修改购物车中id为5的num数量加一
UPDATE cart SET num = num+1 WHERE id=5
#会员表添加一列字段为 memo,类型为varchar(500)
ALTER TABLE account ADD COLUMN memo VARCHAR(500)
#修改购物车表的id字段为cart_id
ALTER TABLE cart CHANGE id cart_id INT
#查询购物车数量等于10的信息,显示购物车编号,商品编号,商品数量,创建时间,账户编号
SELECT cart_id,goods_no,num,create_time,account_id FROM cart WHERE num=10
#删除用户表中账户余额等于9的用户
DELETE FROM account WHERE money=9
#查询购物车中用户id为3的所有信息
SELECT * FROM cart WHERE id=3
#查询goods表中第二页的数据,每页显示5条数据
SELECT * FROM goods LIMIT 5,5
#商品价格一样按照成本降序排序,并显示第三页,每页显示3条数据。
SELECT * FROM goods ORDER BY price ASC, cost DESC LIMIT 6,3
#思考:如何查询用户购物车的单个商品总价
SELECT goods_no AS 商品编号,num AS 商品数量,price AS 商品单价,num*price AS 总价格 FROM cart,goods WHERE goods.good_no = cart.goods_no
#================================== 扩展查询 =================================
1:火腿肠被谁买了?
select a.name from goods g,account a,cart c where g.goods_no = c.goods_no and c.account_id = a.id and g.goods_name = '火腿肠';
2:零食被谁买了?
select a.name from goods g,cart c, account a,category y where g.goods_no = c.goods_no and c.account_id = a.id and g.category_no = y.no and y.name = '零食';
3:张三买了哪些商品?
select g.goods_name from goods g,cart c, account a,category y where g.goods_no = c.goods_no and c.account_id = a.id and g.category_no = y.no and a.name = '张三';
4:张三花了多少钱?
select a.name,sum(g.price * c.num) 商品总价 from goods g,cart c, account a where g.goods_no = c.goods_no and c.account_id = a.id and a.name = '张三';
5:所有用户分别买了多少钱?
select a.name,sum(g.price * c.num) 商品总价 from goods g,cart c, account a where g.goods_no = c.goods_no and c.account_id = a.id group by a.name;
6:加上购物车东西全部销售,周几的营业额最高
select sum(cart.num * goods.price) 营业额,DATE_FORMAT(cart.create_time, '%W') week from goods,cart where goods.goods_no = cart.goods_no group by cart.create_time order by 营业额 desc limit 0,1;
7:张三在什么时候购物
select account.name,account.create_time from cart,account where cart.account_id = account.id and account.name = '张三';
8:购物车里的商品销售后,赚了多少钱
select sum(cart.num * goods.price - cart.num * goods.cost) 总利润 from goods,cart where goods.goods_no = cart.goods_no;
9:求哪个商品利润率最高
s
elect goods_name,format((goods.price - goods.cost) / goods.cost,2) 利润率 from goods,cart where goods.goods_no = cart.goods_no order by 利润率 desc limit 1
;
10: 求2023年3月12日前一周上架的商品
select a.goods_name from goods a,cart b where a.goods_no = b.goods_no and b.create_time > date_sub('2023-03-12',interval 1 week) and b.create_time < '2023-03-12'
;
11:求用户还有多少余额?
select c.name,(c.money - (b.num * a.price)) 余额 from goods a,cart b,account c where a.goods_no = b.goods_no and b.account_id = c.id;
12:还剩多少库存?
select g.goods_name,sum(g.count - c.num) from goods g,cart c where c.goods_no = g.goods_no group by g.goods_name;