-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathmysql_tables.txt
119 lines (94 loc) · 3.69 KB
/
mysql_tables.txt
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
CREATE TABLE `games` (
`game_id` int(11) NOT NULL AUTO_INCREMENT,
`mode` varchar(64) NULL,
`winner` varchar(32) NOT NULL,
`duration` int(11) NOT NULL,
`upload_time` varchar(32) NOT NULL,
`hash` varchar(32) NULL,
`ranked` int(11) NOT NULL,
`completion` varchar(32) NOT NULL,
`withkda` tinyint(1) NOT NULL,
`withcs` tinyint(1) NOT NULL,
`team1_elo` double NOT NULL,
`team2_elo` double NOT NULL,
`team1_elo_change` double NOT NULL,
`elo_alg` varchar(8) NOT NULL,
PRIMARY KEY (`game_id`),
UNIQUE KEY `hash` (`hash`)
);
CREATE TABLE `player_game` (
`player_id` int(11) NOT NULL,
`game_id` int(11) NOT NULL,
`slot_nr` int(11) NOT NULL,
`elo_before` double NOT NULL,
`kills` int(11) NULL,
`deaths` int(11) NULL,
`assists` int(11) NULL,
`cskills` int(11) NULL,
`csdenies` int(11) NULL,
`wards` int(11) NULL,
`hero_damage` int(11) NULL,
`tower_damage` int(11) NULL,
`item1` varchar(20) NULL,
`item2` varchar(20) NULL,
`item3` varchar(20) NULL,
`item4` varchar(20) NULL,
`item5` varchar(20) NULL,
`item6` varchar(20) NULL,
`hero` varchar(20) NULL,
PRIMARY KEY (`player_id`, `game_id`)
);
CREATE TABLE `player` (
`player_id` int(11) NOT NULL AUTO_INCREMENT,
`rank` int(11) DEFAULT NULL,
`bnet_tag` varchar(64) DEFAULT NULL,
`bnet_tag2` varchar(64) DEFAULT NULL,
`discord_id` bigint(20) DEFAULT NULL,
`name` varchar(64) DEFAULT NULL,
`elo` double DEFAULT 1000,
`games` int(11) DEFAULT 0,
`wins` int(11) DEFAULT 0,
`loss` int(11) DEFAULT 0,
`draw` int(11) DEFAULT 0,
`kills` int(11) DEFAULT 0,
`deaths` int(11) DEFAULT 0,
`assists` int(11) DEFAULT 0,
`cskills` int(11) DEFAULT 0,
`csdenies` int(11) DEFAULT 0,
`wards` int(11) DEFAULT 0,
`hero_damage` int(11) DEFAULT 0,
`tower_damage` int(11) DEFAULT 0,
`kdagames` int(11) DEFAULT 0,
`csgames` int(11) DEFAULT 0,
`avgkills` double DEFAULT NULL,
`avgdeaths` double DEFAULT NULL,
`avgassists` double DEFAULT NULL,
`avgcskills` double DEFAULT NULL,
`avgcsdenies` double DEFAULT NULL,
`avgwards` double DEFAULT NULL,
`avghero_damage` double DEFAULT NULL,
`avgtower_damage` double DEFAULT NULL,
PRIMARY KEY (`player_id`),
UNIQUE KEY `bnet_tag` (`bnet_tag`),
UNIQUE KEY `bnet_tag2` (`bnet_tag2`),
UNIQUE KEY `discord_id` (`discord_id`),
UNIQUE KEY `name` (`name`)
);
alter table player add column bnet_tag2 VARCHAR(64) unique DEFAULT NULL AFTER bnet_tag;
alter table player add column hero_damage int(11) DEFAULT 0 AFTER wards;
alter table player add column tower_damage int(11) DEFAULT 0 AFTER hero_damage;
alter table player add column avghero_damage double DEFAULT NULL AFTER avgwards;
alter table player add column avgtower_damage double DEFAULT NULL AFTER avghero_damage;
alter table player_game add column hero_damage int(11) DEFAULT NULL AFTER wards;
alter table player_game add column tower_damage int(11) DEFAULT NULL AFTER hero_damage;
SELECT * FROM player WHERE bnet_tag = "trees#11163" OR bnet_tag2 = "trees#123";
select elo_before from player_game where player_id = 1 union select elo from player where player_id = 1;
select elo from player where player_id = 1;
select elo_before as elo from player_game where player_id = 1 union select elo from player where player_id = 1;
ricefire#1366
select pg.elo_before from player_game pg, player p where pg.player_id = p.player_id and p.bnet_tag="ricefire#1366";
To work with korean characters:
https://stackoverflow.com/questions/1008287/illegal-mix-of-collations-mysql-error
SET collation_connection = 'utf8_general_ci';
ALTER DATABASE lodstats CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE player CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;