Mysql常用语句

Mysql对参赛队伍按照队伍创建时间排序，并且给出队伍的排名（无并列的情况）

奇葩的产品需求，要求对参赛队伍进行创建时间排序，并且给出每个队伍的得票排名
当然有更简单的方法，在每次投票过后，将队伍按投票数倒序排序获取，然后遍历设置排名，但当队伍过多的时候，该方法执行速度过慢，大大影响了程序的反应时间
所以就会出现以下sql语句，当队伍过多的情况下，执行速度还是异常的迅速

SELECT
    pve.teamId,		#队伍主键
    pve.teamPollNum,#队伍票数
    pve.teamName,	#队伍名称
    (SELECT
         f.num
     FROM
     	(SELECT
			@rownum := @rownum + 1 AS num,	#队伍排名
			t.teamId
		FROM
			(SELECT
			    pvi.teamId 		#队伍id主键
			    pvi.teamPollNum #队伍票数
			 FROM
			    活动队伍表 AS pvi	 
			 ORDER BY pvi.teamPollNum DESC #根据队伍票数排名
			) t,
			(SELECT @rownum := 0) r
       ) f
     WHERE
         f.teamId = pvt.teamId	#根据每个队伍的id进行关联
     ) ranking	#队伍排名
FROM
    活动队伍表 AS pve
ORDER BY pve.createTime DESC #队伍的创建时间排序

Mysql时间相关sql

将数据库中createTime时间，格式化为"2020-10-01" 这种的字符串

DATE_FORMAT(createTime,'%Y-%m-%d') 

将字符串格式化为时间类型

STR_TO_DATE(‘2020-10-01’,'%Y-%m-%d') 

获取当前时间是第多少周，NOW()为获取当前时间

YEARWEEK(NOW())

DAYOFWEEK()函数为获取当前为周几，当周日时为1，周一为2依次排下去

DAYOFWEEK(‘2020-10-01’)

对YEARWEEK()函数的优化,以及对上面函数的结合使用
YEARWEEK()该函数有一个问题，当时间为周日时，会判断为下一周，正常逻辑常为周一到周日为一周，而且很多程序逻辑也是这样进行操作的

xxx为数据库中时间类型数据，下列语句为查找当周内所有的数据

	(CASE
     WHEN DAYOFWEEK(date_format(xxx,'%Y-%m-%d'))=1 THEN YEARWEEK(xxx)-1
     ELSE YEARWEEK(xxx) END) =
     (CASE WHEN DAYOFWEEK(date_format(NOW(),'%Y-%m-%d'))=1 THEN YEARWEEK(NOW())-1
     ELSE YEARWEEK(NOW()) END)

上述sql语句用到CASE函数

该语句类似于if else语句，WHEN类似if()小括号中的东西，THEN为if后面大括号的内容，中间还可以添加多个WHEN THEN，类比为else if(){}

xxx为数据库中字符串,如果xxx为1那么该结果为ture

SELECT
	(CASE
	WHEN a.xxx='1' THEN true
	ELSE false END) a
FROM
	表名 as a

快速修改数据库下所有表的编码格式和排序类型

该方法用sql中CONCAT()拼接字符串，拼接成sql语句,
下面sql语句中的xxx为要改的数据库名称，将查询的到的该数据库中所有表，拼接成sql语句，返回到结果中，复制粘贴就可以直接执行。

SELECT
    CONCAT( 'ALTER TABLE ', TABLE_NAME, ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;' ) 
FROM
    information_schema.TABLES 
WHERE
    TABLE_SCHEMA = 'xxx';