sql查询语句典例整理

简单查询：

1、SELECT * FROM 表名称 WHERE 字段名 LIKE '查询内容'

1）、

SELECT * FROM member WHERE NickName LIKE '贝克汉姆'：查询member表NickName字段值为'贝克汉姆'数据

 2）、

SELECT * FROM member WHERE NickName LIKE '贝%'：查询member表NickName字段值首位包含“贝”的数据

 3）、

SELECT * FROM member WHERE NickName LIKE ‘%姆'：查询member表NickName字段值末位包含“姆”的数据

 4）、

SELECT * FROM member WHERE NickName LIKE '%克%'：查询member表NickName字段值中间包含“克”的数据

 

汇总查询：

1、SELECT SUM(字段名) as '总积分' FROM 表名称WHERE 字段名，SUM用于计算总和

1）、

SELECT SUM(Source) as '总积分' FROM member WHERE Source：统计member表Source字段值总和

 2、SELECT COUNT(字段名)  as '总人数' FROM 表名称，COUNT用去统计总数

1）、

SELECT COUNT(NickName)  as '总人数' FROM member ：统计member 表NickName字段下的总人数

 2）、

SELECT COUNT( DISTINCT NickName)  as '总人数' FROM member：统计member 表NickName字段下的总人数（去除重复数据）

3）、汇总统计会员注册时间，语句里面的year是按年统计，“CreateTime”是会员注册时间字段，TIMESTAMPDIFF是时间差函数，“now”是基于现在

SELECT id,TIMESTAMPDIFF(year,CreateTime,now()) from member ORDER BY TIMESTAMPDIFF(year,CreateTime,now()) DESC

 

 

3、SELECT MAX(字段名) as '最大值',MIN(字段名) as '最小值' FROM 表名称 ORDER BY 字段名  （ORDER BY是通过排序的方式展示），MAX、MIN用于展示最大最小值

1）、

SELECT MAX(Source) as '最高分',MIN(Source) as '最低分' FROM member ORDER BY Source：查询member表Source字段最大值和最小值

 分组查询：

1、SELECT 字段名 as '积分' ,COUNT(字段名) as '积分人数' FROM 表名称 GROUP BY 字段名（ GROUP BY是通过分组的方式展示）

1)、

SELECT Source as '积分' ,COUNT(Source) as '积分人数' FROM member GROUP BY Source：统计各积分拥有相同积分的人数  

 

 带条件查询，

语法基本结构：SELECT  +展示数据（SUM(OrderFee)） +FROM 表（orderinfo ） + WHERE字段（StoreId = 100000000384） +展示方式（GROUP BY StoreId）+条件（HAVING COUNT(OrderNo)>100）或者

SELECT  +展示数据（SUM(OrderFee)） +FROM 表（orderinfo ） + WHERE字段（StoreId = 100000000384） +条件（HAVING COUNT(OrderNo)>100）+展示方式（ORDER BY StoreId）

 1、SELECT 字段名,SUM(字段名) as '全部订单总金额' ,AVG(字段名) as '订单平均值',COUNT(字段名) as '订单总数' FROM 表名称GROUP BY 字段名

1）、

SELECT StoreId ,SUM(OrderFee) as '全部订单总金额' ,AVG(OrderFee) as '订单平均值',COUNT(OrderNo) as '订单总数' FROM orderinfo GROUP BY StoreId：根据店铺ID分组，展示全部订单、订单平均值、订单总数

 2）、

SELECT StoreId ,SUM(OrderFee) as '全部订单总金额' ,AVG(OrderFee) as '订单平均值',COUNT(OrderNo) as '订单总数' FROM orderinfo GROUP BY StoreId  HAVING COUNT(OrderNo)>100：根据店铺ID分组，展示全部订单、订单平均值、订单总数，且保留订单总数大于100的数据

 3）、

SELECT SUM(OrderFee) as '全部订单总金额' ,AVG(OrderFee) as '订单平均值',COUNT(OrderNo) as '订单总数' FROM orderinfo WHERE StoreId = 100000000384：统计出指定店铺下的全部订单总金额、订单平均值、订单总数

 4）、

SELECT StoreId as '店铺ID',COUNT(OrderFee) as '金额人数' FROM orderinfo WHERE OrderFee>50 GROUP BY StoreId ：根据店铺ID分组，统计订单金额大于50订单数

5）、

SELECT StoreId,AVG(OrderFee) FROM orderinfo GROUP BY StoreId ORDER BY AVG(OrderFee):根据店铺id分组，计算店铺订单均值排序

 

 6）、

SELECT OrderFee ,OrderNo FROM orderinfo WHERE StoreId = 100000000384  HAVING OrderFee>100 ORDER BY OrderFee DESC:订单金额降序显示（大到小）

SELECT OrderFee ,OrderNo FROM orderinfo WHERE StoreId = 100000000384  HAVING OrderFee>100 ORDER BY OrderFee ASC：订单金额升序显示（小到大）

 

 7）、

SELECT OrderFee,COUNT(OrderFee) FROM orderinfo WHERE StoreId = 100000000384 GROUP BY OrderFee :指定店铺按照订单金额排序且统计每档金额的订单数

8）、区间值查询，统计各区间订单值内的订单数，结果以区间值进行分组显示

select 
    case 
      when OrderFee is null or OrderFee < 100 then '100以内的订单' 
      when OrderFee >= 100 and OrderFee <=1000 then '100到1000的订单'
      when OrderFee > 100000 then '大于10000的订单'
    end 
    as name, 
    count(*) as num
from orderinfo
group by name;

 

 

2、组合条件查询

1）、查询订单价格区间90-100和190-200的订单数据（组合条件查询）

(SELECT *  from orderinfo WHERE Productfee > '90' and Productfee < '100') union
(SELECT *  from orderinfo WHERE Productfee > '190' and Productfee < '200');

 3、跨表查询

1）、统计memberaccount 表里积分数大于100的用户，且（查询）用户在member 表内City值为Changsha（跨表查询）

SELECT MemberID, TotalPoints FROM memberaccount where MemberID  in (SELECT id FROM member WHERE City='Changsha') GROUP BY MemberID HAVING TotalPoints>100

2）、统计memberaccount表TotalFee，member表CreateTime，且以MemberId分组显示。分析查询语句，表后带字段名，FROM一个表JOIN一个表，WHERE接不同字段名但相信数据，这样两个表的数据才能正常匹配

SELECT memberaccount.MemberId , memberaccount.TotalFee, member.CreateTime FROM memberaccount JOIN member WHERE memberaccount.MemberId = member.Id GROUP BY memberaccount.MemberId

 4、跨库查询

1），统计`xkdcore_newfat_ordermanage`库`orderinfo`表内订单数，已MemberId方式分组，且展示订单数大于200的MemberId。统计基于展示的MemberId，查询`xkdcore_newfat_membermanage`库`memberaccount`表下对应MemberId的积分和消费金额

SELECT MemberID, TotalPoints,TotalFee FROM `xkdcore_newfat_membermanage`.`memberaccount` where MemberID  in (SELECT MemberId FROM `xkdcore_newfat_ordermanage`.`orderinfo` GROUP BY MemberID  HAVING COUNT(OrderNo)>200) GROUP BY MemberID 

跨库查询的的关键是，需要在查询语句中写入库名，如“xkdcore_newfat_membermanage”、“xkdcore_newfat_ordermanage”，然后从一个查询结果中提取信息进行二次查询。这里是先查出MemberId，再通过MemberId查询出积分和消费金额

 

 仅执行查询订单数查询语句

SELECT MemberId FROM `xkdcore_newfat_ordermanage`.`orderinfo` GROUP BY MemberID  HAVING COUNT(OrderNo)>200