MySQL练习题

SQL语句层次

from table 从哪张表找
where condition 找满足什么条件的
group by xxx 按照谁进行分组
having xxx 分组之后满足什么条件
order by xxx 按照谁排序（必须是输出列）
select 选择要输出的列

如：从cust_order表中找出2016年至今消费总额超过10000的用户及其消费总额，按用户id排序

select userid,sum(cust_amount) from cust_order where cust_date > '2016-01-01' group by userid having sum(cust_amount) > 10000 order by userid;

---

1.计算各种分数

建表：

create table grades(id int not null auto_increment,stu_id int,course_id int,grade int,constraint primary key(id));

插入数据:

insert into grades(stu_id,course_id,grade) values(1,1,100),(1,2,80),(1,3,75),(1,4,75),(2,1,70),(2,2,60),(2,3,75),(2,4,75),(3,1,90),(3,2,60),(3,3,85),(3,4,65);

题目1：查出每门课成绩都大于70分的学生。

select distinct stu_id from grades where stu_id not in (select stu_id from grades where grade < 70);

题目2：查出所以同学的平均分，并按成绩排名。

select stu_id,avg(grade) from grades group by stu_id order by 2 DESC;

题目3：查出平均分大于70分的学生及其平均分,按成绩排名。

select stu_id,avg(grade) from grades group by stu_id having avg(grade)>70 order by 2 DESC;

---

2.Case类型

建表：

create table salary(id int not null auto_increment,emp_id int,salary float(8,2),constraint primary key(id));

插入数据：

insert into salary(emp_id,salary) values (1,1000),(2,3000),(3,2000),(4,8000),(5,6000),(6,7000),(7,8000),(8,13000),(9,7000);

题目1：输出所有员工的信息，将员工分为三类 C类：工资小于5000； B类：工资 5000-10000；A类：工资大于10000

select emp_id,(case when salary < 5000 then 'C' when salary between 5000 and 10000 then 'B' when salary > 10000 then 'A' else 'NULL' end) as Type from salary;