Oracle常用操作
oracle的一些基本概念
oracle服务器
n 是一个数据管理系统(RDBMS),它提供开放的，全面的，近乎完善的信息管理
n 由一个oracle数据库和多个Oracle实例组成
oracle数据库和oracle实例
n Oracle数据库：位于硬盘上实际存放数据的文件，这些文件组织在一起，成为一个逻辑的整体，即为Oracle数据库，因此oracler看来，数据库 是指硬盘上文件的逻辑集合，必须要与内存里实例合作，才能对外提供数据管理服务。
n oracle实例：位于物理内存里的数据结构，它由一个共享的内存池和多个后台进程所组成，共享的内存池可以被所有进程访问，用户如果要存取数据库里的数据，必有通过实例才能实现，不能直接读取硬盘上的文件
n 区别：实例可以操作数据库，在任何时刻一个实例只能与一个数据库关联，多数据情况下，一个数据库上只能有一个实例对其进行操作
表空间和数据库文件
n 表空间是由多个数据文件组成，数据文件只能属于一个表空间
n 表空间为逻辑概念，数据文件为物理概念
段、区、块
n 段存在于表空间中
n 段是区的集合
n 区是数据块的集合
n 数据块会被映射到磁盘块中
oracle的基本查询、过滤和排序
解决SqlPlus前台程序出现中文乱码的问题
|
第一步：在sqlplus中执行 select userenv('language') from dual;查看当前数据库的字符集为：SIMPLIFIED CHINESE_CHINA.ZHS16GBK。我们只需要把客户端字符集和操作系统字符集都设置成这个就行了 |
|
第二步：在环境变量中查找一个名为“NLS_LANG”的环境变量，如果找不到，则新建一个，把变量值赋值为：“SIMPLIFIED CHINESE_CHINA.ZHS16GBK”
|
n 使用scott帐号登录【sqlplus scott/scott@192.168.56.102:1521/orcl】
基本查询语句
|
spool: |
1.保存sql语句到文件 【spool C:\Users\guoyongfeng\Desktop\基本查询.txt】 2.spool off 写入内容到文件 |
|
host cls: |
清屏 |
|
show user |
显示当前登录用户 |
|
select * from tab; |
当前用户下的表 |
|
desc |
查询表的结构 【desc emp;】 |
|
select * from emp; |
所有的员工信息 |
|
show linesize |
显示行宽 |
|
set linesize 150 |
设置列宽 |
|
col ename for a8 |
设置ename列宽，显示8个字符，a表示字符 |
|
col sal for 9999 |
设置sal列显示4个数字，9表示数字 |
|
/ |
表示执行上一条语句 |
|
c |
Change 命令，输错语句时用【如:c /form/from】 |
|
* + |
乘法和加法运算 【查询员工信息： 员工号 姓名 月薪 年薪 奖金 年收入】 SQL> select empno,ename,sal,sal*12,comm,sal*12+comm |
|
SQL中的null值 |
1.包含null的表达式都为null SQL> select empno,ename,sal,sal*12,comm,sal*12+nvl(comm,0) from emp 2.null永远!=null 如：查询奖金为null的员工 SQL> select * from emp where comm=null; SQL> select * from emp where comm is null;【正确写法】 |
|
as |
列的别名 SQL> select empno as "员工号",ename "姓名",sal "月 薪",sal*12,comm,sal*12+nvl(comm,0) from emp |
|
ed |
写入进入缓存文件 file afiedt.buf |
|
distinct |
1.去除重复 SQL> select distinct deptno from emp; SQL> select job deptno from emp; 2.distinct作用于后面所有的列【了解】 SQL> select distinct deptno, job from emp; |
|
concat |
字符串连接 SQL> select concat('Hello',' World') from dual; //注意：必须加后面的为from与虚表字段，不然会报错 |
|
dual |
伪表 SQL> select concat('Hello',' World') from dual; SQL> select 3+2 from dual; |
|
|| |
1.也表示字符串连接 SQL> select 'Hello'||' World' 字符串 from dual; 2.查询员工信息： ***的薪水是**** SQL> select ename||'的薪水是'||sal 信息 from emp; |
oracle过滤和排序
|
字符大小写敏感 |
如查询员工为SMITH的信息 SQL> select * from emp where ename='SMITH'; SQL> select * from emp where ename='SMITh'; |
|
日期格式敏感 |
SQL> select * from v$nls_parameters;【查看当前的日期格式】 SQL> alter session set NLS_DATE_FORMAT='yyyy-mm-dd';【修改当前会话的日期格式】 查询日期： SQL> select * from emp where hiredate='17-12月-80'; SQL> select * from emp where hiredate='1980-12-17'; |
|
between and |
查询薪水1000~2000之间的员工，结果是包含1000和2000的 SQL> select * from emp where sal between 1000 and 2000; 注：含有边界 小值在前 大值在后，下面写是不合法的 |
|
in 在集合中 not in 不在集合中 |
1.查询10和20号部门的员工 SQL> select * from emp where deptno in(10,20); 2.查询不在10和20号部门的员工 SQL> select * from emp where deptno not in(10,20); 如果集合中含有null，不能使用not in; 但可以使用in |
|
like 模糊查询 |
1.查询名字以S打头的员工 SQL> select * from emp where ename like 'S%'; 2查询名字是4个字的员工，4个下划线即可 SQL> select * from emp where ename like '____' 3.查询名字中含有下划线的员工 需要转意字符 SQL> select * from emp where ename like '%\_%' escape '\'; |
|
rollback 回滚 |
Oracle是自动开启事务,不同mysql需要手动开启 |
|
order by 排序 |
1.查询员工信息按照月薪排序 SQL> select * from emp order by sal; 2.按年薪降序排序【order by后面 + 列，表达式，别名，序号】 SQL> select empno,ename,sal,sal*12 from emp order by sal*12 desc; SQL> select empno,ename,sal,sal*12 年薪 from emp order by 年薪 desc;别名 SQL> select empno,ename,sal,sal*12 年薪 from emp order by 4 desc;列数 3.按部门升序然后按工资降序排序员工信息 SQL> select * from emp order by deptno,sal desc 4.按部门降序然后按工资降序排序员工信息 SQL> select * from emp order by deptno desc,sal desc 5.order by 作用于后面所有的列；desc只作用于离他最近的列 6.查询员工信息 按照奖金降序排序，null值是最大，所以会排在最前面 SQL> select * from emp order by comm desc; select * from emp order by comm desc nulls last; |
|
set pagesize |
SQL> set pagesize 20 设置分页大小 |
Oracle的两种函数
单行函数
n 单行函数：用于操作数据对象,比如操作字符，数值，日期
|
函数格式：参数可以是一个列或一个值 function_name[(arg1,arg2,...)] |
字符操作
|
lower 转小写 |
SQL> select lower('Hello') 转小写upper('Hello') 转大写,initcap('hello') 首写母大写 from dual |
|
upper转大写 |
|
|
Initcap 首写母大写 |
|
|
substr(a,b) 从a中，第b位开始取 |
SQL> select substr('Hello,How Are You',3) from dual; |
|
substr(a,b,c) 从a中，第b位开始取,取后面c个字符 |
SQL> select substr('Hello,How Are You',7,3) from dual; |
|
instr(a,b) b字符串在a 中的位置 |
SQL> select instr('hello','ll') from dual; |
|
length 字符数 lengthb 字节数 |
SQL> select length('hello你好') 字符串,lengthb('hello你好') from dual; |
|
lpad 左填充 rpad 右填充 |
SQL> select lpad('abc',10,'*') from dual; SQL> select rpad('abc',10,'*') from dual; |
|
trim 去掉前后指定的字符 |
SQL> select trim('H' from 'Hello,WorldH') from dual; |
|
replace 替换 |
SQL> select replace('hello','ll','**') from dual; SQL> select substr('13522221234',1,3)||'****'||substr('13522221234',-4,4) 手机 from dual;【手机格式】 |
浮点操作
|
round 四舍五入 |
SQL>select round(466.691,2) 一,round(466.691,1) 二,round(466.691,0) 三,round(466.691,-1) 四,round(466.691,-2) 五 from dual; |
|
trunc 截断 |
SQL>select trunc(466.691,2) 一,trunc(466.691,1) 二,trunc(466.691,0) 三,trunc(466.691,-1) 四,trunc(466.691,-2) 五 from dual |
时间格式化
|
格式 |
说明 |
举例 |
|
YYYY |
Full year in numbers |
2011 |
|
YEAR |
Year spelled out(年英文全称) |
Twenty eleven |
|
MM |
Tow-digit value of month 月分(两位数字) |
04 |
|
MONTH |
Full name of month（月的全称） |
4月 |
|
DY |
Three-letter abrreviation of the day of the week(星期几) |
星期一 |
|
DAY |
Full name of the day of the week |
星期一 |
|
DD |
Numeric day of the moth |
02 |
函数常用格式
|
9 |
数字 |
|
0 |
零 |
|
$ |
美元符 |
|
L |
本地货币符号 |
|
. |
小数点 |
|
, |
千位符 |
时间操作
|
sysdate 当前时间 |
SQL> select sysdate from dual; |
|
to_char 格式化时间 |
to_char的函数格式 to_char(date,’formate_model’) SQL> select to_char(sysdate,'yyyy-mm-dd HH24:mi:ss') 当前时间 from dual; |
|
时间相加减
|
1.昨天 今天 明天 SQL>select sysdate-1 昨天,sysdate 今天,sysdate+1 明天 from dual;
2.计算员工的工龄：天 星期 月 年 SQL>select empno,ename,hiredate, (sysdate-hiredate) 天,(sysdate-hiredate)/7 星期,(sysdate-hiredate)/30 月,(sysdate-hiredate)/365 年 from emp |
|
months_between 两个日期相差的月数 |
SQL> select empno,ename,hiredate, (sysdate-hiredate)/30 一,months_between(sysdate,hiredate) 二 from emp; |
|
add_months 当前日期加上n个月的时间 |
一年后的时间 SQL> select add_months(sysdate,12) from dual; |
|
last_day 最后一天 |
当前月的最后一天 SQL> select last_day(sysdate) from dual; |
|
next_day 下个星期几 |
下个星期 SQL>select next_day(sysdate,'星期日') from dual; next_day的应用：设置时间自动备份数据 |
|
to_char 也可以格式数字 |
1.年和月的四舍五入 SQL> select round(sysdate,'month'),round(sysdate,'year') from dual; 2.2017-06-29 09:50:09今天是星期四 SQL>select to_char(sysdate,'yyyy-mm-dd HH24:mi:ss"今天是"day') from dual 3.查询员工薪水：两位小数 千位符 货币代码 SQL> select to_char(sal,'L9,999.99') from emp; |
空操作
|
nvl2(a,b,c) 当a=null的时候，返回c；否则返回b |
如果奖金是空，输出0，注意2前的是L的小写，不是1 SQL> select ename,nvl2(comm,comm,0) from emp; |
|
nullif(a,b) 当a=b的时候，返回null；否则返回a |
SQL> select nullif('abc','abc') from dual; SQL> select nullif('abc','abcb') from dual; |
|
coalesce 从左到右找到第一个不为null的值 |
SQL> select comm,sal,coalesce(comm,sal) 第一个不为空的值 from emp; |
|
nvl(a,b) |
|
条件判断
n 单行条件判断可以使用case when then end或者decode
|
需求：涨工资，总裁1000 经理 800 其他400 |
|
n case when then end |
|
sql>select ename,job,sal 涨前,case job when 'PRESIDENT' then sal+1000 when 'MANAGER' then sal+800 else sal+400 end 涨后 from emp; |
|
n decode |
|
SQL>select ename,job,sal 涨前,decode(job,'PRESIDENT',sal+1000,'MANAGER',sal+800,sal+400) 涨后 from emp |
分组函数
组函数语法
|
SELECT [column,] group_function(column),... FROM table [WHERE condition] [GROUP BY column] [GROUP BY column] Having... |
常用组函数
|
Avg 求平均数 |
平均工资 SQL> select avg(sal) from emp; |
|
Count 求记录数 |
员工数 SQL> select count(*) from emp; 有奖金的个数 SQL> select count(comm) from emp; distinct 可去除重复 SQL> select count(distinct deptno) from emp; |
|
Max 求最大值 |
最高工资 SQL> select max(sal) from emp; |
|
Min 求最小值 |
最低工薪 SQL> select min(sal) from emp; |
|
Sum 示各和 |
每月工薪支出 SQL> select sum(sal) from emp; 平均奖金 SQL> select avg(comm) from emp; 奖金不为空的员工的平均奖金 SQL> select sum(comm)/count(*) from emp;所有员工的平均奖金 |
|
|
1.null值组函数（多行函数）自动滤空，也就是空值也会计算 SQL> select count(*), count(nvl(comm,0)) from emp; select avg(nvl(comm,0)) from emp; |
group by 分组
n 在select列表中，所有未包含的组函数中的列都应该包含在group by子句中
|
求每个部门的平均工资 |
SQL> select deptno,avg(sal) from emp group by deptno;【一个列的分组】 |
|
按部门和职位求平均工资 |
SQL> select deptno,job,avg(sal) from emp group by deptno,job order by 1;【多个列的分组】 |
|
求平均工资大于2000的部门 |
SQL> select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000; |
n where和having的区别：where后面不能使用多行函数
|
查询10号部门的平均工资 |
SQL> select deptno,avg(sal) from emp where deptno='10' group by deptno; |
|
|
|
n rollup groupby语句增加【报表汇总】
|
|
select deptno,job,avg(sal) from emp group by deptno,job【根据部门和职位汇总平均工资】 select deptno,avg(sal) from emp group by deptno;【根据部门汇总平均工资】 select deptno,avg(sal) from emp group【平均工资】 |
|
group by rollup(a,b) ================= group by a,b group by a group by null |
select deptno,job,avg(sal) from emp group by rollup(deptno,job)
|
|
break on deptno skip 2 break on null |
格式化输出 skip2表示空两行 |
多表查询
连接类型
|
等值连接 |
查询员工信息： 员工号 姓名 月薪 部门名称 SQL> select e.empno,e.ename,e.sal,d.dname from emp e,dept d where e.deptno=d.deptno; |
|
不等值连接 |
查询员工信息： 员工号 姓名 月薪 工资级别【员工表和级别表是没有外键关联的】
SQL> select * from salgrade; SQL>select e.empno,e.ename,e.sal,s.grade from emp e,salgrade s where e.sal between s.losal and s.hisal |
|
外连接 |
1.按部门统计员工人数：部门号部门名称人数 SQL> select d.deptno,d.dname,count(e.empno) from emp e,dept d where e.deptno=d.deptno group by d.deptno,d.dname;
2.对于某些不成立的记录，任然希望包含在最后的结果中 左外连接：当where e.deptno=d.deptno不成立的时候，等号左边的表任然被包含 写法:where e.deptno=d.deptno(+) 右外连接：当where e.deptno=d.deptno不成立的时候，等号右边的表任然被包含 写法: where e.deptno(+)=d.deptno SQL> select d.deptno,d.dname,count(e.empno) from emp e,dept d where e.deptno(+)=d.deptno group by d.deptno,d.dname; |
|
自连接 |
自连接：通过表的别名，将同一张表视为多张表 查询员工信息：员工姓名 老板(上级)姓名 SQL> select e.ename 员工,b.ename 老板 from emp e,emp b where e.mgr = b.empno; |
|
笛卡尔集总数 |
select count(*) from emp e,emp b; |
层次查询
|
|
from 表 connect by prior 条件 start with 开始条件 |
|
|
select level,empno,ename,mgr from emp connect by prior empno=mgr start with mgr=7566 order by 1 |
level是伪列 |
|
select level,empno,ename,mgr from emp connect by prior empno=mgr start with mgr is null order by 1 |
|
子查询
子查询语法
|
select select_list from table where expr operator(select select_list from table) |
子查询注意的问题：
1. 子查询需要添加括号
2. 合理的书写风格
3. 可以在where select having from后面 都可以使用子查询
4. 不可以在group by后面使用子查询
5. 强调from后面的子查询
6. 主查询和子查询可以不是同一张表；只要子查询返回的结果 主查询可以使用 即可
7. 一般不在子查询中排序；但在top-n分析问题中，必须对子查询排序
8. 一般先执行子查询，再执行主查询；但相关子查询例外
9. 单行子查询只能使用单行操作符；多行子查询只能使用多行操作符
10.子查询中的null
举例子
|
了查询需要括号 |
查询工资比SCOTT高的员工信息 SQL>select * from emp where sal > (select sal from emp where ename='SCOTT') |
|
select后可以添加子查询 |
查询员工记录后，每一行后面显示总裁 SQL> select empno,ename,sal,(select ename from emp where empno=7839) 总裁 from emp; |
|
from后面的子查询 |
查询员工信息：员工号 姓名 月薪 SQL> select * from (select empno,ename,sal from emp); 查询员工信息：员工号 姓名 月薪 年薪 SQL> select * from (select empno,ename,sal,sal*12 annsal from emp); 上面的语句跟select empno,ename,sal,sal*12 annsal from emp性能一样的，oracle内部会重构 |
|
主查询和子查询可以不是同一张表；只要子查询返回的结果 主查询可以使用 即可 |
查询部门名称是SALES的员工，实现方法有两种 SQL>select * from emp where deptno=(select deptno from dept where dname='SALES'); SQL>select e.* from emp e,dept d where e.deptno=d.deptno and d.dname='SALES'; |
|
in 在集合中 |
查询部门名称是SALES和ACCOUNTING的员工 SQL> select * from emp where deptno in (select deptno from dept where dname='SALES' or dname='ACCOUNTING'); |
|
any: 和集合中的任意一个值比较 |
查询工资比30号部门到少一个员工高的员工信息【下面两行是等价】 SQL> select * from emp where sal > any (select sal from emp where deptno = 30); SQL> select * from emp where sal > (select min(sal) from emp where deptno = 30); |
|
all 和集合中的所有值比较 |
查询工资比30号部门所有员工高的员工信息【下面两行是等价】 SQL> select * from emp where sal > all (select sal from emp where deptno = 30); SQL> select * from emp where sal > (select max(sal) from emp where deptno = 30); |
|
not in(a,b,null) not in中不能有null in 中可以有null 多行子查询中的null |
查询是老板（管理层）的员工 SQL> select * from emp where empno in(select mgr from emp); 查询不是老板(管理层)的员工 SQL> select * from emp where empno not in(select mgr from emp);【not in中不能有null】 SQL> select * from emp where empno not in(select mgr from emp where mgr is not null);【这个写法才正确】 |
集合运算
|
举例子
n 这里了解union就可以,intersect和minus原理一样
|
Union 注意的问题： 1.参与运算的各个集合必须列数相同且类型一致 2. 采用第一个集合作为最后的表头 3. order by 永远在最后 4. 括号 |
查询10和20号部门的员工 SQL> select * from emp where deptno in (10,20); SQL> select * from emp where deptno=10 or deptno=20; 上面的代码等价于下面的代码 select * from emp where deptno=10 加上 select * from emp where deptno=20
|
|
SQL> select deptno,job,sum(sal) from emp group by rollup(deptno,job); 等价 select deptno,job,sum(sal) from emp group by deptno,job + select deptno,sum(sal) from emp group by deptno + select sum(sal) from emp 等价 select deptno,job,sum(sal) from emp group by deptno,job union select deptno,to_char(null),sum(sal) from emp group by deptno union select to_number(null),to_char(null),sum(sal) from emp |
|
|
开发中少用union,性能比较差 Set timing on/off 可以查时间 |
|
rownum行号
|
SQL> select rownum,e.* from emp e; |
n rownum永远按照默认的顺序生成
|
SQL> select rownum,e.ename,e.sal from emp e order by sal desc; |
n rownum只能使用 < <=; 不能使用> >=
rowid行地址
|
SQL> select rowid,empno,ename,sal from emp;
SQL> select * from emp where rowid='AAAMfPAAEAAAAAgAAA'; |
临时表
1. create global temporary table *****
2. 自动创建: order by
特点：当事务或者会话结束的时候，表中的数据自动删除
|
SQL> create global temporary table tmp_table1 (tid number,tname varchar2(20)) on commit delete rows; |
|
SQL> insert into tmp_table1 values (1,'Tom'); |
|
SQL> select * from tmp_table1; |
|
SQL> commit;【commit后，数据会消失】 |
|
SQL> desc tmp_table1; |
行转列
n wm_concat(varchar2) 组函数
|
SQL> select deptno,wm_concat(ename) namelist from emp group by deptno; |
【oracle的分页】
n 找到员工表中工资最高的前三名
|
select e.ename,e.sal from (select * from emp order by sal desc) e where rownum <=3 |
|
n 分页【这样写是不对的】，rownum不能使用> >= select e.ename,e.sal from (select * from emp order by sal desc) e where rownum >=3 and rownum <=6 n 分页的一种正确写法 select * from (select rownum r,e.* from (select * from emp order by sal) e where rownum <=6) where r>3 |
数据处理
SQL 的类型
n 1. DML（data manipulation Language 数据操作语言）: insert update delete select
n 2. DDL(Data Definition Language 数据定义语言):
create table,alter table,drop table,truncate table
create/drop view,sequence(序列),index,synonym(同义词)
n 3. DCL(Data Control Language 数据控制语言): grant(授权) revoke（撤销权限）
例子
|
PreparedStatement 预处理 |
insert into emp (empno,ename,sal,deptno) values (?,?,?,?); |
|
地址符 & |
SQL>insert into emp (empno,ename,sal,deptno) values (&empno,&ename,&sal,&deptno)
SQL> select empno,ename,sal,&t from emp;
SQL> select * from &t;
|
|
批处理 |
复制表结构 SQL> create table emp10 as select * from emp where 1=2; 一次性将emp中，所有10号部门的员工插入到emp10中 SQL> insert into emp10 select * from emp where deptno=10; |
|
delete和truncate的区别: |
1. delete逐条删除；truncate先摧毁表，再重建 2.(根本)delete是DML（可以回滚），truncate是DDL（不可以回滚） 3. delete不会释放空间 truncate会 4. delete可以闪回 (flashback), truncate不可以 5. delete会产生碎片；truncate不会 |
|
SQL> set feedback off SQL> @C:\课程\JavaEE\oracle【3天】\资料\testdelete.sql SQL> select count(*) from testdelete; SQL> set timing on SQL> delete from testdelete; SQL> drop table testdelete purge; SQL> @C:\课程\JavaEE\oracle【3天】\资料\testdelete.sql SQL> truncate table testdelete; SQL> set feedback on SQL> set timing off
|
|
|
事务的标致 |
1. 起始标志：事务中第一条DML语句 2. 结束标志：提交： 显式 commit 隐式 正常退出（exit），DDL，DCL 回滚: 显式 rollback 隐式 非正常退出，掉电，宕机 |
|
保存点 |
SQL> create table testsavepoint (tid number, tname varchar2(20)); SQL> insert into testsavepoint values(1,'Tom'); SQL> insert into testsavepoint values(2,'Mary'); SQL> savepoint a; SQL> insert into testsavepoint values(3,'Maake'); SQL> select * from testsavepoint; SQL> rollback to savepoint a; SQL> select * from testsavepoint; SQL> commit; SQL> set transaction read only; SQL> select * from testsavepoint; SQL>insert into testsavepoint values(3,'Maake');【不能在 READ ONLY 事务处理中执行插入/删除/更新操作 SQL> rollback;
|
创建和管理表
|
表创建 |
SQL> create table test3(tid number,tname varchar2(20),hiredate date default sysdate); SQL> insert into test3(tid,tname) values(1,'Tom'); SQL> select * from test3; |
|
|
创建表，保存20号部门的员工 SQL> create table emp20 as select * from emp where deptno=20; |
|
|
创建表，员工号 姓名 月薪 年薪 部门名称 SQL> create table empinfo as select e.empno,e.ename,e.sal,e.sal*12 annsal,d.dname from emp e,dept d where e.deptno=d.deptno |
|
|
添加表字段 SQL> alter table test3 add photo blob; 修改表字段 SQL> alter table test3 modify tname varchar2(40); 删除表字段 SQL> alter table test3 drop column photo; 重命名表字段 SQL> alter table test3 rename column tname to username; 改表名 SQL> rename test3 to test5; 删除表 SQL> drop table test5
显示回收站 SQL> show recyclebin
清空回收站 SQL> purge recyclebin; 删除的表，可以从回收站查询 SQL> drop table TESTSAVEPOINT; SQL> select * from "BIN$ULtYrrqvTlqfr+qeSXIHNA==$0"; 闪回删除 SQL> flashback table TESTSAVEPOINT to before drop; 注意：管理员没有回收站sys |
|
|
创建约束表字段 SQL>create table test5 (tid number, tname varchar2(20), gender varchar2(2) check (gender in ('男','女')), sal number check(sal > 0) ) SQL> insert into test5 values(1,'Jone','男',1000); SQL> insert into test5 values(1,'Jone','A',1000);【违反检查约束条件 (SCOTT.SYS_C005420)】 SQL> insert into test5 values(1,'Jone','男',-10); |
|
|
外键关联 SQL>create table student (sid number constraint student_pk primary key, sname varchar2(20) not null, deptno number constraint student_fk references dept(deptno)) SQL> insert into student values (1,'Jone',10);
|
视图
常见的数据库对象
|
对象 |
描述 |
|
表 |
基本的数据存储集合，由行和列组成 |
|
视图 |
从表中抽出的逻辑上的相关的数据集合 |
|
序列 |
提供有规律的数值 |
|
索引 |
提搞查询的效率 |
|
同义词 |
给对象起别名 |
视图
n 视图是一张虚表
n 视图建立在已有表的基础，视图赖以建立的这些表称为基表
n 向视图提供数据的内容的语句为select语句，可以将视图理解为存储起来的select语句
n 视图是向用户提供基表数据的另一种形式
视图的优点
n 限制数据访问
n 简化复杂查询
n 提供数据的相互独立
n 同样的数据可以有不同的显示方式
n 但视图不能提高性能
ora-01034 oracle not acailable
|
添加权限 |
在虚拟机里以管理员登录，并授权scott的视图权限 SQL> sqlplus sys/manager as sysdba SQL> grant create view to scott |
|
创建视图 |
SQL>create view empinfoview as select e.empno,e.ename,e.sal,e.sal*12 annsal,d.dname from emp e,dept d where e.deptno=d.deptno |
|
查看视图结构 |
SQL> desc empinfoview; |
|
查询视图 |
SQL> select * from empinfoview; |
|
只读视图 |
SQL>create or replace view empinfoview as select e.empno,e.ename,e.sal,e.sal*12 annsal,d.dname from emp e,dept d where e.deptno=d.deptno with read only |
|
|
|
序列
序列
n 可供多个用户用来产生唯一数值的数据库对象
n 自动提供惟一的数值
n 共享对象
n 主要用于提供主键
n 将序列值装入内存可以提高访问效率
创建序列
|
|
SQL> create sequence myseq; #创建一个序列 |
|
|
SQL> create table testseq(tid number,tname varchar2(20)); |
|
|
SQL> select myseq.nextval from dual; #序列增1 |
|
|
SQL> select myseq.currval from dual; #查询当前序列 |
|
|
SQL> insert into testseq values(myseq.nextval,'aa'); SQL> insert into testseq values(myseq.nextval,'aa'); SQL> insert into testseq values(myseq.nextval,'aa'); SQL> slect * from testseq; SQL> commit; SQL> insert into testseq values(myseq.nextval,'aa'); SQL>rollback |
索引
索引
n 一种独立于表的模式对象，可以存储在与表不同的磁盘或者表空间
n 索引被删除或者损坏，不会对表产生影响，其影响只是查表的速度
n 索引一旦被建立，oracle管理系统会对其进行自动维护，而且由oracle管理系统决定何时使用索引，用户不用在查询查询语句中指定使用哪个索引
n 在删除一个表时，所有基于该表的索引会自动被删除
n 通过指针加速oracle服务器的查询速度
n 通过快速定位的方法，减少磁盘的I/O
索引可以提高查询速度，我们只需要创建索引，至于怎么使用索引，是oracle系统内部的事情
什么情况下创建索引
n 列中数据值分布很广
n 列经常在where语句或连接条件中出现
n 表经常被访问，而且量很大，访问的数据大概占总量的2%~4%
什么情况下不要创建索引
n 表很小
n 经常更新
n 列不经常在where语句或连接条件中出现
创建索引
|
SQL> create index myindex on emp(); |
同义词
同义词 取别名
n 使用同义词访问其它对象
n 方便访问其它用户的对象
n 缩短对象名字的长度
n 创建同义词需要授权grant create synonym to scott
使用
|
|
SQL> create synonym myemp for emp; SQL> select * from myemp; |
|
|
SQL> create synonym hremp for hr.employees; SQL> grant select on hr.employees to scott【需要授权才能访问其它用户的表】 SQL> select count(*) from hr.employees; SQL> select count(*) from hremp; |
