Oracle数据库-第7章-分组统计查询

【一】、Oracle常用的统计函数Avg(x):求一组行中列x值的平均值count(x):求一组行中列x值的非空行数count(*):求一组行的总行数max(x):求一组行中列x值的最大值min(x):求一组行中列x值的最小值stddev(x):求一组行中列x值的标准差sum(x):求一组行中列x值的总和variance(x):求一组行中列x值的方差【二】、group by与统计函数使用上面介绍的函数时可以使用也可以不使用group by ,但在使用group by时，未在group by部分用到的列在select 部分出现时必须使用统计函数，如按角色统计平均年龄Select user_name,avg(age) from usersGroup by role_id; ×Select count(user_name),avg(age) from usersGroup by role_id√【三】、用having字句规定统计条件having 子句的作用类似于where子句，只不过where 子句针对单个行，而having子句针对的是统计结果，一般和统计的函数搭配使用。

Having子句后必须为前面select后面的子部分，或是group by 后面的字段select count(uer_name),avg(age) from users group by role_id having role_id>20; ×select count(uer_name),avg(age) from users group by role_id having avg(age)>20; √【四】其他oracle常用函数Decode(column1,value1,output1,value2,output2,…..)如果column1 有一个值为value1那么将会用output1 来代替当前值,如果column1 的值为value2 那么就用OUTPUT2 来代替当前值,如果column1 中哪两个值都不是,那么就会用OUTPUT3 来代替当前值Select decode(age,10,7,9,6,3),user_name from users;SQL中的单记录函数1.ASCII返回与指定的字符对应的十进制数;SQL> select ascii(’A’) A,ascii(’a’) a,ascii(’0’) zero,ascii(’ ’) space from dual;A A ZERO SPACE--------- --------- --------- ---------65 97 48 322.CHR给出整数,返回对应的字符;SQL> select chr(54740) zhao,chr(65) chr65 from dual;ZH C-- -赵 A3.CONCAT连接两个字符串;SQL> select concat(’010-’,’88888888’)||’转23’ 高乾竞电话from dual;高乾竞电话----------------************转234.INITCAP返回字符串并将字符串的第一个字母变为大写;SQL> select initcap(’smith’) upp from dual;UPP-----Smith5.INSTR(C1,C2,I,J)在一个字符串中搜索指定的字符,返回发现指定的字符的位置;C1 被搜索的字符串C2 希望搜索的字符串I 搜索的开始位置,默认为1J 出现的位置,默认为1SQL> select instr(’oracle traning’,’ra’,1,2) instring from dual;INSTRING---------96.LENGTH返回字符串的长度;SQL> select name,length(name),addr,length(addr),sal,length(to_char(sal)) from gao.nchar_tst; NAME LENGTH(NAME) ADDR LENGTH(ADDR) SAL LENGTH(TO_CHAR(SAL))------ ------------ ---------------- ------------ --------- --------------------高乾竞 3 北京市海锭区6 9999.99 77.LOWER返回字符串,并将所有的字符小写SQL> selec t lower(’AaBbCcDd’)AaBbCcDd from dual;AABBCCDDaabbccdd8.UPPER返回字符串,并将所有的字符大写SQL> select upper(’AaBbCcDd’) upper from dual;UPPER--------AABBCCDD9.RPAD和LPAD(粘贴字符)RPAD 在列的右边粘贴字符LPAD 在列的左边粘贴字符SQL> select lpad(rpad(’gao’,10,’*’),17,’*’)from dual;LPAD(RPAD(’GAO’,1-----------------*******gao*******不够字符则用*来填满10.LTRIM和RTRIMLTRIM 删除左边出现的字符串RTRIM 删除右边出现的字符串SQL> select ltrim(rtrim(’ gao qian jing ’,’ ’),’ ’) from dual;LTRIM(RTRIM(’-------------gao qian jing11.SUBSTR(string,start,count)取子字符串,从start开始,取count个SQL> select substr(’130****8888’,3,8)from dual;SUBSTR(’--------0888888812.REPLACE(’string’,’s1’,’s2’)string 希望被替换的字符或变量s1 被替换的字符串s2 要替换的字符串SQL> select replace(’he love you’,’he’,’i’) from d ual;REPLACE(’H----------i love you13.SOUNDEX返回一个与给定的字符串读音相同的字符串SQL> create table table1(xm varchar(8));SQL> insert into table1 values(’weather’);SQL> insert into table1 values(’wether’);SQL> insert into table1 values(’gao’);SQL> select xm from table1 where soundex(xm)=soundex(’weather’);--------weatherwether14.TRIM(’s’ from ’string’)LEADING 剪掉前面的字符TRAILING 剪掉后面的字符如果不指定,默认为空格符15.ABS返回指定值的绝对值SQL> select abs(100),abs(-100) from dual; ABS(100) ABS(-100)--------- ---------100 10016.ACOS给出反余弦的值SQL> select acos(-1) from dual;ACOS(-1)---------3.141592717.ASIN给出反正弦的值SQL> select asin(0.5) from dual;ASIN(0.5)---------.5235987818.ATAN返回一个数字的反正切值SQL> select atan(1) from dual;ATAN(1)---------.7853981619.CEIL返回大于或等于给出数字的最小整数SQL> select ceil(3.1415927) from dual; CEIL(3.1415927)---------------420.COS返回一个给定数字的余弦SQL> select cos(-3.1415927) from dual; COS(-3.1415927)----------------121.COSH返回一个数字反余弦值SQL> select cosh(20) from dual;COSH(20)---------24258259822.EXP返回一个数字e的n次方根SQL> select exp(2),exp(1) from dual;EXP(2) EXP(1)--------- ---------7.3890561 2.718281823.FLOOR对给定的数字取整数SQL> select floor(2345.67) from dual;FLOOR(2345.67)--------------234524.LN返回一个数字的对数值SQL> select ln(1),ln(2),ln(2.7182818) from dual;LN(1) LN(2) LN(2.7182818)--------- --------- -------------0 .69314718 .9999999925.LOG(n1,n2)返回一个以n1为底n2的对数SQL> select log(2,1),log(2,4) from dual;LOG(2,1) LOG(2,4)--------- ---------0 226.MOD(n1,n2)返回一个n1除以n2的余数SQL> select mod(10,3),mod(3,3),mod(2,3) from dual; MOD(10,3) MOD(3,3) MOD(2,3)--------- --------- ---------1 0 227.POWER返回n1的n2次方根SQL> select power(2,10),power(3,3) from dual; POWER(2,10) POWER(3,3)----------- ----------1024 2728.ROUND和TRUNC按照指定的精度进行舍入SQL> select round(55.5),round(-55.4),trunc(55.5),trunc(-55.5) from dual; ROUND(55.5) ROUND(-55.4) TRUNC(55.5) TRUNC(-55.5)----------- ------------ ----------- ------------56 -55 55 -5529.SIGN取数字n的符号,大于0返回1,小于0返回-1,等于0返回0SQL> select sign(123),sign(-100),sign(0) from dual;SIGN(123) SIGN(-100) SIGN(0)--------- ---------- ---------1 -1 030.SIN返回一个数字的正弦值SQL> select sin(1.57079) from dual;SIN(1.57079)------------131.SIGH返回双曲正弦的值SQL> select sin(20),sinh(20) from dual;SIN(20) SINH(20)--------- ---------.91294525 24258259832.SQRT返回数字n的根SQL> select sqrt(64),sqrt(10) from dual;SQRT(64) SQRT(10)--------- ---------8 3.162277733.TAN返回数字的正切值SQL> select tan(20),tan(10) from dual;TAN(20) TAN(10)--------- ---------2.2371609 .6483608334.TANH返回数字n的双曲正切值SQL> select tanh(20),tan(20) from dual;TANH(20) TAN(20)--------- ---------1 2.237160935.TRUNC按照指定的精度截取一个数SQL> select trunc(124.1666,-2) trunc1,trunc(124.16666,2) from dual;TRUNC1 TRUNC(124.16666,2)--------- ------------------100 124.1636.ADD_MONTHS增加或减去月份SQL> select to_char(add_months(to_date(’199912’,’yyyymm’),2),’yyyymm’) from dual;TO_CHA------200002SQL> s elect to_char(add_months(to_date(’199912’,’yyyymm’),-2),’yyyymm’) from dual;TO_CHA------199910ST_DAY返回日期的最后一天SQL> select to_char(sysdate,’yyyy.mm.dd’),to_char((sysdate)+1,’yyyy.mm.dd’) from dual;TO_CHAR(SY TO_CHAR((S---------- ----------2004.05.09 2004.05.10SQL> select last_day(sysdate) from dual;LAST_DAY(S----------31-5月-0438.MONTHS_BETWEEN(date2,date1)给出date2-date1的月份SQL> select months_between(’19-12月-1999’,’19-3月-1999’) mon_between from dual;MON_BETWEEN-----------9SQL>selectmonths_between(to_date(’2000.05.20’,’yyyy.mm.dd’),to_date(’2005.05.20’,’yyyy.mm.dd’)) mon_betw from dual; MON_BETW----------6039.NEW_TIME(date,’this’,’that’)给出在this时区=other时区的日期和时间SQL> select to_char(sysdate,’yyyy.mm.dd hh24:mi:ss’) bj_time,to_char(new_time2 (sysdate,’PDT’,’GMT’),’yyyy.mm.dd hh24:mi:ss’) los_angles from dual;BJ_TIME LOS_ANGLES------------------- -------------------2004.05.09 11:05:32 2004.05.09 18:05:32给出日期date和星期x之后计算下一个星期的日期S QL> select next_day(’18-5月-2001’,’星期五’) next_day from dual; NEXT_DAY----------25-5月-0141.SYSDATE用来得到系统的当前日期SQL> select to_char(sysdate,’dd-mm-yyyy day’) from dual;TO_CHAR(SYSDATE,’-----------------09-05-2004 星期日trunc(date,fmt)按照给出的要求将日期截断,如果fmt=’mi’表示保留分,截断秒SQL> select to_char(trunc(sysdate,’hh’),’yyyy.mm.dd hh24:mi:ss’) hh,2 to_char(trunc(sysdate,’mi’),’yyyy.mm.dd hh24:mi:ss’) hhmm from dual; HH HHMM------------------- -------------------2004.05.09 11:00:00 2004.05.09 11:17:0042.CHARTOROWID将字符数据类型转换为ROWID类型SQL> select rowid,rowidtochar(rowid),ename from scott.emp;ROWID ROWIDTOCHAR(ROWID) ENAME------------------ ------------------ ----------AAAAfKAACAAAAEqAAA AAAAfKAACAAAAEqAAA SMITH AAAAfKAACAAAAEqAAB AAAAfKAACAAAAEqAAB ALLEN AAAAfKAACAAAAEqAAC AAAAfKAACAAAAEqAAC WARD AAAAfKAACAAAAEqAAD AAAAfKAACAAAAEqAAD JONES43.CONVERT(c,dset,sset)将源字符串sset从一个语言字符集转换到另一个目的dset字符集SQL> select convert(’strutz’,’we8hp’,’f7dec’) "conversion" from dual; conver------strutz44.HEXTORAW将一个十六进制构成的字符串转换为二进制45.RAWTOHEXT将一个二进制构成的字符串转换为十六进制46.ROWIDTOCHAR将ROWID数据类型转换为字符类型47.TO_CHAR(date,’format’)SQL> select to_char(sysdate,’yyyy/mm/dd hh24:mi:ss’) from dual;TO_CHAR(SYSDATE,’YY-------------------2004/05/09 21:14:41将字符串转化为ORACLE中的一个日期49.TO_MULTI_BYTE将字符串中的单字节字符转化为多字节字符SQL> select to_multi_byte(’高’) from dual;TO--高50.TO_NUMBER将给出的字符转换为数字SQL> select to_number(’1999’) year from dual;YEAR---------199951.BFILENAME(dir,file)指定一个外部二进制文件SQL>insert into file_tb1 values(bfilename(’lob_dir1’,’image1.gif’));52.CONVERT(’x’,’desc’,’source’)将x字段或变量的源source转换为descSQL> select sid,serial#,username,decode(command,2 0,’none’,3 2,’insert’,4 3,5 ’select’,6 6,’update’,7 7,’delete’,8 8,’drop’,9 ’other’) cmd from v$session where type!=’background’;SID SERIAL# USERNAME CMD--------- --------- ------------------------------ ------1 1 none2 1 none3 1 none4 1 none5 1 none6 1 none7 1275 none8 1275 none9 20 GAO select10 40 GAO none53.DUMP(s,fmt,start,length)DUMP函数以fmt指定的内部数字格式返回一个VARCHAR2类型的值SQL> col global_name for a30SQL> col dump_string for a50SQL> set lin 200SQL> select global_name,dump(global_name,1017,8,5) dump_string from global_name; GLOBAL_NAME DUMP_STRING------------------------------ --------------------------------------------------ORACLE.WORLD Typ=1 Len=12 CharacterSet=ZHS16GBK: W,O,R,L,D54.EMPTY_BLOB()和EMPTY_CLOB()这两个函数都是用来对大数据类型字段进行初始化操作的函数55.GREATEST返回一组表达式中的最大值,即比较字符的编码大小.SQL> select greatest(’AA’,’AB’,’AC’) from dual;GR--ACSQL> select greatest(’啊’,’安’,’天’) from dual;GR--天56.LEAST返回一组表达式中的最小值SQL> select least(’啊’,’安’,’天’) from dual;LE--啊57.UID返回标识当前用户的唯一整数SQL> show userUSER 为"GAO"SQL> select username,user_id from dba_users where user_id=uid;USERNAME USER_ID------------------------------ ---------GAO 25ER返回当前用户的名字SQL> select user from dual;USER------------------------------GAOEREVN返回当前用户环境的信息,opt可以是:ENTRYID,SESSIONID,TERMINAL,ISDBA,LABLE,LANGUAGE,CLIENT_INFO,LANG,VSIZE ISDBA 查看当前用户是否是DBA如果是则返回trueSQL> select userenv(’isdba’) from dual;USEREN------FALSESQL> select userenv(’isdba’) from dual; USEREN------TRUESESSION返回会话标志SQL> select userenv(’sessionid’) from dual; USERENV(’SESSIONID’)--------------------152ENTRYID返回会话人口标志SQL> select userenv(’entryid’) from dual; USERENV(’ENTRYID’)------------------INSTANCE返回当前INSTANCE的标志SQL> select userenv(’instance’) from dual; USERENV(’INSTANCE’)-------------------1LANGUAGE返回当前环境变量SQL> select userenv(’language’) from dual; USERENV(’LANGUAGE’)---------------------------------------------------- SIMPLIFIED CHINESE_CHINA.ZHS16GBK LANG返回当前环境的语言的缩写SQL> select userenv(’lang’) from dual; USERENV(’LANG’)----------------------------------------------------ZHSTERMINAL返回用户的终端或机器的标志SQL> select userenv(’terminal’) from dual; USERENV(’TERMINA----------------GAOVSIZE(X)返回X的大小(字节)数SQL> select vsize(user),user from dual; VSIZE(USER) USER----------- ------------------------------6 SYSTEM60.AVG(DISTINCT|ALL)all表示对所有的值求平均值,distinct只对不同的值求平均值SQLWKS> create table table3(xm varchar(8),sal number(7,2));语句已处理。

04数据库——数据库表单查询（where ，分组，聚合函数，筛选，去重，排序）、多表查询、⼦查询前期表准备('tank','male',73,'20140701','teacher',3500,401,1),('owen','male',28,'20121101','teacher',2100,401,1),('jerry','female',18,'20110211','teacher',9000,401,1),('nick','male',18,'19000301','teacher',30000,401,1),('sean','male',48,'20101111','teacher',10000,401,1),('歪歪','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门('丫丫','female',38,'20101101','sale',2000.35,402,2),('丁丁','female',18,'20110312','sale',1000.37,402,2),('星星','female',18,'20160513','sale',3000.29,402,2),('格格','female',28,'20170127','sale',4000.33,402,2),('张野','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门('程咬⾦','male',18,'19970312','operation',20000,403,3),('程咬银','female',18,'20130311','operation',19000,403,3),('程咬铜','male',18,'20150411','operation',18000,403,3),('程咬铁','female',18,'20140512','operation',17000,403,3);#ps ：如果在windows 系统中，插⼊中⽂字符，select 的结果为空⽩，可以将所有字符编码统⼀设置成gbk 创建表，插⼊数据⼀、语法的执⾏顺序select * from emp\G;当表字段特别多的时候 结果的排版可能会出现混乱的现象 你可以在查询语句加\G 来规范查询结果# 语法顺序select fromwhere group by (having)# 再识执⾏顺序from wheregroup by (having)select#完整版sql 语句的查询select distinct post,avg(salary)from table1 where id > 1group by posthaving avg(salary) > 1000order by avg(salary)limit 5,5⼆、where 约束条件"""模糊匹配 like%:匹配多个任意字符 _:匹配⼀个任意字符三、group by 分组1.分组前戏 ——设置严格模式select * from emp group by post; # 报错select id,name,sex from emp group by post; # 报错select post from emp group by post; # 获取部门信息#查询详细信息报错，只能查询到分组的信息，说明设置成功强调:只要分组了，就不能够再“直接”查找到单个数据信息了，只能获取到组名2.聚合函数 max min avg sum count 以组为单位统计组内数据>>>聚合查询(聚集到⼀起合成为⼀个结果)如果⼀张表没有写group by 默认所有的数据就是⼀组#在分组后，即select 后⾯或者having 后⾯才能使⽤# 每个部门的最⾼⼯资select post,max(salary) from emp group by post;PS:给字段取别名（as 也可以省略，但是⼀般不要这样⼲）select post as 部门,max(salary) as 最⾼⼯资 from emp group by post;# 每个部门的最低⼯资select post,min(salary) from emp group by post;# 每个部门的平均⼯资select post,avg(salary) from emp group by post;# 每个部门的⼯资总和select post,sum(salary) from emp group by post;# 每个部门的⼈数总数select post,count(id) from emp group by post;在统计分组内个数的时候，填写任意⾮空字段都可以完成计数,推荐使⽤能够⾮空且唯⼀标识数据的字段，⽐如id 字段# 聚合函数max min sum count avg 只能在分组之后才能使⽤，也就是紧跟着select ⽤或者紧跟着having （分组后的⼆次where ）select id,name,age from emp where max(salary) > 3000; # 报错！select max(salary) from emp;# 正常运⾏，不分组意味着每⼀个⼈都是⼀组，等运⾏到max(salary)的时候已经经过where，group by操作了，只不过我们都没有写这些条件3.group_concat 和 concatgroup_concat（分组之后⽤）不仅可以⽤来显⽰除分组外字段还有拼接字符串的作⽤1.group_concat 显⽰分组外字符 拼接字符串#查询分组之后的部门名称和每个部门下所有⼈的姓名select post,group_concat(name) from emp group by post;#在每个⼈的名字前后拼接字符select post,group_concat('D_',name,"_SB") from emp group by post;#group_concat()能够拿到分组后每⼀个数据指定字段(可以是多个)对应的值select post,group_concat(name,": ",salary) from emp group by post;2.concat拼接 as语法使⽤（不分组时⽤）就是⽤来拼接字符串达到更好的显⽰效果select name as 姓名,salary as 薪资from emp;select concat("NAME: ",name) as 姓名,concat("SAL: ",salary) as 薪资from emp;# 如果拼接的符号是统⼀的可以⽤ concat_wsselect concat_ws(':',name,age,sex) as info from emp;⼩技巧:concat就是⽤来帮你拼接数据，不分组情况下使⽤group_concat 分组之后使⽤，可以拼接数据也可以⽤来显⽰其他字段信息# 补充as语法既可以给字段起别名也可以给表起select emp.id, from emp as t1; # 报错因为表名已经被你改成了t1select t1.id, from emp as t1;3.查询四则运算# 查询每个⼈的年薪select name,salary*12 as annual_salary from emp;select name,salary*12 annual_salary from emp; # as可以省略4.练习题"""View Code8、统计各部门年龄在30岁以上的员⼯平均⼯资四、having 筛选跟where是⼀模⼀样的也是⽤来筛选数据但是having是跟在group by之后的where是对整体数据做⼀个初步的筛选⽽having是对分组之后的数据再进⾏⼀次针对性的筛选1、统计各部门年龄在30岁以上的员⼯平均⼯资，并且保留平均⼯资⼤于10000的部门select post,avg(salary) from emp where age > 30 group by post where avg(salary) > 10000; # 报错select post,avg(salary) from empwhere age >= 30group by posthaving avg(salary) > 10000;强调：having必须在group by后⾯使⽤select * from emp having avg(salary) > 10000; # 报错五、distinct 去重# 对有重复的展⽰数据进⾏去重操作#去重⼀定要满⾜数据是⼀模⼀样的情况下才能达到去重的效果#如果你查询出来的数据中包含主键字段,那么不可能去重成功#只要有⼀个不⼀样都不能算是的重复的数select distinct id,age from emp; #去重失败，id不⼀样，即使age⼀样也没⽑⽤select distinct post from emp; #成功六、limit 限制条数# 限制展⽰条数select * from emp limit 5; # 只展⽰数据的五条# 分页显⽰select * from emp limit 5,5; #第6条开始，往后展⽰5条当limit只有⼀个参数的时候表⽰的是只展⽰⼏条当limit有两个参数的时候第⼀个参数表⽰的起始位置，是索引第⼆个参数表⽰从起始位置开始往后展⽰的条数# 查询⼯资最⾼的⼈的详细信息select * from emp order by salary desc limit 1;七、regexp 正则# 在编程中只要看到reg开头的基本上都是跟正则相关select * from emp where name regexp '^j.*(n|y)$';re模块中findall:分组优先会将括号内正则匹配到的优先返回match:从头开始匹配匹配到⼀个就直接返回res = match('^j.*n$','jason')print(res.group())search:整体匹配匹配到⼀个就直接返回⼋、order by 排序select * from emp order by salary asc; #默认升序排select * from emp order by salary desc; #降序排select * from emp order by age desc; #降序排#先按照age 降序排，在年纪相同的情况下再按照薪资升序排select * from emp order by age desc,salary asc;# 统计各部门年龄在10岁以上的员⼯平均⼯资，并且保留平均⼯资⼤于1000的部门，然后对平均⼯资进⾏排序select post,avg(salary) from empwhere age > 10group by posthaving avg(salary) > 1000order by avg(salary);九、多表查询(203,'运营');insert into emp(name,sex,age,dep_id) values('jason','male',18,200),('egon','female',48,201),('kevin','male',38,201),('nick','female',28,202),('owen','male',18,200),('jerry','female',18,204);# 当初为什么我们要分表，就是为了⽅便管理，在硬盘上确实是多张表，但是到了内存中我们应该把他们再拼成⼀张表进⾏查询才合理创建表当初为什么我们要分表，就是为了⽅便管理，在硬盘上确实是多张表，但是到了内存中我们应该把他们再拼成⼀张表进⾏查询才合理#笛卡尔积select * from emp,dep; # 左表⼀条记录与右表所有记录都对应⼀遍，即10*4=40条 >>>笛卡尔积# 将所有的数据都对应了⼀遍，虽然不合理但是其中有合理的数据，现在我们需要做的就是找出合理的数据# 查询员⼯及所在部门的信息select * from emp,dep where emp.dep_id = dep.id;#查询部门为技术部的员⼯及部门信息select * from emp,dep where emp.dep_id = dep.id and = '技术';其实将两张表关联到⼀起的操作，有专门对应的⽅法：内连接、左连接、右链接、全连接# 1、内连接：只链接两张表有对应关系的记录select * from emp inner join dep on emp.dep_id = dep.id;select * from emp inner join dep on emp.dep_id = dep.idwhere = "技术";# 2、左连接: 在内连接的基础上保留左表没有对应关系的记录，没有部门信息null 补全select * from emp left join dep on emp.dep_id = dep.id;# 3、右连接: 在内连接的基础上保留右表没有对应关系的记录，没有员⼯信息null 补全select * from emp right join dep on emp.dep_id = dep.id;# 4、全连接：在内连接的基础上保留左、右⾯表没有对应关系的的记录，空⽩全⽤null 补全# 只要将左连接和右连接的sql 语句中间加⼀个union 连起来就变成全连接select * from emp left join dep on emp.dep_id = dep.idunionselect * from emp right join dep on emp.dep_id = dep.id;⼗、⼦查询就是将⼀个查询语句的结果⽤括号括起来当作另外⼀个查询语句的条件去⽤,括号⾥⾯语句末尾不能加分号#最新⽇期作为条件select name,hire_date,post from emp where hire_date in (select max(hire_date) from emp group by post) ;# 查询平均年轻在25岁以上的部门名⽅法⼀：⼦查询select name from dep where id in(select dep_id from emp group by dep_id having avg(age)>25);⽅法⼆：连表查询select from emp inner join dep on emp.dep_id = dep.idgroup by having avg(age) > 25;"""记住⼀个规律，表的查询结果可以作为其他表的查询条件，也可以通过其别名的⽅式把它作为⼀张虚拟表去跟其他表做关联查询"""select * from emp inner join dep on emp.dep_id = dep.id;⼗⼀、exist(了解)EXISTS关字键字表⽰存在。

oracle的partition by函数标题：深入剖析Oracle的Partition By函数导言：Oracle数据库是目前企业级应用中最常用的关系型数据库之一，其功能丰富且强大。

在处理大规模数据时，为了提高查询效率和管理数据的灵活性，分区技术成为了一个重要的手段。

Partition By函数作为分区方案中的核心组成部分，在Oracle数据库中具有重要作用。

本文将深入剖析Oracle 的Partition By函数，一步一步回答您关于该函数的疑问。

第一部分：什么是分区？首先，我们需要了解什么是分区。

分区是指将数据库中的表或索引分割成多个较小的部分，成为分区子表（Partition）。

每个分区子表可以独立地存储在不同的位置，可以有不同的物理结构，甚至可以存储在不同的磁盘上。

通过将数据分割成较小的分区，可以提高查询效率、降低维护成本、加快备份和恢复速度等优点。

第二部分：Partition By函数的作用是什么？Partition By函数是Oracle数据库中用于对查询结果进行分区处理的一种函数。

它可以按照指定的列对查询结果进行分区，并将结果按照分区进行分组。

Partition By函数常常与窗口函数一起使用，以实现更为复杂的查询需求。

它可以用于对数据进行排序、分组和统计等操作，增加了查询的灵活性和功能性。

第三部分：Partition By函数使用方法1. Partition By函数的语法结构Partition By函数的语法结构如下：SELECT 列1, 列2, ... 列n, 函数() OVER (PARTITION BY 列x ORDER BY 列y) FROM 表;其中，列1、列2、...列n表示要查询的列名，函数()表示需要对查询结果进行的操作，而列x和列y是用于指定分区的列和排序的列。

通过PARTITION BY子句指定分区列，并通过ORDER BY子句指定排序列。

2. Partition By函数的功能Partition By函数可以实现以下功能：- 数据分组：通过将查询结果按照指定的列进行分组，可以对分组后的数据进行聚合统计等操作，从而得到更加精确的分析结果。

第7章物理存储结构(1)关于联机重做日志，以下哪两个说法是正确的？BCA．所有日志组的所有文件都是同样大小B．一组中的所有成员文件都是同样大小C．成员文件应置于不同的磁盘D．回滚段大小决定成员文件大小(2)DBA使用哪个命令显示当前归档状态？AA．ARCHIVE LOG LIST B．FROM ARCHIVE LOGSC．SELECT * FROM V$THREAD D．SELECT * FROM ARCHIVE_LOG_LIST (3)创建一个数据库需要多少个控制文件？AA．1B．2 C．3 D．0(4)将下句补充完整：控制文件的建议配置是CA．每数据库一个控制文件B．每磁盘一个控制文件C．二个控制文件置于二个磁盘D．二个控制文件置于一个磁盘(5)当创建控制文件时，数据库必须处于什么状态？BA．加载B．未加载C．打开D．受限(6)哪个数据字典视图显示数据库处于归档状态？CA. V$INSTANCE B．V$LOG C．V$DATABASE D．V$THREAD(7)把多路镜像控制文件存于不同磁盘最大的好处是BA．数据库性能提高B．防止失败C．提高归档速度D．能并发访问提高控制文件的写入速度(8)哪个文件用于记录数据库的改变，并且用于实例的恢复？BA．Archive log file B．Redo log file C．Control file D．Alert log file(9)一个实例可以建立几个归档进程？CA．5 B．4 C．10D．取决于操作系统(10)定义归档目标地址时，哪两个参数不能一起使用？AB ACA．LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DESTB．LOG_ARCHIVE_DEST and LOG_ARCHIVE_DEST_1C．LOG_ARCHIVE_DEST and LOG_ARCHIVE_DEST_2D．以上都不是;可以指定所有的归档参数第八章逻辑存储结构(1)段的集合称为CA．区B．段C．表空间D．数据库(2)当数据库崩溃，何时使用回滚信息？CA．在崩溃之前B．恢复完成之后C．数据库重新打开之后、恢复之前D．数据库崩溃后回滚信息不被使用(3)数据字典表和视图存储在BA．USERS表空间B．SYSTEM表空间C．TEMPORARY表空间D．以上三个中任意(4)PCTFREE与PCTUSED之和不应超过AA．100 B．50 C．25 D．10(5)数据块的哪三个部分合称为块头部？CA．表目录、行目录和行数据B．标题、表目录、空闲区C．表目录、行目录、标题D．块头部、行数据、行头部(6)数据库打开时，以下哪个表空间必须联机？AA．SYSTEM B．TEMPORARY C．ROLLBACK D．USERS (7)指定哪个表空间参与排序操作，可以提高排序效率？B A．SYSEM B．TEMPORARY C．ROLLBACK D．USERS(8)临时表空间中用于排序的段是何时创建的AA．排序操作开始的时候B．临时表空间创建的时候C．当排序操作需要内存操过1KB的时候。

oracle 分组统计函数Oracle是一种流行的关系型数据库管理系统，具有强大的分组统计函数，可以帮助用户轻松实现数据分析和汇总。

在本文中，我们将介绍几种常用的Oracle分组统计函数，并说明它们的用途和功能。

GROUP BY子句是SQL语句中用于对查询结果进行分组的重要部分。

在Oracle中，可以结合使用GROUP BY子句和聚合函数来实现数据的分组统计。

以下是几种常用的Oracle分组统计函数：1. COUNT函数：COUNT函数用于统计查询结果集中行的数量。

可以结合GROUP BY子句使用，以实现对分组数据的计数统计。

例如，可以使用COUNT(*)来统计每个分组中的行数，或者使用COUNT(column_name)来统计指定列中非空值的数量。

2. SUM函数：SUM函数用于计算指定列的合计值。

可以结合GROUP BY子句使用，以实现对分组数据的求和统计。

例如，可以使用SUM(column_name)来计算每个分组中指定列的合计值。

3. AVG函数：AVG函数用于计算指定列的平均值。

可以结合GROUP BY子句使用，以实现对分组数据的平均值统计。

例如，可以使用AVG(column_name)来计算每个分组中指定列的平均值。

4. MAX函数：MAX函数用于找出指定列的最大值。

可以结合GROUP BY子句使用，以实现对分组数据的最大值统计。

例如，可以使用MAX(column_name)来找出每个分组中指定列的最大值。

5. MIN函数：MIN函数用于找出指定列的最小值。

可以结合GROUP BY子句使用，以实现对分组数据的最小值统计。

例如，可以使用MIN(column_name)来找出每个分组中指定列的最小值。

除了上述常用的分组统计函数外，Oracle还提供了其他一些函数，如STDDEV、VARIANCE等，用于计算标准差和方差等统计指标。

这些函数可以帮助用户更全面地分析数据，发现数据的规律和趋势。

分组查询的概念分组查询是一种在数据库中进行数据聚合和汇总的查询方法。

通过分组查询，我们可以按照指定的列将数据划分为多个小组，并对每个小组进行统计、分析或计算。

分组查询是SQL语言的一项强大功能，它可以帮助我们快速准确地获取需要的汇总信息，从而进行更深入的数据分析。

在分组查询中，我们需要使用GROUP BY子句指定分组的列。

GROUP BY子句将数据按照指定的列值进行分组，然后根据分组进行聚合操作。

在GROUP BY 子句之后，我们可以使用聚合函数对每个小组的数据进行统计计算，例如对某列求和、求平均值、计数等。

分组查询的结果集通常包括分组的列和聚合函数的计算结果。

分组查询的语法结构如下：SELECT 列1, 列2, ..., 聚合函数(列或表达式)FROM 表名GROUP BY 列1, 列2, ...下面以一个示例来说明分组查询的用法。

假设我们有一个员工信息表employee，包含员工的姓名、所在部门和薪水。

我们想要根据部门对员工进行分组，并计算每个部门的平均薪水和人数。

首先，我们可以使用下面的SQL语句对员工信息进行分组查询：SELECT 部门, AVG(薪水), COUNT(*)FROM employeeGROUP BY 部门;以上SQL语句中，我们使用AVG函数计算了每个部门的平均薪水，使用COUNT(*)函数计算了每个部门的人数。

通过GROUP BY子句，我们将数据按照部门进行了分组。

分组查询的结果将按照分组的列值进行分类，并显示每个小组的统计结果。

对于上述示例，分组查询的结果可能类似于下面的表格：部门平均薪水人数销售部5000 10财务部6000 8技术部7000 12从结果中我们可以看出，分组查询可以帮助我们快速了解每个部门的平均薪水和人数。

这样的信息对于进行部门间比较、薪酬管理和资源安排等决策非常重要。

除了上述形式的分组查询，我们还可以在分组查询中使用HAVING子句。

HAVING子句用于过滤分组查询的结果，只返回满足指定条件的分组。

Oracle中分组查询groupby⽤法规则详解Oracle中group by⽤法在select 语句中可以使⽤group by ⼦句将⾏划分成较⼩的组，⼀旦使⽤分组后select操作的对象变为各个分组后的数据，使⽤聚组函数返回的是每⼀个组的汇总信息。

使⽤having⼦句限制返回的结果集。

group by ⼦句可以将查询结果分组，并返回⾏的汇总信息Oracle 按照group by ⼦句中指定的表达式的值分组查询结果。

在带有group by ⼦句的查询语句中，在select 列表中指定的列要么是group by ⼦句中指定的列，要么包含聚组函数 select max(sal),job emp group by job; (注意max(sal),job的job并⾮⼀定要出现，但有意义) 查询语句的select 和group by ,having ⼦句是聚组函数唯⼀出现的地⽅，在where ⼦句中不能使⽤聚组函数。

select deptno,sum(sal)from emp where sal>1200group by deptnohaving sum(sal)>8500order by deptno;当在gropu by ⼦句中使⽤having ⼦句时，查询结果中只返回满⾜having条件的组。

在⼀个sql语句中可以有where⼦句和having⼦句。

having 与where ⼦句类似，均⽤于设置限定条件 where ⼦句的作⽤是在对查询结果进⾏分组前，将不符合where条件的⾏去掉，即在分组之前过滤数据，条件中不能包含聚合函数，使⽤where条件显⽰特定的⾏。

having ⼦句的作⽤是筛选满⾜条件的组，即在分组之后过滤数据，条件中经常包含聚合函数，使⽤having 条件显⽰特定的组，也可以使⽤多个分组标准进⾏分组。

使⽤order by排序时order by⼦句置于group by 之后并且 order by ⼦句的排序标准不能出现在select查询之外的列。

数据库分组函数
分组函数是数据库中常用的一种函数，可以对一组数据进行统计
和处理。

以下是一些常见的数据库分组函数：
1. COUNT：用于统计行数或列中非空值的个数。

2. SUM：用于计算数值型列的总和。

3. AVG：用于计算数值型列的平均值。

4. MAX：用于求出数值型列中的最大值。

5. MIN：用于求出数值型列中的最小值。

这些函数通常与 GROUP BY 语句一起使用，以便将数据按照某个
列分组后再进行计算和处理。

例如，如果要统计每个城市的人口总数，可以使用以下 SQL 语句：
SELECT city, SUM(population)
FROM population_table
GROUP BY city;
这会按照城市分组并计算每个城市的人口总数。

SQL> analy‎z e table‎emplo‎y ee compu‎t e stati‎s tics‎;表已分析。

SQL> set autot‎r ace onSQL> selec‎t count‎(*) from emplo‎y ee ;COUNT‎(*)----------29999‎9Execu‎t ion Plan----------------------------------------------------------0 SELEC‎T STATE‎M ENT Optim‎i zer=CHOOS‎E (Cost=7 Card=1)1 0 SORT (AGGRE‎G ATE)2 1 BITMA‎P CONVE‎R SION‎(COUNT‎)3 2 BITMA‎P INDEX‎(FAST FULL SCAN) OF 'IDX_B‎M P_EM‎P_SEX‎'Stati‎s tics‎----------------------------------------------------------153 recur‎s ive calls‎0 db block‎gets96 consi‎s tent‎gets11 physi‎c al reads‎0 redo size370 bytes‎sent via SQL*Net to clien‎t425 bytes‎recei‎v ed via SQL*Net from clien‎t2 SQL*Net round‎t rips‎to/from clien‎t0 sorts‎(memor‎y)0 sorts‎(disk)1 rows proce‎s sedSQL> analy‎z e table‎emplo‎y ee delet‎e stati‎s tics‎;表已分析。

SQL> selec‎t count‎(*) from emplo‎y ee;COUNT‎(*)----------29999‎9Execu‎t ion Plan----------------------------------------------------------0 SELEC‎T STATE‎M ENT Optim‎i zer=CHOOS‎E1 0 SORT (AGGRE‎G ATE)2 1 TABLE‎ACCES‎S (FULL) OF 'EMPLO‎Y EE'Stati‎s tics‎----------------------------------------------------------0 recur‎s ive calls‎0 db block‎gets5418 consi‎s tent‎gets3144 physi‎c al reads‎0 redo size370 bytes‎sent via SQL*Net to clien‎t425 bytes‎recei‎v ed via SQL*Net from clien‎t2 SQL*Net round‎t rips‎to/from clien‎t0 sorts‎(memor‎y)0 sorts‎(disk)1 rows proce‎s sedSQL> analy‎z e table‎emppl‎o yee ESTIM‎A TE stati‎s tics‎; analy‎z e table‎emppl‎o yee ESTIM‎A TE stati‎s tics‎*ERROR‎位于第1 行:ORA-00942‎:表或视图不‎存在SQL> analy‎z e table‎emplo‎y ee ESTIM‎A TE stati‎s tics‎;SQL> selec‎t count‎(*) from emplo‎y ee;COUNT‎(*)----------29999‎9Execu‎t ion Plan----------------------------------------------------------0 SELEC‎T STATE‎M ENT Optim‎i zer=CHOOS‎E (Cost=7 Card=1)1 0 SORT (AGGRE‎G ATE)2 1 BITMA‎P CONVE‎R SION‎(COUNT‎)3 2 BITMA‎P INDEX‎(FAST FULL SCAN) OF 'IDX_B‎M P_EM‎P_SEX‎'Stati‎s tics‎----------------------------------------------------------0 recur‎s ive calls‎0 db block‎gets12 consi‎s tent‎gets8 physi‎c al reads‎0 redo size370 bytes‎sent via SQL*Net to clien‎t425 bytes‎recei‎v ed via SQL*Net from clien‎t2 SQL*Net round‎t rips‎to/from clien‎t0 sorts‎(memor‎y)0 sorts‎(disk)1 rows proce‎s sedSQL> analy‎z e table‎emplo‎y ee delet‎e stati‎s tics‎;表已分析。

Oracle按不同时间分组统计1、按年select to_char(record_date,'yyyy'), sum(col_8) as total_moneyfrom table_namewhere group by to_char(record_date,'yyyy')2、按⽉select to_char(record_date,'yyyy-mm'), sum(col_8) as total_moneyfrom table_namewhere group by to_char(record_date,'yyyy-mm')3、按季select to_char(record_date,'yyyy-Q'), sum(col_8) as total_moneyfrom table_namewhere group by to_char(record_date,'yyyy-Q')4、按周select to_char(record_date,'yyyy-IW'), sum(col_8) as total_moneyfrom table_namewhere group by to_char(record_date,'yyyy-IW')5、按⼩时select to_char(record_date,'yyyy-mm-dd hh24'), sum(col_8) as total_moneyfrom table_namewhere group by to_char(record_date,'yyyy-mm-dd hh24')6、按分钟select to_char(record_date,'yyyy-mm-dd hh24:mi'), sum(col_8) as total_moneyfrom table_namewhere group by to_char(record_date,'yyyy-mm-dd hh24:mi')SELECT trunc(sysdate-1) + (ROWNUM - 1) / 24 / 60 AS STAT_TIMEFROM DUAL dCONNECT BY ROWNUM <= 1440 +(trunc(sysdate, 'MI') - (trunc(sysdate))) * 24 * 607、按5分钟SELECT trunc(sysdate) + (ROWNUM - 1) / 24/12 AS STAT_TIMEFROM DUALCONNECT BY ROWNUM <=(trunc(sysdate, 'MI') - (trunc(sysdate))) * 24 * 12按5分钟统计，中间没数据的时间会缺少select to_char(j.record_date,'yyyymmdd') as st_date, sum(j.col_8*j.col_9*j.col_10) as mer_value, to_char(j.record_date,'hh24') as STAT_HOUR, floor(to_char(j.record_date,'mi')/5) as min_flag, '昨⽇'as report_namefrom c , jwhere c.col_4 = j.col_4 and c.col_5 = j.col_5 and c.col_6 = j.col_6and j.actiontype = 217and j.record_date between trunc(sysdate-1) and trunc(sysdate)group by to_char(j.record_date,'yyyymmdd'), to_char(j.record_date,'hh24'), floor(to_char(j.record_date,'mi')/5) ORDER BY st_date, STAT_HOUR, min_flagselect STAT_TIME tdate,'test' app_id,goods_type, --c.col_4 asgoods_id, --c.col_5 asgoods_currency_type --c.col_6 asfrom(SELECT trunc(sysdate-1) + (ROWNUM - 1) / 24 / 12 AS STAT_TIMEFROM DUAL dCONNECT BY ROWNUM <= 288 +(trunc(sysdate, 'MI') - (trunc(sysdate))) * 24 * 12 + 1),(selectt.goods_type,t.goods_id,t.goods_currency_typefrom mid_minutes twhere tdate >= trunc(sysdate) -1and app_id = 'test'group by t.goods_type,t.goods_id,t.goods_currency_type) c8、按⼗分钟SELECT trunc(sysdate) + (ROWNUM - 1) / 24/6 AS STAT_TIMEFROM DUALCONNECT BY ROWNUM <=(trunc(sysdate, 'MI') - (trunc(sysdate))) * 24 * 6按5分钟统计，把时间转换成分钟准点函数create or replace function trunc_minute(v_date date) return date asbeginreturn to_number(trunc(to_char(v_date, 'mi')/5))*5/(24*60) + trunc(v_date, 'hh24'); end;9、按半⼩时select to_date(to_char(j.tdate, 'yyyy-mm-dd hh24:') || casewhen to_char(j.tdate, 'mi') < '30' then'00'else'30'end,'yyyy-mm-dd hh24:mi') as tdate,j.goods_id,sum(j.buy_count) as buy_count,sum(j.total_price) as total_pricefrom tmp_goods_mis jwhere j.tdate >= trunc(sysdate) - 1group by j.goods_idto_char(j.tdate, 'yyyy-mm-dd hh24:') || casewhen to_char(j.tdate, 'mi') < '30' then'00'else'30'end。