使用Oracle数据库的一些小记录 1

ORA-00904: “NAME”: 标识符无效?

当你用Navicat连接Oracle数据库并在Oracle数据库新建了一张名为LESSON的表,然后查询时,用这样的语句

select NAME from LESSON where NAME='JAVA';

你会发现报错了!
ORA-00904: “NAME”: 标识符无效

当写成这样的时候就可以查到

select "name" from LESSON where "name"='JAVA';

这是为啥呢?
我个人以为是因为Oracle数据库严格限制大小写;

如何避免呢?
有的帖子说要在建表的时候就不要写双引号,我发现这不是主要原因,主要原因还是大小写的问题,怎么验证不是引号的问题呢?后面会说到;

我用Navicat 12版本,可以连接Oracle数据库的;
但是在建表的时候,手工右键去建表那种,填写表字段名称,数据类型,约束后,填表名;这就完成建表了;
如果字段名用的是小写字母,那navicat建的表中的字段就是"name",那查询的时候一定是要"name"这样查的;
如果是表名和字段名都是大写字母,那就不用加引号查了,而且可以写小写字母;
例如: 这样也是可以查的

select name from LESSON where name='JAVA';

用IDEA 2021 2.x版本的数据库连接工具连接Oracle数据库,也可以实现像Navicat 12那样的可视化的建表流程,IDEA的数据库连接工具就更好用,因为你建表时字段填的小写, 它也会自动给你转成大写的,导出表结构和数据时,也会是大写且不带引号的;

Navicat 12在导出表结构和数据时,会给表名和字段名加上引号;

Navicat 12和IDEA 2021 2.x自带数据库工具导出Oracle表的实例

Navicat导出实例:

DROP TABLE "STUDENT";
CREATE TABLE "STUDENT" (
  "ID" NUMBER VISIBLE DEFAULT "ISEQ$$_134527".nextval NOT NULL,
  "NAME" VARCHAR2(255 BYTE) VISIBLE,
  "GENDER" CHAR(1 BYTE) VISIBLE DEFAULT 1,
  "AGE" NUMBER VISIBLE DEFAULT 18,
  "ADDRESS" VARCHAR2(255 BYTE) VISIBLE,
  "UPDATE_DATE" DATE VISIBLE,
  "CREATE_TIME" TIMESTAMP(6) VISIBLE
)
TABLESPACE "XGZ_XSD_TBS"
LOGGING
NOCOMPRESS
PCTFREE 10
INITRANS 1
STORAGE (
  INITIAL 65536 
  NEXT 1048576 
  MINEXTENTS 1
  MAXEXTENTS 2147483645
  BUFFER_POOL DEFAULT
)
PARALLEL 1
NOCACHE
DISABLE ROW MOVEMENT
;
COMMENT ON COLUMN "STUDENT"."ID" IS '主键ID';
COMMENT ON COLUMN "STUDENT"."NAME" IS '名字';
COMMENT ON COLUMN "STUDENT"."GENDER" IS '0-女,1-男';
COMMENT ON COLUMN "STUDENT"."AGE" IS '年龄';
COMMENT ON COLUMN "STUDENT"."ADDRESS" IS '地址';
COMMENT ON COLUMN "STUDENT"."UPDATE_DATE" IS '数据更新日期';
COMMENT ON COLUMN "STUDENT"."CREATE_TIME" IS '创建时间';
COMMENT ON TABLE "STUDENT" IS '学生信息表';

-- ----------------------------
-- Records of STUDENT
-- ----------------------------
INSERT INTO "STUDENT" VALUES ('2', '李士', '1', '25', '雨花区', TO_DATE('2022-07-06 11:00:00', 'SYYYY-MM-DD HH24:MI:SS'), TO_TIMESTAMP('2022-07-06 10:00:06.000000', 'SYYYY-MM-DD HH24:MI:SS:FF6'));
INSERT INTO "STUDENT" VALUES ('9', '王五', '0', '26', '岳麓区', TO_DATE('2022-07-06 11:00:00', 'SYYYY-MM-DD HH24:MI:SS'), TO_TIMESTAMP('2022-07-06 10:00:06.000000', 'SYYYY-MM-DD HH24:MI:SS:FF6'));
INSERT INTO "STUDENT" VALUES ('10', '王五', '0', '26', '岳麓区', TO_DATE('2022-07-06 11:00:00', 'SYYYY-MM-DD HH24:MI:SS'), TO_TIMESTAMP('2022-07-06 10:00:06.000000', 'SYYYY-MM-DD HH24:MI:SS:FF6'));
INSERT INTO "STUDENT" VALUES ('11', '赵六', '1', '27', '芙蓉区', TO_DATE('2022-07-06 11:00:00', 'SYYYY-MM-DD HH24:MI:SS'), TO_TIMESTAMP('2022-07-06 10:00:06.000000', 'SYYYY-MM-DD HH24:MI:SS:FF6'));

-- ----------------------------
-- Primary Key structure for table STUDENT
-- ----------------------------
ALTER TABLE "STUDENT" ADD CONSTRAINT "STUDENT_PK" PRIMARY KEY ("ID");

-- ----------------------------
-- Checks structure for table STUDENT
-- ----------------------------
ALTER TABLE "STUDENT" ADD CONSTRAINT "SYS_C0014040" CHECK ("ID" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;

IDEA导出实例对比:

create table STUDENT
(
    ID          NUMBER default "XSD"."ISEQ$$_134527".nextval not null
        constraint STUDENT_PK
            primary key,
    NAME        VARCHAR2(255),
    GENDER      CHAR   default 1,
    AGE         NUMBER default 18,
    ADDRESS     VARCHAR2(255),
    UPDATE_DATE DATE,
    CREATE_TIME TIMESTAMP(6)
)
/

comment on table STUDENT is '学生信息表'
/

comment on column STUDENT.ID is '主键ID'
/

comment on column STUDENT.NAME is '名字'
/

comment on column STUDENT.GENDER is '0-女,1-男'
/

comment on column STUDENT.AGE is '年龄'
/

comment on column STUDENT.ADDRESS is '地址'
/

comment on column STUDENT.UPDATE_DATE is '数据更新日期'
/

comment on column STUDENT.CREATE_TIME is '创建时间'
/

INSERT INTO STUDENT (ID, NAME, GENDER, AGE, ADDRESS, UPDATE_DATE, CREATE_TIME) VALUES (2, '李士', '1', 25, '雨花区', TO_DATE('2022-07-06 11:00:00', 'YYYY-MM-DD HH24:MI:SS'), TO_TIMESTAMP('2022-07-06 10:00:06.000000', 'YYYY-MM-DD HH24:MI:SS.FF6'));
INSERT INTO STUDENT (ID, NAME, GENDER, AGE, ADDRESS, UPDATE_DATE, CREATE_TIME) VALUES (9, '王五', '0', 26, '岳麓区', TO_DATE('2022-07-06 11:00:00', 'YYYY-MM-DD HH24:MI:SS'), TO_TIMESTAMP('2022-07-06 10:00:06.000000', 'YYYY-MM-DD HH24:MI:SS.FF6'));
INSERT INTO STUDENT (ID, NAME, GENDER, AGE, ADDRESS, UPDATE_DATE, CREATE_TIME) VALUES (10, '王五', '0', 26, '岳麓区', TO_DATE('2022-07-06 11:00:00', 'YYYY-MM-DD HH24:MI:SS'), TO_TIMESTAMP('2022-07-06 10:00:06.000000', 'YYYY-MM-DD HH24:MI:SS.FF6'));
INSERT INTO STUDENT (ID, NAME, GENDER, AGE, ADDRESS, UPDATE_DATE, CREATE_TIME) VALUES (11, '赵六', '1', 27, '芙蓉区', TO_DATE('2022-07-06 11:00:00', 'YYYY-MM-DD HH24:MI:SS'), TO_TIMESTAMP('2022-07-06 10:00:06.000000', 'YYYY-MM-DD HH24:MI:SS.FF6'));

为什么说标识符无效和引号没关系?

Oracle的查看建表语句的SQL:
其中’STUDENT’ 这里填你要看的表的表名

select dbms_metadata.get_ddl('TABLE','STUDENT') from dual;

返回结果: 在Navicat和IDEA里的返回结果一样,建表语句都是有引号的,再拿这个建表语句去执行(执行前先删掉这个表),也是可以执行的;这里的示例是全是大写字母的字段,小写字母也就是"name"这样的;
所以说和引号没关系,只和大小写有关系;

CREATE TABLE "STUDENT" 
   (	"ID" NUMBER(*,0) GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE  NOT NULL ENABLE, 
	"NAME" VARCHAR2(255), 
	"GENDER" CHAR(1) DEFAULT 1, 
	"AGE" NUMBER(*,0) DEFAULT 18, 
	"ADDRESS" VARCHAR2(255), 
	"UPDATE_DATE" DATE, 
	"CREATE_TIME" TIMESTAMP (6), 
	 CONSTRAINT "STUDENT_PK" PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "MY_TBS"  ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "MY_TBS" 

Oracle数据主键ID怎么设置INT自增?

从上面的建表语句可以得知:

"ID" NUMBER(*,0) GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE  NOT NULL ENABLE, 

这一段就是设置主键自增的,并且如果选了INTEGER也会变成NUMBER(*,0);
或者不设置自增我选择用UUID来做主键;