php与mysql连接

一.mysql的连接：

<?php

header("Content-Type:text/html;charset=utf8");

$servername="localhost";

$username="root";

$password="password";

$link=new mysqli($servername,$username,$password);

if($link){

　　echo "创建成功";

}else{

　　echo "创建失败: “.$link->connect_error;

}

?>

二.创建数据库和删除数据库

<?php
header("Content-Type:text/html;charset=utf8");
$servername="localhost";
$username="root";
$password="password";
$link = mysqli_connect($servername,$username,$password);
if(!$link){
    die("连接失败: ".mysqli_connect_error());
}
$sql = "CREATE DATABASE data2";
if(mysqli_query($link,$sql)){
    echo "数据库创建成功";
} else{
    echo "Error creating database: ".mysqli_error($link);
}

$sql = "drop database data2";
if($link->query($sql)){
    echo "删除数据库成功";
}else {
    echo "删除数据库失败: " . $link->error;
}
$link->close();

?>
创建：$sql = "create database 数据库名";
删除：$sql = "drop database 数据库名";
三 创建数据表和删除数据表

<?php
header("Content-Type:text/html;charset=utf8");
$servername="localhost";
$username="root";
$password="password";
$dbname="data";
$link = new mysqli($servername,$username,$password,$dbname);//注意这里需要添加所选的数据库
if($link->connect_error){
    die("连接失败: ".$link->connect_error);
}
$sql = "CREATE TABLE MyGuests3(
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30)  NOT NULL,
lastname VARCHAR(30)   NOT NULL,
email VARCHAR(50)
)";
if($link->query($sql)){
    echo "数据表创建成功";
}else{
    echo "创建数据表失败: ".$link->error;
}
$sql = "drop table myguests2,myguests3";

}

$link->close();
?>
创建数据表：$sql = "create table 数据表名（内容）”;
删除数据表:$sql = "drop table 数据表名";
四 插入数据

<?php
header("Content-Type:text/html;charset=utf8");
$servername="localhost";
$username="root";
$password="password";
$dbname="data";
$link=mysqli_connect($servername,$username,$password,$dbname);
if(!$link){
    die("connection failed: ".mysqli_connect_error());
}
$sql ="insert into MyGuests2(firstname,lastname,email) values('John','Doe','john@example.com'),('Julie','Dooely','julie@example.com')";
if(mysqli_query($link,$sql)){
    echo "插入数据成功";
}else{
    echo "Error ". $sql . mysqli_error($link);
}
mysqli_close($link);
?>
插入方法:$sql = "insert into 数据表名(插入所含的内容) values(插入所需的内容1）,(插入所需的内容2)";
预处理插入方法：

<?php
header("Content-Type:text/html;charset=utf8");
$servername="localhost";
$username="root";
$password="password";
$dbname="data";
$conn = new mysqli($servername, $username, $password, $dbname);
// 检测连接
if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}
// 预处理及绑定
$sql="INSERT INTO MyGuests (firstname, lastname, email) VALUES(?, ?, ?)";
$stmt = $conn->prepare($sql);
$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();
?>

"INSERT INTO MyGuests (firstname, lastname, email) VALUES(?, ?, ?)";
这句中？号可以被替换为整型，字符串，浮点型和布尔值。

$stmt->bind_param("sss", $firstname, $lastname, $email);
sss代表即将输入三个字符串
i-整型   d-浮点型    s-字符串   b-布尔型
五 读取数据以及更新，删除数据

<?php
header("Content-Type:text/html;charset=utf8");
$servername="localhost";
$username="root";
$password="password";
$dbname="data";
$link = new mysqli($servername,$username,$password,$dbname);
if($link->connect_error){
    die("连接失败: ".$link->connect_error());
}
$sql = "select id ,firstname , lastname from MyGuests";//选择所需读取的数据
$result = $link->query($sql);
if($result->num_rows>0){//获取结果行数
    while($row = $result->fetch_assoc()){//若读取到结果，返回true值
        echo "<br> id:" . $row["id"] . " - Name: ".$row["firstname"]. " ".$row["lastname"];
    }
}else{
    echo "0个结果";
}
$sql = "select *from MyGuests where firstname='Mary'";//根据内容进行选择
$result = $link ->query($sql);
while($row=$result->fetch_assoc()){
    echo "<br>".$row["firstname"]." ".$row["lastname"];
}
$sql = "select *from MyGuests order by firstname asc , id desc";//对数据表进行排序，asc为增序，desc为减序
$result = $link->query($sql);
while($row=$result->fetch_assoc()){
    echo "<br> id:" . $row["id"] . " - Name: ".$row["firstname"]. " ".$row["lastname"];
}
$sql = "update MyGuests set firstname='cool' where firstname='mary'";//更新数据
$link->query($sql);
$sql = "delete from MyGuests where firstname='cool'";//删除数据
$link ->query($sql);
$link->close();
?>