（十）MySQL数据库——PHP

来得晚一些也行

已于 2022-12-31 13:10:16 修改

阅读量124

点赞数 1

分类专栏： PHP程序设计文章标签： php 开发语言

于 2022-12-29 20:43:16 首次发布

本文链接：https://blog.csdn.net/mr_morgans/article/details/128487712

版权

PHP程序设计专栏收录该内容

10 篇文章 2 订阅

订阅专栏

文章目录

- 第十章数据库

第十章数据库

1 连接数据库

<?php 
	$dbhost = "localhost";
	$dbuser = "root";
	$dbpass = "root";
	$mysqli = new mysqli($dbhost, $dbuser, $dbpass);

	//	连接数据库
	if ($mysqli->connect_errno){
		printf("Connect DataBase Fail，%s<br>",$mysqli->connect_error);
		exit();
	}
	echo "Connect DataBase Success<br>";

	$mysqli->close();
 ?>

2 创建数据库

<?php 
	$dbhost = "localhost";
	$dbuser = "root";
	$dbpass = "root";
	$mysqli = new mysqli($dbhost, $dbuser, $dbpass);

	//	连接数据库
	if ($mysqli->connect_errno){
		printf("Connect DataBase Fail，%s<br>",$mysqli->connect_error);
		exit();
	}
	echo "Connect DataBase Success<br>";
  

	//	创建数据库
	if ($mysqli->query("CREATE DATABASE member")) {
		printf("Create DataBase Success!<br>");
	}
	if ($mysqli->errno) {
		printf("Create DataBase Fail，%s<br>",$mysqli->error);
	}

	$mysqli->close();
 ?>

3 删除数据库

<?php 
	$dbhost = "localhost";
	$dbuser = "root";
	$dbpass = "root";
	$mysqli = new mysqli($dbhost, $dbuser, $dbpass);

	//	连接数据库
	if ($mysqli->connect_errno){
		printf("Connect DataBase Fail，%s<br>",$mysqli->connect_error);
		exit();
	}
	echo "Connect DataBase Success<br>";

	//	删除数据库
	if ($mysqli->query("DROP DATABASE member")){
		echo "DATABASE dropped successfully<br/>";
	}
	if ($mysqli->errno){
		printf("fail drop database %s<br/>",$mysqli->error);
	}

	$mysqli->close();
 ?>

4 创建表

<?php 
	$dbhost = "localhost";
	$dbuser = "root";
	$dbpass = "root";
	$dbname = "member";
	$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

	//	连接数据库
	if ($mysqli->connect_errno){
		printf("Connect DataBase Fail，%s<br>",$mysqli->connect_error);
		exit();
	}
	echo "Connect DataBase Success<br>";


	//	创建表
	$sql = "CREATE TABLE user(".
			"id INT(5) NOT NULL AUTO_INCREMENT,".
			"username VARCHAR(12),".
			"password VARCHAR(50),".
			"PRIMARY KEY(id))";

	if ($mysqli->query($sql)) { // 执行sql语句
		printf("Create Table Success!<br>");
	}

	if($mysqli->errno){
		printf("Create Table Fail，%s<br>",$mysqli->error);
	}	

	$mysqli->close();

 ?>

5 删除表

<?php 
	$dbhost = "localhost";
	$dbuser = "root";
	$dbpass = "root";
	$dbname = "member";
	$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

	//	连接数据库
	if ($mysqli->connect_errno){
		printf("Connect DataBase Fail，%s<br>",$mysqli->connect_error);
		exit();
	}
	echo "Connect DataBase Success<br>";

	//	删除表
	if ($mysqli->query("DROP TABLE user")) { // 执行sql语句
		printf("Drop Table successfully!");
	}
	if ($mysqli->errno){
		printf("Drop Table Fail，%s<br>",$mysqli->error);
	}

	$mysqli->close();

 ?>

6 添加操作

<?php 
        $dbhost = "localhost";
        $dbuser = "root";
        $dbpass = "root";
        $dbname = "member";
        $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

        if ($mysqli->connect_errno){
            printf("Connect DataBase Fail，%s<br>",$mysqli->connect_error);
            exit();
        }
        echo "Connect DataBase Success<br>";

        $sql = "INSERT INTO user(id,username,password) VALUES(null,'zs', '123')";

        if ($mysqli->query($sql)){
            printf("Insert Data SuccessFully");
        }		

        if($mysqli->errno){
            printf("Insert data Fail，%s<br>",$mysqli->error);
        }

        $mysqli->close();
 	?>

7 删除操作

<?php 
	$dbhost = "localhost";
	$dbuser = "root";
	$dbpass = "root";
	$dbname = "member";

	$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
 	if ($mysqli->connect_errno) {
 		printf("Connect database fail，%s<br>",$mysqli->connect_error);
 		exit();
 	}
 	echo "Connect database success.<br>";

 	$delete_sql = 'delete from user where id = 4';
 	if ($mysqli->query($delete_sql)) {
 		printf("delete success<br>");
 	}

 	if ($mysqli->errno) {
 		printf("delete fail，%s<br>",$mysqli->error);
 	}

 	$mysqli->close();
 ?>

8 修改操作

<?php 
	$dbhost = "localhost";
	$dbuser = "root";
	$dbpass = "root";
	$dbname = "member";
	$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

	if ($mysqli->connect_errno) {
		printf("Connect database fail，%s<br>",$mysqli->connect_error);
		exit();
	}
	echo "Connect DataBase SuccessFully<br>";


	$update_sql = 'update user set author="john" where title = "jsp"';
	if ($mysqli->query($update_sql)) {
		echo "Update SuccessFully<br>";
	}
	if ($mysqli->errno) {
		printf("Update Fail %s<br>",$mysql->error);
	}

	$mysqli->close();
 ?>

9 查询操作

<?php 
	$dbhost = "localhost";
	$dbuser = "root";
	$dbpass = "root";
	$dbname = "member";
	$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

	if ($mysqli->connect_errno){
		printf("Connection Database Fail，%s<br>", $mysqli->connect_error);
		exit();
	}
	echo "connect Database Successfully<br>";

	// $sql = 'SELECT title,author FROM user';		// 查询指定字段
	// $sql = 'SELECT * FROM user';				   // 查询全部字段
	$sql = 'SELECT * FROM tbl WHERE author = "me"'; // 条件查询

	$res = $mysqli->query($sql); //	返回一个结果集

	if ($res->num_rows > 0) {	 // 判断记录数是否大于0
		while ($row = $res->fetch_assoc()) {
			printf("id：%s，title：%s， author：%s， date：%s<br>",
				   $row['id'],$row['title'],$row['author'],$row['date']
				  );

			// printf("title：%s， author：%s<br>",
			// 	   $row['title'],$row['author']
			// 	  );
		
		}
	} else {
		echo "No record found";
	}

	mysqli_free_result($res);	// 释放结果集
	$mysqli->close();
 ?>

10 Like查询

<?php 
	$dbhost = "localhost";
	$dbuser = "root";
	$dbpass = "root";
	$dbname = "member";

	$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
	if ($mysqli->connect_errno) {
		printf("connect database fail，%s<br>",$mysqli->error);
	}
	echo "connect database success<br>";

	$sql = 'select * from user where title like "p%"';
	$res = $mysqli->query($sql); // 返回一个结果集
	if ($res->num_rows > 0) {
		while ($row = $res->fetch_assoc()) {
			printf("id：%s id：%s id：%s id：%s<br>",$row['id'],$row['author'],$row['title'],$row['date']);
		}
	} else {
		printf("no record found<br>");
	}

	mysqli_free_result($res); // 释放结果集
	$mysqli->close();

 ?>