Mysql 常用命令

1.时间戳转换

select FROM_UNIXTIME(1156219870);

Select UNIX_TIMESTAMP('2006-11-04 12:23:00');  

2.类型转换

cast(value as type);

BINSRY 二进制

CHAR() DATE TIME DATETIME DECIMAL(浮点)

SIGNED(整数) UNSIGNED无符号整数)

3.时间 字符串

SELECT STR_TO_DATE('1992-04-12',"%Y-%m-%d")

SELECT DATE_FORMAT(now(),"%Y-%m-%d %H:%i:%s %p")

DATE_SUB(curdate(),interval 1 day) -1d

DATE_SUB(curdate(),interval -1 day) +1d

DATE_SUB(curdate(),interval 1 month) -1m

DATE_SUB(curdate(),interval -1 month) +m

DATE_SUB(curdate(),interval 1 year) -1y

DATE_SUB(curdate(),interval -1 year) +1y

DATE_ADD(curdate(),interval 1 day) +1d

4.String

replace('abc','a','b') 替换目标字符串

length(‘aaa’) 字符串长多

concat('a','b','c') 连接abc

concat_ws(sep,s1,s2...,sn) 将s1,s2...,sn连接成字符串，并用sep字符间隔

substring_index('avvdvvc','v',1) 截取字符串，从0至第1次出现V的位置之前

substring('abc',1) 截取字符串，从1开始 bc

substring('abc',1,1) 截取字符串，从1开始 截取1个长度b

right/left('abc',1) 自左/右截取1个长度

LOCATE('abc','a')>0 是否包含，包含>o

trim rtrim ltrim

find_in_set(patientId,patientIds)>0

FIND_IN_SET(str,strlist)

假如字符串str 在由N 子链组成的字符串列表strlist 中，则返回值的范围在 1 到 N 之间。一个字符串列表就是一个由一些被‘,’符号分开的自链组成的字符串。如果第一个参数是一个常数字符串，而第二个是type SET列，则   FIND_IN_SET() 函数被优化，使用比特计算。如果str不在strlist 或strlist 为空字符串，则返回值为 0 。如任意一个参数为NULL，则返回值为 NULL。这个函数在第一个参数包含一个逗号(‘,’)时将无法正常运行。返回值为str在strlist中的位置，从1开始计数。

LOCATE(substr,str) || POSITION(substr IN str) 定位指定字符再字符串中的位置['\等特殊字符需要转义']

4.聚合函数

常用于GROUP BY从句的SELECT查询中

1.AVG(col)返回指定列的平均值

2.COUNT(col)返回指定列中非NULL值的个数

3.MIN(col)返回指定列的最小值

4.MAX(col)返回指定列的最大值

5.SUM(col)返回指定列的所有值之和

6.GROUP_CONCAT(col) 返回由属于一组的列值连接组合而成的结果

SELECT @@global.group_concat_max_len; 查看GROUP_CONCAT的最大长度

SET GLOBAL group_concat_max_len=102400; 设置group_concat的最大长度

5.外键失效

SET FOREIGN_KEY_CHECKS = 0;

TRUNCATE TABLE user_db_test.u_patient;

SET FOREIGN_KEY_CHECKS = 1;

6.判读数字和字符串

select ('123a' REGEXP '[^0-9.]'); --‘123a'中含有字符'a' 输出结果为1 mysql中常量true输出为1 false输出为0

7.更新插入问题

INSERT INTO t_name VALUES(2,'lisi') ON DUPLICATE KEY UPDATE name='zhangsan';

mysql 删除插入问题（出现重复先删除，再插入）

REPLACE INTO t_name VALUES(2, 'lisi');

mysql 忽略异常插入

INSERT IGNORE INTO t_name VALUES(2, 'lisi');

8.查看数据库是否锁表

show open tables from db;

9.查看正在执行的命令

show processlist；（100条）

show full processlist;

select * from information.processlist;

10.查看外键

SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE

REFERENCED_TABLE_NAME = 'u_patient' AND REFERENCED_COLUMN_NAME='patientId'

11.查询系统中记录数

SELECT
 table_name,
 table_rows
FROM
information_schema.`TABLES`
WHERE
 TABLE_SCHEMA = 'suifang_temp'
ORDER BY table_rows DESC;

SELECT COUNT(*) FROM suifang_temp.patient_temp;

table_rows = count(*) myisam

table_rows <> count(*) Innodb

12.AES加解密

select HEX(AES_ENCRYPT('123', '123'));
select AES_DECRYPT(UNHEX('81F44672F7707F551EA23C36B66F7AFE'), '123');