Skip to content

Data Bases

Db Learning Sources

Khan Academy

Codecademy

Data Base migration, Event Sourcing concept

Database Migration: Knex vs TypeORM vs Sequelize(part 1)

Database Migration: Knex vs TypeORM vs Sequelize(part 2)

Db Essentials

DBMS - a collection of tools to delete,insert and lookup data(and mamy other actions). Thera are two types:

  1. Relational Database: postgreSQL, oracle, sql etc.
  2. schema: the relation between tables
  3. structure: data stored in many tables with specific information, e.g. a table that stores users' emails, a table that stores users' addresses etc.

  4. communication via: SQL

  5. Non-relational Database(NoSQL): mongoDB etc.

  6. schema: not needed, so it's more flexible
  7. structure: all data stored in one json file, e.g. each user has a file with username, address etc.
  8. communication via:
    • db query languages, e.g. mongoDB query language
    • Object Data Modeling (ODM) library, e.g. Mongoose

SQL

-- BASICS:

-- create table
CREATE TABLE nameofTable (
    id serial NOT NULL PRIMARY KEY -- auto increase for us when insert, don't need to specify on insert
    columnName dataType NOT NULL,
    columnName dataType UNIQUE
    );

-- insert table
INSERT INTO nameofTable(columnName1,columnName2) VALUES (value1, value2);

-- add column
ALTER TABLE nameofTable ADD columnName

-- add value in column
UPDATE nameofTable SET columnName = 10 WHERE columnName = 'aa'

-- get info
SELECT columnName FROM nameofTable
-- same here using `*` (represents 'all')
SELECT * FROM nameofTable


-- CONDITIONAL SELECTION:

-- select name begin with A
SELECT * FROM users WHERE name LIKE 'A%';

-- ending with y
SELECT * FROM users WHERE name LIKE '%y';

-- order the column
SELECT * FROM users ORDER BY score ASC;
SELECT * FROM users ORDER BY score DESC;


-- FUNCTIONS:

-- most common
SELECT AVG(score) FROM users;
SELECT SUM(score) FROM users;
SELECT COUNT(name) FROM users;

-- JOIN
SELECT * FROM users JOIN login ON users.name = login.name;

-- DELETION:

-- delete row
DELETE FROM users WHERE name='Sally';

-- delete table
DROP TABLE users;

Installation Guides

Linuxi Mint

See psql+instalation.docx or psql+instalation.pdf files in this doc folder.
TODO: Transform psql+instalation.docx to .md format for this section.

GUI dbms clients

Free

  • DBeaver - Free multi-platform database tool for developers, database administrators, analysts and all people who need to work with databases.

Supports: MySQL, PostgreSQL, SQLite, Oracle, DB2, SQL Server, Sybase, MS Access, Teradata, Firebird, Apache Hive, Phoenix, Presto, etc.

Supports: MySQL, Postgres, SQLite, SQL Server, MariaDB, etc.

Last update: 2023-01-26