· 9 years ago · Dec 05, 2016, 01:36 PM
1-- create and select the database
2DROP DATABASE IF EXISTS battlefield_One_Weapons;
3CREATE DATABASE battlefield_One_Weapons;
4USE battlefield_One_Weapons; -- MySQL command
5
6-- create the tables
7CREATE TABLE categories (
8 categoryID INT(11) NOT NULL AUTO_INCREMENT,
9 categoryName VARCHAR(255) NOT NULL,
10 description VARCHAR(1000),
11 PRIMARY KEY (categoryID)
12);
13
14CREATE TABLE weapons (
15 weaponID INT(11) NOT NULL AUTO_INCREMENT,
16 categoryID INT(11) NOT NULL,
17 weaponName VARCHAR(255) NOT NULL,
18 damage INT(11) NOT NULL,
19 accuracy INT(11) NOT NULL,
20 rof INT(11) NOT NULL,
21 ammo INT(11) NOT NULL,
22 PRIMARY KEY (weaponID),
23 FOREIGN KEY (categoryID) REFERENCES categories(categoryID)
24);
25
26
27-- insert data into the database
28INSERT INTO categories VALUES
29(1, 'Rifles', 'Rifles in Battlefield One do incredible damage at medium to close range with a very exceptable rate of fire. Beware of the reload, as it takes quite some time.'),
30(2, 'Snipers', 'Snipers are the go to weapon for long range engagements. Snipers have the best damage and accuracy out of all the classes. Snipers are one shot kill to the head. So if your getting surrounded, go for them headshots!'),
31(3, 'SMG', 'SMGs do high damage during close range engagements with a high range of fire and quick reload speeds. Dont run out into the open areas while using this weapon as you are more than likely going to die from better medium and long range weapons. keep low and in narrow areas and youll destroy the enemy.'),
32(4, 'LMG', 'LMGs are the go to weapon to fire heeps of bullets down range, suppressing your enemy. With your large magazine and medium rate of fire, youll never have to worry about reloading in tight situations.'),
33(5, 'Shotguns', 'Shotguns are close range beasts, get cuaght in a close space with an enemy shotgunner and your 99% going to lose. What is has for incredible damage, it lacks in range so keep in close quaters and youll be fine.'),
34(6, 'Pistols', 'Pistols are especially handy when you run out of ammo of your primary and you need that bullet to finish off an enemy, pistols do just that. With a decent range, pistol can do quite some damage.'),
35(7, 'Melee Weapons', 'Melee weapons are deadly in the battlefield with most being a one hit kill. Melee weapons range from high damage, slow strike rate to low damage, high strike rate so choose wisely.');
36
37INSERT INTO weapons VALUES
38(1, 1, 'CEI Rigotti', 43, 48, 300, 10),
39(2, 1, 'SELBSTLADER m1916', 54, 56, 225, 26),
40(3, 1, 'Mondragon M1908', 54, 56, 257, 10),
41(4, 1, 'M1907 SL', 37, 48, 300, 21),
42(5, 2, 'SMLE MKIII', 100, 100, 53, 10),
43(6, 2, 'Gewehr 98', 100, 100, 50, 5),
44(7, 2, 'Gewehr M.95', 75, 100, 67, 5),
45(8, 2, 'Russian 1895', 100, 100, 56, 5),
46(9, 2, 'M1903', 100, 100, 51, 5),
47(10, 3, 'MP18', 23, 25, 550, 32),
48(11, 3, 'Automatico M1918', 21, 22, 900, 25),
49(12, 3, 'Villar Perosa', 22, 30, 3000, 50),
50(13, 4, 'Lewis Gun', 24, 35, 480, 47),
51(14, 4, 'MG 15', 27, 33, 500, 100),
52(15, 4, 'Benet Mercier M1909', 27, 45, 450, 30),
53(16, 4, 'Madsen MG', 27, 28, 540, 31),
54(17, 4, 'Hellriegel 1915', 23, 26, 650, 60),
55(18, 5, 'M97 Trench', 62, 25, 138, 5),
56(19, 5, 'Model 10-A Slug', 88, 33, 78, 6),
57(20, 6, 'Colt M1911', 44, 25, 300, 8),
58(21, 6, 'Mauser C96', 28, 32, 299, 10),
59(22, 6, 'Luger P08', 20, 38, 250, 10),
60(23, 6, 'Modello 1915', 21, 35, 200, 8),
61(24, 6, 'Auto Revolver', 36, 24, 100, 6),
62(25, 7, 'Bayonet', 100, 0, 0, 0),
63(26, 7, 'Bolo Knife', 20, 0, 0, 0),
64(27, 7, 'Shovel', 50, 0, 0, 0);
65
66
67
68
69-- create the users and grant priveleges to those users
70GRANT SELECT, INSERT, DELETE, UPDATE
71ON battlefield_One_Weapons.*
72TO mgs_user@localhost
73IDENTIFIED BY 'pa55word';
74
75GRANT SELECT
76ON weapons
77TO mgs_tester@localhost
78IDENTIFIED BY 'pa55word';