MySQL: A small practice for where/group by/having/order by clause

davyjones2010

于 2013-04-09 23:53:41 发布

阅读量126

点赞数

分类专栏： MySQL 文章标签： MySQL Practice

本文链接：https://blog.csdn.net/Davyjones2010/article/details/84417266

版权

MySQL 专栏收录该内容

16 篇文章 0 订阅

订阅专栏

# Here is an example for five sql caluse
# Pay attention to the sequence of the four clause
# select *** from *** where *** group by *** having *** order by *** limit ***
select stu_name, avg(stu_score) as avg_score from stu where stu_name != (select stu_name from stu where stu_score = 90) group by stu_name having avg_score> 30 order by avg_score desc;

#################################################################################
# Examples are as below
# 1. Create a table
create table stu (stu_name varchar(20), stu_course varchar(20), stu_score int);

# 2. Insert data
mysql> insert into stu values('zhangsan', 'Math', 90);
Query OK, 1 row affected

mysql> insert into stu values('zhangsan', 'Literature', 50);
Query OK, 1 row affected

mysql> insert into stu values('zhangsan', 'Geograph', 40);
Query OK, 1 row affected

mysql> insert into stu values('lisi', 'Literature', 55);
Query OK, 1 row affected

mysql> insert into stu values('lisi', 'Politic', 4
5);
Query OK, 1 row affected

mysql> insert into stu values('wangwu', 'Politic', 30);
Query OK, 1 row affected

# 3. Purpose: Try to find the average score for the students whose invalid course count is bigger than 2.

## 3.1 Try to find the average score for all the students
select stu_name, avg(stu_score) from stu group by stu_name;

## 3.2 Try to find the average score for the students whose invalid course count is bigger than 2
select stu_name, avg(stu_score) as avg_score, sum(stu_score < 60) as invalid_count from stu group by stu_name having invalid_count >= 2;