Introduction to SQL
Structured Query Language (SQL) is a standardized programming language that is used to manage relational databases and perform various operations on the data in them. A relational database organizes data into rows and columns, which collectively form a table. Data is typically structured across multiple tables, which can be joined together via a primary key or a foreign key. These special keys demonstrate the different relationships that exist between tables.
SQL operates as a programming language by providing commands and a precise syntax to execute a variety of operations.
SQL commands can be categorized into various types, including:
Data Definition Language (DDL) Commands: These commands are used for defining data tables. They create, alter, or remove database structures. They're like the architects of the database.
Data Manipulation Language (DML) Commands: These commands deal with the data stored in the tables. They're used to add, modify, or delete data. DML commands are like the workers who maintain the building.
Data Query Language (DQL) Commands: This consists mainly of the SELECT command, which is used to fetch specific data from the tables. It's like asking for information from a database.
Data Control Language (DCL) Commands: These commands manage user access privileges. They grant or revoke permissions for data access. Think of them as security guards controlling who enters the building.
Transaction Control Language (TCL) Commands: These commands handle transactions. They manage changes in data states, such as committing or undoing changes. They're like keeping track of changes made to the building.
Relational Database Management Systems
While a relational database organizes data based off a relational data model, a relational database management system (RDBMS) is a more specific reference to the underlying database software that enables users to maintain it. These programs allow users to create, update, insert, or delete data in the system, and they provide: -Data structure, Multi-user access, Privilege control & Network access.
Examples of popular RDBMS systems include MySQL, PostgreSQL, and IBM DB2. Additionally, a relational database system differs from a basic database management system (DBMS) in that it stores data in tables while a DBMS stores information as files.
MySQL
MySQL is an open-source Relational Database Management System (RDBMS) that enables users to store, manage, and retrieve structured data efficiently. It is widely used for various applications, from small-scale projects to large-scale websites and enterprise-level solutions.
RDBMS and MySQL are often thought to be the same because of MySQL’s popularity. A few big web applications like Facebook, Twitter, YouTube, Google, and Yahoo! all use MySQL for data storage purposes. Even though it was initially created for limited usage, it is now compatible with many important computing platforms like Linux, macOS, Microsoft Windows, and Ubuntu.
MySQL and SQL are distinct entities. MySQL is a popular brand of RDBMS software using a client-server model, written in C and C++. It utilizes the Structured Query Language (SQL) for communication between the client and server. Similarly, PostgreSQL and Microsoft SQL Server are other RDBMS brands that employ SQL as their primary language for database interaction, even though they're implemented differently.
Installing and Starting MySQL
There are various ways to install MySQL on your operating system. Follow this link for guidance on installing and configuring MySQL server on your local machine.
Once you are connected to the MySQL server, a welcome message is displayed and the mysql> prompt appears, which looks like this:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.32 MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
To disconnect from the MySQL server, type QUIT or \q
mysql> QUIT
Note: In this article, we will use MySQL to facilitate the learning of SQL.
MySQL Data Types
Here are some of the common MySQL data types :
| Data Type | Description | Example |
| INT | Integer values | age INT |
| VARCHAR(n) | Variable-length character strings | name VARCHAR(50) |
| CHAR(n) | Fixed-length character strings | country CHAR(3) |
| DATE | Dates (YYYY-MM-DD) | birthdate DATE |
| TIME | Time of day (HH:MM:SS) | log_time TIME |
| DATETIME | Date and time combination | created_at DATETIME |
| FLOAT | Floating-point numbers | temperature FLOAT |
| DECIMAL(m, d) | Fixed-point numbers with decimal precision | price DECIMAL(8,2) |
| BOOLEAN | Boolean values (0 for false, 1 for true) | is_active BOOLEAN |
| ENUM(val1, val2) | Enumeration of possible string values | gender ENUM('M', 'F') |
| TEXT | Variable-length character strings for large amounts | description TEXT |
Basic SQL Queries
SQL is designed for accessing, modifying, and extracting information from relational databases. As a programming language, SQL has commands and a syntax for issuing those commands. These commands, which adhere to SQL syntax, are referred to as SQL queries.
Showing existing databases. Use a SHOW DATABASES statement:
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
Creating a new database. Use a CREATE DATABASE statement:
mysql> CREATE DATABASE library;
Query OK, 1 row affected (0.02 sec)
Check if the database has been created:
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| library |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
Creating a table inside a database. First, pick the database in which you want to create the table with a USE statement:
mysql> USE library
Database changed
The USE statement tells MySQL to use library as the default database for subsequent statements. Next, create a table with a CREATE TABLE statement:
CREATE TABLE books
(
id INT unsigned NOT NULL AUTO_INCREMENT, # Unique ID for the record
title VARCHAR(100) NOT NULL, # Name of the book
author VARCHAR(50) NOT NULL, # Author of the book
genre VARCHAR(30) NOT NULL, # Genre of the book
publication DATE NOT NULL, # Date of book publication
PRIMARY KEY (id) # Make the id the primary key
);
Data types that are used in each column are explained in Data Types. Primary Key Optimization explains the concept of a primary key. What follows a # on each line is a comment, which is ignored by the MySQL client.
Check if the table has been created with a SHOW TABLES statement:
mysql> SHOW TABLES;
+-------------------+
| Tables_in_library |
+-------------------+
| books |
+-------------------+
1 row in set (0.00 sec)
DESCRIBE shows information on all columns of a table:
mysql> DESCRIBE books;
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| title | varchar(100) | NO | | NULL | |
| author | varchar(50) | NO | | NULL | |
| genre | varchar(30) | NO | | NULL | |
| publication | date | NO | | NULL | |
+-------------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
Adding records into a table. Use an INSERT INTO...VALUES statement:
INSERT INTO books (title, author, genre, publication) VALUES
('To Kill a Mockingbird', 'Harper Lee', 'Fiction', '1960-07-11'),
('1984', 'George Orwell', 'Science Fiction', '1949-06-08'),
('Pride and Prejudice', 'Jane Austen', 'Classic', '1813-01-28'),
('The Hobbit', 'J.R.R. Tolkien', 'Fantasy', '1937-09-21');
Retrieving records from a table. Use a SELECT statement, and “*” to match all columns:
mysql> SELECT * FROM books;
+----+-----------------------+----------------+-----------------+-------------+
| id | title | author | genre | publication |
+----+-----------------------+----------------+-----------------+-------------+
| 1 | To Kill a Mockingbird | Harper Lee | Fiction | 1960-07-11 |
| 2 | 1984 | George Orwell | Science Fiction | 1949-06-08 |
| 3 | Pride and Prejudice | Jane Austen | Classic | 1813-01-28 |
| 4 | The Hobbit | J.R.R. Tolkien | Fantasy | 1937-09-21 |
+----+-----------------------+----------------+-----------------+-------------+
4 rows in set (0.00 sec)
To select specific columns and rows by a certain condition using the WHERE clause:
mysql> SELECT title, author FROM books WHERE genre = 'Fantasy';
+------------+----------------+
| title | author |
+------------+----------------+
| The Hobbit | J.R.R. Tolkien |
+------------+----------------+
1 row in set (0.00 sec)
Deleting a record from a table. Use a DELETE statement to delete a record from a table, specifying the criterion for deletion with the WHERE clause:
mysql> DELETE FROM books WHERE genre='Classic';
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM books;
+----+-----------------------+----------------+-----------------+-------------+
| id | title | author | genre | publication |
+----+-----------------------+----------------+-----------------+-------------+
| 1 | To Kill a Mockingbird | Harper Lee | Fiction | 1960-07-11 |
| 2 | 1984 | George Orwell | Science Fiction | 1949-06-08 |
| 4 | The Hobbit | J.R.R. Tolkien | Fantasy | 1937-09-21 |
+----+-----------------------+----------------+-----------------+-------------+
3 rows in set (0.00 sec)
Adding or deleting a column from a table. Use an ALTER TABLE...ADD statement to add a column. You can use, for example, an AFTER clause to specify the location of the new column:
mysql> ALTER TABLE books ADD publication_language VARCHAR(20) AFTER genre;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
In this example, we're adding a new column named 'publication_language' to the 'books' table. Use DESCRIBE to check the result:
mysql> DESCRIBE books;
+----------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+--------------+------+-----+---------+----------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| title | varchar(100) | NO | | NULL | |
| author | varchar(50) | NO | | NULL | |
| genre | varchar(30) | NO | | NULL | |
| publication_language | varchar(20) | YES | | NULL | |
| publication | date | NO | | NULL | |
+----------------------+--------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
SHOW CREATE TABLE shows a CREATE TABLE statement, which provides even more details on the table:
mysql> SHOW CREATE TABLE books\G
*************************** 1. row ***************************
Table: books
Create Table: CREATE TABLE `books` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(100) NOT NULL,
`author` varchar(50) NOT NULL,
`genre` varchar(30) NOT NULL,
`publication_language` varchar(20) DEFAULT NULL,
`publication` date NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
Use ALTER TABLE...DROP to delete a column:
mysql> ALTER TABLE books DROP COLUMN publication_language;
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESCRIBE books;
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| title | varchar(100) | NO | | NULL | |
| author | varchar(50) | NO | | NULL | |
| genre | varchar(30) | NO | | NULL | |
| publication | date | NO | | NULL | |
+-------------+--------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)
UPDATE...SET modifies existing data in a table:
mysql> UPDATE books SET genre = 'Dystopian Fiction' WHERE title = '1984';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM books;
+----+-----------------------+----------------+-------------------+-------------+
| id | title | author | genre | publication |
+----+-----------------------+----------------+-------------------+-------------+
| 1 | To Kill a Mockingbird | Harper Lee | Fiction | 1960-07-11 |
| 2 | 1984 | George Orwell | Dystopian Fiction | 1949-06-08 |
| 4 | The Hobbit | J.R.R. Tolkien | Fantasy | 1937-09-21 |
+----+-----------------------+----------------+-------------------+-------------+
3 rows in set (0.00 sec)
These are a few fundamental SQL queries and commands that as a beginner you should become familiar with. In our next series, we'll dive into more advanced SQL queries commonly employed in database management.
We will explore topics like subqueries, joins, stored procedures, and advanced data manipulation. These advanced queries will enable you to work with more complex data scenarios and become proficient in SQL.
Conclusion
In conclusion, SQL, with its fundamental queries and commands, serves as the cornerstone of database management for beginners. Understanding these basics provides a solid foundation for effectively interacting with and manipulating data.
As we journey into the world of more advanced SQL queries and techniques, we'll unlock the true power of this language, enabling us to tackle complex data scenarios and harness the full potential of relational databases. Stay tuned for our upcoming series, where we explore the depths of SQL's capabilities, opening doors to a world of data possibilities.