MySQL 常见查询
MySQL 常见查询
MySQL 常见查询

时间处理

1
2
3
SELECT FROM_UNIXTIME(1651366800); -- 转为,可视化时间 2022-05-01 09:00:00

SELECT UNIX_TIMESTAMP('2022-05-01 09:00:00'); -- 转为时间戳 1651366800

次方

1
2
-- 求2的3次方
SELECT POW(2,3); -- 求次方,值为8

CASE 语句

CASE WHEN THEN END; 结构

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 语法结构

CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END

-- 或者(这个写法省略了 case_value )

CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END
1
2
3
4
5
6
-- demo1, 当 username 值为 dafei,输出1,否则输出2
SELECT
	id, username,
	CASE 1 WHEN username = 'dafei' THEN 1 ELSE 2 END  AS fei 
FROM
	`user`
1
2
3
4
5
6
-- demo2, 当 username 值为 dafei,输出1,否则输出2
SELECT
	id, username,
	 CASE username WHEN 'dafei' THEN 1 ELSE 2 END  AS fei 
FROM
	`user`

流程控制语句 CASE

排序

某条数据置顶

1
2
3
SELECT * FROM user WHERE id<10 ORDER BY id!=3 AND id!=4 #3,4数据放到开始

SELECT * FROM user WHERE id<10 ORDER BY id in (3,4) DESC #3,4数据放到开始

ORDER BY

按照行字段求和

1
2
3
4
5
6
7
8
9
SELECT
	id, num, name,flag,
	SUM( course_01 + course_02 + course_03 ) AS total_score  #对一行这几个字段求和
FROM
	`foo`.`fei` 
WHERE
	`flag` = '1' 
GROUP BY num #分组
ORDER BY `total_score` DESC #排序(求和后排序)

字符串转数字排序

1
SELECT id, name FROM dbfei ORDER BY CAST(id AS UNSIGNED);

关联查询

1
2
3
4
5
SELECT
	* 
FROM
	user AS a
	LEFT JOIN user_detail AS b ON a.id = b.user_id

其他

函数和运算符
流程控制语句 CASE