ORACLE常用语句和操作（增删改查、函数使用、授权、查依赖等）
文章目录
一、简介
ORACLE是一种关系型数据库
ORACLE的 用户名.表名 相当于MySQL的 数据库.表名（即MySQL使用一个表时需要知道这个表是哪个库下面的表，而ORACLE使用表时需要知道是哪个用户下的表）
登录时需要知道其 用户名、密码、IP地址、实例
shell命令行登录命令为：sqlplus 用户名/密码@IP地址/实例
shell命令行以dba用户登录：sqlplus / as sysdba
表空间：是一种用于存储数据库对象的逻辑空间（分为下面3种）
永久表空间：存储数据库中需要永久化存储的对象，如表、视图、存储过程、索引（一般口头上的表空间指这个表空间）
临时表空间：存储数据库的空间执行过程，如执行查询过程中产生的临时表
UNDO表空间：保存数据修改前的副本。对事务修改之前的数据进行保存，方便进行回滚、恢复、撤销等操作
（Oracle 12C引入了CDB与PDB的新特性，在ORACLE 12C之前，实例与数据库是一对一或多对一关系，不可能一个实例对应多个数据库，12C之后使用CDB容器数据库就可以实现实例与数据库的一对多。然后我装的是个CDB库，也不会做修改，所以建的用户都是带C##的。。。。。）
二、DDL语句
1.创建用户
创建用户，为用户分配表空间，给用户授予权限
--创建用户( c##test为用户名， 123456为密码)
create user c##test identified by 123456;
--创建表空间（TEST_TBS为新建表空间名称，'D\ORACLE_TBS'为路径，500M为大小）
create tablespace TEST_TBS datafile 'D:\ORACLE_TBS\test_data.bdf' size 500M;
--将创建的表空间分配给用户（c##test为空户名，TEST_TBS为表空间名）
alter user c##test default tablespace TEST_TBS;
--授予用户权限
grant create session, --建立会话，使用户能连接，如plsql
create table, --创建表
create view, --创建视图
create procedure --创建存储过程
to c##test;
--授予dba权限（需使用sysdba或有DBA级联权限的用户）
grant create session, dba to c##test;
2.创建表
--创建一个EMP员工信息表
create table EMP
(
empno NUMBER(4) not null,
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2)
)
--对表名进行注释
comment on table c##dong.emp is '员工信息表';
--对表的字段名进行注释
COMMENT ON COLUMN C##DONG.EMP.EMPNO IS '员工编号';
COMMENT ON COLUMN C##DONG.EMP.ENAME IS '员工姓名';
COMMENT ON COLUMN C##DONG.EMP.JOB IS '职位';
COMMENT ON COLUMN C##DONG.EMP.MGR IS '主管';
COMMENT ON COLUMN C##DONG.EMP.HIREDATE IS '入职日期';
COMMENT ON COLUMN C##DONG.EMP.SAL IS '薪资';
COMMENT ON COLUMN C##DONG.EMP.COMM IS '奖金';
COMMENT ON COLUMN C##DONG.EMP.DEPTNO IS '部门编号';
3.创建视图
CREATE OR REPLACE VIEW EMP_V AS
SELECT
EMPNO
,ENAME
,JOB
,MGR
,HIREDATE
,SAL
,COMM
,DEPTNO
FROM EMP;
4.其他
--将表中的所有数据清除，表结构还在
truncate table 表名;
--删除表
drop table 表名;
--使用子查询来建表并将select出来的数据导入新表；只是表结构一致，但没有注释
create table 新表名 as select * from 来源表的表名;
--按照子查询建表，只有表结构，没有数据，没有注释
create table 新表名 as select * from 来源表表名 where 1=2;
三、DML、DQL语句
1.增 insert
#insert语法，支持事务回滚，执行后需要显式提交（commit或者点击客户端按钮提交）
INSERT INTO c##dong.student VALUES('110','张三','男','1999/09/09','95033');
INSERT INTO c##dong.student(sno,sname,ssex) VALUES('111','李四','男');
INSERT INTO c##dong.student SELECT * FROM c##dong.student2;
INSERT INTO student(sname,ssex) SELECT sname,ssex FROM student2;
commit;
2.删 delete
--delete支持事务回滚，执行后需要显式提交（commit或者点击客户端按钮提交）
--全部删除
delete from student2;
commit;
--删除满足条件的行（删除姓李的同学信息）
delete from student where sname like '李%';
commit;
3.改 update
--update支持事务回滚，执行后需要显式提交（commit或者点击客户端按钮提交）
update student set sno = '113' where sname = '张三';
commit;
--select语句后加 for update就可以通过pl/sql等客户端的可视化界面进行修改,修改后需要提交
select * from student for update;
4.查 select（重重重重点）
--查询学生表的所有信息
select * from student;
--查询95033班的学生编号和学生姓名
select sno,sname from student where class = '95033';
--查询每个班的总人数,并按人数 从多到少排序
select class,count(*) cnt from student group by class order by cnt desc;
--查询人数大于等于4的班级
select class from student group by class having count(*) >=4 ;
--子查询部分，一个sql语句中可以先查出一个结果集作为一个临时表，用户在一条sql语句中多次用到此结果集的一个优化。
WITH TEMPSCORE AS
(SELECT * FROM SCORE)
SELECT * FROM SCORE
UNION ALL
SELECT * FROM TEMPSCORE;
--嵌套，子查询，表关联，with as,in,exists,minus,union all,函数使用
--面试前，网上找题练一练
四、常用函数
-
字符串拼接、截取
#字符串的拼接可以通过 || 来完成 select 'hello' || 'world' from dual; select concat('hello','world') from dual; #截取 substr(start,length) start是开始的位置，length是长度 select substr('hello',1,3) from dual; --hel select '1999' || '-' || substr('2021-12-11',6,5) from dual; --1999-12-11 -
字符串与日期类型的转换、日期的截取
--VARCHAR2转日期 to_date(需要转换的字符串, 字符串格式) select to_date('2021-12-11','yyyy-mm-dd') from dual; select to_date('20211211','YYYYMMDD') from dual; select date'2021-12-11' from dual; --日期类型转VARCHAR select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual; --2021-12-11 11:13:47 select to_char(sysdate,'yyyymmdd') from dual; --20211211 select to_char(sysdate,'yyyy/mm/dd') from dual; --20211211 --获得传入日期的当年第一天 select trunc(date'2021-12-11','yyyy') from dual; --2021/01/01 --获得传入日期的当年最后一天 select last_day(add_months(trunc(date'2021-12-11','yyyy'),11)) from dual; --2021/12/31 --获得传入日期的当月第一天 select trunc(date'2021-12-11','mm') from dual; --2021/12/01 --获得传入日期的当月最后一天 select last_day(date'2021-12-11') from dual; --2021/12/31 --获得传入日期所在周的周日（周日为每周第一天） select trunc(date'2021-12-11','d') from dual; --2021/12/05 周日 --获得传入日期所在周的周一 select trunc(date'2021-12-11','d')+1 from dual; --2021/12/06 周一 --获得VARCHAR类型的 当前日期的上一个月,格式为YYYY-MM select to_char(add_months(sysdate,-1),'yyyy-mm') from dual; --2021-11 -
空值判断，条件判断
--nvl(字段,默认值) 若字段为空，取默认值；不为空，则取字段值即可 例如：oracle中，数字和null值相加时，结果为null，所以需要对空值做处理，这时就可以使用nvl SELECT EMPNO,ENAME,JOB,SAL,COMM,SAL + NVL(COMM) ALL_SAL FROM EMP; --coalesce(值1,值2,值3,...,默认值) 获取第一个不为空的值；函数里的数据类型必须与第一个值的数据类型保持一致，即值2、值3、...、默认值的数据类型都必须与值1的数据类型一致 SELECT EMPNO,ENAME,JOB,SAL,COMM,COALESCE(SAL,COMM,0) FROM EMP; --case when 条件 then 值 when 条件 then 值 ... else 值 end --与if else逻辑相同，如果满足这个条件，则...;如果满足另一个条件,则...;都不满足，则... --例：将EMP表里的comm编号作非空处理，空值取0 SELECT EMPNO,ENAME,JOB,SAL,COMM,CASE WHEN COMM IS NULL THEN 0 ELSE COMM END FROM EMP; --例：统计班级里男女同学的个数(student表) SELECT SUM(CASE WHEN SSEX = '男' THEN 1 ELSE 0 END) cnt_boy ,SUM(CASE WHEN SSEX = '女' THEN 1 ELSE 0 END) cnt_girl FROM STUDENT; --decode(expession, search_1, result_1, search_2, result_2, default) --解码，对于表达式结果，若匹配search_1，则返回result_1,若匹配search_2，则返回result_2，default为默认值，若未指定默认值，则默认值为空 SELECT EMPNO,ENAME,JOB,SAL,COMM,DECODE(COMM,NULL,0,SAL) FROM EMP; -
开窗函数over()
--将查询结果按字段分区、排序，划分窗口，然后配合聚合函数进行计算 --例：查询每门课程的学生成绩，将每门课程的学生成绩按从高到低排列，并显示名次（SCORE表） SELECT SNO,CNO,DEGREE,ROW_NUMBER()OVER(PARTITION BY CNO ORDER BY DEGREE DESC) RN FROM SCORE; --例：查询每门课程的学生成绩，将成绩按从高到低排列，同时，显示该门课程的平均成绩 SELECT SNO,CNO,DEGREE ,AVG(DEGREE)OVER(PARTITION BY CNO ORDER BY DEGREE DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AVG_DEGREE FROM SCORE; --OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化。 CURRENT ROW：当前行 n PRECEDING：往前n行数据 n FOLLOWING：往后n行数据 UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED FOLLOWING表示到后面的终点 -
行转列，列转行
--行转列 行值转换为列字段 先 group by,再使用聚合函数生成列 --转之前 stu_score sid course score '101' 数学 98 '101' 语文 99 '101' 英语 92 '102' 数学 73 '102' 语文 87 '102' 英语 56 -转之后 sid shuxue yuwen yingyu '101' 98 99 92 '102' 73 87 56 --方法： group by sid 之后使用 sum(case when course = '数学' then score else 0 end) as '数学' ,sum(case when course = '语文' then score else 0 end) as '语文' ,sum(case when course = '英语' then score else 0 end) as '英语' 实现行转列 --列转行 使用union all，先拿列名，再拿列数据，组成一行，根据需要再进行排序 select sid, '数学' course, shuxue from stu_score union all select sid, '数学' course, yuwen from stu_score union all select sid, '数学' course, yingyu from stu_score ; --使用专用函数进行行转列，列转行 pivot(...) --行转列 unpivot(...) --列转行 -
其他
sum()、 avg()…
五、常用操作
-
表授权，视图的级联授权，收回权限
--将表授权给其他用户 将表查询权限授权给用户，需在有授权权限的用户下执行授权语句 --GRANT SELECT ON 表名 TO 用户; GRANT SELECT ON C##DONG.STUDENT TO C##TEST; --视图授权时，需授予级联权限，即给用户授予查询此表的权限 和 用户给其他用户授权此表的权限 GRANT SELECT ON EMP_V TO C##TEST WITH GRANT OPTION; --授权后C##TEST用户拥有此表的查询权限 和 将此表的查询权限授予其他用户的权限 --权限回收 REVOKE SELECT ON 被授权对象 FROM 被授权用户; REVOKE SELECT ON C##DONG.STUDENT FROM C##TEST; -
查依赖（影响分析）
--视图的来源表、存过里包含的来源表等 REFERENCED_OWNER为参考用户，REFERENCED_NAME为参考对象的名称，查询出来的OWNER,NAME 为受影响的对象 SELECT * FROM DBA_DEPENDENCIES WHERE REFERENCED_OWNER = 'C##DONG' AND REFERENCED_NAME = 'EMP'; -
查表或视图授权给哪些用户
-- C##DONG 为用户名，EMP_V为表名,即查询C##DONG.EMP_V授权给了哪些用户 SELECT * FROM DBA_TAB_PRIVS WHERE OWNER = 'C##DONG' AND TABLE_NAME = 'EMP_V'; -
ORACLE自带的数据字典的使用
--DBA_*:可以访问数据库中所有的对象，前提是有DBA权限 --ALL_*:可以访问当前用户拥有的所有对象 和 当前用户有访问权限的所有对象 --USER_*:可以访问当前用户拥有的所有对象 SELECT * FROM DBA_TABLES; --查询当前实例（"数据库"）下的所有表 SELECT * FROM ALL_TABLES; --查询当前用户下的所有表 和 当前用户有查询权限的其它表 SELECT * FROM USER_TABLES; --查询当前用户下的所有表 SELECT * FROM DBA_VIEWS; --查询当前实例（"数据库"）下的所有视图 SELECT * FROM ALL_VIEWS; --查询所有用户下的所有视图 和 当前用户有查询权限的其它视图 SELECT * FROM USER_VIEWS; --查询当前用户下的所有视图 --USER_TAB_COLSB 比 USER_TAB_COLUMNS多几列信息 SELECT * FROM DBA_TAB_COLS; --查询当前实例（"数据库"）下的所有表的所有列信息 SELECT * FROM ALL_TAB_COLS; --查询当前用户下的所有表的所有列信息 和 当前用户有查询权限的其它表的所有列信息 SELECT * FROM USER_TAB_COLS; --查询当前用户下的所有表的所有列信息 SELECT * FROM DBA_TAB_COMMENTS; --查询当前实例（"数据库"）下的所有表的中文注释 SELECT * FROM ALL_TAB_COMMENTS; --查询当前用户下的所有表的中文注释 和 当前用户有查询权限的其它表的中文注释 SELECT * FROM USER_TAB_COMMENTS; --查询当前用户下的所有表的中文注释 SELECT * FROM DBA_COL_COMMENTS; --查询当前实例（"数据库"）下的所有表的所有列的中文注释 SELECT * FROM ALL_COL_COMMENTS; --查询当前用户下的所有表的所有列的中文注释 和 当前用户有查询权限的其它表的所有列的中文注释 SELECT * FROM USER_COL_COMMENTS; --查询当前用户下的所有表的所有列的中文注释 SELECT * FROM USER_SYS_PRIVS; --查询当前用户拥有哪些权限（如：建表、建视图、建立会话...） SELECT * FROM DBA_SYS_PRIVS; --查询所有用户拥有哪些权限（如：建表、建视图、建立会话...） -
DBLINK数据库链接
--DBLINK 作用是使用在一台服务器上的实例中访问另一台服务器上的数据库实例对象 --DBLINK的创建，前提条件是用户拥有创建DBLINK的权限，两服务器端口开通 SELECT * FROM dba_SYS_PRIVS WHERE GRANTEE = 'C##DONG' AND PRIVILEGE LIKE '%DATABASE LINK%'; GRANT CREATE PUBLIC DATABASE LINK TO C##DONG; --授予用户创建DBLINK的权限 --创建语句 PUBLIC为公共链接，即实例下的所有用户都可以使用。DBLINK_A2B为BDLINK的名称，C##DONG为链接登录用户，123456为密码，192.168.12.100为链接实例的IP/域名，ORCLCDB为链接库的实例名称(数据库名) CREATE PUBLIC DATABASE LINK DBLINK_A2B CONNECT TO C##DONG IDENTIFIED BY 123456 USING '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS= (PROTOCOL = tcp) (PROT=1521) (HOST=192.168.12.100) ) ) (CONNECT_DATA = (SERVICE_NAME = ORCLCDB)) )';
六、存储过程
一次定义，多次调用
安全机制（可以自己使用，也可以授权给其他用户使用）
减少网络流量（一个数百行的sql语句，可以通过一条调用语句执行）
--示例
CREATE OR REPLACE PROCEDURE C##DONG.P_INSERT_SCORE_TMP(DATADATE IN VARCHAR, RESULT OUT VARCHAR)
AS
ORDER_DATE DATE; --跑批日期
ROW_NUM NUMBER; --插入条数
BEGIN
ORDER_DATE := TO_DATE(DATADATE,'YYYYMMDD');
EXECUTE IMMEDIATE 'TRUNCATE TABLE SCORE_TMP';
INSERT INTO C##DONG.SCORE_TMP(
SNO
,CNO
,DEGREE
,STAT_DT
)
SELECT
SNO
,CNO
,DEGREE
,ORDER_DATE
FROM C##DONG.SCORE;
ROW_NUM := SQL%ROWCOUNT; --获取改动的数据条数
COMMIT;
RESULT := '0'; --跑批成功标识 0成功 1失败
EXCEPTION WHEN OTHERS THEN --异常处理
RESULT := '1';
DBMS_OUTPUT.PUT_LINE('插入失败'); --自定义控制台输出
END;
七、附件（示例中用到的表结构、数据）
-- Create table
create table STUDENT
(
sno VARCHAR2(20),
sname VARCHAR2(20),
ssex VARCHAR2(20),
sbrithday VARCHAR2(20),
class VARCHAR2(20)
);
-- Add comments to the columns
comment on column STUDENT.sno
is '学生编号';
comment on column STUDENT.sname
is '学生姓名';
comment on column STUDENT.ssex
is '学生性别';
comment on column STUDENT.sbrithday
is '学生出生日期';
comment on column STUDENT.class
is '班级';
insert into student (SNO, SNAME, SSEX, SBRITHDAY, CLASS)
values ('113', '张三', '男', '1999/09/09', '95033');
insert into student (SNO, SNAME, SSEX, SBRITHDAY, CLASS)
values ('106', '李四', '男', '2000/09/01', '95033');
insert into student (SNO, SNAME, SSEX, SBRITHDAY, CLASS)
values ('108', '曾华', '男', '1977/9/1 星期四', '95033');
insert into student (SNO, SNAME, SSEX, SBRITHDAY, CLASS)
values ('105', '匡明', '男', '1975/10/2 星期四', '95031');
insert into student (SNO, SNAME, SSEX, SBRITHDAY, CLASS)
values ('107', '王丽', '女', '1976/1/23 星期五', '95033');
insert into student (SNO, SNAME, SSEX, SBRITHDAY, CLASS)
values ('109', '王芳', '女', '1975/2/10 星期一', '95031');
insert into student (SNO, SNAME, SSEX, SBRITHDAY, CLASS)
values ('103', '陆君', '男', '1974/6/3 星期一', '95031');
commit;
create table SCORE
(
sno VARCHAR2(20),
cno VARCHAR2(20),
degree VARCHAR2(20)
);
-- Add comments to the columns
comment on column SCORE.sno
is '学生编号';
comment on column SCORE.cno
is '课程编号';
comment on column SCORE.degree
is '分数';
insert into score (SNO, CNO, DEGREE)
values ('103', '3-245', '86');
insert into score (SNO, CNO, DEGREE)
values ('105', '3-245', '75');
insert into score (SNO, CNO, DEGREE)
values ('109', '3-245', '68');
insert into score (SNO, CNO, DEGREE)
values ('103', '3-105', '92');
insert into score (SNO, CNO, DEGREE)
values ('105', '3-105', '88');
insert into score (SNO, CNO, DEGREE)
values ('109', '3-105', '76');
insert into score (SNO, CNO, DEGREE)
values ('101', '3-105', '64');
insert into score (SNO, CNO, DEGREE)
values ('107', '3-105', '91');
insert into score (SNO, CNO, DEGREE)
values ('108', '3-105', '78');
insert into score (SNO, CNO, DEGREE)
values ('101', '3-166', '85');
insert into score (SNO, CNO, DEGREE)
values ('107', '3-106', '79');
insert into score (SNO, CNO, DEGREE)
values ('108', '3-166', '81');
commit;
create table DEPT
(
deptno NUMBER(2) not null,
dname VARCHAR2(14),
loc VARCHAR2(13)
);
-- Add comments to the columns
comment on column DEPT.deptno
is '部门编号';
comment on column DEPT.dname
is '部门名称';
comment on column DEPT.loc
is '地点';
insert into dept (DEPTNO, DNAME, LOC)
values (10, 'ACCOUNTING', 'NEW YORK');
insert into dept (DEPTNO, DNAME, LOC)
values (20, 'RESEARCH', 'DALLAS');
insert into dept (DEPTNO, DNAME, LOC)
values (30, 'SALES', 'CHICAGO');
insert into dept (DEPTNO, DNAME, LOC)
values (40, 'OPERATIONS', 'BOSTON ');
commit;
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7369, 'SMITH', 'CLERK', 7902, to_date('01-07-2021', 'dd-mm-yyyy'), 800.00, null, 20);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7499, 'ALLEN', 'SALESMAN', 7698, to_date('03-07-2021', 'dd-mm-yyyy'), 1600.00, 300.00, 30);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7521, 'WARD', 'SALESMAN', 7698, to_date('05-07-2021', 'dd-mm-yyyy'), 1250.00, 500.00, 30);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7566, 'JONES', 'MANAGER', 7839, to_date('04-07-2021', 'dd-mm-yyyy'), 2975.00, null, 20);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7654, 'MARTIN', 'SALESMAN', 7698, to_date('08-07-2021', 'dd-mm-yyyy'), 1250.00, 1400.00, 30);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7698, 'BLAKE', 'MANAGER', 7839, to_date('06-07-2021', 'dd-mm-yyyy'), 2850.00, null, 30);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7782, 'CLARK', 'MANAGER', 7839, to_date('03-07-2021', 'dd-mm-yyyy'), 2450.00, null, 10);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7788, 'SCOTT', 'ANALYST', 7566, to_date('02-07-2021', 'dd-mm-yyyy'), 3000.00, null, 20);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7839, 'KING', 'PRESIDENT', null, to_date('01-07-2021', 'dd-mm-yyyy'), 5000.00, null, 10);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7844, 'TURNER', 'SALESMAN', 7698, to_date('10-07-2021', 'dd-mm-yyyy'), 1500.00, 0.00, 30);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7876, 'ADAMS', 'CLERK', 7788, to_date('08-07-2021', 'dd-mm-yyyy'), 1100.00, null, 20);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7900, 'JAMES', 'CLERK', 7698, to_date('06-07-2021', 'dd-mm-yyyy'), 950.00, null, 30);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7902, 'FORD', 'ANALYST', 7566, to_date('05-07-2021', 'dd-mm-yyyy'), 3000.00, null, 20);
commit;
1751
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
