mysql-5information_schema

0

4基础管理命令

4.1参数

查

1. show varible like '%switch' ，
2. select @@optimizer_switch
   改
3. set optimizer_switch = on ; 当前会话
4. set global optimizer_switch = on ; 新建会话
5. my.conf 重启生效
   [mysqld]
   optimizer_switch = on
6. hints select /*+BKA(t1) NO_BKA(t2) */ * from t1 join t2 仅执行本次

4.2 表

查
show creat table x\G;
show table status like '%xx' \G;
改
alter table engine=innodb
？

5.1 information_schema.tables

DESC information_schema.TABLES
TABLE_SCHEMA    ---->库名
TABLE_NAME      ---->表名
ENGINE          ---->引擎
TABLE_ROWS      ---->表的行数
AVG_ROW_LENGTH  ---->表中行的平均行（字节）
INDEX_LENGTH    ---->索引的占用空间大小（字节）
DATA_LENGTH -->表示用存储空间大小
DATA_FREE   -->是否由碎片

5.2 I_S.tables 应用

5.2.1 查询整个数据库中所有库和表数量及名称

select table_schema,count(tabe_name),group_concat(table_name)
FROM  information_schema.tables
GROUP BY table_schema;

5.2.2 查询某个库的大小

select table_schema,sum(TABLE_ROWS *TABLE_ROWS  +INDEX_LENGTH  )
from information.tables 
group by table_schema;

5.2.3 查询所有非系统库中的innodb表

SELECT table_schema,table_name,ENGINE 
FROM information_schema.tables
WHERE engine != 'innodb' 
and  tabke_name not in ('sys','performance_schema','information_schema','mysql');

5.2.4 将所有非 innodb表改为innodb

alter table world.country engine = innodb
select concat ('alter table ',table.schema,'.',table_name,' engin = innodb;')
FROM information_schema.tables
WHERE engine != 'innodb'
into outfile '/tmp/a.sql' ;

5.3 case when

统计每门课程 学生的及格率

select c.cname, concat(count( case when sc.scope > 60 then 1 end )/count(sc.cno)*100 ,'%') as 及格率
from course c
join studentscope sc on c.cno=sc.cno
group by c.cname