html网站地图生成器,wordpress的不好,那些网站是专门做一些调研的,怎样让客户做网站#x1f57a;作者#xff1a; 主页 我的专栏C语言从0到1探秘C数据结构从0到1探秘Linux #x1f618;欢迎关注#xff1a;#x1f44d;点赞#x1f64c;收藏✍️留言 #x1f3c7;码字不易#xff0c;你的#x1f44d;点赞#x1f64c;收藏❤️关注对我真的很重要作者 主页 我的专栏C语言从0到1探秘C数据结构从0到1探秘Linux 欢迎关注点赞收藏✍️留言 码字不易你的点赞收藏❤️关注对我真的很重要有问题可在评论区提出感谢支持 文章目录 一、事务一什么是事务二相关概念三隔离级别 二、锁一什么是锁二锁的分类三表级锁 三、约束一什么是约束二约束的分类三约束的创建四约束的维护 四、权限、角色与用户管理一概述二权限分类三系统权限管理四实体权限管理五角色管理 一、事务
一什么是事务
事务 事务是指作为单个逻辑工作单元执行的一组相关操作。 这些操作要求全部完成或者全部不完成。 使用事务的原因保证数据的安全有效。 事务的四个特点ACID 1、原子性Atomic事务中所有数据的修改要么全部执行要么全部不执行。 2、一致性Consistence事务完成时要使所有所有的数据都保持一致的状态换言之通过事务进行的所有数据修改必须在所有相关的表中得到反映。 3、隔离性Isolation事务应该在另一个事务对数据的修改前或者修改后进行访问。 4、持久性Durability保证事务对数据库的修改是持久有效的即使发生系统故障也不应该丢失。
二相关概念
回滚RollBack只能对未提交的数据撤销已经Commit的数据是无法撤销的因为commit之后已经持久化到数据库中。 脏读Dirty Read事务T1更新了一行数据还没有提交所做的修改T2读取更新后的数据T1回滚T2读取的数据无效这种数据称为脏读数据。 不可重复读UNrepeatable Read事务T1读取一行数据T2修改了T1刚刚读取的记录T1再次查询发现与第一次读取的记录不相同称为不可重复读。 幻读Phantom Read事务T1读取一条带WHERE条件的语句返回结果集T2插入一条新纪录恰好也是T1的WHERE条件T1再次查询结果集中又看到T2的记录新纪录就叫做幻读。
三隔离级别
NO_TRANSACTION 不支持事务READ_UNCOMMITED 允许脏读、不可重复读、幻读READ_COMMITED 允许不可重复读、幻读不允许脏读REPEATABLE 允许幻读不允许脏读、不可重复读SERIALIZABLE 脏读、不可重复读、幻读都不允许
Oracle支持SQL92标准的READ_COMMITED、SERIALIZABLE自身特有的READ_ONLY
二、锁
一什么是锁
数据库是一个多用户使用的共享资源。当多个用户并发地存取数据时在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据破坏数据库的一致性。
锁是实现数据库[并发控制](https://www.baidu.com/s?wd%E5%B9%B6%E5%8F%91%E6%8E%A7%E5%88%B6tn44039180_cprfenleimv6quAkxTZn0IZRqIHckPjm4nH00T1YLujfvP1R3PHTknHb1m1nz0ZwV5Hcvrjm3rH6sPfKWUMw85HfYnjn4nH6sgvPsT6KdThsqpZwYTjCEQLGCpyw9Uz4Bmy-bIi4WUvYETgN-TLwGUv3Erj03nj6znjn)的一个非常重要的技术。当事务在对某个数据对象进行操作前先向系统发出请求对其加锁。加锁后事务就对该数据对象有了一定的控制在该事务释放锁之前其他的事务不能对此数据对象进行更新操作。二锁的分类
根据保护的对象不同Oracle数据库锁可以分为以下几大类DML锁data locks数据锁用于保护数据的完整性DDL锁dictionary locks字典锁用于保护数据库对象的结构如表、索引等的结构定义内部锁和闩internal locks and latches保护数据库的内部结构。 DML锁的目的在于保证并发情况下的数据完整性在Oracle数据库中DML锁主要包括TM锁和TX锁其中TM锁称为表级锁TX锁称为事务锁或行级锁。 当Oracle执行DML语句时系统自动在所要操作的表上申请TM类型的锁。当TM锁获得后系统再自动申请TX类型的锁并将实际锁定的数据行的锁标志位进行置位。这样在事务加锁前检查TX锁相容性时就不用再逐行检查锁标志而只需检查TM锁模式的相容性即可大大提高了系统的效率。TM锁包括了SS、SX、S、X 等多种模式在数据库中用06来表示。不同的SQL操作产生不同类型的TM锁。 在数据行上只有X锁排他锁。在 Oracle数据库中当一个事务首次发起一个DML语句时就获得一个TX锁该锁保持到事务被提交或回滚。当两个或多个会话在表的同一条记录上执行 DML语句时第一个会话在该条记录上加锁其他的会话处于等待状态。当第一个会话提交后TX锁被释放其他会话才可以加锁。
三表级锁
行级排他锁Row Exclusive简称RX锁 当我们进行DML时会自动在被更新的表上添加RX锁或者也可以通过执行lock命令显式的在表上添加RX锁。在该锁定模式下允许其他的事务通过DML语句修改相同表里的其他数据行或通过lock命令对相同表添加RX锁定但是不允许其他事务对相同的表添加排他锁X锁。 行级共享锁Row Shared简称RS锁 通常是通过select … from for update语句添加的同时该方法也是我们用来手工锁定某些记录的主要方法。比如当我们在查询某些记录的过程中不希望其他用户对查询的记录进行更新操作则可以发出这样的语句。当数据使用完毕以后直接发出rollback命令将锁定解除。当表上添加了RS锁定以后不允许其他事务对相同的表添加排他锁但是允许其他的事务通过DML语句或lock命令锁定相同表里的其他数据行。 共享锁Share简称S锁 通过lock table in share mode命令添加该S锁。在该锁定模式下不允许任何用户更新表。但是允许其他用户发出select …from for update命令对表添加RS锁。 排他锁Exclusive简称X锁 通过lock table in exclusive mode命令添加X锁。在该锁定模式下其他用户不能对表进行任何的DML和DDL操作该表上只能进行查询。 共享行级排他锁Share Row Exclusive简称SRX锁 通过lock table in share row exclusive mode命令添加SRX锁。该锁定模式比行级排他锁和共享锁的级别都要高这时不能对相同的表进行DML操作也不能添加共享锁。 这五种模式的TM锁的兼容关系如下表所示√表示互相兼容的请求×表示互相不兼容的请求N/A表示没有锁定请求
-SXRSRXSRXN/AS√×√××√X×××××√RS√×√√√√RX××√√×√SRX××√××√N/A√√√√√√ 从前面的描述中可以看到我们不仅可以通过发出DML语句的方式由Oracle自动在表级别上添加TM锁。我们还可以通过发出lock table命令主动地在表级别上添加TM锁并在该命令中可以指定不同的锁定模式其命令格式如下所示lock table in [row share][row exclusive] [share][share row exclusive][exclusive] mode; 对Oracle数据库中的各SQL语句所产生的表级锁的情况进行汇总如下表所示
SQL语句表锁定模允许的表锁定模式Select * from ……RSRS、RX、S、SRX、XInsert into ……RXRS、RXUpdate ……RXRS、RXDelete from ……RXRS、RXSelect * from for updateRSRS、RX、S、SRXlock table in row share modeRSRS、RX、S、SRXlock table in row exclusive modeRXRS、RXlock table in share modeSRS、Slock table in share row exclusive modeSRXRSlock table in exclusive modeXRS
对于通过lock table命令主动添加的锁定来说如果要释放它们只需要发出rollback命令即可。
三、约束
一什么是约束
在Oracle中数据完整性可以使用约束、触发器、应用程序过程、函数三种方法来实现在这三种方法中因为约束易于维护并且具有最好的性能所以作为维护数据完整性的首选。 列级约束 列级定义是在定义列的同时定义约束 column [CONSTRAINT constraint_name] constraint_type 表级约束 表级定义是指在定义了所有列后再定义约束这里需要注意not null约束只能在列级上定义 column ,…, [CONSTRAINT constraint_name] constraint_type (column,…)
二约束的分类
1 not null非空 如果在列上定义了not null那么当插入数据时必须为列提供数据不能为NULL。约束只能在列级定义不能在表级定义。 2 unique唯一 当定义了唯一约束后该列值是不能重复的但是可以为null。 3 primary key主键 用于唯一的标识表行的数据当定义主键约束后该列不但不能重复而且不能为NULL。一张表最多只能有一个主键但是可以由多个unique约束。 创建主键或唯一约束后ORACLE会自动创建一个与约束同名的索引UNIQUENES为UNIQUE唯一索引。需要注意的是每个表只能有且有一个主键约束。 4 foreign key外键 用于定义主表和从表之间的关系外键约束要定义在从表上主要则必须具有主键约束或是unique约束当定义外键约束后要求外键列数据必须在主表的主键列存在或是为NULL。 用来维护从表Child Table和主表Parent Table之间的引用完整性. 外键约束是个有争议性的约束它一方面能够维护数据库的数据一致性数据的完整性。防止错误的垃圾数据入库 另外一方面它会增加表插入、更新等SQL性能的额外开销不少系统里面通过业务逻辑控制来取消外键约束。例如在数据仓库中就推荐禁用外键约束。 5 check 用于强制行数据必须满足的条件假定在sal列上定义了check约束并要求sal列值在10002000之间如果不在10002000之间就会提示出错。
三约束的创建
1 not null非空 方法一 SQL create table t1(id number,name varchar2(20) constraint nn_t1_id not null);
SQL select constraint_name,constraint_type,owner from user_constraints; CONSTRAINT_NAME C OWNER NN_T1_ID C SCOTT
方法二 SQL drop table t1 purge; SQL create table t1(id number,name varchar2(20)); SQL alter table t1 modify id constraint nn_t1_id not null; SQL select constraint_name,table_name,owner from user_constraints; CONSTRAINT_NAME TABLE_NAME OWNER NN_T1_ID T1 SCOTT
2 unique唯一 方法一 SQL create table t1(id number,qq number,constraint un_t1_qq unique(qq)); 方法二 SQL alter table t1 add constraint un_t1_qq unique(qq);
3 primary key主键 方法一 SQL create table t1(id number,qq number,constraint pk_t1_id primary key(id)); 方法二 SQL alter table t1 add constraint pk_t1_id primary key(id); SQL select constraint_name,table_name,owner from user_constraints; CONSTRAINT_NAME TABLE_NAME OWNER PK_T1_ID T1 SCOTT
4 foreign key外键 方法一 SQL create table t2(id number,cc number,constraint fk_t2_id foreign key(id) references t1(id)); 方法二 SQL alter table t1 add constraint pk_t2_id foreign key(id) references t1(id); SQL select constraint_name,table_name,owner from user_constraints; CONSTRAINT_NAME TABLE_NAME OWNER PK_T1_ID T1 SCOTT PK_T2_ID T1 SCOTT
当定义了外部键约束之后要求外部键列的数据必须在主表的主键列或惟一列中存在或者为NULL,FOREING KEY约束既可以在列级定义也可以在表级定义。
关键字说明 (1) FOREING KEY:该选项用于指定在表级定义外部键约束。当在表级定义外部键约束时必须指定该选项在列级定义外部键约束不需要指定该选项 (2) REFERENCES:该选项用于指定主表名及其主键列。当定义外部键约束时该选项必须指定。 (3) ON DELETE CASCAED:该选项用于指定级联删除选项。如果在定义外部键约束时指定了该选项那么当删除主表数据时会级联删除从表的相关数据。 (4) ON DELECT SET NULL:该选项用于指定转换相关的外部键值为NULL如果在定义外部键约束时指定了该选项那么当删除主表数据时会将从表外部键列的数据设置为NULL。 SQL create table t1(id number,qq number,constraint pk_t1_id primary key(id)); SQL create table t2(id number,sal number,constraint fk_t2_id foreign key(id) references t1(id));
SQL delete t1; ----由于主外键约束无法删除主表 delete t1 * ERROR at line 1: ORA-02292: integrity constraint (SYS.FK_T2_ID) violated - child record found
SQL insert into t2 values(2,2); —由于主外键的约束无法在外键表插入主键中id列没有的值 insert into t2 values(2,2) * ERROR at line 1: ORA-02291: integrity constraint (SYS.FK_T2_ID) violated - parent key not found
SQL delete t2; 1 row deleted.
SQL rollback;
SQL drop table t2 purge; SQL create table t2(id number,sal number,constraint fk_t2_id foreign key(id) references t1(id) on delete cascade); -----外键表添加级联删除参数
SQL delete t1; ----删除主键表上的数据级联删除外键表上的数据 1 row deleted.
SQL select * from t1; no rows selected
SQL select * from t2; no rows selected
5 check检查性约束 方法一 SQL create table t3(id number,sal number,constraint ck_t3_sal check(sal between 5000 and 50000)); SQL select constraint_name,table_name,owner from user_constraints;
CONSTRAINT_NAME TABLE_NAME OWNER CK_T3_SAL T3 SCOTT 方法二 SQL create table t3(id number,sal number); SQL alter table t3 add constraint ck_t3_sal check(sal5000);
四约束的维护
1 增加约束 1 如果增加UNIQUE、PRIMARY KEY、FOREIGN KEY 和CKECK 必须使用ALTER TABLE语句的ADD子句; 2 如果增加NOT NULL约束那么必须使用ALTER TABLE语句的MODIFY子句如 ALTER TABLE table_name ADD [CONSTRAINT constraint_name] constraint_type (column,…) ALTER TABLE table_name MODIFY column [CONSTRAINT constraint_name] NOT NULL;
2 修改约束名 在同一个方案中约束名必须惟一,并且约束名也不能与其他对象同名。当用IMPDP工具或者IMP工具导入其他对象时如发现有同名的对象将会出错 语法 ALTER TABLE table_name RENAME CONSTRAINT old_constraint_name TO new_constraint_name; 例 ALTER TABLE emp01 RENAME CONSTRAINT SYS_C005028 TO ck_emp01_salary;
SQL alter table t1 rename constraint PK_T1_ID to new pk01_t1_id; alter table t1 rename constraint PK_T1_ID to new pk01_t1_id * ERROR at line 1: --------主键无法更改名字 ORA-23290: This operation may not be combined with any other operation
SQL alter table t2 rename constraint fk_t2_id to fk01_t2_id; SQL select constraint_name,table_name from user_constraints where table_name‘T2’;
CONSTRAINT_NAME TABLE_NAME FK01_T2_ID T2 -------------外键可以更改名字
SQL alter table t1 add constraint un_t1_qq unique(qq);
SQL select constraint_name,table_name from user_constraints where table_name‘T1’;
CONSTRAINT_NAME TABLE_NAME PK_T1_ID T1 UN_T1_QQ T1
SQL alter table t1 rename constraint un_t1_qq to un01_t1_qq;
SQL select constraint_name,table_name from user_constraints where table_name‘T1’;
CONSTRAINT_NAME TABLE_NAME PK_T1_ID T1 UN01_T1_QQ T1
3 禁止约束
禁止约束指使约束临时失效。当禁止了约束之后约束规则将不再生效。在使用SQL*LOADER或INSERT装载数据之前为了加快数据装载速度应该首先禁止约束然后装载数据。 语法 ALTER TABLE table_name DISABLE CONSTRAINT constaint_name [CASCAED];–CASCAED用于指定级联禁止从表的外部键 SQL insert into t2 values(2,2); insert into t2 values(2,2) * ERROR at line 1: ORA-02291: integrity constraint (SYS.FK01_T2_ID) violated - parent key not found
SQL alter table t2 disable constraint fk01_t2_id; Table altered.
SQL insert into t2 values(2,2); 1 row created.
4 激活约束
语法 ALTER TABLE table_name ENABLE CONSTRAINT constraint_name; 例 SQL alter table t2 enable constraint fk01_t2_id; alter table t2 enable constraint fk01_t2_id * ERROR at line 1: —外键激活失败原因是在外键表中含有主键表中没有的数据 ORA-02298: cannot validate (SYS.FK01_T2_ID) - parent keys not found
SQL delete t2 where id2; -------删除数据
SQL alter table t2 enable constraint fk01_t2_id; ----激活成功
5 删除约束
当删除特定表的主键约束时如果该表具有相关的从表那么在删除主键约束时必须带有CASCAED选项 语法 ALTER TABLE table_name DROP CONSTRAINT constraint_name |PRIMARY KEY 例一删除唯一性约束 SQL select constraint_name,table_name from user_constraints where table_name‘T1’;
CONSTRAINT_NAME TABLE_NAME PK_T1_ID T1 UN01_T1_QQ T1
SQL alter table t1 drop constraint un01_t1_qq;
Table altered.
SQL select constraint_name,table_name from user_constraints where table_name‘T1’;
CONSTRAINT_NAME TABLE_NAME PK_T1_ID T1
例二删除主键约束级联删除外键约束 SQL alter table t1 drop primary key cascade;
Table altered.
SQL select constraint_name,table_name from user_constraints where table_name‘T2’;
no rows selected
SQL select constraint_name,table_name from user_constraints where table_name‘T1’;
no rows selected
6 显示信息
1.USER_CONSTRAINTS 2.USER_CONS_COLUMNS
SQL select constraint_name,table_name,column_name from user_cons_columns where table_name‘T1’;
CONSTRAINT_NAME TABLE_NAME COLUM UN_T1_ID T1 ID
四、权限、角色与用户管理
一概述
ORACLE 数据库系统预先定义了 CONNECT 、RESOURCE、 DBA、 EXP_FULL_DATABASE、 IMP_FULL_DATABASE 五个角色。 CONNECT 具有创建表、视图、序列等特权(alter session create cluster ) RESOURCE 具有创建过程、触发器、表、序列等特权、 DBA 具有全部系统特权 EXP_FULL_DATABASE、 IMP_FULL_DATABASE 具有卸出与装入数据库的特权。
二权限分类
系统权限系统规定用户使用数据库的权限。系统权限是对用户而言)。 实体权限某种权限用户对其它用户的表或视图的存取权限。是针对表或视图而言的。
三系统权限管理
DBA: 拥有全部特权是系统最高权限只有 DBA 才可以创建数据库结构。 RESOURCE:拥有 Resource 权限的用户只可以创建实体不可以创建数据库结构。 CONNECT:拥有 Connect 权限的用户只可以登录 Oracle不可以创建实体不可以创建数据库结构。 对于普通用户授予 connect, resource 权限。 对于 DBA 管理用户授予 connectresource, dba 权限。
[系统权限只能由 DBA 用户授出sys, system(最开始只能是这两个用户)] 授权命令SQL grant connect, resource, dba to 用户名 1 [,用户名 2]…; [普通用户通过授权可以具有与 system 相同的用户权限但永远不能达到与 sys 用户相同的权限system 用户的权限也可以被回收。] 例 SQL connect system/manager SQL Create user user50 identified by user50; SQL grant connect, resource to user50; 查询用户拥有哪些权限 SQL select * from dba_role_privs; SQL select * from dba_sys_privs; SQL select * from role_sys_privs; 删除用户SQL drop user 用户名 cascade; //加上 cascade 则将用户连同其创建的东西全部删除
四实体权限管理
1、实体权限分类select, update, insert, alter, index, delete, all //all 包括所有权限 execute //执行存储过程权限 user01: SQL grant select, update, insert on product to user02; SQL grant all on product to user02; user02: SQL select * from user01.product; // 此时 user02 查 user_tables不包括 user01.product 这个表但如果查 all_tables 则可以查到因为他可以访问。 2. 将表的操作权限授予全体用户 SQL grant all on product to public; // public 表示是所有的用户这里的 all 权限不包括 drop。 [实体权限数据字典]: SQL select owner, table_name from all_tables; // 用户可以查询的表 SQL select table_name from user_tables; // 用户创建的表 SQL select grantor, table_schema, table_name, privilege from all_tab_privs; // 获权可以存取的表被授权的 SQL select grantee, owner, table_name, privilege from user_tab_privs; // 授出权限的表(授出的权限) 查看用户拥有哪些系统权利 Sql select granteeprivilege from dba_sys_privs where grantee‘SCOTT’; 查看用户拥有哪些对象权利 Sql select granteeprivilegeownertable_name from dba_tab_privs where grantee‘SCOTT’; 3. DBA 用户可以操作全体用户的任意基表(无需授权包括删除) DBA 用户 SQL Create table stud02.product( id number(10), name varchar2(20)); SQL drop table stud02.emp; SQL create table stud02.employee as select * from scott.emp; 4. 实体权限传递(with grant option) user01: SQL grant select, update on product to user02 with grant option; // user02 得到权限并可以传递。 5. 实体权限回收 user01: SQLRevoke select, update on product from user02; //传递的权限将全部丢失。 说明 1如果取消某个用户的对象权限那么对于这个用户使用 WITH GRANT OPTION 授予权限的 用户来说同样还会取消这些用户的相同权限也就是说取消授权时级联的。
五角色管理
我在前面的篇幅中说明权限和用户。慢慢的在使用中你会发现一个问题如果有一组人他们的所需的权限是一样的当对他们的权限进行管理的 时候会很不方便。因为你要对这组中的每个用户的权限都进行管理。 有一个很好的解决办法就是角色。角色是一组权限的集合将角色赋给一个用户这个用户就拥有了这个角色中的所有权限。那么上述问题就 很 好处理了只要第一次将角色赋给这一组用户接下来就只要针对角色进行管理就可以了。 以上是角色的一个典型用途。其实只要明白角色就是一组权限的集合。
1.建一个角色 sqlcreate role role1; 2.授权给角色 sqlgrant create any table,create procedure to role1; Sqlgrant create sessioncreate table to role1; Sqlrevoke create sessioncreate table from role1;
3.授予角色给用户 sqlgrant role1 to user1; Sqlgrant role1 to user1 with admin option;
revoke role1 from user1;
查看系统中所有的角色 Sqlselect * from dba_roles;
查看用户被授予了哪些角色 Sqlselect granteegranted_role from dba_role_privs where grantee‘SCOTT’; 查看角色中包含了哪些系统权限 Sqlselect roleprivilege from role_sys_privs where role‘ROLE1’; 查看角色中包含了哪些对象权限 Sqlselect roleprivilegefrom role_tab_privs where role‘ROLE1’; 将角色授予角色 Sqlgrant role1 to role2;
4.查看角色所包含的权限 sqlselect * from role_sys_privs; 5.创建带有口令以角色(在生效带有口令的角色时必须提供口令) sqlcreate role role1 identified by password1; 6.修改角色是否需要口令 sqlalter role role1 not identified; sqlalter role role1 identified by password1; 7.设置当前用户要生效的角色 (注角色的生效是一个什么概念呢假设用户 a 有 b1,b2,b3 三个角色那么如果 b1 未生效则 b1 所包含的权限对于 a 来讲是不拥有的只有角色生效了角色内的权限才作用于用户最大可生效角色数由参数 MAX_ENABLED_ROLES 设定在用户登录后oracle 将所有直接赋给用户的权限和用户默认角色中的权限赋给用户。 sqlset role role1;//使 role1 生效 sqlset role role,role2;//使 role1,role2 生效 sqlset role role1 identified by password1;//使用带有口令的 role1 生效 sqlset role all;//使用该用户的所有角色生效 sqlset role none;//设置所有角色失效 sqlset role all except role1;//除 role1 外的该用户的所有其它角色生效。 sqlselect * from SESSION_ROLES;//查看当前用户的生效的角色。 8.修改指定用户设置其默认角色 sqlalter user user1 default role role1; sqlalter user user1 default role all except role1; 详见 oracle 参考文档 9.删除角色 sqldrop role role1; 角色删除后原来拥用该角色的用户就不再拥有该角色了相应的权限也就没有了。 说明: 1)无法使用 WITH GRANT OPTION 为角色授予对象权限 2)可以使用 WITH ADMIN OPTION 为角色授予系统权限,取消时不是级联 查看角色中还包含哪些角色 Sqlselect rolegranted_role from role_role_privs where role‘DBA’ 备注授予用户 DBA、RESOURCE 这俩个角色后系统会自动再授予用户 unlimited tablespace
