MYSQL's select study notes
- 2020-05-06 11:47:21
- OfStack
Some tips for recording select:
1. select statements can be separated by a carriage return $sql="select * from article where id=1"
$sql="select * from article
where id=1" gives the correct result, but sometimes it may be clearer to write separately, especially
when sql is longer
2. Batch data query
can be implemented with in $sql="select * from article where id in(1,3,5)"
3. Results of queries using concat connection
$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"); Return 1
Does not exist returns 0
5. Use group by
group by and order by, which is quite simple, group by is the same result as a group of
exam: $sql="select city ,count(*) from customer by city";
What this means is to list all the non-repeating cities from the customer list, and their number (somewhat similar to distinct)
group by is often used with AVG(),MIN(),MAX(),SUM(),COUNT()
6. Use having
having allows conditional aggregation of data into groups
$sql="select city,count(*),min(birth_day) from customer
group by city having count(*) > 10";
This sentence is first grouped by city, and then find out the number of city more than 10 cities
btw: using group by + having is a bit slow
Also, the having clause must contain an expression that appears before
7. Composition clause
where, group by, having, order by (generally in this order if all four are to be used)
8. Use distinct
distinct is
for getting rid of duplicate values $sql="select distinct city from customer order by id desc";
This means querying all non-repeating city
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 user and member both have user_name fields, mysql errors will occur (because mysql does not know which table you are querying for user_name), you must specify which table.
1. select statements can be separated by a carriage return $sql="select * from article where id=1"
$sql="select * from article
where id=1" gives the correct result, but sometimes it may be clearer to write separately, especially
when sql is longer
2. Batch data query
can be implemented with in $sql="select * from article where id in(1,3,5)"
3. Results of queries using concat connection
$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"); Return 1
Does not exist returns 0
5. Use group by
group by and order by, which is quite simple, group by is the same result as a group of
exam: $sql="select city ,count(*) from customer by city";
What this means is to list all the non-repeating cities from the customer list, and their number (somewhat similar to distinct)
group by is often used with AVG(),MIN(),MAX(),SUM(),COUNT()
6. Use having
having allows conditional aggregation of data into groups
$sql="select city,count(*),min(birth_day) from customer
group by city having count(*) > 10";
This sentence is first grouped by city, and then find out the number of city more than 10 cities
btw: using group by + having is a bit slow
Also, the having clause must contain an expression that appears before
7. Composition clause
where, group by, having, order by (generally in this order if all four are to be used)
8. Use distinct
distinct is
for getting rid of duplicate values $sql="select distinct city from customer order by id desc";
This means querying all non-repeating city
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 user and member both have user_name fields, mysql errors will occur (because mysql does not know which table you are querying for user_name), you must specify which table.