All databases in the database support multi-table joint query. The following describes how to use leftjoin to implement multi-table joint query in mysql. if you need it, refer
All databases in the database support multi-table joint query. next I will introduce how to use left join to implement multi-table joint query in mysql. if you need it, refer
Left join syntax
The code is as follows: |
|
Table_references: Table_reference [, table_reference]… Table_reference: Table_factor | Join_table Table_factor: Tbl_name [[AS] alias] [{USE | IGNORE | FORCE} INDEX (key_list)] | (Table_references) | {OJ table_reference left outer join table_reference ON conditional_expr} |
Example:
The code is as follows: |
|
Mysql> create table 'product '( 'Id' int (10) unsigned not null auto_increment, 'Amount 'int (10) unsigned default NULL, Primary key ('id ') ) ENGINE = MyISAM AUTO_INCREMENT = 5 default charset = latin1 Mysql> create table 'product _ details '( 'Id' int (10) unsigned not null, 'Weight' int (10) unsigned default NULL, 'Exist' int (10) unsigned default NULL, Primary key ('id ') ) ENGINE = MyISAM default charset = latin1 Mysql> insert into product (id, amount) VALUES (1,100), (2,200), (3,300), (4,400 ); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 Mysql> insert into product_details (id, weight, exist) VALUES (, 0), (, 1), (, 0), (, 1 ); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 |
Query
The code is as follows: |
|
Mysql> SELECT * FROM product left join product_details ON (product. id = product_details.id) AND product. amount = 200; + ---- + -------- + ------ + -------- + ------- + | Id | amount | id | weight | exist | + ---- + -------- + ------ + -------- + ------- + | 1 | 1 | 100 | NULL | | 1 | 200 | 2 | 22 | 0 | | 4 | 300 | NULL | | 1 | 400 | NULL | + ---- + -------- + ------ + -------- + ------- + 4 rows in set (0.01 sec) |
A friend asked me how to achieve simultaneous deletion of multi-table join data in MySQL.
The id (column number) field in category is used as the primary key of the table. it uniquely identifies the information of a column.
The id field in news is used as the primary key of the table. it uniquely identifies the information of a column.
The category_id (column number) field is associated with the id field of the category table.
1. SQL deletion statement
The code is as follows: |
|
Delete category, news from category left join news on category. id = news. category_id |