Technology Sharing

한어Русский языкEnglishFrançaisIndonesianSanskrit日本語DeutschPortuguêsΕλληνικάespañolItalianoSuomalainenLatina

Inner Join

Combine multiple tables into a temporary table through linking

select table header name from table name 1 inner join table name 2;

 select * from employees inner join departments

select table header name from table name 1 inner join table name 2 on link condition;

select * from employees inner join departments on employees.dept_id = departments.dept_id;
In the temporary new table, you can process it as follows

select table header name from table name 1 inner join table name 2 on link condition [where | order by | group by | limit | having];

Equivalent links: The premise is that there is a header storing the same data in the link

select * from employees inner join departments on employees.dept_id = departments.dept_id;

After defining an alias, you must use the alias to represent the table name

1. select e.* , d.dept_name
2. from employees as e inner join departments as d on e.dept_id=d.dept_id;

Query the total salary of each employee in 2018

select name ,sum(basic+bonus) as total from employees inner join salary on employees.employee_id = salary.employee_id where year(date)=2018 group by name;

select salary.employee_id ,sum(basic+bonus) as total from employees inner join salary on employees.employee_id = salary.employee_id  where year(date)=2018 group by employee_id having total > 300000 order by total desc ,employee_id asc;查询2018年总工资大于30万的员工，按2018年总工资降序排列

Non-equivalent links: There is no header in the table that stores the same data

select employee_id ,basic,garde from salary inner join wage_grade on salary.basic between wage_grade.low and wage_grade.high where year(date)=2018 and month(date)=12;
Query the basic salary of employees in December 2018

select garde as level, count(employee_id) as total number from salary inner join wage_grade on salary.basic between wage_grade.low and wage_grade.high where year(date)=2018 and month(date)=12 group by garde; Query the number of employees with basic salary level in December 2018

Example of joining three tables

select name ,basic ,garde from employees inner join salary on employees.employee_id  = salary.employee_id  inner join wage_grade on salary.basic between wage_grade.low and wage_grade.high where year(date)=2018 and month(date)=12;

External link query

External links compare the differences between two tables

The left link compares the left table data with the right table data. The output result is that all the left table header data is displayed.

On the right, only lines matching the link are displayed.

select table header name from table name 1 left join table name 2 on link condition;

select table header name from table name 1 left join table name 2 on link condition [where | group by | orderby | having | limit];

 select dept_name, name from departments left join employees on departments.dept_id=employees.dept_id where name is null;
 

The right link compares the data in the right table with the data in the left table. The output result is that all the data in the right table header is displayed.

On the right, only lines matching the link are displayed.

select table header name from table name 1 right join table name 2 on link condition [where | group by | orderby | having | limit];

mysql> select dept_name,name from departments as d right join employees as e on d.dept_id=e.dept_id where dept_name is null;

update employees set dept_id=11 where name="bob"; 

Full link query

Combine rows queried by multiple select commands. Multiple select queries should have the same number of table headers.

Output without duplicate lines

mysql> (select date , max(basic) as 工资 from salary where date=20180110)union(select date,min(basic) from salary where date=20180110);

(select query command)union (select query command);

Output without removing duplicate lines

select query command)union all (select query command);

Nested Queries

The select query command contains the select query command

Nested query after where grouping

select table header name from library.table where table header name judgment symbol (select query command)

select employee_id,date,basic,bonus from salary where year(date)=2018 and month(date)=12 and basic > (select basic from salary where year(date)=2018 and month(date)=12 and employee_id=100);

Nested query after having uses the query results in () as filtering conditions.

select table header name from library.table having table header name judgment symbol (select query command)

select dept_id , count(name) as total from employees group by dept_id

having total < (

select count(name) from employees where dept_id=(

select dept_id from departments where dept_name='Development Department')

Nested query after from Use the query result of () as a table

select table header name from (select query result) as temporary table name where filter condition;

select employee_id ,name,email,dept_name from (select d.dept_name,e.* from departments as d inner join employees as e on d.dept_id=e.dept_id) as tmp_table where dept_id=3;

Nested query after select

select table header name, (select query command) as table header name from library.table where filter condition;

select d.* ,(select count(name)from employees as e where d.dept_id=e.dept_id)as vumen from departments as d;