SQL Select Instance Tutorial

Select tips, such as how to use in, LIMIT, CONCAT, DISTINCT, and other MySQL keywords

Note Some of the tips for select:
1. Select statements can be separated by carriage returns
$sql = "SELECT * from article where id=1"
and $sql = "SELECT * FROM article
Where id=1 can get the right results, but sometimes it may be clearer to write separately, especially if the SQL statement is longer

2. Bulk query data
can be implemented in.
$sql = "SELECT * from article where ID in (1,3,5)"

3, the use of Concat connection query results
$sql = "SELECT Concat (ID,"-", con) as res from article where id=1"
Return to "1-article content"

4. Use Locate
Usage: Select locate ("Hello", "Hello Baby"); return 1
Does not exist returns 0

5. Use GROUP BY
It's been a long time. Group BY and order by, in fact, is also full of simple, group by is the same results as a set of
Exam: $sql = "Select City, Count (*) from the customer group by city";
It means to list all the cities that are not duplicated from the Customer table and their numbers (somewhat like distinct).
Group BY is often used with AVG (), MIN (), MAX (), SUM (), COUNT ().

6, the use of having
Having allows conditionally aggregated data into groups
$sql = "Select City,count (*), Min (Birth_day) from customer
GROUP BY city has Count (*) >10 ";
This sentence is first by City group, and then find cities in the number of more than 10
BTW: Using GROUP by + has a slow speed
An expression containing a HAVING clause must appear before

7. Combination clause
Where, group BY, have, order by (if these four are to be used, generally in this sequence)

8. Use distinct
Distinct is used to remove duplicate values.
$sql = "SELECT DISTINCT city from Customer ORDER by id DESC";
It means to query all the city from the Customer table

9. Use limit
If you want to display all the 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 user and member two labels have user_name fields at the same time, there will be a MySQL error (because MySQL does not know which table you are looking for user_name),