if sql语句_SQL IF语句介绍和概述

2022-09-13 13:12:00 浏览数 (1)

大家好，又见面了，我是你们的朋友全栈君。

if sql语句

This article explores the useful function SQL IF statement in SQL Server.

本文探讨了SQL Server中有用的函数SQL IF语句。

介绍 (Introduction)

In real life, we make decisions based on the conditions. For example, look at the following conditions.

在现实生活中，我们根据条件做出决定。 例如，查看以下情况。

  • If I get a performance bonus this year, I will go for international vacation or else I’ll take a domestic vacation 如果我今年获得绩效奖金，我将去国际度假，否则我将去国内度假
  • If the weather becomes good, I will plan to go on a bike trip or else I won’t 如果天气转好，我会计划骑自行车旅行，否则我不会

In these examples, we decide as per the conditions. For example, if I get a bonus then only I will go for an international vacation else I will go for domestic vacations. We need to incorporate these conditions-based decisions in programming logic as well. SQL Server provides the capability to execute real-time programming logic using SQL IF Statement.

在这些示例中，我们根据条件进行决定。 例如，如果我获得奖金，那么只有我会去国际度假，否则我会去国内度假。 我们还需要将这些基于条件的决策也纳入编程逻辑。 SQL Server提供了使用SQL IF语句执行实时编程逻辑的功能。

句法 (Syntax)

In the following SQL IF Statement, it evaluates the expression, and if the condition is true, then it executes the statement mentioned in IF block otherwise statements within ELSE clause is executed.

在下面SQL IF语句中，它计算表达式，如果条件为true，则执行IF块中提到的语句，否则将执行ELSE子句中的语句。

代码语言：javascript复制
IF (Expression )
BEGIN
  -- If the condition is TRUE then execute the following statement
  True Statements;
END
 
ELSE
BEGIN
   -- If the condition is False then execute the following statement
False Statements
END

We can understand SQL IF Statement using the following flow chart.

我们可以使用以下流程图了解SQL IF语句。

  • The condition in SQL IF Statement should return a Boolean value to evaluate SQL IF语句中的条件应返回一个布尔值以求值
  • We can specify a Select statement as well in a Boolean expression, but it should enclose in parentheses 我们也可以在布尔表达式中指定Select语句，但应将其括在括号中
  • We can use BEGIN and END in the IF Statement to identify a statement block 我们可以在IF语句中使用BEGIN和END来标识一个语句块
  • The ELSE condition is optional to use ELSE条件是可选使用

Let’s explore SQL IF Statement using examples.

让我们使用示例探索SQL IF语句。

示例1：布尔表达式中带有数字值的IF语句 (Example 1: IF Statement with a numeric value in a Boolean expression)

In the following example, we specified a numeric value in the Boolean expression that is always TRUE. It prints the statement for If statement because the condition is true.

在下面的示例中，我们在布尔表达式中指定了一个始终为TRUE的数值。 因为条件为真，所以它为If语句打印语句。

代码语言：javascript复制
IF(1 = 1)
    PRINT 'Executed the statement as condition is TRUE';
    ELSE
    PRINT 'Executed the statement as condition is FALSE';

If we change the condition in the Boolean expression to return FALSE, it prints statement inside ELSE.

如果我们更改布尔表达式中的条件以返回FALSE，它将在ELSE内打印语句。

代码语言：javascript复制
IF(2<=0)
    PRINT 'Executed the statement as condition is TRUE';
    ELSE
    PRINT 'Executed the statement as condition is FALSE';

示例2：布尔表达式中带有变量的IF语句 (Example 2: IF Statement with a variable in a Boolean expression)

In the following example, we use a variable in the Boolean expression to execute the statement based on the condition. For example, if a student obtained more than 80% marks, he passes an examination else, he is failed.

在下面的示例中，我们在布尔表达式中使用变量来根据条件执行语句。 例如，如果学生获得超过80％的分数，则他通过了其他考试，则他不及格。

代码语言：javascript复制
DECLARE @StudentMarks INT= 91;
IF @StudentMarks >= 80
    PRINT 'Passed, Congratulations!!';
    ELSE
    PRINT 'Failed, Try again ';

示例3：布尔表达式中带有变量的多重IF语句 (Example 3: Multiple IF Statement with a variable in a Boolean expression)

We can specify multiple SQL IF Statements and execute the statement accordingly. Look at the following example

我们可以指定多个SQL IF语句并相应地执行该语句。 看下面的例子

  • If a student gets more than 90% marks, it should display a message from the first IF statement 如果学生获得90％以上的分数，则应显示第一条IF语句中的消息
  • If a student gets more than 80% marks, it should display a message from the second IF statement 如果学生获得超过80％的分数，则应显示第二条IF语句中的消息
  • Otherwise, it should print the message mentioned in ELSE statement 否则，它应该打印ELSE语句中提到的消息
代码语言：javascript复制
DECLARE @StudentMarks INT= 91;IF @StudentMarks >= 90    PRINT 'Congratulations, You are in Merit list!!';IF @StudentMarks >= 80 PRINT 'Congratulations, You are in First division list!!';  ELSE    PRINT 'Failed, Try again ';

In this example, student marks 91% satisfy the conditions for both SQL IF statements, and it prints a message for both SQL IF statements.

在此示例中，学生分数91％满足两个SQL IF语句的条件，并且为两个SQL IF语句打印一条消息。

We do not want the condition to satisfy both SQL IF statements. We should define the condition appropriately.

我们不希望条件同时满足两个SQL IF语句。 我们应该适当地定义条件。

代码语言：javascript复制
 DECLARE @StudentMarks INT= 91;
IF @StudentMarks >= 90
    PRINT 'Congratulations, You are in Merit list!!';
IF @StudentMarks >= 80 and @StudentMarks < 90
 PRINT 'Congratulations, You are in First division list!!';
  ELSE
    PRINT 'Failed, Try again ';

In the following screenshot, we can see second IF condition is TRUE if student marks are greater than or equal to 80% and less than 90%.

在下面的屏幕截图中，如果学生分数大于或等于80％且小于90％，我们可以看到第二个IF条件为TRUE。

In the output, we can see the following

在输出中，我们可以看到以下内容

  • First, IF statement condition is TRUE. It prints the message inside the IF statement block 首先，IF语句条件为TRUE。 它在IF语句块内打印消息
  • Second, IF statement condition is FALSE, it does not print the message inside IF statement block 其次，IF语句条件为FALSE，它不会在IF语句块内打印消息
  • It executes the ELSE statement and prints the message for it. In this case, we have two SQL IF statements. The second IF statement evaluates to false, therefore, it executes corresponding ELSE statement 它执行ELSE语句并为其打印消息。 在这种情况下，我们有两个SQL IF语句。 第二条IF语句的计算结果为false，因此，它将执行相应的ELSE语句

We need to be careful in specifying conditions in multiple SQL IF statement. We might get an unexpected result set without proper use of SQL IF statement.

在多个SQL IF语句中指定条件时，我们需要格外小心。 如果不正确使用SQL IF语句，我们可能会得到意外的结果集。

示例4：不带ELSE语句的IF语句 (Example 4: IF Statement without ELSE statement)

We specified above that Else statement is optional to use. We can use SQL IF statement without ELSE as well.

我们在上面指定了Else语句是可选的。 我们也可以使用没有ELSESQL IF语句。

In the following, the expression evaluates to TRUE; therefore, it prints the message.

在下面，表达式的计算结果为TRUE； 因此，它将打印该消息。

代码语言：javascript复制
DECLARE @StudentMarks INT= 95;
IF @StudentMarks >= 90
    PRINT 'Congratulations, You are in Merit list!!';

If the expression evaluates to FALSE, it does not return any output. We should use ELSE statement so that if an evaluation is not TRUE, we can set default output.

如果表达式的计算结果为FALSE，则不返回任何输出。 我们应该使用ELSE语句，以便如果评估结果不是TRUE，则可以设置默认输出。

示例5：执行脚本的IF语句 (Example 5: IF Statement to execute scripts)

In the above examples, we print a message if a condition is either TRUE or FALSE. We might want to execute scripts as well once a condition is satisfied.

在上述示例中，如果条件为TRUE或FALSE，我们将打印一条消息。 一旦满足条件，我们可能还希望执行脚本。

In the following example, if sales quantity is greater than 100000000, it should select records from SalesOrderDtails table.

在下面的示例中，如果销售数量大于100000000，则应从SalesOrderDtails表中选择记录。

If the sales quantity is less than 100000000, it should select records from the SalesOrderHeader table.

如果销售数量少于100000000，则应从SalesOrderHeader表中选择记录。

代码语言：javascript复制
 DECLARE @sales INT;
SELECT @sales = SUM(OrderQty * UnitPrice)
FROM [AdventureWorks2017].[Sales].[SalesOrderDetail];
IF @sales > 100000000
    SELECT *
    FROM [AdventureWorks2017].[Sales].[SalesOrderDetail];
    ELSE
    SELECT *
    FROM [AdventureWorks2017].[Sales].[SalesOrderHeader];

示例6：具有BEGIN和END块的IF (Example 6: IF with BEGIN and END block)

We can use BEGIN and END statement block in a SQL IF statement. Once a condition is satisfied, it executes the code inside the corresponding BEGIN and End block.

我们可以在SQL IF语句中使用BEGIN和END语句块。 一旦满足条件，它将在相应的BEGIN和End块内执行代码。

代码语言：javascript复制
 DECLARE @StudentMarks INT= 70;
IF @StudentMarks >= 90
    BEGIN
        PRINT 'Congratulations, You are in Merit list!!';
END;
    ELSE
    BEGIN
        PRINT 'Failed, Try again ';
END;

We can specify multiple statements as well with SQL IF statement and BEGIN END blocks. In the following query, we want to print a message from two print statements once a condition is satisfied.

我们也可以使用SQL IF语句和BEGIN END块指定多个语句。 在下面的查询中，我们希望在满足条件后从两个打印语句中打印一条消息。

  • Note: We should have an END statement with corresponding BEGIN block.注意 ：我们应该有一个带有相应BEGIN块的END语句。
代码语言：javascript复制
DECLARE @StudentMarks INT= 70;
IF @StudentMarks >= 90
    BEGIN
        PRINT 'Congratulations, You are in Merit list!!';
    Print 'Second statement.'
END;
    ELSE
    BEGIN
        PRINT 'Failed,Try again ';
    Print 'Second ELSE statement'
END;

结论 (Conclusion)

In this article, we explored the SQL IF statement and its usage with examples. We can write real-time conditions-based code using SQL IF statements. If you had comments or questions, feel free to leave them in the comments below.

在本文中，我们通过示例探讨了SQL IF语句及其用法。 我们可以使用SQL IF语句编写基于条件的实时代码。 如果您有任何意见或问题，请随时将其留在下面的评论中。

翻译自: https://www.sqlshack.com/sql-if-statement-introduction-and-overview/

if sql语句

发布者：全栈程序员栈长，转载请注明出处：https://javaforall.cn/160602.html原文链接：https://javaforall.cn

sql sqlserver 数据库 linux 数据结构

0 人点赞