针对一个表练习
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 ;