MySQL中的“骚操作”

1.查询指定数据库中指定的字段名存在哪些表中：

SELECT * from information_schema.columns where TABLE_SCHEMA='db_name' and COLUMN_NAME='filed_name';

2.查询很多字段但只去重其中一个或几个字段：

SELECT a,b,c,d FROM TABLE WHERE id IN (SELECT min(id) 	FROM TABLE GROUP BY b);

3.避免使用“%str%”时导致索引失效：

使用全文索引（FULLTEXT）。

创建全文索引
ALTER TABLE table_name ADD FULLTEXT INDEX index_name (fields_name);
使用全文索引：
select id,fnum,fdst from table_name where match(user_name) against('zhangsan' in boolean mode);

4.避免使用in和not in:

select * from 表A where id in (select id from 表B);
等同于：
select * from 表A where exists(select * from 表B where 表B.id=表A.id);

select colname … from A表 where a.id not in (select b.id from B表);
等同于：
select colname … from A表 Left join B表 on where a.id = b.id where b.id is null;

5.如何查找出两个表中相同字段的相同或不同的值

//获取两张表中相同字段相同的值
select A.column1,A.column2 from A left join B on A.column1=B.column1 where B.plz is not null;
//获取两张表中相同字段不相同的值
select A.column1,A.column2 from A left join B on A.column1=B.column1 where B.plz is null;

6.向一张表中的指定字段插入从另一张表中查询得到的一些字段

insert into table_A(column1,column2) select column1,column2 from table_B;
//注意：这里的column1和column2并要求字段名都相同，只需数据类型相同即可。

7.将一张表中的指定字段更新为从另一张表中查询出来的字段

//更新一个字段--亲自验证使用过
UPDATE
	TABLE1
SET
	TABLE1.column =
		(SELECT
			TABLE2.column
		FROM
			TABLE2
		WHERE
			TABLE2.column = XXX
			AND
			TABLE1.column = TABLE2.column)
			
//更新多个字段
update a
set a.Field1=b.Field1,a.Field2=b.Field2
from [DataBaseA].[dbo].[TableA] a,[DataBaseB].[dbo].[TableB] b 
where a.Field=b.Field
或
update TableA 
set Field1=TableB.Field1,Field2=TableB.Field2
from TableB
where TableA.Field=TableB.Field

8.从MySQL数据库表中随机取一条或者N条记录

//基本写法
SELECT id FROM table ORDER BY RAND() LIMIT n;
//最优写法（效率最高）
SELECT id FROM table t1 JOIN (SELECT RAND() * (SELECT MAX(id) FROM table) AS nid) t2 ON t1.id > t2.nid LIMIT n;
//想要达到完全随机，还可以改成下面这种写法：
SELECT id FROM table t1 JOIN (SELECT round(RAND() * (SELECT MAX(id) FROM table)) AS nid FROM table LIMIT n) t2 ON t1.id = t2.nid;

9.分组排序并取最新的一条数据

//5.7之前
select * from (
    select * from table_name order by column2 desc
) as t
group by t.column1;

//5.7以及之后
select * from (
    select * from table_name order by column2 desc limit 10000
) as t
group by t.column1;

//为什么加limit???
//从mysql5.7开始，子查询的排序已经变为无效了。所以要加个limit,这样子查询就不光是排序，所以此时排序会生效，但有条数限制10000(5.6中验证没有这个限制，可执行验证5.7及之后版本)

10.如何给查询结果添加一个递增序号列

select (@i:=@i+1) as i,table_name.* from table_name,(select   @i:=0) as it;
//最佳写法
//其中@后面的字段可以随意替换，但是必须使select后面的和from中保持一致，(SELECT @i := 0) i比需要起别名，整个sql相当于一个表联查，将生成的序号加到查询结果里面。

//可参考的其他写法
select (@rowNO := @rowNo+1) AS rowno,uid,uname from (SELECT *  FROM t_user ) ;
select (@rowNO := @rowNo+1) AS rowno,a.* from (SELECT * FROM t_user) a,(select @rowNO :=0) b ;
set @rn=0; select @rn:=@rn+1 as rn, m.* from (select * from t_user  )m; -- 连续执行两个语句

11.查看表占用硬盘空间大小

//用M做展示单位，数据库名：database_name，表名：table_name
select concat(round(sum(DATA_LENGTH/1024/1024),2),'M') as table_size
from information_schema.tables
where table_schema='database_name' AND table_name='table_name';
//table_size 3.24M

当你学到更多东西的时候，越会发现自己不知道的更多。
以梦为马，以汗为泉，不忘初心，不负韶华。