Simple usage of mysql select

Simple usage of 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 data query 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:

Select locate ("hello", "hello baby"); 1 is returned.

 

No result is returned. 5. group by has never been used to specify group by and order by. In fact, group by is simple. group by makes the same result into a group.

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

 

This statement lists all non-duplicate cities in the customer table and their quantity (similar to distinct). group by is often used with AVG (), MIN (), MAX (), SUM (), COUNT () are used together. 6. havinghaving allows conditional data aggregation as a group.

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

 

In this case, group by city first, and then find out the cities with the number of cities greater than 10 btw: the speed of using group by + having is a little slow. At the same time, the expressions contained in the having clause must appear before. 7. The combination clause where, group by, having, and order by must be used., usually arranged in this order) 8. When distinctdistinct is used, duplicate values are removed.

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

 

This statement queries all non-duplicate cities from the customer table, and uses limit 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  mwhere u.id=m.id and m.reg_date>=2006-12-28order by u.id desc"

 

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