1. 准备
查看表的创建语句: show create table [表名]
create table goods (
goods_id mediumint(8) unsigned primary key auto_increment,
goods_name varchar(120) not null default '',
cat_id smallint(5) unsigned not null default '0',
brand_id smallint(5) unsigned not null default '0',
goods_sn char(15) not null default '',
goods_number smallint(5) unsigned not null default '0',
shop_price decimal(10,2) unsigned not null default '0.00',
market_price decimal(10,2) unsigned not null default '0.00',
click_count int(10) unsigned not null default '0'
) engine=myisam default charset=utf8;
2. 基础练习
1: 基础查询 where的练习:
查出满足以下条件的商品
1.1:主键为32的商品
select goods_id,goods_name,shop_price
from goods
where goods_id=32;
1.2:不属第3栏目的所有商品
select goods_id,cat_id,goods_name,shop_price from goods
where cat_id!=3;
1.3:本店价格高于3000元的商品
select goods_id,cat_id,goods_name,shop_price from ecs_goods
where shop_price >3000;
1.4:本店价格低于或等于100元的商品
select goods_id,cat_id,goods_name,shop_price from ecs_goods where shop_price <=100;
1.5:取出第4栏目或第11栏目的商品(不许用or)
select goods_id,cat_id,goods_name,shop_price from ecs_goods
where cat_id in (4,11);
1.6:取出100<=价格<=500的商品(不许用and)
select goods_id,cat_id,goods_name,shop_price from ecs_goods
where shop_price between 100 and 500;
1.7:取出不属于第3栏目且不属于第11栏目的商品(and,或not in分别实现)
select goods_id,cat_id,goods_name,shop_price from ecs_goods where cat_id!=3 and cat_id!=11;
select goods_id,cat_id,goods_name,shop_price from ecs_goods where cat_id not in (3,11);
1.8:取出价格大于100且小于300,或者大于4000且小于5000的商品()
select goods_id,cat_id,goods_name,shop_price from ecs_goods where shop_price>100 and shop_price <300 or shop_price >4000 and shop_price <5000;
1.9:取出第3个栏目下面价格<1000或>3000,并且点击量>5的系列商品
select goods_id,cat_id,goods_name,shop_price,click_count from ecs_goods where
cat_id=3 and (shop_price <1000 or shop_price>3000) and click_count>5;
1.10:取出第1个栏目下面的商品(注意:1栏目下面没商品,但其子栏目下有)
select goods_id,cat_id,goods_name,shop_price,click_count from ecs_goods
where cat_id in (2,3,4,5);
1.11:取出名字以"诺基亚"开头的商品
select goods_id,cat_id,goods_name,shop_price from ecs_goods where goods_name like ‘诺基亚%’;
1.12:取出名字为"诺基亚Nxx"的手机
select goods_id,cat_id,goods_name,shop_price from ecs_goods
where goods_name like ‘诺基亚N__’;
用"_"来匹配单一字符,比%限制精确,一个"_"对应一个未知字符。
1.13:取出名字不以"诺基亚"开头的商品
select goods_id,cat_id,goods_name,shop_price from ecs_goos
where goods_name not like ‘诺基亚%’;
1.14:取出第3个栏目下面价格在1000到3000之间,并且点击量>5 "诺基亚"开头的系列商品
select goods_id,cat_id,goods_name,shop_price from ecs_goods where
cat_id=3 and shop_price>1000 and shop_price <3000 and click_count>5 and goods_name like ‘诺基亚%’;
select goods_id,cat_id,goods_name,shop_price from ecs_goods where
shop_price between 1000 and 3000 and cat_id=3 and click_count>5 and goods_name like ‘诺基亚%’;
3. sql查询模型(重要)
select * from goods where id =1;
理解执行步骤:
1.把 where 之后的表达式想象成if( )里面的条件.那些行被取出来,就看哪一行符合表达式的条件。
所以对于上面的查询,只有id=1后,条件满足后,才能数据被取出来。
2.把列看成变量,既然是变量,变量之间就可以运算,可以取出商品id,也可以取出市场价和商品价格进行运算
#案例: 取出商品价格差100的商品
select * from ecs_goods where >100
表里面原来没有的列(market_price - shop_price),这一列是运算结果,术语叫做‘广义投影’。
对于这个广义投影,就是表中的新的一列,可以为这个运算结果起一个别名,用来记录这个列。
select *,(market_price - shop_price) as discount where (market_price - shop_price) >100;
在输出中,将会把discount作为新的一列输出。
注意:
select goods_id,(market_price - shop_price) discount from ecs_goods where discount > 100;
是不允许的,因为where查询是对表中的原始列数据进行查询的,当原始表中的数据符合where的条件,where会吧结果都放在新的一张表上,对于discount列,他的出现是在结果表上出现,where发挥作用是在查询的时候。
对于结果中的列在想进行筛选,则需要使用having。
面试题:
1.有如下表和数组
把num值处于[20,29]之间,改为20
num值处于[30,39]之间的,改为30
mian表
±-----+
| num |
±-----+
| 3 |
| 12 |
| 15 |
| 25 |
| 23 |
| 29 |
| 34 |
| 52 |
±-----+
解决:
UPDATE mian set num= FLOOR(num/10)*10 where num >=20 and num <=30;
floor取整数,不大于f的整数值。
2.把good表中商品名为’诺基亚xxxx’的商品,改为’HTCxxxx’,
提示:大胆的把列看成变量,参与运算,甚至调用函数来处理 .
substring(),concat()
SELECT
goods_name,
CONCAT(
'HTC',
SUBSTRING(
goods_name,
4,
LENGTH(goods_name)
)
)
FROM
ecs_goods
WHERE
goods_name LIKE '诺基亚%'
4. group与统计函数(重要)
统计函数:
max 最大商品价格
select max(goods_price) from goods;
min 最小价格
select min(goods_price) from goods;
sum 求和函数,新创建一列,列的值是各个符合条件列的相加而来的。
select sum(goods_price) from goods;
avg 求平均
select avg(goods_price) from goods;
count 求数量
select count(*) from goods;
注意:》》》
1. select max(goods_price),goods_id from goods;
在使用了函数后,有获取其他的值,是没有含义的,这里面的goods_id没有使用到,是没有意义的,goods_id
的值是一个原数据第一个的值,是取不出来最大价格哪一行的值的。在sql标准中是错误的,但是在mysql中是支持的,其他数据库则是不支持的,建议不要这种写法,从语义上理解都是错误的。
再看个案例:统计分类为3的所有商品总价,
select sum(shop_price),cat_id where cat_id = 3;
因为要求明确了类别,所以直接筛选cat_id=3,注意看,这个时候,cat_id就有了意义,因为cat_id都是3,是代表这一段数据,这一段数据都是这个值。如果在加一列显示,goods_name则这个name显示的值,是这一段数据的第一个值。
2. count(*) count(1) count(字段) 区别
对于count(*)和count(1)都是区的绝对行数,即使这一行数据全是null。
对于count(字段)的获取,取得是这一列的数据不为null的这一行数据。
group:
分组通常配合着统计函数使用,因为统计函数是获取一组数据中的一个数据,是全文统计。
对于分组,他可以将表根据某个字段,分割成不同的几段数据,每段数据都有相同的属性,比如商品表中,按照
cat_id进行分类。如果单独分类的话,只会显示cat_id的不同,但是数据往往获取的是这一段数据中某个最
值,比如最大值,最小值或者数量,所以,group配合分组函数,完成某一类型数据的使用。比如某一部门的工资和。
1.统计分类为3的所有商品总价,
select sum(shop_price),cat_id from goods where cat_id = 3;
因为要求明确了类别,所以直接筛选cat_id=3,注意看,这个时候,cat_id就有了意义,因为cat_id都是3,
是代表这一段数据,这一段数据都是这个值。如果在加一列显示,goods_name则这个name显示的值,是这一段
数据的第一个值。
2.统计每个栏目下商品的总价
select sum(shop_price) from goods group by cat_id
如果带要带上栏目,则是
select sum(shop_price),cat_id from goods group by cat_id
执行步骤:》》》
(个人理解): group by 将检索出来的数据,进行分组统计,应该是通过一个结构体数组来存储分组后
统计的值,结构体设计应该是{分组列,变量1 ,变量2},数组大小应该是随着类别不同逐渐增减,最大为
count(*)。过程是:遍历检索出来的数据,获取类别,如果类别存在结构体数组中,去计算结构体中的变
量,如果存在变量是sum函数,则去获取这一列的值,然后去计算。如果不存在这一列,则新建一个结构
体,存储到数组中,然后变量计算。最后将数组中的结构体打印出来。 从过程中可以看到,group中是不
新存储数据的,所以如果想从某一类别总获取到某一列的数据,是获取不到的,他获取的数据是这一类别
下所有列都需要参与的数据。group句子中只能使用函数和分类字段。
(老师讲解):....
总结:
1.统计函数
select max(goods_price),goods_id from goods; (错误的)
对于统计函数,可以计算一组数据中的值,这个计算出来的值是这一组数据的值,所以显示结果上不能加其他字段。
这一sql语句在语义上错误,id只是一个值,不能代替这一列数据。但是mysql支持写法,值是第一行的值。
2. group by
按照SQL规范,以group by a,b,c为列,则select 的列,只能在a,b,c里面选择,语义上才没有茅盾,也可以出现统计函数。具体理解看group执行步骤
5. having 筛选
到目前为止知道的的执行顺序;
from > where > group by > 统计函数 > having > order > select
where 之后开始使用select中的别名,后面的语句中都可以使用
对于之前的SQL语句,查询折扣>200的商品
select goods_id, (maket_price-shop_price) discount from goods where (maket_price-shop_price)>200;是正确的。
下面这句:
select goods_id, (maket_price-shop_price) discount from goods where discount >200
则是错误的,之前分析是where是if条件,select 是where通过之后计算的,所以discount列是在where执行结束后新建的虚拟表中才有的,虚拟表产生在where后,where是访问不到discount的。
如果想要对虚拟表中的数据进行筛选,则可以通过having,则:
select goods_id, (maket_price-shop_price) discount from goods having discount >200
练习:
2 分组查询group:
2.1:查出最贵的商品的价格
select max(shop_price) from ecs_goods;
2.2:查出最大(最新)的商品编号
select max(goods_id) from ecs_goods;
2.3:查出最便宜的商品的价格
select min(shop_price) from ecs_goods;
2.4:查出最旧(最小)的商品编号
select min(goods_id) from ecs_goods;
2.5:查询该店所有商品的库存总量
select sum(goods_number) from ecs_goods;
2.6:查询所有商品的平均价
select avg(shop_price) from ecs_goods;
2.7:查询该店一共有多少种商品
select count(*) from ecs_goods;
2.8:查询每个栏目下面
最贵商品价格
最低商品价格
商品平均价格
商品库存量
商品种类
提示:(5个聚合函数,sum,avg,max,min,count与group综合运用)
select cat_id,max(shop_price) from ecs_goods group by cat_id;
3 having与 group综合运用查询:
3.1:查询该店的商品比市场价所节省的价格
select goods_id,goods_name,market_price-shop_price as j
from ecs_goods ;
3.2:查询每个商品所积压的货款(提示:库存单价)
select goods_id,goods_name,goods_number*shop_price from ecs_goods
3.3:查询该店积压的总货款
select sum(goods_number*shop_price) from ecs_goods;
3.4:查询该店每个栏目下面积压的货款.
select cat_id,sum(goods_number*shop_price) as k from ecs_goods group by cat_id;
3.5:查询比市场价省钱200元以上的商品及该商品所省的钱(where和having分别实现)
select goods_id,goods_name,market_price-shop_price as k from ecs_goods
where market_price-shop_price >200;
select goods_id,goods_name,market_price-shop_price as k from ecs_goods
having k >200;
3.6:查询积压货款超过2W元的栏目,以及该栏目积压的货款
select cat_id,sum(goods_number*shop_price) as k from ecs_goods group by cat_id
having k>20000
写3.6 SQL语句的时候先分析,按照执行顺序分析,先确定'from ecs_goods',然后看where执行,如果没有跳过。然后是分析需不需要分组,要按照栏目分组,则'from ecs_goods group by'语句,执行这个语句,然后查看统计函数,和输出列,则是'select cat_id,sum(goods_number*shop_price) as k from ecs_goods group by cat_id',最后再去加上having语句。
where-having-group综合练习题
create table result
(
name varchar(20) not null default '',
subject char(20),
score tinyint unsigned
)engine myisam charset utf8;
insert into result
values
('张三 ','数学',90),('张三','语文',50),('张三','地理',40),
('李四','语文',55),('李四','',45),
('王五','政治',30);
要求:查询出2门及2门以上不及格者的平均成绩:
SELECT
NAME,
avg(score) a,
SUM(score < 60) n
FROM
result
GROUP BY
NAME
HAVING
n >= 2
分析:按照执行步骤,逐步分析:
查询出2门及2门以上不及格者的平均成绩,需要统计平均成绩,也需要计算不及格科目,也需要分组。
1.确定表 from result //不能用where score<60,没法获得平均数
2.分组:采用name分开统计每个人的成绩单
3.确定变量: 需要的结果是name,平均值avg(score),以及需要一个新的一列,用来存储不
几个分数的科目SUM(score<60)。
4.筛选:上面的结果集中返回的是平均成绩和不及格科目,所以需要选择出科目>=2的选项。
注意:
为什么不用count(score<0)确用sum(score<0)?
count()和 sum()都是函数,是根据参数,去运算,对于count就是单纯的去计算运行几次,无论count参数是什么。
sum()是求和函数,是将括号里面的参数拿去相加,score<60是表达式,返回结果是0(假)或者1(真),所以当成绩小于60,表达式是1,sum++,如果不是,则sum+0,还是不变。
#1.先看每个人的平均成绩
SELECT avg(score) from result GROUP BY name
# 看每个人挂科情况
select name , score < 60 from result;
#计算挂的科目数
SELECT name,SUM(score<60) a from result GROUP BY name;
#计算
SELECT name , avg(score) a ,sum(score<60) n from result GROUP BY name HAVING n >=2
SELECT name , avg(score) a from result GROUP BY name HAVING ,sum(score<60) >=2
#查询出2门及2门以上不及格者的平均成绩 (子查询)
SELECT name,avg(score) from result where name in (SELECT name from result GROUP BY name HAVING SUM(score<60) >=2) GROUP BY name;
对于having之后使用统计函数
SELECT name , avg(score) a ,sum(score<60) n from result GROUP BY name HAVING n >=2
SELECT name , avg(score) a from result GROUP BY name HAVING ,sum(score<60) >=2
从上面看,这两个结果都对,都能检索出不及格成绩,不同是 sum函数在前,多了一列变量。
(个人理解:)having之后的统计函数,也是新建了一个变量放在了结构体中的,在进行循环数据分组等操作,对变量进行操作,在having筛选时,在取出变量,进行判断。这个只是隐藏了在 select中显示列。
6. order limit
order by 列1 [desc/asc],列2 desc
是针对最终结果集进行排序的,order要放在查询的结果之后。
limit(offset,N)
是最后的使用,进行分组操作;偏移量和取出个数
from > where > group by > 统计函数 > having > order > select
4: order by 与 limit查询
4.1:按价格由高到低排序
select goods_id,goods_name,shop_price from ecs_goods order by shop_price desc;
4.2:按发布时间由早到晚排序
select goods_id,goods_name,add_time from ecs_goods order by add_time;
4.3:接栏目由低到高排序,栏目内部按价格由高到低排序
select goods_id,cat_id,goods_name,shop_price from ecs_goods
order by cat_id ,shop_price desc;
4.4:取出价格最高的前三名商品
select goods_id,goods_name,shop_price from ecs_goods order by shop_price desc limit 3;
4.5:取出点击量前三名到前5名的商品
select goods_id,goods_name,click_count from ecs_goods order by click_count desc limit 2,3;
练习:
# 查询出每个栏目号下id最大的一栏;
1.用子查询
SELECT
*
FROM
ecs_goods
WHERE
goods_id IN (
SELECT
MAX(goods_id) id
FROM
ecs_goods
GROUP BY
cat_id
)
2. 关联查询
SELECT
*
FROM
ecs_goods,
(
SELECT
cat_id,
MAX(goods_id) id
FROM
ecs_goods
GROUP BY
cat_id
) z
WHERE
z.id = goods_id
3.利用mysql分组时,对不在group by的列,使用第一条记录的特性
select cat_id ,goods_name from (select * from ecs_goods ORDER BY goods_id desc) s GROUP BY cat_id
6.总结
顺序:
- where对原表数据进行查找,作用于行,查询出数据放在虚拟表v1, 行数和原表不同,列相同。
- group/统计函数进行计算,对虚拟表v1,进行统计函数运算,或者进行分组统计,发生列之间的计算,可能生成新列,运算结果生成新表v2。
- having对v2的结果集进行筛选,生成最终结果集v3;
- order by limit 对最终结果集进行排序,或者限制条目。
where > group by > 函数 > having >order >limit