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>"; ?>
其实仔细观察PHP的语法，你发现并不复杂，而且很有逻辑性，所以我们仔细阅读就可以较好的掌握到其答题的方法！
