--创建永久表空间
CREATE TABLESPACE "58SPACE"
    LOGGING
    DATAFILE 'D:\ORACLE\ORADATA\MYDB\57SPACE.ora' SIZE 5M EXTENT
    MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO

--创建临时表空间   
CREATE
    TEMPORARY TABLESPACE "58TMPSPACE" TEMPFILE
    'D:\ORACLE\ORADATA\MYDB\58TMPSPACE.ora' SIZE 5M EXTENT MANAGEMENT
    LOCAL UNIFORM SIZE 2M
   
--创建用户
CREATE USER "58USER"
IDENTIFIED BY "123456"--指定口令
DEFAULT TABLESPACE "58SPACE"--分配永久表空间
TEMPORARY TABLESPACE "58TMPSPACE"--分配临时表空间
QUOTA UNLIMITED ON "58SPACE";--分配使用表空间的权限

--SQL COMMAND
--用户从客户端登录数据库
CONN 58USER/123456@MYDB;
CONN system/system@MYDB;

--创建角色
CREATE ROLE "58ROLE";
CREATE ROLE "TESTROLE";
--授权
GRANT ALTER ANY TABLE TO "58ROLE";
GRANT ALTER ANY TABLE TO "58ROLE" WITH ADMIN OPTION;
REVOKE ALTER ANY TABLE FROM "58ROLE";

GRANT CONNECT TO "58USER" WITH ADMIN OPTION;
REVOKE CONNECT FROM "58USER";
GRANT RESOURCE TO "58USER";

GRANT CONNECT TO "TESTROLE";

 

 

--创建永久表空间
CREATE TABLESPACE "62SPACE"
    LOGGING
    DATAFILE 'D:\ORACLE\ORADATA\MYDB\62SPACE.ora' SIZE 5M EXTENT
    MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO

--创建临时表空间   
CREATE
    TEMPORARY TABLESPACE "62TMPSPACE" TEMPFILE
    'D:\ORACLE\ORADATA\MYDB\62TMPSPACE.ora' SIZE 5M EXTENT MANAGEMENT
    LOCAL UNIFORM SIZE 2M
   
--创建用户
CREATE USER "62USER"
IDENTIFIED BY "123456"--指定口令
DEFAULT TABLESPACE "62SPACE"--分配永久表空间
TEMPORARY TABLESPACE "62TMPSPACE"--分配临时表空间
QUOTA UNLIMITED ON "62SPACE";--分配使用表空间的权限

--SQL COMMAND
--用户从客户端登录数据库
CONNECT 62USER/123456@MYDB;
CONN system/system@MYDB;

--创建角色
CREATE ROLE "62ROLE";
CREATE ROLE "TESTROLE";
--授权
GRANT ALTER ANY TABLE TO "62ROLE";
GRANT ALTER ANY TABLE TO "62ROLE" WITH ADMIN OPTION;
REVOKE ALTER ANY TABLE FROM "62ROLE";

GRANT CONNECT TO "62USER" WITH ADMIN OPTION;
GRANT CONNECT TO "58USER";
--取消权限或角色
REVOKE CONNECT FROM "62USER";

GRANT RESOURCE TO "62USER";

GRANT CONNECT TO "TESTROLE";

--查看系统视图
SELECT * FROM USER_TABLESPACES;
SELECT * FROM USER_OBJECTS;
SELECT * FROM USER_CONSTRAINTS;

--创建表
CREATE TABLE STUDENT(
  ID CHAR(10),
  NAME VARCHAR2(40) NOT NULL,
  SEX CHAR(1) CONSTRAINTS STU_SEX_NN NOT NULL,
  BIRTHDAY DATE,
  CONSTRAINTS STU_ID_PK PRIMARY KEY (ID)
);


CREATE TABLE DEGREEdd (
  ID CHAR(10),
  CID CHAR(4),
  SCORE NUMBER(4, 1)
);

 

--删除表
DROP TABLE DEGREE;
--添加约束
ALTER TABLE DEGREE
  ADD CONSTRAINTS DEG_IDCID_PK PRIMARY KEY (ID, CID);

--外键约束
ALTER TABLE DEGREE
  ADD CONSTRAINTS DEG_ID_FK FOREIGN KEY (ID) REFERENCES STUDENT(ID);

--CHECK 约束
ALTER TABLE DEGREE
  ADD CONSTRAINTS DEG_SC_C CHECK (SCORE >= 60);
 
--UNIQUE
ALTER TABLE DEGREE
  ADD CONSTRAINTS DEG_CID_UK UNIQUE (CID);

--添加 NOT NULL
ALTER TABLE DEGREE
  MODIFY CID NOT NULL;
 
ALTER TABLE DEGREE
  MODIFY CID CONSTRAINTS DEG_CID_NN NOT NULL;

--删除约束
ALTER TABLE DEGREE
  DROP CONSTRAINTS SYS_C003040;
 
--添加列
ALTER TABLE STUDENT
  ADD ROOMID CHAR(4);
 
--SQL COMMAND
DESC STUDENT;

--删除列
ALTER TABLE STUDENT
  DROP COLUMN ROOMID;
 
--修改列的类型
ALTER TABLE DEGREE
  MODIFY CID VARCHAR2(4);
 
--启用/禁用约束
ALTER TABLE DEGREE
  ENABLE CONSTRAINTS DEG_CID_NN;

ALTER TABLE DEGREE
  DISABLE CONSTRAINTS DEG_CID_NN;
 
--修改列名
ALTER TABLE DEGREE
  RENAME COLUMN CID TO COURSE;

--修改约束名
ALTER TABLE DEGREE
  RENAME CONSTRAINTS DEG_CID_NN TO DEG_COU_NN;
 
--修改表名
RENAME STUDENT TO STU;

--截断表,删除数据的同时释放该表所占用的内存空间
TRUNCATE TABLE STU;