Most helpful SQL commands

Permanent data is everywhere. We need to remember what happened before to avoid repeating everything, basically to allow machines to remember with SQL.

All databases must comply with the ACID concept:

• Atomicity: All transactions complete or none of them
• Consistency: Data must comply with the defined constraints and rules.
• Isolation: One transaction cannot be affected by another. This means that the transactions will modify the values sequentially.
• Durability: Once we commit, the value must become permanent.

Creating a relational SQL database

Relational databases are well formatted, we can define tables and the relation between them, with foreign keys. Tables are composed of rows that contain the cells with our data.

If you are going to work with big unstructured data and complex information like joining documents, graphs, etc, you might consider non-relational databases.

Otherwise, let’s explore what is the best RDBMS (Relational Database Management Systems) according to your needs:

Free options

Best options if you require faster writing:

• PostgreSQL PostgreSQL: Downloads: PostgreSQL is great at writing performance due to features like Write-Ahead Logging (WAL), efficient locking (MVCC), bulk write optimization, partitioning, parallelism, and tunable settings that balance speed and data integrity.
• CockroachDB CockroachDB Releases: CockroachDB excels at writing performance with features like distributed architecture, Raft-based consensus for replication, automatic sharding, and parallel processing across nodes, ensuring scalability and fault tolerance.

Best options if you require faster reading:

• MySQL MySQL :: MySQL Downloads: MySQL is good at reading performance due to its optimized query caching, efficient indexing, InnoDB’s clustered indexes, replication for read scaling, and support for read-only replicas.
• SQLServer SQL Server Downloads | Microsoft: SQL Server is good at reading performance due to its advanced indexing options (e.g., column store and filtered indexes), query optimization, in-memory OLTP, data compression, and read replicas for scaling.
• MariaDB Download MariaDB Products & Tools | MariaDB: MariaDB is good at reading performance due to its advanced indexing, query cache, parallel query execution, optimized storage engines like Aria and InnoDB, and support for replication and sharding.

Alternatively, you can download XAMPP, which has MySQL Download XAMPP

Most used queries

SHOW (DATABASES|TABLES|USERS)

DDL Data Definition Language

• CREATE
  • CREATE (SCHEMA|DATABASE|TABLE) name {... OPTIONS}
• DROP
  • DROP (SCHEMA|DATABASE|TABLE) name
• ALTER
  • ALTER (SCHEMA|DATABASE|TABLE) name (ADD|DROP COLUMN| MODIFY COLUMN| RENAME COLUMN A to B) OPTIONS
• TRUNCATE: Removes all the data from a table, which is faster than DELETE since it doesn’t allow rolling back.
  • TRUNCATE TABLE table_name

DML Data Manipulation Language

• INSERT
  • INSERT INTO table_name (column_names,,,) VALUES (values_to_column_names,,,)
  • Duplicate
    • INSERT INTO table_name SELECT *FROM table_name
• DELETE: You cannot roll back after committing. Be aware that some databases have the auto-commit enabled by default so rolling back won’t work:
  • See it by running:
    • select * from information_schema.global_variables where variable_name = 'AUTOCOMMIT'; If auto-commit is enabled save point and rolling back to won’t restore your data.
    • SAVEPOINT beforedeleting
    • DELETE FROM table_name WHERE (condition like id='10')
    • ROLLBACK to beforedeleting
• UPDATE: You cannot roll back after committing.
  • SAVEPOINT beforeupdating
  • UPDATE table_name SET column_name = new_value WHERE (condition like id='10')
  • ROLLBACK to beforeupdating

DQL Data Query Language

• SELECT:
  • SELECT (*|COLUMN_NAMES) FROM table_name WHERE (condition)

DCL Data Control Language

This is used for controlling the permissions for users:

SELECT User, Host, authentication_string FROM mysql.user;

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

• GRANT
  • GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on myDatabase.* TO 'user'@'localhost' WITH GRANT OPTION;;
• REVOKE
  • REVOKE (CREATE|ALTER|...)ON database_name.table_name FROM 'username'@'localhost';
• SHOW
  • SHOW GRANTS FOR 'username'@'host';
• DROP
  • DROP USER 'username'@'localhost';

Well, the most important command may be to create our database. Name is not case-sensitive:

CREATE SCHEMA company; CREATE DATABASE company;

Let’s configure a user for this database. If you see any errors, try to recover with mysqlcheck -u root -p --repair --all-databases

CREATE USER 'companyadmin'@'host' IDENTIFIED BY 'password';   GRANT ALL on company.* TO 'exampleadmin'@'localhost' WITH GRANT OPTION; 

Now, let’s create our first table, we can add constraints to avoid invalid values:

CREATE TABLE company.employee( firstname VARCHAR(15) NOT NULL, lastname VARCHAR(15) NOT NULL, country CHAR(9) NOT NULL, birthdate DATE, mainaddress VARCHAR(30), city VARCHAR(20), gender CHAR DEFAULT 'M', salary DECIMAL(10,2), tax_id VARCHAR(15) DEFAULT LEFT(MD5(RAND()), 10), active INT NOT NULL, CONSTRAINT pk_mytable PRIMARY KEY (tax_id), CONSTRAINT check_gender CHECK(gender='M' or gender='F') );

Let’s alter the column firstname to increase the length

ALTER TABLE company.employee MODIFY COLUMN firstname VARCHAR(40) NOT NULL 

Then let’s insert all of the values

INSERT INTO company.employee VALUES ('Louis', 'James', 'USA', '1937-11-10', 'Street Escobedo 89', 'LA', 'M', 55000.00, '212', 1);

We can also insert only the ones that are not nullable

INSERT INTO company.employee(firstname,lastname,country,active,tax_id) VALUES ('Mary', 'James', 'USA', 1,'214');

Now let’s get all of the rows

select *from company.employee;

or to get specific columns

select firstname, lastname,country,tax_id from company.employee;

+-----------+----------+---------+--------+
| firstname | lastname | country | tax_id |
+-----------+----------+---------+--------+
| Louis     | James    | USA     | 212    |
| Mary      | James    | USA     | 214    |
+-----------+----------+---------+--------+

Now, let’s perform some updates

UPDATE company.employee SET country="CANADA" WHERE country="USA";

See the new values

+-----------+----------+---------+--------+
| firstname | lastname | country | tax_id |
+-----------+----------+---------+--------+
| Louis     | James    | CANADA  | 212    |
| Mary      | James    | CANADA  | 214    |
+-----------+----------+---------+--------+

Let’s delete an entry, please don’t forget to use the where, otherwise it will delete all of the entries

DELETE from company.employee where firstname='Louis';

Now, let’s create another table that will be linked to our first database:

CREATE TABLE company.works_in (     id INT AUTO_INCREMENT PRIMARY KEY,     id_employee CHAR(9) NOT NULL,     place VARCHAR(40) NOT NULL,     CONSTRAINT fk_employee FOREIGN KEY (id_employee) REFERENCES company.employee(tax_id) ); 

Fill up these two tables with:

-- Inserting data into the employee table with explicitly defined tax_id INSERT INTO company.employee (firstname, lastname, country, birthdate, mainaddress, city, gender, salary, tax_id, active) VALUES ('John', 'Doe', 'US', '1985-08-15', '1234 Elm St', 'New York', 'M', 55000.00, 'TAX123456', 1), ('Jane', 'Smith', 'US', '1990-03-22', '5678 Oak St', 'Los Angeles', 'F', 60000.00, 'TAX234567', 1), ('Alice', 'Johnson', 'UK', '1982-11-05', '45 Baker St', 'London', 'F', 70000.00, 'TAX345678', 1), ('Bob', 'Brown', 'US', '1975-01-17', '12 Pine St', 'Chicago', 'M', 45000.00, 'TAX456789', 1), ('Charlie', 'Davis', 'CA', '1992-06-30', '789 Maple Ave', 'Toronto', 'M', 52000.00, 'TAX567890', 1);  -- Inserting data into the works_in table with corresponding tax_id INSERT INTO company.works_in (id_employee, place) VALUES ('TAX123456', 'Headquarters'), ('TAX234567', 'New York Office'), ('TAX345678', 'London Office'), ('TAX456789', 'Chicago Branch'), ('TAX567890', 'Toronto Office'); 

Two ways to find which employee works in New York

 SELECT firstname, lastname, tax_id FROM company.employee WHERE tax_id IN (     SELECT id_employee     FROM company.works_in     WHERE place = 'New York Office' );  SELECT firstname, lastname, tax_id, wk.place FROM company.employee INNER JOIN company.works_in as wk ON company.employee.tax_id=wk.id_employee where wk.place like '%New York%' 

Now, let’s UNION both tables; the number of columns should match. ALL will allow duplicates while Distinct won’t.

SELECT tax_id, firstname FROM company.employee UNION SELECT id_employee, place FROM company.works_in;

Now you have the most useful queries, happy coding :)!