sql語句學習,sql語句
針對一個表練習
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 ;
/********************************
* 本文來自部落格 “李博Garvin“
* 轉載請標明出處:http://blog.csdn.net/buptgshengod
******************************************/