MySQL statement notes

1. Querying all non-empty field names in a table

SELECT ' column_name ' from ' information_schema '. ' COLUMNS ' WHERE ' is_nullable ' = ' No ' and ' table_name ' = ' feed ' and ' table_ SCHEMA ' = ' prodb_mgmt '

2. View all fields of a table

< table name >

3. View the table size Http://stackoverflow.com/questions/9620198/how-to-get-the-sizes-of-the-tables-of-a-mysql-database

SELECT     table_name as ' table ',     Round ((((Data_length + index_length)/1024/1024), 2) ' Size in MB ' from information _schema. TABLES WHERE table_schema = "$DB _name" and    table_name = "$TABLE _name";

4. Display the table's build statement http://dev.mysql.com/doc/refman/5.7/en/show-create-table.html

Mysql>ShowCreate TableServerstatusinfo \g;*************************** 1. Row***************************       Table: ServerstatusinfoCreate Table:CREATE TABLE' Serverstatusinfo ' (' Date ' date not NULL, ' server 'varchar( $) not NULL, ' Requestsactivemax 'int(Ten) unsigneddefault '0', ' Requesttimemax 'int(Ten) unsigneddefault '0', ' Requesttimemean 'float default '0', ' Requesttimestddev 'float default '0',  PRIMARY KEY(' Date ', ' server ') ENGINE=InnoDBDEFAULTCHARSET=GBK1Rowinch Set(0.00Sec

4. Delete all data in the table Delete/truncate

Delete from Tablename;delete:will the delete all rows from your table. Next insert would take next auto increment id.truncate tableName; Truncate:will also delete the rows from your table but it'll start from new row with 1.

　　

MySQL statement notes