MySQL SELECT tips

The following articles mainly describe how to use MySQL SELECT. The percentage of MySQL SELECT applications in practice is still the majority. If you are curious about this new technology, the following articles will unveil its mysteries.

Record some select techniques, such as how to use MySQL keywords such as IN, LIMIT, CONCAT, and DISTINCT

Record some select tips:

1. select statements can be separated by carriage return.

$ SQL = "select * from article where id = 1"

And $ SQL = "select * from article

Where id = 1 "can get the correct results, but sometimes it is clearer to write separately, especially when the SQL statement is relatively long.

2. batch query data

It can be implemented using in.

 
 

  
  
$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: select locate ("hello", "hello baby"); 1 is returned.

0 is returned if no data exists.

5. Use group

I have never figured out group by and order by in the past. In fact, group by is simple. group by makes the same results into a group.

 
 

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

This statement is used to list all non-duplicate cities from the customer table, and its quantity is similar to distinct)

Group by is often used with AVG (), MIN (), MAX (), SUM (), COUNT ().

6. Use having

Having allows conditional data aggregation as a group

 
 

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

First, group by city, and then find the cities with more than 10 cities.

Btw: using group by + having is slow.

At the same time, the expression contained in the having clause must have appeared before

7. Combined clause

Where, group by, having, and order by are 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 statement queries all non-duplicate cities from the customer table.

9. Use limit

If you want to display all records after a 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>=2006-12-28  
  
  
order by u.id desc" 
 
 

Note: If the user and member are marked with the user_name field, a mysql error may occur because mysql does not know which table you want to query user_name. You must specify which table it belongs;