MySQL基础（2）——检索、排序与过滤

电玩女神 2022-06-13 07:23 136阅读 0赞

本篇主要整理检索数据、排序数据以及过滤数据相关的SQL语句。

### **一、检索数据** ###

关键字：SELECT语句

1.  功能：从一个表中检索特定某列或某几列  
    输入：SELECT 列名(，列名2，列名3…列名n)  
       FROM 表名；  
       / SELECT 表名.列名  
         FROM （数据库名.）表名（完全限定格式）
    
    > SQL语句本身不区分大小写，但有些标识符（如数据库名、表名、列名）可能不同，所以按照大小写的惯例，且使用时保持一致
    > 
    > 处理SQL语句时，其中所有空格都被忽略。但将SQL语句分成多行更容易阅读和调试；多条SQL语句必须以分号(；)  
    > 分隔。
2.  功能：从一个表中检索所有列  
    输入：SELECT \* FROM 表名；
    
    > 列的顺序一般是列在表定义中出现的顺序。但表的模式的变化（如添加或删除列）可能会导致顺序的变化。
3.  功能：只返回不同（唯一）的行  
    输入：SELECT DISTINCT 列名(，列名2，列名3，…列名n）  
       FROM 表名;
    
    > DISTINCT关键字应用于所有列
4.  功能：返回指定开始行和行数的检索结果  
    输入：SELECT 列名  
       FROM 表名  
       LIMIT （开始行数,）行数;  
        /  SELECT 列名  
         FROM 表名  
         LIMIT 开始行数 OFFSET 行数（MySQL 5开始支持）
    
    > LIMIT 5,5指示MySQL返回从行5开始的5行。即第一个数为开始位置，第二个数为要检索的函数。若仅有一个参数，则从第一行开始，给出的数为返回的行数。
    > 
    > 检索出的第一行为行0而不是行1。LIMIT 1,1检索为第二行而不是第一行

### **二、排序数据** ###

**子句**：SQL语句由子句构成，有些子句是必须的，而有的是可选的。一个子句通常由一个关键字和所提供的数据组成。

排序一般使用ORDER BY子句。ORDER BY 子句取一个或多个列的名字，据此对输出排序。

1.  功能：按字母顺序对检索的一个（或一组）特定列的数据排序输出  
    输入：SELECT prod\_name(列名）  
       FROM 表名  
       ORDER BY prod\_name(，prod\_price）;
    
    > 可以使用非检索的列排序数据，即ORDER BY子句中使用的列可以为非检索的列
    > 
    > 按多个列排序时，排序完全按所规定的顺序进行。
2.  功能： 指定排序方向  
    输入：SELECT prod\_id,prod\_price,prod\_name(列名）  
       FROM 表名  
       ORDER BY prod\_price DESC/ASC， prod\_name;
    
    > DESC、ASC关键字只应用到直接位于其前面的列名，一般默认为升序，所以ASC作用不大
    > 
    > 如果想在多个列上进行降序排序，必须对每个列指定DESC关键字
3.  功能：找出最高/最低值  
    输入：SELECT prod\_price  
       FROM 表名  
       ORDER BY prod\_price DESC/ASC  
       LIMIT 1；
    
    > ORDER BY子句必须位于FROM子句之后，LIMIT子句必须位于ORDER BY子句之后，使用子句的次序不对将产生错误消息

### **三、过滤数据** ###

关键字：WHERE子句、通配符、正则表达式

1.  功能：从指定表中检索指定列后，返回特定条件的行  
    输入：SELECT prod\_name,prod\_price（列名）  
         FROM 表名  
         WHERE prod\_price = 2.50;（搜索条件）
    
    > 数据也可以在应用层过滤，但让客户机应用（或开发语言）处理数据库的工作将会极大地影响应用的性能，并且使所创建的应用完全不具备可伸缩性。此外，如果在客户机上过滤数据，服务器不得不通过网络发送多余的数据，这将导致网络带宽的浪费
    > 
    > 若同时使用WHERE子句和ORDER BY子句时，ORDER BY 应位于WHERE之后
    > 
    > **where子句操作符**
    > 
    > <table> 
    >  <thead> 
    >   <tr> 
    >    <th>操作符</th> 
    >    <th align="center">说明</th> 
    >   </tr> 
    >  </thead> 
    >  <tbody> 
    >   <tr> 
    >    <td>=</td> 
    >    <td align="center">等于</td> 
    >   </tr> 
    >   <tr> 
    >    <td>&lt;&gt; / !=</td> 
    >    <td align="center">不等于</td> 
    >   </tr> 
    >   <tr> 
    >    <td>&lt;</td> 
    >    <td align="center">小于</td> 
    >   </tr> 
    >   <tr> 
    >    <td>&lt;=</td> 
    >    <td align="center">小于等于</td> 
    >   </tr> 
    >   <tr> 
    >    <td>&gt;</td> 
    >    <td align="center">大于</td> 
    >   </tr> 
    >   <tr> 
    >    <td>BETWEEN AND</td> 
    >    <td align="center">在指定的两个值之间（包括指定的开始值和结束值）</td> 
    >   </tr> 
    >  </tbody> 
    > </table>
2.  功能：空值检查  
    输入：SELECT prod\_name  
          FROM 表名  
          WHERE cust\_email IS NULL;
    
    > 在通过过滤选择出不具有特定值的行时，不会返回具有NULL值的行。因为未知具有特殊的含义，数据库不知道它们是否匹配，所以在匹配过滤或不匹配过滤时不返回它们。  
    > 因此，在过滤数据时，一定要验证返回数据中确实给出了被过滤列具有NULL的行
3.  功能：多个过滤条件检索的结果  
    输入：SELECT prod\_id,prod\_price,prod\_name  
         FROM 表名  
         WHERE vend\_id = 1003 AND prod\_price <= 10;
    
    > **组合WHERE子句操作符**
    > 
    > <table> 
    >  <tbody> 
    >   <tr> 
    >    <th>操作符</th> 
    >    <th>说明</th> 
    >    <th>优先级</th> 
    >   </tr> 
    >   <tr> 
    >    <td>AND</td> 
    >    <td>用来指示检索满足所有给定条件的行</td> 
    >    <td>高</td> 
    >   </tr> 
    >   <tr> 
    >    <td>OR</td> 
    >    <td>用来指示检索匹配任一条件的行</td> 
    >    <td>低</td> 
    >   </tr> 
    >   <tr> 
    >    <td>IN</td> 
    >    <td>用来指定条件范围，范围中的每个条件都可以进行匹配，IN取合法值的由逗号分隔的清单，**全都括在圆括号中**，作用同OR </td> 
    >    <td></td> 
    >   </tr> 
    >   <tr> 
    >    <td>NOT</td> 
    >    <td>否定其之后所跟的任何条件（MySQL支持其对IN、BETWEEN和EXISTS子句取反）</td> 
    >    <td></td> 
    >   </tr> 
    >   <tr> 
    >    <td>LIKE</td> 
    >    <td>指示后跟的搜索模式利用通配符匹配而不是直接相等匹配进行比较。<br>最常使用的通配符是百分号（%）。在搜索串中，%表示任何字符出现任意次数。<br>使用示例：SELECT * FROM book WHERE NAME LIKE ‘Java%’ </td> 
    >    <td></td> 
    >   </tr> 
    >  </tbody> 
    > </table>
    > 
    > **使用IN操作的优点：**  
    >  1、 在使用长的合法选项清单时，IN操作符的语法更清楚且更直观。  
    >  2、 在使用IN时，计算的次序更容易管理（因为使用的操作符更少）。  
    >  3、 IN操作符一般比OR操作符清单执行更快。  
    >  4、 IN的最大优点是***可以包含其他SELECT语句***，使得能够更动态地建立WHERE子句。
    
    **通配符**：用来匹配值的一部分的特殊字符。*其可在搜索模式中任意位置使用，并且可以使用多个通配符。*  
    **搜索模式**：由字面值、通配符或两者组合构成的搜索条件。
4.  功能：匹配任何位置包含文本anvil的值  
    输入：SELECT prod\_id,prod\_name  
       FORM 表名  
       WHERE prod\_name LIKE ‘%anvil%’
    
    > **搜索模式’%anvil%’**表示匹配任何位置包含文本anvil的值，而 不论它之前或之后出现什么字符。
5.  功能：匹配以s起头以e结尾的所有产品  
    输入：SELECT prod\_name  
       FORM 表名  
       WHERE prod\_name LIKE ‘s%e’
    
    > 通配符可以出现再搜索模式的中间
    > 
    > 尾空格可能会干扰通配符匹配。例如，在保存词anvil时，如果它后面有一个或多个空格，则子句WHERE prod\_name LIKE ‘%anvil’将不会匹配它们。解决这个问题的一个简单的办法是*在搜索模式最后附加一个%*。一个更好的办法是使用函数去掉首尾空格。
    > 
    > 除了一个或多个字符外，%还能匹配0个字符。%代表搜索模式中给定位置的0个、1个或多个字符。**但%不能匹配NULL**  
    > **下划线（\_）**通配符与%用途一样，但是**能且只能**匹配一个字符。
    > 
    > 在确实需要使用通配符时，除非绝对有必要，否则**不要把它们用在搜索模式的开始处**。把通配符置于搜索模式的开始处，搜索起来是最慢的。

--------------------

##      复杂过滤条件 ##

**正则表达式**：用来匹配文本的特殊的串（字符集合）  
      应用场景：从一个文本中提取电话号码、查找名字中间有数字的所有文件、再一个文本块中找到所有重复的单词、替换一个页面中的所有URL为这些URL的实际HTML链接等

> MySQL仅支持多数正则表达式实现的一个很小的子集

1.  功能：检索列prod\_name包含文本1000的所有行  
    输入：SELECT prod\_name  
       FROM 表名  
       WHERE prod\_name REGEXP ‘1000’  
       ORDER BY prod\_name;  
    输出：| prod\_name |  
       | JetPack 1000 |
    
    > 用LIKE可以实现相同功能  
    > 输入：SELECT prod\_name  
    >    FROM 表名  
    >     WHERE prod\_name LIKE ‘%1000’  
    >    ORDER BY prod\_name;
2.  功能：检索列prod\_name包含文本‘000’的所有行  
    输入：SELECT prod\_name  
       FROM 表名  
       WHERE prod\_name REGEXP ‘.000’  
       ORDER BY prod\_name;
    
    > .是正则表达式语言中一个特殊的字符。它表示匹配任意一个字符，因此，1000和2000都匹配且返回。
    > 
    > 现在版本的MySQL中的正则表达式匹配不区分大小写（即，大写和小写都匹配）。为区分大小写，可使用**BINARY**关键字，如WHERE prod\_name REGEXP BINARY ‘JetPack .000’。
3.  功能：OR匹配  
    输入：SELECT prod\_name  
       FROM 表名  
       WHERE prod\_name REGEXP ‘1000|2000’  
       ORDER BY prod\_name;
4.  功能：匹配特定字符  
    输入：SELECT prod\_name  
       FROM 表名  
       WHERE prod\_name REGEXP ‘\[123\] Ton’  
       ORDER BY prod\_name;  
    输出：| prod\_name |  
       | 1 ton anvil |  
       | 2 ton anvil |
    
    > **\[123\]**定义一组字符，为**\[1|2|3\]**的缩写，**作用同OR**，它的意思是匹配1或2或3；也可以用**\[1-3\]**这种方式来表达
    > 
    > **字符集合否定用^**，它将匹配除指定字符外的任何东西。 如\[^123\]匹配除这些字符外的任何东西。
5.  功能：匹配特殊字符  
    输入：SELECT prod\_name  
       FROM 表名  
       WHERE prod\_name REGEXP ‘\\.’  
       ORDER BY prod\_name;  
    输出：| prod\_name |  
       | tommy Inc. |
    
    > 转义字符\\与C、C++用法一样，也可用来引用元字符（如\\\\r回车、\\\\f换页等）
    > 
    > 多数正则表达式实现使用*单个*反斜杠转义特殊字符， 以便能使用这些字符本身。但MySQL要求*两个*反斜杠（MySQL 自己解释一个，正则表达式库解释另一个）。

--------------------

为方便工作，可以使用预定义的字符集，称为字符类 （character class）。下表列出字符类以及它们的含义。

<table> 
 <thead> 
  <tr> 
   <th><font>类</font></th> 
   <th align="center"><font>说明</font></th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><font>[:alnum:]</font></td> 
   <td align="center"><font>任意数字和字母（同[a-zA-Z0-9]</font></td> 
  </tr> 
  <tr> 
   <td><font>[:alpha:]</font></td> 
   <td align="center"><font>任意字符（同[a-zA-Z])</font></td> 
  </tr> 
  <tr> 
   <td><font>[:blank:]</font></td> 
   <td align="center"><font>空格和制表(同[\\t])</font></td> 
  </tr> 
  <tr> 
   <td><font>[:cntrl:]</font></td> 
   <td align="center"><font>ASCII控制字符（ASCII0到31和127）</font></td> 
  </tr> 
  <tr> 
   <td><font>[:digit:]</font></td> 
   <td align="center"><font>任意数字（同[0-9]）</font></td> 
  </tr> 
  <tr> 
   <td><font>[:graph:]</font></td> 
   <td align="center"><font>与[:print:]相同，但不包括空格</font></td> 
  </tr> 
  <tr> 
   <td><font>[:lower:]</font></td> 
   <td align="center"><font>任意小写字母（同[a-z]）</font></td> 
  </tr> 
  <tr> 
   <td><font>[:print:]</font></td> 
   <td align="center"><font>任意可打印字符</font></td> 
  </tr> 
  <tr> 
   <td><font>[:punct:]</font></td> 
   <td align="center"><font>任意可打印字符</font></td> 
  </tr> 
  <tr> 
   <td><font>[:space:]</font></td> 
   <td align="center"><font>包括空格在内的任意空白字符（同[\\f\\n\\r\\t\\v]）</font></td> 
  </tr> 
  <tr> 
   <td><font>[:upper:]</font></td> 
   <td align="center"><font>任意大写字母（同[A-Z]）</font></td> 
  </tr> 
  <tr> 
   <td><font>[:xdigit:]</font></td> 
   <td align="center"><font>任意十六进制数字（同[a-fA-F0-9]）</font></td> 
  </tr> 
 </tbody> 
</table>

--------------------

1.  功能：匹配多个实例  
    输入：SELECT prod\_name  
       FROM 表名  
       WHERE prod\_name REGEXP ‘\$\[0-9\] sticks?\$’  
       ORDER BY prod\_name;  
    输出：| prod\_name |  
       | TNT (1 stick) |  
       | TNT (1 sticks) |
    
    > 双斜杠+( 匹配（， \[0-9\]匹配任意数字，sticks?匹配stick 和sticks（s后的?使s可选，因为?匹配它前面的任何字符的0次或1次出现）,双斜杠+)匹配）
    > 
    > **重复元字符：**
    > 
    > <table> 
    >  <thead> 
    >   <tr> 
    >    <th>元字符</th> 
    >    <th align="center">说明</th> 
    >   </tr> 
    >  </thead> 
    >  <tbody> 
    >   <tr> 
    >    <td>*</td> 
    >    <td align="center">0个或多个匹配</td> 
    >   </tr> 
    >   <tr> 
    >    <td>+</td> 
    >    <td align="center">1个或多个匹配（等于{1，}）</td> 
    >   </tr> 
    >   <tr> 
    >    <td>？</td> 
    >    <td align="center">0个或1个匹配（等于{0，1}）</td> 
    >   </tr> 
    >   <tr> 
    >    <td>{n}</td> 
    >    <td align="center">指定数目的匹配</td> 
    >   </tr> 
    >   <tr> 
    >    <td>{n，}</td> 
    >    <td align="center">不少于指定数目的匹配</td> 
    >   </tr> 
    >   <tr> 
    >    <td>{n，m}</td> 
    >    <td align="center">匹配数目的范围（m不超过255）</td> 
    >   </tr> 
    >  </tbody> 
    > </table>
2.  功能：匹配连在一起的4位数字  
    输入：SELECT prod\_name  
       FROM 表名  
       WHERE prod\_name REGEXP ‘\[\[:digit:\]\]\{4\}’  
       ORDER BY prod\_name;  
    输出：| prod\_name |  
       | JetPack 1000 |  
       | JetPack 2000 |
    
    > \[:digit:\]匹配任意数字，因而它为数字的一个集合。\{4\}确切地要求它前面的字符（任意数字）出现4次，所以 \[\[:digit:\]\]\{4\}匹配连在一起的任意4位数字。
    > 
    > 另一表述为：  
    >    SELECT prod\_name  
    >    FROM 表名  
    >    WHERE prod\_name REGEXP ‘\[0-9\]\[0-9\]\[0-9\]\[0-9\]’  
    >    ORDER BY prod\_name;
3.  功能：检索出以小数点开始的所有产品  
    输入：SELECT prod\_name  
       FROM 表名  
        WHERE prod\_name REGEXP ‘^\[0-9\\.\]’  
        ORDER BY prod\_name
    
    > **定位元字符**
    > 
    > <table> 
    >  <thead> 
    >   <tr> 
    >    <th>元字符</th> 
    >    <th align="center">说明</th> 
    >   </tr> 
    >  </thead> 
    >  <tbody> 
    >   <tr> 
    >    <td>^</td> 
    >    <td align="center">文本的开始</td> 
    >   </tr> 
    >   <tr> 
    >    <td>$</td> 
    >    <td align="center">文本的结尾</td> 
    >   </tr> 
    >   <tr> 
    >    <td>[:&lt;:]</td> 
    >    <td align="center">词的开始</td> 
    >   </tr> 
    >   <tr> 
    >    <td>[:&gt;:]</td> 
    >    <td align="center">词的结束</td> 
    >   </tr> 
    >  </tbody> 
    > </table>
    > 
    >   
    > **LIKE与REGEXP的区别：**  
    >   LIKE匹配整个串而REGEXP匹配子串。利用定位符，通过用^开始每个表达式，用$结束每个表达式，可以使REGEXP与LIKE作用一样。