#表复制-不会复制索引
create table user2 select * from user;
#倍增
insert into user2 select * from user2;
#为当前会话加读写锁 读锁-自己及其它只可读,写锁-自己可读不可写,其它不可读不可写;当前会话有某表读写锁,不读写其它表;解锁只能释放当前会话的锁
LOCK TABLES vehicle write ;
LOCK TABLES user2 write ;
unlock TABLES ;
show open tables where In_use>=1;
#主从状态
show master status;
show slave status;
#一些统计指标,表元数据,变量等(如果不加global,默认session级别)
show global status like '%slow%';
show global status like '%com%';
show global status like '%inno%';
show global status like '%threads_connected%'
show global stauts like 'com_select'
show table status;
SHOW VARIABLES LIKE '%max_connections%'
show processlist;//一般一个sql 的State有十余种,但时间都比较短,如果长时间一种状态如block阻塞(等待锁),可以kill 该sql进程临时解决一些问题
#数据查询导入导出,较直接mysqldump,可以关联查询导出,比较灵活,适合搞一些临时报表,文件目录必须是指定的安全目录:参考show variables like '%secu%';,
select * from user2 into OUTFILE '/var/lib/mysql-files/test3.txt';
load data infile '/var/lib/mysql-files/test3.txt' into table user2;
#分区表,某些情况还是有用的,https://blog.csdn.net/wilsonke/article/details/50109153
create table test (
id int not null ,
name varchar(16),
password varchar(15),
createTime datetime not null default current_timestamp
)
partition by RANGE (year(createTime))(
partition p2017 values less than (2018),
partition p2018 values less than (2019),
partition p2019 values less than (2020)
);
SELECT * FROM test partition(p2019);
SELECT * FROM test
#特殊sql
select * FROM mall_cust ORDER BY RAND() LIMIT 3 #随机取3列