DEV Community

Cover image for SQL ain't that Scary
Abayomi Ogunnusi
Abayomi Ogunnusi

Posted on

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Check databases in MySQL

show databases;
# to see the list of databases
Enter fullscreen mode Exit fullscreen mode
  • 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
Enter fullscreen mode Exit fullscreen mode

Use a database in MySQL

use googledb;
# to use the database
Enter fullscreen mode Exit fullscreen mode

Create a table in MySQL

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

See all tables in MySQL database

show tables;
# to see all tables in the database
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

Select data from a table in MySQL

select * from users;
--  to see all data in the table
Enter fullscreen mode Exit fullscreen mode

Select specific columns from a table in MySQL

select name, email from users;
--  to see only the name and email columns
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Aliases in MySQL

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

Multiple insertions in MySQL

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

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';
Enter fullscreen mode Exit fullscreen mode

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';
Enter fullscreen mode Exit fullscreen mode

Update data in MySQL

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

Delete data in MySQL

delete from users where name = 'John Doe';
# to delete the record of John Doe
Enter fullscreen mode Exit fullscreen mode

Using Wildcards in MySQL

Search ignoring case in MySQL

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

Search for a pattern in MySQL

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

Order By in MySQL

select * from users order by age desc;
# see all users ordered by age in descending order
Enter fullscreen mode Exit fullscreen mode

Limit in MySQL

select * from users limit 2;
# to see only the first 2 records
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode
select * from users where email is null;
select * from users where email is not null;
Enter fullscreen mode Exit fullscreen mode

Min and Max

select min(age) as SmallestAge from users;
-- to see the minimum age
Enter fullscreen mode Exit fullscreen mode
select max(age) as Oldest from users;
-- to see the maximum age
Enter fullscreen mode Exit fullscreen mode

Count, Sum, and Average

select count(*) as TotalUsers from users;
Enter fullscreen mode Exit fullscreen mode

to see the total number of users

select sum(age) as TotalAge from users;
-- to see the total age of all users
Enter fullscreen mode Exit fullscreen mode
select avg(age) as AverageAge from users;
--  to see the average age of all users
Enter fullscreen mode Exit fullscreen mode
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
Enter fullscreen mode Exit fullscreen mode

In Operator in MySQL

select * from users where age in (3, 4, 25, 30);
--  to see all users with age 25 and 30
Enter fullscreen mode Exit fullscreen mode
select * from users where age not in (3, 4, 25, 30);
--  to see all users with age not 25 and 30
Enter fullscreen mode Exit fullscreen mode
Nested Select in MySQL
select * from users where age = (select max(age) from users);
-- to see the user with the maximum age

Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Nested Select in MySQL

select * from users where age = (select max(age) from users);
-- to see the user with the maximum age
Enter fullscreen mode Exit fullscreen mode
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
Enter fullscreen mode Exit fullscreen mode

Concatenate Columns in MySQL

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

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);
Enter fullscreen mode Exit fullscreen mode

Join:

select 
users.name, 
users.email,
orders.product,
orders.amount
from users
join orders
on users.id = orders.user_id;
Enter fullscreen mode Exit fullscreen mode
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);

Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

Without:

select users.name, orders.product from users join orders on users.id = orders.user_id;
Enter fullscreen mode Exit fullscreen mode
  • 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;
Enter fullscreen mode Exit fullscreen mode
  • 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;
Enter fullscreen mode Exit fullscreen mode
  • 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;
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

Check the view

select * from user_orders;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

Drop the view

drop view user_orders;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

Call the procedure

call get_all_users();
Enter fullscreen mode Exit fullscreen mode

Get By Id

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

Call the procedure

call get_user_by_id(1);
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

Call the procedure

call update_user(1, 'John Doe');
Enter fullscreen mode Exit fullscreen mode

Delete

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

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;
Enter fullscreen mode Exit fullscreen mode

Call the procedure

call create_user('Jane Doe', 'jane@c.c', 23);
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

Drop Column

alter table users drop column phone;
Enter fullscreen mode Exit fullscreen mode

Rename Table

alter table users rename to customers;
Enter fullscreen mode Exit fullscreen mode

Drop Table

drop table customers;
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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.

Top comments (0)