sql/script.sql at master · zhuyeqi/sql · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
create table t_element
(
    id    int auto_increment
        primary key,
    eleno varchar(32) not null
);

create table t_menu
(
    id   int auto_increment
        primary key,
    name varchar(20) not null,
    pid  int         not null
);

create table t_role
(
    id   int auto_increment
        primary key,
    name varchar(20) not null
);

create table t_role_element
(
    id  int auto_increment
        primary key,
    rid int not null,
    eid int not null,
    constraint t_role_element_t_element_id_fk
        foreign key (eid) references t_element (id),
    constraint t_role_element_t_role_id_fk
        foreign key (rid) references t_role (id)
);

create table t_role_menu
(
    id  int auto_increment
        primary key,
    rid int not null,
    mid int not null,
    constraint t_role_menu_t_menu_id_fk
        foreign key (mid) references t_menu (id),
    constraint t_role_menu_t_role_id_fk
        foreign key (rid) references t_role (id)
);

create table t_user
(
    id       int auto_increment
        primary key,
    username varchar(32) not null,
    password varchar(32) not null
);

create table t_user_role
(
    id  int auto_increment
        primary key,
    uid int not null,
    rid int not null,
    constraint t_user_role_t_role_id_fk
        foreign key (rid) references t_role (id),
    constraint t_user_role_t_user_id_fk
        foreign key (uid) references t_user (id)
);