The following introduces three connection methods between multiple tables:
(1)Inner join query
(2)Outer join query
(3)Composite conditions Connection query
(free learning recommendation: mysql video tutorial)
Create the data table suppliers below for demonstration:
mysql> create table suppliers -> ( -> s_id int not null auto_increment, -> s_name char(50) not null, -> s_city char(50) null, -> s_zip char(10) null, -> s_call char(50) not null, -> primary key(s_id) -> );Query OK, 0 rows affected (0.17 sec)mysql> insert into suppliers(s_id,s_name,s_city,s_zip,s_call) -> values(101,'FastFruit Inc.','Tianjin','300000','48075'), -> (102,'LT Supplies','Chongqing','400000','44333'), -> (103,'ACME','Shanghai','200000','90046'), -> (104,'FNK Inc.','Zhongshan','528437','11111'), -> (105,'Good Set','Taiyuan','030000','22222'), -> (106,'Just Eat Ours','Beijing','010','45678'), -> (107,'DK Inc','Zhengzhou','450000','33332');Query OK, 7 rows affected (0.07 sec)Records: 7 Duplicates: 0 Warnings: 0
[Example 1] Use inner join query between fruits table and suppliers table.
mysql> desc fruits;+---------+--------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+---------+--------------+------+-----+---------+-------+| f_id | char(10) | NO | PRI | NULL | || s_id | int(11) | NO | | NULL | || f_name | char(255) | NO | | NULL | || f_price | decimal(8,2) | NO | | NULL | |+---------+--------------+------+-----+---------+-------+4 rows in set (0.06 sec)mysql> desc suppliers;+--------+----------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+--------+----------+------+-----+---------+----------------+| s_id | int(11) | NO | PRI | NULL | auto_increment || s_name | char(50) | NO | | NULL | || s_city | char(50) | YES | | NULL | || s_zip | char(10) | YES | | NULL | || s_call | char(50) | NO | | NULL | |+--------+----------+------+-----+---------+----------------+5 rows in set (0.00 sec)
You can see that both the fruits and suppliers tables have the same data type field s_id, and the two tables are connected through the s_id field.
Next, query the f_name and f_price fields from the fruits table, and query the s_id and s_name from the suppliers table. The SQL statement is as follows:
mysql> select suppliers.s_id,s_name,f_name,f_price -> from fruits,suppliers -> where fruits.s_id=suppliers.s_id;+------+----------------+------------+---------+| s_id | s_name | f_name | f_price |+------+----------------+------------+---------+| 104 | FNK Inc. | lemon | 6.40 || 101 | FastFruit Inc. | apple | 5.20 || 103 | ACME | apricot | 2.20 || 101 | FastFruit Inc. | blackberry | 10.20 || 104 | FNK Inc. | berry | 7.60 || 107 | DK Inc | xxxx | 3.60 || 102 | LT Supplies | orange | 11.20 || 105 | Good Set | melon | 8.20 || 101 | FastFruit Inc. | cherry | 3.20 || 106 | Just Eat Ours | mango | 15.70 || 105 | Good Set | xbabay | 2.60 || 105 | Good Set | xxtt | 11.60 || 103 | ACME | coconut | 9.20 || 102 | LT Supplies | banana | 10.30 || 102 | LT Supplies | grape | 5.30 || 107 | DK Inc | xbabay | 3.60 |+------+----------------+------------+---------+16 rows in set (0.00 sec)
[Example 2] Between the fruits table and suppliers table, use inner join syntax to perform inner join query. The SQL statement is as follows:
mysql> select suppliers.s_id,s_name,f_name,f_price -> from fruits inner join suppliers -> on fruits.s_id = suppliers.s_id;+------+----------------+------------+---------+| s_id | s_name | f_name | f_price |+------+----------------+------------+---------+| 104 | FNK Inc. | lemon | 6.40 || 101 | FastFruit Inc. | apple | 5.20 || 103 | ACME | apricot | 2.20 || 101 | FastFruit Inc. | blackberry | 10.20 || 104 | FNK Inc. | berry | 7.60 || 107 | DK Inc | xxxx | 3.60 || 102 | LT Supplies | orange | 11.20 || 105 | Good Set | melon | 8.20 || 101 | FastFruit Inc. | cherry | 3.20 || 106 | Just Eat Ours | mango | 15.70 || 105 | Good Set | xbabay | 2.60 || 105 | Good Set | xxtt | 11.60 || 103 | ACME | coconut | 9.20 || 102 | LT Supplies | banana | 10.30 || 102 | LT Supplies | grape | 5.30 || 107 | DK Inc | xbabay | 3.60 |+------+----------------+------------+---------+16 rows in set (0.00 sec)
If the two tables involved in a join query are the same table, this type of query is called a self-join query. Self-join is a special inner join, which means that the tables connected to each other are physically the same table, but can be logically divided into two tables.
[Example 3] To query the types of fruits provided by the fruit supplier supplying f_id='a1'
, the SQL statement is as follows:
mysql> select f1.f_id,f1.f_name -> from fruits as f1,fruits as f2 -> where f1.s_id =f2.s_id and f2.f_id ='a1';+------+------------+| f_id | f_name |+------+------------+| a1 | apple || b1 | blackberry || c0 | cherry |+------+------------+3 rows in set (0.00 sec)
①Left join: Returns all records in the left table and records with equal join fields in the right table.
②Right join: Return records including records in the right table that are equal to the join fields in the left table.
1. Left joinleft join
First create the table orders, the SQL statement is as follows;
mysql> create table orders -> ( -> o_num int not null auto_increment, -> o_date datetime not null, -> c_id int not null, -> primary key (o_num) -> );Query OK, 0 rows affected (0.11 sec)mysql> insert into orders(o_num,o_date,c_id) -> values(30001,'2008-09-01',10001), -> (30002,'2008-09-12',10003), -> (30003,'2008-09-30',10004), -> (30004,'2008-10-03',10005), -> (30005,'2008-10-08',10001);Query OK, 5 rows affected (0.06 sec)Records: 5 Duplicates: 0 Warnings: 0
[Example] In the customers table and orders table , query all customers, including customers without orders, the SQL statement is as follows:
mysql> select * from customers;+-------+----------+---------------------+---------+--------+-----------+-------------------+| c_id | c_name | c_address | c_city | c_zip | c_contact | c_email |+-------+----------+---------------------+---------+--------+-----------+-------------------+| 10001 | redhool | 200 Street | Tianjin | 300000 | LiMing | LMing@163.com || 10002 | Stars | 333 Fromage Lane | Dalian | 116000 | Zhangbo | Jerry@hotmail.com || 10003 | Netbhood | 1 Sunny Place | Qingdao | 266000 | LuoCong | NULL || 10004 | JOTO | 829 Riverside Drive | Haikou | 570000 | YangShan | sam@hotmail.com |+-------+----------+---------------------+---------+--------+-----------+-------------------+4 rows in set (0.00 sec)mysql> select * from orders;+-------+---------------------+-------+| o_num | o_date | c_id |+-------+---------------------+-------+| 30001 | 2008-09-01 00:00:00 | 10001 || 30002 | 2008-09-12 00:00:00 | 10003 || 30003 | 2008-09-30 00:00:00 | 10004 || 30004 | 2008-10-03 00:00:00 | 10005 || 30005 | 2008-10-08 00:00:00 | 10001 |+-------+---------------------+-------+5 rows in set (0.00 sec)mysql> select customers.c_id , orders.o_num -> from customers left outer join orders -> on customers.c_id = orders.c_id;+-------+-------+| c_id | o_num |+-------+-------+| 10001 | 30001 || 10003 | 30002 || 10004 | 30003 || 10001 | 30005 || 10002 | NULL |+-------+-------+5 rows in set (0.00 sec)
2. Right joinright join
[Example] In the customers table and orders table, query all orders, including orders without customers, the SQL statement is as follows:
mysql> select customers.c_id, orders.o_num -> from customers right outer join orders -> on customers.c_id = orders.c_id;+-------+-------+| c_id | o_num |+-------+-------+| 10001 | 30001 || 10003 | 30002 || 10004 | 30003 || NULL | 30004 || 10001 | 30005 |+-------+-------+5 rows in set (0.00 sec)
[Example 1] In the customers table and orders table, use inner join syntax to query the customers table to summarize the order information of the customer with ID 10001. The SQL statement is as follows:
mysql> select customers.c_id,orders.o_num -> from customers inner join orders -> on customers.c_id = orders.c_id and customers.c_id = 10001;+-------+-------+| c_id | o_num |+-------+-------+| 10001 | 30001 || 10001 | 30005 |+-------+-------+2 rows in set (0.00 sec)
[ Example 2] Between the fruits table and suppliers table, use inner join syntax to perform inner join query and sort the query results. The SQL statement is as follows:
mysql> select suppliers.s_id,s_name,f_name,f_price -> from fruits inner join suppliers -> on fruits.s_id = suppliers.s_id -> order by fruits.s_id;+------+----------------+------------+---------+| s_id | s_name | f_name | f_price |+------+----------------+------------+---------+| 101 | FastFruit Inc. | apple | 5.20 || 101 | FastFruit Inc. | blackberry | 10.20 || 101 | FastFruit Inc. | cherry | 3.20 || 102 | LT Supplies | orange | 11.20 || 102 | LT Supplies | banana | 10.30 || 102 | LT Supplies | grape | 5.30 || 103 | ACME | apricot | 2.20 || 103 | ACME | coconut | 9.20 || 104 | FNK Inc. | lemon | 6.40 || 104 | FNK Inc. | berry | 7.60 || 105 | Good Set | melon | 8.20 || 105 | Good Set | xbabay | 2.60 || 105 | Good Set | xxtt | 11.60 || 106 | Just Eat Ours | mango | 15.70 || 107 | DK Inc | xxxx | 3.60 || 107 | DK Inc | xbabay | 3.60 |+------+----------------+------------+---------+16 rows in set (0.00 sec)
More related free learning recommendations :mysql tutorial(Video)
The above is the detailed content of MySQL data query connection query. For more information, please follow other related articles on the PHP Chinese website!