SQL ain't that Scary

Introduction

The Stack overflow developer survey ranks SQL as one of the highly desired and admired programming languages.

2024-Rank

*What is SQL? *

SQL stands for Structured Query Language

MYSQL

MySQL is a powerful database management system that can be used to store and manage data for a wide variety of applications. It is widely used in web development, e-commerce, and other applications where data needs to be stored and retrieved quickly and efficiently.

Other databases similar to MySQL

• PostgreSQL
• SQLite
• MariaDB
• Oracle

Why use MySQL?

• Open source and free to use
• Fast and reliable
• Scalable and flexible and so on

TERMINOLOGY

• Database: A collection of related data
• Table: A collection of related data organized in rows and columns
• Column: A vertical entity in a table that contains all information associated with a specific field in a table
• Row: A horizontal entity in a table that contains all information associated with a specific record in a table
• Record: Individual data entries in a table
• RDMS: Relational Database Management System is a software that is used to manage relational databases like MySQL, Oracle, etc.

What is relational Database?

Defines relationships between tables and allows you to query data across multiple tables at once.

How to install MySQL?

• Download MySQL from the official website

Check MySQL version

• Open the terminal and type

mysql --version

If you get an error, bash: mysql: command not found you need to add MySQL to the PATH.(system variable: C:\Program Files\MySQL\MySQL Server 8.0\bin) To do this, follow the steps below:

How to connect to MySQL?

• Open the terminal and type

mysql -u root -p  
# and enter the password

Check databases in MySQL

show databases;
# to see the list of databases

• Note: semicolon ; is used to terminate a query

Clear the terminal

• Type system clear to clear the terminal or system cls for windows or use \! cls for windows or \! clear for linux
• Use an alias to avoid typing system clear every time Open the terminal and type alias clear='system clear' and press enter

Create a database in MySQL

create database googledb;
# to create a database

Use a database in MySQL

use googledb;
# to use the database

Create a table in MySQL

create table users(
    id int auto_increment primary key,
    name varchar(100),
    email varchar(100),
    age int
);

See all tables in MySQL database

show tables;
# to see all tables in the database

Insert data into a table in MySQL

insert into users(name, email, age) values('John Doe', 'doe@gmail.com', 25);
insert into users(name, email, age) values('Jane Doe', 'jane@gmail.com', 23);

Select data from a table in MySQL

select * from users;
--  to see all data in the table

Select specific columns from a table in MySQL

select name, email from users;
--  to see only the name and email columns

Naming conventions in MySQL

• Use lowercase for table names and column names
• Use underscores to separate words in table names and column names
• Use singular names for tables

SELECT name   -- column name
FROM users  -- table name
WHERE age > 25; -- condition

Aliases in MySQL

select name as 'Full Name', email as 'Email Address' from users;
-- to see the name and email columns with aliases

Multiple insertions in MySQL

insert into users(name, email, age) values('Jimmy Lee', 'jimmy@gmail.com', 30), ('Jenny Lee', 'jenny@gmail.com', 28);

Where Clause in MySQL

• Type select * from users where age > 30; to see all users with age greater than 30

SELECT name, email 
FROM users 
WHERE name = 'John Doe';

Possible operators in MySQL to use in the WHERE clause

• =: Equal to
• != or <>: Not equal to
• >: Greater than
• <: Less than
• >=: Greater than or equal to
• <=: Less than or equal to
• BETWEEN: Between a range
• LIKE: Search for a pattern
• IN: To specify multiple values
• AND: To combine multiple conditions
• OR: To specify multiple conditions

select * from users where age <> 2;
select * from users where age != 2;
select * from users where age > 2 and name = 'John Doe';
select * from users where age > 2 or name = 'John Doe';

Update data in MySQL

update users set age = 30 where name = 'John Doe';
#  to update the age of John Doe to 30

Delete data in MySQL

delete from users where name = 'John Doe';
# to delete the record of John Doe

Using Wildcards in MySQL

Search ignoring case in MySQL

select * from users where name like 'j%';
# to search for names starting with 'j'

Search for a pattern in MySQL

select * from users where name like '%o%';
# to search for names containing 'o'

Order By in MySQL

select * from users order by age desc;
# see all users ordered by age in descending order

Limit in MySQL

select * from users limit 2;
# to see only the first 2 records

NOT NULL and NULL in MySQL

create table users(id int auto_increment primary key, name varchar(100) not null, email varchar(100) not null, age int);
# to create a table with NOT NULL constraints

select * from users where email is null;
select * from users where email is not null;

Min and Max

select min(age) as SmallestAge from users;
-- to see the minimum age

select max(age) as Oldest from users;
-- to see the maximum age

Count, Sum, and Average

select count(*) as TotalUsers from users;

to see the total number of users

select sum(age) as TotalAge from users;
-- to see the total age of all users

select avg(age) as AverageAge from users;
--  to see the average age of all users

select count(*) as TotalUsers, sum(age) as TotalAge, avg(age) as AverageAge from users;
-- to see the total number of users, total age, and average age

In Operator in MySQL

select * from users where age in (3, 4, 25, 30);
--  to see all users with age 25 and 30

select * from users where age not in (3, 4, 25, 30);
--  to see all users with age not 25 and 30

Nested Select in MySQL

select * from users where age = (select max(age) from users);
-- to see the user with the maximum age

Breakdown of the query

• select max(age) from users: This query returns the maximum age from the users table
• select * from users where age = (select max(age) from users): This query returns all users whose age is equal to the maximum age

Group By in MySQL

select age, count(*) as TotalUsers from users group by age;
-- to see the total number of users grouped by age

Having Clause in MySQL

select age, count(*) as TotalUsers from users group by age having count(*) > 1;
-- to see the total number of users grouped by age having more than 1 user

Nested Select in MySQL

select * from users where age = (select max(age) from users);
-- to see the user with the maximum age

select * from users where date_created between '2022-01-01' and '2022-12-31';
-- to see all users created between 2022-01-01 and 2022-12-31

Concatenate Columns in MySQL

select concat(name, ' ', email) as 'Full Name' from users;
-- to see the name and email columns concatenated

Joins

Drop the users table and create a new table called users with the following columns

drop table users;

create table users(
    id int auto_increment primary key,
    name varchar(100),
    email varchar(100),
    age int
);

create table orders(
    id int auto_increment primary key,
    user_id int,
    product varchar(100),
    amount int
);

insert into users(name, email, age) values('John Doe', 'joe@gmail.com', 25);
insert into users(name, email, age) values('Jane Doe', 'jam@gmail.com', 23);
insert into orders(user_id, product, amount) values(1, 'Laptop', 1000);
insert into orders(user_id, product, amount) values(2, 'Phone', 500);

Join:

select 
users.name, 
users.email,
orders.product,
orders.amount
from users
join orders
on users.id = orders.user_id;

Foreign keys

Unlike what we have seen in the previous example, we can use foreign keys to establish a relationship between two tables. A foreign key is a column or a set of columns in one table that references the primary key in another table. This relationship helps maintain data integrity and ensures that data is consistent across tables.

Drop the users and orders tables and create new tables with foreign keys

drop table users;
drop table orders;

create table users(
    id int auto_increment primary key,
    name varchar(100),
    email varchar(100),
    age int
);

create table orders(
    id int auto_increment primary key,
    user_id int,
    product varchar(100),
    amount int,
    foreign key(user_id) references users(id)
);

insert into users(name, email, age) values('John Doe', 'john@c.c', 25);
insert into users(name, email, age) values('Jane Doe', 'jane@c.c', 23);
insert into orders(user_id, product, amount) values(1, 'Laptop', 1000);
insert into orders(user_id, product, amount) values(2, 'Phone', 500);

-- null values inserted
insert into orders(user_id, product, amount) values(null, 'Phone', 500);
insert into orders(user_id, product, amount) values(3, 'Phone', 500);

Joins

• Inner Join: Returns records that have matching values in both tables. If there is no match, the result is an empty set. Ex of inner join with and without matching records

With:

select
users.name,
orders.product
from users
join orders
on users.id = orders.user_id;

Without:

select users.name, orders.product from users join orders on users.id = orders.user_id;

• Left Join: Returns all records from the left table and the matched records from the right table. If there is no match, the result is NULL. In our case the Left table is users and the right table is orders

select users.name, orders.product from users left join orders on users.id = orders.user_id;

• Right Join: Returns all records from the right table and the matched records from the left table. If there is no match, the result is NULL. In our case the Right table is orders and the left table is users

select users.name, orders.product from users right join orders on users.id = orders.user_id;

• Full Join: Returns all records when there is a match in either the left or right table. If there is no match, the result is NULL.

select users.name, orders.product from users full join orders on users.id = orders.user_id;

Subqueries

A subquery is a query within another query. It is used to return data that will be used in the main query. Subqueries can be used in the SELECT, FROM, WHERE, and HAVING clauses.

select * from users where age = (select max(age) from users);
-- to see the user with the maximum age

Union

The UNION operator is used to combine the result sets of two or more SELECT statements. It removes duplicate rows between the various SELECT statements.

select name from users
union
select product from orders;

Exists

The EXISTS operator is used to test for the existence of any rows in a subquery. It returns true if the subquery returns one or more rows.

select name from users where exists (select * from orders where users.id = orders.user_id);

Having

The HAVING clause is used to filter the results of an aggregate function in a query. It is similar to the WHERE clause but is used with aggregate functions like COUNT, SUM, AVG, etc.

select user_id, sum(amount) as TotalAmount from orders group by user_id having sum(amount) > 500;

All and Any

The ALL and ANY operators are used to compare a value to a set of values returned by a subquery. The ALL operator returns true if the comparison is true for all values in the set, while the ANY operator returns true if the comparison is true for any value in the set.

select name from users where age > all (select age from users);
select name from users where age > any (select age from users);

Views

A view is a virtual table that is based on the result of a SELECT query. It is used to simplify complex queries and to hide the complexity of the underlying tables from the user.

create view user_orders as
select
users.name,
orders.product
from users
join orders
on users.id = orders.user_id;

Check the view

select * from user_orders;

Update the view

create or replace view user_orders as
select
users.name,
orders.product,
orders.amount
from users
join orders
on users.id = orders.user_id;

Drop the view

drop view user_orders;

Views with arguments

Views with arguments are called stored procedures. A stored procedure is a set of SQL statements that are stored in the database and can be executed by calling the procedure name.

Lets write crud open, GET all, GET by id, UPDATE, DELETE, CREATE

create procedure get_all_users()
begin
select * from users;
end;

Call the procedure

call get_all_users();

Get By Id

create procedure get_user_by_id(in user_id int)
begin
select * from users where id = user_id;
end;

Call the procedure

call get_user_by_id(1);

Update

create procedure update_user(in user_id int, in user_name varchar(100))
begin
update users set name = user_name where id = user_id;
end;

Call the procedure

call update_user(1, 'John Doe');

Delete

create procedure delete_user(in user_id int)
begin
delete from users where id = user_id;
end;

Create a user

create procedure create_user(in user_name varchar(100), in user_email varchar(100), in user_age int)
begin
insert into users(name, email, age) values(user_name, user_email, user_age);
end;

Call the procedure

call create_user('Jane Doe', 'jane@c.c', 23);

ALTER TABLE

The ALTER TABLE statement is used to add, modify, or delete columns in an existing table. It can also be used to add or remove constraints like PRIMARY KEY, FOREIGN KEY, etc.

alter table users add column phone varchar(100);

Drop Column

alter table users drop column phone;

Rename Table

alter table users rename to customers;

Drop Table

drop table customers;

Index in MySQL

An index is a data structure that is used to speed up the retrieval of data from a table. It is created on one or more columns in a table and helps in faster retrieval of data when those columns are used in the WHERE clause of a query.

create index idx_name on users(name);

Constraints

**Constraints **are _rules _that are enforced on the data in a table. They are used to ensure the integrity and consistency of the data in the database.

• NOT NULL: Ensures that a column cannot have a NULL value
• UNIQUE: Ensures that all values in a column are unique
• PRIMARY KEY: A combination of NOT NULL and UNIQUE. It uniquely identifies each record in a table
• FOREIGN KEY: Ensures that the values in a column match the values in another table's primary key
• CHECK: Ensures that all values in a column satisfy a specific condition
• DEFAULT: Sets a default value for a column if no value is specified
• INDEX: Used to create an index on a table. This helps in faster retrieval of data

In action

create table users(
    id int auto_increment primary key,
    name varchar(100) not null,
    email varchar(100) unique,
    age int check(age > 0),
    phone varchar(100) default '1234567890'
);

-- index
create index idx_name on users(name);

Transactions

A _transaction _is a set of SQL statements that are executed as a single unit. It is used to ensure that all the statements in the transaction are executed successfully, or none of them are executed at all.

• BEGIN TRANSACTION: Starts a new transaction
• COMMIT: Saves the changes made by the transaction
• ROLLBACK: Undoes the changes made by the transaction

begin transaction;
insert into users(name, email, age) values('John Doe', 'john@c.c', 25);
insert into users(name, email, age) values('Jane Doe', 'jane@c.c', 23);
commit;

To simulate the error

begin transaction;
insert into users(name, email, age) values('John Doe', 'john@c.c', 25);
insert into users(name, email, age) values('Jane Doe', 'jane@c.c', 23);
rollback;

Conclusion

In this article, we have covered the basics of MySQL, including how to install MySQL, connect to MySQL, create databases and tables, insert, update, and delete data, and use various SQL queries to retrieve and manipulate data. We have also covered advanced topics like joins, subqueries, views, stored procedures, and transactions. MySQL is a powerful database management system that is widely used in web development, e-commerce, and other applications. It is essential to have a good understanding of MySQL to work with databases effectively. I hope this article has helped you get started with MySQL and that you will continue to explore and learn more about this powerful database management system.