Displaying MySQL Databases

There are many different ways of sorting and displaying database records during a mysql session. If you are not already connected to a mysql session, type the following command ( assuming the mysql user name of root):

 $  mysql -u root -p  Enter password:  *******  mysql> 

When you are in your mysql session (and have chosen a database), you can display all or selected table records, choose which columns are displayed, or choose how records are sorted.

Displaying All or Selected Records

Assuming that the current database is allusers (as shown in the previous examples), type the following command to choose (SELECT) all records (*) from the names table and display them in the order in which they were entered into the database.

 mysql>  SELECT * FROM names;  +-----------+--------+-------------------+------------+-------+--------+ firstname lastnamestreetaddr  city  state zipcode +-----------+--------+-------------------+------------+-------+--------+  Chris Smith 175 Harrison Street Gig Harbor  WA  98999   John Jones 18 Talbot Road NW  Coventry  NJ  54889   Howard Manty 1515 Broadway  New York  NY  10028  +-----------+--------+-------------------+------------+-------+--------+ 

The following command displays all records from the names table that have the lastname column set to Jones. Instead of using lastname, you could search for a value from any column name used in the table.

 mysql>  SELECT * FROM names WHERE lastname = "Jones";  +-----------+---------+------------------+------------+-------+--------+  firstname lastname streetaddr  city  state  zipcode +-----------+---------+------------------+------------+-------+--------+  John Jones 18 Talbot Road NW  Coventry  NJ  54889  +-----------+---------+------------------+------------+-------+--------+ 

Using the OR operator, you can select records that match several different values. In the following command, records that have either Chris or Howard as the firstname are matched and displayed.

  mysql> SELECT * FROM names WHERE firstname = "Chris" OR firstname = "Howard";   +-----------+----------+--------------------+------------+-------+--------+   firstname  lastname  streetaddr  city  state  zipcode   +-----------+----------+--------------------+------------+-------+--------+   Chris  Smith  175 Harrison Street Gig Harbor  WA  98999   Howard  Manty  1515 Broadway  New York  NY  10028   +----------+----------+------------------+-----------+-------+-------+  

To match and display a record based on the value of two columns in a record, you can use the AND operator. In the following command, any record that has Chris as the firstname and Smith as the lastname is matched.

  mysql> SELECT * FROM names WHERE firstname = "Chris" AND lastname = "Smith";   +----------+---------+---------------------+------------+-------+---------+   firstname lastname streetaddr  city  state  zipcode   +----------+---------+---------------------+------------+-------+---------+   Chris  Smith  175 Harrison Street  Gig Harbor  WA  98999   +---------+---------+-------------------+-----------+-------+--------+  

Displaying Selected Columns

You don't need to display every column of data. Instead of using the asterisk (*) shown in the previous examples to match all columns, you can enter a comma-separated list of column names. The following command displays the firstname, lastname, and zipcode records for all of the records in the names table:

 mysql>  SELECT firstname,lastname,zipcode FROM names;  +------------+----------+---------+  firstname  lastname  zipcode  +------------+----------+---------+  Chris  Smith  98999   John  Jones  54889   Howard  Manty  10028  +------------+----------+---------+ 

Likewise, you can sort columns in any order you choose. Type the following command to show the same three columns with the zipcode column displayed first:

 mysql>  SELECT zipcode,firstname,lastname FROM names;  +---------+------------+----------+  zipcode  firstname  lastname  +---------+------------+----------+  98999  Chris  Smith   54889  John  Jones   10028  Howard  Manty  +---------+------------+----------+ 

You can also mix column selection with record selection as shown in the following example:

  mysql> SELECT firstname,lastname,city FROM names WHERE firstname = "Chris";   +------------+----------+------------+   firstname  lastname  city   +------------+----------+------------+   Chris  Smith  Gig Harbor   +-----------+---------+-----------+  

Sorting Data

You can sort records based on the values in any column you choose. For example, using the ORDER BY operator, you can display the records based on the lastname column:

 mysql>  SELECT * FROM names ORDER BY lastname;  +----------+---------+-------------------+------------+-------+--------+  firstnamelastname streetaddr  city  state  zipcode +----------+---------+-------------------+------------+-------+--------+  John Jones 18 Talbot Road NW  Coventry  NJ  54889   Howard Manty 1515 Broadway  New York  NY  10028   Chris Smith 167 Small Road  Gig Harbor  WA  98999  +----------+---------+-------------------+------------+-------+--------+ 

To sort records based on city name, you could use the following command:

 mysql>  SELECT * FROM names ORDER BY city;  +-----------+---------+------------------+------------+-------+--------+  firstname lastname streetaddr  city  state  zipcode +-----------+---------+------------------+------------+-------+--------+  John Jones 18 Talbot Road NW  Coventry  NJ  54889   Chris Smith 167 Small Road  Gig Harbor  WA  98999   Howard Manty 1515 Broadway  New York  NY  10028  +-----------+---------+------------------+------------+-------+--------+ 

Now that you have entered and displayed the database records, you may find that you need to change some of them. The following section describes how to update database records during a mysql session.