Shulou(Shulou.com)06/01 Report--

1. Sub-query

MySQL 4.1 and above supports subqueries

Subquery: A query nested within another query.

The role of subqueries:

1. Filter:

Example 1: Retrieving the IDs of all customers who ordered item TNT2

=

+

In general, there is no limit to the number of subqueries that can be nested in the WHERE clause, but in practice, due to performance constraints, too many subqueries cannot be nested.

Note: Columns must match--Using a subquery in a WHERE clause (as shown here), you should ensure that the SELECT statement has the same number of columns as in the WHERE clause. Typically, a subquery will return and match a single column, but multiple columns can be used if desired.

Example 2: Return to customer list ordering product TNT2

A more efficient way to query this example is to use joins:

Note: The details of the link are summarized below.

2. Create calculation fields:

Related subqueries: Subqueries that involve external queries. This syntax must be used when column names are potentially ambiguous.

Example: Displays the total number of orders for each customer in the customers table

Summary:

The most common use of subqueries is in the IN operator of a WHERE clause and to populate computed columns

Subqueries The most reliable way to build (and test) queries is gradually, much the same way MySQL handles them. First, build and test the innermost query. The outer query is then built and tested with hardcoded data, and the subquery is embedded only after confirming that it is OK. Then test it again. Repeat these steps for each query you want to add. Doing so adds only a small amount of time to constructing the query, but saves a lot of time later (figuring out why the query isn't working) and greatly increases the likelihood that the query will work right in the first place.

II. Connection table

Join tables are one of SQL's most powerful features

1. Some related basic knowledge reserves:

Relational tables: Ensure that information is broken down into multiple tables, one type of data table. The tables are related to each other by some commonly used value, the relational in relational design. Save time and storage space, and facilitate data modification and update. Relational databases are therefore far more scalable than non-relational databases.

Scalability: The ability to adapt to increasing workloads without failure. A well-designed database or application is called scalable.

Join: A join is a mechanism for associating tables in a SELECT statement, which joins multiple tables to return a set of outputs.

A join is not a physical entity--it doesn't exist in the actual database table. Joints are created by MySQL on demand and exist during query execution.

When working with relational tables, it is important to insert only valid data into relational series. To prevent this from happening, referential integrity needs to be maintained by specifying primary and foreign keys in the table definition.

2. Basic connection:

Example 1:

The two tables are joined correctly with a WHERE clause: the WHERE clause instructs MySQL to match vend_id in the vendors table with vend_id in the products table. Note: Fully qualified column names (table names and column names separated by a dot) must be used when the referenced column may be ambiguous.

When several tables are joined in a SELECT statement, the corresponding relationships are constructed on the fly, and there is nothing in the database table definition that tells MySQL how to join the tables. When you join two tables, you actually pair each row in the first table with each row in the second table. The WHERE clause acts as a filter condition, containing only those rows that match the given condition (in this case, the join condition). Without a WHERE clause, every row in the first table is paired with every row in the second table, regardless of whether they logically fit together.

Cartesian product: The result returned by a table relation without join conditions. The number of rows retrieved will be the number of rows in the first table multiplied by the number of rows in the second table. It is also sometimes referred to as a fork connection.

Example 2: Display items in order number 20005

You should ensure that all joins have WHERE clauses, otherwise MySQL will return much more data than you want.

MySQL associates each table specified at runtime to handle joins. This process can be very resource intensive, so care should be taken not to join unnecessary tables. The more tables you join, the more performance degrades.

Equivalence links: tests based on equality between two tables, also known as internal links. (The most frequently used connection)

Examples:

The ANSI SQL specification prefers the INNER JOIN syntax. In addition, although defining joins using WHERE clauses is indeed simpler, using explicit join syntax ensures that join conditions are not forgotten, and sometimes performance is affected.

3. Advanced connection:

Example 1: Alias a table (same as alias a column)

Note: Table aliases are used only in query execution. Unlike column aliases, table aliases are not returned to the client.

One of the main reasons for using table aliases is to be able to reference the same table more than once in a single SELECT statement

Example 2: Query other items produced by the supplier producing the item with DTNTR ID

The solution is a self-join, which is often used as an external statement in place of a subquery statement used to retrieve data from the same table. This instance can also be resolved with subqueries. Although the end result is the same, sometimes processing joins is much faster than processing subqueries. When solving a problem, try two methods to determine which performs better.

Natural join: excludes multiple occurrences so that each column returns only once. The internal connections we usually use are natural connections.

Example 3: Natural Connections

Natural joins are typically accomplished by using wildcards (SELECT *) for tables and explicit subsets for all other table columns.

External joins: Joins contain rows that have no associated rows in the related tables.

Example 4: Retrieving all customers, including those without orders

Usage is similar to internal joins, using the keyword OUTER JOIN to specify the type of join. However, unlike an inner join that associates rows in two tables, an outer join also includes rows that have no associated rows.

There are two basic forms of external coupling: left external coupling and right external coupling. When using the OUTER JOIN syntax, you must specify the table that includes all its rows using the RIGHT or LEFT keywords (RIGHT indicates the table to the right of the OUTER JOIN, and LEFT indicates the table to the left of the OUTER JOIN). The above example uses LEFT OUTER JOIN to select all rows from the left table (customers table) of the FROM clause.

Note: MySQL does not support the use of simplified characters *= and =*, although these operators are popular in other DBMSs.

Example 5: Retrieves all customers and the number of orders placed by each customer (including customers who did not place any orders

Aggregate functions can be conveniently used with various join types

Use join and join conditions:

1. Note the type of join used. Generally we use internal links, but it is also valid to use external links. 2. Ensure that the correct join conditions are used, otherwise incorrect data will be returned. 3. Connection conditions should always be provided, otherwise Cartesian products result. 4. You can have multiple tables in a join, and you can even have a different join type for each join. While this is legal and generally useful, you should test each connection separately before testing them together. This will make troubleshooting easier.

III. Combined query

Composite queries: Executes multiple queries (multiple SELECT statements) and returns the results as a single query result set. These composite queries are often referred to as union or composite queries.

Why do I need to combine queries?

Return similarly structured data from different tables in a single query; execute multiple queries against a single table to return data as a single query; use composite queries to greatly simplify complex WHERE clauses and simplify retrieving data from multiple tables.

1. Create a composite query

Keyword: UNION operator

Example 1: Get a list of all items with prices less than or equal to 5, and include all items produced by suppliers 1001 and 1002 (regardless of price).

UNION instructs MySQL to execute two SELECT statements and combine the output into a single query result set. The solution is where prod_price

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.