SQL特殊操作

一、特殊排序

1、MySQL 将某个字段值的记录排在最后或者最前,其余记录单独排序

select  * from user t ORDER BY FIELD(t.age,25,15) asc,t.age asc select  * from user t ORDER BY FIELD(t.age,15) asc,t.age asc select  * from user t ORDER BY FIELD(t.age,15) desc,t.age asc

MySQL中的field（）函数，可以用来对SQL中查询结果集进行指定顺序排序。
函数使用格式如下：
order by （str，str1，str2，str3，str4……），str与str1，str2，str3，str4比较，其中str指的是字段名字，
意为：字段str按照字符串str1，str2，str3，str4的顺序返回查询到的结果集。如果表中str字段值不存在于str1，str2，str3，str4中的记录，放在结果集最前面返回。
1.按 age值 25 15 的顺序排序,值相同则按修改时间排序

select  * from user t ORDER BY FIELD(t.age,25,15) asc,t.age asc

2.将 age= 15 的记录排在最后,其余的记录按修改时间排序

select  * from user t ORDER BY FIELD(t.age,15) asc,t.age asc

3.将age= 15 的记录排在最前面,其余的记录按修改时间排序

select*from user t ORDER BY FIELD(t.age,15) desc,t.age asc

二、查看数据库数据量和占用空间

1、查看指定数据库容量大小

SELECT
    table_schema AS '数据库',
    sum( table_rows ) AS '记录数',
    sum(
    TRUNCATE ( data_length / 1024 / 1024, 2 )) AS '数据容量(MB)',
    sum(
    TRUNCATE ( index_length / 1024 / 1024, 2 )) AS '索引容量(MB)'
FROM
    information_schema.TABLES 
WHERE
    table_schema = '数据库名';

2、查看所有数据库各表容量大小

SELECT
	table_schema AS '数据库',
	table_name AS '表名',
	table_rows AS '记录数',
	TRUNCATE ( data_length / 1024 / 1024, 2 ) AS '数据容量(MB)',
	TRUNCATE ( index_length / 1024 / 1024, 2 ) AS '索引容量(MB)' 
FROM
	information_schema.TABLES 
ORDER BY
	data_length DESC,
	index_length DESC;

3、查看所有数据库各表容量大小

SELECT
	table_schema AS '数据库',
	sum( table_rows ) AS '记录数',
	sum(
	TRUNCATE ( data_length / 1024 / 1024, 2 )) AS '数据容量(MB)',
	sum(
	TRUNCATE ( index_length / 1024 / 1024, 2 )) AS '索引容量(MB)' 
FROM
	information_schema.TABLES 
GROUP BY
	table_schema 
ORDER BY
	sum( data_length ) DESC,
	sum( index_length ) DESC;

4、查看所有数据库各表容量大小

SELECT
	table_schema AS '数据库',
	table_name AS '表名',
	table_rows AS '记录数',
	TRUNCATE ( data_length / 1024 / 1024, 2 ) AS '数据容量(MB)',
	TRUNCATE ( index_length / 1024 / 1024, 2 ) AS '索引容量(MB)' 
FROM
	information_schema.TABLES 
WHERE
	table_schema = 'osale_im' 
ORDER BY
	data_length DESC,
	index_length DESC;

5、查看指定数据库指定表容量大小

SELECT
	table_schema AS '数据库',
	sum( table_rows ) AS '记录数',
	sum(
	TRUNCATE ( data_length / 1024 / 1024, 2 )) AS '数据容量(MB)',
	sum(
	TRUNCATE ( index_length / 1024 / 1024, 2 )) AS '索引容量(MB)' 
FROM
	information_schema.TABLES 
WHERE
	table_schema = '数据库名' 
	AND table_name = '表名';

6、查看所有产生碎片的表

  SELECT table_schema db, 
         table_name, 
         data_free, 
         engine 
    FROM information_schema.tables 
   WHERE table_schema NOT IN ('information_schema', 'mysql') 
     AND data_free > 0 
ORDER BY DATA_FREE DESC;

查询结果中的’Data_free’字段的值就是碎片大小。

7、查看某个表的碎片大小

SHOW TABLE STATUS LIKE '表名';