MySQL 数据库设计：QQ 用户系统

-- 创建数据库 CREATE DATABASE IF NOT EXISTS QQDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 使用数据库 USE QQDB;

-- 创建用户表QQUser CREATE TABLE IF NOT EXISTS QQUser ( uid INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, email VARCHAR(50) NOT NULL UNIQUE, 'password' VARCHAR(50) NOT NULL CHECK(CHAR_LENGTH('password')>=6), online_status TINYINT(1) NOT NULL DEFAULT 1 CHECK(online_status IN (0,1,2)), level INT UNSIGNED NOT NULL DEFAULT 0, gender TINYINT(1) DEFAULT NULL CHECK(gender IN (NULL,0,1)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 创建用户基本信息表BaseInfo CREATE TABLE IF NOT EXISTS BaseInfo ( uid INT UNSIGNED PRIMARY KEY, nickname VARCHAR(50) NOT NULL, gender TINYINT(1) DEFAULT NULL CHECK(gender IN (NULL,0,1)), age TINYINT(3) UNSIGNED NOT NULL CHECK(age BETWEEN 1 AND 100), avatar VARCHAR(100) DEFAULT NULL, FOREIGN KEY (uid) REFERENCES QQUser(uid) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 创建关系表Relation CREATE TABLE IF NOT EXISTS Relation ( rid INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uid1 INT UNSIGNED NOT NULL, uid2 INT UNSIGNED NOT NULL, relationship TINYINT(1) NOT NULL CHECK(relationship IN (0,1)), FOREIGN KEY (uid1) REFERENCES QQUser(uid) ON DELETE CASCADE, FOREIGN KEY (uid2) REFERENCES QQUser(uid) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;