前言
本文包括基本的 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 |
- +--------------+--------------+--------------+
来源: https://juejin.im/entry/5ac87d0f6fb9a028d9376776