oracle最基本的操作语法

oracle数据库基本操作

插入数据:

语法：

insert intotable (column1, column2, …)

                            values (column1_value,column2_value, …)

注意格式:

数值型字段可以直接写值

字符型字段，其值要加上单引号

日期型字段，其值要加上单引号，同时还要注意年、月、日的顺序。或者使用to_date ()函数将字符串转化为日期类型

插入列排序和插入值要一一对应，非空列必须有值

注意：

由于insert、update和delete等操作需要影响数据表中的记录，使用commit指令提交所有的操作，使用rollback指令回滚所有的操作

如果向表中插入所有的字段，可以省略列的列表，但是必须包括所有的列的值，而

且和数据表中的定义顺序一致

更新数据：

         语法：

update table set column1=value1, column2=value2, …

                  where condition

范例：

update empset empno=8888, ename=‘Jacky’

                            where empno=7566

删除数据：

         语法：

delete from table where condition

范例：

delete fromemp

where empno=8888;

创建表

语法：

create table table_name (

column1 type1,

column2 type2,

 …… 完整性约束……

)

范例：

create table employees (

           emp_no number not null,

           emp_fname varchar2(20) not null,

           emp_lname varchar2(20) not null,

           dept_no varchar2(4) not null

                  )

修改表结构：

增加列

语法：alter table table_nameadd column_name type

范例：alter tableemployees add phone varchar2(20)

更新列

语法：alter tabletable_name modify column_name type

范例： alter tableemployees modify phone varchar2(30)

 更新列时应注意：

一般情况下，只能把数据的长度从低到高改变，不能从高到低改变。可以把某种数据类型改变为兼容的数据类型

当表中没有数据时，可以把数据的长度从高到低改变，可以把某种数据类型改变为其它数据类型

删除列

语法： alter tabletable_name drop column column_name

范例： alter tableemployees drop column phone

删除表：

语法：

drop tabletable_name

范例：

drop tableprojects

约束

语法：

alter table table_name

         add constraint   constraint_name   constraint_expression

分类：

         主键约束（primary key）：唯一的标识，本身不能为空

                   范例：给person表加主键约束

                            constraint person_pid_pkprimary key(pid);

         唯一约束（unique）：在一张表中只允许建立一个主键约束，而其他列如果不希望出现重复值的话，就可以使用唯一约束

范例：给person表的name加唯一约束

                            constraint person_name_ukunique(name);

         检查约束（check）：检查一个列的内容是否合法

范例：给person表的性别列加上约束

                            constraint person_sex_ckcheck(sex in(‘男’,’女’));

         非空约束（not null）：内容不可以为空

                   范例：name varchar(10) not null,

         外键约束（foreign key）：在两张表中进行操作

范例：给person表和book表加外键约束

                            constraint person_book_fkforeign key(pid) references person(pid);

添加约束

         语法：

                   Alert table 表名称 add constraint 约束名称 约束类型（约束字段）

删除约束

         语法：

                   alert table 表名 drop constraint 约束名称

         级联删除

                   步骤：

1． 先创建一个无任何约束的book表。

2． 再创建另一个带有主键的person表。

3． 给book表加主键,给person表加外键

alert table book

add constraint book_pid_pkprimary key(bid);

alert table book

constraint person_book_fkforeign key(pid) references person(pid)

on delete cascade;

                            4.在删除person表中信息的时候，有关book表中的内容也会被删除

 

结构化查询语句（SQL）

SQL语言组成：

1.数据定义语言DDL（DataDefinition Language）

它是SQL中用来生成、修改、删除数据库基本要素（表、视图、模式、目录等）的部分。（CREATE，DROP，ALTER）

2.数据操纵语言DML（DataManipulation Language）

是SQL运算数据库的部分，它是对数据库中的数据输入、修改及提取的有力工具。（INSERT，UPDATE ，DELETE，SELECT）

3.数据控制语言DCL（Data ControlLanguage）

提供数据库完整性所必需的防护措施。（GRANT，REVOKE）

数据库服务器的关闭、启动等

SQL语言的优点

（1）一体化特点:

使用SQL可实现数据库生命周期中的全部活动

（2）高度非过程化:

它是一种非过程化语言，允许用户依据做什么来说明操作，而不用说明怎样做，存取路径的选择和操作过程由系统自动完成

（3）语言简洁，易学易用

（4）统一的语法结构对待不同的工作方式

无论是联机交互使用，还是嵌入到高级语言中，其语法结构是基本一致的，这就大大改善了最终用户和程序设计人员之间的交流

基本单表查询

         查询所有记录：

                   select* from 表名称;

         查询所有记录的某些字段：

                   selectcolumn1,column2…… from 表名称

         取消重复项：

                   selectdistinct column1,column2…… from 表名称

         显示字段的别名：

                   selectdistinct column1 as 别名1,column2 as 别名2…… from 表名称

         单条件查询：

                   selectdistinct * from 表名称{where 条件(s)}

         比较运算符：

           =（等于）

select *from emp where job=‘MANAGER’

<>、!=、^=（不等于）

select * from emp where sal<>1100

<（小于）

select * fromemp where sal<2000

>（大于）

select * fromemp where sal>2000

<=（小于等于）

select * fromemp where sal<=2000

>=（大于等于）

select * fromemp where sal>=2000

in（列表）

select * fromemp where sal in (1000, 2000, 3000)

not in（不在列表）

select * fromemp where sal not in (1000, 2000, 3000)

between…and…（介于之间）

select * fromemp where sal between 1000 and 2000

not between…and…（不介于之间）

select * fromemp where sal not between 1000 and 2000

is null（是否为空)

select * fromemp where comm is null （注意，区别null和0，空字符串‘’）

is not null（是否不为空)

select * fromemp where comm is not null

组合条件查询：

select empno,ename, sal from emp where job=‘CLERK’ and sal<=2500

组合查询中使用的逻辑比较符：

and（与）

select * from emp where job=‘CLERK’ and  sal<>2000

or（或）

select * from emp where job=‘MANAGER’ or  sal<>2000

not（非）

select * from emp where not job=‘MANAGER’

         模糊查询：

like（模式匹配，检验一个包含字符串数据的字段值是否匹配一指定模式）

not like（模式不匹配）

通配符含义：

_：任何一个单一的字符

%：任意长度的字符

范例：

select * from emp where job like ‘%M%;

                   注：如果在使用like的时候没有指定查询的关键字，则表示全部查询

select * from emp where job like ‘%%’;

排序查询：

         语法：

select distinct * from 表名称 {where 条件(s)} {orderby 排序的字段1 ，排序的字段2 asc|desc(升序|降序)}

         范例：查询emp表并按sal进行降序排列

                   select *from emp order by sal desc;

聚合函数：

count(*) ：返回所有行记录数

select count(*) from emp

count (列名)：返回表达式非null的行记录数

select count(mgr) from emp

min(列名)：返回表达式最小值

select min(sal) from emp

max(列名)：返回表达式最大值

select max(sal) from emp

sum(列名)：返回表达式的总和

select sum(sal) from emp

avg(列名)：返回表达式的平均值

select avg(sal) from emp

         字符函数：

substr(string,start,count):取子字符串,从start开始,取count个

         selectsubstr(’13088888888’,3,8) from dual;

length :返回字符串的长度

         selectname,length(name)  from gao.nchar_tst;

replace(m,n,l)：内容的替换

         select replace(‘hello’,’ll’,’xx’)from dual;

upper()：在一个字符串不知道大小写的情况下用

         select * from empwhere ename=upper(‘smith’);结果返回大写

lower()：将一个字符串变为小写字母表示

         select lower(‘HELLO’)from dual;

initcap()：将单词的首字母大写

         select initcap(‘HELLO’)fromdual;

         数值函数：

           ceil (n)：取大于等于数值n的最小整数

select ceil(756.584) from dual; 结果是:757

floor (n)：取小于等于数值n的最大整数

select floor(756.584) from dual; 结果是: 756

round (m, n)：四舍五入，保留n位

select round(756.584) from dual; 结果是:757

mod (m, n)：取m整除n后的余数

select mod(10,3) from dual;  结果是:1

power (m, n)：取m的n次方

select mod(2,3) from dual;  结果是:8

                   trunc(n):截断小数位

select trunc(756.584) from dual; 结果是:756

         日期函数：

last_day():给定日期的最后一天

                  select last_day(sysdate) from dual;

add_months(): 增加日期

                  select add_months(sysdate,2) from dual;

         months_between():给定日期范围的月数

                   selectempno,ename,months_between(sysdate,hiredate) from emp;

         next_day():下一个给定的日期的今天是哪个日期

                   selectnext_day (sysdate,’星期一’) from dual;

current_date()返回当前会话时区中的当前日期。

         转换函数：

to_char: a.根据格式把日期转化成字符,b.对数字进行格式化，9代表了一位数字      

c.用数字表示区域

其中：a.年：yyyy 月：MM 日：dd

           b.99,999 代表了五位数字

           c.$:表示美元‘$‘  L：表示local的缩写 ‘￥‘

                  select to_char(sysdate,'yyyy-mm-dd') from dual;

           注：在1-9个月份中，显示的时候系统会默认加“0”，若想去掉“0”，则可以使用“fm”。即：’fmyyyy-mm-dd’

to_number:转换成数字

         selectto_number(‘123’)+to_number(‘123’) from dual; 结果是：246

to_date: 根据格式把字符转化成日期

         selectto_date(‘2009-8-6’,’yyyy-mm-dd’) from dual;

         通用函数：

                   decode()：类似于if…elseif…else if…else…

select empno,ename,hirdatedecode(job,’clerk’,’业务员’,’salesman’,’销售人员’,’manager’,’经理’,’analyse’,’分析员’,’persident’,’总裁’) from emp;

         其他常用函数：

ascii:返回与指定的字符对应的十进制数

                  select ascii(’A’) A,ascii(’a’)a,ascii(’0’) zero,ascii(’ ’) space from dual

chr:给出整数,返回对应的字符

                  select chr(54740) zhao,chr(65) chr65from dual;

concat :连接两个字符串

                  select concat(’010-’,’88888888’)||’转23’ fromdual;

rpad和lpad: 在左边/右边重复指定的字符

                  selectlpad(rpad(’gao’,10,’*’),17,’*’)from dual;

trim/ltrim/rtrim: 去除空白字符

 

         分组查询：

                   语法：

select distinct *|查询列1 as 别名1，查询列2 as 别名2……  from 表名称 {where 条件(s)} {groupby 分组条件{Having 分组条件}}{order by 排序的字段1 ，排序的字段2 asc|desc(升序|降序)}

范例：selectjob, count(*) from emp group by job

         注：

常和聚合函数一起使用

聚合函数如果作为条件，必须使用having子句

除了聚合函数以外，如果在查询中显示其他字段，该字段必须出现在group by分组列中

group by中不能使用列的别名，也不能使用序列号

having中的聚合函数不能使用别名

         内连接：

                   语法：

                            Selectcolumn_list from tab_1 [inner] join tab_2 on join_condition

    其中：

column_list表示将要检索的列名列表

table_1和table_2表示将要连接的表的名称

inner join关键字表示内连接，其中inner是可选的

on是指定连接条件的关键字

join_condition表示内连接的条件

         范例：

select e.empno, e.ename, d.dname from emp e

inner join dept d on e.deptno=d.deptno

         左右全连接：

                   语法：

                            Selectcolumn_list form tab_1 [left|right|full] outer join tab_2 on join_condition

         其中：

left outer join表示左连接

right outer join表示右连接

full outer join表示全外连接。其中outer是可选的

其它的参数和内连接相同

范例：

select e.empno, e.ename, d.dname

from emp e left outer join dept d on e.deptno=d.deptno

         多表连接：

           等值连接

selecte.empno, e.ename, d.dname from emp e, dept d

                            where e.deptno=d.deptno

不等值连接

selecte.empno, e.ename, d.dname

                           fromemp e, dept d

                           wheree.deptno<>d.deptno and e.deptno=10

自连接:

使用自连接应注意：

使用两个不同的别名命名表

使用不平衡条件，例如大于或小于条件。如果使用平衡条件，则检索结果会出现两倍的冗余

自连接的结果一定要有实际意义

                            范例:

selectd1.deptno, d1.dname, d2.loc

                                    from dept d1, dept d2

                                    where d1.loc = d2.loc and d1.dname<> d2.dname

子查询：

在select查询语句里可以嵌入select查询语句，成为子查询或嵌套查询。子查询形成的结果又成为父查询的条件

使用子查询应注意：

子查询可以嵌套多层

子查询操作的数据表可以是父查询不操作的数据表

子查询中不能有orderby排序语句

范例：

select empno, ename, job, sal from emp

                  where sal>(select sal from emp whereename=‘WARD’)

 in(not in)操作符：指定一个查询的范围       

                   select* from emp where sal in(select min(sal) from emp group by deptno );

any(some)操作符：=any：与in操作符一样   >any：比里面最大的值大   <any:比里面最小的值小 

select empno, ename, job, sal from emp

                where sal > any (select sal from emp where job=‘MANAGER’)

          all：所有满足条件的

select empno, ename, job, sal from emp

                            where sal > all (selectsal from emp where job=‘MANAGER’)

         exists：满足条件的

select e.empno, e.ename, e.job, e.sal

                            from emp e, dept d  where exists

                            (select * from emp wheredeptno=d.deptno)

并操作（union）的嵌套查询：

并操作是集合中并集的概念，属于集合A或集合B的元素的总和就是并集

select deptnofrom emp

                            union (all)

                   select deptno from dept

union all和union不同之处在于unionall会将每一条符合条件的记录都列出来，无论记录是否重复

交操作（intersect）的嵌套查询:

交操作是集合中交集的概念，属于集合A且属于集合B的元素的总和就是交集

select deptnofrom emp

                            intersect

                   select deptno from dept

差操作（minus）的嵌套查询:

差操作是集合中差集的概念，属于集合A且不属于集合B的元素的总和就是差集

select deptnofrom dept

                            minus

                    select deptno from emp

rownum():表示行号，实际上是一个列，但这个列是伪劣，此列可以在每张表中出现。且rownum采用自动编号的形式出现。

         范例：

                   查询表emp的前5条记录

                   select* from emp where rownum<=5

返回第4条到第10条记录：

select * from

                  (select rownum as no, e.* from emp e whererownum<=10)

                  where no>=4

 

 

以上是oracle的重点  下面的只做了解

索引：

语法：

create[unique] index index_name

                            on table_name(column_name [,column_name…])

                            [tablespace tablespace_name]

其中参数：

unique：表示唯一性索引，即索引值不允许重复

index_name：指定要创建的索引的名称

table_name：要创建索引的目标表名称

column_name：索引的目标列名称

tablespace_name：索引所要位于的表空间的名称。如果没有指定，那么索引位于当前用户默认的表空间中

为employees表的emp_fname列创建索引以加快查询速度：

create indexindex_fname on employees (emp_fname)

创建同义词(SYNONYM)：

同义词(SYNONYM)：可以理解为其它对象的一个别名，由它映射到另一个对象。

同义词分为公有(public)同义词和私有同义词，仅有同义词对所有用户可见，默认创建的同义词是私有同义词，只有某个用户可见。

创建语法：

create [public] synonym table_name for user.table_name;

drop [public] synonym table_name;

序列：

语法：

createsequence sequence_name

                  [start with start_number]

                  [increment by increment_number]

                  [(maxvalue maximum_number |nomaxvalue)]

                  [(minvalue minimum_number |nominvalue)]

                  [(cycle | nocycle)]

                  [(cache cache_number| nocache)]

                  [(order | noorder)]

其中参数：

sequence_name：指定序列的名称

start_number：指定序列的起始整数值，默认值为1

increment_number：指定序列的步长，即增量

maximum_number：指定序列的最大值

nomaxvalue：没有最大值限制，这是默认值

minimum_number：指定序列的最小值

nominvalue：没有最小值的限制，这是默认值

cycle：序列数值是否循环生成

nocycle：序列数值不能循环，这是默认值

cache_number：保留在内存中的整数的数量，默认值是20

nocache：不指定存储在内存中的整数的数量

order：按照顺序生成序列值

noorder：不强调按照顺序生成序列值

序列中包含两个伪列，即currval和nextval列。在使用序列之前，必须先使用nextval对序列执行初始化操作

返回序列的下一个值：

selectsequence_name.nextval from dual

返回序列的当前值：

selectsequence_name.currval from dual

视图：

作用：

视图能简化用户的操作

视图使用户能以多种角度看待同一数据

视图对重构数据库提供了一定程度的逻辑独立性

视图能够对机密数据提供安全保护

语法：

create orreplace view 视图名字

as　SQL语句

使用，语法如下：

select * from视图名

删除视图的语法如下：

drop view 视图名

存储过程：

语法：

         CREATE[OR REPLACE] PROCEDURE<过程名>  (

         <参数1>，[方式l] <数据类型1>， 

         <参数2>，[方式2] <数据类型2>， 

         ……) 

         IS|AS (is或as完全等价 )

         [局部变量声明]

         BEGIN 

               PL/SQL过程体 

         END＜过程名＞

其中参数：

in参数类型：表示输入给过程的参数

out参数类型：表示参数在过程中将被赋值，可以传给过程体的外部

in out参数类型：表示该类参数既可以向过程体传值，也可以在过程体中赋值，以便向过程体外传值

存储过程可以直接用EXECUTE命令调用或PL/SQL程序块内部调用

用EXECUTE命令调用存储过程的格式如下： 

SQL>EXECUTE  proc_name(par1,par2…);

存储过程也可以被另外的PL/SQL块调用，调用的语句是：

         DECLARE par1,par2;

         BEGIN

            proc_name(par1,par2…);

         END;

         在调用前要声明变量par1，par2

删除存储过程：

DROP PROCEDURE proc_name; 

函数：

语法：

         CREATE[OR REPLACE] FUNCTION<函数名> [(参数列表)]

         RETURN数据类型

         IS|AS (is或as完全等价 )

         [局部变量声明]

         BEGIN 

               PL/SQL函数体 

         END[<函数名>]

函数的结束一定要使用return语句返回一个与声明匹配的值

create or replacefunction f_getinfo(str varchar2) return varchar2
is
begin
  dbms_output.put_line('test function ');
  return 'Hello ' || str;
end;

函数的功能是返回一个值，因此可以用在SQL查询语句中，也可以用在PL-SQL语句块中。

用SQL调用函数的格式如下： 

SQL>select  fun_name(par1,par2…) from tablename;

如：select f_getinfo('Jack')from dual;

删除函数可使用如下命令：

         dropfunction funname;

触发器：

功能：

允许/限制对表的修改

自动生成派生列，比如自增字段

强制数据一致性

提供审计和日志记录

防止无效的事务处理

启用复杂的业务逻辑

类型： 

DML语句（DELETE，INSERT，UPDATE）

DDL语句（CREATE，ALTER， DROP）

数据库操作（SERVER ERROR，LOGON，LOGOFF，STARTUP，SHUTDOWN）

语法:

         CREATE[ OR REPLACE ] TRIGGER name 

         {BEFORE | AFTER }  { event [ OR ... ] }

         ON table [FOR [ EACH ] { ROW | STATEMENT } ] 

         [WHEN(condition) ]

         plsql block| call procedure s_statement

注意事项：

触发器可以声明为在对记录进行操作之前(检查约束之前和 INSERT，UPDATE 或 DELETE 执行前)或之后(在检查约束之后和完成 INSERT， UPDATE 或 DELETE 操作)触发

一个 FOR EACH ROW 执行指定操作的触发器为操作修改的每一行都调用一次

SELECT 并不更改任何行，因此不能创建 SELECT 触发器。这种场合下视图更适合

触发器和某一指定的表格有关，当该表格被删除时，任何与该表有关的触发器同样会被删除

在一个表上的每一个动作只能有一个触发器与之关联

在一个单独的表上，最多只能创建三个触发器与之关联，一个INSERT触发器，一个DELETE触发器和一个UPDATE触发器

删除触发器语法：

DROP TRIGGER name ON table