SQL-design/sqlset.php at master · mimaoxiao/SQL-design · 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
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
<?php
$con=mysqli_connect("localhost","root","nekopara","ezgo");
if (mysqli_connect_errno($con))
{
    echo "连接 MySQL 失败: " . mysqli_connect_error();
}

// 执行查询
$sql = "CREATE TABLE user
(
ID int not null AUTO_INCREMENT,
username varchar(36) not null,
password varchar(36) not null,
jobs int not null,
avaliable int not null,
primary key(ID)
)";
if(!mysqli_query($con,$sql))
{
    echo "create user boom";
}
else
{
    echo "user success";
    $sql = "insert into user
    (username,password,jobs,avaliable)
    values
    ('mimaoxiao','5209b685529d6e0731d9f9728c085d4e',1,1)";
    mysqli_query($con,$sql);/*测试帐号mimaoxiao 密码mimao 最高权限*/

    $sql = "insert into user
    (username,password,jobs,avaliable)
    values
    ('Lighting','61d970998e9e02f78156c11e9f12211c',2,1)";
    mysqli_query($con,$sql);/*测试帐号Lighting 密码fbkmio 二等权限*/
}


$sql = "CREATE TABLE donor
(
ID int not null AUTO_INCREMENT,
type int not null,
name varchar(15) not null,
email varchar(30),
phone varchar(20) not null,
remark varchar(50),
avaliable int not null,
primary key(ID)
)";
if(!mysqli_query($con,$sql))
{
    echo "create donor boom";
}
else
{
    echo "donor success";
}

$sql = "CREATE TABLE donee
(
ID int not null AUTO_INCREMENT,
type int not null,
name varchar(15) not null,
email varchar(30),
phone varchar(20) not null,
remark varchar(50),
avaliable int not null,
primary key(ID)
)";
if(!mysqli_query($con,$sql))
{
    echo "create donee boom";
}
else
{
    echo "donee success";
}
$sql = "CREATE TABLE computer
(
ID int not null AUTO_INCREMENT,
type varchar(20) not null,
status int not null,
donor_ID int not null,
owner_ID int,
photo_url varchar(250),
remark varchar(50),
avaliable int not null,
primary key(ID),
foreign key(donor_ID) references donor(ID)
)";
if(!mysqli_query($con,$sql))
{
    echo "create computer boom";
}
else
{
    echo "computer success";
}

$sql = "CREATE TABLE item
(
ID int not null AUTO_INCREMENT,
message varchar(200) not null,
status int not null,
donor_ID int not null,
owner_ID int  ,
photo_url varchar(250),
avaliable int not null,
primary key(ID),
foreign key(donor_ID) references donor(ID)
)";
if(!mysqli_query($con,$sql))
{
    echo "create item boom";
}
else
{
    echo "computer success";
}


$sql = "CREATE TABLE keeper
(
ID int not null AUTO_INCREMENT,
user_ID int not null,
name varchar(15) not null,
email varchar(30),
phone varchar(20) not null,
remark varchar(50),
avaliable int not null,
primary key(ID),
foreign key(user_ID) references user(ID) ON DELETE CASCADE ON UPDATE CASCADE
)";
if(!mysqli_query($con,$sql))
{
    echo "create keeper boom";
}
else
{
    echo "keeper success";
}

mysqli_close($con);

?>