MySQL 多表查询--练习题

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;