- 学·问
- 帖子详情
综合运用
七言真知
发表于2021年07月06日
<p>数据库设计题 (15分) </p><p>1. SQL Code </p><p>1. DROP TABLE IF EXISTS `label`; </p><p>2. CREATE TABLE `label` ( </p><p>3. `id` int(11) NOT NULL AUTO_INCREMENT, </p><p>4. `name` varchar(50) NOT NULL, </p><p>5. PRIMARY KEY (`id`) </p><p>6. ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8; .............3分</p><p>7. DROP TABLE IF EXISTS `question`; </p><p>8. CREATE TABLE `question` ( .............1分</p><p>9. `id` int(11) NOT NULL AUTO_INCREMENT, </p><p>10. `lid` int(11) NOT NULL, </p><p>11. `uid` int(11) NOT NULL, </p><p>12. `title` varchar(100) NOT NULL, </p><p>13. `description` text NOT NULL, </p><p>14. `time` timestamp NOT NULL ON UPDATE CURRENT_TIMESTAMP, </p><p>15. PRIMARY KEY (`id`), </p><p>16. KEY `foreignKey1` (`lid`), </p><p>17. KEY `foreignKey2` (`uid`) </p><p>18. ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8; .............3分</p><p>19. </p><p>20. DROP TABLE IF EXISTS `user`; </p><p>21. CREATE TABLE `user` ( </p><p>22. `id` int(11) NOT NULL AUTO_INCREMENT, </p><p>23. `username` varchar(50) NOT NULL, </p><p>24. `password` varchar(50) NOT NULL, </p><p>25. PRIMARY KEY (`id`) </p><p>26. ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8; </p><p>27. </p><p>28. SET FOREIGN_KEY_CHECKS = 1; ...............3分</p><p> </p><p> </p><p>三、项目搭建与配置题 (15分)</p><p>1. PHP Code </p><p>1. return [ </p><p>2. 'db' => [ </p><p>3. 'host' => '127.0.0.1', ................1分</p><p>4. 'user' => 'root', ...............1分</p><p>5. 'password' => 'root', ................1分</p><p>6. 'dbName' => 'qa', ...............1分</p><p>7. 'charset' => 'utf8', ................1分</p><p>8. 'port' => '3306', </p><p>9. ], </p><p>10. ]; </p><p> </p><p>2. PHP Code </p><p>1. /** </p><p>2. * 初始化数据库(连接数据库、选择数据库、设置字符集) </p><p>3. * @return false|mysqli </p><p>4. */ </p><p>5. function dbInit() { </p><p>6. global $dbConfig; .......................1分</p><p>7. $link = mysqli_connect($dbConfig['host'], $dbConfig['user'], $dbConfig['password'], $dbConfig['dbName']); .......................2分</p><p>8. if (!$link) { </p><p>9. die('连接数据库失败'.mysqli_connect_error()); </p><p>10. } </p><p>11. mysqli_set_charset($link, 'utf8'); .....................1分</p><p>12. return $link; .....................1分</p><p>13. } </p><p>14. </p><p>15. /** </p><p>16. * 执行SQL语句 </p><p>17. * @param $sql SQL语句 </p><p>18. * @return bool|mysqli_result </p><p>19. */ </p><p>20. function query($sql) { </p><p>21. $link = dbInit(); ......................1分</p><p>22. $res = mysqli_query($link, $sql); ......................2分</p><p>23. if (!$res) { ........................1分</p><p>24. echo 'SQL: '.$sql; </p><p>25. echo "</br>"; </p><p>26. die('执行SQL语句失败'.mysqli_error($link)); </p><p>27. } </p><p>28. return $res; ............................1分</p><p>29. } </p><p> </p><p>四、数据库操作题(共20分)</p><p> PHP Code </p><p>1. //获取数据 ............................3分</p><p>2. $username = isset($_POST['username']) ? $_POST['username'] : ''; </p><p>3. $password = isset($_POST['password']) ? $_POST['password'] : ''; </p><p>4. $captcha = isset($_POST['captcha']) ? $_POST['captcha'] : ''; </p><p>5. //判断用户名和密码是否为空 ...........................2分</p><p>6. if(!$username || !$password){ </p><p>7. die('用户名或密码为空'); </p><p>8. } </p><p>9. //开启session会话 .............................2分</p><p>10. session_start(); </p><p>11. //验证验证码是否正确 .........................2分</p><p>12. if (strtolower($captcha) != strtolower($_SESSION['captcha'])) { </p><p>13. die('验证码不正确'); </p><p>14. } </p><p>15. //执行SQL语句,获取用户数据 ..........................3分</p><p>16. $sql ="select * from `user` where `username` = '$username' limit 1"; </p><p>17. $data = fetchRow($sql); </p><p>18. //判断用户是否存在 </p><p>19. if(!$data){ .........................1分</p><p>20. die('用户不存在'); </p><p>21. } </p><p>22. //判断用户密码是否正确 .......................
3
回复