Friday, May 3, 2013

KNOW ABOUT INFORMATION_SCHEMA IN MySQL


INFORMATION_SCHEMA is the information database, the place that stores information about all the other databases that the MySQL server maintains. Inside INFORMATION_SCHEMA there are several read-only tables. They are actually views, not base tables, so there are no files associated with them, and you cannot set triggers on them. Also, there is no database directory with that name.
You can do only select INFORMATION_SCHEMA as the default database with a USE statement, you can only read the contents of tables, not perform INSERTUPDATE, or DELETE operations on them.




1.How to view Table engines using Information_schema for a table.
mysql>SELECT table_name, table_type, engine FROM information_schema.tables WHERE table_schema 'mysql' ORDER BY table_name DESC;

2.Find the Column using Information_schema for a table .
mysql>select table_schema "Data Base Name",table_name,column_name from information_schema.columns where column_name LIKE 'dl;

3.Find the Table in which database it has.
mysql>select table_schema "Data Base Name",table_name from information_schema.tables where table_name='plugin';

We can use a lot of things using information_schema.In the above I gave three examples about information_schema.I think it will help you more.

1 comment: