mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| sales |
+----------------+
1 row in set (0.00 sec)

mysql> create table t (a int,b timestamp default current_timestamp) engine=InnoDB;
Query OK, 0 rows affected (0.07 sec)

mysql> show create table t;
+-------+----------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------------+
| t | CREATE TABLE `t` (
`a` int(11) DEFAULT NULL,
`b` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into t(a) values (1);
Query OK, 1 row affected (0.03 sec)

mysql> select * from t\G;
*************************** 1. row ***************************
a: 1
b: 2015-07-15 13:14:16
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> create table t1(a int,b timestamp on update current_timestamp);
Query OK, 0 rows affected (0.05 sec)

mysql> insert into t1 select 1,current_timestamp;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> select * from t1;
+------+---------------------+
| a | b |
+------+---------------------+
| 1 | 2015-07-15 13:16:03 |
+------+---------------------+
1 row in set (0.00 sec)

mysql> update t1 set a=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from t1;
+------+---------------------+
| a | b |
+------+---------------------+
| 2 | 2015-07-15 13:16:28 |
+------+---------------------+
1 row in set (0.00 sec)

mysql> update t1 set a=2;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0

mysql> select * from t1;
+------+---------------------+
| a | b |
+------+---------------------+
| 2 | 2015-07-15 13:16:28 |
+------+---------------------+
1 row in set (0.00 sec)

mysql> create table t2 (a year(2));
Query OK, 0 rows affected, 1 warning (0.05 sec)

mysql> insert into t2 select '1990';
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> select * from t2;
+------+
| a |
+------+
| 1990 |
+------+
1 row in set (0.00 sec)

对于year(4)，显示年份的范围为1901-2155；对于year(2)其显示的范围为1970-2070，在YEAR(2)下，00-69代表2000-2069

mysql> create table t4(a time);
Query OK, 0 rows affected (0.14 sec)

mysql> insert into t4 select '14:40:20.123456';
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> select * from t4;
+----------+
| a |
+----------+
| 14:40:20 |
+----------+
1 row in set (0.00 sec)

mysql> drop table t;
Query OK, 0 rows affected (0.05 sec)

mysql> create table t(d date,t time,dt datetime);
Query OK, 0 rows affected (0.91 sec)

mysql> insert into t values(now(),now(),now());
Query OK, 1 row affected, 1 warning (0.04 sec)

mysql> select * from t;
+------------+----------+---------------------+
| d | t | dt |
+------------+----------+---------------------+
| 2015-07-16 | 09:06:09 | 2015-07-16 09:06:09 |
+------------+----------+---------------------+
1 row in set (0.00 sec)

mysql> drop table t1;
Query OK, 0 rows affected (0.05 sec)

mysql> create table t1(id1 timestamp);
Query OK, 0 rows affected (0.04 sec)

mysql> insert into t1 values(null);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;
+---------------------+
| id1 |
+---------------------+
| 2015-07-16 09:07:21 |
+---------------------+
1 row in set (0.00 sec)

mysql> show create table t1;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`id1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> create table t2(id1 timestamp,id2 timestamp);
Query OK, 0 rows affected (0.04 sec)

mysql> show create table t2;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t2 | CREATE TABLE `t2` (
`id1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`id2` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table t2 modify id2 timestamp default current_timestamp;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show create table t2;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t2 | CREATE TABLE `t2` (
`id1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`id2` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into t2 values(null,null);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t2;
+---------------------+---------------------+
| id1 | id2 |
+---------------------+---------------------+
| 2015-07-16 09:10:03 | 2015-07-16 09:10:03 |
+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> update t3 set id2=now();
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from t3;
+---------------------+---------------------+
| id1 | id2 |
+---------------------+---------------------+
| 2015-07-16 10:16:09 | 2015-07-16 10:34:57 |
+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> ? timestamp
Name: 'TIMESTAMP'
Description:
TIMESTAMP[(fsp)]

A timestamp. The range is '1970-01-01 00:00:01.000000' UTC to
'2038-01-19 03:14:07.999999' UTC. TIMESTAMP values are stored as the
number of seconds since the epoch ('1970-01-01 00:00:00' UTC). A
TIMESTAMP cannot represent the value '1970-01-01 00:00:00' because that
is equivalent to 0 seconds from the epoch and the value 0 is reserved
for representing '0000-00-00 00:00:00', the "zero" TIMESTAMP value.

mysql> select current_date() ;
+----------------+
| current_date() |
+----------------+
| 2015-07-16 |
+----------------+
1 row in set (0.00 sec)

mysql> create table t6(dt datetime);
Query OK, 0 rows affected (0.04 sec)

mysql> insert into t6 values('2007-9-3 12:10:10');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t6 values('2007/9/3 12+10+10');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t6 values('20070903121010');
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t6;
+---------------------+
| dt |
+---------------------+
| 2007-09-03 12:10:10 |
| 2007-09-03 12:10:10 |
| 2007-09-03 12:10:10 |
+---------------------+
3 rows in set (0.00 sec)

mysql> create table vc(v varchar(4),c char(4));
Query OK, 0 rows affected (0.05 sec)

mysql> insert into vc values('ab ','ab ');
Query OK, 1 row affected (0.01 sec)

mysql> select length(v),length(c) from vc;
+-----------+-----------+
| length(v) | length(c) |
+-----------+-----------+
| 4 | 2 |
+-----------+-----------+
1 row in set (0.00 sec)

mysql> select concat(v,'+'),concat(c,'+') from vc;
+---------------+---------------+
| concat(v,'+') | concat(c,'+') |
+---------------+---------------+
| ab + | ab+ |
+---------------+---------------+
1 row in set (0.00 sec)

mysql> drop table t ;
Query OK, 0 rows affected (0.02 sec)

mysql> create table t(c binary(3));
Query OK, 0 rows affected (0.06 sec)

mysql> insert into t set c='a';
Query OK, 1 row affected (0.02 sec)

mysql> select *,hex(c),c='a',c='a\0',c='a\0\0' from t;
+------+--------+-------+---------+-----------+
| c | hex(c) | c='a' | c='a\0' | c='a\0\0' |
+------+--------+-------+---------+-----------+
| a | 610000 | 0 | 0 | 1 |
+------+--------+-------+---------+-----------+
1 row in set (0.00 sec)

mysql> ? varchar
Name: 'VARCHAR'
Description:
[NATIONAL] VARCHAR(M) [CHARACTER SET charset_name] [COLLATE
collation_name]

A variable-length string. M represents the maximum column length in
characters. The range of M is 0 to 65,535.

mysql> drop table t;
Query OK, 0 rows affected (0.05 sec)

mysql> create table t(gender enum('M','F'));
Query OK, 0 rows affected (0.06 sec)

mysql> insert into t values('M'),('1'),('F'),(NULL);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> select * from t;
+--------+
| gender |
+--------+
| M      |
| M      |
| F      |
| NULL   |
+--------+
4 rows in set (0.00 sec)

mysql> insert into t values('2');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t;
+--------+
| gender |
+--------+
| M      |
| M      |
| F      |
| NULL   |
| F      |
+--------+
5 rows in set (0.00 sec)

mysql> truncate table t;
Query OK, 0 rows affected (0.08 sec)

mysql> insert into t values('2');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t;
+--------+
| gender |
+--------+
| F |
+--------+
1 row in set (0.00 sec)

mysql> insert into t values('3');
ERROR 1265 (01000): Data truncated for column 'gender' at row 1
mysql> insert into t values('1');
Query OK, 1 row affected (0.01 sec)

mysql> drop table t1;
Query OK, 0 rows affected (0.05 sec)

mysql> create table t1(col set('a','b','c','d'));
Query OK, 0 rows affected (0.09 sec)

mysql> insert into t1 values ('a,b'),('a,d,a'),('a,c'),('a');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> select * from t1;
+------+
| col |
+------+
| a,b |
| a,d |
| a,c |
| a |
+------+
4 rows in set (0.00 sec)

mysql> insert into t1 values ('a,b,c');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;
+-------+
| col   |
+-------+
| a,b   |
| a,d   |
| a,c   |
| a     |
| a,b,c |
+-------+
5 rows in set (0.00 sec)

mysql> select now(),current_timestamp(),sysdate()\G;
*************************** 1. row ***************************
now(): 2015-07-15 13:26:33
current_timestamp(): 2015-07-15 13:26:33
sysdate(): 2015-07-15 13:26:33
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> select now(),current_timestamp(),sysdate(),sleep(2),now(),current_timestamp(),sysdate()\G;
*************************** 1. row ***************************
              now(): 2015-07-15 13:27:32
current_timestamp(): 2015-07-15 13:27:32
          sysdate(): 2015-07-15 13:27:32
           sleep(2): 0
              now(): 2015-07-15 13:27:32
current_timestamp(): 2015-07-15 13:27:32
          sysdate(): 2015-07-15 13:27:34
1 row in set (2.00 sec)

ERROR:
No query specified

mysql> select now(),date_add(now(),interval 1 day) as tomorrow,date_sub(now(),interval 1 day) as yesterday\G;
*************************** 1. row ***************************
now(): 2015-07-15 13:31:04
tomorrow: 2015-07-16 13:31:04
yesterday: 2015-07-14 13:31:04
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> select now(),date_add('2000-2-29',interval 4 year) as year\G;
*************************** 1. row ***************************
now(): 2015-07-15 13:32:43
year: 2004-02-29
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> select date_add('2000-2-29',interval 4 year) as year\G;
*************************** 1. row ***************************
year: 2004-02-29
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> select date_add('2000-2-29',interval 1 year) as year\G;
*************************** 1. row ***************************
year: 2001-02-28
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> select now(),date_add(now(),interval 1 hour) as year\G;
*************************** 1. row ***************************
now(): 2015-07-15 13:34:17
year: 2015-07-15 14:34:17
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> select date_format(now(),'%Y%m%d') as datetime;
+----------+
| datetime |
+----------+
| 20150715 |
+----------+
1 row in set (0.00 sec)

mysql> select date_format(now(),'%Y-%m-%d') as datetime;
+------------+
| datetime |
+------------+
| 2015-07-15 |
+------------+
1 row in set (0.00 sec)

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> create table session(id int not null auto_increment,app varchar(10) not null,usr varchar(10) not null,starttime time not null,endtime time not null,primary key(id));
Query OK, 0 rows affected (0.04 sec)

mysql> truncate table session;
Query OK, 0 rows affected (0.06 sec)

insert into session (app,usr,starttime,endtime) values ('app1','user1','08:30','10:30');

insert into session (app,usr,starttime,endtime) values ('app1','user2','08:30','08:45');

insert into session (app,usr,starttime,endtime) values ('app1','user1','09:00','09:30');

insert into session (app,usr,starttime,endtime) values ('app1','user2','09:15','10:30');

insert into session (app,usr,starttime,endtime) values ('app1','user1','09:15','09:30');

insert into session (app,usr,starttime,endtime) values ('app1','user2','10:30','14:30');

insert into session (app,usr,starttime,endtime) values ('app2','user1','10:45','11:30');

insert into session (app,usr,starttime,endtime) values ('app1','user2','11:00','12:30');

insert into session (app,usr,starttime,endtime) values ('app2','user1','08:30','08:45');

insert into session (app,usr,starttime,endtime) values ('app2','user2','09:00','09:30');

insert into session (app,usr,starttime,endtime) values ('app2','user1','11:45','12:00');

insert into session (app,usr,starttime,endtime) values ('app2','user2','12:30','14:00');

insert into session (app,usr,starttime,endtime) values ('app2','user1','12:45','13:30');

insert into session (app,usr,starttime,endtime) values ('app2','user2','13:00','14:00');

insert into session (app,usr,starttime,endtime) values ('app2','user1','14:00','16:30');

insert into session (app,usr,starttime,endtime) values ('app2','user2','15:30','17:00');

commit;

mysql> select * from session;
+----+------+-------+-----------+----------+
| id | app | usr | starttime | endtime |
+----+------+-------+-----------+----------+
| 1 | app1 | user1 | 08:30:00 | 10:30:00 |
| 2 | app1 | user2 | 08:30:00 | 08:45:00 |
| 3 | app1 | user1 | 09:00:00 | 09:30:00 |
| 4 | app1 | user2 | 09:15:00 | 10:30:00 |
| 5 | app1 | user1 | 09:15:00 | 09:30:00 |
| 6 | app1 | user2 | 10:30:00 | 14:30:00 |
| 7 | app2 | user1 | 10:45:00 | 11:30:00 |
| 8 | app1 | user2 | 11:00:00 | 12:30:00 |
| 9 | app2 | user1 | 08:30:00 | 08:45:00 |
| 10 | app2 | user2 | 09:00:00 | 09:30:00 |
| 11 | app2 | user1 | 11:45:00 | 12:00:00 |
| 12 | app2 | user2 | 12:30:00 | 14:00:00 |
| 13 | app2 | user1 | 12:45:00 | 13:30:00 |
| 14 | app2 | user2 | 13:00:00 | 14:00:00 |
| 15 | app2 | user1 | 14:00:00 | 16:30:00 |
| 16 | app2 | user2 | 15:30:00 | 17:00:00 |
+----+------+-------+-----------+----------+
16 rows in set (0.00 sec)

mysql> select a.app,a.usr,a.starttime,a.endtime,b.starttime,b.endtime from session a,session b where a.app = b.app and a.usr= b.usr and (b.endtime >= a.starttime and b.starttime <=a.endtime);
+------+-------+-----------+----------+-----------+----------+
| app | usr | starttime | endtime | starttime | endtime |
+------+-------+-----------+----------+-----------+----------+
| app1 | user1 | 08:30:00 | 10:30:00 | 08:30:00 | 10:30:00 |
| app1 | user1 | 08:30:00 | 10:30:00 | 09:00:00 | 09:30:00 |
| app1 | user1 | 08:30:00 | 10:30:00 | 09:15:00 | 09:30:00 |
| app1 | user1 | 09:00:00 | 09:30:00 | 08:30:00 | 10:30:00 |
| app1 | user1 | 09:00:00 | 09:30:00 | 09:00:00 | 09:30:00 |
| app1 | user1 | 09:00:00 | 09:30:00 | 09:15:00 | 09:30:00 |
| app1 | user1 | 09:15:00 | 09:30:00 | 08:30:00 | 10:30:00 |
| app1 | user1 | 09:15:00 | 09:30:00 | 09:00:00 | 09:30:00 |
| app1 | user1 | 09:15:00 | 09:30:00 | 09:15:00 | 09:30:00 |
| app1 | user2 | 08:30:00 | 08:45:00 | 08:30:00 | 08:45:00 |
| app1 | user2 | 09:15:00 | 10:30:00 | 09:15:00 | 10:30:00 |
| app1 | user2 | 09:15:00 | 10:30:00 | 10:30:00 | 14:30:00 |
| app1 | user2 | 10:30:00 | 14:30:00 | 09:15:00 | 10:30:00 |
| app1 | user2 | 10:30:00 | 14:30:00 | 10:30:00 | 14:30:00 |
| app1 | user2 | 10:30:00 | 14:30:00 | 11:00:00 | 12:30:00 |
| app1 | user2 | 11:00:00 | 12:30:00 | 10:30:00 | 14:30:00 |
| app1 | user2 | 11:00:00 | 12:30:00 | 11:00:00 | 12:30:00 |
| app2 | user1 | 08:30:00 | 08:45:00 | 08:30:00 | 08:45:00 |
| app2 | user1 | 10:45:00 | 11:30:00 | 10:45:00 | 11:30:00 |
| app2 | user1 | 11:45:00 | 12:00:00 | 11:45:00 | 12:00:00 |
| app2 | user1 | 12:45:00 | 13:30:00 | 12:45:00 | 13:30:00 |
| app2 | user1 | 14:00:00 | 16:30:00 | 14:00:00 | 16:30:00 |
| app2 | user2 | 09:00:00 | 09:30:00 | 09:00:00 | 09:30:00 |
| app2 | user2 | 12:30:00 | 14:00:00 | 12:30:00 | 14:00:00 |
| app2 | user2 | 12:30:00 | 14:00:00 | 13:00:00 | 14:00:00 |
| app2 | user2 | 13:00:00 | 14:00:00 | 12:30:00 | 14:00:00 |
| app2 | user2 | 13:00:00 | 14:00:00 | 13:00:00 | 14:00:00 |
| app2 | user2 | 15:30:00 | 17:00:00 | 15:30:00 | 17:00:00 |
+------+-------+-----------+----------+-----------+----------+
28 rows in set (0.00 sec)

mysql> select distinct app,usr,starttime as s from session as a where not exists (select * from session as b where a.usr=b.usr and a.app=b.app and a.starttime > b.starttime and a.starttime <=b.endtime);
+------+-------+----------+
| app | usr | s |
+------+-------+----------+
| app1 | user1 | 08:30:00 |
| app1 | user2 | 08:30:00 |
| app1 | user2 | 09:15:00 |
| app2 | user1 | 08:30:00 |
| app2 | user1 | 10:45:00 |
| app2 | user1 | 11:45:00 |
| app2 | user1 | 12:45:00 |
| app2 | user1 | 14:00:00 |
| app2 | user2 | 09:00:00 |
| app2 | user2 | 12:30:00 |
| app2 | user2 | 15:30:00 |
+------+-------+----------+
11 rows in set (0.00 sec)

mysql> select distinct app,usr,endtime as e from session as a where not exists (select * from session as b where a.usr=b.usr and a.app=b.app and a.endtime >= b.starttime and a.endtime +------+-------+----------+
| app | usr | s |
+------+-------+----------+
| app1 | user1 | 10:30:00 |
| app1 | user2 | 08:45:00 |
| app1 | user2 | 14:30:00 |
| app2 | user1 | 08:45:00 |
| app2 | user1 | 11:30:00 |
| app2 | user1 | 12:00:00 |
| app2 | user1 | 13:30:00 |
| app2 | user1 | 16:30:00 |
| app2 | user2 | 09:30:00 |
| app2 | user2 | 14:00:00 |
| app2 | user2 | 17:00:00 |
+------+-------+----------+
11 rows in set (0.00 sec)

SELECT DISTINCT s.app,
                s.usr,
                s.s,
                (SELECT min(e)
                   FROM (SELECT DISTINCT app, usr, endtime AS e
                           FROM session AS a
                          WHERE NOT EXISTS
                                       (SELECT *
                                          FROM session AS b
                                         WHERE     a.usr = b.usr
                                               AND a.app = b.app
                                               AND a.endtime >= b.starttime
                                               AND a.endtime < b.endtime)) AS s2
                  WHERE s2.e > s.s AND s.app = s2.app AND s.usr = s2.usr)
                   AS e
FROM (SELECT DISTINCT app, usr, starttime AS s
          FROM session AS a
         WHERE NOT EXISTS
                      (SELECT *
                         FROM session AS b
                        WHERE     a.usr = b.usr
                              AND a.app = b.app
                              AND a.starttime > b.starttime
                              AND a.starttime <= b.endtime)) as s ,
                              (SELECT DISTINCT app, usr, endtime AS e
          FROM session AS a
         WHERE NOT EXISTS
                      (SELECT *
                         FROM session AS b
                        WHERE     a.usr = b.usr
                              AND a.app = b.app
                              AND a.endtime >= b.starttime
                              AND a.starttime < b.endtime)) as e
                              where s.app = e.app and s.usr = e.usr;

mysql> create view v_s as select distinct app,usr,starttime as s from session as a where not exists (select * from session as b where a.usr=b.usr and a.app=b.app and a.starttime > b.starttime and a.starttime <=b.endtime);
Query OK, 0 rows affected (0.01 sec)

mysql> create view v_e as select distinct app,usr,endtime as e from session as a where not exists (select * from session as b where a.usr=b.usr and a.app=b.app and a.endtime >= b.starttime and a.endtime Query OK, 0 rows affected (0.01 sec)

mysql> select distinct s.app,s.usr,s.s,
(select min(e) from v_e as s2 where s2.e>s.s and s.app = s2.app and s.usr=s2.usr) from v_s as s,v_e as e where s.app=e.app and s.usr=e.usr;

mysql> select distinct s.app,s.usr,s.s, (select min(e) from v_e as s2 where s2.e>s.s and s.app = s2.app and s.usr=s2.usr) from v_s as s,v_e as e where s.app=e.app and s.usr=e.usr;
+------+-------+----------+-----------------------------------------------------------------------------------+
| app | usr   | s        | (select min(e) from v_e as s2 where s2.e>s.s and s.app = s2.app and s.usr=s2.usr) |
+------+-------+----------+-----------------------------------------------------------------------------------+
| app1 | user1 | 08:30:00 | 10:30:00                                                                          |
| app1 | user2 | 08:30:00 | 08:45:00                                                                          |
| app1 | user2 | 09:15:00 | 14:30:00                                                                          |
| app2 | user1 | 08:30:00 | 08:45:00                                                                          |
| app2 | user1 | 10:45:00 | 11:30:00                                                                          |
| app2 | user1 | 11:45:00 | 12:00:00                                                                          |
| app2 | user1 | 12:45:00 | 13:30:00                                                                          |
| app2 | user1 | 14:00:00 | 16:30:00                                                                          |
| app2 | user2 | 09:00:00 | 09:30:00                                                                          |
| app2 | user2 | 12:30:00 | 14:00:00                                                                          |
| app2 | user2 | 15:30:00 | 17:00:00                                                                          |
+------+-------+----------+-----------------------------------------------------------------------------------+
11 rows in set (0.00 sec)

mysql> select app,s, (select count(1) from session as b where s>=starttime and s +------+----------+-------+
| app | s        | count |
+------+----------+-------+
| app1 | 08:30:00 |     3 |
| app1 | 09:00:00 |     3 |
| app1 | 09:15:00 |     5 |
| app1 | 10:30:00 |     1 |
| app1 | 11:00:00 |     3 |
| app2 | 08:30:00 |     3 |
| app2 | 09:00:00 |     3 |
| app2 | 10:45:00 |     2 |
| app2 | 11:45:00 |     3 |
| app2 | 12:30:00 |     2 |
| app2 | 12:45:00 |     3 |
| app2 | 13:00:00 |     4 |
| app2 | 14:00:00 |     2 |
| app2 | 15:30:00 |     2 |
+------+----------+-------+
14 rows in set (0.00 sec)

mysql> SELECT app, max(count) AS max
    ->   FROM (SELECT app,
    ->                s,
    ->                (SELECT count(1)
    ->                   FROM session AS b
    ->                  WHERE a.app = b.app and s >= starttime AND s < endtime)
    ->                   AS count
    ->           FROM (SELECT distinct   app,starttime as s from session) as a) as bc group by app;
+------+------+
| app | max |
+------+------+
| app1 |    4 |
| app2 |    3 |
+------+------+
2 rows in set (0.01 sec)

mysql> set @a='2015-07-15';
Query OK, 0 rows affected (0.00 sec)

mysql> select WEEKDAY(@a),dayofweek(@a),dayname(@a)\G;
*************************** 1. row ***************************
WEEKDAY(@a): 2
dayofweek(@a): 4
dayname(@a): Wednesday
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> set lc_time_names='zh_CN';
Query OK, 0 rows affected (0.00 sec)

mysql> select WEEKDAY(@a),dayofweek(@a),dayname(@a)\G;
*************************** 1. row ***************************
WEEKDAY(@a): 2
dayofweek(@a): 4
dayname(@a): 星期三
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> set @a='2015-07-15';
Query OK, 0 rows affected (0.00 sec)

mysql> select datediff(@a,'2015-07-22')%7 = 0\G
*************************** 1. row ***************************
datediff(@a,'2015-07-22')%7 = 0: 1
1 row in set (0.00 sec)

mysql> set @a='2015-07-15';
Query OK, 0 rows affected (0.00 sec)

mysql> select datediff(@a,'2015-07-22')%7 = 0\G
*************************** 1. row ***************************
datediff(@a,'2015-07-22')%7 = 0: 1
1 row in set (0.00 sec)

mysql>
mysql> select week('2011-01-01'),week('2011-01-02'),week('2011-01-03')\G
*************************** 1. row ***************************
week('2011-01-01'): 0
week('2011-01-02'): 1
week('2011-01-03'): 1
1 row in set (0.00 sec)

mysql> select week('2011-12-31'),week('2011-01-01')\G
*************************** 1. row ***************************
week('2011-12-31'): 52
week('2011-01-01'): 0
1 row in set (0.00 sec)

mysql> drop table test.sales ;
Query OK, 0 rows affected (0.06 sec)

mysql> create table test.sales (
    -> id int auto_increment not null,
    -> date datetime not null,
    -> cost int unsigned not null,
    -> primary key (id)
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> show create table test.sales;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| sales | CREATE TABLE `sales` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`date` datetime NOT NULL,
`cost` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into sales(date,cost) values('2010-12-31',100);
Query OK, 1 row affected (0.00 sec)

mysql> insert into sales(date,cost) values('2011-01-01',200);
Query OK, 1 row affected (0.00 sec)

mysql> insert into sales(date,cost) values('2011-01-01',100);
Query OK, 1 row affected (0.00 sec)

mysql> insert into sales(date,cost) values('2011-01-06',100);
Query OK, 1 row affected (0.00 sec)

mysql> insert into sales(date,cost) values('2011-01-10',100);
Query OK, 1 row affected (0.00 sec)

mysql> select week(date),sum(cost) from sales group by week(date);
+------------+-----------+
| week(date) | sum(cost) |
+------------+-----------+
|          0 |       300 |
|          1 |       100 |
|          2 |       100 |
|         52 |       100 |
+------------+-----------+
4 rows in set (0.00 sec)

mysql> insert into sales(date,cost) values('2010-12-31',100);
Query OK, 1 row affected (0.00 sec)

mysql> insert into sales(date,cost) values('2011-01-01',200);
Query OK, 1 row affected (0.00 sec)

mysql> insert into sales(date,cost) values('2011-01-01',100);
Query OK, 1 row affected (0.00 sec)

mysql> insert into sales(date,cost) values('2011-01-06',100);
Query OK, 1 row affected (0.00 sec)

mysql> insert into sales(date,cost) values('2011-01-10',100);
Query OK, 1 row affected (0.00 sec)

mysql> select week(date),sum(cost) from sales group by week(date);
+------------+-----------+
| week(date) | sum(cost) |
+------------+-----------+
|          0 |       300 |
|          1 |       100 |
|          2 |       100 |
|         52 |       100 |
+------------+-----------+
4 rows in set (0.00 sec)

mysql>
mysql> select floor(datediff(date,'1900-01-01')/7) as a,
    -> sum(cost)
    -> FROM sales
    -> group by floor(datediff(date,'1900-01-01')/7);
+------+-----------+
| a    | sum(cost) |
+------+-----------+
| 5791 |       400 |
| 5792 |       100 |
| 5793 |       100 |
+------+-----------+
3 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

SELECT
date_add('1900-01-01',interval
floor(datediff(date, '1900-01-01') / 7)*7 DAY) as week_start,
date_add('1900-01-01',interval
floor(datediff(date, '1900-01-01') / 7)*7+6 DAY) as week_end,
sum(cost)
FROM sales
GROUP BY floor(datediff(date, '1900-01-01') / 7);

+------------+------------+-----------+
| week_start | week_end   | sum(cost) |
+------------+------------+-----------+
| 2010-12-27 | 2011-01-02 |       400 |
| 2011-01-03 | 2011-01-09 |       100 |
| 2011-01-10 | 2011-01-16 |       100 |
+------------+------------+-----------+
3 rows in set (0.00 sec)

create PROCEDURE pGetWorkDays (s DATETIME,e DATETIME)
begin
select floor(days/7)*5 + days%7
- case when 6 between wd and wd+days%7-1 then 1 else 0 end
- case when 7 between wd and wd+days%7-1 then 1 else 0 end
from
(select datediff(e,s)+1 as days,weekday(s)+1 as wd) as a;
end;

mysql> call pGetWorkDays('2005-01-01','2005-12-31');
+----------------------------------------------------------------------------------------------------------------------------------------------------+
| floor(days/7)*5 + days%7
- case when 6 between wd and wd+days%7-1 then 1 else 0 end
- case when 7 between wd and wd+days%7-1 then 1 else 0 end |
+----------------------------------------------------------------------------------------------------------------------------------------------------+
| 260 |
+----------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> select weekday('2015-07-15');
+-----------------------+
| weekday('2015-07-15') |
+-----------------------+
| 2 |
+-----------------------+
1 row in set (0.00 sec)

mysql> create table t5
(a int(4) zerofill);
Query OK, 0 rows affected

mysql> show create table t5 \G;
*************************** 1. row ***************************
Table: t5
Create Table: CREATE TABLE `t5` (
`a` int(4) unsigned zerofill DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> show create table t5;
+-------+------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------+
| t5 | CREATE TABLE `t5` (
`a` int(4) unsigned zerofill DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table t5 \G
*************************** 1. row ***************************
Table: t5
Create Table: CREATE TABLE `t5` (
`a` int(4) unsigned zerofill DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> show create table t5 \G；
*************************** 1. row ***************************
Table: t5
Create Table: CREATE TABLE `t5` (
`a` int(4) unsigned zerofill DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

create table test.sales (
id int auto_increment not null,
date datetime not null,
cost int unsigned not null,
primary key (id)
);

mysql> ALTER TABLE test.sales DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show create table test.sales;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                             |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| sales | CREATE TABLE `sales` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `date` datetime NOT NULL,
   `cost` int(10) unsigned NOT NULL,
   PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show collation like 'gbk%' \G;
*************************** 1. row ***************************
Collation: gbk_chinese_ci
   Charset: gbk
        Id: 28
   Default: Yes
Compiled: Yes
   Sortlen: 1
*************************** 2. row ***************************
Collation: gbk_bin
   Charset: gbk
        Id: 87
   Default:
Compiled: Yes
   Sortlen: 1
2 rows in set (0.00 sec)

ERROR:
No query specified

mysql> status
--------------
mysql Ver 14.14 Distrib 5.6.23, for Linux (x86_64) using EditLine wrapper

Connection id: 1
Current database:
Current user: root@localhost
SSL:   Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.6.23 MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8
Db     characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/lib/mysql/mysql.sock
Uptime:   8 min 43 sec

Threads: 1 Questions: 11 Slow queries: 0 Opens: 67 Flush tables: 1 Open tables: 60 Queries per second avg: 0.021
--------------

mysql> set names gbk;
Query OK, 0 rows affected (0.00 sec)

mysql> select @a:='MySQL技术内幕';
+------------------------+
| @a:='MySQL房?内幕' |
+------------------------+
| MySQL技术内幕 |
+------------------------+
1 row in set (0.00 sec)

mysql> select @a,hex(@a),length(@a),char_length(@a) \G;
*************************** 1. row ***************************
              @a: MySQL技术内幕
        hex(@a): 4D7953514CE68A80E69CAFE58685E5B995
      length(@a): 17
char_length(@a): 12
1 row in set (0.00 sec)

ERROR:
No query specified

关于timestamp的一些问题：4字节，范围：1970-01-01 00:00:00--------2037-12-31 23:59:59
mysql> create table t3(id int,hiredate timestamp);
Query OK, 0 rows affected (0.20 sec)

mysql> insert into t3 values(1,'20150815 152700');
ERROR 1292 (22007): Incorrect datetime value: '20150815 152700' for column 'hiredate' at row 1
mysql> insert into t3 values(1,'20150815152700');
Query OK, 1 row affected (0.07 sec)

mysql> insert into t3 values(1,'2015-08-15 15:27:00');
Query OK, 1 row affected (0.06 sec)

mysql> insert into t3 values(1,'2015-08-15 15:29:00');
Query OK, 1 row affected (0.00 sec)

ITPub博客

mysql的数据类型

最新文章