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 &amp;lt ;Field name to be queried&gt; 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:php;toolbar:false;'>mysql&gt;select id, Sum(score) from result group by id;</pre><div class="contentsignin">Copy after login</div></div><h2 id="Multiple-table-query">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 src="/static/imghw/default1.png" data-src="https://img.php.cn/upload/article/000/000/024/008b44e3868246c0f346c11aa31ad2a9-0.png" class="lazy" alt="What is the query statement of mysql database"/></p><p><img src="/static/imghw/default1.png" data-src="https://img.php.cn/upload/article/000/000/024/008b44e3868246c0f346c11aa31ad2a9-1.png" class="lazy" alt="What is the query statement of mysql database"/></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;

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

2. Connection query

1. Outer connection query

(1) Left outer connection query

Query the failing grades of students younger than 20 years old

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 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;

The filtered results of the left table must all exist

(3) Full outer join query

2. Inner join query

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;

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!