MySQL tutorial - 1 Introduction
Tags: mysql, beginners, tutorial
Published: 2021-03-02 01:10:20

SQL is a query language that used in SQL database like MySQL, PostgreSQL and other databases. SQL is a query language that has two main types:

- Data Definition Language (DDL): This SQL type is related to data definition, the example of DDL is create a new database or table.
- Data Manipulation Language (DML): This SQL type is related to data manipulation, the example of DML is to add, edit or delete certain data inside databse.

## Setup

There are many ways to configure a SQL database inside local machine. One of those is using local server that included SQL database called XAMPP that can be downloaded [here](https://www.apachefriends.org/index.html). The XAMPP is already included MySQL database so the MySQL installation isn't needed.

## DDL Query

### Create a new database

> SQL syntax is case insensitive, in this article, the SQL syntax is used in MySQL.

To create a new database, use `CREATE DATABASE` followed with database name. In this example, the database called `mydb` is created.

```sql
CREATE DATABASE mydb
```

After database is created, then use the database with `USE` syntax followed with database that will be used.

```sql
USE mydb
```

### Create a new table

Before create a new table, make sure that the created database is already selected by `USE` syntax.
To create a new table, use the `CREATE TABLE` followed with table name and the columns name.

```
CREATE TABLE table_name(
    column_name data_type constraints,
    column_name data_type constraints
)
```

This is the example of creating table with SQL, the table name is `shop` and consists of column including id, product_name, quantity and price.

```sql
CREATE TABLE shop(
    id INT PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    quantity INT NOT NULL,
    price FLOAT NOT NULL
)
```

Notice that there are some data types that used while creating table, the data types are `INT` represents integer, `VARCHAR` represents characters that contains number and alphabetical characters and `FLOAT` represents float number.

> Data type that supported by SQL can be checked [here](https://www.w3schools.com/sql/sql_datatypes.asp)

The constraints that used in this table are `PRIMARY KEY` and `NOT NULL`. `PRIMARY KEY` is used in column called `id` to identify the data in `shop` table. `NOT NULL` is used in column called `product_name`,`quantity` and `price` to make sure that the column isn't allowed to be empty or null.

> Constraints that available in SQL can be checked [here](https://www.w3schools.com/sql/sql_constraints.asp)

### Edit table structure

To add new column inside table use this query.

```
ALTER TABLE table_name
ADD column_name data_type constraints
```

In this example, the new column called `tags` is added in `shop` table.

```sql
ALTER TABLE shop
ADD tags VARCHAR(225) NOT NULL
```

To edit the certain column, use this query.

```
ALTER TABLE table_name
MODIFY COLUMN column_name data_type constraints
```

In this example, the column called `tags` is edited.

```sql
ALTER TABLE shop
MODIFY COLUMN tags VARCHAR(25) NOT NULL
```

To remove the certain column, use this query.

```
ALTER TABLE table_name
DROP COLUMN column_name
```

In this example, the column called `tags` is removed.

```sql
ALTER TABLE shop
DROP COLUMN tags
```

### Adding reference

In SQL, each table can be connected each other using reference. In order to create a reference into another table, the foreign key is needed in certain table. the foreign key is indicated with `REFERENCES` syntax. In this example the table `cart` has reference into `shop` table with `product_id` act as foreign key that has reference into `id` in `shop` table.

```sql
CREATE TABLE cart(
    id INT PRIMARY KEY,
    product_id INT,
    FOREIGN KEY (product_id) REFERENCES shop(id)
)
```

This is the illustration of relation between `shop` and `cart` table.

![Table reference illustration](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fbhb0scn4fk1vmf2pjfh.png)

### Remove table or database

To remove table from certain database use `DROP TABLE` followed with table name.

```sql
DROP TABLE shop
```

To remove database use `DROP DATABASE` followed with database name.

```sql
DROP DATABASE mydb
```

## Notes

Multiple queries can be executed with delimiter (`;`)

```sql
-- comment is also available in SQL by using '--' notation
CREATE TABLE cars(
    id INT PRIMARY KEY,
    car_name TEXT UNIQUE NOT NULL,
    price FLOAT NOT NULL,
);

CREATE TABLE motorcycles(
    id INT PRIMARY KEY,
    motor_name TEXT UNIQUE NOT NULL,
    price FLOAT NOT NULL,
);
```

I hope this article is helpful for learning SQL, If you have any thoughts or comments you can write in the discussion section below.