针对一个表练习
1.建表
create table student(name Char(20),curriculum Char(20),score Char(20));
插入数据：
INSERT INTO student (name,curriculum,score) VALUES('王五','数学','100');
mysql> select * from student;
+--------+------------+-------+
| name | curriculum | score |
+--------+------------+-------+
| 张三 | 语文 | 81 |
| 张三 | 数学 | 75 |
| 李四 | 语文 | 76 |
| 李四 | 数学 | 90 |
| 王五 | 语文 | 81 |
| 王五 | 数学 | 100 |
+--------+------------+-------+
2.题目开始喽
(1)用一条SQL语句 查询出每门课都大于80分的学生姓名 ,注意理解group by
答案：
select distinct name from student where name not in (select distinct name from student where score<80);
+--------+
| name |
+--------+
| 王五 |
+--------+
select distinct * from student where name not in (select distinct name from student where score<80);
+--------+------------+-------+
| name | curriculum | score |
+--------+------------+-------+
| 王五 | 语文 | 81 |
| 王五 | 数学 | 100 |
+--------+------------+-------+
select distinct * from student where name not in (select distinct name from student where score<80) group by name;
+--------+------------+-------+
| name | curriculum | score |
+--------+------------+-------+
| 王五 | 语文 | 81 |
+--------+------------+-------+
(2)形成如下表格
+--------+--------+--------+
| name | 语文 | 数学 |
+--------+--------+--------+
| 张三 | 81 | 75 |
| 李四 | 76 | 90 |
| 王五 | 81 | 100 |
+--------+--------+--------+
答案：
select name,(select score from student s where curriculum='语文' and s.name=student.name) as 语文,
(select score from student s where curriculum='数学' and s.name=student.name) as 数学 from student group by name;
(3)显示每一科是否及格，利用case when
+--------+------------+-------+-----------+
| name | curriculum | score | pass |
+--------+------------+-------+-----------+
| 张三 | 语文 | 81 | 及格 |
| 张三 | 数学 | 75 | 不及格 |
| 李四 | 语文 | 76 | 不及格 |
| 李四 | 数学 | 90 | 及格 |
| 王五 | 语文 | 81 | 及格 |
| 王五 | 数学 | 100 | 及格 |
+--------+------------+-------+-----------+
答案：
select name, curriculum,score,(CASE WHEN student.score>=80 THEN '及格' ELSE '不及格' END) as pass from student ;
(4)按分数排序order by
+--------+------------+-------+
| name | curriculum | score |
+--------+------------+-------+
| 王五 | 数学 | 100 |
| 李四 | 数学 | 90 |
| 张三 | 语文 | 81 |
| 王五 | 语文 | 81 |
| 李四 | 语文 | 76 |
| 张三 | 数学 | 75 |
+--------+------------+-------+
答案：（+0因为是char转int）
select * from student order by score+0 desc ;