Study with me MySQL Technical Insider (fifth Edition): (chapter III study diary 3)

3.2MySQL Data types
3.2.1 Data Types Overview
3.2.2 Special column types in the table definition
3.2.3 The default value for the specified column
———————————————————————————————
3.2.1 Data Types Overview

MySQL number types include integers, fixed-point numbers, floating-point number, and bit values. Other data types, except bit, can have a sign or a minus sign.

类型名称               含义tinyint               非常小的整数smallint              小整数mediumint             中等大小的整数int                   标准的整数bigint                大整数decimal               定点数float                 单精度浮点数double                双精度浮点数bit                   位域

Strings can hold anything, can hold binary data that represents images and sounds, can be compared in uppercase, or pattern-matching.

类型名称               含义char                  固定长度的非二进制字符串varchar               可变长度的非二进制串binary                固定长度的二进制串varbinary             可变长度的二进制串tinyblob              非常小型的blob（二进制大对象）blob                  小型blobmediumblob            中型bloblongblob              大型blobtinytext              非常小型的非二进制串text                  小型非二进制串mediutext             中等大小的非二进制串longtext              大型非二进制串enum                  枚举集合set                   集合（每个列取值可以为若干个集合元素）

Temporal type:
The types provided by MySQL are:
Date and time (combined or separate representation)
Timestamp (the type that specifically describes the last time a row was modified)

类型名称              含义date                 日期值，格式为‘ccyy-mm-dd’time                 时间值，格式为‘hh：mm：ss’datetime             日期加时间值，格式为‘ccyy-mm-dd hh:mm:ss’timestamp            时间戳值，格式为‘ccyy-mm-dd hh:mm:ss’year                 

respectively, say:

        cc      yy       mm     dd      hh      mm      ss              世纪、   年、      月 、   日   、 小时  、 分钟   、秒

3.2.2 Special column types in the table definition

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

The syntax for a column definition is as follows:

col_name col_type [type_attrs] [general_attrs]

Col_type represents a column type, which indicates what type of value this column can hold.
1. Some type specifiers indicate the maximum length allowed for values stored in joins (char (10))
2. Some type specifiers allow length to be implied in the name (Tinytext)
3. Some type specifiers allow you to specify a maximum display width
4. For fixed-point and floating-point types, you can also specify valid and decimal digits

After the data type of a column, in addition to specifying multiple common properties, you can specify optional properties of some type. The purpose of these properties is to further modify and qualify the type.

1.所允许的特有类型取决于具体的数据类型   2.通用属性可用于任何一种数据类型3.如果存在多个属性，通常将数据类型特有的属性放在通用属性的前面

3.2.3 The default value for the specified column

In addition to BLOBs and text types, spatial types, or columns with the Auto_increment attribute,

You can also specify the default Def_value clause. To indicate when a new row is created, if a value is not specified, the column will be defaulted to the value Def_value

The default value Def_value must be a constant except for the timestamp column and the datetime column. He cannot be an expression, nor can he reference another column

If the default clause is not explicitly included, and the column is allowed to be null, it defaults to null

If the column does not allow null values, there is no default clause, which affects MySQL's handling of columns:

1.如果没有启用SQL的严格模式，这列将会被设置成其数据类型的隐含默认值。2.启用SQL严格模式之后，如果表是事务性的，就会出现错误。这条语句会中止执行，然后回滚。对于非事务性的表，如果这行是该语句插入的第一个行，那么会出现一个错误，该语句中止执行，如果不是第一行，那么可以选择中止执行，或选择把这列设置为它的隐含默认值，同时发出一条警告信息

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

1.对于数字列（不包含那些具有auto_increment属性的列），其默认值为0.  对于auto_increment列，默认值是下一个列的列序号。2.对于大多数时态类型列，其默认值为该类型的“零”值  对于enum列，默认值为枚举集里的第一个元素，  对于set列，如果不允许NULL值，默认值将是一个空集不过它等价于空串。              

Study with me MySQL Technical Insider (fifth Edition): (chapter III study diary 3)