SQL

Translated from German using DeepL.

Date: September 2020
Reading time: 9 minutes

This blog post shows the essential SQL commands.

Overview

SQL is a query language. It is a language for manipulating and querying structured data from a database.
For example, HeidiSQL and MySQL Workbench allow you to write SQL.

To test the knowledge gained in this blog post, I recommend the online editor:
https://sqliteonline.com/ (opens in a new tab)

Sub-areas

SQL is divided into five areas. Here is a brief overview:

Practice

I will now show you how to use the above commands correctly.

I recommend that you test what you have learned in the online editor (https://sqliteonline.com/ (opens in a new tab)). It is also important to note that the commands are not case-sensitive.

This is what the online IDE looks like. On the left you can see the tables. If you expand them, you can see the individual columns. On the right are useful commands. You can also click on History to make the last commands visible.
The middle part is the most important. At the top you can write the commands. In the lower area you can see the output.

Basics

Comments

Comments can be written in two ways.

-- Comment on a line

/*
Comment over multiple lines
*/

Semicolon

A semicolon is required when the statement is finished.

Use database

To be able to work in the desired database, it must be specified.

use baseName;

DDL (Data Definition Language)

DDL commands are used when you want to create or edit various objects in a database.

CREATE

To create something, use CREATE.

DATABASE

CREATE DATABASE baseName;

When creating a database, you can also specify the character set. This is not done in this example in order to keep it simple. You can also delete databases with the same name first to avoid conflicts. But we will come to this later.

TABLE

In addition to databases, tables can also be created.

CREATE TABLE category (
    category_id INT PRIMARY KEY AUTO_INCREMENT,
    category_name VARCHAR(255)
);

Two attributes are added to this table. One of these is a primary key. This is in the form of a number. It is therefore declared as INT. After this, you write PRIMARY KEY to use it as the primary key. Finally, write AUTO_INCREMENT. This means that the number is automatically incremented.

When creating a table, you specify the required data type. The basic ones are shown in this table:

These are the most important ones. However, there is much more.

ALTER

ALTER can be used to change the structure of a table.

Insert

It is possible to insert new columns.

ALTER TABLE ort ADD country name CHAR(3);

In this example, a country name has been added to the city table.

Rename

Now you could also rename something. In this case, the name is changed from zp to zip.

ALTER TABLE place CHANGE zp zip char(5);

Default values

Default values can also be changed with ALTER.

ALTER TABLE place ALTER zip SET DEFAULT '00000';

Delete

Deletion is also possible with ALTER.

ALTER TABLE DROP place;

DROP

In the last example, DROP has already been used. You can use DROP to delete various things:

• Columns
• users
• Databases

Example

Before a database is created, it is often checked whether one with this name already exists. If this is the case, it is deleted.

DROP DATABASE IF EXISTS dataName;
CREATE DATABASE dataName;

RENAME

In combination with ALTER it is possible to rename tables.

ALTER TABLE oldName
RENAME TO newName;

TRUNCATE

With TRUNCATE you delete data from a table without deleting the table.

TRUNCATE TABLE categories;

DQL (Data Query Language)

DQL is used to query the required data.

SELECT

With SELECT you say that you want to select something. Then you specify what you want to select. In this example, this is everything (*). However, it is also possible to select only certain columns.

Then you have to specify from which table you want the attributes.

SELECT * FROM "tableName";

WHERE

It often happens that you only need certain columns that fulfill a certain condition.

SELECT "columnName" FROM "tableName" WHERE "condition";

The following table shows how to check.

Placeholder

Placeholders are used if, for example, you want all first names that begin with a certain letter.

If you test the value with a placeholder, you must use LIKE instead of =:

SELECT price FROM product WHERE description LIKE '%cola%';

Aggregate functions

These functions are used to obtain certain fields/values.

SELECT MIN(field) FROM table; -- the smallest value
SELECT MAX(field) FROM table; -- the largest value
SELECT SUM(field) FROM table; -- the sum of all values
SELECT AVG(field) FROM table; -- the average
SELECT COUNT(field) FROM table; -- the number of values

HAVING

As soon as you use aggregate functions, you no longer filter with WHERE, but with HAVING. HAVING also makes it possible to sort after grouping.

ORDER

Order can be used to sort the data records.

SELECT "columnName" FROM "tableName" ORDER BY "columnName";

By default, records are sorted in ascending order (ASC). However, you can reverse the sorting with DESC.

ORDER BY "column1" [ASC, DESC], "column2" [ASC, DESC]

GROUP

GROUP BY is used to group. This is often done using the aggregate functions.

SELECT COUNT(customerID), country
FROM customers
GROUP BY country;

AS

With AS you can change the name of a table or column for the output. The name is not overwritten but changed for the output.

SELECT market value as price FROM players;

In this example, the "Market value" column is displayed under the name "Price".
There are other ways to select. In my opinion, I will not go into the more advanced things like DISTINCT or UNION.

DML (Data Manipulation Language)

To manipulate the data in a database, you have to use the DML commands.

INSERT

With an INSERT you can insert data into a table.

CREATE table tableName (
	surname varchar(30),
	name varchar(30)
);
INSERT tableName VALUES('john', 'doe');

You do not necessarily have to insert the data of a table manually if you already have it in another table.

CREATE TABLE person AS
SELECT first surname, name
FROM customers;

There are two methods for inserting data records into a table:

Standard Insert

With this variant, you write an INSERT command on each line. This takes more time, but can be clearer.

INSERT INTO customers (customerName, city, country) VALUES ('A', 'Tokio', 'Japan');
INSERT INTO customers (customerName, city, country) VALUES ('B', 'Hongkong', 'China');

Bulk insert

I prefer the bulk insert because it is faster. You only need to write an INSERT statement. Then you can insert several data records.

INSERT INTO 'addresses' ('id', 'name', 'fk_zip')
VALUES (1, 'urs', 90000), (2, 'hans', 30000), (3, 'anna', NULL), (4, 'Kurt', 40000);

UPDATE

Certain values can be changed with UPDATE.

UPDATE customers
SET contactName = 'Alfred Schmidt', City = 'Frankfurt' WHERE customerID = 1;

DELETE

With DELETE I can delete certain attributes. As with a SELECT query, I can filter out certain data with WHERE.

DELETE from class WHERE name = C2A;

Manual data import

If I already have the data in a file, I don't have to enter all the data records manually in a table. I only have to load the file.

LOAD DATA INFILE 'abc.csv' INTO TABLE abc

DCL (Data Control Language)

If you want to assign rights, you need these commands.

To assign rights, you must create users.

CREATE User john identified BY "1234";

A user can also be deleted again.

DROP user john;

To display the users, write the following SELECT query.

SELECT * from mysql.user;

Once you have created users, you can also create user roles.

Tip: You can also create groups and give them the required rights.

GRANT

With GRANT you can distribute rights.

CREATE USER urs@192.168.13.149 IDENTIFIED BY '1234'; -- user with password 1234 is created
GRANT SELECT, UPDATE ON article.article TO urs@192.168.13.149; -- user may execute SELECT and UPDATE commands in the article table

REVOKE

With REVOKE you can revoke rights.

REVOKE SELECT ON article.article TO urs@'%'; -- Urs is not allowed to execute SELECT queries
REVOKE ALL PRIVILEGES ON article.article TO urs@'%'; -- Urs now has no rights at all

TCL (Transaction Control Language)

TCL commands can be used to make secure changes to a database. This is important at a bank, for example. The money should always be transferred in full or not. TCL commands can be used to make such changes securely.

I will not go into the individual commands here, as this would go beyond the scope. However, you can find a good description under this link:
https://www.studytonight.com/dbms/tcl-command.php#:~:text=Commit%2C%20Rollback%20and%20Savepoint%20SQL,grouped%20together%20into%20logical%20transactions (opens in a new tab).

Help!

The order of the individual commands must be correct. Otherwise you will get error messages. This graphic can help.

I hope this blog post has helped. It is clear that you don't understand/know everything when you read it for the first time. To really be able to use SQL, you have to practice.

I recommend two sources for this:

• Learn SQL the fun way: https://sql-island.informatik.uni-kl.de/ (opens in a new tab)
• Try out SQL: https://sqliteonline.com/ (opens in a new tab)