sql 语法

[sql]代码库

SQL语句是不区分大小写的,但是为了增加可读性,可以将关键字全部大写,
非关键字全部小写.SELECT SYSDATE FROM dual

SQL语句根据功能有不同分类DDL语句(数据定义语言)用于操作数据库对象.数据库对象
包括:表,视图,索引,序列

1:创建表
CREATE TABLE employee(
	id NUMBER(4),
	name VARCHAR2(20),
	gender CHAR(1),
	birth DATE,
  salary NUMBER(6,2),
  job VARCHAR2(30),
  deptno NUMBER(2)
)




查看表的结构:
DESC employee

删除表:
DROP TABLE employee

DEFAULT关键字:
在数据库中,所有的数据类型默认值都是NULL.但是可以通过使用DEFAULT关键字
为指定字段单独设置默认值.

在数据库中,字符串的字面量是使用单引号括起来的.这一点需要注意.SQL语句虽然
不区分大小写,但是字符串内容是区分大小写的








NOT NULL约束
非空约束要求指定字段在任何情况下值不允许为空.
CREATE TABLE employee(
	id NUMBER(4),
	name VARCHAR2(20) NOT NULL,
	gender CHAR(1) DEFAULT 'M',
	birth DATE,
  salary NUMBER(6,2) DEFAULT 3000,
  job VARCHAR2(30),
  deptno NUMBER(2)
)


2:修改表
修改表可以修改表的名字和表的结构





2.1:修改表名:
RENAME old_name TO new_name
将employee改名为myemp

RENAME employee TO myemp

DESC myemp


2.2:修改表结构
添加新字段,删除现有字段,修改现有字段









2.2.1:添加新字段
向myemp表中添加字段hiredate

ALTER TABLE myemp 
ADD(
  hiredate DATE
)

DESC myemp

2.2.2:删除字段
将myemp表中的hiredate删除

ALTER TABLE myemp
DROP(hiredate)


2.2.3:修改现有字段
修改字段可以修改字段的类型,长度添加默认值或者约束条件.但是修改字段尽量在表中没有数据的情况下进行,否则尽量不要修改字段类型,改长度也尽量只增不减.否则可能修改失败.

ALTER TABLE myemp
MODIFY(
  job VARCHAR2(40)
)

DESC myemp


DML语句(数据操作语言)
DML用于对表中数据进行增,删,改操作

1:INSERT,插入数据

INSERT INTO myemp
(id,name,job,deptno)
VALUES
(1,'JACK','CLERK',10)

COMMIT

SELECT * FROM myemp


INSERT语句中的字段名可以忽略,但是忽略后就是全列插入.
INSERT INTO myemp
VALUES
(2,'rose','M',SYSDATE,5000,
 'CLERK',20)

SELECT * FROM myemp

插入日期类型数据
可以直接给定字符串,但是格式必须是:'DD-MON-RR',由于月用的是简拼,不同
语言地区这里会有出入,英语地区是以英文字母缩写形式,如:'01-SEP-03'
而中文环境下为:'01-9月-03'所以不建议使用.
可以使用TO_DATE函数,这是数据库的一个内置函数,可以将一个字符串按照指定的日期
格式转换为DATE值.

INSERT INTO myemp
(id,name,job,deptno,birth)
VALUES
(3,'mark','CLERK',20,
 TO_DATE('1992-08-03','YYYY-MM-DD')
)
SELECT * FROM myemp



2:UPDATE,更新数据将rose的性别改为'F'

UPDATE myemp
SET gender='F',salary=6000
WHERE name='rose'

修改的时候通常要使用WHERE子句来添加过滤条件,这样仅会将满足条件的记录进行
修改,若不添加过滤条件则是全表更新,这样的情况实际比较少.


3:DELETE,删除语句
删除rose
DELETE FROM myemp
WHERE name='rose'


SELECT * FROM myemp



DQL语句
SELECT语句用于查询表中数据.SELECT语句至少包含两个子句:

SELECT子句:用于指定查询的内容,在SELECT子句中可以出现表中的字段,函数或表达式

FROM子句:用于指定数据来源的表

查看emp表中数据
SELECT ename,job,sal,deptno 
FROM emp
WHERE sal>2000

添加了WHERE子句后,可以指定过滤条件,这样只会将满足条件的记录查询出来.

查看每个员工的年薪?
SELECT ename,sal,sal*12
FROM emp



字符串函数
1:CONCAT(char1,char2)
连接字符串

SELECT CONCAT(ename,sal)
FROM emp

SELECT 
  CONCAT(CONCAT(ename,'的工资是'),sal)
FROM emp

在数据库中可以使用"||"作为连接字符串的操作SELECT ename||'的工资是'||sal
FROM emp


2:LENGTH(char)
返回字符串的长度

查看每个员工名字的字符个数?
SELECT ename,LENGTH(ename)
FROM emp

3:LOWER,UPPER,INITCAP
将字符串转换为全大写,全小写,首字母大写

dual:伪表
当查询的数据与任何表无关时,可以使用伪表.伪表并不是一张真实存在的表,仅用来"凑数"
SELECT LOWER('HELLOWORLD'),
       UPPER('helloworld'),
       INITCAP('HELLO WORLD')
FROM dual

SELECT ename,sal,job,deptno
FROM emp
WHERE UPPER(ename)=UPPER('smith')


4:TRIM,LTRIM,RTRIM
去除字符串两边的重复字符,或单独去除左面,单独去除右面的字符

SELECT TRIM('e' FROM 'eeeeliteeee')
FROM dual

SELECT LTRIM('eeeeliteeee','e')
FROM dual


5:LPAD(char1,n,char2),RPAD
补位函数,可以将char1字符串达到n个字符长度,若char1不足是,左面(右面)补充若干
个char2字符来达到.若超过,则从左开始截取到n个字符返回.
SELECT ename,sal,LPAD(sal,5,' ')
FROM emp


6:SUBSTR(char,m[,n])
截取字符串从m开始截取n个字符n若不指定或者超过了实际可以截取
的字符数量时则是截取到末尾.m可以是负数,若是负数则是从倒数位置开始截取.
需要注意,数据库中下标从1开始!

SELECT 
  SUBSTR('thinking in java',-7,2)
FROM dual



7:INSTR(char1,char2[,m[,n]])
查找char2在char1中的位置m表示从第几个字符开始检索,不写默认为1
n表示第几次出现,不写默认为1
SELECT 
 INSTR('thinking in java','in',4,2)
FROM
 dual


数字函数
1:ROUND(m[,n])
对m进行四舍五入,保留到小数点后n位,若n不指定则默认为0.n若为负数则是保留10位以上的数字

SELECT ROUND(45.678, 2) 
FROM DUAL
SELECT ROUND(45.678, 0) 
FROM DUAL
SELECT ROUND(55.678, -2) 
FROM DUAL


2:TRUNC(m[,n])
截取数字,参数意义与ROUND一致
SELECT TRUNC(45.678, 2) 
FROM DUAL
SELECT TRUNC(45.678, 0) 
FROM DUAL
SELECT TRUNC(55.678, -1) 
FROM DUAL


3:MOD(m,n)
求余数,计算是使用m除以n得到若n为0则直接返回m
SELECT MOD(12,5)
FROM dual


4:CEIL,FLOOR
向上取整与向下取整
SELECT CEIL(45.678) FROM dual
SELECT FLOOR(45.678) FROM dual



日期函数
与日期相关的关键字:SYSDATE,SYSTIMESTAMP分别返回DATE类型与时间戳类型的
当前系统时间.

SELECT SYSDATE FROM dual
SELECT SYSTIMESTAMP FROM dual


1:TO_DATE函数
可以将一个字符串按照指定的日期格式转换为一个DATE值
SELECT 
 TO_DATE('2008-08-08 20:08:08',
         'YYYY-MM-DD HH24:MI:SS')
FROM
 dual

当日期格式中出现了非时期格式字符或符号之外的其他字符时,通常需要使用双引号
括起来.
SELECT 
 TO_DATE('2008年08月08日',
         'YYYY"年"MM"月"DD"日"')
FROM
 dual

日期类型可以进行计算:
1:对日期加减一个数字,等同于加减天数
2:两个日期向减,差为相差的天数

日期也可以比较大小,越晚的越大

明天的日期?
SELECT SYSDATE+1 FROM dual

查看每个员工入职到今天共多少天了?
SELECT ename,SYSDATE-hiredate
FROM emp


SELECT 
  TRUNC(SYSDATE-TO_DATE('1992-05-03','YYYY-MM-DD'))
FROM
  dual


2:TO_CHAR函数
TO_CHAR函数常用来将DATE按照指定的格式转换为字符串使用
SELECT
 TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS')
FROM
 dual

SELECT ename,sal,deptno,
 TO_CHAR(hiredate,'YYYY/MM/DD')
FROM
 emp


SELECT 
 TO_CHAR(
  TO_DATE('50-05-05','RR-MM-DD'),
  'YYYY-MM-DD'
 )
FROM
 dual


日期常用函数
1:LAST_DAY(date)
返回给定日期所在月的月底日期

查看当月月底日期?
SELECT LAST_DAY(SYSDATE)
FROM dual




2:ADD_MONTHS(date,i)
对给定的日期加上指定的月,若i为负数,则是减去指定的月

查看每个员工入职20周年纪念日?
SELECT 
 ename,hiredate,
 ADD_MONTHS(hiredate,12*20)
FROM
 emp


3:MONTHS_BETWEEN(date1,date2)
计算两个日期之间相差的月计算方式是用date1-date2得出的

查看每个员工入职至今多少个月了?
SELECT ename,MONTHS_BETWEEN(SYSDATE,hiredate)
FROM emp


4:NEXT_DAY(date,i)
返回给定日期第二天开始一周之内周几
对应的日期
i是数字:1-7,分别表示周日-周六

SELECT NEXT_DAY(SYSDATE,6)
FROM dual



5:LEAST,GREATEST
求最小值与最大值
凡是能比较大小的数据类型都可以使用这两个函数,对于日期而言,最小值求的
就是最早的日期,最大值求的就是最晚的日期.
SELECT 
 LEAST(SYSDATE,TO_DATE('2008-10-10',
                       'YYYY-MM-DD')) 
FROM dual


6:EXTRACT()
提取指定日期指定时间分量的值
SELECT EXTRACT(YEAR FROM SYSDATE)
FROM dual

查看1982年入职的员工?
SELECT ename,hiredate
FROM emp
WHERE EXTRACT(YEAR FROM hiredate)=1982



空值操作
CREATE TABLE student(
  id NUMBER(4), 
  name CHAR(20), 
  gender CHAR(1)
);

INSERT INTO student VALUES(1000, '李莫愁', 'F');

INSERT INTO student VALUES(1001, '林平之', NULL);

INSERT INTO student(id, name) VALUES(1002, '张无忌');


更新成NULL
UPDATE student
SET gender=NULL
WHERE id=1000

将性别为NULL的删除
在过滤条件中判断时,要使用:
IS NULL,IS NOT NULL

DELETE FROM student
WHERE gender IS NULL

SELECT * FROM student


空值操作:
字符串与NULL连接等于什么也没干数字与NULL运算结果还是NULL

SELECT ename||NULL
FROM emp

查看每个员工的总收入(工资+绩效)
SELECT ename,sal,comm,sal+comm
FROM emp

空值函数
1:NVL(arg1,arg2)
当arg1为NULL时,函数返回arg2的值若不为NULL,函数返回arg1.
所以NVL函数的意义是将NULL替换为一个非NULL值

查看每个员工的总收入(工资+绩效)
SELECT ename,sal,comm,
       sal+NVL(comm,0)
FROM emp

查看每个员工的绩效情况,即:绩效不为NULL的,则显示为"有绩效"
为NULL的则显示为"没有绩效"

2:NVL2(arg1,arg2,arg3)
当arg1不为NULL时,函数返回arg2arg1为NULL时,函数返回arg3

SELECT 
 ename,comm,
 NVL2(comm,'有绩效','没有绩效')
FROM
 emp

SELECT 
 ename,sal,comm,
 NVL2(comm,comm+sal,sal)
FROM
 emp




DQL 数据查询语言

在SELECT子句中可以为字段添加别名这样在结果集中对应的该字段的名字就是
这个别名.
当SELECT子句中的字段是函数或者表达式的时候,通常应当为其指定别名.

别名不区分大小写,也不能含有空格,若希望区分或含有空格,那么可以使用
双引号括起别名,这样就可以了.
SELECT ename,sal*12 "sal"
FROM emp


AND,OR用来连接多个条件
SELECT ename,sal,job
FROM emp
WHERE sal>1000
AND (job='SALESMAN'
OR job='CLERK')

AND的优先级是高于OR的,可以通过使用括号来提高OR的优先级.


LIKE关键字
LIKE用来模糊匹配字符串
支持两个通配符:
_:单一的一个字符
%:任意个字符(0-多个)

查看名字中第二个字母是A的员工?
SELECT ename,sal,job
FROM emp
WHERE ename LIKE '_A%'

SELECT ename,sal,job
FROM emp
WHERE ename LIKE '%A%K%'



IN(list),NOT IN(list)
判断是列表中的内容或不是列表中的内容IN,NOT IN经常用在子查询中.
查看职位是CLERK或SALESMAN的员工?
SELECT ename,job,sal
FROM emp
WHERE job IN ('CLERK','SALESMAN')


BETWEEN...AND...
判断在一个范围内

查看工资在1500到3000的员工?
SELECT ename,sal,job
FROM emp
WHERE sal BETWEEN 1500 AND 3000


ANY(list),ALL(list)
当判断大于,小于,大于等于,小于等于多个值的时候,要配合ANY,ALL使用
>ANY(list):大于其中之一(大于最小)
>ALL(list):大于所有(大于最大)
<ANY(list):小于其中之一(小于最大)
<ALL(list):小于所有(小于最小)
ANY,ALL用在判断子查询中,所以通常列表
中不会出现固定值(没意义).


DISTINCT关键字
去除结果集中指定字段有重复值的记录

查看公司有哪些职位?
SELECT DISTINCT job FROM emp

多字段去重时,不再保证每个字段一定没有重复记录,但是可以保证这些字段
值的组合没有重复记录.
SELECT DISTINCT job,deptno
FROM emp


排序操作
ORDER BY子句可以将结果集按照指定的字段进行升序或降序排序
ORDER BY子句必须定义在DQL语句的最后一个子句上.
其中:ASC表示升序(不写默认就是升序),
DESC为降序.

查看公司工资排名?
SELECT ename,sal
FROM emp
ORDER BY sal DESC

排序可以按照多字段排序,但是排序具有优先级,即:先按照第一个字段排序结果集
当第一个字段有重复值时,这些记录按照第二个字段排序,以此类推.

SELECT ename,deptno,sal
FROM emp
ORDER BY deptno DESC,sal DESC


排序的字段含有NULL值时,NULL被认定为最大值
SELECT ename,comm
FROM emp
ORDER BY comm


聚合函数
聚合函数又称为多行函数,分组函数聚合函数是用来统计结果集数据的.

其中有四个是对值的统计:MAX,MIN,SUM,AVG

还有一个是对记录数统计的:COUNT

查看公司的最高与最低工资是多少?
SELECT MAX(sal),MIN(sal) FROM emp

查看工资总和与平均工资?
SELECT SUM(sal),AVG(sal) FROM emp

查看公司共多少人?
SELECT COUNT(ename) FROM emp

聚合函数忽略NULL值.
SELECT SUM(comm) FROM emp
SELECT AVG(comm) FROM emp
SELECT AVG(NVL(comm,0)) FROM emp

求记录数的常见写法
SELECT COUNT(*) FROM emp
SELECT COUNT(1) FROM emp



分组
GROUP BY子句
GROUP BY允许我们按照指定字段对表中记录进行分组,原则是该字段值
一样的记录被看做一组.分组是为了配合聚合函数进行细分统计

查看每个部门的平均工资?
SELECT AVG(sal),deptno
FROM emp
GROUP BY deptno

当SELECT子句中有聚合函数时,那么凡不在聚合函数中的单独字段都必须出现在
GROUP BY子句中.

查看每个职位的最高工资?
SELECT MAX(sal),job
FROM emp
GROUP BY job

查看每个部门各多少人?
SELECT COUNT(*),deptno
FROM emp
GROUP BY deptno

查看部门的平均工资,前提是该部门
平均工资要高于2000

SELECT AVG(sal),deptno
FROM emp
WHERE AVG(sal)>2000
GROUP BY deptno

WHERE中不能使用聚合函数作为过滤条件,原因在于:过滤时机不对WHERE是在检索表中数据的时候进行过滤使用的.而聚合函数是作为统计使用的,那么统计的前提是有数据.而是否有数据是WHERE决定的.所以上面的需求应当是将表中数据都查询出来(WHERE已经过滤完毕)后才能进行分组,之后才能统计结果,然后才可以按照聚合函数的结果进行过滤.

HAVING子句
HAVING子句必须跟在GROUP BY子句之后即:不定义GROUP BY子句就不能单独定义
HAVING子句

HAVING子句添加过滤条件是用来过滤GOURP BY子句执行的分组的.即:只将满足HAVING子句条件的分组保留

SELECT AVG(sal),deptno
FROM emp
GROUP BY deptno
HAVING AVG(sal)>2000


查看平均工资高于2000那些部门的最高工资与最低工资分别是多少?
SELECT MAX(sal),MIN(sal),deptno
FROM emp
GROUP BY deptno
HAVING AVG(sal)>2000
 
 
 
关联查询 
查询多张表,根据表与表间数据的对应关系进行关联查询,并生成一个结果集该结果集每条记录中的字段就来自这些表.
 
查看每个员工以及其所在部门的名称?
SELECT ename,dname 
FROM emp,dept
WHERE emp.deptno=dept.deptno
 
可以为表起别名,这样所有字段在标注的时候可以只用表别名来指定字段所属.
当遇到两张表中有同名字段时,该字段必须标注来自那张表.
SELECT e.ename,e.deptno,d.dname 
FROM emp e,dept d
WHERE e.deptno=d.deptno
 
关联查询中要指定连接条件,并且不满足连接条件的记录会被忽略.
N张表连接,至少要有N-1个连接条件.所有连接条件通常要同时成立.
如果有过滤条件时,过滤条件也要与连接条件同时满足.
当不指定连接条件或连接条件无效时,会出现笛卡尔积(这通常是一个无意义的结果集)
 
查看在NEW YORK工作的员工?
SELECT e.ename,e.job,
       d.dname,d.loc
FROM emp e,dept d
WHERE e.deptno=d.deptno
AND d.loc='NEW YORK'


SELECT e.ename,d.dname
FROM emp e,dept d


内连接
内连接的查询作用与普通的关联查询作用一致,写法不同.

查询员工名字以及其所在部门名称?
SELECT e.ename,d.dname
FROM emp e JOIN dept d
ON e.deptno=d.deptno

查看在NEW YORK工作的员工? 
SELECT e.ename,d.dname 
FROM emp e JOIN dept d
ON e.deptno=d.deptno
WHERE d.loc='NEW YORK'
 
 
外链接
外链接可以将不满足连接条件的记录也查询出来.外链接分为:
左外链接,右外链接,全外链接左外链接:以JOIN左侧表作为驱动表,
该表中所有记录都要查询出来,那么凡不满足连接条件的记录,那么来自右侧
表的字段的值全部为NULL.

查看每个员工以及其所在的部门名?
SELECT e.ename,e.sal,
       d.dname,d.loc
FROM emp e 
 LEFT|RIGHT|FULL OUTER JOIN 
     dept d
ON e.deptno=d.deptno
 
SELECT e.ename,e.sal,
       d.dname,d.loc
FROM emp e,dept d
WHERE e.deptno(+)=d.deptno
 
自连接
当前表的一条数据对应当前表自己的多条数据,这样的表设计就是自连接.自连接可以保存数据属性相同,但是又存在上下级关系的树状结构数据使用.

查看每个员工的名字以及其上司的名字?
例如:SMITH的上司是FORD
SELECT e.ename||'的上司是'
       ||NVL(m.ename,'没有') 
FROM emp e,emp m 
WHERE e.mgr=m.empno(+) 
 
 
 
 
 
 
 
 


 
子查询
子查询是一条查询语句,它是嵌套在其他SQL语句之中的,目的是为外层SQL提供数据.


查看比CLARK工资高的员工?
SELECT ename,sal,job
FROM emp
WHERE sal>(SELECT sal FROM emp
           WHERE ename='CLARK')

查看与CLARK同部门的员工?
SELECT ename,job,deptno
FROM emp
WHERE deptno=(SELECT deptno
              FROM emp
              WHERE ename='CLARK')

查看工资高于公司平均工资的员工?
SELECT ename,sal,deptno
FROM emp
WHERE sal>(SELECT AVG(sal) FROM emp)


在DDL中使用子查询
可以根据子查询的结果当作一张表快速创建出来

创建一张表,该表包含字段:
empno,ename,sal,job,deptno,dname,loc
数据来自emp,dept表
CREATE TABLE employee
AS
SELECT 
 e.empno,e.ename,e.sal,e.job,
 d.deptno,d.dname,d.loc
FROM
 emp e FULL OUTER JOIN dept d
ON
 e.deptno = d.deptno

DML中使用子查询

将CLARK所在部门员工工资上浮10%
UPDATE emp
SET sal=sal*1.1
WHERE deptno=(SELECT deptno FROM emp
WHERE ename='CLARK')

SELECT ename,sal
FROM emp
WHERE deptno=10


子查询根据查询结果不同做了分类:
单行单列子查询,多行单列子查询,和多行多列子查询
其中单列子查询通常作为过滤条件使用而多列子查询通常当作表使用

当使用多行单列子查询作为过滤条件时要配合IN,ANY,ALL使用:

查看和职位是MANAGER同部门的员工?
SELECT ename,job,deptno
FROM emp
WHERE deptno IN(SELECT deptno 
                FROM emp
                WHERE job='MANAGER')

查看工资高于职位是SALESMAN和CLERK的员工?
SELECT ename,sal,job
FROM emp
WHERE sal >ALL(SELECT sal FROM emp
               WHERE job IN ('SALESMAN','CLERK'))


EXISTS关键字
EXISTS在过滤条件中使用,其后要跟一个子查询,它不关注子查询具体查出数据的值
是多少,只关注是否能查到数据,只要能查到数据EXISTS则认为满足条件.

查看有员工的部门是哪些?
SELECT deptno,dname,loc
FROM dept d
WHERE 
 NOT EXISTS(SELECT * FROM emp e
            WHERE e.deptno=d.deptno)

查看谁是别人的领导(有下属的员工)?
SELECT m.empno,m.ename,m.job
FROM emp m
WHERE EXISTS(
  SELECT * FROM emp e
  WHERE e.mgr = m.empno
)



查看最低薪水高于30号部门最低薪水的部门最低薪水各是多少?
SELECT MIN(sal),deptno
FROM emp
GROUP BY deptno
HAVING MIN(sal)>(SELECT MIN(sal) 
                 FROM emp
                 WHERE deptno=30)


查看谁比自己所在部门平均工资高?
SELECT e.ename,e.sal,e.deptno
FROM emp e,(SELECT AVG(sal) avg_sal,
                   deptno
            FROM emp
            GROUP BY deptno) t
WHERE e.deptno=t.deptno
AND e.sal>t.avg_sal


子查询也可以在SELECT子句中使用,可以将该查询的结果当作一个字段的值列出来.
SELECT 
  e.ename,e.sal,e.job,
  (SELECT d.dname FROM dept d
   WHERE e.deptno = d.deptno) dname
FROM
  emp e


分页查询
当一次查询的结果集中数据量非常大的时候会导致:网络传输速度慢,系统资源占用大等
问题导致的拖慢情况.并且实际用户也很少能一次性需要这么大批量的数据.对此,我们
常用的办法就是"分批分段"将数据查询出来,这就是分页查询.但是由于分页查询并没有
将语法定义在SQL标准中,所以不同数据库的分页语句不同.


ORACLE中提供了一个伪列:ROWNUM
该字段是为结果集中每条记录编一个行号的.该字段并不存在与任何表中
但是所有表都可以查询该字段.编号的过程是伴随查询的过程进行的,
只要可以从表中查询出一条记录,ROWNUM字段的值就是该记录在结果集中的行号,从1开始递增.
SELECT ROWNUM,ename,sal,job
FROM emp


在使用ROWNUM对结果集编行号的过程中不要使用ROWNUM做>1以上数字的过滤
判断,否则得不到任何结果.因为ROWNUM是在查询过程中生成的,只有查询到数据
才会编号,才会自增.下面SQL查询不到任何结果.
SELECT ROWNUM,ename,sal,job
FROM emp
WHERE ROWNUM >1 

SELECT *
FROM(SELECT ROWNUM rn,t.*
     FROM(SELECT ename,sal,job
          FROM emp
          ORDER BY sal DESC) t)
WHERE rn BETWEEN 6 AND 10



SELECT *
FROM(SELECT ROWNUM rn,t.*
     FROM(SELECT ename,sal,job
          FROM emp
          ORDER BY sal DESC) t
     WHERE ROWNUM <=10)
WHERE rn >=6

page:第几页
pageSize:每页显示的条目数

start:(page-1)*pageSize+1
end:pageSize*page


DECODE函数,可以实现简单的分支效果
SELECT ename, job, sal,
     DECODE(job,  
            'MANAGER', sal * 1.2,
            'ANALYST', sal * 1.1,
            'SALESMAN', sal * 1.05,
            sal
     ) bonus
FROM emp


DECODE在GROUP BY子句中
统计人数,要求将职位是MANAGER,ANALYST看做一组,其他职位员工看做另一组,分别统计
人数?

SELECT 
  COUNT(*),
  DECODE(job,'MANAGER','VIP',
             'ANALYST','VIP',
             'OTHER')
FROM emp
GROUP BY
  DECODE(job,
         'MANAGER','VIP',
         'ANALYST','VIP',
         'OTHER')

排序函数
排序函数与ROWNUM相似,用来对结果集生成行号.但是排序函数是将结果集按照
指定的字段分组,然后组内按照指定字段排序,之后为每组的记录生成行号.

ROW_NUMBER:生成组内连续且唯一的数字查看每个部门的工资排名
SELECT 
 ename,sal,deptno,
 ROW_NUMBER() OVER(
    PARTITION BY deptno
    ORDER BY sal DESC
 ) rank
FROM emp


RANK函数:生成组内不连续也不唯一的数字
SELECT 
 ename,sal,deptno,
 RANK() OVER(
    PARTITION BY deptno
    ORDER BY sal DESC
 ) rank
FROM emp


DENSE_RANK:生成组内连续但不唯一的数字
SELECT 
 ename,sal,deptno,
 DENSE_RANK() OVER(
    PARTITION BY deptno
    ORDER BY sal DESC
 ) rank
FROM emp


高级分组函数:

查看每天营业额?
SELECT year_id,month_id,day_id,SUM(sales_value)
FROM sales_tab
GROUP BY year_id,month_id,day_id
ORDER BY year_id,month_id,day_id

查看每月营业额?
SELECT year_id,month_id,SUM(sales_value)
FROM sales_tab
GROUP BY year_id,month_id
ORDER BY year_id,month_id

查看每年营业额?
SELECT year_id,SUM(sales_value)
FROM sales_tab
GROUP BY year_id
ORDER BY year_id

总共营业额?
SELECT SUM(sales_value)
FROM sales_tab


查看每天,每月,每年,以及总共营业额?
SELECT year_id,month_id,day_id,SUM(sales_value)
FROM sales_tab
GROUP BY year_id,month_id,day_id
UNION ALL
SELECT year_id,month_id,NULL,SUM(sales_value)
FROM sales_tab
GROUP BY year_id,month_id
UNION ALL
SELECT year_id,NULL,NULL,SUM(sales_value)
FROM sales_tab
GROUP BY year_id
UNION ALL
SELECT NULL,NULL,NULL,SUM(sales_value)
FROM sales_tab


ROLLUP函数
GROUP BY ROLLUP(a,b,c)
等效于:
GROUP BY a,b,c
UNION ALL 
GROUP BY a,b
UNION ALL 
GROUP BY a
UNION ALL 
全表

SELECT year_id,month_id,day_id,SUM(sales_value)
FROM sales_tab
GROUP BY ROLLUP(year_id,month_id,day_id)
ORDER BY year_id,month_id,day_id


CUBE()
CUBE函数的分组方式是参数的每种组合都作为一次分组方式,所以分组次数为2的参数个数次方

GROUP BY CUBE(a,b,c)
ab
ac
bc
a
b
c
abc
全表

SELECT year_id,month_id,day_id,SUM(sales_value)
FROM sales_tab
GROUP BY CUBE(year_id,month_id,day_id)
ORDER BY year_id,month_id,day_id


GROUPING SET函数
GROUPING SET的每一个参数为一种分组方式,然后将这些分组统计的结果并在一个结果集中显示

查看每天与每月的营业额?
SELECT year_id,month_id,day_id,SUM(sales_value)
FROM sales_tab
GROUP BY 
 GROUPING SETS(
  (year_id,month_id,day_id),
  (year_id,month_id)
 )
ORDER BY year_id,month_id,day_id