mysql 101
Tags: mysql, sql, database, beginners
Published: 2021-08-30 11:27:44




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 <mark>Basic Queries Section </mark>

![minons](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8l0zd9j7oy6ytcankk9r.gif)
 

Table of Content
🎯 * [Installation (windows)](#installation)
🎯 * [Setup (Windows)](#setup)
🎯 * [Path Variable setup](#path)
🎯 * [Basic Queries](#queries)
🎯 * [Conclusion](#conclusion)
🎯 * [References](#references)

<a name="installation"></a>
Let's get right to it. Click the link [Download Link](https://dev.mysql.com/downloads/mysql/) to navigate to MySQL and download the MSI

![msi](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3urum2u7yx0sjcftg070.PNG)
  
<a name="setup"></a>
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)
![path](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/k4r59qb4tvw38z8jhiv0.PNG)

<a name="path"></a>
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.
![system 5](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/exe6zom44pj9cb6b4umz.PNG)
![system3](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/e4n9fphkoixbq43scivq.PNG)

## Check if MySQL is running
`$ mysql --version
`


📌 You should get 
```MYSQL
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

<a name="queries"></a>
```
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 <database name>
`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 <tablename>;
`DROP TABLE users;`

🔗 **Delete Database**
DROP DATABLE <databasename>
`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;`

or

`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 <mark>OR</mark> operator**
`SELECT * FROM albums
WHERE name LIKE '%er%' OR band_id=3;`

🔗 **The <mark>AND</mark> 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;`

---

<a name="conclusion"></a>
## 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


<a name="reference"></a>
## References: 
🍄 [Mike Dane Video](https://www.youtube.com/watch?v=HXV3zeQKqGY&t=58s)
🍄 [Traversy Media Video](https://www.youtube.com/watch?v=9ylj9NR0Lcg)

{% github drsimplegraffiti/drsimplegraffiti %}