小白学习MySQL Day7-8 20240821-0822

MySQL运算符与WHERE子句详解

原创已于 2024-12-17 13:37:37 修改 · 874 阅读

9 ·

CC 4.0 BY-SA版权

文章标签：

#学习 #mysql #数据库

于 2024-08-22 17:47:18 首次发布

一、运算符

1、比较运算符

=, >, <, >=, <=, <> 或 !=

2、逻辑运算符

AND, OR, NOT

3、BETWEEN运算符

用于选取介于两个值之间的数据范围。

SELECT column_name FROM table_name

WHERE column_name BETWEEN value1 AND value2;

4、IN运算符

用于指定列的多个可能值。

Using IN, we can see if a given value is in a set of values.

Using NOT IN, we can check if a given value is not in the set.

The set can be given explicitly or can be produced in a subquery.

(1)

SELECT column_name FROM table_name

WHERE column_name IN (value1, value2, ...);

(2)

SELECT column_name FROM table_name

WHERE column_name IN (SELECT column_only FROM table_name);

在 IN 和 NOT IN 子查询中，必须明确指定要比较的列。IN 和 NOT IN 后接的子查询不能返回多个（不唯一）的列。Should not return multiple column.

Incorrect subquery, 报错:

SELECT * FROM Company WHERE cID NOT IN (SELECT * FROM Product);

Product表中列数多于1，IN 和 NOT IN只能比较唯一一个列。

IN和BETWEEN的区别

1.应用条件不同：

IN运算符用于指定一个值列表，查询列中匹配列表中任一值的记录。

BETWEEN运算符用于指定一个范围，查询列中值位于该范围内的记录。

2.灵活性不同

IN运算符更加灵活，可以指定多个不连续的值。

BETWEEN运算符只能指定一个连续的范围。

3.性能影响

在大多数情况下，IN操作比BETWEEN操作更快。

IN只需要在指定的值列表中进行简单的匹配，

BETWEEN操作则需要对每个记录进行范围检查。

不过，这一观点可能因数据库的具体实现和查询的具体情况而异。

5、LIKE运算符

用于字符串数据的模糊匹配。String Comparisons in queries.

在文本数据类型（如CHAR、VARCHAR和TEXT）中最为常见。

通过指定的模式 LIKE pattern 和 NOT LIKE pattern 来搜索列中的字符串值。

这个模式包含通配符Wildcard 以实现模糊匹配。

It is NOT normally case sensitive. 通常不区分大小写。

最常见的通配符有两个：

% 百分号：代表任意数量的字符（包括零个字符）。 can represent any number of characters
_ 下划线：代表任意单个字符。 represents exactly one character

%不限制字符个数，所以字符串长度随意；

_限制字符个数，仅仅代表单个字符，所以字符串长度受限。

k% 匹配以字符 k 开头的， 任意长度的字符串。
%k 匹配以字符 k 结尾的， 任意长度的字符串。
%k% 匹配包含字符 k 的，任意长度的字符串。
%m%n% 匹配同时包含字符 m 和 n 且 m 在 n 前面的，任意长度的字符串。
k_ 匹配以字符 k 开头，长度为 2 字符串。
_k 匹配以字符 k 结尾，长度为 2 字符串。

E.g. 从"persons"表中选取满足：

(1) 以"Nan"开始的 city列的行：

SELECT * FROM persons

WHERE city LIKE 'Nan%';

(2) 以"ing"结尾的 city列的行：

SELECT * FROM persons

WHERE city LIKE '%ing';

(3) name列的第一个字符之后是'eorge' 的行:

SELECT * FROM persons

WHERE name LIKE '_eorge';

(4) name列不包含 "lin" 的的行:

SELECT * FROM persons
WHERE name NOT LIKE '%lin%';

• bookName LIKE 'crypt%'

will return “Cryptography Engineering” and “Cryptonomicon”

NOT “Applied Cryptography”

因为必须是crypt开头，但大小写不敏感。

• bookName LIKE 'cloud_'

will return “Clouds”

NOT “Cloud” or “cloud computing”

因为字符串长度不符。

二、WHERE 子句

1、功能

规定选择的标准。确保只有满足特定条件的行被选中或操作。

基本执行流程 The evaluation process of select:

• Get the table (the FROM part)

• For each tuple, assess the WHERE clause.

• True (or non-zero values) -> accepted

• False (or zero)-> filtered

• Get columns (the SELECT part)

2、表达式 WHERE predicate

A WHERE clause restricts rows that are returned.

• It takes the form of a Predicate.

Predicate 谓词:

can be understood as an expression that either returns a true or false

(for numbers, non-zero or zero).

是一个用于评估条件并返回布尔值（true或false）的表达式。

可包含：运算符，IS NULL或IS NOT NULL，正则表达式，子查询，函数

• Only rows that satisfy the condition will appear in the final result.

predicate 可以是任何有效的条件表达式，比如：

(1) 数值类型不需加单引号

WHERE column2 > 10

(2) 字符串类型需加单引号

WHERE column1 = 'value'

WHERE (column1=‘value1') AND (column2>'value2')

WHERE (column1='value' OR column2= 'value2')

注： WHERE (...) AND (...) 两个分开的括号

WHERE (...OR...) 一个合起来的括号

(3) 是否为空

WHERE column1 IS NULL

WHERE column2 IS NOT NULL

(4) 日期与时间类型

• The comparison of date and time can be done just like numbers.

• But you can also search for dates like a string:

1.当数据类型为DATE时：

SELECT * FROM table

WHERE date-of-event < '2012-01-01';

SELECT * FROM table

WHERE date-of-event LIKE '2014-11-%';

2.当数据类型为DATETIME或DATESTAMP时：

SELECT * FROM table

WHERE date-of-event < '2012-01-01 14:00:00';

SELECT * FROM table

WHERE DATE(date-of-event) = '2023-01-01';

3.跨时间范围查询：查询某个时间段内的数据
SELECT * FROM table
WHERE date-of-event BETWEEN '2023-01-01' AND '2023-01-10';

(5) 多条件查询

Search for a set of words. 在单个查询中指定多个条件。

• To find entries with all words you can link conditions with AND.

查询将返回所有同时满足两个条件（两个条件的交集）的记录。

SELECT * FROM books WHERE

bookName LIKE '%crypt%'

AND bookName LIKE '%cloud%';

• To find entries with any words use OR.

查询将返回所有满足至少一个条件（两个条件的并集）的记录。

SELECT * FROM books WHERE

bookName LIKE '%crypt%'

OR bookName LIKE '%cloud%';

3、用法

WHERE子句与SELECT、UPDATE、DELETE等语句结合使用，限制这些操作所影响的行数。

（1）从表中选取数据

SELECT column1, column2
FROM table_name
WHERE predicate;

注：需要使用单引号环绕文本值。如果是数值，不要使用引号。因此，需要先判断数据类型。

（2）修改或添加表中的数据

UPDATE table_name

SET column1 = value1, column2 = value2, ...

WHERE predicate;

（3）从表中删除数据

DELETE FROM table_name
WHERE predicate;

4、实例

三、布尔值 Boolean value

是一个表示真（true）或假（false）的值。

在SQL查询中，条件表达式的结果通常被隐式地视为布尔值：

如果条件为真，则结果被视为true。

When true is evaluated as a number, it will be 1.

如果条件为假，则结果被视为false（即，该行被WHERE子句排除）。

When false is evaluated as a number, it will be 0.

E.g. What are the results of the following expressions and their

corresponding Boolean value?

1 + 12 > 30
- Calculation: 1+12=13
- Comparison: 13>30 is False
- Boolean value: False
(True AND False) OR False
- Evaluation: (True AND False) is False because AND requires both operands to be True.
- Then: False OR False is False
- Boolean value: False
(False OR 12) AND True
- In most programming languages and SQL, non-zero numbers are considered True in a Boolean context.
- Evaluation: (False OR 12) is True because 12 is considered True.
- Then: True AND True is True
- Boolean value: True
(NOT True) AND (NOT False)
- Evaluation: NOT True is False
- Then: NOT False is True
- Finally: False AND True is False
- Boolean value: False
7 + 12
- Calculation: 7+12=19
- This is a numeric result, not a Boolean value. But in a Boolean context, non-zero numbers are often considered True.
- Boolean interpretation (if needed): True (but note this is not the direct result)
True + 1
- This depends on the programming language or context. In many cases, trying to add a Boolean to a number is not allowed or results in an error.
- If we interpret True as 1 and perform the addition, the result is 2.
- But again, this is a numeric result, not a Boolean value.
- Boolean interpretation (if forced): Not directly applicable, but 2 in a Boolean context could be considered True.
False + 2
- Similar to the previous case, if we interpret False as 0 and perform the addition, the result is 2.
- Numeric result: 2
- Boolean interpretation (if forced): True
39 <> False
- The <> operator is the "not equal to" operator.
- In a Boolean context, False is often considered 0.
- Comparison: 39!=0 is True
- Boolean value: True
27 AND False
- Interpret 27 as True in a Boolean context, the expression becomes True AND False.
- Evaluation: True AND False is False
- Boolean value: False
0 OR TRUE
- Evaluation: 0 in a Boolean context is considered False.
- Then: False OR True is True
- Boolean value: True