Select * from surface 1 where a in (select a from surface 2 where Conditions ) Mysql> select foot.s from foot where id = ( select user.foot_id from user where id = 1) Mysql> select foot.s from foot where id = 1 mysql> select user.foot_id from user where id = 1 If we want to check user In the table id by 1 Corresponding person foot The fruit in the table. Select * from surface 1 where a = (select a from surface 2 where Conditions ) In some cases, the results of two queries will coincide, If we don't want to have duplicate data, we can remove union After all. mysql> select u.id,u.name,u.gender from user as u where id = 2īe careful : Multiple select The number of fields in the field list after should always be. Suppose we need to query user In the table id by 2 People's information and yg In the table id by 3 Information about people and summarize it on a table. When we query multiple tables, If you want to gather data in one table, you can use joint query. Self join is essentially a table with two different aliases when two tables are used. mysql> select a.name,b.name from yg as a left outer join yg as b on a.t_id = b.id T_id Field corresponds to id Indicates a superior relationship, Now you need to query each person's corresponding superior, so you need to use self connection. Self connection can be used with internal and external connections. These two sentences SQL The effect of statement execution is consistent. Select * from foot as f right outer join user as u on u.foot_id = f.id Select * from user as u left outer join foot as f on u.foot_id = f.id As long as you change the position of the table, the effect can be the same, for example : // The left outer join In essence, the left outer connection is the same as the right outer connection. It doesn't show user In the table id by 4 The message is because in foot Fields in the table id No corresponding user Fields in the table foot_id Of null value. | 3 | a mandarin orange | 3 | lin | male | 3 | | id | s | id | name | gender | foot_id | Select Field list from surface 1 right join surface 2 on Conditions mysql> select f.*,u.* from user as u right outer join foot as f on u.foot_id = f.id Right outer connection ( All the right tables intersect with the left and right tables ) Select Field list from surface 1 left join surface 2 on Conditions mysql> select u.*,f.* from user as u left outer join foot as f on u.foot_id = f.id The left outer join ( The left table all intersects with the left and right tables ) In this case, if you use the internal connection, you will find that if the condition field is empty, you will not be able to query, So we can use external connection to solve. Mysql> select * from user,foot where user.foot_id = foot.id Go to user Add a new record, Field foot_id by null. This speed advantage is 3 It is obvious when two or more tables are connected. But explicit join can reduce the scanning of fields, There's faster execution. Relatively speaking, Implicit connection is good for understanding and writing, Grammar is simple, Worry less. Select Field list from surface 1 join surface 2 on Connection condition mysql> select * from user as u inner join foot as f on u.foot_id = f.id Select Field list from surface 1, surface 2 where Conditions mysql> select * from user as u,foot as f where u.foot_id = f.id The data displayed by Cartesian product will have something we don't need, So we can filter out unnecessary conditions with conditions. Taking these two tables as an example, the Cartesian product phenomenon is the table A Every record and table B Each record matches to form a new record, Such as table A There are three record sheets B After three records are combined, it is 3*3(9) Bar record. | 3 | lin | male | 3 | 3 | a mandarin orange | | 2 | bom | Woman | 2 | 3 | a mandarin orange | | 1 | ton | male | 1 | 3 | a mandarin orange | | id | name | gender | foot_id | id | s | MySQL Basic course 15- Multi-table query The cartesian product mysql> select * from user
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |