常见数据类型
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:查询结果应该是一个表格,查询规格有要求,一般与分组查询搭配使用