Database and MySQL Database Management System? (Part 2)

Tram Ho

In the previous article, I have introduced to you the basics of database, how to build a DB for web projects and how to deploy DB on MySQL database management system.
Today, I will introduce you to querying data in MySQL

overview

The SELECT keyword

  • Allows viewing of partial data of a table by listing column names after SELECT keyword -> Get table columns to display
  • To select all columns in a table it is possible to use an asterisk instead of listing all column names after the SELECT keyword.

With the DISTINCT keyword, it is possible to remove duplicate data from the SELECT statement.

Derived Attribute

  • SQL provides the ability to create deductive properties in the returned result table using operators and functions based on built-in properties.
  • The column name of the inference attribute is system dependent, but an alias can be assigned as the column name.

WHERE clause

The WHERE clause of the SELECT statement allows you to select specific rows that match the search condition or criteria. Use the WHERE clause to filter records based on a condition
1. Match conditions with the AND and OR operators

2. IS NULL: find undefined values
For fields that have not yet been entered (treats the value as undefined), SQL treats that value as NULL. To check if a field is NULL, instead of using the = comparison, SQL uses the is NULL operator.

3. IN Operator
The IN operator allows you to select a matching value from a set of values. The syntax to use is as follows:

The columns in the WHERE clause need not appear in the selected column list, but it must be one column in the table.
If the list contains more than one value, each item is separated by commas.
Alternatively, the NOT operator associated with the IN operator can be used for negative purposes
4. BETWEEN operator
BETWEEN allows to get values ​​in a specific range. It must be used in the WHERE clause

5. LIKE Operator
LIKE allows searching for information based on a character comparison (‘like’).
LIKE is commonly used with the SELECT statement in the WHERE clause. MySQL provides two wildcard characters for use with LIKE,% and _.

  • The percentage (%) wildcard represents any string that may contain zero or more characters
  • Underline (_) represents only one character.

Limit the number of results with LIMIT

MySQL supports a feature called LIMIT that allows restricting records returned with the SELECT statement. Assuming we have a database table with 10,000 records and want to get the first N records, we can use the following query:

LIMIT also allows retrieving a certain number of records from a certain location:

In the above query, S is the starting point for indexing. MySQL determines that the first recorded position starts with 0; N is the number of records you want to select.

ORDER BY clause

  • The ORDER BY clause allows the results to be ordered on one or more columns in the query results in ascending or descending order.
  • To sort results in ascending order, use ASC ; Descending is DESC . By default, ORDER BY will order the results in ascending order.

GROUP BY clause

  • The GROUP BY clause is used to combine records with the same value in one or more columns, into a collection.
  • GROUP BY, if present, must be preceded by a WHERE or FROM clause.
  • Following the GROUP BY keyword is a comma-separated list of expressions.

HAVING BY Clause

  • HAVING specifies a filter condition on data that is a group of records or the result of a group function implementation.
  • HAVING is often used in conjunction with GROUP BY, then the filter condition is only applied on columns that appear in the GROUP BY clause.
    Note that HAVING applies to groups of records, while WHERE applies to individual records

JOIN table join

  • In fact, we need a lot of queries that require information from many different tables of data
  • Combining data tables to create an inference table is called a join.
  • We will be familiar with the join operation to query data from multiple tables: INNER JOIN, LEFT JOIN, SELF JOIN

INNER JOIN

The INNER JOIN, also known as an inner join, is an optional part of the SELECT statement. It appears immediately after the FROM clause.
Before using the INNER JOIN, hai clearly defines the following criteria:

  1. First, you need to define the tables that you want to associate with the main table. The main table appears in the FROM clause. The table that wants to join the main table must appear after the INNER JOIN keyword
  2. Second, it is necessary to define the join condition. The join condition appears after the keyword ON. The join condition is the rule for finding matching records in the tables and joining them together.
    The INNER JOIN syntax is as follows:

For example, if you join tables A and B, the INNER JOIN compares each record of table A with each record in table B to find all pairs of records that meet the join condition. When the join condition is satisfied, the column values ​​for each matching pair of records of table A and table B are combined into one record in the returned result.

Alias: can create alias of table tbl_A is A and refer to column M as AM, so no need to re-type the table name. The above example could be rewritten as follows:

LEFT JOIN

The LEFT JOIN clause will be executed as follows: When a row from the left table matches a row from the right table based on the join condition, the contents of that row will be selected as one row in the output. . When a row in the left table cannot find a matching row in the concatenated table, it still appears in the output, but combines with a “dummy” row from the right table with NULL values ​​for all. column.
=> In summary, LEFT JOIN allows to select all rows from the left table even if there are no records matching it in the right table.
⁕ The LEFT JOIN is useful when you want to find records in the left table that do not match any of the records in the right table. This can be done by adding a WHERE clause to select rows with only NULL values ​​in a column in the right table.

SELF JOIN

A join is a type of join in which a table is joined to itself, specifically when a table has a foreign key referencing its primary key.
Example: The employees table has a foreign key named reportsTo that refers to the employeeNumber primary key of the employees table itself

Subquery

  • To combine data tables together, in addition to joins and set operators, SQL provides another way to return data from multiple tables called subqueries.
  • When a SELECT statement is used in another, the internal SELECT statement is called a subquery, another is a nested query, an inner query.

Subqueries are divided into two categories: non-correlated subqueries and correlated subqueries.

Subqueries are not correlated

  • An uncorrelated subquery is a subquery that is independent of the outer query.
  • The uncorrelated subquery is executed first and once for the entire statement.
  • The results of the subquery are populated into the outer query, and finally the outer query is executed.

Correlated subquery

  • Correlated subqueries are not independent of the external queries. A correlation subquery is a subquery that uses the values ​​from the outer query in its WHERE clause.
  • The process is as follows: the outer query is executed first and then the inner subquery is executed for each result row of the outer query.
  • This type of subquery is usually very slow because select_type is DEPENDENT SUBQUERY. In practice, this type of query should be limited.
Share the news now

Source : Viblo