本文章将记录学习mysql过程中的学习笔记
工具：Sequel Pro , MySql for mac
- 简单查询
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
