MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Drop table IF EXISTS USER; 
Drop table IF EXISTS USER_seq; 
Drop table IF EXISTS PERMISSION_OWNER; 
Drop table IF EXISTS MANAGER; 
Drop table IF EXISTS MANAGER_seq; 
Drop table IF EXISTS PERMISSION; 
Drop table IF EXISTS PERMISSION_seq;

#使用者 
CREATE TABLE USER_seq ( 
MG_no INT NOT NULL AUTO_INCREMENT PRIMARY KEY );
CREATE TABLE USER ( 
US_no VARCHAR(20) NOT NULL PRIMARY KEY DEFAULT '0', 
US_email VARCHAR(20) NOT NULL, 
US_password VARCHAR(20) NOT NULL, 
US_name VARCHAR(20) NOT NULL, 
US_title VARCHAR(20) NOT NULL, 
US_line VARCHAR(20) , 
US_createtime TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, 
US_updatetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL, 
US_spec VARCHAR(45) );
CREATE TRIGGER tg_USER_insert BEFORE INSERT ON USER 
FOR EACH ROW 
BEGIN 
INSERT INTO USER_seq VALUES (NULL); 
SET NEW.US_no = CONCAT('US', LPAD(LAST_INSERT_ID(), 3, '1')); 
END
INSERT INTO USER (US_email, US_password, US_name, US_title, US_line, US_spec) 
VALUES ('bbosstw@gmail.com', 'aa4731073', '大翔', '老闆', 'siang5032', '');
SELECT *
FROM USER


US_no
US_email
US_password
US_name
US_title
US_line
US_createtime
US_updatetime
US_spec


US111
bbosstw@gmail.com
aa4731073
大翔
老闆
siang5032
2021-10-20 06:46:33
2021-10-20 06:46:33



#管理員MANAGER 
CREATE TABLE MANAGER_seq ( 
MG_no INT NOT NULL AUTO_INCREMENT PRIMARY KEY );
CREATE TABLE MANAGER (
MG_no VARCHAR(20) NOT NULL PRIMARY KEY DEFAULT '0', 
MG_email VARCHAR(20) NOT NULL, 
MG_password VARCHAR(20) NOT NULL, 
MG_name VARCHAR(20) NOT NULL, 
MG_title VARCHAR(20) NOT NULL, 
MG_spec VARCHAR(45), Line_id VARCHAR(20) , 
Line_url VARCHAR(20) , 
MG_createtime TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, 
MG_updatetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL);
CREATE TRIGGER tg_MANAGER_insert BEFORE INSERT ON MANAGER 
FOR EACH ROW 
BEGIN 
INSERT INTO MANAGER_seq VALUES (NULL); 
SET NEW.MG_no = CONCAT('MG', LPAD(LAST_INSERT_ID(), 3, '0')); 
END
INSERT INTO MANAGER (MG_email, MG_password, MG_name, MG_title, MG_spec, Line_id, Line_url) 
VALUES ('bbosstw@gmail.com', 'aa4731073', '大翔', '老闆', '', 'siang5032', ''); 
INSERT INTO MANAGER (MG_email, MG_password, MG_name, MG_title, MG_spec, Line_id, Line_url) 
VALUES ('123456@gmail.com', '123456', '小翔', '小編', '', '', '');
SELECT *
FROM MANAGER


MG_no
MG_email
MG_password
MG_name
MG_title
MG_spec
Line_id
Line_url
MG_createtime
MG_updatetime


MG001
bbosstw@gmail.com
aa4731073
大翔
老闆

siang5032

2021-10-20 06:46:33
2021-10-20 06:46:33

MG002
123456@gmail.com
123456
小翔
小編



2021-10-20 06:46:33
2021-10-20 06:46:33


#權限PERMISSION 
CREATE TABLE PERMISSION_seq ( 
PM_no INT NOT NULL AUTO_INCREMENT PRIMARY KEY );

CREATE TABLE PERMISSION ( 
PM_no VARCHAR(20) NOT NULL PRIMARY KEY DEFAULT '0', 
PM_name VARCHAR(20) NOT NULL, PM_spec VARCHAR(20) );
CREATE TRIGGER tg_PERMISSION_insert BEFORE INSERT ON PERMISSION 
FOR EACH ROW 
BEGIN 
INSERT INTO PERMISSION_seq VALUES (NULL); 
SET NEW.PM_no = CONCAT('PM', LPAD(LAST_INSERT_ID(), 3, '0')); 
END
INSERT INTO PERMISSION (PM_name, PM_spec) 
VALUES ('開發者', '我最大'); 
INSERT INTO PERMISSION (PM_name, PM_spec) 
VALUES ('編輯者', '我老二');
SELECT *
FROM PERMISSION


PM_no
PM_name
PM_spec


PM001
開發者
我最大

PM002
編輯者
我老二


#權限擁有者PERMISSION_OWNER 
CREATE TABLE PERMISSION_OWNER ( 
MG_no VARCHAR(20) NOT NULL, 
PM_no VARCHAR(20) NOT NULL, 
PRIMARY KEY (PM_NO,MG_NO), 
FOREIGN KEY (MG_no) REFERENCES MANAGER (MG_no), 
FOREIGN KEY (PM_no) REFERENCES PERMISSION (PM_no) );
INSERT INTO PERMISSION_OWNER ( MG_NO, PM_NO) 
VALUES ('MG001','PM001'); 
INSERT INTO PERMISSION_OWNER ( MG_NO, PM_NO) 
VALUES ('MG002','PM002');
SELECT *
FROM PERMISSION_OWNER


MG_no
PM_no


MG001
PM001

MG002
PM002
MG_no	MG_email	MG_password	MG_name	MG_title	MG_spec	Line_id	Line_url	MG_createtime	MG_updatetime
MG001	bbosstw@gmail.com	aa4731073	大翔	老闆		siang5032		2021-10-20 06:46:33	2021-10-20 06:46:33
MG002	123456@gmail.com	123456	小翔	小編				2021-10-20 06:46:33	2021-10-20 06:46:33