Home>Article>Backend Development> PHP operation database
Connect to the database
error_reporting(0); $dbhost = 'localhost'; // mysql服务器主机地址 $dbuser = 'root'; // mysql用户名 $dbpass = '123456'; // mysql用户名密码 $conn = mysqli_connect($dbhost, $dbuser, $dbpass); if(! $conn ) { die('数据库连接失败'); } echo '数据库连接成功';
Create a database (recommended learning:"mysql video tutorial")
$sql = 'CREATE DATABASE RUNOOB'; $retval = mysqli_query($conn,$sql ); if(! $retval ) { die('数据库创建失败'); } echo "数据库创建成功";
Delete database
$sql = 'DROP DATABASE RUNOOB'; $retval = mysqli_query( $conn, $sql ); if(! $retval ) { die('数据库删除失败'); } echo "数据库删除成功";
Select database
mysqli_select_db($conn, 'RUNOOB' );
Data type
Value, date/time, string
数值类型 tinyint smallint medium int/integer bigint float double decimal 日期/时间 date time datetime timestamp 字符串 char varchar tinyblob tinytext blob text mediumblob mediumtext longblob longtext
Create data table
$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 "创建数据表成功";
Delete data table
$sql = "DROP TABLE runoob_tbl"; mysqli_select_db( $conn, 'RUNOOB' ); $retval = mysqli_query( $conn, $sql ); if(! $retval ) { die('删除数据表失败'); } echo "删除数据表成功";
Insert data
// 设置编码,防止中文乱码 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 "数据插入成功";
Query data
// 设置编码,防止中文乱码 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 '菜鸟教程 mysqli_fetch_array 测试
'; echo '
教程 ID | 标题 | 作者 | 提交日期 |
{$row['runoob_id']} | ". "{$row['runoob_title']} | ". "{$row['runoob_author']} | ". "{$row['submission_date']} | ". "
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 '菜鸟教程 MySQL WHERE 子句测试
'; echo '
教程 ID | 标题 | 作者 | 提交日期 |
{$row['runoob_id']} | ". "{$row['runoob_title']} | ". "{$row['runoob_author']} | ". "{$row['submission_date']} | ". "
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 '菜鸟教程 mysqli_fetch_array 测试
'; echo '
教程 ID | 标题 | 作者 | 提交日期 |
{$row['runoob_id']} | ". "{$row['runoob_title']} | ". "{$row['runoob_author']} | ". "{$row['submission_date']} | ". "
Sort
$sql = 'SELECT runoob_id, runoob_title, runoob_author, submission_date FROM runoob_tbl ORDER BY submission_date ASC(DESC)'; 升序(降序) 默认升序
The above is the detailed content of PHP operation database. For more information, please follow other related articles on the PHP Chinese website!