MySQL-Tutorial

SQL = Structured Query Language

SQL is used to Create, Retrieve, Update, & Delete

Two types of databases:

1. Relational (SQL) - Uses the concept of keys. It has rows & columns i.e. excel spreadsheet.

2. Non-relational (NoSQL) - data is organized in any way other than a table i.e. json files, key value pairs, graph data structures, etc.

This tutorial focuses solely on relational databases.

Database Management Systems (DBMS) - workspaces that allow us to write SQL statements and work with our database(s).

Common DBMS include:

MySQL

Microsoft SQL Server

Oracle

PostgresSQL

Supabase (Uses PostgresSQL under the hood + additional features like authentication, storage, etc.)

In this series we are of course going to focus on MySQL

Follow the instructions in the previous link to install MySQL.

Once installed, you can create a database with:

create database myDB;

Hit the lightning button to execute.

To use that database, either right click "Set as Default Schema" and select or use the following command:

use myDB;

To drop a database:

drop database myDB;

To set a database to read only:

alter datbase myDB read only = 1;

To change it out of read only:

alter datbase myDB read only = 0;

To create a table:

create table employees(
  employee_id INT,
  first_name VARCHAR(insert number of characters),
  last_name VARCHAR(50),
  hourly_pay DECIAML(5, 2),
  hire_date DATE,
);

If you need to select a table:

select * from employees;

To rename a table:

rename table employees to workers;

To alter your table:

alter table employees 
add phone_number varchar(15);

To change a column name:

alter table employees
rename column phone_number to email;

To alter column type:

alter table employees
modify column email varchar(100);

To move a column:

alter table employees
modify email varchar(100)
after last_name;

OR

alter table employees
modify email varchar(100)
first;

To insert rows:

insert into employees
values (1, "Eugene", "Krabs", 25.50, "2023-01-02");

select * from employees;

To select specific columns:

select last_name, first_name from employees;

To select specific data:

select last_name, first_name from employees
where employee_id = 4;

Or:

select last_name, first_name from employees
where hourly_pay >= 15;

To update data:

update employees
set hourly_pay = 10.25
where employee_id = 6;
select * from employees;

To delete data (be sure to add a where clause, otherwise it will delete all of your data):

delete from employees
where employee_id = 6;
select * from employees;

To manually save each entry:

set autocommit = off;

To undo / rollback to the last step:

rollback;

To add in new values:

insert into employees
values(current_date(), current_time(), NOW());

To make a column with a required value, simply add "not null". For example:

create table products(
  product_id INT,
  product_name varchar(25),
  price decimal (4, 2) **not null**
);

To add it to a full table that already exists:

alter table products
modify price decimal(4, 2) not null;

How to check a table/values against a limit:

alter table employees
add constraint chk_hourly_pay check (hourly_pay >= 10.00);

To drop a check:

alter table employees
drop check chk_hourly_pay;

To add a default value to a column:

create table products
  product_id int,
  product_name varchar(25),
  price decimal (4, 2) default 0
);

Or:

alter table products
alter price set default 0;

Primary Keys are unique identifiers.

To set a primary key to a new table:

create table transactions(
  transaction_id int primary key,
  amount decimal(5, 2)
);

To add a primary key to an existing table:

alter table transactions
add constraint
primary key(transaction_id);

To add auto-increment, you can only do it to a primary key:

create table transactions(
  transaction_id int primary key auto_increment,
  amount decimal(5, 2)
);

And it will continue to auto-increment when you add in new values:

insert into transcations (amount)
values(4.99);

A foreign is is a primary key from one table that is being used in another table.

To create a foreign key, use this as an example:

create table transactions(
  transaction_id int primary key auto_increment,
  amount decimal(5, 2)
  customer_id int,
  foregin key(customer_id) references customers(customer_id),
);

Before attempted to delete a foreign key, make sure to:

set foreign_key_checks = 0;

delete from customers
where customer_id = 4;

Once a a foreign key is deleted, we need to delete it from our current table (i.e. transacitons), you'll need to recreate the table like this:

create table transactions(
  transaction_id int primary key auto_increment,
  amount decimal(5, 2)
  customer_id int,
  foregin key(customer_id) references customers(customer_id),
  on delete set null
);

There's two differen't ways to delete a foreign key (aka on delete):

1. replace foreign key with null

on delete set null

2. delete entire row Or:

on delete cascade

Another way to do this might look like:

alter table transactions
add constraint fk_transactions_id
foreign key(customer_id) references customers(customer_id)
on delete cascade;

A stored procedure is prepared/verbose SQL code that you can save / bootstrap.

To turn code into a stored procedure:

delimiter $$
create procedure function_name()
begin
  <enter verbose code here>;
end $$
delimiter ;

To invoke the stored procedure. You can also pass data into the function parenthesis if you want to invoke a particular id within the function.

call function_name(1);

A trigger is when an event happens, do something. Ex. (insert, update or delete). It can check data, handle errors or audit tables. Example: Step 1:

alter table employees
add column salary decimal(10, 2) after hourly_pay;

Step 2:

update employees
set salary = hourly_pay * 2,080 (work hours in a typical year)

Step 3 (create the trigger):

create trigger before_hourly_pay_update
before update on employees
for each row
set NEW.salary = (NEW.hourly_pay * 2080);

Step 4:

show triggers;

Step 5 (update your trigger):

update employees
set hourly_pay = hourly_pay + 1;

Trigger example #2 Step 1:

create trigger before_hourly_pay_insert
before insert on employees
for each row
set new.salary = (new.hourly_pay * 2080);

Step 2:

insert into employees
values(6, "Sheldon","Plankton", 10, NULL, "janitor", "2023-01-07", 5);. 

Trigger example #3: Step 1:

create table expenses(
  expense_id int primary key,
  expense_name varchar(50),
expense_total decimal(10, 2)
);

Step 2:

insert into expenses
values  (1, "salaries", 0),
        (2, "supplies", 0),
        (3, "taxes", 0);

Step 3:

update expenses
set expense_total = (select sum(salary) from employees)
where expense_name = "salaries";

Step 4:

create trigger after_salary_delete
after delete on employees
for each row
update expenses
set expense_total = expense_total - old.salary
where expense_name = "salaries";

Step 5:

delete from employees
where employee_id = 6;

Step 6:

create trigger after_salary_insert
after insert on employees
for each row
update expenses
set expense_total = expense_total + new.salary
where expense_name = "salaries";

Step 7:

insert into employees
values(6, "Sheldon", "Plankton", 10, NULL, "janitor", "2023-01-07", 5);

Example trigger #4: Step 1:

create trigger after_salary_update
after update on employees
for each row
update expenses
set expense_total = expense_total + (new.salary - old.salary)
where expense_name = "salaries";

Step 2:

update employees
set hourly_pay = 100
where employee_id = 1;