对XSCJ数据库进行数据查询 -- 学生成绩管理系统

目录

一、数据准备

二、数据示意图

三、题目

---

一、数据准备

使用Navicat 的快捷查询

 

 

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
CREATE DATABASE `xscj` DEFAULT CHARACTER SET gb2312 COLLATE gb2312_chinese_ci;
USE `xscj`;

CREATE TABLE `kc` (
  `课程号` char(3) NOT NULL,
  `课程名` char(16) NOT NULL,
  `开课学期` tinyint(1) NOT NULL default '1' COMMENT '只能为1-8',
  `学时` tinyint(1) NOT NULL,
  `学分` tinyint(1) default NULL,
  PRIMARY KEY  (`课程号`)
) ENGINE=MyISAM DEFAULT CHARSET=gb2312;

INSERT INTO `kc` VALUES ('101', '计算机基础', 1, 80, 5);
INSERT INTO `kc` VALUES ('102', '程序设计与语言', 2, 68, 4);
INSERT INTO `kc` VALUES ('206', '离散数学', 4, 68, 4);
INSERT INTO `kc` VALUES ('208', '数据结构', 5, 68, 4);
INSERT INTO `kc` VALUES ('209', '操作系统', 6, 68, 4);
INSERT INTO `kc` VALUES ('210', '计算机原理', 5, 85, 5);
INSERT INTO `kc` VALUES ('212', '数据库原理', 7, 68, 4);
INSERT INTO `kc` VALUES ('301', '计算机网络', 7, 51, 3);
INSERT INTO `kc` VALUES ('302', '软件工程', 7, 51, 3);

CREATE TABLE `xs` (
  `学号` char(6) NOT NULL,
  `姓名` char(8) NOT NULL,
  `专业名` char(10) default NULL,
  `性别` tinyint(1) NOT NULL default '1' COMMENT '1为男 0为女',
  `出生时间` date NOT NULL,
  `总学分` tinyint(1) default NULL,
  `照片` blob,
  `备注` text,
  PRIMARY KEY  (`学号`)
) ENGINE=MyISAM DEFAULT CHARSET=gb2312;

INSERT INTO `xs` VALUES ('081101', '王林', '计算机', 1, '1990-02-10', 50, NULL, NULL);
INSERT INTO `xs` VALUES ('081102', '程明', '计算机', 1, '1991-02-01', 50, NULL, NULL);
INSERT INTO `xs` VALUES ('081103', '王燕', '计算机', 0, '1989-10-06', 50, NULL, NULL);
INSERT INTO `xs` VALUES ('081104', '韦严平', '计算机', 1, '1990-08-26', 50, NULL, NULL);
INSERT INTO `xs` VALUES ('081106', '李方方', '计算机', 1, '1990-11-20', 50, NULL, NULL);
INSERT INTO `xs` VALUES ('081107', '李明', '计算机', 1, '1990-05-01', 54, NULL, '提前修完《数据结构》，并获学分');
INSERT INTO `xs` VALUES ('081108', '林一帆', '计算机', 1, '1989-08-05', 52, NULL, '已提前修完一门课');
INSERT INTO `xs` VALUES ('081109', '张强民', '计算机', 1, '1989-08-11', 50, NULL, NULL);
INSERT INTO `xs` VALUES ('081110', '张蔚', '计算机', 0, '1991-07-22', 50, NULL, '三好生');
INSERT INTO `xs` VALUES ('081111', '赵琳', '计算机', 0, '1990-03-18', 50, NULL, NULL);
INSERT INTO `xs` VALUES ('081113', '严红', '计算机', 0, '1989-08-11', 48, NULL, '有一门功课不及格，待补考');
INSERT INTO `xs` VALUES ('081201', '王敏', '通信工程', 1, '1989-06-10', 42, NULL, NULL);
INSERT INTO `xs` VALUES ('081202', '王林', '通信工程', 1, '1989-01-29', 40, NULL, '有一门课不及格，待补考');
INSERT INTO `xs` VALUES ('081203', '王玉民', '通信工程', 1, '1990-03-26', 42, NULL, NULL);
INSERT INTO `xs` VALUES ('081204', '马琳琳', '通信工程', 0, '1989-02-10', 42, NULL, NULL);
INSERT INTO `xs` VALUES ('081206', '李计', '通信工程', 1, '1989-09-20', 42, NULL, NULL);
INSERT INTO `xs` VALUES ('081210', '李红庆', '通信工程', 1, '1989-05-01', 44, NULL, '已提前修完一门课，并获得学分');
INSERT INTO `xs` VALUES ('081216', '孙祥欣', '通信工程', 1, '1989-03-09', 42, NULL, NULL);
INSERT INTO `xs` VALUES ('081218', '孙研', '通信工程', 1, '1990-10-09', 42, NULL, NULL);
INSERT INTO `xs` VALUES ('081220', '吴薇华', '通信工程', 0, '1990-03-18', 42, NULL, NULL);
INSERT INTO `xs` VALUES ('081221', '刘燕敏', '通信工程', 0, '1989-11-12', 42, NULL, NULL);
INSERT INTO `xs` VALUES ('081241', '罗林琳', '通信工程', 0, '1990-01-30', 50, NULL, '转专业学习');

CREATE TABLE `xs_kc` (
  `学号` char(6) NOT NULL,
  `课程号` char(3) NOT NULL,
  `成绩` tinyint(1) default NULL,
  `学分` tinyint(1) default NULL,
  PRIMARY KEY  USING BTREE (`学号`,`课程号`)
) ENGINE=MyISAM DEFAULT CHARSET=gb2312;

INSERT INTO `xs_kc` VALUES ('081101', '101', 80, 5);
INSERT INTO `xs_kc` VALUES ('081101', '102', 78, 4);
INSERT INTO `xs_kc` VALUES ('081101', '206', 76, 4);
INSERT INTO `xs_kc` VALUES ('081102', '102', 78, 4);
INSERT INTO `xs_kc` VALUES ('081102', '206', 78, 4);
INSERT INTO `xs_kc` VALUES ('081103', '101', 62, 5);
INSERT INTO `xs_kc` VALUES ('081103', '102', 70, 4);
INSERT INTO `xs_kc` VALUES ('081103', '206', 81, 4);
INSERT INTO `xs_kc` VALUES ('081104', '101', 90, 5);
INSERT INTO `xs_kc` VALUES ('081104', '102', 84, 4);
INSERT INTO `xs_kc` VALUES ('081104', '206', 65, 4);
INSERT INTO `xs_kc` VALUES ('081106', '101', 65, 5);
INSERT INTO `xs_kc` VALUES ('081106', '102', 71, 4);
INSERT INTO `xs_kc` VALUES ('081106', '206', 81, 4);
INSERT INTO `xs_kc` VALUES ('081107', '101', 78, 5);
INSERT INTO `xs_kc` VALUES ('081107', '102', 80, 4);
INSERT INTO `xs_kc` VALUES ('081107', '206', 68, 4);
INSERT INTO `xs_kc` VALUES ('081108', '101', 85, 5);
INSERT INTO `xs_kc` VALUES ('081108', '102', 64, 4);
INSERT INTO `xs_kc` VALUES ('081108', '206', 87, 4);
INSERT INTO `xs_kc` VALUES ('081109', '101', 66, 5);
INSERT INTO `xs_kc` VALUES ('081109', '102', 83, 4);
INSERT INTO `xs_kc` VALUES ('081109', '206', 70, 4);
INSERT INTO `xs_kc` VALUES ('081110', '101', 95, 5);
INSERT INTO `xs_kc` VALUES ('081110', '102', 90, 4);
INSERT INTO `xs_kc` VALUES ('081110', '206', 89, 4);
INSERT INTO `xs_kc` VALUES ('081111', '101', 91, 5);
INSERT INTO `xs_kc` VALUES ('081111', '102', 70, 4);
INSERT INTO `xs_kc` VALUES ('081111', '206', 76, 4);
INSERT INTO `xs_kc` VALUES ('081113', '101', 63, 5);
INSERT INTO `xs_kc` VALUES ('081113', '102', 79, 4);
INSERT INTO `xs_kc` VALUES ('081113', '206', 60, 4);
INSERT INTO `xs_kc` VALUES ('081201', '101', 80, 5);
INSERT INTO `xs_kc` VALUES ('081202', '101', 65, 5);
INSERT INTO `xs_kc` VALUES ('081203', '101', 87, 5);
INSERT INTO `xs_kc` VALUES ('081204', '101', 91, 5);
INSERT INTO `xs_kc` VALUES ('081210', '101', 76, 5);
INSERT INTO `xs_kc` VALUES ('081216', '101', 81, 5);
INSERT INTO `xs_kc` VALUES ('081218', '101', 70, 5);
INSERT INTO `xs_kc` VALUES ('081220', '101', 82, 5);
INSERT INTO `xs_kc` VALUES ('081221', '101', 76, 5);
INSERT INTO `xs_kc` VALUES ('081241', '101', 90, 5);

二、数据示意图

 做题时可设为背景，提高速度

三、题目

对XSCJ数据库进行数据查询

1）查询XS表中各个同学的姓名、专业名、和总学分。

select 姓名,专业名,总学分 from XS;

2）查询XS表中计算机系同学的学号、姓名和总学分，结果中各列的标题分别指定为number、name和mark。

select 学号 as number,姓名 as ame,总学分 as mark from XS where 专业名='计算机';

3）查询xs表中计算机系各同学的学号、姓名和总学分，对其总学分按以下规则进行替换：若总学分为空值，替换为“尚未选课”；若总学分小于50，替换为“不及格”；若总学分在50～52之间，替换为“合格”；若总学分大于52，替换为优秀。列标题更改为”等级“。

select 学号,姓名,总学分,case when 总学分 is null then '尚未选课' when 总学分<50 then '不及格' when 总学分>=50 and 总学分<=52 then '合格' when 总学分>52 then '优秀' end as 等级 from XS where 专业名='计算机';

4）按120分计算成绩，显示XS_KC 表中学号为081101的学生课程信息。

select *,姓名,round(成绩*1.2,2) as 120分值的成绩 from XS_KC join xs using(学号) where 学号='081101';

5）对XS表只选择专业名和总学分，消除结果集中的重复行。

select distinct 专业名,总学分 from XS;

6）求学生的总人数。

select count(*) as 总人数 from XS;

7）统计备注不为空的学生数目。

select count(*) as 备注不为空的人数 from XS where 备注 is not null;

8）统计总学分在50分以上的人数。

select count(*) as 总学分在50以上的人数 from XS where 总学分>=50;

9）求选修101课程的学生的最高分和最低分。

select 课程号,max(成绩) as 最高成绩,min(成绩) as 最低成绩 from XS_KC join XS on(XS.学号=XS_KC.学号) where 课程号='101';

10）求学号081101的学生所学课程的总成绩。

select 学号,sum(成绩) as 总成绩 from XS_KC where 学号='081101';

11）求选修101课程的学生的平均成绩。

select avg(成绩) as 选修101课程平均成绩 from XS_KC where 课程号='101';

12）查询XSCJ数据库XS表中学号为081101的学生的情况。

select * from XS where 学号='081101';

13）查询XS表中总学分大于50的学生的情况。

select * from XS where 总学分>=50;

14）查询XS表中备注为空的同学的情况。

select * from XS where 备注 is null;

15）查询XS表中专业为计算机，性别为女（0）的同学的情况。

select * from XS where 专业名='计算机' and 性别='0';

16）查询XS_KC 表中102 和206课程中大于80分的同学的记录。

select * from XS_KC where (课程号='102' or 课程号='206') and 成绩>=80;

17）查询XSCJ 数据库XS 表中姓王的学生学号、姓名及性别。

select 学号,姓名,性别 from XS where 姓名 like '王%';

18）查询XSCJ数据库XS表中学号倒数第二个数字为0的学生学号、姓名及专业名。

select 学号,姓名,专业名 from xs where 学号 like '%0_';

19）查询XS表中名字包含下划线的学生学号和姓名。

select 学号,姓名 from xs where 姓名 like '%#_%'escape '#';

20）查询XS表中不在1989年出生的学生情况。

select * from xs where 出生时间<'1989-01-01' or 出生时间>='1989-12-31';

下列与上列语句等价

select * from xs where 出生时间 not between '1989-01-01' and '1989-12-31';

21）查询xs表中专业名为“计算机” “通信工程” 或 “无线电” 的学生的情况。

select * from xs where 专业名='计算机' or 专业名='通信工程' or 专业名='无线电';

22）查询xscj数据库中总学分尚不定的学生的情况。

select * from xs where 总学分 is null;

23）查找xscj数据库中所有学生选过的课程名和课程号。

select 课程名,课程号 from xs_kc join xs on(XS_KC.学号=XS.学号) join kc using(课程号);

24）查找选修了206课程且成绩在80分以上的学生姓名及成绩。

select 姓名,成绩,课程号 from XS_KC join xs using(学号) where 课程号='206' and 成绩>80;

25)查找选修了“计算机基础” 课程且成绩在80分以上的学生学号、姓名、课程名及成绩。

select 学号,姓名,课程名,成绩 from XS_KC join xs using(学号) join kc using(课程号) where 课程名='计算机基础' and 成绩>=80;

26)查找xscj数据库中课程不同、成绩相同的学生的学号、课程号和成绩。

select 学号,成绩,课程号 from XS_KC where 成绩 in(select 成绩 from XS_KC group by 成绩 having count(*)>1);

27）查找kc表中所有学生选过的课程名。

select distinct 课程名 as 选过的课程名 from kc join xs_kc using(课程号);

28）查找所有学生情况及他们选修的课程号，若学生未选修任何课，也要包括其情况。

select * from xs left outer join XS_KC on(xs.学号=XS_KC.学号);

29)查找被选修了的课程的选修情况和所有开设的课程名。

select * from kc left join xs_KC on(kc.课程号=xs_kc.课程号);

30）查找在xscj数据库中选修了课程号206的课程的学生的姓名、学号。

select 姓名,学号 from xs_kc join xs using(学号) where 课程号='206';

31）查找未选修离散数学的学生的姓名、学号、专业名。

select 姓名,学号,专业名 from xs where 学号 not in(select 学号 from XS_KC where 课程号 in(select 课程号 from KC where 课程名='离散数学'));

32）查找选修了离散数学的学生学号。

select 学号 from XS_kc join kc using(课程号) where 课程名='离散数学';

33）查找xs表中比所有计算机系的学生年龄都大的学生学号、姓名、专业名、出生日期。

select 学号,姓名,专业名,出生时间 from xs where 出生时间>all (select 出生时间 from xs where 专业名='计算机');

34）查找xs_kc表中课程号为206的成绩不低于课程号101的最低成绩的学生的学号。

select 学号 from XS_KC where 课程号='206' and 成绩>any(select 成绩 from XS_KC where 课程号='101');

35）查找选修了206号课程的学生姓名。

select 姓名 from xs join XS_KC using(学号) where 课程号='206';

36）将xs中各专业名输出。

select 专业名 from xs;

37）求xs中各专业的学生数。

select 专业名,count(*) as 各专业学生数 from xs group by 专业名;

38）求被选修的各们课程的平均成绩和选修该课程的人数。

select 课程号,avg(成绩) as 各选修课程的平均成绩,count(*) from XS_KC group by 课程号;

39）在xscj数据库上产生一个结果集，包括每个专业的男生人数、女生人数、总人数以及学生总人数。

select 专业名,性别,count(*) as 各专业的人数 from xs group by 专业名,性别 with rollup;

40）查找xscj数据中平均成绩在85分以上的学生的学号和平均成绩。

select 学号,avg(成绩) as 平均成绩 from XS_KC group by 学号 having avg(成绩)>85;

41）查找选修课程超过2门且成绩都在80分以上的学生的学号。

select 学号 from XS_KC where 成绩>=80 group by 学号 having count(*)>2;

42）将通信工程专业的学生按出生日期先后排序。

select 专业名,出生时间 from xs where 专业名='通信工程' order by 出生时间;

43）将计算机专业学生的“计算机基础”课程成绩按降序排列。

select 课程名,成绩 from kc join XS_kc using(课程号) where 课程名='计算机基础' order by 成绩 desc;

44）将计算机专业学生按期平均成绩排列。

select 学号,姓名,专业名 from xs where 专业名='计算机' order by(select avg(成绩) from XS_KC group by XS_KC.学号 having xs.学号=XS_KC.学号);

45）查找XS表中学号最靠前的5位学生的信息。

select * from xs order by 学号 limit 5;

46）查找XS表中从第四位同学开始的5位学生的信息。

select * from xs order by 学号 limit 3,5;