mysql简单常用语句汇总

1. 常用函数

uuid和时间戳

SELECT UUID(),UNIX_TIMESTAMP() ;

日期格式 相关

1.时间转字符串

DATE_FORMAT(日期，格式字符串)

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');

2.字符串转时间

STR_TO_DATE(字符串，日志格式)

SELECT STR_TO_DATE('2019-01-20 16:01:45', '%Y-%m-%d %H:%i:%s');

3.时间转时间戳

select unix_timestamp(now());

4.字符串转时间戳

select unix_timestamp('2019-01-20');  

5.时间戳转字符串

select FROM_UNIXTIME(mw.created_at,'%Y-%m-%d %H:%i:%s');

 

 

设置参数

select @m_no:= max(m_no) from vc_m;
set @m_no = @m_no+1;
set @merchant_name = 'xxxx';

insert into vc_merchant_accounts(id,m_no,created_at,updated_at)
VALUES (UUID(),@m_no,UNIX_TIMESTAMP(),UNIX_TIMESTAMP());

连接字符串

普通连接：
1.  CONCAT('assets/card/',@m_no,'_cover.jpg')

//string1，string2代表的是字符串，而separator代表的是连接其他参数的分隔符，可以是符号，也可以是字符串。如果分隔符为NULL，则结果为NULL。此方法参数可以为NULL。
2. CONCAT_WS(separator,string1,string2,...)

group by 连接：
3.  SELECT id,GROUP_CONCAT(type) FROM log_sys_interview GROUP BY type ;
例如：SELECT id,GROUP_CONCAT(type) FROM log_sys_interview GROUP BY type ;

  4.  SELECT id,GROUP_CONCAT(type Separator '#') FROM log_sys_interview GROUP BY type ;

  5.  SELECT id,GROUP_CONCAT(type ORDER BY type DESC) FROM log_sys_interview GROUP BY ip ;

　6.SELECT id,GROUP_CONCAT(DISTINCT type ORDER BY type DESC) FROM log_sys_interview GROUP BY ip ;

-- group_concat拼接排序

group_concat(id order by id)

前一天条件

 -- mw.created_at为时间戳
TO_DAYS(NOW()) - TO_DAYS(FROM_UNIXTIME(mw.created_at,'%Y-%m-%d %H:%i:%s')) = 1

case 

CASE
WHEN mw. STATUS = 0 THEN  '未处理'
WHEN mw. STATUS = 1 THEN  '已审核'
WHEN mw. STATUS = 2 THEN  '已拒绝'
ELSE  '未知'
END
 AS '状态',

 

2.mySQL表明称大小写敏感查询

show variables like '%case%';

 3. UNION ALL 、UNION 操作符用于合并两个或多个 SELECT 语句的结果集。请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。

SELECT E_Name FROM Employees_China
UNION ALL
SELECT E_Name FROM Employees_USA

 4.查询当前进程

show full processlist;

 

5. 清空大量数据表技巧

方法一：truncate 表名

例：truncate bssTbCfrmAccessRcd；

方法二：复制结构 + 删除表 +重命名

create table a like b ; -- 复制表结构
drop table b ;  -- 彻底删除
alter table a rename to b ; -- 重命名空表 

 

6.开启远程访问权限

mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
重载授权表：
FLUSH PRIVILEGES;
-- 注意：记得开启服务器端口

 

 查询实例

-- 套餐失效了2个月的设备
select * from (select   d.keyDevID, group_concat(s.suiteState) status,s.invalidDate
from tbViFiDevice d,tbUserSuite s
where d.keyDevID=s.idxViFiID   
GROUP BY d.keyDevID
HAVING group_concat(s.suiteState)='3' ) x
where  x.invalidDate < STR_TO_DATE( DATE_ADD(NOW(), INTERVAL - 2 MONTH), '%Y-%m-%d %H:%i:%s')  
ORDER BY invalidDate desc

  7.设置自增长段落默认下标

alter table tbxxx AUTO_INCREMENT 0 ;

 8.先备份表结构和数据

#导出命令 -u用户名 -p密码 -h主机IP地址 数据库名 表名1   > 导出文件.sql

mysqldump -uroot -proot -h192.168.1.101 Project persons > persons.sql 

 

9.备份表数据

 mysqldump   -u root  -p EULOCATION tbCid >/opt/uuwifi/log/uuwifih5_xsjs/20210616tbCid.sql

 

。