MySQL Query (advanced) (each punctuation is the focus)

MySQL is a common need in work, so it has to be mastered, and what we have been saying is how to save it.

You'll only be able to save a fart. So I hope you can read the data in the way of how to do a little bit more effort.

This and the last one is dry, and I am also the first time to learn. Anyway, I understand that, so I read it over and over again, it is slowly a little feeling.

At present also tidy very disorderly, first will look, and so on later thought up I will further tidy up.

Simple query: Single-table query and multi-table query

Distinct: Go heavy

Single-table only query with distinct

GROUP BY: Grouping

Single query for multiple tables with GROUP by

SELECT * from Depariment,employee;

This is called the Cartesian product.

To stitch all the records together again.

Query two tables. Put two tables in a table, and then line them up.

All the records of the two tables are spliced once (if you don't understand it, you can actually look at them).

This approach is meaningless.

SELECT * from employee,department where employee.dep_id = department.id; The job is to watch the work.

This is the same part of extracting two tables. But the different parts aren't left, right?

It's going to be a magical thing to use.

INNER JOIN : Link to a virtual table with the same portion of two tables as on condition.

SELECT * from the employee inner join Department on employee.dep_id = department.id;

Translation: I check employee first, on this basis continue to check department , on the following is the condition.

SELECT * FROM Employee left JOIN Department on employee.dep_id = department.id;

The top is the left link.

Translation: The records of the left table are retained on the basis of the on condition, taking the two tables together.

SELECT * FROM Employee Right Join department on employee.dep_id = department.id;

Translation: Keep Right table

Above is the right link below said is, to the left and the side of the table are not the same all take out, called: Full link Union

SELECT * FROM Employee left JOIN Department on employee.dep_id = department.id

Union

SELECT * FROM Employee Right Join department on employee.dep_id = department.id;

Sub-query:

In is because there may be multiple

The number of enquiries is only 1 People's Department name:

Select name from department where ID in (select dep_id from Employee GROUP by DEP_ID have count (id) = 1);

MySQL Query (advanced) (each punctuation is the focus)