1.查询student表中所有字段：
mysql> select * from student;
2.查询student表中指定某些字段：
mysql> select stu_name,stu_sex,class from student;
3.去重：DISTINCT
mysql> select cour_num from score; +----------+ | cour_num | +----------+ | 1-245 | | 1-245 | | 1-245 | | 2-271 | | 2-271 | | 2-271 | | 3-105 | | 4-321 | +----------+ 8 rows in set (0.00 sec) mysql> select distinct cour_num from score; +----------+ | cour_num | +----------+ | 1-245 | | 2-271 | | 3-105 | | 4-321 | +----------+ 4 rows in set (0.00 sec)
4.查询区间：BETWEEN ... AND...
mysql> select * from score where degree between 60 and 80; +---------+----------+--------+ | stu_num | cour_num | degree | +---------+----------+--------+ | 11423 | 2-271 | 75 | | 11424 | 4-321 | 75 | | 11426 | 1-245 | 61 | | 11427 | 1-245 | 78 | +---------+----------+--------+
或运算符比较：
mysql> select * from score where degree > 60 and degree < 80; +---------+----------+--------+ | stu_num | cour_num | degree | +---------+----------+--------+ | 11423 | 2-271 | 75 | | 11424 | 4-321 | 75 | | 11426 | 1-245 | 61 | | 11427 | 1-245 | 78 | +---------+----------+--------+
5.表示<或者关系>的查询：in
查询score表中成绩为75，82或84的记录：
mysql> select * from score where degree in(75,82,84); +---------+----------+--------+ | stu_num | cour_num | degree | +---------+----------+--------+ | 11423 | 1-245 | 84 | | 11423 | 2-271 | 75 | | 11424 | 4-321 | 75 | | 11426 | 2-271 | 82 | +---------+----------+--------+
6.查询student表中<班级为‘113‘>或<性别为‘M‘>的同学记录：
表示或者关系：or
mysql> select * from student where class=‘113‘ or stu_sex=‘M‘; +---------+----------+---------+---------------------+-------+ | stu_num | stu_name | stu_sex | stu_birth | class | +---------+----------+---------+---------------------+-------+ | 11328 | DingQi | F | 1994-08-15 00:00:00 | 113 | | 11423 | Bob | M | 1998-04-25 00:00:00 | 114 | | 11424 | LinJie | M | 1994-06-12 00:00:00 | 114 | | 11425 | XieZhou | M | 1995-03-11 00:00:00 | 114 | +---------+----------+---------+---------------------+-------+
7.以class降序查询student表中的所有记录（默认就是升序了）：
mysql> select * from student order by class desc; +---------+----------+---------+---------------------+-------+ | stu_num | stu_name | stu_sex | stu_birth | class | +---------+----------+---------+---------------------+-------+ | 11422 | Baker | F | 1999-09-22 00:00:00 | 114 | | 11423 | Bob | M | 1998-04-25 00:00:00 | 114 | | 11424 | LinJie | M | 1994-06-12 00:00:00 | 114 | | 11425 | XieZhou | M | 1995-03-11 00:00:00 | 114 | | 11426 | MingHui | F | 1998-08-09 00:00:00 | 114 | | 11427 | NanNan | F | 1995-10-20 00:00:00 | 114 | | 11328 | DingQi | F | 1994-08-15 00:00:00 | 113 | | 11215 | JiaWei | F | 1993-07-28 00:00:00 | 112 | +---------+----------+---------+---------------------+-------+
以（order by）成绩降序（desc）查询：
mysql> select * from score order by degree desc; +---------+----------+--------+ | stu_num | cour_num | degree | +---------+----------+--------+ | 11422 | 3-105 | 92 | | 11425 | 2-271 | 89 | | 11423 | 1-245 | 84 | | 11426 | 2-271 | 82 | | 11427 | 1-245 | 78 | | 11423 | 2-271 | 75 | | 11424 | 4-321 | 75 | | 11426 | 1-245 | 61 | +---------+----------+--------+
以（order by）成绩升序（asc）查询：
mysql> select * from score order by degree asc; +---------+----------+--------+ | stu_num | cour_num | degree | +---------+----------+--------+ | 11426 | 1-245 | 61 | | 11423 | 2-271 | 75 | | 11424 | 4-321 | 75 | | 11427 | 1-245 | 78 | | 11426 | 2-271 | 82 | | 11423 | 1-245 | 84 | | 11425 | 2-271 | 89 | | 11422 | 3-105 | 92 | +---------+----------+--------+
8.以cour_num升序，degree降序查询score表中的所有记录：
mysql> select * from score; +---------+----------+--------+ | stu_num | cour_num | degree | +---------+----------+--------+ | 11422 | 3-105 | 92 | | 11423 | 1-245 | 84 | | 11423 | 2-271 | 75 | | 11424 | 4-321 | 75 | | 11425 | 2-271 | 89 | | 11426 | 1-245 | 61 | | 11426 | 2-271 | 82 | | 11427 | 1-245 | 78 | +---------+----------+--------+
mysql> select * from score cour_num asc,degree desc; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘asc,degree desc‘ at line 1 mysql> select * from score order by cour_num asc,degree desc; +---------+----------+--------+ | stu_num | cour_num | degree | +---------+----------+--------+ | 11423 | 1-245 | 84 | | 11427 | 1-245 | 78 | | 11426 | 1-245 | 61 | | 11425 | 2-271 | 89 | | 11426 | 2-271 | 82 | | 11423 | 2-271 | 75 | | 11422 | 3-105 | 92 | | 11424 | 4-321 | 75 | +---------+----------+--------+
9.查询‘114‘班的学生人数：
统计：count
mysql> select count(*) from student where class=‘114‘; +----------+ | count(*) | +----------+ | 6 | +----------+
10.查询score表中最高分的学生学号和课程号：
mysql> select stu_num,cour_num from score where degree=(select max(degree) from score); +---------+----------+ | stu_num | cour_num | +---------+----------+ | 11422 | 3-105 | +---------+----------+
其中：
mysql> select * from score; +---------+----------+--------+ | stu_num | cour_num | degree | +---------+----------+--------+ | 11422 | 3-105 | 92 | | 11423 | 1-245 | 84 | | 11423 | 2-271 | 75 | | 11424 | 4-321 | 75 | | 11425 | 2-271 | 89 | | 11426 | 1-245 | 61 | | 11426 | 2-271 | 82 | | 11427 | 1-245 | 78 | +---------+----------+--------+
原文：https://www.cnblogs.com/direwolf22/p/11891126.html