MySql连接的使用
首先,创建两个表
CREATE TABLE `t_emp` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT NULL,
`address` varchar(50) DEFAULT NULL,
`salary` double DEFAULT NULL,
`sex` char(1) DEFAULT NULL,
`dept_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8;
CREATE TABLE `t_dept` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`deptname` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
插入数据,方便连接操作,表数据:
- t_emp表
+----+----------+------------+--------+-----+---------+
| id | name | address | salary | sex | dept_id |
+----+----------+------------+--------+-----+---------+
| 2 | tom | 河北石家庄 | 7690 | f | 1 |
| 3 | xiaoming | 山西太原 | 6000 | m | 2 |
| 4 | sanli | 宁夏吴中 | 10500 | f | 2 |
| 5 | wuzhi | 湖南张家界 | 7600 | f | 1 |
| 6 | mmc | 陕西商洛 | NULL | m | 2 |
| 7 | _huan | 湖南张家界 | 8500 | f | 1 |
| 8 | liehui | 湖南张家界 | NULL | m | 1 |
| 15 | liangzai | 广东佛山 | 8500 | m | 1 |
| 16 | Tom | 湖南怀化 | NULL | f | 3 |
| 18 | ma | 湖南怀化 | 10000 | f | 2 |
| 19 | ts | 广东佛山 | 7800 | m | 3 |
| 20 | cat | 陕西西安 | 9000 | f | 2 |
| 21 | salry | 美国旧金山 | 27900 | m | 2 |
| 22 | Jam | 印度平民窟 | 1000 | m | 3 |
+----+----------+------------+--------+-----+---------+
14 rows in set
- t_dept表
+----+----------+
| id | deptname |
+----+----------+
| 1 | 财务部 |
| 2 | 人事部 |
| 3 | 销售部 |
+----+----------+
3 rows in set
- 内连接/等值连接(INNER JOIN ON)
获取两张表中字段匹配的记录
1.这里的inner可以省略,效果是一样的
select * from t_emp e inner join t_dept d on e.dept_id=d.id;
2.也可以这样写,带where字句
select * from t_emp e join t_dept d where e.dept_id=d.id;
3.以上两个得到的结果相同
+----+----------+------------+--------+-----+---------+----+----------+
| id | name | address | salary | sex | dept_id | id | deptname |
+----+----------+------------+--------+-----+---------+----+----------+
| 2 | tom | 河北石家庄 | 7690 | f | 1 | 1 | 财务部 |
| 3 | xiaoming | 山西太原 | 6000 | m | 2 | 2 | 人事部 |
| 4 | sanli | 宁夏吴中 | 10500 | f | 2 | 2 | 人事部 |
| 5 | wuzhi | 湖南张家界 | 7600 | f | 1 | 1 | 财务部 |
| 6 | mmc | 陕西商洛 | NULL | m | 2 | 2 | 人事部 |
| 7 | _huan | 湖南张家界 | 8500 | f | 1 | 1 | 财务部 |
| 8 | liehui | 湖南张家界 | NULL | m | 1 | 1 | 财务部 |
| 15 | liangzai | 广东佛山 | 8500 | m | 1 | 1 | 财务部 |
| 16 | Tom | 湖南怀化 | NULL | f | 3 | 3 | 销售部 |
| 18 | ma | 湖南怀化 | 10000 | f | 2 | 2 | 人事部 |
| 19 | ts | 广东佛山 | 7800 | m | 3 | 3 | 销售部 |
| 20 | cat | 陕西西安 | 9000 | f | 2 | 2 | 人事部 |
| 21 | salry | 美国旧金山 | 27900 | m | 2 | 2 | 人事部 |
| 22 | Jam | 印度平民窟 | 1000 | m | 3 | 3 | 销售部 |
+----+----------+------------+--------+-----+---------+----+----------+
14 rows in set
- 外连接(OUTER JOIN)
- 左外连接/左连接
1.左连接:LEFT OUTER JOIN ON
2.左外连接:LEFT JOIN ON
3.左连接是左外连接的缩写
- 结果:
左表数据会全部显示 + 右表符合搜索条件的数据,右表数据不足的地方为NULL
1.左外连接
select e.name name,e.address address,e.salary salary,d.deptname
from t_emp e left outer join t_dept d
on e.dept_id = d.id;
2.左连接
select e.name name,e.address address,e.salary salary,d.deptname
from t_emp e left join t_dept d
on e.dept_id=d.id;
3.两次结果相同,可以将on改为where
+----------+------------+--------+----------+
| name | address | salary | deptname |
+----------+------------+--------+----------+
| tom | 河北石家庄 | 7690 | 财务部 |
| wuzhi | 湖南张家界 | 7600 | 财务部 |
| _huan | 湖南张家界 | 8500 | 财务部 |
| liehui | 湖南张家界 | NULL | 财务部 |
| liangzai | 广东佛山 | 8500 | 财务部 |
| xiaoming | 山西太原 | 6000 | 人事部 |
| sanli | 宁夏吴中 | 10500 | 人事部 |
| mmc | 陕西商洛 | NULL | 人事部 |
| ma | 湖南怀化 | 10000 | 人事部 |
| cat | 陕西西安 | 9000 | 人事部 |
| salry | 美国旧金山 | 27900 | 人事部 |
| Tom | 湖南怀化 | NULL | 销售部 |
| ts | 广东佛山 | 7800 | 销售部 |
| Jam | 印度平民窟 | 1000 | 销售部 |
+----------+------------+--------+----------+
- 右外连接(RIGHT OUTER JOIN ON)
1.右连接:LEFT OUTER JOIN ON
2.右外连接:LEFT JOIN ON
3.右连接是右外连接的缩写
- 结果
与左连接相反,右表的全部数据+左表符合搜索条件的数据,左表其他不足数据用NULL表示
1.右连接
select * from t_emp e right join t_dept d on e.dept_id=d.id;
2.右外连接
select * from t_emp e right outer join t_dept d on e.dept_id=d.id;
3.结果相同
+----+----------+------------+--------+-----+---------+----+----------+
| id | name | address | salary | sex | dept_id | id | deptname |
+----+----------+------------+--------+-----+---------+----+----------+
| 2 | tom | 河北石家庄 | 7690 | f | 1 | 1 | 财务部 |
| 3 | xiaoming | 山西太原 | 6000 | m | 2 | 2 | 人事部 |
| 4 | sanli | 宁夏吴中 | 10500 | f | 2 | 2 | 人事部 |
| 5 | wuzhi | 湖南张家界 | 7600 | f | 1 | 1 | 财务部 |
| 6 | mmc | 陕西商洛 | NULL | m | 2 | 2 | 人事部 |
| 7 | _huan | 湖南张家界 | 8500 | f | 1 | 1 | 财务部 |
| 8 | liehui | 湖南张家界 | NULL | m | 1 | 1 | 财务部 |
| 15 | liangzai | 广东佛山 | 8500 | m | 1 | 1 | 财务部 |
| 16 | Tom | 湖南怀化 | NULL | f | 3 | 3 | 销售部 |
| 18 | ma | 湖南怀化 | 10000 | f | 2 | 2 | 人事部 |
| 19 | ts | 广东佛山 | 7800 | m | 3 | 3 | 销售部 |
| 20 | cat | 陕西西安 | 9000 | f | 2 | 2 | 人事部 |
| 21 | salry | 美国旧金山 | 27900 | m | 2 | 2 | 人事部 |
| 22 | Jam | 印度平民窟 | 1000 | m | 3 | 3 | 销售部 |
+----+----------+------------+--------+-----+---------+----+----------+
14 rows in set
- 交叉连接(CROSS JOIN)
其实,就是两个表做笛卡尔积
笛卡尔积:用 A表 的每一行和 B表 的每一行进行组合,形成一张新的表
select * from t_emp cross join t_dept;
1.t_emp:14行数据
2.t_dept:3行数据
3.结果:14x3=42行
+----+----------+------------+--------+-----+---------+----+----------+
| id | name | address | salary | sex | dept_id | id | deptname |
+----+----------+------------+--------+-----+---------+----+----------+
| 2 | tom | 河北石家庄 | 7690 | f | 1 | 1 | 财务部 |
| 2 | tom | 河北石家庄 | 7690 | f | 1 | 2 | 人事部 |
| 2 | tom | 河北石家庄 | 7690 | f | 1 | 3 | 销售部 |
| 3 | xiaoming | 山西太原 | 6000 | m | 2 | 1 | 财务部 |
| 3 | xiaoming | 山西太原 | 6000 | m | 2 | 2 | 人事部 |
| 3 | xiaoming | 山西太原 | 6000 | m | 2 | 3 | 销售部 |
| 4 | sanli | 宁夏吴中 | 10500 | f | 2 | 1 | 财务部 |
| 4 | sanli | 宁夏吴中 | 10500 | f | 2 | 2 | 人事部 |
| 4 | sanli | 宁夏吴中 | 10500 | f | 2 | 3 | 销售部 |
| 5 | wuzhi | 湖南张家界 | 7600 | f | 1 | 1 | 财务部 |
| 5 | wuzhi | 湖南张家界 | 7600 | f | 1 | 2 | 人事部 |
| 5 | wuzhi | 湖南张家界 | 7600 | f | 1 | 3 | 销售部 |
| 6 | mmc | 陕西商洛 | NULL | m | 2 | 1 | 财务部 |
| 6 | mmc | 陕西商洛 | NULL | m | 2 | 2 | 人事部 |
| 6 | mmc | 陕西商洛 | NULL | m | 2 | 3 | 销售部 |
| 7 | _huan | 湖南张家界 | 8500 | f | 1 | 1 | 财务部 |
| 7 | _huan | 湖南张家界 | 8500 | f | 1 | 2 | 人事部 |
| 7 | _huan | 湖南张家界 | 8500 | f | 1 | 3 | 销售部 |
| 8 | liehui | 湖南张家界 | NULL | m | 1 | 1 | 财务部 |
| 8 | liehui | 湖南张家界 | NULL | m | 1 | 2 | 人事部 |
| 8 | liehui | 湖南张家界 | NULL | m | 1 | 3 | 销售部 |
| 15 | liangzai | 广东佛山 | 8500 | m | 1 | 1 | 财务部 |
| 15 | liangzai | 广东佛山 | 8500 | m | 1 | 2 | 人事部 |
| 15 | liangzai | 广东佛山 | 8500 | m | 1 | 3 | 销售部 |
| 16 | Tom | 湖南怀化 | NULL | f | 3 | 1 | 财务部 |
| 16 | Tom | 湖南怀化 | NULL | f | 3 | 2 | 人事部 |
| 16 | Tom | 湖南怀化 | NULL | f | 3 | 3 | 销售部 |
| 18 | ma | 湖南怀化 | 10000 | f | 2 | 1 | 财务部 |
| 18 | ma | 湖南怀化 | 10000 | f | 2 | 2 | 人事部 |
| 18 | ma | 湖南怀化 | 10000 | f | 2 | 3 | 销售部 |
| 19 | ts | 广东佛山 | 7800 | m | 3 | 1 | 财务部 |
| 19 | ts | 广东佛山 | 7800 | m | 3 | 2 | 人事部 |
| 19 | ts | 广东佛山 | 7800 | m | 3 | 3 | 销售部 |
| 20 | cat | 陕西西安 | 9000 | f | 2 | 1 | 财务部 |
| 20 | cat | 陕西西安 | 9000 | f | 2 | 2 | 人事部 |
| 20 | cat | 陕西西安 | 9000 | f | 2 | 3 | 销售部 |
| 21 | salry | 美国旧金山 | 27900 | m | 2 | 1 | 财务部 |
| 21 | salry | 美国旧金山 | 27900 | m | 2 | 2 | 人事部 |
| 21 | salry | 美国旧金山 | 27900 | m | 2 | 3 | 销售部 |
| 22 | Jam | 印度平民窟 | 1000 | m | 3 | 1 | 财务部 |
| 22 | Jam | 印度平民窟 | 1000 | m | 3 | 2 | 人事部 |
| 22 | Jam | 印度平民窟 | 1000 | m | 3 | 3 | 销售部 |
+----+----------+------------+--------+-----+---------+----+----------+
42 rows in set
sql语句练习
1.两表连接,查询以下结果
等值连接,找到salary在5000-9500的数据
+----------+------------+--------+-----+----------+
| name | address | salary | sex | deptname |
+----------+------------+--------+-----+----------+
| tom | 河北石家庄 | 7690 | f | 财务部 |
| xiaoming | 山西太原 | 6000 | m | 财务部 |
| wuzhi | 湖南张家界 | 7600 | f | 财务部 |
| _huan | 湖南张家界 | 8500 | f | 财务部 |
| liangzai | 广东佛山 | 8500 | m | 财务部 |
| ts | 广东佛山 | 7800 | m | 财务部 |
| cat | 陕西西安 | 9000 | f | 财务部 |
| tom | 河北石家庄 | 7690 | f | 人事部 |
| xiaoming | 山西太原 | 6000 | m | 人事部 |
| wuzhi | 湖南张家界 | 7600 | f | 人事部 |
| _huan | 湖南张家界 | 8500 | f | 人事部 |
| liangzai | 广东佛山 | 8500 | m | 人事部 |
| ts | 广东佛山 | 7800 | m | 人事部 |
| cat | 陕西西安 | 9000 | f | 人事部 |
| tom | 河北石家庄 | 7690 | f | 销售部 |
| xiaoming | 山西太原 | 6000 | m | 销售部 |
| wuzhi | 湖南张家界 | 7600 | f | 销售部 |
| _huan | 湖南张家界 | 8500 | f | 销售部 |
| liangzai | 广东佛山 | 8500 | m | 销售部 |
| ts | 广东佛山 | 7800 | m | 销售部 |
| cat | 陕西西安 | 9000 | f | 销售部 |
+----------+------------+--------+-----+----------+
21 rows in set
select e.name name,e.address address,e.salary salary,e.sex sex,
d.deptname from t_emp e join t_dept d where salary between
5000 and 9500;
1.展示数据库字段信息
show columns from t_dept;
等价于
desc t_dept;
+----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| deptname | varchar(100) | YES | | NULL | |
+----------+--------------+------+-----+---------+----------------+
2 rows in set
1.展示创建表的sql语句
show create table t_dept;
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t_dept | CREATE TABLE `t_dept` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`deptname` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set
共同成长,共同进步,欢迎大家批评指正~