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 )
<?php
while($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);
