mysql语句学习

一些有用的mysql语句备份：

1、mysql查询的值为空时，指定默认值

select coalesce(null,1);

 

2、从左到由保留3个字符（包括小数点）

select left(3.60962,3); -- 结果：3.6

 

 

3、修改表引擎的方法

 alter table table_name engine=InnoDB;

 

4、MySQL提供了两种事务型的存储引擎：InnoDB 和 NDB Cluster。显示是否自动提交：

show variables like'AUTOCOMMIT';

 

设置为自动提交：

SET AUTOCOMMIT = 1; -- 1或者ON表示启用，0或者OFF表示禁用。

 修改AUTOCOMMIT对非事务型的表，比如MyISAM或者内存表，不会有任何影响。

 

5、显示表的相关信息

show table status like 'tb_user'; 

 

6、剖析单条查询 使用 show profile:

set profiling = 1;
--> 在服务器上执行的所有语句，都会测量其耗费的时间和其他一些查询执行状态变更相关的数据。

 当一条查询提交给服务器时，此工具会记录剖析信息到一张临时表，并且给查询赋予一个从1开始的整数标识符。

set profiling = 1;
select * from tb_user;
show profiles;
show profile for query 2;

 停止剖析：

set profiling = 0;

 

7、在剖析后，将查询花费的时间降序：

-- 花费时间降序
set @query_id = 137;
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;

 给@query_id赋予show profiles;查询出的Query_ID的值。

 

 

 

--------------------------------------------------------

修改数据库中的数据：

 

update tb_expert e set e.commentNum = (select count(c.id) from tb_comment c where c.toCommentUser_id = e.userid and c.parent_id is null),
e.sumScore = (select coalesce(sum(c.level),0) from tb_comment c where c.toCommentUser_id = e.userid and c.parent_id is null),
e.averageScore = (select coalesce(left(sum(c.level)/count(c.id),3),0) from tb_comment c where c.toCommentUser_id = e.userid and c.parent_id is null)
where e.commentNum != (select count(c.id) from tb_comment c where c.toCommentUser_id = e.userid and c.parent_id is null)
or e.sumScore != (select sum(c.level) from tb_comment c where c.toCommentUser_id = e.userid and c.parent_id is null)