PHP执行sql
PHP连接MySQL
连接数据库
<?php
$severname='localhost';
$username='root';
$password='123456';
$dbanme='数据库名'
//创建连接
$conn = new mysqli($servername, $username,$password, $dbname);
//连接检测
if ($conn->connect_error) {
die("连接失败: " . $conn->connect_error);
} else {
echo "连接成功";
$conn->close();
}
PHP执行sql
规定由 mysqli_query()、mysqli_store_result() 或 mysqli_use_result() 返回的结果集标识符
mysqli_query() 函数执行某个针对数据库的查询
创建数据库
<?php
$servername='localhost';
$username='root';
$password='root';
//创建连接
$conn = new mysqli($servername,$username,$password);
//检测连接
if ($conn->connect_error)
{
die('连接失败：'.$conn->connect_error);
}else{
echo '连接成功!';
}
//创建数据库
$sql="create database myDB";
if($conn->query($sql) === TRUE){
echo '数据库创建成功';
}else{
echo '数据库创建失败：'.$conn->error;
}
$conn->close();
<?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);
}
创建表
- DEFAULT value - 设置默认值
- UNSIGNED - 使用无符号数值类型，0 及正数
- PRIMARY KEY - 设置数据表中每条记录的唯一标识。 通常列的 PRIMARY KEY 设置为 ID 数值，与 AUTO_INCREMENT 一起使用。
<?php
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "myDB";
// 创建连接
$conn = new mysqli($servername, $username, $password, $dbname);
// 检测连接
if ($conn->connect_error) {
die("连接失败: " . $conn->connect_error);
}
//sql语句
$sql="create table users(
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 "表创建成功！";
} else {
echo "创建数据表错误: " . $conn->error;
}
$conn->close();
插入数据
<?php
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "myDB";
// 创建连接
$conn = new mysqli($servername, $username, $password, $dbname);
// 检测连接
if ($conn->connect_error) {
die("连接失败: " . $conn->connect_error);
}
//sql语句
$sql = "INSERT INTO users (firstname, lastname, email)
VALUES ('John', 'Doe', 'john@example.com')";
if ($conn->query($sql)===TRUE){
echo "表创建成功！";
} else {
echo "创建数据表错误: " . $conn->error;
}
$conn->close();
插入多条数据
<?php
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "myDB";
// 创建连接
$conn = new mysqli($servername, $username, $password, $dbname);
// 检测连接
if ($conn->connect_error) {
die("连接失败: " . $conn->connect_error);
}
//sql语句
$sql = "INSERT INTO users (firstname, lastname, email)
VALUES ('John', 'Doe', 'john@example.com');";
$sql .= "INSERT INTO users (firstname, lastname, email)
VALUES ('Mary', 'Moe', 'mary@example.com');";
$sql .= "INSERT INTO users (firstname, lastname, email)
VALUES ('Julie', 'Dooley', 'julie@example.com')";
if ($conn->multi_query($sql)===TRUE){
echo "表创建成功！";
} else {
echo "创建数据表错误: " . $conn->error;
}
$conn->close();
预处理语句
预处理：预留的值使用参数 "?" 标记
INSERT INTO MyGuests (firstname, lastname, email) VALUES(?, ?, ?)
？的使用：可将问号替换为整型，字符串，双精度浮点型和布尔值
$stmt->bind_param("sss", $firstname, $lastname, $email);
bind_param() 函数：
该函数绑定了 SQL 的参数，且告诉数据库参数的值。 "sss" 参数列处理其余参数的数据类型。s 字符告诉数据库该参数为字符串
<?php
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "myDB";
// 创建连接
$conn = new mysqli($servername, $username, $password, $dbname);
// 检测连接
if ($conn->connect_error) {
die("连接失败: " . $conn->connect_error);
}
//预处理及绑定
$stmt=$conn->prepare("insert into users (firstname,lastname,email) values (?,?,?)");
$stmt->bind_param('sss',$firstanme,$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();
读取数据
<?php
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "myDB";
// 创建连接
$conn = new mysqli($servername, $username, $password, $dbname);
// 检测连接
if ($conn->connect_error) {
die("连接失败: " . $conn->connect_error);
}
//读取数据
$sql = "SELECT id, firstname, lastname FROM users";//读取三个字段，将其结果赋值给$result
$result = $conn->query($sql);
if ($result->num_rows > 0) {//函数 num_rows() 判断返回的数据
// 输出数据
while($row = $result->fetch_assoc()) {//如果返回的是多条数据，函数 fetch_assoc() 将结合集放入到关联数组并循环输出
echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
}
} else {
echo "0 结果";
}
$conn->close();
<?php
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "myDB";
// 创建连接
$conn = new mysqli($servername, $username, $password, $dbname);
// 检测连接
if ($conn->connect_error) {
die("连接失败: " . $conn->connect_error);
}
//读取数据
$sql = "SELECT id, firstname, lastname FROM users";
$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);
where子句
<?php
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "myDB";
// 创建连接
$conn = new mysqli($servername, $username, $password, $dbname);
// 检测连接
if ($conn->connect_error) {
die("连接失败: " . $conn->connect_error);
}
//读取数据
$sql = "SELECT * from users where firstanem=John";
$result = mysqli_query($conn, $sql);
while($row = mysqli_fetch_array($result))
{
echo $row['FirstName'] . " " . $row['LastName'];
echo "<br>";
}
$conn->close();
order by
<?php
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "myDB";
// 创建连接
$conn = new mysqli($servername, $username, $password, $dbname);
// 检测连接
if ($conn->connect_error) {
die("连接失败: " . $conn->connect_error);
}
//读取数据
$sql = "SELECT * from users order by age";
$result = mysqli_query($conn, $sql);
while($row = mysqli_fetch_array($result))
{
echo $row['FirstName'];
echo " " . $row['LastName'];
echo " " . $row['Age'];
echo "<br>";
}
$conn->close();
update
<?php
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "myDB";
// 创建连接
$conn = new mysqli($servername, $username, $password, $dbname);
// 检测连接
if ($conn->connect_error) {
die("连接失败: " . $conn->connect_error);
}
//读取数据
$sql = "update users set age=18 where firstname=’john‘ and lastname=’doe‘";
$result = mysqli_query($conn, $sql);
$conn->close();
Delete
<?php
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "myDB";
// 创建连接
$conn = new mysqli($servername, $username, $password, $dbname);
// 检测连接
if ($conn->connect_error) {
die("连接失败: " . $conn->connect_error);
}
//读取数据
$sql = "delete from users where lastname='doe'";
$result = mysqli_query($conn, $sql);
$conn->close();
