## 常用数据库性能查询

#查看被锁住的表
show open tables where in_use > 0;

#查看运行线程(以及状态信息、执行操作命令等)
show processlist 
show full processlist


#悲观锁与乐观锁
SELECT ... FOR UPDATE (待定)


#count()函数介绍
count(1)会统计包括null值的所有符合条件的字段的条数,count(0)将返回表格中所有存在的行的总数包括值为null的行,
然而count(列名)将返回表格中除去null以外的所有行的总数(有默认值的列也会被计入）,distinct 列名,得到的结果将是除去值为null和重复数据后的结果;
count(1)与count(*)在mysql中没有太大区别,count(*)sql会自动帮你优化;


#查询计数
select ip,count(1) as t
from t1 
group by ip;

#查询计数并筛选
select a.ip,a.t from 
	(	select ip,count(1) as t
		from t1 group by ip
		) a
where a.t >2 ;


#更新表数据
#_1_
update t1 set field_1 = (select column_1,... from t2 
	where column_2 in (select column_1 from t3 where ...),and ...)
where 
	... ;
	
#_2_
UPDATE table1 inner/left/right join table2/(select columns from table3  
 		[inner/left/right join on condition]  [where conditions]) as t3
ON condition
SET column1 = value1,column2 = value2,...
[WHERE conditions]; 	(待定)


#插入数据到表t1,并且其中某个字段值为一个固定值(例值为1),且对插入数据进行过滤(多层筛选,数据来源于一张表,但来源数据需要与其他表进行过滤)
#_1_
insert into t1(column_1,column_2) select 
	a.column_1,1 as column_2 
	from (
		select 
		b.column_1,
		b.column_2,
		...
		from 
		t2 b
		where	
		...
	) a
where a.column_1 not in (
	select c.column_1 from (
		select  
		b.column_1,
		b.column_2,
		...
		from 
		t2 b
		where b.column_3 in(
			select d.column_1 from 
			t3 d
			where 
			...
			)
		and ...
	) as c );

#_2_
insert into t1(column_1,column_2) select 
	a.column_1,1 as column_2 
	from (
		select 
		b.column_1,
		b.column_2,
		...
		from 
		t2 b
		where	
		...
	) a
where a.column_1 not in (
	select c.column_1 from (
		select  
		b.column_1,
		b.column_2,
		...
		from 
		t2 b
		where b.column_3 in(
			select d.column_1 from 
			t3 d
			where 
			...
			)
		and ...
		union all
		select  
		b.column_1,
		b.column_2,
		...
		from 
		t2 b
		where b.column_3 in(
			select d.column_1 from 
			t3 d
			where 
			...
			)
		and ...
	) as c );