MySql基本使用2（select，between，in，where，like，count，distinct，group by，order by，多表查询）

最新推荐文章于 2025-09-15 00:39:38 发布

原创最新推荐文章于 2025-09-15 00:39:38 发布 · 408 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#Mysql

Mysql 专栏收录该内容

1 篇文章

订阅专栏

本文详细介绍了MySQL中的基本查询操作，包括SELECT语句的使用，如SELECT...WHERE用于过滤记录，BETWEEN用于指定范围查询，IN关键字用于指定多个值的查询，LIKE用于模糊匹配，COUNT函数计算记录数，DISTINCT去除重复值，GROUP BY进行数据分组，ORDER BY对结果排序，以及如何进行多表查询。这些知识点是MySQL数据库操作的基础，对于数据库管理和数据分析至关重要。

---------------part1.【student、score、course】表结构及数据示例-----------
mysql> select * from student;
+-----+--------+------+---------------------+-------+
| sno | sname  | ssex | sbirthday           | class |
+-----+--------+------+---------------------+-------+
| 101 | 曾华   | 男   | 1977-09-01 00:00:00 | 95033 |
| 102 | 王丽   | 女   | 1976-01-23 00:00:00 | 95033 |
| 103 | 匡明   | 男   | 1975-10-02 00:00:00 | 95031 |
| 104 | 李军   | 男   | 1976-02-20 00:00:00 | 95033 |
| 105 | 王芳   | 女   | 1975-02-10 00:00:00 | 95031 |
| 106 | 陆军   | 女   | 1974-06-03 00:00:00 | 95031 |
| 107 | 王尼玛 | 男   | 1976-02-20 00:00:00 | 95033 |
| 108 | 张全蛋 | 男   | 1975-02-10 00:00:00 | 95031 |
| 109 | 赵铁柱 | 男   | 1974-06-03 00:00:00 | 95031 |
+-----+--------+------+---------------------+-------+


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


mysql> select * from course;
+-------+------------+-----+
| cno   | cname      | tno |
+-------+------------+-----+
| 3-105 | 计算机导论 | 825 |
| 3-245 | 操作系统   | 804 |
| 6-166 | 数字电路   | 856 |
| 9-888 | 高等数学   | 831 |
+-------+------------+-----+
4 rows in set (0.00 sec)

---------------part2.基本查询示例---------------
//选择degree为86，85，88的记录
mysql> select * from score where degree in (86,85,88)
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-245 |     86 |
| 103 | 6-166 |     85 |
| 105 | 3-105 |     88 |
+-----+-------+--------+
3 rows in set (0.00 sec)


//选择记录在60-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 |
+-----+-------+--------+
4 rows in set (0.00 sec)


降序排列
mysql> select * from student order by class desc;
+-----+--------+------+---------------------+-------+
| sno | sname  | ssex | sbirthday           | class |
+-----+--------+------+---------------------+-------+
| 101 | 曾华   | 男   | 1977-09-01 00:00:00 | 95033 |
| 102 | 王丽   | 女   | 1976-01-23 00:00:00 | 95033 |
| 104 | 李军   | 男   | 1976-02-20 00:00:00 | 95033 |
| 107 | 王尼玛 | 男   | 1976-02-20 00:00:00 | 95033 |
| 103 | 匡明   | 男   | 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 |
+-----+--------+------+---------------------+-------+
9 rows in set (0.00 sec)



//升序排列
mysql> select * from student order by class;
+-----+--------+------+---------------------+-------+
| sno | sname  | ssex | sbirthday           | class |
+-----+--------+------+---------------------+-------+
| 103 | 匡明   | 男   | 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 |
| 101 | 曾华   | 男   | 1977-09-01 00:00:00 | 95033 |
| 102 | 王丽   | 女   | 1976-01-23 00:00:00 | 95033 |
| 104 | 李军   | 男   | 1976-02-20 00:00:00 | 95033 |
| 107 | 王尼玛 | 男   | 1976-02-20 00:00:00 | 95033 |
+-----+--------+------+---------------------+-------+
9 rows in set (0.00 sec)


//以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 rows in set (0.00 sec)



//查询class=95031的记录条数
mysql> select count(*) from student where class='95031';
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)



//查询score表中的最高分的学生学号和课程号（用子查询或者排序，排序可能存在多条最大值记录，此时有bug）
mysql> select sno,cno from score where degree=(select max(degree) from score);
+-----+-------+
| sno | cno   |
+-----+-------+
| 103 | 3-105 |
+-----+-------+
1 row in set (0.00 sec)


//limit 开始位置，查多少条
mysql> select * from score order by degree desc limit 0,1;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |     92 |
+-----+-------+--------+
1 row in set (0.00 sec)



//查询每门课的平均成绩
mysql> select score.cno,cname,avg(degree) from score,course where score.cno=course.cno group by cno;
+-------+------------+-------------+
| cno   | cname      | avg(degree) |
+-------+------------+-------------+
| 3-105 | 计算机导论 |     85.3333 |
| 3-245 | 操作系统   |     76.3333 |
| 6-166 | 数字电路   |     81.6667 |
+-------+------------+-------------+
3 rows in set (0.00 sec)



//查询score表中至少有两名学生选秀，并且以3开头的课程的平均分
mysql> select cno,avg(degree)  from score group by cno having count(cno)>=2 and cno like '3%';
+-------+-------------+
| cno   | avg(degree) |
+-------+-------------+
| 3-105 |     85.3333 |
| 3-245 |     76.3333 |
+-------+-------------+
2 rows in set (0.00 sec)



---------------part3.多表查询-----------------------
//根据共同的属性进行合并。

//查询所有学生的sname，cno，(在student表中)和degree（在score表中）
mysql> select sname,cno,degree from student,score where student.sno=score.sno;
+--------+-------+--------+
| sname  | cno   | degree |
+--------+-------+--------+
| 匡明   | 3-105 |     92 |
| 匡明   | 3-245 |     86 |
| 匡明   | 6-166 |     85 |
| 王芳   | 3-105 |     88 |
| 王芳   | 3-245 |     75 |
| 王芳   | 6-166 |     79 |
| 赵铁柱 | 3-105 |     76 |
| 赵铁柱 | 3-245 |     68 |
| 赵铁柱 | 6-166 |     81 |
+--------+-------+--------+
9 rows in set (0.00 sec)



//查询所有学生的sno，cname，degree
mysql> select cname,sno,degree from score,course where score.cno=course.cno;
+------------+-----+--------+
| cname      | sno | degree |
+------------+-----+--------+
| 计算机导论 | 103 |     92 |
| 计算机导论 | 105 |     88 |
| 计算机导论 | 109 |     76 |
| 操作系统   | 103 |     86 |
| 操作系统   | 105 |     75 |
| 操作系统   | 109 |     68 |
| 数字电路   | 103 |     85 |
| 数字电路   | 105 |     79 |
| 数字电路   | 109 |     81 |
+------------+-----+--------+
9 rows in set (0.00 sec)




//查询所有学生的sname，cname，degree列，（三列分别在三张表中）
mysql> select sname,cname,degree from student,course,score where student.sno=score.sno and course.cno=score.cno;
+--------+------------+--------+
| sname  | cname      | degree |
+--------+------------+--------+
| 匡明   | 计算机导论 |     92 |
| 匡明   | 操作系统   |     86 |
| 匡明   | 数字电路   |     85 |
| 王芳   | 计算机导论 |     88 |
| 王芳   | 操作系统   |     75 |
| 王芳   | 数字电路   |     79 |
| 赵铁柱 | 计算机导论 |     76 |
| 赵铁柱 | 操作系统   |     68 |
| 赵铁柱 | 数字电路   |     81 |
+--------+------------+--------+
9 rows in set (0.00 sec)