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
- Data Definition Language (DDL): is responsible for defining database and table including field, data type, length etc.
- 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])
);
- 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
https://webfocusinfocenter.informationbuilders.com/wfappent/TL3s/TL_nls/source/01_nls_7720.htm