SQL Tutorial 2024: Essential Database Commands

This nutshell tutorial or workshop will mention all commands briefly and concisely and provide a small example. SQL (mysql, Oracle SQL, Microsoft SQL, etc.) offer hundreds of functions and commands, which have already been covered in many good books and online resources. Updated 2026

SQL (Structured Query Language) is the standard language for managing databases. Here is a brief tutorial with the most important SQL commands relevant to websites:##

We provide a quick overview of the respective commands here.

**This German SQL tutorial is regularly expanded: **Current SQL topic overview

more comprehensive overview of the most important SQL commands and topics for websites:

SELECT

Querying data

WHERE, ORDER BY, GROUP BY, HAVING Functions such as COUNT, SUM, AVG JOIN to combine tables

SQL Tutorial for IRC-Coding.de

SELECT

The SELECT command is used to query data from a database table. SELECT spalte1, spalte2 FROM tabelle;

Here is a brief tutorial on the specified SQL commands and concepts:

WHERE

Filters records based on a condition.

SELECT * FROM Kunden WHERE Stadt = 'Bochum';

A WHERE clause in SQL is used to restrict the result set of a query based on specific conditions. Here are some special considerations and best practices to keep in mind when using a WHERE clause:

Syntax and case sensitivity:

SQL keywords are not case-sensitive, but it is common practice to write them in uppercase to distinguish them from table and column names. String comparisons are case-sensitive in most SQL databases. This means ‘Bochum’ and ‘bochum’ are considered different, unless the database is configured to be case-insensitive.

Quotation marks:

String values in SQL are typically enclosed in single quotes (’ ’). If a string contains a single quotation mark, it must be escaped by doubling it or using an escape mechanism, e.g., ‘O”Brien’ for the name “O’Brien”. NULL values:

The query “WHERE Stadt = ‘Bochum’” returns no rows where the value of the ‘Stadt’ column is NULL. If NULL values should be taken into account, this must be explicitly specified with “IS NULL” or “IS NOT NULL”.

Performance optimization:

Indexes can significantly improve query performance. If the ‘Stadt’ column is frequently used in WHERE clauses, an index on this column can increase query speed. Using functions on columns in the WHERE clause can override indexes.

For example: “WHERE UPPER(Stadt) = ‘BOCHUM’” prevents the use of an index on the ‘Stadt’ column.

Multiple conditions: You can combine multiple conditions with AND and OR. The order is important and should be clarified with parentheses in complex queries, e.g., “WHERE Stadt = ‘Bochum’ AND (Alter > 30 OR Beruf = ‘Ingenieur’)”.

Wildcard search: LIKE can be used to perform pattern matching. The underscore (_) stands for a single character and the percent sign (%) for any number of characters, e.g., “WHERE Stadt LIKE ‘Boc%’”.

Security: Avoid SQL injection attacks by using parameter binding, especially with user input. This is especially important for dynamic SQL queries. An example of a secure and optimized query might look like this:

— Example of a secure query with parameter binding ” SELECT * FROM Kunden WHERE Stadt = ?; ”

Here a placeholder (?) is used, which is later replaced by a secure method (e.g. Prepared Statements) with the actual value.

ORDER BY

Sorts the results by one or more columns. SELECT * FROM Produkte ORDER BY Preis DESC;

It is possible to use the position of columns in the SELECT list for sorting:

” SELECT Name, Alter FROM Kunden ORDER BY 2 DESC; ”

sorts by the second column (Alter) in descending order. This can reduce readability and is often not recommended.

NULL values:

NULL values are treated specially in the sort order. In some SQL databases they appear first, in others at the end, depending on whether sorting is ascending or descending.

Many SQL dialects offer options to sort NULL values explicitly, e.g. ORDER BY Spalte ASC NULLS LAST.

Performance optimization:

Sorting can have significant impact on performance, especially with large amounts of data. Indexes on the sort columns can improve performance. Avoid using functions on columns in the ORDER BY clause, as this can override indexes, e.g., ORDER BY UPPER(Name).

Combination of ORDER BY and other clauses:

ORDER BY can be used in combination with LIMIT or FETCH FIRST to deliver paginated results:

” SELECT * FROM Kunden ORDER BY Name LIMIT 10 OFFSET 20;. ”

**It is important to note that an ORDER BY clause typically appears at the end of the SQL query, after WHERE, GROUP BY, and HAVING. Sorting by calculated columns:

You can also sort by calculated columns defined in the SELECT list: SELECT Name, (Gehalt * 1.1) AS NeuesGehalt FROM Mitarbeiter ORDER BY NeuesGehalt DESC;. Collation:

The sort order can be influenced by collation, which determines how text values are sorted. Different collations can have different sort orders for strings. Sorting in combination with JOINs:

In queries that use JOINs, you can sort by columns from any of the participating tables:

SELECT k.Name, o.Bestelldatum FROM Kunden k JOIN Bestellungen o ON k.KundenID = o.KundenID ORDER BY o.Bestelldatum;.

Example of a query with ORDER BY:

SELECT Name, Stadt, Alter FROM Kunden ORDER BY Stadt ASC, Alter DESC;

This query sorts the results first by city in ascending order and within each city by age in descending order.

GROUP BY

Groups rows with common values in a column.

SELECT Kategorie, SUM(Verkaufspreis) FROM Bestellungen GROUP BY Kategorie;

HAVING

Filters groups similar to WHERE, but for aggregated values.

SELECT Kategorie, SUM(Verkaufspreis) FROM Bestellungen The HAVING clause in SQL is used to set conditions for group results produced by the GROUP BY clause. Here are some special considerations and best practices to keep in mind when using the HAVING clause:

Difference between WHERE and HAVING:

The WHERE clause filters rows before grouping, while the HAVING clause filters after grouping.

Example: WHERE is used to filter rows based on individual row values before aggregation takes place. HAVING is used to filter aggregated values.

Usage with aggregate functions:

HAVING is often used in conjunction with aggregate functions such as COUNT, SUM, AVG, MAX and MIN. Example: ” SELECT Stadt, COUNT() AS Kundenanzahl FROM Kunden GROUP BY Stadt HAVING COUNT() > 5;

”

This query counts the number of customers in each city and displays only cities with more than 5 customers. Syntax:

**The HAVING clause always follows the GROUP BY clause and comes after it in the query. **The correct order of clauses in a query is:

_SELECT, FROM, [WHERE], GROUP BY, HAVING, [ORDER BY]. _

Multiple conditions:

You can use multiple conditions in a HAVING clause by combining AND and OR, similar to the WHERE clause. Example:

SELECT Stadt, AVG(Alter) AS Durchschnittsalter FROM Kunden GROUP BY Stadt HAVING AVG(Alter) > 30 AND COUNT(*) > 10;

This query displays only cities where the average age of customers is over 30 years and the number of customers exceeds 10. Performance optimization:

As with the WHERE clause, using HAVING can also impact performance, especially with large amounts of data. Efficient use of indexes and avoiding unnecessary calculations can improve performance. It is often more efficient to place conditions that are based on individual rows in the WHERE clause and only place conditions based on aggregates in the HAVING clause.

Example query with HAVING:

**SELECT Stadt, COUNT() AS Kundenanzahl, AVG(Alter) AS Durchschnittsalter FROM Kunden WHERE Stadt IS NOT NULL GROUP BY Stadt HAVING COUNT() > 5 AND AVG(Alter) > **30;

_This query displays cities with more than 5 customers and an average age over 30. _ In summary:

WHERE filters before aggregation. HAVING filters after aggregation. HAVING is primarily used in conjunction with aggregate functions. The correct placement of conditions between WHERE and HAVING is crucial for query efficiency.

GROUP BY Kategorie

HAVING SUM(Verkaufspreis) > 1000;

Aggregate Functions

COUNT: Counts rows SUM: Sum of values AVG: Average of values

“SELECT COUNT(*) AS NumberOfCustomers FROM Customers;

SELECT AVG(Price) AS AveragePrice FROM Products; “

Aggregate functions are special functions in SQL that perform calculations over multiple rows of a table or filtered result set. They consolidate the values of the specified column into a single value. The most important aggregate functions are:

COUNT Counts the number of rows in a group.

SELECT COUNT(*) AS NumberOfCustomers FROM Customers;

SUM Calculates the sum of the values in a specified column.

SELECT SUM(Price) AS TotalRevenue FROM Orders;

AVG Calculates the average value of the values in a column.

sqlCopy codeSELECT AVG(Age) AS AverageAge FROM Users;

MAX Returns the largest value in a column.

SELECT MAX(Salary) AS HighestSalary FROM Employees;

MIN Returns the smallest value in a column.

SELECT MIN(Price) AS CheapestProduct FROM Products;

Aggregate functions are often used in combination with the GROUP BY clause to perform calculations for groups of rows that have the same value in one or more columns. They are useful for extracting summarized information from data, e.g. total revenue, average values, maximum and minimum values.

JOIN

Links rows from two tables based on matching values.

“ SELECT Orders.OrderNumber, Customers.Name, Products.Description FROM Orders

JOIN Customers ON Orders.CustomerID = Customers.CustomerID

JOIN Products ON Orders.ProductID = Products.ProductID; “

These are the most important commands for filtering, grouping, aggregating and linking data in SQL for use on websites. **

INSERT, UPDATE, DELETE

Adding, Modifying and Deleting Records

CREATE, ALTER, DROP

Tutorial: INSERT, UPDATE and DELETE in SQL

SQL (Structured Query Language) is the standard language for database administration. With the commands INSERT, UPDATE and DELETE you can add, modify and delete records in a database.

Here is a short tutorial on this:

INSERT INTO - Add a New Record

The INSERT INTO command inserts a new record into a database table. INSERT INTO TableName (Column1, Column2, …) VALUES (Value1, Value2, …);

INSERT INTO SQL - Example: INSERT INTO Customers (Name, Address, City) VALUES ('John Smith', 'Main Street 1', 'Sample City');

UPDATE - Modify a Record With UPDATE you can modify one or more records based on a condition.

UPDATE TableName SET Column1 = Value1, Column2 = Value2, … WHERE Condition;

SQL Update Example: UPDATE Customers SET City = 'Berlin' WHERE CustomerID = 22;

DELETE FROM - Delete a Record

The DELETE FROM command deletes one or more records from a table.

DELETE FROM TableName WHERE Condition;

DELETE FROM - Example: “

DELETE FROM Orders WHERE OrderDate < ‘2022-01-01’;

“ With these three SQL commands you maintain full control over the records in your database tables - whether for inserting, modifying or deleting data for your website.

Creating, Modifying and Deleting Tables

Data Types, Constraints, Indexes

Here is a SQL tutorial on creating, modifying and deleting tables as well as data types, constraints and indexes:

CREATE TABLE - Create a Table

CREATE TABLE TableName ( Column1 DataType Constraint, Column2 DataType, Column3 DataType, ... CONSTRAINT ConstraintName ConstraintType (Column) );

Data types determine the type and format of data stored in a column, e.g. INT for integers or VARCHAR for text. Constraints like PRIMARY KEY, FOREIGN KEY, UNIQUE or NOT NULL enforce data integrity.

I’m trying to explain the SQL CONSTRAINT command for tables in SQL as simply as possible: A CONSTRAINT (restriction) is a rule that you set for a column or table in the database. These rules enforce that only specific, authorized data can be entered into the table. There are different types of constraints, for example: PRIMARY KEY - Specifies that each record in this column must have a unique value, e.g. a customer number. Two customers cannot have the same number. FOREIGN KEY - Links data between two tables, e.g. a customer’s orders with their contact information. NOT NULL - Specifies that this column must always have a value and cannot remain empty. UNIQUE - Similar to PRIMARY KEY, but values in this column cannot be duplicated. CHECK - You set a value range, e.g. that age must be between 18 and 99. The constraints help you prevent incorrect, contradictory or duplicate data from entering the database. They serve as quality control for your data.

ALTER TABLE - Modify a Table

ALTER TABLE TableName ADD Column DataType, DROP COLUMN Column, ALTER COLUMN Column DataType;

With ALTER TABLE, columns can be added, deleted or modified.

DROP TABLE - Delete a Table

DROP TABLE TableName;

Deletes the entire SQL table including all data.

SQL Constraints

PRIMARY KEY (Column) - Unique key per row FOREIGN KEY (Column) REFERENCES OtherTable(PrimaryKey) - Link UNIQUE (Column) - Unique values in column NOT NULL - No NULL values in column CHECK (Condition) - Restrict value range Constraints ensure the integrity and consistency of data. Indexes

CREATE INDEX IndexName ON TableName (Column);

Indexes speed up the search for records based on the indexed column(s). With these statements you can manage tables, define data types and constraints, and create indexes for performance.

Database Model

Normalization for Data Integrity

Relationships: One-to-One, One-to-Many, Many-to-Many

Here is an explanation and tutorial on database models, normalization and relationships: Database Model and Normalization

A database model describes how data is structured and organized in a database. Through normalization, the logical database structure is optimized to avoid redundancies and integrity issues. Normalization is done by applying rules (first, second, third normal form etc.) to the tables:

Each table cell may contain exactly one value. Each column must have a unique name. Different data must be stored in separate tables.

The goal is to be able to store data free of redundancy, consistently and efficiently. Well-normalized databases minimize anomalies during insert, update and delete operations.

Relationships Between SQL Tables Normalization creates separate tables that are linked via defined relationships. Depending on cardinality, the following are distinguished:

One-to-One (1:1)

One row from table A is assigned to exactly one row from table B. Example: A person has exactly one passport.

One-to-Many (1:N)

One row from table A is assigned to many rows from table B. Example: A customer has many orders.

Many-to-Many (N:M)

Multiple rows from A are assigned to multiple rows from B. Requires a helper table. Example: Students take multiple courses, courses have multiple students. Through these relationships, data can be linked and queried across table boundaries without creating redundancies.

Transactions

COMMIT and ROLLBACK

Isolation levels for consistency

Here is a tutorial on transactions in SQL - COMMIT, ROLLBACK and isolation levels: Transactions A transaction is a sequence of SQL statements that is considered as a logical unit. Either all statements are executed successfully or none. This ensures the atomicity and consistency of the data.

START TRANSACTION; SQL statement 1; SQL statement 2; ... COMMIT

COMMIT makes the changes through the SQL statements within a transaction persistent in the database.

COMMIT;

All changes since START TRANSACTION are written. After that, the transaction is completed.

ROLLBACK

ROLLBACK cancels all changes within a transaction.

ROLLBACK; The database is reset to its original state before START TRANSACTION. Isolation levels They determine how concurrent transactions can see and affect data:

• READ UNCOMMITTED - Dirty reads possible, no consistency
• READ COMMITTED - Only committed data readable, no phantom problems
• REPEATABLE READ - Read locks, no lost updates
• SERIALIZABLE - Write locks, complete transaction isolation

The higher the level, the better the data consistency and isolation, but more overhead. Transactions with COMMIT/ROLLBACK and defined isolation level are the foundation for reliable and correct operations in a database. They prevent damage from concurrent access.

Security

Users, Roles, Permissions

Prevent SQL injection

Here is a tutorial on security aspects in SQL - users, roles, permissions and prevention of SQL injections:

Users and Permissions

In SQL databases, separate user accounts can be created. Each user is assigned only the necessary permissions (need-to-know principle): Create new user

CREATE User 'schutzgeist'@'localhost' IDENTIFIED BY 'passwort123';

— Grant permission to read a table

GRANT SELECT ON mitarbeiter TO 'schutzgeist'@'localhost';

— Grant permission to write/modify in a table

“ GRANT INSERT, UPDATE ON bestellungen TO ‘schutzgeist’@‘localhost’;

“

— All rights to a database GRANT ALL PRIVILEGES ON firma.* TO 'schutzgeist'@'localhost';

SQL Database Roles

Instead of assigning permissions individually per user, roles can be defined and users added to these roles: Create new role

CREATE ROLE 'ServicesAdmin';

Role receives read permission GRANT SELECT ON finanzen.* TO 'ServicesAdmin';

Add user to role

GRANT 'Servicesadmin' TO 'schutzgeist'@'localhost';

Prevent SQL Injections

SQL injections are attacks where malicious SQL code is injected through unvalidated user input. This can compromise the entire database. To prevent this:

• Never insert user input unvalidated into SQL commands
• Use Prepared Statements or query parameterization
• Grant minimum permissions for applications
• Validate and sanitize inputs (escaping, length limitations)
• Install latest patches for database system

Through restrictive user, role and permission assignment as well as prevention of SQL injections, the security and integrity of a database can be effectively protected.

SQL injections are among the most common and dangerous security vulnerabilities in web applications. In this tutorial, you will learn how to protect your applications against SQL injections. We proceed step by step and explain both the basics and advanced techniques.

Table of Contents - Prevent SQL Injection

What is a SQL Injection?

• How do SQL injections work?
• Preventive measures against SQL injections
• Prepared Statements
• Using Stored Procedures
• Validation and sanitization of input data
• Minimizing the rights of database users
• Using ORM (Object-Relational Mapping)
• Security tools and libraries
• Best practices and further resources

1. What is a SQL Injection?

A SQL injection is a vulnerability where attackers insert malicious SQL code into a query that is sent to a database. This can lead to unauthorized data access, data loss, or data manipulation.

2. How do SQL Injections Work?

SQL injections occur when user input is directly embedded in SQL queries without being properly validated or sanitized. A simple example:

SQL injections are among the most common and dangerous security vulnerabilities in web applications. In this tutorial, you will learn how to protect your applications against SQL injections. We proceed step by step and explain both the basics and advanced techniques.

Table of Contents

• What is a SQL Injection?
• How do SQL injections work?
• Preventive measures against SQL injections
• Prepared Statements
• Using Stored Procedures
• Validation and sanitization of input data
• Minimizing the rights of database users
• Using ORM (Object-Relational Mapping)
• Security tools and libraries
• Best practices and further resources

1. What is a SQL Injection?

A SQL injection is a vulnerability where attackers insert malicious SQL code into a query that is sent to a database. This can lead to unauthorized data access, data loss, or data manipulation.

2. How do SQL Injections Work?

SQL injections occur when user input is directly embedded in SQL queries without being properly validated or sanitized. A simple example:

sql Code kopieren SELECT * FROM users WHERE username = ‘user’ AND password = ‘pass’; An attacker could use the following input:

Username: ’ OR ‘1’=‘1 Password: ” OR ‘1’=‘1 This would result in a query that is always true:

SELECT * FROM users WHERE username = '' OR '1'='1' AND password = '' OR '1'='1';

3. Preventive Measures Against SQL Injections

Prepared Statements Prepared Statements are SQL queries where the structure of the query is compiled in advance and user input is passed as parameters. This prevents input from modifying the SQL code.

Example in PHP with PDO:

$stmt = $pdo->prepare('SELECT * FROM users WHERE username = :username AND password = :password'); $stmt->execute(['username' => $username, 'password' => $password]);

Using Stored Procedures Stored Procedures are stored procedures in the database that can be called. They isolate the SQL code and prevent direct manipulation through user input.

Example in MySQL:

DELIMITER // CREATE PROCEDURE GetUser(IN username VARCHAR(50), IN password VARCHAR(50)) BEGIN SELECT * FROM users WHERE username = username AND password = password; END // DELIMITER ;

Validation and Sanitization of Input Data All inputs should be checked for validity and sanitized. This includes checking for expected data types, length restrictions, and special characters.

Example in PHP: $username = filter_input(INPUT_POST, 'username', FILTER_SANITIZE_STRING); $password = filter_input(INPUT_POST, 'password', FILTER_SANITIZE_STRING);

Minimizing Database User Rights Grant only the minimum necessary rights for database users. This prevents an attacker from causing widespread damage, even if they gain access.

Example:

Create a user with read-only permissions:

CREATE User 'readonly'@'localhost' IDENTIFIED BY 'password'; GRANT SELECT ON mydatabase.* TO 'readonly'@'localhost';

Using ORM (Object-Relational Mapping) ORM libraries abstract database access and provide protection against SQL injections by default. Examples include Hibernate for Java and Entity Framework for .NET.

Example in Python with SQLAlchemy:

user = session.query(User).filter_by(username='user', password='pass').first()

4. Security Tools and Libraries

• SQLMap: An open-source tool for detecting and exploiting SQL injections.
• OWASP ZAP: A security scanner that can detect SQL injections and other vulnerabilities.
• ESAPI: An API from OWASP that provides protection mechanisms against common security vulnerabilities.

5. Best Practices and Further Resources

Code Reviews Conduct regular code reviews to identify security vulnerabilities. Automated Tests Implement tests that specifically target the detection of SQL injections.

Performance

Indexing

Query Optimization

Here is a tutorial on performance aspects in SQL databases - indexing and query optimization: Indexing

Indexes enable faster data access in database tables. They are similar to index registers in books and significantly speed up search operations. **Create index for a column CREATE INDEX index_name ON tabelle (spalte);

** Composite index over multiple columns CREATE INDEX index_name ON tabelle (spalte1, spalte2);

It is best to index columns that are frequently used in WHERE, JOIN or ORDER BY clauses. However, too many indexes can impair write performance.

**Query Optimization

By optimizing SQL queries, the performance of database applications can be significantly improved. General tips:

• Indexing: Use indexes for frequently queried columns
• EXPLAIN: Analyze execution plans with EXPLAIN
• Avoid nested queries: Use JOINs instead of many subqueries
• LIKE patterns start with letters: ‘name%’ instead of ‘%name%’
• LIMIT and pagination: Never select all rows with large datasets

Specific techniques:

• Partitioning: For very large tables, partition by date ranges, etc.
• Indexed views: Query from view instead of complex queries on tables
• Caching: Cache query results, e.g., in Redis
• Replication: Read replica servers for offloading read queries
• Sharding: Distribute database across multiple servers with very large datasets

**Regular monitoring and optimization of slow queries and hot spots is the key to a performant database application. **

Database Administration

Backups, Replication, Clustering

Import/Export

Here is a tutorial on database administration with backups, replication, clustering, and import and export: Backups Regular backups protect against data loss and enable data recovery in case of errors. Full Backup mysqldump --user=root --password --databases datenbank1 datenbank2 > backup.sql

Creates a backup file backup.sql with the complete contents of all databases.

Incremental Backup mysqldump --user=root --password --databases datenbank1 --single-transaction > incrbackup.sql

Only data that has changed since the last backup is backed up. Replication Enables data distribution across multiple systems for high availability and load balancing.

On Master:

CHANGE MASTER TO MASTER_LOG_FILE='log_file', MASTER_LOG_POS=log_position;

** On Slave:** SLAVE START;

Clustering Multiple servers form a cluster for performance and load distribution. Combines master and slave into a synchronized high availability setup.

NDB Cluster (MySQL) SQL-Node: Route requests

mysqld --ndb-cluster --ndb-connectstring=192.168.0.1

Set up data and management nodes Import/Export Import data from backup file

mysql --user=root --password datenbank < backup.sql

Import data from CSV file

LOAD DATA INFILE '/tmp/daten.csv' INTO TABLE tabelle FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

Export data to CSV SELECT * FROM tabelle INTO OUTFILE '/tmp/daten.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

These commands and processes are central to the management, backup, distribution, and data exchange of databases. Careful administration is essential for performance, availability, and data protection.

---

Recommended Reading: Databases