MySQL constraint--not NULL non-null constraint

2, NOT NULL non-null constraint

　　Used to ensure that the value at the top of the column is not empty, and that the field defaults to NULL if you do not specify whether it can be empty when you create the table.

--This is the table created by the previous default constraintCREATE TABLE' Test '. 'User' (' ID 'INT( One) not NULLAuto_increment COMMENT'ID', ' name 'VARCHAR(225) not NULLCOMMENT'name',--the not NULL is added here' Sex 'TINYINT(1)DEFAULT 1COMMENT'Sex 1 Male 0 female',  PRIMARY KEY(' id ')) ENGINE=INNODB CHARSET=UTF8 COLLATE=Utf8_general_c

To add a non-null constraint to a field:

/*when creating a table, add not NULL directly after the field type. If a table already exists, the field that directly modifies the table is non-empty*/ALTER TABLE`User' MODIFY ' name 'VARCHAR(255) not NULL;
DESC`User`; --DESC View Table Structure

Results:
Field TypeNullKey Default Extra
-----------------------------------------------------------------
ID int (one) NO PRI (NULL) auto_increment
namevarchar (225)NO(NULL)
Sex tinyint (1) YES 1

To delete a non-null constraint:

/*Deleting a non-null constraint and adding a non-empty constraint is the same as modifying the structure of a table field, and deleting not null directly, so that non-null is deleted. */ALTER TABLE`User' MODIFY ' name 'VARCHAR(255);DESC`User`;Results:Field Type Null Key Default Extra---------------------------------------------------------------ID            Int (one) NO PRI (NULL) auto_increment name varchar (255) YES (NULL) Sex tinyint (1) YES 1                         

To add data to a non-null constraint:

-- add a record, directly set the Name field to ", you can add success.  INSERT into 'userVALUES(',01  Rows are affected

--Add a ' null ' or ' (null) ' String to add successINSERTInto 'user ' (' name ', ' sex ') VALUES ('null',0);  INSERT into 'user ' (' name ', ' sex ') VALUES ('(NULL)',0); results: A total of 2 rows were affected              

 --  Set the name field to NULL, add failed  insert  into  ' user " (' name ', ' sex ') values  (null , 0   1048  column   " name   '  cannot be null  

--  do not set the value of the Name field, add directly, add failed
--If the default constraint is added to the Name field, then you can add the value to the default value
INSERT  into 'userVALUES(01364'name  'default value

For a query about null:

--query for a record with the name field nullSELECT *  from`User`WHERE' Name ' is NULL;--querying a record with a name field that is not nullSELECT *  from`User`WHERE' Name ' is  not NULL;--query an empty string "'SELECT *  from`User`WHERE' Name '= "';--queries are all empty strings, using the LENGTH and TRIM function combinationsSELECT *  from`User`WHERELENGTH (TRIM (' name '))= 0;

MySQL constraint--not NULL non-null constraint