Abayomi Ogunnusi

Posted on Aug 30, 2021

mysql 101

#mysql #sql #database #beginners

Hello fams, I started learning a relational database (Sequel Query Language) and I decided to make a post to document my continuous progress. I stem from a NoSQL background (MongoDb). My choice software is Mysql and the Operating system used is windows. All commands should be the same. I have included examples in the Basic Queries Section

Table of Content
🎯 * Installation (windows)
🎯 * Setup (Windows)
🎯 * Path Variable setup
🎯 * Basic Queries
🎯 * Conclusion
🎯 * References

Let's get right to it. Click the link Download Link to navigate to MySQL and download the MSI

I use Windows, and to run my SQL commands from my command prompt we need to add the download path (containing the binary) to our Environment variable. (Highlighted in red)

Go to system properties 👉 Environmental Variables 👉 System variable 👉 Click on path 👉 click on edit 👉 click new 👉 paste the copied path highlighted in red earlier 👉 Ok.
Now we have successfully added this path to our environment variable.

Check if MySQL is running

$ mysql --version

📌 You should get

C:\Program Files\MySQL\MySQL Server 8.0\bin\mysql.exe  Ver 8.0.25 for Win64 on x86_64 (MySQL Community Server - GPL)

With that out of the way let's login to our Mysql with the commands below

login to mysql from terminal (command prompt)
mysql -u root -p
mysql -u joseph -p

🔗 Create a specific user for a DataBase
CREATE USER 'joseph'@'localhost' IDENTIFIED BY 'password';

🔗 To see selected users
SELECT user, host FROM mysql.user;

🔗 Give privileges i.e full access
GRANT ALL PRIVILEGES ON * . * TO 'user'@'localhost';
GRANT ALL PRIVILEGES ON * . * TO 'joseph'@'localhost';

🔗 Flush privileges after granting privileges
FLUSH PRIVILEGES;

🔗 Check privileges for certain users
SHOW GRANTS FOR 'user'@'localhost';
SHOW GRANTS FOR 'joseph'@'localhost';

🔗 Exit mysql terminal to windows terminal by:
exit;

Login into mysql terminal
mysql -u joseph -p

🔗 Actions
SELECT * FROM users;
Note: Actions are mostly written in Capitals.( not compulsory just a convention).

🔗 Create DB
CREATE DATABASE ximple;

🔗 Show DB
SHOW DATABASES;

🔗 To use the actual db
USE
USE ximple;

🔗 ** Show tables**
SHOW TABLES;

🔗 *Create table🔗 *
CREATE TABLE employees( id INT AUTO_INCREMENT, first_name VARCHAR(100), last_name VARCHAR(100), email VARCHAR(75), password VARCHAR(255), location VARCHAR(100), dept VARCHAR(75), is_admin TINYINT(1), register_date DATETIME, PRIMARY KEY(id) );

NB: MAX LENGTH FOR VARCHAR is 255.

🔗 Delete table
DROP TABLE ;
DROP TABLE users;

🔗 Delete Database
DROP DATABLE
DROP DATABASE ximple;

🔗 Inserting single data
INSERT INTO bands (name) VALUES ('Abayomi Joseph');

🔗 Insert
INSERT INTO users (first_name, last_name, email, password, location, dept, is_admin, register_date) values ('James', 'Pork','james@gmail.com', '1234', 'Lagos', 'backend', 1, now());

🔗 Insert multiple values separated with commas
INSERT INTO users (first_name, last_name, email, password, location, dept, is_admin, register_date) values ('John', 'Pork','john@gmail.com', '1234', 'Ibadan', 'frontend', 0, now())<mark>,</mark>('bale', 'mario','mario@gmail.com', '1234', 'Uyo', 'backend', 1, now());

🔗 Select values
SELECT first_name, last_name FROM users;

🔗 WHERE clause
SELECT* FROM users WHERE location = 'sagamu';

🔗 Multiple Conditions
SELECT* FROM users WHERE location = 'sagamu' AND dept = "graphics";

🔗 ALTER TABLE test
ADD another_column VARCHAR(255);

🔗 Bands table
CREATE TABLE bands( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(255) NOT NULL, PRIMARY KEY (id) );

🔗 Albums table linked to bands
CREATE TABLE albums( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(255) NOT NULL, release_year INT, band_id INT NOT NULL, PRIMARY KEY (id), FOREIGN KEY(band_id) REFERENCES bands(id) );

🔗 Inserting multiple data
INSERT INTO bands (name) VALUES ('Abayomi Joseph'), ('the maxess'), ('blues');

🔗 Selecting with limits
SELECT * FROM bands LIMIT 2;

🔗 Selecting specific property
SELECT name FROM bands;

🔗 Renaming using the AS alias
SELECT id AS 'ID', name AS 'Band Name' FROM bands;

🔗 Ordering and sorting data
SELECT * FROM bands ORDER BY name;

🔗 Ordering and sorting data Descending order
SELECT * FROM bands ORDER BY name DESC;

🔗 Ordering and sorting data ascending order
SELECT * FROM bands ORDER BY name ASC;

🔗 *Ordering and sorting data Descending order II *
SELECT * FROM bands ORDER BY name;

🔗 ** Multiple insert**
INSERT INTO albums(name, release_year, band_id) VALUES('the beast', 1990, 1), ('power shell', 1986, 2), ('the shark', 2009, 3), ('boom',NULL, 3);

🔗 Querying data
SELECT * FROM albums; SELECT name FROM albums;

🔗 Selecting distinct data without duplicate
SELECT DISTINCT name FROM albums;

🔗 Updating data
UPDATE albums SET release_year= 1677 WHERE id = 1;

🔗** Filtering data using the WHERE **
SELECT * FROM albums WHERE release_year < 2000;

SELECT * FROM albums* WHERE release_year > 2000;

🔗** Selecting using the string filter**
SELECT * FROM albums WHERE name LIKE '%er%';

The above example is saying filter any string which has 'er' inside

🔗 The OR operator
SELECT * FROM albums WHERE name LIKE '%er%' OR band_id=3;

🔗 The AND clause
SELECT * FROM albums WHERE release_year=1986 AND band_id=1;

🔗 Filtering between two different values
SELECT *FROM albums WHERE release_year BETWEEN 2000 AND 2009;

🔗 ** Filtering NULL SELECT FROM albums*
WHERE release_year IS NULL;

🔗 Delete clause
DELETE FROM albums WHERE id = 4;

🔗 Join tables together
SELECT * FROM bands JOIN albums ON bands.id = albums.band_id;

🔗** Inner join work as above**
SELECT * FROM bands INNER JOIN albums ON bands.id = albums.band_id;

🔗** LEFT JOIN**
SELECT * FROM bands LEFT JOIN albums ON bands.id = albums.band_id;

🔗 RIGHT JOIN
SELECT * FROM albums RIGHT JOIN bands ON bands.id = albums.band_id;

AGGREGRATE FUNCTION

🔗 AVERAGE
SELECT AVG(release_year) FROM albums;

🔗 SUM
SELECT SUM(release_year) FROM albums;

🔗 COUNT BY GROUP
SELECT band_id, COUNT(band_id) FROM albums GROUP BY band_id;

🔗 Complex grouping
SELECT b.name AS band_name, COUNT(a.id) AS num_albums FROM bands AS b LEFT JOIN albums AS a ON b.id = a.band_id GROUP BY b.id;

🔗 HAVING
SELECT b.name AS band_name, COUNT(a.id) AS num_albums FROM bands AS b LEFT JOIN albums AS a ON b.id = a.band_id WHERE b.name = 'blues' GROUP BY b.id HAVING num_albums = 1;

Conclusion:

This is a quick guide into using SQL if I miss any command or you have a better way of querying kindly drop it in the comment section. Thanks 🙌🏽 for reading