MySQL——查询数据

查询数据

语法：SELECT column_name,column_name FROM tabel_name;

• 一个或多个表(查询时)
• SELECTk而已读取一条躲着多条记录
• *代替其他字段，SELECT会返回表的所有字段数据
• WHERE语句来包含任何条件
• LIMIT属性来设定返回的记录数
• OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。

---

通过命令提示符获取数据

---

使用PHP脚本来获取数据

mysqli_fetch_array()

语法：myslqi_fetch_array(result,resulttype);

• result:规定由mysql_query(),mysql_store_result()或mysqli_use_result()返回的结果集标识符(必须)。
• resulttype: 规定应该产生那种类型的数组(可选)
  MYSQLI_ASSOC：关联数组
  MYSQLI_NUM：数字数组
  MYSQLI_BOTH：都返回

<?php
header("content-type:text/html;charset=utf-8");

$dbhost='localhost';
$dbuser='root';
$dbpass='root';
$conn = mysqli_connect($dbhost,$dbuser,$dbpass);
if(!$conn)
{
    die('连接失败:'.mysqli_error($conn));
}
mysqli_query($conn,"set names utf-8");

$sql='SELECT id,title,author,submission_date FROM bird_tbl';
mysqli_select_db($conn,'bird');
$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['id']}</td>".
    "<td>{$row['title']}</td>".
    "<td>{$row['author']}</td>".
    "<td>{$row['submission_date']}</td>".
    "</tr>";
}
echo '</table>';
mysqli_close($conn);
?>

---

mysqli_fetch_assoc()

该函数从结果集中取得一行为关联数组。返回根据从结果集取得行生成的关联数组，若果没有更多行，则返回false.

<?php
header("content-type:text/html;charset=utf-8");

$dbhost='localhost';
$dbuser='root';
$dbpass='root';
$conn=mysqli_connect($dbhost,$dbuser,$dbpass);

if(!$conn)
{
    die('连接失败:'.mysqli_error($conn));
}
mysqli_query($conn,"set names utf8");

$sql = 'SELECT id,title,author,submission_date FROM bird_tbl';

mysqli_select_db($conn,'bird');
$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['id']}</td>".
    "<td>{$row['title']}</td>". 
    "<td>{$row['author']}</td>". 
    "<td>{$row['submission_date']}</td>". 
    "</tr>";
}
echo '</table>';
mysqli_close($conn);
?>

---

MYSQLI_NUM

<?php

header("content-type:text/html;charset=utf-8");
$dbhost='localhost';
$dbuser='root';
$dbpass='root';
$conn=mysqli_connect($dbhost,$dbuser,$dbpass);
if(!$conn)
{
    die('连接失败:'.mysqli_error($conn));
}
mysqli_query($conn,"set names utf8");
$sql= 'SELECT id,title,author,submission_date FROM bird_tbl';

mysqli_select_db($conn,'bird');//使用数据库
$retval=mysqli_query($conn,$sql);
if(!$retval)
{
    die('无法读取数据:'.mysqli_error($conn));
}
echo '<h2>MYSLQI_NUM</h2>';
echo '<table border="1"><tr><td>id</td><td>标题</td><td>作者</td><td>时间</td>';

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