MySQL select tips

The following articles mainly describe how to use MySQL select techniques. They mainly record some practical usage skills of select statements, such as correct use of IN, LIMIT, and actual operations such as CONCAT and DISTINCT, the following describes the specific operation steps of the document.

Tips for recording MySQL select:

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. MySQL select tips: 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

The above content is an introduction to the MySQL select technique. I hope you will gain some benefits.