数据库中查看某个数据库的表有多少行
会有偏差,因为是估计值
SELECT
TABLE_SCHEMA,
TABLE_NAME,
TABLE_ROWS
FROM
information_schema.TABLES
WHERE
TABLE_SCHEMA = 'test';
查表的大小:
SELECT
TABLE_SCHEMA ,
TABLE_NAME,
sum(DATA_LENGTH + INDEX_LENGTH + DATA_FREE)/ 1024 / 1024 / 1024 AS SIZE
FROM
information_schema.TABLES
WHERE
TABLE_SCHEMA = database()
GROUP BY
TABLE_SCHEMA,
TABLE_NAME
ORDER BY
SIZE DESC;
查指定表占磁盘空间大小:
SELECT
TABLE_SCHEMA ,
TABLE_NAME,
sum(DATA_LENGTH + INDEX_LENGTH + DATA_FREE)/ 1024 / 1024 / 1024
FROM
information_schema.TABLES
WHERE
TABLE_SCHEMA = "test"
AND TABLE_NAME = 'test_user'
查库的大小:
SELECT
TABLE_SCHEMA ,
sum(DATA_LENGTH + INDEX_LENGTH + DATA_FREE)/ 1024 / 1024 / 1024
FROM
information_schema.TABLES
GROUP BY
TABLE_SCHEMA;
查同样前缀表占用磁盘空间的大小:
SELECT
substr(table_name,
1,
locate('_',
table_name,
locate('_',
table_name)+ 1)) table_prefix,
count(1),
sum(data_length + index_length + DATA_FREE)/ 1024 / 1024 / 1024 SIZE
FROM
information_schema.tables
WHERE
table_schema = database()
GROUP BY
table_prefix
ORDER BY
SIZE,
count(1) DESC;
查询数据库大小与表的大小:
SELECT
sum(data_length + index_length + data_free)/ 1024 / 1024 / 1024 AS SIZE
FROM
information_schema.tables
WHERE
table_schema = database()
AND table_name = 'test_user';
统计某表的主键
SELECT
k.column_name
FROM
information_schema.table_constraints t
JOIN information_schema.key_column_usage k
USING (constraint_name,
table_schema,
table_name)
WHERE
t.constraint_type = 'PRIMARY KEY'
AND t.table_schema = 'db'
AND t.table_name = tbl' ;
统计plt_开头的表的占用空间大小
SELECT
substr(table_name,
1,
locate('_',
table_name)) table_prefix,
sum(data_length + index_length)/ 1024 / 1024 / 1024 SIZE
FROM
information_schema.tables
WHERE
table_schema = database()
AND table_name LIKE "plt_%" ;
查当前数据库中运行的会话
select * from information_schema.processlist where command = 'Query';
mysql 备份
mysqldump -u $user -p$pass -h $host --skip-lock-table --skip-add-drop-table --no-autocommit --skip-opt --extended-insert --quick --create-options -R --force --insert-ignore --default-character-set=utf8 > back.sql
参数说明:
--skip-lock-table(跳过锁表)
--skip-add-drop-table(取消每个数据表创建之前添加drop数据表语句)
--no-autocommit(取消自动提交sql语句)
--skip-opt(--opt等同于--add-drop-table, --add-locks, --create-options, --quick, --extended-insert, --lock-tables, --set-charset, --disable-keys 该选项默认开启, 可以用--skip-opt禁用)
--extended-insert(使用具有多个VALUES列的INSERT语法。这样使导出文件更小,并加速导入时的速度。默认为打开状态,使用--skip-extended-insert取消选项。)
--quick(备份时逐行读取数据)
--create-options(在CREATE TABLE中使用MySQL特定的表选项。备份多个数据库,选项后跟多个库名。备份文件中会包含USE)
--insert-ignore(忽略重复数据)
-R(存储过程)
--force(即使报错也继续执行)
--no-create-info (不导出结构)
--no-data (不导出数据)
--default-character-set=utf8 (指定导出文件默认字符编码)
-D $db table (指定数据库名、表名)
常用命令
跨主机备份:
mysqldump --host=host1 --opt sourceDb| mysql --host=host2 -C targetDb (命令可以将host1上的sourceDb复制到host2的targetDb,前提是host2主机上已经创建targetDb数据库)
mysql数据恢复:
mysql -h host -u root -ppasswd -D db --force <back.sql
mysql 数据导出:
mysql -u root -ppasswd -h host -sNe "select * from mysql.user;" | tr "\t" "," > back.csv
MySQL数据库备份和还原常用命令
(1)备份MySQL数据库的命令
mysqldump -hhostname -uusername -ppassword databasename > backupfile.sql
(2)备份MySQL数据库为带删除表的格式
备份MySQL数据库为带删除表的格式,能够让该备份覆盖已有数据库而不需要手动删除原有数据库。
mysqldump --add-drop-table -uusername -ppassword databasename > backupfile.sql
(3)直接将MySQL数据库压缩备份
mysqldump -hhostname -uusername -ppassword databasename | gzip > backupfile.sql.gz
(4)备份MySQL数据库某个(些)表
mysqldump -hhostname -uusername -ppassword databasename specific_table1 specific_table2 > backupfile.sql
(5)同时备份多个MySQL数据库
mysqldump -hhostname -uusername -ppassword -databases databasename1 databasename2 databasename3 > multibackupfile.sql
(6)仅仅备份数据库结构
mysqldump -hhostname -uusername -ppassword --no-data -d databasename > structurebackupfile.sql
(7)备份服务器上所有数据库
mysqldump -hhostname -uusername -ppassword -all-databases > allbackupfile.sql
(8)还原MySQL数据库的命令
mysql -hhostname -uusername -ppassword databasename < backupfile.sql
(9)还原压缩的MySQL数据库
gunzip < backupfile.sql.gz | mysql -uusername -ppassword databasename
(10)将数据库转移到新服务器
mysqldump -uusername -ppassword databasename | mysql -host=*.*.*.* -C databasename
问题整理:
问题1 : 截取字段内容
从user_submit表里获取到device_id和user_name,user_name取自 blog_url字段的 ‘url/’ 后内容。
user_submit表
根据示例,你的查询应返回以下结果:
select
-- 替换法 replace(string, '被替换部分','替换后的结果')
-- device_id, replace(blog_url,'http:/url/','') as user_name
-- 截取法 substr(string, start_point, length*可选参数*)
-- device_id, substr(blog_url,11,length(blog_url)-10) as user_nam
-- 删除法 trim('被删除字段' from 列名)
-- device_id, trim('http:/url/' from blog_url) as user_name
-- 字段切割法 substring_index(string, '切割标志', 位置数(负号:从后面开始))
device_id, substring_index(blog_url,'/',-1) as user_name
from user_submit;