oracle常用sql语句

26711

名词介绍

DDL

（Data Definition Languages）数据定义语言。定义了不同的数据段、数据库、表、列等数据库对象。包括create,drop,alter等。

DML

(Data Manipulation Language)数据操纵语句，用于对于数据库记录进行增删改查，并检查数据完整性。包insert,delete,update,select等。

DCL

(Data Control Language)数据控制语句。定义了数据库、表、字段、用户的访问权限和安全级别。包括grant,revoke等。

sqlplus操作

用户连接登录

--dba登录 这是典型的操作系统认证，不需要listener进程
[oracle@localhost ~]$sqlplus / as sysdba    

--本地登录 这种连接方式只能连接本机数据库，同样不需要listener进程,用户必须是可用状态
[oracle@localhost ~]$sqlplus username/password    

--网络连接登录 需要listener进程处于可用状态 数据库必需处于open状态 最普遍的通过网络连接
sqlplus 用户/用户密码@IP地址:1521/orcl

切换用户

SQL>connect system/123456 as sysdba
SQL>connect username/password   --切换普工用户
SQL>connect / as sysdba  --切换到dba

查看当前用户

SQL>show user;

乱码解决

1.查看当前数据库字符集
SQL> show parameter nls_la
NAME                                TYPE       VALUE
------------------------------------ -----------------------------------------
nls_language                        string      SIMPLIFIEDCHINESE
2.临时修改
[oracle@rac2 ~]$ export NLS_LANG=american_america.zhs16gbk
3.永久修改
如果要使之永久生效，可以添加这个变量到oracle 用户环境 .bash_profile中。

格式化美观显示

设置列宽：使用COLUMN命令来设置每列的宽度。例如：COLUMN column_name new_width。

设置行高：使用SET LINESIZE命令来设置每行的高度。例如：SET LINESIZE 32767。

取消标题：使用SET HEADING OFF命令来取消标题的显示。

取消列名和行号的显示：使用SET PAGESIZE 0命令来取消列名和行号的显示。

设置显示格式：使用SET LINESPOOL ON命令来开启行缓冲，以便更快地滚动数据。同时，可以使用SET PAGESIZE n命令来设置每页显示的行数。
以下命令将设置每行高度为1000，取消标题和行号的显示，并开启行缓冲
SET LINESIZE 1000
SET HEADING OFF
SET PAGESIZE 0
SET LINESPOOL ON

表空间操作

--------------------查询--------------------
查某个用户属于哪个表空间
select username,default_tablespace from dba_users dba_users where username='用户名' 
查询系统默认表空间的位置
select * from dba_data_files;
select * from dba_temp_files; --查询默认临时表空间位置

--------------------创建--------------------
--创建临时表空间
create temporary tablespace wjlsbkj --  wjlsbkj临时表空间名字
tempfile '/opt/oracle/oradata/ORCLCDB/wjlsbkj.dbf'  --数据文件的路径和名称 可以先查询默认系统表空间的位置
size 10m --表空间的初始大小，单位为MB
autoextend on --自动增长
next 10m maxsize 20480m --每次增加1M，最大增加到2048M
extent management local; --本地管理

--创建表空间
create tablespace wjbkj --wjbkj表空间名字
datafile '/opt/oracle/oradata/ORCLCDB/wjbkj.dbf' --数据文件的路径和名称
size 10m --表空间的初始大小，单位为MB
autoextend on --自动增长
next 10m maxsize 20480m --每次增加1M，最大增加到2048M
extent management local; --本地管理



--------------------删除--------------------
DROP TABLESPACE wjlsbkj INCLUDING CONTENTS AND DATAFILES;  --wjlsbkj指定表空间名字即可
DROP TABLESPACE wjbkj INCLUDING CONTENTS AND DATAFILES; --wjbkj指定表空间名字即可

表操作

备份还原表

--同时备份表结构和表数据：
create table 新表名 as select * from 旧表名

--只备份表结构：
create table 新表名 as select * from 旧表名 where 1=2

--还原表
方式1：
insert into 现表 select * from 备份表;
方式2：
delete 旧表名; #当前旧表的数据
create table 旧表名 as select * from 之前备份的表

表名操作

--表名修改
alter table 表名 rename to 新表名

--表添加注释
comment on table "NWYDPT"."LOGIN_LOCKDEVICE_USER"  is '用户表'

表字段操作

--添加列字段注释
COMMENT ON COLUMN 表名.字段 is 字段注释;

--添加一个字段
alter table 表名 add  新字段  VARCHAR2(128);

--添加两个字段                                     
alter table 表名 Add (新字段名 number(1,0),新字段名 Varchar2(2));

--删除字段,字段要大写
alter table 表名 drop column "ID"

--修改字段
alter table 表名 rename column 旧字段名 to 新字段名;

--修改字段属性                 新的属性   
alter table 表名 modify 字段名 VARCHAR2(2000);
alter table 表名 modify 字段名 date
alter table 表名 modify 字段名 TIMESTAMP

--修改字段值
update 表名 set 字段 = 新值 where [id=1]
update 表名 set 字段 = to_date('2018-09-01','yyyy-mm-dd') where id=1
update 表名 set 字段 = '13-10月-19 03.33.59.000000000' where id=3
<!-- wp:code -->
<pre class="wp-block-code"><code>--------------------查询表数据--------------------
--查询当前用户有多少表
此方式查询，可能会算到被删除的表，结果可能不是特别的准确
select count(*) from tab
此方式查询的结果效果是最好的
select * from tab

--------------------创建表--------------------
--学生表
drop table students;
create table students(
    student_id NUMBER PRIMARY KEY, -- 学生ID，主键
    student_name VARCHAR2(50), -- 学生姓名
    student_class VARCHAR2(50), -- 学生所在班级
    student_teacher VARCHAR2(50), -- 学生所属老师
    student_school VARCHAR2(50),  -- 学生所属学校
    student_district VARCHAR2(50), -- 学生所属市区
    student_province VARCHAR2(50), -- 学生所属省份
    student_country VARCHAR2(50), -- 学生所属国家
    student_enrollment_date DATE  DEFAULT SYSDATE -- 学生入学时间
);
--老师表
drop table teachers;
CREATE TABLE teachers (
  teacher_id NUMBER PRIMARY KEY, -- 老师ID，主键
  teacher_name VARCHAR2(50) NOT NULL, -- 老师姓名，不能为空
  teacher_age NUMBER(3), -- 老师年龄
  teacher_class VARCHAR2(50) -- 老师所教班级
);

--------------------插入数据--------------------
--学生数据
INSERT INTO students (student_id, student_name, student_class, student_teacher, student_school, student_district, student_province, student_country, student_enrollment_date)
VALUES (1, '张三', '一班', '李老师', '清华大学', '海淀区', '北京市', '中国', TO_DATE('2022-06-01', 'YYYY-MM-DD'));
INSERT INTO students (student_id, student_name, student_class, student_teacher, student_school, student_district, student_province, student_country, student_enrollment_date)
VALUES (2, '李四', '二班', '王老师', '北京大学', '朝阳区', '北京市', '中国', TO_DATE('2022-07-01', 'YYYY-MM-DD'))
--老师数据
INSERT INTO teachers (teacher_id, teacher_name, teacher_age, teacher_class)
VALUES (1, '张老师', 35, '一年级一班');
INSERT INTO teachers (teacher_id, teacher_name, teacher_age, teacher_class)
VALUES (2, '李老师', 40, '二年级二班');

--小技巧
--全字段插入,列名可以忽略不写
insert into 表名 values(值1,值2);
--指定字段插入
insert into 表名 (列名1,列名2) values(值1,值2);
小技巧：
如果字段是oracle关键字，需要加双引号，才能插入数据。

--------------------添加注释--------------------
--学生表注释
COMMENT ON TABLE students IS '学生表信息';  --为表添加注释
--表的字段添加注释
COMMENT ON COLUMN students.student_id IS '学生ID';
COMMENT ON COLUMN students.student_name IS '学生姓名';
COMMENT ON COLUMN students.student_class IS '学生所在班级';
COMMENT ON COLUMN students.student_teacher IS '学生所属老师';
COMMENT ON COLUMN students.student_school IS '学生所属学校';
COMMENT ON COLUMN students.student_district IS '学生所属市区';
COMMENT ON COLUMN students.student_province IS '学生所属省份';
COMMENT ON COLUMN students.student_country IS '学生所属国家';
COMMENT ON COLUMN students.student_enrollment_date IS '学生入学时间';

--老师表注释
COMMENT ON TABLE teachers IS '老师表';
COMMENT ON COLUMN teachers.teacher_id IS '老师ID';
COMMENT ON COLUMN teachers.teacher_name IS '老师姓名';
COMMENT ON COLUMN teachers.teacher_age IS '老师年龄';
COMMENT ON COLUMN teachers.teacher_class IS '老师所教班级';


-- 查看表的注释
SELECT table_name, comments
FROM user_tab_comments
WHERE table_name = 'STUDENTS';

--查看表的字段有没有注释
SELECT column_name, comments
FROM user_col_comments
WHERE table_name = 'STUDENTS' AND column_name = 'STUDENT_ID';


--------------------删除表--------------------

--表和数据都删除,并未真正的删除，只是放在数据库回收站了
drop table 表名;
--删除数据,保留表
delete 表名;
--从回收站还原已删除的表名
flashback table 表名 to before drop;

</code></pre>
<!-- /wp:code -->

时间类查询

查询系统时间

SELECT SYSDATE FROM dual
SELECT TO_DATE('2023-07-04 12:34:56', 'YYYY-MM-DD HH24:MI:SS') FROM dual;

查询特定日期范围内的数据

  • 时间之间
SELECT * FROM 表名
WHERE 时间列名 BETWEEN TO_DATE('2023-01-01', 'YYYY-MM-DD') AND TO_DATE('2023-12-31', 'YYYY-MM-DD');
  • 大于 等于 小于
SELECT * FROM your_table
WHERE your_date_column > TO_DATE('2023-07-04', 'YYYY-MM-DD');
  • 排除一个连续的日期区间（包含起止日期）
SELECT * FROM your_table
WHERE NOT (your_date_column BETWEEN TO_DATE('start_date', 'YYYY-MM-DD') AND TO_DATE('end_date', 'YYYY-MM-DD'));

上述语句将返回your_date_column不在'start_date'和'end_date'之间的所有记录

不在某个时间 排除两个离散的特定日期（仅限给出的两个值）

SELECT * FROM your_table
WHERE your_date_column NOT IN (TO_DATE('2023-07-04', 'YYYY-MM-DD'), TO_DATE('2023-07-05', 'YYYY-MM-DD'));

上述语句将返回your_date_column不在'2023-07-04'和'2023-07-05'之间的所有记录。

包含某个时间范围

SELECT * FROM your_table
WHERE EXTRACT(YEAR FROM your_date_column) = 2023
   OR EXTRACT(MONTH FROM your_date_column) = 7
   OR EXTRACT(DAY FROM your_date_column) = 4;

上述语句将返回your_date_column的年份为2023、月份为7或日期为4的所有记录。

按日期进行分组并计算每个日期的统计信息

SELECT TO_CHAR(时间列名, 'YYYY-MM-DD') AS date, COUNT(*) AS count
FROM 表名
GROUP BY TO_CHAR(时间列名, 'YYYY-MM-DD');

时间转换函数

TO_DATE函数用于将字符串转换为日期类型。它接受两个参数：要转换的字符串和表示日期格式的字符串。例如：

SELECT TO_DATE('2023-07-04', 'YYYY-MM-DD') FROM dual;
上述语句将字符串'2023-07-04'转换为日期类型。

TO_CHAR函数用于将日期类型转换为字符串。它接受两个参数：要转换的日期和表示日期格式的字符串。例如：

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM dual;
上述语句将当前日期（SYSDATE）转换为字符串，并使用格式'YYYY-MM-DD HH24:MI:SS'进行格式化。

总结起来，TO_DATE函数用于将字符串转换为日期类型，而TO_CHAR函数用于将日期类型转换为字符串。

用户操作

--------------------查询--------------------

--查询某个用户是否存在,用户名字必须大写,且是单引号
select * from all_users where username = 'APPSTORE'
--查询数据库有多少用户
select count(*) from all_users;

--------------------修改--------------------
--修改用户密码
SQL>alter user 用户名 identified by 用户密码;
--解锁用户
alter user system account unlock;

--------------------删除--------------------
drop user wjdb cascade; --删除用户wjdb
说明： 删除了user，只是删除了该user下的schema objects，是不会删除相应的tablespace的。

--------------------创建用户--------------------
非容器创建用户及PDB下创建用户
如果密码有!  之类的特殊符号 ，需要加双引号

--只创建用户，如果不指定表空间，则使用默认表空间
create user wjdb identified by wangjian;

--创建用户 指定表空间  表空间名字必须大写 必须大写  必须大写

CREATE USER wjdb IDENTIFIED BY wangjian --创建wjdb用户，密码是wangjian
DEFAULT TABLESPACE "WJBKJ" --指定表空间位置
TEMPORARY TABLESPACE "WJLSBKJ"; --指定临时表空间

--------------------授权用户--------------------
--授权,根据实际情况，按需分配
grant CREATE SESSION to wjdb; --如果不授权，用户不能登录
grant CREATE TABLE to wjdb; --创建表的权限
grant unlimited tablespace to wjdb; --允许用户使用表空间，不执行不然无法插入表数据 
grant CREATE VIEW to wjdb; --授权创建视图的权限

grant dba to wjdb;  --直接授权DBA权限  生产环境慎用


--取消授权
REVOKE CREATE SESSION FROM wjdb; --取消授权session的权限，取消其他权限方式一样
REVOKE unlimited tablespace FROM wjdb --取消插入数据的权限  ORA-01950: 对表空间 'WJBKJ' 无权限


权限解释说明
CREATE SEQUENCE 权限：如果需要创建序列（sequence），则需要授予该权限。
CREATE PROCEDURE 和 CREATE FUNCTION 权限：如果需要创建存储过程（procedure）或函数（function），则需要授予该权限。
CREATE TRIGGER 权限：如果需要创建触发器（trigger），则需要授予该权限。
CREATE VIEW 权限：如果需要创建视图（view），则需要授予该权限。
ALTER SESSION 权限：该权限允许用户修改自己的会话设置，例如更改密码等。

视图索引

在Oracle中，视图是一种虚拟的表，它是由查询语句生成的。视图本身不存储数据，而是从基表中获取数据。视图可以用于简化复杂查询，提高数据安全性和保密性，以及控制用户对数据的访问。视图可以像普通表一样使用，并且可以使用常规的SQL语句进行操作。

索引是一种数据库对象，它可以帮助快速访问数据库表中的数据。索引可以加快查询速度，但是如果不合理地创建和使用索引，反而会降低系统的性能。在Oracle中，可以为单个列或多个列创建索引。

视图和索引之间的主要区别在于它们的目的和实现方式。视图是从一个或多个表中导出的虚拟表，而索引则是用于加速对表中数据的访问。视图可以根据需要动态生成，而索引则需要手动创建和维护。

-----------------------------------视图
--创建一个wj_view的视图 ，班级是一班的 学生姓名
CREATE OR REPLACE VIEW wj_view AS SELECT STUDENT_CLASS FROM STUDENTS WHERE STUDENT_CLASS = '一班' ;
--查询视图的数据
select * from wj_view;

--删除视图
DROP VIEW wj_view;

----------------------------------索引
--创建一个wj_index的索引，以学生的老师为基础
CREATE INDEX wj_index ON STUDENTS (STUDENT_NAME);

select * from STUDENTS WHERE STUDENT_NAME = '张三'; --这样查询之后速度会变快些

--删除索引
DROP INDEX wj_index;

数据库导入导出

--------------------------相关参数解释------------------------
full=y 是导入文件中全部内容
ignore=y 如果没有的表，创建并倒入数据，如果已经有的表，忽略创建的，但不忽略导入 
fromuser=源库名 
touser=目标库名 
tablespaces=导入到指定表空间
-----------------------命令行方式exp和imp用户数据-----------------------
--exp导出用户数据
--普通用户登录导出wjdb用户的数据(包括索引，但是不包括视图)
[oracle@localhost ~]$ exp wjdb/wangjian@192.168.15.128:1521/ORCLCDB owner=wjdb file=/home/oracle/wjdbputong.dmp log=/home/oracle/wjdbputong.log
--导出全的数据
exp wjdb/wangjian@192.168.15.128:1521/ORCLCDB file=/home/oracle/wjdbfull.dmp log=/home/oracle/wjdbfull.log full=y

--imp导入用户数据
--普通用户登录导入wjdb用户的数据(包括索引，但是不包括视图)
[oracle@localhost ~]$ imp wjdb/wangjian@192.168.15.128:1521/ORCLCDB fromuser=wjdb touser=wjdb  ignore=y file=/home/oracle/wjdbputong.dmp log=/home/oracle/wjdbputong.log



-----------------------命令行方式exp和imp表数据-----------------------
--exp导出表数据
--普通用户指定表的导出  --多个表使用tabels=(students,teachers,其他表名); 
[oracle@localhost ~]$ exp wjdb/wangjian@192.168.15.128:1521/ORCLCDB file=/home/oracle/wjdbbiao.dmp log=/home/oracle/wjdbbiao.log tables=students

--导入表数据
--普通用户指定表的导入,本质就是插入数据，要确保用户有插入的权限，访问表空间没有就授权
[oracle@localhost ~]$ imp wjdb/wangjian@192.168.15.128:1521/ORCLCDB file=/home/oracle/wjdbbiao.dmp log=/home/oracle/wjdbbiao.log tables=students


----------------------------------数据泵工具dpexp和dpimport-----------------------
---------------参数介绍 
directory --导出路径，该名称指向字典DBA_DIRECTORIES中该名称对应的路径
dumpfile --导出文件的名称，后缀为.dmp
logfile --记录导出过程日志，后缀为.log
tables|schemas|tablespaces|full --分别表示导出表、模式（用户）、表空间、全库
content={all|data_only|metadata_only} --分别表示导出数据及定义、仅导出数据、仅导出定义
query=[tb_name:] query_condition --导出表时使用，指定where子句以进行筛选，不能与content连用

-------------普通用户权限登录，导出导入当前用户数据,这个方式的弊端是导入时用户必须存在，同时必须还要赋予权限，不要忘记
--创建导出目录
[oracle@localhost 1]$ mkdir -p /home/oracle/wjdbbak
SQL> CREATE DIRECTORY wjdbbak as '/home/oracle/wjdbbak';
--授权给用户 为防止在导出时遇到权限问题
SQL> GRANT READ,WRITE ON DIRECTORY wjdbbak TO wjdb; 
--使用数据泵导出
[oracle@localhost wjdbbak]$ expdp wjdb/wangjian@192.168.15.128:1521/ORCLCDB directory=wjdbbak dumpfile=wjdb.dmp logfile=wjdb.log

--使用数据泵导入，如果是新建的用户，记得除了赋予创建用户时基础权限外，还要重新赋予导出目录的权限GRANT READ,WRITE ON DIRECTORY wjdbbak TO wjdb; 
[oracle@localhost wjdbbak]$ impdp wjdb/wangjian@192.168.15.128:1521/ORCLCDB directory=wjdbbak dumpfile=wjdb.dmp logfile=wjdbdaoru.log

-------------dba用户权限登录，导出其它用户的数据，这个方式最好，如果用户不存在，会自动创建用户,如果需要表空间，则需要先创建表空间
--导出数据（按照用户）
--创建导出目录
[oracle@localhost 1]$ mkdir -p /home/oracle/wjdbbak
SQL> CREATE DIRECTORY wjdbbak as '/home/oracle/wjdbbak';
--授权给dba用户
SQL> GRANT READ,WRITE ON DIRECTORY wjdbbak TO sys; 
--使用dba用户登录，导出wjdb用户的数据
[oracle@localhost wjdbbak]$ expdp sys/wangjian@192.168.15.128:1521/ORCLCDB directory=wjdbbak dumpfile=wjdbdba.dmp logfile=wjdbdba.log schemas=wjdb
会提示让输入dba信息，输入账号和密码后 直接回车即可
Username: sys as sysdba
Password:

--使用dba用户登录，导入wjdb用户的数据
[oracle@localhost wjdbbak]$ impdp sys/wangjian@192.168.15.128:1521/ORCLCDB directory=wjdbbak dumpfile=wjdbdba.dmp logfile=wjdbdba.log schemas=wjdb transform=segment_attributes:n

同样会提示让输入dba信息，输入账号和密码后 直接回车即可
Username: sys as sysdba
Password:
ORA-31684: Object type USER:"APPSTORE" already exists 会有一个这个报错 是因为我在新库中提前把用户创建了
ORA-39112，是因为要导入的DMP文件包含了一些原来的数据，比如表空间等，而新库中没有，所有报错，要解决这个问题只需加个参数即可。transform=segment_attributes:n表示导入时忽略其他数据，例如表空间

--全量导出  整个库 适合整个库进行迁移
expdp sys/wangjian@192.168.15.128:1521/ORCLCDB directory=wjdbbak dumpfile=wjdbquanl.dmp logfile=wjdbquanl.log full=y
--全量导入 整个库 适合整个库进行迁移
impdp sys/wangjian@192.168.15.128:1521/ORCLCDB directory=wjdbbak dumpfile=wjdbquanl.dmp logfile=wjdbquanll.log full=y

--------相关操作---------
SELECT * FROM DBA_DIRECTORIES; --查看所有路径
DROP DIRECTORY WJDBBAK --删除刚创建的目录

知识点:
注意1：导入时也需要按照导出时，创建的逻辑目录一样，导入前也需要创建一遍。
注意2：impdp命令在导入数据时,如果用户不存在,则会自动创建该用户,因为expdp导出的dmp文件中包含了创建用户的脚本信息(包括密码,缺省表空间,临时表空间等)。
注意3：impdp自动创建用户有一个前提条件，就是需要首先创建用户的缺省表空间和临时表空间，如果缺省表空间或者临时表空间不存在，则自动创建用户会失败，导致导入数据的失败。

格式化结果集

  • 数值格式化
--将字符转为数字
select cast('236' as int) from dual;

© 版权声明
文章版权归作者所有，未经允许请勿转载。
THE END
oracle
# oracle
点赞11 分享