MySQL数据库程序设计套题⑨【真题截图+原理解析】-阿里云开发者社区

MySQL数据库程序设计套题⑨【真题截图+原理解析】

2022-07-08 189

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

本文涉及的产品

RDS MySQL DuckDB 分析主实例，集群系列 4核8GB

简介： MySQL数据库程序设计套题【真题截图+原理解析】

DELIMITER $$
CREATE _________ sp_emp (OUT avgsalary FLOAT)--producer
BEGIN 
DECLARE ename VARCHAR(12); 
DECLARE edu VARCHAR(10); 
DECLARE esalary FLOAT;
DECLARE totalsalary FLOAT; 
DECLARE count SMALLINT;
DECLARE done INT DEFAULT -1;
DECLARE cur_emp CURSOR FOR 
SELECT name, education, salary
FROM tb_emp INNER JOIN tb_dept 
ON tb_emp.deptno=tb_dept.deptno AND deptname= '市场部';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
SET totalsalary=0;
SET count=0;
OPEN cur_emp;
FETCH cur_emp INTO ename, edu, esalary;
WHILE (done!=1) DO
  SELECT ename, edu, esalary;
  SET totalsalary=totalsalary+___________________;--esalary
  SET count=count+1;
  FETCH cur_emp INTO ename, edu, esalary;
END WHILE;
IF (count>0) THEN
  SET avgsalary= totalsalary/count;
ELSE
  SET avgsalary=0;
END IF;
CLOSE _________;--cur_emp 
END $$
DELIMITER ;

<html>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312" />
<head><title>员工管理统计信息</title>
</head>
</html>
<?php
$host="localhost"; $user="root"; $pwd="";
$db_name="db_yggl";
$conn=mysql_connect($host,$user,$pwd) 
  or die("连接数据库服务器失败。".mysql_error());
mysql_select_db($db_name,$conn)
  or die("连接数据库失败。".mysql_error());
mysql_query("set names 'gb2312'");
$cmd="select * from tb_emp";
$data=mysql_query($cmd);
//**********found**********
$rec_count=mysql_num_rows(_______);
echo "<table width=220 border=1 align=center>";
echo "<caption>员工情况表</caption>";
//**********found**********
echo "<tr><td>总人数</td><td>___________________</td></tr>";
$sql="select * from tb_emp where sex='男'";
$result=mysql_query($sql);
$count_m=mysql_num_rows($result);
//**********found**********
$count_f=$rec_count-_____________;
echo "<tr><td>男</td><td>$count_m</td></tr>";
echo "<tr><td>女</td><td>$count_f</td></tr>";
echo "</table><br><br>";
echo "<table width=460 border=1 align=center>";
echo "<caption>部门统计信息<br>";
echo "<tr><td>部门名称</td><td>员工人数</td><td>平均年龄</td><td>平均工资</td></tr>";
$sql="SELECT deptname, COUNT(*),AVG(YEAR(CURDATE())-birth),AVG(salary) FROM tb_emp,tb_dept ";  
//**********found**********
$sql=$sql."WHERE tb_emp.deptno=tb_dept.deptno GROUP BY ______________";
$result=mysql_query($sql);
while($row=mysql_fetch_row($result))
  {list($BM,$RS,$PJNL,$PJGZ)=$row;
   echo "<tr><td>$BM</td><td>$RS</td><td>$PJNL</td><td>$PJGZ</td></tr>";
  }
//**********found**********
$sql="SELECT COUNT(*),AVG(YEAR(CURDATE())-birth),AVG(salary) FROM tb_emp WHERE tb_emp.deptno ____________";
$result=mysql_query($sql); 
$row=mysql_fetch_row($result);
list($RS,$PJNL,$PJGZ)=$row;
echo "<tr><td>未分配</td><td>$RS</td><td>$PJNL</td><td>$PJGZ</td></tr>";
echo "</table>";
?>