SQL — Create, Update, Delete

In this article. I will first explain some glossary from SQL then guide readers step by step on how to create a database, table, and add a record.


In this article, I will use SQLFiddle to create a table

What is SQL

Structured Query Language (SQL) can be classified into two parts

  1. Data Definition Language (DDL): is responsible for defining database and table including field, data type, length etc.

As you can see the name of SQL comes from Structured Query language. As long as you can understand the rules of writing it, you can use SQL as a powerful tool.


Commands and reserved words should be in caps

wrap up a string with ‘’

wrap up table_name and column_name with ``

Separate the command with a different line

Create a database

CREATE DATABASE [databaseName]
COLLATE [sort rule];
  • unicode: Unicode is a universal character encoding standard that assigns a code to every character and symbol in every language in the world. You can look up on the unicode standard, according to your application’s need, set up with different unicode standard.

Create a table

CREATE TABLE [table_name](
[column1_name] [data_type] [not_null] [default],
[column2_name] [data_type] [not_null] [default],
[column3_name] [data_type] [not_null] [default],
PRIMARY KEY ([column_name])
  1. Name:
  • Table name

2. Data Type:

  • Data type: varchar, text,bitint, smallint, tinyint, float, double, decimal(often used in financial), timestamp(often used in createTine, LastModifiedTime)

3. Primary key: assign the primary key. If you don’t have data that can be assigned as primary key, you can create an id column and use it as primary key. Make for this primary key, every value is unique.

Insert one record, Insert multiple records

INSERT INTO [table_name] ([column_name, column_name...])
VALUES('ABC', 12, 23)



Make sure the column name and value matches its order


Update existing record

Let’s say we want to change the price for Latte from 3 to 3.2

UPDATE [table_name]
SET [value]
WHERE [which record]



Delete record

If we want to delete a record permanently

DELETE FROM [table_name]
WHERE [which record]



Soft delete record

Sometimes you might just want to soft delete a record so you can recover easily, the common way to do that is to add a column called deleted_at

ALTER TABLE [table_name]
ADD [column_name] [type]


SQL Fiddle example




Hello, I am Heidi. I started this medium blog to post all of the note I have when I am learning new things. Hope you find it useful as well:)