mysql if语句的使用_PHP-MySQL简明手册

最新推荐文章于 2021-03-25 06:23:40 发布

weixin_39960319

最新推荐文章于 2021-03-25 06:23:40 发布

阅读量206

点赞数

文章标签： mysql if语句的使用 php mysql 和javascript 学习手册

PHP 的MySQL连接

当用PHP访问数据库时，除了PHP自带的数据库驱动，我们一般还有两种比较好的选择：PDO和MySQLi。

注意：自PHP5.5开始，传统的mysql扩展已经废弃，只能使用PHP提供的MySQLi扩展或PDO扩展

PDO和MySQLi区别

如果你的项目需要在多种数据库中切换，建议使用 PDO;若选用MySQLi,在多种数据库中切换时候，你需要重新编写所有代码。

MySQLi专门针对MySQL设计的，所以MySQLi相对于PDO性能稍微好一些。

PHP 连接MySQL

MySQLi - 面向对象的连接方法

<?php
$servername = "localhost";
$username = "username";
$password = "password";
// 创建连接
$conn = new mysqli($servername, $username, $password);
// 检测连接
if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}
echo "连接成功";
?>

MySQLi - 面向过程的连接方法

<?php
$servername = "localhost";
$username = "username";
$password = "password";
// 创建连接
$conn = mysqli_connect($servername, 
$username, $password);
// 检测连接
if (!$conn) {
    die("Connection 
failed: " . mysqli_connect_error());
}
echo "连接成功";
?

PDO 的连接方法

<?php
$servername = "localhost";
$username = "username";
$password = "password";
try {
    $conn = new PDO("mysql:host=$servername;dbname=myDB", 
$username, $password);
    echo "连接成功";
}
catch(PDOException $e)
{
    
echo $e->getMessage();
}
?>

关闭连接

连接在脚本执行完后会自动关闭。可以使用以下代码来关闭连接：

MySQLi - 面向对象的关闭连接方法

$conn->close();

MySQLi - 面向过程的关闭连接方法

mysqli_close($conn);

PDO关闭连接的方法

$conn = null;

创建数据库

使用 MySQLi 和 PDO 创建 MySQL 数据库

使用CREATE DATABASE语句创建数据库

MySQLi - 使用面向对象的方式创建数据库

<?php
$servername = "localhost";
$username = "username";
$password = "password";
// 创建连接
$conn = new mysqli($servername, $username, $password);
// 检测连接
if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}

// 创建数据库
$sql = "CREATE DATABASE myDB";
if ($conn->query($sql) === TRUE) {
    
echo "数据库创建成功";
} else {
    echo 
"Error creating database: " . $conn->error;
}
$conn->close();
?>

MySQLi-使用面向过程的方式创建数据库

<?php
$servername = "localhost";
$username = "username";
$password = "password";
// 创建连接
$conn = mysqli_connect($servername, 
$username, $password);
// 检测连接
if (!$conn) {
    die("连接失败: " . mysqli_connect_error());
}
// 创建数据库
$sql = "CREATE DATABASE myDB";
if (mysqli_query($conn, $sql)) {
    echo "数据库创建成功";
} else {
    echo "Error 
creating database: " . mysqli_error($conn);
}
mysqli_close($conn);
?>

PDO 创建数据库

<?php
$servername = "localhost";
$username = "username";
$password = "password";

try {
    $conn = new PDO("mysql:host=$servername;dbname=myDB", $username, $password);

    // 设置 PDO 错误模式为异常
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $sql = "CREATE DATABASE myDBPDO";

    // 使用 exec() ，因为没有结果返回
    $conn->exec($sql);

    echo "数据库创建成功<br>";
}
catch(PDOException $e)
{
    echo $sql . "<br>" . $e->getMessage();
}

$conn = null;
?>

创建数据表

使用CREATE TABLE语句创建数据表

我们将创建一个名为 "MyGuests" 的表，有 5 个列： "id", "firstname", "lastname", "email" 和 "reg_date":

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
)

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 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 ($conn->query($sql) === TRUE) {
    echo "Table MyGuests created successfully";
} 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 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_close($conn);
?>

PDO 创建数据表

<?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 创建数据表
   
$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
                )";
    // 使用 exec() ，没有结果返回 
$conn->exec($sql);
echo "数据表 MyGuests 创建成功";
}
catch(PDOException $e)
{
    echo $sql . "<br>" . $e->getMessage();
}
$conn = null;
?>

插入数据

插入语句

INSERT INTO table_name (column1, column2, column3,...)VALUES (value1, value2, value3,...)

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 = "INSERT INTO MyGuests (firstname, lastname, email) 
                    VALUES ('John', 'Doe', 'john@example.com')";
                    
if ($conn->query($sql) === TRUE) {
    echo "新记录插入成功";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();
?>

MySQLi - 使用面向过程方式插入数据

<?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 MyGuests (firstname, lastname, email)
                VALUES ('John', 'Doe', 'john@example.com')";
if (mysqli_query($conn, $sql)) {
    echo "新记录插入成功";
} else {
    echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}
mysqli_close($conn);
?>

POD 插入数据

<?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 MyGuests (firstname, lastname, email)
                                   VALUES ('John', 'Doe', 'john@example.com')";
    // 使用 exec() ，没有结果返回 
    $conn->exec($sql);
    
echo "新记录插入成功";
}
catch(PDOException $e)
 {
    
echo $sql . "<br>" . $e->getMessage();
}
$conn = null;
?>

插入多条数据

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 = "INSERT INTO MyGuests (firstname, lastname, email)
            VALUES ('John', 'Doe', 'john@example.com');";
$sql .= "INSERT INTO MyGuests (firstname, lastname, email)
            VALUES ('Mary', 'Moe', 'mary@example.com');";
$sql .= "INSERT INTO MyGuests (firstname, lastname, email)
            VALUES ('Julie', 'Dooley', 'julie@example.com')";
            
if ($conn->multi_query($sql) === TRUE) {
    echo "新记录插入成功";
} else {
    echo "Error: " . $sql . "<br>" . $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 = "INSERT INTO MyGuests (firstname, lastname, email)
                VALUES ('John', 'Doe', 'john@example.com');";
$sql .= "INSERT INTO MyGuests (firstname, lastname, email)
                VALUES ('Mary', 'Moe', 'mary@example.com');";
$sql .= "INSERT INTO MyGuests (firstname, lastname, email)
                VALUES ('Julie', 'Dooley', 'julie@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 MyGuests (firstname, lastname, email) 
                            VALUES ('John', 'Doe', 'john@example.com')");
    
    $conn->exec("INSERT INTO MyGuests (firstname, lastname, email) 
                            VALUES ('Mary', 'Moe', 'mary@example.com')");
    
    $conn->exec("INSERT INTO MyGuests (firstname, lastname, email) 
                            VALUES ('Julie', 'Dooley', 'julie@example.com')");
    
    // 提交事务
    $conn->commit();
    echo "新记录插入成功";
 }
catch(PDOException $e)
{    
    // 如果执行失败回滚
    $conn->rollback();
    echo $sql . "<br>" . $e->getMessage();
}

$conn = null;

?>

使用预处理语句插入多条数据

<?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 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 = 'John';
    $lastname = 'Doe';
    $email = 'john@example.com';
    mysqli_stmt_execute($stmt);
        
    $firstname = 'Mary';
    $lastname = 'Moe';
    $email = 'mary@example.com';
    mysqli_stmt_execute($stmt); 
        
    $firstname = 'Julie';    
    $lastname = 'Dooley';
    $email = 'julie@example.com';    
    mysqli_stmt_execute($stmt);
    }
}
?>

参数绑定

mysqli_stmt_bind_param($stmt, 'sss', $firstname, $lastname, $email);

该函数绑定参数查询并将参数传递给数据库。第二个参数是 "sss" 。以下列表展示了参数的类型。

i - 整数
d - 双精度浮点数
s - 字符串
b - 布尔值

预处理语句

预处理：创建 SQL 语句模板并发送到数据库。预留的值使用参数 '?' 标记。数据库解析，编译，对SQL语句模板执行查询优化，并存储结果不输出。最后，将应用绑定的值传递给参数（"?" 标记），数据库执行语句

相对直接执行SQL语句，预处理的优点：

预处理语句大大减少了分析时间，只做了一次查询（虽然语句多次执行）。
绑定参数减少了服务器带宽，你只需要发送查询的参数，而不是整个语句。
预处理语句针对SQL注入是非常有用的，因为参数值发送后使用不同的协议，保证了数据的合法性。

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);
}
// 预处理及绑定
$stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email) 
                                                        VALUES(?, ?, ?)");
$stmt->bind_param("sss", $firstname, $lastname, $email);
// 设置参数并执行
$firstname = "John";
$lastname = "Doe";
$email = "john@example.com";
$stmt->execute();

$firstname = "Mary";
$lastname = "Moe";
$email = "mary@example.com";
$stmt->execute();

$firstname = "Julie";
$lastname = "Dooley";
$email = "julie@example.com";
$stmt->execute();

echo "新记录插入成功";
$stmt->close();
$conn->close();
?>

PDO 使用预处理语句

<?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 并绑定参数
    $stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email)
                                        VALUES (:firstname, :lastname, :email)");
    
    $stmt->bindParam(':firstname', $firstname);
    $stmt->bindParam(':lastname', $lastname);
    $stmt->bindParam(':email', $email);
    // 插入行
    $firstname = "John";
    $lastname = "Doe";
    $email = "john@example.com";
    $stmt->execute();
    // 插入其他行
    $firstname = "Mary";
    $lastname = "Moe";
    $email = "mary@example.com";
    $stmt->execute();
    // 插入其他行
    $firstname = "Julie";
    $lastname = "Dooley";
    $email = "julie@example.com";
    $stmt->execute();
    echo "新记录插入成功";
    }
catch(PDOException $e)
 {
    echo $sql . "<br>" . $e->getMessage();
}
$conn = null;
?>

查询数据

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 = "SELECT id, firstname, lastname FROM MyGuests";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
    // 输出每行数据
    while($row = $result->fetch_assoc()) 
    {
        echo "<br> id: ". $row["id"]. " - Name: ". $row["firstname"]. " " . $row["lastname"];
    }
} else {
    echo "0 个结果";
}
$conn->close();
?>

PDO 查询数据

<?php
echo "<table style='border: solid 1px black;'>";
echo "<tr><th>Id</th><th>Firstname</th><th>Lastname</th><th>Email</th><th>Reg date</th>            </tr>";
class TableRows extends RecursiveIteratorIterator {
    function __construct($it) { 
        parent::__construct($it, self::LEAVES_ONLY); 
    }
    function current() {
        return "<td style='width: 150px; border: 1px solid black;'>" . 
        parent::current(). "</td>";
    }
    
    function beginChildren() {     
        echo "<tr>"; 
    } 
    function endChildren() { 
        echo "</tr>" . "n";
    } 
}

$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDBPDO";

try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $stmt = $conn->prepare("SELECT * FROM MyGuests"); 
    $stmt->execute();
    // 设置结果集为关联数组  
    $result = $stmt->setFetchMode(PDO::FETCH_ASSOC); 
    
    foreach(new TableRows(new RecursiveArrayIterator($stmt->fetchAll())) as $k=>$v) { 
        echo $v;
    
    }
    $dsn = null;
 }
catch(PDOException $e)
{
    echo "Error: " . $e->getMessage();
}
$conn = null;
echo "</table>";
?>

使用 WHERE 子句

WHERE 子句用于提取满足指定标准的的记录。

语法：

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();
}
$sql = "SELECT * FROM Persons WHERE FirstName='Peter'";
$result = mysqli_query($con,$sql);
while($row = mysqli_fetch_array($result))   
{
    echo $row['FirstName'] . " " . $row['LastName'];
    echo "<br>";
}
?>

ORDER BY 关键词

ORDER BY 关键词用于对记录集中的数据进行排序，默认为升序。

语法：

SELECT column_name(s)
FROM table_name
ORDER BY column_name(s) ASC|DESC

例子：

<?php
$con=mysqli_connect("localhost","username","password","database");
// 检测连接
if (mysqli_connect_errno())
{    
    echo "连接失败: " . mysqli_connect_error();
}
$sql = "SELECT * FROM Persons ORDER BY age";
$result = mysqli_query($con,$sql);
while($row = mysqli_fetch_array($result))
{
    echo $row['FirstName'];
    echo " " . $row['LastName'];
    echo " " . $row['Age'];
    echo "<br>";
}
mysqli_close($con);
?>

根据两列进行排序

可以根据多个列进行排序.

当按照多个列进行排序时，只有第一列的值相同时才使用第二列：

SELECT column_name(s)
FROM table_name
ORDER BY column1, column2

Update 更新数据

UPDATE 语句用于更新数据库表中已存在的记录。

语法：

UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value

注意：如果省去 WHERE 子句，所有的记录都会被更新！

假设已经创建了“Person表”：

FirstNameLastNameAgePeterGriffin35GlennQuagmire33

对Person表更新

<?php
$con=mysqli_connect("localhost","username","password","database");
// 检测连接
if (mysqli_connect_errno())
{
    echo "连接失败: " . mysqli_connect_error();
}
$sql = "UPDATE Persons SET Age=36 WHERE FirstName='Peter' AND LastName='Griffin'";
mysqli_query($con,$sql);
mysqli_close($con);
?>

更新后：

FirstNameLastNameAgePeterGriffin36GlennQuagmire33

Delete 删除数据

DELETE 语句用于从数据库表中删除行。

语法：

DELETE FROM table_name
WHERE some_column = some_value

注意：如果省去 WHERE 子句，所有的记录都会被删除！

假设有表Person

FirstNameLastNameAgePeterGriffin35GlennQuagmire33

下面的例子删除 "Persons" 表中所有 LastName='Griffin' 的记录：

<?php
$con=mysqli_connect("localhost","username","password","database");
// 检测连接
if (mysqli_connect_errno())
{
    echo "连接失败: " . mysqli_connect_error();
}
$sql = "DELETE FROM Persons WHERE LastName='Griffin'";
mysqli_query($con,$sql);
mysqli_close($con);
?>

删除数据后：

FirstNameLastNameAgeGlennQuagmire33

参考

数据库扩
MySQL Drivers and Plugins
PDO和MySQLi区别与选择?
PHP数据库

以上就是文章全部内容，感谢你的辛苦阅读。对你有帮助的可以关注此专栏，不定期更新文章，在此也准备了一些文档资料给大家。

《PHP进阶、面试；文档、视频资源免费获取》，可复制链接后用石墨文档 App 或小程序打开shimo.im

weixin_39960319

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql if语句的使用_PHP-MySQL简明手册

PHP 的MySQL连接当用PHP访问数据库时，除了PHP自带的数据库驱动，我们一般还有两种比较好的选择：PDO和MySQLi。注意：自PHP5.5开始，传统的mysql扩展已经废弃，只能使用PHP提供的MySQLi扩展或PDO扩展PDO和MySQLi区别如果你的项目需要在多种数据库中切换，建议使用 PDO;若选用MySQLi,在多种数据库中切换时候，你需要重新编写所有代码。MySQLi专门针对M...
复制链接

扫一扫