oracle——数据表的数据查询——max、min、sum、avg、count函数

忘是亡心i 2023-10-02 08:17 14阅读 0赞

sql查询
    
    create table yuangong ( id number,
                            name varchar2(50),
                            zhiwei varchar2(50),
                            age number,
                            sex varchar2(20)
                          ) tablespace test
                          
    insert into yuangong values(1,'周磊01','操作工',21,'男');
    insert into yuangong values(2,'周磊02','操作工',23,'女');
    insert into yuangong values(3,'周磊03','操作工',22,'男');
    insert into yuangong values(4,'周磊04','操作工',24,'女');
    insert into yuangong values(5,'周磊05','操作工',26,'男');
    insert into yuangong values(6,'周磊06','操作工',25,'女');
    insert into yuangong values(7,'周磊07','操作工',29,'男');
    insert into yuangong values(8,'周磊08','操作工',28,'女');
    insert into yuangong values(9,'周磊09','操作工',27,'男');
    insert into yuangong values(10,'周磊10','操作工',25,'女');
    
    insert into yuangong values(11,'周11磊','头目',39,'男');
    insert into yuangong values(12,'周12磊','头目',31,'女');
    insert into yuangong values(13,'周13磊','头目',37,'男');
    insert into yuangong values(14,'周14磊','头目',34,'女');
    
    insert into yuangong values(15,'周15磊','经理',45,'男');
    
    select * from yuangong;
    
    drop table yuangong;
    
    ======================================================================
    
    create table xinzi ( id number,
                         yuangonghao number,
                         yuefen number,
                         jine number
                        ) tablespace test
                        
    
    select * from xinzi;
    
    insert into xinzi values(1,1,1,3800);
    insert into xinzi values(2,1,2,3600);
    insert into xinzi values(3,1,3,3300);
    insert into xinzi values(4,1,4,3200);
    
    
    insert into xinzi values(5,2,1,2800);
    insert into xinzi values(6,2,2,2600);
    insert into xinzi values(7,2,3,2300);
    insert into xinzi values(8,2,4,2200);
    
    insert into xinzi values(9,3,1,2200);
    insert into xinzi values(10,3,2,2500);
    insert into xinzi values(11,3,3,2300);
    insert into xinzi values(12,3,4,2800);
    
    insert into xinzi values(13,4,1,1200);
    insert into xinzi values(14,4,2,1500);
    insert into xinzi values(15,4,3,1300);
    insert into xinzi values(16,4,4,1800);
    
    ---------------------------------------------------
    
    insert into xinzi values(17,11,1,4200);
    insert into xinzi values(18,11,2,4500);
    insert into xinzi values(19,11,3,4300);
    insert into xinzi values(20,11,4,4800);
    
    insert into xinzi values(21,12,1,4800);
    insert into xinzi values(22,12,2,4600);
    insert into xinzi values(23,12,3,4300);
    insert into xinzi values(24,12,4,4200);
    
    insert into xinzi values(25,13,1,4200);
    insert into xinzi values(26,13,2,4300);
    insert into xinzi values(27,13,3,4400);
    insert into xinzi values(28,13,4,4500);
    
    insert into xinzi values(29,13,1,4200);
    insert into xinzi values(30,13,2,4300);
    insert into xinzi values(31,13,3,4400);
    insert into xinzi values(32,13,4,4500);
    -------------------------------------------------------
    
    insert into xinzi values(33,15,1,7300);
    insert into xinzi values(34,15,2,7700);
    insert into xinzi values(35,15,3,7500);
    insert into xinzi values(36,15,4,7900);
    
    =======================================================================================
    
    select * from yuangong;
    
    select * from xinzi;
    
    =============================================================================================
    
    
    
    1、max()求最大值
    
    获取员工的最高工资：
    
    select max(jine) from xinzi;
    
    查询工资最高的员工的信息：
    
    
    select distinct name,jine from yuangong,xinzi where yuangong.id = xinzi.yuangonghao  and xinzi.jine = ( select max(jine) from xinzi );
    
    
    
    
    
    2、min()求最小值
    
    获取员工的最低工资：
    
    select min(jine) from xinzi;
    
    查询工资最低的员工的信息：
    
    
    select distinct name,jine from yuangong,xinzi where yuangong.id = xinzi.yuangonghao  and xinzi.jine = ( select min(jine) from xinzi );
    
    
    
    
    
    3、avg()求平均值
    
    获得四个月，每一个员工的平均工资
    
    select name,avg(jine) from yuangong,xinzi where yuangong.id = xinzi.yuangonghao group by yuangong.id,yuangong.name;
    
    注释：group by yuangong.id,yuangong.name先将所有记录按照员工进行分组，每一个分组都被看做一个记录集，avg(jine)返回每一个结果集的列的平均值
    
    
    
    
    
    4、sum()求和
    
    获取每一个员工前四个月的工资总和
    
    select name,sum(jine) from yuangong,xinzi where yuangong.id = xinzi.yuangonghao group by yuangong.id,yuangong.name;
    
    注释：group by yuangong.id,yuangong.name先将所有记录按照员工进行分组，每一个分组都被看做一个记录集，sum(jine)返回每一个结果集的列的总和值
    
    
    
    
    
    5、count()获得记录数
    
    此处，在总员工表中插入一条数据：
    
    select * from yuangong;
    
    delete from yuangong where id = 16;
    
    insert into yuangong values(16,null,null,null,null);  /*其余的值都是null*/
    
    
    
    (1) 使用count(*)获得员工记录
    
    select count(*) from yuangong;    /*16*/
    
    
    
    
    (2) 使用count(id)获得员工记录
    
    select count(id) from yuangong;    /*16*/
    
    
    
    
    (3) 使用count(name)获得员工记录
    
    select count(name) from yuangong;    /*15*/
    
    select count(zhiwei) from yuangong;    /*15*/
    
    select count(sex) from yuangong;    /*15*/
    
    
    
    
    (4) 使用count(1)获得员工记录
    
    select count(1) from yuangong;    /*16*/
    
    注释：count(1)是利用常量1进行计数。在结果集中，每查询到一条记录，都会累加1，因此最终结果为16；
    
    一般来说，利用count(1)速度最快，但是要注意结果是针对整行数据，还是某列数据。