首先,温馨提示,该部分内容跟昨天“PHP语法学习(第八天)—PHP连接mysql详解(上)”一起食用更佳噢!!
学习本篇内容必须掌握数据库基础命令点击“MYSQL 数据库”~~
本文是接着PHP连接mysql的知识点接着讲,今天主要讲述PHP连接mysql之后对数据库的内容进行操作✔️✔️✔️
另外,想学习更多PHP语法相关内容请点击“PHP专栏”~~
废话少说,进入正题~~~
文章目录
- PHP 创建 MySQL 表
- MYSQL 表的数值限制
- MySQLi - 面向对象
- MySQLi - 面向过程
- PDO方式创建表
- PHP MySQL 插入数据
- 插入数据要注意
- MySQLi 向 MySQL 插入数据
- 面向对象
- 面向过程
- PDO向 MySQL 插入数据
- PHP MySQL 插入多条数据
- 面向对象
- 面向过程
- PDO插入多条数据
- 预处理语句插入数据
- 利用`mysqli` 扩展运行预处理语句
- 实例
- PHP MySQL 查询表内数据
- 实例:
- PHP MySQL Where 子句
- 实例:
- PHP MySQL的 数据更新
- 实例
- PHP MySQL 的数据删除
- 实例
PHP 创建 MySQL 表
- 建立
MYSQL
数据表,同样有两种方式:MySQLi
和PDO
- 建表之前,需要用
use myDB
来选择要操作的数据库 - 建立
MySQL
表:CREATE TABLE
语句
MYSQL 表的数值限制
NOT NULL
每一行都必须含有值(不能为空) ,null
值是不可取的DEFAULT value
设置默认值UNSIGNED
使用无符号数值类型,0 及正数AUTO INCREMENT
设置MySQL
字段的值在新增记录时每次自动增长 1PRIMARY KEY
主键,设置数据表中每条记录的唯一标识。 通常列的PRIMARY KEY
设置为 ID 数值,与AUTO_INCREMENT
(自增约束) 一起使用。- 并且,每个表都应该有一个主键(名称为 “
id
” 列),主键必须包含唯一的值
MySQLi - 面向对象
实例:
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// 创建连接
$conn = new mysqli($servername, $username, $password, $dbname);
// 检测连接
if ($conn->connect_error) {
die("连接失败: " . $conn->connect_error);
}
// 使用 sql 创建数据表
$sql = "CREATE TABLE LXJtable(
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 ($conn->query($sql) === TRUE) {
echo "数据表 LXJtable 创建成功";
} else {
echo "创建数据表错误: " . $conn->error;
}
$conn->close();
?>
MySQLi - 面向过程
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// 创建连接
$conn = mysqli_connect($servername, $username, $password, $dbname);
// 检测连接
if (!$conn) {
die("连接失败: " . mysqli_connect_error());
}
// 使用 sql 创建数据表
$sql = "CREATE TABLE LXJtable(
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 "数据表 LXJtable 创建成功";
} else {
echo "创建数据表错误: " . mysqli_error($conn);
}
mysqli_close($conn);
?>
PDO方式创建表
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDBPDO";
try {
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
// 设置 PDO 错误模式,用于抛出异常
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// 使用 sql 创建数据表
$sql = "CREATE TABLE LXJtable(
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP
)";
// 使用 exec() ,没有结果返回
$conn->exec($sql);
echo "数据表 LXJtable 创建成功";
}
catch(PDOException $e)
{
echo $sql . "<br>" . $e->getMessage();
}
$conn = null;
?>
PHP MySQL 插入数据
- 同样,利用
MySQLi
或者PDO
向MySQL
插入数据 - INSERT INTO 语句常用于向 MySQL 表添加新的记录:
INSERT INTO table_name(字段名1,字段名2,...) VALUES (value1, value2, value3,...)
插入数据要注意
- PHP 中
SQL
查询语句必须使用引号 - 在
SQL
查询语句中的字符串值必须加引号 - 数值的值不需要引号
NULL
值不需要引号
MySQLi 向 MySQL 插入数据
面向对象
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// 创建连接
$conn = new mysqli($servername, $username, $password, $dbname);
// 检测连接
if ($conn->connect_error) {
die("连接失败: " . $conn->connect_error);
}
//myql插入数据
$sql = "INSERT INTO LXJtable(firstname, lastname, email) VALUES ('新之助', '野原', 'lbxx@example.com')";
if ($conn->query($sql) === TRUE) {
echo "新记录插入成功";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
$conn->close();
?>
面向过程
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// 创建连接
$conn = mysqli_connect($servername, $username, $password, $dbname);
// 检测连接
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
$sql = "INSERT INTO LXJtable(firstname, lastname, email) VALUES ('新之助', '野原', 'lbxx@example.com')";
if (mysqli_query($conn, $sql)) {
echo "新记录插入成功";
} else {
echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}
mysqli_close($conn);
?>
PDO向 MySQL 插入数据
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDBPDO";
try {
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
// 设置 PDO 错误模式,用于抛出异常
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "INSERT INTO LXJtable(firstname, lastname, email) VALUES ('新之助', '野原', 'lbxx@example.com')";
// 使用 exec() ,没有结果返回
$conn->exec($sql);
echo "新记录插入成功";
}
catch(PDOException $e)
{
echo $sql . "<br>" . $e->getMessage();
}
$conn = null;
?>
PHP MySQL 插入多条数据
- 通常,
mysqli_multi_query()
函数可用来执行多条SQL语句
面向对象
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// 创建链接
$conn = new mysqli($servername, $username, $password, $dbname);
// 检查链接
if ($conn->connect_error) {
die("连接失败: " . $conn->connect_error);
}
$sql = "INSERT INTO LXJtable(firstname, lastname, email) VALUES ('新之助', '野原', 'lbxx@example.com')";
$sql .= "INSERT INTO LXJtable(firstname, lastname, email) VALUES ('xj', 'Lian', 'DZQ@example.com')";
$sql .= "INSERT INTO LXJtable(firstname, lastname, email) VALUES ('ENSON', 'Chen', 'cyx@example.com')";
if ($conn->multi_query($sql) === TRUE) {
echo "新记录插入成功";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
$conn->close();
?>
面向过程
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// 创建链接
$conn = mysqli_connect($servername, $username, $password, $dbname);
// 检查链接
if (!$conn) {
die("连接失败: " . mysqli_connect_error());
}
$sql = "INSERT INTO LXJtable(firstname, lastname, email) VALUES ('新之助', '野原', 'lbxx@example.com')";
$sql .= "INSERT INTO LXJtable(firstname, lastname, email) VALUES ('xj', 'Lian', 'DZQ@example.com')";
$sql .= "INSERT INTO LXJtable(firstname, lastname, email) VALUES ('ENSON', 'Chen', 'cyx@example.com')";
if (mysqli_multi_query($conn, $sql)) {
echo "新记录插入成功";
} else {
echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}
mysqli_close($conn);
?>
PDO插入多条数据
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDBPDO";
try {
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// 开始事务
$conn->beginTransaction();
// SQL 语句
$conn->exec("INSERT INTO LXJtable(firstname, lastname, email) VALUES ('新之助', '野原', 'lbxx@example.com')");
$conn->exec("INSERT INTO LXJtable(firstname, lastname, email) VALUES ('xj', 'Lian', 'DZQ@example.com')");
$conn->exec("INSERT INTO LXJtable(firstname, lastname, email) VALUES ('ENSON', 'Chen', 'cyx@example.com')");
// 提交数据
$conn->commit();
echo "新记录插入成功";
}
catch(PDOException $e)
{
// 如果执行失败回滚
$conn->rollback();
echo $sql . "<br>" . $e->getMessage();
}
$conn = null;
?>
预处理语句插入数据
利用mysqli
扩展运行预处理语句
mysqli_stmt_bind_param($stmt, 'sss', $firstname, $lastname, $email);
- 该函数绑定参数查询并将参数传递给数据库
- 第二个参数是 “sss” , 通过 s 字符告诉 mysql 参数是字符串
- 可以用四种参数:
i
- 整数,d
- 双精度浮点数,s
- 字符串,b
- 布尔值 - 同时,每个参数必须指定类型,通过类型的判断可以减少SQL注入漏洞带来的风险。
实例
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// 创建连接
$conn = new mysqli($servername, $username, $password, $dbname);
/ 检测连接
if ($conn->connect_error) {
die("连接失败: " . $conn->connect_error);
} else {
$sql = "INSERT INTO MyGuests(firstname, lastname, email) VALUES(?, ?, ?)";
// 为 mysqli_stmt_prepare() 初始化 statement 对象
$stmt = mysqli_stmt_init($conn);
//预处理语句
if (mysqli_stmt_prepare($stmt, $sql)) {
// 绑定参数
mysqli_stmt_bind_param($stmt, 'sss', $firstname, $lastname, $email);
// 设置参数并执行
$firstname = '新之助';
$lastname = '野原';
$email = 'XX@example.com';
mysqli_stmt_execute($stmt);
$firstname = '广志';
$lastname = '野原';
$email = 'GZ@example.com';
mysqli_stmt_execute($stmt);
$firstname = '美伢';
$lastname = '野原';
$email = 'MY@example.com';
mysqli_stmt_execute($stmt);
}
}
?>
PHP MySQL 查询表内数据
从数据表中查询数据: SELECT
语句
SELECT column_name(s) FROM table_name
实例:
- 面向对象
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// 创建连接
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("连接失败: " . $conn->connect_error);
}
//mysql语句
$sql = "SELECT id, firstname, lastname FROM LXJtable";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
// 输出数据
while($row = $result->fetch_assoc()) {
echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
}
} else {
echo "0 结果";
}
$conn->close();
?>
利用函数
num_rows()
判断返回的数据
如果返回的是多条数据,函数fetch_assoc()
将结合相关的数据放入到关联数组并循环输出
while() 循环出运行结果,并输出id, firstname
和lastname
三个字段值。
- 面向过程
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// 创建连接
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
die("连接失败: " . mysqli_connect_error());
}
//sql语句查询
$sql = "SELECT id, firstname, lastname FROM LXJtable";
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
// 输出数据
while($row = mysqli_fetch_assoc($result)) {
echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
}
} else {
echo "0 结果";
}
mysqli_close($conn);
?>
PHP MySQL Where 子句
- where 子句用于查询满足指定条件的数据
- mysql中的语法:
SELECT column_name(s) FROM table_name WHERE column_name operator value
实例:
<?php
$con=mysqli_connect("localhost","username","password","database");
// 检测连接
if (mysqli_connect_errno())
{
echo "连接失败: " . mysqli_connect_error();
}
$result = mysqli_query($con,"SELECT * FROM LXJtable WHERE firstname='新之助'");
while($row = mysqli_fetch_array($result))
{
echo $row['firstName'] . " " . $row['lastName'];
echo "<br>";
}
?>
PHP MySQL的 数据更新
- MySQL中,
UPDATE
语句用于修改表中已存在的记录
UPDATE table_name SET column1 = value1, column2 = value2, ...
WHERE condition;
实例
<?php
$con=mysqli_connect("localhost","username","password","database");
// 检测连接
if (mysqli_connect_errno())
{
echo "连接失败: " . mysqli_connect_error();
}
mysqli_query($con,"UPDATE Persons SET Age=36 WHERE firstName='XJ' AND lastName='Lian'");
mysqli_close($con);
?>
PHP MySQL 的数据删除
DELETE
语句用于从数据库表中删除现有的记录
DELETE FROM table_name WHERE condition;
实例
<?php
$con=mysqli_connect("localhost","username","password","database");
// 检测连接
if (mysqli_connect_errno())
{
echo "连接失败: " . mysqli_connect_error();
}
mysqli_query($con,"DELETE FROM LXJtable WHERE LastName='野原' ");
mysqli_close($con);
?>
PHP连接mysql的相关知识到这里就结束了,明天见啦!
😆想要了解更多请点击练小杰的CSDN!!!!
ℹ️欢迎各位在评论区踊跃讨论,积极提出问题,解决困惑!!!