A detailed explanation of the Double quotation marks required for table names and Field Names during oracle SQL statement Query

A detailed explanation of the Double quotation marks required for table names and Field Names during oracle SQL statement Query

As a beginner in oracle, I believe everyone will encounter this problem,

It is clear that navicat created a table visually, but it cannot be found! Why?

Select * from user;

However, if we add double quotation marks to the user, it will be different!

Select * from "user ";

However, you can wait for the following results,

 

Here I think everyone will say: Is it true that, unlike oracle and mysql, double quotation marks must be added for queries? This is not very troublesome! Therefore, the following conclusions are drawn from the search results:

1. oracle tables and fields are case-sensitive. Oracle uses uppercase letters by default. If we enclose them in double quotation marks, they are case sensitive. If not, the system automatically converts them to uppercase letters.

2. When we use navicat to create a database visually, navicat automatically adds "" to us. In this way, the actual code when we create a database is as follows: (through the SQL statement file everywhere, it is not difficult to understand why we cannot find the table .)

 

Drop table "ROOT ". "user"; create table "ROOT ". "user" ("userid" NUMBER (2) not null) ------- at this point, I think navicat should be used to create tables and fields in visualization.

3. How can I create tables and fields using SQL statements?
① Create and change without double quotation marks:

 

 

CREATE TABLE "ROOT".personal_user_table (personal_id NUMBER(10) NOT NULL ,login_name VARCHAR2(20 BYTE) NOT NULL ,login_password VARCHAR2(20 BYTE) NOT NULL ,register_time DATE NOT NULL ,fullname VARCHAR2(20 BYTE) NOT NULL ,sex CHAR(1 BYTE) NULL ,id_card CHAR(18 BYTE) NULL ,email VARCHAR2(30 BYTE) NULL ,tel CHAR(11 BYTE) NULL ,address VARCHAR2(64 BYTE) NULL ,work_units VARCHAR2(64 BYTE) NULL ,monthly_income NUMBER(8,2) NULL ,bank_account CHAR(20 BYTE) NULL ,credit_rating NUMBER(1) NULL )

The result is as follows:

 

② Add double quotation marks, which is the same as the result of the navicat visualization operation:

 

CREATE TABLE "ROOT"."personal_user_table" ("personal_id" NUMBER(10) NOT NULL ,"login_name" VARCHAR2(20 BYTE) NOT NULL ,"login_password" VARCHAR2(20 BYTE) NOT NULL ,"register_time" DATE NOT NULL ,"fullname" VARCHAR2(20 BYTE) NOT NULL ,"sex" CHAR(1 BYTE) NULL ,"id_card" CHAR(18 BYTE) NULL ,"email" VARCHAR2(30 BYTE) NULL ,"tel" CHAR(11 BYTE) NULL ,"address" VARCHAR2(64 BYTE) NULL ,"work_units" VARCHAR2(64 BYTE) NULL ,"monthly_income" NUMBER(8,2) NULL ,"bank_account" CHAR(20 BYTE) NULL ,"credit_rating" NUMBER(1) NULL )

 

The result is as follows:

 

Therefore, it is recommended that:

1. It is recommended that our visual operator develop the habit of writing SQL statements. We should not use double quotation marks when creating them! In this way, it is case-insensitive like mysql.

2. We recommend that you use uppercase letters to operate databases.