内连接、外连接、自然连接 简单的SQL语句总结
1、建库
指定数据库编码
CREATE DATABASE IF NOT EXISTS yourdb DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE DATABASE yourdb DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;
2、建表
创建表的字段
tongshi1表
CREATE TABLE `tongshi1` (
`id` int(10) NOT NULL,
`name` varchar(100) NOT NULL,
`age` char(3) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
tongshi2表
CREATE TABLE `tongshi2` (
`name` varchar(100) NOT NULL,
`post` varchar(100) NOT NULL,
`money` int(6) NOT NULL,
PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
创建表的记录
创建tongshi1表的记录
INSERT INTO `tongshi1` VALUES ('1', 'li pengwei', '30');
INSERT INTO `tongshi1` VALUES ('2', 'li changyang', '31');
INSERT INTO `tongshi1` VALUES ('3', 'hu kuanguang', '27');
INSERT INTO `tongshi1` VALUES ('4', 'wang huijin', '20');
INSERT INTO `tongshi1` VALUES ('5', 'zhang chen', '25');
INSERT INTO `tongshi1` VALUES ('6', 'zhang lei', '27');
INSERT INTO `tongshi1` VALUES ('7', 'liu hua', '33');
INSERT INTO `tongshi1` VALUES ('8', 'song fang', '42');
INSERT INTO `tongshi1` VALUES ('9', 'hu lei', '50');
INSERT INTO `tongshi1` VALUES ('10', 'yang chuang', '43');
创建tongshi2表的记录
INSERT INTO `tongshi2` VALUES ('dong hang', 'xiaoshou', '4500');
INSERT INTO `tongshi2` VALUES ('hu kuanguang', 'gongchengshi', '6000');
INSERT INTO `tongshi2` VALUES ('li changyang', 'zhu guan', '8000');
INSERT INTO `tongshi2` VALUES ('li pengwei ', 'cto', '10000');
INSERT INTO `tongshi2` VALUES ('liu hua', 'ceo', '30000');
INSERT INTO `tongshi2` VALUES ('song fang', 'xiaoshou', '11000');
INSERT INTO `tongshi2` VALUES ('wang huijin', 'gongchengshi', '5000');
INSERT INTO `tongshi2` VALUES ('yang chuang', 'cio', '12000');
INSERT INTO `tongshi2` VALUES ('zhang chen', 'gongchengshi', '5500');
INSERT INTO `tongshi2` VALUES ('zhang jun', 'xiaoshou', '3500');
3、SQL连接
sql的内连接、外连接和自连接查询
自连接
自然连接:两张表中的名称和类型完全一致的列进行内连接
自连接,连接的两个表都是同一个表,同样可以由内连接,外连接各种组合方式,按实际应用去组合。
select a.* ,b.* from tongshi1 a,tongshi2 b where a.name = b.name;
内连接查询:
内连接:查询出来的结果肯定会满足所有的条件
方法1
select tongshi1.* ,tongshi2.* from tongshi1,tongshi2 where tongshi1.name = tongshi2.name;
方法2
select a.* ,b.* from tongshi1 a inner join tongshi2 b on a.name=b.name;
方法3
select a.* ,b.* from tongshi1 a join tongshi2 b on a.name=b.name;
外连接查询
左/右外连接:查询出来的结果存在不满足条件的可能
左连接
左连接:根据左表的记录,在被连接的右表中找出符合条件的记录与之匹配,如果找不到与左表匹配的,用null表示。
第一种写法:(left join .. on ..)
select a.name,a.age,b.post ,b.money from tongshi1 a left join tongshi2 b on a.name=b.name;
第二种写法:(left outer join .. on ..)
select a.name,a.age,b.post ,b.money from tongshi1 a left outer join tongshi2 b on a.name=b.name;
右连接:根据右表的记录,在被连接的左表中找出符合条件的记录与之匹配,如果找不到匹配的,用null填充。
第一种写法:(right join .. on ..)
select a.name,a.age,b.post ,b.money from tongshi1 a right join tongshi2 b on a.name = b.name;
第二种写法:(right outer join .. on ..)
select a.name,a.age,b.post ,b.money from tongshi1 a right outer join tongshi2 b on a.name=b.name;
全连接:返回符合条件的所有表的记录,没有与之匹配的,用null表示(结果是左连接和右连接的并集)
全外链接:MySQL不支持,Oracle支持
第一种写法:(full join .. on ..)
select a.name,a.age,b.post ,b.money from tongshi1 a full join tongshi2 b on a.name = b.name;
第二种写法:(full outer join .. on)
select a.name,a.age,b.post ,b.money from tongshi1 a full outer join tongshi2 b on a.name = b.name;