活动公开课
网页设计01
数据库设计01
仓颉开发语言01
鸿蒙开发01-HarmonyOS第一课
CPU眼中的C++
本文档使用 MrDoc 发布
-
+
首页
数据库设计01
<div class="article-intro" id="content"> <h1>MySQL <span class="color_h1">教程</span></h1> <div class="tutintro"> <img decoding="async" src="//www.runoob.com/wp-content/uploads/2014/03/mysql.jpg" alt="mysql" width="100" height="75"> <p>MySQL 是最流行的关系型数据库管理系统,在 WEB 应用方面 MySQL 是最好的 RDBMS(Relational Database Management System:关系数据库管理系统)应用软件之一。 </p><p> 在本教程中,会让大家快速掌握 MySQL 的基本知识,并轻松使用 MySQL 数据库。</p> <p>在线测试工具:<a href="https://www.jyshare.com/front-end/7768/" rel="noopener" target="_blank">https://www.jyshare.com/front-end/7768/</a>。</p> </div><hr> <h2>什么是数据库?</h2><p> 数据库(Database)是按照数据结构来组织、存储和管理数据的仓库。</p><p> 每个数据库都有一个或多个不同的 API 用于创建,访问,管理,搜索和复制所保存的数据。</p><p> 我们也可以将数据存储在文件中,但是在文件中读写数据速度相对较慢。</p><p> 所以,现在我们使用关系型数据库管理系统(RDBMS)来存储和管理大数据量。所谓的关系型数据库,是建立在关系模型基础上的数据库,借助于集合代数等数学概念和方法来处理数据库中的数据。 </p><p> RDBMS 即关系数据库管理系统(Relational Database Management System)的特点:</p> <ul> <li>1.数据以表格的形式出现</li> <li>2.每行为各种记录名称</li> <li>3.每列为记录名称所对应的数据域</li> <li>4.许多的行和列组成一张表单</li> <li>5.若干的表单组成database</li> </ul><hr> <h2>RDBMS 术语</h2><p></p><p> 在我们开始学习MySQL 数据库前,让我们先了解下RDBMS的一些术语:</p> <ul> <li><strong>数据库:</strong> 数据库是一些关联表的集合。</li> <li><strong>数据表:</strong> 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。</li> <li><strong>列:</strong> 一列(数据元素) 包含了相同类型的数据, 例如邮政编码的数据。</li> <li><strong>行:</strong>一行(元组,或记录)是一组相关的数据,例如一条用户订阅的数据。</li> <li><strong>冗余</strong>:存储两倍数据,冗余降低了性能,但提高了数据的安全性。</li> <li><strong>主键</strong>:主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。</li> <li><strong>外键:</strong>外键用于关联两个表。</li> <li><strong>复合键</strong>:复合键(组合键)将多个列作为一个索引键,一般用于复合索引。</li> <li><strong>索引:</strong>使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。</li> <li><strong>参照完整性:</strong> 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件,目的是保证数据的一致性。</li> </ul> <p>MySQL 为关系型数据库(Relational Database Management System), 这种所谓的"关系型"可以理解为"表格"的概念, 一个关系型数据库由一个或数个表格组成, 如图所示的一个表格:</p> <p><img decoding="async" src="//www.runoob.com/wp-content/uploads/2014/03/0921_1.jpg"></p> <ul> <li>表头(header): 每一列的名称;</li> <li>列(col): 具有相同数据类型的数据的集合;</li> <li>行(row): 每一行用来描述某条记录的具体信息;</li> <li>值(value): 行的具体信息, 每个值必须与该列的数据类型相同;</li> <li><strong>键(key)</strong>: 键的值在当前列中具有唯一性。</li> </ul> <hr> <h2>MySQL数据库</h2><p> MySQL 是一个关系型数据库管理系统,由瑞典 MySQL AB 公司开发,目前属于 Oracle 公司。MySQL 是一种关联数据库管理系统,关联数据库将数据保存在不同的表中,而不是将所有数据放在一个大仓库内,这样就增加了速度并提高了灵活性。 </p> <ul> <li>MySQL 是开源的,目前隶属于 Oracle 旗下产品。</li> <li>MySQL 支持大型的数据库。可以处理拥有上千万条记录的大型数据库。</li> <li>MySQL 使用标准的 SQL 数据语言形式。</li> <li>MySQL 可以运行于多个系统上,并且支持多种语言。这些编程语言包括 C、C++、Python、Java、Perl、PHP、Eiffel、Ruby 和 Tcl 等。</li> <li>MySQL 对 PHP 有很好的支持,PHP 是很适合用于 Web 程序开发。</li> <li>MySQL 支持大型数据库,支持 5000 万条记录的数据仓库,32 位系统表文件最大可支持 4GB,64 位系统支持最大的表文件为8TB。</li> <li>MySQL 是可以定制的,采用了 GPL 协议,你可以修改源码来开发自己的 MySQL 系统。</li> </ul> <hr> <h2>在开始学习本教程前你应该了解?</h2><p> 在开始学习本教程前你应该了解 PHP 和 HTML 的基础知识,并能简单的应用。</p><p> 本教程的很多例子都跟 PHP 语言有关,我们的实例基本上是采用 PHP 语言来演示。</p><p> 如果你还不了解 PHP,你可以通过本站的 <a title="PHP 教程" href="/php/php-tutorial.html" target="_blank" rel="noopener noreferrer">PHP教程</a>来了解该语言。</p> <!-- 其他扩展 --> </div> <div class="article-body"> <div class="article-intro" id="content"> <h1>MySQL 查询数据</h1> <p>MySQL 数据库使用 <span class="marked">SELECT</span> 语句来查询数据。</p> <p> 你可以通过 <span class="marked">mysql></span> 命令提示窗口中在数据库中查询数据,或者通过 PHP 脚本来查询数据。 </p> <h3>语法</h3> <p> 以下为在 MySQL 数据库中查询数据通用的 SELECT 语法: </p> <pre class="prettyprint prettyprinted" style=""><span class="pln">SELECT column1</span><span class="pun">,</span><span class="pln"> column2</span><span class="pun">,</span><span class="pln"> </span><span class="pun">...</span><span class="pln"> FROM table_name </span><span class="pun">[</span><span class="pln">WHERE condition</span><span class="pun">]</span><span class="pln"> </span><span class="pun">[</span><span class="pln">ORDER BY column_name </span><span class="pun">[</span><span class="pln">ASC </span><span class="pun">|</span><span class="pln"> DESC</span><span class="pun">]]</span><span class="pln"> </span><span class="pun">[</span><span class="pln">LIMIT number</span><span class="pun">];</span><button class="copy-code-button" type="button" data-clipboard-text="SELECT column1, column2, ... FROM table_name [WHERE condition] [ORDER BY column_name [ASC | DESC]] [LIMIT number]; " style="display: none;"></button></pre><p><strong>参数说明:</strong></p> <ul><li><code>column1</code>, <code>column2</code>, ... 是你想要选择的列的名称,如果使用 <code>*</code> 表示选择所有列。</li><li><code>table_name</code> 是你要从中查询数据的表的名称。</li><li><code>WHERE condition</code> 是一个可选的子句,用于指定过滤条件,只返回符合条件的行。</li><li><code>ORDER BY column_name [ASC | DESC]</code> 是一个可选的子句,用于指定结果集的排序顺序,默认是升序(ASC)。</li><li><code>LIMIT number</code> 是一个可选的子句,用于限制返回的行数。</li></ul> <p>MySQL SELECT 语句简单的应用实例:</p> <div class="example"><h2 class="example">实例</h2> <div class="example_code"> <span style="color: #808080; font-style: italic;">-- 选择所有列的所有行</span><br> <span style="color: #993333; font-weight: bold;">SELECT</span> <span style="color: #66cc66;">*</span> <span style="color: #993333; font-weight: bold;">FROM</span> users;<br> <br> <span style="color: #808080; font-style: italic;">-- 选择特定列的所有行</span><br> <span style="color: #993333; font-weight: bold;">SELECT</span> username<span style="color: #66cc66;">,</span> email <span style="color: #993333; font-weight: bold;">FROM</span> users;<br> <br> <span style="color: #808080; font-style: italic;">-- 添加 WHERE 子句,选择满足条件的行</span><br> <span style="color: #993333; font-weight: bold;">SELECT</span> <span style="color: #66cc66;">*</span> <span style="color: #993333; font-weight: bold;">FROM</span> users <span style="color: #993333; font-weight: bold;">WHERE</span> is_active <span style="color: #66cc66;">=</span> <span style="color: #993333; font-weight: bold;">TRUE</span>;<br> <br> <span style="color: #808080; font-style: italic;">-- 添加 ORDER BY 子句,按照某列的升序排序</span><br> <span style="color: #993333; font-weight: bold;">SELECT</span> <span style="color: #66cc66;">*</span> <span style="color: #993333; font-weight: bold;">FROM</span> users <span style="color: #993333; font-weight: bold;">ORDER</span> <span style="color: #993333; font-weight: bold;">BY</span> birthdate;<br> <br> <span style="color: #808080; font-style: italic;">-- 添加 ORDER BY 子句,按照某列的降序排序</span><br> <span style="color: #993333; font-weight: bold;">SELECT</span> <span style="color: #66cc66;">*</span> <span style="color: #993333; font-weight: bold;">FROM</span> users <span style="color: #993333; font-weight: bold;">ORDER</span> <span style="color: #993333; font-weight: bold;">BY</span> birthdate <span style="color: #993333; font-weight: bold;">DESC</span>;<br> <br> <span style="color: #808080; font-style: italic;">-- 添加 LIMIT 子句,限制返回的行数</span><br> <span style="color: #993333; font-weight: bold;">SELECT</span> <span style="color: #66cc66;">*</span> <span style="color: #993333; font-weight: bold;">FROM</span> users <span style="color: #993333; font-weight: bold;">LIMIT</span> <span style="color: #cc66cc;">10</span>;<br> <button class="copy-code-button" type="button" data-clipboard-text="-- 选择所有列的所有行 SELECT * FROM users; -- 选择特定列的所有行 SELECT username, email FROM users; -- 添加 WHERE 子句,选择满足条件的行 SELECT * FROM users WHERE is_active = TRUE; -- 添加 ORDER BY 子句,按照某列的升序排序 SELECT * FROM users ORDER BY birthdate; -- 添加 ORDER BY 子句,按照某列的降序排序 SELECT * FROM users ORDER BY birthdate DESC; -- 添加 LIMIT 子句,限制返回的行数 SELECT * FROM users LIMIT 10;"></button></div></div> <p>SELECT 语句可以是灵活的,我们可以根据实际需求组合和使用这些子句,比如同时使用 WHERE 和 ORDER BY 子句,或者使用 LIMIT 控制返回的行数。</p> <p>在 <code>WHERE</code> 子句中,你可以使用各种条件运算符(如 <code>=</code>, <code><</code>, <code>></code>, <code><=</code>, <code>>=</code>, <code>!=</code>),逻辑运算符(如 <code>AND</code>, <code>OR</code>, <code>NOT</code>),以及通配符(如 <code>%</code>)等。</p> <p>以下是一些进阶的 SELECT 语句实例:</p> <div class="example"><h2 class="example">实例</h2> <div class="example_code"> <span style="color: #808080; font-style: italic;">-- 使用 AND 运算符和通配符</span><br> <span style="color: #993333; font-weight: bold;">SELECT</span> <span style="color: #66cc66;">*</span> <span style="color: #993333; font-weight: bold;">FROM</span> users <span style="color: #993333; font-weight: bold;">WHERE</span> username <span style="color: #993333; font-weight: bold;">LIKE</span> <span style="color: #ff0000;">'j%'</span> <span style="color: #993333; font-weight: bold;">AND</span> is_active <span style="color: #66cc66;">=</span> <span style="color: #993333; font-weight: bold;">TRUE</span>;<br> <br> <span style="color: #808080; font-style: italic;">-- 使用 OR 运算符</span><br> <span style="color: #993333; font-weight: bold;">SELECT</span> <span style="color: #66cc66;">*</span> <span style="color: #993333; font-weight: bold;">FROM</span> users <span style="color: #993333; font-weight: bold;">WHERE</span> is_active <span style="color: #66cc66;">=</span> <span style="color: #993333; font-weight: bold;">TRUE</span> <span style="color: #993333; font-weight: bold;">OR</span> birthdate <span style="color: #66cc66;"><</span> <span style="color: #ff0000;">'1990-01-01'</span>;<br> <br> <span style="color: #808080; font-style: italic;">-- 使用 IN 子句</span><br> <span style="color: #993333; font-weight: bold;">SELECT</span> <span style="color: #66cc66;">*</span> <span style="color: #993333; font-weight: bold;">FROM</span> users <span style="color: #993333; font-weight: bold;">WHERE</span> birthdate <span style="color: #993333; font-weight: bold;">IN</span> <span style="color: #66cc66;">(</span><span style="color: #ff0000;">'1990-01-01'</span><span style="color: #66cc66;">,</span> <span style="color: #ff0000;">'1992-03-15'</span><span style="color: #66cc66;">,</span> <span style="color: #ff0000;">'1993-05-03'</span><span style="color: #66cc66;">)</span>;<br> <button class="copy-code-button" type="button" data-clipboard-text="-- 使用 AND 运算符和通配符 SELECT * FROM users WHERE username LIKE 'j%' AND is_active = TRUE; -- 使用 OR 运算符 SELECT * FROM users WHERE is_active = TRUE OR birthdate < '1990-01-01'; -- 使用 IN 子句 SELECT * FROM users WHERE birthdate IN ('1990-01-01', '1992-03-15', '1993-05-03');"></button></div></div> <hr> <h2>通过命令提示符获取数据</h2> <p>以下实例我们将通过 SQL SELECT 命令来获取 MySQL 数据表 runoob_tbl 的数据: </p> <h3>实例</h3> <p>以下实例将返回数据表 runoob_tbl 的所有记录:</p> <div class="example"> <h2 class="example">读取数据表:</h2> <div class="example_code"> <div class="hl-main"><span class="hl-reserved">select</span><span class="hl-code"> * </span><span class="hl-reserved">from</span><span class="hl-code"> </span><span class="hl-identifier">runoob_tbl</span><span class="hl-code">;</span></div> <button class="copy-code-button" type="button" data-clipboard-text="select * from runoob_tbl;"></button></div> <p>输出结果:</p> <div class="example_code"> <img decoding="async" src="//www.runoob.com/wp-content/uploads/2014/03/DB742246-84F3-4447-BD43-6BAEADD7CA91.jpg"><p></p> <button class="copy-code-button" type="button" data-clipboard-text=""></button></div> </div> <hr> <h2>使用 PHP 脚本来获取数据</h2> <p> 使用 PHP 函数的 <span class="marked">mysqli_query()</span> 及 <span class="marked">SQL SELECT</span> 命令来获取数据。</p><p> 该函数用于执行 SQL 命令,然后通过 PHP 函数 <span class="marked">mysqli_fetch_array() </span> 来使用或输出所有查询的数据。</p> <p> <span class="marked">mysqli_fetch_array() </span> 函数从结果集中取得一行作为关联数组,或数字数组,或二者兼有 返回根据从结果集取得的行生成的数组,如果没有更多行则返回 false。 </p><p> 以下实例为从数据表 runoob_tbl 中读取所有记录。</p> <h3>实例</h3> <p>尝试以下实例来显示数据表 runoob_tbl 的所有记录。 </p><div class="example"> <h2 class="example">使用 mysqli_fetch_array MYSQLI_ASSOC 参数获取数据:</h2> <div class="example_code"> <div class="hl-main"><span class="hl-inlinetags"><?php</span><span class="hl-code"> </span><span class="hl-var">$dbhost</span><span class="hl-code"> = </span><span class="hl-quotes">'</span><span class="hl-string">localhost</span><span class="hl-quotes">'</span><span class="hl-code">; </span><span class="hl-comment">//</span><span class="hl-comment"> mysql服务器主机地址</span><span class="hl-comment"></span><span class="hl-code"> </span><span class="hl-var">$dbuser</span><span class="hl-code"> = </span><span class="hl-quotes">'</span><span class="hl-string">root</span><span class="hl-quotes">'</span><span class="hl-code">; </span><span class="hl-comment">//</span><span class="hl-comment"> mysql用户名</span><span class="hl-comment"></span><span class="hl-code"> </span><span class="hl-var">$dbpass</span><span class="hl-code"> = </span><span class="hl-quotes">'</span><span class="hl-string">123456</span><span class="hl-quotes">'</span><span class="hl-code">; </span><span class="hl-comment">//</span><span class="hl-comment"> mysql用户名密码</span><span class="hl-comment"></span><span class="hl-code"> </span><span class="hl-var">$conn</span><span class="hl-code"> = </span><span class="hl-identifier">mysqli_connect</span><span class="hl-brackets">(</span><span class="hl-var">$dbhost</span><span class="hl-code">, </span><span class="hl-var">$dbuser</span><span class="hl-code">, </span><span class="hl-var">$dbpass</span><span class="hl-brackets">)</span><span class="hl-code">; </span><span class="hl-reserved">if</span><span class="hl-brackets">(</span><span class="hl-code">! </span><span class="hl-var">$conn</span><span class="hl-code"> </span><span class="hl-brackets">)</span><span class="hl-code"> </span><span class="hl-brackets">{</span><span class="hl-code"> </span><span class="hl-reserved">die</span><span class="hl-brackets">(</span><span class="hl-quotes">'</span><span class="hl-string">连接失败: </span><span class="hl-quotes">'</span><span class="hl-code"> . </span><span class="hl-identifier">mysqli_error</span><span class="hl-brackets">(</span><span class="hl-var">$conn</span><span class="hl-brackets">)</span><span class="hl-brackets">)</span><span class="hl-code">; </span><span class="hl-brackets">}</span><span class="hl-code"> </span><span class="hl-comment">//</span><span class="hl-comment"> 设置编码,防止中文乱码</span><span class="hl-comment"></span><span class="hl-code"> </span><span class="hl-identifier">mysqli_query</span><span class="hl-brackets">(</span><span class="hl-var">$conn</span><span class="hl-code"> , </span><span class="hl-quotes">"</span><span class="hl-string">set names utf8</span><span class="hl-quotes">"</span><span class="hl-brackets">)</span><span class="hl-code">; </span><span class="hl-var">$sql</span><span class="hl-code"> = </span><span class="hl-quotes">'</span><span class="hl-string">SELECT runoob_id, runoob_title, runoob_author, submission_date FROM runoob_tbl</span><span class="hl-quotes">'</span><span class="hl-code">; </span><span class="hl-identifier">mysqli_select_db</span><span class="hl-brackets">(</span><span class="hl-code"> </span><span class="hl-var">$conn</span><span class="hl-code">, </span><span class="hl-quotes">'</span><span class="hl-string">RUNOOB</span><span class="hl-quotes">'</span><span class="hl-code"> </span><span class="hl-brackets">)</span><span class="hl-code">; </span><span class="hl-var">$retval</span><span class="hl-code"> = </span><span class="hl-identifier">mysqli_query</span><span class="hl-brackets">(</span><span class="hl-code"> </span><span class="hl-var">$conn</span><span class="hl-code">, </span><span class="hl-var">$sql</span><span class="hl-code"> </span><span class="hl-brackets">)</span><span class="hl-code">; </span><span class="hl-reserved">if</span><span class="hl-brackets">(</span><span class="hl-code">! </span><span class="hl-var">$retval</span><span class="hl-code"> </span><span class="hl-brackets">)</span><span class="hl-code"> </span><span class="hl-brackets">{</span><span class="hl-code"> </span><span class="hl-reserved">die</span><span class="hl-brackets">(</span><span class="hl-quotes">'</span><span class="hl-string">无法读取数据: </span><span class="hl-quotes">'</span><span class="hl-code"> . </span><span class="hl-identifier">mysqli_error</span><span class="hl-brackets">(</span><span class="hl-var">$conn</span><span class="hl-brackets">)</span><span class="hl-brackets">)</span><span class="hl-code">; </span><span class="hl-brackets">}</span><span class="hl-code"> </span><span class="hl-reserved">echo</span><span class="hl-code"> </span><span class="hl-quotes">'</span><span class="hl-string"><h2>菜鸟教程 mysqli_fetch_array 测试</h2></span><span class="hl-quotes">'</span><span class="hl-code">; </span><span class="hl-reserved">echo</span><span class="hl-code"> </span><span class="hl-quotes">'</span><span class="hl-string"><table border="1"><tr><td>教程 ID</td><td>标题</td><td>作者</td><td>提交日期</td></tr></span><span class="hl-quotes">'</span><span class="hl-code">; </span><span class="hl-reserved">while</span><span class="hl-brackets">(</span><span class="hl-var">$row</span><span class="hl-code"> = </span><span class="hl-identifier">mysqli_fetch_array</span><span class="hl-brackets">(</span><span class="hl-var">$retval</span><span class="hl-code">, </span><span class="hl-identifier">MYSQLI_ASSOC</span><span class="hl-brackets">)</span><span class="hl-brackets">)</span><span class="hl-code"> </span><span class="hl-brackets">{</span><span class="hl-code"> </span><span class="hl-reserved">echo</span><span class="hl-code"> </span><span class="hl-quotes">"</span><span class="hl-string"><tr><td> </span><span class="hl-var">{$row['runoob_id']}</span><span class="hl-string"></td> </span><span class="hl-quotes">"</span><span class="hl-code">. </span><span class="hl-quotes">"</span><span class="hl-string"><td></span><span class="hl-var">{$row['runoob_title']}</span><span class="hl-string"> </td> </span><span class="hl-quotes">"</span><span class="hl-code">. </span><span class="hl-quotes">"</span><span class="hl-string"><td></span><span class="hl-var">{$row['runoob_author']}</span><span class="hl-string"> </td> </span><span class="hl-quotes">"</span><span class="hl-code">. </span><span class="hl-quotes">"</span><span class="hl-string"><td></span><span class="hl-var">{$row['submission_date']}</span><span class="hl-string"> </td> </span><span class="hl-quotes">"</span><span class="hl-code">. </span><span class="hl-quotes">"</span><span class="hl-string"></tr></span><span class="hl-quotes">"</span><span class="hl-code">; </span><span class="hl-brackets">}</span><span class="hl-code"> </span><span class="hl-reserved">echo</span><span class="hl-code"> </span><span class="hl-quotes">'</span><span class="hl-string"></table></span><span class="hl-quotes">'</span><span class="hl-code">; </span><span class="hl-identifier">mysqli_close</span><span class="hl-brackets">(</span><span class="hl-var">$conn</span><span class="hl-brackets">)</span><span class="hl-code">; </span><span class="hl-inlinetags">?></span></div> <button class="copy-code-button" type="button" data-clipboard-text="<?php $dbhost = 'localhost'; // 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); ?>"></button></div> <p>输出结果如下所示:</p> <div class="example_code"> <img decoding="async" src="//www.runoob.com/wp-content/uploads/2014/03/9B08EFFD-6326-4C2B-BFCA-171714FC018D.jpg"> <button class="copy-code-button" type="button" data-clipboard-text=""></button></div> </div> <p>以上实例中,读取的每行记录赋值给变量 $row,然后再打印出每个值。</p> <p><strong>注意:</strong>记住如果你需要在字符串中使用变量,请将变量置于花括号。</p> <p> 在上面的例子中,PHP mysqli_fetch_array() 函数第二个参数为 <strong>MYSQLI_ASSOC</strong>, 设置该参数查询结果返回关联数组,你可以使用字段名称来作为数组的索引。</p> <p> PHP 提供了另外一个函数 <strong>mysqli_fetch_assoc()</strong>, 该函数从结果集中取得一行作为关联数组。 返回根据从结果集取得的行生成的关联数组,如果没有更多行,则返回 false。 </p> <h3>实例</h3> <p> 尝试以下实例,该实例使用了 <strong>mysqli_fetch_assoc()</strong> 函数来输出数据表 runoob_tbl 的所有记录:</p> <div class="example"> <h2 class="example">使用 mysqli_fetch_assoc 获取数据:</h2> <div class="example_code"> <div class="hl-main"><span class="hl-inlinetags"><?php</span><span class="hl-code"> </span><span class="hl-var">$dbhost</span><span class="hl-code"> = </span><span class="hl-quotes">'</span><span class="hl-string">localhost:3306</span><span class="hl-quotes">'</span><span class="hl-code">; </span><span class="hl-comment">//</span><span class="hl-comment"> mysql服务器主机地址</span><span class="hl-comment"></span><span class="hl-code"> </span><span class="hl-var">$dbuser</span><span class="hl-code"> = </span><span class="hl-quotes">'</span><span class="hl-string">root</span><span class="hl-quotes">'</span><span class="hl-code">; </span><span class="hl-comment">//</span><span class="hl-comment"> mysql用户名</span><span class="hl-comment"></span><span class="hl-code"> </span><span class="hl-var">$dbpass</span><span class="hl-code"> = </span><span class="hl-quotes">'</span><span class="hl-string">123456</span><span class="hl-quotes">'</span><span class="hl-code">; </span><span class="hl-comment">//</span><span class="hl-comment"> mysql用户名密码</span><span class="hl-comment"></span><span class="hl-code"> </span><span class="hl-var">$conn</span><span class="hl-code"> = </span><span class="hl-identifier">mysqli_connect</span><span class="hl-brackets">(</span><span class="hl-var">$dbhost</span><span class="hl-code">, </span><span class="hl-var">$dbuser</span><span class="hl-code">, </span><span class="hl-var">$dbpass</span><span class="hl-brackets">)</span><span class="hl-code">; </span><span class="hl-reserved">if</span><span class="hl-brackets">(</span><span class="hl-code">! </span><span class="hl-var">$conn</span><span class="hl-code"> </span><span class="hl-brackets">)</span><span class="hl-code"> </span><span class="hl-brackets">{</span><span class="hl-code"> </span><span class="hl-reserved">die</span><span class="hl-brackets">(</span><span class="hl-quotes">'</span><span class="hl-string">连接失败: </span><span class="hl-quotes">'</span><span class="hl-code"> . </span><span class="hl-identifier">mysqli_error</span><span class="hl-brackets">(</span><span class="hl-var">$conn</span><span class="hl-brackets">)</span><span class="hl-brackets">)</span><span class="hl-code">; </span><span class="hl-brackets">}</span><span class="hl-code"> </span><span class="hl-comment">//</span><span class="hl-comment"> 设置编码,防止中文乱码</span><span class="hl-comment"></span><span class="hl-code"> </span><span class="hl-identifier">mysqli_query</span><span class="hl-brackets">(</span><span class="hl-var">$conn</span><span class="hl-code"> , </span><span class="hl-quotes">"</span><span class="hl-string">set names utf8</span><span class="hl-quotes">"</span><span class="hl-brackets">)</span><span class="hl-code">; </span><span class="hl-var">$sql</span><span class="hl-code"> = </span><span class="hl-quotes">'</span><span class="hl-string">SELECT runoob_id, runoob_title, runoob_author, submission_date FROM runoob_tbl</span><span class="hl-quotes">'</span><span class="hl-code">; </span><span class="hl-identifier">mysqli_select_db</span><span class="hl-brackets">(</span><span class="hl-code"> </span><span class="hl-var">$conn</span><span class="hl-code">, </span><span class="hl-quotes">'</span><span class="hl-string">RUNOOB</span><span class="hl-quotes">'</span><span class="hl-code"> </span><span class="hl-brackets">)</span><span class="hl-code">; </span><span class="hl-var">$retval</span><span class="hl-code"> = </span><span class="hl-identifier">mysqli_query</span><span class="hl-brackets">(</span><span class="hl-code"> </span><span class="hl-var">$conn</span><span class="hl-code">, </span><span class="hl-var">$sql</span><span class="hl-code"> </span><span class="hl-brackets">)</span><span class="hl-code">; </span><span class="hl-reserved">if</span><span class="hl-brackets">(</span><span class="hl-code">! </span><span class="hl-var">$retval</span><span class="hl-code"> </span><span class="hl-brackets">)</span><span class="hl-code"> </span><span class="hl-brackets">{</span><span class="hl-code"> </span><span class="hl-reserved">die</span><span class="hl-brackets">(</span><span class="hl-quotes">'</span><span class="hl-string">无法读取数据: </span><span class="hl-quotes">'</span><span class="hl-code"> . </span><span class="hl-identifier">mysqli_error</span><span class="hl-brackets">(</span><span class="hl-var">$conn</span><span class="hl-brackets">)</span><span class="hl-brackets">)</span><span class="hl-code">; </span><span class="hl-brackets">}</span><span class="hl-code"> </span><span class="hl-reserved">echo</span><span class="hl-code"> </span><span class="hl-quotes">'</span><span class="hl-string"><h2>菜鸟教程 mysqli_fetch_assoc 测试</h2></span><span class="hl-quotes">'</span><span class="hl-code">; </span><span class="hl-reserved">echo</span><span class="hl-code"> </span><span class="hl-quotes">'</span><span class="hl-string"><table border="1"><tr><td>教程 ID</td><td>标题</td><td>作者</td><td>提交日期</td></tr></span><span class="hl-quotes">'</span><span class="hl-code">; </span><span class="hl-reserved">while</span><span class="hl-brackets">(</span><span class="hl-var">$row</span><span class="hl-code"> = </span><span class="hl-identifier">mysqli_fetch_assoc</span><span class="hl-brackets">(</span><span class="hl-var">$retval</span><span class="hl-brackets">)</span><span class="hl-brackets">)</span><span class="hl-code"> </span><span class="hl-brackets">{</span><span class="hl-code"> </span><span class="hl-reserved">echo</span><span class="hl-code"> </span><span class="hl-quotes">"</span><span class="hl-string"><tr><td> </span><span class="hl-var">{$row['runoob_id']}</span><span class="hl-string"></td> </span><span class="hl-quotes">"</span><span class="hl-code">. </span><span class="hl-quotes">"</span><span class="hl-string"><td></span><span class="hl-var">{$row['runoob_title']}</span><span class="hl-string"> </td> </span><span class="hl-quotes">"</span><span class="hl-code">. </span><span class="hl-quotes">"</span><span class="hl-string"><td></span><span class="hl-var">{$row['runoob_author']}</span><span class="hl-string"> </td> </span><span class="hl-quotes">"</span><span class="hl-code">. </span><span class="hl-quotes">"</span><span class="hl-string"><td></span><span class="hl-var">{$row['submission_date']}</span><span class="hl-string"> </td> </span><span class="hl-quotes">"</span><span class="hl-code">. </span><span class="hl-quotes">"</span><span class="hl-string"></tr></span><span class="hl-quotes">"</span><span class="hl-code">; </span><span class="hl-brackets">}</span><span class="hl-code"> </span><span class="hl-reserved">echo</span><span class="hl-code"> </span><span class="hl-quotes">'</span><span class="hl-string"></table></span><span class="hl-quotes">'</span><span class="hl-code">; </span><span class="hl-identifier">mysqli_close</span><span class="hl-brackets">(</span><span class="hl-var">$conn</span><span class="hl-brackets">)</span><span class="hl-code">; </span><span class="hl-inlinetags">?></span></div> <button class="copy-code-button" type="button" data-clipboard-text="<?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_assoc 测试</h2>'; echo '<table border="1"><tr><td>教程 ID</td><td>标题</td><td>作者</td><td>提交日期</td></tr>'; while($row = mysqli_fetch_assoc($retval)) { 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); ?>"></button></div> <p>输出结果如下所示:</p> <div class="example_code"> <img decoding="async" src="//www.runoob.com/wp-content/uploads/2014/03/9B7E791A-6461-4B83-B89D-11D592C07AC5.jpg"> <button class="copy-code-button" type="button" data-clipboard-text=""></button></div> </div> <p> 你也可以使用常量 MYSQLI_NUM 作为 PHP mysqli_fetch_array() 函数的第二个参数,返回数字数组。 </p> <h3>实例</h3> <p>以下实例使用 <strong> MYSQLI_NUM </strong> 参数显示数据表 runoob_tbl 的所有记录: </p> <div class="example"> <h2 class="example">使用 mysqli_fetch_array MYSQLI_NUM 参数获取数据:</h2> <div class="example_code"> <div class="hl-main"><span class="hl-inlinetags"><?php</span><span class="hl-code"> </span><span class="hl-var">$dbhost</span><span class="hl-code"> = </span><span class="hl-quotes">'</span><span class="hl-string">localhost:3306</span><span class="hl-quotes">'</span><span class="hl-code">; </span><span class="hl-comment">//</span><span class="hl-comment"> mysql服务器主机地址</span><span class="hl-comment"></span><span class="hl-code"> </span><span class="hl-var">$dbuser</span><span class="hl-code"> = </span><span class="hl-quotes">'</span><span class="hl-string">root</span><span class="hl-quotes">'</span><span class="hl-code">; </span><span class="hl-comment">//</span><span class="hl-comment"> mysql用户名</span><span class="hl-comment"></span><span class="hl-code"> </span><span class="hl-var">$dbpass</span><span class="hl-code"> = </span><span class="hl-quotes">'</span><span class="hl-string">123456</span><span class="hl-quotes">'</span><span class="hl-code">; </span><span class="hl-comment">//</span><span class="hl-comment"> mysql用户名密码</span><span class="hl-comment"></span><span class="hl-code"> </span><span class="hl-var">$conn</span><span class="hl-code"> = </span><span class="hl-identifier">mysqli_connect</span><span class="hl-brackets">(</span><span class="hl-var">$dbhost</span><span class="hl-code">, </span><span class="hl-var">$dbuser</span><span class="hl-code">, </span><span class="hl-var">$dbpass</span><span class="hl-brackets">)</span><span class="hl-code">; </span><span class="hl-reserved">if</span><span class="hl-brackets">(</span><span class="hl-code">! </span><span class="hl-var">$conn</span><span class="hl-code"> </span><span class="hl-brackets">)</span><span class="hl-code"> </span><span class="hl-brackets">{</span><span class="hl-code"> </span><span class="hl-reserved">die</span><span class="hl-brackets">(</span><span class="hl-quotes">'</span><span class="hl-string">连接失败: </span><span class="hl-quotes">'</span><span class="hl-code"> . </span><span class="hl-identifier">mysqli_error</span><span class="hl-brackets">(</span><span class="hl-var">$conn</span><span class="hl-brackets">)</span><span class="hl-brackets">)</span><span class="hl-code">; </span><span class="hl-brackets">}</span><span class="hl-code"> </span><span class="hl-comment">//</span><span class="hl-comment"> 设置编码,防止中文乱码</span><span class="hl-comment"></span><span class="hl-code"> </span><span class="hl-identifier">mysqli_query</span><span class="hl-brackets">(</span><span class="hl-var">$conn</span><span class="hl-code"> , </span><span class="hl-quotes">"</span><span class="hl-string">set names utf8</span><span class="hl-quotes">"</span><span class="hl-brackets">)</span><span class="hl-code">; </span><span class="hl-var">$sql</span><span class="hl-code"> = </span><span class="hl-quotes">'</span><span class="hl-string">SELECT runoob_id, runoob_title, runoob_author, submission_date FROM runoob_tbl</span><span class="hl-quotes">'</span><span class="hl-code">; </span><span class="hl-identifier">mysqli_select_db</span><span class="hl-brackets">(</span><span class="hl-code"> </span><span class="hl-var">$conn</span><span class="hl-code">, </span><span class="hl-quotes">'</span><span class="hl-string">RUNOOB</span><span class="hl-quotes">'</span><span class="hl-code"> </span><span class="hl-brackets">)</span><span class="hl-code">; </span><span class="hl-var">$retval</span><span class="hl-code"> = </span><span class="hl-identifier">mysqli_query</span><span class="hl-brackets">(</span><span class="hl-code"> </span><span class="hl-var">$conn</span><span class="hl-code">, </span><span class="hl-var">$sql</span><span class="hl-code"> </span><span class="hl-brackets">)</span><span class="hl-code">; </span><span class="hl-reserved">if</span><span class="hl-brackets">(</span><span class="hl-code">! </span><span class="hl-var">$retval</span><span class="hl-code"> </span><span class="hl-brackets">)</span><span class="hl-code"> </span><span class="hl-brackets">{</span><span class="hl-code"> </span><span class="hl-reserved">die</span><span class="hl-brackets">(</span><span class="hl-quotes">'</span><span class="hl-string">无法读取数据: </span><span class="hl-quotes">'</span><span class="hl-code"> . </span><span class="hl-identifier">mysqli_error</span><span class="hl-brackets">(</span><span class="hl-var">$conn</span><span class="hl-brackets">)</span><span class="hl-brackets">)</span><span class="hl-code">; </span><span class="hl-brackets">}</span><span class="hl-code"> </span><span class="hl-reserved">echo</span><span class="hl-code"> </span><span class="hl-quotes">'</span><span class="hl-string"><h2>菜鸟教程 mysqli_fetch_array 测试</h2></span><span class="hl-quotes">'</span><span class="hl-code">; </span><span class="hl-reserved">echo</span><span class="hl-code"> </span><span class="hl-quotes">'</span><span class="hl-string"><table border="1"><tr><td>教程 ID</td><td>标题</td><td>作者</td><td>提交日期</td></tr></span><span class="hl-quotes">'</span><span class="hl-code">; </span><span class="hl-reserved">while</span><span class="hl-brackets">(</span><span class="hl-var">$row</span><span class="hl-code"> = </span><span class="hl-identifier">mysqli_fetch_array</span><span class="hl-brackets">(</span><span class="hl-var">$retval</span><span class="hl-code">, </span><span class="hl-identifier">MYSQLI_NUM</span><span class="hl-brackets">)</span><span class="hl-brackets">)</span><span class="hl-code"> </span><span class="hl-brackets">{</span><span class="hl-code"> </span><span class="hl-reserved">echo</span><span class="hl-code"> </span><span class="hl-quotes">"</span><span class="hl-string"><tr><td> </span><span class="hl-var">{$row[0]}</span><span class="hl-string"></td> </span><span class="hl-quotes">"</span><span class="hl-code">. </span><span class="hl-quotes">"</span><span class="hl-string"><td></span><span class="hl-var">{$row[1]}</span><span class="hl-string"> </td> </span><span class="hl-quotes">"</span><span class="hl-code">. </span><span class="hl-quotes">"</span><span class="hl-string"><td></span><span class="hl-var">{$row[2]}</span><span class="hl-string"> </td> </span><span class="hl-quotes">"</span><span class="hl-code">. </span><span class="hl-quotes">"</span><span class="hl-string"><td></span><span class="hl-var">{$row[3]}</span><span class="hl-string"> </td> </span><span class="hl-quotes">"</span><span class="hl-code">. </span><span class="hl-quotes">"</span><span class="hl-string"></tr></span><span class="hl-quotes">"</span><span class="hl-code">; </span><span class="hl-brackets">}</span><span class="hl-code"> </span><span class="hl-reserved">echo</span><span class="hl-code"> </span><span class="hl-quotes">'</span><span class="hl-string"></table></span><span class="hl-quotes">'</span><span class="hl-code">; </span><span class="hl-identifier">mysqli_close</span><span class="hl-brackets">(</span><span class="hl-var">$conn</span><span class="hl-brackets">)</span><span class="hl-code">; </span><span class="hl-inlinetags">?></span></div> <button class="copy-code-button" type="button" data-clipboard-text="<?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_NUM)) { echo "<tr><td> {$row[0]}</td> ". "<td>{$row[1]} </td> ". "<td>{$row[2]} </td> ". "<td>{$row[3]} </td> ". "</tr>"; } echo '</table>'; mysqli_close($conn); ?>"></button></div> <p>输出结果如下所示:</p> <div class="example_code"> <img decoding="async" src="//www.runoob.com/wp-content/uploads/2014/03/9B08EFFD-6326-4C2B-BFCA-171714FC018D.jpg"> <button class="copy-code-button" type="button" data-clipboard-text=""></button></div> </div> <p>以上三个实例输出结果都一样。</p> <hr> <h2> 内存释放</h2> <p>在我们执行完 SELECT 语句后,释放游标内存是一个很好的习惯。 </p><p> 可以通过 PHP 函数 mysqli_free_result() 来实现内存的释放。 </p> <p> 以下实例演示了该函数的使用方法。 </p> <h3>实例</h3> <p>尝试以下实例:</p> <div class="example"> <h2 class="example">使用 mysqli_free_result 释放内存:</h2> <div class="example_code"> <div class="hl-main"><span class="hl-inlinetags"><?php</span><span class="hl-code"> </span><span class="hl-var">$dbhost</span><span class="hl-code"> = </span><span class="hl-quotes">'</span><span class="hl-string">localhost:3306</span><span class="hl-quotes">'</span><span class="hl-code">; </span><span class="hl-comment">//</span><span class="hl-comment"> mysql服务器主机地址</span><span class="hl-comment"></span><span class="hl-code"> </span><span class="hl-var">$dbuser</span><span class="hl-code"> = </span><span class="hl-quotes">'</span><span class="hl-string">root</span><span class="hl-quotes">'</span><span class="hl-code">; </span><span class="hl-comment">//</span><span class="hl-comment"> mysql用户名</span><span class="hl-comment"></span><span class="hl-code"> </span><span class="hl-var">$dbpass</span><span class="hl-code"> = </span><span class="hl-quotes">'</span><span class="hl-string">123456</span><span class="hl-quotes">'</span><span class="hl-code">; </span><span class="hl-comment">//</span><span class="hl-comment"> mysql用户名密码</span><span class="hl-comment"></span><span class="hl-code"> </span><span class="hl-var">$conn</span><span class="hl-code"> = </span><span class="hl-identifier">mysqli_connect</span><span class="hl-brackets">(</span><span class="hl-var">$dbhost</span><span class="hl-code">, </span><span class="hl-var">$dbuser</span><span class="hl-code">, </span><span class="hl-var">$dbpass</span><span class="hl-brackets">)</span><span class="hl-code">; </span><span class="hl-reserved">if</span><span class="hl-brackets">(</span><span class="hl-code">! </span><span class="hl-var">$conn</span><span class="hl-code"> </span><span class="hl-brackets">)</span><span class="hl-code"> </span><span class="hl-brackets">{</span><span class="hl-code"> </span><span class="hl-reserved">die</span><span class="hl-brackets">(</span><span class="hl-quotes">'</span><span class="hl-string">连接失败: </span><span class="hl-quotes">'</span><span class="hl-code"> . </span><span class="hl-identifier">mysqli_error</span><span class="hl-brackets">(</span><span class="hl-var">$conn</span><span class="hl-brackets">)</span><span class="hl-brackets">)</span><span class="hl-code">; </span><span class="hl-brackets">}</span><span class="hl-code"> </span><span class="hl-comment">//</span><span class="hl-comment"> 设置编码,防止中文乱码</span><span class="hl-comment"></span><span class="hl-code"> </span><span class="hl-identifier">mysqli_query</span><span class="hl-brackets">(</span><span class="hl-var">$conn</span><span class="hl-code"> , </span><span class="hl-quotes">"</span><span class="hl-string">set names utf8</span><span class="hl-quotes">"</span><span class="hl-brackets">)</span><span class="hl-code">; </span><span class="hl-var">$sql</span><span class="hl-code"> = </span><span class="hl-quotes">'</span><span class="hl-string">SELECT runoob_id, runoob_title, runoob_author, submission_date FROM runoob_tbl</span><span class="hl-quotes">'</span><span class="hl-code">; </span><span class="hl-identifier">mysqli_select_db</span><span class="hl-brackets">(</span><span class="hl-code"> </span><span class="hl-var">$conn</span><span class="hl-code">, </span><span class="hl-quotes">'</span><span class="hl-string">RUNOOB</span><span class="hl-quotes">'</span><span class="hl-code"> </span><span class="hl-brackets">)</span><span class="hl-code">; </span><span class="hl-var">$retval</span><span class="hl-code"> = </span><span class="hl-identifier">mysqli_query</span><span class="hl-brackets">(</span><span class="hl-code"> </span><span class="hl-var">$conn</span><span class="hl-code">, </span><span class="hl-var">$sql</span><span class="hl-code"> </span><span class="hl-brackets">)</span><span class="hl-code">; </span><span class="hl-reserved">if</span><span class="hl-brackets">(</span><span class="hl-code">! </span><span class="hl-var">$retval</span><span class="hl-code"> </span><span class="hl-brackets">)</span><span class="hl-code"> </span><span class="hl-brackets">{</span><span class="hl-code"> </span><span class="hl-reserved">die</span><span class="hl-brackets">(</span><span class="hl-quotes">'</span><span class="hl-string">无法读取数据: </span><span class="hl-quotes">'</span><span class="hl-code"> . </span><span class="hl-identifier">mysqli_error</span><span class="hl-brackets">(</span><span class="hl-var">$conn</span><span class="hl-brackets">)</span><span class="hl-brackets">)</span><span class="hl-code">; </span><span class="hl-brackets">}</span><span class="hl-code"> </span><span class="hl-reserved">echo</span><span class="hl-code"> </span><span class="hl-quotes">'</span><span class="hl-string"><h2>菜鸟教程 mysqli_fetch_array 测试</h2></span><span class="hl-quotes">'</span><span class="hl-code">; </span><span class="hl-reserved">echo</span><span class="hl-code"> </span><span class="hl-quotes">'</span><span class="hl-string"><table border="1"><tr><td>教程 ID</td><td>标题</td><td>作者</td><td>提交日期</td></tr></span><span class="hl-quotes">'</span><span class="hl-code">; </span><span class="hl-reserved">while</span><span class="hl-brackets">(</span><span class="hl-var">$row</span><span class="hl-code"> = </span><span class="hl-identifier">mysqli_fetch_array</span><span class="hl-brackets">(</span><span class="hl-var">$retval</span><span class="hl-code">, </span><span class="hl-identifier">MYSQLI_NUM</span><span class="hl-brackets">)</span><span class="hl-brackets">)</span><span class="hl-code"> </span><span class="hl-brackets">{</span><span class="hl-code"> </span><span class="hl-reserved">echo</span><span class="hl-code"> </span><span class="hl-quotes">"</span><span class="hl-string"><tr><td> </span><span class="hl-var">{$row[0]}</span><span class="hl-string"></td> </span><span class="hl-quotes">"</span><span class="hl-code">. </span><span class="hl-quotes">"</span><span class="hl-string"><td></span><span class="hl-var">{$row[1]}</span><span class="hl-string"> </td> </span><span class="hl-quotes">"</span><span class="hl-code">. </span><span class="hl-quotes">"</span><span class="hl-string"><td></span><span class="hl-var">{$row[2]}</span><span class="hl-string"> </td> </span><span class="hl-quotes">"</span><span class="hl-code">. </span><span class="hl-quotes">"</span><span class="hl-string"><td></span><span class="hl-var">{$row[3]}</span><span class="hl-string"> </td> </span><span class="hl-quotes">"</span><span class="hl-code">. </span><span class="hl-quotes">"</span><span class="hl-string"></tr></span><span class="hl-quotes">"</span><span class="hl-code">; </span><span class="hl-brackets">}</span><span class="hl-code"> </span><span class="hl-reserved">echo</span><span class="hl-code"> </span><span class="hl-quotes">'</span><span class="hl-string"></table></span><span class="hl-quotes">'</span><span class="hl-code">; </span><span class="hl-comment">//</span><span class="hl-comment"> 释放内存</span><span class="hl-comment"></span><span class="hl-code"> </span><span class="hl-identifier">mysqli_free_result</span><span class="hl-brackets">(</span><span class="hl-var">$retval</span><span class="hl-brackets">)</span><span class="hl-code">; </span><span class="hl-identifier">mysqli_close</span><span class="hl-brackets">(</span><span class="hl-var">$conn</span><span class="hl-brackets">)</span><span class="hl-code">; </span><span class="hl-inlinetags">?></span></div> <button class="copy-code-button" type="button" data-clipboard-text="<?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_NUM)) { echo "<tr><td> {$row[0]}</td> ". "<td>{$row[1]} </td> ". "<td>{$row[2]} </td> ". "<td>{$row[3]} </td> ". "</tr>"; } echo '</table>'; // 释放内存 mysqli_free_result($retval); mysqli_close($conn); ?>"></button></div> <p>输出结果如下所示:</p> <div class="example_code"> <img decoding="async" src="//www.runoob.com/wp-content/uploads/2014/03/9B08EFFD-6326-4C2B-BFCA-171714FC018D.jpg"> <button class="copy-code-button" type="button" data-clipboard-text=""></button></div> </div> <!-- 其他扩展 --> </div> </div> <div class="article-intro" id="content"> <h1>MySQL WHERE 子句</h1> <p> 我们知道从 MySQL 表中使用 <span class="marked">SELECT</span> 语句来读取数据。</p><p> 如需有条件地从表中选取数据,可将 WHERE 子句添加到 SELECT 语句中。</p><p>WHERE 子句用于在 MySQL 中过滤查询结果,只返回满足特定条件的行。</p> <h3> 语法</h3> <p> 以下是 SQL SELECT 语句使用 WHERE 子句从数据表中读取数据的通用语法:</p> <pre class="prettyprint prettyprinted" style=""><span class="pln">SELECT column1</span><span class="pun">,</span><span class="pln"> column2</span><span class="pun">,</span><span class="pln"> </span><span class="pun">...</span><span class="pln"> FROM table_name WHERE condition</span><span class="pun">;</span><button class="copy-code-button" type="button" data-clipboard-text="SELECT column1, column2, ... FROM table_name WHERE condition; "></button></pre><p><strong>参数说明:</strong></p> <ul><li><code>column1</code>, <code>column2</code>, ... 是你要选择的列的名称,如果使用 <code>*</code> 表示选择所有列。</li><li><code>table_name</code> 是你要从中查询数据的表的名称。</li><li><code>WHERE condition</code> 是用于指定过滤条件的子句。</li></ul> <p><strong>更多说明:</strong></p> <ul><li> 查询语句中你可以使用一个或者多个表,表之间使用逗号<span class="marked">,</span> 分割,并使用WHERE语句来设定查询条件。</li><li> 你可以在 WHERE 子句中指定任何条件。</li><li> 你可以使用 AND 或者 OR 指定一个或多个条件。</li><li> WHERE 子句也可以运用于 SQL 的 DELETE 或者 UPDATE 命令。</li><li> WHERE 子句类似于程序语言中的 if 条件,根据 MySQL 表中的字段值来读取指定的数据。</li></ul> <p>以下为操作符列表,可用于 WHERE 子句中。 </p> <p> 下表中实例假定 A 为 10, B 为 20 </p> <table class="reference"> <tbody><tr> <th style="width:10%">操作符</th><th style="width:45%">描述</th><th>实例</th> </tr> <tr> <td>=</td><td> 等号,检测两个值是否相等,如果相等返回true</td><td> (A = B) 返回false。 </td> </tr> <tr> <td><>, != </td><td> 不等于,检测两个值是否相等,如果不相等返回true</td><td> (A != B) 返回 true。 </td> </tr> <tr> <td>></td><td> 大于号,检测左边的值是否大于右边的值, 如果左边的值大于右边的值返回true</td><td> (A > B) 返回false。 </td> </tr> <tr> <td><</td><td> 小于号,检测左边的值是否小于右边的值, 如果左边的值小于右边的值返回true</td><td> (A < B) 返回 true。 </td> </tr> <tr> <td>>=</td><td> 大于等于号,检测左边的值是否大于或等于右边的值, 如果左边的值大于或等于右边的值返回true</td><td> (A >= B) 返回false。</td> </tr> <tr> <td><=</td><td> 小于等于号,检测左边的值是否小于或等于右边的值, 如果左边的值小于或等于右边的值返回true</td><td> (A <= B) 返回 true。 </td> </tr> </tbody></table> <h3>简单实例</h3> <p> 1. 等于条件:</p> <pre class="prettyprint prettyprinted" style=""><span class="pln">SELECT </span><span class="pun">*</span><span class="pln"> FROM users WHERE username </span><span class="pun">=</span><span class="pln"> </span><span class="str">'test'</span><span class="pun">;</span><button class="copy-code-button" type="button" data-clipboard-text="SELECT * FROM users WHERE username = 'test';"></button></pre> <p>2. 不等于条件:</p> <pre class="prettyprint prettyprinted" style=""><span class="pln">SELECT </span><span class="pun">*</span><span class="pln"> FROM users WHERE username </span><span class="pun">!=</span><span class="pln"> </span><span class="str">'runoob'</span><span class="pun">;</span><button class="copy-code-button" type="button" data-clipboard-text="SELECT * FROM users WHERE username != 'runoob';"></button></pre> <p>3. 大于条件:</p> <pre class="prettyprint prettyprinted" style=""><span class="pln">SELECT </span><span class="pun">*</span><span class="pln"> FROM products WHERE price </span><span class="pun">></span><span class="pln"> </span><span class="lit">50.00</span><span class="pun">;</span><button class="copy-code-button" type="button" data-clipboard-text="SELECT * FROM products WHERE price > 50.00;"></button></pre> <p>4. 小于条件:</p> <pre class="prettyprint prettyprinted" style=""><span class="pln">SELECT </span><span class="pun">*</span><span class="pln"> FROM orders WHERE order_date </span><span class="pun"><</span><span class="pln"> </span><span class="str">'2023-01-01'</span><span class="pun">;</span><button class="copy-code-button" type="button" data-clipboard-text="SELECT * FROM orders WHERE order_date < '2023-01-01';"></button></pre> <p>5. 大于等于条件:</p> <pre class="prettyprint prettyprinted" style=""><span class="pln">SELECT </span><span class="pun">*</span><span class="pln"> FROM employees WHERE salary </span><span class="pun">>=</span><span class="pln"> </span><span class="lit">50000</span><span class="pun">;</span><button class="copy-code-button" type="button" data-clipboard-text="SELECT * FROM employees WHERE salary >= 50000;"></button></pre> <p>6. 小于等于条件:</p> <pre class="prettyprint prettyprinted" style=""><span class="pln">SELECT </span><span class="pun">*</span><span class="pln"> FROM students WHERE age </span><span class="pun"><=</span><span class="pln"> </span><span class="lit">21</span><span class="pun">;</span><button class="copy-code-button" type="button" data-clipboard-text="SELECT * FROM students WHERE age <= 21;"></button></pre> <p>7. 组合条件(AND、OR):</p><pre class="prettyprint prettyprinted" style=""><span class="pln">SELECT </span><span class="pun">*</span><span class="pln"> FROM products WHERE category </span><span class="pun">=</span><span class="pln"> </span><span class="str">'Electronics'</span><span class="pln"> AND price </span><span class="pun">></span><span class="pln"> </span><span class="lit">100.00</span><span class="pun">;</span><span class="pln"> SELECT </span><span class="pun">*</span><span class="pln"> FROM orders WHERE order_date </span><span class="pun">>=</span><span class="pln"> </span><span class="str">'2023-01-01'</span><span class="pln"> OR total_amount </span><span class="pun">></span><span class="pln"> </span><span class="lit">1000.00</span><span class="pun">;</span><button class="copy-code-button" type="button" data-clipboard-text="SELECT * FROM products WHERE category = 'Electronics' AND price > 100.00; SELECT * FROM orders WHERE order_date >= '2023-01-01' OR total_amount > 1000.00;"></button></pre> <p>8. 模糊匹配条件(LIKE):</p> <pre class="prettyprint prettyprinted" style=""><span class="pln">SELECT </span><span class="pun">*</span><span class="pln"> FROM customers WHERE first_name LIKE </span><span class="str">'J%'</span><span class="pun">;</span><button class="copy-code-button" type="button" data-clipboard-text="SELECT * FROM customers WHERE first_name LIKE 'J%';"></button></pre> <p>9. IN 条件:</p> <pre class="prettyprint prettyprinted" style=""><span class="pln">SELECT </span><span class="pun">*</span><span class="pln"> FROM countries WHERE country_code IN </span><span class="pun">(</span><span class="str">'US'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'CA'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'MX'</span><span class="pun">);</span><button class="copy-code-button" type="button" data-clipboard-text="SELECT * FROM countries WHERE country_code IN ('US', 'CA', 'MX');"></button></pre> <p>10. NOT 条件:</p> <pre class="prettyprint prettyprinted" style=""><span class="pln">SELECT </span><span class="pun">*</span><span class="pln"> FROM products WHERE NOT category </span><span class="pun">=</span><span class="pln"> </span><span class="str">'Clothing'</span><span class="pun">;</span><button class="copy-code-button" type="button" data-clipboard-text="SELECT * FROM products WHERE NOT category = 'Clothing';"></button></pre> <p>11. BETWEEN 条件:</p> <pre class="prettyprint prettyprinted" style=""><span class="pln">SELECT </span><span class="pun">*</span><span class="pln"> FROM orders WHERE order_date BETWEEN </span><span class="str">'2023-01-01'</span><span class="pln"> AND </span><span class="str">'2023-12-31'</span><span class="pun">;</span><button class="copy-code-button" type="button" data-clipboard-text="SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';"></button></pre> <p>12. IS NULL 条件</p> <pre class="prettyprint prettyprinted" style=""><span class="pln">SELECT </span><span class="pun">*</span><span class="pln"> FROM employees WHERE department IS NULL</span><span class="pun">;</span><button class="copy-code-button" type="button" data-clipboard-text="SELECT * FROM employees WHERE department IS NULL;"></button></pre> <p>13. IS NOT NULL 条件:</p> <pre class="prettyprint prettyprinted" style=""><span class="pln">SELECT </span><span class="pun">*</span><span class="pln"> FROM customers WHERE email IS NOT NULL</span><span class="pun">;</span><button class="copy-code-button" type="button" data-clipboard-text="SELECT * FROM customers WHERE email IS NOT NULL;"></button></pre> <p>如果我们想在 MySQL 数据表中读取指定的数据,WHERE 子句是非常有用的。</p> <p>使用主键来作为 WHERE 子句的条件查询是非常快速的。</p> <p>如果给定的条件在表中没有任何匹配的记录,那么查询不会返回任何数据。</p> <hr> <h2>从命令提示符中读取数据</h2> <p>我们将在 <span class="marked">SELECT</span> 语句使用 WHERE 子句来读取 MySQL 数据表 runoob_tbl 中的数据。</p> <p>以下实例将读取 runoob_tbl 表中 runoob_author 字段值为 Sanjay 的所有记录:</p> <div class="example"> <h2 class="example">SQL SELECT WHERE 子句</h2> <div class="example_code"> <div class="hl-main"><span class="hl-reserved">SELECT</span><span class="hl-code"> * </span><span class="hl-reserved">from</span><span class="hl-code"> </span><span class="hl-identifier">runoob_tbl</span><span class="hl-code"> </span><span class="hl-reserved">WHERE</span><span class="hl-code"> </span><span class="hl-identifier">runoob_author</span><span class="hl-code">=</span><span class="hl-quotes">'</span><span class="hl-string">菜鸟教程</span><span class="hl-quotes">'</span><span class="hl-code">;</span></div> <button class="copy-code-button" type="button" data-clipboard-text="SELECT * from runoob_tbl WHERE runoob_author='菜鸟教程';"></button></div> <p>输出结果:</p> <div class="example_code"> <img decoding="async" src="//www.runoob.com/wp-content/uploads/2014/03/CED9CA9C-E4C7-4809-875C-A7E48F430059.jpg"><p></p> <button class="copy-code-button" type="button" data-clipboard-text=""></button></div> </div> <p>MySQL 的 WHERE 子句的字符串比较是不区分大小写的。 你可以使用 BINARY 关键字来设定 WHERE 子句的字符串比较是区分大小写的。</p> <p>如下实例: </p> <div class="example"> <h2 class="example">BINARY 关键字</h2> <div class="example_code"> <div class="hl-main"><span class="hl-identifier">mysql</span><span class="hl-code">> </span><span class="hl-reserved">SELECT</span><span class="hl-code"> * </span><span class="hl-reserved">from</span><span class="hl-code"> </span><span class="hl-identifier">runoob_tbl</span><span class="hl-code"> </span><span class="hl-reserved">WHERE</span><span class="hl-code"> </span><span class="hl-reserved">BINARY</span><span class="hl-code"> </span><span class="hl-identifier">runoob_author</span><span class="hl-code">=</span><span class="hl-quotes">'</span><span class="hl-string">runoob.com</span><span class="hl-quotes">'</span><span class="hl-code">; </span><span class="hl-identifier">Empty</span><span class="hl-code"> </span><span class="hl-reserved">set</span><span class="hl-code"> </span><span class="hl-brackets">(</span><span class="hl-number">0.01</span><span class="hl-code"> </span><span class="hl-identifier">sec</span><span class="hl-brackets">)</span><span class="hl-code"> </span><span class="hl-identifier">mysql</span><span class="hl-code">> </span><span class="hl-reserved">SELECT</span><span class="hl-code"> * </span><span class="hl-reserved">from</span><span class="hl-code"> </span><span class="hl-identifier">runoob_tbl</span><span class="hl-code"> </span><span class="hl-reserved">WHERE</span><span class="hl-code"> </span><span class="hl-reserved">BINARY</span><span class="hl-code"> </span><span class="hl-identifier">runoob_author</span><span class="hl-code">=</span><span class="hl-quotes">'</span><span class="hl-string">RUNOOB.COM</span><span class="hl-quotes">'</span><span class="hl-code">; +-----------+---------------+---------------+-----------------+ | </span><span class="hl-identifier">runoob_id</span><span class="hl-code"> | </span><span class="hl-identifier">runoob_title</span><span class="hl-code"> | </span><span class="hl-identifier">runoob_author</span><span class="hl-code"> | </span><span class="hl-identifier">submission_date</span><span class="hl-code"> | +-----------+---------------+---------------+-----------------+ | </span><span class="hl-number">3</span><span class="hl-code"> | </span><span class="hl-identifier">JAVA</span><span class="hl-code"> 教程 | </span><span class="hl-identifier">RUNOOB</span><span class="hl-code">.</span><span class="hl-identifier">COM</span><span class="hl-code"> | </span><span class="hl-number">2016</span><span class="hl-code">-</span><span class="hl-number">05</span><span class="hl-code">-</span><span class="hl-number">06</span><span class="hl-code"> | | </span><span class="hl-number">4</span><span class="hl-code"> | 学习 </span><span class="hl-identifier">Python</span><span class="hl-code"> | </span><span class="hl-identifier">RUNOOB</span><span class="hl-code">.</span><span class="hl-identifier">COM</span><span class="hl-code"> | </span><span class="hl-number">2016</span><span class="hl-code">-</span><span class="hl-number">03</span><span class="hl-code">-</span><span class="hl-number">06</span><span class="hl-code"> | +-----------+---------------+---------------+-----------------+ </span><span class="hl-number">2</span><span class="hl-code"> </span><span class="hl-reserved">rows</span><span class="hl-code"> </span><span class="hl-reserved">in</span><span class="hl-code"> </span><span class="hl-reserved">set</span><span class="hl-code"> </span><span class="hl-brackets">(</span><span class="hl-number">0.01</span><span class="hl-code"> </span><span class="hl-identifier">sec</span><span class="hl-brackets">)</span></div> <button class="copy-code-button" type="button" data-clipboard-text="mysql> SELECT * from runoob_tbl WHERE BINARY runoob_author='runoob.com'; Empty set (0.01 sec) mysql> SELECT * from runoob_tbl WHERE BINARY runoob_author='RUNOOB.COM'; +-----------+---------------+---------------+-----------------+ | runoob_id | runoob_title | runoob_author | submission_date | +-----------+---------------+---------------+-----------------+ | 3 | JAVA 教程 | RUNOOB.COM | 2016-05-06 | | 4 | 学习 Python | RUNOOB.COM | 2016-03-06 | +-----------+---------------+---------------+-----------------+ 2 rows in set (0.01 sec)"></button></div> </div> <p>实例中使用了 <strong>BINARY</strong> 关键字,是区分大小写的,所以 <strong>runoob_author='runoob.com'</strong> 的查询条件是没有数据的。</p> <hr> <h2>使用 PHP 脚本读取数据</h2> <p>你可以使用 PHP 函数的 mysqli_query() 及相同的 SQL SELECT 带上 WHERE 子句的命令来获取数据。 </p><p> 该函数用于执行 SQL 命令,然后通过 PHP 函数 mysqli_fetch_array() 来输出所有查询的数据。 </p> <h3>实例</h3> <p>以下实例将从 runoob_tbl 表中返回使用 runoob_author 字段值为 <span class="marked">RUNOOB.COM</span> 的记录: </p> <div class="example"> <h2 class="example">MySQL WHERE 子句测试:</h2> <div class="example_code"> <div class="hl-main"><span class="hl-inlinetags"><?php</span><span class="hl-code"> </span><span class="hl-var">$dbhost</span><span class="hl-code"> = </span><span class="hl-quotes">'</span><span class="hl-string">localhost</span><span class="hl-quotes">'</span><span class="hl-code">; </span><span class="hl-comment">//</span><span class="hl-comment"> mysql服务器主机地址</span><span class="hl-comment"></span><span class="hl-code"> </span><span class="hl-var">$dbuser</span><span class="hl-code"> = </span><span class="hl-quotes">'</span><span class="hl-string">root</span><span class="hl-quotes">'</span><span class="hl-code">; </span><span class="hl-comment">//</span><span class="hl-comment"> mysql用户名</span><span class="hl-comment"></span><span class="hl-code"> </span><span class="hl-var">$dbpass</span><span class="hl-code"> = </span><span class="hl-quotes">'</span><span class="hl-string">123456</span><span class="hl-quotes">'</span><span class="hl-code">; </span><span class="hl-comment">//</span><span class="hl-comment"> mysql用户名密码</span><span class="hl-comment"></span><span class="hl-code"> </span><span class="hl-var">$conn</span><span class="hl-code"> = </span><span class="hl-identifier">mysqli_connect</span><span class="hl-brackets">(</span><span class="hl-var">$dbhost</span><span class="hl-code">, </span><span class="hl-var">$dbuser</span><span class="hl-code">, </span><span class="hl-var">$dbpass</span><span class="hl-brackets">)</span><span class="hl-code">; </span><span class="hl-reserved">if</span><span class="hl-brackets">(</span><span class="hl-code">! </span><span class="hl-var">$conn</span><span class="hl-code"> </span><span class="hl-brackets">)</span><span class="hl-code"> </span><span class="hl-brackets">{</span><span class="hl-code"> </span><span class="hl-reserved">die</span><span class="hl-brackets">(</span><span class="hl-quotes">'</span><span class="hl-string">连接失败: </span><span class="hl-quotes">'</span><span class="hl-code"> . </span><span class="hl-identifier">mysqli_error</span><span class="hl-brackets">(</span><span class="hl-var">$conn</span><span class="hl-brackets">)</span><span class="hl-brackets">)</span><span class="hl-code">; </span><span class="hl-brackets">}</span><span class="hl-code"> </span><span class="hl-comment">//</span><span class="hl-comment"> 设置编码,防止中文乱码</span><span class="hl-comment"></span><span class="hl-code"> </span><span class="hl-identifier">mysqli_query</span><span class="hl-brackets">(</span><span class="hl-var">$conn</span><span class="hl-code"> , </span><span class="hl-quotes">"</span><span class="hl-string">set names utf8</span><span class="hl-quotes">"</span><span class="hl-brackets">)</span><span class="hl-code">; </span><span class="hl-comment">//</span><span class="hl-comment"> 读取 runoob_author 为 RUNOOB.COM 的数据</span><span class="hl-comment"></span><span class="hl-code"> </span><span class="hl-var">$sql</span><span class="hl-code"> = </span><span class="hl-quotes">'</span><span class="hl-string">SELECT runoob_id, runoob_title, runoob_author, submission_date FROM runoob_tbl WHERE runoob_author="RUNOOB.COM"</span><span class="hl-quotes">'</span><span class="hl-code">; </span><span class="hl-identifier">mysqli_select_db</span><span class="hl-brackets">(</span><span class="hl-code"> </span><span class="hl-var">$conn</span><span class="hl-code">, </span><span class="hl-quotes">'</span><span class="hl-string">RUNOOB</span><span class="hl-quotes">'</span><span class="hl-code"> </span><span class="hl-brackets">)</span><span class="hl-code">; </span><span class="hl-var">$retval</span><span class="hl-code"> = </span><span class="hl-identifier">mysqli_query</span><span class="hl-brackets">(</span><span class="hl-code"> </span><span class="hl-var">$conn</span><span class="hl-code">, </span><span class="hl-var">$sql</span><span class="hl-code"> </span><span class="hl-brackets">)</span><span class="hl-code">; </span><span class="hl-reserved">if</span><span class="hl-brackets">(</span><span class="hl-code">! </span><span class="hl-var">$retval</span><span class="hl-code"> </span><span class="hl-brackets">)</span><span class="hl-code"> </span><span class="hl-brackets">{</span><span class="hl-code"> </span><span class="hl-reserved">die</span><span class="hl-brackets">(</span><span class="hl-quotes">'</span><span class="hl-string">无法读取数据: </span><span class="hl-quotes">'</span><span class="hl-code"> . </span><span class="hl-identifier">mysqli_error</span><span class="hl-brackets">(</span><span class="hl-var">$conn</span><span class="hl-brackets">)</span><span class="hl-brackets">)</span><span class="hl-code">; </span><span class="hl-brackets">}</span><span class="hl-code"> </span><span class="hl-reserved">echo</span><span class="hl-code"> </span><span class="hl-quotes">'</span><span class="hl-string"><h2>菜鸟教程 MySQL WHERE 子句测试<h2></span><span class="hl-quotes">'</span><span class="hl-code">; </span><span class="hl-reserved">echo</span><span class="hl-code"> </span><span class="hl-quotes">'</span><span class="hl-string"><table border="1"><tr><td>教程 ID</td><td>标题</td><td>作者</td><td>提交日期</td></tr></span><span class="hl-quotes">'</span><span class="hl-code">; </span><span class="hl-reserved">while</span><span class="hl-brackets">(</span><span class="hl-var">$row</span><span class="hl-code"> = </span><span class="hl-identifier">mysqli_fetch_array</span><span class="hl-brackets">(</span><span class="hl-var">$retval</span><span class="hl-code">, </span><span class="hl-identifier">MYSQLI_ASSOC</span><span class="hl-brackets">)</span><span class="hl-brackets">)</span><span class="hl-code"> </span><span class="hl-brackets">{</span><span class="hl-code"> </span><span class="hl-reserved">echo</span><span class="hl-code"> </span><span class="hl-quotes">"</span><span class="hl-string"><tr><td> </span><span class="hl-var">{$row['runoob_id']}</span><span class="hl-string"></td> </span><span class="hl-quotes">"</span><span class="hl-code">. </span><span class="hl-quotes">"</span><span class="hl-string"><td></span><span class="hl-var">{$row['runoob_title']}</span><span class="hl-string"> </td> </span><span class="hl-quotes">"</span><span class="hl-code">. </span><span class="hl-quotes">"</span><span class="hl-string"><td></span><span class="hl-var">{$row['runoob_author']}</span><span class="hl-string"> </td> </span><span class="hl-quotes">"</span><span class="hl-code">. </span><span class="hl-quotes">"</span><span class="hl-string"><td></span><span class="hl-var">{$row['submission_date']}</span><span class="hl-string"> </td> </span><span class="hl-quotes">"</span><span class="hl-code">. </span><span class="hl-quotes">"</span><span class="hl-string"></tr></span><span class="hl-quotes">"</span><span class="hl-code">; </span><span class="hl-brackets">}</span><span class="hl-code"> </span><span class="hl-reserved">echo</span><span class="hl-code"> </span><span class="hl-quotes">'</span><span class="hl-string"></table></span><span class="hl-quotes">'</span><span class="hl-code">; </span><span class="hl-comment">//</span><span class="hl-comment"> 释放内存</span><span class="hl-comment"></span><span class="hl-code"> </span><span class="hl-identifier">mysqli_free_result</span><span class="hl-brackets">(</span><span class="hl-var">$retval</span><span class="hl-brackets">)</span><span class="hl-code">; </span><span class="hl-identifier">mysqli_close</span><span class="hl-brackets">(</span><span class="hl-var">$conn</span><span class="hl-brackets">)</span><span class="hl-code">; </span><span class="hl-inlinetags">?></span></div> <button class="copy-code-button" type="button" data-clipboard-text="<?php $dbhost = 'localhost'; // 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"); // 读取 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, 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_free_result($retval); mysqli_close($conn); ?>"></button></div> <p>输出结果如下所示:</p> <div class="example_code"> <img decoding="async" src="//www.runoob.com/wp-content/uploads/2014/03/0512776C-5E25-4B76-8893-568A6271B5BB.jpg"> <button class="copy-code-button" type="button" data-clipboard-text=""></button></div> </div> <!-- 其他扩展 --> </div> <div class="article-intro" id="content"> <h1>MySQL 插入数据</h1><p> MySQL 表中使用 <span class="marked">INSERT INTO</span> 语句来插入数据。</p> <p> 你可以通过 <span class="marked">mysql></span> 命令提示窗口中向数据表中插入数据,或者通过PHP脚本来插入数据。 </p> <h3>语法</h3> <p>以下为向MySQL数据表插入数据通用的<strong> INSERT INTO </strong> SQL语法:</p> <pre class="prettyprint prettyprinted" style=""><span class="pln">INSERT INTO table_name </span><span class="pun">(</span><span class="pln">column1</span><span class="pun">,</span><span class="pln"> column2</span><span class="pun">,</span><span class="pln"> column3</span><span class="pun">,</span><span class="pln"> </span><span class="pun">...)</span><span class="pln"> VALUES </span><span class="pun">(</span><span class="pln">value1</span><span class="pun">,</span><span class="pln"> value2</span><span class="pun">,</span><span class="pln"> value3</span><span class="pun">,</span><span class="pln"> </span><span class="pun">...);</span><button class="copy-code-button" type="button" data-clipboard-text="INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...); "></button></pre><p><strong>参数说明:</strong></p> <ul><li><code>table_name</code> 是你要插入数据的表的名称。</li><li><code>column1</code>, <code>column2</code>, <code>column3</code>, ... 是表中的列名。</li><li><code>value1</code>, <code>value2</code>, <code>value3</code>, ... 是要插入的具体数值。</li></ul> <p>如果数据是字符型,必须使用单引号 <span class="marked">'</span> 或者双引号 <span class="marked">"</span>,如: 'value1', "value1"。</p> <p>一个简单的实例,插入了一行数据到名为 users 的表中:</p> <pre class="prettyprint prettyprinted" style=""><span class="pln">INSERT INTO users </span><span class="pun">(</span><span class="pln">username</span><span class="pun">,</span><span class="pln"> email</span><span class="pun">,</span><span class="pln"> birthdate</span><span class="pun">,</span><span class="pln"> is_active</span><span class="pun">)</span><span class="pln"> VALUES </span><span class="pun">(</span><span class="str">'test'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'test@runoob.com'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'1990-01-01'</span><span class="pun">,</span><span class="pln"> </span><span class="kwd">true</span><span class="pun">);</span><button class="copy-code-button" type="button" data-clipboard-text="INSERT INTO users (username, email, birthdate, is_active) VALUES ('test', 'test@runoob.com', '1990-01-01', true);"></button></pre> <ul><li><code>username</code>: 用户名,字符串类型。</li><li><code>email</code>: 邮箱地址,字符串类型。</li><li><code>birthdate</code>: 用户生日, 日期类型。</li><li><code>is_active</code>: 是否已激活,布尔类型。</li></ul> <p>如果你要插入所有列的数据,可以省略列名:</p> <pre class="prettyprint prettyprinted" style=""><span class="pln">INSERT INTO users VALUES </span><span class="pun">(</span><span class="pln">NULL</span><span class="pun">,</span><span class="str">'test'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'test@runoob.com'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'1990-01-01'</span><span class="pun">,</span><span class="pln"> </span><span class="kwd">true</span><span class="pun">);</span><button class="copy-code-button" type="button" data-clipboard-text="INSERT INTO users VALUES (NULL,'test', 'test@runoob.com', '1990-01-01', true);"></button></pre> <p>这里,<span class="marked">NULL</span> 是用于自增长列的占位符,表示系统将为 <strong>id</strong> 列生成一个唯一的值。</p> <p>如果你要插入多行数据,可以在 VALUES 子句中指定多组数值:</p> <pre class="prettyprint prettyprinted" style=""><span class="pln">INSERT INTO users </span><span class="pun">(</span><span class="pln">username</span><span class="pun">,</span><span class="pln"> email</span><span class="pun">,</span><span class="pln"> birthdate</span><span class="pun">,</span><span class="pln"> is_active</span><span class="pun">)</span><span class="pln"> VALUES </span><span class="pun">(</span><span class="str">'test1'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'test1@runoob.com'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'1985-07-10'</span><span class="pun">,</span><span class="pln"> </span><span class="kwd">true</span><span class="pun">),</span><span class="pln"> </span><span class="pun">(</span><span class="str">'test2'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'test2@runoob.com'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'1988-11-25'</span><span class="pun">,</span><span class="pln"> </span><span class="kwd">false</span><span class="pun">),</span><span class="pln"> </span><span class="pun">(</span><span class="str">'test3'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'test3@runoob.com'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'1993-05-03'</span><span class="pun">,</span><span class="pln"> </span><span class="kwd">true</span><span class="pun">);</span><button class="copy-code-button" type="button" data-clipboard-text="INSERT INTO users (username, email, birthdate, is_active) VALUES ('test1', 'test1@runoob.com', '1985-07-10', true), ('test2', 'test2@runoob.com', '1988-11-25', false), ('test3', 'test3@runoob.com', '1993-05-03', true);"></button></pre> <p>以上代码将在 users 表中插入三行数据。</p> <hr> <h2>通过命令提示窗口插入数据</h2> <p> 以下我们将使用 <span class="marked">INSERT INTO</span> 语句向 MySQL 数据表 runoob_tbl 插入数据 </p> <h3>实例</h3> <p>以下实例中我们将向 runoob_tbl 表插入三条数据: </p> <div class="example"><h2 class="example">实例</h2> <div class="example_code"> root@host# mysql <span style="color: #66cc66;">-</span>u root <span style="color: #66cc66;">-</span>p password;<br> Enter password:<span style="color: #66cc66;">*******</span><br> mysql<span style="color: #66cc66;">></span> <span style="color: #993333; font-weight: bold;">USE</span> RUNOOB;<br> <span style="color: #993333; font-weight: bold;">DATABASE</span> changed<br> mysql<span style="color: #66cc66;">></span> <span style="color: #993333; font-weight: bold;">INSERT</span> <span style="color: #993333; font-weight: bold;">INTO</span> runoob_tbl <br> <span style="color: #66cc66;">-></span> <span style="color: #66cc66;">(</span>runoob_title<span style="color: #66cc66;">,</span> runoob_author<span style="color: #66cc66;">,</span> submission_date<span style="color: #66cc66;">)</span><br> <span style="color: #66cc66;">-></span> <span style="color: #993333; font-weight: bold;">VALUES</span><br> <span style="color: #66cc66;">-></span> <span style="color: #66cc66;">(</span><span style="color: #ff0000;">"学习 PHP"</span><span style="color: #66cc66;">,</span> <span style="color: #ff0000;">"菜鸟教程"</span><span style="color: #66cc66;">,</span> NOW<span style="color: #66cc66;">(</span><span style="color: #66cc66;">)</span><span style="color: #66cc66;">)</span>;<br> Query OK<span style="color: #66cc66;">,</span> <span style="color: #cc66cc;">1</span> <span style="color: #993333; font-weight: bold;">ROWS</span> affected<span style="color: #66cc66;">,</span> <span style="color: #cc66cc;">1</span> warnings <span style="color: #66cc66;">(</span><span style="color: #cc66cc;">0.01</span> sec<span style="color: #66cc66;">)</span><br> mysql<span style="color: #66cc66;">></span> <span style="color: #993333; font-weight: bold;">INSERT</span> <span style="color: #993333; font-weight: bold;">INTO</span> runoob_tbl<br> <span style="color: #66cc66;">-></span> <span style="color: #66cc66;">(</span>runoob_title<span style="color: #66cc66;">,</span> runoob_author<span style="color: #66cc66;">,</span> submission_date<span style="color: #66cc66;">)</span><br> <span style="color: #66cc66;">-></span> <span style="color: #993333; font-weight: bold;">VALUES</span><br> <span style="color: #66cc66;">-></span> <span style="color: #66cc66;">(</span><span style="color: #ff0000;">"学习 MySQL"</span><span style="color: #66cc66;">,</span> <span style="color: #ff0000;">"菜鸟教程"</span><span style="color: #66cc66;">,</span> NOW<span style="color: #66cc66;">(</span><span style="color: #66cc66;">)</span><span style="color: #66cc66;">)</span>;<br> Query OK<span style="color: #66cc66;">,</span> <span style="color: #cc66cc;">1</span> <span style="color: #993333; font-weight: bold;">ROWS</span> affected<span style="color: #66cc66;">,</span> <span style="color: #cc66cc;">1</span> warnings <span style="color: #66cc66;">(</span><span style="color: #cc66cc;">0.01</span> sec<span style="color: #66cc66;">)</span><br> mysql<span style="color: #66cc66;">></span> <span style="color: #993333; font-weight: bold;">INSERT</span> <span style="color: #993333; font-weight: bold;">INTO</span> runoob_tbl<br> <span style="color: #66cc66;">-></span> <span style="color: #66cc66;">(</span>runoob_title<span style="color: #66cc66;">,</span> runoob_author<span style="color: #66cc66;">,</span> submission_date<span style="color: #66cc66;">)</span><br> <span style="color: #66cc66;">-></span> <span style="color: #993333; font-weight: bold;">VALUES</span><br> <span style="color: #66cc66;">-></span> <span style="color: #66cc66;">(</span><span style="color: #ff0000;">"JAVA 教程"</span><span style="color: #66cc66;">,</span> <span style="color: #ff0000;">"RUNOOB.COM"</span><span style="color: #66cc66;">,</span> <span style="color: #ff0000;">'2016-05-06'</span><span style="color: #66cc66;">)</span>;<br> Query OK<span style="color: #66cc66;">,</span> <span style="color: #cc66cc;">1</span> <span style="color: #993333; font-weight: bold;">ROWS</span> affected <span style="color: #66cc66;">(</span><span style="color: #cc66cc;">0.00</span> sec<span style="color: #66cc66;">)</span><br> mysql<span style="color: #66cc66;">></span><br> <button class="copy-code-button" type="button" data-clipboard-text="root@host# mysql -u root -p password; Enter password:******* mysql> USE RUNOOB; DATABASE changed mysql> INSERT INTO runoob_tbl -> (runoob_title, runoob_author, submission_date) -> VALUES -> ("学习 PHP", "菜鸟教程", NOW()); Query OK, 1 ROWS affected, 1 warnings (0.01 sec) mysql> INSERT INTO runoob_tbl -> (runoob_title, runoob_author, submission_date) -> VALUES -> ("学习 MySQL", "菜鸟教程", NOW()); Query OK, 1 ROWS affected, 1 warnings (0.01 sec) mysql> INSERT INTO runoob_tbl -> (runoob_title, runoob_author, submission_date) -> VALUES -> ("JAVA 教程", "RUNOOB.COM", '2016-05-06'); Query OK, 1 ROWS affected (0.00 sec) mysql>"></button></div></div> <p> <strong>注意:</strong> 使用箭头标记 <span class="marked">-></span> 不是 SQL 语句的一部分,它仅仅表示一个新行,如果一条 SQL 语句太长,我们可以通过回车键来创建一个新行来编写 SQL 语句,SQL 语句的命令结束符为分号 <span class="marked">;</span>。 </p> <p> 在以上实例中,我们并没有提供 <strong>runoob_id</strong> 的数据,因为该字段我们在创建表的时候已经设置它为 <strong>AUTO_INCREMENT</strong>(自动增加) 属性。 所以,该字段会自动递增而不需要我们去设置。实例中 <strong>NOW()</strong> 是一个 MySQL 函数,该函数返回日期和时间。 </p> <p>接下来我们可以通过以下语句查看数据表数据:</p> <div class="example"> <h2 class="example">读取数据表:</h2> <div class="example_code"> <div class="hl-main"><span class="hl-reserved">select</span><span class="hl-code"> * </span><span class="hl-reserved">from</span><span class="hl-code"> </span><span class="hl-identifier">runoob_tbl</span><span class="hl-code">;</span></div> <button class="copy-code-button" type="button" data-clipboard-text="select * from runoob_tbl;"></button></div> <p>输出结果:</p> <div class="example_code"> <img decoding="async" src="//www.runoob.com/wp-content/uploads/2014/03/71971E68-78B3-4964-AC4C-E75114D3B5B5.jpg"><p></p> <button class="copy-code-button" type="button" data-clipboard-text=""></button></div> </div> <hr> <h2>使用 PHP 脚本插入数据</h2> <p> 你可以使用 PHP 的 mysqli_query() 函数来执行 <strong>INSERT INTO</strong>命令来插入数据。</p> <p> 该函数有两个参数,在执行成功时返回 TRUE,否则返回 FALSE。</p> <h3> 语法</h3> <pre class="prettyprint prettyprinted" style=""><span class="pln">mysqli_query</span><span class="pun">(</span><span class="pln">connection</span><span class="pun">,</span><span class="pln">query</span><span class="pun">,</span><span class="pln">resultmode</span><span class="pun">);</span><button class="copy-code-button" type="button" data-clipboard-text="mysqli_query(connection,query,resultmode); "></button></pre> <table class="reference"> <tbody><tr> <th width="20%">参数</th> <th width="80%">描述</th> </tr> <tr> <td><em>connection</em></td> <td>必需。规定要使用的 MySQL 连接。</td> </tr> <tr> <td><em>query</em></td> <td>必需,规定查询字符串。</td> </tr> <tr> <td><em>resultmode</em></td> <td> <p>可选。一个常量。可以是下列值中的任意一个:</p> <ul> <li>MYSQLI_USE_RESULT(如果需要检索大量数据,请使用这个)</li> <li>MYSQLI_STORE_RESULT(默认)</li> </ul> </td> </tr> </tbody></table> <h3>实例</h3> <p>以下实例中程序接收用户输入的三个字段数据,并插入数据表中:</p> <div class="example"> <h2 class="example">添加数据</h2> <div class="example_code"> <div class="hl-main"><span class="hl-inlinetags"><?php</span><span class="hl-code"> </span><span class="hl-var">$dbhost</span><span class="hl-code"> = </span><span class="hl-quotes">'</span><span class="hl-string">localhost</span><span class="hl-quotes">'</span><span class="hl-code">; </span><span class="hl-comment">//</span><span class="hl-comment"> mysql服务器主机地址</span><span class="hl-comment"></span><span class="hl-code"> </span><span class="hl-var">$dbuser</span><span class="hl-code"> = </span><span class="hl-quotes">'</span><span class="hl-string">root</span><span class="hl-quotes">'</span><span class="hl-code">; </span><span class="hl-comment">//</span><span class="hl-comment"> mysql用户名</span><span class="hl-comment"></span><span class="hl-code"> </span><span class="hl-var">$dbpass</span><span class="hl-code"> = </span><span class="hl-quotes">'</span><span class="hl-string">123456</span><span class="hl-quotes">'</span><span class="hl-code">; </span><span class="hl-comment">//</span><span class="hl-comment"> mysql用户名密码</span><span class="hl-comment"></span><span class="hl-code"> </span><span class="hl-var">$conn</span><span class="hl-code"> = </span><span class="hl-identifier">mysqli_connect</span><span class="hl-brackets">(</span><span class="hl-var">$dbhost</span><span class="hl-code">, </span><span class="hl-var">$dbuser</span><span class="hl-code">, </span><span class="hl-var">$dbpass</span><span class="hl-brackets">)</span><span class="hl-code">; </span><span class="hl-reserved">if</span><span class="hl-brackets">(</span><span class="hl-code">! </span><span class="hl-var">$conn</span><span class="hl-code"> </span><span class="hl-brackets">)</span><span class="hl-code"> </span><span class="hl-brackets">{</span><span class="hl-code"> </span><span class="hl-reserved">die</span><span class="hl-brackets">(</span><span class="hl-quotes">'</span><span class="hl-string">连接失败: </span><span class="hl-quotes">'</span><span class="hl-code"> . </span><span class="hl-identifier">mysqli_error</span><span class="hl-brackets">(</span><span class="hl-var">$conn</span><span class="hl-brackets">)</span><span class="hl-brackets">)</span><span class="hl-code">; </span><span class="hl-brackets">}</span><span class="hl-code"> </span><span class="hl-reserved">echo</span><span class="hl-code"> </span><span class="hl-quotes">'</span><span class="hl-string">连接成功<br /></span><span class="hl-quotes">'</span><span class="hl-code">; </span><span class="hl-comment">//</span><span class="hl-comment"> 设置编码,防止中文乱码</span><span class="hl-comment"></span><span class="hl-code"> </span><span class="hl-identifier">mysqli_query</span><span class="hl-brackets">(</span><span class="hl-var">$conn</span><span class="hl-code"> , </span><span class="hl-quotes">"</span><span class="hl-string">set names utf8</span><span class="hl-quotes">"</span><span class="hl-brackets">)</span><span class="hl-code">; </span><span class="hl-var">$runoob_title</span><span class="hl-code"> = </span><span class="hl-quotes">'</span><span class="hl-string">学习 Python</span><span class="hl-quotes">'</span><span class="hl-code">; </span><span class="hl-var">$runoob_author</span><span class="hl-code"> = </span><span class="hl-quotes">'</span><span class="hl-string">RUNOOB.COM</span><span class="hl-quotes">'</span><span class="hl-code">; </span><span class="hl-var">$submission_date</span><span class="hl-code"> = </span><span class="hl-quotes">'</span><span class="hl-string">2016-03-06</span><span class="hl-quotes">'</span><span class="hl-code">; </span><span class="hl-var">$sql</span><span class="hl-code"> = </span><span class="hl-quotes">"</span><span class="hl-string">INSERT INTO runoob_tbl </span><span class="hl-quotes">"</span><span class="hl-code">. </span><span class="hl-quotes">"</span><span class="hl-string">(runoob_title,runoob_author, submission_date) </span><span class="hl-quotes">"</span><span class="hl-code">. </span><span class="hl-quotes">"</span><span class="hl-string">VALUES </span><span class="hl-quotes">"</span><span class="hl-code">. </span><span class="hl-quotes">"</span><span class="hl-string">('</span><span class="hl-var">$runoob_title</span><span class="hl-string">','</span><span class="hl-var">$runoob_author</span><span class="hl-string">','</span><span class="hl-var">$submission_date</span><span class="hl-string">')</span><span class="hl-quotes">"</span><span class="hl-code">; </span><span class="hl-identifier">mysqli_select_db</span><span class="hl-brackets">(</span><span class="hl-code"> </span><span class="hl-var">$conn</span><span class="hl-code">, </span><span class="hl-quotes">'</span><span class="hl-string">RUNOOB</span><span class="hl-quotes">'</span><span class="hl-code"> </span><span class="hl-brackets">)</span><span class="hl-code">; </span><span class="hl-var">$retval</span><span class="hl-code"> = </span><span class="hl-identifier">mysqli_query</span><span class="hl-brackets">(</span><span class="hl-code"> </span><span class="hl-var">$conn</span><span class="hl-code">, </span><span class="hl-var">$sql</span><span class="hl-code"> </span><span class="hl-brackets">)</span><span class="hl-code">; </span><span class="hl-reserved">if</span><span class="hl-brackets">(</span><span class="hl-code">! </span><span class="hl-var">$retval</span><span class="hl-code"> </span><span class="hl-brackets">)</span><span class="hl-code"> </span><span class="hl-brackets">{</span><span class="hl-code"> </span><span class="hl-reserved">die</span><span class="hl-brackets">(</span><span class="hl-quotes">'</span><span class="hl-string">无法插入数据: </span><span class="hl-quotes">'</span><span class="hl-code"> . </span><span class="hl-identifier">mysqli_error</span><span class="hl-brackets">(</span><span class="hl-var">$conn</span><span class="hl-brackets">)</span><span class="hl-brackets">)</span><span class="hl-code">; </span><span class="hl-brackets">}</span><span class="hl-code"> </span><span class="hl-reserved">echo</span><span class="hl-code"> </span><span class="hl-quotes">"</span><span class="hl-string">数据插入成功</span><span class="hl-special">\n</span><span class="hl-quotes">"</span><span class="hl-code">; </span><span class="hl-identifier">mysqli_close</span><span class="hl-brackets">(</span><span class="hl-var">$conn</span><span class="hl-brackets">)</span><span class="hl-code">; </span><span class="hl-inlinetags">?></span></div> <button class="copy-code-button" type="button" data-clipboard-text="<?php $dbhost = 'localhost'; // 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')"; mysqli_select_db( $conn, 'RUNOOB' ); $retval = mysqli_query( $conn, $sql ); if(! $retval ) { die('无法插入数据: ' . mysqli_error($conn)); } echo "数据插入成功\n"; mysqli_close($conn); ?>"></button></div></div> <p>对于含有中文的数据插入,需要添加 <span class="marked">mysqli_query($conn , "set names utf8");</span> 语句。</p> <p>接下来我们可以通过以下语句查看数据表数据:</p> <div class="example"> <h2 class="example">读取数据表:</h2> <div class="example_code"> <div class="hl-main"><span class="hl-reserved">select</span><span class="hl-code"> * </span><span class="hl-reserved">from</span><span class="hl-code"> </span><span class="hl-identifier">runoob_tbl</span><span class="hl-code">;</span></div> <button class="copy-code-button" type="button" data-clipboard-text="select * from runoob_tbl;"></button></div> <p>输出结果:</p> <div class="example_code"> <img decoding="async" src="//www.runoob.com/wp-content/uploads/2014/03/DB742246-84F3-4447-BD43-6BAEADD7CA91.jpg"><p></p> <button class="copy-code-button" type="button" data-clipboard-text=""></button></div> </div> <!-- 其他扩展 --> </div> <div class="article-intro" id="content"> <h1>MySQL UPDATE 更新 </h1> <p> 如果我们需要修改或更新 MySQL 中的数据,我们可以使用 <span class="marked">UPDATE</span> 命令来操作。 </p> <h3> 语法</h3> <p> 以下是 UPDATE 命令修改 MySQL 数据表数据的通用 SQL 语法:</p> <pre class="prettyprint prettyprinted" style=""><span class="pln">UPDATE table_name SET column1 </span><span class="pun">=</span><span class="pln"> value1</span><span class="pun">,</span><span class="pln"> column2 </span><span class="pun">=</span><span class="pln"> value2</span><span class="pun">,</span><span class="pln"> </span><span class="pun">...</span><span class="pln"> WHERE condition</span><span class="pun">;</span><button class="copy-code-button" type="button" data-clipboard-text="UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition; "></button></pre><p><strong>参数说明:</strong></p> <ul><li><code>table_name</code> 是你要更新数据的表的名称。</li><li><code>column1</code>, <code>column2</code>, ... 是你要更新的列的名称。</li><li><code>value1</code>, <code>value2</code>, ... 是新的值,用于替换旧的值。</li><li><code>WHERE condition</code> 是一个可选的子句,用于指定更新的行。如果省略 <code>WHERE</code> 子句,将更新表中的所有行。</li></ul> <p><strong>更多说明:</strong></p> <ul> <li> 你可以同时更新一个或多个字段。</li><li> 你可以在 WHERE 子句中指定任何条件。</li><li> 你可以在一个单独表中同时更新数据。</li></ul> <p> 当你需要更新数据表中指定行的数据时 WHERE 子句是非常有用的。 </p> <h3>实例</h3><p> 以下实例演示了如何使用 UPDATE 语句。</p> <p>1. 更新单个列的值:</p> <pre class="prettyprint prettyprinted" style=""><span class="pln">UPDATE employees SET salary </span><span class="pun">=</span><span class="pln"> </span><span class="lit">60000</span><span class="pln"> WHERE employee_id </span><span class="pun">=</span><span class="pln"> </span><span class="lit">101</span><span class="pun">;</span><button class="copy-code-button" type="button" data-clipboard-text="UPDATE employees SET salary = 60000 WHERE employee_id = 101;"></button></pre> <p>2. 更新多个列的值:</p> <pre class="prettyprint prettyprinted" style=""><span class="pln">UPDATE orders SET status </span><span class="pun">=</span><span class="pln"> </span><span class="str">'Shipped'</span><span class="pun">,</span><span class="pln"> ship_date </span><span class="pun">=</span><span class="pln"> </span><span class="str">'2023-03-01'</span><span class="pln"> WHERE order_id </span><span class="pun">=</span><span class="pln"> </span><span class="lit">1001</span><span class="pun">;</span><button class="copy-code-button" type="button" data-clipboard-text="UPDATE orders SET status = 'Shipped', ship_date = '2023-03-01' WHERE order_id = 1001;"></button></pre> <p>3. 使用表达式更新值:</p> <pre class="prettyprint prettyprinted" style=""><span class="pln">UPDATE products SET price </span><span class="pun">=</span><span class="pln"> price </span><span class="pun">*</span><span class="pln"> </span><span class="lit">1.1</span><span class="pln"> WHERE category </span><span class="pun">=</span><span class="pln"> </span><span class="str">'Electronics'</span><span class="pun">;</span><button class="copy-code-button" type="button" data-clipboard-text="UPDATE products SET price = price * 1.1 WHERE category = 'Electronics';"></button></pre> <p>以上 SQL 语句将每个属于 'Electronics' 类别的产品的价格都增加了 10%。</p> <p>4. 更新符合条件的所有行:</p> <pre class="prettyprint prettyprinted" style=""><span class="pln">UPDATE students SET status </span><span class="pun">=</span><span class="pln"> </span><span class="str">'Graduated'</span><span class="pun">;</span><button class="copy-code-button" type="button" data-clipboard-text="UPDATE students SET status = 'Graduated';"></button></pre> <p>以上 SQL 语句将所有学生的状态更新为 'Graduated'。</p> <p> 5. 更新使用子查询的值:</p> <pre class="prettyprint prettyprinted" style=""><span class="pln">UPDATE customers SET total_purchases </span><span class="pun">=</span><span class="pln"> </span><span class="pun">(</span><span class="pln"> SELECT SUM</span><span class="pun">(</span><span class="pln">amount</span><span class="pun">)</span><span class="pln"> FROM orders WHERE orders</span><span class="pun">.</span><span class="pln">customer_id </span><span class="pun">=</span><span class="pln"> customers</span><span class="pun">.</span><span class="pln">customer_id </span><span class="pun">)</span><span class="pln"> WHERE customer_type </span><span class="pun">=</span><span class="pln"> </span><span class="str">'Premium'</span><span class="pun">;</span><button class="copy-code-button" type="button" data-clipboard-text="UPDATE customers SET total_purchases = ( SELECT SUM(amount) FROM orders WHERE orders.customer_id = customers.customer_id ) WHERE customer_type = 'Premium';"></button></pre><p> 以上 SQL 语句通过子查询计算每个 'Premium' 类型客户的总购买金额,并将该值更新到 total_purchases 列中。</p> <blockquote> <p><strong>注意: </strong>在使用 UPDATE 语句时,请确保你提供了足够的条件来确保只有你想要更新的行被修改。如果不提供 WHERE 子句,将更新表中的所有行,可能导致不可预测的结果。</p></blockquote> <hr> <h2>通过命令提示符更新数据</h2> <p>以下我们将在 UPDATE 命令使用 WHERE 子句来更新 runoob_tbl 表中指定的数据。</p> <p>以下实例将更新数据表中 runoob_id 为 3 的 runoob_title 字段值:</p> <div class="example"> <h2 class="example">SQL UPDATE 语句:</h2> <div class="example_code"> <div class="hl-main"><span class="hl-identifier">mysql</span><span class="hl-code">> </span><span class="hl-reserved">UPDATE</span><span class="hl-code"> </span><span class="hl-identifier">runoob_tbl</span><span class="hl-code"> </span><span class="hl-reserved">SET</span><span class="hl-code"> </span><span class="hl-identifier">runoob_title</span><span class="hl-code">=</span><span class="hl-quotes">'</span><span class="hl-string">学习 C++</span><span class="hl-quotes">'</span><span class="hl-code"> </span><span class="hl-reserved">WHERE</span><span class="hl-code"> </span><span class="hl-identifier">runoob_id</span><span class="hl-code">=</span><span class="hl-number">3</span><span class="hl-code">; </span><span class="hl-identifier">Query</span><span class="hl-code"> </span><span class="hl-identifier">OK</span><span class="hl-code">, </span><span class="hl-number">1</span><span class="hl-code"> </span><span class="hl-reserved">rows</span><span class="hl-code"> </span><span class="hl-identifier">affected</span><span class="hl-code"> </span><span class="hl-brackets">(</span><span class="hl-number">0.01</span><span class="hl-code"> </span><span class="hl-identifier">sec</span><span class="hl-brackets">)</span><span class="hl-code"> </span><span class="hl-identifier">mysql</span><span class="hl-code">> </span><span class="hl-reserved">SELECT</span><span class="hl-code"> * </span><span class="hl-reserved">from</span><span class="hl-code"> </span><span class="hl-identifier">runoob_tbl</span><span class="hl-code"> </span><span class="hl-reserved">WHERE</span><span class="hl-code"> </span><span class="hl-identifier">runoob_id</span><span class="hl-code">=</span><span class="hl-number">3</span><span class="hl-code">; +-----------+--------------+---------------+-----------------+ | </span><span class="hl-identifier">runoob_id</span><span class="hl-code"> | </span><span class="hl-identifier">runoob_title</span><span class="hl-code"> | </span><span class="hl-identifier">runoob_author</span><span class="hl-code"> | </span><span class="hl-identifier">submission_date</span><span class="hl-code"> | +-----------+--------------+---------------+-----------------+ | </span><span class="hl-number">3</span><span class="hl-code"> | 学习 </span><span class="hl-var">C</span><span class="hl-code">++ | </span><span class="hl-identifier">RUNOOB</span><span class="hl-code">.</span><span class="hl-identifier">COM</span><span class="hl-code"> | </span><span class="hl-number">2016</span><span class="hl-code">-</span><span class="hl-number">05</span><span class="hl-code">-</span><span class="hl-number">06</span><span class="hl-code"> | +-----------+--------------+---------------+-----------------+ </span><span class="hl-number">1</span><span class="hl-code"> </span><span class="hl-reserved">rows</span><span class="hl-code"> </span><span class="hl-reserved">in</span><span class="hl-code"> </span><span class="hl-reserved">set</span><span class="hl-code"> </span><span class="hl-brackets">(</span><span class="hl-number">0.01</span><span class="hl-code"> </span><span class="hl-identifier">sec</span><span class="hl-brackets">)</span></div> <button class="copy-code-button" type="button" data-clipboard-text="mysql> UPDATE runoob_tbl SET runoob_title='学习 C++' WHERE runoob_id=3; Query OK, 1 rows affected (0.01 sec) mysql> SELECT * from runoob_tbl WHERE runoob_id=3; +-----------+--------------+---------------+-----------------+ | runoob_id | runoob_title | runoob_author | submission_date | +-----------+--------------+---------------+-----------------+ | 3 | 学习 C++ | RUNOOB.COM | 2016-05-06 | +-----------+--------------+---------------+-----------------+ 1 rows in set (0.01 sec)"></button></div> </div> <p>从结果上看,runoob_id 为 3 的 runoob_title 已被修改。</p> <hr> <h2>使用PHP脚本更新数据</h2> <p> PHP 中使用函数 mysqli_query() 来执行 SQL 语句,你可以在 SQL UPDATE 语句中使用或者不使用 WHERE 子句。 </p><p><strong>注意:</strong>不使用 WHERE 子句将数据表的全部数据进行更新,所以要慎重。</p> <p> </p><p> 该函数与在 <span class="marked">mysql></span> 命令提示符中执行 SQL 语句的效果是一样的。 </p> <h3>实例</h3> <p> 以下实例将更新 runoob_id 为 3 的 runoob_title 字段的数据。 </p> <div class="example"> <h2 class="example">MySQL UPDATE 语句测试:</h2> <div class="example_code"> <div class="hl-main"><span class="hl-inlinetags"><?php</span><span class="hl-code"> </span><span class="hl-var">$dbhost</span><span class="hl-code"> = </span><span class="hl-quotes">'</span><span class="hl-string">localhost</span><span class="hl-quotes">'</span><span class="hl-code">; </span><span class="hl-comment">//</span><span class="hl-comment"> mysql服务器主机地址</span><span class="hl-comment"></span><span class="hl-code"> </span><span class="hl-var">$dbuser</span><span class="hl-code"> = </span><span class="hl-quotes">'</span><span class="hl-string">root</span><span class="hl-quotes">'</span><span class="hl-code">; </span><span class="hl-comment">//</span><span class="hl-comment"> mysql用户名</span><span class="hl-comment"></span><span class="hl-code"> </span><span class="hl-var">$dbpass</span><span class="hl-code"> = </span><span class="hl-quotes">'</span><span class="hl-string">123456</span><span class="hl-quotes">'</span><span class="hl-code">; </span><span class="hl-comment">//</span><span class="hl-comment"> mysql用户名密码</span><span class="hl-comment"></span><span class="hl-code"> </span><span class="hl-var">$conn</span><span class="hl-code"> = </span><span class="hl-identifier">mysqli_connect</span><span class="hl-brackets">(</span><span class="hl-var">$dbhost</span><span class="hl-code">, </span><span class="hl-var">$dbuser</span><span class="hl-code">, </span><span class="hl-var">$dbpass</span><span class="hl-brackets">)</span><span class="hl-code">; </span><span class="hl-reserved">if</span><span class="hl-brackets">(</span><span class="hl-code">! </span><span class="hl-var">$conn</span><span class="hl-code"> </span><span class="hl-brackets">)</span><span class="hl-code"> </span><span class="hl-brackets">{</span><span class="hl-code"> </span><span class="hl-reserved">die</span><span class="hl-brackets">(</span><span class="hl-quotes">'</span><span class="hl-string">连接失败: </span><span class="hl-quotes">'</span><span class="hl-code"> . </span><span class="hl-identifier">mysqli_error</span><span class="hl-brackets">(</span><span class="hl-var">$conn</span><span class="hl-brackets">)</span><span class="hl-brackets">)</span><span class="hl-code">; </span><span class="hl-brackets">}</span><span class="hl-code"> </span><span class="hl-comment">//</span><span class="hl-comment"> 设置编码,防止中文乱码</span><span class="hl-comment"></span><span class="hl-code"> </span><span class="hl-identifier">mysqli_query</span><span class="hl-brackets">(</span><span class="hl-var">$conn</span><span class="hl-code"> , </span><span class="hl-quotes">"</span><span class="hl-string">set names utf8</span><span class="hl-quotes">"</span><span class="hl-brackets">)</span><span class="hl-code">; </span><span class="hl-var">$sql</span><span class="hl-code"> = </span><span class="hl-quotes">'</span><span class="hl-string">UPDATE runoob_tbl SET runoob_title="学习 Python" WHERE runoob_id=3</span><span class="hl-quotes">'</span><span class="hl-code">; </span><span class="hl-identifier">mysqli_select_db</span><span class="hl-brackets">(</span><span class="hl-code"> </span><span class="hl-var">$conn</span><span class="hl-code">, </span><span class="hl-quotes">'</span><span class="hl-string">RUNOOB</span><span class="hl-quotes">'</span><span class="hl-code"> </span><span class="hl-brackets">)</span><span class="hl-code">; </span><span class="hl-var">$retval</span><span class="hl-code"> = </span><span class="hl-identifier">mysqli_query</span><span class="hl-brackets">(</span><span class="hl-code"> </span><span class="hl-var">$conn</span><span class="hl-code">, </span><span class="hl-var">$sql</span><span class="hl-code"> </span><span class="hl-brackets">)</span><span class="hl-code">; </span><span class="hl-reserved">if</span><span class="hl-brackets">(</span><span class="hl-code">! </span><span class="hl-var">$retval</span><span class="hl-code"> </span><span class="hl-brackets">)</span><span class="hl-code"> </span><span class="hl-brackets">{</span><span class="hl-code"> </span><span class="hl-reserved">die</span><span class="hl-brackets">(</span><span class="hl-quotes">'</span><span class="hl-string">无法更新数据: </span><span class="hl-quotes">'</span><span class="hl-code"> . </span><span class="hl-identifier">mysqli_error</span><span class="hl-brackets">(</span><span class="hl-var">$conn</span><span class="hl-brackets">)</span><span class="hl-brackets">)</span><span class="hl-code">; </span><span class="hl-brackets">}</span><span class="hl-code"> </span><span class="hl-reserved">echo</span><span class="hl-code"> </span><span class="hl-quotes">'</span><span class="hl-string">数据更新成功!</span><span class="hl-quotes">'</span><span class="hl-code">; </span><span class="hl-identifier">mysqli_close</span><span class="hl-brackets">(</span><span class="hl-var">$conn</span><span class="hl-brackets">)</span><span class="hl-code">; </span><span class="hl-inlinetags">?></span></div> <button class="copy-code-button" type="button" data-clipboard-text="<?php $dbhost = 'localhost'; // 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 = '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 '数据更新成功!'; mysqli_close($conn); ?>"></button></div> </div> <!-- 其他扩展 --> </div> <div class="article-intro" id="content"> <h1> MySQL DELETE 语句 </h1> <p> 你可以使用 <span class="marked">DELETE FROM</span> 命令来删除 MySQL 数据表中的记录。 </p><p> 你可以在 <span class="marked">mysql></span> 命令提示符或 PHP 脚本中执行该命令。 </p> <h3>语法</h3><p> 以下是 DELETE 语句从 MySQL 数据表中删除数据的通用语法:</p> <pre class="prettyprint prettyprinted" style=""><span class="pln">DELETE FROM table_name WHERE condition</span><span class="pun">;</span><button class="copy-code-button" type="button" data-clipboard-text="DELETE FROM table_name WHERE condition; "></button></pre> <p><strong>参数说明:</strong></p> <ul><li><code>table_name</code> 是你要删除数据的表的名称。</li><li><code>WHERE condition</code> 是一个可选的子句,用于指定删除的行。如果省略 <code>WHERE</code> 子句,将删除表中的所有行。</li></ul> <p><strong>更多说明:</strong></p> <ul> <li> 如果没有指定 WHERE 子句,MySQL 表中的所有记录将被删除。</li><li> 你可以在 WHERE 子句中指定任何条件</li><li> 您可以在单个表中一次性删除记录。</li></ul> <p> 当你想删除数据表中指定的记录时 WHERE 子句是非常有用的。 </p> <h3>实例</h3><p> 以下实例演示了如何使用 DELETE 语句。</p> <p> 1. 删除符合条件的行:</p><pre class="prettyprint prettyprinted" style=""><span class="pln">DELETE FROM students WHERE graduation_year </span><span class="pun">=</span><span class="pln"> </span><span class="lit">2021</span><span class="pun">;</span><button class="copy-code-button" type="button" data-clipboard-text="DELETE FROM students WHERE graduation_year = 2021;"></button></pre><p> 以上 SQL 语句删除了 students 表中所有 graduation_year 为 2021 的学生的记录。</p> <p> 2. 删除所有行:</p><pre class="prettyprint prettyprinted" style=""><span class="pln">DELETE FROM orders</span><span class="pun">;</span><button class="copy-code-button" type="button" data-clipboard-text="DELETE FROM orders;"></button></pre><p> 以上 SQL 语句删除了 orders 表中的所有记录,但表结构保持不变。</p> <p> 3. 使用子查询删除符合条件的行:</p> <pre class="prettyprint prettyprinted" style=""><span class="pln">DELETE FROM customers WHERE customer_id IN </span><span class="pun">(</span><span class="pln"> SELECT customer_id FROM orders WHERE order_date </span><span class="pun"><</span><span class="pln"> </span><span class="str">'2023-01-01'</span><span class="pln"> </span><span class="pun">);</span><button class="copy-code-button" type="button" data-clipboard-text="DELETE FROM customers WHERE customer_id IN ( SELECT customer_id FROM orders WHERE order_date < '2023-01-01' );"></button></pre> <p>以上 SQL 语句通过子查询删除了 orders 表中在 '2023-01-01' 之前下的订单对应的客户。</p> <blockquote> <p> <strong>注意:</strong> 在使用 DELETE 语句时,请确保你提供了足够的条件来确保只有你想要删除的行被删除。如果不提供 WHERE 子句,将删除表中的所有行,可能导致不可预测的结果。</p></blockquote> <hr> <h2>从命令行中删除数据</h2> <p>这里我们将在 DELETE 命令中使用 WHERE 子句来删除 MySQL 数据表 runoob_tbl 所选的数据。</p> <h3>实例</h3> <p>以下实例将删除 runoob_tbl 表中 runoob_id 为3 的记录:</p> <div class="example"> <h2 class="example">DELETE 语句:</h2> <div class="example_code"> <div class="hl-main"><span class="hl-identifier">mysql</span><span class="hl-code">> </span><span class="hl-identifier">use</span><span class="hl-code"> </span><span class="hl-identifier">RUNOOB</span><span class="hl-code">; </span><span class="hl-identifier">Database</span><span class="hl-code"> </span><span class="hl-identifier">changed</span><span class="hl-code"> </span><span class="hl-identifier">mysql</span><span class="hl-code">> </span><span class="hl-reserved">DELETE</span><span class="hl-code"> </span><span class="hl-reserved">FROM</span><span class="hl-code"> </span><span class="hl-identifier">runoob_tbl</span><span class="hl-code"> </span><span class="hl-reserved">WHERE</span><span class="hl-code"> </span><span class="hl-identifier">runoob_id</span><span class="hl-code">=</span><span class="hl-number">3</span><span class="hl-code">; </span><span class="hl-identifier">Query</span><span class="hl-code"> </span><span class="hl-identifier">OK</span><span class="hl-code">, </span><span class="hl-number">1</span><span class="hl-code"> </span><span class="hl-reserved">row</span><span class="hl-code"> </span><span class="hl-identifier">affected</span><span class="hl-code"> </span><span class="hl-brackets">(</span><span class="hl-number">0.23</span><span class="hl-code"> </span><span class="hl-identifier">sec</span><span class="hl-brackets">)</span></div> <button class="copy-code-button" type="button" data-clipboard-text="mysql> use RUNOOB; Database changed mysql> DELETE FROM runoob_tbl WHERE runoob_id=3; Query OK, 1 row affected (0.23 sec)"></button></div> </div> <hr><h2>使用 PHP 脚本删除数据</h2> <p>PHP 使用 mysqli_query() 函数来执行SQL语句, 你可以在 DELETE 命令中使用或不使用 WHERE 子句。</p> <p>该函数与 <span class="marked">mysql></span> 命令符执行SQL命令的效果是一样的。</p> <h3>实例</h3> <p>以下PHP实例将删除 runoob_tbl 表中 runoob_id 为 3 的记录:</p> <div class="example"> <h2 class="example">MySQL DELETE 子句测试:</h2> <div class="example_code"> <div class="hl-main"><span class="hl-inlinetags"><?php</span><span class="hl-code"> </span><span class="hl-var">$dbhost</span><span class="hl-code"> = </span><span class="hl-quotes">'</span><span class="hl-string">localhost</span><span class="hl-quotes">'</span><span class="hl-code">; </span><span class="hl-comment">//</span><span class="hl-comment"> mysql服务器主机地址</span><span class="hl-comment"></span><span class="hl-code"> </span><span class="hl-var">$dbuser</span><span class="hl-code"> = </span><span class="hl-quotes">'</span><span class="hl-string">root</span><span class="hl-quotes">'</span><span class="hl-code">; </span><span class="hl-comment">//</span><span class="hl-comment"> mysql用户名</span><span class="hl-comment"></span><span class="hl-code"> </span><span class="hl-var">$dbpass</span><span class="hl-code"> = </span><span class="hl-quotes">'</span><span class="hl-string">123456</span><span class="hl-quotes">'</span><span class="hl-code">; </span><span class="hl-comment">//</span><span class="hl-comment"> mysql用户名密码</span><span class="hl-comment"></span><span class="hl-code"> </span><span class="hl-var">$conn</span><span class="hl-code"> = </span><span class="hl-identifier">mysqli_connect</span><span class="hl-brackets">(</span><span class="hl-var">$dbhost</span><span class="hl-code">, </span><span class="hl-var">$dbuser</span><span class="hl-code">, </span><span class="hl-var">$dbpass</span><span class="hl-brackets">)</span><span class="hl-code">; </span><span class="hl-reserved">if</span><span class="hl-brackets">(</span><span class="hl-code">! </span><span class="hl-var">$conn</span><span class="hl-code"> </span><span class="hl-brackets">)</span><span class="hl-code"> </span><span class="hl-brackets">{</span><span class="hl-code"> </span><span class="hl-reserved">die</span><span class="hl-brackets">(</span><span class="hl-quotes">'</span><span class="hl-string">连接失败: </span><span class="hl-quotes">'</span><span class="hl-code"> . </span><span class="hl-identifier">mysqli_error</span><span class="hl-brackets">(</span><span class="hl-var">$conn</span><span class="hl-brackets">)</span><span class="hl-brackets">)</span><span class="hl-code">; </span><span class="hl-brackets">}</span><span class="hl-code"> </span><span class="hl-comment">//</span><span class="hl-comment"> 设置编码,防止中文乱码</span><span class="hl-comment"></span><span class="hl-code"> </span><span class="hl-identifier">mysqli_query</span><span class="hl-brackets">(</span><span class="hl-var">$conn</span><span class="hl-code"> , </span><span class="hl-quotes">"</span><span class="hl-string">set names utf8</span><span class="hl-quotes">"</span><span class="hl-brackets">)</span><span class="hl-code">; </span><span class="hl-var">$sql</span><span class="hl-code"> = </span><span class="hl-quotes">'</span><span class="hl-string">DELETE FROM runoob_tbl WHERE runoob_id=3</span><span class="hl-quotes">'</span><span class="hl-code">; </span><span class="hl-identifier">mysqli_select_db</span><span class="hl-brackets">(</span><span class="hl-code"> </span><span class="hl-var">$conn</span><span class="hl-code">, </span><span class="hl-quotes">'</span><span class="hl-string">RUNOOB</span><span class="hl-quotes">'</span><span class="hl-code"> </span><span class="hl-brackets">)</span><span class="hl-code">; </span><span class="hl-var">$retval</span><span class="hl-code"> = </span><span class="hl-identifier">mysqli_query</span><span class="hl-brackets">(</span><span class="hl-code"> </span><span class="hl-var">$conn</span><span class="hl-code">, </span><span class="hl-var">$sql</span><span class="hl-code"> </span><span class="hl-brackets">)</span><span class="hl-code">; </span><span class="hl-reserved">if</span><span class="hl-brackets">(</span><span class="hl-code">! </span><span class="hl-var">$retval</span><span class="hl-code"> </span><span class="hl-brackets">)</span><span class="hl-code"> </span><span class="hl-brackets">{</span><span class="hl-code"> </span><span class="hl-reserved">die</span><span class="hl-brackets">(</span><span class="hl-quotes">'</span><span class="hl-string">无法删除数据: </span><span class="hl-quotes">'</span><span class="hl-code"> . </span><span class="hl-identifier">mysqli_error</span><span class="hl-brackets">(</span><span class="hl-var">$conn</span><span class="hl-brackets">)</span><span class="hl-brackets">)</span><span class="hl-code">; </span><span class="hl-brackets">}</span><span class="hl-code"> </span><span class="hl-reserved">echo</span><span class="hl-code"> </span><span class="hl-quotes">'</span><span class="hl-string">数据删除成功!</span><span class="hl-quotes">'</span><span class="hl-code">; </span><span class="hl-identifier">mysqli_close</span><span class="hl-brackets">(</span><span class="hl-var">$conn</span><span class="hl-brackets">)</span><span class="hl-code">; </span><span class="hl-inlinetags">?></span></div> <button class="copy-code-button" type="button" data-clipboard-text="<?php $dbhost = 'localhost'; // 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 = '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 '数据删除成功!'; mysqli_close($conn); ?>"></button></div> </div> <!-- 其他扩展 --> </div>
admin
2024年11月8日 17:03
转发文档
收藏文档
上一篇
下一篇
手机扫码
复制链接
手机扫一扫转发分享
复制链接
Markdown文件
分享
链接
类型
密码
更新密码