mysql单表查询语句

• 查询所有字段

select * from fruits;效率很低
select f_id, f_name, f_prise from fruits;
desc table用于显示表结构

• 查询指定字段

select f_id from fruits;

• 查询指定的数据，对数据进行过滤

select 字段名1， 数据段2，...
from 表名
where 查询条件
例子：
select f_id, f_name
from fruits
where f_price = 3.6;

select f_name, f_price
from fruits
where strcmp(f_name, 'apple') = 0;

select f_name, f_price
from fruits
where f_price < 5;

• 带有in关键字的查询

select f_name, f_price, s_id
from fruits
where s_id in(101, 102) 
order by s_id;

• 使用not in限定查询

select f_name, f_price, s_id
from fruits
where s_id not in(101, 102)
order by s_id;

• 使用between and的范围限定查询

select f_name, f_price
from fruits
where f_price between 3 and 6;

• 使用带有like的字符进行匹配查询

百分号通配符%可以匹配任意长度的字符，甚至可以包括0字符
下划线通配符_一次只能匹配任意一个字符
select f_id, f_name
from fruits
where f_name like 'b%';

select f_id, f_name
from fruits
where f_name like '%a%';

select f_name, f_price
from fruits
where f_name like 'appl_';
- 查询空值（数据未知）
select c_id, c_name 
from customers
where c_email is null;
- 带有and的多条件查询
select f_id, f_price, s_id
from fruits
where f_price > 4 and s_id = 101;

select f_id, f_name, f_price, s_id
from fruits
where f_name = 'apple' and s_id = 101 and f_price < 6;

• 带有or的多条件查询

select f_id, f_name, f_price, s_id
from fruits
where f_name = 'apple' or s_id = 102;

• 查询结果不重复

select distinct s_id 
from fruits
order by s_id;

• 对查询结果进行排序

select f_name, f_price
from fruits
order by f_name, f_price;

select f_name, f_price
from fruits
order by f_price desc;

select f_price, f_name
from fruits
order by f_price desc, f_name;

• 分组查询

常常和集合函数一起使用
【GROUP BY字段】
【HAVING <条件>】
创建分组的时候，常常和集合函数一起使用，如max、min、count、avg、sum
select s_id, group_concat(f_name) as Names, count(*) as total
from fruits
group by s_id;

select s_id, count(f_name) as total, group_concat(f_name) as Name
from fruits
group by s_id
having count(f_name) >= 2;

• 使用limit限制查询结果的数量

limit [位置偏移][指定的行数]
select * 
from fruits
limit 4;返回前4行数据

select * 
from fruits
limit 4, 3;返回从第四行数据开始的3行数据