MySQL dataclass型 and 约束

1. 数值class型

MySQLsupport many 种数值class型, including整数class型, 浮点数class型 and 定点数class型.

1.1 整数class型

class型	字节	范围 ( has 符号)	范围 (无符号)	describes
TINYINT	1	-128 to 127	0 to 255	非常 small 整数
SMALLINT	2	-32768 to 32767	0 to 65535	small 整数
MEDIUMINT	3	-8388608 to 8388607	0 to 16777215	inetc. big small 整数
INT	4	-2147483648 to 2147483647	0 to 4294967295	标准整数
BIGINT	8	-9223372036854775808 to 9223372036854775807	0 to 18446744073709551615	big 整数

1.2 浮点数 and 定点数class型

class型	字节	范围	精度	describes
FLOAT	4	±1.175494351e-38 to ±3.402823466e+38	单精度	单精度浮点数
DOUBLE	8	±2.2250738585072014e-308 to ±1.7976931348623157e+308	双精度	双精度浮点数
DECIMAL(M,D)	可变	取决于M and D	M位精度, D位 small 数	定点数 (精确 small 数)

1.3 数值class型example

-- using整数class型
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    age TINYINT UNSIGNED,
    score SMALLINT,
    salary MEDIUMINT,
    big_number BIGINT
);

-- using浮点数 and 定点数class型
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    price FLOAT,
    weight DOUBLE,
    total DECIMAL(10, 2) -- 10位精度, 2位 small 数
);

2. stringclass型

MySQLsupport many 种stringclass型, including定 long string and 变 long string.

2.1 stringclass型

class型	最 big long 度	store	describes
CHAR(M)	0-255	固定M字节	定 long string
VARCHAR(M)	0-65535	可变 long 度	变 long string
TINYTEXT	0-255	可变 long 度	非常 small 文本
TEXT	0-65535	可变 long 度	文本
MEDIUMTEXT	0-16777215	可变 long 度	inetc. big small 文本
LONGTEXT	0-4294967295	可变 long 度	big 文本

2.2 stringclass型example

-- usingstringclass型
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE,
    password CHAR(32), -- MD5哈希值
    email VARCHAR(100) UNIQUE,
    bio TEXT,
    long_description LONGTEXT
);

-- using枚举 and collectionclass型
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    status ENUM('active', 'inactive', 'deleted'),
    tags SET('new', 'sale', 'featured')
);

3. 日期时间class型

MySQLsupport many 种日期时间class型, 用于store日期 and 时间值.

3.1 日期时间class型

class型	字节	范围	格式	describes
DATE	3	1000-01-01 to 9999-12-31	YYYY-MM-DD	日期
TIME	3	-838:59:59 to 838:59:59	HH:MM:SS	时间
DATETIME	8	1000-01-01 00:00:00 to 9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	日期 and 时间
TIMESTAMP	4	1970-01-01 00:00:01 to 2038-01-19 03:14:07	YYYY-MM-DD HH:MM:SS	时间戳 (UTC)
YEAR	1	1901 to 2155	YYYY	年份

3.2 日期时间class型example

-- using日期时间class型
CREATE TABLE events (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(100),
    event_date DATE,
    start_time TIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    event_year YEAR
);

-- 插入日期时间值
INSERT INTO events (title, event_date, start_time, event_year) VALUES
('会议', '2023-12-25', '09:00:00', 2023),
('聚会', '2023-12-31', '20:00:00', 2023);

4. 约束

MySQLsupport many 种约束, 用于确保data integrity and consistency.

4.1 主键约束 (PRIMARY KEY)

主键约束用于唯一标识表in 每一行data, 具 has 唯一性 and 非空性.

-- 单列主键
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

-- 自增主键
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100)
);

-- 复合主键
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

4.2 out 键约束 (FOREIGN KEY)

out 键约束用于建立表之间 关联relationships, 确保引用integrity.

-- creation主表
CREATE TABLE departments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL
);

-- creation from 表, 添加 out 键约束
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE CASCADE
);

--  out 键约束选项
-- ON DELETE CASCADE: delete主表记录时, 自动delete from 表相关记录
-- ON DELETE SET NULL: delete主表记录时, 将 from 表 out 键字段设置 for NULL
-- ON DELETE RESTRICT: 禁止delete主表in has  from 表引用 记录
-- ON UPDATE CASCADE: update主表主键时, 自动update from 表 out 键
-- ON UPDATE SET NULL: update主表主键时, 将 from 表 out 键字段设置 for NULL
-- ON UPDATE RESTRICT: 禁止update主表in has  from 表引用 记录

4.3 唯一性约束 (UNIQUE)

唯一性约束确保字段值 唯一性, 但允许NULL值.

-- 单列唯一性约束
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE,
    email VARCHAR(100) UNIQUE
);

-- 复合唯一性约束
CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    student_id VARCHAR(20),
    class_id INT,
    UNIQUE (student_id, class_id)
);

4.4 非空约束 (NOT NULL)

非空约束确保字段值不能 for 空.

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    password VARCHAR(100) NOT NULL,
    bio TEXT -- 允许NULL值
);

4.5 默认值约束 (DEFAULT)

默认值约束 for 字段设置默认值, 当插入data时未指定该字段值时using.

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMP DEFAULT '2000-01-01 00:00:00'
);

4.6 check约束 (CHECK)

check约束用于verification字段值 is 否满足指定条件 (MySQL 8.0+support) .

-- MySQL 8.0+ supportCHECK约束
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    age INT CHECK (age >= 18),
    salary DECIMAL(10, 2) CHECK (salary > 0),
    gender ENUM('male', 'female') CHECK (gender IN ('male', 'female'))
);

-- 插入满足条件 data
INSERT INTO users (username, age, salary, gender) VALUES ('admin', 25, 5000, 'male');

-- 插入不满足条件 data (会失败) 
-- INSERT INTO users (username, age, salary, gender) VALUES ('test', 15, -1000, 'other');

5. 完整example

-- creationexampledatalibrary
CREATE DATABASE IF NOT EXISTS school_db;
USE school_db;

-- creation学生表, package含各种dataclass型 and 约束
CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    student_id VARCHAR(20) UNIQUE NOT NULL,
    name VARCHAR(50) NOT NULL,
    gender ENUM('male', 'female') NOT NULL,
    age TINYINT UNSIGNED CHECK (age >= 0 AND age <= 100),
    grade VARCHAR(10) NOT NULL,
    score DECIMAL(5, 2) DEFAULT 0,
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(20),
    address TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- creation课程表
CREATE TABLE courses (
    id INT PRIMARY KEY AUTO_INCREMENT,
    course_code VARCHAR(20) UNIQUE NOT NULL,
    course_name VARCHAR(100) NOT NULL,
    credits TINYINT UNSIGNED NOT NULL,
    description TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- creation选课表, package含 out 键约束
CREATE TABLE enrollments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    enrollment_date DATE NOT NULL,
    grade VARCHAR(5) DEFAULT '未评定',
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
    FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE,
    UNIQUE (student_id, course_id) -- 确保一个学生不会重复选同一门课
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 插入data
INSERT INTO students (student_id, name, gender, age, grade, score, email, phone) VALUES
('S2023001', '张三', 'male', 18, ' high 一', 85.5, 'zhangsan@example.com', '13800138001'),
('S2023002', '李四', 'female', 17, ' high 一', 92.0, 'lisi@example.com', '13800138002'),
('S2023003', '王五', 'male', 18, ' high 二', 78.5, 'wangwu@example.com', '13800138003');

INSERT INTO courses (course_code, course_name, credits) VALUES
('C001', '数学', 4),
('C002', '语文', 4),
('C003', '英语', 4),
('C004', '物理', 3),
('C005', '化学', 3);

INSERT INTO enrollments (student_id, course_id, enrollment_date) VALUES
(1, 1, '2023-09-01'),
(1, 2, '2023-09-01'),
(1, 3, '2023-09-01'),
(2, 1, '2023-09-01'),
(2, 2, '2023-09-01'),
(3, 1, '2023-09-01'),
(3, 4, '2023-09-01'),
(3, 5, '2023-09-01');

-- querydata
SELECT s.id, s.student_id, s.name, s.gender, s.age, s.grade, c.course_name, e.grade AS enrollment_grade
FROM students s
JOIN enrollments e ON s.id = e.student_id
JOIN courses c ON e.course_id = c.id
ORDER BY s.id, c.id;