Chapter II single-Table query T-SQL language Basics (2)

Single table query (2) 2.2 predicates and operators

T-SQL has several different language elements that can specify logical expressions, such as query filters (where and have), check constraints, and so on.

Various predicates (values of true,false, or unknown expressions) and operators can be used in logical expressions.

The predicates supported by T-SQL include In,between, as well as like.

--predicates:in, between, likeSELECTOrderID, Empid, OrderDate fromsales.ordersWHEREOrderIDinch(10248,10249,10250);--In this predicate is used to check whether a value (or scalar expression) is equal to at least one of a set of elements.SELECTOrderID, Empid, OrderDate fromsales.ordersWHEREOrderIDbetween 10300  and 10310;--between is used to check whether a value is within a specified range, including two specified boundary valuesSELECTEmpid, FirstName, LastName fromHR. EmployeesWHERELastName likeN'D%';--like this predicate is used to check whether a string value matches a specified pattern--n ' D% ', n is used to denote that a string is a Unicode data type (nchar or nvarchar), which corresponds to a regular character data type (char or varchar)

The comparison operator supported by T-sql: =,>,<,>=,<=,<>,!=,!<,!>, where the last 3 operators are not standard operators. Because nonstandard operators can be substituted with corresponding standard operators, Avoid using nonstandard operators as much as possible.

--SELECT  OrderID, Empid, OrderDate from sales.ordersWHERE  >='20080101'; -- the query returns all orders generated after January 1, 2008

If you want to combine multiple logical expressions, you can use the logical operators OR and and. If you want to negate a Boolean expression, you can use the NOT operator.

-- Logical Operators:and, OR, not SELECT OrderID, Empid, OrderDate  from sales.orders WHERE >= ' 20080101 '   and inch (135); -- The query returns all orders processed by an employee with ID 1,3,5 after January 1, 2008

The arithmetic operators supported by T-sql: +,-, *,/, and the% (modulo) operator, which returns the remainder of an integer division operation.

-- arithmetic operators: +,-, *,/,% SELECT OrderID, ProductID, qty, UnitPrice, discount,   * * (1- as val from Sales.orderdetails;

Note: The data type of a scalar expression involving two operands depends on the priority of the data type and is determined by the higher precedence operand.

If two operands have the same data type, the result of the expression is the same data type.

If two operands have different types, operands with lower type precedence are automatically converted to higher-level types

T-SQL operator precedence rules:

1. ()

2.*,/,%

3.+,-,

4.=,>,<,>=,<=,<>,!=,!<,!>

5.NOT

6.AND

7.between,in,like,or

8. =

--Operators Precedence--and precedes ORSELECTOrderID, CustID, Empid, OrderDate fromsales.ordersWHERECustID= 1     andEmpidinch(1,3,5)    ORCustID=  -     andEmpidinch(2,4,6);--equivalent toSELECTOrderID, CustID, Empid, OrderDate fromsales.ordersWHERE(CustID= 1         andEmpidinch(1,3,5) )    OR(CustID=  -         andEmpidinch(2,4,6) );--*,/precedes +,-SELECT Ten + 2 * 3   -- -SELECT(Ten + 2)* 3 -- $

111

Chapter II single-Table query T-SQL language Basics (2)