Introduction
The Stack overflow developer survey ranks SQL as one of the highly desired and admired programming languages.
*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 FilesMySQLMySQL Server 8.0bin) 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 orsystem 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 typealias 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.