Oracle基础
时间：2021-07-01 10:21:17
帮助过：11人阅读
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
Oracle基础
标签：int 控制 ble 用户 自定义 运算 span desc rownum
