Mysql-Query related queries

? Querying this block is the most important, which is related to the system response time. Project to achieve late, are to do performance testing and performance optimization, optimization, the database this piece is a big head.

SQL format: Select column name/* from table name where condition group BY column having condition order by column Asc/desc;

There is a problem with the order of query execution.

Single-Table query execution order:

Select Sex, COUNT (sex) as Count from Tch_teacher where ID > All GROUP by sex have count > 5 order by sex ASC limit 1;

1-> from table: first get table Tch_teacher

2-> Where Condition: Filter the data set a once according to the conditions following the where

3-> GROUP BY group: The filtered data A, grouped by the columns following group by, gets the dataset B

4-> having a filter: Further filtering data set B to get the data set C

5-> Select data: Here are four steps

The first step: according to the column name after the Select, go to Data set C to fetch the data. Get Data Set D

The second step is to redo the data in DataSet D (this step is based on the distinct case in SQL) and get the data set E

Step three: Sort the data set E to get the dataset F

Fourth step: Data set F interception data operation, to obtain the final data set (to perform the limit 10 operation)

In the multi-table, only the 1th step has changed, a more than a table operation, nothing else changes.

One, even table query

1. Cross-connect--Cartesian product crosses join

SELECT * from Tch_teacher Cross join Tch_contact

This kind of connection, no one has ever been used. If the Tch_teacher,tch_contact table has 10 data, then the result of the connection is ten x 10 = 100 data.

In MySQL, the cross join can be followed by the on and where, plus, it's the same as the inner join.

2. Inner connection--inner JOIN

The inner connection is also to seek the product of Descartes without being added. Non-on usage is not recommended, it is prone to memory overflow situations. When on, the data is filtered when the table is attached to reduce the range of valid data

From the above SQL and picture, inner join, you can not add on, you can also get a result, and the result is the same as the result of the cross join.

There are two other ways to do this:

SELECT * FROM Tch_teacher,tch_contactselect * from Tch_teacher join Tch_contact

The result is the same.

3. Outer connection--left/right join on

Here I specifically added a on top, because do not add on will be error.

The left join is referred to as the Zuo connection, and the connection is based on the left table data, and when the right table has no data to match, it is filled with null

Right joins are referred to as the left join, as opposed to left joins, which are based on the right table

Just look at the effect.

SELECT * from Tch_teacher left joins Tch_contact on Tch_teacher. Id = Tch_contact. TId;

SELECT * from Tch_teacher right joins Tch_contact on Tch_teacher. Id = Tch_contact. TId;

In fact, there is a full join, but MySQL inside the full join, really some people have no words, anyway, I have not so used, skip the table.

Here I do a little test, here's the data, Tch_contact's TID value, I'm taking a random number between 0~100000

SELECT * FROM Tch_teacher inner joins tch_contact on Tch_teacher. Id = Tch_contact. TId; select * from Tch_teacher left joins Tch_contact on Tch_teacher. Id = Tch_contact. TId;

Tch_teacher	Tch_contact	Inner JOIN (s)	Results	Left JOIN (s)	Results
100,000	100,000	0.499	99999	0.526	137017
100,000 +5000	100,000	0.345	99999	0.565	142017
100,000-5000	100,000	0.472	94949	0.534	130124

The value in this is that I repeatedly run, and in some discrete values, get closer to the center point value.

4. I even own

MySQL inside, there is a more useful function, is oneself even. I tch_teacher add a column to the table, CreateBy, storing the ID value of the table.

Select A.*, b.name as Createbyname from Tch_teacher Aleft joins Tch_teacher b on a.createby = b.ID

The following results can be obtained:

Second, union query

In addition to putting several tables into a table result through internal relationships, you can also make a table result of the query table results of multiple tables. The method used is union.

It is important to note that the order of the columns. You can rename the column names of the table results to be the same if you are afraid that the order of the columns is not consistent.

Select Id, sex, bid, ' no ', name, createdate from Tch_teacherunionselect 0 as Id, sex, bid, ' no ', name, createdate from tch _teacher_temp

Union is going to redo the final table results, and if I don't want to go heavy, just want to get the results of stitching quickly, you can use union ALL to splice.

Long press QR Code recognition concern, your support is our greatest power.

Public Number: Testing DreamWorks

QQ Group: 300897805

　　

Mysql-Query related queries