berry sky

/*mysqll过程*/CREATE PROCEDURE `NewProcedure`(`Param` int(11))BEGINDECLARE b INT default 0;declare var_id,var_value varchar(20) default null;DECLARE cur1 cursorFORselect testId from test2 ;declare continue handler for not found set b=1;open cur1;repeatfetch

select t1.id, sum(t2.id) as sumN       from   address as t1, address as t2        where  t1.id>=t2.id        group by t1.id   结果：id     sumN------------------1     16     7

SELECT * FROM (SELECT u.mobile as mobile, SUM(log.playTime) as playCount        ,if(exists (select uv.userId from ivmall.user_vip uv where uv.vipId= 12 and uv.userId = log.accountId),'true','fal

mysql查看表存不存在

EXPLAIN 介绍先从一个最简单的查询开始：Query-1explain select zipcode,firstname,lastname from people;EXPLAIN输出结果共有id,select_type,table,type,possible_keys,key,key_len,ref,rows和Extra几列。

mysql 常用集合执行sql文件，文件放到mysql/bin下cd mysql/bin./mysql -uroot -p123456 数据库名 查看所有过程show procedure status;删除过程drop procedure  过程名//用户受权  过程 grant execute  on procedure dbname.proc

1. if,count,distioct SELECT IF(COUNT(DISTINCT cc.id)>0,'hasBuy','noBuy') FROM cc;

删除已不存在的profileId(pro表)的profile_grows表中的记录:DELETE FROM `profile_grows` pcgw WHERE NOT EXISTS (SELECT id FROM `pro` p WHERE p.`profileId` = pcgw.`profileId`)

/**     * 清空所有记录，并重新定义index     */    @Select("TRUNCATE TABLE 表名")    void deleteAll();

set @item:=0;select @item:=@item+1 as item,h.* from vm_future_holding h where futureid='IF1204' and type=1

set @x=0;//从0开始select @x:=ifnull(@x,0)+1 as rownum,userid,yestPer  from vm_future_performance order by yestPer desc;查询结果：f_yestper

1.  union all不去重//例第一个集合20条数据，第二个集合10条数据   最后结果30条select  * from (select id,name from tab1union allselect id,name from tab2)

<br />--将2个sql语句结果合并，筛选重复的记录 <br /> <br /> <br />select name <br />from ( <br />        select * from test1 where ......<br />        union<br />        select * from test1<br />)

<br />--组合查询表记录<br /> <br />select .....<br />from   表名<br />inner join 表名1<br />inner join 表名2

<br />实现performance表中performance字段的连乘1*2*3*4*5结果，解决代码中循环相乘的问题：<br /> <br />select   exp(sum(ln(performance)))<br />from performance<br />where uid=100104<br />and date>'2010-12-10'<br /> 

Mysql 数据库缓存cache功能总结[转]<br />mysql cache功能分析：<br />1 mysql的cache功能的key的生成原理是：把select语句按照一定的hash规则生成唯一的key，select的结果生成value，即 key=>value。所以对于cache而言，select语句是区分大小写的，也区分空格的。两个select语句必须完完全 全一致，才能够获取到同一个cache。<br />2 生成cache之后，只要该select中涉及到的table有任何的数据变动(inse

1. 把日期转为 时间戳SELECT UNIX_TIMESTAMP( '2011-01-26' ) 结果：12959712002. 把 时间戳 转为时间型  SELECT FROM_UNIXTIME( '1295971200' )结果：2011-01-26 上午 12:00:00

当前week的第一天：select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) + 1 DAY)当前week的最后一天：select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) -

1.  简单select * from ((select * from tb_trade_9740 where f9740_005v='zhoudeling' and f9740_015n=1))tab1inner join(select * from tb_

select          t1_id,         t1_name,         t2_id,         t2_namefrom(select id as t1_id,           (case when name is nu

insert into `tbl_mobile_wap_all_brand_keywords_pc` (id) values(func_sequence_nextval('mysql提供的函数名'));