很多情况下,用户需要进行一些汇总操作,比如统计整个公司的人数或者统计每个部门的人数,这时候就要用到 sql 的聚合操作。 常用的聚合操作有sum(),count(*),avg(*),max(*),min(*) group by 后面接需要聚合的字段 with rollup 是可选参数,表名是否对分类聚合后的结果进行在汇总。 having 关键字标识对分类后的结果再进行条件的过滤。 注意:having 和 where 的区别在于,having是对聚合后的结果进行条件的过滤,而where 是在聚合前就对记录进行过滤,如果逻辑允许,我们尽可能用where先过滤记录,因为这样结果集减小、聚合的效率将大大提高,最后在根据逻辑看是否用having进行过滤。
1 2 3 4 5 6 7 8
selectcount(ename) from employee; # 以deptno进行分组查询,相当于使用count()方法对分组查询的查询结果进行处理,返回出现次数 select deptno,count(ename) from employee groupby deptno; # 加上withrollup 之后相当于额外再对全部结果进行一次count()操作 select deptno,count(ename) from employee groupby deptno withrollup; # 语句中的as 可以对查询结果进行重命名 select deptno,count(1) as count from employee groupby deptno withrolluphavingcount(1)>1; # selectmax(sal),min(sal),avg(sal) from employee;
select sur.user_id,nick_name,name from sys_user leftjoin sys_users_roles sur on sys_user.user_id = sur.user_id leftjoin sys_role sr on sur.role_id = sr.role_id;
# 如果子查询记录唯一则可以使用=来代替in select nick_name from sys_user where user_id in (select user_id from sys_users_roles); # 上述子查询可以转换为表连接查询 select nick_name from sys_user leftjoin sys_users_roles sur on sys_user.user_id = sur.user_id;
ps:表连接在大多数情况下都由于子查询
记录联合
我们经常会遇到这样的应用,将两个表的数据按照一定的查询条件查询出来之后,将结果合并一起显示出来,这个时候就需要用 union 和 union all 关键字来实现这样的功能。(合并一列显示,名字以第一个查询查询语句的的目标字段名为主)
1 2 3 4 5 6 7
# union 全都放到一列 # unionall 会进行一次去重 select nick_name as myUnion from sys_user unionall select name from sys_role
DCL 语句(Data Control Language)
DCL 语句主要是 DBA 用来管理系统中的对象权限时使用。
操作权限:GRANTSELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE
1 2 3 4 5 6 7
# 添加一个拥有全部增删改查权限且允许从任意host访问的用户lopponia,密码为xxxxxxxx on 后接数据库与表demo:test.employee,*.*代表全部数据库与表 to 后接用户名与host demo:'lopponia'@'%' by 后接密码 grantallon*.*to'lopponia'@'%' identified by'xxxxxxxxx'; # 刷新权限 flush privileges;