2, Condition query

1. Syntax: select query list from table name where filter criteria;
2. Function: filter the data of the original table according to the conditions and query the desired data.

1. Filter by conditional expression

Conditional operator: > < > = < =! = < >

Case 1: query employee information with salary greater than 12000

SELECT * FROM employees WHERE salary>12000;

Case 2: query the employee's name and department number whose department number is not equal to 90

SELECT 
	CONCAT(`first_name`,`last_name`),
	`department_id` 
FROM 
	employees 
WHERE 
	`department_id`!=90;

2. Filter by logical expression

1. Action: Join conditional expressions.
2. Logical operator
   ① & &: if both conditions are true, the result is true; otherwise, it is false.
   ② ||: if one of the two conditions is true, the result is true, otherwise it is false.
   ③ !: If the condition is true, it is false after not, otherwise it is true.

Case 1: query the name, salary and bonus of employees whose salary is (10000000)

SELECT 
	CONCAT(`first_name`,`last_name`),
	`salary`,
	`commission_pct` 
FROM 
	employees
WHERE 
	salary>=10000 AND salary<=20000;

Case 2: query the information of employees whose department number is not (90110) or whose salary is higher than 15000

# Method 1
SELECT * FROM employees 
WHERE `department_id`<90 OR `department_id`>110 OR `salary`>15000;

# Method 2
SELECT * FROM employees 
WHERE NOT(`department_id`>=90 AND `department_id`<=110) OR `salary`>15000;

3. Filter by fuzzy query

(1) like

1. Features: generally used with wildcards.
2. wildcard
   ① % any number of characters, including zero characters.
   ② _ Any single character.

Case 1: query employee information with character a in employee name

SELECT * FROM employees
WHERE last_name LIKE '%a%'

Case 2: query the employee name and salary with the third character e and the fifth character a in the employee name

SELECT `last_name`,`salary` FROM employees
WHERE last_name LIKE '__e_a%'

Case 3: the second character in the employee name is_ Employee name

SELECT 
	`last_name`,`salary`
FROM
	employees
WHERE
	# \: escape character.
	last_name LIKE '_\_%';
	# Set escape character: last_name LIKE '_$_%' ESCAPE '$';

(2)between and

1. Benefits: it can improve the brevity of statements.
2. matters needing attention
   ① Contains critical values.
   ② The two critical values cannot be interchanged.

Case 1: query employee information with employee number between 100 and 120

SELECT * FROM employees
WHERE
	#`employee_id` >= 100 and `employee_id`<=120;
	`employee_id` BETWEEN 100 AND 120;

(3)in

1. Meaning: judge whether the value of a field belongs to an item in the in list.
2. characteristic:
   ① Use in to improve statement conciseness.
   ② The value types of the in list must be uniform or compatible.

Case 1: the job number of the employee queried is it_ PROG / AD_ VP / AD_ Employee name and type of work number in Pres

SELECT
	`last_name`,
	`job_id`
FROM
	employees
WHERE
	#job_id = 'IT_PROG' or job_id = 'AD_VP' or job_id = 'AD_PRES';
	`job_id` IN('IT_PROG','AD_VP','AD_PRES');

(4)is null / is not null

1. be careful
   ① = or= Cannot be used to determine null values.
   ② is null / is not null can be used to determine the null value.

Case 1: query employee name and bonus rate without bonus

SELECT
	`last_name`,
	`commission_pct`
FROM
	employees
WHERE
	`commission_pct` IS NULL;

Case 2: query employee name and bonus rate with bonus

SELECT
	`last_name`,
	`commission_pct`
FROM
	employees
WHERE
	`commission_pct` IS NOT NULL;

4. Supplement: safety equals < = > less use

1. be careful:
   ① is null: it can only judge the NULL value, with high readability.
   ② < = >: you can judge NULL values or ordinary values, with low readability.

Case 1: query employee name and bonus rate without bonus

SELECT
	`last_name`,
	`commission_pct`
FROM
	employees
WHERE
	`commission_pct` <=> NULL;

Case 2: query employee information with bonus of 12000

SELECT * FROM employees WHERE salary <=> 12000;