MySQL将表中的价格全部加五_MySQL总结练习---如何查找每个分类中最贵的商品、如何将查询的数据插入到某个表中...

sql语句练习

一、如何查找每个分类中最贵的商品

1、创建数据库，插入数据

creare database jingdong charset=utf8;

use jingdong;

create table goods(

id int unsigned primary key auto_increment not null,

name varchar(150) not null,

cate_name varchar(40) not null,

brand_name varchar(40) not null,

price decimal(10.3) not null default 0,

is_show bit not null default 1,

is_saleoff bit not null default 0);

--插入数据

insert into goods values(0,'r510vc 15.6英寸笔记本','笔记本','华硕','3399',default,default);

2、-- 求所有商品的平均价格,并且保留两位小数

select round(avg(price),2) from goods;

– 查询所有价格大于平均价格的商品，并且按价格降序排序

select name from goods

where price > (select avg(price) from goods)

order by price desc;

– 查询类型cate_name为 ‘超级本’ 的商品名称、价格

select name,price from goods where cate_name = "超级本";

– 显示商品的种类

select cate_name from goods group by cate_name;

– 显示每种类型的商品的平均价格

select cate_name,avg(price) from goods group by cate_name;

3、-- 查询每种类型的商品中 最贵、最便宜、平均价、数量

select cate_name,max(price),min(price),avg(price),count(*) from goods

group by cate_name;

4、查询每种类型中最贵的商品信息

1)方法一，表级子查询，内关联方式

拆解一下，首先通过分组，划分每种类型。但是没有办法通过单纯的聚合max来得到多个值，故思考使用关联2个表的方法：

查询得到一个有类型信息和每个类型中最贵的价格。即上题得到的表。

通过关联总表所有商品的价格与上表最贵价格，内连接取交集，即得到每种类型中最贵的商品信息

select * from goods inner join

(select cate_name,max(price) as 最贵,min(price),avg(price),count(*) from goods

group by cate_name) as info

on goods.cate_name=info.cate_name and goods.price=info.最贵;

简化一下结果，把子表去掉

select goods.* from goods inner join

(select cate_name,max(price) as 最贵,min(price),avg(price),count(*) from goods

group by cate_name) as info

on goods.cate_name=info.cate_name and goods.price=info.最贵;

2)方法二：行级子查询，判断多列的值，注意使用in或=any，不能单独使用=号，=号代表了一行对应多行

select * from goods where cate_name,price in

(select cate_name,max(price) from goods group by cate_name);

select * from goods where (cate_name,price) =any (select cate_name,max(price) from goods group by cate_name);

二、如何将查询的数据插入到某个表中

– insert into xx表 values (), (),…; 需要手动写比较麻烦

– insert … select # 将查询的结果插入到某一个表中，注意新表后要加括号说明字段值，否则报错

1、创建新表

create table if not exists goods_cates(

id int unsigned primary key auto_increment,

name varchar(40) not null

);

2、将goods表中的商品类别插入到goods_cates表的name字段，注意括号

insert into goods_cates(name)

select distinct cate_name from goods;

3、表连接更新，update…set

将主表中数据依据从表数据更新

将goods表中的商品类别更新为goods_cates表中类别对应的id

update goods as g inner join

goods_cates as c on g.cate_name = c.name

set g.cate_name = c.id;

– 更新字段名称和数据类型 字符串类型 — > int 类型

alter table goods change cate_name cate_id int unsigned not null;