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.

Tools

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.

Styling

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]
CHARACTER SET [unicode]
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)

Command

Values

Make sure the column name and value matches its order

Result

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]

Command

Result

Delete record

If we want to delete a record permanently

DELETE FROM [table_name]
WHERE [which record]

Command

Result

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]

Result

SQL Fiddle example

http://sqlfiddle.com/#!9/4ddd49/1

Reference

SQL Tutorial

Overview of the Collate SQL command

https://webfocusinfocenter.informationbuilders.com/wfappent/TL3s/TL_nls/source/01_nls_7720.htm