MySQL查询技巧-优快云博客

本文链接：https://blog.youkuaiyun.com/heshihu2019/article/details/101022713

MySQL where 子句

我们知道从MySQL表中使用SQL SELECT 语句来读取数据。
如需有条件地从表中选取数据，可将 WHERE 子句添加到 SELECT 语句中。
语法
以下是SQL SELECT 语句使用 WHERE 子句从数据表中读取数据的通用语法：

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

查询语句中你可以使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件。
你可以在WHERE子句中指定任何条件。
你可以使用AND或者OR指定一个或多个条件。
WHERE子句也可以运用于SQL的 DELETE 或者 UPDATE 命令。
WHERE 子句类似于程序语言中的if条件，根据 MySQL 表中的字段值来读取指定的数据。
以下为操作符列表，可用于 WHERE 子句中。
下表中实例假定 A为10 B为20

**操作符	  描述	  实例**
=	 等号，检测两个值是否相等，如果相等返回true	  (A = B) 返回false。
, !=	 不等于，检测两个值是否相等，如果不相等返回true	   (A != B) 返回 true。
>	 大于号，检测左边的值是否大于右边的值, 如果左边的值大于右边的值返回true	  (A > B) 返回false。
<	 小于号，检测左边的值是否小于右边的值, 如果左边的值小于右边的值返回true	   (A < B) 返回 true。
>=	 大于等于号，检测左边的值是否大于或等于右边的值, 如果左边的值大于或等于右边的值返回true	  (A >= B) 返回false。
<=	 小于等于号，检测左边的值是否小于于或等于右边的值, 如果左边的值小于或等于右边的值返回true	  (A <= B) 返回 true。

如果我们想再MySQL数据表中读取指定的数据，WHERE 子句是非常有用的。
使用主键来作为 WHERE 子句的条件查询是非常快速的。
如果给定的条件在表中没有任何匹配的记录，那么查询不会返回任何数据。

从命令提示符中读取数据

我们将在SQL SELECT语句使用WHERE子句来读取MySQL数据表 tutorials_tbl 中的数据：
实例
以下实例将读取 tutorials_tbl 表中 tutorial_author 字段值为 Sanjay 的所有记录：

root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> SELECT * from tutorials_tbl WHERE tutorial_author='Sanjay';
+-------------+----------------+-----------------+-----------------+
| tutorial_id | tutorial_title | tutorial_author | submission_date |
+-------------+----------------+-----------------+-----------------+
|           3 | JAVA Tutorial  | Sanjay          | 2007-05-21      |
+-------------+----------------+-----------------+-----------------+
1 rows in set (0.01 sec)
 
mysql>

除非你使用 LIKE 来比较字符串，否则MySQL的WHERE子句的字符串比较是不区分大小写的。你可以使用 BINARY 关键字来设定WHERE子句的字符串比较是区分大小写的。
如下实例

root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> SELECT * from tutorials_tbl \
          WHERE BINARY tutorial_author='sanjay';
Empty set (0.02 sec)
 
mysql>

使用PHP脚本读取数据

你可以使用PHP函数的mysql_query()及相同的SQL SELECT 带上 WHERE 子句的命令来获取数据。
该函数用于执行SQL命令，然后通过 PHP 函数 mysql_fetch_array() 来输出所有查询的数据。
实例
以下实例将从 tutorials_tbl 表中返回使用 tutorial_author 字段值为 Sanjay 的记录：

<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}
$sql = 'SELECT tutorial_id, tutorial_title, 
               tutorial_author, submission_date
        FROM tutorials_tbl
        WHERE tutorial_author="Sanjay"';
 
mysql_select_db('TUTORIALS');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_array($retval, MYSQL_ASSOC))
{
    echo "Tutorial ID :{$row['tutorial_id']}  <br> ".
         "Title: {$row['tutorial_title']} <br> ".
         "Author: {$row['tutorial_author']} <br> ".
         "Submission Date : {$row['submission_date']} <br> ".
         "--------------------------------<br>";
} 
echo "Fetched data successfully\n";
mysql_close($conn);
?>

MySQL UPDATE 查询

如果我们需要修改或更新MySQL中的数据，我们可以使用 SQL UPDATE 命令来操作。.
语法
以下是 UPDATE 命令修改 MySQL 数据表数据的通用SQL语法：

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

你可以同时更新一个或多个字段。
你可以在 WHERE 子句中指定任何条件。
你可以在一个单独表中同时更新数据。
当你需要更新数据表中指定行的数据时 WHERE 子句是非常有用的。

通过命令提示符更新数据

以下我们将在 SQL UPDATE 命令使用 WHERE子句来更新tutorials_tbl表中指定的数据：
实例
以下实例将更新数据表中 tutorial_id 为 3 的 tutorial_title 字段值：

root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> UPDATE tutorials_tbl 
    -> SET tutorial_title='Learning JAVA' 
    -> WHERE tutorial_id=3;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql>

使用PHP脚本更新数据

PHP中使用函数mysql_query()来执行SQL语句，你可以在SQL UPDATE语句中使用或者不适用WHERE子句。
该函数与在mysql>命令提示符中执行SQL语句的效果是一样的。
实例
以下实例将更新 tutorial_id 为3的 tutorial_title 字段的数据。

<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}
$sql = 'UPDATE tutorials_tbl
        SET tutorial_title="Learning JAVA"
        WHERE tutorial_id=3';
 
mysql_select_db('TUTORIALS');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not update data: ' . mysql_error());
}
echo "Updated data successfully\n";
mysql_close($conn);
?>

MySQL DELETE 语句

你可以使用 SQL 的 DELETE FROM 命令来删除 MySQL 数据表中的记录。
你可以在mysql>命令提示符或PHP脚本中执行该命令。
语法
以下是SQL DELETE 语句从MySQL数据表中删除数据的通用语法：

DELETE FROM table_name [WHERE Clause]

如果没有指定 WHERE 子句，MySQL表中的所有记录将被删除。
你可以在 WHERE 子句中指定任何条件
您可以在单个表中一次性删除记录。
当你想删除数据表中指定的记录时 WHERE 子句是非常有用的。
从命令行中删除数据
这里我们将在 SQL DELETE 命令中使用 WHERE 子句来删除MySQL数据表tutorials_tbl所选的数据。
实例
以下实例将删除 tutorial_tbl 表中 tutorial_id 为3 的记录：

root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> DELETE FROM tutorials_tbl WHERE tutorial_id=3;
Query OK, 1 row affected (0.23 sec)
 
mysql>

使用 PHP 脚本删除数据

PHP使用 mysql_query() 函数来执行SQL语句，你可以在SQL DELETE命令中使用或不使用 WHERE 子句。
该函数与 mysql>命令符执行SQL命令的效果是一样的。
实例
以下PHP实例将删除tutorial_tbl表中tutorial_id为3的记录:

<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}
$sql = 'DELETE FROM tutorials_tbl
        WHERE tutorial_id=3';
 
mysql_select_db('TUTORIALS');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not delete data: ' . mysql_error());
}
echo "Deleted data successfully\n";
mysql_close($conn);
?>

MySQL LIKE 子句

我们知道在MySQL中使用 SQL SELECT 命令来读取数据，同时我们可以在 SELECT 语句中使用 WHERE 子句来获取指定的记录。
WHERE 子句中可以使用等号 (=) 来设定获取数据的条件，如 “tutorial_author = ‘Sanjay’”。
但是有时候我们需要获取 tutorial_author 字段含有 “jay” 字符的所有记录，这时我们就需要在 WHERE 子句中使用 SQL LIKE 子句。
SQL LIKE 子句中使用百分号(%)字符来表示任意字符，类似于UNIX或正则表达式中的星号 (*)。
如果没有使用百分号(%), LIKE 子句与等号（=）的效果是一样的。
语法
以下是SQL SELECT 语句使用 LIKE 子句从数据表中读取数据的通用语法：

SELECT field1, field2,...fieldN table_name1, table_name2...
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'

你可以在WHERE子句中指定任何条件。
你可以在WHERE子句中使用LIKE子句。
你可以使用LIKE子句代替等号(=)。
LIKE 通常与 % 一同使用，类似于一个元字符的搜索。
你可以使用AND或者OR指定一个或多个条件。
你可以在 DELETE 或 UPDATE 命令中使用 WHERE…LIKE 子句来指定条件。

在命令提示符中使用 LIKE 子句

以下我们将在 SQL SELECT 命令中使用 WHERE…LIKE 子句来从MySQL数据表 tutorials_tbl 中读取数据。
实例
以下是我们将tutorials_tbl表中获取tutorial_author字段中以"jay"为结尾的的所有记录：

root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> SELECT * from tutorials_tbl 
    -> WHERE tutorial_author LIKE '%jay';
+-------------+----------------+-----------------+-----------------+
| tutorial_id | tutorial_title | tutorial_author | submission_date |
+-------------+----------------+-----------------+-----------------+
|           3 | JAVA Tutorial  | Sanjay          | 2007-05-21      |
+-------------+----------------+-----------------+-----------------+
1 rows in set (0.01 sec)
 
mysql>

在PHP脚本中使用 LIKE 子句

你可以使用PHP函数的mysql_query()及相同的SQL SELECT 带上 WHERE…LIKE 子句的命令来获取数据。
该函数用于执行SQL命令，然后通过 PHP 函数 mysql_fetch_array() 来输出所有查询的数据。
但是如果是DELETE或者UPDATE中使用 WHERE…LIKE 子句的SQL语句，则无需使用mysql_fetch_array() 函数。
实例
以下是我们使用PHP脚本在tutorials_tbl表中读取tutorial_author字段中以"jay"为结尾的的所有记录：

<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}
$sql = 'SELECT tutorial_id, tutorial_title, 
               tutorial_author, submission_date
        FROM tutorials_tbl
        WHERE tutorial_author LIKE "%jay%"';
 
mysql_select_db('TUTORIALS');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_array($retval, MYSQL_ASSOC))
{
    echo "Tutorial ID :{$row['tutorial_id']}  <br> ".
         "Title: {$row['tutorial_title']} <br> ".
         "Author: {$row['tutorial_author']} <br> ".
         "Submission Date : {$row['submission_date']} <br> ".
         "--------------------------------<br>";
} 
echo "Fetched data successfully\n";
mysql_close($conn);
?>

MySQL 排序

我们知道从MySQL表中使用SQL SELECT 语句来读取数据。
如果我们需要对读取的数据进行排序，我们就可以使用MySQL的 ORDER BY 子句来设定你想按哪个字段哪中方式来进行排序，再返回搜索结果。
语法
以下是SQL SELECT 语句使用 ORDER BY 子句将查询数据排序后再返回数据：

SELECT field1, field2,...fieldN table_name1, table_name2...
ORDER BY field1, [field2...] [ASC [DESC]]

你可以使用任何字段来作为排序的条件，从而返回排序后的查询结果。
你可以设定多个字段来排序。
你可以使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。默认情况下，它是按升排列。
你可以添加 WHERE…LIKE 子句来设置条件。

在命令提示符中使用 ORDER BY 子句

以下将在 SQL SELECT 语句中使用 ORDER BY 子句来读取MySQL 数据表 tutorials_tbl 中的数据：
实例
尝试以下实例，结果将按升序排列

root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> SELECT * from tutorials_tbl ORDER BY tutorial_author ASC
+-------------+----------------+-----------------+-----------------+
| tutorial_id | tutorial_title | tutorial_author | submission_date |
+-------------+----------------+-----------------+-----------------+
|           2 | Learn MySQL    | Abdul S         | 2007-05-24      |
|           1 | Learn PHP      | John Poul       | 2007-05-24      |
|           3 | JAVA Tutorial  | Sanjay          | 2007-05-06      |
+-------------+----------------+-----------------+-----------------+
3 rows in set (0.42 sec)
 
mysql>

读取 tutorials_tbl 表中所有数据并按 tutorial_author 字段的（ ASC）升序排列；降序排序用（DESC）。

在PHP脚本中使用 ORDER BY 子句

你可以使用PHP函数的mysql_query()及相同的SQL SELECT 带上 ORDER BY 子句的命令来获取数据。该函数用于执行SQL命令，然后通过 PHP 函数 mysql_fetch_array() 来输出所有查询的数据。
实例
尝试以下实例，查询后的数据按 tutorial_author 字段的降序排列后返回。

<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}
$sql = 'SELECT tutorial_id, tutorial_title, 
               tutorial_author, submission_date
        FROM tutorials_tbl
        ORDER BY  tutorial_author DESC';
 
mysql_select_db('TUTORIALS');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_array($retval, MYSQL_ASSOC))
{
    echo "Tutorial ID :{$row['tutorial_id']}  <br> ".
         "Title: {$row['tutorial_title']} <br> ".
         "Author: {$row['tutorial_author']} <br> ".
         "Submission Date : {$row['submission_date']} <br> ".
         "--------------------------------<br>";
} 
echo "Fetched data successfully\n";
mysql_close($conn);
?>

Mysql Join的使用

在前几章节中，我们已经学会了如果在一张表中读取数据，这是相对简单的，但是在真正的应用中经常需要从多个数据表中读取数据。
本章节我们将向大家介绍如何使用MySQL 的 JOIN 在两个或多个表中查询数据。
你可以在SELECT, UPDATE 和 DELETE 语句中使用Mysql 的 join 来联合多表查询。
以下我们将演示MySQL LEFT JOIN 和 JOIN 的使用的不同之处。

在命令提示符中使用JOIN

我们在TUTORIALS数据库中有两张表 tcount_tbl 和 tutorials_tbl。两张数据表数据如下：
实例
尝试以下实例：

root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> SELECT * FROM tcount_tbl;
+-----------------+----------------+
| tutorial_author | tutorial_count |
+-----------------+----------------+
| mahran          |             20 |
| mahnaz          |           NULL |
| Jen             |           NULL |
| Gill            |             20 |
| John Poul       |              1 |
| Sanjay          |              1 |
+-----------------+----------------+
6 rows in set (0.01 sec)
mysql> SELECT * from tutorials_tbl;
+-------------+----------------+-----------------+-----------------+
| tutorial_id | tutorial_title | tutorial_author | submission_date |
+-------------+----------------+-----------------+-----------------+
|           1 | Learn PHP      | John Poul       | 2007-05-24      |
|           2 | Learn MySQL    | Abdul S         | 2007-05-24      |
|           3 | JAVA Tutorial  | Sanjay          | 2007-05-06      |
+-------------+----------------+-----------------+-----------------+
3 rows in set (0.00 sec)
mysql>

接下来我们就使用MySQL的JOIN来连接以上两张表来读取tutorials_tbl表中所有tutorial_author字段在tcount_tbl表对应的tutorial_count字段值：

mysql> SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count
    -> FROM tutorials_tbl a, tcount_tbl b
    -> WHERE a.tutorial_author = b.tutorial_author;
+-------------+-----------------+----------------+
| tutorial_id | tutorial_author | tutorial_count |
+-------------+-----------------+----------------+
|           1 | John Poul       |              1 |
|           3 | Sanjay          |              1 |
+-------------+-----------------+----------------+
2 rows in set (0.01 sec)
mysql>

在PHP脚本中使用JOIN

PHP 中使用mysql_query()函数来执行SQL语句，你可以使用以上的相同的SQL语句作为mysql_query()函数的参数。
尝试如下实例:

<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}
$sql = 'SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count
        FROM tutorials_tbl a, tcount_tbl b
        WHERE a.tutorial_author = b.tutorial_author';
 
mysql_select_db('TUTORIALS');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_array($retval, MYSQL_ASSOC))
{
    echo "Author:{$row['tutorial_author']}  <br> ".
         "Count: {$row['tutorial_count']} <br> ".
         "Tutorial ID: {$row['tutorial_id']} <br> ".
         "--------------------------------<br>";
} 
echo "Fetched data successfully\n";
mysql_close($conn);
?>

MySQL LEFT JOIN

MySQL left join 与 join 有所不同。 MySQL LEFT JOIN 会读取左边数据表的全部数据，即便右边表无对应数据。
实例
尝试以下实例，理解MySQL LEFT JOIN的应用：

root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count
    -> FROM tutorials_tbl a LEFT JOIN tcount_tbl b
    -> ON a.tutorial_author = b.tutorial_author;
+-------------+-----------------+----------------+
| tutorial_id | tutorial_author | tutorial_count |
+-------------+-----------------+----------------+
|           1 | John Poul       |              1 |
|           2 | Abdul S         |           NULL |
|           3 | Sanjay          |              1 |
+-------------+-----------------+----------------+
3 rows in set (0.02 sec)

以上实例中使用了LEFT JOIN，该语句会读取左边的数据表tutorials_tbl的所有选取的字段数据，即便在右侧表tcount_tbl中没有对应的tutorial_author字段值。