Query records in a data table (SELECT) (1)

Unless they are finally retrieved and used to do something, it is no good to put records into the database. This is the purpose of the SELECT statement, that is, to help retrieve data. SELECT is probably the most commonly used statement in SQL, and it is also the most important to use it; using it to SELECT records may be quite complex and may involve comparison between columns in many tables. This section describes the basic functions of a Select statement.

The SELECT statement syntax is as follows:

SELECT selection_list

FROM table_list where to select a row

WHERE primary_constraint

How does group by grouping_columns GROUP results?

HAVING secondary_constraint must meet the second condition

Order by sorting_columns how to sort results

LIMIT on LIMIT count results

Note: All keywords used must be given exactly in the above Order. For example, a HAVING clause must be followed BY the group by clause and before the order by clause.

Everything in the syntax is optional except for the word "SELECT" and the column_list section that describes what you want to retrieve. Some databases also need the FROM clause. MySQL is different. It allows evaluate expressions without referencing any tables.

Common Query

The simplest form of SELECT is to retrieve everything from a table:

Mysql> SELECT * FROM pet;

The result is:

+ ---------- + -------- + --------- + ------ + ------------ +

| Name | owner | species | sex | birth | death |

+ ---------- + -------- + --------- + ------ + ------------ +

| Fluffy | Harold | cat | f | 1993-02-04 | NULL |

| Claws | Gwen | cat | m | 1994-03-17 | NULL |

| Buffy | Harold | dog | f | 1989-05-13 | NULL |

| Chirpy | Gwen | bird | f | 1998-09-11 | NULL |

| Fang | Benny | dog | m | 1990-08-27 | NULL |

| Boane | Diane | dog | m |

| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |

| Slim | Benny | snake | m | 1996-04-29 | NULL |

| Puffball | Diane | hamster | f | © 3-30 | NULL |

+ ---------- + -------- + --------- + ------ + ------------ +

Query specific rows:

You can select only specific rows from your table. For example, if you want to verify your changes to the birth date of Bowser, select a Bowser record like this:

Mysql> SELECT * FROM pet WHERE name = "bow ";

The result is:

+ -------- + ------- + --------- + ------ + ------------ +

| Name | owner | species | sex | birth | death |

+ -------- + ------- + --------- + ------ + ------------ +

| Boane | Diane | dog | m |

+ -------- + ------- + --------- + ------ + ------------ +

You can verify it against the previous example.

Query specific columns

If you do not want to see the entire row of your table, name the columns you are interested in and separate them with commas. For example, if you want to know when your animal was born, select the name and birth columns:

Mysql> SELECT name, birth FROM pet where owner = "Gwen ";

The result is:

+ ---------- + ------------ +

| Name | birth |

+ ---------- + ------------ +

| Claws | 1994-03-17 |

| Chirpy | 1998-09-11 |

| Whistler | 1997-12-09 |

+ ---------- + ------------ +

Perform expression Calculation

Most of the preceding queries have produced output results by retrieving values from the table. MySQL also allows the result of a formula to calculate the value of the output column. Expressions can be simple or complex. The following query calculates the value constant of a simple expression) and the value of a more complex expression involving several Arithmetic Operators and two function calls. For example, calculate the days of Browser life:

Mysql> SELECT death-birth FROM pet WHERE name = "bow ";

The result is:

+ ------------- +

| Death-birth |

+ ------------- +

| 1, 49898 |

+ ------------- +

MySQL allows you to evaluate expressions without referencing any tables. You can also use the following code:

Mysql> select (2 + 3*4.5)/2.5;

The result is:

+ --------------- +

| (2 + 3*4.5)/2.5 |

+ --------------- +

| 1, 6.200 |

+ --------------- +