查詢語句

• 查詢所有字段
  select *from fruite;
• 查詢單個(gè)字段
  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的數(shù)據(jù)
  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;
• 查詢價(jià)格在2.00-10.20的記錄
  select f_name, f_price from fruite where f_price between 2.00 and 10.20;
• 查詢價(jià)格不在2.00-10.20的記錄
  select f_name, f_price from fruite where f_price not between 2.00 and 10.20;
• like運(yùn)算符匹配
  select f_id, f_name from fruite where f_name like 'b%';
• 下劃線通配符 like '_ _ _ _e' 查詢結(jié)果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;
• 查詢結(jié)果不重復(fù)
  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;
  
• 分組將結(jié)果拼接
  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 限制結(jié)果數(shù)量
  select *from fruite limit 3;
  返回第三條記錄之后的一條記錄
  select * from fruite limit 3, 1;
• 集合函數(shù)count個(gè)數(shù)
  select count(*) as num from fruite;
• 集合函數(shù)sum總和
  select SUM(s_id) as sum from fruite;
• 集合函數(shù)平均值
  select avg(s_id) as avg from fruite;
• 集合函數(shù)查詢最大值
  select max(f_price) as max_p from fruite;
• 集合函數(shù)最小值
  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;
• 內(nèi)連接連表查
  select suppliers.s_id, s_name, f_name, f_price from fruite inner join suppliers on fruite.s_id = suppliers.s_id;
  
  內(nèi)連接
• 外連接右連接 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;
• 左連接 返回左表的數(shù)據(jù)
  select suppliers.s_id, s_name, f_name, f_price from fruite left outer join suppliers on fruite.s_id = suppliers.s_id;
• 復(fù)合查詢
  select fruite.f_name, suppliers.s_name from fruite inner join suppliers on fruite.s_id = suppliers.s_id;

子查詢(沒寫完，還有幾種)

• ANY 大于任意一個(gè)數(shù)既返回
  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 合并查詢結(jié)果

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關(guān)鍵字

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

使用正則表達(dá)式查詢

• 查詢以a開頭
  select * from fruite where f_name regexp '^a';
• 查詢以y結(jié)尾
  `select * from fruite where f_name regexp 'y$';
• 查詢a與g之間只包含一個(gè)字母
  select * from fruite where f_name regexp 'a.g';
• b開頭后面有a的
  select * from fruite where f_name regexp '^ba*';
• 開頭后面有a的a最少出現(xiàn)一次
  select * from fruite where f_name regexp '^ba+';
• 匹配字符串的任意一個(gè)
  select * from fruite where f_name regexp '[ot]';
• 查詢ba出現(xiàn)至少一次，最多三次的記錄
  select * from fruite where f_name regexp 'ba(1,3)';