Ask for some SQL query knowledge

I would like to ask you some knowledge about SQL queries. I have some basic SQL knowledge and a little knowledge about SQL queries. I have never learned any questions (mainly for SQL queries ), please give us some advice.

The basic knowledge is as follows:
SELECT, FROM, WHERE, group by, order by, LIMIT (partial aggregate function, non-aggregate function.
Basic knowledge about joint queries and connection queries (union, union all, join, left join, right join, etc ).
I have some knowledge about the SQL execution sequence.

The most confusing problem is as follows:
You can't start with complicated queries. I also browsed some information on the internet. for example, complex SQL statements are made up of simple SQL stacks. Although it sounds like this, I can't help myself when I want to write it.

How can I make a breakthrough? are there any related posts OR documents OR books? please send them to me. Thank you.

Reply to discussion (solution)

Why is it that your data is not organized for easy query?

The following is a simple example:

If it is a single table query, it is no problem to write it.
Sometimes, the SQL statements used to query multiple tables with complex points cannot be written.
In fact, I also know that it is nothing more than joint query, connection query, subquery, and then use related SQL aggregate functions, non-aggregate functions, GROUP BY and so on. For complex business logic, I don't know when to use GROUP BY or connection query.

Join query: because of the relationship between data, you divide the data into multiple tables based on the degree of relevance. Therefore, when the data to be obtained is distributed in two or more tables, you need to connect the first table to a large table.

GROUP: grouping. when you need to evaluate the mathematical features of similar data, you need to GROUP (count, sum, average ...)

It seems a little enlightened. Thank you, moderator.
Please give more advice to other friends.

There is no shortcut. read documents and practice more.

Although there is no system to read a book, I often go to the forum to dive into the water. is the level of swelling so slow. Isn't my IQ a little swollen.

The basic understanding is just a matter of time. many complicated tasks can only be accumulated slowly during the process of asking for advice from laruence.

For example:

Insert into image_tb (images, link)
Select CONCAT (COALESCE (select max (ID) + 1 FROM image_tb), 1), '.jpeg '),' ". $ link ."'
From image_tb

I already understand this.


For example

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)
WHERE a. rId <= 5;

Why is there @ if? I haven't understood it yet.

Reading 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


The basic understanding is just a matter of time. many complicated tasks can only be accumulated slowly during the process of asking for advice from laruence.

For example:

Insert into image_tb (images, link)
Select CONCAT (COALESCE (select max (ID) + 1 FROM image_tb), 1), '.jpeg '),' ". $ link ."'
From image_tb

I already understand this.


For example

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)
WHERE a. rId <= 5;

Why is there @ if? I haven't understood it yet.

Exercise a lot of projects, and you will have a deep understanding when you meet your needs...

Reading 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

Take a look at the English manual.

If you encounter basic problems, learn more about the information.

Data management is generally used to handle problems. if too many queries are made, the server will be slow accordingly.
For example
Administrator table id, user name, password, group ID
Management Group table id, group name, permission

... I usually want to create an index table.

Index administrator ID and management group id
In this way, you will reduce the number of queries during use.
In terms of time complexity, we can subtract one power.

There is a good book, "database principles and application tutorials", which provides a detailed description of database operations.

It's good to find some open-source projects.