mysql 高级语句 order by desc limit group by

create table Ptype(
id int primary key auto_increment not null,
typ_name varchar(20) not null)
engine=InnoDB default charset=utf8;


create table product (
pro_id int primary key auto_increment,
pro_name varchar(20) not null,
protype_id int,
price int not null,
pinpai varchar(10) not null,
chandi varchar(10) not null,
foreign key (protype_id) references ptype(id)
)ENGINE=InnoDB;


insert into ptype (typ_name) value ('电视');
insert into ptype (typ_name) value ('手机');
insert into ptype (typ_name) value ('笔记本');


INSERT INTO `product`(`pro_id`, `pro_name`, `protype_id`, `price`, `pinpai`, `chandi`) VALUES (2, '康佳高清液晶电视', 1, 1999, '康佳', '深圳');
INSERT INTO `product`(`pro_id`, `pro_name`, `protype_id`, `price`, `pinpai`, `chandi`) VALUES (3, '索尼手机', 2, 3238, '索尼', '深圳');
INSERT INTO `product`(`pro_id`, `pro_name`, `protype_id`, `price`, `pinpai`, `chandi`) VALUES (4, '海信只能电视', 1, 4199, '海信', '青岛');
INSERT INTO `product`(`pro_id`, `pro_name`, `protype_id`, `price`, `pinpai`, `chandi`) VALUES (5, '联想笔记本电脑', 3, 5499, '联想', '背景');
INSERT INTO `product`(`pro_id`, `pro_name`, `protype_id`, `price`, `pinpai`, `chandi`) VALUES (6, '索尼超薄笔记本', 3, 11499, '索尼', '天津');
INSERT INTO `product`(`pro_id`, `pro_name`, `protype_id`, `price`, `pinpai`, `chandi`) VALUES (7, '索尼高清电视', 1, 6999, '索尼', '背景');
INSERT INTO `product`(`pro_id`, `pro_name`, `protype_id`, `price`, `pinpai`, `chandi`) VALUES (8, '联想笔记本', 3, 2999, '联想', '北京');
INSERT INTO `product`(`pro_id`, `pro_name`, `protype_id`, `price`, `pinpai`, `chandi`) VALUES (9, '联想双待手机', 2, 988, '联想', '北京');
INSERT INTO `product`(`pro_id`, `pro_name`, `protype_id`, `price`, `pinpai`, `chandi`) VALUES (10, '惠普打印机', 3, 1169, '惠普', '天津');


#最贵
select * from product order by price desc limit 1;
# 最大id
select pro_id as zuidaid from product order by pro_id desc limit 1;
# 最便宜
select price as zuipianyi from product order by pro_id desc limit 1;
# 最小id
select pro_id as zuidaid from product order by pro_id asc limit 1;
#查出所有商品的总数量。
select count(*) from product 
#查出所有商品的平均价格。
select avg(price) from product 
#查出联想品牌的所有商品的平均价格。
select avg(price) as lenove from product group by pinpai having  pinpai='联想'


#按价格由高到低排序。
select * from product order by price desc
#按商品类型由低到高排序，类型内部按价格由高到低排序。
select * from product order by protype_id,price desc
#取出价格最高的前三个商品。
select * from product order by price desc limit 0,3
#查出每个产地各有多少数量的商品
select chandi,count(*) from product GROUP BY  chandi
#查出每个品种各有多少个商品

select protype_id,count(*) from  product group by protype_id;

内外链接

select * from product as p left join ptype as t on p.protype_id=t.id
select p.pro_name,t.typ_name from product as p left join ptype as t on p.protype_id=t.id
select t.typ_name,count(*) from ptype as t left join product as p on p.protype_id=t.id  GROUP BY t.typ_name
select p.pro_name,t.typ_name from product as p left join ptype as t on p.protype_id=t.id