查询语句
- 查询所有字段
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)';
