本文共 47250 字,大约阅读时间需要 157 分钟。
感谢被转载作者 zhangxu3739
SQL | iSQL*Plus |
是一个与 Oracle Server 通讯储存资料的语言 | 可识别 SQL 叙述并传送到 Server |
美国国际标准协会 (ANSI) 标准 SQL | 是 Oracle 专有执行 SQL 叙述的接口 |
在数据库操作定义窗体 | 在数据库不允许操作数值 |
不可以将字符串断行 | 可以利用 dash(_) 来作字符串断行 |
不能缩短 | 可缩短 |
使用函数来执行一些资料格式化 | 使用指令来格式化资料 |
2. Restricting and Sorting Data [限制及排序资料] |
操作数 | 表示意义 |
= | 相等 |
> | 大于 |
>= | 大于或等于 |
< | 小于 |
<= | 小于或等于 |
<> | != 与 ^= 皆表示不相等 |
BETWEEN...AND... | 包含两个数值之间的值 ( 相异 ) |
IN(set) | 符合清单数值中的任一笔数值 |
LIKE | 符合部分字符 |
IS NULL | 是一个 NULL 值 |
操作数 | 表示意义 |
AND | 两者同时成立传回 TRUE, 否则传回 FALSE |
OR | 两者其中成立传回 TRUE |
NOT | 如果条件是FALSE则传回TRUE |
评定次序 | 操作数 |
1 | 数学操作数 * / + - |
2 | 连接操作数 || |
3 | 比较条件 > , < , = , >= , <= …etc |
4 | IS [NOT] NULL, LIKE, [NOT] IN |
5 | [NOT] BETWEEN |
6 | NOT 逻辑条件式 |
7 | AND 逻辑条件式 |
8 | OR 逻辑条件式 |
3. Single-Row Functions [单列函数] |
函数 | 动作 |
LOWER(column | expression) | 小写转换 |
UPPER(column | expression) | 大写转换 |
INITCAP(column | expression) | 前缀转换,即使全大写或全小写字符串,一律转换成前缀大写其余小写. |
CONCAT(column1 | expression1 , column2 | expression2) | 连结字符串,将两个字段或字符串连结;相当于 (||) 操作数 |
SUBSTR(column | expression,m[,n]) | 取出子字符串,将字段或字符串中取出子字符串,m表示起始字数,n表示取出字数 |
函数 | 动作 |
LENGTH(column | expression) | 传回一个NUMBER的字符串字符数. |
INSTR(column | expression, ’string’ , [,m] , [n] ) | 传回string在column | expression中的位置的数字. 1.当传入只有一个数值,也就是指m字数开始搜寻 2.当传入两个数值时,指从m开始,第n个出现的位置 3.当传入一个负数值,指从右方搜寻,正数值则从左方搜寻 |
LPAD(column | expression, n, 'string') RPAD(column | expression, n, 'string') | 1. LPAD与RPAD之间差异,在于补足字的左方还是右方. 2. n,代表字符串字符总数, 3. string代表当字段或字符串字符总数不足n时,以string补足n值 4. 当n>column/expression字数时,同substr只取该colmn前n个字符 |
TRIM(leading | trailing | both , trim_character FROM trim_source) | 1.如果没有其它参数,传回前后空白去除之trim_source. 2.当传带入参数时, SQL > SELECT TRIM( ‘l’ FROM ‘lllHello World!!llllll’) FROM DUAL; SQL > ----------------- Hello World!! |
REPLACE( text, search_string, replacement_string) | 搜寻text字符串中search_string,替换成replacement_string SQL > select replace('this is a book','book','dog') from dual; ---------------------------------- this is a dog |
函数 | 结果 |
CONCAT('Hello', 'World') SUBSTR('HelloWorld',1,5) LENGTH('HelloWorld') INSTR('HelloWorld', 'W') LPAD(salary,10,'*') RPAD(salary, 10, '*') TRIM('H' FROM 'HelloWorld') | HelloWorld Hello 10 6 *****24000 24000***** elloWorld |
函数 | 动作 |
ROUND(column | expression, n) | 传回小数第n位,四舍五入的值 |
TRUNC(column | expression,n) | 传回小数第n位,无条件舍的值 |
MOD(m,n) | 传回m除以n的余数 |
操作数 | 结果 | 说明 |
date + number | 日期 | 日期加上number的天数 |
date - number | 日期 | 日期减掉number的天数 |
date - date | 天数 | 两个日期之间相差天数 |
date + number/24 | 日期 | 加入小时到日期 |
函数 | 说明 |
MONTHS_BETWEEN( Date1,Date2 ) | 两个日期间相差的月份,如果第二个参数比第一个参数时间还大(新),则传回负数值 |
ADD_MONTHS( Date,Number ) | 日期月份值加上Number值,当Number为负数,则月份 往前计算. |
NEXT_DAY(Date,Number|Week) | 计算下一个星期的日期,Number 1代表星期日,2代表星期一,3:二……7:六 |
LAST_DAY(Date,Number) | 计算该月份的最后一天 |
ROUND(Date,MONTH|YEAR) | 如果date的日或年已过一半,则传回下月或下年的第一天. |
TRUNC(Date,MONTH|YEAR) | 传回该月第一天或传回该年度第一天 |
转换来源型别 | 转换后型别 |
VARCHAR2 or CHAR | NUMBER |
VARCHAR2 or CHAR | DATE |
NUMBER | VARCHAR2 |
DATE | VARCHAR2 |
函数 | 说明 |
TO_CHAR ( number | date , [ fmt], [nlsparams] ) | 转换一个number或date成为VARCHAR2 fmt为指定的格式,像指定date格式:‘DD-MM-YY’ 指定number的格式:’$99999.99’ |
TO_NUMBER ( char , [ fmt ], [ nlsparams ]) | 转换一个包含digit的字符字符串成为number型别 fmt为指定的格式,像 ‘99999.99’ |
TO_DATE ( char , [ fmt ] , [ nlsparams ] ) | 转换一个字符字符串成为date型别 fmt为指定的格式 |
元素 | 说明 |
SCC or CC | Century; server prefixes B.C. date with - |
Years in dates YYYY or SYYYY | Year; server prefixes B.C. date with - |
YYY or YY or Y | Last three, two, or one digits of year |
Y,YYY | Year with comma in this position |
IYYY, IYY, IY, | I Four, three, two, or one digit year based on the ISO standard |
SYEAR or YEAR | Year spelled out; server prefixes B.C. date with - |
BC or AD | B.C.A.D. indicator |
B.C. or A.D. | B.C./A.D. indicator with periods |
Q | Quarter of year |
MM | Month: two-digit value |
MONTH | Name of month padded with blanks to length of nine characters |
MON | Name of month, three-letter abbreviation |
RM | Roman numeral month |
WW or W | Week of year or month |
DDD or DD or D | Day of year, month, or week |
DAY | Name of day padded with blanks to a length of nine characters |
DY | Name of day; three-letter abbreviation |
J | Julian day; the number of days since 31 December 4713 B.C. |
元素 | 说明 |
AM or PM | Meridian indicator |
A.M. or P.M. | Meridian indicator with periods |
HH or HH12 or HH24 | Hour of day, or hour (1–12), or hour (0–23) |
MI | Minute (0–59) |
SS | Second (0–59) |
SSSSS | Seconds past midnight (0–86399) |
函数 | 说明 |
NVL NVL(expr1, expr2) | 转换一个null值成为一个实际的值 |
NVL2 NVL2(expr1, expr2, expr3) | 如果expr1 不是 null 则return expr2. 如果expr1是 null ,NVL2 return expr3. Expr1的参数可以是任何的资料型别 |
NULLIF NULLIF (expr1, expr2) | 比较expr1与expr2如果相等则传回null,如果两者不相等则传回expr1 |
COALESCE COALESCE (expr1, expr2, ... exprn) | 传回expr1,expr2,expr3….中,第一个非null值 |
4. Displaying Data from Multiple Tables [从多表中显示资料] |
我们要利用JOB_GRADES的各级别的范围值来对EMPLOYEES的SALARY来评比级别, SELECT e.last_name , e.salary , j.grade_level FROM employees e , job_grades j WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;←重要!! |
5.Aggregating Data using Group Functions[使用群组函数来统计数据] |
函数 | 说明 |
AVG ( [ DISTINCT|ALL ] n ) | 传回平均值 |
COUNT ( { * | [ DISTINCT | ALL ] expr } ) | 传回资料笔数 |
MAX ( [ DISTINCT | ALL ] expr ) | 传回最大值 |
MIN ( [ DISTINCT | ALL ] expr ) | 传回最小值 |
STDDEV ( [ DISTINCT | ALL ] x ) | 传回标准差 |
SUM ( [ DISTINCT | ALL ] n ) | 传回总和 |
VARIANCE ( [ DISTINCT | ALL ] x ) | 传回异数估计值 |
6. Subqueries [子查询] |
7.Producing Readable Output with iSQL*Plus[在SQL*Plus产生美观的输出报表] |
元素 | 说明 | 范例 | 结果 | ||
9 | Single zero-suppression Digit | 999999 | 1234 | ||
0 | Enforces leading zero | 099999 | 001234 | ||
$ | Floating dollar sign | $9999 | $1234 | ||
L | Local currency | L9999 | L1234 | ||
. | Position of decimal point | 9999.99 | 1234.00 | ||
, | Thousand separator | 9,999 | 1,234 | ||
8. Manipulating Data [数据处理动作] |
方法 | 说明 |
Implicit 隐含 | column及value中将字段省略 |
Explicit 明确 | 在数值清单中明确使用NULL关键词, 或使用空字符串(‘’)在字符串或日期格式中 |
9. Creating and Managing Tables[建立与维护表格] |
对象 | 说明 |
Table | Table 表格,基本储存单位; 由字段与列组成 |
View | VIEW 视观表,描述由一个或多个表格的子集合 |
Sequence | Sequence 序列,产生连续的数值. |
Index | Index 索引,有效改善相同请求的效能 |
Synonym | Synonym 同义字,给一个对象替代名称. |
前缀 | 说明 |
USER_ | 这个视观表包含使用者相关对象信息. |
ALL_ | These views contain information about all of the tables (object tables and relational tables) accessible to the user. |
DBA_ | These views are restricted views, which can be accessed only by people who have been assigned the DBA role. |
V$ | These views are dynamic performance views, database server performance, memory, and locking. |
资料型别 | 说明 |
VARCHAR2(n) | 可存放变量长度的字符串,长度为n字节;n最大可以到4,000字节 |
NVARCHAR2(n) | 可存放变量长度的Unicode字符串,长度为n字节;n最大可以到4,000字节 |
CHAR(n) | 可存放固定长度的字符串,长度为n字节;n最大可以到2,000字节 |
NCHAR(n) | 可存放固定长度的Unicode字符串,长度为n字节;n最大可以到2,000字节 |
NUMBER(n,[m]) | 可存数值资料,总长度为n位数;n最高可以到38位数;此外,NUMBER资料型别还允许您指定m位小数,如果未指定n,预设是38 |
DATE | 可存放日期信息,长度为7个字节 |
RAW | 类似VARCHAR2的变量长度资料型别,可存放高逹2,000字节的二进制数据 |
LONG | 可存放高逹2GB的文字资料 |
LONG RAW | 可存放高逹2GB的二进制数据 |
ROWID | 可存放表格内每笔资料的实体位置 |
BLOB | 可存放大量二进制数据,最高到4GB |
CLOB | 可存放大量文字资料,最高到4GB |
NCLOB | 可存放大量Unicode文字资料,最高到4GB |
BFILE | 存放档案指针,指向ORACLE以外的操作系统档案 |
10.Including Constraints[加入约束条件] |
约束条件 | 说明 | 存入NULL | 允许重复 |
PRIMARY KEY | 「主键」字段,如果是「复合式主键」其组合也必须唯一 | ╳ | ╳ |
FOREIGN KEY | 「外来键」字段,该字段’的资料必须对应「父表格」的「主键」字段 | ○ | ○ |
UNIQUE | 该约束条件的字段不能存放重复资料 | ○ | ╳ |
CHECK | 该约束条件的字段可以检核字段值是否符合某个规则,或一组事先指定的资料 | ○ | ○ |
NOT NULL | 该约束条件的字段不允许NULL值存放 | ╳ | ○ |
11. Creating Views[建立视观表] |
| |
Table | TABLE 由字段及列组成一个基本的储存单位 |
View | VIEW 由一个或多个表格资料子集合逻辑表示. |
Sequence | Sequence 产生主键值. |
Index | INDEX 索引,对相同的请求改善效能. |
Synonym | Synonym 同义字,给一个对象替代名称. |
Feature | 简单视观表 | 复杂视观表 |
Number of tables | One | One or more |
Contain functions | No | Yes |
Contain groups of data | No | Yes |
DML operations through a view | Yes | Not always |
12. Creating Other Database Objects[建立其它资料对象] |
13. Controlling User Access[使用者权限管理] |
| |
CREATE USER | 授与可以建立其它Oracle使用者的权限(这个权限需要DBA的角色才可以) |
DROP USER | 授与可以删除其它的使用者的权限. |
DROP ANY TABLE | 授与可以删除任何schema下的表格 |
BACKUP ANY TABLE | 授与可以使用export工具备份任何schema下的表格. |
SELECT ANY TABLE | 授与可以在任何schema下读取表格,视界或快照(snapshot). |
CREATE ANY TABLE | 授与可以在任何schema下建立表格的权限 |
| |
CREATE SESSION | 连接到数据库 |
CREATE TABLE | 在使用者的schema下建立表格 |
CREATE SEQUENCE | 在使用者的schema下建立序列(sequence) |
CREATE VIEW | 在使用者的schema下建立视界(view) |
CREATE PROCEDURE | 在使用者的schema下建立储存程序,函数或package |
| | | | |
ALTER | ◎ | ◎ | ||
DELETE | ◎ | ◎ | ||
EXECUTE | ◎ | |||
INDEX | ◎ | |||
INSERT | ◎ | ◎ | ||
REFERENCES | ◎ | ◎ | ||
SELECT | ◎ | ◎ | ◎ | |
UPDATE | ◎ | ◎ |