PHP 笔记——操作MySQL数据库

1. 连接MySQL服务器

​ mysqli_connect ：此函数是该函数的别名： mysqli::__construct()

mysqli mysqli_connect ( [string server [, string username [, string password [, string dbname [, int port [, string socket]]]]]] ) 参数说明

server	MySQL服务器地址（host）
username	用户名
password	密码
dbname	连接的数据库名称
port	MySQL服务器使用的端口号
socket	UNIX域socket

<?php$host = "127.0.0.1"; //MySQL服务器地址$userName = "root"; //用户名$password = "root"; //密码if ($connID = mysqli_connect($host, $userName, $password)){echo "<script type='text/javascript'>alert('数据库连接成功！');</script>";}else{echo "<script type='text/javascript'>alert('数据库连接失败！');</script>";} ?>

为了屏蔽由于数据库连接失败而显示的不友好的错误信息，可以在 mysqli_connect () 函数前加 “@” 来屏蔽错误提示。

<?php$host = "127.0.0.1"; //MySQL服务器地址$userName = "root"; //用户名$password = "111"; //密码if ($connID = @mysqli_connect($host, $userName, $password)){echo "<script type='text/javascript'>alert('数据库连接成功！');</script>";}else{echo "<script type='text/javascript'>alert('数据库连接失败！');</script>";} ?>

2. 选择MySQL数据库

（1）mysqli_connect

$connID = mysqli_connect("127.0.0.1", "root", "111", "mysql");

（2）mysqli_select_db

bool mysqli_select_db ( mysqli $link , string $dbname ) 参数说明

link	仅以过程化样式：由mysqli_connect() 或 mysqli_init() 返回的链接标识。
dbname	数据库名称

<?php$host = "127.0.0.1"; //MySQL服务器地址$userName = "root"; //用户名$password = "root"; //密码$dbName = "mysql"; //数据库名称//建立与MySQL数据库服务器的连接，并选择数据库$connID = mysqli_connect($host, $userName, $password);if(mysqli_select_db($connID, $dbName)){echo "数据库选择成功！";}else{echo "数据库选择失败！"; } ?>

3. 执行SQL语句

mixed mysqli_query( mysqli link, string query [, int resultmode] ) 参数说明

link	仅以过程化样式：由mysqli_connect() 或 mysqli_init() 返回的链接标识。
query	sql语句
resultmode

//连接MySQL服务器，选择数据库 $conn = mysqli_connect("localhost", "root", "root", "stu") or die("连接数据库服务器失败！" . mysqli_error()); //设置数据库编码格式utf8 mysqli_query($conn, "set names utf8");mysqli_query($conn, "insert into users values('4', '张三', '男', '23')"); mysqli_query($conn, "update users set Name='张三' where Id='1'"); mysqli_query($conn, "delete from users where Id='211789111'"); mysqli_query($conn, "use stu"); mysqli_query($conn, "set names utf8"); $result = mysqli_query($conn, "select * from users");

4. 将结果集返回到数组中

array mysqli_fetch_array ( resource result [, int result_type] ) 参数说明

result	由 mysqli_query()函数返回的数据指针
result_type	设置结果集数组的表达方式。 - MYSQL_ASSOC：关联数组 - MYSQL_NUM：数字数组 - MYSQL_BOTH：默认。同时产生关联和数字数组。

<?php while($myrow = mysqli_fetch_array($result)){ ?><tr><td align="center"><span><?php echo $myrow[0]; ?></span></td><td align="center"><span><?php echo $myrow[1]; ?></span></td><td align="center"><span><?php echo $myrow[2]; ?></span></td><td align="center"><span><?php echo $myrow[3]; ?></span></td></tr><br> <?php} ?>

5. 从结果集中获取一行作为对象

mixed mysqli_fetch_object ( resource result ) <?php while($myrow = mysqli_fetch_object($result)){ ?><tr><td align="center"><span><?php echo $myrow->Id; ?></span></td><td align="center"><span><?php echo $myrow->Name; ?></span></td><td align="center"><span><?php echo $myrow->Sex; ?></span></td><td align="center"><span><?php echo $myrow->Age; ?></span></td></tr><br> <?php} ?>

6. 从结果集中获取一行作为枚举数组

mixed mysqli_fetch_row ( resource result ) <?php while($myrow = mysqli_fetch_row($result)){ ?><tr><td align="center"><span><?php echo $myrow[0]; ?></span></td><td align="center"><span><?php echo $myrow[1]; ?></span></td><td align="center"><span><?php echo $myrow[2]; ?></span></td><td align="center"><span><?php echo $myrow[3]; ?></span></td></tr><br> <?php} ?>

7. 从结果集中获取一行作为关联数组

mixed mysqli_fetch_assoc ( resource result ) <?phpwhile($myrow = mysqli_fetch_assoc($result)){ ?><tr><td align="center"><span><?php echo $myrow['Id']; ?></span></td><td align="center"><span><?php echo $myrow['Name']; ?></span></td><td align="center"><span><?php echo $myrow['Sex']; ?></span></td><td align="center"><span><?php echo $myrow['Age']; ?></span></td></tr><br> <?php} ?>

8. 获取查询结果集中的记录数

int mysqli_num_rows ( resource result ) $nums = mysqli_num_rows ( $result ); echo $nums;

9. 释放内存

void mysqli_free_result(resource result)

10. 关闭连接

bool mysqli_close ( mysqli link ) mysqli_free_result($result); // 释放内存 mysqli_close($conn); // 关闭连接

11. 连接与关闭MySQL服务器的最佳时机

function book_query(){global $conn;$result = mysqli_query($conn,"select * from tb_demo01");while($myrow = mysqli_fetch_row($result)){echo $myrow[1]." ";echo $myrow[2]."<br />";}mysqli_free_result($result); }$conn = mysqli_connect("localhost", "root", "111", "db_database10") or die("连接数据库服务器失败！".mysqli_error()); mysqli_query($conn,"set names utf8"); book_query(); book_query(); mysqli_close($conn);

转载于:https://www.cnblogs.com/xzh0717/p/10661032.html

与50位技术专家面对面20年技术见证，附赠技术全景图

总结

以上是生活随笔为你收集整理的PHP 笔记——操作MySQL数据库的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。