连接查询，子查询

连接查询（列连接）

问题：学生表有10条数据，成绩表有10条数据

    连接之后有多少条数据（笛卡尔积）：10*10

连接查询会产生笛卡尔积；假设集合A={a,b},集合B={c,d}，

2各集合的笛卡尔积{（a,c）,(a,d),(b,c),(b,d)}

但是：

1. 从数据结果来讲，有很多无用的数据。
2. 通过where条件过滤无用的数据：通过主外键来清除无用数据

使用主外键清楚无用数据

select * from emp,dept where emp.deptno = dept.deptno;

 内连接

上面的写法其实就是内连接，但是不在SQL标准

select

        *

from

        emp e

inner join

        dept d

on

        e.deptno = d.deptno;

1. inner可以省略
2. 内连接的特点：查询的条件必须满足条件，不满足条件的数据将会丢失

部门是40的数据就没有了，员工emp中没有40这个部门的人。

外连接 outer join

A left join B：A 左连接 B 表；A表示主表

B left join A：B 左连接 A 表；B表示主表

A Right join B：A 右连接 B 表；B表示主表

B Right join A：B 右连接 A 表；A表示主表

特点：主表的数据全部都在，

       然后去匹配 非主表的 数据，匹配补上为null

##员工表emp是主表，去匹配dept

select

        *

from

        emp e

left join

        dept d

on

        e.deptno = d.deptno;

##部门表是 主表，然后去匹配 员工表

select

        *

from

        emp e

right join

        dept d

on

        e.deptno = d.deptno;

面试题：左连接和右连接的区别

 

自然连接

2张表中，有字段名字和数据类型一样会自然作为连接的条件

Dept表中emp都有deptno

select

        *

from

        emp e

natural join

        dept d;

子查询

一个select查询中包含另外一个完整的select语句。

子查询就是嵌套查询，一个select嵌套着一个或者多个select。

##子查询

 

###查询工资 高于 JONES 的员工

        （1）先查JONES的工资。

select sal from emp where ename = 'JONES';

        （2）他的工资作为条件

select * from emp where sal > (第一步得到工资)

 

        （3）合并

select

        *

from

        emp

where

sal > (select sal from emp where ename = 'JONES');

 

##查询与SCOTT同一个部门的员工

 

select

        *

from

        emp

where

deptno = (select deptno from emp where ename = 'SCOTT');

##工资高于30号部门所有人的员工信息

##（1）查询30部门的所有人的工资

select sal from emp where deptno = 30;

##（2）查询高于30部门所有员工的信息

select

        *

from

        emp

where

        sal > ALL (第一步所有的值)

##（3）合并

select

        *

from

        emp

where

        sal > ALL (select sal from emp where deptno = 30)；

 

 

 

子查询出现的位置

From后：作为一个表

Where：作为条件；

       In：在什么里面

       Any：有一个

       All：所有

=============多行多列============

##查询 工作（job） 和 工资(sal) 与 MARTIN 完全一样的员工信息

#(1)查看MARTIN的 工作和工资

select job,sal from emp where ename = "MARTIN";

+----------+---------+

| job      | sal     |

+----------+---------+

| SALESMAN | 1250.00 |

+----------+---------+

| SALESMAN | 150.00 |

+----------+---------+

 

#(2)根据员工信息

select

        *

from

        emp

where

        (job,sal) in (第一步结果)

 

##（3）合并之后

select

        *

from

        emp

where

        (job,sal) in (select job,sal from emp where ename = "MARTIN") ;

 

 

==================多行单列--------

##找出有员工的部门信息

##（1）找出所有 有员工的部门ID

select distinct deptno from emp;

+--------+

| deptno |

+--------+

|     30 |

|     20 |

|     10 |

+--------+

 

##（2）找部门信息

select * from dept where deptno in （第一步的结果）

 

##（3）合并

select * from dept where deptno in (select distinct deptno from emp);

 

 

 

##查询有2个以上直接下属的员工信息

##(1)找出各个领导的下属人数

 

select mgr, count(*) as cnt from  emp group by mgr ;

 

+------+-----+

| mgr  | cnt |

+------+-----+

| NULL |   1 |

| 7566 |   2 |

| 7698 |   5 |

| 7782 |   1 |

| 7788 |   1 |

| 7839 |   3 |

+------+-----+

 

select

        t.mgr,t.cnt

from

(

        select mgr, count(*) as cnt from  emp group by mgr

) t

where

   t.cnt > 2;

 

 +------+-----+

| mgr  | cnt |

+------+-----+

| 7698 |   5 |

| 7839 |   3 |

+------+-----+ 

 

更简单方式：select mgr, count(*) as cnt from  emp group by mgr having   cnt > 2;

 

 

 

##查询员工编号为7788员工名字，员工工资，部门名字，部门地址

 

总结：

select

        字段，聚合函数（max,sum,avg,round,case,distinct,ifnull）

from

        数据源

where

        条件：and 、or、is not null、

                 in 、between、like

group by

        分组：（每个，各个，）

having

        分组之后过滤

order by

        排序asc / desc

limit

        现在条目数

       

       

1.     查询男生、女生的人数；

select

        gender as 性别,count(*) as 人数 

from

        student

group by

        gender;

       

2.     查询姓“张”的学生名单；

select sname from student where sname like '张%';

 

3.     课程平均分从高到低显示

select

        course_id,avg(num) as avg_num

from

        score

group by

        course_id

order by

        avg_num;

 

4.     查询有课程成绩小于60分的同学的学号、姓名；

方式一：

select

        sid as 学号,sname as 姓名

from

        student

where

        sid in(select distinct student_id from score where num < 60);

       

5.     查询至少有一门课与学号为1的同学所学课程相同的同学的学号和姓名；

##(1)学号为1的同学，学习了什么科目：成绩表score -> 科目ID（多行单列）

select course_id from score where student_id=1;

 

合并：

select

    t2.sid, t2.sname

from

(select

    distinct student_id

from 

    score 

where

    course_id

in

(select course_id from score where student_id=1

 )

 )t1

left join

    student t2

on

    t1.student_id=t2.sid;

 

6.     查询出只选修了一门课程的全部学生的学号和姓名；

##需求：

        （1）统计每个人 课程数量  ：：：分组：group by 人

 

select

        student_id,count(*) as cnt

from

        score

group by

        student_id;

 

##    （2）课程数量是1

 

方案一:嵌套查询

select

        student_id,sname

from

        (第一步结果)

where

        cnt = 1;

 

方式二：使用having

select

        student_id,count(*) as cnt

from

        score

group by

        student_id

having

        cnt = 1;

合并

select

        t.student_id

from

(

        select

        student_id,count(*) as cnt

from

        score

group by

        student_id

) t

where

        t.cnt = 1;

 

(3)根据学生ID去查找名字

 

select

        t2.sid, t2.sname

from

        (第二步结果) t1

left join

        student t2

on

        t1.student_id = t2.sid;

8.     查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名；

## （1）先进行自己和自己连接

select

        t1.student_id sid, t1.course_id  t1cid, t1.num t1num,t2.course_id t2cid, t2.num t2num

from

        score t1

join

        score t2

on t1.student_id = t2.student_id

 

where

        t2.course_id=2 and t1.course_id=1 and t2.num  < t1.num;

13.   查询没学过“张磊老师”课的同学的学号、姓名；

select

        cid

from

        course

join

        (select * from teacher where tname= '张磊老师') t

on

        course.teacher_id = t.tid;

       

 

select

        student_id

from

        score

where

        cid not in any

(select

        cid

from

        course

join

        (select * from teacher where tname= '张磊老师') t

on

        course.teacher_id = t.tid )

       

14.   查询学过“1”并且也学过编号“2”课程的同学的学号、姓名；

##分析:学习了科目1 和2 的学生学号 和名字

##（1）查询科目1的人；

select * from score where course_id = 1;

+-----+------------+-----------+-----+

| sid | student_id | course_id | num |

+-----+------------+-----------+-----+

|   1 |          1 |         1 |  10 |

|   6 |          2 |         1 |   8 |

|  10 |          3 |         1 |  77 |

|  14 |          4 |         1 |  79 |

|  18 |          5 |         1 |  79 |

|  22 |          6 |         1 |   9 |

|  26 |          7 |         1 |   9 |

|  30 |          8 |         1 |   9 |

|  34 |          9 |         1 |  91 |

|  38 |         10 |         1 |  90 |

|  42 |         11 |         1 |  90 |

|  46 |         12 |         1 |  90 |

+-----+------------+-----------+-----+

 

##（2）查询科目2的人；

select student_id from score where course_id = 2;

+------------+

| student_id |

+------------+

|          1 |

|          2 |

|          3 |

|          4 |

|          5 |

|          6 |

|          7 |

|          8 |

|          9 |

|         10 |

|         11 |

|         12 |

+------------+

 

##（3）第一步表中 数据在 第二步

 

select

        t.student_id

from

        (第一步结果) t

where

        t.student_id in (第二步结果)

 

合并

select

        t.student_id

from

        (select * from score where course_id = 1) t

where

        t.student_id in (select student_id from score where course_id = 2);