Base Query

1. Grammar

The select query list from table name is similar to System.out.println (print stuff)

2. Characteristics

1. Query lists can be: fields in tables, constant values, expressions, functions
2. Query results can be a virtual table

3. Examples

1. Query a single field: select field name from table name;

2. Query multiple fields: select field name 1, field name 2,... from table name;

3. Query all fields:
Method 1 select all field names from table names;
Method 2 select *front table name;

4. Query constant value: select constant value; '
Note: Character and date constants must be enclosed in single quotation marks, and numeric values are not required

5. Query expression: select 100%98;

6. Query function: select function name (list of arguments);select version();

7. Aliases (easy to understand, distinguish between duplicates):

#Mode 1 uses as
select 100%98 as Result;
select last_name as surname,first_name as name from employees;

#Mode 2 uses spaces
select last_name surname,first_name name from employees;

#Case: Query salary and display the result as out put
select salary as 'out put' from employees;

8. Deduplication: select distinct field name from table name;

#Case: Query all department numbers involved in the employee table
select distinct department_id from employees;

Role of [+]
The role of [+] in java: 1) Operators, where both operands are numeric; 2) Connectors, where one operand is a string
Role of [+] in mysql: Operators.
(1) Numeric + numeric: addition operation;
(2) If one side is a character type, the conversion to a numeric type will continue with the addition operation, and if the conversion fails, the character type will be converted to 0;
(3) If one of the parties is null, the result is null.

#Case study: Query employee name and name joined into a field and displayed as name
select concat(last_name,first_name) as Full name from employees;

10.concat function
Function: Stitching characters to connect multiple columns together

select concat(Character 1, Character 2, Character 3,...);

#Example
select concat('a','b','c');

11.ifnull function
Function: Determines whether a field or expression is null, returns the specified value for null, otherwise returns the original value

select ifnull(Field name,Specified value)from Table Name;

#Example
select ifnull(commission_pct,0) from employees;

12.isnull function
Function: Determines if a field or expression is null, if it is, returns 1, otherwise returns 0

#Case: Query whether bonus is null
select isnull(commission_pct),commmission_pct from employees;

Conditional Query

1. Grammar

select 
	Query List
from
	Table Name
where
	query criteria;

2. Filtering by Conditional Expressions

Simple Operators: >, <, =,!=, <>, >=, <=

#Example: Filter the employees table for last_name with department_id greater than or equal to 90
select last_name from employees where department_id>=90

3. Filter by logical expression

1. Logical Operators

Used to join conditional expressions
&& // and: same true result is true, others are False
|| // or: One is true and the result is true
!//not: If the connection condition is false, the result is true, otherwise it is false

2. Examples

#Example 1: Query employee names, salaries, and bonuses with salaries between 10000 and 2000
select 
	last_name,salary,commission_pct 
from 
	employees 
where 
	salary>=10000 
and 
	salary<=20000;

#Example 2: Query for employee information whose department number is not between 90 and 110 or whose salary is higher than 15,000
select
	*
from
	employees
where
	department_id<90 
or 
	department_id>110 
or 
	salary>15000;

4. Fuzzy Query

like,between and,in,is null

1.like

Features: Usually used with wildcards
Wildcards: [%] means any number of characters, including 0 characters; [_] means any single character, [\] escape characters

#Example 1: Query employee information whose employee name contains the character a
select 
	*
from
	employees
where 
	last_name like '%a%';

Escape Character

#Example 2: Query employee information whose second character is''in employee name
select * from employees where last_name like '_\_%';

Specify the escape character: escape function

#Specify $as an escape character
select 
	last_name 
from 
	employees 
where 
	last_name like '_$_%' escape '$'

2.between and

Increase language simplicity, include critical values, do not swap order between two critical values

#Example: Query employee information for employee numbers between 100 and 120
select 
	*
from
	employees
where
	employee_id between 100 and 120;

3.in

Determine if the value of a field belongs to an item in the in list
Characteristic:
1. Use in to improve sentence conciseness
(2) Value types of in lists must be consistent or compatible
(3) Wildcards are not supported in the in list

#Example: Query an employee's job number is an employee name and job number in IT_PROG, AD_VP, AD_PRES
select 
	job_id,last_name 
from 
	employees 
where 
	job_id in('IT_PROG','AD_VP','AD_PRES');

4.is null

= or <> cannot be used to determine null values
is null or is not null to determine null values

#Example: Query employee names and bonus rates without bonuses
select
	last_name,
	commission_pct
from
	employees
where
	commission_pct is null;

5. Safety equals [<=>]

#Case 1: Query employee names and bonus rates without bonus
select
	last_name,
	commission_pct
from
	employees
where
	commission_pct <=> null;

#Case 2: Query employee information for 12,000 salaries
select
	last_name,
	salary
from
	employees
where
	salary <=> 12000;

is null: only null values can be judged, high readability, recommended use;
<=>: Both null and normal values can be judged with low readability.

Classic interview questions:

Ask if the results of select *from employees and select *from employees where commission_pct like'%'and last_name like'%%' are the same?
The results are different. If there is a null value in the query field, the results of the query select *from employees where commission_pct like'%%'and last_name like'%%' do not include a null value, and the results of the select *from employees include.

Sort Query

1. Grammar

select Query List
from Table Name
[where Filter Criteria]
order by Sort List [ asc|desc]

Features: 1) asc stands for ascending order, desc for descending order, do not write the default ascending order;
(2) An order by clause can support a single field, multiple fields, expressions, functions, and aliases;
(3) The order by clause is generally placed at the end of a query statement, with the exception of the limit clause;

#Case 1: Query employee information and ask for high to low wages
select * from employees order by salary desc;

#Case 2: Query employee information with department number >=90 and sort by entry time (add filter)
select * from employees where department_id>=90 order by hiredate asc;

#Case 3: Display employee information and annual pay by annual pay level [Filter by expression] [Sort by alias]
select *,salary*12(1+ifnull(commission_pct,0)) as Annual salary from employees order by Annual Pay [ salary*12(1+ifnull(commission_pct,0))] desc;

#Case 4: Show employee's name and salary by name length [sorted by function]
select last_name,salary from employees order by length(last_name) desc;

#Case 6: Querying employee information requires that salary information be sorted in ascending order and then in descending order by employee number [sorted by multiple fields]
select * from employees order by salary asc,employee_id desc;