Introduction

Data refers to facts related to any object in consideration. E.g Name, Age, Height, Weight, etc.

A database is a systematic collection of data.

Databases use:

• Fields - commonly known as columns.
• Records - commonly known as rows.

Layout and definition of how fields, tables and relationships are set up is called the shema of the database.

Unlike spreadsheets, databases help us ask questions using SQL.

A Database Management System (DBMS) - refers to a collection of programs which enable its users to access a database, manipulate data and help in representation of data. It also helps control access to the database by various users.

Types of DBMS

1. Hierarchical DBMS - Employes the parent-child relationship of storing data. Rarely used nowadays. Has a tree like structure with nodes representing records and branches representing fields. Example: The windows registry used in Windows Xp.
2. Network DBMS - Supports many to many relationships resulting to a complex Database Structure. Example: RdM server.
3. Relational DBMS - Defines database relationships in forms of tables also known as relations. Does not support many to many relationships. Has pre-defined data types it can support. Most popular in the market. Examples: MySQL, Oracle, Microsoft Sql Server.
4. Object Oriented DBMS - Supports storage of new data types. The data to be stored is in the form of objects. Objects to be stored in the database have attributes for example gender or age and methods that define what to do with the data. Example: Postgresql.

SQL

Stands for Structured Query Language.

It is the standard language for dealing with relational databases.

It first came to use in 1974 and became a standard in 1986.

It's about writing questions the database can understand and as a result be used to insert, search, update and delete database records.

The language is called SQL, but was originally called SEQUEL (Structure English Query Language).

Anything you write in SQL to make a change or ask a question is called a statement.

SQL is whitespace independent - meaning if you want to add space between expressions or statements to make statements easier to read, you can do so.

A statement is made up of clauses - the smaller building block that makes up the whole.

clauses - are constructed from keywords (SELECT, FROM, WHERE, GROUP BY) which tell the database what to do.

Fieldnames - tell the database where to look and what to look from.

Predicates - let us specify what information we are working with, they include a value or a condition called an expression.

Operators - allow comparisons.

SQL Statements

SQL Statements are divided into two major categories: Data Definition Language (DDL) and Data Manipulation Language (DML).

Data Definition Language

DDL statements are used to build and modify the structure of tables and other objects in the database.

When a DDL statement is executed, it takes effect immediately.

Examples include:

CREATE TABLE

CREATE TABLE table_name (
	attribute_name_1 data_type_1,
	...
	attribute_name_n data_type_n
);

Data types mostly used:

• Varchar or Char - character strings for variable or fixed length strings.
• Numeric types - such as number or integer which specify a precision.
• Date.

ALTER TABLE

May be used to specify primary and foreign key constraints, as well as to make other modifications to the tablel structure. Key constraints may also be specified in the CREATE TABLE statement.

ALTER TABLE table_name 
ADD CONSTRAINT constraint_name PRIMARY KEY (attribute_list);                

SELECT

tells the database we want some information returned to us even if it's not something in the database.

SELECT last_name, first_name FROM people;                        

the way field names are written is the way they are output.

WHERE

Adds conditions to a statement.

SELECT * FROM people WHERE state='CA';

AND

And is a predicate that adds more criteria to return what we want exactly.

SELECT first_name, last_name, team
FROM people 
WHERE state='CA' AND shirt_or_hat = 'shirt' AND team != 'blue';                        

OR

SELECT first_name, last_name, shirt_or_hat
FROM people
WHERE state = ('CA' OR state = 'CO') AND shirt_or_hat = 'shirt';                        

ORDER BY

sorts data in either ascending (A - Z) or descending order (Z - A).

SELECT fist_name, last_name, state
FROM people
ORDER BY first_name ASC;                        

To sort according to state then according to last name:

SELECT fist_name, last_name, state
FROM people
ORDER BY state ASC, last_name DESC;                        

JOIN

for records that are associated with each other based on a common piece of information.

SELECT people.fist_name, people.last_name, states.division
FROM people
JOIN states ON people.state = states.state_abbrev
WHERE people.first_name LIKE '%j%' AND states.region = 'South';                        

DISTINCT

SELECT DISTINCT(first_name)
FROM people
ORDER BY first_name ASC;

Selects only those without duplicates.

LIKE

used to limit responses.

SELECT fist_name, last_name, state
FROM people
WHERE state LIKE 'c%';

The statement selects all values starting with c.

To select all statements ending with c we would use '%c'

To select all statements with c in the middle somewhere, '%c%'

LIMIT

tell the db to stop returning when a given number is reached.

SELECT fist_name, last_name, state
FROM people
WHERE state LIKE 'c%'
LIMIT 10;                        

OFFSET

tells the db to skip a given number of rows before returning values.

SELECT fist_name, last_name, state
FROM people
WHERE state LIKE 'c%'
LIMIT 10
OFFSET 5;                        

Starts from the 6th record.

COUNT

SELECT COUNT(*)
FROM people
WHERE state = 'CA';                        

Sources

1. pocketbase.io - open source backend database system with realtime database, authentication, file storage and an admin dashboard.

MySQL Basics

Installing MySQL on Fedora 41

First make sure your system is up to date:

sudo dnf update

Setup Yum Repository:

sudo vi /etc/yum.repos.d/mysql-community.repo

Add this content to the file then save and close:

Ensure the gpgkey is correct, otherwise this won't work, you'll have to disable the gpgcheck and set it to 0. The drawback is that this reduces the security of this installation.

[mysql80-community]
name=MySQL 8.0 Community Server
baseurl=http://repo.mysql.com/yum/mysql-8.0-community/fc/$releasever/$basearch/
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

Install the MySQL server:

sudo dnf install mysql-community-server

Start the MySQL server:

sudo systemctl start mysqld

Enable start when the system boots:

sudo systemctl enable mysqld

Check the status of MySQL service:

sudo systemctl status mysqld

Now you need to secure MySQL. First find the temporary MySQL password that was generated.

sudo grep ‘temporay password’ /var/log/mysqld.log

Start the process of securing MySQL and select yes for all the prompts you get.

sudo mysql_secure_installation

You're set to go and can start using MySQL with the newly set password through the command:

mysql -u root -p