首页 > 代码库 > 啦啦啦 年终大作业之数据库
啦啦啦 年终大作业之数据库
前言：
if(true) goto:正文;
作为一只已经投身于概率事业的CVdog怎么也没有想到这学期居然要修两门很CS的课程设计——DATABASE和COMPILER。今天先介（tu）绍（cao）一下DATABASE；
首先要吐槽的是时间节点。身边的小伙伴们都急匆匆的在11月份就着手了数据库设计的实验，而我却完全没当回事有木有。。。然后在deadline之前的8小时的时候组队的另一个同学（其实就是室友小受受嘛）突然告诉我今晚24：00数据库截止。
我愣了一下。
然后我就（和谐）（和谐）（和谐）（和谐）（和谐）（和谐）（和谐）（和谐）（和谐）（和谐）了。
然后就开始写了起来。
正文：
首先介绍一下作业吧。。
数据库课程设计就是要写一个8个实体的范式满足3NF以上的并且带有完备后端和前端界面的系统，并要拥有优化、存储过程、函数、触发器、bulabulabula之类的东东。
一句话说就是要完成一个看起来像点样子的后端+前端。
对外行来说，就是要写一个”学生管理系统“之类的东西。
于是我选择了写一个食堂外卖配送和下单系统。
好吧 最近外卖比较火。
设计：
先来一发屎屎的最终UI效果吧（什么？你说这不叫UI?)
首先是食堂菜单页面，你能感受到我大晚上的在做这个页面的时候肚子有多抗议吗？！
然后是每个用户的订单页
然后是配送员可以选择配送的订单
然后是配送员的订单队列
还有五六个页面就算了吧。。反正都是这样，朴朴素素的。。。。。（UI做得屎找什么借口。。。）
1.先上一发存储过程图哒设计
2.再上一发ER图(请不要吐槽线细字小一片蓝…人家可是满足第四范式的呢)
3.基本表是这样设计哒：
实现
我采用了HTML+JS+CSS(乱入了一个奇怪的东西)做前端设计，PHP做调用数据库和用户登录控制，MySQL server做后台数据库。
首先是建立数据库和相关的关系：
-- phpMyAdmin SQL Dump-- version 3.4.10.1-- http://www.phpmyadmin.net---- 主机: localhost-- 生成日期: 2014 年 12 月 28 日 16:57-- 服务器版本: 5.5.20-- PHP 版本: 5.3.10SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";SET time_zone = "+00:00";/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;/*!40101 SET NAMES utf8 */;---- 数据库: `test`---- ------------------------------------------------------------ 表的结构 `address`--CREATE TABLE IF NOT EXISTS `address` ( `AID` int(10) unsigned NOT NULL AUTO_INCREMENT, `ANAME` varchar(200) NOT NULL, PRIMARY KEY (`AID`,`ANAME`), UNIQUE KEY `AID_UNIQUE` (`AID`), UNIQUE KEY `ANAME_UNIQUE` (`ANAME`)) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;---- 转存表中的数据 `address`--INSERT INTO `address` (`AID`, `ANAME`) VALUES(1, ‘No.5 dorm‘),(2, ‘qqq‘);-- ------------------------------------------------------------ 表的结构 `canteen`--CREATE TABLE IF NOT EXISTS `canteen` ( `CID` int(10) unsigned NOT NULL AUTO_INCREMENT, `CADDRESS` varchar(200) NOT NULL, `CNAME` varchar(45) NOT NULL, PRIMARY KEY (`CID`), UNIQUE KEY `CID_UNIQUE` (`CID`)) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;---- 转存表中的数据 `canteen`--INSERT INTO `canteen` (`CID`, `CADDRESS`, `CNAME`) VALUES(2, ‘Middle of BUAA, XueYuan Rd. No.37, Near N0.5 Dorm.‘, ‘The Second Canteen‘),(4, ‘North of BUAA, XueYuan Rd. No.37, Near the north gate.‘, ‘The Third Canteen‘),(6, ‘West of BUAA, XueYuan Rd. No.37, in Building HeYi.‘, ‘The First Canteen‘);-- ------------------------------------------------------------ 表的结构 `food`--CREATE TABLE IF NOT EXISTS `food` ( `FID` int(10) unsigned NOT NULL AUTO_INCREMENT, `FNAME` varchar(45) NOT NULL, `FCATEENID` int(10) unsigned NOT NULL, `FPRICE` float DEFAULT NULL, `FIMG` longblob, `LAST` int(11) NOT NULL DEFAULT ‘100‘, PRIMARY KEY (`FID`), UNIQUE KEY `FID_UNIQUE` (`FID`), KEY `FCATEENID_idx` (`FCATEENID`)) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ;---- 转存表中的数据 `food`--INSERT INTO `food` (`FID`, `FNAME`, `FCATEENID`, `FPRICE`, `FIMG`, `LAST`) VALUES(1, ‘rice‘, 2, 0.25, NULL, 100),(2, ‘rice‘, 4, 0.3, NULL, 100),(3, ‘rice‘, 6, 0.2, NULL, 100),(4, ‘white noodle‘, 2, 5, NULL, 100),(5, ‘pulled noodles‘, 6, 7, NULL, 100),(6, ‘goulash‘, 2, 5.5, NULL, 100),(7, ‘great goulash‘, 4, 6, NULL, 100),(8, ‘perfect goulash‘, 6, 6.5, NULL, 100),(9, ‘noodle with beef‘, 2, 10, NULL, 100),(10, ‘noodle with greens‘, 2, 7, NULL, 100),(11, ‘hot noodle‘, 6, 6.5, NULL, 100),(12, ‘xiangguo‘, 4, 30, NULL, 100);-- ------------------------------------------------------------ 表的结构 `of`--CREATE TABLE IF NOT EXISTS `of` ( `OID` int(10) unsigned NOT NULL, `FID` int(10) unsigned NOT NULL, PRIMARY KEY (`OID`,`FID`), KEY `FK_OFFID` (`FID`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;-- ------------------------------------------------------------ 表的结构 `order`--CREATE TABLE IF NOT EXISTS `order` ( `OID` int(10) unsigned NOT NULL AUTO_INCREMENT, `OUID` int(10) unsigned NOT NULL, `SETDATE` datetime NOT NULL, `PAYDATE` datetime DEFAULT NULL, `OUTDATE` datetime DEFAULT NULL, `ENDDATE` datetime DEFAULT NULL, `OWID` int(10) unsigned DEFAULT NULL, `OSIGN` tinyint(1) NOT NULL DEFAULT ‘0‘, PRIMARY KEY (`OID`), UNIQUE KEY `OID_UNIQUE` (`OID`), KEY `OUID_idx` (`OUID`)) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ;---- 转存表中的数据 `order`--INSERT INTO `order` (`OID`, `OUID`, `SETDATE`, `PAYDATE`, `OUTDATE`, `ENDDATE`, `OWID`, `OSIGN`) VALUES(1, 26, ‘2014-12-28 21:55:32‘, NULL, NULL, NULL, NULL, 0),(2, 26, ‘2014-12-28 22:12:05‘, NULL, NULL, NULL, NULL, 0),(3, 26, ‘2014-12-28 22:19:29‘, NULL, NULL, NULL, NULL, 0),(4, 26, ‘2014-12-28 22:27:57‘, NULL, NULL, NULL, NULL, 0),(5, 26, ‘2014-12-28 22:29:14‘, NULL, NULL, NULL, NULL, 0),(6, 26, ‘2014-12-28 22:30:10‘, NULL, NULL, NULL, NULL, 0),(7, 26, ‘2014-12-28 22:33:12‘, NULL, NULL, NULL, NULL, 0),(8, 26, ‘2014-12-28 22:34:28‘, NULL, NULL, NULL, NULL, 0),(9, 26, ‘2014-12-28 22:38:02‘, NULL, NULL, NULL, NULL, 0),(10, 26, ‘2014-12-28 22:38:34‘, NULL, NULL, NULL, NULL, 0),(11, 26, ‘2014-12-28 23:44:27‘, NULL, NULL, NULL, NULL, 0);-- ------------------------------------------------------------ 表的结构 `ow`--CREATE TABLE IF NOT EXISTS `ow` ( `OID` int(10) unsigned NOT NULL, `WID` int(10) unsigned NOT NULL, PRIMARY KEY (`OID`,`WID`), UNIQUE KEY `OID_UNIQUE` (`OID`), KEY `WID_idx` (`WID`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;-- ------------------------------------------------------------ 表的结构 `user`--CREATE TABLE IF NOT EXISTS `user` ( `UID` int(10) unsigned NOT NULL AUTO_INCREMENT, `PASSWD` varchar(45) NOT NULL, `UNAME` varchar(45) NOT NULL, `UPHONE` varchar(45) NOT NULL, `UEMAIL` varchar(45) DEFAULT NULL, `UAID` int(10) unsigned NOT NULL, PRIMARY KEY (`UID`), UNIQUE KEY `UID_UNIQUE` (`UID`), KEY `UAID_idx` (`UAID`)) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=28 ;---- 转存表中的数据 `user`--INSERT INTO `user` (`UID`, `PASSWD`, `UNAME`, `UPHONE`, `UEMAIL`, `UAID`) VALUES(5, ‘11111‘, ‘123‘, ‘‘, ‘‘, 12),(13, ‘2222‘, ‘1111‘, ‘‘, ‘‘, 1),(15, ‘aaaa‘, ‘aaeeea‘, ‘‘, ‘‘, 1),(18, ‘aaaa‘, ‘aaeeea‘, ‘‘, ‘‘, 1),(19, ‘3434‘, ‘3432123‘, ‘‘, ‘‘, 1),(20, ‘12323‘, ‘12323‘, ‘‘, ‘‘, 1),(26, ‘qqq‘, ‘qqq‘, ‘qqq‘, ‘qqq‘, 2);-- ------------------------------------------------------------ 表的结构 `worker`--CREATE TABLE IF NOT EXISTS `worker` ( `WID` int(10) unsigned NOT NULL AUTO_INCREMENT, `WNAME` varchar(45) DEFAULT NULL, `WPHONE` varchar(45) DEFAULT NULL, `WLONGITUDE` float DEFAULT NULL, `WLATITUDE` float DEFAULT NULL, PRIMARY KEY (`WID`), UNIQUE KEY `WID_UNIQUE` (`WID`), UNIQUE KEY `WNAME_UNIQUE` (`WNAME`), UNIQUE KEY `WPHONE_UNIQUE` (`WPHONE`)) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;---- 限制导出的表------ 限制表 `food`--ALTER TABLE `food` ADD CONSTRAINT `FCATEENID` FOREIGN KEY (`FCATEENID`) REFERENCES `canteen` (`CID`) ON DELETE NO ACTION ON UPDATE NO ACTION;---- 限制表 `of`--ALTER TABLE `of` ADD CONSTRAINT `FK_OFFID` FOREIGN KEY (`FID`) REFERENCES `food` (`FID`), ADD CONSTRAINT `FK_OFOID` FOREIGN KEY (`OID`) REFERENCES `order` (`OID`);---- 限制表 `order`--ALTER TABLE `order` ADD CONSTRAINT `OUID` FOREIGN KEY (`OUID`) REFERENCES `user` (`UID`) ON DELETE NO ACTION ON UPDATE NO ACTION;---- 限制表 `ow`--ALTER TABLE `ow` ADD CONSTRAINT `OID` FOREIGN KEY (`OID`) REFERENCES `order` (`OID`) ON DELETE cascade ON UPDATE cascade, ADD CONSTRAINT `WID` FOREIGN KEY (`WID`) REFERENCES `worker` (`WID`) ON DELETE NO ACTION ON UPDATE NO ACTION;---- 限制表 `user`--ALTER TABLE `user` ADD CONSTRAINT `UAID` FOREIGN KEY (`UAID`) REFERENCES `address` (`AID`) ON DELETE NO ACTION ON UPDATE NO ACTION;/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;delimiter |create trigger deluser_tig before delete on user for each rowbegin delete from `of` where OID = any(select OID from `order` where OUID = old.UID); delete from `order` where OUID = old.UID;end|CREATE DEFINER=`root`@`localhost` PROCEDURE `ordermenu`(in oid int)select FNAME from food where FID = (select FID from `of` where OID = oid)alter table `order` drop foreign key OUID;alter table `order` add foreign key(OUID) references `user`(UID)on delete cascadeon update cascadealter table `ow` drop foreign key OID;alter table `ow` add foreign key(OID) references `order`(OID)on delete cascadeon update cascade
建好数据库之后就是前端啦
只贴上注册和下订单的部分代码
首先是注册控制页：
<?phpecho "<head><meta http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\" /></head>" ; error_reporting(0); session_start(); if(isset($_POST["submit"]) && $_POST["submit"] == "登陆") { $user = $_POST["username"]; $psw = $_POST["password"]; if($user == "" || $psw == "") { echo "<script>alert(‘请输入用户名或密码！‘); history.go(-1);</script>"; } else { $link = mysql_connect("localhost","root",""); mysql_select_db("test"); mysql_query("set names ‘gbk‘"); $sql = "select * from user where UNAME = ‘$_POST[username]‘ and PASSWD = ‘$_POST[password]‘"; //echo $sql; $result = mysql_query($sql); $num = mysql_num_rows($result); LOGINED: if($num) { $row = mysql_fetch_array($result); //将数据以索引方式储存在数组中 $addrque = "select ANAME from address where AID = ‘$row[5]‘"; $addrname = mysql_fetch_array(mysql_query($addrque)); echo "Login Success!<br>"; echo "欢迎".$row[2]."登录本系统！<br>--------------------------------<br>"; echo "Username:".$row[2]."<br>Password:".$row[1]."<br>收餐地址:".$addrname[0]; echo "<br>Phone:".$row[3]."<br>E-mail:".$row[4]."<br>"; echo "<br>--------------------------------<br>"; echo "我的订单："; $myorder_sql = "select * from `order` where ouid = $row[0]"; //echo $myorder_sql; $morst = mysql_query($myorder_sql); echo "<br><table border=\"1\"><center><tr><td><center>订单号</center></td><td><center>下单时间</center></td><td><center>付款时间</center></td><td><center>派送时间</center></td><td><center>确认收货时间</center></td><td><center>是否完成</center></td><td><center>订单内容</center></td></tr>"; if(!$morst){ echo mysql_error(); } for($i=0;$i<mysql_num_rows($morst);$i++) { $rs=mysql_fetch_row($morst); echo "<tr><td><center>$rs[0]</center></td><td><center>$rs[2]</center></td><td><center>$rs[3]</center></td><td><center>$rs[4]</center></td><td><center>$rs[5]</center></td>"; if ($rs[7] == 0) { echo "<td><center>未完成</center></td>"; }elseif ($rs[7] == 1) { echo "<td><center>已完成</center></td>"; }else{ echo "<td><center>订单已取消</center></td>"; } $menu = "select FNAME from food where FID = (select FID from `of` where oid = $rs[0])"; $rs=mysql_fetch_row(mysql_query($menu)); echo "<td><center>$rs[0]</center></td></tr>"; } echo "</table>"; $_SESSION[uid]=$row[0]; $sn = session_id(); echo "<a href=http://www.mamicode.com/"order.php?s=".$sn."\">点击订餐</a><br>"; echo "<a href=http://www.mamicode.com/"delete.php?s=".$sn."\">删除本用户</a>"; } else { echo "<script>alert(‘用户名或密码不正确！‘);history.go(-1);</script>"; //echo mysql_error(); } mysql_close($link); } } else if($_SESSION[‘uid‘]){ $link = mysql_connect("localhost","root",""); mysql_select_db("test"); mysql_query("set names ‘gbk‘"); $sql = "select * from user where UID = $_SESSION[uid]"; //echo $sql; //echo $sql; $result = mysql_query($sql); $num = mysql_num_rows($result); goto LOGINED; } else { echo "<script>alert(‘提交未成功！‘); history.go(-1);</script>"; } $sn = session_id(); ?> <br> <form action="done.php" method="post" > 输入确认收货的订单号：<input type="text" name="oid"/> <input type="Submit" name="confirm" value="http://www.mamicode.com/确认收货"/> </form><?phpecho "<form action=\"cancel.php?s=\".$sn.\" method=\"post\" >"?> 输入想要取消的订单号：<input type="text" name="oid"/> <input type="Submit" name="Submit" value="http://www.mamicode.com/取消订单"/> </form></html>
然后是下订单的页面：
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8" /><title>菜单一览</title></head><html> <br> <form method="POST"> 选择食堂： <select name="canteenNum"> <option value="http://www.mamicode.com/1">一食堂</option> <option value="http://www.mamicode.com/2">二食堂</option> <option value="http://www.mamicode.com/3">三食堂</option> <option value="http://www.mamicode.com/4">全部</option> </select> <input value="http://www.mamicode.com/提交" name="submit" type="submit"> </form> <br><?php error_reporting(0); session_start(); if(!$_SESSION[‘uid‘]){ echo "<script>alert(‘请先登录并打开Cookie！‘); history.go(-1);</script>"; } $nuid = $_SESSION[‘uid‘]; $sn = session_id(); $link = mysql_connect("localhost","root",""); mysql_select_db("test"); mysql_query("set names ‘gbk‘"); if($_POST[‘submit‘]){ $cnum = $_POST[‘canteenNum‘]; switch ($cnum) { case ‘1‘: $sqlq = "select CADDRESS from canteen where CID = 6"; break; case ‘2‘: $sqlq = "select CADDRESS from canteen where CID = 2"; break; case ‘3‘: $sqlq = "select CADDRESS from canteen where CID = 4"; break; case ‘4‘: break; default: echo "<br>不存在该食堂<br>"; break; } $rst = mysql_fetch_array(mysql_query($sqlq)); if ($cnum < 4) { echo "<br>食堂地址：".$rst[0]."<br>"; } switch ($cnum) { case ‘1‘: $sqlq = "select FID, FNAME,FPRICE,FCATEENID,LAST,FIMG from food where FCATEENID = 6"; break; case ‘2‘: $sqlq = "select FID, FNAME,FPRICE,FCATEENID,LAST,FIMG from food where FCATEENID = 2"; break; case ‘3‘: $sqlq = "select FID, FNAME,FPRICE,FCATEENID,LAST,FIMG from food where FCATEENID = 4"; break; case ‘4‘: $sqlq = "select FID, FNAME,FPRICE,FCATEENID,LAST,FIMG from food"; break; default: echo "<br>不存在该食堂<br>"; break; } $result = mysql_query($sqlq); echo "<br><table border=\"1\"><center><tr> <td><center>菜编号</center></td> <td><center>菜名</center></td> <td><center>价 格</center></td> <td><center>食堂编号</center></td> <td><center>剩余量</center></td> <td><center>图 片</center></td> </tr>"; for($i=0;$i<mysql_num_rows($result);$i++) { $rs=mysql_fetch_row($result); echo"<tr><td><center>$rs[0]</center></td> <td><center>$rs[1]</center></td> <td><center>$rs[2]</center></td> <td><center>$rs[3]</center></td> <td><center>$rs[4]</center></td> <td><center><img src=http://www.mamicode.com/‘$rs[5]‘></center></td> </tr>"; } echo "</center></table>"; } mysql_close($link); echo "<br> <form action=\"makeorder.php?s=\".$sn.\" method=\"post\" >"?> 输入需要的编号：<input type="text" name="fid"/> <input type="Submit" name="Submit" value="http://www.mamicode.com/下单"/> </form></html>
然后是下单检查页面：
<?phperror_reporting(0);session_start();echo "<head><meta http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\" /></head>" ;if(!$_SESSION[‘uid‘]){ echo "<script>alert(‘请先登录‘); history.go(-1);</script>";}$nuid = $_SESSION[‘uid‘];echo "<head><meta http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\" /></head>" ; if(isset($_POST["Submit"]) && $_POST["Submit"] == "下单"){ $link = mysql_connect(‘localhost‘,‘root‘,‘‘); //连接数据库 $db_selected = mysql_select_db("test", $link); $fid = $_POST["fid"]; $sql = "select * from food where FID = $fid"; //echo "<br>".$sql; $result = mysql_query($sql); //执行SQL语句 $num = mysql_num_rows($result); //统计执行结果影响的行数 if(!$num){ echo "<script>alert(‘不存在该类菜品！‘); history.go(-1);</script>"; }else{ $sql = "insert into `order`(OUID, SETDATE, OSIGN) VALUES($nuid, now(), 0)"; $result = mysql_query($sql); $newID = mysql_insert_id(); if (!$result) { echo "下单失败，系统繁忙！".mysql_error(); }else{ $sql = "insert into `of`(OID, FID) VALUES($newID, $fid)"; //echo $sql; $result = mysql_query($sql); } $purl = "logincheck.php?s=".$sn; echo "<script>alert(‘下单成功！请返回个人信息页面查看‘); self.location=‘$purl‘;</script>"; } mysql_close($link); }else{ echo "<script>alert(‘下单失败！‘); history.go(-1);</script>"; }?>
其他代码就不贴了。。反正代码这么渣又不是纯正的码农血统肯定没人看。
细心的人应该不难发现整个表里的食堂是写死的，不是通过一个新的admin实例来插入删除修改。
不过从工程角度来看（还工程角度。。码农装的还挺像啊。。）新建一个食堂的时间里我应该已经跳了N个槽了吧lol 维护神马的一点都没有趣hiahia
总结一下 8个小时从0开始搭服务器（血的教训以后永远不要！！不要！！不要在一个server上搭一堆sql服务器，找配置文件都能找乱，最后回滚服务器硬盘重头来简直浪费生命），学mysql语句（之前一直是sqlserver啊混蛋！），学php里的各种东西，学session，学xxx，再加上码了上千行代码，还写了个文档，也是挺醉的啊！！！
啦啦啦 年终大作业之数据库
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。