MySQL (ix) Data Sheet query detailed (select syntax) two

On a relatively simple single-table query and MySQL group functions, this one to share a bit more rare knowledge, about multi-table query, sub-query, left connection, external connection and so on. I hope we can all get help!

Before you begin because you want to query more tables, set up a good environment:

1) Create data table Suppliers

There is already a table in front of the Book table, we are building a suppliers (supplier) table and the previous Book table corresponds.

This means that the s_id field value in book points to the primary key value of suppliers and creates a foreign key constraint relationship.

　　

In fact, there is no real foreign key constraint relationship, just simulation, let fruits in the s_id value can match to suppliers in the primary key value, by manually adding this data, to achieve this relationship.

Anyway is dead data, also not add other data, do not have to establish a foreign key constraint relationship, here to figure out.

2) inserting data

one, multi-table query 　　 1.1. Take aliases

1) Alias the table

Because the two tables are queried, it is a bit cumbersome to write the table name each time, so use a simple alias to represent the table name

Format: Table name as Alias

2) Alias the field

The name of the field is aliased because some field names are displayed for clearer

Example: Select B_price as ' Price ' from the book;

　　　　　　

　　 1.2, ordinary double table query

Requirement: The number of the inquiry, the name of the book, the wholesaler number of the book, the name of the wholesaler of the book

Analysis: Look at the requirements, you know to query two tables, if you need to query two tables, then the relationship between the two tables must be a foreign key relationship, or similar to the foreign key relationship (similar to that is to say two tables do not really add foreign key constraints,

but its characteristics are the same as the foreign key, just like the two tables we manually created above, although the Foreign Key association is not set, but its characteristics are the same as the foreign key relationship . )

Select  from  as  as where b.s_id=s.s_id;

　　　　　　

Attention: So the table is aliased above Can be used anywhere in the statement

1.3, inner join query

After understanding the above two tables basic connection query,

format: Span style= "color: #ff0000;" "Table name inner join table name

need:: Query the number of the book, the name of a book, the number of wholesalers, the name of the wholesaler of the book (this is the same as above, we look at the grammatical difference)

select  b.b_id,b.b_price,s.s_id,s.s_name from book as b inner joins suppliers as s on b.s_id=s.s_id;

　　　　　　

In fact, there is a natural connection: the two tables involved are the same table.

Example: View other books supplied by a vendor with a book ID of G2?

Select B2.b_id,b2.b_name from book as B1 inner join book as B2 on b1.s_id=b2.s_id and b1.b_id= ' G2 ';

　　　　　　　

Analysis: The Book table is divided into two identical tables, in the B1 table to find b_id= ' G2 ' s_id, and then to B2 this table to find and the s_id equal records, also query out the results required by the problem.

There is another way, without internal connection query, through sub-query can also be done , the following will explain, here first give the answer, you can look back to see the problem.

Select B_id,b_name from book where s_id= (select s_id from book where b_id= ' G2 ');

And the result is the same as above.

1.4, external connection query

An inner join is a return of a row that conforms to a query condition (which conforms to the join condition), that is, the associated row .

a row that has no association is also displayed, except that the associated row is returned by an outer connection.

Why do I have to show the rows that are not unrelated? This is based on different business needs, for example, order and customers, customers can have orders and can not order, now need to know all the customer's orders, and we can not only find the order of users,

And put the user without the order is not displayed, this is not in line with our business needs, some people say, since we know that there are orders of customers, through a single-table query out of these orders are not included in the customer, can not meet our requirements, so it is possible, but very troublesome, how can it be displayed together and not so troublesome? In order to solve this problem, there is an external connection to query this thing.

1.4.1, left outer connection

Format: table name left JOIN table name on condition;

Returns records that include all the records in the left table and the join fields in the right table, in Layman's words, in addition to displaying the associated rows, all record rows in the left table are also displayed.

Simply put: connect two tables, the query results contain all the data from the table on the left and the data related to the table on the right and the left table .

Example: To demonstrate our effect we add two data to suppliers

　　　　　　

Select S.s_id,s.s_name,b.b_id,b.b_name

From suppliers as S left join book as B

On s.s_id=b.s_id;

　　　　

Analysis: The Suppliers table is on the left side of the leftmost join, so all of its records are displayed, and there are no associated items for the associated item. This is the meaning of the left outer join, showing all the records on the left side of the table (provided that the fields we need are

That is, the selected field after select). If you put the Suppliers table to the right of the left join, then the two records of 80 and 90 are not displayed. To see

　　　　　　

1.4.2, right outer connection

Format: table name right JOIN table name on condition

　　　　returns records that include all records in the right table and the connection fields in the right table . In fact, with the left outer connection is similar, is the right side of the table to show all

　　　　

1.5. Compound condition Query

In the process of connecting queries (inner joins, outer joins), by adding filter conditions to limit the results of the query, the results of the query more accurate, popular point, is to connect query conditions more refined.

1) Use the INNER JOIN syntax in the book and Suppliers tables to query the supply information for suppliers s_id 70 in the Suppliers table?

Select S.s_id,s.s_name,b.b_id,b.b_name

From book as b inner join suppliers as S

On s.s_id=b.s_id and s.s_id=70;

　　　　　　

2) between the fruits table and the Suppliers table, use the INNER JOIN syntax for INNER JOIN queries and sort the query results

Select S.s_id,s.s_name,b.b_id,b.b_name

From book as b inner join suppliers as S

On s.s_id=b.s_id order by b.s_id; The b.s_id is ascending. The default is ASC, so do not write.

　　　　　　

Second, sub-query

subqueries, queries the results of one table to act as conditions for another query, so that nested queries are called subqueries.

Build the Environment:

Table tb1: Table TB2:

2.1. Sub-query with any, SOME, all keyword

　　　　

1) Theany keyword is followed by a comparison operator that returns true if any value returned from the subquery is true, and in layman's terms returns trueif any of the conditions are met.

　　　　　　The Some keyword is the same as the usage of the ANY keyword .

Example: Select Num1 from tb1 where num1> any (select num2 from TB2); This is where the results of the query in the TB2 table are placed in the previous query statement as a condition parameter . As long as the NUM1 is larger than any one of its results, the match is even.

　　　　　　

2) The allkeyword indicates that all conditions need to be met simultaneously

Select Num1 from tb1 where Num1> all (select num2 from TB2); NUM1 needs to be larger than all query results to match

　　　　　　　　

　　 2.2, with exists keyword query

The argument after the EXISTS keyword is any subquery, or true if the subquery has a return record row, or False if the subquery does not return any record rows, and the outer query statement will not be queried.

　　　　

　 2.3. Subqueries with comparison operators

In addition to using the keyword all, any, some, and so on, you can also use normal comparison operators. To compare them. For example, when we explain the internal connection query, we used the subquery, and still use = the comparison operator.

Third, the merger query3.1. Union keyword

Merges the result set, stitching together multiple result sets . Merging only focuses on the same number of columns, not on the data type . However, it is best not to merge columns with different data types without special requirements .

When merging with different data types, the data type of the column after the merge is a varchar type. when merging, the duplicate rows are eliminated, and the duplicate rows are not eliminated, and union all is used.

　　　The Union keyword allows you to merge the results of a query into a single result set, which is to concatenate multiple SELECT statements through the Union keyword, noting that merging result sets only increases the records in the table, not increasing the fields in the table, merely merging the record rows together. The fields they display should be the same, or they cannot be merged .

3.2. Use of Union[all]

UNION: does not use the keyword all, when execution deletes duplicate records, all returned row degrees are unique,

UNION all: Do not remove duplicate navigation and do not automatically sort the results.

Format:

SELECT Field name,... From table name

Union[all]

SELECT Field name,... From table name

3.3. Example

1) Query book price is less than 50, query s_id for 50 or 70 of the book information, using the Union

　　　　　　

With union instead of union all, duplicate records are deleted.

Iv. using regular expression queries

Use the RegExp keyword to specify a regular expression, and draw a table to cover all of the following degrees.

　　

1) query for records beginning with a specific character or beginning of a string

SELECT * FROM book where B_name REGEXP ' ^j '; Records starting with J

　　　　　　

Note: The only difference is that the regular expression is different, generally using this fuzzy query, the use of MySQL ' _ ' and '% ' is enough.

2) query for records that end with a specific character or string

3) Use the symbol "." To replace any one of the characters in the string

4) Use "*" and "+" to match multiple characters

5) match the specified string

6) matches any one of the specified characters

7) Match characters other than the specified character

8) Use {N,} or {n,m} to specify the number of consecutive occurrences of a string

Feel good big guy point a "recommended" "Oh!"

MySQL (ix) Data Sheet query detailed (select syntax) two