小数字表 DROP TABLE IF EXISTSnumbers_small;创建表numbers_small(number INT);插入数字_小值 (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);主号码表 DROP TABLE IF EXISTS 号码;创建表编号
丢弃
如果存在则表numbers_small;DROP
TABLE IF EXISTS numbers_small;<br>
CREATE TABLE numbers_small (number INT);<br>
INSERT INTO numbers_small VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);<br>
创建表numbers_small (number INT);
DROP
TABLE IF EXISTS numbers;<br>
CREATE TABLE numbers (number BIGINT);<br>
INSERT INTO numbers<br>
SELECT thousands.number * 1000 + hundreds.number * 100 + tens.number * 10 + ones.number<br>
FROM numbers_small thousands, numbers_small hundreds, numbers_small tens, numbers_small ones<br>
LIMIT 1000000;<br>
限制 1000000;DROP
TABLE IF EXISTS Dates_D;<br>
CREATE TABLE Dates_D (<br>
date_id BIGINT PRIMARY KEY,<br>
date DATE NOT NULL,<br>
day CHAR(10),<br>
day_of_week INT,<br>
day_of_month INT,<br>
day_of_year INT,<br>
previous_day date NOT NULL default '0000-00-00',<br>
next_day date NOT NULL default '0000-00-00',<br>
weekend CHAR(10) NOT NULL DEFAULT "Weekday",<br>
week_of_year CHAR(2),<br>
month CHAR(10),<br>
month_of_year CHAR(2),<br>
quarter_of_year INT,<br>
year INT,<br>
UNIQUE KEY `date` (`date`));
丢弃
如果存在则表 Dates_D;INSERT
INTO Dates_D (date_id, date)<br>
SELECT number, DATE_ADD( '2010-01-01', INTERVAL number DAY )<br>
FROM numbers<br>
WHERE DATE_ADD( '2010-01-01', INTERVAL number DAY ) BETWEEN '2010-01-01' AND '2010-12-31'<br>
ORDER BY number;<br>
创建表 Dates_D (
date_id BIGINT 主键,
previous_day 日期 NOT NULL 默认 '0000-00-00',UPDATE
Dates_D SET<br>
day = DATE_FORMAT( date, "%W" ),<br>
day_of_week = DAYOFWEEK(date),<br>
day_of_month = DATE_FORMAT( date, "%d" ),<br>
day_of_year = DATE_FORMAT( date, "%j" ),<br>
previous_day = DATE_ADD(date, INTERVAL -1 DAY),<br>
next_day = DATE_ADD(date, INTERVAL 1 DAY),<br>
weekend = IF( DATE_FORMAT( date, "%W" ) IN ('Saturday','Sunday'), 'Weekend', 'Weekday'),<br>
week_of_year = DATE_FORMAT( date, "%V" ),<br>
month = DATE_FORMAT( date, "%M"),<br>
month_of_year = DATE_FORMAT( date, "%m"),<br>
quarter_of_year = QUARTER(date),<br>
year = DATE_FORMAT( date, "%Y" );
next_day 日期 NOT NULL 默认 '0000-00-00',
更新
日期_D SET
日 = DATE_FORMAT( 日期, "%W" ),
day_of_week = DAYOFWEEK(日期),
day_of_month = DATE_FORMAT( 日期, "%d" ),
day_of_year = DATE_FORMAT( 日期, "%j" ),
previous_day = DATE_ADD(日期, 间隔 -1 天),
next_day = DATE_ADD(日期,间隔 1 天),
周末 = IF( DATE_FORMAT( date, "%W" ) IN ('星期六','星期日'), '周末', '工作日'),
week_of_year = DATE_FORMAT( 日期, "%V" ),
月 = DATE_FORMAT(日期, "%M"),
Month_of_year = DATE_FORMAT( 日期, "%m"),
年季度 = QUARTER(日期),
年 = DATE_FORMAT(日期, "%Y");