首页 > 编程札记 > 编程

实验08 PHP操作MySQL

阅读： 评论：0

实验08 PHP操作MySQL

实验08 PHP操作MySQL

实验08 PHP操作MySQL

实验目的

(1)掌握MySQL建库建表和查询的方法； (2)掌握创建过程式数据库对象的方法；
(3)掌握PHP操作MySQL数据库的方法； (4)了解SQL注入漏洞(攻击)与防范。

实验内容

一、建库建表：理解并运行SY8_1_CREATE-TABLE.php和SY8_1_INSERT.php两个文件。

SY8_1_CREATE-TABLE.php

!DOCTYPE html>
<html>
<head><meta charset="utf-8"><title>建数据库和表</title>
</head>
<body>
<?php
$conn=mysqli_connect('localhost','root','111') or die('连接失败');
mysqli_query($conn,"set names utf8");
//创建数据库
$sql = "CREATE DATABASE PXSCJ";
if ($conn->query($sql) === TRUE) {echo "数据库创建成功";
} else {echo "数据库创建失败: " . $conn->error;
}// 创建表
mysqli_select_db($conn,"PXSCJ") or die('选择数据库失败');
$sql = 'CREATE TABLE XSB (
XH char(6) not null primary key,
XM  char(8) not null,
XB  tinyint(1) null default 1,
CSSJ date null,
ZY  char(12) null,
ZXF int(4)  null  default  0,
BZ text null
)';
if ($conn->query($sql) === TRUE) {echo "表XSB创建成功";
} else {echo "表XSB创建失败: " . $conn->error;
}$sql = 'CREATE TABLE KCB (
KCH char(3) not null primary key,
KCM char(16) not null,
KKSQ tinyint(1) null default 1,
XS tinyint(1) null,
XF tinyint(1) not null
)';
if ($conn->query($sql) === TRUE) {echo "表KCB创建成功";
} else {echo "表KCB创建失败: " . $conn->error;
}$sql = 'CREATE TABLE CJB (
XH char(6) not null,
KCH char(3) not null,
CJ  int(4) null,
primary key(XH,KCH)
)';
if ($conn->query($sql) === TRUE) {echo "表CJB创建成功";
} else {echo "表CJB创建失败: " . $conn->error;
}$conn->close();
?>
</body>
</html>

运行结果：

SY8_1_INSERT.php

<!DOCTYPE html>
<html>
<head><meta charset="utf-8"><title>插入表的数据</title>
</head>
<body><?php
$conn=mysqli_connect('localhost','root','111') or die('连接失败');
mysqli_query($conn,"set names utf8");
mysqli_select_db($conn,"PXSCJ") or die('选择数据库失败');
$sql .= "INSERT INTO XSB values('181101','王林',default,'2000-02-10','计算机',50,null),
('181102','程明',default,'2001-02-01','计算机',50,null),
('181103','王燕',0,'1999-10-06','计算机',50,null),
('181104','韦严严',default,'2000-08-26','计算机',50,null),
('181106','李方方',default,'2000-11-20','计算机',50,null),
('181107','李明',default,'2000-05-01','计算机',54,'提前修完《数据结构》，并获学分'),
('181108','林一凡',default,'1999-08-05','计算机',52,'已提前修完一门课'),
('181109','张强明',default,'1999-08-11','计算机',50,null),
('181110','张蔚',0,'2001-07-22','计算机',50,'三好学生'),
('181111','赵琳',0,'2000-03-18','计算机',50,null),
('181113','严红',0,'1999-08-11','计算机',48,'有一门课不及格，待补考'),
('181201','王敏',default,'1999-06-10','通信工程',42,null),
('181202','王林',default,'1999-01-29','通信工程',40,'有一门课不及格，待补考'),
('181203','王玉民',default,'2000-03-26','通信工程',42,null),
('181204','马琳琳',0,'1999-02-10','通信工程',42,null),
('181206','李计',default,'1999-09-20','通信工程',42,null),
('181210','李红庆',default,'1999-05-01','通信工程',44,'已提前修完一门课，并获学分'),
('181216','孙祥欣',default,'1999-03-19','通信工程',42,null),
('181218','孙研',default,'2000-10-09','通信工程',42,null),
('181220','吴薇华',0,'2000-03-18','通信工程',42,null),
('181221','刘燕敏',0,'1999-11-12','通信工程',42,null),
('181241','罗林琳',0,'2000-01-30','通信工程',50,'转专业学习');
INSERT INTO KCB values('101','计算机基础',1,80,5),
('102','程序设计与语言',2,68,4),
('206','离散数学',4,68,4),
('208','数据结构',5,68,4),
('210','计算机原理',5,85,5),
('209','操作系统',6,68,4),
('212','数据库原理',7,68,4),
('301','计算机网络',7,51,3),
('302','软件工程',7,51,3);
INSERT INTO CJB values('181101','101',80),
('181101','102',78),
('181101','206',76),
('181103','101',62),
('181103','102',70),
('181103','206',81),
('181104','101',90),
('181104','102',84),
('181104','206',65),
('181102','102',78),
('181107','101',78),
('181107','102',80),
('181107','206',68),
('181108','101',85),
('181108','102',64),
('181108','206',87),
('181109','101',66),
('181109','102',83),
('181109','206',70),
('181110','101',95),
('181111','206',76),
('181113','101',63),
('181113','102',79),
('181113','206',60),
('181201','101',80),
('181202','101',65),
('181203','101',87),
('181204','101',91),
('181210','101',76),
('181216','101',81),
('181102','206',78),
('181106','101',65),
('181106','102',71),
('181106','206',80),
('181110','102',90),
('181110','206',89),
('181111','101',91),
('181111','102',70),
('181218','101',70),
('181220','101',82),
('181221','101',76),
('181241','101',90);";
if (mysqli_multi_query($conn, $sql)) {echo "数据插入成功";
} else {echo "数据插入失败: " . $sql . "<br>" . mysqli_error($conn);
}
$conn->close();
?></body>
</html>


二、完成教材P259例9.2(文件命名为SY8_2_FETCH-ROW.php)–基本操作

<!DOCTYPE html>
<html>
<head><meta charset="utf-8"><title>学生信息</title>
</head>
<body>
<?php
$conn=mysqli_connect('localhost','root','111') or die('连接失败');
mysqli_select_db($conn,"PXSCJ") or die('选择数据库失败');
mysqli_query($conn,"set names utf8");
$sql="select * from XSB where XB=0";
$result=mysqli_query($conn,$sql);
if ($result) {echo "SQL 语句执行成功！";
}
echo "<table border=1 align='center'>";
echo "<tr><td>学号</td><td>姓名</td><td>总学分</td></tr>";
while ($row=mysqli_fetch_row($result)) 
{list($XH,$XM,$XB,$CSSJ,$ZY,$ZXF,$BZ)=$row;echo "<tr><td>$XH</td><td>$XM</td><td>$ZXF</td></tr>";
}
echo "</table>";
?>
</body>
</html>

运行结果：

三、分页显示(文件命名为SY8_3_PageCount.php)–一页显示三条记录

<!DOCTYPE html>
<html>
<head><meta charset="utf-8"><title>课程列表</title>
</head>
<body>
<center><font size="5" face="华文楷体" color="blue">PHP分页显示</font></center>
<div align="center">
<form action="" method="post"><?php$conn=mysql_connect("localhost","root","111") or die('连接失败');mysql_select_db('PXSCJ',$conn) or die('选择数据库失败');mysql_query("SET NAMES utf8");$result=mysql_query("select * from KCB order by KCH asc");$row=mysql_fetch_row($result);$num=$row[0];$page_count=ceil($num/3);if (empty($_GET['page'])) {$page_curr==1; } 	else $page_curr=$_GET['page'];?><table border="1"><tr><td>课程号</td><td>课程名</td><td>开学时期</td><td>学时</td><td>学分</td><td>编辑</td><td>删除</td></tr><?php                $result=mysql_query("select *from kcb order by KCH asc limit ".(($page_curr-1)*3).",3");if ($result) {echo "SQL 语句执行成功！";}while ($row=mysql_fetch_row($result)) {?><tr><td><?php echo $row[0];?></td> <td><?php echo $row[1];?></td><td><?php echo $row[2];?></td> <td><?php echo $row[3];?></td><td><?php echo $row[4];?></td><td><a href="">编辑</a></td>   <td><a href="">删除</a></td></tr><?php } ?></table><br><a href="SY8_3_PageCount.php?page=1">首页</a><a href="SY8_3_PageCount.php?page=<?php echo $page_curr<=1?1:($page-1)?>">上一页</a><a href="SY8_3_PageCount.php?page=<?php echo $page_curr>=$page_count?$page_count:($page+1) ?>">下一页</a><a href="SY8_3_PageCount.php?page=<?php echo $page_count ?>">页末</a></form>
</div>
</body>
</html>

运行结果：

四、SQL注入漏洞(攻击)与防范(文件命名为SY8_4_SQLBUG.php)–了解魔术引号 magic_quotes_gpc的设置，了解addslashes( )函数的使用。

SY8_4DATABASE.php

<html>
<head><meta charset="utf-8"><title>建表userinfo</title>
</head>
<body>
<?php
$conn=mysqli_connect('localhost','root','111') or die('连接失败');
mysqli_query($conn,"set names utf8");// 创建表
mysqli_select_db($conn,"PXSCJ") or die('选择数据库失败');
$sql = 'CREATE TABLE  userinfo (
username char(4) not null primary key,
password  char(8) not null,
XB tinyint(1) null default 1,
NL tinyint(3) null default 0,
YX char(20) not null
)';
if ($conn->query($sql) === TRUE) {echo "表userinfo创建成功";
} else {echo "表userinfo创建失败: " . $conn->error;
}$sql = "insert into userinfo
values('php1','123456',1,23,'123456@qq'),
('php2','654321',0,21,'654321@qq');";
if (mysqli_multi_query($conn, $sql)) {echo "数据插入成功";
} else {echo "数据插入失败: " . $sql . "<br>" . mysqli_error($conn);
}
$conn->close();
?>
</body>
</html>

运行结果：

SY8_4_SQLBUG.php

<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8"><title>注入漏洞</title>
</head>
<body><center><font size=5 face="华文楷体" color="blue">SQL注入漏洞(攻击)与防范</font></center><div align="center"><form action="" method="post"><table width="300" border="1" align="center"><tr align="center"><td>用户名：</td><td><input type="text" name="username" value=""></td></tr><tr align="center"><td>密码：</td><td><input type="text" name="password" value=""></td></tr><tr align="center"><td colspan="2"><input type="submit" name="Submit" value="提交"></td></tr></table></form><?phpif (isset($_POST['Submit'])) {$username=$_POST['username'];$password=$_POST['password'];$conn = mysql_connect("localhost","root","111") or die('连接失败');mysql_select_db('PXSCJ',$conn) or die('选择数据库失败');mysql_query("SET NAMES utf8");$str="select * from userinfo where username='$username' and password='$password'";echo "<br>";echo $str;$result = mysql_query($str);?><table border="1"><tr><td>用户</td><td>密码</td><td>性别</td><td>年龄</td><td>邮箱</td></tr><?phpwhile($row = mysql_fetch_row($result)){?><tr><td><?php echo $row[0];?></td> <td><?php echo $row[1];?></td><td><?php echo $row[2];?></td> <td><?php echo $row[3];?></td><td><?php echo $row[4];?></td></tr><?php } ?></table><?php } ?></div>
</body>
</html>

运行结果：



五、完成教材P262~266例9.3(文件命名为SY8_5_PHP-MySQL.php)-- 综合(基本)应用

建立触发器，如果删除一条记录，则同时删除成绩表中该课程信息（在MySQL客户端操作)

use pxscj;
set names gb2312;
delimiter $$  
create trigger kc_delete after delete 
on kcb for each row
begindelete from cjb where KCH=old.KCH;
end$$
delimiter ;

SY8_5_PHP-MySQL.php

<html>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312"/>
<head><title>课程信息更新</title><style type="text/css"><!-- .STYLE1 {font-size: 15px; font-family: "幼圆";} -->div{ text-align:center; font-family:"幼圆"; font-size:24px; font-weight:bold;color:"#008000"; }table{ width:300px;}</style>
</head><body>
<div>课程表操作</div>
<form name="frm1" method="post">
<table align="center"><tr><td width="120"><span class="STYLE1">根据课程号查询:</span></td><td><input type="text" name="KCNumber" id="KCNumber" size="10"><input type="submit" name="test" class="STYLE1" value="查找"></td></tr>
</table>
</form><?php$conn=mysqli_connect("localhost","root","111")or die('连接失败');mysqli_select_db($conn,"PXSCJ")or die('连接数据库失败');mysqli_query($conn,"SET NAMES 'gb2312'");$KCNumber=@$_POST['KCNumber'];$sql="select * from KCB where KCH='$KCNumber'";$result=mysqli_query($conn,$sql);$row=@mysqli_fetch_array($result);if(($KCNumber!==NULL)&&(!$row))echo "<script>alert('没有该课程信息！')</script>";?><form name="frm2" method="post">
<table bgcolor="#CCCCCC" border="1" align="center" cellpadding="0" cellspacing="0"><tr><td bgcolor="#CCCCCC" width="90"><span class="STYLE1">课程号:</span></td><td><input name="KCNum" type="text" class="STYLE1" value="<?php echo $row['KCH'];?>"><input name="h_KCNum" type="hidden" value="<?php echo $row['KCH'];?>"></td> </tr>   <!--一个隐藏文本框--><tr><td bgcolor="#CCCCCC" width="90"><span class="STYLE1">课程名:</span></td><td><input name="KCName" type="text" class="STYLE1" value="<?php echo $row['KCM']; ?>"></td> </tr><tr><td bgcolor="#CCCCCC"><span class="STYLE1">开课学期:</span></td><td><input name="KCTerm" type="text" class="STYLE1" value="<?php echo $row['KKSQ']; ?>"></td> </tr><tr><td bgcolor="#CCCCCC"><span class="STYLE1">学时:</span></td><td><input name="KCtime" type="text" class="STYLE1" value="<?php echo $row['XS']?>"></td> </tr><tr><td bgcolor="#CCCCCC"><span class="STYLE1">学分:</span></td><td><input name="KCCredit" type="text" class="STYLE1" value="<?php echo $row['XF'];?>"></td> </tr><tr><td align="center" colspan="2" bgcolor="#CCCCCC"><input name="b" type="submit" value="修改" class="STYLE1">&nbsp;<input name="b" type="submit" value="添加" class="STYLE1"/>&nbsp;<input name="b" type="submit" value="删除" class="STYLE1">&nbsp;   </td> </tr>
</table>
</form>
</body>
</html><?php$KCH=@$_POST['KCNum'];			//KCH$h_KCH=@$_POST['h_KCNum'];			//KCH(表单中隐藏文本框中的KCH--修改课程信息用--KCH不能修改)$KCM=@$_POST['KCName'];			//KCM$KKXQ=@$_POST['KCTerm'];			//KKSQ$XS=@$_POST['KCtime'];			//XS$XF=@$_POST['KCCredit'];			//XFfunction test($KCH,$KCM,$KKXQ,$XF)            //简单的验证函数，验证表单数据的正确性{if(!$KCH) echo "<script>alert('课程号不能为空!');location.href='SY8_5_PHP-MySQL.php';</script>";	          //判断KCH是否为空elseif(!$KCM)	echo "<script>alert('课程名不能为空!');location.href='SY8_5_PHP-MySQL.php';</script>";	          //判断KCM是否为空elseif($KKXQ>8||$KKXQ<1) echo "<script>alert('开课学期必须为1-8的数字!');location.href='SY8_5_PHP-MySQL.php';</script>";   //判断KKSQ是否在1-8之间elseif(!is_numeric($XF)) echo "<script>alert('学分必须为数字!');location.href='SY8_5_PHP-MySQL.php';</script>";	          //判断XF是否为数字}if(@$_POST["b"]=='修改')                                                 //单击【修改】按钮{    test($KCH,$KCM,$KKXQ,$XF);					           //检查输入信息if($KCH!=$h_KCH)					           //判断用户是否修改了原来的KCH值echo "<script>alert('课程号与原数据有异，无法修改!');</script>";     //KCH不能修改else{$update_sql="update KCB set KCM='$KCM',KKSQ=$KKXQ,XS=$XS,XF=$XF WHERE KCH='$KCH'";	//update set$update_result=mysqli_query($conn,$update_sql);	                                                        //mysql_query()if(mysqli_affected_rows($conn)!=0)	echo "<script>alert('修改成功!');</script>";                    //mysql_affected_rows()else  echo "<script>alert('信息未修改!');</script>";                              //获取最后执行的INSERT-UPDATE-DELETE-所影响行数}}if(@$_POST["b"]=='添加')                                                 //单击【添加】按钮{test($KCH,$KCM,$KKXQ,$XF);	$s_sql="select KCH from KCB where KCH='$KCH'";	$s_result=mysqli_query($conn,$s_sql);$s_row=mysqli_fetch_array($s_result);if($s_row)								   //若要添加的课程已经存在则提示echo "<script>alert('课程已存在，无法添加!');</script>";else{$insert_sql="insert into KCB(KCH,KCM,KKSQ,XS,XF) values('$KCH', '$KCM', $KKXQ, $XS, $XF)";$insert_result=mysqli_query($conn,$insert_sql) or die('添加失败！');if( mysqli_affected_rows($conn)!=0 )                                                      //判断insert是否成功echo "<script>alert('添加成功!');</script>"; }}if(@$_POST["b"]=='删除')	                                           //单击【删除】按钮{if(!$KCH)echo "<script>alert('请输入要删除的课程号!');</script>";else{$d_sql="select KCH from KCB where KCH='$KCH'";	$d_result=mysqli_query($conn,$d_sql);$d_row=mysqli_fetch_array($d_result);if(!$d_row)							   //课程如果不存在则提示echo "<script>alert('课程号不存在，无法删除!');</script>";  else{$del_sql="delete from KCB where KCH='$KCH'";                                       //删除KCB中KCH为$KCH的记录$del_result=mysqli_query($conn,$del_sql) or die('删除失败！');if(mysqli_affected_rows($conn)!=0) echo "<script>alert('删除课程".$KCH."成功!');</script>";}}}
?>

运行结果：







本文发布于:2024-02-02 10:12:54，感谢您对本站的认可！

本文链接：https://www.4u4v.net/it/170683997543119.html

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系，我们将在24小时内删除。

上一篇：[转]ORACLE 绑定变量用法总结
下一篇：【SQL server】期末模拟
标签：操作   PHP   MySQL
留言与评论（共有 0 条评论）
   
验证码：
推荐文章
排行榜
热门标签

Copyright ©2019-2022 Comsenz Inc.Powered by ©

网站地图1 网站地图2 网站地图3 网站地图4 网站地图5 网站地图6 网站地图7 网站地图8 网站地图9 网站地图10 网站地图11 网站地图12 网站地图13 网站地图14 网站地图15 网站地图16 网站地图17 网站地图18 网站地图19 网站地图20 网站地图21 网站地图22/a> 网站地图23