Walker News

Dummy Guide: Introduce Some Simple MySQL Commands

MySQL is one of the relational database management systems that released to end user under the GNU GPL license. Since the first release that happened on 1995, MySQL has accumulated more than 11 million installations.

Most of the web applications that developed in PHP and supported with a database are using this open source database system, including WordPress that power WalkerNews.net!
Although this blog is “alive” with a MySQL database, I hardly have to deal with MySQL thingy (as my web hosting package doesn’t require me to do so :-).

But, it’s good to know a little of database system, days after days, for personal interest. So, I think it’s good to log down some common MySQL commands for quick reference, especially to those who access MySQL database for the first time :-)

These MySQL commands are executed in Linux command prompt, using the MySQL Monitor program (i.e. mysql). Of course, they works in Windows platforms too :-P
  • To login MySQL server with a MySQL user ID, e.g. walkeradmin (this is not a Linux user ID), and interactively enter password for authentication (as given by the -p command switch):
    mysql -u walkeradmin -p
  • List all databases that are created or administrated by walkeradmin user id in MySQL:
    show databases;
  • Connect to a MySQL database called walkernewsdb (i.e. to access the database):
    use walkernewsdb;
  • Confirm the database you’re currently working on in MySQL. This is useful when you forget or not sure which MySQL database you’re dealing with:
    select database ();
  • Get MySQL to display or show all tables that are created in the database (after connecting to the database, e.g. the use walkernewsdb; command):
    show tables;
  • Get MySQL to display or show the table schema of a table called my_table, such as table columns (Field), column data type (Type), allow null value or not (Null), primary keys (Key), default value, etc.
    describe my_table;
  • If you’re running a WordPress blog and have some of the posts that are not enabled for readers to comment, use this MySQL statement to list all posts where the comment_status is closed.
    select post_title from wp_posts where comment_status != "open";
  • If the MySQL statement output is too long to view / parse on screen, you may write the MySQL statements into a text file and redirect the text file to MySQL Monitor program. For example,
    mysql -u walkeradmin -p < command.mysql | more

    where more is a Linux command to display output one page at a time and the command.mysql text file contains some samples of MySQL statement:
    show databases;
    use walkernewsdb;
    select post_name from wp_posts;
    quit
  • To quit from the MySQL Monitor:
    quit

To check the MySQL database server version number, uptime information, query performance, etc:
mysqladmin version

Alternatively, you could simply run the MySQL Monitor to report MySQL server version:
mysql

To confirm the MySQL database server is alive:
mysqladmin ping

To view MySQL server settings, such as database collation, date-time format, etc:
mysqladmin variables

To access MySQL online documentation, please refer to the official site that publishes complete MySQL documentation. Good luck.

Custom Search

  1. MySQL Dummy Guide: How To Display Long SQL Output In Page By Page Fashion? – Walker News 06-07-08@13:27

    […] posting the earlier MySQL dummy guide, I found another easier way to display long SQL result in page by page fashion (tested in MySQL […]

2017  •  Privacy Policy