Oracle SQL 071-1 dba/all/user区别及DATE NUMBER

本文链接：https://blog.csdn.net/readfor/article/details/112863296

本文详细介绍了Oracle数据库中的数据对象，包括DDL语句中的表、索引、约束、视图等，并探讨了对象名称空间和数据类型的使用，如NUMBER、DATE及其显示格式。还讨论了DATE类型在不同时区和时间格式下的表现，以及DML操作中的数据类型限制和使用技巧。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Oracle SQL 071-1

各种DB如Oracle/SQLserver/MySQL因为特色虽遵守ISO标准但不兼容

SQL(Structured Query Language)

DDL (Data Definition Language，对对象Object的增删改查ADCL）
DML (Data Manipulation Language，对数据的ADCL语句）
DCL（Data Control Language，对用户角色权限语句）

一、DDL中对象类型（常见类型：有table\index\constraint\view\synonym\sequence）

所有对象：select count(count(object_type)) from dba_objects group by object_type;48
能访问对象：select count(count(object_type)) from all_objects group by object_type;27
拥有的对象：select count(count(object_type)) from user_objects group by object_type;7

所有表：select count(*) from dba_tables;2189
能访问的表：select count(*) from all_tables;171
拥有的表：select count(*) from user_tables;56

所有索引:select count(*) from dba_indexes;2932
能访问的索引:select count(*) from all_indexes;174
拥有的索引:select count(*) from user_indexes;84

所有的表簇select count(*) from dba_clusters;12
所能访问的表簇select count(*) from user_clusters;2
拥有的表簇select count(*) from user_clusters;2
所有的表簇列select count(*) from dba_clu_columns;52
拥有的表簇列select count(*) from user_clu_columns;2(没有all_clu_columns)
所有segment空间:select count(*) from dba_segments;5250(没有all_segments)
拥有的segment空间:select count(*) from user_segments;31

所有表列：select count(*) from dba_tab_columns;114607
能访问的表列：select count(*) from all_tab_columns;23733
拥有的表列：select count(*) from user_tab_columns;755

所有索引列:select count(*) from dba_ind_columns;4981
能访问索引列:select count(*) from all_ind_columns;256
拥有索引列:select count(*) from user_ind_columns;68

1、所有同义词 select * from dba_synonyms; 12212 含公共和私有同义词

2、能访问同义词 select * from all_synonyms; 12194

3、拥有的同义词 select * from user_synonyms; 2

1、所有序列select count(*) from dba_sequences; 311

2、可访问的序列select count(*) from all_sequences;18

3、当前拥有的序列SQL> select count(*) from user_sequences;8

1.select count(*) from dba_constraints; 7504

2.能访问的约束select count(*) from all_constraints; 711

3.拥有约束：select count(*) from user_constraints; 413

1、所有的约束列 select count(*) from dba_cons_columns; 9059
2、能访问的约束列 select count(*) from all_cons_columns;734

3、拥有的约束列 select count(*) from user_cons_columns;

create table a1_$#(a date);select count(*) from user_tables where table_name='A1_$#'; 1
select count(*) from user_tables where table_name='a1_$#' ;0 不过desc 表名是不分大小写的
考点：对象名称可字母数字下划线$#，自动转大写
有版本是对象长度30，desc看sys.obj$z中name的长度128和desc user_tables中的table_name长度128，实测表名长是128，目前我版本是18c

二、DDL中对象名称空间

（常见类型：有table\index\constraint\view\synonym\sequence）

在同一模式中，同一名称空间下不能同名
共享了名称空间的常用对象有(table\view\synonym\sequence)
如create view a1_$# as select * from A1_$#;提示ORA-00955: name is already used by an existing object
有独立名称空间的有（constraint\index\trigger\dblink等），虽然这些可以和其它对象同名但现实不建议

三、DML中表数据类型

desc 对象名（不分大小写）

NUMBER	number(precision精度，scale刻度）没有指定precision和scale，同时不指定P和S时默认1-38，-84至127; 1、P-S大于0，整数超P-S长度限制会报错。S>0，从小数点向右S长度进行舍入；S<0时，从小数点向左归S个0长度舍入。如果number(5,2)输入1234.12会报错;number(5,-4)如果1234567890.123会报错，如果是123456789会变成123460000 2、P-S小于等于0时，整数不是0会报错，小数部分最右边非0的数字长度，超P会报错，如果是number(2,5),0.00012正常，如果输入0.00123会报错；如果输入0.000123会变成0.00012 3、小数部分超S长会自动舍入，（S=0或者小于0，没小数位）因各种舍入导致进位增加了长度的也会受上面约束。如number(5,2)输入999.995,舍入变成1000.00但是1000大于了5-2长度所以也会报错 (备注：已上经过实际验证）
DATE	Date 对date理解,分3种思考模式 1、DATE类型有存储时分秒，to_date进行insert，date用sysdate\timestamp可用systimestamp等作为默认值 2、DATE类型默认展示不含时分秒，只有用to_char显示全 3、时区（操作系统时区、数据库时区、用户进程时区） `create table datetest(id number(10) not null,nyrsfm date,nyr date,dbsysdate date default sysdate,dbsystimestamp timestamp default systimestamp);` `insert into datetest(ID,NYRSFM,NYR)values(1,to_date('20210121164830','yyyymmddhh24miss'),to_date('2021-01-21','yyyy-mm-dd'));` `SQL> select * from datetest; ID NYRSFM NYR DBSYSDATE DBSYSTIMESTAMP 1 21-JAN-21 21-JAN-21 21-JAN-21 21-JAN-21 04.44.19.143312 PM` 考点： selectdate类型默认输出只有年月日，时间戳默认可以显示全， DATE是都存有yyyy-mm-dd hh24:mi:ss，to_date只要满足有年月日就可保存 SQL> select to_char(nyr,'yyyy-mm-dd hh24:mi:ss') from datetest; TO_CHAR(NYR,'YYYY-MM-DDHH24:MI:SS') -------------------------------------- 2021-01-21 00:00:00 *SQL> select from datetest where to_char(NYR,'yyyy-mm-dd')='2021-01-21'; SQL> select * from datetest where NYR=to_date('2021-01-21','yyyy-mm-dd'); 两边保持一致就可以查询了，也支持>=< trunc是个神奇的函数，后期再看，对DATE可以获取各类的第一天和NUMBER都可以处理** 只有年月日可简化的一种,其它得用to_date SQL> select date'2021-07-31' fd from dual; FD ------------ 31-JUL-21 SQL> select * from datetest where nyr='2021-01-21 00:00:00';或select * from datetest where nyr='2021-01-21‘都不可以查 select * from datetest where nyr='2021-01-21 00:00:00' * ERROR at line 1: ORA-01861: literal does not match format string 虽然有存，但不可以直接查需用to_char对比考点：如何显示DATE类型时分秒出来用to_char 12小时制 SQL> select to_char(NYRSFM,'yyyy-mm-dd hh:mi:ss') ,to_char(dbsystimestamp,'yyyy-mm-dd hh:mi:ss') from datetest; TO_CHAR(NYRSFM,'YYYY-MM-DDHH:MM:SS') TO_CHAR(DBSYSTIMESTAMP,'YYYY-MM-DDHH:MM:SS') -------------------------------------- 2021-01-21 04:42:30 2021-01-21 04:44:19 问答2：显示了时分秒，上面的是16点，变成显示为04点了，把我输入的时间-12进行了展示，为什么呢，因为是12小时制显示。 24小时制 `select to_char(NYRSFM,'yyyy-mm-dd hh24:mi:ss') ,to_char(dbsysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(dbsystimestamp,'yyyy-mm-dd hh24:mi:ss') from datetest;` TO_CHAR(NYRSFM,'YYYY-MM-DDHH24:MI:SS') TO_CHAR(DBSYSDATE,'YYYY-MM-DDHH24:MI:SS') TO_CHAR(DBSYSTIMESTAMP,'YYYY-MM-DDHH24:MI:SS') -------------------------------------- ---------------------------------- ------------------------------------- 2021-01-21 16:42:30 2021-01-21 16:44:19 2021-01-21 16:44:19 问答3：显示了进行年月日时分秒的24小时制的进行真实完整展示。 Oracle SQL> select dbtimezone from dual; DBTIMEZONE ------------ +00:00 SQL> select sessiontimezone from dual; SESSIONTIMEZONE ------------------------------------------------------------------------------------------------------------------------------------------------------ +08:00 UTC+北京时区等于所在区的沟通时间 select to_char(sys_extract_utc(systimestamp),'yyyy-mm-dd hh24:mi:ss') from dual; 好处是世界会看到同一个时间UTC 2021-01-21 10:43:40 而当前北京时间是18点43，各经纬区域时差会不一样操作系统[oracle@host01 ~]$ date 2021年 01月 21日星期四 18:43:47 CST----北京时间	输入
VARCHAR2	变长存储，定义1000如果只有10，也只占10长
	除了char,nchar,date,TIMESTAMP固定字符长和日期时间类是是固定的长度，其它比如数字、varchar2，或者超长存储的都是可变长类型