4.mysql相关用法

4.mysql相关用法

标签（空格分隔）： 2mysql

---

1.order by 排序输出
mysql> select c_name,c_outprice from commodity
-> where c_outprice is not null
-> order by c_outprice desc;

2.limit
输出售价最贵的前5名信息;
mysql> select c_name,c_outprice from commodity
-> where c_outprice>500
-> order by c_outprice desc
-> limit 5;

最贵的6-10名;
mysql> select c_name,c_outprice,c_outprice-c_inprice as 利润 from commodity
-> where c_outprice -c_inprice>100
-> order by c_outprice-c_inprice desc
-> limit 5,5;
Empty set (0.02 sec)

3.聚合和分组
通常情况下,分组和聚合是两个兄弟,一般都是同时出现

3.1 count :统计记录数—聚合

语法:mysql> select count(*) from commodity;
±---------+
| count(*) |
±---------+
| 60 |
±---------+
1 row in set (0.00 sec)

count(*)无法排除空数据
count(f)可以排除空数据

sum : 对f字段求和
语法:

3.2 group by —分组(求某种商品的数量)

5.having

二、多表连接查询
1 外链接
1.1 左连接
语法:select… from 表1 left join 表2 on 条件(以表1为主表)

1.2 右连接
语法:select… from 表1 right join 表2 on 条件(以表2为主表)

• 主表中有的字段,外链接的表没有数据值则显示为空值;
• 主表中没有该字段,外连接的表中有则不显示,以主表为主.

2.内连接

语法:select… from 表1 inner join 表2 on 外键

将商品表和商品类型拼在一起,

求商品表里的所有的玩具

• 不加拼接条件即on的条件句,会形成笛卡尔积表
• 延伸:连接多张表格(4)

mysql> select *
-> from commoditytype as ct,commodity as c,order as o,customer as cu
-> where c.c_type=ct.ct_id and o.o_cid=c.c_id and o.o_cuid=cu.cu_id;

• 将表格另命名,
• 引用字段时添加表格名称,
• 多条件用and连接

３.子查询

将查询条件作为查询条件进行查询

select c_name,ct_name from commodity inner join commoditytype
-> on c_type=ct_id where ct_name=‘玩具’
1 比玩具平均进价高的所有商品
mysql> select c_name,c_inprice from commodity where c_inprice>(select avg(c_inprice) from commodity where c_type=(select ct_id from commoditytype where ct_name=‘玩具’));

1)求出玩具商品的id即c_id
select ct_id from commoditytype where ct_name=‘玩具’ =A

2)求出玩具平均进价
select avg(c_inprice) from commodity where c_type=A =B

3)大于玩具平均进价的产品
select c_name,c_inprice from commodity where c_inprice> B

2 求出刘德华买过什么商品

• 方法一(子查询+连接查询)
  1)求出刘德华客户id
  select cu_id from customer where cu_name=‘刘德华’ =A

2)求出订单表中的商品单号
select o_cid from order where o_cuid=(A); =B

3) 根据商品单号求出商品名称
select c_name from commodity where c_id in (B)

4)将销售者名字和商品放在一起
mysql> select c_name from commodity where c_id in (select o_cid from order where o_cuid=(select cu_id from customer where cu_name=’ 刘德华’));

方法二:
mysql> select c_name,t.cu_name,t.o_num from commodity inner join ( select * from order inner join customer on cu_id=o_cuid ) as t on c_id=t.o_cid where cu_name=‘刘德华’;(连接查询)

3 销售量在前五的商品
select c_name from commodity where c_id in (select o_cid from order group by o_cid order by sum(o_num) desc) limit 5;
1).将商品id分类汇总排序
mysql> select o_cid from order
-> group by o_cid
-> order by sum(o_num) desc;
±------+

2).排序输出值

• 方法1

select c_name from commodity where c_id in (select o_cid from order group by o_cid order by sum(o_num) desc) limit 5;(注意limit条件不能放在括号里边,//因为c_id 输出是无序的,最外围的条件一输出的值为准,也是无序的)

• 方法2

mysql> select * from commodity as c ,(select o_cid from order group by o_cid order by sum(o_num) desc limit 5) as t where t.o_cid=c.c_id; 将***商品订单分类排序表***作为一个整体,即输出的值直接就是以降序排列

4 求出刘德华和张学友共同买过的商品

方法一:连接查询

方法二:子查询