1. Abstract
Sakila is a sample database available in the official mysql documentation (Download link below). That repository aims: (I) Exemplify technics about how to use Structured Query Language (SQL). (II) Use native functions utils to: concatenate, count, calculate, get current time and others. (III) Introduce joins using two or three tables.
Exemples: CREATE, ALTER, DROP, [...]. Used to interact with database object structures.
CREATE DATABASE SECURITY_ENTERPRISE DEFAULT CHARACTER SET UTF8;
---------------------------------------------------------------------------
USE SECURITY_ENTERPRISE;
---------------------------------------------------------------------------
CREATE TABLE USER (
USER_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
FIRST_NAME VARCHAR(45),
LAST_NAME VARCHAR(45),
EMAIL VARCHAR(45),
P_ASSW0RD TEXT,
CREATED_AT DATETIME DEFAULT CURRENT_TIMESTAMP()
);
---------------------------------------------------------------------------
DROP DATABASE SECURITY_ENTERPRISE;
Exemples: INSERT, DELETE, UPDATE, [...]. Used to realize interact with data.
INSERT INTO
USER (FIRST_NAME, LAST_NAME, EMAIL, P_ASSW0RD)
VALUES
("FIRST_NAME", "LAST_NAME", "exemple@exemple.com", MD5("password"));
---------------------------------------------------------------------------
UPDATE USER SET FIRST_NAME = "LUIZ" WHERE USER_ID = 1;
---------------------------------------------------------------------------
DELETE FROM USER WHERE USER_ID = 1;
Exemples: SELECT. Used to get data, make joins, etc.
SELECT
CITY.CITY,
COUNTRY.COUNTRY
FROM
COUNTRY
RIGHT JOIN
CITY ON CITY.COUNTRY_ID = COUNTRY.COUNTRY_ID
WHERE COUNTRY.COUNTRY = "BRAZIL"
ORDER BY CITY.CITY DESC;
Exemples: BEGIN TRANSACTION, COMMIT, ROLLBACK, [...]. Utils to make and validate (or not) important or big changes according response events.
START TRANSACTION;
-- ALL TRANSACTION CODE
[...];
COMMIT;
START TRANSACTION;
-- ALL TRANSACTION CODE;
[...];
ROLLBACK;
Exemples: DENY, REVOKE, GRANT, [...].
GRANT ALL ON SECURITY_ENTERPRISE.* TO 'admin'@'localhost';