MySQL数据库 *实验报告10 子查询

欢迎来到言人冰的博客，本次实验有亿点点难度

首先创建两个数据库 xscj3 和 ygg3

+-----------------+
| Tables_in_xscj3 |
+-----------------+
| kc              |
| xs              |
| xs_kc           |
+-----------------+
+----------------+
| Tables_in_ygg3 |
+----------------+
| departments    |
| employees      |
| salary         |
+----------------+

先来看看 数据库ygg3中的表

1、查找yggl数据库中收入最高的员工的编号 、姓名和收入

select 编号,姓名,收入 from salary
join employees using(编号)
where 收入 = (select max(收入) from salary);
+--------+------+---------+
| 编号   | 姓名 | 收入    |
+--------+------+---------+
| 108991 | 钟敏 | 3259.98 |
+--------+------+---------+
1 row in set (0.00 sec)

2、查找yggl数据库中收入比平均工资高的员工的编号、姓名和收入（计算平均工资的语句也要贴图）

 select avg(收入) as 平均工资 from salary;
+--------------------+
| 平均工资           |
+--------------------+
| 2290.9845909705527 |
+--------------------+

 select 编号,姓名,收入 from salary join employees using(编号) where 收入 > (select avg(收入) from salary);
+--------+--------+---------+
| 编号   | 姓名   | 收入    |
+--------+--------+---------+
| 020010 | 王向容 |    2860 |
| 020018 | 李丽   | 2347.68 |
| 102201 | 刘明   | 2569.88 |
| 108991 | 钟敏   | 3259.98 |
| 302566 | 李玉珉 |  2980.7 |
| 308759 | 叶凡   | 2531.98 |
+--------+--------+---------+

3、查找yggl数据库，列出比所有男员工工资高的女员工（编号、姓名、性别、收入）

【！】先查看表 employees 中所有员工的收入情况
mysql> select 姓名,
    -> case
    -> when 性别 = 1 then '男'
    -> else  '女'
    -> end as 性别,
    -> 收入 from salary join employees using(编号);
+--------+------+---------+
| 姓名   | 性别 | 收入    |
+--------+------+---------+
| 王林   | 男   |  2100.8 |
| 伍容华 | 男   | 1582.62 |
| 王向容 | 男   |    2860 |
| 李丽   | 女   | 2347.68 |
| 刘明   | 男   | 2569.88 |
| 朱俊   | 男   |    1456 |
| 朱慧名 | 男   |    1800 |
| 钟敏   | 女   | 3259.98 |
| 张石兵 | 男   | 1987.01 |
| 林涛   | 男   |    2240 |
| 李玉珉 | 男   |  2980.7 |
| 叶凡   | 男   | 2531.98 |
| 陈林琳 | 女   | 2066.15 |
+--------+------+---------+
13 rows in set (0.00 sec)

【注】很显然，在所有女生中，其收入比所有男生都高的只有1位，即 钟敏 收入位为 3259.98

以下代码能够找出该女员工：
 
 select 编号,姓名,性别,收入 from employees join salary using(编号)
 where 性别 = 0 
 and 收入 > all(select 收入 from salary join employees using(编号) where 性别 = 1);
+--------+------+------+---------+
| 编号   | 姓名 | 性别 | 收入    |
+--------+------+------+---------+
| 108991 | 钟敏 |    0 | 3259.98 |
+--------+------+------+---------+

先来看看 数据库xscj3中的表

4、查找xscj数据库，列出比女同学平均分低的男同学姓名，显示学号、姓名、性别、成绩（性别=0为女同学）

首先来看一下女生的平均成绩
 
 select avg(成绩) as 女生的平均成绩 from xs_kc join xs using(学号) where 性别 = 0;
+----------------+
| 女生的平均成绩  |
+----------------+
|        73.6667 |
+----------------+

再来领略一下全班男生的成绩

mysql> select 学号,姓名,性别,成绩 from xs join xs_kc using(学号) where 性别 = 1;
+--------+--------+------+------+
| 学号   | 姓名   | 性别 | 成绩 |
+--------+--------+------+------+
| 081101 | 王林   |    1 |   80 |
| 081101 | 王林   |    1 |   78 |
| 081102 | 程明   |    1 |   91 |
| 081102 | 程明   |    1 |   80 |
| 081102 | 程明   |    1 |   76 |
| 081104 | 韦平平 |    1 |   65 |《《《《《比女生平均成绩低（用*表示）
| 081201 | 王敏   |    1 |   76 |
| 081202 | 王林   |    1 |   78 |
| 081301 | 李计   |    1 |   71 |《《《《《 *
| 081302 | 李红庆 |    1 |   78 |
| 081302 | 李红庆 |    1 |   89 |
| 081303 | 孙祥欣 |    1 |   63 |《《《《《 *
| 081303 | 孙祥欣 |    1 |   81 |
| 081304 | 孙研   |    1 |   65 |《《《《《 *
| 081304 | 孙研   |    1 |   84 |
| 081304 | 孙研   |    1 |   76 |
+--------+--------+------+------+

最后来输出这几个害羞男生的名单吧
mysql> select 学号,姓名,性别,成绩 from xs_kc join xs using(学号)
    -> where 性别 = 1
    -> and 成绩 < (select avg(成绩) from xs_kc join xs using(学号) where 性别 = 0);
+--------+--------+------+------+
| 学号   | 姓名   | 性别 | 成绩 |
+--------+--------+------+------+
| 081104 | 韦平平 |    1 |   65 |
| 081301 | 李计   |    1 |   71 |
| 081303 | 孙祥欣 |    1 |   63 |
| 081304 | 孙研   |    1 |   65 |
+--------+--------+------+------+
4 rows in set (0.00 sec)

！！！重点关注这道题

5、查找xscj数据库，查询没有被选修的课程（要求只显示没被选修的课程），显示的列为课程号、课程名、成绩

select  a.课程号 ,a.课程名,b.成绩 from kc a left join xs_kc b
on a.课程号 = b.课程号 where b.课程号 is null;
+--------+----------+------+
| 课程号 | 课程名   | 成绩 |
+--------+----------+------+
| 201    | 数据结构 | NULL |
+--------+----------+------+

》》》》》》》》》》》》》》》》》》》》》》》》》。。。。。。。。。。。。
补充一下 左外连接 这个知识点

感谢访问本实验，加油！！！

感谢访问本实验，加油！！！

感谢访问本实验，加油！！！