对口高考MySQL知识点精讲: 布尔+默认值

📊 表1: users (用户信息表)

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    is_active TINYINT(1) DEFAULT 1,
    login_count INT DEFAULT 0
);

INSERT INTO users (username, is_active, login_count) VALUES
('alice', 1, 5),
('bob', 1, 12),
('charlie', 0, 3);

📊 表2: products (商品信息表)

CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(6, 2) NOT NULL,
    stock INT DEFAULT 0,
    status ENUM('上架', '下架', '缺货') DEFAULT '上架',
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

INSERT INTO products (product_name, price, stock, status) VALUES
('iPhone 15', 5999.00, 50, '上架'),
('华为 Mate 60', 5499.00, 0, '缺货'),
('小米手环', 199.00, 200, '上架');

📊 表3: posts (博客文章表)

CREATE TABLE posts (
    post_id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200) NOT NULL,
    content LONGTEXT,
    author VARCHAR(50) DEFAULT '匿名',
    view_count INT DEFAULT 0,
    is_published TINYINT(1) DEFAULT 0,
    publish_date DATE DEFAULT '2000-01-01'
);

INSERT INTO posts (title, content, author, view_count, is_published, publish_date) VALUES
('C#入门教程', 'C#是一种面向对象的编程语言...', '张三', 156, 1, '2024-01-10'),
('MySQL基础', 'MySQL是最流行的关系型数据库...', DEFAULT, 23, 0, DEFAULT),
('Web开发技巧', '分享一些前端开发的小技巧...', '李四', 89, 1, '2024-01-12');

📊 表4: orders (订单表)

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_name VARCHAR(100) NOT NULL,
    order_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    total_amount DECIMAL(10, 2) DEFAULT 0.00,
    order_status VARCHAR(20) DEFAULT '待付款',
    payment_method VARCHAR(30) DEFAULT '未选择'
);

INSERT INTO orders (customer_name, total_amount, order_status, payment_method) VALUES
('张三', 5999.00, '已付款', '支付宝'),
('李四', 1298.00, DEFAULT, DEFAULT),
('王五', 0.00, '已取消', '微信支付');

📊 表5: system_config (系统配置表)

CREATE TABLE system_config (
    config_id INT PRIMARY KEY AUTO_INCREMENT,
    config_key VARCHAR(50) NOT NULL UNIQUE,
    config_value VARCHAR(255) NOT NULL,
    config_type VARCHAR(20) DEFAULT 'general',
    description VARCHAR(200) DEFAULT '无描述',
    created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

INSERT INTO system_config (config_key, config_value, config_type, description) VALUES
('site_name', '我的网站', 'general', '网站名称'),
('max_users', '1000', 'security', '最大用户数'),
('theme_color', 'blue', 'appearance', '主题颜色');

📊 表6: employees (员工信息表)

CREATE TABLE employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(50) NOT NULL,
    department VARCHAR(30) DEFAULT '未分配',
    salary DECIMAL(8, 2) DEFAULT 3000.00,
    hire_date DATE DEFAULT (CURRENT_DATE),
    probation_period INT DEFAULT 3,
    is_full_time TINYINT(1) DEFAULT 1
);

INSERT INTO employees (emp_name, department, salary, hire_date, probation_period, is_full_time) VALUES
('张三', '技术部', 15000.00, '2024-01-15', 3, 1),
('李四', DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT),
('王五', '销售部', 8000.00, '2024-01-10', 6, 0);

📊 表7: student_scores (学生成绩表)

CREATE TABLE student_scores (
    score_id INT PRIMARY KEY AUTO_INCREMENT,
    student_name VARCHAR(50) NOT NULL,
    subject VARCHAR(30) NOT NULL,
    score INT DEFAULT 0,
    exam_date DATE DEFAULT '2024-01-01',
    grade_level ENUM('一年级', '二年级', '三年级') DEFAULT '一年级',
    is_pass TINYINT(1) DEFAULT 0
);

INSERT INTO student_scores (student_name, subject, score, exam_date, grade_level, is_pass) VALUES
('小明', '数学', 85, '2024-01-15', '二年级', 1),
('小红', '语文', 92, DEFAULT, DEFAULT, 1),
('小刚', '英语', 55, DEFAULT, '三年级', 0);

📊 表8: website_stats (网站访问统计表)

CREATE TABLE website_stats (
    stat_id INT PRIMARY KEY AUTO_INCREMENT,
    page_url VARCHAR(255) NOT NULL,
    visitor_ip VARCHAR(15) DEFAULT '0.0.0.0',
    visit_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    stay_duration INT DEFAULT 0,
    browser_type VARCHAR(50) DEFAULT 'Unknown',
    is_mobile TINYINT(1) DEFAULT 0,
    country VARCHAR(50) DEFAULT '未知'
);

INSERT INTO website_stats (page_url, visitor_ip, stay_duration, browser_type, is_mobile, country) VALUES
('/home', '192.168.1.100', 120, 'Chrome', 0, '中国'),
('/products', DEFAULT, 45, 'Safari', 1, DEFAULT),
('/about', '10.0.0.25', 300, 'Firefox', 0, '美国');

📊 表9: project_tasks (项目任务表)

CREATE TABLE project_tasks (
    task_id INT PRIMARY KEY AUTO_INCREMENT,
    task_name VARCHAR(100) NOT NULL,
    project_id INT DEFAULT 0,
    assignee VARCHAR(50) DEFAULT '未分配',
    priority ENUM('低', '中', '高', '紧急') DEFAULT '中',
    status ENUM('未开始', '进行中', '已完成', '已延期') DEFAULT '未开始',
    estimated_hours DECIMAL(4, 1) DEFAULT 0.0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

INSERT INTO project_tasks (task_name, project_id, assignee, priority, status, estimated_hours) VALUES
('数据库设计', 101, '张三', '高', '进行中', 8.0),
('前端开发', 101, DEFAULT, DEFAULT, DEFAULT, DEFAULT),
('测试用例编写', 102, '李四', '紧急', '已完成', 12.5);