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.
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 |lastname|streetaddr | 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 | +----------+---------+---------------------+------------+-------+---------+
You don't need to display every column of data. Instead of using the asterisk (*) shown in 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 | +------------+----------+------------+
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; +----------+---------+-------------------+------------+-------+--------+ | firstname|lastname |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.