一:聚合函数
avg(num)平均值 sum(num)求和 max(num)最大值 min(num)最小值 count(*)行数
二:声明
where属于约束声明,对数据表中的数据筛选,不能使用聚合函数
having属于过滤声明,聚合函数先执行,对查询出来的结果筛选。
如 select count(name) as num from A group by name having num>2;
三:子查询
1.where子句中使用子查询
select score from A where score> (select avg(score) from A);//查询成绩多余平均分的人员。
2.from中子查询
SELECT b.login_id FROM (SELECT u.login_id,u.code FROM base_admin_users u,base_city c where u.city_id=c.id) as b;
查询出用户表和能和城市表对应的用户的账号
3.where中
SELECT login_id from base_admin_users where login_id not in (SELECT u.login_id FROM base_admin_users u,base_city c where u.city_id=c.id) ;
查询出用户表中存在的城市表中不存在的账号
四:exists
SELECT login_id from base_admin_users e where exists (select 1 from base_city c where e.city_id=c.id and c.pid=0);
查询pid是0的账号信息;
SELECT login_id from base_admin_users e where not exists (select 1 from base_city c where e.city_id=c.id and c.pid=0);
查询pid不是0的账号信息;