远程后台登录

mysql -h [ip] -P [port] -u [username] -p [password]

创建database

CREATE DATABASE [dbname]
  CHARACTER SET utf8
  COLLATE utf8_general_ci;

查指定分区数据

select * from table PARTITION (p202505) where ...

授权用户所有来源ip所有权限

# 第一个*是库名，第二个*是权限名，如果是myDB.*即授权myDB这个库的全部权限
grant all privileges on *.* to 'yangxin'@'%'

构造1-10000的整数序列

SELECT @row := @row + 1 as row FROM 
(
  select 0 union all 
  select 1 union all 
  select 3 union all 
  select 4 union all 
  select 5 union all 
  select 6 union all 
  select 6 union all 
  select 7 union all 
  select 8 union all 
  select 9) t, 
(
  select 0 union all 
  select 1 union all 
  select 3 union all 
  select 4 union all 
  select 5 union all 
  select 6 union all 
  select 6 union all 
  select 7 union all 
  select 8 union all 
  select 9) t2,
(
  select 0 union all 
  select 1 union all 
  select 3 union all 
  select 4 union all 
  select 5 union all 
  select 6 union all 
  select 6 union all 
  select 7 union all 
  select 8 union all 
  select 9) t3,
(
  select 0 union all 
  select 1 union all 
  select 3 union all 
  select 4 union all 
  select 5 union all 
  select 6 union all 
  select 6 union all 
  select 7 union all 
  select 8 union all 
  select 9) t4,   
( SELECT @row:=0)t5

查数据库死锁情况和sql执行情况

SHOW ENGINE INNODB STATUS

时间计算函数

使用DATE_SUB、DATE_ADD可以进行时间运算，参数二固定为INTERVAL XXX 单位

使用UTC_TIMESTAMP()可以获取当前时间

SELECT DATE_SUB(UTC_TIMESTAMP(), INTERVAL 300 SECOND)
select u.* from t_test u WHERE createtime >= DATE_ADD(UTC_TIMESTAMP(), INTERVAL -5 MINUTE) ;

时间戳转秒数使用time_to_sec

SELECT time_to_sec(updateTime - createTime) FROM t_test_time

索引相关

添加索引

# 添加主键
ALTER TABLE tbl_name ADD PRIMARY KEY (col_list);
# 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL

# 添加唯一索引
ALTER TABLE tbl_name ADD UNIQUE index_name (col_list);
# 这条语句创建索引的值必须是唯一的。

# 添加普通索引
ALTER TABLE tbl_name ADD INDEX index_name (col_list, col_list2);
#  添加普通索引，索引值可出现多次

# 添加全文索引
ALTER TABLE tbl_name ADD FULLTEXT index_name (col_list);
# 该语句指定了索引为 FULLTEXT ，用于全文索引。
# 或者
ALTER TABLE tbl_name DROP INDEX index_name；
ALTER TABLE tbl_name DROP PRIMARY KEY;

删除索引

DROP INDEX index_name ON tbl_name;

查询索引

SELECT COUNT(1) FROM information_schema.statistics WHERE table_schema = DATABASE()
AND table_name = "test1" AND index_name = "idx_test"

语句组装和条件执行

SET @s =
        (SELECT
             IF(
                  (条件，可以是select语句结果和比较,
                  '条件满足执行的语句',
                  '条件不满足执行的语句'
                 )) ;

PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

复杂insert

insert ignore

如果insert ignore语句中的数据主键中的uniquekey是联合键，其中一个字段为null，将不会被ignore语句忽略掉

insert on duplicate key update

假设有一个表有三个字段：id、name、certNo、age

其中id是自增主键，name+certNo是唯一索引

有两条数据：1，ggy，333，10；2，nnm，444，20；

以下四种sql：

SQL1-可以正常insert

INSERT INTO user(`name`, `certNo`, `age`)
VALUES ('ZZQ', 555, 30)
ON DUPLICATE KEY UPDATE
`age` = VALUES(`age`);

SQL2-会变成update

INSERT INTO user(`name`, `certNo`, `age`)
VALUES ('nnm', 444, 30)
ON DUPLICATE KEY UPDATE
`age` = VALUES(`age`);

SQL3-会变成update

INSERT INTO user(`id`, `name`, `certNo`, `age`)
VALUES (2, 'nnm', 444, 30)
ON DUPLICATE KEY UPDATE
`age` = VALUES(`age`);

SQL4-多冲突，只更新第一个条件

INSERT INTO user(`id`, `name`, `certNo`, `age`)
VALUES (1, 'nnm', 444, 30)
ON DUPLICATE KEY UPDATE
`age` = VALUES(`age`);

这个场景比较特殊，1是主键冲突，而name+certNo是唯一索引冲突，二者冲突的是两条数据，这时转化成update是：

UPDATE user set age = 30 where id = 1 or (name = 'nnm' and certNo = 444);

锁表

有时候执行清理可能被一些自启动的慢查询给阻塞，kill一个又一个，这时可以先锁表，阻塞查询，不断kill掉，让清理的语句优先级提高

LOCK TABLES mytable WRITE;
truncate mytable;
unlock tables ;
select * from mytable;

# 可以从information_schema中查询进程
SELECT * FROM information_schema.`PROCESSLIST` WHERE DB = "mydb" ORDER BY time DESC

分页查询

分页查询一般使用limit、offset实现

limit n   // 只输出前n条
limit n, m  // 跳过n条，从第n+1条开始输出，一共输出m条
limit n offset m // 跳过m条，从第m+1条开始输出，一共输出n条

删除所有库表

可以通过information_schema.tables进行拼接

SELECT CONCAT('DROP TABLE ', 'your_database_name.', table_name, ';')
FROM information_schema.tables
WHERE table_schema = 'your_database_name';

然后执行所有drop语句

目录CONTENT

mysql中的不常用sql及命令