(12A)select 五种子句:where、group by (结合统计函数使用)、having

下面测试用到的表

这里写图片描述
一、where:在行角度判断条件是否成立(重要思想)

这里写图片描述

①常规查询
in(值1,值2……值N) :等于值1-N的任意一个都可以
between 值1 and 值2 : 表示在值1,值2之间(包含边界)
逻辑运算符用法举例:
and : 逻辑表达式1 and 逻辑表达式2

//差价大于等于400
select * from goods where market_price-shop_price>=400;

//取出商店价格<50的的行,除了market_price列
select goods_id,goods_name,shop_price from goods where shop_price <50;

//选出商品价格不等于10
select goods_id,goods_name,shop_price from goods where shop_price<>10;

//选出商店价格在42,52,76中的商品
select goods_id,goods_name,shop_price from goods where shop_price in (42,52,76);


//取出商店价格在40-80之间的商品
select goods_id,goods_name,shop_price from goods where shop_price between 40 and 80;

//想买商品价格在40—80之间,但不用between and
select goods_id,goods_name,shop_price from goods where shop_price>=50 && shop_price<=80;


//取出商品价格不在40-80之间的商品

select goods_id,goods_name,shop_price from goods where shop_price not between 40 and 80;

②模糊查询
1)%–》通配任意字符
2)_—》通配任何1个字符


//查询'诺基亚'开头的商品
select * from goods where goods_name like'诺基亚%';

//查询'诺基亚__'系列的手机

select * from goods where goods_name like '诺基亚__';

二、group by与统计函数配合使用
count、sum、avg、max、min(统计函数)

//查询每个栏目下面商店最贵商品价格,这里要列cata就有意义,因为是以cata分组的,取goods_id没有意义
select cata,max(shop_price) from goods group by cata;


//查询最新(编号最大)的商品价格
select min(goods_id) from goods;


//查询所有商品的总价格
select sum(shop_price)from goods;


//查询所有商品的总价格的平均值
select avg(shop_price)from goods;

select avg(sum(shop_price))from goods;(语法错误,求平均的函数,自带求总和的)


//查询商品的种类(用count而不是sum)
select count(*) from goods;
注意count(*)统计的元组的个数

//查询每类商品的最低价格

select cata,min(shop_price) from goods group by cata;

//查询每类商品的平均价格
select cata,avg(shop_price) from goods group by cata;

//查询每类商品的库存量,每个商品的库存量就是goods_id
select cata,sum(goods_id) from goods group by cata;

重要思想:select 列名 理解为变量名

//查询每种产品本店价格比市场价格低的钱数
select goods_id,goods_name, market_price-shop_price,shop_price,market_price from goods; 
market_price-shop_price(可以根据原有的列,构造出新列)

//查询每个栏目下面的积压货款
select cata,sum(goods_id*shop_price) from goods group by cata;

//可以给列或者计算结果来取别名: as  别名
select cata,sum(goods_id*shop_price)as hk from goods group by cata;

三、having
where在查询结果前发挥作用,对原数据表起作用,对查询出来的结果表没作用.
having对查询结果表起作用,就能对结果表中数据进行操作

这里写图片描述

//查询每个商品比市场价低多少,并将低50元以上的商品选出
where在查询结果前发挥作用,对原数据表起作用,对查询出来的结果表没作用
select goods_id,market_price-shop_price as sheng from goods where sheng>50;(不正确,因为表中没有sheng字段)

(有market_price-shop_price字段,但是这样相当于进行了两次重复的运算,效率低)
select goods_id,market_price-shop_price as sheng from goods where market_price-shop_price>50;


(利用关键字having,就能对结果表中数据进行操作)
select goods_id,market_price-shop_price as sheng from goods having sheng>50;

//查询第三个栏目下比市场价低100以上的商品
select goods_id,goods_name,market_price-shop_price as sheng  from goods where cata=3 having sheng>40;

//查询积压货款超过500的栏目,以及该栏目积压的货款
select cata,sum(goods_id*shop_price)as jy from goods group by cata having jy>1000;

下面的例子就是对count的理解:元组数(一般写成count(*),其他的条件在分组时,也是元组数,而不管括号里什么判断条件),还有将列属性看成变量的理解:sum(grade<60)

//练习(多看看)
drop table if exists student;
create table student(
stu_name varchar(5),
sub varchar(6),
grade int
) character set utf8;

insert into student values('张三','数学',90);
insert into student values('张三','语文',50);
insert into student values('张三','地理',40);
insert into student values('李四','语文',55);
insert into student values('李四','政治',45);
insert into student values('王五','政治',30);

insert into student values('赵六','政治',100);
insert into student values('赵六','语文',100);
insert into student values('赵六','数学',100);

select stu_name,count(grade<60) as failnum,avg(grade) from student group by stu_name having failnum>=2;
这是错误的,因为count在此处无论写什么,都是统计的同组元素的个数。

//所以逆向思考
select stu_name,avg(grade) from student group by stu_name;


//查询每个学生科目及不及格(grade<60是个布尔表达式,1是真,0是假,所以可以统计不及格的科目)
select stu_name,sum(grade<60) from student group by stu_name;


//查询不及格科目>=2,的学生的平均分(最终结果)
select stu_name,sum(grade<60) as failnum,avg(grade) from student group by stu_name having failnum>=2;
  • 0
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值