MySQL数据操作之多表查询
16lz
2021-01-22
一、准备表
#建表
create table dept(
id int primary key not null auto_increment,
name varchar(20) not null
);
create table employee(
id int primary key auto_increment,
name varchar(20) not null,
sex enum('male','female') not null default 'male',
age int not null ,
dept_id int not null,
foreign key(dept_id) references dept(id)
on delete cascade
on update cascade
);
#插入数据
insert into dept(name) values
('技术'),
('人力资源'),
('销售'),
('运营');
insert into employee(name,sex,age,dept_id) values
('egon','male',18,1),
('alex','female',48,2),
('wupeiqi','male',38,3),
('yuanhao','female',28,4),
('liwenzhou','male',18,1),
('jingliyang','female',18,3)
;
#查看表结构和数据
mysql> desc department;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
mysql> desc employee;
+---------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| sex | enum('male','female') | NO | | male | |
| age | int(11) | NO | | NULL | |
| dept_id | int(11) | NO | MUL | NULL | |
+---------+-----------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> select * from dept;
+----+----------+
| id | name |
+----+----------+
| 1 | 技术 |
| 2 | 人力资源 |
| 3 | 销售 |
| 4 | 运营 |
+----+----------+
4 rows in set (0.00 sec)
mysql> select * from employee;
+----+------------+--------+-----+---------+
| id | name | sex | age | dept_id |
+----+------------+--------+-----+---------+
| 1 | egon | male | 18 | 1 |
| 2 | alex | female | 48 | 2 |
| 3 | wupeiqi | male | 38 | 3 |
| 4 | yuanhao | female | 28 | 4 |
| 5 | liwenzhou | male | 18 | 1 |
| 6 | jingliyang | female | 18 | 3 |
+----+------------+--------+-----+---------+
6 rows in set (0.00 sec)
表department与employee二、多表连接查询
重点：外链接语法
SELECT 字段列表
FROM 表1 INNER|LEFT|RIGHT JOIN 表2
ON 表1.字段 = 表2.字段;1 交叉连接：不适用任何匹配条件。生成笛卡尔积,结果没有任何参考作用；
mysql> select * from dept, employee;
+----+----------+----+------------+--------+-----+---------+
| id | name | id | name | sex | age | dept_id |
+----+----------+----+------------+--------+-----+---------+
| 1 | 技术 | 1 | egon | male | 18 | 1 |
| 2 | 人力资源 | 1 | egon | male | 18 | 1 |
| 3 | 销售 | 1 | egon | male | 18 | 1 |
| 4 | 运营 | 1 | egon | male | 18 | 1 |
| 1 | 技术 | 2 | alex | female | 48 | 2 |
| 2 | 人力资源 | 2 | alex | female | 48 | 2 |
| 3 | 销售 | 2 | alex | female | 48 | 2 |
| 4 | 运营 | 2 | alex | female | 48 | 2 |
| 1 | 技术 | 3 | wupeiqi | male | 38 | 3 |
| 2 | 人力资源 | 3 | wupeiqi | male | 38 | 3 |
| 3 | 销售 | 3 | wupeiqi | male | 38 | 3 |
| 4 | 运营 | 3 | wupeiqi | male | 38 | 3 |
| 1 | 技术 | 4 | yuanhao | female | 28 | 4 |
| 2 | 人力资源 | 4 | yuanhao | female | 28 | 4 |
| 3 | 销售 | 4 | yuanhao | female | 28 | 4 |
| 4 | 运营 | 4 | yuanhao | female | 28 | 4 |
| 1 | 技术 | 5 | liwenzhou | male | 18 | 1 |
| 2 | 人力资源 | 5 | liwenzhou | male | 18 | 1 |
| 3 | 销售 | 5 | liwenzhou | male | 18 | 1 |
| 4 | 运营 | 5 | liwenzhou | male | 18 | 1 |
| 1 | 技术 | 6 | jingliyang | female | 18 | 3 |
| 2 | 人力资源 | 6 | jingliyang | female | 18 | 3 |
| 3 | 销售 | 6 | jingliyang | female | 18 | 3 |
| 4 | 运营 | 6 | jingliyang | female | 18 | 3 |
+----+----------+----+------------+--------+-----+---------+
24 rows in set (0.00 sec)更多相关文章
- 2008技术内幕：T-SQL语言基础 单表查询摘记
- 专访周彦伟：十年技术老兵谈为什么MySQL最流行及DBA精神
- 直播技术（从服务端到客户端）一
- android apk 防止反编译技术第四篇-对抗JD-GUI
- [置顶] 欢迎加入我们的Java技术交流群
- 【Android应用开发技术：应用组件】Android事件处理机制
- 面试之菜鸟吐槽技术总监
- Linux（Android）：如何禁用Intel DPST（显示节电技术） - i915驱动程序
- android:使用网络通信技术从客户端直接获取服务端的对象数据