MySQL数据库常用操作命令

1use mysql;
2select host,user from user;

1show full processlist

 1SELECT
 2    table_schema AS '数据库',
 3    sum(table_rows) AS '记录数',
 4    sum(
 5        TRUNCATE (data_length / 1024 / 1024, 2)
 6    ) AS '数据容量(MB)',
 7    sum(
 8        TRUNCATE (index_length / 1024 / 1024, 2)
 9    ) AS '索引容量(MB)'
10FROM
11    information_schema. TABLES
12GROUP BY
13    table_schema
14ORDER BY
15    sum(data_length) DESC,
16    sum(index_length) DESC;

 1SELECT
 2    table_schema AS '数据库',
 3    table_name AS '表名',
 4    table_rows AS '记录数',
 5    TRUNCATE (data_length / 1024 / 1024, 2) AS '数据容量(MB)',
 6    TRUNCATE (index_length / 1024 / 1024, 2) AS '索引容量(MB)'
 7FROM
 8    information_schema. TABLES
 9ORDER BY
10    data_length DESC,
11    index_length DESC;

 1SELECT
 2    table_schema AS '数据库',
 3    sum(table_rows) AS '记录数',
 4    sum(
 5        TRUNCATE (data_length / 1024 / 1024, 2)
 6    ) AS '数据容量(MB)',
 7    sum(
 8        TRUNCATE (index_length / 1024 / 1024, 2)
 9    ) AS '索引容量(MB)'
10FROM
11    information_schema.tables where table_schema = 'youshu_data';

 1SELECT
 2    table_schema AS '数据库',
 3    table_name AS '表名',
 4    table_rows AS '记录数',
 5    TRUNCATE ( data_length / 1024 / 1024, 2 ) AS '数据容量(MB)',
 6    TRUNCATE ( index_length / 1024 / 1024, 2 ) AS '索引容量(MB)' 
 7FROM
 8    information_schema.TABLES 
 9WHERE
10    table_schema = 'youshu_data' 
11ORDER BY
12    data_length DESC,
13    index_length DESC;
14    
15SELECT
16    table_schema AS '数据库',
17    table_name AS '表名',
18    table_rows AS '记录数',
19    TRUNCATE ( data_length / 1024 / 1024, 2 ) AS '数据容量(MB)',
20    TRUNCATE ( index_length / 1024 / 1024, 2 ) AS '索引容量(MB)' 
21FROM
22    information_schema.TABLES 
23WHERE
24    table_schema = 'youshu_data' 
25ORDER BY
26    table_rows DESC;

 1SELECT
 2    table_schema AS '库名',
 3    table_name AS '表名',
 4    ENGINE AS '存储引擎',
 5    table_rows AS '行数',
 6    trim(
 7        concat(
 8            round(DATA_LENGTH / 1024 / 1024, 1)
 9        )
10    ) AS '数据大小MB',
11    trim(
12        round(index_length / 1024 / 1024, 1)
13    ) AS '索引大小MB',
14    trim(
15        round(DATA_FREE / 1024 / 1024, 1)
16    ) AS '碎片大小MB'
17FROM
18    information_schema. TABLES
19WHERE
20    table_schema NOT IN (
21        'information_schema',
22        'phpmyadmin',
23        'scripts',
24        'test',
25        'performance_schema',
26        'mysql'
27    )
28AND DATA_FREE / 1024 / 1024 > 1000
29ORDER BY
30    DATA_FREE DESC;

1# 注意：该命令只对myisam引擎生效
2OPTIMIZE TABLE 表名;
3show table status from TDMS like '表名'

 1#导出数据库表的表数据和表结构
 2mysqldump -u 'root' -h 127.0.0.1 -p'Test#2023' -P3306 --databases 数据库名
 3表名 > mysqldump.sql
 4
 5#导出包括系统数据库在内的所有数据库
 6mysqldump -u 'root' -h 127.0.0.1 -p'Test#2023' -P3306 --all-databases>all.sql
 7
 8## 导出多张表
 9mysqldump -u 'root' -h 127.0.0.1 -p'Test#2023' --databases test --tables t1 t2>two_tables.sql
10
11## 只导出表结构不导表数据，添加'-d'命令参数
12mysqldump -uroot -h127.0.0.1 -pTest#2023 -P3306 -d testdb game > mysqldump.sql
13
14## 只导出表数据不导表结构，添加'-t'命令参数
15mysqldump -uroot -h127.0.0.1 -pTest#2023 -P3306 -t testdb game > mysqldump.sql
16
17## 只导出数据库的表结构
18mysqldump -uroot -pTest#2023 --no-data --databases testdb>database_testdb.sql
19
20#从test数据库的test_data表中导出id大于100的数据到 /tmp/test.sql 这个文件中
21mysqldump -u 'root' -h 127.0.0.1 -p'123456' test test_data --where=" tdbank_imp_date>='20221201'" > /tmp/ad_spu.sql
22
23## 只导出表数据
24mysqldump  -u 'root' -h 127.0.0.1 -p'test' -P3306 --default-character-set=utf8mb4 --single-transaction --max-allowed-packet=1024M  --extended-insert --no-create-info --where="tdbank_imp_date>='20220309' and tdbank_imp_date<'20221220'" databases table > /data/home/test/backup.sql
25
26mysqldump  -u 'ams_analysis_write' -h 10.101.2.xxx -p'mysql' -P3306 --default-character-set=utf8mb4 --single-transaction --max-allowed-packet=1024M  --extended-insert --no-create-info --where="fdate_zone>='20240219'" youshu_data re_sales_all_new_spu_all > /data/home/keresliu/mysql_data_backup/re_sales_all_new_spu_all_backup.sql
27
28
29## 只导出表结构
30mysqldump  -u 'root' -h 127.0.0.1 -p'123456' -P3306 --add-locks=0 --no-create-info --single-transaction --no-data database table --where="tdbank_imp_date>='20221201'" > /data/home/xxx.sql
31
32## 替换sql里的字符串
33find ./ -name test-sql.txt | xargs sed -i 's/AAA/DDD/g'

1##登陆mysql后
2source /home/abc/abc.sql;
3
4mysql -u 'xxxx' -h 10.101.2.1 -p'xxxx' database < ./test.sql
5
6mysqldump  -u 'XXXX' -h 1.11.2.72 -p'mysql&DD' -P3306 --add-locks=0 --single-transaction --no-create-info youshu_data < /data/home/keresliu/1702977062472.sql

1##根据json字段查询后，按照主健批量更新
2update t_xxxx set f_status=-1 where f_id in (select f_id from t_xxxx  where f_create_time>='2023-10-27 00:00:00'  and f_input like '%yyy%'  and f_input->'$.packageName' in (select json_extract(f_input, '$.packageName')  from t_xxxx  where f_create_time>='2023-10-27 00:00:00'  and f_input like '%yyy%'));
3
4##等值/模糊查询
5select f_id from t_xxxx  where f_create_time>='2023-10-27 00:00:00'  and f_input like '%yyy%'  and f_input->'$.packageName' ='yyy';
6
7select f_id from t_xxxx  where f_create_time>='2023-10-27 00:00:00'  and f_input like '%yyy%'  and f_input->'$.packageName' like 'yyy%';

1#这里相当于把db1的tb1移动到db2里
2RENAME TABLE db1.tb1 TO db2.tb1;
3#同一个库里更改表名
4RENAME TABLE tb1 TO tb2;

1select * from table where id in (50,90,30) order by field(id,50,90,30)