1、实验测试内、左、右查询
数据信息提供了三张表:department、employee、student
提供三张表sql语句如下:
department:
CREATE TABLE `department` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`departmentName` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
employee:
CREATE TABLE `employee` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`lastName` varchar(255) DEFAULT NULL,
`email` varchar(255) DEFAULT NULL,
`gender` int(2) DEFAULT NULL,
`d_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
student:
CREATE TABLE `student` (
`id_s` varchar(10) NOT NULL,
`name` varchar(255) NOT NULL,
`age` int(10) NOT NULL COMMENT '年龄',
`birdhday` varchar(255) CHARACTER SET utf8 NOT NULL COMMENT '生日日期',
`adress` varchar(255) CHARACTER SET utf8 NOT NULL COMMENT '地址'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
三张表的数据:
1.1 使用内连接两表查询数据的变化,如图所示:
select * FROM department d
INNER JOIN student s ON d.id=s.id_s
1.2 使用内连接三表查询数据的变化,如图所示:
# 内连接三表查询
SELECT * FROM (department d INNER JOIN employee e ON e.id=d.id)
INNER JOIN student s ON s.id_s=e.id
2、使用左连接两表查询数据的变化,如图所示:
select * FROM department d
LEFT JOIN student s ON d.id=s.id_s
2.1 左连接,三表查询也是一样的,如图所示:
SELECT * FROM (department d LEFT JOIN employee e ON e.id=d.id)
LEFT JOIN student s ON s.id_s=e.id
3、右连接两表查询
select * FROM department d
RIGHT JOIN student s ON d.id=s.id_s
右连接,三表查询,如图所示
SELECT * FROM (department d RIGHT JOIN employee e ON e.id=d.id)
RIGHT JOIN student s ON s.id_s=e.id