xTibia

.com
CA
action

[SQL] Tutorial Básico

Por Caronte, 30 de jan. de 2016 em Tutoriais de Scripting

5
8.7k
CaronteC
31 de janeiro de 2016 às 11:34

sql-file-black-rounded-rectangular-inter

Olá pessoal,

hoje tenho um tempo livre para dedicar, e decidi criar um tutorial de SQL, nele vou explicar algumas coisas básicas, e algumas que pesquisei para ter certeza do que estou passando, no final do tutorial vou fornecer fontes, que podem ser usadas como forma de estudo.

 

Antes de começar, vou utilizar algumas formatações para facilitar:

 

 

Importante!

 

Não é necessário.

 

Dica
Texto Copiado.
Comentário/Curiosidade/Codigo

 

 

 

 

Esse tutorial pode ser usado para MySQL também, pois o MySQL como o próprio nome já dá enfase, usa uma interface em SQL - que é uma linguagem, não confunda SQL com SQLite Studio ou MySQL.

 

 

Primeiramente tenho que explicar o que é:

SQL - (Structured Query Language) Linguagem de Consulta Estruturada, é uma linguagem de banco de dados, ou seja, serve para guardar, consultar e editar quase quaisquer dados.

AJ9YJY8.png

No seu arquivo .sql que fica na sua pasta \schemas, contém uma database salva através de vários QUERYS, como eu disse antes, querys são parte da interface do SQL, e com eles, você pode jogar num MySQL que abrirá magicamente, ou simplesmente executará os querys preenchendo sua database.

 

 

 

Exemplo de arquivo .SQL:

 

 

CREATE TABLE "server_config" (
	"config" VARCHAR(35) NOT NULL DEFAULT '',
	"value" VARCHAR(255) NOT NULL DEFAULT '',
	UNIQUE ("config")
);

INSERT INTO "server_config" VALUES ('db_version', 26);

CREATE TABLE "server_motd" (
	"id" INTEGER NOT NULL,
	"world_id" INTEGER NOT NULL DEFAULT 0,
	"text" TEXT NOT NULL DEFAULT '',
	UNIQUE ("id", "world_id")
);

INSERT INTO "server_motd" VALUES (1, 0, 'Welcome to The Forgotten Server!');

CREATE TABLE "server_record" (
	"record" INTEGER NOT NULL,
	"world_id" INTEGER NOT NULL DEFAULT 0,
	"timestamp" INTEGER NOT NULL,
	UNIQUE ("record", "world_id", "timestamp")
);

INSERT INTO "server_record" VALUES (0, 0, 0);

CREATE TABLE "server_reports" (
	"id" INTEGER PRIMARY KEY,
	"world_id" INTEGER NOT NULL DEFAULT 0,
	"player_id" INTEGER NOT NULL DEFAULT 0,
	"posx" INTEGER NOT NULL DEFAULT 0,
	"posy" INTEGER NOT NULL DEFAULT 0,
	"posz" INTEGER NOT NULL DEFAULT 0,
	"timestamp" INTEGER NOT NULL DEFAULT 0,
	"report" TEXT NOT NULL DEFAULT '',
	"reads" INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE "accounts" (
	"id" INTEGER PRIMARY KEY NOT NULL,
	"name" VARCHAR(255) NOT NULL,
	"password" VARCHAR(255) NOT NULL,
	"salt" VARCHAR(40) NOT NULL DEFAULT '',
	"premdays" INTEGER NOT NULL DEFAULT 0,
	"lastday" INTEGER NOT NULL DEFAULT 0,
	"email" VARCHAR(255) NOT NULL DEFAULT '',
	"key" VARCHAR(32) NOT NULL DEFAULT '0',
	"blocked" BOOLEAN NOT NULL DEFAULT 0,
	"warnings" INTEGER NOT NULL DEFAULT 0,
	"group_id" INTEGER NOT NULL DEFAULT 1,
	UNIQUE ("name")
);

INSERT INTO "accounts" VALUES (1, '1', '1', '', 65535, 0, '', '0', 0, 0, 1);

CREATE TABLE "players" (
	"id" INTEGER PRIMARY KEY NOT NULL,
	"name" VARCHAR(255) NOT NULL,
	"world_id" INTEGER NOT NULL DEFAULT 0,
	"group_id" INTEGER NOT NULL,
	"account_id" INTEGER NOT NULL,
	"level" INTEGER NOT NULL DEFAULT 1,
	"vocation" INTEGER NOT NULL DEFAULT 0,
	"health" INTEGER NOT NULL DEFAULT 100,
	"healthmax" INTEGER NOT NULL DEFAULT 100,
	"experience" INTEGER NOT NULL DEFAULT 0,
	"lookbody" INTEGER NOT NULL DEFAULT 10,
	"lookfeet" INTEGER NOT NULL DEFAULT 10,
	"lookhead" INTEGER NOT NULL DEFAULT 10,
	"looklegs" INTEGER NOT NULL DEFAULT 10,
	"looktype" INTEGER NOT NULL DEFAULT 136,
	"lookaddons" INTEGER NOT NULL DEFAULT 0,
	"maglevel" INTEGER NOT NULL DEFAULT 0,
	"mana" INTEGER NOT NULL DEFAULT 100,
	"manamax" INTEGER NOT NULL DEFAULT 100,
	"manaspent" INTEGER NOT NULL DEFAULT 0,
	"soul" INTEGER NOT NULL DEFAULT 0,
	"town_id" INTEGER NOT NULL,
	"posx" INTEGER NOT NULL DEFAULT 0,
	"posy" INTEGER NOT NULL DEFAULT 0,
	"posz" INTEGER NOT NULL DEFAULT 0,
	"conditions" BLOB NOT NULL,
	"cap" INTEGER NOT NULL DEFAULT 0,
	"sex" INTEGER NOT NULL DEFAULT 0,
	"lastlogin" INTEGER NOT NULL DEFAULT 0,
	"lastip" INTEGER NOT NULL DEFAULT 0,
	"save" BOOLEAN NOT NULL DEFAULT 1,
	"skull" INTEGER NOT NULL DEFAULT 0,
	"skulltime" INTEGER NOT NULL DEFAULT 0,
	"rank_id" INTEGER NOT NULL,
	"guildnick" VARCHAR(255) NOT NULL DEFAULT '',
	"lastlogout" INTEGER NOT NULL DEFAULT 0,
	"blessings" INTEGER NOT NULL DEFAULT 0,
	"balance" INTEGER NOT NULL DEFAULT 0,
	"stamina" INTEGER NOT NULL DEFAULT 151200000,
	"direction" INTEGER NOT NULL DEFAULT 2,
	"loss_experience" INTEGER NOT NULL DEFAULT 100,
	"loss_mana" INTEGER NOT NULL DEFAULT 100,
	"loss_skills" INTEGER NOT NULL DEFAULT 100,
	"loss_containers" INTEGER NOT NULL DEFAULT 100,
	"loss_items" INTEGER NOT NULL DEFAULT 100,
	"premend" INTEGER NOT NULL DEFAULT 0,
	"online" TINYINT NOT NULL DEFAULT 0,
	"marriage" INTEGER NOT NULL DEFAULT 0,
	"promotion" INTEGER NOT NULL DEFAULT 0,
	"deleted" INTEGER NOT NULL DEFAULT 0,
	"description" VARCHAR(255) NOT NULL DEFAULT '',
	UNIQUE ("name", "deleted"),
	FOREIGN KEY ("account_id") REFERENCES "accounts" ("id")
);

INSERT INTO "players" VALUES (1, 'Account Manager', 0, 1, 1, 1, 0, 150, 150, 0, 0, 0, 0, 0, 110, 0, 0, 0, 0, 0, 0, 0, 50, 50, 7, '', 400, 0, 0, 0, 0, 0, 0, 0, '', 0, 0, 0, 201660000, 0, 100, 100, 100, 100, 100, 0, 0, 0, 0, 0, '');

CREATE TABLE "account_viplist" (
	"account_id" INTEGER NOT NULL,
	"world_id" INTEGER NOT NULL DEFAULT 0,
	"player_id" INTEGER NOT NULL,
	UNIQUE ("account_id", "player_id"),
	FOREIGN KEY ("account_id") REFERENCES "accounts" ("id"),
	FOREIGN KEY ("player_id") REFERENCES "players" ("id")
);

CREATE TABLE "global_storage" (
	"key" VARCHAR(32) NOT NULL,
	"world_id" INTEGER NOT NULL DEFAULT 0,
	"value" VARCHAR(255) NOT NULL DEFAULT '0',
	UNIQUE ("key", "world_id")
);

CREATE TABLE "guilds" (
	"id" INTEGER PRIMARY KEY,
	"world_id" INTEGER NOT NULL DEFAULT 0,
	"name" VARCHAR(255) NOT NULL,
	"ownerid" INTEGER NOT NULL,
	"creationdata" INTEGER NOT NULL,
	"checkdata" INTEGER NOT NULL,
	"motd" VARCHAR(255) NOT NULL DEFAULT '',
	UNIQUE ("name", "world_id"),
	FOREIGN KEY ("ownerid") REFERENCES "players" ("id")
);

CREATE TABLE "guild_invites" (
	"player_id" INTEGER NOT NULL,
	"guild_id" INTEGER NOT NULL,
	UNIQUE ("player_id", "guild_id"),
	FOREIGN KEY ("player_id") REFERENCES "players" ("id"),
	FOREIGN KEY ("guild_id") REFERENCES "guilds" ("id")
);

CREATE TABLE "guild_ranks" (
	"id" INTEGER PRIMARY KEY,
	"guild_id" INTEGER NOT NULL,
	"name" VARCHAR(255) NOT NULL,
	"level" INTEGER NOT NULL,
	FOREIGN KEY ("guild_id") REFERENCES "guilds" ("id")
);

CREATE TABLE "houses" (
	"id" INTEGER NOT NULL,
	"world_id" INTEGER NOT NULL DEFAULT 0,
	"owner" INTEGER NOT NULL,
	"paid" INTEGER NOT NULL DEFAULT 0,
	"warnings" INTEGER NOT NULL DEFAULT 0,
	"lastwarning" INTEGER NOT NULL DEFAULT 0,
	"name" VARCHAR(255) NOT NULL,
	"town" INTEGER NOT NULL DEFAULT 0,
	"size" INTEGER NOT NULL DEFAULT 0,
	"price" INTEGER NOT NULL DEFAULT 0,
	"rent" INTEGER NOT NULL DEFAULT 0,
	"doors" INTEGER NOT NULL DEFAULT 0,
	"beds" INTEGER NOT NULL DEFAULT 0,
	"tiles" INTEGER NOT NULL DEFAULT 0,
	"guild" BOOLEAN NOT NULL DEFAULT FALSE,
	"clear" BOOLEAN NOT NULL DEFAULT FALSE,
	UNIQUE ("id", "world_id")
);

CREATE TABLE "house_lists" (
	"house_id" INTEGER NOT NULL,
	"world_id" INTEGER NOT NULL DEFAULT 0,
	"listid" INTEGER NOT NULL,
	"list" TEXT NOT NULL,
	UNIQUE ("house_id", "world_id", "listid"),
	FOREIGN KEY ("house_id", "world_id") REFERENCES "houses" ("id", "world_id")
);

CREATE TABLE "house_data" (
	"house_id" INTEGER NOT NULL,
	"world_id" INTEGER NOT NULL DEFAULT 0,
	"data" LONGBLOB NOT NULL,
	UNIQUE ("house_id", "world_id"),
	FOREIGN KEY ("house_id", "world_id") REFERENCES "houses" ("id", "world_id")
);

CREATE TABLE "house_auctions" (
	"house_id" INTEGER NOT NULL,
	"world_id" INTEGER NOT NULL DEFAULT 0,
	"player_id" INTEGER NOT NULL,
	"bid" INTEGER NOT NULL DEFAULT 0,
	"limit" INTEGER NOT NULL DEFAULT 0,
	"endtime" INTEGER NOT NULL DEFAULT 0,
	UNIQUE ("house_id", "world_id"),
	FOREIGN KEY ("house_id", "world_id") REFERENCES "houses" ("id", "world_id")
	FOREIGN KEY ("player_id") REFERENCES "players" ("id")
);

CREATE TABLE "player_deaths" (
	"id" INTEGER PRIMARY KEY,
	"player_id" INTEGER NOT NULL,
	"date" INTEGER NOT NULL,
	"level" INTEGER NOT NULL,
	FOREIGN KEY ("player_id") REFERENCES "players" ("id")
);

CREATE TABLE "killers" (
	"id" INTEGER PRIMARY KEY,
	"death_id" INTEGER NOT NULL,
	"final_hit" BOOLEAN NOT NULL DEFAULT FALSE,
	"unjustified" BOOLEAN NOT NULL DEFAULT FALSE,
	FOREIGN KEY ("death_id") REFERENCES "player_deaths" ("id")
);

CREATE TABLE "player_killers" (
	"kill_id" INTEGER NOT NULL,
	"player_id" INTEGER NOT NULL,
	FOREIGN KEY ("kill_id") REFERENCES "killers" ("id"),
	FOREIGN KEY ("player_id") REFERENCES "players" ("id")
);

CREATE TABLE "environment_killers" (
	"kill_id" INTEGER NOT NULL,
	"name" VARCHAR(255) NOT NULL,
	FOREIGN KEY ("kill_id") REFERENCES "killers" ("id")
);

CREATE TABLE "player_depotitems" (
	"player_id" INTEGER NOT NULL,
	"sid" INTEGER NOT NULL,
	"pid" INTEGER NOT NULL DEFAULT 0,
	"itemtype" INTEGER NOT NULL,
	"count" INTEGER NOT NULL DEFAULT 0,
	"attributes" BLOB NOT NULL,
	UNIQUE ("player_id", "sid"),
	FOREIGN KEY ("player_id") REFERENCES "players" ("id")
);

CREATE TABLE "player_namelocks" (
	"player_id" INTEGER NOT NULL,
	"name" VARCHAR(255) NOT NULL,
	"new_name" VARCHAR(255) NOT NULL,
	"date" INTEGER NOT NULL DEFAULT 0,
	FOREIGN KEY ("player_id") REFERENCES "players" ("id")
);

CREATE TABLE "player_skills" (
	"player_id" INTEGER NOT NULL,
	"skillid" INTEGER NOT NULL,
	"value" INTEGER NOT NULL DEFAULT 0,
	"count" INTEGER NOT NULL DEFAULT 0,
	UNIQUE ("player_id", "skillid"),
	FOREIGN KEY ("player_id") REFERENCES "players" ("id")
);

CREATE TABLE "player_storage" (
	"player_id" INTEGER NOT NULL,
	"key" VARCHAR(32) NOT NULL,
	"value" VARCHAR(255) NOT NULL DEFAULT '0',
	UNIQUE ("player_id", "key"),
	FOREIGN KEY ("player_id") REFERENCES "players" ("id")
);

CREATE TABLE "player_viplist" (
	"player_id" INTEGER NOT NULL,
	"vip_id" INTEGER NOT NULL,
	UNIQUE ("player_id", "vip_id"),
	FOREIGN KEY ("player_id") REFERENCES "players" ("id"),
	FOREIGN KEY ("vip_id") REFERENCES "players" ("id")
);

CREATE TABLE "tiles" (
	"id" INTEGER NOT NULL,
	"world_id" INTEGER NOT NULL DEFAULT 0,
	"house_id" INTEGER NOT NULL,
	"x" INTEGER NOT NULL,
	"y" INTEGER NOT NULL,
	"z" INTEGER NOT NULL,
	UNIQUE ("id", "world_id"),
	FOREIGN KEY ("house_id", "world_id") REFERENCES "houses" ("id", "world_id")
);

CREATE TABLE "tile_items" (
	"tile_id" INTEGER NOT NULL,
	"world_id" INTEGER NOT NULL DEFAULT 0,
	"sid" INTEGER NOT NULL,
	"pid" INTEGER NOT NULL DEFAULT 0,
	"itemtype" INTEGER NOT NULL,
	"count" INTEGER NOT NULL DEFAULT 0,
	"attributes" BLOB NOT NULL,
	UNIQUE ("tile_id", "world_id", "sid"),
	FOREIGN KEY ("tile_id") REFERENCES "tiles" ("id")
);

CREATE TABLE "player_items" (
	"player_id" INT NOT NULL,
	"sid" INT NOT NULL,
	"pid" INT NOT NULL DEFAULT 0,
	"itemtype" INT NOT NULL,
	"count" INT NOT NULL DEFAULT 0,
	"attributes" BLOB NOT NULL,
	UNIQUE ("player_id", "sid"),
	FOREIGN KEY ("player_id") REFERENCES "players" ("id")
);

CREATE TABLE "player_spells" (
	"player_id" INTEGER NOT NULL,
	"name" VARCHAR(255) NOT NULL,
	UNIQUE ("player_id", "name"),
	FOREIGN KEY ("player_id") REFERENCES "players" ("id")
);

CREATE TABLE "bans" (
	"id" INTEGER PRIMARY KEY NOT NULL,
	"type" INTEGER NOT NULL,
	"value" INTEGER NOT NULL,
	"param" INTEGER NOT NULL DEFAULT 4294967295,
	"active" BOOLEAN NOT NULL DEFAULT 1,
	"expires" INTEGER NOT NULL,
	"added" INTEGER NOT NULL,
	"admin_id" INTEGER NOT NULL DEFAULT 0,
	"comment" TEXT NOT NULL,
	"reason" INTEGER NOT NULL DEFAULT 0,
	"action" INTEGER  NOT NULL DEFAULT 0,
	"statement" VARCHAR(255) NOT NULL DEFAULT ''
);

CREATE TRIGGER "oncreate_guilds"
AFTER INSERT ON "guilds"
BEGIN
	INSERT INTO "guild_ranks" ("name", "level", "guild_id") VALUES ("Leader", 3, NEW."id");
	INSERT INTO "guild_ranks" ("name", "level", "guild_id") VALUES ("Vice-Leader", 2, NEW."id");
	INSERT INTO "guild_ranks" ("name", "level", "guild_id") VALUES ("Member", 1, NEW."id");
END;

CREATE TRIGGER "oncreate_players"
AFTER INSERT
ON "players"
BEGIN
	INSERT INTO "player_skills" ("player_id", "skillid", "value") VALUES (NEW."id", 0, 10);
	INSERT INTO "player_skills" ("player_id", "skillid", "value") VALUES (NEW."id", 1, 10);
	INSERT INTO "player_skills" ("player_id", "skillid", "value") VALUES (NEW."id", 2, 10);
	INSERT INTO "player_skills" ("player_id", "skillid", "value") VALUES (NEW."id", 3, 10);
	INSERT INTO "player_skills" ("player_id", "skillid", "value") VALUES (NEW."id", 4, 10);
	INSERT INTO "player_skills" ("player_id", "skillid", "value") VALUES (NEW."id", 5, 10);
	INSERT INTO "player_skills" ("player_id", "skillid", "value") VALUES (NEW."id", 6, 10);
END;

CREATE TRIGGER "ondelete_accounts"
BEFORE DELETE
ON "accounts"
FOR EACH ROW
BEGIN
	DELETE FROM "players" WHERE "account_id" = OLD."id";
	DELETE FROM "account_viplist" WHERE "account_id" = OLD."id";
	DELETE FROM "bans" WHERE "type" IN (3, 4) AND "value" = OLD."id";
END;

CREATE TRIGGER "ondelete_players"
BEFORE DELETE
ON "players"
FOR EACH ROW
BEGIN
	SELECT RAISE(ROLLBACK, 'DELETE on table "players" violates foreign: "ownerid" from table "guilds"')
	WHERE (SELECT "id" FROM "guilds" WHERE "ownerid" = OLD."id") IS NOT NULL;

	DELETE FROM "account_viplist" WHERE "player_id" = OLD."id";
	DELETE FROM "player_viplist" WHERE "player_id" = OLD."id" OR "vip_id" = OLD."id";
	DELETE FROM "player_storage" WHERE "player_id" = OLD."id";
	DELETE FROM "player_skills" WHERE "player_id" = OLD."id";
	DELETE FROM "player_items" WHERE "player_id" = OLD."id";
	DELETE FROM "player_depotitems" WHERE "player_id" = OLD."id";
	DELETE FROM "player_spells" WHERE "player_id" = OLD."id";
	DELETE FROM "player_killers" WHERE "player_id" = OLD."id";
	DELETE FROM "player_deaths" WHERE "player_id" = OLD."id";
	DELETE FROM "guild_invites" WHERE "player_id" = OLD."id";
	DELETE FROM "bans" WHERE "type" IN (2, 5) AND "value" = OLD."id";
	UPDATE "houses" SET "owner" = 0 WHERE "owner" = OLD."id";
END;

CREATE TRIGGER "ondelete_guilds"
BEFORE DELETE
ON "guilds"
FOR EACH ROW
BEGIN
	UPDATE "players" SET "guildnick" = '', "rank_id" = 0 WHERE "rank_id" IN (SELECT "id" FROM "guild_ranks" WHERE "guild_id" = OLD."id");
	DELETE FROM "guild_ranks" WHERE "guild_id" = OLD."id";
	DELETE FROM "guild_invites" WHERE "guild_id" = OLD."id";
END;

CREATE TRIGGER "oninsert_players"
BEFORE INSERT
ON "players"
FOR EACH ROW
BEGIN
	SELECT RAISE(ROLLBACK, 'INSERT on table "players" violates foreign: "account_id"')
	WHERE NEW."account_id" IS NULL
		OR (SELECT "id" FROM "accounts" WHERE "id" = NEW."account_id") IS NULL;
END;

CREATE TRIGGER "onupdate_players"
BEFORE UPDATE
ON "players"
FOR EACH ROW
BEGIN
	SELECT RAISE(ROLLBACK, 'UPDATE on table "players" violates foreign: "account_id"')
	WHERE NEW."account_id" IS NULL
		OR (SELECT "id" FROM "accounts" WHERE "id" = NEW."account_id") IS NULL;
END;

CREATE TRIGGER "oninsert_guilds"
BEFORE INSERT
ON "guilds"
FOR EACH ROW
BEGIN
	SELECT RAISE(ROLLBACK, 'INSERT on table "guilds" violates foreign: "ownerid"')
	WHERE NEW."ownerid" IS NULL
		OR (SELECT "id" FROM "players" WHERE "id" = NEW."ownerid") IS NULL;
END;

CREATE TRIGGER "onupdate_guilds"
BEFORE UPDATE
ON "guilds"
FOR EACH ROW
BEGIN
	SELECT RAISE(ROLLBACK, 'UPDATE on table "guilds" violates foreign: "ownerid"')
	WHERE NEW."ownerid" IS NULL
		OR (SELECT "id" FROM "players" WHERE "id" = NEW."ownerid") IS NULL;
END;

CREATE TRIGGER "ondelete_houses"
BEFORE DELETE
ON "houses"
FOR EACH ROW
BEGIN
	DELETE FROM "house_lists" WHERE "house_id" = OLD."id";
END;

CREATE TRIGGER "ondelete_tiles"
BEFORE DELETE
ON "tiles"
FOR EACH ROW
BEGIN
	DELETE FROM "tile_items" WHERE "tile_id" = OLD."id";
END;

CREATE TRIGGER "oninsert_guild_ranks"
BEFORE INSERT
ON "guild_ranks"
FOR EACH ROW
BEGIN
	SELECT RAISE(ROLLBACK, 'INSERT on table "guild_ranks" violates foreign: "guild_id"')
	WHERE NEW."guild_id" IS NULL
		OR (SELECT "id" FROM "guilds" WHERE "id" = NEW."guild_id") IS NULL;
END;

CREATE TRIGGER "onupdate_guild_ranks"
BEFORE UPDATE
ON "guild_ranks"
FOR EACH ROW
BEGIN
	SELECT RAISE(ROLLBACK, 'UPDATE on table "guild_ranks" violates foreign: "guild_id"')
	WHERE NEW."guild_id" IS NULL
		OR (SELECT "id" FROM "guilds" WHERE "id" = NEW."guild_id") IS NULL;
END;

CREATE TRIGGER "oninsert_house_lists"
BEFORE INSERT
ON "house_lists"
FOR EACH ROW
BEGIN
	SELECT RAISE(ROLLBACK, 'INSERT on table "house_lists" violates foreign: "house_id"')
	WHERE NEW."house_id" IS NULL
		OR (SELECT "id" FROM "houses" WHERE "id" = NEW."house_id") IS NULL;
END;

CREATE TRIGGER "onupdate_house_lists"
BEFORE UPDATE
ON "house_lists"
FOR EACH ROW
BEGIN
	SELECT RAISE(ROLLBACK, 'UPDATE on table "house_lists" violates foreign: "house_id"')
	WHERE NEW."house_id" IS NULL
		OR (SELECT "id" FROM "houses" WHERE "id" = NEW."house_id") IS NULL;
END;

CREATE TRIGGER "oninsert_player_depotitems"
BEFORE INSERT
ON "player_depotitems"
FOR EACH ROW
BEGIN
	SELECT RAISE(ROLLBACK, 'INSERT on table "player_depotitems" violates foreign: "player_id"')
	WHERE NEW."player_id" IS NULL
		OR (SELECT "id" FROM "players" WHERE "id" = NEW."player_id") IS NULL;
END;

CREATE TRIGGER "onupdate_player_depotitems"
BEFORE UPDATE
ON "player_depotitems"
FOR EACH ROW
BEGIN
	SELECT RAISE(ROLLBACK, 'UPDATE on table "player_depotitems" violates foreign: "player_id"')
	WHERE NEW."player_id" IS NULL
		OR (SELECT "id" FROM "players" WHERE "id" = NEW."player_id") IS NULL;
END;

CREATE TRIGGER "oninsert_player_skills"
BEFORE INSERT
ON "player_skills"
FOR EACH ROW
BEGIN
	SELECT RAISE(ROLLBACK, 'INSERT on table "player_skills" violates foreign: "player_id"')
	WHERE NEW."player_id" IS NULL
		OR (SELECT "id" FROM "players" WHERE "id" = NEW."player_id") IS NULL;
END;

CREATE TRIGGER "onupdate_player_skills"
BEFORE UPDATE
ON "player_skills"
FOR EACH ROW
BEGIN
	SELECT RAISE(ROLLBACK, 'UPDATE on table "player_skills" violates foreign: "player_id"')
	WHERE NEW."player_id" IS NULL
		OR (SELECT "id" FROM "players" WHERE "id" = NEW."player_id") IS NULL;
END;

CREATE TRIGGER "oninsert_player_storage"
BEFORE INSERT
ON "player_storage"
FOR EACH ROW
BEGIN
	SELECT RAISE(ROLLBACK, 'INSERT on table "player_storage" violates foreign: "player_id"')
	WHERE NEW."player_id" IS NULL
		OR (SELECT "id" FROM "players" WHERE "id" = NEW."player_id") IS NULL;
END;

CREATE TRIGGER "onupdate_player_storage"
BEFORE UPDATE
ON "player_storage"
FOR EACH ROW
BEGIN
	SELECT RAISE(ROLLBACK, 'UPDATE on table "player_storage" violates foreign: "player_id"')
	WHERE NEW."player_id" IS NULL
		OR (SELECT "id" FROM "players" WHERE "id" = NEW."player_id") IS NULL;
END;

CREATE TRIGGER "oninsert_player_viplist"
BEFORE INSERT
ON "player_viplist"
FOR EACH ROW
BEGIN
	SELECT RAISE(ROLLBACK, 'INSERT on table "player_viplist" violates foreign: "player_id"')
	WHERE NEW."player_id" IS NULL
		OR (SELECT "id" FROM "players" WHERE "id" = NEW."player_id") IS NULL;

	SELECT RAISE(ROLLBACK, 'INSERT on table "player_viplist" violates foreign: "vip_id"')
	WHERE NEW."vip_id" IS NULL
		OR (SELECT "id" FROM "players" WHERE "id" = NEW."vip_id") IS NULL;
END;

CREATE TRIGGER "onupdate_player_viplist"
BEFORE UPDATE
ON "player_viplist"
FOR EACH ROW
BEGIN
	SELECT RAISE(ROLLBACK, 'UPDATE on table "player_viplist" violates foreign: "vip_id"')
	WHERE NEW."vip_id" IS NULL
		OR (SELECT "id" FROM "players" WHERE "id" = NEW."vip_id") IS NULL;
END;

CREATE TRIGGER "oninsert_account_viplist"
BEFORE INSERT
ON "account_viplist"
FOR EACH ROW
BEGIN
	SELECT RAISE(ROLLBACK, 'INSERT on table "account_viplist" violates foreign: "account_id"')
	WHERE NEW."account_id" IS NULL
		OR (SELECT "id" FROM "accounts" WHERE "id" = NEW."account_id") IS NULL;

	SELECT RAISE(ROLLBACK, 'INSERT on table "account_viplist" violates foreign: "player_id"')
	WHERE NEW."player_id" IS NULL
		OR (SELECT "id" FROM "players" WHERE "id" = NEW."player_id") IS NULL;
END;

CREATE TRIGGER "onupdate_account_viplist"
BEFORE UPDATE
ON "account_viplist"
FOR EACH ROW
BEGIN
	SELECT RAISE(ROLLBACK, 'UPDATE on table "account_viplist" violates foreign: "player_id"')
	WHERE NEW."player_id" IS NULL
		OR (SELECT "id" FROM "players" WHERE "id" = NEW."player_id") IS NULL;
END;

CREATE TRIGGER "oninsert_tile_items"
BEFORE INSERT
ON "tile_items"
FOR EACH ROW
BEGIN
	SELECT RAISE(ROLLBACK, 'INSERT on table "tile_items" violates foreign: "tile_id"')
	WHERE NEW."tile_id" IS NULL
		OR (SELECT "id" FROM "tiles" WHERE "id" = NEW."tile_id") IS NULL;
END;

CREATE TRIGGER "onupdate_tile_items"
BEFORE UPDATE
ON "tile_items"
FOR EACH ROW
BEGIN
	SELECT RAISE(ROLLBACK, 'UPDATE on table "tile_items" violates foreign: "tile_id"')
	WHERE NEW."tile_id" IS NULL
		OR (SELECT "id" FROM "tiles" WHERE "id" = NEW."tile_id") IS NULL;
END;

CREATE TRIGGER "oninsert_player_spells"
BEFORE INSERT
ON "player_spells"
FOR EACH ROW
BEGIN
	SELECT RAISE(ROLLBACK, 'INSERT on table "player_spells" violates foreign: "player_id"')
	WHERE NEW."player_id" IS NULL
		OR (SELECT "id" FROM "players" WHERE "id" = NEW."player_id") IS NULL;
END;

CREATE TRIGGER "onupdate_player_spells"
BEFORE UPDATE
ON "player_spells"
FOR EACH ROW
BEGIN
	SELECT RAISE(ROLLBACK, 'UPDATE on table "player_spells" violates foreign: "player_id"')
	WHERE NEW."player_id" IS NULL
		OR (SELECT "id" FROM "players" WHERE "id" = NEW."player_id") IS NULL;
END;

CREATE TRIGGER "oninsert_player_deaths"
BEFORE INSERT
ON "player_deaths"
FOR EACH ROW
BEGIN
	SELECT RAISE(ROLLBACK, 'INSERT on table "player_deaths" violates foreign: "player_id"')
	WHERE NEW."player_id" IS NULL
		OR (SELECT "id" FROM "players" WHERE "id" = NEW."player_id") IS NULL;
END;

CREATE TRIGGER "onupdate_player_deaths"
BEFORE UPDATE
ON "player_deaths"
FOR EACH ROW
BEGIN
	SELECT RAISE(ROLLBACK, 'UPDATE on table "player_deaths" violates foreign: "player_id"')
	WHERE NEW."player_id" IS NULL
		OR (SELECT "id" FROM "players" WHERE "id" = NEW."player_id") IS NULL;
END;

CREATE TRIGGER "oninsert_killers"
BEFORE INSERT
ON "killers"
FOR EACH ROW
BEGIN
	SELECT RAISE(ROLLBACK, 'INSERT on table "killers" violates foreign: "death_id"')
	WHERE NEW."death_id" IS NULL
		OR (SELECT "id" FROM "player_deaths" WHERE "id" = NEW."death_id") IS NULL;
END;

CREATE TRIGGER "onupdate_killers"
BEFORE UPDATE
ON "killers"
FOR EACH ROW
BEGIN
	SELECT RAISE(ROLLBACK, 'UPDATE on table "killers" violates foreign: "death_id"')
	WHERE NEW."death_id" IS NULL
		OR (SELECT "id" FROM "player_deaths" WHERE "id" = NEW."death_id") IS NULL;
END;

CREATE TRIGGER "oninsert_environment_killers"
BEFORE INSERT
ON "environment_killers"
FOR EACH ROW
BEGIN
	SELECT RAISE(ROLLBACK, 'INSERT on table "enviroment_killers" violates foreign: "kill_id"')
	WHERE NEW."kill_id" IS NULL
		OR (SELECT "id" FROM "killers" WHERE "id" = NEW."kill_id") IS NULL;
END;

CREATE TRIGGER "onupdate_environment_killers"
BEFORE UPDATE
ON "environment_killers"
FOR EACH ROW
BEGIN
	SELECT RAISE(ROLLBACK, 'INSERT on table "enviroment_killers" violates foreign: "kill_id"')
	WHERE NEW."kill_id" IS NULL
		OR (SELECT "id" FROM "killers" WHERE "id" = NEW."kill_id") IS NULL;
END;

CREATE TRIGGER "oninsert_player_killers"
BEFORE INSERT
ON "player_killers"
FOR EACH ROW
BEGIN
	SELECT RAISE(ROLLBACK, 'INSERT on table "player_killers" violates foreign: "player_id"')
	WHERE NEW."player_id" IS NULL
		OR (SELECT "id" FROM "players" WHERE "id" = NEW."player_id") IS NULL;
	
	SELECT RAISE(ROLLBACK, 'INSERT on table "player_killers" violates foreign: "kill_id"')
	WHERE NEW."kill_id" IS NULL
		OR (SELECT "id" FROM "killers" WHERE "id" = NEW."kill_id") IS NULL;
END;

CREATE TRIGGER "onupdate_player_killers"
BEFORE UPDATE
ON "player_killers"
FOR EACH ROW
BEGIN
	SELECT RAISE(ROLLBACK, 'UPDATE on table "player_killers" violates foreign: "player_id"')
	WHERE NEW."player_id" IS NULL
		OR (SELECT "id" FROM "players" WHERE "id" = NEW."player_id") IS NULL;
		
	SELECT RAISE(ROLLBACK, 'UPDATE on table "killers" violates foreign: "kill_id"')
	WHERE NEW."kill_id" IS NULL
		OR (SELECT "id" FROM "killers" WHERE "id" = NEW."kill_id") IS NULL;
END;

 

 

Os arquivos .s3db são do SQLite Studio, essa extensão de arquivo é usada para armazenar a database para o programa em específico.
A database most often contains one or more tables. Each table is identified by a name (e.g. "Customers" or "Orders"). Tables contain records (rows) with data.

 

 

Uma database quase sempre contém uma ou mais tabelas. Cada tabela é identificada por um nome ( Exemplo: "Clientes" ou "Pedidos"). Tabelas contém gravações (rows) com data dentro deles.

Trecho retirado de: http://www.w3schools.com/sql/sql_syntax.asp

 

 

Querys - query, pode ser traduzido para: pergunta, questão, dúvida. Eles são usados em tudo dentro da linguagem, tudo que você faz com seus dados na database, é um query que roda no interpretador.

AJ9YJY8.png

 

Cada tabela contém colunas, e cada coluna, contém data = dados, e o conjunto de dados forma a sua database, através da organização de rows e colunas. Vou explicar melhor na prática:

 

Sintaxe - é todo o conjunto de comandos de uma linguagem. que serve para montar a lógica.

 

Umas das sintaxes do SQL:

SELECT - Extrai dados da database
UPDATE - Atualiza dados da database
DELETE - Deleta dados de uma database
INSERT INTO - Insere novos dados dentro da database
CREATE DATABASE - Cria uma nova database
ALTER DATABASE - Altera uma database
CREATE TABLE - Cria uma nova tabela
ALTER TABLE - Modifica uma tabela
DROP TABLE - Deleta uma tabela
CREATE INDEX - Cria um index
DROP INDEX - Deleta um index
DISTINCT - Usa-se junto com SELECT para distinguir o que selecionar
WHERE - Usa para impor condições
AND - Usa para somar condições
OR - Usa para subtrair condições

...E etc...
AJ9YJY8.png

Acesse este link, é um interpretador de querys, com uma database. No canto direito "You Database:" você pode visualizar os dados, e restaurar, restaure quando você fizer alguma merda (como deletar ou alterar algo sem querer).

 

Lá dentro, escreva isto, e aperte "Run SQL >>" , o interpretador irá rodar/executar o query.

SELECT * FROM Customers;

 

 

Querys são terminados com ( ; - ponto e vírgula) , apesar de não ser necessário, é um símbolo vital para fazer múltiplas declarações, ele pode ser descartado no último query, porém é recomendável colocar sempre no final de qualquer query, para que não haja erros.

 

 

 

Essa expressão acima, seleciona * (todos os dados) da tabela Customers (poderia ser tabela Accounts ou Players), que contém várias rows/fileiras (um total de 91), vamos diminuir utilizando outro comando, para diminuir a quantidade de informação:

SELECT * FROM Customers WHERE CustomerID < 4;

Ótimo, agora ficará assim:

 

DGw3vqT.png

 

Para explicar o que é o que:

 

6IDOsF9.png

 

Quando usamos um QUERY para selecionar, seguimos o seguinte padrão:

SELECT coluna FROM tabela WHERE coluna=1;

Ou 

SELECT ContactName FROM Customers WHERE Address="Obere Str. 57"

isso vai selecionar direto "Maria Anders" pois os endereços são únicos...

 

Essa situação pode ser usada no tibia por exemplo, para selecionar o noivo ou noiva no marriage system,

já que eles precisam ser colocados no banco de dados.

 

 

Os storages não são nada mais nada menos do que dados da database, que são chamados e alterados através de querys, por LUA ou C++...

 

Vale lembrar pessoal que para usar um query em LUA, é só usar a função:

db.query("SELECT * FROM `accounts`")

o query dentro foi somente um exemplo.

 

 

Fontes:

http://www.w3schools.com/sql/

http://www.1keydata.com/pt/sql/ Não usei, mas é uma opção em português.

 

Recomendo muito o w3schools, não só para sql, mas para tudo que tiver lá, eles explicam muito bem.

 

 

Bom, foi isso pessoal, espero que tenham gostado e que ajude nas suas empreitadas

QUALQUER Crítica construtiva ou sugestão pode comentar abaixo.

Posso fazer outros tópicos de sql, caso este esteja bom.

Suporte no tópico.

:smile_positivo:

Editado Janeiro 31 por Caronte

Felipe MoraesF
31 de janeiro de 2016 às 18:06

Aguardo por mais tutoriais como esse.

Apesar de bem básico, com certeza já é um caminho para os que estão começando.

Parabéns, e obrigado por compartilhar conosco.

FurabioF
31 de janeiro de 2016 às 18:16

Muito bom caronte :smile_positivo:

AdministradorA
01 de fevereiro de 2016 às 10:47

Boa, tava faltando conteúdos assim.

CaronteC
01 de fevereiro de 2016 às 10:59

Aguardo por mais tutoriais como esse.

Apesar de bem básico, com certeza já é um caminho para os que estão começando.

Parabéns, e obrigado por compartilhar conosco.

 

Hahaha, verdade, mas eu fiquei muito preocupado de aprofundar e os leitores não entenderem, existem querys bem complicados...

 

Muito bom caronte :smile_positivo:

 

Obrigado :D , espero que ajude

 

Boa, tava faltando conteúdos assim.

 

Cadê a seção escolinha? :tongue:

queria postar lá, seria legal por 1 seção para cada assunto... (para melhor organização)

Editado Fevereiro 1 por Caronte