SQL

Overview

Create Table

CREATE TABLE student_info(
	id VARCHAR(100) NOT NULL,
	// allow Chinese Character
	name VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
	age int NOT NULL,
	class VARCHAR(100),
	PRIMARY KEY (id)
) ENGINE = InnoDB DEFAULT charset utf8mb4 COLLATE utf8mb4_unicode_ci;

• Engine : define storage engines to decide how and where the table data is stored (Default = InnoDB)

• Charset: define the character Sets to encode the characters (Default = utf8mb4)

• Collate: define how to the field do comparison or ordering (ORDER BY, WHERE will be affected)

Key

• key means a constraint imposed on the behaviour of the column

• Primary Key(PK): a unique key that represent the row (usually a random number) and include unique and non-null constraint and primary index

• Foreign Key(FK): a key that referencing the primary of other table in order to make sure the data in tables are valid and consistent

• Composite Primary Key: combine 2 columns into a primary key

Constraint

Introduction

• Constraints are the rules that we can apply on the type of data in a table. That is, we can specify the limit on the type of data that can be stored in a particular column in a table using constraints.

Type

• NOT NULL: This constraint tells that we cannot store a null value in a column. That is, if a column is specified as NOT NULL then we will not be able to store null in this particular column any more.

CREATE TABLE Student
(
ID int(6) NOT NULL,
NAME varchar(10) NOT NULL,
ADDRESS varchar(20)
);

• UNIQUE: This constraint when specified with a column, tells that all the values in the column must be unique. That is, the values in any row of a column must not be repeated.

CREATE TABLE Student
(
ID int(6) NOT NULL UNIQUE,
NAME varchar(10),
ADDRESS varchar(20)
);

• CHECK: This constraint helps to validate the values of a column to meet a particular condition. That is, it helps to ensure that the value stored in a column meets a specific condition.

CREATE TABLE Student
(
ID int(6) NOT NULL,
NAME varchar(10) NOT NULL,
AGE int NOT NULL CHECK (AGE >= 18)
);

Date

Introduction

• Date can be used to search for the range of data

• If a String is entered into a column where its type is a date/ datetime, its format must be 'Year-Month-Day' or 'Year/Month/Day'

Method

Date Format

• Structure : Date_Format(date, format) and output as a string

DATE_FORMAT(curdate(), '%d/%m/%Y') 

String to Date

• Convert the String to date, Structure: STR_TO_DATE(input, the input format)

Str_to_date('5/7/2020','%e/%e/%Y') 
// output : 2020-07-05

Date Add/Min

SELECT "2017-06-15" + INTERVAL 10 DAY;  // 2017-06-25
SELECT "2017-06-15" - INTERVAL 10 DAY;  // 2017-06-05

Date Diff

SELECT datediff('2015-06-04','2015-07-04') // -30
SELECT datediff('2015-06-04','2015-05-04') // 31

Extract

• Obtain the part of date as a string or number

EXTRACT(YEAR_MONTH FROM '2015-7-15') // 201507
SELECT MONTHNAME("2017-06-15"); // June

Get the day of week

SELECT DAYOFWEEK("2017-06-15"); // 5 which means Thursday
SELECT DayName("2017-06-15"); // Thursday

String

Method

Insert

SELECT insert("hitest", 3, 0, "test");    // hitesttest
SELECT insert("hitest", 3, 1, "example"); // hiexampleest
SELECT insert("hitest", 3, 9, "example"); // hiexample

Concat

SELECT CONCAT("SQL ", "Tutorial ", "is ", "fun!"); // SQL Tutorial is fun
SELECT CONCAT_WS("-", "SQL", "Tutorial", "is", "fun!") // SQL-Tutorial-is-fun!

Sub String

SELECT left("hitest", 2); // hi
SELECT right("hitest",2); // st
SELECT SUBSTR("test test", 6, 4); //test

TRIM

SELECT LTRIM(" test "); //test_
SELECT RTRIM(" test "); //_test
SELECT TRIM(" test "); //test

String Comparison

SELECT strcmp("test", "test"); // 0
SELECT strcmp("test123", "test"); // 1
SELECT strcmp("test", "test123"); // -1

Number

Method

ABS

SELECT ABS(-1) // 1
SELECT ABS(1)  // 1

Round

SELECT Ceil(25.25) // 26
SELECT Floor(25.75) // 25
SELECT ROUND(135.775, 0) // 136
SELECT ROUND(135.775, 2) //135.78

+/-

SELECT 25-2 // 23
SELECT 25+3 // 28

Random Number

SELECT Rand() 

Case Study

Case 1

Select * FROM Estate AS e 
JOIN District d ON e.district = d.pk 
GROUP BY d.pk 
ORDER BY 
Case 
    WHEN convert (d.name using big5) LIKE CONCAT(Char(63),'%') then 1 else 0 end
, d.willDelivery DESC;    

Case 2

INSERT INTO "PermissionGroupFeature" ("groupTypes", "featureName", "featureKey", "isBasic") 
SELECT ARRAY['BOT', 'FLOW', 'INSIGHT']::"GroupType"[], 'Audit Log' , 'audit-log', false WHERE NOT EXISTS
(SELECT 1 FROM "PermissionGroupFeature" WHERE "featureKey" = 'audit-log');

References

DBMS Normalization: 1NF, 2NF, 3NF Database ExampleGuru99

An in-depth look at Database IndexingfreeCodeCamp.org