DDL : 数据库模式定义语言，关键字：create
DML : 数据操纵语言，关键字：Insert、delete、update
DCL : 数据库控制语言 ，关键字：grant、remove
DQL : 数据库查询语言，关键字：select
结构化查询语言 (Structured Query Language, SQL)，Oracle 中的 SQL 不区分大小写只有在文本数字值得时候才会检查
注 : 当创建表或列名的时候使用双引号括起来就可以创建大小写敏感的列名或表名，但是不推荐这样做
SQL*Plus 是 Oracle 的一种操作数据库数据和对数据库执行即时查询的工具，甚至还可以作为代码生成器来使用
逻辑判断 : = > >= < <= != ^= <>
!= ^= <> 这3个都表示不等于
注 : 在进行数据类型比较时，一定要考虑编码情况，VARCHAR2 和 CHAR 是字符比较，如果列数据类型为 NUMBER 则 12 > 9，如果是字符比较则 9 > 12 因为字符 9 比字符 1要大
DESCRIBE : 查询表结构
SQL> describe newspaper;
LIKE 执行模式匹配 : _表示一个字符 %表示任意多个字符
NULL 表示该列是否存在数据，如果没有数据，就说明该列是 null
IS NULL，IS NOT NULL
IN : 在某几个指定项中
NOT IN : 不在几个指定项中
BETWEEN AND : 在指定范围之间
NOT BETWEEN AND : 不在指定范围之间
AND : 同事满足两边的条件
OR : 只满足其中一边的条件
优先级 : () 优先级最高，乘除次之，加减法最后，AND 的优先级高于 OR
表
使用 CREATE TABLE 关键字创建表
SQL> CREATE TABLE trouble(city VARCHAR2(13) NOT NULL,sample_date DATE NOT NULL,noon NUMBER(3,1),midnight NUMBER(3,1),precipitation NUMBER);
表明和列名必须以由字母、数字和下划线，长度为1~30个，并且名称必须唯一，不使用双引号括起来时不考虑字母大小写，使用双引号括起来时就要考虑大小写，建议不要括起来否则可能会给开发人员和用户造成麻烦
在设计表时需要考虑字符(CHAR 和 VARCHAR2)的最大宽度，并设置 NUMBER 精度
注 : 1> 使用宽的 VARCHAR2 存储数据不会浪费空间，但是会对默认的 SQL*Plus 列格式产生影响
2> NUMBER(3,1) 3表示存储数字位数，1表示小数点右边的位数 小数部分的长度比较大会进行四舍五入来达到指定精度
定义约束 : 表添加的约束越多那么维护数据的工作就越少，但更新数据花的时间就越长
1> 候选键(condidation key) : 是一列或多列的组合，其唯一地标识表中的一行
SQL> CREATE TABLE trouble(
city VARCHAR2(13) NOT NULL,
sample_date DATE NOT NULL,
noon NUMBER(4,1),
midnight NUMBER(4,1),
precipitation NUMBER,
CONSTRAINT trouble_uq UNIQUE(city,sample_date)
);
创建 UNIQUE 约束时，将创建唯一地索引来强制值得唯一性。在主键约束中，主键的列中至少有一列必须是 NOT NULL
2> 主键(primary key) : 具有特殊性质的候选键之一，可以仅有一个主键，并且主键列不能包含 NULL 值
SQL> CREATE TABLE trouble(
city VARCHAR2(13),
sample_date DATE,
noon NUMBER(4,1),
midnight NUMBER(4,1),
precipitation NUMBER,
CONSTRAINT trouble_pk PRIMARY KEY(city,sample_date)
);
# 对于但主键，可以使用如下方式定义
SQL> CREATE TABLE author(
author_name VARCHAR2(50)
PRIMARY KEY
,
comments VARCHAR2(100)
);
3> 指定索引表空间 : UNIQUE约束和 PRIMARY KEY 约束创建索引，索引默认存储在表空间中
SQL> CREATE TABLE author2(
author_name VARCHAR2(50),
comments VARCHAR2(100),
CONSTRAINT
author2_pk PRIMARY KEY(author_name) USING INDEX TABLESPACE USERS
)
注 : 在大多数默认安装中，会创建 USERS 表空间，他是默认表空间
<1> 外键(foreign key) : 也叫完整性约束，指明外键值对应另一个表中主键的实际值
SQL>
CREATE TABLE
bookshelf
(
title VARCHAR2(100)
PRIMARY KEY
,
publisher VARCHAR2(20),
category_Name VARCHAR2(20),
rating VARCHAR2(2),
CONSTRAINT bookshelf_fk FOREIGN KEY(category_Name) FERENCES category(category_name));
<2> CHECK约束 : 指定某个表达式，表达式对于表中的每一行数据都必须始终为真
SQL> CREATE TABLE rating_with_check(
rating NUMBER(1) CHECK(rating<=8),
rating_description VARCHAR2(50)
);
4> 命名约束 : 可以对约束命名，如果约束名使用一种有效的命名模式，能更好地识别和管理约束。约束名应该标识出它所作用的表和它所表示的约束类型。创建时如果没有指定约束名称，Oracle 会生成名称大多以 SYS_C##### 的形式因此最好自己命名
删除表 : 使用 DROP TABLE table_name 即可
SQL> DROP TABLE trouble;
使用 TRUNCATE 可以高效的删除表中的所有数据并回收空间，而不从数据库中删除删除表的定义
从 Oracle10g 开始，删除表仍保留表空间，可以在 "回收站" 中来访问，如果要从回收站中清除，使用 PURGE 子句
SQL> DROP TABLE trouble PURGE;
如果表已经删除，可以从 "回收站" 中清除所占的空间
SQL> PURGE TABLE trouble;
清除 "回收站" 中所有内容
SQL> PURGE RECYCLEBIN;
截断表
SQL> TRUNCATE TABLE trouble;
更改表
添加或更改列的规则
1> 可随时增加字符列宽度
2> 可随时增加 NUMBER 列的数字位数
3> 可随时增加或减少 NUMBER 列中的小数位数，对于减少的情况需要表为空
如果表中每一行的某一列都为 NULL，可作如下更改
1> 可以更改该列的数据类型
2> 可以减少字符列的宽度
3> 减少 NUMBER 列的数字位数
注 : 添加或更改列，更改后的列的每一行数据必须满足更改后列的数据限定，否则将会更改失败
添加列
语法 : alter table 表名 add (字段 字段类型) [ default '输入默认值'] [null/not null]
在空表中可以直接添加非空列
SQL> ALTER TABLE trouble ADD(
condition VARCHAR2(9) NOT NULL DEFAULT 'no',
wind NUMBER(3)
);
在有数据的表中添加非空列会出现 table must be empty to add mandatory (NOT NULL) column 错误，因为新添加的列为空，可以先不添加 NO NULL 增加完列后修改这列使之有数据然后再修改列增加 NO NULL
SQL> ALTER TABLE trouble ADD(
condition VARCHAR2(9),
wind NUMBER(3)
);
SQL> UPDATE trouble SET condition='no';
SQL> ALTER TABLE trouble MODIFY(condition VARCHAR2(9) NOT NULL);
更改列
SQL> DESCRIBE trouble;
Name Type Nullable Default Comments
------------- ------------ -------- ------- --------
CITY VARCHAR2(13)
SAMPLE_DATE DATE
NOON NUMBER(4,1) Y
MIDNIGHT NUMBER(4,1) Y
PRECIPITATION NUMBER Y
CONDITION VARCHAR2(9)
WIND NUMBER(3) Y
SQL> ALTER TABLE trouble MODIFY(city VARCHAR2(17));
SQL> DESCRIBE trouble;
Name Type Nullable Default Comments
------------- ------------ -------- ------- --------
CITY VARCHAR2(17)
SAMPLE_DATE DATE
NOON NUMBER(4,1) Y
MIDNIGHT NUMBER(4,1) Y
PRECIPITATION NUMBER Y
CONDITION VARCHAR2(9)
WIND NUMBER(3) Y
设置表只读
设置表只读这样就可以限制表的 INSERT、UPDATE 和 DELETE 操作
SQL> INSERT INTO trouble(city,sample_date) VALUES('武汉',SYSDATE);
1 row inserted
SQL> ALTER TABLE trouble READ ONLY;
Table altered
SQL> INSERT INTO trouble(city,sample_date) VALUES('成都',SYSDATE);
INSERT INTO trouble(city,sample_date) VALUES('成都',SYSDATE)
ORA-12081: update operation not allowed on table "C##CHENSHUN"."TROUBLE"
# 恢复表可读可写
SQL> ALTER TABLE trouble READ WRITE;
Table altered
SQL> INSERT INTO trouble(city,sample_date) VALUES('成都',SYSDATE);
1 row inserted
删除列
删除列比添加或修改列更复杂，因为 Oracle 必须执行一些额外工作。删除表中一列很简单，复杂的是恢复列占用的空间。将该列标记为 "unused" 稍后再删除，不会影响性能。如果立即删除，该操作会影响性能。可以使用 ALTER TABLE命令
SQL> SELECT* FROM trouble;
CITY SAMPLE_DATE NOON MIDNIGHT PRECIPITATION CONDITION WIND
----------------- ----------- ----- -------- ------------- --------- ----
Pleasant Lake 2011-3-21 40.0 -1.3 3.6 no
Pleasant Lake 2011-9-23 92.9 79.6 1.00003 no
Pleasant Lake 2011-12-22 -17.4 -10.4 2.4 no
武汉 2018-1-29 1
成都 2018-1-29 1
SQL> ALTER TABLE trouble DROP COLUMN wind;
# 删除多列
ALTER TABLE trouble DROP (
wind,condition
);
Table altered
SQL> SELECT * FROM trouble;
CITY SAMPLE_DATE NOON MIDNIGHT PRECIPITATION CONDITION
----------------- ----------- ----- -------- ------------- ---------
Pleasant Lake 2011-3-21 40.0 -1.3 3.6 no
Pleasant Lake 2011-9-23 92.9 79.6 1.00003 no
Pleasant Lake 2011-12-22 -17.4 -10.4 2.4 no
武汉 2018-1-29 1
成都 2018-1-29 1
标记列为 UNUSED，此时该列无法读取但空间不会释放，除非使用 DROP 删除列
SQL> SELECT * FROM trouble;
CITY SAMPLE_DATE NOON MIDNIGHT PRECIPITATION CONDITION
----------------- ----------- ----- -------- ------------- ---------
Pleasant Lake 2011-3-21 40.0 -1.3 3.6 no
Pleasant Lake 2011-9-23 92.9 79.6 1.00003 no
Pleasant Lake 2011-12-22 -17.4 -10.4 2.4 no
武汉 2018-1-29 1
成都 2018-1-29 1
SQL> ALTER TABLE trouble SET UNUSED COLUMN condition;
Table altered
SQL> SELECT * FROM trouble;
CITY SAMPLE_DATE NOON MIDNIGHT PRECIPITATION
----------------- ----------- ----- -------- -------------
Pleasant Lake 2011-3-21 40.0 -1.3 3.6
Pleasant Lake 2011-9-23 92.9 79.6 1.00003
Pleasant Lake 2011-12-22 -17.4 -10.4 2.4
武汉 2018-1-29 1
成都 2018-1-29 1
SQL> ALTER TABLE trouble DROP UNUSED COLUMNS;
Table altered
根据一个表创建另一个表，同时拷贝对应的数据
SQL> CREATE TABLE rain_table AS SELECT city,precipitation FROM trouble WHERE city IS NOT NULL;
Table created
SQL> SELECT * FROM rain_table;
CITY PRECIPITATION
----------------- -------------
Pleasant Lake 3.6
Pleasant Lake 1.00003
Pleasant Lake 2.4
武汉
成都
创建索引组织表
索引有两个主要用途 :
1> 强制唯一性 : 在创建 PRIMARY KEY 或 UNIQUE 结束时，Oracle 创建索引来强调索引列的唯一性
2> 改善性能 : 当某个查询使用索引时查询性能将显著改善
根据表的主键列值对数据进行排序，索引组织表存储数据时就像整个表存储在一个索引中。普通索引只存储索引列，而索引组织表则在索引中存储表中的所有列
# 经常通过
city,sample_date 创建索引那么在这两个字段上创建索引非常合适
SQL> CREATE TABLE trouble_iot(
city VARCHAR2(13),
sample_date DATE,
noon NUMBER(4,1),
midnight NUMBER(4,1),
precipitation NUMBER,
CONSTRAINT trouble_iot_pk PRIMARY KEY(city,sample_date) ORGANIZATION INDEX
);
如果索引对应的列数据频繁更改，那么就使用普通表
视图
视图不是为了方便查看，也不是看起来像新表，而是可以重命名，同时可以像表一样进行处理，可用于多张表级联查询
创建视图 : CREATE VIEW [视图名] AS 视图名
SQL> CREATE OR REPLACE VIEW newsView AS SELECT feature,section,page FROM newspaper WHERE page IN(SELECT page FROM newspaper WHERE section='M') ORDER BY page;
查看视图结构
SQL> DESCRIBE newsView;
视图可以像普通表一样查询，其对多表的查询被隐藏
注 : 1> 视图不包含任何数据，表包含数据，尽管可以创建包含数据的 “物化视图”，但它们是真实的表，而不是视图
2> 在查询表数据的时候，查询的字段以及少选择条件中的字段必须在视图中，否则会导致查询数据失败
修改视图的基表，视图仍然可见但是视图也会发生对应的改变
可以通过视图修改基表中的数据，创建只读是视图就无法修改
SQL> CREATE OR REPLACE VIEW rein_read_only AS SELECT * FROM trouble WITH READ ONLY;
SQL> INSERT INTO rein_read_only VALUES('北京',SYSDATE,1,2,3);
INSERT INTO rein_read_only VALUES('北京',SYSDATE,1,2,3)
ORA-42399: cannot perform a DML operation on a read-only view
数据类型
包含 字母、数字、空格以及其它符号 (如标点符号、特殊字符) 的串称为字符串
CHAR 定长字符串，有利于查询，插入的字符小于指定长度，将使用空格填充，在进行数据比较时会先比较空格长度是否相等 再进行比较
注 : CHAR 使用空格填充，填充的位置是在字符串结尾填充
VARCHAR 和 VARCHAR2 变长字符串，能很好的节省空间，这两个关键字是同义的，在以后的 ORacle 版本中会有所变化应尽量使用 VARCHAR2
串函数
|| : 将两个串连接在一起，"|" 符号被称为竖线或管道符
CONCAT : 连接两个字符串，功能和 || 相同，只接收两个参数
SQL> SELECT FEATURE||'-'||SECTION||'-'||PAGE FROM newspaper;
SQL> SELECT FEATURE || '-' || SECTION || '-' || PAGE FROM newspaper; # 和上一句效果等同
SQL> SELECT CONCAT(CONCAT(CONCAT(CONCAT(FEATURE, '-'), SECTION), '-'), PAGE) FROM newspaper;
INITCAP : 所有字符串首字母大写
LOWER : 转换成小写
UPPER : 转换成大写
# 首字母大写
SQL> select FEATURE, INITCAP(FEATURE) from newspaper where PAGE=8;
FEATURE INITCAP(FEATURE)
--------------- ----------------
Winw Winw
FEATURE point Feature Point
# 将字符串转换成 大写 小写
SQL> SELECT FEATURE,UPPER(FEATURE),LOWER(FEATURE) FROM newspaper;
LENGTH : 字符串长度
SQL> SELECT FEATURE,LENGTH(FEATURE) FROM newspaper;
SQL> SELECT FEATURE FROM newspaper ORDER BY LENGTH(FEATURE); # 按字符长度递增排序
SQL> SELECT FEATURE FROM newspaper WHERE
LENGTH(FEATURE) < 10
;
# 获取小于 10 个字符的数据
LPAD : 指定字符长度的空间，多余的空间在左边使用指定字符填充，如果字符长度超过指定长度字符串尾部多余的数据将会被截取，默认使用空格填充也就是没有第3个参数
RPAD : 指定字符长度的空间，多余的空间在右边使用指定字符填充，如果字符长度超过指定长度字符串尾部多余的数据将会被截取，默认使用空格填充也就是没有第3个参数
SQL> SELECT FEATURE,LPAD(FEATURE, 10, '.'),RPAD(FEATURE, 10, '-') FROM newspaper;
FEATURE LPAD(FEATURE,10,'.') RPAD(FEATURE,10,'-')
--------------- ---------------------------------------- ----------------------------------------
Births ....Births Births----
Memo ......Memo Memo------
Cat .......Cat Cat-------
KK ........KK KK--------
FEATURE point FEATURE po FEATURE po
5 rows selected
LTRIM : 去掉字符串左边的字符，如果操作的是多行字符串，会将多行字符串的每一行进行处理，接收两个参数，第二个参数可接收多个字符
RTRIM : 去掉字符串右边的字符，如果操作的是多行字符串，会将多行字符串的每一行进行处理，接收两个参数，第二个参数可接收多个字符
TRIM : 截取两边的字符串，只接收一个参数，且只能设置一个字符
SQL> SELECT FEATURE,LTRIM(FEATURE, 'BS'),RTRIM(FEATURE, 'sr'),TRIM('B' FROM FEATURE) FROM newspaper;
FEATURE LTRIM(FEATURE,'BS') RTRIM(FEATURE,'SR') TRIM('B'FROMFEATURE)
--------------- ------------------- ------------------- --------------------
Births irths Birth irths
Bridge ridge Bridge ridge
Sky ky Sky Sky
Scape cape Scape Scape
Winw Winw Winw Winw
Desk Desk Desk Desk
Boss oss Bo oss
Member Member Membe Member
Memo Memo Memo Memo
Cat Cat Cat Cat
KK KK KK KK
FEATURE point FEATURE point FEATURE point FEATURE point
12 rows selected
SUBSTR : 截取字符串，第二个参数指定开始地方 (基于1)如果是负数则从末尾开始计算，如果没有第三个参数那么将截取剩余参数，如果由第三个参数则表示指定截取字符串的长度
SQL> SELECT FEATURE,SUBSTR(FEATURE, 2),SUBSTR(FEATURE, 2, 4) FROM newspaper;
FEATURE SUBSTR(FEATURE,2) SUBSTR(FEATURE,2,4)
--------------- -------------------------------------------------------- -------------------
Births irths irth
Bridge ridge ridg
Memo emo emo
Cat at at
KK K K
FEATURE point EATURE point EATU
6 rows selected
注 : 对于 CHAR 类型，由于其是定长会使用空格填充，若其位置使用负数，将会从列的末尾确定开始位置，而不是从字符串末尾开始确定
数值处理
在 Oracle 中，数值列可以没有任何值，此时的值是 NULL 而不是 0，是空值，这在计算中相当有意义
加减乘除
SQL> SELECT name,above,below,empty,(above+below) AS plus,(above-below) AS subtr,(above*below) AS times, (above/below) AS divided FROM math;
NAME ABOVE BELOW EMPTY PLUS SUBTR TIMES DIVIDED
------------ ---------- ---------- ---------- ---------- ---------- ---------- ----------
Whole Number 11 -22 -11 33 -242 -0.5
Low Decimal 33.33 -44.44 -11.11 77.77 -1481.1852 -0.75
Mid Decimal 55.5 -55.5 0 111 -3080.25 -1
High Decimal 66.666 -77.777 -11.111 144.443 -5185.0814 -0.8571428
TO_NUMBER : 将 CHAR 或 VARCHAR2 转换成 NUMBER
NULL : 任何与 NULL 运算的结果都是 NULL，可以看做未知
SQL> SELECT name,above,below,empty,above+empty AS plus,above-empty AS subtr,above*empty AS times,above/empty AS divided FROM math;
NAME ABOVE BELOW EMPTY PLUS SUBTR TIMES DIVIDED
------------ ---------- ---------- ---------- ---------- ---------- ---------- ----------
Whole Number 11 -22
Low Decimal 33.33 -44.44
Mid Decimal 55.5 -55.5
High Decimal 66.666 -77.777
NVL : 该函数接收两个参数，当第一个参数不为 NULL 是为该值，当其为空时为第二个值，且两个参数类型要一致
SQL> SELECT name,NVL(name, 'empty'),NVL(empty, 0) FROM math;
NVL2 : 接收3个参数，当第一个参数不为 NULL 时返回 第二个参数，当第一个参数为 NULL 时返回 第三个参数
SQL> SELECT NVL2(name, 1, 2),NVL2(empty, 1, 2) FROM math;
NVL2(NAME,1,2) NVL2(EMPTY,1,2)
-------------- ---------------
1 2
1 2
1 2
1 2
ABS : 接收一个参数获取绝对值
SQL> SELECT name,below,ABS(below) FROM math;
CEIL : 获取大于或等于指定值的最小整数
FLOOR : 获取小于或等于指定值得最大整数
SQL> SELECT CEIL(1.2),CEIL(-0.3) FROM math;
SQL> SELECT
FLOOR
(1.2),
FLOOR
(-0.3) FROM math;
MOD : 求模运算，就是整除后的余数，接收的两个参数都是正整数，对于第一个参数不是整数的情况将会使用 FLOOR
REMAINDER : 和 MOD 功能一样，只是在对于第一个参数不是整数的情况将会使用 ROUND 方法
SQL> SELECT MOD(100, 10),MOD(22,23),MOD(10,3) FROM math;
MOD(100,10) MOD(22,23) MOD(10,3)
----------- ---------- ----------
0 22 1
SQL> SELECT MOD(7.5, 3),REMAINDER(7.5, 3) FROM math;
MOD(7.5,3) REMAINDER(7.5,3)
---------- ----------------
1.5 1.5
SQL> SELECT MOD(4.5, 3),REMAINDER(4.5, 3) FROM math;
MOD(4.5,3) REMAINDER(4.5,3)
---------- ----------------
1.5 -1.5
POWER : 乘方函数
SQRT : 平方根函数
SQL> SELECT POWER(3,2),POWER(4,3) FROM math;
POWER(3,2) POWER(4,3)
---------- ----------
9 64
SQL> SELECT SQRT(64),SQRT(49) FROM math;
SQRT(64) SQRT(49)
---------- ----------
8 7
聚集函数
聚集函数和数组函数是用于统计的函数，对于 NULL 值采用忽略的方式计算
AVG : 平均值
COUNT : 数据条数，可作用于字符列，另外该函数在计算时不管指定的是否为 NULL 都会计算
MAX : 最大值
MIN : 最小值
SUM : 所有值之和
SQL> SELECT AVG(noon),COUNT(noon),MAX(noon),MIN(noon),SUM(noon) FROM comfort;
AVG(NOON) COUNT(NOON) MAX(NOON) MIN(NOON) SUM(NOON)
---------- ----------- ---------- ---------- ----------
54.8285714 7 99.8 -7.2 383.8
SQL> SELECT (MAX(noon) - MIN(noon)) AS swing FROM comfort WHERE city='SAN FRANCISCO';
SWING
----------
11.4
SQL> SELECT COUNT(DISTINCT city),COUNT(city),COUNT(*) FROM comfort;
# 使用 DISTINCT 来获取不同值
COUNT(DISTINCTCITY) COUNT(CITY) COUNT(*)
------------------- ----------- ----------
2 8 8
DISTINCT 关键在在除 COUNT 函数以外的其它函数很少使用
聚合函数 RANK 和 DENSE_RANK 主要的功能是计算一组数值中的排序值
在 9i版本之前，只有分析功能(analytic)，即从一个查询结果中计算每一行的排序值，是基于 order_by_clause子句中的value_exprs指定字段的，语法 :
rank() over([query_partition_clause] order_by_clause)
dense_rank() over([query_partition_clause] order_by_clause)
在 9i版本新增加了合计功能(aggregate)，即对给定的参数值在设定的排序查询中计算出其排序值。这些参数必须是常数或常值表达式，且必须和 ORDER BY子句中的字段个数、位置、类型完全一致，语法 :
RANK ( expr [, expr]... ) WITHIN GROUP ( ORDER BY expr [ DESC | ASC ] [NULLS { FIRST | LAST }] [, expr [ DESC | ASC ] [NULLS { FIRST | LAST }]]...
区别 :
1> DENSE_RANK 在并列关系是，相关等级不会跳过，RANK 则跳过
2> RANK() 是跳跃排序，有两个第二名时接下来就是第四名(同样是在各个分组内)，DENSE_RANK()是连续排序，有两个第二名时仍然跟着第三名
RANK
SQL> create table xgj(col1 NUMBER,col2 NUMBER);
SQL> INSERT INTO xgj(col1, col2) VALUES(1,1);
SQL> INSERT INTO xgj(col1, col2) VALUES(2,1);
SQL> INSERT INTO xgj(col1, col2) VALUES(3,2);
SQL> INSERT INTO xgj(col1, col2) VALUES(3,2);
SQL> INSERT INTO xgj(col1, col2) VALUES(3,1);
SQL> INSERT INTO xgj(col1, col2) VALUES(4,1);
SQL> INSERT INTO xgj(col1, col2) VALUES(4,2);
SQL> INSERT INTO xgj(col1, col2) VALUES(5,2);
SQL> INSERT INTO xgj(col1, col2) VALUES(5,2);
SQL> INSERT INTO xgj(col1, col2) VALUES(6,2);
SQL> COMMIT;
# 列出Col2分组后根据Col1排序，并生成数字列
SQL> SELECT COL1,COL2,RANK() OVER(PARTITION BY col2 ORDER BY col1) AS rank FROM xgj;
COL1 COL2 RANK
---------- ---------- ----------
1 1 1
1 1 1
2 1 3
3 1 4
4 1 5
3 2 1
4 2 2
5 2 3
5 2 3
6 2 5
10 rows selected
SQL> create table xgj_2(score NUMBER,subject VARCHAR2(20));
SQL> INSERT INTO xgj_2(score, subject) VALUES(80,'数学');
SQL> INSERT INTO xgj_2(score, subject) VALUES(70,'语文');
SQL> INSERT INTO xgj_2(score, subject) VALUES(90,'数学');
SQL> INSERT INTO xgj_2(score, subject) VALUES(60,'数学');
SQL> INSERT INTO xgj_2(score, subject) VALUES(100,'数学');
SQL> INSERT INTO xgj_2(score, subject) VALUES(88,'语文');
SQL> INSERT INTO xgj_2(score, subject) VALUES(65,'语文');
SQL> INSERT INTO xgj_2(score, subject) VALUES(77,'语文');
SQL> COMMIT;
# 查询每门科目前3名的分数
SQL> SELECT * FROM (SELECT RANK() OVER(PARTITION BY subject ORDER BY score DESC) AS rank,SCORE,SUBJECT FROM xgj_2) WHERE rank<=3;
RANK SCORE SUBJECT
---------- ---------- --------------------
1 100 数学
2 90 数学
3 80 数学
1 88 语文
2 77 语文
3 70 语文
6 rows selected
# 计算出数值 (65,'语文') 在Orade By score,subject排序下的排序值，也就是score=65,subject='语文' 在排序以后的位置
SQL> SELECT RANK(65,'语文') WITHIN GROUP(ORDER BY score DESC,subject) AS rank FROM xgj_2;
RANK
----------
7
DENSE_RANK
SQL> create table xgj_3(a VARCHAR2(1),b VARCHAR2(20),c VARCHAR2(20));
SQL> INSERT INTO
xgj_3(a,b,c) VALUES('a','liu','wang');
SQL> INSERT INTO xgj_3(a,b,c) VALUES('a','jin','shu');
SQL> INSERT INTO xgj_3(a,b,c) VALUES('a','cai','kai');
SQL> INSERT INTO xgj_3(a,b,c) VALUES('b','yang','du');
SQL> INSERT INTO xgj_3(a,b,c) VALUES('b','lin','ying');
SQL> INSERT INTO xgj_3(a,b,c) VALUES('b','yao','cai');
SQL> INSERT INTO xgj_3(a,b,c) VALUES('b','yang','99');
SQL> select * FROM xgj_3;
A B C
- -------------------- --------------------
a liu wang
a jin shu
a cai kai
b yang du
b lin ying
b yao cai
b yang 99
7 rows selected
SQL> select m.a,m.b,m.c,rank() over(partition by a order by b) RK from xgj_3 m
2 ;
A B C RK
- -------------------- -------------------- ----------
a cai kai 1
a jin shu 2
a liu wang 3
b lin ying 1
b yang 99 2
b yang du 2
b yao cai 4
7 rows selected
SQL> select m.a,m.b,m.c,dense_rank() over(partition by a order by b) RK from xgj_3 m;
A B C RK
- -------------------- -------------------- ----------
a cai kai 1
a jin shu 2
a liu wang 3
b lin ying 1
b yang 99 2
b yang du 2
b yao cai 3
7 rows selected
列表函数
列表函数作用于列，用于对各列进行比较，从而选出最大值和最小值
GREATEST : 可以有很多各参数，每个参数都是列名，返回行中这几列最大的值，参数可以是 列、字面值、计算值等
LEAST : 可以有很多各参数，每个参数都是列名，返回行中这几列最小的值，参数可以是 列、字面值、计算值等
SQL> SELECT city,sample_date,midnight,noon,GREATEST(midnight,noon) AS high,LEAST(midnight,noon) AS low FROM comfort;
CITY SAMPLE_DATE MIDNIGHT NOON HIGH LOW
------------- ----------- -------- ----- ---------- ----------
SAN FRANCISCO 2003-3-21 42.3 62.5 62.5 42.3
SAN FRANCISCO 2003-6-22 71.9 51.1 71.9 51.1
SAN FRANCISCO 2003-9-23 61.5
SAN FRANCISCO 2003-12-22 39.8 52.6 52.6 39.8
KEENE 2003-3-21 -1.2 39.9 39.9 -1.2
KEENE 2003-6-22 66.7 85.1 85.1 66.7
KEENE 2003-9-23 82.6 99.8 99.8 82.6
KEENE 2003-12-22 -1.2 -7.2 -1.2 -7.2
8 rows selected
COALESCE : 计算多个值的非空值，返回第一个非空值，若全部都是 NULL 将返回 NULL
SQL> SELECT noon,midnight,COALESCE(noon, midnight) FROM comfort WHERE city='SAN FRANCISCO';
NOON MIDNIGHT COALESCE(NOON,MIDNIGHT)
----- -------- -----------------------
62.5 42.3 62.5
51.1 71.9 51.1
61.5 61.5
52.6 39.8 52.6
查询最高温度和最低温度的城市
SQL> SELECT city,sample_date,noon FROM comfort WHERE noon=(SELECT MAX(noon) FROM comfort) OR noon=(SELECT MIN(noon) FROM comfort);
CITY SAMPLE_DATE NOON
------------- ----------- -----
KEENE 2003-9-23 99.8
KEENE 2003-12-22 -7.2
日期时间
如果 BETWEEN、IN 运算前面的列是日期列，则 BETWEEN、IN 将进行日期运算
SQL> SELECT holiday,celebrated_date FROM holiday WHERE celebrated_date BETWEEN '01-JAN-12' AND '22-FEB-12';
HOLIDAY CELEBRATED_DATE
------------------------- ---------------
New Year's Day 2012-1-2
Martin Luther King, Jr. 2012-1-16
Lincoln's Birthday 2012-2-20
Washington's Birthday 2012-2-20
President's Day 2012-2-2
SQL> SELECT holiday,celebrated_date FROM holiday WHERE celebrated_date IN ('02-JAN-12','20-FEB-12');
HOLIDAY CELEBRATED_DATE
------------------------- ---------------
New Year's Day 2012-1-2
Lincoln's Birthday 2012-2-20
Washington's Birthday 2012-2-20
President's Day 2012-2-20
SYSDATE : 主机系统中的当前日期
CURRENT_DATE : 当前主机的时区的系统日期
SYSTIMESTAMP : 返回当前主机 TIMESTAMP 数据类型格式
SQL> SELECT SYSDATE,CURRENT_DATE,SYSTIMESTAMP FROM dual;
SYSDATE CURRENT_DATE SYSTIMESTAMP
----------- ------------ --------------------------------------------------------------------------------
2018-1-17 1 2018-1-17 22 17-JAN-18 02.37.07.083985 PM +00:00
ADD_MONTHS : 添加月份，有两个参数，第一个参数是时间，第二参数要添加的月
SQL> SELECT actual_date,ADD_MONTHS(actual_date,1),ADD_MONTHS(actual_date,-1) FROM holiday;
ACTUAL_DATE ADD_MONTHS(ACTUAL_DATE,1) ADD_MONTHS(ACTUAL_DATE,-1)
----------- ------------------------- --------------------------
2012-1-1 2012-2-1 2011-12-1
2012-1-16 2012-2-16 2011-12-16
2012-2-12 2012-3-12 2012-1-12
2012-2-22 2012-3-22 2012-1-22
2012-2-20 2012-3-20 2012-1-20
2012-5-28 2012-6-28 2012-4-28
2012-7-4 2012-8-4 2012-6-4
2012-9-3 2012-10-3 2012-8-3
2012-10-8 2012-11-8 2012-9-8
2012-11-25 2012-12-25 2012-10-25
10 rows selected
SQL> SELECT holiday,LEAST(actual_date,celebrated_date) AS first,actual_date,celebrated_date FROM holiday WHERE actual_date-celebrated_date!=0;
HOLIDAY FIRST ACTUAL_DATE CELEBRATED_DATE
------------------------- ----------- ----------- ---------------
New Year's Day 2012-1-1 2012-1-1 2012-1-2
Lincoln's Birthday 2012-2-12 2012-2-12 2012-2-20
Washington's Birthday 2012-2-20 2012-2-22 2012-2-20
TO_DATE : 接收一个参数，将日期字面值转换为 Oracle 能够用于面向日期的函数内部 Date 格式，第一个参数默认是日期字面值，第二个参数是日期格式没有第二个参数默认是 DD-MON-YY
SQL> SELECT LEAST(TO_DATE('20-JAN-12'), TO_DATE('20-DEC-12')) FROM dual;
LEAST(TO_DATE('20-JAN-12'),TO_
------------------------------
2012-1-20
SQL> SELECT TO_DATE('01/19/18','MM/DD/YY') FROM dual;
TO_DATE('01/19/18','MM/DD/YY')
------------------------------
2018-1-19
注 : LEAST 和 GREATEST 不会自动将日期字符串作为日期处理，其还是会将其作为字符串处理
TO_CHAR : 将 Oracle 日期转换成字符串，第一个参数必须是 Oracle 的 Date 类型，第二个参数是日期格式 没有第二个参数使用默认输出格式 DD-MON-YY
注 : 很多情况下，使用 EXTRACT 函数代替 TO_CHAR
SQL> SELECT birthdate,TO_CHAR(birthdate, 'YYYY-mm-dd HH:mm') AS format FROM birthday;
BIRTHDATE FORMAT
----------- ----------------
1976-5-12 1976-05-12 12:05
1967-8-23 1967-08-23 12:08
1977-2-2 1977-02-02 12:02
1979-5-20 3 1979-05-20 03:05
1972-11-11 1972-11-11 12:11
TO_CHAR 还可用于将其他类型转换成字符串，此时只能有一个参数且是纯数字
SQL> SELECT SUBSTR(TO_CHAR(123456),1,4)||'-'||SUBSTR(TO_CHAR(78910),2,3) FROM dual;
SUBSTR(TO_CHAR(123456),1,4)||'
------------------------------
1234-89
注 : 隐式转换将会导致索引无效
EXTRACT : 该函数用于替代 TO_CHAR 来选择日期值某一部分，语法如下 :
EXTRACT (
{ YEAR | MONTH | DAY | HOUR | MINUTE | SECOND }
| { TIMEZONE_HOUR | TIMEZONE_MINUTE }
| { TIMEZONE_REGION | TIMEZONE_ABBR }
FROM { date_value | interval_value } )
如
SQL> SELECT birthdate,EXTRACT(MONTH FROM birthdate) AS month FROM birthday;
BIRTHDATE MONTH
----------- ----------
1976-5-12 5
1967-8-23 8
1977-2-2 2
1979-5-20 3 5
1972-11-11 11
NEXT_DAY : 有两个参数，第一个是日期，第二个是星期 (Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday)，获取指定日期的之后第一个星期
SQL> SELECT cycle_date,NEXT_DAY(cycle_date,'FRIDAY') FROM payday;
CYCLE_DATE NEXT_DAY(CYCLE_DATE,'FRIDAY')
----------- -----------------------------
2004-1-15 2004-1-16
2004-2-15 2004-2-20
2004-3-15 2004-3-19
2004-4-15 2004-4-16
2004-5-15 2004-5-21
2004-6-15 2004-6-18
2004-7-15 2004-7-16
2004-8-15 2004-8-20
2004-9-15 2004-9-17
2004-10-15 2004-10-22
2004-11-15 2004-11-19
2004-12-15 2004-12-17
12 rows selected
LAST_DAY : 返回每个月最后一天
SQL> SELECT cycle_date,LAST_DAY(cycle_date) AS "End Month" FROM payday;
CYCLE_DATE End Month
----------- -----------
2004-1-15 2004-1-31
2004-2-15 2004-2-29
2004-3-15 2004-3-31
2004-4-15 2004-4-30
2004-5-15 2004-5-31
2004-6-15 2004-6-30
2004-7-15 2004-7-31
2004-8-15 2004-8-31
2004-9-15 2004-9-30
2004-10-15 2004-10-31
2004-11-15 2004-11-30
2004-12-15 2004-12-31
12 rows selected
SQL> SELECT SYSDATE AS today,LAST_DAY(ADD_MONTHS(SYSDATE,6)) + 1 review FROM dual;
TODAY REVIEW
----------- -----------
2018-1-17 1 2018-8-1 15
MONTHS_BETWEEN : 获取两个时间之间的月份差
SQL> SELECT first_name,last_name,birthdate,FLOOR(MONTHS_BETWEEN(sysdate,birthdate)/12) AS age FROM birthday;
# 获取年龄
FIRST_NAME LAST_NAME BIRTHDATE AGE
--------------- --------------- ----------- ----------
George Sand 1976-5-12 41
Robert James 1967-8-23 50
Nancy Lee 1977-2-2 40
Victoria Lynn 1979-5-20 3 38
Frank Pilot 1972-11-11 45
在 Oracle 日期相减不一定会是整数，这是由于 Oracle 计算时间会精确到秒，为减少非整数天数的麻烦，Oracle 做出一些假定 :
1> 以日期字符值输入的日期的默认时间为当天的开始时间 0 时
2> 通过 SQL*Plus 输入的日期，除非特别指定时间，否则将它设置为当天的开始时间 0时
3> 除非特意舍入，否则 SYSDATE 总是包括日期和时间。如果某一天的时间在中午之前，则使用 ROUND 函数将改天的时间设置为零点；如果其时间为中午之后，则设置为次日零点。TRUNC 函数功能与之类似，只不过它将任何时间包括午夜前一秒也设置成当天零点
SQL> SELECT ROUND(SYSDATE)- TO_DATE('17-JAN-18') FROM dual;
ROUND(SYSDATE)-TO_DATE('17-JAN
------------------------------
1
TIMESTAMP : DATE 存储精确到秒的日期和时间，TIMESTAMP存储精确到十亿分之一秒的日期
分组函数
GROUP BY : 按条件进行分组
SQL> SELECT category_name,COUNT(*) FROM bookshelf GROUP BY category_name;
CATEGORY_NAME COUNT(*)
-------------------- ----------
ADULTFIC 6
CHILDRENFIC 5
ADULTNF 10
ADULTREF 6
CHILDRENPIC 3
CHILDRENNF 1
6 rows selected
HAVING : 功能与 where 类似，只是逻辑仅和分组函数的结果有关，而不语但行列或表达式有关
SQL> SELECT category_name,COUNT(*),AVG(rating) FROM bookshelf GROUP BY category_name HAVING COUNT(*)>5;
CATEGORY_NAME COUNT(*) AVG(RATING)
-------------------- ---------- -----------
ADULTFIC 6 3.666666666
ADULTNF 10 4.2
ADULTREF 6 3.166666666
添加 ORDER BY 子句
SQL> SELECT category_name,COUNT(*) FROM bookshelf GROUP BY category_name ORDER BY category_name ASC;
CATEGORY_NAME COUNT(*)
-------------------- ----------
ADULTFIC 6
ADULTNF 10
ADULTREF 6
CHILDRENFIC 5
CHILDRENNF 1
CHILDRENPIC 3
6 rows selected
SQL> SELECT category_name,COUNT(*) FROM bookshelf GROUP BY category_name ORDER BY COUNT(*) DESC;
CATEGORY_NAME COUNT(*)
-------------------- ----------
ADULTNF 10
ADULTREF 6
ADULTFIC 6
CHILDRENFIC 5
CHILDRENPIC 3
CHILDRENNF 1
6 rows selected
注 : 可以使用列的别名作为 ORDER BY 子句的一部分，但不能作为 HAVING 子句的一部分
执行循序 :
1> 根据 WHERE 子句选择行
2> 根据 GROUP BY 子句将这些行进行分组
3> 为每一组计算分组函数的结果
4> 根据 HAVING 子句选择和排序
5> 根据 ORDER BY 子句中分组函数的结果对组进行排序，ORDER BY子句必须使用分组函数，或者使用在 GROUP BY 子句中指定列
SQL> SELECT category_name,COUNT(*),AVG(rating) FROM bookshelf WHERE rating > 1 GROUP BY category_name HAVING category_name LIKE 'A%' ORDER BY COUNT(*) DESC;
CATEGORY_NAME COUNT(*) AVG(RATING)
-------------------- ---------- -----------
ADULTNF 10 4.2
ADULTREF 6 3.166666666
ADULTFIC 6 3.666666666
SQL> SELECT category_name,COUNT(*),AVG(rating) FROM bookshelf WHERE rating > 1 AND category_name LIKE 'A%' GROUP BY category_name ORDER BY COUNT(*) DESC;
# 直接筛选出更少的数据进行分组，速度更快
CATEGORY_NAME COUNT(*) AVG(RATING)
-------------------- ---------- -----------
ADULTNF 10 4.2
ADULTREF 6 3.166666666
ADULTFIC 6 3.666666666
依赖查询
SQL> DESCRIBE category;
Name Type Nullable Default Comments
--------------- ------------ -------- ------- --------
CATEGORY_NAME VARCHAR2(12)
PARENT_CATEGORY VARCHAR2(8) Y
SUB_CATEGORY VARCHAR2(20) Y
SQL> DESCRIBE bookshelf;
Name Type Nullable Default Comments
------------- ------------- -------- ------- --------
TITLE VARCHAR2(100)
PUBLISHER VARCHAR2(20) Y
CATEGORY_NAME VARCHAR2(20) Y
RATING VARCHAR2(2) Y
SQL> DESCRIBE bookshelf_checkout;
Name Type Nullable Default Comments
------------- ------------- -------- ------- --------
NAME VARCHAR2(25) Y
TITLE VARCHAR2(100) Y
CHECKOUT_DATE DATE Y
RETURNED_DATE DATE Y
SQL> DESCRIBE bookshelf_author;
Name Type Nullable Default Comments
----------- ------------- -------- ------- --------
TITLE VARCHAR2(100) Y
AUTHOR_NAME VARCHAR2(50) Y
EXISTS : 检查是否存在值
// 检查书架上作者写了多少书
SQL> SELECT author_name,title FROM bookshelf_author ba WHERE EXISTS(SELECT 'x' FROM bookshelf_author ba2 WHERE ba.author_name=ba2.author_name GROUP By ba2.author_name HAVING COUNT(ba2.title)>1);
AUTHOR_NAME TITLE
-------------------------------------------------- --------------------------------------------------------------------------------
soren kierkegaard kierkegaard anthology
dietrich bonhoeffer letters and papers from prison
soren kierkegaard either/or
david mccullough john adams
david mccullough truman
wilton barnhardt gospel
wilton barnhardt emma who saved my life
w. p. kinsella shoeless joe
w. p. kinsella box socials
e. b. white trumpet of the swan
e. b. white charlotte's web
dietrich bonhoeffer the cost of discipleship
12 rows selected
在 Oracle 9i 之前的版本外部链接的语法使用 (+)，他将返回额外的行
使用外部链接代替 NOT IN，以提高性能
SQL> DESCRIBE bookshelf;
Name Type Nullable Default Comments
------------- ------------- -------- ------- --------
TITLE VARCHAR2(100)
PUBLISHER VARCHAR2(20) Y
CATEGORY_NAME VARCHAR2(20) Y
RATING VARCHAR2(2) Y
SQL> DESCRIBE bookshelf_checkout;
Name Type Nullable Default Comments
------------- ------------- -------- ------- --------
NAME VARCHAR2(25) Y
TITLE VARCHAR2(100) Y
CHECKOUT_DATE DATE Y
RETURNED_DATE DATE Y
SQL> SELECT title FROM bookshelf WHERE title NOT IN(SELECT title FROM bookshelf_checkout) ORDER BY title;
TITLE
--------------------------------------------------------------------------------
Box Socials
Charlotte's Web
Complete Poems Of John Keats
Emma Who Saved My Life
Gospel
Journals Of Lewis And Clark
Kierkegaard Anthology
Letters And Papers From Prison
Preaching To Head And Heart
Runaway Bunny
Shoeless Joe
The Cost Of Discipleship
The Good Book
Trumpet Of The Swan
Under The Eye Of The Clock
15 rows selected
SQL> SELECT DISTINCT b.title FROM bookshelf b LEFT JOIN bookshelf_checkout bc ON b.title=bc.title WHERE bc.title IS NULL ORDER BY b.title;
TITLE
--------------------------------------------------------------------------------
Box Socials
Charlotte's Web
Complete Poems Of John Keats
Emma Who Saved My Life
Gospel
Journals Of Lewis And Clark
Kierkegaard Anthology
Letters And Papers From Prison
Preaching To Head And Heart
Runaway Bunny
Shoeless Joe
The Cost Of Discipleship
The Good Book
Trumpet Of The Swan
Under The Eye Of The Clock
15 rows selected
还可以使用 NOT EXISTS 代替 NOT IN
SQL> SELECT b.title FROM bookshelf b WHERE NOT EXISTS(SELECT 'x' FROM bookshelf_checkout bc WHERE b.title=bc.title) ORDER BY b.title;
TITLE
--------------------------------------------------------------------------------
Box Socials
Charlotte's Web
Complete Poems Of John Keats
Emma Who Saved My Life
Gospel
Journals Of Lewis And Clark
Kierkegaard Anthology
Letters And Papers From Prison
Preaching To Head And Heart
Runaway Bunny
Shoeless Joe
The Cost Of Discipleship
The Good Book
Trumpet Of The Swan
Under The Eye Of The Clock
15 rows selected
UNION、INTERSECT 和 MINUS
在合并两个表时，Oracle 并不关心合并运算符任何一边的列名，也就是说虽然 Oracle 要求每一条 SELECT 语句都是有效的并对他自己的表有效的列，但第一条 SELECT 语句中的列名不必和第二条 SELECT 语句中的雷鸣相同，Oracle 有这样一些约定 :
1> SELECT 语句必须具有相同的列数，如果被查询的两个表具有不同数目选择列，可选择字符串代替列，以便使两个查询的列的列表匹配
2> SELECT 语句中相应的列必须有相同的数据类型 (长度可以不同)
注 : 虽然可以对两张表或多张表使用合并运算，但如果这样做，优先级次序就会出现问题，特别在使用 INTERSECT 和 MINUS 更是如此，应该使用圆括号来强制所需的执行顺序
UNION(并)，使用 UNION 合并两个查询并去掉重复项，使用 UNION ALL 合并两个查询不去掉重复项
SQL> SELECT title FROM bookshelf WHERE title<'M' UNION SELECT title FROM book_order;
TITLE
--------------------------------------------------------------------------------
Anne Of Green Gables
Box Socials
Charlotte's Web
Complete Poems Of John Keats
Either/Or
Emma Who Saved My Life
Galileo's Daughter
Good Dog, Carl
Gospel
Harry Potter And The Goblet Of Fire
Innumeracy
John Adams
Journals Of Lewis And Clark
Kierkegaard Anthology
Letters And Papers From Prison
Longitude
Once Removed
Shoeless Joe
Something So Strong
19 rows selected
SQL> SELECT title FROM bookshelf WHERE title<'M' UNION ALL SELECT title FROM book_order;
TITLE
--------------------------------------------------------------------------------
Anne Of Green Gables
Box Socials
Charlotte's Web
Complete Poems Of John Keats
Either/Or
Emma Who Saved My Life
Good Dog, Carl
Gospel
Harry Potter And The Goblet Of Fire
Innumeracy
John Adams
Journals Of Lewis And Clark
Kierkegaard Anthology
Letters And Papers From Prison
Galileo's Daughter
Gospel
Longitude
Once Removed
Shoeless Joe
Something So Strong
20 rows selected
INTERSECT(交)
SQL> SELECT title FROM bookshelf INTERSECT SELECT title FROM book_order ORDER BY title;
TITLE
--------------------------------------------------------------------------------
Gospel
Shoeless Joe
MINUS(差)
# 查询新书
SQL> SELECT title FROM book_order MINUS SELECT title FROM bookshelf ORDER BY title;
TITLE
--------------------------------------------------------------------------------
Galileo's Daughter
Longitude
Once Removed
Something So Strong
# 未借出书籍
SQL> SELECT title FROM bookshelf MINUS SELECT title FROM bookshelf_checkout;
TITLE
--------------------------------------------------------------------------------
Box Socials
Charlotte's Web
Complete Poems Of John Keats
Emma Who Saved My Life
Gospel
Journals Of Lewis And Clark
Kierkegaard Anthology
Letters And Papers From Prison
Preaching To Head And Heart
Runaway Bunny
Shoeless Joe
The Cost Of Discipleship
The Good Book
Trumpet Of The Swan
Under The Eye Of The Clock
15 rows selected
临时表
可以在会话中创建独立存在的表，或者创建数据在事务处理期间可以永久存在的表。可以使用临时表来支持专门的统计或支持特定的处理要求，即使有 COMMIT 运算结果也不会持久保存
使用 CREATE GLOBAL TEMPORARY TABLE 命令创建临时表，在创建表时，可以指定它是否在整个会话期间都存在 (通过 ON COMMIT PRESERVE ROWS子句)，或者在事务处理完成时是否删除它的行(通过 ON COMMIT DELETE ROWS字句)
与永久表不同的是，在创建临时表时不会自动分配空间，表的空间实在插入行时动态分配的
ROLLBACK、COMMIT 和 AUTOCOMMIT 命令
使用 COMMIT 提交已完成的工作后 INSERT、UPDATE 和 DELETE操作才算最后完成，可以使用 SET 设置 AUTOCOMMIT 成 true 自动提交 默认是 false 且推荐关闭
注 : ROLLBACK 对表数据的修改进行回滚，但是对 修改表结构 或 增加、删除表无法恢复
在对数据进行插入、更新或删除数据的操作时，可以后退或回滚(ROLLBACK) 已完成的工作，ROLLBACK 只能回滚那些未被提交的工作
SAVEPOINT 用于回滚当前事务集的部分事务，使用 COMMIT 所有的 SAVEPOINT 都会丢失 (提交数据后SAVEPOINT的存在也没有什么意义)
SQL> INSERT INTO comfort VALUES('Valpole','22-apr-11',50.1,24.8,0);
1 row inserted
SQL> SAVEPOINT a;
Savepoint created
SQL> INSERT INTO comfort VALUES('Walpole','27-apr-11',63.7,33.8,0);
1 row inserted
SQL> SAVEPOINT b;
Savepoint created
SQL> INSERT INTO comfort VALUES('Aalpole','07-apr-11',72.7,33.8,0);
1 row inserted
SQL> SELECT * FROM comfort;
CITY SAMPLE_DATE NOON MIDNIGHT PRECIPITATION
------------- ----------- ----- -------- -------------
San Francisco 2011-3-21 62.5 42.3 0.5
San Francisco 2011-6-22 51.1 71.9 0.1
San Francisco 2011-9-23 61.5 0.1
San Francisco 2011-12-22 52.6 39.8 2.3
Keene 2011-3-21 39.9 -1.2 4.4
Keene 2011-6-22 85.1 66.7 1.3
Keene 2011-9-23 99.8 82.6
Keene 2011-12-22 -7.2 -1.2 3.9
Walpole 2011-12-22 -7.2 -1.2 3.9
Valpole 2011-4-22 50.1 24.8 0
Walpole 2011-4-27 63.7 33.8 0
Aalpole 2011-4-7 72.7 33.8 0
12 rows selected
SQL> ROLLBACK TO SAVEPOINT b; # 会管道保存点
Rollback complete
SQL> SELECT * FROM comfort;
CITY SAMPLE_DATE NOON MIDNIGHT PRECIPITATION
------------- ----------- ----- -------- -------------
San Francisco 2011-3-21 62.5 42.3 0.5
San Francisco 2011-6-22 51.1 71.9 0.1
San Francisco 2011-9-23 61.5 0.1
San Francisco 2011-12-22 52.6 39.8 2.3
Keene 2011-3-21 39.9 -1.2 4.4
Keene 2011-6-22 85.1 66.7 1.3
Keene 2011-9-23 99.8 82.6
Keene 2011-12-22 -7.2 -1.2 3.9
Walpole 2011-12-22 -7.2 -1.2 3.9
Valpole 2011-4-22 50.1 24.8 0
Walpole 2011-4-27 63.7 33.8 0
11 rows selected
SQL> ROLLBACK;
# 回滚所有未提交的数据
Rollback complete
SQL> SELECT * FROM comfort;
CITY SAMPLE_DATE NOON MIDNIGHT PRECIPITATION
------------- ----------- ----- -------- -------------
San Francisco 2011-3-21 62.5 42.3 0.5
San Francisco 2011-6-22 51.1 71.9 0.1
San Francisco 2011-9-23 61.5 0.1
San Francisco 2011-12-22 52.6 39.8 2.3
Keene 2011-3-21 39.9 -1.2 4.4
Keene 2011-6-22 85.1 66.7 1.3
Keene 2011-9-23 99.8 82.6
Keene 2011-12-22 -7.2 -1.2 3.9
Walpole 2011-12-22 -7.2 -1.2 3.9
9 rows selected
SQL> ROLLBACK TO SAVEPOINT a; # 回滚后相应的 保存点丢失
ROLLBACK TO SAVEPOINT a
ORA-01086: savepoint 'A' never established in this session or is invalid
隐式提交 : 即使没有直接下达提交指令，有些操作(如 QUIT、EXIT(等价于QUIT))以及任意数据定义语言(DDL)的命令也会强制提交发生，使用这些指令会强制提交事务
自动回滚 : 当主机遇到严重故障时将会自动回滚未提交的工作
插入
INSERT 命令把一行信息直接插入到一个表中(或通过一个视图插入)
SQL> INSERT INTO COMFORT VALUES('SAN FRANCISCO', TO_DATE('21-MAR-2003','DD-MON-YYYY'),62.5,42.3,.5,NULL); # NULL 表示插入空数据
使用 SELECT 插入数据，用于将某个表中的选择信息插入到另外一个表中
SQL> INSERT INTO comfort(sample_date,precipitation,city,noon,midnight)
SELECT '22-dec-11',precipitation,'Walpole',noon,midnight FROM comfort WHERE city='Keene' AND sample_date='22-dec-11';
使用 SELECT 插入多行数据时可能性能不是很好，可以通过 APPEND 提示来改善执行计划，从而盖上大量数据插入操作的性能。APPEND 提示僵尸数据库查找已经被插入表中数据的最后一块，新的记录将从表的最高水位线之上重新分配的 extent 中的第一个块开始插入。此外，插入的数据将直接写入数据文件中。而不先进入数据块缓存。因此，在插入期间对数据库进行的空间管理工作就很少。于是，在使用 APPEND 提示时，插入操作会进行得很快。使用的 APPEND 提示看起来像一个注释，因为它也可以 /* 开始并以 */ 结束。唯一不同的是，字符集开始时会在提示名前加一个 "+" 号
SQL> INSERT /*+ append */ INTO bookshelf(title)
SELECT title FROM book_order WHERE title NOT IN(SELECT title FROM bookshelf);
新插入的记录将放在表的物理存储空间的尾部，而不是重用 bookshelf表以前所用过的空间。因为新记录不会重新使用该表已经使用过的可用空间，所以对 bookshelf表空间需求会有所增加。通常，只有在把大量的数据插入到具有较少的可重置空间表中时，才使用 APPEND 提示。插入追加记录的点称为表的高水位标记(high-water mark)，重置高水位标记的唯一方式是把表截断(TRUNCATE)。因为 TRUNCATE 将删除所有的记录并且不能回滚，所以应该确保在执行 TRUNCATE 操作之前该表有数据备份
多表插入
可在一条命令中执行多个插入，指定 ALL 将判定所有的 WHEN字句
SQL> CREATE TABLE comfort_test(city VARCHAR2(13) NOT NULL,sample_date DATE NOT NULL,measure VARCHAR2(10),value NUMBER(3,1));
SQL>
INSERT ALL
INTO comfort_test(city,sample_date,measure,value) VALUES(city,sample_date,'Noon',noon)
INTO comfort_test(city,sample_date,measure,value) VALUES(city,sample_date,'Midnight',midnight)
INTO comfort_test(city,sample_date,measure,value) VALUES(city,sample_date,'Precip',precipitation)
SELECT city,sample_date,noon,midnight,precipitation FROM comfort WHERE city='Keene';
SQL> SELECT * FROM comfort_test;
CITY SAMPLE_DATE MEASURE VALUE
------------- ----------- ---------- -----
Keene 2011-3-21 Noon 39.9
Keene 2011-6-22 Noon 85.1
Keene 2011-9-23 Noon 99.8
Keene 2011-12-22 Noon -7.2
Keene 2011-3-21 Midnight -1.2
Keene 2011-6-22 Midnight 66.7
Keene 2011-9-23 Midnight 82.6
Keene 2011-12-22 Midnight -1.2
Keene 2011-3-21 Precip 4.4
Keene 2011-6-22 Precip 1.3
Keene 2011-9-23 Precip
Keene 2011-12-22 Precip 3.9
12 rows selected
指定 FIRST 会在找到第一个判断为真的 WHEN 子句后将忽略后面的 WHEN 子句
SQL>
INSERT ALL
WHEN noon > 80 THEN
INTO comfort_test(city,sample_date,measure,value) VALUES(city,sample_date,'Noon',noon)
WHEN midnight > 70 THEN
INTO comfort_test(city,sample_date,measure,value) VALUES(city,sample_date,'Midnight',midnight)
WHEN precipitation IS NOT NULL THEN
INTO comfort_test(city,sample_date,measure,value) VALUES(city,sample_date,'Precip',precipitation)
SELECT city,sample_date,noon,midnight,precipitation FROM comfort WHERE city='Keene';
SQL> SELECT * FROM comfort_test;
CITY SAMPLE_DATE MEASURE VALUE
------------- ----------- ---------- -----
Keene 2011-6-22 Noon 85.1
Keene 2011-9-23 Noon 99.8
Keene 2011-9-23 Midnight 82.6
Keene 2011-3-21 Precip 4.4
Keene 2011-6-22 Precip 1.3
Keene 2011-12-22 Precip 3.9
6 rows selected
SQL> ROLLBACK;
SQL> INSERT FIRST
WHEN noon > 80 THEN
INTO comfort_test(city,sample_date,measure,value) VALUES(city,sample_date,'Noon',noon)
WHEN midnight > 70 THEN
INTO comfort_test(city,sample_date,measure,value) VALUES(city,sample_date,'Midnight',midnight)
WHEN precipitation IS NOT NULL THEN
INTO comfort_test(city,sample_date,measure,value) VALUES(city,sample_date,'Precip',precipitation)
SELECT city,sample_date,noon,midnight,precipitation FROM comfort WHERE city='Keene';
SQL> SELECT * FROM comfort_test;
CITY SAMPLE_DATE MEASURE VALUE
------------- ----------- ---------- -----
Keene 2011-6-22 Noon 85.1
Keene 2011-9-23 Noon 99.8
Keene 2011-3-21 Precip 4.4
Keene 2011-12-22 Precip 3.9
在指定 FIRST语句中，对每一条数据进行判定，其中的 Midnight 的 WHEN midnight > 70 THEN 判定条件也是满足 WHEN noon > 80 THEN 因此只会插入 Noon
删除
使用 DELETE 语句来删除数据可以进行回滚，TRUNCATE 用于清空表数据在进行请空表数据方面比使用 DELETE 效率高但是无法进行回滚
SQL> SELECT * FROM comfort_test;
CITY SAMPLE_DATE MEASURE VALUE
------------- ----------- ---------- -----
Keene 2011-6-22 Noon 85.1
Keene 2011-9-23 Noon 99.8
Keene 2011-3-21 Precip 4.4
Keene 2011-12-22 Precip 3.9
SQL> TRUNCATE TABLE comfort_test;
Table truncated
SQL> SELECT * FROM comfort_test;
CITY SAMPLE_DATE MEASURE VALUE
------------- ----------- ---------- -----
更新
使用 UPDATE 更新数据，在使用子查询进行更新数据时必须确保查询出的数据只有一条
可以更新某一列为 NULL，这是使用 NULL 而不使用 IS 关键字的唯一示例
SQL> UPDATE comfort SET noon=NULL WHERE city='Keene' AND sample_date='22-dec-11';
MERGE 命令
使用 MERGE 命令 对单个表执行 INSERT 和 UPDATE 操作，根据指定条件 Oracle 将接收数据源(可以是表、视图或查询)。如果满足条件，就更新已有的值，如果不满足条件就插入此行
SQL>
CREATE TABLE comfort2(City VARCHAR2(13) NOT NULL,
Sample_Date DATE NOT NULL,
Noon NUMBER(3,1),
Midnight NUMBER(3,1),
Precipitation NUMBER
);
SQL> INSERT INTO comfort2 VALUES('Keene','21-mar-11',55,-2.2,4.4);
SQL> INSERT INTO comfort2 VALUES('Keene','22-dec-11',55,66,0.5);
SQL> INSERT INTO comfort2 VALUES('Keene','16-may-11',55,55,1);
SQL> COMMIT;
SQL> SELECT * FROM comfort2;
CITY SAMPLE_DATE NOON MIDNIGHT PRECIPITATION
------------- ----------- ----- -------- -------------
Keene 2011-3-21 55.0 -2.2 4.4
Keene 2011-12-22 55.0 66.0 0.5
Keene 2011-5-16 55.0 55.0 1
SQL> SELECT * FROM comfort WHERE city='Keene';
CITY SAMPLE_DATE NOON MIDNIGHT PRECIPITATION
------------- ----------- ----- -------- -------------
Keene 2011-3-21 39.9 -1.2 4.4
Keene 2011-6-22 85.1 66.7 1.3
Keene 2011-9-23 99.8 82.6
Keene 2011-12-22 -7.2 -1.2 3.9
# 对于匹配 21-MAR-11 和 22-DEC-11 项的行将更新 confort表 中的数据，对于仅存在 comfort2 中的行将插入到 COMFORT 表中
SQL>
MERGE INTO comfort c1
USING (SELECT city,sample_date,noon,midnight,precipitation FROM comfort2) c2
ON (c1.city=c2.city AND c1.sample_date=c2.sample_date)
WHEN MATCHED THEN
UPDATE SET noon=c2.noon
WHEN NOT MATCHED THEN
INSERT(c1.city,c1.sample_date,c1.noon,c1.midnight,c1.precipitation) VALUES(c2.city,c2.sample_date,c2.noon,c2.midnight,c2.precipitation);
SQL> SELECT * FROM comfort WHERE city='Keene';
CITY SAMPLE_DATE NOON MIDNIGHT PRECIPITATION
------------- ----------- ----- -------- -------------
Keene 2011-3-21 55.0 -1.2 4.4
Keene 2011-6-22 85.1 66.7 1.3
Keene 2011-9-23 99.8 82.6
Keene 2011-12-22 55.0 -1.2 3.9
Keene 2011-5-16 55.0 55.0 1
对于要从源表中插入数据和更新许多行的操作，可以使用 MERGE 命令进行简化
在 MERGE命令的 UPDATE字句中可以包含 DELETE子句
SQL>
MERGE
INTO comfort c1
USING (SELECT city,sample_date,noon,midnight,precipitation FROM comfort2) c2 ON (c1.city=c2.city AND c1.sample_date=c2.sample_date)
WHEN MATCHED THEN
UPDATE SET noon=c2.noon
DELETE WHERE(precipitation IS NULL)
WHEN NOT MATCHED THEN
INSERT(c1.city,c1.sample_date,c1.noon,c1.midnight,c1.precipitation) VALUES(c2.city,c2.sample_date,c2.noon,c2.midnight,c2.precipitation);
if-then-else
使用 DECODE 函数 : DECODE(value,if1,then1,if2,then2...,else)
其中 value 表示表中任意一列或计算结果，每一行都对 value 进行测试，如果 value 符合条件 if1 则 DECODE 的结果为 then1，依次类推，如果都不符合结果为 else
SQL> SELECT b.category_name,
MAX(DECODE(bc.name,'Fred Fuller',bc.returned_date-bc.checkout_date,null)) maxff,
AVG(DECODE(bc.name,'Fred Fuller',bc.returned_date-bc.checkout
_
date,null)) avgff,
MAX(DECODE(bc.name,'Dorah Talbot',bc.returned_date-bc.checkout
_
date,null)) maxdt,
AVG(DECODE(bc.name,'
Dorah Talbot
',bc.returned_date-bc.checkout
_
date,null)) avgdt,
MAX(DECODE(bc.name,'Gerhardt Kentgen',bc.returned_date-bc.checkout
_
date,null)) maxgk,
AVG(DECODE(bc.name,'Gerhardt Kentgen
',bc.returned_date-bc.checkout
_
date,null)) avggk
FROM bookshelf_checkout bc,bookshelf b WHERE bc.title=b.title GROUP BY b.category_name ORDER BY
b.category_name;
可以使用 CASE 来代替 DECODE，CASE函数 显得比较冗长但是可读性高，CASE 函数使用关键字 WHEN、THEN、ELSE 和 END
SQL> SELECT DISTINCT DECODE(
category_name,'A','NAME_A','B','NAME_B',category_name
) FROM bookshelf;
SQL> SELECT DISTINCT CASE
category_name
WHEN 'A' THEN 'NAME_A'
WHEN 'B' THEN 'NAME_B'
ELSE
category_name
END
FROM
bookshelf;
索引
Oracle 中标准索引类型称为 B树索引，是将列值与其相关的 RowID 相
创建索引 : 可通过 CREATE INDEX 指令创建索引，如果设置主键或唯一列将会自动创建唯一索引
常用格式 : CREATE [BITMAP | UNIQUE] INDEX index_name ON TABLE(column1...) [REVERSE];
索引名称必须唯一，位图索引可以在只有很少几个不同值的列上创建有用的索引。REVERSE 关键字指定反转索引值得字节，这在插入许多有序的数据值时可以改善数据库的 I/O 分布
防止数据库创建重复主键，有两种措施 :
1> 通过索引或约束来保证唯一性
2> 使用序列生成器
创建索引的3中方式 :
1> 创建主键约束
2> 创建唯一约束
3> 创建唯一索引
SQL> ALTER TABLE
bookshelf_author ADD CONSTRAINT ba_pk PRIMARY KEY(title,author_name);
SQL> CREATE UNIQUE INDEX ba_index ON
bookshelf_author
(title,author_name);
索引在大型表，以及在 WHERE 子句中以两边相等的形式出现非常有效。除支持 WHERE 子句和连接外，索引还支持 ORDER BY 子句和 MAX、MIN 函数。在一些情况下，Oracle 会选择扫描索引而不是扫描整个表
创建不可见索引 : 创建索引后可以通过 ALTER INDEX 指定在生成执行计划时不考虑这个索引
SQL> ALTER INDEX
ba_index INVISIBLE;
传统索引(B树索引) 对于包含大量可变数据的列非常有用，如电话号码就很适合，但对于 Y N(是否) 就不适宜而且还会影响速度。小型表建议不要创建索引。一般索引会加快数据访问速度，但是同时也会降低数据的 INSERT、UPDATE、DELETE 速度。从加载性能来看，拥有较少的索引但每个索引中列数较多，比拥有较多索引但每个索引中列数较少更好。NULL 不会在索引中有项
通过给索引分配指定的表空间来指定表的索引将要放置的位置，表空间是数据库的逻辑分区，对应于一个或多个数据文件。数据库文件提供数据库使用的物理存储(存储表和索引的磁盘扇区)。数据库有几个表空间，每个表空间都有自己的名称。为提高可用性和管理选项，表的索引应该放置在物理上把磁盘驱动器和相应的表分隔开的表空间中
指定放置索引的表空间
SQL> CREATE UNIQUE INDEX
ON bookshelf_author(title,author_name) TABLESPACE ba_index;
# 使用 USING INDEX 为约束创建的索引指定存储参数和表空间位置
SQL> ALTER TABLE bookshelf_author ADD CONSTRINT PRIMARY KEY(title,author_name) USING INDEX TABLESPACE ba_index;
重建索引 : 重建但不删除已有索引，在重建索引过程中可以修改 STORAGE 和 TABLESPACE 值，重建索引必须要有足够的空间同时容纳新旧索引，创建完成之后将删除旧索引
# 使用 8M 初始化区和 4M 下一个扩展区
SQL> ALTER INDEX ba_pk REBUILD
STORAGE(INITIAL 8M NEXT 4M PCTINCREASE 0) TABLESPACE ba_index;
基于函数索引 : 使用函数作为查询条件可能导致无法使用列上的索引，可以创建基于函数访问的索引
SQL> CREATE INDEX
ba_pk ON bookshelf(UPPER(title));
序列
使用序列可以把唯一的数值分配各数据库中的列，不需要创建特殊的表和代码来记录使用中的唯一的数值
SQL> CREATE SEQUENCE customer_id INCREMENT BY 1 START WITH 1000;
#
NEXTVAL 获取序列中下一个有效值
SQL> INSERT INTO customer_demo(name,contact,id) VALUES('Cole Construction','Veronica',
customer_id.NEXTVAL
);
# CURRVAL 获取当前有效值
SQL> INSERT INTO customer_demo(name,contact,id) VALUES('Cole Construction','Veronica',
customer_id.CURRVAL
);
251
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
