CREATE DATABASE test;
use test;
-- 创建扩展版学生表
CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    gender ENUM('男','女'),
    age TINYINT UNSIGNED,
    height DECIMAL(3,2),
    class VARCHAR(20),
    major VARCHAR(50),
    enrollment_date DATE,
    contact VARCHAR(20),
    email VARCHAR(50)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 插入测试数据
INSERT INTO students 
(name, gender, age, height, class, major, enrollment_date, contact, email)
VALUES
('李雷', '男', 18, 1.75, '计算机2023级1班', '计算机科学', '2023-09-01', '13800138000', 'lilei@university.edu'),
('韩梅梅', '女', 19, 1.65, '外语2023级2班', '英语文学', '2023-09-01', '13912345678', 'hanmeimei@college.edu'),
('王云', '男', NULL, 1.78, '机械2022级3班', '机械工程', '2022-09-01', NULL, 'wangyun@tech.edu'),
('张伟', '男', 28, 1.82, '物理2020级1班', '理论物理', '2020-09-01', '010-66778899', 'zhangwei@physics.edu'),
('赵强', '男', 27, 1.81, '数学2021级2班', '应用数学', '2021-09-01', '13698765432', 'zhaoqiang@math.edu'),
('唐飞', '男', 22, 1.85, '软件2022级3班', '软件工程', '2022-09-01', '17722334455', 'tangfei@cs.edu'),
('李菲', '女', 20, 1.68, '设计2023级1班', '视觉设计', '2023-09-01', '18844556677', 'lifei@art.edu'),
('李渊', '男', 25, 1.80, '历史2021级2班', '历史学', '2021-09-01', '15611223344', 'liyuan@humanity.edu');


-- 1. 查询年龄17-23岁的学生信息
SELECT * FROM students WHERE age BETWEEN 17 AND 23;