MYSQL select Study Notes
MYSQL select Study Notes
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 lists all non-duplicate cities and their quantities from the customer table (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 (if the four are to be used, they are generally 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 will occur (because mysql does not know which table you want to query user_name). You must specify which table it belongs;
