我的学习空间

26套

逐题详细解析

第1题：为gname字段设置唯一约束

ALTER TABLE tb_group 
ADD CONSTRAINT gname UNIQUE (gname);

解析：

  • ALTER TABLE tb_group：修改社团信息表
  • ADD CONSTRAINT gname：添加名为 gname 的约束
  • UNIQUE (gname)：指定 gname 字段为唯一约束

其他写法：

-- 不指定约束名（系统自动生成）
ALTER TABLE tb_group ADD UNIQUE (gname);

-- 创建唯一索引
ALTER TABLE tb_group ADD UNIQUE INDEX idx_gname (gname);

第2题：建立降序索引

CREATE INDEX idx_stu ON tb_student (sname DESC);

解析：

  • CREATE INDEX idx_stu：创建名为 idx_stu 的索引
  • ON tb_student：指定学生表
  • (sname DESC)：按学生姓名降序排列

其他写法：

-- 使用 ALTER TABLE
ALTER TABLE tb_student ADD INDEX idx_stu (sname DESC);

第3题：查询参加话剧社的学生

SELECT s.sno, s.sname, gs.joindate, gs.role
FROM tb_student s
INNER JOIN tb_gs gs ON s.sno = gs.sno
INNER JOIN tb_group g ON gs.gno = g.gno
WHERE g.gname = '话剧社';

解析：

  • 三表连接：学生表 → 学生社团表 → 社团表
  • INNER JOIN：获取有社团记录的学生
  • WHERE g.gname = '话剧社'：筛选话剧社成员

输出字段：学号、姓名、参加社团时间、职务

保存：将语句保存到 sj13.txt 文件

其他写法：

-- 使用传统连接
SELECT s.sno, s.sname, gs.joindate, gs.role
FROM tb_student s, tb_gs gs, tb_group g
WHERE s.sno = gs.sno 
  AND gs.gno = g.gno 
  AND g.gname = '话剧社';

第4题：创建视图查询社团g101的信息

CREATE VIEW v_club AS
SELECT g.gname AS 社团名称, COUNT(gs.sno) AS 参加总人数
FROM tb_group g
LEFT JOIN tb_gs gs ON g.gno = gs.gno
WHERE g.gno = 'g101'
GROUP BY g.gno, g.gname;

解析：

  • CREATE VIEW v_club：创建名为 v_club 的视图
  • AS 社团名称：字段别名
  • AS 参加总人数：字段别名
  • LEFT JOIN：确保社团有成员（人数为0也显示）
  • WHERE g.gno = 'g101'：只查询社团编号为 g101 的社团

简化写法（如果不需要GROUP BY）：

CREATE VIEW v_club AS
SELECT g.gname AS 社团名称, COUNT(gs.sno) AS 参加总人数
FROM tb_group g
LEFT JOIN tb_gs gs ON g.gno = gs.gno
WHERE g.gno = 'g101';

第5题：授予用户创建用户的权限

GRANT CREATE USER 
ON *.* 
TO '张民'@'localhost';

解析：

  • GRANT CREATE USER：授予创建用户的权限
  • ON *.*：对所有数据库的所有表生效（全局权限）
  • TO '张民'@'localhost'：指定用户和主机名

注意事项：

  • 创建用户权限是全局权限，必须在 *.* 上授予
  • 如果用户不存在，需要先创建：
CREATE USER '张民'@'localhost' IDENTIFIED BY 'password';

其他授权方式：

-- 授予所有权限（包含创建用户）
GRANT ALL PRIVILEGES ON *.* TO '张民'@'localhost' WITH GRANT OPTION;

-- 查看用户权限
SHOW GRANTS FOR '张民'@'localhost';

答案汇总

题号答案
1ALTER TABLE tb_group ADD CONSTRAINT gname UNIQUE (gname);
2CREATE INDEX idx_stu ON tb_student (sname DESC);
3SELECT s.sno, s.sname, gs.joindate, gs.role FROM tb_student s INNER JOIN tb_gs gs ON s.sno = gs.sno INNER JOIN tb_group g ON gs.gno = g.gno WHERE g.gname = '话剧社';
4CREATE VIEW v_club AS SELECT g.gname AS 社团名称, COUNT(gs.sno) AS 参加总人数 FROM tb_group g LEFT JOIN tb_gs gs ON g.gno = gs.gno WHERE g.gno = 'g101' GROUP BY g.gno, g.gname;
5GRANT CREATE USER ON *.* TO '张民'@'localhost';

知识点总结

题号考点关键语法
1唯一约束ALTER TABLE ... ADD CONSTRAINT ... UNIQUE
2降序索引CREATE INDEX ... ON ... (字段 DESC)
3多表连接查询INNER JOIN ... ON ... WHERE
4创建视图CREATE VIEW ... AS SELECT ... LEFT JOIN ... WHERE
5用户权限GRANT CREATE USER ON *.* TO ...
DELIMITER %%
CREATE TRIGGER tr_gs_insert BEFORE INSERT
ON _______ FOR EACH ROW
BEGIN
	IF (NOT EXISTS (SELECT * FROM tb_gs WHERE sno =NEW.sno AND gno IN (SELECT gno FROM tb_group WHERE gname _____ ( '合唱团' , '舞蹈团')))) THEN
		UPDATE tb_group SET remainder = remainder - 1 WHERE gno=____________;
	ELSE _______ NEW.sno=NULL;
	END IF;
END %%
DELIMITER ;

触发器 tr_gs_insert 逐空详细解析

第1个空：tb_gs

ON _______ FOR EACH ROW

答案：tb_gs

解析：

  • 触发器绑定的表名
  • 这是一个 BEFORE INSERT 触发器，在插入学生参加社团记录之前执行
  • 绑定在 tb_gs（学生参加社团信息表）上

第2个空：IN

WHERE gname _____ ( '合唱团' , '舞蹈团')

答案：IN

解析：

  • 判断社团名称是否在指定列表中
  • IN 表示在集合中
  • 这里要判断社团是否是“合唱团”或“舞蹈团”

逻辑说明：

SELECT gno FROM tb_group WHERE gname IN ('合唱团', '舞蹈团')
-- 查询合唱团和舞蹈团的社团编号

第3个空：NEW.gno

UPDATE tb_group SET remainder = remainder - 1 WHERE gno=____________;

答案：NEW.gno

解析：

  • NEW.gno 是新插入记录中的社团编号
  • 将该社团的学生名额（remainder）减1
  • 表示新增一名成员

第4个空：SET

ELSE _______ NEW.sno=NULL;

答案：SET

解析：

  • SET 是赋值语句的关键字
  • 如果条件不满足（学生已经参加过另一个社团），则将该记录的学号设为 NULL
  • 学号不能为 NULL，这样会导致插入失败，从而达到阻止插入的目的

完整代码

DELIMITER %%
CREATE TRIGGER tr_gs_insert BEFORE INSERT
ON tb_gs FOR EACH ROW
BEGIN
    IF (NOT EXISTS (SELECT * FROM tb_gs WHERE sno = NEW.sno AND gno IN (SELECT gno FROM tb_group WHERE gname IN ('合唱团', '舞蹈团')))) THEN
        UPDATE tb_group SET remainder = remainder - 1 WHERE gno = NEW.gno;
    ELSE 
        SET NEW.sno = NULL;
    END IF;
END %%
DELIMITER ;

触发器功能说明

功能：学生只能参加合唱团或舞蹈团中的一个

执行逻辑：

1. 用户尝试插入一条学生参加社团的记录
2. 检查该学生是否已经参加过合唱团或舞蹈团
   ↓
3. 如果学生从未参加过这两个社团（NOT EXISTS为真）
   → 允许插入，并将该社团的学生名额减1
   ↓
4. 如果学生已经参加过合唱团或舞蹈团（NOT EXISTS为假）
   → 将新记录的学号设为NULL，阻止插入

逻辑详解

条件判断

NOT EXISTS (SELECT * FROM tb_gs WHERE sno = NEW.sno AND gno IN (SELECT gno FROM tb_group WHERE gname IN ('合唱团', '舞蹈团')))
条件含义
gname IN ('合唱团', '舞蹈团')查找合唱团和舞蹈团的编号
gno IN (子查询结果)判断是否是这两个社团
WHERE sno = NEW.sno AND gno IN (...)该学生是否参加过这两个社团
NOT EXISTS如果不存在（没参加过），返回真

示例说明

场景1：学生第一次参加合唱团

步骤结果
检查是否参加过合唱团/舞蹈团未参加过 → NOT EXISTS 为真
触发器行为允许插入，社团名额减1

场景2：学生已参加合唱团，再参加舞蹈团

步骤结果
检查是否参加过合唱团/舞蹈团已参加过 → NOT EXISTS 为假
触发器行为SET NEW.sno = NULL，插入失败

答案汇总

空位答案含义
第1空tb_gs触发器绑定的表
第2空IN在集合中
第3空NEW.gno新插入的社团编号
第4空SET赋值关键字
DELIMITER $$
CREATE PROCEDURE sp_gs( IN ___________ VARCHAR(20))
BEGIN 
	DECLARE num TINYINT;
	DECLARE ssno CHAR(10);
	SELECT COUNT(*) INTO num FROM tb_gs, tb_group WHERE tb_gs.gno = tb_group.gno AND gname = groupname;
	IF (num = 1) THEN
		SELECT sno INTO ssno FROM tb_gs, tb_group WHERE tb_gs.gno = tb_group.gno AND gname = groupname;
		DELETE FROM tb_gs WHERE sno = ______ AND gno = (SELECT  gno FROM tb_group WHERE gname = groupname);
	END IF; 
	____________ tb_group SET remainder = __________________ WHERE gname = groupname;
END $$
DELIMITER ;

存储过程 sp_gs 逐空详细解析

第1个空：groupname

CREATE PROCEDURE sp_gs( IN ___________ VARCHAR(20))

答案：groupname

解析：

  • 存储过程的输入参数名
  • 类型为 VARCHAR(20)，用于接收社团名称
  • 参数名需要与后面 WHERE 条件中的变量名一致

第2个空：ssno

DELETE FROM tb_gs WHERE sno = ______ AND gno = (SELECT gno FROM tb_group WHERE gname = groupname);

答案：ssno

解析：

  • ssno 是前面声明的变量，用于存储要删除的学生学号
  • 当人数为1时，先查询出该学生的学号存入 ssno
  • 然后删除该学生的社团记录

第3个空：UPDATE

____________ tb_group SET remainder = __________________ WHERE gname = groupname;

答案：UPDATE

解析：

  • 更新表的关键字
  • 无论人数是否为1，都需要更新社团的剩余名额
  • 将社团的剩余名额恢复（复位）

第4个空：remainder + 1

SET remainder = __________________

答案：remainder + 1

解析：

  • 将社团的剩余名额增加1（复位）
  • 因为之前取消了学生参加该社团，所以名额需要恢复
  • 如果人数小于2，删除记录后，名额加1

完整代码

DELIMITER $$
CREATE PROCEDURE sp_gs(IN groupname VARCHAR(20))
BEGIN 
    DECLARE num TINYINT;
    DECLARE ssno CHAR(10);
    SELECT COUNT(*) INTO num FROM tb_gs, tb_group WHERE tb_gs.gno = tb_group.gno AND gname = groupname;
    IF (num = 1) THEN
        SELECT sno INTO ssno FROM tb_gs, tb_group WHERE tb_gs.gno = tb_group.gno AND gname = groupname;
        DELETE FROM tb_gs WHERE sno = ssno AND gno = (SELECT gno FROM tb_group WHERE gname = groupname);
    END IF; 
    UPDATE tb_group SET remainder = remainder + 1 WHERE gname = groupname;
END $$
DELIMITER ;

存储过程功能说明

功能：统计社团人数，如果人数为1，则取消该学生的参加，并恢复社团名额

执行逻辑：

1. 接收输入参数 groupname（社团名称）
2. 统计该社团的成员人数 → num
3. 判断 num 是否等于 1
   ↓
4. 如果 num = 1：
   - 查询该学生的学号 → ssno
   - 删除该学生的社团记录
   ↓
5. 无论人数是否为1，都将社团剩余名额加1（复位）

示例说明

假设数据：

gnognameremainder
G01合唱团10
snogno
S001G01

调用存储过程：

CALL sp_gs('合唱团');

执行过程：

  1. 统计合唱团人数 → num = 1
  2. 查询学生学号 → ssno = ‘S001’
  3. 删除该学生的社团记录
  4. 更新社团剩余名额：remainder = 10 + 1 = 11

结果：

  • 学生 S001 退出合唱团
  • 合唱团名额恢复为 11

答案汇总

空位答案含义
第1空groupname输入参数名（社团名称）
第2空ssno要删除的学生学号变量
第3空UPDATE更新表关键字
第4空remainder + 1剩余名额加1
<html>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312" />
<head><title>班级参加社团情况查询</title>
<style type="text/css">	
<!--
.STYLE1 {font-size: 20px; font-family: "楷体";}
-->
</style>
</head>
<body >
	<br><br>
	<div align="center"><font face="楷体" size="5" color="#008000">
					<b>班级参加社团情况查询</b></font></div><br><br>
<form name="frm" method="post">
<table width="400" align="center">
<tr><td><span class="STYLE1">班级编号：</span></td>
	<td><input type="text" name="ClassNo" size="30"></td>
	<td><input type="submit" name="select" value="提交"></td></tr>
</table>
</form>

<?php
$host="localhost"; $user="root"; $pwd="";
$db_name="db_club";
//**********found**********
$conn=_________________($host,$user,$pwd ) 
	or die("连接数据库服务器失败。".mysql_error());
//**********found**********
mysql_select_db ($db_name,________)
	or die("连接数据库失败。".mysql_error());
mysql_query("set names 'gb2312'");
?>

<?php
if(isset($_POST['select']))
{
	$ClassNo=$_POST['ClassNo'];
	if(!$ClassNo)
//**********found**********
		echo "<script> ________('输入不正确！')</script>;";
	else
	{
		echo "<br>";
		echo "<table width=500 border=2 align=center>";
		echo "<tr><td>姓名</td><td>性别</td><td>籍贯</td><td>社团名称</td><td>参加社团时间</td><td>承担的工作</td></tr>";
		$sql1="select sname,sex,native,gname,joindate, role from tb_student s,tb_gs gs,tb_group g where gs.sno=s.sno AND gs.gno=g.gno AND classno ='$ClassNo'";
//**********found**********
		$result1=_____________($sql1);
		while($row1=mysql_fetch_row($result1))
		  {list($SNAME,$SEX,$NATIVE,$GNAME,$JOINDATE,$ROLE)=$row1;
		   echo "<tr><td>$SNAME</td><td>$SEX</td><td>$NATIVE</td><td>$GNAME</td><td>$JOINDATE</td><td>$ROLE</td></tr>";
  		   }
		echo "</table>";		
		echo "<br><br>";
		echo "<table width=500 border=5 align=center>";
		echo "<tr><td>参加社团总人数</td><td>参加社团总个数</td></tr>";
//**********found**********
		$sql2="select count(___________ sname), count(gname) from tb_student s,tb_gs gs,tb_group g where gs.sno=s.sno AND gs.gno=g.gno AND classno ='$ClassNo'";
		$result2=mysql_query($sql2);
		while($row2=mysql_fetch_row($result2))
			 {list($SUM,$TOTAL)=$row2;
			  echo "<tr><td>$SUM</td><td>$TOTAL</td></tr>";
			  }
		echo "</table>";
	}
}
?>

</body>
</html>

PHP程序逐空详细解析

第1个空：mysql_connect

$conn=_________________($host,$user,$pwd )

答案：mysql_connect

解析：

  • PHP连接MySQL数据库的函数
  • 语法：mysql_connect(服务器地址, 用户名, 密码)
  • 返回数据库连接资源，存储在 $conn 变量中

第2个空：$conn

mysql_select_db ($db_name,________)

答案：$conn

解析：

  • mysql_select_db() 的第二个参数是数据库连接资源
  • 需要传入之前建立的连接变量 $conn

第3个空：alert

echo "<script> ________('输入不正确！')</script>;";

答案：alert

解析：

  • JavaScript的弹窗函数
  • 当用户没有输入班级编号时，弹出警告框
  • 语法：alert('消息内容')

第4个空：mysql_query

$result1=_____________($sql1);

答案：mysql_query

解析：

  • 执行SQL查询的函数
  • 语法：mysql_query(SQL语句)
  • 这里执行 $sql1 查询学生社团详细信息

第5个空：DISTINCT

$sql2="select count(___________ sname), count(gname) from tb_student s,tb_gs gs,tb_group g where gs.sno=s.sno AND gs.gno=g.gno AND classno ='$ClassNo'";

答案：DISTINCT

解析：

  • 去重关键字
  • COUNT(DISTINCT sname)：统计该班级中参加社团的不同学生人数
  • 一个学生可能参加多个社团，不加 DISTINCT 会重复计数

统计说明：

统计项SQL含义
参加社团总人数COUNT(DISTINCT sname)该班级有多少个不同的学生参加了社团
参加社团总个数COUNT(gname)该班级学生参加社团的总次数（含重复）

完整代码

<html>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312" />
<head><title>班级参加社团情况查询</title>
<style type="text/css">    
<!--
.STYLE1 {font-size: 20px; font-family: "楷体";}
-->
</style>
</head>
<body >
    <br><br>
    <div align="center"><font face="楷体" size="5" color="#008000">
                    <b>班级参加社团情况查询</b></font></div><br><br>
<form name="frm" method="post">
<table width="400" align="center">
<tr><td><span class="STYLE1">班级编号：</span></td>
    <td><input type="text" name="ClassNo" size="30"></td>
    <td><input type="submit" name="select" value="提交"></td>
</tr>
</table>
</form>

<?php
$host="localhost"; $user="root"; $pwd="";
$db_name="db_club";
//**********found**********
$conn=mysql_connect($host,$user,$pwd ) 
    or die("连接数据库服务器失败。".mysql_error());
//**********found**********
mysql_select_db ($db_name,$conn)
    or die("连接数据库失败。".mysql_error());
mysql_query("set names 'gb2312'");
?>

<?php
if(isset($_POST['select']))
{
    $ClassNo=$_POST['ClassNo'];
    if(!$ClassNo)
//**********found**********
        echo "<script> alert('输入不正确！')</script>;";
    else
    {
        echo "<br>";
        echo "<table width=500 border=2 align=center>";
        echo "<tr><th>姓名</th><th>性别</th><th>籍贯</th><th>社团名称</th><th>参加社团时间</th><th>承担的工作</th></tr>";
        $sql1="select sname,sex,native,gname,joindate, role from tb_student s,tb_gs gs,tb_group g where gs.sno=s.sno AND gs.gno=g.gno AND classno ='$ClassNo'";
//**********found**********
        $result1=mysql_query($sql1);
        while($row1=mysql_fetch_row($result1))
          {list($SNAME,$SEX,$NATIVE,$GNAME,$JOINDATE,$ROLE)=$row1;
           echo "<tr><td>$SNAME</td><td>$SEX</td><td>$NATIVE</td><td>$GNAME</td><td>$JOINDATE</td><td>$ROLE</td></tr>";
             }
        echo "</table>";        
        echo "<br><br>";
        echo "<table width=500 border=5 align=center>";
        echo "<tr><th>参加社团总人数</th><th>参加社团总个数</th></tr>";
//**********found**********
        $sql2="select count(distinct sname), count(gname) from tb_student s,tb_gs gs,tb_group g where gs.sno=s.sno AND gs.gno=g.gno AND classno ='$ClassNo'";
        $result2=mysql_query($sql2);
        while($row2=mysql_fetch_row($result2))
             {list($SUM,$TOTAL)=$row2;
              echo "<tr><td>$SUM</td><td>$TOTAL</td></tr>";
              }
        echo "</table>";
    }
}
?>

</body>
</html>

程序功能说明

功能：查询指定班级学生参加社团的情况

执行流程：

1. 用户输入班级编号，点击"提交"
2. 查询该班级学生的社团详细信息（姓名、性别、籍贯、社团名称、参加时间、职务）
3. 统计该班级的：
   - 参加社团总人数（不同学生数）
   - 参加社团总个数（总记录数）

答案汇总

空位答案说明
第1空mysql_connect连接数据库函数
第2空$conn数据库连接资源
第3空alertJavaScript弹窗函数
第4空mysql_query执行SQL查询函数
第5空DISTINCT去重关键字
xuankin

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注