mysql查询练习 distinct count max avg limit x,y order by desc, asc having（分组） where（分行） like

1. 查询table student中所有记录

SELECT * FROM student;

2. 查询student表中所有记录的s_name,s_sex和s_class列

select s_name, s_sex, s_class from student;

3. 查询教师所有的单位(t_depart)但是不重复的t_depart列

select distinct(t_depart) from teacher;
+------------+
| t_depart   |
+------------+
| 计算机系   |
| 电子工程系 |
+------------+

4. 查询score表中成绩在60-80之间所有的记录(sc_degree)

select * from score where sc_degree < 80 and sc_degree > 60;
+------+-------+-----------+
| s_no | c_no  | sc_degree |
+------+-------+-----------+
| 105  | 3-245 |        75 |
| 105  | 6-166 |        79 |
| 109  | 3-105 |        76 |
| 109  | 3-245 |        68 |
+------+-------+-----------+

5. 查询score表中成绩为85, 86, 或者88的记录(sc_degree)

select * from score where sc_degree in (85,86,88);
+------+-------+-----------+
| s_no | c_no  | sc_degree |
+------+-------+-----------+
| 103  | 6-166 |        85 |
| 105  | 3-105 |        88 |
+------+-------+-----------+

6.查询student表中’95031’班或者性别为’女’的同学记录

select * from student where s_class = '95031' or s_sex = '女';
+------+--------+-------+---------------------+---------+
| s_no | s_name | s_sex | s_birthday          | s_class |
+------+--------+-------+---------------------+---------+
| 102  | 匡明   | 男    | 1975-10-02 00:00:00 | 95031   |
| 103  | 王丽   | 女    | 1976-01-23 00:00:00 | 95033   |
| 105  | 王芳   | 女    | 1975-02-10 00:00:00 | 95031   |
| 106  | 陆军   | 男    | 1974-06-03 00:00:00 | 95031   |
| 108  | 张全蛋 | 男    | 1975-02-10 00:00:00 | 95031   |
| 109  | 赵铁柱 | 男    | 1974-06-03 00:00:00 | 95031   |
+------+--------+-------+---------------------+---------+

7.以class降序查询student表中所有的记录

select * from student order by s_class desc;
mysql> select * from student order by s_class desc;
+------+--------+-------+---------------------+---------+
| s_no | s_name | s_sex | s_birthday          | s_class |
+------+--------+-------+---------------------+---------+
| 101  | 曾华   | 男    | 1977-09-01 00:00:00 | 95033   |
| 103  | 王丽   | 女    | 1976-01-23 00:00:00 | 95033   |
| 104  | 李军   | 男    | 1976-02-20 00:00:00 | 95033   |
| 107  | 王尼玛 | 男    | 1976-02-20 00:00:00 | 95033   |
| 102  | 匡明   | 男    | 1975-10-02 00:00:00 | 95031   |
| 105  | 王芳   | 女    | 1975-02-10 00:00:00 | 95031   |
| 106  | 陆军   | 男    | 1974-06-03 00:00:00 | 95031   |
| 108  | 张全蛋 | 男    | 1975-02-10 00:00:00 | 95031   |
| 109  | 赵铁柱 | 男    | 1974-06-03 00:00:00 | 95031   |
+------+--------+-------+---------------------+---------+

8. 以c_no升序.sc_degree降序插叙score表中所有的数据
   (先以c_no进行升序,若c_no相同,则以sc_degree降序)

select * from score order by c_no asc, sc_degree desc;
+------+-------+-----------+
| s_no | c_no  | sc_degree |
+------+-------+-----------+
| 103  | 3-105 |        92 |
| 105  | 3-105 |        88 |
| 109  | 3-105 |        76 |
| 105  | 3-245 |        75 |
| 109  | 3-245 |        68 |
| 103  | 6-166 |        85 |
| 109  | 6-166 |        81 |
| 105  | 6-166 |        79 |
+------+-------+-----------+

9. 查询’95031’班的学生人数

select count(s_no) from student where s_class = '95031';
+-------------+
| count(s_no) |
+-------------+
|           5 |
+-------------+

10. 查询score表中的最高分数的学生号和课程号.(子查询或者排序)

select c_no, s_no, sc_degree from score where sc_degree = (select max(sc_degree) from score);
+-------+------+-----------+
| c_no  | s_no | sc_degree |
+-------+------+-----------+
| 3-105 | 103  |        92 |
+-------+------+-----------+

–子查询：
—1 找到最高分

select max(sc_degree)  from score;

—2 找最高分的s_no c_no

select  s_no,c_no from score where sc_degree = (select max(sc_degree)  from score);

–排序

SELECT c_no, s_no FROM score ORDER BY sc_degree desc LIMIT 0,1

但要注意排序有问题，最高分有多个（多个92）的话limit是要重新规定的
limit x,y (x:表示从X条数据开始 y:需要查出多少条)

11. 查询每门课的平均成绩

select c_no, avg(sc_degree) from score group by c_no;
+-------+----------------+
| c_no  | avg(sc_degree) |
+-------+----------------+
| 3-105 |        85.3333 |
| 3-245 |        71.5000 |
| 6-166 |        81.6667 |
+-------+----------------+

12. 查询score表中至少有2名学生选修的,并且以3开头的课程的平均分
    —having用于分组查询
    —where用于行级查询

select c_no, avg(sc_degree) from score group by c_no having count(c_no) >= 2 and c_no like '3%';
+-------+----------------+
| c_no  | avg(sc_degree) |
+-------+----------------+
| 3-105 |        85.3333 |
| 3-245 |        71.5000 |
+-------+----------------+

13. 查询分数大于70但是小于90的s_no列:

select s_no from score where sc_degree between 71 and 89;
+------+
| s_no |
+------+
| 103  |
| 105  |
| 105  |
| 105  |
| 109  |
| 109  |
+------+