mysql笔记

mysql查出指定时间段中的每天的日期

SELECT
 date_add( date_sub('2018-01-01',interval 1 day), INTERVAL ( cast( help_topic_id AS signed INTEGER ) + 1 ) day ) day 
FROM
 mysql.help_topic 
WHERE
 help_topic_id < DATEDIFF('2019-01-01',date_sub('2018-01-01',interval 1 day))
ORDER BY
 help_topic_id

结果如下：

mysql查询表、字段信息

1.查询表信息

select * from information_schema.tables

2.查询字段信息

select * from information_schema.columns

注：加上where table_schema = (select database())即仅查询当前连接数据库

将毫秒值转换为时间展示

SELECT FROM_UNIXTIME(time/1000,'%Y-%m-%d %h:%i:%s') from person_info

将日期格式字符串转换为时间

STR_TO_DATE(str,format)

format为当前字符串的日期格式

计算两个坐标之间的距离

SELECT
		ROUND(
			6378.138 * 2 * ASIN(
				SQRT(
					POW(
						SIN(
							(
								latitude* PI() / 180 - t.lat * PI() / 180
							) / 2
						),
						2
					) + COS(latitude* PI() / 180) * COS(
						t.lat * PI() / 180
					) * POW(
						SIN(
							(
								longitude* PI() / 180 - t.lng * PI() / 180
							) / 2
						),
						2
					)
				)
			) * 1000
		) AS distance
FROM
	temp
WHERE
	ROUND(
			6378.138 * 2 * ASIN(
				SQRT(
					POW(
						SIN(
							(
								latitude* PI() / 180 - t.lat * PI() / 180
							) / 2
						),
						2
					) + COS(latitude* PI() / 180) * COS(
						t.lat * PI() / 180
					) * POW(
						SIN(
							(
								longitude* PI() / 180 - t.lng * PI() / 180
							) / 2
						),
						2
					)
				)
			) * 1000
		)  < distance

分组排序

				
select * from (
	select 
		r.*,
		@rownum := @rownum +1,
		if( 
			@g = r.order_id,
			@rank := @rank + 1,
			@rank := 1
		) as rank,
		@g := r.order_id 
	from 
		refund r,
		(select @rownum:=0,@g := null, @rank:=0 ) b
		order by r.order_id ,r.update_time desc  
) result
WHERE rank = 1 ;

SQL除法保留小数

select cast(a/b as decimal(4,2)) as rate from test

创建用户并授权

grant all PRIVILEGES on `xxx`.* to 'xxx'@'%' identified by 'xxx'; 
flush PRIVILEGES;