PHP+MySQL

链接和关闭

<?php
    //强制显示utf-8
    header("Content-type: text/html; charset=utf-8"); 
    $servername = "localhost";
    $username = "root";
    $password = "";

    $con = mysql_connect($servername,$username,$password);
    // 检测连接
    if (!$con) {
        die("Connection failed: " . mysql_error());
    } 
    echo "数据库链接成功";
    mysql_close($con);
    echo "数据库关闭";
?>

创建数据库&创建表

<?php
    //强制显示utf-8
    header("Content-type: text/html; charset=utf-8"); 
    $servername = "localhost";
    $username = "root";
    $password = "";
    
    $con = mysql_connect($servername,$username,$password);
    // 检测连接
    if (!$con) {
        die("Connection failed: " . mysql_error());
    } 
    //创建数据库
    if (mysql_query("CREATE DATABASE my_db",$con)) {
        # code...
        echo "Database create";
    }else {
        echo "Error createing database:".mysql_error();
    }
    //create table in my_db database
    mysql_select_db("my_db",$con);
    //创建了主键和需要的字段
    $sql = "CREATE TABLE Person
    (
        personID int NOT NULL primary key AUTO_INCREMENT, 
        FirstName varchar(15),
        LastName varchar(15),
        Age int
    )";
    mysql_query($sql,$con);
    mysql_close($con);
    echo "<br>执行完成-数据库关闭";

?>

MySQL数据类型

4D134F37-DC97-4744-ABE2-CD6267B3DA6E.png

插入数据

<?php
    //强制显示utf-8
    header("Content-type: text/html; charset=utf-8"); 
    $servername = "localhost";
    $username = "root";
    $password = "";
    
    $con = mysql_connect($servername,$username,$password);
    // 检测连接
    if (!$con) {
        die("Connection failed: " . mysql_error());
    } 
    //创建数据库
    if (mysql_query("CREATE DATABASE my_db",$con)) {
        # code...
        echo "Database create";
    }else {
        echo "Error createing database:".mysql_error();
    }
    //create table in my_db database
    mysql_select_db("my_db",$con);
    //创建了主键和需要的字段
    mysql_query("INSERT INTO Person (FirstName,LastName,Age)
        Values ('Glenn1','Quagmire','33')");
    mysql_query("INSERT INTO Person (FirstName,LastName,Age)
        Values ('aaaaa','bbbb','10')");
    mysql_close($con);
    echo "<br>执行完成-数据库关闭";

?>

使用表单插入数据

post.php

<html>
<body>

<form action="dbtest.php" method="post">
Firstname: <input type="text" name="firstname" />
Lastname: <input type="text" name="lastname" />
Age: <input type="text" name="age" />
<input type="submit" />
</form>

</body>
</html>

dbtest.php

<?php
    //强制显示utf-8
    header("Content-type: text/html; charset=utf-8"); 
    $servername = "localhost";
    $username = "root";
    $password = "";
    
    $con = mysql_connect($servername,$username,$password);
    // 检测连接
    if (!$con) {
        die("Connection failed: " . mysql_error());
    } 
    //创建数据库
    if (mysql_query("CREATE DATABASE my_db",$con)) {
        # code...
        echo "Database create";
    }else {
        echo "Error createing database:".mysql_error()."<br>";
    }
    //create table in my_db database
    mysql_select_db("my_db",$con);
    
    $sql = "INSERT INTO Person (FirstName,LastName,Age) 
        VALUES ('$_POST[firstname]','$_POST[lastname]','$_POST[age]')";
    if (!mysql_query($sql,$con)) {
        # code...
        die('Error:'.mysql_error());
    }
    echo "添加了一条数据";
    mysql_close($con);
    echo "<br>执行完成-数据库关闭";

?>

查询

<?php
    //强制显示utf-8
    header("Content-type: text/html; charset=utf-8"); 
    $servername = "localhost";
    $username = "root";
    $password = "";
    
    $con = mysql_connect($servername,$username,$password);
    // 检测连接
    if (!$con) {
        die("Connection failed: " . mysql_error());
    } 
    //创建数据库
    if (mysql_query("CREATE DATABASE my_db",$con)) {
        # code...
        echo "Database create";
    }else {
        echo "Error createing database:".mysql_error()."<br>";
    }
    // table in my_db database
    mysql_select_db("my_db",$con);
    
    $result = mysql_query("SELECT * FROM Person");

    while ($row = mysql_fetch_array($result)) {
        # code...
        echo "姓名:".$row['FirstName']."   ".$row['LastName'];
        echo "<br>";
    }

    mysql_close($con);
    echo "<br>执行完成-数据库关闭";

?>

条件查询

运算符

D766BF81-B7E8-419E-BE77-2EE444BFF849.png

$result = mysql_query("SELECT * FROM Person WHERE FirstName = 'aaaaa' ");

排序

默认升序

$result = mysql_query("SELECT * FROM Persons ORDER BY age");

使用 DESC 关键词来设定降序排序（9 在 1 之前，"p" 在 "a" 之前）：

SELECT column_name(s)
FROM table_name
ORDER BY column_name DESC

可以根据多个列进行排序。当按照多个列进行排序时，只有第一列相同时才使用第二列：

SELECT column_name(s)
FROM table_name
ORDER BY column_name1, column_name2

更新数据

UPDATE table_name
SET column_name = new_value
WHERE column_name = some_value

$sql = mysql_query("UPDATE Person SET Age = '100' WHERE FirstName = 'aaaaa'");

删除数据

DELETE FROM table_name
WHERE column_name = some_value

$sql = mysql_query("DELETE FROM Person WHERE FirstName = '11'");

最后编辑于：2017.12.03 06:43:03