查询语句

• 查询所有字段
  select *from fruite;
• 查询单个字段
  select f_id, s_id, f_name, f_price from fruite;
• 查询指定记录
  select f_name, f_price from fruite where f_price = 10.2;
• 查询s_id 为101和102的数据
  select s_id, f_name, f_price from fruite where s_id in (101,102) order by f_name;
• 查询s_id不等于101，102的记录
  select s_id, f_name, f_price from fruite where s_id not in (101,102) order by f_name;
• 查询价格在2.00-10.20的记录
  select f_name, f_price from fruite where f_price between 2.00 and 10.20;
• 查询价格不在2.00-10.20的记录
  select f_name, f_price from fruite where f_price not between 2.00 and 10.20;
• like运算符匹配
  select f_id, f_name from fruite where f_name like 'b%';
• 下划线通配符 like '_ _ _ _e' 查询结果apple
  select f_id, f_name from fruite where f_name like '____e';
• 查询字段为空的
  select f_id, f_name from fruite where f_email is null;
• 带and的多条件查询
  select f_id, f_price, f_name from fruite where s_id = '101' and f_price >=5;
• 带OR的查询
  select s_id, f_name, f_price from fruite where s_id = 101 or s_id = 102;
• 查询结果不重复
  select distinct s_id from fruite;
• 排序order by
  select f_name, f_price from fruite order by f_name, f_price;
• 降序排序
  select f_name, f_price from fruite order by f_price desc;
• 分组查询
  select s_id, count(*) as total from fruite group by s_id;
  
• 分组将结果拼接
  select s_id, group_concat(f_name) as name from fruite group by s_id;
  
• 过滤分组
  select s_id, group_concat(f_name) as names from fruite group by s_id having count(f_name) > 1;
  
• group by 语句中用with rollup
  select s_id, count(*) as total from fruite group by s_id with rollup;
  
  最后一行显示总和
• limit 限制结果数量
  select *from fruite limit 3;
  返回第三条记录之后的一条记录
  select * from fruite limit 3, 1;
• 集合函数count个数
  select count(*) as num from fruite;
• 集合函数sum总和
  select SUM(s_id) as sum from fruite;
• 集合函数平均值
  select avg(s_id) as avg from fruite;
• 集合函数查询最大值
  select max(f_price) as max_p from fruite;
• 集合函数最小值
  select min(f_price) from fruite;
• 普通连表查
  select suppliers.s_id, s_name, f_name, f_price from fruite, suppliers where fruite.s_id = suppliers.s_id;
• 内连接连表查
  select suppliers.s_id, s_name, f_name, f_price from fruite inner join suppliers on fruite.s_id = suppliers.s_id;
  
  内连接
• 外连接右连接 right outer join on
  select suppliers.s_id, s_name, f_name, f_price from fruite right outer join suppliers on fruite.s_id = suppliers.s_id;
• 左连接 返回左表的数据
  select suppliers.s_id, s_name, f_name, f_price from fruite left outer join suppliers on fruite.s_id = suppliers.s_id;
• 复合查询
  select fruite.f_name, suppliers.s_name from fruite inner join suppliers on fruite.s_id = suppliers.s_id;

子查询(没写完，还有几种)

• ANY 大于任意一个数既返回
  select num1 from tab11 where num1 > ANY (select num1 from tab12);
• ALL 满足所有条件
  select num1 from tab11 where num1 > ALL (select num1 from tab12);
• exists 里层要返回0行，外层不查询
  select * from fruite where exists (select s_name from suppliers where s_id = 107);

union 合并查询结果

select s_id, f_name, f_price from fruite where f_price < 9.0 union select s_id, f_name, f_price from fruite where s_id in (101, 103);

为表取别名 as关键字

select * from fruite as ff where ff.s_id = 101;

使用正则表达式查询

• 查询以a开头
  select * from fruite where f_name regexp '^a';
• 查询以y结尾
  `select * from fruite where f_name regexp 'y$';
• 查询a与g之间只包含一个字母
  select * from fruite where f_name regexp 'a.g';
• b开头后面有a的
  select * from fruite where f_name regexp '^ba*';
• 开头后面有a的a最少出现一次
  select * from fruite where f_name regexp '^ba+';
• 匹配字符串的任意一个
  select * from fruite where f_name regexp '[ot]';
• 查询ba出现至少一次，最多三次的记录
  select * from fruite where f_name regexp 'ba(1,3)';