【10】查询练习：as、union、avg、group by

时间：2020-04-15 10:52:45 阅读：66 评论：0 收藏：0 [点我收藏+]

1.as取别名

查询所有教师和同学的姓名、性别、生日：

错误查询：

mysql> select stu_name,tea_name,stu_sex,tea_sex,stu_birth,tea_birth from student,teacher;
+----------+-------------+---------+---------+---------------------+---------------------+
| stu_name | tea_name    | stu_sex | tea_sex | stu_birth           | tea_birth           |
+----------+-------------+---------+---------+---------------------+---------------------+
| JiaWei   | LiMei       | F       | F       | 1993-07-28 00:00:00 | 1983-02-24 00:00:00 |
| JiaWei   | MaDi        | F       | F       | 1993-07-28 00:00:00 | 1984-01-23 00:00:00 |
| JiaWei   | LiZhe       | F       | F       | 1993-07-28 00:00:00 | 1974-01-23 00:00:00 |
| JiaWei   | ShaoGuoYing | F       | F       | 1993-07-28 00:00:00 | 1985-06-17 00:00:00 |
| JiaWei   | Susan       | F       | F       | 1993-07-28 00:00:00 | 1985-07-18 00:00:00 |
| JiaWei   | Mary        | F       | F       | 1993-07-28 00:00:00 | 1990-05-02 00:00:00 |
| DingQi   | LiMei       | F       | F       | 1994-08-15 00:00:00 | 1983-02-24 00:00:00 |
| DingQi   | MaDi        | F       | F       | 1994-08-15 00:00:00 | 1984-01-23 00:00:00 |
| DingQi   | LiZhe       | F       | F       | 1994-08-15 00:00:00 | 1974-01-23 00:00:00 |
| DingQi   | ShaoGuoYing | F       | F       | 1994-08-15 00:00:00 | 1985-06-17 00:00:00 |
| DingQi   | Susan       | F       | F       | 1994-08-15 00:00:00 | 1985-07-18 00:00:00 |
| DingQi   | Mary        | F       | F       | 1994-08-15 00:00:00 | 1990-05-02 00:00:00 |
| Baker    | LiMei       | F       | F       | 1999-09-22 00:00:00 | 1983-02-24 00:00:00 |
| Baker    | MaDi        | F       | F       | 1999-09-22 00:00:00 | 1984-01-23 00:00:00 |
| Baker    | LiZhe       | F       | F       | 1999-09-22 00:00:00 | 1974-01-23 00:00:00 |
| Baker    | ShaoGuoYing | F       | F       | 1999-09-22 00:00:00 | 1985-06-17 00:00:00 |
| Baker    | Susan       | F       | F       | 1999-09-22 00:00:00 | 1985-07-18 00:00:00 |
| Baker    | Mary        | F       | F       | 1999-09-22 00:00:00 | 1990-05-02 00:00:00 |
| Bob      | LiMei       | M       | F       | 1998-04-25 00:00:00 | 1983-02-24 00:00:00 |
| Bob      | MaDi        | M       | F       | 1998-04-25 00:00:00 | 1984-01-23 00:00:00 |
| Bob      | LiZhe       | M       | F       | 1998-04-25 00:00:00 | 1974-01-23 00:00:00 |
| Bob      | ShaoGuoYing | M       | F       | 1998-04-25 00:00:00 | 1985-06-17 00:00:00 |
| Bob      | Susan       | M       | F       | 1998-04-25 00:00:00 | 1985-07-18 00:00:00 |
| Bob      | Mary        | M       | F       | 1998-04-25 00:00:00 | 1990-05-02 00:00:00 |
| LinJie   | LiMei       | M       | F       | 1994-06-12 00:00:00 | 1983-02-24 00:00:00 |
| LinJie   | MaDi        | M       | F       | 1994-06-12 00:00:00 | 1984-01-23 00:00:00 |
| LinJie   | LiZhe       | M       | F       | 1994-06-12 00:00:00 | 1974-01-23 00:00:00 |
| LinJie   | ShaoGuoYing | M       | F       | 1994-06-12 00:00:00 | 1985-06-17 00:00:00 |
| LinJie   | Susan       | M       | F       | 1994-06-12 00:00:00 | 1985-07-18 00:00:00 |
| LinJie   | Mary        | M       | F       | 1994-06-12 00:00:00 | 1990-05-02 00:00:00 |
| XieZhou  | LiMei       | M       | F       | 1995-03-11 00:00:00 | 1983-02-24 00:00:00 |
| XieZhou  | MaDi        | M       | F       | 1995-03-11 00:00:00 | 1984-01-23 00:00:00 |
| XieZhou  | LiZhe       | M       | F       | 1995-03-11 00:00:00 | 1974-01-23 00:00:00 |
| XieZhou  | ShaoGuoYing | M       | F       | 1995-03-11 00:00:00 | 1985-06-17 00:00:00 |
| XieZhou  | Susan       | M       | F       | 1995-03-11 00:00:00 | 1985-07-18 00:00:00 |
| XieZhou  | Mary        | M       | F       | 1995-03-11 00:00:00 | 1990-05-02 00:00:00 |
| MingHui  | LiMei       | F       | F       | 1998-08-09 00:00:00 | 1983-02-24 00:00:00 |
| MingHui  | MaDi        | F       | F       | 1998-08-09 00:00:00 | 1984-01-23 00:00:00 |
| MingHui  | LiZhe       | F       | F       | 1998-08-09 00:00:00 | 1974-01-23 00:00:00 |
| MingHui  | ShaoGuoYing | F       | F       | 1998-08-09 00:00:00 | 1985-06-17 00:00:00 |
| MingHui  | Susan       | F       | F       | 1998-08-09 00:00:00 | 1985-07-18 00:00:00 |
| MingHui  | Mary        | F       | F       | 1998-08-09 00:00:00 | 1990-05-02 00:00:00 |
| NanNan   | LiMei       | F       | F       | 1995-10-20 00:00:00 | 1983-02-24 00:00:00 |
| NanNan   | MaDi        | F       | F       | 1995-10-20 00:00:00 | 1984-01-23 00:00:00 |
| NanNan   | LiZhe       | F       | F       | 1995-10-20 00:00:00 | 1974-01-23 00:00:00 |
| NanNan   | ShaoGuoYing | F       | F       | 1995-10-20 00:00:00 | 1985-06-17 00:00:00 |
| NanNan   | Susan       | F       | F       | 1995-10-20 00:00:00 | 1985-07-18 00:00:00 |
| NanNan   | Mary        | F       | F       | 1995-10-20 00:00:00 | 1990-05-02 00:00:00 |
+----------+-------------+---------+---------+---------------------+---------------------+

View Code

学生的：

mysql> select stu_name,stu_sex,stu_birth from student;
+----------+---------+---------------------+
| stu_name | stu_sex | stu_birth           |
+----------+---------+---------------------+
| JiaWei   | F       | 1993-07-28 00:00:00 |
| DingQi   | F       | 1994-08-15 00:00:00 |
| Baker    | F       | 1999-09-22 00:00:00 |
| Bob      | M       | 1998-04-25 00:00:00 |
| LinJie   | M       | 1994-06-12 00:00:00 |
| XieZhou  | M       | 1995-03-11 00:00:00 |
| MingHui  | F       | 1998-08-09 00:00:00 |
| NanNan   | F       | 1995-10-20 00:00:00 |
+----------+---------+---------------------+

教师的：

mysql> select tea_name,tea_sex,tea_birth from teacher;
+-------------+---------+---------------------+
| tea_name    | tea_sex | tea_birth           |
+-------------+---------+---------------------+
| LiMei       | F       | 1983-02-24 00:00:00 |
| MaDi        | F       | 1984-01-23 00:00:00 |
| LiZhe       | F       | 1974-01-23 00:00:00 |
| ShaoGuoYing | F       | 1985-06-17 00:00:00 |
| Susan       | F       | 1985-07-18 00:00:00 |
| Mary        | F       | 1990-05-02 00:00:00 |
+-------------+---------+---------------------+

使用union时：

mysql> select stu_name,stu_sex,stu_birth from student
    -> union
    -> select tea_name,tea_sex,tea_birth from teacher;
+-------------+---------+---------------------+
| stu_name    | stu_sex | stu_birth           |
+-------------+---------+---------------------+
| JiaWei      | F       | 1993-07-28 00:00:00 |
| DingQi      | F       | 1994-08-15 00:00:00 |
| Baker       | F       | 1999-09-22 00:00:00 |
| Bob         | M       | 1998-04-25 00:00:00 |
| LinJie      | M       | 1994-06-12 00:00:00 |
| XieZhou     | M       | 1995-03-11 00:00:00 |
| MingHui     | F       | 1998-08-09 00:00:00 |
| NanNan      | F       | 1995-10-20 00:00:00 |
| LiMei       | F       | 1983-02-24 00:00:00 |
| MaDi        | F       | 1984-01-23 00:00:00 |
| LiZhe       | F       | 1974-01-23 00:00:00 |
| ShaoGuoYing | F       | 1985-06-17 00:00:00 |
| Susan       | F       | 1985-07-18 00:00:00 |
| Mary        | F       | 1990-05-02 00:00:00 |
+-------------+---------+---------------------+

更改字段名称：

mysql> select tea_name as name,tea_sex as sex,tea_birth as birthday from teacher
    -> union
    -> select stu_name,stu_sex,stu_birth from student;
+-------------+-----+---------------------+
| name        | sex | birthday            |
+-------------+-----+---------------------+
| LiMei       | F   | 1983-02-24 00:00:00 |
| MaDi        | F   | 1984-01-23 00:00:00 |
| LiZhe       | F   | 1974-01-23 00:00:00 |
| ShaoGuoYing | F   | 1985-06-17 00:00:00 |
| Susan       | F   | 1985-07-18 00:00:00 |
| Mary        | F   | 1990-05-02 00:00:00 |
| JiaWei      | F   | 1993-07-28 00:00:00 |
| DingQi      | F   | 1994-08-15 00:00:00 |
| Baker       | F   | 1999-09-22 00:00:00 |
| Bob         | M   | 1998-04-25 00:00:00 |
| LinJie      | M   | 1994-06-12 00:00:00 |
| XieZhou     | M   | 1995-03-11 00:00:00 |
| MingHui     | F   | 1998-08-09 00:00:00 |
| NanNan      | F   | 1995-10-20 00:00:00 |
+-------------+-----+---------------------+

2.查询成绩比该课程平均成绩低的同学的成绩表

mysql> select * from score;
+---------+----------+--------+
| stu_num | cour_num | degree |
+---------+----------+--------+
| 11422   | 3-105    |     92 |
| 11423   | 1-245    |     84 |
| 11423   | 2-271    |     75 |
| 11424   | 4-321    |     75 |
| 11425   | 2-271    |     89 |
| 11426   | 1-245    |     61 |
| 11426   | 2-271    |     82 |
| 11427   | 1-245    |     78 |
+---------+----------+--------+

平均值：

mysql> select cour_num,avg(degree) from score group by cour_num;
+----------+-------------+
| cour_num | avg(degree) |
+----------+-------------+
| 1-245    |     74.3333 |
| 2-271    |     82.0000 |
| 3-105    |     92.0000 |
| 4-321    |     75.0000 |
+----------+-------------+

复制表数据做条件查询：

mysql> select * from score a
    -> where degree<(select avg(degree) from score b where a.cour_num=b.cour_num);
+---------+----------+--------+
| stu_num | cour_num | degree |
+---------+----------+--------+
| 11423   | 2-271    |     75 |
| 11426   | 1-245    |     61 |
+---------+----------+--------+

【10】查询练习：as、union、avg、group by

原文：https://www.cnblogs.com/direwolf22/p/12684016.html

踩

(0)

评论一句话评论（0）

分享档案

更多>

2021年09月23日 (328)
2021年09月24日 (313)
2021年09月17日 (191)
2021年09月15日 (369)
2021年09月16日 (411)
2021年09月13日 (439)
2021年09月11日 (398)
2021年09月12日 (393)
2021年09月10日 (160)
2021年09月08日 (222)