创建数据表
$sql = "CREATE TABLE MyGuests (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP
)";
if (mysqli_query($conn, $sql)) {
echo "数据表 MyGuests 创建成功";
} else {
echo "创建数据表错误 : " . mysqli_error($conn);
}
往数据表中插入单条数据
插入多条数据：mysqli_multi_query($conn,$sql)
$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('qiong', 'wu', 'dacha@example.com')";
if (mysqli_query($conn, $sql)) {
echo "新纪录插入成功";
} else {
echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}
使用预处理语句执行SQL
$sql = "INSERT INTO MyGuests VALUES(?, ?, ?)";
// 为 mysqli_stmt_prepare() 初始化 statement 对象
$stmt = mysqli_stmt_init($conn);
// 预处理语句
if (mysqli_stmt_prepare($stmt, $sql)) {
// 绑定参数，"sss"指后面三个参数都是字符串类型
//四种参数：i-整数 d-浮点数 s-字符串 b-布尔值
mysqli_stmt_bind_param($stmt, 'sss', $firstname, $lastname, $email);
//设置参数并执行
$firstname = 'san';
$lastname = 'zhang';
$email = 'zhang@example.com';
mysqli_stmt_execute($stmt);
}
mysqli_stmt_close($stmt);
读取MySQLi数据表中的数据
从表MyGuests读取id,firstname和lastname列的数据并显示在页面上
<?php
// 创建连接
$conn = new mysqli_connect("localhost","username", "password", "TestDB");
//检查连接
if ($conn->connect_error) {
die(" : " . mysqli_connect_error()`|);
}
$sql = "SELECT id, firstname, lastname FROM MyGuests";
$result = $mysqli_query($conn,$sql);
if (mysqli_num_rows($result) > 0) {
// 输出每行数据
while($row = mysqli_fetch_assoc($result)) {
echo "<br> id: ".$row["id"]." - Name: ".$row["firstname"]." ".$row
}
}else{
echo "0 个结果";
}
// 释放结果集
mysqli_free_result($result);
mysqli_close($conn);
?>
update更新数据
UPDATE table_name
SET column1=value
WHERE some_column=some_value
delete删除数据
DELETE FROM table_name
WHERE some_column = some_value
``