创建数据库,该命令的作用:
1. 如果数据库不存在则创建,存在则不创建。
2. 创建RUNOOB数据库,并设定编码集为utf8
CREATE DATABASE IF NOT EXISTS RUNOOB DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
//显示数据库
show databases;
use db_test;
drop database db_test;
//创建表
create table if not exists db_person (id int(20) primary key auto_increment not null,name varchar(20) not null);
//查看表结构
show columns from db_person;
describe db_person;
//修改表结构
alter
//重命名表
rename table db_person to db_persons;
//插入
insert into db_persons (id,name) value (0,"tom");
//
create table if not exists db_person(
id int auto_increment primary key,
username varchar(50) not null,
password varchar(50) not null,
email varchar(50) not null);
insert into db_person (username,password) values ('tom','123456');
//选择数据库
<?php
$dbhost = 'localhost:3306'; // mysql服务器主机地址
$dbuser = 'root'; // mysql用户名
$dbpass = '123456'; // mysql用户名密码
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die('连接失败: ' . mysqli_error($conn));
}
echo '连接成功';
mysqli_select_db($conn, 'RUNOOB' );
mysqli_close($conn);
?>
//创建表
<?php
$dbhost = 'localhost:3306'; // mysql服务器主机地址
$dbuser = 'root'; // mysql用户名
$dbpass = '123456'; // mysql用户名密码
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die('连接失败: ' . mysqli_error($conn));
}
echo '连接成功<br />';
$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('数据表创建失败: ' . mysqli_error($conn));
}
echo "数据表创建成功\n";
mysqli_close($conn);
?>
//添加数据
<?php
$dbhost = 'localhost:3306'; // mysql服务器主机地址
$dbuser = 'root'; // mysql用户名
$dbpass = '123456'; // mysql用户名密码
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die('连接失败: ' . mysqli_error($conn));
}
echo '连接成功<br />';
// 设置编码,防止中文乱码
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')";
insert into db_person (firstname,lastname,email) values ('tom','jery','709@.com');
mysqli_select_db( $conn, 'RUNOOB' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
die('无法插入数据: ' . mysqli_error($conn));
}
echo "数据插入成功\n";
mysqli_close($conn);
?>
//查询数据
<?php
$dbhost = 'localhost:3306'; // mysql服务器主机地址
$dbuser = 'root'; // mysql用户名
$dbpass = '123456'; // mysql用户名密码
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die('连接失败: ' . mysqli_error($conn));
}
// 设置编码,防止中文乱码
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('无法读取数据: ' . 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>';
mysqli_close($conn);
?>