33 lines
1.1 KiB
SQL
33 lines
1.1 KiB
SQL
-- Create the UserText table to store user information
|
|
CREATE TABLE IF NOT EXISTS UserText (
|
|
account VARCHAR(20) PRIMARY KEY,
|
|
nickname VARCHAR(50) NOT NULL,
|
|
email VARCHAR(100),
|
|
phone VARCHAR(20),
|
|
photo VARCHAR(255) DEFAULT NULL,
|
|
category VARCHAR(20) NOT NULL,
|
|
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Create the UserPassword table to store user account and password
|
|
CREATE TABLE IF NOT EXISTS UserPassword (
|
|
account VARCHAR(20) PRIMARY KEY,
|
|
password VARCHAR(50) NOT NULL,
|
|
FOREIGN KEY (account) REFERENCES UserText(account)
|
|
);
|
|
|
|
-- Insert sample users as specified in the requirements
|
|
-- Student users
|
|
INSERT INTO UserText (account, nickname, email, phone, category) VALUES
|
|
('2', '张三', 'student@qq.com', '17267383831', 'student'),
|
|
('9222', '李华', 'student123@qq.com', '12345678901', 'student');
|
|
|
|
-- Teacher user
|
|
INSERT INTO UserText (account, nickname, email, phone, category) VALUES
|
|
('0', '教师demo', 'teach@qq.com', NULL, 'teacher');
|
|
|
|
-- Insert passwords (all passwords are '1')
|
|
INSERT INTO UserPassword (account, password) VALUES
|
|
('2', '1'),
|
|
('9222', '1'),
|
|
('0', '1'); |