Mysql supported data types and table field constraint types of learning tutorials _mysql

MySQL data (field) type
when you create a table, you explicitly define the data type of the field. MySQL's main data types are numeric, string (text), date, and other types.
Numeric type
Numeric type description:

Supplementary notes
In the Int (integer) series, only integer values can be stored, and the displayed dimensions (M) may be specified in parentheses followed by default allocations if unspecified. If the display width of the actual value is greater than the set value, the actual value will be displayed without truncation to fit the display size. If the 3 in smallint (3) is the display dimension, that is, the three-bit value (excluding-number) is displayed.
The int type can specify the UNSIGNED property, that is, unsigned (nonnegative), so there are two types of storage range
In float, double, and decimal types, you cannot specify the UNSIGNED property, whose display size contains the decimal point precision (D), that is, float (3,1) is saved to-99.9 to 99.9
Decimal must specify display dimensions (M) and decimal precision (D), float and double are optional
On the basis of the possible range of values, choose a smaller type to increase efficiency and conserve storage space, such as age, and choose tinyint (3). This principle is also true for character types
String (text) type
String (text) type description:

Supplementary notes
Char and Varcha need to specify the length, but the char store is always stored at the specified length, while varchar allocates space based on the actual string length.
Time Date Type
Time Date Type description:

Tips
In PHP, in general, the time is stored in the table according to the UNIX timestamp with the type int, and then processed using PHP's time function, but not exactly.

MySQL table Field Properties
PRIMARY Key
The primary key (primary key, primary keyword) of a table is one or more fields in a table whose values are used to uniquely identify a record in a table. A table cannot have more than one primary key, and the column of the primary key cannot contain null values and duplicate values. The primary key is optional and can be defined in the CREATE table or ALTER table statement.
Grammar:
PRIMARY KEY (column_name)

In this table, the UID field is the primary key of the table.
General principles to be followed in establishing a primary key
The primary key should be meaningless to the user
Never update a primary key, if the primary key needs to be updated, then the primary key should be violated by the principle that the user is meaningless
Primary keys should not contain dynamically changing data, such as timestamp, creation time, and so on
Primary keys should be automatically generated by the system
FOREIGN key
In a two-table relationship, when a table (such as table A) 's primary key is contained in another table (such as table B), the primary key in table A is the foreign key of Table B (the foreign key). Table B is called the primary table, and A table is called from the table.
Foreign keys are mainly used to maintain data consistency, integrity, and avoid redundant data, so that two or more tables to form an association.

In this article Comment table Comment sample, the UID is the foreign key, which is the primary key of the user table. The user's comments on the article are recorded in this table, and the user information is only required for the primary key UID of the users table. In data consistency, for example, when the comment table has no UID 3 comment, you can delete the UID = 3 record in the user table.
Auto Increment
In the MySQL database, the automatic increment (auto_increment) property of the field is provided, and the field is set to the data type of the int class, and the value of the field is automatically increased by 1 each time a record is added to the datasheet. When automatic increments are set, the column does not have to set default values and uniqueness constraints.
Example:

UID Mediumint (8) Not NULL auto_increment

Not empty
Because of some logical requirements, it is sometimes necessary to set the field property to Non-null (not null), such as a field that records a non-empty value such as a user name, password, and so on.
Columns that are not empty are not required, but it is best to set a default value to prevent unexpected errors and reduce the complexity of SQL statements when data is added. When you add data records to a datasheet, if a field that is set to Non-null does not write data, the system is written with the default value.
Example:

Username char not NULL default '
//convert NOT NULL to NULL
ALTER TABLE user change username username char (.) NULL

Tips
In the example above, the change is followed by username username, which represents the changed field name and the new field name. In this case, only the field property is changed to NULL and the field name changes are not involved, so they are consistent.
Empty
In contrast to Non-null, the field property can be set to NULL, and if the field property is not set to a non-empty (not null) property, then the system defaults to null (NULL) values.
Change NULL to NOT NULL:

ALTER TABLE User change username username char (.) Not NULL DEFAULT '

Uniqueness Constraint
Sometimes some field data does not allow repetition, such as user name, which requires additional uniqueness constraints (unique).
Grammar:

Unique (column_name)//Add unique
ALTER TABLE tb_name add unique (column_name)
//Remove unique alter for
table fields TABLE tb_name DROP INDEX column_name

The primary key (PRIMARY key) is mandatory to have automatically defined uniqueness constraints without additional definition of unique.
Index
A database index is an identifier that is attached to a field to increase the speed of the query. When we query the field data, we create an appropriate index for some of the fields that may need to be queried frequently.
Grammar:
KEY Key_name (column_name)
The character following the KEY is the index name, and the name of the field in parentheses is the index.
Improved table-building SQL

CREATE TABLE User (
  uid mediumint (8) unsigned not NULL auto_increment,
  username char not null default ',
  Password char NOT null default ',
  email varchar (+) NOT null default ',
  regdate Int (a) unsigned not null de Fault ' 0 ',
  PRIMARY key (UID),
  UNIQUE key username (username),
  key Email (email)
Engine=myisam DEFAULT C Harset=utf8 auto_increment=1;

Supplementary notes
Several other attributes of the table are appended to the body of the table statement:
ENGINE: Represents the storage engine type, divided into MyISAM and InnoDB two types. MyISAM does not support advanced processing, such as transaction processing, emphasizing the performance of the table, and execution is faster than InnoDB. and InnoDB provides transaction support has the external key and other advanced database functions, performance is worse than MyISAM. Default is MyISAM type
CHARSET: Represents a data table table character set, generally GBK or UTF8, and big5, for compatibility reasons, we set to the UTF8 character set
Auto_increment: Sets the starting number of default growth for primary keys