2、MYSQL

来源：互联网发布：ubuntu 17.10 安装wine 编辑：程序博客网时间：2024/06/01 16:55

1、创建 MySQL 数据库

<?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();?>

2、创建 MySQL 表，每个表都应该有一个主键(本列为 “id” 列)，主键必须包含唯一的值。

<?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();?>

3、向 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);} $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();?>

4、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); //sss表示后面为3个字符串// 设置参数并执行$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();?>

5、PHP MySQL 读取数据

<?php$servername = "localhost";$username = "username";$password = "password";$dbname = "myDB";// 创建连接$conn = new mysqli($servername, $username, $password, $dbname);// Check connectionif ($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 "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";    }} else {    echo "0 结果";}$conn->close();?>

6、查找具体数据

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

7、修改某个具体数据

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

8、删除数据

<?php$con=mysqli_connect("localhost","username","password","database");// 检测连接if (mysqli_connect_errno()){    echo "连接失败: " . mysqli_connect_error();}mysqli_query($con,"DELETE FROM Persons WHERE LastName='Griffin'");//指定某个数据即删除其所在行mysqli_close($con);?>

阅读全文

0 0