SQL NOT 运算符

一、SQL NOT 运算符 语法

NOT 是SQL语言中的逻辑运算符。

SQL 中的此运算符否定任何布尔表达式的输出。

该运算符将给定值与每个值进行比较，并返回那些不满足条件的值。

NOT 运算符运算符主要用于 WHERE 子句中的 INSERT、UPDATE、DELETE 和 UPDATE SQL 语句。

NOT 运算符 语法：

SELECT  First_Column_Name, Second_Column_Name, ...., Nth_Column_Name  FROM  Table_Name  WHERE NOT  Condition;   

在 SQL 语法中，我们必须在 WHERE 子句中用条件指定 NOT 关键字。

二、SQL NOT 运算符 示例

要理解SQL语言中 NOT 运算符的查询，我们必须创建一个名为 Worker_Info 的新表。此表包含在该行业工作的工人的详细信息。

以下查询在行业数据库中创建Worker_Info表：

CREATE TABLE Worker_Info  
(  
Worker_ID INT NOT NULL PRIMARY KEY,    
Worker_First_Name VARCHAR (100),    
Worker_Last_Name VARCHAR (100),    
Worker_Dept_Id INT NOT NULL,  
Worker_Joining_Date Varchar (80),  
Worker_City Varchar (80),  
Worker_Salary INT   
);  

以下 INSERT 语句插入了在该行业工作的多个工人的记录：

INSERT INTO Worker_Info (Worker_ID, Worker_First_Name, Worker_Last_Name Worker_Dept_Id, Worker_Joining_Date, Worker_City, Worker_Salary) VALUES (5001, Arush, Sharma, 1001, 2020-01-02, Delhi, 20000);  
INSERT INTO Worker_Info (Worker_ID, Worker_First_Name, Worker_Last_Name Worker_Dept_Id, Worker_Joining_Date, Worker_City, Worker_Salary) VALUES (5002, Bulbul, Roy, 1002, 2019-12-31, Delhi, 38000 );  
INSERT INTO Worker_Info (Worker_ID, Worker_First_Name, Worker_Last_Name Worker_Dept_Id, Worker_Joining_Date, Worker_City, Worker_Salary) VALUES (5004, Saurabh, Sharma, 1001, 2020-10-10, Mumbai, 45000);  
INSERT INTO Worker_Info (Worker_ID, Worker_First_Name, Worker_Last_Name Worker_Dept_Id, Worker_Joining_Date, Worker_City, Worker_Salary) VALUES (5005, Shivani, Singhania, 1001, 2019-07-15, Kolkata, 42000);  
INSERT INTO Worker_Info (Worker_ID, Worker_First_Name, Worker_Last_Name Worker_Dept_Id, Worker_Joining_Date, Worker_City, Worker_Salary) VALUES (5006, Avinash, Sharma, 1002, 2019-11-11, Delhi, 28000);  
INSERT INTO Worker_Info (Worker_ID, Worker_First_Name, Worker_Last_Name Worker_Dept_Id, Worker_Joining_Date, Worker_City, Worker_Salary)VALUES (5007, Shyam, Besas, 1003, 2021-06-21, Lucknow, 35000);  

以下查询显示了 Worker_Info 表的数据。

SELECT * FROM Worker_Info; 

输出结果为：

Worker_Id	Worker_First_Name	Worker_Last_Name	Worker_Dept_Id	Worker_Joining_Date	Worker_City	Worker_Salary
5001	Arush	Sharma	1001	2020-01-02	Delhi	20000
5002	Bulbul	Roy	1002	2019-12-31	Delhi	38000
5004	Saurabh	Roy	1001	2020-10-10	Mumbai	45000
5005	Shivani	Singhania	1001	2019-07-15	Kolkata	42000
5006	Avinash	Sharma	1002	2019-11-11	Delhi	28000
5007	Shyam	Besas	1003	2021-06-21	Lucknow	35000

以下查询显示了上表中不属于Kolkata市的工人的记录：

SELECT * FROM Worker_Info WHERE NOT Worker_City = 'Kolkata';  

输出结果为：

Worker_Id	Worker_First_Name	Worker_Last_Name	Worker_Dept_Id	Worker_Joining_Date	Worker_City	Worker_Salary
5001	Arush	Sharma	1001	2020-01-02	Delhi	20000
5002	Bulbul	Roy	1002	2019-12-31	Delhi	38000
5004	Saurabh	Roy	1001	2020-10-10	Mumbai	45000
5006	Avinash	Sharma	1002	2019-11-11	Delhi	28000
5007	Shyam	Besas	1003	2021-06-21	Lucknow	35000

以下查询不显示上表中薪水大于 30000 的工人的记录。

SELECT * FROM Worker_Info WHERE NOT Worker_Salary > 30000; 

输出结果为：

Worker_Id	Worker_First_Name	Worker_Last_Name	Worker_Dept_Id	Worker_Joining_Date	Worker_City	Worker_Salary
5001	Arush	Sharma	1001	2020-01-02	Delhi	20000
5006	Avinash	Sharma	1002	2019-11-11	Delhi	28000

三、SQL 中的 NOT 运算符和 IN 运算符

我们还可以在 SQL 中使用 NOT 运算符和 IN 运算符来过滤那些没有在 IN 运算符的括号中传递的记录。

1、NOT 运算符与 IN 运算符的语法

SELECT First_Column_Name, Second_Column_Name, …., Nth_Column_Name FROM Table_Name WHERE Column_Name NOT LIKE Pattern;

2、带有 IN 运算符的 NOT 运算符示例

以下查询显示了那些 Worker_ID 未在 IN 运算符中传递的工人的记录：

SELECT * FROM Worker_Info WHERE Worker_ID NOT IN (5004, 5005, 5007);  

输出结果为：

Worker_Id	Worker_First_Name	Worker_Last_Name	Worker_Dept_Id	Worker_Joining_Date	Worker_City	Worker_Salary
5001	Arush	Sharma	1001	2020-01-02	Delhi	20000
5002	Bulbul	Roy	1002	2019-12-31	Delhi	38000
5006	Avinash	Sharma	1002	2019-11-11	Delhi	28000

四、SQL 中的 NOT 运算符与 LIKE 运算符

我们还可以在 SQL 中使用 NOT 运算符和 LIKE 运算符来过滤那些与指定模式不匹配的值。

1、NOT 运算符与 LIKE 运算符的语法

SELECT First_Column_Name, Second_Column_Name, …., Nth_Column_Name FROM Table_Name WHERE Column_Name NOT LIKE Pattern;  

2、NOT 运算符与 LIKE 运算符的示例

示例 1：以下查询显示了 worker_Info 表中 First Name 不以 A 开头的工人的记录。

SELECT * FROM Worker_Info WHERE Worker_First_Name NOT LIKE 'A%'  

输出结果为：

Worker_Id	Worker_First_Name	Worker_Last_Name	Worker_Dept_Id	Worker_Joining_Date	Worker_City	Worker_Salary
5002	Bulbul	Roy	1002	2019-12-31	Delhi	38000
5004	Saurabh	Roy	1001	2020-10-10	Mumbai	45000
5005	Shivani	Singhania	1001	2019-07-15	Kolkata	42000
5007	Shyam	Besas	1003	2021-06-21	Lucknow	35000

示例 2：以下查询不显示 worker_Info 表中城市名称以 D 字符开头并以 i 字符结尾的工人的记录。 

SELECT Worker_Id, Worker_First_Name, Worker_City, Worker_Salary FROM Worker_Info WHERE Worker_City NOT LIKE 'D%i' 

输出结果为：

Worker_Id	Worker_First_Name	Worker_Last_Name	Worker_Dept_Id	Worker_Joining_Date	Worker_City	Worker_Salary
5004	Saurabh	Roy	1001	2020-10-10	Mumbai	45000
5005	Shivani	Singhania	1001	2019-07-15	Kolkata	42000
5007	Shyam	Besas	1003	2021-06-21	Lucknow	35000