select
初始准备工作：
1、建木瓜库并选中
create database mugua;
use mugua;
2、创建商品表、栏目表、品牌表
create table goods (
goods_id int primary key auto_increment,
cat_id smallint not null default 0,
goods_sn char(15) not null default '',
goods_name varchar(30) not null default '',
click_count mediumint unsigned not null default 0,
brand_id smallint not null default 0,
goods_number smallint not null default 0,
market_price decimal(7,2) not null default 0.00,
shop_price decimal(7,2) not null default 0.00,
add_time int unsigned not null default 0
);
create table category (
cat_id smallint primary key auto_increment,
cat_name varchar(30) not null default '',
parent_id smallint not null default 0
);
create table brand (
brand_id smallint primary key auto_increment,
brand_name varchar(30) not null default ''
);
3、分别把shop库(ECShop软件里面的库)的商品数据、栏目数据、品牌数据导入到木瓜库
insert into mugua.goods
select
goods_id,cat_id,goods_sn,goods_name,click_count,
brand_id,goods_number,market_price,
shop_price,
add_time
from shop.goods;
insert into mugua.category
select
cat_id,cat_name,parent_id
from shop.category;
insert into mugua.brand
select
brand_id,brand_name
from shop.brand;
select5种子句：
where 条件查询
group by 分组
having 筛选
order by 排序
limit 限制结果条数
where
常用运算符:
| < | 小于 |
| > | 大于 |
| !=或<> | 不等于 |
| <= | 小于等于 |
| >= | 大于等于 |
| in | 在某集合内 |
| between....and..... | 在某范围内 |
in(值1,值2,....,值n)等于值1~值n任意一个都行。
between 值1 and 值2，表示在值1(包括)和值2(包括)之间。
例：查询第4个栏目或第5个栏目的商品。
select goods_id,cat_id,goods_name from goods where cat_id in(4,5);
例：取出商品本店价格在2000和3000之间的商品。
select goods_id,goods_name,shop_price from goods where shop_price between 2000 and 3000;
| not或! | 逻辑非 |
| or或|| | 逻辑或 |
| and或&& | 逻辑与 |
例：想买3000-5000之间的商品，但不用between and。
select goods_id,goods_name,shop_price from goods where shop_price >= 3000 and shop_price <= 5000;
例：想买3000-5000之间的商品，或者500-1000之间的商品。
select goods_id,goods_name,shop_price from goods
where shop_price between 3000 and 5000
or shop_price between 3000 and 5000
select goods_id,goods_name,shop_price from goods
where shop_price >= 3000 and shop_price <= 5000
or shop_price >= 500 and shop_price <= 1000;
not的用法：
例：取出不属于第4,5个栏目的商品。
select goods_id,cat_id,goods_name from goods where cat_id <> 4 and cat_id <> 5;
select goods_id,cat_id,goods_name from goods where cat_id not in(4,5);
模糊查询
like→像
例：想查找’诺基亚‘开头的所有商品。
select goods_id,goods_name from goods
where goods_name like '诺基亚%';
例：取’诺基亚NXX’系列。
select goods_id,goods_name from goods where goods_name like '诺基亚N__';
注意：
‘%’----通配任意字符
‘_’----通配单个字符
group by
常用于统计场合
常与下列聚合函数一起使用：
max : 求最大
min : 求最小
sum : 求总和
avg : 求平均
count:求总行数
例：查出最贵的商品的价格。
select max(shop_price) from goods;
例：查出最大（最新）的商品价格。
select max(goods_id) from goods;
例：查出最便宜的商品价格。
select min(shop_price) from goods;
例：查出最旧(最小)的商品编号。
select min(goods_id) from goods;
例：查询该店所有商品的库存总量。
select sum(goods_number) from goods;
例：查询该店所有商品的平均价格。
select avg(shop_price) from goods;
例：查询该店一共有多少种商品。
select count(*) from goods;
例：查询每个栏目下面，最贵商品价格、最低商品价格、商品平均价格、商品库存量、商品种类。提示(5个聚合函数,sum,avg,max,min,count与group综合运用)。
select cat_id,max(shop_price) from goods group by cat_id;
select cat_id,min(shop_price) from goods group by cat_id;
select cat_id,avg(shop_price) from goods group by cat_id;
select cat_id,sum(goods_number) from goods group by cat_id;
select cat_id,count(*) from goods group by cat_id;
例：请查询出本店每个商品比市场价格低多少？（要把列名当成变量来看待！！！）
select goods_id,goods_name,market_price - shop_price from goods;
例：查询每个栏目下面积压的货款。
select cat_id,sum(shop_price * goods_number) from goods group by cat_id;
可以给列或计算结果取别名，用as
select cat_id,sum(shop_price * goods_number) as hk from goods group by cat_id;
例：查询出本店价格比市场价低多少钱，并且把低200元以上的商品选出来。
1、先做前半部分
select goods_id,goods_name,market_price - shop_price as sheng from goods;
2、再做后半部分
select goods_id,goods_name,market_price - shop_price as sheng from goods where market_price - shop_price > 200;
select goods_id,goods_name,market_price - shop_price as sheng from goods having sheng > 200;
例：同上题，只不过查第3个栏目下比市场价低200以上的商品。
select goods_id,cat_id,market_price - shop_price as sheng from goods
where cat_id = 3
having sheng > 200;
例：查询积压货款超过2W元的栏目，以及该栏目所积压的货款。
1、先做后半句
select cat_id,sum(shop_price * goods_number) as hk from goods
group by cat_id;
2、再用having完成前半句。
select cat_id,sum(shop_price * goods_number) as hk from goods
group by cat_id
having hk > 20000;
having与where异同点:
having与where类似，可筛选数据，where后的表达式怎么写，having就怎么写。
where针对表中的列发挥作用，查询数据。
having针对查询结果中的列发挥作用，筛选数据。
where对表起作用，having是对结果进行筛选。
练习：设有成绩表student，如下：
查询挂科两门及两门以上不及格同学的平均分。
解一：
1、查看每个人的平均分。
select name, avg(score) from student group by name;
2、查看每个人的挂科情况。
select name,score < 60 from student;
3、计算每个人的挂科科目数。
select name,sum(score < 60) from student group by name;
4、综合以上各步，得出
select name,sum(score < 60) as gk,avg(score) as pj from student group by name
having gk >= 2;
解法二：
如何用子查询查询挂科两门及两门以上不及格同学的平均分，where型和from型不限。
先把挂科2门及2门以上的同学找出来。
①
select name,count(*) as gks from student where score < 60 group by name having gks >= 2;
②
select name from
(select name,count(*) as gks from student where score < 60 group by name having gks >= 2) as temp;
③我们用where+from型子查询，查询挂科两门及两门以上不及格同学的平均分。
select name,avg(score) from student
where name
in (select name from
(select name,count(*) as gks from student where score < 60 group by name having gks >= 2) as temp) group by name;
order by
排序可以根据字段来排序，根据字段来排序可以升序排，也可以降序排。默认是升序排列(ASC)，降序排列(DESC)。
例：按照价格把第3个栏目下的商品由低到高(升序)排列。
select goods_id,cat_id,goods_name,shop_price from goods
where cat_id = 3
order by shop_price (asc);
例：按照价格把第3个栏目下的商品由高到低(降序)排列。
select goods_id,cat_id,goods_name,shop_price from goods
where cat_id = 3
order by shop_price desc;
order by可以按多字段排序，order by 列1 [desc/asc],列2 [desc/asc]。
例如：
select goods_id,cat_id,goods_name,shop_price from goods
where cat_id <> 3
order by cat_id,shop_price desc;
例：按发布时间(add_time)由早到晚排序。
select goods_id,goods_name,add_time from goods
order by add_time asc;
limit
在语句的最后，起到限制条目的作用。
limit [offset,][N]
offset:偏移量
N: 取出条目(数)
offset如果不写，则相当于 limit 0,N。
例：取价格第4到第6高的商品。
select goods_id,goods_name,shop_price from goods
order by shop_price desc
limit 3,3;
例：取价格最高的前3个商品。
select goods_id,goods_name,shop_price from goods
order by shop_price desc
limit 3;
例：取最贵的商品。
select goods_id,goods_name,shop_price from goods
order by shop_price desc
limit 1;
例：取最新的商品。
select goods_id,goods_name,shop_price from goods
order by add_time desc
limit 1;
注意：例、取出每个栏目下的最贵的商品。
错误方式一：
#原因：分组之后，再按每个组的第一个排序。
select goods_id,cat_id,goods_name,shop_price from goods
group by cat_id
order by shop_price desc;
错误方式二：
1、建立一张临时表。
create table g2 like goods;
2、向临时表中导入源表排好序的数据。
insert into g2
select * from goods
order by cat_id asc,shop_price desc;
3、查询。
select goods_id,cat_id,goods_name,shop_price from g2;
注意：清空表语法
truncate g2;
正确做法（可使用from子查询语句）：
select * from
(select goods_id,cat_id,goods_name,shop_price from goods
order by cat_id asc,shop_price desc) as tmp
group by cat_id;#按每个组的第一个排序
注意：5个子句是有顺序要求的：where,group by,having,order by,limit。
良好的理解模型：
where 表达式，把表达式放在行中，看表达式是否为真。列：理解成变量，可以运算。
取出结果：可以理解成一张临时表。
子查询
子查询：子查询就是在原有的查询语句中，嵌入新的查询，来得到我们想要的结果集。一般根据子查询的嵌入位置分为，where型子查询，from型子查询。
where 型子查询：把内层查询的结果作为外层查询的比较条件。
例：查询最新的商品(以id为最大为最新)。
select goods_id,goods_name from goods
order by goods_id desc
limit 1;
不让用order by：
select goods_id,goods_name from goods
where goods_id = (select max(goods_id) from goods);
例：用where子查询，取出每个栏目下的最新的商品(以id为最大为最新)。
select goods_id,cat_id,goods_name,shop_price from goods
where goods_id
in (select max(goods_id) from goods group by cat_id);
例：用where子查询，取出每个栏目下的最贵的商品。
select goods_id,cat_id,goods_name,shop_price from goods
where shop_price
in (select max(shop_price) from goods group by cat_id)
from 型子查询：把内层的查询结果当成临时表，供外层sql再次查询。
例：用from子查询，取出每个栏目下的最新的商品。
select * form
(select goods_id,cat_id,goods_name from goods
order by cat_id asc,goods_id desc) as temp
group by cat_id;
exists 型子查询：外层sql查询所查到的行代入内层sql查询，要使内层查询能够成立 。查询可以与in型子查询（？）互换，但效率要高。
例：查有商品的栏目。
select cat_id,cat_name from category
where exists
(select * from goods where goods.cat_id = category.cat_id);