Mysql advanced 1

When we use php to process data, we will always encounter some troublesome things, such as: Two-dimensional arrays, one containing the content of the article classification table and the other containing the article list, it has an associated field, which is fully equivalent. It is required that the column name be displayed before the title when listing the article. In this case, the article list will be circulated, the column list will be repeated in the loop, and the associated fields will be compared, the same is the column where this article is located. The column name is used to show the practice of php. If there are many articles or many columns, for example, there are 100 columns in 100 articles, the number of loops will be 10 thousand. In this case, you can use mysql to perform computation. This is a two-table join query. The basic syntax is as follows:

Select Table 1. Field, Table 2. Field from table 1, Table 2 where Table 1. Joined field = TABLE 2. Joined field, perform a test to create the following table:

Column table structure menu

Tid int (10)

Name varchar (32)

 

Article Table Structure article

Wid int (10)

Tid int (10)

Title varchar (100)

Content text

Execute the following statement:

SELECT 'menu '. 'name', 'Article'. 'id', 'Article'. 'title'

FROM 'Article', 'menu'

WHERE 'Article'. 'tid' = 'menu '. 'tid'

 

I only show the topic name, Article ID, and title. If this result is output .. That saves time. A loop is enough.

The result is as follows.

When the php computing volume is large, handing over some operations to mysql will greatly reduce the php workload. When the data volume is large, mysql will obviously hurry up, except for double table join queries, mysql also provides another method called comparative query. The syntax is as follows:

SELECT Table 1. Field, Table 2 Field

FROM table 1

Left join table 2 ON table 1. Joined field = TABLE 2. Joined Field

Used here

The left join keyword returns all rows from the LEFT table (table_name1), even if no matching row exists in the right table (table_name2.

The principle used here is totally different from the above

The principle is: Compare query, which is based on one of the tables. If it is right, table 2 is the benchmark.

Two-table join query: When two tables can be associated with each other. To meet the query conditions.

If inner join is used, the join result is basically the same as that of the two tables.

If the boss wants to know how many actual transactions and the transaction volume of each user after a period of operation in a mall, he wants to know how many transactions will be generated. select count (*) it is difficult to calculate the from table name but know how many users have traded, and how many transactions each user has traded separately. In this case, you can use the grouping function, the keyword is "group by". In the previous table test, we want to know the number of articles in each topic, which is equivalent to the number of transactions produced by each user,

Select 'tid', count (*) from 'Article' group by 'tid'

Grouping query is equivalent to stacking the same data of the specified fields in the database. In this case, if count is used, not a total number of groups are returned. The number of data records in each group.

If we have the id of several articles in hand, if we have the id of several articles in hand, for example, we have 3 6 9 three IDs in hand.

As shown in the previous article. How should I check it. To make them appear in a result.

Someone may think of this.

Select * from 'Article' where 'wid' = 3 or 'wid' = 6 or 'wid' = 9

If so, there will be a new problem. For PHP, it is more appropriate to write a lot or use in at this time,

In syntax format

SELECT column_name (s)
FROM table_name
WHERE column_name IN (value1, value2 ,...)

If there are not many values to be queried, it is easy to use this method. If you want to query the value, there are thousands of data records. That's another thing .. Subquery.

Let me make a hypothesis first. If we have thousands of members, I just want to view the transaction records of the first 20 members. In this case, you can query the data by comparison,

The statement for querying user tables is probably like this.

Select * from 'user' where 'vip '= 1

For example, the transaction table is named shop shopping table, and both tables have the username field. We can use subqueries. Assume that the first query result is a table and the vip table.

If you use the query result just now. The syntax is like this.

Select * // return all fields

From 'shop' // query from the transaction table

Right join 'vip '// The right side, that is, the vip table.

On 'vip '. 'username' = 'shop'. 'username' as long as the vip user's transaction record is used. Therefore, some shop data in the left table is not required. Of course, the right table prevails.

Select * from 'user' where 'vip '= 1

 

The right table is the query result of this statement.

Select *

From 'shop'

Right join

(Select * from 'user' where 'vip '= 1) as 'vip'

On 'vip '. 'username' = 'shop'. 'username'

(Select * from 'user' where 'vip '= 1) as 'vip'

Regard the query result of this statement as a table and name it vip temporarily

In this way, the transaction records found from the shop table. The "virtual table" of vip is used for selective output.

The result is of course the transaction records of all VIP users. This is called a subquery. In one query statement, there is another query statement. Of course, there is another way of writing that can also be done without comparing queries.

Prepare our subquery statements first. The username of all VIPs.

Select 'username' from 'user' where 'vip '= 1

 

Select * from 'shop 'where 'username' = 'one username'. You can obtain all the transaction records of a user from the transaction table. If the two statements are merged.

Select * from 'shop 'where 'username' = (select 'username' from 'user' where 'vip '= 1)

This is another usage of subqueries.

In this way, the results returned by the subquery must have only one field.

Select * from 'Article'

Where 'tid' in

(Select 'tid' from 'menu 'where 'tid'> 1)

When the data volume is large .. Select these "a little complicated" SQL statements, and let the database that processes the data "more professional" Calculate the results to us. Often, you can get twice the result with half the effort.

I complained that it would be hard to work as a qualified programmer ..