MySql多表联结查询与多表关联数据同时删除

在数据库中所有数据库都是支持多表联合查询了，下面我来介绍利用left join在mysql中实现多表联合查询，有需要的朋友可参考

left join语法

代码如下	复制代码
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 }

例:

代码如下

复制代码

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 (2,22,0),(4,44,1),(5,55,0),(6,66,1); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0

查询

代码如下

复制代码

mysql> SELECT * FROM product LEFT JOIN product_details ON (product.id = product_details.id) AND product.amount=200; +----+--------+------+--------+-------+ | id | amount | id | weight | exist | +----+--------+------+--------+-------+ | 1 | 100 | NULL | NULL | NULL | | 2 | 200 | 2 | 22 | 0 | | 3 | 300 | NULL | NULL | NULL | | 4 | 400 | NULL | NULL | NULL | +----+--------+------+--------+-------+ 4 rows in set (0.01 sec)

超级简单吧,那么有朋友问我怎么在MySQL中实现多表关联数据同时删除

category中的id（栏目编号）字段作为该表的主键（primary key）.唯一标识了一个栏目的信息。
news 中的id字段作为该表的主键（primary key）.唯一标识了一个栏目的信息。

category_id（栏目编号）字段与category表的id字段相关联。

1.SQL删除语句

代码如下	复制代码
delete category,news from category left join news on category.id = news.category_id