oracle sql操作,Oracle下SQL基本操作(一)

--创建永久表空间

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;