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);
