Bii o ṣe le lo MySQL nibiti alaye? ibi ti gbolohun ọpọ ni àídájú sintasi

MySQL databaseBawo ni lati lo ibi ti alaye? ibi ti gbolohun ọpọ awọn ipo sintasi

MySQL NIBI gbolohun ọrọ

A mọ lati lo SQL SELECT gbólóhùn lati ka data lati MySQL tabili.

Lati yan data ni ipo lati tabili kan, ṣafikun gbolohun NIBI kan si alaye YAN.

girama

Atẹle naa ni sintasi gbogbogbo ti alaye SQL SELECT lati ka data lati tabili data nipa lilo gbolohun NIBI:

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....

• O le lo ọkan tabi diẹ ẹ sii tabili ninu alaye ibeere, lilo aami idẹsẹ laarin awọn tabili, Pin, ki o lo alaye NIBI lati ṣeto awọn ipo ibeere.
• O le pato ipo eyikeyi ninu gbolohun ọrọ NIBI.
• O le lo ATI tabi TABI lati pato ọkan tabi diẹ ẹ sii awọn ipo.
• Awọn gbolohun WHERE tun le ṣee lo pẹlu SQL's DELETE tabi awọn aṣẹ imudojuiwọn.
• Awọn gbolohun WHERE jẹ iru si ipo ti o ba wa ni ede siseto, kika data ti a pato gẹgẹbi iye aaye ni tabili MySQL.

Atẹle ni atokọ ti awọn oniṣẹ ti o le ṣee lo ninu gbolohun NIBI.

Awọn apẹẹrẹ ninu tabili ni isalẹ ro pe A jẹ 10 ati B jẹ 20

Awọn gbolohun WHERE wulo pupọ ti a ba fẹ ka data ti a ti sọ pato lati inu tabili data MySQL.

Lilo bọtini akọkọ bi ibeere ipo ni WHERE gbolohun ti o yara pupọ.

Ti awọn iyasọtọ ti a fun ko ba ni awọn igbasilẹ ibaramu eyikeyi ninu tabili, ibeere naa ko pada data kankan.

ka data lati aṣẹ tọ

A yoo lo gbolohun WHERE ninu alaye SQL SELECT lati ka data naa ninu tabili data MySQL chenweiliang_tbl:

Apeere

Apẹẹrẹ atẹle yoo ka gbogbo awọn igbasilẹ ninu tabili chenweiliang_tbl nibiti iye aaye chenweiliang_author jẹ Sanjay:

Awọn afiwe okun ni MySQL's WHERE gbolohun ọrọ ko ṣe pataki.O le lo koko-ọrọ alakomeji lati ṣọkasi pe awọn afiwe okun ninu gbolohun WHERE jẹ ifarabalẹ ọran.

Apẹẹrẹ atẹle:

mysql> SELECT * from chenweiliang_tbl WHERE BINARY chenweiliang_author='chenweiliang.com';
Empty set (0.01 sec)
 
mysql> SELECT * from chenweiliang_tbl WHERE BINARY chenweiliang_author='chenweiliang.com';
+-----------+---------------+---------------+-----------------+
| chenweiliang_id | chenweiliang_title | chenweiliang_author | submission_date |
+-----------+---------------+---------------+-----------------+
| 3 | JAVA 教程 | chenweiliang.com | 2016-05-06 |
| 4 | 学习 Python | chenweiliang.com | 2016-03-06 |
+-----------+---------------+---------------+-----------------+
2 rows in set (0.01 sec)

lo ninu apẹẹrẹ Alakomeji koko, ni irú-kókó, rẹ chenweiliang_author='chenweiliang.com' Ipo ibeere kii ṣe data.

Ka data nipa lilo iwe afọwọkọ PHP

O le lo iṣẹ PHP mysqli_query () ati aṣẹ SQL SELECT kanna pẹlu gbolohun NIBI lati gba data naa.

Iṣẹ yii ni a lo lati ṣiṣẹ awọn aṣẹ SQL ati lẹhinna gbejade gbogbo data ti o beere nipasẹ iṣẹ PHP mysqli_fetch_array ().

Apeere

Apẹẹrẹ atẹle yoo pada lati tabili chenweiliang_tbl nipa lilo iye aaye aaye chenweiliang_author bi chenweiliang.com igbasilẹ ti:

MySQL NIBI idanwo gbolohun ọrọ:

<?
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");
 
// 读取 chenweiliang_author 为 chenweiliang.com 的数据
$sql = 'SELECT chenweiliang_id, chenweiliang_title, 
 chenweiliang_author, submission_date
 FROM chenweiliang_tbl
 WHERE chenweiliang_author="chenweiliang.com"';
 
mysqli_select_db( $conn, 'chenweiliang' );
$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, MYSQL_ASSOC))
{
 echo "<tr><td> {$row['chenweiliang_id']}</td> ".
 "<td>{$row['chenweiliang_title']} </td> ".
 "<td>{$row['chenweiliang_author']} </td> ".
 "<td>{$row['submission_date']} </td> ".
 "</tr>";
}
echo '</table>';
// 释放内存
mysqli_free_result($retval);
mysqli_close($conn);
?>