【Oracle】曾经的Oracle学习笔记（8-15）ER图，三大范式，数据库字典，视图，索引，序列

一、数据库建模

二、建表

三、数据库字典

四、DML语句

五、视图

六、索引

七、序列

八、DDL语句

 

Lesson 8 Overview of Data Modeling and Database Design
-----------------------------------------------------------------------------
数据库建模

E-R图

Entity 实体
对象实体
业务实体

Relationship 关系（这里指的是实体与实体之间）

ER图中:
# 唯一
* 非空

#* 唯一非空

o 没有任何约束

 

one-to-one
one-to-many

may be

must be

建表的时候要遵循的范式

第一范式:表中要一个可以做主键的字段(非空唯一)

 

第二范式：表中每一列的值都是不可分割的

第三范式：表中的某一个列不能依赖于其他非主键列（即非主键列的值得修改不会影响其他列上的值）

Lesson 9 Creating Tables
------------------------------------

建表

create table 表名(
列名1 数据类型 列级约束,
列名2 数据类型 列级约束,
列名3 数据类型 列级约束,
列名4 数据类型 列级约束
);

create table 表名(
列名1 数据类型 列级约束,
列名2 数据类型 列级约束,
列名3 数据类型 列级约束,
列名4 数据类型 列级约束,
表级约束1,
表级约束2
);

注:列级约束和表级约束都是可以不写的。
create table 表名(
列名1 数据类型,
列名2 数据类型,
列名3 数据类型,
列名4 数据类型
);

约束是指这个列上面的值要遵循什么样的要求。

主键约束
外键约束
唯一约束
非空约束
check约束

常用的数据类型:

number
varchar2
char
date

 

建表
create table student(
id number primary key,
name varchar2(200) not null,
age number,
birthday date
);

删除表
drop table 表名;
drop table student;

create table customer(
id number primary key,
name varchar2(20) not null,
age number,
email varchar2(100) unique,
gender char check(gender in('f','m'))
);

drop table customer;

 

constraint关键字
-------------------------
建表的时候可以给约束起一个名字,这个名字起的规律一般会是:表名_列名_约束名

如果没有给约束起名字,那么系统也会给这个约束起一个默认的名字,只不过这个默认的名字我们看了之后不知道它是哪张表里面的哪个列上面的什么约束。

create table customer(
id number constraint cus_id_pk primary key,
name varchar2(20) not null,
age number,
emile varchar2(100) unique,
gender char check(gender in('f','m'))
);

查看当前用户所创建的约束
select constraint_name
from user_constraints;

references关键字
-----------------------
外键的约束，引用某表的主键。

create table t_order(
id number primary key,
price number not null,
customer_id number references customer(id)
);

这个时候直接删除customer表会报错
因为这个表的主键id已经被t_order表引用过去做了外键
drop table customer;

1.可以先删除order表,再删除customer表
2.使用cascade constraints命令:
drop table customer cascade constraints;
这样可以把customer表直接删除掉,同时也把order表所声明的外键约束也删除了

使用表级约束:
create table customer(
id number,
name varchar2(20) not null,
age number,
email varchar2(100),
gender char,
constraint cus_id_pk primary key(id),
constraint cus_email_un unique(email),
constraint cus_gender_ck check(gender in('f','m'))
);

 

create table t_order(
id number,
price number not null,
customer_id number,
constraint order_id_pk primary key(id),
constraint order_cus_id_fk foreign key(customer_id) references customer(id)
);

注意:not null 约束只能声明为列级约束

1.表级约束和列级约束所写的位置不一样
2.not null约束不能用表级约束来声明
3.表级约束和列级约束声明语法稍有所不同
4.如果要声明的约束为联合主键、联合外键、联合唯一的时候,就一定要用表级约束

 

//声明的一个主键
//联合外键和联合唯一约束也是类似的写法.
create table t_order(
id number,
price number not null,
customer_id number,
constraint order_id_pk primary key(id,price),
constraint order_cus_id_fk foreign key(customer_id) references customer(id)
);

Lesson 10 Orcale Data Ditionary
-------------------------------------
数据库字典
作用：帮助用户了解当前数据库的一些信息或是对象的信息以及用户的信息

view 视图

USER 开头的视图里面存放着用户自己拥有的对象
ALL 开头的视图存放着用户有权限查看的对象
DBA 开头的视图存放着数据库所有的对象
V$ 开头的视图存放数据库运行的一些性能属性数据

DICTIONARY
TABLE_PRIVILEGES
IND

通过desc命令来了解这些视图中列的信息，然后再去查询混视图找到想要的信息

 

 

Lesson11 Manipulating Data
------------------------------------
DML语句

insert
update
delete
commit
savepoint
rollback

DML语句 :这个语句会修改数据库表中的数据
insert update delelte

create table student (
id number primary key,
name varchar2(20) not null,
age number
);

//这样表示向student表中全部插入数据
//插入数据的顺序是按照建表的时候列的顺序
insert into student values(1,'tom',20);

//这样是可以自己控制向表中那些列插入数据，以及插入数据的顺序
insert into student (id,name) values(2,'jerry');

 

DML语句执行的时候会产生事务,事务不提交,那么我对数据所做的修改都是不能生效的,
事务提交,那么之前对数据的修改就全部生效了(保存到硬盘上了)
事务回滚,那么之前对数据的修改全部撤销.

例如:
1 修改数据1
2 修改数据2
3 修改数据3
4 修改数据4
5 提交事务

在执行第一行DML语句的时候,就会产生了事务,之后的第2 3 4 行执行的DML也会放进第一行所产生的事务里面,然后第五事务一提交,那么这个事务里面的所有操作全部生效了.
如果是第五行是回滚了事务,那么这个事务里面的所有操作全部撤销。

每个事务之间都是独立的,相互不影响,一个事务里面的所有操作要么同时成功,要么同时失败(撤销)。

注意：提交事务的命令commit，回滚事务的命令式rollback，DDL语句也能把事务提交，相当于commit的效果。

例如:
1 修改数据1
2 修改数据2
3 修改数据3
4 修改数据4
5 执行DDL语句
6 rollback

这里的第六行回滚是没有用的,因为第五行执行了DDL语句,已经把事务给提交了。

create table student (
id number primary key,
name varchar2(20) not null,
age number
);

insert语句
--------------------------
//这样表示向student表中全部插入数据
//插入数据的顺序是按照建表的时候列的顺序
insert into student values(1,'tom',20);

//这样是可以自己控制向表中那些列插入数据，以及插入数据的顺序
insert into student (id,name) values(2,'jerry');

insert into student(id,name,age) values(&id,'&name',&age);

update语句
-----------------
修改表中所有数据
update student
set name = 'terry';

修改某一些数据
update student
set name = 'terry'
where id = 2;

若事务未提交，则此数据会锁住，其他终端不能对此修改，若修改，则卡在那里，除非到事务提交，则其他终端才能正常修改。

delete语句
-----------------
//删除表中所有数据
delete from student;

//加入where条件限制,删除某些数据
delete from student
where id=2;

 

savepoint 回滚点
------------------

DML语句1
savepoint A
DML语句2
savepoint B
DML语句3

rollback to A/B

这个时候可以通过这个回滚点让事务回滚到指定的位置,如果不指定回滚点而是直接rollback,那么事务会一下子回滚完（撤销整个事务）

lesson 14 view 视图
-----------------------
视图 view:
作用1:隐藏表中的重要数据
作用2:代替一些比较长的sql语句

//创建视图
create view v_student
as
select *
from student
where id = 2;

//查看视图内容
select *
from v_student;

视图分为俩类:
简单视图:
视图所代表的sql中如果没有group by语句,没有组函数,查询的只有一张表,那么这样的视图就是简单视图.
复杂视图
视图所代表的sql中如果有group by语句,或者有组函数,或者查询的是多张表,那么这样的视图就是复杂视图.

 

简单视图和复杂视图的区别:
1.简单视图可以修改,复杂视图不能修改。
2.通过简单视图可以修改原来表中的数据,通过复杂视图是不能修改原来的数据的。

 

Lesson 15 index 索引
-----------------------------
作用：提高在表查询数据的效率

index是建立在列上面的

条件：
1.这个列中的数据会经常被用作查询数据的条件
2.这个列上的数据不会经常的改动

注意：
1.有主键约束的列或者有唯一性约束性的列上面，系统会默认的加上索引。
2.在一个表中，索引并非是越多越好
3.在表中的某一个列加入上了合适的索引，那么也只有在数据量很大的时候，才能有所体现出这个查询的效率。
4.索引一点建立成功，那么之后这个索引就有系统来管理，我们自己是控制不了的。

 

索引的种类:
B-tree
位图
反序
函数

建立索引的格式:

create index 索引名
on 表名(列名);

删除索引
drop index 索引名;

 

Lesson 13 Creating Sequences 序列
-------------------------------
作用：帮我们生成主键列的值（非空唯一）

创建序列:
一般不需要设置sequence的属性,使用默认的方式去创建就可以了.
create sequence 序列名;

如果需要设置属性,那么就加上下面的语句.
[INCREMENT BY n] 每次拿出值加多少
[START WITH n] 初始值从几开始
[{MAXVALUE n | NOMAXVALUE}] 最大值
[{MINVALUE n | NOMINVALUE}] 最小值
[{CYCLE | NOCYCLE}] 到了最大值后是否循环
[{CACHE n | NOCACHE}] 每次在缓存里面放多少个值.

 

例子:
create sequence my_seq;

//获得序列中的下一个值
//这个值对应当前这个序列来说,肯定是非空唯一
select my_seq.nextval
from dual;

//查询序列中当前的值是多少
select my_seq.currval
from dual;

//在插入表中的主键列的时候会经常使用到序列
insert into student(id,name,age) values(my_seq.nextval,'tom',20);
insert into student(id,name,age) values(my_seq.nextval,'&name',&age);

 

drop sequence 序列名;

 

SYNONYM 同义词
synonym

student

my_stu

//给表student创建一个同义词
create synonym my_stu
for student;

//因为一般用户没有创建public synonym
的权限,所有我们需要用dba的身份登录到数据库中去创建
//sqlplus "/as sysdba"
create public synonym my_stu
for tom.student;

grant select on my_stu to public;//授权

 

一个新的用户至少有一个什么权限才能登录到数据库里面?
create session

Lesson 12 Altering Tables 修改表结构
------------------------------------------------
表结构的修改:

alter

在表中添加一个新的列
alter table student
add birthday date;

删除表的某列
alter table student
drop column birthday;

修改表的名字:
rename student to mystudent;

修改表中某列的类型
ALTER TABLE student
MODIFY (name VARCHAR2(500));

让约束失效:
必须知道约束的名字
ALTER TABLE s_emp
DISABLE CONSTRAINT s_emp_id_pk CASCADE;

让失效的约束再次生效
ALTER TABLE s_emp
ENABLE CONSTRAINT s_emp_id_pk;

 

rownum 伪列
-----------------------------------
rownum特点：
1.rownum是一个隐藏的行号
2.oracle里面独有，其他数据库没有
3.每个表里面或者查询的结果里面都存在

rownum的操作特点：
1.能够<任何数，
2.只能>0，大于的数不是0的话拿不到任何数据
3.只能=1，等于的数不是1的话拿不到任何数据

select rownum,last_name
from s_emp
where rownum>0;

 

select rownum,last_name
from s_emp;

 

 

我有数十万条数据的地址，看清是数十万条数据，必须要能够批量查询
我要提供数据样本供客户查询，但暂时不能给他显示具体的数字牌号
我希望能把这些数字隐藏掉或替换成*号，该如何实现呢？

数据表格式如：南京路123弄456号7890室
希望查询后显示：南京路***弄***号****室

该如何写该select语句呢？

select replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(地址,'0','*'),'1','*'),'2','*'),'3','*'),'4','*'),'5','*'),'6','*'),'7','*'),'8','*'),'9','*')