DEV Community

nadirbasalamah
nadirbasalamah

Posted on

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
Enter fullscreen mode Exit fullscreen mode

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

USE mydb
Enter fullscreen mode Exit fullscreen mode

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
)
Enter fullscreen mode Exit fullscreen mode

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
)
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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

ALTER TABLE shop
ADD tags VARCHAR(225) NOT NULL
Enter fullscreen mode Exit fullscreen mode

To edit the certain column, use this query.

ALTER TABLE table_name
MODIFY COLUMN column_name data_type constraints
Enter fullscreen mode Exit fullscreen mode

In this example, the column called tags is edited.

ALTER TABLE shop
MODIFY COLUMN tags VARCHAR(25) NOT NULL
Enter fullscreen mode Exit fullscreen mode

To remove the certain column, use this query.

ALTER TABLE table_name
DROP COLUMN column_name
Enter fullscreen mode Exit fullscreen mode

In this example, the column called tags is removed.

ALTER TABLE shop
DROP COLUMN tags
Enter fullscreen mode Exit fullscreen mode

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)
)
Enter fullscreen mode Exit fullscreen mode

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

Table reference illustration

Remove table or database

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

DROP TABLE shop
Enter fullscreen mode Exit fullscreen mode

To remove database use DROP DATABASE followed with database name.

DROP DATABASE mydb
Enter fullscreen mode Exit fullscreen mode

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,
);
Enter fullscreen mode Exit fullscreen mode

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

Top comments (0)