Introduction to SQL: Querying and Managing Data with Examples

SQL (Structured Query Language) is a powerful and versatile language that is used to manage and manipulate data in relational databases. In this blog post, we will provide an introduction to SQL, including the basics of querying and managing data, as well as examples of how to set up a MySQL server using Docker. https://www.w3schools.com/mysql is a great resource and provides interactive tutorials.

First build a database:

create database

Next Make a table.

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
);

Let’s take a look at the basics of SQL. SQL is used to create, read, update, and delete data in a relational database. There are several different types of SQL statements, including SELECT, INSERT, UPDATE, and DELETE.

The SELECT statement is used to retrieve data from a database. The basic syntax of a SELECT statement is as follows:

SELECT column1, column2, ...
FROM table_name;

For example, if we have a table called “employees” with columns “id”, “name”, and “age”, we can use the following SQL statement to select all of the data from the “employees” table:

SELECT *
FROM employees;

The INSERT statement is used to add new data to a table. The basic syntax of an INSERT statement is as follows:

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

For example, if we want to insert a new employee into the “employees” table with an “id” of 1, a “name” of “John Doe”, and an “age” of 30, we can use the following SQL statement:

INSERT INTO employees (id, name, age)
VALUES (1, 'John Doe', 30);

The UPDATE statement is used to modify existing data in a table. The basic syntax of an UPDATE statement is as follows:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE some_column = some_value;

For example, if we want to change the age of the employee with an “id” of 1 to 35, we can use the following SQL statement:

UPDATE employees
SET age = 35
WHERE id = 1;

The DELETE statement is used to remove data from a table. The basic syntax of a DELETE statement is as follows:

DELETE FROM table_name
WHERE some_column = some_value;

For example, if we want to delete the employee with an “id” of 1, we can use the following SQL statement:

DELETE FROM employees
WHERE id = 1;

That will at least get you started in SQL, now lets get you a place to work with SQL. Docker will be the easyest way to spin up a SQL server. Docker is a platform that allows you to easily create, deploy, and run applications in containers. Containers are a lightweight and portable way to package and distribute software.

To set up a MySQL server using Docker, you will first need to have Docker installed on your computer. You can download and install Docker from the official website at https://www.docker.com/products/docker-desktop. Once you have Docker installed, you pull the MySQL image from the Docker Hub. The Docker Hub is a central repository for storing and distributing Docker images. You can use the following command to pull the MySQL image:

docker pull mysql

This command will download the latest version of the MySQL image from the Docker Hub.

Once the image is downloaded, you can use the following command to start a new container from the image:

docker run  -p 3307:3306 --name mysqldb -e MYSQL_ROOT_PASSWORD=mypassword -d mysql

This command will start a new container named “mysqldb” with the environment variable MYSQL_ROOT_PASSWORD set to “mypassword” and the container running as daemon. This also forwards the server’s port 3307 to the docker’s 3306. This will be used when trying to connect a program in to work with the data: as opposed to using manual SQL statements for everything.

You can check the container is running with the following command:

docker ps

You can now connect to the MySQL server inside the container by using the following command:

docker exec -it mysqldb mysql -u root -p

This command will open a MySQL shell and prompt you for the password, you should type the password you set before “mypassword”

You can now start running queries and managing your database as you would do in a local MySQL server.

You can stop the container with the following command:

docker stop mysqldb

You can start it again with:

docker start mysqldb

And remove it with:

docker rm mysqldb

Keep in mind that in this example the data is not persisted, meaning that if you remove the container you will lose all the data in the database. If you want to keep the data even if you remove the container you can use the -v flag to map a host folder to the container’s data folder, for example

docker run -p 3307:3306 --name mysqldb -v /host/folder:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=mypassword -d mysql

In this way, you are able to set up a MySQL server in Docker quickly and easily. This can be especially useful for development and testing, as you can easily spin up and tear down a MySQL server as needed.