04 MySQL - 基础篇 -2

/*
创建一张商品表goods, 
id 			编号 		int 主键 ，自增
type 		种类		int
name 		名称 		varchar
price		价格 		real
clickCount 点击量 	int
goodsNumber 商品库存 int
*/
create table goods(
	id int not null auto_increment,
	type  int,
	name varchar(20),
	price double,
	clickCount int,
	goodsNumber int,
	primary key(id)
);

--1. 基本操作
--1.1 查出主键为20的商品
select * from goods where id = 12;

--1.2 查出不属于第三类的所有商品
select * from goods where type != 4;

--1.3 查询高于3000元的商品
select * from goods where price > 3000;

--1.4 查询低于或等于10元的商品
select * from goods where price <= 10;

--1.5 取出第4类和第9类的商品（in） 列表筛选
select * from goods where type in(4,9);

--1.6 取出不在第3类和第7类的商品(not in)
select * from goods where type not in(3,7);

--1.7 取出 100 <= 价格 <= 500 的商品(between)
select * from goods where price >= 100 and price <= 500;
select * from goods where price between 100 and 500;

--1.8 取出价格大于100且小于300，或者大于3000且小于5000的商品
select * from goods where (price>100 and price<300) or (price>3000 and price<5000);

--1.9 显示出所有商品及总价格（SQL乘法）
select name,goodsNumber * price from goods;

-- like 模糊查询
--1.10 查出以"诺基亚"开头的商品； "%"通配任意字符
select * from goods where name like "诺基亚%";

--1.11 取出以"诺基亚52"开头，且后面有两个字符的商品；  "_"通配单一字符
select * from goods where name like "诺基亚52__";

--1.12 取出价格在1000到3000之间，并且点击量 > 5 "诺基亚"开头的系列商品
select * from goods 
where price >= 1000 and price <= 3000 and clickCount > 5 and name like "诺基亚%";

--group by 分组查询:对整列内容进行筛选
--以某一列作为判断的条件
--函数
--2.1查出最贵的商品 max(字段名) 求哪一列的最大值
select name,max(price) from goods;
select * from goods order by price desc limit 1;

--2.2查出最便宜的商品价格
select min(price) from goods;

--2.3查询总库存量 sum求和函数
select sum(goodsNumber) from goods;

--2.4查看所有商品的平均价格 avg  求平均值
--round(avg(price),2) 保留小数点后2位
select round(avg(price),2) from goods;

--2.5统计共有多少个商品 count() 求行数
select count(id) from goods;
select count(*) from goods;
select count(name) from goods;

--count是统计一列中有效字段的行数
--2.6 查询第4类下商品总数
select sum(goodsNumber) from goods where type = 4;

--2.7 查询每个类别下的商品总量
--group by 有多少个类别，就有多少行
-- 对一列数据中相同的数字段进行分组 统计
select type,sum(goodsNumber) from goods group by type;

--2.8 查询每个类别下商品的平均价格
select type,avg(price) from goods group by type;

--2.9 查询每个商品所积压的货款
select goodsNumber * price from goods ;

--2.10 查询积压的总货款
select sum(goodsNumber * price) from goods;

--2.11 查询每个类别下积压的货款
select type,sum(goodsNumber * price) from goods group by type;

--3.1 查询某个类别下积压的货款大于20000的记录
select sum(price*goodsNumber) from goods 
group by type having sum(price*goodsNumber)>20000;
--聚合函数不能和where 一起使用 只能用having

--4.1 取出第四类的商品，并按价格由高到低（降序）
select * from goods where type = 4 order by price desc;

--4.2 按类别升序排列，同一类别下的商品按价格降序排列（升序+降序）
select * from goods order by type,price desc;

--limt
--5.1 查询价格最高的前三件商品
select * from goods order by price desc limit 0,3;

--5.2 从id升序排序结果中的第3件商品开始取，取出5件商品（limit范围）
--5就是步长
select * from goods order by id limite 3,5;

--5.3 取出价格最高的前第3至第5件商品
select * from goods order by price desc limit 2,3;