Mysql知识点总结

1.case…when

SELECT
            JURISDICTIONAL_DEPT dept,
            sum(case DATE_FORMAT(ALARM_TIME,'%Y-%c') when DATE_FORMAT(NOW(),'%Y-%c') then 1 else 0 end) dy,
            sum(case DATE_FORMAT(ALARM_TIME,'%Y-%c') when DATE_FORMAT(DATE_SUB(NOW(), interval 1 month),'%Y-%c') then 1 else 0 end) sy,
            sum(case DATE_FORMAT(ALARM_TIME,'%Y-%c') when DATE_FORMAT(DATE_SUB(NOW(), interval 1 year),'%Y-%c') then 1 else 0 end) sn
        FROM
            ALARM_INFO
        WHERE JURISDICTIONAL_DEPT is NOT NULL
        GROUP BY
            JURISDICTIONAL_DEPT
           
case when 就行了

CASE
	WHEN cm.TRANSFROM_EXPR = '' then tc.DB_COLUMN_NAME
	WHEN cm.TRANSFROM_EXPR is null then tc.DB_COLUMN_NAME
	ELSE cm.TRANSFROM_EXPR
END AS TRANSFROM_EXPR

2.格式化日期

2.1.日期格式化成时间字符串

#表示：格式化ALARM_TIME字段为yyyy-MM字段 ，现在时间匹配为yyyy-MM字段的记录
case DATE_FORMAT(ALARM_TIME,'%Y-%c') when DATE_FORMAT(NOW(),'%Y-%c') then 1 else 0 end

2.2.字符串格式化成日期

str_to_date('${startTime}', '%Y-%m-%d %H:%I:%S')

3.同一表中某个字段的比较

   # //同一表中某个字段的比较       
           SELECT *  FROM ALARM_ASSIGNMENT a where NOT EXISTS (
                    SELECT
                        1
                    FROM
                        ALARM_ASSIGNMENT t2
                    WHERE
                        a.ALARM_NO = t2.ALARM_NO
                    AND a.UPDATE_TIME < t2.UPDATE_TIME
 )

4.将某个字段的值合并排序并以/分隔 不写默认以“，”分隔

#//将DEPT_ID字段的值合并排序并以/分隔   不写默认以“，”分隔
GROUP_CONCAT(DEPT_ID ORDER BY DEPT_ID SEPARATOR '/')

5.返回年份和该年第几个星期

#返回年份和该年第几个星期，可选参数mode：设置每个星期的开始
YEARWEEK(date[,mode])

#返回第几个星期
WEEK(date[,mode])

Mode	First day of week	Range	Week 1 is the first week …
0	sunday	0-53	with a Sunday in this year
1	monday	0-53	with more than 3 days this year
2	sunday	1-53	with a Sunday in this year
3	monday	1-53	with more than 3 days this year
4	sunday	0-53	with more than 3 days this year
5	monday	0-53	with a Monday in this year
6	sunday	1-53	with more than 3 days this year
7	monday	1-53	with a Monday in this year

6.连接字符串

#//连接字符串
CONCAT(’My’, ‘S’, ‘QL’);
结果：MySQL

7.字符串编码

#汉字、英文编码
#十六进制（A-Z）： 0xB0A1,0xB0C5,0xB2C1,0xB4EE,0xB6EA,0xB7A2,0xB8C1,0xB9FE,0xBBF7,     
# 0xBFA6,0xC0AC,0xC2E8,0xC4C3,0xC5B6,0xC5BE,0xC6DA,0xC8BB,0xC8F6,
# 0xCBFA,0xCDDA,0xCEF4,0xD1B9,0xD4D1
<if test="labelId == 2">
                    and (CONV(HEX(left(CONVERT(li.LABEL_NAME USING gbk),1)),16,10)
                    between 45217 and 47613
                    or CONV(HEX(left(CONVERT(li.LABEL_NAME USING gbk),1)),16,10)
                    between 65 and 71
                    or CONV(HEX(left(CONVERT(li.LABEL_NAME USING gbk),1)),16,10)
                    between 97 and 103)
                </if>
                <if test="labelId == 3">
                    and (CONV(HEX(left(CONVERT(li.LABEL_NAME USING gbk),1)),16,10)
                    between 47614 and 50613
                    or CONV(HEX(left(CONVERT(li.LABEL_NAME USING gbk),1)),16,10)
                    between 72 and 78
                    or CONV(HEX(left(CONVERT(li.LABEL_NAME USING gbk),1)),16,10)
                    between 104 and 110)
                </if>
                <if test="labelId == 4">
                    and (CONV(HEX(left(CONVERT(li.LABEL_NAME USING gbk),1)),16,10)
                    between 50614 and 52697
                    or CONV(HEX(left(CONVERT(li.LABEL_NAME USING gbk),1)),16,10)
                    between 79 and 84
                    or CONV(HEX(left(CONVERT(li.LABEL_NAME USING gbk),1)),16,10)
                    between 111 and 116)
                </if>
                <if test="labelId == 5">
                    and (CONV(HEX(left(CONVERT(li.LABEL_NAME USING gbk),1)),16,10)
                    between 52698 and 55289
                    or CONV(HEX(left(CONVERT(li.LABEL_NAME USING gbk),1)),16,10)
                    between 85 and 90
                    or CONV(HEX(left(CONVERT(li.LABEL_NAME USING gbk),1)),16,10)
                    between 117 and 122)
                </if>

8.行号递增(自定义变量)

SELECT
	*,
	( @rowno := ( @rowno + 1 ) ) AS indexNum 
FROM table,( SELECT @rowno := 0 ) s

9.将某个字段的值转换成字符串

#核心方法: CAST ( xxx AS CHAR)
SELECT
*,
CAST(field AS CHAR) AS new_field
FROM
table_name cm

10.字符串截取

#核心方法: substring_index(str,delim,count)
# str:要处理的字符串
# delim:分隔符
# count:计数
# 如果count是正数，那么就是从左往右数，第N个分隔符的左边的全部内容;相反，如果是负数，那么就是从右边开始数，第N个分隔符右边的所有内容

SELECT
	substring_index('192.10.168.1','.',1) as ip1,
	substring_index(substring_index('192.10.168.1','.',2),'.',-1) as ip2,
	substring_index(substring_index('192.10.168.1','.',3),'.',-1) as ip3,
	substring_index(substring_index('192.10.168.1','.',4),'.',-1) as ip4
FROM
	table_name d;
	
# 结果： ip1:192,ip2:10,ip3:168,ip4:1

11.字符串反转

#核心方法: REVERSE(str)
SELECT REVERSE('abcd');

# 结果： 'dcba'

12.获取数据库表、字段

SHOW DATABASES;
select * from information_schema.tables WHERE table_schema='databaseName';
select * from information_schema.columns where table_schema='databaseName' and table_name='TABLE_INFO'

13.授权相关

select user,host from mysql.user;

#修改 root密码
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'abc12345';

CREATE USER 'user1'@'%' IDENTIFIED BY 'abc12345';
GRANT ALL ON *.* TO 'user1'@'%';
GRANT ALL ON *.* TO 'user1'@'%' WITH GRANT OPTION;