PHP学习笔记4-php的MYSQL数据库访问

比眉伴天荒 2022-06-10 12:10 587阅读 0赞

04-PHP的MYSQL数据库访问

# 1. PHPMYSQL支持配置 #

2. PHPMYSQL的连接

3. PHP库的创建  
4. PHP表的创建

5. PHP数据插入、删除、更新6. PHP数据查询  
7. PHP数据库连接封装

**一:PHP MYSQL 支持配置**

1、检测PHP 数据库支持

![page1image4352][] ![page1image4512][page1image4352] ![page1image4672][page1image4352] ![page1image4832][page1image4352]

<?php  
$conn= mysqli\_connect("127.0.0.1:3306","root","");

//mysql端口如果没有修改,可以不指定端口  
//$conn = mysqli\_connect("127.0.0.1","root","");echo"<pre>";  
print\_r($conn);  
echo"</pre>";

![Center][]

通过修改php.ini 文件 添加数据库连接支持

![page1image7160][page1image4352] ![page1image7320][page1image4352] ![page1image7480][page1image4352] ![page1image7640][page1image4352] ![Center 1][] ![Center 2][]

**二:PHP MYSQL 的连接**  
1、在PHP 连接数据库时 可以使用mysql\_connect()函数 ,也可以使用mysqli\_connect()函数;

2、mysql\_connect()是PHP5版本之前使用的,该方法每次连接数据库都会创建一个新的连接,且该方法不支持MYSQL 的高级函数;

3、mysqli\_connect()使用连接缓存技术,第一连接是会创建新的mysql连接,后面同级连接将使用上次建立的连接,不会再创建一个新的连接;

4、mysqli\_connect()因使用连接缓存技术,所以相对效率较高,且支持mysql的高级用法;

<?php

//未修改mysql软件端口时 可以不写端口$servername="127.0.0.1";$username="root";  
$password="";

//创建连接  
$conn= mysqli\_connect($servername,$username,$password);

//检测连接if(!$conn) \{

//php中对于错误处理使用die(msg)函数  
//该函数执行完成后 会直接 退出当前脚本,后续代码不会继续运行//mysqli\_connect\_error()是PHP数据库连接时的错误信息获取函数die("连接失败: ". mysqli\_connect\_error());

\}  
echo"连接成功";

**三:PHP MYSQL 库的创建**  
1、mysqli\_close($conn); 用于关闭数据库连接,参数 $conn 是数据库连接参数

<?php  
$servername="127.0.0.1";$username="root";$password="";

$conn= mysqli\_connect($servername,$username,$password);if(!$conn) \{

die("连接失败: ". mysqli\_connect\_error());\}

//创建数据库  
$sql="create database student";

//mysqli\_query($conn, $sql)为数据库的操作方法  
//第一个参数为数据库连接对象  
//第二参数用于指定sql语句  
//该方法有返回值 返回类型为boolean true表示返回成功false表示失败$falg= mysqli\_query($conn,$sql);

if($falg) \{  
echo"数据库创建成功";

\}  else\{  
//mysqli\_error(connection)函数返回最近调用函数的最后一个错误描述。//需要当前 数据库连接的对象做为参数

echo"创建数据库成功: ". mysqli\_error($conn);\}

//数据库访问完成后,因及时关闭连接mysqli\_close($conn);

**四:PHP MYSQL 表的创建**

**1、在mysql数据库中创建表,需要首先选择库。**

<?php  
$servername="127.0.0.1";$username="root";$password="";

$conn= mysqli\_connect($servername,$username,$password);if(!$conn) \{

die("连接失败: ". mysqli\_connect\_error());\}

![page2image14480][page1image4352] ![page2image14640][page1image4352] ![page2image14800][page1image4352] ![page2image14960][page1image4352] ![page2image15120][page1image4352] ![page2image15280][page1image4352] ![page2image15440][page1image4352] ![page2image15600][page1image4352] ![page2image15760][page1image4352] ![page2image15920][page1image4352] ![page2image16080][page1image4352] ![page2image16240][page1image4352] ![page2image16400][page1image4352] ![page2image16560][page1image4352] ![page2image16720][page1image4352] ![page2image16880][page1image4352] ![page2image17040][page1image4352] ![page2image17200][page1image4352] ![page2image17360][page1image4352] ![page2image17520][page1image4352]

$sql="use student;";  
$falg1= mysqli\_query($conn,$sql);if($falg1)\{

//使用sql创建数据表  
$sql= "create table student (

id int auto\_increment primary key,name varchar(30),  
nikename varchar(30)

);";  
if(mysqli\_query($conn,$sql)) \{

echo"数据表student创建成功";\}else\{

echo"创建数据表错误: ". mysqli\_error($conn);\}

\}else\{  
echo"切换数据库失败: ". mysqli\_error($conn);

\}mysqli\_close($conn);

**2、在mysqli\_connect() 方法中提供第四个参数,该参数可以设置库名**

<?php  
$servername="127.0.0.1";$username="root";$password="";  
$dbname= "student";

$conn= mysqli\_connect($servername,$username,$password,$dbname);if(!$conn) \{

die("连接失败: ". mysqli\_connect\_error());\}

$sql= "create table student2 (  
id int auto\_increment primary key,name varchar(30),  
nikename varchar(30)

);";  
if(mysqli\_query($conn,$sql)) \{

echo"数据表student2创建成功";\}else\{

echo"创建数据表错误: ". mysqli\_error($conn);\}

mysqli\_close($conn);

**五:PHP MYSQL 数据的插入、删除和更新**

1、数据插入

<?php  
$servername="127.0.0.1";$username="root";$password="";  
$dbname= "student";

//创建连接  
$conn= mysqli\_connect($servername,$username,$password,$dbname);//检测连接  
if(!$conn) \{

die("Connection failed: ". mysqli\_connect\_error());\}

$sql= "insert into student(name, nikeName)

values  
('John','Doe')";

if(mysqli\_query($conn,$sql)) \{ echo"新记录插入成功";

\}  else\{

![page3image15744][page1image4352] ![page3image15904][page1image4352] ![page3image16064][page1image4352] ![page3image16224][page1image4352] ![page3image16384][page1image4352] ![page3image16544][page1image4352] ![page3image16704][page1image4352] ![page3image16864][page1image4352] ![page3image17024][page1image4352] ![page3image17184][page1image4352] ![page3image17344][page1image4352] ![page3image17504][page1image4352] ![page3image17664][page1image4352] ![page3image17824][page1image4352] ![page3image17984][page1image4352] ![page3image18144][page1image4352]

echo"数据插入失败,执行sql为: ".  $sql."<br>". mysqli\_error($conn);\}

mysqli\_close($conn);

**2、数据删除**

<?php  
$servername="127.0.0.1";$username="root";$password="";  
$dbname= "student";

//创建连接  
$conn= mysqli\_connect($servername,$username,$password,$dbname);//检测连接  
if(!$conn) \{

die("Connection failed: ". mysqli\_connect\_error());\}

$id= 2;  
$sql= "delete from student where id = $id";

if(mysqli\_query($conn,$sql)) \{ echo"数据删除成功";

\}  else\{  
echo"数据删除失败,执行sql为: ".  $sql."<br>". mysqli\_error($conn);

\}mysqli\_close($conn);

**3、数据更新**

<?php  
$servername="127.0.0.1";$username="root";$password="";  
$dbname= "student";

//创建连接  
$conn= mysqli\_connect($servername,$username,$password,$dbname);//检测连接  
if(!$conn) \{

die("Connection failed: ". mysqli\_connect\_error());\}

$nikeName="哈哈";  
$id= 1;  
$sql= "update student set nikeName = '$nikeName' where id = $id";

if(mysqli\_query($conn,$sql)) \{ echo"数据更新成功";

\}  else\{  
echo"数据更新失败,执行sql为: ".  $sql."<br>". mysqli\_error($conn);

\}mysqli\_close($conn);

**六:PHP MYSQL 数据查询**

<?php  
$servername= "127.0.0.1";$username= "root";$password= "";  
$dbname= "student";

//创建连接  
$conn= mysqli\_connect($servername,$username,$password,$dbname);//检测连接  
if(!$conn) \{

![page4image14768][page1image4352] ![page4image14928][page1image4352] ![page4image15088][page1image4352] ![page4image15248][page1image4352] ![page4image15408][page1image4352] ![page4image15568][page1image4352] ![page4image15728][page1image4352] ![page4image15888][page1image4352] ![page4image16048][page1image4352] ![page4image16208][page1image4352] ![page4image16368][page1image4352] ![page4image16528][page1image4352] ![page4image16688][page1image4352] ![page4image16848][page1image4352] ![page4image17008][page1image4352] ![page4image17168][page1image4352] ![page4image17328][page1image4352] ![page4image17488][page1image4352] ![page4image17648][page1image4352] ![page4image17808][page1image4352] ![page4image17968][page1image4352] ![page4image18128][page1image4352] ![page4image18288][page1image4352] ![page4image18448][page1image4352]

die("数据库连接失败: " . mysqli\_connect\_error());\}

$sql= "select id,name,nikeName from student1";$result= mysqli\_query($conn,$sql);

echo"<pre>";print\_r($result);echo"</pre>";

// $result为对象 在该对象中 存在属性num\_rows为当前查询结果的返回值if($result\->num\_rows>0)\{

//mysqli\_fetch\_array()方法将查询的结果以行的形式进行循环返回,  
//返回的结果为一个数组,可以通过指定返回类型选择返回索引数组还是关联数组  
// MYSQLI\_ASSOC返回关联数组MYSQLI\_NUM返回索引数组MYSQLI\_BOTH返回两者//当行循环过程中没有更多的行 返回null

while($row= mysqli\_fetch\_array($result))\{ echo$row\['id'\] . " ". $row\['name'\];echo"<br />";

\}\}else\{

echo"查无数据";\}

//释放结果集mysqli\_free\_result($result);mysqli\_close($conn);

**七:PHP MYSQL 连接面向过程的简单封装**

1、简单工具脚本

<?php  
define("HOST","127.0.0.1");define("USERNAME","root");define("PASSWORD","");define("DBNAME","stutest");define("MYSQL\_CHARSET","utf8");

$conn= mysqli\_connect("127.0.0.1","root","","stutest");

if(!$conn) \{  
die("数据库连接失败: ". mysqli\_connect\_error());

//设置数据库返回值 和php脚本一致mysqli\_query($conn,"set names '".MYSQL\_CHARSET."'");

2、调用工具脚本

<?php

//通过include或require语句,可以将PHP文件的内容插入另一个PHP文件//该操作是在服务其执行之前进行

//如果指定文件丢失  
//使用include只生成警告(E\_WARNING),并且脚本会继续//include "util/dbUtil.php";

//使用require会生成致命错误(E\_COMPILE\_ERROR)并停止脚本require"util/dbUtil.php";  
echo"string";

$sql= "select \* from dept";

$result= mysqli\_query($conn,$sql);

if($result\->num\_rows>0)\{  
while($row= mysqli\_fetch\_array($result,MYSQLI\_BOTH)) \{

echo"<pre>";print\_r($row);echo"</pre>";

\}\}else\{

echo"查无数据";\}

![page5image15664][page1image4352] ![page5image15824][page1image4352] ![page5image15984][page1image4352] ![page5image16144][page1image4352] ![page5image16304][page1image4352] ![page5image16464][page1image4352] ![page5image16624][page1image4352] ![page5image16784][page1image4352] ![page5image16944][page1image4352] ![page5image17104][page1image4352] ![page5image17264][page1image4352] ![page5image17424][page1image4352] ![page5image17584][page1image4352] ![page5image17744][page1image4352] ![page5image17904][page1image4352] ![page5image18064][page1image4352] ![page5image18224][page1image4352] ![page5image18384][page1image4352] ![page5image18544][page1image4352] ![page5image18704][page1image4352]

[page1image4352]: 
[Center]: /images/20220610/e8ecebbee48b45b596041c35d3def623.png
[Center 1]: https://img-blog.csdn.net/20170818153444274?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvbXN5cW1zeXE=/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center
[Center 2]: /images/20220610/33ad8d5776c24500aa4a2745bc1def79.png