oracle数据库基本操作
Oracle系统的特点
支持大数据库、多用户的高性能的事务处理
Oracle遵守数据存取语言、操作系统、用户接口和网络通信协议的工业标准
实施安全性控制和完整性控制
支持分布式数据库和分布处理
具有可移植性、可兼容性和可连接性
创建表
语法:
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
)
插入数据:
语法:
insert into table (column1, column2, …)
values (column1_value, column2_value, …)
注意格式:
数值型字段可以直接写值
字符型字段,其值要加上单引号
日期型字段,其值要加上单引号,同时还要注意年、月、日的顺序。或者使用to_date ()函数将字符串转化为日期类型
插入列排序和插入值要一一对应,非空列必须有值
注意:
由于insert、update和delete等操作需要影响数据表中的记录,使用commit指令提交所有的操作,使用rollback指令回滚所有的操作
如果向表中插入所有的字段,可以省略列的列表,但是必须包括所有的列的值,而
且和数据表中的定义顺序一致
更新数据:
语法:
update table set column1=value1, column2=value2, …
where condition
范例:
update emp set empno=8888, ename=‘Jacky’
where empno=7566
删除数据:
语法:
delete from table where condition
范例:
delete from emp
where empno=8888;
修改表结构:
增加列
语法:alter table table_name add column_name type
范例:alter table employees add phone varchar2(20)
更新列
语法:alter table table_name modify column_name type
范例: alter table employees modify phone varchar2(30)
更新列时应注意:
一般情况下,只能把数据的长度从低到高改变,不能从高到低改变。可以把某种数据类型改变为兼容的数据类型
当表中没有数据时,可以把数据的长度从高到低改变,可以把某种数据类型改变为其它数据类型
删除列
语法: alter table table_name drop column column_name
范例: alter table employees drop column phone
删除表:
语法:
drop table table_name
范例:
drop table projects
约束
语法:
alter table table_name
add constraint constraint_name constraint_expression
分类:
主键约束(primary key):唯一的标识,本身不能为空
范例:给person表加主键约束
constraint person_pid_pk primary key(pid);
唯一约束(unique):在一张表中只允许建立一个主键约束,而其他列如果不希望出现重复值的话,就可以使用唯一约束
范例:给person表的name加唯一约束
constraint person_name_uk unique(name);
检查约束(check):检查一个列的内容是否合法
范例:给person表的性别列加上约束
constraint person_sex_ck check(sex in(‘男’,’女’));
非空约束(not null):内容不可以为空
范例:name varchar(10) not null,
外键约束(foreign key):在两张表中进行操作
范例:给person表和book表加外键约束
constraint person_book_fk foreign 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_pk primary key(bid);
alert table book
constraint person_book_fk foreign key(pid) references person(pid)
on delete cascade;
4.在删除person表中信息的时候,有关book表中的内容也会被删除
结构化查询语句(SQL)
SQL语言组成:
1.数据定义语言DDL(Data Definition Language)
它是SQL中用来生成、修改、删除数据库基本要素(表、视图、模式、目录等)的部分。(CREATE,DROP,ALTER)
2.数据操纵语言DML(Data Manipulation Language)
是SQL运算数据库的部分,它是对数据库中的数据输入、修改及提取的有力工具。(INSERT,UPDATE ,DELETE,SELECT)
3.数据控制语言DCL(Data Control Language)
提供数据库完整性所必需的防护措施。(GRANT,REVOKE)
数据库服务器的关闭、启动等
SQL语言的优点
(1)一体化特点:
使用SQL可实现数据库生命周期中的全部活动
(2)高度非过程化:
它是一种非过程化语言,允许用户依据做什么来说明操作,而不用说明怎样做,存取路径的选择和操作过程由系统自动完成
(3)语言简洁,易学易用
(4)统一的语法结构对待不同的工作方式
无论是联机交互使用,还是嵌入到高级语言中,其语法结构是基本一致的,这就大大改善了最终用户和程序设计人员之间的交流
基本单表查询
查询所有记录:
select * from 表名称;
查询所有记录的某些字段:
select column1,column2…… from 表名称
取消重复项:
select distinct column1,column2…… from 表名称
显示字段的别名:
select distinct column1 as 别名1,column2 as 别名2…… from 表名称
单条件查询:
select distinct * from 表名称 {where 条件(s)}
比较运算符:
=(等于)
select * from emp where job=‘MANAGER’
<>、!=、^=(不等于)
select * from emp where sal<>1100
<(小于)
select * from emp where sal<2000
>(大于)
select * from emp where sal>2000
<=(小于等于)
select * from emp where sal<=2000
>=(大于等于)
select * from emp where sal>=2000
in(列表)
select * from emp where sal in (1000, 2000, 3000)
not in(不在列表)
select * from emp where sal not in (1000, 2000, 3000)
between…and…(介于之间)
select * from emp where sal between 1000 and 2000
not between…and…(不介于之间)
select * from emp where sal not between 1000 and 2000
is null(是否为空)
select * from emp where comm is null (注意,区别null和0,空字符串‘’)
is not null(是否不为空)
select * from emp 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)} {order by 排序的字段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个
select substr(’13088888888’,3,8) from dual;
length :返回字符串的长度
select name,length(name) from gao.nchar_tst;
replace(m,n,l):内容的替换
select replace(‘hello’,’ll’,’xx’) from dual;
upper():在一个字符串不知道大小写的情况下用
select * from emp where ename=upper(‘smith’);结果返回大写
lower():将一个字符串变为小写字母表示
select lower(‘HELLO’) from dual;
initcap():将单词的首字母大写
select initcap(‘HELLO’)from dual;
数值函数:
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():给定日期范围的月数
select empno,ename,months_between(sysdate,hiredate) from emp;
next_day():下一个给定的日期的今天是哪个日期
select next_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:转换成数字
select to_number(‘123’)+to_number(‘123’) from dual; 结果是:246
to_date: 根据格式把字符转化成日期
select to_date(‘2009-8-6’,’yyyy-mm-dd’) from dual;
通用函数:
decode():类似于if…else if…else if…else…
select empno,ename,hirdate decode(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) chr65 from dual;
concat :连接两个字符串
select concat(’010-’,’88888888’)||’转23’ from dual;
rpad和lpad: 在左边/右边重复指定的字符
select lpad(rpad(’gao’,10,’*’),17,’*’)from dual;
trim/ltrim/rtrim: 去除空白字符
分组查询:
语法:
select distinct *|查询列1 as 别名1,查询列2 as 别名2…… from 表名称 {where 条件(s)} {group by 分组条件{Having 分组条件}}{order by 排序的字段1 ,排序的字段2 asc|desc(升序|降序)}
范例:select job, count(*) from emp group by job
注:
常和聚合函数一起使用
聚合函数如果作为条件,必须使用having子句
除了聚合函数以外,如果在查询中显示其他字段,该字段必须出现在group by分组列中
group by中不能使用列的别名,也不能使用序列号
having中的聚合函数不能使用别名
内连接:
语法:
Select column_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
左右全连接:
语法:
Select column_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
多表连接:
等值连接
select e.empno, e.ename, d.dname from emp e, dept d
where e.deptno=d.deptno
不等值连接
select e.empno, e.ename, d.dname
from emp e, dept d
where e.deptno<>d.deptno and e.deptno=10
自连接:
使用自连接应注意:
使用两个不同的别名命名表
使用不平衡条件,例如大于或小于条件。如果使用平衡条件,则检索结果会出现两倍的冗余
自连接的结果一定要有实际意义
范例:
select d1.deptno, d1.dname, d2.loc
from dept d1, dept d2
where d1.loc = d2.loc and d1.dname <> d2.dname
子查询:
在select查询语句里可以嵌入select查询语句,成为子查询或嵌套查询。子查询形成的结果又成为父查询的条件
使用子查询应注意:
子查询可以嵌套多层
子查询操作的数据表可以是父查询不操作的数据表
子查询中不能有order by排序语句
范例:
select empno, ename, job, sal from emp
where sal>(select sal from emp where ename=‘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 (select sal 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 where deptno=d.deptno)
并操作(union)的嵌套查询:
并操作是集合中并集的概念,属于集合A或集合B的元素的总和就是并集
select deptno from emp
union (all)
select deptno from dept
union all和union不同之处在于union all会将每一条符合条件的记录都列出来,无论记录是否重复
交操作(intersect)的嵌套查询:
交操作是集合中交集的概念,属于集合A且属于集合B的元素的总和就是交集
select deptno from emp
intersect
select deptno from dept
差操作(minus)的嵌套查询:
差操作是集合中差集的概念,属于集合A且不属于集合B的元素的总和就是差集
select deptno from 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 where rownum<=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 index index_fname on employees (emp_fname)
创建同义词(SYNONYM):
同义词(SYNONYM):可以理解为其它对象的一个别名,由它映射到另一个对象。
同义词分为公有(public)同义词和私有同义词,仅有同义词对所有用户可见,默认创建的同义词是私有同义词,只有某个用户可见。
创建语法:
create [public] synonym table_name for user.table_name;
drop [public] synonym table_name;
序列:
语法:
create sequence 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对序列执行初始化操作
返回序列的下一个值:
select sequence_name.nextval from dual
返回序列的当前值:
select sequence_name.currval from dual
视图:
作用:
视图能简化用户的操作
视图使用户能以多种角度看待同一数据
视图对重构数据库提供了一定程度的逻辑独立性
视图能够对机密数据提供安全保护
语法:
create or replace 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 replace function 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;
删除函数可使用如下命令:
drop function 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