Learn MySQL technology insider with me (fifth edition): (chapter 3 study diary 3)

3.2MySQL data type 3.2.1 data type overview 3.2.2 Special Column types in Table definitions 3.2.3 default value of specified columns ----------------------------- 3.2.1 data type overview MySQL numeric types include integer, fixed point, floating point, and bit value. Other data types except bit can contain positive values.

3.2MySQL data type 3.2.1 data type overview 3.2.2 Special Column types in Table definitions 3.2.3 default value of specified columns ----------------------------- 3.2.1 data type overview MySQL numeric types include integer, fixed point, floating point, and bit value. Other data types except bit can contain positive values.

3.2MySQL Data Type
3.2.1 overview of data types
3.2.2 Special Column types in Table Definitions
3.2.3 default values of specified Columns
-------------------------------
3.2.1 overview of data types

MySQL numeric types include integers, fixed points, floating point numbers, and bit values. Data types other than BITs can carry positive and negative numbers, or do not carry positive and negative numbers.

Type name meaning tinyint very small integer smallint small integer mediumint medium size integer int standard integer bigint large integer decimal fixed point float single precision Floating Point double precision Floating Point bit Field

A string can contain any content, binary data that represents the image and sound, and can be compared in uppercase or in pattern matching.

Type name meaning char fixed length non-binary string varchar variable length non-binary string binary Fixed Length binary string varbinary Variable Length binary string tinyblob very small blob (binary Large Object) blob small blobmediumblob medium-sized bloblongblob large blobtinytext very small non-binary string text small non-binary string mediutext medium size non-binary string longtext large non-binary string enum enumeration set (each column can take a value for several collection elements)

Temporal type:
MySQL provides the following types:
Date and time (combined or separated)
Timestamp (specifies the type of the last modification time of a row)

Type Name Description date value, format: 'ccyy-mm-dd' time value, format: 'hh: mm: ss' datetime date plus time value, the format is 'ccyy-mm-dd hh: mm: ss' timestamp. The format is 'ccyy-mm-dd hh: mm: ss' year value, the format is ccyy or yy.

Respectively:

Cc yy mm dd hh mm ss century, year, month, day, hour, minute, second

3.2.2 Special Column types in Table Definitions

create table mytabl1(   f float(10,4),   c char (15)  not null default 'none'   i tinyint unsigned null);

The syntax of column definition is as follows:

col_name col_type [type_attrs] [general_attrs]

Col_type indicates the column type, indicating the type of value that the column can accommodate.
1. Some type specifiers indicate the maximum length allowed for values stored in the column (char (10 ))
2. Some Types of specifiers allow the length to be hidden in the name (tinytext)
3. Some type specifiers allow you to specify a maximum display width.
4. For the fixed point type and floating point type, you can also specify the valid bits and decimal places

After the data type of a column, in addition to specifying multiple common attributes, you can also specify some type-specific optional attributes. These attributes are used to further modify and limit the type.

1. the allowed special type depends on the specific data type. 2. common attributes can be used for any data type. if multiple attributes exist, the attributes specific to the data type are usually placed before the common attributes.

3.2.3 default values of specified Columns

Except blob and text, space, or columns with the auto_increment attribute,

You can also specify the default def_value clause to create a new row. If a value is not explicitly specified, this column will be set by the default value def_value.

Except for the timestamp column and datetime column, the default value def_value must beConstant. It cannot be an expression or reference other columns.

If the default clause is not explicitly included and the column can be null, the default value is NULL.

If the column cannot be NULL or has no default clause, this will affect MySQL's column processing:

1. If the strict SQL mode is not enabled, this column will be set to the implicit default value of its data type. 2. After the SQL strict mode is enabled, if the table is transactional, an error occurs. This statement is aborted and rolled back. For non-transactional tables, if this row is the first row inserted by the statement, an error occurs. If this row is not the first row, you can choose to stop the statement, or set this column as its implicit default value and issue a warning message.

The implicit default value of a column depends on its data type.

1. For numeric columns (excluding columns with the auto_increment attribute), the default value is 0. For auto_increment columns, the default value is the sequence number of the next column. 2. for most columns of the temporal type, the default value is the "zero" value of this type. For the enum column, the default value is the first element in the enumeration set. For the set column, if the NULL value is not allowed, the default value is an empty set, but it is equivalent to an empty string.