mysql -h ip -P 端口 -u username -p password -D 数据库 -e 执行命令 例:mysql -h localhost -P3306 -uroot -p -D test -e “select * from user” > user.txt Enter password:******
MySQL导出数据库表
mysqldump --skip-lock-tables -u用戶名 -p密码 数据库名 表名 > 文件名 例:mysqldump --skip-lock-tables -u root -p root test user > user.sql
mysqldump -u 用户名-p 数据库名 表明(–all-tablespaces:所有表) > 文件名 Enter password: ******
查看数据库容量
查看所有数据库容量大小
select
table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables
group by table_schema
order by sum(data_length) desc, sum(index_length) desc;
查看所有数据库各表容量大小
select
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
order by data_length desc, index_length desc;
查看指定数据库容量大小
select
table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables
where table_schema='mysql';
查看指定数据库各表容量大小
select
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
where table_schema='mysql'
order by data_length desc, index_length desc;
sql筛选group count(*) > 1的记录
select xiaohua_id, count(*)
from
(select count(1),aid,xiaohua_id from loan_table group by aid,xiaohua_id) as tmp
group by xiaohua_id
having count(*)>1
select * from table where time is false;
# 不是
select * from table where time is null;
# 也不是
select * from table where time = “”l;
# 更不是
select * from table where time = null;