Mysql难语句总结

1.concat 函数

SELECT id, name

FROM customer

WHERE geo_code LIKE CONCAT(?, '%');

2.模糊查询+in elements

SELECT s

FROM teacher t,student s

WHERE t.tno ='001'

AND s.sname LIKE '张%'

AND s IN ELEMENTS(t.students)

3.自定义变量变为多少，是根据执行计划来走了。应该先把结果得出来，再使用自定义变量

SELECT t.*

FROM

(

SELECT @mycnt := @mycnt + 1 AS ranking,ta.*

FROM

(

SELECT user_id,count(activity_id) finished,max(create_time)

FROM t_d_activity_register

WHERE status = 3

GROUP BY user_id

ORDER BY count(activity_id) DESC,create_time

) ta , (SELECT @mycnt := 0) m

) t

WHERE user_id = 76

4.Mysql查看和创建函数

查看函数：

SHOW FUNCTION SATATE;

服务器命令行创建函数：

DELIMITER ;;

CREATE DEFINER=`root`@`%` FUNCTION `rownum`() RETURNS int(11)

BEGIN

SET @startnum := IFNULL(@startnum,0) + 1;

return @startnum;

END ;;

DELIMITER ;

5.DATEFORMAT函数

SELECT DISTINCT

date_format(an.start_time, '%Y-%m-%d'),

an.user_id,

SUM(an.time_use) time_use,

m.*

FROM

view_activity_userscore_total AS an,

(

SELECT

Min(a.time_use),

Avg(a.time_use),

Max(a.time_use),

Min(

date_format(a.start_time, '%Y-%m-%d')

),

Max(

date_format(a.start_time, '%Y-%m-%d')

)

FROM

view_activity_userscore_total a

WHERE

date_format(a.start_time, '%Y') = 2016

AND a.user_id = 86

) m

WHERE

date_format(an.start_time, '%Y') = 2016

AND an.user_id = 86

GROUP BY

date_format(an.start_time, '%Y-%m-%d')

ORDER BY

DATE_FORMAT(an.start_time,'%Y-%m-%d')

6.小数格式化两位

SELECT

FORMAT(testscore.num/testscore.score,2)

FROM

testscore

SELECT TABLE_NAME,DATA_LENGTH+INDEX_LENGTH,TABLE_ROWS FROM TABLES WHERE TABLE_SCHEMA='report_v2'

7.Mysql创建过程

DELIMITER ;;

CREATE PROCEDURE userpoints(IN activityCode varchar(200),IN userId int,IN activityId int)

BEGIN

SET @table_prefix='t_l_position_';

SET @table_name = concat(@table_prefix, activityCode);

SET @s = concat('SELECT l.longitude longitude,l.latitude latitude FROM ', @table_name,

' l WHERE l.activity_id = ',activityId,' AND l.user_id =', userId);

prepare stmt from @s;

execute stmt;

DEALLOCATE prepare stmt;

END ;;

DELIMITER ;

show index from t_d_activity

ALTER TABLE `t_d_activity` ADD INDEX end_time ( `end_time` ) ;

在where中可以包含任意数目的and和or操作符，在没有任何其他符号的时候，例如括号，SQL会首先执行and条件，然后才执行or语句

eg.    select * from table from id=1 or id=2 and price>=10;

这条语句默认执行的是id=2并且price大于等于10的，或者是id=1。

如果加上括号：select * from table from (id=1 or id=2) and price>=10;

则这条语句执行的是id=1或id=2,并且price大于等于10。

mysql 事件：

查看定时任务的是否开启：show variables like '%sche%';

开启事件：SET GLOBAL event_scheduler = 1;

创建事件： create event order_remain_time on schedule every 60 second on completion preserve do update t_order set remain_time = remain_time -1 where state in (1,2) and situation in(0,1);