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.

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.

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)

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

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

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