MySQL Simple build table

MySQL field constraint Null,not null,default,auto_increment "comes from" no rules, inadequate surrounding area, everything is so. In MySQL, each field definition contains additional constraints or modifiers that can be used to increase the constraints on the input data. Today we'll take a look at MySQL field constraints: null and NOT NULL modifiers, default modifier, auto_increment modifier.

NULL and NOT NULL modifiers:

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

1234567891011121314151617

/*Create a friend table where ID, name, pass are not empty*/Create table friends ( ID int(3) is not null, name varchar(8) is not null, Pass varchar(not null ) );/*error message, ID column cannot be empty#1048-column ' id ' cannot be null*/INSERT into friends VALUES ( NULL , ' Simaopig ', ' Simaopig ' );

However, in the self-increment 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, your old man forgets to pass the value of the field, and MySQL automatically sets the default value for you on that field.

1234567891011121314151617181920212223

/*Create an IM table, set the Name field to the default value of ' QQ '*/Create table im ( ID int(3) is not null, name varchar(+) not null default ' QQ ' );/*Insert data, do not pass the value of the Name field, MySQL will set its default value for theThe 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 , /*ID Name2 MSN3 QQ*/

If the default modifier is not specified in a field, MySQL automatically sets the defaults based on whether the field is null or NOT NULL. If the specified field can be null, then MySQL sets the default value to NULL for it. If it is a NOT null field, MySQL inserts 0 for the numeric type, the string type inserts an empty string, the timestamp type is inserted into the current date and time, and the enum type is inserted into the first 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 that field (each time it adds 1 above the last value generated). This is useful for primary keys (described later). Because it allows developers to use MySQL to create unique identifiers for each record.

123456789101112131415161718192021222324252627282930

/*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 c16>, label varchar( 255 ) not NULL );/*Insert three data, do not specify ID, use default value, plus auto_incrementThe SQL statement you are running has run successfully. */insert into items(label) values (' xxx '); insert into items values (' yyy '); insert into items(label) values (' zzz '); /*show it all, take a look at the data, and look at the change in ID.*/SELECT * from items; /*ID Label1 xxx2 yyy3 zzz*/

there can be only one auto_increment field in a 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 keys and foreign keys, indexes, and unique constraints. These constraints are placed after the field definition of the CREATE TABLE command. You'll be introduced later. Please look forward to it.

MySQL Simple build table