数据库基础及举例
为职员表 emp插入示例数据，示例数据如图－1所示：
图－1
为部门表 dept插入示例数据，示例数据如图－2所示：
图－2
SELECT SYSDATE FROM dual
SQL语句本身不区分大小写,但是出于
可读性的目的,我们通常会将SQL中的
关键字全部大写,非关键字全部小写.
DDL 数据定义语言
DDL是对数据库对象进行操作的语言.
数据库对象包括:表,视图,索引,序列
创建表:
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
数据库中所有数据类型的默认值都是NULL
在创建表的时候可以使用DEFAULT为某个
字段单独指定一个默认值.
数据库中的字符串字面量是使用单引号的
虽然SQL语句本身不区分大小写,但是字符串
的值是区分大小写的!
CREATE TABLE employee(
id NUMBER(4),
name VARCHAR2(20) NOT NULL,
gender CHAR(1) DEFAULT 'M',
birth DATE,
salary NUMBER(6,2),
job VARCHAR2(30),
deptno NUMBER(2)
)
DESC employee
修改表
1:修改表名
2:修改表结构
修改表名:
RENAME employee TO myemp
DESC myemp
修改表结构
1:添加新的字段
2:修改现有字段
3:删除现有字段
添加新字段
ALTER TABLE myemp
ADD(
hiredate DATE DEFAULT SYSDATE
)
DESC myemp
删除字段
ALTER TABLE myemp
DROP(hiredate)
修改字段
可以修改字段的类型,长度,默认值,是否非空.
修改表结构都应当避免在表中有数据以后进行.
若表中有数据,修改表中字段时尽量不要修改类
型,若修改长度尽量增大避免缩小,否则可能导致
失败.
ALTER TABLE myemp
MODIFY(
job VARCHAR2(40) DEFAULT 'CLERK'
)
DESC myemp
DML语句
DML是对表中的数据进行的操作
DML伴随事物控制(TCL)
DML包含操作:
增,删,改.
INSERT语句
向表中插入数据
INSERT INTO myemp
(id,name,salary,deptno)
VALUES
(1,'jack',5000,10)
SELECT * FROM myemp
COMMIT
--使用自定义日期格式插入记录
INSERT INTO myemp
(id, name, job,birth)
VALUES
(1003, 'donna', 'MANAGER',
TO_DATE('2009-09-01','YYYY-MM-DD') MYSQL 中的直接写('2009-09-01','YYYY-MM-DD') 不用 TO_DATE
)
附加：
- INSERT INTO FLOOR VALUES ( to_date ( '2007-12-14 14:10' , 'YYYY-MM-DD HH24:MI' ) ); ｏｒａｃｌｅ
UPDATE语句
修改表中数据
修改表中数据要使用WHERE添加过滤
条件,这样才会只将满足条件的记录
进行修改,否则是全表所有数据都修改
UPDATE myemp
SET salary=7000,gender='F',
name='rose'
WHERE id=1
DELETE语句
删除表中数据,删除数据通常也要添加
WHERE语句来限定要删除数据的条件
否则就是清空表操作!
DELETE FROM myemp
WHERE name='rose'
SELECT语句
用于查询表中数据
SELECT子句后面跟的是要查询的
字段,可以包括表中的具体字段,函数
或者表达式.
FROM子句用来指定数据来源的表
WHERE子句用来添加过滤条件,这样
做的结果是只将满足条件的记录查询出来
查看emp表中的数据
SELECT empno,ename,job,sal
FROM emp
SELECT子句中使用表达式
查看每个员工的年薪?
SELECT ename,sal*12
FROM emp
字符串函数
CONCAT()函数,用来连接字符串
SELECT CONCAT(ename,sal)
FROM emp
SELECT CONCAT(CONCAT(ename,','),sal)
FROM emp
SELECT ename||','||sal
FROM emp
LENGTH函数,查看字符串长度
SELECT ename,LENGTH(ename)
FROM emp
UPPER,LOWER,INITCAP
将字符串转换为全大写,全小写
以及首字母大写
对于INITCAP而言,可以使用空格
隔开多个单词,那么每个单词首字母
都会大写.
伪表:dual
当查询的内容不和任何表中数据有关系
时,可以使用伪表,伪表只会查询出一条
记录.
SELECT UPPER('helloworld'),
LOWER('HELLOWORLD'),
INITCAP('HELLO WORLD')
FROM dual
TRIM,LTRIM,RTRIM
去除当前字符串中两边的指定重复
字符,LTRIM仅去除左侧的,RTRIM
则仅去除右侧的.
SELECT TRIM('e' FROM 'eeeliteeee')
FROM dual
SELECT LTRIM('esrrersrresresliteeee','res')
FROM dual
LPAD,RPAD补位函数
SELECT LPAD(sal,5,'$')
FROM emp
SUBSTR截取字符串
数据库中的下标都是从1开始的
SELECT
SUBSTR('thinking in java',13,4)
FROM dual
第三个参数不指定则是截取到末尾,指定的长度
若超过实际可以截取的内容也是截取到末尾
SELECT
SUBSTR('thinking in java',10)
FROM dual
截取的位置可以是负数,若是则表示从倒数
第几个字符开始截取
SELECT
SUBSTR('thinking in java',-4,4)
FROM dual
INSTR(char1,char2[,n,m])函数
查找char2在char1中的位置
n为从第几个字符开始检索
m为第几次出现
n,m不写则默认都是1
SELECT
INSTR('thinking in java','in',4,1)
FROM
dual
数字函数
ROUND(n,m)四舍五入
SELECT ROUND(45.678, 2) FROM DUAL
SELECT ROUND(45.678, 0) FROM DUAL
SELECT ROUND(45.678, -1) FROM DUAL
TRUNC(n,m)截取数字
SELECT TRUNC(45.678, 2) FROM DUAL
SELECT TRUNC(45.678, 0) FROM DUAL
SELECT TRUNC(45.678, -1) FROM DUAL
MOD(m,n)求余数
SELECT ename, sal, MOD(sal, 1000)
FROM emp
CEIL,FLOOR
向上取整和向下取整
SELECT CEIL(45.678) FROM DUAL
SELECT FLOOR(45.678) FROM DUAL
查看scott员工的信息?
SELECT ename,sal,deptno
FROM emp
WHERE ename=UPPER('scott')
查看名字只有5个字母的员工的名字,工资?
部门号
SELECT ename,sal,deptno
FROM emp
WHERE LENGTH(ename)=5
查看第三个字母是A的员工信息?
SELECT ename,sal,deptno
FROM emp
WHERE INSTR(ename,'A')=3
SELECT ename,sal,deptno
FROM emp
WHERE SUBSTR(ename,3,1)='A'
日期类型
SYSDATE,SYSTIMESTAMP
SYSDATE对应数据库一个内部函数,
该函数返回一个表示当前系统时间
的DATE类型值.
SYSTIMESTAMP返回的是一个表示
当前系统时间的时间戳类型的值
SELECT SYSDATE FROM dual
SELECT SYSTIMESTAMP FROM dual
TO_DATE函数
可以将字符串按照给定的日期格式解析
为一个DATE类型的值
在日期格式字符串中凡不是英文,符号,数字的
其他字符,都需要使用双引号括起来.
SELECT
TO_DATE('2008年08月08日 20:08:08',
'YYYY"年"MM"月"DD"日" HH24:MI:SS')
FROM
dual
日期的计算
日期可以与一个数字进行加减法,这相当
与加减指定的天
两个日期可以进行减法,差为相差的天.
查看每个员工至今入职多少天了?
SELECT ename,SYSDATE-hiredate
FROM emp
输入自己生日:1992-08-02
查看到今天为止活了多少天?
SELECT
SYSDATE-TO_DATE('1992-08-02',
'YYYY-MM-DD')
FROM dual
TO_CHAR():可以将DATE按照给定的格式
转换为字符串.
SELECT
TO_CHAR(SYSDATE,
'YYYY-MM-DD HH24:MI:SS')
FROM
dual
SELECT
TO_CHAR(
TO_DATE('49-07-12','RR-MM-DD'),
'YYYY-MM-DD'
)
FROM
dual
LAST_DAY(date)
返回给定日期所在月的月底日期
查看当月底?
SELECT LAST_DAY(SYSDATE)
FROM dual
ADD_MONTHS(date,i)
对给定日期加上指定的月,若
i为负数则是减去.
查看每个员工入职20周年纪念日
SELECT
ename,ADD_MONTHS(hiredate,12*20)
FROM
emp
MONTHS_BETWEEN(date1,date2)
计算两个日期之间相差的月,计算是
根据date1-date2得到的
查看每个员工至今入职多少个月了?
SELECT
ename,MONTHS_BETWEEN(SYSDATE,hiredate)
FROM
emp
NEXT_DAY(date,i)
返回给定日期的第二天开始一周
之内的指定周几的日期
i:1表示周日,2表示周一,以此类推
SELECT NEXT_DAY(SYSDATE,7)
FROM dual
LEAST,GREATEST
求最小值与最大值,除了
日期外,常用的数字也可以比较大小
SELECT
LEAST(SYSDATE,
TO_DATE('2008-08-05',
'YYYY-MM-DD'))
FROM DUAL
EXTRACT()提取给定日期中指定
时间分量的值
SELECT EXTRACT(YEAR FROM SYSDATE)
FROM dual
查看1980年入职的员工
SELECT ename,hiredate
FROM emp
WHERE EXTRACT(YEAR FROM hiredate)=1980
空值操作
1:插入NULL值
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, '张无忌');
SELECT * FROM student
2:更新为NULL
UPDATE student
SET gender=NULL
WHERE id=1000
判断字段的值是否为NULL
判断要使用IS NULL或IS NOT NULL
DELETE FROM student
WHERE gender IS NULL
NULL值的运算操作
NULL与任何数字运算结果还为NULL
NULL与字符串拼接等于什么都没干
查看每个员工的收入:
SELECT ename,sal,comm,sal+comm
FROM emp
空值函数
NVL(arg1,arg2)
当arg1为NULL,函数返回arg2的值
若不为NULL,则返回arg1本身.
所以该函数的作用是将NULL值替换为
一个非NULL值.
查看每个员工的收入:
SELECT
ename,sal,comm,
sal+NVL(comm,0)
FROM emp
查看每个人的绩效情况,即:
有绩效的,显示为"有绩效"
绩效为NULL的,则显示为"没有绩效"
NVL2(arg1,arg2,arg3)
当arg1不为NULL,则函数返回arg2
当arg1为NULL,则函数返回arg3
该函数是根据一个值是否为NULL来
返回两个不同结果.
SELECT
ename,comm,
NVL2(comm,'有奖金','没有奖金')
FROM
emp
SELECT
ename,sal,comm,
NVL2(comm,sal+comm,sal)
FROM
emp
DQL查询语句
SELECT子句中可以使用函数或表达式
,那么结果集中对应的该字段名就是这个
函数或表达式,可读性差,为此可以为这样
的字段添加别名,那么结果集会以这个别名
作为该字段的名字
别名本身不区分大小写,而且不能含有空格.
若希望别名区分大小写或含有空格,那么可以
在别名上使用双引号括起来.
SELECT ename,sal*12 "sal"
FROM emp
查看工资高于1000的职位是
CLERK和SALESMAN
SELECT ename,sal,job
FROM emp
WHERE sal>1000
AND (job='SALESMAN'
OR job='CLERK')
AND的优先级高于OR,可以通过括号
来提高OR的优先级
LIKE用于模糊匹配字符串,支持两个
通配符:
_:单一的一个字符
%:任意个字符
查看名字第二个字母是A最后一个字母是N的?
SELECT ename
FROM emp
WHERE ename LIKE '_A%N'
IN和NOT IN
判断是否在列表中或不在列表中
SELECT ename, job FROM emp
WHERE job IN ('MANAGER', 'CLERK');
SELECT ename, job FROM emp
WHERE deptno NOT IN (10, 20);
IN和NOT IN常用来判断子查询的结果
BETWEEN...AND...
判断在一个区间范围内
工资在1500到3000之间的员工
SELECT ename,sal
FROM emp
WHERE sal BETWEEN 1500 AND 3000
ANY,ALL
ANY和ALL是配合>,>=,<,<=一个
列表使用的.
>ANY(list):大于列表中最小的
>ALL(list):大于列表中最大的
<ANY(list):小于列表中最大的
<ALL(list):小于列表中最小的
ANY和ALL常用于子查询.
SELECT empno, ename, job,
sal, deptno
FROM emp
WHERE sal > ANY (3500,4000,4500);
使用函数或者表达式最为过滤条件
SELECT ename, sal, job
FROM emp
WHERE ename = UPPER('scott');
SELECT ename, sal, job
FROM emp
WHERE sal * 12 >50000;
DISTINCT关键字
对结果集中指定字段值重复的记录进行
去重
查看公司有哪些职位?
SELECT DISTINCT job
FROM emp
多字段去重,是对这些字段值的组合
进行去重
SELECT DISTINCT job,deptno
FROM emp
排序
ORDER BY子句
ORDER BY可以根据其后指定的
字段对结果集按照该字段的值进行
升序或者降序排列.
ASC:升序,不写默认就是升序
DESC:降序.
查看公司的工资排名:
SELECT ename,sal
FROM emp
ORDER BY sal DESC
ORDER BY按照多个字段排序
ORDER BY首先按照第一个字段的排序
方式对结果集进行排序,当第一个字段
有重复值时才会按照第二个字段排序
方式进行排序,以此类推.每个字段都可以
单独指定排序方式.
SELECT ename,deptno,sal
FROM emp
ORDER BY deptno DESC,sal DESC
排序的字段中含有NULL值,NULL被认作
最大值.
SELECT ename,comm
FROM emp
ORDER BY comm DESC
聚合函数
聚合函数有叫多行函数,分组函数
聚合函数是对结果集某些字段的值进行统计的.
MAX,MIN
求给定字段的最大值与最小值
查看公司的最高工资与最低工资是多少?
SELECT MAX(sal),MIN(sal)
FROM emp
AVG,SUM
求平均值和总和
SELECT AVG(sal),SUM(sal)
FROM emp
COUNT函数
COUNT函数不是对给定的字段的值进行
统计的,而是对给定字段不为NULL的记录
数统计的.
实际上所有聚合函数都忽略NULL值统计.
SELECT COUNT(ename)
FROM emp
通常查看表的记录数可以使用COUNT(*)
SELECT COUNT(*) FROM emp
查看平均绩效
SELECT AVG(NVL(comm,0)),SUM(comm)
FROM emp
分组
GROUP BY 子句
GROUP BY可以将结果集按照其后指定
的字段值相同的记录看做一组,然后配合
聚合函数进行更细分的统计工作.
查看每个部门的平均工资?
SELECT AVG(sal),deptno
FROM emp
GROUP BY deptno
查看每个职位的最高工资?
SELECT MAX(sal),job
FROM emp
GROUP BY job
GROUP BY也可以根据多个字段分组
分组原则为这几个字段值都相同的记录看做一组
查看同部门同职位的平均工资?
SELECT AVG(sal),job,deptno
FROM emp
GROUP BY job,deptno
当SELECT子句中含有聚合函数时,那么凡
不在聚合函数中的其他单独字段都必须出现
在GROUP BY子句中,反过来则不是必须的.
查看部门的平均工资,前提是该部门的平均
工资高于2000
SELECT AVG(sal),deptno
FROM emp
WHERE AVG(sal)>2000
GROUP BY deptno
WHERE中不能使用聚合函数作为过滤
条件,原因是过滤时机不对.
WHERE是在数据库检索表中数据时,对
数据逐条过滤以决定是否查询出该数据
时使用的,所以WHERE用来确定结果集
的数据.
使用聚合函数的结果作为过滤条件,那么
一定是数据从表中查询完毕(WHERE在查询
过程中发挥作用)得到结果集,并且分组完毕
才进行聚合函数统计结果,得到后才可以对
分组进行过滤,由此可见,这个过滤时机是在
WHERE之后进行的.
聚合函数的过滤条件要在HAVING子句中使用
HAVING必须跟在GROUP 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 e.ename,d.dname,e.deptno
FROM emp e,dept d
WHERE e.deptno=d.deptno
当两张表有同名字段时,SELECT子句中
必须明确指定该字段来自哪张表.在关联
查询中,表名也可以添加别名,这样可以简化
SELECT语句的复杂度
关联查询要添加连接条件,否则会产生笛卡尔积
笛卡尔积通常是一个无意义的结果集,它的记录
数是所有参与查询的表的记录数乘积的结果.
要避免出现,数据量大时极易出现内存溢出等现象.
N张表关联查询要有至少N-1个连接条件.
SELECT e.ename,d.dname,e.deptno
FROM emp e,dept d
查看在NEW YORK工作的员工?
SELECT e.ename,d.deptno
FROM emp e,dept d
WHERE e.deptno=d.deptno
AND d.loc='NEW YORK'
查看工资高于3000的员工的名字
工资,部门名以及所在地?
SELECT e.ename,e.sal,
d.dname,d.loc
FROM emp e,dept d
WHERE e.deptno=d.deptno
AND e.sal>3000
查看SALES部门的员工名字以及部门的名字
SELECT e.ename,d.dname
FROM emp e JOIN dept d
ON e.deptno=d.deptno
WHERE d.dname='SALES'
不满足连接条件的记录是不会在关联
查询中被查询出来的.
外链接
外链接除了会将满足链接条件的记录查询
出来之外,还会将不满足链接条件的记录也
查询出来.
外链接分为:
左外链接:以JOIN左侧表作为驱动表(所有数据都会
被查询出来),那么当该表中的某条记录不
满足链接条件时来自右侧表中的字段全部
填NULL.
右外链接:
全外链接:
SELECT e.ename,d.dname
FROM emp e
LEFT|RIGHT|FULL OUTER JOIN
dept d
ON e.deptno=d.deptno
SELECT e.ename,d.dname
FROM emp e JOIN dept d
ON e.deptno(+)=d.deptno
自连接
自连接即:当前表的一条记录可以对应
当前表自己的多条记录
自连接是为了解决同类型数据但是又存在
上下级关系的树状结构数据时使用.
查看每个员工以及其领导的名字?
SELECT e.ename,m.ename
FROM emp e,emp m
WHERE e.mgr=m.empno
查看SMITH的上司在哪个城市工作?
SELECT e.ename,m.ename,
m.deptno,d.loc
FROM emp e,emp m,dept d
WHERE e.mgr=m.empno
AND m.deptno=d.deptno
AND e.ename='SMITH'
SELECT e.ename,m.ename,
m.deptno,d.loc
FROM emp e JOIN emp m
ON e.mgr=m.empno
JOIN dept d
ON m.deptno=d.deptno
WHERE e.ename='SMITH'
子查询
子查询是一条SELECT语句,但它是嵌套在
其他SQL语句中的,为的是给该SQL提供数据
以支持其执行操作.
查看谁的工资高于CLARK?
SELECT ename,sal
FROM emp
WHERE sal>(SELECT sal FROM emp
WHERE ename='CLARK')
查看与CLARK同职位的员工?
SELECT ename,job FROM emp
WHERE job=(SELECT job FROM emp
WHERE ename='CLARK')
查看与CLARK同部门的员工?
SELECT ename,deptno FROM emp
WHERE deptno=(SELECT deptno FROM emp
WHERE ename='CLARK')
在DDL中使用子查询
可以根据子查询的结果集快速创建一张表
创建表employee,表中字段为:
empno,ename,job,sal,deptno,dname,loc
数据为现有表中emp与dept对应的数据
CREATE TABLE employee
AS
SELECT e.empno,e.ename,e.job,e.sal,
e.deptno,d.dname,d.loc
FROM emp e,dept d
WHERE e.deptno=d.deptno(+)
DESC employee
SELECT * FROM employee
DROP TABLE employee
创建表时若子查询中的字段有别名则该表
对应的字段就使用该别名作为其字段名,
当子查询中一个字段含有函数或表达式,那么
该字段必须给别名.
CREATE TABLE employee
AS
SELECT e.empno id,e.ename name,
e.job,e.sal*12 salary,
e.deptno,d.dname,d.loc
FROM emp e,dept d
WHERE e.deptno=d.deptno(+)
SELECT name,salary
FROM employee
DML中使用子查询
将CLARK所在部门的所有员工删除
DELETE FROM employee
WHERE deptno=(SELECT deptno
FROM employee
WHERE name='CLARK')
SELECT * FROM employee
子查询常用于SELECT语句中.
子查询根据查询结果集的不同分为:
单行单列子查询:常用于过滤条件,可以配合
=,>,>=,<,<=使用
多行单列子查询:常用于过滤条件,由于查询出
多个值,在判断=时要用IN,
判断>,>=等操作要配合ANY,ALL
多行多列子查询:常当做一张表看待.
查询与SALESMAN同部门的其他职位员工：
SELECT ename,job,deptno
FROM emp
WHERE deptno IN (SELECT deptno
FROM emp
WHERE job='SALESMAN')
AND job<>'SALESMAN'
查看比职位是CLERK和SALESMAN工资都高的员工?
SELECT ename,sal
FROM emp
WHERE sal> ALL(SELECT sal FROM emp
WHERE job IN('CLERK','SALESMAN')
)
EXISTS关键字
EXISTS后面跟一个子查询,当该子查询可以查询
出至少一条记录时,则EXISTS表达式成立并返回
true
SELECT deptno, dname FROM dept d
WHERE
NOT EXISTS(SELECT * FROM emp e
WHERE d.deptno = e.deptno)
查看每个部门的最低薪水是多少?前提是该部门
的最低薪水要高于30号部门的最低薪水
SELECT MIN(sal),deptno
FROM emp
GROUP BY deptno
HAVING MIN(sal)>(SELECT MIN(sal)
FROM emp
WHERE deptno=30)
子查询在FROM子句中的使用
当一个子查询是多列子查询,通常将该子查询
的结果集当做一张表看待并基于它进行二次
查询.
查看比自己所在部门平均工资高的员工?
1:查看每个部门平均工资
SELECT AVG(sal),deptno
FROM emp
GROUP BY deptno
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,
(SELECT d.dname FROM dept d
WHERE d.deptno = e.deptno) deptno
FROM emp e
分页查询
分页查询是将查询表中数据时分段查询,而不
是一次性将所有数据查询出来.
有时查询的数据量非常庞大,这回导致系统资源
消耗大,响应速度长,数据冗余严重.
为此当遇到这种情况时一般使用分页查询解决.
数据库基本都支持分页,但是不同数据库语法不
同(方言).
ORACLE中的分页是基于伪列ROWNUM实现的.
ROWNUM不存在与任何一张表中,但是所有的表
都可以查询该字段.该字段的值是随着查询自动
生成的,方式是:每当可以从表中查询出一条记
录时,该字段得值即为该条记录的行号,从1开始,
逐次递增.
SELECT ROWNUM,empno,ename,sal,job
FROM emp
WHERE ROWNUM > 1
在使用ROWNUM对结果集进行编号的查询过程
中不能使用ROWNUM做>1以上的数字判断,否则
将查询不出任何数据.
SELECT *
FROM(SELECT ROWNUM rn,empno,
ename,sal,job
FROM emp)
WHERE rn BETWEEN 6 AND 10
查看公司工资排名的6-10
SELECT *
FROM(SELECT ROWNUM rn,t.*
FROM(SELECT empno,ename,sal
FROM emp
ORDER BY sal DESC) t)
WHERE rn BETWEEN 6 AND 10
SELECT *
FROM(SELECT ROWNUM rn,t.*
FROM(SELECT empno,ename,sal
FROM emp
ORDER BY sal DESC) t
WHERE ROWNUM <=10)
WHERE rn>=6
计算区间公式
pageSize:每页显示的条目数
page:页数
star:(page-1)*pageSize+1
end:pageSize*page
int start = (page-1)*pageSize+1;
int end = pageSize*page;
String sql = "SELECT * " +
"FROM(SELECT ROWNUM rn,t.* " +
" FROM(SELECT empno,ename,sal" +
" FROM emp " +
" ORDER BY sal DESC) t " +
" WHERE ROWNUM <="+end+") " +
"WHERE rn>="+start;
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')
SELECT deptno, dname, loc
FROM dept
ORDER BY
DECODE(dname,
'OPERATIONS',1,
'ACCOUNTING',2,
'SALES',3)
排序函数
排序函数允许对结果集按照指定的字段分组
在组内再按照指定的字段排序,最终生成组内
编号.
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,sales_value
FROM sales_tab
ORDER BY year_id,month_id,day_id
查看每天营业额?
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
高级分组函数
高级分组函数用在GROUP BY子句中,每个高级
分组函数都有一套分组策略.
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)
CUBE():每种组合分一次组
分组次数:2的参数个数次方
GROUP BY CUBE(a,b,c)
abc
ab
bc
ac
a
b
c
全表
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 SETS:每个参数是一种分组方式,
然后将这些分组统计后并在一个结果集显示.
仅查看每天与每月营业额?
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)
)
视图
视图是数据库对象之一
所有数据库对象名字不能重复,所以
视图名字一般是以"v_"开头
视图在SQL语句中体现的角色与表相同
但是视图并不是一张真实存在的表,而
只是对应一个SELECT语句的查询结果集,
并将其当做表看待而已.
使用视图的目的是简化SQL语句的复杂度,
重用子查询,限制数据访问.
创建视图
该视图包含的数据为10号部门的员工信息
CREATE VIEW v_emp_10
AS
SELECT empno,ename,sal,deptno
FROM emp
WHERE deptno=10
查看视图数据:
SELECT * FROM v_emp_10
视图对应的子查询中的字段若含有函数
或者表达式,那么该字段必须指定别名.
当视图对应的子查询中的字段使用了别名,
那么视图中该字段就用别名来命名.
修改视图
由于视图仅对应一个SELECT语句,所以修改
视图就是替换该SELECT语句而已.
CREATE OR REPLACE VIEW v_emp_10
AS
SELECT empno id,ename name,
sal salary,deptno
FROM emp
WHERE deptno=10
SELECT * FROM v_emp_10
DESC v_emp_10
视图分为简单视图与复杂视图
简单视图:对应的子查询中不含有
关联查询,查询的字段不包含函数,
表达式等,没有分组,没有去重.
反之则是复杂视图.
对视图进行DML操作
仅能对简单视图进行DML操作.
对视图进行DML操作就是对视图数据来源
的基础表进行的操作.
INSERT INTO v_emp_10
(id,name,salary,deptno)
VALUES
(1001,'JACK',2000,10)
SELECT * FROM v_emp_10
SELECT * FROM emp
对视图的DML操作就是对基表操作,那么
操作不当可能对基表进行数据污染
INSERT INTO v_emp_10
(id,name,salary,deptno)
VALUES
(1002,'ROSE',3000,20)
视图对ROSE不可见
SELECT * FROM v_emp_10
SELECT * FROM emp
更新同样存在更新后对数据不可控
的情况
UPDATE v_emp_10
SET deptno=20
删除不会对基表产生数据污染
DELETE FROM v_emp_10
WHERE deptno=20
为视图添加检查选项,可以保证对视图
的DML操作后视图对其可见,否则不允许
进行该DML操作,这样就避免了对基表
进行数据污染.
CREATE OR REPLACE VIEW v_emp_10
AS
SELECT empno id,ename name,
sal salary,deptno
FROM emp
WHERE deptno=10
WITH CHECK OPTION
为视图添加只读选项,那么该视图
不允许进行DML操作.
CREATE OR REPLACE VIEW v_emp_10
AS
SELECT empno id,ename name,
sal salary,deptno
FROM emp
WHERE deptno=10
WITH READ ONLY
SELECT object_name
FROM user_objects
WHERE object_type = 'VIEW'
AND object_name LIKE '%_fanchuanqi'
SELECT TEXT,view_name
FROM user_views
SELECT table_name
FROM user_tables
复杂视图
创建一个含有公司部门工资情况的视图,
内容为:部门编号,部门名称,部门的最高,
最低,平均,以及工资总和信息.
CREATE VIEW v_dept_sal
AS
SELECT d.deptno,d.dname,
MIN(e.sal) min_sal,
MAX(e.sal) max_sal,
AVG(e.sal) avg_sal,
SUM(e.sal) sum_sal
FROM emp e,dept d
WHERE e.deptno=d.deptno
GROUP BY d.deptno,d.dname
SELECT * FROM v_dept_sal
查看谁比自己所在部门平均工资高?
SELECT e.ename,e.sal,e.deptno
FROM emp e,v_dept_sal v
WHERE e.deptno=v.deptno
AND e.sal>v.avg_sal
删除视图
DROP VIEW v_emp_10
删除视图本身并不会影响基表数据.
但是删除视图数据会对应将基表数据删除.
序列
序列也是数据库对象之一.
作用是生成一系列数字.
序列常用与为某张表的主键字段提供值使用.
CREATE SEQUENCE seq_emp_id
START WITH 1
INCREMENT BY 1
序列支持两个伪列:
NEXTVAL:获取序列下一个值
若是新创建的序列,那么第一次调用返回的是
START WITH指定的值,以后每次调用都会得到
当前序列值加上步长后的数字.
NEXTVAL会导致序列发生步进,且序列不能回退.
CURRVAL:获取序列当前值,即:最后一次调用
NEXTVAL后得到的值,CURRVAL不会导致步进.
但是新创建的序列至少调用一次NEXTVAL后才可
以使用CURRVAL.
SELECT seq_emp_id.CURRVAL
FROM dual
使用序列为EMP表中信插入的数据提供
主键字段的值
INSERT INTO emp
(empno,ename,sal,job,deptno)
VALUES
(seq_emp_id.NEXTVAL,'JACK',
3000,'CLERK',10)
SELECT * FROM emp
删除序列
DROP SEQUENCE seq_emp_id
索引
索引是数据库对象之一
索引是为了提高查询效率
索引的统计与应用是数据库自动完成的
只要数据库认为可以使用某个已创建的
索引时就会自动应用.
约束
唯一性约束
唯一性约束可以保证表中该字段的值
任何一条记录都不可以重复,NULL除外.
CREATE TABLE employees1 (
eid NUMBER(6) UNIQUE,
name VARCHAR2(30),
email VARCHAR2(50),
salary NUMBER(7, 2),
hiredate DATE,
CONSTRAINT employees_email_uk UNIQUE(email)
)
INSERT INTO employees1
(eid,name,email)
VALUES
(NULL,'JACK',NULL)
SELECT * FROM employees1
DELETE FROM employees1
WHERE name='JACK'
CREATE TABLE employees2 (
eid NUMBER(6) PRIMARY KEY,
name VARCHAR2(30),
email VARCHAR2(50),
salary NUMBER(7, 2),
hiredate DATE
);
INSERT INTO employees2
(eid,name)
VALUES
(2,'JACK')
