数据库多表查询

等值连接查询

  原理：将多个表组合成一张逻辑大表

    字段相加

    记录相乘

  语法

    两张表

      select

        查询内容

      from

        A，B

      where

        A.主键=B.外键

        and

          查询条件

    三张表

      select *

      from A,B,C

      where A.主键=B.外键 and C.主键=B.外键

      and 查询条件

 

 

#查询张三的C语言的成绩

select cj

from xsb,kcb,cjb

where xsb.xh=cjb.xh and cjb.kch=kcb.kch

and xm='张三' and kcm='C语言'

 

#查询参加缺陷管理课程考试的人数

 

select count(*)

from kcb,cjb

where kcb.kch=cjb.kch

and kcm='缺陷管理'

 

#查询总成绩大于400分的学员姓名

 

select xh

from cjb

group by xh

having sum(cj)>400

 

select xm ,sum(cj)

from xsb,cjb

where xsb.xh=cjb.xh

group by xsb.xh

having sum(cj)>400

 

#查询189班男生C语言的总成绩

 

select  sum(cj)

from xsb,kcb,cjb

where kcb.kch=cjb.kch and cjb.xh=xsb.xh

and bj='189' and xb='男' and kcm='C语言'

 

#按姓名显示每个人的最高分、总分以及平均分

 

select xm,max(cj),sum(cj),avg(cj)

from xsb,cjb

where xsb.xh=cjb.xh

group by cjb.xh

 

#显示(个人)总成绩前三名的学员姓名

 

select xm,sum(cj),cjb.xh

from xsb,cjb

where xsb.xh=cjb.xh

group by xsb.xh

order by sum(cj) desc

limit 3

 

 

 

 

 

 

子查询/ 嵌套查询

  原理：将多条单表查询的语句拼接成一条SQL语句

  步骤

    通过已知条件找到查询的未知条件

    1.分析需求，拆分为多条单表查询的语句

    2.依次编写每个单表查询的语句

      写一条执行一条

        检查语句的正确性

        查看语句的结果条数

          1条

          多条

    3.按需求拼接SQL语句

      子句的结果是单条

        =

        !=

        >

        >=

        <

        <=

      子句的结果是多条

        in

        not in

  优点

    查询效率高

  缺点

逻辑比较复杂，需要对表结构有清晰的了解

 

 

#子查询

 

#查询张三的C语言的成绩

select cj

from cjb

where kch=(select kch from kcb where kcm='C语言'

  )  and xh=(select xh from xsb where xm='张三')

#查询参加缺陷管理课程考试的人数

select count(*)

from cjb

where kch=(select kch from kcb where kcm='缺陷管理')

 

#查询总成绩大于400分的学员姓名

select xm

from xsb

 

where xh=(select xh from cjb group by xh having sum(cj)>400)

#查询189班男生C语言的总成绩

select sum(cj)

from cjb

where kch=(select kch from kcb where kcm='C语言')

and xh in (select xh from xsb where bj='189' and xb='男')

#按姓名显示每个人的最高分、总分以及平均分

#---无法使用嵌套，因为查询的结果在两个表中

select xm,max(cj),sum(cj),avg(cj)

from cjb,xsb

group by xm

#显示(个人)总成绩前三名的学员姓名---1235错误

select xm

from xsb,cjb

where xsb.xh=cjb.xh

group by xsb.xh

order by sum(cj) desc

limit 3

 

#1235 解决方案二：将limit子句放在from后面---临时表

select xm

from xsb,(select xh

from cjb

group by xh

order by sum(cj) desc

limit 3) a

where xsb.xh=a.xh

#查询学生表中最大年龄和最小年龄的姓名

select xm,nl

 

from xsb

where nl=

or nl=

#查询001号学员最高分对应的课程名

#

select kcm

from kcb

where kch in(

       select kch from cjb

         where cj=(select max(cj)

                    from cjb where xh='001')

                and xh='001')

 

# 查询人数大于30人的班级中的男女生人数

#大于30人的班级有哪些

select bj

from xsb

group by bj

having count(*)>30

 

select bj,xb,count(*)

from xsb

where bj in(select bj from xsb

           group by bj having count(*)>30)

group by bj,xb

 

 

 

 

内连接查询

  与等值查询的结果相同，区别是

    等值连接是先连接，再判断

    内连接是先判断，再连接

  语法

    两张表

      select *

      from A inner join B

      on A.主键=B.外键

      where 查询条件

    三张表

      select *

      from A inner join B

      on A.主键=B.外键

      inner join C

      on C.主键=B.外键

      where 查询条件

 

 

#查询张三的C语言的成绩

select cj

from xsb inner join cjb

on xsb.xh=cjb.xh

inner join kcb

on kcb.kch=cjb.kch

where xm= '张三' and kcm='C语言'

 

#查询参加缺陷管理课程考试的人数

select count(cj)

from kcb inner join cjb

on kcb.kch=cjb.kch

where kcm='缺陷管理'

 

#查询总成绩大于400分的学员姓名

select xm

from xsb inner join cjb

on xsb.xh=cjb.xh

group by xsb.xh

having sum(cj)>400

#查询189班男生C语言的总成绩

select sum(cj)

from xsb inner join cjb

on xsb.xh=cjb.xh

inner join kcb

on kcb.kch=cjb.kch

where bj=189 and xb='男' and kcm='C语言'

 

#按姓名显示每个人的最高分、总分以及平均分

select xm,max(cj),sum(cj),avg(cj)

from xsb inner join cjb

on xsb.xh=cjb.xh

group by xsb.xm

#显示(个人)总成绩前三名的学员姓名

select xm,sum(cj)

from xsb inner join cjb

on xsb.xh=cjb.xh

group by xsb.xh

order by sum(cj) desc

 

 

左外连接查询

  显示左边的表全部记录+与其有关联的记录

  等值/内连接的结果+左边的其他记录

  语法

    select *

    from A left join B

    on A.主键=B.外键

右外连接查询

  显示右边的表全部记录+与其有关联的记录

  等值/内连接的结果+右边的其他记录

  语法

    select *

    from B right join A

    on A.主键=B.外键