日期时间数据类型表
用途：日期时间类型用于存储日期和时间。
日期和时间是一种特殊的字符串。
1. 用户注册信息表 (users)
2. 员工信息表 (employees)
3. 会议日程表 (meetings)
4. 新闻文章表 (articles)
说明：国际版
5. 考勤打卡记录表 (attendance)
6. 促销活动表 (promotions)
7. 历史档案表 (historical_records)
说明：大时间范围
8. 电影放映时间表 (movie_schedules)
练习题答案
1. 用户注册信息表 (users)
CREATE TABLE users (
user_id INT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
register_time DATETIME
);
INSERT INTO users (user_id, username, register_time) VALUES
(1, 'zhangsan', '2023-10-01 09:30:15'),
(2, 'lisi', '2023-10-01 14:25:08'),
(3, 'wangwu', '2023-10-02 08:45:33');2. 员工信息表 (employees)
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(50) NOT NULL,
birth_date DATE,
hire_date DATE
);
INSERT INTO employees (emp_id, emp_name, birth_date, hire_date) VALUES
(101, '张三', '1990-05-15', '2020-03-01'),
(102, '李四', '1985-12-20', '2018-06-15'),
(103, '王五', '1995-08-03', '2022-01-10');3. 会议日程表 (meetings)
CREATE TABLE meetings (
meeting_id INT PRIMARY KEY,
meeting_name VARCHAR(100) NOT NULL,
start_time DATETIME,
end_time DATETIME
);
INSERT INTO meetings (meeting_id, meeting_name, start_time, end_time) VALUES
(1, '项目启动会', '2023-10-05 09:00:00', '2023-10-05 11:00:00'),
(2, '技术评审会', '2023-10-06 14:30:00', '2023-10-06 16:00:00'),
(3, '月度总结会', '2023-10-10 10:00:00', '2023-10-10 12:00:00');4. 新闻文章表 (articles)
CREATE TABLE articles (
article_id INT PRIMARY KEY,
title VARCHAR(200) NOT NULL,
publish_time DATETIME,
content TEXT
);
INSERT INTO articles (article_id, title, publish_time, content) VALUES
(1001, '全球气候变化峰会', '2023-10-01 08:00:00', '气候变化相关报道...'),
(1002, '人工智能新突破', '2023-10-01 12:30:00', 'AI技术发展...'),
(1003, '国际金融市场动态', '2023-10-02 09:15:00', '金融市场分析...');5. 考勤打卡记录表 (attendance)
CREATE TABLE attendance (
record_id INT PRIMARY KEY,
emp_id INT,
check_date DATE,
check_in_time TIME,
check_out_time TIME
);
INSERT INTO attendance (record_id, emp_id, check_date, check_in_time, check_out_time) VALUES
(1, 101, '2023-10-01', '08:55:00', '17:30:00'),
(2, 102, '2023-10-01', '09:05:00', '18:15:00'),
(3, 103, '2023-10-02', '08:50:00', '17:45:00');6. 促销活动表 (promotions)
CREATE TABLE promotions (
promo_id INT PRIMARY KEY,
promo_name VARCHAR(100) NOT NULL,
valid_year YEAR,
start_date DATE,
end_date DATE
);
INSERT INTO promotions (promo_id, promo_name, valid_year, start_date, end_date) VALUES
(1, '国庆大促销', 2023, '2023-10-01', '2023-10-07'),
(2, '双十一预售', 2023, '2023-10-20', '2023-11-11'),
(3, '年终清仓', 2023, '2023-12-01', '2023-12-31');7. 历史档案表 (historical_records)
CREATE TABLE historical_records (
record_id INT PRIMARY KEY,
event_name VARCHAR(200) NOT NULL,
event_date DATE,
description TEXT
);
INSERT INTO historical_records (record_id, event_name, event_date, description) VALUES
(1, '第一次工业革命', '1760-01-01', '工业革命开始...'),
(2, '第二次世界大战结束', '1945-09-02', '二战正式结束...'),
(3, '互联网诞生', '1969-10-29', 'ARPANET首次连接...');8. 电影放映时间表 (movie_schedules)
CREATE TABLE movie_schedules (
schedule_id INT PRIMARY KEY,
movie_name VARCHAR(100) NOT NULL,
show_date DATE,
show_time TIME,
duration TIME
);
INSERT INTO movie_schedules (schedule_id, movie_name, show_date, show_time, duration) VALUES
(1, '流浪地球2', '2023-10-05', '14:30:00', '02:53:00'),
(2, '封神第一部', '2023-10-05', '19:00:00', '02:28:00'),
(3, '孤注一掷', '2023-10-06', '16:15:00', '02:10:00');