MySQL in Null,not null,default,auto_increment detailed

NULL and NOT NULL modifiers:

You can add this null or NOT NULL modifier to each field to specify whether the field can be empty (null), or you must fill in the data (not NULL). mysql specifies null modifiers by default, and if a field is specified as not null,mysql, it is not allowed to insert a null value into the field (Null in this case) because it is "turtle".

The code is as follows	Copy Code
/* Create a friend table, where ID, name, and pass cannot be empty */ CREATE TABLE Friends ( ID int (3) NOT NULL, Name varchar (8) NOT NULL, Pass varchar (%) NOT NULL ); /* Error prompts, id column cannot be empty #1048-column ' id ' cannot be null */ INSERT into Friends VALUES ( NULL, ' Simaopig ', ' Simaopig ' );

However, in the self-added and timestamp fields, this rule does not apply. Inserting a null value into these fields will result in the insertion of the next automatically incremented value or the current timestamp.

DEFAULT modifier:

You can set a default value for a field by using the default modifier. When you insert a record, when you forget to pass the value of the field, MySQL automatically sets the default value for the field for you.

The code is as follows

Copy Code

/* Create an IM table and set the Name field to the default value ' QQ ' */ Create TABLE IM ( ID int (3) NOT NULL, Name varchar NOT NULL default ' QQ ' ); /* Inserts the data, does not pass the Name field the value, the MySQL will set the default value for it The SQL statement you are running has run successfully. */ INSERT into IM (ID, name) VALUES (2, ' MSN '); INSERT into IM (ID) VALUES (3); SELECT * from im LIMIT 0, 30; /* ID Name 2 MSN 3 QQ */

If the default modifier is not specified in a field, MySQL automatically sets the default value based on whether the field is null or NOT NULL. If the specified field can be null, MySQL sets the default value to NULL. If the is not null field, MySQL inserts 0 for the numeric type, the string type inserts an empty string, the timestamp type inserts the current date and time, and the enum type inserts the first article of the enumeration group.

Auto_increment modifier:

The Auto_increment modifier applies only to the Int field, indicating that MySQL should automatically generate a number for the field (plus 1 each time on the last generated value). This is useful for primary keys (described later). Because it allows developers to use MySQL to create unique identifiers for each record.

The code is as follows

Copy Code

/* The SQL statement you are running has run successfully. (Query takes 0.0170 seconds) */ CREATE TABLE Items ( ID int (5) not NULL auto_increment PRIMARY KEY, Label varchar (255) Not NULL ); /* Inserts three data, does not specify the ID, takes the default value, plus auto_increment The SQL statement you are running has run successfully. */ INSERT into items (label) VALUES (' xxx '); INSERT into items (label) values (' yyy '); INSERT into items (label) values (' zzz '); /* Show it all, look at the data, look at the change in ID */ select * from items; /* ID Label 1 xxx 2 yyy 3 zzz

*/

There can be only one auto_increment field in the MySQL table, and this field must be defined as a key. In addition to field constraints, MySQL also allows table-level constraints, such as primary and foreign keys, indexes, and UNIQUE constraints

Command: CREATE table < table name > (< Field name 1> < type 1> [,.. < Field name N> < type n>];

For example, to create a table named MyClass,
Field name numeric type data width is null whether the primary key automatically increases the default value

code is as follows	copy code
ID int 4 No Primary key auto_increment   name Char 20 no        Sex int 4 no    ;   0 Degree Double 16 is        mysql> CREATE table MyClass ( &G T ID int (4) NOT null primary key auto_increment, > Name char (a) not null, > Sex int (4) is not null default ' 0 ' br> > Degree double (16,2);