MySQL常用SQL语句

一. 从数据库导出sql文件

1.将数据库mydb导出到e:\mysql\mydb.sql文件中:
打开开始->运行->输入cmd 进入命令行模式
c:\>mysqldump -h localhost -u root -p mydb >e:\mysql\mydb.sql
然后输入密码,等待一会导出就成功了,可以到目标文件中检查是否成功。
2.将数据库mydb中的mytable导出到e:\mysql\mytable.sql文件中:
c:\>mysqldump -h localhost -u root -p mydb mytable>e:\mysql\mytable.sql
3.将数据库mydb的结构导出到e:\mysql\mydb_stru.sql文件中:
c:\>mysqldump -h localhost -u root -p mydb --add-drop-table >e:\mysql\mydb_stru.sql
//-h localhost可以省略,其一般在虚拟主机上用

从被锁住的表导出sql文件：
mysqldump --skip-lock-table -h 192.168.1.123 -u root -p db_name t_table > t_table.sql;

二. MySQL备份乱码问题解决:

Use database(数据库名字);

Set character set gbk;

Source a.sql(sql文件的路径);

客户端连接乱码

set names utf8;

三. 批量插入和更新

insert into t_zhb values (1,1),(2,2),(2,1) on duplicate key update age=age+values(age);

四.查看数据库占用空间

use information_schema;
select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from TABLES;
select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from TABLES where table_schema='xxxx';
select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from TABLES where table_schema='xxx' and table_name='xxxx';

五.查看数据库连接ip

//查看DB连接ip
select SUBSTRING_INDEX(host,':',1) as ip , count(*) from information_schema.processlist group by ip;
//查看DB连接ip:port
select host , count(*) from information_schema.processlist group by host;