MySQL Crash Course #04# Chapter 7. 8 AND. OR. IN. NOT. LIKE

索引

• AND. OR 运算顺序
• IN Operator VS. OR
• NOT 在 MySQL 中的表现
• LIKE 之注意事项
• 运用通配符的技巧

Understanding Order of Evaluation

与大多数编程语言一样， AND 比 OR 有更高的优先级。

Using Parentheses in WHERE Clauses Whenever you write WHERE clauses that use both AND and OR operators, use parentheses to explicitly group operators. Don't ever rely on the default evaluation order, even if it is exactly what you want. There is no downside to using parentheses, and you are always better off eliminating any ambiguity.

 

Using the IN Operator

MySQL 5.7 Reference Manual  /  Functions and Operators  /  Operators

mysql> SELECT manga_id, manga_name
    -> FROM manga
    -> WHERE manga_id IN (1005,1007);
+----------+------------+
| manga_id | manga_name |
+----------+------------+
|     1005 | 2asdasds   |
|     1007 | 4444444444 |
+----------+------------+
2 rows in set (0.00 sec)

/ 等效代码 ↓

mysql> SELECT manga_id, manga_name
    -> FROM manga
    -> WHERE manga_id=1005 OR manga_id=1007;
+----------+------------+
| manga_id | manga_name |
+----------+------------+
|     1005 | 2asdasds   |
|     1007 | 4444444444 |
+----------+------------+
2 rows in set (0.00 sec)

那么为什么 还需要 IN 呢？ 或者说 IN 操作符有什么好处呢？有如下几个理由：

1. 显然，IN 比 OR 更短更干净
2. 用 IN 就不用考虑运算顺序了
3. 执行 IN 操作符几乎总是要快过执行一连串的 OR ... 
4. 最大的好处是 IN 操作符里可以包含另一个 SELECT 语句（嵌套查询.）

Using the NOT Operator

So why use NOT? Well, for simple WHERE clauses, there really is no advantage to using NOT. NOT is useful in more complex clauses. For example, using NOT in conjunction with an IN operator makes it simple to find all rows that do not match a list of criteria.

MySQL supports the use of NOT to negate IN, BETWEEN, and EXISTS clauses. This is quite different from most other DBMSs that allow NOT to be used to negate any conditions.

 

Using the LIKE Operator

mysql> SELECT manga_id, manga_name
    -> FROM manga
    -> WHERE manga_id LIKE '%100%';
+----------+-----------------------+
| manga_id | manga_name            |
+----------+-----------------------+
|     1000 | 至不死的你            |
|     1001 | 烙印勇士              |
|     1002 | 幸福（happiness）     |

1. 通配符 % 可以配 空 （也就是不配字符）

2. Watch for Trailing Spaces Trailing spaces can interfere with wildcard matching. For example, if any of the anvils had been saved with one or more spaces after the word anvil, the clause WHERE prod_name LIKE '%anvil' would not have matched them as there would have been additional characters after the final l. One simple solution to this problem is to always append a final % to the search pattern. A better solution is to trim the spaces using functions, as is discussed in Chapter 11, "Using Data Manipulation Functions."

3. '%' 可以通配除了 NULL 外的所有值。

Tips for Using Wildcards

As you can see, MySQL's wildcards are extremely powerful. But that power comes with a price: Wildcard searches typically take far longer to process than any other search types discussed previously. Here are some tips to keep in mind when using wildcards:

• Don't overuse wildcards. If another search operator will do, use it instead.

• When you do use wildcards, try to not use them at the beginning of the search pattern unless absolutely necessary. Search patterns that begin with wildcards are the slowest to process.

• Pay careful attention to the placement of the wildcard symbols. If they are misplaced, you might not return the data you intended.

Having said that, wildcards are an important and useful search tool, and one that you will use frequently.