Oracle数据库的学习笔记2

数据表之间的关联

集合操作：

union 去除重复，会先进行排序工作
select ename,sal,job
from emp where job=‘CLERK’
union
select ename,sal,job
from emp where job=‘MANAGER’;
**union all **
select ename,sal,job from emp where job=‘CLERK’
2 union all
3 select ename,sal,job
4 from emp where job=‘MANAGER’;
注意：两个查询列的数目必须一致
列的数据类型必须属于同一组
两个查询不能有order by子句
INTERSECT———>交集
select ename,sal
2 from emp
3 INTERSECT
4 select ename,sal
5 from emp where deptno=‘10’;
MINUS------>取的是差集
select ename,sal
2 from emp
3 MINUS
4 select ename,sal
5 from emp where deptno=10;

数据库的对象：

表：最基本的存储单元，由行和列组成
视图：由一张或多张表提取数据而组成的一张虚拟的表
索引：假如把一本书看作是一张表，那么这本书的目录就是索引，目的
主要是提升查询效率
序列：数字生成器，通常用来生成主键
同义词：相当于数据库其它对象的别名
语法：
create table [schema] 表名（
列名 数据类型 [default value1]
[column_constraint] [table_constraint],
…
)
规则：
<1>表名由字母，下划线$，#，数字组成
<2>表名只能以字母打头
<3>表名不能是数据库中的关键字
其中：schema 可以理解成是数据库用户中所有对象的一个集合，
它的名字与用户的名字相同。

其中：数据类型最常用的有如下几种：
<1> varchar2---->变长字符型
<2>char ---->定长字符型
<3>number(p,s) ->数值型
<4>date ---->日期型
<5>blob ---->存储二进制数据（4G）
<6>clob ----->文本数据（4G）

其中：default 用来定义列的默认值.当象表中插入数据时，假如
这一列没有值，就是默认值
其中：column_constraint称为列级约束
table_constraint 称为表级约束
约束：------>就是保证数据的完整性，而在某些列上加的一些限制条件

约束的类型：
not null ----->非空约束(只能定义在列一级约束）
primary key —>主键约束（非空且为一）
foreign key----->外键约束
check ------>检查约束
unique ------>唯一约束

列如：
create table emp 11(
empno number primary key,
ename varchar2(20) not null,
email varchar2(50) not null unique,
addr varchar2(200),
sal number(7,2) check(sal>3000)
);

create table bbs_user
(id number primary key,
username varchar2(10) not null,
userpass varchar2(20) not null,
email varchar2(50) not null unique,
userage number check(userage>0 and userage < 120),
addr varchar2(200)
);
删除用户，如果bbs_topic 中已经包含该用户发的帖子，那么
会报错。
create table bbs_user(
id number,
username varchar2(10),
userpass varchar2(10),
email varchar2(10),
constraint bbs_user_pk primary key(id)
);

create table bbs_topic(
2 id number,
3 title varchar2(10),
4 user_id number,
5 constraint bbs_topic_fk primary key(id),
6 constraint bbs_user_topic_fk foreign key(user_id)
7 references bbs_user(id)
8 );

or
on delete cascade 指的是删除用户，用户发的帖子也会全部被删除
create table bbs_topic(
2 id number,
3 title varchar2(10),
4 user_id number,
5 constraint bbs_topic_fk primary key(id),
6 constraint bbs_user_topic_fk foreign key(user_id)
7 references bbs_user(id) on delete cascade
8 );

*快速建表

语法：create table xxx[(column,column…)]
as subquery;
例如：
create table bbs_user_1 as
select * from bbs_user;

*修改表

<1>向表中添加字段
alter table bbs_user_2
add(email varchar2(20) not null);
<2>删除表中字段
alter table bbs_user_2
drop colunm email;
<3>修改表中字段
alter table bbs_user_2
modify nickname varchar2(30)
);
<4>重命名表中字段
alter table bbs_user_2
rename column address to addr
<5>重命名表
加粗样式rename bbs_user_2 to bbs_user_03;

*修改约束

alter table bbs_user_3 add constaint user_pk
primary key(id);
<2>删除约束
alter table emp_100 drop constraint em_n(约束的名字）；
<3>禁用约束
alter table emp_100 disable constraint sys_coo6514;
<4>启用约束
alter table emp_100 enable constraint sys_coo6514;

*删除表：

      drop table 表名

DML(数据库操作语言）—>操作的是数据库表中的数据
<1>向表中插入数据：
insert into emp_100(empno,ename,email)
values(1,‘bj’,‘bj’)
insert into emp_100 values(3,‘bj’,‘bj’,null,null,null)
insert into emp_100(empno,ename,email,phone,deptno,xxxx)
values(3,‘bj’,‘bj’,null,null,null)
多行数据的插入
insert into sales_reps(id,name,salary,commission_pct)
select employee_id,last_name,salary,commission_pct
from employees
where job_id like’%REP%’;
<2>修改表中数据
update emp_100 set ename = ‘baojian’,phone=123456
where empno = 3;
<3>删除表中数据
delete from emp_100;
delete from emp_100 where empno = 1;
truncat table emp_100;(数据一旦删除无法恢复)

*事物：(可以理解成由一个或多个sql语句组成的原子操作)；

<1>原子性：要么都成功要么都失败（比方说银行的转账）
<2>一致性：数据前后的总额不会发生变化
<3>隔离性：事物与事物之间不能相互影响
<4>持久性：数据一旦提交，将永久保存
commit:提交事物（正常退出 sqlplus 会自动提交缓冲区中的数据）
rollback：回滚事物
savepoint：设置回滚点（例如 savepoint A）
select depnto，dname from dept_10 where deptno = 20
for update
其中：for update 在读取这条数据的时候进行枷锁，防止他人对此条书进行
修改
允许别人去修改的时候，执行一下 commit；
关于事物操作我们在jdbc中更具体的体现

*序列

：序列被所有对象所共享，它每次都能产生一个唯一的值，这个值通常
用来生成表的主键

例如：

create sequence dept_seq
2 increment by 1
3 start with 1
4 maxvalue 9999
5 nocache
6 nocycle
7 ;
.nextval—>每次返回下一个可用的值
.currval---->永远返回当前的一个序列值.(前提是必须执行
过.nextval)
修改序列
alter sequence dept_deptid_seq
increment by 20
maxvalue by 999999
nocache
nocycle;
alter sequence emp_10_seq increment by 2;

删除序列：
drop sequence 序列名
例如：drop sequence emp_10_seq;

*视图：

视图是由一张或多张表抽取数据而组成的一个虚拟的表(
视图中不存储任何数据)，可以把视图理解成一个窗口，透过
这个窗口去看表中的数
作用：
<1>屏蔽对基表访问，提高系统的安全性
<2> 简化sql语句的编写
分类：

<1>简单视图
|–表的个数是1个
|–没有函数
|–没有group by
|–可以执行DML
<2> 复杂视图
|–表的个数可以是1个或多个
|–可以有函数
|–可以有group
|–不可以执行DML

语法:
<1>创建简单视图
create view deptvw
as select deptno,dname
from dept_120;
查看视图结构
desc deptvw
查询视图中数据
select* from deptvw
<2> with check option
create view deptvw2
as select deptno,dname
from dept_120
where detpno=2
with check option constraint dept_vw_check
注意：此时不能再执行 update deptvw2 set deptno=3 where
dname = ‘n2’;
但是可以执行 update deptvw2 set dname= ‘name2’ where
deptno = 2;
<3>with read only
<4>修改视图
<5>创建复杂视图：
<6>删除视图
drop view 视图名

*索引：---->数据库对象之一

假如你把一本书看作是一张表，那么这本书的目录就是索引
作用：提高查询效率，减少I/O操作
索引在查询中：提高查询效率，减少I/O操作
索引在排序中：提高排序效率，减少cpu运算
创建索引的方式：
例如：create index emp_in_name on emp_80(ename)

删除索引

drop index 索引名
其中：表和索引独立存在，删除表的同时，会删除索引

注意：经常作为条件字段且修改较少的字段要创建索引
索引的利弊：索引在查询中：提高查询效率，减少I/O操作
索引在排序中：提高排序效率，减少cpu运算
如果在某个列上创建了索引，那么这一列数据
的修改也会导致索引自动修改
查看sql的执行计划
explain plan for sql语句；
select * from table(DBMS_XPLAN.DISPLAY);

索引有一些失效情况 写sql语句的时候就要注意；
1.不要在索引列上做任何计算，函数，如果做了，索引会失效变成
全表扫表，性能会很差。
select ename, from emp where sal*12>3000;如果sal是索引列那么索引失效
2.模糊查询的时候以前配置通配符，那么索引列也会失效
3.在索引列上有自动的类型转换，索引也会失效

*同义词---->一个数据库对象
可以理解成是数据库对象的一个别名

DCL
<1>创建用户
create user 用户名 identified by 密码
<2>修改用户
alter user 用户名 identifide by 密码
用户解锁：1
alter user scott account unlock;