1. 基本查询语句回顾

本文章将记录学习mysql过程中的学习笔记
工具：Sequel Pro , MySql for mac

1. 简单查询

select * from oc_order  #从oc_order表中查询所有字段
select o.phone, o.name from oc_order as(可省略) o #查询oc_order 表中的phone,name两个字段

2.关联表查询

select u.user_name , o.paid_price 
  from t_user as u , oc_order as o
  where u.user_id = o.user_id

3.条件查询

select * from my_order t where t.user_id in(
  select t.user_id from my_user t where t.inviter_id = '*****'
) and t.paid_status = 5 and t.paid_date >= '2017-02-10' and t.paid_date < '2017-02-11'

4.连表关系查询

SELECT t.user_id ,
 t.user_name ,
 a.user_name ,
 a.phone ,
 CONCAT(
    a.province_name ,
    a.city_name ,
    a.district_name ,
    a.address
) FROM my_user t ,
 my_user_address a WHERE a.user_id = t.user_id AND a.default_flag = 1 AND t.user_name IN(
    'hjzl' ,
    'CGG慈格格' ,
    '13638293016’ )

SELECT it.id , it.item_name ,
(
    SELECT `NAME` AS c1Name FROM ic_category c WHERE c.id = it.category1_id)
 c1Name ,
(
  SELECT `NAME` AS c2Name FROM ic_category c WHERE c.id = it.category2_id
) c2Name ,
 purchase_price , 
sale_price , 
time_purchase_start , 
time_purchase_end ,
(
  SELECT CONCAT(
                 (
                      SELECT CONCAT(
                                      (
                                        SELECT e.`name` FROM ic_item_supplier2 e WHERE e.id = d.parent_id
                                      ) ,
                                      d.`name`
                                   ) FROM ic_item_supplier2 d WHERE d.id = c.parent_id
                ) ,
                c.`name`
               ) FROM ic_item_supplier2 c WHERE c.id = it.supplier_id
) stall FROM ic_item it WHERE it.id in (158781,158787,151421);

5.加减法运算

select (o.goods_price  + o.express_price) as paid_price  from my_order o

6.sum运算

select sum( (o.goods_price  + o.express_price)  ) as total_gmv  
from oc_order o 
where o.paid_date > '2017-01-01'

7.count

select count(o.goods_price) as '大于1000块的订单个数' from my_order o
where o.goods_price > 1000