This paper reviews the knowledge of database, including creating database, creating table, primary key constraint, foreign key constraint and table relation (many to many).

1, Table description
A user table, a role table, and a permission table. A user corresponds to multiple roles, a role corresponds to multiple users, and a permission can correspond to multiple roles.

The following are Sql statements:

-- Create database
CREATE DATABASE day19hometest;

-- Switch to the specified database
USE day19hometest;

-- Create user table
CREATE TABLE `user`(
    uid INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(20),
    `password` VARCHAR(20)
);
-- Create role table
CREATE TABLE role(
    rid INT PRIMARY KEY AUTO_INCREMENT,
    rname VARCHAR(20)
);

-- Create user role association table
CREATE TABLE user_role(
    u_uid INT,
    u_rid INT
);


/*
One user corresponds to multiple roles, and one role corresponds to multiple users

*/
-- Add foreign key constraint
ALTER TABLE user_role ADD CONSTRAINT fk_u_rid FOREIGN KEY(u_rid) REFERENCES `user`(uid);

ALTER TABLE user_role ADD CONSTRAINT fk_u_uid FOREIGN KEY(u_uid) REFERENCES role(rid);




-- Insert data for user table
INSERT INTO `user` VALUE 
(NULL,'It's said that long names can't be found','ccc12345'),
(NULL,'They say names are too long to be found','xxx12345'),
(NULL,'It's a long name. It's hard to find','zzz12345');
-- Insert data for role table
INSERT INTO role VALUE 
(NULL,'user'),
(NULL,'Member users'),
(NULL,'Super member users');


-- Test association table
INSERT INTO user_role VALUE
(1,3);-- Correct information

INSERT INTO user_role VALUE (1,4);-- error message

-- Add authority table
CREATE TABLE privilege(
    pid INT PRIMARY KEY AUTO_INCREMENT,
    pdescription VARCHAR(50)
);

-- Add data for permission table
INSERT INTO privilege VALUE 
(NULL,'General learning materials'),
(NULL,'Paid learning materials'),
(NULL,'Premium learning materials unique to super members');


-- Create role and permission association table
CREATE TABLE role_pri(
    r_p_rid INT,
    r_p_pid INT
);
/*
A role
*/
-- Add foreign key constraint
ALTER TABLE role_pri ADD CONSTRAINT fk_r_p_rid FOREIGN KEY (r_p_rid) REFERENCES role(rid);
ALTER TABLE role_pri ADD CONSTRAINT fk_r_p_pid FOREIGN KEY (r_p_pid) REFERENCES privilege(pid);

-- Test association table
INSERT INTO role_pri VALUE (1,3); -- Correct information
INSERT INT role_pei VALUE (1,4);-- error message

2, Architecture design drawing