Day 34 MySQL select

day34思维导图

Day 34 MySQL select

Querying data

Introduction to MySQL `SELECT` statement

The SELECT statement allows you to read data from one or more tables. To write a SELECT statement in MySQL, you follow this syntax:

SELECT select_list
FROM table_name;

First, you start with the SELECT keyword. The keyword has a special meaning in MySQL. In this case, SELECT instructs MySQL to retrieve data.

Next, you have space and then a list of columns or expressions that you want to show in the result.

Then, you have the FROM keyword, space and the name of the table.

Finally, you have a semicolon ; at the end of the statement.

The semicolon ; is the statement delimiter. It specifies the end of a statement. If you have two or more statements, you use the semicolon ; to separate them so that MySQL will execute each statement individually.

In the SELECT statement, the SELECT and FROM are keywords and written in capital letters. Basically, it is just about formatting. The uppercase letters make the keywords stand out.

Since SQL is not a case-sensitive language, you can write the keywords in lowercase e.g., select and from, the code will still run.

It is also important to note that the FROM keyword is on a new line. MySQL doesn’t require this. However, placing the FROM keyword on a new line will make the query easier to read and simpler to maintain.

When evaluating the SELECT statement, MySQL evaluates the FROM clause first and then the SELECT clause:

Notes about `SELECT` star

The SELECT * is often called “select star” or “select all” since you select all data from a table.

It is a good practice to use the SELECT * for the ad-hoc queries only. If you embed the SELECT statement in the code such as PHP, Java, Python, Node.js, you should explicitly specify the name of columns from which you want to get data because of the following reasons:

The SELECT * returns data from the columns that you may not use. It produces unnecessary I/O disk and network traffic between the MySQL database server and application.
When you explicitly specify the column names, the result set is predictable and easier to manage. However, if you use the SELECT * and someone changes the table by adding more columns, you will end up with a result set that is different from the one that you expected.
Using the SELECT * may expose sensitive information to unauthorized users.

Sorting data

Introduction to MySQL `ORDER BY` clause

When you use the SELECT statement to query data from a table, the result set is not sorted. It means that the rows in the result set can be in any order.

To sort the result set, you add the ORDER BY clause to the SELECT statement. The following illustrates the syntax of the ORDER BY clause:

SELECT 
   select_list
FROM 
   table_name
ORDER BY 
   column1 [ASC|DESC], 
   column2 [ASC|DESC],
   ...;Code language: SQL (Structured Query Language) (sql)

In this syntax, you specify the one or more columns which you want to sort after the ORDER BY clause.

The ASC stands for ascending and the DESC stands for descending. You use ASC to sort the result set in ascending order and DESC to sort the result set in descending order.

This ORDER BY clause sorts the result set in ascending order:

ORDER BY column1 ASC;Code language: SQL (Structured Query Language) (sql)

And this ORDER BY clause sorts the result set in descending order:

ORDER BY column1 DESC;Code language: SQL (Structured Query Language) (sql)

By default, the ORDER BY clause uses ASC if you don’t explicitly specify any option.

Therefore, the following clauses are equivalent:

ORDER BY column1 ASC;Code language: SQL (Structured Query Language) (sql)

and

ORDER BY column1;Code language: SQL (Structured Query Language) (sql)

If you want to sort the result set by multiple columns, you specify a comma-separated list of columns in the ORDER BY clause:

ORDER BY
   column1,
   column2;Code language: SQL (Structured Query Language) (sql)

It is possible to sort the result by a column in ascending order, and then by another column in descending order:

ORDER BY
    column1 ASC,
    column2 DESC;Code language: SQL (Structured Query Language) (sql)

In this case, the ORDER BY clause:

First, sort the result set by the values in the column1 in ascending order.
Then, sort the sorted result set by the values in the column2 in descending order. Note that the order of values in the column1 will not change in this step, only the order of values in the column2 changes.

Note that the ORDER BY clause is always evaluated after the FROM and SELECT clause.

Filtering data

Introduction to MySQL `WHERE` clause

The WHERE clause allows you to specify a search condition for the rows returned by a query. The following shows the syntax of the WHERE clause:

SELECT 
    select_list
FROM
    table_name
WHERE
    search_condition;Code language: SQL (Structured Query Language) (sql)

The search_condition is a combination of one or more predicates using the logical operator AND, OR and NOT.

In MySQL, a predicate is a Boolean expression that evaluates to TRUE, FALSE, or UNKNOWN.

Any row from the table_name that causes the search_condition to evaluate to TRUE will be included in the final result set.

Besides the SELECT statement, you can use the WHERE clause in the UPDATE or DELETE statement to specify which rows to update or delete.

In the SELECT statement, the WHERE clause is evaluated after the FROM clause and before the SELECT clause.

Introduction to MySQL `AND` operator

The AND operator is a logical operator that combines two or more Boolean expressions and returns true only if both expressions evaluate to true. The AND operator returns false if one of the two expressions evaluate to false.

Here is the syntax of the AND operator:

boolean_expression_1 AND boolean_expression_2Code language: SQL (Structured Query Language) (sql)

The following table illustrates the results of the AND operator when combining true, false, and null.

	TRUE	FALSE	NULL
TRUE	TRUE	FALSE	NULL
FALSE	FALSE	FALSE	FALSE
NULL	NULL	FALSE	NULL

The AND operator is often used in the WHERE clause of the SELECT, UPDATE, DELETE statement to form a condition. The AND operator is also used in join conditions of the INNER JOIN and LEFT JOIN clauses.

When evaluating an expression that has the AND operator, MySQL stops evaluating the remaining parts of the expression whenever it can determine the result. This function is called short-circuit evaluation.

Introduction to the MySQL `OR` operator

The MySQL OR operator combines two Boolean expressions and returns true when either condition is true.

The following illustrates the syntax of the OR operator.

boolean_expression_1 OR boolean_expression_2Code language: SQL (Structured Query Language) (sql)

Both boolean_expression_1 and boolean_expression_2 are Boolean expressions that return true, false, or NULL.

The following table shows the result of the OR operator.

	TRUE	FALSE	NULL
TRUE	TRUE	TRUE	TRUE
FALSE	TRUE	FALSE	NULL
NULL	TRUE	NULL	NULL

MySQL `OR` short-circuit evaluation

MySQL uses short-circuit evaluation for the OR operator. In other words, MySQL stops evaluating the remaining parts of the statement when it can determine the result.

Operator precedence

When you use more than one logical operator in an expression, MySQL always evaluates the OR operators after the AND operators. This is called operator precedence which determines the order of evaluation of the operators. MySQL evaluates the operator with higher precedence first.

Introduction to the MySQL `IN` Operator

The IN operator allows you to determine if a specified value matches any value in a set of values or returned by a subquery.

The following illustrates the syntax of the IN operator:

SELECT 
    column1,column2,...
FROM
    table_name
WHERE 
    (expr|column_1) IN ('value1','value2',...);Code language: SQL (Structured Query Language) (sql)

Let’s examine the query in more detail:

Use a column or an expression ( expr ) with the IN operator in the WHERE clause.
Separate the values in the list by commas (,).

The IN operator returns 1 if the value of the column_1 or the result of the expr expression is equal to any value in the list, otherwise, it returns 0.

When the values in the list are all constants, MySQL performs the following steps:

First, evaluate the values based on the type of the column_1 or result of the expr expression.
Second, sort the values.
Third, search for the value using the binary search algorithm. Therefore, a query that uses the IN operator with a list of constants performs very fast.

Note that if the expr or any value in the list is NULL, the IN operator returns NULL.

You can combine the IN operator with the NOT operator to determine if a value does not match any value in a list or a subquery. And you can also use the IN operator in the WHERE clause of other statements such as UPDATE, and DELETE

Introduction to MySQL `BETWEEN` Operator

The BETWEEN operator is a logical operator that allows you to specify whether a value in a range or not. The BETWEEN operator is often used in the WHERE clause of the SELECT, UPDATE, and DELETE statements.

The following illustrates the syntax of the BETWEEN operator:

expr [NOT] BETWEEN begin_expr AND end_expr;Code language: SQL (Structured Query Language) (sql)

The expr is the expression to test in the range defined by begin_expr and end_expr. All three expressions: expr, begin_expr, and end_expr must have the same data type.

The BETWEEN operator returns true if the value of the expr is greater than or equal to (>=) the value of begin_expr and less than or equal to (<= ) the value of the end_expr, otherwise, it returns zero.

The NOT BETWEEN returns true if the value of expr is less than (<) the value of the begin_expr or greater than (>)the value of the value of end_expr, otherwise, it returns 0.

If any expression is NULL, the BETWEEN operator returns NULL .

In case you want to specify an exclusive range, you can use the greater than (>) and less than (<) operators instead.

Introduction to MySQL `LIKE` operator

The LIKE operator is a logical operator that tests whether a string contains a specified pattern or not. Here is the syntax of the LIKE operator:

expression LIKE pattern ESCAPE escape_characterCode language: SQL (Structured Query Language) (sql)

The LIKE operator is used in the WHERE clause of the SELECT , DELETE, and UPDATE statements to filter data based on patterns.

MySQL provides two wildcard characters for constructing patterns: percentage % and underscore _ .

The percentage ( % ) wildcard matches any string of zero or more characters.
The underscore ( _ ) wildcard matches any single character.

For example, s% matches any string starts with the character s such as sun and six. The se_ matches any string starts with se and is followed by any character such as see and sea.

Introduction to MySQL `LIMIT` clause

The LIMIT clause is used in the SELECT statement to constrain the number of rows to return. The LIMIT clause accepts one or two arguments. The values of both arguments must be zero or positive integers.

The following illustrates the LIMIT clause syntax with two arguments:

SELECT 
    select_list
FROM
    table_name
LIMIT [offset,] row_count;Code language: SQL (Structured Query Language) (sql)

In this syntax:

The offset specifies the offset of the first row to return. The offset of the first row is 0, not 1.
The row_count specifies the maximum number of rows to return.

When you use the LIMIT clause with one argument, MySQL will use this argument to determine the maximum number of rows to return from the first row of the result set.

Therefore, these two clauses are equivalent:

LIMIT row_count;Code language: SQL (Structured Query Language) (sql)

And

LIMIT 0 , row_count;

In addition to the above syntax, MySQL provides the following alternative LIMIT clause for compatibility with PostgreSQL.

LIMIT row_count OFFSET offsetCode language: SQL (Structured Query Language) (sql)

`LIMIT` and `ORDER BY` clauses

The SELECT statement without an ORDER BY clause returns rows in an unspecified order. It means that rows can be in any order. When you apply the LIMIT clause to this unordered result set, you will not know which rows the query will return.

For example, you may want to get fifth through tenth rows, but fifth through tenth in what order? The order of rows is unknown unless you specify the ORDER BY clause.

Therefore, it is a good practice to always use the LIMIT clause with the ORDER BY clause to constraint the result rows in unique order.

SELECT select_list
FROM table_name
ORDER BY order_expression
LIMIT offset, row_count;

Introduction to MySQL `IS NULL` operator

To test whether a value is NULL or not, you use the IS NULL operator.

Here is the basic syntax of the IS NULL operator:

value IS NULLCode language: SQL (Structured Query Language) (sql)

If the value is NULL, the expression returns true. Otherwise, it returns false.

Note that MySQL does not have a built-in BOOLEAN type. It uses the TINYINT(1) to represent the BOOLEAN values i.e., true means 1 and false means 0.

Because the IS NULL is a comparison operator, you can use it anywhere that an operator can be used e.g., in the SELECT or WHERE clause.

Joining tables

MySQL supports two kinds of aliases which are known as column alias and table alias.

MySQL alias for columns

Sometimes, column names are so technical that make the query’s output very difficult to understand. To give a column a descriptive name, you can use a column alias.

The following statement illustrates how to use the column alias:

SELECT 
   [column_1 | expression] AS descriptive_name
FROM table_name;Code language: SQL (Structured Query Language) (sql)

To assign an alias to a column, you use the AS keyword followed by the alias. If the alias contains spaces, you must quote it as the following:

SELECT 
   [column_1 | expression] AS `descriptive name`
FROM 
   table_name;Code language: SQL (Structured Query Language) (sql)

Because the AS keyword is optional, you can omit it in the statement. Note that you can also give an expression an alias.

Introduction to MySQL join clauses

A relational database consists of multiple related tables linking together using common columns which are known as foreign key columns. Because of this, data in each table is incomplete from the business perspective.

For example, in the sample database, we have the orders and orderdetails tables that are linked using the orderNumber column:

To get complete orders’ information, you need to query data from both orders and orderdetails tables.

That’s why joins come into the play.

A join is a method of linking data between one (self-join) or more tables based on values of the common column between the tables.

MySQL supports the following types of joins:

To join tables, you use the cross join, inner join, left join, or right join clause for the corresponding type of join. The join clause is used in the SELECT statement appeared after the FROM clause.

Note that MySQL hasn’t supported the FULL OUTER JOIN yet.