mysql常用语句整理记录

数据库中查看某个数据库的表有多少行

会有偏差，因为是估计值

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;