MySQL之查询数据的基本与复杂操作本文包括基本的SELECT查询及其相关子句的使用。复杂的SELECT查询，如子查询、

前言

本文包括基本的SELECT查询及其相关子句的使用。复杂的SELECT查询，如子查询、连接查询等。简单查询操作、连接查询操作、嵌套查询、数据的集合查询和统计查询。

创建XSCJ数据库

create database xscj;

创建表

在XSCJ数据库中创建学生情况表XSQK，课程表KC，学生成绩表XS_KC。

学生情况表XSQK的结构

列名	数据类型	长度	是否允许为空值	默认值	说明
学号	Char	6	N	-	主键
姓名	Char	8	N	-	-
性别	Char	2	N	-	男，女
出生日期	datetime	4	N	-	-
专业名	Char	10	N	-	-
所在系	Char	10	N	-	-
联系电话（宿舍电话）	char	11	Y	-	-

create table XSQK
    -> (
    -> 学号 char(6) not null primary key,
    -> 姓名 char(8) not null,
    -> 性别 char(2) not null check(性别 in (男,女)),
    -> 出生日期 datetime(4) not null,
    -> 专业名 char(10) not null,
    -> 所在系 char(10) not null,
    -> 联系电话 char(11)
    -> );

课程表KC的结构

列名	数据类型	长度	是否允许为空值	默认值	说明
课程号	Char	3	N	-	主键
课程名	Char	20	N	-	-
教师	Char	10	Y	-	-
开课学期	Tinyint	1	Y	-	只能1-6
学时	Tinyint	1	Y	60	-
学分	Tinyint	1	N	-	-
先修课程	Char	20	Y	-	-

create table KC
    -> (
    -> 课程号 char(3) not null primary key,
    -> 课程名 char(20) not null,
    -> 教师 char(10),
    -> 开学学期 Tinyint(1),
    -> 学时 tinyint(1)default 60,
    -> 学分 tinyint(1) not null,
    -> 选修课程 char(20),
    -> check (开学学期>=1 and 开学学期<=6)
    -> );

成绩表XS_KC的结构

列名	数据类型	长度	是否允许为空值	默认值	说明
学号	Char	6	N	-	主键、外键
课程号	Char	3	N	-	主键、外键
成绩	Tinyint	1	Y	-	0-100之间

create table XS_KC
    -> (
    -> 学号 char(6) not null,
    -> 课程号 char(3) not null,
    -> 成绩 tinyint(1) check(成绩>=0 and 成绩<=100),
    -> PRIMARY KEY (学号,课程号),
    -> foreign key(学号) references XSQK(学号),
    -> foreign key(课程号) references KC(课程号)
    -> );

输入数据

在XSQK、KC、XS_KC表中输入以下数据。

学生情况表XSQK的记录

学号	姓名	性别	出生日期	专业	所在系	联系电话
20101	杨颖	女	1980/7/20	计算机应用	计算机	88297147
20102	方露露	女	1981/1/15	计算机应用	计算机	88297147
20103	俞奇军	男	1980/2/20	信息管理	计算机	88297151
20104	胡国强	男	1980/11/7	信息管理	计算机	88297151
20105	薛冰	男	1980/7/29	水利工程	水利系	88297152
20201	秦盈飞	女	1981/3/10	电子商务	经济系	88297161
20202	董含静	女	1980/9/25	电子商务	经济系	88297062
20203	陈伟	男	1980/8/7	电子商务	经济系	88297171
20204	陈新江	男	1980/7/20	房建	水利系	88297171

insert into XSQK
-> values (20101,'杨颖','女','1980/7/20','计算机应用','计算机',88297147),
-> (20102,'方露露','女','1981/1/15','计算机应用','计算机',88297147),
    -> (20103,'余琪军','男','1980/2/20','信息管理','计算机',88297151),
    -> (20104,'胡国强','男','1980/11/7','信息管理','计算机',88297151),
    -> (20105,'薛冰','男','1980/7/29','水利工程','水利系',88297152),
    -> (20201,'秦盈飞','女','1981/3/10','电子商务','经济系',88297161),
    -> (20202,'董含静','女','1980/9/25','电子商务','经济系',88297062),
    -> (20203,'陈伟','男','1980/8/7','电子商务','经济系',88297171),
    -> (20204,'陈新江','男','1980/7/20','房建','水利系',88297171);

课程表KC的记录

课程号	课程名	教师	开课学期	学时	学分	先修课程
101	计算机原理	陈红	2	45	3	电路
102	计算方法	王颐	3	45	3	-
103	操作系统	徐格	2	60	4	-
104	数据库原理及应用	应对刚	3	75	5	离散数学
105	网络基础	吴江江	4	45	3	计算机原理
106	高等数学	孙中文	1	90	6	-
107	英语	陈刚	1	90	6	-
108	VB程序设计	赵红韦	3	70	5	-

insert into KC
    -> values(101,'计算机原理','陈红',2,45,3,'电路'),
    ->(102,'计算方法','王颐',3,45,3,''),
    -> (103,'操作系统','徐格',2,60,4,''),
    -> (104,'数据库原理及应用','应对刚',3,75,5,'离散数学'),
    -> (105,'网络基础','吴江江',4,45,3,'计算机原理'),
    -> (106,'高等数学','孙中文',1,90,6,''),
    -> (107,'英语','陈刚',1,90,6,''),
    -> (108,'VB程序设计','赵红伟',3,70,5,'');

成绩表XS_KC的记录

学号	课程号	成绩
20101	101	85
20101	102	87
20101	107	88
20102	101	58
20102	102	63
20104	107	76
20202	103	55
20202	108	80
20203	103	57
20204	103	71

insert into XS_KC
    -> values(20101,101,85),
    -> (20101,102,87),
    -> (20101,107,88),
    -> (20102,101,58),
    -> (20102,102,63),
    -> (20104,107,76),
    -> (20202,103,55),
    -> (20202,108,80),
    -> (20203,103,57),
    -> (20204,103,71);

简单查询操作

在XSCJ数据库中实现其数据查询操作。

a) 求计算机系学生的学号和姓名

select 学号,姓名 from XSQK;

b) 求选修了课程的学生学号

select 学号 from XS_KC where 课程号 in(101,104,105);

+--------+
| 学号   |
+--------+
| 20101  |
| 20102  |
+--------+

c) 求选修课程号为‘101’的学号和成绩，并要求对查询结果按成绩的降序排列，如果成绩相同按学号的升序排列。

 select 学号,成绩 from XS_KC where 课程号 in(101) order by 成绩 desc,学号;

+--------+--------+
| 学号   | 成绩   |
+--------+--------+
| 20101  |     85 |
| 20102  |     58 |
+--------+--------+

d) 求选修课程号为‘101’且成绩在80~90之间的学生学号和成绩。

select 学号,成绩 from XS_KC where 课程号 in(101) and 成绩 between 80 and 90;

+--------+--------+
| 学号   | 成绩   |
+--------+--------+
| 20101  |     85 |
+--------+--------+

e) 求水利系或计算机系姓陈的学生的信息。

 select 姓名 from XSQK where 所在系 in('计算机','水利系') and 姓名 like '陈%';

+-----------+
| 姓名      |
+-----------+
| 陈新江    |
+-----------+

f) 求缺少了成绩的学生的学号和课程号

select XSQK.学号,KC.课程号,XS_KC.成绩 from XSQK,KC,XS_KC where 成绩 is null;

Empty set (0.00 sec)

这里返回了为空结果。可以将上面的指令后面改为is not null发现成绩都已经填满了。这里我也不是懂为什么创建的时候他会自动填满。

g) 在KC表中查询学分低于3的课程信息，并按课程号升序排列。

select 课程名 from KC where 学分<3;

Empty set (0.00 sec)

h) 查询全体学生的姓名, 出生年份,和所在系。

 select 姓名,出生日期,所在系 from XSQK;

+-----------+--------------------------+-----------+
| 姓名      | 出身日期                 | 所在系    |
+-----------+--------------------------+-----------+
| 杨颖      | 1980-07-20 00:00:00.0000 | 计算机    |
| 方露露    | 1981-01-15 00:00:00.0000 | 计算机    |
| 俞奇军    | 1980-02-20 00:00:00.0000 | 计算机    |
| 胡国强    | 1980-11-07 00:00:00.0000 | 计算机    |
| 薛冰      | 1980-07-29 00:00:00.0000 | 水利系    |
| 秦盈飞    | 1981-03-10 00:00:00.0000 | 经济系    |
| 董含静    | 1980-09-25 00:00:00.0000 | 经济系    |
| 陈伟      | 1980-08-07 00:00:00.0000 | 经济系    |
| 陈新江    | 1980-07-20 00:00:00.0000 | 水利系    |
+-----------+--------------------------+-----------+

i) 查询计算机,经济系的所有学生的姓名和性别。

select 姓名,性别 from XSQK where 所在系 in('计算机','经济系');

+-----------+--------+
| 姓名      | 性别   |
+-----------+--------+
| 杨颖      | 女     |
| 方露露    | 女     |
| 俞奇军    | 男     |
| 胡国强    | 男     |
| 秦盈飞    | 女     |
| 董含静    | 女     |
| 陈伟      | 男     |
+-----------+--------+

j) 查询没有选修课的课程的课程号和课程名。

select 课程号,课程名 from KC where 选修课程='';

+-----------+----------------+
| 课程号    | 课程名         |
+-----------+----------------+
| 102       | 计算方法       |
| 103       | 操作系统       |
| 106       | 高等数学       |
| 107       | 英语           |
| 108       | VB程序设计     |
+-----------+----------------+

连接查询操作

a) 求107号课程不及格的学生信息(学号,姓名,联系电话)。

select xsqk.学号, xsqk.姓名,xsqk.联系电话 from xs_kc left join xsqk on xs_kc.学号=xsqk.学号 where xs_kc.成绩<60;

+--------+-----------+--------------+
| 学号   | 姓名      | 联系电话     |
+--------+-----------+--------------+
| 20102  | 方露露    | 88297147     |
| 20202  | 董含静    | 88297062     |
| 20203  | 陈伟      | 88297171     |
+--------+-----------+--------------+

b) 求学生的学号、姓名、选修的课程号及成绩

 select xsqk.学号,xsqk.姓名,kc.课程号,xs_kc.成绩 from xs_kc left join kc on xs_kc.课程号=kc.课程号 left join xsqkon xsqk.学号=xs_kc.学号;

+--------+-----------+-----------+--------+
| 学号   | 姓名      | 课程号    | 成绩   |
+--------+-----------+-----------+--------+
| 20101  | 杨颖      | 101       |     85 |
| 20101  | 杨颖      | 102       |     87 |
| 20101  | 杨颖      | 107       |     88 |
| 20102  | 方露露    | 101       |     58 |
| 20102  | 方露露    | 102       |     63 |
| 20104  | 胡国强    | 107       |     76 |
| 20202  | 董含静    | 103       |     55 |
| 20202  | 董含静    | 108       |     80 |
| 20203  | 陈伟      | 103       |     57 |
| 20204  | 陈新江    | 103       |     71 |

c) 求选修课程号为‘101’且成绩在90以上的学生学号、姓名和成绩

select xsqk.学号,xsqk.姓名,xs_kc.成绩 from xs_kc left join xsqk on xsqk.学号=xs_kc.学号 where 课程号=101 and 成绩>90;

Empty set (0.00 sec)

d) 用连接查询在XSQK表中查询住在同一寝室的学生，即其联系电话相同

select test.姓名,test.联系电话 from xsqk as test left join xsqk on test.联系电话 = xsqk.联系电话;


+-----------+--------------+
| 姓名      | 联系电话     |
+-----------+--------------+
| 杨颖      | 88297147     |
| 方露露    | 88297147     |
| 杨颖      | 88297147     |
| 方露露    | 88297147     |
| 俞奇军    | 88297151     |
| 胡国强    | 88297151     |
| 俞奇军    | 88297151     |
| 胡国强    | 88297151     |
| 薛冰      | 88297152     |
| 秦盈飞    | 88297161     |
| 董含静    | 88297062     |
| 陈伟      | 88297171     |
| 陈新江    | 88297171     |
| 陈伟      | 88297171     |
| 陈新江    | 88297171     |
+-----------+--------------+

嵌套查询

a) 查询与’杨颖’在同一个系学习的学生的信息

 select * from xsqk where 所在系=(select 所在系 from xsqk where 姓名='杨颖');

+--------+-----------+--------+--------------------------+-----------------+-----------+--------------+
| 学号   | 姓名      | 性别   | 出身日期                 | 专业名          | 所在系    | 联系电话     |
+--------+-----------+--------+--------------------------+-----------------+-----------+--------------+
| 20101  | 杨颖      | 女     | 1980-07-20 00:00:00.0000 | 计算机应用      | 计算机    | 88297147     |
| 20102  | 方露露    | 女     | 1981-01-15 00:00:00.0000 | 计算机应用      | 计算机    | 88297147     |
| 20103  | 俞奇军    | 男     | 1980-02-20 00:00:00.0000 | 信息管理        | 计算机    | 88297151     |
| 20104  | 胡国强    | 男     | 1980-11-07 00:00:00.0000 | 信息管理        | 计算机    | 88297151     |
+--------+-----------+--------+--------------------------+-----------------+-----------+--------------+

b) 查询选修了课程名为’计算机原理’的学生的学号和姓名

 select xsqk.姓名,xsqk.学号 from xsqk where xsqk.学号 = any(select xs_kc.学号 from xs_kc where xs_kc.课程号 = (select kc.课程号 from kc where kc.课程名='计算机原理'));


+-----------+--------+
| 姓名      | 学号   |
+-----------+--------+
| 杨颖      | 20101  |
| 方露露    | 20102  |
+-----------+--------+

c) 查询选修了课程’101’和课程’102’的学生的学号

 select 学号 from xs_kc where 课程号 in (101,102);

+--------+
| 学号   |
+--------+
| 20101  |
| 20102  |
| 20101  |
| 20102  |
+--------+

d) 查询所有选修了101号课程的学生姓名

 select xsqk.姓名 from xsqk where xsqk.学号 in (select xs_kc.学号 from xs_kc where xs_kc.课程号=101);

+-----------+
| 姓名      |
+-----------+
| 杨颖      |
| 方露露    |
+-----------+

e) 查询没有选修101号课程的学生姓名

select xsqk.姓名 from xsqk where xsqk.学号 not  in (select xs_kc.学号 from xs_kc where xs_kc.课程号=101);

+-----------+
| 姓名      |
+-----------+
| 俞奇军    |
| 胡国强    |
| 薛冰      |
| 秦盈飞    |
| 董含静    |
| 陈伟      |
| 陈新江    |
+-----------+

f) 查询选修了全部课程的学生姓名

 select xsqk.姓名 from xsqk where xsqk.学号 = all (select xs_kc.学号 from xs_kc);
Empty set (0.00 sec)

g) 求没有人选修的课程号和课程名

select xsqk.姓名 from xsqk where xsqk.学号 not in (select xs_kc.学号 from xs_kc);
+-----------+
| 姓名      |
+-----------+
| 俞奇军    |
| 薛冰      |
| 秦盈飞    |
+-----------+

h) 查询每个学生的课程成绩最高的成绩信息(sno,cno,grade)

select xs_kc.学号,max(xs_kc.成绩) from xs_kc group by xs_kc.学号;

+--------+-------------------+
| 学号   | max(xs_kc.成绩)   |
+--------+-------------------+
| 20101  |                88 |
| 20102  |                63 |
| 20104  |                76 |
| 20202  |                80 |
| 20203  |                57 |
| 20204  |                71 |
+--------+-------------------+

数据的集合查询和统计查询

a) 在XS_KC表中按学号分组汇总学生的平均分，并按平均分的降序排列。

 select xs_kc.学号,avg(xs_kc.成绩) from xs_kc group by xs_kc.学号 order by avg(xs_kc.成绩) desc;
+--------+-------------------+
| 学号   | avg(xs_kc.成绩)   |
+--------+-------------------+
| 20101  |           86.6667 |
| 20104  |           76.0000 |
| 20204  |           71.0000 |
| 20202  |           67.5000 |
| 20102  |           60.5000 |
| 20203  |           57.0000 |
+--------+-------------------+

b) 在XS_KC表中查询选修了3门以上课程的学生学号。

 select 学号 from xs_kc group by 学号 having count(学号)>=3;
+--------+
| 学号   |
+--------+
| 20101  |
+--------+

c) 按学号对不及格的成绩记录进行明细汇总。

select * from xs_kc where 成绩<60 ;
+--------+-----------+--------+
| 学号   | 课程号    | 成绩   |
+--------+-----------+--------+
| 20102  | 101       |     58 |
| 20202  | 103       |     55 |
| 20203  | 103       |     57 |
+--------+-----------+--------+

d) 查询所有课程的总学分数和平均学分数,以及最高学分和最低学分。

 select sum(学分) as 总学分,avg(学分) as 平均学分,max(学分) as 最高学分,min(学分) as 最低学分 from kc;
+-----------+--------------+--------------+--------------+
| 总学分    | 平均学分     | 最高学分     | 最低学分     |
+-----------+--------------+--------------+--------------+
|        35 |       4.3750 |            6 |            3 |
+-----------+--------------+--------------+--------------+

e) 计算101号课程的学生的平均成绩, 最高分和最低分。

 select avg(成绩) as 平均成绩,max(成绩) as 最高成绩,min(成绩) as 最低成绩 from xs_kc where 课程号=101;
+--------------+--------------+--------------+
| 平均成绩     | 最高成绩     | 最低成绩     |
+--------------+--------------+--------------+
|      71.5000 |           85 |           58 |
+--------------+--------------+--------------+