mysql-sql案例

//利用主键索引使用内连接 好于limit
select * FROM product p JOIN  (SELECT id FROM product WHERE id > 500 LIMIT 10) tmp ON p.id = tmp.id;
//update 1
update 第一条 也可以用limit 1
//更新自己
update table set a = a + 1,b = b + 1；

//查询自己 更新自己
#1093 - You can't specify target table 'tab1' for update in FROM clause
update tab1 SET NAME = '333' WHERE id in (SELECT id FROM tab1)//报错
#1，将以下查询结果作为中间表，
SELECT id FROM tab1
#2，再查询一遍中间表作为结果集
SELECT t1.id FROM (SELECT id FROM tab1) t1
#更新
update tab1 SET NAME = '333' WHERE id in (SELECT t1.id FROM (SELECT id FROM tab1) t1);

//查询某一pid下 最小的id的数据
select * from t_sys_china a where a.p_id = '150000';
select `NO` FROM t_sys_china a WHERE a.p_id = '150000'; -- 全表扫描 mysql的确定最后的数据是否为150000
EXPLAIN select MIN(`NO`) FROM t_sys_china a WHERE a.p_id = '150000';-- 这样查询全表扫描 min()要把所有数据都比较过才能确定最小
# 解决 :强制使用主键索引 使用索引查询不使用order by 也是有序的
EXPLAIN select * from t_sys_china a USE INDEX(PRIMARY)  where a.p_id = '150000' ;

//COUNT() 大于多少 可以 用2个 count 小于 相减 来得到结果,select(*) 是有缓存的,其查询速度非常快
select count(*) - (select count(*) where id < 100)   ;

//#group by 分组用于统计,而不是筛选数据 -- 有索引,group by 会避免临时表
//#A,B两表连接,主要查询A表列,GROUP BY,ORDER BY 的列尽量相同,而且应该显示为A列
//#union 优化 union all 不过滤 建议放在程序中过滤

//使用变量
eg:
set @age:=20;
select @age;
set @age := @age+100;
select @age;

1.计算排名 原理:2个数利用中间数进行比较,并不断递增
set @pres:=0,@currs:=0,@rank:=0;
select `name`,(@currs:=price) AS price,
@rank:=if(@currs<>@pres,@rank:=@rank+1,@rank) as rank,
@pres:=price as prev
 from product ORDER BY price DESC;
 
2.如果在1号表里面查找到了 就不去2号表了
SET @find:=0;
select id,name,@find:=1 FROM product
UNION
select id,name,1 from B WHERE id =xx and @find <> 1
UNION
select 1,1,@find:=0 from product where 0;
3.row_numer
SELECT @rowno:=@rowno+1 as rowno,
r.*
from t_project r ,(select @rowno:=0) t;

4.查询并获得行id,用于查询并添加中
SELECT @rowno:=@rowno+1 as rowno,
p.*
CONCAT(p.p_name,@rowno)
from
(select * from t_project) p ,
(select @rowno:=0) t;
5.根据某字段分组并递增排序
select rank, result.*
    from (  
        select B.*,
            @rownum:=@rownum+1 ,  
            if(@pdept=B.XXXXXX, @rank:=@rank+1,@rank:=1) as rank,  
            @pdept:=B.XXXXXX  
        from  B , (select @rownum :=0 , @pdept := null ,@rank:=0) a  
    ) result

//2.统计AAA表中 总数 和某一状态的 个数
select
COUNT(a.XXX)
,COUNT(b.XXX)
from AAA a
LEFT JOIN (select PRJ_ID FROM AAA WHERE 某个状态 = '1' ) b
ON  a.XX = b.XX #自己关联自己
GROUP BY a.XXX

//3.先聚合,在关联,防止(出现以字表多条)  PPP与EEE是1:n EEE中有PPP的pid
select  sum(b.num)
    from PPP p    LEFT JOIN (
                        SELECT e.pID,sum(e.EQU_NUM) as total FROM EEE e
                        WHERE     e.column = '1'
                        GROUP BY e.pID
                        ) a ON p.pID = e.pID
    WHERE 1=1
    GROUP BY p.column  

//通过最大时间过滤1
ORDER BY r.DATA_DATE DESC LIMIT 1
//通过最大时间过滤2
SELECT t.ORDER_ID,t.time FROM table t WHERE t.time in (
    SELECT MAX(a.time)
        FROM table2 a GROUP BY a.AA,a.BB HAVING MAX(a.time) )

//in与exists   
1.in 型子查询陷阱              
select * from A where  colum  in (select ids from B whre b.col = xx)
执行原理:     此时索引发挥作用 ,并不是先查询in里面的sql语句 而是
              从上至下扫描主表,判断是否满足主从表关系查询,查询效率非常低
              mysql的查询优化器将in 优化为了exists的执行效果,所以当主表特别大时,从上到下非常慢
              解决 改成 join
              
2.exists和inner join 哪个快?  不一定,创建临时表的一定要排除
select c.cat_id,c.cat_name from category c join goods g
on c.cat_id=g.cat_id group by cat_id;

select c.cat_id,c.cat_name from category c
where exists (select * from goods g where g.cat_id c.cat_id);