SQL — Create, Update, Delete

4 min readSep 4, 2021


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.
  2. Data Manipulation Language (DML): is responsible for adding, deleting, updating, looking up data. A more common way to say it is Create, Read, Update, Delete (CRUD)

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.
  • sort rule: COLLATE command followed by a sort rule, with different sort rules, words can be translated differently. It stores the unicode in a different order, when it gets translated, it will pick up the 1st result in that list of characters

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
  • Column name: if it's a foreign key then the naming convention should be tableName_columnName

2. Data Type:

  • Data type: varchar, text,bitint, smallint, tinyint, float, double, decimal(often used in financial), timestamp(often used in createTine, LastModifiedTime)
  • Length: coming along with type, you can put a length to specify the length on the data type to avoid memory waste
  • Auto Increment: it will increase automatically, you don’t need to assign value to it. We often use it on id
  • Zerofill: fill in the empty value with zero
  • NOTNULL: can’t be null value
  • UNSINGED: can’t be negative value

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:)