mysql增删改查

查找SELECT

1. group分组与统计函数

   max(),  min(),count(),avg(),sum()

   例子：SELECT avg(`shop_price`)FROM `goods`
   SELECT avg(shop_price) FROM goods group by cat_id;
   

2. 模糊查找

    SELECT * FROM　goods  WHERE name like “诺基亚%”

   数学计算   floor(),ceil(),等。

3. 筛选having

   select goods_name  (market_price-shop_price) as sheng where having sheng>200

4. order by 排序

   select   goods_name  ,shop_price FROM goods order by shop_price;(升序)

   select   goods_name  ,shop_price FROM goods order by shop_price desc;(降序)

5. 选出价格最高的三个商品

   select   goods_name  ,shop_price FROM goods order by shop_price desc limit 3;

   select   goods_name  ,shop_price FROM goods order by shop_price desc limit 3,2;(跳过3行，取2行)。

6. where子查询

   SELECT goods_id,goods_name FROM goods WHERE goods_id in (SELECT max(goods_id) FROM goods group by cat_id);即可选出每一个项目的最大值。

    7.from子查询，把查询到的结果作为新的表进行查询。

    SELECT * FROM (SELECT goods_id ,goods_name,cat_id FROM goods group by cat_id) as tmp

    8.exists子查询，在不同表之间查询

    SELECT  * FROM categroy WHERE exists(SELECT * FROM goods WHERE goods.cat_id=categroy.cat_id );

    9.内链表 inner join 连接两张表

     SELECT boy.hid,boy.bname,girl.hid,girl.gname from boy inner join girl on boy.hid=girl.hid

    10.左右内链

    SELECT boy.hid,boy.bname,girl.hid,girl.gname from boy left join girl on boy.hid=girl.hid

    SELECT boy.hid,boy.bname,girl.hid,girl.gname from boy rightjoin girl on boy.hid=girl.hid