为了演示 我创建了两张表:
tb_test:
sql:
CREATE TABLE `tb_test` (
`id` bigint(32) NOT NULL AUTO_INCREMENT,
`startTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`endTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`startDate` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`endDate` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
tb_test_join:
sql:
CREATE TABLE `tb_test_join` (
`tid` int(30) NOT NULL AUTO_INCREMENT,
`id` bigint(30) NOT NULL,
`orderid` bigint(30) NOT NULL,
PRIMARY KEY (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
查询语句:
#内连接查询
SELECT j.id ,j.startTime ,j.endTime , u.username FROM tb_test AS j JOIN tb_user AS u ON j.id=u.id
#左外连接
SELECT j.id ,j.startTime ,j.endTime , u.username FROM tb_test AS j LEFT JOIN tb_user AS u ON j.id=u.id
#右外连接
SELECT j.id ,j.startTime ,j.endTime , u.username FROM tb_test AS j RIGHT JOIN tb_user AS u ON j.id=u.id
①内连接:
查询两个表中符合连接条件的记录:
SELECT 字段名称,… FROM tbl_name1
INNER JOIN tbl_name2
ON 连接条件;
结果:
②左外连接:
左外连接:
SELECT 字段名称,… FROM tbl_name1
LEFT [OUTER] JOIN tbl_name2
ON 条件;
先显示左表中的全部记录,再去右表中查询复合条件的记录,不符合的以NULL代替
②右外接:
右外连接:
SELECT 字段名称,… FROM tbl_name1
RIGHT [OUTER] JOIN tbl_name2
ON 条件;
先显示右表中的全部记录,再去左表中查询复合条件的记录,不符合的以NULL代替
三表连接
再引入一张表:
tb_spec_param
sql:
CREATE TABLE `tb_spec_param` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
`cid` bigint(20) NOT NULL COMMENT '商品分类id',
`group_id` bigint(20) NOT NULL,
`name` varchar(255) NOT NULL COMMENT '参数名',
`numeric` tinyint(1) NOT NULL COMMENT '是否是数字类型参数,true或false',
`unit` varchar(255) DEFAULT '' COMMENT '数字类型参数的单位,非数字类型可以为空',
`generic` tinyint(1) NOT NULL COMMENT '是否是sku通用属性,true或false',
`searching` tinyint(1) NOT NULL COMMENT '是否用于搜索过滤,true或false',
`segments` varchar(1000) DEFAULT '' COMMENT '数值类型参数,如果需要搜索,则添加分段间隔值,如CPU频率间隔:0.5-1.0',
PRIMARY KEY (`id`),
KEY `key_group` (`group_id`),
KEY `key_category` (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8 COMMENT='规格参数组下的参数名';
语句
SELECT
A.student_code, A.student_name,
A.class_name,B.exam_name,
B.start_time,
B.end_time, C.student_score
FROM
( A LEFT JOIN B ON A.examination_id = B.id )
LEFT JOIN C ON A.examination_id = C.examination_id