导图社区 Oracle
Oracle Database,又名Oracle RDBMS,或简称Oracle。Oracle数据库系统是世界上流行的关系数据库管理系统,系统可移植性好、使用方便、功能强,适用于各类大、中、小微机环境。
编辑于2021-12-10 17:03:07Oracle
术语
DB Database 数据库
DBMS database management system 数据库管理系统
DBA database administrator 数据库管理员
SQL structured query language 结构化查询语言
DDL 数据定义语言 create\alter\drop
DML 数据操作语言 insert\update\deldte\select
DQL 数据查询语言 order\group by
DCL 数据控制语言 grant\revoke
TCL 事务控制语言 commit\rollback\savepoint
目前主流的数据库管理系统:Mysql、Oracle、SQLServer、DB2
创建用户与权限
创建用户:create user 用户名 identified by 口令 unlock
添加权限: grant connect to 用户名 添加连接权限 grant resource to 用户名 添加资源访问权限
回收权限: revoke resource from 用户名 回收资源访问权限 revoke connect to 用户名 回收连接权限
数据类型
字符串: char(length) 固定长度字符串 varchar(length) 可变长充字符串 数值型: number number(6,2) 代表小数占2位,整数占4位 日期:date
约束类型
非空约束 not null
唯一约束 unique
主键约束 primary key (每个表中只能有一个主键约束)
外键约束 references
条件匐 check
默认约束 default
创建表
create table 表名(列名1 列名1类型 【约束】,列名2 列名2类型 【约束】......)
添加约束:alter table 表名 add constraint 约束名 约束类型
删除约束: alter table 表名 drop constraint 约束名
修改表名: rename 原表名 to 新表名
添加列:alter table 表名 add 列名 列类型 约束
删除列:alter table 表名 drop colum 列名
查询
查询:select *|列名|表达式 from 表名 【where 条件】
排序:order by 默认asc(升序)、desc(降序)
操作符: · 算数运算:+ - * / · 关系运算:=、>、<、 !=、<>、>=、<= · 逻辑运算:and、or、not · 字符串连接操作符: ||
消除重复行:distinct
null操作:is null is not null
in操作:使用in操作符来查询其列值在指定的列表中的行,not in 用法一样,结果取反。
between...and... 查询列值包含在指定区间内的行
like模糊查询: %:表示零个或者多个任意字符 _:代表一个任意字符 '\_%' escape '\' escape 表示\后面的那个符号不当成特殊字符处理,就是查找普通的_符号
集合运算: intersect交集(返回两个表中相同的内容) union并集(返回两个表中所有的内容,不包括重复的) minus补集(返回第一个表比第二个表多的内容)
等值连接: 在连接条件中使用等于号(=)运算符比较被连接列的列值,其查询结果中列出被连接表中的所有列,包括其中的重复列 例:selecte.ename,e.deptno,d.dnamefrom emp e,dept d wheree.deptno=d.deptno; 非等值连接:除了=之外的连接
自连接: 连接的两个表都是同一个表,同样可以由内连接,外连接各种组合方式,按实际应用去组合。 例:查询员工的领导姓名,员工的领导编号在emp中,领导的姓名在emp中。 selecte1.empno,e1.ename"员工姓名",e1.mgr"领导编号",e2.ename from emp e1,emp e2 wheree1.mgr=e2.empno;
左连接 left join :返回包括左表中的所有内容和右表中联结字段相等的内容 语法结构:select * from 表1 left join 表2 on 条件
右连接right join:返回包括右表中的所有内容和左表中联结字段相等的内容 语法结构 select* from表1 right join表2 on条件;
满连接full join:返回左表与右表的所有内容 例:selectd.deptno,d.dname,e.empno,e.salfrom emp e full join dept d on e.deptno=d.deptno;
any查询: <any 小于最高的 >any 大于最小的 例:查询出emp表中比销售员("SALESMAN")最高薪水低的员工姓名、工作、薪水 selecte.ename,e.job,e.salfrom emp e where sal < any (select sal from emp where job ='SALESMAN');
all查询: >all 大于最高的 <all 小于最低的 例:查询出比所有销售员的薪水都高的员工姓名、工作、薪水。 selecte.ename,e.job,e.salfrom emp e where sal > all (select sal from emp where job ='SALESMAN');
group by ...having...分组查询
求出除了部门20之外的,部门平均薪水大于1500的部门。 select deptno, avg(sal) from emp where deptno !=20 group by deptno havingavg(sal) >1500;
根据查询结果创建表
创建表结构+表数据: create table 表名 as select 语句
只创建表结构,不复制数据:create table 表名 as select 语句 where 1=2
插入数据 insert
语法:insert into 表名(列名1,列名2...)values(值1,值2....) 当后面的值与表中的列名一一对应的,前面的列名可以忽略不写 例:insert into dept values(80,'保卫部','深圳');
更新数据 update
语法:update 表名 set 列名1=值,列名2=值 where 条件; 例:update emp set sal = sal+100where sal >3000;
删除数据 delete
语法:delete from 表名 where 条件; 例:deletefrom empinfo where sal>3000;
删除全部数据,无法回滚
语法:truncate table 表名
事务
commit 提交更改
rolback 回滚
savepoit 保存点
函数
单行函数(只能输入一行结果,返回一个结果)
字符函数
concat(x,y) 连接字符串x和y
instr(x,str[,start][,n]) 在 x 中查找 str,可以指定从 start 开始,也可以指定从第 n 次开始
LENGTH(x) 返回x的长度
lower(x) 返回x的小写
upper(x) x转换为大写
LTRIM(x[,trim_str]) 把 x 的左边截去 trim_str 字符串,缺省截去空格
RTRIM(x[,trim_str]) 把 x 的两边截去 trim_str 字符串,缺省截去空格
TRIM([trim_str FROM] x) 把 x 的两边截去 trim_str 字符串,缺省截去空格
数字函数
abs(x) 绝对值
ceil(x) 大于或等于 x 的最小整数
FLOOR(x) 小于或等于 x 的最大整数
ROUND(x[,y]) x 在第 y 位四舍五入
TRUNC(x[,y]) x 在第 y 位截断
转换函数
to_char 例:select to_char(-123.45) from dual;
to_date 例:select to_date('2017年07月09日 21:26:08', 'YYYY"年"MM"月"DD"日" HH24:MI:SS') from dual;
to_number 例:select to_number('-123.45') from dual;
日期函数
显示系统当前时间 select sysdate from dual;
显示本月最后一天 select last_day(sysdate) from dual;
其它常用单选函数
NVL(x,value): 如果 x 为空,返回 value,否则返回 x
NVL2(x,value1,value2): 如果 x 非空,返回 value1,否则返回 value2
聚合函数(可以对多行数据操作,返回一个结果)
avg 平均值
sum 求和
min,max 最小值,最大值
count 数据统计
伪列
rowid:显示每一行唯一的物理地址
rownum:返回指定的行数
返回表前5行数据: · 查询出员工表中前 5 名员工的姓名,工作,薪水。 SELECT ROWNUM,ENAME,JOB,SAL FROM EMP WHERE ROWNUM<=5;
查询出薪水最高的前 5 名员工的姓名、薪水和工作。 SELECT ROWNUM,T.* FROM (SELECT ENAME,JOB,SAL FROM EMP ORDER BY SAL DESC) T --T为别名 WHERE ROWNUM<=5;
查询出表 EMP 中第 5 条到第 10 条之间的记录 SELECT*FROM (SELECT ROWNUM R,ENAME,JOB,SAL FROM EMP) WHERE R between 5and10;
视图
创建一个虚拟表,提供给第三方调用,起到保密的作用 创建视图语法: CREATE [OR REPLACE] [{FORCE|NOFORCE}] VIEW 视图名 AS SELECT查询 [WITH READ ONLY]
CREATE OR REPLACEVIEWEMPDETAIL AS SELECT EMPNO,ENAME,JOB,HIREDATE,EMP.DEPTNO,DNAME FROM EMP JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO WITH READ ONLY;
调用视图
select * from 视图名
删除视图
drop view 视图名
索引
可以对表中经常访问的列创建索引,主要是为了提高查询的效率 语法:CREATE [UNIQUE] INDEX 索引名 ON table_name(列名1[,列名2…])
例:CREATE INDEX IDX_SAL ON EMP(SAL);
删除索引:dropindex 索引名
存储过程
存储过程是一组为了完成特定功能的SQL 语句集,经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它 存储过程只在创造时进行编译,以后每次执行存储过程都不需再重新编译,而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度
创建存储过程(在SQL窗口中创建,创建成功后在“Procedures”中查看) create or replace procedure sp_update_sal(name in varchar) --通知Oracle数据库去创建一个叫做sp_update_sal存储过程, 如果存在就覆盖它 as --as关键词表明后面将跟随一个PL/SQL体 begin --BEGIN关键词表明PL/SQL体的开始 update emp set sal = sal+1 where ename = name; commit; end sp_update_sal; --END关键词表明PL/SQL体的结束
执行存储过程(在SQL窗口中调用) begin 存储过程名(参数); end; begin dbms_output.put_line() -- 在SQL窗口“输出”中打印出结果 end;
实例
for 循环
生成1000000行数据 前提条件,先创建一个表: create table testnumber (id number,name varchar2(10)); create or replace procedure sp_number(id in number) as begin for i in 1 .. id loop insert into testnumber values(i,'A'||i); end loop; commit; end; begin sp_number(1000000); end;