SQL entry conditional expressions

SQL entry conditional expressions

The where clause and having clause are mainly used to filter qualified tuples, followed by conditional expressions.

0. and, or condition connection

Similar to common programming languages, it is mainly used for condition concatenation. And, if both are true, the result is true. Or, if either of them is true, the result is true.

If the where clause requires three or more conditions and uses the and or operators at the same time, parentheses should be used to specify the intent, so that the database or yourself or others can understand the code and enhance readability. For example:

select emp_idfrom employeewhere end_date is null and (title='Teller' or start_date<'2007-01-01');

1. negation of the not condition

Not is a non-operation, which returns the inverse of the condition.

2. Equal Conditions

The format is 'column = expression', for example:

• Title = 'teller'
• Fed_id = '2017-111'
• ...

These conditions are equal because they make one expression equal to another expression.

select pt.name product_type, p.name productfrom product p inner join product_type pton p.product_type_cd = pt.product_type_cdwhere pt.name = 'Customer Accounts';

Select the product whose name is Customer Accounts.

3. Unequal conditions

This is used to judge that two expressions are not equal. The operator is '<>' or '! = '. For example, select a product whose name is not "Customer Accounts:

select pt.name product_type, p.name productfrom product p inner join product_type pton p.product_type_cd = pt.product_type_cdwhere pt.name <> 'Customer Accounts';

4. Range Conditions

Common values that indicate the range are: <, >,< =, >=, between... and .... The key point is... and ..., the selected conditions include the tuples that meet the two endpoints (the upper and lower limits of the range are closed), and the upper and lower limits must be determined. The lower limit is after between and the upper limit is behind and. Incorrect use may cause problems, this is because... and... when the database is executed, it is converted to two operation conditions: <= and> =. For example:

Correct use:

The following error occurs:

Here, the database actually runs the following statement:

select emp_id,  fname, lname, start_datefrom employeewhere start_date >= '2007-01-01' and start_date <= '2005-01-01';

Because there is no date greater than, but less than, the result is of course a null value.

The result of the number or date range can be easily viewed, but the result is not so easy for the general string range. It is necessary to know the dictionary sequence of the characters in the character set.

5. Member Conditions

For a condition: where product_cd = 'chk' or product_cd = 'sav' or product_cd = 'cd' or product_cd = 'mm'; obviously, writing is very troublesome, there are only four conditions, but when there are dozens of conditions, such writing is very boring. In this case, the in operator can be used. The preceding code can be written as follows:

select account_id, product_cd, cust_id, avail_balancefrom accountwhere product_cd in ('CHK', 'SAV', 'CD', 'MM');

The effect is the same as that of the first one, but it is much easier.

6. Matching conditions

Wildcard characters are often used for matching. Although some built-in functions provided by SQL can complete tasks, the flexibility is relatively poor. The wildcard characters are as follows:

• _: Match a character
• %: Match any character (including 0 characters)

select lnamefrom employeewhere lname like '_a%e%';

The purpose of this Code is to select the first character in the lname from the employee table as any, and the second must be a and e, which can appear at any location after the lname.

Also, you can use regular expressions to learn more later. It seems like a long journey ....