oracle基础

--创建一个用户
create user y2165 identified by y2165


--权限
grant connect to y2165
grant RESOURCE to y2165


--建立一张表
create table userinfo
(id number primary 
ey not null)




--伪列
select id,rowid,rownum from userinfo




--查询自然排序的第三条记录
select * 
from
(
select ename,rownum rn from emp
)temp
where rn=3


--通过角色控制
--自定义角色
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