ORACLE相关语法及命令
一、Oracle入门
理论知识：
Oracle的物理组件有三个：
(1)数据文件 数据文件是用于存储数据库数据的文件，如表、索引数据。每个Oracle数据库有一个或多个物理数据文件， 一个数据文件只能与一个数据库关联。
(2)日志文件 用于记录对数据库进行的修改信息，日志文件主要用于在数据库出现故障时实施数据库恢复。
(3)控制文件 控制文件是记录数据库物理结构的二进制文件，每个Oracle数据库都含有一个控制文件。
Oracle的逻辑组件：
表空间(TableSpace) 表空间是数据库最大的逻辑单位，一个数据库至少包含一个表空间，一个表空间包含一个或多个段等等。
段(Segment) 段存在于表空间中，分成4类，数据段、索引段、回退段、临时段。
区(Extent) 区是磁盘空间分配最小单位，由连续的数据块组成，一个或多个区构成段，区只能存在于一个数据文件中。
数据块(Data Block) 数据块是数据库中最小的数据组织单位与管理单位，Oracle数据库中的数据存储于数据块中，取值范围2K-64K之间。
模式(schema) 模式是对用户所创建的数据库对象的总称，又称为用户模式。
概念：
内存 Oracle内存结构包含以下两个内存区。
1、系统全局区(SGA) 实例启动时分配该内存区，是Oracle实例的一个基本组件。
又称为共享全局区，它用来存储数据库信息，并由多个数据库进程共享。可分为共享池、数据缓冲区及日志缓冲区。
(1)共享池 是对SQL、PL\SQL程序进行语法分析、编译、执行的内存区域。共享池由库缓存和数
据字典缓存组成。其中，库缓存含有最近执行的SQL、PL\SQL语句的分析码和执行计划；数据字典缓存含有从数据字典中得到的表、索引、列定义和权限等信息。
(2)数据缓冲区 数据缓冲区用于存储从磁盘数据文件中读入的数据，所有用户共享。
(3)日志缓冲区 日志记录数据库的所有修改信息，主要用于恢复数据。
2、程序全局区(PGA) 服务器进程启动时分配该内存区。PGA为非共享区，只能单个进程使用，当一个用户会话结束后，PGA释放。
用户进程(PGA)发送SQL语句到共享全局区(SGA)，先在共享池的库缓存中查询是否存在所需的数据块，如果存在就在数据字典中读取相应的数据块，如果不存在就由服务器进程(DBWR)来IO数据库
语法知识：
创建表空间的语法如下：
CREATE TABLESPACE tablespacename DATAFILE 'd:\filename.DBF' [SIZE int [KB|MB]] [AUTOEXTEND [OFF|ON]];
tablespacename 是需创建的表空间名称。
DATAFILE 指定组成
表空间的一个或多个数据文件，当有多个数据文件时使用逗号分隔。
filename 是表空间中数据文件的路径和名称。
SIZE 指定文件的大小，用K指定千字节大小，用M指定兆字节大小。
AUTOEXTEND子句用来启用或禁用数据文件的自动扩展。
Oracle默认用户：
用户名：sys 默认密码：chage_on_install 用来管理拥有Oracle数据字典文件
用户名：system 默认密码：manager 用来管理拥有数据字典视图对象
用户名：scott 默认密码：tiger 示例用户，包括emp、dept等表
连接Oracle: 在控制台下输入 sqlplus 用户名/密码 回车或 sqlplusw 回车
相关命令：
disconn //退出当前登录
conn 用户名/密码 //连接Oracle
alter user 用户名 identified by 密码 //修改用户口令
drop user 用户名 cascade; //删除用户
alter user 用户名 account lock; //给某个用户加锁
alter user 用户名 account unlock; //给某个用户解锁
ed 回车: //打开缓冲区
/ 回车: //执行缓冲区中的语句
create user 用户名 identified by 密码 [password expire]
[default tablespace 表空间名] [temporary tablespace 临时表空间名]; //创建用户
相关权限：
grant connect to scott; //connect角将允许用户创建数据库并在数据库中创建表或其他对象
grant resource to scott; //resource角将允许用户使用数据库中的空间
grant create sequence to scott; //create sequence权限将允许用户创建序列，此权限包含在connect连接角中
grant select on emp to scott; //将emp表的查询权限授予用户scott
grant update(vencode,venname) on 表名 to scott; //将特定列的更新权限授予用户scott
grant 权限 on 表名 to 用户名 with grant option; //接受该权限的用户可以将此权限授予其他用户
revoke select,update on 表名 from 用户名; //收回相应的权限
二、SQL查询和SQL函数
SQL支持如下类别的命令：
数据定义语言： create(创建)、alter(更改)、drop(删除)和truncate(截断)命令。
数据操纵语言： insert(插入)、select(选择)、delete(删除)和update(更新)命令。
事务控制语言： commit(提交)、savepoint(保存点)和rollback(回滚)命令。
数据控制语言： grant(授予)和revoke(回收)命令。
数据类型：
char: 长度在1到2000个字节，声明多少字节在内存中就占用多少字节，输入的值小于指定的长度时用空格填充。
varchar2: 长度在1到4000个字节，输入的值是多少字节，就占用多少字节。
long: 长度在2GB，设置为此类型的列时，要注意：一个表中只有一列可以为long
类型，long类型列不能定义为唯一约束或主键约束，不能建立索引，过程或存储过程不能接受long类型的参数。
number(p,s): 其中p为精度，表示数字的总位数，在1至38之间。s为范围，表示小数点右边数字的位数，在-84至127之间。
date: 日期类型，sysdate为当前系统时间。格式为08-9月 -07。
timestamp: 用于存储日期的年、月、日以及时间的时、分和秒。其中秒精确到小数点后6位，
systimestamp返回当前日期、时间。格式为08-9月 -07 04.08.30.000000 下午。
raw: 此数据类型用于存储基于字节的数据，如二进制数据或字节串，该类型最多能存储2000个字节，可以建立索引。
long raw: 此数据类型用于可变长度的二进制数据，最多能存储2GB。long数据类型的所有限制对long raw数据类型也同样有效。
lob又称为"大对象"数据类型，最多能存储4GB的非结构化信息。包括：
clob: clob代表Character LOB(字符LOB)，它能存储大量字符数据。如XML文档。
blob: blob代表Binary LOB(二进制LOB)，它能存储较大的二进制对象，如图形、视频剪辑和声音剪辑。
bfile: bfile代表Binary File(二进制文件)，它能够将二进制文件存储在数据库外部的操作系统文件中。
伪列：
rowid: select rowid,ename, p where empno='7900';
rownum: select * p where rownum<11; //限制查询返回的行数
语法知识：
desc 表名; //查看表结构
alter table 表名 modify (列名 varchar2(25)); //修改列
alter table 表名 add (列名 varchar2(12)，列名 number(12)); //添加列
alter table 表名 drop column 列名; //删除列
oracle建立数据库连接 truncate table 表名; //中删除记录而不删除结构，不使用事务处理，因此无法回滚
drop table 表名; //删除表及其全部数据
create table 新表名 as select * from 表名 where 1=2; //用现有的表创建一个新表
select deptno*2 "New No",dname,loc from dept; //指定一个含有特殊字符(如空格)的列标题
commit; //提交事务
savepoint 标记名; //标记事务点
rollback; //回滚整个事务处理
rollback to [savepoint] 标记名;//回滚到事务中某个特定的保存点
集合操作符：
union(联合): 此操作符返回两个查询选定的所有不重复的行。
语法 select orderno from order_master UNION select orderno from order_detail;
union all(联合所有): 此操作符合并两个查询选定的所有行，包括重复的行。
语法：select orderno,ename from order_master UNION ALL select orderno,proname from order_detail order by 2;
注意：在两个select语句中指定的列名不必相同，但数
据类型必须匹配。也可以对联合查询的结果进行排序，使用Order By子句时，它必须放在最后
一个select语句之后，而且必须指定列索引来排序，而不是指定列名，列索引是从1开始的整数。上述语法便是以proname的索引排序
intersect(交集): 此操作符只返回两个查询都有的行。
语法：select orderno from order_master INTERSECT select orderno from order_detail;
minus(减集): 此操作符中返回由第一个查询选定但是第二个查询中没有选定的行，也就是在第一个查询结果中排除第二个查询结果中出现的行。
语法：select orderno from order_master MINUS select orderno from order_detail；查询尚未交付的订单
连接( || )操作符：
语法： select ('供应商'||venname||'的地址是'||venadd1||' '||venadd2||' '||venadd3) 地址 from vendor_master where vencode='V002'; 将多个字符串合并为一个字符串。
SQL函数：
1、日期函数：
add_months: 此函数返回给指定的日期加上指定的月数后的日期值。语法为add_months(d,n)，其中d是日期，n表示月数。
示例：select add_months(sysdate,2) from dual; 将当前时间加上2个月后的日期值。
months_between: 此函数返回两个日期之间的月数。语法为months_between(d1,d2)，其中d1和d2是日期，如果d1大于d2，则结果为正数；否则为负数。
last_day: 此函数返回指定日期当月的最后一天的日期值，语法为last_day(d)，其中d表示日期。
示例：select last_day(sysdate) from dual; 返回当前日期的月的最后一天，如果是9月就返回30-09月-07
round: 此函数返回日期值，将日期四舍五入为格式模型指定的单位。语法为round(d,[fmt])。其中d是日期，fmt是格式模型。fmt是一个可选项，日期默认舍入为最靠近的那一天。如果指定格式为年"Year"，则舍入到年的开始，即1月1日；如果格式为月"Month"，则舍入到月的第一日；如果格式为周"Day"，则舍入到最靠近的星期日。
示例：select round(sysdate,'month') from dual; 返回最接近的一个月。
next_day: 此函数返回指定的下一个星期几的日期。语法为next_day(d,day)。其中d表示日期，而day指周内任何一天。
示例：select next_day(sysdate,'星期日') from dual; 返回下一个星期日的日期，也可以用1表示，以此类推，星期一以2表示。
trunc: 此函数将指定日期截断为由格式模型指定的单位日期，与Round函数不同的是它只舍不入，语法为trunc(d,[fmt])，与round格式相同。
示例：select trunc(sysdate,'year') from dual; 返回当前年的第一天，也就是1月1日。
示例：select trunc(sysdate,'day') from dual; 返回紧靠前面的星期日。如果为"2005年1月27日"就会返回"2005年1月23日"。
extrac
t: 此函数提取日期时间类型中的特定部分。语法为extract(fmt from d)，其中d是日期时间表达式，fmt是要提取的部分的格式。格式的取值可以是year,month,day,hour,minute,second，注意此处的格式不使用单引号。
示例：select extract(year from sysdate) from dual; 返回当前的年份。
2、字符函数：
initcap(char): 首字母大写，示例：select initcap('hello') from dual; 输出结果:Hello。
lower(char): 转换为小写，示例：select lower('FUN') from dual; 输出结果:fun。
upper(char): 转换为大写，示例：select upper('sun') from dual; 输出结果:SUN。
ltrim(char,set): 左剪裁，示例：select ltrim('xyzadams','xyz') from dual; 输出结果:adams。
rtrim(char,set): 右剪裁，示例：select rtrim('xyzadams','ams') from dual; 输出结果:xyzad。
translate(char,from,to): 按字符翻译，示例：select translate('jack','abcd','1234') from dual; 输出结果:j13k。
replace(char,search_str,replace_str): 字符串替换，示例：select replace('jack and jue','j','bl') from dual; 输出结果:black and blue。
instr(char,substr[,pos1,pos2]): 查子字串位置。
示例：select instr('vorldwide','d') from dual; 输出结果:5。pos1为可选，表示从第几个位置查。pos2为
可选，表示从第几次出现的位置。
substr(char,pos,len): 取子字符串，示例：select substr('abcdefg',3,2) from dual; 输出结果:cd。
concat(char1,char2): 连接字符串，示例：select concat('Hello','world') from dual; 输出结果:Helloworld。
chr: 此函数根据Ascii码返回对应的字符，示例：select chr(45788),chr(53671),chr(50167),chr(65) from dual; 输出结果：曹 学 明 A。
ascii: 此函数返回GBK编码值，示例：select ascii('曹') cao ,ascii('学') xue,ascii('明') Ming from dual; 输出结果:45788 53671 50167。
lpad和rpad： 示例：select lpad('function',15,'=') from dual; 输出结果:=======function。而rpad则相反，字符串填充在右边。
trim: 此函数从字符串的开头或结尾(或开头和结尾)剪裁特定的字符，默认剪裁空格。如果加上leading选项时与ltrim函数相似。指定trailing时和 rtrim函数相似。
示例： select trim(9 from 999992598899) from dual; 输出结果:25988。
示例： select trim(leading 9 from 999992598899) from dual; 输出结果:2598899。
示例： select trim(trailing 9 from 999992598899) from dual; 输出结果:9999925988。
length: 此函数返回字符串的长度，示例：select length('frances') from dual; 输出结果:7。
decode: 示例：select deptno,dname,decode(loc,'NEW YORK','纽约','BOSTON','波士顿') from scott.dept;
此示例将替换显示loc列的结果，结果为"NEW YORK"的替换为"纽约"，"BOSTON"的替换为"波士顿"。
GREATEST/least: 返回一组表达式中的
最大值/最小值，即比较字符的编码大小.
示例：select greatest('AA','AB','AC') from dual; 输出结果:AC。
select least('AA','AB','AC') from dual; 输出结果:AA。
select greatest('啊','安','天') from dual; 输出结果：天。
select least('啊','安','天') from dual; 输出结果：啊。
3、数字函数：
abs(n): 取绝对值，示例：select abs(-15) from dual; 输出结果:15。
ceil(n): 向上取整，示例：select ceil(44.778) from dual; 输出结果:45。
sign(n): 取符号，示例：select sign(-2) from dual; 输出结果:-1。
floor(n): 向下取整，示例：select floor(200.88) from dual; 输出结果:200。
power(m,n): m的n次幂，示例：select power(5,3) from dual; 输出结果:125。
mod(m,n): 取余数，示例：select mod(10,3) from dual; 输出结果:1。
round(m,n): 四舍五入，示例：select round(100.256,2) from dual; 输出结果:100.26。
trunc(m,n): 截断，示例：select trunc(100.256,2) from dual; 输出结果:100.25。
sqrt(n): 平方根，示例：select sqrt(4) from dual; 输出结果:2。
4、转换函数：
to_char(d|n[,fmt]): 其中d是日期，n是数字，fmt指定日期或数字的格式。
示例：select to_char(sysdate,'yyyy"年"fmmm"月"fmdd"日" hh24:mi:ss') from dual; 输出结果:2007年9月09日 20:44:27。
select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual;
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
select to_char(sysdate,'yyyy"年"mm"月"dd"日" hh24"时"mi"分"ss"秒"') from dual;
示例：select to_char(sal,'$99999') from emp; 输出结果:$1600。
to_date(char[,fmt]): 此函数将char或varchar2数据类型转换为日期数据类型。
示例：select to_date('2005-12-06','yyyy-mm-dd') from dual; 输出结果:06-12月-05。
select to_date('2008/08/10 20:08:08','yyyy/mm/dd hh24:mi:ss') from dual;
select to_date('2008-08-10 20:08:08','yyyy/mm/dd hh24:mi:ss') from dual;
select to_date('2008年08月10日 20时08分08秒','yyyy"年"mm"月"dd"日" hh24"时"mi"分"ss"秒"') from dual;
to_number(char): 此函数将包含数字的字符串转换为number数据类型，通常不用这么做，因为Oracle可以对数字字符串进行隐式转换。
示例：select sqrt(to_number('100')) from dual; 输出结果:10。
5、其它函数：
nvl(expression1,expression2): 如果expression1为NULL，则nvl返回expression2。
nvl2(expression1,expression2,expression3): 如果expression1不是NULL，则nvl2返回expression2，如果expression1是NULL，则返回expression3。
nullif(expr1,expr2): 此函数比较两个表达式，如果它们相等，则返回空值，否则返回expr1。
nullif函数等价于以下的case表达式：
case when expr1=expr2 then null else expr1 end
6、分组
发表评论