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

Source: Internet
Author: User

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.