查询练习

1、查询student表的所有记录；

ysql> select * from student;
+-----+--------------+------+---------------------+-------+
| sno | sname        | ssex | sbirthday           | class |
+-----+--------------+------+---------------------+-------+
| 101 | zenghua      | nan  | 1977-09-01 00:00:00 | 95033 |
| 102 | kuangming    | nan  | 1975-10-02 00:00:00 | 95031 |
| 103 | wangli       | nv   | 1976-01-23 00:00:00 | 95033 |
| 104 | lijun        | nan  | 1976-02-20 00:00:00 | 95033 |
| 105 | wangfang     | nv   | 1975-02-10 00:00:00 | 95031 |
| 106 | lujun        | nan  | 1974-06-03 00:00:00 | 95031 |
| 107 | wangnima     | nan  | 1976-02-20 00:00:00 | 95033 |
| 108 | zhangquandan | nan  | 1975-02-10 00:00:00 | 95031 |
| 109 | zhaotiezhu   | nan  | 1974-06-03 00:00:00 | 95031 |
+-----+--------------+------+---------------------+-------+

2、查询student表中的所有记录的sname、ssex、class列；

mysql> select sname,ssex,class from 
student;
+--------------+------+-------+
| sname        | ssex | class |
+--------------+------+-------+
| zenghua      | nan  | 95033 |
| kuangming    | nan  | 95031 |
| wangli       | nv   | 95033 |
| lijun        | nan  | 95033 |
| wangfang     | nv   | 95031 |
| lujun        | nan  | 95031 |
| wangnima     | nan  | 95033 |
| zhangquandan | nan  | 95031 |
| zhaotiezhu   | nan  | 95031 |
+--------------+------+-------+

3、查询教师所有的单位即不重复的depart列。

//----distinct 排除重复
mysql> select distinct depart from 
teacher;
+-------------------+
| depart            |
+-------------------+
| jisuanjixi        |
| dianzigongchengxi |

4、查询score表中成绩在60-80之间的所有记录。
----查询区间 between…and…

mysql> select * from score where degree
 > 60 and degree < 80;
或
mysql> select * from score where 
degree between 60 and 80;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 105 | 3-245 |     75 |
| 105 | 6-166 |     79 |
| 109 | 3-105 |     76 |
| 109 | 3-245 |     68 |
+-----+-------+--------+

5.查询score表中成绩为85,86或88的记录。

----表示或者关系的查询 in

mysql> select * from score where degree in(85,86,88);
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-245 |     86 |
| 103 | 6-166 |     85 |
| 105 | 3-105 |     88 |
+-----+-------+--------+

6、查询student表中“95031”班或性别为“女”的同学记录。

----or表示或者

mysql> select * from student where class='95031'or'ssex=nv';
+-----+--------------+------+---------------------+-------+
| sno | sname        | ssex | sbirthday           | class |
+-----+--------------+------+---------------------+-------+
| 102 | kuangming    | nan  | 1975-10-02 00:00:00 | 95031 |
| 105 | wangfang     | nv   | 1975-02-10 00:00:00 | 95031 |
| 106 | lujun        | nan  | 1974-06-03 00:00:00 | 95031 |
| 108 | zhangquandan | nan  | 1975-02-10 00:00:00 | 95031 |
| 109 | zhaotiezhu   | nan  | 1974-06-03 00:00:00 | 95031 |
+-----+--------------+------+---------------------+-------+

7、以class降序查询student表的所有记录。

----升序，降序

mysql> select * from student order by class desc;
+-----+--------------+------+---------------------+-------+
| sno | sname        | ssex | sbirthday           | class |
+-----+--------------+------+---------------------+-------+
| 101 | zenghua      | nan  | 1977-09-01 00:00:00 | 95033 |
| 103 | wangli       | nv   | 1976-01-23 00:00:00 | 95033 |
| 104 | lijun        | nan  | 1976-02-20 00:00:00 | 95033 |
| 107 | wangnima     | nan  | 1976-02-20 00:00:00 | 95033 |
| 102 | kuangming    | nan  | 1975-10-02 00:00:00 | 95031 |
| 105 | wangfang     | nv   | 1975-02-10 00:00:00 | 95031 |
| 106 | lujun        | nan  | 1974-06-03 00:00:00 | 95031 |
| 108 | zhangquandan | nan  | 1975-02-10 00:00:00 | 95031 |
| 109 | zhaotiezhu   | nan  | 1974-06-03 00:00:00 | 95031 |
+-----+--------------+------+---------------------+-------+

----asc升序（不加asc也行，默认升序）

mysql> select * from student order by class asc;
+-----+--------------+------+---------------------+-------+
| sno | sname        | ssex | sbirthday           | class |
+-----+--------------+------+---------------------+-------+
| 102 | kuangming    | nan  | 1975-10-02 00:00:00 | 95031 |
| 105 | wangfang     | nv   | 1975-02-10 00:00:00 | 95031 |
| 106 | lujun        | nan  | 1974-06-03 00:00:00 | 95031 |
| 108 | zhangquandan | nan  | 1975-02-10 00:00:00 | 95031 |
| 109 | zhaotiezhu   | nan  | 1974-06-03 00:00:00 | 95031 |
| 101 | zenghua      | nan  | 1977-09-01 00:00:00 | 95033 |
| 103 | wangli       | nv   | 1976-01-23 00:00:00 | 95033 |
| 104 | lijun        | nan  | 1976-02-20 00:00:00 | 95033 |
| 107 | wangnima     | nan  | 1976-02-20 00:00:00 | 95033 |
+-----+--------------+------+---------------------+-------+

8、以cno升序、degree降序查询score表的所有记录。

mysql> select * from score order by cno asc,degree desc;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |     92 |
| 105 | 3-105 |     88 |
| 109 | 3-105 |     76 |
| 103 | 3-245 |     86 |
| 105 | 3-245 |     75 |
| 109 | 3-245 |     68 |
| 103 | 6-166 |     85 |
| 109 | 6-166 |     81 |
| 105 | 6-166 |     79 |
+-----+-------+--------+

9、查询“95031”班的学生人数。

----统计 count

mysql> select count(*) from student where class='95031';
+----------+
| count(*) |
+----------+
|        5 |
+----------+

10、查询score表中的最高分的学生号和课程号。（子查询或者排序）

mysql> select sno,cno from score where 
degree=(select max(degree) from score);
+-----+-------+
| sno | cno   |
+-----+-------+
| 103 | 3-105 |
+-----+-------+