您现在的位置是:网站首页> 内容页

分享知识-快乐自己:Oracle基本语法(创建:表空间、用户、授权、约束等)使用指南

  • 宝马在线顶级娱乐城
  • 2019-03-27
  • 179人已阅读
简介Oracle12c与Oracle11g创建用户时有差别。Oracle12C默认为CDB模式这时创建用户的时候需要加上c##开头;例如:c##MLQ。--说明--需求:创建

Oracle12c 与 Oracle11g 创建用户时有差别。Oracle12C默认为 CDB模式 这时创建用户的时候需要加上 c## 开头;例如:c##MLQ。

--说明--需求:创建表空间(MLQ1_hr) 和 临时表空间(MLQ1_TEMP)、创建新用户 c##MLQ1 为其分配指定的表空间和临时表空间--为用户分配基本权限、为用户创建表结构、添加数据。

--可能出现的问题--Oracle12c 默认的表空间为:USERS,--01、没有在创建用户的时候同时指定表空间,单独执行为用户分配表空间的时候可能会更改不成功。--02、在对某张表添加数据的时候,可能会出现对指定的表空间无权限操作:错误编码:ORA-01950,这个时候重新分配一下表空间即可(6)

1)创建一个数据库临时表空间

CREATE TEMPORARY TABLESPACE MLQ1_TEMPTEMPFILE "E:OracleOracleTablespacesMLQ1TEMP.DBF"SIZE 50MAUTOEXTEND ONNEXT 50 MAXSIZE 100MEXTENT MANAGEMENT LOCAL;

2)创建一个自动增长的表空间

CREATE tablespace MLQ1_hrLOGGING Datafile"E:OracleOracleTablespacesMLQ1MLQ1_hr01.dbf" size 60M Autoextend onNEXT 50 MAXSIZE 2048MEXTENT MANAGEMENT LOCAL;

3)创建用户并指定表空间

create user c##MLQ1 identified by rootdefault tablespace MLQ1_hr temporary tablespace MLQ1_TEMP;

4)删除用户

drop user c##MLQ1 cascade;

无法删除用户时:查看当前用户连接状态

select saddr,sid,serial#,paddr,username,status from v$session where username is not null

结束指定的用户连接

alter system kill session"(sid),(serial#)";

5)给用户授权

--connect role(连接角色)

  --临时用户,特指不需要建表的用户,通常只赋予他们connect role.

  --connect是使用oracle简单权限,这种权限只对其他用户的表有访问权限,包括select/insert/update和delete等。

  --拥有connect role 的用户还能够创建表、视图、序列(sequence)、簇(cluster)、同义词(synonym)、回话(session)

  --和其他 数据的链(link)

--resource role(资源角色)

  --更可靠和正式的数据库用户可以授予resource role。

  --resource提供给用户另外的权限以创建他们自己的表、序列、过程(procedure)、触发器(trigger)、索引(index)和簇(cluster)。

--dba role(数据库管理员角色)

  --dba role拥有所有的系统权限

  --包括无限制的空间限额和给其他用户授予各种权限的能力。system由dba用户拥有

grant resource,connect to c##MLQ1grant resource to c##MLQ1

撤销授权:

revoke connect, resource from c##MLQ1

创建、授权、删除角色:

除了前面讲到的三种系统角色----connect、resource和dba,用户还可以在oracle创建自己的role。

用户创建的role可以由表或系统权限或两者的组合构成。为了创建role,用户必须具有create role系统权限。

1》创建角色  语法: create role 角色名;  例子: create role testRole;2》授权角色  语法: grant select on class to 角色名;  列子: grant select on class to testRole;  注:现在,拥有testRole角色的所有用户都具有对class表的select查询权限3》删除角色  语法: drop role 角色名;  例子: drop role testRole;  注:与testRole角色相关的权限将从数据库全部删除

6)为用户分配重新分配数据库表空间

alter user c##MLQ1 quota unlimited on MLQ1_hr;alter user c##MLQ1 QUOTA unlimited ON MLQ1_hr TEMPORARY TABLESPACE MLQ1_TEMP;

7)删除表空间

DROP TABLESPACE MLQ1_hr INCLUDING CONTENTS;drop tablespace MLQ1_hr including contents and datafiles cascade constraints

8)在c##MLQ1用户下创建表结构

--创建表emp--create table emp(empno number primary key not null,empname nvarchar2(32) not null,deptno number not null);--创建表dept--create table dept(deptno number primary key not null,deptname nvarchar2(32) not null);insert into EMP values("2018001","MLQ",1);

9)删除表空间下所有的表

SELECT "DROP TABLE " || TABLE_NAME || " CASCADE CONSTRAINTS" V_NAMEFROM DBA_TABLESWHERE TABLESPACE_NAME = "USERS";

10):查询当前用户所在的表空间

select username,default_tablespace from user_users;

11):查看所有存在的表空间(查询到的数据可能没有全部展开,点击向下的绿箭头)

select * from dba_tablespaces

12):查看所有用户

select * from all_users;

13):查看用户具有怎样的角色

select * from dba_role_privs where grantee="C##MLQ1";

14):查看oracle中所有的角色

select * from dba_roles;

 

1):Oracle的创建表和创建约束的Sql语句— —

Oracle数据库中的约束有五种约束,分别是:

实体完整性:主键约束、唯一性约束(可以为空)

域完整性:check 检查约束

引用完整性:Foreign KEY 外键约束

默认约束(默认值)

SQL语句创建约束需要一个关键字:Costraint

模拟两张表:

---1、创建模拟的数据表 --- --1.1.创建学生表Student create table Student( StuId NUMBER NOT NULL, --学生ID StuName VARCHAR2(10) NOT NULL, --名称 Gender VARCHAR2(10)NOT NULL, -- 性别 Age NUMBER(2) NOT NULL, -- 年龄 JoinDate DATE NULL, --入学时间 ClassId NUMBER NOT NULL, --班级ID Address VARCHAR2(50) NULL --家庭住址 ); --1.2、创建班级表StuClass create table StuClass( classId NUMBER not null, -- 班级ID ClassName varchar2(20) not null, --班级名称 Notes varchar2(50) null default"班级信息", --备注,默认班级信息 );

创建数据表约束:

--2.1)创建主键约束-- alter table Student add constraint PK_Student_StuId primary key(StuId); alter table StuClass add constraint PK_StuClass_ClassId primary key(ClassId); --2.2) 创建检查约束-- alter table Student add constraint CK_Student_Gender check(gender="男" or gender="女"); alter table Student add constraint CK_Student_Age check(Age>=0 and Age<=100); --2.3)创建唯一约束-- alter table Student add constraint UQ_Student_StuName unique(StuName); --2.4)创建默认约束-- --alter table Student add constraint DF_Student_Address default("地址不详"); alter table Student Modify Address varchar(50) default "地址不详"; alter table Student Modify JoinDate Date default sysdate; --2.5)创建外键约束-- alter table Student add constraint FK_Student_StuCLass_ClassId foreign key(ClassId) references StuClass(ClassId);

注意:创建表还是约束,与SQL Server基本相同,注意:在Oracle中default是一个值,而SQL Server中default是一个约束,因此Oracle的default设置可以在建表的时候创建或者通过Modify函数创建

添加模拟数据:

--3.1)添加班级信息 insert into StuClass(ClassId,Classname) values(1,"一班"); insert into StuClass(ClassId,Classname) values(2,"二班"); insert into StuClass(ClassId,Classname) values(3,"三班"); --3.2)添加学生信息 insert into Student(StuId,Stuname,Gender,Age,ClassId)   values(1,"关羽","男",17,1); insert into Student(StuId,Stuname,Gender,Age,ClassId)   values(2,"张飞","男",16,2); insert into Student(StuId,Stuname,Gender,Age,ClassId)   values(3,"刘备","男",18,3);

查询模拟数据:

select * from Student;select * from StuClass;

查询结果:

验证数据表约束:验证Student表的StuName是否唯一(唯一约束)

--插入相同名称--insert into Student(StuId,Stuname,Gender,Age,ClassId) values(5,"关羽","男",18,1);

验证Student表Gender的检查约束:

--添加性别为未知的数据-- insert into Student(StuId,Stuname,Gender,Age,ClassId) values(4,"曹操","未知",18,1);

 

其他的验证就不一样了,这里不在多多演示。

Face your past without regret. Handle your present with confidence.Prepare for future without fear. keep the faith and drop the fear.

面对过去无怨无悔,把握现在充满信心,备战未来无所畏惧。保持信念,克服恐惧!一点一滴的积累,一点一滴的沉淀,学技术需要不断的积淀!

 

文章评论

Top