MySQL常用数据类型以及内置函数

常见数据类型

1）、数值型：整型、小数（定点数、浮点数）
①整型 分为有符号和无符号两种
Tinyint：1个字节 Smallint：2个字节 Mediumint：3个字节
Int/Integer：4个字节 Bigint：8个字节
默认为无符号类型，即-n~n，若需无符号的，则应追加unsigned关键字
若插入数值超出范围，会默认添加临界值
整型类型括号内的长度即显示的域宽，需添加关键字ZEROFILL（用0填充），此时unsigned关键字无效
②小数
浮点型：float（M，D）：4个字节 double（M，D）：8个字节
定点型：DECIMAL/DEC（M，D） M+2个字节，最大取值与double相同，由M和D决定，底层存储为字符串
M和D：M为数值总位数（1~65），D为小数位数（0~30)，DECIMAL默认为（10，0）
定点型的精确度较高，货币运算等精度较高的考虑使用

2）、字符型：短文本、长文本
①短文本：
char （固定长度字符，较费空间，存储效率高）
varchar （可变长度（需要0.5个字节来存储具体长度），节省空间，存储效率低）
binary、varbinary（存储二进制数据）
ENUM（’ ‘,’ ‘,’ ‘) 设置固定的数据进行插入
SET （’ ‘,’ ‘,’ ') ENUM只能选择一个进行插入，而SET可以选多个
②长：text 、 blob（大二进制）
字符类型括号内的长度即所存储的最大字符数
PS：char的字符长度默认为1，varchar的字符长度必须进行设置

3）、日期型
date：4个字节 1000-01-01~9999-12-31
datetime：8个字节 1000-01-01 00：00：00 ~ 9999-12-31 23：59：59
timestamp:4个字节 1970-01-01 08：00：01 ~ 2038年某一时刻，底层存储为毫秒值，比较效率高
time：3个字节 -838：59：59 ~ 839：59：59
year：1个字节 1901 ~ 2155
timestamp和实际的时区有关，也受Mysql版本和SQLMode影响，datetime只能反映插入时的当地时区
PS：SHOW VARIABLES LIKE ‘time_zone’显示当前时区
SET time_zone = ‘ ’ 设置时区

单行函数（处理）

1）字符函数
IFNULL(字段，返回值) # 判断字段是否为NULL
ISNULL(字段) # NULL返回1，否则0
LENGTH（字段） #获取字段数据的字节长度
CONCAT （字段1，字段2） #将字段数据进行拼接
UPPER (字段) #字段数据大写
LOWER （字段） #字段数据小写
SUBSTRING（字段，索引） #索引从一开始，截取字符串，重载函数可指定字符长度，可简写SUBSTR
INSTR（字段，数据） #返回第一次出现数据的索引值
TRIM（字段） #去除字段数据前后空格，可以去除指定字符TRIM（字符 FROM　字段）
LPAD (字段，域宽，填充符) #用指定填充符左填充字段数据
RPAD(字段，域宽，填充符) #右填充
REPLACE（字段，目标数据，替换数据） #将字段数据中的部分进行替换

2）数学函数
ROUND（字段） #四舍五入，只与后一位有关，可添加位数参数，
CEIL（字段） #向上取整
FLOOR（字段） #向下取整
TRUNCATE（字段） #截断，去除多余位数
MOD（字段） #取余，同% ，余数符号只与被除数符号有关（a%b = a-a/b*b)
RAND（） #0~1小数

3）日期函数
NOW（） #返回当前系统日期+时间
CURDATE（） #返回当前系统日期
CURTIME（） #返回当前时间
YRAE（DATE）、MONTH（DATE）… #获取年月日时分秒
STR_TO_DATE（’ date ‘,’ format ‘） #将日期字符转换为指定的日期（%Y为四位 %y为两位）
DATE_FORMAT（’ date ‘,’ format '） #将日期转换为指定的日期字符(%M为英语月份，%m为汉语）
DATEDIFF（ ， ） #返回时间差

4）其他函数
VERSION（） #查看版本
DATABASE（） #当前数据库
USER（） #用户
PASSWORD（’字符‘）/MP5（） #字符加密

5）流程控制函数
IF（ ，，） #同判断语句（ ？：）
CASE 字段 WHEN 值 THEN WHEN 值 THEN … ELSE END CASE #类似于SWITCH
CASE WHEN 条件 THEN …ELSE END CASE #类似于if elseif
PS：当ELSE省略且其他条件都不符合时，返回NULL

分组函数 （统计）又称统计、复合、组函数

SUM（） #求和 (忽略null值) 参数：数值型
AVG（） #平均 (忽略null值、包括除数) 参数：数值型
MAX（） #最大值 (忽略null值) 参数：数值型、字符型、日期型
MIN（） #最小值 (忽略null值) 参数：数值型、字符型、日期型
COUNT（） #计算个数 （不计算NULL值个数） 参数：数值型、字符型、日期型
因为null的影响， 常使用（*）统计总行数，count（常量值）相当于加上一列常量统计行数，常用count（1）
tips：可以与DISTINCT搭配使用

统计效率：
MYISAM存储引擎下，COUNT（*）的效率高，有内部计数器，直接可返回
INNODB存储引擎下，COUNT（*）和COUN（1）效率差不多，比COUNT（字段）效率高
PS：查询结果应该是一个表格，查询规格有要求，一般与分组查询搭配使用