3.4 MySQL数据类型及时间类型剪切操作
3.4.1 整数类型的操作
例:create database db;(创建数据库)
--> use db;(使用数据库)
-->create table t1(c1 tinyint unsigned,c2 tinyint);(创建表)
-->desc t1;(查看表结构)
-->insert into t1 values(10,-1);(插入内容,注:可自行对3.3的表格进行测试)
-->select * from t1;(查看表内容)
-->insert into t1 values (257,1000);(边界值测试,可插入,但有警告)
-->show warnings;(查看警告)
例:create table t5 (c1 bit(16));//十六进制
-->insert into t5 values(b'010101');//插入内容
-->select hex(c1) from t5;//十六进制查询
3.4.2 时间类型剪切操作
例:create table t1(c1 year);
-->desc t1;
--------------------------------------------------------------------------
field
type
null
key
default
extra
c1
year(4)
yes
null
--------------------------------------------------------------------------
(1)间接插入年份
insert into t1 values(1);
-->select * from t1;
----------
c1
2001
----------
insert into t1 values(70);
-->select * from t1;
----------
c1
1970
---------
注:从1-69 插入显示 2001--2069
70- 1970--
(2)直接插入年份 insert into t1 values(2013);
(3)时间 //支持24小时格式
insert into t2(c1 time);
-->insert into t2 values("12:34:20");
("22:24:30");
(4)日期
create table t3 values(c1 date);
-->insert into t3 values("2017-11-2");
-->
insert into t3 values("999
-11-2");
-->select * from t3
---------------------------
c1
2017-11-2
0999-11-2
---------------------------
create table t4(c1 datetime);
-->insert into t4 values("2017-11-2 15:17:20");
-->alter table t4 add c2 timestamp;
-->insert into t4 values("2017-11-2 15:17:20","2017-11-2 15:17:20");
-->select * from t4;
----------------------------------------------------------------------------------------
c1
c2
2017-11-2 15:17:20 2017-11-2 15:20
:24 (时间偏移)
2017-11-2 15:17:20 2017-11-2 15:17:20
----------------------------------------------------------------------------------------
(5)函数
1)select now();
2)select current_timestamp;
3)select date(now());
//获取日期部分
4)select time(now()); time(current_timestamp) //year/month/day(now());
//对时间进行处理
5)select unix_timestamp();
//显示当前时间戳,可不接受参数执行
6)select unix_timestamp("2017-11-10 15:27:34");
//可将时间转化为标准的时间戳
7)select from_unixtime(1111111111);
//可对其进行加减运算