Retrieve record
3.6 Retrieving Records Unless I finally retrieve them and use them to do something, it is nothing benefit to the database. This is the use of the SELECT statement, that is, helps remove the data. SELECT is probably the most commonly used statement in the SQL language, and how to use it is also the most aberration; use it to choose a record may be quite complicated, which may involve a comparison between columns in many tables. The syntax of the SELECT statement is as follows: In addition to the word "S E L E C T" and the description of the column_list part you want to search, the syntax is optional. Some databases also need from clauses. MySQL varies, it allows the expression to be evaluated without quoting any table: in Chapter 1, we have a big effort to the SELECT statement, mainly introduced the list of columns and WHERE, group by, order BY, H AVING, and LIMIT clause. In this chapter, we will focus on the SELECT statement in the SELECT statement, that is, connect (J o i n). We will introduce the connection types supported by mysql, how to specify them, etc. This will help use M Y S Q L more efficiently, because in many cases, the key to solve how to write queries is to determine how to connect the table together. It should also be found in this chapter 3. 8 "Solution with the pen". In that section, you will find solutions to several SQL issues, which are mostly features such as the SELECT statement. One problem with SELECT is that when you encounter a new problem, you can't always know how to write a SELECT query to solve it. But after the solution, it can take advantage of the experience when encountering similar problems. SELECT is probably the experience in which it works greatly in effectively use it because it is used to use it. After having a certain experience, you can use these experiences for new problems, you will find that you think about the problem is similar, "Oh, yes, it is a left join problem." Or, ", this is a Each three-way connection to the index column is connected. "(Pointing out this, I am actually a bit unwilling. I heard the experience is helpful, you may be encouraged. In addition, considering that you can finally think about the problem It is a bit surprised.) In the next few, how to use the format of the connection operation supported by MySQL, most examples use the following two tables. They are very small, very simple, enough to see the effect of each connection. 3.6.1 The simplest connection of the ordinary connection is a Trivial Join, which only specifies a table in this connection. In this case, the line is selected from the specified table. For example: Some autists do not consider this SELECT connection form, which uses "Connect" terms only for SELECT statements retrieved from two or more tables. I think that it is only different. 3.6.2 Full Connection If multiple tables are specified, the numerals are separated by commas, and the full connection is specified. For example, if two tables are connected, each row in the first table is combined with each line in the second table: full connection is also known as a fork connection, because each row of each table is connected to each table. The intersection of crossings to produce all possible combinations. This is the so-called Cartesian. This way to connect the table potentially have a very large number of rows, because the number of lines that may be obtained is the product of the number of rows in each table. The three connections that contain 1 0 0, 2 0 0, 3 0 0 rows will result in 1 0 0 × 2 0 0 × 3 0 0 = 6 million lines. Even if each table is small, the number of rows obtained will be large. In this case, the W H E R e clause is usually used to reduce the result set as the size of easy management.
If a condition is added to the WHERE clause to match each table on some columns, this connection is the so-called equivalent connection (E Q u i - j o i n), because only those rows having equal values in the specified column are selected. As: J o I n, Cross Join, and Inner Join Connection Types are the same as "," connect operators. Straight_join is similar to full connections, but each table is connected in the order specified in the FROM clause. Under normal circumstances, the MySQL optimizer itself does not consider the order in which each table is arranged, in order to make the recorded retrieval. At any occasion, the optimization program will make a non-optimized choice, which will ignore the stright_join keyword. In the SELECT statement, S T R A I g h t _ J o I N can be given in two locations. One location is between the SELECT keyword and the selection list, put it here to all all connections in the statement. Another in the FROM clause. The following two statements are equivalent: the defined column references the references in the SELECT statement must have no dismissal of each table specified in the FROM clause. If you specify only a table in the FROM clause, there is no disringus existence because all columns must be the column of the table. If multiple tables are specified, only the column names in one table are also unambigied. However, if a column name appears in multiple tables, the reference must be limited to the table name, and the TBL_NAME.COL_NAME syntax indicates which table refers to. If table my_tbl1 contains columns A and B, Table My_TBL2 contains columns B and C, the references of columns A and C are unambiguous, but B's reference must be limited to MY_TBL1.B or MY _ TBL 2. B, such as: Sometimes The table name limit does not resolve the column reference problem. For example, if a table is used multiple times in a query, there is no use of the list name. In this case, you can use an alias to express your idea. Assign an alias for the table, use this alias to reference columns, its syntax is: alias _ name.col _ name. The following query is connected to itself, assigns an alias for the table to cope with the reference column: 3.6.3 Left connection equivalent connection only gives two tables matching line. The left connection also gives a match, but it also displays a row in the left table but does not match in the right table. For such rows, the columns selected from the right table are displayed as N u L L. In this way, each line is selected from the left table. If there is a match in the right table, the row is selected. If it does not match, the row is still selected, but it is a "false" line, where all columns are set to n u L L. In other words, the Left Join enforcement set contains the rows of each row in the left table, regardless of whether there is a match in the left table in the left table. Matching is based on the column given in the ON or Using () clause. ON can be used regardless of whether the columns connected have the same name. Such as: USING () clause is similar to O n, but the name of the connection column must be the same in each table. The following query connects my_tbl1.b to M y _ T B L 2. B: LEFT JOIN is extremely useful when you want to find only rows in the left table without appearing in the right table. This work can be done by adding a WHERE clause with a column with a null value in the right table. Neaver you don't have to worry about the columns selected as null, because there is nothing mean.
