mysql 学习小札（3）-- 数据的处理

一、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的串列，太长的串会被裁剪为适合该列存储的最大长串

               → 对于日期和列，非法值被转化为该类型适当的“零值”