sql内连接、左连接、右连接以及全连接查询

Mysql

一，内连接、左连接、右连接以及全连接查询

一、内连接查询 inner join

关键字：inner join on

语句：select * from a_table a inner join b_table b on a.a_id = b.b_id;

说明：组合两个表中的记录，返回关联字段相符的记录，也就是返回两个表的交集（阴影）部分。

案例解释：在boy表和girl 表中查出两表 hid 字段一致的姓名（gname，bname），boy表和girl 表如下：

采用内连接查询方式：

SELECT boy.hid,boy.bname,girl.gname FROM boy INNER JOIN girl ON girl.hid = boy.hid;

二、左连接查询 left join

关键字：left join on / left outer join on

语句：SELECT * FROM a_table a left join b_table b ON a.a_id = b.b_id;

说明： left join 是left outer join的简写，它的全称是左外连接，是外连接中的一种。 左(外)连接，左表(a_table)的记录将会全部表示出来，而右表(b_table)只会显示符合搜索条件的记录。右表记录不足的地方均为NULL。

案例解释：在boy表和girl 表中左连接查询，boy表和girl 表如下：

采用内连接查询方式：

SELECT boy.hid,boy.bname,girl.gname FROM boy LEFT JOIN girl ON girl.hid = boy.hid;

查询结果如下：

三、右连接 right join

关键字：right join on / right outer join on

语句：SELECT * FROM a_table a right outer join b_table b on a.a_id = b.b_id;

说明：right join是right outer join的简写，它的全称是右外连接，是外连接中的一种。与左(外)连接相反，右(外)连接，左表(a_table)只会显示符合搜索条件的记录，而右表(b_table)的记录将会全部表示出来。左表记录不足的地方均为NULL。

案例解释：在boy表和girl 表中右连接查询，boy表和girl 表如下：

采用内连接查询方式：

SELECT boy.hid,boy.bname,girl.gname FROM boy RIGHT JOIN girl ON girl.hid = boy.hid;

查询结果如下：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-8kgkgFJ6-1603684358739)(https://i.loli.net/2020/08/31/M7e3YjLDw5Wd4to.png)]

从教学库中查询出所有学生的选课情况，要求没选修任何课程的学生信息也要反映出来(左，右连接)

select a.学生号,b.姓名,a.成绩 from 选课 a 
left join 学生 b on a.学生号=b.学生号 
where a.成绩 is null

查询课程表中 显示学生姓名,课程名称,成绩

select 学生.姓名,课程.课程名,选课.成绩 from 选课
inner join 学生 on 学生号=学生号
inner join 课程 on 课程号=课程号

查询选课表要求显示如下内容：（姓名,课程名称,成绩）选课（学生号，课程号）–》学生（学生号）–》课程（课程号）

select b.姓名,c.课程名,a.成绩 from 选课 a
inner join 学生 b  on a.学生号=b.学生号
inner join 课程 c  on a.课程号=c.课程号 where c.课程名='C++语言'

作业

1，统计各部门男员工，女员工的最高薪资 (员工)

   select 部门,if(性别=-1 or 性别='False','女','男') as 性别,max(目前薪资) as 最高薪资 from 员工
   group by 部门,性别 

2、统计各部门平均年龄(员工)

    select 部门,format(avg(year(now())-year(出生日期)),2) as 平均年龄 from 员工 group by 部门

3、统计超过666人的部门(员工)

select 部门,count(部门) as 人数 from 员工 group by 部门 having 人数>666

4.查询员工表中年龄最大的员工信息

select 员工编号,姓名,出生日期 from 员工 where 出生日期=(select min(出生日期) from 员工)

select 员工编号,姓名,出生日期 from 员工 order by 出生日期 limit 1

5.查询员工表中各部门年龄最小的员工信息(难)

select  distinct a.姓名,a.部门,a.出生日期 from 员工 a  where 
a.出生日期=(select max(出生日期) from 员工 b where b.部门=a.部门 ) 

select  distinct a.姓名,a.部门,a.出生日期 from 员工 a  inner join 
(select 部门,max(出生日期) as 出生日期  from 员工 group by 部门) b on a.部门=b.部门 where a.出生日期=b.出生日期

select  distinct a.姓名,a.部门,a.出生日期 from 员工 a  inner join 
(select 部门,max(出生日期) as 出生日期  from 员工 group by 部门) b on a.出生日期=b.出生日期 

7.查询比平均单价高的产品的名称和单价(产品资料)

select 产品,单价 from 产品资料 where 单价>(select avg(单价) from 产品资料)

10.查询有哪些员工姓名是重复的并显示重复姓名的人数

select 姓名,count(*) as 同名人数 from 员工 group by 姓名 having 同名人数>2

12、查询哪些公司没有采购(客户，订货主档)

select 客户编号,公司名称 from 客户 where 客户编号 not in (select distinct 客户编号 from 订货主档)

select a.客户编号,a.公司名称 from 客户 a left join 订货主档 b on a.客户编号=b.客户编号
where b.订单号码 is null

13、查询销售数量最多的产品信息(产品资料,订货明细)

select 产品编号,产品 from 产品资料  where 产品编号=销售数量最多的那个产品编号

如何求销售数量最多的那个产品编号？
select  产品编号,sum(数量) as 数量 from 订货明细 group by  产品编号 order by 数量 desc limit 1

最终代码：
select * from 产品资料  where 产品编号=(select  产品编号  from 订货明细 group by  产品编号 order by sum(数量) desc limit 1)

14、查询销售金额最多的产品信息(产品资料,訂貨明細)

select * from 产品资料  where 产品编号=(select  产品编号  from 订货明细 group by  产品编号 order by sum(单价*数量*(1-折扣)) desc limit 1)

15、查询出本月销售成单数量最多的前三名员工,并将其工资增加10%(訂貨主檔,员工)
本月销售成单数量最多的前三名员工

select 员工编号 from 订货主档 where month(now())= month(订单日期) group by 员工编号 order by  count(*) desc limit 3

16、查询1997年7月份客户采购的详细信息(客户 ,訂貨主檔 , 訂貨明細 )

select a.客户编号,a.公司名称,a.联系人,a.地址,b.订单号码,b.订单日期,b.收货人,
d.产品,e.类别名称,f.供应商,c.单价,c.数量,c.折扣 from 客户 a,订货主档 b,订货明细 c ,产品资料 d,产品类别 e ,商品供应 f where a.客户编号=b.客户编号 and b.订单号码=c.订单号码 and c.产品编号=d.产品编号 and d.类别编号=e.类别编号 and d.供应商编号=f.供应商编号
and 订单日期 between '1997-7-1' and '1997-7-31'

17、查询一个订单中采购金额最多的前三位公司详细信息(客户,訂貨主檔,訂貨明細)

  select a.*
  from 客户 a, 订货主档 b
  where a.客户编号=b.客户编号 and
   b.订单号码 in (select * from  ( select 订单号码
                   from 订货明细
                   group by 订单号码
                   order by SUM(单价*数量*(1-折扣))  limit 3
                   ) t)

18.根据公司采购数量多少从高到低排列(訂貨主檔,訂貨明細)

select a.客户编号, sum(b.采购数量) as  采购总数量
from 订货主档 a,  (select 订单号码, SUM(数量) as 采购数量 from 订货明细 group by 订单号码) b
where a.订单号码=b.订单号码 
group by a.客户编号
order by 采购总数量 desc

19.统计各种产品类别的产品数量，按照产品类别数量从低到高显示(產品類別,产品资料)

  select a.类别名称, b.产品数量
  from 产品类别 a, ( select 类别编号, COUNT(*) as 产品数量 from 产品资料 group by 类别编号  ) b
  where a.类别编号=b.类别编号  
  order by b.产品数量 asc

20、统计各个城市中客户的人数(客户)

  select 城市, COUNT(*) as 客户人数
  from 客户
  group by  城市