在数据库中所有数据库都是支持多表联合查询了,下面我来介绍利用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字段相关联。
SQL删除语句,代码如下:
delete category,news from category left join news on category.id = news.category_id |