Here's a quick guide to creating and managing databases Most popular databases we'll mention here: + MySQL + PostgreSQL + SQLite + SQL Server + Oracle Each with its own particularities. ## <br>installing MySQL ```sql # On Linux (Ubuntu): sudo apt update sudo apt install mysql-server # On macOS com Homebrew: brew install mysql # On Windows: Download and install MySQL Installer from the official website. ``` ## <br>Installing PostgreSQL ```sql # On Linux (Ubuntu): sudo apt update sudo apt install postgresql postgresql-contrib # On macOS com Homebrew: brew install postgresql # On Windows: Download and install PostgreSQL Installer from the official website. ``` ## <br>Creating db **MySQL** ```sql CREATE DATABASE db_name; ``` **PostgreSQL** ```sql CREATE DATABASE db_name; ``` ## <br>Creating tables ```sql CREATE TABLE usuarios ( id SERIAL PRIMARY KEY, name VARCHAR(100), email VARCHAR(100) UNIQUE, pass VARCHAR(100), created_in TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); ``` ## <br>Inserting data ```sql INSERT INTO users (name, email, pass) VALUES ('João Silva', 'joao.silva@example.com', 'senha123'), ('Maria Santos', 'maria.santos@example.com', 'senha456'); ``` ## <br>Consulting data ```sql SELECT * FROM users; SELECT name, email FROM users WHERE id = 1; ``` ## <br>updating data ```sql UPDATE users SET name = 'João Souza' WHERE id = 1; ``` ## <br>deleting data ```sql DELETE FROM users WHERE id = 1; ``` ## <br>Indexes Creation of indexes to improve query performance ```sql CREATE INDEX idx_email ON usuarios(email); ``` ## <br>Backup and Restore MySQL: ```bash # Backup mysqldump -u user -p db_name > backup.sql # Restoration mysql -u user -p db_name < backup.sql ``` PostgreSQL: ```sql # Backup pg_dump db_name > backup.sql # Restoration psql db_name < backup.sql ``` ## <br>Security + Access Control: Create users with limited permissions. ```sql -- MySQL CREATE USER 'user'@'localhost' IDENTIFIED BY 'pass'; GRANT ALL PRIVILEGES ON db_name.* TO 'user'@'localhost'; -- PostgreSQL CREATE USER user WITH PASSWORD 'pass'; GRANT ALL PRIVILEGES ON DATABASE db_name TO user; ``` Just a quick guide to managing databases