如何在 SQL 中使用 IN
一、SQL IN 语法
在这篇 SQL 文章中，您将学习如何在 SQL 数据库的查询中使用 IN 关键字。
IN 是SQL语言中的逻辑运算符，它允许数据库用户在 WHERE 子句中定义多个值。
带有 IN 运算符的 WHERE 子句显示结果中与给定值集匹配的那些记录。我们还可以在 IN 运算符的括号中指定子查询。
我们可以将 IN 运算符与 SQL 数据库中的 INSERT、SELECT、UPDATE 和 DELETE 查询一起使用。
SQL 中的 IN 运算符替代了查询中多个 OR 条件的过程。
SQL IN 语法：
SELECT Column_Name_1, Column_Name_2, Column_Name_3, ......, Column_Name_N FROM Table_Name WHERE Column_Name IN (Value_1, Value_2, Value_3, ......., Value_N);
二、SQL IN 使用步骤
如果要在 SQL 语句中使用 IN 运算符，则必须按相同顺序执行以下步骤：
- 在 SQL 中创建数据库。
- 创建新的 SQL 表。
- 在表中插入数据。
- 查看插入的数据。
- 使用 SQL IN 运算符显示表的数据。
现在，我们将通过最好的 SQL 示例，对每一步进行简要说明：
第 1 步：创建一个简单的新数据库
第一步是用结构化查询语言创建一个新数据库。
以下 CREATE 语句在 SQL Server 中创建新的Mechanical_College数据库：
CREATE Database Mechanical_College;
第 2 步：创建一个新表
现在，使用以下有助于在数据库中创建新表的 SQL 语法：
CREATE TABLE table_name
(
1st_Column data type (character_size of 1st Column),
2nd_Column data type (character_size of the 2nd column ),
3rd_Column data type (character_size of the 3rd column),
...
Nth_Column data type (character_size of the Nth column)
);
以下 CREATE 语句在Mechanical_College数据库中创建Faculty_Info表：
CREATE TABLE Faculty_Info
(
Faculty_ID INT NOT NULL PRIMARY KEY,
Faculty_First_Name VARCHAR (100),
Faculty_Last_Name VARCHAR (100),
Faculty_Dept_Id INT NOT NULL,
Faculty_Joining_DateDATE,
Faculty_City Varchar (80),
Faculty_Salary INT
);
第 3 步：将数据插入表中
以下 INSERT 查询将 Faculties 的记录插入到 Faculty_Info 表中：
INSERT INTO Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_NameFaculty_Dept_Id, Faculty_Joining_Date, Faculty_City, Faculty_Salary) VALUES (1001, Arush, Sharma, 4001, 2020-01-02, Delhi, 20000);
INSERT INTO Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_NameFaculty_Dept_Id, Faculty_Joining_Date, Faculty_City, Faculty_Salary) VALUES (1002, Bulbul, Roy, 4002, 2019-12-31, Delhi, 38000 );
INSERT INTO Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_NameFaculty_Dept_Id, Faculty_Joining_Date, Faculty_City, Faculty_Salary) VALUES (1004, Saurabh, Sharma, 4001, 2020-10-10, Mumbai, 45000);
INSERT INTO Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_NameFaculty_Dept_Id, Faculty_Joining_Date, Faculty_City, Faculty_Salary) VALUES (1005, Shivani, Singhania, 4001, 2019-07-15, Kolkata, 42000);
INSERT INTO Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_NameFaculty_Dept_Id, Faculty_Joining_Date, Faculty_City, Faculty_Salary) VALUES (1006, Avinash, Sharma, 4002, 2019-11-11, Delhi, 28000);
INSERT INTO Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_NameFaculty_Dept_Id, Faculty_Joining_Date, Faculty_City, Faculty_Salary)VALUES (1007, Shyam, Besas, 4003, 2021-06-21, Lucknow, 35000);
第 4 步：查看插入的数据
以下 SELECT 语句显示 Faculty_Info 表的数据：
SELECT * FROM Faculty_Info;
输出结果为：
| Faculty_Id | Faculty_First_Name | Faculty_Last_Name | Faculty_Dept_Id | Faculty_Joining_Date | Faculty_City | Faculty_Salary |
|---|---|---|---|---|---|---|
| 1001 | Arush | Sharma | 4001 | 2020-01-02 | Delhi | 20000 |
| 1002 | Bulbul | Roy | 4002 | 2019-12-31 | Delhi | 38000 |
| 1004 | Saurabh | Roy | 4001 | 2020-10-10 | Mumbai | 45000 |
| 1005 | Shivani | Singhania | 4001 | 2019-07-15 | Kolkata | 42000 |
| 1006 | Avinash | Sharma | 4002 | 2019-11-11 | Delhi | 28000 |
| 1007 | Shyam | Besas | 4003 | 2021-06-21 | Lucknow | 35000 |
第 5 步：使用IN操作符以不同方式查看Faculty_Info表的数据
以下查询使用带有 IN 运算符的数值：
SELECT Faculty_Id, Faculty_First_Name, Faculty_Dept_Id, Faculty_Joining_Date, Faculty_Salary FROM Faculty_Info WHERE Faculty_Salary IN ( 38000, 42000, 45000, 35000);
此SQL仅显示那些在 WHERE 子句的 IN 运算符中传递薪水的学院的记录。
输出结果为：
| Faculty_Id | Faculty_First_Name | Faculty_Dept_Id | Faculty_Joining_Date | Faculty_Salary |
|---|---|---|---|---|
| 1002 | Bulbul | 4002 | 2019-12-31 | 38000 |
| 1004 | Saurabh | 4001 | 2020-10-10 | 45000 |
| 1005 | Shivani | 4001 | 2019-07-15 | 42000 |
| 1007 | Shyam | 4003 | 2021-06-21 | 35000 |
以下查询使用带有 IN 逻辑运算符的文本或字符值：
SELECT Faculty_Id, Faculty_First_Name, Faculty_Joining_Date, Faculty_City FROM Faculty_Info WHERE Faculty_City IN ( Mumbai, Kolkata, Lucknow);
此SQL仅显示其城市包含在 WHERE 子句中 IN 运算符的括号中的那些学院的记录。
输出结果为：
| Faculty_Id | Faculty_First_Name | Faculty_Joining_Date | Faculty_City |
|---|---|---|---|
| 1004 | Saurabh | 2020-10-10 | Mumbai |
| 1005 | Shivani | 2019-07-15 | Kolkata |
| 1007 | Shyam | 2021-06-21 | Lucknow |
以下SQL使用带有 IN 逻辑运算符的 DATE Format：
SELECT Faculty_Id, Faculty_First_Name, Faculty_Dept_ID Faculty_Joining_Date, Faculty_Salary FROM Faculty_Info WHERE Faculty_Joining_Date IN (2020-01-02, 2021-06-21, 2020-10-10, 2019-07-15);
此SQL仅显示那些加入日期在 WHERE 子句的 IN 运算符中传递的学院记录。
输出结果为：
| Faculty_Id | Faculty_First_Name | Faculty_Dept_Id | Faculty_Joining_Date | Faculty_Salary |
|---|---|---|---|---|
| 1001 | Arush | 4001 | 2020-01-02 | 20000 |
| 1004 | Saurabh | 4001 | 2020-10-10 | 45000 |
| 1005 | Shivani | 4001 | 2019-07-15 | 42000 |
| 1007 | Shyam | 4003 | 2021-06-21 | 35000 |
以下SQL使用带有 IN 逻辑运算符的 SQL UPDATE 命令：
UPDATE Faculty_Info SET Faculty_Salary = 50000 WHERE Faculty_Dept_ID IN (4002, 4003);
此SQL更新那些 Dept_Id 在 WHERE 子句的 IN 运算符中传递的学院的薪水。
要检查上述查询的结果，请在 SQL 中键入以下 SELECT 查询：
SELECT * FROM Faculty_Info;
输出结果为：
| Faculty_Id | Faculty_First_Name | Faculty_Last_Name | Faculty_Dept_Id | Faculty_Joining_Date | Faculty_City | Faculty_Salary |
|---|---|---|---|---|---|---|
| 1001 | Arush | Sharma | 4001 | 2020-01-02 | Delhi | 20000 |
| 1002 | Bulbul | Roy | 4002 | 2019-12-31 | Delhi | 50000 |
| 1004 | Saurabh | Roy | 4001 | 2020-10-10 | Mumbai | 45000 |
| 1005 | Shivani | Singhania | 4001 | 2019-07-15 | Kolkata | 42000 |
| 1006 | Avinash | Sharma | 4002 | 2019-11-11 | Delhi | 50000 |
| 1007 | Shyam | Besas | 4003 | 2021-06-21 | Lucknow | 50000 |
三、带有子查询的 SQL IN 运算符
在SQL语言中，我们还可以使用带有 IN 逻辑运算符的子查询。
带有子查询的 IN 运算符的语法如下所示：
SELECT Column_Name_1, Column_Name_2, Column_Name_3, ......, Column_Name_N FROM Table_Name WHERE Column_Name IN (Subquery);
如果您想了解带有子查询的 IN 运算符，您必须使用 CREATE 语句在结构化查询语言中创建两个不同的表。
以下查询在数据库中创建 Faculty_Info 表：
CREATE TABLE Faculty_Info
(
Faculty_ID INT NOT NULL PRIMARY KEY,
Faculty_First_Name VARCHAR (100),
Faculty_Last_Name VARCHAR (100),
Faculty_Dept_Id INT NOT NULL,
Faculty_Address Varchar (80),
Faculty_City Varchar (80),
Faculty_Salary INT
);
以下查询在数据库中创建Department_Info表：
CREATE TABLE Department_Info
(
Dept_Id INT NOT NULL,
Dept_Name Varchar(100),
Head_Id INT
);
以下 INSERT 语句将 Faculties 的记录插入到 Faculty_Info 表中：
INSERT INTO Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_NameFaculty_Dept_Id, Faculty_Address, Faculty_City, Faculty_Salary) VALUES (1001, Arush, Sharma, 4001, 22 street, New Delhi, 20000);
INSERT INTO Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_NameFaculty_Dept_Id, Faculty_Address, Faculty_City, Faculty_Salary) VALUES (1002, Bulbul, Roy, 4002, 120 street, New Delhi, 38000 );
INSERT INTO Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_NameFaculty_Dept_Id, Faculty_Address, Faculty_City, Faculty_Salary) VALUES (1004, Saurabh, Sharma, 4001, 221 street, Mumbai, 45000);
INSERT INTO Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_NameFaculty_Dept_Id, Faculty_Address, Faculty_City, Faculty_Salary) VALUES (1005, Shivani, Singhania, 4001, 501 street, Kolkata, 42000);
以下 INSERT 语句在 Department_Info 表中插入部门的记录：
INSERT INTO Department_Info (Dept_ID, Dept_Name, Head_Id) VALUES ( 4001, Arun, 1005);
INSERT INTO Department_Info (Dept_ID, Dept_Name, Head_Id) VALUES ( 4002, Zayant, 1009);
INSERT INTO Department_Info (Dept_ID, Dept_Name, Head_Id) VALUES ( 4003,
Manish, 1007);
以下 SELECT 语句显示 Faculty_Info 表的数据：
SELECT * FROM Faculty_Info;
输出结果为：
| Faculty_Id | Faculty_First_Name | Faculty_Last_Name | Faculty_Dept_Id | Faculty_Address | Faculty_City | Faculty_Salary |
|---|---|---|---|---|---|---|
| 1001 | Arush | Sharma | 4001 | 22 Street | New Delhi | 20000 |
| 1002 | Bulbul | Roy | 4002 | 120 Street | New Delhi | 38000 |
| 1004 | Saurabh | Roy | 4001 | 221 Street | Mumbai | 45000 |
| 1005 | Shivani | Singhania | 4001 | 501 Street | Kolkata | 42000 |
| 1006 | Avinash | Sharma | 4002 | 12 Street | Delhi | 28000 |
| 1007 | Shyam | Besas | 4003 | 202 Street | Lucknow | 35000 |
以下查询显示 Department_Info 表中的部门记录：
SELECT * FROM Department_Info;
以下查询使用带有子查询的 IN 运算符：
SELECT * FROM Faculty_Info WHERE Faculty_Dept_Id IN ( Select Dept_Id FROM Department_Info WHERE Head_Id >= 1007);
该查询显示Faculty_Info 表的Dept_ID 与Department_Info 表的Dept_Id 匹配的那些Faculties 的记录。
输出结果为：
| Faculty_Id | Faculty_First_Name | Faculty_Last_Name | Faculty_Dept_Id | Faculty_Address | Faculty_City | Faculty_Salary |
|---|---|---|---|---|---|---|
| 1002 | Bulbul | Roy | 4002 | 120 Street | New Delhi | 38000 |
| 1006 | Avinash | Sharma | 4002 | 12 Street | Delhi | 28000 |
| 1007 | Shyam | Besas | 4003 | 202 Street | Lucknow | 35000 |
四、SQL NOT IN 语句 语法
NOT IN 是SQL语言中的另一个运算符，与 SQL IN 运算符正好相反。它允许您从表中访问这些值，该表不传递 IN 运算符的括号。
NOT IN 运算符可用于 INSERT、UPDATE、SELECT 和 DELETE SQL 查询。
NOT IN 运算符的语法：
SELECT Column_Name_1, Column_Name_2, Column_Name_3, ......, Column_Name_N FROM Table_Name WHERE Column_Name NOT IN (Value_1, Value_2, Value_3, ......., Value_N);
五、SQL NOT IN 语句 使用步骤
如果要在 SQL 语句中使用 NOT IN 运算符，则必须按相同顺序执行给定的步骤：
- 在 SQL 系统中创建数据库。
- 在数据库中创建新表。
- 在表格中插入数据
- 查看插入的数据
- 使用 NOT IN 运算符查看数据。
现在，我们将通过最好的 SQL 示例，对每一步进行简要说明：
第 1 步：创建简单的新数据库
以下查询在 SQL Server 中创建新的Civil_Industry数据库：
CREATE Database Industry;
第 2 步：创建新表
以下查询在Civil_Industry数据库中创建Worker_Info表：
CREATE TABLE Worker_Info
(
Worker_ID INT NOT NULL PRIMARY KEY,
Worker_Name VARCHAR (100),
Worker_Gender Varchar(20),
Worker_Age INT NOT NULL DEFAULT 18,
Worker_Address Varchar (80),
Worker_Salary INT NOT NULL
);
第 3 步：插入值
以下 INSERT 语句在 Worker_Info 表中插入工人的记录：
INSERT INTO Worker_Info (Worker_ID, Worker_Name, Worker_Gender, Worker_Age, Worker_Address, Worker_Salary) VALUES (1001, Arush, Male, Agra, 35000);
INSERT INTO Worker_Info (Worker_ID, Worker_Name, Worker_Gender, Worker_Age, Worker_Address, Worker_Salary) VALUES (1002, Bulbul, Female, Lucknow, 42000);
INSERT INTO Worker_Info (Worker_ID, Worker_Name, Worker_Gender, Worker_Age, Worker_Address, Worker_Salary) VALUES (1004, Saurabh, Male, 20, Lucknow, 45000);
INSERT INTO Worker_Info (Worker_ID, Worker_Name, Worker_Gender, Worker_Age, Worker_Address, Worker_Salary) VALUES (1005, Shivani, Female, Agra, 28000);
INSERT INTO Worker_Info (Worker_ID, Worker_Name, Worker_Gender, Worker_Age, Worker_Address, Worker_Salary) VALUES (1006, Avinash, Male, 22, Delhi, 38000);
INSERT INTO Worker_Info (Worker_ID, Worker_Name, Worker_Gender, Worker_Age, Worker_Address, Worker_Salary) VALUES (1007, Shyam, Male, Banglore, 20000);
第 4 步：查看表的数据
以下查询显示 Worker_Info 表的数据。
SELECT * FROM Worker_Info;
输出结果为：
| Worker_ID | Worker_Name | Worker_Gender | Worker_Age | Worker_Address | Worker_Saalary |
|---|---|---|---|---|---|
| 1001 | Arush | Male | 18 | Agra | 35000 |
| 1002 | Bulbul | Female | 18 | Lucknow | 42000 |
| 1004 | Saurabh | Male | 20 | Lucknow | 45000 |
| 1005 | Shivani | Female | 18 | Agra | 28000 |
| 1006 | Avinash | Male | 22 | Delhi | 38000 |
| 1007 | Shyam | Male | 18 | Banglore | 20000 |
第 5 步：使用 NOT IN 运算符
以下SQL对数字数据使用 NOT IN 运算符：
SELECT * FROM Worker_Info WHERE Worker_salary NOT IN (35000, 28000, 38000);
此 SELECT 查询显示输出中的所有员工，其薪水未在 NOT IN 运算符中传递。
上述语句的结果如下表所示：
| Worker_ID | Worker_Name | Worker_Gender | Worker_Age | Worker_Address | Worker_Saalary |
|---|---|---|---|---|---|
| 1002 | Bulbul | Female | 18 | Lucknow | 42000 |
| 1004 | Saurabh | Male | 20 | Lucknow | 45000 |
| 1007 | Shyam | Male | 18 | Banglore | 20000 |
以下查询使用具有字符或文本值的 NOT IN 逻辑运算符：
SELECT * FROM Worker_Info WHERE Worker_Address NOT IN (Lucknow, Delhi);
此SQL显示所有地址未在 NOT IN 运算符中传递的工人的记录。
输出结果为：
| Worker_ID | Worker_Name | Worker_Gender | Worker_Age | Worker_Address | Worker_Saalary |
|---|---|---|---|---|---|
| 1001 | Arush | Male | 18 | Agra | 35000 |
| 1005 | Shivani | Female | 18 | Agra | 28000 |
| 1007 | Shyam | Male | 18 | Banglore | 20000 |
热门文章
优秀文章
