Made by Mike_Zhang

1. Abstract

Continue with the previous post where we have created the table and discussed how to manipulate them. Now we can query data inside the table with SQL’s Query Mechanics with SELECT Statements, which can help us to retrieve, join, filter, group and sort data from the table¹.

SELECT Statements have several Clause (parts). There are six commonly used clauses in MySQL:

Clause	Description
`SELECT`	Determines which of all possible columns should be indicated in the query’s result set.
`FROM`	Defines the tables used by the query, with the ability of joining the tables together.
`WHERE`	Filters out unwanted rows from the result set.
`GROUP BY`	Groups rows together based on the common value of columns.
`HAVING`	Filters out unwanted groups from the result set.
`ORDER BY`	Sorts results set using either raw column data or expressions of them.

2. `SELECT` Clause

SELECT Clause determines which of all possible columns should be indicated in the query’s result set.

2.1 Basic Syntax

Select all columns from a table (indicated by *):
1
2
SELECT * FROM [table_name];
Select specific columns from a table (explicitly indicate names):

1 2	`SELECT [column_name_n], ..., [column_name_n] FROM [table_name];`

[Example]

mysql> SELECT *
    -> FROM language;
+-------------+----------+---------------------+
| language_id | name     | last_update         |
+-------------+----------+---------------------+
|           1 | English  | 2006-02-15 05:02:19 |
|           2 | Italian  | 2006-02-15 05:02:19 |
|           3 | Japanese | 2006-02-15 05:02:19 |
|           4 | Mandarin | 2006-02-15 05:02:19 |
|           5 | French   | 2006-02-15 05:02:19 |
|           6 | German   | 2006-02-15 05:02:19 |
+-------------+----------+---------------------+
6 rows in set (0.00 sec)

mysql> SELECT language_id, name
    -> FROM language;
+-------------+----------+
| language_id | name     |
+-------------+----------+
|           1 | English  |
|           2 | Italian  |
|           3 | Japanese |
|           4 | Mandarin |
|           5 | French   |
|           6 | German   |
+-------------+----------+
6 rows in set (0.00 sec)

2.2 Additional Columns

The SELECT clause’s result set is not limited by the only columns in the table indicated by FROM. Users are able to add additional columns into the result set without affecting data in the real table.

Four types of additional columns added by users:

Literals: numbers, strings,…;
Expressions: such as language.language_id + 1;
Build-in function calls: such as upper();
User-defined function calls;

[Example]

mysql> SELECT language_id, /* column in the table */
    -> 'GOOD' AS level, /* Literals */
    -> language_id+1 AS id_plus_one, /* Expressions */
    -> upper(name) AS upper_name /* Build-in function calls */
    -> FROM language;
+-------------+-------+-------------+------------+
| language_id | level | id_plus_one | upper_name |
+-------------+-------+-------------+------------+
|           1 | GOOD  |           2 | ENGLISH    |
|           2 | GOOD  |           3 | ITALIAN    |
|           3 | GOOD  |           4 | JAPANESE   |
|           4 | GOOD  |           5 | MANDARIN   |
|           5 | GOOD  |           6 | FRENCH     |
|           6 | GOOD  |           7 | GERMAN     |
+-------------+-------+-------------+------------+
6 rows in set (0.01 sec)

2.3 Column Aliases

SQL adds label to each column from the table in the result set, such as language_id.
However, users can add their own label to additional columns, which are Column Aliases.

Syntax:

1	`[statements] AS [column_alias]`

1	`[statements] [column_alias]`

where AS keyword is optional, which is used for easy reading and understanding

[Example]

1
2
3

'GOOD' AS level, /* Literals */
language_id+1 AS id_plus_one, /* Expressions */
upper(name) AS upper_name /* Build-in function calls */

2.4 Distinct Data

To get the distinct set of result, no more duplicated data, like ID.

Syntax:

Just add DISTINCT keyword after the SELECT keyword.

1	`SELECT DISTINCT ...`

[Example]

WITHOUT DISTINCT keyword:

mysql> SELECT actor_id
    -> FROM film_actor;
+----------+
| actor_id |
+----------+
|        1 |
|       10 |
|       20 |
|       30 |
|       40 |
...
|      140 |
|      142 |
|      155 |
|      166 |
|      178 |
+----------+
5462 rows in set (0.01 sec)

WITH DISTINCT keyword:

mysql> SELECT DISTINCT actor_id FROM film_actor;
+----------+
| actor_id |
+----------+
|        1 |
|        2 |
|        3 |
|        4 |
|        5 |
...
|      196 |
|      197 |
|      198 |
|      199 |
|      200 |
+----------+
200 rows in set (0.01 sec)

Remainder:

The opposite of DISTINCT is ALL keyword, which is the default and NO need to be explicitly added into statements.
Using DISTINCT to get the result requires sorting the result set, which is time consuming. Users should rethink before using it.

3. `FROM` Clause

FROM Clause defines the tables used by the query, with the ability of joining the tables together.

3.1 Basic Syntax

1 2	`SELECT [column_name_n], ..., [column_name_n] FROM [table_name];`

[Example]

mysql> SELECT *
    -> FROM language;
+-------------+----------+---------------------+
| language_id | name     | last_update         |
+-------------+----------+---------------------+
|           1 | English  | 2006-02-15 05:02:19 |
|           2 | Italian  | 2006-02-15 05:02:19 |
|           3 | Japanese | 2006-02-15 05:02:19 |
|           4 | Mandarin | 2006-02-15 05:02:19 |
|           5 | French   | 2006-02-15 05:02:19 |
|           6 | German   | 2006-02-15 05:02:19 |
+-------------+----------+---------------------+

3.2 Table Types

Four types of tables can be used in the FROM clause:

Permanent table:
- Commonly used table created using CREATE TABLE statement.
Derived table:
- Generated by subquery (a query inside another query) within the FROM clause, to generate a derived table and visible to all other query clauses in FROM clause:
  1
  2
  3
  4
  5
  SELECT d.name FROM (SELECT name FROM customer ) AS d;
- Where (SELECT name FROM customer) is a derived table with alias d.
- Columns in the derived table can be referenced by table alias, such as d.name.
Temporary table:
- It is a volatile, temporary table, like a local variable, which looks like a permanent table. But will disappear when database session closed.
- Syntax:
  1
  2
  3
  4
  CREATE TEMPORARY TABLE [table_name]( [column_name] [data_type] [column_constraint], ... )
- Then can be manipulated and queried like a permanent table.
Views / Virtual table:
- No data associated with a virtual table, only references.
- Syntax:
  1
  2
  3
  CREATE VIEW [view_name] AS SELECT [column_name], ..., [column_name] FROM [table_name];
  - After a view is created, NO additional data generated and stored in the database, only references.
- View can be used by the FROM clause:
  1
  2
  SELECT * FROM [view_name];
  - WThe final real query to the views will NOT be generated until it is executed by a query.
- Usage:
  - The view can hide columns from users, as well as simplify complex database design.

3.3 Table Links

More than one tables can appear in the FROM clause, then these tables can be linked/joined together based on the relational design.

Syntax:

SELECT [column_name_n], ..., [column_name_n]
FROM [table_name_1]
    INNER JOIN [table_name_2]
        ON [conditions]
WHERE [conditions];

[Example]

mysql> SELECT customer.first_name, customer.last_name,
    ->      time(rental.rental_date) rental_time
    -> FROM customer
    ->      INNER JOIN rental
    ->      ON customer.customer_id = rental.customer_id
    -> WHERE date(rental.rental_date) = '2005-06-14';
+------------+-----------+-------------+
| first_name | last_name | rental_time |
+------------+-----------+-------------+
| JEFFERY    | PINSON    | 22:53:33    |
| ELMER      | NOE       | 22:55:13    |
| MINNIE     | ROMERO    | 23:00:34    |
| MIRIAM     | MCKINNEY  | 23:07:08    |
| DANIEL     | CABRAL    | 23:09:38    |
| TERRANCE   | ROUSH     | 23:12:46    |
| JOYCE      | EDWARDS   | 23:16:26    |
| GWENDOLYN  | MAY       | 23:16:27    |
| CATHERINE  | CAMPBELL  | 23:17:03    |
| MATTHEW    | MAHAN     | 23:25:58    |
| HERMAN     | DEVORE    | 23:35:09    |
| AMBER      | DIXON     | 23:42:56    |
| TERRENCE   | GUNDERSON | 23:47:35    |
| SONIA      | GREGORY   | 23:50:11    |
| CHARLES    | KOWALSKI  | 23:54:34    |
| JEANETTE   | GREENE    | 23:54:46    |
+------------+-----------+-------------+
16 rows in set (0.02 sec)

Where

Table customer and rental are joined together within thr FROM clause.
The join condition is ON customer.customer_id = rental.customer_id for the primary key.

3.4 Table Aliases

Two ways to reference a table and its columns outside the FROM clause:

Using the entire table name
- such as customer.customer_id, or
Assign tables aliases, then use the alias as reference name:
- FROM customer AS c or FROM customer c (Assign table alias as c)
- c.customer_id (Reference the column customer_id of the table c)

4. `WHERE` Clause

WHERE clause filters out unwanted rows from the result set.

Syntax:

1
2
3

SELECT [column_name_n], ..., [column_name_n]
FROM [table_name]
WHERE [conditions];

Where

conditions is a logical expression that combines and, or, and not operators as well as their statements.
Separate groups of conditions using parentheses.

[Example]

mysql> SELECT title 
    -> FROM film 
    -> WHERE rating = 'R' AND rental_duration = 7;
+--------------------+
| title              |
+--------------------+
| BROOKLYN DESERT    |
| CANYON STOCK       |
| DINOSAUR SECRETARY |
| ESCAPE METROPOLIS  |
| FLIGHT LIES        |
...
| SPIRIT FLINTSTONES |
| STORY SIDE         |
| UNFAITHFUL KILL    |
| WASH HEAVENLY      |
| WORKER TARZAN      |
+--------------------+
33 rows in set (0.00 sec)

5. `GROUP BY` and `HAVING` Clause

GROUP BY Clause groups rows together based on the common value of columns.
HAVING Clause filters out unwanted groups from the result set.

Syntax:

1
2
3

...
GROUP BY [column_name_1], .., [column_name_n]
HAVING [conditions];

[Example]

mysql> SELECT c.first_name, c.last_name, count(*)
    -> FROM customer c
    ->      INNER JOIN rental r
    ->      ON c.customer_id = r.customer_id
    -> GROUP BY c.first_name, c.last_name
    -> HAVING count(*) >= 40;
+------------+-----------+----------+
| first_name | last_name | count(*) |
+------------+-----------+----------+
| TAMMY      | SANDERS   |       41 |
| CLARA      | SHAW      |       42 |
| ELEANOR    | HUNT      |       46 |
| SUE        | PETERS    |       40 |
| MARCIA     | DEAN      |       42 |
| WESLEY     | BULL      |       40 |
| KARL       | SEAL      |       45 |
+------------+-----------+----------+
7 rows in set (0.03 sec)

6. `ORDER BY` Clause

ORDER BY Clause sorts results set using either raw column data or expressions of them.

6.1 Syntax

1
2
3

SELECT 
...
ORDER BY [column_name_1], .., [column_name_n] ASC/DESC;

Where

[column_name_1], .., [column_name_n] is the sorting criteria, can be extended to more than one condition;
ASC/DESC is the sort order:
- ASC is ascending order, default;
- DESC is descending order.

[Example]

mysql> SELECT * 
    -> FROM language 
    -> ORDER BY name;
+-------------+----------+---------------------+
| language_id | name     | last_update         |
+-------------+----------+---------------------+
|           1 | English  | 2006-02-15 05:02:19 |
|           5 | French   | 2006-02-15 05:02:19 |
|           6 | German   | 2006-02-15 05:02:19 |
|           2 | Italian  | 2006-02-15 05:02:19 |
|           3 | Japanese | 2006-02-15 05:02:19 |
|           4 | Mandarin | 2006-02-15 05:02:19 |
+-------------+----------+---------------------+
6 rows in set (0.00 sec)

mysql> SELECT * 
    -> FROM language 
    -> ORDER BY name DESC;
+-------------+----------+---------------------+
| language_id | name     | last_update         |
+-------------+----------+---------------------+
|           4 | Mandarin | 2006-02-15 05:02:19 |
|           3 | Japanese | 2006-02-15 05:02:19 |
|           2 | Italian  | 2006-02-15 05:02:19 |
|           6 | German   | 2006-02-15 05:02:19 |
|           5 | French   | 2006-02-15 05:02:19 |
|           1 | English  | 2006-02-15 05:02:19 |
+-------------+----------+---------------------+
6 rows in set (0.00 sec)

6.2 Sort via Column Position

Rather than using column names, the target column can be indicated by its position in SELECT clause.

Syntax:

1
2
3

SELECT [column_name_1], .., [column_name_n]
...
ORDER BY 1,...,n ASC/DESC;

[Example]

mysql> SELECT language_id, name 
    -> FROM language 
    -> ORDER BY 2 DESC;
+-------------+----------+
| language_id | name     |
+-------------+----------+
|           4 | Mandarin |
|           3 | Japanese |
|           2 | Italian  |
|           6 | German   |
|           5 | French   |
|           1 | English  |
+-------------+----------+
6 rows in set (0.00 sec)

Where

2 refers to the second column in the SELECT language_id, name, which is name.

But it is NOT recommended to use this method because columns in SELECT statement may be changed frequently, so do their positions.

References

¹. A. Beaulieu, LEARNING SQL: master sql fundamentals. S.L.: O’reilly Media, Inc, Usa, 2020. ↩

‌

Outro

This post has just introduced the Query Primer in SQL. More details will be discussed in the following posts. See you next time!

初次接触SQL，相关的知识会继续学习，继续更新.
最后，希望大家一起交流，分享，指出问题，谢谢！

原创文章，转载请标明出处
Made by Mike_Zhang

感谢你的支持

Programming > SQL

#学习 #SQL

SQL Learning 2 - Query Introduction

https://ultrafish.io/post/sql-learning-2/

Author

Mike_Zhang

Posted on

August 11, 2022

Licensed under

来港第三年 - 挑战与机遇 Previous

SQL Learning 1 - Intro, Data Types & Table Next

SQL Learning 2 - Query Introduction

1. Abstract

2. SELECT Clause

2.1 Basic Syntax

2.2 Additional Columns

2.3 Column Aliases

2.4 Distinct Data

3. FROM Clause

3.1 Basic Syntax

3.2 Table Types

3.3 Table Links

3.4 Table Aliases

4. WHERE Clause

5. GROUP BY and HAVING Clause

6. ORDER BY Clause

6.1 Syntax

6.2 Sort via Column Position

References

‌

Outro

2. `SELECT` Clause

3. `FROM` Clause

4. `WHERE` Clause

5. `GROUP BY` and `HAVING` Clause

6. `ORDER BY` Clause