Database 101

introduction to Databases...

 

Basics

• Developed in the 1970s

• initially, Structured English Query Language (SEQUEL)

• Database Management System (DBMS)

• Structured Query Language (SQL)

 

Relational Databases (RDBMS)

SQL

Schema

Structured Data

• PostgreSQL
• Oracle DB
• MySQL
• IBM DB2
• Microsoft SQL Server
• Apache Derby
• MariaDB
• SQLite
• HyperSQL
• TERADATA

 

Non-Relational Databases

NoSQL

Non-Structured Data

• Redis
• Apache CouchDB, "Time to relax"
• Apache HBASE
• HYPERTABLE
• Cassandra
• riak
• MongoDB, Document Oriented - (MongoDB Query Language)

 

SQL Data Types

• Depending on different databases, some data type might not work in one while working flawlessly in another );
• Data types often could be database specific!

Numeric

• bit
• tinyint
• smallint
• int
• bigint
• decimal
• numeric
• float
• real

Date and Time

• date
• time
• datetime
• timestamp
• year

Character and String

• char
• varchar
• text

Binary (Not supported in MySQL)

• binary
• image

Miscellaneous

• json
• xml

 

Getting started with PostgreSQL (psql)

• Windows w/ WSL
• DBeaver as DBMS

installation w/ winget

winget install dbeaver.dbeaver

APT

sudo apt install postgresql

Start postgresql

sudo systemctl start postgresql

• stop
• status
• restart

Log in as root: postgres

sudo su - postgres

psql -U postgres

Create a db

createdb test

Alter db

• user/password/etc.

ALTER USER username WITH PASSWORD 'new_password';

New db user

CREATE USER starboy WITH PASSWORD 'helloworld...lol';

Log In

psql -h localhost -U postgres -d test

Create a new db

CREATE DATABASE test;

List available dbs

\list

\l

List all users

\du

Clear

\! clear

Yes, there's a space before clear

 

Create a table

CREATE TABLE table_name (column_1 datatype, column_2 datatype, column_3 datatype);

Tip: You can do this too.)

create table users (name text, age smallint, birthday date);

• Semicolon ( ; ) at the end of the syntax is Important!
• CTRL+C

List all tables

\d

Insert into a table

INSERT INTO table_name(column_1, column_2, column_3) VALUES (value_1, value_2, value_3);

insert into users(name, age, birthday) values ('Sam', 3000, '2049-12-31');

Select from a table

SELECT name, age, birthday FROM users;

 

Frequently used commands

select * from table_name;

• Wildcard ( * )

Alter table

ALTER TABLE table_name ADD column_name datatype;

Update value w/ conditions

UPDATE table_name SET column_name = value WHERE column_identifier = column_value;

AND | OR

update users set score=0 where name='Sam' or name='Starboy';

Filter data

• case-sensitive
• % = whatever*

SELECT * FROM table_name WHERE column_name LIKE 'A%';

vice-versa

select * from users where name like '%a';

Sorting through data

• Descending order

SELECT * FROM table_name ORDER BY column_name DESC;

vice-versa (Ascending Order.)

select * from users order by score asc;

 

SQL functions ()

• AVG()

SELECT AVG(column_name) FROM table_name;

• SUM()

SELECT SUM(column_name) FROM table_name;

• COUNT()

SELECT COUNT(column_name) FROM table_name;

 

Join tables

• auto-incrementing IDs

CREATE TABLE login(
  ID serial NOT NULL PRIMARY KEY,
  secret VARCHAR (100) NOT NULL,
  name text UNIQUE NOT NULL
);

Insert into tables

INSERT INTO login (secret,name) VALUES ('xyz', 'Sam');

Read table

 select * from login;

Joining users w/ login

The real power of Relational Databases.

• Schemas
• Separation of concerns*

SELECT * FROM table_1 JOIN table_2 ON table_1.identifier = table_2.identifier;

Here, Sam is a foreign key in login and in users, Sam is a primary key

select * from users join login on users.name = login.name;

 

Delete from table

DELETE FROM table_name WHERE column='Something';

 

Drop Table !

Note: Be Careful with this!

DROP TABLE table_name;

 

Tip: Don't play with prod environments!

 

Drop Database !

Note: Be Extra Careful with this !

DROP DATABASE database_name;

You can't drop the db you're currently logged in!

List available dbs

• \list

\l

Create a new database prod

CREATE DATABASE prod;

Give database a structure: users

CREATE TABLE users (
  id serial PRIMARY KEY,
  name VARCHAR(100),
  email text UNIQUE NOT NULL,
  entries BIGINT DEFAULT 0,
  joined TIMESTAMP NOT NULL
);

Verify Creation

\d

Database: login

CREATE TABLE login (
  id serial PRIMARY KEY,
  hash varchar(100) NOT NULL,
  email text UNIQUE not null
);

Verify.)

select * from login;

Clear screen

\! clear

 

PostgreSQL Info

psql --version

psql -V

 

Help!?

psql --help

man psql

 

Here's something...

 

Until Next Time...✌️