In-depth query of SELECT statements (1) (1)

This section describes some advanced functions of SELECT statements.

Column and table alias

Column alias

The selected output columns can be referenced BY the column name, column alias, or column position in the order by and group by clauses. The column position starts from 1.

For example, we retrieve pets and types from the pet table and directly reference the column Name:

Mysql> SELECT name, species FROM pet order by name, species;

The output is:

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

| Name | species |

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

| Bowser | dog |

| Buffy | dog |

| Chirpy | bird |

| Claws | cat |

| Fang | dog |

| Fluffy | cat |

| Puffball | hamster |

| Slim | snake |

| Whistler | bird |

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

Use the column position in the clause:

Mysql> SELECT name, species FROM pet order by 1, 2;

The output of this statement is no different from that above.

Finally, you can name the column:

Mysql> SELECT name AS n, species AS s FROM pet order by n, s;

Note the returned results:

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

| N | s |

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

| Bowser | dog |

| Buffy | dog |

| Chirpy | bird |

| Claws | cat |

| Fang | dog |

| Fluffy | cat |

| Puffball | hamster |

| Slim | snake |

| Whistler | bird |

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

The returned record sequence is not different. However, the column name has changed, and the create table... The SELECT statement makes sense when creating a table.

For example, we want to generate a table from the pet table that includes the name and owner fields, but we want to rename the names of the name and owner fields as animal and child, A very stupid way is to CREATE a TABLE and then input data. If an alias is used, only one SQL statement can solve the problem. It is very simple. The statement we want to use enables CREATE TABLE:

mysql> CREATE TABLE pet1-> SELECT name AS animal,owner AS child-> FROM pet;

Then, retrieve the generated table to see if it reaches the target:

Mysql> SELECT * FROM pet1;

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

| Animal | child |

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

| Fluffy | Harold |

| Claws | Gwen |

| Buffy | Harold |

| Chirpy | Gwen |

| Fang | Benny |

| Boane | Diane |

| Whistler | Gwen |

| Slim | Benny |

| Puffball | Diane |

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

Use column alias in Clause

You can use an alias to reference columns in the group by, order by, or HAVING section. An alias can also be used to get a better name for a column:

mysql> SELECT species,COUNT(*) AS total FROM pet -> GROUP BY species HAVING total>1;

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

| Species | total |

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

| Bird | 2 |

| Cat | 2 |

| Dog | 3 |

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

Note that your ansi SQL does not allow you to reference an alias in a WHERE clause. This is because the column value may not end when the WHERE code is executed. For example, the following query is invalid:

SELECT id, COUNT (*) AS total FROM pet WHERE total> 1 group by species

The following error occurs:

ERROR 1054: Unknown column 'Total' in 'where clause'

The WHERE statement is executed to determine which rows should be included in the group by section, while HAVING is used to determine which rows in the result set should be used only.

Table alias

Aliases can be applied not only to columns, but also to table names. The specific method is similar to the column alias, which is not repeated here.

Column aliases are often used in table join operations. You do not have to have two different tables to execute a join. If you want to compare the records of a table with other records of the same table, it is sometimes useful to link a table to itself. For example, in order to breed a spouse among your pets, you can use pet to associate yourself with a similar type of female:

mysql> SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species -> FROM pet AS p1, pet AS p2 -> WHERE p1.species = p2.species AND p1.sex = "f" AND p2.sex = "m";

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

| Name | sex | species |

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

| Fluffy | f | Claws | m | cat |

| Buffy | f | Fang | m | dog |

| Buffy | f | Bowser | m | dog |

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

In this query, we specify an alias for the table name so that the column can be referenced and the table instance associated with each column reference is more intuitive.