MySQL table properties, indexes, constraints

1. Table Properties

Basic syntax for creating a table:

CREATE TABLE "If not exists" Table name (field list, index or constraint list ")" Table option List "

Where the field list is formatted as follows:

Field Name Type "Property List",

Field Name Type "Property List" ...

Each property in the property list is separated by a space.

Common Field Properties:

Auto_increment	Set field values to grow automatically for integer types
Primary key	Sets the field as the primary key, at which point the value of the field can "uniquely determine" a row of data
Unique key	The SET field is unique and is not duplicated throughout the data table
NOT NULL	The SET field cannot be null, and if you do not specify the property, the value of the field is nullable by default.
Default	Set the default value of the field, and when you insert the data, the default value is populated if you do not specify the field's value.
Comment	Setting a description for a field

Note: Primary key and unique key both determine the uniqueness of the field, the fields decorated by these two properties can uniquely determine a row of data, the difference is that primary key cannot be null (when primary key is specified, the default specified not Null property), and the unique key can be null. It can be said that primary key is a special unique key.

Code:

/* CREATE TABLE, note properties */
Mysql> Create Tableitem_properties_table ( -IdintAuto_incrementPrimary Key,     -Namevarchar( -) not NULL Unique Key,     -Pwdvarchar( -) not NULL,     -Agetinyint default  -,     -Emailvarchar( -) Comment'Email'     - ); Query OK,0Rows Affected (0.02sec)
/* View the table structure primarily to view the Comment*/mysql>Show FullColumns fromitem_properties_table;+-------+-------------+-----------------+------+-----+---------+----------------+----------------------------- ----+--------------+|Field|Type|Collation| Null | Key | Default |Extra| Privileges                      |Comment|+-------+-------------+-----------------+------+-----+---------+----------------+----------------------------- ----+--------------+|Id| int( One)| NULL            |NO|Pri| NULL    |Auto_increment| Select,Insert,Update,References |              ||Name| varchar( -)|Utf8_general_ci|NO|UNI| NULL    |                | Select,Insert,Update,References |              ||Pwd| varchar( -)|Utf8_general_ci|NO|     | NULL    |                | Select,Insert,Update,References |              ||Age| tinyint(4)| NULL            |YES|     |  -      |                | Select,Insert,Update,References |              ||Email| varchar( -)|Utf8_general_ci|YES|     | NULL    |                | Select,Insert,Update,References |Email|+-------+-------------+-----------------+------+-----+---------+----------------+----------------------------- ----+--------------+5Rowsinch Set(0.00sec) MySQL> 

2. Index

MySQL table properties, indexes, constraints