sql语句相关操作
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
create user test identified by test default tablespace users temporary tablespace temp quota 3M on users; grant connect,resource to test with admin option; conn test/test show user; alter user test identified by test1;//修改用户口令 grant create any view to scott; revoke create any view from scott; grant select,update on dept to public;//授予实体权限 revoke update on dept from public; //回收实体权限 create role app_user identified by hello; //创建角色 grant create view to app_user; //授予创建视图权限 grant app_user to test; //角色授予用户,这些用户都具有这个角色的权限 create user WangMing identified by WangMing default tablespace userwang temporary tablespace tempwang quota 3M on userwang; create user LiYong identified by LiYong default tablespace userli temporary tablespace templi quota 3M on userli;
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
CREATE USER WangMing IDENTIFIED BY WangMing DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA 3M ON users; CREATE USER LIYONG IDENTIFIED BY LIYONG DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA 3M ON users; GRANT CONNECT,RESOURCE to WangMing WITH ADMIN OPTION; GRANT CONNECT,RESOURCE to LIYONG WITH ADMIN OPTION; CREATE TABLE Mydept ( DNO VARCHAR2(10), DNAME VARCHAR2(10), MANAGER VARCHAR2(10), LOC VARCHAR2(10), PHONE VARCHAR(10), CONSTRAINT PK_Mydept PRIMARY KEY (DNO) ); CREATE TABLE Myemp ( ENO VARCHAR2(10), ENAME VARCHAR2(10), AGE NUMBER(10), JOB VARCHAR2(10), SAL NUMBER(10), DNO VARCHAR2(10), CONSTRAINT PK_Myemp PRIMARY KEY (ENO), CONSTRAINT FK_Myemp_To_Mydept FOREIGN KEY (DNO) REFERENCES Mydept (DNO) ); INSERT INTO Mydept VALUES('1','SALES','ZHAOSI','WuHan','12345678'); INSERT INTO Myemp VALUES('2','LIYONG',26,'SALESMAN',3400,'1'); INSERT INTO Myemp VALUES('4','SCOTT',24,'SALESMAN',3400,'1'); INSERT INTO Myemp VALUES('1','WANGMING',24,'SALESMAN',3400,'1'); INSERT INTO scott.Mydept VALUES('2','OFFICE','ZHANGHENG','BeiJing','33334567'); GRANT select(MAX(SAL)) ON Myemp TO ZHANGXIN;
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
CREATE TABLE Employee ( ENO VARCHAR2(10), ENAME VARCHAR2(10), BIRTH VARCHAR(15), JOB VARCHAR2(10), SAL VARCHAR2(10), DNO VARCHAR2(10), STATE VARCHAR2(10), PRIMARY KEY(ENO), FOREIGN KEY(DNO) REFERENCE TO Department(DNO) ); CREATE TABLE Department ( DNO VARCHAR2(10), DNAME VARCHAR2(10), MAGNO VARCHAAR2(10), LOC VARCHAR2(10), PHONE VARCHAR2(12), PRIMARY KEY(DNO) ); CREATE TABLE Teacher ( Eno NUMERIC(4), Sal NUMERIC(7,2), PJob char(10), CONSTRAINT PK_Teacher PRIMARY KEY(Eno) VALIDATE ); CREATE OR REPLACE TRIGGER Insert_Or_Update_Sal BEFORE INSERT OR UPDATE ON Teacher FOR EACH ROW WHEN((NEW.PJob='教授') AND (new.Sal<4000)) BEGIN select 4000 into:new.Sal from dual; END; / INSERT INTO Teacher VALUES('1','3400','教授'); INSERT INTO Teacher VALUES('2','4500','教授'); INSERT INTO Teacher VALUES('3','4500','教授'); CREATE TABLE Sal_log ( Eno NUMERIC(4), Sal NUMERIC(7,2), Username char(80), DDate TIMESTAMP, CONSTRAINT PK_Sal_log PRIMARY KEY(Eno) ); CREATE OR REPLACE TRIGGER Insert_Sal AFTER INSERT ON Teacher FOR EACH ROW BEGIN INSERT INTO Sal_log VALUES(:new.Eno,:new.Sal,user,sysdate); END; / CREATE TABLE Department ( No NUMBER(12), Name CHAR(40), PRIMARY KEY(No) ); CREATE TABLE Students ( No NUMBER(12), Name CHAR(8), Sex INTEGER DEFAULT 0, Birthday DATE, Class CHAR(40), DeptNo NUMBER(12), PRIMARY KEY(No), FOREIGN KEY(DeptNo) REFERENCES Department(No) ); CREATE TABLE Course ( No NUMBER(12), Name CHAR(8), Credit FLOAT, PRIMARY KEY(No) ); CREATE TABLE SC ( CNo NUMBER(12), SNo NUMBER(12), Grade FLOAT, PRIMARY KEY(CNo,SNo), FOREIGN KEY(CNo) REFERENCES Course(No), FOREIGN KEY(SNo) REFERENCES Students(No) ); CREATE TABLE SC_U ( CNo NUMBER(12), SNo NUMBER(12), Oldgrade FLOAT, Newgrade FLOAT ); CREATE OR REPLACE TRIGGER SC_T AFTER UPDATE OF Grade ON SC FOR EACH ROW WHEN (NEW.Grade>=1.1*OLD.Grade) BEGIN INSERT INTO SC_U(SNo,CNo,OldGrade,NewGrade) VALUES(:OLD.SNo, :OLD.CNo, :OLD.Grade, :NEW.Grade); END; / CREATE TABLE StudentInsertLog ( InsertDate DATE, InsertNumber NUMBER(12), Operator CHAR(20) ); CREATE OR REPLACE TRIGGER Student_Count AFTER INSERT ON Students DECLARE ICount NUMBER(12); temp NUMBER(12); BEGIN SELECT COUNT(*) INTO temp FROM StudentInsertLog; SELECT COUNT(*) INTO ICount FROM Students; IF(temp<>0) THEN SELECT InsertNumber INTO temp FROM StudentInsertLog WHERE InsertDate = (SELECT MAX(InsertDate) FROM StudentInsertLog); END IF; INSERT INTO StudentInsertLog (InsertDate, InsertNumber, Operator) VALUES(SYSDATE, ICount-temp,user); END; /