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 | +----------+
count(*):对表中所有的数目进行计数，不管表中包含的是空值还是非空值。
count(column):指定列时，对有值的行计数，忽略NULL值。
mysql> select * from pet; +----------+-------------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +----------+-------------+---------+------+------------+------------+ | Puffball | Diane | hamster | f | 2000-09-22 | NULL | | Cuihua | ZhouXingChi | DOG | M | 1999-09-21 | NULL | | DNN | Huimin | Cat | f | 2018-07-07 | NULL | | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | | Fang | Benny | dog | m | 1909-08-27 | NULL | | Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 | | Chirpy | Gwen | bird | f | 1989-09-01 | NULL | | Slim | Benny | snake | m | 2006-04-21 | NULL | | Puffball | Diane | hamster | f | 2009-10-12 | NULL | +----------+-------------+---------+------+------------+------------+
mysql> select count(name) from pet; +-------------+ | count(name) | +-------------+ | 10 | +-------------+
mysql> select count(death) from pet; +--------------+ | count(death) | +--------------+ | 1 | +--------------+
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 | +---------+----------+--------+
首先先找到最高分：
mysql> select max(degree) from score; +-------------+ | max(degree) | +-------------+ | 92 | +-------------+
然后找最高分的学号和课程号：
mysql> select stu_num,cour_num from score where degree=(select max(degree) from score); +---------+----------+ | stu_num | cour_num | +---------+----------+ | 11422 | 3-105 | +---------+----------+
排序的做法：
按degree排序，默认为升序：
mysql> select stu_num,cour_num,degree from score order by degree; +---------+----------+--------+ | 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 | +---------+----------+--------+
mysql> select stu_num,cour_num,degree from score order by degree limit 0,1; +---------+----------+--------+ | stu_num | cour_num | degree | +---------+----------+--------+ | 11426 | 1-245 | 61 | +---------+----------+--------+
可见，上述步骤是取出了第一行。（从0开始查入一条）
mysql> select stu_num,cour_num,degree from score order by degree desc limit 0,1; +---------+----------+--------+ | stu_num | cour_num | degree | +---------+----------+--------+ | 11422 | 3-105 | 92 | +---------+----------+--------+
降序排列，再取出第一行。就是所求的最高分所对应的序号和课程号了。
但是用这种排序的方法，可能取得最高分的同时有多个人那，所以也是有缺陷的。
mysql> select stu_num,cour_num,degree from score order by degree desc limit 0,2; +---------+----------+--------+ | stu_num | cour_num | degree | +---------+----------+--------+ | 11422 | 3-105 | 92 | | 11425 | 2-271 | 89 | +---------+----------+--------+
mysql> select stu_num,cour_num,degree from score order by degree desc limit 1,3; +---------+----------+--------+ | stu_num | cour_num | degree | +---------+----------+--------+ | 11425 | 2-271 | 89 | | 11423 | 1-245 | 84 | | 11426 | 2-271 | 82 | +---------+----------+--------+
limit中第一个数是从哪个位置开始查，第二个数字是查多少条。