Ask for some SQL query knowledge

Hello everyone, I understand some basic SQL knowledge, SQL query also know a little bit, a little problem has been learned not (mainly for SQL query), please point out.

Know the basic knowledge as follows:
SELECT, from, WHERE, GROUP by, ORDER by, LIMIT (partial aggregate function, non-aggregate function.)
A basic understanding of federated queries, connection queries (Union, UNION All, join, left JOIN, right join, and so on).
Have a little understanding of the order in which SQL is executed.

The most puzzling questions are as follows:
For some of the more complex queries. On the internet also browse some, for example, the complexity of simple, complex SQL is made of SQL stack, although it sounds like this truth, can self-want to write and do not know what to do.

How can I break through, there are no related posts or documents or books, please send us. Thank you.

Reply to discussion (solution)

How to a bit of putting the cart before the horse, isn't your data organized according to the convenience of the query?

The simple point is that you can understand this:

If it is a single-table query, it is no problem to write.
If it is a complex point, the SQL of several table queries is sometimes impossible to write.
In fact, I also know that it is a union query, connection query, sub-query, and then apply the relevant SQL aggregate functions, non-aggregate functions, GROUP by, and so on. For complex business logic I don't quite understand when to use the group by and when to use a connection query.

Connection query: Because of the relationship between data, you divide the data into multiple tables according to the degree of relevance. Therefore, when the data to be obtained is distributed in two or more tables, it is necessary to concatenate the first table into a large table

Group: Grouping, when you need to examine the mathematical characteristics of similar data, you need to group (count, Sum, average ...) ）

It seems a little bit enlightened. Thank you moderator.
Other friends also ask for more advice.

There is no shortcut, read the document more practice.

Although there is no systematic to read, but also often on the forum dive into the water, swollen level of ascension so slow it. Isn't my IQ a little swollen?

Those basic very good understanding, is only a matter of time, many complex can only be in the process of consulting the birds slowly accumulate.

For example:

INSERT INTO IMAGE_TB (images,link)
Select CONCAT (Coalesce ((select MAX (ID) + 1 from IMAGE_TB), 1), '. jpeg '), '. $link. '
from IMAGE_TB

This is clear.


and more like

SELECT * from
(SELECT @rn: =if (@pv =source, @rn +1, 1 As RId,
' A ' t_name,id,title, @pv: =source,description,date,fullindex
from table1 A1
JOIN (SELECT @pv: = 0, @ RID:=0) TMP
where MATCH (fullindex) against (' +bool ' in BOOLEAN MODE)
ORDER by source, date) a
WHERE A.rid &L t;=5;

Why do I have @ If, I don't understand it yet.

Look at the manual is the best

@:
Http://dev.mysql.com/doc/refman/5.0/en/user-variables.html

If
Http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html


Those basic good understanding, is only a matter of time, many complex can only be in the process of consulting the birds to accumulate slowly.

Like what:

Insert into IMAGE_TB (Images,link)
Select CONCAT (Coalesce ((SELECT MAX (ID) + 1 from IMAGE_TB), 1), '. jpeg '), '. $link. '
From IMAGE_TB

This one's got it figured out.


Another example

SELECT * FROM
(SELECT @rn: =if (@pv =source, @rn +1, 1) as RIDs,
' A ' t_name,id,title, @pv: =source,description,date,fullindex
from table1 A1
JOIN (SELECT @pv: =0, @rId: =0) tmp
WHERE MATCH (Fullindex) against (' +bool ' in BOOLEAN MODE)
ORDER by source, date) a
WHERE A.rid <=5;

Why there is @ if, still do not understand now.

A lot of practice projects, when you do the project to meet the needs of you will have a profound experience ...

Look at the manual is the best

@:
Http://dev.mysql.com/doc/refman/5.0/en/user-variables.html

If
Http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html

English manual, look slowly.

The basic problem or learn to find information to see it.

Generally use data management to deal with problems, too many queries, will cause the server is too slow.
Like what
Administrator table ID, user name, password, group ID
Manage Group table ID, group name, permissions

... I would normally build an index table.

Index Administrator ID, administrative group ID
In this way, you will reduce the number of queries in the process of use.
In terms of time complexity, you can subtract a second-party

There is a good book, "Database principles and application of the tutorial," There is a detailed explanation of database operations.

It's good to find some open source projects.

