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
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