MySQl 4-3 -- database query 3 -- from Statement

1. The SELECT query object of the from clause is specified by the FROM clause. Its format is: www.2cto.com FROM table name 1 [, table name 2]… Table Name: tbl_name [[AS] table alias] [{USE | ignore | force} index (key_list)]/* query table */| join_table/* connection table */description: the table name indicates the table or view to be queried. ● Table aliases are mainly used in related subqueries and connection queries. If the FROM clause specifies the table alias, all other clauses in the SELECT statement must use the table alias instead of the original table name. When the same table is mentioned multiple times in the SELECT statement, you must use the table alias to differentiate it. ● {USE | IGNORE | FORCE} INDEX: use index tells MySQL to select an INDEX to find rows in the table. ignore index tells MySQL not to USE certain indexes, force index is similar to use index (key_list). Table scanning is used only when a given INDEX cannot be used to find rows in the table. 2. the table name can contain one or more tables: www.2cto.com ● reference a table: You can reference a table in two ways. The first method is to USE the USE statement to make a database the current database, in this case, if the table name is specified in the FROM clause, the table should belong to the current database. The second method is to include the name of the database to which the table belongs before the table name. For example, if the current database is db1 and the table tb in the database db2 needs to be displayed, use the following statement: SELECT * FROM db2.tb; of course, when specifying a column name after the SELECT keyword, you can also include the database and table name before the column name. However, if the selected field is unique in each table, there is no need to specify it. ● Reference multiple tables: to query data in different tables, you must specify multiple tables in the FROM clause. When multiple tables are specified, the connection is used. When the data of different columns is combined into a table, it is called a table connection. 3. There are three connection methods. Advanced query (Multi-Table query: the data source is multiple tables) Multi-table join: To locate the join condition, the public fields (attributes) of the two tables are analyzed as follows: 1. consider the Data source: xs: Student ID name; xs_kc: course number score www.2cto.com 2. join condition: the student IDs of the two tables are equal to xs. student ID = xs_kc. student ID: If the column to be viewed exists in multiple tables, you must add the table name before the field name. 1). the first method of full join is to separate the tables with commas (,), so that the full join is specified. The intermediate result produced by the FROM clause is a new table. The new table indicates that each row in each table is crossed with each row in other tables to generate all possible combinations. The column contains the columns in all tables, that is, the flute product. In this way, the connected table may generate a very large number of rows, because the possible number of rows is the product of the number of rows in each table. In this case, you usually need to use the WHERE clause to set conditions to reduce the result set to a manageable size. Such a connection is equivalent join. For example, find the course name and course number selected by all students in the XSCJ database. Select distinct kc. course name, XS_KC. course No. from kc, XS_KC where kc. course No. = XS_KC. course number; 2 ). the JOIN syntax format is as follows: table Name 1 inner join table name 2 [join_condition] | table name 1 {left | right} [outer] JOIN table name 2 join_condition | table name 1 natural [{RIGHT | LEFT }[ OUTER] JOIN table name 2 | table name 1 cross join table name 2 [join_condition] | table name 1 straight_join table name 2 [ON condition_exp] where, join_condition indicates: ON Connection condition | using (column_list) the JOIN keyword is mainly divided into three types:. internal Connection (the connection with the inner keyword specified is internal Connection ). The INNER keyword can be omitted. After inner join is used, the ON condition in the FROM clause is mainly used to connect to the table. Other conditions that do not belong to the connected table can be specified using the WHERE clause. As a special case, a table can be connected to itself, which is called a self-join. To search for rows with the same column value in a table, you can use auto-join. When using auto-join, you must specify two aliases for the table and define aliases for reference to all columns. For example, find the student ID, course number, and score of the student with different courses and the same score in the XSCJ database. SELECT. student ID,. course No., B. course No.,. score FROM XS_KC AS a JOIN XS_KC AS B www.2cto.com ON. score = B. score AND. student ID = B. student ID ANDa. course No! = B. Course number. if the name of the column in the table to be connected is the same and the join condition is that the column name is the same, the ON condition can be changed to the USING clause.

The USING (column_list) clause is used to name a series of columns. These columns must exist in both tables. Column_list is the same column name in the two tables. For example, find the course names selected by all students in the KC table. SELECT Course name from kc inner join XS_KC USING (course number); note: the query result is the course name corresponding to all course numbers in the XS_KC table. B. OUTER Join (the connection with the OUTER keyword specified is OUTER join .) Outer join: ● left outer join: In addition to matching rows, the result table also contains rows in the LEFT table but not in the right table. For such rows, the column selected from the right table is set to NULL. ● RIGHT outer join: In addition to matching rows, the result table also contains rows that do not match in the RIGHT table but do not match in the left table. For such rows, the column selected from the left table is set to NULL. ● Natural join: there are natural left outer join and NATURAL right outer join ). The semantic definition of natural join is the same as that of inner join that uses the ON condition. The OUTER keyword can be omitted. For example, you can check the status of all students and the number of courses they take. If the students do not take any courses, they should also include the status. Select xs. *, course number from xs left outer join XS_KC on xs. student ID = XS_KC. student ID. Note: if left outer join is not used in this example, the result does not contain student information for any course not selected. After the left Outer Join is used, the returned row contains the student information of the course not selected, and the course number field value of the corresponding row is NULL. Example: Use a natural connection to implement the same result in example 4.22. Select distinct Course name, XS_KC. course No. from kc natural join XS_KC; Note: When only one column is selected in the SELECT statement to connect to the table, you can use NATURAL connections instead of internal connections. In this way, you can replace the left Outer Join with the natural left Outer Join and the right outer join with the natural right outer join. (Not many tables are used, which is prone to errors because the system can automatically find the same condition and an error will be reported when it cannot be found) Note: Only two tables can be connected. C. cross join (the connection with the cross join keyword specified is a cross join .) When the join condition is not included, the cross join operation is actually performed on two tables, and the result table is a table formed by splicing each row of the first table and each row of the second table, therefore, the number of rows in the result table is equal to the number of rows in two tables. In MySQL, cross join is syntactically equivalent to inner join, and the two can be exchanged. For example, list all possible course selections of students. Www.2cto.com SELECT student ID, name, course number, course name from xs cross join kc. In addition, the usage of the STRAIGHT_JOIN connection is basically the same as that of the INNERJOIN connection. The difference is that the ON condition cannot be replaced by the USING clause after STRAIGHT_JOIN. Example: Use the STRAIGHT_JOIN connection to implement the same result in example 4.22. Select distinct Course name, XS_KC. course No. from kc STRAIGHT_JOIN XS_KC ON (KC. course No. = XS_KC. course number); 4. comparison of several connections: Requirements: query the name, professional name, course name, score Data source: xs: name, professional name; kc: Course name; xs_kc: score connection condition: xs. student ID = xs_kc. student ID and kc. course No. = xs_kc. example 1: select name, professional name, course name, score from xs, kc, xs_kc where xs. student ID = xs_kc. student ID and kc. course No. = xs_kc. course number; Example 2: select name, Major name, course name, result fromxs, kc, xs_kc where xs. student ID = xs_kc. student ID and kc. course No. = xs_kc. course No. and Course name = 'computer basics 'and score> = 80 order by score desc; (2) Inner join Example 1: select xs. student ID, name, course number, score from xs inner join xs_kc on xs. student ID = xs_kc. student ID; Example 2: select name, Major name, course name, score from xs inner join xs_kc on xs. student ID = xs_kc. student ID inner join kc on xs_kc. course No. = kc. course No. where Course name = 'computer basics 'and score> = 80; 5. connect multiple tables (if three tables exist ). join join: from table 1 inner join table 2 on condition 1 inner Join table 3 on condition 2 www.2cto.com 2 ). full join: from table 1, Table 2, Table 3 where Condition 1 and condition 2 3 ). add an alias to the table: from table name as alias. Note: if an alias is added to the table, you must use the alias later. You cannot use the original table name tianyazaiheruan.