Mysql 简明教程
MySQL - IN Operator
MySQL In Operator
MySQL 中的 IN 运算符是一个逻辑运算符，它允许我们检查数据库中的值是否存在于 SQL 语句中指定的值列表中。
The IN operator in MySQL is a logical operator that allows us to check whether the values in a database are present in a list of values specified in the SQL statement.
IN 运算符可用于 SQL 中的任何数据类型。它用于根据指定的值从数据库表中筛选数据。它返回指定列值与列表中的任何一个值匹配的所有行。
The IN operator can be used with any data type in SQL. It is used to filter data from a database table based on specified values. It returns all rows in which the specified column value matches any one of the values in the list.
在某些情况下，我们可能会使用多个 OR 语句在 SELECT、DELETE、UPDATE 或 INSERT 语句中包含多个条件。您可以使用 IN 子句替换许多 OR 条件
In some scenarios we may use multiple OR statements to include multiple conditions in SELECT, DELETE, UPDATE, or INSERT statements. You can use IN clause to replace many OR conditions
Syntax
以下是 IN 运算符的基本语法 −
Following is the basic syntax of IN operator −
WHERE COLUMN_NAME IN (value1, value2, value3,....);Example
要理解 IN 子句，让我们首先使用以下 CREATE TABLE 语句创建一个名为 CUSTOMERS 的表 −
To understand IN clause, let us first create a table named CUSTOMERS, using the following CREATE TABLE statement −
CREATE TABLE CUSTOMERS (
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25),
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);现在，使用 INSERT 语句插入以下记录 −
Now, insert the following records using the INSERT statement −
INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00),
(2, 'Khilan', 25, 'Delhi', 1500.00),
(3, 'Kaushik', 23, 'Kota', 2000.00),
(4, 'Chaitali', 25, 'Mumbai', 6500.00),
(5, 'Hardik', 27, 'Bhopal', 8500.00),
(6, 'Komal', 22, 'Hyderabad', 4500.00),
(7, 'Muffy', 24, 'Indore', 10000.00);该表将按如下方式创建：
The table will be created as follows −
使用以下查询，从 CUSTOMERS 表中检索名称为“Khilan”、“Hardik”、“Muffy”的记录 −
Using the following query, retrieve the records with the names ‘Khilan’, ‘Hardik’, ‘Muffy’, from the CUSTOMERS table −
SELECT * FROM CUSTOMERS
WHERE NAME IN ('Khilan', 'Hardik', 'Muffy');The IN Operator in UPDATE statement
MySQL UPDATE 语句用于修改数据库表中的现有数据。因此，我们还可以在 UPDATE 语句中使用 IN 运算符（作为筛选器）来更新现有行。
The MySQL UPDATE statement is used to modify existing data in a database table. So, we can also use the IN operator in an UPDATE statement (as a filter) to update existing rows.
MySQL NOT IN operator
为了否定一个条件，我们使用 NOT 运算符。MySQL IN 运算符可以与 NOT 运算符结合使用，以在 WHERE 子句中排除特定值。
To negate a condition, we use the NOT operator. The MySQL IN operator can be used in combination with the NOT operator to exclude specific values in a WHERE clause.
换句话说，将检查表达式中是否缺少列表。
In other words, the absence of a list from an expression will be checked.
Syntax
接下来是 NOT IN 运算符的基本语法：
Following is the basic syntax of NOT IN operator −
WHERE column_name NOT IN (value1, value2,...);Comparing Values Using IN Operator
我们可以使用 IN 运算符连同列名来将某个列的值与另一个列的值进行比较。此运算符用于选择特定值存在于给定列的那些行。
We can also use the IN operator with a column name to compare the values of one column to another. It is used to select the rows in which a specific value exists for the given column.
MySQL Subquery with IN operator
我们可以使用子查询和 IN 运算符来返回来自单列的记录。这意味着 SELECT 列列表中的不止一列不能包含在指定子查询中。
We can use a subquery with the IN operator to return records from a single column. This means that more than one column in the SELECT column list cannot be included in the subquery specified.
Syntax
IN 运算符的基本语法如下 −
The basic syntax of the IN operator to specify a query is as follows −
WHERE column_name IN (subquery);Example
在下面给出的查询中，我们显示了 CUSTOMERS 表中所有记录，其中的客户 NAME 是通过 SALARY 大于 2000 获得的 −
In the query given below we are displaying all the records from the CUSTOMERS table where the NAME of the customer is obtained with SALARY greater than 2000 −
SELECT * FROM CUSTOMERS
WHERE NAME IN (
SELECT NAME FROM CUSTOMERS
WHERE SALARY > 2000
);In Operator Using Client Program
除了直接在 MySQL 服务器中执行 IN 运算符，我们还可以使用客户端程序执行此操作。
We can execute IN operator using a client program, in addition to executing it directly in the MySQL server.