MySQL tutorial - 1 Introduction

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. 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.

CREATE DATABASE mydb

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

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.

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

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

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.

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.

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.

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.

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.

Remove table or database

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

DROP TABLE shop

To remove database use DROP DATABASE followed with database name.

DROP DATABASE mydb

Notes

Multiple queries can be executed with delimiter (;)

-- 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.