13、oracle 用户的创建和权限管理、视图、索引、增删改

用户的创建和权限管理、视图、索引、增删改

视图

什么是视图?

视图是一张虚表,不占用物理内存,是一个相对的概念。实际在,视图里面存的是逻辑代码,每次使用的时候都会取执行SQL代码。相当于我们定义的sql语句存在了字典里面,需要的时候就直接拿出来就可以了。

视图看上去非常像数据库的物理表,对它的操作同任何其它的表一样。当通过视图修改数据时,实际上是在改变基表中的数据;相反地,基表数据的改变也会自动反映在由基表产生的视图中。由于逻辑上的原因,有些Oracle视图可以修改对应的基表,有些则不能(仅仅能查询)

只读视图

create or replace view v_test01 as (
select * from emp with read only

好处:可以优化我们查询语句的代码,增强我们代码的可读性。

缺点:不建议直接对视图进行DML语句(insert,delete,update)的操作,要想对数据的增删改,建议直接

对数据表的增删改。

--创建一个视图
create or replace View V$_EMP
as select * from emp
select * from V$_EMP
--求部门平均薪资的等级最低的部门名称,用子查询。
select t02.*, d.dname
from (select t01.*, sg.grade
from (select e.deptno, avg(e.sal) vsal from emp e group by e.deptno) t01,
salgrade sg
where t01.vsal between sg.losal and sg.hisal) t02,
dept d
where t02.grade <= all
(select sg.grade
from (select e.deptno, avg(e.sal) vsal from emp e group by e.deptno) t01,
salgrade sg
where t01.vsal between sg.losal and sg.hisal)
and t02.deptno = d.deptno
------------
---利用视图来简化
create or replace View V$_EMP_SAL
as (select t01.*, sg.grade
from (select e.deptno, avg(e.sal) vsal from emp e group by e.deptno) t01,
salgrade sg
where t01.vsal between sg.losal and sg.hisal)
select es.*,d.dname
from V$_EMP_SAL es,dept d
where es.grade <= all(select grade from V$_EMP_SAL)
and es.deptno = d.deptno

创建数据库用户和赋予权限

通过sys或者system解锁用户:alter user 用户名 account unlock/lock;

创建用户:通过sys或者是system用户来创建。

create user 用户名 identified by 密码;

赋予权限:(grant)

  1. ​ 赋予登录的权限:grant create session to 用户名;

    (查看用户是否创建 SQL>select username from dba_users;)

  2. ​ 赋予表的权限 : grant all on scott.emp to 用户名;

    1. 收回权限:revoke all on scott.emp from 用户名;
  3. 赋予创建表,创建视图,创建索引,创建序列 的权限

    ​ grant create 权限名 to 用户名;

  4. 授予普通用户的 三大权限:

    1. connect
    2. resource
    3. dba

DDL语句

创建表结构的三大权限:

  • create:用来创建表----->create table 表名(字段名 类型,字段名 类型,.....);
  • drop:用来删除表------>drop table 表名;
  • alter:用来修改表------->alter table 表名 modify 字段名 所要修改的约束条件;

要想创建一张表结构与另一张表结构一样的话。

insert into user 01 select * from user02 where 1=2(写一个不成立的条件);

将一张表的数据全部都移到另一张表,表结构必须要一样。

insert into user01 select * from user02(不加条件)

DML语句

  • insert:插入一条记录。---->insert into 表名 (所需要插入的字段) values (所需要插入的数据);

  • delete:删除一条记录。----->delete from 表名 +条件;

    如果没有条件就删除所有的记录。

  • update:修改一条记录。----->update 表明 set 所需要修改的字段 =所需要修改的字段的值 +条件。

    如果没有条件就更改所要列中的改字段。

  • 删除表中所有的行,建议使用truncate table 语句。不要使用delete

事务处理

当我们需要等所有的逻辑都运行完才能算成功的话,我们需要用到事务管理。

比如说银行的取钱操作,我们必须等同时完成减钱和加钱的操作都完成了才算完成一个操作,同时减钱和加钱的两个操作把他当成一个事务,只有当事务正常的执行完毕或者事务回滚到操作之前。

产生事务的几个条件:

  1. 使用DML语句的时候,需要手动的提交事务,否则只能在本窗口才能查询,其他的不能够查询到你修改的数据。手动提交
  2. 对表结构进行操作的时候(DDL语句)(create ,drop,alter语句执行后)会自动的将之前为提交的事务进行提交。自动提交
  3. 正常退出当前用户的时候,也会自动的提交事务。
  4. 当程序不正常退出,比如说突然断电等,此时会自动回滚事务
  • commit:提交事务。

    提交事务后的状态:数据的修改被永久写在数据库中.
    数据以前的状态永久性丢失.
    所有的用户都能看到操作后的结果.
    记录锁被释放,其他用户可操作这些记录.

  • rollback:回滚事务。

    以前的数据可恢复
    当前的用户可以看到DML操作的结果
    其他用户不能看到DML操作的结果
    被操作的数据被锁住,其他用户不能修改这些数据

  • savepoint:创建一个保存点。

    ......

    savepoint 名称;

    ......

    rollback to 名称;

--手动提交事务
commit;
--手动回滚事务
rollback;
---可以回滚到某个节点
insert into user01 values ('101',123);
insert into user01 values ('102',123);
insert into user01 values ('103',123);
--保存一个节点,不完全回滚到最初状态。
savepoint t01;
insert into user01 values ('104',123);
insert into user01 values ('105',123);
rollback to t01;

序列化sequence

序列是oracle专有的对象,它用来产生一个自动递增的数列
创建序列的语法:
create sequence seq_name
increment by n
start with n
实例:create sequence seq_empcopy_id start with 1 increment by 1;

有两个方法:seq_name.nextval 获取下一个数据

​ seq_name.currval 获取当前的数据

--序列(创建一个自增不重复的一组数据)sequence
--用法:(sequence sq_no) sq_no.nextval获取下一个步数。
-- sq_no.currval 获取当前的
create sequence sq_no start with 100 increment by 1;
create or replace View V$_EMP as select * from user01;
create table emp001(
eno number(5),
ename varchar2(10),
eage number(3)
);
select * from emp001;
insert into emp001 values (sq_no.nextval,'张三',18);
delete from emp001;

数据类型

  1. number(x,y) :数字类型 ,最长x位,y位小数
  2. varchar2(maxlength):变长字符串,这个参数的上限是32767字节
  3. 声明方式如下VARCHAR2(L),L为字符串长度,没有缺省值,作为变量最大32767个字节
  4. char(max_length) 定长字符串 最大2000字节
  5. DATE:日期类型 (只能精确到秒。)
  6. TIMESTAMP:时间戳 (精确到微秒)
  7. long:长字符串,最长2GB
  8. 了解类型
    CLOB:最大长度4G -->大对象很少使用:如果存在大对象,一般的解决方案存入文件地址(地址为程序所在应用服务器的相对路径)。
    BLOB:存二进制文件
数据类型 含义
Varchar2(n) 变长字符串,存储空间等与实际空间的数据大小,最大为4K,长度以字节为单位指定(注意中文字符)
Char(n) 定长字符串,存储空间大小固定
Number(p,s) 整数或小数 ,p是精度(所有数字位的个数,最大38),s是刻度范围(小数点右边的数字位个数,最大127)
Date /timestamp(精确到毫秒) 年、月、日、时、分、秒 Date 精确到秒timestamp(精确到毫秒)

Long 长字符串,最长2GB
CLOB 最长长度4G 存储大对象,但是一般都不会将大对象存入数据库 而是保存地址
BLOB 存二进制文件

数据库的对象

对象名称 描述
基本的数据存储对象,以行和列的形式存在,列 也就是字段,行也就是记录
约束 执行数据校验,保证了数据完整性的
视图 一个或者多个表数据的逻辑显示
索引 用于提高查询的性能
Sequence 自增序列

命名规则:

必须以字母开头
可包括数字和三个特殊字符(# _ $)
不要使用oracle的保留字
同一用户下的对象不能同名

约束

--表的约束
create table ys(
u_id number(5),
username varchar2(10) ,--constraint ys_username_null not null,--not null,
usex varchar2(2),
uage number(3),
address varchar2(150),
deptno number(3),
--constraint ys_username check(username is not null)
foreign key (deptno) references dept (deptno)
);
create table dept(
deptno number(3) primary key,
dname varchar2(10)
);
select * from ys;
drop table ys;
--主键,外键,非空,年龄的大小,性别合法,唯一性。
--主键:非空的唯一的。
--主键能够唯一的区分一条数据。
----问题一,t_id怎么能够重复。
insert into ys values(001,'张三','男',18,'广东省某某',12);
insert into ys values(001,'张三','男',18,'广东省某某');
--加主键。
--第一第二种是列约束
--第一种:直接在字段名后面加 primary key
--模板:字段名 类型 primary key;
create table ys(
u_id number(5) ,--constraint ys_u_id primary key,--primary key,
username varchar2(10),
usex varchar2(2),
uage number(3),
address varchar2(150),
constraint pk_ys_uid primary key (u_id)
);
--第二种:用constraint关键字。
--1.放在字段类型后面
--例如:字段名 类型 constraint 声明名称 primary key,
u_id number(5) constraint ys_u_id primary key,
--第三种:表约束:
--放在字段的后面,结束之前。
--模板 constraint 声明错误名 primary key(所要声明主键的字段)
constraint pk_ys_uid primary key (u_id)
--移除主键约束
alter table ys modify u_id primary key;
--问题二: select * from ys;
--名字怎么能为空呢?
insert into ys (u_id) values (1234);
--解决:not null 非空约束1
--第一种: 直接在字段后面加 not null
-- 字段名 类型 not null;
username varchar2(10)
--第二种: constraint 变量名 not null;
username varchar2(10) constraint ys_username_null not null,
--第三种: 在字段后面添加 constraint 名称 check(需要非空的字段 not null )
constraint ys_username check(username is not null)
--表已存在的时候修改
alter table ys modify username not null;
--问题三:外键约束
--当一张表与另一张表关联的时候,而这个字段又是关联表的主键,
--此时需要外键约束他,只能让其填入关联表里主键的数据,其他数据则不能存进去
--关键字:foreign key(与外表关联的字段) references 所要关联的表 (与另外一张表关联的字段)
foreign key (deptno) references dept (deptno)
--问题四 : 年龄和性别都要符合正常的逻辑
--关键字 check 检查
-- check(所要判断的逻辑)
--问题五: 里面有唯一的,比如说qq号,手机号码。
--关键字 unique
--字段名 类型 not null unique;

关系模型的三类规则

为了维护数据库中的数据与现实世界的一致性,关系数据库的数据与更新操作必须遵循下列三类完整性规则:
实体完整性规则
这条规则要求关系中在组成主键的属性上不能有空值。
参照完整性规则
这条规则要求“不引用不存在的实体”。例如:deptno是dept表的主键,而相应的属性也在表emp中出现,此时deptno是表emp的外键。在emp表中,deptno的取值要么为空,要么等于dept中的某个主键值。
用户定义的完整性规则
用户定义的完整性规则反应了某一具体的应用涉及的数据必须满足的语义要求。

索引

索引是为了加快对数据的搜索速度而设立的。索引是方案(schema)中的一个数据库对象,与表独立存放.
索引的作用:在数据库中用来加速对表的查询,通过使用快速路径访问方法快速定位数据,减少了磁盘的I/O
Sql中的索引是非显示索引,也就是在索引创建以后,在用户撤销它之前不会在用到该索引的名字,但是索引在用户查询时会自动起作用。
索引的创建有两种情况

  1. 自动: 当在表上定义一个PRIMARY KEY 或者UNIQUE 约束条件时,Oracle数据库自动创建一个对应的唯一索引.
  2. 手动: 用户可以创建索引以加速查询

开发中使用索引的要点:

  1. 索引改善检索操作的性能,但降低数据插入、修改和删除的性能。在执行这些操作时,DBMS必须动态地更新索引。
  2. 索引数据可能要占用大量的存储空间。
  3. 并非所有的数据都适合于索引。唯一性不好的数据(如省)从索引的到的好处不比具有更多可能值的数据(如姓名)从索引得到的好处多。
  4. 索引用于数据过滤和数据排序。如果你经常以某种特定的顺序排序数据,则该数据可能是索引的备选。
  5. 可以在索引中定义多个列(如省加城市),这样的索引只在以省加城市的顺序排序时有用。如果想按城市排序,则这种索引没有用处。

在一列或者多列上创建索引.
CREATE INDEX index ON table (column[, column]...);

下面的索引将会提高对EMP表基于 ENAME 字段的查询速度.
CREATE INDEX emp_last_name_idx
ON emp (ename)

通过DROP INDEX 命令删掉一个索引.
DROP INDEX index;
删掉 UPPER_LAST_NAME_IDX 索引.
DROP INDEX upper_last_name_idx;

rowid

1、rowid 是oracle实际存在的值,是唯一的值
2、rownum 是一个虚拟的顺序值 ,前提是一定要排序
select emp.*,rowid from emp;

delete from emp e where rowid not in(select min(rowid) from emp group by ename)

如何只显示重复数据,或不显示重复数据
显示重复:select * from tablename group by id having count()>1
不显示重复:select * from tablename group by id having count(
)=1
删除重复数据原型:
delete from temp where rowid not in (
select min(rowid) from emp group by ename having count(*) >= 1)
and ename='wangcai'

数据库设计的三范式

第一范式:保持原子性,就是不要将组合的东西放在一个字段里面,比如说姓名和id,这样在取出来到java代码中就无法有对应得一个属性了。我们应该要做到一个字段不能够再分成多个了,也要保持这一个字段对应java代码的一个属性。

第二范式:不要多个对象的将他创建成一张表,就算有联系,也应该有主外键来区分。

​ 比如说将学生和班级创建成一张表是不合理的,我们一个通过班级的id作为学生表的外键,这样两张表就有关联 了。

第三范式:两张表不应该多个相同的字段,只要有一个联系的就能关联到两种表了。我的理解:不能存在传递依赖。即:除主键外,其他字段必须依赖主键。

posted @   站着说话不腰疼  阅读(1658)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· 百万级群聊的设计实践
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
· 永远不要相信用户的输入:从 SQL 注入攻防看输入验证的重要性
点击右上角即可分享
微信分享提示