Introduction

to

Databases and SQL

Why?

  • we need a persistent memory (not only when the program is not running)
  • we need "unlimited" size of this memory
  • we need a protocol to manage this information
def create_an_order():
    with open('users_database.csv', 'w+'):
        ...

    with open('orders_database.csv', 'w+'):
        ...

Why not just:

# users_database.csv
username, password, age
Alex, strongstrongpass123, 23
Bogi, nosostrongpass, 21
...
# orders_database.csv
order_no, client, price
1234, Alex, 20.00
...

🤔

  • What format should we use - JSON / CSV / ... ?
  • Should we read the whole database every time to get 1 result. What about if it's 100GB?
  • What if we update something ? Read and write the whole database over and over again ?
  • What if we need an encryption ?

DB vs DBMS

Databases:
Relational

vs

Non-relational

 

Relational

  • used since 1970s
  • unified language - SQL
  • defined structures - tables and relations
  • defined protocol

Non-Relational

  • No-SQL
  • used since 2000s 
  • "freedom" with the structure
  • horizonal scaling
  • but still.. it has it's problems...

Relational DB

  • Language - SQL (Structured Query Language / sequel) 
  • The data is put into Tables
  • Each table has column with defined type
  • Each record in the database is a row in a table

Attributes

1 row == 1 record == 1 object

DB

"A database is an organized collection of data, generally stored and accessed electronically from .... "


At the end it's just a file in the filesystem :)

(R)DBMS

The software we use to interact with the database.
Examples:

  • PosgreSQL
  • MySQL
  • SQLite

Colunm data types

  • TEXT
  • VARCHAR
  • NUMERIC
  • INTEGER
  • REAL
  • BLOB
  • others

Primary keys

Natural and "Surogate" (syntetic)

Create a Table

CREATE TABLE users (

    id INTEGER PRIMARY KEY,

    email VARCHAR(255)

);

  • CREATE TABLE + name of the table
  • INTEGER, VARCHAR - types of attributes

Alter a Table

 

ALTER TABLE usres

    RENAME TO users;

 

ALTER TABLE users

    ADD COLUMN is_student BOOLEAN;

 

Drop a Table

DROP TABLE users;

CRUD

CREATE    ==     INSERT

READ        ==     SELECT

UPDATE    ==    UPDATE

DELETE     ==    DELETE

SQL

CRUD - SELECT

SELECT language

FROM languages

WHERE answer="200 OK";

  • FROM - the name of the table, you want to select from
  • SELECT - the names of the attributes you want to select
  • WHERE - the SELECT clause filters the table's rows by these conditions
  • ; is required for most RDBMS;

SELECT with AND, OR, NOT

SELECT language

FROM languages

WHERE answer="200 OK" OR answer="NDI="

SELECT language

FROM languages

WHERE answer="200 OK" AND id=2;

SELECT with LIKE

SELECT language
FROM languages
WHERE answer LIKE "%o%";

  • LIKE - it's used as a `regex`

CRUD - INSERT INTO

INSERT INTO language

VALUES (9, "PHP", "$$$", 0, "WHY?");

  • INSERT INTO - the name of the table we want to insert the values
  • VALUES - set a value for every attribute of the table 

CRUD - UPDATE

UPDATE languages
SET answer = 8
WHERE language = "Python";

  • UPDATE - the name of the table we want to update
  • SET - set values to all attributes we want to update
  • WHERE - we need to SELECT the rows before updating them so we need this condition too

CRUD - DELETE

DELETE FROM languages
WHERE language = "PHP";

  • DELETE FROM - the name of the table
  • WHERE - we need to SELECT the rows before deleting them so we need the WHERE conditions

Task 1 🐼

Foreign Key

A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table.

 

 

In other words: A way to make a relation between tables.

Task 2 !

Python 101 9th DB & SQL Intro

By Hack Bulgaria

Python 101 9th DB & SQL Intro

  • 960