Alias the field during query (if there are special symbols in the alias, it is recommended to enclose the alias in double quotation marks / single quotation marks)

select     Column name 1    as   Alias 1, Column name 2     as     Alias 2    from Table name;

select     Column name 1     Alias 1, Column name 2     Alias 2    from Table name;

Query the department number in the employee table and delete distinct

select distinct department_id from employees;

The role of (plus sign) + in mysql
1: In mysql, the plus sign can only be used as an operator, not a connector
2: '123' + 90: if one of them is character type, mysql will try to convert character type to numerical type. The conversion is successful, and the addition operation is performed normally. The conversion fails. The character type value is converted to 0, and then the addition operation is performed
3: null+10: as long as one party is null, the result must be null

mysql concat (field 1, field 2, field n) function
The employee name (last_name) and last name (first_name) queried in the employees table are connected into one field and displayed as name

select concat(first_name,last_name)  as full name from employees;

**Note: * * if a field in concat function is null, the query result is null
Specific video

Ifnull (field name, return value) function
Query the bonus rate (commission_pct) of all employees in the employees table. If the bonus rate is null, the bonus rate will be displayed as 0

select concat(last_name,first_name) as full name  , ifnull(commission_pct,0)  as Bonus rate from employees 

All columns of the table employees are displayed. The columns are connected with commas, and the column name is displayed as out_put

select 
	  concat(first_name,     ',' ,     last_name,     ',' ,     ifnull(commission_pct,0))     as     'out_put'  
from  employees;

Isnull (field name) function
If the value of each field is null, return 1. If the field value is not null, return 0

Condition query
1: Filter by conditional expression:

>  <  =  !=perhaps<>  >=  <=

2: By logical expression:

&&perhaps and  ||perhaps or   !perhaps not

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 >= 90 and department_id <= 110) or salary > 15000;

3: Fuzzy query

like , between and , in , is null , is not null
 And like Wildcard characters:
					% : Match any number of characters
					_ : Match one character
 Fuzzy queries filter out null values

Query all the information of the employee with name a in the employees table

select * from employees  where last_name like '%a%';

Query all the information of employees whose second character is o and fifth character is a in the employees table

select * from employees  where last_name like '_o__a%';

Query the information of employee ID (employee_id) in the range of 100 to 120

select * from employees  where employee_id>=100 and employee_id<=120;

select * from employees  where employee_id>=100 && employee_id<=120;

select * from employees  where employee _id between 100 and 120;

Query the employee name and bonus rate without commission_pct

select last_name,commission_pct from employees where commission_pct = null
 Error: the equal sign cannot be judged null value

select last_name,commission_pct from employees where commission_pct is null

Phased summary video

Sort query (ascending by default)
Query employee information and ask for salary from low to high

select * from employees order by salary asc;

Query employee information and ask for salary from high to low

select * from employees order by salary desc;

Query employee information and require employment time from high to low

select * from employees order by hiredate desc;

Find the length (field name) function
Query employee id, name and name length, and arrange them in ascending order by name length

select employee_id staff id,  concat(last_name,first_name)  full name , length(last_name)+length(first_name) Name length from employees order by Name length asc;

Query employee id, name and name length, and arrange them in ascending order by name length. If the length is the same, arrange them in ascending order by id

select employee_id staff id,  concat(last_name,first_name)  full name , length(last_name)+length(first_name) Name length from employees order by Name length asc  ,staff id asc;

Query all information in the employee table where the employee's salary is not between 8000 and 17000, and sort by salary

select * from employees where not (salary>=8000 && salary<=17000) order by salary asc