MySQL is the most popular, free, open-source database management system for the Unix/Linux platform. The MySQL database management system contains an enormous amount of functionality and power. In this article, Mitchell will describe some of its most popular features and most powerful functions.
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:
To view a list of tables in a specific database:
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:
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:
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:
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:
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:
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:
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:
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.