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);