What is the query statement of mysql database

Query statement: 1. "select * from table name;" can query all the data in the table; 2. "select field name from table name;" can query the data of the specified field in the table; 3. "Select distinct field name from table name" can perform deduplication query on the data in the table.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

Single table query

1. Ordinary query

(1) Command: select * from <table name>/ /通PI<p>(2) Command: <code>select <field to be queried> from <table name>;<p><strong>2 , Deduplication query (distinct) </strong></p> <p>Command: <code>select <strong>distinct</strong> <field to be queried> from <table name> <p><strong>3. Sorting query (order by) </strong></p> <p>Ascending order: asc</p> <p>Descending order: desc</p> <p>Descending order command: <code>select &lt ;Field name to be queried> from <table name> order by <field name to be queried> desc<p><strong>If desc is not added, the default is ascending order</strong> </p> <p><strong>4. Group by (group by)</strong></p> <p>Command: <code>select <group by>, Sum(score) from <table name> group by <group by what><p> Suppose there is another student score table (result). Request to query a student's total score. We divided them into different groups based on their student numbers. </p> <p>Command: </p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:js;toolbar:false">mysql&gt;select id, Sum(score) from result group by id;</pre><div class="contentsignin">Copy after login</div></div><h2>Multiple table query</h2><p><span style="font-size: 18px;"><strong>1. Equal value query</strong></span></p><p>Now there are two Table: </p><p><img alt="What is the query statement of mysql database" src="https://img.php.cn/upload/article/000/000/024/008b44e3868246c0f346c11aa31ad2a9-0.png"/></p><p><img alt="What is the query statement of mysql database" src="https://img.php.cn/upload/article/000/000/024/008b44e3868246c0f346c11aa31ad2a9-1.png"/></p>##Now we want to<p>query the failing grades<strong> of students younger than 20 years old. </strong></p><p> Statement: <code>select stu.id,score from stu,result where stu.id = result.id and age < 20 and score < 60;

Its query is as shown below:

It can be seen that the equivalent query efficiency is too low

2. Connection query

1. Outer connection query

(1) Left outer connection query

Assume we are still using the two tables above, and

Query the failing grades of students younger than 20 years old

We use left outer join to query, first Take out all the students whose age is less than 20 years old in the student table, and then take out all the students whose grades are less than 60 in the score table, and then match them. We will find that the efficiency is greatly improved, and we can find them by matching only four times.

As shown in the figure below:

The statement is:

select a.id,score
from
(select id,age from stu where age < 20) a (过滤左表信息)
left join
(select id, score from result where score < 60) b (过滤右表信息)
on a.id = b.id;

The left outer join is

The filtered results of the left table must all exist. If there is filtered data in the left table and there is no match in the right table, NULL will appear in the right table;

(2) Right outer join query

select a.id,score
 from
 (select id,age from stu where age < 20) a (过滤左表信息)
 right join
 (select id, score from result where score < 60) b (过滤右表信息)
 on a.id = b.id;

Left outer join is

The filtered results of the left table must all exist

As shown in the figure:

We found that the filtered table There are only two matching conditions that meet the condition (red means the condition is met), but the final result is:

The unmatched data in the left table is changed to empty, and the right table is filtered out All data must exist.

(3) Full outer join query

combines left outer join and right outer join, so that the data in both the left table and the right table exists.

2. Inner join query

Only filter matching results

For example, the filtered results are as follows:

The final result is:

Only matches the results we need

The statement is:

select a.id,score
 from
 (select id,age from stu where age < 20) a (过滤左表信息)
 inner join
 (select id, score from result where score < 60) b (过滤右表信息)
 on a.id = b.id;

[Related recommendations:

mysql video tutorial】

The above is the detailed content of What is the query statement of mysql database. For more information, please follow other related articles on the PHP Chinese website!