Viewing Your New MySQL Databases -CodeIdol

Viewing Your New MySQL Databases

A number of commands can provide information about your newly created database. Here are some examples:

Log in as the database user: It is best to do all your database testing as the MySQL user you want the application to eventually use. This will make your testing mimic the actions of the application and results in better testing in a more production-like environment than using the "root" account.
```
     [root@bigboy tmp]# mysql -u mysqluser -p salesdata
```
List all your MySQL databases: The show databases command gives you a list of all your available MySQL databases. In the example, you can see that the salesdata database has been successfully created:

     mysql> show databases;
     +-----------+
     | Database  |
     +-----------+
     | salesdata |
     +-----------+
     1 row in set (0.00 sec)

     mysql>

Listing the Data Tables in Your MySQL Database

The show tables command gives you a list of all the tables in your MySQL database, but you have to use the use command first to tell MySQL to which database it should apply the show tables command.

The example uses the salesdata database; notice that it has a table named test.

     mysql> use salesdata;
     Reading table information for completion of table and column names
     You can turn off this feature to get a quicker startup with -A

     Database changed
     mysql> show tables;
     +---------------------+
     | Tables_in_salesdata |
     +---------------------+
     | test                |
     +---------------------+
     1 row in set (0.00 sec)

     mysql>

Viewing Your MySQL Database's Table Structure

The describe command gives you a list of all the data fields used in your database table. In the example, you can see that the table named test in the salesdata database keeps track of four fields: name, description, num, and date_modified.

     mysql> describe test;
     +---------------+--------------+------+-----+------------+------------
     ----+
     | Field         | Type         | Null | Key | Default    | Extra
     |
     +---------------+--------------+------+-----+------------+------------
     ----+
     | num           | int(11)      |      | PRI | NULL       |
     auto_increment |
     | date_modified | date         |      | MUL | 0000-00-00 |
     |
     | name          | varchar(50)  |      | MUL |            |
     |
     | description   | varchar(75)  | YES  |     | NULL       |
     |
     +---------------+--------------+------+-----+------------+------------
     ----+
     6 rows in set (0.00 sec)

     mysql>

Viewing the Contents of a Table

You can view all the data contained in the table named test by using the select command. In this example, you want to see all the data contained in the very first row in the table.

     mysql> select * from test limit 1;

With a brand new database this will give a blank listing, but once the application starts and you enter data, you may want to run this command again as a rudimentary database sanity check.

July 9, 2008, 4:45 p.m.posted by whitehat

Viewing Your New MySQL Databases