MySQL notes: Advanced connections

MySQL notes: Advanced connections
Create advanced connections

The previous blog briefly introduces the use of connections. This blog introduces how to create advanced connections.

Use table alias

In the previous study, we learned how to get an alias for a column. Now let's review the following:

select cust_name,upper(cust_name) as upper_name from customers;

Or, use the concat function to combine the customer's name and phone number into a new information, and take the new information as an alias, as shown below:

The above is an alias for the column, or an alias for the calculated field. In addition, SQL allows aliases for table names. There are two main reasons for doing so:

Shortening SQL statements allows the same table to be used multiple times in a single select statement.

The following example shows how to get an alias for a table. As follows:

Use as MERS MERs/Z containers? Http://www.bkjia.com/kf/ware/vc/ "target =" _ blank "class =" keylink "> memory + Memory + LK7ysfKudPDyKvD + 6Os1eLR + memory/memory + DQo8cD48aW1nIGFsdD0 =" "src =" http://www.bkjia.com/uploads/allimg/160414/04195S221-2.jpg "title =" \ "/>

Use different types of joins

In the previous blog, internal connections (equivalent connections) were used ). First, review: the internal join is completed through the table_name inner join InnerJoin_table_name on clause. The actual example is as follows:

The above is a simple internal join, because I just learned to give the table an alias, I just added this knowledge point.

In addition to internal connections, there are three other forms:

Self-join, natural join, external join

The following is a one-to-one introduction.

Auto join

Self-join can be understood literally, that is, only one table exists and you are connected to yourself.

Let's look at an example: How many points does the English score of a student with a score of 100 in the student's score table mean?

You may say that you still need to use auto-join to complete this process. Simply use where to filter it.

Indeed, for this example, you can use the where clause directly, as shown below:

In addition to the above where filter, you can also use subqueries to complete the process as follows:

In addition to the two methods described above, you can also use the auto-join method, as shown below:

Here we use the auto-join method to complete the query. The tables in this query are actually the same tables, all of which are student2. Because we need to reference a table twice, therefore, we need to introduce two different aliases to table student2. Otherwise, the reference to student2 will be ambiguous. Therefore, MySQL does not know which instance you reference in the student2 table.

When an alias is introduced, all columns must be accessed using an alias. Otherwise, an error is returned because of ambiguity.

The following error is reported because the instance in student2 table is not specified:

The above describes how to complete such an operation. In the actual process, we should try to determine which method should be used to ensure better performance.

Natural connection

Since the premise of joining multiple tables is that at least one table should be listed. The standard join (internal join) returns all data (including duplicate columns ). The natural join clause eliminates multiple occurrences so that each column is returned only once.

Next, let's take a look at the standard join. When we use (select *) in the join process, the result is to return all column data.

How does a natural join eliminate multiple occurrences so that each column returns only once ??

The answer is: the system will not complete this task, but we will do it ourselves. The natural join is such a join, where we can only select those unique columns. This is generally done by using a wildcard (select *) for the table and using a clear subset for all columns in other tables.

To put it simply, the natural concatenation means that we specify the output content, not all the output content (because repeated column data is not what we want ).

External connections

It's too late to continue tomorrow, April 3, 2016 01:45:33;

Today, I went out for a day. I was so tired. I found my blog post was not completed yet. So I spent some time before going to bed, reading external connections, and practicing it.

As follows:

The preceding three forms are introduced: inner join, self join, and natural join. The following describes the external connections. What is external connection?

MySQL must know that this book is as follows:

Many joins associate rows in one table with rows in another table. But sometimes it is necessary to include those rows without associated rows. For example, we want to view the orders of all customers, including those that have not yet placed orders.

In this case, external connections are required.

For better experiments, I inserted a new customer information in the MERs table, which has no orders.

As follows:

If you do not need external connections, check the results of the internal connections to see which customers have not placed their orders yet:

From the above results, we can see that internal connections cannot meet our requirements (the order information of all customers will be displayed, including those without orders)

Let's take a look at external connections as follows:

From the results, we can see that the external links show the order status of all customers, including those who did not place orders. If a row in the left table does not match a row in the right table, all selection list columns in the right table in the row of the associated result set are null. This is also an important difference between external connections and internal connections.

I don't know whether everyone understands the external connection.

Simply put, a join contains rows that are not associated with rows in the relevant table. This type of join is an external join.

The syntax of external join is explained below.

External joins include left outer joins and right outer joins ).

Left outer join means that left points to the table on the left of outer join, that is, use left outer join to select all rows from the left table of the from clause (the MERs table in the above example.

To select all rows from the table on the right, use right outer join.

In addition to the preceding right outer join method, the positions of the two tables can be directly exchanged Based on the left outer join method, which achieves the same effect. That is, the left Outer Join can be converted to the right Outer Join by reversing the order of the tables in the from or where clause.

Summary

The usage of join is summarized as follows:

1) Pay attention to the connection type used. Generally, we use internal connections, but external connections are also valid.

2) ensure that correct join conditions are used; otherwise, incorrect results are returned.

3) The join conditions should always be provided. Otherwise, cartesian products will be obtained.