mysql系列 -查询

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.总结

顺序：

1. where对原表数据进行查找，作用于行，查询出数据放在虚拟表v1, 行数和原表不同，列相同。
2. group/统计函数进行计算，对虚拟表v1，进行统计函数运算，或者进行分组统计，发生列之间的计算，可能生成新列，运算结果生成新表v2。
3. having对v2的结果集进行筛选，生成最终结果集v3；
4. order by limit 对最终结果集进行排序，或者限制条目。

where > group by > 函数 > having >order >limit