Oracle常用语句

一、表注释

1、添加表注释
comment on table scheduler_table is '调度表'
2、删除表注释
comment on table scheduler_table is ''

3、查询表注释
select * from user_tab_comments where table_name='SCHEDULER_TABLE'

4、 添加字段注释
comment on column scheduler_table.id is '主键值';
5、查询表字段注释
select * from user_col_comments where table_name='SCHEDULER_TABLE'

6、查询数据库中的所有表

select * from all_tables

7、查询数据库中的所有用户

select * from all_users

8、查询数据库中的见表语句，表名RESOUCE要大写

SELECT DBMS_METADATA.GET_DDL('TABLE','RESOUCE') from DUAL;

二、DDL语句

1、创建表

CREATE TABLE employee(
id NUMBER(4),
name VARCHAR2(20) NOT NULL,
gender CHAR(1) DEFAULT 'M',
birth DATE,
salary NUMBER(30),
job VARCHAR2(30),
deptno NUMBER(2)

)

2、查询表结构

desc  employee

3、修改表名

 rename employee to emp1008

修改后查询表结构

desc  emp1008

4、添加新字段

 alter table employee add hiredate date  default sysdate

5、修改字段类型，例如：将job字段的长度由20变为40

 alter table employee modify job varchar2(40) 

6、删除字段

 alter table  employee  drop (hiredate)

 

三、字符串

1、字符串连接 concat 、"||"

CONCAT（ch1，ch2），ch1、ch2是两个用于连接的字符串

 select concat('hello','world') from dual;

  select 'hello' || 'world'from dual

2、length：返回字符串的长度

  select length('hello world') from dual

3、大小写转换

UPPER(char)：用于将字符转换为大写形式

LOWER(char)：用于将字符转换为小写形式

INITCAP(char)：用于将字符串中每个单词的首字母大写，其他字符小写

  select upper('hello world') from dual

  select lower('HELLO WORLD') from dual

  select initcap('HELLO WORLD') from dual

4、去除字符串中的空格

--去除左侧空格
select ltrim(' hello world ') from dual
-- 去除右侧空格
select rtrim(' hello world ') from dual
-- 去除左右两侧空格
select trim(' hello world ') from dual

5、截取字符串

SUBSTR（char , m,,n） 用于获取字符串的子串，返回char中从m位开始取n个字符

如果m = 0，则从首字符开始，如果m取负数，则从尾部开始

如果没有设置n，或者n的长度超过了char的长度，则取到字符串末尾为止

下面两条语句运行结果都为hel

 select substr('hello world',1,3) from dual

 select substr('hello world',0,3) from dual

6、instr

INSTR（char1,char2,n,m）函数
查找char2在char1中的位置
n为从第几个字符开始检索
m为第几次出现
n,m不写则默认都是1

SELECT INSTR('THINKING IN JAVA','IN',3，1) FROM dual        

7、lpad,rpad 补位函数  

lpad(原始字符，填充后长度，填充字符)

RPAD(原始字符，填充后长度，填充字符)

实现右对齐： SELECT LPAD(sal,5,' ') FROM empoyee

实现左对齐： SELECT RPAD(sal,5,' ') FROM empoyee

 

四、数值函数

1、round(n,m)，四舍五入，n表示要处理的数值，m表示四舍五入到小数点后m位

select round(56.28369,3) from dual --56.284
-- m为空表示0为
select round(56.28369) from dual --56

2、trunc(n,m) 截取数字

select trunc(56.2836,3) from dual -- 56.283

3、mod(n,m) 求余

 select mod(3,2) from dual

4、ceil(n) 向上取整

 select ceil(3.26) from dual --4

5、floor(n) 向下取整

  select floor(3.26) from dual --3

 

五、日期函数

1、获取当前时间

  select systimestamp from dual  --08-10月-23 02.15.08.934000000 下午 +08:00

  select sysdate from dual --08-10月-23

2、TO_DATE

TO_DATE(char,fmt , nlsparam)：将字符串按照定制格式转换为日期类型。
char要转换的字符串
fmt：格式
常用格式

YY 2位数字的年份
YYYY 4位数字的年份
MM 2位数字的月份
MON 简拼的月份
MONTH 全拼的月份
DD 2位数字的天
DY 周几的缩写
DAY 周几的全拼
HH24 24小时制的小时
HH12 12小时
MI 显示分钟
SS 显示秒
  select to_date('2023-10-08 14:20:36','YYYY-MM-DD HH24:MI:SS') from dual

 

3、TO_CHAR将其他类型的数据转换为字符串类型。

  select to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss')  from dual  --2023-10-08 14:23:06

4、LAST_DAY(date) ： 返回日期date所在月的最后一天

  select last_day(systimestamp) from dual

5、ADD_MONTHS(date,i)：返回日期date加上i个月后的日期值

  select add_months(systimestamp,3) from dual

6、MONTHS_BETWEEN(date1,date2)：计算date1和date2两个日期值之间间隔了多少个月

   select months_between('08-1月 -24',sysdate)  from dual 

7、NEXT_DAY(date,char)：返回date日期数据的下一个周几，周几是由参数char来决定的

1. NEXT_DAY不是明天！！！！
2. 1表示周日，2表示周一，依次类推

  select next_day(sysdate,3) from dual

8、EXTRACT(date FROM datetime) ：从参数datetime中提取参数date指定的数据，比如提取年，月，日。

获取年  

select extract(year from sysdate) from dual

获取月

select extract(month from sysdate) from dual

获取日

select extract(day from sysdate) from dual

获取小时

select extract(hour from systimestamp) from dual

获取分

select extract(minute from systimestamp) from dual

获取秒

select extract(second from systimestamp) from dual

 

六、表空间

创建表空间
语法格式如下：

CREATE TABLESPACE tablespace_name
DATAFILE 'path/to/datafile' SIZE file_size [ REUSE ]
[ EXTENT MANAGEMENT <LOCAL | DICTIONARY> ]
[ AUTOEXTEND ON NEXT auto_extend_size MAXSIZE max_size ]
[ DEFAULT STORAGE (storage_clause)]
[ LOGGING | NOLOGGING]
[ ONLINE | OFFLINE]
[ PERMANENT | TEMPORARY]
[ SEGMENT SPACE MANAGEMENT { MANUAL | AUTO } ];

各个参数含义如下：

tablespace_name:创建的表空间名称，即新表空间的名字。
DATAFILE: 物理文件路径及名称，这个文件将作为表空间的数据容器（可以有多个），用于指定数据文件的路径和文件名。
SIZE: 指定新创建的数据文件的初始大小，默认单位为 M。。
REUSE: 若数据文件已存在，可使用 REUSE 参数来重复利用该数据文件，不做任何命名修改操作。
EXTENT MANAGEMENT: 指定表空间的分配方式。DICTIONARY 表示使用数据字典管理， LOCAL 表示使用局部管理方式，可以继承 DEFAULT 数据库设置。
AUTOEXTEND: 对于需要自动扩展的数据文件，启用自动扩展功能。
NEXT: 指定自动扩展的下一个增量大小，默认单位为 M。
MAXSIZE: 数据文件最大允许的大小限制，默认也是以 M 作为单位。
DEFAULT STORAGE (storage_clause)：指定表空间的默认存储属性。
LOGGING：设置并激活日志记录，将数据更改所导致的事务记录在 Oracle 的重做日志文件中。
NOLOGGING：不启用日志记录，不写事务日志，提高性能。
ONLINE：允许并发连接到表空间中的对象，在创建表空间时，将其设置为在线状态。
OFFLINE ：禁止对表空间的所有活动，在创建表空间时，将其设置为离线状态。
PERMANENT：创建永久表空间。
TEMPORARY：创建临时表空间。
SEGMENT SPACE MANAGEMENT: 指定表空间中的段管理方式，MANUAL 表示手动管理，AUTO 表示自动管理。

例1.创建一个名为 USERS 的用户表空间，存储在目录 /u01/app/oracle/oradata/db1/ 下的 users01.dbf 文件中，初始大小为 100MB，启用自动扩展功能，每次自动以 10MB 为单位增加文件的容量，最大不能超过 2GB

create tablespace users
datafile '/u01/app/oracle/oradata/db1/users01.dbf' SIZE 100M
extent management local
autoextend on next 10M maxsize 2G

例2、创建具备自动扩展功能的数据文件，初始大小为 100 MB，每次增加 10 MB，最大值不超过 500 MB 的“my_datafile.dbf”路径下的表空间“my_tablespace”

create tablespace my_tablespace
datafile 'C:\oracle\product\12.2.0\dbhome_1\database\my_datafile.dbf' SIZE 100M
autoextend on next 10M maxsize 500M

例3、创建一个名为 `my_tablespace` 的永久表空间，数据文件路径为 `/u01/app/oracle/oradata/mydb/my_tablespace.dbf`，大小为 100M，启用了自动扩展功能，每次自动扩展 50M，最大大小为 500M，使用默认的存储属性，设置为在线状态

CREATE TABLESPACE my_tablespace
DATAFILE '/u01/app/oracle/oradata/mydb/my_tablespace.dbf' SIZE 100M
AUTOEXTEND ON NEXT 50M MAXSIZE 500M
DEFAULT STORAGE (INITIAL 64K NEXT 64K)
ONLINE
PERMANENT;