<p></p> <p>SELECT statement is one of the most commonly used operations in MySQL and is mainly used to query data. First, querying the data of the entire table can be achieved through SELECT * FROM users; but it is recommended to specify fields such as SELECT id, name FROM users; to improve performance. Secondly, use the WHERE clause to filter data by condition, and support operators include =, , >, </p> <p><img src="/static/imghw/default1.png" data-src="https://img.php.cn/upload/article/000/000/000/175217267120370.jpg" class="lazy" alt="mysql select query example"></p> <p> Searching data is one of the most commonly used operations in MySQL, especially the <code>SELECT</code> statement. Whether it is development, debugging or analyzing data, it is inseparable from it. The following is a few common scenarios to familiarize you with how to write practical <code>SELECT</code> queries. </p> <img src="/static/imghw/default1.png" data-src="https://img.php.cn/upload/article/000/000/000/175217267271378.jpeg" class="lazy" alt="mysql select query example"><hr> <h3 id="Query-the-data-of-the-entire-table"> Query the data of the entire table</h3> <p> If you are just coming into contact with a new table and want to see what is inside, the easiest way is to find out the entire table:</p><pre class='brush:php;toolbar:false;'> SELECT * FROM users;</pre><p> This statement returns all rows and columns in the <code>users</code> table. However, it should be noted that this is not recommended when the data volume is large, because it may lag or affect performance. In normal development, it is best to only choose the fields you need, such as: </p><img src="/static/imghw/default1.png" data-src="https://img.php.cn/upload/article/000/000/000/175217267499233.jpeg" class="lazy" alt="mysql select query example" /><pre class='brush:php;toolbar:false;'> SELECT id, name, email FROM users;</pre><p> This reduces unnecessary data transmission and is more efficient.</p><hr /><h3 id="Filter-data-by-conditional"> Filter data by conditional</h3><p> Many times we don’t need all the data, but want to find records that meet certain conditions. At this time, you have to use the <code>WHERE</code> clause. For example, find a user with a status of "Active": </p><img src="/static/imghw/default1.png" data-src="https://img.php.cn/upload/article/000/000/000/175217267514492.jpeg" class="lazy" alt="mysql select query example" /><pre class='brush:php;toolbar:false;'> SELECT id, name FROM users WHERE status = &#39;active&#39;;</pre><p> You can also add multiple conditions, such as filtering the region and status at the same time:</p><pre class='brush:php;toolbar:false;'> SELECT id, name FROM users WHERE status = &#39;active&#39; AND region = &#39;Asia&#39;;</pre><p> Common operators include: <code>=</code> , <code><></code> , <code>></code> , <code><</code> , <code>IN</code> , <code>LIKE</code> , etc. For example, users with "Tom" in their fuzzy matching names:</p><pre class='brush:php;toolbar:false;'> SELECT id, name FROM users WHERE name LIKE &#39;%Tom%&#39;;</pre><p> Note that <code>%</code> is a wildcard character, which means that any character appears multiple times.</p><hr /><h3 id="Sorting-and-limiting-the-number-of-results"> Sorting and limiting the number of results</h3><p> Sometimes you just want to see the first few results, or sort them by a certain field before checking. You can use <code>ORDER BY</code> and <code>LIMIT</code> :</p><p> For example, in reverse order of creation time, select the first 5 users:</p><pre class='brush:php;toolbar:false;'> SELECT id, name, created_at FROM users ORDER BY created_at DESC LIMIT 5;</pre><ul><li> <code>DESC</code> is descending, and <code>ASC</code> is used ascending (default)</li><li> <code>LIMIT</code> controls how many records to return at most</li></ul><p> This combination is very useful when doing paging or presenting the latest data.</p><hr /><h3 id="Re-retrieve-query"> Re-retrieve query</h3><p> If some field values ??are repeated and you want to redisplay, you can use <code>DISTINCT</code> . For example, see what are the different regions:</p><pre class='brush:php;toolbar:false;'> SELECT DISTINCT region FROM users;</pre><p> This way you can see which regions the user comes from and will not list the same region name repeatedly.</p> <hr> <p> Basically that's it. <code>SELECT</code> statements look simple, but they can complete many complex tasks after flexible matching of various clauses. At the beginning, you can practice more basic grammar and slowly learn advanced content such as connection query and aggregation functions.</p>

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