MySQL_数据类型及函数

数据类型

整数类型

• TINYINT相当于java数据类型中的byte，SMALLINT相当于short，BIGINT相当于long
• 整数类型可以设置 自增约束条件 auto_increment
• 创建字段定义整数类型时 int(m)，m仅限制显示长度，并不能限制其本身的存储大小

浮点数类型和定点数类型

• 浮点数和定点数定义方式都可以用（m,n），m表示总位数，n表示小数位数
• DECIMAL是以字符串形式存储的，精确度要求高时推荐使用

文本字符串类型

• CHAR(M)定义时指定长度，当保存数据时，右侧填充空格补足长度，但插入数据中尾部的空格会自动删除
• VARCHAR实际占用空间为实际长度+1，不会删除插入数据尾部的空格
• CHAR浪费存储空间，但处理速度快
• MyISAM引擎推荐使用CHAR，InnoDB推荐使用VARCHAR

在检索效率上来讲,char > varchar,因此在使用中,如果确定某个字段的值的长度,可以使用char,否则应该尽量使用varchar.例如存储用户MD5加密后的密码,则应该使用char.

二进制字符串类型

• BLOB存储的二进制字符串，没有字符集，排序和比较基于列值字节的数值
• TEXT存储的非二进制字符串，有字符集，排序和比较基于字符集

日期、时间类型

• DATETIME存储的是输入的时间
• TIMESTAMP存储的是时间戳，可以理解为最后一次修改时间

常见运算

算术运算

select 2+3 from dual;	-- +-*/%同理

比较运算

select null=null,null<=>null from dual;

• 结果是null和1，null参与算术运算结果为null，如果用安全的等于<=>可以比较null

逻辑运算

• 逻辑运算的结果为1或0
• 包括and,or,not,xor,&&,||,!

位运算

数学函数

-- 绝对值
select abs(-4);
-- 圆周率
select pi();
-- 非负数的二次平方根
select sqrt(4);
-- 取余
select mod(5,3);
-- 向上取整
select ceil(3.2);
select ceiling(3.2);
-- 向下取整
select floor(3.6);
-- 随机数0-1
select rand();
-- 若干个相同的随机数
select rand(1),rand(1),rand(1);
-- 四舍五入
select round(-10.5);	-- -10 和java的机制不同，日常数学机制
-- 保留两位小数四舍五入
select round(10.95353,2);
-- 截取保留两位小数，不四舍五入
select truncate(10.5353,2);	-- 10.53
-- 符号
select sign(-2);	-- -1
select sign(0);	-- 0
select sign(3);	-- 1
-- 幂运算
select pow(2,3);
select power(2,3);
select exp(2);

字符串函数

-- 字符串长度
select char_length("i love you");
-- 连接字符串
select concat("hello","you");
-- 指定分隔符连接字符串
select concat_ws("-","hello","u");
-- 替换子串
select insert("hello",1,2,"hhhh");	-- 索引从1开始，将he替换为hhhh
-- 大小写转换
select lower("LOVE"),lcase("LOVE"),upper("lova"),ucase("lova");
-- 获取左右两端的子串
select left("love",2),right("love",2);
-- 删除左右两边的空格或者子串
select ltrim(" love"),rtrim("love "),trim(" love "),trim("l" from "love");
-- 子串替换子串
select replace("love","ov","ik");
-- 字符串比较
select strcmp("apple","Apple"),strcmp("apple","ball");	-- 不区分大小写
-- 返回子串
select substring("loce",1,2),mid("love",1,2);	-- 索引从1开始
-- 反转
select reverse("love");
-- 返回字符串列表中指定位置字符串
select elt(1,"i","love","u");
-- 返回字符串在字符串列表中第一次出现的位置
select field("hhh","hhh","hhhh","hhh");
-- 返回字符串在字符串列表出现的位置
select find_in_set("hhh",set);

日期和时间函数

-- 当前日期
select curdate(),current_date();
-- 无分隔符的当前日期
select curdate()+0,current_date()+0;
-- 当前时间
select curtime(),current_time();
-- 无分隔符的当前时间
select curtime()+0,current_time()+0;
-- 当前日期和时间
select current_timestamp(),localtime(),now(),sysdate();
-- 时间戳，1970-1-1 0:0:0 GMT之后的秒数
select unix_timestamp(now());
-- 将时间戳转换为普通格式的时间
select from_unixtime(unix_timestamp(now()));
-- UTC世界标准时间的日期值和时间值
select utc_date(),utc_time();
-- 月份
select month(now());
-- 月份名称
select monthname(now());
-- 工作日双休日名称
select dayname(curdate());
-- 周内索引
select dayofweek(now());	-- 周日=1
-- 工作日索引
select weekday("2019-11-29");	-- 周一=0
-- 年中第几周
select week(now()), weekofyear(now());
-- 年中第几天
select dayofyear(now());
-- 月中第几天
select dayofmonth(now());
-- 年份
select year(now());
-- 季度
select quarter(now());
-- 分钟数
select minute(now());
-- 秒数
select second(now());
-- 时间转化为秒
select time_to_sec(now());

加密和解密函数

-- 不可逆加密，用于服务器鉴定系统，不用于应用程序
select password(123);	--  *23AE809DDACAF96AF0FD78ED04B6A265E05AA257
-- MD5加密
select md5(123);	-- 202cb962ac59075b964b07152d234b70
-- 加密函数
select encode(123,123);	-- ,�
-- 解密函数
select decode(encode(123,123),123);	-- 123

其他函数

-- 条件判断函数
select if(1>0,1,0);
-- null判断函数
select ifnull(null,0);
-- case
select case 2+3 
when 5 then "hhh"
when 2 then "aaa"
end;
-- 版本
select version();
-- 连接次数
select connection_id();
-- 用户名，主机名
select user(),current_user(),system_user(),session_user();
-- 数字格式化 四舍五入保留2位小数
select format(3.123442,2);
-- 数字转型
-- 可转换：binary,char,date,time,datetime,decimal,signed,unsigned
-- cast(x as type)
-- convert(x,type);

-- 进制转换
select conv(173,10,2);	-- 将173从10进制转到2进制