mysql:多表查询

目录

笛卡尔积：

多表查询总简介：

内连接：

        1.内连接查询语法

外连接：

        1.外连接查询语法

子查询（嵌套查询）：

多表查询案例一

        笛卡尔积结果 

        1.1 和1.4结果

        1.2和1.3的结果

        2.1.1结果

        2.2.1结果

        3.1.1结果

        3.2.1结果

        3.3.1结果

多表查询案例二

        案例中4个表的关系

        案例第一题答案

        案例第二题答案

        案例第三题答案

        案例第四题答案

        案例第五题答案

---

笛卡尔积：

        取A,B集合所有的组合情况。

多表查询总简介：

        1.连接查询

                1)内连接：相当于查询A,B表的交集数据。

                2)外连接：

                                1>右外连接：相当于查询A表所有数据和交集部分数据。

                                2>左外连接：相当于查询B表所有数据和交集部分数据。

        2.子查询

                                注：根据查询结果不同，作用不同：1.单行单列，2.多行单列，3.多行多列

内连接：

        1.内连接查询语法

-- 隐式内连接
select 字段列表 from 表1，表2 where 条件;
​
-- 显式内连接
select 字段列表 from 表1 [inner] join 表2 on 条件

外连接：

        1.外连接查询语法

-- 左外连接    相当于查询B表所有数据和交集部分数据。
select 字段列表 from left [outer] join 表2 on 条件
​
-- 右外连接    相当于查询A表所有数据和交集部分数据。
select 字段列表 from right [outer] join 表2 on 条件

子查询（嵌套查询）：

        注：根据查询结果不同，作用不同：1.单行单列，2.多行单列，3.多行多列

-- 单行单列   作为条件值，使用= != > <
select 字段列表 from 表 where 字段名=(子查询);
​
-- 多行单列   作为条件值,使用in等关键字进行判断
select 字段列表 from 表 where 字段名 in (子查询);
​
-- 多行多列   作为虚拟表
select 字段列表 from (子查询) where 条件;

多表查询案例一

-- 案例一
drop table if exists emp;
drop table if exists dept;
-- 创建部门表
create table dept(
    did int PRIMARY KEY auto_increment,
    dname varchar(20)
);
-- 创建员工表
create table emp(
    id int PRIMARY KEY auto_increment,
    name varchar(10),
    gender char(1),
    salary double,
    join_date date,
    dep_id int,
    FOREIGN KEY(dep_id) REFERENCES dept(did)
);
-- 添加部门数据
insert into dept(dname) values ('研发部'),('市场部'),('财务部'),('销售部');
-- 添加员工数据
insert into emp(name,gender,salary,join_date,dep_id) values
('孙悟空','男',7200,'2013-02-24',1),
('猪八戒','男',3600,'2010-12-02',2),
('唐僧','男',9000,'2008-08-08',2),
('白骨精','女',5000,'2015-10-07',3),
('蜘蛛精','女',4500,'2011-03-14',1),
('小白龙','男',2500,'2011-02-14',null);
​
​
-- 多表查询
-- 笛卡尔积：有A，B两个集合，取A,B所有组合情况，4*6条数据
select * from emp,dept;
-- 连接查询
-- 1.内连接
-- 1.1 隐式内连接   select 字段列表 from 表1，表2 where 条件;
select * from emp,dept where emp.dep_id=dept.did;
-- 1.2 查询emp的name，gender，dept表的dname
select emp.name,emp.gender,dept.dname from emp,dept where emp.dep_id=dept.did;
-- 1.3 给表起别名，emp为t1，dept为t2    
select t1.name,t1.gender,t2.dname from emp t1,dept t2 where t1.dep_id=t2.did;
-- 1.4 显式内连接   select 字段列表 from 表1 [inner] join 表2 on 条件  注：inner可以省略
select * from emp inner join dept on emp.dep_id=dept.did;
-- 2 外连接
-- 2.1 左外连接   select 字段列表 from left [outer] join 表2 on 条件
-- 2.1.1 查询emp表所有数据和对应的部门信息
select * from emp left outer join dept on emp.dep_id=dept.did;
select * from dept left outer join emp on emp.dep_id=dept.did;
-- 2.2 右外连接   select 字段列表 from right [outer] join 表2 on 条件
-- 2.2.1 查询dept表的所有数据和对应的员工信息
select * from dept right outer join emp on emp.dep_id=dept.did;
select * from emp right outer join dept on emp.dep_id=dept.did;
​
​
-- 3.子查询（嵌套查询）
​
-- 3.1 单行单列   select 字段列表 from 表 where 字段名=(子查询);
-- 3.1.1查询工资高于猪八戒的员工信息    查询猪八戒的工资，查询工资高于猪八戒的员工信息
select salary from emp where name='猪八戒';
select * from emp where salary>3600;
-- 合二为一：
select * from emp where salary>(select salary from emp where name='猪八戒');
​
-- 3.2 多行单列   select 字段列表 from 表 where 字段名 in (子查询);
-- 3.2.1 查询'财务部'和'市场部'所有的员工信息
select * from emp where dep_id in (select did from dept where dname='财务部' or dname='市场部');
-- 3.3 多行多列   select 字段列表 from (子查询) where 条件;  作为虚拟表
-- 3.3.1 查询入职日期是'2011-11-11'之后的员工信息和部门
select * from (select * from emp where join_date>'2011-11-11') t1,dept where t1.dep_id=dept.did;

        笛卡尔积结果 

        1.1 和1.4结果

        1.2和1.3的结果

        2.1.1结果

        2.2.1结果

        3.1.1结果

 

        3.2.1结果

 

        3.3.1结果

 

多表查询案例二

drop table if exists emp;
drop table if exists dept;
drop table if exists job;
drop table if exists salarygrade;
​
​
-- 部门表
create table dept(
    id int PRIMARY KEY auto_increment,  -- 部门id
    dname varchar(50),  -- 部门名称
    loc varchar(50)  -- 部门所在地
);
​
-- 职务表
create table job(
    id int PRIMARY KEY auto_increment,
    jname varchar(20),  -- 职务名称
    description varchar(50) -- 职务描述
);
​
-- 员工表
create table emp(
    id int PRIMARY KEY auto_increment,
    ename varchar(50),
    job_id int, -- 职务编号
    mgr int,-- 上级领导
    joindate date,  -- 入职日期
    salary decimal(7,2),    -- 工资
    bonus decimal(7,2), -- 奖金
    dept_id int,    -- 所在部门编号
    -- salarygrade_grade int,
    constraint fk_emp_job foreign key(job_id) references job(id),
    constraint fk_emp_dept foreign key(dept_id) references dept(id)
    -- constraint fk_emp_salarygarde foreign key(salarygrade_grade) references salarygrade(grade),
);
​
-- 工资等级表
create table salarygrade(
    grade int PRIMARY KEY auto_increment,  -- 级别
    losalary int, -- 最低工资
    hisalary int, -- 最高工资
);
​
-- 添加4个部门
insert into dept(id,dname,loc) values
(10,'教育部','北京'),
(20,'学工部','上海'),
(30,'销售部','广州'),
(40,'财务部','深圳');
​
-- 添加4个职务
insert into job(id,jname,description) values
(1,'董事长','管理整个公司，接单'),
(2,'经理','管理部门员工'),
(3,'销售员','向客人推销产品'),
(4,'文员','使用办公软件');
​
​
-- 添加员工
insert into emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) values
(1001,'孙悟空',4,1004,'2000-12-17','8000.00',null,20),
(1002,'卢俊义',3,1006,'2001-02-20','16000.00',3000.00,30),
(1003,'林冲',3,1006,'2001-02-22','12500.00',5000.00,30),
(1004,'唐僧',2,1009,'2001-04-02','29750.00',null,20),
(1005,'李逵',4,1006,'2001-09-28','12500.00',14000.00,30),
(1006,'宋江',2,1009,'2001-05-01','28500.00',null,30),
(1007,'刘备',2,1009,'2001-09-01','24500.00',null,10),
(1008,'猪八戒',4,1004,'2007-04-19','30000.00',null,20),
(1009,'罗贯中',1,null,'2001-11-17','50000.00',null,10),
(1010,'吴用',3,1006,'2001-09-08','15000.00',0.00,30),
(1011,'沙僧',4,1004,'2007-05-23','11000.00',null,20),
(1012,'李逵',4,1006,'2001-12-03','9500.00',null,30),
(1013,'小白龙',4,1004,'2001-12-03','30000.00',null,20),
(1014,'关羽',4,1007,'2002-12-23','13000.00',null,10);
​
​
-- 添加5个工资等级
insert into salarygrade(grade,losalary,hisalary) values
(1,7000,12000),
(2,12010,14000),
(3,14010,20000),
(4,20010,30000),
(5,30010,99990);
​
​
​
-- 1.查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述
select emp.id,ename,salary,jname,description from emp,job where emp.job_id=job.id;
​
-- 2.查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置
select emp.id,ename,salary,jname,description,dname,loc from emp,job,dept where emp.job_id=job.id and emp.dept_id=dept.id;
​
-- 3.查询员工姓名，工资，工资等级
-- 法1 
select ename,salary,grade from emp,salarygrade t2 where salary>=t2.losalary and salary<=t2.hisalary;
-- 法2
select ename,salary,grade from emp,salarygrade t2 where salary between t2.losalary and t2.hisalary;
-- 法3
select ename,salary,grade from emp inner join salarygrade t2 on salary between t2.losalary and t2.hisalary;
-- 4.查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
-- 法1
select ename,salary,jname,description,dname,loc,grade from emp,job,dept,salarygrade t4 where emp.job_id=job.id and emp.dept_id=dept.id and salary>=t4.losalary and salary<=t4.hisalary;
-- 法2
select ename,salary,jname,description,dname,loc,grade from emp 
inner join job on emp.job_id=job.id 
inner join dept on emp.dept_id=dept.id
inner join salarygrade t2 on salary between t2.losalary and t2.hisalary;
-- 5.查询出部门编号，部门名称，部门位置，部门人数   ---子查询
-- 分析 select * from dept;   select dept_id,count(*) from emp group by dept_id; 让部门表和分组后的员工表进行子查询
​
select dept.id,dname,loc,t2.count from dept,(select dept_id,count(*) as count from emp group by dept_id) t2 where dept.id=t2.dept_id;

​

        案例中4个表的关系

 

        案例第一题答案

 

        案例第二题答案

 

        案例第三题答案

 

        案例第四题答案

 

        案例第五题答案