对供货商的名字进行模糊搜索，想找出他的商品的名字和价格

Question

这段代码想来个模糊搜索。  只记得某个供货商叫比什么来着，想找出他提供是商品名称和价格。但怎么都出来了mysql> describe vendors;
+--------------+----------+------+-----+---------+-------+
| Field        | Type     | Null | Key | Default | Extra |
+--------------+----------+------+-----+---------+-------+
| vend_id      | int(11)  | NO   | PRI | 0       |       |
| vend_name    | char(50) | YES  |     | NULL    |       |
| vend_address | char(50) | YES  |     | NULL    |       |
| vend_city    | char(50) | YES  |     | NULL    |       |
| vend_state   | char(50) | YES  |     | NULL    |       |
| vend_zip     | char(50) | YES  |     | NULL    |       |
| vend_country | char(50) | YES  |     | NULL    |       |
+--------------+----------+------+-----+---------+-------+
7 rows in set (0.00 sec)mysql> describe products;
+------------+----------+------+-----+---------+-------+
| Field      | Type     | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+-------+
| prod_id    | int(11)  | NO   | PRI | 0       |       |
| vend_id    | int(11)  | YES  |     | NULL    |       |
| prod_name  | char(50) | YES  |     | NULL    |       |
| prod_price | int(11)  | YES  |     | NULL    |       |
| prod_desc  | char(50) | YES  |     | NULL    |       |
+------------+----------+------+-----+---------+-------+
5 rows in set (0.00 sec)mysql> select * from vendors;
+---------+------------+--------------+-----------+------------+----------+-----
---------+
| vend_id | vend_name  | vend_address | vend_city | vend_state | vend_zip | vend
_country |
+---------+------------+--------------+-----------+------------+----------+-----
---------+
|       1 | 比尔盖茨   | NULL         | NULL      | NULL       | NULL     | 美国
         |
|       2 | 纳爱斯集团 | NULL         | NULL      | NULL       | NULL     | 中国
         |
+---------+------------+--------------+-----------+------------+----------+-----
---------+
2 rows in set (0.00 sec)mysql> select * from products;
+---------+---------+------------------+------------+-----------+
| prod_id | vend_id | prod_name        | prod_price | prod_desc |
+---------+---------+------------------+------------+-----------+
|       1 |       1 | windows 操作系统 |        400 | NULL      |
|       2 |       1 | Word软件         |        500 | NULL      |
|       3 |       1 | Excel软件        |        650 | NULL      |
|       4 |       1 | PowerPoint软件   |        750 | NULL      |
|       5 |       2 | 雕牌肥皂         |          3 | NULL      |
|       6 |       2 | 雕牌洗衣粉       |          9 | NULL      |
|       7 |       2 | 雕牌洗洁精       |          4 | NULL      |
+---------+---------+------------------+------------+-----------+
7 rows in set (0.00 sec)mysql> select vend_name, prod_name , prod_price from products,vendors where prod
ucts.vend_id in ( select vendors.vend_id from vendors where vendors.vend_name li
ke '%比%');
+------------+------------------+------------+
| vend_name  | prod_name        | prod_price |
+------------+------------------+------------+
| 比尔盖茨   | windows 操作系统 |        400 |
| 纳爱斯集团 | windows 操作系统 |        400 |
| 比尔盖茨   | Word软件         |        500 |
| 纳爱斯集团 | Word软件         |        500 |
| 比尔盖茨   | Excel软件        |        650 |
| 纳爱斯集团 | Excel软件        |        650 |
| 比尔盖茨   | PowerPoint软件   |        750 |
| 纳爱斯集团 | PowerPoint软件   |        750 |
+------------+------------------+------------+
8 rows in set (0.00 sec)

Answer 1

select vend_name,prod_name,prod_price from products,vendors where products.vend_id = vendors.vend_id
and  products.vend_id in ( select vendors.vend_id from vendors where vendors.vend_name li
ke '%比%');

Answer 2

为什么要有
products.vend_id = vendors.vend_id
呢。这是什么思想

Answer 3

select p.*
from products p inner join vendors v on p.vend_id=v.vend_id
where v.vend_name like '比%'