MySQL: Open-Source Power - Powerful functions of MySQL explained

(Page 3 of 5 )

MySQL contains a huge amount of functions, all of which are accessible directly from the MySQL console application, or as part of a query:

show [object type]: The show command lists all of the [object type]’s available on the current MySQL server. Use the Show command like this:

To view a list of databases on the current MySQL server:

show databases;

To view a list of tables in a specific database:

connect myDatabase;

show tables;

show table status: When connected to a database, the show table status command will display a detailed set of information about each table, including its maximum capacity, table type, data allocation method and date created. Use the show table status like this:

connect myDatabase;

show table status;

use [database name]: If you are connected to one database, and need to query data from another database, then the use command will change which database your queries will be executed against. Use the use command like this:

connect myDatabase;

use myTable;

flush [flush type]: The flush command is used to clear a specific type of MySQL cache. The types of cache that can be cleared are shown below:

• HOSTS: The hosts flush type will remove all entries from the hosts cache tables. You should use the hosts flush type if one or more of your MySQL servers’ I.P. address have changed and need to be updated.
• LOGS: Closes all open handles to log files and then re-opens them.
• PRIVLEGES: Flushes the privileges cache, which is used to grant permission to objects in the data store. The privileges are then reloaded from the grant tables in the mysql database.
• QUERY CACHE: Defragments the memory allocated to query caching. Each dynamically allocated block is moved towards the beginning of the pack to segment unused bits.
• TABLES: Closes all tables and their handles. The query cache is also flushed when the tables flush type is used.
• STATUS: The status flush type should only be used when a MySQL query is being debugged. It resets all of MySQL’s internal status variables to zero.

To remove all entries from the hosts cache tables, we would use this command:

flush HOSTS

drop table [table name]: This command is used to physically remove a table and all of its definitions from a database. Use the drop table command like this:

connect myDatabase;

drop table myTable;

If you are not sure whether or not the table exists, then you can add the “if exists” keywords before the table name, like this:

connect myDatabase;

drop table if exists myTable;

rename table [table name] to [new table name]: Use the rename table command to rename a table. The table in question must not be locked, and [new table name] must not already exist. Use the rename table command like this:

connect myDatabase;

rename table myTable newTable;

truncate table [table name]: The truncate table command deletes every single row from the table [table name]. The command is actually an alias for “commit; delete from [table name]”, however, it is several times faster than using the “delete from [table name]” command because it drops the table and re-creates it. It doesn’t delete each record individually. If you are using the truncate table command within a transaction, however, MySQL will raise an error. Use the truncate table command like this:

connect myDatabase;

truncate table [table name];

I have just described some of the more important and powerful MySQL functions. The MySQL language also includes several handy functions, which, is some cases, aren’t available in any other database management system! These are described on the next page.

Next: Some MySQL language syntax explained >>
 

More MySQL Articles
More By Mitchell Harper

Comments On: MySQL: Open-Source Power

>>> Be the FIRST to comment on this article!