admirer
SQLServer和Mysql左连接，右连接，内连接，全连接前提
创建员⼯表
CREATE TABLE`ur`(
`id`int(11)NOT NULL AUTO_INCREMENT COMMENT'主键',
`name`varchar(255)CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT'员⼯姓名',
`dpid`int(11)NULL DEFAULT NULL COMMENT'部门id',
intheafternoonPRIMARY KEY(`id`)USING BTREE
)ENGINE=InnoDB AUTO_INCREMENT=4CHARACTER SET= utf8 COLLATE= utf8_general_ci ROW_FORMAT = Compact;新东方官方网站
创建部门表
CREATE TABLE`department`(
`id`int(11)NOT NULL COMMENT'主键',
`dpname`varchar(255)CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT'部门名称', PRIMARY KEY(`id`)USING BTREE
)ENGINE=InnoDB CHARACTER SET= utf8 COLLATE= utf8_general_ci ROW_FORMAT = Compact;
向表中插⼊数据
inrt into`ur`VALUES(1,'⼩王',1),(2,'⼩李',1),(3,'⼩刘',2)
inrt into`department`VALUES(1,'公司办'),(2,'秘书处'),(3,'开发中⼼')
橡皮英语内连接
定义：inner join （等值连接或者叫内连接）：只返回两个表中连接字段相等的⾏。
lect ur.id,ur.`name`,department.dpname from ur inner join department on ur.dpid=department.id
爱词典增加两个空记录在测试obss>六级翻译出淤泥而不染
韩国搞笑电视剧执⾏lect ur.id,ur.name,department.dpname from ur inner join department on ur.dpid=department.id 语句
左连接
dst
定义：left join （左连接）：返回包括左表中的所有记录和右表中连接字段相等的记录。执⾏以下SQL语句
lect*from ur;
lect*from department;
lect ur.id,ur.`name`,department.dpname from ur left join department on ur.dpid=department.id
右连接
定义：right join （右连接）：返回包括右表中的所有记录和左表中连接字段相等的记录。执⾏以下SQL语句
lect*from ur;
lect*from department;
lect*from ur right join department on ur.dpid=department.id
—
———————————————————————————————————————————
左连接右连接内连接，Mysql和SqlServer相同，但是Mysql不⽀持全连接————————————————————————————————————————————
踟蹰怎么读全连接
SqlServer
定义：full join （全外连接）：返回左右表中所有的记录和左右表中连接字段相等的记录。执⾏以下SQL语句
lect u.id from ur as u FULL JOIN department as d on u.dpid=d.id
-Mysql
mysql不⽀持全连接全连接，使⽤union连接左连接和右连接，得到全连接
lect*from ur as u right JOIN department as d on u.dpid=d.id
UNION
lect*from ur left join department on ur.dpid=department.id;
参考链接
