MySQL string types, column properties, and foreign keys

String Type

Char and varchar

When you define char and varchar , you need to set the length.

Grammar:

char (M); Maximum length is 255

varchar (M); Maximum length is 65532 Different encoding lengths GBK The maximum is: 32767

UTF8 max:21845

M for length!

The difference between the two:

The m of Char is fixed, even if the input character length is less than the m value, the character length is also defined as m value;

More efficient, use some fixed character length can use, example: phone number, ID number, etc.

Varchar m is a range of values, as long as no more than m, the value of the input length is how much! More flexible storage space.

text

Text type, mainly divided into four kinds:

Tinytext,text,mediumtext,longtext

Character length does not need to be defined

from left to right, the more backward the value,the Longtext value is almost infinite!

Mainly used in text boxes and other free output areas (such as: An article, a piece of news, etc.)!

Enum

Single option String type

Grammar:

Enum(option string 1, option string n);

When inserting a value, only one string in the option string is inserted, others are invalid, and an error occurs!

because the enum is stored as an integer in the storage space, we can insert the value directly into the corresponding string number, starting from left to right, starting with a value of 1.

Set

Multi-option String type

The syntax is the same as a single option, except that you can insert multiple values when inserting values!

As with the single option, it can be read by string numbering, except sorting, sorting rules, left to right, and 1.2.4.8.16.32.64 up to

Binary,varbinary,blob

Binary text type, want to be a computer word can own Google study, that is not a person to learn!

Column Properties

Syntax format: The column property is when the table is created, fill in the following field type

null and nonull

Null allows the field to be empty;

Nonull does not allow the field to be empty, if the field set this property, the system will determine whether the field is empty, if there is a default value set, if not, the system will error!

Default

Field Default value

Grammar:

Default default value can be an integer or a string

PRIMARY Key

Primary key

1. after defining a field as the primary key, the field cannot be duplicated;

2. the contents of the primary key cannot be empty

3. the primary key in a table can only have one

4. also called the key index

Methods for defining primary keys

1. when defining a field, add primary key directly at the back ;

2. define the primary key syntax after you finish defining the fields :primary key(field);

This method can create a combined primary key, combining two fields into a primary key, but remember that the primary key has only one

combined PRIMARY KEY syntax: primary key(field name 1, field name 2);

Unique Key

Unique key

Also known as a unique index

As with the primary key, the definition method is the same

Difference: The unique key can be empty, the primary key cannot be empty!

auto_increment

Self-growth attributes

The function is to automatically add 1 to the value of a field each time a record is inserted (based on the previous record)

Attention:

There are two conditions for using this property:

1, the field type must be an integral type

2, There must be an index on the field (the primary key is also called the primary key index, and the unique key is also called a unique key index)

Auto_increment Auto-Grow initial value

syntax format: Increase auto_increment initial value after a field is defined

Comment

In-field comments

FOREIGN Key

One field in a table points to and refers to another field within another table

The table that is responsible for pointing to is a child table, the table that is pointed to is the primary table

Role:

1, when the child table record is added, whether there is a corresponding parent table record!

2, when deleting or changing the parent table record, how to handle the related records from the table!

Syntax format:

Foreign KEY ( sub-table field ) references parent table name ( parent table primary key )

First, the parent table and the Word table, the syntax is written in the field of the creation of the Word table;

define a foreign key name

Easy to delete after defining a foreign key name

Constraint foreign Key name foreign KEY ( sub-table field ) references parent table name ( parent table primary key )

Foreign key names cannot be quoted!

If you want to modify the parent table, you need to set a cascade operation command, when the parent table is manipulated, how the child table will be affected, if not set the cascade operation, change the parent table will be an error!

Cascade Operations

Master Table Update

syntax form:on update[ cascade Operation ]

Primary table Delete

syntax form:on delete[ cascade Operation ]

There are three common types of cascading operations here:

Cascade: synchronous operation, or serial operation! That is, when the main table record is deleted or updated, the table is also deleted or updated accordingly!

set NULL: set to null, that is, when the primary table record is deleted or updated, the foreign key field from the table is set to null

Restrict: reject Update or delete of primary table

syntax for deleting foreign keys

ALTER TABLE name drop foreign key foreign key name ;

MySQL string types, column properties, and foreign keys