MySQL数据库之MySQL扩展

连接数据库

数据库在网站中的位置

• 前端 -> HTTP协议 -> PHP服务器 -> MySQL数据库

开启MySQL扩展

• 在php.ini中开启mysqli扩展
  • extension=php_mysqli.dll
  • 开启扩展后重启服务器，就可以使用mysqli_函数了

连接数据库

创建news数据库

MariaDB [sel]> drop table if exists news;
# `Query OK, 0 rows affected (0.025 sec)`

MariaDB [sel]> create table news(
    -> id int unsigned auto_increment primary key comment '主键',
    -> title varchar(20) not null comment '标题',
    -> content text not null comment '内容',
    -> createtime int not null comment '添加时间'
    -> )engine=innodb charset=gbk comment '新闻表';
# `Query OK, 0 rows affected (0.024 sec)`

MariaDB [sel]> insert into news values (null,'基本知识','第1章 什么是Javascript',unix_timestamp());
# `Query OK, 1 row affected (0.013 sec)`

MariaDB [sel]> insert into news values (null,'基本知识','第2章 HTML中的Javascript',unix_timestamp());
# `Query OK, 1 row affected (0.015 sec)`

MariaDB [sel]> select * from news;
+----+----------+--------------------------+------------+
| id | title    | content                  | createtime |
+----+----------+--------------------------+------------+
|  1 | 基本知识  | 第1章 什么是Javascript    | 1607050534 |
|  2 | 基本知识  | 第2章 HTML中的Javascript  | 1607050590 |
+----+----------+--------------------------+------------+
# `2 rows in set (0.001 sec)`

连接数据库

• 语法
  • @ 隐藏错误
  • or die 条件显示

mysqli_connect(主机IP，用户名，密码，数据库名，端口号) 
//如果端口号是3306可以省略
mysqli_connect_error()：获取连接数据库的错误信息
mysqli_connect_errno()：获取连接数据库的错误编码
mysqli_set_charset(连接对象,字符编码)  

<?php
$link= @mysqli_connect('localhost','root','','sel','3306');
var_dump($link);
if(mysqli_connect_error()){
	echo '错误号：'.mysqli_connect_errno(),'<br>';	
	echo '错误信息：'.mysqli_connect_error();		
	exit;
}else{
    echo '数据库连接成功！';
}
mysqli_set_charset($link,'utf8');	
?>
// 与数据库相关用utf8，与页面显示相关用utf-8

操作数据语句

• 数据操作语句

  • 通过mysqli_query()执行SQL语句
  • 增、删、改语句执行成功返回true,失败返回false

• 使用的函数

  • mysqli_query() 执行SQL语句
  • mysqli_insert_id() 获取插入记录自动增长的ID
  • mysqli_affected_rows() 获取受影响的记录数
  • mysqli_error() 获取执行SQL语句的错误信息
  • mysqli_errno() 获取执行SQL语句的错误码

增操作 insert

<?php
$link= @mysqli_connect('localhost','root','','sel','3306');
mysqli_set_charset($link,'utf8');
$rs= mysqli_query($link, "insert into news values (null, '基本知识', '第三章 语言基础', unix_timestamp())");
if($rs){
    echo '自动增长的编号是：'.mysqli_insert_id($link).'<br>';
}
?>

改操作 update

<?php
$link= @mysqli_connect('localhost','root','','sel','3306');
mysqli_set_charset($link,'utf8');
$rs= mysqli_query($link, "update news set content='第3章 语言基础' where id=3");
if($rs){
    echo '受影响的记录数是：'.mysqli_affected_rows($link);
}
?>

删操作 delete

<?php
$link= @mysqli_connect('localhost','root','','sel','3306');
mysqli_set_charset($link,'utf8');
$rs= mysqli_query($link, "delete from news where id>3");
if($rs){
    echo '受影响的记录数是'.mysqli_affected_rows($link);
}
?>

数据查询语句

• 数据查询语句

  • 数据查询用select、desc、show，成功会返回结果集，失败返回false

• 使用的函数

  • mysqli_fetch_assoc() 将一条数组匹配关联数组
  • mysqli_fetch_row() 将一条记录匹配成索引数组
  • mysqli_fetch_array() 将一条记录匹配成既有关联数组又有索引数组
  • mysqli_fetch_all() 匹配所有记录
  • mysqli_num_rows() 总行数
  • mysqli_num_fields() 总记录数
  • mysqli_free_result() 销毁结果集
  • mysqli_close() 关闭连接

执行查询语句

<?php
$link= @mysqli_connect('localhost','root','','sel','3306') or die ('错误信息：'.mysqli_connect_error());
mysqli_query($link,'set names utf8');
// 执行查询语句
$rs= mysqli_query($link,'select * from news');
// 将对象中的一条数据匹配成索引数组,指针下移一条
$rows= mysqli_fetch_row($rs);
echo "<pre>";
print_r($rows);
echo "</pre>";
// 将对象中的一条数据匹配成关联数组,指针下移一条
$assocs= mysqli_fetch_assoc($rs);
echo "<pre>";
print_r($assocs);
echo "</pre>";
// 将对象中的一条数据匹配成索引,关联数组,指针下移一条
$arrays= mysqli_fetch_array($rs);
echo "<pre>";
print_r($arrays);
echo "</pre>";
?>

获取总列数、总行数

<?php
$link= @mysqli_connect('localhost','root','','sel','3306') or die ('错误信息：'.mysqli_connect_error());
mysqli_query($link,'set names utf8');
$rs= mysqli_query($link,'select * from news');

echo '总行数：'.mysqli_num_rows($rs),'<br>';
echo '总列数：'.mysqli_num_fields($rs),'<br>';
?>

获取所有数据

• 默认是索引数组

<?php
$link= @mysqli_connect('localhost','root','','sel','3306') or die ('错误信息：'.mysqli_connect_error());
mysqli_query($link,'set names utf8');
$rs= mysqli_query($link,'select * from news');

$list= mysqli_fetch_all($rs);		
echo '<pre>';
print_r($list);
echo '</pre>';
?>

• 匹配成索引数组

<?php
$link= @mysqli_connect('localhost','root','','sel','3306') or die ('错误信息：'.mysqli_connect_error());
mysqli_query($link,'set names utf8');
$rs= mysqli_query($link,'select * from news');

$list= mysqli_fetch_all($rs,MYSQLI_NUM);
echo '<pre>';
print_r($list);
echo '</pre>';
?>

• 匹配成关联数组

<?php
$link= @mysqli_connect('localhost','root','','sel','3306') or die ('错误信息：'.mysqli_connect_error());
mysqli_query($link,'set names utf8');
$rs= mysqli_query($link,'select * from news');

$list=mysqli_fetch_all($rs,MYSQLI_ASSOC);
echo '<pre>';
print_r($list);
echo '</pre>';
?>

• 匹配成关联、索引数组

<?php
$link= @mysqli_connect('localhost','root','','sel','3306') or die ('错误信息：'.mysqli_connect_error());
mysqli_query($link,'set names utf8');
$rs= mysqli_query($link,'select * from news');

$list=mysqli_fetch_all($rs,MYSQLI_BOTH);
echo '<pre>';
print_r($list);
echo '</pre>';
?>

销毁结果集

<?php
$link= @mysqli_connect('localhost','root','','sel','3306') or die ('错误信息：'.mysqli_connect_error());
mysqli_query($link,'set names utf8');
$rs= mysqli_query($link,'select * from news');
// 销毁结果集
mysqli_free_result($rs);

$list=mysqli_fetch_all($rs,MYSQLI_BOTH);
echo '<pre>';
print_r($list);
echo '</pre>';
?>

关闭连接

<?php
$link= @mysqli_connect('localhost','root','','sel','3306') or die ('错误信息：'.mysqli_connect_error());
// 关闭连接
mysqli_close($link);

mysqli_query($link,'set names utf8');
$rs= mysqli_query($link,'select * from news');

$list=mysqli_fetch_all($rs,MYSQLI_BOTH);
echo '<pre>';
print_r($list);
echo '</pre>';
?>

案例：新闻模块

包含文件

• 说明

  • 由于所有的操作都要连接数据库,将连接数据库的代码存放到包含文件中

• 步骤

  • 在站点下创建inc文件夹
  • 在inc下创建conn.php文件,用来连接数据库,代码就是上面连接数据库的代码

# conn.php
<?php
$link= @mysqli_connect('localhost','root','','sel') or die('错误：'.mysqli_connect_error());
mysqli_set_charset($link,'utf8');
?>

显示新闻

• 步骤
  • 连接数据库
  • 获取数据
  • 遍历循环数据

# list.php
<style type="text/css">
table{
    780px;
    border:solid 1px #000;
    margin:auto;
}
th,td{
    border:solid 1px #000;
}
</style>
<body>
<?php
//1、连接数据库
require './inc/conn.php';
//2、获取数据
$rs= mysqli_query($link,'select * from news order by id desc');	
$list= mysqli_fetch_all($rs,MYSQLI_ASSOC);		
?>
<table>
<tr>
    <th>编号</th> <th>标题</th> <th>内容</th> <th>时间</th> <th>修改</th> <th>删除</th>
    <!--3、循环显示数据-->
    <?php foreach($list as $rows):?>
    <tr>
        <td><?php echo $rows['id']?></td>
        <td><?php echo $rows['title']?></td>
        <td><?php echo $rows['content']?></td>
        <td><?php echo date('Y-m-d H:i:s',$rows['createtime'])?></td>
        <td><input type="button" value="修改" onclick=""></td>
        <td><input type="button" value="删除" onclick=""></td>
    </tr>
    <?php endforeach;?>
    <a href="./add.php">添加新闻</a>
</tr>
</table>
</body>

添加新闻

• 步骤
  • 创建表单
  • 连接数据库
  • 将新闻数据写入到数据库中

# add.php
<body>
<?php
if(!empty($_POST)) {
	//2、连接数据库
	require './inc/conn.php';
    //3、插入数据
	$time= time();	
    $sql= "insert into news values (null,'{$_POST['title']}','{$_POST['content']}',$time)";  
	if(mysqli_query($link,$sql))	//执行SQL语句
		header('location:./list.php');	//插入成功就跳转到list.php页面
	else{
		echo 'SQL语句插入失败<br>';
		echo '错误码：'.mysqli_errno($link),'<br>';
		echo '错误信息：'.mysqli_error($link);
	}
}
?>
<!--1、创建表单-->
<form method="post" action="">
	标题： <input type="text" name="title"><br> <br>
	内容:  <textarea name="content" rows="5" cols="30"></textarea><br><br>
	<input type="submit" name="button" value="提交">
</form>
</body>

删除新闻

• 步骤

  • 在list.php页面点击删除按钮，跳转到del.php页面，传递删除的id
  • 在del.php页面连接数据库
  • 通过id删除数据
  • 删除成功后，跳转到list.php

• 说明

  • 一个页面是否写HTML架构,取决于是否有显示功能
  • 如果一个页面只是做业务逻辑,没有显示功能,就不需要写HTML架构,比如del.php页面

# del.php
<?php
//1、连接数据库
require './inc/conn.php';
//2、拼接SQL语句
$sql="delete from news where id={$_GET['id']}";
//3、执行SQL语句
if(mysqli_query($link,$sql))
	header('location:./list.php');
else{
	echo '删除失败';
}
?>

修改新闻

• 步骤
  • 显示修改界面
    • 连接数据库
    • 获取修改的数据
    • 将数据显示到表单中
  • 执行修改逻辑
    • 获取新数据
    • 拼接修改的SQL语句,执行修改逻辑

# edit.php
<?php
//连接数据库
require './inc/conn.php';
//1、获取修改的数据库
$sql= "select * from news where id={$_GET['id']}";	
$rs= mysqli_query($link,$sql);	//获取修改的数据
$rows= mysqli_fetch_assoc($rs);	//将修改的数据匹配成一维关联数组
//2、执行修改的逻辑
if(!empty($_POST)) {
	$id=$_GET['id'];		
	$title=$_POST['title'];	//修改的标题
	$content=$_POST['content'];	//修改的内容
	$sql="update news set title='$title',content='$content' where id=$id"; 
	if(mysqli_query($link,$sql))
		header('location:listl.php');   //修改成功跳转到list.php页面
	else
		echo '错误：'.mysqli_error($link);
	exit;
}
?>
<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>修改页面</title>
</head>

<body>
<form method="post" action="">
	标题： <input type="text" name="title" value='<?php echo $rows['title']?>'> <br /> <br />
	内容:  <textarea name="content" rows="5" cols="30"><?php echo $rows['content']?></textarea> <br /> <br />
	<input type="submit" name="button" value="提交">
	<input type="button" value="返回" onclick="location.href='list.php'">
</form>
</body>
</html>

数据库备份与还原

• 概念
  • 数据库中的数据需要定期备份
  • 数据量小的可以一周备份一次
  • 数据量大的可以一天备份一次

数据备份

• 语法
  • mysqldump 数据库连接 数据库 > SQL文件备份地址

# mysqldump -uroot -p sel>D:Databasesel.sql
Enter password:

# 例题

-- 将data数据库中所有的表导出到data.sql中
F:wampPHPTutorialMySQLin>mysqldump -uroot -proot data>c:data.sql

-- 将data数据库中的stuinfo、stumarks表
F:wampPHPTutorialMySQLin>mysqldump -uroot -proot data stuinfo stumarks>c:data.sql

-- 导出data数据库，导出的语句中带有创建数据库的语法
F:wampPHPTutorialMySQLin>mysqldump -uroot -proot -B data>c:data1.sql

数据还原

• MySQL的source指令
  • 需要登录MySQL才能使用
  • 地址分隔符用斜线，不能用反斜线
  • source 导入的SQL文件

MariaDB [(none)]> source D:/Database/sel.sql

• 通过mysql指令数据还原
  • 不需要登录MySQL
  • mysql 连接数据库 导入的数据库名 < 导入的SQL文件

# mysql -uroot -p sel < D:/Database/sel.sql
Enter password:

# 例题

F:wampPHPTutorialMySQLin>mysql -uroot -proot data1 < c:data.sql

单例模式封装

分析

• 步骤
  • 实现单例
  • 初始化参数
  • 连接数据库
  • 操作数据
    • 执行数据操作语句（增、删、改）
    • 执行数据查询语句
      • 返回二维数组
      • 返回一维数组
      • 返回一行一列

代码实现

第一步：实现单例

<?php
class MySQLDB {
	private static $instance;
	private function __construct() {

	}
	private function __clone() {
		
	}
	public static function getInstance() {
		if(!self::$instance instanceof self)
			self::$instance=new self();
		return self::$instance;
	}
}
//测试
$db=MySQLDB::getInstance();
var_dump($db);

注意：A instanceof B，表示A是否是B的类型，返回bool值

第二步：初始化参数

<?php
//封装MySQL单例
class MySQLDB {
	private $host;		//主机地址
	private $port;		//端口号
	private $user;		//用户名
	private $pwd;		//密码
	private $dbname;	//数据接名
	private $charset;	//字符集
	private $link;		//连接对象
	private static $instance;
	private function __construct($param) {
		$this->initParam($param);		
	}
	private function __clone() {
		
	}
	//获取单例
	public static function getInstance($param=array()) {
		if(!self::$instance instanceof self)
			self::$instance=new self($param);
		return self::$instance;
	}
	//初始化参数
	private function initParam($param) {
		$this->host=$param['host']??'127.0.0.1';
		$this->port=$param['port']??'3306';
		$this->user=$param['user']??'';
		$this->pwd=$param['pwd']??'';
		$this->dbname=$param['dbname']??'';
		$this->charset=$param['charset']??'utf8';
	}
}

//测试
//配置参数
$param=array(
	'user'		=>	'root',
	'pwd'		=>	'root',
	'dbname'	=>	'data'
);
//获取单例
$db=MySQLDB::getInstance($param);
var_dump($db);

第三步：连接数据库

<?php
//封装MySQL单例
class MySQLDB {
	private $host;		//主机地址
	private $port;		//端口号
	private $user;		//用户名
	private $pwd;		//密码
	private $dbname;	//数据接名
	private $charset;	//字符集
	private $link;		//连接对象
	private static $instance;
	private function __construct($param) {
		$this->initParam($param);
		$this->initConnect();
	}
	private function __clone() {
		
	}
	//获取单例
	public static function getInstance($param=array()) {
		if(!self::$instance instanceof self)
			self::$instance=new self($param);
		return self::$instance;
	}
	//初始化参数
	private function initParam($param) {
		$this->host=$param['host']??'127.0.0.1';
		$this->port=$param['port']??'3306';
		$this->user=$param['user']??'';
		$this->pwd=$param['pwd']??'';
		$this->dbname=$param['dbname']??'';
		$this->charset=$param['charset']??'utf8';
	}
	//连接数据库
	private function initConnect() {
		$this->link=@mysqli_connect($this->host,$this->user,$this->pwd,$this->dbname);
		if(mysqli_connect_error()){
			echo '数据库连接失败<br>';
			echo '错误信息：'.mysqli_connect_error(),'<br>';
			echo '错误码：'.mysqli_connect_errno(),'<br>';
			exit;
		}
		mysqli_set_charset($this->link,$this->charset);
	}
}

//测试
//配置参数
$param=array(
	'user'		=>	'root',
	'pwd'		=>	'root',
	'dbname'	=>	'data'
);
//获取单例
$db=MySQLDB::getInstance($param);
var_dump($db);

第四步：数据操作的功能

1、执行增、删、改操作

<?php
//封装MySQL单例
class MySQLDB {
	private $host;		//主机地址
	private $port;		//端口号
	private $user;		//用户名
	private $pwd;		//密码
	private $dbname;	//数据接名
	private $charset;	//字符集
	private $link;		//连接对象
	private static $instance;
	private function __construct($param) {
		$this->initParam($param);
		$this->initConnect();
	}
	private function __clone() {
		
	}
	//获取单例
	public static function getInstance($param=array()) {
		if(!self::$instance instanceof self)
			self::$instance=new self($param);
		return self::$instance;
	}
	//初始化参数
	private function initParam($param) {
		$this->host=$param['host']??'127.0.0.1';
		$this->port=$param['port']??'3306';
		$this->user=$param['user']??'';
		$this->pwd=$param['pwd']??'';
		$this->dbname=$param['dbname']??'';
		$this->charset=$param['charset']??'utf8';
	}
	//连接数据库
	private function initConnect() {
		$this->link=@mysqli_connect($this->host,$this->user,$this->pwd,$this->dbname);
		if(mysqli_connect_error()){
			echo '数据库连接失败<br>';
			echo '错误信息：'.mysqli_connect_error(),'<br>';
			echo '错误码：'.mysqli_connect_errno(),'<br>';
			exit;
		}
		mysqli_set_charset($this->link,$this->charset);
	}
	//执行数据库的增、删、改、查
	private function execute($sql) {
		if(!$rs=mysqli_query($this->link,$sql)){
			echo 'SQL语句执行失败<br>';
			echo '错误信息：'.mysqli_error($this->link),'<br>';
			echo '错误码：'.mysqli_errno($this->link),'<br>';
			echo '错误的SQL语句：'.$sql,'<br>';
			exit;
		}
		return $rs;
	}
	/**
	*执行增、删、改
	*@return bool 成功返回true,失败返回false
	*/
	public function exec($sql) {
		$key=substr($sql,0,6);
		if(in_array($key,array('insert','update','delete')))
			return $this->execute($sql);
		else{
			echo '非法访问<br>';
			exit;
		}

	}
	//获取自动增长的编号
	public function getLastInsertId() {
		return mysqli_insert_id($this->link);
	}
}

//测试
//配置参数
$param=array(
	'user'		=>	'root',
	'pwd'		=>	'root',
	'dbname'	=>	'data'
);
//获取单例
$db=MySQLDB::getInstance($param);
//更新
//$db->exec("update news set title='青草' where id=2");
//插入
if($db->exec("insert into news values (null,'aa','bb',unix_timestamp())"))
	echo '编号是：'.$db->getLastInsertId();

2、查询结果

<?php
//封装MySQL单例
class MySQLDB {
	private $host;		//主机地址
	private $port;		//端口号
	private $user;		//用户名
	private $pwd;		//密码
	private $dbname;	//数据接名
	private $charset;	//字符集
	private $link;		//连接对象
	private static $instance;
	private function __construct($param) {
		$this->initParam($param);
		$this->initConnect();
	}
	private function __clone() {
		
	}
	//获取单例
	public static function getInstance($param=array()) {
		if(!self::$instance instanceof self)
			self::$instance=new self($param);
		return self::$instance;
	}
	//初始化参数
	private function initParam($param) {
		$this->host=$param['host']??'127.0.0.1';
		$this->port=$param['port']??'3306';
		$this->user=$param['user']??'';
		$this->pwd=$param['pwd']??'';
		$this->dbname=$param['dbname']??'';
		$this->charset=$param['charset']??'utf8';
	}
	//连接数据库
	private function initConnect() {
		$this->link=@mysqli_connect($this->host,$this->user,$this->pwd,$this->dbname);
		if(mysqli_connect_error()){
			echo '数据库连接失败<br>';
			echo '错误信息：'.mysqli_connect_error(),'<br>';
			echo '错误码：'.mysqli_connect_errno(),'<br>';
			exit;
		}
		mysqli_set_charset($this->link,$this->charset);
	}
	//执行数据库的增、删、改、查
	private function execute($sql) {
		if(!$rs=mysqli_query($this->link,$sql)){
			echo 'SQL语句执行失败<br>';
			echo '错误信息：'.mysqli_error($this->link),'<br>';
			echo '错误码：'.mysqli_errno($this->link),'<br>';
			echo '错误的SQL语句：'.$sql,'<br>';
			exit;
		}
		return $rs;
	}
	/**
	*执行增、删、改
	*@return bool 成功返回true,失败返回false
	*/
	public function exec($sql) {
		$key=substr($sql,0,6);
		if(in_array($key,array('insert','update','delete')))
			return $this->execute($sql);
		else{
			echo '非法访问<br>';
			exit;
		}

	}
	//获取自动增长的编号
	public function getLastInsertId() {
		return mysqli_insert_id($this->link);
	}

	//执行查询语句
	private function query($sql) {
		if(substr($sql,0,6)=='select' || substr($sql,0,4)=='show' || substr($sql,0,4)=='desc'){
			return $this->execute($sql);
		}else{
			echo '非法访问<br>';
			exit;
		}
	}
	/**
	*执行查询语句，返回二维数组
	*@$sql string 查询sql语句
	*@type string assoc|num|both
	*/
	public function fetchAll($sql,$type='assoc') {
		$rs=$this->query($sql);
		$type=$this->getType($type);
		return mysqli_fetch_all($rs,$type);
	}
	//匹配一维数组
	public function fetchRow($sql,$type='assoc') {
		$list=$this->fetchAll($sql,$type);
		if(!empty($list))
			return $list[0];
		return array();
	}
	//匹配一行一列
	public function fetchColumn($sql) {
		$list=$this->fetchRow($sql,'num');
		if(!empty($list))
			return $list[0];
		return null;
	}

	//获取匹配类型
	private function getType($type) {
		switch($type){
			case 'num':
				return  MYSQLI_NUM;
			case 'both':
				return  MYSQLI_BOTH;
			default:
				return  MYSQLI_ASSOC;
		}
	}
}

//测试
//配置参数
$param=array(
	'user'		=>	'root',
	'pwd'		=>	'root',
	'dbname'	=>	'data'
);
//获取单例
$db=MySQLDB::getInstance($param);
//更新
//$db->exec("update news set title='青草' where id=2");
//插入
/*
if($db->exec("insert into news values (null,'aa','bb',unix_timestamp())"))
	echo '编号是：'.$db->getLastInsertId();
*/

//查询
//$list=$db->fetchAll('select * from news','aa');
//$list=$db->fetchRow('select * from news where id=1','aa');

$list=$db->fetchColumn('select count(*) from news');

echo '<pre>';
var_dump($list);