数据库查询语句sql

                            数据库查询语句sql

SELECT * FROM emp;
SELECT * FROM dept;
--数据库语句中分号是分隔符，系统遇到分号才会执行语句

--单行注释

/*
多行注释
*/

/*
select 列1，列2 ，……列n    --定义查询数据所在的列
from 表1，表2 ，……表n     --定义查询数据所在的表
where 限定条件1 and/or 限定条件2 …… 限定条件n     --定义查询数据的限定条件
group by 列 1，列 2 ，……列 n    --定义查询数据的分组方式
having 分组函数条件 1 and/or 分组函数条件2 ……     --定义查询数据分组函数的限定条件
order by 列1 [asc/desc] ，列2 [asc/desc] ，…… 列n [asc/desc]     --定义查询结果的排序方式
*/

第二章 sql基础

1.*表示所有列
SELECT * FROM emp;
--* 会降低查询效率，尽量避免使用

2.选择指定的列
SELECT ename,job,sal,deptno FROM emp;

3.对查询结果进行运算
SELECT ename,job,sal,12*sal FROM emp;

--对日期格式的数据可以做加减运算，表示加上或减去天数
SELECT ename,hiredate,hiredate+365 FROM emp;
--sysdate 表示当前系统时间
SELECT ename,hiredate,(SYSDATE-hiredate)/365,SYSDATE-30 FROM emp;

4.空值
--当空值参与运算时，得到的结果也是空值
SELECT ename,sal,comm,12*(sal+comm) FROM emp;

5.列的别名
/*
select 列名1 别名，列名2 别名 ……
select 列名1 as 别名，列名2 as 别名 ……

如果别名区分大小写或者包含特殊字符，使用双引号定义别名
select 列名1 “别名”，列名2 “别名” ……
*/
SELECT ename xingming,job zhiwei,deptno FROM emp;

--别名通常用来给计算结果加以说明
SELECT ename,sal,comm,12*(sal+comm) nianxin FROM emp;

SELECT ename,sal,comm,12*(sal+comm) "nian xin" FROM emp;
--双引号只出现在列的别名中，在 sql 语句中其他任何地方都不会再出现双引号

6.连接符||
SELECT ename||job FROM emp;

7.字符串
SELECT 50,SYSDATE,'I am the king' FROM emp;
--字符串必须加单引号
SELECT 'ename',job FROM emp;

SELECT ename||' is a '||job FROM emp;

8.重记录
SELECT deptno FROM emp;

--去重 distinct
SELECT DISTINCT deptno FROM emp;
SELECT DISTINCT job FROM emp;

--普通列不能和去重列一起查询
--SELECT ename,DISTINCT job FROM emp;

--多列去重
SELECT DISTINCT deptno,job FROM emp;

第三章 限定和排序

1.where 字句
SELECT ename,job,sal,deptno FROM emp
WHERE deptno = 10;

SELECT ename,job,sal,deptno FROM emp
WHERE sal > 2000;

SELECT ename,job,sal,deptno FROM emp
WHERE job = 'CLERK';
--字符串加单引号，区分大小写

--where 字句中不能出现列的别名
SELECT ename,job,sal,12*sal nianxin FROM emp
WHERE 12*sal > 30000;

--where 子句中的列可以不出现在 select ，为了结果的可读性最好选择 where 子句中的条件查询
SELECT ename,job,deptno FROM emp
WHERE deptno = 30;

2.比较运算符

--between 最小值 and 最大值，范围匹配
SELECT ename,job,sal,deptno FROM emp
WHERE sal BETWEEN 1500 AND 3000;

--in() 列表匹配
SELECT ename,job,sal,deptno FROM emp
WHERE job IN ('CLERK','SALESMAN','PRESIDENT');

--like 模糊匹配字符串
--_ 表示任意1个字符
--% 表示任意个任意字符
SELECT * FROM emp
WHERE ename LIKE 'S%';   --开头为S

SELECT * FROM emp         
WHERE ename LIKE '%S';  --结尾为S

SELECT * FROM emp
WHERE ename LIKE '%S%';  --包含S

SELECT * FROM emp
WHERE ename LIKE '_A%';  -- 第二个字符为A 

--is null 匹配空值
SELECT ename,job,sal,comm FROM emp
WHERE comm IS NULL;

3.逻辑运算符

--and 满足所有条件才会返回结果
SELECT ename,job,sal,deptno FROM emp
WHERE deptno = 20
AND sal > 2500;

--or 满足任意一个条件就会返回结果
SELECT ename,job,sal,deptno FROM emp
WHERE deptno = 20
OR sal > 2500;

--not 返回不满足条件的结果
SELECT ename,job,sal,deptno FROM emp
WHERE job NOT IN('SALESMAN','MANAGER');

4.运算优先级：数学运算>比较运算符>not>and>or
SELECT * FROM emp
WHERE job IN ('SALESMAN','CLERK')
OR sal > 2000
AND deptno = 20;

SELECT * FROM emp
WHERE (job IN ('SALESMAN','CLERK')
OR sal > 2000)
AND deptno = 20;

5.order by子句排序
--order by 子句永远在查询语句的最后
--asc 升序，默认可以不写
--desc 降序
SELECT * FROM emp
ORDER BY sal;

SELECT * FROM emp
WHERE deptno = 30
ORDER BY sal DESC;

--不仅可以对数字排序，也可以对字符和日期排序
SELECT * FROM emp
ORDER BY hiredate;

SELECT * FROM emp
ORDER BY ename DESC;

--可以对多列进行排序
SELECT ename,job,sal,deptno FROM emp
ORDER BY deptno,sal DESC;

--order by 后面的列可以不出现在 select ，为了提高结果的可读性最好选择排序的列
SELECT ename,job,sal FROM emp
ORDER BY deptno;

--可以使用列的别名排序
SELECT ename,job,sal,12*sal nianxin FROM emp
ORDER BY nianxin DESC;

练习

1.查询emp表，显示薪水大于2000，且工作类别是MANAGER的雇员信息
SELECT * FROM emp
WHERE sal > 2000
AND job = 'MANAGER';

2.查询emp表，显示年薪大于30000，工作类别不是MANAGER的雇员信息
SELECT ename,job,sal,12*sal nianxin FROM emp
WHERE 12*sal > 30000
AND job <> 'MANAGER';

3.查询emp表， 显示薪水在1500到3000之间，工作类别以“M”开头的雇员信息
SELECT * FROM emp
WHERE sal BETWEEN 1500 AND 3000
AND job LIKE 'M%';

4.查询emp表，显示佣金为空并且部门号为20或30的雇员信息(佣金=薪水SAL+津贴COMM)  
SELECT ename,job,sal,comm,sal+comm yongjin,deptno FROM emp
WHERE sal+comm IS NULL
AND deptno IN (20,30);

5.查询emp表，显示佣金不为空或者部门号为20的雇员信息，要求按照薪水降序排列 (佣金=薪水+津贴)
SELECT ename,sal,comm,sal+comm yongjin,deptno FROM emp
WHERE sal+comm IS NOT NULL
OR deptno = 20
ORDER BY sal DESC;

6.查询emp表，显示年薪大于30000工作类别不是MANAGER，
且部门号不是10或40的雇员信息，要求按照雇员姓名进行排列
SELECT ename,job,sal,12*sal nianxin,deptno FROM emp
WHERE 12*sal > 30000
AND job <> 'MANAGER'
AND deptno NOT IN (10,40)
ORDER BY ename;

第四章 单行函数

1.函数
f(x)=3x+4
f(2)=10

f1(x,y)=5x+2y
f1(3,4)=23

2.字符函数

--upper(x) 将字符串 x 转换成大写
SELECT UPPER('abcdefg') FROM emp;
--dual 是系统默认的一张空表，用来做辅助的查询和运算
SELECT UPPER('abcdefg') FROM dual;

SELECT * FROM emp
WHERE UPPER(job) = UPPER('clerk');

--lower(x) 将字符串 x 转换成小写
SELECT LOWER('ABCDEFG') FROM dual;
SELECT LOWER(ename),LOWER(job) FROM emp;
SELECT * FROM emp
WHERE LOWER(job) = 'salesman';

--initcap(x) 将字符串 x 首字母转换成大写，其他字母小写
SELECT INITCAP('abcdEFG') FROM dual;
SELECT INITCAP(ename),INITCAP(job) FROM emp;

--length(x) 返回字符串 x 的长度
SELECT LENGTH('abcdefg') FROM dual;
SELECT ename,LENGTH(ename) FROM emp;
SELECT * FROM emp
WHERE LENGTH(ename) = 5;

--nvl(x,y) 判断 x 的值，如果 x 不为空则返回 x ，如果 x 为空则返回 y
SELECT ename,sal,comm,12*(sal+NVL(comm,0)) FROM emp;

--x 和 y 必须是相同的数据类型
--SELECT ename,job,NVL(mgr,'BOSS') FROM emp;

--nvl2(x,y,z) 判断 x 的值，如果 x 不为空则返回 y ，如果 x 为空则返回 z
SELECT ename,sal,comm,12*(sal+NVL2(comm,comm,0)) FROM emp;

SELECT ename,job,mgr,NVL2(mgr,'EMPLOYEE','BOSS') FROM emp;
--y 和 z 数据类型必须一致

--replace(x,y,z) 在字符串 x 中查找字符串 y 替换为字符串 z
SELECT REPLACE('abcdefg','cd','xyz') FROM dual;
SELECT ename,REPLACE(ename,'A','wwwwwww') FROM emp;

--substr(x,m,n) 从字符串 x 的第 m 个字符开始截取，截取长度为 n 的子字符串
SELECT SUBSTR('abcdefg',2,3) FROM dual;
SELECT ename,SUBSTR(ename,2,2) FROM emp;

--n 可以不写，如果不写默认截取后面所有的字符串
SELECT ename,SUBSTR(ename,3) FROM emp;
SELECT ename,SUBSTR(ename,LENGTH(ename)-1) FROM emp;

--如果 m 为负，从右向左数
SELECT ename,SUBSTR(ename,-2) FROM emp;

--lpad(x,n,y) 使用字符串 y 将字符串 x 从左边补齐到长度为 n 的字符串
SELECT LPAD('abcdefg',15,'w') FROM dual;
SELECT LPAD(ename,10,'name') FROM emp;

--y 可以不写，如果不写默认用空格补齐
SELECT LPAD('abcdefg',15) FROM dual;
SELECT LPAD(ename,10) FROM emp;

--当 n 小于 x 的长度时，会截取字符串
SELECT LPAD(ename,4) FROM emp;

--rpad(x,n,y) 使用字符串 y 将字符串 x 从右边补齐到长度为 n 的字符串
SELECT RPAD('abcdefg',15,'w') FROM dual;
SELECT RPAD(ename,10,'name') FROM emp;

--y 可以不写，如果不写默认用空格补齐
SELECT RPAD('abcdefg',15) rp FROM dual;
SELECT RPAD(ename,10) rp FROM emp;

--当 n 小于 x 的长度时，会截取字符串
SELECT RPAD(ename,4) FROM emp;

--ltrim(x,y) 将字符串 x 左边的子字符串 y 去掉
SELECT LTRIM('abcbcxbacbacbdefg','abc') FROM dual;
--y 中的字符串没有先后顺序，只要 x 的左边有 y 中的任意字符都会被去掉
SELECT ename,LTRIM(ename,'AD') FROM emp;

--y 可以不写，如果不写默认去掉空格
SELECT LTRIM('       abcdefg       ') FROM dual;

--rtrim(x,y) 将字符串 x 右边的子字符串 y 去掉
SELECT RTRIM('abcbcxbacbacbdefgabcbac','abc') FROM dual;
--y 中的字符串没有先后顺序，只要 x 的左边有 y 中的任意字符都会被去掉
SELECT ename,RTRIM(ename,'AD') FROM emp;

--y 可以不写，如果不写默认去掉空格
SELECT RTRIM('       abcdefg       ') FROM dual;

--trim(leading/trailing/both y from x) 将字符串 y 从字符串 x 的左边 / 右边 / 两边 去掉
--leading 左边，trailing 右边
--both 两边，默认可以不写
SELECT ename,TRIM('S' FROM ename) FROM emp;
SELECT ename,RTRIM(LTRIM(ename,'S'),'S') FROM emp;

--y 只能有1个字符
--y 可以不写，如果不写默认去掉空格
SELECT TRIM('       abcdefg     ') tr FROM dual;

--concat(x,y) 将字符串 x 和字符串 y 连接起来，等于||
--concat 只允许 2 个参数
SELECT CONCAT(ename,job) FROM emp;
SELECT CONCAT(CONCAT(ename,' is a '),job) FROM emp;

--instr(x,y,m,n) 返回 字符串 y 从字符串 x 第 m 个位置出现第 n 次的位置
SELECT INSTR('abcabcfdfdsfdsbfb','d',3,3) FROM dual;
SELECT ename,INSTR(ename,'A',1,1) FROM emp;

--n 可以不写，如果不写默认返回第一次出现的位置
SELECT ename,INSTR(ename,'A',2) FROM emp;

--m 可以不写，如果不想默认从第一个字符开始查找
SELECT ename,INSTR(ename,'A') FROM emp;

SELECT * FROM emp
WHERE ename LIKE '%A%';

SELECT * FROM emp
WHERE INSTR(ename,'A') > 0;

2.数字函数

--floor(n) 返回小于等于数字 n 的最大整数，向下取整
SELECT FLOOR(123),FLOOR(123.456),FLOOR(-123.456) FROM dual;

--mod(m,n) 返回 m 除以 n 的余数
SELECT MOD(8,3),MOD(7.5,2.3),MOD(-9,-2) FROM dual;
--n 可以为 0 ，n 为 0 返回 m

--round(m,n) 返回 数字 m 精确到小数点后 n 位 的数字
SELECT ROUND(123.456,1),ROUND(123.456,2),ROUND(123.456,0) FROM dual;

--当 n 为负数时，精确到小数点左边 n 位
SELECT ROUND(127.456,-1),ROUND(183.456,-2),ROUND(623.456,-3) FROM dual;

--n 可以不写，如果不写默认精确到个位
SELECT ROUND(123.456) FROM dual;

--trunc(m,n) 返回 数字 m 截取到小数点后 n 位 的数字
SELECT TRUNC(123.456,1),TRUNC(123.456,2),TRUNC(123.456,0) FROM dual;

--当 n 为负数时，截取到小数点左边 n 位
SELECT TRUNC(127.456,-1),TRUNC(183.456,-2),TRUNC(623.456,-3) FROM dual;

--n 可以不写，如果不写默认截取到个位
SELECT TRUNC(123.456) FROM dual;

4.日期函数
SELECT * FROM emp;
SELECT SYSDATE FROM dual;

--add_months(x,n) 给日期 x 加上 n 个月
SELECT ADD_MONTHS(SYSDATE,5) FROM dual;
SELECT ename,hiredate,ADD_MONTHS(hiredate,3) FROM emp;

--n 可以为负数，如果为负减去 n 个月
SELECT ADD_MONTHS(SYSDATE,-5) FROM dual;

--last_day(x) 返回 日期 x 所在月份的最后一天
SELECT LAST_DAY(SYSDATE) FROM dual;
SELECT ename,hiredate,LAST_DAY(hiredate) FROM emp;

--next_day(x,date) 返回日期 x 之后的下一个星期几
SELECT NEXT_DAY(SYSDATE,'SATURDAY') FROM dual;
SELECT ename,hiredate,NEXT_DAY(hiredate,'FRIDAY') FROM emp;

SELECT LAST_DAY(ADD_MONTHS(SYSDATE,7)) FROM dual;

5.转换函数

--隐含的数据类型转换：系统默认完成的数据类型转换，主要是数字和字符之间的转换，由系统自动完成
SELECT * FROM emp
WHERE deptno = '10';

--清楚的数据类型转换：使用转换函数

--to_char(x,'format') 将日期 x 转换成格式为 format 的字符串
--YYYY 表示四位年份数字，YEAR 表示年份的拼写
--MM 表示两位月份数字，MONTH 表示月份的拼写
--DD 表示两位日期数字，DAY 表示日期的拼写（星期）
--可以使用分隔符将年月日隔开，可以是，：；-_空格
SELECT SYSDATE,TO_CHAR(SYSDATE,'YYYYMMDD') FROM dual;
SELECT ename,hiredate,TO_CHAR(hiredate,'YYYY:MM:DD') FROM emp;
SELECT ename,hiredate,TO_CHAR(hiredate,'YEAR MONTH DAY') FROM emp;

--数字和拼写可以混合使用
SELECT ename,hiredate,TO_CHAR(hiredate,'YYYY MONTH DD') FROM emp;

--年月日的顺序可以自定义
SELECT ename,hiredate,TO_CHAR(hiredate,'MONTHDD-YYYY') FROM emp;

--可以在格式前面加 fm 去掉多余的 0 或者空格
SELECT ename,hiredate,TO_CHAR(hiredate,'fmMONTH-DD-YYYY') FROM emp;

--年月日可以不写全
SELECT ename,hiredate,TO_CHAR(hiredate,'YYYY') FROM emp;
SELECT ename,hiredate,TO_CHAR(hiredate,'MM') FROM emp;
SELECT ename,hiredate,TO_CHAR(hiredate,'MM-DD') FROM emp;

--to_char(m,'format') 将数字 m 转换成格式为 format 的字符串
--9 代表一位数字
--0 代表一位数字，如果位数不足用 0 补齐
--， 代表分隔符，位置可以自定义
--. 代表小数点，只能出现一次，不能在开头和结尾
--$ 代表货币，L 代表本地货币
SELECT ename,TO_CHAR(sal,'999,999.99') FROM emp;
SELECT ename,TO_CHAR(sal,'000,000.00') FROM emp;
SELECT ename,TO_CHAR(sal,'99,99,99.99') FROM emp;
SELECT ename,TO_CHAR(sal,'$999,999.99') FROM emp;

--to_date(x,'format') 将字符串 x 转换成日期
SELECT TO_DATE('1997-07-01','YYYY-MM-DD') FROM dual;
SELECT TO_DATE('19970701','YYYYMMDD') FROM dual;

--年月日的顺序可以自定义
SELECT TO_DATE('19970701','YYYYDDMM') FROM dual;

--年月日也可以只定义部分，最终结果是一个完整的日期格式
SELECT TO_DATE('1997','YYYY') FROM dual;
--如果只转换年份，会用当前系统所在月份的第一天来补齐日期

SELECT TO_DATE('12','MM') FROM dual;
--如果只转换月份，会用当前系统所在年份和当月的第一天来补齐

SELECT TO_DATE('12','DD') FROM dual;
--如果只转换日期，会用当前系统所在月份来补齐

SELECT TO_DATE('0809','MMDD') FROM dual;

6.decode函数
/*
decode(x,value1,result1,
               value2,result2,
               ...
               valueN,resultN,
               default)
判断 x 的值，如果 x=value1 ，返回 result1，
                   如果 x=value2 ，返回 result2 ，
                   ……
                   如果 x=valueN ，返回 resultN ，
                   如果 x 不等于以上任何值，返回 default
*/
SELECT ename,job,sal,deptno,
DECODE(deptno,10,sal+200,
                          20,sal+300,
                          30,sal+500,
                          sal+100) addsal
FROM emp;

练习

1.用一个sql语句完成在字符串”hello”左右各添加5个*，使其最终返
     回*****hello*****(用三种方法实现)
SELECT '*****'||'hello'||'*****' FROM dual;
SELECT CONCAT('*****',CONCAT('hello','*****')) FROM dual;
SELECT RPAD(LPAD('hello',10,'*'),15,'*') FROM dual;

2.写一条sql语句返回”abcdefg”的后三位字符“efg”,并转换成大写
SELECT UPPER(SUBSTR('abcdefg',5)) FROM dual;
SELECT UPPER(LTRIM('abcdefg','abcd')) FROM dual;

3.查询emp表，显示删除掉第一个字符后的员工姓名（如员工ALLEN，显示为LLEN）（2种方法实现）
SELECT ename,SUBSTR(ename,2) FROM emp;
SELECT ename,LTRIM(ename,SUBSTR(ename,1,1)) FROM emp;

4.查询emp表，显示姓名中不包含字符’A’的员工信息
SELECT * FROM emp
WHERE ename NOT LIKE '%A%';

SELECT * FROM emp
WHERE INSTR(ename,'A') = 0;

5.查询emp表，使用员工姓名的第一个字符将员工姓名从左边补齐到长度为10
SELECT ename,LPAD(ename,10,SUBSTR(ename,1,1)) FROM emp;

6.查询emp表，显示6月份入职的员工信息
SELECT ename,hiredate,TO_CHAR(hiredate,'MM') FROM emp
WHERE TO_CHAR(hiredate,'MM') = 6;

7.查询在某个月倒数第三天入职的员工信息
SELECT * FROM emp
WHERE hiredate = LAST_DAY(hiredate)-2;

8.写一条sql语句查询员工编号、员工姓名、工资、部门号，
要求当员工在10号部门时，显示’财务部’，20号部门时显示’研发部’，
30号部门时显示’销售部’，其余部门显示’未知部门’
SELECT empno,ename,sal,deptno,
DECODE(deptno,10,'Finance',
                          20,'Research',
                          30,'Sales',
                          'Unknown') deptname
FROM emp;

第五章 多表查询

1.笛卡尔结果
SELECT ename,job,sal,dname,loc FROM emp,dept;
SELECT * FROM dept;

2.多表查询
--多表查询时，必须使用表名对列名进行限定
--如果不限定列名，会在所有的表中去查找该列，会降低查询效率
--多表查询时，from 换行
SELECT emp.ename,emp.job,emp.sal,emp.deptno,dept.deptno,dept.dname,dept.loc
FROM emp,dept;

3.等值连接
--外键和主键的等值对应
SELECT emp.ename,emp.job,emp.sal,emp.deptno empdno,dept.deptno deptdno,dept.dname,dept.loc
FROM emp,dept
WHERE emp.deptno = dept.deptno;
--使用别名来区别同名的列

--多表查询时，必须使用表的别名对列名进行限定
SELECT e.ename,e.job,e.sal,e.deptno eno,d.deptno dno,d.dname,d.loc
FROM emp e,dept d
WHERE e.deptno = d.deptno;

SELECT e.ename,e.job,e.sal,e.deptno eno,d.deptno dno,d.dname,d.loc
FROM emp e,dept d
WHERE e.deptno = d.deptno
AND e.sal > 2000;

4.不等值连接
SELECT * FROM salgrade;
SELECT e.ename,e.sal,s.grade,s.losal,s.hisal
FROM emp e,salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal;

5.自连接
SELECT * FROM emp;

SELECT w.ename,w.mgr,m.empno,m.ename
FROM emp w,emp m
WHERE w.mgr = m.empno;

6.外连接
--查询员工信息和他所在部门的信息，没有员工的部门也显示
SELECT * FROM emp;
SELECT * FROM dept;

--如果在多表查询时，需要显示某张表的所有数据，包括不符合连接条件的数据，使用外连接
--如果要显示某张表的所有数据，把 (+) 放在连接条件的另一侧
SELECT e.*,d.*
FROM emp e,dept d
WHERE e.deptno(+) = d.deptno;

--显示员工和其经理的名字，没有经理的员工也显示
SELECT w.ename,w.mgr,m.empno,m.ename
FROM emp w,emp m
WHERE w.mgr = m.empno(+);

--全外连接
--查询员工信息和他所在部门的信息，没有员工的部门也显示，没有部门的员工也显示
SELECT e.*,d.*
FROM emp e FULL OUTER JOIN dept d
ON e.deptno = d.deptno;

练习

1.查询EMP、DEPT表，输出的列包含员工姓名、工资、部门编号、部门名称、部门地址.
SELECT e.ename,e.sal,e.deptno,d.dname,d.loc
FROM emp e,dept d
WHERE e.deptno = d.deptno;

2.查询工资等级为3/4/5级的员工姓名，工资，工资等级
SELECT e.ename,e.sal,s.grade
FROM emp e,salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal
AND s.grade >= 3;

3.显示10号部门的员工姓名，工资，工资等级，部门名称
SELECT e.ename,e.sal,s.grade,d.dname
FROM emp e,dept d,salgrade s
WHERE e.deptno = d.deptno
AND e.sal BETWEEN s.losal AND s.hisal
AND e.deptno = 10;

4.查询emp表，显示员工姓名及其经理的姓名，没有经理的员工也需要显示
SELECT w.ename,m.ename
FROM emp w,emp m
WHERE w.mgr = m.empno(+);

5.列出EMP表中部门名称和这些部门的员工信息，同时列出那些没有员工的部门。
SELECT d.dname,e.*
FROM emp e,dept d
WHERE e.deptno(+) = d.deptno;

第六章 分组函数

1.分组函数

--avg() 求平均值
SELECT AVG(sal) FROM emp;

--sum() 求和
SELECT SUM(sal) FROM emp;

--count() 计数
SELECT COUNT(empno) FROM emp;
SELECT COUNT(*) FROM emp;

--max() 最大值， min() 最小值
SELECT MAX(sal),MIN(sal) FROM emp;

--对日期和字符串也能计算最大和最小值
SELECT MAX(ename),MIN(ename) FROM emp;
SELECT MAX(hiredate),MIN(hiredate) FROM emp;

--分组函数会忽略空值
SELECT AVG(comm),COUNT(comm) FROM emp;
SELECT SUM(comm)/COUNT(empno),SUM(comm)/COUNT(comm) FROM emp;

--使用 nvl 函数处理空值
SELECT AVG(NVL(comm,0)),COUNT(NVL(comm,0)) FROM emp;

--使用分组函数时可以使用 where 子句对分组计算的范围进行限定
SELECT AVG(sal),COUNT(empno) FROM emp
WHERE deptno = 10;

SELECT AVG(sal),COUNT(empno) FROM emp
WHERE job = 'SALESMAN';

SELECT AVG(sal),COUNT(empno) FROM emp
WHERE sal > 1500;

--使用分组函数时，能不能选择普通列？
--SELECT deptno,AVG(sal) FROM emp;

2.group by子句
--group by 跟列名，当该列的数值想等时看作为一组
--group by 后面的普通列可以出现在 select 后面，让分组函数的结果可读性更高
SELECT deptno,AVG(sal),COUNT(empno) FROM emp
GROUP BY deptno;

--当 select 后面既有普通列又有分组函数时，必须使用 group by 子句
--select 后面既有普通列又有分组函数时，普通列必须出现在 group by 子句

--计算每个职位的人数和工资之和
SELECT job,COUNT(empno),SUM(sal) FROM emp
GROUP BY job;

--多列分组
SELECT deptno,job,MAX(sal),MIN(sal),COUNT(empno) FROM emp
GROUP BY deptno,job
ORDER BY deptno;

--对分组函数的结果进行限定时，不能使用  where 子句，where 子句中不能出现分组函数
/*
SELECT deptno,AVG(sal) FROM emp
WHERE AVG(sal) > 2500
GROUP BY deptno;
*/

3.having子句
SELECT deptno,AVG(sal) FROM emp
GROUP BY deptno
HAVING AVG(sal) > 2500;

--使用组函数时（group by 和 having 子句），select 后面可以没有分组函数
SELECT deptno FROM emp
GROUP BY deptno
HAVING SUM(sal) > 10000;

4.组函数嵌套
SELECT AVG(AVG(sal)) FROM emp
GROUP BY deptno;

练习

1.查询EMP表，输出每个职位的职位名称及平均工资，按平均工资升序排列.
SELECT job,AVG(sal) FROM emp
GROUP BY job
ORDER BY AVG(sal);

2.查询EMP表，输出每个部门的各个职位的平均工资，并按部门编号升序、平均工资降序排序。
SELECT deptno,job,AVG(sal) FROM emp
GROUP BY deptno,job
ORDER BY deptno,AVG(sal) DESC;

3.查询EMP表，要求写一条sql语句统计出如下图所示结果

  10号部门人数 20号部门人数  30号部门人数
  ------------     ------------       ------------
            4                         4                       6

SELECT deptno,COUNT(empno) FROM emp
GROUP BY deptno;

SELECT SUM(DECODE(deptno,10,1,0)) dept10,
SUM(DECODE(deptno,20,1,0)) dept20,
SUM(DECODE(deptno,30,1,0)) dept30
FROM emp;

还有一种用的很少的拼接(uniqe)，把我们查询到的结果拼接起来，已达到我们展示的效果：对于这钟用法我们，是查询不相关的表

得到的结果。忘记了这个单词了 ，想起了再说 。。。。。

再oracle还有一个用发法   转载的  下面

oracle wm_concat(column)函数使我们经常会使用到的，下面就教您如何使用 oraclewm_concat(column)函数实现字段合并，如果您对oracle wm_concat(column)函数使用方面感兴趣的话，不妨一看。

shopping:

-----------------------------------------

u_id       goods            num

------------------------------------------

1                苹果                2

2                 梨子               5

1                 西瓜               4

3                 葡萄               1

3                香蕉                1

1               橘子                 3

=======================

想要的结果为:

--------------------------------

u_id          goods_sum

____________________

1              苹果,西瓜,橘子

2              梨子

3              葡萄,香蕉

---------------------------------

1. select u_id, wmsys.wm_concat(goods) goods_sum  
2.  
3. from shopping  
4.  
5. group by u_id  

想要的结果2:

--------------------------------

u_id          goods_sum

____________________

1              苹果(2斤),西瓜(4斤),橘子(3斤)

2              梨子(5斤)

3              葡萄(1斤),香蕉(1斤)

---------------------------------

使用oracle wm_concat(column)函数实现：

1. select u_id, wmsys.wm_concat(goods || '(' || num || '斤)' ) goods_sum  
2.  
3. from shopping  
4.  
5. group by u_id