Caronte 444 Posted January 31, 2016 Share Posted January 31, 2016 (edited) 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. 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. 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... 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: Para explicar o que é o que: 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: Edited January 31, 2016 by Caronte Link to comment Share on other sites More sharing options...
Felipe Moraes 151 Posted January 31, 2016 Share Posted January 31, 2016 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. Link to comment Share on other sites More sharing options...
Furabio 257 Posted January 31, 2016 Share Posted January 31, 2016 Muito bom caronte :smile_positivo: Link to comment Share on other sites More sharing options...
Administrador Administrador 1435 Posted February 1, 2016 Administrador Share Posted February 1, 2016 Boa, tava faltando conteúdos assim. Link to comment Share on other sites More sharing options...
Caronte 444 Posted February 1, 2016 Author Share Posted February 1, 2016 (edited) 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 , espero que ajude Boa, tava faltando conteúdos assim. Cadê a seção escolinha? queria postar lá, seria legal por 1 seção para cada assunto... (para melhor organização) Edited February 1, 2016 by Caronte Link to comment Share on other sites More sharing options...
Recommended Posts