1, Object name
1. Case sensitive
CREATE TABLE TEST.Abc(COL INT);
------For creating a table, when case sensitive, the table name ABC is not enclosed in double quotation marks, and the system will automatically convert it to uppercase table name ABC
The inquiry is as follows:
SELECT * FROM TEST.Abc; ------Successful execution SELECT * FROM TEST.ABC; ------Successful execution SELECT * FROM TEST."Abc"; ------error: Invalid table or view name[Abc]SELECT * FROM TEST."ABC"; ------Successful execution
CREATE TABLE TEST."Abc"(COL INT);
------For creating a table, in case of case sensitivity, the table name Abc is enclosed in double quotation marks, that is, the created table is Abc
The inquiry is as follows:
SELECT * FROM TEST.Abc; ------error: Invalid table or view name[ABC]SELECT * FROM TEST.ABC; ------error: Invalid table or view name[ABC]SELECT * FROM TEST."Abc"; ------Successful execution SELECT * FROM TEST."ABC"; ------error: Invalid table or view name[ABC]
Note: (in case of case sensitivity) when using the graphical interface to create objects in Dameng management tool (manager), if lowercase is used, the system will automatically add double quotation marks. Double quotation marks must be used to access when querying.
2. Case insensitive
CREATE TABLE a(C1 INT);
------When creating a table, whether the table name a is enclosed in double quotation marks has no effect on the query results when it is case insensitive.
The query is as follows:
SELECT * FROM a; ------Successful execution SELECT * FROM A; ------Successful execution SELECT * FROM "a"; ------Successful execution SELECT * FROM "A"; ------Successful execution
CREATE TABLE "b"(C1 INT);
The inquiry is as follows:
SELECT * FROM b; ------Successful execution SELECT * FROM B; ------Successful execution SELECT * FROM "b"; ------Successful execution SELECT * FROM "B"; ------Successful execution
2, String content
CREATE TABLE MMJ(COL VARCHAR(100)); ---Create table MMJ
INSERT INTO MMJ VALUES('Abc'); ---insert data AbcINSERT INTO MMJ VALUES('ABC'); ---insert data ABC1. Case sensitive
SELECT COUNT(*) FROM MMJ WHERE COL='Abc'; ------The result is 1 SELECT COUNT(*) FROM MMJ WHERE COL='ABC'; ------The result is 1
2. Case insensitive
SELECT COUNT(*) FROM MMJ WHERE COL='Abc'; ------The result is 2 SELECT COUNT(*) FROM MMJ WHERE COL='ABC'; ------The result is 2
III. accessing database with different tools
1. disql access database
It should be noted that the password contains "@", "/" and other special characters, which need to be handled by escape character at this time.
The disql escape character is used as follows:
**linux environment**
The password needs to be enclosed in double quotation marks
At the same time, the outer layer is escaped with single quotation marks. Specific examples are as follows:
./disql SYSDBA/' "abcd@efgh" '@localhost
**windows Environment**
The password needs to be enclosed in double quotation marks
At the same time, use "\" to escape double quotation marks. Specific examples are as follows:
disql SYSDBA/\"abcd@efgh\"@localhost
2. manager access database
1) Case sensitive:
Create a lowercase table object: you need to add double quotes to create it
Create an uppercase table object: you do not need to add double quotes to
Create query lowercase table object: you need to add double quotation marks to query
Query uppercase table object: no need to add double quotation marks to query
2) Case insensitive:
Create a lowercase table object: you need to add double quotes to create it
Create an uppercase table object: you do not need to add double quotes to create it
Query lowercase table objects: no need to add double quotes to create
Query uppercase table object: no need to add double quotation marks to create
Whether it is case sensitive or case insensitive, you should pay attention to the following situations
create user "bb" identified by "11111111111"create user "BB" identified by "11111111111" ------Error reporting: object[BB]Already exists
3. DTS migration tool
1) Case sensitive:
Table object lowercase: if you want the migrated table object to remain lowercase, you need to check "keep object name case". In later queries, you need to use double quotation marks
Table object capitalization: you do not need to check "keep object name case". In later queries, you do not need to use double quotation marks for query, because it will be automatically converted to uppercase query
2) Case insensitive:
Table object lowercase: if you want the migrated table object to remain lowercase, you need to check "keep object name case". In later queries, you don't need to use double quotation marks.
Table object capitalization: you do not need to check "keep object name case". In later queries, you do not need to use double quotation marks for query, because it will be automatically converted to uppercase query
Summary
1. In case sensitive databases
1) Create table:
If double quotation marks are not added to the table name or column name, the table name and column name will be automatically converted to uppercase form;
If double quotation marks "" are added to the table name or column name, the upper and lower case of writing will be fixed;
Database objects with the same name are two different objects if the case is different.
2) The fields are the same as above:
In a table, even for the same field name, as long as the case is different, fields with the same name and different case forms are allowed.
3) During DML or DDL operation:
Ø if double quotation marks "" are not added to the table name or column name, the table name and column name will be automatically converted to uppercase form;
Ø when DML is performed on the table, if there is no field in lowercase, the filter field cannot be specified in the form of lowercase plus "", which will be deemed as invalid;
Ø if '' is added to the table name or column name, the upper and lower case forms of writing will be fixed. If '' is in upper case, the filtered field is in upper case; if '' is in lower case, the filtered field is in lower case;
Ø when performing DML operation on it, you need to use "" to specify the table name and field name, otherwise it will be determined to query the object in uppercase by default.
Ø when querying, the strings in the '' and '' delimiters are case sensitive. If the string in the delimiter is in uppercase form, only the objects in uppercase form will be queried. If it is in or lowercase form, only the objects in lowercase form will be queried. The DML operation remains the same.
2. Case insensitive database
1) When creating a table:
Whether double quotation marks are added to the table name or column name, the case of table name and column name will not change. Uppercase form is uppercase and lowercase form is lowercase;
2) The fields are the same as above:
The same field name is not allowed, even if the case is different;
When querying, the 'and' delimiters are not case sensitive. The expected result set can be queried even if the query or filter conditions in the delimiter are uppercase or lowercase
3) During DML or DDL operation:
No matter whether you add "" to the table name or column name, the case of table name and column name will not change. Uppercase form is uppercase form and lowercase form is lowercase form;
In a table, the same field name is not allowed, even if the case is different;
When querying, the 'and' 'delimiters are not case sensitive. Even if the query or filter conditions in the delimiter are uppercase or lowercase, the expected result set can be queried, which remains the same when DML operation is performed.
In case of case insensitive, database objects with the same name are not allowed in the above cases. Even if the case is different, only one object can exist by default.