深入学习之mysql（五）连接查询

深入学习Mysql（五）连接查询

1、准备数据库：

CREATE DATABASE IF NOT EXISTS `db_book2` DEFAULT CHARACTER SET UTF8;
 
USE `db_book2`;
 
DROP TABLE IF EXISTS `t_book`;
 
CREATE TABLE `t_book` (
`id` INT(11) PRIMARY KEY AUTO_INCREMENT,
`bookName` VARCHAR(20) DEFAULT NULL,
`price` DECIMAL(6,2) DEFAULT NULL,
`author` VARCHAR(20) DEFAULT NULL,
`bookTypeId` INT(11) DEFAULT NULL
) ENGINE=INNODB DEFAULT CHARSET=UTF8;
 
INSERT INTO `t_book` VALUES (NULL,'Java编程思想','100.00','埃史尔',1),(NULL,'Struts2权威指南','80.00','李刚',1),(NULL,'三剑客','70.00','大仲马',2),(NULL,'生理学(第二版)','24.00','刘先国',3);
 
DROP TABLE IF EXISTS `t_booktype`;
 
CREATE TABLE `t_booktype` (
`id` INT(11) PRIMARY KEY AUTO_INCREMENT,
`bookTypeName` VARCHAR(20) DEFAULT NULL
) ENGINE=INNODB DEFAULT CHARSET=UTF8;
 
INSERT INTO `t_booktype`(`id`,`bookTypeName`) VALUES (1,'计算机类'),(2,'文学类'),(3,'教育类');

 

 

 

2、连接查询：连接查询是将2个或者2个以上的表按照某个条件连接起来，从中选取需要的数据。

2.1、内连接：内连接可以查询2个或者2个以上的表；

例：查询t_book表中的编号，作者，书名和t_bookType表的类别名称：

 

SELECT t1.id,t1.author,t1.bookName,t2.bookTypeName FROM t_book t1,t_booktype t2 WHERE t1.bookTypeId=t2.id;

 

+----+-----------+----------------------+--------------+

| id | author | bookName | bookTypeName |

+----+-----------+----------------------+--------------+

| 1 | 埃史尔 | Java编程思想 | 计算机类 |

| 2 | 李刚 | Struts2权威指南 | 计算机类 |

| 3 | 大仲马 | 三剑客 | 文学类 |

| 4 | 刘先国 | 生理学(第二版) | 教育类 |

+----+-----------+----------------------+--------------+

4 rows in set (0.02 sec)

2.2.外链接查询：

外链接可以查询出某一张表中的所有信息；

SELECT 属性名列表FROM 表名1 LEFT|RIGHT JOIN 表名2 ON 表名1.属性名1=表名2.属性名2；

2.2.1:左连接查询：可以查询出表名1的所有记录，而表名2中只能查出匹配的记录：

例：以左外连接查询t_book 中的所有信息：

SELECT * FROM t_book t1 LEFT JOIN t_bookType t2 ON t1.bookTypeId = t2.id;

SELECT * FROM t_book t1 LEFT JOIN t_bookType t2 ON t1.bookTypeId = t2.id;

 

+----+----------------------+--------+--------------+------------+------+--------------+

| id | bookName | price | author | bookTypeId | id | bookTypeName |

+----+----------------------+--------+--------------+------------+------+--------------+

| 1 | Java编程思想 | 100.00 | 埃史尔 | 1 | 1 | 计算机类 |

| 2 | Struts2权威指南 | 80.00 | 李刚 | 1 | 1 | 计算机类 |

| 3 | 三剑客 | 70.00 | 大仲马 | 2 | 2 | 文学类 |

| 4 | 生理学(第二版) | 24.00 | 刘先国 | 3 | 3 | 教育类 |

| 5 | 大主宰 | 20.00 | 天蚕土豆 | 4 | NULL | NULL |

+----+----------------------+--------+--------------+------------+------+--------------+

5 rows in set (0.00 sec)

 

2.2.2右连接查询：

SELECT * FROM t_book t1 LEFT JOIN t_bookType t2 ON t1.bookTypeId = t2.id;

 

+------+----------------------+--------+-----------+------------+----+--------------+

| id | bookName | price | author | bookTypeId | id | bookTypeName |

+------+----------------------+--------+-----------+------------+----+--------------+

| 1 | Java编程思想 | 100.00 | 埃史尔 | 1 | 1 | 计算机类 |

| 2 | Struts2权威指南 | 80.00 | 李刚 | 1 | 1 | 计算机类 |

| 3 | 三剑客 | 70.00 | 大仲马 | 2 | 2 | 文学类 |

| 4 | 生理学(第二版) | 24.00 | 刘先国 | 3 | 3 | 教育类 |

+------+----------------------+--------+-----------+------------+----+--------------+

4 rows in set (0.00 sec)

2.2.3：多条件查询：

例: 查询t_book表中编号,作者,书名和t_bookType表的的类别名称,价格在20-30之间的记录

 

mysql> SELECT t1.id,t1.author,t1.bookName,t1.price,t2.bookTypeName FROM t_book t1,t_booktype t2 WHERE t1.bookTypeId=t2.id AND price BETWEEN 20 AND 30;

 

+----+-----------+----------------------+-------+--------------+

| id | author | bookName | price | bookTypeName |

+----+-----------+----------------------+-------+--------------+

| 4 | 刘先国 | 生理学(第二版) | 24.00 | 教育类 |

+----+-----------+----------------------+-------+--------------+

1 row in set (0.00 sec)

2.2.4：自连接查询

例：以自连接方式查询t_book表中作者不是天蚕土豆的所有记录：

SELECT t1.* FROM t_book t1,t_book t2 WHERE t1.id=t2.id AND t2.author<>'天蚕土豆';

mysql> SELECT t1.* FROM t_book t1,t_book t2 WHERE t1.id=t2.id AND t2.author<>'天蚕土豆';

 

+----+----------------------+--------+-----------+------------+

| id | bookName | price | author | bookTypeId |

+----+----------------------+--------+-----------+------------+

| 1 | Java编程思想 | 100.00 | 埃史尔 | 1 |

| 2 | Struts2权威指南 | 80.00 | 李刚 | 1 |

| 3 | 三剑客 | 70.00 | 大仲马 | 2 |

| 4 | 生理学(第二版) | 24.00 | 刘先国 | 3 |

+----+----------------------+--------+-----------+------------+

4 rows in set (0.00 sec)