Arithmetic expressions in queries

Use data operators to create expressions for numbers and date data

Operator precedence

MySQL Alias in

Use column aliases

SELECT Name AS Column alias FROM Table name WHERE Conditions ;

MySQL Remove duplicates from

Remove the same lines

stay select Clause distinct Keywords go out on the same line

SELECT Name AS Column alias FROM Table name WHERE Conditions ;

MySQL Comparison conditions in

Other comparison conditions

Use between Conditions

Inquire about employees surface , Salary in 3000-8000 Between employees ID、 Name and salary .

select employee_id,last_name,salary fromemployees where salary between 3000 and 8000;

Use in Conditions

Inquire about employees surface , Find out the salary is 5000,6000,8000 Of employees ID、 Name and salary .

select employee_id,last_name,salary fromemployees where salary between 3000 and 8000;

Use like Conditions

Inquire about employees The second letter of the employee's name is e Employee's name .

select last_name from employees wherelast_name like '_e%';

Use null Conditions

NULL Conditions , Include IS NULL Conditions and IS NOT NULL Conditions .

find emloyees Those employees in the table who have no Commission ID、 Name and Commission .

select employee_id,last_name,commission_pctfrom employees where commission_pct is null;

find employees Those employees who have commissions in the table ID、 Name and Commission .

select employee_id,last_name,commission_pct from employees where commission_pct is not null;

Logical conditions

Inquire about employees The employee name in the table does not contain u The name of the employee .

select last_name from employees where last_name not like '%u%';

The priority rule

Use parentheses to change the priority rule

Use order by Sort

If used ORDER BY Clause , It has to be in SQL At the end of the sentence .

SELECT The execution order of the statements is as follows ：

• FROM Clause
• WHERE Clause
• SELECT Clause
• ORDER BY Clause

Inquire about employees All employees in the table , Show their ID、 Name and salary , And sort by salary in ascending order . select employee_id,last_name,salary from employees order by salary asc;

Multi column sorting

Display in ascending order DEPARTMENT_ID Column , At the same time, it is displayed in descending order SALARY Column . select department_id,salary from employees order by department_id asc ,salary desc;