Querying the Database in MySQL

By: Sathya Narayana Emailed: 1768 times Printed: 2515 times    

Latest comments
By: rohit kumar - how this program is work
By: Kirti - Hi..thx for the hadoop in
By: Spijker - I have altered the code a
By: ali mohammed - why we use the java in ne
By: ali mohammed - why we use the java in ne
By: mizhelle - when I exported the data
By: raul - no output as well, i'm ge
By: Rajesh - thanx very much...
By: Suindu De - Suppose we are executing

You'll now have a closer look at the command you ran:

mysql> SELECT id, title, price, status FROM book;

Running this command produces output something like this:

+----+-------------------------------------------+-------+--------+
| id | title                                     | price | status |
+----+-------------------------------------------+-------+--------+
| 1 | Lord of the Things                         | 9.99  | P      |
| 1 | Mr Bunny's Guide to JDO                    | 14.99 | P      |
| 1 | Parachuting for You and Your Kangaroo      | 19.99 | P      |
+----+-------------------------------------------+-------+--------+
3 rows in set (0.04 sec)

The command asks for the columns called id, title, price, and status for all rows in the Book table. The general form for a SELECT statement that retrieves all of the rows in the table is as follows:

> SELECT Column1Name, .... , ColumnXName FROM TableName;

There's also a special form that returns all columns from a table, without you having to type the name for every column:

> SELECT * FROM TableName;

If you run this, you'll see that other columns for which you didn't specify a value are set to NULL.

Note 

As a rule, you should avoid using SELECT * FROM except for testing or debugging purposes, unless you really do need every column from the table. Performance will be enhanced if you request only those fields you actually intend to use. Additionally, SELECT * offers no control over the order of the returned fields because they're returned in the order in which they were declared in the CREATE TABLE statement.

When retrieving data with a SELECT query, you can order the returned rows by adding an ORDER BY clause to the command. The ORDER BY statement is followed by the column that you want to sort on, and finally you specify whether to order highest to lowest (a descending sort as indicated by DESC) or lowest to highest (an ascending sort, as indicated by ASC). ASC is the default sort, so it's assumed if neither DESC nor ASC is specified. For instance, the following command displays books in order of price, highest price first:

mysql> SELECT price, title FROM book
          -> ORDER BY price DESC;

You'll see something like this:

+-------+-------------------------------------------+
| price | title                                     |
+-------+-------------------------------------------+
| 9.99  | Lord of the Things                        |
| 14.99 | Mr Bunny's Guide to JDO                   |
| 19.99 | Parachuting for You and Your Kangaroo     |
+-------+-------------------------------------------+

You'll see that the column order has changed because of how you've ordered the column names in the SELECT statement.


MySQL Home | All MySQL Tutorials | Latest MySQL Tutorials

Sponsored Links

If this tutorial doesn't answer your question, or you have a specific question, just ask an expert here. Post your question to get a direct answer.



Bookmark and Share

Comments(0)


Be the first one to add a comment

Your name (required):


Your email(required, will not be shown to the public):


Your sites URL (optional):


Your comments:



More Tutorials by Sathya Narayana
Querying Data with the JSTL in JSP
Deleting Data in MySQL
Modifying data and using WHERE clause in MySQL
Querying the Database in MySQL
Inserting Data into Tables in MySQL
Changing the Structure of an Existing Table in MySQL
The Exception Object in JSP
The Application Object in JSP
The Session Object in JSP
The Out Object in JSP
The Response Object in JSP
The Request Object in JSP
Tag Libraries in JSP
Scriptlets and Expressions in JSP
Declarations in JSP

More Tutorials in MySQL
Windows cannot access the specified device, path or file. You may not have the appropriate permissions to access them.
ERROR 1251: Client does not support authentication protocol requested by server; consider upgrading MySQL client
Changing the Structure of an Existing Table in MySQL
Inserting Data into Tables in MySQL
Querying the Database in MySQL
Modifying data and using WHERE clause in MySQL
Deleting Data in MySQL
What is SQL Injection
MySQL Strengths and Weaknesses
Table __________ is marked as crashed and should be repaired.

More Latest News
Most Viewed Articles (in MySQL )
What is SQL Injection
MySQL Strengths and Weaknesses
Changing the Structure of an Existing Table in MySQL
Inserting Data into Tables in MySQL
Querying the Database in MySQL
ERROR 1251: Client does not support authentication protocol requested by server; consider upgrading MySQL client
Windows cannot access the specified device, path or file. You may not have the appropriate permissions to access them.
Table __________ is marked as crashed and should be repaired.
Modifying data and using WHERE clause in MySQL
Deleting Data in MySQL
Most Emailed Articles (in MySQL)
Windows cannot access the specified device, path or file. You may not have the appropriate permissions to access them.
Table __________ is marked as crashed and should be repaired.
Changing the Structure of an Existing Table in MySQL
What is SQL Injection
MySQL Strengths and Weaknesses
Inserting Data into Tables in MySQL
Querying the Database in MySQL
Modifying data and using WHERE clause in MySQL
Deleting Data in MySQL
ERROR 1251: Client does not support authentication protocol requested by server; consider upgrading MySQL client