数据库连接

作者：海鹰
此教程讲述数据库连接，包括内连接、外连接（左连接、右连接、完整连接）、交叉连接、合并
版权所有，未经允许，请勿随意转载。

数据库连接

初始化数据

-- 创建数据库表
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(30) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `book` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `bookname` varchar(30) NOT NULL,
	`userId` int(11),
  PRIMARY KEY (`id`),
  UNIQUE KEY `bookname` (`bookname`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 插入测试数据
INSERT INTO `user`(`id`, `username`) VALUES(1, 'hai');
INSERT INTO `user`(`id`, `username`) VALUES(2, 'ying');
INSERT INTO `user`(`id`, `username`) VALUES(3, 'sea');
INSERT INTO `user`(`id`, `username`) VALUES(4, 'eagle');

INSERT INTO `book`(`id`, `bookname`, `userId`) VALUES(1, 'book_0', 1);
INSERT INTO `book`(`id`, `bookname`, `userId`) VALUES(2, 'book_1', 1);
INSERT INTO `book`(`id`, `bookname`, `userId`) VALUES(3, 'book_2', 3);
INSERT INTO `book`(`id`, `bookname`, `userId`) VALUES(4, 'book_3', NULL);

交叉连接

两张表在没有任何条件的情况下联合，即笛卡尔积
提前了解交叉连接，能够更好地对比内连接及外连接

1
2
3

-- 有两种写法，结果都一样
SELECT * FROM `user`, `book` ;
SELECT * FROM `user` JOIN `book`;

结果

id	username	id1	bookname	userId
1	hai	1	book_0	1
1	hai	2	book_1	1
1	hai	3	book_2	3
2	ying	1	book_0	1
2	ying	2	book_1	1
2	ying	3	book_2	3
3	sea	1	book_0	1
3	sea	2	book_1	1
3	sea	3	book_2	3
4	eagle	1	book_0	1
4	eagle	2	book_1	1
4	eagle	3	book_2	3
1	hai	4	book_3	null
2	ying	4	book_3	null
3	sea	4	book_3	null
4	eagle	4	book_3	null

内连接

利用内连接获取两表之间公共部分的记录
在本教程中，user表的id，与book表中的userId为对应关系，根据这个关系可以获取公共部分的数据

1
2
3

-- 有两种写法，结果都一样
SELECT * FROM `user` u, `book` b WHERE u.`id` = b.`userId`;
SELECT * FROM `user` u JOIN `book`b ON u.`id` = b.`userId`;

结果

id	username	id1	bookname	userId
1	hai	1	book_0	1
1	hai	2	book_1	1
3	sea	3	book_2	3

外连接——左连接

在语句中，假设，A在B的左边，并且是LEFT JOIN，所以其运算方式为：A左连接B的记录=公共部分记录集C＋表A记录集A1
在本教程中，假设user表左连book表

1	SELECT * FROM `user` u LEFT JOIN `book` b ON u.`id` = b.`userId`;

结果

id	username	id1	bookname	userId
1	hai	1	book_0	1
1	hai	2	book_1	1
3	sea	3	book_2	3
2	ying	null	null	null
4	eagle	null	null	null

外连接——右连接

在语句中，假设，B在A的右边，并且是RIGHT JOIN，所以其运算方式为：A右连接B的记录=公共部分记录集C＋表B记录集B1
在本教程中，假设user表右连book表

1	SELECT * FROM `user` u RIGHT JOIN `book` b ON u.`id` = b.`userId`;

结果

id	username	id1	bookname	userId
1	hai	1	book_0	1
1	hai	2	book_1	1
3	sea	3	book_2	3
null	null	4	book_3	null

外连接——完整连接

完整外部连接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。

-- 这个是标准写法，只是MySQL数据库并不支持这样的写法
SELECT * FROM `user` u FULL JOIN `book` b ON u.`id` = b.`userId`;
-- 采用union实现
SELECT * FROM `user` u LEFT JOIN `book` b ON u.`id` = b.`userId`
UNION
SELECT * FROM `user` u RIGHT JOIN `book` b ON u.`id` = b.`userId`;

结果

id	username	id1	bookname	userId
1	hai	1	book_0	1
1	hai	2	book_1	1
3	sea	3	book_2	3
2	ying	null	null	null
4	eagle	null	null	null
null	null	4	book_3	null

合并

UNION用于合并两个或多个SELECT语句的结果集，并消去表中任何重复行。
UNION的SELECT语句必须拥有相同数量的列，列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。

语法如下

1
2
3

SELECT column_name1, column_name2 FROM table1
UNION
SELECT column_name1, column_name2 FROM table2

默认情况下，UNION操作符会消除重复行，如上面的例子（外连接——完整连接），若不想消除重复行，可使用ALL关键字。

语法如下

1
2
3

SELECT column_name1, column_name2 FROM table1
UNION ALL
SELECT column_name1, column_name2 FROM table2

以上面的例子（外连接——完整连接）为例：

1
2
3

SELECT * FROM `user` u LEFT JOIN `book` b ON u.`id` = b.`userId`
UNION ALL
SELECT * FROM `user` u RIGHT JOIN `book` b ON u.`id` = b.`userId`;

结果

id	username	id1	bookname	userId
1	hai	1	book_0	1
1	hai	2	book_1	1
3	sea	3	book_2	3
2	ying	null	null	null
4	eagle	null	null	null
1	hai	1	book_0	1
1	hai	2	book_1	1
3	sea	3	book_2	3
null	null	4	book_3	null

结语

本教程到此结束，欢迎指正，互相交流。
版权所有，未经允许，请勿随意转载。