LIKE, IN and BETWEEN
Databases & SQL Lesson 7 2:04 English narration · English + 中文 subtitles burned in
Chapters
Transcript
Sometimes you do not know the exact value — you know the shape of it.
有时候你并不知道确切的值——你知道的是它的形状。
LIKE compares against a pattern.
LIKE 拿它跟一个模式去比。
Here are our five students, three times over.
这里是我们那五个学生，重复了三遍。
Anything that starts with an S: only Sara.
任何以 S 开头的：只有 Sara。
Anything that has an a anywhere in it: Jamal and Sara.
任何里面某处有一个 a 的：Jamal 和 Sara。
Anything with an o as its second letter: Tom alone.
任何第二个字母是 o 的：只有 Tom。
Two wildcards do that work, and they are easy to blur.
有两个通配符在做这件事，而它们很容易混。
A percent sign stands for any run of characters — any length, and importantly including none at all.
百分号代表任意一段字符——任意长度， 而且很重要的是，包括一个字符都没有。
An underscore stands for one character exactly: never zero, never two.
下划线代表恰好一个字符：不能是零个，也不能是两个。
So A percent matches Ada and even a bare A, while A underscore matches Al but not Ada.
所以 'A%' 能匹配 Ada，甚至能匹配光秃秃的 A， 而 'A_' 能匹配 Al，但匹配不了 Ada。
IN handles the other common case: one column against a list of allowed values.
IN 处理另一种常见情况：一列对上一串允许的取值。
Written out with ORs it is three lines and repeats the column name three times.
用 OR 写出来是三行，而且把列名重复了三遍。
With IN it is one line instead.
用 IN 就只有一行。
The results are identical — IN is only shorter — but it is what an exam answer is expected to use, and it is far harder to mistype.
结果完全一样——IN 只是更短—— 但它正是考卷答案期待你用的写法，而且不容易打错。
BETWEEN handles a range, and there is one thing to be certain about: both ends are included.
BETWEEN 处理一个区间，而有一件事必须确定：两端都包含在内。
Score between seventy and ninety means seventy itself counts, and so does ninety.
score BETWEEN 70 AND 90 表示 70 本身算数，90 也算数。
It is exactly the same as writing score at least seventy and score at most ninety — which is worth remembering, because that is how an exam will phrase the same question.
它和写 score >= 70 AND score <= 90 完全一样—— 这一点值得记住，因为考卷就是用后一种说法来问同一个问题的。
Four things to take with you.
带走四点。
One: in LIKE, a percent matches any run of characters.
第一：在 LIKE 里，百分号匹配任意一段字符。
Two: an underscore matches exactly one.
第二：下划线恰好匹配一个字符。
Three: IN matches any value in a list.
第三：IN 匹配列表里的任意一个值。
Four: BETWEEN matches a range, including both ends.
第四：BETWEEN 匹配一个区间，两端都包含。
Now run the tasks below.
现在去做下面的题。