10 simple usage examples of MySQL select statements

This article mainly talks about a simple analysis of MySQL SELECT syntax. We all know that MySQL database is a database that we all often use, and its related applications are also of great concern. So the following article is mainly about MySQL SELECT A simple analysis of syntax.

1. Select statements can be separated by carriage returns

$sql="select * from article where id=1"
和
$sql="select * from article
where id=1"，都可以得到正确的结果，但有时分开写或许能更明了一点，特别是当sql语句比较长时

2. Batch query data

can be separated by carriage returns inTo achieve

$sql="select * from article where id in(1,3,5)"

3. Use concat to connect the query results

$sql="select concat(id,"-",con) as res from article where id=1"

Return "1-article content"

4. Use locate

Usage: <br/>

select locate("hello","hello baby");

Returns 1 if it exists, 0 if it does not exist

5. Use group by

group by is to group the same results into a group<br/>

exam：$sql="select city ,count(*) from customer group by city";

This sentence means to list all non-duplicate cities from the customer table and their number (somewhat similar to distinct)
group by often with AVG(), MIN(), MAX(), SUM(), COUNT() are used together

6. Using having

having allows conditional aggregation of data into groups

$sql="select city,count(*),min(birth_day) from customer
group by city having count(*)>10";

This sentence is to group by city first, and then find the cities with more than 10 cities
btw: using group by + having is a bit slow
At the same time, the expression contained in the having clause must be before Appeared

7, combination clause

where, group by, having, order by (if all four are used, they are usually arranged in this order)

8. Use distinct

distinct is used to remove duplicate values

$sql="select distinct city from customer order by id desc";

This sentence means to query all non-duplicate cities from the customer table

9. Use limit

If you want to display all records after a certain record

$sql="select * from article limit 100,-1";

10. Multi-table query

$sql="select user_name from user u,member m
where u.id=m.id and
m.reg_date>=2015-08-13
order by u.id desc"

Note: If both user and member have user_name fields, A mysql error will occur (because mysql does not know which table you want to query for user_name), and you must specify which table;

The above is the detailed content of 10 simple usage examples of MySQL select statements. For more information, please follow other related articles on the PHP Chinese website!