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' );

数据类型

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

数值类型tinyintsmallintmediumint/integerbigintfloatdoubledecimal日期/时间datetimedatetimetimestamp字符串charvarchartinyblobtinytextblobtextmediumblobmediumtextlongbloblongtext

创建数据表

$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)';升序(降序)默认升序

更多相关文章

随机推荐