燕十八 公益PHP培训
课堂地址:YY频道88354001
学习社区:www.zixue.it
1.1 select 5种子句之group by
作用：把行 按 字段 分组
语法：group by col1,col2,...colN
运用场合
常用于统计场合，如按栏目计算帖子数。统计每个人的平均成绩等。
统计函数：
max:求最大
min：求最小
sum：求总和
avg：求平均
count：求总行数
2.分组查询group:
2.1:查出最贵的商品的价格
select max(shop_price) from goods;
2.2 查出最大（最新）的商品的编号
select max(goods_id) from goods;
select max(goods_id) from goods group by cat_id;
2.3查出最小（最旧）的商品的编号
select min(goods_id) from goods;
select min(goods_id) from goods group by cat_id;
2.4查出最便宜的商品价格
select min(shop_price) from goods;
select min(shop_price) from goods group by cat_id;
2.5查出该店所有商品的库存总量
select sum(goods_number) from goods;
select sum(goods_number) from goods;
2.6查询的有商品的平均价：
select avg(shop_price) from goods;
2.7 查询该店一共有多少种商品。
select count(*) from goods;
2.8查询每个栏目下面
最贵商品价格
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(shop_price) from goods group by cat_id;
商品种类
select cat_id count(*) from goods group by cat_id;
提示：（5个聚合函数，sum,avg,max,min,count与综合运用）
select count(cat_id),cat_id from goods group by cat_id;
要把每个列名当成变量名来看。
2.9 查询出本店的价格比市场价格少的多少钱？
select goods_name ,market_price - shop_price from goods;
2.10 查询出每个栏目下面积压的的货款
select goods_name ,sum(shop_price*goods_number) from goods group by cat_id;
select 五种查询语句之 having 子句。
2.11 查询出栏目3下面省钱都省200以上，并且显示goods_id,goods_name,cat_id,列，还有把省的价钱重命名为s。
select goods_id,goods_name,cat_id,market_price-shop_price as s from goods
where cat_id=3 having s>200;
2.12 查询每个商品所积压的货款。(提示：库存*单价)
select goods_id,goods_name,cat_id,goods_number*shop_price as j from goods;
2.13 查询该店每个栏目下面积压的货款。
select cat_id,sum(goods_number*shop_price) as j from goods group by cat_id;
2.14 查询比市场价省钱200以上的商品及该商品所省的钱（where 和having 分别实现）
1. select goods_id,goods_name,market_price - shop_price as k from goods where
market_price - shop_price >200;
2. select goods_id,goods_name,market_price - shop_price as k from goods;
having k>200;
2.15 查询积压货款超过2 万元的栏目 ，以及该栏目 积压的货款。
select cat_id,sum(shop_price*goods_number) as k from goods group by cat_id having k>20000 ;
来个面试题
查询挂科两门或者两门以上的学生的平均成绩。
创建表
create table sstu
(id int primary key auto_increment,
name char(4) not null default '',
kuomu varchar(10) not null default '',
score tinyint unsigned not null default 0);
向表里插入数据
insert into sstu values(1,'张三','数学',78),(2,'张三','语文',23),(3,'张三','化学',54),(4,'李四','语文',45),(5,'李四','数学',34),(6,'王五','语文',33),(7,'王五','数学',22),(8,'赵六','语文',34);
最终代码为：（要把每一个列看成一人变量，score<1 就得到的是0，或者1，条件成立就得1，否则就是0
sum(score <60)就是统计所有满足条件的各记录之和。是多个1相加，）
select id ,name,sum(score<60) as s,avg(score) from sstu group by name having s>=2;
1）先把挂科两门及以上的找出来
select name,count(*) as gk f
rom sstu where score<60 group by name having gk>=2
2）再把全部人的平均分找出来
select name,avg(score) from sstu group by name;
3）综合两个结果就可以得到挂科两门或者两门以上的学生的平均成绩
select name,avg(score) from sstu where name in(select name from (select name,count(*) as gk f
rom sstu where score<60 group by name having gk>=2) as temp) group by name;
select 子句之 order by asc/desc (注：在默认的情况下，是按照升序排列的即为：asc )
order by 排序功能
按一个或多人字段对查询的结果进行排序
知识点在本项止案例的运用
对栏目的商品由低到高排序
知识点的运用场合描述
各种排序场合描述，如取热点新闻，发帖状元等
排序可以根据字段来排序，可以升序排，可以降序排、
可用 字段名 desc 来声明排列
显示声明 降序排序 asc.
order by 与limit 查询
4.1按价格由高到低排序
select goods_id,goods_name,shop_price from goods order by shop_price;
4.2按发布时间由早到晚排序
select goods_id,goods_name,add_time from goods order by add_time;
4.3按栏目由低到高排序，栏目内部按价格由高到低排序
select goods_id,goods_name,cat_id,shop_price from goods
where cat_id<>3 order by cat_id,shop_price desc;
limit 在语句的最后，起到限制的作用
limit [offset],N
offset,如果不写则相当于limit 0,N
offset : 代表偏移量
N:取出条目
4.4 取出价格最高的前三个商品
select goods_id,goods_name,shop_price from goods order by shop_price desc limit 3;
4.5 把商品按价格由高到底排。
select goods_id,goods_name,shop_price from goods order by shop_price desc;
4.6取价格第4到第6的商品
select goods_id,goods_name,shop_price from goods order by shop_price desc limit 3,3;
4.7取出最贵的商品
select goods_id,goods_name,shop_price from goods order by shop_price desc limit 1;
4.8取出点击量前三名到前5名的商品
select goods_id,goods_name,click_count from goods order by click_count desc limit 3,3;
4.9 取出每个栏目下的最贵的商品？
select * from (select goods_id,goods_name,cat_id,shop_price from goods order by cat_id asc,shop_price desc) as tem group by cat_id;
五种子句的顺序要求
1.where
2.group by
3.having
4.order by
5.limit
5.0 要建立和某张表一样的表
如下：建立一张和goods 一样的表。
create table g2 like goods;
清空表用
truncate tbName;
利用另一张表的数据插入到新表中的方法。
如下：create table g2 select goods_id,goods_name,cat_id,shop_price from goods
order by cat_id,shop_price desc;
良好的理解模型
where 表达式 把表达式放在行中，看表达式是否为真
列 理解成变量，可以运算
取出结果 可以理解成一第临时表
子查询
where 型子查询:把内层的查询结果作为外层查询的比较条件。
要求查询出最新的商品（以id最大为最新）
1.select goods_id,goods_name,cat_id,shop_price from goods order by goods_id desc limit 1;
2.
select goods_id,goods_name,cat_id,shop_price from goods where
goods_id=(select max(goods_id) from goods );
查出每个栏目下最新（以id最大为最新）的商品
1.
select goods_id,goods_name,cat_id,shop_price from goods where goods_id in(select max(goods_id) from goods group by cat_id);
查出每个栏目下最贵的商品
select goods_id,goods_name,cat_id,shop_price from goods where shop_price in(select max(shop_price) from goods group by cat_id);
from 型子查询:把内层的查询结果当成临时表，供外层sql再次查询。
1.查出每个栏目下最新（以id最大为最新）的商品
select * from (select goods_id,goods_name,cat_id,shop_price from goods order by cat_id asc,goods_id desc ) as temp group by cat_id;
exists子查询：把外层的查询结果，拿到内层，看内层的查询是否成立。
select cat_id,cat_name from category where exists (select * from goods where goods.cat_id=category.cat_id);
select cat_id,cat_name from goods where exists (select * from goods where goods.cat_id=category.cat_id);
union 联合
作用：把2次或多次查询的结果合并起来。
要求两次查询的列数一致
推荐：查询的每一列相应的列类型也一样
可以来自多张表
多次sql语句取出的列名可以不一致，此时以第1个sql的列名为准。
如果不同的语句中取出的行，有完全相同（每个列的值都相同）的
那么相同的行将会合并（去重复）
如果不去重复可以加all来指定。（union all）
1.相取第4栏目的商品，价格降序排列，还想取第5商品，价格也降序排列
select goods_id,goods_name,cat_id,shop_price where cat_id=4 or cat_id=5 order by shop_price desc;
2.如果子句中有order by limit ,须加（），推荐放到所有子句之后，即--对最终的结果再来排序。
在子句中，order by 配合limit 使用才有意义。如果order by不配合里limit 使用会被语法分析器去除。
(select goods_id,goods_name,cat_id,shop_price from goods where cat_id=4 order by shop_price desc) union
(select goods_id,goods_name,cat_id,shop_price from goods where cat_id=5 order by shop_price desc) ;
(select goods_id,goods_name,cat_id,shop_price from goods where cat_id=4 ) union
(select goods_id,goods_name,cat_id,shop_price from goods where cat_id=5 ) order by shop_price desc;
取第3个栏目价格前3高的商品和第4个栏目价格前2高的商品的。
用union来实现。
(select goods_id,goods_name,cat_id,shop_price from goods where cat_id= 3 order by shop_price desc limit 3) union (select goods_id,goods_name,cat_id,shop_price from goods where cat_id=4 order by shop_price desc limit 2);
集合相乘，笛卡尔积
其实就是两个集合完全组合
问：设集合 A有M个元素，M个元素各不相同。
设集合B，有N个元素，N个元素各不相同。
A*B 得到 的积有M*N个元素，不可能重复，
表A有9行，表B有10 行，则两表相乘有90行。
如要构造一张ecshop 里的报价单
如下：
select goods_id,goods.cat_id,cat_name,shop_price,goods_name
from
goods left join category
on goods.cat_id=category.cat_id;
左连接
select 列1，列2 列N from
tableA left join tableB
on tableA 列= tableB [此处表连接成一张大表完全当成普通表看]
were group by ,having....照常写
右连接
select 列1，列2 列N from
tableA right join tableB
on tableA 列= tableB [此处表连接成一张大表完全当成普通表看]
were group by ,having....照常写
内连接 inner
select 列1，列2 列N from
tableA inner join tableB
on tableA 列= tableB [此处表连接成一张大表完全当成普通表看]
were group by ,having....照常写
create table boy
( name char(4),
flower char(5))charset utf8;
create table girl
( name char(4),
flower char(5))charset utf8;
如何记忆
1.左右连接可以相互转化。
2.可以把右连接转换为左连接来使用（并推荐使用左连接代替右连接）
3.A站在B的左=B站在A的右边
4.A left join B = B right jion A
5.
6.
7.内连接 查询左右表都有的数据即：不要左/右边NUll的那第一部分
8.内连接是 左右连接的交集。
insert into boy
values('林书豪','玫瑰花'),
('周杰伦','桃花'),
('成龙','茉莉花'),
('犀利哥','荷花'),
('刘德华','狗尾巴花');
insert into girl
values('雷雯姐','玫瑰花'),
('芙蓉姐','桃花'),
('凤姐','茉莉花'),
('林姐','茉莉花'),
('大S姐','荷花');
select boy.*,girl.*
from
girl left join boy
on boy.flower=girl.flower;
select boy.*,girl.*
from
girl inner join boy
on boy.flower=girl.flower;
insert into girl
values('小花','腰花'),
create table member(
id int unsigned primary key auto_increment,
username char(20) not null default ‘’,
gender char(1) not null default ‘’,
weight tinyint unsigned not null default 0,
birth date not null default ‘0000-00-00’,
salary mecimal(8,2) not null default ‘0.00’,
作业：让我们建一个电子商城，如何来设计表
作业：再把商城表的字段，一个个删掉，再一个个加上，并穿插改列操作
思考：能否查出左右连接的并集呢？
答：目前不能，目前的mysql不扶持外连接
outer join
可以用union来达到目的
（select boy.*,girl.*
from
girl left join boy
on boy.flower=girl.flower）union
（select boy.*,girl.*
from
girl right join boy
on boy.flower=girl.flower）;
