mysql一些常用语法

柔光的暖阳◎ 2023-10-17 12:57 10阅读 0赞

下面罗列需要经常使用的一些MySQL相关语法，不定时更新中...

1、正则表达式，关键字regexp

示例：SELECT \* FROM test where field1 regexp '^(\[0-9a-zA-Z\]+)-(\[0-9a-zA-Z\]+)-(\[0-9a-zA-Z\]+\[.\]+)';

2、替换，关键字REPLACE

示例：UPDATE test SET field1= REPLACE(field1,'－','-')

3、获取截取字符的index，关键字SUBSTRING\_INDEX

示例：SELECT SUBSTRING\_INDEX(field,'sub',1) FROM test;

4、截取字符串

关键字substring，示例：SELECT substring(field,2) FROM test;

关键字MID，语法：SELECT MID(column\_name,start\[,length\]) FROM table\_name，示例：SELECT MID(filed,1,3) as f FROM test;

5、获取字符长度

关键字CHAR\_LENGTH，示例：SELECT CHAR\_LENGTH(field) FROM test;

6、 GROUP BY相关操作

示例：SELECT Gender des,COUNT(1) quantity FROM test GROUP BY des;\#\#\#可以直接使用字段别名来分组统计

示例：SELECT field,COUNT(field) c FROM test GROUP BY ISBN HAVING c>10;\#\#\#关键字having表示分组之后再判断，意思是查询filed合计值大于10的数据

7、时间相关操作

关键字DATE\_FORMAT，示例：SELECT DATE\_FORMAT(\`DateTime\`, '%Y-%m-%d  %H:%i:%s') FROM test;

计算生日，示例：SELECT (YEAR(NOW())-YEAR(Birth)-1) + (DATE\_FORMAT(Birth, '%m%d') <= DATE\_FORMAT(NOW(),'%m%d')) age FROM test;

8、求多行最大值

关键字：greatest，示例：SELECT GREATEST(loanDate,regDate) updateTime FROM test;\#\#\#每行只有一条数据

9、求列最大值

关键字：max，示例：SELECT MAX(GREATEST(loanDate,regDate)) updateTime FROM test;\#\#\#只能得到一个数据

10、多个字段连接

关键字：CONCAT\_WS

示例：SELECT CONCAT\_WS(',',barcode,ISBN,title,author,publish,callNo,pubAddress,pubDate,price,subjec) str FROM \`bookitem\`;\#\#\#连接多个字段，逗号分隔

11、查询重复数据

SELECT NAME,\`recno\`,\`dmId\`,result,cardno FROM \`card\_log\` GROUP BY NAME,\`recno\`,\`dmId\`,cardno,result HAVING COUNT(cardno) > 1

12、删除重复数据只保留一条

DELETE FROM \`card\_log\` WHERE cardLogId IN (  
SELECT cardLogId FROM(  
SELECT cardLogId,NAME,\`recno\`,\`dmId\`,result,cardno FROM \`card\_log\`  
WHERE (NAME,\`recno\`,\`dmId\`,result,cardno) IN (SELECT NAME,\`recno\`,\`dmId\`,result,cardno FROM \`card\_log\` GROUP BY NAME,\`recno\`,\`dmId\`,cardno,result HAVING COUNT(cardno) > 1)  
AND \`cardLogId\` NOT IN (SELECT MIN(cardLogId) FROM card\_log GROUP BY NAME,\`recno\`,\`dmId\`,cardno,result HAVING COUNT(cardno) > 1)  
)tb  
)

13、判断是否为空

关键字：IFNULL、NULLIF和ISNULL

ISNULL(expr) 的用法：  
如expr 为null，那么ISNULL() 的返回值为 1，否则返回值为 0。

IFNULL(expr1,expr2)的用法：  
假如expr1 不为 NULL，则 IFNULL() 的返回值为 expr1;  
否则其返回值为 expr2。IFNULL()的返回值是数字或是字符串，具体情况取决于其所使用的语境。

NULLIF(expr1,expr2) 的用法：  
如果expr1 = expr2 成立，那么返回值为NULL，否则返回值为 expr1。这和CASE WHEN expr1 = expr2  
THEN NULL ELSE expr1 END相同。