插入练习数据
CREATE TABLE `user`(
`id` INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户id(主键)',
`username` VARCHAR(50) COMMENT '用户姓名',
`age` CHAR(3) COMMENT '用户年龄'
);
CREATE TABLE `orders`(
`id` INT AUTO_INCREMENT PRIMARY KEY COMMENT '订单id(主键)',
`price` DOUBLE COMMENT '订单价格',
`user_id` INT COMMENT '用户id(外键)'
);
ALTER TABLE orders ADD CONSTRAINT user_fk FOREIGN KEY (user_id) REFERENCES `user` (id);
INSERT INTO USER VALUES(1,'第一',11);
INSERT INTO USER VALUES(2,'小二',12);
INSERT INTO USER VALUES(3,'张三',33);
INSERT INTO USER VALUES(4,'李四',24);
INSERT INTO USER VALUES(5,'王五',17);
INSERT INTO USER VALUES(6,'赵六',36);
INSERT INTO USER VALUES(7,'七七',18);
INSERT INTO USER VALUES(8,'粑粑',NULL);
INSERT INTO USER VALUES(9,'小舅',NULL);
INSERT INTO orders VALUES(111,1314,3);
INSERT INTO orders VALUES(112,122,3);
INSERT INTO orders VALUES(113,15,4);
INSERT INTO orders VALUES(114,315,5);
INSERT INTO orders VALUES(115,1014,NULL);
INSERT INTO orders VALUES(116,666,6);
INSERT INTO orders VALUES(117,1111,1);
INSERT INTO orders VALUES(118,8888,NULL);
left join 举例
on
SELECT * FROM `user` AS u LEFT JOIN `orders` AS o
ON u.`id`=o.`user_id` AND u.`age` IS NOT NULL;
- 虽然判断了 AND u.
age
IS NOT NULL 但还是返回了全部的数据包括 age == null 的;
where
SELECT * FROM `user` AS u LEFT JOIN `orders` AS o
ON u.`id`=o.`user_id` WHERE u.`age` IS NOT NULL;
- on 后面跟 where 条件 语句,WHERE u.
age
IS NOT NULL 数据 age == null 的,没有显示了。
END…