SQL Basics: An Introduction to Structured Query Language
Tags: beginners, sql, mysql, webdev
Published: 2023-03-09 11:18:26


SQL Basics: An Introduction to Structured Query Language
========================================================

Structured Query Language (SQL) is the most widely-used and popular tool for working with relational databases. Originally developed in the 1970s, SQL is now considered an essential skill for data analysts, database administrators, and other professionals who work with data. This article provides a brief introduction to SQL basics that will help you get started with this powerful tool.

What is SQL?
------------

As mentioned above, SQL stands for Structured Query Language. It is a domain-specific language used to manage, manipulate, and retrieve data from relational databases. A relational database stores data in tables, which can be related to each other through keys or foreign keys. SQL allows users to interact with these tables and their relationships, making it an incredibly valuable tool for managing large sets of data.

Who will need SQL?
------------------
SQL (Structured Query Language) is widely used by software developers, analysts, data scientists, and database administrators. Anyone who needs to work with large or complex data sets, such as those in financial systems, customer relationship management tools, inventory management software, or data warehousing solutions, may need to use SQL to manage and analyze that data efficiently. Many companies today require expertise in SQL for positions dealing with data analysis or database management.


Key Concepts in SQL
-------------------

Before we dive into how SQL works, it's important to understand some key concepts:

*   Tables: A table is a collection of data organized into rows and columns.
*   Rows: Each row represents a single record in a table.
*   Columns: Each column represents a specific attribute of the records in the table.
*   Keys: A key is an attribute, or combination of attributes, that uniquely identifies a row in a table.
*   Foreign Keys: A foreign key is an attribute in one table that refers to the primary key of another table.

Basic SQL Commands
------------------

Once you're familiar with the key concepts of SQL, you're ready to start writing SQL commands. Here are a few basic SQL commands to get you started:

1.  SELECT: This command is used to extract data from a database. It retrieves one or more columns from a table based on some conditions.

Example: `SELECT column_name FROM table_name;`

2.  INSERT: This command is used to insert new data into a database. It adds a new row to the table with the specified values.

Example: `INSERT INTO table_name (column1, column2, column3) VALUES (value1, value2, value3);`

3.  UPDATE: This command is used to update the existing data in a database. It modifies the values of the selected rows in a table.

Example: `UPDATE table_name SET column_name = new_value WHERE some_column = some_value;`

4.  DELETE: This command is used to delete data from a database. It removes one or more rows from a table based on some conditions.

Example: `DELETE FROM table_name WHERE some_column = some_value;`

5.  CREATE: This command is used to create a new table or a view in a database.

Example: `CREATE TABLE table_name (column1 datatype(length), column2 datatype(length), column3 datatype(length));`

6.  DROP: This command is used to drop an existing table, view, database, or index.

Example: `DROP TABLE table_name;`


# Mysql development environment


## for MacOS
To build a MySQL development environment on macOS, follow these steps:

1.  Install Homebrew: Homebrew is macOS's package manager that makes it easy to install and manage software packages on your system. To install Homebrew, open Terminal and run the following command:

```shell
/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"
```

2.  Install MySQL: Once you have installed Homebrew, you can use it to install MySQL. Run the following command in the Terminal:

```shell
brew install mysql
```

3.  Start MySQL: Once the installation is complete, start the MySQL server by running the following command:

```shell
mysql.server start
```

4.  Secure the Installation: It is recommended that you secure your MySQL installation. Run the following command in the Terminal to do that:

```shell
mysql_secure_installation
```

You will be prompted with several questions about the security of your installation.

5.  Log into MySQL: To log in to MySQL, enter the following command in the Terminal:

```shell
mysql -u root -p
```

You will be prompted for your password. Once you enter the correct password, you will be logged in to MySQL and can begin working with databases and tables.

Note: These are just basic steps to set up a MySQL development environment on MacOs. There may be additional steps depending on the specific requirements of your project or software.


## for Windows OS

https://dev.mysql.com/doc/refman/8.0/en/windows-installation.html

# Example

## CREATE TABLE

### Elementary level
below is an example of how to create the "users" table with columns for "id", "name", "age", and "email".

```SQL
CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  email VARCHAR(100)
);
```

In this example, we are creating a table called "users" with four columns. The "id" column is an integer that serves as the primary key for the table. The "name" column is a variable-length string that can store up to 50 characters. The "age" column is an integer that stores the age of the user. Finally, the "email" column is a variable-length string that can store up to 100 characters and represents the email address associated with the user.

### intermediate level

Example SQL command to create `users` and `posts` tables for a blog-like service:

```SQL
CREATE TABLE users (
  id INT NOT NULL AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(100) NOT NULL,
  password VARCHAR(255) NOT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE posts (
  id INT NOT NULL AUTO_INCREMENT,
  user_id INT NOT NULL,
  title VARCHAR(255) NOT NULL,
  body TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  FOREIGN KEY (user_id) REFERENCES users(id)
);
```

This SQL code will create two tables, one called `users` and another called `posts`, along with the necessary columns for each table. The `users` table includes columns for `id`, `username`, `email`, and `password`, while the `posts` table includes columns for `id`, `user_id`, `title`, `body`, `created_at`, and `updated_at`. The `user_id` field in the `posts` table references the `id` field in the `users` table.

## DROP TABLE
Example of a SQL DROP TABLE implementation in the context of the "users" table:

```sql
DROP TABLE users;
```

This statement would permanently delete the "users" table from your database, along with all data contained within it. It's important to use caution when implementing this command, as there is no way to retrieve the data once the table has been dropped. It's also a good practice to ensure that you have a backup of your data before performing any potentially destructive actions like dropping tables.

## SELECT

You have a table named "users" with columns for "id", "name", "age", and "email". And we want to select all the records from the table where the age column is greater than or equal to 18. We can write the following SQL query:

```SQL
SELECT * FROM users WHERE age >= 18;
```

This will return all records from the "users" table where the age is greater than or equal to 18. The `*` symbol means we want to select all columns from the table. If we only wanted to select specific columns, we could replace `*` with the names of the columns we want (e.g. `SELECT name, email FROM users WHERE age >= 18`).

## SELECT INNER JOIN
example of implementing an SQL SELECT INNER JOIN command to get users and posts at the same time when we have tables of users and posts.

```SQL
SELECT
    users.name,
    posts.title,
    posts.content
FROM
    users
INNER JOIN
    posts
ON
    users.id = posts.user_id;
```

This query will return all users who have written one or more posts, along with the titles and content of those posts. If a user has not written any posts, they will not be included in the results.

## LEFT JOIN
Example of implementing a SQL SELECT LEFT JOIN command to get users and posts at the same time when there is a users and posts table:

Suppose we have two tables named "users" and "posts". We want to retrieve all records from "users" table along with records from "posts" table where user\_id matches. But also, if a user has no post we still want to return their information.

```SQL
SELECT 
  users.id,
  users.name,
  posts.title,
  posts.body
FROM 
  users
LEFT JOIN 
  posts 
    ON users.id = posts.user_id;
```

In this query, `LEFT JOIN` will return all records from the `users` table along with matching records from the `posts` table based on the common column `user_id`. If there are any users with no corresponding post, the result set will contain `NULL` values for the post-related columns.

Note: In `LEFT JOIN`, the entire left-side table (`users`) selected with the related rows matched from right-side table (`posts`). If there are some unmatched records in right side then NULL value will be there in place of columns values from right side.

## RIGHT JOIN
Example of implementing a SQL SELECT RIGHT JOIN command to get users and posts at the same time when there is a users and posts table.

Assume that we have two tables: `users` and `posts`. The `users` table has columns for `id`, `username`, `email`, and `joined_at`, while the `posts` table has columns for `id`, `title`, `content`, and `user_id`.

If we want to retrieve all the rows from the `users` table, as well as any corresponding rows from the `posts` table which match on the `user_id` column, we can use the following SQL query:

```SQL
SELECT *
FROM users
RIGHT JOIN posts
ON users.id = posts.user_id;
```

This would return all rows from the `posts` table, as well as any matching rows from the `users` table, where the `user_id` column in `posts` matches the `id` column in `users`. If there are no matching rows in `users` for a given row in `posts`, the columns from `users` will contain `NULL` values.


## INSERT INFO
Example of SQL INSERT INTO statement to add a new row to the "users" table:

To insert a single record:

```sql
INSERT INTO users (id, name, age, email)
VALUES (1, 'John Smith', 25, 'john@example.com');
```

Note: If you don't specify the id column, then depending on your database configuration, it may generate a new value automatically for the primary key column.

To insert multiple records at once:

```sql
INSERT INTO users (name, age, email) VALUES 
  ('Sarah Johnson', 29, 'sarah@example.com'),
  ('Michael Brown', 32, 'michael@example.com'),
  ('Lisa Davis', 24, 'lisa@example.com');
```

This will insert three new rows into the users table with the specified values.

## UPDATE
Example of SQL UPDATE implementation:

Let's say we want to update the user's name stored in our "users" table for user with id 1. We can achieve that using the following SQL statement:

```SQL
UPDATE users
SET name = 'John'
WHERE id = 1;
```

This will update the user's name to "John" where the id is equal to 1.

Similarly, if we need to update multiple columns then we can use the following syntax:

```SQL
UPDATE users
SET name = 'John', age = 30, email = 'john@example.com'
WHERE id = 1;
```

This will update the columns - "name", "age" and "email" for the user with id 1, as specified in the SET clause of the statement.


## DELETE
Example of an SQL DELETE implementation for the "users" table:

```sql
DELETE FROM users WHERE id = 1;
```

This query will delete a row from the "users" table where the "id" column has a value of 1. If you want to delete all rows from the "users" table, you can simply omit the WHERE clause:

```sql
DELETE FROM users;
```

It's important to be careful when using the DELETE command as it permanently removes data from your table. Always make sure to backup your data and double-check your conditions before executing a DELETE statement.

Conclusion
----------

SQL is an incredibly powerful tool for managing, manipulating, and retrieving data from relational databases. While this article has only scratched the surface of SQL basics, it should provide you with a good foundation to build upon. Whether you're a beginner or an experienced professional, learning SQL will make you more effective at working with data and analysis. Keep experimenting and practicing with SQL commands to improve your skills and become a master of this useful tool.