mysql 笔记

最新推荐文章于 2022-10-09 13:32:47 发布

风行傲天

最新推荐文章于 2022-10-09 13:32:47 发布

阅读量230

点赞数

分类专栏： sql 文章标签： mysql

本文链接：https://blog.csdn.net/fengxing_2/article/details/108196300

版权

sql 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

一、随机从表中取一条数据

三种方法：

   （1）ORDER BY RAND()

            写法：SELECT * FROM 表A ORDER BY RAND() LIMIT 1

            缺点：rand()放在ORDER BY 子句中会被执行多次，自然效率极低，mysql官方不提倡

   （2）max(id) * rand()   +   join

           写法：select id from 表A join (select round(rand()*(select max(id ) from table)) as idd) as 表B on 表A.id>表B.idd limit 1;

          大佬们都认为采用JOIN的语法是最优的

   （3）max(id) * rand()   

          写法：SELECT * FROM 表A  WHERE id >= (SELECT FLOOR( MAX(id) * RAND()) FROM 表A ) ORDER BY id LIMIT 1;

         此种写法的效率是介于前面两种之间

二、生成连续数字

-- 生成连续日期
SELECT DATE_FORMAT(DATE_SUB(NOW(), INTERVAL xc MONTH), '%Y-%m') AS DATE
FROM ( 
	SELECT @xi:=@xi+1 AS xc FROM 
	(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) xc1, 
	(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) xc2,  
	(SELECT @xi:=0) xc0 
) xcxc;

-- 生成连续数字
SELECT @xi:=@xi+1 AS xc FROM 
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) xc1, 
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) xc2,  
(SELECT @xi:=0) xc0;

-- 生成当前周前7周数字
SELECT @xi:=@xi+1 AS xc FROM 
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7) xc1, 
(SELECT @xi:=WEEK(CURDATE()) - 7) xc0;

三、日历操作

-- 生成近10天日期
SELECT DATE_FORMAT(DATE_ADD('2019-08-20',INTERVAL -t.help_topic_id DAY),'%Y-%m-%d') AS 'curr_date'  
FROM mysql.help_topic t WHERE t.help_topic_id<=10;

-- 获取本周周一
SELECT SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')-1);
-- 获取本周周末
SELECT SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')-7);

SELECT SUBDATE('2020-08-20',DATE_FORMAT('2020-08-20','%w')-7);

SELECT DATE_FORMAT(DATE_ADD('2019-08-20',INTERVAL -t.help_topic_id DAY),'%Y-%m-%d') AS 'curr_date'  
FROM mysql.help_topic t WHERE t.help_topic_id<7;

-- 生成本周七天日期
SELECT DATE_FORMAT(DATE_ADD((SELECT SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')-1)),INTERVAL +t.help_topic_id DAY),'%Y-%m-%d') AS 'curr_date'  
FROM mysql.help_topic t WHERE t.help_topic_id<7;

SELECT DATE_SUB(CURDATE(),INTERVAL 1 MONTH);
SELECT LAST_DAY(DATE_SUB(CURDATE(),INTERVAL 1 MONTH));
SELECT WEEKDAY(LAST_DAY(CURDATE()))
-- 查询本月最后一天
SELECT LAST_DAY(CURDATE());
-- 查询本月第一天
SELECT DATE_ADD(CURDATE(),INTERVAL -DAY(CURDATE())+1 DAY)
SELECT DAY(CURDATE());

-- 查询最后一天属于第几周
SELECT WEEK(LAST_DAY(CURDATE()));
SELECT WEEK(DATE_ADD(CURDATE(),INTERVAL -DAY(CURDATE())+1 DAY));
-- 查询今天属于第几周
SELECT WEEK(CURDATE());