This is the 12th day of my participation in the August More Text Challenge. For details, see:August is more challenging

1. MySQL data type

Data types in MySQL are strongly typed.

1.1 the numeric

1, the integer

The integer	Number of bytes occupied	The scope of
The integer	Number of bytes occupied	The scope of
tinyint	1	– (128 ~ 127Minus 2 to the seventh minus 2 to the eighth minus 1)
smallint	2	– (32768 ~ 32767Minus 2 to the 15, minus 2 to the 16 minus 1)
mediumint	3	– (8388608 ~ 8388607Minus 2 to the 23, minus 2 to the 24 minus 1)
int	4	– (2147483648 ~ 2147483647Minus 2 to the 31 minus 2 to the 32 minus 1)
bigint	8	– (9223372036854775808 ~ 92233720368547758087Minus 2 to the 63, minus 2 to the 64 minus 1)
For example, the number of students in a class, we can directly use Tinyint, because the normal is not more than 127, we set the range as small as possible. A smaller scope consumes fewer resources.

An unsigned integer is one that has no negative number. An unsigned integer is twice an integer. Ex. :We modify the table field properties to an unsigned integer type and see that we can store twice as much as the original integer type. Tinyint (3) is an integer that has a minimum display width, so if you don’t have enough digits, you can fill it with zeros. Display widths do not work by default, for example:

It is obvious that there are less than 3 bits here, but there are no zerofill fills, which must be combined with Zerofill to work:

1.2 floating-point

Float (M, D), double(M, D)

M: total place D: decimal place

If the decimal place is not specified, float defaults to 6 digits behind the decimal point, double defaults to 17 digits behind the decimal point,

2, the decimal (fixed-point number) principle, the integer and the fractional part of the separate storage. Grammar:decimal(M, D)Ex. :

1.3 character

There is no concept of strings in the database, only characters.

Char vs. varchar

1. Char (4) gives you four Spaces, you save one space, or you take up four Spaces, the maximum char is 255.
2. If varchar(4) stores 2 Spaces, then 2 Spaces will be reclaimed. In theory, the maximum vARCHar is 65536, but in practice, it cannot reach, because some characters are multi-byte characters. Char (4) and varchar(4) cannot exceed 4. Ex. :

In UTF8 encoding, a byte takes three characters:Under GBK encoding, a byte takes up two characters:The total length of all fields recorded in a table cannot exceed 65535 bytes. The text data type is not limited.

1.4 Enumeration Types (enum)

Only one value can be selected as data from the collection. Ex. :You can see the constraint data. You can only choose one of them.

Enumerations are managed by integer numbers, starting at 1 and increasing by 1. Ex. :

1.5 Set Types

Multiple values can be selected as data from the collection.

The order of insertion is different, but the order in which the collection was set is displayed.

If the value inserted is not in the range:You can see that an error is reported. Collections, like enumerations, assign a fixed value to each element: 0,1,2,3,4,… To the power. Ex. :

It can be found:

1.6 Date Types

1. Datetime and date

Datetime Format: (YYYY-MM-DD hh: MM :ss)

Year · Month · Day Hour: minute: second. Date format :(yyyy-mm-dd)

2. Timestamp (timestamp)

The dateTime and timestamp types behave the same. The differences are: Datetime ranges from 1000 to 9999, and timestamp ranges from 1970 to 2038 (TIMESTAMP takes 4 bytes, same as integer, description after 11:14:07 on January 19, 2038 is longer than 4 bytes)

3. Year indicates the year from 1901 to 2155. The value contains only one byte and contains 255 numbers4. Time indicates the time or interval. The value ranges from -838:59:59 to 838:59:59Time supports a time interval expressed in days:

1.7 the Boolean

MySQL does not support booleans, but true and false correspond to 1 and 0 in the database.

1.8 exercises

1. What data types are commonly used to store phone numbers?

Varchar, numeric class does not calculate the general use varchar 2. What data type is used for mobile phone numbers? 3. What data type is used to store gender? Char, tinyint, enum, etc. Tinyint 5. What data types are used to store photo information? Binary, but in practice we usually store the photo’s address, varchar. 6. What data types are used to store salaries? decimal

MySQL > select * from ‘MySQL’

2.1 Whether the value is Empty

Null indicates that the field value can be null. Not NULL indicates that the field cannot be null

2.2 Default Value (default)

If a field has no inserted value, a specified value can be set by default.

2.3 Auto_increment

The value of the field starts at 1 and increments by 1 each time. The autoincrement value is not repeated and is suitable for generating unique IDS. In MySQL, any autoincrement column must be a primary key.

2.4 Primary Key

Primary key: A column or set of columns that uniquely identifies a record in a table. The characteristics of primary keys are as follows: Primary keys cannot be empty and cannot be repeated. A table can have only one primary key. Function:

1. Ensure data integrity.
2. Speed up the query.

The primary key selection principle is minimum: try to select a single building as the primary key. Stability: Try to select the column with the least numeric update as the primary key. Add primary key:Delete primary key:Add primary key data:Null auto_increment primary key auto_increment primary key auto_increment primary key auto_increment primary key auto_increment primary key auto_increment primary key auto_increment primary key auto_increment primary key auto_increment primary key auto_increment primary key auto_increment primary key auto_increment primary key auto_increment Note that when we delete a field and add a new field, the id of the primary key will be incremented by 1. For example:

Truncate (truncate, truncate, truncate, truncate);

2.5 Unique Key

The only key:

1. Cannot be repeated, can be empty.
2. A table can have more than one unique key.

Ex. :The unique key is written in the same way as the primary key, except that the primary key is changed to unique. Ex. :Delete a unique key. There may be more than one unique key, so you can’t delete a unique key directly like a primary key. You delete a unique key by its name. grammarShow create table table name \GView unique key name:Then throughAlter TABLE 表名 drop index 表名;:

2.6 Remarks (Comment)

Give each field an explanatory text.

On the way to learning MySQL, if you find this article helpful to you, then please focus on like and comment 3 times, thank you, your must be another support of my blog.