Home arrow MySQL arrow Page 3 - MySQL: Open-Source Power

MySQL: Open-Source Power

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.

Author Info:
By: Mitchell Harper
Rating: 3 stars3 stars3 stars3 stars3 stars / 8
December 09, 2001
  1. · MySQL: Open-Source Power
  2. · Popular features of MySQL
  3. · Powerful functions of MySQL explained
  4. · Some MySQL language syntax explained
  5. · Conclusion

print this article

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.
blog comments powered by Disqus

- MySQL and BLOBs
- Two Lessons in ASP and MySQL
- Lord Of The Strings Part 2
- Lord Of The Strings Part 1
- Importing Data into MySQL with Navicat
- Building a Sustainable Web Site
- Creating An Online Photo Album with PHP and ...
- Creating An Online Photo Album with PHP and ...
- PhpED 3.2 – More Features Than You Can Poke ...
- Creating An Online Photo Album with PHP and ...
- Creating An Online Photo Album with PHP and ...
- Security and Sessions in PHP
- Setup Your Personal Reminder System Using PHP
- Create a IP-Country Database Using PERL and ...
- Developing a Dynamic Document Search in PHP ...

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 

Developer Shed Affiliates


© 2003-2019 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials