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

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_TEMP TEMPFILE 'E:\Oracle\OracleTablespaces\MLQ1\TEMP.DBF' SIZE 50M AUTOEXTEND ON NEXT 50 MAXSIZE 100M EXTENT MANAGEMENT LOCAL;

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

CREATE tablespace MLQ1_hr LOGGING Datafile 'E:\Oracle\OracleTablespaces\MLQ1\MLQ1_hr01.dbf' size 60M Autoextend on NEXT 50 MAXSIZE 2048M EXTENT MANAGEMENT LOCAL;

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

create user c##MLQ1 identified by root default 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##MLQ1

grant 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_NAME FROM DBA_TABLES WHERE 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.

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