数据库（MySQL）之多表查询（三）

数据库（MySQL）之多表查询

1.创建表

-- 多表联合查询 
-- 创建三个表，并进行插入数据 
-- 创建emp表，并插入数据 
CREATE TABLE emp  (  
eno int(5),-- 员工号  
ename varchar(40),-- 名字  
sal double,-- 薪水  
hiredate date COMMENT '入职时间',-- comment 注释  
dno int(5) -- 部门编号 ); 
INSERT INTO emp VALUES (1001, '易大师', 3501.06, '2018-10-01', 2); 
INSERT INTO emp VALUES (1002, '埃尔咋哈', 8500.09, '2019-08-06', 2); 
INSERT INTO emp VALUES (1004, '艾希', 9566.8, '2017-11-11', 4); 
INSERT INTO emp VALUES (1005, '墨菲特', 4300, '2018-01-22', 2); 
INSERT INTO emp VALUES (1007, '波比', 6500, '2014-11-14', 2); 
INSERT INTO emp VALUES (1008, '艾伦', 7899, '2018-10-10', 3); 
INSERT INTO emp VALUES (1009, '赵信', 9800, '2019-08-07', 3); 
-- 创建dept表，并插入数据 
CREATE TABLE dept  (  
dno int(5),  
dname varchar(40),  
tel char(11) ) 
INSERT INTO dept VALUES (2, '市场部', '10010'); 
INSERT INTO dept VALUES (3, '研发部', '10086');
INSERT INTO dept VALUES (4, '行政部', '10000'); 
INSERT INTO dept VALUES (5, '后勤部', '10987'); 
-- 创建sal_info表          
CREATE TABLE sal_info  (  
eno int(5),-- 员工的编号  
basesal double,-- 基本薪资  
carsal double,-- 交通补贴  
housesal double,-- 住房补贴  
eatsal double,-- 餐补  
telsal double -- 话费补贴 ); 
INSERT INTO sal_info VALUES (1001, 3500, 500, 300, 300, 200); 
INSERT INTO sal_info VALUES (1002, 2800, 400, 200, 300, 300); 
INSERT INTO sal_info VALUES (1004, 3400, 500, 300, 300, 500); 
INSERT INTO sal_info VALUES (1005, 2200, 200, 200, 300, 500); 
INSERT INTO sal_info VALUES (1007, 1800, 100, 100, 300, 400); 
INSERT INTO sal_info VALUES (1008, 1500, 100, 100, 300, 200); 
INSERT INTO sal_info VALUES (1009, 5000, 1000, 800, 500, 1000);

2.连接类型语句

-- 查询出所有的员工信息以及部门信息？ 
select * from emp as e,dept d where e.dno=d.dno; 
-- 查询出所有的员工信息部门信息和薪资构成信息？ 
select * from emp e,dept d,sal_info s where e.dno=d.dno and e.eno=s.eno;
-- 针对于查询需要分析出三个主要目标: 
-- 1. 查询列 
-- 2. 查询目标表(虚拟表) 
-- 3. 查询条件
-- 表与表之间的关联 
-- join...on...用来将多个表关联到一起，on后面需要写通过什么字段关联 
-- 驱动表和匹配表 
-- t1表 join t2表 on 条件   
-- t1表叫做驱动表  t2表叫做匹配表 
-- 在等值连接下驱动表和匹配表可以互换，不会影响结果 
-- 内连接和外连接
-- 内连接inner（等值连接属于内连接） 
-- 查询出所有的员工信息以及部门信息 
select * from emp inner join dept on emp.dno=dept.dno; 
select * from dept inner join emp on emp.dno=dept.dno;
-- 外连接 
-- 左外连接 
语法结构：表1 left outer join 表2 on 条件 
-- 右外连接 语法结构：表1 right outer join 表2 on 条件 
-- 全外连接 语法结构：表1 full outer join 表2 on 条件 
-- 外连接的特点： 
-- 如果驱动表在匹配表中找不到匹配记录，则匹配一行空行 
-- 外连接驱动表中的数据一个都不能少 
-- left outer join：以左边的表为驱动表 
-- right outer join：以右边的表为驱动表 
-- full outer join：全外连接驱动表和匹配表的位置可以互换 
-- full outer join：Mysql不支持这个关键字，可以通过union实现
-- 查询出所有的员工信息部门信息，以及没有部门的员工信息 
select * from emp left outer join dept on emp.dno=dept.dno; 
-- 查询出所有的员工信息部门信息，以及没有员工的部门信息 
select * from emp right outer join dept on emp.dno=dept.dno;

3.练习

1.5，作业 
-- 查询出所有的员工信息部门信息（全外连接） 
select * from emp left outer join dept on emp.dno=dept.dno union select * from emp right outer join dept on emp.dno=dept.dno;
-- 查询出所有研发部员工的姓名，薪资，入职时间以及部门电话？ 
select e.ename,e.sal,e.hiredate,d.tel from emp e,dept d where e.dno=d.dno and d.dname='研发部'; 
-- join...on... select e.ename,e.sal,e.hiredate,d.tel from emp e join dept d on e.dno=d.dno and d.dname='研发部';
-- 子查询（某些情况下,当进行查询的时候,需要的条件是另外一个select语句的结果,这个时候就会用到子 查询,为了给主查询(外部查询) 提供数据而首先执行的查询(内部查询)被叫做子查询） 
-- 显示研发部的所有员工？ 
-- 先查找研发部的部门编号 select dno from dept where dname='研发部'; 
-- 根据部门编号去员工表中查询结果 
select * from emp where dno = 3; 
-- select * from emp where dno = (select dno from dept where dname='市场部');
-- 统计每个部门中员工人数并显示部门编号？ 
select dno,count(*) from emp group by dno; 
-- 统计每个部门中员工人数并显示部门名称？ 
select d.dname,count(*) from emp e,dept d where e.dno=d.dno group by e.dno;
-- 统计每个部门的员工人数并显示部门的名称？（复杂子查询） 
select t.dno,t.ct,d.dname from (select dno,count(*) ct from emp group by dno) t, dept d where d.dno = t.dno;
-- 查询出研发部和市场部的所有员工（使用子查询完成） 
select dno from dept where dname='研发部' or dname='市场部';
select * from emp where dno in (select dno from dept where dname='研发部' or dname='市场部');
-- 查询出1001/1002/1005/1009这几位员工的信息 
select * from emp where eno in (1001,1002,1005,1009);