软件测试-数据库-day03-连接查询、自关联、子查询

目标

1. 掌握内连接的用法2. 熟悉外连接(左连接、右连接)的用法3. 掌握子查询的用法

一、连接查询

1.【知道】连接查询概述

• 内连接: 连接两个表时，取的是两个表中都存在的数据。（取交集）

  

• 左连接: 连接两个表时，取的是左表中特有的数据，对于右表中不存在的数据，用null来填充。

• 右连接：连接两个表时，取的是右表中特有的数据，对于左表中不存在的数据，用null来填充。

  

• 数据准备

  drop table if exists students;create table students (studentNo varchar(10) primary key,name varchar(10),sex varchar(1),hometown varchar(20),age tinyint(4),class varchar(10),card varchar(20));​insert into students values('001', '王昭君', '女', '北京', 20, '1班', '340322199001247654'),('002', '诸葛亮', '男', '上海', 18, '2班', '340322199002242354'),('003', '张飞', '男', '南京', 24, '3班', '340322199003247654'),('004', '白起', '男', '安徽', 22, '4班', '340322199005247654'),('005', '大乔', '女', '天津', 19, '3班', '340322199004247654'),('006', '孙尚香', '女', '河北', 18, '1班', '340322199006247654'),('007', '百里玄策', '男', '山西', 20, '2班', '340322199007247654'),('008', '小乔', '女', '河南', 15, '3班', null),('009', '百里守约', '男', '湖南', 21, '1班', ''),('010', '妲己', '女', '广东', 26, '2班', '340322199607247654'),('011', '李白', '男', '北京', 30, '4班', '340322199005267754'),('012', '孙膑', '男', '新疆', 26, '3班', '340322199000297655');​drop table if exists courses;create table courses (courseNo int(10) unsigned primary key auto_increment,name varchar(10));insert into courses values (null, '数据库'), (null, 'qtp'), (null, 'linux'),(null, '系统测试'), (null, '单元测试'), (null, '测试过程');​drop table if exists scores;create table scores (id int(10) unsigned primary key auto_increment,courseNo int(10),studentno varchar(10),score tinyint(4));insert into scores values (0, 1, '001', 90), (0, 1, '002', 75),(0, 2, '002', 98),(0, 3, '001', 86),(0, 3, '003', 80),(0, 4, '004', 79),(0, 5, '005', 96),(0, 6, '006', 80);

2.【重点】内连接

• 语法格式

  select * from表名1 inner join 表名2 on 表1.列=表2.列;

  • 查询的是两个表的交集的数据

  • 表1的列与表2的列一定是存在关联关系

  • 内连接连接时可以连接多个表

• 案例

  -- 例1： 查询学生信息及学生的成绩select * from students inner join scores on students.studentNo = scores.studentNo;-- students起别名为stu, scores起别名为scselect * from students as stu inner join scores as sc on stu.studentNo = sc.studentNo;-- 起别名可以不写asselect * from students stu inner join scores sc on stu.studentNo = sc.studentNo;select * from students stu, scores sc where stu.studentNo = sc.studentNo;​-- 例2： 查询课程信息及课程的成绩select * from courses cs inner join scores sc on cs.courseNo = sc.courseNo;​-- 例3： 查询王昭君的成绩， 要求显示姓名、 课程号、 成绩select stu.name, sc.courseNo, sc.score from students stu inner join scores sc on stu.studentNo = sc.studentNo where stu.name = '王昭君';​-- 1、 查询学生信息及学生的课程对应的成绩select * from students inner join scores on students.studentNo=scores.studentNoinner join courses on scores.courseNo=courses.courseNo​-- 2、 查询所有学生的数据库成绩， 要求显示姓名、 课程名、 成绩select students.name, courses.name, scores.score from students inner join scores on students.studentNo=scores.studentNoinner join courses on scores.courseNo=courses.courseNo​-- 3、 查询王昭君的数据库成绩， 要求显示姓名、 课程名、 成绩select students.name, courses.name, scores.score from students inner join scores on students.studentNo=scores.studentNoinner join courses on scores.courseNo=courses.courseNo where students.name='王昭君'​-- 4、 查询男生中最高成绩， 要求显示姓名、 课程名、 成绩select students.name, courses.name, scores.score from students inner join scores on students.studentNo=scores.studentNoinner join courses on scores.courseNo=courses.courseNowhere students.sex='男' order by scores.score desc limit 0, 1 -- 等价于 limit 1

3.【知道】左连接

• 语法格式

  select * from 表1 left join表2 on 表1.列=表2.列

  • 左连接查询的是左表特有的数据，对于右表中不存在的数据用null来填充

• 案例

  -- 例1: 查询所有学生的成绩， 包括没有成绩的学生select * from students stu left join scores sc on stu.studentNo = sc.studentNo​-- 例2:查询所有学生的成绩， 包括没有成绩的学生， 需要显示课程名select * from students left join scores on students.studentNo = scores.studentNo left join courses on scores.courseNo = courses.courseNo

4.【知道】右连接

• 语法格式

  select * from 表1 right join 表2 on 表1.列=表2.列

  • 右连接查询的是右表特有的数据，对于左表中不存在的数据用null来填充

• 案例

  -- 例1:查询所有学生的成绩， 包括没有成绩的学生select * from scores right join students on scores.studentNo = students.studentNo​-- 例2:查询所有学生的成绩， 包括没有成绩的学生， 需要显示课程名select * from scores right join courses on scores.courseNo = courses.courseNoright join students on students.studentNo = scores.studentNo 

二、自关联

1.【知道】自关联介绍

• 自连接的应用场景

  • 等级关系即可使用一张表维护

  • 在同一张表中，用两个字段来表示记录之间的层级关系时，可以使用自关联。比如地址信息中的，省、市的信息。

  • 省、市、区的信息，一般不会分开放在不同的表里面进行存储，而是放在同一个表当中。

    

• 数据准备

  drop table if exists areas;create table areas(aid varchar(10) primary key, atitle varchar(20),pid varchar(10));​insert into areas values ('130000', '河北省', NULL), ('130100', '石家庄市', '130000'),('130400', '邯郸市', '130000'), ('130600', '保定市', '130000'),('130700', '张家口市', '130000'),('130800', '承德市', '130000'),('410000', '河南省', NULL), ('410100', '郑州市', '410000'),('410300', '洛阳市', '410000'),('410500', '安阳市', '410000'),('410700', '新乡市', '410000'),('410800', '焦作市', '410000'),('410101', '中原区', '410100'),('410102', '二七区', '410100'),('410301', '洛龙区', '410300');

2.【知道】自关联实现

• 要通过自关联进行查询时，当前自关联的表当中一定会存在两个相关联的字段

• 自关联要用别名

• 语法格式

  select * from 表名 as 别名1 inner join 表名 as 别名2 on别名1.列=别名2.列

• 案例

  -- 查询出河南省所有的市select * from areas as a1 inner join areas as a2 on a1.aid=a2.pid where a1.atitle='河南省';​-- 查询出郑州市所有的区select * from areas as a1 inner join areas as a2 on a1.aid=a2.pid where a1.atitle='郑州市';​-- 查询出河南省所有的市区信息select * from areas as a1 inner join areas as a2 on a1.aid=a2.pidleft join areas as a3 on a2.aid=a3.pid where a1.atitle='河南省';​​-- 1、 查询河北省所有的市的信息select * from areas as a1 inner join areas as a2 on a1.aid=a2.pid where a1.atitle='河北省';​-- 2、 查询出洛阳市所有的区的信息select * from areas as a1 inner join areas as a2 on a1.aid=a2.pid where a1.atitle='洛阳市';

三、子查询

• 将一条SQL查询的语句嵌入在其他的SQL语句中，被嵌入的SQL语句称之为子查询，其他的SQL称之为主查询

  • 子查询select语句，要么是充当条件，要么充当数据源

  • 子查询语句是一条完整的select语句，且可以单独执行

1.【重点】子查询充当条件

-- 例1：查询王昭君的成绩，要求显示成绩（标量子查询）select studentNo from students where name='王昭君';select * from scores where studentNo=(select studentNo from students where name='王昭君');​-- 例2：查询18岁的学生的成绩，要求显示成绩（列子查询）select studentNo from students where age=18;select score from scores where studentNo in (select studentNo from students where age=18);​-- 例3：查询和王昭君同班、同龄的学生信息（行子查询）select class, age from students where name='王昭君';​select * from students where (class, age)=(select class, age from students where name='王昭君');

2.【知道】子查询充当数据源

-- 例1：查询数据库和系统测试的课程成绩select * from scores as sc inner join (select * from courses where name in ('数据库','系统测试')) as co on sc.courseNo=co.courseNo;

• 练习

  -- 1、 查询大于平均年龄的学生select avg(age) from students;select * from students where age > (select avg(age) from students);​-- 2、 查询年龄在18-20之间的学生的成绩select * from students where age between 18 and 20;select * from scores as sc inner join (select * from students where age between 18 and 20) as stu on sc.studentNo=stu.studentNo;

四、查询演练

• 数据准备

  /***创建部门表*/drop table if exists departments;create table departments (deptid int(10) primary key,deptname varchar(20) not null -- 部门名称);insert into departments values ('1001', '市场部');insert into departments values ('1002', '测试部');insert into departments values ('1003', '开发部');​/***创建员工表*/drop table if exists employees;create table employees (empid int(10) primary key,empname varchar(20) not null, -- 姓名sex varchar(4) default null, -- 性别deptid int(20) default null, -- 部门编号jobs varchar(20) default null, -- 岗位politicalstatus varchar(20) default null, -- 政治面貌leader int(10) default null);insert into employees values ('1', '王昭君', '女', '1003', '开发', '群众', '9');insert into employees values ('2', '诸葛亮', '男', '1003', '开发经理', '群众', null);insert into employees values ('3', '张飞', '男', '1002', '测试', '团员', '4');insert into employees values ('4', '白起', '男', '1002', '测试经理', '党员', null);insert into employees values ('5', '大乔', '女', '1002', '测试', '党员', '4');insert into employees values ('6', '孙尚香', '女', '1001', '市场', '党员', '12');insert into employees values ('7', '百里玄策', '男', '1001', '市场', '团员', '12');insert into employees values ('8', '小乔', '女', '1002', '测试', '群众', '4');insert into employees values ('9', '百里守约', '男', '1003', '开发', '党员', '9');insert into employees values ('10', '妲己', '女', '1003', '开发', '团员', '9');insert into employees values ('11', '李白', '男', '1002', '测试', '团员', '4');insert into employees values ('12', '孙膑', '男', '1001', '市场经理', '党员', null);​/***创建工资表*/drop table if exists salary;create table salary (sid int(10) primary key,empid int(10) not null,salary int(10) not null -- 工资);insert into salary values ('1', '7', '2100');insert into salary values ('2', '6', '2000');insert into salary values ('3', '12', '5000');insert into salary values ('4', '9', '1999');insert into salary values ('5', '10', '1900');insert into salary values ('6', '1', '3000');insert into salary values ('7', '2', '5500');insert into salary values ('8', '5', '2000');insert into salary values ('9', '3', '1500');insert into salary values ('10', '8', '4000');insert into salary values ('11', '11', '2600');insert into salary values ('12', '4', '5300');

• 参考答案：

  — 1、列出总人数大于4的部门号和总人数。(要统计所有部门的人数，需要使用分组, 同时也要使用聚合函数)
  select deptid, count(*) from employees group by deptid having count(*)>4;

  — 2、列出开发部和和测试部的职工号、姓名
  select deptid from departments where deptname in (‘开发部’,’测试部’);

  select empid, empname from employees where deptid in (
  select deptid from departments where deptname in (‘开发部’,’测试部’)
  );

  — 3、求出各部门党员的人数，要求显示部门名称。
  select * from employees as emp
  inner join departments as dep on emp.deptid=dep.deptid
  where emp.politicalstatus=’党员’;

  select dep.deptname from employees as emp
  inner join departments as dep on emp.deptid=dep.deptid
  where emp.politicalstatus=’党员’;

  select dep.deptname, count(*) from employees as emp
  inner join departments as dep on emp.deptid=dep.deptid
  where emp.politicalstatus=’党员’
  group by emp.deptid;

  — 4、列出市场部的所有女职工的姓名和政治面貌。
  select * from employees as emp
  inner join departments as dep on emp.deptid=dep.deptid
  where emp.sex=’女’ and dep.deptname=’市场部’;

  select emp.empname, emp.politicalstatus from employees as emp
  inner join departments as dep on emp.deptid=dep.deptid
  where emp.sex=’女’ and dep.deptname=’市场部’;

  — 5、显示所有职工的姓名、部门名和工资数。
  select * from employees as emp
  inner join departments as dep on emp.deptid=dep.deptid
  inner join salary as sa on sa.empid=emp.empid;

  select emp.empname, dep.deptname, sa.salary from employees as emp
  inner join departments as dep on emp.deptid=dep.deptid
  inner join salary as sa on sa.empid=emp.empid;

  — 6、显示各部门名和该部门的职工平均工资。
  select dep.deptname,avg(sa.salary) from employees as emp
  inner join departments as dep on emp.deptid=dep.deptid
  inner join salary as sa on sa.empid=emp.empid
  group by emp.deptid;

  — 7、显示工资最高的前3名职工的职工号和姓名。
  select emp.empname, dep.deptname, sa.salary from employees as emp
  inner join departments as dep on emp.deptid=dep.deptid
  inner join salary as sa on sa.empid=emp.empid
  order by sa.salary desc limit 3 — limit 3 等价于 limit 0, 3

  — 8、列出工资在1000－2000之间的所有职工姓名。
  select emp.empname, dep.deptname, sa.salary from employees as emp
  inner join departments as dep on emp.deptid=dep.deptid
  inner join salary as sa on sa.empid=emp.empid
  where sa.salary between 1000 and 2000;

  — 9、列出工资比王昭君高的员工。(首先查询王昭君的工资)
  — 查询王昭君的工资
  select sa.salary from employees as emp
  inner join salary as sa on sa.empid=emp.empid
  where emp.empname=’王昭君’

  select emp.empname, sa.salary from employees as emp
  inner join salary as sa on sa.empid=emp.empid
  where sa.salary > (
  select sa.salary from employees as emp
  inner join salary as sa on sa.empid=emp.empid
  where emp.empname=’王昭君’
  );

  — 10、列出每个部门中工资小于本部门平均工资的员工信息。(首先查询出每个部门的平均工资)
  — 查询出每个部门的平均工资
  select emp.deptid, avg(sa.salary) as avg_salary from employees as emp
  inner join departments as dep on emp.deptid=dep.deptid
  inner join salary as sa on sa.empid=emp.empid
  group by emp.deptid

  select emp.deptid, emp.empname, sa.salary from employees as emp
  inner join salary as sa on sa.empid=emp.empid
  inner join (
  select emp.deptid, avg(sa.salary) as avg_salary from employees as emp
  inner join departments as dep on emp.deptid=dep.deptid
  inner join salary as sa on sa.empid=emp.empid
  group by emp.deptid
  ) as c on emp.deptid=c.deptid
  where sa.salary < c.avg_salary;