Oracle database Create table insert table query NULL Value MySQL database create table insert table query NULL value MySQL database create table insert table query
The use of NULL values in the chapter summarizing Oracle statements to determine whether a string is a number and translating translate in the previous section had a different effect. The document associated with this query records the difference between an empty string and a NULL value.
The Oracle database
Create a table
Create a test table for subsequent Sql operation validation.
DROP TABLE "spring"."student";
CREATE TABLE "spring"."student" (
"id" NUMBER(5) NOT NULL ,
"num" VARCHAR2(20 BYTE) NOT NULL ,
"desc" VARCHAR2(20 BYTE) NULL
)
LOGGING
NOCOMPRESS
NOCACHE
;
COMMENT ON COLUMN "spring"."student"."num" IS 'number';
COMMENT ON COLUMN "spring"."student"."desc" IS 'description';
-- ----------------------------
-- Indexes structure for table student
-- ----------------------------
-- ----------------------------
-- Checks structure for table student
-- ----------------------------
ALTER TABLE "spring"."student" ADD CHECK ("id" IS NOT NULL);
ALTER TABLE "spring"."student" ADD CHECK ("num" IS NOT NULL);
-- ----------------------------
-- Primary Key structure for table student
-- ----------------------------
ALTER TABLE "spring"."student" ADD PRIMARY KEY ("id");
Copy the code
Table insert operation
INSERT INTO "spring"."student" ("id"."num"."desc") VALUES (101.'2019001'.'hresh');-- Insert successful
Copy the codeInsert when the desc field is set to NULL.
INSERT INTO "spring"."student" ("id"."num"."desc") VALUES (102.'2019001'.NULL);-- Insert successful
Copy the codeInsert when the desc field value is set to an empty string.
INSERT INTO "spring"."student" ("id"."num"."desc") VALUES (104.'2019001'.' ');-- Insert successful
Copy the codeInsert when the num field value is set to NULL or an empty string.
INSERT INTO "spring"."student" ("id"."num"."desc") VALUES (103.NULL.'hresh');Failure -
INSERT INTO "spring"."student" ("id"."num"."desc") VALUES (105.' '.'hresh');Failure -
Copy the codeInsert when the num field or desc field value is set to a space.
INSERT INTO "spring"."student" ("id"."num"."desc") VALUES (107.'2019001'.' ');-- Insert space successfully
INSERT INTO "spring"."student" ("id"."num"."desc") VALUES (108.' '.'hresh');-- Insert space successfully
Copy the codeAfter the insert operation is complete, view the data in the table, as shown in the figure:
The following conclusions can be drawn from the data in the table:
- For fields defined as types char and varchar2, “‘(an empty string) is null;
- A field (num) defined as not NULL cannot insert an empty string or null;
- A null field (desc) can insert an empty string and null and display null results.
Table query operation
select * from "student" where "desc" =' '; ---- Determining Spaces
----
-- One record whose ID is 107 is displayed
select * from "student" where "desc" =' '; ---- Determine an empty string
----
- no record
select * from "student" where "desc" is NULL; - determine null
----
---- Two records, whose ids are 102 and 104, are displayed
Copy the codeA NULL value to summarize
What is a NULL value?
When we don’t know what the data is, we don’t know, we can use NULL, which is also called NULL. In Oracle, a NULL value has a NULL field length.
select "LENGTH"(null) from dual;
----
The result is null
Copy the codeWhat is the range of NULL values?
Oracle allows fields of any data type to be empty except in the following two cases:
- Primary key field
- A field defined with a NOT NULL constraint
Add, subtract, multiply, divide, and so on to the null value, the result is still null
SELECT null + 1 from dual;
SELECT null - 1 from dual;
SELECT null * 1 from dual;
SELECT null / 1 from dual;
----
Both results are NULL
Copy the codeNULL values are handled using NVL functions
SELECT "NVL"(NULL.'521') from dual;
---
521
Copy the codeNULL value comparison using the keyword “is NULL “and “is not NULL”
SELECT 1 FROM dual where NULL IS NULL;-- The result is 1
SELECT 1 FROM dual where NULL = NULL;-- Null
SELECT 1 FROM dual where ' ' = ' ';-- Null
SELECT 1 FROM dual where "NVL"(NULL.1) ="NVL"(NULL.1);-- The result is 1
Copy the codeNull values cannot be indexed, so some qualified data may not be found when querying. In count(column name), use NVL (column name,0) to process the data and then search
select "COUNT"("desc") from "student";--3
select "COUNT"(1) from "student";5 -
select "COUNT"("NVL"("desc".1)) from "student";5 -
Copy the codeThe index is sorted in descending order (small → large), so NULL values are always last
select * from "student" ORDER BY "desc";
Copy the code
The MySQL database
Create a table
CREATE TABLE `student` (
`id` int(5) NOT NULL AUTO_INCREMENT,
`num` varchar(5) NOT NULL,
'desc' varchar(20) DEFAULT NULL COMMENT 'desc ',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
Copy the codeTable insert operation
INSERT INTO `spring`.`student` (`NUM`, `DESC`) VALUES ('100', 'hresh'); -- Insert successful
Copy the codeInsert when the desc field is set to NULL.
INSERT INTO `spring`.`student` (`NUM`, `DESC`) VALUES ('101', NULL); -- Insert successful
Copy the codeInsert when the desc field value is set to an empty string.
INSERT INTO `spring`.`student` (`NUM`, `DESC`) VALUES ('102', ''); -- Insert successful
Copy the codeInsert when the num field value is set to NULL or an empty string.
INSERT INTO `spring`.`student` (`NUM`, `DESC`) VALUES (NULL, 'hresh'); Failure -
INSERT INTO `spring`.`student` (`NUM`, `DESC`) VALUES ('', 'hresh'); Success -
Copy the codeInsert when the num field or desc field value is set to a space.
INSERT INTO `spring`.`student` (`NUM`, `DESC`) VALUES (' ', ' '); -- Insert space successfully
Copy the codeAfter the insert operation is complete, view the data in the table, as shown in the figure:
The following conclusions can be drawn from the data in the table:
- An empty string and a NULL value are not the same thing;
- A field (num) defined as not NULL can only be inserted with an empty string, not a null value;
- A field (desc) defined as null can be inserted with an empty string and null, which is displayed accordingly.
Table query operation
SELECT * FROM spring.student WHERE `desc` IS NULL;
-- One record whose ID is 3 is displayed
SELECT * FROM spring.student WHERE `desc` = '';
-- Query two records whose ids are 4 and 8
SELECT * FROM spring.student WHERE `desc` IS NOT NULL;
---- Four records with ids 4, 5, 7, and 8 are displayed
SELECT * FROM spring.student WHERE `desc` ! = ' ';
-- query two records (id 5,7)
Copy the codeIs not NULL will filter only columns with a null value, while! = Filters both empty strings and null values. Therefore, select the filter mode based on the actual situation. In addition, null values can only be determined by is NULL or is not NULL, not by = or! =, < >.
SELECT count(`desc`) FROM spring.student; 4 -
Copy the codeWhen count() is used to count the number of records in a column, the system will automatically ignore the NULL value, but empty strings will be counted.
When ORDER BY is used, the NULL value is rendered first. If you use DESC to sort in descending order, NULL values are displayed last. When GROUP BY is used, all NULL values are considered equal, so only one row is displayed. Empty strings are followed by NULL values, and Spaces are followed by empty strings.