DATABASE/mySQL at main · muffinhead03/DATABASE · 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
DROP DATABASE IF EXISTS DB2025Team09;

DROP USER IF EXISTS DB2025Team09@localhost;

CREATE USER DB2025Team09@localhost identified by 'DB2025Team09';

CREATE DATABASE DB2025Team09;

GRANT ALL PRIVILEGES ON DB2025Team09.* TO 'DB202509'@'localhost' WITH GRANT OPTION;

commit;

USE DB2025Team09;

CREATE TABLE DB2025_Team(
idTeam INTEGER PRIMARY KEY,
nation varchar(30) NOT NULL,
FIFArank INTEGER NOT NULL,
currName varchar(50),
currRank INTEGER,
currPoints INTEGER
);

CREATE TABLE DB2025_Player(
idPlayer INTEGER PRIMARY KEY,
playerName varchar(20),
position varchar(10),
birthday date NOT NULL,
idTeam INTEGER,
ableToPlay TINYINT,
performance INTEGER,
playerAction varchar(20),
FOREIGN KEY(idTeam) REFERENCES DB2025_Team(idTeam) ON DELETE SET NULL
);

CREATE TABLE DB2025_Tactics(
idTactic INTEGER PRIMARY KEY,
idTeam INTEGER NOT NULL,
tacticType varchar(10) NOT NULL,
tacticName  varchar(50) NOT NULL,
tacticFormation varchar(50) NOT NULL,
explainTactics varchar(100) NOT NULL,
ableToTactic TINYINT NOT NULL
);

CREATE TABLE DB2025_GameRec(
idGame INTEGER PRIMARY KEY,
dateGame DATE NOT NULL,
idOurTeam INTEGER NOT NULL,
idAgainstTeam INTEGER NOT NULL,
idField INTEGER,
idSetpiece INTEGER,
goalFor INTEGER,
goalAgainst INTEGER,
FOREIGN KEY(idField) REFERENCES DB2025_Tactics(idTactic) ON DELETE SET NULL,
FOREIGN KEY(idSetpiece) REFERENCES DB2025_Tactics(idTactic) ON DELETE SET NULL,
FOREIGN KEY(idOurTeam) REFERENCES DB2025_Team(idTeam) ON DELETE CASCADE,
FOREIGN KEY(idAgainstTeam) REFERENCES DB2025_Team(idTeam) ON DELETE CASCADE
);

CREATE TABLE DB2025_GameStat(
idGame INTEGER PRIMARY KEY,
allShots INTEGER,
shotOnTarget INTEGER,
accPass INTEGER,
attackPass INTEGER,
intercept INTEGER,
blocking INTEGER,
FOREIGN KEY(idGame) REFERENCES DB2025_GameRec(idGame) ON DELETE NO ACTION
);

CREATE TABLE DB2025_Squad(
idGame INTEGER,
idPlayer INTEGER,
playTime INTEGER,

PRIMARY KEY(idGame, idPlayer),
FOREIGN KEY(idGame) REFERENCES DB2025_GameRec(idGame) ON DELETE CASCADE,
FOREIGN KEY(idPlayer) REFERENCES DB2025_Player(idPlayer) ON DELETE CASCADE
);


INSERT INTO DB2025_Team
VALUES
(1, '대한민국', 28, '2022 카타르 월드컵', 2, 1),
(2, '가나', 61, '2022 카타르 월드컵', 3, 0),
(3, '포르투갈', 9, '2022 카타르 월드컵', 1, 3);

INSERT INTO DB2025_Player (idPlayer, playerName, position, birthday, idTeam, ableToPlay, playerAction, performance)
VALUES
(1, '김승규', 'GK', '1990-09-30', 1, 1, '패스', 96),
(3, '김진수', 'DF', '1992-06-13', 1, 1, '크로스', 91),
(4, '김민재', 'DF', '1996-11-15', 1, 1, '태클', 102),
(5, '정우영', 'DM', '1989-12-14', 1, 1, '공중볼 경합 성공', 98),
(6, '황인범', 'DM', '1996-09-20', 1, 1, '패스', 101),
(7, '손흥민', 'AM', '1992-09-07', 1, 1, '슛', 100),
(9, '조규성', 'FW', '1998-01-25', 1, 1, '슛', 99),
(11, '황희찬', 'AM', '1996-01-26', 1, 1, '슛', 99),
(10, '이재성', 'AM', '1992-08-10', 1, 1, '패스', 100),
(13, '손준호', 'DM', '1992-05-12', 1, 1, '패스', 93),
(15, '김문환', 'DF', '1995-08-01', 1, 1, '크로스', 97),
(16, '황의조', 'FW', '1992-08-28', 1, 1, '슛', 95),
(17, '나상호', 'AM', '1996-08-12', 1, 1, '슛', 91),
(18, '이강인', 'AM', '2001-02-19', 1, 1, '드리블', 98),
(19, '김영권', 'DF', '1990-02-27', 1, 1, '차단', 100),
(20, '권경원', 'DF', '1992-01-31', 1, 1, '차단', 93),
(22, '권창훈', 'AM', '1994-06-30', 1, 1, '슛', 93),
(24, '조유민', 'DF', '1996-11-17', 1, 1, '공중볼 경합 성공', 88),
(25, '정우영', 'AM', '1999-09-20', 1, 1, '슛', 91);

INSERT INTO DB2025_Tactics
VALUES
(1, 1, 'Field', '빌드업', '4-2-3-1', '안정적과 창의공격, 수비 안정', 1),
(2, 1, 'Field', '점유율과 밸런스', '4-3-3', '빠른 측면 공격과 압박', 1),
(3, 1, 'Field', '공격형', '3-4-3', '윙백의 적극적인 활용', 0),
(4, 1, 'Field', '수비형', '4-4-2', '조직적 블록', 0),
(5, 1, 'Setpiece', '인스윙 슈터', 'Near Post Overload', '전방 근처 포스트에 공격수 2~3명 배치하여 빠르게 슈팅 연결', 1),
(6, 1, 'Setpiece', '아웃스윙 타깃킥', 'Central Target Box', '중앙 타깃 구역에 장신 공격수 배치, 크로스 연계', 1),
(7, 1, 'Setpiece', '숏코너 리사이클', 'Short Corner Pair', '짧은 패스 이후 공격수 2~3명 조합 플레이로 기회 창출', 0),
(8, 1, 'Setpiece', '백포스트 침투', 'Far Post Run-in', '뒷포스트로 뛰어드는 선수를 위한 크로스 전개', 1);

INSERT INTO DB2025_GameRec
VALUES
(1, '2022-11-28', 1, 2, 1, 5, 2, 3),
(2, '2022-12-06', 1, 3, 1, 6, 2, 1);

INSERT INTO DB2025_GameStat
VALUES
(1, 22, 7, 455, 142, 10, 19),
(2, 13, 6, 281, 59, 14, 23);

INSERT INTO DB2025_Squad
VALUES
(1, 1, 90),(1, 15, 90),(1, 4, 89),(1, 19, 90),(1, 3, 90),(1, 5, 78),
(1, 6, 90),(1, 22, 57),(1, 25, 46),(1, 7, 90),(1, 9, 90),(1, 17, 45),
(1, 18, 33),(1, 16, 11),(1, 20, 1),(2, 1, 90),(2, 15, 90),(2, 20, 90),
(2, 19, 81),(2, 3, 90),(2, 6, 90),(2, 5, 90),(2, 18, 81),(2, 10, 65),
(2, 7, 90),(2, 9, 90),(2, 11, 24),(2, 13, 9),(2, 16, 9),(2, 24, 1);