Oracle数据库基础知识点大全
- sqlplus – 自带工具
- regedit – 注册表
管理 服务
- net start OracleOraDb11g_home1TNSListener --监听服务
- net start OracleServiceORCL —基础服务
登录管理员
- 锁定用户 alter user 用户名 account lock;
- 解锁 alter user 用户名 account unlock;
- 修改用户密码 alter user 用户名 identified by 密码;（没有密码找回）
一.数据库基本概念
- 数据库（Datebase,DB)
- 数据库管理系统（DATabase Management System, DBMS)
- 数据库管理员（Database Administrator，DBA)
- 数据库系统（Database System，DBS)
- 关系型数据库（Relationship Database，RDB)
- 关系型数据库管理系统（RDBMS)
E-R模型
E-R模型三要素：实体，关系，属性
E-R图三要素：
- 实体：用矩形框表示
- 属性：用椭圆形表示，并用连线与实体联系在一起。
- 实体间联系：用菱形框表示，并用连线分别与相关实体相连，且需要在连线上注明联系类型。
实体间联系类型：
- 一对一
- 一对多
- 多对多
数据库对象
- 表
- 视图
- 约束条件
- 索引
- 序列
- 存储过程
- 函数
- 触发器
- 包
二：标准SQL包含了4种基本的语句类别
- DDL语句，数据定义语句，主要用来定义数据库，表名，字段，例如create,drop,alter.
- DML语句，数据操作语句，用来对数据记录的增删改查，还用来保证数据的一致性。主要有select，delete，insert，update语句。
- DCL语句，数据控制语句，用于控制不同数据对象访问级别的语句。定义了数据库、表、表、用户的访问权限和完全级别。常用的语句包括grant、revoke等
- TCL语句，事务控制语句，用来确保事务的特性。commit，rollback，savepoint
三：DML语句（数据操作语句）
1.查询emp表中的所有数据
select *from emp
查询emp表中的指定数据
select empno,ename from emp
2.添加emp表中的所有数据
insert into emp values(1111,'tom','抓老鼠',2222,to_date('1996/12/25', 'yyyy-mm-dd'),15000,0,40)
添加emp表中的指定数据
insert into emp (empno,ename) values(1112,'tomo');
---注意：主键不能为空；主键有唯一约束，不能重复；
3.修改emp表中的数据
update emp set empno=2222,ename='王首惟' where empno=1111 ---一般选择的条件是主键
4.删除emp表中的数据
delete from emp where empno=1111
清空表数据；事务不是自动提交的
delete from emp
清空表数据：事务是自动提交的
truncate table employee;
5.复制表数据
表的结构 得一致
create table employee
(
empno number(4) not null primary key,
ename varchar2(10) ,
job varchar2(10),
mgr number(4),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2)
);
复制表数据
insert into employee (empno,ename) (select empno,ename from emp where deptno!=40)
insert into employee (select * from emp )
6.合并 表数据 merge
merge into employee e1
using emp e2
on (e1.empno=e2.empno)
when matched then ---条件是真
update set e1.job=e2.job ,
e1.hiredate=e2.hiredate
when not matched then -----条件是假
insert values(e2.empno,e2.ename,e2.job,e2.mgr,e2.hiredate,e2.sal,e2.comm,e2.deptno)
7.开启事务的地方
update emp set sal =sal -1000 where empno=1024;
savepoint emp1;
update emp set sal=sal+1000 where empno=8888;
rollback to emp1;
commit ;
8.只读事务 必须放在 事务开启的第一行
set transaction read only ;
update emp set sal =sal -1000 where empno=1024;
数字和日期使用的数学表达式
| 操作符 | 描述 |
|---|---|
| * | 乘 |
| / | 除 |
| + | 加 |
| - | 减 |
数学运算一样（先乘除再加减，先计算括号里边的 ）
1.查询当前用户下的所有 表 对象
select *from user_tables
2.虚拟表 dual
select 1+3 from dual;
select 1/3 from dual;
select 1/(3+1) from dual;
select 1/3,EMP.* from emp;
3.空值问题：空值是无效的，未指定的，未知的或不可预知的值。空值不是空格或者0。空值参与的表达式 ，结果一定为空 （需要后期的函数 解决）
每个员工的总工资
select sal+COMM from emp;
select COMM from emp;
4.列的别名：重命名一个列，便于计算，as可加可不加。
select sal+COMM aaa from emp;
select sal+COMM as 总工资 from emp;
5. " " 不是字符串，列的别名使用 " " 可以包含特殊符号 区分 内容的大小写
select sal+COMM "总工资 aaa" from emp;
6. ' ' 表示字符串
select '1' from emp;
7.|| 拼接 我是 ？ 我的工作是 ？ 我的薪资是?
select '我是'|| ename ||'，我的工作是' || job ||'，我的薪资是'||sal as 个人信息 from emp;
去除重复行distinct
1.重复行
select deptno from emp ;
去除重复行 distinct
select distinct deptno from emp
常用 where 查询条件
| where 条件 | 解释 |
|---|---|
| > < <= >= <> | 大于、小于、大于（小于）等于、不等于 |
| between…and… | 在某一区间，闭区间，效果同 and |
| in(set) | 在 in 列表中的值，如 in(100, 200)，效果同 or |
| like 通配符 | 模糊查询：% 匹配多个字符，如 first_name like “a%”；_ 匹配一个字符，如 first_name like “a_”。 |
| is null | 为空 |
| is not null | 不为空 |
| and | 同时成立 |
| or | 任一成立 |
| not | 不成立，如 where not(salary > 100); |
1.where子句 '10' 可以表示 数字10
select *from emp where deptno=10
select *from emp where deptno='10'
2.字符大小写敏感
select *from emp where job='CLERK'
select *from emp where job='clerk'
3.日期 工具--首选项 --时间格式 nfl-option oracle 格式 ‘01-1月-1982’
select * from emp where hiredate='17-12月-1980'
4. and逻辑与 or逻辑或
查询 job 是CLERK 并且 sal 大于 1000
select *from emp where job='CLERK' and sal>1000
查询 job 是CLERK 或者 sal 大于 1000 且部门编号是20号部门
select *from emp where job='CLERK' or sal>1000 and deptno=20
5. > >= < <= <> !=
日期大于 81 年 sal大于 2000 不是 20号部门了
select *from emp where hiredate>'01-1月-1981' and sal>2000 and deptno<>20
select *from emp where hiredate>'01-1月-1981' and sal>2000 and deptno!=20
6. between 值1 and 值2 [ 值1, 值2] 等价于 >= and <= 值1< = 值2
sal 1000-3000之间
select *from emp where sal>=1500 and sal<=3000
select *from emp where sal between 1500 and 3000;
select *from emp where sal not between 1500 and 3000;
7.in (值列表) 等值 比较 等价 or
sal 800 or 1000 or 3000
select *from emp where sal=800 or sal=1000 or sal=3000
select *from emp where sal in(800,1000,3000)
8.like 模糊查询 % [0-∞ ) _ [1] 占位符
select *from emp where ename like 'S%'
select *from emp where ename like '%T'
select *from emp where ename like '%S%'
第二位 D
select *from emp where ename like '_D%'
倒数第二位 D
select *from emp where ename like '%T_'
9.--- is null 是否是 空
select *from emp where mgr is null
10.not ....
--- and or not
not between ... and ...
select *from emp where sal not between 1500 and 3000;
not in (1,2,3)
select *from emp where sal not in(3000,5000 );
not like
select *from emp where ename not like 'S%'
is not null
11.查询 有奖金
select *from emp where comm is not null and comm<>0
order by 子句 排序子句 默认升序 ASC DESC 降序
1.order by 排序的是 返回的所有数据
select *from emp order by sal
select empno,ename,sal 工资 from emp order by 工资
注意别名 不能直接使用在 自己的 where 子句中
--select empno,ename,sal 工资 from emp where 工资 >1000
select empno ,ename,sal from emp where sal>1000 order by sal
select empno ,ename,sal from emp where sal>1000 order by sal desc
根据多个条件排序
select * from emp order by empno, sal desc
字符函数
| 字符 | 解释 |
|---|---|
| lower | 转小写 |
| upper | 转大写 |
| initcap | 首字母大写 |
| concat | 拼接 |
| substr | 截取 |
| length | 长度 |
| instr | 返回下标 |
| lpad | 左补 |
| rpad | 右补 |
| trim | 去除两边的字符 |
| replace | 替换 |
1.大小写控制函数
select lower(ename) from emp where deptno=10;
select upper('zhaoxiaogang') from dual;
select *from emp where job=upper('clerk');
select initcap('shi hao chen') from dual
2.拼接 concat
select concat('姓名'，ename) from emp;
3.截取 substr
下标从1开始，从第2位开始截取，3代表从二开始截取几个字符
select substr ('仙剑奇侠传',2,3) from dual
从第 3位开始 后面所有
select substr ('仙剑奇侠传',3) from dual
4.length 长度
select length('仙剑奇侠传') from dual;
嵌套使用
select substr('仙剑奇侠传',2,length('你好')) from dual
5.instr 返回 指定字符第一次出现的下标
select instr('wangyuanming','g') from dual;
instr 返回 指定字符第一次出现的下标 指定从 第3位开始
select instr('wangyuanming','g',5) from dual;
6.左补 lpad rpad
select lpad('李逍遥',8,'灵') from dual;
select rpad('李逍遥',8,'灵') from dual;
7.trim 去除左右两边的指定字符
select trim(' ' from ' 赵灵儿 ' ) from dual（去除两边的空格）
select trim('w' from 'ww赵灵儿www' ) from dual
8.replace 替换
select replace('wangshouwei','shouwei','yuanming') from dual;
数字函数
| 函数 | 解释 |
|---|---|
| round | 四舍五入 |
| trunc | 截断 |
| mod | 求余 |
| ceil | 向上取整 |
| floor | 向下取整 |
1.四舍五入 round
select round(66.66) from dual; （默认到各位）
select round(66.66,0) from dual;（四舍五入到个位，默认）
select round(66.66,1) from dual;（四舍五入到十分位，默认）
select round(66.66,-1) from dual;（四舍五入到十位，默认）
2.截断 trunc
select trunc(66.66) from dual（同上理）
select trunc(66.66,1) from dual
select trunc(66.66,-1) from dual
3.取整 ceil floor
select ceil(63.01) from dual;
select floor(63.99) from dual;
4.mod 求余
select mod(12,0) from dual;
select mod(12,11) from dual;
select mod(11,12) from dual;
日期函数
| 函数 | 解释 |
|---|---|
| sysdate | 当前时间 |
| months_between | 两个日期相差的月数 |
| add_months | 指定日期中加上若干月数 |
| next_day | 指定日期的下一个日期 |
| last_day | 本月的最后一天 |
| round | 日期四舍五入 |
| trunc | 日期截断 |
1.当前时间
select sysdate from dual
员工入职 年限 日期相减 间隔的天数
select hiredate, (sysdate-hiredate)/365, floor((sysdate-hiredate)/365) from emp;
months_between() 日期间隔的 月数
select sysdate,months_between(sysdate,hiredate)/12,(sysdate-hiredate)/365 from emp
add_months() 指定日期中加上若干月数
select add_months('01-12月-2002',1) from dual;
next_day() 表示下一个日期 （那一天 周几） [周天-周六]--[1-7]
select next_day(sysdate,4) from dual;
last_day() 本月的最后一天
select last_day(sysdate) from dual;
转化函数
| 函数 | 解释 |
|---|---|
| to_date | 将字符串转化成date |
| to_char | 1.将date转化成char 2.将number转化成char |
| to_number | 将char转化成number |
1.to_date 将字符串 转成date
select to_date('2019-11-20','yyyy-mm-dd') from dual; -- mm MM 都表示月份
select to_date('2019-5月-20','yyyy-mon-dd') from dual; --- 5月 mon month
select to_date('2019-05-20 11:23:13 ','yyyy-mm-dd hh:MI:ss') from dual; --12 小时制
select to_date('2019-05-20 13:23:13','yyyy-mm-dd hh24:MI:ss') from dual; --24 小时制
2. to_char date--char
select to_char(sysdate,'yyyy"年"mm"月"dd"日"') from dual -- 格式中 “” 里面的内容原样输出 不参与转化
3.to_char number--char
select to_char(123.00 ,'999,999.999') from dual ---9 格式 数值不够 补空格 小数点后 补 0
select to_char(-123.00,'000,000.000')from dual -- -0格式 数值不够 补0 小数点后 补 0
4.to_number 将字符串转成数字
select to_number('123.66' ,'000.000') from dual;
select to_number('123.66' ,'999,999.999') from dual;
5.二月份入职的人
select * from emp where to_char(hiredate,'mm')='02';
6.员工工资
select to_char(123.00,'L99,999.000')from emp
select to_char(123.00,'$99,999.000')from emp
通用函数
| 函数 | 解释 |
|---|---|
| nvl(expr1,expr2) | 如果表达式1为空，则返回表达式2，否则返回表达式1 |
| nvl2(expr1,expr2,expr3) | 如果表达式1为空，则返回表达式3，否则返回表达式2 |
| nullif(expr1,expr2) | 判断两个值是否相等，相等则返回空，不相等则返回表达式1 |
| coalesce(expr1,expr2,…exprn) | 返回第一个不为空的值 |
1. nvl (1,2) 表达式1 为空 则返回表达式2 否则 返回表达式1
总工资
select sal+comm 总工资 from emp;
select sal+nvl(comm,0) 总工资 from emp;
2.nvl2 (1,2,3) 表达式1 为空 返回表达式 3 否则是 2
select nvl2(comm,'不为空','为空') from emp
select nvl2(comm,sal+comm,sal) from emp
3.nullif 判断两个值是否相等 相等 返回空 如果不相等 返回表达式1 数据类型 得一致
select nullif('123','1231') ,nullif('1','2'),nullif('',null) from dual;
4.coalesce 返回第一个不为空的值
select coalesce(1,2,3,3),coalesce(null,0,1) from dual;
表达式判定
-
在SQL语句中使用if-then-else 逻辑
-
有两种方法
1）case表达式
2）decode函数1. if else case 表达式 when 值1 then when 值1 then else end 2. 等值判定 select case 4 when 1 then '壹' when 2 then '贰' when 3 then '叁' else '没钱' end 别名 from dual; 3.select ename, case job when 'CLERK' then '办事员' when 'SALESMAN' then '销售' when 'MANAGER' then '经理' when 'ANALYST' then '分析师' when 'PRESIDENT' then '董事长' end 具体工作,hiredate from emp; 4. 不等值 select case when sal<1000 then '屌丝' when sal<2000 then '穷鬼' when sal<3000 then '小康' else '额滴神啊' end 困 from emp; 5.工资>2000*0.8 >3000*0.7 >4000*0.6 其他不扣,实际月薪（税后 包含奖金） select ename,sal,comm, case when sal>2000 then sal*0.8+nvl(comm,0) when sal>3000 then sal*0.7+nvl(comm,0) when sal>4000 then sal*0.6+nvl(comm,0) else sal+nvl(comm,0) end 实际月薪 from emp 6.decode(表达式， 值1，返回值1 ，值2 ，返回值2 ...,返回值n(默认返回值) ) --只能做等值 select emp.* , decode(deptno,10,'财务部',20,'销售部',30,'技术部') from emp select emp.* , decode(deptno,10,'财务部',20,'销售部','没人管') from emp
四：RDBMS：关系型数据库管理系统
关系表设计原则
三范式
- 第一范式：列不可分割
- 第二范式：必须有主键，不能存在部分依赖
- 第三范式：不能存在传递依赖：属性不能依赖与其他非主键属性，非主键字段之间，不应有依赖关系
多表查询
注意：表的别名不能使用 as
笛卡尔集现象
1.笛卡尔集现象
select *from emp ,dept
避免笛卡尔集现象 使用 where 子句过滤
select *from emp ,dept where emp.deptno=dept.deptno
| 关键字 | 等同关键字 | 描述 |
|---|---|---|
| join | inner join | 两个表匹配查询的交集 |
| left join | left outer join | 以左表为基础，右表进行匹配（若无则补空） |
| right join | right outer join | 以右表为基础，左表进行匹配（若无则补空） |
| full join | full outer join | 左右表相互匹配（并集）（若无则补空） |
等值连接 = 自然连接 = 内连接
select *from emp ,dept where emp.deptno=dept.deptno
自然连接 natural join
select *from emp natural join dept
on
-
内连接
select *from emp inner join dept on emp.deptno=dept.deptno select *from emp join dept on emp.deptno=dept.deptno -
外连接
select *from emp left join dept on emp.deptno=dept.deptno select *from emp right join dept on emp.deptno=dept.deptno select *from emp full join dept on emp.deptno=dept.deptno
using
-
内连接
select *from emp inner join dept using(deptno) select *from emp join dept using(deptno) -
外连接
select *from emp left join dept using(deptno) select *from emp right join dept using(deptno) select *from emp full join dept using(deptno)
外连接:
可以返回不满足条件的数据 （主表的数据全部返回 从表如果唯有匹配 补null
-
外连接一： (+) 表示自己是从表，对面是主表
select *from emp ,dept where emp.deptno(+)=dept.deptno select *from emp ,dept where emp.deptno=dept.deptno(+) -
外连接二：cross join
select *from emp cross join dept where emp.deptno=dept.deptno
自连接
自己连接自己 一张表
select e1.*,e2.* from emp e1,emp e2
返回所有员工 找自己的领导 即使没有管理者
select *from emp e1 ,emp e2 where e1.mgr=e2.empno
select *from emp e1 left join emp e2 on e1.mgr=e2.empno
select *from emp e1 ,emp e2 where e1.mgr=e2.empno(+)
组函数
| 统计函数 | 解释 |
|---|---|
| count() | 求总数 |
| avg() | 求平均数 |
| sum() | 求和 |
| max() | 求最大值 |
| min() | 求最小值 |
select min(sal),max(sal),avg(sal),sum(sal)/count(*) from emp;
select sum(comm) from emp; --- 空值不参与组函数运算
select avg(comm) from emp;--- 空值不参与组函数运算
count --计数
select count(comm) from emp;
* 表示有多少条数据
select count(*) from emp;
select count(empno) from emp;
有多少人有管理者
select count(mgr) from emp;
统计一下员工有多少个部门
select count(distinct deptno) from emp
group by 条件 分组条件
1.部门编号进行分组 组函数 会返回 空 组
select deptno from emp group by deptno
2.根据多个条件分组
select deptno,sal,count(*) from emp group by deptno,sal
3.计算每个部门的工资和
select deptno,sum(sal) ,min(sal),max(sal),avg(sal) ,count(*) from emp group by deptno
4.不是10号部门 部门编号 和部门人数 按照部门编号排序
select deptno,count(*) from emp where deptno<>10 group by deptno
不能在where子句中使用组函数，可以在having子句中使用
需要使用组函数作为过滤条件 having 子句
不是10号部门，部门编号和部门人数，按照部门编号排序 ，部门人数必须大于5
select deptno,count(*） from emp where deptno<>10 group by deptno having count(*)>5
注意：where having 都是过滤子句 结果集还没有返回 不能使用结果集的别名
--- 查询每个部门的人数，部门 人数的工资和 工资和大于9000 的部门编号
select deptno,count(*),sum(sal) from emp group by deptno having sum(sal)>9000 ;
select count(*) ,sum(sal),emp.deptno,dname from emp ,dept where emp.deptno=dept.deptno group by emp.deptno,dname having sum(sal)>9000
查询 员工表中的每个部门 相同职位的人数 当前职位的工资和 工资和大于5000 的职位和部门编号
select deptno,job ,sum(sal+nvl(comm,0)),count(*) from emp group by deptno,job having sum(sal+nvl(comm,0))>5000
子查询
子查询 把一个查询结果集 当做另外一个要查询的对象（表）
查询比KING 晚的人
select hiredate from emp where ename='KING' ----先查询king的入职年份
select *from emp where hiredate>(select hiredate from emp where ename='KING')
in any all
in 等值判定
select *from emp where sal in (1600,5000)
select *from emp where sal =1600 or sal=5000
select *from emp where sal in (select sal from emp where deptno=10)
any 大于 任意一个值
select *from emp where sal>=any (1600,5000)
all 大于所有的值
select *from emp where sal>=all (1600,5000)
查询比10 号部门任意一个员工工 资高的员工信息
select *from emp where sal >any (select sal from emp where deptno=10)
查询与10号部门同一年入职的员工 但是不要 10号部门
select to_char(hiredate,'yyyy') from emp where deptno=10 ---先查询10号部门员工的入职年份
select *from emp where to_char(hiredate,'yyyy') in ( select to_char(hiredate,'yyyy') from emp where deptno=10) and deptno<>10
子查询 +分组
查询员工信息 每个部门 下最低工资的员工信息
select *from emp , ( select min(sal) ms,deptno from emp group by deptno ) e1 where e1.deptno=emp.deptno and e1.ms=emp.sal
with 子句
with
res as (select min(sal) ms,deptno from emp group by deptno) ----声明一张 瞬时表 临时表
select *from emp ,res where res.deptno=emp.deptno and res.ms=emp.sal and res.ms>900
with
copyEmp as (select *from emp)
select *from copyEmp;
with
copyEmp as (select *from emp)
select *from emp ,copyEmp where emp.mgr=copyEmp.empno ;
Oracle分页语句
rowid 当前查询结果集的 每一个对象的唯一标识
select emp.*,rowid from emp
rownum 当前查询结果集 中 每一行的行号 始终从1开始 到 count(*) 结束
select emp.*,rownum from emp
rownum > >= = 返回均是空值 只能使用 < <=
oracle 分页 必须使用子查询 rownum 起别名
-
查询6到10页的员工信息，效率要 高很多 推荐使用
select *from (select emp.*,rownum r from emp where rownum<=10 ) e where e.r>5 -
查询6到10页的员工信息，效率低 不建议 使用
select *from (select emp.*,rownum r from emp ) e where e.r>5 and e.r<=10 select *from (select emp.*,rownum r from emp ) e where e.r between 6 and 10 -
按照工资查询6到10页的员工信息，先排序 后生成 rownum
select *from (select e1.*,rownum r from (select *from emp order by sal asc) e1 where rownum<=10)e2 where e2.r>5; select *from (select e1.*,rownum r from (select *from emp order by sal ) e1) e where e.r>5 and e.r<=10
事务和锁
事务的ACID特性
- 原子性：一个事务要么全部执行，要么全部不执行
- 一致性：数据库的事务在开始前和结束后都应该是一致的
- 隔离性：一个事务不能看到另一个没有提交的事务的执行结果
- 持久性：一旦事务成功，数据的修改是永久的
Oracle数据库中，锁是：
- 并行事务中避免资源竞争
- 避免用户动作
- 在事务结束结束前存在
死锁
- 两个会话互相等待对方释放锁才能进行下去，这种情况就是死锁。
五：DDL(数据定义语言)
- 数据定义语言
- 作用于创建，修改，删除，管理数据库对象
- 会自动提交当前事务
表管理
表名和列名的命名规则
- 必须以字母开头
- 必须在1-30字符之间
- 只能包含数字，字母，下划线，$和#
- 不能和用户定义的其他对象重名
- 不能是Oracle保留字
创建表
create table mytable
(
myid number(4) not null primary key,---- 主键 并且不为空
myname varchar2(10) not null,
mybirthday date default sysdate --- default 表示默认值 如果没有指定值 就是默认值 sysdate
)
删除表
drop table mytable
修改表名
rename mytable to userInfo
alert table 语句
-
追加一个新的列
alter table userInfo add sex varchar2(2) default '女' --添加一个新列 默认值 女 -
修改现在有的列
alter table userInfo modify myname varchar2(5) -
删除一个列
alter table userInfo drop column sex
清空表数据：事务是自动提交的
truncate table 表名;
常用约束类型Oracle
| 约束类型 | 解释 |
|---|---|
| not null | 非空约束，设定的内容在添加数据的时候不能是 null 的 |
| unique | 唯一约束，值是唯一的 |
| primary key | 主键约束，非空唯一 |
| foreign key | 外键约束，B 表使用 A 表的主键 |
| check | 检查约束 |
not null 约束 不能为空
主键约束 primary key
--SYS_C009677 不指定名字系统默认分配主键，不能为null.唯一 等价于二个约束 not null，unique
create table myTable(
myid number(2) primary key ,
myname varchar2(10) ,
birthday date default sysdate
)
create table myTable(
myid number(2) constraint my_key primary key , --- constraint 给主键起名字
myname varchar2(10) ,
birthday date default sysdatemy_key
)
create table myTable(
myid number(2) not null ,
myname varchar2(10) ,
birthday date default sysdate,
constraint my_key primary key (myid) --- constraint 给主键起名字
)
唯一约束unique 唯一的
create table stu_table(
sid number(2),
sname varchar2(10) unique ,
stuaccount varchar2(2) constraint stuaccount_unique unique
)
检查约束check
create table student(
sid number(2) primary key ,
sname varchar2(10) ,
sex varchar2(2) constraint sex_checked check (sex='男' or sex='女'),
sage number(3) check (sage=1 or sage=2)
)
外键约束foreign key
class_table(cid)必须是主键
alter table student add foreign key (cid) references class_table(cid)
删除外键所在表的主键 数据是有关联 默认选择 no-action
--级联删除
alter table student add constraint FK_CID foreign key (cid) references class_table(cid);---class_table(cid)
直接将外键所在的行数据 设置为 null
alter table student add constraint fk_cid foreign key (cid) references class_table (cid) on delete set null;
假删除
alter table student add flag number(1)
select *from student where flag=1
删除外键 约束
alter table student drop constraint FK_CID
约束失效
alter table student disable constraint FK_CID
激活约束
alter table student enable constraint FK_CID
六：视图
为什么使用视图
- 控制数据访问（DML)
- 简化查询
- 数据独立性
- 避免重复访问相同的数据
dba 开放权限 grant 权限 to 用户
登录超级管理员给scott开放视图权限
grant create view to scott;
创建视图 --- or replace 如果视图存在 就替换
create or replace view myView as select *from emp;
视图使用起来和表的操作思路 完全一致
使用 DML 语言 操纵 视图数据 ----原数据 也会改变
update myView set ename='强弟' where empno=8888
insert into myView(empno,ename) values(9999,'攻城狮')
无法同时修改 多个基表的 信息
update emp_dept set deptno=10, loc='China' where empno=8888
只读视图 控制 DML 语句进行 数据操作
create or replace view emp_dept as
select empno ,ename,job,emp.deptno,dname,loc from emp,dept where emp.deptno=dept.deptno and mgr is not null
with read only
无法对只读视图进行DML 操作
update emp_dept set deptno=10 where empno=8888
删除 视图
drop view emp_dept
七：序列
什么是序列
- 自动提供唯一的数值
- 共享对象
- 主要用于提供主键值
- 代替应用代码
- 将序列值装入内存可以提高访问效率
序列 — 生成主键 不能回退 不可逆性
create sequence my_num --- 序列名
increment by 1 --自增 1
minvalue 1
maxvalue 10
nocycle ---不循环
nocache --不缓存
select my_num.nextval from dual;
select my_num.currval from dual;
-
NEXTVAL：返回序列中下一个有效的值，任何用户都可以引用
-
CURRVAL: 中存放序列的当前值
-
NEXTVAL: 应在CURRVAL之前指定，二者应同时有效
create sequence my_num --- 序列名 increment by 2 --自增 1 minvalue 1 maxvalue 10 cycle ---循环 cache 5-- 缓存5 个数
删除序列
drop sequence my_num
八：索引
- 一种数据库对象
- 通过指针加速Oracle服务器的查询速度
- 通过快速定位数据的方法，减少磁盘I/O
- 索引与表相互独立
- Oracle服务器自动使用和维护索引
创建索引
create index emp_name on emp(ename)
适合创建索引的时机：
- 列值基数比较高（重复率比较低）
- 不能以空值做条件
- 列经常出现在条件子句中
- 表很大
- 经常做查询操作，而不是DML操作
九：用户和权限
- 创建新用户
- 删除用户
- 删除表
- 备份表
十：集合运算（了解）
-----集合运算 ----
select *from emp;
select *from employee;
---并集 去重
select empno ,ename from emp union select empno ,ename from employee; --去重
select empno ,ename from emp union all select empno ,ename from employee; ---- 不去重
----交集
select empno ,ename from emp intersect select empno ,ename from employee;
----相对补集 差集
select empno ,ename from emp minus select empno ,ename from employee; --- 左边 - 右边
select empno ,ename from employee minus select empno ,ename from emp;
4738
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
