Shulou(Shulou.com)06/01 Report--

First of all, prepare two tables.

The first is the employee information table, named employee.

Create table employee (emp_id int primary key auto_increment not null, emp_name varchar (50), age int, dept_id int)

Then insert 6 records in the employee information table:

Insert into employee (emp_name,age,dept_id) values ('Achilles dint 19200), (' Barrie dagger 26201), ('clockwould30201), (' Dracula pr é cor 24202), ('Elaine dagger 20200), (' Flemish dagger 38204)

# created 6 employees.

Then there is the department information sheet.

Create table department (dept_id int, dept_name varchar)

# the department information table contains the department ID and the department name.

Insert into department values (20050 'personnel Department'), (20110 'Technical Department'), (202 'sales Department'), (203 'Ministry of Finance')

# added 4 departments to the department table.

Employee table:

+-+

| | emp_id | emp_name | age | dept_id | |

+-+

| | 1 | A | 19 | 200 | |

| | 2 | B | 26 | 201 | |

| | 3 | C | 30 | 201 | |

| | 4 | D | 24 | 202 | |

| | 5 | E | 20 | 200 | |

| | 6 | F | 38 | 204 | |

+-+

Department table:

+-+ +

| | dept_id | dept_name |

+-+ +

| | 200 | personnel Department |

| | 201 | Technical Department |

| | 202 | sales Department |

| | 203 | Ministry of Finance |

+-+ +

I. Cartesian product query:

Select * from employee,department

Results:

+-+ +

| | emp_id | emp_name | age | dept_id | dept_id | dept_name | |

+-+ +

| | 1 | A | 19 | 200 | 200 | personnel Department |

| | 1 | A | 19 | 200 | 201 | Technical Department |

| | 1 | A | 19 | 200 | 202 | sales Department |

| | 1 | A | 19 | 200 | 203 | Ministry of Finance |

| | 2 | B | 26 | 201 | 200 | personnel Department |

| | 2 | B | 26 | 201 | 201 | Technical Department |

| | 2 | B | 26 | 201 | 202 | sales Department |

| | 2 | B | 26 | 201 | 203 | Ministry of Finance |

| | 3 | C | 30 | 201 | 200 | personnel Department |

| | 3 | C | 30 | 201 | 201 | Technical Department |

| | 3 | C | 30 | 201 | 202 | sales Department |

| | 3 | C | 30 | 201 | 203 | Ministry of Finance |

| | 4 | D | 24 | 202 | 200 | personnel Department |

| | 4 | D | 24 | 202 | 201 | Technical Department |

| | 4 | D | 24 | 202 | 202 | sales Department |

| | 4 | D | 24 | 202 | 203 | Ministry of Finance |

| | 5 | E | 20 | 200 | 200 | personnel Department |

| | 5 | E | 20 | 200 | 201 | Technical Department |

| | 5 | E | 20 | 200 | 202 | sales Department |

| | 5 | E | 20 | 200 | 203 | Ministry of Finance |

| | 6 | F | 38 | 204 | 200 | personnel Department |

| | 6 | F | 38 | 204 | 201 | Technical Department |

| | 6 | F | 38 | 204 | 202 | sales Department |

| | 6 | F | 38 | 204 | 203 | Ministry of Finance |

+-+ +

Obviously, this result doesn't make any sense.

Second, internal connection query.

Querying the associated data in both tables is equivalent to using conditions to filter out the correct results from the Cartesian product results.

Here is an example of an inner join query:

Also take the previous two tables as an example, now you need to list the names of all employees, as well as the names of positions.

Select emp_name,dept_name from employee,department where employee.dept_id = department.dept_id

+-+ +

| | emp_name | dept_name |

+-+ +

| | A | personnel Department |

| | B | Technical Department |

| | C | Technical Department |

| | D | sales Department |

| | E | personnel Department |

+-+ +

Third, external connection query.

Left outer link: on the basis of the inner link, there is no result on the right side of the left side.

Select * from employee left join department on employee.dept_id = department.dept_id

# is based on the table on the left

+-+ +

| | emp_id | emp_name | age | dept_id | dept_id | dept_name | |

+-+ +

| | 1 | A | 19 | 200 | 200 | personnel Department |

| | 5 | E | 20 | 200 | 200 | personnel Department |

| | 2 | B | 26 | 201 | 201 | Technical Department |

| | 3 | C | 30 | 201 | 201 | Technical Department |

| | 4 | D | 24 | 202 | 202 | sales Department |

| | 6 | F | 38 | 204 | NULL | NULL |

+-+ +

two。 Right outer link: on the basis of the inner link, there is no result on the left side.

Select * from employee right join department on employee.dept_id = department.dept_id

+-+ +

| | emp_id | emp_name | age | dept_id | dept_id | dept_name | |

+-+ +

| | 1 | A | 19 | 200 | 200 | personnel Department |

| | 2 | B | 26 | 201 | 201 | Technical Department |

| | 3 | C | 30 | 201 | 201 | Technical Department |

| | 4 | D | 24 | 202 | 202 | sales Department |

| | 5 | E | 20 | 200 | 200 | personnel Department |

| | NULL | NULL | NULL | NULL | 203 | Ministry of Finance |

+-+ +

3. Full external links:

Full external link is a combination of left outer link and right outer link.

Select * from employee RIGHT JOIN department on employee.dept_id = department.dept_id UNION select * from employee LEFT JOIN department on employee.dept_id = department.dept_id

Use the union keyword to concatenate the left link with the linked result.

Union: remove all duplicate results.

Union all: there is no deduplication function.

4. Multi-table compound conditional query.

Now, for example, you want to find out which department the company's employees over the age of 25 are in.

Select distinct department.dept_name from employee,department where employee.dept_id = department.dept_id and age > 25

+-+

| | dept_name |

+-+

| | Technical Department |

+-+

Sorted by the age of all employees in the company, from small to big.

Select employee.emp_id,employee.emp_name,employee.age,department.dept_name from employee,department where employee.dept_id = department.dept_id order by age asc

5. Multi-table subquery.

A subquery is to nest the sql statement of one query in the sql statement of another query.

The query results of the inner query statement can provide query conditions for the outer query statement.

Subqueries can contain keywords such as IN, NOT IN, ANY, ALL, EXISTS, and NOT EXISTS.

IN keyword: this keyword is probably the meaning of inclusion.

For example, query the employee table, but dept_id must have appeared in the department table.

It is included in the dept_id field in the department table before it is displayed.

Select * from employee where dept_id in (select dept_id from department)

+-+

| | emp_id | emp_name | age | dept_id | |

+-+

| | 1 | A | 19 | 200 | |

| | 2 | B | 26 | 201 | |

| | 3 | C | 30 | 201 | |

| | 4 | D | 24 | 202 | |

| | 5 | E | 20 | 200 | |

+-+

Use the comparison operator:

=,!, >,

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.