MySQL advanced 2: condition query

For this tutorial, please refer to MySQL 5.5.27 and sqliog installation tutorial MySQL and sqliog installation tutorial

Catalog

WHERE

grammar

Execution sequence

classification

1. Filter by conditional expression

2. Filter by logical expression

3. Fuzzy query

1) like

2) between and

3) in

4) is null

5) < = > exactly equal to

WHERE

grammar

SELECT
	Query list 
FROM
	Table name
WHERE
	Filter criteria; select the one that returns TRUE, skip FALSE

Execution sequence

from-->where-->select

classification

  1. Fi lt er by conditional expression: >
  2. Filter by logical expression: functions as connection condition expression, & &, |! , but and or not is recommended
  3. Fuzzy query: like, between and, in, is null

1. Filter by conditional expression

#Case 1: query the information of employees whose salary is more than 12000
SELECT
	*
FROM
	employees
WHERE 
	salary > 12000;
#Case 2: query the employee name and department number whose department number is not equal to 90
SELECT
	CONCAT(last_name, first_name),
	department_id
FROM
	employees
WHERE
	department_id <> 90;

2. Filter by logical expression

#Case 1: query the employee's name, salary and bonus with salary between 10000 and 20000
SELECT
	first_name,
	salary,
	commission_pct
FROM
	employees
WHERE
	salary >= 10000 AND salary <= 20000;
#Case 2: query the information of employees whose department number is not between 90 and 110, or whose salary is higher than 15000
SELECT
	*
FROM
	employees
WHERE
	NOT(department_id <= 110 AND department_id >= 90) 
	OR salary > 15000;

3. Fuzzy query

1) like

  • Commonly used with% wildcard
  • %Is any number of characters (including 0 characters)
  • _Any single character
#Case 1: query employee information with character a in employee list
SELECT
	*
FROM
	employees
WHERE
	last_name LIKE '%a%'; #The percent sign represents a wildcard
#Case 2: query the employee name and salary whose third character is n and the fifth character is l in the employee list
SELECT
	last_name,
	salary
FROM 
	employees
WHERE
	last_name LIKE '__n_l%';
#Case 3: query the employee whose second character is "Ou" in the employee name
SELECT 
	*
FROM
	employees
WHERE 
	last_name LIKE '_\_%'; #Transfer characters by '\'
	# Or use
	# last_name LIKE '_$_%' ESCAPE '$';

2) between and

  • Simplicity is better than < = and >=
  • Include boundary
  • Two values cannot be reversed
#Case 1: query all employee information with employee number between 100-120
SELECT
	*
FROM 
	employees
WHERE
	employee_id BETWEEN 100 AND 120;

3) in

Meaning: judge whether the information of a field belongs to an item in the in list, meet one of the items and return TRUE

Characteristic:

  • Using in to improve the conciseness of language compared with or
  • The value type of in list must be uniform or compatible
  • Wildcards are not supported for characters in the in list
#Case: query employee's work type number is an employee's name and work type number in it ﹣ prot, ad ﹣ VP and ad ﹣ Pres
SELECT
	last_name,
	job_id
FROM
	employees	
WHERE
	job_id IN('IT_PROT', 'AD_VP', 'AD_PRES');

4) is null

  • =Or < > cannot judge null
  • is null and is not null can judge null value
  • Is is matched with null and cannot be used as
#Case 1: query the employee name and bonus rate without bonus / use of bonus is not null
SELECT last_name, commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;

5) < = > exactly equal to

  • is null can only judge null value
  • < = > it can judge both null value and value, but it is not readable and easy to be confused with < >
#Case 1: query the employee name and bonus rate without bonus
SELECT last_name, commission_pct
FROM employees
WHERE commission_pct <=> NULL;
#Case 2: query the information of 12000 employees
SELECT *
FROM employees
WHERE salary <=> 12000;

 

331 original articles published, praised 133, visited 110000+
Private letter follow

Tags: MySQL

Posted on Wed, 12 Feb 2020 09:37:17 -0500 by rd2010