sql运算符_SQL运算符-白红宇

sql运算符_SQL运算符

阅读量：2533 次

发布时间：2019-05-11

本文共 12822 字，大约阅读时间需要 42 分钟。

sql运算符

SQL Operators are a series of characters, symbols and words which are used as part of the WHERE clause.

SQL运算符是一系列字符，符号和单词，它们用作WHERE子句的一部分。

SQL运算符 (SQL Operators)

operators are used to perform operations like comparisons and arithmetic operations.
运算符用于执行诸如比较和算术运算之类的运算。

These Operators are used to specify conditions in an SQL statement.
这些运算符用于在SQL语句中指定条件。

SQL operators help us in selecting only specific records from the tables or views.
SQL运算符可帮助我们从表或视图中仅选择特定记录。

SQL运算符类型 (SQL Operators Types)

Broadly SQL operators are classified in following parts.

大致上，SQL运算符分为以下几部分。

Arithmetic Operators
算术运算符

Comparison Operators
比较运算符

Logical Operators
逻辑运算符

Bitwise Operators
按位运算符

Let’s try to try to understand all the above-mentioned operators one by one.

让我们尝试一一理解所有上述运算符。

SQL算术运算符 (SQL Arithmetic Operators)

SQL Arithmetic operators are the operators which are used for mathematical calculation like addition, subtraction etc. They are used with .

SQL算术运算符是用于数学计算（如加法，减法等）的运算符。它们与。

Operator	Description	Example
+ (Addition)	Adds values on both sides of the operator.	SELECT 30 + 20; Output: 50
-(Substraction)	Subtracts values on right side from the value on left side of the operator.	SELECT 30 – 20; Output: 10
*(Multiplication)	Multiplies the values on both sides of the operator	SELECT 30 * 20; Output: 600
/(Division)	Divides left hand side value by right hand side value.	SELECT 30 / 20; Output: 1
%(Modulus)	Divides left hand side value by right hand side value and returns the reminder	SELECT 30 % 20; Output: 10

操作员	描述	例
+（加法）	在运算符的两边添加值。	选择30 + 20; 输出：50
-（减法）	从运算符左侧的值中减去右侧的值。	选择30 – 20; 输出：10
*（乘法）	将运算符两边的值相乘	选择30 * 20; 输出：600
/（师）	用左侧值除以右侧值。	选择30/20; 输出1
％（模量）	用左侧值除以右侧值并返回提醒	选择30％20; 输出：10

SQL比较运算符 (SQL Comparison Operators)

Comparison operators are the operators which are used for comparison between two values. To understand the comparison operator better, we will take example of Employee table as shown below.

比较运算符是用于在两个值之间进行比较的运算符。为了更好地理解比较运算符，我们将以Employee表为例，如下所示。

Let’s understand usage of comparison operators using the table above as an example.

EmpId	EmpName	EmpAge	EmpSalary
1	John	32	2000
2	Smith	25	2500
3	Henry	29	3000

Operator	Description	Example
= (Equal To)	Checks if the values of two operands are equal, if its equal then condition becomes true.	SELECT EmpName FROM Employee WHERE EmpSalary=2000; Output: John
!= (Not Equal To)	Checks if the values of two operands are not equal, if values are not equal then condition becomes true.	SELECT EmpName FROM Employee WHERE EmpSalary!=2000; Output: Smith Henry
<> (Not Equal To)	Checks if the values of two operands are equal or not, if values are not equal then condition becomes true.	SELECT EmpName FROM Employee WHERE EmpSalary<>2000; Output: Smith Henry
> (Greater Than)	Checks if the value of left operand is greater than the value of right operand, condition becomes true if it is yes.	SELECT EmpName FROM Employee WHERE EmpSalary > 2000 Output: Smith Henry
< (Less Than)	Checks if the value of left operand is less than the value of right operand, condition becomes true if it is yes.	SELECT EmpName FROM Employee WHERE EmpSalary < 2000 Output: No Records Found
>= (Greater than or Equal To)	Checks if the value of left operand is greater than or equal to the value of right operand, condition becomes true if its yes.	SELECT EmpName FROM Employee WHERE EmpSalary >= 2000 Output: John Smith Henry
<=(Less than or Equal To)	Checks if the value of left operand is less than or equal to the value of right operand, condition becomes true if it is yes.	SELECT EmpName FROM Employee WHERE EmpSalary <= 2000 Output: John
!< (Not Less than)	Checks if the value of left operand is not less than the value of right operand, condition becomes true if it is yes.	SELECT EmpName FROM Employee WHERE EmpSalary !< 2000 Output: Smith Henry
!> (Not Greater Than)	Checks if the value of left operand is not greater than the value of right operand, condition becomes true if it is yes.	SELECT EmpName FROM Employee WHERE EmpSalary !> 2000 Output: – John

SQL Logical Operators

Logical operators are the operators which are used for logical operations. To understand the logical operator better, we will take example of Employee table as shown below.

Let’s understand usage of logical operator using the table above as an example.

Operator	Description	Example
ALL	ALL operator is used to compare a value to all the values in another set of values.	SELECT EmpName FROM Employee WHERE EmpAge > ALL (SELECT EmpAge FROM Employee WHERE EmpSalary >= 2500); Output: John Smith
AND	AND operator allows the multiple conditions in an SQL statement’s WHERE clause.	SELECT EmpName FROM Employee WHERE EmpSalary > 2000 and EmpAge > 28 Output: Henry
ANY	ANY operator is used to compare a value to any applicable value in the list based on the condition.	SELECT EmpName FROM Employee WHERE EmpAge > ANY (SELECT EmpAge FROM Employee WHERE EmpSalary >= 2500); Output: John Smith
BETWEEN	BETWEEN operator is used to search for values that are within a range, given the minimum value and the maximum value.	SELECT EmpName FROM Employee WHERE EmpAge BETWEEN 25 AND 30; Output: Smith Henry
EXISTS	EXISTS operator is used to search for the presence of a row in a specified table that meets a certain criterion.	SELECT EmpName FROM Employee WHERE EXISTS (SELECT EmpName FROM Employee WHERE EmpSalary >= 2500); Output: Smith Henry
IN	IN operator is used to compare a value to a list of literal values that have been specified.	SELECT EmpName FROM Employee WHERE EmpSalary IN (2000, 2500); Output: John Smith
LIKE	LIKE operator is used to compare a value to similar values using wildcard operators.	SELECT EmpName FROM Employee WHERE EmpName LIKE ‘Jo%’; Output: John
NOT	NOT operator reverses the meaning of the logical operator with which it is used.	SELECT EmpName FROM Employee WHERE EmpSalary IS NOT NULL Output: John Smith Henry
OR	OR operator is used to combine multiple conditions in one SQL statement’s WHERE clause.	SELECT EmpName FROM Employee WHERE EmpSalary > 2000 OR EmpName IS NOT NULL; Output: John Smith Henry
IS NULL	IS NULL operator is used to compare a value with a NULL value.	SELECT EmpName FROM Employee WHERE EmpSalary IS NULL; Output: No records found
UNIQUE	UNIQUE operator searches every row of a specified table for uniqueness	SELECT UNIQUE(EmpName) FROM Employee WHERE EmpSalary IS NOT NULL; Output: John Smith Henry

SQL Bitwise Operators

Bitwise operators are the operators which are used on bit of data.

Operator	Description
&	Bitwise AND operator
\|	Bitwise OR operator
^	Bitwise Exclusive OR operator
<<	Left Shift operator
>>	Right Shift operator

Here is a simple program showing usage of sql bitwise operators.

That’s all for SQL operators in a nutshell.

I love Open Source technologies and writing about my experience about them is my passion.

Follow Author

Comments

Valentino
says:

And finally,

select 27 ^ 19′

is also wrong, it should return 01000.

Thanks for the effort, but I think you should just review this guide, there are too many wrong points,.

Valentino
says:

The ANY is also incorrect, the returned list should be John, Henry., a

The EXISTS is wrong, the way you are using it returns all the 3 names, because the EXISTS is true, and you are just selecting all the table rows, it is equivalent to doing:

SELECT EmpName FROM Employee
WHERE 1=1;

I guess, the way you wanted to use EXISTS was:

SELECT a.EmpName FROM Employee a WHERE EXISTS (SELECT b.EmpName FROM Employee b WHERE b.EmpSalary >= 2500 AND a.EmpName=b.EmpName)

The use of UNIQUE is also not convincing. Normally UNIQUE is used when creating a table to establish a constraint on a column, I guess you here meant to use DISTINCT in place of UNIQUE.

Valentino
says:

The output of operator ALL is also incorrect, it should be only John.

Valentino
says:

Pankaj, I have never used the (Not Less than) operator, but for the (Not Less than) operator you are selecting the rows for which EmpSalary is greater or equal than 2000, So, I expect also John to be in the list, Or am I misunderstanding? Thanks for the tutorial!

让我们以上表为例了解比较运算符的用法。

EmpId	EmpName	EmpAge	薪酬
1个	约翰	32	2000
2	史密斯	25	2500
3	亨利	29	3000

操作员	描述	例
=（等于）	检查两个操作数的值是否相等，如果相等，则条件为真。	从雇员那里选择EmpName EmpSalary = 2000; 输出：约翰
！=（不等于）	检查两个操作数的值是否不相等，如果值不相等，则条件为真。	从员工那里选择EmpName EmpSalary！= 2000; 输出：史密斯亨利
<>（不等于）	检查两个操作数的值是否相等，如果值不相等，则条件为真。	从雇员中选择EmpName EmpSalary <> 2000; 输出：史密斯亨利
>（大于）	检查left操作数的值是否大于right操作数的值，如果条件为true，则条件为true。	从雇员的工资中选择EmpName> 2000 输出：史密斯亨利
<（小于）	检查left操作数的值是否小于right操作数的值，如果条件为true，则条件为true。	从雇员中选择EmpName EmpSalary <2000 输出：没有找到记录
> =（大于或等于）	检查左操作数的值是否大于或等于右操作数的值，如果条件为true，则条件为true。	从雇员那里选择EmpName EmpSalary> = 2000 输出：约翰史密斯亨利
<=（小于或等于）	检查左操作数的值是否小于或等于右操作数的值，如果条件为true，则条件为true。	从雇员中选择EmpName EmpSalary <= 2000 输出：约翰
！<（不少于）	检查left操作数的值是否不小于right操作数的值，如果条件为true，则条件为true。	从雇员那里选择EmpName EmpSalary！<2000 输出：史密斯亨利
！>（不大于）	检查left操作数的值是否不大于right操作数的值，如果条件为true，则条件为true。	从雇员那里选择EmpName EmpSalary！> 2000 输出：– 约翰

SQL逻辑运算符

逻辑运算符是用于逻辑运算的运算符。为了更好地理解逻辑运算符，我们将以Employee表为例，如下所示。

让我们以上表为例来了解逻辑运算符的用法。

操作员	描述	例
所有	ALL运算符用于将一个值与另一组值中的所有值进行比较。	从员工中选择EmpName 哪里EmpAge>所有（从雇员那里选择EmpAge哪里EmpSalary> = 2500）；输出：约翰史密斯
和	AND运算符允许SQL语句的WHERE子句中的多个条件。	从员工那里选择EmpName工资> 2000和EmpAge> 28 输出：亨利
任何	ANY运算符用于根据条件将值与列表中的任何适用值进行比较。	从员工中选择EmpName 哪里有EmpAge>任何（从雇员那里选择EmpAge哪里有EmpSalary> = 2500）；输出：约翰史密斯
之间	给定最小值和最大值，使用BETWEEN运算符搜索范围内的值。	在25至30岁之间的员工中选择EmpName；输出：史密斯亨利
存在	EXISTS运算符用于搜索满足特定条件的指定表中是否存在行。	从员工中选择EmpName 存在的位置（从雇员中选择EmpName EmpSalary> = 2500）；输出：史密斯亨利
在	IN运算符用于将值与指定的文字值列表进行比较。	从员工所在的SELECT EmpName雇员中（2000，2500）; 输出：约翰史密斯
喜欢	LIKE运算符用于使用通配符运算符将值与相似值进行比较。	从雇员那里选择EmpName像Emp％一样的'Jo％'; 输出：约翰
不	NOT运算符颠倒了与其一起使用的逻辑运算符的含义。	从雇员那里选择EmpName EmpSalary不为空输出：约翰史密斯亨利
要么	OR运算符用于在一个SQL语句的WHERE子句中组合多个条件。	从雇员那里选择EmpName EmpSalary> 2000或EmpName不为空；输出：约翰史密斯亨利
一片空白	IS NULL运算符用于将值与NULL值进行比较。	从雇员那里选择EmpName EmpSalary为NULL；输出：没有找到记录
独特	UNIQUE运算符在指定表的每一行中搜索唯一性	从雇员那里选择SELECT UNIQUE（EmpName）Empsalary不为NULL；输出：约翰史密斯亨利

SQL按位运算符

按位运算符是用于数据位的运算符。

操作员	描述
和	按位与运算符
\|	按位或运算符
^	按位异或运算符
<<	左移运算符
>>	右移运算符

这是一个显示sql按位运算符用法的简单程序。

-- 27 = 11011-- 19 = 10011select 27 & 19; -- 10011select 27 | 19; -- 11011select 27 ^ 19; -- 00100select 5 << 2; -- 101 to 10100 i.e. 20select 17 >> 2; -- 10001 to 100 i.e. 4

简而言之，这就是SQL运算符的全部内容。

上一个

SQL面试问答

下一个

SQL表达式

我喜欢开源技术，写关于我的经验是我的热情。

关注作者

注释

华伦天奴（
Valentino）说：

最后，

选择27 ^ 19′

同样是错误的，它应该返回01000。

感谢您的努力，但我认为您应该只阅读本指南，错误点太多。

华伦天奴（
Valentino）说：

ANY也不正确，返回的列表应该是John，Henry。

EXISTS是错误的，您使用它的方式将返回所有3个名称，因为EXISTS为true，并且您只选择所有表行，这等效于：

从员工中选择EmpName
1 = 1时；

我想，您想使用EXISTS的方式是：

从存在雇员的SELECT中选择a.EmpName（从雇员b的选择b.EmpName中b.EmpSalary> = 2500和a.EmpName = b.EmpName）

使用UNIQUE也不令人信服。通常，在创建表以对列建立约束时会使用UNIQUE，我想您在这里是要使用DISTINCT代替UNIQUE。