本文共 43759 字,大约阅读时间需要 145 分钟。
DDL : 数据库模式定义语言,关键字:create
DML : 数据操纵语言,关键字:Insert、delete、update
DCL : 数据库控制语言 ,关键字:grant、remove
结构化查询语言 (Structured Query Language, SQL),Oracle 中的 SQL 不区分大小写只有在文本数字值得时候才会检查
注 : 当创建表或列名的时候使用双引号括起来就可以创建大小写敏感的列名或表名,但是不推荐这样做 SQL*Plus 是 Oracle 的一种操作数据库数据和对数据库执行即时查询的工具,甚至还可以作为代码生成器来使用
逻辑判断 : = > >= < <= != ^= <>
注 : 在进行数据类型比较时,一定要考虑编码情况,VARCHAR2 和 CHAR 是字符比较,如果列数据类型为 NUMBER 则 12 > 9,如果是字符比较则 9 > 12 因为字符 9 比字符 1要大 LIKE 执行模式匹配 : _表示一个字符 %表示任意多个字符
NULL 表示该列是否存在数据,如果没有数据,就说明该列是 null
NOT BETWEEN AND : 不在指定范围之间
优先级 : () 优先级最高,乘除次之,加减法最后,AND 的优先级高于 OR
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, CONSTRAINT trouble_uq UNIQUE(city,sample_date) 创建 UNIQUE 约束时,将创建唯一地索引来强制值得唯一性。在主键约束中,主键的列中至少有一列必须是 NOT NULL
2> 主键(primary key) : 具有特殊性质的候选键之一,可以仅有一个主键,并且主键列不能包含 NULL 值
SQL> CREATE TABLE trouble( CONSTRAINT trouble_pk PRIMARY KEY(city,sample_date) SQL> CREATE TABLE author( author_name VARCHAR2(50) PRIMARY KEY , 3> 指定索引表空间 : UNIQUE约束和 PRIMARY KEY 约束创建索引,索引默认存储在表空间中
SQL> CREATE TABLE author2( author_name VARCHAR2(50), CONSTRAINT author2_pk PRIMARY KEY(author_name) USING INDEX TABLESPACE USERS 注 : 在大多数默认安装中,会创建 USERS 表空间,他是默认表空间 <1> 外键(foreign key) : 也叫完整性约束,指明外键值对应另一个表中主键的实际值
SQL> CREATE TABLE bookshelf ( title VARCHAR2(100) PRIMARY KEY , category_Name VARCHAR2(20), 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 即可
使用 TRUNCATE 可以高效的删除表中的所有数据并回收空间,而不从数据库中删除删除表的定义
从 Oracle10g 开始,删除表仍保留表空间,可以在 "回收站" 中来访问,如果要从回收站中清除,使用 PURGE 子句
SQL> DROP TABLE trouble PURGE; 如果表已经删除,可以从 "回收站" 中清除所占的空间
SQL> PURGE TABLE trouble; SQL> TRUNCATE TABLE trouble; 3> 可随时增加或减少 NUMBER 列中的小数位数,对于减少的情况需要表为空
如果表中每一行的某一列都为 NULL,可作如下更改
注 : 添加或更改列,更改后的列的每一行数据必须满足更改后列的数据限定,否则将会更改失败 语法 : alter table 表名 add (字段 字段类型) [ default '输入默认值'] [null/not null]
SQL> ALTER TABLE trouble ADD( condition VARCHAR2(9) NOT NULL DEFAULT 'no', 在有数据的表中添加非空列会出现 table must be empty to add mandatory (NOT NULL) column 错误,因为新添加的列为空,可以先不添加 NO NULL 增加完列后修改这列使之有数据然后再修改列增加 NO NULL
SQL> ALTER TABLE trouble ADD( SQL> UPDATE trouble SET condition='no'; SQL> ALTER TABLE trouble MODIFY(condition VARCHAR2(9) NOT NULL); Name Type Nullable Default Comments ------------- ------------ -------- ------- -------- SQL> ALTER TABLE trouble MODIFY(city VARCHAR2(17)); Name Type Nullable Default Comments ------------- ------------ -------- ------- -------- 设置表只读这样就可以限制表的 INSERT、UPDATE 和 DELETE 操作
SQL> INSERT INTO trouble(city,sample_date) VALUES('武汉',SYSDATE); SQL> ALTER TABLE trouble READ ONLY; 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; SQL> INSERT INTO trouble(city,sample_date) VALUES('成都',SYSDATE); 删除列比添加或修改列更复杂,因为 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 SQL> ALTER TABLE trouble DROP COLUMN wind; # 删除多列 ALTER TABLE trouble DROP ( wind,condition ); 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 标记列为 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 SQL> ALTER TABLE trouble SET UNUSED COLUMN condition; 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 SQL> ALTER TABLE trouble DROP UNUSED COLUMNS; SQL> CREATE TABLE rain_table AS SELECT city,precipitation FROM trouble WHERE city IS NOT NULL; SQL> SELECT * FROM rain_table; ----------------- ------------- 1> 强制唯一性 : 在创建 PRIMARY KEY 或 UNIQUE 结束时,Oracle 创建索引来强调索引列的唯一性
2> 改善性能 : 当某个查询使用索引时查询性能将显著改善
根据表的主键列值对数据进行排序,索引组织表存储数据时就像整个表存储在一个索引中。普通索引只存储索引列,而索引组织表则在索引中存储表中的所有列
# 经常通过 city,sample_date 创建索引那么在这两个字段上创建索引非常合适 SQL> CREATE TABLE trouble_iot( 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; 注 : 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; SQL> select FEATURE, INITCAP(FEATURE) from newspaper where PAGE=8; --------------- ---------------- FEATURE point Feature Point SQL> SELECT FEATURE,UPPER(FEATURE),LOWER(FEATURE) FROM newspaper; 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------- FEATURE point FEATURE po FEATURE po 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) --------------- ------------------- ------------------- -------------------- Bridge ridge Bridge ridge Member Member Membe Member FEATURE point FEATURE point FEATURE point FEATURE point SUBSTR : 截取字符串,第二个参数指定开始地方 (基于1)如果是负数则从末尾开始计算,如果没有第三个参数那么将截取剩余参数,如果由第三个参数则表示指定截取字符串的长度
SQL> SELECT FEATURE,SUBSTR(FEATURE, 2),SUBSTR(FEATURE, 2, 4) FROM newspaper; FEATURE SUBSTR(FEATURE,2) SUBSTR(FEATURE,2,4) --------------- -------------------------------------------------------- ------------------- FEATURE point EATURE point EATU 注 : 对于 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 ------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- 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) -------------- --------------- SQL> SELECT name,below,ABS(below) FROM math; 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) ----------- ---------- ---------- SQL> SELECT MOD(7.5, 3),REMAINDER(7.5, 3) FROM math; MOD(7.5,3) REMAINDER(7.5,3) ---------- ---------------- SQL> SELECT MOD(4.5, 3),REMAINDER(4.5, 3) FROM math; MOD(4.5,3) REMAINDER(4.5,3) ---------- ---------------- SQL> SELECT POWER(3,2),POWER(4,3) FROM math; SQL> SELECT SQRT(64),SQRT(49) FROM math; 聚集函数和数组函数是用于统计的函数,对于 NULL 值采用忽略的方式计算
COUNT : 数据条数,可作用于字符列,另外该函数在计算时不管指定的是否为 NULL 都会计算
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'; SQL> SELECT COUNT(DISTINCT city),COUNT(city),COUNT(*) FROM comfort; # 使用 DISTINCT 来获取不同值 COUNT(DISTINCTCITY) COUNT(CITY) COUNT(*) ------------------- ----------- ---------- 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()是连续排序,有两个第二名时仍然跟着第三名
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); # 列出Col2分组后根据Col1排序,并生成数字列 SQL> SELECT COL1,COL2,RANK() OVER(PARTITION BY col2 ORDER BY col1) AS rank FROM xgj; ---------- ---------- ---------- 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> SELECT * FROM (SELECT RANK() OVER(PARTITION BY subject ORDER BY score DESC) AS rank,SCORE,SUBJECT FROM xgj_2) WHERE rank<=3; ---------- ---------- -------------------- # 计算出数值 (65,'语文') 在Orade By score,subject排序下的排序值,也就是score=65,subject='语文' 在排序以后的位置 SQL> SELECT RANK(65,'语文') WITHIN GROUP(ORDER BY score DESC,subject) AS rank FROM xgj_2; 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; - -------------------- -------------------- SQL> select m.a,m.b,m.c,rank() over(partition by a order by b) RK from xgj_3 m - -------------------- -------------------- ---------- SQL> select m.a,m.b,m.c,dense_rank() over(partition by a order by b) RK from xgj_3 m; - -------------------- -------------------- ---------- 列表函数作用于列,用于对各列进行比较,从而选出最大值和最小值
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 COALESCE : 计算多个值的非空值,返回第一个非空值,若全部都是 NULL 将返回 NULL
SQL> SELECT noon,midnight,COALESCE(noon, midnight) FROM comfort WHERE city='SAN FRANCISCO'; NOON MIDNIGHT COALESCE(NOON,MIDNIGHT) ----- -------- ----------------------- SQL> SELECT city,sample_date,noon FROM comfort WHERE noon=(SELECT MAX(noon) FROM comfort) OR noon=(SELECT MIN(noon) FROM comfort); ------------- ----------- ----- 如果 BETWEEN、IN 运算前面的列是日期列,则 BETWEEN、IN 将进行日期运算
SQL> SELECT holiday,celebrated_date FROM holiday WHERE celebrated_date BETWEEN '01-JAN-12' AND '22-FEB-12'; ------------------------- --------------- Martin Luther King, Jr. 2012-1-16 Lincoln's Birthday 2012-2-20 Washington's Birthday 2012-2-20 SQL> SELECT holiday,celebrated_date FROM holiday WHERE celebrated_date IN ('02-JAN-12','20-FEB-12'); ------------------------- --------------- Lincoln's Birthday 2012-2-20 Washington's Birthday 2012-2-20 President's Day 2012-2-20 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 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_ ------------------------------ SQL> SELECT TO_DATE('01/19/18','MM/DD/YY') FROM dual; TO_DATE('01/19/18','MM/DD/YY') ------------------------------ 注 : 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; ----------- ---------------- 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)||' ------------------------------ EXTRACT : 该函数用于替代 TO_CHAR 来选择日期值某一部分,语法如下 :
{ 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; 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') ----------- ----------------------------- SQL> SELECT cycle_date,LAST_DAY(cycle_date) AS "End Month" FROM payday; SQL> SELECT SYSDATE AS today,LAST_DAY(ADD_MONTHS(SYSDATE,6)) + 1 review FROM dual; 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 --------------- --------------- ----------- ---------- Robert James 1967-8-23 50 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 ------------------------------ TIMESTAMP : DATE 存储精确到秒的日期和时间,TIMESTAMP存储精确到十亿分之一秒的日期
SQL> SELECT category_name,COUNT(*) FROM bookshelf GROUP BY category_name; -------------------- ---------- HAVING : 功能与 where 类似,只是逻辑仅和分组函数的结果有关,而不语但行列或表达式有关
SQL> SELECT category_name,COUNT(*),AVG(rating) FROM bookshelf GROUP BY category_name HAVING COUNT(*)>5; CATEGORY_NAME COUNT(*) AVG(RATING) -------------------- ---------- ----------- SQL> SELECT category_name,COUNT(*) FROM bookshelf GROUP BY category_name ORDER BY category_name ASC; -------------------- ---------- SQL> SELECT category_name,COUNT(*) FROM bookshelf GROUP BY category_name ORDER BY COUNT(*) DESC; -------------------- ---------- 注 : 可以使用列的别名作为 ORDER BY 子句的一部分,但不能作为 HAVING 子句的一部分 2> 根据 GROUP BY 子句将这些行进行分组
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) -------------------- ---------- ----------- 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) -------------------- ---------- ----------- Name Type Nullable Default Comments --------------- ------------ -------- ------- -------- CATEGORY_NAME VARCHAR2(12) PARENT_CATEGORY VARCHAR2(8) Y SUB_CATEGORY VARCHAR2(20) Y Name Type Nullable Default Comments ------------- ------------- -------- ------- -------- CATEGORY_NAME VARCHAR2(20) Y SQL> DESCRIBE bookshelf_checkout; Name Type Nullable Default Comments ------------- ------------- -------- ------- -------- SQL> DESCRIBE bookshelf_author; Name Type Nullable Default Comments ----------- ------------- -------- ------- -------- AUTHOR_NAME VARCHAR2(50) Y 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); -------------------------------------------------- -------------------------------------------------------------------------------- soren kierkegaard kierkegaard anthology dietrich bonhoeffer letters and papers from prison soren kierkegaard either/or david mccullough john adams 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 在 Oracle 9i 之前的版本外部链接的语法使用 (+),他将返回额外的行
Name Type Nullable Default Comments ------------- ------------- -------- ------- -------- CATEGORY_NAME VARCHAR2(20) Y SQL> DESCRIBE bookshelf_checkout; Name Type Nullable Default Comments ------------- ------------- -------- ------- -------- SQL> SELECT title FROM bookshelf WHERE title NOT IN(SELECT title FROM bookshelf_checkout) ORDER BY title; -------------------------------------------------------------------------------- Complete Poems Of John Keats Journals Of Lewis And Clark Letters And Papers From Prison Preaching To Head And Heart Under The Eye Of The Clock 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; -------------------------------------------------------------------------------- Complete Poems Of John Keats Journals Of Lewis And Clark Letters And Papers From Prison Preaching To Head And Heart Under The Eye Of The Clock 还可以使用 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; -------------------------------------------------------------------------------- Complete Poems Of John Keats Journals Of Lewis And Clark Letters And Papers From Prison Preaching To Head And Heart Under The Eye Of The Clock 在合并两个表时,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; -------------------------------------------------------------------------------- Complete Poems Of John Keats Harry Potter And The Goblet Of Fire Journals Of Lewis And Clark Letters And Papers From Prison SQL> SELECT title FROM bookshelf WHERE title<'M' UNION ALL SELECT title FROM book_order; -------------------------------------------------------------------------------- Complete Poems Of John Keats Harry Potter And The Goblet Of Fire Journals Of Lewis And Clark Letters And Papers From Prison SQL> SELECT title FROM bookshelf INTERSECT SELECT title FROM book_order ORDER BY title; -------------------------------------------------------------------------------- SQL> SELECT title FROM book_order MINUS SELECT title FROM bookshelf ORDER BY title; -------------------------------------------------------------------------------- SQL> SELECT title FROM bookshelf MINUS SELECT title FROM bookshelf_checkout; -------------------------------------------------------------------------------- Complete Poems Of John Keats Journals Of Lewis And Clark Letters And Papers From Prison Preaching To Head And Heart Under The Eye Of The Clock 可以在会话中创建独立存在的表,或者创建数据在事务处理期间可以永久存在的表。可以使用临时表来支持专门的统计或支持特定的处理要求,即使有 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); SQL> INSERT INTO comfort VALUES('Walpole','27-apr-11',63.7,33.8,0); SQL> INSERT INTO comfort VALUES('Aalpole','07-apr-11',72.7,33.8,0); 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 SQL> ROLLBACK TO SAVEPOINT b; # 会管道保存点 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 SQL> ROLLBACK; # 回滚所有未提交的数据 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 SQL> 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)); 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-12-22 Precip 3.9 指定 FIRST 会在找到第一个判断为真的 WHEN 子句后将忽略后面的 WHEN 子句
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) 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 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) 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; 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 命令 对单个表执行 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> 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) 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) DELETE WHERE(precipitation IS NULL) INSERT(c1.city,c1.sample_date,c1.noon,c1.midnight,c1.precipitation) VALUES(c2.city,c2.sample_date,c2.noon,c2.midnight,c2.precipitation); 使用 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 Oracle 中标准索引类型称为 B树索引,是将列值与其相关的 RowID 相
创建索引 : 可通过 CREATE INDEX 指令创建索引,如果设置主键或唯一列将会自动创建唯一索引
常用格式 : CREATE [BITMAP | UNIQUE] INDEX index_name ON TABLE(column1...) [REVERSE];
索引名称必须唯一,位图索引可以在只有很少几个不同值的列上创建有用的索引。REVERSE 关键字指定反转索引值得字节,这在插入许多有序的数据值时可以改善数据库的 I/O 分布
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 值,重建索引必须要有足够的空间同时容纳新旧索引,创建完成之后将删除旧索引
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; SQL> INSERT INTO customer_demo(name,contact,id) VALUES('Cole Construction','Veronica', customer_id.NEXTVAL ); SQL> INSERT INTO customer_demo(name,contact,id) VALUES('Cole Construction','Veronica', customer_id.CURRVAL ); 转载地址:http://eagab.baihongyu.com/