PHP操作数据库

error_reporting(0);
$dbhost = 'localhost';  // mysql服务器主机地址
$dbuser = 'root';  // mysql用户名
$dbpass = '123456';// mysql用户名密码
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
    die('数据库连接失败');
}
echo '数据库连接成功'; 

创建数据库（推荐学习：《mysql视频教程》）

$sql = 'CREATE DATABASE RUNOOB';
$retval = mysqli_query($conn,$sql );
if(! $retval )
{
    die('数据库创建失败');
}
echo "数据库创建成功"; 

删除数据库

$sql = 'DROP DATABASE RUNOOB';
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
    die('数据库删除失败');
}
echo "数据库删除成功"; 

选择数据库

mysqli_select_db($conn, 'RUNOOB' ); 

数据类型

数值、日期/时间、字符串

数值类型
tinyint
smallint
medium
int/integer
bigint
float
double
decimal
日期/时间
date
time
datetime
timestamp
字符串
char
varchar
tinyblob
tinytext
blob
text
mediumblob
mediumtext
longblob
longtext 

创建数据表

$sql = "CREATE TABLE runoob_tbl( ".
   "runoob_id INT NOT NULL AUTO_INCREMENT, ".
   "runoob_title VARCHAR(100) NOT NULL, ".
   "runoob_author VARCHAR(40) NOT NULL, ".
   "submission_date DATE, ".
   "PRIMARY KEY ( runoob_id ))ENGINE=InnoDB DEFAULT CHARSET=utf8; ";
mysqli_select_db( $conn, 'RUNOOB' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
    die('创建数据表失败');
}
echo "创建数据表成功"; 

删除数据表

$sql = "DROP TABLE runoob_tbl";
mysqli_select_db( $conn, 'RUNOOB' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
  die('删除数据表失败');
}
echo "删除数据表成功"; 

插入数据

// 设置编码，防止中文乱码
mysqli_query($conn , "set names utf8");
 
$runoob_title = '学习 Python';
$runoob_author = 'RUNOOB.COM';
$submission_date = '2016-03-06';
 
$sql = "INSERT INTO runoob_tbl ".
   "(runoob_title,runoob_author, submission_date) ".
   "VALUES ".
   "('$runoob_title','$runoob_author','$submission_date')";
 
 
 
mysqli_select_db( $conn, 'RUNOOB' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
  die('插入数据失败');
}
echo "数据插入成功"; 

查询数据

// 设置编码，防止中文乱码
mysqli_query($conn , "set names utf8");
 
$sql = 'SELECT runoob_id, runoob_title, 
   runoob_author, submission_date
   FROM runoob_tbl';
 
mysqli_select_db( $conn, 'RUNOOB' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
    die('无法读取数据');
}
echo '<h2>菜鸟教程 mysqli_fetch_array 测试<h2>';
echo '<table border="1"><tr><td>教程 ID</td><td>标题</td><td>作者</td><td>提交日期</td></tr>';
while($row = mysqli_fetch_array($retval, MYSQLI_ASSOC))
{
    echo "<tr><td> {$row['runoob_id']}</td> ".
    "<td>{$row['runoob_title']} </td> ".
    "<td>{$row['runoob_author']} </td> ".
    "<td>{$row['submission_date']} </td> ".
    "</tr>";
}
echo '</table>'; 

WHERE

// 设置编码，防止中文乱码
mysqli_query($conn , "set names utf8");
 
// 读取 runoob_author 为 RUNOOB.COM 的数据
$sql = 'SELECT runoob_id, runoob_title, 
   runoob_author, submission_date
   FROM runoob_tbl
   WHERE runoob_author="RUNOOB.COM"';
 
mysqli_select_db( $conn, 'RUNOOB' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
    die('无法读取数据: ' . mysqli_error($conn));
}
echo '<h2>菜鸟教程 MySQL WHERE 子句测试<h2>';
echo '<table border="1"><tr><td>教程 ID</td><td>标题</td><td>作者</td><td>提交日期</td></tr>';
while($row = mysqli_fetch_array($retval, MYSQL_ASSOC))
{
    echo "<tr><td> {$row['runoob_id']}</td> ".
    "<td>{$row['runoob_title']} </td> ".
    "<td>{$row['runoob_author']} </td> ".
    "<td>{$row['submission_date']} </td> ".
    "</tr>";
}
echo '</table>';
// 释放内存
mysqli_free_result($retval); 

UPDATE

// 设置编码，防止中文乱码
mysqli_query($conn , "set names utf8");
 
$sql = 'UPDATE runoob_tbl
   SET runoob_title="学习 Python"
   WHERE runoob_id=3';
 
mysqli_select_db( $conn, 'RUNOOB' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
    die('无法更新数据: ' . mysqli_error($conn));
}
echo '数据更新成功！'; 

DELETE

// 设置编码，防止中文乱码
mysqli_query($conn , "set names utf8");
 
$sql = 'DELETE FROM runoob_tbl
   WHERE runoob_id=3';
 
mysqli_select_db( $conn, 'RUNOOB' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
    die('无法删除数据: ' . mysqli_error($conn));
}
echo '数据删除成功！'; 

LINK

// 设置编码，防止中文乱码
mysqli_query($conn , "set names utf8");
 
$sql = 'SELECT runoob_id, runoob_title, 
   runoob_author, submission_date
   FROM runoob_tbl
   WHERE runoob_author LIKE "%COM"';
 
mysqli_select_db( $conn, 'RUNOOB' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
    die('无法读取数据: ' . mysqli_error($conn));
}
echo '<h2>菜鸟教程 mysqli_fetch_array 测试<h2>';
echo '<table border="1"><tr><td>教程 ID</td><td>标题</td><td>作者</td><td>提交日期</td></tr>';
while($row = mysqli_fetch_array($retval, MYSQLI_ASSOC))
{
    echo "<tr><td> {$row['runoob_id']}</td> ".
    "<td>{$row['runoob_title']} </td> ".
    "<td>{$row['runoob_author']} </td> ".
    "<td>{$row['submission_date']} </td> ".
    "</tr>";
}
echo '</table>'; 

排序

$sql = 'SELECT runoob_id, runoob_title, 
   runoob_author, submission_date
   FROM runoob_tbl
   ORDER BY  submission_date ASC(DESC)';
升序(降序)
默认升序 

以上就是PHP操作数据库的详细内容，更多请关注Gxl网其它相关文章！

查看更多关于PHP操作数据库的详细内容...