php操作数据库笔记,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

$host = "127.0.0.1"; //MySQL服务器地址

$userName = "root"; //用户名

$password = "root"; //密码

if ($connID = mysqli_connect($host, $userName, $password)){

echo "";

}else{

echo "";

}

?>

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

$host = "127.0.0.1"; //MySQL服务器地址

$userName = "root"; //用户名

$password = "111"; //密码

if ($connID = @mysqli_connect($host, $userName, $password)){

echo "";

}else{

echo "";

}

?>

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

数据库名称

$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：默认。同时产生关联和数字数组。

while($myrow = mysqli_fetch_array($result)){

?>

<?php echo $myrow[0]; ?> <?php echo $myrow[1]; ?> <?php echo $myrow[2]; ?> <?php echo $myrow[3]; ?>

}

?>

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

mixed mysqli_fetch_object ( resource result )

while($myrow = mysqli_fetch_object($result)){

?>

<?php echo $myrow->Id; ?> <?php echo $myrow->Name; ?> <?php echo $myrow->Sex; ?> <?php echo $myrow->Age; ?>

}

?>

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

mixed mysqli_fetch_row ( resource result )

while($myrow = mysqli_fetch_row($result)){

?>

<?php echo $myrow[0]; ?> <?php echo $myrow[1]; ?> <?php echo $myrow[2]; ?> <?php echo $myrow[3]; ?>

}

?>

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

mixed mysqli_fetch_assoc ( resource result )

while($myrow = mysqli_fetch_assoc($result)){

?>

<?php echo $myrow['Id']; ?> <?php echo $myrow['Name']; ?> <?php echo $myrow['Sex']; ?> <?php echo $myrow['Age']; ?>

}

?>

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]."
";

}

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);