mysql中级操作

解析sql执行过程

show VARIABLES like '%profil%' //查看是否开启了剖析 如没开启set profiling=1; 启用

show profiles;

set @query_id=37; //sql_id

select STATE,sum(duration) as Total_R,ROUND(100*SUM(DURATION)/(SELECT SUM(DURATION) FROM INFORMATiON_SCHEMA.PROFILING WHERE QUERY_ID=@query_id),2) as Pct_R,

count(*) as calls,sum(duration)/count(*) as "R/Call" from information_schema.profiling

where query_id=@query_id

group by state

order by Total_R desc;

 

查询数据库中表的数据量最大

select sum(DATA_LENGTH)+sum(INDEX_LENGTH) from information_schema.tables

where table_schema='database-name'

 

 

查看数据库中有哪些表（后面的条件是没有数据的表）

 

SELECT TABLE_NAME TABLES FROM information_schema.TABLES

 

where table_schema = 'database-name' and table_rows = 0

 

导出数据库

mysqldump -u username -p  target_database > new_sql_filename.sql

 

导出的表结构sql文件（加d参数）

mysql -u username -p -d database_name < sql_file_name.sql

 

导出某张表结构

mysqldump -u username -p  -d target_database  table_name > new_sql_filename.sql

 

导出多张表结构

mysqldump -u username -p  -d target_database  tab1_name tab2_name > new_sql_filename.sql

 

查询表中某字段重复记录

select field_name,count(*) as count from table_name group by field_name having count>1;

 

删除重复记录中的一条

delete from table_name where id in
(
    select a.id from
    (
        select max(id) id from table_name a  group by field_name HAVING count(field_name)>1

    ) a
)