mysql 学习小札（3）-- 数据的处理_MySQL
程序员文章站
2022-06-12 11:21:44
...
一、mysql数据类型 1） 数值值 ■ 十六进制的数不区分大小写，但其前缀0x不能为0X，即0x0A和0x0a合法，0X0a不合法 ★★★ ■ 科学计数法 → 1.34E+12、1.34e-12都是合法的，但1.24E12不合法，指数前的符号必须给出 2）字符串值 ■ 既可用单引号，也可用双引号 ■ NUL为一个零值（/0 ASCII 0），NULL为没有值 ★★★ ■ 串中再使用引号 → 若串使用相同的引号，则在串中在需要使用引号的地方双写即可 → 若串使用不同的引号，则在串中在需要使用引号的地方直接用即可 → 用反斜杠的方式表示 3）日期、时间值 ■ DATE_FORMATE（）函数以任意形式显示时间值 4）NULL值 ■ 无类型的值，可对其进行检索，但不能对其进行算术运算
二、列类型 1）概述 ■ 数值列类型 → TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT、FLOAT、DOUBLE、DECIMAL ■ 串列类型 → CHAR、VARCHAR、TINYBLOB、BLOB、MEDIUM、BOLB、LONGBLOB → TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、ENUM、SET ■ 时间与日期类型 → DATE(YYYY-MM-DD)、TIME(hh:mm:ss) → DATETIME(YYYY-MM-DD hh:mm:ss)、TIMESTMAP(YYYYMMDDhhmmss)、YEAR ■ 语法 → col_name col_type [col_attribute] [general_attributes] ★★ 2）数值列类型 ■ 三种浮点数类型（FLOAT、DOUBLE、DECIMAL）→ DECIMALE(M, D) ，如DECIMAL(4,1)范围-9.9~99.9 ■ 类型属性 → ZEROFILL:相应列的显示值用前导零填充，达到显示宽度 → 只用于整数列的属性 -- AUTO_INCREMENT:一般从零开始; UNSIGNED:禁用负值 ★★★ → 通用属性NULL、NOT NULL -- 缺省为NULL; DEFAULT -- 指定缺省值 ■ 序列号 → LAST_INSERT_ID() 返回最近自动产生的序列编号值，若当前会话没有生成AUTO_INCREMENT则返回0 → UPDATE seq_table SET seq = LAST_INSERT_ID(seq+1) -- 不使用AUTO_INCREMENT 注释：LAST_INSERT_ID(exp)后下一次调用其但不适用参数时返回exp ★★★
3）串列类型 ■ “通用”类型 -- 可存储二进制数据（声音和音像等）、gzip的输出数据 ■ CHAR & VARCHAR → CHAR(M) -- 短于M个字节在右边加空格，但检索时去掉 → VARCHAR(M) -- 只用必须的字节数存放，结尾空格去掉，再加一个字节记录长度 → CHAR(0) -- 占位符，只占一位，后期可用ALTER TABLE 修改 → 行定长的表比可变长的容易处理，表行只在所有行为定长类型时才是定长的 → ALTER TABLE tbl_name MODIFY c1 CHAR(10) , MODIFY c2 CHAR(10) ★★★ ■ BLOB & TEXT → BLOB 与 TEXT 同 VARCHAR 也是可变长的 （可通过DESCRIBE tbl_name查看细节） → BLOB -- 区分大小写; TEXT -- 不区分大小写 → 使用OPTIMIZE TABLE 减少碎片率以保持良好的性能 → 若使用很大的值，则需调整max_allowed_packet参数的值
■ ENUM & SET → ENUM -- 集合中某一成员; SET -- 集合中任意或所有成员 → 将一个错误值赋给ENUM时， mysql自动将其换成错误成员 → SET集合成员不是顺序编号的， 每个成员对应SET值中一个二进制位 → 如果SET列赋予一个含有未作为集合成员列出的子串的值，那么这些子串被删除 ★★★ 4）时间类型 ■ TIMESTAMP -- 取值范围从19700101000000到2037年的某个时间 对应UNIX时间（4B） ■ MYSQL 没有可在记录建立时设置为当前时间和日期，并自此以后保持不变的类型，但有实现方法 → 使用TIMESTAMP列，建立一个记录时，设置该列为NULL，初始化为当前时间和日期 INSERT INTO tbl_name (ts_col, ...) VALUES(NULL, ...) → 使用DATETIME列， 在建立记录时，将该值设置为NOW() ★★★ INSERT INTO tbl_name(ts_col) VALUES(NOW(),...) ★★★ 注：赋予一个明确的值使时间戳机制生效 ■ MYSQL会按照年份推测规则将YEAR类型的2位值转化为4位 → 97 得到1997， 14得到2014 但00得到0000，“00”得到2000 ★★★ ■ 属性 -- 缺省时默认为NULL
■ MYSQL没有千年虫问题
三）选择列的属性 ■ 表示钱用DECIMAL(M,2)类型 ■ 性能与效率 → 数值运算一般比串运算更快，如果串列的数值有限应使用ENUM或SET → 可索引的类型，不能对BLOB和TEXT进行索引 → 如果定义一列为NOT NULL 其处理更快 ■ 区分大小写的比较
→ my_col = "ABC" -- 不区分大小写 → BINARY my_col = "ABC" -- 区分大小写 → my_col = BINARY "ABC" -- 区分大小写
■ AUTO_INCREMENT、PRIMARY KEY、UNIQUE
四）表达式&类型转换
■ 撰写表达式
■ ANSI SQL ||作为串运算符，但在mysql中表示一个逻辑或运算 SELECT "ABC" || "CD" 返回0 ，串被转换成0
■ 比较运算符 → IN、BETWEEN...AND...、LIKE、NOT LIKE、REGEXP、NOT REGEXP → （两个操作数是否相等，即使为NULL）、IS NULL 、IS NOT NULL -- BINARY (BINARY "abc" = "Abc" 返回0) 注：除非至少有一个操作数为二进制串，否则LIKE是不区分大小写的 ★★★
■ 模式匹配 -- 具有NULL操作数的任何模式匹配都将失败
■ 运算符优先级 -- 从大到小 
■ 如果给函数传递一个NULL，除了那些处理NULL值得函数，其他都返回NULL ★★★ ■ 类型转换 → 除了，涉及NULL值的比较其都为NULL → 如果两个操作数都是串，则按串字典的顺序比较 → 不与数进行比较的十六进制常量按二进制串进行比较 ★★ → 一个为TIMESTAMP 或DATETIME 另一个为常量，则按TIMESTAMP比较 → 数和串比较，串转化为数，若转换后不是数，则结果为0 ★★★ 
→ 强制类型转换 
■ 时间与日期 → TO_DAYS("1997-04-10") 、TO_DAYS("19970410")、TO_DAYS(19970410)等同
■ 超范围值和非法值得转换 → 对于数值和TIME，超出合法范围内的值被裁剪为相应取值范围内最接近的值 → 对于非ENUM、SET的串列，太长的串会被裁剪为适合该列存储的最大长串 → 对于日期和列，非法值被转化为该类型适当的“零值”
二、列类型 1）概述 ■ 数值列类型 → TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT、FLOAT、DOUBLE、DECIMAL ■ 串列类型 → CHAR、VARCHAR、TINYBLOB、BLOB、MEDIUM、BOLB、LONGBLOB → TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、ENUM、SET ■ 时间与日期类型 → DATE(YYYY-MM-DD)、TIME(hh:mm:ss) → DATETIME(YYYY-MM-DD hh:mm:ss)、TIMESTMAP(YYYYMMDDhhmmss)、YEAR ■ 语法 → col_name col_type [col_attribute] [general_attributes] ★★ 2）数值列类型 ■ 三种浮点数类型（FLOAT、DOUBLE、DECIMAL）→ DECIMALE(M, D) ，如DECIMAL(4,1)范围-9.9~99.9 ■ 类型属性 → ZEROFILL:相应列的显示值用前导零填充，达到显示宽度 → 只用于整数列的属性 -- AUTO_INCREMENT:一般从零开始; UNSIGNED:禁用负值 ★★★ → 通用属性NULL、NOT NULL -- 缺省为NULL; DEFAULT -- 指定缺省值 ■ 序列号 → LAST_INSERT_ID() 返回最近自动产生的序列编号值，若当前会话没有生成AUTO_INCREMENT则返回0 → UPDATE seq_table SET seq = LAST_INSERT_ID(seq+1) -- 不使用AUTO_INCREMENT 注释：LAST_INSERT_ID(exp)后下一次调用其但不适用参数时返回exp ★★★
3）串列类型 ■ “通用”类型 -- 可存储二进制数据（声音和音像等）、gzip的输出数据 ■ CHAR & VARCHAR → CHAR(M) -- 短于M个字节在右边加空格，但检索时去掉 → VARCHAR(M) -- 只用必须的字节数存放，结尾空格去掉，再加一个字节记录长度 → CHAR(0) -- 占位符，只占一位，后期可用ALTER TABLE 修改 → 行定长的表比可变长的容易处理，表行只在所有行为定长类型时才是定长的 → ALTER TABLE tbl_name MODIFY c1 CHAR(10) , MODIFY c2 CHAR(10) ★★★ ■ BLOB & TEXT → BLOB 与 TEXT 同 VARCHAR 也是可变长的 （可通过DESCRIBE tbl_name查看细节） → BLOB -- 区分大小写; TEXT -- 不区分大小写 → 使用OPTIMIZE TABLE 减少碎片率以保持良好的性能 → 若使用很大的值，则需调整max_allowed_packet参数的值
■ ENUM & SET → ENUM -- 集合中某一成员; SET -- 集合中任意或所有成员 → 将一个错误值赋给ENUM时， mysql自动将其换成错误成员 → SET集合成员不是顺序编号的， 每个成员对应SET值中一个二进制位 → 如果SET列赋予一个含有未作为集合成员列出的子串的值，那么这些子串被删除 ★★★ 4）时间类型 ■ TIMESTAMP -- 取值范围从19700101000000到2037年的某个时间 对应UNIX时间（4B） ■ MYSQL 没有可在记录建立时设置为当前时间和日期，并自此以后保持不变的类型，但有实现方法 → 使用TIMESTAMP列，建立一个记录时，设置该列为NULL，初始化为当前时间和日期 INSERT INTO tbl_name (ts_col, ...) VALUES(NULL, ...) → 使用DATETIME列， 在建立记录时，将该值设置为NOW() ★★★ INSERT INTO tbl_name(ts_col) VALUES(NOW(),...) ★★★ 注：赋予一个明确的值使时间戳机制生效 ■ MYSQL会按照年份推测规则将YEAR类型的2位值转化为4位 → 97 得到1997， 14得到2014 但00得到0000，“00”得到2000 ★★★ ■ 属性 -- 缺省时默认为NULL
■ MYSQL没有千年虫问题
三）选择列的属性 ■ 表示钱用DECIMAL(M,2)类型 ■ 性能与效率 → 数值运算一般比串运算更快，如果串列的数值有限应使用ENUM或SET → 可索引的类型，不能对BLOB和TEXT进行索引 → 如果定义一列为NOT NULL 其处理更快 ■ 区分大小写的比较
■ AUTO_INCREMENT、PRIMARY KEY、UNIQUE
四）表达式&类型转换
■ 撰写表达式
■ 比较运算符 → IN、BETWEEN...AND...、LIKE、NOT LIKE、REGEXP、NOT REGEXP → （两个操作数是否相等，即使为NULL）、IS NULL 、IS NOT NULL -- BINARY (BINARY "abc" = "Abc" 返回0) 注：除非至少有一个操作数为二进制串，否则LIKE是不区分大小写的 ★★★
■ 模式匹配 -- 具有NULL操作数的任何模式匹配都将失败
■ 时间与日期 → TO_DAYS("1997-04-10") 、TO_DAYS("19970410")、TO_DAYS(19970410)等同
■ 超范围值和非法值得转换 → 对于数值和TIME，超出合法范围内的值被裁剪为相应取值范围内最接近的值 → 对于非ENUM、SET的串列，太长的串会被裁剪为适合该列存储的最大长串 → 对于日期和列，非法值被转化为该类型适当的“零值”