SQL操练题完整,消灭星星

By admin in web前端 on 2019年9月12日

H5游戏开发:消灭星星

2018/01/25 · HTML5 ·
游戏

原文出处: 凹凸实验室   

「消灭星星」是一款很经典的「消除类游戏」,它的玩法很简单:消除相连通的同色砖块。

web前端 1

H5游戏开发:一笔画

2017/11/07 · HTML5 ·
游戏

原文出处: 凹凸实验室   

web前端 2

SQL 练习题答案

1. 游戏规则

「消灭星星」存在多个版本,不过它们的规则除了「关卡分值」有些出入外,其它的规则都是一样的。笔者介绍的版本的游戏规则整理如下:

1. 色砖分布

  • 10 x 10 的表格
  • 5种颜色 —— 红、绿、蓝,黄,紫
  • 每类色砖个数在指定区间内随机
  • 5类色砖在 10 x 10 表格中随机分布

2. 消除规则

两个或两个以上同色砖块相连通即是可被消除的砖块。

3. 分值规则

  • 消除总分值 = n * n * 5
  • 奖励总分值 = 2000 – n * n * 20

「n」表示砖块数量。上面是「总」分值的规则,还有「单」个砖块的分值规则:

  • 消除砖块得分值 = 10 * i + 5
  • 剩余砖块扣分值 = 40 * i + 20

「i」表示砖块的索引值(从 0
开始)。简单地说,单个砖块「得分值」和「扣分值」是一个等差数列。

4. 关卡分值

关卡分值 = 1000 + (level – 1) * 2000;「level」即当前关卡数。

5. 通关条件

  • 可消除色块不存在
  • 累计分值 >= 当前关卡分值

上面两个条件同时成立游戏才可以通关。

H5游戏开发:一笔画

by leeenx on 2017-11-02

一笔画是图论[科普](https://zh.wikipedia.org/wiki/%E5%9B%BE%E8%AE%BA)中一个著名的问题,它起源于柯尼斯堡七桥问题[科普](https://zh.wikipedia.org/wiki/%E6%9F%AF%E5%B0%BC%E6%96%AF%E5%A0%A1%E4%B8%83%E6%A1%A5%E9%97%AE%E9%A2%98)。数学家欧拉在他1736年发表的论文《柯尼斯堡的七桥》中不仅解决了七桥问题,也提出了一笔画定理,顺带解决了一笔画问题。用图论的术语来说,对于一个给定的连通图[科普](https://zh.wikipedia.org/wiki/%E8%BF%9E%E9%80%9A%E5%9B%BE)存在一条恰好包含所有线段并且没有重复的路径,这条路径就是「一笔画」。

寻找连通图这条路径的过程就是「一笔画」的游戏过程,如下:

web前端 3

 

2. MVC 设计模式

笔者这次又是使用了 MVC
模式来写「消灭星星」。星星「砖块」的数据结构与各种状态由 Model
实现,游戏的核心在 Model 中完成;View 映射 Model
的变化并做出对应的行为,它的任务主要是展示动画;用户与游戏的交互由
Control 完成。

从逻辑规划上看,Model 很重而View 与 Control
很轻,不过,从代码量上看,View 很重而 Model 与 Control 相对很轻。

游戏的实现

「一笔画」的实现不复杂,笔者把实现过程分成两步:

  1. 底图绘制
  2. 交互绘制

「底图绘制」把连通图以「点线」的形式显示在画布上,是游戏最容易实现的部分;「交互绘制」是用户绘制解题路径的过程,这个过程会主要是处理点与点动态成线的逻辑。

一、补充作业一、

 

设有三个关系:

               S(SNO, SNAME, AGE, SEX,Sdept)

               SC(SNO, CNO, GRADE)

               C(CNO, CNAME, TEACHER)

试用关系代数表达式表示下列查询:

 

1、查询学号为S3学生所学课程的课程名与任课教师名。

  

2、查询至少选修LIU老师所教课程中一门课的女生姓名。

3、查询WANG同学不学的课程的课程号。

4、查询至少选修两门课程的学生学号。

5、查询选修课程中包含LIU老师所教全部课程的学生学号。

补充作业二、

 

三个关系同上,试用SQL语言表示下列查询:

 

1、  查询门门课程都及格的学生的学号

方法1:

提示:根据学号分组,就得到每个学生所有的课程成绩,在某个学生这一组成绩里,如果他所有的课程成绩都大于60分则输出该组学生的学号

Select sno frome sc group by sno having(min(grade)>=60)

 

2、查询既有课程大于90分又有课程不及格的学生的学号

自身连接:

Select sno from sc where grade >90 and sno in (select sno from sc where grade<60)

 

3、查询平均分不及格的课程号和平均成绩

Select cno , avg(GRADE) from sc group by cno having avg(grade)<60

查询平均分及格的课程号和课程名

Select C.cno , Cname from SC,C where C.cno=SC.cno group by C.cno having avg(grade)>=60

 

4、找出至少选修了2号学生选修过的全部课程的学生号

提示:不存在这样的课程y,学生2选修了y,而学生x没有选。

SELECT DISTINCT Sno

   FROM SC as SCX

   WHERE NOT EXISTS

      (SELECT *

       FROM SC as SCY

       WHERE SCY.Sno =‘2’AND NOT EXISTS

                               (SELECT *

                                  FROM SC SCZ

                          WHERE SCZ.Sno=SCX.Sno AND SCZ.Cno=SCY.Cno))



5、求各门课程去掉一个最高分和最低分后的平均分

第一步,求所有成绩的平均分(去掉一个最高分和最低分)

select   avg(GRADE)   from   SC       where   GRADE   not   in (select   top   1   GRADE   from   SC order   by   GRADE)     and     GRADE   not   in (select   top   1   GRADE   from   SC order   by   GRADE   desc)  

第二步,将所有成绩按各门课程的课程号CNO分组

SELECT CNO avg(GRADE)   from   SC       where   GRADE   not   in (select   top  1  GRADE   from   SC order   by   GRADE)     and     GRADE   not   in (select   top  1  GRADE   from   SC order   by   GRADE   desc) group by CNO

3. Model

10 x 10 的表格用长度为 100 的数组可完美映射游戏的星星「砖块」。

[ R, R, G, G, B, B, Y, Y, P, P, R, R, G, G, B, B, Y, Y, P, P, R, R, G,
G, B, B, Y, Y, P, P, R, R, G, G, B, B, Y, Y, P, P, R, R, G, G, B, B, Y,
Y, P, P, R, R, G, G, B, B, Y, Y, P, P, R, R, G, G, B, B, Y, Y, P, P, R,
R, G, G, B, B, Y, Y, P, P, R, R, G, G, B, B, Y, Y, P, P, R, R, G, G, B,
B, Y, Y, P, P ]

1
2
3
4
5
6
7
8
9
10
11
12
[
R, R, G, G, B, B, Y, Y, P, P,
R, R, G, G, B, B, Y, Y, P, P,
R, R, G, G, B, B, Y, Y, P, P,
R, R, G, G, B, B, Y, Y, P, P,
R, R, G, G, B, B, Y, Y, P, P,
R, R, G, G, B, B, Y, Y, P, P,
R, R, G, G, B, B, Y, Y, P, P,
R, R, G, G, B, B, Y, Y, P, P,
R, R, G, G, B, B, Y, Y, P, P,
R, R, G, G, B, B, Y, Y, P, P
]

R – 红色,G – 绿色,B – 蓝色,Y – 黄色,P – 紫色。Model
的核心任务是以下四个:

  • 生成砖墙
  • 消除砖块 (生成砖块分值)
  • 夯实砖墙
  • 清除残砖 (生成奖励分值)

底图绘制

「一笔画」是多关卡的游戏模式,笔者决定把关卡(连通图)的定制以一个配置接口的形式对外暴露。对外暴露关卡接口需要有一套描述连通图形状的规范,而在笔者面前有两个选项:

  • 点记法
  • 线记法

举个连通图 —— 五角星为例来说一下这两个选项。

web前端 4

点记法如下:

JavaScript

levels: [ // 当前关卡 { name: “五角星”, coords: [ {x: Ax, y: Ay}, {x:
Bx, y: By}, {x: Cx, y: Cy}, {x: Dx, y: Dy}, {x: Ex, y: Ey}, {x: Ax, y:
Ay} ] } … ]

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
levels: [
// 当前关卡
{
name: "五角星",
coords: [
{x: Ax, y: Ay},
{x: Bx, y: By},
{x: Cx, y: Cy},
{x: Dx, y: Dy},
{x: Ex, y: Ey},
{x: Ax, y: Ay}
]
}
]

线记法如下:

JavaScript

levels: [ // 当前关卡 { name: “五角星”, lines: [ {x1: Ax, y1: Ay, x2:
Bx, y2: By}, {x1: Bx, y1: By, x2: Cx, y2: Cy}, {x1: Cx, y1: Cy, x2: Dx,
y2: Dy}, {x1: Dx, y1: Dy, x2: Ex, y2: Ey}, {x1: Ex, y1: Ey, x2: Ax, y2:
Ay} ] } ]

1
2
3
4
5
6
7
8
9
10
11
12
13
levels: [
// 当前关卡
{
name: "五角星",
lines: [
{x1: Ax, y1: Ay, x2: Bx, y2: By},
{x1: Bx, y1: By, x2: Cx, y2: Cy},
{x1: Cx, y1: Cy, x2: Dx, y2: Dy},
{x1: Dx, y1: Dy, x2: Ex, y2: Ey},
{x1: Ex, y1: Ey, x2: Ax, y2: Ay}
]
}
]

「点记法」记录关卡通关的一个答案,即端点要按一定的顺序存放到数组
coords中,它是有序性的记录。「线记法」通过两点描述连通图的线段,它是无序的记录。「点记法」最大的优势是表现更简洁,但它必须记录一个通关答案,笔者只是关卡的搬运工不是关卡创造者,所以笔者最终选择了「线记法」。:)

 

3.1 生成砖墙

砖墙分两步生成:

  • 色砖数量分配
  • 打散色砖

理论上,可以将 100 个格子可以均分到 5
类颜色,不过笔者玩过的「消灭星星」都不使用均分策略。通过分析几款「消灭星星」,其实可以发现一个规律
—— 「色砖之间的数量差在一个固定的区间内」。

如果把传统意义上的均分称作「完全均分」,那么「消灭星星」的分配是一种在均分线上下波动的「不完全均分」。

web前端 5

笔者把上面的「不完全均分」称作「波动均分」,算法的具体实现可以参见「波动均分算法」。

「打散色砖」其实就是将数组乱序的过程,笔者推荐使用「
费雪耶兹乱序算法」。

以下是伪代码的实现:

JavaScript

// 波动均分色砖 waveaverage(5, 4, 4).forEach( // tiles 即色墙数组
(count, clr) => tiles.concat(generateTiles(count, clr)); ); //
打散色砖 shuffle(tiles);

1
2
3
4
5
6
7
// 波动均分色砖
waveaverage(5, 4, 4).forEach(
// tiles 即色墙数组
(count, clr) => tiles.concat(generateTiles(count, clr));
);
// 打散色砖
shuffle(tiles);

交互绘制

在画布上绘制路径,从视觉上说是「选择或连接连通图端点」的过程,这个过程需要解决2个问题:

  • 手指下是否有端点
  • 选中点到待选中点之间能否成线

收集连通图端点的坐标,再监听手指滑过的坐标可以知道「手指下是否有点」。以下伪代码是收集端点坐标:

JavaScript

// 端点坐标信息 let coords = []; lines.forEach(({x1, y1, x2, y2})
=> { // (x1, y1) 在 coords 数组不存在 if(!isExist(x1, y1))
coords.push([x1, y1]); // (x2, y2) 在 coords 数组不存在
if(!isExist(x2, y2)) coords.push([x2, y2]); });

1
2
3
4
5
6
7
8
// 端点坐标信息
let coords = [];
lines.forEach(({x1, y1, x2, y2}) => {
// (x1, y1) 在 coords 数组不存在
if(!isExist(x1, y1)) coords.push([x1, y1]);
// (x2, y2) 在 coords 数组不存在
if(!isExist(x2, y2)) coords.push([x2, y2]);
});

以下伪代码是监听手指滑动:

JavaScript

easel.addEventListener(“touchmove”, e => { let x0 =
e.targetTouches[0].pageX, y0 = e.targetTouches[0].pageY; // 端点半径
—— 取连通图端点半径的2倍,提升移动端体验 let r = radius * 2;
for(let [x, y] of coords){ if(Math.sqrt(Math.pow(x – x0, 2) +
Math.pow(y – y0), 2) <= r){ // 手指下有端点,判断能否连线
if(canConnect(x, y)) { // todo } break; } } })

1
2
3
4
5
6
7
8
9
10
11
12
13
14
easel.addEventListener("touchmove", e => {
let x0 = e.targetTouches[0].pageX, y0 = e.targetTouches[0].pageY;
// 端点半径 —— 取连通图端点半径的2倍,提升移动端体验
let r = radius * 2;
for(let [x, y] of coords){
if(Math.sqrt(Math.pow(x – x0, 2) + Math.pow(y – y0), 2) <= r){
// 手指下有端点,判断能否连线
if(canConnect(x, y)) {
// todo
}
break;
}
}
})

在未绘制任何线段或端点之前,手指滑过的任意端点都会被视作「一笔画」的起始点;在绘制了线段(或有选中点)后,手指滑过的端点能否与选中点串连成线段需要依据现有条件进行判断。

web前端 6

上图,点A与点B可连接成线段,而点A与点C不能连接。笔者把「可以与指定端点连接成线段的端点称作有效连接点」。连通图端点的有效连接点从连通图的线段中提取:

JavaScript

coords.forEach(coord => { // 有效连接点(坐标)挂载在端点坐标下
coord.validCoords = []; lines.forEach(({x1, y1, x2, y2}) => { //
坐标是当前线段的起点 if(coord.x === x1 && coord.y === y1) {
coord.validCoords.push([x2, y2]); } // 坐标是当前线段的终点 else
if(coord.x === x2 && coord.y === y2) { coord.validCoords.push([x1,
y1]); } }) })

1
2
3
4
5
6
7
8
9
10
11
12
13
14
coords.forEach(coord => {
// 有效连接点(坐标)挂载在端点坐标下
coord.validCoords = [];
lines.forEach(({x1, y1, x2, y2}) => {
// 坐标是当前线段的起点
if(coord.x === x1 && coord.y === y1) {
coord.validCoords.push([x2, y2]);
}
// 坐标是当前线段的终点
else if(coord.x === x2 && coord.y === y2) {
coord.validCoords.push([x1, y1]);
}
})
})

But…有效连接点只能判断两个点是否为底图的线段,这只是一个静态的参考,在实际的「交互绘制」中,会遇到以下情况:

web前端 7
如上图,AB已串连成线段,当前选中点B的有效连接点是 A 与 C。AB
已经连接成线,如果 BA 也串连成线段,那么线段就重复了,所以此时 BA
不能成线,只有 AC 才能成线。

对选中点而言,它的有效连接点有两种:

  • 与选中点「成线的有效连接点」
  • 与选中点「未成线的有效连接点」

其中「未成线的有效连接点」才能参与「交互绘制」,并且它是动态的。

web前端 8

回头本节内容开头提的两个问题「手指下是否有端点」 与
「选中点到待选中点之间能否成线」,其实可合并为一个问题:手指下是否存在「未成线的有效连接点」。只须把监听手指滑动遍历的数组由连通图所有的端点坐标
coords 替换为当前选中点的「未成线的有效连接点」即可。

至此「一笔画」的主要功能已经实现。可以抢先体验一下:

web前端 9

 1、查询7号课程没有考试成绩的学生学号。

3.2 消除砖块

「消除砖块」的规则很简单 —— 相邻相连通相同色即可以消除

web前端 10
前两个组合符合「相邻相连通相同色即可以消除」,所以它们可以被消除;第三个组合虽然「相邻相同色」但是不「相连通」所以它不能被消除。

「消除砖块」的同时有一个重要的任务:生成砖块对应的分值。在「游戏规则」中,笔者已经提供了对应的数学公式:「消除砖块得分值
= 10 * i + 5」。

「消除砖块」算法实现如下:

JavaScript

function clean(tile) { let count = 1; let sameTiles =
searchSameTiles(tile); if(sameTiles.length > 0) { deleteTile(tile);
while(true) { let nextSameTiles = []; sameTiles.forEach(tile => {
nextSameTiles.push(…searchSameTiles(tile)); makeScore(++count * 10 +
5); // 标记当前分值 deleteTile(tile); // 删除砖块 }); //
清除完成,跳出循环 if(nextSameTiles.length === 0) break; else {
sameTiles = nextSameTiles; } } } }

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
function clean(tile) {
let count = 1;
let sameTiles = searchSameTiles(tile);
if(sameTiles.length > 0) {
deleteTile(tile);
while(true) {
let nextSameTiles = [];
sameTiles.forEach(tile => {
nextSameTiles.push(…searchSameTiles(tile));
makeScore(++count * 10 + 5); // 标记当前分值
deleteTile(tile); // 删除砖块
});
// 清除完成,跳出循环
if(nextSameTiles.length === 0) break;
else {
sameTiles = nextSameTiles;
}
}
}
}

清除的算法使用「递归」逻辑上会清晰一些,不过「递归」在浏览器上容易「栈溢出」,所以笔者没有使用「递归」实现。

自动识图

笔者在录入关卡配置时,发现一个7条边以上的连通图很容易录错或录重线段。笔者在思考能否开发一个自动识别图形的插件,毕竟「一笔画」的图形是有规则的几何图形。

web前端 11

上面的关卡「底图」,一眼就可以识出三个颜色:

  • 白底
  • 端点颜色
  • 线段颜色

并且这三种颜色在「底图」的面积大小顺序是:白底 > 线段颜色 >
端点颜色。底图的「采集色值表算法」很简单,如下伪代码:

JavaScript

let imageData = ctx.getImageData(); let data = imageData.data; // 色值表
let clrs = new Map(); for(let i = 0, len = data.length; i < len; i +=
4) { let [r, g, b, a] = [data[i], data[i + 1], data[i + 2],
data[i + 3]]; let key = `rgba(${r}, ${g}, ${b}, ${a})`; let value =
clrs.get(key) || {r, g, b, a, count: 0}; clrs.has(key) ? ++value.count :
clrs.set(rgba, {r, g, b, a, count}); }

1
2
3
4
5
6
7
8
9
10
let imageData = ctx.getImageData();
let data = imageData.data;
// 色值表
let clrs = new Map();
for(let i = 0, len = data.length; i < len; i += 4) {
let [r, g, b, a] = [data[i], data[i + 1], data[i + 2], data[i + 3]];
let key = `rgba(${r}, ${g}, ${b}, ${a})`;
let value = clrs.get(key) || {r, g, b, a, count: 0};
clrs.has(key) ? ++value.count : clrs.set(rgba, {r, g, b, a, count});
}

对于连通图来说,只要把端点识别出来,连通图的轮廓也就出来了。

    Select sno fromsc where cno=’7′ and
grade is null

3.3 夯实砖墙

砖墙在消除了部分砖块后,会出现空洞,此时需要对墙体进行夯实:

向下夯实 向左夯实 向左下夯实(先下后左)

一种快速的实现方案是,每次「消除砖块」后直接遍历砖墙数组(10×10数组)再把空洞夯实,伪代码表示如下:

JavaScript

for(let row = 0; row < 10; ++row) { for(let col = 0; col < 10;
++col) { if(isEmpty(row, col)) { // 水平方向(向左)夯实
if(isEmptyCol(col)) { tampRow(col); } // 垂直方向(向下)夯实 else {
tampCol(col); } break; } } }

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
for(let row = 0; row < 10; ++row) {
for(let col = 0; col < 10; ++col) {
if(isEmpty(row, col)) {
// 水平方向(向左)夯实
if(isEmptyCol(col)) {
tampRow(col);
}
// 垂直方向(向下)夯实
else {
tampCol(col);
}
break;
}
}
}

But…
为了夯实一个空洞对一张大数组进行全量遍历并不是一种高效的算法。在笔者看来影响「墙体夯实」效率的因素有:

  1. 定位空洞
  2. 砖块移动(夯实)

扫描墙体数组的主要目的是「定位空洞」,但能否不扫描墙体数组直接「定位空洞」?

墙体的「空洞」是由于「消除砖块」造成的,换种说法 ——
被消除的砖块留下来的坑位就是墙体的空洞。在「消除砖块」的同时标记空洞的位置,这样就无须全量扫描墙体数组,伪代码如下:

JavaScript

function deleteTile(tile) { // 标记空洞 markHollow(tile.index); //
删除砖块逻辑 … }

1
2
3
4
5
6
function deleteTile(tile) {
// 标记空洞
markHollow(tile.index);
// 删除砖块逻辑
}

在上面的夯实动图,其实可以看到它的夯实过程如下:

  1. 空洞上方的砖块向下移动
  2. 空列右侧的砖块向左移动

墙体在「夯实」过程中,它的边界是实时在变化,如果「夯实」不按真实边界进行扫描,会产生多余的空白扫描:

web前端 12

如何记录墙体的边界?
把墙体拆分成一个个单独的列,那么列最顶部的空白格片段就是墙体的「空白」,而其余非顶部的空白格片段即墙体的「空洞」。

web前端 13

笔者使用一组「列集合」来描述墙体的边界并记录墙体的空洞,它的模型如下:

JavaScript

/* @ count – 列砖块数 @ start – 顶部行索引 @ end – 底部行索引 @
pitCount – 坑数 @ topPit – 最顶部的坑 @ bottomPit – 最底部的坑 */ let
wall = [ {count, start, end, pitCount, topPit, bottomPit}, {count,
start, end, pitCount, topPit, bottomPit}, … ];

1
2
3
4
5
6
7
8
9
10
11
12
13
/*
@ count – 列砖块数
@ start – 顶部行索引
@ end – 底部行索引
@ pitCount – 坑数
@ topPit – 最顶部的坑
@ bottomPit – 最底部的坑
*/
let wall = [
{count, start, end, pitCount, topPit, bottomPit},
{count, start, end, pitCount, topPit, bottomPit},
];

这个模型可以描述墙体的三个细节:

  • 空列
  • 列的连续空洞
  • 列的非连续空洞
JavaScript

// 空列 if(count === 0) { ... } // 连续空洞 else if(bottomPit -
topPit + 1 === pitCount) { ... } // 非连续空洞 else { ... }

<table>
<colgroup>
<col style="width: 50%" />
<col style="width: 50%" />
</colgroup>
<tbody>
<tr class="odd">
<td><div class="crayon-nums-content" style="font-size: 13px !important; line-height: 15px !important;">
<div class="crayon-num" data-line="crayon-5b8f3d2c2df29914802382-1">
1
</div>
<div class="crayon-num crayon-striped-num" data-line="crayon-5b8f3d2c2df29914802382-2">
2
</div>
<div class="crayon-num" data-line="crayon-5b8f3d2c2df29914802382-3">
3
</div>
<div class="crayon-num crayon-striped-num" data-line="crayon-5b8f3d2c2df29914802382-4">
4
</div>
<div class="crayon-num" data-line="crayon-5b8f3d2c2df29914802382-5">
5
</div>
<div class="crayon-num crayon-striped-num" data-line="crayon-5b8f3d2c2df29914802382-6">
6
</div>
<div class="crayon-num" data-line="crayon-5b8f3d2c2df29914802382-7">
7
</div>
<div class="crayon-num crayon-striped-num" data-line="crayon-5b8f3d2c2df29914802382-8">
8
</div>
<div class="crayon-num" data-line="crayon-5b8f3d2c2df29914802382-9">
9
</div>
<div class="crayon-num crayon-striped-num" data-line="crayon-5b8f3d2c2df29914802382-10">
10
</div>
<div class="crayon-num" data-line="crayon-5b8f3d2c2df29914802382-11">
11
</div>
<div class="crayon-num crayon-striped-num" data-line="crayon-5b8f3d2c2df29914802382-12">
12
</div>
</div></td>
<td><div class="crayon-pre" style="font-size: 13px !important; line-height: 15px !important; -moz-tab-size:4; -o-tab-size:4; -webkit-tab-size:4; tab-size:4;">
<div id="crayon-5b8f3d2c2df29914802382-1" class="crayon-line">
// 空列
</div>
<div id="crayon-5b8f3d2c2df29914802382-2" class="crayon-line crayon-striped-line">
if(count === 0) { 
</div>
<div id="crayon-5b8f3d2c2df29914802382-3" class="crayon-line">
 ...
</div>
<div id="crayon-5b8f3d2c2df29914802382-4" class="crayon-line crayon-striped-line">
}
</div>
<div id="crayon-5b8f3d2c2df29914802382-5" class="crayon-line">
// 连续空洞
</div>
<div id="crayon-5b8f3d2c2df29914802382-6" class="crayon-line crayon-striped-line">
else if(bottomPit - topPit + 1 === pitCount) { 
</div>
<div id="crayon-5b8f3d2c2df29914802382-7" class="crayon-line">
 ...
</div>
<div id="crayon-5b8f3d2c2df29914802382-8" class="crayon-line crayon-striped-line">
}
</div>
<div id="crayon-5b8f3d2c2df29914802382-9" class="crayon-line">
// 非连续空洞
</div>
<div id="crayon-5b8f3d2c2df29914802382-10" class="crayon-line crayon-striped-line">
else {
</div>
<div id="crayon-5b8f3d2c2df29914802382-11" class="crayon-line">
 ...
</div>
<div id="crayon-5b8f3d2c2df29914802382-12" class="crayon-line crayon-striped-line">
}
</div>
</div></td>
</tr>
</tbody>
</table>

砖块在消除后,映射到单个列上的空洞会有两种分布形态 —— 连续与非连续。

web前端 14

「连续空洞」与「非连续空洞」的夯实过程如下:

web前端 15

其实「空列」放大于墙体上,也会有「空洞」类似的分布形态 ——
连续与非连续。
web前端 16

它的夯实过程与空洞类似,这里就不赘述了。

端点识别

理论上,通过采集的「色值表」可以直接把端点的坐标识别出来。笔者设计的「端点识别算法」分以下2步:

  1. 按像素扫描底图直到遇到「端点颜色」的像素,进入第二步
  2. 从底图上清除端点并记录它的坐标,返回继续第一步

伪代码如下:

JavaScript

for(let i = 0, len = data.length; i < len; i += 4) { let [r, g, b,
a] = [data[i], data[i + 1], data[i + 2], data[i + 3]]; //
当前像素颜色属于端点 if(isBelongVertex(r, g, b, a)) { // 在 data
中清空端点 vertex = clearVertex(i); // 记录端点信息
vertexes.push(vertext); } }

1
2
3
4
5
6
7
8
9
10
for(let i = 0, len = data.length; i < len; i += 4) {
let [r, g, b, a] = [data[i], data[i + 1], data[i + 2], data[i + 3]];
// 当前像素颜色属于端点
if(isBelongVertex(r, g, b, a)) {
// 在 data 中清空端点
vertex = clearVertex(i);
// 记录端点信息
vertexes.push(vertext);
}
}

But…
上面的算法只能跑无损图。笔者在使用了一张手机截屏做测试的时候发现,收集到的「色值表」长度为
5000+ !这直接导致端点和线段的色值无法直接获得。

经过分析,可以发现「色值表」里绝大多数色值都是相近的,也就是在原来的「采集色值表算法」的基础上添加一个近似颜色过滤即可以找出端点和线段的主色。伪代码实现如下:

JavaScript

let lineColor = vertexColor = {count: 0}; for(let clr of clrs) { //
与底色相近,跳过 if(isBelongBackground(clr)) continue; //
线段是数量第二多的颜色,端点是第三多的颜色 if(clr.count >
lineColor.count) { [vertexColor, lineColor] = [lineColor, clr] } }

1
2
3
4
5
6
7
8
9
let lineColor = vertexColor = {count: 0};
for(let clr of clrs) {
// 与底色相近,跳过
if(isBelongBackground(clr)) continue;
// 线段是数量第二多的颜色,端点是第三多的颜色
if(clr.count > lineColor.count) {
[vertexColor, lineColor] = [lineColor, clr]
}
}

取到端点的主色后,再跑一次「端点识别算法」后居识别出 203
个端点!这是为什么呢?

web前端 17

上图是放大5倍后的底图局部,蓝色端点的周围和内部充斥着大量噪点(杂色块)。事实上在「端点识别」过程中,由于噪点的存在,把原本的端点被分解成十几个或数十个小端点了,以下是跑过「端点识别算法」后的底图:

web前端 18

通过上图,可以直观地得出一个结论:识别出来的小端点只在目标(大)端点上集中分布,并且大端点范围内的小端点叠加交错。

如果把叠加交错的小端点归并成一个大端点,那么这个大端点将十分接近目标端点。小端点的归并伪代码如下:

JavaScript

for(let i = 0, len = vertexes.length; i < len – 1; ++i) { let vertexA
= vertexes[i]; if(vertextA === undefined) continue; // 注意这里 j = 0
而不是 j = i +1 for(let j = 0; j < len; ++j) { let vertexB =
vertexes[j]; if(vertextB === undefined) continue; //
点A与点B有叠加,点B合并到点A并删除点B if(isCross(vertexA, vertexB)) {
vertexA = merge(vertexA, vertexB); delete vertexA; } } }

1
2
3
4
5
6
7
8
9
10
11
12
13
14
for(let i = 0, len = vertexes.length; i < len – 1; ++i) {
let vertexA = vertexes[i];
if(vertextA === undefined) continue;
// 注意这里 j = 0 而不是 j = i +1
for(let j = 0; j < len; ++j) {
let vertexB = vertexes[j];
if(vertextB === undefined) continue;
// 点A与点B有叠加,点B合并到点A并删除点B
if(isCross(vertexA, vertexB)) {
vertexA = merge(vertexA, vertexB);
delete vertexA;
}
}
}

加了小端点归并算法后,「端点识别」的准确度就上去了。经笔者本地测试已经可以
100% 识别有损的连通图了。

 

3.4 消除残砖

上一小节提到了「描述墙体的边界并记录墙体的空洞」的「列集合」,笔者是直接使用这个「列集合」来消除残砖的,伪代码如下:

JavaScript

function clearAll() { let count = 0; for(let col = 0, len =
this.wall.length; col < len; ++col) { let colInfo = this.wall[col];
for(let row = colInfo.start; row <= colInfo.end; ++row) { let tile =
this.grid[row * this.col + col]; tile.score = -20 – 40 * count++; //
标记奖励分数 tile.removed = true; } } }

1
2
3
4
5
6
7
8
9
10
11
function clearAll() {
let count = 0;
for(let col = 0, len = this.wall.length;  col < len; ++col) {
let colInfo = this.wall[col];
for(let row = colInfo.start; row <= colInfo.end; ++row) {
let tile = this.grid[row * this.col + col];
tile.score = -20 – 40 * count++; // 标记奖励分数
tile.removed = true;
}
}
}

线段识别

笔者分两个步骤完成「线段识别」:

  1. 给定的两个端点连接成线,并采集连线上N个「样本点」;
  2. 遍历样本点像素,如果像素色值不等于线段色值则表示这两个端点之间不存在线段

如何采集「样式点」是个问题,太密集会影响性能;太疏松精准度不能保证。

在笔者面前有两个选择:N 是常量;N 是变量。
假设 N === 5。局部提取「样式点」如下:

web前端 19

上图,会识别出三条线段:AB, BC 和 AC。而事实上,AC不能成线,它只是因为
AB 和 BC 视觉上共一线的结果。当然把 N 值向上提高可以解决这个问题,不过 N
作为常量的话,这个常量的取量需要靠经验来判断,果然放弃。

为了避免 AB 与 BC 同处一直线时 AC 被识别成线段,其实很简单 ——
两个「样本点」的间隔小于或等于端点直径
假设 N = S / (2 * R),S 表示两点的距离,R
表示端点半径。局部提取「样式点」如下:

web前端 20

如上图,成功地绕过了 AC。「线段识别算法」的伪代码实现如下:

JavaScript

for(let i = 0, len = vertexes.length; i < len – 1; ++i) { let {x: x1,
y: y1} = vertexes[i]; for(let j = i + 1; j < len; ++j) { let {x:
x2, y: y2} = vertexes[j]; let S = Math.sqrt(Math.pow(x1 – x2, 2) +
Math.pow(y1 – y2, 2)); let N = S / (R * 2); let stepX = (x1 – x2) / N,
stepY = (y1 – y2) / n; while(–N) { // 样本点不是线段色
if(!isBelongLine(x1 + N * stepX, y1 + N * stepY)) break; } //
样本点都合格 —- 表示两点成线,保存 if(0 === N) lines.push({x1, y1, x2,
y2}) } }

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
for(let i = 0, len = vertexes.length; i < len – 1; ++i) {
let {x: x1, y: y1} = vertexes[i];
for(let j = i + 1; j < len; ++j) {
let {x: x2, y: y2} = vertexes[j];
let S = Math.sqrt(Math.pow(x1 – x2, 2) + Math.pow(y1 – y2, 2));
let N = S / (R * 2);
let stepX = (x1 – x2) / N, stepY = (y1 – y2) / n;
while(–N) {
// 样本点不是线段色
if(!isBelongLine(x1 + N * stepX, y1 + N * stepY)) break;
}
// 样本点都合格 —- 表示两点成线,保存
if(0 === N) lines.push({x1, y1, x2, y2})
}
}

 2、查询7号课程成绩在90分以上或60分以下的学生学号。

4. View

View 主要的功能有两个:

  • UI 管理
  • 映射 Model 的变化(动画)

UI
管理主要是指「界面绘制」与「资源加载管理」,这两项功能比较常见本文就直接略过了。View
的重头戏是「映射 Model
的变化」并完成对应的动画。动画是复杂的,而映射的原理是简单的,如下伪代码:

JavaScript

update({originIndex, index, clr, removed, score}) { // 还没有
originIndex 或没有色值,直接不处理 if(originIndex === undefined || clr
=== undefined) return ; let tile = this.tiles[originIndex]; // tile
存在,判断颜色是否一样 if(tile.clr !== clr) { this.updateTileClr(tile,
clr); } // 当前索引变化 —– 表示位置也有变化 if(tile.index !== index)
{ this.updateTileIndex(tile, index); } // 设置分数 if(tile.score !==
score) { tile.score = score; } if(tile.removed !== removed) { //
移除或添加当前节点 true === removed ? this.bomb(tile) :
this.area.addChild(tile.sprite); tile.removed = removed; } }

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
update({originIndex, index, clr, removed, score}) {
// 还没有 originIndex 或没有色值,直接不处理
if(originIndex === undefined || clr === undefined) return ;
let tile = this.tiles[originIndex];
// tile 存在,判断颜色是否一样
if(tile.clr !== clr) {
this.updateTileClr(tile, clr);
}
// 当前索引变化 —– 表示位置也有变化
if(tile.index !== index) {
this.updateTileIndex(tile, index);
}
// 设置分数
if(tile.score !== score) {
tile.score = score;
}
if(tile.removed !== removed) {
// 移除或添加当前节点
true === removed ? this.bomb(tile) : this.area.addChild(tile.sprite);
tile.removed = removed;
}
}

Model 的砖块每次数据的更改都会通知到 View 的砖块,View
会根据对应的变化做对应的动作(动画)。

性能优化

由于「自动识图」需要对图像的的像素点进行扫描,那么性能确实是个需要关注的问题。笔者设计的「自动识图算法」,在识别图像的过程中需要对图像的像素做两次扫描:「采集色值表」
与 「采集端点」。在扫描次数上其实很难降低了,但是对于一张 750 * 1334
的底图来说,「自动识图算法」需要遍历两次长度为
750 * 1334 * 4 = 4,002,000
的数组,压力还是会有的。笔者是从压缩被扫描数组的尺寸来提升性能的。

被扫描数组的尺寸怎么压缩?
笔者直接通过缩小画布的尺寸来达到缩小被扫描数组尺寸的。伪代码如下:

JavaScript

// 要压缩的倍数 let resolution = 4; let [width, height] = [img.width
/ resolution >> 0, img.height / resolution >> 0];
ctx.drawImage(img, 0, 0, width, height); let imageData =
ctx.getImageData(), data = imageData;

1
2
3
4
5
// 要压缩的倍数
let resolution = 4;
let [width, height] = [img.width / resolution >> 0, img.height / resolution >> 0];
ctx.drawImage(img, 0, 0, width, height);
let imageData = ctx.getImageData(), data = imageData;

把源图片缩小4倍后,得到的图片像素数组只有原来的
4^2 = 16倍。这在性能上是很大的提升。

Select sno from sc where cno=’7′ and grade
not between 60and 90

5. Control

Control 要处理的事务比较多,如下:

  • 绑定 Model & View
  • 生成通关分值
  • 判断通关条件
  • 对外事件
  • 用户交互

初始化时,Control 把 Model 的砖块单向绑定到 View 的砖块了。如下:

Object.defineProperties(model.tile, { originIndex: { get() {…}, set(){
… view.update({originIndex}) } }, index: { get() {…}, set() { …
view.update({index}) } }, clr: { get() {…}, set() { …
view.update({clr}) } }, removed: { get() {…}, set() { …
view.update({removed}) } }, score: { get() {…}, set() { …
view.update({score}) } } })

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
Object.defineProperties(model.tile, {
    originIndex: {
        get() {…},
        set(){
            …
            view.update({originIndex})
        }
    },  
    index: {
        get() {…},
        set() {
            …
            view.update({index})
        }
    },
    clr: {
        get() {…},
        set() {
            …
            view.update({clr})
        }
    },
    removed: {
        get() {…},
        set() {
            …
            view.update({removed})
        }
    },  
    score: {
        get() {…},
        set() {
            …
            view.update({score})
        }
    }
})
 

「通关分值」与「判断通关条件」这对逻辑在本文的「游戏规则」中有相关介绍,这里不再赘述。

对外事件规划如下:

name detail
pass 通关
pause 暂停
resume 恢复
gameover 游戏结束

用户交互 APIs 规划如下:

name type deltail
init method 初始化游戏
next method 进入下一关
enter method 进入指定关卡
pause method 暂停
resume method 恢复
destroy method 销毁游戏

使用「自动识图」的建议

尽管笔者在本地测试的时候可以把所有的「底图」识别出来,但是并不能保证其它开发者上传的图片能否被很好的识别出来。笔者建议,可以把「自动识图」做为一个单独的工具使用。

笔者写了一个「自动识图」的单独工具页面:
可以在这个页面生成对应的关卡配置。

 

6. 问题

在知乎有一个关于「消灭星星」的话题:popstar关卡是如何设计的?

这个话题在最后提出了一个问题 ——
「无法消除和最大得分不满足过关条件的矩阵」

web前端 21

「无法消除的矩阵」其实就是最大得分为0的矩阵,本质上是「最大得分不满足过关条件的矩阵」。

最大得分不满足过关条件的矩阵
求「矩阵」的最大得分是一个
「背包问题」,求解的算法不难:对当前矩阵用「递归」的形式把所有的消灭分支都执行一次,并取最高分值。但是
javascript 的「递归」极易「栈溢出」导致算法无法执行。

其实在知乎的话题中提到一个解决方案:

网上查到有程序提出做个工具随机生成关卡,自动计算,把符合得分条件的关卡筛选出来

这个解决方案代价是昂贵的!笔者提供有源码并没有解决这个问题,而是用一个比较取巧的方法:进入游戏前检查是事为「无法消除矩阵」,如果是重新生成关卡矩阵

注意:笔者使用的取巧方案并没有解决问题。

结语

下面是本文介绍的「一笔画」的线上
DEMO 的二维码:

web前端 9

游戏的源码托管在:
其中游戏实现的主体代码在:
自动识图的代码在:

感谢耐心阅读完本文章的读者。本文仅代表笔者的个人观点,如有不妥之处请不吝赐教。

感谢您的阅读,本文由 凹凸实验室
版权所有。如若转载,请注明出处:凹凸实验室()

1 赞 1 收藏
评论

web前端 23

 3、查询课程名以“数据”两个字开头的所有课程的课程号和课程名。

7. 结语

下面是本文介绍的「消灭星星」的线上 DEMO 的二维码:

web前端 24

游戏的源码托管在:

感谢耐心阅读完本文章的读者。本文仅代表笔者的个人观点,如有不妥之处请不吝赐教。
如果对「H5游戏开发」感兴趣,欢迎关注我们的专栏。

Select cno,cname from c where cname like
‘数据%’

参考资料

  • Knapsack problem
  • NP-completeness
  • popstar关卡是如何设计的?
  • 费雪耶兹乱序算法
  • 波动均分算法

    1 赞 收藏
    评论

web前端 23

 

 4、查询每个学生所有课程的平均成绩,输出学生学号和平均成绩。

    Select sno,avg(grade)from sc group by
sno

 5、查询每门课程的选修人数,输出课程号和选修人数。

    Selectcno,count(*) from sc group by
cno

 6、查询选修7号课程的学生的学号、姓名、性别。

    Selects.sno,sname,ssex from s,sc where
s.sno=sc.sno and cno=’7′

    或: Select sno,sname,ssex from s
where sno in

              ( Select sno from sc where
cno=’7′ )

 7、查询选修7号课程的学生的平均年龄。

    Selectavg(sage) from s,sc where
s.sno=sc.sno and cno=’7′

    或: Select avg(sage) from s where sno
in

              (Select sno from sc where
cno=’7′ )

 8、查询有30名以上学生选修的课程号。

    Select cno fromsc group by cno having
count(*)>30

 9、查询至今没有考试不及格的学生学号。

    Select distinctsno from sc where sno
not in

         ( Select sno from sc where
grade<60 )

    或: Select sno from sc group by sno
havingmin(grade)>=60

10、查询所有考试成绩的平均分相同的学生学号分组

二、

 1、找出选修课程号为C2的学生学号与成绩。

Select sno,grade from sc where
cno=’C2′

 

 2、找出选修课程号为C4的学生学号与姓名。

    Selects.sno,sname from s,sc where
s.sno=sc.sno and cno=’C4′

    注意本题也可以用嵌套做

思考本题改为“找出选修课程号为C4的学生学号、姓名与成绩”后还能用嵌套做吗?

 

 3、找出选修课程名为 Maths
的学生学号与姓名。

    Selects.sno,sname from s,sc,c

    where  s.sno=sc.sno and c.cno=sc.cno
andcname=’Maths’

注意本题也可以用嵌套做

 

 4、找出选修课程号为C2或C4的学生学号。

    Select distinctsno from sc where cno
in (‘C2′,’C4’)

或: Select distinct sno from sc where
cno=’C2′ or cno=’C4′

 

 5、找出选修课程号为C2和C4的学生学号。

    Select sno fromsc where cno=’C2′ and
sno in

         ( Select sno from sc where
cno=’C4′ )

    注意本题也可以用连接做

思考:Select distinct sno from sc where
cno=’C2′ andcno=’C4’正确吗?

 

 6、找出不学C2课程的学生姓名和年龄。

    Selectsname,sage from s where sno not
in

         ( Selectsno from sc where
cno=’C2′ )

    或: Select sname,sage from s where
not exists

              (Select * from sc where
sno=s.sno and cno=’C2′ )

 

 7、找出选修了数据库课程的所有学生姓名。(同3)

    Select snamefrom s,sc,c

where  s.sno=sc.snoand c.cno=sc.cno and
cname=’数据库’

 

 8、找出数据库课程不及格的女生姓名。

    连接:Select sname from s,sc,c

         where  s.sno=sc.sno
andc.cno=sc.cno and cname=’数据库’

                and grade<60 and
ssex=’女’

    嵌套:Select sname from s where
ssex=’女’ and  sno in

               (Select sno from sc where
grade<60 and cno in

                     ( Select cno from c
where cname=’数据库’ )

               )

 

 9、找出各门课程的平均成绩,输出课程名和平均成绩。

    Selectcname,avg(grade) from
sc,c

    wherec.cno=sc.cno  group by
c.cno,cname

思考本题也可以用嵌套做吗?

 

10、找出各个学生的平均成绩,输出学生姓名和平均成绩。

    Selectsname,avg(grade) from
s,sc

    wheres.sno=sc.sno group by
s.sno,sname

思考本题也可以用嵌套做吗?

 

11、找出至少有30个学生选修的课程名。

    Select cnamefrom c where cno in

         ( Selectcno from sc group by cno
having count(*)>=30 )

注意本题也可以用连接做

 

12、找出选修了不少于3门课程的学生姓名。

    Select snamefrom s where sno in

         ( Selectsno from sc group by sno
having count(*)>=3 )

注意本题也可以用连接做

 

13、找出各门课程的成绩均不低于90分的学生姓名。

   Select snamefrom s,sc where
s.sno=sc.sno

         group bys.sno,sname having
min(grade)>=90

方法二:

Select sname from s where sno not
in

         ( Selectsno from sc where
grade<90 )

只要有一门不小于90分就会输出该学生学号

 

14、找出数据库课程成绩不低于该门课程平均分的学生姓名。

    Select snamefrom s,sc,c

    where  s.sno=sc.sno and sc.cno=c.cno
and cname=’数据库’ and grade>

         ( Selectavg(grade) from
sc,c

           where sc.cno=c.cnoand
cname=’数据库’

         )

15、找出各个系科男女学生的平均年龄和人数。

    Selectsdept,ssex,avg(sage),count(*)
from s group by sdept,ssex

16、找出计算机系(JSJ)课程平均分最高的学生学号和姓名。

    Selects.sno,sname from s,sc where
s.sno=sc.sno and sdept=’JSJ’

    group bys.sno,sname

    havingavg(grade) >=ALL

         ( Selectavg(grade) from
s,sc

           wheres.sno=sc.sno and
sdept=’JSJ’

           group bys.sno

         )

17、(补充)查询每门课程的及格率。

    本题可以分三步做:

   

    第1步:得到每门课的选修人数

     createview  v_all(cno,cnt)

         as selectcno, count(*) from sc
group by cno

    第2步:得到每门课及格人数

     createview 
v_pass(cno,cnt_pass)

         as selectcno, count(*) from sc
where grade>=60 group by cno

   
第3步:每门课的及格人数/每门课选修人数

     selectv_all.cno, cnt_pass*100/cnt 
from  v_all, v_pass

     where v_all.cno = v_pass.cno

 

18、查询平均分不及格的学生的学号,姓名,平均分。

    Selectsc.sno,sname,avg(grade) from
student,sc

    wherestudent.sno=sc.sno

    group bysc.sno,sname

    havingavg(grade)<60

思考本题也可以用嵌套做吗?

 

19、查询平均分不及格的学生人数。

    Select count(*)from student

    where sno in

         ( selectsno from sc group by sno
having avg(grade)<60 )

    下面是一个典型的错误

Select count(*) from sc group by sno
havingavg(grade)<60

这是每个学生有几门不及格的数目

 

三、

 1、查询工资在1000到3000元之间的男性业务员的姓名和办公室编号。

    SelectYname,Ono from YWY

    where Salarybetween 1000 and 3000 and
Ysex=’男’

 2、查询各个办公室的业务员人数,输出办公室编号和对应的人数。

    SelectOno,count(*) from YWY group by
Ono

 3、查询每个客户在2002年5月购买的总金额,输出客户号和相应的总金额。

    SelectKno,sum(Fmoney) from FP

    where Fdatebetween ‘2002.5.1’ and
‘2002.5.31’

    group by Kno

 4、查询2002年5月购买次数超过5次的所有客户号,且按客户号升序排序。 

    Select Kno fromFP

    where Fdatebetween ‘2002.5.1’ and
‘2002.5.31’

    group by Kno

    havingcount(*)>5

    order by KnoASC

 5、查询各办公室男性和女性业务员的平均工资。

    SelectOno,Ysex,avg(Salary) from YWY
group by Ono,Ysex

 6、查询2002年5月曾经在王海亮业务员手中购买过商品的客户号、

            客户姓名和联系电话。

    SelectKno,Kname,Phone from KH where
Kno in

         ( SelectKno from FP

           whereFdate between ‘2002.5.1’
and ‘2002.5.31’ and Yno in

                      ( Select Yno from
YWY where Yname=’王海亮’ )

         )

    注意本题也可以用连接做

 7、查询所有工资比1538号业务员高的业务员的编号、姓名和工资。

    SelectYno,Yname,Salary from YWY where
Salary >

         ( SelectSalary from YWY where
Yno=’1538′ )

 8、查询所有与1538号业务员在同一个办公室的其他业务员的编号和姓名。

    SelectYno,Yname from YWY where
Yno!=’1538′ and Ono in

         ( SelectOno from YWY where
Yno=’1538′ )

 9、查询销售总金额最高的业务员的编号。

    Select Yno fromFP group by Yno having
sum(Fmoney) >=ALL

         ( Selectsum(Fmoney) from FP group
by Yno )

10、查询所有业务员的编号、姓名、工资以及工资比他高的其他业务员的平均工资。

    利用自连接

   
SelectY1.Yno,Y1.Yname,Y1.Salary,avg(Y2.Salary)

    from   YWY Y1, YWY Y2

    where  Y1.Salary < Y2.Salary

    group by  Y1.Yno  

 

四、

 1、找出每个班级的班级代码、学生人数、平均成绩。

    SelectBJDM,count(*),avg(CJ) from SC
group by BJDM

 2、找出每个学生的班级代码、学生姓名、考试科目数、总成绩。

    SelectBJDM,XSXM,count(*),sum(CJ) from
SC

    group byBJDM,BNXH,XSXM

 3、输出一张表格,每位学生对应一条记录,包括字段:

         
班级代码、学生姓名、语文成绩、数学成绩、外语成绩。

   
SelectSC1.BJDM,SC1.XSXM,SC1.CJ,SC2.CJ,SC3.CJ

    from  SC SC1, SC SC2, SC SC3

    whereSC1.BJDM=SC2.BJDM and
SC1.BNXH=SC2.BNXH and

         SC2.BJDM=SC3.BJDM and
SC2.BNXH=SC3.BNXH and

          SC1.KM=’语文’ and SC2.KM=’数学’
and SC3.KM=’外语’

 4、输出一张表格,有成绩低于60分的每位学生对应一条记录,包括字段:

         
班级代码、学生姓名、最低成绩。

    SelectBJDM,XSXM,min(CJ) from SC

    where  CJ<60 group by
BJDM,BNXH,XSXM

    或:  SelectBJDM,XSXM,min(CJ) from
SC

          group byBJDM,BNXH,XSXM

          havingmin(CJ)<60

 5、输出一张表格,有成绩低于60分的每位学生对应一条记录,包括字段:

         
班级代码、学生姓名、最高成绩、平均成绩。

    SelectBJDM,XSXM,max(CJ) from SC

    group byBJDM,BNXH,XSXM

    havingmin(CJ)<60

    请思考下列做法是否正确:

          SelectBJDM,XSXM,max(CJ),avg(CJ)
from SC

         where  CJ<60 group
byBJDM,BNXH,XSXM

 6、输出一张表格,所有成绩都不低于60分的每位学生对应一条记录,包括字段:

         
班级代码、学生姓名、平均成绩。

    SelectBJDM,XSXM,avg(CJ) from SC

    group by BJDM,BNXH,XSXM

    havingmin(CJ)>=60

 7、输出一张表格,每一位学生对应一条记录,包括字段:

         
班级代码、学生姓名、去掉一个最低分后的平均成绩。

   
SelectBJDM,XSXM,(sum(CJ)-min(CJ))/(count(*)-1) from SC

    group byBJDM,BNXH,XSXM

 8、输出一张表格,每门科目对应一条记录,包括字段:

         
科目、去掉一个最低分后的平均成绩。

    Select
KM,(sum(CJ)-min(CJ))/(count(*)-1)from SC

    group by KM

 

 

 

        实验指导中“八 SQL查询语句”
的答案

 

 1、查询年龄在19至21岁之间的女生的学号,姓名,年龄,按年龄从大到小排列。

    Selectsno,sname,sage from
student

    where sagebetween 19 and 21 and
ssex=’女’

    order by sagedesc

 2、查询姓名中有“明”字的学生人数。

    Select count(*)from student

    where snamelike “%明%”

 3、查询1001课程没有成绩的学生的学号。

    Select sno fromsc where cno=’1001′ and
grade is null

 4、查询JSJ、SX、WL系的学生学号,姓名,结果按系及学号排列。

    Selectsno,sname,sdept from
student

    where sdept in( ‘JSJ’, ‘SX’, ‘WL’
)

    order bysdept,sno

 5、计算每一门课的总分、平均分,最高分、最低分。

   
Selectcno,sum(grade),avg(grade),max(grade),min(grade)

    from sc

    group by cno

 6、查询平均分大于90分的男学生学号及平均分。

    连接:

    selectsc.sno,avg(grade) from
student,sc

    wherestudent.sno=sc.sno and
ssex=’男’

    group by sc.sno

    havingavg(grade)>90

    嵌套:

    selectsno,avg(grade) from sc

    where sno in (select sno from student
where ssex=’男’)

    group by sno

    havingavg(grade)>90

 7、查询选修课程超过2门的学生姓名。

    select snamefrom student,sc

    where student.sno=sc.sno

    group bysc.sno,sname

    havingcount(*)>2

    本题也可以用嵌套做

 8、查询 JSJ 系的学生选修的课程号。

    Select distinctcno from
student,sc

    where  student.sno=sc.sno and
sdept=’JSJ’

    本题也可以用嵌套做

 9、查询选修1002课程的学生的学生姓名(用连接和嵌套2种方法)

    连接:Select sname from student,sc

          wherestudent.sno=sc.sno and
cno=’1002′

    嵌套:Select sname from student where
sno in

              (select sno from sc where
cno=’1002′ )

10、查询学生姓名以及他选修课程的课程号及成绩。

    Selectsname,cno,grade from
student,sc

    wherestudent.sno=sc.sno

    思考本题也可以用嵌套做吗?

11、查询选修“数据库原理”课且成绩 80
以上的学生姓名(用连接和嵌套2种方法)

    连接:Select sname from
student,sc,course

          wherestudent.sno=sc.sno and
sc.cno=course.cno and

               cname=’数据库原理’ and
grade>80

    嵌套:Select sname from student where
sno in 

               (select sno from sc where
grade>80 and cno in 

                    ( select cno from
course where cname=’数据库原理’ )

               )

 

14、查询没有选修1002课程的学生的学生姓名。

    Select snamefrom student

    where sno notin ( select sno from sc
where cno=’1002′)

    或: select sname from student

         where notexists

              (select * from sc where
cno=’1002′ and sno=student.sno)

   
思考本题也可以用一般的连接做吗?

15、查询平均分最高的学生学号及平均分。

    Selectsno,avg(grade)

    from sc

    group by sno

    havingavg(grade) >=ALL ( Select
avg(grade)

                              from
sc

                              group by
sno

                            )

16、查询每门课程成绩都高于该门课程平均分的学生学号。

    可以先计算每门课程平均分

    create
viewc_avg(cno,avg_grade)

         as selectcno,avg(grade) from sc
group by cno

    再查询

    Select distinctsno from sc

    where sno notin ( Select sno from
sc,c_avg

                      where
sc.cno=c_avg.cno and grade<avg_grade

                    )

   ===========================================

    SELECT DISTINCT Sno

    FROM SC SC1

    WHERE SC1.SnoNOT IN

          ( SELECT SC2.Sno

            FROM SC SC2

            WHERE SC2.Grade <=

                 ( SELECT
AVG(SC3.Grade)

                   FROM SC SC3

                  
WHERE SC3.Cno=SC2.Cno

                 )

          )

    或:

    SELECT DISTINCTSno

    FROM SC SC1

    WHERE NOTEXISTS

          (SELECT *

            FROM SC SC2

            WHERE SC2.Sno=SC1.Sno AND
SC2.Grade <=

                       (SELECT
AVG(SC3.Grade)

                         FROM SC
SC3

                        
WHERE SC3.Cno=SC2.Cno

                       )

          )

   

 

(3)检索至少选修LIU老师所授课程中一门课程的女学生姓名。
    SELECT SNAME
    FROM S
    WHERE SEX=‘F’ AND S# IN
     (SELECT S#
     FROM SC
     WHERE C# IN
      (SELECT C#
      FROM C
      WHERE TEACHER=‘LIU’)

NOTICE:有多种写法,比如联接查询写法:
    SELECT SNAME
    FROM S,SC,C
    WHERE SEX=‘F’ AND SC.S#=S.S#
    AND SC.C#=C.C#
    AND TEACHER=’LIU’
但上一种写法更好一些。

(4)检索WANG同学不学的课程的课程号。
    SELECT C#
    FROM C
    WHERE C# NOT IN
     (SELECT C#
     FROM SC
     WHERE S# IN
      (SELECT S#
      FROM S
      WHERE SNAME=’WANG’))

(5)检索至少选修两门课程的学生学号。
    SELECT DISTINCT X.SNO
    FROM SC X,SC Y
    WHERE X.SNO=Y.SNO AND X.CNO<>Y.CNO
Notice:对表SC进行自连接,X,Y是SC的两个别名。

(6)检索全部学生都选修的课程的课程号与课程名。
    SELECT C#,CNAME
    FROM C
    WHERE NOT EXISTS
     (SELECT *
     FROM S
     WHERE S# NOT IN
      (SELECT *
      FROM SC
      WHERE  SC.C#=C.C#))

要从语义上分解:(1)选择课程的课程号与课程名,不存在不选这门课的同学。
   其中,“不选这门课的同学”可以表示为:

SELECT *

 FROM  S

 WHERE  S# NOT IN

  (SELECT  *

      FROM SC  

      WHERE  SC.C#=C.C#)

 

或者

SELECT *

 FROM  S

 WHERE   NOT EXISTS

  (SELECT  *

      FROM SC  

      WHERE S.S#=C.S# AND 

SC.C#=C.C# )

 

      

(7)检索选修课程包含LIU老师所授课的学生学号。    
     SELECT DISTINCT S#
     FROM SC
     WHERE C# IN
      (SELECT C#
      FROM C
      WHERE TEACHER=’LIU’))   

3.3 设有两个基本表R(A,B,C)和S(D,E,F),试用SQL查询语句表达下列关系代数表达式:
  (1)πA(R)(2)σB=’17’(R)(3)R×S(4))πA,FC=D(R×S))
(1)SELECT A FROM R
(2)SELECT * FROM R WHERE B=’17’
(3)SELECT A,B,C,D,E,F FROM R,S
(4)SELECT A,F FROM R,S WHERE R.C=S.D

3.43.4 设有两个基本表R(A,B,C)和S(A,B,C)试用SQL查询语句表达下列关系代数表达式:
  (1)R∪S  (2)R∩S  (3)R-S  (4)πA,B(R)πB,C(S)

    (1)SELECT A,B,C
      FROM R
      UNION
      SELECT A,B,C
      FROM S

    (2)SELECT A,B,C
      FROM R
      INTERSECT
      SELECT A,B,C
      FROM S

    (3)SELECT A,B,C
      FROM R
      WHERE NOT EXISTS
       (SELECT A,B,C
       FROM S
       WHERE R.A=S.A AND R.B=S.B AND R.C=S.C)

    (4)SELECT R.A,R.B,S.C
      FROM R,S
      WHERE R.B=S.B

3.5 试叙述SQL语言的关系代数特点和元组演算特点。
(P61-62)

3.6 试用SQL查询语句表达下列对教学数据库中三个基本表S、SC、C的查询:

(1)统计有学生选修的课程门数。
    SELECT COUNT(DISTINCT C#) FROM SC

(2)求选修C4课程的学生的平均年龄。
    SELECT AVG(AGE)
    FROM S
    WHERE S# IN
     (SELECT S#
     FROM SC
     WHERE C#=’C4′)
或者,
    SELECT AVG(AGE)
    FROM S,SC
    WHERE S.S#=SC.S# AND C#=’004′

(3)求LIU老师所授课程的每门课程的学生平均成绩。
   SELECT CNAME,AVG(GRADE)
   FROM SC ,C
   WHERE SC.C#=C.C# ANDTEACHER=’LIU’
   GROUP BY C#   

(4)统计每门课程的学生选修人数(超过10人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列。
    SELECT DISTINCT C#,COUNT(S#)
    FROM SC
    GROUP BY C#
    HAVING COUNT(S#)>10
    ORDER BY 2 DESC, C# ASC

(5)检索学号比WANG同学大,而年龄比他小的学生姓名。
    SELECT X.SNAME
    FROM S AS X, S AS Y
    WHERE Y.SNAME=’WANG’ AND X.S#>Y.S# AND X.AGE<Y.AGE

(6)检索姓名以WANG打头的所有学生的姓名和年龄。
    SELECT SNAME,AGE
    FROM S
    WHERE SNAME LIKE ‘WANG%’

(7)在SC中检索成绩为空值的学生学号和课程号。
    SELECT S#,C#
    FROM SC
    WHERE GRADE IS NULL

(8)求年龄大于女同学平均年龄的男学生姓名和年龄。
    SELECT SNAME,AGE
    FROM S AS X
    WHERE X.SEX=’男’ AND X.AGE>(SELECT AVG(AGE)FROM S AS Y WHERE
Y.SEX=’女’)

(9)求年龄大于所有女同学年龄的男学生姓名和年龄。
    SELECT SNAME,AGE
    FROM S AS X
    WHERE X.SEX=’男’ AND X.AGE>ALL (SELECT AGE FROM S AS Y WHERE
Y.SEX=’女’)
除法运算

3.7 试用SQL更新语句表达对教学数据库中三个基本表S、SC、C的各个更新操作:
(1)往基本表S中插入一个学生元组(‘S9’,‘WU’,18)。
    INSERT INTO S(S#,SNAME,AGE) VALUES(’59’,’WU’,18)
(2)在基本表S中检索每一门课程成绩都大于等于80分的学生学号、姓名和性别,并把检索到的值送往另一个已存在的基本表STUDENT(S#,SANME,SEX)。
    INSERT INTO STUDENT(S#,SNAME,SEX)
     SELECT S#,SNAME,SEX
     FROM S WHERE  NOT EXISTS
      (SELECT * FROM SC WHERE
       GRADE<80 AND S.S#=SC.S#)

(3)在基本表SC中删除尚无成绩的选课元组。
    DELETE FROM SC
     WHERE GRADE IS NULL

(4)把WANG同学的学习选课和成绩全部删去。
    DELETE FROM SC
     WHERE S# IN
      (SELECT S#
      FROM S
      WHERE SNAME=’WANG’)

(5)把选修MATHS课不及格的成绩全改为空值。
    UPDATE SC
    SET GRADE=NULL
    WHERE GRADE<60 AND C# IN
      (SELECT C#
      FROM C
      WHERE CNAME=’MATHS’)

(6)把低于总平均成绩的女同学成绩提高5%。
    UPDATE SC
    SET GRADE=GRADE*1.05
    WHERE GRADE<(SELECT AVG(GRADE) FROM SC) AND S# IN (SELECT S#
FROM SWHERE SEX=’F’)

(7)在基本表SC中修改C4课程的成绩,若成绩小于等于75分时提高5%,若成绩大于75分时提高4%(用两个UPDATE语句实现)。
    UPDATE SC
     SET GRADE=GRADE*1.05
     WHERE C#=’C4′ AND GRADE<=75
    UPDATE SC
     SET GRADE=GRADE*1.04
     WHERE C#=’C4′ AND GRADE>75

3.8 在第1章例1.4中提到“仓库管理”关系模型有五个关系模式:
  零件 PART(P#,PNAME,COLOR,WEIGHT)
  项目 PROJECT(J#,JNAME,DATE)
  供应商 SUPPLIER(S#,SNAME,SADDR)
  供应 P_P(J#,P#,TOTOAL)
  采购 P_S(P#,S#,QUANTITY)

(1)试用SQLDDL语句定义上述五个基本表,并说明主键和外键。
    CREATE TABLE PART
    (P# CHAR(4) NOT NULL,PNAME CHAR(12) NOT NULL,
    COLOR CHAR(10),WEIGHT REAL,
    PRIMARY KEY(P#))
    
    CREATE TABLE PROJECT
    (J# CHAR(4) NOT NULL,JNAME CHAR(12) NOT NULL,
    DATE DATE,
    PRIMARY KEY(J#))
    
    CREATE TABLE SUPLIER
    (S# CHAR(4) NOT NULL,SNAME CHAR(12),SADDR VARCHAR(20),
    PRIMARY KEY(S#))
    
    CREATE TABLE P_P
    (J# CHAR(4),P# CHAR(4),TOTAL INTEGER,
    PRIMARY KEY(J#,P#),
    FOREIGN KEY(J#) REFERENCE PROJECT(J#),
    FOREIGN KEY(P#) REFERENCE PART(P#))
    
    CREATE TABLE P_S
    (P# CHAR(4),S# CHAR(4),QUANTITY INTEGER,
    PRIMARY KEY(P#,S#),
    FOREIGN KEY(P#) REFERENCE PART(P#),
    FOREIGN KEY(S#) REFERENCE SUPLIER(S#))
    

(2)试将PROGECT、P_P、PART三个基本表的自然联接定义为一个视图VIEW1,PART、P_S、SUPPLIER三个基本表的自然联接定义为一个视图VIEW2。
    CREATE VIEW VIEW1(J#,JNAME,DATE,P#,PNAME,COLOR,WEIGHT,TOTAL)
          AS SELECT
PROJECT.J#,JNAME,DATE,PART.P#,PNAME,COLOR,WEIGHT,TOTAL
          FROM PROJECT,PART,P_P
          WHERE PART.P#=P_P.P# AND P_P.J#=PROJECT.J# 
     
    CREATE VIEW
VIEW2(P#,PNAME,COLOR,WEIGHT,S#,SNAME,SADDR,QUANTITY)
          AS SELECT
PART.P#,PNAME,COLOR,WEIGHT,SUPPLIER.S#,SNAME,SADDR,QUANTITY
          FROM PART,P_S,SUPPLIER
          WHERE PART.P#=P_S.P# AND P_S.S#=SUPPLIER.S#

(3)试在上述两个视图的基础上进行数据查询:

    1)检索上海的供应商所供应的零件的编号和名字。
    SELECT P#,PNAME FROM VIEW2 WHERE SADDR=’SHANGHAI’

    2)检索项目J4所用零件的供应商编号和名字。
    SELECT S#,SNAME FROM VIEW2 WHERE P# IN(SELECT P# FROM VIEW1
WHERE J#=’J4′)

3.9 对于教学数据库中基本表SC,已建立下列视图:
CREATEVIEWS_GRADE(S#,C_NUM,AVG_GRADE)
ASSELECTS#,COUNT(C#),AVG(GRADE)
FROMSC
GROUPBYS#
试判断下列查询和更新是否允许执行。若允许,写出转换到基本表SC上的相应操作。
  (1)
SELECT*
FROMS_GRADE
      允许
   SELECT S#,COUNT(C#),AVG(GRADE)FROM SC GROUP BY S#

  (2)
SELECTS#,C_NUM
FROMS_GRADE
WHEREAVG_GRADE>80
    允许
   SELECT S#,COUNT(C#) FROM SC WHEREAVG(GRADE)>80

  (3)
SELECTS#,AVG_GRADE
FROMS_GRADE
WHEREC_NUM>(SELECTC_NUM
FROMS_GRADE
WHERES#=‘S4’)
    允许
   SELECT S#,AVG(GRADE)
   FROM SC AS X
   WHERE COUNT(X.C#)>(SELECTCOUNT(Y.C#) FROM SC AS Y WHERE
Y.S#=’S4′)
   GROUP BY S#

  (4)
UPDATES_GRADE
SETC_NUM=C_NUM+1
WHERES#=‘S4’
    不允许

  (5)
DELETEFROMS_GRADE
WHEREC_NUM>4
    不允许

3.10 预处理方式对于嵌入式SQL的实现有什么重要意义?
   
预处理方式是先用预处理程序对源程序进行扫描,识别出SQL语句,并处理成宿主语言的函数调用形式;
然后再用宿主语言的编译程序把源程序编译成目标程序。这样,不用扩充宿主语言的编译程序,
就能处理SQL语句。

3.11 在宿主语言的程序中使用SQL语句有哪些规定?
在宿主语言的程序中使用SLQ语句有以下规定:
(1)在程序中要区分SQL语句与宿主语言语句
(2)允许嵌入的SQL语句引用宿主语言的程序变量(称为共享变量),但有两条规定:
   1)引用时,这些变量前必须加“:”作为前缀标识,以示与数据库中变量有区别。
   2)这些变量由宿主语言的程序定义,并用SQL的DECLARE语句说明。
(3)SQL的集合处理方式与宿主语言单记录处理方式之间要协调。
需要采用游标机制,把集合操作转换成单记录处理方式。

3.12SQL的集合处理方式与宿主语言单记录处理方式之间如何协调?
    由于SQL语句处理的是记录集合,而宿主语言语句一次只能处理一个记录,
因此需要用游标(cousor)机制,把集合操作转换成单记录处理方式。

2.13 嵌入式SQL语句何时不必涉及到游标?何时必须涉及到游标?
    (1)INSERT、DELETE、UPDATE语句,查询结果肯定是单元组时的SELECT语句,
都可直接嵌入在主程序中使用,不必涉及到游标。
    (2)当SELECT语句查询结果是多个元组时,此时宿主语言程序无法使用,
一定要用游标机制把多个元组一次一个地传送给宿主语言处理。

 

 

 

(电商)数据库原理及应用_模拟考核试题及参考答案

 

    一、单选 (每空1分,共10分)

    1.在Access数据库中,数据保存在(   
)对象中。

       A.窗体        B.查询         
C.报表          D.表

   
2.如果某一字段数据型为文本型,字段大小为8,该字段中最多可输入(   
)个汉字。

       A.8          B.4              C.16           D.32

    3.文本型字段最多可以存放(   
)个字符。

       A.250        B.252        
C.254           D.255

    4.Access用户操作界面由(   
)部分组成。

       A.4           B.5          C.3           D.6

    5.下列(   
)图标是Access中表对象的标志。

       A.         B.         C.        D.

   
6.在设计Access数据表时,“索引”属性有(    )取值。

       A.1              B.2            
 C.3
               D.4

    7.Access中包含有(   
)种数据类型。

       A.9              B.10             C.7                D.8

   
8.在一个宏中要打开一个报表,应该使用的操作是(    )。

       A.OpenForm       B.OpenReport      C.OpenTable      
D.OpenQuery

   
9.可以通过Internet进行数据发布的对象是(    )。

       A.窗体         B.报表          
C.查询          D.数据访问页

   10.模块窗口由(   
)个部分组成。

       A.2            B.3             C.4              D.5

 

    二、填空 (每空1分,共20分)

   
1.在人工管理和文件管理阶段,程序设计__依赖于 ___数据表示。

   
2.在文件系统中,存取数据的基本单位为___记录____,在数据库系统中,存取数据的基本单位为___数据项_____。

   
3.若实体A和B是多对多的联系,实体B和C是1对1的联系,则实体A和C是___多_____对___多_____的联系。

   
4.在一个关系中不允许出现重复的____元组____,也不允许出现具有相同名字的___属性_____。

   
5.数据库系统中的四类用户分别为____数据库管理员、数据库设计员、应用程序员、终端用户_____。

   
6.在存取数据库的数据的过程中,使用了两个数据缓冲区,分别为___系统_____缓冲区和____用户
____缓冲区。

   
7.学生关系中的班级号属性与班级关系中的班级号主码属性相对应,则____班级号____为学生关系中的___外码___。

   
8.设一个关系A具有a1个属性和a2个元组,关系B具有b1个属性和b2个元组,则关系A´B具有___a1+b1____个属性和____
a2´b2 ____个元组。

   
9.设一个学生关系为S(学生号,姓名),课程关系为C(课程号,课程名),选课关系为X(学生号,课程号,成绩),求出所有选课的学生信息的运算表达式为_____Õ学生号(X)______与____S
____的自然连接。

   10.在一个关系R中,若存在X→Y和X→Z,则存在_____
X→(Y,Z)_______,称此为函数依赖的合并性规则。

 

    三、填空 (每空1分,共20分)

   
1.若一个关系的任何非主属性都不部分依赖依赖于任何候选码,则称该关系达到____第二____范式。

   
2.在SQL中,列级完整性约束分为__6__种情况,表级完整性约束分为__4__种情况。

   3.
在SQL中,每个视图中的列可以来自不同的___表___,它是在原有表的基础上____建立____的逻辑意义上的新关系。

   4. 在SQL的查询语句中,group
by选项实现____分组统计______功能,order
by选项实现对结果表的____排序_____功能。

   
5.对于较复杂的系统,概念设计阶段的主要任务是:首先根据系统的各个局部应用画出各自对应的____局部ER图______,然后再进行综合和整体设计,画出_____整体ER图_____。

   
6.机器实现阶段的目标是在计算机系统中得到一个满足______设计要求、功能完善、操作方便___的数据库应用系统。

    7.Access的用户操作界面由    标题栏、菜单栏、工作区  、工具栏、状态栏等五个部分组成。

   
8.Access“表”结构设计窗口中上半部分的“表设计器”是由      字段名称、数据类型、说明        等三列组成。

    9.Access中的窗体由      页眉、主体      和页脚等三个部分组成。

 

    四、填空 (每空1分,共20分)

   1.
设一个关系为R(A,B,C,D,E),它的最小函数依赖集为FD={A→B,A→C,(C,D)→E},则该关系的候选码为_____(A,D)___,候选码函数决定E是___伪传递___性。

   
2.设一个关系为R(A,B,C,D,E),它的最小函数依赖集为FD={A→B,A→C,(A,D)→E},该关系只满足___第一_____范式,若要规范化为高一级的范式,则将得到____2____个关系。

   
3.在实际的数据库管理系统中,对数据库的操作方式有_____命令交互、程序执行、窗口界面______等三种。

    4.在SQL中,主码约束的关键字为____
primary key________,外码约束的关键字为______foreignkey
______。

   
5.基本表属于全局模式中的表,它是____实表____,而视图则属于局部模式中的表,它是____虚表
____。

   6.
在SQL新版的查询语句中,select选项实现投影运算,from选项实现____连接____运算,where选项实现____web前端,选择___运算。

   
7.数据字典是对系统工作流程中____数据____和____处理____的描述。

   
8.关系数据库系统中的全局模式由若干个基本表所组成,表与表之间的联系是通过定义的____主码____和____外码____实现的。

   
9.在旅店管理中,涉及到的基本表有四个,它们分别为客房表、住宿表、_____旅客登记表、消费卡表____。

   10.在设置或撤消数据库密码的过程中,密码对于字母     大小写     是敏感的。

 

    五、填空 (每空1分,共10分)

    1.Access的用户操作界面由   标题栏、菜单栏、工作区   、工具栏、状态栏等五个部分组成。

   
2.Access“表”结构设计窗口中上半部分的“表设计器”是由     字段名称、数据类型   和说明等三列组成。

    3.Access中的窗体由     页眉、主体、页脚      等三个部分组成。

    4.在Access中模块分为   类模块      
和       标准模块     两种类型。

 

   
六、根据主教材第四章所给的商品库和教学库,或者按照下列所给的每条SQL查询语句写出相应的功能,或者按照下列所给的每种功能写出相应的SQL查询语句。(每小题4分,共20分)

   
在名称为商品库的数据库中包含有商品表1和商品表2,它们的定义分别为:

       商品表1(商品代号 char(8),分类名 char(8),单价 float,数量
int)

       商品表2(商品代号 char(8),产地 char(6),品牌
char(6),)

   
在名称为教学库的数据库中包含有学生、课程和选课三个表,它们的定义分别为:

       学生(学生号 char(7),姓名 char(6),性别
char(2),出生日期 datetime,

            专业 char(10),年级 int)

       课程(课程号 char(4),课程名 char(10),课程学分
int

       选课(学生号 char(7),课程号 char(4),成绩 int)

    1.select distinct 产地

        from 商品表2

       功能:从商品库中查询出所有商品的不同产地。

 

    2.select *

        from 学生

        where 学生号 in (select
学生号

          from 选课

          group by 学生号 having
count(*)=1

        )

   
功能:从教学库中查询出只选修了一门课程的全部学生。

 

    3.select *

         from 学生

         where 学生号 in (select
学生号

           from 选课

           group by 学生号 having
count(*)<=2   

         ) or not exists (select *

              from 选课

              where
学生.学生号=选课.学生号

         )

    功能:
从教学库中查询出最多选修了2门课程(含未选任何课程)的全部学生。

 

   
4.从商品库中查询出每类(即分类名相同)商品的最高单价。

 select 分类名,max(单价) as 最高单价

        from 商品表1

        group by 分类名

 

   
5.从教学库中查询出至少选修了姓名为@m1学生所选课程中一门课的全部学生。

select distinct 学生.*

        from 学生,选课

        where 学生.学生号=选课.学生号 and
课程号=any(select 课程号

          from 学生,选课

          where 学生.学生号=选课.学生号
and 姓名=@m1

        )

 

 

 

 

 

1.  
Student(S#,Sname,Sage,Ssex) 学生表 

2.  
Course(C#,Cname,T#) 课程表 

3.  
SC(S#,C#,score) 成绩表 

4.  
Teacher(T#,Tname) 教师表 

5.  
 

6.  
问题: 

7.  
1、查询“001”课程比“002”课程成绩高的所有学生的学号; 

8.  
  select a.S# from (select s#,score from SC where C#=’001′) a,(select s#,score 

9.  
  from SC where C#=’002′) b 

10. 
  where a.score>b.score and a.s#=b.s#; 

11. 
2、查询平均成绩大于60分的同学的学号和平均成绩; 

12. 
    select S#,avg(score) 

13. 
    from sc 

14. 
    group by S# having avg(score) >60; 

15. 
3、查询所有同学的学号、姓名、选课数、总成绩; 

16. 
  select Student.S#,Student.Sname,count(SC.C#),sum(score) 

17. 
  from Student left Outer join SC on Student.S#=SC.S# 

18. 
  group by Student.S#,Sname 

19. 
4、查询姓“李”的老师的个数; 

20. 
  select count(distinct(Tname)) 

21. 
  from Teacher 

22. 
  where Tname like ‘李%’; 

23. 
5、查询没学过“叶平”老师课的同学的学号、姓名; 

24. 
    select Student.S#,Student.Sname 

25. 
    from Student  

26. 
    where S# not in (select distinct( SC.S#) from SC,Course,Teacher where  SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname=’叶平’); 

27. 
6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名; 

28. 
  select Student.S#,Student.Sname from Student,SC where Student.S#=SC.S# and SC.C#=’001’and exists( Select * from SC as SC_2 where SC_2.S#=SC.S# and SC_2.C#=’002′); 

29. 
7、查询学过“叶平”老师所教的所有课的同学的学号、姓名; 

30. 
  select S#,Sname 

31. 
  from Student 

32. 
  where S# in (select S# from SC ,Course ,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname=’叶平’ group by S# having count(SC.C#)=(select count(C#) from Course,Teacher  where Teacher.T#=Course.T# and Tname=’叶平’)); 

33. 
8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名; 

34. 
  Select S#,Sname from (select Student.S#,Student.Sname,score ,(select score from SC SC_2 where SC_2.S#=Student.S# and SC_2.C#=’002′) score2 

35. 
  from Student,SC where Student.S#=SC.S# and C#=’001′) S_2 where score2 <score; 

36. 
9、查询所有课程成绩小于60分的同学的学号、姓名; 

37. 
  select S#,Sname 

38. 
  from Student 

39. 
  where S# not in (select Student.S# from Student,SC where S.S#=SC.S# and score>60); 

40. 
10、查询没有学全所有课的同学的学号、姓名; 

41. 
    select Student.S#,Student.Sname 

42. 
    from Student,SC 

43. 
    where Student.S#=SC.S# group by  Student.S#,Student.Sname having count(C#) <(select count(C#) from Course); 

44. 
11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名; 

45. 
    select S#,Sname from Student,SC where Student.S#=SC.S# and C# in select C# from SC where S#=’1001′; 

46. 
12、查询至少学过学号为“001”同学所有一门课的其他同学学号和姓名; 

47. 
    select distinct SC.S#,Sname 

48. 
    from Student,SC 

49. 
    where Student.S#=SC.S# and C# in (select C# from SC where S#=’001′); 

50. 
13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩; 

51. 
    update SC set score=(select avg(SC_2.score) 

52. 
    from SC SC_2 

53. 
    where SC_2.C#=SC.C# ) from Course,Teacher where Course.C#=SC.C# and Course.T#=Teacher.T# and Teacher.Tname=’叶平’); 

54. 
14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名; 

55. 
    select S# from SC where C# in (select C# from SC where S#=’1002′) 

56. 
    group by S# having count(*)=(select count(*) from SC where S#=’1002′); 

57. 
15、删除学习“叶平”老师课的SC表记录; 

58. 
    Delect SC 

59. 
    from course ,Teacher  

60. 
    where Course.C#=SC.C# and Course.T#= Teacher.T# and Tname=’叶平’; 

61. 
16、向SC表中插入一些记录,这些记录要求符合以下条件:没有上过编号“003”课程的同学学号、2、 

62. 
    号课的平均成绩; 

63. 
    Insert SC select S#,’002′,(Select avg(score) 

64. 
    from SC where C#=’002′) from Student where S# not in (Select S# from SC where C#=’002′); 

65. 
17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩,按如下形式显示: 学生ID,,数据库,企业管理,英语,有效课程数,有效平均分 

66. 
    SELECT S# as 学生ID 

67. 
        ,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#=’004′) AS 数据库 

68. 
        ,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#=’001′) AS 企业管理 

69. 
        ,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#=’006′) AS 英语 

70. 
        ,COUNT(*) AS 有效课程数, AVG(t.score) AS 平均成绩 

71. 
    FROM SC AS t 

72. 
    GROUP BY S# 

73. 
    ORDER BY avg(t.score)  

74. 
18、查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分 

75. 
    SELECT L.C# As 课程ID,L.score AS 最高分,R.score AS 最低分 

76. 
    FROM SC L ,SC AS R 

77. 
    WHERE L.C# = R.C# and 

78. 
        L.score = (SELECT MAX(IL.score) 

79. 
                      FROM SC AS IL,Student AS IM 

80. 
                      WHERE L.C# = IL.C# and IM.S#=IL.S# 

81. 
                      GROUP BY IL.C#) 

82. 
        AND 

83. 
        R.Score = (SELECT MIN(IR.score) 

84. 
                      FROM SC AS IR 

85. 
                      WHERE R.C# = IR.C# 

86. 
                  GROUP BY IR.C# 

87. 
                    ); 

88. 
19、按各科平均成绩从低到高和及格率的百分数从高到低顺序 

89. 
    SELECT t.C# AS 课程号,max(course.Cname)AS 课程名,isnull(AVG(score),0) AS 平均成绩 

90. 
        ,100 * SUM(CASE WHEN  isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格百分数 

91. 
    FROM SC T,Course 

92. 
    where t.C#=course.C# 

93. 
    GROUP BY t.C# 

94. 
    ORDER BY 100 * SUM(CASE WHEN  isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) DESC 

95. 
20、查询如下课程平均成绩和及格率的百分数(用”1行”显示): 企业管理(001),马克思(002),OO&UML (003),数据库(004) 

96. 
    SELECT SUM(CASE WHEN C# =’001′ THEN score ELSE 0 END)/SUM(CASE C# WHEN ‘001’ THEN 1 ELSE 0 END) AS 企业管理平均分 

97. 
        ,100 * SUM(CASE WHEN C# = ‘001’ AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = ‘001’ THEN 1 ELSE 0 END) AS 企业管理及格百分数 

98. 
        ,SUM(CASE WHEN C# = ‘002’ THEN score ELSE 0 END)/SUM(CASE C# WHEN ‘002’ THEN 1 ELSE 0 END) AS 马克思平均分 

99. 
        ,100 * SUM(CASE WHEN C# = ‘002’ AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = ‘002’ THEN 1 ELSE 0 END) AS 马克思及格百分数 

100.                ,SUM(CASE WHEN C# = ‘003’ THEN score ELSE 0 END)/SUM(CASE C# WHEN ‘003’ THEN 1 ELSE 0 END) AS UML平均分 

101.                ,100 * SUM(CASE WHEN C# = ‘003’ AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = ‘003’ THEN 1 ELSE 0 END) AS UML及格百分数 

102.                ,SUM(CASE WHEN C# = ‘004’ THEN score ELSE 0 END)/SUM(CASE C# WHEN ‘004’ THEN 1 ELSE 0 END) AS 数据库平均分 

103.                ,100 * SUM(CASE WHEN C# = ‘004’ AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = ‘004’ THEN 1 ELSE 0 END) AS 数据库及格百分数 

104.          FROM SC 

105.        21、查询不同老师所教不同课程平均分从高到低显示 

106.          SELECT max(Z.T#) AS 教师ID,MAX(Z.Tname) AS 教师姓名,C.C# AS 课程ID,MAX(C.Cname) AS 课程名称,AVG(Score) AS 平均成绩 

107.            FROM SC AS T,Course AS C ,Teacher AS Z 

108.            where T.C#=C.C# and C.T#=Z.T# 

109.          GROUP BY C.C# 

110.          ORDER BY AVG(Score) DESC 

111.        22、查询如下课程成绩第 3 名到第 6 名的学生成绩单:企业管理(001),马克思(002),UML (003),数据库(004) 

112.            [学生ID],[学生姓名],企业管理,马克思,UML,数据库,平均成绩 

113.            SELECT  DISTINCT top 3 

114.              SC.S# As 学生学号, 

115.                Student.Sname AS 学生姓名 , 

116.              T1.score AS 企业管理, 

117.              T2.score AS 马克思, 

118.              T3.score AS UML, 

119.              T4.score AS 数据库, 

120.              ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) as 总分 

121.              FROM Student,SC  LEFT JOIN SC AS T1 

122.                              ON SC.S# = T1.S# AND T1.C# = ‘001’ 

123.                    LEFT JOIN SC AS T2 

124.                              ON SC.S# = T2.S# AND T2.C# = ‘002’ 

125.                    LEFT JOIN SC AS T3 

126.                              ON SC.S# = T3.S# AND T3.C# = ‘003’ 

127.                    LEFT JOIN SC AS T4 

128.                              ON SC.S# = T4.S# AND T4.C# = ‘004’ 

129.              WHERE student.S#=SC.S# and 

130.              ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) 

131.              NOT IN 

132.              (SELECT 

133.                    DISTINCT 

134.                    TOP 15 WITH TIES 

135.                    ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) 

136.              FROM sc 

137.                    LEFT JOIN sc AS T1 

138.                              ON sc.S# = T1.S# AND T1.C# = ‘k1’ 

139.                    LEFT JOIN sc AS T2 

140.                              ON sc.S# = T2.S# AND T2.C# = ‘k2’ 

141.                    LEFT JOIN sc AS T3 

142.                              ON sc.S# = T3.S# AND T3.C# = ‘k3’ 

143.                    LEFT JOIN sc AS T4 

144.                              ON sc.S# = T4.S# AND T4.C# = ‘k4’ 

145.              ORDER BY ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) DESC); 

146.         

147.        23、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60] 

148.            SELECT SC.C# as 课程ID, Cname as 课程名称 

149.                ,SUM(CASE WHEN score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS [100 – 85] 

150.                ,SUM(CASE WHEN score BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS [85 – 70] 

151.                ,SUM(CASE WHEN score BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS [70 – 60] 

152.                ,SUM(CASE WHEN score < 60 THEN 1 ELSE 0 END) AS [60 -] 

153.            FROM SC,Course 

154.            where SC.C#=Course.C# 

155.            GROUP BY SC.C#,Cname; 

156.         

157.        24、查询学生平均成绩及其名次 

158.              SELECT 1+(SELECT COUNT( distinct 平均成绩) 

159.                      FROM (SELECT S#,AVG(score) AS 平均成绩 

160.                              FROM SC 

161.                          GROUP BY S# 

162.                          ) AS T1 

163.                    WHERE 平均成绩 > T2.平均成绩) as 名次, 

164.              S# as 学生学号,平均成绩 

165.            FROM (SELECT S#,AVG(score) 平均成绩 

166.                    FROM SC 

167.                GROUP BY S# 

168.                ) AS T2 

169.            ORDER BY 平均成绩 desc; 

170.          

171.        25、查询各科成绩前三名的记录:(不考虑成绩并列情况) 

172.              SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数 

173.              FROM SC t1 

174.              WHERE score IN (SELECT TOP 3 score 

175.                      FROM SC 

176.                      WHERE t1.C#= C# 

177.                    ORDER BY score DESC 

178.                      ) 

179.              ORDER BY t1.C#; 

180.        26、查询每门课程被选修的学生数 

181.          select c#,count(S#) from sc group by C#; 

182.        27、查询出只选修了一门课程的全部学生的学号和姓名 

183.          select SC.S#,Student.Sname,count(C#) AS 选课数 

184.          from SC ,Student 

185.          where SC.S#=Student.S# group by SC.S# ,Student.Sname having count(C#)=1; 

186.        28、查询男生、女生人数 

187.            Select count(Ssex) as 男生人数 from Student group by Ssex having Ssex=’男’; 

188.            Select count(Ssex) as 女生人数 from Student group by Ssex having Ssex=’女’; 

189.        29、查询姓“张”的学生名单 

190.            SELECT Sname FROM Student WHERE Sname like ‘张%’; 

191.        30、查询同名同性学生名单,并统计同名人数 

192.          select Sname,count(*) from Student group by Sname having  count(*)>1;; 

193.        31、1981年出生的学生名单(注:Student表中Sage列的类型是datetime) 

194.            select Sname,  CONVERT(char (11),DATEPART(year,Sage)) as age 

195.            from student 

196.            where  CONVERT(char(11),DATEPART(year,Sage))=’1981′; 

197.        32、查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列 

198.            Select C#,Avg(score) from SC group by C# order by Avg(score),C# DESC ; 

199.        33、查询平均成绩大于85的所有学生的学号、姓名和平均成绩 

200.            select Sname,SC.S# ,avg(score) 

201.            from Student,SC 

202.            where Student.S#=SC.S# group by SC.S#,Sname having    avg(score)>85; 

203.        34、查询课程名称为“数据库”,且分数低于60的学生姓名和分数 

204.            Select Sname,isnull(score,0) 

205.            from Student,SC,Course 

206.            where SC.S#=Student.S# and SC.C#=Course.C# and  Course.Cname=’数据库’and score <60; 

207.        35、查询所有学生的选课情况; 

208.            SELECT SC.S#,SC.C#,Sname,Cname 

209.            FROM SC,Student,Course 

210.            where SC.S#=Student.S# and SC.C#=Course.C# ; 

211.        36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数; 

212.            SELECT  distinct student.S#,student.Sname,SC.C#,SC.score 

213.            FROM student,Sc 

214.            WHERE SC.score>=70 AND SC.S#=student.S#; 

215.        37、查询不及格的课程,并按课程号从大到小排列 

216.            select c# from sc where scor e <60 order by C# ; 

217.        38、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名; 

218.            select SC.S#,Student.Sname from SC,Student where SC.S#=Student.S# and Score>80 and C#=’003′; 

219.        39、求选了课程的学生人数 

220.            select count(*) from sc; 

221.        40、查询选修“叶平”老师所授课程的学生中,成绩最高的学生姓名及其成绩 

222.            select Student.Sname,score 

223.            from Student,SC,Course C,Teacher 

224.            where Student.S#=SC.S# and SC.C#=C.C# and C.T#=Teacher.T# and Teacher.Tname=’叶平’ and SC.score=(select max(score)from SC where C#=C.C# ); 

225.        41、查询各个课程及相应的选修人数 

226.            select count(*) from sc group by C#; 

227.        42、查询不同课程成绩相同的学生的学号、课程号、学生成绩 

228.          select distinct  A.S#,B.score from SC A  ,SC B where A.Score=B.Score and A.C# <>B.C# ; 

229.     43、查询每门功课成绩最好的前两名 

230.            SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数 

231.              FROM SC t1 

232.              WHERE score IN (SELECT TOP 2 score 

233.                      FROM SC 

234.                      WHERE t1.C#= C# 

235.                    ORDER BY score DESC 

236.                      ) 

237.              ORDER BY t1.C#; 

238.        44、统计每门课程的学生选修人数(超过10人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列,查询结果按人数降序排列,若人数相同,按课程号升序排列  

239.            select  C# as 课程号,count(*) as 人数 

240.            from  sc  

241.            group  by  C# 

242.            order  by  count(*) desc,c#  

243.        45、检索至少选修两门课程的学生学号 

244.            select  S#  

245.            from  sc  

246.            group  by  s# 

247.            having  count(*)  >  =  2 

248.        46、查询全部学生都选修的课程的课程号和课程名 

249.            select  C#,Cname  

250.            from  Course  

251.            where  C#  in  (select  c#  from  sc group  by  c#)  

252.        47、查询没学过“叶平”老师讲授的任一门课程的学生姓名 

253.            select Sname from Student where S# not in (select S# from Course,Teacher,SC where Course.T#=Teacher.T# and SC.C#=course.C# and Tname=’叶平’); 

254.        48、查询两门以上不及格课程的同学的学号及其平均成绩 

255.            select S#,avg(isnull(score,0)) from SC where S# in (select S# from SC where score <60 group by S# having count(*)>2)group by S#; 

256.        49、检索“004”课程分数小于60,按分数降序排列的同学学号 

257.            select S# from SC where C#=’004’and score <60 order by score desc; 

258.        50、删除“002”同学的“001”课程的成绩 

259.        delete from Sc where S#=’001’and C#=’001′; 

260.         

 

 

 

 

 

 

 

 

 

 

模拟考核试题参考答案

 

    一、单选 (每空1分,共10分)

   1. D  2. B  3. D 4. B  5. C  6. C 7. B

  1. B 9. D  10. B

二、填空 (每空1分,共20分)

 

   1. 依赖于                 2.
记录、数据项

   3. 多、多                 4.
元组、属性

   5.
数据库管理员、数据库设计员、应用程序员、终端用户(次序无先后)

   6. 系统、用户             7.
班级号、外码

   8. a1+b1、a2´b2           9.
Õ学生号(X)、S  (次序无先后)

    10. X→(Y,Z)

三、填空 (每空1分,共20分)

 

   1. 第二                           
2.6、4

   3. 表、建立                        4.
分组统计、排序

   5. 局部ER图、整体ER图            6.
设计要求、功能完善、操作方便

   7.
标题栏、菜单栏、工作区(次序无先后)

   8.
字段名称、数据类型、说明(次序无先后)

   9. 页眉、主体(次序无先后)

四、填空 (每空1分,共20分)

 

   1. (A,D)、伪传递                     

  1. 第一、2

   3.
命令交互、程序执行、窗口界面(次序无先后)

   4. primary key、foreign key

   5. 实表、虚表                        

  1. 连接、选择

   7. 数据、处理(次序无先后)          

  1. 主码、外码(次序无先后)

   9. 旅客登记表、消费卡表(次序无先后)

  1. 大小写

五、填空 (每空1分,共10分)

 

   1. 标题栏、菜单栏、工作区  
(次序无先后)

   2. 字段名称、数据类型      
(次序无先后)

   3. 页眉、主体、页脚        
(次序无先后)

  1. 类模块、标准模块        
    (次序无先后)

 

   
六、根据主教材第四章所给的商品库和教学库,或者按照下列所给的每条SQL查询语句写出相应的功能,或者按照下列所给的每种功能写出相应的SQL查询语句。(每小题4分,共20分)

   1.
从商品库中查询出所有商品的不同产地。

   2.
从教学库中查询出只选修了一门课程的全部学生。

   3.
从教学库中查询出最多选修了2门课程(含未选任何课程)的全部学生。

   4. select 分类名,max(单价) as 最高单价

        from 商品表1

        group by 分类名

   5. select distinct 学生.*

        from 学生,选课

        where 学生.学生号=选课.学生号 and
课程号=any(select 课程号

          from 学生,选课

          where 学生.学生号=选课.学生号
and 姓名=@m1

        )

 

3.1 名词解释

(1)SQL模式:SQL模式是表和授权的静态定义。一个SQL模式定义为基本表的集合。
一个由模式名和模式拥有者的用户名或账号来确定,并包含模式中每一个元素(基本表、视图、索引等)的定义。
(2)SQL数据库:SQL(Structured Query
Language),即‘结构式查询语言’,采用英语单词表示和结构式的语法规则。
一个SQL数据库是表的汇集,它用一个或多个SQL模式定义。
(3)基本表:在SQL中,把传统的关系模型中的关系模式称为基本表(Base
Table)。 基本表是实际存储在数据库中的表,对应一个关系。
(4)存储文件:在SQL中,把传统的关系模型中的存储模式称为存储文件(Stored
File)。 每个存储文件与外部存储器上一个物理文件对应。
(5)视图:在SQL中,把传统的关系模型中的子模式称为视图(View),视图是从若干基本表和(或)其他视图构造出来的表。
(6):在SQL中,把传统的关系模型中的元组称为行(row)。
(7)列:在SQL中,把传统的关系模型中的属性称为列(coloumn)。
(8)实表:基本表被称为“实表”,它是实际存放在数据库中的表。
(9)虚表:视图被称为“虚表”,创建一个视图时,只把视图的定义存储在数据词典中,而不存储视图所对应的数据。
(10)相关子查询:在嵌套查询中出现的符合以下特征的子查询:子查询中查询条件依赖于外层查询中的某个值,
所以子查询的处理不只一次,要反复求值,以供外层查询使用。
(11)联接查询:查询时先对表进行笛卡尔积操作,然后再做等值联接、选择、投影等操作。
联接查询的效率比嵌套查询低。
(12)交互式SQL:在终端交互方式下使用的SQL语言称为交互式SQL。
(13)嵌入式SQL:嵌入在高级语言的程序中使用的SQL语言称为嵌入式SQL。
(14)共享变量:SQL和宿主语言的接口。共享变量有宿主语言程序定义,再用SQL的DECLARE语句说明,
SQL语句就可引用这些变量传递数据库信息。
(15)游标:游标是与某一查询结果相联系的符号名,用于把集合操作转换成单记录处理方式。
(16)卷游标:为了克服游标在推进时不能返回的不便,SQL2提供了卷游标技术。
卷游标在推进时不但能沿查询结果中元组顺序从头到尾一行行推进,也能一行行返回。

3.2 对于教学数据库的三个基本表
  学生S(S#,SNAME,AGE,SEX)
  学习 SC(S#,C#,GRADE)
  课程C(C#,CNAME,TEACHER)
 试用SQL的查询语句表达下列查询:

(1)检索LIU老师所授课程的课程号和课程名。
    SELECT C#,CNAME
    FROM C
    WHERE TEACHER=‘LIU’

(2)检索年龄大于23岁的男学生的学号和姓名。
    SELECT S#,SNAME
    FROM S
    WHERE (AGE>23) AND (SEX=‘M’)

(3)检索至少选修LIU老师所授课程中一门课程的女学生姓名。
    SELECT SNAME
    FROM S
    WHERE SEX=‘F’ AND S# IN
     (SELECT S#
     FROM SC
     WHERE C# IN
      (SELECT C#
      FROM C
      WHERE TEACHER=‘LIU’)

NOTICE:有多种写法,比如联接查询写法:
    SELECT SNAME
    FROM S,SC,C
    WHERE SEX=‘F’ AND SC.S#=S.S#
    AND SC.C#=C.C#
    AND TEACHER=’LIU’
但上一种写法更好一些。

(4)检索WANG同学不学的课程的课程号。
    SELECT C#
    FROM C
    WHERE C# NOT IN
     (SELECT C#
     FROM SC
     WHERE S# IN
      (SELECT S#
      FROM S
      WHERE SNAME=’WANG’))

(5)检索至少选修两门课程的学生学号。
    SELECT DISTINCT X.SNO
    FROM SC X,SC Y
    WHERE X.SNO=Y.SNO AND X.CNO<>Y.CNO
Notice:对表SC进行自连接,X,Y是SC的两个别名。

(6)检索全部学生都选修的课程的课程号与课程名。
    SELECT C#,CNAME
    FROM C
    WHERE NOT EXISTS
     (SELECT *
     FROM S
     WHERE S# NOT IN
      (SELECT *
      FROM SC
      WHERE  SC.C#=C.C#))

要从语义上分解:(1)选择课程的课程号与课程名,不存在不选这门课的同学。
   其中,“不选这门课的同学”可以表示为:

SELECT *

 FROM  S

 WHERE  S# NOT IN

  (SELECT  *

      FROM SC  

      WHERE  SC.C#=C.C#)

 

或者

SELECT *

 FROM  S

 WHERE   NOT EXISTS

  (SELECT  *

      FROM SC  

      WHERE S.S#=C.S# AND 

SC.C#=C.C# )

 

      

(7)检索选修课程包含LIU老师所授课的学生学号。    
     SELECT DISTINCT S#
     FROM SC
     WHERE C# IN
      (SELECT C#
      FROM C
      WHERE TEACHER=’LIU’))   

3.3 设有两个基本表R(A,B,C)和S(D,E,F),试用SQL查询语句表达下列关系代数表达式:
  (1)πA(R)(2)σB=’17’(R)(3)R×S(4))πA,FC=D(R×S))
(1)SELECT A FROM R
(2)SELECT * FROM R WHERE B=’17’
(3)SELECT A,B,C,D,E,F FROM R,S
(4)SELECT A,F FROM R,S WHERE R.C=S.D

3.43.4 设有两个基本表R(A,B,C)和S(A,B,C)试用SQL查询语句表达下列关系代数表达式:
  (1)R∪S  (2)R∩S  (3)R-S  (4)πA,B(R)πB,C(S)

    (1)SELECT A,B,C
      FROM R
      UNION
      SELECT A,B,C
      FROM S

    (2)SELECT A,B,C
      FROM R
      INTERSECT
      SELECT A,B,C
      FROM S

    (3)SELECT A,B,C
      FROM R
      WHERE NOT EXISTS
       (SELECT A,B,C
       FROM S
       WHERE R.A=S.A AND R.B=S.B AND R.C=S.C)

    (4)SELECT R.A,R.B,S.C
      FROM R,S
      WHERE R.B=S.B

3.5 试叙述SQL语言的关系代数特点和元组演算特点。
(P61-62)

3.6 试用SQL查询语句表达下列对教学数据库中三个基本表S、SC、C的查询:

(1)统计有学生选修的课程门数。
    SELECT COUNT(DISTINCT C#) FROM SC

(2)求选修C4课程的学生的平均年龄。
    SELECT AVG(AGE)
    FROM S
    WHERE S# IN
     (SELECT S#
     FROM SC
     WHERE C#=’C4′)
或者,
    SELECT AVG(AGE)
    FROM S,SC
    WHERE S.S#=SC.S# AND C#=’004′

(3)求LIU老师所授课程的每门课程的学生平均成绩。
   SELECT CNAME,AVG(GRADE)
   FROM SC ,C
   WHERE SC.C#=C.C# ANDTEACHER=’LIU’
   GROUP BY C#   

(4)统计每门课程的学生选修人数(超过10人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列。
    SELECT DISTINCT C#,COUNT(S#)
    FROM SC
    GROUP BY C#
    HAVING COUNT(S#)>10
    ORDER BY 2 DESC, C# ASC

(5)检索学号比WANG同学大,而年龄比他小的学生姓名。
    SELECT X.SNAME
    FROM S AS X, S AS Y
    WHERE Y.SNAME=’WANG’ AND X.S#>Y.S# AND X.AGE<Y.AGE

(6)检索姓名以WANG打头的所有学生的姓名和年龄。
    SELECT SNAME,AGE
    FROM S
    WHERE SNAME LIKE ‘WANG%’

(7)在SC中检索成绩为空值的学生学号和课程号。
    SELECT S#,C#
    FROM SC
    WHERE GRADE IS NULL

(8)求年龄大于女同学平均年龄的男学生姓名和年龄。
    SELECT SNAME,AGE
    FROM S AS X
    WHERE X.SEX=’男’ AND X.AGE>(SELECT AVG(AGE)FROM S AS Y WHERE
Y.SEX=’女’)

(9)求年龄大于所有女同学年龄的男学生姓名和年龄。
    SELECT SNAME,AGE
    FROM S AS X
    WHERE X.SEX=’男’ AND X.AGE>ALL (SELECT AGE FROM S AS Y WHERE
Y.SEX=’女’)

3.7 试用SQL更新语句表达对教学数据库中三个基本表S、SC、C的各个更新操作:
(1)往基本表S中插入一个学生元组(‘S9’,‘WU’,18)。
    INSERT INTO S(S#,SNAME,AGE) VALUES(’59’,’WU’,18)
(2)在基本表S中检索每一门课程成绩都大于等于80分的学生学号、姓名和性别,并把检索到的值送往另一个已存在的基本表STUDENT(S#,SANME,SEX)。
    INSERT INTO STUDENT(S#,SNAME,SEX)
     SELECT S#,SNAME,SEX
     FROM S WHERE  NOT EXISTS
      (SELECT * FROM SC WHERE
       GRADE<80 AND S.S#=SC.S#)

(3)在基本表SC中删除尚无成绩的选课元组。
    DELETE FROM SC
     WHERE GRADE IS NULL

(4)把WANG同学的学习选课和成绩全部删去。
    DELETE FROM SC
     WHERE S# IN
      (SELECT S#
      FROM S
      WHERE SNAME=’WANG’)

(5)把选修MATHS课不及格的成绩全改为空值。
    UPDATE SC
    SET GRADE=NULL
    WHERE GRADE<60 AND C# IN
      (SELECT C#
      FROM C
      WHERE CNAME=’MATHS’)

(6)把低于总平均成绩的女同学成绩提高5%。
    UPDATE SC
    SET GRADE=GRADE*1.05
    WHERE GRADE<(SELECT AVG(GRADE) FROM SC) AND S# IN (SELECT S#
FROM SWHERE SEX=’F’)

(7)在基本表SC中修改C4课程的成绩,若成绩小于等于75分时提高5%,若成绩大于75分时提高4%(用两个UPDATE语句实现)。
    UPDATE SC
     SET GRADE=GRADE*1.05
     WHERE C#=’C4′ AND GRADE<=75
    UPDATE SC
     SET GRADE=GRADE*1.04
     WHERE C#=’C4′ AND GRADE>75

3.8 在第1章例1.4中提到“仓库管理”关系模型有五个关系模式:
  零件 PART(P#,PNAME,COLOR,WEIGHT)
  项目 PROJECT(J#,JNAME,DATE)
  供应商 SUPPLIER(S#,SNAME,SADDR)
  供应 P_P(J#,P#,TOTOAL)
  采购 P_S(P#,S#,QUANTITY)

(1)试用SQLDDL语句定义上述五个基本表,并说明主键和外键。
    CREATE TABLE PART
    (P# CHAR(4) NOT NULL,PNAME CHAR(12) NOT NULL,
    COLOR CHAR(10),WEIGHT REAL,
    PRIMARY KEY(P#))
    
    CREATE TABLE PROJECT
    (J# CHAR(4) NOT NULL,JNAME CHAR(12) NOT NULL,
    DATE DATE,
    PRIMARY KEY(J#))
    
    CREATE TABLE SUPLIER
    (S# CHAR(4) NOT NULL,SNAME CHAR(12),SADDR VARCHAR(20),
    PRIMARY KEY(S#))
    
    CREATE TABLE P_P
    (J# CHAR(4),P# CHAR(4),TOTAL INTEGER,
    PRIMARY KEY(J#,P#),
    FOREIGN KEY(J#) REFERENCE PROJECT(J#),
    FOREIGN KEY(P#) REFERENCE PART(P#))
    
    CREATE TABLE P_S
    (P# CHAR(4),S# CHAR(4),QUANTITY INTEGER,
    PRIMARY KEY(P#,S#),
    FOREIGN KEY(P#) REFERENCE PART(P#),
    FOREIGN KEY(S#) REFERENCE SUPLIER(S#))
    

(2)试将PROGECT、P_P、PART三个基本表的自然联接定义为一个视图VIEW1,PART、P_S、SUPPLIER三个基本表的自然联接定义为一个视图VIEW2。
    CREATE VIEW VIEW1(J#,JNAME,DATE,P#,PNAME,COLOR,WEIGHT,TOTAL)
          AS SELECT
PROJECT.J#,JNAME,DATE,PART.P#,PNAME,COLOR,WEIGHT,TOTAL
          FROM PROJECT,PART,P_P
          WHERE PART.P#=P_P.P# AND P_P.J#=PROJECT.J# 
     
    CREATE VIEW
VIEW2(P#,PNAME,COLOR,WEIGHT,S#,SNAME,SADDR,QUANTITY)
          AS SELECT
PART.P#,PNAME,COLOR,WEIGHT,SUPPLIER.S#,SNAME,SADDR,QUANTITY
          FROM PART,P_S,SUPPLIER
          WHERE PART.P#=P_S.P# AND P_S.S#=SUPPLIER.S#

(3)试在上述两个视图的基础上进行数据查询:

    1)检索上海的供应商所供应的零件的编号和名字。
    SELECT P#,PNAME FROM VIEW2 WHERE SADDR=’SHANGHAI’

    2)检索项目J4所用零件的供应商编号和名字。
    SELECT S#,SNAME FROM VIEW2 WHERE P# IN(SELECT P# FROM VIEW1
WHERE J#=’J4′)

3.9 对于教学数据库中基本表SC,已建立下列视图:
CREATEVIEWS_GRADE(S#,C_NUM,AVG_GRADE)
ASSELECTS#,COUNT(C#),AVG(GRADE)
FROMSC
GROUPBYS#
试判断下列查询和更新是否允许执行。若允许,写出转换到基本表SC上的相应操作。
  (1)
SELECT*
FROMS_GRADE
      允许
   SELECT S#,COUNT(C#),AVG(GRADE)FROM SC GROUP BY S#

  (2)
SELECTS#,C_NUM
FROMS_GRADE
WHEREAVG_GRADE>80
    允许
   SELECT S#,COUNT(C#) FROM SC WHEREAVG(GRADE)>80

  (3)
SELECTS#,AVG_GRADE
FROMS_GRADE
WHEREC_NUM>(SELECTC_NUM
FROMS_GRADE
WHERES#=‘S4’)
    允许
   SELECT S#,AVG(GRADE)
   FROM SC AS X
   WHERE COUNT(X.C#)>(SELECTCOUNT(Y.C#) FROM SC AS Y WHERE
Y.S#=’S4′)
   GROUP BY S#

  (4)
UPDATES_GRADE
SETC_NUM=C_NUM+1
WHERES#=‘S4’
    不允许

  (5)
DELETEFROMS_GRADE
WHEREC_NUM>4
    不允许

3.10 预处理方式对于嵌入式SQL的实现有什么重要意义?
   
预处理方式是先用预处理程序对源程序进行扫描,识别出SQL语句,并处理成宿主语言的函数调用形式;
然后再用宿主语言的编译程序把源程序编译成目标程序。这样,不用扩充宿主语言的编译程序,
就能处理SQL语句。

3.11 在宿主语言的程序中使用SQL语句有哪些规定?
在宿主语言的程序中使用SLQ语句有以下规定:
(1)在程序中要区分SQL语句与宿主语言语句
(2)允许嵌入的SQL语句引用宿主语言的程序变量(称为共享变量),但有两条规定:
   1)引用时,这些变量前必须加“:”作为前缀标识,以示与数据库中变量有区别。
   2)这些变量由宿主语言的程序定义,并用SQL的DECLARE语句说明。
(3)SQL的集合处理方式与宿主语言单记录处理方式之间要协调。
需要采用游标机制,把集合操作转换成单记录处理方式。

3.12SQL的集合处理方式与宿主语言单记录处理方式之间如何协调?
    由于SQL语句处理的是记录集合,而宿主语言语句一次只能处理一个记录,
因此需要用游标(cousor)机制,把集合操作转换成单记录处理方式。

2.13 嵌入式SQL语句何时不必涉及到游标?何时必须涉及到游标?
    (1)INSERT、DELETE、UPDATE语句,查询结果肯定是单元组时的SELECT语句,
都可直接嵌入在主程序中使用,不必涉及到游标。
    (2)当SELECT语句查询结果是多个元组时,此时宿主语言程序无法使用,
一定要用游标机制把多个元组一次一个地传送给宿主语言处理。

 

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图
Copyright @ 2010-2019 澳门新葡亰官网app 版权所有