DROP TABLE IF EXISTS t1,t2,t3,t4;
CREATE TABLE t1(c1 YEAR NOT NULL,c2 YEAR, PRIMARY KEY(c1));
CREATE TABLE t2(c1 YEAR NOT NULL, c2 YEAR, UNIQUE INDEX idx(c1,c2));
CREATE TABLE t3(c1 YEAR(2) NOT NULL,c2 YEAR(2), PRIMARY KEY(c1));
Warnings:
Warning	1287	'YEAR(2)' is deprecated and will be removed in a future release. Please use 'YEAR(4)' instead
Warning	1287	'YEAR(2)' is deprecated and will be removed in a future release. Please use 'YEAR(4)' instead
CREATE TABLE t4(c1 YEAR(2), c2 YEAR(2), UNIQUE INDEX idx(c1,c2));
Warnings:
Warning	1287	'YEAR(2)' is deprecated and will be removed in a future release. Please use 'YEAR(4)' instead
Warning	1287	'YEAR(2)' is deprecated and will be removed in a future release. Please use 'YEAR(4)' instead
INSERT INTO t1 VALUES (1901,1901),(1970,1970),(1999,1999),(2000,2000),(2155,2155);
INSERT INTO t2 VALUES (1901,1901),(1970,1970),(1999,1999),(2000,2000),(2155,2155);
INSERT INTO t3 VALUES (1901,1901),(1970,1970),(1999,1999),(2000,2000),(2155,2155);
INSERT INTO t4 VALUES (1901,1901),(1970,1970),(1999,1999),(2000,2000),(2155,2155);
INSERT INTO t1 VALUES ('1902','1902'),('1971','1971'),('1998','1998'),('2001','2001'),('2154','2154');
INSERT INTO t2 VALUES ('1902','1902'),('1971','1971'),('1998','1998'),('2001','2001'),('2154','2154');
INSERT INTO t3 VALUES ('1902','1902'),('1971','1971'),('1998','1998'),('2001','2001'),('2154','2154');
INSERT INTO t4 VALUES ('1902','1902'),('1971','1971'),('1998','1998'),('2001','2001'),('2154','2154');
INSERT INTO t1 VALUES (04,04),(64,64),(69,69),(97,97);
INSERT INTO t2 VALUES (04,04),(64,64),(69,69),(97,97);
INSERT INTO t3 VALUES (04,04),(64,64),(69,69),(97,97);
INSERT INTO t4 VALUES (04,04),(64,64),(69,69),(97,97);
INSERT INTO t1 VALUES ('05','05'),('65','65'),('75','75'),('95','95');
INSERT INTO t2 VALUES ('05','05'),('65','65'),('75','75'),('95','95');
INSERT INTO t3 VALUES ('05','05'),('65','65'),('75','75'),('95','95');
INSERT INTO t4 VALUES ('05','05'),('65','65'),('75','75'),('95','95');
INSERT INTO t1 VALUES ('09',null),('61',null),('79',null),('96',null);
INSERT INTO t2 VALUES ('09',null),('61',null),('79',null),('96',null);
INSERT INTO t3 VALUES ('09',null),('61',null),('79',null),('96',null);
INSERT INTO t4 VALUES ('09',null),('61',null),('79',null),('96',null);
SELECT * FROM t1;
c1	c2
1901	1901
1902	1902
1970	1970
1971	1971
1975	1975
1979	NULL
1995	1995
1996	NULL
1997	1997
1998	1998
1999	1999
2000	2000
2001	2001
2004	2004
2005	2005
2009	NULL
2061	NULL
2064	2064
2065	2065
2069	2069
2154	2154
2155	2155
SELECT * FROM t2;
c1	c2
1901	1901
1902	1902
1970	1970
1971	1971
1975	1975
1979	NULL
1995	1995
1996	NULL
1997	1997
1998	1998
1999	1999
2000	2000
2001	2001
2004	2004
2005	2005
2009	NULL
2061	NULL
2064	2064
2065	2065
2069	2069
2154	2154
2155	2155
SELECT * FROM t3;
c1	c2
00	00
01	01
01	01
02	02
04	04
05	05
09	NULL
54	54
55	55
61	NULL
64	64
65	65
69	69
70	70
71	71
75	75
79	NULL
95	95
96	NULL
97	97
98	98
99	99
SELECT * FROM t4;
c1	c2
00	00
01	01
01	01
02	02
04	04
05	05
09	NULL
54	54
55	55
61	NULL
64	64
65	65
69	69
70	70
71	71
75	75
79	NULL
95	95
96	NULL
97	97
98	98
99	99
SELECT c1 FROM t1 WHERE c1='2005';
c1
2005
DELETE FROM t1 WHERE c1='2005';
SELECT c1 FROM t1;
c1
1901
1902
1970
1971
1975
1979
1995
1996
1997
1998
1999
2000
2001
2004
2009
2061
2064
2065
2069
2154
2155
SELECT c2 FROM t2 WHERE c2=null;
c2
DELETE FROM t2 WHERE c2=null LIMIT 2;
SELECT c2 FROM t2;
c2
1901
1902
1970
1971
1975
1995
1997
1998
1999
2000
2001
2004
2005
2064
2065
2069
2154
2155
NULL
NULL
NULL
NULL
SELECT c1 FROM t4 WHERE c1 < '55';
c1
00
01
01
02
04
05
09
DELETE FROM t4 WHERE c1 < '65' ORDER BY c1 LIMIT 5;
SELECT c1 FROM t4;
c1
05
09
54
55
61
64
65
69
70
71
75
79
95
96
97
98
99
DELETE FROM t2 WHERE c1=2009 AND c2=null;
SELECT c2 FROM t2;
c2
1901
1902
1970
1971
1975
1995
1997
1998
1999
2000
2001
2004
2005
2064
2065
2069
2154
2155
NULL
NULL
NULL
NULL
DELETE FROM t1 WHERE c1=1979 OR c1=2154;
SELECT c2 FROM t1;
c2
1901
1902
1970
1971
1975
1995
1997
1998
1999
2000
2001
2004
2064
2065
2069
2155
NULL
NULL
NULL
DELETE FROM t4 WHERE c2 IN (01,54,65,69,null) LIMIT 2;
SELECT c1 FROM t4;
c1
05
09
55
61
64
65
70
71
75
79
95
96
97
98
99
DELETE t3,t4 FROM t3,t4 WHERE t3.c1=61 AND t4.c2=4;
DELETE FROM t1 WHERE c1=2064 AND c2=2064;
SELECT * FROM t1;
c1	c2
1901	1901
1902	1902
1970	1970
1971	1971
1975	1975
1995	1995
1996	NULL
1997	1997
1998	1998
1999	1999
2000	2000
2001	2001
2004	2004
2009	NULL
2061	NULL
2065	2065
2069	2069
2155	2155
DELETE FROM t1 WHERE c1 BETWEEN 2000 AND 2010 LIMIT 2;
SELECT * FROM t1;
c1	c2
1901	1901
1902	1902
1970	1970
1971	1971
1975	1975
1995	1995
1996	NULL
1997	1997
1998	1998
1999	1999
2004	2004
2009	NULL
2061	NULL
2065	2065
2069	2069
2155	2155
DELETE FROM t1 WHERE c1 IN (2155,2009,1975) LIMIT 2;
SELECT * FROM t1;
c1	c2
1901	1901
1902	1902
1970	1970
1971	1971
1995	1995
1996	NULL
1997	1997
1998	1998
1999	1999
2004	2004
2061	NULL
2065	2065
2069	2069
2155	2155
DELETE t1,t2 FROM t1,t2 WHERE t1.c1=t2.c1 AND t1.c2=t2.c2;
SELECT * FROM t1;
c1	c2
1996	NULL
2061	NULL
SELECT * FROM t2;
c1	c2
1975	1975
1979	NULL
1996	NULL
2000	2000
2001	2001
2005	2005
2009	NULL
2061	NULL
2064	2064
2154	2154
SELECT * FROM t3;
c1	c2
00	00
01	01
01	01
02	02
04	04
05	05
09	NULL
54	54
55	55
61	NULL
64	64
65	65
69	69
70	70
71	71
75	75
79	NULL
95	95
96	NULL
97	97
98	98
99	99
SELECT * FROM t4;
c1	c2
05	05
09	NULL
55	55
61	NULL
64	64
65	65
70	70
71	71
75	75
79	NULL
95	95
96	NULL
97	97
98	98
99	99
DROP TABLE IF EXISTS t1,t2,t3,t4;