--创建用户 create user qjt identified by 1234 --权限 grant connect to qjt grant resource to qjt --建表 create table userinfo (id number primary key not null) --查询有几张表 select * from all_tables where owner='QJT' --伪列 select id,rowid,rownum from userinfo --查询自然排序的第三条记录 select * from ( select ename,rownum rn from emp ) temp where rn=3 --查询教师表中薪水排名第五的教师信息 select * from ( select rownum rn,ta.* from ( select ename,sal,rownum rn from emp order by sal desc )ta where rownum<6 )where rn>=5 --分页 --方法一 select * from ( select temp.*,rownum rn from ( select emp.* from emp order by sal desc ) temp where rownum<=9 ) where rn>=5 --方法二 select * from ( SELECT temp.*,ROWNUM rn FROM ( SELECT * FROM emp e ORDER BY e.sal DESC )temp )temp2 WHERE TEMP2.rn BETWEEN 5 AND 9 --授权 grant select on HAPPYY2165.STUDENT to scott --解锁用户 alter user hr account unlock --收回权限 revoke select on qjt from SCOTT --通过角色控制 --自定义角色 CREATE role role_testy2165 --将查询表的权限和角色绑定 grant SELECT ON Y2165."student" TO role_testy2165 --给用户分配角色 GRANT role_testy2165 to SCOTT --distinct查询不重复的数据 select DISTINCT "stuname","stuage" from STUDENT; --创建学生表的备份表 create table studentbak as select * from STUDENT --删除相同的列,保留id最大的 DELETE FROM student WHERE "stuno" NOT IN(SELECT "MAX"("stuno") FROM student GROUP BY "stuname","stuage") SELECT 1+1 FROM dual SELECT '你'||'好' FROM dual SELECT '运算结果'||'5' FROM dual --交集 SELECT deptno FROM dept INTERSECT SELECT DISTINCT deptno FROM emp --减集 SELECT deptno FROM dept MINUS SELECT DISTINCT deptno FROM emp