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.
Top comments (0)