mysql多表查询

笛卡尔积

 * 有两个集合，他们里面的额元素的所有组合情况就是迪卡尔积

多表查询

 * 内连接查询
     1.隐式内连接：
     2.显式内连接：
        select 字段列表 from 表1 inner join 表2 on 条件
        inner 可省略
     3.内连接的使用步骤
         1)  确定查询哪些表
         2)  确定表连接的条件
         3)  确定查询的条件
         4)  确定查询的字段
 * 外连接查询
     1.左外连接：
        select 字段列表 from 表1 left [outer] join 表2 on 条件
        查询的是左表的所有数据和其交集部分
     2.右外连接：
 * 子查询

-- 删除和修改的时候 子查询中不允许直接出现 删除的表或者修改的表
-- 解决方案：使用临时表

     *子查询的概念：
        1) 一个查询的结果做为另一个查询的条件
        2) 有查询的嵌套，内部的查询称为子查询
        3) 子查询要使用括号

    1.子查询结果只要是单行单列，肯定在 WHERE 后面作为条件，父查询使用 ： 比较运算符，如 ： > 、<、<>、 =等

    2.子查询结果是单例多行，结果集类似于一个数组，父查询使用 IN 运算符

    3.子查询结果只要是多列，肯定在 FROM 后面作为表
        SELECT  查询字段 FROM  （子查询）  表别名 WHERE  条件;
        子查询作为表需要取别名，否则这张表没有名称则无法访问表中的字段

 *子查询小结：
    子查询结果只要是单列，则在 WHERE 后面作为条件
    子查询结果只要是多列，则在 FROM 后面作为表进行二次查询

多表查询案例：

teacher 教师表
student 学生表
cource 课程表
studentcource 选课表  学生和课程的关系表
CREATE TABLE teacher (
  id int(11) NOT NULL primary key auto_increment,
  name varchar(20) not null unique
 );
CREATE TABLE student (
  id int(11) NOT NULL primary key auto_increment,
  name varchar(20) NOT NULL unique,
  city varchar(40) NOT NULL,
  age int 
) ;
CREATE TABLE course(
  id int(11) NOT NULL primary key auto_increment,
  name varchar(20) NOT NULL unique,
  teacher_id int(11) NOT NULL,
  FOREIGN KEY (teacher_id) REFERENCES teacher (id)
);

CREATE TABLE studentcourse (
   student_id int NOT NULL,
   course_id int NOT NULL,
   score double NOT NULL,
   FOREIGN KEY (student_id) REFERENCES student (id),
   FOREIGN KEY (course_id) REFERENCES course (id)
);

insert into teacher values(null,'关羽');
insert into teacher values(null,'张飞');
insert into teacher values(null,'赵云');

insert into student values(null,'小王','北京',20);
insert into student values(null,'小李','上海',18);
insert into student values(null,'小周','北京',22);
insert into student values(null,'小刘','北京',21);
insert into student values(null,'小张','上海',22);
insert into student values(null,'小赵','北京',17);
insert into student values(null,'小蒋','上海',23);
insert into student values(null,'小韩','北京',25);
insert into student values(null,'小魏','上海',18);
insert into student values(null,'小明','北京',20);

insert into course values(null,'语文',1);
insert into course values(null,'数学',1);
insert into course values(null,'生物',2);
insert into course values(null,'化学',2);
insert into course values(null,'物理',2);
insert into course values(null,'英语',3);

insert into studentcourse values(1,1,80);
insert into studentcourse values(1,2,90);
insert into studentcourse values(1,3,85);
insert into studentcourse values(1,4,78);
insert into studentcourse values(2,2,53);
insert into studentcourse values(2,3,77);
insert into studentcourse values(2,5,80);
insert into studentcourse values(3,1,71);
insert into studentcourse values(3,2,70);
insert into studentcourse values(3,4,80);
insert into studentcourse values(3,5,65);
insert into studentcourse values(3,6,75);
insert into studentcourse values(4,2,90);
insert into studentcourse values(4,3,80);
insert into studentcourse values(4,4,70);
insert into studentcourse values(4,6,95);
insert into studentcourse values(5,1,60);
insert into studentcourse values(5,2,70);
insert into studentcourse values(5,5,80);
insert into studentcourse values(5,6,69);
insert into studentcourse values(6,1,76);
insert into studentcourse values(6,2,88);
insert into studentcourse values(6,3,87);
insert into studentcourse values(7,4,80);
insert into studentcourse values(8,2,71);
insert into studentcourse values(8,3,58);
insert into studentcourse values(8,5,68);
insert into studentcourse values(9,2,88);
insert into studentcourse values(10,1,77);
insert into studentcourse values(10,2,76);
insert into studentcourse values(10,3,80);
insert into studentcourse values(10,4,85);
insert into studentcourse values(10,5,83);
需求：
1、查询不及格的学生
2、查询获得最高分的学生信息
3、查询编号2课程比编号1课程最高成绩高学生信息


根据上述的4张表强化练习：
1、查询平均成绩大于70分的同学的学号和平均成绩
2、查询所有同学的学号、姓名、选课数、总成绩
3、查询学过赵云老师所教课的同学的学号、姓名
4、查询没学过关羽老师课的同学的学号、姓名
5、查询没有学三门课以上的同学的学号、姓名
6、查询各科成绩最高和最低的分
7、查询学生信息和平均成绩
8、查询上海和北京学生数量
9、查询不及格的学生信息和课程信息
10、统计每门课程的学生选修人数（超过四人的进行统计）


3、部门员工查询练习

-- 部门表
create table dept(
    deptno int primary key auto_increment, -- 部门编号
    dname varchar(14) ,   -- 部门名字
    loc varchar(13)   -- 地址
) ;
-- 员工表
create table emp(
    empno int primary key auto_increment,-- 员工编号
    ename varchar(10), -- 员工姓名                                      -
    job varchar(9), -- 岗位
    mgr int,     -- 直接领导编号
    hiredate date, -- 雇佣日期，入职日期
    sal int, -- 薪水
    comm int,  -- 提成
    deptno int not null, -- 部门编号
    foreign key (deptno) references dept(deptno)
);
insert into dept values(10,'财务部','北京');
insert into dept values(20,'研发部','上海');
insert into dept values(30,'销售部','广州');
insert into dept values(40,'行政部','深圳');
insert into emp values(7369,'刘一','职员',7902,'1980-12-17',800,null,20);
insert into emp values(7499,'陈二','推销员',7698,'1981-02-20',1600,300,30);
insert into emp values(7521,'张三','推销员',7698,'1981-02-22',1250,500,30);
insert into emp values(7566,'李四','经理',7839,'1981-04-02',2975,null,20);
insert into emp values(7654,'王五','推销员',7698,'1981-09-28',1250,1400,30);
insert into emp values(7698,'赵六','经理',7839,'1981-05-01',2850,null,30);
insert into emp values(7782,'孙七','经理',7839,'1981-06-09',2450,null,10);
insert into emp values(7788,'周八','分析师',7566,'1987-06-13',3000,null,20);
insert into emp values(7839,'吴九','总裁',null,'1981-11-17',5000,null,10);
insert into emp values(7844,'郑十','推销员',7698,'1981-09-08',1500,0,30);
insert into emp values(7876,'郭靖','职员',7788,'1987-06-13',1100,null,20);
insert into emp values(7900,'令狐冲','职员',7698,'1981-12-03',950,null,30);
insert into emp values(7902,'张无忌','分析师',7566,'1981-12-03',3000,null,20);
insert into emp values(7934,'杨过','职员',7782,'1983-01-23',1300,null,10);

练习：
-- 1．列出至少有一个员工的所有部门。

-- 2．列出薪金比"刘一"多的所有员工。

-- 3．***** 列出所有员工的姓名及其直接上级的姓名。

-- 4．列出受雇日期早于其直接上级的所有员工。

-- 5．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。

-- 6．列出所有job为“职员”的姓名及其部门名称。

-- 7．列出最低薪金大于1500的各种工作。

-- 8．列出在部门 "销售部" 工作的员工的姓名，假定不知道销售部的部门编号。

-- 9．列出薪金高于公司平均薪金的所有员工。

-- 10．列出与"周八"从事相同工作的所有员工。

-- 11．列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。(只要和部门30中任意一个员工的薪资相等即可)

-- 12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。

-- 13．列出在每个部门工作的员工数量、平均工资。

-- 14．列出所有员工的姓名、部门名称和工资。

-- 15．列出所有部门的详细信息和部门人数。

-- 16．列出各种工作的最低工资。

-- 17．列出各个部门的 经理 的最低薪金。

-- 18．列出所有员工的年工资,按年薪从低到高排序。 

-- 19.查出emp表中薪水在3000以上（包括3000）的所有员工的员工号、姓名、薪水。

-- 20.查询出所有薪水在'陈二'之上的所有人员信息。

-- 21.查询出emp表中部门编号为20，薪水在2000以上（不包括2000）的所有员工，显示他们的员工号，姓名以及薪水，以如下列名显示：员工编号 员工名字 薪水

-- 22.查询出emp表中所有的工作种类（无重复）

-- 23.查询出所有奖金（comm）字段不为空的人员的所有信息。

-- 24.查询出薪水在800到2500之间（闭区间）所有员工的信息。

-- 25.查询出员工号为7521，7900，7782的所有员工的信息。

-- 26.查询出名字中有“张”字符，并且薪水在1000以上（不包括1000）的所有员工信息。

-- 27.查询出名字第三个汉字是“忌”的所有员工信息。

-- 28.将所有员工按薪水升序排序，薪水相同的按照入职时间降序排序。

-- 29.将所有员工按照名字首字母升序排序，首字母相同的按照薪水降序排序。 order by convert(name using gbk) asc; 

-- 30.查询出最早工作的那个人的名字、入职时间和薪水。

-- 31.显示所有员工的名字、薪水、奖金，如果没有奖金，暂时显示100.

-- 32.显示出薪水最高人的职位。

-- 33.查出emp表中所有部门的最高薪水和最低薪水，部门编号为10的部门不显示。

-- 34.删除10号部门薪水最高的员工。

-- 35.将薪水最高的员工的薪水降30%。

-- 36.查询员工姓名，工资和 工资级别(工资>=3000 为3级，工资>2000 为2级，工资<=2000 为1级)
语法：case when ... then ... when ... then ... else ... end

**答案**
==

 1、查询平均成绩大于70分的同学的学号和平均成绩
-- 1.1 请每个学生的平均成绩  分组
select student_id,truncate(avg(score),0)
from studentcourse
group by student_id
having avg(score)>70
;




-- 2、查询所有同学的学号、姓名、选课数、总成绩
-- 2.1 查询每个学生的选课数和总成绩 根据学生id分组
select student_id,count(course_id),sum(score)
from studentcourse
group by student_id;

-- 2.2 在2.1基础上 查询 每个同学的学号、姓名、选课数、总成绩
select student.id 学号,student.name 姓名,temp.courseCount 选课数,temp.sumScore 总成绩
from student,(select student_id,count(course_id) as courseCount,sum(score) as sumScore
                from studentcourse
                group by student_id) as temp
where student.id=temp.student_id;


-- 3、查询学过赵云老师所教课的同学的学号、姓名
-- 3.1 找到赵云老师教的课程 id
select id from teacher where name='赵云';



select id from course
where teacher_id = (select id from teacher where name='赵云');


-- 3.2 在中间表 根据课程的id 查询哪些学生学了这些课程  学生的id
select student_id from studentcourse
where course_id in (select id from course
                    where teacher_id = (select id from teacher where name='赵云'));


-- 3.3 根据学生的id 查询学生的信息
select * from student
where id in (select student_id from studentcourse
            where course_id in (select id from course
                                where teacher_id = (select id from teacher where name='赵云'))
);




-- 4、查询没学过关羽老师课的同学的学号、姓名
select * from student
where id not in (select student_id from studentcourse
            where course_id in (select id from course
                                where teacher_id = (select id from teacher where name='关羽'))
);


-- 5、查询没有学三门课以上的同学的学号、姓名
-- 5.1 每个同学学了几门课  分组 根据学生的id
select student_id
from studentcourse
group by student_id
having count(*)<=3
;

-- 5.2 找学号 和 姓名 ；哪？ 学生表
select * 
from student
where id in (select student_id
                from studentcourse
                group by student_id
                having count(*)<=3);


-- 6、查询各科成绩最高和最低的分
select course_id,max(score),min(score)
from studentcourse
group by course_id;

-- 临时表
select course.name 课程,temp.maxScore 最高分,temp.minScore 最低分
from course,(select course_id,max(score) maxScore,min(score) minScore
                from studentcourse
                group by course_id) as temp
where course.id=temp.course_id;


-- 7、查询学生信息和平均成绩
-- 7.1查询每个学生的平均成绩
select student_id,avg(score)
from studentcourse
group by student_id;

-- 7.2 关联查询 临时表
select student.*,temp.avgScore
from student,(select student_id,truncate(avg(score),0) avgScore
                from studentcourse
                group by student_id) as temp
where student.id=temp.student_id;



-- 8、查询上海和北京学生数量
select city,count(*) from student 
group by city
having city in ('北京','上海')

;


select city,count(*)
from student
where city in ('北京','上海')
group by city
;


-- 9、查询不及格的学生信息和课程信息
-- 9.1 将不及格的人查询出来
select course_id,student_id
from studentcourse
where score<60;

-- 9.2 将9.1 作为临时表 和 学生表与课程表关联
select student.*,course.*
from student,course,(select course_id,student_id
                    from studentcourse
                    where score<60) as temp
where student.id=temp.student_id 
    and course.id=temp.course_id;


-- 10、统计每门课程的学生选修人数（超过四人的进行统计）
-- 10.1 查询每门课程的学生人数
select course_id,count(*)
from studentcourse
group by course_id;

-- 10.2 结果
select course_id,count(*)
from studentcourse
group by course_id
having count(*)>4




select * from emp;

select * from dept;

-- 1．列出至少有一个员工的所有部门。
select dept.deptno,count(empno) from dept
left outer join emp
on dept.deptno=emp.deptno
group by dept.deptno
having count(empno)>=1
;

-- 2．列出薪金比"刘一"多的所有员工。
-- 刘一的薪金
select sal from emp where ename='刘一';

-- 查询 > 刘一薪金 的员工信息
select * from emp where sal > (select sal from emp where ename='刘一');

-- 3．***** 列出所有员工的姓名及其直接上级的姓名。
select e1.ename, e2.ename
from emp as e1, emp as e2
where e1.mgr=e2.empno;


-- 4．列出受雇日期早于其直接上级的所有员工。
select e1.*
from emp e1,emp e2
where e1.hiredate<e2.hiredate
    and e1.mgr=e2.empno;

select * from emp where empno=7902;


-- 5．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。
select dept.*,emp.*
from dept
left outer join emp
on dept.deptno = emp.deptno;

-- 6．列出所有job为“职员”的姓名及其部门名称。
select emp.ename,dept.dname
from emp,dept
where job='职员'
    and emp.deptno=dept.deptno
;

-- 7．列出最低薪金大于1500的各种工作。
-- 7.1 列出各种工作的最低薪金
select job,min(sal) from emp
group by job
having min(sal)>1500
;

-- 8．列出在部门 "销售部" 工作的员工的姓名，假定不知道销售部的部门编号。
select emp.ename
from emp,dept
where emp.deptno=dept.deptno
    and dept.dname='销售部'
;


-- 9．列出薪金高于公司平均薪金的所有员工。
-- 9.1 公司的平均薪金
select avg(sal) from emp;

-- 9.2 高于平均薪资
select * from emp where sal > (select avg(sal) from emp);

-- 10．列出与"周八"从事相同工作的所有员工。
-- 10.1 找出周八的job
select job from emp where ename='周八';

-- 10.2 从事相同工作的员工
select * from emp where job = (select job from emp where ename='周八');

-- 11．列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。
-- 列出薪金等于部门30中员工的薪金
select sal
from emp 
where deptno=30;

select ename,sal 
from emp
where sal in (select sal
                from emp 
                where deptno=30);




-- 12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。
-- 12.1 部门30的薪金
select max(sal)
from emp 
where deptno=30;

-- 12.2 高于
select * from emp
where sal > all(select sal
                from emp 
                where deptno=30);



-- 13．列出在每个部门工作的员工数量、平均工资。
select deptno,count(empno),avg(sal)
from emp
group by deptno;


-- 14．列出所有员工的姓名、部门名称和工资。
select emp.ename,dept.dname,emp.sal
from emp,dept
where emp.deptno=dept.deptno;


-- 15．列出所有部门的详细信息和部门人数。
select * 
from dept
left outer join emp
on dept.deptno=emp.deptno; 

select dept.deptno,count(emp.empno) 
from dept
left outer join emp
on dept.deptno=emp.deptno
group by dept.deptno
; 

select dept.*,temp.empCount from dept,(select dept.deptno as deptno,count(emp.empno) empCount
                    from dept
                    left outer join emp
                    on dept.deptno=emp.deptno
                    group by dept.deptno) as temp
where dept.deptno=temp.deptno;



-- 16．列出各种工作的最低工资。
select job,min(sal) 
from emp
group by job
;

-- 17．列出各个部门的 经理 的最低薪金。
select deptno,min(sal) 
from emp
where job='经理'
group by deptno
;

-- 18．列出所有员工的年工资,按年薪从低到高排序。 
select emp.*,(sal+ifnull(comm,0))*12 年薪
from emp
order by (sal+ifnull(comm,0))*12 asc
;


-- 19.查出emp表中薪水在3000以上（包括3000）的所有员工的员工号、姓名、薪水。
select empno,ename,sal
from emp
where sal>=3000;


-- 20.查询出所有薪水在'陈二'之上的所有人员信息。
select sal from emp where ename='陈二';

select *
from emp
where sal > (select sal from emp where ename='陈二');


-- 21.查询出emp表中部门编号为20，薪水在2000以上（不包括2000）的所有员工，
-- 显示他们的员工号，姓名以及薪水，以如下列名显示：员工编号 员工名字 薪水
select empno 员工编号,ename 员工名字,sal 薪水
from emp
where deptno=20 
    and sal>2000;

-- 22.查询出emp表中所有的工作种类（无重复）
select distinct job
from emp;


-- 23.查询出所有奖金（comm）字段不为空的人员的所有信息。
select *
from emp
where comm is not null;


-- 24.查询出薪水在800到2500之间（闭区间）所有员工的信息。（注：使用两种方式实现and以及between and）
select *
from emp
where sal>=800 and sal<=2500;

select *
from emp
where sal between 800 and 2500;



-- 25.查询出员工号为7521，7900，7782的所有员工的信息。（注：使用两种方式实现，or以及in）
select * 
from emp
where empno=7521 or empno=7900 or empno=7782;

select * 
from emp
where empno in (7521, 7900, 7782);

-- 26.查询出名字中有“张”字符，并且薪水在1000以上（不包括1000）的所有员工信息。
select *
from emp
where ename like '%张%'
    and sal>1000;


-- 27.查询出名字第三个汉字是“忌”的所有员工信息。
select *
from emp
where ename like '__忌';


-- 28.将所有员工按薪水升序排序，薪水相同的按照入职时间降序排序。
select *
from emp
order by sal asc, hiredate desc;

-- 29.将所有员工按照名字首字母升序排序，首字母相同的按照薪水降序排序。 order by convert(name using gbk) asc; 
select * from emp
order by convert(substring(ename,1,1) using gbk) asc, sal desc
;

-- 30.查询出最早工作的那个人的名字、入职时间和薪水。
select min(hiredate) from emp;

select ename,hiredate, sal
from emp
where hiredate = (select min(hiredate) from emp);

-- 31.显示所有员工的名字、薪水、奖金，如果没有奖金，暂时显示100.
select ename,sal, ifnull(comm,100)
from emp;

-- 32.显示出薪水最高人的职位。
-- 方式一 找出薪水最高的人
select max(sal) from emp;

select job
from emp
where sal = (select max(sal) from emp);

-- 方式二
select job
from emp
where sal >= all(select sal from emp);


-- 33.查出emp表中所有部门的最高薪水和最低薪水，部门编号为10的部门不显示。
select deptno,min(sal) 最低薪水,max(sal) 最高薪水
from emp
where deptno<>10
group by deptno;

-- 34.删除10号部门薪水最高的员工。
-- 34.1 10号部门薪水最高的员工
select max(sal) from emp where deptno=10;

-- 34.2 删除
-- 删除和修改的时候 子查询中不允许直接出现 删除的表或者修改的表
-- 解决方案：使用临时表
delete from emp 
where deptno=10 
    and sal=(select temp.maxSal from (select max(sal) as maxSal from emp where deptno=10) as temp);

-- 35.将薪水最高的员工的薪水降30%。
-- 薪水最高的
select max(sal) from emp;

select empno from emp 
where sal = (select max(sal) from emp);

update emp 
set sal=sal*(1-0.3)   只能写成0.3 
where empno in (select temp.empno from (select empno from emp 
                                        where sal = (select max(sal) from emp)) temp);

select * from emp;

-- 36.查询员工姓名，工资和 工资级别(工资>=3000 为3级，工资>2000 为2级，工资<=2000 为1级)
-- 语法：case when ... then ... when ... then ... else ... end
select ename,sal,
    case when sal>=3000 then '3级'
         when sal>2000  then '2级'
         else '1级'
    end
from emp;