软件测试常用SQL查询语句(一)
一、简单查询
from > distinct > select > order by > limit
(1) 普通查询:select name,age from students;
(2) 起别名查询:select s.name,s.age from students as s;
(3) 分页查询:select name,age from students limit 0,10;
(4)排序查询:select name,age,id from students order by age desc,id asc;
(5)去重查询:select distinct age from students;
二、条件查询
from > where > distinct > select > distinct > order by > limit
eg: select name,age, from students where name=‘xiaoming’ and age=18;
三、高级查询(聚合函数)
sum求和 | avg平均数 | max最大数 | 最小数min
group by | 分组查询
(1) 求班级男女平均年龄分别为多少?
select sex avg(age) from students group by sex;
count(*)记录含空值记录数 | count(列名)不含空记录数
(2) 求班级男女分别为多少人?
select sex count(*) from students group by sex;
(3) 查询每个部门里,每种职位的平均底薪。
select deptno,job,count(*),avg(sal) from t_emp group by deptno,job order by deptno;
group_concat | 可以搭配group by使用,使某个字段连接成字符串
(4) 查询每个部门内底薪超过2000元的人数和员工姓名。
select deptno grouo_concat(ename) count(*) from t_emp where sal>=2000 group by deptno;
having | 跟在group by 后面的条件语句
eg: select sex avg(age) from students group by sex having age in(10,20);