sql 查询语句:总结一下常用的,只要你不是数据库管理员,一般都够用了。
表一:select * from t_step;
表二:select * from t_case;
说10个知识点,分组查询中有个【字段合并】聚合函数注意一下
1、limit 读取前几条
# 解读:读取前2条 select * from t_step limit 2; select * from t_step limit 3,2; # 返回:2条数据 解读:从第3个索引读取2条(索引从0开始,从第4条开始取) id case_id step---------------- 4 case004 步骤4 5 case002 步骤22 # 解读:先where条件,再limit读取 select * from t_step where case_id like 'case%' limit 2;
2、排序 order by
# 以下两条排序 等价,order by 默认是 asc(从小到大排序) select * from t_step order by case_id select * from t_step order by case_id asc # 从大到小排序 select * from t_step order by case_id desc
3、去重复 distinct
# 把 case_id 去重复 select distinct case_id from t_step
4、分组查询 group by
常用聚合函数: 行数count(*),求和sum(score),求平均avg(age), 最小值min(age),更大值max(age) group_concat(sep) 合并sep字段数据 select case_id,count(*) from t_step group by case_id ; case_id count(*)-------------- case001 1 case002 2 case003 1 case004 1 select case_id,count(*) from t_step where case_id!='case001' group by case_id ; case_id count(*)-------------- case002 2 case003 1 case004 1 select case_id,count(*) from t_step where case_id!='case001' group by case_id having count(*)>1; case_id count(*)-------------- case002 2 select case_id,group_concat(step) from t_step group by case_id; case_id,group_concat(step) case001 步骤1 case002 步骤2,步骤22 case003 步骤3 case004 步骤4
5、取交集1:笛卡尔积
select * from t_case as c, t_sep as s where c.case_id = s.case_id; # 返回:5条 解读:把所有行数取交集 id case_id case_name id(1) case_id(1) step---------------- 1 case001 用例1 1 case001 步骤1 2 case002 用例2 2 case002 步骤2 3 case003 用例3 3 case003 步骤3 4 case004 用例4 4 case004 步骤4 2 case002 用例2 5 case002 步骤22
6、取交集2:链接(内链接)join on 等价于 inner join on
select * from t_case join t_step on t_case.case_id = t_step.case_id; # 返回:5条 解读:把所有行数取交集 id case_id case_name id(1) case_id(1) step---------------- 1 case001 用例1 1 case001 步骤1 2 case002 用例2 2 case002 步骤2 3 case003 用例3 3 case003 步骤3 4 case004 用例4 4 case004 步骤4 2 case002 用例2 5 case002 步骤22
7、左链接 left join on
# 以下2条语句输出一样 select * from t_case as c left join t_step as s on c.case_id = s.case_id # 返回:5条 解读:以左表为基础链接,右表中多,则丢弃,右表中少,则Null补充 select * from t_case left join t_step using(case_id); # 返回:5条 解读:using(字段) 可以取代 on条件 id case_id case_name id(1) case_id(1) step---------------- 1 case001 用例1 1 case001 步骤1 2 case002 用例2 2 case002 步骤2 3 case003 用例3 3 case003 步骤3 4 case004 用例4 4 case004 步骤4 2 case002 用例2 5 case002 步骤22 5 case006 用例6 Null Null Null
8、右链接
右链接和左链接相反,以右表为基础链接,左表中多,则丢弃,左表中少,则Null补充 select * from t_case as c right join t_step as s on c.case_id = s.case_id; select * from t_case right join t_step using(case_id);
9、全链接(union)可以用左链接 union 右链接
union 去重复;union all 不去重复 select * from t_case as c left join t_step as s on c.case_id = s.case_id union select * from t_case as c right join t_step as s on c.case_id = s.case_id;
10、子查询 简单举个例子,新表用()包裹起来
select * from t_case where case_id in (select case_id from t_step) select * from (select case_id,case_name from t_case) as t where t.case_id = 'case001'; select c.case_id,c.case_name,s.step from (select case_id,case_name from t_case) as c, (select case_id,step from t_step) as s where c.case_id = s.case_id;
下篇文章说一下 sql 语句的 增、删、改。