1. 创建并合理设计表：至少满足如下要求 商品： 商品名称 、商品描述、商品价格、商品数量、商品类别等 商品类别： 类别名称 、多级分类，类别排序 商品购买表： 购买的商品、数量

习题整理总结
高级查询
1、 创建并合理设计表：至少满足如下要求
商品： 商品名称 、商品描述、商品价格、商品数量、商品类别等
商品类别： 类别名称 、多级分类，类别排序
商品购买表： 购买的商品、数量 、价格 等

#创建商品类别
create table category(
id int UNSIGNED auto_increment key ,
  name varchar(20),
  pid int UNSIGNED DEFAULT 0,
  sorts TINYINT UNSIGNED
);

#创建商品表 
create table goods(
id int UNSIGNED auto_increment key ,
  name varchar(20),
  content text,
  price decimal(10,2),
  stock MEDIUMINT DEFAULT 0,
  cid int unsigned,
  categoryPath varchar(200),
  create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  update_time TIMESTAMP on update CURRENT_TIMESTAMP
);

#商品购买表
create table goods_sale(
id int UNSIGNED auto_increment key ,
gid int UNSIGNED,
name varchar(20),
num MEDIUMINT DEFAULT 0,
price decimal(10,2),
create_time TIMESTAMP DEFAULT  CURRENT_TIMESTAMP   
);

运行SQL语句成功建立三张表。

2、写一个存储过程，实现商品类别数据插入至少4条

create PROCEDURE p_initcategory()
begin
  insert into category(id,name,pid,sorts)
	values(1,'电器',0,1),(2,'电脑',0,2),(null,'电视',1,1),(null,'空调',1,2),(null,'笔记本',2,1);
end;

#调用
call p_initcategory();

插入数据成功

数据显示如下：

3、写一个存储过程，插入商品数据n条

create PROCEDURE p_initgoods()
begin
  insert into goods(name,content,price,stock,categoryPath)
	values('小米电视','',2999,100,'1/3/'),('格力空调','',6999,50,'1/4/'),('创维电视','',2100,10,'1/3/');
end;

#调用
call p_initgoods();

运行成功可显示下图进行查看


4、关联查询显示商品名称、价格、数量、类别

select g.`name`,g.price,g.stock,c.`name` categoryname  from goods g JOIN category c on g.cid=c.id

注意标点符号的使用，可以直接把sql代码复制过去。

5、根据类别查询出对应商品数据

select * from goods where instr(categoryPath,'3/')

6、写一个触发器添加商品购买记录时，自动更新商品数量

7、写一个函数，根据类别id返回类别名称

create FUNCTION f_queryCategoryName(_id int UNSIGNED)
returns varchar(20)
begin 
  DECLARE _name varchar(20);
	select name into _name from category where id=_id;
	return _name;
end;

select f_queryCategoryName(1);

8、统计商品销售情况，显示商品名称、销售数量,取排名前三

select s.`name`,sum(s.num) num from goods_sale s group by name order by num desc limit 3

9、查询当天的购买记录

select * from goods_buy where date_format(create_time,'%Y-%m-%d')=curdate();

10、查询最近3天的购买记录

select * from goods_buy where DATE_FORMAT(create_time,'%Y-%m-%d')>=DATE_FORMAT(DATE_SUB(now(),interval 3 day),'%Y-%m-%d')